useful-qry/function-in-db/procedures/stock-io/stock_io_act.sql

-- truncate table dbr_stock_io;
drop procedure if exists stock_io_act;

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

BEGIN

Declare _created_on bigint ;
Declare _updated_on bigint ;
Declare _slip_bd_id int ;
Declare _slip_no varchar(21) ;
Declare _seq_no tinyint;
Declare _io_date char(8);
Declare _deal_type_id int;
Declare _storage_id int;
Declare _company_id int;
Declare _vat_rate_id int;
Declare _item_id int;
Declare _io_qty decimal(20,4);
Declare _io_prc decimal(20,4);
Declare _io_supply decimal(20,4);
Declare _io_vat decimal(20,4);
Declare _io_sum decimal(20,4);

if _slip_code = 'purch' then
	select pubd.id, purch_no, pubd.seq_no, purch_date, deal_type_id, 
		storage_id, supplier_id, vat_rate_id, item_id, purch_qty,
		porder_prc, purch_supply, purch_vat, purch_sum
	into _slip_bd_id, _slip_no, _seq_no, _io_date, _deal_type_id,
		_storage_id, _company_id, _vat_rate_id, _item_id, _io_qty,
		_io_prc, _io_supply, _io_vat, _io_sum
	from dbr_porder  as pohd
		inner join dbr_porder_bd as pobd on pohd.id = pobd.porder_id
		inner join dbr_purch_bd as pubd on pobd.id = pubd.porder_bd_id
		inner join dbr_purch as puhd on puhd.id = pubd.purch_id
	where pubd.id = _target;

elseif _slip_code = 'sales' then
	select sabd.id, sales_no, sabd.seq_no, sales_date, deal_type_id, 
		storage_id, buyer_id, vat_rate_id, item_id, sales_qty,
		sorder_prc, sales_supply, sales_vat, sales_sum
	into _slip_bd_id, _slip_no, _seq_no, _io_date, _deal_type_id,
		_storage_id, _company_id, _vat_rate_id, _item_id, _io_qty,
		_io_prc, _io_supply, _io_vat, _io_sum
	from dbr_sorder  as sohd
		inner join dbr_sorder_bd as sobd on sohd.id = sobd.sorder_id
		inner join dbr_sales_bd as sabd on sobd.id = sabd.sorder_bd_id
		inner join dbr_sales as sahd on sahd.id = sabd.sales_id
	where sabd.id = _target;

elseif _slip_code = 'genio' then
	select gnhd.id, genio_no, gnbd.seq_no, genio_date, deal_type_id, 
		storage_id, company_id, vat_rate_id, item_id, genio_qty,
		genio_prc, genio_supply, genio_vat, genio_sum
	into _slip_bd_id, _slip_no, _seq_no, _io_date, _deal_type_id,
		_storage_id, _company_id, _vat_rate_id, _item_id, _io_qty,
		_io_prc, _io_supply, _io_vat, _io_sum
	from dbr_genio  as gnhd
		inner join dbr_genio_bd as gnbd on gnhd.id = gnbd.genio_id
	where gnbd.id = _target;
end if;

-- act for stock_io
if _act = 0 then 
	insert into dbr_stock_io
		( created_on, slip_code, slip_bd_id, slip_no, seq_no, io_date, 
			deal_type_id, storage_id, company_id, vat_rate_id, item_id, 
			io_qty, io_prc, io_supply, io_vat, io_sum )
	values ( unix_timestamp(), _slip_code, _slip_bd_id, _slip_no, _seq_no, _io_date, 
			_deal_type_id, _storage_id, _company_id, _vat_rate_id, _item_id, 
			_io_qty, _io_prc, _io_supply, _io_vat, _io_sum );

elseif _act = 1 then
	update dbr_stock_io
	set updated_on=unix_timestamp(),
		slip_no=_slip_no, seq_no=_seq_no, io_date=_io_date, 
		deal_type_id=_deal_type_id, storage_id=_storage_id, company_id=_company_id, 
		vat_rate_id=_vat_rate_id, item_id=_item_id, 
		io_qty=_io_qty, io_prc=_io_prc, io_supply=_io_supply, 
		io_vat=_io_vat, io_sum= _io_sum
	where  slip_code=_slip_code and slip_bd_id= _target;

elseif _act = -1 then
	delete from dbr_stock_io
	where  slip_code=_slip_code and slip_bd_id= _target;

end if;

END;
//
DELIMITER ;

-- call stock_io_act('purch', 1, 1);