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)

Definition

DECLARE
    json_result json;
BEGIN
    search_text = nullif(search_text, '');
    carplate_no = nullif(replace(carplate_no , ' ', ''), '');

    Select json_agg(RES)
    from (
        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
        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
                        --and (search_text is null or g.full_name like '%'||search_text||'%')
                    ) as mg
        where --(r.status <> 'X') and
            (statuses like '%'||r.status||'%') --and (i.room_numbers is not null)
            and ((search_text is null)
                or (r.id::text = search_text)
                or (r.booking_id::text = search_text)
                --or (r.confirm_no::text = search_text)
                or (r.voucher_no = search_text)
                or (upper(rm.room_number) = upper(search_text))
                or (g.full_name like '%'||search_text||'%')
                OR is_match_any(mg.more_guests, search_text, match_partial:=true, ignore_case:=true)
               -- or (EXISTS(SELECT 1 FROM UNNEST(mg.more_guests) aname WHERE aname LIKE '%'||search_text||'%'))
                )
            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)
        --group by  r.id, r.confirm_no, r.status, g.full_name, g.birthdate, g.confidential,
        --  r.channel_id, c.name, n.name, g.carplate_no, r.arrival, r.departure--, i.room_numbers
        order by r.id desc
        --LIMIT 1500
    ) as RES
    into json_result;

    RETURN COALESCE(json_result, '[]');
END

sp_search_guest_in_house_js

Parameters

Definition