aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
new_room_id	integer	IN
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    _arrival date;
    _departure date;
    _status char(1);
    _old_room int;
    _ooo_status TEXT;
BEGIN   
    CALL sp_check_endday_process();

    SELECT rg.arrival, rg.departure, rg.status, rg.room_id 
    FROM registration rg WHERE rg.id = $1
    INTO _arrival, _departure, _status, _old_room;
    
    CASE  
        WHEN _old_room = new_room_id THEN RETURN fn_result_error('30131', 'The room number is alreadly %s', new_room_id);  
        when _status = 'O' THEN RETURN fn_result_error('30111', 'Guest already checked out');   
        when _status = 'X' THEN RETURN fn_result_error('30114', 'Check-in is already cancelled'); 
        else
    END CASE;

    CALL sp_check_room_activity(new_room_id, _arrival, _departure, register_id := $1, check_room_status := true);

    UPDATE registration rg 
    SET room_id = new_room_id, 
        modify_user = $3, 
        modify_time = current_timestamp
    WHERE rg.id = $1;

    -- Update old room status to VAC/OO
    IF NOT is_room_occupied(_old_room, $1) THEN 
        _ooo_status := (SELECT ooo_type FROM out_of_order oo WHERE oo.room_id = _old_room AND oo.start_date = fn_system_date() LIMIT 1);
        UPDATE room_status 
        SET status = COALESCE(_ooo_status, 'VAC'), 
            hk_status = fn_sys_param('ROOM_STATUS', 'CHECKOUT_STATUS', 'DI')  
        WHERE room_id = _old_room;   
    END IF;   
        
    -- Update new room status to OCC
    UPDATE room_status SET status = 'OCC' WHERE room_id = new_room_id;

    PERFORM sp_log_inhouse($1, 'Change room', 
                           'Change room from '||fn_room_number(_old_room)||' to '||fn_room_number(new_room_id), 
                           user_name, _old_room, new_room_id);

    RETURN fn_result_success();
EXCEPTION  
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _sqlstate = RETURNED_SQLSTATE,
        _msg_text = MESSAGE_TEXT,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;    
    RETURN fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_change_stay_room', 
                jsonb_build_object('register_id',register_id, 'room_id',new_room_id, 'user_name',user_name)
           );
END