aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN (DEFAULT NULL)

Definition

declare json_result jsonb;
begin

WITH RG AS (
        SELECT rg.id as register_id, rm.room_number, 
            rm.roomtype_id, rt.code AS roomtype_code, rg.rateplan_id, rg.mealtype_id, 
            rg.rate_id, 1 as qty, rg.arrival, rg.departure, --0 as block_qty, 1 as inhouse_qty,
            rp."name" AS rateplan_name, mt.name as mealtype_name,
            rg.adult as total_adult, rg.child as total_child, rg.infant as total_infant, 
            rg.included_adult as include_adult, 
            rg.child  - iif(rg.extra_child > rg.child, 0, rg.extra_child) as include_child, 
            rg.infant - iif(rg.extra_infant > rg.infant, 0, rg.extra_infant) as include_infant,
            rg.extra_adult, 
            iif(rg.extra_child > rg.child, 0, rg.extra_child) as extra_child, 
            iif(rg.extra_infant > rg.infant, 0, rg.extra_infant) as extra_infant
        FROM registration rg
           LEFT JOIN room rm on rm.id = rg.room_id 
           LEFT JOIN room_type rt ON rt.id = rm.roomtype_id 
           LEFT JOIN rate_plan rp ON rp.id = rg.rateplan_id 
           LEFT JOIN meal_type mt ON mt.id = coalesce(rg.mealtype_id, rp.mealtype_id)
        WHERE (RG.id = $1)  
    ), rate_schedule AS (
        SELECT rg.register_id, rg.roomtype_id, rg.roomtype_code, rg.rateplan_id, rg.rate_id, rg.qty, 
            rg.arrival, rg.departure,  rg.extra_adult, rg.extra_child, rg.extra_infant, 
            rg.include_adult, rg.include_child, rg.include_infant, rg.total_adult, rg.total_child, rg.total_infant,
            rs.charge_date as "date", 
            rs.room_rate as amount, 
            rs.extrabed_rate as extra_adult_rate, 
            rs.child_rate as extra_child_rate, 
            rs.infant_rate as extra_infant_rate, 
            rs.comp, rs.comp_abf,
            coalesce(rs.mealtype_id, rg.mealtype_id) as mealtype_id
        FROM RG left join registration_rates rs on rs.register_id = rg.register_id 
    ), BR AS (  
        /* Room Charge */
        SELECT 0 AS charge_type, rs.roomtype_code AS roomtype, 1 AS qty, IIF(rs.comp, 0, rs.amount) AS room_rate, rs.comp, count(rs."date")::int AS night 
        FROM rate_schedule AS rs 
        GROUP BY 2,4,5
        
        UNION ALL /* Extra Adult */
        SELECT 1 AS charge_type, 'Extra Adult', rs.extra_adult, rs.extra_adult_rate, false, count(rs."date")::int night
        FROM rate_schedule AS rs
        WHERE rs.extra_adult > 0 AND rs.extra_adult_rate > 0
        GROUP BY 3,4,5
        
        UNION ALL /* Extra Child */
        SELECT 2 AS charge_type, 'Extra Child', rs.extra_child*rs.qty, rs.extra_child_rate, false, count(rs."date")::int night
        FROM rate_schedule AS rs
        WHERE rs.extra_child > 0 AND rs.extra_child_rate > 0 
        GROUP BY 3,4,5
        
        UNION ALL /* Extra Infant */
        SELECT 3 AS charge_type, 'Extra Infant', rs.extra_infant*rs.qty, rs.extra_infant_rate, false, count(rs."date")::int night
        FROM rate_schedule AS rs
        WHERE rs.extra_infant > 0 AND rs.extra_infant_rate > 0 
        GROUP BY 3,4,5

), RQ AS (
        SELECT req.register_id, req.id, req.inclusion_id, req.multiplier AS "charge_type",
           req.frequency, req.charge_dept, req.charge_rate, req.qty, req.start_date, req.end_date, false AS inclusive   
        FROM guest_request req
        WHERE (req.register_id = $1) AND COALESCE(req.inclusive, false) = false
        UNION ALL 
        SELECT rg.register_id, NULL, inc.inclusion_id, inc.multiplier AS "charge_type", inc.frequency, 
            inc.charge_dept, inc.charge_rate, inc.qty, 
            NULL AS start_date, NULL AS end_date, TRUE AS inclusive     
        FROM RG, lateral sp_get_rate_inclusions(rg.rateplan_id) AS inc
    ), REQUESTS AS (
        SELECT inc.name, inc.inclusion_type, 
            fn_accommodation(inc.name, a.qty, a.charge_rate, iif(a.frequency IN (3,4), NULL, a.night)) AS description, 
            A.*
        FROM (
            SELECT rq.inclusion_id, rq.charge_rate, rq.frequency, 
                sum(fn_get_request_qty(rq.charge_type, inclusive, 1, rq.qty, rg.include_adult, rg.include_child, rg.include_infant, rg.extra_adult, rg.extra_child, rg.extra_infant))::int AS qty,
                fn_get_request_nights(rq.frequency, rg.arrival, rg.departure, rq.start_date, rq.end_date) AS night, COALESCE(inclusive,FALSE) as inclusive
            FROM RQ, RG  
            --WHERE rq.charge_type <> 1 <-- First room of individual booking may has Per-booking request
            GROUP BY rq.inclusion_id, rq.charge_rate, rq.frequency, rq.qty, rq.charge_type, night, inclusive    
        ) A LEFT JOIN inclusion inc ON inc.id = a.inclusion_id 
    ), meals as (
        SELECT nights::smallint as nights, abf, abf_child, abf_extra_adult, abf_extra_child, 
           lunch, lunch_child, lunch_extra_adult, lunch_extra_child, 
           dinner, dinner_child, dinner_extra_adult, dinner_extra_child,
           sum(iif(mt.abf is null, 0, qty * include_adult))::int as abf_adult_qty,
           sum(iif(mt.abf_child is null, 0, qty * include_child))::int as abf_child_qty, 
           sum(iif(mt.abf_extra_adult is null, 0, qty * extra_adult))::int as abf_extra_adult_qty, 
           sum(iif(mt.abf_extra_child is null, 0, qty * extra_child))::int as abf_extra_child_qty,
           sum(iif(mt.lunch is null, 0, qty * include_adult))::int as lunch_adult_qty,
           sum(iif(mt.lunch_child is null, 0, qty * include_child))::int as lunch_child_qty, 
           sum(iif(mt.lunch_extra_adult is null, 0, qty * extra_adult))::int as lunch_extra_adult_qty, 
           sum(iif(mt.lunch_extra_child is null, 0, qty * extra_child))::int as lunch_extra_child_qty,
           sum(iif(mt.dinner is null, 0, qty * include_adult))::int as dinner_adult_qty,
           sum(iif(mt.dinner_child is null, 0, qty * include_child))::int as dinner_child_qty, 
           sum(iif(mt.dinner_extra_adult is null, 0, qty * extra_adult))::int as dinner_extra_adult_qty, 
           sum(iif(mt.dinner_extra_child is null, 0, qty * extra_child))::int as dinner_extra_child_qty
        from (
            SELECT abf, abf_child, abf_extra_adult, abf_extra_child, 
               lunch, lunch_child, lunch_extra_adult, lunch_extra_child, 
               dinner, dinner_child, dinner_extra_adult, dinner_extra_child,  
               rs.qty, rs.include_adult, include_child, rs.extra_adult, rs.extra_child, --rs.item_id, 
               count(rs."date")::int as nights
            FROM rate_schedule AS rs left join meal_type m on m.id = rs.mealtype_id    
            GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17       
        ) as MT
        group by 1,2,3,4,5,6,7,8,9,10,11,12,13
    ), ACCOM AS (
        SELECT sum(amount) AS total_room_charge,
               json_agg(json_build_object(
                          'description', description,
                          'amount', amount
                        )
                       ORDER BY charge_type, description
               ) accommodations
        FROM (
              /* Room and Extrabed */
              SELECT charge_type, fn_accommodation(roomtype, qty, room_rate, night, comp => br.comp) AS description, room_rate, night, 
                     sum(qty)::int AS qty , 
                     sum(qty) * room_rate * night AS amount
              FROM BR
              GROUP BY 1,2,3,4

UNION ALL /* ABF */
              SELECT 4 AS charge_type, fn_accommodation('Breakfast', abf_adult_qty, abf, nights)||' [INCLUSIVE]', 
                    abf as charge_rate, nights as night, abf_adult_qty as qty, null::t_money as amount --abf_adult_qty*abf*nights as amount  
              FROM meals WHERE abf_adult_qty > 0
              UNION ALL /* ABF Child */
              SELECT 5 AS charge_type, fn_accommodation('Breakfast-Child', abf_child_qty, abf_child, nights)||' [INCLUSIVE]', 
                    abf_child, nights, abf_child_qty, null as amount --abf_child_qty*abf_child*nights as amount  
              FROM meals WHERE abf_child_qty > 0       
              
              UNION ALL /* Lunch */
              SELECT 6 AS charge_type, fn_accommodation('Lunch', lunch_adult_qty, lunch, nights)||' [INCLUSIVE]', 
                    lunch, nights, lunch_adult_qty, null as amount --lunch_adult_qty*lunch*nights as amount  
              FROM meals WHERE lunch_adult_qty > 0
              UNION ALL /* Lunch Child */
              SELECT 7 AS charge_type, fn_accommodation('Lunch-Child', lunch_child_qty, lunch_child, nights)||' [INCLUSIVE]', 
                    lunch_child, nights, lunch_child_qty, null as amount --lunch_child_qty*lunch_child*nights as amount  
              FROM meals WHERE lunch_child_qty > 0       
              
              UNION ALL /* Dinner */
              SELECT 8 AS charge_type, fn_accommodation('Dinner', dinner_adult_qty, dinner, nights)||' [INCLUSIVE]', 
                    dinner, nights, dinner_adult_qty, null as amount --dinner_adult_qty*dinner*nights as amount  
              FROM meals WHERE dinner_adult_qty > 0
              UNION ALL /* Dinner Child */
              SELECT 9 AS charge_type, fn_accommodation('Dinner-Child', dinner_child_qty, dinner_child, nights)||' [INCLUSIVE]', 
                    dinner_child, nights, dinner_child_qty, null as amount --dinner_child_qty*dinner_child*nights as amount  
              FROM meals WHERE dinner_child_qty > 0          
              
              UNION ALL /* Extra ABF */
              SELECT 10 AS charge_type, fn_accommodation('Breakfast', abf_extra_adult_qty, abf_extra_adult, nights)||' [EXTRA]', 
                     abf_extra_adult, nights, abf_extra_adult_qty, null as amount --abf_extra_adult_qty*abf_extra_adult*nights as amount  
              FROM meals WHERE abf_extra_adult_qty > 0
              UNION ALL /* Extra ABF Child */
              SELECT 11 AS charge_type, fn_accommodation('Breakfast-Child', abf_extra_child_qty, abf_extra_child, nights)||' [EXTRA]', 
                     abf_extra_child, nights, abf_extra_child_qty, null as amount --abf_extra_child_qty*abf_extra_child*nights as amount  
              FROM meals WHERE abf_extra_child_qty > 0       
              
              UNION ALL /* Extra Lunch */
              SELECT 12 AS charge_type, fn_accommodation('Lunch', lunch_extra_adult_qty, lunch_extra_adult, nights)||' [EXTRA]', 
                    lunch_extra_adult, nights, lunch_extra_adult_qty, null as amount --lunch_extra_adult_qty*lunch_extra_adult*nights as amount  
              FROM meals WHERE lunch_extra_adult_qty > 0
              UNION ALL /* Extra Lunch Child */
              SELECT 13 AS charge_type, fn_accommodation('Lunch-Child', lunch_extra_child_qty, lunch_extra_child, nights)||' [EXTRA]', 
                    lunch_extra_child, nights, lunch_extra_child_qty, null as amount --lunch_extra_child_qty*lunch_extra_child*nights as amount  
              FROM meals WHERE lunch_extra_child_qty > 0       
              
              UNION ALL /* Extra Dinner */
              SELECT 14 AS charge_type, fn_accommodation('Dinner', dinner_extra_adult_qty, dinner_extra_adult, nights)||' [EXTRA]', 
                    dinner_extra_adult, nights, dinner_extra_adult_qty, null as amount --dinner_extra_adult_qty*dinner_extra_adult*nights as amount  
              FROM meals WHERE dinner_extra_adult_qty > 0
              UNION ALL /* Extra Dinner Child */
              SELECT 15 AS charge_type, fn_accommodation('Dinner-Child', dinner_extra_child_qty, dinner_extra_child, nights)||' [EXTRA]', 
                    dinner_extra_child, nights, dinner_extra_child_qty, null as amount --dinner_extra_child_qty*dinner_extra_child*nights as amount  
              FROM meals WHERE dinner_extra_child_qty > 0

UNION ALL /* Other inclusive charges */  
              SELECT IIF(inclusion_type='meal', 16, 17), description||' [INCLUSIVE]', charge_rate, night, qty, NULL
              FROM REQUESTS
              WHERE inclusive AND qty > 0                                 
             ) AS R         
    ), REQ_SUM AS (
        SELECT COALESCE(sum(a.charge_rate * a.qty * a.night),0.0)::t_money AS total_other_charge,
            json_agg(
                json_build_object(
                    'description', a.description,
                    'amount', a.charge_rate * a.qty * a.night
                )
                ORDER BY a.name
            ) other_charges
        FROM REQUESTS AS A
        WHERE a.qty > 0 AND NOT inclusive     
    ), DATASET AS (
        SELECT rg.room_number, 
            rg.rateplan_name AS rate_plan, 
            rg.mealtype_name as meal, 
            rg.arrival, rg.departure, 
            rg.departure-rg.arrival nights,         
            rg.total_adult as adult, 
            rg.total_child as child, 
            rg.total_infant as infant, 
            rg.include_adult, 
            rg.include_child, 
            rg.include_infant,
            rg.extra_adult, 
            rg.extra_child, 
            rg.extra_infant, 
            total_room_charge, 
            total_other_charge,
            total_room_charge + total_other_charge AS total_charge,
            0.0 as deposit,
            (total_room_charge + total_other_charge) AS balance,
            COALESCE(accommodations,'[]') AS accommodations,
            COALESCE(REQ_SUM.other_charges,'[]') AS other_charges
        FROM RG, ACCOM, REQ_SUM
    )
    SELECT row_to_json(DATASET)::jsonb FROM DATASET
    INTO json_result;

RETURN json_result;
END

fn_inhouse_summary_js

Parameters

Definition