aonestar.public

Parameters

Name	Type	Mode
i_regiser_id	integer	IN (DEFAULT NULL)
i_booking_id	integer	IN (DEFAULT NULL)
i_room_id	integer	IN (DEFAULT NULL)
default_issue_qty	integer	IN (DEFAULT 0)
valid_from	timestamp without time zone	OUT
valid_until	timestamp without time zone	OUT
register_id	integer	OUT
booking_id	integer	OUT
room_id	integer	OUT
room_number	t_room_number	OUT
first_name	text	OUT
last_name	text	OUT
full_name	text	OUT
adult	smallint	OUT
intf_room_id	text	OUT
room_data	jsonb	OUT
remark	text	OUT
card_serial	text	OUT
card_index	smallint	OUT
access_code	text	OUT
agent	text	OUT
issued_qty	integer	OUT
max_cards	integer	OUT

Definition

declare keycard_agent text;
begin
	keycard_agent := (select code from intf_agent where lower(device_type) = 'keycard' limit 1);
 
	if (i_regiser_id is not null) or (i_room_id is not null) then
		RETURN QUERY
		select arrival + checkin_time::time::interval as valid_from, 
			   rg.departure + rg.departure_time as valid_until, 
			   rg.id, rg.booking_id, rg.room_id, rm.room_number, g.first_name, g.last_name, g.full_name::text, rg.adult, 
			   intf.intf_room_id, intf.room_data, intf.remark, intf.card_serial, intf.card_index, intf.access_code, intf.agent_code,
			   (select sum(iif(log_type='R', -k.card_qty, k.card_qty))::int 
                from intf_keycard_logs k 
			    where (k.register_id = $1) and (k.room_id = rg.room_id)) as issued_qty,
			   iif(default_issue_qty = 0, rg.adult, default_issue_qty) as max_cards	
		from registration as rg 
			left join room rm on rm.id = rg.room_id
			left join guest g on g.id = rg.guest_id
			left join intf_room intf on intf.room_id = rg.room_id
		where intf.agent_code = keycard_agent and rg.status = 'I' 
			 and ($1 is null or rg.id = $1) and ($3 is null or rg.room_id = $3);
	end if;

	if (i_booking_id is not null) or (i_room_id is not null) then
		RETURN QUERY
		select rb.arrival::timestamp as valid_from, 
			rb.departure + bk.departure_time as valid_until, 
			null::int, rb.booking_id, rb.room_id, rm.room_number, g.first_name, g.last_name, g.full_name::text, bi.adult, 
			intf.intf_room_id, intf.room_data, intf.remark, intf.card_serial, intf.card_index, intf.access_code, intf.agent_code,
		    (select sum(iif(log_type='R', -k.card_qty, k.card_qty))::int  
             from intf_keycard_logs k 
		     where (k.booking_id = rb.booking_id) and (k.room_id = rb.room_id)) as issued_qty,
			iif(default_issue_qty = 0, bi.adult, default_issue_qty) as max_cards		
		from room_block rb 
			left join booking_items bi on bi.item_id = rb.booking_item
			left join booking as bk on bk.id = rb.booking_id 
			left join room rm on rm.id = rb.room_id
			left join guest g on g.id = bk.guest_id
			left join intf_room intf on intf.room_id = rb.room_id
		where intf.agent_code = keycard_agent and bk.status in ('R','C') and rb.status = 'B'
			and ($2 is null or rb.booking_id = $2) and ($3 is null or rb.room_id = $3);

	end if;
end

sp_get_keycard_issue_info

Parameters

Definition