1 --Upgrade Script for 2.2 to 2.3.0
3 \set eg_version '''2.3.0'''
5 \qecho **********************************************************************
6 \qecho Your current working directory when you run this script *must* be
7 \qecho Open-ILS/src/sql/Pg/version-upgrade/ or it will fail to find the point
8 \qecho release upgrade scripts. Press <ENTER> to continue, or hit <CTRL-C> to
9 \qecho prevent the upgrade from happening.
10 \qecho **********************************************************************
11 \prompt chance-to-quit
13 \qecho The following statement might fail, and that is okay; we are
14 \qecho ensuring that an upgrade that should have been applied during
15 \qecho the 2.2 upgrade is actually applied now.
17 -- 0715.data.add_acq_config_group
18 INSERT INTO config.settings_group (name, label) VALUES
19 ('acq', oils_i18n_gettext('config.settings_group.system', 'Acquisitions', 'coust', 'label'));
21 UPDATE config.org_unit_setting_type
23 WHERE name LIKE 'acq%';
25 \qecho Apply previous point release upgrades to ensure that all known fixes are
26 \qecho in place. Failures here will generally indicate that the fixes were
27 \qecho already applied.
29 \i 2.2.0-2.2.1-upgrade-db.sql
30 \i 2.2.1-2.2.2-upgrade-db.sql
31 \i 2.2.2-2.2.3-upgrade-db.sql
32 \i 2.2.3-2.2.4-upgrade-db.sql
33 \i 2.2.4-2.2.5-upgrade-db.sql
35 \qecho The real upgrade begins now.
36 \set eg_version '''2.3.0'''
39 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.0', :eg_version);
40 -- Evergreen DB patch 0703.tpac_value_maps.sql
42 -- check whether patch can be applied
43 SELECT evergreen.upgrade_deps_block_check('0703', :eg_version);
45 ALTER TABLE config.coded_value_map
46 ADD COLUMN opac_visible BOOL NOT NULL DEFAULT TRUE,
47 ADD COLUMN search_label TEXT,
48 ADD COLUMN is_simple BOOL NOT NULL DEFAULT FALSE;
52 SELECT evergreen.upgrade_deps_block_check('0712', :eg_version);
54 -- General purpose query container. Any table the needs to store
55 -- a QueryParser query should store it here. This will be the
56 -- source for top-level and QP sub-search inclusion queries.
57 CREATE TABLE actor.search_query (
58 id SERIAL PRIMARY KEY,
59 label TEXT NOT NULL, -- i18n
60 query_text TEXT NOT NULL -- QP text
63 -- e.g. "Reading Level"
64 CREATE TABLE actor.search_filter_group (
65 id SERIAL PRIMARY KEY,
66 owner INT NOT NULL REFERENCES actor.org_unit (id)
67 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
68 code TEXT NOT NULL, -- for CGI, etc.
69 label TEXT NOT NULL, -- i18n
70 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
71 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
72 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
75 -- e.g. "Adult", "Teen", etc.
76 CREATE TABLE actor.search_filter_group_entry (
77 id SERIAL PRIMARY KEY,
78 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
79 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
80 pos INT NOT NULL DEFAULT 0,
81 query INT NOT NULL REFERENCES actor.search_query(id)
82 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
83 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
87 SELECT evergreen.upgrade_deps_block_check('0713', :eg_version);
89 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
90 'ui.grid_columns.circ.hold_pull_list',
94 'ui.grid_columns.circ.hold_pull_list',
100 'ui.grid_columns.circ.hold_pull_list',
101 'Hold Pull List Saved Column Settings',
110 SELECT evergreen.upgrade_deps_block_check('0714', :eg_version);
112 INSERT into config.org_unit_setting_type
113 (name, grp, label, description, datatype)
115 'opac.patron.auto_overide_hold_events',
118 'opac.patron.auto_overide_hold_events',
119 'Auto-Override Permitted Hold Blocks (Patrons)',
124 'opac.patron.auto_overide_hold_events',
125 'When a patron places a hold that fails and the patron has the correct permission ' ||
126 'to override the hold, automatically override the hold without presenting a message ' ||
127 'to the patron and requiring that the patron make a decision to override',
134 -- Evergreen DB patch 0718.data.add-to-permanent-bookbag.sql
136 -- check whether patch can be applied
137 SELECT evergreen.upgrade_deps_block_check('0718', :eg_version);
139 INSERT into config.org_unit_setting_type
140 (name, grp, label, description, datatype)
142 'opac.patron.temporary_list_warn',
145 'opac.patron.temporary_list_warn',
146 'Warn patrons when adding to a temporary book list',
151 'opac.patron.temporary_list_warn',
152 'Present a warning dialog to the patron when a patron adds a book to a temporary book bag.',
159 INSERT INTO config.usr_setting_type
160 (name,grp,opac_visible,label,description,datatype)
162 'opac.temporary_list_no_warn',
166 'opac.temporary_list_no_warn',
167 'Opt out of warning when adding a book to a temporary book list',
172 'opac.temporary_list_no_warn',
173 'Opt out of warning when adding a book to a temporary book list',
180 INSERT INTO config.usr_setting_type
181 (name,grp,opac_visible,label,description,datatype)
188 'Default list to use when adding to a bookbag',
194 'Default list to use when adding to a bookbag',
202 SELECT evergreen.upgrade_deps_block_check('0719', :eg_version);
204 INSERT INTO config.org_unit_setting_type (
205 name, label, grp, description, datatype
207 'circ.staff.max_visible_event_age',
208 'Maximum visible age of User Trigger Events in Staff Interfaces',
210 'If this is unset, staff can view User Trigger Events regardless of age. When this is set to an interval, it represents the age of the oldest possible User Trigger Event that can be viewed.',
214 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
215 'ui.grid_columns.actor.user.event_log',
219 'ui.grid_columns.actor.user.event_log',
225 'ui.grid_columns.actor.user.event_log',
226 'User Event Log Saved Column Settings',
233 INSERT INTO permission.perm_list ( id, code, description )
236 'VIEW_TRIGGER_EVENT',
239 'Allows a user to view circ- and hold-related action/trigger events',
246 SELECT evergreen.upgrade_deps_block_check('0720', :eg_version);
248 ALTER TABLE config.circ_matrix_weights
249 ADD COLUMN copy_location NUMERIC(6,2) NOT NULL DEFAULT 5.0;
250 UPDATE config.circ_matrix_weights
251 SET copy_location = 0.0 WHERE name = 'All_Equal';
252 ALTER TABLE config.circ_matrix_weights
253 ALTER COLUMN copy_location DROP DEFAULT; -- for consistency w/ baseline schema
255 ALTER TABLE config.circ_matrix_matchpoint
256 ADD COLUMN copy_location INTEGER REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED;
258 DROP INDEX config.ccmm_once_per_paramset;
260 CREATE UNIQUE INDEX ccmm_once_per_paramset ON config.circ_matrix_matchpoint (org_unit, grp, COALESCE(circ_modifier, ''), COALESCE(copy_location::TEXT, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_bib_level,''), COALESCE(marc_vr_format, ''), COALESCE(copy_circ_lib::TEXT, ''), COALESCE(copy_owning_lib::TEXT, ''), COALESCE(user_home_ou::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(is_renewal::TEXT, ''), COALESCE(usr_age_lower_bound::TEXT, ''), COALESCE(usr_age_upper_bound::TEXT, ''), COALESCE(item_age::TEXT, '')) WHERE active;
262 -- Linkage between limit sets and circ mods
263 CREATE TABLE config.circ_limit_set_copy_loc_map (
264 id SERIAL PRIMARY KEY,
265 limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
266 copy_loc INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
267 CONSTRAINT cl_once_per_set UNIQUE (limit_set, copy_loc)
270 -- Add support for checking config.circ_limit_set_copy_loc_map's
271 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL )
272 RETURNS SETOF action.circ_matrix_test_result AS $func$
274 user_object actor.usr%ROWTYPE;
275 standing_penalty config.standing_penalty%ROWTYPE;
276 item_object asset.copy%ROWTYPE;
277 item_status_object config.copy_status%ROWTYPE;
278 item_location_object asset.copy_location%ROWTYPE;
279 result action.circ_matrix_test_result;
280 circ_test action.found_circ_matrix_matchpoint;
281 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
282 circ_limit_set config.circ_limit_set%ROWTYPE;
283 hold_ratio action.hold_stats%ROWTYPE;
286 context_org_list INT[];
289 -- Assume success unless we hit a failure condition
290 result.success := TRUE;
292 -- Need user info to look up matchpoints
293 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
295 -- (Insta)Fail if we couldn't find the user
296 IF user_object.id IS NULL THEN
297 result.fail_part := 'no_user';
298 result.success := FALSE;
304 -- Need item info to look up matchpoints
305 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
307 -- (Insta)Fail if we couldn't find the item
308 IF item_object.id IS NULL THEN
309 result.fail_part := 'no_item';
310 result.success := FALSE;
316 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
318 circ_matchpoint := circ_test.matchpoint;
319 result.matchpoint := circ_matchpoint.id;
320 result.circulate := circ_matchpoint.circulate;
321 result.duration_rule := circ_matchpoint.duration_rule;
322 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
323 result.max_fine_rule := circ_matchpoint.max_fine_rule;
324 result.hard_due_date := circ_matchpoint.hard_due_date;
325 result.renewals := circ_matchpoint.renewals;
326 result.grace_period := circ_matchpoint.grace_period;
327 result.buildrows := circ_test.buildrows;
329 -- (Insta)Fail if we couldn't find a matchpoint
330 IF circ_test.success = false THEN
331 result.fail_part := 'no_matchpoint';
332 result.success := FALSE;
338 -- All failures before this point are non-recoverable
339 -- Below this point are possibly overridable failures
341 -- Fail if the user is barred
342 IF user_object.barred IS TRUE THEN
343 result.fail_part := 'actor.usr.barred';
344 result.success := FALSE;
349 -- Fail if the item can't circulate
350 IF item_object.circulate IS FALSE THEN
351 result.fail_part := 'asset.copy.circulate';
352 result.success := FALSE;
357 -- Fail if the item isn't in a circulateable status on a non-renewal
358 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
359 result.fail_part := 'asset.copy.status';
360 result.success := FALSE;
363 -- Alternately, fail if the item isn't checked out on a renewal
364 ELSIF renewal AND item_object.status <> 1 THEN
365 result.fail_part := 'asset.copy.status';
366 result.success := FALSE;
371 -- Fail if the item can't circulate because of the shelving location
372 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
373 IF item_location_object.circulate IS FALSE THEN
374 result.fail_part := 'asset.copy_location.circulate';
375 result.success := FALSE;
380 -- Use Circ OU for penalties and such
381 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
384 penalty_type = '%RENEW%';
386 penalty_type = '%CIRC%';
389 FOR standing_penalty IN
390 SELECT DISTINCT csp.*
391 FROM actor.usr_standing_penalty usp
392 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
393 WHERE usr = match_user
394 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
395 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
396 AND csp.block_list LIKE penalty_type LOOP
398 result.fail_part := standing_penalty.name;
399 result.success := FALSE;
404 -- Fail if the test is set to hard non-circulating
405 IF circ_matchpoint.circulate IS FALSE THEN
406 result.fail_part := 'config.circ_matrix_test.circulate';
407 result.success := FALSE;
412 -- Fail if the total copy-hold ratio is too low
413 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
414 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
415 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
416 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
417 result.success := FALSE;
423 -- Fail if the available copy-hold ratio is too low
424 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
425 IF hold_ratio.hold_count IS NULL THEN
426 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
428 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
429 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
430 result.success := FALSE;
436 -- Fail if the user has too many items out by defined limit sets
437 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
438 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
439 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
440 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
442 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
443 SELECT INTO context_org_list ARRAY_AGG(aou.id)
444 FROM actor.org_unit_full_path( circ_ou ) aou
445 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
446 WHERE aout.depth >= circ_limit_set.depth;
447 IF circ_limit_set.global THEN
448 WITH RECURSIVE descendant_depth AS (
451 FROM actor.org_unit ou
452 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
456 FROM actor.org_unit ou
457 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
458 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
460 SELECT INTO items_out COUNT(DISTINCT circ.id)
461 FROM action.circulation circ
462 JOIN asset.copy copy ON (copy.id = circ.target_copy)
463 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
464 WHERE circ.usr = match_user
465 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
466 AND circ.checkin_time IS NULL
467 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
468 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
469 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
470 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
472 IF items_out >= circ_limit_set.items_out THEN
473 result.fail_part := 'config.circ_matrix_circ_mod_test';
474 result.success := FALSE;
479 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;
482 -- If we passed everything, return the successful matchpoint
489 $func$ LANGUAGE plpgsql;
492 -- adding copy_loc to circ_matrix_matchpoint
493 CREATE OR REPLACE FUNCTION action.find_circ_matrix_matchpoint( context_ou INT, item_object asset.copy, user_object actor.usr, renewal BOOL ) RETURNS action.found_circ_matrix_matchpoint AS $func$
495 cn_object asset.call_number%ROWTYPE;
496 rec_descriptor metabib.rec_descriptor%ROWTYPE;
497 cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
498 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
499 weights config.circ_matrix_weights%ROWTYPE;
501 my_item_age INTERVAL;
502 denominator NUMERIC(6,2);
504 result action.found_circ_matrix_matchpoint;
507 result.success = false;
510 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
511 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
513 -- Pre-generate this so we only calc it once
514 IF user_object.dob IS NOT NULL THEN
515 SELECT INTO user_age age(user_object.dob);
519 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
521 -- Grab the closest set circ weight setting.
522 SELECT INTO weights cw.*
523 FROM config.weight_assoc wa
524 JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
525 JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
530 -- No weights? Bad admin! Defaults to handle that anyway.
531 IF weights.id IS NULL THEN
533 weights.org_unit := 10.0;
534 weights.circ_modifier := 5.0;
535 weights.copy_location := 5.0;
536 weights.marc_type := 4.0;
537 weights.marc_form := 3.0;
538 weights.marc_bib_level := 2.0;
539 weights.marc_vr_format := 2.0;
540 weights.copy_circ_lib := 8.0;
541 weights.copy_owning_lib := 8.0;
542 weights.user_home_ou := 8.0;
543 weights.ref_flag := 1.0;
544 weights.juvenile_flag := 6.0;
545 weights.is_renewal := 7.0;
546 weights.usr_age_lower_bound := 0.0;
547 weights.usr_age_upper_bound := 0.0;
548 weights.item_age := 0.0;
551 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
552 -- If you break your org tree with funky parenting this may be wrong
553 -- Note: This CTE is duplicated in the find_hold_matrix_matchpoint function, and it may be a good idea to split it off to a function
554 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
555 WITH all_distance(distance) AS (
556 SELECT depth AS distance FROM actor.org_unit_type
558 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
560 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
562 -- Loop over all the potential matchpoints
563 FOR cur_matchpoint IN
565 FROM config.circ_matrix_matchpoint m
566 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
567 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
568 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
569 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
570 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
573 -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
575 -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
576 AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
577 AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
578 AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
580 AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
581 -- Static User Checks
582 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
583 AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
584 AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
585 -- Static Item Checks
586 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
587 AND (m.copy_location IS NULL OR m.copy_location = item_object.location)
588 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
589 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
590 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
591 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
592 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
593 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
596 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
598 CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
599 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
600 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
601 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
602 -- Circ Type -- Note: 4^x is equiv to 2^(2*x)
603 CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
604 -- Static User Checks
605 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
606 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
607 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
608 -- Static Item Checks
609 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
610 CASE WHEN m.copy_location IS NOT NULL THEN 4^weights.copy_location ELSE 0.0 END +
611 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
612 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
613 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
614 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
615 -- Item age has a slight adjustment to weight based on value.
616 -- This should ensure that a shorter age limit comes first when all else is equal.
617 -- NOTE: This assumes that intervals will normally be in days.
618 CASE WHEN m.item_age IS NOT NULL THEN 4^weights.item_age - 1 + 86400/EXTRACT(EPOCH FROM m.item_age) ELSE 0.0 END DESC,
619 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
620 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
623 -- Record the full matching row list
624 row_list := row_list || cur_matchpoint.id;
626 -- No matchpoint yet?
627 IF matchpoint.id IS NULL THEN
628 -- Take the entire matchpoint as a starting point
629 matchpoint := cur_matchpoint;
630 CONTINUE; -- No need to look at this row any more.
633 -- Incomplete matchpoint?
634 IF matchpoint.circulate IS NULL THEN
635 matchpoint.circulate := cur_matchpoint.circulate;
637 IF matchpoint.duration_rule IS NULL THEN
638 matchpoint.duration_rule := cur_matchpoint.duration_rule;
640 IF matchpoint.recurring_fine_rule IS NULL THEN
641 matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
643 IF matchpoint.max_fine_rule IS NULL THEN
644 matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
646 IF matchpoint.hard_due_date IS NULL THEN
647 matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
649 IF matchpoint.total_copy_hold_ratio IS NULL THEN
650 matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
652 IF matchpoint.available_copy_hold_ratio IS NULL THEN
653 matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
655 IF matchpoint.renewals IS NULL THEN
656 matchpoint.renewals := cur_matchpoint.renewals;
658 IF matchpoint.grace_period IS NULL THEN
659 matchpoint.grace_period := cur_matchpoint.grace_period;
663 -- Check required fields
664 IF matchpoint.circulate IS NOT NULL AND
665 matchpoint.duration_rule IS NOT NULL AND
666 matchpoint.recurring_fine_rule IS NOT NULL AND
667 matchpoint.max_fine_rule IS NOT NULL THEN
668 -- All there? We have a completed match.
669 result.success := true;
672 -- Include the assembled matchpoint, even if it isn't complete
673 result.matchpoint := matchpoint;
675 -- Include (for debugging) the full list of matching rows
676 result.buildrows := row_list;
678 -- Hand the result back to caller
681 $func$ LANGUAGE plpgsql;
686 SELECT evergreen.upgrade_deps_block_check('0721', :eg_version);
688 UPDATE config.standing_penalty
689 SET block_list = REPLACE(block_list, 'HOLD', 'HOLD|CAPTURE')
691 -- STAFF_ penalties have names that match their block list
692 name NOT LIKE 'STAFF_%'
693 -- belt & suspenders, also good for testing
694 AND block_list NOT LIKE '%CAPTURE%';
696 -- CIRC|FULFILL is now the same as CIRC previously was by itself
697 UPDATE config.standing_penalty
698 SET block_list = REPLACE(block_list, 'CIRC', 'CIRC|FULFILL')
700 -- STAFF_ penalties have names that match their block list
701 name NOT LIKE 'STAFF_%'
702 -- belt & suspenders, also good for testing
703 AND block_list NOT LIKE '%FULFILL%';
706 -- apply the HOLD vs CAPTURE block logic
707 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
710 user_object actor.usr%ROWTYPE;
711 age_protect_object config.rule_age_hold_protect%ROWTYPE;
712 standing_penalty config.standing_penalty%ROWTYPE;
713 transit_range_ou_type actor.org_unit_type%ROWTYPE;
714 transit_source actor.org_unit%ROWTYPE;
715 item_object asset.copy%ROWTYPE;
716 item_cn_object asset.call_number%ROWTYPE;
717 item_status_object config.copy_status%ROWTYPE;
718 item_location_object asset.copy_location%ROWTYPE;
719 ou_skip actor.org_unit_setting%ROWTYPE;
720 result action.matrix_test_result;
721 hold_test config.hold_matrix_matchpoint%ROWTYPE;
722 use_active_date TEXT;
723 age_protect_date TIMESTAMP WITH TIME ZONE;
725 hold_transit_prox INT;
726 frozen_hold_count INT;
727 context_org_list INT[];
731 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
732 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
734 result.success := TRUE;
736 -- The HOLD penalty block only applies to new holds.
737 -- The CAPTURE penalty block applies to existing holds.
738 hold_penalty := 'HOLD';
740 hold_penalty := 'CAPTURE';
743 -- Fail if we couldn't find a user
744 IF user_object.id IS NULL THEN
745 result.fail_part := 'no_user';
746 result.success := FALSE;
752 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
754 -- Fail if we couldn't find a copy
755 IF item_object.id IS NULL THEN
756 result.fail_part := 'no_item';
757 result.success := FALSE;
763 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
764 result.matchpoint := matchpoint_id;
766 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
768 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
769 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
770 result.fail_part := 'circ.holds.target_skip_me';
771 result.success := FALSE;
777 -- Fail if user is barred
778 IF user_object.barred IS TRUE THEN
779 result.fail_part := 'actor.usr.barred';
780 result.success := FALSE;
786 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
787 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
788 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
790 -- Fail if we couldn't find any matchpoint (requires a default)
791 IF matchpoint_id IS NULL THEN
792 result.fail_part := 'no_matchpoint';
793 result.success := FALSE;
799 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
801 IF hold_test.holdable IS FALSE THEN
802 result.fail_part := 'config.hold_matrix_test.holdable';
803 result.success := FALSE;
808 IF item_object.holdable IS FALSE THEN
809 result.fail_part := 'item.holdable';
810 result.success := FALSE;
815 IF item_status_object.holdable IS FALSE THEN
816 result.fail_part := 'status.holdable';
817 result.success := FALSE;
822 IF item_location_object.holdable IS FALSE THEN
823 result.fail_part := 'location.holdable';
824 result.success := FALSE;
829 IF hold_test.transit_range IS NOT NULL THEN
830 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
831 IF hold_test.distance_is_from_owner THEN
832 SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
834 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
837 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
840 result.fail_part := 'transit_range';
841 result.success := FALSE;
847 FOR standing_penalty IN
848 SELECT DISTINCT csp.*
849 FROM actor.usr_standing_penalty usp
850 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
851 WHERE usr = match_user
852 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
853 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
854 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
856 result.fail_part := standing_penalty.name;
857 result.success := FALSE;
862 IF hold_test.stop_blocked_user IS TRUE THEN
863 FOR standing_penalty IN
864 SELECT DISTINCT csp.*
865 FROM actor.usr_standing_penalty usp
866 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
867 WHERE usr = match_user
868 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
869 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
870 AND csp.block_list LIKE '%CIRC%' LOOP
872 result.fail_part := standing_penalty.name;
873 result.success := FALSE;
879 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
880 SELECT INTO hold_count COUNT(*)
881 FROM action.hold_request
882 WHERE usr = match_user
883 AND fulfillment_time IS NULL
884 AND cancel_time IS NULL
885 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
887 IF hold_count >= hold_test.max_holds THEN
888 result.fail_part := 'config.hold_matrix_test.max_holds';
889 result.success := FALSE;
895 IF item_object.age_protect IS NOT NULL THEN
896 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
897 IF hold_test.distance_is_from_owner THEN
898 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
900 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
902 IF use_active_date = 'true' THEN
903 age_protect_date := COALESCE(item_object.active_date, NOW());
905 age_protect_date := item_object.create_date;
907 IF age_protect_date + age_protect_object.age > NOW() THEN
908 IF hold_test.distance_is_from_owner THEN
909 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
910 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
912 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
915 IF hold_transit_prox > age_protect_object.prox THEN
916 result.fail_part := 'config.rule_age_hold_protect.prox';
917 result.success := FALSE;
930 $func$ LANGUAGE plpgsql;
932 -- Evergreen DB patch 0727.function.xml_pretty_print.sql
934 -- A simple pretty printer for XML.
935 -- Particularly useful for debugging the biblio.record_entry.marc field.
938 -- check whether patch can be applied
939 SELECT evergreen.upgrade_deps_block_check('0727', :eg_version);
941 CREATE OR REPLACE FUNCTION evergreen.xml_pretty_print(input XML)
945 SELECT xslt_process($1::text,
946 $$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
948 <xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
949 <xsl:strip-space elements="*"/>
950 <xsl:template match="@*|node()">
952 <xsl:apply-templates select="@*|node()"/>
959 COMMENT ON FUNCTION evergreen.xml_pretty_print(input XML) IS
960 'Simple pretty printer for XML, as written by Andrew Dunstan at http://goo.gl/zBHIk';
963 SELECT evergreen.upgrade_deps_block_check('0728', :eg_version);
965 INSERT INTO actor.search_filter_group (owner, code, label)
966 VALUES (1, 'kpac_main', 'Kid''s OPAC Search Filter');
968 INSERT INTO actor.search_query (label, query_text)
969 VALUES ('Children''s Materials', 'audience(a,b,c)');
970 INSERT INTO actor.search_query (label, query_text)
971 VALUES ('Young Adult Materials', 'audience(j,d)');
972 INSERT INTO actor.search_query (label, query_text)
973 VALUES ('General/Adult Materials', 'audience(e,f,g, )');
975 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
977 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
978 (SELECT id FROM actor.search_query WHERE label = 'Children''s Materials'),
981 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
983 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
984 (SELECT id FROM actor.search_query WHERE label = 'Young Adult Materials'),
987 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
989 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
990 (SELECT id FROM actor.search_query WHERE label = 'General/Adult Materials'),
995 -- Evergreen DB patch 0729.vr_format_value_maps.sql
998 -- check whether patch can be applied
999 SELECT evergreen.upgrade_deps_block_check('0729', :eg_version);
1001 CREATE OR REPLACE FUNCTION config.update_coded_value_map(in_ctype TEXT, in_code TEXT, in_value TEXT, in_description TEXT DEFAULT NULL, in_opac_visible BOOL DEFAULT NULL, in_search_label TEXT DEFAULT NULL, in_is_simple BOOL DEFAULT NULL, add_only BOOL DEFAULT FALSE) RETURNS VOID AS $f$
1003 current_row config.coded_value_map%ROWTYPE;
1005 -- Look for a current value
1006 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
1008 IF FOUND AND NOT add_only THEN
1009 -- Update anything we were handed
1010 current_row.value := COALESCE(current_row.value, in_value);
1011 current_row.description := COALESCE(current_row.description, in_description);
1012 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
1013 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
1014 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
1015 UPDATE config.coded_value_map
1017 value = current_row.value,
1018 description = current_row.description,
1019 opac_visible = current_row.opac_visible,
1020 search_label = current_row.search_label,
1021 is_simple = current_row.is_simple
1022 WHERE id = current_row.id;
1024 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
1025 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
1028 $f$ LANGUAGE PLPGSQL;
1030 SELECT config.update_coded_value_map('vr_format', 'a', 'Beta', add_only := TRUE);
1031 SELECT config.update_coded_value_map('vr_format', 'b', 'VHS', add_only := TRUE);
1032 SELECT config.update_coded_value_map('vr_format', 'c', 'U-matic', add_only := TRUE);
1033 SELECT config.update_coded_value_map('vr_format', 'd', 'EIAJ', add_only := TRUE);
1034 SELECT config.update_coded_value_map('vr_format', 'e', 'Type C', add_only := TRUE);
1035 SELECT config.update_coded_value_map('vr_format', 'f', 'Quadruplex', add_only := TRUE);
1036 SELECT config.update_coded_value_map('vr_format', 'g', 'Laserdisc', add_only := TRUE);
1037 SELECT config.update_coded_value_map('vr_format', 'h', 'CED videodisc', add_only := TRUE);
1038 SELECT config.update_coded_value_map('vr_format', 'i', 'Betacam', add_only := TRUE);
1039 SELECT config.update_coded_value_map('vr_format', 'j', 'Betacam SP', add_only := TRUE);
1040 SELECT config.update_coded_value_map('vr_format', 'k', 'Super-VHS', add_only := TRUE);
1041 SELECT config.update_coded_value_map('vr_format', 'm', 'M-II', add_only := TRUE);
1042 SELECT config.update_coded_value_map('vr_format', 'o', 'D-2', add_only := TRUE);
1043 SELECT config.update_coded_value_map('vr_format', 'p', '8 mm.', add_only := TRUE);
1044 SELECT config.update_coded_value_map('vr_format', 'q', 'Hi-8 mm.', add_only := TRUE);
1045 SELECT config.update_coded_value_map('vr_format', 's', 'Blu-ray disc', add_only := TRUE);
1046 SELECT config.update_coded_value_map('vr_format', 'u', 'Unknown', add_only := TRUE);
1047 SELECT config.update_coded_value_map('vr_format', 'v', 'DVD', add_only := TRUE);
1048 SELECT config.update_coded_value_map('vr_format', 'z', 'Other', add_only := TRUE);
1049 SELECT config.update_coded_value_map('vr_format', ' ', 'Unspecified', add_only := TRUE);
1053 SELECT evergreen.upgrade_deps_block_check('0730', :eg_version);
1055 DROP FUNCTION acq.propagate_funds_by_org_tree (INT, INT, INT);
1056 DROP FUNCTION acq.propagate_funds_by_org_unit (INT, INT, INT);
1058 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
1061 org_unit_id INTEGER,
1062 include_desc BOOL DEFAULT TRUE
1063 ) RETURNS VOID AS $$
1074 IF old_year IS NULL THEN
1075 RAISE EXCEPTION 'Input year argument is NULL';
1076 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1077 RAISE EXCEPTION 'Input year is out of range';
1080 IF user_id IS NULL THEN
1081 RAISE EXCEPTION 'Input user id argument is NULL';
1084 IF org_unit_id IS NULL THEN
1085 RAISE EXCEPTION 'Org unit id argument is NULL';
1087 SELECT TRUE INTO org_found
1089 WHERE id = org_unit_id;
1091 IF org_found IS NULL THEN
1092 RAISE EXCEPTION 'Org unit id is invalid';
1096 -- Loop over the applicable funds
1098 FOR old_fund in SELECT * FROM acq.fund
1102 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1103 OR (NOT include_desc AND org = org_unit_id ) )
1107 INSERT INTO acq.fund (
1115 balance_warning_percent,
1116 balance_stop_percent
1121 old_fund.currency_type,
1125 old_fund.balance_warning_percent,
1126 old_fund.balance_stop_percent
1128 RETURNING id INTO new_id;
1130 WHEN unique_violation THEN
1131 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1134 --RAISE NOTICE 'Propagating fund % to fund %',
1135 -- old_fund.code, new_id;
1138 $$ LANGUAGE plpgsql;
1140 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$
1141 SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE );
1145 DROP FUNCTION acq.rollover_funds_by_org_tree (INT, INT, INT);
1146 DROP FUNCTION acq.rollover_funds_by_org_unit (INT, INT, INT);
1149 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
1152 org_unit_id INTEGER,
1153 encumb_only BOOL DEFAULT FALSE,
1154 include_desc BOOL DEFAULT TRUE
1155 ) RETURNS VOID AS $$
1159 new_year INT := old_year + 1;
1162 xfer_amount NUMERIC := 0;
1171 IF old_year IS NULL THEN
1172 RAISE EXCEPTION 'Input year argument is NULL';
1173 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1174 RAISE EXCEPTION 'Input year is out of range';
1177 IF user_id IS NULL THEN
1178 RAISE EXCEPTION 'Input user id argument is NULL';
1181 IF org_unit_id IS NULL THEN
1182 RAISE EXCEPTION 'Org unit id argument is NULL';
1185 -- Validate the org unit
1190 WHERE id = org_unit_id;
1192 IF org_found IS NULL THEN
1193 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
1194 ELSIF encumb_only THEN
1195 SELECT INTO perm_ous value::BOOL FROM
1196 actor.org_unit_ancestor_setting(
1197 'acq.fund.allow_rollover_without_money', org_unit_id
1199 IF NOT FOUND OR NOT perm_ous THEN
1200 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
1205 -- Loop over the propagable funds to identify the details
1206 -- from the old fund plus the id of the new one, if it exists.
1210 oldf.id AS old_fund,
1216 newf.id AS new_fund_id
1219 LEFT JOIN acq.fund AS newf
1220 ON ( oldf.code = newf.code )
1222 oldf.year = old_year
1224 AND newf.year = new_year
1225 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1226 OR (NOT include_desc AND oldf.org = org_unit_id ) )
1228 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
1230 IF roll_fund.new_fund_id IS NULL THEN
1232 -- The old fund hasn't been propagated yet. Propagate it now.
1234 INSERT INTO acq.fund (
1242 balance_warning_percent,
1243 balance_stop_percent
1248 roll_fund.currency_type,
1252 roll_fund.balance_warning_percent,
1253 roll_fund.balance_stop_percent
1255 RETURNING id INTO new_fund;
1257 new_fund = roll_fund.new_fund_id;
1260 -- Determine the amount to transfer
1264 FROM acq.fund_spent_balance
1265 WHERE fund = roll_fund.old_fund;
1267 IF xfer_amount <> 0 THEN
1268 IF NOT encumb_only AND roll_fund.rollover THEN
1270 -- Transfer balance from old fund to new
1272 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
1274 PERFORM acq.transfer_fund(
1284 -- Transfer balance from old fund to the void
1286 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
1288 PERFORM acq.transfer_fund(
1294 'Rollover into the void'
1299 IF roll_fund.rollover THEN
1301 -- Move any lineitems from the old fund to the new one
1302 -- where the associated debit is an encumbrance.
1304 -- Any other tables tying expenditure details to funds should
1305 -- receive similar treatment. At this writing there are none.
1307 UPDATE acq.lineitem_detail
1310 fund = roll_fund.old_fund -- this condition may be redundant
1316 fund = roll_fund.old_fund
1320 -- Move encumbrance debits from the old fund to the new fund
1322 UPDATE acq.fund_debit
1325 fund = roll_fund.old_fund
1329 -- Mark old fund as inactive, now that we've closed it
1333 WHERE id = roll_fund.old_fund;
1336 $$ LANGUAGE plpgsql;
1338 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER, encumb_only BOOL DEFAULT FALSE ) RETURNS VOID AS $$
1339 SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE );
1342 INSERT into config.org_unit_setting_type
1343 (name, grp, label, description, datatype)
1345 'acq.fund.allow_rollover_without_money',
1348 'acq.fund.allow_rollover_without_money',
1349 'Allow funds to be rolled over without bringing the money along',
1354 'acq.fund.allow_rollover_without_money',
1355 '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.',
1362 -- 0731.schema.vandelay_item_overlay.sql
1364 SELECT evergreen.upgrade_deps_block_check('0731', :eg_version);
1366 ALTER TABLE vandelay.import_item_attr_definition
1367 ADD COLUMN internal_id TEXT;
1369 ALTER TABLE vandelay.import_item
1370 ADD COLUMN internal_id BIGINT;
1372 INSERT INTO permission.perm_list ( id, code, description ) VALUES
1373 ( 536, 'IMPORT_OVERLAY_COPY', oils_i18n_gettext( 536,
1374 'Allows a user to overlay copy data in MARC import', 'ppl', 'description'));
1376 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1379 item_data vandelay.import_item%ROWTYPE;
1382 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
1386 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1388 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1389 INSERT INTO vandelay.import_item (
1416 item_data.definition,
1417 item_data.owning_lib,
1419 item_data.call_number,
1420 item_data.copy_number,
1423 item_data.circulate,
1425 item_data.deposit_amount,
1430 item_data.circ_modifier,
1431 item_data.circ_as_type,
1432 item_data.alert_message,
1434 item_data.priv_note,
1435 item_data.internal_id,
1436 item_data.opac_visible,
1437 item_data.import_error,
1438 item_data.error_detail
1444 $func$ LANGUAGE PLPGSQL;
1447 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
1458 deposit_amount TEXT;
1472 tmp_attr_set RECORD;
1473 attr_set vandelay.import_item%ROWTYPE;
1480 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
1484 attr_set.definition := attr_def.id;
1486 -- Build the combined XPath
1490 WHEN attr_def.owning_lib IS NULL THEN 'null()'
1491 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
1492 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
1497 WHEN attr_def.circ_lib IS NULL THEN 'null()'
1498 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
1499 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
1504 WHEN attr_def.call_number IS NULL THEN 'null()'
1505 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
1506 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
1511 WHEN attr_def.copy_number IS NULL THEN 'null()'
1512 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
1513 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
1518 WHEN attr_def.status IS NULL THEN 'null()'
1519 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
1520 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
1525 WHEN attr_def.location IS NULL THEN 'null()'
1526 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
1527 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
1532 WHEN attr_def.circulate IS NULL THEN 'null()'
1533 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
1534 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
1539 WHEN attr_def.deposit IS NULL THEN 'null()'
1540 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
1541 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
1546 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
1547 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
1548 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
1553 WHEN attr_def.ref IS NULL THEN 'null()'
1554 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
1555 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
1560 WHEN attr_def.holdable IS NULL THEN 'null()'
1561 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
1562 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
1567 WHEN attr_def.price IS NULL THEN 'null()'
1568 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
1569 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
1574 WHEN attr_def.barcode IS NULL THEN 'null()'
1575 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
1576 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
1581 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
1582 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
1583 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
1588 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
1589 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
1590 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
1595 WHEN attr_def.alert_message IS NULL THEN 'null()'
1596 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
1597 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
1602 WHEN attr_def.opac_visible IS NULL THEN 'null()'
1603 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
1604 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
1609 WHEN attr_def.pub_note IS NULL THEN 'null()'
1610 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
1611 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
1615 WHEN attr_def.priv_note IS NULL THEN 'null()'
1616 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
1617 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
1622 WHEN attr_def.internal_id IS NULL THEN 'null()'
1623 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
1624 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
1630 owning_lib || '|' ||
1632 call_number || '|' ||
1633 copy_number || '|' ||
1638 deposit_amount || '|' ||
1643 circ_modifier || '|' ||
1644 circ_as_type || '|' ||
1645 alert_message || '|' ||
1648 internal_id || '|' ||
1653 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
1654 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
1655 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
1656 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
1659 attr_set.import_error := NULL;
1660 attr_set.error_detail := NULL;
1661 attr_set.deposit_amount := NULL;
1662 attr_set.copy_number := NULL;
1663 attr_set.price := NULL;
1665 IF tmp_attr_set.pr != '' THEN
1666 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
1667 IF tmp_str = '' THEN
1668 attr_set.import_error := 'import.item.invalid.price';
1669 attr_set.error_detail := tmp_attr_set.pr; -- original value
1670 RETURN NEXT attr_set; CONTINUE;
1672 attr_set.price := tmp_str::NUMERIC(8,2);
1675 IF tmp_attr_set.dep_amount != '' THEN
1676 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
1677 IF tmp_str = '' THEN
1678 attr_set.import_error := 'import.item.invalid.deposit_amount';
1679 attr_set.error_detail := tmp_attr_set.dep_amount;
1680 RETURN NEXT attr_set; CONTINUE;
1682 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
1685 IF tmp_attr_set.cnum != '' THEN
1686 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
1687 IF tmp_str = '' THEN
1688 attr_set.import_error := 'import.item.invalid.copy_number';
1689 attr_set.error_detail := tmp_attr_set.cnum;
1690 RETURN NEXT attr_set; CONTINUE;
1692 attr_set.copy_number := tmp_str::INT;
1695 IF tmp_attr_set.ol != '' THEN
1696 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
1698 attr_set.import_error := 'import.item.invalid.owning_lib';
1699 attr_set.error_detail := tmp_attr_set.ol;
1700 RETURN NEXT attr_set; CONTINUE;
1704 IF tmp_attr_set.clib != '' THEN
1705 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
1707 attr_set.import_error := 'import.item.invalid.circ_lib';
1708 attr_set.error_detail := tmp_attr_set.clib;
1709 RETURN NEXT attr_set; CONTINUE;
1713 IF tmp_attr_set.cs != '' THEN
1714 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
1716 attr_set.import_error := 'import.item.invalid.status';
1717 attr_set.error_detail := tmp_attr_set.cs;
1718 RETURN NEXT attr_set; CONTINUE;
1722 IF tmp_attr_set.circ_mod != '' THEN
1723 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
1725 attr_set.import_error := 'import.item.invalid.circ_modifier';
1726 attr_set.error_detail := tmp_attr_set.circ_mod;
1727 RETURN NEXT attr_set; CONTINUE;
1731 IF tmp_attr_set.circ_as != '' THEN
1732 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
1734 attr_set.import_error := 'import.item.invalid.circ_as_type';
1735 attr_set.error_detail := tmp_attr_set.circ_as;
1736 RETURN NEXT attr_set; CONTINUE;
1740 IF tmp_attr_set.cl != '' THEN
1742 -- search up the org unit tree for a matching copy location
1743 WITH RECURSIVE anscestor_depth AS (
1747 FROM actor.org_unit ou
1748 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1749 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
1754 FROM actor.org_unit ou
1755 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1756 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
1757 ) SELECT cpl.id INTO attr_set.location
1758 FROM anscestor_depth a
1759 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
1760 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
1761 ORDER BY a.depth DESC
1765 attr_set.import_error := 'import.item.invalid.location';
1766 attr_set.error_detail := tmp_attr_set.cs;
1767 RETURN NEXT attr_set; CONTINUE;
1771 attr_set.circulate :=
1772 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
1773 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
1776 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
1777 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
1779 attr_set.holdable :=
1780 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
1781 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
1783 attr_set.opac_visible :=
1784 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
1785 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
1788 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
1789 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
1791 attr_set.call_number := tmp_attr_set.cn; -- TEXT
1792 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1793 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1794 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1795 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1796 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1797 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
1799 RETURN NEXT attr_set;
1808 $$ LANGUAGE PLPGSQL;
1812 -- 0732.schema.acq-lineitem-summary.sql
1814 SELECT evergreen.upgrade_deps_block_check('0732', :eg_version);
1816 CREATE OR REPLACE VIEW acq.lineitem_summary AS
1820 SELECT COUNT(lid.id)
1821 FROM acq.lineitem_detail lid
1822 WHERE lineitem = li.id
1825 SELECT COUNT(lid.id)
1826 FROM acq.lineitem_detail lid
1827 WHERE recv_time IS NOT NULL AND lineitem = li.id
1830 SELECT COUNT(lid.id)
1831 FROM acq.lineitem_detail lid
1832 WHERE cancel_reason IS NOT NULL AND lineitem = li.id
1835 SELECT COUNT(lid.id)
1836 FROM acq.lineitem_detail lid
1837 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1838 WHERE NOT debit.encumbrance AND lineitem = li.id
1841 SELECT COUNT(DISTINCT(lid.id))
1842 FROM acq.lineitem_detail lid
1843 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
1844 WHERE lineitem = li.id
1847 SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
1848 FROM acq.lineitem_detail lid
1849 WHERE lid.cancel_reason IS NULL AND lineitem = li.id
1850 ) AS estimated_amount,
1852 SELECT SUM(debit.amount)::NUMERIC(8,2)
1853 FROM acq.lineitem_detail lid
1854 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1855 WHERE debit.encumbrance AND lineitem = li.id
1856 ) AS encumbrance_amount,
1858 SELECT SUM(debit.amount)::NUMERIC(8,2)
1859 FROM acq.lineitem_detail lid
1860 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1861 WHERE NOT debit.encumbrance AND lineitem = li.id
1864 FROM acq.lineitem AS li;
1869 -- Template update included here for reference only.
1870 -- The stock JEDI template is not updated here (see WHERE clause)
1871 -- We do update the environment, though, for easier local template
1872 -- updating. No env fields are removed (that aren't otherwise replaced).
1876 SELECT evergreen.upgrade_deps_block_check('0733', :eg_version);
1878 UPDATE action_trigger.event_definition SET template =
1880 [%# start JEDI document
1881 # Vendor specific kludges:
1882 # BT - vendcode goes to NAD/BY *suffix* w/ 91 qualifier
1883 # INGRAM - vendcode goes to NAD/BY *segment* w/ 91 qualifier (separately)
1884 # BRODART - vendcode goes to FTX segment (lineitem level)
1887 IF target.provider.edi_default.vendcode && target.provider.code == 'BRODART';
1888 xtra_ftx = target.provider.edi_default.vendcode;
1891 [%- BLOCK big_block -%]
1893 "recipient":"[% target.provider.san %]",
1894 "sender":"[% target.ordering_agency.mailing_address.san %]",
1896 "ORDERS":[ "order", {
1897 "po_number":[% target.id %],
1898 "date":"[% date.format(date.now, '%Y%m%d') %]",
1900 [% IF target.provider.edi_default.vendcode && (target.provider.code == 'BT' || target.provider.name.match('(?i)^BAKER & TAYLOR')) -%]
1901 {"id-qualifier": 91, "id":"[% target.ordering_agency.mailing_address.san _ ' ' _ target.provider.edi_default.vendcode %]"}
1902 [%- ELSIF target.provider.edi_default.vendcode && target.provider.code == 'INGRAM' -%]
1903 {"id":"[% target.ordering_agency.mailing_address.san %]"},
1904 {"id-qualifier": 91, "id":"[% target.provider.edi_default.vendcode %]"}
1906 {"id":"[% target.ordering_agency.mailing_address.san %]"}
1910 [%- # target.provider.name (target.provider.id) -%]
1911 "[% target.provider.san %]",
1912 {"id-qualifier": 92, "id":"[% target.provider.id %]"}
1914 "currency":"[% target.provider.currency_type %]",
1917 [%- FOR li IN target.lineitems %]
1919 "line_index":"[% li.id %]",
1920 "identifiers":[ [%-# li.isbns = helpers.get_li_isbns(li.attributes) %]
1921 [% FOR isbn IN helpers.get_li_isbns(li.attributes) -%]
1922 [% IF isbn.length == 13 -%]
1923 {"id-qualifier":"EN","id":"[% isbn %]"},
1925 {"id-qualifier":"IB","id":"[% isbn %]"},
1928 {"id-qualifier":"IN","id":"[% li.id %]"}
1930 "price":[% li.estimated_unit_price || '0.00' %],
1932 {"BTI":"[% helpers.get_li_attr_jedi('title', '', li.attributes) %]"},
1933 {"BPU":"[% helpers.get_li_attr_jedi('publisher', '', li.attributes) %]"},
1934 {"BPD":"[% helpers.get_li_attr_jedi('pubdate', '', li.attributes) %]"},
1935 {"BPH":"[% helpers.get_li_attr_jedi('pagination','', li.attributes) %]"}
1938 FOR note IN li.lineitem_notes;
1939 NEXT UNLESS note.vendor_public == 't';
1940 ftx_vals.push(note.value);
1942 IF xtra_ftx; ftx_vals.unshift(xtra_ftx); END;
1943 IF ftx_vals.size == 0; ftx_vals.unshift(''); END; # BT needs FTX+LIN for every LI, even if it is an empty one
1947 [% FOR note IN ftx_vals -%] "[% note %]"[% UNLESS loop.last %], [% END %][% END %]
1949 "quantity":[% li.lineitem_details.size %],
1952 [%- FOR lid IN li.lineitem_details;
1953 fund = lid.fund.code;
1954 item_type = lid.circ_modifier;
1955 callnumber = lid.cn_label;
1956 owning_lib = lid.owning_lib.shortname;
1957 location = lid.location;
1959 # when we have real copy data, treat it as authoritative
1960 acp = lid.eg_copy_id;
1962 item_type = acp.circ_modifier;
1963 callnumber = acp.call_number.label;
1964 location = acp.location.name;
1966 { [%- IF fund %] "fund" : "[% fund %]",[% END -%]
1967 [%- IF callnumber %] "call_number" : "[% callnumber %]", [% END -%]
1968 [%- IF item_type %] "item_type" : "[% item_type %]", [% END -%]
1969 [%- IF location %] "copy_location" : "[% location %]", [% END -%]
1970 [%- IF owning_lib %] "owning_lib" : "[% owning_lib %]", [% END -%]
1971 [%- #chomp %]"copy_id" : "[% lid.id %]" }[% ',' UNLESS loop.last %]
1975 }[% UNLESS loop.last %],[% END %]
1976 [%-# TODO: lineitem details (later) -%]
1979 "line_items":[% target.lineitems.size %]
1980 }] [%# close ORDERS array %]
1981 }] [%# close body array %]
1984 [% tempo = PROCESS big_block; helpers.escape_json(tempo) %]
1986 WHERE id = 23 AND FALSE; -- DON'T PERFORM THE UPDATE
1989 -- add copy-related fields to the environment if they're not already there.
1993 FROM action_trigger.environment
1996 path = 'lineitems.lineitem_details.owning_lib';
1998 INSERT INTO action_trigger.environment (event_def, path)
1999 VALUES (23, 'lineitems.lineitem_details.owning_lib');
2003 FROM action_trigger.environment
2006 path = 'lineitems.lineitem_details.fund';
2008 INSERT INTO action_trigger.environment (event_def, path)
2009 VALUES (23, 'lineitems.lineitem_details.fund');
2013 FROM action_trigger.environment
2016 path = 'lineitems.lineitem_details.location';
2018 INSERT INTO action_trigger.environment (event_def, path)
2019 VALUES (23, 'lineitems.lineitem_details.location');
2023 FROM action_trigger.environment
2026 path = 'lineitems.lineitem_details.eg_copy_id.location';
2028 INSERT INTO action_trigger.environment (event_def, path)
2029 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.location');
2033 FROM action_trigger.environment
2036 path = 'lineitems.lineitem_details.eg_copy_id.call_number';
2038 INSERT INTO action_trigger.environment (event_def, path)
2039 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.call_number');
2044 -- remove redundant entry
2045 DELETE FROM action_trigger.environment
2046 WHERE event_def = 23 AND path = 'lineitems.lineitem_details';
2051 -- Evergreen DB patch 0734.tpac_holdable_check.sql
2054 -- check whether patch can be applied
2055 SELECT evergreen.upgrade_deps_block_check('0734', :eg_version);
2057 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2062 JOIN asset.call_number acn ON acp.call_number = acn.id
2063 JOIN asset.copy_location acpl ON acp.location = acpl.id
2064 JOIN config.copy_status ccs ON acp.status = ccs.id
2067 AND acp.holdable = true
2068 AND acpl.holdable = true
2069 AND ccs.holdable = true
2070 AND acp.deleted = false
2077 $f$ LANGUAGE PLPGSQL;
2079 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2084 JOIN asset.call_number acn ON acp.call_number = acn.id
2085 JOIN asset.copy_location acpl ON acp.location = acpl.id
2086 JOIN config.copy_status ccs ON acp.status = ccs.id
2087 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
2089 mmsm.metarecord = rid
2090 AND acp.holdable = true
2091 AND acpl.holdable = true
2092 AND ccs.holdable = true
2093 AND acp.deleted = false
2100 $f$ LANGUAGE PLPGSQL;
2102 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
2106 depth INT DEFAULT NULL,
2107 includes TEXT[] DEFAULT NULL::TEXT[],
2108 slimit HSTORE DEFAULT NULL,
2109 soffset HSTORE DEFAULT NULL,
2110 include_xmlns BOOL DEFAULT TRUE,
2111 pref_lib INT DEFAULT NULL
2117 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2118 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
2119 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
2123 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
2126 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2128 FROM asset.opac_ou_record_copy_count($2, $1)
2132 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2134 FROM asset.staff_ou_record_copy_count($2, $1)
2138 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2140 FROM asset.opac_ou_record_copy_count($9, $1)
2145 WHEN ('bmp' = ANY ($5)) THEN
2147 name monograph_parts,
2148 (SELECT XMLAGG(bmp) FROM (
2149 SELECT unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
2150 FROM biblio.monograph_part
2158 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
2160 SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
2161 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
2164 SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), 0, name, label_sortkey
2165 FROM evergreen.located_uris($1, $2, $9) AS uris
2168 CASE WHEN ('ssub' = ANY ($5)) THEN
2171 (SELECT XMLAGG(ssub) FROM (
2172 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
2173 FROM serial.subscription
2174 WHERE record_entry = $1
2178 CASE WHEN ('acp' = ANY ($5)) THEN
2180 name foreign_copies,
2181 (SELECT XMLAGG(acp) FROM (
2182 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
2183 FROM biblio.peer_bib_copy_map p
2184 JOIN asset.copy c ON (p.target_copy = c.id)
2185 WHERE NOT c.deleted AND p.peer_record = $1
2186 LIMIT ($6 -> 'acp')::INT
2187 OFFSET ($7 -> 'acp')::INT
2192 $F$ LANGUAGE SQL STABLE;
2194 -- Evergreen DB patch 0735.data.search_filter_group_perms.sql
2197 -- check whether patch can be applied
2198 SELECT evergreen.upgrade_deps_block_check('0735', :eg_version);
2200 INSERT INTO permission.perm_list ( id, code, description )
2203 'ADMIN_SEARCH_FILTER_GROUP',
2206 'Allows staff to manage search filter groups and entries',
2213 'VIEW_SEARCH_FILTER_GROUP',
2216 'Allows staff to view search filter groups and entries',
2223 -- check whether patch can be applied
2224 SELECT evergreen.upgrade_deps_block_check('0737', :eg_version);
2226 UPDATE action_trigger.event_definition
2230 # target is the bookbag itself. The 'items' variable does not need to be in
2231 # the environment because a special reactor will take care of filling it in.
2234 bibxml = helpers.unapi_bre(item.target_biblio_record_entry, {flesh => '{mra}'});
2236 FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
2237 title = title _ part.textContent;
2239 author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
2240 item_type = bibxml.findnodes('//*[local-name()="attributes"]/*[local-name()="field"][@name="item_type"]').getAttribute('coded-value');
2242 FOR pdatum IN bibxml.findnodes('//*[@tag="260"]/*[@code="c"]');
2244 pub_date = pub_date _ ", " _ pdatum.textContent;
2246 pub_date = pdatum.textContent;
2249 helpers.csv_datum(title) %],[% helpers.csv_datum(author) %],[% helpers.csv_datum(pub_date) %],[% helpers.csv_datum(item_type) %],[% FOR note IN item.notes; helpers.csv_datum(note.note); ","; END; "\n";
2252 WHERE name = 'Bookbag CSV';
2256 \qecho Evergreen depends heavily on each bibliographic record containing
2257 \qecho a 901 field with a subfield "c" to hold the record ID. The following
2258 \qecho query identifies the bibs that are missing 901s or whose first
2259 \qecho 901$c is not equal to the bib ID. This *will* take a long time in a
2260 \qecho big database; as the schema updates are over now, you can cancel this
2261 \qecho if you are in a rush.
2264 FROM biblio.record_entry
2266 (XPATH('//marc:datafield[@tag="901"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT IS NULL
2268 (XPATH('//marc:datafield[@tag="901"][1]/marc:subfield[@code="c"]/text()', marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]))[1]::TEXT <> id::TEXT)
2271 \qecho If there are records with missing or incorrect 901$c values, you can
2272 \qecho generally rely on the triggers in the biblio.record_entry table to
2273 \qecho populate the 901$c properly; for each offending record, run:
2274 \qecho UPDATE biblio.record_entry SET marc = marc WHERE id = <id>;