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);
}