LP#1779920: stamp database update
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 1123.schema.autorenewals.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('1123', :eg_version);
4
5     ALTER TABLE config.rule_circ_duration
6     ADD column max_auto_renewals INTEGER;
7
8     ALTER TABLE action.circulation
9     ADD column auto_renewal BOOLEAN;
10
11     ALTER TABLE action.circulation
12     ADD column auto_renewal_remaining INTEGER;
13
14     ALTER TABLE action.aged_circulation
15     ADD column auto_renewal BOOLEAN;
16
17     ALTER TABLE action.aged_circulation
18     ADD column auto_renewal_remaining INTEGER;
19
20     INSERT INTO action_trigger.validator values('CircIsAutoRenewable', 'Checks whether the circulation is able to be autorenewed.');
21     INSERT INTO action_trigger.reactor values('Circ::AutoRenew', 'Auto-Renews a circulation.');
22     INSERT INTO action_trigger.hook(key, core_type, description) values('autorenewal', 'circ', 'Item was auto-renewed to patron.');
23
24     -- AutoRenewer A/T Def: 
25     INSERT INTO action_trigger.event_definition(active, owner, name, hook, validator, reactor, delay, max_delay, delay_field, group_field)
26         values (false, 1, 'Autorenew', 'checkout.due', 'CircIsOpen', 'Circ::AutoRenew', '-23 hours'::interval,'-1 minute'::interval, 'due_date', 'usr');
27
28     -- AutoRenewal outcome Email notifier A/T Def:
29     INSERT INTO action_trigger.event_definition(active, owner, name, hook, validator, reactor, group_field, template)
30         values (false, 1, 'AutorenewNotify', 'autorenewal', 'NOOP_True', 'SendEmail', 'usr', 
31 $$
32 [%- USE date -%]
33 [%- user = target.0.usr -%]
34 To: [%- params.recipient_email || user.email %]
35 From: [%- params.sender_email || default_sender %]
36 Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %]
37 Subject: Items Out Auto-Renewal Notification 
38 Auto-Submitted: auto-generated
39
40 Dear [% user.family_name %], [% user.first_given_name %]
41 An automatic renewal attempt was made for the following items:
42
43 [% FOR circ IN target %]
44     [%- SET idx = loop.count - 1; SET udata =  user_data.$idx -%]
45     [%- SET cid = circ.target_copy || udata.copy -%]
46     [%- SET copy_details = helpers.get_copy_bib_basics(cid) -%]
47     Item# [% loop.count %]
48     Title: [% copy_details.title %]
49     Author: [% copy_details.author %]
50     [%- IF udata.is_renewed %]
51     Status: Loan Renewed
52     New Due Date: [% date.format(helpers.format_date(udata.new_due_date), '%Y-%m-%d') %]
53     [%- ELSE %]
54     Status: Not Renewed
55     Reason: [% udata.reason %]
56     Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
57     [% END %]
58 [% END %]
59 $$
60     );
61
62     INSERT INTO action_trigger.environment (event_def, path ) VALUES
63     ( currval('action_trigger.event_definition_id_seq'), 'usr' ),
64     ( currval('action_trigger.event_definition_id_seq'), 'circ_lib' );
65
66
67 DROP VIEW action.all_circulation;
68 CREATE OR REPLACE VIEW action.all_circulation AS
69     SELECT  id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
70         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
71         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
72         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
73         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
74         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
75         auto_renewal, auto_renewal_remaining, NULL AS usr
76       FROM  action.aged_circulation
77             UNION ALL
78     SELECT  DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
79         cp.call_number AS copy_call_number, circ.copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
80         cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
81         circ.checkin_lib, circ.renewal_remaining, circ.grace_period, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
82         circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
83         circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
84         circ.parent_circ, circ.auto_renewal, circ.auto_renewal_remaining, circ.usr
85       FROM  action.circulation circ
86         JOIN asset.copy cp ON (circ.target_copy = cp.id)
87         JOIN asset.call_number cn ON (cp.call_number = cn.id)
88         JOIN actor.usr p ON (circ.usr = p.id)
89         LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
90         LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
91
92
93 DROP FUNCTION action.summarize_all_circ_chain (INTEGER);
94 DROP FUNCTION action.all_circ_chain (INTEGER);
95
96 -- rebuild slim circ view
97 DROP VIEW action.all_circulation_slim;
98 CREATE OR REPLACE VIEW action.all_circulation_slim AS
99     SELECT
100         id,
101         usr,
102         xact_start,
103         xact_finish,
104         unrecovered,
105         target_copy,
106         circ_lib,
107         circ_staff,
108         checkin_staff,
109         checkin_lib,
110         renewal_remaining,
111         grace_period,
112         due_date,
113         stop_fines_time,
114         checkin_time,
115         create_time,
116         duration,
117         fine_interval,
118         recurring_fine,
119         max_fine,
120         phone_renewal,
121         desk_renewal,
122         opac_renewal,
123         duration_rule,
124         recurring_fine_rule,
125         max_fine_rule,
126         stop_fines,
127         workstation,
128         checkin_workstation,
129         copy_location,
130         checkin_scan_time,
131         auto_renewal,
132         auto_renewal_remaining,
133         parent_circ
134     FROM action.circulation
135 UNION ALL
136     SELECT
137         id,
138         NULL AS usr,
139         xact_start,
140         xact_finish,
141         unrecovered,
142         target_copy,
143         circ_lib,
144         circ_staff,
145         checkin_staff,
146         checkin_lib,
147         renewal_remaining,
148         grace_period,
149         due_date,
150         stop_fines_time,
151         checkin_time,
152         create_time,
153         duration,
154         fine_interval,
155         recurring_fine,
156         max_fine,
157         phone_renewal,
158         desk_renewal,
159         opac_renewal,
160         duration_rule,
161         recurring_fine_rule,
162         max_fine_rule,
163         stop_fines,
164         workstation,
165         checkin_workstation,
166         copy_location,
167         checkin_scan_time,
168         auto_renewal,
169         auto_renewal_remaining,
170         parent_circ
171     FROM action.aged_circulation
172 ;
173
174 CREATE OR REPLACE FUNCTION action.all_circ_chain (ctx_circ_id INTEGER) 
175     RETURNS SETOF action.all_circulation_slim AS $$
176 DECLARE
177     tmp_circ action.all_circulation_slim%ROWTYPE;
178     circ_0 action.all_circulation_slim%ROWTYPE;
179 BEGIN
180
181     SELECT INTO tmp_circ * FROM action.all_circulation_slim WHERE id = ctx_circ_id;
182
183     IF tmp_circ IS NULL THEN
184         RETURN NEXT tmp_circ;
185     END IF;
186     circ_0 := tmp_circ;
187
188     -- find the front of the chain
189     WHILE TRUE LOOP
190         SELECT INTO tmp_circ * FROM action.all_circulation_slim 
191             WHERE id = tmp_circ.parent_circ;
192         IF tmp_circ IS NULL THEN
193             EXIT;
194         END IF;
195         circ_0 := tmp_circ;
196     END LOOP;
197
198     -- now send the circs to the caller, oldest to newest
199     tmp_circ := circ_0;
200     WHILE TRUE LOOP
201         IF tmp_circ IS NULL THEN
202             EXIT;
203         END IF;
204         RETURN NEXT tmp_circ;
205         SELECT INTO tmp_circ * FROM action.all_circulation_slim 
206             WHERE parent_circ = tmp_circ.id;
207     END LOOP;
208
209 END;
210 $$ LANGUAGE 'plpgsql';
211
212 -- same as action.summarize_circ_chain, but returns data collected
213 -- from action.all_circulation, which may include aged circulations.
214 CREATE OR REPLACE FUNCTION action.summarize_all_circ_chain 
215     (ctx_circ_id INTEGER) RETURNS action.circ_chain_summary AS $$
216
217 DECLARE
218
219     -- first circ in the chain
220     circ_0 action.all_circulation_slim%ROWTYPE;
221
222     -- last circ in the chain
223     circ_n action.all_circulation_slim%ROWTYPE;
224
225     -- circ chain under construction
226     chain action.circ_chain_summary;
227     tmp_circ action.all_circulation_slim%ROWTYPE;
228
229 BEGIN
230     
231     chain.num_circs := 0;
232     FOR tmp_circ IN SELECT * FROM action.all_circ_chain(ctx_circ_id) LOOP
233
234         IF chain.num_circs = 0 THEN
235             circ_0 := tmp_circ;
236         END IF;
237
238         chain.num_circs := chain.num_circs + 1;
239         circ_n := tmp_circ;
240     END LOOP;
241
242     chain.start_time := circ_0.xact_start;
243     chain.last_stop_fines := circ_n.stop_fines;
244     chain.last_stop_fines_time := circ_n.stop_fines_time;
245     chain.last_checkin_time := circ_n.checkin_time;
246     chain.last_checkin_scan_time := circ_n.checkin_scan_time;
247     SELECT INTO chain.checkout_workstation name FROM actor.workstation WHERE id = circ_0.workstation;
248     SELECT INTO chain.last_checkin_workstation name FROM actor.workstation WHERE id = circ_n.checkin_workstation;
249
250     IF chain.num_circs > 1 THEN
251         chain.last_renewal_time := circ_n.xact_start;
252         SELECT INTO chain.last_renewal_workstation name FROM actor.workstation WHERE id = circ_n.workstation;
253     END IF;
254
255     RETURN chain;
256
257 END;
258 $$ LANGUAGE 'plpgsql';
259
260
261 COMMIT;