aonestar.public

Parameters

Name	Type	Mode
i_statuses	text	IN (DEFAULT 'RCWI')
i_search_text	text	IN (DEFAULT NULL)
i_arrival	date	IN (DEFAULT NULL)
i_departure	date	IN (DEFAULT NULL)
i_stay_date	date	IN (DEFAULT NULL)
i_room_number	text	IN (DEFAULT NULL)
i_roomtype_id	integer	IN (DEFAULT NULL)
i_channel_name	text	IN (DEFAULT NULL)
i_vip_id	integer	IN (DEFAULT NULL)
i_member_code	text	IN (DEFAULT NULL)
i_company	text	IN (DEFAULT NULL)
i_nation_id	integer	IN (DEFAULT NULL)
i_rateplan_id	integer	IN (DEFAULT NULL)
i_carplate_no	text	IN (DEFAULT NULL)
expected_arrival_mode	boolean	IN (DEFAULT false)
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 'booking_id')
i_sort_order	text	IN (DEFAULT 'desc')

Definition

DECLARE
    json_result json;
    _statuses char[];
    _bk_arrival date;
    _sort_stmt text;
    allow_sort_fields CONSTANT text[]
    = '{booking_id,confirm_no,status,guest_name,arrival,departure,night,qty,channel_name,nationality,room_numbers,voucher_no}';
BEGIN
    i_search_text = nullif(i_search_text, '');
    i_carplate_no = nullif(replace(i_carplate_no, ' ', ''), '');
    _statuses = string_to_array(i_statuses, null);

IF expected_arrival_mode THEN
        _statuses = '{R,C,I}';
        _bk_arrival = coalesce(i_arrival, fn_system_date());
        i_arrival = NULL;
    END IF;

-- 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 := 'booking_id';
        i_sort_order := 'DESC';
    end if;

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

EXECUTE format($SQL$
    WITH RES AS (
        -- PUBLIC SCHEMA
        SELECT b.id booking_id, b.confirm_no, b.status, g.full_name guest_name, more_guest.names AS more_guests,
            g.birthdate, b.msg_count, b.flag_count, coalesce(g.confidential, false) confidential,
            b.channel_id, c.name channel_name, b.voucher_no, n.name nationality, g.carplate_no,
            b.arrival, b.departure, b.departure - b.arrival night,
            sum(bi.qty) as qty, sum(bi.qty * bi.adult) as adult, sum(bi.qty * bi.child) as child,
            sum(bi.qty * bi.infant) as infant, sum(bi.block_qty) block_qty,
            sum(bi.inhouse_qty) inhouse_qty, sum(bi.qty + bi.noshow_qty) as book_qty,
            sum(bi.noshow_qty) as noshow_qty, rm.rooms AS room_numbers,
            block.rooms AS block_rooms, inhouse.rooms AS inhouse_rooms,
            array_agg(bi.qty||' '||rt.name||' @ '||rp.name) details,
            false AS is_archived
        FROM booking b
            LEFT JOIN guest g ON g.id = b.guest_id
            LEFT JOIN channel c ON c.id = b.channel_id
            LEFT JOIN nationality n ON n.id = g.nation_id
            LEFT JOIN booking_items bi ON bi.booking_id = b.id
            LEFT JOIN room_type rt ON rt.id = bi.roomtype_id
            LEFT JOIN rate_plan rp ON rp.id = bi.rateplan_id,
            LATERAL (SELECT coalesce(array_agg(full_name::text),'{}') AS names,
                            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 booking_guests bg INNER JOIN guest g ON g.id = bg.guest_id
                     WHERE bg.booking_id = b.id) AS more_guest,
            LATERAL (SELECT coalesce(array_agg(DISTINCT r.room_number ORDER BY r.room_number), '{}') AS rooms
                     FROM room_block rb INNER JOIN room r ON r.id = rb.room_id
                     WHERE rb.booking_id = b.id AND NOT rb.checked_in) AS block,
            LATERAL (SELECT coalesce(array_agg(DISTINCT r.room_number ORDER BY r.room_number), '{}') AS rooms
                     FROM registration rg INNER JOIN room r ON r.id = rg.room_id
                     WHERE rg.booking_id = b.id AND rg.status IN ('I','O')) AS inhouse,
            LATERAL (SELECT array_agg(roomno ORDER BY roomno) AS rooms
                     FROM (SELECT DISTINCT UNNEST(block.rooms||inhouse.rooms) AS roomno) AS r) AS rm
        WHERE b.status = ANY($1)
            AND ($2 IS NULL
                OR (b.id::text = $2) OR (b.confirm_no::text = $2)
                OR (b.voucher_no = $2) OR (g.full_name LIKE '%%'||$2||'%%')
                OR is_match_any(more_guest.names, $2, match_partial:=true, ignore_case:=true))
            AND ($3 IS NULL OR $3 = b.arrival)
            AND ($4 IS NULL OR $4 = bi.arrival)
            AND ($5 IS NULL OR $5 = b.departure)
            AND ($6 IS NULL OR $6 BETWEEN b.arrival AND b.departure)
            AND ($7 IS NULL OR is_start_with(rm.rooms, $7))
            AND ($8 IS NULL OR $8 = bi.roomtype_id)
            AND ($9 IS NULL OR c.name LIKE '%%'||$9||'%%')
            AND ($10 IS NULL OR $10 = g.vip_id OR $10 = ANY(more_guest.vips))
            AND ($11 IS NULL OR $11 = b.member_code)
            AND ($12 IS NULL OR g.company ILIKE '%%'||$12||'%%'
                 OR is_match_any(more_guest.companies, $12, match_partial:=true, ignore_case:=true))
            AND ($13 IS NULL OR $13 = g.nation_id OR $13 = ANY(more_guest.nations))
            AND ($14 IS NULL OR $14 = bi.rateplan_id)
            AND ($15 IS NULL OR replace(g.carplate_no, ' ', '') LIKE '%%'||$15||'%%'
                 OR EXISTS (SELECT * FROM UNNEST(more_guest.carplates) AS mg_carplate
                            WHERE replace(mg_carplate, ' ', '') LIKE '%%'||$15||'%%'))
        GROUP BY b.id, b.confirm_no, b.status, g.full_name, g.birthdate, g.confidential,
            b.channel_id, c.name, n.name, g.carplate_no, b.arrival, b.departure,
            block.rooms, inhouse.rooms, rm.rooms, more_guest.names

UNION ALL

-- BACKUP SCHEMA (guard: only when i_include_archive = true)
        SELECT b.id booking_id, b.confirm_no, b.status, g.full_name guest_name, more_guest.names AS more_guests,
            g.birthdate, b.msg_count, b.flag_count, coalesce(g.confidential, false) confidential,
            b.channel_id, c.name channel_name, b.voucher_no, n.name nationality, g.carplate_no,
            b.arrival, b.departure, b.departure - b.arrival night,
            sum(bi.qty) as qty, sum(bi.qty * bi.adult) as adult, sum(bi.qty * bi.child) as child,
            sum(bi.qty * bi.infant) as infant, sum(bi.block_qty) block_qty,
            sum(bi.inhouse_qty) inhouse_qty, sum(bi.qty + bi.noshow_qty) as book_qty,
            sum(bi.noshow_qty) as noshow_qty, rm.rooms AS room_numbers,
            block.rooms AS block_rooms, inhouse.rooms AS inhouse_rooms,
            array_agg(bi.qty||' '||rt.name||' @ '||rp.name) details,
            true AS is_archived
        FROM backup.booking b
            LEFT JOIN guest g ON g.id = b.guest_id
            LEFT JOIN channel c ON c.id = b.channel_id
            LEFT JOIN nationality n ON n.id = g.nation_id
            LEFT JOIN backup.booking_items bi ON bi.booking_id = b.id
            LEFT JOIN room_type rt ON rt.id = bi.roomtype_id
            LEFT JOIN rate_plan rp ON rp.id = bi.rateplan_id,
            LATERAL (SELECT coalesce(array_agg(full_name::text),'{}') AS names,
                            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.booking_guests bg INNER JOIN guest g ON g.id = bg.guest_id
                     WHERE bg.booking_id = b.id) AS more_guest,
            LATERAL (SELECT '{}'::text[] AS rooms) AS block,
            LATERAL (SELECT coalesce(array_agg(DISTINCT r.room_number ORDER BY r.room_number), '{}') AS rooms
                     FROM backup.registration rg INNER JOIN room r ON r.id = rg.room_id
                     WHERE rg.booking_id = b.id AND rg.status IN ('I','O')) AS inhouse,
            LATERAL (SELECT array_agg(roomno ORDER BY roomno) AS rooms
                     FROM (SELECT DISTINCT UNNEST(block.rooms||inhouse.rooms) AS roomno) AS r) AS rm
        WHERE $17
            AND b.status = ANY($1)
            AND ($2 IS NULL
                OR (b.id::text = $2) OR (b.confirm_no::text = $2)
                OR (b.voucher_no = $2) OR (g.full_name LIKE '%%'||$2||'%%')
                OR is_match_any(more_guest.names, $2, match_partial:=true, ignore_case:=true))
            AND ($3 IS NULL OR $3 = b.arrival)
            AND ($4 IS NULL OR $4 = bi.arrival)
            AND ($5 IS NULL OR $5 = b.departure)
            AND ($6 IS NULL OR $6 BETWEEN b.arrival AND b.departure)
            AND ($7 IS NULL OR is_start_with(rm.rooms, $7))
            AND ($8 IS NULL OR $8 = bi.roomtype_id)
            AND ($9 IS NULL OR c.name LIKE '%%'||$9||'%%')
            AND ($10 IS NULL OR $10 = g.vip_id OR $10 = ANY(more_guest.vips))
            AND ($11 IS NULL OR $11 = b.member_code)
            AND ($12 IS NULL OR g.company ILIKE '%%'||$12||'%%'
                 OR is_match_any(more_guest.companies, $12, match_partial:=true, ignore_case:=true))
            AND ($13 IS NULL OR $13 = g.nation_id OR $13 = ANY(more_guest.nations))
            AND ($14 IS NULL OR $14 = bi.rateplan_id)
            AND ($15 IS NULL OR replace(g.carplate_no, ' ', '') LIKE '%%'||$15||'%%'
                 OR EXISTS (SELECT * FROM UNNEST(more_guest.carplates) AS mg_carplate
                            WHERE replace(mg_carplate, ' ', '') LIKE '%%'||$15||'%%'))
        GROUP BY b.id, b.confirm_no, b.status, g.full_name, g.birthdate, g.confidential,
            b.channel_id, c.name, n.name, g.carplate_no, b.arrival, b.departure,
            block.rooms, inhouse.rooms, rm.rooms, more_guest.names
    ),
    FILTERED AS MATERIALIZED (
        SELECT * FROM RES
        WHERE NOT $16 OR (qty - inhouse_qty) > 0
    ),
    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
          i_search_text,          -- $2
          i_arrival,              -- $3
          _bk_arrival,            -- $4
          i_departure,            -- $5
          i_stay_date,            -- $6
          i_room_number,          -- $7
          i_roomtype_id,          -- $8
          i_channel_name,         -- $9
          i_vip_id,               -- $10
          i_member_code,          -- $11
          i_company,              -- $12
          i_nation_id,            -- $13
          i_rateplan_id,          -- $14
          i_carplate_no,          -- $15
          expected_arrival_mode,  -- $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_booking_js

Parameters

Definition