useful-qry/function-in-db/procedures/stock-io/lt1-stock-io-sum-bal-bf-turbo.sql

truncate table dbt_list_type1;

drop procedure if exists lt1_stock_io_sum_bal;
DELIMITER //
create procedure lt1_stock_io_sum_bal( _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)

BEGIN

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

Declare _sbet1 varchar(64);
Declare _ebet1 varchar(64);
Declare _sbet2 varchar(64);
Declare _ebet2 varchar(64);
Declare _sbet3 varchar(64);
Declare _ebet3 varchar(64);


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;

if _s1 != '' and _e1 != '' then
	set _sbet1 = _s1;
	set _ebet1 = _e1;
else
	set _sbet1 = '!';
	set _ebet1 = '힣';
end if;

if _s2 != '' and _e2 != '' then
	set _sbet2 = _s2;
	set _ebet2 = _e2;
else
	set _sbet2 = '!';
	set _ebet2 = '힣';
end if;


if _s3 != '' and _e3 != '' then
	set _sbet3 = _s3;
	set _ebet3 = _e3;
else
	set _sbet3 = '!';
	set _ebet3 = '힣';
end if;

-- 전월 재고 --
-- bad_qty 는 Delta 만 보여줌.
insert into dbt_list_sum
	( created_on, list_token, h_id, id, c1, c2, c3,
     d1, d2, d3, d4, d5,
     c4, c5, order_by )

select unix_timestamp(), _listToken, mx.id, mx.id, item_code, item_name, sub_name,
     bal_qty, 0, 0, 0, 0,
   	igroup_name, igroup_code, item_code

from ( select id, item_code, item_name, sub_name, igroup_id
		from dbr_item as itm
		where item_code between _sbet2 and _ebet2
            and isnt_stkio = '0' and is_material = '0'
	) as mx
    inner join ( select item_id, bal_qty  
		from dbr_bal_item
		where yyyy_mm = _prev_month(_sdate) and storage_id = _storage
	) as bal on mx.id = bal.item_id

    inner join ( select id, igroup_code, igroup_name
		from dbr_igroup
		where igroup_code between _sbet1 and _ebet1
	) as igr on mx.igroup_id = igr.id

order by item_code asc;

-- 당월 수불 합계 --
insert into dbt_list_sum
	( created_on, list_token, h_id, id, c1, c2, c3,
     d1, d2, d3,
     d4, d5,
     c4, c5, order_by )

select unix_timestamp(), _listToken, mx.id, mx.id, item_code, item_name, sub_name,
     0, sum(io_qty*cast((stock_status+1)/2 as int)), sum(io_qty*cast(-1*(stock_status-1)/2 as int)),
     0, sum(io_qty*badstk_status),
   	igroup_name, igroup_code, item_code

from ( select id, item_code, item_name, sub_name, igroup_id
		from dbr_item as itm
		where item_code between _sbet2 and _ebet2
            and isnt_stkio = '0' and is_material = '0'
	) as mx
    inner join ( 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 io_date between _sdate and _edate
			and storage_id = _storage
	) as stk on mx.id = stk.item_id

    inner join ( select id, igroup_code, igroup_name
		from dbr_igroup
		where igroup_code between _sbet1 and _ebet1
	) as igr on mx.igroup_id = igr.id
group by stk.item_id
order by item_code asc;

-- dbt_list_type1 으로 집계 --
insert into dbt_list_type1
	( created_on, list_token, h_id, id, c1, c2, c3,
		d1, d2, d3, d4, d5,
		c4, c5, order_by)
select min(unix_timestamp()), min(_listToken), min(id), min(id), min(c1), min(c2), min(c3),
	sum(d1),
	sum(d2),
	sum(d3),
    sum(d1) + sum(d2) - sum(d3),
	sum(d5),
	min(c4), min(c5), min(order_by)
from dbt_list_sum as mx
where list_token = _listToken
group by mx.id
having min(mx.id) is not null
order by order_by asc;

END;
//
DELIMITER ;