//供应商价格表 DataTable ICommon.GetSupPriceList(string sysn_time, string sup_id, string item_no) { try { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); var condition_sql = ""; if (sup_id != "") { condition_sql += " and sup_id=@sup_id "; } if (item_no != "") { condition_sql += " and item_no=@item_no "; } string sql = "select item_no,sup_id,price,top_price,bottom_price,last_price,spec_from,spec_to,spec_price,item_status"; sql += ",top_sheet_no,bottom_sheet_no,last_sheet_no from bi_t_sup_item where update_time>=@sysn_time " + condition_sql; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time), new MySql.Data.MySqlClient.MySqlParameter("@sup_id", sup_id), new MySql.Data.MySqlClient.MySqlParameter("@item_no", item_no) }; var dt = db.ExecuteToTable(sql, pars); return(dt); } catch (Exception ex) { LogHelper.writeLog("Common.GetSupPriceList()", ex.ToString(), null); throw new Exception(ex.Message); } }
void ICommon.UpdatePwd(string branch_no, string oper_id, string pwd, string new_pwd) { pwd = pwd.ToUpper(); new_pwd = new_pwd.ToUpper(); DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select * from sa_t_operator_i where branch_no=@branch_no and oper_id=@oper_id "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@oper_id", oper_id), new MySql.Data.MySqlClient.MySqlParameter("@branch_no", branch_no) }; var dt = db.ExecuteToTable(sql, pars); if (dt.Rows.Count > 0) { var temp_pwd = dt.Rows[0]["oper_pw"].ToString(); if (temp_pwd.ToUpper() != pwd.ToUpper()) { throw new Exception("旧密码不正确"); } sql = "update sa_t_operator_i set oper_pw=@new_pwd where branch_no=@branch_no and oper_id=@oper_id "; pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@oper_id", oper_id), new MySql.Data.MySqlClient.MySqlParameter("@new_pwd", new_pwd), new MySql.Data.MySqlClient.MySqlParameter("@branch_no", branch_no) }; db.ExecuteScalar(sql, pars); } else { throw new Exception("操作员不存在"); } }
//获取商品更新数目 int ICommon.GetItemCount(string sysn_time) { try { int count = 0; DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select count(item_no) total_count from bi_t_item_info where update_time>=@sysn_time "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time) }; var dt = db.ExecuteToTable(sql, pars); if (dt.Rows.Count > 0) { count = Conv.ToInt(dt.Rows[0]["total_count"]); } return(count); } catch (Exception ex) { LogHelper.writeLog("Common.GetItemCount()", ex.ToString(), sysn_time); throw new Exception(ex.Message); } }
//获取支付方式列表 DataTable ICommon.GetPayWayList() { try { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select * from bt_payment "; var dt = db.ExecuteToTable(sql, null); return(dt); } catch (Exception ex) { LogHelper.writeLog("Common.GetPayWayList()", ex.ToString(), null); throw new Exception(ex.Message); } }
//价格系统参数表 DataTable ICommon.GetSystemPars(string sysn_time) { try { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select * from sys_t_system where 1=1";// "update_time>=@sysn_time "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time) }; var dt = db.ExecuteToTable(sql, pars); return(dt); } catch (Exception ex) { LogHelper.writeLog("Common.GetSystemPars()", ex.ToString(), null); throw new Exception(ex.Message); } }
//获取品类列表 DataTable ICommon.GetItemClsList(string sysn_time) { try { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select item_clsno,item_clsname,item_flag,display_flag "; sql += "from bi_t_item_cls where item_flag='0' and length(item_clsno)=2 and 1=1";//"update_time>=@sysn_time "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time) }; var dt = db.ExecuteToTable(sql, pars); return(dt); } catch (Exception ex) { LogHelper.writeLog("Common.GetItemClsList()", ex.ToString(), sysn_time); throw new Exception(ex.Message); } }
//获取供应商/客户列表 DataTable ICommon.GetSupCusList(string sysn_time) { try { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select supcust_no,supcust_flag,sup_name,display_flag,sup_tel,sup_pyname,credit_amt,other1,cust_level,is_retail "; sql += "from bi_t_supcust_info where update_time>=@sysn_time "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time) }; var dt = db.ExecuteToTable(sql, pars); return(dt); } catch (Exception ex) { LogHelper.writeLog("Common.GetSupCusList()", ex.ToString(), sysn_time); throw new Exception(ex.Message); } }
//获取商品列表 DataTable ICommon.GetItemList(string sysn_time) { try { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select item_no,item_subno,item_subname,item_clsno,item_name,item_brand,item_brandname,unit_no,item_size"; sql += ",product_area,barcode,price,base_price,base_price2,base_price3,0 base_price4,0 base_price5,sale_price,display_flag,item_flag "; sql += "from bi_t_item_info where update_time>=@sysn_time "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time) }; var dt = db.ExecuteToTable(sql, pars); return(dt); } catch (Exception ex) { LogHelper.writeLog("Common.GetItemList()", ex.ToString(), sysn_time); throw new Exception(ex.Message); } }
//获取操作员列表 DataTable ICommon.GetOperList(string sysn_time, string branch_no) { try { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select oper_id,oper_type,oper_name,oper_pw,oper_status,update_time,is_branch,is_admin,branch_no "; sql += "from sa_t_operator_i where 1=1 ";//update_time>=@sysn_time "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time), new MySql.Data.MySqlClient.MySqlParameter("@branch_no", branch_no) }; var dt = db.ExecuteToTable(sql, pars); return(dt); } catch (Exception ex) { LogHelper.writeLog("Common.GetOperList()", ex.ToString(), sysn_time); throw new Exception(ex.Message); } }
//获取商品库存 DataTable ICommon.GetBranchStockList(string sysn_time, string branch_no) { try { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); string sql = "select branch_no,item_no,stock_qty,cost_price,display_flag,last_price,fifo_price "; sql += "from ic_t_branch_stock where branch_no=@branch_no and update_time>=@sysn_time "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@branch_no", branch_no), new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time) }; var dt = db.ExecuteToTable(sql, pars); return(dt); } catch (Exception ex) { LogHelper.writeLog("Common.GetBranchStockList()", ex.ToString(), branch_no); throw new Exception(ex.Message); } }
//客户已用额度 void ICommon.GetBalance(string cus_no, out decimal balance, out decimal credit_amt) { balance = 0; credit_amt = 0; DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); //已用额度 string sql = "select ifnull(sum(a.pay_type*a.sheet_amount - a.pay_type*a.paid_amount - a.pay_type*a.free_money),0) as sum_money "; sql += "from rp_t_accout_payrec_flow a "; sql += "inner join bi_t_supcust_info b on a.supcust_no = b.supcust_no "; sql += "where (a.supcust_flag = 'c') and b.supcust_flag = 'c' and a.supcust_no=cus_no "; sql += "and (a.pay_type * a.sheet_amount <> a.pay_type * (a.paid_amount + a.free_money )) "; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@cus_no", cus_no) }; var dt = db.ExecuteToTable(sql, pars); if (dt != null && dt.Rows.Count > 0) { balance = Conv.ToDecimal(dt.Rows[0]["sum_money"]); } balance = 0 - balance; //信用额度 sql = "select ifnull(credit_amt,0) credit_amt from bi_t_supcust_info where supcust_flag='C' and supcust_no=@cus_no "; pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@cus_no", cus_no) }; dt = db.ExecuteToTable(sql, pars); if (dt.Rows.Count > 0) { credit_amt = Conv.ToDecimal(dt.Rows[0]["credit_amt"]); } }
//供应商价格表 int ICommon.GetSupPriceCount(string sysn_time, string sup_id, string item_no) { try { int count = 0; DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); var condition_sql = ""; if (sup_id != "") { condition_sql += " and sup_id=@sup_id "; } if (item_no != "") { condition_sql += " and item_no=@item_no "; } string sql = "select count(*) total_count from bi_t_sup_item where update_time>=@sysn_time " + condition_sql; var pars = new MySql.Data.MySqlClient.MySqlParameter[] { new MySql.Data.MySqlClient.MySqlParameter("@sysn_time", sysn_time), new MySql.Data.MySqlClient.MySqlParameter("@sup_id", sup_id), new MySql.Data.MySqlClient.MySqlParameter("@item_no", item_no) }; var dt = db.ExecuteToTable(sql, pars); if (dt.Rows.Count > 0) { count = Conv.ToInt(dt.Rows[0]["total_count"]); } return(count); } catch (Exception ex) { LogHelper.writeLog("Common.GetSupPriceCount()", ex.ToString(), null); throw new Exception(ex.Message); } }
//取客户商品价格 decimal ICommon.GetCusItemPrice(string cust_id, string item_no) { DB.IDB db = new DB.MySqlByAutoClose(Appsetting.conn); decimal price = 0; // string sql = "select ifnull(other1,'1') other1,ifnull(cust_level,'1') cust_level "; sql += "from bi_t_supcust_info where supcust_no='" + cust_id + "' and supcust_flag='C' "; var dt1 = db.ExecuteToTable(sql, null); var price_type = "1"; int cust_level = 1; if (dt1.Rows.Count > 0) { price_type = dt1.Rows[0]["other1"].ToString(); cust_level = Conv.ToInt(dt1.Rows[0]["cust_level"]); if (cust_level == 0) { cust_level = 1; } } if (price_type == "2")//最后价格 { sql = "select * from bi_t_cust_price where cust_id='" + cust_id + "' and item_no='" + item_no + "' "; var dt2 = db.ExecuteToTable(sql, null); if (dt2.Rows.Count > 0) { //bi_t_cust_price.last_price price = Conv.ToDecimal(dt2.Rows[0]["last_price"]); if (price > 0) { return(price); } //bi_t_cust_price.new_price price = Conv.ToDecimal(dt2.Rows[0]["new_price"]); if (price > 0) { return(price); } } } else if (price_type == "1")//约定价格 { sql = "select * from sys_t_system where sys_var_id='cust_pricetype' "; var dt3 = db.ExecuteToTable(sql, null); if (dt3.Rows.Count > 0) { var temp_price_type = Conv.ToInt(dt3.Rows[0]["sys_var_value"]); if (temp_price_type == 1) //最后批发价 { sql = "select * from bi_t_cust_price where cust_id='" + cust_id + "' and item_no='" + item_no + "' "; var dt2 = db.ExecuteToTable(sql, null); if (dt2.Rows.Count > 0) { //bi_t_cust_price.last_price price = Conv.ToDecimal(dt2.Rows[0]["last_price"]); if (price > 0) { return(price); } //bi_t_cust_price.new_price price = Conv.ToDecimal(dt2.Rows[0]["new_price"]); if (price > 0) { return(price); } } } else if (temp_price_type == 3)//最低批发价 { sql = "select * from bi_t_cust_price where cust_id='" + cust_id + "' and item_no='" + item_no + "' "; var dt2 = db.ExecuteToTable(sql, null); if (dt2.Rows.Count > 0) { //bi_t_cust_price.last_price price = Conv.ToDecimal(dt2.Rows[0]["bottom_price"]); if (price > 0) { return(price); } //bi_t_cust_price.new_price price = Conv.ToDecimal(dt2.Rows[0]["new_price"]); if (price > 0) { return(price); } } } else if (temp_price_type == 5) //最后进货价 { sql = "select top 1 last_price from bi_t_sup_item where item_no='" + item_no + "'"; sql += "and last_sheet_no=(select max(last_sheet_no) from bi_t_sup_item where item_no='" + item_no + "') "; var dt2 = db.ExecuteToTable(sql, null); if (dt2.Rows.Count > 0) { //bi_t_sup_item.last_price price = Conv.ToDecimal(dt2.Rows[0]["last_price"]); if (price > 0) { return(price); } } } } } //以上价格策略无法取到相应价格时,默认取商品档案约定级别价格 if (1 == 1) { //bi_t_item_info中约定的商品价格 sql = "select base_price,base_price2,base_price3,base_price4,base_price5 "; sql += "from bi_t_item_info where item_no='" + item_no + "' "; var dt3 = db.ExecuteToTable(sql, null); if (dt3.Rows.Count > 0) { //取客户级别价格,如无级别则默认取base_price if (dt3.Columns.Count >= cust_level) { price = Conv.ToDecimal(dt3.Rows[0][cust_level - 1]); if (price > 0) { return(price); } } else { price = Conv.ToDecimal(dt3.Rows[0][0]); if (price > 0) { return(price); } } } } // return(price); }