aonestar.public

Parameters

Name	Type	Mode
guest_id	integer	IN (DEFAULT NULL)
register_id	integer	IN (DEFAULT NULL)
only_checkout	boolean	IN (DEFAULT true)
guest_id	integer	OUT
visit_count	integer	OUT
visit_nights	integer	OUT
last_arrival	date	OUT
last_departure	date	OUT
total_revenue	t_money	OUT
revenues	jsonb	OUT

Definition

with stays as (
        select rg.id as id, rg.guest_id, rg.arrival, rg.departure, rg.nights, f.id as folio_id 
        from registration rg inner join folio f on f.register_id = rg.id 
        where (not only_checkout or rg.status = 'O') and ($2 is null or $2 = rg.id) and ($1 is null or $1 = rg.guest_id)
        union all 
        select rg.id as id, rg.guest_id, rg.arrival, rg.departure, rg.nights, f.id as folio_id  
        from backup.registration rg inner join backup.folio f on f.register_id = rg.id 
        where (not only_checkout or rg.status = 'O') and ($2 is null or $2 = rg.id) and ($1 is null or $1 = rg.guest_id)
    ),trans as (
       Select rg.guest_id, 
            coalesce(dg.category, 'other') as category,
            sum(t.acc_amount) as amount    
       from stays rg 
          inner join transactions t on t.folio_id = rg.folio_id
          left join department d on d.id = t.dept_id 
          left join department_group dg on dg.id = d.group_id 
       where t.post_type = 'P' and t.active 
       group by 1,2  
       order by 1
    ), summ as (
      select guest_id, count(id) as visit_count, sum(nights) as visit_nights, 
             max(arrival) as last_arrival, max(departure) as last_departure, array_agg(id) as registers
      from stays 
      group by 1
    ), guest_rev as ( 
        select guest_id, 
            sum(amount) as total_revenue, 
            jsonb_agg(jsonb_build_object('category',category, 'amount',amount)) as revenues 
            --jsonb_object_agg(category, amount) as revenues 
        from trans group by 1 order by 1 desc
    )
    select s.guest_id, visit_count, visit_nights, last_arrival, last_departure, 
           coalesce(r.total_revenue, 0.0) as total_revenue, 
           coalesce(r.revenues, '[]'::jsonb)
    from summ s left join guest_rev r on r.guest_id = s.guest_id;

sp_get_guest_stat

Parameters

Definition