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());
        }
Beispiel #2
0
        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();
//                        }
//                    }
//                }


//            }
        }