1 --Upgrade Script for 3.4.3 to 3.4.4
2 \set eg_version '''3.4.4'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.4.4', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1206', :eg_version);
8 CREATE OR REPLACE FUNCTION
9 action.hold_request_regen_copy_maps(
10 hold_id INTEGER, copy_ids INTEGER[]) RETURNS VOID AS $$
11 DELETE FROM action.hold_copy_map WHERE hold = $1;
12 INSERT INTO action.hold_copy_map (hold, target_copy) SELECT DISTINCT $1, UNNEST($2);
16 SELECT evergreen.upgrade_deps_block_check('1207', :eg_version);
18 UPDATE config.org_unit_setting_type
19 SET description = oils_i18n_gettext(
20 'circ.staff_client.receipt.alert_text',
21 'Text to be inserted into Print Templates in place of {{includes.alert_text}}',
23 WHERE name = 'circ.staff_client.receipt.alert_text';
24 UPDATE config.org_unit_setting_type
25 SET description = oils_i18n_gettext(
26 'circ.staff_client.receipt.event_text',
27 'Text to be inserted into Print Templates in place of {{includes.event_text}}',
29 WHERE name = 'circ.staff_client.receipt.event_text';
30 UPDATE config.org_unit_setting_type
31 SET description = oils_i18n_gettext(
32 'circ.staff_client.receipt.footer_text',
33 'Text to be inserted into Print Templates in place of {{includes.footer_text}}',
35 WHERE name = 'circ.staff_client.receipt.footer_text';
36 UPDATE config.org_unit_setting_type
37 SET description = oils_i18n_gettext(
38 'circ.staff_client.receipt.header_text',
39 'Text to be inserted into Print Templates in place of {{includes.header_text}}',
41 WHERE name = 'circ.staff_client.receipt.header_text';
42 UPDATE config.org_unit_setting_type
43 SET description = oils_i18n_gettext(
44 'circ.staff_client.receipt.notice_text',
45 'Text to be inserted into Print Templates in place of {{includes.notice_text}}',
47 WHERE name = 'circ.staff_client.receipt.notice_text';
50 SELECT evergreen.upgrade_deps_block_check('1208', :eg_version);
52 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_circ ( circ_closing_entry INT )
55 circ action.circulation%ROWTYPE;
56 e_closing action.emergency_closing%ROWTYPE;
57 e_c_circ action.emergency_closing_circulation%ROWTYPE;
58 closing actor.org_unit_closed%ROWTYPE;
59 adjacent actor.org_unit_closed%ROWTYPE;
60 bill money.billing%ROWTYPE;
61 last_bill money.billing%ROWTYPE;
63 hoo_close TIME WITHOUT TIME ZONE;
69 -- Gather objects involved
70 SELECT * INTO e_c_circ
71 FROM action.emergency_closing_circulation
72 WHERE id = circ_closing_entry;
74 IF e_c_circ.process_time IS NOT NULL THEN
75 -- Already processed ... moving on
79 SELECT * INTO e_closing
80 FROM action.emergency_closing
81 WHERE id = e_c_circ.emergency_closing;
83 IF e_closing.process_start_time IS NULL THEN
84 -- Huh... that's odd. And wrong.
89 FROM actor.org_unit_closed
90 WHERE emergency_closing = e_closing.id;
93 FROM action.circulation
94 WHERE id = e_c_circ.circulation;
96 -- Record the processing
97 UPDATE action.emergency_closing_circulation
98 SET original_due_date = circ.due_date,
100 WHERE id = circ_closing_entry;
102 UPDATE action.emergency_closing
103 SET last_update_time = NOW()
104 WHERE id = e_closing.id;
106 SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', circ.circ_lib);
107 SELECT value::BOOL INTO extend_grace FROM actor.org_unit_ancestor_setting('circ.grace.extend', circ.circ_lib);
109 new_due_date := evergreen.find_next_open_time( closing.org_unit, circ.due_date, EXTRACT(EPOCH FROM circ.duration)::INT % 86400 > 0 )::TEXT;
110 UPDATE action.circulation SET due_date = new_due_date::TIMESTAMPTZ WHERE id = circ.id;
112 -- Now, see if we need to get rid of some fines
113 SELECT * INTO last_bill
115 WHERE b.xact = circ.id
118 ORDER BY billing_ts DESC
124 WHERE b.xact = circ.id
128 b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
129 OR (extend_grace AND last_bill.billing_ts <= new_due_date::TIMESTAMPTZ + circ.grace_period)
131 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
134 IF avoid_negative THEN
135 PERFORM FROM money.materialized_billable_xact_summary WHERE id = circ.id AND balance_owed < bill.amount;
136 EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
142 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
150 CREATE OR REPLACE FUNCTION action.emergency_closing_stage_2_reservation ( res_closing_entry INT )
153 res booking.reservation%ROWTYPE;
154 e_closing action.emergency_closing%ROWTYPE;
155 e_c_res action.emergency_closing_reservation%ROWTYPE;
156 closing actor.org_unit_closed%ROWTYPE;
157 adjacent actor.org_unit_closed%ROWTYPE;
158 bill money.billing%ROWTYPE;
160 hoo_close TIME WITHOUT TIME ZONE;
165 -- Gather objects involved
166 SELECT * INTO e_c_res
167 FROM action.emergency_closing_reservation
168 WHERE id = res_closing_entry;
170 IF e_c_res.process_time IS NOT NULL THEN
171 -- Already processed ... moving on
175 SELECT * INTO e_closing
176 FROM action.emergency_closing
177 WHERE id = e_c_res.emergency_closing;
179 IF e_closing.process_start_time IS NULL THEN
180 -- Huh... that's odd. And wrong.
184 SELECT * INTO closing
185 FROM actor.org_unit_closed
186 WHERE emergency_closing = e_closing.id;
189 FROM booking.reservation
190 WHERE id = e_c_res.reservation;
192 IF res.pickup_lib IS NULL THEN -- Need to be far enough along to have a pickup lib
196 -- Record the processing
197 UPDATE action.emergency_closing_reservation
198 SET original_end_time = res.end_time,
200 WHERE id = res_closing_entry;
202 UPDATE action.emergency_closing
203 SET last_update_time = NOW()
204 WHERE id = e_closing.id;
206 SELECT value::BOOL INTO avoid_negative FROM actor.org_unit_ancestor_setting('bill.prohibit_negative_balance_on_overdues', res.pickup_lib);
208 new_due_date := evergreen.find_next_open_time( closing.org_unit, res.end_time, EXTRACT(EPOCH FROM res.booking_interval)::INT % 86400 > 0 )::TEXT;
209 UPDATE booking.reservation SET end_time = new_due_date::TIMESTAMPTZ WHERE id = res.id;
211 -- Now, see if we need to get rid of some fines
215 WHERE b.xact = res.id
218 AND b.billing_ts BETWEEN closing.close_start AND new_due_date::TIMESTAMPTZ
219 AND NOT EXISTS (SELECT 1 FROM money.account_adjustment a WHERE a.billing = b.id)
221 IF avoid_negative THEN
222 PERFORM FROM money.materialized_billable_xact_summary WHERE id = res.id AND balance_owed < bill.amount;
223 EXIT WHEN FOUND; -- We can't go negative, and voiding this bill would do that...
229 note = COALESCE(note,'') || ' :: Voided by emergency closing handler'
239 SELECT evergreen.upgrade_deps_block_check('1209', :eg_version);
241 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.circ_matrix_test_result AS $func$
243 user_object actor.usr%ROWTYPE;
244 standing_penalty config.standing_penalty%ROWTYPE;
245 item_object asset.copy%ROWTYPE;
246 item_status_object config.copy_status%ROWTYPE;
247 item_location_object asset.copy_location%ROWTYPE;
248 result action.circ_matrix_test_result;
249 circ_test action.found_circ_matrix_matchpoint;
250 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
251 circ_limit_set config.circ_limit_set%ROWTYPE;
252 hold_ratio action.hold_stats%ROWTYPE;
255 context_org_list INT[];
260 -- Assume success unless we hit a failure condition
261 result.success := TRUE;
263 -- Need user info to look up matchpoints
264 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
266 -- (Insta)Fail if we couldn't find the user
267 IF user_object.id IS NULL THEN
268 result.fail_part := 'no_user';
269 result.success := FALSE;
275 -- Need item info to look up matchpoints
276 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
278 -- (Insta)Fail if we couldn't find the item
279 IF item_object.id IS NULL THEN
280 result.fail_part := 'no_item';
281 result.success := FALSE;
287 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
289 circ_matchpoint := circ_test.matchpoint;
290 result.matchpoint := circ_matchpoint.id;
291 result.circulate := circ_matchpoint.circulate;
292 result.duration_rule := circ_matchpoint.duration_rule;
293 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
294 result.max_fine_rule := circ_matchpoint.max_fine_rule;
295 result.hard_due_date := circ_matchpoint.hard_due_date;
296 result.renewals := circ_matchpoint.renewals;
297 result.grace_period := circ_matchpoint.grace_period;
298 result.buildrows := circ_test.buildrows;
300 -- (Insta)Fail if we couldn't find a matchpoint
301 IF circ_test.success = false THEN
302 result.fail_part := 'no_matchpoint';
303 result.success := FALSE;
309 -- All failures before this point are non-recoverable
310 -- Below this point are possibly overridable failures
312 -- Fail if the user is barred
313 IF user_object.barred IS TRUE THEN
314 result.fail_part := 'actor.usr.barred';
315 result.success := FALSE;
320 -- Fail if the item can't circulate
321 IF item_object.circulate IS FALSE THEN
322 result.fail_part := 'asset.copy.circulate';
323 result.success := FALSE;
328 -- Fail if the item isn't in a circulateable status on a non-renewal
329 IF NOT renewal AND item_object.status <> 8 AND item_object.status NOT IN (
330 (SELECT id FROM config.copy_status WHERE is_available) ) THEN
331 result.fail_part := 'asset.copy.status';
332 result.success := FALSE;
335 -- Alternately, fail if the item isn't checked out on a renewal
336 ELSIF renewal AND item_object.status <> 1 THEN
337 result.fail_part := 'asset.copy.status';
338 result.success := FALSE;
343 -- Fail if the item can't circulate because of the shelving location
344 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
345 IF item_location_object.circulate IS FALSE THEN
346 result.fail_part := 'asset.copy_location.circulate';
347 result.success := FALSE;
352 -- Use Circ OU for penalties and such
353 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
355 -- Proximity of user's home_ou to circ_ou to see if penalties should be ignored.
356 SELECT INTO home_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = circ_ou;
358 -- Proximity of user's home_ou to item circ_lib to see if penalties should be ignored.
359 SELECT INTO item_prox prox FROM actor.org_unit_proximity WHERE from_org = user_object.home_ou AND to_org = item_object.circ_lib;
362 penalty_type = '%RENEW%';
364 penalty_type = '%CIRC%';
367 FOR standing_penalty IN
368 SELECT DISTINCT csp.*
369 FROM actor.usr_standing_penalty usp
370 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
371 WHERE usr = match_user
372 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
373 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
374 AND (csp.ignore_proximity IS NULL
375 OR csp.ignore_proximity < home_prox
376 OR csp.ignore_proximity < item_prox)
377 AND csp.block_list LIKE penalty_type LOOP
379 result.fail_part := standing_penalty.name;
380 result.success := FALSE;
385 -- Fail if the test is set to hard non-circulating
386 IF circ_matchpoint.circulate IS FALSE THEN
387 result.fail_part := 'config.circ_matrix_test.circulate';
388 result.success := FALSE;
393 -- Fail if the total copy-hold ratio is too low
394 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
395 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
396 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
397 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
398 result.success := FALSE;
404 -- Fail if the available copy-hold ratio is too low
405 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
406 IF hold_ratio.hold_count IS NULL THEN
407 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
409 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
410 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
411 result.success := FALSE;
417 -- Fail if the user has too many items out by defined limit sets
418 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
419 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
420 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
421 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
423 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
424 SELECT INTO context_org_list ARRAY_AGG(aou.id)
425 FROM actor.org_unit_full_path( circ_ou ) aou
426 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
427 WHERE aout.depth >= circ_limit_set.depth;
428 IF circ_limit_set.global THEN
429 WITH RECURSIVE descendant_depth AS (
432 FROM actor.org_unit ou
433 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
437 FROM actor.org_unit ou
438 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
439 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
441 SELECT INTO items_out COUNT(DISTINCT circ.id)
442 FROM action.circulation circ
443 JOIN asset.copy copy ON (copy.id = circ.target_copy)
444 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
445 WHERE circ.usr = match_user
446 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
447 AND circ.checkin_time IS NULL
448 AND circ.xact_finish IS NULL
449 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
450 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
451 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
452 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
454 IF items_out >= circ_limit_set.items_out THEN
455 result.fail_part := 'config.circ_matrix_circ_mod_test';
456 result.success := FALSE;
461 SELECT INTO result.limit_groups result.limit_groups || ARRAY_AGG(limit_group) FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id AND NOT check_only;
464 -- If we passed everything, return the successful matchpoint
471 $func$ LANGUAGE plpgsql;
474 SELECT evergreen.upgrade_deps_block_check('1211', :eg_version); -- Dyrcona/rhamby/gmcharlt
476 CREATE OR REPLACE FUNCTION actor.usr_delete(
481 old_profile actor.usr.profile%type;
482 old_home_ou actor.usr.home_ou%type;
483 new_profile actor.usr.profile%type;
484 new_home_ou actor.usr.home_ou%type;
486 new_dob actor.usr.dob%type;
489 id || '-PURGED-' || now(),
503 -- Quit if no such user
505 IF old_profile IS NULL THEN
509 perform actor.usr_purge_data( src_usr, dest_usr );
511 -- Find the root grp_tree and the root org_unit. This would be simpler if we
512 -- could assume that there is only one root. Theoretically, someday, maybe,
513 -- there could be multiple roots, so we take extra trouble to get the right ones.
520 permission.grp_ancestors( old_profile )
529 actor.org_unit_ancestors( old_home_ou )
533 -- Truncate date of birth
535 IF new_dob IS NOT NULL THEN
536 new_dob := date_trunc( 'year', new_dob );
543 profile = new_profile,
546 passwd = random()::text,
551 FROM config.identification_type
556 net_access_level = DEFAULT,
559 first_given_name = new_name,
560 second_given_name = NULL,
561 family_name = new_name,
566 evening_phone = NULL,
568 mailing_address = NULL,
569 billing_address = NULL,
570 home_ou = new_home_ou,
573 master_account = DEFAULT,
574 super_user = DEFAULT,
579 claims_returned_count = DEFAULT,
580 credit_forward_balance = DEFAULT,
581 last_xact_id = DEFAULT,
582 alert_message = NULL,
584 pref_first_given_name = NULL,
585 pref_second_given_name = NULL,
586 pref_family_name = NULL,
588 name_keywords = NULL,
598 -- Update auditor tables to catch changes to source tables.
599 -- Can be removed/skipped if there were no schema changes.
600 SELECT auditor.update_auditors();