aonestar.public

Parameters

Name	Type	Mode
booking_data	jsonb	IN
recalc_inclusive	boolean	IN (DEFAULT true)

Definition

DECLARE 
    json_result jsonb;
    _arrival date;
    _departure date;
    _room_id int;
    is_checkin bool;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
BEGIN
--    PERFORM sp_log_debug('sp_get_booking_summary_js', 'recalc_inclusive='||recalc_inclusive::TEXT||E'\n'||jsonb_pretty(booking_data));

    IF NOT booking_data ? 'details' THEN 
        RETURN '{}'::jsonb;
    END IF;

    recalc_inclusive := TRUE;

    _arrival   := (booking_data->>'arrival')::date;
    _departure := (booking_data->>'departure')::date;
    _room_id   := (booking_data->>'room_id')::int; -- << for checkin_data
    is_checkin := booking_data ? 'room_id';

    WITH BI AS (
        SELECT bi.item_id, bi.roomtype_id, rt.code AS roomtype_code, bi.rateplan_id, 
            coalesce(bi.mealtype_id, rp.mealtype_id) as mealtype_id,
            bi.rate_id, bi.qty, bi.arrival, bi.departure, 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, 
            ex.include_adult, ex.include_child, ex.include_infant,
            ex.extra_adult, ex.extra_child, ex.extra_infant,
           -- bi.adult, bi.child, bi.infant,
--            LEAST(bi.adult +bi.extra_adult, COALESCE(rd.include_adult,0)) AS adult, 
--            LEAST(bi.child +bi.extra_child, COALESCE(rd.include_child,0)) AS child, 
--            LEAST(bi.infant+bi.extra_infant,COALESCE(rd.include_infant,0)) AS infant,
--            IIF(bi.adult +bi.extra_adult  > COALESCE(rd.include_adult,0) , bi.adult +bi.extra_adult -COALESCE(rd.include_adult,0), 0) AS extra_adult,
--            IIF(bi.child +bi.extra_child  > COALESCE(rd.include_child,0) , bi.child +bi.extra_child -COALESCE(rd.include_child,0), 0) AS extra_child,
--            IIF(bi.infant+bi.extra_infant > COALESCE(rd.include_infant,0), bi.infant+bi.extra_infant-COALESCE(rd.include_infant,0), 0) AS extra_infant,
--            bi.adult - bi.extra_adult AS include_adult, 
--            bi.child - bi.extra_child AS include_child, 
--            bi.infant - bi.extra_infant AS include_infant, 
--            bi.adult + bi.extra_adult AS total_adult, 
--            bi.child + bi.extra_child AS total_child, 
--            bi.infant + COALESCE(bi.extra_infant,0) AS total_infant,
            requirements, rate_schedule, row_number() over() AS seq
        FROM jsonb_to_recordset(COALESCE(booking_data->'details','[]'::jsonb)) 
        AS bi (item_id int,
               roomtype_id int,
               rateplan_id int,
               mealtype_id int, 
               rate_id int,
               arrival date,
               departure date,
               qty int,
               adult int,
               child int,
               infant int,
               block_qty int,
               inhouse_qty int,
               extra_adult int,
               extra_child int,
               extra_infant int,
               requirements jsonb,
               rate_schedule jsonb)
           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),
           LATERAL sp_calc_extra(bi.rateplan_id, bi.roomtype_id, bi.adult, bi.child, bi.infant) as ex   
           --LEFT JOIN v_rate_details rd ON rd.rateplan_id = bi.rateplan_id AND rd.roomtype_id = bi.roomtype_id
    ), 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.adult, bi.child, bi.infant,
            bi.include_adult, bi.include_child, bi.include_infant, bi.total_adult, bi.total_child, bi.total_infant,
            rs."date", rs.amount, rs.extra_adult_rate, rs.extra_child_rate, rs.extra_infant_rate, rs.comp, rs.comp_abf,
            coalesce(rs.mealtype_id, bi.mealtype_id) as mealtype_id
        FROM BI, LATERAL jsonb_to_recordset(COALESCE(bi.rate_schedule,'[]'::jsonb)) 
        AS RS("date" date, amount t_money, extra_adult_rate t_money, extra_child_rate t_money, extra_infant_rate t_money, comp bool, comp_abf bool, mealtype_id int)
        --WHERE NOT recalc_inclusive
        UNION ALL 
        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.adult, bi.child, bi.infant, 
            bi.include_adult, bi.include_child, bi.include_infant, bi.total_adult, bi.total_child, bi.total_infant,
            rs.rate_date, rs.rate_amount, rs.extra_adult, rs.extra_child, rs.extra_infant, rs.comp, rs.comp_abf, 
            bi.mealtype_id          
        FROM BI, LATERAL sp_get_rate_schedule(bi.roomtype_id, bi.rateplan_id, bi.arrival, bi.departure, false) as rs
            --left join rate_p on rd.rate_id = rs.rate_id
        WHERE bi.rate_schedule IS NULL 
    ), 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.* --null::int as booking_item, req.*
        FROM jsonb_to_recordset(COALESCE(booking_data->'requirements','[]'::jsonb)) 
        AS req(booking_item int, 
               id int,
               inclusion_id int,
               charge_type smallint,
               frequency smallint, 
               charge_dept int,
               charge_rate t_money,
               qty int,
               start_date date,
               end_date date,
               inclusive bool
           )
        WHERE NOT (recalc_inclusive AND COALESCE(req.inclusive,FALSE))
        UNION ALL   
        SELECT bi.item_id, req.* 
        FROM bi, lateral jsonb_to_recordset(COALESCE(bi.requirements, '[]'::jsonb))  
        AS req(id int,
          inclusion_id int,
          charge_type smallint,
          frequency smallint, 
          charge_dept int,
          charge_rate t_money,
          qty int,
          start_date date,
          end_date date,
          inclusive bool
        )
        WHERE NOT (recalc_inclusive AND COALESCE(req.inclusive,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
            /* 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 (
--              select -2 as charge_type, 'Rate: '||rateplan_name AS description, null::t_money as room_rate, null::int as night, null::int as qty, null::t_money as amount 
--              from BI
--              union all
--              select -1 as charge_type, 'Meal: '||mealtype_name AS description, null, null, null, null
--              from BI  
--              UNION ALL 
              /* Room and Extrabed */
              SELECT charge_type, fn_accommodation(roomtype, sum(qty)::int, 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 charge_type, roomtype, room_rate, night, comp

              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)::t_money AS total_deposit 
        FROM jsonb_to_recordset(COALESCE(booking_data->'deposits', '[]'::jsonb)) 
        AS (status char(1), amount t_money)
        WHERE status <> 'X' 
    ), DATASET AS (
        SELECT SUMM.*, 
            total_room_charge, 
            total_other_charge,
            total_room_charge + total_other_charge AS total_charge,
            total_deposit 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;

    IF is_checkin THEN 
        json_result := json_result || jsonb_build_object('room_number', fn_room_number(_room_id)); 
        json_result := json_result - '{room_qty,block_qty,inhouse_qty}'::text[]; 
    ELSE 
        json_result := json_result - 'rate_plan'; 
    END IF;

-- PERFORM sp_log_debug('sp_get_booking_summary_js(ORIGINAL)', booking_data::text); 
-- PERFORM sp_log_debug('sp_get_booking_summary_js(CALCULATED)', json_result::text);

    RETURN json_result;
--EXCEPTION 
--    WHEN OTHERS THEN
--    GET STACKED DIAGNOSTICS
--        _msg_text = MESSAGE_TEXT,
--        _sqlstate = RETURNED_SQLSTATE,
--        _detail   = PG_EXCEPTION_DETAIL,
--        _hint     = PG_EXCEPTION_HINT,
--        _context  = PG_EXCEPTION_CONTEXT; 
--    PERFORM fn_handle_error(_sqlstate, _msg_text, _detail, _hint, _context, 'sp_get_booking_summary_js', booking_data);
--    RETURN '{}'::jsonb;
end