aonestar.public

Parameters

Name	Type	Mode
room_id	integer	IN
ooo_type	character	IN
reason_id	integer	IN
start_date	date	IN
end_date	date	IN
remark	text	IN (DEFAULT NULL)
return_status	character	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
replace_current	boolean	IN (DEFAULT false)
in_loop	boolean	IN (DEFAULT false)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
ooo_id	integer	OUT

Definition

declare 
	cur_status text;
	log_old text;
	log_text text;
	room_number text;
	system_date date;
	is_current_period boolean = false;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT; 	
   
   
    cur_system_date date;
begin
	select fn_system_date() into cur_system_date;

if start_date < cur_system_date then
		    result_code := '1';
		    result_msg  := 'Start date must not be in the past';
		    msg_code    := 'INVALID_DATE';
		    ooo_id      := NULL;
    		return;
    end if;

perform sp_log_debug('sp_add_out_of_order', fn_room_number(room_id));

IF NOT in_loop THEN 
        CALL sp_check_endday_process();
    END IF ;

result_code = '0'; result_msg = 'Success'; msg_code = '00';
	system_date = fn_system_date() ; 
	is_current_period = (system_date between start_date and end_date);
	select r.room_number, s.status from room r inner join room_status s on r.id = s.room_id where r.id = $1
	into room_number, cur_status;
	
    CALL sp_check_room_activity(room_id, start_date, end_date);
	
	log_old = cur_status;
	if replace_current and is_current_period and cur_status <> 'VAC' then
		with del as (
		   delete from out_of_order as o 
		   where o.room_id = $1 and system_date between o.start_date and o.end_date
		   returning id
		)
		select fn_get_ooo_desc(del.id,1) from del limit 1 into log_old;
	end if;		
	
	insert into out_of_order(room_id, ooo_type, reason_id, start_date, end_date, remark, return_status, create_user)
	values (room_id, ooo_type, reason_id, start_date, end_date, remark, return_status, coalesce(user_name, fn_current_user()))
	returning id into ooo_id;

if is_current_period then
		update room_status s set status = ooo_type where s.room_id = $1;
		perform sp_log_room($1, 'Change room status', format('Room %s: %s -> %s', room_number, log_old, fn_get_ooo_desc(ooo_id,1)), user_name);
	else
		perform sp_log_room($1, 'Set Out Of Order', fn_get_ooo_desc(ooo_id), user_name);
	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;
    
    CALL sp_handle_error(result_code, result_msg, msg_code,
        _state, result_msg, _detail, _hint, _context, 'sp_add_out_of_order', 
                jsonb_build_object(
                    'ooo_id'       , ooo_id, 
                    'ooo_type'     , ooo_type, 
                    'reason_id'    , reason_id, 
                    'start_date'   , start_date, 
                    'end_date'     , end_date, 
                    'remark'       , remark,
                    'return_status', return_status,
                    'user_name'    , user_name 
                )
    ); 
END