private string VaildateSku(List <Item> list)
{
StringBuilder sb = new StringBuilder();
foreach (Item item in list)
{
if (!VaildationSKU(item.ALTERNATEPART, item.VENDORCODE))
{
sb.Append(string.Format("SKU:{0} STORE:{1} 主数据没有维护<br>", item.ALTERNATEPART, item.VENDORCODE));
MailClient.WriteLog(string.Format("SKU:{0} STORE:{1} 主数据没有维护<br>", item.ALTERNATEPART, item.VENDORCODE));
}
}
return(sb.ToString());
}
public void Read()
{
using (FileStream stream = File.Open(_excelFile, FileMode.Open, FileAccess.Read)) {
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
DataTable dt = result.Tables[0];
excelReader.Close();
stream.Close();
if (result.Tables.Count > 0 && dt.Rows.Count > 0)
{
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
conn.Open();
SqlTransaction trx = conn.BeginTransaction();
try
{
foreach (DataRow dr in dt.Rows)
{
object year = dr["财务年"];
if (year == null || string.IsNullOrEmpty(year.ToString()))
{
continue;
}
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trx;
cmd.CommandText = string.Format(@"INSERT INTO [{7}].[STXCALENDAR]
([WHSEID]
,[FISCALYEAR]
,[QUARTER]
,[WEEK]
,[FROM_DATE]
,[TO_DATE]
,[ADDWHO]
)
VALUES
('{0}'
,'{1}'
,'{2}'
,'{3}'
,'{4}'
,'{5}'
,'{6}'
)"
, _warehouse
, dr["财务年"]
, dr["季度"]
, dr["第几周"]
, dr["开始日期"]
, dr["结束日期"]
, _warehouse
, _warehouse
);
//Site Prime Part Alternate Part Vendor Number Allocation Percentage Start Date Active End Date Active Planner Code Non ASIC Indicator
cmd.ExecuteNonQuery();
}
trx.Commit();
conn.Close();
MailClient.SendCalendarNotificationMail(dt, _excelFile, string.Empty);
}
catch (Exception e)
{
Console.WriteLine(e);
trx.Rollback();
conn.Close();
MailClient.SendCalendarNotificationMail(dt, _excelFile, e.Message);
}
}
}
}
}
public void Read()
{
List <Item> list = ReadCSV();
if (list.Count > 0)
{
string result = VaildateSku(list);
if (!string.IsNullOrEmpty(result))
{
MailClient.SendNoSKUAllocNotificationMail(result, _excelFile, "以下SKU没有找到主数据-请先维护好主数据后再重新导入");
return;
}
using (SqlConnection conn = new SqlConnection(_connectionstring))
{
conn.Open();
SqlTransaction trx = conn.BeginTransaction();
try
{
foreach (Item item in list)
{
string site = item.SITE;
if (site == null || string.IsNullOrEmpty(site.ToString()))
{
continue;
}
#region sqlcmd
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trx;
cmd.CommandText = string.Format(@"INSERT INTO [{0}].[STXALLOCATION]
([WHSEID]
,[PLANNERCODE]
,[STXAGRP]
,[SITE]
,[TYPE]
,[PRISKU]
,[SKU]
,[STORERKEY]
,[COMMODITYCLASS]
,[ALLQTY]
,[STARTDATE]
,[ENDDATE]
,[ADDDATE]
,[ADDWHO]
,[NOTES])
VALUES
('{0}'
,'{1}'
,'{14}'
,'{2}'
,'{3}'
,'{4}'
,'{5}'
,'{6}'
,'{7}'
,'{8}'
,'{9}'
,'{10}'
,'{11}'
,'{12}'
,'{13}')"
, _warehouse
, item.PLANNERCODE
, item.SITE
, GetAllocType(_excelFile)
, item.PRIMEPART
, item.ALTERNATEPART
, item.VENDORCODE
, item.FLAG
, item.PERCENTAGE
, item.STARTDATE
, item.ENDDATE
, DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
, _warehouse
, ""
, _fileName
);
Console.WriteLine("reading....." + item.ToString());
#endregion
//Site Prime Part Alternate Part Vendor Number Allocation Percentage Start Date Active End Date Active Planner Code Non ASIC Indicator
cmd.ExecuteNonQuery();
}
trx.Commit();
conn.Close();
MailClient.SendAllocNotificationMail(list, _excelFile, string.Empty);
}
catch (Exception e)
{
MailClient.SendAllocNotificationMail(list, _excelFile, e.Message);
MailClient.WriteLog("配额导入异常" + e.Message);
Console.WriteLine(e);
trx.Rollback();
conn.Close();
}
}
}
// using (FileStream stream = File.Open(_excelFile, FileMode.Open, FileAccess.Read)) {
// IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
// excelReader.IsFirstRowAsColumnNames = true;
// DataSet result = excelReader.AsDataSet();
// DataTable dt = null;
// excelReader.Close();
// stream.Close();
// if (result.Tables.Count > 0 && result.Tables[0].Rows.Count>0)
// {
// try
// {
// //DataTable notsku = VaildateTable(result.Tables[0]);
// //if (notsku.Rows.Count > 0)
// //{
// // MailClient.SendNoSKUAllocNotificationMail(notsku, _excelFile, "以下SKU没有找到主数据-请先维护好主数据后再重新导入");
// // return;
// //}
// }
// catch (Exception e)
// {
// Console.WriteLine(e);
// }
// using (SqlConnection conn = new SqlConnection(_connectionstring))
// {
// conn.Open();
// SqlTransaction trx = conn.BeginTransaction();
// try
// {
// dt = result.Tables[0];
// foreach (DataRow dr in dt.Rows)
// {
// object site = dr["Site"];
// if (site == null || string.IsNullOrEmpty(site.ToString()))
// {
// continue;
// }
// #region sqlcmd
// SqlCommand cmd = conn.CreateCommand();
// cmd.Transaction = trx;
// cmd.CommandText =string.Format(@"INSERT INTO [{0}].[STXALLOCATION]
// ([WHSEID]
// ,[PLANNERCODE]
// ,[STXAGRP]
// ,[SITE]
// ,[TYPE]
// ,[PRISKU]
// ,[SKU]
// ,[STORERKEY]
// ,[COMMODITYCLASS]
// ,[ALLQTY]
// ,[STARTDATE]
// ,[ENDDATE]
// ,[ADDDATE]
// ,[ADDWHO]
// ,[NOTES])
// VALUES
// ('{0}'
// ,'{1}'
// ,'{14}'
// ,'{2}'
// ,'{3}'
// ,'{4}'
// ,'{5}'
// ,'{6}'
// ,'{7}'
// ,'{8}'
// ,'{9}'
// ,'{10}'
// ,'{11}'
// ,'{12}'
// ,'{13}')"
// , _warehouse
// , dr["Planner Code"]
// , dr["SITE"]
// , GetAllocType(_excelFile)
// , dr["Prime Part"]
// , dr["Alternate Part"]
// , dr["Vendor Number"]
// , dr["Non ASIC Indicator"]
// , dr["Allocation Percentage"]
// , dr["Start Date Active"]
// , dr["End Date Active"]
// , DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
// , _warehouse
// , ""
// , _fileName
// );
// Console.WriteLine("reading....." + dr["Planner Code"].ToString());
// #endregion
// //Site Prime Part Alternate Part Vendor Number Allocation Percentage Start Date Active End Date Active Planner Code Non ASIC Indicator
// cmd.ExecuteNonQuery();
// }
// trx.Commit();
// conn.Close();
// MailClient.SendAllocNotificationMail(dt, _excelFile,string.Empty);
// }
// catch (Exception e)
// {
// MailClient.SendAllocNotificationMail(dt, _excelFile, e.Message);
// Console.WriteLine(e);
// trx.Rollback();
// conn.Close();
// }
// }
// }
// }
}