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-beta2', :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);
525 INSERT INTO money.aged_payment
526 SELECT mp.* FROM money.payment_view mp
527 JOIN action.aged_circulation circ ON (circ.id = mp.xact);
529 INSERT INTO money.aged_billing
530 SELECT mb.* FROM money.billing mb
531 JOIN action.aged_circulation circ ON (circ.id = mb.xact);
533 CREATE OR REPLACE VIEW money.all_payments AS
534 SELECT * FROM money.payment_view
536 SELECT * FROM money.aged_payment;
538 CREATE OR REPLACE VIEW money.all_billings AS
539 SELECT * FROM money.billing
541 SELECT * FROM money.aged_billing;
543 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
548 -- If there are any renewals for this circulation, don't archive or delete
549 -- it yet. We'll do so later, when we archive and delete the renewals.
551 SELECT 'Y' INTO found
552 FROM action.circulation
553 WHERE parent_circ = OLD.id
557 RETURN NULL; -- don't delete
560 -- Archive a copy of the old row to action.aged_circulation
562 INSERT INTO action.aged_circulation
563 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
564 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
565 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
566 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
567 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
568 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ)
570 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
571 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
572 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
573 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
574 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
575 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
576 FROM action.all_circulation WHERE id = OLD.id;
578 -- Migrate billings and payments to aged tables
580 INSERT INTO money.aged_billing
581 SELECT * FROM money.billing WHERE xact = OLD.id;
583 INSERT INTO money.aged_payment
584 SELECT * FROM money.payment_view WHERE xact = OLD.id;
586 DELETE FROM money.payment WHERE xact = OLD.id;
587 DELETE FROM money.billing WHERE xact = OLD.id;
591 $$ LANGUAGE 'plpgsql';
593 -- NOTE you could COMMIT here then start a new TRANSACTION if desired.
595 \qecho Deleting aged payments and billings from active payment/billing
596 \qecho tables. This may take a while...
598 ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr;
599 ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr;
600 ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr;
601 ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr;
602 ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr;
603 ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr;
604 ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr;
606 DELETE FROM money.payment WHERE id IN (SELECT id FROM money.aged_payment);
608 ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr;
609 ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr;
610 ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr;
611 ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr;
612 ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr;
613 ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr;
614 ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr;
616 -- TODO: This approach assumes most of the money.billing rows have been
617 -- copied to money.aged_billing. If that is not the case, which would
618 -- happen if circ anonymization is not enabled, it will be faster to
619 -- perform a simple delete instead of a truncate/rebuild.
621 -- Copy all money.billing rows that are not represented in money.aged_billing
622 CREATE TEMPORARY TABLE tmp_money_billing ON COMMIT DROP AS
623 SELECT mb.* FROM money.billing mb
624 LEFT JOIN money.aged_billing mab USING (id)
625 WHERE mab.id IS NULL;
627 ALTER TABLE money.billing DISABLE TRIGGER ALL;
629 -- temporarily remove the foreign key constraint to money.billing on
630 -- account adjusment. Needed for money.billing truncate.
631 ALTER TABLE money.account_adjustment
632 DROP CONSTRAINT account_adjustment_billing_fkey;
634 TRUNCATE money.billing;
636 INSERT INTO money.billing SELECT * FROM tmp_money_billing;
638 ALTER TABLE money.billing ENABLE TRIGGER ALL;
639 ALTER TABLE money.account_adjustment
640 ADD CONSTRAINT account_adjustment_billing_fkey
641 FOREIGN KEY (billing) REFERENCES money.billing (id);
644 -- Good to run after truncating -- OK to run after COMMIT.
645 ANALYZE money.billing;
648 SELECT evergreen.upgrade_deps_block_check('1182', :eg_version);
650 INSERT INTO permission.perm_list ( id, code, description ) VALUES
651 ( 616, 'IMPORT_USE_ORG_UNIT_COPIES', oils_i18n_gettext( 616,
652 'Allows users to import records based on the number of org unit copies attached to a record', 'ppl', 'description' )),
653 ( 617, 'IMPORT_ON_ORDER_CAT_COPY', oils_i18n_gettext( 617,
654 'Allows users to import copies based on the on-order items attached to a record', 'ppl', 'description' ));
656 -- function update in 1182 further updated in 1186
658 SELECT evergreen.upgrade_deps_block_check('1183', :eg_version);
660 INSERT into config.org_unit_setting_type
661 ( name, grp, label, description, datatype, fm_class ) VALUES
662 ( 'ui.patron.edit.au.ident_value.require', 'gui',
663 oils_i18n_gettext('ui.patron.edit.au.ident_value.require',
664 'require ident_value field on patron registration',
666 oils_i18n_gettext('ui.patron.edit.au.ident_value.require',
667 'The ident_value field will be required on the patron registration screen.',
668 'coust', 'description'),
673 SELECT evergreen.upgrade_deps_block_check('1184', :eg_version);
675 INSERT INTO permission.perm_list(id, code, description)
676 VALUES (618, 'CREATE_PRECAT', 'Allows user to create a pre-catalogued copy');
678 -- Add this new permission to any group with Staff login perm.
679 -- Manually remove if needed
680 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;
682 SELECT evergreen.upgrade_deps_block_check('1185', :eg_version); -- csharp / gmcharlt / jboyer
684 ALTER FUNCTION permission.grp_descendants( INT ) STABLE;
686 SELECT evergreen.upgrade_deps_block_check('1186', :eg_version);
688 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 $$
692 rec vandelay.bib_match%ROWTYPE;
697 max_copy_count INT := 0;
700 PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
703 -- RAISE NOTICE 'already imported, cannot auto-overlay'
707 -- Gather all the owning libs for our import items.
708 -- These are our initial scope_orgs.
709 SELECT ARRAY_AGG(DISTINCT owning_lib) INTO scope_orgs
710 FROM vandelay.import_item
711 WHERE record = import_id;
713 WHILE CARDINALITY(scope_orgs) > 0 LOOP
714 FOR scope_org IN SELECT * FROM UNNEST(scope_orgs) LOOP
715 -- For each match, get a count of all copies at descendants of our scope org.
716 FOR rec IN SELECT * FROM vandelay.bib_match AS vbm
717 WHERE queued_record = import_id
718 ORDER BY vbm.eg_record DESC
720 SELECT COUNT(acp.id) INTO copy_count
721 FROM asset.copy AS acp
722 INNER JOIN asset.call_number AS acn
723 ON acp.call_number = acn.id
724 WHERE acn.owning_lib IN (SELECT id FROM
725 actor.org_unit_descendants(scope_org))
726 AND acn.record = rec.eg_record
727 AND acp.deleted = FALSE;
728 IF copy_count > max_copy_count THEN
729 max_copy_count := copy_count;
730 eg_id := rec.eg_record;
735 -- If no matching bibs had holdings, gather our next set of orgs to check, and iterate.
736 IF max_copy_count = 0 THEN
737 SELECT ARRAY_AGG(DISTINCT parent_ou) INTO scope_orgs
739 WHERE id IN (SELECT * FROM UNNEST(scope_orgs))
740 AND parent_ou IS NOT NULL;
744 IF eg_id IS NULL THEN
745 -- Could not determine best match via copy count
746 -- fall back to default best match
747 IF (SELECT * FROM vandelay.auto_overlay_bib_record_with_best( import_id, merge_profile_id, lwm_ratio_value_p )) THEN
754 RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
758 SELECT evergreen.upgrade_deps_block_check('1187', :eg_version);
760 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
765 -- If there are any renewals for this circulation, don't archive or delete
766 -- it yet. We'll do so later, when we archive and delete the renewals.
768 SELECT 'Y' INTO found
769 FROM action.circulation
770 WHERE parent_circ = OLD.id
774 RETURN NULL; -- don't delete
777 -- Archive a copy of the old row to action.aged_circulation
779 INSERT INTO action.aged_circulation
780 (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
781 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
782 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
783 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
784 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
785 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
786 auto_renewal, auto_renewal_remaining)
788 id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
789 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
790 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
791 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
792 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
793 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
794 auto_renewal, auto_renewal_remaining
795 FROM action.all_circulation WHERE id = OLD.id;
799 $$ LANGUAGE 'plpgsql';
802 SELECT evergreen.upgrade_deps_block_check('1188', :eg_version);
804 UPDATE action.circulation SET auto_renewal = FALSE WHERE auto_renewal IS NULL;
805 UPDATE action.aged_circulation SET auto_renewal = FALSE WHERE auto_renewal IS NULL;
808 SELECT evergreen.upgrade_deps_block_check('1189', :eg_version);
810 CREATE OR REPLACE VIEW action.open_circulation AS
812 FROM action.circulation
813 WHERE checkin_time IS NULL
816 CREATE OR REPLACE VIEW action.billable_circulations AS
818 FROM action.circulation
819 WHERE xact_finish IS NULL;
821 CREATE OR REPLACE VIEW reporter.overdue_circs AS
823 FROM "action".circulation
824 WHERE checkin_time is null
825 AND (stop_fines NOT IN ('LOST','CLAIMSRETURNED') OR stop_fines IS NULL)
826 AND due_date < now();
828 CREATE OR REPLACE VIEW reporter.circ_type AS
830 CASE WHEN opac_renewal OR phone_renewal OR desk_renewal OR auto_renewal
834 FROM action.circulation;
836 SELECT evergreen.upgrade_deps_block_check('1190', :eg_version);
838 UPDATE action.circulation SET desk_renewal = FALSE WHERE auto_renewal IS TRUE;
840 UPDATE action.aged_circulation SET desk_renewal = FALSE WHERE auto_renewal IS TRUE;
843 SELECT evergreen.upgrade_deps_block_check('1191', :eg_version);
845 INSERT INTO permission.perm_list ( id, code, description ) SELECT DISTINCT
847 'EDIT_SELF_IN_CLIENT',
848 oils_i18n_gettext(619,
849 'Allow a user to edit their own account in the staff client', 'ppl', 'description'
851 FROM permission.perm_list
852 WHERE NOT EXISTS (SELECT 1 FROM permission.perm_list WHERE code = 'EDIT_SELF_IN_CLIENT');
856 -- The following two changes from 1188 cannot occur in a transaction with the
857 -- above updates because we will get an error about not being able to
858 -- alter a table with pending transactions. They also need to occur
859 -- after the above updates or the SET NOT NULL change will fail.
861 ALTER TABLE action.circulation ALTER COLUMN auto_renewal SET DEFAULT FALSE;
862 ALTER TABLE action.circulation ALTER COLUMN auto_renewal SET NOT NULL;
864 ALTER TABLE action.aged_circulation ALTER COLUMN auto_renewal SET DEFAULT FALSE;
865 ALTER TABLE action.aged_circulation ALTER COLUMN auto_renewal SET NOT NULL;
867 -- Update auditor tables to catch changes to source tables.
868 -- Can be removed/skipped if there were no schema changes.
869 SELECT auditor.update_auditors();