aonestar.public

Parameters

Name	Type	Mode
p_show_assigned	boolean	IN (DEFAULT false)

Definition

DECLARE
    v_lang text := fn_intf_param('IDCARD', 'lang', 'en');
    v_keep_days integer := fn_prop_config('GUEST_DOCUMENT_POOL_KEEP_DAYS', '7')::integer;
    v_result json;
BEGIN
    WITH docs AS (
        SELECT d.id AS guest_doc_id,
               d.expire_date,
               d.doc_type,
               d.doc_number,
               d.doc_number AS passport,
               CASE WHEN d.doc_type = 1 THEN d.doc_data->>'given_name'
                    ELSE iif(v_lang = 'th', d.doc_data->>'name_th', d.doc_data->>'name_en') END AS first_name,
               CASE WHEN d.doc_type = 1 THEN d.doc_data->>'surname'
                    ELSE iif(v_lang = 'th', d.doc_data->>'lastname_th', d.doc_data->>'lastname_en') END AS last_name,
               CASE WHEN d.doc_type = 2
                    THEN iif(v_lang = 'th', d.doc_data->>'title_th', d.doc_data->>'title_en') END AS title,
               CASE WHEN d.doc_data->>'sex' IN ('1', 'M') THEN 'Male'
                    WHEN d.doc_data->>'sex' IN ('2', 'F') THEN 'Female' END AS sex,
               to_char(to_date(d.doc_data->>'date_of_birth', 'YYYY-MM-DD'), 'DD-MM-YYYY') AS birth_date,
               c.name AS country,
               n.name AS nationality,
               CASE WHEN d.doc_type = 2 THEN fn_format_address(d.doc_data->'address') END AS address,
               iif(d.pending_assignment, d.tag, 'ASSIGNED') AS tag,
               d.guest_id,
               d.photo_url,
               d.image_url,
               NOT d.pending_assignment AS is_assigned,
               d.pending_assignment
        FROM guest_document d
        LEFT JOIN country c ON c.id = d.country_id
        LEFT JOIN nationality n ON n.id = d.nation_id
        WHERE (p_show_assigned OR d.pending_assignment)
          AND d.last_scan_time >= current_timestamp - make_interval(days => v_keep_days)
    ), grouped AS (
        SELECT tag, is_assigned,
               json_agg(docs ORDER BY is_assigned, guest_doc_id) AS documents
        FROM docs
        GROUP BY tag, is_assigned
    )
    SELECT json_agg(
               json_build_object('tag', tag, 'documents', documents)
               ORDER BY is_assigned, tag DESC
           )
    INTO v_result
    FROM grouped;

RETURN COALESCE(v_result, '[]'::json);
END

sp_get_scanned_documents_json

Parameters

Definition