aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN (DEFAULT NULL)
booking_id	integer	IN (DEFAULT NULL)
booking_item	integer	IN (DEFAULT NULL)

Definition

SELECT 
        CASE 
            WHEN booking_id IS NOT NULL THEN (
                SELECT COALESCE(json_agg(A), '[]') 
                FROM (select 
                        bi.item_id AS id, 
                        fn_accommodation(rt.code, bt.code::text, bi.qty, rp.name, arrival, departure) AS description,
                        bi.extra_adult as extra_adult_qty,
                        bi.extra_child as extra_child_qty,
                        bi.extra_infant as extra_infant_qty,
                        (SELECT json_agg(rates) 
                         FROM (
                            SELECT bi.item_id::TEXT||'-'||to_char(charge_date,'YYYYMMDD') AS "key",
                                br.charge_date AS "date",
                                --fn_date_range(br.start_date, br.end_date) AS "date", 
                                br.rateplan_id,
                                br.mealtype_id, 
                                mt.name as mealtype_name, 
                                bm.name as mealtype_name_default,
                                br.room_rate, 
                                br.extrabed_rate AS extra_adult, br.child_rate AS extra_child, br.infant_rate AS extra_infant, br.comp, br.comp_abf
                            FROM booking_rates br 
                                left join meal_type mt on mt.id = br.mealtype_id
                            WHERE br.booking_item = bi.item_id 
                            ORDER BY 1
                        ) AS rates) AS rate_schedule
                      from booking_items bi left join rate_plan rp on rp.id = bi.rateplan_id 
                        left join room_type rt on rt.id = bi.roomtype_id 
                        left join bed_type bt on bt.id = bi.bedtype_id 
                        left join meal_type bm on bm.id = bi.mealtype_id
                      where (bi.booking_id = $2) AND ($3 IS NULL OR bi.item_id = $3)
                      ORDER BY bi.item_id
                ) AS A)
            ELSE (
                SELECT COALESCE(json_agg(A), '[]') 
                FROM (select rg.id, 
                        fn_accommodation('Room '||rm.room_number, null, null, rp.name, rg.arrival, rg.departure) AS description ,
                        rg.extra_adult as extra_adult_qty,
                        rg.extra_child as extra_child_qty,
                        rg.extra_infant as extra_infant_qty,
                        (SELECT json_agg(rates) 
                         FROM (
                            SELECT rr.register_id::TEXT||'-'||to_char(charge_date,'YYYYMMDD') AS "key",
                                rr.charge_date AS "date", 
                                rr.rateplan_id,
                                rr.mealtype_id, 
                                mt.name as mealtype_name, 
                                bm.name as mealtype_name_default,
                                rr.room_rate, 
                                rr.extrabed_rate AS extra_adult, rr.child_rate AS extra_child, rr.infant_rate AS extra_infant, rr.comp, rr.comp_abf
                            FROM registration_rates rr 
                                left join meal_type mt on mt.id = rr.mealtype_id
                            WHERE rr.register_id = $1
                            ORDER BY 1
                        ) AS rates) AS rate_schedule
                      from registration rg 
                        LEFT JOIN rate_plan rp on rp.id = rg.rateplan_id
                        LEFT JOIN room rm ON rm.id = rg.room_id 
                        LEFT JOIN room_type rt on rt.id = rm.roomtype_id 
                        left join meal_type bm on bm.id = rg.mealtype_id
                      where rg.id = $1 
                    ) AS A
                )
        END

sp_get_room_charge_schedule

Parameters

Definition