maintenance/age-circs.sql

   1 /*
   2  * age-circs.sql - a script to age/anonymize patrons' circ history.
   3  *
   4  * Copyright (C) 2008 Equinox Software, Inc.
   5  * Copyright (C) 2014 BC Libraries Cooperative
   6  * Author: Jeff Davis <jdavis@sitka.bclibraries.ca>
   7  *
   8  * This program is free software; you can redistribute it and/or
   9  * modify it under the terms of the GNU General Public License
  10  * as published by the Free Software Foundation; either version 2
  11  * of the License, or (at your option) any later version.
  12  *
  13  * This program is distributed in the hope that it will be useful,
  14  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  15  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  16  * GNU General Public License for more details.
  17  */
  18
  19 BEGIN;
  20
  21 \echo 'exception matrix table'
  22 CREATE TABLE sitka.age_circ_exception_matrix (
  23   id SERIAL NOT NULL,
  24   active BOOLEAN NOT NULL DEFAULT TRUE,
  25   org_unit INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  26   grp INT REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
  27 );
  28
  29 \echo 'function for finding exceptions'
  30 CREATE OR REPLACE FUNCTION sitka.has_age_circ_exception(circ_id BIGINT) RETURNS BOOL AS $$
  31 DECLARE
  32   potential_aged_circ  action.circulation%ROWTYPE;
  33   usr                  actor.usr%ROWTYPE;
  34   matching_exceptions  INT;
  35 BEGIN
  36   matching_exceptions := 0;
  37   -- TODO: should we match exceptions against the entire circ chain?
  38   SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
  39   SELECT * INTO usr FROM actor.usr WHERE id = potential_aged_circ.usr;
  40   SELECT count(*) INTO matching_exceptions
  41     FROM sitka.age_circ_exception_matrix m
  42       LEFT JOIN permission.grp_ancestors_distance( usr.profile ) upgad ON m.grp = upgad.id
  43       LEFT JOIN actor.org_unit_ancestors_distance( potential_aged_circ.circ_lib ) ctoua ON m.org_unit = ctoua.id
  44     WHERE m.active
  45       AND (m.grp IS NULL      OR upgad.id IS NOT NULL)
  46       AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL);
  47   IF matching_exceptions > 0 THEN
  48     RETURN TRUE;
  49   END IF;
  50   RETURN FALSE;
  51 END;
  52 $$ LANGUAGE plpgsql;
  53
  54 \echo 'table for aged circs'
  55 CREATE TABLE sitka.aged_circs (
  56   id BIGINT NOT NULL,
  57   circ_chain_tail_xact_finish TIMESTAMPTZ NOT NULL,
  58   delete_date DATE NOT NULL
  59 );
  60
  61 \echo 'table for aged circ count'
  62 CREATE TABLE sitka.aged_circs_by_user (
  63   usr BIGINT NOT NULL,
  64   month INT NOT NULL,
  65   year INT NOT NULL,
  66   count INT NOT NULL DEFAULT 0,
  67   UNIQUE (usr, month, year)
  68 );
  69
  70 CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
  71 DECLARE
  72   today                DATE;
  73   potential_aged_circ  action.circulation%ROWTYPE;
  74   circ_chain_tail      action.circulation%ROWTYPE;
  75   usr_keep_start       actor.usr_setting%ROWTYPE;
  76   usr_keep_age         actor.usr_setting%ROWTYPE;
  77   circ_cutoff          DATE;
  78   has_exception        BOOL;
  79   circs_aged           INT;
  80 BEGIN
  81   today := now()::DATE;
  82
  83   -- TODO: more strictness here?
  84   CREATE TEMPORARY TABLE circs_to_age AS
  85     SELECT c.id FROM action.circulation c
  86     JOIN money.materialized_billable_xact_summary m ON c.id = m.id
  87     WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
  88     AND m.balance_owed = 0
  89     ORDER BY c.xact_start
  90     LIMIT max_circ_count;
  91
  92   -- for each circ that we might want to age...
  93   FOR potential_aged_circ IN
  94     SELECT b.* FROM circs_to_age a
  95     JOIN action.circulation b ON a.id = b.id
  96   LOOP
  97
  98     -- use the cutoff param as the default cutoff date for the current circ
  99     circ_cutoff := cutoff;
 100
 101     -- renewals create a "chain" of circs; find the last circ in the current chain
 102     SELECT * INTO circ_chain_tail FROM action.circ_chain(potential_aged_circ.id) ORDER BY xact_start DESC LIMIT 1;
 103
 104     -- user's history.circ.retention_start setting, if any, overrides default cutoff date
 105     -- (we are ignoring history.circ.retention_age which does not appear to be in use)
 106     usr_keep_start.value := NULL;
 107     SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start';
 108     IF usr_keep_start.value IS NOT NULL THEN
 109       circ_cutoff := oils_json_to_text(usr_keep_start.value)::DATE;
 110     END IF;
 111
 112     -- don't age the chain if the last circ is later than the cutoff
 113     IF circ_chain_tail.xact_finish IS NULL OR circ_chain_tail.xact_finish > circ_cutoff THEN
 114       DELETE FROM circs_to_age WHERE id IN (SELECT id FROM action.circ_chain(potential_aged_circ.id));
 115       CONTINUE; -- proceed to next potential_aged_circ
 116     END IF;
 117
 118     -- don't age the circ if it matches a defined exception
 119     -- TODO: If there's a matching exception, we skip only the current circ; should we skip the entire chain?
 120     has_exception := FALSE;
 121     SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
 122     IF has_exception IS TRUE THEN
 123       DELETE FROM circs_to_age WHERE id = potential_aged_circ.id;
 124       CONTINUE; -- proceed to next potential_aged_circ
 125     END IF;
 126
 127   END LOOP;
 128
 129   -- log the aging of this circ
 130   INSERT INTO sitka.aged_circs (id, circ_chain_tail_xact_finish, delete_date)
 131     SELECT id, circ_chain_tail.xact_finish, today FROM circs_to_age;
 132
 133   -- update user circ counts
 134   CREATE TEMPORARY TABLE usr_circ_totals AS
 135     SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
 136       (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
 137     FROM action.circulation a
 138     JOIN circs_to_age b ON a.id = b.id
 139     GROUP BY 1,2,3;
 140   UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
 141     FROM usr_circ_totals b WHERE a.usr = b.usr
 142     AND a.month = b.month AND a.year = b.year;
 143   INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
 144     SELECT usr, month, year, fresh_circs FROM usr_circ_totals
 145     WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
 146   DROP TABLE usr_circ_totals;
 147
 148   -- old circs may have a null copy_location, but action.aged_circulation doesn't allow that
 149   UPDATE action.circulation a SET copy_location = 1
 150     FROM circs_to_age b WHERE a.id = b.id
 151     AND a.copy_location IS NULL;
 152
 153   -- this will set off the age_circs trigger on action.circulation
 154   DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
 155
 156   UPDATE action.aged_circulation
 157   SET usr_post_code = NULL, usr_birth_year = NULL
 158   WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
 159
 160   SELECT count(*) INTO circs_aged FROM circs_to_age;
 161   DROP TABLE circs_to_age;
 162   RETURN circs_aged;
 163 END;
 164 $$ LANGUAGE plpgsql;
 165
 166 SAVEPOINT ready;
 167
 168 ---- To actually age circs, do something like the following:
 169 --SELECT count(*) FROM action.aged_circulation;
 170 --SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
 171 --SELECT count(*) FROM action.aged_circulation;
 172 --
 173 --\echo 'SAVEPOINT done:'
 174 --SAVEPOINT done;
 175 --
 176 --COMMIT;
 177