/// <summary>
/// 更新退貨單及出庫單
/// </summary>
/// <param name="ParameterList">0:UserID 1:UpdateDate 2:ReturnNo 3:ID 4.UpdateTime</param>
/// <param name="dtUpdate">0:Item 1:Period 3:OnHD_Qty 3:New_Qty 4.ID 5: Old_Qty</param>
/// <param name="RootDBT">Transaction</param>
/// <returns></returns>
public bool UpdateReturnData(ArrayList ParameterList, DataTable dtUpdate, DbTransaction RootDBT)
{
string UserID = ParameterList[0].ToString();
string UpdateDate = ParameterList[1].ToString();
string ReturnNo = ParameterList[2].ToString();
string UnTax_Total_Amt = ParameterList[3].ToString();
string Tax_Total_Amt = ParameterList[4].ToString();
string UnTax_ReMain_Amt = ParameterList[5].ToString();
string Tax_ReMain_Amt = ParameterList[6].ToString();
string Tax_Amt = ParameterList[7].ToString();
string ID = ParameterList[8].ToString();
string UpdateTime = ParameterList[9].ToString();
string LocateSection = "";
bool IsRootTranscation = false;
ArrayList UpdateParamter = new ArrayList();
try
{
DBO.UpdateReturnData db = new UpdateReturnData(ref USEDB);
IVMDBO.MaintainStockInOut dbStock = new IVMDBO.MaintainStockInOut(strConn);
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
//更新主表資料
UpdateParamter.Clear();
UpdateParamter.Add(UserID);
UpdateParamter.Add(UpdateDate);
UpdateParamter.Add(ReturnNo);
UpdateParamter.Add(UnTax_Total_Amt);
UpdateParamter.Add(Tax_Total_Amt);
UpdateParamter.Add(UnTax_ReMain_Amt);
UpdateParamter.Add(Tax_ReMain_Amt);
UpdateParamter.Add(Tax_Amt);
UpdateParamter.Add(ID);
UpdateParamter.Add(UpdateTime);
db.UpdateReturnMain(UpdateParamter, DBT);
//更新Detl資料
for (int i = 0; i < dtUpdate.Rows.Count; i++)
{
UpdateParamter.Clear();
UpdateParamter.Add(UserID);
UpdateParamter.Add(UpdateDate);
UpdateParamter.Add(dtUpdate.Rows[i]["ID"].ToString());
UpdateParamter.Add(int.Parse(dtUpdate.Rows[i]["return_qty"].ToString()));
db.UpdateReturnDetl(UpdateParamter, DBT);
}
//更新稅額資料
UpdateParamter.Clear();
UpdateParamter.Add(ReturnNo);
UpdateParamter.Add(UserID);
UpdateParamter.Add(UpdateDate);
db.UpdateReturnMainTax(UpdateParamter, DBT);
//更新出庫單
//取得STOCKOUT儲區
LocateSection = db.QueryStockOutLocation(ReturnNo);
if (LocateSection == string.Empty)
{
throw new Exception("查無對應的儲位可供更新!");
}
UpdateParamter.Clear();
UpdateParamter.Add("1");
UpdateParamter.Add(ReturnNo);
UpdateParamter.Add(UserID);
//建立更新所使用的datatable
DataTable dtStockout = new DataTable();
dtStockout.Columns.Add("Locate_No");
dtStockout.Columns.Add("Locate_Section");
dtStockout.Columns.Add("Item");
dtStockout.Columns.Add("Period");
dtStockout.Columns.Add("Old_Qty");
dtStockout.Columns.Add("New_Qty");
for(int i = 0; i<dtUpdate.Rows.Count; i++)
{
DataRow drStock = dtStockout.NewRow();
drStock["Locate_No"] = LocateSection.Substring(0,1) ;
drStock["Locate_Section"] = LocateSection;
drStock["Item"]= dtUpdate.Rows[i][0].ToString() ;
drStock["Period"] = dtUpdate.Rows[i][1].ToString();
drStock["Old_Qty"] = int.Parse(dtUpdate.Rows[i][9].ToString());
drStock["New_Qty"] = int.Parse(dtUpdate.Rows[i][3].ToString());
dtStockout.Rows.Add(drStock);
}
dbStock.UpdStockOut(UpdateParamter, dtStockout, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return true;
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
}
public bool CalculateReturnTaxByReturnNo(ArrayList ParameterList, DbTransaction RootDBT)
{
string ReturnNo = ParameterList[0].ToString();
string UserID = ParameterList[1].ToString();
string UpdateDate = ParameterList[2].ToString();
bool IsRootTranscation = false;
ArrayList UpdateParamter = new ArrayList();
try
{
DBO.UpdateReturnData db = new UpdateReturnData(ref USEDB);
IVMDBO.MaintainStockInOut dbStock = new IVMDBO.MaintainStockInOut(strConn);
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
UpdateParamter.Clear();
UpdateParamter.Add(ReturnNo);
UpdateParamter.Add(UserID);
UpdateParamter.Add(UpdateDate);
db.UpdateReturnMainTax(UpdateParamter, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
#endregion
return true;
}
catch (Exception ex)
{
#region 交易失敗
if (IsRootTranscation)
{
//獨立呼叫Transcation失敗
DBT.Rollback();
}
#endregion
throw ex;
}
}
/// <summary>
/// 建立退貨單主檔
/// </summary>
/// <param name="ParameterList">0:UserID 1:ReturnDate(yyyy/MM/dd HH:mm:ss) 2:@IsAdj 3:退貨LocateSection 4:Vendor 5:ReturnNo 6:AdjustNo 7:UnTax_Total_Amt 8:Tax_Total_Amt 9:UnTax_ReMain_Amt 10:Tax_ReMain_Am 11:Tax_Amt</param>
/// <param name="dtDetail">欄位 0:item 1:Period 2:tax_type 3:onhd_qty 4:return_qty 5:adjust_qty 6:cost 7:price</param>
/// <param name="RootDBT">使用的交易 null代表無傳入交易系統可以自行產生</param>
/// <param name="ReturnNo">退貨單號 10碼</param>
/// <param name="MainID"> 主表單ID </param>
/// <param name="AdjustNo">調整單號</param>
/// <returns>是否成功</returns>
public bool CreateReturnData(ArrayList ParameterList, DataTable dtDetail, DbTransaction RootDBT,
out string ReturnNo, out string MainID, out string AdjustNo)
{
ReturnNo = "";
MainID = "";
AdjustNo = "";
string AdjMainID = "";
int PID = 0;
bool IsRootTranscation = false;
string UserID = ParameterList[0].ToString();
string ReturnDate = ParameterList[1].ToString();
string IsAdj = ParameterList[2].ToString();
string LocateSection = ParameterList[3].ToString();
string LocateNo = LocateSection.Substring(0,1);
string Vendor = ParameterList[4].ToString();
string ReasonNo = ParameterList[5].ToString();
string AdjReason = ParameterList[6].ToString();
string strReasonNO = "";
ArrayList ErrorList = new ArrayList();
try
{
//建立退貨主檔資料
DBO.CreateReturnData db = new CreateReturnData(ref USEDB);
DBO.UpdateReturnData dbu = new UpdateReturnData(ref USEDB);
IVMDBO.MaintainAdjustData dbAdj = new IVMDBO.MaintainAdjustData(strConn);
IVMDBO.MaintainStockInOut dbStock = new IVMDBO.MaintainStockInOut(strConn);
//判斷是否有傳入Root Transcation
IsRootTranscation = (RootDBT == null) ? true : false;
#region 啟動交易或指定RootTranscation
if (IsRootTranscation)
{
//獨立呼叫啟動Transcation
Conn = USEDB.CreateConnection();
Conn.Open();
DBT = Conn.BeginTransaction();
}
else
{
DBT = RootDBT;
}
#endregion
if (IsAdj == "1") //建立調整單並建立出庫單
{
ArrayList AdjustList = new ArrayList();
AdjustList.Add(UserID);
AdjustList.Add(ReturnDate);
AdjustList.Add(AdjReason);
AdjustList.Add(ReasonNo);
//轉換DataTable資料格式
DataTable DtAdjust = new DataTable();
//轉換DataTable資料格式
DtAdjust.Columns.Add("Locate_No", typeof(String));
DtAdjust.Columns.Add("Locate_Section", typeof(String));
DtAdjust.Columns.Add("Item", typeof(String));
DtAdjust.Columns.Add("Period", typeof(String));
DtAdjust.Columns.Add("Adjust_Qty", typeof(Int32));
DtAdjust.Columns.Add("Cost", typeof(Double));
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
if (int.Parse(dtDetail.Rows[i]["Adjust_Qty"].ToString()) != 0)
{
DataRow dr = DtAdjust.NewRow();
dr["Locate_No"] = LocateNo;
dr["Locate_Section"] = LocateSection;
dr["Item"] = dtDetail.Rows[i][0].ToString();
dr["Period"] = dtDetail.Rows[i][1].ToString();
dr["Adjust_Qty"] = int.Parse(dtDetail.Rows[i][5].ToString());
dr["Cost"] = double.Parse(dtDetail.Rows[i][6].ToString());
DtAdjust.Rows.Add(dr);
}
}
//建立調整單
dbAdj.CreateAdjustData(AdjustList, DtAdjust, DBT, out AdjMainID, out AdjustNo, out ErrorList);
}
ArrayList ReturnMainList = new ArrayList();
ReturnMainList.Add(UserID);
ReturnMainList.Add(ReturnDate);
ReturnMainList.Add(Vendor);
ReturnMainList.Add(decimal.Parse(ParameterList[7].ToString()));
ReturnMainList.Add(decimal.Parse(ParameterList[8].ToString()));
ReturnMainList.Add(decimal.Parse(ParameterList[9].ToString()));
ReturnMainList.Add(decimal.Parse(ParameterList[10].ToString()));
ReturnMainList.Add(decimal.Parse(ParameterList[11].ToString()));
db.CreateReturnMain(ReturnMainList, DBT, out PID,out ReturnNo);
strReasonNO = ReturnNo;
MainID = PID.ToString();
//建立Detail資料
ArrayList ReturnDetailList = new ArrayList();
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
ReturnDetailList.Clear();
ReturnDetailList.Add(UserID);
ReturnDetailList.Add(ReturnDate);
ReturnDetailList.Add(PID);
ReturnDetailList.Add(dtDetail.Rows[i][0].ToString()); //item
ReturnDetailList.Add(dtDetail.Rows[i][1].ToString()); //Period
ReturnDetailList.Add(dtDetail.Rows[i][2].ToString()); //tax_type
ReturnDetailList.Add(dtDetail.Rows[i][3].ToString()); //onhd_qty
ReturnDetailList.Add(dtDetail.Rows[i][4].ToString()); //return_qty
ReturnDetailList.Add(dtDetail.Rows[i][5].ToString()); //adjust_qty
ReturnDetailList.Add(dtDetail.Rows[i][6].ToString()); //cost
ReturnDetailList.Add(dtDetail.Rows[i][7].ToString()); //price
db.CreateReturnDetl(ReturnDetailList, DBT, out PID);
}
//計算稅額
ReturnMainList.Clear();
ReturnMainList.Add(ReturnNo);
ReturnMainList.Add(UserID);
ReturnMainList.Add(ReturnDate);
dbu.UpdateReturnMainTax(ReturnMainList, DBT);
//建立出庫單
ArrayList StockList = new ArrayList();
StockList.Add("1");
StockList.Add("S4");
StockList.Add(AdjustNo);
StockList.Add(strReasonNO);
StockList.Add(UserID);
DataTable DtStock = new DataTable();
DtStock.Columns.Add("Locate_No", typeof(String));
DtStock.Columns.Add("Locate_Section", typeof(String));
DtStock.Columns.Add("Item", typeof(String));
DtStock.Columns.Add("Period", typeof(String));
DtStock.Columns.Add("InOut_Qty", typeof(Int32));
DtStock.Columns.Add("Cost", typeof(Double));
for (int i = 0; i < dtDetail.Rows.Count; i++)
{
DataRow dr = DtStock.NewRow();
dr["Locate_No"] = LocateNo;
dr["Locate_Section"] = LocateSection;
dr["Item"] = dtDetail.Rows[i][0].ToString();
dr["Period"] = dtDetail.Rows[i][1].ToString();
dr["InOut_Qty"] = int.Parse(dtDetail.Rows[i][4].ToString());
dr["Cost"] = double.Parse(dtDetail.Rows[i][6].ToString());
DtStock.Rows.Add(dr);
}
dbStock.CreateStockOut(StockList, DtStock, DBT);
db.UpdateMainAdjustNo(MainID, AdjustNo, DBT);
#region 交易成功
if (IsRootTranscation)
{
//獨立呼叫Transcation成立
DBT.Commit();
}
return 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
}
}