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