public DataTable QueryTmp(ArrayList ParameterList)
{
try
{
CAA_32DBO DBO = new CAA_32DBO(ref USEDB);
DataTable dt = DBO.QueryTmp(ParameterList);
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public ArrayList TmpToDB(string strLoginUser, string ConntionDB)
{
ArrayList arl_Return = new ArrayList();
try
{
int iTotal = 0;
int iRight = 0;
ArrayList ParameterList = new ArrayList();
CAA_32DBO DBO = new CAA_32DBO(ref USEDB);
ParameterList.Clear();
ParameterList.Add(strLoginUser);
ParameterList.Add("");
ParameterList.Add("");
DataTable dt = DBO.QueryTmp(ParameterList);
iTotal = dt.Rows.Count;
string strCREATEUID = strLoginUser;
for (int i = 0; i < dt.Rows.Count; i++)
{
string strID = dt.Rows[i]["ID"].ToString();
string strWASH_AMT_TOT = dt.Rows[i]["WASH_AMT_TOT"].ToString();
string strSOURCE_TYPE = dt.Rows[i]["SOURCE_TYPE"].ToString();
string strSecond_NO = dt.Rows[i]["Second_NO"].ToString();
string strCREATEDATE = dt.Rows[i]["CREATEDATE"].ToString();
int iCheckREMAIN_AMT = CheckREMAIN_AMT(strSecond_NO, strSOURCE_TYPE, strWASH_AMT_TOT);
string strErrMsg = "";
#region 20110117 純萍異動規則
//檢查該筆資料是否月結,若立沖帳已月結則不可進行次代轉ACCT沖帳處理
DateTime WASH_DATE = Convert.ToDateTime(dt.Rows[i]["WASH_DATE"]);
string strWASH_DATE = WASH_DATE.Year.ToString() + WASH_DATE.Month.ToString();
if (CheckClose(strWASH_DATE) == true)
{
strErrMsg += strWASH_DATE + "立沖帳已月結,";
}
//沖帳年月則自沖帳日期擷取
if (DBO.CHECK_CLOSE_MONTH(strSOURCE_TYPE, strSecond_NO) == "")
{
strErrMsg += "抓取結帳年月出現錯誤,";
}
if (DBO.QUERY_SDATE(strSOURCE_TYPE, strSecond_NO) == "")
{
strErrMsg += "抓取單據日期出現錯誤,";
}
#endregion
if (iCheckREMAIN_AMT == 0)
strErrMsg += "找不到符合的單據資料,";
else if (iCheckREMAIN_AMT == 1)
strErrMsg += "沖帳總額不足,";
if (strErrMsg != "")
{
strErrMsg = strErrMsg.Substring(0, strErrMsg.Length - 1);//去除最後一個逗點
ParameterList.Clear();
ParameterList.Add(strID);
ParameterList.Add("2");
ParameterList.Add("1");
ParameterList.Add(strErrMsg);
ParameterList.Add(strCREATEUID);
ParameterList.Add(strCREATEDATE);
DBO.Update_Tmp(ParameterList, null);
}
}
ParameterList.Clear();
ParameterList.Add(strCREATEUID);
DBO.CheckImport(ParameterList);
ParameterList.Clear();
ParameterList.Add(strCREATEUID);
ParameterList.Add("");
ParameterList.Add("");
DataTable dtTmp = DBO.QueryTmp(ParameterList);
DataRow[] FindRows;
FindRows = dtTmp.Select("STATUS='2'");
int iWrong = FindRows.Length;
if (iWrong == 0)
{
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
for (int i = 0; i < dt.Rows.Count; i++)
{
string strID = dt.Rows[i]["ID"].ToString();
#region 20110117 純萍異動規則
//20110117 Rika改原[沖帳年月]為[沖帳日期] 匯入前期資料表增加沖帳日期欄位
//沖帳年月則自沖帳日期擷取
string strWASH_DATE = dt.Rows[i]["WASH_DATE"].ToString();
#endregion
string strOLD_CREATEDATE = dt.Rows[i]["CREATEDATE"].ToString();
string strCREATEDATE = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
string strSOURCE_TYPE = dt.Rows[i]["SOURCE_TYPE"].ToString();
string strSecond_NO = dt.Rows[i]["Second_NO"].ToString();
//string strSDATE = dt.Rows[i]["SDATE"].ToString(); 20110301 Rika 單據日期改為系統抓取
string strSDATE = DBO.QUERY_SDATE(strSOURCE_TYPE, strSecond_NO);
//結帳年月原取自匯入資料,改為抓取系統所判定的結帳年月
string strCLOSE_MONTH = DBO.CHECK_CLOSE_MONTH(strSOURCE_TYPE, strSecond_NO);
string strSTORE_ID = dt.Rows[i]["STORE_ID"].ToString();
string strWASH_AMT = dt.Rows[i]["WASH_AMT"].ToString();
string strWASH_CHAGE = dt.Rows[i]["WASH_CHAGE"].ToString();
string strWASH_AMT_TOT = dt.Rows[i]["WASH_AMT_TOT"].ToString();
string strMEMO = dt.Rows[i]["MEMO"].ToString();
string s_DIFF_AMT = dt.Rows[i]["DIFF_AMT"].ToString();
string s_OTHER_DIFF = dt.Rows[i]["OTHER_DIFF"].ToString();
string s_REMIT_DIFF = dt.Rows[i]["REMIT_DIFF"].ToString();
//Update Remain Amt
{
ParameterList.Clear();
ParameterList.Add(strWASH_AMT_TOT);
ParameterList.Add(strSOURCE_TYPE);
ParameterList.Add(strSecond_NO);
ParameterList.Add(strCREATEUID);
ParameterList.Add(strCREATEDATE);
DBO.UPDATE_REMAIN_AMT(ParameterList, DBT);
}
//Add PREVIOUS_MAIN
{
ParameterList.Clear();
ParameterList.Add(strCLOSE_MONTH);
ParameterList.Add(strSOURCE_TYPE);
ParameterList.Add(strSDATE);
ParameterList.Add(strSecond_NO);
ParameterList.Add(strSTORE_ID);
ParameterList.Add(strWASH_AMT);
ParameterList.Add(strWASH_CHAGE);
ParameterList.Add(strWASH_AMT_TOT);
ParameterList.Add(strMEMO);
ParameterList.Add(strCREATEUID);
ParameterList.Add(strCREATEDATE);
ParameterList.Add(strWASH_DATE); //20110117 Rika改原[沖帳年月]為[沖帳日期]
#region 20100609秀玲新增規則
ParameterList.Add(s_DIFF_AMT);//尾差
ParameterList.Add(s_OTHER_DIFF);//其他差異
ParameterList.Add(s_REMIT_DIFF);//匯兌差異
#endregion
DBO.Add_PREVIOUS(ParameterList, DBT);
}
//CONNECT_MAIN
{
string V_CUBE_NO = "";
string V_CUBE_TYPE = "";
string V_WASH_NO = "";
string V_WASH_TYPE = "";
string V_LINK_NO = "";
string V_LINK_TYPE = "";
string V_WASH_AMT = "";
string V_SOURCE_TYPE = "";
string V_WASH_DATE = "";
if (strSOURCE_TYPE == "1")
{
V_CUBE_NO = "AA20100331";
V_CUBE_TYPE = "1";
V_WASH_NO = strSecond_NO;
V_WASH_TYPE = "1";
V_LINK_TYPE = "1";
V_LINK_NO = strSecond_NO;
V_WASH_AMT = strWASH_AMT_TOT;
V_SOURCE_TYPE = "3";
}
else if (strSOURCE_TYPE == "2")
{
V_CUBE_NO = "AA20100331";
V_CUBE_TYPE = "1";
V_WASH_NO = strSecond_NO;
V_WASH_TYPE = "2";
V_LINK_TYPE = "2";
V_LINK_NO = strSecond_NO;
V_WASH_AMT = strWASH_AMT_TOT;
V_SOURCE_TYPE = "3";
}
else if (strSOURCE_TYPE == "3")
{
V_CUBE_NO = strSecond_NO;
V_CUBE_TYPE = "1";
V_WASH_NO = "BB20100331";
V_WASH_TYPE = "6";
V_LINK_TYPE = "4";
V_LINK_NO = "BB20100331";
V_WASH_AMT = strWASH_AMT_TOT;
V_SOURCE_TYPE = "3";
}
else if (strSOURCE_TYPE == "4")
{
V_CUBE_NO = "AA20100331";
V_CUBE_TYPE = "1";
V_WASH_NO = strSecond_NO;
V_WASH_TYPE = "4";
V_LINK_TYPE = "4";
V_LINK_NO = "BB20100331";
V_WASH_AMT = strWASH_AMT_TOT;
V_SOURCE_TYPE = "3";
}
else if (strSOURCE_TYPE == "5")
{
V_CUBE_NO = "AA20100331";
V_CUBE_TYPE = "1";
V_WASH_NO = strSecond_NO;
V_WASH_TYPE = "5";
V_LINK_TYPE = "4";
V_LINK_NO = "BB20100331";
V_WASH_AMT = strWASH_AMT_TOT;
V_SOURCE_TYPE = "3";
}
V_WASH_AMT = strWASH_AMT_TOT;//;
//20110117 純萍異動規則 改為抓取沖帳日期
V_WASH_DATE = strWASH_DATE;
ParameterList.Clear();
ParameterList.Add(V_CUBE_NO);
ParameterList.Add(V_CUBE_TYPE);
ParameterList.Add(V_WASH_NO);
ParameterList.Add(V_WASH_TYPE);
ParameterList.Add(V_LINK_NO);
ParameterList.Add(V_LINK_TYPE);
ParameterList.Add(V_WASH_AMT);
ParameterList.Add(V_WASH_DATE);
ParameterList.Add(V_SOURCE_TYPE);
ParameterList.Add(strCREATEUID);
ParameterList.Add(strCREATEDATE);
DBO.Add_CONNECT(ParameterList, DBT);
ParameterList.Clear();
ParameterList.Add(strID);
ParameterList.Add("1");
ParameterList.Add("2");
ParameterList.Add("");
ParameterList.Add(strCREATEUID);
ParameterList.Add(strOLD_CREATEDATE);
DBO.Update_Tmp(ParameterList, DBT);
}
iRight++;
}
DBT.Commit();
arl_Return.Add("TRUE");
arl_Return.Add(iTotal);
arl_Return.Add(iRight);
arl_Return.Add(iWrong);
}
else
{
arl_Return.Add("TRUE");
arl_Return.Add(iTotal);
arl_Return.Add(iRight);
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;
}
private int CheckREMAIN_AMT(string strSecond_NO, string strSOURCE_TYPE, string strWASH_AMT_TOT)
{
int iResult = 0; //0.無資料 1.沖帳總額不足 2.OK
ArrayList ParameterList = new ArrayList();
try
{
CAA_32DBO DBO = new CAA_32DBO(ref USEDB);
ParameterList.Clear();
ParameterList.Add(strSOURCE_TYPE);
ParameterList.Add(strSecond_NO);
DataTable dt = DBO.QueryREMAIN_AMT(ParameterList);
if (dt.Rows.Count > 0)
{
if (int.Parse(dt.Rows[0]["REMAIN_AMT"].ToString()) < int.Parse(strWASH_AMT_TOT))
{
iResult = 1;
}
else
{
iResult = 2;
}
}
}
catch (Exception ex)
{
throw ex;
}
return iResult;
}
public ArrayList FileToTmp(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;
int ChkCount = 0;
string strErrMsg = "";
Int32 iErrCount = 0;
Double doubleVal;
ArrayList ParameterList = new ArrayList();
try
{
ParameterList.Clear();
CAA_32DBO DBO = new CAA_32DBO(ref USEDB);
#region 先清除前一次，同一使用者執行的匯入暫存資訊與錯誤資訊
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
DBO.DeleteTmp(ParameterList);
#endregion
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
foreach (DataRow dRow in ds_Excel.Tables[0].Rows)
{
iErrCount = 0;
string V_ID = dRow["序號"].ToString().Trim();
string V_STATUS = "";
string V_DATASOURCE = s_AP_FileName;
string V_DATATYPE = "1";
string V_ERRORMEMO = "";
//20110117 Rika改原[沖帳年月]為[沖帳日期]
string V_WASH_DATE = dRow["沖帳日期"].ToString().Trim();
string V_CLOSE_MONTH = dRow["結帳年月"].ToString().Trim();
string V_SOURCE_TYPE = dRow["來源別"].ToString().Trim();
string V_SDATE = dRow["日期"].ToString().Trim();
string V_SECOND_NO = dRow["單號(次代)"].ToString().Trim();
string V_STORE_ID = dRow["店號"].ToString().Trim();
string V_WASH_AMT = dRow["繳款金額"].ToString().Trim();
string V_WASH_CHAGE = dRow["手續費/郵資"].ToString().Trim();
string V_WASH_AMT_TO = dRow["沖帳總額"].ToString().Trim();
string V_MEMO = 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");
string V_UPDATEUID = s_LoginUser;
string V_UPDATEDATE = d_CreateDate.ToString("yyyy/MM/dd");
string s_DIFF_AMT = dRow["尾差"].ToString();
string s_OTHER_DIFF = dRow["其他差異"].ToString();
string s_REMIT_DIFF = dRow["匯兌損益"].ToString();
strErrMsg = "";
DateTime dtTEMP;//檢查日期格式用的 不會真正使用此變數
#region 20110117異動規格 純萍改原[沖帳年月]為[沖帳日期] 且須判斷該沖帳年月是否已月結
if (V_WASH_DATE == string.Empty)
{
strErrMsg += "沖帳日期欄位為必填,";
}
//else if (CheckLength(V_WASH_DATE, 8, "沖帳日期", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
//{
// //超過DB欄位
// V_WASH_MONTH = SubStr(V_WASH_DATE, 0, 8);
//}
else if (DateTime.TryParseExact(V_WASH_DATE, "yyyyMMdd", null, DateTimeStyles.None, out dtTEMP) == false)
{
strErrMsg += "沖帳日期格式錯誤,";
V_WASH_DATE = "";
}
else
{
string V_WASH_MONTH = V_WASH_DATE.Substring(0, 6);
//檢查該筆資料是否月結,若立沖帳已月結則不可進行次代轉ACCT沖帳處理
if (CheckClose(V_WASH_MONTH) == true)
{
strErrMsg += V_WASH_MONTH + "立沖帳已月結,";
}
}
#endregion
//20110117 純萍異動規格 將結帳年月從必填改為選填 且不採用此欄位資料為匯入資料的結帳年月
/* if (V_CLOSE_MONTH == string.Empty)
{
strErrMsg += "結帳年月欄位為必填,";
}
else if (CheckLength(V_CLOSE_MONTH, 6, "結帳年月", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_CLOSE_MONTH = SubStr(V_CLOSE_MONTH, 0, 6);
}
else if (DateTime.TryParseExact(V_CLOSE_MONTH + "01", "yyyyMMdd", null, DateTimeStyles.None, out dtTEMP) == false)
{
strErrMsg += "結帳年月格式錯誤,";
}
if (strErrMsg == "") //如果結帳年月和沖帳年月格式都沒有錯的話,在做這個檢查
{
if (int.Parse(V_WASH_MONTH) < int.Parse(V_CLOSE_MONTH))
{
strErrMsg += "結帳年月不可大於沖帳年月,";
}
} */
if (V_SOURCE_TYPE == string.Empty)
{
strErrMsg += "來源別欄位為必填,";
}
else if (CheckLength(V_SOURCE_TYPE, 1, "來源別", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_SOURCE_TYPE = SubStr(V_SOURCE_TYPE, 0, 1);
}
else if (!((V_SOURCE_TYPE == "1") || (V_SOURCE_TYPE == "2") || (V_SOURCE_TYPE == "3") || (V_SOURCE_TYPE == "4") || (V_SOURCE_TYPE == "5")))
{
strErrMsg += "來源別資料只允許 1 ~ 5,";
}
//20110301 Rika 改單據日期為由系統抓取
//if (V_SDATE == string.Empty)
//{
// strErrMsg += "日期欄位為必填,";
//}
//else
//if (DateTime.TryParseExact(V_SDATE, "yyyyMMdd", null, DateTimeStyles.None, out dtTEMP) == false)
//{
// strErrMsg += "日欄位必須為日期型態或日期格式錯誤,";
// V_SDATE = "";
//}
if (V_SDATE != string.Empty)
{
if (DateTime.TryParseExact(V_SDATE, "yyyyMMdd", null, DateTimeStyles.None, out dtTEMP) == false)
{
strErrMsg += "日期欄位必須為日期型態或日期格式錯誤,";
V_SDATE = "";
}
}
if (V_SECOND_NO == string.Empty)
{
strErrMsg += "單號(次代)欄位為必填,";
}
else
{
if (CheckLength(V_SECOND_NO, 14, "單號(次代)", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_SECOND_NO = SubStr(V_SECOND_NO, 0, 14);
}
//20110303 Rika 新增判斷
switch (V_SOURCE_TYPE)
{
case "1":
if(V_SECOND_NO.Substring(0,2)!="CR")
{
strErrMsg += "單據號碼所對應的來源別錯誤,";
}
break;
case "2":
if(V_SECOND_NO.Substring(0,2)!="BR")
{
strErrMsg += "單據號碼所對應的來源別錯誤,";
}
break;
case "3":
if(V_SECOND_NO.Length != 10)
{
strErrMsg += "單據號碼所對應的來源別錯誤,";
}
break;
case "4":
if(!(SubStr(V_SECOND_NO,0,1)=="W" || SubStr(V_SECOND_NO,0,1)=="Z"))
{
strErrMsg += "單據號碼所對應的來源別錯誤,";
}
break;
case "5":
if(SubStr(V_SECOND_NO,0,2)!="TR")
{
strErrMsg += "單據號碼所對應的來源別錯誤,";
}
break;
}
}
if (V_STORE_ID == string.Empty)
{
strErrMsg += "店號欄位為必填,";
}
else
{
if (CheckLength(V_STORE_ID, 10, "店號", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{ //超過DB欄位
V_STORE_ID = SubStr(V_STORE_ID, 0, 10);
}
//若已經有來源別錯誤時可避免進入判斷造成系統錯誤
if (strErrMsg == "")
{
//20110303 Rika 新增判斷單據編號所對應的店號
//20110706 Rika 單據號碼錯誤判斷
string str_STORE = DBO.Check_Store(V_SECOND_NO, V_SOURCE_TYPE, V_STORE_ID).ToString();
if (str_STORE == "0")
{
strErrMsg += "找不到單據號碼,";
}
else if (str_STORE != V_STORE_ID)
{
strErrMsg += "單據號碼所對應之店號錯誤,";
}
}
}
if (V_WASH_AMT == string.Empty)
{
strErrMsg += "繳款金額欄位為必填,";
V_WASH_AMT = "0";
}
else if (CheckLength(V_WASH_AMT, 12, "繳款金額", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_WASH_AMT = SubStr(V_WASH_AMT, 0, 12);
}
else if (double.TryParse(V_WASH_AMT, out doubleVal) == false)
{
strErrMsg += "繳款金額必須為數值型態,";
V_WASH_AMT = "0";
}
if (V_WASH_CHAGE == string.Empty)
{
strErrMsg += "手續費/郵資欄位為必填,";
V_WASH_CHAGE = "0";
}
else if (CheckLength(V_WASH_CHAGE, 8, "手續費/郵資", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_WASH_CHAGE = SubStr(V_WASH_CHAGE, 0, 8);
}
else if (double.TryParse(V_WASH_CHAGE, out doubleVal) == false)
{
strErrMsg += "手續費/郵資必須為數值型態,";
V_WASH_CHAGE = "0";
}
if (V_WASH_AMT_TO == string.Empty)
{
strErrMsg += "沖帳總額欄位為必填,";
V_WASH_AMT_TO = "0";
}
else if (CheckLength(V_WASH_AMT_TO, 12, "沖帳總額", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_WASH_CHAGE = SubStr(V_WASH_AMT_TO, 0, 12);
}
else if (double.TryParse(V_WASH_AMT_TO, out doubleVal) == false)
{
strErrMsg += "沖帳總額必須為數值型態,";
V_WASH_AMT_TO = "0";
}
#region 20100609秀玲新增規則
#region 尾差
if (s_DIFF_AMT == string.Empty)
{
strErrMsg += "尾差欄位為必填,";
s_DIFF_AMT = "0";
}
else if (CheckLength(s_DIFF_AMT, 3, "尾差", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
s_DIFF_AMT = SubStr(s_DIFF_AMT, 0, 12);
}
else if (double.TryParse(s_DIFF_AMT, out doubleVal) == false)
{
strErrMsg += "尾差必須為數值型態,";
s_DIFF_AMT = "0";
}
#endregion
#region 其他差異
if (s_OTHER_DIFF == string.Empty)
{
strErrMsg += "其他差異欄位為必填,";
s_OTHER_DIFF = "0";
}
else if (CheckLength(s_OTHER_DIFF, 8, "其他差異", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
s_OTHER_DIFF = SubStr(s_OTHER_DIFF, 0, 12);
}
else if (double.TryParse(s_OTHER_DIFF, out doubleVal) == false)
{
strErrMsg += "其他差異必須為數值型態,";
s_OTHER_DIFF = "0";
}
#endregion
#region 匯兌損益
if (s_REMIT_DIFF == string.Empty)
{
strErrMsg += "匯兌損益欄位為必填,";
s_REMIT_DIFF = "0";
}
else if (CheckLength(s_REMIT_DIFF, 8, "匯兌損益", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
s_REMIT_DIFF = SubStr(s_REMIT_DIFF, 0, 12);
}
else if (double.TryParse(s_REMIT_DIFF, out doubleVal) == false)
{
strErrMsg += "匯兌損益必須為數值型態,";
s_REMIT_DIFF = "0";
}
#endregion
#endregion
#region 20100722秀玲新增規則
if (V_SOURCE_TYPE == "4")
{
int i_Check_Flg = 0;
CAACommon CAAComm = new CAACommon();
ParameterList.Clear();
ParameterList.Add(CAAComm.GetValueSetParameter(V_SECOND_NO, "string", false));//[折讓單號]
ParameterList.Add(CAAComm.GetValueSetParameter(s_LoginUser, "string", false));//[登入人員]
i_Check_Flg = DBO.CHECK_DISC_MAIN_FLG(ParameterList);
if (i_Check_Flg != 1)
{ strErrMsg += "此筆尚未憑證已回，故不允許匯入,"; }
}
#endregion
if ((int.Parse(V_WASH_CHAGE) + int.Parse(V_WASH_AMT) + int.Parse(s_DIFF_AMT) + int.Parse(s_OTHER_DIFF) + int.Parse(s_REMIT_DIFF)) != int.Parse(V_WASH_AMT_TO))
{
strErrMsg += "繳款金額+手續費/郵資+尾差+其他差異+匯兌損益與沖帳總額不合,";
}
if (strErrMsg == "")
{
int iCheckREMAIN_AMT = CheckREMAIN_AMT(V_SECOND_NO, V_SOURCE_TYPE, V_WASH_AMT_TO);
if (iCheckREMAIN_AMT == 0)
strErrMsg += "找不到符合的單據資料,";
else if (iCheckREMAIN_AMT == 1)
strErrMsg += "沖帳總額不足,";
#region 20110217 RIKA 新增規則
if (V_SOURCE_TYPE == "3" || V_SOURCE_TYPE == "4" || V_SOURCE_TYPE == "5")
{
if (int.Parse(V_WASH_CHAGE) != 0 || int.Parse(s_DIFF_AMT) != 0 || int.Parse(s_OTHER_DIFF) != 0 || int.Parse(s_REMIT_DIFF) != 0)
strErrMsg += "手續費/郵資/尾差/差異/匯兌損益沖帳餘額不足,";
}
else
{
DataRow ds_Check = DBO.CheckDIFF_AMT(V_SECOND_NO, V_SOURCE_TYPE);
if (int.Parse(ds_Check["WASH_AMT"].ToString()) < int.Parse(V_WASH_AMT))
strErrMsg += "繳款金額沖帳餘額不足,";
if (int.Parse(ds_Check["WASH_CHAGE"].ToString()) < int.Parse(V_WASH_CHAGE))
strErrMsg += "手續費/郵資沖帳餘額不足,";
if (int.Parse(ds_Check["DIFF_AMT"].ToString()) < int.Parse(s_DIFF_AMT))
strErrMsg += "尾差沖帳餘額不足,";
if (int.Parse(ds_Check["OTHER_DIFF"].ToString()) < int.Parse(s_OTHER_DIFF))
strErrMsg += "其他差異沖帳餘額不足,";
if (int.Parse(ds_Check["REMIT_DIFF"].ToString()) < int.Parse(s_REMIT_DIFF))
strErrMsg += "匯兌損益沖帳餘額不足,";
}
#endregion
}
//20110117 純萍異動規則 因結帳年月改為選填，所以改為判斷單號
DataRow[] FindDbls = ds_Excel.Tables[0].Select("[單號(次代)]='" + V_SECOND_NO + "'");
if (FindDbls.Length > 1)
strErrMsg += "單號(次代)有重覆資料,";
if (strErrMsg == "")
{
V_STATUS = "1";
}
else
{
V_STATUS = "2";
strErrMsg = strErrMsg.Substring(0, strErrMsg.Length - 1);//去除最後一個逗點
}
V_ERRORMEMO = strErrMsg;
ParameterList.Clear();
ParameterList.Add(V_ID); //0.N_ID
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
ParameterList.Add(V_CLOSE_MONTH); //5.V_CLOSE_MONTH 20110117 純萍異動規格 將結帳年月從必填改為選填
ParameterList.Add(V_SOURCE_TYPE); //6.V_SOURCE_TYPE 必填 1:專案代收 2:支票 3:發票 4:折讓 5:暫收
ParameterList.Add(V_SDATE); //7.V_SDATE 必填(YYYYMMDD) 20110301 Rika 改為由系統抓取
ParameterList.Add(V_SECOND_NO); //8.V_SECOND_NO 必填
ParameterList.Add(V_STORE_ID); //9.V_STORE_ID 必填
ParameterList.Add(V_WASH_AMT); //10.V_WASH_AMT 必填
ParameterList.Add(V_WASH_CHAGE); //11.V_WASH_CHAGE 必填
ParameterList.Add(V_WASH_AMT_TO); //12.V_WASH_AMT_TO 必填(需驗證欄位N + 欄位O = 欄位P)
ParameterList.Add(V_MEMO); //13.V_MEMO
ParameterList.Add(V_CREATEUID); //14.V_CREATEUID
ParameterList.Add(V_CREATEDATE); //15.V_CREATEDATE
ParameterList.Add(V_UPDATEUID); //16.V_UPDATEUID
ParameterList.Add(V_UPDATEDATE); //17.V_UPDATEDATE
ParameterList.Add(V_WASH_DATE); //18.V_WASH_DATE 20110117 異動規格 純萍改原[沖帳年月]為[沖帳日期](YYYYMMDD)
ParameterList.Add(s_DIFF_AMT); //19.V_DIFF_AMT
ParameterList.Add(s_OTHER_DIFF); //20.V_OTHER_DIFF
ParameterList.Add(s_REMIT_DIFF); //21.V_REMIT_DIFF
DBO.Add_Tmp(ParameterList, DBT);
i_FileToTemp_Total_count += 1;
}
DBT.Commit();
//取得匯入TMP 的資料
int i_FileToTemp_Right_count = 0;
int i_FileToTemp_Wrong_count = 0;
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
ParameterList.Add("");
ParameterList.Add("");
DataTable dtTmp = DBO.QueryTmp(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;
}
/// <summary>
/// 回復轉一代資料
/// </summary>
/// <param name="ParameterList"></param>
/// <returns></returns>
public ArrayList UndoPreData(ArrayList ParameterList, string ConntionDB)
{
ArrayList arl_Return = new ArrayList();
try
{
CAA_32DBO DBO = new CAA_32DBO(ref USEDB);
string strWash_month = ParameterList[0].ToString();
bool chkClose = CheckClose(strWash_month);//WASH_MONTH
if (chkClose == true)
{
arl_Return.Add("FALSE");
arl_Return.Add("立沖帳已月結，請解除月結後再執行本項功能");
return arl_Return;
}
else
{
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
//Update Remain Amt And Wash_STATUS
int WashAmt= DBO.UNDO_REMAIN_AMT(ParameterList, DBT);
//Add PREVIOUS_MAIN
DBO.DELETE_PREVIOUS(ParameterList, DBT);
//CONNECT_MAIN
int SumConAmt=DBO.DELETE_CONNECT(ParameterList, DBT);
//若單據的沖帳金額不等於沖帳加總金額則
if (WashAmt != SumConAmt)
{
arl_Return.Add("FALSE");
arl_Return.Add("刪除沖帳失敗!");
DBT.Rollback();
}
else
{
arl_Return.Add("TRUE");
DBT.Commit();
}
}
}
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;
}
public ArrayList FileToTmp(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;
int ChkCount = 0;
string strErrMsg = "";
Int32 iErrCount = 0;
int iVal;
ArrayList ParameterList = new ArrayList();
try
{
ParameterList.Clear();
CAA_38DBO DBO = new CAA_38DBO(ref USEDB);
#region 先清除前一次，同一使用者執行的匯入暫存資訊與錯誤資訊
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
DBO.DeleteTmp(ParameterList);
#endregion
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
int i = 0;
foreach (DataRow dRow in ds_Excel.Tables[0].Rows)
{
iErrCount = 0;
i = i + 1;
int V_ID = i;
string V_STATUS = "";
string V_DATASOURCE = s_AP_FileName;
string V_DATATYPE = "1";
string V_ERRORMEMO = "";
string V_WASH_DATE = dRow["沖帳日期"].ToString().Trim();
string V_CUBE_NO = dRow["發票號碼"].ToString().Trim();
string V_WASH_NO = dRow["沖帳號碼"].ToString().Trim();
string V_LINK_NO = dRow["關連號碼"].ToString().Trim();
string s_DIFF_AMT = dRow["尾差"].ToString();
string s_OTHER_DIFF = dRow["其他差異"].ToString();
string s_REMIT_DIFF = dRow["匯兌損益"].ToString();
string V_WASH_AMT = dRow["沖帳金額"].ToString().Trim();
string V_WASH_TYPE = string.Empty;
string V_LINK_TYPE = string.Empty;
strErrMsg = "";
DateTime dtTEMP;//檢查日期格式用的 不會真正使用此變數
#region 沖帳日期檢核
if (V_WASH_DATE == string.Empty)
{
strErrMsg += "沖帳日期欄位為必填,";
}
else if (DateTime.TryParseExact(V_WASH_DATE, "yyyyMMdd", null, DateTimeStyles.None, out dtTEMP) == false)
{
strErrMsg += "沖帳日期格式錯誤,";
V_WASH_DATE = "";
}
else
{
string V_WASH_MONTH = V_WASH_DATE.Substring(0, 6);
//檢查該筆資料是否月結,若立沖帳已月結則不可進行沖帳處理
if (CheckClose(V_WASH_MONTH) == true)
{
strErrMsg += V_WASH_MONTH + "立沖帳已月結,";
}
DateTime.TryParseExact(V_WASH_DATE, "yyyyMMdd", null, DateTimeStyles.None, out dtTEMP);
if (DateTime.Compare(dtTEMP, DateTime.Now) > 0)
{
strErrMsg += "沖帳日期不可大於系統日,";
}
}
#endregion
#region 發票號碼檢核
if (V_CUBE_NO == string.Empty)
{
strErrMsg += "發票號碼欄位為必填,";
}
else
{
if (CheckLength(V_CUBE_NO, 14, "發票號碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_CUBE_NO = SubStr(V_CUBE_NO, 0, 14);
}
else
{
string str_Chk = DBO.Check_No(V_CUBE_NO, "6").ToString();
if (str_Chk != "1")
{
strErrMsg += "找不到發票號碼或該發票號碼已結案/作廢,";
}
}
}
#endregion
#region 沖帳號碼檢核
if (V_WASH_NO == string.Empty)
{
strErrMsg += "沖帳號碼欄位為必填,";
}
else
{
if (CheckLength(V_WASH_NO, 14, "沖帳號碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_WASH_NO = SubStr(V_WASH_NO, 0, 14);
}
else
{
if (V_WASH_NO.Substring(0, 2) == "CR")
{
V_WASH_TYPE = "1";
}
else if (V_WASH_NO.Substring(0, 2) == "BR")
{
V_WASH_TYPE = "2";
}
else if (V_WASH_NO.Substring(0, 2) == "DR")
{
V_WASH_TYPE = "3";
}
else if (V_WASH_NO.Substring(0, 2) == "TR")
{
V_WASH_TYPE = "5";
}
else if (SubStr(V_WASH_NO, 0, 1) == "W" || SubStr(V_WASH_NO, 0, 1) == "Z")
{
V_WASH_TYPE = "4";
}
else
{
V_WASH_TYPE = string.Empty;
strErrMsg += "沖帳單號錯誤,";
}
if (V_WASH_TYPE == "1" || V_WASH_TYPE == "2" || V_WASH_TYPE == "3")
{
if (V_WASH_NO != V_LINK_NO)
{
strErrMsg += "沖帳單據為支票/代收/應收應付調整時關連號碼應與沖帳號碼相同,";
}
}
else if ( V_WASH_TYPE == "5")
{
if (V_WASH_NO == V_LINK_NO)
{
strErrMsg += "沖帳單據為暫收時關連號碼不可與沖帳號碼相同,";
}
}
else if (V_WASH_TYPE == "4")
{
if (V_WASH_NO == V_LINK_NO)
{
strErrMsg += "沖帳單據為折讓時關連號碼不可與沖帳號碼相同,";
}
else
{
#region 憑證未回判斷
int i_Check_Flg = 0;
CAACommon CAAComm = new CAACommon();
CAA_32DBO DBO1 = new CAA_32DBO(ref USEDB);
ParameterList.Clear();
ParameterList.Add(CAAComm.GetValueSetParameter(V_WASH_NO, "string", false));//[折讓單號]
ParameterList.Add(CAAComm.GetValueSetParameter(s_LoginUser, "string", false));//[登入人員]
i_Check_Flg = DBO1.CHECK_DISC_MAIN_FLG(ParameterList);
if (i_Check_Flg != 1)
{ strErrMsg += "此筆貨找不到折讓資料或尚未憑證已回，故不允許匯入,"; }
#endregion
}
}
}
//若已經有來源別錯誤時可避免進入判斷造成系統錯誤
if (strErrMsg == "")
{
string str_Chk = DBO.Check_No(V_WASH_NO, V_WASH_TYPE).ToString();
if (str_Chk != "1")
{
strErrMsg += "找不到沖帳號碼或該沖帳號碼已結案/作廢/退票/轉暫收/轉其他收入,";
}
}
}
#endregion
#region 關連號碼檢核
if (V_LINK_NO == string.Empty)
{
strErrMsg += "關連號碼欄位為必填,";
}
else
{
if (CheckLength(V_LINK_NO, 14, "關連號碼", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_LINK_NO = SubStr(V_LINK_NO, 0, 14);
}
else
{
if (V_WASH_TYPE == "4" || V_WASH_TYPE == "5")
{
if (V_LINK_NO.Substring(0, 2) == "CR")
{
V_LINK_TYPE = "1";
}
else if (V_LINK_NO.Substring(0, 2) == "BR")
{
V_LINK_TYPE = "2";
}
else if (V_LINK_NO.Substring(0, 2) == "DR")
{
V_LINK_TYPE = "3";
DataRow[] FindDbls = ds_Excel.Tables[0].Select("[發票號碼]='" + V_CUBE_NO + "' AND [關連號碼]='" + V_LINK_NO + "' AND [沖帳號碼]='" + V_LINK_NO + "'");
if (FindDbls.Length !=1)
strErrMsg += "缺少發票沖應收應付調整資料(請參考範例補上),";
}
else
{
V_LINK_TYPE = string.Empty;
strErrMsg += "關連單號錯誤,";
}
}
else
{
V_LINK_TYPE = "0";
}
}
//若已經有來源別錯誤時可避免進入判斷造成系統錯誤
if (strErrMsg == "")
{
string str_Chk = "0";
if (V_LINK_TYPE == "0")
{
//沖帳資料為支票/代收/應收應付時,沖帳號碼與關連號碼相同
str_Chk = DBO.Check_No(V_LINK_NO, V_WASH_TYPE).ToString();
}
else
{
str_Chk = DBO.Check_No(V_LINK_NO, V_LINK_TYPE).ToString();
}
if (str_Chk != "1")
{
strErrMsg += "找不到關連號碼或該關連號碼已結案/作廢/退票/轉暫收/轉其他收入,";
}
}
}
#endregion
#region 尾差
if (s_DIFF_AMT == string.Empty)
{
strErrMsg += "尾差欄位為必填且為數值型態,";
s_DIFF_AMT = "0";
}
else if (CheckLength(s_DIFF_AMT, 3, "尾差", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
strErrMsg += "尾差數值過大,";
s_DIFF_AMT = "0";
}
else if (int.TryParse(s_DIFF_AMT, out iVal) == false)
{
strErrMsg += "尾差必須為數值型態,";
s_DIFF_AMT = "0";
}
#endregion
#region 其他差異
if (s_OTHER_DIFF == string.Empty)
{
strErrMsg += "其他差異欄位為必填且為數值型態,";
s_OTHER_DIFF = "0";
}
else if (CheckLength(s_OTHER_DIFF, 8, "其他差異", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
strErrMsg += "其他差異數值過大,";
s_OTHER_DIFF = "0";
}
else if (int.TryParse(s_OTHER_DIFF, out iVal) == false)
{
strErrMsg += "其他差異必須為數值型態,";
s_OTHER_DIFF = "0";
}
#endregion
#region 匯兌損益
if (s_REMIT_DIFF == string.Empty)
{
strErrMsg += "匯兌損益欄位為必填且為數值型態,";
s_REMIT_DIFF = "0";
}
else if (CheckLength(s_REMIT_DIFF, 8, "匯兌損益", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
strErrMsg += "匯兌損益數值過大,";
s_REMIT_DIFF = "0";
}
else if (int.TryParse(s_REMIT_DIFF, out iVal) == false)
{
strErrMsg += "匯兌損益必須為數值型態,";
s_REMIT_DIFF = "0";
}
#endregion
#region 沖帳金額檢核
if (V_WASH_AMT == string.Empty)
{
strErrMsg += "沖帳金額欄位為必填且為數值型態,";
V_WASH_AMT = "0";
}
else if (CheckLength(V_WASH_AMT, 12, "沖帳金額", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//超過DB欄位
V_WASH_AMT = "0";
strErrMsg += "沖帳金額過大,";
}
else if (int.TryParse(V_WASH_AMT, out iVal) == false)
{
strErrMsg += "沖帳金額必須為數值型態,";
V_WASH_AMT = "0";
}
else if (int.Parse(V_WASH_AMT) <0)
{
strErrMsg += "沖帳金額必須為正整數,";
V_WASH_AMT = "0";
}
#endregion
#region 餘額檢核
if (strErrMsg == "")
{
int V_DIFF_AMT = 0;
if (int.Parse(s_DIFF_AMT) != 0 || int.Parse(s_OTHER_DIFF) != 0 || int.Parse(s_REMIT_DIFF) != 0)
{
if (V_WASH_TYPE == "1" || V_WASH_TYPE == "2" || V_WASH_TYPE == "3")
{
V_DIFF_AMT = int.Parse(s_DIFF_AMT) + int.Parse(s_OTHER_DIFF) + int.Parse(s_REMIT_DIFF);
int iCheckREMAIN_AMT_DIFF = CheckREMAIN_AMT(V_WASH_NO, V_WASH_TYPE, (V_DIFF_AMT * -1).ToString());
if (iCheckREMAIN_AMT_DIFF == 0)
strErrMsg += "找不到符合的沖帳號碼或該號碼已結案,";
else if (iCheckREMAIN_AMT_DIFF == 1)
strErrMsg += "差異金額對應沖帳號碼餘額不足,";
}
else
{
strErrMsg += "沖帳單據類型無法新增尾差/其他差異/匯兌損益,";
s_DIFF_AMT = "0";
s_OTHER_DIFF = "0";
s_REMIT_DIFF = "0";
}
}
int iCheckREMAIN_AMT = CheckREMAIN_AMT(V_CUBE_NO, "6", V_WASH_AMT);
if (iCheckREMAIN_AMT == 0)
{
strErrMsg += "找不到符合的發票號碼或該號碼已結案,";
}
else if (iCheckREMAIN_AMT == 1)
{
strErrMsg += "發票餘額不足,";
}
int jCheckREMAIN_AMT = CheckREMAIN_AMT(V_WASH_NO, V_WASH_TYPE, Convert.ToString(int.Parse(V_WASH_AMT) - V_DIFF_AMT));
if (jCheckREMAIN_AMT == 0)
{
strErrMsg += "找不到符合的沖帳號碼或該號碼已結案,";
}
else if (jCheckREMAIN_AMT == 1)
{
strErrMsg += "沖帳餘額不足,";
}
}
#endregion
#region 資料重覆判斷
DataRow[] FindDbls1 = ds_Excel.Tables[0].Select("[發票號碼]='" + V_CUBE_NO + "' AND [沖帳號碼]='" + V_WASH_NO + "' AND [關連號碼]='" + V_LINK_NO + "'");
if (FindDbls1.Length > 1)
strErrMsg += "有重覆資料,";
if (strErrMsg == "")
{
V_STATUS = "1";
}
else
{
V_STATUS = "2";
strErrMsg = strErrMsg.Substring(0, strErrMsg.Length - 1);//去除最後一個逗點
}
#endregion
V_ERRORMEMO = strErrMsg;
ParameterList.Clear();
ParameterList.Add(V_ID); //0.N_ID
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
ParameterList.Add(V_WASH_DATE); //5.V_WASH_DATE 沖帳日期
ParameterList.Add(V_CUBE_NO);
ParameterList.Add(V_WASH_NO);
ParameterList.Add(V_WASH_TYPE);
ParameterList.Add(V_LINK_NO);
ParameterList.Add(V_LINK_TYPE);
ParameterList.Add(s_DIFF_AMT);
ParameterList.Add(s_OTHER_DIFF);
ParameterList.Add(s_REMIT_DIFF);
ParameterList.Add(V_WASH_AMT);
ParameterList.Add(s_LoginUser);
DBO.Add_Tmp(ParameterList, DBT);
i_FileToTemp_Total_count += 1;
}
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
DBO.CheckImport(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.QueryTmp(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;
}