1 --Upgrade Script for 3.9.1 to 3.10.0
2 \set eg_version '''3.10.0'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.10.0', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('1326', :eg_version);
8 INSERT into config.workstation_setting_type (name, grp, datatype, label)
10 'eg.grid.admin.config.idl_field_doc', 'gui', 'object',
12 'eg.grid.admin.config.idl_field_doc',
13 'Grid Config: admin.config.idl_field_doc',
19 SELECT evergreen.upgrade_deps_block_check('1329', :eg_version);
21 CREATE TABLE config.openathens_uid_field (
22 id SERIAL PRIMARY KEY,
26 INSERT INTO config.openathens_uid_field
33 SELECT SETVAL('config.openathens_uid_field_id_seq'::TEXT, 100);
35 CREATE TABLE config.openathens_name_field (
36 id SERIAL PRIMARY KEY,
40 INSERT INTO config.openathens_name_field
48 SELECT SETVAL('config.openathens_name_field_id_seq'::TEXT, 100);
50 CREATE TABLE config.openathens_identity (
51 id SERIAL PRIMARY KEY,
52 active BOOL NOT NULL DEFAULT true,
53 org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
54 api_key TEXT NOT NULL,
55 connection_id TEXT NOT NULL,
56 connection_uri TEXT NOT NULL,
57 auto_signon_enabled BOOL NOT NULL DEFAULT true,
58 auto_signout_enabled BOOL NOT NULL DEFAULT false,
59 unique_identifier INT NOT NULL REFERENCES config.openathens_uid_field (id) DEFAULT 1,
60 display_name INT NOT NULL REFERENCES config.openathens_name_field (id) DEFAULT 1,
61 release_prefix BOOL NOT NULL DEFAULT false,
62 release_first_given_name BOOL NOT NULL DEFAULT false,
63 release_second_given_name BOOL NOT NULL DEFAULT false,
64 release_family_name BOOL NOT NULL DEFAULT false,
65 release_suffix BOOL NOT NULL DEFAULT false,
66 release_email BOOL NOT NULL DEFAULT false,
67 release_home_ou BOOL NOT NULL DEFAULT false,
68 release_barcode BOOL NOT NULL DEFAULT false
72 INSERT INTO permission.perm_list ( id, code, description) VALUES
73 ( 639, 'ADMIN_OPENATHENS', oils_i18n_gettext(639,
74 'Allow a user to administer OpenAthens authentication service', 'ppl', 'description'));
78 SELECT evergreen.upgrade_deps_block_check('1330', :eg_version);
80 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
82 'eg.grid.admin.local.negative_balances', 'gui', 'object',
84 'eg.grid.admin.local.negative_balances',
85 'Patrons With Negative Balances Grid Settings',
89 'eg.orgselect.admin.local.negative_balances', 'gui', 'integer',
91 'eg.orgselect.admin.local.negative_balances',
92 'Default org unit for patron negative balances interface',
98 SELECT evergreen.upgrade_deps_block_check('1331', :eg_version);
100 INSERT into config.org_unit_setting_type
101 (name, datatype, grp, label, description)
103 'ui.staff.traditional_catalog.enabled', 'bool', 'gui',
105 'ui.staff.traditional_catalog.enabled',
106 'GUI: Enable Traditional Staff Catalog',
110 'ui.staff.traditional_catalog.enabled',
111 'Display an entry point in the browser client for the ' ||
112 'traditional staff catalog.',
113 'coust', 'description'
120 SELECT evergreen.upgrade_deps_block_check('1332', :eg_version);
122 INSERT into config.org_unit_setting_type
123 ( name, grp, label, description, datatype, fm_class ) VALUES
125 ( 'acq.default_owning_lib_for_auto_lids_strategy', 'acq',
126 oils_i18n_gettext('acq.default_owning_lib_for_auto_lids_strategy',
127 'How to set default owning library for auto-created line item items',
129 oils_i18n_gettext('acq.default_owning_lib_for_auto_lids_strategy',
130 'Stategy to use to set default owning library to set when line item items are auto-created because the provider''s default copy count has been set. Valid values are "workstation" to use the workstation library, "blank" to leave it blank, and "use_setting" to use the "Default owning library for auto-created line item items" setting. If not set, the workstation library will be used.',
131 'coust', 'description'),
133 ,( 'acq.default_owning_lib_for_auto_lids', 'acq',
134 oils_i18n_gettext('acq.default_owning_lib_for_auto_lids',
135 'Default owning library for auto-created line item items',
137 oils_i18n_gettext('acq.default_owning_lib_for_auto_lids',
138 'The default owning library to set when line item items are auto-created because the provider''s default copy count has been set. This applies if the "How to set default owning library for auto-created line item items" setting is set to "use_setting".',
139 'coust', 'description'),
144 SELECT evergreen.upgrade_deps_block_check('1333', :eg_version);
146 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
148 'eg.grid.acq.lineitem.history', 'gui', 'object',
150 'eg.grid.acq.lineitem.history',
151 'Grid Config: Acq Lineitem History',
155 'eg.grid.acq.po.history', 'gui', 'object',
157 'eg.grid.acq.po.history',
158 'Grid Config: Acq PO History',
162 'eg.grid.acq.po.edi_messages', 'gui', 'object',
164 'eg.grid.acq.po.edi_messages',
165 'Grid Config: Acq PO EDI Messages',
169 'acq.lineitem.page_size', 'gui', 'integer',
171 'acq.lineitem.page_size',
172 'ACQ Lineitem List Page Size',
176 'ui.staff.angular_acq_search.enabled', 'gui', 'bool',
178 'ui.staff.angular_acq_search.enabled',
179 'Enable Experimental ACQ Selection/Purchase Search Interface Links',
184 INSERT INTO config.print_template
185 (id, name, label, owner, active, locale, template)
187 5, 'lineitem_worksheet', 'Lineitem Worksheet', 1, TRUE, 'en-US',
190 USE money=format('%.2f');
192 SET li = template_data.lineitem;
195 FOREACH attr IN li.attributes;
196 IF attr.attr_type == 'lineitem_marc_attr_definition';
197 IF attr.attr_name == 'title';
198 title = attr.attr_value;
199 ELSIF attr.attr_name == 'author';
200 author = attr.attr_value;
206 <div class="wrapper">
207 <div class="summary" style='font-size:110%; font-weight:bold;'>
208 <div>Title: [% title.substr(0, 80) %][% IF title.length > 80 %]...[% END %]</div>
209 <div>Author: [% author %]</div>
210 <div>Item Count: [% li.lineitem_details.size %]</div>
211 <div>Lineitem ID: [% li.id %]</div>
212 <div>PO # : [% li.purchase_order %]</div>
213 <div>Est. Price: [% money(li.estimated_unit_price) %]</div>
214 <div>Open Holds: [% template_data.hold_count %]</div>
215 [% IF li.cancel_reason.label %]
216 <div>[% li.cancel_reason.label %]</div>
219 [% IF li.distribution_formulas.size > 0 %]
221 [% FOREACH form IN li.distribution_formulas; forms.push(form.formula.name); END %]
222 <div>Distribution Formulas: [% forms.join(',') %]</div>
225 [% IF li.lineitem_notes.size > 0 %]
228 [%- FOR note IN li.lineitem_notes -%]
230 [% IF note.alert_text %]
231 [% note.alert_text.code -%]
251 <th>Shelving Location</th>
254 <th>Delayed / Canceled</th>
258 <!-- set detail.owning_lib from fm object to org name -->
259 [% FOREACH detail IN li.lineitem_details %]
260 [% detail.owning_lib = detail.owning_lib.shortname %]
263 [% FOREACH detail IN li.lineitem_details.sort('owning_lib') %]
265 IF detail.eg_copy_id;
266 SET copy = detail.eg_copy_id;
267 SET cn_label = copy.call_number.label;
270 SET cn_label = detail.cn_label;
274 <!-- acq.lineitem_detail.id = [%- detail.id -%] -->
275 <td style='padding:5px;'>[% detail.owning_lib %]</td>
276 <td style='padding:5px;'>[% IF copy.barcode %]<span class="barcode" >[% detail.barcode %]</span>[% END %]</td>
277 <td style='padding:5px;'>[% IF cn_label %]<span class="cn_label" >[% cn_label %]</span>[% END %]</td>
278 <td style='padding:5px;'>[% IF detail.fund %]<span class="fund">[% detail.fund.code %] ([% detail.fund.year %])</span>[% END %]</td>
279 <td style='padding:5px;'>[% copy.location.name %]</td>
280 <td style='padding:5px;'>[% IF detail.recv_time %]<span class="recv_time">[% date.format(helpers.format_date(detail.recv_time, staff_org_timezone), '%x %r', locale) %]</span>[% END %]</td>
281 <td style='padding:5px;'>[% detail.note %]</td>
282 <td style='padding:5px;'>[% detail.cancel_reason.label %]</td>
291 INSERT INTO config.print_template
292 (id, name, label, owner, active, locale, template)
293 VALUES (6, 'purchase_order', 'Purchase Order', 1, TRUE, 'en-US',
299 USE money=format('%.2f');
300 SET po = template_data.po;
302 # find a lineitem attribute by name and optional type
304 FOR attr IN li.attributes;
305 IF attr.attr_name == attr_name;
306 IF !attr_type OR attr_type == attr.attr_type;
314 BLOCK get_li_order_attr_value;
315 FOR attr IN li.attributes;
316 IF attr.order_ident == 't';
324 <table style="width:100%">
329 <th>ISBN / Item # / Charge Type</th>
341 FOR li IN po.lineitems;
345 idval = PROCESS get_li_attr attr_name = 'vendor_num';
348 idval = PROCESS get_li_order_attr_value;
355 <td>[% PROCESS get_li_attr attr_name = 'title' %]</td>
356 <td>[% PROCESS get_li_attr attr_name = 'author' %]</td>
359 [% PROCESS get_li_attr attr_name = 'publisher' %],
360 [% PROCESS get_li_attr attr_name = 'pubdate' %]
362 <div>Edition: [% PROCESS get_li_attr attr_name = 'edition' %]</div>
365 SET count = li.lineitem_details.size;
366 SET price = li.estimated_unit_price;
367 SET itotal = (price * count);
370 <td>[% money(price) %]</td>
371 <td>[% money(litotal) %]</td>
387 SELECT evergreen.upgrade_deps_block_check('1334', :eg_version);
389 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
391 'eg.acq.picklist.upload.templates','acq','object',
393 'eg.acq.picklist.upload.templates',
394 'Acq Picklist Uploader Templates',
400 SELECT evergreen.upgrade_deps_block_check('1335', :eg_version);
402 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
404 'acq.lineitem.sort_order', 'gui', 'integer',
406 'acq.lineitem.sort_order',
407 'ACQ Lineitem List Sort Order',
412 INSERT INTO config.org_unit_setting_type (name, grp, datatype, label)
414 'ui.staff.acq.show_deprecated_links', 'gui', 'bool',
416 'ui.staff.acq.show_deprecated_links',
417 'Display Links to Deprecated Acquisitions Interfaces',
423 SELECT evergreen.upgrade_deps_block_check('1336', :eg_version);
425 INSERT INTO config.workstation_setting_type (name, grp, datatype, label)
427 'eg.grid.admin.actor.org_unit_settings', 'gui', 'object',
429 'eg.grid.admin.actor.org_unit_settings',
430 'Grid Config: admin.actor.org_unit_settings',
436 SELECT evergreen.upgrade_deps_block_check('1342', :eg_version);
438 INSERT INTO config.org_unit_setting_type
439 (name, label, datatype, description, grp, update_perm, view_perm)
441 'circ.permit_renew_when_exceeds_fines',
443 'circ.permit_renew_when_exceeds_fines',
444 'Permit renewals when patron exceeds max fine threshold',
450 'circ.permit_renew_when_exceeds_fines',
451 'Permit renewals even when the patron exceeds the maximum fine threshold',
460 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$
462 user_object actor.usr%ROWTYPE;
463 standing_penalty config.standing_penalty%ROWTYPE;
464 item_object asset.copy%ROWTYPE;
465 item_status_object config.copy_status%ROWTYPE;
466 item_location_object asset.copy_location%ROWTYPE;
467 result action.circ_matrix_test_result;
468 circ_test action.found_circ_matrix_matchpoint;
469 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
470 circ_limit_set config.circ_limit_set%ROWTYPE;
471 hold_ratio action.hold_stats%ROWTYPE;
474 context_org_list INT[];
478 -- Assume success unless we hit a failure condition
479 result.success := TRUE;
481 -- Need user info to look up matchpoints
482 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
484 -- (Insta)Fail if we couldn't find the user
485 IF user_object.id IS NULL THEN
486 result.fail_part := 'no_user';
487 result.success := FALSE;
493 -- Need item info to look up matchpoints
494 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
496 -- (Insta)Fail if we couldn't find the item
497 IF item_object.id IS NULL THEN
498 result.fail_part := 'no_item';
499 result.success := FALSE;
505 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
507 circ_matchpoint := circ_test.matchpoint;
508 result.matchpoint := circ_matchpoint.id;
509 result.circulate := circ_matchpoint.circulate;
510 result.duration_rule := circ_matchpoint.duration_rule;
511 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
512 result.max_fine_rule := circ_matchpoint.max_fine_rule;
513 result.hard_due_date := circ_matchpoint.hard_due_date;
514 result.renewals := circ_matchpoint.renewals;
515 result.grace_period := circ_matchpoint.grace_period;
516 result.buildrows := circ_test.buildrows;
518 -- (Insta)Fail if we couldn't find a matchpoint
519 IF circ_test.success = false THEN
520 result.fail_part := 'no_matchpoint';
521 result.success := FALSE;
527 -- All failures before this point are non-recoverable
528 -- Below this point are possibly overridable failures
530 -- Fail if the user is barred
531 IF user_object.barred IS TRUE THEN
532 result.fail_part := 'actor.usr.barred';
533 result.success := FALSE;
538 -- Fail if the item can't circulate
539 IF item_object.circulate IS FALSE THEN
540 result.fail_part := 'asset.copy.circulate';
541 result.success := FALSE;
546 -- Fail if the item isn't in a circulateable status on a non-renewal
547 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
548 result.fail_part := 'asset.copy.status';
549 result.success := FALSE;
552 -- Alternately, fail if the item isn't checked out on a renewal
553 ELSIF renewal AND item_object.status <> 1 THEN
554 result.fail_part := 'asset.copy.status';
555 result.success := FALSE;
560 -- Fail if the item can't circulate because of the shelving location
561 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
562 IF item_location_object.circulate IS FALSE THEN
563 result.fail_part := 'asset.copy_location.circulate';
564 result.success := FALSE;
569 -- Use Circ OU for penalties and such
570 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
573 penalty_type = '%RENEW%';
575 penalty_type = '%CIRC%';
578 FOR standing_penalty IN
579 SELECT DISTINCT csp.*
580 FROM actor.usr_standing_penalty usp
581 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
582 WHERE usr = match_user
583 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
584 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
585 AND csp.block_list LIKE penalty_type LOOP
587 -- override PATRON_EXCEEDS_FINES penalty for renewals based on org setting
588 IF renewal AND standing_penalty.name = 'PATRON_EXCEEDS_FINES' THEN
589 SELECT INTO permit_renew value FROM actor.org_unit_ancestor_setting('circ.permit_renew_when_exceeds_fines', circ_ou);
590 IF permit_renew IS NOT NULL AND permit_renew ILIKE 'true' THEN
595 result.fail_part := standing_penalty.name;
596 result.success := FALSE;
601 -- Fail if the test is set to hard non-circulating
602 IF circ_matchpoint.circulate IS FALSE THEN
603 result.fail_part := 'config.circ_matrix_test.circulate';
604 result.success := FALSE;
609 -- Fail if the total copy-hold ratio is too low
610 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
611 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
612 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
613 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
614 result.success := FALSE;
620 -- Fail if the available copy-hold ratio is too low
621 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
622 IF hold_ratio.hold_count IS NULL THEN
623 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
625 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
626 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
627 result.success := FALSE;
633 -- Fail if the user has too many items out by defined limit sets
634 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
635 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
636 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
637 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
639 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
640 SELECT INTO context_org_list ARRAY_AGG(aou.id)
641 FROM actor.org_unit_full_path( circ_ou ) aou
642 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
643 WHERE aout.depth >= circ_limit_set.depth;
644 IF circ_limit_set.global THEN
645 WITH RECURSIVE descendant_depth AS (
648 FROM actor.org_unit ou
649 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
653 FROM actor.org_unit ou
654 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
655 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
657 SELECT INTO items_out COUNT(DISTINCT circ.id)
658 FROM action.circulation circ
659 JOIN asset.copy copy ON (copy.id = circ.target_copy)
660 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
661 WHERE circ.usr = match_user
662 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
663 AND circ.checkin_time IS NULL
664 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
665 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
666 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
667 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
669 IF items_out >= circ_limit_set.items_out THEN
670 result.fail_part := 'config.circ_matrix_circ_mod_test';
671 result.success := FALSE;
676 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;
679 -- If we passed everything, return the successful matchpoint
686 $func$ LANGUAGE plpgsql;
690 SELECT evergreen.upgrade_deps_block_check('1343', :eg_version);
692 ALTER TABLE actor.hours_of_operation
693 ADD COLUMN dow_0_note TEXT,
694 ADD COLUMN dow_1_note TEXT,
695 ADD COLUMN dow_2_note TEXT,
696 ADD COLUMN dow_3_note TEXT,
697 ADD COLUMN dow_4_note TEXT,
698 ADD COLUMN dow_5_note TEXT,
699 ADD COLUMN dow_6_note TEXT;
701 SELECT evergreen.upgrade_deps_block_check('1344', :eg_version);
703 -- This function is used to help clean up facet labels. Due to quirks in
704 -- MARC parsing, some facet labels may be generated with periods or commas
705 -- at the end. This will strip a trailing commas off all the time, and
706 -- periods when they don't look like they are part of initials or dotted
708 -- Smith, John => no change
709 -- Smith, John, => Smith, John
710 -- Smith, John. => Smith, John
711 -- Public, John Q. => no change
712 -- Public, John, Ph.D. => no change
713 -- Atlanta -- Georgia -- U.S. => no change
714 -- Atlanta -- Georgia. => Atlanta, Georgia
715 -- The fellowship of the rings / => The fellowship of the rings
716 -- Some title ; => Some title
717 CREATE OR REPLACE FUNCTION metabib.trim_trailing_punctuation ( TEXT ) RETURNS TEXT AS $$
723 last_char = substring(result from '.$');
725 IF last_char = ',' THEN
726 result := substring(result from '^(.*),$');
728 ELSIF last_char = '.' THEN
729 -- must have a single word-character following at least one non-word character
730 IF substring(result from '\W\w\.$') IS NULL THEN
731 result := substring(result from '^(.*)\.$');
734 ELSIF last_char IN ('/',':',';','=') THEN -- Dangling subtitle/SoR separator
735 IF substring(result from ' .$') IS NOT NULL THEN -- must have a space before last_char
736 result := substring(result from '^(.*) .$');
743 $$ language 'plpgsql';
746 INSERT INTO config.metabib_field_index_norm_map (field,norm,pos)
750 FROM config.metabib_field m,
751 config.index_normalizer i
752 WHERE i.func = 'metabib.trim_trailing_punctuation'
753 AND m.field_class='title' AND (m.browse_field OR m.facet_field OR m.display_field)
754 AND NOT EXISTS (SELECT 1 FROM config.metabib_field_index_norm_map WHERE field = m.id AND norm = i.id);
757 SELECT evergreen.upgrade_deps_block_check('1345', :eg_version);
759 CREATE TABLE acq.shipment_notification (
760 id SERIAL PRIMARY KEY,
761 receiver INT NOT NULL REFERENCES actor.org_unit (id),
762 provider INT NOT NULL REFERENCES acq.provider (id),
763 shipper INT NOT NULL REFERENCES acq.provider (id),
764 recv_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
765 recv_method TEXT NOT NULL REFERENCES acq.invoice_method (code) DEFAULT 'EDI',
766 process_date TIMESTAMPTZ,
767 processed_by INT REFERENCES actor.usr(id) ON DELETE SET NULL,
768 container_code TEXT NOT NULL, -- vendor-supplied super-barcode
769 lading_number TEXT, -- informational
771 CONSTRAINT container_code_once_per_provider UNIQUE(provider, container_code)
774 CREATE INDEX acq_asn_container_code_idx ON acq.shipment_notification (container_code);
776 CREATE TABLE acq.shipment_notification_entry (
777 id SERIAL PRIMARY KEY,
778 shipment_notification INT NOT NULL REFERENCES acq.shipment_notification (id)
780 lineitem INT REFERENCES acq.lineitem (id)
781 ON UPDATE CASCADE ON DELETE SET NULL,
782 item_count INT NOT NULL -- How many items the provider shipped
785 /* TODO alter valid_message_type constraint */
787 ALTER TABLE acq.edi_message DROP CONSTRAINT valid_message_type;
788 ALTER TABLE acq.edi_message ADD CONSTRAINT valid_message_type
803 DELETE FROM acq.edi_message WHERE message_type = 'DESADV';
805 DELETE FROM acq.shipment_notification_entry;
806 DELETE FROM acq.shipment_notification;
808 ALTER TABLE acq.edi_message DROP CONSTRAINT valid_message_type;
809 ALTER TABLE acq.edi_message ADD CONSTRAINT valid_message_type
820 DROP TABLE acq.shipment_notification_entry;
821 DROP TABLE acq.shipment_notification;
826 SELECT evergreen.upgrade_deps_block_check('1346', :eg_version);
828 -- insert then update for easier iterative development tweaks
829 INSERT INTO config.print_template
830 (name, label, owner, active, locale, content_type, template)
831 VALUES ('items_out', 'Patron Items Out', 1, TRUE, 'en-US', 'text/html', '');
833 UPDATE config.print_template SET template = $TEMPLATE$
836 circulations = template_data.circulations;
839 <div>Welcome to [% staff_org.name %]</div>
840 <div>You have the following items:</div>
843 [% FOR checkout IN circulations %]
845 <div>[% checkout.title %]</div>
847 [% IF checkout.copy %]Barcode: [% checkout.copy.barcode %][% END %]
848 Due: [% date.format(helpers.format_date(checkout.dueDate, staff_org_timezone), '%x %r') %]
854 <div>[% staff_org.name %] [% date.format(date.now, '%x %r') %]</div>
855 <div>You were helped by [% staff.first_given_name %]</div>
858 $TEMPLATE$ WHERE name = 'items_out';
860 UPDATE config.print_template SET active = TRUE WHERE name = 'patron_address';
862 -- insert then update for easier iterative development tweaks
863 INSERT INTO config.print_template
864 (name, label, owner, active, locale, content_type, template)
865 VALUES ('bills_current', 'Bills, Current', 1, TRUE, 'en-US', 'text/html', '');
868 UPDATE config.print_template SET template = $TEMPLATE$
871 USE money = format('$%.2f');
872 SET xacts = template_data.xacts;
875 <style>td { padding: 1px 3px 1px 3px; }</style>
876 <div>Welcome to [% staff_org.name %]</div>
877 <div>You have the following bills:</div>
880 [% FOR xact IN xacts %]
885 <td>[% xact.id %]</td>
889 <td>[% date.format(helpers.format_date(
890 xact.xact_start, staff_org_timezone), '%x %r') %]
894 <td>Last Billing:</td>
895 <td>[% xact.last_billing_type %]</td>
898 <td>Total Billed:</td>
899 <td>[% money(xact.total_owed) %]</td>
902 <td>Last Payment:</td>
904 [% xact.last_payment_type %]
905 [% IF xact.last_payment_ts %]
908 xact.last_payment_ts, staff_org_timezone), '%x %r') %]
914 <td>[% money(xact.total_paid) %]</td>
918 <td>[% money(xact.balance_owed) %]</td>
925 <div>[% staff_org.name %] [% date.format(date.now, '%x %r') %]</div>
926 <div>You were helped by [% staff.first_given_name %]</div>
929 $TEMPLATE$ WHERE name = 'bills_current';
932 INSERT INTO config.print_template
933 (name, label, owner, active, locale, content_type, template)
934 VALUES ('bills_payment', 'Bills, Payment', 1, TRUE, 'en-US', 'text/html', '');
936 UPDATE config.print_template SET template = $TEMPLATE$
939 USE money = format('$%.2f');
940 SET payments = template_data.payments;
941 SET previous_balance = template_data.previous_balance;
942 SET new_balance = template_data.new_balance;
943 SET payment_type = template_data.payment_type;
944 SET payment_total = template_data.payment_total;
945 SET payment_applied = template_data.payment_applied;
946 SET amount_voided = template_data.amount_voided;
947 SET change_given = template_data.change_given;
948 SET payment_note = template_data.payment_note;
949 SET copy_barcode = template_data.copy_barcode;
950 SET title = template_data.title;
953 <style>td { padding: 1px 3px 1px 3px; }</style>
954 <div>Welcome to [% staff_org.name %]</div>
955 <div>A receipt of your transaction:</div>
958 <table style="width:100%">
960 <td>Original Balance:</td>
961 <td align="right">[% money(previous_balance) %]</td>
964 <td>Payment Method:</td>
966 [% SWITCH payment_type %]
967 [% CASE "cash_payment" %]Cash
968 [% CASE "check_payment" %]Check
969 [% CASE "credit_card_payment" %]Credit Card
970 [% CASE "debit_card_payment" %]Debit Card
971 [% CASE "credit_payment" %]Patron Credit
972 [% CASE "work_payment" %]Work
973 [% CASE "forgive_payment" %]Forgive
974 [% CASE "goods_payment" %]Goods
979 <td>Payment Received:</td>
980 <td align="right">[% money(payment_total) %]</td>
983 <td>Payment Applied:</td>
984 <td align="right">[% money(payment_applied) %]</td>
987 <td>Billings Voided:</td>
988 <td align="right">[% money(amount_voided) %]</td>
991 <td>Change Given:</td>
992 <td align="right">[% money(change_given) %]</td>
995 <td>New Balance:</td>
996 <td align="right">[% money(new_balance) %]</td>
999 <p>Note: [% payment_note %]</p>
1003 [% FOR payment IN payments %]
1004 <table style="width:100%">
1006 <td>Bill # [% payment.xact.id %]</td>
1007 <td>[% payment.xact.summary.last_billing_type %]</td>
1008 <td>Received: [% money(payment.amount) %]</td>
1010 [% IF payment.copy_barcode %]
1012 <td colspan="5">[% payment.copy_barcode %] [% payment.title %]</td>
1022 <div>[% staff_org.name %] [% date.format(date.now, '%x %r') %]</div>
1023 <div>You were helped by [% staff.first_given_name %]</div>
1025 $TEMPLATE$ WHERE name = 'bills_payment';
1028 INSERT INTO config.print_template
1029 (name, label, owner, active, locale, content_type, template)
1030 VALUES ('patron_data', 'Patron Data', 1, TRUE, 'en-US', 'text/html', '');
1032 UPDATE config.print_template SET template = $TEMPLATE$
1035 USE money = format('$%.2f');
1036 SET patron = template_data.patron;
1039 <tr><td>Barcode:</td><td>[% patron.card.barcode %]</td></tr>
1040 <tr><td>Patron's Username:</td><td>[% patron.usrname %]</td></tr>
1041 <tr><td>Prefix/Title:</td><td>[% patron.prefix %]</td></tr>
1042 <tr><td>First Name:</td><td>[% patron.first_given_name %]</td></tr>
1043 <tr><td>Middle Name:</td><td>[% patron.second_given_name %]</td></tr>
1044 <tr><td>Last Name:</td><td>[% patron.family_name %]</td></tr>
1045 <tr><td>Suffix:</td><td>[% patron.suffix %]</td></tr>
1046 <tr><td>Holds Alias:</td><td>[% patron.alias %]</td></tr>
1047 <tr><td>Date of Birth:</td><td>[% patron.dob %]</td></tr>
1048 <tr><td>Juvenile:</td><td>[% patron.juvenile %]</td></tr>
1049 <tr><td>Primary Identification Type:</td><td>[% patron.ident_type.name %]</td></tr>
1050 <tr><td>Primary Identification:</td><td>[% patron.ident_value %]</td></tr>
1051 <tr><td>Secondary Identification Type:</td><td>[% patron.ident_type2.name %]</td></tr>
1052 <tr><td>Secondary Identification:</td><td>[% patron.ident_value2 %]</td></tr>
1053 <tr><td>Email Address:</td><td>[% patron.email %]</td></tr>
1054 <tr><td>Daytime Phone:</td><td>[% patron.day_phone %]</td></tr>
1055 <tr><td>Evening Phone:</td><td>[% patron.evening_phone %]</td></tr>
1056 <tr><td>Other Phone:</td><td>[% patron.other_phone %]</td></tr>
1057 <tr><td>Home Library:</td><td>[% patron.home_ou.name %]</td></tr>
1058 <tr><td>Main (Profile) Permission Group:</td><td>[% patron.profile.name %]</td></tr>
1059 <tr><td>Privilege Expiration Date:</td><td>[% patron.expire_date %]</td></tr>
1060 <tr><td>Internet Access Level:</td><td>[% patron.net_access_level.name %]</td></tr>
1061 <tr><td>Active:</td><td>[% patron.active %]</td></tr>
1062 <tr><td>Barred:</td><td>[% patron.barred %]</td></tr>
1063 <tr><td>Is Group Lead Account:</td><td>[% patron.master_account %]</td></tr>
1064 <tr><td>Claims-Returned Count:</td><td>[% patron.claims_returned_count %]</td></tr>
1065 <tr><td>Claims-Never-Checked-Out Count:</td><td>[% patron.claims_never_checked_out_count %]</td></tr>
1066 <tr><td>Alert Message:</td><td>[% patron.alert_message %]</td></tr>
1068 [% FOR addr IN patron.addresses %]
1069 <tr><td colspan="2">----------</td></tr>
1070 <tr><td>Type:</td><td>[% addr.address_type %]</td></tr>
1071 <tr><td>Street (1):</td><td>[% addr.street1 %]</td></tr>
1072 <tr><td>Street (2):</td><td>[% addr.street2 %]</td></tr>
1073 <tr><td>City:</td><td>[% addr.city %]</td></tr>
1074 <tr><td>County:</td><td>[% addr.county %]</td></tr>
1075 <tr><td>State:</td><td>[% addr.state %]</td></tr>
1076 <tr><td>Postal Code:</td><td>[% addr.post_code %]</td></tr>
1077 <tr><td>Country:</td><td>[% addr.country %]</td></tr>
1078 <tr><td>Valid Address?:</td><td>[% addr.valid %]</td></tr>
1079 <tr><td>Within City Limits?:</td><td>[% addr.within_city_limits %]</td></tr>
1082 [% FOR entry IN patron.stat_cat_entries %]
1083 <tr><td>-----------</td></tr>
1084 <tr><td>[% entry.stat_cat.name %]</td><td>[% entry.stat_cat_entry %]</td></tr>
1089 $TEMPLATE$ WHERE name = 'patron_data';
1092 INSERT INTO config.print_template
1093 (name, label, owner, active, locale, content_type, template)
1094 VALUES ('hold_shelf_slip', 'Hold Shelf Slip', 1, TRUE, 'en-US', 'text/html', '');
1097 UPDATE config.print_template SET template = $TEMPLATE$
1100 USE money = format('$%.2f');
1101 SET copy = template_data.checkin.copy;
1102 SET hold = template_data.checkin.hold;
1103 SET volume = template_data.checkin.volume;
1104 SET hold = template_data.checkin.hold;
1105 SET record = template_data.checkin.record;
1106 SET patron = template_data.checkin.patron;
1110 [% IF hold.behind_desk == 't' %]
1111 This item needs to be routed to the <strong>Private Holds Shelf</strong>.
1113 This item needs to be routed to the <strong>Public Holds Shelf</strong>.
1118 <div>Barcode: [% copy.barcode %]</div>
1119 <div>Title: [% checkin.title %]</div>
1120 <div>Call Number: [% volume.prefix.label %] [% volume.label %] [% volume.suffix.label %]</div>
1124 <div>Hold for patron: [% patron.family_name %],
1125 [% patron.first_given_name %] [% patron.second_given_name %]</div>
1126 <div>Barcode: [% patron.card.barcode %]</div>
1128 [% IF hold.phone_notify %]
1129 <div>Notify by phone: [% hold.phone_notify %]</div>
1131 [% IF hold.sms_notify %]
1132 <div>Notify by text: [% hold.sms_notify %]</div>
1134 [% IF hold.email_notify %]
1135 <div>Notify by email: [% patron.email %]</div>
1138 [% FOR note IN hold.notes %]
1140 [% IF note.slip == 't' %]
1141 <li><strong>[% note.title %]</strong> - [% note.body %]</li>
1147 <div>Request Date: [%
1148 date.format(helpers.format_date(hold.request_time, staff_org_timezone), '%x %r') %]</div>
1149 <div>Slip Date: [% date.format(date.now, '%x %r') %]</div>
1150 <div>Printed by [% staff.first_given_name %] at [% staff_org.shortname %]</div>
1154 $TEMPLATE$ WHERE name = 'hold_shelf_slip';
1157 INSERT INTO config.print_template
1158 (name, label, owner, active, locale, content_type, template)
1159 VALUES ('transit_slip', 'Transit Slip', 1, TRUE, 'en-US', 'text/html', '');
1162 UPDATE config.print_template SET template = $TEMPLATE$
1165 USE money = format('$%.2f');
1166 SET checkin = template_data.checkin;
1167 SET copy = checkin.copy;
1168 SET destOrg = checkin.destOrg;
1169 SET destAddress = checkin.destAddress;
1170 SET destCourierCode = checkin.destCourierCode;
1173 <div>This item needs to be routed to <b>[% destOrg.shortname %]</b></div>
1174 <div>[% destOrg.name %]</div>
1175 [% IF destCourierCode %]Courier Code: [% destCourierCode %][% END %]
1177 [% IF destAddress %]
1178 <div>[% destAddress.street1 %]</div>
1179 <div>[% destAddress.street2 %]</div>
1180 <div>[% destAddress.city %],
1181 [% destAddress.state %]
1182 [% destAddress.post_code %]</div>
1184 <div>We do not have a holds address for this library.</div>
1188 <div>Barcode: [% copy.barcode %]</div>
1189 <div>Title: [% checkin.title %]</div>
1190 <div>Author: [% checkin.author %]</div>
1193 <div>Slip Date: [% date.format(date.now, '%x %r') %]</div>
1194 <div>Printed by [% staff.first_given_name %] at [% staff_org.shortname %]</div>
1197 $TEMPLATE$ WHERE name = 'transit_slip';
1200 INSERT INTO config.print_template
1201 (name, label, owner, active, locale, content_type, template)
1202 VALUES ('hold_transit_slip', 'Hold Transit Slip', 1, TRUE, 'en-US', 'text/html', '');
1204 UPDATE config.print_template SET template = $TEMPLATE$
1207 USE money = format('$%.2f');
1208 SET checkin = template_data.checkin;
1209 SET copy = checkin.copy;
1210 SET hold = checkin.hold;
1211 SET patron = checkin.patron;
1212 SET destOrg = checkin.destOrg;
1213 SET destAddress = checkin.destAddress;
1214 SET destCourierCode = checkin.destCourierCode;
1217 <div>This item needs to be routed to <b>[% destOrg.shortname %]</b></div>
1218 <div>[% destOrg.name %]</div>
1219 [% IF destCourierCode %]Courier Code: [% destCourierCode %][% END %]
1221 [% IF destAddress %]
1222 <div>[% destAddress.street1 %]</div>
1223 <div>[% destAddress.street2 %]</div>
1224 <div>[% destAddress.city %],
1225 [% destAddress.state %]
1226 [% destAddress.post_code %]</div>
1228 <div>We do not have a holds address for this library.</div>
1232 <div>Barcode: [% copy.barcode %]</div>
1233 <div>Title: [% checkin.title %]</div>
1234 <div>Author: [% checkin.author %]</div>
1237 <div>Hold for patron [% patron.card.barcode %]</div>
1240 <div>Request Date: [%
1241 date.format(helpers.format_date(hold.request_time, staff_org_timezone), '%x %r') %]
1243 <div>Slip Date: [% date.format(date.now, '%x %r') %]</div>
1244 <div>Printed by [% staff.first_given_name %] at [% staff_org.shortname %]</div>
1247 $TEMPLATE$ WHERE name = 'transit_slip';
1249 INSERT INTO config.print_template
1250 (name, label, owner, active, locale, content_type, template)
1251 VALUES ('checkin', 'Checkin', 1, TRUE, 'en-US', 'text/html', '');
1253 UPDATE config.print_template SET template = $TEMPLATE$
1256 USE money = format('$%.2f');
1257 SET checkins = template_data.checkins;
1261 <div>Welcome to [% staff_org.name %]</div>
1262 <div>You checked in the following items:</div>
1265 [% FOR checkin IN checkins %]
1267 <div>[% checkin.title %]</div>
1268 <span>Barcode: </span>
1269 <span>[% checkin.copy.barcode %]</span>
1270 <span>Call Number: </span>
1272 [% IF checkin.volume %]
1273 [% volume.prefix.label %] [% volume.label %] [% volume.suffix.label %]
1282 <div>Slip Date: [% date.format(date.now, '%x %r') %]</div>
1283 <div>Printed by [% staff.first_given_name %] at [% staff_org.shortname %]</div>
1286 $TEMPLATE$ WHERE name = 'checkin';
1289 INSERT INTO config.print_template
1290 (name, label, owner, active, locale, content_type, template)
1291 VALUES ('holds_for_patron', 'Holds For Patron', 1, TRUE, 'en-US', 'text/html', '');
1294 UPDATE config.print_template SET template = $TEMPLATE$
1297 USE money = format('$%.2f');
1298 SET holds = template_data;
1302 <div>Welcome to [% staff_org.name %]</div>
1303 <div>You have the following items on hold:</div>
1306 [% FOR hold IN holds %]
1308 <div>[% hold.title %]</div>
1313 <div>Slip Date: [% date.format(date.now, '%x %r') %]</div>
1314 <div>Printed by [% staff.first_given_name %] at [% staff_org.shortname %]</div>
1317 $TEMPLATE$ WHERE name = 'holds_for_patron';
1320 INSERT INTO config.print_template
1321 (name, label, owner, active, locale, content_type, template)
1322 VALUES ('bills_historical', 'Bills, Historical', 1, TRUE, 'en-US', 'text/html', '');
1325 UPDATE config.print_template SET template = $TEMPLATE$
1328 USE money = format('$%.2f');
1329 SET xacts = template_data.xacts;
1332 <style>td { padding: 1px 3px 1px 3px; }</style>
1333 <div>Welcome to [% staff_org.name %]</div>
1334 <div>You have the following bills:</div>
1337 [% FOR xact IN xacts %]
1342 <td>[% xact.id %]</td>
1346 <td>[% date.format(helpers.format_date(
1347 xact.xact_start, staff_org_timezone), '%x %r') %]
1351 <td>Last Billing:</td>
1352 <td>[% xact.last_billing_type %]</td>
1355 <td>Total Billed:</td>
1356 <td>[% money(xact.total_owed) %]</td>
1359 <td>Last Payment:</td>
1361 [% xact.last_payment_type %]
1362 [% IF xact.last_payment_ts %]
1364 helpers.format_date(
1365 xact.last_payment_ts, staff_org_timezone), '%x %r') %]
1370 <td>Total Paid:</td>
1371 <td>[% money(xact.total_paid) %]</td>
1375 <td>[% money(xact.balance_owed) %]</td>
1382 <div>[% staff_org.name %] [% date.format(date.now, '%x %r') %]</div>
1383 <div>You were helped by [% staff.first_given_name %]</div>
1386 $TEMPLATE$ WHERE name = 'bills_historical';
1388 INSERT INTO config.print_template
1389 (name, label, owner, active, locale, content_type, template)
1390 VALUES ('checkout', 'Checkout', 1, TRUE, 'en-US', 'text/html', '');
1392 UPDATE config.print_template SET template = $TEMPLATE$
1395 USE money = format('$%.2f');
1396 SET checkouts = template_data.checkouts;
1400 <div>Welcome to [% staff_org.name %]</div>
1401 <div>You checked out the following items:</div>
1404 [% FOR checkout IN checkouts %]
1406 <div>[% checkout.title %]</div>
1407 <span>Barcode: </span>
1408 <span>[% checkout.copy.barcode %]</span>
1409 <span>Call Number: </span>
1411 [% IF checkout.volume %]
1412 [% volume.prefix.label %] [% volume.label %] [% volume.suffix.label %]
1421 <div>Slip Date: [% date.format(date.now, '%x %r') %]</div>
1422 <div>Printed by [% staff.first_given_name %] at [% staff_org.shortname %]</div>
1425 $TEMPLATE$ WHERE name = 'checkout';
1427 INSERT INTO config.print_template
1428 (name, label, owner, active, locale, content_type, template)
1429 VALUES ('renew', 'renew', 1, TRUE, 'en-US', 'text/html', '');
1431 UPDATE config.print_template SET template = $TEMPLATE$
1434 USE money = format('$%.2f');
1435 SET renewals = template_data.renewals;
1439 <div>Welcome to [% staff_org.name %]</div>
1440 <div>You renewed the following items:</div>
1443 [% FOR renewal IN renewals %]
1445 <div>[% renewal.title %]</div>
1446 <span>Barcode: </span>
1447 <span>[% renewal.copy.barcode %]</span>
1448 <span>Call Number: </span>
1450 [% IF renewal.volume %]
1451 [% volume.prefix.label %] [% volume.label %] [% volume.suffix.label %]
1460 <div>Slip Date: [% date.format(date.now, '%x %r') %]</div>
1461 <div>Printed by [% staff.first_given_name %] at [% staff_org.shortname %]</div>
1464 $TEMPLATE$ WHERE name = 'renew';
1466 INSERT INTO config.org_unit_setting_type (name, grp, datatype, label, description)
1468 'ui.staff.angular_circ.enabled', 'gui', 'bool',
1470 'ui.staff.angular_circ.enabled',
1471 'Enable Angular Circulation Menu',
1475 'ui.staff.angular_circ.enabled',
1476 'Enable Angular Circulation Menu',
1477 'coust', 'description'
1481 INSERT INTO permission.perm_list ( id, code, description ) VALUES
1482 ( 640, 'ACCESS_ANGULAR_CIRC', oils_i18n_gettext(640,
1483 'Allow a user to access the experimental Angular circulation interfaces', 'ppl', 'description'))
1490 SELECT evergreen.upgrade_deps_block_check('1347', :eg_version);
1492 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
1494 specified_dest_usr IN INTEGER
1495 ) RETURNS VOID AS $$
1498 renamable_row RECORD;
1502 IF specified_dest_usr IS NULL THEN
1503 dest_usr := 1; -- Admin user on stock installs
1505 dest_usr := specified_dest_usr;
1508 -- action_trigger.event (even doing this, event_output may--and probably does--contain PII and should have a retention/removal policy)
1509 UPDATE action_trigger.event SET context_user = dest_usr WHERE context_user = src_usr;
1512 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
1513 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
1514 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
1515 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
1516 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
1517 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
1518 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
1519 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
1521 -- Update with a rename to avoid collisions
1522 FOR renamable_row in
1525 WHERE owner = src_usr
1527 suffix := ' (' || src_usr || ')';
1531 SET owner = dest_usr, name = name || suffix
1532 WHERE id = renamable_row.id;
1533 EXCEPTION WHEN unique_violation THEN
1534 suffix := suffix || ' ';
1541 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
1542 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
1543 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
1544 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
1545 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
1546 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
1547 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
1548 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
1551 DELETE FROM action.circulation WHERE usr = src_usr;
1552 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
1553 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
1554 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
1555 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
1556 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
1557 DELETE FROM action.hold_request WHERE usr = src_usr;
1558 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
1559 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
1560 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
1561 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
1562 DELETE FROM action.survey_response WHERE usr = src_usr;
1563 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
1564 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
1565 UPDATE action.curbside SET notes = NULL WHERE patron = src_usr;
1568 DELETE FROM actor.card WHERE usr = src_usr;
1569 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
1570 DELETE FROM actor.usr_privacy_waiver WHERE usr = src_usr;
1571 DELETE FROM actor.usr_message WHERE usr = src_usr;
1573 -- The following update is intended to avoid transient violations of a foreign
1574 -- key constraint, whereby actor.usr_address references itself. It may not be
1575 -- necessary, but it does no harm.
1576 UPDATE actor.usr_address SET replaces = NULL
1577 WHERE usr = src_usr AND replaces IS NOT NULL;
1578 DELETE FROM actor.usr_address WHERE usr = src_usr;
1579 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
1580 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
1581 DELETE FROM actor.usr_setting WHERE usr = src_usr;
1582 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
1583 UPDATE actor.usr_message SET title = 'purged', message = 'purged', read_date = NOW() WHERE usr = src_usr;
1584 DELETE FROM actor.usr_message WHERE usr = src_usr;
1585 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
1586 UPDATE actor.usr_message SET editor = dest_usr WHERE editor = src_usr;
1589 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
1590 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
1591 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
1592 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
1593 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
1594 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
1597 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
1598 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
1599 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
1600 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
1601 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
1602 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
1603 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
1604 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
1607 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
1608 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
1609 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
1610 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
1613 -- Update buckets with a rename to avoid collisions
1614 FOR renamable_row in
1616 FROM container.biblio_record_entry_bucket
1617 WHERE owner = src_usr
1619 suffix := ' (' || src_usr || ')';
1622 UPDATE container.biblio_record_entry_bucket
1623 SET owner = dest_usr, name = name || suffix
1624 WHERE id = renamable_row.id;
1625 EXCEPTION WHEN unique_violation THEN
1626 suffix := suffix || ' ';
1633 FOR renamable_row in
1635 FROM container.call_number_bucket
1636 WHERE owner = src_usr
1638 suffix := ' (' || src_usr || ')';
1641 UPDATE container.call_number_bucket
1642 SET owner = dest_usr, name = name || suffix
1643 WHERE id = renamable_row.id;
1644 EXCEPTION WHEN unique_violation THEN
1645 suffix := suffix || ' ';
1652 FOR renamable_row in
1654 FROM container.copy_bucket
1655 WHERE owner = src_usr
1657 suffix := ' (' || src_usr || ')';
1660 UPDATE container.copy_bucket
1661 SET owner = dest_usr, name = name || suffix
1662 WHERE id = renamable_row.id;
1663 EXCEPTION WHEN unique_violation THEN
1664 suffix := suffix || ' ';
1671 FOR renamable_row in
1673 FROM container.user_bucket
1674 WHERE owner = src_usr
1676 suffix := ' (' || src_usr || ')';
1679 UPDATE container.user_bucket
1680 SET owner = dest_usr, name = name || suffix
1681 WHERE id = renamable_row.id;
1682 EXCEPTION WHEN unique_violation THEN
1683 suffix := suffix || ' ';
1690 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
1693 DELETE FROM money.billable_xact WHERE usr = src_usr;
1694 DELETE FROM money.collections_tracker WHERE usr = src_usr;
1695 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
1698 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
1699 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
1700 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
1701 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
1704 -- Update with a rename to avoid collisions
1706 FOR renamable_row in
1708 FROM reporter.output_folder
1709 WHERE owner = src_usr
1711 suffix := ' (' || src_usr || ')';
1714 UPDATE reporter.output_folder
1715 SET owner = dest_usr, name = name || suffix
1716 WHERE id = renamable_row.id;
1717 EXCEPTION WHEN unique_violation THEN
1718 suffix := suffix || ' ';
1724 EXCEPTION WHEN undefined_table THEN
1729 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
1730 EXCEPTION WHEN undefined_table THEN
1734 -- Update with a rename to avoid collisions
1736 FOR renamable_row in
1738 FROM reporter.report_folder
1739 WHERE owner = src_usr
1741 suffix := ' (' || src_usr || ')';
1744 UPDATE reporter.report_folder
1745 SET owner = dest_usr, name = name || suffix
1746 WHERE id = renamable_row.id;
1747 EXCEPTION WHEN unique_violation THEN
1748 suffix := suffix || ' ';
1754 EXCEPTION WHEN undefined_table THEN
1759 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
1760 EXCEPTION WHEN undefined_table THEN
1765 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
1766 EXCEPTION WHEN undefined_table THEN
1770 -- Update with a rename to avoid collisions
1772 FOR renamable_row in
1774 FROM reporter.template_folder
1775 WHERE owner = src_usr
1777 suffix := ' (' || src_usr || ')';
1780 UPDATE reporter.template_folder
1781 SET owner = dest_usr, name = name || suffix
1782 WHERE id = renamable_row.id;
1783 EXCEPTION WHEN unique_violation THEN
1784 suffix := suffix || ' ';
1790 EXCEPTION WHEN undefined_table THEN
1795 -- Update with a rename to avoid collisions
1796 FOR renamable_row in
1799 WHERE owner = src_usr
1801 suffix := ' (' || src_usr || ')';
1804 UPDATE vandelay.queue
1805 SET owner = dest_usr, name = name || suffix
1806 WHERE id = renamable_row.id;
1807 EXCEPTION WHEN unique_violation THEN
1808 suffix := suffix || ' ';
1815 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
1817 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
1818 -- can access the information before deletion.
1819 UPDATE actor.usr SET
1822 mailing_address = NULL,
1823 billing_address = NULL
1827 $$ LANGUAGE plpgsql;
1830 SELECT evergreen.upgrade_deps_block_check('1348', :eg_version);
1832 ALTER TABLE config.circ_matrix_matchpoint
1833 ADD COLUMN renew_extends_due_date BOOLEAN NOT NULL DEFAULT FALSE,
1834 ADD COLUMN renew_extend_min_interval INTERVAL;
1837 SELECT evergreen.upgrade_deps_block_check('1349', :eg_version);
1839 UPDATE config.org_unit_setting_type
1840 SET label = 'Rollover encumbrances only',
1841 description = 'Rollover encumbrances only when doing fiscal year end. This makes money left in the old fund disappear, modeling its return to some outside entity.'
1842 WHERE name = 'acq.fund.allow_rollover_without_money'
1843 AND label = 'Allow funds to be rolled over without bringing the money along'
1844 AND description = 'Allow funds to be rolled over without bringing the money along. This makes money left in the old fund disappear, modeling its return to some outside entity.';
1847 SELECT evergreen.upgrade_deps_block_check('1350', :eg_version);
1849 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
1851 moved_objects INT := 0;
1852 source_cn asset.call_number%ROWTYPE;
1853 target_cn asset.call_number%ROWTYPE;
1854 metarec metabib.metarecord%ROWTYPE;
1855 hold action.hold_request%ROWTYPE;
1856 ser_rec serial.record_entry%ROWTYPE;
1857 ser_sub serial.subscription%ROWTYPE;
1858 acq_lineitem acq.lineitem%ROWTYPE;
1859 acq_request acq.user_request%ROWTYPE;
1860 booking booking.resource_type%ROWTYPE;
1861 source_part biblio.monograph_part%ROWTYPE;
1862 target_part biblio.monograph_part%ROWTYPE;
1863 multi_home biblio.peer_bib_copy_map%ROWTYPE;
1867 uri_text TEXT := '';
1870 -- we don't merge bib -1
1871 IF target_record = -1 OR source_record = -1 THEN
1875 -- move any 856 entries on records that have at least one MARC-mapped URI entry
1876 SELECT INTO uri_count COUNT(*)
1877 FROM asset.uri_call_number_map m
1878 JOIN asset.call_number cn ON (m.call_number = cn.id)
1879 WHERE cn.record = source_record;
1881 IF uri_count > 0 THEN
1883 -- This returns more nodes than you might expect:
1884 -- 7 instead of 1 for an 856 with $u $y $9
1885 SELECT COUNT(*) INTO counter
1886 FROM oils_xpath_table(
1889 'biblio.record_entry',
1891 'id=' || source_record
1892 ) as t(i int,c text);
1894 FOR i IN 1 .. counter LOOP
1895 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
1897 ' ind1="' || FIRST(ind1) || '"' ||
1898 ' ind2="' || FIRST(ind2) || '">' ||
1900 '<subfield code="' || subfield || '">' ||
1903 regexp_replace(data,'&','&','g'),
1907 ) || '</subfield>', ''
1908 ) || '</datafield>' INTO uri_datafield
1909 FROM oils_xpath_table(
1912 'biblio.record_entry',
1913 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
1914 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
1915 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
1916 '//*[@tag="856"][position()=' || i || ']/*[@code]',
1917 'id=' || source_record
1918 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
1920 -- As most of the results will be NULL, protect against NULLifying
1921 -- the valid content that we do generate
1922 uri_text := uri_text || COALESCE(uri_datafield, '');
1925 IF uri_text <> '' THEN
1926 UPDATE biblio.record_entry
1927 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
1928 WHERE id = target_record;
1933 -- Find and move metarecords to the target record
1934 SELECT INTO metarec *
1935 FROM metabib.metarecord
1936 WHERE master_record = source_record;
1939 UPDATE metabib.metarecord
1940 SET master_record = target_record,
1942 WHERE id = metarec.id;
1944 moved_objects := moved_objects + 1;
1947 -- Find call numbers attached to the source ...
1948 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
1950 SELECT INTO target_cn *
1951 FROM asset.call_number
1952 WHERE label = source_cn.label
1953 AND prefix = source_cn.prefix
1954 AND suffix = source_cn.suffix
1955 AND owning_lib = source_cn.owning_lib
1956 AND record = target_record
1959 -- ... and if there's a conflicting one on the target ...
1962 -- ... move the copies to that, and ...
1964 SET call_number = target_cn.id
1965 WHERE call_number = source_cn.id;
1967 -- ... move V holds to the move-target call number
1968 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
1970 UPDATE action.hold_request
1971 SET target = target_cn.id
1974 moved_objects := moved_objects + 1;
1977 UPDATE asset.call_number SET deleted = TRUE WHERE id = source_cn.id;
1981 -- ... just move the call number to the target record
1982 UPDATE asset.call_number
1983 SET record = target_record
1984 WHERE id = source_cn.id;
1987 moved_objects := moved_objects + 1;
1990 -- Find T holds targeting the source record ...
1991 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
1993 -- ... and move them to the target record
1994 UPDATE action.hold_request
1995 SET target = target_record
1998 moved_objects := moved_objects + 1;
2001 -- Find serial records targeting the source record ...
2002 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
2003 -- ... and move them to the target record
2004 UPDATE serial.record_entry
2005 SET record = target_record
2006 WHERE id = ser_rec.id;
2008 moved_objects := moved_objects + 1;
2011 -- Find serial subscriptions targeting the source record ...
2012 FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
2013 -- ... and move them to the target record
2014 UPDATE serial.subscription
2015 SET record_entry = target_record
2016 WHERE id = ser_sub.id;
2018 moved_objects := moved_objects + 1;
2021 -- Find booking resource types targeting the source record ...
2022 FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
2023 -- ... and move them to the target record
2024 UPDATE booking.resource_type
2025 SET record = target_record
2026 WHERE id = booking.id;
2028 moved_objects := moved_objects + 1;
2031 -- Find acq lineitems targeting the source record ...
2032 FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
2033 -- ... and move them to the target record
2035 SET eg_bib_id = target_record
2036 WHERE id = acq_lineitem.id;
2038 moved_objects := moved_objects + 1;
2041 -- Find acq user purchase requests targeting the source record ...
2042 FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
2043 -- ... and move them to the target record
2044 UPDATE acq.user_request
2045 SET eg_bib = target_record
2046 WHERE id = acq_request.id;
2048 moved_objects := moved_objects + 1;
2051 -- Find parts attached to the source ...
2052 FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP
2054 SELECT INTO target_part *
2055 FROM biblio.monograph_part
2056 WHERE label = source_part.label
2057 AND record = target_record;
2059 -- ... and if there's a conflicting one on the target ...
2062 -- ... move the copy-part maps to that, and ...
2063 UPDATE asset.copy_part_map
2064 SET part = target_part.id
2065 WHERE part = source_part.id;
2067 -- ... move P holds to the move-target part
2068 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
2070 UPDATE action.hold_request
2071 SET target = target_part.id
2074 moved_objects := moved_objects + 1;
2079 -- ... just move the part to the target record
2080 UPDATE biblio.monograph_part
2081 SET record = target_record
2082 WHERE id = source_part.id;
2085 moved_objects := moved_objects + 1;
2088 -- Find multi_home items attached to the source ...
2089 FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
2090 -- ... and move them to the target record
2091 UPDATE biblio.peer_bib_copy_map
2092 SET peer_record = target_record
2093 WHERE id = multi_home.id;
2095 moved_objects := moved_objects + 1;
2098 -- And delete mappings where the item's home bib was merged with the peer bib
2099 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
2100 SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
2101 FROM asset.copy WHERE id = target_copy
2104 -- Apply merge tracking
2105 UPDATE biblio.record_entry
2106 SET merge_date = NOW() WHERE id = target_record;
2108 UPDATE biblio.record_entry
2109 SET merge_date = NOW(), merged_to = target_record
2110 WHERE id = source_record;
2112 -- replace book bag entries of source_record with target_record
2113 UPDATE container.biblio_record_entry_bucket_item
2114 SET target_biblio_record_entry = target_record
2115 WHERE bucket IN (SELECT id FROM container.biblio_record_entry_bucket WHERE btype = 'bookbag')
2116 AND target_biblio_record_entry = source_record;
2118 -- move over record notes
2119 UPDATE biblio.record_note
2120 SET record = target_record, value = CONCAT(value,'; note merged from ',source_record::TEXT)
2121 WHERE record = source_record
2124 -- add note to record merge
2125 INSERT INTO biblio.record_note (record, value)
2126 VALUES (target_record,CONCAT('record ',source_record::TEXT,' merged on ',NOW()::TEXT));
2128 -- Finally, "delete" the source record
2129 UPDATE biblio.record_entry SET active = FALSE WHERE id = source_record;
2130 DELETE FROM biblio.record_entry WHERE id = source_record;
2132 -- That's all, folks!
2133 RETURN moved_objects;
2135 $func$ LANGUAGE plpgsql;
2138 SELECT evergreen.upgrade_deps_block_check('1351', :eg_version);
2140 INSERT INTO permission.perm_list ( id, code, description )
2143 'ADMIN_FUND_ROLLOVER',
2146 'Allow a user to perform fund propagation and rollover',
2152 -- ensure that permission groups that are able to
2153 -- rollover funds can continue to do so
2154 WITH perms_to_add AS
2156 permission.perm_list
2157 WHERE code IN ('ADMIN_FUND_ROLLOVER'))
2158 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
2159 SELECT grp, perms_to_add.id as perm, depth, grantable
2161 permission.grp_perm_map
2163 --- Don't add the permissions if they have already been assigned
2165 (SELECT DISTINCT grp FROM permission.grp_perm_map
2166 INNER JOIN perms_to_add ON perm=perms_to_add.id)
2168 --- Anybody who can view resources should also see reservations
2169 --- at the same level
2172 FROM permission.perm_list
2173 WHERE code = 'ADMIN_FUND'
2177 SELECT evergreen.upgrade_deps_block_check('1352', :eg_version);
2179 INSERT into config.workstation_setting_type (name, grp, datatype, label)
2181 'eg.grid.admin.local.cash_reports.desk_payments', 'gui', 'object',
2183 'eg.grid.admin.local.cash_reports.desk_payments',
2184 'Grid Config: admin.local.cash_reports.desk_payments',
2188 'eg.grid.admin.local.cash_reports.user_payments', 'gui', 'object',
2190 'eg.grid.admin.local.cash_reports.user_payments',
2191 'Grid Config: admin.local.cash_reports.user_payments',
2197 SELECT evergreen.upgrade_deps_block_check('1353', :eg_version);
2199 UPDATE config.org_unit_setting_type SET description = oils_i18n_gettext('cat.default_classification_scheme',
2200 'Defines the default classification scheme for new call numbers.',
2201 'coust', 'description')
2202 WHERE name = 'cat.default_classification_scheme'
2204 'Defines the default classification scheme for new call numbers: 1 = Generic; 2 = Dewey; 3 = LC';
2208 -- Update auditor tables to catch changes to source tables.
2209 -- Can be removed/skipped if there were no schema changes.
2210 SELECT auditor.update_auditors();
2212 \qecho A partial reingest is necessary to get the full benefit of the change in
2213 \qecho upgrade script 1344 (bug 1864507) It will take a while.
2214 \qecho You can cancel now without losing the effect of
2215 \qecho the rest of the upgrade script, and arrange the reingest later.
2218 SELECT metabib.reingest_metabib_field_entries(
2219 id, TRUE, FALSE, FALSE, TRUE,
2220 (SELECT ARRAY_AGG(id) FROM config.metabib_field WHERE field_class='title' AND (browse_field OR facet_field OR display_field))
2221 ) FROM biblio.record_entry;