aonestar.public

Parameters

Name	Type	Mode
date_ref	date	IN (DEFAULT NULL)
recalculate	boolean	IN (DEFAULT false)
room_id	integer	OUT
room_number	t_room_number	OUT
room_status	character	OUT
hk_status	character	OUT
roomtype_id	integer	OUT
bedtype_id	integer	OUT
location_id	integer	OUT
building_id	integer	OUT
exposure_id	integer	OUT
register_id	integer	OUT
booking_id	integer	OUT
book_type	character	OUT
share_room	boolean	OUT
house_used	boolean	OUT
walk_in	boolean	OUT
comp	boolean	OUT
day_used	boolean	OUT
rateplan_id	integer	OUT
stay_type	character	OUT
start_date	date	OUT
end_date	date	OUT
in_house_qty	smallint	OUT
day_used_qty	smallint	OUT
adult	smallint	OUT
child	smallint	OUT
infant	smallint	OUT
extra_adult	smallint	OUT
extra_child	smallint	OUT
extra_infant	smallint	OUT
room_rate	t_money	OUT
extra_adult_rate	t_money	OUT
extra_child_rate	t_money	OUT
extra_infant_rate	t_money	OUT
promotion_id	integer	OUT
channel_id	integer	OUT
origin_id	integer	OUT
sales_id	integer	OUT
market_id	integer	OUT
rsvtype_id	integer	OUT
advance_book_period	integer	OUT
return_guest	boolean	OUT
guest_id	integer	OUT
guest_sex	character	OUT
guest_age	integer	OUT
guest_nation_id	integer	OUT
guest_lang_id	integer	OUT
guest_country_id	integer	OUT
guest_country_res_id	integer	OUT
guest_city_id	integer	OUT
guest_occupation_id	integer	OUT
guest_vip_id	integer	OUT
guest_name	text	OUT
guest_member_code	text	OUT
mealtype_id	integer	OUT
room_charge	t_money	OUT
rate_breakdown	jsonb	OUT

Definition

DECLARE system_date DATE = fn_system_date();
BEGIN
    date_ref = COALESCE(date_ref, system_date);
    IF (date_ref = system_date) OR recalculate THEN 
        RETURN query
        WITH rooms as (
            SELECT rm.id, rm.room_number, rs.status, rs.hk_status, rm.roomtype_id, rm.bedtype_id, rm.location_id, rm.building_id, rm.exposure_id
            FROM room rm LEFT JOIN room_status rs ON rs.room_id = rm.id
        ), rates as (
            SELECT * from sp_rate_schedule_breakdown(post_date := $1, status := 'I')
        ), in_house AS (
            SELECT rg.room_id, rm.room_number, 
                IIF(date_ref < system_date AND rg.arrival <> rg.departure, 'OCC', rm.status)::char(3) AS status, 
                IIF(date_ref < system_date, 'CL', rm.hk_status)::char(2) AS hk_status, 
                rm.roomtype_id, rm.bedtype_id, rm.location_id, rm.building_id, rm.exposure_id, 
                rg.id AS register_id, rg.booking_id, bk.book_type, rg.is_join_room AS share_room, rg.house_used, rg.walk_in, 
                COALESCE(rr.comp,FALSE) AS comp, rg.day_used, rg.rateplan_id, rg.stay_type, rg.arrival, rg.departure,                 
                IIF(rg.is_join_room OR rg.day_used, 0, 1)::SMALLINT AS in_house_qty, 
                IIF(rg.day_used, 1, 0)::SMALLINT AS day_used_qty, 
                rg.adult, rg.child, rg.infant, rg.extra_adult, rg.extra_child, rg.extra_infant, 
                rr.room_rate, rr.extrabed_rate, rr.child_rate, rr.infant_rate, rg.promotion_id, rg.channel_id, rg.origin_id, rg.sales_id, rg.market_id, 
                bk.rsvtype_id, bk.arrival-bk.book_date AS advance_book_period, (g.last_departure NOTNULL AND g.last_departure < rg.arrival) AS return_guest, 
                rg.guest_id, g.sex, EXTRACT(YEAR FROM age($1, g.birthdate))::int AS guest_age, g.nation_id, g.lang_id, g.country_id, g.country_res_id, 
                g.city_id, g.occupation_id, g.vip_id, g.full_name, g.member_code, rg.mealtype_id,
                r.room as room_charge,
                jsonb_build_object(
                    'room',         r.room,  
                    'abf',          r.abf_included,  
                    'lunch',        r.lunch_included,  
                    'dinner',       r.dinner_included,  
                    'inclusive',    r.inclusive,  
                    'extrabed',     r.extrabed,  
                    'abf_extra',    r.abf_extra,  
                    'lunch_extra',  r.lunch_extra , 
                    'dinner_extra', r.dinner_extra,
                    'other',        r.other  
                ) as rate_breakdown
            FROM registration rg 
                LEFT JOIN booking bk ON bk.id = rg.booking_id 
                LEFT JOIN guest g ON g.id = rg.guest_id 
                LEFT JOIN rooms rm ON rm.id  = rg.room_id 
                LEFT JOIN rates r ON r.register_id = rg.id
                LEFT JOIN registration_rates rr ON rr.register_id = rg.id AND rr.charge_date = $1
            WHERE ($1 >= system_date AND rg.status = 'I') 
                OR ($1 < system_date AND $1 BETWEEN rg.arrival AND rg.departure-1 AND rg.status <> 'X')
                OR ($1 = rg.arrival AND rg.arrival = rg.departure AND rg.status <> 'X')
        ), more_guest AS (
            SELECT rg.room_id, rg.room_number, rg.status, rg.hk_status, rg.roomtype_id, rg.bedtype_id, rg.location_id, rg.building_id, rg.exposure_id, 
                rg.register_id, rg.booking_id, rg.book_type, TRUE AS share_room, rg.house_used, rg.walk_in, rg.comp, rg.day_used, rg.rateplan_id, 
                rg.stay_type, rg.arrival, rg.departure, 0::SMALLINT, 0::SMALLINT, 0::SMALLINT, 0::SMALLINT, 0::SMALLINT, 0::SMALLINT, 0::SMALLINT, 0::SMALLINT, 
                0::t_money, 0::t_money, 0::t_money, 0::t_money, rg.promotion_id, rg.channel_id, rg.origin_id, rg.sales_id, rg.market_id, rg.rsvtype_id, 
                rg.advance_book_period, (g.last_departure NOTNULL AND g.last_departure < rg.arrival) AS return_guest, mg.guest_id, g.sex, 
                EXTRACT(YEAR FROM age($1, g.birthdate))::int AS guest_age, g.nation_id, g.lang_id, g.country_id, g.country_res_id, g.city_id, g.occupation_id, 
                g.vip_id, g.full_name, g.member_code, rg.mealtype_id, 0::t_money, null::jsonb
            FROM in_house AS rg 
                INNER JOIN register_guests mg ON rg.register_id = mg.register_id AND mg.guest_id <> rg.guest_id
                LEFT JOIN guest g ON g.id = mg.guest_id 
        ), OOO AS (
            SELECT rm.id AS room_id, rm.room_number, IIF(date_ref < system_date, oo.ooo_type, rm.status)::char(3), IIF(date_ref < system_date, rm.hk_status, 'CL')::char(2), 
                rm.roomtype_id, rm.bedtype_id, rm.location_id, rm.building_id, rm.exposure_id,
                NULL::int, NULL::int, NULL::char(1), NULL::bool, NULL::bool, NULL::bool, NULL::bool, NULL::bool, NULL::int, NULL::char(1), 
                oo.start_date, oo.end_date,
                NULL::int2, NULL::int2, NULL::int2, NULL::int2, NULL::int2, NULL::int2, NULL::int2, NULL::int2, 
                NULL::t_money, NULL::t_money, NULL::t_money, NULL::t_money, 
                NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::bool, NULL::int, NULL::char(1), 
                NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::text, NULL::text, 
                NULL::int, NULL::t_money, null::jsonb
            FROM out_of_order oo 
                INNER JOIN rooms rm ON rm.id = oo.room_id        
            WHERE $1 BETWEEN oo.start_date AND oo.end_date -1   
        ), VAC AS (
            SELECT rm.id, rm.room_number, 'VAC'::char(3) AS status, IIF(date_ref < system_date, rm.hk_status, 'CL')::char(2) AS hk_status, 
                rm.roomtype_id, rm.bedtype_id, rm.location_id, rm.building_id, rm.exposure_id,
                NULL::int, NULL::int, NULL::char(1), NULL::bool, NULL::bool, NULL::bool, NULL::bool, NULL::bool, NULL::int, NULL::char(1), NULL::date, NULL::date,
                NULL::int2, NULL::int2, NULL::int2, NULL::int2, NULL::int2, NULL::int2, NULL::int2, NULL::int2, 
                NULL::t_money, NULL::t_money, NULL::t_money, NULL::t_money, 
                NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::bool, NULL::int, NULL::char(1), 
                NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::int, NULL::text, NULL::text, 
                NULL::int, NULL::t_money, null::jsonb
            FROM rooms rm
            WHERE /*(date_ref = system_date AND rm.status = 'VAC')
                OR (date_ref < system_date 
                    AND */rm.id NOT IN(SELECT i.room_id FROM in_house AS I
                                       UNION 
                                       SELECT o.room_id FROM OOO AS O)
                --)
        ), dataset AS (
            SELECT * FROM in_house
            UNION ALL
            SELECT * FROM more_guest
            UNION ALL
            SELECT * FROM OOO
            UNION ALL
            SELECT * FROM VAC
        )
        SELECT * FROM dataset ORDER BY room_id, register_id, share_room ;
    
--      IF (date_ref = system_date) THEN
--          RETURN query 
--          SELECT rm.id, rm.room_number, rs.status, rs.hk_status, rm.roomtype_id, rm.bedtype_id, rm.location_id, rm.building_id, rm.exposure_id,
--              NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
--              oo.start_date, oo.end_date,
--              NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL::t_money, NULL::t_money, NULL::t_money, 
--              NULL::t_money, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
--              NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL--, NULL
--          FROM room rm LEFT JOIN room_status rs ON rs.room_id = rm.id
--              LEFT JOIN out_of_order oo ON oo.room_id = rm.id AND $1 BETWEEN oo.start_date AND oo.end_date -1
--          WHERE rm.status IN ('VAC','OOO','OOS','OOI')
--      ELSE
--          RETURN query 
--          SELECT rm.id, rm.room_number, oo.ooo_type, 'CL', rm.roomtype_id, rm.bedtype_id, rm.location_id, rm.building_id, rm.exposure_id,
--              NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
--              oo.start_date, oo.end_date,
--              NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL::t_money, NULL::t_money, NULL::t_money, 
--              NULL::t_money, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
--              NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL--, NULL
--          FROM out_of_order oo 
--              LEFT JOIN room rm ON oo.room_id = rm.id
--              LEFT JOIN room_status rs ON rs.room_id = rm.id               
--          WHERE $1 BETWEEN oo.start_date AND oo.end_date -1 
--      END IF;
    ELSE
        RETURN query
        SELECT sm.room_id, sm.room_number, sm.room_status, sm.hk_status, sm.roomtype_id, sm.bedtype_id, sm.location_id, sm.building_id, sm.exposure_id, 
            sm.register_id, sm.booking_id, sm.book_type, sm.share_room, sm.house_used, sm.walk_in, sm.comp, sm.day_used, sm.rateplan_id, sm.stay_type, 
            sm.start_date, sm.end_date, sm.in_house_qty, sm.day_used_qty, sm.adult, sm.child, sm.infant, sm.extra_adult, sm.extra_child, sm.extra_infant, 
            sm.room_rate, sm.extra_adult_rate, sm.extra_child_rate, sm.extra_infant_rate, sm.promotion_id, sm.channel_id, sm.origin_id, sm.sales_id, 
            sm.market_id, sm.rsvtype_id, sm.advance_book_period, sm.return_guest, sm.guest_id, sm.guest_sex, sm.guest_age, sm.guest_nation_id, sm.guest_lang_id, 
            sm.guest_country_id, sm.guest_country_res_id, sm.guest_city_id, sm.guest_occupation_id, sm.guest_vip_id, sm.guest_name, sm.guest_member_code,
            sm.mealtype_id, coalesce(sm.room_charge, sm.room_rate), sm.rate_breakdown
        FROM room_summary sm
        WHERE sm.date_ref = $1;
    END IF;
END

sp_get_room_summary

Parameters

Definition