public void Compute(DB.IDB db, string sheet_property, string db_no, string branch_no, string item_no, decimal sheet_qty, decimal sheet_price, out decimal price, out decimal end_price, out decimal adjust_amt) { string sql = "select * from bi_t_item_info where item_no='" + item_no + "'"; Model.bi_t_item_info item = db.ExecuteToModel <Model.bi_t_item_info>(sql, null); if (item == null) { throw new Exception("不存在商品内码" + item.item_no); } sql = "select * from ic_t_branch_stock where branch_no='" + branch_no + "'" + " and item_no='" + item_no + "'"; Model.ic_t_branch_stock stock = db.ExecuteToModel <Model.ic_t_branch_stock>(sql, null); if (stock == null) { stock = new Model.ic_t_branch_stock(); stock.branch_no = branch_no; stock.item_no = item_no; stock.stock_qty = 0; stock.cost_price = item.price; stock.display_flag = "1"; stock.last_price = 0; stock.fifo_price = 0; stock.update_time = System.DateTime.Now; db.Insert(stock); } if (sheet_property == "1") { //入库类 if (db_no.Equals("-")) { InPrice(stock.stock_qty, stock.cost_price, -sheet_qty, sheet_price, out price, out end_price, out adjust_amt); } else { InPrice(stock.stock_qty, stock.cost_price, sheet_qty, sheet_price, out price, out end_price, out adjust_amt); } } else { //出库类 if (db_no.Equals("-")) { OutPrice(stock.stock_qty, stock.cost_price, sheet_qty, sheet_price, out price, out end_price, out adjust_amt); } else { OutPrice(stock.stock_qty, stock.cost_price, -sheet_qty, sheet_price, out price, out end_price, out adjust_amt); } } }
// D:客户退货单 A:采购入库 F:采购退货 G:调拨单 void IInOutBLL.CheckInOut(string sheet_no, string approve_man, DateTime update_time) { var db = new DB.DBByAutoClose(Appsetting.conn); DB.IDB d = db; try { // string sql = "select trans_no,approve_flag,update_time from ic_t_inout_store_master where sheet_no='" + sheet_no + "'"; var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count == 0) { throw new Exception("单据不存在[" + sheet_no + "]"); } ic_t_inout_store_master master = d.ExecuteToModel <ic_t_inout_store_master>("select * from ic_t_inout_store_master where sheet_no='" + sheet_no + "'", null); CheckSheet check = new CheckSheet(); switch (master.trans_no) { case "A": check.CheckPISheet(sheet_no, approve_man, update_time); break; case "D": check.CheckRISheet(sheet_no, approve_man, update_time); break; case "F": check.CheckROSheet(sheet_no, approve_man, update_time); break; case "G": check.CheckIOSheet(sheet_no, approve_man, update_time); break; case "I": check.CheckSOSheet(sheet_no, approve_man, update_time); break; default: check.CheckOOSheet(sheet_no, approve_man, update_time); break; } } catch (Exception ex) { LogHelper.writeLog("InOutBLL.CheckInOut()", ex.ToString(), sheet_no, approve_man); throw ex; } }
public void UpdateChectInitSheet(global::Model.ic_t_check_init init) { var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // string sql = "select * from ic_t_check_init where sheet_no='" + init.sheet_no + "'"; var item = d.ExecuteToModel <ic_t_check_init>(sql, null); if (item == null || string.IsNullOrEmpty(item.sheet_no)) { throw new Exception("单据不存在[" + init.sheet_no + "]"); } else { if (item.approve_flag.Equals("1")) { throw new Exception("单据已审核[" + init.sheet_no + "]"); } if (item.update_time > init.update_time) { throw new Exception("单据已被他人修改[" + init.sheet_no + "]"); } } sql = "delete from ic_t_check_init where sheet_no='" + init.sheet_no + "'"; d.ExecuteScalar(sql, null); // init.update_time = DateTime.Now; d.Insert(init); // db.CommitTran(); } catch (Exception ex) { Log.writeLog("CheckBLL.UpdateChectInitSheet()", ex.ToString(), init.sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }
void ISupcustGroup.SaveGroup(List <bi_t_supcust_group> lis) { DB.DBByHandClose db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); d.ExecuteScalar("delete from dbo.bi_t_supcust_group", null); string sql = @" SELECT * FROM dbo.bi_t_supcust_group WHERE SupCust_GroupNo = @SupCust_GroupNo "; foreach (bi_t_supcust_group group in lis) { var item = d.ExecuteToModel <bi_t_supcust_group>(sql, new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@SupCust_GroupNo", group.SupCust_GroupNo) }); if (item == null || string.IsNullOrEmpty(item.SupCust_GroupNo)) { d.Insert(group); } else { d.Update(group, "SupCust_GroupNo"); } } db.CommitTran(); } catch (Exception) { db.RollBackTran(); throw; } finally { db.Close(); } }
public void UpdateCheckFinish(List <ic_t_check_finish> finishs) { var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // foreach (ic_t_check_finish f in finishs) { ic_t_check_finish item = d.ExecuteToModel <ic_t_check_finish>("select * from ic_t_check_finish where sheet_no='" + f.sheet_no + "' and item_no='" + f.item_no + "'", null); if (f.update_time <= item.update_time) { throw new Exception("库存已被修改"); } item.change_flag = item.change_flag; item.update_time = f.update_time; item.memo = f.memo; d.Update(item, "sheet_no,item_no", "change_flag,update_time,memo"); } // db.CommitTran(); } catch (Exception ex) { Log.writeLog("CheckBLL.CheckPDSheet()", ex.ToString()); db.RollBackTran(); throw ex; } finally { db.Close(); } }
public void CheckPDSheet(string sheet_no) { var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); ic_t_check_master master = d.ExecuteToModel <ic_t_check_master>("select * from ic_t_check_master where sheet_no='" + sheet_no + "'", null); if (master == null || string.IsNullOrEmpty(master.sheet_no)) { throw new Exception("单据:[" + sheet_no + "]不存在"); } if (!master.approve_flag.Equals("0")) { throw new Exception("单据:[" + sheet_no + "]已审核"); } master.approve_flag = "1"; d.Update(master, "sheet_no", "approve_flag"); // db.CommitTran(); } catch (Exception ex) { Log.writeLog("CheckBLL.CheckPDSheet()", ex.ToString(), sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }
public void SaveGrant(List <global::Model.sa_t_oper_grant> grant) { DB.DBByHandClose db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); global::Model.sa_t_oper_grant g = d.ExecuteToModel <global::Model.sa_t_oper_grant>( "select top 1 * from sa_t_oper_grant where oper_id='" + grant[0].oper_id + "'", null); if (g != null && g.update_time > grant[0].update_time) { throw new Exception("权限已被修改"); } else { d.ExecuteScalar("delete sa_t_oper_grant where oper_id='" + grant[0].oper_id + "'", null); foreach (var item in grant) { d.Insert(item); } } db.CommitTran(); } catch (Exception) { db.RollBackTran(); throw; } finally { db.Close(); } }
public void CheckPCSheet(ic_t_check_init ini) { var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // ic_t_check_init master = d.ExecuteToModel <ic_t_check_init>("select * from ic_t_check_init where sheet_no='" + ini.sheet_no + "'", null); if (master == null || string.IsNullOrEmpty(master.sheet_no)) { throw new Exception("单据:[" + ini.sheet_no + "]不存在"); } if (!master.approve_flag.Equals("0")) { throw new Exception("单据:[" + ini.sheet_no + "]已审核"); } master.sheet_no = ini.sheet_no; master.approve_flag = "1"; master.approve_date = ini.approve_date; master.approve_man = ini.approve_man; master.update_time = DateTime.Now; master.check_status = "2"; master.memo = ini.memo; master.end_date = DateTime.Now; d.Update(master, "sheet_no"); // DataTable tb = d.ExecuteToTable("select * from ic_t_check_finish where sheet_no='" + ini.sheet_no + "'", null); foreach (DataRow dr in tb.Rows) { if ("1".Equals(dr["change_flag"].ToString())) { //修改库存 //写入库存流水、结存 string sql = "select * from ic_t_branch_stock where branch_no='" + master.branch_no + "'" + " and item_no='" + dr["item_no"] + "' "; Model.ic_t_branch_stock stock = d.ExecuteToModel <Model.ic_t_branch_stock>(sql, null); if (stock == null) { continue; } // sql = "select * from bi_t_item_info where item_no='" + dr["item_no"] + "'"; Model.bi_t_item_info it = d.ExecuteToModel <Model.bi_t_item_info>(sql, null); if (it == null) { throw new Exception("不存在商品内码" + dr["item_no"]); } // sql = "select isnull(max(flow_id)+1,1) from ic_t_flow_dt"; Model.ic_t_flow_dt flow = new Model.ic_t_flow_dt(); flow.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null)); flow.branch_no = master.branch_no; flow.item_no = dr["item_no"].ToString(); flow.oper_date = System.DateTime.Now; flow.init_qty = stock.stock_qty; flow.init_amt = stock.stock_qty * stock.cost_price; flow.new_qty = Conv.ToDecimal(dr["real_qty"]) - Conv.ToDecimal(dr["stock_qty"]); flow.new_amt = flow.new_qty * Conv.ToDecimal(dr["in_price"]); flow.settle_qty = flow.init_qty + flow.new_qty; flow.settle_amt = flow.init_amt + flow.new_qty * Conv.ToDecimal(dr["sale_price"]); flow.cost_price = Conv.ToDecimal(dr["sale_price"]); flow.db_type = Conv.ToDecimal(dr["real_qty"]) > Conv.ToDecimal(dr["stock_qty"]) ? "+" : "-"; flow.sheet_no = dr["sheet_no"].ToString(); flow.sheet_type = "PC"; flow.voucher_no = dr["sheet_no"].ToString(); flow.supcust_no = ""; flow.supcust_flag = ""; flow.oper_day = System.DateTime.Now.ToString("yyyy/MM/dd"); flow.adjust_amt = flow.settle_amt; flow.cost_type = it.cost_type; flow.sale_price = Conv.ToDecimal(dr["sale_price"]); d.Insert(flow); // if (flow.new_qty != 0) { Model.ic_t_cost_adjust ad = new Model.ic_t_cost_adjust(); sql = "select isnull(max(flow_id)+1,1) from ic_t_cost_adjust"; ad.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null)); ad.branch_no = dr["branch_no"].ToString(); ad.item_no = dr["item_no"].ToString(); ad.oper_date = System.DateTime.Now; ad.old_price = stock.cost_price; ad.new_price = flow.cost_price; ad.in_qty = flow.new_qty; ad.sheet_no = dr["sheet_no"].ToString(); ad.memo = ""; ad.type_no = "1"; ad.adjust_amt = flow.new_qty; ad.sup_no = ""; ad.max_flow_id = flow.flow_id; ad.cost_type = it.cost_type; ad.old_qty = stock.stock_qty; d.Insert(ad); } // stock.stock_qty = flow.settle_qty; stock.cost_price = flow.cost_price; stock.last_price = Conv.ToDecimal(dr["in_price"]); d.Update(stock, "branch_no,item_no", "stock_qty,cost_price,last_price"); } } // db.CommitTran(); } catch (Exception ex) { Log.writeLog("CheckBLL.CheckPCSheet()", ex.ToString(), ini.sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }
void ICheckBLL.ChangeCheckSheet(Model.ic_t_check_master ord, List <Model.ic_t_check_detail> lines) { var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // string sql = "select approve_flag,update_time from ic_t_check_master where sheet_no='" + ord.sheet_no + "'"; var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count == 0) { throw new Exception("单据不存在[" + ord.sheet_no + "]"); } else { var row = tb.Rows[0]; if (row["approve_flag"].ToString() == "1") { throw new Exception("单据已审核[" + ord.sheet_no + "]"); } if (Helper.Conv.ToDateTime(row["update_time"]) > ord.update_time) { throw new Exception("单据已被他人修改[" + ord.sheet_no + "]"); } } DataTable finfishtb = d.ExecuteToTable("select * from ic_t_check_detail where sheet_no='" + ord.sheet_no + "' ", null); sql = "delete from ic_t_check_detail where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); sql = "delete from ic_t_check_master where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); // ord.update_time = DateTime.Now; d.Insert(ord); foreach (Model.ic_t_check_detail line in lines) { sql = "select isnull(max(flow_id),0) + 1 as flow_id from ic_t_check_detail "; line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null)); d.Insert(line); var finfish = d.ExecuteToModel <ic_t_check_finish>("select * from ic_t_check_finish where sheet_no='" + ord.check_no + "' and item_no='" + line.item_no + "'", null); if (finfish == null) { finfish = new ic_t_check_finish() { sheet_no = ord.check_no, item_no = line.item_no, branch_no = ord.branch_no, change_flag = "1", sale_price = line.sale_price, stock_qty = line.stock_qty, create_time = DateTime.Now, in_price = line.in_price, memo = line.memo, real_qty = line.real_qty, update_time = DateTime.Now, }; d.Insert(finfish); } else { DataRow t = finfishtb.Select().Where(a => a["item_no"].ToString().Equals(finfish.item_no)).SingleOrDefault(); finfish.real_qty += line.real_qty; if (t != null) { finfish.real_qty -= Conv.ToDecimal(t["real_qty"]); } finfish.update_time = DateTime.Now; d.Update(finfish, "sheet_no,item_no"); } } // db.CommitTran(); } catch (Exception ex) { Log.writeLog("CheckBLL.ChangeCheckSheet()", ex.ToString(), ord.sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }
void ICheckBLL.AddCheckSheet(Model.ic_t_check_master ord, List <Model.ic_t_check_detail> lines, out string sheet_no) { sheet_no = ""; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); sheet_no = MaxCode(d, "PD"); ord.sheet_no = sheet_no; ord.create_time = DateTime.Now; ord.update_time = ord.create_time; // d.Insert(ord); foreach (Model.ic_t_check_detail line in lines) { var bak = d.ExecuteToModel <ic_t_check_bak>("select * from ic_t_check_bak where sheet_no='" + ord.check_no + "' and item_no='" + line.item_no + "' ", null); if (bak != null) { line.stock_qty = bak.stock_qty; line.in_price = bak.price; line.sale_price = bak.sale_price; line.balance_qty = line.real_qty - line.stock_qty; } string sql = "select isnull(max(flow_id),0) + 1 as flow_id from ic_t_check_detail "; line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null)); line.sheet_no = sheet_no; d.Insert(line); var finfish = d.ExecuteToModel <ic_t_check_finish>("select * from ic_t_check_finish where sheet_no='" + ord.check_no + "' and item_no='" + line.item_no + "'", null); if (finfish == null) { finfish = new ic_t_check_finish() { sheet_no = ord.check_no, item_no = line.item_no, branch_no = ord.branch_no, change_flag = "1", sale_price = line.sale_price, stock_qty = line.stock_qty, create_time = DateTime.Now, in_price = line.in_price, memo = line.memo, real_qty = line.real_qty, update_time = DateTime.Now, }; d.Insert(finfish); } else { finfish.real_qty += line.real_qty; finfish.update_time = DateTime.Now; d.Update(finfish, "sheet_no,item_no"); } } // db.CommitTran(); } catch (Exception ex) { Log.writeLog("CheckBLL.AddCheckSheet()", ex.ToString(), ord.sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }