aonestar.public

Parameters

Name	Type	Mode
format	integer	IN (DEFAULT 1)

Definition

WITH parents AS (
        SELECT 'T'||lpad(rt.id::text, 2, '0')||'00000' AS id,
            rt.name AS title,
            jsonb_build_object(
--              'roomtype_id', id,
--              'roomtype_code', code,
--              'order_seq', order_seq
                'roomtype', rt.name,
                'roomtype_id', rt.id,
                'roomtype_code', rt.code,
                'roomgroup', rg.name,
                'roomgroup_id', rt.group_id,
                'order_seq', order_seq*10000
            ) AS "extendedProps",
            lpad(rt.id::text, 2, '0') AS prefix,
            rt.id AS roomtype_id,
            rt.code,
            rt.group_id AS roomgroup_id,
            rg.name AS roomgroup,
            rt.order_seq
        FROM room_type rt LEFT JOIN room_group rg ON rg.id = rt.group_id
        WHERE rt.enabled AND rt.total_rooms> 0
        ORDER BY order_seq
    ), items AS (
        SELECT 'R'||prefix||lpad(rm.id::text, 5, '0') AS id,
            rm.room_number AS title,
            --format('%s%s', rm.room_number, coalesce('/'||bt.code,'')) AS title,
            --format('%s%s   [%s]', rm.room_number, coalesce('/'||bt.code,''), rs.hk_status) AS title,
            --rm.room_number||coalesce('/'||bt.code,'')||COALESCE('  ['||rs.hk_status||']','') AS title,
            rt.id AS "parentId",
            jsonb_build_object(
                'room_id', rm.id,
                'room_number', rm.room_number,
                'roomtype', rt.title,
                'roomtype_id', rm.roomtype_id,
                'roomtype_code', rt.code,
                'roomgroup', rt.roomgroup,
                'roomgroup_id', rt.roomgroup_id,
                'bedtype', bt.name,
                'bedtype_id', rm.bedtype_id,
                'bedtype_code', bt.code,
                'building', bu."name",
                'building_id', rm.building_id,
                'location', lc."name" ,
                'location_id', rm.location_id,
                'exposure', xp."name",
                'exposure_id', rm.exposure_id,
                'status', rs.status,
                'hk_status', rs.hk_status,
                'order_seq', (rt.order_seq*10000) + (row_number() OVER ())
            ) AS "extendedProps"
        FROM room rm
            LEFT JOIN parents rt ON rt.roomtype_id = rm.roomtype_id
            LEFT JOIN bed_type bt ON bt.id = rm.bedtype_id
            LEFT JOIN room_status rs ON rs.room_id = rm.id
            LEFT JOIN building bu ON bu.id = rm.building_id
            LEFT JOIN room_location lc ON lc.id = rm.location_id
            LEFT JOIN room_exposure xp ON xp.id = rm.exposure_id
        ORDER BY room_number
    ), headers AS (
       SELECT id, title, "extendedProps" FROM parents
    )
    SELECT
       CASE format
           WHEN 2 THEN (
               WITH tree AS (
                   SELECT id, title, "extendedProps",
                       (SELECT json_agg(i) FROM items i where i."parentId" = p.id) AS children
                   FROM parents p
               )
               SELECT coalesce(json_agg(tree), '[]'::json) FROM tree
           )
           WHEN 3 THEN (SELECT coalesce(json_agg(items), '[]'::json) FROM items)
           ELSE /* 1 */
               ((SELECT coalesce(jsonb_agg(headers), '[]'::jsonb) FROM headers)
                 || (SELECT coalesce(jsonb_agg(items), '[]'::jsonb) FROM items))::json
       END

sp_get_roomplan_resources_js

Parameters

Definition