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 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(); } }
void IBLL.ISettle.WriteFHD(List <model.sm_t_salesheet> lst1, List <model.sm_t_salesheet_detail> lst2, List <model.ot_pay_flow> lst3) { var db = new DB.MySqlByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); //提取付款方式 string sql = "select * from bi_t_payment_info"; var tb = d.ExecuteToTable(sql, null); Dictionary <string, model.bi_t_payment_info> dic_payment = new Dictionary <string, model.bi_t_payment_info>(); foreach (System.Data.DataRow row in tb.Rows) { string pay_way = row["pay_way"].ToString().Trim(); dic_payment.Add(pay_way, DB.ReflectionHelper.DataRowToModel <model.bi_t_payment_info>(row)); } //重取单号 foreach (model.sm_t_salesheet item in lst1) { string offline_sheet_no = item.sheet_no; item.sheet_no = GetSheetCode(db, "SO"); item.offline_sheet_no = offline_sheet_no; } // Dictionary <string, model.sm_t_salesheet> dic = new Dictionary <string, model.sm_t_salesheet>(); foreach (model.sm_t_salesheet item in lst1) { dic.Add(item.offline_sheet_no, item); } //生成明细行号,单号 foreach (model.sm_t_salesheet item in lst1) { item.temp1 = 0; } foreach (model.sm_t_salesheet_detail item in lst2) { model.sm_t_salesheet it; if (dic.TryGetValue(item.sheet_no, out it) == true) { it.temp1 += 1; item.sheet_no = it.sheet_no; } } //生成付款明细行号,单号 foreach (model.sm_t_salesheet item in lst1) { item.temp1 = 0; } foreach (model.ot_pay_flow item in lst3) { model.sm_t_salesheet it; if (dic.TryGetValue(item.sheet_no, out it) == true) { it.temp1 += 1; item.flow_id = it.temp1; item.sheet_no = it.sheet_no; } } // //写入单头 foreach (model.sm_t_salesheet item in lst1) { d.Insert(item); } //写入单体 foreach (model.sm_t_salesheet_detail item in lst2) { d.Insert(item); } //写入支付 foreach (model.ot_pay_flow item in lst3) { d.Insert(item); } //批处理 foreach (model.sm_t_salesheet item in lst1) { //审核发货单 item.approve_flag = "1"; item.approve_man = "1001"; item.approve_date = System.DateTime.Now; d.Update(item, "sheet_no", "approve_flag,approve_man,approve_date"); //提取付款方式 string pay_way = ""; decimal pay_amount = 0; string ml_pay_way = ""; decimal ml_amount = 0; foreach (model.ot_pay_flow payflow in lst3) { if (item.sheet_no == payflow.sheet_no) { if (payflow.pay_way != "Y") { if (pay_way == "")//除了抹零,只处理一种支付方式 { pay_way = payflow.pay_way; pay_amount += payflow.pay_amount; } else { pay_amount += payflow.pay_amount; } } else { ml_pay_way = "Y"; ml_amount += payflow.pay_amount; } } } //写入结算单 string settle_sheet_no = ""; string visa_id = ""; if (pay_way != "") { model.rp_t_recpay_record_info it = new model.rp_t_recpay_record_info(); it.sheet_no = GetSheetCode(db, "CP"); settle_sheet_no = it.sheet_no; it.supcust_no = item.cust_no; it.supcust_flag = "C"; it.flag_post = ""; it.total_amount = pay_amount; if (ml_pay_way != "") { it.free_money = ml_amount; } it.coin_no = "RMB"; it.coin_rate = 1; it.pay_way = pay_way; it.approve_flag = "0"; it.oper_id = "1001"; it.oper_date = System.DateTime.Now; it.deal_man = ""; it.approve_man = ""; it.approve_date = System.DateTime.MinValue; it.other1 = ""; it.other2 = ""; it.other3 = ""; model.bi_t_payment_info payment; if (dic_payment.TryGetValue(pay_way, out payment) == true) { if (payment.visa_id == null || payment.visa_id == "") { throw new Exception("付款方式" + pay_way + "找不到默认现金银行帐户!"); } else { it.visa_id = payment.visa_id; } } else { throw new Exception("付款方式" + pay_way + "找不到默认现金银行帐户!"); } visa_id = it.visa_id; it.num1 = 0; it.num2 = 0; it.num3 = 0; it.cm_branch = "00"; it.branch_no = item.branch_no; it.from_date = System.DateTime.Now; it.to_date = System.DateTime.Now; it.rc_sheet_no = ""; it.pay_memo = ""; it.money_date = System.DateTime.Now; d.Insert(it); // model.rp_t_recpay_record_detail line = new model.rp_t_recpay_record_detail(); line.sheet_no = it.sheet_no; line.voucher_no = item.sheet_no; line.sheet_amount = item.total_amount; line.paid_amount = 0; line.paid_free = 0; line.pay_amount = pay_amount; line.pay_free = ml_amount; line.memo = ""; line.other1 = ""; line.other2 = ""; line.other3 = ""; line.num1 = 0; line.num2 = 0; line.num3 = 0; line.pay_date = System.DateTime.Now; line.item_no = ""; line.path = "+"; line.select_flag = "1"; line.voucher_type = ""; line.oper_date = System.DateTime.Now; line.voucher_other1 = ""; line.voucher_other2 = ""; line.order_no = ""; d.Insert(line); // it.approve_date = System.DateTime.Now; it.approve_flag = "1"; it.approve_man = "1001"; d.Update(it, "sheet_no", "approve_date,approve_flag,approve_man"); } //写入收支流水 if (pay_way != "") { model.bank_t_cash_master it = new model.bank_t_cash_master(); it.sheet_no = GetSheetCode(d, "SR"); it.branch_no = item.branch_no; it.voucher_no = settle_sheet_no; it.visa_id = visa_id; it.visa_in = ""; it.pay_way = pay_way; it.coin_no = "RMB"; it.coin_rate = 1; it.deal_man = ""; it.oper_id = "1001"; it.oper_date = System.DateTime.Now; it.bill_total = pay_amount; it.bill_flag = "S"; it.cm_branch = "00"; it.approve_flag = "0"; it.approve_man = ""; it.approve_date = System.DateTime.MinValue; it.other1 = ""; it.other2 = ""; it.other3 = ""; it.num1 = 0; it.num2 = 0; it.num3 = 0; d.Insert(it); model.bank_t_cash_detail line = new model.bank_t_cash_detail(); line.sheet_no = it.sheet_no; line.type_no = "101"; line.bill_cash = pay_amount; line.memo = ""; d.Insert(line); // it.approve_date = System.DateTime.Now; it.approve_flag = "1"; it.approve_man = "1001"; d.Update(it, "sheet_no", "approve_date,approve_flag,approve_man"); } } db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); LogHelper.writeLog("", ex.ToString()); throw; } finally { db.Close(); } // }