Open-ILS/src/sql/Pg/upgrade/XXXX.autorenewals_acp_and_circ_duration.sql

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