aonestar.public

Parameters

Name	Type	Mode
i_room_id	integer	IN (DEFAULT NULL)
more_guest	boolean	IN (DEFAULT false)
i_start_date	date	IN (DEFAULT NULL)
i_end_date	date	IN (DEFAULT NULL)
room_number	t_room_number	OUT
activity_type	text	OUT
activity_code	integer	OUT
room_id	integer	OUT
booking_id	integer	OUT
booking_item	integer	OUT
block_id	integer	OUT
register_id	integer	OUT
guest_id	integer	OUT
folio_id	integer	OUT
ooo_id	integer	OUT
name	text	OUT
start_date	date	OUT
end_date	date	OUT
is_more_guest	boolean	OUT

Definition

WITH inh AS (
        SELECT r.room_id, r.booking_id, r.booking_item, NULL::int AS block_id,
                r.id AS register_id, r.guest_id, null::int as ooo_id, g.full_name AS name, 
                r.arrival AS start_date, r.departure AS end_date, r.folio_id 
        FROM registration r LEFT JOIN guest g ON g.id = r.guest_id 
        WHERE ($1 IS NULL OR r.room_id = $1) AND r.status = 'I'
    ), activity AS (
        SELECT 'In-house' AS activity_type, 1 AS activity_code, inh.*, False as is_more_guest FROM inh
        UNION ALL
        SELECT 'In-house' AS activity_type, 1 AS activity_code, 
               inh.room_id, inh.booking_id, inh.booking_item, null, inh.register_id, 
               rg.guest_id, null, g.full_name, inh.start_date, inh.end_date, inh.folio_id, True as is_more_guest 
        FROM register_guests rg 
            INNER JOIN inh ON inh.register_id = rg.register_id
            LEFT JOIN guest g ON g.id = rg.guest_id 
        WHERE more_guest = TRUE 
        UNION ALL
        SELECT 'Block', 3, rb.room_id, rb.booking_id, rb.booking_item, rb.id AS block_id, null, b.guest_id, 
                null, g.full_name, bi.arrival, bi.departure, b.folio_id, False as is_more_guest
        FROM room_block rb 
            LEFT JOIN booking_items bi ON bi.item_id = rb.booking_item 
            LEFT JOIN booking b ON b.id = rb.booking_id 
            LEFT JOIN guest g ON g.id = b.guest_id 
        WHERE ($1 IS NULL OR rb.room_id = $1) AND (b.status IN ('R','C','I')) AND NOT rb.checked_in 
        UNION ALL
        SELECT 
            case o.ooo_type 
                when 'OOO' then 'Out Of Order' 
                when 'OOS' then 'Out Of Service' 
                when 'OOI' then 'Out Of Inventory' 
            end, 
            case o.ooo_type 
                when 'OOO' then 4 
                when 'OOS' then 5 
                when 'OOI' then 6 
            end, 
            o.room_id, NULL, NULL, NULL, NULL, NULL, o.id, rs."name", o.start_date, o.end_date, NULL, null
        FROM out_of_order o LEFT JOIN ooo_reason rs ON rs.id = o.reason_id 
        WHERE ($1 IS NULL OR o.room_id = $1)
    )
    SELECT r.room_number, a.activity_type, a.activity_code, a.room_id, a.booking_id, a.booking_item, a.block_id, 
           a.register_id, a.guest_id, a.folio_id, a.ooo_id, a.name, a.start_date, a.end_date, is_more_guest
    FROM activity a LEFT JOIN room r ON r.id = a.room_id
    WHERE i_start_date is null OR i_end_date is null 
          OR is_overlapped(i_start_date, i_end_date-1, a.start_date, a.end_date-1) 
--        or (i_start_date between a.start_date and a.end_date-1)
--        or (a.start_date between i_start_date and i_end_date-1)
    ORDER BY room_number, activity_code, start_date desc

sp_get_room_activity

Parameters

Definition