public DataTable QueryByLike(ArrayList ParameterList) { try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); ArrayList QueryParameter = new ArrayList(); QueryParameter.Clear(); QueryParameter.Add("1"); if (ParameterList[0].ToString() != string.Empty) { QueryParameter.Add(ParameterList[0].ToString() + "%"); //驗收單號 } else { QueryParameter.Add(ParameterList[0].ToString()); //驗收單號 } QueryParameter.Add(ParameterList[1].ToString()); //驗收日期 QueryParameter.Add(ParameterList[2].ToString()); //驗收時間 if (ParameterList[3].ToString() != string.Empty) { QueryParameter.Add(ParameterList[3].ToString() + "%"); //採購單號 } else { QueryParameter.Add(ParameterList[3].ToString()); //採購單號 } if (ParameterList[4].ToString() != string.Empty) { QueryParameter.Add(ParameterList[4].ToString() + "%"); //廠商送貨單號 } else { QueryParameter.Add(ParameterList[4]); //廠商送貨單號 } if (ParameterList[5].ToString() != string.Empty) { QueryParameter.Add(ParameterList[5].ToString() + "%"); //廠商發票號碼 } else { QueryParameter.Add(ParameterList[5].ToString()); //廠商發票號碼 } QueryParameter.Add(ParameterList[6].ToString()); //供應商編號 QueryParameter.Add(ParameterList[7].ToString()); //Item QueryParameter.Add(ParameterList[8].ToString()); //Period QueryParameter.Add(ParameterList[9].ToString()); //RowNum return dbo.QueryAcceptData(QueryParameter); } catch (Exception ex) { throw ex; } }
public string CheckVendorCloseStaus(string SysDate) { try { DBO.MaintainAcceptDBO dbo = new MaintainAcceptDBO(ref USEDB); DataTable dtResult = dbo.CheckVendorDailyClosed(SysDate); if (dtResult.Rows.Count == 0) { return "0"; } return "1"; } catch (Exception ex) { throw ex; } }
//VGI011.aspx #region 查詢方法 //VGI011.aspx使用 public DataTable QueryByFind(ArrayList ParameterList) { try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); ArrayList QueryParameter = new ArrayList(); QueryParameter.Add("0"); //flag QueryParameter.Add(ParameterList[0].ToString()); //驗收單號 QueryParameter.Add(ParameterList[1].ToString()); //驗收日期 QueryParameter.Add(ParameterList[2].ToString()); //驗收時間 QueryParameter.Add(ParameterList[3].ToString()); //採購單號 QueryParameter.Add(ParameterList[4].ToString()); //廠商送貨單號 QueryParameter.Add(ParameterList[5].ToString()); //廠商發票號碼 QueryParameter.Add(ParameterList[6].ToString()); //供應商編號 QueryParameter.Add(ParameterList[7].ToString()); //Item QueryParameter.Add(ParameterList[8].ToString()); //Period QueryParameter.Add(ParameterList[9].ToString()); //RowNum return dbo.QueryAcceptData(QueryParameter); } catch (Exception ex) { throw ex; } }
public bool UpdateAccept_2(ArrayList ParameterList, DataTable dtDetls, DataTable dtGifts, DbTransaction RootDBT, out string Statue, out string ErrorItem, out string ErrMsg) { bool IsRootTranscation = false; ErrorItem = string.Empty; Statue = string.Empty; ErrMsg = string.Empty; int ID = -1; int PID = -1; string Result = ""; try { string UserID = ParameterList[0].ToString(); string UpdateDate = ParameterList[1].ToString(); string AcceptNo = ParameterList[2].ToString(); string AcceptDate = ParameterList[3].ToString(); string PurchaseNo = ParameterList[4].ToString(); string WaybillNo = ParameterList[5].ToString(); string InvoiceNo = ParameterList[6].ToString(); string TaxAmt = ParameterList[7].ToString(); string UtaxAmt = ParameterList[8].ToString(); string Tax = ParameterList[9].ToString(); DBO.MaintainAcceptDBO VGIDBO = new DBO.MaintainAcceptDBO(ref USEDB); DBO.MaintainAcceptQualityCheck VGIQC = new DBO.MaintainAcceptQualityCheck(strConn); PURDBO.MaintainGoodsInTransit PURDB = new PURDBO.MaintainGoodsInTransit(strConn); IVMDBO.MaintainStockInOut IVMDB = new IVMDBO.MaintainStockInOut(strConn); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion ArrayList InsertParameter = new ArrayList(); #region 商品明細 //barcode //item //period //purchase_Qty //In_Date //TAcc_Qty //Acc_Qty //Cost //Price //Package_Unit //S_Weight //S_Length //S_Width //S_Height //P_Weight //P_Length //P_Width //P_Height //Product_Source //Produce_Date //Reserved_Period //Valid_Date //AllowAccept_Date //Old_Qty //RowState for (int i = 0; i < dtDetls.Rows.Count; i++) { int DetlID = -1; string Barcode = dtDetls.Rows[i]["barcode"].ToString(); string Item = dtDetls.Rows[i]["item"].ToString(); string Period = dtDetls.Rows[i]["period"].ToString(); string In_Date = dtDetls.Rows[i]["In_Date"].ToString(); string PurchaseQty = dtDetls.Rows[i]["purchase_Qty"].ToString(); string TotalAcceptQty = dtDetls.Rows[i]["TAcc_Qty"].ToString(); string AcceptQty = dtDetls.Rows[i]["Acc_Qty"].ToString(); string OLDQty = dtDetls.Rows[i]["Old_Qty"].ToString(); string DFlag = dtDetls.Rows[i]["RowState"].ToString(); int accept_days = -1; int out_days = -1; //寫入明細檔資料,逐筆寫入 InsertParameter.Clear(); InsertParameter.Add(UserID); InsertParameter.Add(UpdateDate); InsertParameter.Add(AcceptNo); InsertParameter.Add(Barcode); //BarCode InsertParameter.Add(Item); //Item InsertParameter.Add(Period); //Period InsertParameter.Add(AcceptQty); //NewQty InsertParameter.Add(dtDetls.Rows[i]["Cost"].ToString()); //Cost InsertParameter.Add(dtDetls.Rows[i]["Price"].ToString()); //Price InsertParameter.Add(dtDetls.Rows[i]["Package_Unit"].ToString()); //Package_Unit InsertParameter.Add(dtDetls.Rows[i]["S_Weight"].ToString()); //S_Weight InsertParameter.Add(dtDetls.Rows[i]["S_Length"].ToString()); //S_Length InsertParameter.Add(dtDetls.Rows[i]["S_Width"].ToString()); //S_Width InsertParameter.Add(dtDetls.Rows[i]["S_Height"].ToString()); //S_Height InsertParameter.Add(dtDetls.Rows[i]["P_Weight"].ToString()); //P_Weight InsertParameter.Add(dtDetls.Rows[i]["P_Length"].ToString()); //P_Length InsertParameter.Add(dtDetls.Rows[i]["P_Width"].ToString()); //P_Width InsertParameter.Add(dtDetls.Rows[i]["P_Height"].ToString()); //P_Height InsertParameter.Add(dtDetls.Rows[i]["Product_Source"].ToString()); //Product_Source InsertParameter.Add(dtDetls.Rows[i]["Produce_Date"].ToString()); //Produce_Date InsertParameter.Add(dtDetls.Rows[i]["Reserved_Period"].ToString()); //Reserved_Period InsertParameter.Add(dtDetls.Rows[i]["Valid_Date"].ToString()); //Valid_Date InsertParameter.Add(dtDetls.Rows[i]["AllowAccept_Date"].ToString());//AllowAccept_Date InsertParameter.Add(DFlag); //23.FLAG InsertParameter.Add(dtDetls.Rows[i]["UPDATEDATE"]); //OLD UPDATEDATE InsertParameter.Add(dtDetls.Rows[i]["UPDATEUID"]); //OLD UPDATEUID VGIDBO.UpdateAcceptDetl_2(InsertParameter, DBT, out DetlID); //依傳入的明細資料集參數,將正常品資料回寫生效中(enable=1)的商品屬性檔(VDS_ITM_PERIOD_PROFILE)的下列欄位 InsertParameter.Clear(); InsertParameter.Add(Item); InsertParameter.Add(Period); InsertParameter.Add(dtDetls.Rows[i]["Package_Unit"].ToString()); //Package_Unit InsertParameter.Add(dtDetls.Rows[i]["S_Weight"].ToString()); //S_Weight InsertParameter.Add(dtDetls.Rows[i]["S_Length"].ToString()); //S_Length InsertParameter.Add(dtDetls.Rows[i]["S_Width"].ToString()); //S_Width InsertParameter.Add(dtDetls.Rows[i]["S_Height"].ToString()); //S_Height InsertParameter.Add(dtDetls.Rows[i]["P_Weight"].ToString()); //P_Weight InsertParameter.Add(dtDetls.Rows[i]["P_Length"].ToString()); //P_Length InsertParameter.Add(dtDetls.Rows[i]["P_Width"].ToString()); //P_Width InsertParameter.Add(dtDetls.Rows[i]["P_Height"].ToString()); //P_Height InsertParameter.Add(AcceptDate); InsertParameter.Add(AcceptQty); //NewQty InsertParameter.Add(dtDetls.Rows[i]["Valid_Date"].ToString()); //Valid_Date InsertParameter.Add(dtDetls.Rows[i]["AllowAccept_Date"].ToString());//AllowAccept_Date //23.FLAG //24 OldQty UpdateItmPeriodProfile_2(UserID, UpdateDate, InsertParameter, DBT, out accept_days, out out_days); #region 贈品明細 //Barcode 商品條碼 //Item 品號 //Period 期別 //Virtual_Code 虛擬品號 //purchase_Qty 採購數量 //GiftName //Accept_Qty 驗收數量 //accept_accqty 總驗收數 //Old_Qty 驗收數量(原始) //SQty 剩餘數量 //Package_Unit 1包裝數 //S_Weight 單品重量 //S_Length 單品長度 //S_Width 單品寬度 //S_Height 單品高度 //P_Weight 包裝重量 //P_Length 包裝長度 //P_Width 包裝寬度 //P_Height 包裝高度 //UPDATEUID //UPDATEDATE //RowState "U"; if (DFlag == "U") { //更新贈品明細 for (int x = 0; x < dtGifts.Rows.Count; x++) { string GiftBarcode = dtGifts.Rows[x]["Barcode"].ToString(); string GiftItem = dtGifts.Rows[x]["Item"].ToString(); string GiftPeriod = dtGifts.Rows[x]["Period"].ToString(); string GiftVirtualCode = dtGifts.Rows[x]["Virtual_Code"].ToString(); string GFlag = dtGifts.Rows[x]["RowState"].ToString(); string GiftTotalAcceptQty = dtGifts.Rows[x]["accept_accqty"].ToString(); string GiftAcceptQty = dtGifts.Rows[x]["Accept_Qty"].ToString(); string GiftAcceptQty_Old = dtGifts.Rows[x]["Old_Qty"].ToString(); string GiftPurchaseQty = dtGifts.Rows[x]["purchase_Qty"].ToString(); if (Barcode == GiftBarcode && Item == GiftItem && Period == GiftPeriod) { InsertParameter.Clear(); InsertParameter.Add(UserID); //0 InsertParameter.Add(UpdateDate); //1 InsertParameter.Add(DetlID); //2 InsertParameter.Add(dtGifts.Rows[x]["Barcode"].ToString()); //3 Barcode InsertParameter.Add(GiftItem); //4 Item InsertParameter.Add(GiftPeriod); //5 Period InsertParameter.Add(GiftVirtualCode); //6 VirtualCode InsertParameter.Add(AcceptNo); //7 InsertParameter.Add(dtGifts.Rows[x]["purchase_Qty"].ToString());//8 PurchaseQty InsertParameter.Add(dtGifts.Rows[x]["Accept_Qty"].ToString()); //9 AcceptQty InsertParameter.Add(dtGifts.Rows[x]["Package_Unit"].ToString());//10 PackageUnit InsertParameter.Add(dtGifts.Rows[x]["S_Weight"].ToString()); //11 SWeight InsertParameter.Add(dtGifts.Rows[x]["S_Length"].ToString()); //12 SLength InsertParameter.Add(dtGifts.Rows[x]["S_Width"].ToString()); //13 SWidth InsertParameter.Add(dtGifts.Rows[x]["S_Height"].ToString()); //14 SHeigh InsertParameter.Add(dtGifts.Rows[x]["P_Weight"].ToString()); //15 PWeight InsertParameter.Add(dtGifts.Rows[x]["P_Length"].ToString()); //16 PLength InsertParameter.Add(dtGifts.Rows[x]["P_Width"].ToString()); //17 PWidth InsertParameter.Add(dtGifts.Rows[x]["P_Height"].ToString()); //18 PHeigh InsertParameter.Add(dtGifts.Rows[x]["RowState"].ToString()); //19 RowState InsertParameter.Add(dtGifts.Rows[x]["UPDATEDATE"]); //20 OLD UPDATEDATE InsertParameter.Add(dtGifts.Rows[x]["UPDATEUID"]); //21 OLD UPDATEUID VGIDBO.UpdateAcceptGift_2(InsertParameter, DBT); if (GFlag == "U") { //依傳入的贈品明細資料集參數,將贈品資料回寫贈品屬性檔(VDS_MKT_GIFT_PROFILE)的下列欄位 InsertParameter.Clear(); InsertParameter.Add(UserID); //0 InsertParameter.Add(UpdateDate); //1 InsertParameter.Add(GiftItem); //2 Item InsertParameter.Add(GiftPeriod); //3 Period InsertParameter.Add(GiftVirtualCode); //4 VirtualCode InsertParameter.Add(dtGifts.Rows[x]["Package_Unit"].ToString());//5 PackageUnit InsertParameter.Add(dtGifts.Rows[x]["S_Weight"].ToString()); //6 SWeight InsertParameter.Add(dtGifts.Rows[x]["S_Length"].ToString()); //7 SLength InsertParameter.Add(dtGifts.Rows[x]["S_Width"].ToString()); //8 SWidth InsertParameter.Add(dtGifts.Rows[x]["S_Height"].ToString()); //9 SHeigh InsertParameter.Add(dtGifts.Rows[x]["P_Weight"].ToString()); //10 PWeight InsertParameter.Add(dtGifts.Rows[x]["P_Length"].ToString()); //11 PLength InsertParameter.Add(dtGifts.Rows[x]["P_Width"].ToString()); //12 PWidth InsertParameter.Add(dtGifts.Rows[x]["P_Height"].ToString()); //13 PHeigh InsertParameter.Add(AcceptDate); //14 InsertParameter.Add(dtGifts.Rows[x]["Accept_Qty"].ToString()); //15 accept_qty InsertParameter.Add(accept_days); //16 InsertParameter.Add(out_days); //17 DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); dbo.UpdateMktGiftProfile_2(InsertParameter, DBT); } } } } #endregion } #endregion #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion return true; } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
//0.ChanTransNo,1.CreateID,2.CreateDate //Datatable 欄位 0.item,1.period public bool CheckData(string ChanTransNo,string CreateID,string CreateDate,DataTable dtTransIn,DbTransaction RootDBT,out string ErrorMsg,out string PurchaseNo,out DataTable DtTransItem,out DataTable dtTransGift) { bool IsRootTranscation = false; PurchaseNo = string.Empty; ErrorMsg = string.Empty; DtTransItem = null; dtTransGift = null; ArrayList ParameterList = new ArrayList(); try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion //確認資料都是屬於同一張採購單,並回傳採購單號,兩筆以上採購單回傳錯誤訊息 for (int i = 0; i < dtTransIn.Rows.Count; i++) { ParameterList.Clear(); ParameterList.Add(dtTransIn.Rows[i][0].ToString()); ParameterList.Add(dtTransIn.Rows[i][1].ToString()); DataTable dtPurchase = dbo.CheckPurData(ParameterList); if (i == 0) { if (dtPurchase.Rows.Count == 1) { PurchaseNo = dtPurchase.Rows[0][0].ToString(); } else if (dtPurchase.Rows.Count == 0) { ErrorMsg = "查無採購資料"; return false; } else if (dtPurchase.Rows.Count > 1) { ErrorMsg = "勾選品項非存在同一張採購單,請確認後再執行驗收單開立"; return false; } } else { if (dtPurchase.Rows.Count == 1) { if (dtPurchase.Rows[0][0].ToString() != PurchaseNo) { ErrorMsg = "勾選品項非存在同一張採購單,請確認後再執行驗收單開立"; return false; } } else if (dtPurchase.Rows.Count == 0) { ErrorMsg = "查無採購資料"; return false; } else if (dtPurchase.Rows.Count > 1) { ErrorMsg = "勾選品項非存在同一張採購單,請確認後再執行驗收單開立"; return false; } } } //取得唯一的採購單號後開始建立初始驗收單若存在於單一採購單中,將@ChanTransNo,@CreateID,@item,@period及步驟1取得的採購單號[@purchase_no]為傳入參數呼叫[ImportAccept.CreateTransIn] //傳入第一筆的ChanTransNo、CreateID、Item、period取得INSERT到DataTransInMain資料,回傳主檔ID和CODE CreateTransIn(ChanTransNo, PurchaseNo, CreateID, CreateDate, dtTransIn, DBT, out DtTransItem, out dtTransGift); #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion return true; } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
public string GetTaxRate() { try { string TaxRate = string.Empty; DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); DataTable dtResult = dbo.GetTaxRate(); if (dtResult.Rows.Count > 0) { TaxRate = dtResult.Rows[0]["TaxRate"].ToString(); } else { throw new Exception("無法取得稅率資料"); } return TaxRate; } catch (Exception ex) { throw ex; } }
///新增驗收單 /// *表頭參數: //(0)[@UserID] //(1)[@CreateDate] //(2)[@驗收日期] //(3)[@驗收時間] //(4)[@採購單號] //(5)[@廠商送貨單號] //(6)[@發票號碼] //(7)[@供應商] //(8)[@應稅總額] //(9)[@未稅總額] //(10)[@稅額] //*明細資料集參數: //(0)[@商品條碼] //(1)[@品號] //(2)[@期別] //(3)[@採購數量] //(4)[@預進日期] //(5)[@總驗收數] //(6)[@驗收數量] //(7)[@成本價] //(8)[@零售價] //(9)[@1包裝數] //(10)[@單品重量] //(11)[@單品長度] //(12)[@單品寬度] //(13)[@單品高度] //(14)[@包裝重量] //(15)[@包裝長度] //(16)[@包裝寬度] //(17)[@包裝高度] //(18)[@商品來源] //(19)[@製造日期] //(20)[@保存期限] //(21)[@有效日期] //(22)[@允收日期] //*品質檢驗資料集 //(1)[@品號] //(2)[@期別] //(3)[@檢查項目] //(4)[@是否正常] //(5)[@異常說明] //*贈品明細資料集參數: //(1)[@商品條碼] //(2)[@品號] //(3)[@期別] //(4)[@虛擬品號] //(5)[@採購數量] //(6)[@總驗收數] //(7)[@驗收數量] //(8)[@1包裝數] //(9)[@單品重量] //(10)[@單品長度] //(11)[@單品寬度] //(12)[@單品高度] //(13)[@包裝重量] //(14)[@包裝長度] //(15)[@包裝寬度] //(16)[@包裝高度] /// <summary> /// 建立驗收單 /// </summary> /// <param name="ParameterList"></param> /// <param name="dtDetls"></param> /// <param name="dtGifts"></param> /// <param name="RootDBT"></param> /// <param name="AcceptNo"></param> /// <param name="Statue"></param> /// <param name="ErrorItem"></param> public bool CreateAccept(ArrayList ParameterList, DataTable dtDetls, DataTable dtGifts, DbTransaction RootDBT, out string AcceptNo, out string Statue, out string ErrorItem) { bool IsRootTranscation = false; //檢查商品和贈品是不是都驗收了,未驗收結案才能繼續 ErrorItem = string.Empty; AcceptNo = string.Empty; Statue = string.Empty; int PID = -1; try { string UserID = ParameterList[0].ToString(); string UpdateDate = ParameterList[1].ToString(); string AcceptDate = ParameterList[2].ToString(); string AcceptTime = ParameterList[3].ToString(); string PurchaseNo = ParameterList[4].ToString(); string WaybillNo = ParameterList[5].ToString(); string InvoiceNo = ParameterList[6].ToString(); string Vender = ParameterList[7].ToString(); string TaxAmt = ParameterList[8].ToString(); string UtaxAmt = ParameterList[9].ToString(); string Tax = ParameterList[10].ToString(); DBO.MaintainAcceptDBO VGIDBO = new DBO.MaintainAcceptDBO(ref USEDB); DBO.MaintainAcceptQualityCheck VGIQC = new DBO.MaintainAcceptQualityCheck(strConn); PURDBO.MaintainGoodsInTransit PURDB = new PURDBO.MaintainGoodsInTransit(strConn); IVMDBO.MaintainStockInOut IVMDB = new IVMDBO.MaintainStockInOut(strConn); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion //新增主檔 ArrayList InsertParameter = new ArrayList(); InsertParameter.Clear(); InsertParameter.Add(UserID); InsertParameter.Add(UpdateDate); InsertParameter.Add(AcceptDate); InsertParameter.Add(AcceptTime); InsertParameter.Add(PurchaseNo); InsertParameter.Add(WaybillNo); InsertParameter.Add(InvoiceNo); InsertParameter.Add(Vender); InsertParameter.Add(TaxAmt); InsertParameter.Add(UtaxAmt); InsertParameter.Add(Tax); VGIDBO.CreateAcceptMain(InsertParameter, DBT, out PID, out AcceptNo); //新增明細 DataTable dtStockIn = new DataTable(); dtStockIn.Columns.Add("LocateNo"); dtStockIn.Columns.Add("LocateSec"); dtStockIn.Columns.Add("Item"); dtStockIn.Columns.Add("Period"); dtStockIn.Columns.Add("In_Qty"); dtStockIn.Columns.Add("Cost"); //取得入庫暫存儲區、儲格 string LocateNo = ""; string LocateSec = ""; LocateSec = VGIDBO.GetStockInTempLocateSec(); if (LocateSec == string.Empty) { throw new Exception("無法取得入庫暫存儲區或儲位"); } else { LocateNo = LocateSec.Substring(0, 1); } #region 商品明細 //barcode //item //period //purchase_Qty //In_Date //TAcc_Qty //Acc_Qty //Cost //Price //Package_Unit //S_Weight //S_Length //S_Width //S_Height //P_Weight //P_Length //P_Width //P_Height //Product_Source //Produce_Date //Reserved_Period //Valid_Date //AllowAccept_Date //Old_Qty //RowState for (int i = 0; i < dtDetls.Rows.Count; i++) { int DetlID = -1; string Barcode = dtDetls.Rows[i]["barcode"].ToString(); string Item = dtDetls.Rows[i]["item"].ToString(); string Period = dtDetls.Rows[i]["period"].ToString(); string PurchaseQty = dtDetls.Rows[i]["purchase_Qty"].ToString(); string In_Date = dtDetls.Rows[i]["In_Date"].ToString(); string TotalAcceptQty = dtDetls.Rows[i]["TAcc_Qty"].ToString(); string AcceptQty = dtDetls.Rows[i]["Acc_Qty"].ToString(); int accept_days = -1; int out_days = -1; //取得入庫單明細資料 DataRow drStockIn = dtStockIn.NewRow(); drStockIn["LocateNo"] = LocateNo; drStockIn["LocateSec"] = LocateSec; drStockIn["Item"] = Item; drStockIn["Period"] = Period; drStockIn["In_Qty"] = AcceptQty; drStockIn["Cost"] = dtDetls.Rows[i]["Cost"].ToString(); dtStockIn.Rows.Add(drStockIn); //寫入明細檔資料,逐筆寫入 InsertParameter.Clear(); InsertParameter.Add(UserID); InsertParameter.Add(UpdateDate); InsertParameter.Add(PID); InsertParameter.Add(AcceptNo); InsertParameter.Add(Barcode); //BarCode InsertParameter.Add(Item); //Item InsertParameter.Add(Period); //Period InsertParameter.Add(AcceptQty); //AcceptQty InsertParameter.Add(dtDetls.Rows[i]["Cost"].ToString()); //Cost InsertParameter.Add(dtDetls.Rows[i]["Price"].ToString()); //Price InsertParameter.Add(dtDetls.Rows[i]["Package_Unit"].ToString()); //Package_Unit InsertParameter.Add(dtDetls.Rows[i]["S_Weight"].ToString()); //S_Weight InsertParameter.Add(dtDetls.Rows[i]["S_Length"].ToString()); //S_Length InsertParameter.Add(dtDetls.Rows[i]["S_Width"].ToString()); //S_Width InsertParameter.Add(dtDetls.Rows[i]["S_Height"].ToString()); //S_Height InsertParameter.Add(dtDetls.Rows[i]["P_Weight"].ToString()); //P_Weight InsertParameter.Add(dtDetls.Rows[i]["P_Length"].ToString()); //P_Length InsertParameter.Add(dtDetls.Rows[i]["P_Width"].ToString()); //P_Width InsertParameter.Add(dtDetls.Rows[i]["P_Height"].ToString()); //P_Height InsertParameter.Add(dtDetls.Rows[i]["Product_Source"]); //Product_Source InsertParameter.Add(dtDetls.Rows[i]["Produce_Date"]); //Produce_Date InsertParameter.Add(dtDetls.Rows[i]["Reserved_Period"]); //Reserved_Period InsertParameter.Add(dtDetls.Rows[i]["Valid_Date"]); //Valid_Date InsertParameter.Add(dtDetls.Rows[i]["AllowAccept_Date"]); //AllowAccept_Date VGIDBO.CreateAcceptDetl(InsertParameter, DBT, out DetlID); //寫入商品檢驗結果 InsertParameter.Clear(); InsertParameter.Add(UserID); InsertParameter.Add(UpdateDate); InsertParameter.Add(DetlID); InsertParameter.Add(AcceptNo); InsertParameter.Add(Item); InsertParameter.Add(Period); VGIQC.CreateQualityCheck(InsertParameter, DBT); //若該商品品項的[@總驗收數]+[@驗收數量]>=[採購數量],則呼叫[MaintainPurchaseOrder.ClosePurchaseOrderDetail] if (int.Parse(TotalAcceptQty) + int.Parse(AcceptQty) >= int.Parse(PurchaseQty)) { InsertParameter.Clear(); InsertParameter.Add(UserID); InsertParameter.Add(PurchaseNo); InsertParameter.Add(Item); InsertParameter.Add(Period); //VirtualCode = Item InsertParameter.Add(Item); ClosePurchaseOrderDetail(InsertParameter, DBT); } //依傳入的明細資料集參數,將正常品資料回寫生效中(enable=1)的商品屬性檔(VDS_ITM_PERIOD_PROFILE)的下列欄位 InsertParameter.Clear(); InsertParameter.Add(Item); InsertParameter.Add(Period); InsertParameter.Add(dtDetls.Rows[i]["Package_Unit"].ToString()); //Package_Unit InsertParameter.Add(dtDetls.Rows[i]["S_Weight"].ToString()); //S_Weight InsertParameter.Add(dtDetls.Rows[i]["S_Length"].ToString()); //S_Length InsertParameter.Add(dtDetls.Rows[i]["S_Width"].ToString()); //S_Width InsertParameter.Add(dtDetls.Rows[i]["S_Height"].ToString()); //S_Height InsertParameter.Add(dtDetls.Rows[i]["P_Weight"].ToString()); //P_Weight InsertParameter.Add(dtDetls.Rows[i]["P_Length"].ToString()); //P_Length InsertParameter.Add(dtDetls.Rows[i]["P_Width"].ToString()); //P_Width InsertParameter.Add(dtDetls.Rows[i]["P_Height"].ToString()); //P_Height InsertParameter.Add(AcceptDate); InsertParameter.Add(AcceptQty); InsertParameter.Add(dtDetls.Rows[i]["Valid_Date"].ToString()); //Valid_Date InsertParameter.Add(dtDetls.Rows[i]["AllowAccept_Date"].ToString());//AllowAccept_Date UpdateItmPeriodProfile(UserID, UpdateDate, InsertParameter, DBT, out accept_days, out out_days); //呼叫[MaintainGoodsInTransit.UpdateGoodsInTrainsitByGoodsReceive],傳入[@品號],[@期別],[@虛擬品號],[@採購單號],[@驗收量](若品項為正常品,則[@虛擬品號]=[@品號]),更新在途量,若未找到相對應的在途單資料,表示未開立在途單,不需更新 InsertParameter.Clear(); InsertParameter.Add(PurchaseNo); InsertParameter.Add(Item); InsertParameter.Add(Period); InsertParameter.Add(Item); InsertParameter.Add(AcceptQty); PURDB.UpdateGoodsInTrainsitByGoodsReceive(InsertParameter, DBT); #region 贈品明細 //Barcode 商品條碼 //Item 品號 //Period 期別 //Virtual_Code 虛擬品號 //purchase_Qty 採購數量 //TAcc_Qty 總驗收數 //Accept_Qty 驗收數量 //accept_accqty //Old_Qty 驗收數量(原始 //Package_Unit 1包裝數 //S_Weight 單品重量 //S_Length 單品長度 //S_Width 單品寬度 //S_Height 單品高度 //P_Weight 包裝重量 //P_Length 包裝長度 //P_Width 包裝寬度 //P_Height 包裝高度 //RowState 包裝高度 //寫入贈品明細 for (int x = 0; x < dtGifts.Rows.Count; x++) { string GiftBarcode = dtGifts.Rows[x]["Barcode"].ToString(); string GiftItem = dtGifts.Rows[x]["Item"].ToString(); string GiftPeriod = dtGifts.Rows[x]["Period"].ToString(); string GiftVirtualCode = dtGifts.Rows[x]["Virtual_Code"].ToString(); string GiftTotalAcceptQty = dtGifts.Rows[x]["accept_accqty"].ToString(); //總驗收量 string GiftAcceptQty = dtGifts.Rows[x]["Accept_Qty"].ToString(); //驗收數量 string GiftAcceptQty_Old = dtGifts.Rows[x]["Old_Qty"].ToString(); string GiftPurchaseQty = dtGifts.Rows[x]["purchase_Qty"].ToString(); //採購數量 if (Barcode == GiftBarcode && Item == GiftItem && Period == GiftPeriod) { InsertParameter.Clear(); InsertParameter.Add(UserID); //0 InsertParameter.Add(UpdateDate); //1 InsertParameter.Add(DetlID); //2 InsertParameter.Add(dtGifts.Rows[x][0].ToString()); //3 Barcode InsertParameter.Add(GiftItem); //4 Item InsertParameter.Add(GiftPeriod); //5 Period InsertParameter.Add(GiftVirtualCode); //6 VirtualCode InsertParameter.Add(AcceptNo); //7 InsertParameter.Add(dtGifts.Rows[x]["purchase_Qty"].ToString()); //8 PurchaseQty InsertParameter.Add(dtGifts.Rows[x]["Accept_Qty"].ToString()); //9 AcceptQty InsertParameter.Add(dtGifts.Rows[x]["Package_Unit"].ToString()); //10 PackageUnit InsertParameter.Add(dtGifts.Rows[x]["S_Weight"].ToString()); //11 SWeight InsertParameter.Add(dtGifts.Rows[x]["S_Length"].ToString()); //12 SLength InsertParameter.Add(dtGifts.Rows[x]["S_Width"].ToString()); //13 SWidth InsertParameter.Add(dtGifts.Rows[x]["S_Height"].ToString()); //14 SHeigh InsertParameter.Add(dtGifts.Rows[x]["P_Weight"].ToString()); //15 PWeight InsertParameter.Add(dtGifts.Rows[x]["P_Length"].ToString()); //16 PLength InsertParameter.Add(dtGifts.Rows[x]["P_Width"].ToString()); //17 PWidth InsertParameter.Add(dtGifts.Rows[x]["P_Height"].ToString()); //18 PHeigh VGIDBO.CreateAcceptGift(InsertParameter, DBT); //若該贈品品項的[@總驗收數]+[@驗收數量]>=[採購數量],則呼叫[MaintainPurchaseOrder.ClosePurchaseOrderDetail] if (int.Parse(GiftTotalAcceptQty) + int.Parse(GiftAcceptQty) >= int.Parse(GiftPurchaseQty)) { InsertParameter.Clear(); InsertParameter.Add(UserID); //0 InsertParameter.Add(PurchaseNo); //1 InsertParameter.Add(GiftItem); //2 InsertParameter.Add(GiftPeriod); //3 InsertParameter.Add(GiftVirtualCode); //4 ClosePurchaseOrderDetail(InsertParameter, DBT); } //依傳入的贈品明細資料集參數,將贈品資料回寫贈品屬性檔(VDS_MKT_GIFT_PROFILE)的下列欄位 InsertParameter.Clear(); InsertParameter.Add(UserID); //0 InsertParameter.Add(UpdateDate); //1 InsertParameter.Add(GiftItem); //2 Item InsertParameter.Add(GiftPeriod); //3 Period InsertParameter.Add(GiftVirtualCode); //4 VirtualCode InsertParameter.Add(dtGifts.Rows[x]["Package_Unit"].ToString()); //5 PackageUnit InsertParameter.Add(dtGifts.Rows[x]["S_Weight"].ToString()); //6 SWeight InsertParameter.Add(dtGifts.Rows[x]["S_Length"].ToString()); //7 SLength InsertParameter.Add(dtGifts.Rows[x]["S_Width"].ToString()); //8 SWidth InsertParameter.Add(dtGifts.Rows[x]["S_Height"].ToString()); //9 SHeigh InsertParameter.Add(dtGifts.Rows[x]["P_Weight"].ToString()); //10 PWeight InsertParameter.Add(dtGifts.Rows[x]["P_Length"].ToString()); //11 PLength InsertParameter.Add(dtGifts.Rows[x]["P_Width"].ToString()); //12 PWidth InsertParameter.Add(dtGifts.Rows[x]["P_Height"].ToString()); //13 PHeigh InsertParameter.Add(AcceptDate); //14 InsertParameter.Add(dtGifts.Rows[x]["Accept_Qty"].ToString()); //15 InsertParameter.Add(accept_days); //16 InsertParameter.Add(out_days); //17 //UpdateGiftProfile(UserID, UpdateDate, accept_days, out_days, InsertParameter,DBT); DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); dbo.UpdateMktGiftProfile(InsertParameter, DBT); //呼叫[MaintainGoodsInTransit.UpdateGoodsInTrainsitByGoodsReceive],傳入[@品號],[@期別],[@虛擬品號],[@採購單號],[@驗收量](若品項為正常品,則[@虛擬品號]=[@品號]),更新在途量,若未找到相對應的在途單資料,表示未開立在途單,不需更新 InsertParameter.Clear(); InsertParameter.Add(PurchaseNo); //0 InsertParameter.Add(GiftItem); //1 InsertParameter.Add(GiftPeriod); //2 InsertParameter.Add(GiftVirtualCode); //3 InsertParameter.Add(Convert.ToString(int.Parse(GiftAcceptQty_Old) - int.Parse(GiftAcceptQty))); //4 PURDB.UpdateGoodsInTrainsitByGoodsReceive(InsertParameter, DBT); } } #endregion //判斷該品項是否需產生新的期別 //(1)該品項的期別(period)若不為[000000]且定期出刊(select attribute from vds_itm_item where item=[@item] , attribute的值為=1表示定期出刊),則需需產生下一期別資料 //(2)產生新期別的步驟如下: //*檢查該品項期別是否已產生下一期期別資料(select count(*) from vds_itm_period_main where item=@item and period=lpad(to_char(to_number(@period) +(select period_step from vds_itm_item where item=@item)),6,'0')),若尚未產生,則呼叫[MaintainSKUPeriod.ProcessAddNextPeriod]產生新的期別資料,傳入資料參數如[新增下一期別.doc]文件所述 InsertParameter.Clear(); InsertParameter.Add(Item); InsertParameter.Add(Period); string V_Next = VGIDBO.CheckNextPeriod(InsertParameter); if (V_Next == "1") { string Msg = ""; string Result = ""; InsertParameter.Clear(); InsertParameter.Add(AcceptNo); InsertParameter.Add(UserID); InsertParameter.Add(DateTime.Now); VGIDBO.AddNextPeriod(InsertParameter, DBT, out Msg, out Result); if (Result != "1") { throw new Exception("新增下一期別發生錯誤" + Msg); } } } #endregion //建立入庫單 //取得入庫單主檔資料 >輸入變數 0.CheckFreeze_Flag(預設1) 1.ReasonNo 2.ADJNo 3.SourceNo 4.UserID //(0)Flag //(1)出入庫原因 (InOut_Reason_No)=S1 //(2)調整單號 (Adjust_No)=null //(3)來源單號 (Source_No)=[@驗收單號] //(4)使用者 InsertParameter.Clear(); InsertParameter.Add("1"); InsertParameter.Add("S1"); InsertParameter.Add(""); InsertParameter.Add(AcceptNo); InsertParameter.Add(UserID); IVMDB.CreateStockIn(InsertParameter, dtStockIn, DBT); #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion return true; } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
public void UpdateItmPeriodProfile_2(string UserID, string UpdateDate, ArrayList ParameterList, DbTransaction RootDBT, out int accept_days, out int out_days) { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); string ValidDate = ParameterList[13].ToString(); string AllowAccept_Date = ParameterList[14].ToString(); accept_days = -1; out_days = -1; if (ValidDate != string.Empty && AllowAccept_Date != string.Empty) { DateTime dtValid = DateTime.ParseExact(ValidDate, "yyyy/MM/dd", null); DateTime dtAllow = DateTime.ParseExact(AllowAccept_Date, "yyyy/MM/dd", null); // 計算差異天數 TimeSpan tsDay = dtValid - dtAllow; accept_days = (int)tsDay.TotalDays + 1; out_days = GetOutDays(accept_days); } bool IsRootTranscation = false; try { //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion ArrayList SQLParameters = new ArrayList(); SQLParameters.Clear(); SQLParameters.Add(UserID); //0 string SQLParameters.Add(UpdateDate); //1 string SQLParameters.Add(ParameterList[0]); //2 string SQLParameters.Add(ParameterList[1]); //3 string SQLParameters.Add(ParameterList[2]); //4 int 一包裝數 SQLParameters.Add(ParameterList[3]); //5 double 3-10 共八個都是 double (SIZE) SQLParameters.Add(ParameterList[4]); //6 double SQLParameters.Add(ParameterList[5]); //7 double SQLParameters.Add(ParameterList[6]); //8 double SQLParameters.Add(ParameterList[7]); //9 double SQLParameters.Add(ParameterList[8]); //10 double SQLParameters.Add(ParameterList[9]); //11 double SQLParameters.Add(ParameterList[10]); //12 double SQLParameters.Add(ParameterList[11]); //13 string SQLParameters.Add(ParameterList[12]); //14 int SQLParameters.Add(accept_days); //15 int SQLParameters.Add(out_days); //16 int int Res = dbo.UpdateItmPeriodProfile_2(SQLParameters, DBT); } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } }
public DataTable GetGiftDetail(ArrayList ParameterList) { try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); return dbo.GetGiftDetail(ParameterList); } catch (Exception ex) { throw ex; } }
public DataTable QueryGift(string Item, string Period, string PurchaseNo) { try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); ArrayList QueryParameter = new ArrayList(); QueryParameter.Clear(); QueryParameter.Add(Item); QueryParameter.Add(Period); QueryParameter.Add(PurchaseNo); return dbo.GetGiftData(QueryParameter); } catch (Exception ex) { throw ex; } }
public void DelDataTransTemp(ArrayList ParameterList, DbTransaction RootDBT) { bool IsRootTranscation = false; try { //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); #endregion dbo.DeleteDataTransTemp(ParameterList, RootDBT); #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
public DataTable QueryTemp(ArrayList ParameterList) { try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); return dbo.QueryTemp(ParameterList); } catch (Exception ex) { throw ex; } }
public bool QueryItemData(string ItemBarCode, string PeriodBarCode, string PurchaseNo, out string Item, out string Period, out string Cost, out string Price, out string TaxType, out string PURCHASE_QTY, out string PLAN_ACCEPT_DATE, out string accept_qty, out string Qty, out string DIS_Qty) { //1.取得ITEM PERIOD商品基本資料 Item = string.Empty; Period = string.Empty; Cost = string.Empty; Price = string.Empty; TaxType = string.Empty; PURCHASE_QTY = string.Empty; PLAN_ACCEPT_DATE = string.Empty; accept_qty = string.Empty; Qty = string.Empty; DIS_Qty = string.Empty; DataTable dtResult = new DataTable(); try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); ArrayList QueryParameter = new ArrayList(); QueryParameter.Clear(); QueryParameter.Add(ItemBarCode); QueryParameter.Add(PeriodBarCode); QueryParameter.Add(PurchaseNo); dtResult = dbo.GetItemPeriod(QueryParameter); if (dtResult.Rows.Count > 0) { Item = dtResult.Rows[0]["Item"].ToString(); Period = dtResult.Rows[0]["Period"].ToString(); Cost = dtResult.Rows[0]["Cost"].ToString(); Price = dtResult.Rows[0]["Price"].ToString(); TaxType = dtResult.Rows[0]["Tax_Type"].ToString(); } else { return false; } QueryParameter.Clear(); QueryParameter.Add(Item); QueryParameter.Add(Period); QueryParameter.Add(PurchaseNo); dtResult = dbo.GetPurQtyDate(QueryParameter); if (dtResult.Rows.Count > 0) { PURCHASE_QTY = dtResult.Rows[0]["PURCHASE_QTY"].ToString(); PLAN_ACCEPT_DATE = dtResult.Rows[0]["PLAN_ACCEPT_DATE"].ToString(); } else { return false; } dtResult = dbo.GetTotalPurQty(QueryParameter); if (dtResult.Rows.Count > 0) { accept_qty = dtResult.Rows[0]["accept_qty"].ToString(); } else { //return false; accept_qty = "0"; } QueryParameter.Clear(); QueryParameter.Add(Item); QueryParameter.Add(Period); dtResult = dbo.GetTotalDisQty(QueryParameter); if (dtResult.Rows.Count > 0) { if (dtResult.Rows[0]["Qty"].ToString() == "") { Qty = "0"; } else { Qty = dtResult.Rows[0]["Qty"].ToString(); } if (dtResult.Rows[0]["DIS_Qty"].ToString() == "") { DIS_Qty = "0"; } else { DIS_Qty = dtResult.Rows[0]["DIS_Qty"].ToString(); } } else { //return false; Qty = "0"; DIS_Qty = "0"; } return true; } catch (Exception ex) { throw ex; } }
//VGI013.aspx #region 查詢方法 //取得檢驗項目 public DataTable QueryItemCK() { try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); return dbo.GetItemCK(); } catch (Exception ex) { throw ex; } }
public void UpdateGiftProfile_2(string UserID, string UpdateDate, int accept_days, int out_days, ArrayList ParameterList, DbTransaction RootDBT) { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); bool IsRootTranscation = false; try { //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion ArrayList SQLParameters = new ArrayList(); SQLParameters.Clear(); SQLParameters.Add(UserID); //0 string SQLParameters.Add(UpdateDate); //1 string SQLParameters.Add(ParameterList[0]); //2 string SQLParameters.Add(ParameterList[1]); //3 string SQLParameters.Add(ParameterList[2]); //4 string SQLParameters.Add(ParameterList[3]); //5 int 一包裝數 SQLParameters.Add(ParameterList[4]); //6 double 3-10 共八個都是 double (SIZE) SQLParameters.Add(ParameterList[5]); //7 double SQLParameters.Add(ParameterList[6]); //8 double SQLParameters.Add(ParameterList[7]); //9 double SQLParameters.Add(ParameterList[8]); //10 double SQLParameters.Add(ParameterList[9]); //11 double SQLParameters.Add(ParameterList[10]); //12 double SQLParameters.Add(ParameterList[11]); //13 double SQLParameters.Add(ParameterList[12]); //14 string SQLParameters.Add(ParameterList[13]); //15 int SQLParameters.Add(accept_days); //16 int SQLParameters.Add(out_days); //17 int dbo.UpdateMktGiftProfile_2(SQLParameters, DBT); } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } }
public string CheckItemApprove(string Item, string Period) { try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); ArrayList QueryParameter = new ArrayList(); QueryParameter.Clear(); QueryParameter.Add(Item); QueryParameter.Add(Period); DataTable dtResult = dbo.GetItemApprove(QueryParameter); if (dtResult.Rows[0]["Acct_Validate"].ToString() != "1") //未核准 { return "1"; } string dateNow = DateTime.Now.ToString("yyyyMMdd"); string EndDate = dtResult.Rows[0]["Period_End_Date"].ToString(); if (EndDate == "") { return "0"; } if (int.Parse(EndDate) < int.Parse(dateNow)) //過期 { return "2"; } return "0"; } catch (Exception ex) { throw ex; } }
//0.ChanTransNo,1.CreateID,2.CreateDate //Datatable 欄位 0.item,1.period public bool CreateTransIn(string ChanTransNo,string PurchaseNo, string CreateID, string CreateDate, DataTable dtTransIn, DbTransaction RootDBT,out DataTable dtItem,out DataTable dtGift) { dtItem = null; dtGift = null; bool IsRootTranscation = false; int PID = -1; string TranNum = ""; ArrayList ParameterList = new ArrayList(); //取得並UPDATE DATATABLE的內容 dtItem = MaterDetl(); dtGift = GIftDetl(); try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); DBO.MaintainAccept bco = new DBO.MaintainAccept(strConn); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion //寫入LOG檔 //LOG 主檔 ParameterList.Clear(); ParameterList.Add(ChanTransNo); ParameterList.Add(dtTransIn.Rows[0][0].ToString()); ParameterList.Add(dtTransIn.Rows[0][1].ToString()); ParameterList.Add(CreateID); ParameterList.Add(CreateDate); ParameterList.Add(dtTransIn.Rows.Count); ParameterList.Add(dtTransIn.Rows[0][2].ToString()); dbo.CreateDataTransMain(ParameterList, DBT, out PID, out TranNum); for (int i = 0; i < dtTransIn.Rows.Count; i++) { ParameterList.Clear(); ParameterList.Add(PID); ParameterList.Add(ChanTransNo); ParameterList.Add(dtTransIn.Rows[i][0].ToString()); ParameterList.Add(dtTransIn.Rows[i][1].ToString()); ParameterList.Add(CreateID); ParameterList.Add(CreateDate); ParameterList.Add(dtTransIn.Rows[i][2].ToString()); dbo.CreateDataTransDetl(ParameterList, DBT); } ParameterList.Clear(); ParameterList.Add(ChanTransNo); ParameterList.Add(CreateID); DataTable dtTemp = dbo.GetTempSourceData(ParameterList); for (int i = 0; i < dtTemp.Rows.Count; i++) { bool haveData = false; for (int j = 0; j < dtTransIn.Rows.Count; j++) { string InItem = dtTransIn.Rows[j][0].ToString(); string InPeriod = dtTransIn.Rows[j][1].ToString(); string InChanSourceNo = dtTransIn.Rows[j][2].ToString(); if (InItem == dtTemp.Rows[i]["item"].ToString() && InPeriod == dtTemp.Rows[i]["period"].ToString() && InChanSourceNo == dtTemp.Rows[i]["chan_source_no"].ToString()) { haveData = true; } } if (haveData == true) { string BarCode = dtTemp.Rows[i]["barcode"].ToString(); string ItemCode = dtTemp.Rows[i]["ItemCode"].ToString(); string PeriodCode = dtTemp.Rows[i]["PeriodCode"].ToString(); string Item = dtTemp.Rows[i]["item"].ToString(); string period = dtTemp.Rows[i]["period"].ToString(); string Acc_Qty = dtTemp.Rows[i]["trans_qty"].ToString(); string ItemName = dtTemp.Rows[i]["ItemName"].ToString(); string package_unit = dtTemp.Rows[i]["package_unit"].ToString(); string s_Weight = dtTemp.Rows[i]["s_Weight"].ToString(); string s_length = dtTemp.Rows[i]["s_length"].ToString(); string s_width = dtTemp.Rows[i]["s_width"].ToString(); string s_height = dtTemp.Rows[i]["s_height"].ToString(); string p_weight = dtTemp.Rows[i]["p_weight"].ToString(); string p_length = dtTemp.Rows[i]["p_length"].ToString(); string p_width = dtTemp.Rows[i]["p_width"].ToString(); string p_height = dtTemp.Rows[i]["p_height"].ToString(); string Product_Source = dtTemp.Rows[i]["Product_Source"].ToString(); string Produce_Date = dtTemp.Rows[i]["Produce_Date"].ToString(); string Reserved_Period = dtTemp.Rows[i]["Reserved_Period"].ToString(); string Valid_Date = dtTemp.Rows[i]["Valid_Date"].ToString(); string AllowAccept_Date = dtTemp.Rows[i]["AllowAccept_Date"].ToString(); string Pitem = ""; string Pperiod = ""; string Cost = ""; string Price = ""; string TaxType = ""; string PurchaseQty = ""; string PlantAcceptDate = ""; string AcceptyQty = ""; string Qty = ""; string DisQty = ""; //if (BarCode.Length == 8 || BarCode.Length == 13) //{ // ItemCode = BarCode; // PeriodCode = "0000000000000"; //} //else if (BarCode.Length == 15) //{ // ItemCode = BarCode.Substring(0, 13); // PeriodCode = BarCode.Substring(13, 2); //} //else if (BarCode.Length == 18) //{ // ItemCode = BarCode.Substring(0, 13); // PeriodCode = BarCode.Substring(13, 5); //} bco.QueryItemData(ItemCode, PeriodCode, PurchaseNo, out Pitem, out Pperiod, out Cost, out Price, out TaxType, out PurchaseQty, out PlantAcceptDate, out AcceptyQty, out Qty, out DisQty); DataRow drItem = dtItem.NewRow(); drItem["barcode"] = BarCode; drItem["ItemCode"] = ItemCode; drItem["PeriodCode"] = PeriodCode; //2 drItem["item"] = Item; //品號 drItem["period"] = period; //期別 drItem["ItemName"] = ItemName; //5 drItem["purchase_Qty"] = PurchaseQty; //採購數量 drItem["In_Date"] = PlantAcceptDate; //預進日期 drItem["TAcc_Qty"] = Qty; //總驗收數 drItem["Acc_Qty"] = Acc_Qty; //驗收數量 drItem["Cost"] = Cost; //成本價 drItem["Price"] = Price; //零售價 drItem["TaxType"] = TaxType; //12 稅別 drItem["Package_Unit"] = package_unit; //1包裝數 drItem["S_Weight"] = s_Weight; //單品重量 drItem["S_Length"] = s_length; //單品長度 drItem["S_Width"] = s_width; //單品寬度 drItem["S_Height"] = s_height; //單品高度 drItem["P_Weight"] = p_weight; //包裝重量 drItem["P_Length"] = p_length; //包裝長度 drItem["P_Width"] = p_width; //包裝寬度 drItem["P_Height"] = p_height; //包裝高度 drItem["Product_Source"] = Product_Source; //商品來源 drItem["Produce_Date"] = Produce_Date; //製造日期 drItem["Reserved_Period"] = Reserved_Period; //保存期限 drItem["Valid_Date"] = Valid_Date; //有效日期 drItem["AllowAccept_Date"] = AllowAccept_Date; //允收日期 dtItem.Rows.Add(drItem); DataTable dtGiftTemp = bco.QueryGift(Item, period, PurchaseNo); for (int x = 0; x < dtGiftTemp.Rows.Count; x++) { DataRow drGift = dtGift.NewRow(); drGift["GiftName"] = dtGiftTemp.Rows[x]["GiftName"].ToString(); drGift["Barcode"] = BarCode; //0商品條碼 drGift["Item"] = Item; //1.品號 drGift["Period"] = period; //2.期別 drGift["Virtual_Code"] = dtGiftTemp.Rows[x]["VIRTUAL_CODE"].ToString(); //3.虛擬品號 drGift["purchase_Qty"] = dtGiftTemp.Rows[x]["PURCHASE_QTY"].ToString(); //4.採購數量 drGift["TAcc_Qty"] = dtGiftTemp.Rows[x]["accept_accqty"].ToString(); //X總驗收數 drGift["Accept_Qty"] = "0"; //5.驗收數量 drGift["Package_Unit"] = "0"; //6.1包裝數 drGift["S_Weight"] = "0"; //7.單品重量 drGift["S_Length"] = "0"; //8.單品長度 drGift["S_Width"] = "0";//9.單品寬度 drGift["S_Height"] = "0";//10.單品高度 drGift["P_Weight"] = "0"; //11.包裝重量 drGift["P_Length"] = "0"; //12.包裝長度 drGift["P_Width"] = "0";//13.包裝寬度 drGift["P_Height"] = "0"; //14.包裝高度 } } } //刪除暫存資料 ParameterList.Clear(); ParameterList.Add(ChanTransNo); ParameterList.Add(CreateID); dbo.DeleteDataTransTemp(ParameterList, DBT); #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion return true; } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
public DataTable QueryAcceptGifts(ArrayList ParameterList) { try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); ArrayList QueryParameter = new ArrayList(); return dbo.GetAcceptGifts(ParameterList); } catch (Exception ex) { throw ex; } }
public DataTable GetSourcetData(ArrayList ParameterList,DbTransaction RootDBT) { bool IsRootTranscation = false; try { #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); string ChanTransNo = ParameterList[0].ToString(); string UserID = ParameterList[1].ToString(); ArrayList DelList = new ArrayList(); DelList.Add(ChanTransNo); DelList.Add(UserID); dbo.DeleteDataTransTemp(DelList, RootDBT); DataTable dtResult = dbo.GetSourcetData(ParameterList); #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion return dtResult; } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
/// <summary> /// 取得允收日期 /// </summary> /// <param name="StartDate">製造日期</param> /// <param name="Days">保存期限</param> /// <param name="EndDate">有效日期</param> /// <param name="Source">商品來源</param> /// <param name="ReceiveDate">允收日期</param> /// <returns></returns> public bool QueryAcceptSituation(string StartDate, string Days, string EndDate, string Source, out string ReceiveDate) { ReceiveDate = ""; try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); ArrayList QueryParameter = new ArrayList(); QueryParameter.Clear(); QueryParameter.Add(int.Parse(Source)); QueryParameter.Add(int.Parse(Days)); DataTable dtResult = dbo.GetAcceptSituation(QueryParameter); if (dtResult.Rows.Count == 0) { ReceiveDate = ""; return false; } else { DateTime dtStart = DateTime.ParseExact(StartDate, "yyyy/MM/dd", null); DateTime dtEnd = DateTime.ParseExact(EndDate, "yyyy/MM/dd", null); if (dtResult.Rows[0]["CAL_TYPE"].ToString() == "S") { ReceiveDate = dtStart.AddDays(int.Parse(dtResult.Rows[0]["ALLOWACCEPT_DAYS"].ToString())).ToString("yyyy/MM/dd"); } else if (dtResult.Rows[0]["CAL_TYPE"].ToString() == "E") { //ReceiveDate = dtStart.AddDays(int.Parse(dtResult.Rows[0]["ALLOWACCEPT_DAYS"].ToString()) + 1).ToString("yyyy/MM/dd"); ReceiveDate = dtEnd.AddDays(((int.Parse(dtResult.Rows[0]["ALLOWACCEPT_DAYS"].ToString())) * (-1)) + 1).ToString("yyyy/MM/dd"); } } return true; } catch (Exception ex) { throw ex; } }
///取得採購單DETL資料 //string PurchaseNo,string Item,string Period ,string VirtualCode public DataRow GetPurchaseOrderDetail(ArrayList ParameterList) { DataRow dr = null; try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); DataTable dtResult = dbo.GetPurchaseOrderDetail(ParameterList); if (dtResult.Rows.Count == 1) { dr = dtResult.Rows[0]; } else { throw new Exception("查無相關採購資料可供更新"); } return dr; } catch (Exception ex) { throw ex; } }
//新增模式使用 public bool QueryPurchaseMaster(string ItemBarCode, string PeriodBarCode, out string PurchaseNo, out string Vendor) { PurchaseNo = string.Empty; Vendor = string.Empty; try { DBO.MaintainAcceptDBO dbo = new DBO.MaintainAcceptDBO(ref USEDB); ArrayList QueryParameter = new ArrayList(); QueryParameter.Clear(); QueryParameter.Add(ItemBarCode); QueryParameter.Add(PeriodBarCode); DataTable dtResult = dbo.QueryPurMaster(QueryParameter); if (dtResult.Rows.Count > 0) { PurchaseNo = dtResult.Rows[0]["purchase_no"].ToString(); Vendor = dtResult.Rows[0]["vendor"].ToString(); return true; } else { PurchaseNo = ""; Vendor = ""; return false; } } catch (Exception ex) { throw ex; } }