aonestar.public

Parameters

Name	Type	Mode
p_show_assigned	boolean	IN (DEFAULT false)

Definition

declare
    v_lang text = fn_intf_param('IDCARD','lang', 'en');
    result_json json;
begin
    with passports as (
        select d.id as guest_doc_id, d.expire_date, d.doc_type, d.doc_number, d.doc_number as passport, 
            d.doc_data ->> 'given_name' as first_name,
            d.doc_data ->> 'surname' as last_name,
            null::text as title,
            case
                when d.doc_data ->> 'sex' in ('1','M') then 'Male'
                when d.doc_data ->> 'sex' in ('2','F') then 'Female' 
                else null
            end as sex,
            to_char(to_date(d.doc_data ->> 'date_of_birth', 'YYYY-MM-DD'),'DD-MM-YYYY') as birth_date,
           -- c.id as country_id, n.id as nation_id, 
            c.name as country, n.name as nationality,
            null::text as address,
            iif(is_assigned, 'ASSIGNED', d.tag) as tag,
            d.guest_id, d.photo_url, d.image_url, d.is_assigned
        from guest_document d
            left join country c on c.code = d.doc_data ->> 'country_code'
            left join nationality n on n.iso_code = d.doc_data ->> 'nation_code'
        where d.doc_type = 1 and (p_show_assigned or not d.is_assigned)
            and (current_timestamp - d.create_time) <= interval'7 days' 
    ), id_cards as (
        select d.id as guest_doc_id, d.expire_date, d.doc_type, d.doc_number, d.doc_number as passport,  
            iif(v_lang = 'th', d.doc_data->>'name_th', d.doc_data->>'name_en') as first_name,
            iif(v_lang = 'th', d.doc_data->>'lastname_th', d.doc_data->>'lastname_en') as last_name,
            iif(v_lang = 'th', d.doc_data->>'title_th', d.doc_data->>'title_en') as first_name,
            case
                when d.doc_data ->> 'sex' in ('1','M') then 'Male'
                when d.doc_data ->> 'sex' in ('2','F') then 'Female' 
                else null
            end as sex,
            to_char(to_date(d.doc_data ->> 'date_of_birth', 'YYYY-MM-DD'),'DD-MM-YYYY') as birth_date,
            --c.id as country_id, n.id as nation_id, 
            c.name as country, n.name as nationality,
            fn_format_address(d.doc_data -> 'address') as address,
            iif(is_assigned, 'ASSIGNED', d.tag) as tag,
            d.guest_id, d.photo_url, d.image_url, d.is_assigned
        from guest_document d 
            left join country c on c.code = 'THA'
            left join nationality n on n.iso_code = 'THA'
        where d.doc_type = 2 and (p_show_assigned or not d.is_assigned)
            and (current_timestamp - d.create_time) <= interval'7 days' 
    )
    , grouped_doc AS (
        SELECT tag, is_assigned,
            json_agg(docs
            --json_agg(row_to_json(select guest_doc_id, doc_type, guest_id, first_name, last_name, sex, passport, country, nationality, birth_date, address, photo_url, image_url) 
                     order by is_assigned, guest_doc_id
            ) as documents
        FROM (
            select * from passports
            union all
            select * from id_cards
        ) docs
        GROUP BY is_assigned, tag
    )
    SELECT json_agg(
               json_build_object('tag', tag, 'documents', documents )
               order by is_assigned,tag desc
           ) 
    FROM grouped_doc
    into result_json;

    return COALESCE(result_json, '[]'::json);
end;

sp_get_scanned_documents_json

Parameters

Definition