aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN
end_date	date	IN
inclusion_id	integer	IN (DEFAULT NULL)
charge_date	date	OUT
inclusion	text	OUT
inclusion_id	integer	OUT
room	integer	OUT
qty	integer	OUT
amount	t_money	OUT
inh_room	integer	OUT
inh_qty	integer	OUT
inh_amount	t_money	OUT
res_room	integer	OUT
res_qty	integer	OUT
res_amount	t_money	OUT
ind_room	integer	OUT
ind_qty	integer	OUT
ind_amount	t_money	OUT
ind_inh_room	integer	OUT
ind_inh_qty	integer	OUT
ind_inh_amount	t_money	OUT
ind_res_room	integer	OUT
ind_res_qty	integer	OUT
ind_res_amount	t_money	OUT
grp_room	integer	OUT
grp_qty	integer	OUT
grp_amount	t_money	OUT
grp_inh_room	integer	OUT
grp_inh_qty	integer	OUT
grp_inh_amount	t_money	OUT
grp_res_room	integer	OUT
grp_res_qty	integer	OUT
grp_res_amount	t_money	OUT

Definition

WITH rsv_charge_detail_per_room AS (
        SELECT 
            cs.charge_date,
            inc.id as inc_id,
            inc.name as inclusion,
            cs.register_id, 
            cs.booking_id,
            b.group_id, 
            (bi.qty - bi.inhouse_qty) AS room,
            fn_get_request_qty(bi.qty - bi.inhouse_qty, bi.adult, bi.child, bi.infant, gr.qty, gr.multiplier) AS qty,
            charge_amount
        FROM charge_schedule cs
            LEFT JOIN guest_request gr ON gr.id = cs.request_id
            LEFT JOIN booking b ON b.id = cs.booking_id
            LEFT JOIN booking_items bi ON (bi.booking_id = gr.booking_id)
            LEFT JOIN inclusion inc ON inc.id = gr.inclusion_id   
        WHERE (gr.multiplier <> 1) AND (b.status IN ('I','R','C')) AND cs.register_id IS NULL
            AND (cs.charge_date BETWEEN $1 and $2) 
            AND (inc.inclusion_type = 'meal') 
            AND (inc.id = $3 OR $3 IS NULL)
           
    ) , rsv_meals_per_room AS (
        SELECT charge_date, inc_id, inclusion, register_id, booking_id, group_id, sum(room) as room, sum(qty) as qty, sum(qty * charge_amount) AS charge_amount
        FROM rsv_charge_detail_per_room
        WHERE qty > 0
        GROUP BY 1,2,3,4,5,6
        
    ), rsv_meals_per_booking AS (
        SELECT 
            cs.charge_date,
            inc.id as inc_id,
            inc.name as inclusion,
            cs.register_id, 
            cs.booking_id,
            b.group_id, 
            sum(bi.qty - bi.inhouse_qty) AS room,
            sum(gr.qty) AS qty, 
            sum(cs.total_amount) AS charge_amount    
        FROM charge_schedule cs
            LEFT JOIN guest_request gr ON gr.id = cs.request_id
            LEFT JOIN booking b ON b.id = cs.booking_id
            LEFT JOIN booking_items bi ON (bi.booking_id = gr.booking_id)
            LEFT JOIN inclusion inc ON inc.id = gr.inclusion_id   
        WHERE (gr.multiplier = 1) AND (b.status IN ('I','R','C')) AND cs.register_id IS NULL
            AND (cs.charge_date BETWEEN $1 and $2) 
            AND (inc.inclusion_type = 'meal') 
            AND (inc.id = $3 OR $3 IS NULL)
        GROUP BY 1,2,3,4,5,6    
        
    ), inh_meals AS (
        SELECT 
            cs.charge_date,
            inc.id as inc_id,
            inc.name as inclusion,
            cs.register_id,
            NULL::int AS booking_id,
            r.group_id,
            count (distinct r.room_id) as room,
            sum(cs.charge_qty) as qty,
            sum(cs.total_amount) AS charge_amount        
        FROM charge_schedule cs
            LEFT JOIN registration r ON r.id = cs.register_id
            LEFT JOIN guest_request gr ON gr.id = cs.request_id
            LEFT JOIN inclusion inc ON inc.id = gr.inclusion_id    
        WHERE r.status = 'I' AND cs.booking_id IS NULL
            AND (cs.charge_date BETWEEN $1 and $2)
            AND (inc.inclusion_type = 'meal')
            AND (inc.id = $3 OR $3 IS NULL)           
        GROUP BY 1,2,3,4,5,6
        
    ), all_meals AS (
        SELECT *, TRUE AS is_booking, FALSE AS is_inhouse, (group_id is NOT NULL) AS is_group FROM rsv_meals_per_room
        UNION ALL 
        SELECT *, TRUE, FALSE, (group_id is NOT NULL) AS is_group FROM rsv_meals_per_booking        
        UNION ALL 
        SELECT *, FALSE, TRUE, (group_id is NOT NULL) AS is_group FROM inh_meals  
        
    ), all_dates AS (
        SELECT fn_date_range(min_date, max_date) AS charge_date, inc_id, inclusion, 
            NULL::int, NULL::int, NULL::int, 0, 0, 0.0, FALSE, FALSE, FALSE                 
        FROM (
            SELECT DISTINCT inc_id, inclusion, min(charge_date) AS min_date, max(charge_date) AS max_date 
            FROM all_meals
            GROUP BY 1,2
        ) AS A
    ) 
    
    SELECT 
        charge_date,
        inclusion,
        inc_id,
        sum(room)::int as room,
        sum(qty)::int as qty,
        sum(charge_amount)::t_money as amount,
        sum(IIF(is_inhouse, room, 0))::int as inh_room,
        sum(IIF(is_inhouse, qty,  0))::int as inh_qty,
        sum(IIF(is_inhouse, charge_amount, 0))::t_money as inh_amount,
        sum(IIF(is_booking, room, 0))::int as res_room,
        sum(IIF(is_booking, qty,  0))::int as res_qty,
        sum(IIF(is_booking, charge_amount, 0))::t_money as res_amount,
        sum(IIF(NOT is_group, room, 0))::int as ind_room,
        sum(IIF(NOT is_group, qty,  0))::int as ind_qty,
        sum(IIF(NOT is_group, charge_amount, 0))::t_money as ind_amount,
        sum(IIF(NOT is_group and is_inhouse, room, 0))::int as ind_inh_room,
        sum(IIF(NOT is_group and is_inhouse, qty,  0))::int as ind_inh_qty,
        sum(IIF(NOT is_group and is_inhouse, charge_amount, 0))::t_money as ind_inh_amount,
        sum(IIF(NOT is_group and is_booking, room, 0))::int as ind_res_room,
        sum(IIF(NOT is_group and is_booking, qty,  0))::int as ind_res_qty,
        sum(IIF(NOT is_group and is_booking, charge_amount, 0))::t_money as ind_res_amount,
        sum(IIF(is_group, room, 0))::int as grp_room,
        sum(IIF(is_group, qty,  0))::int as grp_qty,
        sum(IIF(is_group, charge_amount, 0))::t_money as grp_amount,
        sum(IIF(is_group and is_inhouse, room, 0))::int as grp_inh_room,
        sum(IIF(is_group and is_inhouse, qty,  0))::int as grp_inh_qty,
        sum(IIF(is_group and is_inhouse, charge_amount, 0))::t_money as grp_inh_amount,
        sum(IIF(is_group and is_booking, room, 0))::int as grp_res_room,
        sum(IIF(is_group and is_booking, qty,  0))::int as grp_res_qty,   
        sum(IIF(is_group and is_booking, charge_amount, 0))::t_money as grp_res_amount
    FROM (
        SELECT * FROM all_meals
        UNION ALL 
        SELECT * FROM all_dates
    ) AS X
    GROUP BY charge_date, inclusion, inc_id
    ORDER BY inc_id, charge_date

sp_mealplan_forecast

Parameters

Definition