/// <summary>
/// 新增在途單主檔(VDS_PUR_ONWAY_MAIN)及明細檔(VDS_PUR_ONWAY_ITEM)資料
/// * 處理原則：
/// 1.在途單新增時，需call CheckVendorAccountClose.CheckVendorCloseStatus(在途日期) ,
/// 檢查在途日期是否已進行供應商帳務日結，若已日結，則需顯示訊息"在途日XXX已日結，不可新增在途單"。
///
/// 2.在途單號第一碼於代號控制檔取得，'E'+YMMDD+XXXX
///
/// 3.若在途商品為正常品,Call this.UpdatePeriodInTransitData(品號,期別,
/// 在途量)更新期別屬性檔(VDS_PERIOD_PROFILE)的下列欄位:在途量(Onway_Qty)
///
/// 4.若採購商品為贈品,則Call this.UpdateGiftInTransitData(品號,期別,虛擬品號,
/// 在途量)更新贈品屬性檔(VDS_MKT_GIFT_PERIOD)的在途量資料
///
/// 5.新增時，預設ONWAY_QTY(在途數量) = PETITION_QTY(申請數量)
/// 6.若新增不成功則全部rollback
/// </summary>
public bool CreateGoodsInTransit(ArrayList ParameterList, out string ONWAY_NO, DbTransaction RootDBT)
{
bool IsRootTranscation = false;
int iResult = 0;
bool bResult = false;
bool bAllClose = true;
string strUpdateQty = "0";
try
{
PUR_05_DBO DBO = new PUR_05_DBO(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
//0.Item DataTable
//1.Gift DataTable
//2.VENDOR
//3.PURCHASE_NO
//4.ONWAY_DATE
//5.CREATEDATE
//6.CREATEUID
//7.UPDATEDATE
//8.UPDATEUID
//9.STATUS
//out ONWAY_NO
ArrayList Para = new ArrayList();
DataTable dtItem = (DataTable)ParameterList[0];
DataTable dtGift = (DataTable)ParameterList[1];
string strVENDOR = ParameterList[2].ToString();
string strPURCHASE_NO = ParameterList[3].ToString();
string strONWAY_DATE = ParameterList[4].ToString().Substring(0, 10);
string strCREATEDATE = ParameterList[5].ToString();
string strCREATEUID = ParameterList[6].ToString();
string strUPDATEDATE = ParameterList[7].ToString();
string strUPDATEUID = ParameterList[8].ToString();
string strSTATUS = ParameterList[9].ToString();
//主檔儲存
Para.Clear();
Para.Add(strVENDOR); //0
Para.Add(strPURCHASE_NO); //1
Para.Add(strONWAY_DATE); //2
Para.Add(strCREATEDATE); //3
Para.Add(strCREATEUID); //4
Para.Add(strUPDATEDATE); //5
Para.Add(strUPDATEUID); //6
Para.Add(strSTATUS); //7
string strID = string.Empty;
iResult = DBO.CreateMain(DBT, Para, out ONWAY_NO, out strID);
//0.PID
//1.ONWAY_NO
//2.ITEM
//3.PERIOD
//4.VIRTUAL_CODE
//5.PETITION_QTY
//6.ONWAY_QTY
//7.CREATEDATE
//8.CREATEUID
//9.UPDATEDATE
//10.UPDATEUID
//11.CLOSE_DATE
//12.CLOSE_TYPE
//一般Item儲存
foreach (DataRow dr in dtItem.Rows)
{
if (dr["ENABLE"].ToString() == "1")
{
Para.Clear();
Para.Add(strID); //0
Para.Add(ONWAY_NO); //1
Para.Add(dr["ITEM"].ToString()); //2
Para.Add(dr["PERIOD"].ToString()); //3
Para.Add(dr["VIRTUAL_CODE"].ToString());//4
Para.Add(dr["PETITION_QTY"].ToString());//5
Para.Add(dr["ONWAY_QTY"].ToString()); //6
Para.Add(strCREATEDATE); //7
Para.Add(strCREATEUID); //8
Para.Add(strUPDATEDATE); //9
Para.Add(strUPDATEUID); //10
Para.Add(dr["CLOSE_DATE"].ToString()); //11
Para.Add(GetStatus(dr["CLOSE_TYPE"].ToString().Trim())); //12
if (dr["CLOSE_TYPE"].ToString().Trim() == "")
bAllClose = false;
iResult = DBO.CreateItem(DBT, Para);
if (GetStatus(dr["CLOSE_TYPE"].ToString().Trim()) == "")
strUpdateQty = dr["ONWAY_QTY"].ToString();
else
strUpdateQty = "0";
UpdatePeriodInTransitData(DBO,
DBT,
dr["ITEM"].ToString(),
dr["PERIOD"].ToString(),
strUpdateQty,
strUPDATEDATE,
strUPDATEUID);
}
}
//贈品儲存
foreach (DataRow dr in dtGift.Rows)
{
if (dr["ENABLE"].ToString() == "1")
{
Para.Clear();
Para.Add(strID); //0
Para.Add(ONWAY_NO); //1
Para.Add(dr["ITEM"].ToString()); //2
Para.Add(dr["PERIOD"].ToString()); //3
Para.Add(dr["VIRTUAL_CODE"].ToString());//4
Para.Add(dr["PETITION_QTY"].ToString());//5
Para.Add(dr["ONWAY_QTY"].ToString()); //6
Para.Add(strCREATEDATE); //7
Para.Add(strCREATEUID); //8
Para.Add(strUPDATEDATE); //9
Para.Add(strUPDATEUID); //10
Para.Add(dr["CLOSE_DATE"].ToString()); //11
Para.Add(GetStatus(dr["CLOSE_TYPE"].ToString().Trim())); //12
if (dr["CLOSE_TYPE"].ToString().Trim() == "")
bAllClose = false;
iResult = DBO.CreateItem(DBT, Para);
if (GetStatus(dr["CLOSE_TYPE"].ToString().Trim()) == "")
strUpdateQty = dr["ONWAY_QTY"].ToString();
else
strUpdateQty = "0";
UpdateGiftInTransitData(DBO,
DBT,
dr["ITEM"].ToString(),
dr["PERIOD"].ToString(),
dr["VIRTUAL_CODE"].ToString(),
strUpdateQty,
strUPDATEDATE,
strUPDATEUID);
}
}
//假如細項都結案的話 主檔也要結案
if (bAllClose == true)
{
//0.V_ONWAY_NO
//1.V_STATUS
//2.V_ONWAY_DATE
//3.V_UPDATEUID
//4.V_UPDATEDATE
//5.V_OLD_UPDATEUID
//6.V_OLD_UPDATEDATE
//7.V_ENABLE
Para.Clear();
Para.Add(ONWAY_NO); //0
Para.Add("75"); //1
Para.Add(strONWAY_DATE); //2
Para.Add(strUPDATEUID); //3
Para.Add(strUPDATEDATE); //4
Para.Add(strUPDATEUID); //5
Para.Add(strUPDATEDATE); //6
Para.Add("1"); //7
iResult = DBO.UpdateMain(DBT, Para);
}
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
bResult = true;
}
#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
}
return bResult;
}
/// <summary>
/// 1:目的:在途單結案
/// 2. 傳入參數：[@在途單號],[@結案方式]
///
/// 3:table:VDS_PUR_ONWAY_MAIN,VDS_PUR_ONWAY_ITEM
///
/// 4:處理方式
/// ●.當人工於畫面進行結案時，將在途單的狀態改為"結案"狀態(VDS_PUR_ONWAY_MAIN.status='75'),
/// 同時將明細資料(VDS_PUR_ONWAY_ITEM)的所有狀態(close_type)改為"手動"('M')。
///
/// 5.若在途商品為正常品,Call MaintainPeriodProfile.UpdatePeriodInTransitData(品號,期別,
/// 在途量=0)更新期別屬性檔(VDS_ITM_PERIOD_PROFILE)的下列欄位:在途量(Onway_Qty)
///
/// 6.若採購商品為贈品,則Call MaintainGiftProfile.UpdateGiftInTransitData(品號,期別,虛擬品號,
/// 在途量=0)更新贈品屬性檔(VDS_MKT_GIFT_PERIOD)的在途量資料
/// </summary>
public bool CloseGoodsInTransit(ArrayList ParameterList)
{
bool IsRootTranscation = false;
DbTransaction RootDBT = null;
int iResult = 0;
bool bResult = false;
try
{
PUR_05_DBO DBO = new PUR_05_DBO(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
//0.Item DataTable
//1.Gift DataTable
//2.ONWAY_NO
//3.ONWAY_DATE
//4.UPDATEDATE
//5.UPDATEUID
//6.STATUS
//7.OLD_UPDATEDATE
//8.OLD_UPDATEUID
ArrayList Para = new ArrayList();
DataTable dtItem = (DataTable)ParameterList[0];
DataTable dtGift = (DataTable)ParameterList[1];
string strONWAY_NO = ParameterList[2].ToString();
string strONWAY_DATE = ParameterList[3].ToString().Substring(0, 10);
string strUPDATEDATE = ParameterList[4].ToString();
string strUPDATEUID = ParameterList[5].ToString();
string strSTATUS = ParameterList[6].ToString();
string strOLD_UPDATEDATE = ParameterList[7].ToString();
string strOLD_UPDATEUID = ParameterList[8].ToString();
//主檔更新
//0.V_ONWAY_NO
//1.V_STATUS
//2.V_ONWAY_DATE
//3.V_UPDATEUID
//4.V_UPDATEDATE
//5.V_OLD_UPDATEUID
//6.V_OLD_UPDATEDATE
//7.V_ENABLE
Para.Clear();
Para.Add(strONWAY_NO); //0
Para.Add(strSTATUS); //1
Para.Add(strONWAY_DATE); //2
Para.Add(strUPDATEUID); //3
Para.Add(strUPDATEDATE); //4
Para.Add(strOLD_UPDATEUID); //5
Para.Add(strOLD_UPDATEDATE); //6
Para.Add("1"); //7
string strID = string.Empty;
iResult = DBO.UpdateMain(DBT, Para);
//一般Item結案
foreach (DataRow dr in dtItem.Rows)
{
if (dr["CLOSE_TYPE"].ToString() == "")
{
//0.ONWAY_NO
//1.ITEM
//2.PERIOD
//3.VIRTUAL_CODE
//4.PETITION_QTY
//5.ONWAY_QTY
//6.CLOSE_DATE
//7.CLOSE_TYPE
//8.UPDATEUID
//9.UPDATEDATE
//10.OLD_UPDATEUID
//11.OLD_UPDATEDATE
//Update
Para.Clear();
Para.Add(strONWAY_NO); //0
Para.Add(dr["ITEM"].ToString()); //1
Para.Add(dr["PERIOD"].ToString()); //2
Para.Add(dr["VIRTUAL_CODE"].ToString());//3
Para.Add(dr["PETITION_QTY"].ToString());//4
Para.Add(dr["ONWAY_QTY"].ToString()); //5
Para.Add(strUPDATEDATE.Substring(0, 10)); //6
Para.Add("M"); //7
Para.Add(strUPDATEUID); //8
Para.Add(strUPDATEDATE); //9
Para.Add(dr["UPDATEUID"].ToString()); //10
Para.Add(dr["UPDATEDATE"].ToString()); //11
iResult = DBO.UpdateItem(DBT, Para);
UpdatePeriodInTransitData(DBO,
DBT,
dr["ITEM"].ToString(),
dr["PERIOD"].ToString(),
"0",
strUPDATEDATE,
strUPDATEUID);
}
}
//贈品結案
foreach (DataRow dr in dtGift.Rows)
{
if (dr["CLOSE_TYPE"].ToString() == "")
{
//0.ONWAY_NO
//1.ITEM
//2.PERIOD
//3.VIRTUAL_CODE
//4.PETITION_QTY
//5.ONWAY_QTY
//6.CLOSE_DATE
//7.CLOSE_TYPE
//8.UPDATEUID
//9.UPDATEDATE
//10.OLD_UPDATEUID
//11.OLD_UPDATEDATE
//Update
Para.Clear();
Para.Add(strONWAY_NO); //0
Para.Add(dr["ITEM"].ToString()); //1
Para.Add(dr["PERIOD"].ToString()); //2
Para.Add(dr["VIRTUAL_CODE"].ToString());//3
Para.Add(dr["PETITION_QTY"].ToString());//4
Para.Add(dr["ONWAY_QTY"].ToString()); //5
Para.Add(strUPDATEDATE.Substring(0, 10)); //6
Para.Add("M"); //7
Para.Add(strUPDATEUID); //8
Para.Add(strUPDATEDATE); //9
Para.Add(dr["UPDATEUID"].ToString()); //10
Para.Add(dr["UPDATEDATE"].ToString()); //11
iResult = DBO.UpdateItem(DBT, Para);
UpdateGiftInTransitData(DBO,
DBT,
dr["ITEM"].ToString(),
dr["PERIOD"].ToString(),
dr["VIRTUAL_CODE"].ToString(),
"0",
strUPDATEDATE,
strUPDATEUID);
}
}
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
bResult = true;
}
#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
}
return bResult;
}
/// <summary>
/// 更新期別屬性檔(vds_ITM_period_profile)中,該品號期別的在途量
/// </summary>
/// <param name="Item"></param>
/// <param name="Period"></param>
/// <param name="InTransitQty"></param>
public void UpdatePeriodInTransitData(PUR_05_DBO DBO, DbTransaction DBT, string Item, string Period, string InTransitQty, string strDate, string strUID)
{
ArrayList Para = new ArrayList();
Para.Clear();
Para.Add(Item); //0
Para.Add(Period); //1
Para.Add(InTransitQty); //2
Para.Add(strDate); //3
Para.Add(strUID); //4
DBO.UpdatePeriodInTransitData(DBT, Para);
}
//檢查採購單是否被其他未結案的在途單使用中
public DataTable CheckUsePURCHASE(ArrayList ParameterList)
{
try
{
PUR_05_DBO dbo = new PUR_05_DBO(ref USEDB);
return dbo.CheckUsePURCHASE(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 在途單贈品資料查詢
/// 傳入參數[@在途單號]
/// </summary>
public DataTable QueryGift(ArrayList ParameterList)
{
try
{
PUR_05_DBO dbo = new PUR_05_DBO(ref USEDB);
return dbo.QueryGift(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 1.傳入參數[@採購單號], [@品號],[@期別],[@虛擬品號],[@驗收量]
/// 2.當驗收時,會呼叫此功能,以異動在途商品檔(VDS_PUR_ONWAY_ITEM)的在途數量:
/// 在途數量(onway_qty)=(原在途數量-[@驗收數量])，若在途數量<0，則將在途數量設為0。
/// 3.當在途數量<=0，則將該筆在途單明細的狀態改為"結案"('A')狀態。
/// 4.當該張在途單所有明細資料皆為結案狀態時，將該筆在途單的狀態改為結案('A')狀態。
/// 5.若在途商品為正常品,Call MaintainPeriodProfile.UpdatePeriodInTransitData(品號,期別,
/// 在途量)更新期別屬性檔(VDS_ITM_PERIOD_PROFILE)的下列欄位:在途量(Onway_Qty)
///
/// 6.若採購商品為贈品,則Call MaintainGiftProfile.UpdateGiftInTransitData(品號,期別,虛擬品號,
/// 在途量)更新贈品屬性檔(VDS_MKT_GIFT_PERIOD)的在途量資料
/// </summary>
public void UpdateGoodsInTrainsitByGoodsReceive(ArrayList ParameterList,DbTransaction RootDBT)
{
bool IsRootTranscation = false;
int iResult = 0;
bool bAllClose = true;
try
{
PUR_05_DBO DBO = new PUR_05_DBO(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
//0.[@採購單號]
//1.[@品號]
//2.[@期別]
//3.[@虛擬品號]
//4.[@驗收量]
ArrayList Para = new ArrayList();
string strPURCHASE_NO = ParameterList[0].ToString().Trim();
string strITEM = ParameterList[1].ToString().Trim();
string strPERIOD = ParameterList[2].ToString().Trim();
string strVIRTUAL_CODE = ParameterList[3].ToString().Trim();
string strACCEPT_QTY = ParameterList[4].ToString().Trim();
//0.V_PURCHASE_NO
Para.Clear();
Para.Add(strPURCHASE_NO);
DataTable dtAllItem = DBO.QueryAllItem(Para);
string strONWAY_NO = string.Empty;
foreach (DataRow dr in dtAllItem.Rows)
{
if (
(dr["ITEM"].ToString().Trim() == strITEM) &&
(dr["PERIOD"].ToString().Trim() == strPERIOD) &&
(dr["VIRTUAL_CODE"].ToString().Trim() == strVIRTUAL_CODE)
)
{
//0.ONWAY_NO
//1.ITEM
//2.PERIOD
//3.VIRTUAL_CODE
//4.PETITION_QTY
//5.ONWAY_QTY
//6.CLOSE_DATE
//7.CLOSE_TYPE
//8.UPDATEUID
//9.UPDATEDATE
//10.OLD_UPDATEUID
//11.OLD_UPDATEDATE
//Update
strONWAY_NO = dr["ONWAY_NO"].ToString().Trim();
Para.Clear();
Para.Add(strONWAY_NO); //0
Para.Add(dr["ITEM"].ToString()); //1
Para.Add(dr["PERIOD"].ToString()); //2
Para.Add(dr["VIRTUAL_CODE"].ToString());//3
Para.Add(dr["PETITION_QTY"].ToString());//4
// 2.當驗收時,會呼叫此功能,以異動在途商品檔(VDS_PUR_ONWAY_ITEM)的在途數量:
// 在途數量(onway_qty)=(原在途數量-[@驗收數量])，若在途數量<0，則將在途數量設為0。
// 3.當在途數量<=0，則將該筆在途單明細的狀態改為"結案"('A')狀態
int iNEW_QTY = int.Parse(dr["ONWAY_QTY"].ToString()) - int.Parse(strACCEPT_QTY);
string strCLOSE_TYPE = "";
string strCLOSE_DATE = "";
if (iNEW_QTY <= 0)
{
iNEW_QTY = 0;
strCLOSE_TYPE = "A";
strCLOSE_DATE = DateTime.Now.ToString("yyyy/MM/dd");
}
Para.Add(iNEW_QTY.ToString()); //5
Para.Add(strCLOSE_DATE); //6
Para.Add(strCLOSE_TYPE); //7
Para.Add(""); //8
Para.Add(""); //9
Para.Add(dr["UPDATEUID"].ToString()); //10
Para.Add(dr["UPDATEDATE"].ToString()); //11
if (strCLOSE_TYPE == "")
bAllClose = false;
iResult = DBO.UpdateItem(DBT, Para);
UpdatePeriodInTransitData(DBO,
DBT,
dr["ITEM"].ToString(),
dr["PERIOD"].ToString(),
dr["ONWAY_QTY"].ToString(),
"",
"");
}
else
{
if (dr["CLOSE_TYPE"].ToString().Trim() == "")
bAllClose = false;
}
}
//假如細項都結案的話 主檔也要結案
if (strONWAY_NO.Trim() != "")
{
//如果沒有在途單資料就不更新
if (bAllClose == true)
{
//0.V_ONWAY_NO
//1.V_STATUS
//2.V_ONWAY_DATE
//3.V_UPDATEUID
//4.V_UPDATEDATE
//5.V_OLD_UPDATEUID
//6.V_OLD_UPDATEDATE
//7.V_ENABLE
Para.Clear();
Para.Add(strONWAY_NO); //0
Para.Add("75"); //1
Para.Add(""); //2
Para.Add(""); //3
Para.Add(""); //4
Para.Add(""); //5
Para.Add(""); //6
Para.Add("1"); //7
iResult = DBO.UpdateMain(DBT, Para);
}
}
#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>
/// 套表列印在途單報表,請參閱規格書"PUR05_在途單報表"
/// </summary>
public DataTable PrintGoodsInTransit(ArrayList ParameterList)
{
try
{
PUR_05_DBO dbo = new PUR_05_DBO(ref USEDB);
return dbo.PrintGoodsInTransit(ParameterList);
}
catch (Exception ex)
{
throw ex;
}
}
