aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_booking_rooms_and_guests_js
Parameters
Name
Type
Mode
booking_id
integer
IN
Definition
DECLARE js_result json; sellable text[]; sys_date date; BEGIN sellable := ARRAY(SELECT json_array_elements_text(fn_sys_param('ROOM_STATUS','SELLABLE', '[]'::json))); sys_date := fn_system_date(); WITH BI AS ( SELECT item_id, qty, block_qty, inhouse_qty, bi.roomtype_id, rt.code AS roomtype_code, rt."name" AS roomtype_name, bi.bedtype_id, bt.code AS bedtype_code, bt.name AS bedtype_name, bi.arrival, bi.departure, format('%s%s @%s (%s)', rt.name, coalesce('/'||bt.code, ''), rp.name, fn_period_to_str(bi.arrival, bi.departure)) AS description FROM booking_items bi LEFT JOIN room_type rt ON rt.id = bi.roomtype_id LEFT JOIN bed_type bt ON bt.id = bi.bedtype_id LEFT JOIN rate_plan rp ON rp.id = bi.rateplan_id WHERE bi.booking_id = $1 and bi.arrival = sys_date ), inh AS ( SELECT '1-'||rg.id::text AS "key", rg.booking_item, 'I' AS item_status, rg.room_id, rm.room_number, rs.status room_status, rs.hk_status, rm.roomtype_id AS roomtype_id, rt.code AS roomtype_code, rt."name" AS roomtype_name, rm.bedtype_id, bt.code AS bedtype_code, bt.name AS bedtype_name, rg.id AS register_id, NULL::int AS block_id, FALSE AS can_checkin, rg.status = 'I' AS can_join, g.full_name ||coalesce(' | '||(select string_agg(g.full_name, ' | ') from register_guests mg inner join guest g on g.id = mg.guest_id where mg.register_id = rg.id ), '') AS guest_name, FALSE AS room_is_ready FROM registration rg LEFT JOIN room rm ON rm.id = rg.room_id LEFT JOIN guest g ON g.id = rg.guest_id LEFT JOIN room_status rs ON rs.room_id = rm.id LEFT JOIN room_type rt ON rt.id = rm.roomtype_id LEFT JOIN bed_type bt ON bt.id = rm.bedtype_id WHERE rg.status <> 'X' AND rg.booking_id = $1 ), block AS ( SELECT '2-'||rb.id::text AS "key", rb.booking_item, 'B' AS item_status, rb.room_id, rm.room_number, rs.status room_status, rs.hk_status, rm.roomtype_id AS roomtype_id, rt.code AS roomtype_code, rt."name" AS roomtype_name, rm.bedtype_id, bt.code AS bedtype_code, bt.name AS bedtype_name, NULL::int, rb.id AS block_id, TRUE AS can_checkin, FALSE AS can_join, NULL, (rs.status = 'VAC' AND rs.hk_status = ANY(sellable)) AS room_is_ready FROM room_block rb LEFT JOIN room rm ON rm.id = rb.room_id LEFT JOIN room_status rs ON rs.room_id = rm.id LEFT JOIN room_type rt ON rt.id = rm.roomtype_id LEFT JOIN bed_type bt ON bt.id = rm.bedtype_id WHERE rb.booking_id = $1 AND NOT rb.checked_in and rb.arrival = sys_date ), unblock AS ( SELECT '3-'||bi.item_id::TEXT||'-'||lpad(seq::TEXT,3,'0') AS "key", bi.item_id, 'B', NULL::int, NULL, NULL, NULL, NULL::int, NULL, NULL, --bi.roomtype_id, roomtype_code, roomtype_name, NULL::int, NULL, NULL, --bi.bedtype_id, bedtype_code, bedtype_name, NULL::int, NULL::int, TRUE AS can_checkin, FALSE AS can_join, NULL, FALSE AS room_is_ready FROM BI, LATERAL generate_series(1, bi.qty-bi.block_qty-bi.inhouse_qty) AS seq ), items AS ( SELECT * FROM inh UNION ALL SELECT * FROM block UNION ALL SELECT * FROM unblock ), bk_items AS ( SELECT bi.*, (SELECT json_agg(B) FROM (SELECT "key", item_status, item_status AS block_status, room_id, room_number, room_status, hk_status, roomtype_id, roomtype_code, roomtype_name, bedtype_id, bedtype_code, bedtype_name, register_id, block_id, guest_name, can_checkin, can_join, room_is_ready FROM items WHERE items.booking_item = bi.item_id) AS B ) AS rooms FROM BI ), inh_guests AS ( SELECT guest_id FROM registration rg WHERE rg.booking_id = $1 and rg.status <> 'X' union SELECT mg.guest_id FROM register_guests mg inner join registration rg on rg.id = mg.register_id WHERE rg.booking_id = $1 and rg.status <> 'X' ), bk_guests AS ( SELECT guest_id FROM booking WHERE id = $1 union all SELECT guest_id FROM booking_guests bg WHERE bg.booking_id = $1 and NOT bg.registered --AND bg.guest_id NOT IN (SELECT guest_id FROM inh_guests) ), guests AS ( SELECT guest_id, g.full_name AS name, g.sex, g.birthdate, fn_age_str(g.birthdate) AS "age", n.name as nationality, v.name vip_level from bk_guests bg LEFT join guest g ON g.id = bg.guest_id left join nationality n on n.id = g.nation_id left join "language" l on l.id = g.lang_id left join vip v on v.id = g.vip_id WHERE bg.guest_id NOT IN (SELECT guest_id FROM inh_guests) ), dataset AS ( SELECT sum(bi.qty) AS booking_qty, sum(bi.inhouse_qty) AS inhouse_qty, sum(bi.qty) - sum(bi.inhouse_qty) AS checkin_available, (SELECT COALESCE(jsonb_agg(bk_items),'[]') FROM bk_items) AS booking_items, (SELECT COALESCE(jsonb_agg(guests),'[]') FROM guests) AS guests FROM BI ) SELECT row_to_json(dataset) FROM dataset INTO js_result; RETURN js_result; END