aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_booking_js
Parameters
Name
Type
Mode
booking_id
integer
IN
Definition
with BK as ( select b.id as booking_id, b.status, b.book_type, b.booking_name, b.stay_type, b.confirm_no, b.folio_id, b.rsvtype_id, rt.name as reservation_type, b.book_by, b.guest_id, b.group_id, grp.name as group_name, b.member_code, b.channel_id, cn.name as channel_name, b.voucher_no, b.origin_id, org.name as origin_name, b.arrival, b.arrival_time, b.arrive_by, b.arrive_from, b.departure, b.departure_time, b.depart_by, b.depart_to, b.sales_id, ss.name as sales_name, b.market_id, mk.name as market_name, b.create_user, b.create_time, b.modify_user, b.modify_time, b.confirm_user, b.confirm_time, b.confirm_remark, b.cancel_user, b.cancel_time, --rg.checkin_user, rg.checkin_time, iif(b.status IN ('I','O'), coalesce(b.modify_user, b.confirm_user, b.create_user), NULL) AS checkin_user, ((b.arrival + b.arrival_time) AT TIME ZONE 'Asia/Bangkok')::timestamptz AS checkin_time, --rg.checkout_user, rg.checkout_time, iif(b.status = 'O', coalesce(b.modify_user, b.confirm_user, b.create_user), NULL) AS checkout_user, (b.departure + b.departure_time)::timestamptz AS checkout_time, b.msg_count, b.flag_count, b.note, json_build_object('payment_method', b.payment_method, 'folio_pattern_id', b.folio_pattern_id, 'folio_pattern_name', fp.name, 'credit_limit', b.credit_limit, 'promo_code', b.promo_code ) AS payment from booking b left join channel cn on cn.id = b.channel_id left join reservation_type rt on rt.id = b.rsvtype_id left join group_info grp on grp.id = b.group_id left join origin org on org.id = b.origin_id left join folio_pattern fp on fp.id = b.folio_pattern_id left join sales_staff ss on ss.id = b.sales_id left join market mk on mk.id = b.market_id --LEFT JOIN registration rg ON rg.booking_id = b.id where b.id = $1 ), all_requests AS ( SELECT br.booking_item, br.id, br.inclusion_id, inc."name", br.multiplier AS "charge_type", br.frequency, br.charge_dept, br.charge_rate, br.qty, br.start_date, br.end_date, br.request_time, br.remark, br.inclusive FROM guest_request br LEFT JOIN inclusion inc ON inc.id = br.inclusion_id WHERE br.booking_id = $1 --AND booking_item IS NULL ), requirements AS ( SELECT id, inclusion_id, "name", charge_type, frequency, charge_dept, charge_rate, qty, start_date, end_date, request_time, remark, inclusive FROM all_requests WHERE booking_item IS NULL AND NOT inclusive ORDER BY 1 ), dtl_requests AS ( SELECT br.booking_item, string_agg("name", ', ') request_list, json_agg((id, inclusion_id, "name", charge_type, frequency, charge_dept, charge_rate, qty, start_date, end_date, request_time, remark, inclusive )::t_requirement ) AS requirements -- json_agg((SELECT X FROM -- (SELECT id, inclusion_id, "name", charge_type, frequency, charge_dept, charge_rate, qty, start_date, end_date, request_time, remark, inclusive -- ) AS X -- )) AS requirements FROM all_requests br --LEFT JOIN inclusion inc ON inc.id = br.inclusion_id WHERE booking_item IS NOT NULL AND NOT inclusive GROUP BY br.booking_item --WHERE recalc_inclusive AND (inc.multiplier <> 1 OR bi.seq = 1) ), details as ( select bi.item_id, bi.roomtype_id, rt.code AS roomtype_code, rt."name" as roomtype_name, bi.bedtype_id, bt."name" as bedtype_name, bi.rateplan_id, rp."name" as rateplan_name, bi.mealtype_id, mt."name" as mealtype_name, bi.rate_id, arrival, departure, (departure-arrival) AS nights, bi.qty, bi.qty + bi.noshow_qty as book_qty, bi.inhouse_qty, bi.noshow_qty, bi.adult, bi.child, bi.infant, bi.extra_adult, extra_child, extra_infant, allot_qty, block_qty, inhouse_qty, charge_to, bi.no_amend, bi.no_cancel, bi.no_refund, coalesce(string_to_array(bi.room_numbers, ', '), '{}') as room_numbers, req.request_list, COALESCE(req.requirements, '[]') AS requirements, (SELECT COALESCE(json_agg(rates), '[]') --COALESCE(array_agg(json_build_object('date', adate, 'amount', room_rate)), '{}') FROM (SELECT br.charge_date AS "date", br.room_rate AS amount, coalesce(br.extrabed_rate, rd.rate_extra_adult) AS extra_adult_rate, coalesce(br.child_rate, rd.rate_extra_child) AS extra_child_rate, coalesce(br.infant_rate, rd.rate_extra_infant) AS extra_infant_rate, br.comp, br.comp_abf, br.rateplan_id, rp."name" as rateplan_name, br.mealtype_id, mt."name" as mealtype_name FROM booking_rates br left join rate_plan rp on rp.id = br.rateplan_id left join meal_type mt on mt.id = br.mealtype_id WHERE br.booking_item = bi.item_id ORDER BY 1 ) AS rates ) AS rate_schedule from booking_items bi left join rate_plan rp on rp.id = bi.rateplan_id left join meal_type mt on mt.id = bi.mealtype_id left join room_type rt on rt.id = bi.roomtype_id left join bed_type bt on bt.id = bi.bedtype_id LEFT JOIN dtl_requests req ON req.booking_item = bi.item_id LEFT JOIN v_rate_details rd ON rd.rateplan_id = bi.rateplan_id AND rd.roomtype_id = bi.roomtype_id where bi.booking_id = $1 order by bi.item_id ), guests as ( SELECT guest_id FROM bk union SELECT guest_id FROM booking_guests WHERE booking_id = $1 ), deposits AS ( SELECT d.id, d.status, d.deposit_date, d.payment_id, dp.name AS payment_name, d.amount, d.remark, d.create_user, d.create_time, d.modify_user, d.modify_time, d.cancel_user, d.cancel_time FROM deposit d LEFT JOIN department dp ON dp.id = d.payment_id WHERE d.booking_id = $1 ORDER BY d.deposit_date, d.id ), bk_log AS ( SELECT id, log_user AS user_name, log_type, COALESCE(log_text,log_type) AS log_text, log_date, log_time FROM booking_log WHERE booking_id = $1 ORDER BY 1 ), objects AS ( SELECT (SELECT json_agg(details) from details) as details, (SELECT sp_get_guest_list_js(array_agg(guest_id)) FROM guests) as guests, (SELECT COALESCE(json_agg(all_requests), '[]') from all_requests WHERE booking_item IS NULL OR inclusive) as bk_requests, (SELECT COALESCE(json_agg(requirements), '[]') from requirements) as requirements, (SELECT COALESCE(json_agg(deposits), '[]') from deposits) as deposits, --(SELECT sp_get_booking_summary_js($1)) AS summary, (SELECT COALESCE(json_agg(bk_log), '[]') from bk_log) AS logs ), summ AS ( Select sp_get_booking_summary_js(row_to_json(booking_data)::jsonb, recalc_inclusive => FALSE) AS summary from (SELECT BK.*, details, guests, bk_requests AS requirements, deposits, logs FROM bk, objects) AS booking_data ) Select row_to_json(dataset) from (SELECT BK.*, details, guests, requirements, deposits, logs, summary FROM bk, objects, summ) AS dataset