aonestar.public

Parameters

Name	Type	Mode
room_id	integer	IN
arrival	date	IN
departure	date	IN
register_id	integer	IN (DEFAULT NULL)
block_id	integer	IN (DEFAULT NULL)
ooo_id	integer	IN (DEFAULT NULL)
check_room_status	boolean	IN (DEFAULT false)

Definition

DECLARE
    room_status char(3);
    maid_status char(2);
    sellable_status text[];
    msg_code text; 
    messages jsonb = $${
        "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.",
        "10508" : "Room %s is dirty.",
        "10509" : "Room %s is not available (Maid In)",
        "10510" : "Room %s is not available (Clean)",
        "10511" : "Room %s is not available (Inspect)",
        "10512" : "Room %s is not available (Pickup)"
    }$$;
BEGIN 
    IF check_room_status THEN
        sellable_status := fn_sellable_statuses();

        select status, hk_status 
        from room_status rs where rs.room_id = $1    
        into room_status, maid_status;

        msg_code = case 
            when (room_status = 'OCC')/* and (fn_system_date() between arrival and departure-1)*/ then '10501'
            when (room_status = 'OOO') then '10504'
            when (room_status = 'OOS') then '10505'
            when (room_status = 'OOI') then '10506'
            when (maid_status = 'DI') then '10508'
            when (maid_status <> all(sellable_status)) then 
                case maid_status 
                   when 'MI' then '10509' 
                   when 'CL' then '10510' 
                   when 'IS' then '10511' 
                   when 'PU' then '10512' 
                end
            else null
        end; 
    END IF;

    IF msg_code is null THEN
        msg_code = (SELECT CASE a.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
                                ELSE NULL 
                           END 
                    FROM sp_get_room_activity(room_id, false, arrival, departure) a
                    WHERE ($4 IS NULL OR a.register_id is null OR $4 <> a.register_id)
                         AND ($5 IS NULL OR a.block_id is null OR $5 <> a.block_id)
                         AND ($6 IS NULL OR a.ooo_id is null OR $6 <> a.ooo_id)
                    ORDER BY activity_code, start_date
                    LIMIT 1);
    END IF;
        
    IF msg_code IS NOT NULL THEN 
        RAISE EXCEPTION SQLSTATE '99999' USING detail = fn_result_error(msg_code, messages->>msg_code, fn_room_number($1)); 
    END IF;

END

sp_check_room_activity

Parameters

Definition