useful-qry/function-in-db/procedures/point/pl1_reward_bd_ledger_detail.sql

-- truncate table zzz_log;
truncate table dbt_list_type1;
truncate table dbt_list_sum;
drop procedure if exists pl1_reward_bd_ledger_detail;

DELIMITER //
create procedure pl1_reward_bd_ledger_detail( _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, _target int)

SQL SECURITY INVOKER
BEGIN

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

Declare _first_bal decimal(20,4);
Declare _first_bad_bal decimal(20,4);
Declare _first_bal_caption varchar(20);

Declare _first_id int;
Declare _last_id int;

Declare _line_id int;
Declare _line_badrwd_status varchar(64);
Declare _line_in decimal(20,4) default "0.0000";
Declare _line_out decimal(20,4) default "0.0000";
Declare _line_bal decimal(20,4) default "0.0000";
Declare _line_bad_bal decimal(20,4) default "0.0000";
Declare _in_sum decimal(20,4) default "0.0000";
Declare _out_sum decimal(20,4) default "0.0000";

Declare _company_name varchar(64);
Declare _full_name varchar(96);

Declare _item_code varchar(21);
Declare _item_sub_name varchar(128);

Declare _not_found boolean;
Declare _line_cur cursor for -- c5:badrwd_status
    select t_id, d3, d4 from dbt_list_type1 where list_token = _listToken 
	order by order_by asc;
Declare continue handler for 
        not found set _not_found = true;

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;

-- insert for first_bal
select ifnull(last_mbal.bal_amt,0) + ifnull(rwd_bal.reward_amt,0) ,'이월잔액' 
	into _first_bal, _first_bal_caption
	from
		dbr_company as mxv
		left join
		( select buyer_id, bal_amt
		from dbr_bal_reward
		where yyyy_mm = _prev_month(_sdate) and branch_id = _branch
		) as last_mbal
		on mxv.id = last_mbal.buyer_id

		left join
		( select rwd.buyer_id, sum(reward_amt*reward_status) AS reward_amt
		from dbr_reward_bd  as rwd
			inner join dbr_deal_type as deal on deal.id = rwd.deal_type_id
		where avail_date between _first_date(_sbal_date) and _prev_date(_ebal_date)
			and branch_id = _branch
		group by rwd.buyer_id
		) as rwd_bal
		on mxv.id = rwd_bal.buyer_id

where mxv.id = _target;

insert into dbt_list_type1 ( created_on, list_token, c3, d5, order_by)
	values ( unix_timestamp(), _listToken, _first_bal_caption, _first_bal, '19000101');

-- insert for reward_bd
insert into dbt_list_type1
	( created_on, list_token, c1, c2, c3, d1, d2, d3, d4, order_by )
select unix_timestamp(), _listToken, avail_date, deal_code, 
	concat( add_msg,' / ', item_name ), 
	sobd.sorder_qty, sobd.sorder_sum, rwd.reward_amt*cast((reward_status+1)/2 as int), 
	rwd.reward_amt*cast(-1*(reward_status-1)/2 as int),
	concat( avail_date, '-', -1*reward_status,'-', bd_id)
from dbr_reward_bd  as rwd
	inner join dbr_deal_type as deal on deal.id = rwd.deal_type_id
	left join dbr_sorder_bd as sobd on sobd.id = rwd.bd_id
	left join dbr_item as itm on sobd.item_id = itm.id

where avail_date between _sdate and _edate and branch_id = _branch and rwd.buyer_id = _target;

-- compute line_bal
set _line_bal = _first_bal;
set _line_bad_bal = _first_bad_bal;
open _line_cur;
	loop1: loop
		fetch _line_cur into _line_id, _line_in, _line_out;
		if _not_found then leave loop1; end if;

		set _in_sum = _in_sum + ifnull(_line_in,0);
		set _out_sum = _out_sum + ifnull(_line_out,0);
		set _line_bal = ifnull(_line_bal,0) + ifnull(_line_in,0) - ifnull(_line_out,0);

		update dbt_list_type1 set d5 = _line_bal  where t_id = _line_id;
	end loop loop1;
close _line_cur;

-- write list_sum
select company_name, concat( main_contact, ' / ', email ) into _company_name, _full_name from dbr_company where id = _target;
insert into dbt_list_sum
	( created_on, list_token, c1, c2, c3, c4, d1, d2, d3, d4)
	values (unix_timestamp(), _listToken, _company_name, _full_name, _sdate, _edate, 
	_first_bal, _in_sum, _out_sum, _line_bal);

END;
//
DELIMITER ;