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();
}
}