public DataTable QuerySurveyRecMain_1(ArrayList ParameterList)
{
try
{
DBO.MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(ref USEDB);
return dbo.QuerySurveyRecMain_1(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 基本表單刪除BCO
/// </summary>
/// <param name="ParameterList">刪除變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳影響筆數</returns>
public void DeleteMultiMasterAndDetail(ArrayList ParameterList,
DataTable dtMaster,
DbTransaction RootDBT
)
{
bool IsRootTranscation = false;
try
{
MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(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
#region 逐筆刪除MasterAndDetail by every master data
foreach (DataRow drMaster in dtMaster.Rows)
{
DeleteMasterAndDetail(ParameterList, drMaster, DBT);
}
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#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
}
}
/// <summary>
/// 基本表單刪除BCO
/// </summary>
/// <param name="ParameterList">刪除變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳影響筆數</returns>
public void DeleteMasterAndDetail(ArrayList ParameterList,
DataRow drMaster,
DbTransaction RootDBT
)
{
bool IsRootTranscation = false;
try
{
MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(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
int rtnValue = dbo.doDelete(ParameterList, drMaster, DBT);
if (rtnValue > 0)
{
DeleteDetailByPID(drMaster["ID"].ToString(), ParameterList[0].ToString(), DBT);
}
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#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
}
}
/// <summary>
/// 基本表單更新BCO
/// </summary>
/// <param name="ParameterList">更新變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳成功與否</returns>
public bool UpdateMaster(ArrayList ParameterList,
DataRow drMaster,
DbTransaction RootDBT)
{
bool IsRootTranscation = false;
bool bResult = false;
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
MKT_MarketSurveyRecordsDBO MainDBO = new MKT_MarketSurveyRecordsDBO(ref USEDB);
MainDBO.doUpdate(ParameterList, drMaster, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
bResult = true;
return bResult;
}
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
}
}
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;
}
public ArrayList FileToTmp(DataTable dtExcel, DateTime d_CreateDate, string s_LoginUser, string s_AP_FileName)
{
ArrayList ParameterList = new ArrayList();
ArrayList arl_Return = new ArrayList();
int i_FileToTemp_Total_count = 0;
int i_FileToTemp_Right_count = 0;
int i_FileToTemp_Wrong_count = 0;
try
{
MKT_MarketSurveyRecordsDBO DBO = new MKT_MarketSurveyRecordsDBO(ref USEDB);
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
DBO.doDeleteTmp(ParameterList, DBT);
foreach (DataRow dr in dtExcel.Rows)
{
string V_CREATEDATE = d_CreateDate.ToString("yyyy/MM/dd");
string V_CREATEUID = s_LoginUser;
string V_Z_O_ALL = dr["Z_O_ALL"].ToString().Trim();
string V_Z_O = dr["Z_O"].ToString().Trim();
string V_Z_O_NAME = dr["Z_O_NAME"].ToString().Trim();
string V_SURVEY_DATE = dr["SURVEY_DATE"].ToString().Trim();
string V_ITEM = dr["ITEM"].ToString().Trim();
string V_ITEM_NAME = dr["ITEM_NAME"].ToString().Trim();
string V_PERIOD = dr["PERIOD"].ToString().Trim();
string V_CHAN_NO = dr["CHAN_NO"].ToString().Trim();
string V_CHAN_NAME = dr["CHAN_NAME"].ToString().Trim();
string V_STORE = dr["STORE"].ToString().Trim();
string V_STORE_NAME = dr["STORE_NAME"].ToString().Trim();
string V_STOCKS_QTY = dr["STOCKS_QTY"].ToString().Trim();
string V_MEMO = dr["MEMO"].ToString().Trim();
string V_ERROR = dr["ERROR"].ToString().Trim();
ParameterList.Clear();
ParameterList.Add(V_CREATEDATE);
ParameterList.Add(V_CREATEUID);
ParameterList.Add(V_Z_O);
ParameterList.Add(V_Z_O_NAME);
ParameterList.Add(V_SURVEY_DATE);
ParameterList.Add(V_ITEM);
ParameterList.Add(V_ITEM_NAME);
ParameterList.Add(V_PERIOD);
ParameterList.Add(V_CHAN_NO);
ParameterList.Add(V_CHAN_NAME);
ParameterList.Add(V_STORE);
ParameterList.Add(V_STORE_NAME);
ParameterList.Add(V_STOCKS_QTY);
ParameterList.Add(V_MEMO);
ParameterList.Add(V_ERROR);
if (V_ERROR != "")
ParameterList.Add("2");
else
ParameterList.Add("1");
ParameterList.Add("1");
ParameterList.Add(SubStr(s_AP_FileName, 0, 50));
ParameterList.Add(V_Z_O_ALL);
DBO.doInsertTMP(ParameterList, DBT);
}
DBT.Commit();
bool bCheck = false;
DataRow[] FindRows = dtExcel.Select("ERROR='' or ERROR is null");
i_FileToTemp_Total_count = dtExcel.Rows.Count;
i_FileToTemp_Right_count = FindRows.Length;
i_FileToTemp_Wrong_count = i_FileToTemp_Total_count - i_FileToTemp_Right_count;
if (i_FileToTemp_Total_count == i_FileToTemp_Right_count)
bCheck = true;
arl_Return.Add("TRUE");
arl_Return.Add(bCheck);
arl_Return.Add(i_FileToTemp_Total_count);
arl_Return.Add(i_FileToTemp_Right_count);
arl_Return.Add(i_FileToTemp_Wrong_count);
}
catch (Exception ex)
{
if (DBT != null)
DBT.Rollback();
arl_Return.Add("FALSE");
arl_Return.Add(ex.Message);
}
return arl_Return;
}
public DataTable CheckImportExist(ArrayList ParameterList)
{
try
{
DBO.MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(ref USEDB);
return dbo.doCheckImportExist(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 測試SP交易失敗
/// </summary>
/// <param name="ParameterList">變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
public void TestTranscation(ArrayList ParameterList1,
ArrayList ParameterList2,
ArrayList ParameterList3,
DbTransaction RootDBT
)
{
bool IsRootTranscation = false;
try
{
DBO.MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(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
//dbo.doCreate(ParameterList1, DBT);
//dbo.doUpdate(ParameterList2, DBT);
//dbo.doDelete(ParameterList3, DBT);
throw new Exception("交易失敗");
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#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
}
}