public DataTable QuerySwitch(QueryType QT, ArrayList ParameterList ) { DBO.RemoveDataDetail dbo = new RemoveDataDetail(ref USEDB); DataTable Dt; try { switch (QT) { case QueryType.ALL: Dt = dbo.doQueryAll(); break; case QueryType.ID: Dt = dbo.doQueryByID(ParameterList); break; case QueryType.Custom: Dt = dbo.doQueryByFind(ParameterList); break; default: Dt = new DataTable(); break; } return Dt; } catch (Exception ex) { throw ex; } }
public DataTable QueryRemoveDataDetailByLike(ArrayList ParameterList) { try { DBO.RemoveDataDetail dbo = new RemoveDataDetail(ref USEDB); return dbo.doQueryByLIKE(ParameterList); } catch (Exception ex) { throw ex; } }
public DataTable QueryRemoveDataDetailByALL() { try { DBO.RemoveDataDetail dbo = new RemoveDataDetail(ref USEDB); return dbo.doQueryAll(); } catch (Exception ex) { throw ex; } }
public DataTable QueryRemoveDetlStockQty(ArrayList ParameterList) { try { DBO.RemoveDataDetail dbo = new RemoveDataDetail(ref USEDB); return dbo.QueryRemoveDetlStockQty(ParameterList); } catch (Exception ex) { throw ex; } }
public string GetItemName(string Item) { try { DBO.RemoveDataDetail dbo = new RemoveDataDetail(ref USEDB); return dbo.GetItemName(Item.Trim()); } catch (Exception ex) { throw ex; } }
/// <summary> /// 測試SP交易失敗 /// </summary> /// <param name="ParameterList">變數</param> /// <param name="RootDBT">是否有主交易,無主交易輸入null</param> public void TestTranscation(ArrayList ParameterList1, ArrayList ParameterList2, ArrayList ParameterList3, DbTransaction RootDBT ) { bool IsRootTranscation = false; try { DBO.RemoveDataDetail dbo = new RemoveDataDetail(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 dbo.doCreate(ParameterList1, DBT); dbo.doUpdate(ParameterList2, DBT); dbo.doDelete(ParameterList3, DBT); throw new Exception("交易失敗"); #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 bool GetItemPeriodCost(string Item, string Period, out double Cost) { try { DBO.RemoveDataDetail dbo = new RemoveDataDetail(ref USEDB); return dbo.QueryItemPeriodCost(Item,Period,out Cost); } catch (Exception ex) { throw ex; } }
public DataTable QueryRemoveDetlEmpty() { try { DBO.RemoveDataDetail dbo = new RemoveDataDetail(ref USEDB); return dbo.QueryRemoveDetlEmpty(); } catch (Exception ex) { throw ex; } }
/// <summary> /// 基本表單新增BCO /// </summary> /// <param name="ParameterList">輸入變數</param> /// 0.UserID /// 1.RemoveDate /// 2.RemoveReason /// 3.CreateDate /// <param name="dtDetail"> /// 0.Out_Locate_No /// 1.Out_Locate_Section /// 2.In_Locate_Section /// 3.Out_Locate_Section /// 4.Item /// 5.Period /// 6.Remove_Qty /// 7.Cost /// </param> /// <param name="RootDBT">是否有主交易,無主交易輸入null</param> /// <returns>回傳影響筆數</returns> public bool CreateRemoveData(ArrayList ParameterList, DataTable dtDetail, DbTransaction RootDBT, out string MainID, out string RemoveNo, out ArrayList ErrList) { //是否還有儲位 bool HaveSpace = true; bool HaveSpaceLocNo = true; MainID = ""; RemoveNo = ""; int PID = 0; string UserID = ParameterList[0].ToString(); string RemoveDate = ParameterList[1].ToString(); string RemoveReason = ParameterList[2].ToString(); bool IsRootTranscation = false; //AP傳入的現在時間 string strDateNow = DateTime.Now.ToString("yyyyMMddHHmmss"); ArrayList ItemQtyList = new ArrayList(); ErrList = new ArrayList(); try { DBO.RemoveData dbo = new RemoveData(ref USEDB); DBO.RemoveDataDetail dboDetail = new RemoveDataDetail(ref USEDB); DBO.VDS_IVM025_DBO dboUpdateStock = new VDS_IVM025_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 //建立新的DATATABLE儲存SUMMARY的移出儲位及儲位數量 DataTable dtSpace = new DataTable(); dtSpace.Columns.Add("OutLocateSection"); dtSpace.Columns.Add("InLocateSection"); //充數欄位 dtSpace.Columns.Add("Item"); dtSpace.Columns.Add("Period"); dtSpace.Columns.Add("Remove_Qty"); //建立新的DATATABLE儲存SUMMARY的移出儲區及儲區可移轉數量 DataTable dtLocateNo = new DataTable(); dtLocateNo.Columns.Add("OutLocateNo"); dtLocateNo.Columns.Add("Item"); dtLocateNo.Columns.Add("Period"); dtLocateNo.Columns.Add("Remove_Qty"); //計算移出欄位數量,並加總 //ParameterList數量 = 4 代表前面已經檢查過數量了 if (ParameterList.Count != 4) { for (int i = 0; i < dtDetail.Rows.Count; i++) { string OutLocateNo = dtDetail.Rows[i][0].ToString(); string OutLocateSection = dtDetail.Rows[i][1].ToString(); string InLocateNo = dtDetail.Rows[i][2].ToString(); string InLocateSection = ""; string Item = dtDetail.Rows[i][4].ToString(); string Period = dtDetail.Rows[i][5].ToString(); string RemoveQty = dtDetail.Rows[i][6].ToString(); #region 處理儲位數量判斷資料 bool HaveSec = false; int U = -1; for (int x = 0; x < dtSpace.Rows.Count; x++) { if (OutLocateSection == dtSpace.Rows[x]["OutLocateSection"].ToString() && Item == dtSpace.Rows[x]["Item"].ToString() && Period == dtSpace.Rows[x]["Period"].ToString()) { HaveSec = true; U = x; } } if (HaveSec == true) { dtSpace.Rows[U]["Remove_Qty"] = int.Parse(dtSpace.Rows[U]["Remove_Qty"].ToString()) + int.Parse(RemoveQty); } else { DataRow drSpace = dtSpace.NewRow(); drSpace["OutLocateSection"] = OutLocateSection; drSpace["InLocateSection"] = InLocateSection; drSpace["Item"] = Item; drSpace["Period"] = Period; drSpace["Remove_Qty"] = RemoveQty; dtSpace.Rows.Add(drSpace); } #endregion #region 處理儲區數量判斷資料 bool HaveNo = false; int N = -1; //不同儲區間移動才要判斷配本保留量 if (OutLocateNo != InLocateNo) { for (int x = 0; x < dtLocateNo.Rows.Count; x++) { if (OutLocateNo == dtLocateNo.Rows[x]["OutLocateNo"].ToString() && Item == dtLocateNo.Rows[x]["Item"].ToString() && Period == dtLocateNo.Rows[x]["Period"].ToString()) { HaveNo = true; N = x; } } if (HaveNo == true) { dtLocateNo.Rows[N]["Remove_Qty"] = int.Parse(dtLocateNo.Rows[N]["Remove_Qty"].ToString()) + int.Parse(RemoveQty); } else { DataRow drLocateNo = dtLocateNo.NewRow(); drLocateNo["OutLocateNo"] = OutLocateNo; drLocateNo["Item"] = Item; drLocateNo["Period"] = Period; drLocateNo["Remove_Qty"] = RemoveQty; dtLocateNo.Rows.Add(drLocateNo); } } #endregion } #region 驗證是否有空間 HaveSpace = CheckSpace(dtSpace, out ErrList, out ItemQtyList); if (HaveSpace) { HaveSpaceLocNo = CheckSpaceLocNo(dtLocateNo, out ErrList); } if (HaveSpace == false || HaveSpaceLocNo==false) { if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } return false; } dtDetail.Columns.Remove("Out_locate_no"); dtDetail.Columns.Remove("In_locate_no"); } #endregion #region 建立移轉單主檔 ArrayList RemoveMainList = new ArrayList(); RemoveMainList.Clear(); RemoveMainList.Add(UserID); RemoveMainList.Add(RemoveDate); RemoveMainList.Add(RemoveReason); RemoveMainList.Add(strDateNow); dbo.CreateRemoveMain(RemoveMainList, DBT, out PID, out RemoveNo); MainID = PID.ToString(); #endregion #region 建立移轉單明細檔&更新庫存量 ArrayList RemoveDetailList = new ArrayList(); string CheckFreeze_Flag = "1"; for (int i = 0; i < dtDetail.Rows.Count; i++) { //建立移轉單明細檔 RemoveDetailList.Clear(); RemoveDetailList.Add(UserID); //0.UserID RemoveDetailList.Add(PID); //1.PID RemoveDetailList.Add(dtDetail.Rows[i][0].ToString().Substring(0, 1)); //2.Out_Locate_No RemoveDetailList.Add(dtDetail.Rows[i][0].ToString()); //3.Out_Locate_Section RemoveDetailList.Add(dtDetail.Rows[i][1].ToString().Substring(0, 1)); //4.In_Locate_No RemoveDetailList.Add(dtDetail.Rows[i][1].ToString()); //5.In_Locate_Section RemoveDetailList.Add(dtDetail.Rows[i][2].ToString()); //6.Item RemoveDetailList.Add(dtDetail.Rows[i][3].ToString()); //7.Period RemoveDetailList.Add(int.Parse(dtDetail.Rows[i][4].ToString())); //8.RemoveQty RemoveDetailList.Add(double.Parse(dtDetail.Rows[i][5].ToString())); //9.Cost RemoveDetailList.Add(strDateNow); //10 dboDetail.CreateRemoveDetl(RemoveDetailList, DBT); //更新庫存量CALL DoUpdate ArrayList UpdateParameter = new ArrayList(); //更新移出標籤 UpdateParameter.Clear(); UpdateParameter.Add(CheckFreeze_Flag); UpdateParameter.Add(dtDetail.Rows[i][0].ToString()); UpdateParameter.Add(dtDetail.Rows[i][0].ToString().Substring(0, 1)); UpdateParameter.Add(dtDetail.Rows[i][2].ToString()); UpdateParameter.Add(dtDetail.Rows[i][3].ToString()); UpdateParameter.Add("O"); UpdateParameter.Add(int.Parse(dtDetail.Rows[i][4].ToString())); UpdateParameter.Add(UserID); UpdateParameter.Add(""); UpdateParameter.Add(""); dboUpdateStock.UpdateINVQty(UpdateParameter, DBT); //更新移入數量 UpdateParameter.Clear(); UpdateParameter.Add(CheckFreeze_Flag); UpdateParameter.Add(dtDetail.Rows[i][1].ToString()); UpdateParameter.Add(dtDetail.Rows[i][1].ToString().Substring(0, 1)); UpdateParameter.Add(dtDetail.Rows[i][2].ToString()); UpdateParameter.Add(dtDetail.Rows[i][3].ToString()); UpdateParameter.Add("I"); UpdateParameter.Add(int.Parse(dtDetail.Rows[i][4].ToString())); UpdateParameter.Add(UserID); UpdateParameter.Add(""); UpdateParameter.Add(""); dboUpdateStock.UpdateINVQty(UpdateParameter, DBT); } #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 } }
//於7/14新增取得庫存資料成本 public bool QueryItemPeriodCost(string Item, string Period, out Double Cost) { try { bool HaveDate = false; Cost = 0; DBO.RemoveDataDetail dbo = new DBO.RemoveDataDetail(ref USEDB); HaveDate = dbo.QueryItemPeriodCost(Item, Period, out Cost); if (Cost != 0) { return true; } else { return false; } } catch (Exception ex) { throw ex; } }