1 --Upgrade Script for 2.6.3 to 2.7.0
2 \set eg_version '''2.7.0'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.7.0', :eg_version);
5 -- check whether patch can be applied
6 SELECT evergreen.upgrade_deps_block_check('0884', :eg_version);
8 UPDATE container.biblio_record_entry_bucket_type
9 SET label = oils_i18n_gettext(
14 ) WHERE code = 'bookbag';
16 UPDATE container.user_bucket_type
17 SET label = oils_i18n_gettext(
18 'folks:pub_book_bags.view',
19 'List Published Book Lists',
22 ) WHERE code = 'folks:pub_book_bags.view';
24 UPDATE container.user_bucket_type
25 SET label = oils_i18n_gettext(
26 'folks:pub_book_bags.add',
27 'Add to Published Book Lists',
30 ) WHERE code = 'folks:pub_book_bags.add';
32 UPDATE action_trigger.hook
33 SET description = oils_i18n_gettext(
34 'container.biblio_record_entry_bucket.csv',
35 'Produce a CSV file representing a book list',
38 ) WHERE key = 'container.biblio_record_entry_bucket.csv';
40 UPDATE action_trigger.reactor
41 SET description = oils_i18n_gettext(
43 'Facilitates producing a CSV file representing a book list by introducing an "items" variable into the TT environment, sorted as dictated according to user params',
47 WHERE module = 'ContainerCSV';
49 UPDATE action_trigger.event_definition
50 SET template = REPLACE(template, 'bookbag', 'book list'),
51 name = 'Book List CSV'
52 WHERE name = 'Bookbag CSV';
54 UPDATE config.org_unit_setting_type
55 SET description = oils_i18n_gettext(
56 'opac.patron.temporary_list_warn',
57 'Present a warning dialog to the patron when a patron adds a book to a temporary book list.',
60 ) WHERE name = 'opac.patron.temporary_list_warn';
62 UPDATE config.usr_setting_type
63 SET label = oils_i18n_gettext(
65 'Default list to use when adding to a list',
69 description = oils_i18n_gettext(
71 'Default list to use when adding to a list',
74 ) WHERE name = 'opac.default_list';
77 SELECT evergreen.upgrade_deps_block_check('0885', :eg_version);
79 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
82 depth INT DEFAULT NULL,
83 slimit HSTORE DEFAULT NULL,
84 soffset HSTORE DEFAULT NULL,
85 pref_lib INT DEFAULT NULL,
86 includes TEXT[] DEFAULT NULL::TEXT[]
87 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
88 WITH RECURSIVE ou_depth AS (
93 FROM actor.org_unit_type aout
94 INNER JOIN actor.org_unit ou ON ou_type = aout.id
98 ), descendant_depth AS (
102 FROM actor.org_unit ou
103 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
104 JOIN anscestor_depth ad ON (ad.id = ou.id),
106 WHERE ad.depth = ou_depth.depth
111 FROM actor.org_unit ou
112 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
113 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
114 ), anscestor_depth AS (
118 FROM actor.org_unit ou
119 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
125 FROM actor.org_unit ou
126 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
127 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
129 SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
132 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
133 SELECT acn.id, aou.name, acn.label_sortkey,
135 FROM asset.call_number acn
136 JOIN asset.copy acp ON (acn.id = acp.call_number)
137 JOIN descendants AS aou ON (acp.circ_lib = aou.id)
138 WHERE acn.record = ANY ($1)
139 AND acn.deleted IS FALSE
140 AND acp.deleted IS FALSE
141 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
144 FROM asset.opac_visible_copies
145 WHERE copy_id = acp.id AND record = acn.record
147 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
151 CASE WHEN aou.id = $2 THEN -20000 END,
152 CASE WHEN aou.id = $6 THEN -10000 END,
153 (SELECT distance - 5000
154 FROM actor.org_unit_descendants_distance($6) as x
155 WHERE x.id = aou.id AND $6 IN (
156 SELECT q.id FROM actor.org_unit_descendants($2) as q)),
157 (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
160 evergreen.rank_cp_status(acp.status)
163 GROUP BY ua.id, ua.name, ua.label_sortkey
164 ORDER BY rank, ua.name, ua.label_sortkey
165 LIMIT ($4 -> 'acn')::INT
166 OFFSET ($5 -> 'acn')::INT;
167 $$ LANGUAGE SQL STABLE ROWS 10;
169 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes
170 ( bibid BIGINT, ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] )
171 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT)
172 AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE;
175 SELECT evergreen.upgrade_deps_block_check('0886', :eg_version);
177 INSERT INTO config.copy_status
178 (id, name, holdable, opac_visible, copy_active, restrict_copy_delete)
179 VALUES (17, 'Lost and Paid', FALSE, FALSE, FALSE, TRUE);
181 INSERT INTO config.org_unit_setting_type
182 (name, grp, label, description, datatype)
184 ('circ.use_lost_paid_copy_status',
186 oils_i18n_gettext('circ.use_lost_paid_copy_status',
187 'Use Lost and Paid copy status',
189 oils_i18n_gettext('circ.use_lost_paid_copy_status',
190 'Use Lost and Paid copy status when lost or long overdue billing is paid',
191 'coust', 'description'),
195 -- check whether patch can be applied
196 SELECT evergreen.upgrade_deps_block_check('0888', :eg_version);
198 DROP VIEW acq.lineitem_summary;
200 CREATE VIEW acq.lineitem_summary AS
205 FROM acq.lineitem_detail lid
206 WHERE lineitem = li.id
210 FROM acq.lineitem_detail lid
211 WHERE recv_time IS NOT NULL AND lineitem = li.id
215 FROM acq.lineitem_detail lid
216 JOIN acq.cancel_reason acqcr ON (acqcr.id = lid.cancel_reason)
217 WHERE acqcr.keep_debits IS FALSE AND lineitem = li.id
221 FROM acq.lineitem_detail lid
222 JOIN acq.cancel_reason acqcr ON (acqcr.id = lid.cancel_reason)
223 WHERE acqcr.keep_debits IS TRUE AND lineitem = li.id
227 FROM acq.lineitem_detail lid
228 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
229 WHERE NOT debit.encumbrance AND lineitem = li.id
232 SELECT COUNT(DISTINCT(lid.id))
233 FROM acq.lineitem_detail lid
234 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
235 WHERE lineitem = li.id
238 SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
239 FROM acq.lineitem_detail lid
240 WHERE lid.cancel_reason IS NULL AND lineitem = li.id
241 ) AS estimated_amount,
243 SELECT SUM(debit.amount)::NUMERIC(8,2)
244 FROM acq.lineitem_detail lid
245 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
246 WHERE debit.encumbrance AND lineitem = li.id
247 ) AS encumbrance_amount,
249 SELECT SUM(debit.amount)::NUMERIC(8,2)
250 FROM acq.lineitem_detail lid
251 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
252 WHERE NOT debit.encumbrance AND lineitem = li.id
255 FROM acq.lineitem AS li;
258 -- check whether patch can be applied
259 SELECT evergreen.upgrade_deps_block_check('0889', :eg_version);
261 -- Update ACQ cancel reason names, but only those that
262 -- have not already been locally modified from stock values.
264 UPDATE acq.cancel_reason
265 SET label = oils_i18n_gettext(1,'Canceled: Invalid ISBN', 'acqcr', 'label')
266 WHERE id = 1 AND label = 'invalid_isbn';
268 UPDATE acq.cancel_reason
269 SET label = oils_i18n_gettext(1,'Canceled: Postpone', 'acqcr', 'label')
270 WHERE id = 2 AND label = 'postpone';
272 UPDATE acq.cancel_reason
273 SET label = oils_i18n_gettext(1,'Delayed: Delivered but Lost', 'acqcr', 'label')
274 WHERE id = 3 AND label = 'delivered_but_lost';
276 UPDATE acq.cancel_reason
277 SET label = oils_i18n_gettext(1,'Canceled: Deleted', 'acqcr', 'label')
278 WHERE id = 1002 AND label = 'Deleted';
280 UPDATE acq.cancel_reason
281 SET label = oils_i18n_gettext(1,'Delayed: Changed', 'acqcr', 'label')
282 WHERE id = 1003 AND label = 'Changed';
284 UPDATE acq.cancel_reason
285 SET label = oils_i18n_gettext(1,'Delayed: No Action', 'acqcr', 'label')
286 WHERE id = 1004 AND label = 'No action';
288 UPDATE acq.cancel_reason
289 SET label = oils_i18n_gettext(1,'Delayed: Accepted without amendment', 'acqcr', 'label')
290 WHERE id = 1005 AND label = 'Accepted without amendment';
292 UPDATE acq.cancel_reason
293 SET label = oils_i18n_gettext(1,'Canceled: Not Accepted', 'acqcr', 'label')
294 WHERE id = 1007 AND label = 'Not accepted';
296 UPDATE acq.cancel_reason
297 SET label = oils_i18n_gettext(1,'Canceled: Not Found', 'acqcr', 'label')
298 WHERE id = 1010 AND label = 'Not found';
300 UPDATE acq.cancel_reason
301 SET label = oils_i18n_gettext(1,'Delayed: Accepted with amendment', 'acqcr', 'label')
302 WHERE id = 1024 AND label = 'Accepted with amendment, no confirmation required';
304 UPDATE acq.cancel_reason
305 SET label = oils_i18n_gettext(1,'Delayed: Split Quantity', 'acqcr', 'label')
306 WHERE id = 1211 AND label = 'Split quantity';
308 UPDATE acq.cancel_reason
309 SET label = oils_i18n_gettext(1,'Delayed: Ordered Quantity', 'acqcr', 'label')
310 WHERE id = 1221 AND label = 'Ordered quantity';
312 UPDATE acq.cancel_reason
313 SET label = oils_i18n_gettext(1,'Delayed: Pieces Delivered', 'acqcr', 'label')
314 WHERE id = 1246 AND label = 'Pieces delivered';
316 UPDATE acq.cancel_reason
317 SET label = oils_i18n_gettext(1,'Delayed: Backorder', 'acqcr', 'label')
318 WHERE id = 1283 AND label = 'Backorder quantity';
320 -- action/trigger additions
321 -- All following changes are only applied where the source data matches
322 -- the stock data. IOW, if a template has been locally modified,
323 -- it's left unchanged.
327 -- avoid collisions by testing for the presence of the
328 -- desired environment addition.
330 PERFORM 1 FROM action_trigger.environment
331 WHERE event_def = 4 AND path = 'lineitems.cancel_reason';
333 INSERT INTO action_trigger.environment (event_def, path)
334 VALUES (4, 'lineitems.cancel_reason');
337 PERFORM 1 FROM action_trigger.environment
338 WHERE event_def = 14 AND path = 'cancel_reason';
340 INSERT INTO action_trigger.environment (event_def, path)
341 VALUES ( 14, 'cancel_reason' );
344 PERFORM 1 FROM action_trigger.environment
345 WHERE event_def = 14 AND path = 'lineitem_details.cancel_reason';
347 INSERT INTO action_trigger.environment (event_def, path)
348 VALUES ( 14, 'lineitem_details.cancel_reason' );
352 UPDATE action_trigger.event_definition SET template =
356 # find a lineitem attribute by name and optional type
358 FOR attr IN li.attributes;
359 IF attr.attr_name == attr_name;
360 IF !attr_type OR attr_type == attr.attr_type;
369 <h2>Purchase Order: [% target.name %] ([% target.id %])</h2>
371 date <b>[% date.format(date.now, '%Y%m%d') %]</b>
375 table td { padding:5px; border:1px solid #aaa;}
376 table { width:95%; border-collapse:collapse; }
377 #vendor-notes { padding:5px; border:1px solid #aaa; }
379 <table id='vendor-table'>
381 <td valign='top'>Vendor</td>
383 <div>[% target.provider.name %]</div>
384 <div>[% target.provider.addresses.0.street1 %]</div>
385 <div>[% target.provider.addresses.0.street2 %]</div>
386 <div>[% target.provider.addresses.0.city %]</div>
387 <div>[% target.provider.addresses.0.state %]</div>
388 <div>[% target.provider.addresses.0.country %]</div>
389 <div>[% target.provider.addresses.0.post_code %]</div>
391 <td valign='top'>Ship to / Bill to</td>
393 <div>[% target.ordering_agency.name %]</div>
394 <div>[% target.ordering_agency.billing_address.street1 %]</div>
395 <div>[% target.ordering_agency.billing_address.street2 %]</div>
396 <div>[% target.ordering_agency.billing_address.city %]</div>
397 <div>[% target.ordering_agency.billing_address.state %]</div>
398 <div>[% target.ordering_agency.billing_address.country %]</div>
399 <div>[% target.ordering_agency.billing_address.post_code %]</div>
405 <fieldset id='vendor-notes'>
406 <legend>Notes to the Vendor</legend>
408 [% FOR note IN target.notes %]
409 [% IF note.vendor_public == 't' %]
410 <li>[% note.value %]</li>
421 <th>ISBN or Item #</th>
426 <th>Delayed / Canceled</th>
433 [% FOR li IN target.lineitems %]
436 [% count = li.lineitem_details.size %]
437 [% price = li.estimated_unit_price %]
438 [% litotal = (price * count) %]
439 [% subtotal = subtotal + litotal %]
441 ident_attr = helpers.get_li_order_ident(li.attributes);
442 SET ident_value = ident_attr.attr_value IF ident_attr;
444 <td>[% target.id %]</td>
445 <td>[% ident_value %]</td>
446 <td>[% PROCESS get_li_attr attr_name = 'title' %]</td>
449 <td>[% litotal %]</td>
450 <td>[% li.cancel_reason.label %]</td>
453 [% FOR note IN li.lineitem_notes %]
454 [% IF note.vendor_public == 't' %]
455 <li>[% note.value %]</li>
465 <td>[% subtotal %]</td>
472 Total Line Item Count: [% target.lineitems.size %]
474 WHERE id = 4 AND template =
478 # find a lineitem attribute by name and optional type
480 FOR attr IN li.attributes;
481 IF attr.attr_name == attr_name;
482 IF !attr_type OR attr_type == attr.attr_type;
491 <h2>Purchase Order: [% target.name %] ([% target.id %])</h2>
493 date <b>[% date.format(date.now, '%Y%m%d') %]</b>
497 table td { padding:5px; border:1px solid #aaa;}
498 table { width:95%; border-collapse:collapse; }
499 #vendor-notes { padding:5px; border:1px solid #aaa; }
501 <table id='vendor-table'>
503 <td valign='top'>Vendor</td>
505 <div>[% target.provider.name %]</div>
506 <div>[% target.provider.addresses.0.street1 %]</div>
507 <div>[% target.provider.addresses.0.street2 %]</div>
508 <div>[% target.provider.addresses.0.city %]</div>
509 <div>[% target.provider.addresses.0.state %]</div>
510 <div>[% target.provider.addresses.0.country %]</div>
511 <div>[% target.provider.addresses.0.post_code %]</div>
513 <td valign='top'>Ship to / Bill to</td>
515 <div>[% target.ordering_agency.name %]</div>
516 <div>[% target.ordering_agency.billing_address.street1 %]</div>
517 <div>[% target.ordering_agency.billing_address.street2 %]</div>
518 <div>[% target.ordering_agency.billing_address.city %]</div>
519 <div>[% target.ordering_agency.billing_address.state %]</div>
520 <div>[% target.ordering_agency.billing_address.country %]</div>
521 <div>[% target.ordering_agency.billing_address.post_code %]</div>
527 <fieldset id='vendor-notes'>
528 <legend>Notes to the Vendor</legend>
530 [% FOR note IN target.notes %]
531 [% IF note.vendor_public == 't' %]
532 <li>[% note.value %]</li>
543 <th>ISBN or Item #</th>
554 [% FOR li IN target.lineitems %]
557 [% count = li.lineitem_details.size %]
558 [% price = li.estimated_unit_price %]
559 [% litotal = (price * count) %]
560 [% subtotal = subtotal + litotal %]
562 ident_attr = helpers.get_li_order_ident(li.attributes);
563 SET ident_value = ident_attr.attr_value IF ident_attr;
565 <td>[% target.id %]</td>
566 <td>[% ident_value %]</td>
567 <td>[% PROCESS get_li_attr attr_name = 'title' %]</td>
570 <td>[% litotal %]</td>
573 [% FOR note IN li.lineitem_notes %]
574 [% IF note.vendor_public == 't' %]
575 <li>[% note.value %]</li>
585 <td>[% subtotal %]</td>
592 Total Line Item Count: [% target.lineitems.size %]
595 -- lineitem worksheet
596 UPDATE action_trigger.event_definition SET template =
599 [%- SET li = target; -%]
600 <div class="wrapper">
601 <div class="summary" style='font-size:110%; font-weight:bold;'>
603 <div>Title: [% helpers.get_li_attr("title", "", li.attributes) %]</div>
604 <div>Author: [% helpers.get_li_attr("author", "", li.attributes) %]</div>
605 <div class="count">Item Count: [% li.lineitem_details.size %]</div>
606 <div class="lineid">Lineitem ID: [% li.id %]</div>
607 <div>Open Holds: [% helpers.bre_open_hold_count(li.eg_bib_id) %]</div>
608 [% IF li.cancel_reason.label %]
609 <div>[% li.cancel_reason.label %]</div>
612 [% IF li.distribution_formulas.size > 0 %]
614 [% FOREACH form IN li.distribution_formulas; forms.push(form.formula.name); END %]
615 <div>Distribution Formulas: [% forms.join(',') %]</div>
618 [% IF li.lineitem_notes.size > 0 %]
621 [%- FOR note IN li.lineitem_notes -%]
623 [% IF note.alert_text %]
624 [% note.alert_text.code -%]
644 <th>Shelving Location</th>
647 <th>Delayed / Canceled</th>
651 <!-- set detail.owning_lib from fm object to org name -->
652 [% FOREACH detail IN li.lineitem_details %]
653 [% detail.owning_lib = detail.owning_lib.shortname %]
656 [% FOREACH detail IN li.lineitem_details.sort('owning_lib') %]
658 IF detail.eg_copy_id;
659 SET copy = detail.eg_copy_id;
660 SET cn_label = copy.call_number.label;
663 SET cn_label = detail.cn_label;
667 <!-- acq.lineitem_detail.id = [%- detail.id -%] -->
668 <td style='padding:5px;'>[% detail.owning_lib %]</td>
669 <td style='padding:5px;'>[% IF copy.barcode %]<span class="barcode" >[% detail.barcode %]</span>[% END %]</td>
670 <td style='padding:5px;'>[% IF cn_label %]<span class="cn_label" >[% cn_label %]</span>[% END %]</td>
671 <td style='padding:5px;'>[% IF detail.fund %]<span class="fund">[% detail.fund.code %] ([% detail.fund.year %])</span>[% END %]</td>
672 <td style='padding:5px;'>[% copy.location.name %]</td>
673 <td style='padding:5px;'>[% IF detail.recv_time %]<span class="recv_time">[% detail.recv_time %]</span>[% END %]</td>
674 <td style='padding:5px;'>[% detail.note %]</td>
675 <td style='padding:5px;'>[% detail.cancel_reason.label %]</td>
682 WHERE id = 14 AND template =
685 [%- SET li = target; -%]
686 <div class="wrapper">
687 <div class="summary" style='font-size:110%; font-weight:bold;'>
689 <div>Title: [% helpers.get_li_attr("title", "", li.attributes) %]</div>
690 <div>Author: [% helpers.get_li_attr("author", "", li.attributes) %]</div>
691 <div class="count">Item Count: [% li.lineitem_details.size %]</div>
692 <div class="lineid">Lineitem ID: [% li.id %]</div>
693 <div>Open Holds: [% helpers.bre_open_hold_count(li.eg_bib_id) %]</div>
695 [% IF li.distribution_formulas.size > 0 %]
697 [% FOREACH form IN li.distribution_formulas; forms.push(form.formula.name); END %]
698 <div>Distribution Formulas: [% forms.join(',') %]</div>
701 [% IF li.lineitem_notes.size > 0 %]
704 [%- FOR note IN li.lineitem_notes -%]
706 [% IF note.alert_text %]
707 [% note.alert_text.code -%]
727 <th>Shelving Location</th>
733 <!-- set detail.owning_lib from fm object to org name -->
734 [% FOREACH detail IN li.lineitem_details %]
735 [% detail.owning_lib = detail.owning_lib.shortname %]
738 [% FOREACH detail IN li.lineitem_details.sort('owning_lib') %]
740 IF detail.eg_copy_id;
741 SET copy = detail.eg_copy_id;
742 SET cn_label = copy.call_number.label;
745 SET cn_label = detail.cn_label;
749 <!-- acq.lineitem_detail.id = [%- detail.id -%] -->
750 <td style='padding:5px;'>[% detail.owning_lib %]</td>
751 <td style='padding:5px;'>[% IF copy.barcode %]<span class="barcode" >[% detail.barcode %]</span>[% END %]</td>
752 <td style='padding:5px;'>[% IF cn_label %]<span class="cn_label" >[% cn_label %]</span>[% END %]</td>
753 <td style='padding:5px;'>[% IF detail.fund %]<span class="fund">[% detail.fund.code %] ([% detail.fund.year %])</span>[% END %]</td>
754 <td style='padding:5px;'>[% copy.location.name %]</td>
755 <td style='padding:5px;'>[% IF detail.recv_time %]<span class="recv_time">[% detail.recv_time %]</span>[% END %]</td>
756 <td style='padding:5px;'>[% detail.note %]</td>