aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_get_guest_list_js
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