aonestar.public

Parameters

Name	Type	Mode
i_ooo_id	integer	IN
i_ooo_type	character	IN
i_reason_id	integer	IN
i_start_date	date	IN
i_end_date	date	IN
i_remark	text	IN (DEFAULT NULL)
i_return_status	character	IN (DEFAULT NULL)
user_name	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

declare 
    log_old text;
    log_new text;
    room_number text;
    current_status text;
    change_status_to_vac boolean;
    change_status_to_ooo boolean;
    new_period_is_current boolean;
    old_period_is_current boolean;
    old_return_status text;
    old_start date;
    old_end date;
    system_date date;
    _room_id int;
    activity int;
    _msg     TEXT;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;  
BEGIN
    CALL sp_check_endday_process();

    result_code = '0'; result_msg = 'Success'; --err_code = '00';
    system_date = fn_system_date();
    
    select o.room_id, r.room_number, o.start_date, o.end_date, o.return_status
    from out_of_order o left join room r on r.id = o.room_id 
    where o.id = $1
    into _room_id, room_number, old_start, old_end, old_return_status;
    
    CALL sp_check_room_activity(_room_id, COALESCE(i_start_date, old_start), COALESCE(i_end_date, old_end), ooo_id => $1);
    
    log_old = fn_get_ooo_desc(i_ooo_id, 1);
    
    update out_of_order o
    set ooo_type = $2, reason_id = $3, start_date = $4, end_date = $5, remark = $6, return_status = $7,
        modify_user = coalesce($8, fn_current_user()),
        modify_time = CURRENT_TIMESTAMP
    where o.id = i_ooo_id;
    
    change_status_to_vac = (system_date between old_start and old_end) and not(system_date between i_start_date and i_end_date);
    change_status_to_ooo = not(system_date between old_start and old_end) and (system_date between i_start_date and i_end_date);
    
    if change_status_to_vac then
        update room_status 
        set status = 'VAC', 
            hk_status = coalesce(old_return_status, fn_sys_param('ROOM_STATUS', 'RETURN_STATUS', 'DI'))  
        where room_id = _room_id and status in ('OOO','OOS','OOI');
    elseif change_status_to_ooo then
        update room_status 
        set status = i_ooo_type 
        where room_id = _room_id and status = 'VAC';
    end if;
        
    log_new = fn_get_ooo_desc(i_ooo_id, 1);
    perform sp_log_room(_room_id, 'Update Out Of Order', format('Room %s: %s -> %s', room_number, log_old, log_new), user_name);
    perform sp_enqueue_realtime('*', fn_current_propcode(), 'room_status', sp_get_room_details_js(ARRAY[_room_id], true));  
    
EXCEPTION 
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS _msg = MESSAGE_TEXT, _state = RETURNED_SQLSTATE, _detail = PG_EXCEPTION_DETAIL, _hint = PG_EXCEPTION_HINT, _context = PG_EXCEPTION_CONTEXT;
    
    -- Handle database error and custom exception from statement "CALL sp_check_xxx"
    CALL sp_handle_error(result_code, result_msg, msg_code,
        _state, _msg, _detail, _hint, _context, 'sp_update_out_of_order', 
        jsonb_build_object(
            'ooo_id'       , i_ooo_id, 
            'ooo_type'     , i_ooo_type, 
            'reason_id'    , i_reason_id, 
            'start_date'   , i_start_date, 
            'end_date'     , i_end_date, 
            'remark'       , i_remark,
            'return_status', i_return_status,
            'user_name'    , user_name 
        )
    );
END