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   matching_exceptions  INT;
  34 BEGIN
  35   matching_exceptions := 0;
  36   -- TODO: should we match exceptions against the entire circ chain?
  37   SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
  38   SELECT count(*) INTO matching_exceptions
  39     FROM sitka.age_circ_exception_matrix m
  40       LEFT JOIN permission.grp_ancestors_distance( potential_aged_circ.usr_profile ) upgad ON m.grp = upgad.id
  41       LEFT JOIN actor.org_unit_ancestors_distance( potential_aged_circ.circ_lib ) ctoua ON m.org_unit = ctoua.id
  42     WHERE m.active
  43       AND (m.grp IS NULL      OR upgad.id IS NOT NULL)
  44       AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL);
  45   IF matching_exceptions > 0 THEN
  46     RETURN TRUE;
  47   END IF;
  48   RETURN FALSE;
  49 END;
  50 $$ LANGUAGE plpgsql;
  51
  52 \echo 'table for aged circs'
  53 CREATE TABLE sitka.aged_circs (
  54   id BIGINT NOT NULL,
  55   circ_chain_tail_xact_finish TIMESTAMPTZ,
  56   delete_date DATE NOT NULL
  57 );
  58
  59 \echo 'table for aged circ count'
  60 CREATE TABLE sitka.aged_circs_by_user (
  61   usr BIGINT NOT NULL,
  62   month INT NOT NULL,
  63   year INT NOT NULL,
  64   count INT NOT NULL DEFAULT 0,
  65   UNIQUE (usr, month, year)
  66 );
  67
  68 CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
  69 DECLARE
  70   today                DATE;
  71   potential_aged_circ  action.circulation%ROWTYPE;
  72   circ_chain_tail      action.circulation%ROWTYPE;
  73   usr_keep_start       actor.usr_setting%ROWTYPE;
  74   usr_keep_age         actor.usr_setting%ROWTYPE;
  75   circ_cutoff          DATE;
  76   has_exception        BOOL;
  77   circs_aged           INT;
  78 BEGIN
  79   today := now()::DATE;
  80
  81   -- TODO: more strictness here?
  82   CREATE TEMPORARY TABLE circs_to_age AS
  83     SELECT c.id FROM action.circulation c
  84     JOIN money.materialized_billable_xact_summary m ON c.id = m.id
  85     WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
  86     AND m.balance_owed = 0
  87     ORDER BY c.xact_start
  88     LIMIT max_circ_count;
  89
  90   -- for each circ that we might want to age...
  91   FOR potential_aged_circ IN
  92     SELECT b.* FROM circs_to_age a
  93     JOIN action.circulation b ON a.id = b.id
  94   LOOP
  95
  96     -- use the cutoff param as the default cutoff date for the current circ
  97     circ_cutoff := cutoff;
  98
  99     -- renewals create a "chain" of circs; find the last circ in the current chain
 100     SELECT * INTO circ_chain_tail FROM action.circ_chain(potential_aged_circ.id) ORDER BY xact_start DESC LIMIT 1;
 101
 102     -- user's history.circ.retention_start setting, if any, overrides default cutoff date
 103     -- (we are ignoring history.circ.retention_age which does not appear to be in use)
 104     usr_keep_start.value := NULL;
 105     SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start';
 106     IF usr_keep_start.value IS NOT NULL THEN
 107       circ_cutoff := oils_json_to_text(usr_keep_start.value)::DATE;
 108     END IF;
 109
 110     -- don't age the chain if the last circ is later than the cutoff
 111     IF circ_chain_tail.xact_finish IS NULL OR circ_chain_tail.xact_finish > circ_cutoff THEN
 112       DELETE FROM circs_to_age WHERE id IN (SELECT id FROM action.circ_chain(potential_aged_circ.id));
 113       CONTINUE; -- proceed to next potential_aged_circ
 114     END IF;
 115
 116     -- don't age the circ if it matches a defined exception
 117     -- TODO: If there's a matching exception, we skip only the current circ; should we skip the entire chain?
 118     has_exception := FALSE;
 119     SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
 120     IF has_exception IS TRUE THEN
 121       DELETE FROM circs_to_age WHERE id = potential_aged_circ.id;
 122       CONTINUE; -- proceed to next potential_aged_circ
 123     END IF;
 124
 125   END LOOP;
 126
 127   -- log the aging of this circ
 128   INSERT INTO sitka.aged_circs (id, delete_date)
 129     SELECT id, today FROM circs_to_age;
 130
 131   -- update user circ counts
 132   CREATE TEMPORARY TABLE usr_circ_totals AS
 133     SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
 134       (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
 135     FROM action.circulation a
 136     JOIN circs_to_age b ON a.id = b.id
 137     GROUP BY 1,2,3;
 138   UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
 139     FROM usr_circ_totals b WHERE a.usr = b.usr
 140     AND a.month = b.month AND a.year = b.year;
 141   INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
 142     SELECT usr, month, year, fresh_circs FROM usr_circ_totals
 143     WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
 144   DROP TABLE usr_circ_totals;
 145
 146   -- this will set off the age_circs trigger on action.circulation
 147   DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
 148
 149   UPDATE action.aged_circulation
 150   SET usr_post_code = NULL, usr_birth_year = NULL
 151   WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
 152
 153   SELECT count(*) INTO circs_aged FROM circs_to_age;
 154   RETURN circs_aged;
 155 END;
 156 $$ LANGUAGE plpgsql;
 157
 158 SAVEPOINT ready;
 159
 160 ---- To actually age circs, do something like the following:
 161 --SELECT count(*) FROM action.aged_circulation;
 162 --SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
 163 --SELECT count(*) FROM action.aged_circulation;
 164 --
 165 --\echo 'SAVEPOINT done:'
 166 --SAVEPOINT done;
 167 --
 168 --COMMIT;
 169