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;
    name_format text = replace(fn_sys_param('SYSTEM', 'NAME_FORMAT', 'LFM'), 'T', '');
    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 a.*, reg.shared_register_id
        FROM sp_get_room_activity(more_guest := true, use_short_name := true) a
            LEFT JOIN registration reg ON reg.id = a.register_id
        WHERE is_overlapped($1, $2, a.start_date, a.end_date)
    ), activity_data 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", ' | ' order by a.is_more_guest, a.register_id) AS name,
            array_agg(json_object('id': a.guest_id, 'name': a."name",
                                  'register_id': a.register_id, 'folio_id': a.folio_id)
                      order by a.is_more_guest, a.register_id) as guests,
            min(a.booking_id) AS booking_id,
            min(a.booking_item) AS booking_item,
            min(a.block_id) AS block_id,
            coalesce(a.join_register_id, a.shared_register_id, 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(DISTINCT a.folio_id) FILTER (WHERE a.folio_id IS NOT NULL) AS folio_ids,
            array_agg(DISTINCT a.register_id) AS register_ids
        FROM room_activity AS a
        WHERE activity_code = 1
        GROUP BY 1,2,3,4,12
        UNION ALL
        SELECT a.activity_type, a.activity_code, a.room_id, a.room_number, a.start_date, a.end_date, a.name,
            COALESCE(
                ( SELECT array_agg(
                           json_object('id': bg.guest_id,
                                       'name': COALESCE(
                                                 fn_fullname(g.last_name, g.first_name, g.middle_name, null, name_format),
                                                 g.full_name),
                                       'register_id': NULL, 'folio_id': NULL)
                           ORDER BY bg.room_seq, bg.guest_id)
                  FROM booking_guests bg
                  JOIN guest g ON g.id = bg.guest_id
                  WHERE bg.booking_id  = a.booking_id
                    AND bg.booking_item = a.booking_item ),
                -- group block with no namelist yet → empty (NOT the group name);
                -- individual block / OOO keep their single guest/reason entry
                CASE
                    WHEN a.guest_id IS NULL AND a.booking_id IS NOT NULL THEN '{}'::json[]
                    ELSE array[json_object('id': a.guest_id, 'name': a."name",
                                           'register_id': a.register_id, 'folio_id': NULL)]
                END
            ) as guests,
            a.booking_id, a.booking_item, a.block_id, a.register_id, a.ooo_id, a.guest_id, NULL,
            NULL::int[], '{}'::int[]
        FROM room_activity a
        WHERE activity_code <> 1
    ), folio_balances AS (
        SELECT ad.register_id AS grp_register_id,
               COALESCE(sum(fi.balance), 0.0)::t_money AS folio_balance
        FROM activity_data ad
            CROSS JOIN LATERAL unnest(ad.folio_ids) AS u(folio_id)
            JOIN folio_items fi ON fi.folio_id = u.folio_id
        WHERE ad.activity_code = 1
            AND fi.folio_seq IN (1,2,3,4)
            AND NOT fi.closed
        GROUP BY ad.register_id
    ), activity AS (
        SELECT activity_data.*,
            CASE
                WHEN activity_data.activity_code = 1 THEN COALESCE(fb.folio_balance, 0.0)::t_money
                ELSE NULL::t_money
            END AS folio_balance
        FROM activity_data
            LEFT JOIN folio_balances fb ON fb.grp_register_id = activity_data.register_id
    ), 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,
                        a.guests,
                        a.folio_balance,
                        COALESCE(b.total_charge, 0.0)::t_money - COALESCE(b.total_deposit, 0.0)::t_money
                     )
            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
            LEFT JOIN booking b ON b.id = a.booking_id
    )
    SELECT json_agg(events) FROM events
    INTO roomplan_events;

RETURN roomplan_events;
END

sp_get_roomplan_events_js

Parameters

Definition