useful-qry/function-in-db/procedures/point/lt1-credit-bd-sum-bal.sql--

truncate table dbt_list_type1;
drop procedure if exists lt1_credit_bd_sum_bal;

DELIMITER //
create procedure lt1_credit_bd_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);
Declare _sbet3 varchar(64);
Declare _ebet3 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;


if _s3 != '' and _e3 != '' then
	set _sbet3 = _s3;
	set _ebet3 = _e3;
else
	set _sbet3 = '!';
	set _ebet3 = '駾';
end if;

insert into dbt_list_sum
	( created_on, list_token, h_id, id, c1,
	  d1, d2, d3, d4,
	  c2, c3, c4, order_by )

select unix_timestamp(),'aaa', mx.id, mx.id, mx.company_name,
	bal_amt, 0, 0, 0,
	cgroup_name, cgroup_code, concat(company_name, company_no), concat(company_name, company_no)
from
	( select buyer_id, bal_amt
		from dbr_bal_reward
		where yyyy_mm = _prev_month(_sdate) and branch_id = _branch
	) as last_mbal
	inner join dbr_company as mx on mx.id = last_mbal.buyer_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 = 'AA' or company_class = 'AB' );



insert into dbt_list_type1
	( created_on, list_token, id, c1, d1, d2, d3, d4, c2, c3)

select unix_timestamp(), _listToken, mx.id, company_name, bal.last_bal, 
	ifnull(io_sum.in_tot, 0), ifnull(io_sum.out_tot, 0),
	bal.last_bal + ifnull(io_sum.in_tot, 0) - ifnull(io_sum.out_tot, 0) as curr_bal, 
  	cgroup_name, cgroup_code

from
	dbr_company as mx
	inner join dbr_cgroup as cgr on mx.cgroup_id = cgr.id
	left join
	(
		select mxv.id,
			ifnull(last_mbal.bal_amt,0) + ifnull(crd_bal.crd_amt,0) as last_bal
		from dbr_company as mxv
		left join
		( select buyer_id, bal_amt
			from dbr_bal_credit
			where yyyy_mm = _prev_month(_sdate) and branch_id = _branch
		) as last_mbal
		on mxv.id = last_mbal.buyer_id

		left join
		( select crd.buyer_id, sum(credit_amt*credit_status) AS crd_amt
		from dbr_credit_bd  as crd
			inner join dbr_deal_type as deal on deal.id = crd.deal_type_id
		where avail_date between _first_date(_sbal_date) and _prev_date(_ebal_date)
			and branch_id = _branch
		group by crd.buyer_id
		) as crd_bal
		on mxv.id = crd_bal.buyer_id

	) bal

	on mx.id = bal.id

	left join
		( select crd.buyer_id,
			sum(credit_amt*cast((credit_status+1)/2 as int)) as in_tot,
			sum(credit_amt*cast(-1*(credit_status-1)/2 as int)) as out_tot
		from dbr_credit_bd  as crd
			inner join dbr_deal_type as deal on deal.id = crd.deal_type_id
		where avail_date between _sdate and _edate
			and branch_id = _branch
		group by crd.buyer_id
		) as io_sum
		on mx.id = io_sum.buyer_id

where cgr.cgroup_code between _sbet1 and _ebet1
	and mx.company_name between _sbet2 and _ebet2
	and mx.company_class = 'AA' or mx.company_class = 'AB'

order by company_name asc;

END;
//
DELIMITER ;