aonestar.public

Parameters

Name	Type	Mode
guest_id_array	int4[]	IN

Definition

WITH Guests AS (
        SELECT g.id, g.first_name, g.last_name, g.title_id , t.name as title_name, g.full_name, g.sex, g.birthdate, 
            g.nation_id, n.name as nationality, g.country_res_id, cr."name" as country_of_residence, 
            g.lang_id, l.name as "language", g.vip_id, v.name vip_name, 
            company, occupation_id, oc.name as occupation_name,
            credit_limit, COALESCE(confidential, FALSE) AS confidential, 
            member_code, carplate_no, NULL AS photo, 
            coalesce(
                doc.get_file_url('guest_photo', i_guest_id), 
                d.photo_url--(select d.photo_url from guest_document d where d.guest_id = g.id order by id desc limit 1)
            ) AS photo_url,
            remark, COALESCE(likes, '{}') likes, COALESCE(dislikes, '{}') dislikes,
            image_count, visit_count, visit_nights, last_arrival, last_departure, 
            json_build_object(
                'address1', g.address1,
                'address2', g.address2,
                'city_id', g.city_id,
                'city_name', ct."name",
                'country_id', g.country_id,
                'country_name', c.name,
                'emails', COALESCE(g.emails, '{}'),
                'telephones', COALESCE(g.telephones, '{}'),
                'mobiles', COALESCE(g.mobiles, '{}')
            ) as contact,
            json_build_object(
                'doc_type', g.doc_type,
                'doc_number', g.doc_number,
                'expire_date', g.doc_expire,
                'country_id', g.doc_country,
                'country_name', cd.name,
                'photo_url', d.photo_url,
                'image_url', d.image_url
            ) AS "document",
            (select coalesce(array_agg(json_build_object(
                        'id', card_id, 
                        'card_number', cc.card_number,
                        'card_type', cc.card_type,
                        'expire_month', cc.expire_month,
                        'expire_year', cc.expire_year 
                        )
                    ), '{}')
             from unnest(g.credit_cards) card_id left join credit_card cc on cc.id = card_id
            ) as credit_cards,
            (select coalesce(array_agg(json_build_object(
                        'id', sm.id, 
                        'name', sm.name,
                        'url', sm.url,
                        'account_name', gs.account_name,
                        'account_link', fn_path(sm.url, gs.account_name)
                        --'icon', sm.icon,
                        --'icon_type', sm.icon_type 
                        )
                    ), '{}')
             FROM guest_social gs LEFT JOIN social_media sm ON sm.id = gs.social_id 
             WHERE gs.guest_id = g.id
            ) AS social_media
        from unnest($1) i_guest_id LEFT join guest g ON g.id = i_guest_id
             left join title t on t.id = g.title_id
             left join nationality n on n.id = g.nation_id 
             left join "language" l on l.id = g.lang_id 
             left join country c on c.id = g.country_id
             left join country cr on cr.id = g.country_res_id
             left join country cd on cd.id = g.doc_country 
             left join occupation oc on oc.id = g.occupation_id 
             left join city ct on ct.id = g.city_id  
             left join vip v on v.id = g.vip_id
             left join guest_document d on d.id = g.doc_id,
             fn_prop_config('PREFIX_URL_PMS_FILE') AS file_prefix
        WHERE i_guest_id IS NULL or g.id = ANY($1)
    )
    SELECT COALESCE(json_agg(Guests), '[]') JsonData FROM Guests

sp_get_guest_list_js

Parameters

Definition