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

SQL SECURITY INVOKER
BEGIN

Declare _curr_bal decimal(20,4) default "0.0000";
Declare _curr_bad decimal(20,4) default "0.0000";
Declare _sum_stkio decimal(20,4) default "0.0000";
Declare _sum_bad decimal(20,4) default "0.0000";

Declare _bal_date varchar(8);

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


select bal_qty, bad_bal_qty
into _curr_bal, _curr_bad
from dbr_bal_item
where item_id = _target
    and yyyy_mm = _prev_month(_curr_date) and storage_id = _storage;

select sum(io_qty*stock_status), sum(io_qty*badstk_status)
into _sum_stkio, _sum_bad
from ( select item_id, io_qty, stock_status, badstk_status
    from dbr_stock_io  as stk
        inner join dbr_deal_type as deal on deal.id = stk.deal_type_id
    where item_id = _target
        and io_date between _first_date(_bal_date) and _bal_date
        and storage_id = _storage
    ) stkio
group by stkio.item_id;

select _curr_bal + _sum_stkio as c1, _curr_bad + _sum_bad as c2;

END;
//
DELIMITER ;

call fnc_curr_item_bal('20230228', 1, 1) ;







ALTER TABLE `crm_account` CHANGE `account_name` `order_by` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '';
ALTER TABLE `crm_account_item` CHANGE `item_name` `order_by` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '';


select bal_amt
from dbr_bal_reward
where buyer_id = 10
    and yyyy_mm = _prev_month('20230228') and branch_id = 1;


select sum(reward_amt*reward_status)
from dbr_reward_bd  as rwd
    inner join dbr_deal_type as deal on deal.id = deal_type_id
where buyer_id = 10
    and avail_date between '20230201' and '20230228'
    and branch_id = 1
group by buyer_id;