public void ComTable(DB.IDB from_db, DB.IDB to_db, out List <string> insert_lis, out List <string> update_lis) { insert_lis = new List <string>(); update_lis = new List <string>(); string sql = ""; if (sync_type == 0) { sql = $"select {sel_field2} keys,update_time from {table_name} order by {key_field}"; DataTable from_tb = from_db.ExecuteToTable(sql); Dictionary <string, string> to_dic = to_db.ExecuteToDic <string, string>(sql, "keys", "update_time"); foreach (DataRow dr in from_tb.Rows) { string key = dr["keys"].ToString(); DateTime update_time = dr["update_time"].ToDateTime(); if (to_dic.TryGetValue(key, out string dt)) { //比较时间 if (dt.ToDateTime() != update_time) { update_lis.Add(key); } } else { insert_lis.Add(key); } } } else if (sync_type == 1) { sql = $"select {sel_field2} keys from {table_name} order by {key_field}"; DataTable from_tb = from_db.ExecuteToTable(sql); foreach (DataRow dr in from_tb.Rows) { string key = dr["keys"].ToString(); insert_lis.Add(key); } to_db.ExecuteScalar($"delete from {table_name}"); } else if (sync_type == 2) { sql = $"select {sel_field2} keys from {table_name} order by {key_field}"; DataTable from_tb = from_db.ExecuteToTable(sql); List <string> to_lis = to_db.ExecuteToList <string>(sql, "keys"); HashSet <string> to_hs = new HashSet <string>(to_lis); foreach (DataRow dr in from_tb.Rows) { string key = dr["keys"].ToString(); if (!to_hs.Contains(key)) { insert_lis.Add(key); } } } }
void IBLL.ICusPriceOrder.Check(string sheet_no, string approve_man) { string sql = "select * from pm_t_flow_main where sheet_no='" + sheet_no + "'"; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count == 0) { throw new Exception("单据不存在" + sheet_no); } else { var row = tb.Rows[0]; if (row["approve_flag"].ToString() == "1") { throw new Exception("单据已审核" + sheet_no); } } sql = "update pm_t_flow_main set approve_flag='1',approve_man='" + approve_man + "',approve_date='" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "' where sheet_no='" + sheet_no + "'"; d.ExecuteScalar(sql, null); // sql = "select * from pm_t_price_flow_detial where sheet_no='" + sheet_no + "'"; tb = d.ExecuteToTable(sql, null); foreach (System.Data.DataRow row in tb.Rows) { string item_no = row["item_no"].ToString(); decimal new_price = Helper.Conv.ToDecimal(row["new_price"].ToString()); decimal new_price2 = Helper.Conv.ToDecimal(row["new_price2"].ToString()); decimal new_price3 = Helper.Conv.ToDecimal(row["new_price3"].ToString()); sql = "update bi_t_item_info set base_price=" + new_price.ToString() + ",base_price2=" + new_price2.ToString() + ",base_price3=" + new_price3.ToString() + " where item_no='" + item_no + "'"; d.ExecuteScalar(sql, null); } // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); throw; } finally { db.Close(); } }
void IInOutBLL.ChangeSaleSheet(Model.sm_t_salesheet ord, List <Model.sm_t_salesheet_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 sm_t_salesheet 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"]).ToString("yyyy-MM-dd HH:mm:ss") != ord.update_time.ToString("yyyy-MM-dd HH:mm:ss")) { throw new Exception("单据已被他人修改[" + ord.sheet_no + "]"); } } sql = "delete from sm_t_salesheet_detail where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); sql = "delete from sm_t_salesheet where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); // ord.update_time = DateTime.Now; d.Insert(ord); foreach (Model.sm_t_salesheet_detail line in lines) { sql = "select isnull(max(flow_id),0) + 1 as flow_id from sm_t_salesheet_detail "; line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null)); d.Insert(line); } // db.CommitTran(); } catch (Exception ex) { LogHelper.writeLog("InOutBLL.ChangeSaleSheet()", ex.ToString(), ord.sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }
public void AddChectInitSheet(global::Model.ic_t_check_init check_init, out string sheet_no) { sheet_no = ""; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); check_init.sheet_no = sheet_no = MaxCode(d, "PC"); check_init.create_time = DateTime.Now; check_init.update_time = DateTime.Now; d.Insert(check_init); //备份仓库 DataTable tb = d.ExecuteToTable(@"select * from ic_t_branch_stock s left join bi_t_item_info i on i.item_no=s.item_no where branch_no='" + check_init.branch_no + "'", null); foreach (DataRow dr in tb.Rows) { ic_t_check_bak bak = new ic_t_check_bak() { sheet_no = check_init.sheet_no, branch_no = check_init.branch_no, item_no = dr["item_no"].ToString(), stock_qty = Conv.ToDecimal(dr["stock_qty"]), cost_price = Conv.ToDecimal(dr["cost_price"]), price = Conv.ToDecimal(dr["last_price"]), sale_price = Conv.ToDecimal(dr["sale_price"]), create_time = DateTime.Now, update_time = DateTime.Now }; d.Insert(bak); } db.CommitTran(); } catch (Exception ex) { LogHelper.writeLog("CheckBLL.AddChectInitSheet()", ex.ToString(), check_init.sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }
// 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; } }
T DB.IDB.ExecuteToModel <T>(string sql, System.Data.IDbDataParameter[] pars) { DB.IDB db = this; var dt = db.ExecuteToTable(sql, pars); if (dt.Rows.Count == 0) { return(default(T)); } else { return(DB.ReflectionHelper.DataRowToModel <T>(dt.Rows[0])); } }
void IBLL.ICusFY.Change(Model.rp_t_supcust_fy_master ord, List <Model.rp_t_supcust_fy_detail> lines) { string sql = "select * from rp_t_supcust_fy_master where sheet_no='" + ord.sheet_no + "'"; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // 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); } } sql = "delete from rp_t_supcust_fy_detail where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); sql = "delete from rp_t_supcust_fy_master where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); // d.Insert(ord); foreach (Model.rp_t_supcust_fy_detail line in lines) { sql = "select isnull(max(flow_id)+1,1) from rp_t_supcust_fy_detail"; line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null)); d.Insert(line); } // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); throw; } finally { db.Close(); } }
void IBLL.ICusPriceOrder.Change(Model.pm_t_flow_main ord, List <Model.pm_t_price_flow_detial> lines) { string sql = "select * from pm_t_flow_main where sheet_no='" + ord.sheet_no + "'"; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // 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); } } sql = "delete from pm_t_price_flow_detial where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); sql = "delete from pm_t_flow_main where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); // d.Insert(ord); foreach (Model.pm_t_price_flow_detial line in lines) { d.Insert(line); } // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); throw; } finally { db.Close(); } }
void ICheckBLL.DeleteCheckSheet(string sheet_no, DateTime update_time) { 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='" + sheet_no + "'"; var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count == 0) { throw new Exception("单据不存在[" + sheet_no + "]"); } else { var row = tb.Rows[0]; if (row["approve_flag"].ToString() == "1") { throw new Exception("单据已审核[" + sheet_no + "]"); } if (Helper.Conv.ToDateTime(row["update_time"]).ToString("yyyy-MM-dd HH:mm:ss") != update_time.ToString("yyyy-MM-dd HH:mm:ss")) { throw new Exception("单据已被他人修改[" + sheet_no + "]"); } } sql = "delete from ic_t_check_detail where sheet_no='" + sheet_no + "'"; d.ExecuteScalar(sql, null); sql = "delete from ic_t_check_master where sheet_no='" + sheet_no + "'"; d.ExecuteScalar(sql, null); // db.CommitTran(); } catch (Exception ex) { Log.writeLog("CheckBLL.DeleteCheckSheet()", ex.ToString(), sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }
public bool ExistField(string tb, string field) { try { string sql = "select 1 from information_schema.columns where table_schema=DATABASE() AND table_name='" + tb + "' AND column_name='" + field + "' "; DB.IDB db = this; var dt = db.ExecuteToTable(sql, null); if (dt.Rows.Count > 0) { return(true); } return(false); } catch (Exception ex) { throw ex; } }
void IBLL.ICashOrder.Change(Model.bank_t_cash_master ord) { string sql = "select * from bank_t_cash_master where sheet_no='" + ord.sheet_no + "'"; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // 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); } } sql = "delete from bank_t_cash_master where sheet_no='" + ord.sheet_no + "'"; d.ExecuteScalar(sql, null); // d.Insert(ord); // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); LogHelper.writeLog("", ex.ToString()); throw; } finally { db.Close(); } }
void IBLL.ICusFY.Delete(string sheet_no) { string sql = "select * from rp_t_supcust_fy_master where sheet_no='" + sheet_no + "'"; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count == 0) { throw new Exception("单据不存在" + sheet_no); } else { var row = tb.Rows[0]; if (row["approve_flag"].ToString() == "1") { throw new Exception("单据已审核" + sheet_no); } } sql = "delete from rp_t_supcust_fy_detail where sheet_no='" + sheet_no + "'"; d.ExecuteScalar(sql, null); sql = "delete from rp_t_supcust_fy_master where sheet_no='" + sheet_no + "'"; d.ExecuteScalar(sql, null); // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); throw; } finally { db.Close(); } }
public bool ExistTable(string tb) { try { string sql = "select table_name from information_schema.tables where table_name ='" + tb + "' "; DB.IDB db = this; var dt = db.ExecuteToTable(sql, null); if (dt.Rows.Count > 0) { return(true); } return(false); } catch (Exception ex) { throw ex; } }
void IBLL.ICGRK.InsertOrder(Model.ic_t_inout_store_master ord, List <Model.ic_t_inout_store_detail> items) { var db = new DB.SQLiteByHandClose(Program.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // string sql = "select 1 from ic_t_inout_store_master where sheet_no='" + ord.sheet_no + "' limit 0,1 "; var dt = d.ExecuteToTable(sql, null); if (dt.Rows.Count > 0) { throw new Exception("已经存在单据号:" + ord.sheet_no); } // foreach (Model.ic_t_inout_store_detail item in items) { sql = "insert into ic_t_inout_store_detail values('" + item.sheet_no + "','" + item.item_no + "','" + item.item_subno + "','" + item.item_name + "','" + item.unit_no + "','" + item.in_qty + "','" + item.orgi_price + "'"; sql += ",'" + item.valid_price + "','" + item.cost_price + "','" + item.valid_date + "','" + item.barcode + "','" + item.sheet_sort + "','" + item.other3 + "') "; d.ExecuteScalar(sql, null); } // sql = "insert into ic_t_inout_store_master values('" + ord.sheet_no + "','" + ord.trans_no + "','" + ord.branch_no + "','" + ord.supcust_no + "','" + ord.total_amount + "'"; sql += ",'" + ord.inout_amount + "','" + ord.approve_flag + "','" + ord.oper_date.ToString("yyyy-MM-dd HH:mm:ss") + "','" + ord.oper_id + "','0','" + ord.pay_way + "') "; d.ExecuteScalar(sql, null); // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); Log.writeLog("CGRK.InsertOrder()", ex.ToString(), ord.sheet_no); throw ex; } finally { db.Close(); } }
void IBLL.ICusSettle.Add(Model.rp_t_recpay_record_info ord, List <Model.rp_t_recpay_record_detail> lines, out string sheet_no) { IBLL.ICusSettle ins = this; // var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); ord.sheet_no = sheet_no = ins.MaxCode(); // string sql = "select * from rp_t_recpay_record_info where sheet_no='" + ord.sheet_no + "'"; var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count != 0) { throw new Exception("已存在单号" + ord.sheet_no); } d.Insert(ord); foreach (Model.rp_t_recpay_record_detail line in lines) { sql = "select isnull(max(flow_id)+1,1) from rp_t_recpay_record_detail"; line.sheet_no = sheet_no; line.flow_no = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null)); d.Insert(line); } // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); throw; } finally { db.Close(); } }
void IBLL.ICusPriceOrder.Add(Model.pm_t_flow_main ord, List <Model.pm_t_price_flow_detial> lines, out string sheet_no) { IBLL.ICusPriceOrder ins = this; ord.sheet_no = ins.MaxCode(); // string sql = "select * from pm_t_flow_main where sheet_no='" + ord.sheet_no + "'"; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count != 0) { throw new Exception("已存在单号" + ord.sheet_no); } d.Insert(ord); foreach (Model.pm_t_price_flow_detial line in lines) { line.sheet_no = ord.sheet_no; d.Insert(line); } // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); throw; } finally { db.Close(); } // sheet_no = ord.sheet_no; }
void IBLL.IFYOrder.Add(Model.bank_t_cash_master ord, List <Model.bank_t_cash_detail> lines, out string sheet_no) { var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; IBLL.IFYOrder order = this; try { db.Open(); db.BeginTran(); // sheet_no = ord.sheet_no = order.MaxCode(); string sql = "select * from bank_t_cash_master where sheet_no='" + ord.sheet_no + "'"; var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count != 0) { throw new Exception("已存在单号" + ord.sheet_no); } d.Insert(ord); foreach (Model.bank_t_cash_detail line in lines) { sql = "select isnull(max(flow_id)+1,1) from bank_t_cash_detail"; line.flow_id = Helper.Conv.ToInt64(d.ExecuteScalar(sql, null)); line.sheet_no = sheet_no; d.Insert(line); } // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); throw; } finally { db.Close(); } }
void IBLL.ICashOrder.Add(Model.bank_t_cash_master ord, out string sheet_no) { IBLL.ICashOrder ins = this; ord.sheet_no = ins.MaxCode(); // string sql = "select * from bank_t_cash_master where sheet_no='" + ord.sheet_no + "'"; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); // var tb = d.ExecuteToTable(sql, null); if (tb.Rows.Count != 0) { throw new Exception("已存在单号" + ord.sheet_no); } d.Insert(ord); // db.CommitTran(); } catch (Exception ex) { db.RollBackTran(); LogHelper.writeLog("", ex.ToString()); throw; } finally { db.Close(); } sheet_no = ord.sheet_no; }
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(); } }
string Sync_Oper() { DB.DBByAutoClose form_db = new DB.DBByAutoClose(AppSetting.Sync_from_con); DB.DBByAutoClose to_db = new DB.DBByAutoClose(AppSetting.Sync_to_con); DB.IDB form_d = form_db; DB.IDB to_d = to_db; SyncHelper syncHelper; string sql = ""; syncHelper = new SyncHelper("sa_t_operator_i", "oper_id", 0); string table_name = syncHelper.table_name; string key_field = syncHelper.key_field; string sel_field2 = syncHelper.sel_field2; string where_field = syncHelper.where_field; syncHelper.ComTable(form_d, to_d, out List <string> insert_lis, out List <string> update_lis); { //删除 目标库中有的,源库没有的数据 sql = $"select {where_field} keys from {table_name} order by {key_field}"; DataTable del_tb = form_d.ExecuteToTable(sql); if (del_tb.Rows.Count > 0) { syncHelper.DeleteOfTmp(to_d, del_tb); } else { to_d.ExecuteScalar($"delete {table_name}"); } } if (insert_lis.Count > 0) { DataTable tb = new DataTable(); int insert_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(insert_lis.Count / insert_num.ToDecimal()); i++) { string[] array = insert_lis.Skip(i * insert_num).Take(insert_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); foreach (DataRow dr in tb.Rows) { string pwd = dr["oper_pw"].ToString(); if (string.IsNullOrEmpty(pwd)) { dr["oper_pw"] = ""; } else { pwd = Helper.sec.des(pwd); pwd = Helper.MD5.ToMD5(pwd); dr["oper_pw"] = pwd; } } syncHelper.InsertOfTmp(to_d, tb); tb = null; } } if (update_lis.Count > 0) { DataTable tb = new DataTable(); int update_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(update_lis.Count / update_num.ToDecimal()); i++) { string[] array = update_lis.Skip(i * update_num).Take(update_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); foreach (DataRow dr in tb.Rows) { string pwd = dr["oper_pw"].ToString(); if (string.IsNullOrEmpty(pwd)) { dr["oper_pw"] = ""; } else { pwd = Helper.sec.des(pwd); pwd = Helper.MD5.ToMD5(pwd); dr["oper_pw"] = pwd; } } syncHelper.UpdateOfTmp(to_d, tb); tb = null; } } SyncResult result = new SyncResult { flag = true, Message = "* " + String.Format("{0,-31}", table_name) + " | " + String.Format("{0,-30}", insert_lis.Count + update_lis.Count) + " *" }; return(result.Message); }
int ISync.SyncData() { DB.DBByAutoClose form_db = new DB.DBByAutoClose(AppSetting.Sync_from_con); DB.DBByAutoClose to_db = new DB.DBByAutoClose(AppSetting.Sync_to_con); DB.IDB form_d = form_db; DB.IDB to_d = to_db; SyncHelper syncHelper; string sql = ""; { syncHelper = new SyncHelper("sa_t_operator_i", "oper_id", 0); string table_name = syncHelper.table_name; string key_field = syncHelper.key_field; string sel_field2 = syncHelper.sel_field2; string where_field = syncHelper.where_field; syncHelper.ComTable(form_d, to_d, out List <string> insert_lis, out List <string> update_lis); //删除 目标库中有的,源库没有的数据 sql = $"select {sel_field2} keys from {table_name} order by {key_field}"; List <string> del_lis = form_d.ExecuteToList <string>(sql, "keys"); if (del_lis.Count > 0) { to_d.ExecuteScalar($"delete {table_name} where ({where_field}) not in ({string.Join(",", del_lis.ToArray())})"); } if (insert_lis.Count > 0) { DataTable tb = new DataTable(); int insert_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(insert_lis.Count / insert_num.ToDecimal()); i++) { string[] array = insert_lis.Skip(i * insert_num).Take(insert_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); foreach (DataRow dr in tb.Rows) { string pwd = dr["oper_pw"].ToString(); if (string.IsNullOrEmpty(pwd)) { dr["oper_pw"] = ""; } else { pwd = Helper.sec.des(pwd); pwd = Helper.MD5.ToMD5(pwd); dr["oper_pw"] = pwd; } } syncHelper.InsertOfTmp(to_d, tb); tb = null; } } if (update_lis.Count > 0) { DataTable tb = new DataTable(); int update_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(update_lis.Count / update_num.ToDecimal()); i++) { string[] array = update_lis.Skip(i * update_num).Take(update_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); foreach (DataRow dr in tb.Rows) { string pwd = dr["oper_pw"].ToString(); if (string.IsNullOrEmpty(pwd)) { dr["oper_pw"] = ""; } else { pwd = Helper.sec.des(pwd); pwd = Helper.MD5.ToMD5(pwd); dr["oper_pw"] = pwd; } } syncHelper.UpdateOfTmp(to_d, tb); tb = null; } } return(0); } }
string Sync_SaleOrderCur() { DB.DBByAutoClose form_db = new DB.DBByAutoClose(AppSetting.Sync_from_con); DB.DBByAutoClose to_db = new DB.DBByAutoClose(AppSetting.Sync_to_con); DB.IDB form_d = form_db; DB.IDB to_d = to_db; SyncHelper syncHelper; string sql = ""; { sql = "select top 1 settle_time from sys_t_daily_settle_log where settle_status='0' order by settle_time desc"; string time = form_d.ExecuteScalar(sql).ToString(); to_d.ExecuteScalar($"delete from sm_t_sale_order_cur where create_time<='{time}'"); } syncHelper = new SyncHelper("sm_t_sale_order_cur", "flow_id,sheet_no", 0); string table_name = syncHelper.table_name; string key_field = syncHelper.key_field; string sel_field2 = syncHelper.sel_field2; string where_field = syncHelper.where_field; syncHelper.ComTable(form_d, to_d, out List <string> insert_lis, out List <string> update_lis); { //删除 目标库中有的,源库没有的数据 sql = $"select {where_field} keys from {table_name} order by {key_field}"; DataTable del_tb = form_d.ExecuteToTable(sql); if (del_tb.Rows.Count > 0) { syncHelper.DeleteOfTmp(to_d, del_tb); } else { to_d.ExecuteScalar($"delete {table_name}"); } } if (insert_lis.Count > 0) { DataTable tb = new DataTable(); int insert_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(insert_lis.Count / insert_num.ToDecimal()); i++) { string[] array = insert_lis.Skip(i * insert_num).Take(insert_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); syncHelper.InsertOfTmp(to_d, tb); tb = null; } } if (update_lis.Count > 0) { DataTable tb = new DataTable(); int update_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(update_lis.Count / update_num.ToDecimal()); i++) { string[] array = update_lis.Skip(i * update_num).Take(update_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); syncHelper.UpdateOfTmp(to_d, tb); tb = null; } } SyncResult result = new SyncResult { flag = true, Message = "* " + String.Format("{0,-31}", table_name) + " | " + String.Format("{0,-30}", insert_lis.Count + update_lis.Count) + " *" }; return(result.Message); }
public void AddChectInitSheet(global::Model.ic_t_check_init check_init, out string sheet_no) { sheet_no = ""; var db = new DB.DBByHandClose(Appsetting.conn); DB.IDB d = db; try { db.Open(); db.BeginTran(); string sql = $@"SELECT COUNT(1) FROM dbo.ic_t_check_init WHERE branch_no='{check_init.branch_no}' AND check_status='0'"; int count = d.ExecuteScalar(sql, null).ToInt32(); if (count > 0) { throw new Exception("该仓库存在未结束盘点单,不可创建新盘点单"); } check_init.sheet_no = sheet_no = MaxCode(d, "PC"); check_init.create_time = DateTime.Now; check_init.update_time = DateTime.Now; d.Insert(check_init); //备份仓库 DataTable tb = d.ExecuteToTable(@"select * from ic_t_branch_stock s left join bi_t_item_info i on i.item_no=s.item_no where branch_no='" + check_init.branch_no + "'", null); foreach (DataRow dr in tb.Rows) { ic_t_check_bak bak = new ic_t_check_bak() { sheet_no = check_init.sheet_no, branch_no = check_init.branch_no, item_no = dr["item_no"].ToString(), stock_qty = Conv.ToDecimal(dr["stock_qty"]), cost_price = Conv.ToDecimal(dr["cost_price"]), price = Conv.ToDecimal(dr["last_price"]), sale_price = Conv.ToDecimal(dr["sale_price"]), create_time = DateTime.Now, update_time = DateTime.Now }; d.Insert(bak); } db.CommitTran(); } catch (Exception ex) { Log.writeLog("CheckBLL.AddChectInitSheet()", ex.ToString(), check_init.sheet_no); db.RollBackTran(); throw ex; } finally { db.Close(); } }
/* public int SyncData() * { * DB.DBByHandClose form_db = new DB.DBByHandClose(AppSetting.Sync_from_con); * DB.DBByHandClose to_db = new DB.DBByHandClose(AppSetting.Sync_to_con); * DB.IDB form_d = form_db; * DB.IDB to_d = to_db; * try * { * form_db.Open(); * to_db.Open(); * * string sql = ""; * ComTable(form_d, to_d, out List<string> insert_lis, out List<string> update_lis); * * //删除 目标库中有的,源库没有的数据 * sql = $"select {sel_field2} keys from {table_name} order by {key_field}"; * List<string> del_lis = form_d.ExecuteToList<string>(sql, "keys"); * if (del_lis.Count > 0) * to_d.ExecuteScalar($"delete {table_name} where ({where_field}) not in ({string.Join(",", del_lis.ToArray())})"); * * if (insert_lis.Count > 0) * { * DataTable tb = new DataTable(); * int insert_num = AppSetting.Sync_count; * * for (int i = 0; i < Math.Ceiling(insert_lis.Count / insert_num.ToDecimal()); i++) * { * string[] array = insert_lis.Skip(i * insert_num).Take(insert_num).ToArray(); * sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; * * tb = form_d.ExecuteToTable(sql); * tb.TableName = table_name; * * GenInsertSql(to_d, tb); * * tb = null; * } * } * * if (update_lis.Count > 0) * { * DataTable tb = new DataTable(); * int update_num = AppSetting.Sync_count; * * for (int i = 0; i < Math.Ceiling(update_lis.Count / update_num.ToDecimal()); i++) * { * string[] array = update_lis.Skip(i * update_num).Take(update_num).ToArray(); * sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; * * tb = form_d.ExecuteToTable(sql); * * GenUpdateSql(to_d, tb); * * * tb = null; * } * } * * return insert_lis.Count; * } * catch (Exception) * { * throw; * } * finally * { * form_db.Close(); * to_db.Close(); * } * }*/ public int SyncDataOfTmp() { DB.DBByAutoClose form_db = new DB.DBByAutoClose(AppSetting.Sync_from_con); DB.DBByAutoClose to_db = new DB.DBByAutoClose(AppSetting.Sync_to_con); DB.IDB form_d = form_db; DB.IDB to_d = to_db; try { string sql = ""; ComTable(form_d, to_d, out List <string> insert_lis, out List <string> update_lis); { //删除 目标库中有的,源库没有的数据 sql = $"select {where_field} keys from {table_name} order by {key_field}"; DataTable del_tb = form_d.ExecuteToTable(sql); if (del_tb.Rows.Count > 0) { DeleteOfTmp(to_d, del_tb); } else { to_d.ExecuteScalar($"delete {table_name}"); } } if (insert_lis.Count > 0) { DataTable tb = new DataTable(); int insert_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(insert_lis.Count / insert_num.ToDecimal()); i++) { string[] array = insert_lis.Skip(i * insert_num).Take(insert_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); InsertOfTmp(to_d, tb); tb = null; } } if (update_lis.Count > 0) { DataTable tb = new DataTable(); int update_num = AppSetting.Sync_count; for (int i = 0; i < Math.Ceiling(update_lis.Count / update_num.ToDecimal()); i++) { string[] array = update_lis.Skip(i * update_num).Take(update_num).ToArray(); sql = $" select * from {table_name} where ({where_field}) in ({string.Join(",", array)}) "; tb = form_d.ExecuteToTable(sql); UpdateOfTmp(to_d, tb); tb = null; } } return(insert_lis.Count); } catch (Exception) { throw; } }
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 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(); } // }