useful-qry/function-in-db/procedures/stock-io/pl1_stock_io_ledger_detail.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), 
_having varchar(386), _orderby varchar(64), 
_branch int, _storage int, _member_company_id int, _lt_filter 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_badstk_status varchar(1);
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 begin_bals
select begin_bal_qty, bad_begin_bal_qty
	into _first_bal, _first_bad_bal
from dbr_bal_item
where yyyy_mm = substring(_sdate, 1, 6) and storage_id = _storage and item_id = _lt_filter;

insert into dbt_list_type1 ( created_on, list_token, c3, d4, d5, order_by)
	values ( unix_timestamp(), _listToken, '이월재고', _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_name, concat(slip_no,' / ', stk.seq_no ), company_name, if(stk.bad_in_qty+stk.bad_out_qty = 0, '0', '1'),
	io_prc, stk.in_qty, stk.out_qty, concat( io_date, '-', slip_no, '-', seq_no)
from dbr_stock_io  as stk
where io_date between _sdate and _edate and storage_id = _storage and stk.item_id = _lt_filter;

-- 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 = _lt_filter;
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 ;