aonestar.public

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

sp_get_roomplan_events_js

Parameters

Definition