aonestar.public

Parameters

Name	Type	Mode
i_guest_id	integer	IN (DEFAULT NULL)

Definition

DECLARE
    _all  boolean = (i_guest_id IS NULL);
    _rows integer = 0;
BEGIN

WITH scope AS (
        SELECT g.id AS guest_id
        FROM public.guest g
        WHERE _all OR g.id = i_guest_id
    ), agg AS (
        SELECT gh.guest_id,
               count(*)::integer                            AS visit_count,
               coalesce(sum(gh.nights), 0)::integer          AS visit_nights,
               max(gh.arrival)                              AS last_arrival,
               max(gh.departure)                             AS last_departure,
               coalesce(sum(gh.total_revenue), 0)::public.t_money AS total_revenue
        FROM public.guest_history gh
        WHERE _all OR gh.guest_id = i_guest_id
        GROUP BY 1
    ), cat AS (
        SELECT gh.guest_id, e.category, sum(e.amount)::public.t_money AS amount
        FROM public.guest_history gh
             CROSS JOIN LATERAL jsonb_to_recordset(gh.revenues)
                 AS e(category text, amount public.t_money)
        WHERE _all OR gh.guest_id = i_guest_id
        GROUP BY 1, 2
    ), revs AS (
        SELECT c.guest_id,
               jsonb_agg(jsonb_build_object('category', c.category, 'amount', c.amount)
                         ORDER BY c.category) AS revenues
        FROM cat c
        GROUP BY 1
    ), target AS (
        SELECT s.guest_id,
               coalesce(a.visit_count, 0)   AS visit_count,
               coalesce(a.visit_nights, 0)  AS visit_nights,
               a.last_arrival,
               a.last_departure,
               coalesce(a.total_revenue, 0) AS total_revenue,
               coalesce(r.revenues, '[]'::jsonb) AS revenues
        FROM scope s
             LEFT JOIN agg  a ON a.guest_id = s.guest_id
             LEFT JOIN revs r ON r.guest_id = s.guest_id
    ), upd AS (
        UPDATE public.guest g
        SET visit_count    = t.visit_count,
            visit_nights   = t.visit_nights,
            last_arrival   = t.last_arrival,
            last_departure = t.last_departure,
            total_revenue  = t.total_revenue,
            revenues       = t.revenues
        FROM target t
        WHERE g.id = t.guest_id
          AND (coalesce(g.visit_count, 0), coalesce(g.visit_nights, 0),
               g.last_arrival, g.last_departure,
               coalesce(g.total_revenue, 0), coalesce(g.revenues, '[]'::jsonb))
              IS DISTINCT FROM
              (t.visit_count, t.visit_nights,
               t.last_arrival, t.last_departure,
               t.total_revenue, t.revenues)
        RETURNING 1
    )
    SELECT count(*)::integer FROM upd INTO _rows;

RETURN _rows;
END

sp_refresh_guest_stat

Description

Parameters

Definition