aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_guest_inhouse_js
Parameters
Name
Type
Mode
register_id
integer
IN
Definition
with REG as ( select rg.id AS register_id, rg.booking_id, rg.booking_item, b.book_type, rg.status, rg.stay_type, b.confirm_no, rg.folio_id, COALESCE(rg.rsvtype_id, b.rsvtype_id) AS rsvtype_id, --rst.name as reservation_type, b.book_by, rg.guest_id, rg.group_id, grp.name as group_name, rg.member_code, rg.channel_id, cn.name as channel_name, rg.voucher_no, rg.origin_id, org.name as origin_name, rg.arrival, rg.checkin_time::time AS arrival_time, rg.arrive_by, rg.arrive_from, rg.departure, rg.departure_time, rg.depart_by, rg.depart_to, (rg.departure-rg.arrival) AS nights, rg.sales_id, ss.name as sales_name, rg.market_id, mk.name as market_name, rg.checkin_user, rg.checkin_time, rg.modify_user, rg.modify_time, rg.checkout_user, rg.checkout_time, rg.msg_count, rg.flag_count, rg.note, json_build_object('payment_method', rg.payment_method, 'folio_pattern_id',rg.folio_pattern_id, 'folio_pattern_name', fp.name, 'credit_limit', rg.credit_limit, 'promo_code', rg.promo_code ) AS payment, rg.room_id, rm.room_number, rm.roomtype_id, rt.code AS roomtype_code, rt."name" as roomtype_name, rm.bedtype_id, bt."name" as bedtype_name, rg.rateplan_id, rp."name" as rateplan_name, rg.mealtype_id, mt."name" as mealtype_name, rg.rate_id, rg.adult, rg.child, rg.infant, rg.extra_adult, rg.extra_child, rg.extra_infant, rg.charge_to_booking, --bi.no_amend, bi.no_cancel, bi.no_refund, (SELECT COALESCE(json_agg(rates), '[]') FROM ( SELECT 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, br.mealtype_id FROM registration_rates br WHERE br.register_id = $1 ORDER BY 1 ) AS rates ) AS rate_schedule ,json_build_object( 'max_adult', coalesce(rt.max_adult,0), 'max_child', coalesce(rt.max_child, 0), 'max_infant', coalesce(rt.max_infant, 0), 'max_pax',coalesce(rt.max_pax,0) ) as pax from registration rg LEFT JOIN booking b ON rg.booking_id = b.id left join channel cn on cn.id = rg.channel_id left join group_info grp on grp.id = rg.group_id left join origin org on org.id = rg.origin_id left join folio_pattern fp on fp.id = rg.folio_pattern_id left join sales_staff ss on ss.id = rg.sales_id left join market mk on mk.id = rg.market_id left join rate_plan rp on rp.id = rg.rateplan_id left join room rm on rm.id = rg.room_id left join room_type rt on rt.id = rm.roomtype_id left join meal_type mt on mt.id = rg.mealtype_id left join bed_type bt on bt.id = rm.bedtype_id LEFT JOIN v_rate_details rd ON rd.roomtype_id = rm.roomtype_id AND rd.rateplan_id = rg.rateplan_id -- LEFT JOIN registration rg ON rg.booking_id = b.id where rg.id = $1 ), requests AS ( SELECT rr.id, rr.inclusion_id, inc."name", rr.multiplier AS "charge_type", rr.frequency, rr.charge_dept, rr.charge_rate, rr.qty, rr.start_date, rr.end_date, rr.request_time, rr.remark, rr.inclusive FROM guest_request rr LEFT JOIN inclusion inc ON inc.id = rr.inclusion_id WHERE rr.register_id = $1 --AND NOT rr.inclusive ), guests as ( SELECT guest_id FROM REG UNION SELECT guest_id FROM register_guests WHERE register_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.register_id = $1 ), reg_log AS ( SELECT id, log_user AS user_name, log_type, COALESCE(log_text,log_type) AS log_text, log_date, log_time FROM registration_log WHERE register_id = $1 ), jsons AS ( SELECT sp_get_guest_list_js((SELECT array_agg(guest_id) FROM guests)) as guests, (SELECT COALESCE(json_agg(requests), '[]') from requests) as all_requests, (SELECT COALESCE(json_agg(requests), '[]') from requests WHERE NOT inclusive) as requirements, (SELECT COALESCE(json_agg(deposits), '[]') from deposits) as deposits, (SELECT COALESCE(json_agg(reg_log), '[]') from reg_log) AS logs ), summ AS ( SELECT sp_get_checkin_summary_js(row_to_json(checkin_data)::jsonb) AS summary FROM (SELECT REG.*, all_requests AS requirements, deposits FROM REG, jsons) AS checkin_data ) SELECT row_to_json(dataset) FROM (SELECT REG.*, guests, requirements, deposits, logs, summary FROM REG, jsons, summ) AS dataset