aonestar.public

Parameters

Name	Type	Mode
block_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;
    _booking_id int;
    _booking_item int;
   
    -- ============
    -- add by T.supol
    -- ============
    v_room_number public.t_room_number; 
   
BEGIN   
    CALL sp_check_endday_process();
    CALL sp_check_record_lock(user_name, block_id := $1);

    SELECT rb.booking_id, rb.booking_item, bi.arrival, bi.departure, rb.status, rb.room_id 
    FROM room_block rb LEFT JOIN booking_items bi ON bi.item_id = rb.booking_item 
    WHERE rb.id = $1
    INTO _booking_id, _booking_item, _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::text);  
        WHEN _status <> 'B'          THEN RETURN fn_result_error('30103', 'Cannot change room, this booking already checked in');   
        ELSE
    END CASE;

    CALL sp_check_room_activity(new_room_id, _arrival, _departure, block_id => $1);

    UPDATE room_block rb 
    SET room_id = new_room_id
    WHERE rb.id = $1;
   
    -- ==== 
    -- update booking_items by T.Supol 15-May-2025
    -- ====
   
--    select r.room_number  into v_room_number
--    from room r 
--    where r.id = new_room_id;
--   
--    update booking_items
--    set  
--      rooms = string_to_array(v_room_number::text, ','),
--      room_numbers  = v_room_number
--    where item_id = _booking_item;
   
    perform sp_recalc_booking_rooms(_booking_item::integer);
   
    -- ==== 
    -- end update booking_items by T.Supol 15-May-2025
    -- ====

    PERFORM sp_log_booking(_booking_id, _booking_item, 'Change block room', 
                           'Change room from '||fn_room_number(_old_room)||' to '||fn_room_number(new_room_id), 
                           user_name);

    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_block_room', 
                jsonb_build_object('block_id',block_id, 'room_id',new_room_id, 'user_name',user_name)
           );
END