aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_booking_summary_js
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