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
i_register_id
integer
IN (DEFAULT NULL)
i_booking_id
integer
IN (DEFAULT NULL)
Definition
WITH Guests AS ( SELECT g.id, g.doc_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 AS 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) AS photo_url, (SELECT fn_presign_or_cached(gsig.signature_path, gsig.signature_url) FROM guest_signature gsig WHERE gsig.guest_id = g.id AND (gsig.register_id = i_register_id OR gsig.booking_id = i_booking_id) LIMIT 1) AS signature_url, remark, COALESCE(likes, '{}') AS likes, COALESCE(dislikes, '{}') AS 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', fn_presign_or_cached(d.photo_path, d.photo_url), 'image_url', fn_presign_or_cached(d.image_path, 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) )), '{}') 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), '[]') FROM Guests;