public DataTable AutoDiscFlg(ArrayList ParameterList, out int iDiscRows) { //ParameterList=> //0.D_SOURCE_DATE_S Date, //1.D_SOURCE_DATE_E Date, //2.N_TAX_TYPE number //3.DISC_DATE Date //4.D_KEYIN_DATE Date //5.UID DataTable dtResult = new DataTable(); ArrayList Para = new ArrayList(); try { VAM22_DBO DBO = new VAM22_DBO(ref USEDB); Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); //取得稅率 DataTable dtTaxRate = DBO.GetTaxRate(ParameterList); decimal dTaxRate = decimal.Parse(dtTaxRate.Rows[0]["TAXRATE"].ToString()); //取得不足餘額可以折減的廠商 dtResult = DBO.QueryNoDiscData(ParameterList); //取得要折讓的退貨資料 DataTable dtReturnData = DBO.QueryReturnData(ParameterList); //取得要折讓的發票資料 DataTable dtInvoiceData = DBO.QueryInvoiceData(ParameterList); DateTime DISC_DATE = DateTime.Parse(ParameterList[3].ToString()); int iTAX_TYPE = int.Parse(ParameterList[2].ToString()); string strVendor = string.Empty; DateTime KEYIN_DATE = DateTime.Parse(ParameterList[4].ToString()); DateTime NOW_TIME = DateTime.Now; string strUID = ParameterList[5].ToString(); int N_ID = 0; string V_DISC_NO = string.Empty; iDiscRows = 0; for (int i = 0; i < dtReturnData.Rows.Count; i++) { //產生折讓主檔 if (strVendor != dtReturnData.Rows[i]["VENDOR"].ToString()) { strVendor = dtReturnData.Rows[i]["VENDOR"].ToString(); Para.Clear(); Para.Add(DISC_DATE); //D_DISC_DATE date, 折讓日期 web 輸入 Para.Add(iTAX_TYPE); //V_TAX_TYPE varchar2, 稅別 web 輸入 Para.Add(strVendor); //V_VENDOR varchar2, 廠商統編 //N_DISC_UAMT number, 折讓總額 = [步驟2 TOTAL_SRC_REMAIN_AMT] Para.Add(dtReturnData.Rows[i]["TOTAL_SRC_REMAIN_AMT"].ToString()); //N_DISC_TAX number, 稅額 =依照課稅別自動計算稅額。 //注意稅別,要抓SYS_VALUEADDTAX設定的稅率進行計算。 //如果是免稅,或零稅率,稅額是 0。 //select taxrate from sys_valueaddtax where code ='V5' --應稅的稅率 //Para.Add(Math.Round(dTaxRate * Decimal.Parse(dtReturnData.Rows[i]["TOTAL_SRC_REMAIN_AMT"].ToString()), 0)) //2011/03/17 修改使用實際單張發票沖銷金額*稅率 逐筆ROUND再加總 if (iTAX_TYPE == 0) { DataRow[] FindInv = dtInvoiceData.Select("VENDOR='" + strVendor + "'"); decimal dSumTax = 0; foreach (DataRow dr in FindInv) { dSumTax += Math.Round(dTaxRate * decimal.Parse(dr["DISC_AMT"].ToString()), 0, MidpointRounding.AwayFromZero); } Para.Add(dSumTax); } else { Para.Add(0); } Para.Add(KEYIN_DATE); //D_KEYIN_DATE date, 入帳日期 web 輸入 Para.Add(NOW_TIME); //D_UPDATEDATE date, 更新日期 = sysdate Para.Add(strUID); //V_UPDATEUID varchar2,更新人員=loginuser Para.Add(NOW_TIME); //D_CREATEDATE date, 建檔日期 =sysdate Para.Add(strUID); //V_CREATEUID varchar2,建檔人員 = loginuser Para.Add("0"); //V_TRANS_TAX varchar2 ,套印時稅別自動轉換 固定帶入0 DBO.doInsertDiscountMain(Para, out N_ID, out V_DISC_NO, DBT); iDiscRows++; //更新折讓主檔是否為自動折讓的flag Para.Clear(); Para.Add(V_DISC_NO); Para.Add(strUID); DBO.doUpdateAutoDiscFlg(Para, DBT); //發票沖銷 DataRow[] FindRows = dtInvoiceData.Select("VENDOR='" + strVendor + "'"); foreach (DataRow dr in FindRows) { Para.Clear(); Para.Add(V_DISC_NO); //V_DISC_NO varchar2,折讓單號 Para.Add(dr["INV_NO"].ToString()); //V_INV_NO varchar2,發票號碼 [事件三有折減的發票 INV_NO] Para.Add(Decimal.Parse(dr["DISC_AMT"].ToString()));//N_DISC_AMT number, 折讓金額[事件三單張發票折讓的金額] Para.Add(strUID); //V_UPDATEUID varchar2,更新日期 = sysdate Para.Add(NOW_TIME); //D_UPDATEDATE date, 更新人員=loginuser Para.Add(strUID); //V_CREATEUID varchar2,建檔日期 =sysdate Para.Add(NOW_TIME); //D_CREATEDATE date 建檔人員 = loginuser DBO.doInsertDiscountInvoice(Para, DBT); } } //寫入明細檔 Para.Clear(); Para.Add(N_ID); //N_PID number, VDS_VAM_DISCOUNT_MAIN.ID Para.Add(V_DISC_NO); //V_DISC_NO varchar2,折讓單號 Para.Add(iTAX_TYPE); //V_TAX_TYPE varchar2,稅別 Para.Add("2"); //V_SOURCE_TYPE varchar2, 來源類別退貨單固定=2 //V_SOURCE_NO varchar2, [發票餘額足夠的廠商的退貨單號 RETURN_NO] Para.Add(dtReturnData.Rows[i]["RETURN_NO"].ToString()); Para.Add(DBNull.Value); //N_REMAIN_AMT number, 固定為空值='' //N_AMT number, 本次折讓金額 = [步驟一 未折減金額SRC_REMAIN_AMT] Para.Add(Decimal.Parse(dtReturnData.Rows[i]["SRC_REMAIN_AMT"].ToString())); Para.Add(NOW_TIME); //D_UPDATEDATE date, 更新日期 = sysdate Para.Add(strUID); //V_UPDATEUID varchar2,更新人員=loginuser Para.Add(NOW_TIME); //D_CREATEDATE date, 建檔日期 =sysdate Para.Add(strUID); //V_CREATEUID varchar2 建檔人員 = loginuser DBO.doInsertDiscountDetl(Para, DBT); } DBT.Commit(); } catch (Exception ex) { if (DBT != null) DBT.Rollback(); throw ex; } finally { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) Conn.Close(); } return dtResult; }
public int DeleteAutoDisc(ArrayList ParameterList) { int iResult = 0; try { VAM22_DBO DBO = new VAM22_DBO(ref USEDB); Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); //D_KEYIN_DATE Date, //N_TAX_TYPE NUMBER, //V_NEW_UPDATEUID VARCHAR2, //D_NEW_UPDATEDATE Date, DBO.doDeleteAutoDisc(ParameterList, out iResult, DBT); DBT.Commit(); } catch (Exception ex) { if (DBT != null) DBT.Rollback(); throw ex; } finally { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) Conn.Close(); } return iResult; }