public DataTable QueryByFind(ArrayList ParameterList)
{
try
{
DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
DataTable dt;
dt = dbo.doQueryByFind(ParameterList);
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public bool DeleteChainItemInfo(ArrayList ParameterList, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
int iResult = 0;
bool bResult = false;
try
{
MKT_ChainItemInfoDBO DBO = new MKT_ChainItemInfoDBO(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.OLD_UPDATEDATE
//1.OLD_UPDATEUID
//2.UPDATEDATE
//3.UPDATEUID
//4.CHAN_NO
//5.ITEM
//6.PERIOD
iResult = DBO.doDeleteChainItemInfo(DBT, ParameterList);
#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;
}
public bool UpdateChainItemInfo(ArrayList ParameterList, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
int iResult = 0;
bool bResult = false;
string strID = string.Empty;
try
{
MKT_ChainItemInfoDBO DBO = new MKT_ChainItemInfoDBO(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.NAME
//1.OLD_UPDATEDATE
//2.OLD_UPDATEUID
//3.UPDATEDATE
//4.UPDATEUID
//5.ENABLE
//6.CHAN_NO
//7.ITEM
//8.PERIOD
//9.CHAN_CODE
//10.EAN_CODE
//11.BARCODE
//12.PERIOD_BARCODE
//13.DATASOURCE
//14.MEMO
//15.PRICES
//16.COVER_PRICE
//17.COST_PRICE
//18.CHECK_FLAG
//19.VDS_PRICE
iResult = DBO.doUpdateChainItemInfo(DBT, ParameterList);
//更新 [期別通路檔].對應通路之通路對照號
//6.CHAN_NO
//7.ITEM
//8.PERIOD
//9.CHAN_CODE
ArrayList Para = new ArrayList();
Para.Clear();
Para.Add(ParameterList[6].ToString());
Para.Add(ParameterList[7].ToString());
Para.Add(ParameterList[8].ToString());
Para.Add(ParameterList[9].ToString());
iResult = DBO.doUpdateITEM_MAP(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;
}
public ArrayList FileToTmp1(DataSet ds_Excel, string strLoginUser, string strStoreChain, string strOrg_FileName)
{
ArrayList arl_Return = new ArrayList();
int i_FileToTemp_Total_count = 0;
string strErrMsg = "";
Int32 iErrCount = 0;
ArrayList ParameterList = new ArrayList();
ArrayList pList = new ArrayList();
try
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
ParameterList.Clear();
DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
// 先清除前一次，同一使用者執行的匯入暫存資訊與錯誤資訊，並將 Excel 資料匯入 TEMP TABLE
ParameterList.Clear();
ParameterList.Add(strLoginUser);
dbo.doDeleteTmp1(ParameterList);
//0.V_CREATEDATE
//1.V_CREATEUID
//2.V_ID
//3.V_UPDATEDATE
//4.V_UPDATEUID
//5.V_CHAIN_CODE
//6.V_ITEM_CODE
//7.V_TEP_CODE
//8.V_ITEM_NAME
//9.V_MEMO
//10.V_YEAR
//11.V_PERIOD_CODE
//12.V_COVER_DATE
//13.V_PRICES
//14.V_BARCODE
//15.V_EAN_CODE
//16.V_PUBLISH_TYPE
//17.V_PRE_ACCEPT_DATE
//18.V_ERROR_MEMO
//19.V_DATASOURCE
//20.V_STATUS
//21.V_DATATYPE
string strNowTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
int iRow = 0;
foreach (DataRow dRow in ds_Excel.Tables[0].Rows)
{
iRow++;
if (iRow > 1)
{
if (
(dRow[1].ToString().Trim() != "") ||
(dRow[2].ToString().Trim() != "") ||
(dRow[3].ToString().Trim() != "") ||
(dRow[4].ToString().Trim() != "") ||
(dRow[5].ToString().Trim() != "") ||
(dRow[6].ToString().Trim() != "") ||
(dRow[8].ToString().Trim() != "") ||
(dRow[9].ToString().Trim() != "") ||
(dRow[10].ToString().Trim() != "") ||
(dRow[11].ToString().Trim() != "") ||
(dRow[12].ToString().Trim() != "") ||
(dRow[14].ToString().Trim() != "")
)//不為空值才執行
{
iErrCount = 0;
strErrMsg = "";
ParameterList.Clear();
ParameterList.Add(strNowTime); //0.V_CREATEDATE
ParameterList.Add(strLoginUser); //1.V_CREATEUID
ParameterList.Add(iRow); //2.V_ID
ParameterList.Add(strNowTime); //3.V_UPDATEDATE
ParameterList.Add(strLoginUser); //4.V_UPDATEUID
ParameterList.Add(strStoreChain);//5.V_CHAIN_CODE
//6.V_ITEM_CODE (10)
ParameterList.Add(CheckLength(dRow[1].ToString().Trim(), 10, "高見品號", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//7.V_TEP_CODE (10)
ParameterList.Add(CheckLength(dRow[2].ToString().Trim(), 10, "台英品號", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//8.V_ITEM_NAME (30)
//ParameterList.Add(CheckLength(dRow[3].ToString(), 30, "品名", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
ParameterList.Add(SubStr(dRow[3].ToString().Trim(), 0, 30));//自動截斷字串
//9.V_MEMO (500)
ParameterList.Add(CheckLength(dRow[4].ToString().Trim(), 500, "備註", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//10.V_YEAR (4)
ParameterList.Add(CheckLength(dRow[5].ToString().Trim(), 4, "高見期別(年)", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//11.V_PERIOD_CODE (6)
ParameterList.Add(CheckLength(dRow[5].ToString().Trim() + dRow[6].ToString().Trim(), 6, "高見期別(年月)", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//12.V_COVER_DATE (10)
ParameterList.Add(CheckLength(dRow[8].ToString().Trim(), 20, "封面日期", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//13.V_PRICES (10)
ParameterList.Add(CheckLength(dRow[9].ToString().Trim(), 10, "價格", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//14.V_BARCODE (20)
ParameterList.Add(CheckLength(dRow[10].ToString().Trim(), 20, "原印碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//15.V_EAN_CODE (20)
ParameterList.Add(CheckLength(dRow[11].ToString().Trim(), 30, "EAN條碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//16.V_PUBLISH_TYPE (10)
ParameterList.Add(CheckLength(dRow[12].ToString().Trim(), 10, "刊別", strErrMsg, iErrCount, out strErrMsg, out iErrCount));
//17.V_PRE_ACCEPT_DATE (10)
if (dRow[14].ToString().Trim().Length < 10)
ParameterList.Add(dRow[14].ToString().Trim());
else
ParameterList.Add(dRow[14].ToString().Trim().Substring(0, 10));//17.V_PRE_ACCEPT_DATE
//高見品號不得為空值
if (dRow[1].ToString().Trim() == "")
{
iErrCount++;
strErrMsg = strErrMsg + "高見品號不得為空值;";
}
//高見期別不得為空值
if ((dRow[5].ToString().Trim() + dRow[6].ToString().Trim()) == "")
{
iErrCount++;
strErrMsg = strErrMsg + "高見期別不得為空值;";
}
//價格不得為空值
if (dRow[9].ToString().Trim() == "")
{
iErrCount++;
strErrMsg = strErrMsg + "價格不得為空值;";
}
else
{
double dblPrice = 0;
if (double.TryParse(dRow[9].ToString().Trim(), out dblPrice) == false)
{
iErrCount++;
strErrMsg = strErrMsg + "價格非數值資料;";
}
}
//條碼不得為空值
if (dRow[11].ToString().Trim() == "")
{
iErrCount++;
strErrMsg = strErrMsg + "EAN條碼不得為空值;";
}
ParameterList.Add(strErrMsg);//18.V_ERROR_MEMO
ParameterList.Add(strOrg_FileName);//19.V_DATASOURCE
//20.V_STATUS 1:正常資料 2:錯誤資料
if (iErrCount != 0)
{
ParameterList.Add("2");
}
else
{
ParameterList.Add("1");
}
ParameterList.Add("1");//21.V_DATATYPE 1:File To Temp 2:Temp To DB
dbo.doFileToTmp1(ParameterList, DBT);
i_FileToTemp_Total_count += 1;
}
}
}
DBT.Commit();
arl_Return.Add("TRUE");
arl_Return.Add(i_FileToTemp_Total_count);
}
catch (Exception ex)
{
DBT.Rollback();
arl_Return.Add("FALSE");
arl_Return.Add(ex.Message);
throw ex;
}
return arl_Return;
}
public bool CreateChainItemInfo(ArrayList ParameterList, out string strID, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
int iResult = 0;
bool bResult = false;
try
{
MKT_ChainItemInfoDBO DBO = new MKT_ChainItemInfoDBO(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
iResult = DBO.doCreateChainItemInfo(DBT, ParameterList);
//6.CHAN_NO
//7.ITEM
//8.PERIOD
//9.CHAN_CODE
ArrayList Para = new ArrayList();
Para.Clear();
Para.Add(ParameterList[6].ToString());
Para.Add(ParameterList[7].ToString());
Para.Add(ParameterList[8].ToString());
Para.Add(ParameterList[9].ToString());
iResult = DBO.doUpdateITEM_MAP(DBT, Para);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
bResult = true;
}
#endregion
//6.CHAN_NO
//7.ITEM
//8.PERIOD
Para.Clear();
Para.Add(ParameterList[6].ToString());
Para.Add(ParameterList[7].ToString());
Para.Add(ParameterList[8].ToString());
DataTable dt = QueryID(Para);
strID = dt.Rows[0]["ID"].ToString().Trim();
}
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;
}
private int CheckData(string strITEM, string strBARCODE, string strPERIOD_BARCODE, string strPRICES, string strCOVER_PRICE, string strCOST_PRICE)
{
//檢查匯入項目為 1:新增 2:異動 3:既有品項
int iResult = 0;
bool bExistBARCODE = false; //期別主檔1段碼存在否 (只用BARCODE查詢)
bool bExistPERIOD_BARCODE = false; //期別主檔2段碼存在否(1段碼存在)(只用BARCODE查詢)
bool bExistPERIOD = false; //期別主檔2段碼存在 (用ITEM和2段碼查詢)
bool bExistItem = false; //商品主檔存在對應商品品號
DataTable dt = null;
ArrayList ParameterList = new ArrayList();
DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
//用一段碼查詢期別主檔
ParameterList.Clear();
ParameterList.Add("");
ParameterList.Add(strBARCODE);
ParameterList.Add("");
ParameterList.Add("");
dt = dbo.doQueryITM_PERIOD(ParameterList);
if (dt.Rows.Count > 0)
{
bExistBARCODE = true;
DataRow[] FindRows = dt.Select("PERIOD_BARCODE='" + strPERIOD_BARCODE + "' or PERIOD_BARCODE is null");
if (FindRows.Length > 0)
{
bExistPERIOD_BARCODE = true;
}
}
//用品號和二段碼查詢期別主檔
ParameterList.Clear();
ParameterList.Add(strITEM);
ParameterList.Add("");
ParameterList.Add(strPERIOD_BARCODE);
ParameterList.Add("");
dt = dbo.doQueryITM_PERIOD(ParameterList);
if (dt.Rows.Count > 0)
{
bExistPERIOD = true;
}
//查詢商品主檔是否存在品號
ParameterList.Clear();
ParameterList.Add(strITEM);
dt = dbo.doQueryITM_ITEM(ParameterList);
if (dt.Rows.Count > 0)
{
bExistItem = true;
}
if ((bExistItem == true) && (bExistBARCODE == false) && (bExistPERIOD == true))
{
//1. 商品主檔存在對應商品品號，但 2段碼符合，1段碼不符=>註記原因：調價。
iResult = 2;//異動品項
}
else if ((bExistPERIOD_BARCODE == true) && (bExistBARCODE == true) && (bExistItem == true))
{
//假如是異動品項 要判斷是否為調價
ParameterList.Clear();
ParameterList.Add(strITEM);
ParameterList.Add(strBARCODE);
ParameterList.Add(strPERIOD_BARCODE);
ParameterList.Add("");
DataTable dtIPM = dbo.doQueryITM_PERIOD(ParameterList);
if (dtIPM.Rows.Count > 0)
{
string strIPM_Price = dtIPM.Rows[0]["PRICE"].ToString();
if (
(strIPM_Price != strPRICES) ||
(strIPM_Price != strCOVER_PRICE) ||
(strIPM_Price != strCOST_PRICE)
)
{
iResult = 2;//異動品項 調價
}
else
{
//1. 商品主檔存在對應品號，且商品期別檔之2段碼、1段碼均相符者。
// 並將[通路對照號]回寫到[期別通路檔]對應通路對照號的通路品號。
iResult = 3;//即有品項
}
}
else
{
//1. 商品主檔存在對應品號，且商品期別檔之2段碼、1段碼均相符者。
// 並將[通路對照號]回寫到[期別通路檔]對應通路對照號的通路品號。
iResult = 3;//即有品項
}
}
else
{
iResult = 4;//應該不會有這個狀況
}
return iResult;
}
public ArrayList TmpToDB(string strLoginUser, string strStoreChain)
{
ArrayList arl_Return = new ArrayList();
int i_Total_count = 0;
int i_Right_count = 0;
int i_Wrong_count = 0;
string strErrMsg = "";
Int32 iErrCount = 0;
ArrayList ParameterList = new ArrayList();
ArrayList pList = new ArrayList();
try
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
ParameterList.Clear();
DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
//0.V_NAME
//1.V_CREATEDATE
//2.V_CREATEUID
//3.V_UPDATEDATE
//4.V_UPDATEUID
//5.V_ENABLE
//6.V_CHAN_NO
//7.V_ITEM
//8.V_PERIOD
//9.V_CHAN_CODE
//10.V_EAN_CODE
//11.V_BARCODE
//12.V_PERIOD_BARCODE
//13.V_DATASOURCE
//14.V_MEMO
//15.V_PRICES
//16.V_COVER_PRICE
//18.17.V_COST_PRICE
//19.V_CHECK_FLAG
//20.V_VDS_PRICE
string strNowTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
ParameterList.Clear();
ParameterList.Add(strLoginUser);
DataTable dt = dbo.doQueryTemp(ParameterList);
foreach (DataRow dr in dt.Rows)
{
i_Total_count++;
string strMemo = "";
string strITEM = dr["ITEM"].ToString().Trim();
string strBARCODE = dr["BARCODE"].ToString().Trim();
string strPERIOD_BARCODE = dr["PERIOD_BARCODE"].ToString().Trim();
int iCheckState = 0;
string strExistTmp2 = dr["t2_exist"].ToString().Trim();
if (strExistTmp2 != "")
{
iCheckState = CheckData(strITEM, strBARCODE, strPERIOD_BARCODE, dr["PRICES"].ToString(), dr["COVER_PRICE"].ToString(), dr["COST_PRICE"].ToString());
}
else
{
iCheckState = 1;//新增 Tmp1有 Tmp2沒有
}
//新增到 vds_mkt_chainiteminfo 通路對照檔
if (dr["PERIOD"].ToString().Trim() != "XXXXXX")
{
ParameterList.Clear();
ParameterList.Add(dr["ITEM"].ToString()); //0.V_NAME
ParameterList.Add(strNowTime); //1.V_CREATEDATE
ParameterList.Add(strLoginUser); //2.V_CREATEUID
ParameterList.Add(strNowTime); //3.V_UPDATEDATE
ParameterList.Add(strLoginUser); //4.V_UPDATEUID
ParameterList.Add("1"); //5.V_ENABLE
ParameterList.Add(dr["CHAN_NO"].ToString()); //6.V_CHAN_NO
ParameterList.Add(dr["ITEM"].ToString()); //7.V_ITEM
ParameterList.Add(dr["PERIOD"].ToString()); //8.V_PERIOD
ParameterList.Add(dr["CHAN_CODE"].ToString()); //9.V_CHAN_CODE
ParameterList.Add(dr["EAN_CODE"].ToString()); //10.V_EAN_CODE
ParameterList.Add(dr["BARCODE"].ToString()); //11.V_BARCODE
ParameterList.Add(dr["PERIOD_BARCODE"].ToString());//12.V_PERIOD_BARCODE
ParameterList.Add("2"); //13.V_DATASOURCE
ParameterList.Add(dr["MEMO"].ToString()); //14.V_MEMO
ParameterList.Add(dr["PRICES"].ToString()); //15.V_PRICES
ParameterList.Add(dr["COVER_PRICE"].ToString()); //16.V_COVER_PRICE
ParameterList.Add(dr["COST_PRICE"].ToString()); //18.17.V_COST_PRICE
ParameterList.Add(iCheckState); //19.V_CHECK_FLAG 1:新增 2:異動 3:即有品項
ParameterList.Add("0"); //20.V_VDS_PRICE
dbo.doCreateChainItemInfo(DBT, ParameterList);
}
if (iCheckState == 3)
{
//假如是既有商品 更新 [期別通路檔].對應通路之通路對照號
//0.CHAN_NO
//1.ITEM
//2.PERIOD
//3.CHAN_CODE
ParameterList.Clear();
ParameterList.Add(dr["CHAN_NO"].ToString());
ParameterList.Add(dr["ITEM"].ToString());
ParameterList.Add(dr["PERIOD"].ToString());
ParameterList.Add(dr["CHAN_CODE"].ToString());
dbo.doUpdateITEM_MAP(DBT, ParameterList);
}
//更新 VDS_MKT_CHAINITEMINFO_TMP1 的 DATATYPE 變成 2:TMP TO DB
//0.V_ID
//1.V_UID
//2.V_STATUS
//3.V_DATATYPE
//4.V_ERROR_MEMO
if (iCheckState == 2)
{
strMemo = "調價";
}
else if (iCheckState == 4)
{
strMemo = "其他例外錯誤";
}
ParameterList.Clear();
ParameterList.Add(dr["ID"].ToString());
ParameterList.Add(strLoginUser);
ParameterList.Add("");
ParameterList.Add("2");
ParameterList.Add(strMemo);
dbo.doUpdateTmp(DBT, ParameterList);
i_Right_count++;
//i_Wrong_count++;
}
DBT.Commit();
arl_Return.Add("TRUE");
arl_Return.Add(i_Total_count);
arl_Return.Add(i_Right_count);
arl_Return.Add(i_Wrong_count);
}
catch (Exception ex)
{
DBT.Rollback();
arl_Return.Add("FALSE");
arl_Return.Add(ex.Message);
throw ex;
}
return arl_Return;
}
//取得Tmp2成功和錯誤的筆數
public ArrayList GetTmp2Count(string strLoginUser)
{
ArrayList ParameterList = new ArrayList();
ArrayList arl_Return = new ArrayList();
int i_FileToTemp_Right_count = 0;
int i_FileToTemp_Wrong_count = 0;
DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
//取得正確和錯誤筆數
ParameterList.Clear();
ParameterList.Add(strLoginUser);
DataTable dtCounts = dbo.QueryTemp2Count(ParameterList);
foreach (DataRow dr in dtCounts.Rows)
{
if (dr["status"].ToString() == "2")
{
i_FileToTemp_Wrong_count = int.Parse(dr["counts"].ToString());
}
if (dr["status"].ToString() == "1")
{
i_FileToTemp_Right_count = int.Parse(dr["counts"].ToString());
}
}
arl_Return.Add(i_FileToTemp_Right_count);
arl_Return.Add(i_FileToTemp_Wrong_count);
return arl_Return;
}
//把Temp1和Temp2做其他例外檢查
public void CheckOther(string strLoginUser)
{
ArrayList ParameterList = new ArrayList();
DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
//1.檢查Tmp1&Tmp2的EAN是否完全對應
//2.檢查Temp2的誠品編號不能重複
ParameterList.Clear();
ParameterList.Add(strLoginUser);
dbo.doCheckTmp(ParameterList);
}
public ArrayList FileToTmp2(DataSet ds_Excel, string strLoginUser, string strStoreChain, string strOrg_FileName)
{
ArrayList arl_Return = new ArrayList();
int i_FileToTemp_Total_count = 0;
string strErrMsg = "";
Int32 iErrCount = 0;
ArrayList ParameterList = new ArrayList();
ArrayList pList = new ArrayList();
try
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
ParameterList.Clear();
DBO.MKT_ChainItemInfoDBO dbo = new MKT_ChainItemInfoDBO(ref USEDB);
// 先清除前一次，同一使用者執行的匯入暫存資訊與錯誤資訊，並將 Excel 資料匯入 TEMP TABLE
ParameterList.Clear();
ParameterList.Add(strLoginUser);
dbo.doDeleteTmp2(ParameterList);
//0.V_ID
//1.V_CREATEDATE
//2.V_CREATEUID
//3.V_UPDATEDATE
//4.V_UPDATEUID
//5.V_EAN_CODE
//6.V_ITEM
//7.V_CHAN_CODE
//8.V_ITEM_NAME
//9.V_PRICES
//10.V_PRICES_CURRENCY
//11.V_COVER_PRICE
//12.V_COVER_PRICE_CURRENCY
//13.V_COST_PRICE
//14.V_COST_PRICE_CURRENCY
//15.V_DISCOUNT
//16.V_PUBLISH_TYPE
//17.V_PUBLISH_TYPE_MEMO
//18.V_LANGUAGE_CODE
//19.V_LANGUAGE_MEMO
//20.V_SOURCE
//21.V_MAIN_FACTORY_CODE
//22.V_PUBLISH_CODE
//23.V_BUY_GROUP
//24.V_MATERIEL_GROUP
//25.V_MATERIEL_GROUP_MEMO
//26.V_MADE_DATE
//27.V_AUTHOR
//28.V_MATERIAL
//29.V_SIZES
//30.V_ERROR_MSG
//31.V_ERROR_MEMO
//32.V_DATASOURCE
//33.V_STATUS,
//34.V_DATATYPE
string strNowTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
int iRow = 1;
int iCol1 = 0; //假如第1個Column是空格的 iCol1=1,假如第1個是EAN則iCol1=-1,因為第1個Column沒值時,Excel讀取後第1個Col會忽略掉.
if (ds_Excel.Tables[0].Columns[0].ColumnName.Trim() == "EAN")
iCol1 = -1;
else
iCol1 = 0;
foreach (DataRow dRow in ds_Excel.Tables[0].Rows)
{
if (dRow[1 + iCol1].ToString().Trim() != "EAN")
{
if ((dRow[1 + iCol1].ToString().Trim() != "") || (dRow[3 + iCol1].ToString().Trim() != "") || (dRow[4 + iCol1].ToString().Trim() != "")) //不為空值才執行
{
iErrCount = 0;
strErrMsg = "";
ParameterList.Clear();
ParameterList.Add(iRow); //0.V_ID
ParameterList.Add(strNowTime); //1.V_CREATEDATE
ParameterList.Add(strLoginUser); //2.V_CREATEUID
ParameterList.Add(strNowTime); //3.V_UPDATEDATE
ParameterList.Add(strLoginUser); //4.V_UPDATEUID
ParameterList.Add(CheckLength(dRow[1 + iCol1].ToString(), 30, "EAN條碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //5.V_EAN_CODE
ParameterList.Add(CheckLength(dRow[3 + iCol1].ToString(), 10, "廠商貨號", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //6.V_ITEM
ParameterList.Add(CheckLength(dRow[4 + iCol1].ToString(), 20, "通路對照號", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //7.V_CHAN_CODE
//ParameterList.Add(CheckLength(dRow[6 + iCol].ToString(), 30, "商品名稱", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //8.V_ITEM_NAME
ParameterList.Add(SubStr(dRow[6 + iCol1].ToString(), 0, 30));//8.V_ITEM_NAME 自動截斷字串
ParameterList.Add(CheckLength(dRow[7 + iCol1].ToString(), 10, "售價", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //9.V_PRICES
ParameterList.Add(CheckLength(dRow[9 + iCol1].ToString(), 10, "售價幣別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //10.V_PRICES_CURRENCY
ParameterList.Add(CheckLength(dRow[10 + iCol1].ToString(), 10, "Cover Price", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //11.V_COVER_PRICE
ParameterList.Add(CheckLength(dRow[11 + iCol1].ToString(), 10, "Cover Price 幣別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //12.V_COVER_PRICE_CURRENCY
ParameterList.Add(CheckLength(dRow[12 + iCol1].ToString(), 10, "進貨基準價", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //13.V_COST_PRICE
ParameterList.Add(CheckLength(dRow[13 + iCol1].ToString(), 10, "進貨基準價幣別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //14.V_COST_PRICE_CURRENCY
ParameterList.Add(CheckLength(dRow[14 + iCol1].ToString(), 10, "折扣", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //15.V_DISCOUNT
ParameterList.Add(CheckLength(dRow[15 + iCol1].ToString(), 10, "版別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //16.V_PUBLISH_TYPE
ParameterList.Add(CheckLength(dRow[16 + iCol1].ToString(), 50, "版別說明", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //17.V_PUBLISH_TYPE_MEMO
ParameterList.Add(CheckLength(dRow[17 + iCol1].ToString(), 10, "語言別", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //18.V_LANGUAGE_CODE
ParameterList.Add(CheckLength(dRow[18 + iCol1].ToString(), 10, "語言別說明", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //19.V_LANGUAGE_MEMO
ParameterList.Add(CheckLength(dRow[19 + iCol1].ToString(), 10, "貨源", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //20.V_SOURCE
ParameterList.Add(CheckLength(dRow[20 + iCol1].ToString(), 10, "主廠商編號", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //21.V_MAIN_FACTORY_CODE
ParameterList.Add(CheckLength(dRow[21 + iCol1].ToString(), 10, "出版商編號", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //22.V_PUBLISH_CODE
ParameterList.Add(CheckLength(dRow[22 + iCol1].ToString(), 10, "採購群組", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //23.V_BUY_GROUP
ParameterList.Add(CheckLength(dRow[23 + iCol1].ToString(), 10, "物料群組", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //24.V_MATERIEL_GROUP
ParameterList.Add(CheckLength(dRow[24 + iCol1].ToString(), 50, "物料群組說明", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //25.V_MATERIEL_GROUP_MEMO
ParameterList.Add(CheckLength(dRow[25 + iCol1].ToString(), 10, "製造日期", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //26.V_MADE_DATE
ParameterList.Add(CheckLength(dRow[26 + iCol1].ToString(), 50, "作者名", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //27.V_AUTHOR
ParameterList.Add(CheckLength(dRow[27 + iCol1].ToString(), 10, "材質", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //28.V_MATERIAL
ParameterList.Add(CheckLength(dRow[28 + iCol1].ToString(), 10, "SIZE", strErrMsg, iErrCount, out strErrMsg, out iErrCount)); //29.V_SIZES
//誠品編號不得空值
if (dRow[4 + iCol1].ToString().Trim() == "")
{
iErrCount++;
strErrMsg = strErrMsg + "誠品編號不得空值;";
}
int iTemp = 0;
//售價不得空值
if (dRow[7 + iCol1].ToString().Trim() == "")
{
iErrCount++;
strErrMsg = strErrMsg + "售價不得空值;";
}
else if (int.TryParse(dRow[7 + iCol1].ToString().Trim(), out iTemp) == false)
{
iErrCount++;
strErrMsg = strErrMsg + "售價非數值格式;";
}
//Cover Price不得空值
if (dRow[10 + iCol1].ToString().Trim() == "")
{
iErrCount++;
strErrMsg = strErrMsg + "Cover Price不得空值;";
} if (int.TryParse(dRow[10 + iCol1].ToString().Trim(), out iTemp) == false)
{
iErrCount++;
strErrMsg = strErrMsg + "Cover Price非數值格式;";
}
//進貨基準價不得空值
if (dRow[12 + iCol1].ToString().Trim() == "")
{
iErrCount++;
strErrMsg = strErrMsg + "進貨基準價不得空值";
} if (int.TryParse(dRow[12 + iCol1].ToString().Trim(), out iTemp) == false)
{
iErrCount++;
strErrMsg = strErrMsg + "進貨基準價非數值格式;";
}
ParameterList.Add(strErrMsg); //30.V_ERROR_MSG
ParameterList.Add(strErrMsg); //31.V_ERROR_MEMO
ParameterList.Add(strOrg_FileName); //32.V_DATASOURCE
//33.V_STATUS 1:正常資料 2:錯誤資料
if (iErrCount != 0)
{
ParameterList.Add("2");
}
else
{
ParameterList.Add("1");
}
ParameterList.Add("1");//34.V_DATATYPE 1:File To Temp 2:Temp To DB
dbo.doFileToTmp2(ParameterList, DBT);
i_FileToTemp_Total_count += 1;
}
}
iRow++;
}
DBT.Commit();
arl_Return.Add("TRUE");
arl_Return.Add(i_FileToTemp_Total_count);
}
catch (Exception ex)
{
DBT.Rollback();
arl_Return.Add("FALSE");
arl_Return.Add(ex.Message);
throw ex;
}
return arl_Return;
}