aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN (DEFAULT NULL)
booking_id	integer	IN (DEFAULT NULL)
room_number	text	IN (DEFAULT NULL)

Definition

declare 
	default_issue_qty int;
	result_json json;
	configs jsonb;
	v_room_id int;
begin
	configs			  := (select i.configs from interfaces i where code = 'KEYCARD'); 
    default_issue_qty := coalesce(configs->>'default_issue_qty', '0')::int;
	if room_number is not null then
		v_room_id := (select id from room r where r.room_number = $3);
	end if;

	with items as (
	    select 
			iif(rec.register_id is null, 'RSV', 'INH') as status,
			rec.room_id,
			rec.room_number,
			rec.register_id,
			rec.booking_id,
			rec.valid_from,
			rec.valid_until,
			rec.first_name,
			rec.last_name,
			rec.full_name,
			iif(rec.issued_qty >= rec.max_cards, 0, rec.max_cards - coalesce(rec.issued_qty ,0)) as number_of_cards,
			rec.max_cards as maximum_cards,
			coalesce(rec.issued_qty ,0) as issued_cards,
			json_build_object(
				'Agent', rec.agent,
				'RoomNo', rec.room_number,
				'RoomID', rec.intf_room_id,
				'cardSN', rec.card_serial,
				'cardIdx',  rec.card_index,
				'DataWrite', '',
				'Data', rec.room_data
			) as card_data
		from sp_get_keycard_issue_info($1, $2, v_room_id) as rec
	)
	select json_agg(items) from items
	into result_json;
	
	return result_json;
	
end;

sp_get_keycard_issue_info_json

Parameters

Definition