aonestar.public

Parameters

Name	Type	Mode
rooms	int4[]	IN
new_status	character	IN (DEFAULT NULL)
new_hkstatus	character	IN (DEFAULT NULL)
reason_id	integer	IN (DEFAULT NULL)
start_date	date	IN (DEFAULT NULL)
end_date	date	IN (DEFAULT NULL)
remark	text	IN (DEFAULT NULL)
return_status	character	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
result_data	json	OUT

Definition

declare 
	cur_status char(3);
	cur_hkstatus char(2);
	success_count int = 0;
	success bool;
	_room_id int;
	system_date date;
	cur_startdate date;
	cur_enddate date;
	room_number text;
	ooo_id int;
	log_text text;
	--log_new text;
	input_count int;
	current_to_ooo boolean = false;
	js_result json[] = '{}';
	changed_rooms int[] = '{}';
	event_data json;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;	
BEGIN
    --PERFORM sp_log_debug('out_of_order', format('room_id: %s, return_status: %s', rooms::text, return_status));
    
	system_date = fn_system_date();
	input_count = fn_length(rooms);
	
	if input_count = 0 then
		msg_code = '10'; 
	ELSEIF new_status IS NULL AND new_hkstatus IS NULL THEN 
		msg_code = '05';
	elseif (new_status IS NOT NULL) AND (new_status not in ('VAC','OOO','OOS','OOI')) then	
		msg_code = '09'; 
	else
		FOR _room_id, room_number, cur_status, cur_hkstatus in 
			SELECT rm.id, rm.room_number, s.status, s.hk_status 
			FROM unnest(rooms) rm_id 
				INNER JOIN room rm ON rm.id = rm_id 
				INNER JOIN room_status s on s.room_id = rm_id
		LOOP 
		    result_code = '0'; result_msg = 'Success'; msg_code = '00'; success = FALSE;
			ooo_id = null;
			
			-- ROOM STATUS --
		    IF (cur_status = 'OCC')	and (new_status = 'VAC')  then
				msg_code   = '04';
			
			ELSIF new_status = 'VAC' then 
			    -- Change to VAC
				log_text := cur_status;				
				if cur_status in ('OOO','OOS','OOI') then
					delete from out_of_order o 
					where (o.room_id = _room_id) and (system_date between o.start_date and o.end_date)
					returning fn_get_ooo_desc(id,1) into log_text;
				end if;		

				update room_status s set status = 'VAC' where s.room_id = _room_id;
				success := TRUE;			
			
			ELSEIF new_status IN ('OOO','OOS','OOI') then 
			    -- Change to OOO, OOS, OOI
				select o.result_code, o.result_msg, o.msg_code, o.ooo_id--, fn_get_ooo_desc(o.ooo_id,1) 
				from sp_add_out_of_order(_room_id, new_status, reason_id, start_date, end_date, remark, return_status, user_name, true) as o
				into result_code, result_msg, msg_code, ooo_id; --, log_new; 

				success := success OR (ooo_id IS NOT NULL);	
			END IF;
		
			
			-- MAID STATUS --
			IF (msg_code = '00') AND (new_hkstatus IS NOT NULL) AND (new_hkstatus IS DISTINCT FROM cur_hkstatus) THEN 
				RAISE NOTICE '% : %', _room_id, new_hkstatus;
				update room_status rs 
				set hk_status = new_hkstatus
				where rs.room_id = _room_id;
				success := TRUE;
			END IF;
		
			success_count = success_count + IIF(success, 1, 0); 
		    IF success THEN 
		    	changed_rooms := changed_rooms || _room_id;	
			END IF;
		
			-- LOG --
			IF new_status NOT IN ('OOO','OOS','OOI') then 
				perform sp_log_roomstatus(_room_id, user_name, cur_status, new_status, cur_hkstatus, new_hkstatus);
			END IF;
			
			result_code = case msg_code
				when '04' then '4'
				--when '08' then '4'--'1'
				else result_code
			end;	
			
			msg_code = iif(msg_code in ('04','08'), '106'||msg_code, msg_code);
			result_msg = case msg_code 
				when '10604' then sys_msg_format(msg_code, 'Room %s is occupied.', room_number)
--				when '10608' then sys_msg_format(msg_code, 'Room %s is already vacant.', room_number)
				else result_msg
			end;
		
			IF NOT success THEN  
			    js_result = js_result || json_build_object('room_id', _room_id, 'result_code', result_code, 'result_msg', result_msg, 'msg_code', msg_code, 'ooo_id', ooo_id);
			END IF;
		END LOOP;
		
		msg_code = '00';
		if success_count > 0 then
			event_data = sp_get_room_details_js(changed_rooms, true);
			perform sp_enqueue_realtime('*', fn_current_propcode(), 'room_status', event_data);	
			--perform sp_sys_log('room_status event', changed_rooms::text);		
		end if;
	end if;	
	
	result_data = array_to_json(js_result);

	msg_code = case 
		when msg_code in ('05','09','10') then msg_code
		when (success_count = 0) then '03' -- coalesce(msg_code, '03')
		when (success_count = input_count) and (success_count = 1) then '00'
		when (success_count = input_count) and (success_count > 1) then '01'
		when (success_count < input_count) then '02'
	end;
	
	result_code = case
		when msg_code in ('00','01') then '0'
		when msg_code = '02' then '1'
		else '4'
	end;
	
	--IF (fn_length(rooms) = 1) AND (js_result)
	
	if char_length(msg_code) = 2 then
		msg_code = '106'||msg_code;
		result_msg = CASE msg_code 
			WHEN '10600' THEN sys_msg_format(msg_code, 'Room %s has been changed.', room_number)
			WHEN '10601' THEN sys_msg_format(msg_code, '%s rooms have been changed.', success_count::text)
			WHEN '10602' THEN sys_msg_format(msg_code, '%s of %s rooms have been changed.', success_count::text, input_count::text)
			WHEN '10603' THEN sys_msg(msg_code, 'No room has been changed.')
-- 			WHEN '10604' THEN sys_msg_format(msg_code, 'Room %s is occupied on this period.', room_number)
			WHEN '10605' THEN sys_msg(msg_code, 'Please specify room status or maid status.')
			when '10609' then sys_msg_format(msg_code, 'Invalid status (%s -> %s).', cur_status, new_status)
 			WHEN '10610' THEN sys_msg(msg_code, 'No room to be changed.')
		end;
	end if;
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _state   = RETURNED_SQLSTATE,
        result_msg= MESSAGE_TEXT,
        _detail  = PG_EXCEPTION_DETAIL,
        _hint    = PG_EXCEPTION_HINT,
        _context = PG_EXCEPTION_CONTEXT;
    result_code = '4'; msg_code = '99';     
    PERFORM sp_log_error('sp_change_room_status', result_msg, _detail, _state, _hint, _context, 
        jsonb_build_object(
        	'rooms',rooms, 
       		'new_status', new_status,
       		'new_hkstatus', new_hkstatus,
       		'reason_id', reason_id,
       		'start_date', start_date,
       		'end_date', end_date,
       		'remark', remark,
       		'return_status', return_status,
       		'user_name', user_name
       		)); 	 
END

sp_change_room_status1

Parameters

Definition