aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_roomplan_events_js
Parameters
Name
Type
Mode
start_date
date
IN
end_date
date
IN
no_extend_props
boolean
IN (DEFAULT false)
Definition
declare roomplan_events json; colors json; OCC_TextColor text; BLK_TextColor text; OOO_TextColor text; OOS_TextColor text; OOI_TextColor text; HSU_TextColor text; DEFAULT_COLOR json = $${ "OCC": "#1E90FF", "BLK": "#7B68EE", "OOO": "#FF4500", "OOS": "#F4A460", "OOI": "#C0C0C0", "HSU": "#EE82EE" }$$::json; BEGIN /* Event codes ----------- 1: In-house 3: Block 4: Out of Order 5: Out of Service 6: Out of Inventoty */ colors := fn_sys_param('ROOM_STATUS', 'COLORS', DEFAULT_COLOR); OCC_TextColor := IIF(is_light_color(colors->>'OCC'), '#000000', '#FFFFFF'); BLK_TextColor := IIF(is_light_color(colors->>'BLK'), '#000000', '#FFFFFF'); OOO_TextColor := IIF(is_light_color(colors->>'OOO'), '#000000', '#FFFFFF'); OOS_TextColor := IIF(is_light_color(colors->>'OOS'), '#000000', '#FFFFFF'); OOI_TextColor := IIF(is_light_color(colors->>'OOI'), '#000000', '#FFFFFF'); HSU_TextColor := IIF(is_light_color(colors->>'HSU'), '#000000', '#FFFFFF'); WITH room_activity AS ( SELECT * FROM sp_get_room_activity(more_guest => true) a WHERE is_overlapped($1, $2, a.start_date, a.end_date) ), activity AS ( SELECT a.activity_type, a.activity_code, a.room_id, a.room_number, min(a.start_date) AS start_date, max(a.end_date) AS end_date, string_agg(a."name", ' | ') AS name, min(a.booking_id) AS booking_id, min(a.booking_item) AS booking_item, min(a.block_id) AS block_id, min(a.register_id) AS register_id, min(a.ooo_id) AS ooo_id, min(a.guest_id) AS guest_id, min(a.folio_id) AS folio_id, array_agg(register_id ORDER BY register_id) AS register_ids FROM room_activity AS a WHERE activity_code = 1 GROUP BY 1,2,3,4 UNION ALL SELECT a.activity_type, a.activity_code, a.room_id, a.room_number, a.start_date, a.end_date, a.name, a.booking_id, a.booking_item, a.block_id, a.register_id, a.ooo_id, a.guest_id, NULL, '{}'::int[] FROM room_activity a WHERE activity_code <> 1 ), events AS ( SELECT a.activity_code::text||'-'||COALESCE(a.register_id, a.block_id, a.ooo_id)::text AS id, a."name" AS title, IIF(a.start_date = a.end_date, a.start_date, a.start_date + interval '12 hour') AS "start", a.end_date + interval '12 hour' AS "end", CASE a.activity_code WHEN 1 THEN colors->>'OCC' WHEN 3 THEN colors->>'BLK' WHEN 4 THEN colors->>'OOO' WHEN 5 THEN colors->>'OOS' WHEN 6 THEN colors->>'OOI' END AS color, CASE a.activity_code WHEN 1 THEN OCC_TextColor WHEN 3 THEN BLK_TextColor WHEN 4 THEN OOO_TextColor WHEN 5 THEN OOS_TextColor WHEN 6 THEN OOI_TextColor END AS "textColor", 'R'||lpad(rm.roomtype_id::text, 2, '0')||lpad(rm.id::text, 5, '0') AS "resourceId", a.activity_code IN (4,5,6) AS "startEditable", --activity_code IN (4,5,6) AS "resizableFromStart", <-- not effect to UI component a.activity_code IN (1,4,5,6)AS "durationEditable", a.activity_code = 3 AS "resourceEditable", -- temporary disble change room on in-house guest --a.activity_code IN (1,3)AS "resourceEditable", CASE WHEN no_extend_props THEN NULL::json ELSE json_build_array( a.activity_type, a.activity_code, a.room_number, a.room_id, a.booking_id, a.booking_item, a.block_id, rb.super_block, a.register_id, a.guest_id, a.register_ids, a.folio_id, a.start_date, a.end_date, a.ooo_id, oo.ooo_type, oo.start_date, oo.end_date, oo.reason_id, oo.return_status, oo.remark ) END AS "extendedProps" FROM activity AS a LEFT JOIN room rm ON rm.id = a.room_id LEFT JOIN out_of_order oo ON oo.id = a.ooo_id LEFT JOIN room_block rb on rb.id = a.block_id ) SELECT json_agg(events) FROM events INTO roomplan_events; RETURN roomplan_events; END