aonestar.public

Parameters

Name	Type	Mode
arrival_date	date	IN (DEFAULT NULL)

Definition

with INH as (
    select r.id as register_id, r.room_id, rm.room_number, r.guest_id,
        r.arrival, r.departure, r.status, r.adult, r.child, r.infant, r.pax,
        coalesce(r.group_id,0) as group_id,
        coalesce(grp.name, 'Individuals') as group_name
    from registration r
        left join group_info grp on grp.id = r.group_id
        left join room rm on rm.id = r.room_id
    where (r.status = 'I') and ($1 is null or r.arrival = $1)
), guests as (
    select m.guest_id, g.full_name, g.first_name, g.last_name, t.name as title, g.sex,
        coalesce(g.address1, '')||coalesce(trim(' '||g.address2), '') as address,
        g.doc_number, gc.name as country_name, gn.name as nation_name, g.birthdate,
        coalesce(doc.get_file_url('guest_photo', m.guest_id), gd.photo_url) AS photo_url,
        --gd.photo_url,
        gd.image_url
    from (
        select inh.guest_id from INH
        union
        select m.guest_id
        from INH left join register_guests m on m.register_id = inh.register_id
    ) m
    left join guest g on m.guest_id = g.id
    left join nationality gn on gn.id = g.nation_id
    left join country gc on gc.id = g.country_id
    left join guest_document gd on gd.id = g.doc_id
    left join title t on t.id = g.title_id
), dataset as (
    select group_id, group_name, inh.room_number,
        json_build_object(
           'register_id', inh.register_id,
           'room_number', inh.room_number,
           'room_id', inh.room_id,
           'registration_id', inh.register_id,
           'arrival', TO_CHAR(inh.arrival, 'DD-MM-YYYY'),
           'departure', TO_CHAR(inh.departure, 'DD-MM-YYYY'),
           'summoreguest', COALESCE(inh.adult + inh.child + inh.infant -1, 0),
           'pax', inh.pax,
           'guest_id', inh.guest_id,
           'guest', g.full_name,
           'first_name', g.first_name,
           'last_name', g.last_name,
           'title', g.title,
           'sex', g.sex,
           'address', g.address,
           'passport', g.doc_number,
           'country', g.country_name,
           'nationality', g.nation_name,
           'birthdate', TO_CHAR(g.birthdate, 'DD-MM-YYYY'),
           'status', inh.status,
           'accno', inh.register_id,
           'photo_url', g.photo_url,
           'image_url', g.image_url,
           'moreguest', COALESCE((
                select json_agg(
                    json_build_object(
                       'guest_id', inh.guest_id,
                       'guest', mg.full_name,
                       'first_name', mg.first_name,
                       'last_name', mg.last_name,
                       'title', mg.title,
                       'sex', mg.sex,
                       'address', mg.address,
                       'passport', mg.doc_number,
                       'country', mg.country_name,
                       'nationality', mg.nation_name,
                       'birthdate', TO_CHAR(mg.birthdate, 'DD-MM-YYYY'),
                       'status', inh.status,
                       'accno', inh.register_id,
                       'photo_url', mg.photo_url,
                       'image_url', mg.image_url
                    ) ORDER BY mg.guest_id
                )
                from register_guests rg left join guests mg on rg.guest_id = mg.guest_id
                where rg.register_id = inh.register_id
            ),'[]'::json)
        ) as rooms
    from INH left join guests g on g.guest_id = inh.guest_id
)
select fn_result_success(json_agg(
           json_build_object(
               'group_id', a.group_id,
               'group_name', a.group_name,
               'rooms', a.rooms
           )
           order by a.group_id, a.group_name
       ))
from (
    select d.group_id, d.group_name, json_agg(d.rooms order by d.room_number) as rooms
    from dataset d
    group by d.group_id, d.group_name
) as A

sp_get_scan_inhouse_json

Parameters

Definition