aonestar.public

Parameters

Name	Type	Mode
i_username	text	IN
i_booking_item	integer	IN
i_room_id	integer	IN
i_super_block	boolean	IN (DEFAULT false)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT

Definition

declare
    _block_user text;
    _block_id int;
    _booking_id int;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    error json;
begin
    /**
     * Create by KenG (2020/06/01)
     * Modified by Pisanu (2025/07/04)
     */
    CALL sp_check_endday_process();
    msg_code = '0';
    i_username := coalesce(i_username, fn_current_user());

    --คนที่ convert  ได้ต้องเป็นคนที่ทำการ block  เท่านั้น/check permission ด้วย
    SELECT id, booking_id, block_user 
    FROM room_block 
    WHERE booking_item = i_booking_item and room_id = i_room_id  
    INTO _block_id, _booking_id, _block_user;

    CALL sp_check_record_lock(i_username, block_id := _block_id);
        
    CASE
        WHEN (_block_user is null) THEN 
            result_code := '4';
            result_msg  := 'Room block record not found!';
        WHEN (i_super_block and not sp_check_permission($1, '12002')) THEN  
            result_code := '4';
            result_msg  := 'You don''t have permission to convert to Super-Block!';
        WHEN (not i_super_block and _block_user <> i_username and not sp_check_permission($1, '12003')) THEN  
            result_code := '4';
            result_msg  := 'You don''t have permission to cancel Super-Block!';
        ELSE
            result_code := '0'; 
            result_msg  := 'Success';
    END CASE;

    IF (result_code <> '4') THEN
        --update supper_block filed
        UPDATE room_block SET super_block = i_super_block WHERE id = _block_id;

        IF i_super_block THEN
           PERFORM sp_log_booking(_booking_id, i_booking_item, 'Block', 'Convert room '||fn_room_number(i_room_id)||' to Super-Block', i_username);
        ELSE
           PERFORM sp_log_booking(_booking_id, i_booking_item, 'Block', 'Cancel Super-Block on room '||fn_room_number(i_room_id), i_username); 
        END IF;
    END IF;

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;    
    error := fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_convert_supper_block', 
                jsonb_build_object(
                    'booking_item', i_booking_item, 
                    'block_id', _block_id,
                    'room_id', i_room_id, 
                    'super_block', i_super_block,
                    'user_name', i_username
                )
           );
    result_code := error->>'result_code';
    result_msg  := error->>'result_msg';
    msg_code    := error->>'msg_code';
END;