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
}
