a5b5353b88f3eace3a0091553b46787ea187cd65
[sitka/sitka-tools.git] / 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