aonestar.public

Parameters

Name	Type	Mode
rate_id	integer	IN
arrival	date	IN
departure	date	IN
group_same_rates	boolean	IN (DEFAULT false)
rate_date	date	OUT
start_date	date	OUT
end_date	date	OUT
rate_amount	t_money	OUT
stop_sell	boolean	OUT
cta	boolean	OUT
ctd	boolean	OUT
min_stay	smallint	OUT
max_stay	smallint	OUT
extra_adult	t_money	OUT
extra_child	t_money	OUT
extra_infant	t_money	OUT
comp	boolean	OUT
comp_abf	boolean	OUT
comp_lunch	boolean	OUT
comp_dinner	boolean	OUT
room_charge_dept	integer	OUT
available	boolean	OUT
restricted	boolean	OUT
roomtype_id	integer	IN
rateplan_id	integer	IN
arrival	date	IN
departure	date	IN
group_same_rates	boolean	IN (DEFAULT false)
rate_id	integer	OUT
rate_date	date	OUT
start_date	date	OUT
end_date	date	OUT
rate_amount	t_money	OUT
stop_sell	boolean	OUT
cta	boolean	OUT
ctd	boolean	OUT
min_stay	smallint	OUT
max_stay	smallint	OUT
extra_adult	t_money	OUT
extra_child	t_money	OUT
extra_infant	t_money	OUT
comp	boolean	OUT
comp_abf	boolean	OUT
comp_lunch	boolean	OUT
comp_dinner	boolean	OUT
room_charge_dept	integer	OUT
available	boolean	OUT
restricted	boolean	OUT

Definition

DECLARE 
    nights int;
--    def_rate_amount t_money;
--    def_extra_adult t_money;
--    def_extra_child t_money;
--    def_extra_infant t_money;
BEGIN
    IF group_same_rates THEN
        RETURN QUERY 
        WITH ranges AS (
            SELECT s.rate_id, s.start_date id, s.rate_amount, s.start_date AS rate_date,
                   CASE WHEN (lag(s.rate_amount) OVER (ORDER BY s.start_date) = s.rate_amount)
                        THEN NULL 
                        ELSE 1 
                   END flag
            FROM sp_get_rate_schedule($1, $2, $3, $4, false) s 
        )
        , groups AS (
            SELECT r.rate_id, r.id, r.rate_amount, r.rate_date, flag, sum(flag) OVER (ORDER BY r.rate_date) grp
            FROM ranges R
        )
        SELECT g.rate_id, NULL::date, min(g.rate_date) start_date,
               max(g.rate_date) end_date,
               min(g.rate_amount)::t_money rate_amount, 
               NULL::bool, null::bool, null::bool, NULL::smallint, NULL::smallint, 
               NULL::t_money, NULL::t_money, NULL::t_money, NULL::bool, NULL::bool, NULL::bool, NULL::bool, NULL::int, 
               null::bool, null::bool
        FROM GROUPS G
        GROUP BY g.rate_id, grp
        ORDER BY start_date;    
    ELSE    
        rate_id := (SELECT rd.rate_id FROM rate_details rd WHERE rd.roomtype_id = $1 AND rd.rateplan_id = $2);
        
        IF rate_id IS NOT NULL THEN          
            RETURN QUERY
            SELECT rate_id, rs.*
            FROM sp_get_rate_schedule(rate_id, arrival, departure, group_same_rates) AS rs;
        ELSE 
            RETURN QUERY
            SELECT NULL::int, adate, adate, adate, rt.room_rate, 
                NULL::bool, null::bool, null::bool, NULL::smallint, NULL::smallint, 
                COALESCE(rt.extrabed_rate, def.extra_adult), 
                COALESCE(rt.child_rate,    def.extra_child), 
                COALESCE(rt.infant_rate,   def.extra_infant),
                NULL::bool, NULL::bool, NULL::bool, NULL::bool, NULL::int, NULL::bool, NULL::bool
            FROM room_type rt, 
                LATERAL fn_date_range(arrival, iif(arrival = departure, departure, departure-1)) adate,
                LATERAL (SELECT 
                            fn_sys_param('ROOM_RATE', 'EXTRA_ADULT', 0.0)::t_money AS extra_adult,
                            fn_sys_param('ROOM_RATE', 'EXTRA_CHILD', 0.0)::t_money AS extra_child,
                            fn_sys_param('ROOM_RATE', 'EXTRA_INFANT', 0.0)::t_money AS extra_infant) def 
            WHERE rt.id = $1;
        END IF;
    END IF; 
END