install-eg.sh: fix error in tarball download
[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 'utility function for testing interval'
22 CREATE OR REPLACE FUNCTION sitka.is_valid_interval(TEXT) RETURNS BOOL AS $$
23 BEGIN
24   RETURN CASE WHEN $1::INTERVAL IS NULL THEN FALSE ELSE TRUE END;
25 EXCEPTION WHEN OTHERS THEN
26   RETURN FALSE;
27 END;
28 $$ LANGUAGE plpgsql;
29
30 \echo 'exception matrix table'
31 CREATE TABLE IF NOT EXISTS sitka.age_circ_exception_matrix (
32   id SERIAL NOT NULL,
33   active BOOLEAN NOT NULL DEFAULT TRUE,
34   org_unit INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
35   grp INT REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
36 );
37
38 \echo 'function for finding exceptions'
39 CREATE OR REPLACE FUNCTION sitka.has_age_circ_exception(circ_id BIGINT) RETURNS BOOL AS $$
40 DECLARE
41   potential_aged_circ  action.circulation%ROWTYPE;
42   usr                  actor.usr%ROWTYPE;
43   matching_exceptions  INT;
44 BEGIN
45   matching_exceptions := 0;
46   -- TODO: should we match exceptions against the entire circ chain?
47   SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
48   SELECT * INTO usr FROM actor.usr WHERE id = potential_aged_circ.usr;
49   SELECT count(*) INTO matching_exceptions
50     FROM sitka.age_circ_exception_matrix m
51       LEFT JOIN permission.grp_ancestors_distance( usr.profile ) upgad ON m.grp = upgad.id
52       LEFT JOIN actor.org_unit_ancestors_distance( potential_aged_circ.circ_lib ) ctoua ON m.org_unit = ctoua.id
53     WHERE m.active
54       AND (m.grp IS NULL      OR upgad.id IS NOT NULL)
55       AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL);
56   IF matching_exceptions > 0 THEN
57     RETURN TRUE;
58   END IF;
59   RETURN FALSE;
60 END;
61 $$ LANGUAGE plpgsql;
62
63
64 -- function for testing whether circ should be aged
65 CREATE OR REPLACE FUNCTION sitka.age_circ_test(circ_id BIGINT, cutoff DATE, skip_basic_tests BOOL DEFAULT FALSE) RETURNS BOOLEAN AS $$
66 DECLARE
67   potential_aged_circ  action.circulation%ROWTYPE;
68   balance              NUMERIC;
69   circ_chain_finish    TIMESTAMP WITH TIME ZONE;
70   usr_keep_start       TEXT;
71   usr_keep_age         TEXT;
72   usr_keep_date        DATE;
73   circ_cutoff          DATE;
74   has_exception        BOOL;
75 BEGIN
76
77   -- use the cutoff param as the default cutoff date for the current circ
78   circ_cutoff := cutoff;
79
80   -- grab our circ
81   SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
82
83   -- When you are batch-deleting old circs, you would normally gather a list of
84   -- closed circs with no outstanding balance, and then pass each of those
85   -- circs through the age_circ_test function to check for further exceptions.
86   -- In that situation, we don't want to waste time and processing power
87   -- re-checking whether the circs are closed or have outstanding balances,
88   -- so we provide an option to skip those basic tests.
89   IF skip_basic_tests IS FALSE THEN
90     IF potential_aged_circ.xact_finish IS NULL THEN
91       RETURN FALSE;
92     END IF;
93     SELECT balance_owed INTO balance FROM money.materialized_billable_xact_summary WHERE id = circ_id;
94     IF balance != 0 THEN
95       RETURN FALSE;
96     END IF;
97   END IF;
98     
99   -- renewals create a "chain" of circs; find the last circ in the current chain
100   -- but don't use MAX(xact_finish) since the most recent circ might have a null xact_finish
101   SELECT xact_finish INTO circ_chain_finish FROM action.circ_chain(potential_aged_circ.id)
102   ORDER BY xact_start DESC LIMIT 1;
103
104   -- user's history.circ.retention_start setting, if any, overrides default cutoff date
105   SELECT value INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start'; IF usr_keep_start IS NOT NULL THEN
106     circ_cutoff := oils_json_to_text(usr_keep_start)::DATE;
107   END IF;
108
109   -- trim cutoff to user's history.circ.retention_age, if set
110   SELECT oils_json_to_text(value) INTO usr_keep_age FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_age';
111   IF usr_keep_age IS NOT NULL AND sitka.is_valid_interval(usr_keep_age) IS TRUE THEN
112     SELECT (now() - usr_keep_age::INTERVAL)::DATE INTO usr_keep_date;
113     IF usr_keep_date IS NOT NULL AND usr_keep_date < circ_cutoff THEN
114       circ_cutoff := usr_keep_date;
115     END IF;
116   END IF;
117
118   -- don't age the chain if the last circ is later than the cutoff
119   IF circ_chain_finish IS NULL OR circ_chain_finish > circ_cutoff THEN
120     RETURN FALSE;
121   END IF;
122
123   -- don't age the circ if it matches a defined exception
124   has_exception := FALSE;
125   SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
126   IF has_exception IS TRUE THEN
127     RETURN FALSE;
128   END IF;
129
130   -- this circ passes all our tests
131   RETURN TRUE;
132
133 EXCEPTION WHEN OTHERS THEN
134   RAISE WARNING 'Cannot age circ %, error during age circ test', circ_id;
135   RETURN FALSE;
136 END;
137 $$ LANGUAGE plpgsql;
138
139
140 \echo 'table for aged circs'
141 CREATE TABLE IF NOT EXISTS sitka.aged_circs (
142   id BIGINT NOT NULL,
143   delete_date DATE NOT NULL
144 );
145
146 \echo 'table for aged circ count'
147 CREATE TABLE IF NOT EXISTS sitka.aged_circs_by_user (
148   usr BIGINT NOT NULL,
149   month INT NOT NULL,
150   year INT NOT NULL,
151   count INT NOT NULL DEFAULT 0,
152   UNIQUE (usr, month, year)
153 );
154
155 CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
156 DECLARE
157   today                DATE;
158   potential_aged_circ  BIGINT;
159   can_age_circ         BOOL;
160   circs_aged           INT;
161 BEGIN
162   today := now()::DATE;
163
164   -- TODO: more strictness here?
165   CREATE TEMPORARY TABLE circs_to_age AS
166     SELECT c.id FROM action.circulation c
167     JOIN money.materialized_billable_xact_summary m ON c.id = m.id
168     WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
169     AND m.balance_owed = 0
170     ORDER BY c.xact_start
171     LIMIT max_circ_count;
172
173   -- for each circ that we might want to age...
174   FOR potential_aged_circ IN
175     SELECT id FROM circs_to_age
176   LOOP
177
178     can_age_circ := NULL;
179
180     -- test whether we can age each circ
181     SELECT * INTO can_age_circ FROM sitka.age_circ_test(potential_aged_circ, cutoff, TRUE);
182     IF can_age_circ IS FALSE OR can_age_circ IS NULL THEN
183       DELETE FROM circs_to_age WHERE id = potential_aged_circ;
184     END IF;
185
186   END LOOP;
187
188   -- update user circ counts
189   CREATE TEMPORARY TABLE usr_circ_totals AS
190     SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
191       (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
192     FROM action.circulation a 
193     JOIN circs_to_age b ON a.id = b.id
194     GROUP BY 1,2,3;
195   UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
196     FROM usr_circ_totals b WHERE a.usr = b.usr
197     AND a.month = b.month AND a.year = b.year;
198   INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
199     SELECT usr, month, year, fresh_circs FROM usr_circ_totals
200     WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
201   DROP TABLE usr_circ_totals;
202
203   -- old circs may have a null copy_location, but action.aged_circulation doesn't allow that
204   UPDATE action.circulation a SET copy_location = 1
205     FROM circs_to_age b WHERE a.id = b.id
206     AND a.copy_location IS NULL;
207
208   -- this will set off the age_circs trigger on action.circulation
209   DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
210
211   UPDATE action.aged_circulation
212   SET usr_post_code = NULL, usr_birth_year = NULL
213   WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
214
215   INSERT INTO sitka.aged_circs (id, delete_date)
216     SELECT id, today FROM circs_to_age;
217
218   SELECT count(*) INTO circs_aged FROM circs_to_age;
219   DROP TABLE circs_to_age;
220   RETURN circs_aged;
221 END; $$ LANGUAGE plpgsql;
222
223 SAVEPOINT ready;
224
225 ---- To actually age circs, do something like the following:
226 --SELECT count(*) FROM action.aged_circulation;
227 --SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
228 --SELECT count(*) FROM action.aged_circulation;
229 --
230 --\echo 'SAVEPOINT done:'
231 --SAVEPOINT done;
232 --
233 --COMMIT;
234