aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_system_date date := fn_system_date();
    v_guest_keep_days integer := fn_prop_config('GUEST_KEEP_DAYS', '1095')::integer;
    v_orphan_keep_days integer := fn_prop_config('GUEST_ORPHAN_KEEP_DAYS', '30')::integer;
    v_document_keep_days integer := fn_prop_config('GUEST_DOCUMENT_POOL_KEEP_DAYS', '7')::integer;
    v_deleted integer := 0;
    v_rows integer;
BEGIN
    UPDATE guest_document gd
    SET pending_assignment = false
    WHERE gd.pending_assignment
      AND gd.last_scan_time < current_timestamp - make_interval(days => v_document_keep_days)
      AND (gd.guest_id IS NOT NULL OR EXISTS (SELECT 1 FROM guest g WHERE g.doc_id = gd.id));

DELETE FROM guest_document gd
    WHERE gd.pending_assignment
      AND gd.last_scan_time < current_timestamp - make_interval(days => v_document_keep_days)
      AND gd.guest_id IS NULL
      AND NOT EXISTS (SELECT 1 FROM guest g WHERE g.doc_id = gd.id);
    GET DIAGNOSTICS v_rows = ROW_COUNT;
    v_deleted := v_deleted + v_rows;

IF to_regclass('pg_temp.tmp_guest_retention_delete') IS NULL THEN
        CREATE TEMP TABLE tmp_guest_retention_delete (
            guest_id integer PRIMARY KEY
        ) ON COMMIT DROP;
    ELSE
        TRUNCATE tmp_guest_retention_delete;
    END IF;

INSERT INTO tmp_guest_retention_delete(guest_id)
    SELECT g.id
    FROM guest g
    WHERE g.last_departure < v_system_date - v_guest_keep_days
      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 registration r WHERE r.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 guest_signature gs WHERE gs.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM precheckin_consent_logs pc WHERE pc.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM precheckin_legal_acceptance pa WHERE pa.guest_id = g.id)
    ON CONFLICT DO NOTHING;

INSERT INTO tmp_guest_retention_delete(guest_id)
    SELECT g.id
    FROM guest g
    WHERE g.last_departure IS NULL
      AND g.created_at < current_timestamp - make_interval(days => v_orphan_keep_days)
      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 registration r WHERE r.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 room_summary rs WHERE rs.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM rsv_pickup rp WHERE rp.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM guest_history gh WHERE gh.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM guest_signature gs WHERE gs.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM precheckin_consent_logs pc WHERE pc.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM precheckin_legal_acceptance pa WHERE pa.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 bg WHERE bg.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM backup.registration br WHERE br.guest_id = g.id)
      AND NOT EXISTS (SELECT 1 FROM backup.register_guests rg WHERE rg.guest_id = g.id)
    ON CONFLICT DO NOTHING;

UPDATE room_summary rs
    SET guest_id = NULL
    WHERE rs.guest_id IN (SELECT guest_id FROM tmp_guest_retention_delete);

UPDATE rsv_pickup rp
    SET guest_id = NULL
    WHERE rp.guest_id IN (SELECT guest_id FROM tmp_guest_retention_delete);

DELETE FROM guest g
    USING tmp_guest_retention_delete d
    WHERE g.id = d.guest_id;
    GET DIAGNOSTICS v_rows = ROW_COUNT;
    v_deleted := v_deleted + v_rows;

DELETE FROM guest_document gd
    WHERE gd.guest_id IS NULL
      AND NOT EXISTS (SELECT 1 FROM guest g WHERE g.doc_id = gd.id)
      AND NOT gd.pending_assignment;
    GET DIAGNOSTICS v_rows = ROW_COUNT;
    v_deleted := v_deleted + v_rows;

RETURN v_deleted;
END

sp_endday_clear_guest_data

Description

Parameters

Definition