/// <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);
}
