public ArrayList CloseMonthSKU_TRACE(string strLoginUser, string ConntionDB)
{
ArrayList arl_Return = new ArrayList();
try
{
ArrayList ParameterList = new ArrayList();
DBO.VAM031_DBO DBO = new VAM031_DBO(ref USEDB);
ParameterList.Clear();
ParameterList.Add(strLoginUser);
ParameterList.Add("");
DataTable dtTmp = DBO.QueryItemTmp(ParameterList);
DataRow[] FindRows;
FindRows = dtTmp.Select("STATUS='2'");
int iWrong = FindRows.Length;
if (iWrong == 0)
{
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
ParameterList.Clear();
ParameterList.Add(strLoginUser);
string[] bResult = DBO.CloseMonthSKU_TRACE(ParameterList, DBT);
if (bResult[3].ToString() != "")
{
throw new System.Exception(bResult[3].ToString());
}
else
{
DBT.Commit();
arl_Return.Add("TRUE");
arl_Return.Add(bResult[0].ToString()); //總筆數
arl_Return.Add(bResult[1].ToString()); //正確筆數
arl_Return.Add(bResult[2].ToString()); //錯誤筆數
}
}
else
{
arl_Return.Add("TRUE");
arl_Return.Add(dtTmp.Rows.Count);
arl_Return.Add(dtTmp.Rows.Count - iWrong);
arl_Return.Add(iWrong);
}
}
catch (Exception ex)
{
arl_Return.Add("FALSE");
arl_Return.Add(ex.Message);
//獨立呼叫Transcation失敗
DBT.Rollback();
}
finally
{
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
return arl_Return;
}
/// <summary>
/// 查詢商品合約交易補收錯誤訊息
/// </summary>
/// <param name="ParameterList"></param>
/// <returns></returns>
public DataTable QueryItemTmp(ArrayList ParameterList)
{
try
{
DBO.VAM031_DBO DBO = new VAM031_DBO(ref USEDB);
DataTable dt = DBO.QueryItemTmp(ParameterList);
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public ArrayList ImportFile(DataSet ds_Excel, DateTime d_CreateDate, string s_LoginUser, string s_DBConnString, string s_AP_FileName)
{
ArrayList arl_Return = new ArrayList();
int i_FileToTemp_Total_count = 0;
string strErrMsg = "";
ArrayList ParameterList = new ArrayList();
try
{
ParameterList.Clear();
DBO.VAM031_DBO DBO = new VAM031_DBO(ref USEDB);
#region 先清除前一次，同一使用者執行的匯入暫存資訊與錯誤資訊
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
DBO.DeleteItemTmp(ParameterList);
#endregion
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
foreach (DataRow dRow in ds_Excel.Tables[0].Rows)
{
i_FileToTemp_Total_count += 1;
string V_STATUS = "";
string V_DATASOURCE = s_AP_FileName;
string V_DATATYPE = "1";
string V_ERRORMEMO = "";
string V_ITEM = dRow["品號"].ToString().Trim();
string V_PERIOD_S = dRow["期別起"].ToString().Trim();
string V_PERIOD_E = dRow["期別迄"].ToString().Trim();
string V_CLOSE_MONTH_S = dRow["補收年月起"].ToString().Trim();
string V_CLOSE_MONTH_E = dRow["補收年月迄"].ToString().Trim();
string V_CREATEUID = s_LoginUser;
string V_CREATEDATE = d_CreateDate.AddSeconds(i_FileToTemp_Total_count).ToString("yyyy/MM/dd HH:mm:ss");
strErrMsg = "";
DateTime dtTEMP;//檢查日期格式用的 不會真正使用此變數
#region 格式檢查
if (V_ITEM == string.Empty)
{
strErrMsg += "品號為必填,";
}
else if (CheckLength(V_ITEM, 10, "品號", strErrMsg, out strErrMsg) == false)
{
strErrMsg += "品號格式錯誤,";
}
else { }
if (V_PERIOD_S == string.Empty || V_PERIOD_E == string.Empty)
{
strErrMsg += "期別起迄為必填,";
}
else if (CheckLength(V_PERIOD_S, 6, "期別", strErrMsg, out strErrMsg) == false)
{
strErrMsg += "期別起迄格式錯誤,";
}
else if (CheckLength(V_PERIOD_E, 6, "期別", strErrMsg, out strErrMsg) == false)
{
strErrMsg += "期別起迄格式錯誤,";
}
else { }
if (V_CLOSE_MONTH_S == string.Empty || V_CLOSE_MONTH_E == string.Empty)
{
strErrMsg += "補收年月起迄為必填,";
}
else if (CheckLength(V_CLOSE_MONTH_S, 6, "補收年月起", strErrMsg, out strErrMsg) == false ||
CheckLength(V_CLOSE_MONTH_E, 6, "補收年月起", strErrMsg, out strErrMsg) == false
)
{
}
else if (DateTime.TryParseExact(V_CLOSE_MONTH_S + "01", "yyyyMMdd", null, DateTimeStyles.None, out dtTEMP) == false ||
DateTime.TryParseExact(V_CLOSE_MONTH_E + "01", "yyyyMMdd", null, DateTimeStyles.None, out dtTEMP) == false
)
{
strErrMsg += "補收年月起迄格式錯誤,";
}
else if (Decimal.Parse(V_CLOSE_MONTH_S) > int.Parse(V_CLOSE_MONTH_E))
{
strErrMsg += "補收年月起不可大於補收年月迄,";
}
else
{
}
V_STATUS = (strErrMsg == "") ? "1" : "2"; //無錯誤訊表示為正常資料
#endregion
V_ERRORMEMO = strErrMsg;
ParameterList.Clear();
ParameterList.Add(i_FileToTemp_Total_count); //0.序號
ParameterList.Add(V_STATUS); //1.V_STATUS 1:正常資料； 2:錯誤資料
ParameterList.Add(V_DATASOURCE); //2.V_DATASOURCE 匯入檔名/TMP檔名
ParameterList.Add(V_DATATYPE); //3.V_DATATYPE
ParameterList.Add(V_ERRORMEMO); //4.V_ERRORMEMO
if (V_STATUS == "1")
{
ParameterList.Add(V_ITEM); //5.V_ITEM 品號
ParameterList.Add(V_PERIOD_S); //6.V_PERIOD_S 期別起
ParameterList.Add(V_PERIOD_E); //7.V_PERIOD_E 期別迄
ParameterList.Add(V_CLOSE_MONTH_S); //8.V_CLOSE_MONTH_S 結帳年月起
ParameterList.Add(V_CLOSE_MONTH_E); //9.V_CLOSE_MONTH_E 結帳年月迄
}
else
{
//為避免長度問題造成匯入DB出錯,所以有錯誤則不匯入相關資料
ParameterList.Add(""); //5.V_ITEM 品號
ParameterList.Add(""); //6.V_PERIOD_S 期別起
ParameterList.Add(""); //7.V_PERIOD_E 期別迄
ParameterList.Add(""); //8.V_CLOSE_MONTH_S 結帳年月起
ParameterList.Add(""); //9.V_CLOSE_MONTH_E 結帳年月起
}
ParameterList.Add(V_CREATEUID); //10.V_CREATEUID
ParameterList.Add(V_CREATEDATE); //11.V_CREATEDATE
DBO.AddItemTmp(ParameterList, DBT);
}
DBT.Commit();
//取得匯入TMP 的資料
int i_FileToTemp_Right_count = 0;
int i_FileToTemp_Wrong_count = 0;
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
ParameterList.Add("");
DataTable dtTmp = DBO.QueryItemTmp(ParameterList);
DataRow[] FindRows;
FindRows = dtTmp.Select("STATUS='1'");
i_FileToTemp_Right_count = FindRows.Length;
FindRows = dtTmp.Select("STATUS='2'");
i_FileToTemp_Wrong_count = FindRows.Length;
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("FALSE");
arl_Return.Add(ex.Message);
//獨立呼叫Transcation失敗
DBT.Rollback();
}
finally
{
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
return arl_Return;
}