You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
327 lines
15 KiB
327 lines
15 KiB
using Newtonsoft.Json;
|
|
using NFine.Code;
|
|
using NFine.Data.Extensions;
|
|
using NFine.Domain._03_Entity.SRM;
|
|
using NFine.Repository;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Configuration;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using System.Net;
|
|
using System.Net.Mail;
|
|
using System.Net.Security;
|
|
using System.Security.Cryptography.X509Certificates;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace NFine.Application.SRM
|
|
{
|
|
public class ICSSrmMessageApp : RepositoryFactory<ICSVendor>
|
|
{
|
|
/// <summary>
|
|
/// 获取消息信息
|
|
/// </summary>
|
|
/// <param name="queryJson"></param>
|
|
/// <param name="jqgridparam"></param>
|
|
/// <returns></returns>
|
|
public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = @"SELECT
|
|
a.MESSAGEID
|
|
,MESSAGECONTENT
|
|
,MESSAGETITLE
|
|
,c.USERCODE
|
|
,a.CreateDate
|
|
,c.VENDORCODE
|
|
FROM ICSSRMMESSAGE a
|
|
LEFT JOIN ICSSRMMESSAGEATTACHMENT b ON a.MESSAGEID=b.MESSAGEID
|
|
LEFT JOIN ICSSRMMESSAGEREVEIVER c ON a.MESSAGEID=c.MESSAGEID
|
|
LEFT JOIN ICSSRMMESSAGETO d ON a.MESSAGEID=d.MESSAGEID
|
|
Where 1=1 ";
|
|
if (!string.IsNullOrWhiteSpace(queryJson))
|
|
{
|
|
if (!string.IsNullOrWhiteSpace(queryParam["MESSAGETITLE"].ToString()))
|
|
{
|
|
sql += " and MESSAGETITLE like '%" + queryParam["MESSAGETITLE"].ToString() + "%' ";
|
|
}
|
|
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
|
|
{
|
|
sql += " and a.StarTime >= '" + queryParam["TimeFrom"].ToString() + "' ";
|
|
}
|
|
if (!string.IsNullOrWhiteSpace(queryParam["TimeTo"].ToString()))
|
|
{
|
|
sql += " and a.EndTime <= '" + queryParam["TimeTo"].ToString() + "' ";
|
|
}
|
|
//if (!string.IsNullOrWhiteSpace(queryParam["Status"].ToString()))
|
|
//{
|
|
// sql += " and a.Status ='" + queryParam["Status"].ToString() + "'";
|
|
|
|
//}
|
|
}
|
|
return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
|
|
}
|
|
/// <summary>
|
|
/// 删除消息信息
|
|
/// </summary>
|
|
/// <param name="keyValue"></param>
|
|
/// <returns></returns>
|
|
public string DeleteICSSrmMessage(string keyValue)
|
|
{
|
|
string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MUSERName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string sql = string.Empty;
|
|
sql = @" delete FROM ICSSRMMESSAGE where BidCode in ({0})
|
|
delete FROM ICSSRMMESSAGEATTACHMENT where BidCode in ({0})
|
|
delete FROM ICSSRMMESSAGEREVEIVER where BidCode in ({0})
|
|
delete FROM ICSSRMMESSAGETO where BidCode in ({0})";
|
|
sql = string.Format(sql, keyValue);
|
|
string msg = "";
|
|
try
|
|
{
|
|
SqlHelper.ExecuteNonQuery(sql);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
msg = ex.Message;
|
|
}
|
|
return msg;
|
|
|
|
}
|
|
//保存
|
|
public void SaveDetail(string queryJson)
|
|
{
|
|
string sql = string.Empty;
|
|
try
|
|
{
|
|
string SendHost = ConfigurationManager.ConnectionStrings["SendHost"].ConnectionString;
|
|
string StrSendPort = ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString;
|
|
int SendPort = 25;
|
|
if (!string.IsNullOrEmpty(StrSendPort))
|
|
SendPort = Convert.ToInt32(ConfigurationManager.ConnectionStrings["SendPort"].ConnectionString);
|
|
string SendDisplayName = ConfigurationManager.ConnectionStrings["SendDisplayName"].ConnectionString;
|
|
string SendAddress = ConfigurationManager.ConnectionStrings["SendAddress"].ConnectionString;
|
|
string SendPassword = ConfigurationManager.ConnectionStrings["SendPassword"].ConnectionString;
|
|
ICSSRMMESSAGE[] details = JsonConvert.DeserializeObject<ICSSRMMESSAGE[]>(queryJson);
|
|
string connString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
|
|
SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString);
|
|
conn.Open();
|
|
SqlTransaction sqlTran = conn.BeginTransaction();
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.Transaction = sqlTran;
|
|
cmd.Connection = conn;
|
|
string MESSAGEID = "";
|
|
try
|
|
{
|
|
string Muser = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
|
|
string MuserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
|
|
foreach (var obj in details)
|
|
{
|
|
if (string.IsNullOrWhiteSpace(obj.MESSAGEID))
|
|
{
|
|
MESSAGEID = Guid.NewGuid().ToString();
|
|
}
|
|
else
|
|
{
|
|
MESSAGEID = obj.MESSAGEID;
|
|
}
|
|
sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSSRMMESSAGE WHERE MESSAGEID=@MESSAGEID)
|
|
BEGIN
|
|
INSERT INTO dbo.ICSSRMMESSAGE
|
|
( MESSAGEID
|
|
,MESSAGECONTENT
|
|
,MESSAGETITLE
|
|
,LOGDATE
|
|
,LogUser
|
|
,CREATEDATE
|
|
,CREATEUSER)
|
|
VALUES ( @MESSAGEID
|
|
, @MESSAGECONTENT
|
|
, @MESSAGETITLE
|
|
, @LOGDATE
|
|
, @LogUser
|
|
, @CREATEDATE
|
|
, @CREATEUSER)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.ICSSRMMESSAGE SET
|
|
MESSAGECONTENT=@MESSAGECONTENT
|
|
,MESSAGETITLE=@MESSAGETITLE
|
|
,LOGDATE=@LOGDATE
|
|
,LogUser=@LogUser
|
|
WHERE MESSAGEID=@MESSAGEID
|
|
END";
|
|
SqlParameter[] sp_Detail = {
|
|
new SqlParameter("@MESSAGEID",MESSAGEID),
|
|
new SqlParameter("@MESSAGECONTENT",obj.MessageContent),
|
|
new SqlParameter("@MESSAGETITLE",obj.MessageTitle),
|
|
new SqlParameter("@LOGDATE",DateTime.Now.ToString("yyyy-MM-dd")),
|
|
new SqlParameter("@LogUser",Muser),
|
|
new SqlParameter("@CREATEDATE",DateTime.Now.ToString("yyyy-MM-dd")),
|
|
new SqlParameter("@CREATEUSER",Muser),
|
|
};
|
|
SqlCommandHelper.CmdExecuteNonQuery(sql, sp_Detail, cmd);
|
|
|
|
#region 附件信息
|
|
if (!string.IsNullOrWhiteSpace(obj.ATTACHMENTNAME))
|
|
{
|
|
sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSSRMMESSAGEATTACHMENT WHERE MESSAGEID=@MESSAGEID)
|
|
BEGIN
|
|
INSERT INTO dbo.ICSSRMMESSAGEATTACHMENT( ATTACHMENTID, ATTACHMENTNAME, ATTACHMENTPATH, ATTACHMENTURL, MESSAGEID)
|
|
VALUES ( NewID(), @ATTACHMENTNAME, @ATTACHMENTPATH, @ATTACHMENTURL, @MESSAGEID)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.ICSSRMMESSAGEATTACHMENT SET ATTACHMENTNAME=@ATTACHMENTNAME,ATTACHMENTPATH=@ATTACHMENTPATH,ATTACHMENTURL=@ATTACHMENTURL
|
|
WHERE MESSAGEID=@MESSAGEID
|
|
END";
|
|
SqlParameter[] sp_BD = {
|
|
new SqlParameter("@ATTACHMENTNAME",obj.ATTACHMENTNAME),
|
|
new SqlParameter("@ATTACHMENTPATH",obj.ATTACHMENTPATH),
|
|
new SqlParameter("@ATTACHMENTURL",obj.ATTACHMENTURL),
|
|
new SqlParameter("@MESSAGEID",MESSAGEID),
|
|
};
|
|
SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BD, cmd);
|
|
}
|
|
|
|
#endregion
|
|
#region 消息发送对象
|
|
sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSSRMMESSAGEREVEIVER WHERE MESSAGEID=@MESSAGEID)
|
|
BEGIN
|
|
INSERT INTO dbo.ICSSRMMESSAGEREVEIVER
|
|
(MESSAGEID
|
|
,PARENTMESSAGEID
|
|
,USERCODE
|
|
,USERTYPE
|
|
,VENDORCODE
|
|
,CREATEDATE
|
|
,CREATEUSER
|
|
,LOGDATE
|
|
,LOGUSER)
|
|
VALUES (@MESSAGEID
|
|
, NewID()
|
|
, @USERCODE
|
|
, @USERTYPE
|
|
, @VENDORCODE
|
|
, @CREATEDATE
|
|
, @CREATEUSER
|
|
, @LOGDATE
|
|
, @LOGUSER)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.ICSSRMMESSAGEREVEIVER SET USERCODE=@USERCODE
|
|
,USERTYPE=@USERTYPE
|
|
,LOGDATE=@LOGDATE
|
|
,LOGUSER=@LOGUSER WHERE MESSAGEID=@MESSAGEID
|
|
END";
|
|
SqlParameter[] sp_GYS = {
|
|
new SqlParameter("@MESSAGEID",MESSAGEID),
|
|
new SqlParameter("@USERCODE",Muser),
|
|
new SqlParameter("@USERTYPE",Muser),
|
|
new SqlParameter("@VENDORCODE",obj.VENDORCODE),
|
|
new SqlParameter("@LOGDATE",DateTime.Now.ToString("yyyy-MM-dd")),
|
|
new SqlParameter("@LOGUSER",Muser),
|
|
new SqlParameter("@CREATEDATE",DateTime.Now.ToString("yyyy-MM-dd")),
|
|
new SqlParameter("@CREATEUSER",Muser),
|
|
};
|
|
SqlCommandHelper.CmdExecuteNonQuery(sql, sp_GYS, cmd);
|
|
#endregion
|
|
#region 消息收件人
|
|
//投标信息
|
|
sql = @"IF NOT EXISTS(SELECT * FROM dbo.ICSSRMMESSAGETO WHERE MESSAGEID=@MESSAGEID)
|
|
BEGIN
|
|
INSERT INTO dbo.ICSSRMMESSAGETO (
|
|
MESSAGEID
|
|
,MESSAGETOID
|
|
,STATUS
|
|
,USERCODE
|
|
,CREATEDATE
|
|
,CREATEUSER
|
|
,LOGDATE
|
|
,LOGUSER)
|
|
VALUES
|
|
(@MESSAGEID, newid(), @STATUS, @USERCODE, @CREATEDATE, @CREATEUSER, @LOGDATE,@LOGUSER)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE dbo.ICSSRMMESSAGETO SET USERCODE=@USERCODE, LOGDATE=@LOGDATE, LOGUSER=@LOGUSER
|
|
WHERE MESSAGEID=@MESSAGEID
|
|
END";
|
|
SqlParameter[] sp_BidInfo = {
|
|
|
|
new SqlParameter("@MESSAGEID",MESSAGEID),
|
|
//new SqlParameter("@TBCount",details.ZTBCount),
|
|
new SqlParameter("@STATUS","1"),
|
|
new SqlParameter("@USERCODE",Muser),
|
|
new SqlParameter("@LOGDATE",DateTime.Now.ToString("yyyy-MM-dd")),
|
|
new SqlParameter("@LogUser",Muser),
|
|
new SqlParameter("@CREATEDATE",DateTime.Now.ToString("yyyy-MM-dd")),
|
|
new SqlParameter("@CREATEUSER",Muser),
|
|
};
|
|
SqlCommandHelper.CmdExecuteNonQuery(sql, sp_BidInfo, cmd);
|
|
#endregion
|
|
cmd.Transaction.Commit();
|
|
|
|
}
|
|
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
cmd.Transaction.Rollback();
|
|
throw new Exception(ex.Message);
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
}
|
|
conn.Dispose();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(ex.Message);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改时获取维护信息
|
|
/// </summary>
|
|
/// <param name="POCode"></param>
|
|
/// <param name="PORow"></param>
|
|
/// <param name="WorkPoint"></param>
|
|
/// <returns></returns>
|
|
public DataTable ICSICSSrmMessageByCreate(string MESSAGEID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
//var queryParam = queryJson.ToJObject();
|
|
List<DbParameter> parameter = new List<DbParameter>();
|
|
string sql = string.Empty;
|
|
sql = @"SELECT
|
|
a.MESSAGEID
|
|
,MESSAGECONTENT
|
|
,MESSAGETITLE
|
|
,c.USERCODE
|
|
,a.CreateDate
|
|
,c.VENDORCODE
|
|
FROM ICSSRMMESSAGE a
|
|
LEFT JOIN ICSSRMMESSAGEATTACHMENT b ON a.MESSAGEID=b.MESSAGEID
|
|
LEFT JOIN ICSSRMMESSAGEREVEIVER c ON a.MESSAGEID=c.MESSAGEID
|
|
LEFT JOIN ICSSRMMESSAGETO d ON a.MESSAGEID=d.MESSAGEID
|
|
Where 1=1 ";
|
|
sql += " and a.MESSAGEID='" + MESSAGEID + "'";
|
|
return Repository().FindTableBySql(sql.ToString());
|
|
}
|
|
|
|
}
|
|
}
|