aonestar.public

Parameters

Name	Type	Mode
rooms	int4[]	IN
new_status	character	IN
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);
	success int = 0;
	_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 not in ('VAC','OOO','OOS','OOI') then	
		msg_code = '09'; 
	else
		for _room_id in select unnest(rooms)
		loop
		    result_code = '0'; result_msg = 'Success'; msg_code = '00';
			ooo_id = null;
			
			select r.room_number, s.status from room r inner join room_status s on r.id = s.room_id where r.id = _room_id
			into room_number, cur_status;
			
			if (cur_status = 'OCC')	and (new_status = 'VAC')  then
				msg_code   = '04';
			elseif (cur_status = 'VAC')	and (new_status = 'VAC') then
				msg_code   = '08';
			else
			
				if new_status = 'VAC' then 
					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;
	
					perform sp_log_room(_room_id, 'Change room status', format('Room %s: %s -> VAC', room_number, log_text), user_name);
	
					success = success + 1;				
					changed_rooms = changed_rooms || _room_id;	
				else
					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 + iif(ooo_id is null, 0, 1);
	
					if ooo_id is not null then
						changed_rooms = changed_rooms || _room_id;	
					end if;
				end if;			
			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;
			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 loop;
		
		msg_code = '00';
		if success > 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 ('09','10') then msg_code
		when (success = 0) then '03' -- coalesce(msg_code, '03')
		when (success = input_count) and (success = 1) then '00'
		when (success = input_count) and (success > 1) then '01'
		when (success < 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 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::text)
			WHEN '10602' THEN sys_msg_format(msg_code, '%s of %s rooms have been changed.', success::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 '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), user_name); 	 
END

sp_change_room_status_old

Parameters

Definition