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

drop procedure if exists reward_bd_act;

DELIMITER //
create procedure reward_bd_act( _slip_code varchar(6), _target int, _act int)

_procedure: SQL SECURITY INVOKER
BEGIN

Declare _created_on bigint ;
Declare _updated_on bigint ;
Declare _created_id int ;
Declare _hd_id int(11) defauLt 0;
Declare _bd_id int(11) defauLt 0;
Declare _reward_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 _reward_rate decimal(4,2);
Declare _reward_amt decimal(20,4);
Declare _add_msg varchar(49);

Declare _from_buyer_id int;
Declare _to_buyer_id int;
Declare _is_transfer char(1);

Declare _deal_sordbd int(11) default 55; -- 적립금 발생 
Declare _deal_sorder int(11) default 56; -- 적립금 사용 
Declare _deal_reward int(11) default 57; -- 적립금 변경

if _slip_code = 'sordbd' then  -- 적립금 발생 
	select sobd.id, sorder_date, date_format(date_add(_sorder_date, interval 1 day), '%Y%m%d'),
		branch_id, sohd.user_id, _deal_sordbd, 
		buyer_id, sorder_sum, reward_rate, rdecimal((sorder_sum*reward_rate)/100,0), 
		concat( sorder_no, ' / ', item_code )
	into _bd_id, _sorder_date, _avail_date, _branch_id, _user_id, _deal_type_id,
		_to_buyer_id, _sorder_sum, _reward_rate, _reward_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 = 'reward' then -- sorder_date = reward_date = avail_date 로 한다.
	select id, reward_date, reward_date, branch_id, user_id, deal_type_id, 
		is_transfer, from_buyer_id, to_buyer_id, '0.00', manual_amt, reward_no
	into _reward_id, _sorder_date, _avail_date, _branch_id, _user_id, _deal_type_id,
		_is_transfer, _from_buyer_id, _to_buyer_id, _reward_rate, _reward_amt, _add_msg
	from dbr_reward as rewd
	where rewd.id = _target;

elseif _slip_code = 'sorder' then -- 적립금 사용
	select sohd.id, sorder_date, sorder_date,
		branch_id, sohd.user_id, _deal_sorder, 
		buyer_id, ifnull(sum(sorder_sum), 0),'0.00', reward_use_amt, 
		concat( sorder_no, ' / ', max(item_code) )
	into _hd_id, _sorder_date, _avail_date, _branch_id, _user_id, _deal_type_id,
		_to_buyer_id, _sorder_sum, _reward_rate, _reward_amt, _add_msg
	from dbr_sorder  as sohd
		left join dbr_sorder_bd as sobd on sohd.id = sobd.sorder_id
		left join dbr_item as itm on itm.id = sobd.item_id
	where sohd.id = _target;

end if;

if _reward_amt = '0.0000' then
	leave _procedure;
end if;

if _act = 0 then 

	insert into dbr_reward_bd
		( created_on, hd_id, bd_id, reward_id, occur_date, avail_date, branch_id,
		user_id, deal_type_id, buyer_id, reward_rate, reward_amt,
		add_msg, is_from_buyer )
	values ( unix_timestamp(), _hd_id, _bd_id, _reward_id, _sorder_date, _avail_date, _branch_id,
			_user_id, _deal_type_id, _to_buyer_id, _reward_rate, _reward_amt,
			_add_msg, '0' );

	if _is_transfer = '1' then  -- reward 에서만
		insert into dbr_reward_bd
			( created_on, hd_id, bd_id, reward_id, occur_date, avail_date, branch_id,
			user_id, deal_type_id, buyer_id, reward_rate, reward_amt,
			add_msg, is_from_buyer )
		values ( unix_timestamp(), _hd_id, _bd_id, _reward_id, _sorder_date, _avail_date, _branch_id,
				_user_id, _deal_type_id, _from_buyer_id, _reward_rate, -1*_reward_amt,
				_add_msg, '1' );
	end if;

elseif _act = 1 then

	update dbr_reward_bd
	set updated_on=unix_timestamp(),
		hd_id=_hd_id, bd_id=_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, reward_rate = _reward_rate, reward_amt = _reward_amt,
		add_msg = _add_msg
	where hd_id = _hd_id  and bd_id = _bd_id and is_from_buyer = '0';

	if _is_transfer = '1' then  -- reward 에서만
		update dbr_reward_bd
		set updated_on=unix_timestamp(),
			hd_id=_hd_id, bd_id=_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, reward_rate = _reward_rate, reward_amt = -1*_reward_amt,
			add_msg = _add_msg
		where hd_id = _hd_id  and bd_id = _bd_id and is_from_buyer = '1';
	end if;

elseif _act = -1 then

	delete from dbr_reward_bd
	where hd_id=_hd_id and bd_id= _bd_id and is_from_buyer = '0';

	if _is_transfer = '1' then  -- reward 에서만
		delete from dbr_reward_bd
		where hd_id = _hd_id  and bd_id = _bd_id and is_from_buyer = '1';
	end if;

end if;

END;
//
DELIMITER ;