aonestar.public

Parameters

Name	Type	Mode
i_guest_name	text	IN (DEFAULT NULL)
i_doc_number	text	IN (DEFAULT NULL)
i_member_code	text	IN (DEFAULT NULL)
i_email	text	IN (DEFAULT NULL)
i_telephone	text	IN (DEFAULT NULL)
i_nation_id	integer	IN (DEFAULT NULL)
i_country_id	integer	IN (DEFAULT NULL)
i_vip_id	integer	IN (DEFAULT NULL)
i_birthdate	date	IN (DEFAULT NULL)
i_arrival	date	IN (DEFAULT NULL)
i_departure	date	IN (DEFAULT NULL)
i_stay_date	date	IN (DEFAULT NULL)
i_channel_id	integer	IN (DEFAULT NULL)
i_company	text	IN (DEFAULT NULL)
i_visittime_op	text	IN (DEFAULT NULL)
i_visittime_value	integer	IN (DEFAULT NULL)
i_visitnight_op	text	IN (DEFAULT NULL)
i_visitnight_value	integer	IN (DEFAULT NULL)
i_page	integer	IN (DEFAULT 1)
i_limit	integer	IN (DEFAULT 20)
i_sort_field	text	IN (DEFAULT 'id')
i_sort_order	text	IN (DEFAULT 'desc')

Definition

declare 
    _phone_number text = null;
    _sort_stmt text;
    json_result json;
    sql_text text;
    allow_sort_fields CONSTANT text[] 
    = '{id,full_name,sex,birthdate,age,nationality,vip_level,company,telephones,mobiles,fax,emails,member_code,doc_number,visit_count,visit_nights,last_arrival,last_departure}'::text[];
begin   
    i_page := coalesce(i_page, 1);
    i_limit := coalesce(i_limit, 20);

    i_sort_order := IIF(lower(i_sort_order) <> 'desc', 'DESC', 'ASC');

    if i_sort_field is null or (lower(i_sort_field) <> ALL(allow_sort_fields)) then
        i_sort_field := 'id';
        i_sort_order := 'desc';
       -- raise exception 'Invalid sort field "%"', i_sort_field;
    end if;

    _sort_stmt := lower(i_sort_field)||IIF(lower(i_sort_order) <> 'desc', ' DESC', ' ASC');

    if i_sort_field <> 'id' then
        _sort_stmt := _sort_stmt || ' NULLS LAST, id DESC';
    end if;

--    i_visittime_value  := coalesce(i_visittime_value, 0);
--    i_visittime_op     := coalesce(i_visittime_op, '>=');
--    i_visitnight_value := coalesce(i_visitnight_value, 0);
--    i_visitnight_op    := coalesce(i_visitnight_op, '>=');
    i_guest_name  := nullif(i_guest_name, '');
    i_doc_number  := nullif(i_doc_number, '');
    i_member_code := nullif(i_member_code, '');
    i_email       := nullif(i_email, '');
    i_telephone   := nullif(i_telephone, '');
    i_company     := nullif(i_company, '');
    if (i_telephone is not null) then
        _phone_number := regexp_replace(i_telephone, '[^0-9]', '', 'g');
    end if;

    SQL_TEXT := $SQL1$
    with dataset as (
        select g.id, 
            g.full_name, 
            g.sex, 
            g.birthdate,  
            case 
                when (date_part('year', age(g.birthdate)) < 0) or (date_part('year', age(g.birthdate)) > 124) 
                    then null
                else date_part('year', age(g.birthdate))
            end as "age", 
            n.name as nationality, v.name as vip_level, 
            g.company, 
            coalesce(g.telephones, '{}') as telephones, 
            coalesce(g.mobiles, '{}') as mobiles, 
            g.fax, 
            coalesce(g.emails, '{}') as emails, 
            g.member_code, 
            nullif(g.doc_number,'') as doc_number, 
            g.visit_count, 
            g.visit_nights, 
            g.last_arrival, 
            g.last_departure--,
            --rpc.guest_history(null, jsonb_build_object('guest_id', g.id)) as visits
        from guest as g
            left join nationality as n on g.nation_id = n.id
            left join vip as v on g.vip_id = v.id
        where ($3 is null or g.full_name ilike '%'||$3||'%')
                and ($4 is null or lower($4) = lower(g.doc_number))
                and ($5 is null or lower($5) = lower(g.member_code))
                and ($6 is null or lower($6) in (select lower(al_email) from unnest(g.emails) as al_email))
                and ($7 is null 
                    or $7 = any(select regexp_replace(phone, '[^0-9]', '', 'g') FROM unnest(g.mobiles) phone)
                    or $7 = any(select regexp_replace(phone, '[^0-9]', '', 'g') FROM unnest(g.telephones) phone)
                    )
                and ($8 is null or $8 = g.nation_id)
                and ($9 is null or $9 = g.country_id)
                and ($10 is null or $10 = g.vip_id)
                and ($11 is null or $11 = g.birthdate)
                and (($12 is null and $13 is null and $14 is null and $15 is null)
                      or exists(
                             select 1 from registration rg 
                             where rg.status = 'O' 
                                and ($12 is null or $12 = rg.arrival)
                                and ($13 is null or $13 = rg.departure)
                                and ($14 is null or $14 between rg.arrival and rg.departure-1)
                                and ($15 is null or $15 = rg.channel_id)
                             union all
                             select 1 from backup.registration rg
                             where rg.status = 'O' 
                                and ($12 is null or $12 = rg.arrival)
                                and ($13 is null or $13 = rg.departure)
                                and ($14 is null or $14 between rg.arrival and rg.departure-1)
                                and ($15 is null or $15 = rg.channel_id)   
                         ) 
                    )
                and ($16 is null or $16 = g.company)
                and ($18 is null or fn_match_value($17, g.visit_count, $18))
                and ($20 is null or fn_match_value($19, g.visit_nights, $20))
    ), items as (
        SELECT * FROM dataset
        ORDER BY $SQL1$ || _sort_stmt || $SQL2$
        LIMIT $1
        OFFSET (($2::integer - 1) * $1)
    ), total as (
        SELECT count(*) as total_rows from dataset
    )
    SELECT json_build_object(
        'items', (Select COALESCE(json_agg(items), '[]') from items),
        'paging', json_build_object(
            'current', $2,
            'limit', $1,
            'total_rows', total_rows,
            'row_count', LEAST($1, total_rows)
        )
    )
    FROM total;
    
    $SQL2$ ;

    --raise notice '%', SQL_TEXT;

    EXECUTE SQL_TEXT
    USING i_limit, i_page, i_guest_name, i_doc_number, i_member_code, i_email, _phone_number, i_nation_id,
          i_country_id, i_vip_id, i_birthdate, i_arrival, i_departure, i_stay_date, i_channel_id, i_company, 
          i_visittime_op, i_visittime_value, i_visitnight_op, i_visitnight_value
    INTO json_result;

    RETURN json_result;   
end

sp_search_guest_js

Parameters

Definition