PNSRM/NFine.Application/KBSSRM/MaterialImportApp.cs

using ICSSoft.SendMail;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NFine.Code;
using NFine.Data.Extensions;
using NFine.Domain._03_Entity.SRM;
using NFine.Repository;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Net.Mime;
using System.Net.Security;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.Mail;

namespace NFine.Application.KBSSRM
{
    public class MaterialImportApp : RepositoryFactory<ICSVendor>
    {

        public DataTable SelectColumnName(string BeginTime, string EndTime)
        {
            DateTime ETtim;
            if (string.IsNullOrEmpty(BeginTime))
            {
                BeginTime = DateTime.Now.ToString("yyyy-MM-dd");
                ETtim = Convert.ToDateTime(BeginTime).AddDays(15);
                EndTime = ETtim.ToString("yyyy-MM-dd");
            }
            string sql = @"declare @StartDate DATETIME = '{0}'
declare @EndDate DATETIME = '{1}' 
select ColCaption , ColCaption ColFiledName
from(
select CONVERT(varchar(100), dateadd(day,number,@StartDate), 111) as ColCaption
 from master.dbo.spt_values  where type ='P'
and number <=DATEDIFF(day, @StartDate,   @EndDate)) a";
            sql = string.Format(sql, BeginTime, EndTime, Convert.ToDateTime(BeginTime).ToString("yyyy/MM/dd"));
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }

        public DataTable GetListGridJsonTOZ(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string wheresql = string.Empty;
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"].ToString()))
                {
                    wheresql += " and VenCode like ''%" + queryParam["VenCode"].ToString() + "%''";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"].ToString()))
                {
                    wheresql += " and VenName like ''%" + queryParam["VenName"].ToString() + "%''";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    wheresql += " and InvCode like ''%" + queryParam["InvCode"].ToString() + "%''";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"].ToString()))
                {
                    wheresql += " and  InvName like ''%" + queryParam["InvName"].ToString() + "%''";
                }

            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
            {
                wheresql += " and VenCode like ''%" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "%''";
            }
            string sql = @" if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#table3')) 
DROP TABLE #table3  
if exists(select * from tempdb..sysobjects where id = object_id('tempdb..#TempForecast')) 
DROP TABLE #TempForecast    
declare @StartDate DATETIME = '{0}'  declare @EndDate DATETIME ='{1}'  
select CONVERT(varchar(100), dateadd(day,number,@StartDate), 111) as DATE_NUM
into #table3
 from master.dbo.spt_values  where type ='P'
and number <=DATEDIFF(day, @StartDate,   @EndDate) 

declare @sql varchar(MAX)
declare @PlanTime varchar(MAX)
select @sql = isnull(@sql + ',', '') + ' max(case b.PDate when ''' + PlanTime + ''' then REPLACE(CONVERT(VARCHAR(20),CAST(b.Quantity AS MONEY),1),''.00'','''') else ''0'' end) [' + PlanTime + ']'
from(select distinct CONVERT(varchar(100), a.DATE_NUM, 23) PlanTime
from #table3 a
LEFT JOIN ICSInvCodeImport b ON CONVERT(varchar(100), b.PDate, 23) = CONVERT(varchar(100), a.DATE_NUM, 23)
WHERE 1 = 1 
) as a
select @PlanTime = isnull(@PlanTime + ',', '') + ' isnull([' + PlanTime + '] , 0) [' + PlanTime + ']'
from(select distinct CONVERT(varchar(100), a.DATE_NUM, 23) PlanTime
from #table3 a
LEFT JOIN ICSInvCodeImport b ON CONVERT(varchar(100), PDate, 23) = CONVERT(varchar(100), a.DATE_NUM, 23)
WHERE 1 = 1 
) as a
set @sql = 'SELECT b.Code,b.VenCode ,b.VenName ,b.InvCode ,b.InvName ,'+@sql+'
INTO ##TempForecast
FROM [dbo].[ICSInvCodeImport] b
where 1=1  {2}
GROUP BY b.VenCode ,b.VenName ,b.InvCode ,b.InvName,b.Code


'
exec(@sql)
 ";

            sql = string.Format(sql, Convert.ToDateTime(queryParam["BeginTime"].ToString()).ToString("yyyy/MM/dd"), Convert.ToDateTime(queryParam["EndTime"].ToString()).ToString("yyyy/MM/dd"), wheresql);
            return SqlHelper.FindTablePageBySql_OtherTemp2(sql.ToString(), " " + "##TempForecast" + " ", " DROP TABLE #table3;drop table ##TempForecast;", parameter.ToArray(), ref jqgridparam);

        }



        public object GetStartMonth(string keyValue)
        {
            var reqInterNme = "http://172.66.9.15:9090/sys/user/login";
            var userInfo = new Dictionary<string, string>
                     {
                    {"id", "wms"},
                    {"password", "DrCoffee@wms1114"},
                    };
            string jsonString = JsonConvert.SerializeObject(userInfo, Formatting.Indented);
            var responseStr = SqlHelper.HttpPost(reqInterNme, jsonString);
            JObject res = (JObject)JsonConvert.DeserializeObject(responseStr);
            //  string Data = res["data"].ToString();//获取Tockn
            string token = (string)res["data"]["token"];//获取Tockn
            string APIURLBidversion = "http://172.66.9.15:9090/mrp/need/getExternalVersionByLatest";
            var ResultVersion = SqlHelper.httpGetByHeader(APIURLBidversion, "Authorization", token);
            JObject resVersion = (JObject)JsonConvert.DeserializeObject(ResultVersion);
            string startmonth = (string)resVersion["start_month"];//获取Tockn
            string version = (string)resVersion["version"];
            var Header = new
            {
                startmonth = startmonth,
                version = version,
            };
            return Header;
        }






        public DataTable GetListGridJsonTOZ2(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            var reqInterNme = "http://172.66.9.15:9090/sys/user/login";
            var userInfo = new Dictionary<string, string>
                     {
                    {"id", "wms"},
                    {"password", "DrCoffee@wms1114"},
                    };
            string jsonString = JsonConvert.SerializeObject(userInfo, Formatting.Indented);
            var responseStr = SqlHelper.HttpPost(reqInterNme, jsonString);
            JObject res = (JObject)JsonConvert.DeserializeObject(responseStr);
          //  string Data = res["data"].ToString();//获取Tockn
            string token = (string)res["data"]["token"];//获取Tockn
            string APIURLBidversion = "http://172.66.9.15:9090/mrp/need/getExternalVersionByLatest";
            var ResultVersion = SqlHelper.httpGetByHeader(APIURLBidversion, "Authorization", token);
            JObject resVersion = (JObject)JsonConvert.DeserializeObject(ResultVersion);
            string version = (string)resVersion["version"];//获取Tockn
            string APIURLBid = "http://172.66.9.15:9090/mrp/need/getMaterialRequirementExternalData?version="+ version;
            var Result = SqlHelper.httpGetByHeader(APIURLBid, "Authorization", token);
            DataTable inputTable = JsonConvert.DeserializeObject<DataTable>(Result);
            inputTable.Columns.Add("ID", typeof(string));
            foreach (DataRow row in inputTable.Rows)
            {
                // 获取 Code 列和 Seq 列的值
                string code = row["code"].ToString();
                string suppliercode = row["supplier_code"].ToString();
                // 将两列的值拼接起来作为新列 "ID" 的值
                row["ID"] = suppliercode + code;
            }
            DataTable resultTable = inputTable.Clone();
            DataRow[] rows = inputTable.Select();
            bool filterApplied = false;

            // 首先检查 queryJson 是否不为空或仅包含空白字符
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                string filter = "";
                // 检查 VenCode 是否不为空或仅包含空白字符
                if (!string.IsNullOrWhiteSpace(queryParam["VenCode"]?.ToString()))
                {
                    filter += "supplier_code like '%" + queryParam["VenCode"].ToString() + "%' AND ";
                }
                // 检查 VenName 是否不为空或仅包含空白字符
                if (!string.IsNullOrWhiteSpace(queryParam["VenName"]?.ToString()))
                {
                    filter += "supplier_name like '%" + queryParam["VenName"].ToString() + "%' AND ";
                }
                // 检查 InvCode 是否不为空或仅包含空白字符
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"]?.ToString()))
                {
                    filter += "code like '%" + queryParam["InvCode"].ToString() + "%' AND ";
                }
                // 检查 InvName 是否不为空或仅包含空白字符
                if (!string.IsNullOrWhiteSpace(queryParam["InvName"]?.ToString()))
                {
                    filter += "materiel_name like '%" + queryParam["InvName"].ToString() + "%' AND ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["beforeprincipal"]?.ToString()))
                {
                    filter += "before_principal like '%" + queryParam["beforeprincipal"].ToString() + "%' AND ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["afterprincipal"]?.ToString()))
                {
                    filter += "after_principal like '%" + queryParam["afterprincipal"].ToString() + "%' AND ";
                }
                if (!string.IsNullOrEmpty(filter))
                {
                    filter = filter.Substring(0, filter.Length - 5); // 移除最后的 " AND "
                    rows = inputTable.Select(filter);
                    filterApplied = true;
                }
            }

            // 检查角色编码是否为 Vendor
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode == "Vendor")
            {
                string vendorFilter = "supplier_code like '%" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "%'";
                if (filterApplied)
                {
                    rows = rows.Where(row => row.Table.Select(vendorFilter).Contains(row)).ToArray();
                }
                else
                {
                    rows = inputTable.Select(vendorFilter);
                }
            }

            foreach (DataRow row in rows)
            {
                resultTable.ImportRow(row);
            }
            return SqlHelper.FindTablePageBySql_OtherTempKbs( ref jqgridparam, resultTable);
            

        }






        public string SetData_PR(String savePath)
        {
            DataTable data = FileToExcel.ExcelToTable(savePath);
            string msg = EmailNotice(data);
            return msg;
        }





        public string EmailNotice(DataTable data)
        {
            string msg = "";
            //数据获取
            try
            {
                DataTable DtEmail = new DataTable();
                DtEmail.Columns.Add("供应商编码", typeof(string));
                DtEmail.Columns.Add("供应商名称", typeof(string));
                DtEmail.Columns.Add("料品编码", typeof(string));
                DtEmail.Columns.Add("料品名称", typeof(string));
                DtEmail.Columns.Add("日期", typeof(string));
                DtEmail.Columns.Add("采购订单号", typeof(string));
                DtEmail.Columns.Add("采购订行号", typeof(string));
                DtEmail.Columns.Add("单据数量", typeof(string));

                string sqlVbl = @"select top 0 *,'' as PoNum,'' as PDate,'' as Code from dbo.ICSPurchaseOrder";
                DataTable dtVbl = SqlHelper.GetDataTableBySql(sqlVbl);
                string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                SqlConnection conn = SqlHelper.GetDataCenterConn();
                string sql = "";
                int count = 0;
             
                string sqlCode = @"select distinct max(code) from ICSInvCodeImport";
                object Code = SqlHelper.ExecuteScalar(sqlCode);
                if (data != null && data.Rows.Count > 0)
                {
                    foreach (DataRow dr in data.Rows)
                    {
                        bool Flag = false;
                        if (Code.ToString()=="")
                        {
                            Code = "00001";
                        }
                        else
                        {
                            Code = (Convert.ToInt32(Code) + 1).ToString("D6");
                        }
                  
                        string GUID = Guid.NewGuid().ToString();
                        if (string.IsNullOrWhiteSpace(dr["供应商编码"].ToString()))
                            throw new Exception("供应商编码不能为空！");
                        if (string.IsNullOrWhiteSpace(dr["物料编码"].ToString()))
                            throw new Exception("物料编码不能为空！");
                        string sqlVendor = "SELECT VenName FROM ICSVendor WHERE VenCode='" + dr["供应商编码"].ToString() + "'";
                        DataTable dtVendor = SqlHelper.GetDataTableBySql(sqlVendor);
                        if (dtVendor == null || dtVendor.Rows.Count < 0)
                        {
                            msg += "供应商编码" + dr["供应商编码"].ToString() + "不存在";
                            continue;
                        }
                        string VenName = dtVendor.Rows[0]["VenName"].ToString();
                        string sqlInv = "SELECT INVName FROM ICSINVENTORY WHERE INVCODE='" + dr["物料编码"].ToString() + "'";
                        DataTable dtInv = SqlHelper.GetDataTableBySql(sqlInv);
                        if (dtInv == null || dtInv.Rows.Count <= 0)
                        {
                            msg += "物料编码" + dr["物料编码"].ToString() + "不存在";
                            continue;
                        }
                        string INVName = dtInv.Rows[0]["INVName"].ToString();

                        string sqlSeach = @"select *,Quantity-isnull(InQuantity,0) as PoNum,'' as PDate,'' as Code from dbo.ICSPurchaseOrder where   Quantity-isnull(InQuantity,0)>0 and InvCode='" + dr["物料编码"].ToString() + "' and VenCode='" + dr["供应商编码"].ToString() + "' order by MTIME desc ";
                        DataTable dtPo = SqlHelper.GetDataTableBySql(sqlSeach);
                        foreach (DataColumn dc in data.Columns)
                        {
                            if (dc.Caption == "供应商编码" || dc.Caption == "供应商名称" || dc.Caption == "物料编码" || dc.Caption == "物料名称")
                                continue;
                            sql += @"INSERT into ICSInvCodeImport(ID, VenCode, VenName, InvCode, InvName, 
                            PDate, cModifyPerson, dModifyDate, SendDate, WorkPoint, Quantity, Code)
							values( newid(),'{0}','{1}','{2}','{3}','{4}','{5}',getdate(),'',{6},'{7}','{8}' )";
                            sql = string.Format(sql, dr["供应商编码"].ToString(), dtVendor.Rows[0]["VenName"].ToString(), dr["物料编码"].ToString(), dtInv.Rows[0]["INVName"].ToString(),
                                dc.Caption, UserCode, WorkPoint, string.IsNullOrWhiteSpace(dr[dc.Caption].ToString()) ? 0 : Convert.ToDouble((dr[dc.Caption].ToString())), Code);

                            double ss = string.IsNullOrWhiteSpace(dr[dc.Caption].ToString()) ? 0 : Convert.ToDouble((dr[dc.Caption].ToString()));
                            if (dtPo == null || data.Rows.Count <= 0)
                            {
                                if (ss < 0)
                                {
                                    // msg += "提示采购人员供应商:" + dr["供应商编码"].ToString() + "物料:" + dr["物料编码"].ToString() + "订单数量不足了";
                                    // msg += "供应商" + dtVendor.Rows[0]["cVenName"].ToString() + "请在" + dc.Caption + "日前,完成物料" + dr["物料编码"].ToString() + "数量" + data.Columns + "送货";
                                    Flag = true;
                                }

                            }
                            if (ss < 0)
                            {
                                //100
                                double RequiredNum = Math.Abs(ss);
                                //循环该行供应商有未入库数量的采购订单
                                for (int i = dtPo.Rows.Count - 1; i >= 0; i--)
                                {
                                    //判断导入日期下的数量是否全部匹配完了
                                    if (RequiredNum == 0)
                                        continue;
                                    sql += @"INSERT into ICSInvCodeImportByPO(ID,VenCode,InvCode,PDate,PoCode,Sequence,
			                                    Quantity,ParentQuantity,CreateTime,CreateUser)
                                    values( newid(),'{0}','{1}','{2}','{3}','{4}','{5}','{6}',getdate(),'{7}' )";
                                   //需求100   订单数500 
                                    if (Convert.ToDouble(dtPo.Rows[i]["PoNum"].ToString()) - RequiredNum > 0)
                                    {
                                        var PoNum = Convert.ToDouble(dtPo.Rows[i]["PoNum"].ToString()) - RequiredNum;
                                        dtPo.Rows[i]["PoNum"] = PoNum;
                                        RequiredNum = 0;
                                        //在这里做订单与导入行的关联操作(需要记录订单实际匹配过去的数量)
                                        sql = string.Format(sql, dr["供应商编码"].ToString(), dr["物料编码"].ToString(), dc.Caption, dtPo.Rows[i]["POCode"].ToString(), dtPo.Rows[i]["Sequence"].ToString()
                                            , PoNum, Math.Abs(ss),UserCode);
                                        //  供应商dt (001号单据  数 100)
                                        DataRow newrow = DtEmail.NewRow();
                                        newrow["供应商编码"] = dr["供应商编码"].ToString();
                                        newrow["供应商名称"] = dtVendor.Rows[0]["VenName"].ToString();
                                        newrow["料品编码"] = dr["物料编码"].ToString();
                                        newrow["料品名称"] = dtInv.Rows[0]["INVName"].ToString();
                                        newrow["日期"] = dc.Caption;
                                        newrow["采购订单号"] = dtPo.Rows[i]["POCode"].ToString();
                                        newrow["采购订行号"] = dtPo.Rows[i]["Sequence"].ToString();
                                        newrow["单据数量"] = Math.Round(PoNum, 3);
                                        DtEmail.Rows.Add(newrow);
                                    }
                                    else
                                    {
                                      
                                        RequiredNum = RequiredNum - Convert.ToDouble(dtPo.Rows[i]["PoNum"].ToString());
                                        //在这里做订单与导入行的关联操作(订单的未入库数量)
                                        sql = string.Format(sql, dr["供应商编码"].ToString(), dr["物料编码"].ToString(), dc.Caption, dtPo.Rows[i]["POCode"].ToString(), dtPo.Rows[i]["Sequence"].ToString()
                                       , dtPo.Rows[i]["PoNum"].ToString(), Math.Abs(ss), UserCode);

                                        //采购dt (  001号单据    数400) 
                                        DataRow newrow = DtEmail.NewRow();
                                        newrow["供应商编码"] = dr["供应商编码"].ToString();
                                        newrow["供应商名称"] = dtVendor.Rows[0]["VenName"].ToString();
                                        newrow["料品编码"] = dr["物料编码"].ToString();
                                        newrow["料品名称"] = dtInv.Rows[0]["INVName"].ToString();
                                        newrow["日期"] = dc.Caption;
                                        newrow["采购订单号"] = dtPo.Rows[i]["POCode"].ToString();
                                        newrow["采购订行号"] = dtPo.Rows[i]["Sequence"].ToString();
                                        newrow["单据数量"] = Math.Round(dtPo.Rows[i]["PoNum"].ToDouble(), 3) ;
                                        DtEmail.Rows.Add(newrow);

                                        dtPo.Rows.Remove(dtPo.Rows[i]);
                                    }
                                }
                                if (RequiredNum > 0)
                                {
                                    //标记给供应商发送邮件
                                    Flag = true;                          
                                }
                                //else
                                //{
                                    //  msg += "发给供应商告诉他发货.";
                                  //   SendEmailBySupplier(dr["供应商编码"].ToString(), dtVendor.Rows[0]["VenName"].ToString(), dc.Caption, dr[dc.Caption].ToString(), WorkPoint, dr["物料编码"].ToString(), dtInv.Rows[0]["INVName"].ToString());            
                                //}
                            }

                        }

                        if (Flag == true)
                        {
                            SendEmailByProcurement(dr["供应商编码"].ToString(), VenName,WorkPoint, dr["物料编码"].ToString(), INVName);
                          //  msg += "提示采购人员供应商:" + dr["供应商编码"].ToString() + "物料:" + dr["物料编码"].ToString() + "订单数量不足了.";
                        }

                    }

                }
                //在导入方法的最后去给供应商发送邮件



                count = SqlHelper.CmdExecuteNonQueryLi(sql);

                if (count > 0)
                {
                    var groupedData = DtEmail.Select("1=1").GroupBy(x => new { Sty1 = x["供应商编码"].ToString(), Sty2 = x["供应商名称"].ToString() });

                    foreach (var datas in groupedData)
                    {

                        string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + datas.Key.Sty1;
                        string filePath = ConvertToExcel(datas.ToList(), fileName);
                        SendEmailBySupplier(datas.Key.Sty1, datas.Key.Sty2, filePath, WorkPoint);
                    }
                    msg = "导入成功" + msg.TrimEnd(';');
                }
                else
                {
                    throw new Exception("无有效的导入数据！");
                }
                return msg;
            }
            catch (Exception ex)
            {
                throw new Exception("" + msg + "异常信息:" + ex.Message + "！");

            }

        }


        public static string ConvertToExcel(List<DataRow> dataRows, string fileName)
        {
             fileName = $"Excel_{fileName}.xlsx";

          
            string Paths = HttpContext.Current.Server.MapPath("/File/VendorsFileSendemail");    
            if (!Directory.Exists(Paths))
            {
                Directory.CreateDirectory(Paths);
            }
            string filePath = HttpContext.Current.Server.MapPath("/File/VendorsFileSendemail/"+ fileName);
            FileInfo fileInfo = new FileInfo(filePath);
            using (ExcelPackage package = new ExcelPackage(fileInfo))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
                // 将列名写入第一行
               for (int i = 0; i < dataRows[0].Table.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = dataRows[0].Table.Columns[i].ColumnName;
                }
                // 将数据写入Excel中
                for (int i = 0; i < dataRows.Count; i++)
                {
                    for (int j = 0; j < dataRows[i].Table.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1].Value = dataRows[i][j];
                    }
                }
                package.Save();
                // 返回文件的保存路径
                return fileInfo.FullName;

            }

        }




        #region 发邮件
        public static void SendEmail(string ConnectionString, string SendHost, int SendPort, string SendDisplayName, string SendAddress, string SendPassword, string TOAddress, string CCAddress, string Subject, bool IsBodyHtml, string Body, string File)
        {
            try
            {
                SmtpClient smtpClient = new SmtpClient
                {
                    //EnableSsl = false,
                    UseDefaultCredentials = false,
                    Host = SendHost,
                    Port = SendPort,
                    Credentials = new NetworkCredential(SendAddress, SendPassword)
                };

                System.Net.Mail.MailMessage mailMessage = new System.Net.Mail.MailMessage
                {
                    Subject = Subject,
                    SubjectEncoding = Encoding.GetEncoding("utf-8"),
                    BodyEncoding = Encoding.GetEncoding("utf-8"),
                    From = new MailAddress(SendAddress, SendDisplayName),
                    IsBodyHtml = IsBodyHtml,
                    Body = Body
                };
                string SUpFile = File;
                Attachment data = new Attachment(SUpFile, MediaTypeNames.Application.Octet);
                //附件资料
                System.Net.Mime.ContentDisposition disposition = data.ContentDisposition;
                disposition.CreationDate = System.IO.File.GetCreationTime(SUpFile);
                disposition.ModificationDate = System.IO.File.GetLastWriteTime(SUpFile);
                disposition.ReadDate = System.IO.File.GetLastAccessTime(SUpFile);
                //加入邮件附件
                mailMessage.Attachments.Add(data);
                string[] array = TOAddress.Split(new char[]
                {
                    ','
                });
                string[] array2 = array;
                for (int i = 0; i < array2.Length; i++)
                {
                    string text = array2[i];
                    if (!string.IsNullOrEmpty(text))
                    {
                        mailMessage.To.Add(text);
                    }
                }
                string[] array3 = CCAddress.Split(new char[]
                {
                    ','
                });
                array2 = array3;
                for (int i = 0; i < array2.Length; i++)
                {
                    string text2 = array2[i];
                    if (!string.IsNullOrEmpty(text2))
                    {
                        mailMessage.CC.Add(text2);
                    }
                }
                ServicePointManager.ServerCertificateValidationCallback = ((object obj, X509Certificate certificate, X509Chain chain, SslPolicyErrors errors) => true);
                smtpClient.Send(mailMessage);
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        #endregion


        public void SendEmailBySupplier(string VenCode,string VenName, string filePath, string WorkPoint)
        {

            string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
            if (MailOpen == "true")
            {
                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;
                string cVenCode = VenCode;
                string TOAddress = GetVendorEmail(cVenCode).TrimEnd(',');
                string CCAddress = "";
                string Subject = "有来自咖博士SRM平台新发布的采购订单信息";
                bool isBodyHtml = false;
                // string POCode = dr["POCode"].ToString();
                string NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
             //  double Nums = Math.Abs(Convert.ToDouble(Num));
                string body = VenName + "：";
                body += "\r\n";
                body += "                               您好！您有采购订单需要进行发货,详情请见邮件附件! ！";
                body += "\r\n";
                body += "                                                                                                                                           顺颂商祺！";
                body += "\r\n";
                body += "                                                                                                                                           苏州咖博士咖啡系统科技有限公司";
                body += "\r\n";
                body += "                                                                                                                                           " + NowDate;

                if (string.IsNullOrWhiteSpace(TOAddress))
                {
                    throw new Exception("未维护邮箱，请到ERP维护供应商邮箱地址！");
                }
                string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                //    ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body);
                SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body, filePath);
            }

        }



        public void SendEmailByProcurement(string VenCode, string VenName, string WorkPoint, string InvCode, string InvName)
        {

            string MailOpen = ConfigurationManager.ConnectionStrings["MailOpen"].ConnectionString;
            if (MailOpen == "true")
            {
                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;
                string cVenCode = VenCode;
                string TOAddress = GetVendorEmail(cVenCode).TrimEnd(',');
                string CCAddress = "";
                string Subject = "有来自咖博士SRM平台采购订单信息";
                bool isBodyHtml = false;
                // string POCode = dr["POCode"].ToString();
                string NowDate = DateTime.Now.GetDateTimeFormats('D')[0].ToString();
                string body = "采购人员：";
                body += "\r\n";
                body += "                               您好！" + VenName + "供应商," + InvCode + "物料的订单数量不满足本次的需求数量,请登陆SRM系统查看相关信息 ！";
                body += "\r\n";
                body += "                                                                                                                                           顺颂商祺！";
                body += "\r\n";
                body += "                                                                                                                                           苏州咖博士咖啡系统科技有限公司";
                body += "\r\n";
                body += "                                                                                                                                           " + NowDate;

                if (string.IsNullOrWhiteSpace(TOAddress))
                {
                    throw new Exception("未维护邮箱，请到ERP维护供应商邮箱地址！");
                }
                string StrConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
                ICSSendMail.SendEmail(StrConn, SendHost, SendPort, SendDisplayName, SendAddress, SendPassword, TOAddress, CCAddress, Subject, isBodyHtml, body);

            }

        }





        public string GetVendorEmail(string VenCode)
        {
            string sql = " SELECT F_Email FROM dbo.Sys_SRM_User WHERE F_VenCode='" + VenCode + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            string Email = string.Empty;
            foreach (DataRow dr in dt.Rows)
            {
                Email += dr["F_Email"].ToString() + ",";
            }
            return Email;
        }





        public string SaveAndUpdate(string ICSInvImport)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string msg = "";
            string sql = string.Empty;
            JArray res = (JArray)JsonConvert.DeserializeObject(ICSInvImport);
            foreach (var item in res)
            {
                JObject jo = (JObject)item;

                    sql += @"IF EXISTS(SELECT a.PDate FROM ICSInvCodeImport a WHERE a.Code='{0}' AND a.PDate='{3}')
                        BEGIN
                                UPDATE ICSInvCodeImport  SET Quantity='{2}'
                                WHERE Code='{0}' AND PDate='{3}'
                        END
                        ELSE
                        BEGIN
						 insert into ICSInvCodeImport(ID, VenCode, VenName, InvCode, InvName, 
                            PDate, cModifyPerson, dModifyDate, SendDate, WorkPoint, Quantity, Code)
							values( newid(),'{4}','{5}','{6}','{7}','{3}','{8}',getdate(),'',{1},'{2}','{0}' )
                        END                                                       ";
                sql = string.Format(sql, jo["Code"].ToString(), WorkPoint,jo["DateColValue"].ToString(),Convert.ToDateTime(jo["DateCol"]).ToString("yyyy-MM-dd"), 
                    jo["VenCode"].ToString(), jo["VenName"].ToString(), jo["InvCode"].ToString(), jo["InvName"].ToString(), MUSER);
            }
            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
                else
                {
                    msg = "保存失败";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }

            return msg;
        }





        public string Delete(string ICSInvImport)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string msg = "";
            string sql = string.Empty;
            JArray res = (JArray)JsonConvert.DeserializeObject(ICSInvImport);
            foreach (var item in res)
            {
                JObject jo = (JObject)item;

                sql += @"delete ICSInvCodeImport where Code='{0}' and PDate>='{1}' and PDate<='{2}'";
                sql = string.Format(sql, jo["Code"].ToString(), jo["DateColStart"].ToString(), jo["DateColEnd"].ToString() );
            }
            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
                else
                {
                    msg = "删除失败";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }

            return msg;
        }




        public string KBSTZ(string ICSInvImport)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string msg = "";
            string sql = string.Empty;

            try
            {
                DataTable dt = JsonConvert.DeserializeObject<DataTable>(ICSInvImport);
                var distinctRows = dt.AsEnumerable()
                     .Select(row => new
                     {
                         供应商编号 = row.Field<string>("供应商编号"),
                         供应商名称 = row.Field<string>("供应商名称")
                     })
                     .Distinct();


                foreach (var rows in distinctRows)
                {
                    var filteredRows = dt.AsEnumerable()
                     .Where(r => r.Field<string>("供应商编号") == rows.供应商编号 &&
                                   r.Field<string>("供应商名称") == rows.供应商名称);

                    // 将 IEnumerable<DataRow> 转换为 DataTable
                    DataTable filteredDataTable = filteredRows.CopyToDataTable();

                    string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + rows.供应商编号;
                    string filePath = ConvertToExcel(filteredRows.ToList(), fileName);
                     SendEmailBySupplier(rows.供应商编号.ToString(), rows.供应商名称, filePath, WorkPoint);

                }

            }

            catch (Exception ex)
            {
                msg = ex.Message;
            }

            return msg;
        }



























    }
}