aonestar.public

Parameters

Name	Type	Mode
booking_id	integer	IN (DEFAULT NULL)
booking_item	integer	IN (DEFAULT NULL)
single_room	boolean	IN (DEFAULT true)

Definition

declare 
    json_result jsonb;
    _arrival date;
    _departure date;
begin
    if (booking_id is null) and (booking_item is null) then
        return null;
    end if;

    booking_id := IIF(booking_item is not null, null, booking_id); 

    select arrival, departure 
    from booking where id = $1
    into _arrival, _departure;

    WITH BI AS (
        SELECT bi.item_id, bi.roomtype_id, rt.code AS roomtype_code, bi.rateplan_id, bi.mealtype_id, 
            bi.rate_id, bi.arrival, bi.departure, IIF(SINGLE_ROOM, 1, bi.qty) as qty, block_qty, inhouse_qty,
            rp."name" AS rateplan_name, mt.name as mealtype_name,
            bi.adult as total_adult, bi.child as total_child, bi.infant as total_infant, 
            bi.included_adult as include_adult, bi.included_child as include_child, bi.included_infant as include_infant,
            bi.extra_adult, bi.extra_child, bi.extra_infant, bi.booking_seq AS seq          
        FROM booking_items bi
           LEFT JOIN room_type rt ON rt.id = bi.roomtype_id 
           LEFT JOIN rate_plan rp ON rp.id = bi.rateplan_id 
           LEFT JOIN meal_type mt ON mt.id = coalesce(bi.mealtype_id, rp.mealtype_id)
        WHERE ($2 is not null and bi.item_id = $2) or ($2 is null and bi.booking_id = $1)  
    ), rate_schedule AS (
        SELECT bi.item_id, bi.roomtype_id, bi.roomtype_code, bi.rateplan_id, bi.rate_id, bi.qty, 
            bi.arrival, bi.departure,  bi.extra_adult, bi.extra_child, bi.extra_infant, 
            bi.include_adult, bi.include_child, bi.include_infant, bi.total_adult, bi.total_child, bi.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, bi.mealtype_id) as mealtype_id
        FROM BI left join booking_rates rs on rs.booking_item = bi.item_id 
    ), BR AS (  
        /* Room Charge */
        SELECT 0 AS charge_type, rs.item_id AS booking_item, rs.roomtype_id, rs.roomtype_code AS roomtype, rs.qty, IIF(rs.comp, 0, rs.amount) AS room_rate, rs.comp, 
               count(rs."date")::int AS night 
        FROM rate_schedule AS rs --LEFT JOIN bi ON bi.booking_item = rs.item_id
        GROUP BY 2,3,4,5,6,7
        
        UNION ALL /* Extra Adult */
        SELECT 1 AS charge_type, rs.item_id AS booking_item, NULL, 'Extra Adult', rs.extra_adult*rs.qty, 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 2,5,6
        
        UNION ALL /* Extra Child */
        SELECT 2 AS charge_type, rs.item_id AS booking_item, NULL, '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 2,5,6
        
        UNION ALL /* Extra Infant */
        SELECT 3 AS charge_type, rs.item_id AS booking_item, NULL, '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 2,5,6

    ), RQ AS (
        SELECT req.booking_item, 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 (($2 is not null and req.booking_item = $2) or ($2 is null and req.booking_id = $1)) 
                AND COALESCE(req.inclusive, false) = false
        UNION ALL 
        SELECT bi.item_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 bi, LATERAL sp_get_rate_inclusions(bi.rateplan_id) AS inc
        --WHERE recalc_inclusive --AND (inc.multiplier <> 1 OR bi.seq = 1)
    ), 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 (
            /* requirement per booking */
            SELECT 0 AS seq, rq.inclusion_id, rq.charge_rate, rq.frequency, rq.qty,                                
                fn_get_request_nights(rq.frequency, _arrival, _departure, rq.start_date, rq.end_date) AS night, COALESCE(inclusive,FALSE) AS inclusive  
            FROM RQ             
            WHERE (rq.charge_type = 1) and ($1 is not null) and ($2 is null)
            /* requirement per room */
            UNION ALL 
            SELECT min(bi.item_id) AS seq, rq.inclusion_id, rq.charge_rate, rq.frequency, 
                sum(fn_get_request_qty(rq.charge_type, inclusive, bi.qty, rq.qty, bi.include_adult, bi.include_child, bi.include_infant, bi.extra_adult, bi.extra_child, bi.extra_infant))::int AS qty,
                fn_get_request_nights(rq.frequency, bi.arrival, bi.departure, rq.start_date, rq.end_date) AS night, COALESCE(inclusive,FALSE)  
            FROM RQ LEFT JOIN BI on (bi.item_id = rq.booking_item OR rq.booking_item IS NULL)        
            WHERE rq.charge_type <> 1
            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,18       
        ) 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, a.seq
            ) other_charges
        FROM REQUESTS AS A
        WHERE a.qty > 0 AND NOT inclusive     
    ), SUMM AS (
        SELECT min(rateplan_name) AS rate_plan, 
            min(mealtype_name) as meal, 
            min(arrival) arrival, max(departure) departure, max(departure)-min(arrival) nights, sum(qty) room_qty,             
            sum(qty * total_adult) adult, 
            sum(qty * total_child) child, 
            sum(qty * total_infant) infant, 
            sum(qty * include_adult) as include_adult, 
            sum(qty * include_child) as include_child, 
            sum(qty * include_infant) as include_infant,
            sum(qty * extra_adult) as extra_adult, 
            sum(qty * extra_child) as extra_child, 
            sum(qty * extra_infant) as extra_infant,
            sum(block_qty) block_qty, 
            sum(inhouse_qty) inhouse_qty
        FROM BI  
    ), DP AS (
        SELECT COALESCE(sum(amount),0.0) AS total_deposit from deposit d where d.booking_id = $1 and d.status <> 'X'
    ), DATASET AS (
        SELECT 
            $1 in_booking_id, 
            $2 in_booking_item, SUMM.*, 
            total_room_charge, 
            total_other_charge,
            total_room_charge + total_other_charge AS total_charge,
            total_deposit as deposit,
            (total_room_charge + total_other_charge - total_deposit) AS balance,
            COALESCE(accommodations,'[]') AS accommodations,
            COALESCE(REQ_SUM.other_charges,'[]') AS other_charges
        FROM SUMM, ACCOM, REQ_SUM, DP
    )
    SELECT row_to_json(DATASET)::jsonb FROM DATASET
    INTO json_result;

    RETURN json_result;
END

fn_booking_summary_js

Parameters

Definition