aonestar.public

Parameters

Name	Type	Mode
register_id	integer	IN
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	json	OUT

Definition

WITH stat AS (
        select * from sp_get_guest_stat(register_id := $1, only_checkout := false)
    ), rev AS (
        Select category, sum(amount) as amount 
        from (
            SELECT * 
            FROM stat, lateral jsonb_to_recordset(stat.revenues::jsonb) 
            AS item(category text, amount t_money)
            UNION ALL
            SELECT *
            FROM stat, lateral jsonb_to_recordset((Select g.revenues from guest g where stat.guest_id = g.id)) 
            AS item(category text, amount t_money) 
        ) R
        group by 1
    )
    UPDATE guest g
    SET visit_count    = coalesce(g.visit_count,0)   + s.visit_count,
        visit_nights   = coalesce(g.visit_nights,0)  + s.visit_nights,
        total_revenue  = coalesce(g.total_revenue,0) + s.total_revenue,
        last_arrival   = s.last_arrival,
        last_departure = s.last_departure,
        revenues = coalesce((select jsonb_agg(rev) from rev), '[]'::jsonb)
    FROM stat s
    WHERE g.id = s.guest_id
    RETURNING g.id, g.visit_count, g.visit_nights, g.last_arrival, g.last_departure, g.total_revenue, g.revenues

sp_update_guest_stat

Parameters

Definition