public DataTable QueryTmp(ArrayList ParameterList)
{
try
{
DBO.MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(ref USEDB);
return dbo.doQueryTmp(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
public ArrayList UpdateTmpToDB(string s_LoginUser)
{
ArrayList arl_Return = new ArrayList();
ArrayList ParameterList = new ArrayList();
int iRight = 0;
int iWrong = 0;
try
{
MKT_MarketSurveyRecordsDBO DBO = new MKT_MarketSurveyRecordsDBO(ref USEDB);
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
ParameterList.Add(DBNull.Value);
ParameterList.Add("1");
ParameterList.Add(999999);
DataTable dtTmp = DBO.doQueryTmp(ParameterList);
if (dtTmp.Rows.Count > 0)
{
string strZ_O = string.Empty;
string strCHAN_NO = string.Empty;
string strSTORE = string.Empty;
string strSURVEY_DATE = string.Empty;
string strNow = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
for (int i = 0; i < dtTmp.Rows.Count; i++)
{
if ((dtTmp.Rows[i]["Z_O"].ToString() != strZ_O) &&
(dtTmp.Rows[i]["CHAN_NO"].ToString() != strCHAN_NO) &&
(dtTmp.Rows[i]["STORE"].ToString() != strSTORE) &&
(dtTmp.Rows[i]["SURVEY_DATE"].ToString() != strSURVEY_DATE))
{
//更新主檔,如果不是同一個主檔的key(一個excel可能包含不同主檔的資料),才再進行主檔更新,沒有需要一直更新重複的資料
ParameterList.Clear();
if (dtTmp.Rows[i]["Z_O_ALL"].ToString() == "全部")
ParameterList.Add("1"); //0.V_OLD_Z_O
else
ParameterList.Add(dtTmp.Rows[i]["Z_O"].ToString()); //0.V_OLD_Z_O
ParameterList.Add(dtTmp.Rows[i]["CHAN_NO"].ToString()); //1.V_OLD_CHAN_NO
ParameterList.Add(dtTmp.Rows[i]["STORE"].ToString()); //2.V_OLD_STORE
ParameterList.Add(dtTmp.Rows[i]["SURVEY_DATE"].ToString()); //3.V_OLD_SURVEY_DATE
ParameterList.Add(s_LoginUser); //4.V_NEW_UPDATEUID
ParameterList.Add(strNow); //5.V_NEW_UPDATEUDATE
DBO.doTempToDB_MAIN(ParameterList, DBT);
}
//更新明細檔
strZ_O = dtTmp.Rows[i]["Z_O"].ToString();
strCHAN_NO = dtTmp.Rows[i]["CHAN_NO"].ToString();
strSTORE = dtTmp.Rows[i]["STORE"].ToString();
strSURVEY_DATE = dtTmp.Rows[i]["SURVEY_DATE"].ToString();
ParameterList.Clear();
if (dtTmp.Rows[i]["Z_O_ALL"].ToString() == "全部")
ParameterList.Add("1"); //0.V_OLD_Z_O
else
ParameterList.Add(strZ_O); //0.V_OLD_Z_O
ParameterList.Add(strCHAN_NO); //1.V_OLD_CHAN_NO
ParameterList.Add(strSTORE); //2.V_OLD_STORE
ParameterList.Add(strSURVEY_DATE); //3.V_OLD_SURVEY_DATE
ParameterList.Add(dtTmp.Rows[i]["ITEM"].ToString()); //4.V_OLD_ITEM
ParameterList.Add(dtTmp.Rows[i]["PERIOD"].ToString()); //5.V_OLD_PERIOD
ParameterList.Add(dtTmp.Rows[i]["STOCKS_QTY"].ToString()); //6.V_STOCKS_QTY
ParameterList.Add(dtTmp.Rows[i]["MEMO"].ToString()); //7.V_MEMO
ParameterList.Add(s_LoginUser); //8.V_NEW_UPDATEUID
ParameterList.Add(strNow); //9.V_NEW_UPDATEUDATE
DBO.doTempToDB_DTL(ParameterList, DBT);
iRight++;
}
}
//獨立呼叫Transcation成立
DBT.Commit();
arl_Return.Add("TRUE");
arl_Return.Add(dtTmp.Rows.Count);
arl_Return.Add(iRight);
arl_Return.Add(iWrong);
}
catch (Exception ex)
{
if (DBT != null)
DBT.Rollback();//獨立呼叫Transcation失敗
arl_Return.Add("FALSE");
arl_Return.Add(ex.Message);
}
finally
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
Conn.Close();
}
return arl_Return;
}