Added Encode decode_utf8 use statement
[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;
33 matching_exceptions INT;
34BEGIN
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;
49END;
50$$ LANGUAGE plpgsql;
51
52\echo 'table for aged circs'
53CREATE 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'
60CREATE 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
68CREATE OR REPLACE FUNCTION sitka.age_circs(cutoff DATE, max_circ_count BIGINT) RETURNS INT AS $$
69DECLARE
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;
78BEGIN
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;
155END;
156$$ LANGUAGE plpgsql;
157
158SAVEPOINT 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