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)

Definition

DECLARE
    json_result json;
    _statuses char[];
    _bk_arrival date;
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;


    WITH RES as (
        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(qty||' '||rt.name||' @ '||rp.name) details
        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   --(statuses like '%'||b.status||'%') --and (bi.room_numbers is not null)
            b.status = ANY(_statuses)
            AND ((i_search_text is null)
                OR (b.id::text = i_search_text)
                OR (b.confirm_no::text = i_search_text)
                OR (b.voucher_no = i_search_text)
                OR (g.full_name like '%'||i_search_text||'%')
                OR is_match_any(more_guest.names, i_search_text, match_partial:=true, ignore_case:=true)
--                OR EXISTS(
--                     SELECT * FROM UNNEST(more_guest.names) AS mg_name
--                     WHERE mg_name like '%'||i_search_text||'%'
--                   )
                )
            AND (i_arrival is null OR $3 = b.arrival)
            AND (_bk_arrival IS NULL OR _bk_arrival = bi.arrival) --ANY(SELECT arrival FROM booking_items i WHERE i.booking_id = b.id))
            AND (i_departure is null or i_departure = b.departure)
            AND (i_stay_date is null or i_stay_date between b.arrival and b.departure)
            AND (i_room_number is NULL OR is_start_with(rm.rooms, i_room_number))--i_room_number = ANY(rm.rooms))
            AND (i_roomtype_id is null or i_roomtype_id = bi.roomtype_id)
            AND (i_channel_name is null or c.name like '%'||i_channel_name||'%')
            and (i_vip_id is null or i_vip_id = g.vip_id or i_vip_id = any(more_guest.vips))
            and (i_member_code is null or i_member_code = b.member_code)
            and (i_company is null
                 or g.company ilike '%'||i_company||'%'
                 or is_match_any(more_guest.companies, i_company, match_partial:=true, ignore_case:=true))
            and (i_nation_id is null or i_nation_id = g.nation_id or i_nation_id = any(more_guest.nations))
            and (i_rateplan_id is null or i_rateplan_id = bi.rateplan_id)
            and (i_carplate_no is null or replace(g.carplate_no, ' ', '') like '%'||i_carplate_no||'%'
                OR EXISTS(
                     SELECT * FROM UNNEST(more_guest.carplates) AS mg_carplate
                     WHERE replace(mg_carplate, ' ', '') like '%'||i_carplate_no||'%'
                   ))
        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
        ORDER BY b.id desc
    )
    SELECT json_agg(RES) FROM RES
    WHERE NOT expected_arrival_mode OR (qty- inhouse_qty) > 0
    into json_result;

    RETURN COALESCE(json_result, '[]');
END

sp_search_booking_js

Parameters

Definition