public DataTable QuerySwitch(QueryType QT, ArrayList ParameterList ) { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); DataTable Dt; try { switch (QT) { case QueryType.QueryByLOAD: Dt = dbo.doQueryByLoad(ParameterList); break; case QueryType.QueryByPrint: Dt = dbo.doQueryByPrint(ParameterList); break; default: Dt = new DataTable(); break; } return Dt; } catch (Exception ex) { throw ex; } }
/// <summary> /// 取得該日期、批號是否有人在編輯 /// </summary> public DataTable QueryControlChk(ArrayList ParameterList) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); return dbo.QueryControlChk(ParameterList); } catch (Exception ex) { throw ex; } }
public DataTable QueryDeliveryGoodsByFind(ArrayList ParameterList, out string v_PICK_CNT, out string v_RESTRAIN_CNT) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); return dbo.doQueryByFind(ParameterList, out v_PICK_CNT, out v_RESTRAIN_CNT); } catch (Exception ex) { throw ex; } }
public void ChkDupData(ArrayList ParameterList, out string V_PICK_BATCH, out string V_DATATYPE) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); dbo.ChkDupData(ParameterList, out V_PICK_BATCH, out V_DATATYPE); } catch (Exception ex) { throw ex; } }
public ArrayList FileToTmp(DataSet ds_Excel, string strUID, string strPLAN_ACCEPT_DATE, string strKind, string strPICK_BATCH, string ConnectionDB, string strOrg_FileName, DataTable dtExist) { #region int i_newTable_Count = 0; ArrayList arl_Return = new ArrayList(); int i_FileToTemp_Total_count = 0; string strErrMsg = ""; string strTempMsg = ""; Int32 iErrCount = 0; ArrayList ParameterList = new ArrayList(); try { if (ds_Excel.Tables[0].Columns.Count != 4) { arl_Return.Add("ERROR"); arl_Return.Add("匯入檔案發生錯誤,請檢查EXCEL檔案格式是否正確"); return arl_Return; } if (!((ds_Excel.Tables[0].Columns[0].Caption.Trim() == "品號") && (ds_Excel.Tables[0].Columns[1].Caption.Trim() == "期別") && (ds_Excel.Tables[0].Columns[2].Caption.Trim() == "贈品品號") && (ds_Excel.Tables[0].Columns[3].Caption.Trim() == "理貨/管制"))) { arl_Return.Add("ERROR"); arl_Return.Add("匯入檔案發生錯誤,請檢查EXCEL檔案格式是否正確"); return arl_Return; } ParameterList.Clear(); DataTable dtTMP = new DataTable(); dtTMP.Columns.Add("ID"); dtTMP.Columns.Add("CREATEDATE"); dtTMP.Columns.Add("CREATEUID"); dtTMP.Columns.Add("UPDATEDATE"); dtTMP.Columns.Add("UPDATEUID"); dtTMP.Columns.Add("ITEM"); dtTMP.Columns.Add("PERIOD"); dtTMP.Columns.Add("VIRTUAL_CODE"); dtTMP.Columns.Add("PICK_SELECT"); dtTMP.Columns.Add("ERROR_MEMO"); dtTMP.Columns.Add("STATUS"); dtTMP.Columns.Add("DATATYPE"); dtTMP.Columns.Add("DATASOURCE"); dtTMP.Columns.Add("DATAKIND"); foreach (System.Data.DataRow dRow in ds_Excel.Tables[0].Rows) { iErrCount = 0; strErrMsg = ""; //檢查資料邏輯正確性 //將資料寫入 TEMP TABLE DataRow drTMP = dtTMP.NewRow(); DateTime dtNow = DateTime.Now; drTMP["ID"] = Convert.ToInt64(dtNow.ToString("yyyyMMddHHmmss") + (i_newTable_Count + 1).ToString()); drTMP["CREATEDATE"] = dtNow.ToString("yyyy/MM/dd HH:mm:ss"); drTMP["CREATEUID"] = strUID; drTMP["UPDATEDATE"] = dtNow.ToString("yyyy/MM/dd HH:mm:ss"); drTMP["UPDATEUID"] = strUID; drTMP["STATUS"] = "1";//File To TMP drTMP["DATASOURCE"] = strOrg_FileName; drTMP["DATAKIND"] = strKind; drTMP["ITEM"] = ""; drTMP["PERIOD"] = ""; drTMP["VIRTUAL_CODE"] = ""; drTMP["PICK_SELECT"] = ""; drTMP["ERROR_MEMO"] = ""; drTMP["DATATYPE"] = ""; string strITEM = dRow[0].ToString().Trim(); //品號 string strPERIOD = dRow[1].ToString().Trim(); //期別 string strVIRTUAL_CODE = dRow[2].ToString().Trim(); //贈品品號 string strPICK_SELECT = dRow[3].ToString().Trim(); //理貨/管制 if ((strITEM != "") || (strPERIOD != "") || (strVIRTUAL_CODE != "") || (strPICK_SELECT != "")) { // 驗證新增資料欄位是否為必填。 //品號 if (strITEM == string.Empty) { strErrMsg += "品號欄位為必填,"; } else if (CheckLength(strITEM, 6, "品號", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false) { //過長 strITEM = SubStr(strITEM, 0, 6); } else { //檢查品號是否存在主檔 strTempMsg = ""; ParameterList.Clear(); ParameterList.Add(strITEM); ParameterList.Add(null); CheckItemData(ParameterList, out strTempMsg); if (strTempMsg != "") { strErrMsg += strTempMsg + ","; } } drTMP["ITEM"] = strITEM; //期別 if (strPERIOD == string.Empty) { strErrMsg += "期別欄位為必填,"; } else if (CheckLength(strPERIOD, 6, "期別", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false) { //過長 strPERIOD = SubStr(strPERIOD, 0, 6); } else { if (strErrMsg == "")//假如前面品號就有錯誤了,就不必檢查期別是否存在品號內了 { strTempMsg = ""; ParameterList.Clear(); ParameterList.Add(strITEM); ParameterList.Add(strPERIOD); CheckItemData(ParameterList, out strTempMsg); if (strTempMsg != "") { strErrMsg += strTempMsg + ","; } } } drTMP["PERIOD"] = strPERIOD; //贈品 if (strVIRTUAL_CODE == string.Empty) { strErrMsg += "贈品品號為必填,"; } else if (CheckLength(strVIRTUAL_CODE, 10, "贈品品號", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false) { //過長 strVIRTUAL_CODE = SubStr(strVIRTUAL_CODE, 0, 10); } else if (!((strVIRTUAL_CODE.Length == 6) || (strVIRTUAL_CODE.Length == 10))) { strErrMsg += "贈品品號為六碼或十碼,"; } else { if (strVIRTUAL_CODE.Substring(0, 6) != strITEM) { strErrMsg += "贈品品號前六碼需與品號一樣,"; } else { if (strErrMsg == "")//假如前面品號期別就有錯誤了,就不檢查了 { if (strVIRTUAL_CODE.Length == 10) { PIC.VDS2G.BSM.MKT.MKT_GiftDBO dboGift = new PIC.VDS2G.BSM.MKT.MKT_GiftDBO(ref USEDB); ParameterList.Clear(); ParameterList.Add(strITEM); ParameterList.Add(strPERIOD); ParameterList.Add(-1); DataTable dtGift = dboGift.doQueryGift(ParameterList); if (dtGift.Select("ITEM='" + strITEM + "' And PERIOD='" + strPERIOD + "' And VIRTUAL_CODE='" + strVIRTUAL_CODE + "'").Length == 0) { strErrMsg += "找不到對應的贈品品號,"; } } } } } drTMP["VIRTUAL_CODE"] = strVIRTUAL_CODE; //20130312 顏筱盈:檢查匯入的資料中,是否有重複的【品號 + 期別 + 贈品品號】 DataRow[] drDouble = dtTMP.Select("ITEM='" + drTMP["ITEM"].ToString().Trim() + "' And PERIOD='" + drTMP["PERIOD"].ToString().Trim() + "' And VIRTUAL_CODE='" + drTMP["VIRTUAL_CODE"].ToString().Trim() + "'"); if (drDouble.Length > 0) { strErrMsg += "品號:" + drTMP["ITEM"].ToString().Trim() + ",期別:" + drTMP["PERIOD"] + ",贈品品號:" + drTMP["VIRTUAL_CODE"] + ",重複出現在匯入的檔案中,"; } //理貨管制設定 if (strPICK_SELECT == string.Empty) { strErrMsg += "理貨管制設定為必填,"; } else if (!((strPICK_SELECT == "1") || (strPICK_SELECT == "2"))) { //過長 strErrMsg += "理貨管制設定的值應為1或2,"; strPICK_SELECT = SubStr(strPICK_SELECT, 0, 1); } drTMP["PICK_SELECT"] = strPICK_SELECT; if (strErrMsg == "") { string strCheckMsg = ChkDupData(dtExist, strPLAN_ACCEPT_DATE, strVIRTUAL_CODE, strPERIOD, strPICK_BATCH, strKind); if (strCheckMsg != "") { strErrMsg = strCheckMsg + ","; } } if (strErrMsg.Trim() == string.Empty) { drTMP["ERROR_MEMO"] = System.DBNull.Value; } else { if (strErrMsg.Substring(strErrMsg.Length - 1, 1) == ",") strErrMsg = strErrMsg.Substring(0, strErrMsg.Length - 1); drTMP["ERROR_MEMO"] = strErrMsg; iErrCount++; } if (iErrCount != 0) drTMP["DATATYPE"] = "2"; //錯誤資料 else drTMP["DATATYPE"] = "1"; //正常資料 dtTMP.Rows.Add(drTMP); i_newTable_Count += 1; i_FileToTemp_Total_count += 1; } } #region 先清除前一次,同一使用者執行的匯入暫存資訊與錯誤資訊,並將 Excel 資料匯入 TEMP TABLE ParameterList.Clear(); ParameterList.Add(strUID); DELETE_AND_INSERT_TEMP_TABLE(ParameterList, dtTMP); #endregion int i_FileToTemp_Right_count = 0; int i_FileToTemp_Wrong_count = 0; DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); ParameterList.Clear(); ParameterList.Add(strUID); DataTable dtCount = dbo.doQueryTmpCount(ParameterList); for (int i = 0; i < dtCount.Rows.Count; i++) { if (dtCount.Rows[i]["DATATYPE"].ToString() == "1") i_FileToTemp_Right_count = int.Parse(dtCount.Rows[i]["COUNTS"].ToString()); if (dtCount.Rows[i]["DATATYPE"].ToString() == "2") i_FileToTemp_Wrong_count = int.Parse(dtCount.Rows[i]["COUNTS"].ToString()); } if (i_FileToTemp_Wrong_count > 0) { arl_Return.Add("FALSE"); } else { arl_Return.Add("TRUE"); } arl_Return.Add(i_FileToTemp_Total_count); arl_Return.Add(i_FileToTemp_Right_count); arl_Return.Add(i_FileToTemp_Wrong_count); } catch (Exception ex) { arl_Return.Add("ERROR"); arl_Return.Add(ex.Message); } return arl_Return; #endregion }
public DataTable QueryAloItemNum(ArrayList ParameterList, out string TOTAL_DIS_QTY) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); return dbo.QueryAloItemNum(ParameterList, out TOTAL_DIS_QTY); } catch (Exception ex) { throw ex; } }
public DataTable GetPurchaseItemInfo(ArrayList ParameterList) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); return dbo.GetPurchaseItemInfo(ParameterList); } catch (Exception ex) { throw ex; } }
public bool QueryPeriod(ArrayList ParameterList) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); return dbo.QueryPeriod(ParameterList); } catch (Exception ex) { throw ex; } }
public DataTable LoadChannelPickNew(ArrayList ParameterList) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); return dbo.LoadChannelPickNew(ParameterList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 基本表單更新BCO /// </summary> /// <param name="ParameterList">輸入變數</param> /// <param name="RootDBT">是否有主交易,無主交易輸入null</param> /// <returns>回傳更新成功與否</returns> public bool SaveData( ArrayList ParameterList, DataTable dtMST, DataTable dtChannel, string strDelDataType ) { bool bResult = false; try { ArrayList Para = new ArrayList(); //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); string strPLAN_ACCEPT_DATE = ParameterList[0].ToString(); string strPICK_BATCH = ParameterList[1].ToString(); string strUID = ParameterList[2].ToString(); string strUPDATEDATE = ParameterList[3].ToString(); if (strDelDataType != "") { Para.Clear(); Para.Add(strPLAN_ACCEPT_DATE); //0.V_ST_ACCEPT_DATE Para.Add(strPICK_BATCH); //1.V_PICK_BATCH Para.Add(strDelDataType); //2.V_DATATYPE Para.Add(strUID); //3.V_NEW_UPDATEUID Para.Add(strUPDATEDATE); //4.V_NEW_UPDATEDATE dbo.doDeleteBatch(Para, DBT); } foreach (DataRow dr in dtMST.Rows) { string strITEM = dr["ITEM"].ToString().Substring(0, 6); string strPERIOD = dr["PERIOD"].ToString(); string strVIRTUAL_CODE = dr["VIRTUAL_CODE"].ToString(); //新增 if ((dr["CREATEDATE"].ToString().Trim() == "") && (dr["GV_DEL"].ToString() == "0")) { Para.Clear(); Para.Add(strPLAN_ACCEPT_DATE); //0.V_ST_ACCEPT_DATE Para.Add(strPICK_BATCH); //1.V_PICK_BATCH Para.Add(strVIRTUAL_CODE); //2.V_ITEM Para.Add(strPERIOD); //3.V_PERIOD Para.Add(dr["PICK_SELECT"].ToString()); //4.V_PICK_SELECT Para.Add(strUID); //5.V_NEW_UPDATEUID Para.Add(strUPDATEDATE); //6.V_NEW_UPDATEDATE Para.Add(dr["DATATYPE"].ToString()); //7.V_DATATYPE dbo.doInsertMaster(Para, DBT); } //修改&刪除 if (dr["CREATEDATE"].ToString().Trim() != "") { Para.Clear(); Para.Add(strPLAN_ACCEPT_DATE); //0.V_ST_ACCEPT_DATE Para.Add(strPICK_BATCH); //1.V_PICK_BATCH Para.Add(strVIRTUAL_CODE); //2.V_ITEM Para.Add(strPERIOD); //3.V_PERIOD Para.Add(dr["PICK_SELECT"].ToString()); //4.V_PICK_SELECT Para.Add(strUID); //5.V_NEW_UPDATEUID Para.Add(strUPDATEDATE); //6.V_NEW_UPDATEDATE Para.Add(dr["UPDATEUID"].ToString()); //7.V_OLD_UPDATEUID Para.Add(dr["UPDATEDATE"].ToString()); //8.V_OLD_UPDATEDATE if (dr["GV_DEL"].ToString() == "1") Para.Add("0"); //刪除 //9.V_ENABLE else Para.Add("1");//編輯 //9.V_ENABLE dbo.doUpdateMaster(Para, DBT); //刪除明細 if (dr["GV_DEL"].ToString() == "1") { //修改 Para.Clear(); Para.Add(strPLAN_ACCEPT_DATE); //0.V_ST_ACCEPT_DATE Para.Add(strPICK_BATCH); //1.V_PICK_BATCH Para.Add(strITEM); //2.V_ITEM Para.Add(strPERIOD); //3.V_PERIOD Para.Add(strVIRTUAL_CODE); //4.V_VIRTUAL_CODE Para.Add(DBNull.Value); //5.V_CHAN_NO Para.Add(DBNull.Value); //6.V_PICK_SELECT Para.Add(strUID); //7.V_NEW_UPDATEUID Para.Add(strUPDATEDATE); //8.V_NEW_UPDATEDATE Para.Add(DBNull.Value); //9.V_OLD_UPDATEUID Para.Add(DBNull.Value); //10.V_OLD_UPDATEDATE Para.Add(DBNull.Value); //11.V_STATUS Para.Add("0"); //12.V_ENABLE dbo.doUpdateChannel(Para, DBT); } } //處理管制明細部份 if (dtChannel != null) { //處理沒有上刪除FALG的資料,刪除的部分與上面的主檔處理部分一起做掉了 if (dr["GV_DEL"].ToString() == "0") { DataRow[] FindChannels = dtChannel.Select(" ST_ACCEPT_DATE='" + strPLAN_ACCEPT_DATE + "'" + " And PICK_BATCH='" + strPICK_BATCH + "'" + " And ITEM='" + strITEM + "'" + " And PERIOD='" + strPERIOD + "'" + " And VIRTUAL_CODE='" + strVIRTUAL_CODE + "'"); foreach (DataRow drChan in FindChannels) { if (drChan["CREATEUID"].ToString() == "") { //新增 Para.Clear(); Para.Add(strPLAN_ACCEPT_DATE); //0.V_ST_ACCEPT_DATE Para.Add(strITEM); //1.V_ITEM Para.Add(strPERIOD); //2.V_PERIOD Para.Add(drChan["CHAN_NO"].ToString()); //3.V_CHAN_NO Para.Add(strVIRTUAL_CODE); //4.V_VIRTUAL_CODE Para.Add(strPICK_BATCH); //5.V_PICK_BATCH Para.Add(drChan["PICK_SELECT"].ToString()); //6.V_PICK_SELECT Para.Add(strUID); //7.V_UPDATEUID Para.Add(strUPDATEDATE); //8.V_UPDATEDATE Para.Add("25"); //9.V_STATUS dbo.doInsertChannel(Para, DBT); } else { //修改 Para.Clear(); Para.Add(strPLAN_ACCEPT_DATE); //0.V_ST_ACCEPT_DATE Para.Add(strPICK_BATCH); //1.V_PICK_BATCH Para.Add(strITEM); //2.V_ITEM Para.Add(strPERIOD); //3.V_PERIOD Para.Add(strVIRTUAL_CODE); //4.V_VIRTUAL_CODE Para.Add(drChan["CHAN_NO"].ToString()); //5.V_CHAN_NO Para.Add(drChan["PICK_SELECT"].ToString()); //6.V_PICK_SELECT Para.Add(strUID); //7.V_NEW_UPDATEUID Para.Add(strUPDATEDATE); //8.V_NEW_UPDATEDATE Para.Add(drChan["UPDATEUID"].ToString()); //9.V_OLD_UPDATEUID Para.Add(drChan["UPDATEDATE"].ToString()); //10.V_OLD_UPDATEDATE Para.Add(DBNull.Value); //11.V_STATUS Para.Add("1"); //12.V_ENABLE dbo.doUpdateChannel(Para, DBT); } } } } } DBT.Commit(); bResult = true; } catch (Exception ex) { if (DBT != null) DBT.Rollback(); throw ex; } finally { if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } return bResult; }
public string CheckStAcceptData(ArrayList ParameterList) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); return dbo.CheckStAcceptData(ParameterList); } catch (Exception ex) { throw ex; } }
public void CalBoxNum(ArrayList ParameterList, out string CountQty) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); dbo.CalBoxNum(ParameterList, out CountQty); } catch (Exception ex) { throw ex; } }
public void CheckItemData(ArrayList ParameterList, out string Msg) { try { DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); dbo.CheckItemData(ParameterList, out Msg); } catch (Exception ex) { throw ex; } }
/// <summary> /// 刪除VDS_PUR_PICK_CONTROL_CHK,取消鎖定編輯 /// </summary> public int DeleteControlChk(ArrayList ParameterList, DbTransaction RootDBT) { #region bool IsRootTranscation = false; int bResult; 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 DBO.PUR09_DBO dbo = new PUR09_DBO(ref USEDB); bResult = dbo.DeleteControlChk(ParameterList, DBT); #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion return bResult; } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { if (DBT != null) DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } #endregion }
public void DELETE_AND_INSERT_TEMP_TABLE(ArrayList ParameterList, DataTable dtTMP) { try { DBO.PUR09_DBO DBO = new PUR09_DBO(ref USEDB); //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DbTransaction DBT = Conn.BeginTransaction(); DBO.doDel_TMP(ParameterList, DBT); foreach (DataRow dRow in dtTMP.Rows) { DBO.doAdd_TMP(dRow, DBT); } DBT.Commit(); } catch (Exception ex) { if (DBT != null) DBT.Rollback(); throw ex; } finally { if (Conn.State == ConnectionState.Connecting) Conn.Close(); } }