public bool CheckPurItemIsOnWay(string sPurNo, string sItemNo, string sPeriod)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
int iCount = dbo.CheckPurItemIsOnWay(sPurNo, sItemNo, sPeriod);
if (iCount == 0)
{ return false; }
else
{ return true; }
}
catch (Exception ex)
{
throw ex;
}
}
public bool CheckPurchaseIsACCEPT(string sPurNo)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
int iCount = dbo.CheckPurchaseIsACCEPT(sPurNo);
if (iCount == 0)
{ return false; }
else
{ return true; }
}
catch (Exception ex)
{
throw ex;
}
}
public string GetDetail_Data(ArrayList ParameterList)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.doGetDetailData(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable GetVendorDataByVendorNo(ArrayList ParameterList)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.GetVendorData(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
public int Check_DC_Date(ArrayList ParameterList)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.doCheck_DC_Date(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 刪除該使用者存在於 VDS_PUR_PURCHASEORDER_TMP 的舊資料，並將 EXCEL 中新資料匯入 VDS_PUR_PURCHASEORDER_TMP
/// </summary>
/// <param name="ParameterList">刪除變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳影響筆數</returns>
public void DELETE_AND_INSERT_TEMP_TABLE(ArrayList ParameterList, DbTransaction RootDBT, DataTable dt_ALO_IMPORT_TMP)
{
bool IsRootTranscation = false;
try
{
PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
dbo.doDelete_PURCHASEORDER_TMP(ParameterList, DBT);
foreach (DataRow dRow in dt_ALO_IMPORT_TMP.Rows)
{
dbo.doAdd_PURCHASEORDER_TMP(dRow, DBT);
}
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
//當資料寫入TMP檔後,做最後檢查
//1.檢查匯入的贈品有無父層品項
//2.檢查有無重複的品項/期別/虛擬品號
//3.檢查主品項與贈品的交貨日期必需一樣，才能匯入
dbo.doCheckOtherTmp(ParameterList, null);
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
public DataTable GetTempErrorDataByfind(ArrayList ParameterList)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.GetTempErrorDataByFind1(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 基本表單新增BCO
/// </summary>
/// <param name="ParameterList">輸入變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳新增之ID</returns>
public string CreateMasterAndDetail_Gift(ArrayList ParameterList,
DataTable DetailData,
DataTable GiftData,
DbTransaction RootDBT)
{
#region
bool IsRootTranscation = false;
Int32 PID = 0;
string[] sID_NO;
string sTemp = "";
string sPurchaseNo = "";
try
{
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
//寫入 Master Table
sTemp = CreatePurchaseOrder(ParameterList, DBT);
sID_NO = sTemp.Split(new Char[] { ';' });
PID = int.Parse(sID_NO[0].ToString());
sPurchaseNo = sID_NO[1].ToString();
//將ParameterList內存放主檔ID與PURCHASRE_NO欄位值，換成PID與sPurchaseNo
ParameterList[5] = sPurchaseNo;
ParameterList[6] = PID;
if (PID != 0)
{
#region 新增明細資料與更新 VDS_MKT_PURCARD_MAIN.PUR_NO(雜誌採購卡) 或 VDS_MKT_BPURCARD_MAIN.PUR_NO(圖書採購卡)
foreach (DataRow dRow in DetailData.Rows)
{
CreatePurchaseOrderDetail(PID, ParameterList, dRow, DBT);
//更新期別屬性檔(VDS_ITM_PERIOD_PROFILE)中,該品號期別的交貨日期、採購日、採購量與採購單號
UpdatePeriodProfile(dRow["ITEM"].ToString().Trim(), dRow["PERIOD"].ToString().Trim(), dRow["PLAN_ACCEPT_DATE"].ToString().Trim(), ParameterList[0].ToString().Trim(),
dRow["PURCHASE_QTY"].ToString().Trim(), sPurchaseNo, ParameterList[4].ToString().Trim(), DBT);
//更新圖書採購卡或雜誌採購卡採購單號
if (dRow["PERIOD"].ToString().Trim() == "000000") //在圖書裡其period值絕對是000000
{
Update_B_PURCARD(sPurchaseNo, dRow["ITEM"].ToString().Trim(), ParameterList[4].ToString().Trim(), DBT);
}
else
{
Update_PURCARD(sPurchaseNo, dRow["ITEM"].ToString().Trim(), dRow["PERIOD"].ToString().Trim(), ParameterList[4].ToString().Trim(), DBT);
}
}
#endregion
#region 新增明細贈品資料
if (GiftData != null)
{
foreach (DataRow gRow in GiftData.Rows)
{
CreatePurchaseOrderDetail(PID, ParameterList, gRow, DBT);
//更新贈品屬性檔(VDS_MKT_GIFT_PROFILE)資料中,該品號期別的交貨日期、採購日、採購量與採購單號
UpdateGiftProfile(gRow["ITEM"].ToString().Trim(), gRow["PERIOD"].ToString().Trim(), gRow["VIRTUAL_CODE"].ToString().Trim(),
gRow["PLAN_ACCEPT_DATE"].ToString().Trim(), ParameterList[0].ToString().Trim(),
gRow["PURCHASE_QTY"].ToString().Trim(), sPurchaseNo, ParameterList[4].ToString().Trim(), DBT);
}
}
#endregion
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
sPurchaseNo = dbo.doQueryPurchase_NotByID(PID.ToString());
}
else
{
throw new Exception("新增0筆資料!");
}
return sPurchaseNo;
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
#endregion
}
/// <summary>
/// 將資料由 TEMP TABLE 新增至正式 TABLE
/// </summary>
/// <param name="ParameterList">變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳結果</returns>
public int TMP_To_Purchase_Order(ArrayList ParameterList, out string strPURCHASE_NO, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
int iResult = 0;
try
{
PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
//將Temple data 寫入 Master 與 Deail Table
iResult = dbo.doTempToPUR_Order(ParameterList, out strPURCHASE_NO, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return iResult;
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
/// <summary>
/// 刪除基本表單時，並同時對Detail資料做刪除動作
/// </summary>
/// <param name="sPurNo">採購單號</param>
/// <param name="UpdateID">刪除人員ID</param>
/// <returns>回傳影響筆數</returns>
public void DeteleMain_DetailData(string sPurNo, string UpdateID, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
try
{
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
dbo.doDeteleMain_DetailData(sPurNo, UpdateID, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
public string GetOrderDocumentIDAndCode()
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.GetOrderDocumentIDAndCode();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新贈品屬性檔(VDS_MKT_GIFT_PROFILE)資料中,該品號期別的交貨日期、採購日、採購量與採購單號
/// </summary>
/// <param name="sItem">品號</param>
/// <param name="sPeriod">期別</param>
/// <param name="sVCode">虛擬品號</param>
/// <param name="sPA_Date">交貨日期</param>
/// <param name="sPurDate">採購日期</param>
/// <param name="sQty">採購數量</param>
/// <param name="sPurchaseNo">採購單號</param>
/// <param name="sUpdater">更新人員帳號</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳成功與否</returns>
public bool UpdateGiftProfile(string sItem, string sPeriod, string sVCode, string sPA_Date, string sPurDate, string sQty, string sPurchaseNo, string sUpdater, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
bool bResult = false;
try
{
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
dbo.doUpdateGiftProfile(sItem, sPeriod, sVCode, sPA_Date, sPurDate, sQty, sPurchaseNo, sUpdater, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
bResult = true;
return bResult;
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
public DataTable QueryGiftSchema()
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.doQueryGiftSchema();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 對Detail table 做結案動作
/// </summary>
/// <param name="ParMasterList">更新變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳影響筆數</returns>
public void ClosePurchaseOrderDetail(ArrayList ParDetailList,
DataRow OldDetailRow,
DbTransaction RootDBT
)
{
bool IsRootTranscation = false;
try
{
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
dbo.doClosePurchaseOrderDetail(ParDetailList, OldDetailRow, DBT);
//如果明細都結案了則把主檔結案
dbo.doClosePurchaseOrderDetailM(ParDetailList, OldDetailRow, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
/// <summary>
/// 對Master table 做結案動作
/// </summary>
/// <param name="ParMasterList">更新變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳影響筆數</returns>
public void ClosePurchaseOrder(ArrayList ParMasterList,
DataRow OldMasterRow,
DbTransaction RootDBT
)
{
bool IsRootTranscation = false;
try
{
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
dbo.doClosePurchaseOrder(ParMasterList, OldMasterRow, DBT);
ArrayList Para = new ArrayList();
Para.Clear();
Para.Add(OldMasterRow["PURCHASE_NO"].ToString().Trim());
Para.Add("M");
Para.Add(ParMasterList[0].ToString());
dbo.doCloseCaseDetailByPurNo(Para, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
/// <summary>
/// 取Temp table schema
/// </summary>
/// <returns>回傳table schema</returns>
public DataTable GetTempTableSchema()
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
DataTable Dt;
try
{
Dt = dbo.doQueryTempTableSchema();
return Dt;
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable QueryPurchaseOrderByFind_1(ArrayList ParameterList)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.doQueryByFind_1(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 新增明細檔BCO
/// </summary>
/// <param name="DetailData">輸入變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
/// <returns>回傳異動筆數</returns>
public int CreatePurchaseOrderDetail(Int32 PID, ArrayList ParameterList, DataRow dRow, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
Int32 iResult = 0;
try
{
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{ DBT = RootDBT; }
#endregion
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
iResult = dbo.doCreateDetail(PID, DBT, dRow, ParameterList);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return iResult;
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
public DataTable QueryPrintPurchaseOrderDetail(ArrayList ParameterList)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.PrintPurchaseOrderDetail(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
public DataTable QuerySwitch(QueryType QT,
ArrayList ParameterList
)
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
DataTable Dt;
try
{
switch (QT)
{
//case QueryType.ALL:
// Dt = dbo.doQueryAll();
// break;
//case QueryType.CODE:
// Dt = dbo.doQueryByCode(ParameterList);
// break;
case QueryType.PK:
Dt = dbo.GetMasterByPK(ParameterList);
break;
case QueryType.Where1:
Dt = dbo.GetMainByWhere1(ParameterList);
break;
default:
Dt = new DataTable();
break;
}
return Dt;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 測試SP交易失敗
/// </summary>
/// <param name="ParameterList">變數</param>
/// <param name="RootDBT">是否有主交易,無主交易輸入null</param>
public void TestTranscation(ArrayList ParameterList1,
ArrayList ParameterList2,
ArrayList ParameterList3,
DbTransaction RootDBT
)
{
bool IsRootTranscation = false;
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
dbo.doCreateMaster(ParameterList1, DBT);
//dbo.doUpdate(ParameterList2, DBT);
dbo.doDelete(ParameterList3, DBT);
throw new Exception("交易失敗");
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
/// <summary>
///1.目的:將暫存檔中的待處理資料，匯入正式表單
///2.傳入參數：@處理檔名、@使用者
/// 回傳參數：@處理筆數、@正確筆數、@錯誤筆數、@錯誤訊息
///3.處理表格：VDS_PUR_PURCHASEORDER_TMP(T)、VDS_PUR_PURCHASE_MAIN(M)、VDS_PUR_PURCHASE_ITEM(D)
///4.將暫存檔中的[狀態]欄位調整為2:TMP TO DB
///5.匯入處理完成後，回傳匯入處理結果:處理總筆數、成功筆數、失敗筆數、@錯誤訊息(成功時=null)
/// </summary>
public ArrayList TmpToDB(string s_LoginUser,string MAIL_ADDR, string ConnectionDB)
{
#region
ArrayList arl_Return = new ArrayList();
try
{
string strPURCHASE_NO = string.Empty;
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
ParameterList.Add(MAIL_ADDR);
int iResult = TMP_To_Purchase_Order(ParameterList, out strPURCHASE_NO, null);
PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
ParameterList.Clear();
ParameterList.Add(strPURCHASE_NO);
DataTable dtPur = dbo.doQueryPurData(ParameterList); ;
MaintainPurchaseOrder po = new MaintainPurchaseOrder(ConnectionDB);
#region 更新期別屬性檔&圖書採購卡或雜誌採購卡採購單號
DataRow[] DetailRows = dtPur.Select("ITEM=VIRTUAL_CODE");
foreach (DataRow dRow in DetailRows)
{
//更新期別屬性檔(VDS_ITM_PERIOD_PROFILE)中,該品號期別的交貨日期、採購日、採購量與採購單號
po.UpdatePeriodProfile(dRow["ITEM"].ToString().Trim(),
dRow["PERIOD"].ToString().Trim(),
dRow["PLAN_ACCEPT_DATE"].ToString().Trim(),
dRow["PURCHSE_DATE"].ToString().Trim(),
dRow["PURCHASE_QTY"].ToString().Trim(),
dRow["PURCHASE_NO"].ToString().Trim(),
s_LoginUser,
null);
//更新圖書採購卡或雜誌採購卡採購單號
if (dRow["PERIOD"].ToString().Trim() == "000000") //在圖書裡其period值絕對是000000
{
po.Update_B_PURCARD(dRow["PURCHASE_NO"].ToString().Trim(),
dRow["ITEM"].ToString().Trim(),
s_LoginUser,
null);
}
else
{
po.Update_PURCARD(dRow["PURCHASE_NO"].ToString().Trim(),
dRow["ITEM"].ToString().Trim(),
dRow["PERIOD"].ToString().Trim(),
s_LoginUser,
null);
}
}
#endregion
#region 更新贈品屬性檔
DataRow[] GiftRows = dtPur.Select("ITEM<>VIRTUAL_CODE");
foreach (DataRow gRow in GiftRows)
{
//更新贈品屬性檔(VDS_MKT_GIFT_PROFILE)資料中,該品號期別的交貨日期、採購日、採購量與採購單號
po.UpdateGiftProfile(gRow["ITEM"].ToString().Trim(),
gRow["PERIOD"].ToString().Trim(),
gRow["VIRTUAL_CODE"].ToString().Trim(),
gRow["PLAN_ACCEPT_DATE"].ToString().Trim(),
gRow["PURCHSE_DATE"].ToString().Trim(),
gRow["PURCHASE_QTY"].ToString().Trim(),
gRow["PURCHASE_NO"].ToString().Trim(),
s_LoginUser,
null);
}
#endregion
if (iResult > 0)
{
arl_Return.Add("TEMPTODB 正確");
arl_Return.Add(iResult);
arl_Return.Add(iResult);
arl_Return.Add(0);
}
}
catch (Exception ex)
{
arl_Return.Add("程式錯誤");
arl_Return.Add(ex.Message);
}
finally
{ }
return arl_Return;
#endregion
}
public DataTable GetItemGiftByPurchase_No(ArrayList ParameterList)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.GetItemGiftByPUR_No(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
public int Get_AcceptQty(ArrayList ParameterList)
{
try
{
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
return dbo.doGet_AcceptQty(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
//修改採購主檔狀態為結案
public bool UpdatePUR_MasterClose(ArrayList ParameterList, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
bool bResult = false;
try
{
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
dbo.doUpdateMasterClose(ParameterList, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
bResult = true;
return bResult;
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
finally
{
#region 判斷是否關閉交易連線
if (IsRootTranscation)
{
//獨立呼叫Transcation,關閉連線
if (Conn.State == ConnectionState.Connecting)
{
Conn.Close();
}
}
#endregion
}
}
/// <summary>
///1.目的:將上傳檔案內容，存入暫存檔
///2.傳入參數：@處理檔名、@使用者
/// 回傳參數：@處理筆數、@正確筆數、@錯誤筆數、@錯誤訊息
///3.處理表格：VDS_ALO_STORESPEC_TMP(T)
///4.處理邏輯：
/// 4.1) 先清除前一次，同一使用者執行的匯入暫存資訊與錯誤資訊。
/// 相關Table:VDS_ALO_STORESPEC_TM
/// 4.2) 讀取匯入檔案到暫存資料集。
/// 4.3) 檢查匯入檔案格式內容是否正確，如有任何錯誤，則停止上傳流程，並回傳錯誤訊息。
/// 『XX檔案，檔案內容錯誤：@錯誤說明』 (格式請參考資料庫Table Shcema)
/// * 日期：檢查日期格式。
/// * 數字：檢查內容是否為數字。
/// * 文字：檢查長度是否溢位。
/// * 檔案欄位數是否不足。
/// * 檢查必填欄位是否有值：通路、店號、品號、數量、原因代碼、設定類別
/// * 工作表匯入內容有任一筆錯，皆視為錯誤，整批放棄。
/// * 匯入表格細節說明請參考：Excel文件
/// 4.4) 格式檢查正確無誤後
/// a) 暫存檔中的[狀態]欄位調整為1:FILE TO TMP
/// b) 呼叫 [CheckData] ，檢查資料內容邏輯正確性
/// 4.5) 無論CheckData()檢查結果是否為True，
/// 皆將上傳檔案內容新增到資料庫Tmp Table:，
/// 並記錄錯誤原因、匯入者、匯入日期、處理檔案名稱=上傳檔名。
///5. 回傳處理結果(@處理筆數、@正確筆數、@錯誤筆數、@錯誤訊息(無錯誤時=null) )
/// </summary>
public ArrayList FileToTmp(DataSet ds_Excel, DateTime d_CreateDate, string s_LoginUser, string s_VendorID, string s_DBConnString, string s_AP_FileName)
//public ArrayList FileToTmp(DataSet ds_Excel, DateTime d_CreateDate)
{
#region
int i_newTable_Count = 0;
ArrayList arl_Return = new ArrayList();
int i_FileToTemp_Total_count = 0;
int ChkCount = 0;
string strErrMsg = "";
Int32 iErrCount = 0;
Double doubleVal;
ArrayList pList = new ArrayList();
try
{
ParameterList.Clear();
DataTable dt_PUR_IMPORT_TMP = GetTempTableSchema();
PUR_PurchaseOrderDBO PUR_POSOURCE_NO_TMP = new PUR_PurchaseOrderDBO(ref USEDB);
DBO.PUR_PurchaseOrderDBO dbo = new PUR_PurchaseOrderDBO(ref USEDB);
foreach (System.Data.DataRow dRow in ds_Excel.Tables[0].Rows)
{
iErrCount = 0;
strErrMsg = "";
//檢查資料邏輯正確性
//將資料寫入 TEMP TABLE
DataRow dRow_Temp = dt_PUR_IMPORT_TMP.NewRow();
dRow_Temp["CREATEDATE"] = DateTime.Parse(d_CreateDate.ToString("yyyy/MM/dd"));
dRow_Temp["CREATEUID"] = s_LoginUser;
dRow_Temp["ID"] = Convert.ToInt64(d_CreateDate.ToString("yyyyMMddHHmmss") + (i_newTable_Count + 1).ToString());
dRow_Temp["UPDATEDATE"] = DateTime.Parse(d_CreateDate.ToString("yyyy/MM/dd"));
dRow_Temp["UPDATEUID"] = s_LoginUser;
dRow_Temp["VENDOR"] = s_VendorID;
dRow_Temp["DATASOURCE"] = s_AP_FileName;
dRow_Temp["STATUS"] = 1;//File To TMP
string sItem = dRow[1].ToString().Trim(); //高見品號
string sGift = dRow[2].ToString().Trim(); //贈品品號
string sPeriod = dRow[3].ToString().Trim(); //期別
string sItemName = dRow[4].ToString().Trim(); //品名
string sQty = dRow[5].ToString().Trim(); //採購數量
string sDate = dRow[6].ToString().Trim(); //交貨日期
string strMsg = dRow[7].ToString().Trim(); //商品訊息
if ((sItem != "") || (sPeriod != "") || (sItemName != "") || (sQty != "") || (sDate != "") || (strMsg != ""))
{
// 驗證新增資料欄位是否為必填。
//品號
if (sItem == string.Empty)
{
strErrMsg += "品號欄位為必填,";
}
else if (CheckLength(sItem, 6, "品號", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//過長
sItem = SubStr(sItem, 0, 6);
}
else
{
pList.Clear();
pList.Add(sItem);
DataTable dt = dbo.doQueryItem(pList);
if (dt.Rows.Count > 0)
{
if (s_VendorID.Trim() != dt.Rows[0]["MANUFACTURE"].ToString().Trim())
{
strErrMsg += "該品號不屬於此供應商,";
}
else
{
string strNowDate = DateTime.Now.ToString("yyyy/MM/dd");
if (string.Compare(dt.Rows[0]["mdc_end_date"].ToString(), strNowDate) < 0)
{
strErrMsg += "該品號已過期,";
}
}
}
else
{
strErrMsg += "該品號不存在商品主檔內,";
}
}
dRow_Temp["ITEM"] = sItem;
//贈品
if (sGift == string.Empty)
{
strErrMsg += "贈品品號為必填,";
}
else if (CheckLength(sGift, 10, "贈品品號", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//過長
sGift = SubStr(sGift, 0, 10);
}
else if (sGift.Length < sItem.Length)
{
strErrMsg += "贈品品號前六碼需與高見品號一樣,";
}
else
{
if (sGift.Length == sItem.Length)
{
if (sGift != sItem)
{
strErrMsg += "贈品品號前六碼需與高見品號一樣,";
}
}
else
{
if (sGift.Substring(0, 6) != sItem)
{
strErrMsg += "贈品品號前六碼需與高見品號一樣,";
}
else
{
PIC.VDS2G.BSM.MKT.MKT_GiftDBO dboGift = new PIC.VDS2G.BSM.MKT.MKT_GiftDBO(ref USEDB);
pList.Clear();
pList.Add(sItem);
pList.Add(sPeriod);
pList.Add(-1);
DataTable dtGift = dboGift.doQueryGift(pList);
if (dtGift.Select("ITEM='" + sItem + "' And PERIOD='" + sPeriod + "' And VIRTUAL_CODE='" + sGift + "'").Length == 0)
{
strErrMsg += "找不到對應的贈品品號,";
}
}
}
}
dRow_Temp["VIRTUAL_CODE"] = sGift;
//期別
ChkCount = 0;
if (sPeriod == string.Empty)
{
strErrMsg += "期別欄位為必填,";
}
else if (CheckLength(sPeriod, 6, "期別", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//過長
sPeriod = SubStr(sPeriod, 0, 6);
}
else
{
pList.Clear();
pList.Add(sItem);
pList.Add(sPeriod);
ChkCount = dbo.doCheck_Exist_Period(pList);
pList.Clear();
if (ChkCount == 0)
{
strErrMsg += "期別不存在期別主檔內,";
}
}
dRow_Temp["PERIOD"] = sPeriod;
//檢查品名
if (CheckLength(sItemName, 50, "品名", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//過長
sItemName = SubStr(sItemName, 0, 50);
}
dRow_Temp["ITEM_NAME"] = sItemName;
//採購數
if (sQty == string.Empty)
{
strErrMsg += "採購數欄位為必填,";
dRow_Temp["PURCHASE_QTY"] = DBNull.Value;
}
else if (double.TryParse(sQty, out doubleVal) == false)
{
strErrMsg += "採購數欄位必須為數值型態,";
dRow_Temp["PURCHASE_QTY"] = DBNull.Value;
}
else if (CheckLength(sQty, 6, "採購數", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//過長
sQty = SubStr(sQty, 0, 6);
dRow_Temp["PURCHASE_QTY"] = sQty;
}
else if (int.Parse(sQty) <= 0)
{
if (sGift != sItem && (int.Parse(sQty) == 0))
{
//贈品可以輸入零
}
else
{
strErrMsg += "採購數欄位必須大於0,";
}
dRow_Temp["PURCHASE_QTY"] = sQty;
}
else
{
dRow_Temp["PURCHASE_QTY"] = sQty;
}
//交貨日
if (sDate == string.Empty || sDate == "")
{
strErrMsg += "交貨日欄位為必填,";
dRow_Temp["PLAN_ACCEPT_DATE"] = DBNull.Value;
}
else if (double.TryParse(sDate, out doubleVal) == false)//(DateTime.TryParse(dRow[5].ToString().Trim(), out dateVal) == false)
{ //因日期格式設定為yyyyMMdd，故由此可知皆是數值，若輸入格式為yyyy-MM-dd or YYYY/MM/DD等，則代表格式輸入錯誤；
//或者輸入非日期值時，則代表型輸入錯。
//在這裡不使用 DateTime.TryParse 原因是，user的格式設定為yyyyMMdd，而TryParse對此格式會斷讀成False
strErrMsg += "交貨日欄位必須為日期型態或日期格式錯誤,";
dRow_Temp["PLAN_ACCEPT_DATE"] = DBNull.Value;
}
else
{
string sDateTmp = sDate.Insert(6, "/");
sDateTmp = sDateTmp.Insert(4, "/");
//驗證日期格式 YYYYMMDD, 範圍00010101~99991231
Match m = Regex.Match(sDate, "(^0000|0001|9999|[0-9]{4})+(0[1-9]|1[0-2])+(0[1-9]|[12][0-9]|3[01])$");
if (sDate.Substring(4, 2) == "00" || sDate.Substring(6, 2) == "00")
{
strErrMsg += "交貨日欄位資料有誤,";
dRow_Temp["PLAN_ACCEPT_DATE"] = DBNull.Value;
}
else if (m.Success == false)
{
strErrMsg += "交貨日欄位格式不符,";
dRow_Temp["PLAN_ACCEPT_DATE"] = DBNull.Value;
}
else if (DateTime.Parse(sDateTmp) < DateTime.Today)
{
strErrMsg += "交貨日欄位必須大於等於系統日期,";
dRow_Temp["PLAN_ACCEPT_DATE"] = sDate;
}
else
{
dRow_Temp["PLAN_ACCEPT_DATE"] = sDate;
}
}
//商品訊息
if (CheckLength(strMsg, 60, "商品訊息", strErrMsg, iErrCount, out strErrMsg, out iErrCount) == false)
{
//過長
strMsg = SubStr(strMsg, 0, 60);
}
dRow_Temp["MSG"] = strMsg;
if (strErrMsg.Trim() == string.Empty)
{
dRow_Temp["ERROR_MEMO"] = System.DBNull.Value;
}
else
{
dRow_Temp["ERROR_MEMO"] = strErrMsg;
iErrCount++;
}
if (iErrCount != 0)
{
dRow_Temp["DATATYPE"] = "2"; //錯誤資料
}
else
{
dRow_Temp["DATATYPE"] = "1"; //正常資料
}
dt_PUR_IMPORT_TMP.Rows.Add(dRow_Temp);
i_newTable_Count += 1;
i_FileToTemp_Total_count += 1;
}
}
#region 先清除前一次，同一使用者執行的匯入暫存資訊與錯誤資訊，並將 Excel 資料匯入 TEMP TABLE
ParameterList.Clear();
ParameterList.Add(s_LoginUser);
DELETE_AND_INSERT_TEMP_TABLE(ParameterList, null, dt_PUR_IMPORT_TMP);
#endregion
int i_FileToTemp_Right_count = 0;
int i_FileToTemp_Wrong_count = 0;
DataTable dtCount = dbo.doQueryTmpCount(ParameterList);
for (int i = 0; i < dtCount.Rows.Count; i++)
{
if (dtCount.Rows[i]["DATATYPE"].ToString() == "1")
i_FileToTemp_Right_count = int.Parse(dtCount.Rows[i]["COUNTS"].ToString());
if (dtCount.Rows[i]["DATATYPE"].ToString() == "2")
i_FileToTemp_Wrong_count = int.Parse(dtCount.Rows[i]["COUNTS"].ToString());
}
if (i_FileToTemp_Wrong_count > 0)
{
arl_Return.Add("FALSE");
}
else
{
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);
}
return arl_Return;
#endregion
}
