aonestar.public

Parameters

Name	Type	Mode
i_search_text	text	IN (DEFAULT NULL)
i_roomtype_id	integer	IN (DEFAULT NULL)
i_bedtype_id	integer	IN (DEFAULT NULL)
i_group_id	integer	IN (DEFAULT NULL)
i_location_id	integer	IN (DEFAULT NULL)
i_building_id	integer	IN (DEFAULT NULL)
i_exposure_id	integer	IN (DEFAULT NULL)
i_arrival	date	IN (DEFAULT NULL)
i_departure	date	IN (DEFAULT NULL)
i_connecting	boolean	IN (DEFAULT NULL)
i_connecting_room	text	IN (DEFAULT NULL)
i_facilities	int4[]	IN (DEFAULT NULL)
i_show_activities	boolean	IN (DEFAULT false)
i_room_ids	int4[]	IN (DEFAULT NULL)

Definition

DECLARE
		json_result json;
		--room_available int[] = '{}';
		result_count_room int;
		system_date date;
	_prefix_pms_file_url text;
	begin
		/**
		 * Create by KenG (2020/04/06)
		 * -add param checkin mode, status (2020/04/17)
		 */
		
    	/*
		--Get room_available
		--check sp_room_avviable pass  arrival | departure -1 | roomtype_id
		--select avviable come > 0 หมายความว่า มีห้องว่าง
		if ((i_arrival is not null) and (i_departure is not null)) then 
			select array_agg(roomtype_id) from (
				select roomtype_id from sp_get_room_available(i_arrival, i_departure::date - integer '1', i_roomtype_id, i_group_id) group by roomtype_id
			) as room_avail into room_available;
		/*elseif((i_arrival is not null) and (i_departure is not null) and (i_roomtype_id is not null)) then
			select array_agg(roomtype_id) from (
				select roomtype_id from sp_get_room_available(i_arrival, i_departure::date - integer '1', i_roomtype_id is null or i_roomtype_id, null) group by roomtype_id
			) as room_avail into room_available;	*/	
		end if;
	    */
		--count all room
		select count(id) from room into result_count_room;
	
		--Get system date
		system_date = fn_system_date();
	
		--Get prefix file pms url
		--fn_prop_config can't test from stored procedure because pass str_conn from nodeJS
		--select config_value from fn_prop_config(null, 'PREFIX_URL_PMS_FILE', '') into _prefix_pms_file_url;
		_prefix_pms_file_url := fn_prop_config('PREFIX_URL_PMS_FILE');
	
		if (i_show_activities = true) then
		/**
		 * Show activities
		 */
			select json_build_object('total_rows', result_count_room, 'row_count', count(*), 'results', coalesce(json_agg(dataset), '[]'))
			from (
				select r.id, concat(r.room_number, '/', rt.code) as room,
					r.room_number as room_number,
					rt.code as roomtype_code,
					rt.id as roomtype_id, rt.name as room_type, 
					bt.id as bedtype_id, bt."name" as bed_type,
					
					(
						case
							when (bt.image is null) then null 
							else _prefix_pms_file_url || bt.image_url 
						end
					) as bedtype_image_url,
					
					loc.id as location_id, loc.name as location, 
					b.id as building_id, b.name as building, 
					rx.id as exposure_id, rx."name" as exposure, 
					
					st.status as room_status, (
					 case
					 	when st.status = 'VAC' then 'Vacant'
					 	when st.status = 'OCC' then 'Occupied'
					 	when st.status = 'OOO' then 'Out of Order'
					 	when st.status = 'OOS' then 'Out of Service'
					 	when st.status = 'OOI' then 'Out of Inventory'
					 	else null
					 end
					) as room_status_name,
					
					st.hk_status, (
						case
							when st.hk_status = 'DI' then 'Dirty'
							when st.hk_status = 'CL' then 'Clean'
							when st.hk_status = 'IS' then 'Inspected'
							when st.hk_status = 'PU' then 'Pickup'
							else null
						end
					) as hk_status_name,
					
					rg.id as roomgroup_id, rg.name room_group,
					r.remark as room_remark,
					r.connecting as 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,
					
					json_build_object(
						'expected_arrival', exists(select id from room_block where room_id = r.id and arrival = system_date and checked_in = false),
						'expected_departure', exists(select id from registration where room_id = r.id and departure = system_date and status = 'I')
					) as flag,
					
					(
                        WITH activity AS (
                            SELECT a.activity_code||'-'||COALESCE(a.register_id, a.block_id, a.ooo_id) AS id, 
                                a.*, oo.ooo_type, oo.reason_id, oo.return_status, oo.remark                           
                            FROM sp_get_room_activity(r.id, true) a 
                                LEFT JOIN out_of_order oo ON oo.id = a.ooo_id
                        ), items AS (
                            SELECT activity_type, activity_code, json_agg(a) AS guests  
                            FROM activity a
                            GROUP BY activity_code, activity_type
                            ORDER BY activity_code
                        )
                        SELECT COALESCE(json_agg(items),'[]') FROM items
					) as activities
					
				from room r 
    				left join room_type rt on rt.id = r.roomtype_id
    				left join room_group rg on rg.id = rt.group_id
    				left join bed_type bt on bt.id = r.bedtype_id
    				left join room_location loc on loc.id = r.location_id
    				left join building b on b.id = r.building_id
    				left join room_exposure rx on rx.id = r.exposure_id
    				left join room_status st on st.room_id = r.id
				where ((i_search_text is null) 
						or (LOWER(r.room_number)::text like LOWER(i_search_text)||'%')
						or (LOWER(rt.code)::text = LOWER(i_search_text))
						or (LOWER(bt.name) like '%'||LOWER(i_search_text)||'%')
						or (LOWER(st.status )::text = LOWER(i_search_text))
					)
					and (i_roomtype_id is null or i_roomtype_id = r.roomtype_id) --or r.roomtype_id = any(room_available))
					and (i_bedtype_id is null or i_bedtype_id = r.bedtype_id)
					and (i_group_id is null or i_group_id = rt.group_id)
					and (i_location_id is null or i_location_id = r.location_id)
					and (i_building_id is null or i_building_id = r.building_id)
					and (i_exposure_id is null or i_exposure_id = r.exposure_id)
					and (i_connecting is null or i_connecting = r.connecting)
					--and (i_connecting_room is null or (r.connecting =true and i_connecting_room รlike '%'||r.room_number||'%'))
				 	and (i_connecting_room is null or (r.connecting =true and r.room_number ilike i_connecting_room ||'%'))
--					and 
--					(
--						   i_connecting_room is null 
--						or i_connecting_room::integer = any(r.connect_rooms)
--					)
--					and 
--					(
--						   i_connecting_room is null
--						or exists (
--									select 1 from unnest(r.connect_rooms) as room_id
--									where i_connecting_room ilike room_id::text
--								  )
--					)
					
					
					
					and (i_facilities is null or i_facilities && r.facilities)
					and (i_room_ids is null or r.id = any(i_room_ids))
					AND (i_arrival IS NULL OR i_departure IS NULL OR NOT EXISTS(SELECT * FROM sp_get_room_activity(r.id, i_start_date=>i_arrival, i_end_date=>i_departure)))
				order by r.room_number
			) as dataset into json_result;
			return json_result;		
		else
		/**
		 * No show activities
		 */
			select json_build_object('total_rows', result_count_room, 'row_count', count(*), 'results', coalesce(json_agg(dataset), '[]'))
			from (
				select r.id, concat(r.room_number, '/', rt.code) as room,
					r.room_number as room_number,
					rt.code as roomtype_code,
					rt.id as roomtype_id, rt.name as room_type, 
					bt.id as bedtype_id, bt."name" as bed_type,
					(
						case
							when (bt.image is null) then null 
							else _prefix_pms_file_url || bt.image_url 
						end
					) as bedtype_image_url,
					loc.id as location_id, loc.name as location, 
					b.id as building_id, b.name as building, 
					rx.id as exposure_id, rx."name" as exposure, 
					st.status as room_status, (
					 case
					 	when st.status = 'VAC' then 'Vacant'
					 	when st.status = 'OCC' then 'Occupied'
					 	when st.status = 'OOO' then 'Out of Order'
					 	when st.status = 'OOS' then 'Out of Service'
					 	when st.status = 'OOI' then 'Out of Inventory'
					 	else null
					 end
					) as room_status_name,
					st.hk_status, (
						case
							when st.hk_status = 'DI' then 'Dirty'
							when st.hk_status = 'CL' then 'Clean'
							when st.hk_status = 'IS' then 'Inspected'
							when st.hk_status = 'PU' then 'Pickup'
							else null
						end
					) as hk_status_name,
					rg.id as roomgroup_id, rg.name room_group,
					r.remark as room_remark,
					json_build_object(
						'expected_arrival', exists(select id from room_block where room_id = r.id and arrival = system_date and checked_in = false),
						'expected_departure', exists(select id from registration where room_id = r.id and departure = system_date and status = 'I')
					) as flag
				from room r 
    				left join room_type rt on rt.id = r.roomtype_id
    				left join room_group rg on rg.id = rt.group_id
    				left join bed_type bt on bt.id = r.bedtype_id
    				left join room_location loc on loc.id = r.location_id
    				left join building b on b.id = r.building_id
    				left join room_exposure rx on rx.id = r.exposure_id
    				left join room_status st on st.room_id = r.id
				where ((i_search_text is null) 
						or (LOWER(r.room_number)::text like LOWER(i_search_text)||'%')
						or (LOWER(rt.code)::text = LOWER(i_search_text))
						or (LOWER(bt.name) like '%'||LOWER(i_search_text)||'%')
						or (LOWER(st.status )::text = LOWER(i_search_text))
					)
					and (i_roomtype_id is null or i_roomtype_id = r.roomtype_id) --or r.roomtype_id = any(room_available))
					and (i_bedtype_id is null or i_bedtype_id = r.bedtype_id)
					and (i_group_id is null or i_group_id = rt.group_id)
					and (i_location_id is null or i_location_id = r.location_id)
					and (i_building_id is null or i_building_id = r.building_id)
					and (i_exposure_id is null or i_exposure_id = r.exposure_id)
					and (i_connecting is null or i_connecting = r.connecting)
					and (i_connecting_room is null or i_connecting_room::integer = any(r.connect_rooms))
					and (i_facilities is null or i_facilities && r.facilities)
					and (i_room_ids is null or r.id = any(i_room_ids))
					AND (i_arrival IS NULL OR i_departure IS NULL OR NOT EXISTS(SELECT * FROM sp_get_room_activity(r.id, i_start_date=>i_arrival, i_end_date=>i_departure)))
				order by r.room_number
			) as dataset into json_result;
			return json_result;
		end if;
	END;

sp_search_room_js

Parameters

Definition