useful-qry/function-in-db/procedures/stock-io/pl1_stock_io_ledger_detail-bf-turbo.sql

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

DELIMITER //
create procedure pl1_stock_io_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)

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_badstk_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 _item_code varchar(21);
Declare _item_sub_name varchar(128);

Declare _not_found boolean;
Declare _line_cur cursor for -- c5:badstk_status
    select t_id, c5, d2, d3 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_qty,0) + ifnull(stk_bal.io_qty,0) , 
		ifnull(last_mbal.bad_bal_qty,0) + ifnull(stk_bal.bad_io_qty,0) ,'이월재고' 
	into _first_bal, _first_bad_bal, _first_bal_caption
	from
		dbr_item as mxv
		left join
		( select item_id, bal_qty, bad_bal_qty
		from dbr_bal_item
		where yyyy_mm = _prev_month(_sdate) and storage_id = _storage
		) as last_mbal
		on mxv.id = last_mbal.item_id

		left join
		( select stk.item_id, sum(io_qty*stock_status) AS io_qty, sum(io_qty*badstk_status) AS bad_io_qty
		from dbr_stock_io  as stk
			inner join dbr_deal_type as deal on deal.id = stk.deal_type_id
		where io_date between _first_date(_sbal_date) and _prev_date(_ebal_date)
			and storage_id = _storage
		group by stk.item_id
		) as stk_bal
		on mxv.id = stk_bal.item_id

where mxv.id = _target;

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

-- insert for stock_io
insert into dbt_list_type1
	( created_on, list_token, c1, c2, c3, c4, c5, d1, d2, d3, order_by)
select unix_timestamp(), _listToken, io_date, deal_code, concat(slip_no,' / ', stk.seq_no )slip_no, company_name, badstk_status,
		io_prc, stk.io_qty*cast((stock_status+1)/2 as int), stk.io_qty*cast(-1*(stock_status-1)/2 as int),
		concat( io_date, '-', -1*stock_status,'-', slip_no, '-', seq_no)
from dbr_stock_io  as stk
	inner join dbr_company as cmp on cmp.id = stk.company_id
	inner join dbr_deal_type as deal on deal.id = stk.deal_type_id
where io_date between _sdate and _edate and storage_id = _storage and stk.item_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_badstk_status, _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);

		if _line_badstk_status = '0' then
			set _line_bal = ifnull(_line_bal,0) + ifnull(_line_in,0) - ifnull(_line_out,0);
		else 
			set _line_bad_bal = ifnull(_line_bad_bal,0) + ifnull(_line_in,0) - ifnull(_line_out,0);
		end if;

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

-- write list_sum
select item_code, concat(item_name,' / ',sub_name ) into _item_code, _item_sub_name from dbr_item where id = _target;
insert into dbt_list_sum
	( created_on, list_token, c1, c2, c3, c4, d1, d2, d3, d4)
	values (unix_timestamp(), _listToken, _item_code, _item_sub_name, _sdate, _edate, 
	_first_bal, _in_sum, _out_sum, _line_bal);

END;
//
DELIMITER ;