age-circs.sql: bugfixes
[sitka/sitka-tools.git] / maintenance / age-circs.sql
CommitLineData
9fb9f27b
JD
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
cedfa307
JD
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
9fb9f27b
JD
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;
ceb63535 42 usr actor.usr%ROWTYPE;
9fb9f27b
JD
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;
ceb63535 48 SELECT * INTO usr FROM actor.usr WHERE id = potential_aged_circ.usr;
9fb9f27b
JD
49 SELECT count(*) INTO matching_exceptions
50 FROM sitka.age_circ_exception_matrix m
ceb63535 51 LEFT JOIN permission.grp_ancestors_distance( usr.profile ) upgad ON m.grp = upgad.id
9fb9f27b
JD
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\echo 'table for aged circs'
64CREATE TABLE sitka.aged_circs (
65 id BIGINT NOT NULL,
5f2162a2 66 circ_chain_tail_xact_finish TIMESTAMPTZ NOT NULL,
9fb9f27b
JD
67 delete_date DATE NOT NULL
68);
69
70\echo 'table for aged circ count'
71CREATE TABLE sitka.aged_circs_by_user (
72 usr BIGINT NOT NULL,
73 month INT NOT NULL,
74 year INT NOT NULL,
75 count INT NOT NULL DEFAULT 0,
76 UNIQUE (usr, month, year)
77);
78
79CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
80DECLARE
81 today DATE;
82 potential_aged_circ action.circulation%ROWTYPE;
cedfa307
JD
83 circ_chain_finish TIMESTAMP WITH TIME ZONE;
84 usr_keep_start TEXT;
85 usr_keep_age TEXT;
9fb9f27b
JD
86 circ_cutoff DATE;
87 has_exception BOOL;
88 circs_aged INT;
89BEGIN
90 today := now()::DATE;
91
92 -- TODO: more strictness here?
93 CREATE TEMPORARY TABLE circs_to_age AS
cedfa307
JD
94 SELECT c.id, NULL::TIMESTAMP WITH TIME ZONE AS circ_chain_tail_xact_finish
95 FROM action.circulation c
9fb9f27b
JD
96 JOIN money.materialized_billable_xact_summary m ON c.id = m.id
97 WHERE c.xact_finish IS NOT NULL AND c.xact_finish < cutoff
98 AND m.balance_owed = 0
99 ORDER BY c.xact_start
100 LIMIT max_circ_count;
101
102 -- for each circ that we might want to age...
103 FOR potential_aged_circ IN
104 SELECT b.* FROM circs_to_age a
105 JOIN action.circulation b ON a.id = b.id
106 LOOP
107
cedfa307
JD
108 -- reset variables
109 circ_chain_finish := NULL;
110 usr_keep_start := NULL;
111 usr_keep_age := NULL;
112
9fb9f27b
JD
113 -- use the cutoff param as the default cutoff date for the current circ
114 circ_cutoff := cutoff;
115
116 -- renewals create a "chain" of circs; find the last circ in the current chain
cedfa307
JD
117 SELECT MAX(xact_finish) INTO circ_chain_finish FROM action.circ_chain(potential_aged_circ.id);
118 UPDATE circs_to_age SET circ_chain_tail_xact_finish = circ_chain_finish WHERE id = potential_aged_circ.id;
9fb9f27b
JD
119
120 -- user's history.circ.retention_start setting, if any, overrides default cutoff date
cedfa307
JD
121 SELECT value INTO usr_keep_start FROM actor.usr_setting WHERE usr = potential_aged_circ.usr AND name = 'history.circ.retention_start';
122 IF usr_keep_start IS NOT NULL THEN
123 circ_cutoff := oils_json_to_text(usr_keep_start)::DATE;
124 END IF;
125
126 -- trim cutoff to user's history.circ.retention_age, if set
127 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';
128 IF usr_keep_age IS NOT NULL AND sitka.is_valid_interval(usr_keep_age) IS TRUE THEN
129 IF (now() - usr_keep_age::INTERVAL)::DATE > circ_cutoff THEN
130 circ_cutoff := (now() - oils_json_to_text(usr_keep_age)::INTERVAL)::DATE;
131 END IF;
9fb9f27b
JD
132 END IF;
133
134 -- don't age the chain if the last circ is later than the cutoff
cedfa307 135 IF circ_chain_finish IS NULL OR circ_chain_finish > circ_cutoff THEN
9fb9f27b
JD
136 DELETE FROM circs_to_age WHERE id IN (SELECT id FROM action.circ_chain(potential_aged_circ.id));
137 CONTINUE; -- proceed to next potential_aged_circ
138 END IF;
139
140 -- don't age the circ if it matches a defined exception
141 -- TODO: If there's a matching exception, we skip only the current circ; should we skip the entire chain?
142 has_exception := FALSE;
143 SELECT * INTO has_exception FROM sitka.has_age_circ_exception(potential_aged_circ.id);
144 IF has_exception IS TRUE THEN
145 DELETE FROM circs_to_age WHERE id = potential_aged_circ.id;
146 CONTINUE; -- proceed to next potential_aged_circ
147 END IF;
148
149 END LOOP;
150
9fb9f27b
JD
151 -- update user circ counts
152 CREATE TEMPORARY TABLE usr_circ_totals AS
153 SELECT a.usr, (EXTRACT(month FROM a.xact_start)) AS month,
154 (EXTRACT(year FROM a.xact_start)) AS year, count(*) AS fresh_circs
155 FROM action.circulation a
156 JOIN circs_to_age b ON a.id = b.id
157 GROUP BY 1,2,3;
158 UPDATE sitka.aged_circs_by_user a SET count = count + b.fresh_circs
159 FROM usr_circ_totals b WHERE a.usr = b.usr
160 AND a.month = b.month AND a.year = b.year;
161 INSERT INTO sitka.aged_circs_by_user (usr, month, year, count)
162 SELECT usr, month, year, fresh_circs FROM usr_circ_totals
163 WHERE (usr, month, year) NOT IN (SELECT usr, month, year FROM sitka.aged_circs_by_user);
164 DROP TABLE usr_circ_totals;
165
ceb63535
JD
166 -- old circs may have a null copy_location, but action.aged_circulation doesn't allow that
167 UPDATE action.circulation a SET copy_location = 1
168 FROM circs_to_age b WHERE a.id = b.id
169 AND a.copy_location IS NULL;
170
9fb9f27b
JD
171 -- this will set off the age_circs trigger on action.circulation
172 DELETE FROM action.circulation WHERE id IN (SELECT id FROM circs_to_age);
173
174 UPDATE action.aged_circulation
175 SET usr_post_code = NULL, usr_birth_year = NULL
176 WHERE usr_post_code IS NOT NULL OR usr_birth_year IS NOT NULL;
177
cedfa307
JD
178 INSERT INTO sitka.aged_circs (id, circ_chain_tail_xact_finish, delete_date)
179 SELECT id, circ_chain_tail_xact_finish, today FROM circs_to_age;
180
9fb9f27b 181 SELECT count(*) INTO circs_aged FROM circs_to_age;
ceb63535 182 DROP TABLE circs_to_age;
9fb9f27b 183 RETURN circs_aged;
cedfa307 184END; $$ LANGUAGE plpgsql;
9fb9f27b
JD
185
186SAVEPOINT ready;
187
188---- To actually age circs, do something like the following:
189--SELECT count(*) FROM action.aged_circulation;
190--SELECT * FROM sitka.age_circs( (now() - '2 years'::INTERVAL)::DATE, NULL );
191--SELECT count(*) FROM action.aged_circulation;
192--
193--\echo 'SAVEPOINT done:'
194--SAVEPOINT done;
195--
196--COMMIT;
197