age-circs.sql: for clarity, age_circ_test function should really return a boolean
[sitka/sitka-tools.git] / maintenance / age-circs.sql
... / ...
CommitLineData
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
19BEGIN;
20
21\echo 'utility function for testing interval'
22CREATE OR REPLACE FUNCTION sitka.is_valid_interval(TEXT) RETURNS BOOL AS $$
23BEGIN
24 RETURN CASE WHEN $1::INTERVAL IS NULL THEN FALSE ELSE TRUE END;
25EXCEPTION WHEN OTHERS THEN
26 RETURN FALSE;
27END;
28$$ LANGUAGE plpgsql;
29
30\echo 'exception matrix table'
31CREATE TABLE 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'
39CREATE OR REPLACE FUNCTION sitka.has_age_circ_exception(circ_id BIGINT) RETURNS BOOL AS $$
40DECLARE
41 potential_aged_circ action.circulation%ROWTYPE;
42 usr actor.usr%ROWTYPE;
43 matching_exceptions INT;
44BEGIN
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;
60END;
61$$ LANGUAGE plpgsql;
62
63
64-- function for testing whether circ should be aged
65CREATE OR REPLACE FUNCTION sitka.age_circ_test(circ_id BIGINT, cutoff DATE, skip_basic_tests BOOL DEFAULT FALSE) RETURNS TIMESTAMPTZ AS $$
66DECLARE
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;
75BEGIN
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 SELECT MAX(xact_finish) INTO circ_chain_finish FROM action.circ_chain(potential_aged_circ.id);
101
102 -- user's history.circ.retention_start setting, if any, overrides default cutoff date
103 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
104 circ_cutoff := oils_json_to_text(usr_keep_start)::DATE;
105 END IF;
106
107 -- trim cutoff to user's history.circ.retention_age, if set
108 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';
109 IF usr_keep_age IS NOT NULL AND sitka.is_valid_interval(usr_keep_age) IS TRUE THEN
110 SELECT (now() - usr_keep_age::INTERVAL)::DATE INTO usr_keep_date;
111 IF usr_keep_date IS NOT NULL AND usr_keep_date < circ_cutoff THEN
112 circ_cutoff := usr_keep_date;
113 END IF;
114 END IF;
115
116 -- don't age the chain if the last circ is later than the cutoff
117 IF circ_chain_finish IS NULL OR circ_chain_finish > circ_cutoff THEN
118 RETURN FALSE;
119 END IF;
120
121 -- don't age the circ if it matches a defined exception
122 has_exception := FALSE;
123 SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
124 IF has_exception IS TRUE THEN
125 RETURN FALSE;
126 END IF;
127
128 -- this circ passes all our tests
129 RETURN TRUE;
130
131EXCEPTION WHEN OTHERS THEN
132 RAISE WARNING 'Cannot age circ %, error during age circ test', circ_id;
133 RETURN FALSE;
134END;
135$$ LANGUAGE plpgsql;
136
137
138\echo 'table for aged circs'
139CREATE TABLE sitka.aged_circs (
140 id BIGINT NOT NULL,
141 delete_date DATE NOT NULL
142);
143
144\echo 'table for aged circ count'
145CREATE TABLE sitka.aged_circs_by_user (
146 usr BIGINT NOT NULL,
147 month INT NOT NULL,
148 year INT NOT NULL,
149 count INT NOT NULL DEFAULT 0,
150 UNIQUE (usr, month, year)
151);
152
153CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
154DECLARE
155 today DATE;
156 potential_aged_circ BIGINT;
157 can_age_circ BOOL;
158 circs_aged INT;
159BEGIN
160 today := now()::DATE;
161
162 -- TODO: more strictness here?
163 CREATE TEMPORARY TABLE circs_to_age AS
164 SELECT c.id FROM action.circulation c
165 JOIN money.materialized_billable_xact_summary m ON c.id = m.id
166 WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
167 AND m.balance_owed = 0
168 ORDER BY c.xact_start
169 LIMIT max_circ_count;
170
171 -- for each circ that we might want to age...
172 FOR potential_aged_circ IN
173 SELECT id FROM circs_to_age
174 LOOP
175
176 can_age_circ := NULL;
177
178 -- test whether we can age each circ
179 SELECT * INTO can_age_circ FROM sitka.age_circ_test(potential_aged_circ, cutoff, TRUE);
180 IF can_age_circ IS FALSE OR can_age_circ IS NULL THEN
181 DELETE FROM circs_to_age WHERE id = potential_aged_circ;
182 END IF;
183
184 END LOOP;
185
186 -- update user circ counts
187 CREATE TEMPORARY TABLE usr_circ_totals AS
188 SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
189 (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
190 FROM action.circulation a
191 JOIN circs_to_age b ON a.id = b.id
192 GROUP BY 1,2,3;
193 UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
194 FROM usr_circ_totals b WHERE a.usr = b.usr
195 AND a.month = b.month AND a.year = b.year;
196 INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
197 SELECT usr, month, year, fresh_circs FROM usr_circ_totals
198 WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
199 DROP TABLE usr_circ_totals;
200
201 -- old circs may have a null copy_location, but action.aged_circulation doesn't allow that
202 UPDATE action.circulation a SET copy_location = 1
203 FROM circs_to_age b WHERE a.id = b.id
204 AND a.copy_location IS NULL;
205
206 -- this will set off the age_circs trigger on action.circulation
207 DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
208
209 UPDATE action.aged_circulation
210 SET usr_post_code = NULL, usr_birth_year = NULL
211 WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
212
213 INSERT INTO sitka.aged_circs (id, delete_date)
214 SELECT id, today FROM circs_to_age;
215
216 SELECT count(*) INTO circs_aged FROM circs_to_age;
217 DROP TABLE circs_to_age;
218 RETURN circs_aged;
219END; $$ LANGUAGE plpgsql;
220
221SAVEPOINT ready;
222
223---- To actually age circs, do something like the following:
224--SELECT count(*) FROM action.aged_circulation;
225--SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
226--SELECT count(*) FROM action.aged_circulation;
227--
228--\echo 'SAVEPOINT done:'
229--SAVEPOINT done;
230--
231--COMMIT;
232