aonestar.public

Parameters

Name	Type	Mode
booking_item	integer	IN
rooms	int4[]	IN
super_block	boolean	IN (DEFAULT false)
block_user	text	IN (DEFAULT NULL)
result_code	text	OUT
result_msg	text	OUT
msg_code	text	OUT
result_data	json	OUT

Definition

DECLARE 
    rm record;
    rooms_to_block int;
    counter int;
    _booking_id int;
    _arrival date;
    _departure date;
    block_success int = 0;
    input_room_count int = 0;
    block_room_count int = 0;
    in_use_room_count int = 0;
    block_rooms text[] = '{}';
    block_room_ids int[] = '{}';
    in_use_rooms text[] = '{}';
    block_id int;
    block_ids int[] = '{}';
    error    TEXT;
    _msg     TEXT;
    _state   TEXT;
    _detail  TEXT;
    _hint    TEXT;
    _context TEXT;
    messages jsonb = $${
        "10200" : "Room %s has been blocked.",
        "10201" : "%s rooms have been blocked.",
        "10208" : "Room %s not available during this period.",
        "10209" : "%s of %s rooms have been blocked, room %s not available during this period.",
        "10210" : "No room to be blocked.",
        "10501" : "Room %s is occupied during this period.", 
        "10503" : "Room %s is blocked during this period.", 
        "10504" : "Room %s is Out Of Order during this period.",      
        "10505" : "Room %s is Out Of Service during this period.",
        "10506" : "Room %s is Out Of Inventory during this period."
    }$$;
BEGIN     
    CALL sp_check_endday_process();

    block_user = coalesce(block_user, fn_current_user());  

    CALL sp_check_record_lock(block_user, booking_item := $1);
    result_data = '[]';

    SELECT booking_id, arrival, departure, (qty -block_qty -inhouse_qty) 
    FROM booking_items 
    WHERE item_id = $1
    INTO _booking_id, _arrival, _departure, rooms_to_block;

    rooms_to_block = least(rooms_to_block, fn_length(rooms));       

    IF rooms_to_block < 1 THEN
        result_code = '4';
        msg_code = '10210';
        result_msg = sys_msg('10210', messages->>msg_code);
        return;
    END IF;

    -- Process block rooms
    FOR rm in (SELECT r.id as room_id, r.room_number::text, fn_get_room_activity(r.id, _arrival, _departure) as activity_code 
               from room r
               where r.id = any(rooms)
               order by activity_code, r.room_number)
    LOOP
        IF block_success < rooms_to_block THEN         
            IF rm.activity_code = 0 THEN    
                -- Do block room
                INSERT INTO room_block(booking_id, booking_item, arrival, departure, block_user, room_id, super_block)
                VALUES (_booking_id, $1, _arrival, _departure, block_user, rm.room_id, super_block)
                returning id into block_id; 
                block_ids     = block_ids || block_id; 
                block_rooms   = block_rooms || rm.room_number;
                block_room_ids= block_room_ids || rm.room_id;
                block_success = block_success +1;
            ELSE 
                in_use_rooms = in_use_rooms || rm.room_number;
                msg_code = CASE rm.activity_code  
                    WHEN 1 THEN '10501'  -- In-house
                    WHEN 2 THEN '10501'  -- In-house (more guest)
                    WHEN 3 THEN '10503'  -- Block
                    WHEN 4 THEN '10504'  -- Out of order
                    WHEN 5 THEN '10505'  -- Out of service
                    WHEN 6 THEN '10506'  -- Out of inventory
                END;
            END IF;              
        END IF;
    END LOOP;


    input_room_count  := fn_length(rooms);
    block_room_count  := fn_length(block_rooms);
    in_use_room_count := fn_length(in_use_rooms);
       
    IF block_success > 0 THEN 
        -- Get result data
        with res as (
            select r2.id,
                    r2.room_number || '/' || rt2.code as room,
                    rt2.id as roomtype_id,
                    rt2."name" as room_type,
                    bt2.id as bedtype_id,
                    bt2."name" as bed_type,
                    loc2.id as location_id,
                    loc2."name" as location,
                    b2.id as building_id,
                    b2."name" as building,
                    rx2.id as exposure_id,
                    rx2."name" as exposure,
                    st2.status as room_status,
                    st2.hk_status,
                    rg2.id as roomgroup_id,
                    rg2."name" as room_group,
                    r2.room_number, 
                    rt2.code as roomtype_code,
                    bt2.code as bedtype_code,
                    rb2.block_date,
                    rb2.block_time,
                    rb2.block_user,
                    rb2.super_block 
            from room_block rb2
                left join room r2 on r2.id = rb2.room_id
                left join room_type rt2 on rt2.id = r2.roomtype_id
                left join room_group rg2 on rg2.id = rt2.group_id
                left join bed_type bt2 on bt2.id = r2.bedtype_id
                left join room_location loc2 on loc2.id = r2.location_id
                left join building b2 on b2.id = r2.building_id
                left join room_exposure rx2 on rx2.id = r2.exposure_id
                left join room_status st2 on st2.room_id = r2.id
            where  rb2.id = ANY(block_ids)
        )         
        select coalesce(json_agg(res),'[]') from res
        into result_data;
 
        -- Process tasks after rooms blocked
        PERFORM sp_recalc_booking_rooms($1);
        PERFORM sp_log_booking(_booking_id, booking_item, 'Block', 'Block room '||array_to_string(block_rooms, ', '), block_user); 
        PERFORM sp_enqueue_realtime('*', fn_current_propcode(), 'room_status', sp_get_room_details_js(block_room_ids));
    END IF;
    
    
    result_code = CASE 
        WHEN (block_room_count = 0)              THEN '4'   -- All rooms failed (Error)
        WHEN (block_room_count = rooms_to_block) THEN '0'   -- ALL room blocked (Success)
        WHEN (block_room_count < rooms_to_block) THEN '1'   -- Some room failed (Warning) 
    END;   

    IF result_code = '0' THEN
        msg_code = IIF(block_room_count = 1, '10200', '10201');
    ELSEIF (input_room_count > 1) or (in_use_room_count > 1) THEN  
        msg_code = CASE result_code 
                        WHEN '4' THEN '10208' 
                        WHEN '1' THEN '10209' 
                   END; 
    END IF; 
    
    result_msg = CASE    
        WHEN msg_code = '10200' THEN sys_msg_format(msg_code, messages->>msg_code, block_rooms[1])
        WHEN msg_code = '10201' THEN sys_msg_format(msg_code, messages->>msg_code, block_room_count::text)
        WHEN msg_code = '10208' THEN sys_msg_format(msg_code, messages->>msg_code, array_to_string(in_use_rooms, ', '))
        WHEN msg_code = '10209' THEN sys_msg_format(msg_code, messages->>msg_code, block_room_count::text, rooms_to_block::text, array_to_string(in_use_rooms, ', '))
        WHEN msg_code IN ('10501','10503','10504','10505','10506') THEN 
            sys_msg_format(msg_code, messages->>msg_code, in_use_rooms[1])
    END;

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_block_rooms', 
         jsonb_build_object(
            'booking_item', booking_item, 
            'rooms'       , rooms, 
            'super_block' , super_block, 
            'block_user'  , block_user
         )
    ); 
END