/// <summary> /// 傳入值: /// @結帳年月 /// /// 回傳值: /// @月結狀態FLAG /// /// TABLE:VDS_VAM_MONTH_CHECK_OUT(供應商月結紀錄檔) /// /// 說明:檢查ACCT_MONTH=〔@結帳年月〕是否存在,如果有值表示已經月結了,如果沒有值表示還沒月結,回傳檢查狀態@月結狀態FLAG。 /// </summary> public string VendorMonthCloseYN(string V_CHECK_MONTH,string V_VENDOR, string V_VENDOR_CLOSE_TYPE, int N_Close_Status) { try { string V_CLOSE_FLAG = ""; DBO.VAM031_DBO dbo = new VAM031_DBO(ref USEDB); dbo.doQueryVendorMonthCloseYN(V_CHECK_MONTH,V_VENDOR, V_VENDOR_CLOSE_TYPE, N_Close_Status, out V_CLOSE_FLAG); return V_CLOSE_FLAG; } catch (Exception ex) { throw ex; } }
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; string strErrMsg = ""; Decimal intVal; ArrayList ParameterList = new ArrayList(); ArrayList arl_Vendor_Check = new ArrayList(); try { ParameterList.Clear(); DBO.VAM031_DBO DBO = new VAM031_DBO(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) { i_FileToTemp_Total_count += 1; string V_STATUS = ""; string V_DATASOURCE = s_AP_FileName; string V_DATATYPE = "1"; string V_ERRORMEMO = ""; string V_VENDOR = 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_VENDOR == string.Empty) { strErrMsg += "供應商編號為必填,"; } else if (CheckLength(V_VENDOR, 10, "供應商編號", strErrMsg, out strErrMsg) == false) { } else if (Decimal.TryParse(V_VENDOR, out intVal) == false) { strErrMsg += "供應商編號格式錯誤,"; } else { string V_VENDOR_FLAG = ""; DBO.CheckVendorContract(V_VENDOR, out V_VENDOR_FLAG); if (V_VENDOR_FLAG == "N") { strErrMsg += "找不到生效中供應商合約,"; } else if (V_VENDOR_FLAG == "X") { strErrMsg += "找到多筆生效中供應商合約,"; } else { int s_chk = arl_Vendor_Check.IndexOf(V_VENDOR); if (s_chk != -1) { strErrMsg += "供應商編號重複,"; } } } 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 { string V_CLOSE_FLAG=""; //DBO.doQueryVendorMonthCloseYN(V_CLOSE_MONTH_E,"1",1, out V_CLOSE_FLAG); //if (V_CLOSE_FLAG == "N") //{ // strErrMsg += "補收年月迄尚未月結,"; //} DBO.doQueryVendorMonthCloseYN(V_CLOSE_MONTH_E,V_VENDOR, "1", 3, out V_CLOSE_FLAG); if (V_CLOSE_FLAG == "Y") { strErrMsg += "補收年月已曾做過補收,請解除補收之後再重新匯入,"; } } V_STATUS = (strErrMsg == "") ? "1" : "2"; //無錯誤訊表示為正常資料 arl_Vendor_Check.Add(V_VENDOR); #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_CLOSE_MONTH_S); //5.V_CLOSE_MONTH_S 結帳年月起 ParameterList.Add(V_CLOSE_MONTH_E); //6.V_CLOSE_MONTH_E 結帳年月迄 ParameterList.Add(V_VENDOR); //7.V_VENDOR 供應商編號 } else { //為避免長度問題造成匯入DB出錯,所以有錯誤則不匯入相關資料 ParameterList.Add(""); //5.V_CLOSE_MONTH_S 結帳年月起 ParameterList.Add(""); //6.V_CLOSE_MONTH_E 結帳年月起 ParameterList.Add(""); //7.V_VENDOR 供應商編號 } ParameterList.Add(V_CREATEUID); //8.V_CREATEUID ParameterList.Add(V_CREATEDATE); //9.V_CREATEDATE DBO.Add_Tmp(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; }