useful-qry/function-in-db/procedures/purch/pl1_purch_ledger_detail.sql

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

DELIMITER //
create procedure pl1_purch_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_bal_caption varchar(20);

Declare _first_id int;
Declare _last_id int;

Declare _line_id int;
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 _in_sum decimal(20,4) default "0.0000";
Declare _out_sum decimal(20,4) default "0.0000";

Declare _company_name varchar(64);
Declare _full_name varchar(96);

Declare _not_found boolean;
Declare _line_cur cursor for 
    select t_id, d3, d4 from dbt_list_type1 where t_id > _first_id and t_id <= _last_id
	order by c1, c2, c3 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;

-- 전월잔액 
select ifnull(last_mbal.bal_amt,0) , '이월잔액' into _first_bal, _first_bal_caption
from
	dbr_company as mv
	left join
	( select supplier_id, bal_amt
	from dbr_bal_supplier
	where yyyy_mm = _prev_month(_sdate) and branch_id = _branch
	) as last_mbal on mv.id = last_mbal.supplier_id
where mv.id = _target;

insert into dbt_list_type1 ( created_on, list_token, c4, d5 )
	values ( unix_timestamp(), _listToken, _first_bal_caption, _first_bal);

-- insert for purch
insert into dbt_list_type1
	( created_on, list_token, c1, c2, c3, c4, c5, d1, d2, d3)
select unix_timestamp(), _listToken, concat( purch_date,'-', deal_code, '*', sbbd.seq_no ), 
	concat( purch_no, '*', sbbd.seq_no ), deal_code, 
    concat( item_code, ' / ', item_name, ' / ', sub_name ), '', -- bill_type 은 비운다.
 	purch_qty, porder_prc, purch_sum*purch_status
from ( select id, purch_no, purch_date
	from dbr_purch
    where purch_date between _sdate and _edate 
	) as sbhd
	inner join dbr_purch_bd as sbbd on sbhd.id = sbbd.purch_id
	inner join dbr_porder_bd as mnbd on mnbd.id = sbbd.porder_bd_id
	inner join dbr_porder as mnhd on mnhd.id = mnbd.porder_id
	inner join dbr_item as itm on mnbd.item_id = itm.id
	inner join dbr_deal_type as deal on deal.id = mnhd.deal_type_id
where branch_id = _branch and mnhd.supplier_id = _target;

-- 출금내역
insert into dbt_list_type1
	( created_on, list_token, c1, c2, c3, c4, c5, d4)
select unix_timestamp(), _listToken, concat( acc_date,'-', deal_code ), 
	acc_slip_no, deal_code, 
	bill_column1, bill_type, -1*slip_amt*purch_status
from 
    ( select id, acc_date, acc_slip_no, deal_type_id,
        bill_column1, bill_type, slip_amt
        from dbr_acc_slip
        where ( deal_type_id = 22 or deal_type_id = 24 ) -- 출금과 출금할인만
            and acc_date between _sdate and _edate 	
	        and branch_id = _branch and company_id = _target
    ) as mnhd  
inner join dbr_deal_type as deal on deal.id = deal_type_id;

-- -- get id range for current ledger
select min(t_id), max(t_id) into  _first_id, _last_id from dbt_list_type1 where list_token = _listToken;

-- compute line_bal
set _line_bal = _first_bal;
open _line_cur;
	loop1: loop
		fetch _line_cur into _line_id, _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);
		set _line_bal = ifnull(_line_bal,0) + ifnull(_line_in,0) - ifnull(_line_out,0);
		update dbt_list_type1 set d5 = _line_bal where t_id = _line_id;
	end loop loop1;
close _line_cur;

-- write list_sum
select company_name, concat(full_name, ' ', company_no) 
	into _company_name, _full_name from dbr_company where id = _target;
insert into dbt_list_sum
	( created_on, list_token, c1, c2, c3, c4, d1, d2, d3, d4)
	values (unix_timestamp(), _listToken, _company_name, _full_name, _sdate, _edate, 
	_first_bal, _in_sum, _out_sum, _line_bal);

END;
//
DELIMITER ;