1 --Upgrade Script for 3.3.3 to 3.4.0
2 \set eg_version '''3.4.0'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.4.0', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1168', :eg_version); -- csharp/khuckins/gmcharlt
8 UPDATE permission.perm_list
9 SET description = oils_i18n_gettext(
11 'Allows a user to apply values to workstation settings',
13 WHERE code = 'APPLY_WORKSTATION_SETTING' and description = 'APPLY_WORKSTATION_SETTING';
17 SELECT evergreen.upgrade_deps_block_check('1169', :eg_version);
19 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
21 'eg.catalog.search_templates', 'gui', 'object',
23 'eg.catalog.search_templates',
24 'Staff Catalog Search Templates',
31 SELECT evergreen.upgrade_deps_block_check('1170', :eg_version);
33 CREATE TABLE config.hold_type (
35 hold_type TEXT UNIQUE,
39 INSERT INTO config.hold_type (hold_type,description) VALUES
43 ('M','Metarecord Hold'),
46 ('I','Issuance Hold'),
50 ALTER TABLE action.hold_request ADD CONSTRAINT hold_request_hold_type_fkey FOREIGN KEY (hold_type) REFERENCES config.hold_type(hold_type) DEFERRABLE INITIALLY DEFERRED;
53 SELECT evergreen.upgrade_deps_block_check('1172', :eg_version);
55 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
57 'eg.grid.admin.local.config.hold_matrix_matchpoint', 'gui', 'object',
59 'eg.grid.admin.local.config.hold_matrix_matchpoint',
60 'Grid Config: admin.local.config.hold_matrix_matchpoint',
64 'eg.grid.admin.local.actor.address_alert', 'gui', 'object',
66 'eg.grid.admin.local.actor.address_alert',
67 'Grid Config: admin.local.actor.address_alert',
71 'eg.grid.admin.local.config.barcode_completion', 'gui', 'object',
73 'eg.grid.admin.local.config.barcode_completion',
74 'Grid Config: admin.local.config.barcode_completion',
78 'eg.grid.admin.local.actor.copy_alert_suppress', 'gui', 'object',
80 'eg.grid.admin.local.actor.copy_alert_suppress',
81 'Grid Config: admin.local.actor.copy_alert_suppress',
85 'eg.grid.admin.local.asset.copy_location', 'gui', 'object',
87 'eg.grid.admin.local.asset.copy_location',
88 'Grid Config: admin.local.asset.copy_location',
92 'eg.grid.admin.local.asset.copy_tag', 'gui', 'object',
94 'eg.grid.admin.local.asset.copy_tag',
95 'Grid Config: admin.local.asset.copy_tag',
99 'eg.grid.admin.local.permission.grp_penalty_threshold', 'gui', 'object',
101 'eg.grid.admin.local.permission.grp_penalty_threshold',
102 'Grid Config: admin.local.permission.grp_penalty_threshold',
106 'eg.grid.admin.local.config.non_cataloged_type', 'gui', 'object',
108 'eg.grid.admin.local.config.non_cataloged_type',
109 'Grid Config: admin.local.config.non_cataloged_type',
114 -- eg.grid.admin.local.rating.badge already exists
118 SELECT evergreen.upgrade_deps_block_check('1173', :eg_version);
120 CREATE TABLE config.print_template (
121 id SERIAL PRIMARY KEY,
122 name TEXT NOT NULL, -- programatic name
123 label TEXT NOT NULL, -- i18n
124 owner INT NOT NULL REFERENCES actor.org_unit (id),
125 active BOOLEAN NOT NULL DEFAULT FALSE,
126 locale TEXT REFERENCES config.i18n_locale(code)
127 ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
128 content_type TEXT NOT NULL DEFAULT 'text/html',
129 template TEXT NOT NULL,
130 CONSTRAINT name_once_per_lib UNIQUE (owner, name),
131 CONSTRAINT label_once_per_lib UNIQUE (owner, label)
134 INSERT INTO config.print_template
135 (id, name, locale, active, owner, label, template)
137 1, 'patron_address', 'en-US', FALSE,
138 (SELECT id FROM actor.org_unit WHERE parent_ou IS NULL),
139 oils_i18n_gettext(1, 'Address Label', 'cpt', 'label'),
142 SET patron = template_data.patron;
143 SET addr = template_data.address;
147 [% patron.first_given_name %]
148 [% patron.second_given_name %]
149 [% patron.family_name %]
151 <div>[% addr.street1 %]</div>
152 [% IF addr.street2 %]<div>[% addr.street2 %]</div>[% END %]
154 [% addr.city %], [% addr.state %] [% addr.post_code %]
160 INSERT INTO config.print_template
161 (id, name, locale, active, owner, label, template)
163 2, 'holds_for_bib', 'en-US', FALSE,
164 (SELECT id FROM actor.org_unit WHERE parent_ou IS NULL),
165 oils_i18n_gettext(2, 'Holds for Bib Record', 'cpt', 'label'),
169 SET holds = template_data;
170 # template_data is an arry of wide_hold hashes.
173 <div>Holds for record: [% holds.0.title %]</div>
175 <style>#holds-for-bib-table td { padding: 5px; }</style>
176 <table id="holds-for-bib-table">
179 <th>Request Date</th>
180 <th>Patron Barcode</th>
182 <th>Patron Alias</th>
183 <th>Current Item</th>
187 [% FOR hold IN holds %]
190 date.format(helpers.format_date(
191 hold.request_time, staff_org_timezone), '%x %r', locale)
193 <td>[% hold.ucard_barcode %]</td>
194 <td>[% hold.usr_family_name %]</td>
195 <td>[% hold.usr_alias %]</td>
196 <td>[% hold.cp_barcode %]</td>
203 [% staff_org.shortname %]
204 [% date.format(helpers.current_date(client_timezone), '%x %r', locale) %]
206 <div>Printed by [% staff.first_given_name %]</div>
213 -- Allow for 1k stock templates
214 SELECT SETVAL('config.print_template_id_seq'::TEXT, 1000);
216 INSERT INTO permission.perm_list (id, code, description)
217 VALUES (611, 'ADMIN_PRINT_TEMPLATE',
218 oils_i18n_gettext(611, 'Modify print templates', 'ppl', 'description'));
222 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1174', :eg_version);
224 ALTER TABLE asset.copy_tag
228 SELECT evergreen.upgrade_deps_block_check('1175', :eg_version);
230 CREATE TABLE config.carousel_type (
231 id SERIAL PRIMARY KEY,
233 automatic BOOLEAN NOT NULL DEFAULT TRUE,
234 filter_by_age BOOLEAN NOT NULL DEFAULT FALSE,
235 filter_by_copy_owning_lib BOOLEAN NOT NULL DEFAULT FALSE,
236 filter_by_copy_location BOOLEAN NOT NULL DEFAULT FALSE
239 INSERT INTO config.carousel_type
240 (id, name, automatic, filter_by_age, filter_by_copy_owning_lib, filter_by_copy_location)
242 (1, 'Manual', FALSE, FALSE, FALSE, FALSE),
243 (2, 'Newly Catalogued Items', TRUE, TRUE, TRUE, TRUE),
244 (3, 'Recently Returned Items', TRUE, TRUE, TRUE, TRUE),
245 (4, 'Top Circulated Items', TRUE, TRUE, TRUE, FALSE),
246 (5, 'Newest Items By Shelving Location', TRUE, TRUE, TRUE, FALSE)
249 SELECT SETVAL('config.carousel_type_id_seq'::TEXT, 100);
251 CREATE TABLE container.carousel (
252 id SERIAL PRIMARY KEY,
253 type INTEGER NOT NULL REFERENCES config.carousel_type (id),
254 owner INTEGER NOT NULL REFERENCES actor.org_unit (id),
256 bucket INTEGER REFERENCES container.biblio_record_entry_bucket (id),
257 creator INTEGER NOT NULL REFERENCES actor.usr (id),
258 editor INTEGER NOT NULL REFERENCES actor.usr (id),
259 create_time TIMESTAMPTZ NOT NULL DEFAULT now(),
260 edit_time TIMESTAMPTZ NOT NULL DEFAULT now(),
262 owning_lib_filter INT[],
263 copy_location_filter INT[],
264 last_refresh_time TIMESTAMPTZ,
265 active BOOLEAN NOT NULL DEFAULT TRUE,
266 max_items INTEGER NOT NULL
269 CREATE TABLE container.carousel_org_unit (
270 id SERIAL PRIMARY KEY,
271 carousel INTEGER NOT NULL REFERENCES container.carousel (id) ON DELETE CASCADE,
273 org_unit INTEGER NOT NULL REFERENCES actor.org_unit (id),
277 INSERT INTO container.biblio_record_entry_bucket_type (code, label) VALUES ('carousel', 'Carousel');
279 INSERT INTO permission.perm_list ( id, code, description ) VALUES
280 ( 612, 'ADMIN_CAROUSEL_TYPE', oils_i18n_gettext(611,
281 'Allow a user to manage carousel types', 'ppl', 'description')),
282 ( 613, 'ADMIN_CAROUSEL', oils_i18n_gettext(612,
283 'Allow a user to manage carousels', 'ppl', 'description')),
284 ( 614, 'REFRESH_CAROUSEL', oils_i18n_gettext(613,
285 'Allow a user to refresh carousels', 'ppl', 'description'))
289 SELECT evergreen.upgrade_deps_block_check('1176', :eg_version);
291 ALTER TABLE booking.reservation
292 ADD COLUMN note TEXT;
295 SELECT evergreen.upgrade_deps_block_check('1177', :eg_version);
297 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
299 'eg.grid.booking.manage', 'gui', 'object',
302 'Grid Config: Booking Manage Reservations',
305 'eg.grid.booking.pickup.ready', 'gui', 'object',
307 'booking.pickup.ready',
308 'Grid Config: Booking Ready to pick up grid',
311 'eg.grid.booking.pickup.picked_up', 'gui', 'object',
313 'booking.pickup.picked_up',
314 'Grid Config: Booking Already Picked Up grid',
317 'eg.grid.booking.return.patron.picked_up', 'gui', 'object',
319 'booking.return.patron.picked_up',
320 'Grid Config: Booking Return Patron tab Already Picked Up grid',
323 'eg.grid.booking.return.patron.returned', 'gui', 'object',
325 'booking.return.patron.returned',
326 'Grid Config: Booking Return Patron tab Returned Today grid',
329 'eg.grid.booking.return.resource.picked_up', 'gui', 'object',
331 'booking.return.resourcce.picked_up',
332 'Grid Config: Booking Return Resource tab Already Picked Up grid',
335 'eg.grid.booking.return.resource.returned', 'gui', 'object',
337 'booking.return.resource.returned',
338 'Grid Config: Booking Return Resource tab Returned Today grid',
341 'eg.booking.manage.selected_org_family', 'gui', 'object',
343 'booking.manage.selected_org_family',
344 'Sticky setting for pickup ou family in Manage Reservations screen',
347 'eg.booking.return.tab', 'gui', 'string',
349 'booking.return.tab',
350 'Sticky setting for tab in Booking Return',
353 'eg.booking.create.granularity', 'gui', 'integer',
355 'booking.create.granularity',
356 'Sticky setting for granularity combobox in Booking Create',
359 'eg.booking.create.multiday', 'gui', 'bool',
361 'booking.create.multiday',
362 'Default to creating multiday booking reservations',
365 'eg.booking.pickup.ready.only_show_captured', 'gui', 'bool',
367 'booking.pickup.ready.only_show_captured',
368 'Include only resources that have been captured in the Ready grid in the Pickup screen',
373 SELECT evergreen.upgrade_deps_block_check('1178', :eg_version);
375 INSERT INTO action_trigger.event_definition (active, owner, name, hook, validator, reactor, delay, group_field, max_delay, template)
376 VALUES (false, 1, 'Fine Limit Exceeded', 'penalty.PATRON_EXCEEDS_FINES', 'NOOP_True', 'SendEmail', '00:05:00', 'usr', '1 day',
379 [%- user = target.usr -%]
381 To: [%- params.recipient_email || user.email %]
382 From: [%- params.sender_email || default_sender %]
383 Date: [%- date.format(date.now, '%a, %d %b %Y %T -0000', gmt => 1) %]
384 Subject: Fine Limit Exceeded
385 Auto-Submitted: auto-generated
387 Dear [% user.first_given_name %] [% user.family_name %],
390 Our records indicate your account has exceeded the fine limit allowed for the use of your library account.
392 Please visit the library to pay your fines and restore full access to your account.
397 INSERT INTO action_trigger.environment (event_def, path) VALUES
398 (currval('action_trigger.event_definition_id_seq'), 'usr'),
399 (currval('action_trigger.event_definition_id_seq'), 'usr.card');
402 SELECT evergreen.upgrade_deps_block_check('1179', :eg_version);
404 INSERT INTO config.org_unit_setting_type
405 (grp, name, datatype, label, description)
408 'opac.show_owning_lib_column', 'bool',
410 'opac.show_owning_lib_column',
411 'Show Owning Lib in Items Out',
416 'opac.show_owning_lib_column',
417 'If enabled, the Owning Lib will be shown in the Items Out display.' ||
418 ' This may assist in requesting additional renewals',
425 SELECT evergreen.upgrade_deps_block_check('1180', :eg_version);
427 INSERT INTO permission.perm_list ( id, code, description ) VALUES
428 ( 615, 'ADMIN_REMOTEAUTH', oils_i18n_gettext( 615,
429 'Administer remote patron authentication', 'ppl', 'description' ));
431 CREATE TABLE config.remoteauth_profile (
432 name TEXT PRIMARY KEY,
434 context_org INT NOT NULL REFERENCES actor.org_unit(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
435 enabled BOOLEAN NOT NULL DEFAULT FALSE,
436 perm INT NOT NULL REFERENCES permission.perm_list(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
437 restrict_to_org BOOLEAN NOT NULL DEFAULT TRUE,
438 allow_inactive BOOL NOT NULL DEFAULT FALSE,
439 allow_expired BOOL NOT NULL DEFAULT FALSE,
441 usr_activity_type INT REFERENCES config.usr_activity_type(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
444 CREATE OR REPLACE FUNCTION actor.permit_remoteauth (profile_name TEXT, userid BIGINT) RETURNS TEXT AS $func$
446 usr actor.usr%ROWTYPE;
447 profile config.remoteauth_profile%ROWTYPE;
449 context_org_list INT[];
455 SELECT INTO usr * FROM actor.usr WHERE id = userid AND NOT deleted;
460 IF usr.barred IS TRUE THEN
464 SELECT INTO profile * FROM config.remoteauth_profile WHERE name = profile_name;
465 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( profile.context_org );
467 -- user's home library must be within the context org
468 IF profile.restrict_to_org IS TRUE AND usr.home_ou NOT IN (SELECT * FROM UNNEST(context_org_list)) THEN
472 SELECT INTO perm code FROM permission.perm_list WHERE id = profile.perm;
473 IF permission.usr_has_perm(usr.id, perm, profile.context_org) IS FALSE THEN
477 IF usr.expire_date < NOW() AND profile.allow_expired IS FALSE THEN
481 IF usr.active IS FALSE AND profile.allow_inactive IS FALSE THEN
485 -- Proximity of user's home_ou to context_org to see if penalties should be ignored.
486 SELECT INTO home_prox prox FROM actor.org_unit_proximity WHERE from_org = usr.home_ou AND to_org = profile.context_org;
488 -- Loop through the block list to see if the user has any matching penalties.
489 IF profile.block_list IS NOT NULL THEN
490 FOR block IN SELECT UNNEST(STRING_TO_ARRAY(profile.block_list, '|')) LOOP
491 SELECT INTO penalty_count COUNT(DISTINCT csp.*)
492 FROM actor.usr_standing_penalty usp
493 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
494 WHERE usp.usr = usr.id
495 AND usp.org_unit IN ( SELECT * FROM UNNEST(context_org_list) )
496 AND ( usp.stop_date IS NULL or usp.stop_date > NOW() )
497 AND ( csp.ignore_proximity IS NULL OR csp.ignore_proximity < home_prox )
498 AND csp.block_list ~ block;
499 IF penalty_count > 0 THEN
500 -- User has penalties that match this block, so auth is not permitted.
501 -- Don't bother testing the rest of the block list.
507 -- User has passed all tests.
511 $func$ LANGUAGE plpgsql;
516 SELECT evergreen.upgrade_deps_block_check('1181', :eg_version);
518 \qecho Migrating aged billing and payment data. This might take a while.
520 CREATE TABLE money.aged_payment (LIKE money.payment INCLUDING INDEXES);
521 ALTER TABLE money.aged_payment ADD COLUMN payment_type TEXT NOT NULL;
523 CREATE TABLE money.aged_billing (LIKE money.billing INCLUDING INDEXES);
526 /* LP 1858448 : Disable initial aged money migration
528 INSERT INTO money.aged_payment
529 SELECT mp.* FROM money.payment_view mp
530 JOIN action.aged_circulation circ ON (circ.id = mp.xact);
532 INSERT INTO money.aged_billing
533 SELECT mb.* FROM money.billing mb
534 JOIN action.aged_circulation circ ON (circ.id = mb.xact);
538 CREATE OR REPLACE VIEW money.all_payments AS
539 SELECT * FROM money.payment_view
541 SELECT * FROM money.aged_payment;
543 CREATE OR REPLACE VIEW money.all_billings AS
544 SELECT * FROM money.billing
546 SELECT * FROM money.aged_billing;
548 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
553 -- If there are any renewals for this circulation, don't archive or delete
554 -- it yet. We'll do so later, when we archive and delete the renewals.
556 SELECT 'Y' INTO found
557 FROM action.circulation
558 WHERE parent_circ = OLD.id
562 RETURN NULL; -- don't delete
565 -- Archive a copy of the old row to action.aged_circulation
567 INSERT INTO action.aged_circulation
568 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
569 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
570 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
571 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
572 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
573 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
575 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
576 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
577 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
578 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
579 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
580 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
581 FROM action.all_circulation WHERE id = OLD.id;
583 -- Migrate billings and payments to aged tables
585 /* LP 1858448 : Disable initial aged money migration
586 INSERT INTO money.aged_billing
587 SELECT * FROM money.billing WHERE xact = OLD.id;
589 INSERT INTO money.aged_payment
590 SELECT * FROM money.payment_view WHERE xact = OLD.id;
592 DELETE FROM money.payment WHERE xact = OLD.id;
593 DELETE FROM money.billing WHERE xact = OLD.id;
598 $$ LANGUAGE 'plpgsql';
601 /* LP 1858448 : Disable initial aged money migration
603 -- NOTE you could COMMIT here then start a new TRANSACTION if desired.
605 \qecho Deleting aged payments and billings from active payment/billing
606 \qecho tables. This may take a while...
608 ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr;
609 ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr;
610 ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr;
611 ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr;
612 ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr;
613 ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr;
614 ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr;
616 DELETE FROM money.payment WHERE id IN (SELECT id FROM money.aged_payment);
618 ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr;
619 ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr;
620 ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr;
621 ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr;
622 ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr;
623 ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr;
624 ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr;
626 -- TODO: This approach assumes most of the money.billing rows have been
627 -- copied to money.aged_billing. If that is not the case, which would
628 -- happen if circ anonymization is not enabled, it will be faster to
629 -- perform a simple delete instead of a truncate/rebuild.
631 -- Copy all money.billing rows that are not represented in money.aged_billing
632 CREATE TEMPORARY TABLE tmp_money_billing ON COMMIT DROP AS
633 SELECT mb.* FROM money.billing mb
634 LEFT JOIN money.aged_billing mab USING (id)
635 WHERE mab.id IS NULL;
637 ALTER TABLE money.billing DISABLE TRIGGER ALL;
639 -- temporarily remove the foreign key constraint to money.billing on
640 -- account adjusment. Needed for money.billing truncate.
641 ALTER TABLE money.account_adjustment
642 DROP CONSTRAINT account_adjustment_billing_fkey;
644 TRUNCATE money.billing;
646 INSERT INTO money.billing SELECT * FROM tmp_money_billing;
648 ALTER TABLE money.billing ENABLE TRIGGER ALL;
649 ALTER TABLE money.account_adjustment
650 ADD CONSTRAINT account_adjustment_billing_fkey
651 FOREIGN KEY (billing) REFERENCES money.billing (id);
654 -- Good to run after truncating -- OK to run after COMMIT.
655 ANALYZE money.billing;
660 SELECT evergreen.upgrade_deps_block_check('1182', :eg_version);
662 INSERT INTO permission.perm_list ( id, code, description ) VALUES
663 ( 616, 'IMPORT_USE_ORG_UNIT_COPIES', oils_i18n_gettext( 616,
664 'Allows users to import records based on the number of org unit copies attached to a record', 'ppl', 'description' )),
665 ( 617, 'IMPORT_ON_ORDER_CAT_COPY', oils_i18n_gettext( 617,
666 'Allows users to import copies based on the on-order items attached to a record', 'ppl', 'description' ));
668 -- function update in 1182 further updated in 1186
670 SELECT evergreen.upgrade_deps_block_check('1183', :eg_version);
672 INSERT into config.org_unit_setting_type
673 ( name, grp, label, description, datatype, fm_class ) VALUES
674 ( 'ui.patron.edit.au.ident_value.require', 'gui',
675 oils_i18n_gettext('ui.patron.edit.au.ident_value.require',
676 'require ident_value field on patron registration',
678 oils_i18n_gettext('ui.patron.edit.au.ident_value.require',
679 'The ident_value field will be required on the patron registration screen.',
680 'coust', 'description'),
685 SELECT evergreen.upgrade_deps_block_check('1184', :eg_version);
687 INSERT INTO permission.perm_list(id, code, description)
688 VALUES (618, 'CREATE_PRECAT', 'Allows user to create a pre-catalogued copy');
690 -- Add this new permission to any group with Staff login perm.
691 -- Manually remove if needed
692 INSERT INTO permission.grp_perm_map(perm, grp, depth) SELECT 618, map.grp, 0 FROM permission.grp_perm_map AS map WHERE map.perm = 2;
694 SELECT evergreen.upgrade_deps_block_check('1185', :eg_version); -- csharp / gmcharlt / jboyer
696 ALTER FUNCTION permission.grp_descendants( INT ) STABLE;
698 SELECT evergreen.upgrade_deps_block_check('1186', :eg_version);
700 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_org_unit_copies ( import_id BIGINT, merge_profile_id INT, lwm_ratio_value_p NUMERIC ) RETURNS BOOL AS $$
704 rec vandelay.bib_match%ROWTYPE;
709 max_copy_count INT := 0;
712 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
715 -- RAISE NOTICE 'already imported, cannot auto-overlay'
719 -- Gather all the owning libs for our import items.
720 -- These are our initial scope_orgs.
721 SELECT ARRAY_AGG(DISTINCT owning_lib) INTO scope_orgs
722 FROM vandelay.import_item
723 WHERE record = import_id;
725 WHILE CARDINALITY(scope_orgs) > 0 LOOP
726 FOR scope_org IN SELECT * FROM UNNEST(scope_orgs) LOOP
727 -- For each match, get a count of all copies at descendants of our scope org.
728 FOR rec IN SELECT * FROM vandelay.bib_match AS vbm
729 WHERE queued_record = import_id
730 ORDER BY vbm.eg_record DESC
732 SELECT COUNT(acp.id) INTO copy_count
733 FROM asset.copy AS acp
734 INNER JOIN asset.call_number AS acn
735 ON acp.call_number = acn.id
736 WHERE acn.owning_lib IN (SELECT id FROM
737 actor.org_unit_descendants(scope_org))
738 AND acn.record = rec.eg_record
739 AND acp.deleted = FALSE;
740 IF copy_count > max_copy_count THEN
741 max_copy_count := copy_count;
742 eg_id := rec.eg_record;
747 -- If no matching bibs had holdings, gather our next set of orgs to check, and iterate.
748 IF max_copy_count = 0 THEN
749 SELECT ARRAY_AGG(DISTINCT parent_ou) INTO scope_orgs
751 WHERE id IN (SELECT * FROM UNNEST(scope_orgs))
752 AND parent_ou IS NOT NULL;
756 IF eg_id IS NULL THEN
757 -- Could not determine best match via copy count
758 -- fall back to default best match
759 IF (SELECT * FROM vandelay.auto_overlay_bib_record_with_best( import_id, merge_profile_id, lwm_ratio_value_p )) THEN
766 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
770 SELECT evergreen.upgrade_deps_block_check('1187', :eg_version);
771 SELECT evergreen.upgrade_deps_block_check('1192', :eg_version);
773 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
778 -- If there are any renewals for this circulation, don't archive or delete
779 -- it yet. We'll do so later, when we archive and delete the renewals.
781 SELECT 'Y' INTO found
782 FROM action.circulation
783 WHERE parent_circ = OLD.id
787 RETURN NULL; -- don't delete
790 -- Archive a copy of the old row to action.aged_circulation
792 INSERT INTO action.aged_circulation
793 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
794 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
795 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
796 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
797 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
798 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
799 auto_renewal, auto_renewal_remaining)
801 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
802 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
803 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
804 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
805 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
806 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
807 auto_renewal, auto_renewal_remaining
808 FROM action.all_circulation WHERE id = OLD.id;
810 -- Migrate billings and payments to aged tables
812 /* LP 1858448 : Disable initial aged money migration
813 INSERT INTO money.aged_billing
814 SELECT * FROM money.billing WHERE xact = OLD.id;
816 INSERT INTO money.aged_payment
817 SELECT * FROM money.payment_view WHERE xact = OLD.id;
819 DELETE FROM money.payment WHERE xact = OLD.id;
820 DELETE FROM money.billing WHERE xact = OLD.id;
825 $$ LANGUAGE 'plpgsql';
827 SELECT evergreen.upgrade_deps_block_check('1188', :eg_version);
829 UPDATE action.circulation SET auto_renewal = FALSE WHERE auto_renewal IS NULL;
830 UPDATE action.aged_circulation SET auto_renewal = FALSE WHERE auto_renewal IS NULL;
833 SELECT evergreen.upgrade_deps_block_check('1189', :eg_version);
835 CREATE OR REPLACE VIEW action.open_circulation AS
837 FROM action.circulation
838 WHERE checkin_time IS NULL
841 CREATE OR REPLACE VIEW action.billable_circulations AS
843 FROM action.circulation
844 WHERE xact_finish IS NULL;
846 CREATE OR REPLACE VIEW reporter.overdue_circs AS
848 FROM "action".circulation
849 WHERE checkin_time is null
850 AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED') OR stop_fines IS NULL)
851 AND due_date < now();
853 CREATE OR REPLACE VIEW reporter.circ_type AS
855 CASE WHEN opac_renewal OR phone_renewal OR desk_renewal OR auto_renewal
859 FROM action.circulation;
861 SELECT evergreen.upgrade_deps_block_check('1190', :eg_version);
863 UPDATE action.circulation SET desk_renewal = FALSE WHERE auto_renewal IS TRUE;
865 UPDATE action.aged_circulation SET desk_renewal = FALSE WHERE auto_renewal IS TRUE;
868 SELECT evergreen.upgrade_deps_block_check('1191', :eg_version);
870 INSERT INTO permission.perm_list ( id, code, description ) SELECT DISTINCT
872 'EDIT_SELF_IN_CLIENT',
873 oils_i18n_gettext(619,
874 'Allow a user to edit their own account in the staff client', 'ppl', 'description'
876 FROM permission.perm_list
877 WHERE NOT EXISTS (SELECT 1 FROM permission.perm_list WHERE code = 'EDIT_SELF_IN_CLIENT');
881 -- The following two changes from 1188 cannot occur in a transaction with the
882 -- above updates because we will get an error about not being able to
883 -- alter a table with pending transactions. They also need to occur
884 -- after the above updates or the SET NOT NULL change will fail.
886 ALTER TABLE action.circulation ALTER COLUMN auto_renewal SET DEFAULT FALSE;
887 ALTER TABLE action.circulation ALTER COLUMN auto_renewal SET NOT NULL;
889 ALTER TABLE action.aged_circulation ALTER COLUMN auto_renewal SET DEFAULT FALSE;
890 ALTER TABLE action.aged_circulation ALTER COLUMN auto_renewal SET NOT NULL;
892 -- Update auditor tables to catch changes to source tables.
893 -- Can be removed/skipped if there were no schema changes.
894 SELECT auditor.update_auditors();