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.
843 lines
39 KiB
843 lines
39 KiB
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;
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
}
|