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; }
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; }