//查詢過期合約檢查
public DataTable QueryVednorCotractOverdue(string V_CHECK_MONTH)
{
try
{
DBO.VAM031_DBO dbo = new VAM031_DBO(ref USEDB);
return dbo.doQueryVednorCotractOverdue(V_CHECK_MONTH);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 呼叫VDS_ACM_VAM03.SP_CloseMonthSKU
/// 參考附件：Package_SP_VDS_ACM_VAM03_商品月結算.doc
/// </summary>
public void CloseMonthSKU(string V_CHECK_MONTH, string V_USER_ID, out string V_MSG, out string V_STATUS_CREATEUID, out DateTime D_STATUS_CREATEDATE)
{
try
{
DBO.VAM031_DBO DBO = new VAM031_DBO(ref USEDB);
DBO.doCloseMonthSKU(V_CHECK_MONTH, V_USER_ID, out V_MSG, out V_STATUS_CREATEUID, out D_STATUS_CREATEDATE);
}
catch (Exception ex)
{
throw ex;
}
}
/// <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;
}
}
/// <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 string UnCloseItemTrace(ArrayList ParameterList)
{
try
{
DBO.VAM031_DBO DBO = new VAM031_DBO(ref USEDB);
string resault = DBO.doUnCloseItemTrace(ParameterList);
return resault;
}
catch (Exception ex)
{
throw ex;
}
}
public string ItemCloseYN(ArrayList ParameterList)
{
try
{
DBO.VAM031_DBO DBO = new VAM031_DBO(ref USEDB);
string resault = DBO.doQueryItemCloseYN(ParameterList);
return resault;
}
catch (Exception ex)
{
throw ex;
}
}
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;
}
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;
}
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;
}
/// <summary>
/// 呼叫VDS_ACM_VAM03.SP_UnCheckMonthSKU
/// 傳入參數：
/// 〔@結帳年月〕acct_month
/// 傳回訊息：
/// 〔@商品月結解除是否成功〕
///
///
/// table:
/// vds_vam_subsidy_mon_cls(供應商各項補貼月結檔)
/// vds_vam_sub_mon_cls_log
/// vds_vam_month_check_out(供應商帳務月結紀錄檔)
///
/// 說明：
/// 1.刪除vds_vam_subsidy_mon_cls(供應商各項補貼月結檔)
/// delete vds_vam_agm_mon_cls where check_month=〔@結帳年月〕
/// 刪除的資料要新增至log檔內vds_vam_sub_mon_cls_log
///
/// 3. 更新月結紀錄檔有效狀態
///
/// delete vds_ven_month_check_out（供應商月結紀錄檔）
/// where acct_month=〔@結帳年月〕
/// and vendor_close_type=2 //商品資料
/// 刪除的資料要新增至log檔內 vds_vam_check_out_log
/// </summary>
public void UnCheckMonthSKU(string V_CHECK_MONTH)
{
try
{
DBO.VAM031_DBO DBO = new VAM031_DBO(ref USEDB);
DBO.doUnCloseMonthSKU(V_CHECK_MONTH);
}
catch (Exception ex)
{
throw ex;
}
}