age-circs.sql: move circ exception testing to separate function
[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 -- If circ CANNOT be aged based on exceptions and user settings, function returns NULL.
66 -- If circ CAN be aged, function returns xact_finish timestamp of last circ in chain.
67 CREATE OR REPLACE FUNCTION sitka.age_circ_test(circ_id BIGINT, cutoff DATE, skip_basic_tests BOOL DEFAULT FALSE) RETURNS TIMESTAMPTZ AS $$
68 DECLARE
69   potential_aged_circ  action.circulation%ROWTYPE;
70   balance              NUMERIC;
71   circ_chain_finish    TIMESTAMP WITH TIME ZONE;
72   usr_keep_start       TEXT;
73   usr_keep_age         TEXT;
74   usr_keep_date        DATE;
75   circ_cutoff          DATE;
76   has_exception        BOOL;
77 BEGIN
78
79   -- use the cutoff param as the default cutoff date for the current circ
80   circ_cutoff := cutoff;
81
82   -- grab our circ
83   SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
84
85   -- When you are batch-deleting old circs, you would normally gather a list of
86   -- closed circs with no outstanding balance, and then pass each of those
87   -- circs through the age_circ_test function to check for further exceptions.
88   -- In that situation, we don't want to waste time and processing power
89   -- re-checking whether the circs are closed or have outstanding balances,
90   -- so we provide an option to skip those basic tests.
91   IF skip_basic_tests IS FALSE THEN
92     IF potential_aged_circ.xact_finish IS NULL THEN
93       --RAISE NOTICE 'Cannot age circ %, circ is not closed', circ_id;
94       RETURN NULL;
95     END IF;
96     SELECT balance_owed INTO balance FROM money.materialized_billable_xact_summary WHERE id = circ_id;
97     IF balance != 0 THEN
98       --RAISE NOTICE 'Cannot age circ %, non-zero balance', circ_id;
99       RETURN NULL;
100     END IF;
101   END IF;
102     
103   -- renewals create a "chain" of circs; find the last circ in the current chain
104   SELECT MAX(xact_finish) INTO circ_chain_finish FROM action.circ_chain(potential_aged_circ.id);
105
106   -- user's history.circ.retention_start setting, if any, overrides default cutoff date
107   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
108     circ_cutoff := oils_json_to_text(usr_keep_start)::DATE;
109     --RAISE NOTICE 'circ cutoff adjusted to % for circ %', circ_cutoff, circ_id;
110   END IF;
111
112   -- trim cutoff to user's history.circ.retention_age, if set
113   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';
114   IF usr_keep_age IS NOT NULL AND sitka.is_valid_interval(usr_keep_age) IS TRUE THEN
115     SELECT (now() - usr_keep_age::INTERVAL)::DATE INTO usr_keep_date;
116     IF usr_keep_date IS NOT NULL AND usr_keep_date < circ_cutoff THEN
117       circ_cutoff := usr_keep_date;
118       --RAISE NOTICE 'circ cutoff adjusted to % for circ %', circ_cutoff, circ_id;
119     END IF;
120   END IF;
121
122   -- don't age the chain if the last circ is later than the cutoff
123   IF circ_chain_finish IS NULL OR circ_chain_finish > circ_cutoff THEN
124     --RAISE NOTICE 'Cannot age circ %, circ chain is still open or was open on cutoff date', circ_id;
125     RETURN NULL;
126   END IF;
127
128   -- don't age the circ if it matches a defined exception
129   has_exception := FALSE;
130   SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
131   IF has_exception IS TRUE THEN
132     --RAISE NOTICE 'Cannot age circ %, has exceptions', circ_id;
133     RETURN NULL;
134   END IF;
135
136   -- this circ passes all our tests; return the timestamp when the circ chain was closed
137   RETURN circ_chain_finish;
138
139 EXCEPTION WHEN OTHERS THEN
140   RAISE WARNING 'Cannot age circ %, error during age circ test', circ_id;
141   RETURN NULL;
142 END;
143 $$ LANGUAGE plpgsql;
144
145
146 \echo 'table for aged circs'
147 CREATE TABLE sitka.aged_circs (
148   id BIGINT NOT NULL,
149   circ_chain_tail_xact_finish TIMESTAMPTZ NOT NULL,
150   delete_date DATE NOT NULL
151 );
152
153 \echo 'table for aged circ count'
154 CREATE TABLE sitka.aged_circs_by_user (
155   usr BIGINT NOT NULL,
156   month INT NOT NULL,
157   year INT NOT NULL,
158   count INT NOT NULL DEFAULT 0,
159   UNIQUE (usr, month, year)
160 );
161
162 CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
163 DECLARE
164   today                DATE;
165   potential_aged_circ  BIGINT;
166   circ_chain_finish    TIMESTAMPTZ;
167   circs_aged           INT;
168 BEGIN
169   today := now()::DATE;
170
171   -- TODO: more strictness here?
172   CREATE TEMPORARY TABLE circs_to_age AS
173     SELECT c.id, NULL::TIMESTAMP WITH TIME ZONE AS circ_chain_tail_xact_finish
174     FROM action.circulation c
175     JOIN money.materialized_billable_xact_summary m ON c.id = m.id
176     WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
177     AND m.balance_owed = 0
178     ORDER BY c.xact_start
179     LIMIT max_circ_count;
180
181   -- for each circ that we might want to age...
182   FOR potential_aged_circ IN
183     SELECT id FROM circs_to_age
184   LOOP
185
186     circ_chain_finish := NULL;
187
188     -- test whether we can age each circ
189     SELECT * INTO circ_chain_finish FROM sitka.age_circ_test(potential_aged_circ, cutoff, TRUE);
190     IF circ_chain_finish IS NOT NULL THEN
191       UPDATE circs_to_age SET circ_chain_tail_xact_finish = circ_chain_finish WHERE id = potential_aged_circ;
192     ELSE
193       DELETE FROM circs_to_age WHERE id = potential_aged_circ;
194     END IF;
195
196   END LOOP;
197
198   -- update user circ counts
199   CREATE TEMPORARY TABLE usr_circ_totals AS
200     SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
201       (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
202     FROM action.circulation a 
203     JOIN circs_to_age b ON a.id = b.id
204     GROUP BY 1,2,3;
205   UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
206     FROM usr_circ_totals b WHERE a.usr = b.usr
207     AND a.month = b.month AND a.year = b.year;
208   INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
209     SELECT usr, month, year, fresh_circs FROM usr_circ_totals
210     WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
211   DROP TABLE usr_circ_totals;
212
213   -- old circs may have a null copy_location, but action.aged_circulation doesn't allow that
214   UPDATE action.circulation a SET copy_location = 1
215     FROM circs_to_age b WHERE a.id = b.id
216     AND a.copy_location IS NULL;
217
218   -- this will set off the age_circs trigger on action.circulation
219   DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
220
221   UPDATE action.aged_circulation
222   SET usr_post_code = NULL, usr_birth_year = NULL
223   WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
224
225   INSERT INTO sitka.aged_circs (id, circ_chain_tail_xact_finish, delete_date)
226     SELECT id, circ_chain_tail_xact_finish, today FROM circs_to_age;
227
228   SELECT count(*) INTO circs_aged FROM circs_to_age;
229   DROP TABLE circs_to_age;
230   RETURN circs_aged;
231 END; $$ LANGUAGE plpgsql;
232
233 SAVEPOINT ready;
234
235 ---- To actually age circs, do something like the following:
236 --SELECT count(*) FROM action.aged_circulation;
237 --SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
238 --SELECT count(*) FROM action.aged_circulation;
239 --
240 --\echo 'SAVEPOINT done:'
241 --SAVEPOINT done;
242 --
243 --COMMIT;
244