Epicor新增价格表 C#

转载请注明出处，联系我: t39q@163.com
本人热衷于数据库技术及算法的研究，志同道合之士， 欢迎探讨

public static void GetMtlPriceList(string Part, string PUM, int ActualVendorNum,string CurrencyCode, decimal BaseUnitPrice, string FromCompany, string ToCompany,DateTime EffectiveDate, DateTime EffectiveDate2){Session session;StringBuilder sql = new StringBuilder();sql.Clear();sql.Append( \"select VendorID from erp.Vendor where VendorNum=\" + ActualVendorNum + \" and company=\'\" + ToCompany + \"\'\");string ActualVendorID = Util.DBHelper.ExecuteScalar(sql.ToString()).ToString();session = Util.EpicorHelper.GetSession();session.CompanyID = FromCompany;//FromCompany作為傳入參數 轉換session到FromCompany進行操作，而不能再當前Company操作VendPartAdapter a = new VendPartAdapter(new ILauncher(session));a.BOConnect();sql.Clear();sql.Append(\"select VendorNum from erp.Vendor where VendorID=\'\" + ToCompany + \"\' and company=\'\" + FromCompany + \"\'\");int VendorNum = (int)Util.DBHelper.ExecuteScalar(sql.ToString());a.GetNewVendPart(Part, \"\", PUM, VendorNum);DataRow NewVendPart = a.VendPartData.VendPart[a.VendPartData.VendPart.Rows.Count - 1];//sql = \"select part_increase_percent_c from part where company=\'\" + Util.EpicorHelper.GetSession().CompanyID + \"\' and partNum=\'\" + Part + \"\'\";sql.Clear();sql.Append(\"select InternalPercent from [InternalPartProfitPercent] where part=\'\" + Part + \"\' and \");sql.Append(\" FromCompany =\'\" + FromCompany + \"\' and ToCompany=\'\" + ToCompany + \"\' and Type=1\" );DataTable dt = new DataTable();dt.Clear();dt = Util.DBHelper.ExecuteDt(sql.ToString());decimal part_increase_percent_c = 0;if (dt.Rows.Count > 0){part_increase_percent_c = (decimal)dt.Rows[0][0];}//拿到1000公司最新的匯率//sql2.Append(\" select CurrentRate from  erp.CurrExRate where TargetCurrCode=\'\"+ CurrencyCode + \"\' AND SourceCurrCode=\'HKD\' \");//sql2.Append(\" and EffectiveDate=(select max(EffectiveDate) from erp.CurrExRate where TargetCurrCode=\'\" + CurrencyCode + \"\' \");//sql2.Append(\" AND SourceCurrCode=\'HKD\' and company=\'\"+ToCompany+ \"\') and company=\'\" + ToCompany + \"\'\");decimal CurrentRate = 1;//幣別相同的時候匯率默認為1，不同的時候再重新取。if (CurrencyCode != \"HKD\"){sql.Clear();sql.Append(\" SELECT cer.CurrentRate FROM erp.CurrExRate cer \");sql.Append(\" LEFT JOIN erp.CurrRateGrp crg ON (cer.Company = crg.Company AND cer.RateGrpCode = crg.RateGrpCode) \");sql.Append(\" WHERE cer.EffectiveDate=( \");sql.Append(\" select max(EffectiveDate) from  erp.CurrExRate cer \");sql.Append(\" LEFT JOIN  erp.CurrRateGrp crg ON (cer.Company = crg.Company AND cer.RateGrpCode = crg.RateGrpCode) \");sql.Append(\" WHERE cer.Company=\'\" + ToCompany + \"\' AND crg.Inactive=0 AND cer.SourceCurrCode=\'\" + CurrencyCode + \"\' AND cer.TargetCurrCode=\'HKD\') \");sql.Append(\" AND cer.Company=\'\" + ToCompany + \"\' AND crg.Inactive=0 AND cer.SourceCurrCode=\'\" + CurrencyCode + \"\' AND cer.TargetCurrCode=\'HKD\' \");dt.Clear();dt = Util.DBHelper.ExecuteDt(sql.ToString());if (dt.Rows.Count == 0){MessageBox.Show(CurrencyCode+\"至HKD的匯率沒有找到，生成\"+ FromCompany+\"價格表失敗！\");return;}CurrentRate = (decimal)dt.Rows[0][0];}//CurrentRate = 1;decimal ActualBaseUnitPrice = BaseUnitPrice;//1000公司的基礎價格if (part_increase_percent_c > 0){BaseUnitPrice = BaseUnitPrice * (1 + part_increase_percent_c / 100) * CurrentRate;}else{//sql = \"select increase_percent_c from XaSyst where company=\'\" + Util.EpicorHelper.GetSession().CompanyID + \"\'\";sql.Clear();sql.Append(\"select InternalPercent from [InternalCompanyProfitPercent] where FromCompany=\'\" + FromCompany + \"\' \");sql.Append(\"and ToCompany=\'\" + ToCompany + \"\' and Type=1\");part_increase_percent_c = decimal.Parse(Util.DBHelper.ExecuteScalar(sql.ToString()).ToString());BaseUnitPrice = BaseUnitPrice * (1 + part_increase_percent_c / 100) * CurrentRate;}NewVendPart[\"BaseUnitPrice\"] = BaseUnitPrice;//EffectiveDate  ExpirationDate Confirm_1_c CurrencyCode//取價格表裏面的生效日期，不要取當天日期//NewVendPart[\"EffectiveDate\"] = DateTime.Now.ToString(\"yyyy-MM-dd\");//NewVendPart[\"EffectiveDate\"] = EffectiveDate.ToString(\"yyyy-MM-dd\");((VendPartDataSet.VendPartRow)NewVendPart).EffectiveDate = EffectiveDate;NewVendPart[\"CurrencyCode\"] = \"HKD\";//此處固定公司間交易使用港幣NewVendPart[\"Confirm_1_c\"] = 1; //Demo公司沒有這個欄位，所以報錯退出了NewVendPart[\"Confirm_2_c\"] = 1;NewVendPart[\"actual_supplier_c\"] = ActualVendorID;NewVendPart[\"actual_price_c\"] = ActualBaseUnitPrice;NewVendPart[\"Company\"] = FromCompany;NewVendPart[\"AprvSupplier\"] = 1;a.Update();//a.Dispose();//這裏還有另外一個問題，就是分段價格，之前沒有考慮到，現在加進去//Method：GetNewVendPBrk//先查找一下對應的EffectiveDate有沒有分段的核價單//查找條件 company,part number,effective date,Vendor Numsql.Clear();sql.Append(\" SELECT * FROM erp.VendPBrk \");sql.Append(\" WHERE PartNum=\'\" + Part + \"\' \");sql.Append(\" AND VendorNum=\" + ActualVendorNum + \" \");sql.Append(\" AND EffectiveDate=\'\" + EffectiveDate2.ToString(\"yyyy-MM-dd\") + \"\' \");sql.Append(\" AND Company=\'\" + ToCompany + \"\' \");sql.Append(\" ORDER BY BreakQty\");dt.Clear();dt = Util.DBHelper.ExecuteDt(sql.ToString());session = Util.EpicorHelper.GetSession();session.CompanyID = FromCompany;//FromCompany作為傳入參數 轉換session到FromCompany進行操作，而不能再當前Company操作VendPartAdapter b = new VendPartAdapter(new ILauncher(session));b.BOConnect();decimal BreakPrice = 0, PriceModifier=0;if (dt.Rows.Count > 0){try{b.GetNewVendPBrk(Part, \"\", PUM, VendorNum, EffectiveDate);}catch{}for (int n = 0; n < dt.Rows.Count; n++){//GetNewVendPBrk(string partNum, string opCode, string puM, int vendorNum, DateTime effectiveDate)//VendPartDataSet GetLastEffectiveVendPart(int iVendorNum, string cPartNum, string cOpCode, string cPUM, DateTime? dtEffDate)b.GetNewVendPBrk(Part, \"\", PUM, VendorNum, EffectiveDate);DataRow NewVendPartBreak = b.VendPartData.VendPBrk[b.VendPartData.VendPBrk.Rows.Count - 1];//NewVendPartBreak[\"EffectiveDate\"] = EffectiveDate.ToString(\"yyyy-MM-dd\");NewVendPartBreak[\"BreakQty\"] = dt.Rows[n][\"BreakQty\"];//分段價格if (part_increase_percent_c > 0){BreakPrice = (ActualBaseUnitPrice + decimal.Parse(dt.Rows[n][\"PriceModifier\"].ToString()))* (1 + part_increase_percent_c / 100) * CurrentRate;PriceModifier = BreakPrice * CurrentRate - BaseUnitPrice;}else{//sql = \"select increase_percent_c from XaSyst where company=\'\" + Util.EpicorHelper.GetSession().CompanyID + \"\'\";sql.Append(\"select InternalPercent from [InternalCompanyProfitPercent] where FromCompany=\'\" + FromCompany + \"\' \");sql.Append(\"and ToCompany=\'\" + ToCompany + \"\' and Type=1\");part_increase_percent_c = (decimal)Util.DBHelper.ExecuteScalar(sql.ToString());BreakPrice = (ActualBaseUnitPrice + decimal.Parse(dt.Rows[n][\"PriceModifier\"].ToString()))* (1 + part_increase_percent_c / 100) * CurrentRate;PriceModifier = BreakPrice * CurrentRate - BaseUnitPrice;}NewVendPartBreak[\"PriceModifier\"] = PriceModifier;b.Update();}}}