aonestar.public

Parameters

Name	Type	Mode
p_id	integer	IN (DEFAULT NULL)
p_guest_id	integer	IN (DEFAULT NULL)
p_doc_type	smallint	IN (DEFAULT NULL)
p_doc_number	text	IN (DEFAULT NULL)
p_expire_date	date	IN (DEFAULT NULL)
p_country_id	integer	IN (DEFAULT NULL)

Definition

declare
    result_json json;
begin
    select
    json_build_object(
        'photo_url', coalesce(gd.photo_url, '') ,
        'name', coalesce((select g.first_name || ' ' || g.last_name from guest g where g.id=gd.guest_id limit 1),'') ,
        'sex', coalesce((select g.sex from guest g where g.id = gd.guest_id limit 1), '') ,
        'passport', coalesce(gd.doc_number,'') ,
        'country', coalesce((select c.name from country c where c.id = (select g.country_id from guest g where g.id=gd.guest_id limit 1 ) limit 1), '') ,
        'nationality',coalesce((select n.name from nationality n where n.id = gd.nation_id limit 1), '') ,
        'birth_date', coalesce((select birthdate from guest g where g.id = gd.guest_id limit 1)::text, '')
    ) into result_json
    from guest_document gd
    where (
        (p_id is null or gd.id = p_id)
        and (p_guest_id is null or gd.guest_id = p_guest_id)
        and (p_doc_type is null or gd.doc_type = p_doc_type)
        and (p_doc_number is null or p_doc_number = '' or gd.doc_number = p_doc_number)
        and (p_expire_date is null or gd.expire_date = p_expire_date)
        and (p_country_id is null or gd.country_id = p_country_id)
    );

return coalesce(result_json, '{}'::json);  -- เปลี่ยนจาก '[]' เป็น '{}'
end;

sp_get_guest_document_json

Parameters

Definition