useful-qry/function-in-db/procedures/purch/lt1-purch-sum-bal.sql

truncate table dbt_list_sum;
truncate table dbt_list_type1;
drop procedure if exists lt1_purch_sum_bal;

DELIMITER //
create procedure lt1_purch_sum_bal( _listToken varchar(21), _sdate varchar(8), _edate varchar(8), 
_s1 varchar(32), _e1 varchar(32), _s2 varchar(32), _e2 varchar(32), 
_s3 varchar(32), _e3 varchar(32), _s4 varchar(32), _e4 varchar(32), 
_where varchar(386), _having varchar(386), _orderby varchar(64), 
_branch int, _storage int)

SQL SECURITY INVOKER
BEGIN

Declare _sbal_date varchar(8);
Declare _ebal_date varchar(8);

Declare _sbet1 varchar(64);
Declare _ebet1 varchar(64);
Declare _sbet2 varchar(64);
Declare _ebet2 varchar(64);

if _is_first_date(_sdate) then
	set _sbal_date = '19000101';
	set _ebal_date = '19000101';
else
	set _sbal_date = _sdate;
	set _ebal_date = _edate;
end if;

if _s1 != '' and _e1 != '' then
	set _sbet1 = _s1;
	set _ebet1 = _e1;
else
	set _sbet1 = '!';
	set _ebet1 = '힣';
end if;

if _s2 != '' and _e2 != '' then
	set _sbet2 = _s2;
	set _ebet2 = _e2;
else
	set _sbet2 = '!';
	set _ebet2 = '힣';
end if;

-- 전월 잔액 --
insert into dbt_list_sum
	( created_on, list_token, h_id, id, c1, 
		d1, d2, d3, d4, d5, d6, 
		c2, c3, c4, order_by)

select unix_timestamp(), _listToken, mx.id, mx.id, mx.company_name,
	bal_amt, 0, 0, 0, 0, 0,
	cgroup_name, cgroup_code, concat(full_name, ' ', company_no), concat(company_name, company_no)
from 
	( select supplier_id, bal_amt
		from dbr_bal_supplier
		where yyyy_mm = _prev_month(_sdate) and branch_id = _branch
	) as last_mbal 
	inner join dbr_company as mx on mx.id = last_mbal.supplier_id
	inner join dbr_cgroup as cgr on cgroup_id = cgr.id
where cgr.cgroup_code between _sbet1 and _ebet1
	and mx.company_name between _sbet2 and _ebet2
	and company_class = 'BB';

-- 매출/반품 합계 --
insert into dbt_list_sum
	( created_on, list_token, h_id, id, c1, 
		d1, d2, d3, d4, d5, d6, 
		c2, c3, c4, order_by)
select unix_timestamp(), _listToken, cmp.id, cmp.id, company_name,
	0, sum(purch_sum*cast((purch_status+1)/2 as int)), sum(purch_sum*cast(-1*(purch_status-1)/2 as int)), 0, 0, 0,
	cgroup_name, cgroup_code, concat(full_name, ' ', company_no), concat(company_name, company_no)
from 
    ( select id, purch_no, purch_date
        from dbr_purch
        where purch_date between _sdate and _edate
	) as sbhd
	inner join dbr_purch_bd as sbbd on sbhd.id = sbbd.purch_id
	inner join dbr_porder_bd as mnbd on mnbd.id = sbbd.porder_bd_id
	inner join dbr_porder as mnhd on mnhd.id = mnbd.porder_id
	inner join dbr_item as itm on mnbd.item_id = itm.id
	inner join dbr_deal_type as deal on deal.id = mnhd.deal_type_id
    inner join ( select cmp.id, company_name, full_name, company_no, cgroup_name, cgroup_code
        from dbr_company as cmp
            inner join dbr_cgroup as cgr on cgroup_id = cgr.id
        where cgr.cgroup_code between _sbet1 and _ebet1
			and cmp.company_name between _sbet2 and _ebet2
			and company_class = 'BB'
	) as cmp on cmp.id = mnhd.supplier_id 
where branch_id = _branch
group by cmp.id;

-- 입금/입금할인 합계 --
insert into dbt_list_sum
	( created_on, list_token, h_id, id, c1, 
		d1, d2, d3, d4, d5, d6, 
		c2, c3, c4, order_by)
select unix_timestamp(), _listToken, cmp.id, cmp.id, company_name,
	0, 0, 0, sum(slip_amt*cast((collect_status+1)/2 as int)), 
    sum(slip_amt*cast(-1*(collect_status-1)/2 as int)), 0,
	cgroup_name, cgroup_code, concat(full_name, ' ', company_no), concat(company_name, company_no)
from 
    ( select id, acc_date, acc_slip_no, deal_type_id,
        bill_column1, bill_type, slip_amt, company_id
        from dbr_acc_slip
        where ( deal_type_id = 22 or deal_type_id = 24 ) -- 출금과 출금할인만
            and acc_date between _sdate and _edate
	        and branch_id = _branch
    ) as mnhd 
	inner join dbr_deal_type as deal on deal.id = mnhd.deal_type_id
    inner join ( select cmp.id, company_name, full_name, company_no, cgroup_name, cgroup_code
        from dbr_company as cmp
            inner join dbr_cgroup as cgr on cgroup_id = cgr.id
        where cgr.cgroup_code between _sbet1 and _ebet1
			and cmp.company_name between _sbet2 and _ebet2
			and company_class = 'BB'
	) as cmp on cmp.id = mnhd.company_id 
group by cmp.id;

-- dbt_list_type1 으로 집계 --
insert into dbt_list_type1
	( created_on, list_token, h_id, id, c1, 
		d1, d2, d3, d4, d5, d6, 
		c2, c3, c4, order_by)
select min(unix_timestamp()), min(_listToken), min(id), min(id), min(c1),
	sum(d1),
	sum(d2),
	sum(d3),
	sum(d4),
	sum(d5),
	sum(d1) + sum(d2) + sum(d3) - sum(d4) - sum(d5),
	min(c2), min(c3), min(c4), min(order_by)
from dbt_list_sum as mx
where list_token = _listToken
group by mx.id
having min(mx.id) is not null
order by order_by asc;

END;
//
DELIMITER ;