aonestar.public

Parameters

Name	Type	Mode
i_guest_id	integer	IN (DEFAULT NULL)
p_rebuild	boolean	IN (DEFAULT true)
i_force	boolean	IN (DEFAULT false)
phase	text	OUT
visits	bigint	OUT
guests	bigint	OUT
room_nights	bigint	OUT
revenue	numeric	OUT

Definition

DECLARE
    _synced  integer = 0;
    _refresh integer = 0;
BEGIN
    RETURN QUERY
    SELECT 'before'::text, count(*)::bigint, count(DISTINCT gh.guest_id)::bigint,
           coalesce(sum(gh.nights), 0)::bigint, coalesce(sum(gh.total_revenue), 0)::numeric
    FROM public.guest_history gh
    WHERE i_guest_id IS NULL OR gh.guest_id = i_guest_id;

IF NOT p_rebuild THEN
        RETURN;
    END IF;

/* set-based union public.* + backup.* — เป็นที่เดียวในระบบที่อ่าน backup.*
       (hot path ใช้ sp_upsert_guest_history ที่แตะ public.* เท่านั้น)
       seq scan backup.transactions รอบเดียวต่อ batch คือ plan ที่ถูกของงาน bulk
       จึงไม่ต้องเพิ่ม index บน backup.transactions.folio_id

⚠️ สูตร revenues / room_revenue / rate_revenue / requirements ตรงนี้
       ต้องเหมือนกับใน sp_upsert_guest_history เป๊ะ — TC21 ใน
       pms/tests/test_guest_history_store.sql เป็นตัวจับถ้าใครแก้ที่เดียว */
    WITH reg AS (
        SELECT rg.id AS register_id, rg.guest_id, rg.booking_id, rg.folio_id,
               rg.arrival, rg.departure, rg.adult, rg.child, rg.infant,
               rg.room_id, rg.rateplan_id, rg.channel_id, rg.origin_id, rg.note,
               false AS is_archived
        FROM public.registration rg
        WHERE rg.status = 'O'
          AND rg.guest_id IS NOT NULL
          AND (i_guest_id IS NULL OR rg.guest_id = i_guest_id)
          /* กัน FK violation จาก guest_id ที่ค้าง (guest ถูกลบ/merge ไปแล้ว) */
          AND EXISTS (SELECT 1 FROM public.guest g WHERE g.id = rg.guest_id)
        UNION ALL
        SELECT br.id, br.guest_id, br.booking_id, br.folio_id,
               br.arrival, br.departure, br.adult, br.child, br.infant,
               br.room_id, br.rateplan_id, br.channel_id, br.origin_id, br.note,
               true AS is_archived
        FROM backup.registration br
        WHERE br.status = 'O'
          AND br.guest_id IS NOT NULL
          AND (i_guest_id IS NULL OR br.guest_id = i_guest_id)
          AND NOT EXISTS (SELECT 1 FROM public.registration p WHERE p.id = br.id)
          AND EXISTS (SELECT 1 FROM public.guest g WHERE g.id = br.guest_id)
    ), tran_raw AS (
        SELECT r.register_id, t.dept_id, t.acc_amount
        FROM reg r JOIN public.transactions t ON t.folio_id = r.folio_id
        WHERE t.active AND t.post_type = 'P'
        UNION ALL
        SELECT r.register_id, bt.dept_id, bt.acc_amount
        FROM reg r JOIN backup.transactions bt ON bt.folio_id = r.folio_id
        WHERE bt.active AND bt.post_type = 'P'
          AND NOT EXISTS (SELECT 1 FROM public.transactions p WHERE p.id = bt.id)
    ), tran AS (
        SELECT t.register_id,
               coalesce(dg.category, 'other') AS category,
               CASE dg.category
                    WHEN 'room' THEN 'Room'
                    WHEN 'fb'   THEN 'Food & Beverage'
                    ELSE coalesce(dg.category, 'Others')
               END AS name,
               sum(t.acc_amount) AS amount
        FROM tran_raw t
             LEFT JOIN public.department d        ON d.id  = t.dept_id
             LEFT JOIN public.department_group dg ON dg.id = d.group_id
        GROUP BY 1, 2, 3
    ), rev AS (
        SELECT t.register_id,
               sum(t.amount)::public.t_money AS total_revenue,
               jsonb_agg(jsonb_build_object(
                   'category', t.category,
                   'name',     t.name,
                   'amount',   t.amount
               ) ORDER BY t.category) AS revenues
        FROM tran t
        GROUP BY 1
    ), rate_raw AS (
        SELECT r.register_id, rr.room, rr.room_rate, rr.tran_id, rr.rateplan_id
        FROM reg r JOIN public.registration_rates rr ON rr.register_id = r.register_id
        UNION ALL
        SELECT r.register_id, brr.room, brr.room_rate, brr.tran_id, brr.rateplan_id
        FROM reg r JOIN backup.registration_rates brr ON brr.register_id = r.register_id
        WHERE NOT EXISTS (SELECT 1 FROM public.registration_rates p
                          WHERE p.register_id = brr.register_id
                            AND p.charge_date = brr.charge_date)
    ), rate_net AS (
        /* REVENUE_CALC_TYPE bases 1 and 2, per night, from the posted room-charge
           transaction. Its amount is the Gross Room figure and its BASE_PRICE itemizer
           the Net Room one; both have to be the frozen values because
           department.itemizers is NOT versioned, so recomputing an old stay with
           today's set invents numbers.
           Both transaction tables are probed by primary key, so this stays index-backed
           and does not reintroduce a seq scan of backup.transactions per row. */
        SELECT rr.register_id, rr.room, rr.room_rate,
               coalesce(pt.amount, bt.amount,
                        nullif(rr.room, 0), rr.room_rate) AS room_gross,
               coalesce(
                   (SELECT x.amount FROM jsonb_to_recordset(pt.itemizers) x(code text, amount numeric)
                     WHERE x.code = 'BASE_PRICE'),
                   (SELECT x.amount FROM jsonb_to_recordset(bt.itemizers) x(code text, amount numeric)
                     WHERE x.code = 'BASE_PRICE'),
                   public.fn_itemizer_base_amount(
                       coalesce(nullif(rr.room, 0), rr.room_rate),
                       coalesce(rp.room_charge_dept,
                                public.fn_sys_param('AUTO_POST', 'ROOM', NULL::int)))
               ) AS room_net
        FROM rate_raw rr
             LEFT JOIN public.rate_plan rp ON rp.id = rr.rateplan_id
             LEFT JOIN public.transactions pt
                    ON pt.id = rr.tran_id AND pt.active
             LEFT JOIN backup.transactions bt
                    ON bt.id = rr.tran_id AND bt.active
    ), rates AS (
        SELECT rr.register_id,
               sum(rr.room_gross)::public.t_money                              AS room_revenue,
               sum(rr.room_rate)::public.t_money                               AS rate_revenue,
               sum(rr.room_net)::public.t_money                                AS revenue_room_net
        FROM rate_net rr
        GROUP BY 1
    ), req_raw AS (
        SELECT r.register_id, r.adult, r.child, r.infant,
               gr.id AS request_id, gr.inclusion_id, gr.qty, gr.charge_rate,
               gr.multiplier, gr.frequency, gr.start_date, gr.end_date
        FROM reg r JOIN public.guest_request gr ON gr.register_id = r.register_id
        UNION ALL
        SELECT r.register_id, r.adult, r.child, r.infant,
               bgr.id, bgr.inclusion_id, bgr.qty, bgr.charge_rate,
               bgr.multiplier, bgr.frequency, bgr.start_date, bgr.end_date
        FROM reg r JOIN backup.guest_request bgr ON bgr.register_id = r.register_id
        WHERE NOT EXISTS (SELECT 1 FROM public.guest_request p WHERE p.id = bgr.id)
    ), reqs AS (
        SELECT q.register_id,
               jsonb_agg(jsonb_build_object(
                   'name',        inc.name,
                   'description', public.fn_get_inclusion_desc(
                                      inc.name, 1, q.adult, q.child, q.infant,
                                      q.qty, q.charge_rate, q.multiplier,
                                      q.frequency, q.start_date, q.end_date, true)
               ) ORDER BY q.request_id) AS requirements
        FROM req_raw q
             LEFT JOIN public.inclusion inc ON inc.id = q.inclusion_id
        GROUP BY 1
    )
    INSERT INTO public.guest_history (
        guest_id, register_id, booking_id, folio_id, arrival, departure,
        adult, child, infant, room_id, room_number, roomtype_id,
        rateplan_id, channel_id, origin_id, note,
        room_revenue, rate_revenue, revenue_room_net, total_revenue, revenues, requirements,
        is_archived, updated_at
    )
    SELECT r.guest_id, r.register_id, r.booking_id, r.folio_id, r.arrival, r.departure,
           r.adult, r.child, r.infant, r.room_id, rm.room_number, rm.roomtype_id,
           r.rateplan_id, r.channel_id, r.origin_id, r.note,
           coalesce(rt.room_revenue, 0), coalesce(rt.rate_revenue, 0), rt.revenue_room_net,
           coalesce(rv.total_revenue, 0),
           coalesce(rv.revenues, '[]'::jsonb),
           coalesce(rq.requirements, '[]'::jsonb),
           r.is_archived, current_timestamp
    FROM reg r
         LEFT JOIN public.room rm ON rm.id = r.room_id
         LEFT JOIN rates rt       ON rt.register_id = r.register_id
         LEFT JOIN rev   rv       ON rv.register_id = r.register_id
         LEFT JOIN reqs  rq       ON rq.register_id = r.register_id
    /* write-once: เติมแถวที่ขาดเท่านั้น; i_force เป็นทางซ่อมที่ต้องเจตนาเรียก */
    ON CONFLICT (register_id) DO UPDATE
    SET guest_id      = excluded.guest_id,
        booking_id    = excluded.booking_id,
        folio_id      = excluded.folio_id,
        arrival       = excluded.arrival,
        departure     = excluded.departure,
        adult         = excluded.adult,
        child         = excluded.child,
        infant        = excluded.infant,
        room_id       = excluded.room_id,
        room_number   = excluded.room_number,
        roomtype_id   = excluded.roomtype_id,
        rateplan_id   = excluded.rateplan_id,
        channel_id    = excluded.channel_id,
        origin_id     = excluded.origin_id,
        note          = excluded.note,
        room_revenue  = excluded.room_revenue,
        rate_revenue  = excluded.rate_revenue,
        revenue_room_net = excluded.revenue_room_net,
        total_revenue = excluded.total_revenue,
        revenues      = excluded.revenues,
        requirements  = excluded.requirements,
        is_archived   = excluded.is_archived,
        updated_at    = current_timestamp
    WHERE coalesce(i_force, false);

GET DIAGNOSTICS _synced = ROW_COUNT;
    _refresh = public.sp_refresh_guest_stat(i_guest_id);

RAISE NOTICE 'sp_rebuild_guest_history: % stay row(s) synced, % guest roll-up row(s) updated',
        _synced, _refresh;

RETURN QUERY
    SELECT 'after'::text, count(*)::bigint, count(DISTINCT gh.guest_id)::bigint,
           coalesce(sum(gh.nights), 0)::bigint, coalesce(sum(gh.total_revenue), 0)::numeric
    FROM public.guest_history gh
    WHERE i_guest_id IS NULL OR gh.guest_id = i_guest_id;
END

sp_rebuild_guest_history

Description

Parameters

Definition