void IBLL.ISup.Delete(string supcust_no) { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = ""; // sql = "select top 1 * from ic_t_inout_store_master where trans_no='PI' and supcust_no='" + supcust_no + "' "; var tb = db.ExecuteToTable(sql, null); if (tb.Rows.Count != 0) { throw new Exception("客户已被单据引用,不能删除!"); } // sql = "select top 1 * from ic_t_inout_store_master where trans_no='F' and supcust_no='" + supcust_no + "' "; tb = db.ExecuteToTable(sql, null); if (tb.Rows.Count != 0) { throw new Exception("客户已被单据引用,不能删除!"); } // sql = "delete from bi_t_supcust_info where supcust_flag='S' and supcust_no='" + supcust_no + "'"; db.ExecuteScalar(sql, null); }
System.Data.DataTable IBLL.ISup.GetList(string region_no, string keyword, int show_stop, int page_index, int page_size, out int total_count) { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = @"select a.*,b.region_name from (select * from bi_t_supcust_info where supcust_flag='S')a left join bi_t_region_info b on a.region_no=b.region_no where 1=1"; if (region_no != "") { sql += " and a.region_no like '" + region_no + "%'"; } if (keyword != "") { sql += " and (a.supcust_no like '%@%' or sup_name like '%@%')".Replace("@", keyword); } if (show_stop != 1) { sql += " and a.display_flag='1'"; } var tb = db.ExecuteToTable(sql, "a.supcust_no", null, page_size, page_index, out total_count); return(tb); }
System.Data.DataTable IBLL.ISup.GetItem(string supcust_no) { string sql = "select * from bi_t_supcust_info where supcust_flag='S' and supcust_no='" + supcust_no + "'"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); return(db.ExecuteToTable(sql, null)); }
DataTable IPriceBLL.GetSupPriceList(string sup_id) { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = "select sup_name from bi_t_supcust_info where supcust_no=@sup_id and supcust_flag='S' "; var pars = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@sup_id", sup_id) }; var dt = db.ExecuteToTable(sql, pars); if (dt.Rows.Count <= 0) { throw new Exception("货商不存在[" + sup_id + "]"); } sql = "select a.*,'" + dt.Rows[0]["sup_name"].ToString() + "' as sup_name,b.item_name,b.item_subno,b.item_subname,b.item_clsno,b.unit_no,b.barcode ,b.base_price "; sql += "from(select * from bi_t_sup_item where sup_id=@sup_id) a "; sql += "left join bi_t_item_info b on a.item_no=b.item_no "; sql += "order by a.update_time desc "; pars = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@sup_id", sup_id) }; dt = db.ExecuteToTable(sql, pars); return(dt); }
string IBLL.ISupFY.MaxCode() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string front_str = "BM"; string sql = "select sheet_value from sys_t_sheet_no where sheet_id='" + front_str + "'"; object obj = db.ExecuteScalar(sql, null); if (obj == null || obj == DBNull.Value) { return(""); } else { int index = 0; int.TryParse(obj.ToString(), out index); index += 1; if (index > 9999) { index = 0; } sql = "update sys_t_sheet_no set sheet_value=" + index + " where sheet_id='" + front_str + "'"; db.ExecuteScalar(sql, null); return(front_str + "00" + System.DateTime.Now.ToString("yyMMdd") + index.ToString().PadLeft(4, '0')); } }
System.Data.DataTable IBLL.ISupFY.GetList(DateTime date1, DateTime date2, string sup_no) { string sql = @"select a.sheet_no,a.supcust_no as sup_no, cus.sup_name as sup_name,a.supcust_flag,a.pay_type, a.pay_date,a.old_no,a.oper_id,oper.oper_name, (a.oper_id +'/'+ oper.oper_name) as oper_id_a, a.oper_date,a.approve_flag, a.approve_man,man.oper_name as approve_man_name, (a.approve_man+'/'+man.oper_name) as approve_man_a, a.approve_date,a.is_payed,a.branch_no, branch.branch_name,(a.branch_no +'/'+ branch.branch_name) as branch_no_a, a.sale_man,people.oper_name as sale_man_name,(a.sale_man +'/'+people.oper_name) as sale_man_a, a.other1,a.other2,a.other3,a.num1,a.num2,a.num3, a.visa_id,bank.visa_nm as visa_name,(a.visa_id+'/'+bank.visa_nm) as visa_id_a, a.is_over,a.total_amount,a.paid_amount,a.pay_way,a.pay_name from rp_t_supcust_fy_master a left join (select * from bi_t_supcust_info where supcust_flag='C') cus on a.supcust_no=cus.supcust_no left join (select * from sa_t_operator_i) oper on a.oper_id=oper.oper_id left join (select * from sa_t_operator_i) man on a.approve_man=man.oper_id left join (select * from bi_t_branch_info where len(branch_no)=4) branch on a.branch_no=branch.branch_no left join bi_t_people_info people on a.sale_man=people.oper_id left join bi_t_bank_info bank on a.visa_id=bank.visa_id" + " where sheet_no like 'BM%'" + " and a.oper_date>='" + date1.ToString("yyyy-MM-dd") + " 00:00:00.000'" + " and a.oper_date<='" + date2.ToString("yyyy-MM-dd") + " 23:59:59.999'"; if (sup_no != "") { sql += " and a.supcust_no='" + sup_no + "'"; } DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
System.Data.DataTable IBLL.IPayment.GetList() { string sql = "select a.*,(a.visa_id+'/'+b.visa_nm) as visa_id_a,b.visa_nm as visa_name from bi_t_payment_info a left join bi_t_bank_info b on a.visa_id=b.visa_id order by a.pay_way"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); return(db.ExecuteToTable(sql, null)); }
System.Data.DataTable IBLL.IPayment.GetItem(string pay_way) { string sql = "select * from bi_t_payment_info where pay_way='" + pay_way + "'"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); return(db.ExecuteToTable(sql, null)); }
string IBLL.IOper.GetMaxCode() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = "select isnull(max(oper_id) ,0)+1 from sa_t_operator_i "; var code = db.ExecuteScalar(sql, null).ToString(); return(code); }
public DataTable GetOpers() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = "select a.*,b.type_name from sa_t_operator_i a left join sa_t_oper_type b on a.oper_type=b.oper_type"; DataTable tb = db.ExecuteToTable(sql, null); return(tb); }
string GetMaxTypeMode() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = " select isnull(max(oper_type) ,0)+1 from sa_t_oper_type "; var code = db.ExecuteScalar(sql, null).ToString(); return(code); }
public void ResetPWD(string oper_id, string new_pwd) { Helper.sec sec = new Helper.sec(); DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = "update sa_t_operator_i set oper_pw='" + sec.des(new_pwd) + "' where oper_id='" + oper_id + "'"; db.ExecuteScalar(sql, null); }
public DataTable GetJH() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable("select * from netsetup ", null); return(tb); }
public System.Data.DataTable GetOperType() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = "select * from sa_t_oper_type "; DataTable tb = db.ExecuteToTable(sql, null); return(tb); }
public DataTable GetICOutDetail(DateTime start_time, DateTime end_time, string branch_no, string barcode, string item_name, string sheet_no, string item_clsno, int page_index, int page_size, out int total_count) { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = @"SELECT a.sheet_no 单号, a.voucher_no 原始单号, b.item_subno 货号, a.item_name 商品名称, a.barcode 条码, (c.branch_no+'/'+bi.branch_name) 仓库, (c.supcust_no+'/'+s.sup_name) 供应商, (c.oper_id+'/'+o1.oper_name) 操作人, (c.approve_man+'/'+o2.oper_name) 审核人, c.oper_date 操作时间, c.approve_date 审核时间, isnull(a.in_qty,0) 数量, isnull(a.in_qty*a.orgi_price,0.00) 金额 FROM ic_t_inout_store_detail a left join ic_t_inout_store_master c on a.sheet_no=c.sheet_no left join bi_t_item_info b on a.item_no = b.item_no left join bi_t_branch_info bi on bi.branch_no=c.branch_no left join bi_t_item_cls ic on ic.item_clsno=b.item_clsno left join bi_t_supcust_info s on s.supcust_no=c.supcust_no and s.supcust_flag='S' left join sa_t_operator_i o1 on o1.oper_id=c.oper_id left join sa_t_operator_i o2 on o2.oper_id=c.approve_man WHERE c.trans_no in ('01','03') AND c.approve_flag = '1' AND Convert(varchar(10), c.approve_date ,20 ) BETWEEN '" + start_time.ToString("yyyy-MM-dd") + "' AND '"+ end_time.ToString("yyyy-MM-dd") + "'"; if (!string.IsNullOrEmpty(branch_no)) { sql += " AND c.branch_no = '" + branch_no + "' "; } if (!string.IsNullOrEmpty(barcode)) { sql += " AND b.barcode like '%" + barcode + "%' "; } if (!string.IsNullOrEmpty(item_name)) { sql += " and b.item_name like '%" + item_name + "%' "; } if (!string.IsNullOrEmpty(sheet_no)) { sql += " and c.sheet_no like '%" + sheet_no + "%'"; } if (!string.IsNullOrEmpty(item_clsno)) { sql += "and b.item_clsno like '" + item_clsno + "%'"; } DataTable tb = db.ExecuteToTable(sql, "a.sheet_no", null, page_size, page_index, out total_count); return(tb); }
System.Data.DataTable IBLL.IBranch.GetListByParCode(string par_code) { string sql = "select * from bi_t_branch_info where branch_no like '" + par_code + "%' order by branch_no"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
System.Data.DataTable IBLL.IBank.GetList() { string sql = "select * from bi_t_bank_info order by visa_id"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
System.Data.DataTable IBLL.IRegion.GetList() { string sql = "select * from bi_t_region_info order by region_no"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
public DataTable GetCheckPlan(DateTime start_time, DateTime end_time, string branch_no, int page_index, int page_size, out int total_count) { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = @"select b.sheet_no 盘点批次, (f.branch_no+'/'+branch.branch_name) 仓库, (i.item_clsno+'/'+c.item_clsname) 商品类别, (i.oper_id+'/'+oi.oper_name) 操作员, case when i.approve_flag='0' then '未审核' else '已审核' end 审核情况, case when i.check_status='0' then '开始盘点' else '盘点结束' end 盘点情况, i.begin_date 开始时间, i.end_date 结束时间, sum(case when f.real_qty=b.stock_qty then 1 else 0 end) 盘对品种, sum(case when f.real_qty=b.stock_qty then f.real_qty else 0 end) 盘对数量, sum(case when f.real_qty>b.stock_qty then 1 else 0 end) 盘盈品种, sum(case when f.real_qty>b.stock_qty then f.real_qty-b.stock_qty else 0 end) 盘盈数量, sum(case when f.real_qty<b.stock_qty then 1 else 0 end) 盘亏品种, sum(case when f.real_qty<b.stock_qty then b.stock_qty-f.real_qty else 0 end) 盘亏数量, f1.多盘品种, f1.多盘数量, sum(case when f.item_no is NULL then 1 else 0 end) 未盘品种 from ic_t_check_bak b left join ic_t_check_finish f on f.sheet_no=b.sheet_no and f.item_no=b.item_no left join (select f.sheet_no,count(1) 多盘品种,sum(f.real_qty) 多盘数量 from ic_t_check_bak b RIGHT JOIN ic_t_check_finish f on f.sheet_no = b.sheet_no and f.item_no=b.item_no where b.sheet_no is null group by f.sheet_no ) f1 on f1.sheet_no=b.sheet_no left join ic_t_check_init i on i.sheet_no=b.sheet_no left join bi_t_branch_info branch on branch.branch_no=b.branch_no left join bi_t_item_cls c on c.item_clsno=i.item_clsno and c.item_flag='0' left join sa_t_operator_i oi on oi.oper_id=i.oper_id where i.check_status!='2' and Convert(varchar(10), i.create_time ,20 ) BETWEEN '" + start_time.ToString("yyyy-MM-dd") + "' and '" + end_time.ToString("yyyy-MM-dd") + "' "; if (!string.IsNullOrEmpty(branch_no)) { sql += " and i.branch_no='" + branch_no + "' "; } sql += @" group by b.sheet_no , (f.branch_no+'/'+branch.branch_name) , (i.item_clsno+'/'+c.item_clsname) , (i.oper_id+'/'+oi.oper_name) , i.approve_flag, i.check_status, i.begin_date , i.end_date , f1.多盘品种, f1.多盘数量 "; DataTable tb = db.ExecuteToTable(sql, "b.sheet_no", null, page_size, page_index, out total_count); return(tb); }
public DataTable GetSheetPayInfo(DateTime start_time, DateTime end_time, string trans_no, string supcust_no, string sheet_no, string type, int page_index, int page_size, out int total_count) { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = @"SELECT a.voucher_no 单号, a.trans_no 单据类型, a.sheet_amount 单据金额, a.paid_amount 已付款金额, a.pay_date 限收日期, (s.supcust_no+'/'+s.sup_name )客户, a.free_money 免付金额, ( a.sheet_amount - a.paid_amount - a.free_money ) 尚欠金额 FROM rp_t_accout_payrec_flow a LEFT OUTER JOIN sm_t_salesheet b ON a.voucher_no = b.sheet_no LEFT JOIN bi_t_supcust_info s ON s.supcust_no = a.supcust_no and s.supcust_flag='C' where Convert(varchar(10), a.pay_date ,20 ) BETWEEN '" + start_time.ToString("yyyy-MM-dd") + "' and '" + end_time.ToString("yyyy-MM-dd") + "' "; if (!string.IsNullOrEmpty(supcust_no)) { sql += " and a.supcust_no='" + supcust_no + "' "; } if (!string.IsNullOrEmpty(sheet_no)) { sql += " and a.voucher_no like '%" + sheet_no + "%' "; } if (!string.IsNullOrEmpty(trans_no)) { sql += " and a.trans_no IN (" + trans_no + ") "; } if (type.Equals("全部")) { sql += " and (1=1) "; } else if (type.Equals("完全没付")) { sql += " and (a.paid_amount + a.free_money = 0) "; } else if (type.Equals("部分付款")) { sql += " and (abs(a.pay_type * a.sheet_amount - a.paid_amount - a.free_money) >= 0.01) "; } else if (type.Equals("没有付清")) { sql += " and (abs(a.pay_type * a.sheet_amount - a.paid_amount - a.free_money) >= 0.01) "; } else if (type.Equals("完全付清")) { sql += " and (abs(a.pay_type * a.sheet_amount - a.paid_amount - a.free_money) <= 0.01) "; } DataTable tb = db.ExecuteToTable(sql, "a.voucher_no", null, page_size, page_index, out total_count); return(tb); }
System.Data.DataTable IBLL.IPeople.GetItem(string oper_id) { string sql = "select * from bi_t_people_info where oper_id='" + oper_id + "'"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
public System.Data.DataTable GetList() { string sql = "select * from bi_t_item_cls where item_flag='0'"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
System.Data.DataTable IBLL.IBank.GetItem(string visa_id) { string sql = "select * from bi_t_bank_info where visa_id='" + visa_id + "'"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
public System.Data.DataTable GetAll() { string sql = "select * from sys_t_system"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
System.Data.DataTable IBLL.IItem.GetItem(string item_no) { string sql = "select * from bi_t_item_info where item_no='" + item_no + "'"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); var tb = db.ExecuteToTable(sql, null); return(tb); }
public DataTable GetRpSupDetail(DateTime start_time, DateTime end_time, string sheet_no, string deal_man, string supcust_no, int page_index, int page_size, out int total_count) { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = @"SELECT a.pay_date 限付日期, a.sheet_no 单号, a.voucher_no 业务单号, a.memo 备注, b.supcust_no 供应商编号, b.total_amount 单据金额, b.oper_id 操作员, b.oper_date 操作日期, b.deal_man 经办人, b.approve_man 审核人, b.approve_date 审核日期, a.sheet_amount - a.paid_amount - a.paid_free AS 本次应付款金额, substring(a.voucher_no, 1, 2) 业务类型, c.sup_name 货商名称, CASE a.path WHEN '-' THEN - 1 ELSE 1 END * a.paid_amount 已付金额, CASE a.path WHEN '-' THEN - 1 ELSE 1 END * a.paid_free 已免付金额 FROM rp_t_recpay_record_info b LEFT OUTER JOIN rp_t_recpay_record_detail a ON b.sheet_no = a.sheet_no, bi_t_supcust_info c WHERE b.supcust_flag = 's' AND c.supcust_no = b.supcust_no and Convert(varchar(10), b.approve_date,20 ) BETWEEN '" + start_time.ToString("yyyy-MM-dd") + "' AND '" + end_time.ToString("yyyy-MM-dd") + "'"; if (!string.IsNullOrEmpty(deal_man)) { sql += " AND (b.deal_man = '" + deal_man + "') "; } if (!string.IsNullOrEmpty(supcust_no)) { sql += " AND (b.supcust_no LIKE '%" + supcust_no + "%')"; } if (!string.IsNullOrEmpty(sheet_no)) { sql += " AND (a.sheet_no LIKE '%" + sheet_no + "%') "; } DataTable tb = db.ExecuteToTable(sql, "a.sheet_no", null, page_size, page_index, out total_count); return(tb); }
void IBLL.ISupSettle.GetOrder(string sheet_no, out System.Data.DataTable tb1, out System.Data.DataTable tb2) { string sql = @" select a.sheet_no,a.supcust_no as sup_no,sup.sup_name, (a.supcust_no+'/'+sup.sup_name) as sup_no_a, a.flag_post,a.total_amount,a.free_money, a.coin_no,a.coin_rate, a.pay_way,payment.pay_name, (a.pay_way+'/'+payment.pay_name) as pay_way_a, a.approve_flag, a.oper_id,oper.oper_name, (a.oper_id+'/'+oper.oper_name) as oper_id_a, a.oper_date, a.deal_man, people.oper_name as deal_man_name, (a.deal_man+'/'+people.oper_name) as deal_man_a, a.approve_man, o2.oper_name AS approve_man_name, a.approve_date, a.other1,a.other2,a.other3, a.visa_id, bank.visa_nm as visa_name, (a.visa_id+'/'+bank.visa_nm) as visa_id_a, a.num1,a.num2,a.num3,a.cm_branch, a.branch_no, branch.branch_name, (a.branch_no+'/'+branch.branch_name) as branch_no_a, a.from_date,a.to_date, a.rc_sheet_no from rp_t_recpay_record_info a left join (select * from bi_t_supcust_info where supcust_flag='S') sup on a.supcust_no=sup.supcust_no left join bi_t_payment_info payment on a.pay_way=payment.pay_way left join sa_t_operator_i oper on a.oper_id=oper.oper_id LEFT JOIN dbo.sa_t_operator_i o2 ON a.approve_man=o2.oper_id left join bi_t_people_info people on a.deal_man=people.oper_id left join bi_t_bank_info bank on a.visa_id=bank.visa_id left join bi_t_branch_info branch on a.branch_no=branch.branch_no"; sql += " where a.sheet_no ='" + sheet_no + "'"; DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); tb1 = db.ExecuteToTable(sql, null); if (tb1.Rows.Count == 0) { throw new Exception("不存在货商结算单" + sheet_no); } // sql = @"select a.flow_no,a.sheet_no,a.voucher_no,left(a.voucher_no,2) as voucher_first,a.sheet_amount, a.paid_amount,a.paid_free,isnull(a.sheet_amount,0)-isnull(a.paid_amount,0)-isnull(a.paid_free,0) as yf_amount, a.pay_amount,a.pay_free, a.memo,a.other1,a.other2,a.num1,a.num2,a.num3, a.pay_date,a.path,a.select_flag,a.voucher_type, a.oper_date,a.voucher_other1,a.voucher_other2,a.order_no from rp_t_recpay_record_detail a"; sql += " where a.sheet_no='" + sheet_no + "'"; sql += " order by a.flow_no"; tb2 = db.ExecuteToTable(sql, null); }
System.Data.DataTable IBLL.IItem.GetListShort() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = "select a.item_no,a.item_subno,a.item_name,a.item_subname,a.item_size,a.unit_no,b.item_clsname from bi_t_item_info a" + " left join (select * from bi_t_item_cls where item_flag='0') b on a.item_clsno=b.item_clsno " + " where display_flag='1' order by a.item_no"; var tb = db.ExecuteToTable(sql, null); return(tb); }
DataTable ISupcustGroup.GetAll() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = @"SELECT * FROM dbo.bi_t_supcust_group ORDER BY SupCust_Flag,SupCust_GroupNo"; var tb = db.ExecuteToTable(sql, null); return(tb); }
DataTable ISupcustGroup.GetSupGroup() { DB.IDB db = new DB.DBByAutoClose(Appsetting.conn); string sql = @"SELECT * FROM dbo.bi_t_supcust_group WHERE SupCust_Flag='2'"; var tb = db.ExecuteToTable(sql, null); return(tb); }