/// <summary> /// get sap A501 & A306 ladder price /// </summary> /// <returns></returns> private SapMiddleData GetSapPriceList() { //抓全部資料,由於不是參數化查詢,第二個參數傳null string _SqlA501 = @"select a.KUNNR ,d.name1,a.ZZENDCUST,e.name1 as zname1,a.MATNR ,b.KONWA,c.KSTBM,c.KBETR,c.KLFN1 ,a.KNUMH ,b.KPEIN ,a.DATAB ,a.kschl from sapabap1.A501 a join sapabap1.KONP b on a.KNUMH=b.KNUMH join sapabap1.KONM c on a.KNUMH=c.KNUMH join sapabap1.kna1 d on a.kunnr=d.kunnr join sapabap1.kna1 e on a.ZZENDCUST=e.kunnr where DATBI='99991231' "; if (txtDate.Text != "") { _SqlA501 += " and DATAB > " + txtDate.Text; } _SqlA501 += " order by a.knumh,a.KUNNR,a.ZZENDCUST,c.KLFN1,a.matnr"; DataTable dtA501 = OdbcHelper.GetDataTableText(_SqlA501, null); string _SqlA306 = @"select a.KUNNR ,d.name1, a.MATNR, b.KONWA ,c.KSTBM,c.KBETR,c.KLFN1 ,a.KNUMH ,b.KPEIN,a.DATAB ,a.kschl from sapabap1.A305 a join sapabap1.KONP b on a.KNUMH=b.KNUMH join sapabap1.KONM c on a.KNUMH=c.KNUMH join sapabap1.kna1 d on a.kunnr=d.kunnr where DATBI='99991231' "; if (txtDate.Text != "") { _SqlA306 += " and DATAB > " + txtDate.Text; } _SqlA306 += " order by a.knumh,a.KUNNR,a.matnr,c.KLFN1"; DataTable dtA306 = OdbcHelper.GetDataTableText(_SqlA306, null); dtA501.Merge(dtA306); SapPrice _SapPrice = new SapPrice(); SapMiddleData _SapMiddleData = _SapPrice.Parse(dtA501); return(_SapMiddleData); }
public bool BatchInsert(SapMiddleData sapMiddleData) { List <string> _ListError = new List <string>(); _ListError = miltiCreate(sapMiddleData); return(_ListError.Count > 1 ? false : true); }
public bool PostSapMiddleData(SapMiddleData sapMiddleData) { bool _OK = false; _OK = this._sapEDIService.BatchInsert(sapMiddleData); return(_OK); }
private SapMiddleData initSapMiddleData() { SapMiddleData _SapMiddleData = GetSapPriceList(); IList <QuotationTax> _QuotationTaxList = GetQuotationTaxList(); //extra process CNY PR00 & PR01 price SapMiddleData _SapMiddleData_CNY = ProcessChinaPrice(_SapMiddleData, _QuotationTaxList); _SapMiddleData.PriceList.AddRange(_SapMiddleData_CNY.PriceList); return(_SapMiddleData); }
private void Button2_Click(object sender, EventArgs e) { Stopwatch sw = new Stopwatch(); sw.Reset(); sw.Start(); SapMiddleData _SapMiddleData = initSapMiddleData(); sw.Stop(); lbSapPrice.Text = sw.ElapsedMilliseconds.ToString() + "毫秒"; // lbCount.Text = _SapMiddleData.PriceList.Count().ToString()+"筆"; }
private List <string> miltiCreate(SapMiddleData sapMiddleData) { string type = ""; List <string> _ListError = new List <string>(); switch (type) { default: _ListError = this._sap_PriceListService.MiltiCreate(sapMiddleData.PriceList); break; } return(_ListError); }
/// <summary> /// use adatper factory to produce object /// </summary> /// <param name="filePath"></param> private SapMiddleData parse(string filePath) { SapBase _Base = _sapAdapterFactory.CreateSapAdapter(""); _Base.PriceType = HttpContext.Current.Request["PriceType"].ToString(); SapMiddleData _SapMiddleData = _Base.Parse(filePath); //if (_Base.ListError.Count() != 0) //{ // string _Error = string.Join("\r\n", _Base.ListError.ToArray()); // throw new Exception(_Error); //} return(_SapMiddleData); }
/// <summary> /// according to SapMiddleData type batch insert data /// </summary> /// <param name="type">SapMiddleData type</param> /// <param name="sapMiddleData"></param> /// <param name="tableName"></param> /// <returns></returns> private List <string> miltiCreate(string type, SapMiddleData sapMiddleData, out string tableName) { tableName = ""; List <string> _ListError = new List <string>(); switch (type) { default: _ListError = this._sap_PriceListService.MiltiCreate(sapMiddleData.PriceList); tableName = "SAP_PriceList"; break; } return(_ListError); }
private void BtnExcel_Click(object sender, EventArgs e) { Stopwatch sw = new Stopwatch(); sw.Reset(); sw.Start(); txtDate.Text = ""; SapMiddleData _SapMiddleData = initSapMiddleData(); //建立 xlxs 轉換物件 XSLXHelper helper = new XSLXHelper(); List <SapPriceListExcel> _SapPriceListExcelList = SetSapPriceListExcel(_SapMiddleData.PriceList); var xlsx = helper.Export(_SapPriceListExcelList); xlsx.SaveAs(filepathNoExt + "_SapPrice" + ".xlsx"); sw.Stop(); MessageBox.Show(sw.ElapsedMilliseconds.ToString() + "毫秒"); }
private void BtnPostSapPrice_Click(object sender, EventArgs e) { SapMiddleData _SapMiddleData = initSapMiddleData(); using (WebClient webClient = new WebClient()) { // 從 url 讀取資訊至 stream //using (Stream stream = webClient.OpenRead("http://localhost:52006/api/SAP_PriceList/1")) //using (Stream stream = webClient.OpenRead("http://localhost:52006/api/Token/1")) // 指定 WebClient 編碼 webClient.Encoding = Encoding.UTF8; // 指定 WebClient 的 Content-Type header webClient.Headers.Add(HttpRequestHeader.ContentType, "application/json"); // 指定 WebClient 的 authorization header //webClient.Headers.Add("authorization", "token {apitoken}"); // 將 data 轉為 json string json = JsonConvert.SerializeObject(_SapMiddleData); // 執行 post 動作 var result = webClient.UploadString(_ConnectionString + "SAP_PriceList", json); } }
/// <summary> /// 1. upload file (excel) /// 2. parse excel column value /// 3. batch insert data /// 4. copy file to success directory /// 5. insert uploadlog /// </summary> /// <returns></returns> public List <string> UploadFile() { List <string> _ListError = new List <string>(); string _FilePath = this._commonFileService.Upload(); int _LoginID = HttpContext.Current.Request["LoginID"].ToString() != "" ? Convert.ToInt32(HttpContext.Current.Request["LoginID"].ToString()) : 0; string _TableName = ""; SapMiddleData _SapMiddleData = parse(_FilePath); _ListError = miltiCreate("", _SapMiddleData, out _TableName); bool _Success = _ListError.Count > 1 ? false : true; string _ServerFileName = this._commonFileService.SaveToSuccess(_LoginID, _FilePath, _TableName); string _UploadLogError = this._commonFileService.InsertUploadLog(_LoginID, _FilePath, _TableName, _Success, _ServerFileName); if (_UploadLogError != "") { _ListError.Add(_UploadLogError); } if (_ListError.Count > 0) { _ListError[0] = _ListError[0] + "ms"; } return(_ListError); }
public virtual SapMiddleData Parse(string _FilePath) { SapMiddleData _SapMiddleData = new SapMiddleData(); return(_SapMiddleData); }
private SapMiddleData ProcessChinaPrice(SapMiddleData sapMiddleData, IList <QuotationTax> quotationTaxList) { bool _Has = false; int _Tax = 0; bool _AddNoTaxPrice = true; List <SAP_PriceList> _Sap_PriceList_CNY = sapMiddleData.PriceList.Where(x => x.Currency == "CNY").ToList(); List <SAP_PriceList> _Sap_PriceList_PR00 = new List <SAP_PriceList>(); SapMiddleData _SapMiddleData = new SapMiddleData(); foreach (SAP_PriceList s in _Sap_PriceList_CNY) { _AddNoTaxPrice = s.TaxType == "PR01" ? true : false; //if no PR00 price _Has = IsHasNoTax(_Sap_PriceList_CNY, s); if (!_Has) { _Tax = _AddNoTaxPrice ? GetTax(quotationTaxList, s) : 13; SAP_PriceList _Sap_Price = new SAP_PriceList(); _Sap_Price.TaxRate = _Tax; _Tax += 100; _Sap_Price.CustomerId = s.CustomerId; _Sap_Price.CustomerName = s.CustomerName; _Sap_Price.EndCustomerId = s.EndCustomerId; _Sap_Price.EndCustomerName = s.EndCustomerName; _Sap_Price.PartNumber = s.PartNumber; _Sap_Price.Currency = s.Currency; _Sap_Price.MOQ1 = s.MOQ1; if (s.MOQ1 != null) { _Sap_Price.Price1 = _AddNoTaxPrice ? s.Price1 * 100 / _Tax : s.Price1 * _Tax / 100; _Sap_Price.Price1 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price1), 2)); } _Sap_Price.MOQ2 = s.MOQ2; if (s.MOQ2 != null) { _Sap_Price.Price2 = _AddNoTaxPrice ? s.Price2 * 100 / _Tax : s.Price2 * _Tax / 100; _Sap_Price.Price2 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price2), 2)); } _Sap_Price.MOQ3 = s.MOQ3; if (s.MOQ3 != null) { _Sap_Price.Price3 = _AddNoTaxPrice ? s.Price3 * 100 / _Tax : s.Price3 * _Tax / 100; _Sap_Price.Price3 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price3), 2)); } _Sap_Price.MOQ4 = s.MOQ4; if (s.MOQ4 != null) { _Sap_Price.Price4 = _AddNoTaxPrice ? s.Price4 * 100 / _Tax : s.Price4 * _Tax / 100; _Sap_Price.Price4 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price4), 2)); } _Sap_Price.MOQ5 = s.MOQ5; if (s.MOQ5 != null) { _Sap_Price.Price5 = _AddNoTaxPrice ? s.Price5 * 100 / _Tax : s.Price5 * _Tax / 100; _Sap_Price.Price5 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price5), 2)); } _Sap_Price.MOQ6 = s.MOQ6; if (s.MOQ6 != null) { _Sap_Price.Price6 = _AddNoTaxPrice ? s.Price6 * 100 / _Tax : s.Price6 * _Tax / 100; _Sap_Price.Price6 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price6), 2)); } _Sap_Price.MOQ7 = s.MOQ7; if (s.MOQ7 != null) { _Sap_Price.Price7 = _AddNoTaxPrice ? s.Price7 * 100 / _Tax : s.Price7 * _Tax / 100; _Sap_Price.Price7 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price7), 2)); } _Sap_Price.MOQ8 = s.MOQ8; if (s.MOQ8 != null) { _Sap_Price.Price8 = _AddNoTaxPrice ? s.Price8 * 100 / _Tax : s.Price8 * _Tax / 100; _Sap_Price.Price8 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price8), 2)); } _Sap_Price.MOQ9 = s.MOQ9; if (s.MOQ9 != null) { _Sap_Price.Price9 = _AddNoTaxPrice ? s.Price9 * 100 / _Tax : s.Price9 * _Tax / 100; _Sap_Price.Price9 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price9), 2)); } _Sap_Price.MOQ10 = s.MOQ10; if (s.MOQ10 != null) { _Sap_Price.Price10 = _AddNoTaxPrice ? s.Price10 * 100 / _Tax : s.Price10 * _Tax / 100; _Sap_Price.Price10 = Convert.ToDecimal(System.Math.Round(Convert.ToDouble(_Sap_Price.Price10), 2)); } _Sap_Price.Date = DateTime.Now.ToString("yyyyMMdd"); _Sap_Price.Rate = s.Rate; _Sap_Price.Per = s.Per; _Sap_Price.TaxType = _AddNoTaxPrice ? "PR00" : "PR01"; _Sap_Price.TaxRate = _Tax - 100; _Sap_Price.EffectiveDate = s.EffectiveDate; _Sap_Price.InsertSapPrice = false; _Sap_PriceList_PR00.Add(_Sap_Price); } } _SapMiddleData.PriceList = _Sap_PriceList_PR00; //_SapMiddleData.PriceList = _Sap_PriceList_PR00.Where(x => x.TaxRate == 13).ToList(); return(_SapMiddleData); }
public override SapMiddleData Parse(string filePath) { List <SapRate> _SapRateList = initSapRate(); SapMiddleData _SapMiddleData = new SapMiddleData(); List <SAP_PriceList> _SAP_PriceList = new List <SAP_PriceList>(); IQueryable <PriceList> _PriceList; LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath); _Excel.AddMapping <PriceList>(d => d.KUNNR, "KUNNR"); _Excel.AddMapping <PriceList>(d => d.NAME1, "NAME1"); if (base.PriceType == "終端客戶價格表A501") { _Excel.AddMapping <PriceList>(d => d.ZZENDCUST, "ZZENDCUST"); _Excel.AddMapping <PriceList>(d => d.ZNAME1, "ZNAME1"); } _Excel.AddMapping <PriceList>(d => d.MATNR, "MATNR"); _Excel.AddMapping <PriceList>(d => d.KONWA, "KONWA"); _Excel.AddMapping <PriceList>(d => d.KSTBM, "KSTBM"); _Excel.AddMapping <PriceList>(d => d.KBETR, "KBETR"); _Excel.AddMapping <PriceList>(d => d.KLFN1, "KLFN1"); _Excel.AddMapping <PriceList>(d => d.KPEIN, "KPEIN"); _Excel.AddMapping <PriceList>(d => d.KNUMH, "KNUMH"); _Excel.AddMapping <PriceList>(d => d.KSCHL, "KSCHL"); // every time get first sheet data _PriceList = from x in _Excel.Worksheet <PriceList>(0) select x; int _Start = 1, _Order = 1; string _KNUMH = ""; SAP_PriceList _Sap_Price = new SAP_PriceList(); foreach (PriceList c in _PriceList) { try { int _KSTBM = 0; decimal?_KBETR = 0, _Price = 0, _UnitPrice; if (c.KNUMH != _KNUMH) { _Order = 1; if (_Start != 1) { _SAP_PriceList.Add(_Sap_Price); } _Sap_Price = new SAP_PriceList(); _KNUMH = c.KNUMH;//group price _Sap_Price.CustomerId = c.KUNNR; _Sap_Price.CustomerName = c.NAME1; _Sap_Price.EndCustomerId = c.ZZENDCUST; _Sap_Price.EndCustomerName = c.ZNAME1; _Sap_Price.PartNumber = c.MATNR; _Sap_Price.Currency = c.KONWA; _Sap_Price.Date = DateTime.Now.Date.ToString("yyyyMMdd"); _Sap_Price.Rate = Convert.ToDecimal(_SapRateList.Where(x => x.Currency == c.KONWA.Trim()).First().Rate); _Sap_Price.Per = c.KPEIN == null ? 0 : int.Parse(c.KPEIN, System.Globalization.NumberStyles.AllowThousands); _Sap_Price.TaxType = c.KSCHL; } _KSTBM = c.KSTBM == null ? 0 : int.Parse(c.KSTBM, System.Globalization.NumberStyles.AllowThousands); if (c.KONWA == "TWD" || c.KONWA == "JPY") { _KBETR = Convert.ToDecimal(c.KBETR) * 100; } else { _KBETR = Convert.ToDecimal(c.KBETR); } _UnitPrice = Convert.ToDecimal(_KBETR / _Sap_Price.Per); _Price = _UnitPrice * _Sap_Price.Rate; if (_Order == 1) { _Sap_Price.MOQ1 = _KSTBM; _Sap_Price.Price1 = _Price; } else if (_Order == 2) { _Sap_Price.MOQ2 = _KSTBM; _Sap_Price.Price2 = _Price; } else if (_Order == 3) { _Sap_Price.MOQ3 = _KSTBM; _Sap_Price.Price3 = _Price; } else if (_Order == 4) { _Sap_Price.MOQ4 = _KSTBM; _Sap_Price.Price4 = _Price; } else if (_Order == 5) { _Sap_Price.MOQ5 = _KSTBM; _Sap_Price.Price5 = _Price; } else if (_Order == 6) { _Sap_Price.MOQ6 = _KSTBM; _Sap_Price.Price6 = _Price; } else if (_Order == 7) { _Sap_Price.MOQ7 = _KSTBM; _Sap_Price.Price7 = _Price; } else if (_Order == 8) { _Sap_Price.MOQ8 = _KSTBM; _Sap_Price.Price8 = _Price; } else if (_Order == 9) { _Sap_Price.MOQ9 = _KSTBM; _Sap_Price.Price9 = _Price; } else if (_Order == 10) { _Sap_Price.MOQ10 = _KSTBM; _Sap_Price.Price10 = _Price; } _Order++; if (_Start == _PriceList.Count()) { _SAP_PriceList.Add(_Sap_Price); } } catch (Exception ex) { string _c = JsonConvert.SerializeObject(c); this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c); } _Start++; } _SapMiddleData.PriceList = _SAP_PriceList; return(_SapMiddleData); }
public SapMiddleData Parse(DataTable dtPrice) { List <SapRate> _SapRateList = initSapRate(); SapMiddleData _SapMiddleData = new SapMiddleData(); List <SAP_PriceList> _SAP_PriceList = new List <SAP_PriceList>(); int _Start = 1, _Order = 1; string _KNUMH = ""; SAP_PriceList _Sap_Price = new SAP_PriceList(); for (int i = 0; i < dtPrice.Rows.Count; i++) { try { int _KSTBM = 0; decimal?_KBETR = 0, _Price = 0, _UnitPrice; if (dtPrice.Rows[i]["KNUMH"].ToString() != _KNUMH) { _Order = 1; if (_Start != 1) { _SAP_PriceList.Add(_Sap_Price); } _Sap_Price = new SAP_PriceList(); _KNUMH = dtPrice.Rows[i]["KNUMH"].ToString();//group price _Sap_Price.CustomerId = dtPrice.Rows[i]["KUNNR"].ToString(); _Sap_Price.CustomerName = dtPrice.Rows[i]["NAME1"].ToString(); _Sap_Price.EndCustomerId = dtPrice.Rows[i]["ZZENDCUST"].ToString(); _Sap_Price.EndCustomerName = dtPrice.Rows[i]["ZNAME1"].ToString(); _Sap_Price.PartNumber = dtPrice.Rows[i]["MATNR"].ToString(); _Sap_Price.Currency = dtPrice.Rows[i]["KONWA"].ToString(); _Sap_Price.Date = DateTime.Now.Date.ToString("yyyyMMdd"); _Sap_Price.Rate = Convert.ToDecimal(_SapRateList.Where(x => x.Currency == _Sap_Price.Currency.Trim()).First().Rate); _Sap_Price.Per = dtPrice.Rows[i]["KPEIN"].ToString() == "" ? 0 : int.Parse(dtPrice.Rows[i]["KPEIN"].ToString(), System.Globalization.NumberStyles.AllowThousands); _Sap_Price.TaxType = dtPrice.Rows[i]["KSCHL"].ToString(); _Sap_Price.EffectiveDate = dtPrice.Rows[i]["DATAB"].ToString(); _Sap_Price.InsertSapPrice = true; } _KSTBM = dtPrice.Rows[i]["KSTBM"].ToString() == "" ? 0 : int.Parse(dtPrice.Rows[i]["KSTBM"].ToString(), System.Globalization.NumberStyles.Float); string _KONWA = dtPrice.Rows[i]["KONWA"].ToString(); if (_KONWA == "TWD" || _KONWA == "JPY") { _KBETR = Convert.ToDecimal(dtPrice.Rows[i]["KBETR"].ToString()) * 100; } else { _KBETR = Convert.ToDecimal(dtPrice.Rows[i]["KBETR"].ToString()); } _UnitPrice = Convert.ToDecimal(_KBETR / _Sap_Price.Per); _Price = _UnitPrice * _Sap_Price.Rate; if (_Order == 1) { _Sap_Price.MOQ1 = _KSTBM; _Sap_Price.Price1 = _Price; } else if (_Order == 2) { _Sap_Price.MOQ2 = _KSTBM; _Sap_Price.Price2 = _Price; } else if (_Order == 3) { _Sap_Price.MOQ3 = _KSTBM; _Sap_Price.Price3 = _Price; } else if (_Order == 4) { _Sap_Price.MOQ4 = _KSTBM; _Sap_Price.Price4 = _Price; } else if (_Order == 5) { _Sap_Price.MOQ5 = _KSTBM; _Sap_Price.Price5 = _Price; } else if (_Order == 6) { _Sap_Price.MOQ6 = _KSTBM; _Sap_Price.Price6 = _Price; } else if (_Order == 7) { _Sap_Price.MOQ7 = _KSTBM; _Sap_Price.Price7 = _Price; } else if (_Order == 8) { _Sap_Price.MOQ8 = _KSTBM; _Sap_Price.Price8 = _Price; } else if (_Order == 9) { _Sap_Price.MOQ9 = _KSTBM; _Sap_Price.Price9 = _Price; } else if (_Order == 10) { _Sap_Price.MOQ10 = _KSTBM; _Sap_Price.Price10 = _Price; } _Order++; if (_Start == dtPrice.Rows.Count) { _SAP_PriceList.Add(_Sap_Price); } } catch (Exception ex) { this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:"); } _Start++; } _SapMiddleData.PriceList = _SAP_PriceList; return(_SapMiddleData); }