useful-qry/function-in-db/procedures/common/fnc_curr_reward_bal.sql

drop procedure if exists fnc_curr_reward_bal;
DELIMITER //
create procedure fnc_curr_reward_bal( _curr_date varchar(8), _branch int, _target int)

SQL SECURITY INVOKER
BEGIN

Declare _bal_date varchar(8);

Declare _last_amt decimal(20,4);
Declare _sum_amt decimal(20,4);

Declare _avail_bal decimal(20,4);
Declare _tmp_int int;

-- setup 변수들 --
Declare _setup text;
Declare _reserve int;
Declare _min_use int;

if _is_first_date(_curr_date) then
	set _bal_date = '19000101';
else
	set _bal_date = _curr_date;
end if;

-- 아래것 작동됨 ---- https://dololak.tistory.com/257 
-- Json Data 가져오기
SELECT json_extract(setup_json, '$.ReservePoint'), json_extract(setup_json, '$.MinUsePoint') 
	into _reserve, _min_use
	from dbr_setup
	where setup_code = 'user-reward';

-- 전월 잔액
select bal_amt into _last_amt
from dbr_bal_reward
where buyer_id = 10
    and yyyy_mm = _prev_month(_curr_date) and branch_id = _branch;

-- 당월 변동잔액
select sum(reward_amt*reward_status) into _sum_amt
from dbr_reward_bd
    inner join dbr_deal_type as deal on deal.id = deal_type_id
where buyer_id = _target
    and avail_date between _first_date(_bal_date) and _bal_date
    and branch_id = _branch
group by buyer_id;


-- 보관적립금, 최소사용적립금 계산식 --
set _avail_bal = cast( ( (_last_amt - _reserve) / _min_use) as int) * _min_use;
if _avail_bal < 0 then
	set _avail_bal = '0.0000';
end if;

select _last_amt as c1, _avail_bal as c2;

END;
//
DELIMITER ;

call fnc_curr_reward_bal('20230227', 1, 10) ;