aonestar.public

Parameters

Name	Type	Mode
statuses	text	IN (DEFAULT 'I')
search_text	text	IN (DEFAULT NULL)
arrival	date	IN (DEFAULT NULL)
departure	date	IN (DEFAULT NULL)
stay_date	date	IN (DEFAULT NULL)
room_number	text	IN (DEFAULT NULL)
roomtype_id	integer	IN (DEFAULT NULL)
channel_name	text	IN (DEFAULT NULL)
vip_id	integer	IN (DEFAULT NULL)
member_code	text	IN (DEFAULT NULL)
company	text	IN (DEFAULT NULL)
nation_id	integer	IN (DEFAULT NULL)
rateplan_id	integer	IN (DEFAULT NULL)
carplate_no	text	IN (DEFAULT NULL)
register_id	integer	IN (DEFAULT NULL)
booking_id	integer	IN (DEFAULT NULL)
i_include_archive	boolean	IN (DEFAULT false)
i_page	integer	IN (DEFAULT NULL)
i_limit	integer	IN (DEFAULT NULL)
i_sort_field	text	IN (DEFAULT 'register_id')
i_sort_order	text	IN (DEFAULT 'desc')

Definition

#variable_conflict use_variable
DECLARE
    json_result json;
    _sort_stmt text;
    allow_sort_fields CONSTANT text[]
    = '{register_id,booking_id,room_number,status,guest_name,arrival,departure,night,channel_name,nationality,rateplan_name}';
BEGIN
    search_text = nullif(search_text, '');
    carplate_no = nullif(replace(carplate_no, ' ', ''), '');

-- Validate and build sort statement
    i_sort_order := IIF(lower(coalesce(i_sort_order, 'desc')) <> 'desc', 'ASC', 'DESC');

if i_sort_field is null or (lower(i_sort_field) <> ALL(allow_sort_fields)) then
        i_sort_field := 'register_id';
        i_sort_order := 'DESC';
    end if;

_sort_stmt := lower(i_sort_field) || ' ' || i_sort_order;
    if i_sort_field <> 'register_id' then
        _sort_stmt := _sort_stmt || ' NULLS LAST, register_id DESC';
    end if;

EXECUTE format($SQL$
    WITH RES AS (
        -- PUBLIC SCHEMA
        SELECT r.id as register_id, r.booking_id, r.booking_item, r.room_id, rm.room_number,
            r.status, r.folio_id, r.walk_in, g.full_name guest_name,
            r.group_id, grp.name as group_name, mg.more_guests,
            g.birthdate, r.msg_count, r.flag_count, coalesce(g.confidential, false) confidential,
            r.channel_id, c.name channel_name, r.voucher_no, n.name nationality, g.carplate_no,
            r.arrival, r.departure, r.departure - r.arrival night,
            r.rateplan_id, rp.name as rateplan_name, adult, child, infant,
            false AS is_archived
        FROM registration r
            LEFT JOIN guest g ON g.id = r.guest_id
            LEFT JOIN room rm ON rm.id = r.room_id
            LEFT JOIN group_info grp ON grp.id = r.group_id
            LEFT JOIN channel c ON c.id = r.channel_id
            LEFT JOIN nationality n ON n.id = g.nation_id
            LEFT JOIN room_type rt ON rt.id = rm.roomtype_id
            LEFT JOIN rate_plan rp ON rp.id = r.rateplan_id,
            LATERAL (SELECT coalesce(array_agg(g.full_name::text),'{}') as more_guests,
                            coalesce(array_agg(g.nation_id),'{}') AS nations,
                            coalesce(array_agg(g.vip_id),'{}') AS vips,
                            coalesce(array_agg(g.carplate_no),'{}') AS carplates,
                            coalesce(array_agg(g.company),'{}') AS companies
                     FROM register_guests rg LEFT JOIN guest g ON g.id = rg.guest_id
                     WHERE rg.register_id = r.id AND rg.guest_id <> r.guest_id) AS mg
        WHERE ($1 LIKE '%%'||r.status||'%%')
            AND ($2 IS NULL
                OR (r.id::text = $2) OR (r.booking_id::text = $2)
                OR (r.voucher_no = $2) OR (upper(rm.room_number) = upper($2))
                OR (g.full_name LIKE '%%'||$2||'%%')
                OR is_match_any(mg.more_guests, $2, match_partial:=true, ignore_case:=true))
            AND ($3 IS NULL OR $3 = r.arrival)
            AND ($4 IS NULL OR $4 = r.departure)
            AND ($5 IS NULL OR $5 BETWEEN r.arrival AND r.departure)
            AND ($6 IS NULL OR upper($6) = upper(rm.room_number) OR rm.room_number ILIKE $6||'%%')
            AND ($7 IS NULL OR $7 = rm.roomtype_id)
            AND ($8 IS NULL OR c.name LIKE '%%'||$8||'%%')
            AND ($9 IS NULL OR $9 = g.vip_id OR $9 = ANY(mg.vips))
            AND ($10 IS NULL OR $10 = r.member_code)
            AND ($11 IS NULL OR g.company LIKE '%%'||$11||'%%'
                 OR is_match_any(mg.companies, $11, match_partial:=true, ignore_case:=true))
            AND ($12 IS NULL OR $12 = g.nation_id OR $12 = ANY(mg.nations))
            AND ($13 IS NULL OR $13 = r.rateplan_id)
            AND ($14 IS NULL OR replace(g.carplate_no, ' ', '') LIKE '%%'||$14||'%%'
                 OR EXISTS (SELECT * FROM UNNEST(mg.carplates) AS mg_carplate
                            WHERE replace(mg_carplate, ' ', '') LIKE '%%'||$14||'%%'))
            AND ($15 IS NULL OR $15 = r.id)
            AND ($16 IS NULL OR $16 = r.booking_id)

UNION ALL

-- BACKUP SCHEMA (guard: only when i_include_archive = true)
        SELECT r.id as register_id, r.booking_id, r.booking_item, r.room_id, rm.room_number,
            r.status, r.folio_id, r.walk_in, g.full_name guest_name,
            r.group_id, grp.name as group_name, mg.more_guests,
            g.birthdate, r.msg_count, r.flag_count, coalesce(g.confidential, false) confidential,
            r.channel_id, c.name channel_name, r.voucher_no, n.name nationality, g.carplate_no,
            r.arrival, r.departure, r.departure - r.arrival night,
            r.rateplan_id, rp.name as rateplan_name, adult, child, infant,
            true AS is_archived
        FROM backup.registration r
            LEFT JOIN guest g ON g.id = r.guest_id
            LEFT JOIN room rm ON rm.id = r.room_id
            LEFT JOIN group_info grp ON grp.id = r.group_id
            LEFT JOIN channel c ON c.id = r.channel_id
            LEFT JOIN nationality n ON n.id = g.nation_id
            LEFT JOIN room_type rt ON rt.id = rm.roomtype_id
            LEFT JOIN rate_plan rp ON rp.id = r.rateplan_id,
            LATERAL (SELECT coalesce(array_agg(g.full_name::text),'{}') as more_guests,
                            coalesce(array_agg(g.nation_id),'{}') AS nations,
                            coalesce(array_agg(g.vip_id),'{}') AS vips,
                            coalesce(array_agg(g.carplate_no),'{}') AS carplates,
                            coalesce(array_agg(g.company),'{}') AS companies
                     FROM backup.register_guests rg LEFT JOIN guest g ON g.id = rg.guest_id
                     WHERE rg.register_id = r.id AND rg.guest_id <> r.guest_id) AS mg
        WHERE $17
            AND ($1 LIKE '%%'||r.status||'%%')
            AND ($2 IS NULL
                OR (r.id::text = $2) OR (r.booking_id::text = $2)
                OR (r.voucher_no = $2) OR (upper(rm.room_number) = upper($2))
                OR (g.full_name LIKE '%%'||$2||'%%')
                OR is_match_any(mg.more_guests, $2, match_partial:=true, ignore_case:=true))
            AND ($3 IS NULL OR $3 = r.arrival)
            AND ($4 IS NULL OR $4 = r.departure)
            AND ($5 IS NULL OR $5 BETWEEN r.arrival AND r.departure)
            AND ($6 IS NULL OR upper($6) = upper(rm.room_number) OR rm.room_number ILIKE $6||'%%')
            AND ($7 IS NULL OR $7 = rm.roomtype_id)
            AND ($8 IS NULL OR c.name LIKE '%%'||$8||'%%')
            AND ($9 IS NULL OR $9 = g.vip_id OR $9 = ANY(mg.vips))
            AND ($10 IS NULL OR $10 = r.member_code)
            AND ($11 IS NULL OR g.company LIKE '%%'||$11||'%%'
                 OR is_match_any(mg.companies, $11, match_partial:=true, ignore_case:=true))
            AND ($12 IS NULL OR $12 = g.nation_id OR $12 = ANY(mg.nations))
            AND ($13 IS NULL OR $13 = r.rateplan_id)
            AND ($14 IS NULL OR replace(g.carplate_no, ' ', '') LIKE '%%'||$14||'%%'
                 OR EXISTS (SELECT * FROM UNNEST(mg.carplates) AS mg_carplate
                            WHERE replace(mg_carplate, ' ', '') LIKE '%%'||$14||'%%'))
            AND ($15 IS NULL OR $15 = r.id)
            AND ($16 IS NULL OR $16 = r.booking_id)
    ),
    FILTERED AS MATERIALIZED (SELECT * FROM RES),
    TOTAL AS (SELECT count(*)::int AS total_rows FROM FILTERED)
    SELECT CASE
        WHEN $18 IS NULL THEN
            (SELECT COALESCE(json_agg(f), '[]') FROM (SELECT * FROM FILTERED ORDER BY %s) f)
        ELSE
            (SELECT json_build_object(
                'items', COALESCE(
                    (SELECT json_agg(p) FROM (
                        SELECT * FROM FILTERED ORDER BY %s
                        LIMIT $18 OFFSET GREATEST(0, (COALESCE($19,1) - 1) * $18)
                    ) p), '[]'::json),
                'paging', json_build_object(
                    'current', COALESCE($19,1), 'limit', $18,
                    'total_rows', t.total_rows, 'row_count', LEAST($18, t.total_rows)
                )
            ) FROM TOTAL t)
    END
    $SQL$, _sort_stmt, _sort_stmt)
    USING statuses,              -- $1
          search_text,           -- $2
          arrival,               -- $3
          departure,             -- $4
          stay_date,             -- $5
          room_number,           -- $6
          roomtype_id,           -- $7
          channel_name,          -- $8
          vip_id,                -- $9
          member_code,           -- $10
          company,               -- $11
          nation_id,             -- $12
          rateplan_id,           -- $13
          carplate_no,           -- $14
          register_id,           -- $15
          booking_id,            -- $16
          i_include_archive,     -- $17
          i_limit,               -- $18
          i_page                 -- $19
    INTO json_result;

RETURN COALESCE(json_result, CASE WHEN i_limit IS NULL THEN '[]'::json
        ELSE '{"items":[],"paging":{"current":1,"limit":0,"total_rows":0,"row_count":0}}'::json END);
END

sp_search_guest_in_house_js

Parameters

Definition