/// <summary> /// 新增在途單主檔(VDS_PUR_ONWAY_MAIN)及明細檔(VDS_PUR_ONWAY_ITEM)資料 /// * 處理原則: /// 1.在途單新增時,需call CheckVendorAccountClose.CheckVendorCloseStatus(在途日期) , /// 檢查在途日期是否已進行供應商帳務日結,若已日結,則需顯示訊息"在途日XXX已日結,不可新增在途單"。 /// /// 2.在途單號第一碼於代號控制檔取得,'E'+YMMDD+XXXX /// /// 3.若在途商品為正常品,Call this.UpdatePeriodInTransitData(品號,期別, /// 在途量)更新期別屬性檔(VDS_PERIOD_PROFILE)的下列欄位:在途量(Onway_Qty) /// /// 4.若採購商品為贈品,則Call this.UpdateGiftInTransitData(品號,期別,虛擬品號, /// 在途量)更新贈品屬性檔(VDS_MKT_GIFT_PERIOD)的在途量資料 /// /// 5.新增時,預設ONWAY_QTY(在途數量) = PETITION_QTY(申請數量) /// 6.若新增不成功則全部rollback /// </summary> public bool CreateGoodsInTransit(ArrayList ParameterList, out string ONWAY_NO, DbTransaction RootDBT) { bool IsRootTranscation = false; int iResult = 0; bool bResult = false; bool bAllClose = true; string strUpdateQty = "0"; try { PUR_05_DBO DBO = new PUR_05_DBO(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 //0.Item DataTable //1.Gift DataTable //2.VENDOR //3.PURCHASE_NO //4.ONWAY_DATE //5.CREATEDATE //6.CREATEUID //7.UPDATEDATE //8.UPDATEUID //9.STATUS //out ONWAY_NO ArrayList Para = new ArrayList(); DataTable dtItem = (DataTable)ParameterList[0]; DataTable dtGift = (DataTable)ParameterList[1]; string strVENDOR = ParameterList[2].ToString(); string strPURCHASE_NO = ParameterList[3].ToString(); string strONWAY_DATE = ParameterList[4].ToString().Substring(0, 10); string strCREATEDATE = ParameterList[5].ToString(); string strCREATEUID = ParameterList[6].ToString(); string strUPDATEDATE = ParameterList[7].ToString(); string strUPDATEUID = ParameterList[8].ToString(); string strSTATUS = ParameterList[9].ToString(); //主檔儲存 Para.Clear(); Para.Add(strVENDOR); //0 Para.Add(strPURCHASE_NO); //1 Para.Add(strONWAY_DATE); //2 Para.Add(strCREATEDATE); //3 Para.Add(strCREATEUID); //4 Para.Add(strUPDATEDATE); //5 Para.Add(strUPDATEUID); //6 Para.Add(strSTATUS); //7 string strID = string.Empty; iResult = DBO.CreateMain(DBT, Para, out ONWAY_NO, out strID); //0.PID //1.ONWAY_NO //2.ITEM //3.PERIOD //4.VIRTUAL_CODE //5.PETITION_QTY //6.ONWAY_QTY //7.CREATEDATE //8.CREATEUID //9.UPDATEDATE //10.UPDATEUID //11.CLOSE_DATE //12.CLOSE_TYPE //一般Item儲存 foreach (DataRow dr in dtItem.Rows) { if (dr["ENABLE"].ToString() == "1") { Para.Clear(); Para.Add(strID); //0 Para.Add(ONWAY_NO); //1 Para.Add(dr["ITEM"].ToString()); //2 Para.Add(dr["PERIOD"].ToString()); //3 Para.Add(dr["VIRTUAL_CODE"].ToString());//4 Para.Add(dr["PETITION_QTY"].ToString());//5 Para.Add(dr["ONWAY_QTY"].ToString()); //6 Para.Add(strCREATEDATE); //7 Para.Add(strCREATEUID); //8 Para.Add(strUPDATEDATE); //9 Para.Add(strUPDATEUID); //10 Para.Add(dr["CLOSE_DATE"].ToString()); //11 Para.Add(GetStatus(dr["CLOSE_TYPE"].ToString().Trim())); //12 if (dr["CLOSE_TYPE"].ToString().Trim() == "") bAllClose = false; iResult = DBO.CreateItem(DBT, Para); if (GetStatus(dr["CLOSE_TYPE"].ToString().Trim()) == "") strUpdateQty = dr["ONWAY_QTY"].ToString(); else strUpdateQty = "0"; UpdatePeriodInTransitData(DBO, DBT, dr["ITEM"].ToString(), dr["PERIOD"].ToString(), strUpdateQty, strUPDATEDATE, strUPDATEUID); } } //贈品儲存 foreach (DataRow dr in dtGift.Rows) { if (dr["ENABLE"].ToString() == "1") { Para.Clear(); Para.Add(strID); //0 Para.Add(ONWAY_NO); //1 Para.Add(dr["ITEM"].ToString()); //2 Para.Add(dr["PERIOD"].ToString()); //3 Para.Add(dr["VIRTUAL_CODE"].ToString());//4 Para.Add(dr["PETITION_QTY"].ToString());//5 Para.Add(dr["ONWAY_QTY"].ToString()); //6 Para.Add(strCREATEDATE); //7 Para.Add(strCREATEUID); //8 Para.Add(strUPDATEDATE); //9 Para.Add(strUPDATEUID); //10 Para.Add(dr["CLOSE_DATE"].ToString()); //11 Para.Add(GetStatus(dr["CLOSE_TYPE"].ToString().Trim())); //12 if (dr["CLOSE_TYPE"].ToString().Trim() == "") bAllClose = false; iResult = DBO.CreateItem(DBT, Para); if (GetStatus(dr["CLOSE_TYPE"].ToString().Trim()) == "") strUpdateQty = dr["ONWAY_QTY"].ToString(); else strUpdateQty = "0"; UpdateGiftInTransitData(DBO, DBT, dr["ITEM"].ToString(), dr["PERIOD"].ToString(), dr["VIRTUAL_CODE"].ToString(), strUpdateQty, strUPDATEDATE, strUPDATEUID); } } //假如細項都結案的話 主檔也要結案 if (bAllClose == true) { //0.V_ONWAY_NO //1.V_STATUS //2.V_ONWAY_DATE //3.V_UPDATEUID //4.V_UPDATEDATE //5.V_OLD_UPDATEUID //6.V_OLD_UPDATEDATE //7.V_ENABLE Para.Clear(); Para.Add(ONWAY_NO); //0 Para.Add("75"); //1 Para.Add(strONWAY_DATE); //2 Para.Add(strUPDATEUID); //3 Para.Add(strUPDATEDATE); //4 Para.Add(strUPDATEUID); //5 Para.Add(strUPDATEDATE); //6 Para.Add("1"); //7 iResult = DBO.UpdateMain(DBT, Para); } #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); bResult = true; } #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 } return bResult; }
/// <summary> /// 1:目的:在途單結案 /// 2. 傳入參數:[@在途單號],[@結案方式] /// /// 3:table:VDS_PUR_ONWAY_MAIN,VDS_PUR_ONWAY_ITEM /// /// 4:處理方式 /// ●.當人工於畫面進行結案時,將在途單的狀態改為"結案"狀態(VDS_PUR_ONWAY_MAIN.status='75'), /// 同時將明細資料(VDS_PUR_ONWAY_ITEM)的所有狀態(close_type)改為"手動"('M')。 /// /// 5.若在途商品為正常品,Call MaintainPeriodProfile.UpdatePeriodInTransitData(品號,期別, /// 在途量=0)更新期別屬性檔(VDS_ITM_PERIOD_PROFILE)的下列欄位:在途量(Onway_Qty) /// /// 6.若採購商品為贈品,則Call MaintainGiftProfile.UpdateGiftInTransitData(品號,期別,虛擬品號, /// 在途量=0)更新贈品屬性檔(VDS_MKT_GIFT_PERIOD)的在途量資料 /// </summary> public bool CloseGoodsInTransit(ArrayList ParameterList) { bool IsRootTranscation = false; DbTransaction RootDBT = null; int iResult = 0; bool bResult = false; try { PUR_05_DBO DBO = new PUR_05_DBO(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 //0.Item DataTable //1.Gift DataTable //2.ONWAY_NO //3.ONWAY_DATE //4.UPDATEDATE //5.UPDATEUID //6.STATUS //7.OLD_UPDATEDATE //8.OLD_UPDATEUID ArrayList Para = new ArrayList(); DataTable dtItem = (DataTable)ParameterList[0]; DataTable dtGift = (DataTable)ParameterList[1]; string strONWAY_NO = ParameterList[2].ToString(); string strONWAY_DATE = ParameterList[3].ToString().Substring(0, 10); string strUPDATEDATE = ParameterList[4].ToString(); string strUPDATEUID = ParameterList[5].ToString(); string strSTATUS = ParameterList[6].ToString(); string strOLD_UPDATEDATE = ParameterList[7].ToString(); string strOLD_UPDATEUID = ParameterList[8].ToString(); //主檔更新 //0.V_ONWAY_NO //1.V_STATUS //2.V_ONWAY_DATE //3.V_UPDATEUID //4.V_UPDATEDATE //5.V_OLD_UPDATEUID //6.V_OLD_UPDATEDATE //7.V_ENABLE Para.Clear(); Para.Add(strONWAY_NO); //0 Para.Add(strSTATUS); //1 Para.Add(strONWAY_DATE); //2 Para.Add(strUPDATEUID); //3 Para.Add(strUPDATEDATE); //4 Para.Add(strOLD_UPDATEUID); //5 Para.Add(strOLD_UPDATEDATE); //6 Para.Add("1"); //7 string strID = string.Empty; iResult = DBO.UpdateMain(DBT, Para); //一般Item結案 foreach (DataRow dr in dtItem.Rows) { if (dr["CLOSE_TYPE"].ToString() == "") { //0.ONWAY_NO //1.ITEM //2.PERIOD //3.VIRTUAL_CODE //4.PETITION_QTY //5.ONWAY_QTY //6.CLOSE_DATE //7.CLOSE_TYPE //8.UPDATEUID //9.UPDATEDATE //10.OLD_UPDATEUID //11.OLD_UPDATEDATE //Update Para.Clear(); Para.Add(strONWAY_NO); //0 Para.Add(dr["ITEM"].ToString()); //1 Para.Add(dr["PERIOD"].ToString()); //2 Para.Add(dr["VIRTUAL_CODE"].ToString());//3 Para.Add(dr["PETITION_QTY"].ToString());//4 Para.Add(dr["ONWAY_QTY"].ToString()); //5 Para.Add(strUPDATEDATE.Substring(0, 10)); //6 Para.Add("M"); //7 Para.Add(strUPDATEUID); //8 Para.Add(strUPDATEDATE); //9 Para.Add(dr["UPDATEUID"].ToString()); //10 Para.Add(dr["UPDATEDATE"].ToString()); //11 iResult = DBO.UpdateItem(DBT, Para); UpdatePeriodInTransitData(DBO, DBT, dr["ITEM"].ToString(), dr["PERIOD"].ToString(), "0", strUPDATEDATE, strUPDATEUID); } } //贈品結案 foreach (DataRow dr in dtGift.Rows) { if (dr["CLOSE_TYPE"].ToString() == "") { //0.ONWAY_NO //1.ITEM //2.PERIOD //3.VIRTUAL_CODE //4.PETITION_QTY //5.ONWAY_QTY //6.CLOSE_DATE //7.CLOSE_TYPE //8.UPDATEUID //9.UPDATEDATE //10.OLD_UPDATEUID //11.OLD_UPDATEDATE //Update Para.Clear(); Para.Add(strONWAY_NO); //0 Para.Add(dr["ITEM"].ToString()); //1 Para.Add(dr["PERIOD"].ToString()); //2 Para.Add(dr["VIRTUAL_CODE"].ToString());//3 Para.Add(dr["PETITION_QTY"].ToString());//4 Para.Add(dr["ONWAY_QTY"].ToString()); //5 Para.Add(strUPDATEDATE.Substring(0, 10)); //6 Para.Add("M"); //7 Para.Add(strUPDATEUID); //8 Para.Add(strUPDATEDATE); //9 Para.Add(dr["UPDATEUID"].ToString()); //10 Para.Add(dr["UPDATEDATE"].ToString()); //11 iResult = DBO.UpdateItem(DBT, Para); UpdateGiftInTransitData(DBO, DBT, dr["ITEM"].ToString(), dr["PERIOD"].ToString(), dr["VIRTUAL_CODE"].ToString(), "0", strUPDATEDATE, strUPDATEUID); } } #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); bResult = true; } #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 } return bResult; }
/// <summary> /// 更新期別屬性檔(vds_ITM_period_profile)中,該品號期別的在途量 /// </summary> /// <param name="Item"></param> /// <param name="Period"></param> /// <param name="InTransitQty"></param> public void UpdatePeriodInTransitData(PUR_05_DBO DBO, DbTransaction DBT, string Item, string Period, string InTransitQty, string strDate, string strUID) { ArrayList Para = new ArrayList(); Para.Clear(); Para.Add(Item); //0 Para.Add(Period); //1 Para.Add(InTransitQty); //2 Para.Add(strDate); //3 Para.Add(strUID); //4 DBO.UpdatePeriodInTransitData(DBT, Para); }
//檢查採購單是否被其他未結案的在途單使用中 public DataTable CheckUsePURCHASE(ArrayList ParameterList) { try { PUR_05_DBO dbo = new PUR_05_DBO(ref USEDB); return dbo.CheckUsePURCHASE(ParameterList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 在途單贈品資料查詢 /// 傳入參數[@在途單號] /// </summary> public DataTable QueryGift(ArrayList ParameterList) { try { PUR_05_DBO dbo = new PUR_05_DBO(ref USEDB); return dbo.QueryGift(ParameterList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 1.傳入參數[@採購單號], [@品號],[@期別],[@虛擬品號],[@驗收量] /// 2.當驗收時,會呼叫此功能,以異動在途商品檔(VDS_PUR_ONWAY_ITEM)的在途數量: /// 在途數量(onway_qty)=(原在途數量-[@驗收數量]),若在途數量<0,則將在途數量設為0。 /// 3.當在途數量<=0,則將該筆在途單明細的狀態改為"結案"('A')狀態。 /// 4.當該張在途單所有明細資料皆為結案狀態時,將該筆在途單的狀態改為結案('A')狀態。 /// 5.若在途商品為正常品,Call MaintainPeriodProfile.UpdatePeriodInTransitData(品號,期別, /// 在途量)更新期別屬性檔(VDS_ITM_PERIOD_PROFILE)的下列欄位:在途量(Onway_Qty) /// /// 6.若採購商品為贈品,則Call MaintainGiftProfile.UpdateGiftInTransitData(品號,期別,虛擬品號, /// 在途量)更新贈品屬性檔(VDS_MKT_GIFT_PERIOD)的在途量資料 /// </summary> public void UpdateGoodsInTrainsitByGoodsReceive(ArrayList ParameterList,DbTransaction RootDBT) { bool IsRootTranscation = false; int iResult = 0; bool bAllClose = true; try { PUR_05_DBO DBO = new PUR_05_DBO(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 //0.[@採購單號] //1.[@品號] //2.[@期別] //3.[@虛擬品號] //4.[@驗收量] ArrayList Para = new ArrayList(); string strPURCHASE_NO = ParameterList[0].ToString().Trim(); string strITEM = ParameterList[1].ToString().Trim(); string strPERIOD = ParameterList[2].ToString().Trim(); string strVIRTUAL_CODE = ParameterList[3].ToString().Trim(); string strACCEPT_QTY = ParameterList[4].ToString().Trim(); //0.V_PURCHASE_NO Para.Clear(); Para.Add(strPURCHASE_NO); DataTable dtAllItem = DBO.QueryAllItem(Para); string strONWAY_NO = string.Empty; foreach (DataRow dr in dtAllItem.Rows) { if ( (dr["ITEM"].ToString().Trim() == strITEM) && (dr["PERIOD"].ToString().Trim() == strPERIOD) && (dr["VIRTUAL_CODE"].ToString().Trim() == strVIRTUAL_CODE) ) { //0.ONWAY_NO //1.ITEM //2.PERIOD //3.VIRTUAL_CODE //4.PETITION_QTY //5.ONWAY_QTY //6.CLOSE_DATE //7.CLOSE_TYPE //8.UPDATEUID //9.UPDATEDATE //10.OLD_UPDATEUID //11.OLD_UPDATEDATE //Update strONWAY_NO = dr["ONWAY_NO"].ToString().Trim(); Para.Clear(); Para.Add(strONWAY_NO); //0 Para.Add(dr["ITEM"].ToString()); //1 Para.Add(dr["PERIOD"].ToString()); //2 Para.Add(dr["VIRTUAL_CODE"].ToString());//3 Para.Add(dr["PETITION_QTY"].ToString());//4 // 2.當驗收時,會呼叫此功能,以異動在途商品檔(VDS_PUR_ONWAY_ITEM)的在途數量: // 在途數量(onway_qty)=(原在途數量-[@驗收數量]),若在途數量<0,則將在途數量設為0。 // 3.當在途數量<=0,則將該筆在途單明細的狀態改為"結案"('A')狀態 int iNEW_QTY = int.Parse(dr["ONWAY_QTY"].ToString()) - int.Parse(strACCEPT_QTY); string strCLOSE_TYPE = ""; string strCLOSE_DATE = ""; if (iNEW_QTY <= 0) { iNEW_QTY = 0; strCLOSE_TYPE = "A"; strCLOSE_DATE = DateTime.Now.ToString("yyyy/MM/dd"); } Para.Add(iNEW_QTY.ToString()); //5 Para.Add(strCLOSE_DATE); //6 Para.Add(strCLOSE_TYPE); //7 Para.Add(""); //8 Para.Add(""); //9 Para.Add(dr["UPDATEUID"].ToString()); //10 Para.Add(dr["UPDATEDATE"].ToString()); //11 if (strCLOSE_TYPE == "") bAllClose = false; iResult = DBO.UpdateItem(DBT, Para); UpdatePeriodInTransitData(DBO, DBT, dr["ITEM"].ToString(), dr["PERIOD"].ToString(), dr["ONWAY_QTY"].ToString(), "", ""); } else { if (dr["CLOSE_TYPE"].ToString().Trim() == "") bAllClose = false; } } //假如細項都結案的話 主檔也要結案 if (strONWAY_NO.Trim() != "") { //如果沒有在途單資料就不更新 if (bAllClose == true) { //0.V_ONWAY_NO //1.V_STATUS //2.V_ONWAY_DATE //3.V_UPDATEUID //4.V_UPDATEDATE //5.V_OLD_UPDATEUID //6.V_OLD_UPDATEDATE //7.V_ENABLE Para.Clear(); Para.Add(strONWAY_NO); //0 Para.Add("75"); //1 Para.Add(""); //2 Para.Add(""); //3 Para.Add(""); //4 Para.Add(""); //5 Para.Add(""); //6 Para.Add("1"); //7 iResult = DBO.UpdateMain(DBT, Para); } } #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 } }
/// <summary> /// 套表列印在途單報表,請參閱規格書"PUR05_在途單報表" /// </summary> public DataTable PrintGoodsInTransit(ArrayList ParameterList) { try { PUR_05_DBO dbo = new PUR_05_DBO(ref USEDB); return dbo.PrintGoodsInTransit(ParameterList); } catch (Exception ex) { throw ex; } }