useful-qry/function-in-db/procedures/point/credit_bd_act.sql

drop procedure if exists credit_bd_act;

create procedure credit_bd_act( _slip_code varchar(6), _target int, _act int)

SQL SECURITY INVOKER
BEGIN

Declare _created_on bigint ;
Declare _updated_on bigint ;
Declare _created_id int ;
Declare _sorder_bd_id int(11) defauLt 0;
Declare _credit_id int(11) default 0;
Declare _sorder_date varchar(8);
Declare _avail_date varchar(8);
Declare _branch_id int ;
Declare _user_id int ;
Declare _deal_type_id int;

Declare _sorder_sum decimal(20,4);
Declare _credit_amt decimal(20,4);
Declare _add_msg varchar(49);

Declare _from_buyer_id int;
Declare _to_buyer_id int;
Declare _is_transfer varchar(49);

Declare _tgt_sorder_bd_id int;
Declare _tgt_credit_id int;

if _slip_code = 'sordbd' then
    set _tgt_sorder_bd_id = _target;
    set _tgt_credit_id = 0;
	select sobd.id, sorder_date, date_format(date_add(_sorder_date, interval 1 day), '%Y%m%d'),
		branch_id, sohd.user_id, deal_type_id, 
		buyer_id, sorder_sum, rdecimal((sorder_qty*offer_credit),0), 
		concat( sorder_no, ' / ', item_code )
	into _sorder_bd_id, _sorder_date, _avail_date, _branch_id, _user_id, _deal_type_id,
		_to_buyer_id, _sorder_sum, _credit_amt, _add_msg
	from dbr_sorder  as sohd
		inner join dbr_sorder_bd as sobd on sohd.id = sobd.sorder_id
		inner join dbr_item as itm on itm.id = sobd.item_id
	where sobd.id = _target;

elseif _slip_code = 'credit' then -- sorder_date = credit_date = avail_date 로 한다.
    set _tgt_sorder_bd_id = 0;
    set _tgt_credit_id =  _target;
	select id, credit_date, credit_date, branch_id, user_id, deal_type_id, 
		is_transfer, from_buyer_id, to_buyer_id, manual_amt, credit_no
	into _credit_id, _sorder_date, _avail_date, _branch_id, _user_id, _deal_type_id,
		_is_transfer, _from_buyer_id, _to_buyer_id, _credit_amt, _add_msg
	from dbr_credit as crdt
	where crdt.id = _target;

end if;

-- act for stock_io
if _act = 0 then 

	insert into dbr_credit_bd
		( created_on, bd_id, credit_id, occur_date, avail_date, branch_id,
		user_id, deal_type_id, buyer_id, credit_amt,
		add_msg, is_from_buyer )
	values ( unix_timestamp(), _sorder_bd_id, _credit_id, _sorder_date, _avail_date, _branch_id,
			_user_id, _deal_type_id, _to_buyer_id, _credit_amt,
			_add_msg, '0' );

	if _is_transfer = '1' then  -- credit 에서만
		insert into dbr_credit_bd
			( created_on, bd_id, credit_id, occur_date, avail_date, branch_id,
			user_id, deal_type_id, buyer_id, credit_amt,
			add_msg, is_from_buyer )
		values ( unix_timestamp(), _sorder_bd_id, _credit_id, _sorder_date, _avail_date, _branch_id,
				_user_id, _deal_type_id, _from_buyer_id, -1*_credit_amt,
				_add_msg, '1' );
	end if;

elseif _act = 1 then

	update dbr_credit_bd
	set updated_on=unix_timestamp(),
		bd_id=_sorder_bd_id, occur_date=_sorder_date, avail_date=_avail_date, 
		branch_id=_branch_id, user_id = _user_id, deal_type_id = _deal_type_id,
		buyer_id = _to_buyer_id, credit_amt = _credit_amt,
		add_msg = _add_msg
	where credit_id=_tgt_credit_id and bd_id= _tgt_sorder_bd_id and is_from_buyer = '0';


	if _is_transfer = '1' then  -- credit 에서만
		update dbr_credit_bd
		set updated_on=unix_timestamp(),
			bd_id=_sorder_bd_id, occur_date=_sorder_date, avail_date=_avail_date, 
			branch_id=_branch_id, user_id = _user_id, deal_type_id = _deal_type_id,
			buyer_id = _from_buyer_id, credit_amt = -1*_credit_amt,
			add_msg = _add_msg
		where credit_id=_tgt_credit_id and bd_id= _tgt_sorder_bd_id and is_from_buyer = '1';
	end if;

elseif _act = -1 then

	delete from dbr_credit_bd
	where credit_id=_tgt_credit_id and bd_id= _tgt_sorder_bd_id and is_from_buyer = '0';

	if _is_transfer = '1' then  -- credit 에서만
		delete from dbr_credit_bd
		where credit_id=_tgt_credit_id and bd_id= _tgt_sorder_bd_id and is_from_buyer = '1';
	end if;

end if;

END;