5fb7f53bda93f160144c5de8429f0dd5df1cae1e
[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   matching_exceptions  INT;
34 BEGIN
35   matching_exceptions := 0;
36   -- TODO: should we match exceptions against the entire circ chain?
37   SELECT * INTO potential_aged_circ FROM action.circulation WHERE id = circ_id;
38   SELECT count(*) INTO matching_exceptions
39     FROM sitka.age_circ_exception_matrix m
40       LEFT JOIN permission.grp_ancestors_distance( potential_aged_circ.usr_profile ) upgad ON m.grp = upgad.id
41       LEFT JOIN actor.org_unit_ancestors_distance( potential_aged_circ.circ_lib ) ctoua ON m.org_unit = ctoua.id
42     WHERE m.active
43       AND (m.grp IS NULL      OR upgad.id IS NOT NULL)
44       AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL);
45   IF matching_exceptions > 0 THEN
46     RETURN TRUE;
47   END IF;
48   RETURN FALSE;
49 END;
50 $$ LANGUAGE plpgsql;
51
52 \echo 'table for aged circs'
53 CREATE TABLE sitka.aged_circs (
54   id BIGINT NOT NULL,
55   circ_chain_tail_xact_finish TIMESTAMPTZ,
56   delete_date DATE NOT NULL
57 );
58
59 \echo 'table for aged circ count'
60 CREATE TABLE sitka.aged_circs_by_user (
61   usr BIGINT NOT NULL,
62   month INT NOT NULL,
63   year INT NOT NULL,
64   count INT NOT NULL DEFAULT 0,
65   UNIQUE (usr, month, year)
66 );
67
68 CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
69 DECLARE
70   today                DATE;
71   potential_aged_circ  action.circulation%ROWTYPE;
72   circ_chain_tail      action.circulation%ROWTYPE;
73   usr_keep_start       actor.usr_setting%ROWTYPE;
74   usr_keep_age         actor.usr_setting%ROWTYPE;
75   circ_cutoff          DATE;
76   has_exception        BOOL;
77   circs_aged           INT;
78 BEGIN
79   today := now()::DATE;
80
81   -- TODO: more strictness here?
82   CREATE TEMPORARY TABLE circs_to_age AS
83     SELECT c.id FROM action.circulation c
84     JOIN money.materialized_billable_xact_summary m ON c.id = m.id
85     WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
86     AND m.balance_owed = 0
87     ORDER BY c.xact_start
88     LIMIT max_circ_count;
89
90   -- for each circ that we might want to age...
91   FOR potential_aged_circ IN
92     SELECT b.* FROM circs_to_age a
93     JOIN action.circulation b ON a.id = b.id
94   LOOP
95
96     -- use the cutoff param as the default cutoff date for the current circ
97     circ_cutoff := cutoff;
98
99     -- renewals create a "chain" of circs; find the last circ in the current chain
100     SELECT * INTO circ_chain_tail FROM action.circ_chain(potential_aged_circ.id) ORDER BY xact_start DESC LIMIT 1;
101
102     -- user's history.circ.retention_start setting, if any, overrides default cutoff date
103     -- (we are ignoring history.circ.retention_age which does not appear to be in use)
104     usr_keep_start.value := NULL;
105     SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start';
106     IF usr_keep_start.value IS NOT NULL THEN
107       circ_cutoff := oils_json_to_text(usr_keep_start.value)::DATE;
108     END IF;
109
110     -- don't age the chain if the last circ is later than the cutoff
111     IF circ_chain_tail.xact_finish IS NULL OR circ_chain_tail.xact_finish > circ_cutoff THEN
112       DELETE FROM circs_to_age WHERE id IN (SELECT id FROM action.circ_chain(potential_aged_circ.id));
113       CONTINUE; -- proceed to next potential_aged_circ
114     END IF;
115
116     -- don't age the circ if it matches a defined exception
117     -- TODO: If there's a matching exception, we skip only the current circ; should we skip the entire chain?
118     has_exception := FALSE;
119     SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
120     IF has_exception IS TRUE THEN
121       DELETE FROM circs_to_age WHERE id = potential_aged_circ.id;
122       CONTINUE; -- proceed to next potential_aged_circ
123     END IF;
124
125   END LOOP;
126
127   -- log the aging of this circ
128   INSERT INTO sitka.aged_circs (id, delete_date)
129     SELECT id, today FROM circs_to_age;
130
131   -- update user circ counts
132   CREATE TEMPORARY TABLE usr_circ_totals AS
133     SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
134       (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
135     FROM action.circulation a 
136     JOIN circs_to_age b ON a.id = b.id
137     GROUP BY 1,2,3;
138   UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
139     FROM usr_circ_totals b WHERE a.usr = b.usr
140     AND a.month = b.month AND a.year = b.year;
141   INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
142     SELECT usr, month, year, fresh_circs FROM usr_circ_totals
143     WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
144   DROP TABLE usr_circ_totals;
145
146   -- this will set off the age_circs trigger on action.circulation
147   DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
148
149   UPDATE action.aged_circulation
150   SET usr_post_code = NULL, usr_birth_year = NULL
151   WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
152
153   SELECT count(*) INTO circs_aged FROM circs_to_age;
154   RETURN circs_aged;
155 END;
156 $$ LANGUAGE plpgsql;
157
158 SAVEPOINT ready;
159
160 ---- To actually age circs, do something like the following:
161 --SELECT count(*) FROM action.aged_circulation;
162 --SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
163 --SELECT count(*) FROM action.aged_circulation;
164 --
165 --\echo 'SAVEPOINT done:'
166 --SAVEPOINT done;
167 --
168 --COMMIT;
169