public void FileToTmp(int iType,int proType,string proID, DataTable dtTemp, DbTransaction RootDBT, string V_CREATEUID, out string ErrorMsg)
{
bool IsRootTranscation = false;
try
{
DBO.VDS_STM37_DBO dbo = new VDS_STM37_DBO(ref USEDB);
ErrorMsg = "";
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
#region 清空暫存檔
dbo.doDeleteTemp(iType, V_CREATEUID, DBT);
#endregion
#region FileToTmp
ArrayList ParameterList = new ArrayList();
if (dtTemp != null)
{
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
Double di;//檢查數字格式用的 不會真正使用此變數
ErrorMsg = "";
ParameterList.Clear();
if (iType == 1)
{
string STORE = dtTemp.Rows[i]["STORE"].ToString().Trim();//店號
string PRICE_SUBSIDY_PERCENT = dtTemp.Rows[i]["PRICE_SUBSIDY_PERCENT"].ToString().Trim();//門市折扣補貼
if (STORE == string.Empty)
{
ErrorMsg = "店號為必填;";
}
else if (STORE.Length > 6)
{
ErrorMsg += "店號長度過長;";
STORE = "";
}
else if (!IsWord(STORE))
{
ErrorMsg += "店號格式錯誤;";
}
if (PRICE_SUBSIDY_PERCENT == string.Empty)
{
ErrorMsg += "門市折扣補貼為必填;";
PRICE_SUBSIDY_PERCENT = "";
}
else if (!double.TryParse(PRICE_SUBSIDY_PERCENT, out di))
{
ErrorMsg += "門市折扣補貼格式錯誤;";
PRICE_SUBSIDY_PERCENT = "";
}
else if (Convert.ToDouble(PRICE_SUBSIDY_PERCENT) < 0 || Convert.ToDouble(PRICE_SUBSIDY_PERCENT) > 100)
{
ErrorMsg += "門市折扣補貼範圍為0~100;";
PRICE_SUBSIDY_PERCENT = "";
}
if (ErrorMsg == "")
{
DataRow[] drDetlArrs = dtTemp.Select(string.Format("STORE ='{0}' and PRICE_SUBSIDY_PERCENT ='{1}'",
dtTemp.Rows[i]["STORE"],
dtTemp.Rows[i]["PRICE_SUBSIDY_PERCENT"]
));
if (drDetlArrs.Length > 1)
{
ErrorMsg += "資料重複;";
}
}
ParameterList.Clear();
ParameterList.Add(dtTemp.Rows[i]["ROW_NUM"].ToString());//0
ParameterList.Add(STORE);//1
ParameterList.Add(PRICE_SUBSIDY_PERCENT);//2
ParameterList.Add("");//3
ParameterList.Add("");//4
ParameterList.Add("");//5
ParameterList.Add("");//6
}
else
{
string VENDOR = dtTemp.Rows[i]["VENDOR"].ToString().Trim();//供應商編號
string ITEM = dtTemp.Rows[i]["ITEM"].ToString().Trim();//品號
string PERIOD = dtTemp.Rows[i]["PERIOD"].ToString().Trim();//期別
string COST_SUBSIDY_PERCENT = dtTemp.Rows[i]["COST_SUBSIDY_PERCENT"].ToString().Trim();//供應商折扣補貼%
if(proType==1)
{
if (VENDOR == string.Empty)
{
ErrorMsg = "此書展供應商編號為必填;";
}
else if (VENDOR.Length > 10)
{
ErrorMsg += "供應商編號最多20個字元;";
VENDOR = "";
}
else if (!IsWord(VENDOR))
{
ErrorMsg += "供應商編號格式錯誤;";
}
ITEM = "";
PERIOD = "";
}
else
{
VENDOR = "";
if (ITEM == string.Empty)
{
ErrorMsg = "此書展品號為必填;";
}
else if (ITEM.Length != 6)
{
ErrorMsg += "品號長度不正確;";
ITEM = "";
}
else if (!IsNumber(ITEM))
{
ErrorMsg += "品號格式錯誤;";
}
if (PERIOD == string.Empty)
{
ErrorMsg += "此書展期別為必填;";
}
else if (PERIOD.Length != 6)
{
ErrorMsg += "期別長度不正確;";
PERIOD = "";
}
else if (!IsNumber(PERIOD))
{
ErrorMsg += "期別格式錯誤;";
}
}
if (COST_SUBSIDY_PERCENT == string.Empty)
{
ErrorMsg += "供應商折扣補貼為必填;";
COST_SUBSIDY_PERCENT = "";
}
else if (!double.TryParse(COST_SUBSIDY_PERCENT,out di))
{
ErrorMsg += "供應商折扣補貼格式錯誤;";
COST_SUBSIDY_PERCENT = "";
}
else if (Convert.ToDouble(COST_SUBSIDY_PERCENT) < 0 || Convert.ToDouble(COST_SUBSIDY_PERCENT) > 100)
{
ErrorMsg += "供應商折扣補貼範圍為0~100;";
COST_SUBSIDY_PERCENT = "";
}
if (ErrorMsg == "")
{
DataRow[] drDetlArrs;
if (proType == 1)
{
drDetlArrs = dtTemp.Select(string.Format("VENDOR ='{0}' and COST_SUBSIDY_PERCENT ='{1}'",
dtTemp.Rows[i]["VENDOR"],
dtTemp.Rows[i]["COST_SUBSIDY_PERCENT"]
));
}
else
{
drDetlArrs = dtTemp.Select(string.Format("ITEM ='{0}' and PERIOD ='{1}' and COST_SUBSIDY_PERCENT ='{2}'",
dtTemp.Rows[i]["ITEM"],
dtTemp.Rows[i]["PERIOD"],
dtTemp.Rows[i]["COST_SUBSIDY_PERCENT"]
));
}
if (drDetlArrs.Length > 1)
{
ErrorMsg += "資料重複;";
}
}
ParameterList.Add(dtTemp.Rows[i]["ROW_NUM"].ToString());//0
ParameterList.Add("");//1
ParameterList.Add("");//2
ParameterList.Add(VENDOR);//3
ParameterList.Add(ITEM);//4
ParameterList.Add(PERIOD);//5
ParameterList.Add(COST_SUBSIDY_PERCENT);//6
}
ParameterList.Add(ErrorMsg);//7
ParameterList.Add(V_CREATEUID);//8
if (ErrorMsg == "")
{
ParameterList.Add("1");//9
}
else
{
ParameterList.Add("2");
}
ParameterList.Add(proID);//10
ParameterList.Add(iType);//11
ParameterList.Add(proType);//12
dbo.doCreateTemp(ParameterList, DBT);
}
}
#endregion
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
IsRootTranscation = false;
}
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation == true)
DBT.Rollback();
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
#endregion
}
}
public DataTable QueryDup(string UID)
{
try
{
DBO.VDS_STM37_DBO dbo = new VDS_STM37_DBO(ref USEDB);
return dbo.QueryDup(UID);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable QueryPromoteS(string PROMOTE_ID, out int N_CNT_M, out int N_CNT_N)
{
try
{
DBO.VDS_STM37_DBO dbo = new VDS_STM37_DBO(ref USEDB);
return dbo.QueryPromoteS(PROMOTE_ID, out N_CNT_M, out N_CNT_N);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable QueryErrorByFind(int iType,int datatype, string V_CREATEUID, DateTime D_CREATEDATE_S, DateTime D_CREATEDATE_E)
{
try
{
DBO.VDS_STM37_DBO dbo = new VDS_STM37_DBO(ref USEDB);
return dbo.doQueryByFind(iType, datatype, V_CREATEUID, D_CREATEDATE_S, D_CREATEDATE_E);
}
catch (Exception ex)
{
throw ex;
}
}
public void TmpToDB(DbTransaction RootDBT, string V_CREATEUID, int type, out string ErrorMsg)
{
bool IsRootTranscation = false;
try
{
DBO.VDS_STM37_DBO dbo = new VDS_STM37_DBO(ref USEDB);
ErrorMsg = "";
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
#region TmpToDB
string V_RESULT = "1";
dbo.doTmpToDB(V_CREATEUID, type, DBT, out V_RESULT);
if (V_RESULT == "0")
ErrorMsg = "轉入正式資料失敗，請檢視異常報表！";
#endregion
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
}
catch (Exception ex)
{
#region 交易失敗
DBT.Rollback();
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
public void FileToTmpM(DataTable dtTemp, DbTransaction RootDBT, string V_CREATEUID, out string ErrorMsg)
{
bool IsRootTranscation = false;
try
{
DBO.VDS_STM37_DBO dbo = new VDS_STM37_DBO(ref USEDB);
ErrorMsg = "";
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
#region 清空暫存檔
dbo.doDeleteTemp(0, V_CREATEUID, DBT);
#endregion
#region FileToTmp
ArrayList ParameterList = new ArrayList();
if (dtTemp != null)
{
for (int i = 0; i < dtTemp.Rows.Count; i++)
{
DateTime dt;//檢查日期格式用的 不會真正使用此變數
ErrorMsg = "";
string PROMOTE_ID = dtTemp.Rows[i]["PROMOTE_ID"].ToString().Trim();//檔期編號
string PROMOTE_NAME = dtTemp.Rows[i]["PROMOTE_NAME"].ToString().Trim();//檔期名稱
string ACCT_MONTH = dtTemp.Rows[i]["ACCT_MONTH"].ToString().Trim();//結帳月份
string PROMOTE_TYPE = dtTemp.Rows[i]["PROMOTE_TYPE"].ToString().Trim();//檔期型態(1:供應商全系列2:主題書展)
string DISCOUNT_TYPE = dtTemp.Rows[i]["DISCOUNT_TYPE"].ToString().Trim();//出貨折扣(A:促銷折扣 B:原折扣)
string MEMO = dtTemp.Rows[i]["MEMO"].ToString().Trim();//備註
if (PROMOTE_ID == string.Empty)
{
ErrorMsg = "檔期編號為必填;";
}
else if (Encoding.Default.GetByteCount(PROMOTE_ID) > 20)
{
ErrorMsg += "檔期編號最多20個字元;";
PROMOTE_ID = "";
}
else if (!IsWord(PROMOTE_ID))
{
ErrorMsg += "檔期編號格式錯誤;";
}
if (PROMOTE_NAME == string.Empty)
{
ErrorMsg += "檔期名稱為必填;";
}
else if (Encoding.Default.GetByteCount(PROMOTE_NAME) > 50)
{
ErrorMsg += "檔期名稱最多50個字元;";
PROMOTE_NAME = "";
}
if (ACCT_MONTH == string.Empty)
{
ErrorMsg += "結帳月份欄位為必填;";
}
else if (ACCT_MONTH.Length != 6)
{
ErrorMsg += "結帳月份欄位必須為6個字元;";
ACCT_MONTH = "";
}
else if (DateTime.TryParseExact(ACCT_MONTH + "01", "yyyyMMdd", null, DateTimeStyles.None, out dt) == false)
{
ErrorMsg += "結帳月份格式錯誤;";
ACCT_MONTH = "";
}
if (PROMOTE_TYPE == string.Empty)
{
ErrorMsg += "檔期型態為必填;";
PROMOTE_TYPE = "";
}
else if (PROMOTE_TYPE.Length > 1)
{
ErrorMsg += "檔期型態長度太長;";
PROMOTE_TYPE = "";
}
else if (!IsNumber(PROMOTE_TYPE))
{
ErrorMsg += "檔期型態格式錯誤;";
PROMOTE_TYPE = "";
}
else if (PROMOTE_TYPE != "1" && PROMOTE_TYPE != "2")
{
ErrorMsg += "無此檔期型態;";
}
if (DISCOUNT_TYPE == string.Empty)
{
ErrorMsg += "出貨折扣為必填;";
}
else if (DISCOUNT_TYPE.Length > 1)
{
ErrorMsg += "出貨折扣長度太長;";
DISCOUNT_TYPE = "";
}
else if (!IsWord(DISCOUNT_TYPE))
{
ErrorMsg += "出貨折扣格式錯誤;";
DISCOUNT_TYPE = "";
}
else if (DISCOUNT_TYPE != "B")
{
ErrorMsg += "不可輸入B以外的出貨折扣;";
}
if (Encoding.Default.GetByteCount(MEMO) > 100)
{
ErrorMsg += "備註最多100個字元;";
MEMO = "";
}
if (ErrorMsg == "")
{
DataRow[] drDetlArrs = dtTemp.Select(string.Format("PROMOTE_ID ='{0}'",
dtTemp.Rows[i]["PROMOTE_ID"]));
if (drDetlArrs.Length > 1)
{
ErrorMsg += "檔期編號重複;";
}
}
ParameterList.Clear();
ParameterList.Add(dtTemp.Rows[i]["ROW_NUM"].ToString());//0
ParameterList.Add(PROMOTE_ID);//1
ParameterList.Add(PROMOTE_NAME);//2
ParameterList.Add(ACCT_MONTH);//3
ParameterList.Add(PROMOTE_TYPE);//4
ParameterList.Add(DISCOUNT_TYPE);//5
ParameterList.Add(ErrorMsg);//6
ParameterList.Add(V_CREATEUID);//7
if (ErrorMsg == "")
{
ParameterList.Add("1");//8
}
else
{
ParameterList.Add("2");
}
ParameterList.Add(MEMO);//9
dbo.doCreateTempM(ParameterList, DBT);
}
}
#endregion
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
IsRootTranscation = false;
}
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation == true)
DBT.Rollback();
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
#endregion
}
}