aonestar.public

Parameters

Name	Type	Mode
p_rebuild	boolean	IN (DEFAULT false)
phase	text	OUT
rp_status	text	OUT
bookings	bigint	OUT
room_nights	bigint	OUT
revenue	numeric	OUT

Definition

DECLARE
    v_inserted bigint;
    v_deleted  bigint;
BEGIN
    RETURN QUERY
    SELECT 'before'::text, rp.status::text,
           COUNT(*)::bigint,
           SUM(rp.room_qty * GREATEST(rp.departure - rp.arrival, 1))::bigint,
           SUM(rp.room_charge)::numeric
    FROM rsv_pickup rp
    GROUP BY rp.status
    ORDER BY rp.status;

RETURN QUERY
    SELECT 'before'::text, 'ALL'::text,
           COUNT(*)::bigint,
           SUM(rp.room_qty * GREATEST(rp.departure - rp.arrival, 1))::bigint,
           SUM(rp.room_charge)::numeric
    FROM rsv_pickup rp;

IF p_rebuild THEN
        -- Preserve AMD rows — historical ledger that cannot be recalculated
        DELETE FROM rsv_pickup
        WHERE status <> 'AMD'
          AND booking_id IN (
            SELECT id FROM booking
            UNION
            SELECT id FROM backup.booking
        );
        GET DIAGNOSTICS v_deleted = ROW_COUNT;
        RAISE NOTICE 'sp_regenerate_rsv_pickup: deleted % rows (non-AMD, known bookings only)', v_deleted;
    END IF;

INSERT INTO rsv_pickup (
        date_ref, status, booking_id, booking_item,
        roomtype_id, bedtype_id, book_type, house_used, comp, day_used,
        rateplan_id, mealtype_id, stay_type, arrival, departure,
        adult, child, infant, extra_adult, extra_child, extra_infant,
        room_rate, extra_adult_rate, extra_child_rate, extra_infant_rate,
        room_charge, extra_charge, other_charge, total_charge,
        promotion_id, channel_id, origin_id, sales_id, market_id, rsvtype_id,
        lead_time, return_guest,
        guest_id, guest_sex, guest_age,
        guest_nation_id, guest_lang_id, guest_country_id, guest_country_res_id,
        guest_city_id, guest_occupation_id, guest_vip_id,
        guest_name, guest_member_code, room_qty
    )
    SELECT
        date_ref, status, booking_id, booking_item,
        roomtype_id, bedtype_id, book_type, house_used, comp, day_used,
        rateplan_id, mealtype_id, stay_type, arrival, departure,
        adult, child, infant, extra_adult, extra_child, extra_infant,
        room_rate, extra_adult_rate, extra_child_rate, extra_infant_rate,
        room_charge, extra_charge, other_charge, total_charge,
        promotion_id, channel_id, origin_id, sales_id, market_id, rsvtype_id,
        lead_time, return_guest,
        guest_id, guest_sex, guest_age,
        guest_nation_id, guest_lang_id, guest_country_id, guest_country_res_id,
        guest_city_id, guest_occupation_id, guest_vip_id,
        guest_name, guest_member_code, room_qty
    FROM sp_calc_rsv_pickup()
    ON CONFLICT (date_ref, booking_item, status) WHERE status <> 'AMD' DO NOTHING;

GET DIAGNOSTICS v_inserted = ROW_COUNT;
    RAISE NOTICE 'sp_regenerate_rsv_pickup: % rows inserted (p_rebuild=%)', v_inserted, p_rebuild;

RETURN QUERY
    SELECT 'after'::text, rp.status::text,
           COUNT(*)::bigint,
           SUM(rp.room_qty * GREATEST(rp.departure - rp.arrival, 1))::bigint,
           SUM(rp.room_charge)::numeric
    FROM rsv_pickup rp GROUP BY rp.status ORDER BY rp.status;

RETURN QUERY
    SELECT 'after'::text, 'ALL'::text, COUNT(*)::bigint,
           SUM(rp.room_qty * GREATEST(rp.departure - rp.arrival, 1))::bigint,
           SUM(rp.room_charge)::numeric
    FROM rsv_pickup rp;
END