aonestar.public

Parameters

Name	Type	Mode
i_folio_type	character	IN (DEFAULT NULL)
i_status	character	IN (DEFAULT NULL)
i_search_text	text	IN (DEFAULT NULL)
i_room_number	text	IN (DEFAULT NULL)
i_folio_id	integer	IN (DEFAULT NULL)
i_register_id	integer	IN (DEFAULT NULL)
i_booking_id	integer	IN (DEFAULT NULL)
i_channel_name	text	IN (DEFAULT NULL)
i_arrival	date	IN (DEFAULT NULL)
i_departure	date	IN (DEFAULT NULL)
i_stay_date	date	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 'folio_id')
i_sort_order	text	IN (DEFAULT 'desc')

Definition

DECLARE
    json_result json;
    _sort_stmt text;
    allow_sort_fields CONSTANT text[]
    = '{folio_id,folio_type,status,booking_id,register_id,room_number,name,arrival,departure,night,channel_name,total_balance,folio_total,folio_balance1,folio_balance2,folio_balance3,folio_balance4}';
BEGIN
    -- 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 := 'folio_id';
        i_sort_order := 'DESC';
    end if;

-- Alias: folio_total -> total_balance
    if lower(i_sort_field) = 'folio_total' then
        i_sort_field := 'total_balance';
    end if;

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

EXECUTE format($SQL$
    WITH DATASET AS (
        -- PUBLIC SCHEMA
        SELECT f.id AS folio_id, f.folio_type, f.status,
            iif(f.folio_type = 'R', rg.booking_id, f.booking_id) AS booking_id,
            bk.status AS booking_status,
            f.register_id, rg.room_id, rm.room_number,
            CASE f.folio_type WHEN 'R' THEN g.full_name WHEN 'B' THEN COALESCE(g.full_name, grp.name) WHEN 'C' THEN cs.name END AS name,
            (g.doc_id IS NOT NULL OR d.file_path IS NOT NULL) AS has_image,
            COALESCE(iif(f.folio_type = 'R', rg.msg_count, bk.msg_count), 0) AS msg_count,
            COALESCE(iif(f.folio_type = 'R', rg.flag_count, bk.flag_count), 0) AS flag_count,
            COALESCE(grp."name", '') AS group_name, mg.more_guests, cn."name" AS channel_name,
            CASE f.folio_type WHEN 'R' THEN rg.arrival WHEN 'B' THEN bk.arrival WHEN 'C' THEN f.open_date END AS arrival,
            CASE f.folio_type WHEN 'R' THEN rg.departure WHEN 'B' THEN bk.departure WHEN 'C' THEN f.close_date END AS departure,
            CASE f.folio_type WHEN 'R' THEN rg.departure-rg.arrival WHEN 'B' THEN bk.departure-bk.arrival WHEN 'C' THEN f.close_date-f.open_date END AS night,
            COALESCE(rg.adult,0) adult, COALESCE(rg.child,0) child, COALESCE(rg.infant,0) infant,
            f.open_date, f.open_time, f.open_user, f.close_date, f.close_time, f.close_user,
            COALESCE(f1.balance,0.0) as folio_balance1, COALESCE(f2.balance,0.0) as folio_balance2,
            COALESCE(f3.balance,0.0) as folio_balance3, COALESCE(f4.balance,0.0) as folio_balance4,
            COALESCE(f1.balance,0.0)+COALESCE(f2.balance,0.0)+COALESCE(f3.balance,0.0)+COALESCE(f4.balance,0.0) AS total_balance,
            false AS is_archived
        FROM folio f
            LEFT JOIN folio_items f1 ON f1.folio_id = f.id AND f1.folio_seq = 1 AND NOT f1.closed
            LEFT JOIN folio_items f2 ON f2.folio_id = f.id AND f2.folio_seq = 2 AND NOT f2.closed
            LEFT JOIN folio_items f3 ON f3.folio_id = f.id AND f3.folio_seq = 3 AND NOT f3.closed
            LEFT JOIN folio_items f4 ON f4.folio_id = f.id AND f4.folio_seq = 4 AND NOT f4.closed
            LEFT JOIN registration rg ON rg.id = f.register_id
            LEFT JOIN booking bk ON bk.id = f.booking_id
            LEFT JOIN cash_sale cs ON cs.folio_id = f.id
            LEFT JOIN room rm ON rm.id = rg.room_id
            LEFT JOIN guest g ON g.id = iif(f.folio_type = 'B', bk.guest_id, rg.guest_id)
            LEFT JOIN doc.documents d ON d.doc_type = 'guest_photo' AND d.ref_code = g.id::text
            LEFT JOIN group_info grp ON grp.id = iif(f.folio_type = 'B', bk.group_id, rg.group_id)
            LEFT JOIN channel cn ON cn.id = CASE f.folio_type WHEN 'R' THEN rg.channel_id WHEN 'B' THEN bk.channel_id ELSE cs.channel_id END,
            LATERAL (SELECT coalesce(array_agg(gi.full_name),'{}') as more_guests
                     FROM register_guests mg LEFT JOIN guest gi ON gi.id = mg.guest_id
                     WHERE mg.register_id = f.register_id AND mg.guest_id <> rg.guest_id) AS mg
        WHERE ($1 IS NULL OR $1 = f.folio_type)
            AND ($2 IS NULL OR $2 = f.status)
            AND ($3 IS NULL OR $3 = f.id)
            AND ($4 IS NULL OR $4 = f.register_id)
            AND ($5 IS NULL OR $5 = f.booking_id OR $5 = rg.booking_id)
            AND ($6 IS NULL OR $6 = rg.arrival OR $6 = bk.arrival OR (f.folio_type='C' AND $6 = f.open_date))
            AND ($7 IS NULL OR $7 = rg.departure OR $7 = bk.departure OR (f.folio_type='C' AND $7 = f.close_date))
            AND ($8 IS NULL OR $8 BETWEEN rg.arrival AND rg.departure OR $8 BETWEEN bk.arrival AND bk.departure OR (f.folio_type='C' AND $8 BETWEEN f.open_date AND f.close_date))
            AND ($9 IS NULL OR upper(rm.room_number) LIKE upper($9)||'%%')
            AND ($10 IS NULL OR lower(cn."name") LIKE '%%'||lower($10)||'%%')
            AND ($11 IS NULL
                OR upper(rm.room_number) = upper($11)
                OR upper(g.full_name) LIKE '%%'||upper($11)||'%%'
                OR upper(grp."name") LIKE '%%'||upper($11)||'%%'
                OR lower(cs."name") LIKE '%%'||lower($11)||'%%'
                OR f.id::text = $11 OR f.register_id::text = $11
                OR f.booking_id::text = $11 OR rg.booking_id::text = $11
                OR EXISTS (SELECT 1 FROM UNNEST(mg.more_guests) aname WHERE aname LIKE '%%'||$11||'%%'))

UNION ALL

-- BACKUP SCHEMA (guard: only when i_include_archive = true)
        SELECT f.id AS folio_id, f.folio_type, f.status,
            iif(f.folio_type = 'R', rg.booking_id, f.booking_id) AS booking_id,
            bk.status AS booking_status,
            f.register_id, rg.room_id, rm.room_number,
            CASE f.folio_type WHEN 'R' THEN g.full_name WHEN 'B' THEN COALESCE(g.full_name, grp.name) WHEN 'C' THEN cs.name END AS name,
            (g.doc_id IS NOT NULL OR d.file_path IS NOT NULL) AS has_image,
            COALESCE(iif(f.folio_type = 'R', rg.msg_count, bk.msg_count), 0) AS msg_count,
            COALESCE(iif(f.folio_type = 'R', rg.flag_count, bk.flag_count), 0) AS flag_count,
            COALESCE(grp."name", '') AS group_name, mg.more_guests, cn."name" AS channel_name,
            CASE f.folio_type WHEN 'R' THEN rg.arrival WHEN 'B' THEN bk.arrival WHEN 'C' THEN f.open_date END AS arrival,
            CASE f.folio_type WHEN 'R' THEN rg.departure WHEN 'B' THEN bk.departure WHEN 'C' THEN f.close_date END AS departure,
            CASE f.folio_type WHEN 'R' THEN rg.departure-rg.arrival WHEN 'B' THEN bk.departure-bk.arrival WHEN 'C' THEN f.close_date-f.open_date END AS night,
            COALESCE(rg.adult,0) adult, COALESCE(rg.child,0) child, COALESCE(rg.infant,0) infant,
            f.open_date, f.open_time, f.open_user, f.close_date, f.close_time, f.close_user,
            COALESCE(f1.balance,0.0) as folio_balance1, COALESCE(f2.balance,0.0) as folio_balance2,
            COALESCE(f3.balance,0.0) as folio_balance3, COALESCE(f4.balance,0.0) as folio_balance4,
            COALESCE(f1.balance,0.0)+COALESCE(f2.balance,0.0)+COALESCE(f3.balance,0.0)+COALESCE(f4.balance,0.0) AS total_balance,
            true AS is_archived
        FROM backup.folio f
            LEFT JOIN backup.folio_items f1 ON f1.folio_id = f.id AND f1.folio_seq = 1 AND NOT f1.closed
            LEFT JOIN backup.folio_items f2 ON f2.folio_id = f.id AND f2.folio_seq = 2 AND NOT f2.closed
            LEFT JOIN backup.folio_items f3 ON f3.folio_id = f.id AND f3.folio_seq = 3 AND NOT f3.closed
            LEFT JOIN backup.folio_items f4 ON f4.folio_id = f.id AND f4.folio_seq = 4 AND NOT f4.closed
            LEFT JOIN backup.registration rg ON rg.id = f.register_id
            LEFT JOIN backup.booking bk ON bk.id = f.booking_id
            LEFT JOIN backup.cash_sale cs ON cs.folio_id = f.id
            LEFT JOIN room rm ON rm.id = rg.room_id
            LEFT JOIN guest g ON g.id = iif(f.folio_type = 'B', bk.guest_id, rg.guest_id)
            LEFT JOIN doc.documents d ON d.doc_type = 'guest_photo' AND d.ref_code = g.id::text
            LEFT JOIN group_info grp ON grp.id = iif(f.folio_type = 'B', bk.group_id, rg.group_id)
            LEFT JOIN channel cn ON cn.id = CASE f.folio_type WHEN 'R' THEN rg.channel_id WHEN 'B' THEN bk.channel_id ELSE cs.channel_id END,
            LATERAL (SELECT coalesce(array_agg(gi.full_name),'{}') as more_guests
                     FROM backup.register_guests mg LEFT JOIN guest gi ON gi.id = mg.guest_id
                     WHERE mg.register_id = f.register_id AND mg.guest_id <> rg.guest_id) AS mg
        WHERE $12
            AND ($1 IS NULL OR $1 = f.folio_type)
            AND ($2 IS NULL OR $2 = f.status)
            AND ($3 IS NULL OR $3 = f.id)
            AND ($4 IS NULL OR $4 = f.register_id)
            AND ($5 IS NULL OR $5 = f.booking_id OR $5 = rg.booking_id)
            AND ($6 IS NULL OR $6 = rg.arrival OR $6 = bk.arrival OR (f.folio_type='C' AND $6 = f.open_date))
            AND ($7 IS NULL OR $7 = rg.departure OR $7 = bk.departure OR (f.folio_type='C' AND $7 = f.close_date))
            AND ($8 IS NULL OR $8 BETWEEN rg.arrival AND rg.departure OR $8 BETWEEN bk.arrival AND bk.departure OR (f.folio_type='C' AND $8 BETWEEN f.open_date AND f.close_date))
            AND ($9 IS NULL OR upper(rm.room_number) LIKE upper($9)||'%%')
            AND ($10 IS NULL OR lower(cn."name") LIKE '%%'||lower($10)||'%%')
            AND ($11 IS NULL
                OR upper(rm.room_number) = upper($11)
                OR upper(g.full_name) LIKE '%%'||upper($11)||'%%'
                OR upper(grp."name") LIKE '%%'||upper($11)||'%%'
                OR lower(cs."name") LIKE '%%'||lower($11)||'%%'
                OR f.id::text = $11 OR f.register_id::text = $11
                OR f.booking_id::text = $11 OR rg.booking_id::text = $11
                OR EXISTS (SELECT 1 FROM UNNEST(mg.more_guests) aname WHERE aname LIKE '%%'||$11||'%%'))
    ),
    FILTERED AS MATERIALIZED (SELECT * FROM DATASET),
    TOTAL AS (SELECT count(*)::int AS total_rows FROM FILTERED)
    SELECT CASE
        WHEN $13 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 $13 OFFSET GREATEST(0, (COALESCE($14,1) - 1) * $13)
                    ) p), '[]'::json),
                'paging', json_build_object(
                    'current', COALESCE($14,1), 'limit', $13,
                    'total_rows', t.total_rows, 'row_count', LEAST($13, t.total_rows),
                    'sort_field', $15, 'sort_order', $16
                )
            ) FROM TOTAL t)
    END
    $SQL$, _sort_stmt, _sort_stmt)
    USING i_folio_type,          -- $1
          i_status,              -- $2
          i_folio_id,            -- $3
          i_register_id,         -- $4
          i_booking_id,          -- $5
          i_arrival,             -- $6
          i_departure,           -- $7
          i_stay_date,           -- $8
          i_room_number,         -- $9
          i_channel_name,        -- $10
          i_search_text,         -- $11
          i_include_archive,     -- $12
          i_limit,               -- $13
          i_page,                -- $14
          i_sort_field,          -- $15
          i_sort_order           -- $16
    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_folio_js

Parameters

Definition