aonestar.public

Parameters

Name	Type	Mode
room_id	integer	IN (DEFAULT NULL)

Definition

select json_agg(dataset)
	from (
	select r.id, r.room_number, st.status, st.hk_status,
		r.roomtype_id, rt.name as room_type, 
		r.bedtype_id, bt.name as bed_type,
		r.building_id, b.name as building, 
		r.location_id, loc.name as location, 
		r.exposure_id, rx.name as exposure,
		r.connecting,
		r.connect_rooms as connecting_room_id,
		(select string_agg(rf.room_number, ', ')
		 from unnest(r.connect_rooms) as connect_rooms_id
			left join room rf on rf.id = connect_rooms_id
		) as connecting_room,
		
		(
			select 
				CASE 
					 WHEN array_agg(rf.name) is null 
					 THEN '{}'
					 ELSE array_agg(json_build_object('id', facility_id, 'name', rf.name))
				 END
		 	from unnest(r.facilities) facility_id 
			left join room_facility rf on rf.id = facility_id
		) as facilities
	from room r left join room_type rt on rt.id = r.roomtype_id
		left join bed_type bt on bt.id = r.bedtype_id
		left join building b on b.id = r.building_id
		left join room_location loc on loc.id = r.location_id
		left join room_exposure rx on rx.id = r.exposure_id
		left join room cnx on cnx.id = r.connecting_room
		left join room_status st on st.room_id = r.id 
	where $1 is null or r.id = $1
	) as dataset

sp_get_room_info_js

Parameters

Definition