//调拨单页面对应commit操作，将传进的参数分别插入到调拨单主表和从表中去
public Boolean CommitAction(DataTable dataTable)
{
//将dataTable中的每行数据插入数据库中
foreach (DataRow datarow in dataTable.Rows)
{
//将每行数据插入数据库中
Exchange_headerDC exchange_headerDC = new Exchange_headerDC();
Exchange_lineDC exchange_lineDC = new Exchange_lineDC();
//将数据插入调拨单主表
Boolean flag_header = exchange_headerDC.insertExchange_header(datarow.ItemArray[5].ToString(), int.Parse(datarow.ItemArray[3].ToString()), int.Parse(datarow.ItemArray[2].ToString()), DateTime.Now, DateTime.Now, datarow.ItemArray[4].ToString());
//通过invoice_No获取调拨单主表ID，这样将主表ID插入从表，使主从表有关联
int Exchange_header_id = exchange_headerDC.getExchange_header_idByinvoice_no(datarow.ItemArray[5].ToString());
//将数据插入调拨单从表
Boolean flag_line = exchange_lineDC.insertExchange_line(Exchange_header_id, datarow.ItemArray[0].ToString(), int.Parse(datarow.ItemArray[1].ToString()), DateTime.Now, datarow.ItemArray[4].ToString(), datarow.ItemArray[6].ToString());
//若插入失败时，则返回false
if (flag_header == false || flag_line == false)
{
return(false);
}
}
return(true);
}
//调拨单页面对应Finish操作,对应调拨单动态添加时使用的方法
public Boolean FinishAction(DataTable dataTable)
{
//对dataTable中的每行数据进行操作
foreach (DataRow datarow in dataTable.Rows)
{
Exchange_headerDC exchange_headerDC = new Exchange_headerDC();
Boolean flag = false;
//调拨单页面对应的Finish操作，Finish操作包括Commit提交和Debit扣账
flag = exchange_headerDC.FinishAction(datarow.ItemArray[0].ToString(), int.Parse(datarow.ItemArray[1].ToString()), int.Parse(datarow.ItemArray[2].ToString()), int.Parse(datarow.ItemArray[3].ToString()), int.Parse(datarow.ItemArray[4].ToString()), datarow.ItemArray[5].ToString(), datarow.ItemArray[6].ToString(), int.Parse(datarow.ItemArray[7].ToString()), int.Parse(datarow.ItemArray[8].ToString()), int.Parse(datarow.ItemArray[9].ToString()), datarow.ItemArray[10].ToString());
//操作失败时，则返回false
if (flag == false)
{
return(false);
}
}
return(true);
}
//调拨单操作页面对应commit操作，将传进的参数分别插入到调拨单主表和从表中去
public Boolean CommitAction(string invoice_no, string item_name, int required_qty)
{
Exchange_headerDC exchange_headerDC = new Exchange_headerDC();
Exchange_lineDC exchange_lineDC = new Exchange_lineDC();
//将数据插入调拨单主表
Boolean flag_header = exchange_headerDC.insertExchange_header(invoice_no);
//通过invoice_No获取调拨单主表ID，这样将主表ID插入从表，使主从表有关联
int Exchange_header_id = exchange_headerDC.getExchange_header_idByinvoice_no(invoice_no);
//将数据插入调拨单从表
Boolean flag_line = exchange_lineDC.insertExchange_line(Exchange_header_id, item_name, required_qty);
//若插入失败时，则返回false
if (flag_header == false || flag_line == false)
{
return(false);
}
else
{
return(true);
}
}
/**作者：周雅雯 修改时间：2016/9/11
* 调拨单页面对应的Finish操作，Finish操作包括Commit提交和Debit扣账
* Commit提交：
* 将数据插入到调拨单主表和调拨单从表
* Debit扣账：
* 扣除庫存总表的庫存，扣除庫存明細表中对应仓库的庫存量，增加調入倉的庫存量、可用量，将信息插入到交易表中
*/
public Boolean FinishAction(string invoice_no, int out_subinventory, int in_subinventory, int in_locator_id, int out_locator_id, string exchange_wo_no, string item_name, int operation_seq_num, int required_qty, int exchanged_qty, string remark)
{
Exchange_headerDC exchange_headerDC = new Exchange_headerDC();
List <ModelExchange_header> list = new List <ModelExchange_header>();
DateTime transaction_time = DateTime.Now;
string sql = null;
list = exchange_headerDC.getExchange_headerByINVOICE_NO(invoice_no);
if (list == null)
{
sql =
//Commit提交
//+ "--将数据插入到调拨单主表"
"insert into wms_exchange_header "
+ "(invoice_no,out_subinventory_key,in_subinventory_key,out_locator_id,in_locator_id,status,exchange_wo_no)values "
+ "(@invoice_no,@out_subinventory,@in_subinventory,@out_locator_id,@in_locator_id,'N',@exchange_wo_no); "
//+ "--将数据插入到调拨单从表"
+ "insert into wms_exchange_line "
+ "(exchange_header_id,item_name,required_qty,operation_seq_num,exchange_wo_no,remark,exchanged_qty)values "
+ "((select exchange_header_id from wms_exchange_header where invoice_no=@invoice_no ),@item_name,@required_qty,@operation_seq_num,@exchange_wo_no,@remark,@exchanged_qty); "
//debit扣账
//+ "--减少庫存总表的庫存(调出仓）"
+ "update wms_items_onhand_qty_detail set onhand_quantiy=onhand_quantiy - @required_qty where subinventory=@out_subinventory and item_id = (select item_id from wms_pn where item_name=@item_name);"
//+ "--减少庫存明細表中对应仓库的庫存量(调出仓）"
+ "update wms_material_io set onhand_qty=onhand_qty - @required_qty where subinventory=(select subinventory_name from wms_subinventory where subinventory_key=@out_subinventory) and item_id = (select item_id from wms_pn where item_name=@item_name) and frame_key=@out_locator_id;"
//+ "--增加調入倉的庫存量、可用量（库存总表）"
+ "update wms_items_onhand_qty_detail set onhand_quantiy=onhand_quantiy - @required_qty where subinventory=@in_subinventory and item_id = (select item_id from wms_pn where item_name=@item_name);"
//+ "--增加調入倉的庫存量、可用量（库存明细表）"
+ "update wms_material_io set onhand_qty=onhand_qty - @required_qty where subinventory=(select subinventory_name from wms_subinventory where subinventory_key=@in_subinventory) and item_id = (select item_id from wms_pn where item_name=@item_name) and frame_key=@in_locator_id;"
//+ "--将数据插入到交易表"
+ "insert into wms_transaction_operation (transaction_qty,transaction_type,transaction_time) values (@required_qty,'exchange ',@transaction_time)";
}
else
{
sql =
//+ "--将数据插入到调拨单从表"
"insert into wms_exchange_line "
+ "(exchange_header_id,item_name,required_qty,operation_seq_num,exchange_wo_no,remark,exchanged_qty)values "
+ "((select exchange_header_id from wms_exchange_header where invoice_no=@invoice_no ),@item_name,@required_qty,@operation_seq_num,@exchange_wo_no,@remark,@exchanged_qty); "
//debit扣账
//+ "--减少庫存总表的庫存(调出仓）"
+ "update wms_items_onhand_qty_detail set onhand_quantiy=onhand_quantiy - @required_qty where subinventory=@out_subinventory and item_id = (select item_id from wms_pn where item_name=@item_name);"
//+ "--减少庫存明細表中对应仓库的庫存量(调出仓）"
+ "update wms_material_io set onhand_qty=onhand_qty - @required_qty where subinventory=(select subinventory_name from wms_subinventory where subinventory_key=@out_subinventory) and item_id = (select item_id from wms_pn where item_name=@item_name) and frame_key=@out_locator_id;"
//+ "--增加調入倉的庫存量、可用量（库存总表）"
+ "update wms_items_onhand_qty_detail set onhand_quantiy=onhand_quantiy - @required_qty where subinventory=@in_subinventory and item_id = (select item_id from wms_pn where item_name=@item_name);"
//+ "--增加調入倉的庫存量、可用量（库存明细表）"
+ "update wms_material_io set onhand_qty=onhand_qty - @required_qty where subinventory=(select subinventory_name from wms_subinventory where subinventory_key=@in_subinventory) and item_id = (select item_id from wms_pn where item_name=@item_name) and frame_key=@in_locator_id;"
//+ "--将数据插入到交易表"
+ "insert into wms_transaction_operation (transaction_qty,transaction_type,transaction_time) values (@required_qty,'exchange ',@transaction_time)";
}
SqlParameter[] parameters =
{
new SqlParameter("invoice_no", invoice_no),
new SqlParameter("out_subinventory", out_subinventory),
new SqlParameter("in_subinventory", in_subinventory),
new SqlParameter("in_locator_id", in_locator_id),
new SqlParameter("out_locator_id", out_locator_id),
new SqlParameter("exchange_wo_no", exchange_wo_no),
new SqlParameter("item_name", item_name),
new SqlParameter("operation_seq_num", operation_seq_num),
new SqlParameter("required_qty", required_qty),
new SqlParameter("exchanged_qty", exchanged_qty),
new SqlParameter("remark", remark),
new SqlParameter("transaction_time", transaction_time)
};
DB.connect();
//返回受影响行数InfluenceNum
int InfluenceNum = DB.tran(sql, parameters);
if (InfluenceNum > 0)
{
return(true);
}
else
{
return(false);
}
}