aonestar.public

Parameters

Name	Type	Mode
user_name	text	IN
status	text	INOUT
msg	text	INOUT

Definition

DECLARE
    v_system_date date       = fn_system_date();
    v_backup_days interval   = (fn_prop_config('ARCHIVE_MOVE_DAYS', '90') || ' days')::interval;
    v_clearing_days interval = (fn_prop_config('ARCHIVE_PURGE_DAYS', '280') || ' days')::interval;
    rec_count int = 0;
    deleted_rows int = 0;
    archived_rows int = 0;
    archive_batch_rows int = 0;
    table_name TEXT;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    blocked_count int = 0;
BEGIN
    IF to_regclass('pg_temp.tmp_endday_archive_reg_ids') IS NULL THEN
        CREATE TEMP TABLE tmp_endday_archive_reg_ids (
            register_id int PRIMARY KEY,
            date_ref date NOT NULL
        ) ON COMMIT DROP;
    END IF;

IF to_regclass('pg_temp.tmp_endday_archive_booking_ids') IS NULL THEN
        CREATE TEMP TABLE tmp_endday_archive_booking_ids (
            booking_id int PRIMARY KEY,
            date_ref date NOT NULL
        ) ON COMMIT DROP;
    END IF;

IF to_regclass('pg_temp.tmp_endday_archive_folio_ids') IS NULL THEN
        CREATE TEMP TABLE tmp_endday_archive_folio_ids (
            folio_id int PRIMARY KEY,
            date_ref date NOT NULL,
            owner_type text NOT NULL,
            owner_id int NOT NULL
        ) ON COMMIT DROP;
    END IF;

TRUNCATE tmp_endday_archive_reg_ids;
    TRUNCATE tmp_endday_archive_booking_ids;
    TRUNCATE tmp_endday_archive_folio_ids;

INSERT INTO tmp_endday_archive_reg_ids(register_id, date_ref)
    SELECT rg.id, GREATEST(
            rg.departure,
            COALESCE(b.departure, rg.departure),
            COALESCE(MAX(f.close_date), rg.departure)
        ) AS date_ref
    FROM registration rg
        LEFT JOIN booking b ON b.id = rg.booking_id
        LEFT JOIN folio f ON f.register_id = rg.id
    WHERE rg.status IN ('O','X')
    GROUP BY rg.id, rg.departure, b.departure
    HAVING GREATEST(
            rg.departure,
            COALESCE(b.departure, rg.departure),
            COALESCE(MAX(f.close_date), rg.departure)
        ) + v_backup_days <= v_system_date;

/* date_ref must not fall behind the registrations this booking owns, because the
       retention sweep at the end deletes each backup table independently by its own
       date_ref. A folio on an R row carries register_id and leaves booking_id NULL, so
       joining folio on f.booking_id sees only the B and G folios and misses every late
       check-out on an individual booking -- backup.booking* would then be purged while
       backup.registration* of the same stay was still there.
       tmp_endday_archive_reg_ids.date_ref already folds in MAX(folio.close_date) for
       f.register_id, so taking its MAX covers the R folios without joining them again.
       The NOT EXISTS guard below only lets a booking through once every one of its
       registrations is in the batch, so this MAX is never NULL for a row that qualifies. */
    INSERT INTO tmp_endday_archive_booking_ids(booking_id, date_ref)
    SELECT b.id, GREATEST(b.departure,
                          COALESCE(MAX(f.close_date), b.departure),
                          COALESCE(MAX(r.date_ref), b.departure)) AS date_ref
    FROM booking b
        LEFT JOIN folio f ON f.booking_id = b.id
        LEFT JOIN registration rg ON rg.booking_id = b.id
        LEFT JOIN tmp_endday_archive_reg_ids r ON r.register_id = rg.id
    WHERE ((b.departure + v_backup_days <= v_system_date) OR (b.status = 'W' AND b.arrival = v_system_date))
      AND NOT EXISTS(
          SELECT 1
          FROM registration rg
          WHERE rg.booking_id = b.id
            AND NOT EXISTS (
                SELECT 1
                FROM tmp_endday_archive_reg_ids tr
                WHERE tr.register_id = rg.id
            )
      )
    GROUP BY b.id, b.departure, b.status, b.arrival;

INSERT INTO tmp_endday_archive_folio_ids(folio_id, date_ref, owner_type, owner_id)
    SELECT f.id, f.close_date, 'C', f.id
    FROM folio f
    WHERE f.status = 'C'
      AND f.folio_type = 'C'
      AND f.close_date + v_backup_days <= v_system_date
    ON CONFLICT DO NOTHING;

INSERT INTO tmp_endday_archive_folio_ids(folio_id, date_ref, owner_type, owner_id)
    SELECT f.id, GREATEST(r.date_ref, COALESCE(f.close_date, r.date_ref)), 'R', r.register_id
    FROM tmp_endday_archive_reg_ids r
    JOIN folio f ON f.register_id = r.register_id
    ON CONFLICT DO NOTHING;

INSERT INTO tmp_endday_archive_folio_ids(folio_id, date_ref, owner_type, owner_id)
    SELECT f.id, GREATEST(b.date_ref, COALESCE(f.close_date, b.date_ref)), 'B', b.booking_id
    FROM tmp_endday_archive_booking_ids b
    JOIN folio f ON f.booking_id = b.booking_id
    ON CONFLICT DO NOTHING;

WITH candidate_tran AS (
        SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
        FROM tmp_endday_archive_folio_ids tf
        JOIN transactions t ON t.folio_id = tf.folio_id
    ),
    blocked_owner AS (
        SELECT DISTINCT ct.owner_type, ct.owner_id
        FROM candidate_tran ct
        JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
        LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
        WHERE ref_ct.id IS NULL
        UNION
        SELECT DISTINCT ct.owner_type, ct.owner_id
        FROM candidate_tran ct
        JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
        LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
        WHERE ref_ct.id IS NULL
    )
    SELECT COUNT(*) INTO blocked_count FROM blocked_owner;

IF blocked_count > 0 THEN
        PERFORM sp_log_error(
            'sp_endday_clear_data',
            format('Skipped %s archive owner set(s) because transfer transactions reference active rows outside the archive batch', blocked_count),
            null, null, null, null,
            (
                WITH candidate_tran AS (
                    SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
                    FROM tmp_endday_archive_folio_ids tf
                    JOIN transactions t ON t.folio_id = tf.folio_id
                ),
                blocked_owner AS (
                    SELECT DISTINCT ct.owner_type, ct.owner_id
                    FROM candidate_tran ct
                    JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
                    LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                    WHERE ref_ct.id IS NULL
                    UNION
                    SELECT DISTINCT ct.owner_type, ct.owner_id
                    FROM candidate_tran ct
                    JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
                    LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                    WHERE ref_ct.id IS NULL
                )
                SELECT jsonb_agg(jsonb_build_object('owner_type', owner_type, 'owner_id', owner_id))
                FROM blocked_owner
            ),
            user_name,
            notify_admin := false
        );

DELETE FROM tmp_endday_archive_reg_ids r
        USING tmp_endday_archive_folio_ids f
        WHERE f.owner_type = 'R'
          AND f.owner_id = r.register_id
          AND EXISTS (
              WITH candidate_tran AS (
                  SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
                  FROM tmp_endday_archive_folio_ids tf
                  JOIN transactions t ON t.folio_id = tf.folio_id
              ),
              blocked_owner AS (
                  SELECT DISTINCT ct.owner_type, ct.owner_id
                  FROM candidate_tran ct
                  JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
                  LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                  WHERE ref_ct.id IS NULL
                  UNION
                  SELECT DISTINCT ct.owner_type, ct.owner_id
                  FROM candidate_tran ct
                  JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
                  LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                  WHERE ref_ct.id IS NULL
              )
              SELECT 1 FROM blocked_owner bo
              WHERE bo.owner_type = 'R'
                AND bo.owner_id = r.register_id
          );

DELETE FROM tmp_endday_archive_booking_ids b
        USING tmp_endday_archive_folio_ids f
        WHERE f.owner_type = 'B'
          AND f.owner_id = b.booking_id
          AND EXISTS (
              WITH candidate_tran AS (
                  SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
                  FROM tmp_endday_archive_folio_ids tf
                  JOIN transactions t ON t.folio_id = tf.folio_id
              ),
              blocked_owner AS (
                  SELECT DISTINCT ct.owner_type, ct.owner_id
                  FROM candidate_tran ct
                  JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
                  LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                  WHERE ref_ct.id IS NULL
                  UNION
                  SELECT DISTINCT ct.owner_type, ct.owner_id
                  FROM candidate_tran ct
                  JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
                  LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                  WHERE ref_ct.id IS NULL
              )
              SELECT 1 FROM blocked_owner bo
              WHERE bo.owner_type = 'B'
                AND bo.owner_id = b.booking_id
          );

DELETE FROM tmp_endday_archive_folio_ids f
        WHERE EXISTS (
            WITH candidate_tran AS (
                SELECT tf.owner_type, tf.owner_id, t.id, t.transfer_src, t.transfer_des
                FROM tmp_endday_archive_folio_ids tf
                JOIN transactions t ON t.folio_id = tf.folio_id
            ),
            blocked_owner AS (
                SELECT DISTINCT ct.owner_type, ct.owner_id
                FROM candidate_tran ct
                JOIN transactions ref ON ref.id IN (ct.transfer_src, ct.transfer_des)
                LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                WHERE ref_ct.id IS NULL
                UNION
                SELECT DISTINCT ct.owner_type, ct.owner_id
                FROM candidate_tran ct
                JOIN transactions ref ON ref.transfer_src = ct.id OR ref.transfer_des = ct.id
                LEFT JOIN candidate_tran ref_ct ON ref_ct.id = ref.id
                WHERE ref_ct.id IS NULL
            )
            SELECT 1 FROM blocked_owner bo
            WHERE bo.owner_type = f.owner_type
              AND bo.owner_id = f.owner_id
        );
    END IF;

-- Re-evaluate owner sets until both relationship rules are stable.  A
    -- transfer-blocked registration can invalidate its booking and can also
    -- turn another candidate transaction into an external reference.
    IF to_regclass('pg_temp.tmp_endday_archive_blocked_owner') IS NULL THEN
        CREATE TEMP TABLE tmp_endday_archive_blocked_owner (
            owner_type text NOT NULL,
            owner_id int NOT NULL,
            PRIMARY KEY (owner_type, owner_id)
        ) ON COMMIT DROP;
    END IF;

IF to_regclass('pg_temp.tmp_endday_archive_candidate_tran') IS NULL THEN
        CREATE TEMP TABLE tmp_endday_archive_candidate_tran (
            id int PRIMARY KEY,
            owner_type text NOT NULL,
            owner_id int NOT NULL,
            transfer_src int,
            transfer_des int,
            splitted_src int
        ) ON COMMIT DROP;
    END IF;

LOOP
        archive_batch_rows := 0;
        TRUNCATE tmp_endday_archive_blocked_owner;
        TRUNCATE tmp_endday_archive_candidate_tran;

INSERT INTO tmp_endday_archive_candidate_tran(
            id, owner_type, owner_id, transfer_src, transfer_des, splitted_src
        )
        SELECT t.id, tf.owner_type, tf.owner_id,
               t.transfer_src, t.transfer_des, t.splitted_src
        FROM tmp_endday_archive_folio_ids tf
        JOIN transactions t ON t.folio_id = tf.folio_id;

INSERT INTO tmp_endday_archive_blocked_owner(owner_type, owner_id)
        SELECT DISTINCT ct.owner_type, ct.owner_id
        FROM tmp_endday_archive_candidate_tran ct
        CROSS JOIN LATERAL (
            VALUES (ct.transfer_src), (ct.transfer_des), (ct.splitted_src)
        ) link(ref_id)
        LEFT JOIN tmp_endday_archive_candidate_tran ref_ct
          ON ref_ct.id = link.ref_id
        WHERE link.ref_id IS NOT NULL AND ref_ct.id IS NULL
        UNION
        SELECT DISTINCT ct.owner_type, ct.owner_id
        FROM tmp_endday_archive_candidate_tran ct
        JOIN transactions ref ON ref.transfer_src = ct.id
        LEFT JOIN tmp_endday_archive_candidate_tran ref_ct ON ref_ct.id = ref.id
        WHERE ref_ct.id IS NULL
        UNION
        SELECT DISTINCT ct.owner_type, ct.owner_id
        FROM tmp_endday_archive_candidate_tran ct
        JOIN transactions ref ON ref.transfer_des = ct.id
        LEFT JOIN tmp_endday_archive_candidate_tran ref_ct ON ref_ct.id = ref.id
        WHERE ref_ct.id IS NULL
        UNION
        SELECT DISTINCT ct.owner_type, ct.owner_id
        FROM tmp_endday_archive_candidate_tran ct
        JOIN transactions ref ON ref.splitted_src = ct.id
        LEFT JOIN tmp_endday_archive_candidate_tran ref_ct ON ref_ct.id = ref.id
        WHERE ref_ct.id IS NULL;

DELETE FROM tmp_endday_archive_reg_ids r
        USING tmp_endday_archive_blocked_owner bo
        WHERE bo.owner_type = 'R' AND bo.owner_id = r.register_id;
        GET DIAGNOSTICS rec_count = ROW_COUNT;
        archive_batch_rows := archive_batch_rows + rec_count;

DELETE FROM tmp_endday_archive_booking_ids b
        USING tmp_endday_archive_blocked_owner bo
        WHERE bo.owner_type = 'B' AND bo.owner_id = b.booking_id;
        GET DIAGNOSTICS rec_count = ROW_COUNT;
        archive_batch_rows := archive_batch_rows + rec_count;

DELETE FROM tmp_endday_archive_folio_ids f
        USING tmp_endday_archive_blocked_owner bo
        WHERE bo.owner_type = f.owner_type AND bo.owner_id = f.owner_id;
        GET DIAGNOSTICS rec_count = ROW_COUNT;
        archive_batch_rows := archive_batch_rows + rec_count;

DELETE FROM tmp_endday_archive_booking_ids b
        WHERE EXISTS (
            SELECT 1
            FROM registration rg
            WHERE rg.booking_id = b.booking_id
              AND NOT EXISTS (
                  SELECT 1
                  FROM tmp_endday_archive_reg_ids tr
                  WHERE tr.register_id = rg.id
              )
        );
        GET DIAGNOSTICS rec_count = ROW_COUNT;
        archive_batch_rows := archive_batch_rows + rec_count;

DELETE FROM tmp_endday_archive_folio_ids f
        WHERE (f.owner_type = 'R' AND NOT EXISTS (
                  SELECT 1 FROM tmp_endday_archive_reg_ids r
                  WHERE r.register_id = f.owner_id
              ))
           OR (f.owner_type = 'B' AND NOT EXISTS (
                  SELECT 1 FROM tmp_endday_archive_booking_ids b
                  WHERE b.booking_id = f.owner_id
              ));
        GET DIAGNOSTICS rec_count = ROW_COUNT;
        archive_batch_rows := archive_batch_rows + rec_count;

EXIT WHEN archive_batch_rows = 0;
    END LOOP;

IF EXISTS (SELECT 1 FROM tmp_endday_archive_folio_ids)
       OR EXISTS (SELECT 1 FROM tmp_endday_archive_reg_ids)
       OR EXISTS (SELECT 1 FROM tmp_endday_archive_booking_ids) THEN
        archive_batch_rows := 0;
        BEGIN
            INSERT INTO backup.folio
            SELECT tf.date_ref, f.*
            FROM folio f
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = f.id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

-- backup.folio_items has no GENERATED ALWAYS columns; SELECT * keeps in sync automatically
            INSERT INTO backup.folio_items
            SELECT tf.date_ref, fi.*
            FROM public.folio_items fi
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = fi.folio_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.folio_details
            SELECT tf.date_ref, fd.*
            FROM folio_details fd
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = fd.folio_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

-- backup.transactions has no GENERATED ALWAYS columns among the listed ones;
            -- explicit list required (advance_post is GENERATED and excluded). bill_id
            -- added here so the mini POS link survives archival (see column comment).
            INSERT INTO backup.transactions(date_ref, id, folio_item, post_type, post_date, post_time, tran_type, dept_id,
                description, qty, amount, reference, remark, post_user, post_shift, post_source, card_id, voided, splitted,
                splitted_src, transfer_in, transfer_src, transfer_out, transfer_des, original_folio_id, post_flag, tax_code,
                itemizers, charge_date, active, total_amount, acc_qty, acc_amount, folio_id, details, vat_issued, vat_id, legacy_gtran_id, bill_id)
            SELECT tf.date_ref, t.id, t.folio_item, t.post_type, t.post_date, t.post_time, t.tran_type, t.dept_id,
                t.description, t.qty, t.amount, t.reference, t.remark, t.post_user, t.post_shift, t.post_source, t.card_id, t.voided, t.splitted,
                t.splitted_src, t.transfer_in, t.transfer_src, t.transfer_out, t.transfer_des, t.original_folio_id, t.post_flag, t.tax_code,
                t.itemizers, t.charge_date, t.active, t.total_amount, t.acc_qty, t.acc_amount, t.folio_id, t.details, t.vat_issued, t.vat_id, t.legacy_gtran_id, t.bill_id
            FROM transactions t
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = t.folio_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.cash_sale
            SELECT tf.date_ref, cs.*
            FROM cash_sale cs
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = cs.folio_id
            WHERE tf.owner_type = 'C'
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.cashier_log
            SELECT tf.date_ref, lg.*
            FROM cashier_log lg
            JOIN tmp_endday_archive_folio_ids tf ON tf.folio_id = lg.folio_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.registration (
                date_ref, id, status, booking_id, booking_item, room_id, guest_id, group_id, arrival, departure, departure_time, walk_in, adult, child, infant,
                extra_adult, extra_child, extra_infant, approved, approve_user, approve_time, checkin_user, checkin_time, checkout_user, checkout_time,
                member_code, channel_id, origin_id, arrive_by, arrive_from, depart_by, depart_to, folio_pattern_id, voucher_no, promo_code, payment_method,
                sales_id, market_id, stay_type, folio_id, credit_limit, note, accno, msg_count, flag_count, rateplan_id, room_posted, charge_to_booking,
                promotion_id, rate_id, modify_user, modify_time, house_used, shared_register_id, share_room, day_used, nights, join_register_id, is_join_room,
                is_group, early_checkout, rsvtype_id, pax, early_checkout_date, mealtype_id, room_charge, other_charge, extra_charge, total_deposit,
                share_rate, split_folio, legacy_accno, legacy_namelist_seq)
            SELECT tr.date_ref, rg.id, rg.status, rg.booking_id, rg.booking_item, rg.room_id, rg.guest_id, rg.group_id, rg.arrival, rg.departure,
                rg.departure_time, rg.walk_in, rg.adult, rg.child, rg.infant, rg.extra_adult, rg.extra_child, rg.extra_infant, rg.approved,
                rg.approve_user, rg.approve_time, rg.checkin_user, rg.checkin_time, rg.checkout_user, rg.checkout_time, rg.member_code, rg.channel_id,
                rg.origin_id, rg.arrive_by, rg.arrive_from, rg.depart_by, rg.depart_to, rg.folio_pattern_id, rg.voucher_no, rg.promo_code,
                rg.payment_method, rg.sales_id, rg.market_id, rg.stay_type, rg.folio_id, rg.credit_limit, rg.note, rg.accno, rg.msg_count, rg.flag_count,
                rg.rateplan_id, rg.room_posted, rg.charge_to_booking, rg.promotion_id, rg.rate_id, rg.modify_user, rg.modify_time, rg.house_used,
                rg.shared_register_id, rg.share_room, rg.day_used, rg.nights, rg.join_register_id, rg.is_join_room, rg.is_group, rg.early_checkout,
                rg.rsvtype_id, rg.pax, rg.early_checkout_date, rg.mealtype_id, rg.room_charge, rg.other_charge, rg.extra_charge, rg.total_deposit,
                rg.share_rate, rg.split_folio, rg.legacy_accno, rg.legacy_namelist_seq
            FROM registration rg
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = rg.id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.register_guests
            SELECT tr.date_ref, rg.*
            FROM register_guests rg
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = rg.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.registration_rates
            SELECT tr.date_ref, rr.*
            FROM registration_rates rr
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = rr.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.guest_request
            SELECT tr.date_ref, gr.*
            FROM guest_request gr
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = gr.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.deposit
            SELECT tr.date_ref, d.*
            FROM deposit d
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = d.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.registration_log
            SELECT tr.date_ref, lg.*
            FROM registration_log lg
            JOIN tmp_endday_archive_reg_ids tr ON tr.register_id = lg.register_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking (
                 date_ref, id, status, rsvtype_id, book_type, book_by, guest_id, group_id, party_id, member_code, channel_id, origin_id, arrival,
                 arrival_time, arrive_by, arrive_from, departure, departure_time, depart_by, depart_to, folio_pattern_id, voucher_no, promo_code,
                 payment_method, flags, sales_id, market_id, create_user, create_time, modify_user, modify_time, cancel_user, cancel_time, msg_count,
                 flag_count, stay_type, accno, confirm_no, folio_id, credit_limit, confirm_remark, confirm_user, confirm_time, note, book_date,
                 cancel_reason, booking_name, room_charge, other_charge, extra_charge, total_deposit,
                 cancel_date, channel_booking_id, rebook_date, noshow_date, namelist_created)
            SELECT tb.date_ref, b.id, b.status, b.rsvtype_id, b.book_type, b.book_by, b.guest_id, b.group_id, b.party_id, b.member_code,
                 b.channel_id, b.origin_id, b.arrival, b.arrival_time, b.arrive_by, b.arrive_from, b.departure, b.departure_time,
                 b.depart_by, b.depart_to, b.folio_pattern_id, b.voucher_no, b.promo_code, b.payment_method, b.flags, b.sales_id,
                 b.market_id, b.create_user, b.create_time, b.modify_user, b.modify_time, b.cancel_user, b.cancel_time, b.msg_count,
                 b.flag_count, b.stay_type, b.accno, b.confirm_no, b.folio_id, b.credit_limit, b.confirm_remark, b.confirm_user,
                 b.confirm_time, b.note, b.book_date, b.cancel_reason, b.booking_name, b.room_charge, b.other_charge, b.extra_charge, b.total_deposit,
                 b.cancel_date, b.channel_booking_id, b.rebook_date, b.noshow_date, b.namelist_created
            FROM booking b
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = b.id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

-- backup.booking_items has GENERATED ALWAYS columns; explicit list required.
            -- When adding a non-generated column: update backup.booking_items AND this list together.
            INSERT INTO backup.booking_items (
                date_ref, item_id, booking_id, booking_seq, rateplan_id, roomtype_id, bedtype_id, arrival, departure, qty, adult, child, infant,
                allot_qty, block_qty, inhouse_qty, charge_to, extra_adult, extra_child, extra_infant, no_amend, no_cancel, no_refund,
                room_numbers, rate_id, rooms, promotion_id, noshow_qty, pax, mealtype_id, room_charge, other_charge, extra_charge,
                namelist_group, namelist_orig_seq, namelist_add,
                split_folio, share_rate
            )
            SELECT tb.date_ref, bi.item_id, bi.booking_id, bi.booking_seq, bi.rateplan_id, bi.roomtype_id, bi.bedtype_id, bi.arrival, bi.departure,
                bi.qty, bi.adult, bi.child, bi.infant, bi.allot_qty, bi.block_qty, bi.inhouse_qty, bi.charge_to, bi.extra_adult, bi.extra_child,
                bi.extra_infant, bi.no_amend, bi.no_cancel, bi.no_refund, bi.room_numbers, bi.rate_id, bi.rooms, bi.promotion_id, bi.noshow_qty,
                bi.pax, bi.mealtype_id, bi.room_charge, bi.other_charge, bi.extra_charge, bi.namelist_group, bi.namelist_orig_seq, bi.namelist_add,
                bi.split_folio, bi.share_rate
            FROM booking_items bi
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = bi.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking_guests (
                date_ref, booking_id, guest_id, registered, register_id, booking_item, room_seq
            )
            SELECT tb.date_ref, bg.booking_id, bg.guest_id, bg.registered, bg.register_id, bg.booking_item, bg.room_seq
            FROM booking_guests bg
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = bg.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking_rates
            SELECT tb.date_ref, br.*
            FROM booking_rates br
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = br.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.guest_request
            SELECT tb.date_ref, gr.*
            FROM guest_request gr
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = gr.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.deposit
            SELECT tb.date_ref, d.*
            FROM deposit d
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = d.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

INSERT INTO backup.booking_log
            SELECT tb.date_ref, bl.*
            FROM booking_log bl
            JOIN tmp_endday_archive_booking_ids tb ON tb.booking_id = bl.booking_id
            ON CONFLICT DO NOTHING;
            GET DIAGNOSTICS rec_count = ROW_COUNT; archive_batch_rows = archive_batch_rows + rec_count;

UPDATE transactions t
            SET transfer_src = NULL
            WHERE t.folio_id IN (SELECT folio_id FROM tmp_endday_archive_folio_ids)
              AND t.transfer_src IN (
                  SELECT t2.id
                  FROM transactions t2
                  WHERE t2.folio_id IN (SELECT folio_id FROM tmp_endday_archive_folio_ids)
              );

UPDATE transactions t
            SET transfer_des = NULL
            WHERE t.folio_id IN (SELECT folio_id FROM tmp_endday_archive_folio_ids)
              AND t.transfer_des IN (
                  SELECT t2.id
                  FROM transactions t2
                  WHERE t2.folio_id IN (SELECT folio_id FROM tmp_endday_archive_folio_ids)
              );

DELETE FROM folio WHERE id IN (
                SELECT folio_id FROM tmp_endday_archive_folio_ids WHERE owner_type = 'B'
            );
            DELETE FROM registration WHERE id IN (SELECT register_id FROM tmp_endday_archive_reg_ids);
            DELETE FROM booking WHERE id IN (SELECT booking_id FROM tmp_endday_archive_booking_ids);
            DELETE FROM folio WHERE id IN (
                SELECT folio_id FROM tmp_endday_archive_folio_ids WHERE owner_type = 'C'
            );

archived_rows := archived_rows + archive_batch_rows;
        EXCEPTION WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS
                _sqlstate= RETURNED_SQLSTATE,
                _msg_text= MESSAGE_TEXT,
                _detail  = PG_EXCEPTION_DETAIL,
                _hint    = PG_EXCEPTION_HINT,
                _context = PG_EXCEPTION_CONTEXT;
            PERFORM sp_log_error('sp_endday_clear_data', _msg_text, _detail, _sqlstate, _hint, _context, null::jsonb, user_name, notify_admin := false);
        END;
    END IF;

FOR table_name IN (
        SELECT t.table_name::text
        FROM information_schema.TABLES t
        WHERE t.table_schema='backup')
    LOOP
        EXECUTE format('DELETE FROM backup.%I WHERE date_ref <= %L', table_name, v_system_date - v_clearing_days);
        GET DIAGNOSTICS rec_count = ROW_COUNT; deleted_rows = deleted_rows + rec_count;
    END LOOP;

-- Clear processed IPU queue rows older than IPU_QUEUE_KEEP_DAYS (default 7).
    -- queue_delivery cascades via FK; rows with unfinished deliveries are kept
    -- regardless of age.
    DELETE FROM ipu.queue q
    WHERE q.event_time < (current_timestamp
            - (fn_prop_config('IPU_QUEUE_KEEP_DAYS', '7') || ' days')::interval)
      AND NOT EXISTS (
          SELECT 1 FROM ipu.queue_delivery d
          WHERE d.queue_id = q.id
            AND d.status NOT IN ('done', 'cancelled', 'dead')
      );
    GET DIAGNOSTICS rec_count = ROW_COUNT;
    deleted_rows = deleted_rows + rec_count;

-- Mini POS retention: purge บิลที่ post_date เก่ากว่า POS_BILL_KEEP_DAYS (default 90 ~ 3 เดือน).
    -- bill_item cascade ตาม (FK ON DELETE CASCADE); transactions.bill_id -> SET NULL (main)
    -- แต่ลิงก์ยังอยู่ใน backup.transactions.bill_id (โฟลิโอที่ archive แล้ว).
    DELETE FROM pos.bill
    WHERE post_date < v_system_date
        - (fn_prop_config('POS_BILL_KEEP_DAYS', '90') || ' days')::interval;
    GET DIAGNOSTICS rec_count = ROW_COUNT;
    deleted_rows = deleted_rows + rec_count;

-- Delete dummy guests that are no longer referenced anywhere.
    -- Checks both main tables (active/future bookings) and backup tables
    -- (past stays) so that guests used in archived history are preserved.
    -- booking.guest_id / register_guests / registration / booking_guests are
    -- all NO ACTION FKs; credit_card / guest_document / guest_social cascade.
    DELETE FROM guest g
    WHERE g.is_dummy = true
      AND NOT EXISTS (SELECT 1 FROM booking            b   WHERE  b.guest_id  = g.id)
      AND NOT EXISTS (SELECT 1 FROM booking_guests     bg  WHERE bg.guest_id  = g.id)
      AND NOT EXISTS (SELECT 1 FROM register_guests    rg  WHERE rg.guest_id  = g.id)
      AND NOT EXISTS (SELECT 1 FROM registration        r   WHERE  r.guest_id  = g.id)
      AND NOT EXISTS (SELECT 1 FROM backup.booking      bb  WHERE  bb.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM backup.booking_guests   bbg WHERE bbg.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM backup.register_guests  brg WHERE brg.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM backup.registration      br  WHERE  br.guest_id = g.id);
    GET DIAGNOSTICS rec_count = ROW_COUNT;
    deleted_rows = deleted_rows + rec_count;

rec_count := sp_endday_clear_guest_data();
    deleted_rows := deleted_rows + rec_count;

status = 'success';
    msg = format('%s records archived, %s records deleted', archived_rows, deleted_rows);
END

sp_endday_clear_data

Description

Parameters

Definition