age-circs.sql: for clarity, age_circ_test function should really return a boolean
[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 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 TIMESTAMPTZ 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   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
131 EXCEPTION WHEN OTHERS THEN
132   RAISE WARNING 'Cannot age circ %, error during age circ test', circ_id;
133   RETURN FALSE;
134 END;
135 $$ LANGUAGE plpgsql;
136
137
138 \echo 'table for aged circs'
139 CREATE TABLE sitka.aged_circs (
140   id BIGINT NOT NULL,
141   delete_date DATE NOT NULL
142 );
143
144 \echo 'table for aged circ count'
145 CREATE 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
153 CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
154 DECLARE
155   today                DATE;
156   potential_aged_circ  BIGINT;
157   can_age_circ         BOOL;
158   circs_aged           INT;
159 BEGIN
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;
219 END; $$ LANGUAGE plpgsql;
220
221 SAVEPOINT 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