age-circs.sql: a script to age circs/anonymize patron circ history

[sitka/sitka-tools.git] / maintenance / age-circs.sql

/*
 * age-circs.sql - a script to age/anonymize patrons' circ history.
 *
 * Copyright (C) 2008 Equinox Software, Inc.
 * Copyright (C) 2014 BC Libraries Cooperative
 * Author: Jeff Davis <jdavis@sitka.bclibraries.ca>
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 */

BEGIN;

\echo 'exception matrix table'
CREATE TABLE sitka.age_circ_exception_matrix (
  id SERIAL NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  org_unit INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  grp INT REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
);

\echo 'function for finding exceptions'
CREATE OR REPLACE FUNCTION sitka.has_age_circ_exception(circ_id BIGINT) RETURNS BOOL AS $$
DECLARE
  potential_aged_circ  action.circulation%ROWTYPE;
  matching_exceptions  INT;
BEGIN
  matching_exceptions := 0;
  -- TODO: should we match exceptions against the entire circ chain?
  SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
  SELECT count(*) INTO matching_exceptions
    FROM sitka.age_circ_exception_matrix m
      LEFT JOIN permission.grp_ancestors_distance( potential_aged_circ.usr_profile ) upgad ON m.grp = upgad.id
      LEFT JOIN actor.org_unit_ancestors_distance( potential_aged_circ.circ_lib ) ctoua ON m.org_unit = ctoua.id
    WHERE m.active
      AND (m.grp IS NULL      OR upgad.id IS NOT NULL)
      AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL);
  IF matching_exceptions > 0 THEN
    RETURN TRUE;
  END IF;
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

\echo 'table for aged circs'
CREATE TABLE sitka.aged_circs (
  id BIGINT NOT NULL,
  circ_chain_tail_xact_finish TIMESTAMPTZ,
  delete_date DATE NOT NULL
);

\echo 'table for aged circ count'
CREATE TABLE sitka.aged_circs_by_user (
  usr BIGINT NOT NULL,
  month INT NOT NULL,
  year INT NOT NULL,
  count INT NOT NULL DEFAULT 0,
  UNIQUE (usr, month, year)
);

CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
DECLARE
  today                DATE;
  potential_aged_circ  action.circulation%ROWTYPE;
  circ_chain_tail      action.circulation%ROWTYPE;
  usr_keep_start       actor.usr_setting%ROWTYPE;
  usr_keep_age         actor.usr_setting%ROWTYPE;
  circ_cutoff          DATE;
  has_exception        BOOL;
  circs_aged           INT;
BEGIN
  today := now()::DATE;

  -- TODO: more strictness here?
  CREATE TEMPORARY TABLE circs_to_age AS
    SELECT c.id FROM action.circulation c
    JOIN money.materialized_billable_xact_summary m ON c.id = m.id
    WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
    AND m.balance_owed = 0
    ORDER BY c.xact_start
    LIMIT max_circ_count;

  -- for each circ that we might want to age...
  FOR potential_aged_circ IN
    SELECT b.* FROM circs_to_age a
    JOIN action.circulation b ON a.id = b.id
  LOOP

    -- use the cutoff param as the default cutoff date for the current circ
    circ_cutoff := cutoff;

    -- renewals create a "chain" of circs; find the last circ in the current chain
    SELECT * INTO circ_chain_tail FROM action.circ_chain(potential_aged_circ.id) ORDER BY xact_start DESC LIMIT 1;

    -- user's history.circ.retention_start setting, if any, overrides default cutoff date
    -- (we are ignoring history.circ.retention_age which does not appear to be in use)
    usr_keep_start.value := NULL;
    SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start';
    IF usr_keep_start.value IS NOT NULL THEN
      circ_cutoff := oils_json_to_text(usr_keep_start.value)::DATE;
    END IF;

    -- don't age the chain if the last circ is later than the cutoff
    IF circ_chain_tail.xact_finish IS NULL OR circ_chain_tail.xact_finish > circ_cutoff THEN
      DELETE FROM circs_to_age WHERE id IN (SELECT id FROM action.circ_chain(potential_aged_circ.id));
      CONTINUE; -- proceed to next potential_aged_circ
    END IF;

    -- don't age the circ if it matches a defined exception
    -- TODO: If there's a matching exception, we skip only the current circ; should we skip the entire chain?
    has_exception := FALSE;
    SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
    IF has_exception IS TRUE THEN
      DELETE FROM circs_to_age WHERE id = potential_aged_circ.id;
      CONTINUE; -- proceed to next potential_aged_circ
    END IF;

  END LOOP;

  -- log the aging of this circ
  INSERT INTO sitka.aged_circs (id, delete_date)
    SELECT id, today FROM circs_to_age;

  -- update user circ counts
  CREATE TEMPORARY TABLE usr_circ_totals AS
    SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
      (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
    FROM action.circulation a 
    JOIN circs_to_age b ON a.id = b.id
    GROUP BY 1,2,3;
  UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
    FROM usr_circ_totals b WHERE a.usr = b.usr
    AND a.month = b.month AND a.year = b.year;
  INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
    SELECT usr, month, year, fresh_circs FROM usr_circ_totals
    WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
  DROP TABLE usr_circ_totals;

  -- this will set off the age_circs trigger on action.circulation
  DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);

  UPDATE action.aged_circulation
  SET usr_post_code = NULL, usr_birth_year = NULL
  WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;

  SELECT count(*) INTO circs_aged FROM circs_to_age;
  RETURN circs_aged;
END;
$$ LANGUAGE plpgsql;

SAVEPOINT ready;

---- To actually age circs, do something like the following:
--SELECT count(*) FROM action.aged_circulation;
--SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
--SELECT count(*) FROM action.aged_circulation;
--
--\echo 'SAVEPOINT done:'
--SAVEPOINT done;
--
--COMMIT;