1 --Upgrade Script for 2.2 to 2.3.0
3 \set eg_version '''2.3.0'''
5 \qecho The following statement might fail, and that is okay; we are
6 \qecho ensuring that an upgrade that should have been applied during
7 \qecho the 2.2 upgrade is actually applied now.
9 -- 0715.data.add_acq_config_group
10 INSERT INTO config.settings_group (name, label) VALUES
11 ('acq', oils_i18n_gettext('config.settings_group.system', 'Acquisitions', 'coust', 'label'));
13 UPDATE config.org_unit_setting_type
15 WHERE name LIKE 'acq%';
17 \qecho The real upgrade begins now.
20 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.0', :eg_version);
21 -- Evergreen DB patch 0703.tpac_value_maps.sql
23 -- check whether patch can be applied
24 SELECT evergreen.upgrade_deps_block_check('0703', :eg_version);
26 ALTER TABLE config.coded_value_map
27 ADD COLUMN opac_visible BOOL NOT NULL DEFAULT TRUE,
28 ADD COLUMN search_label TEXT,
29 ADD COLUMN is_simple BOOL NOT NULL DEFAULT FALSE;
33 SELECT evergreen.upgrade_deps_block_check('0712', :eg_version);
35 -- General purpose query container. Any table the needs to store
36 -- a QueryParser query should store it here. This will be the
37 -- source for top-level and QP sub-search inclusion queries.
38 CREATE TABLE actor.search_query (
39 id SERIAL PRIMARY KEY,
40 label TEXT NOT NULL, -- i18n
41 query_text TEXT NOT NULL -- QP text
44 -- e.g. "Reading Level"
45 CREATE TABLE actor.search_filter_group (
46 id SERIAL PRIMARY KEY,
47 owner INT NOT NULL REFERENCES actor.org_unit (id)
48 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
49 code TEXT NOT NULL, -- for CGI, etc.
50 label TEXT NOT NULL, -- i18n
51 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
52 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
53 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
56 -- e.g. "Adult", "Teen", etc.
57 CREATE TABLE actor.search_filter_group_entry (
58 id SERIAL PRIMARY KEY,
59 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
60 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
61 pos INT NOT NULL DEFAULT 0,
62 query INT NOT NULL REFERENCES actor.search_query(id)
63 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
64 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
68 SELECT evergreen.upgrade_deps_block_check('0713', :eg_version);
70 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
71 'ui.grid_columns.circ.hold_pull_list',
75 'ui.grid_columns.circ.hold_pull_list',
81 'ui.grid_columns.circ.hold_pull_list',
82 'Hold Pull List Saved Column Settings',
91 SELECT evergreen.upgrade_deps_block_check('0714', :eg_version);
93 INSERT into config.org_unit_setting_type
94 (name, grp, label, description, datatype)
96 'opac.patron.auto_overide_hold_events',
99 'opac.patron.auto_overide_hold_events',
100 'Auto-Override Permitted Hold Blocks (Patrons)',
105 'opac.patron.auto_overide_hold_events',
106 'When a patron places a hold that fails and the patron has the correct permission ' ||
107 'to override the hold, automatically override the hold without presenting a message ' ||
108 'to the patron and requiring that the patron make a decision to override',
115 -- Evergreen DB patch 0718.data.add-to-permanent-bookbag.sql
117 -- check whether patch can be applied
118 SELECT evergreen.upgrade_deps_block_check('0718', :eg_version);
120 INSERT into config.org_unit_setting_type
121 (name, grp, label, description, datatype)
123 'opac.patron.temporary_list_warn',
126 'opac.patron.temporary_list_warn',
127 'Warn patrons when adding to a temporary book list',
132 'opac.patron.temporary_list_warn',
133 'Present a warning dialog to the patron when a patron adds a book to a temporary book bag.',
140 INSERT INTO config.usr_setting_type
141 (name,grp,opac_visible,label,description,datatype)
143 'opac.temporary_list_no_warn',
147 'opac.temporary_list_no_warn',
148 'Opt out of warning when adding a book to a temporary book list',
153 'opac.temporary_list_no_warn',
154 'Opt out of warning when adding a book to a temporary book list',
161 INSERT INTO config.usr_setting_type
162 (name,grp,opac_visible,label,description,datatype)
169 'Default list to use when adding to a bookbag',
175 'Default list to use when adding to a bookbag',
183 SELECT evergreen.upgrade_deps_block_check('0719', :eg_version);
185 INSERT INTO config.org_unit_setting_type (
186 name, label, grp, description, datatype
188 'circ.staff.max_visible_event_age',
189 'Maximum visible age of User Trigger Events in Staff Interfaces',
191 '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.',
195 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
196 'ui.grid_columns.actor.user.event_log',
200 'ui.grid_columns.actor.user.event_log',
206 'ui.grid_columns.actor.user.event_log',
207 'User Event Log Saved Column Settings',
214 INSERT INTO permission.perm_list ( id, code, description )
217 'VIEW_TRIGGER_EVENT',
220 'Allows a user to view circ- and hold-related action/trigger events',
227 SELECT evergreen.upgrade_deps_block_check('0720', :eg_version);
229 ALTER TABLE config.circ_matrix_weights
230 ADD COLUMN copy_location NUMERIC(6,2) NOT NULL DEFAULT 5.0;
231 UPDATE config.circ_matrix_weights
232 SET copy_location = 0.0 WHERE name = 'All_Equal';
233 ALTER TABLE config.circ_matrix_weights
234 ALTER COLUMN copy_location DROP DEFAULT; -- for consistency w/ baseline schema
236 ALTER TABLE config.circ_matrix_matchpoint
237 ADD COLUMN copy_location INTEGER REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED;
239 DROP INDEX config.ccmm_once_per_paramset;
241 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;
243 -- Linkage between limit sets and circ mods
244 CREATE TABLE config.circ_limit_set_copy_loc_map (
245 id SERIAL PRIMARY KEY,
246 limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
247 copy_loc INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
248 CONSTRAINT cl_once_per_set UNIQUE (limit_set, copy_loc)
251 -- Add support for checking config.circ_limit_set_copy_loc_map's
252 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL )
253 RETURNS SETOF action.circ_matrix_test_result AS $func$
255 user_object actor.usr%ROWTYPE;
256 standing_penalty config.standing_penalty%ROWTYPE;
257 item_object asset.copy%ROWTYPE;
258 item_status_object config.copy_status%ROWTYPE;
259 item_location_object asset.copy_location%ROWTYPE;
260 result action.circ_matrix_test_result;
261 circ_test action.found_circ_matrix_matchpoint;
262 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
263 circ_limit_set config.circ_limit_set%ROWTYPE;
264 hold_ratio action.hold_stats%ROWTYPE;
267 context_org_list INT[];
270 -- Assume success unless we hit a failure condition
271 result.success := TRUE;
273 -- Need user info to look up matchpoints
274 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
276 -- (Insta)Fail if we couldn't find the user
277 IF user_object.id IS NULL THEN
278 result.fail_part := 'no_user';
279 result.success := FALSE;
285 -- Need item info to look up matchpoints
286 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
288 -- (Insta)Fail if we couldn't find the item
289 IF item_object.id IS NULL THEN
290 result.fail_part := 'no_item';
291 result.success := FALSE;
297 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
299 circ_matchpoint := circ_test.matchpoint;
300 result.matchpoint := circ_matchpoint.id;
301 result.circulate := circ_matchpoint.circulate;
302 result.duration_rule := circ_matchpoint.duration_rule;
303 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
304 result.max_fine_rule := circ_matchpoint.max_fine_rule;
305 result.hard_due_date := circ_matchpoint.hard_due_date;
306 result.renewals := circ_matchpoint.renewals;
307 result.grace_period := circ_matchpoint.grace_period;
308 result.buildrows := circ_test.buildrows;
310 -- (Insta)Fail if we couldn't find a matchpoint
311 IF circ_test.success = false THEN
312 result.fail_part := 'no_matchpoint';
313 result.success := FALSE;
319 -- All failures before this point are non-recoverable
320 -- Below this point are possibly overridable failures
322 -- Fail if the user is barred
323 IF user_object.barred IS TRUE THEN
324 result.fail_part := 'actor.usr.barred';
325 result.success := FALSE;
330 -- Fail if the item can't circulate
331 IF item_object.circulate IS FALSE THEN
332 result.fail_part := 'asset.copy.circulate';
333 result.success := FALSE;
338 -- Fail if the item isn't in a circulateable status on a non-renewal
339 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
340 result.fail_part := 'asset.copy.status';
341 result.success := FALSE;
344 -- Alternately, fail if the item isn't checked out on a renewal
345 ELSIF renewal AND item_object.status <> 1 THEN
346 result.fail_part := 'asset.copy.status';
347 result.success := FALSE;
352 -- Fail if the item can't circulate because of the shelving location
353 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
354 IF item_location_object.circulate IS FALSE THEN
355 result.fail_part := 'asset.copy_location.circulate';
356 result.success := FALSE;
361 -- Use Circ OU for penalties and such
362 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
365 penalty_type = '%RENEW%';
367 penalty_type = '%CIRC%';
370 FOR standing_penalty IN
371 SELECT DISTINCT csp.*
372 FROM actor.usr_standing_penalty usp
373 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
374 WHERE usr = match_user
375 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
376 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
377 AND csp.block_list LIKE penalty_type LOOP
379 result.fail_part := standing_penalty.name;
380 result.success := FALSE;
385 -- Fail if the test is set to hard non-circulating
386 IF circ_matchpoint.circulate IS FALSE THEN
387 result.fail_part := 'config.circ_matrix_test.circulate';
388 result.success := FALSE;
393 -- Fail if the total copy-hold ratio is too low
394 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
395 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
396 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
397 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
398 result.success := FALSE;
404 -- Fail if the available copy-hold ratio is too low
405 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
406 IF hold_ratio.hold_count IS NULL THEN
407 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
409 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
410 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
411 result.success := FALSE;
417 -- Fail if the user has too many items out by defined limit sets
418 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
419 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
420 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
421 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
423 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
424 SELECT INTO context_org_list ARRAY_AGG(aou.id)
425 FROM actor.org_unit_full_path( circ_ou ) aou
426 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
427 WHERE aout.depth >= circ_limit_set.depth;
428 IF circ_limit_set.global THEN
429 WITH RECURSIVE descendant_depth AS (
432 FROM actor.org_unit ou
433 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
437 FROM actor.org_unit ou
438 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
439 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
441 SELECT INTO items_out COUNT(DISTINCT circ.id)
442 FROM action.circulation circ
443 JOIN asset.copy copy ON (copy.id = circ.target_copy)
444 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
445 WHERE circ.usr = match_user
446 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
447 AND circ.checkin_time IS NULL
448 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
449 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
450 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
451 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
453 IF items_out >= circ_limit_set.items_out THEN
454 result.fail_part := 'config.circ_matrix_circ_mod_test';
455 result.success := FALSE;
460 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;
463 -- If we passed everything, return the successful matchpoint
470 $func$ LANGUAGE plpgsql;
473 -- adding copy_loc to circ_matrix_matchpoint
474 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$
476 cn_object asset.call_number%ROWTYPE;
477 rec_descriptor metabib.rec_descriptor%ROWTYPE;
478 cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
479 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
480 weights config.circ_matrix_weights%ROWTYPE;
482 my_item_age INTERVAL;
483 denominator NUMERIC(6,2);
485 result action.found_circ_matrix_matchpoint;
488 result.success = false;
491 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
492 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
494 -- Pre-generate this so we only calc it once
495 IF user_object.dob IS NOT NULL THEN
496 SELECT INTO user_age age(user_object.dob);
500 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
502 -- Grab the closest set circ weight setting.
503 SELECT INTO weights cw.*
504 FROM config.weight_assoc wa
505 JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
506 JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
511 -- No weights? Bad admin! Defaults to handle that anyway.
512 IF weights.id IS NULL THEN
514 weights.org_unit := 10.0;
515 weights.circ_modifier := 5.0;
516 weights.copy_location := 5.0;
517 weights.marc_type := 4.0;
518 weights.marc_form := 3.0;
519 weights.marc_bib_level := 2.0;
520 weights.marc_vr_format := 2.0;
521 weights.copy_circ_lib := 8.0;
522 weights.copy_owning_lib := 8.0;
523 weights.user_home_ou := 8.0;
524 weights.ref_flag := 1.0;
525 weights.juvenile_flag := 6.0;
526 weights.is_renewal := 7.0;
527 weights.usr_age_lower_bound := 0.0;
528 weights.usr_age_upper_bound := 0.0;
529 weights.item_age := 0.0;
532 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
533 -- If you break your org tree with funky parenting this may be wrong
534 -- 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
535 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
536 WITH all_distance(distance) AS (
537 SELECT depth AS distance FROM actor.org_unit_type
539 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
541 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
543 -- Loop over all the potential matchpoints
544 FOR cur_matchpoint IN
546 FROM config.circ_matrix_matchpoint m
547 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
548 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
549 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
550 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
551 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
554 -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
556 -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
557 AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
558 AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
559 AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
561 AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
562 -- Static User Checks
563 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
564 AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
565 AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
566 -- Static Item Checks
567 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
568 AND (m.copy_location IS NULL OR m.copy_location = item_object.location)
569 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
570 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
571 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
572 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
573 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
574 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
577 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
579 CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
580 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
581 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
582 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
583 -- Circ Type -- Note: 4^x is equiv to 2^(2*x)
584 CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
585 -- Static User Checks
586 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
587 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
588 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
589 -- Static Item Checks
590 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
591 CASE WHEN m.copy_location IS NOT NULL THEN 4^weights.copy_location ELSE 0.0 END +
592 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
593 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
594 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
595 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
596 -- Item age has a slight adjustment to weight based on value.
597 -- This should ensure that a shorter age limit comes first when all else is equal.
598 -- NOTE: This assumes that intervals will normally be in days.
599 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,
600 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
601 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
604 -- Record the full matching row list
605 row_list := row_list || cur_matchpoint.id;
607 -- No matchpoint yet?
608 IF matchpoint.id IS NULL THEN
609 -- Take the entire matchpoint as a starting point
610 matchpoint := cur_matchpoint;
611 CONTINUE; -- No need to look at this row any more.
614 -- Incomplete matchpoint?
615 IF matchpoint.circulate IS NULL THEN
616 matchpoint.circulate := cur_matchpoint.circulate;
618 IF matchpoint.duration_rule IS NULL THEN
619 matchpoint.duration_rule := cur_matchpoint.duration_rule;
621 IF matchpoint.recurring_fine_rule IS NULL THEN
622 matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
624 IF matchpoint.max_fine_rule IS NULL THEN
625 matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
627 IF matchpoint.hard_due_date IS NULL THEN
628 matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
630 IF matchpoint.total_copy_hold_ratio IS NULL THEN
631 matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
633 IF matchpoint.available_copy_hold_ratio IS NULL THEN
634 matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
636 IF matchpoint.renewals IS NULL THEN
637 matchpoint.renewals := cur_matchpoint.renewals;
639 IF matchpoint.grace_period IS NULL THEN
640 matchpoint.grace_period := cur_matchpoint.grace_period;
644 -- Check required fields
645 IF matchpoint.circulate IS NOT NULL AND
646 matchpoint.duration_rule IS NOT NULL AND
647 matchpoint.recurring_fine_rule IS NOT NULL AND
648 matchpoint.max_fine_rule IS NOT NULL THEN
649 -- All there? We have a completed match.
650 result.success := true;
653 -- Include the assembled matchpoint, even if it isn't complete
654 result.matchpoint := matchpoint;
656 -- Include (for debugging) the full list of matching rows
657 result.buildrows := row_list;
659 -- Hand the result back to caller
662 $func$ LANGUAGE plpgsql;
667 SELECT evergreen.upgrade_deps_block_check('0721', :eg_version);
669 UPDATE config.standing_penalty
670 SET block_list = REPLACE(block_list, 'HOLD', 'HOLD|CAPTURE')
672 -- STAFF_ penalties have names that match their block list
673 name NOT LIKE 'STAFF_%'
674 -- belt & suspenders, also good for testing
675 AND block_list NOT LIKE '%CAPTURE%';
677 -- CIRC|FULFILL is now the same as CIRC previously was by itself
678 UPDATE config.standing_penalty
679 SET block_list = REPLACE(block_list, 'CIRC', 'CIRC|FULFILL')
681 -- STAFF_ penalties have names that match their block list
682 name NOT LIKE 'STAFF_%'
683 -- belt & suspenders, also good for testing
684 AND block_list NOT LIKE '%FULFILL%';
687 -- apply the HOLD vs CAPTURE block logic
688 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$
691 user_object actor.usr%ROWTYPE;
692 age_protect_object config.rule_age_hold_protect%ROWTYPE;
693 standing_penalty config.standing_penalty%ROWTYPE;
694 transit_range_ou_type actor.org_unit_type%ROWTYPE;
695 transit_source actor.org_unit%ROWTYPE;
696 item_object asset.copy%ROWTYPE;
697 item_cn_object asset.call_number%ROWTYPE;
698 item_status_object config.copy_status%ROWTYPE;
699 item_location_object asset.copy_location%ROWTYPE;
700 ou_skip actor.org_unit_setting%ROWTYPE;
701 result action.matrix_test_result;
702 hold_test config.hold_matrix_matchpoint%ROWTYPE;
703 use_active_date TEXT;
704 age_protect_date TIMESTAMP WITH TIME ZONE;
706 hold_transit_prox INT;
707 frozen_hold_count INT;
708 context_org_list INT[];
712 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
713 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
715 result.success := TRUE;
717 -- The HOLD penalty block only applies to new holds.
718 -- The CAPTURE penalty block applies to existing holds.
719 hold_penalty := 'HOLD';
721 hold_penalty := 'CAPTURE';
724 -- Fail if we couldn't find a user
725 IF user_object.id IS NULL THEN
726 result.fail_part := 'no_user';
727 result.success := FALSE;
733 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
735 -- Fail if we couldn't find a copy
736 IF item_object.id IS NULL THEN
737 result.fail_part := 'no_item';
738 result.success := FALSE;
744 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
745 result.matchpoint := matchpoint_id;
747 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
749 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
750 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
751 result.fail_part := 'circ.holds.target_skip_me';
752 result.success := FALSE;
758 -- Fail if user is barred
759 IF user_object.barred IS TRUE THEN
760 result.fail_part := 'actor.usr.barred';
761 result.success := FALSE;
767 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
768 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
769 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
771 -- Fail if we couldn't find any matchpoint (requires a default)
772 IF matchpoint_id IS NULL THEN
773 result.fail_part := 'no_matchpoint';
774 result.success := FALSE;
780 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
782 IF hold_test.holdable IS FALSE THEN
783 result.fail_part := 'config.hold_matrix_test.holdable';
784 result.success := FALSE;
789 IF item_object.holdable IS FALSE THEN
790 result.fail_part := 'item.holdable';
791 result.success := FALSE;
796 IF item_status_object.holdable IS FALSE THEN
797 result.fail_part := 'status.holdable';
798 result.success := FALSE;
803 IF item_location_object.holdable IS FALSE THEN
804 result.fail_part := 'location.holdable';
805 result.success := FALSE;
810 IF hold_test.transit_range IS NOT NULL THEN
811 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
812 IF hold_test.distance_is_from_owner THEN
813 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;
815 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
818 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
821 result.fail_part := 'transit_range';
822 result.success := FALSE;
828 FOR standing_penalty IN
829 SELECT DISTINCT csp.*
830 FROM actor.usr_standing_penalty usp
831 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
832 WHERE usr = match_user
833 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
834 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
835 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
837 result.fail_part := standing_penalty.name;
838 result.success := FALSE;
843 IF hold_test.stop_blocked_user IS TRUE THEN
844 FOR standing_penalty IN
845 SELECT DISTINCT csp.*
846 FROM actor.usr_standing_penalty usp
847 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
848 WHERE usr = match_user
849 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
850 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
851 AND csp.block_list LIKE '%CIRC%' LOOP
853 result.fail_part := standing_penalty.name;
854 result.success := FALSE;
860 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
861 SELECT INTO hold_count COUNT(*)
862 FROM action.hold_request
863 WHERE usr = match_user
864 AND fulfillment_time IS NULL
865 AND cancel_time IS NULL
866 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
868 IF hold_count >= hold_test.max_holds THEN
869 result.fail_part := 'config.hold_matrix_test.max_holds';
870 result.success := FALSE;
876 IF item_object.age_protect IS NOT NULL THEN
877 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
878 IF hold_test.distance_is_from_owner THEN
879 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
881 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
883 IF use_active_date = 'true' THEN
884 age_protect_date := COALESCE(item_object.active_date, NOW());
886 age_protect_date := item_object.create_date;
888 IF age_protect_date + age_protect_object.age > NOW() THEN
889 IF hold_test.distance_is_from_owner THEN
890 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
891 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
893 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
896 IF hold_transit_prox > age_protect_object.prox THEN
897 result.fail_part := 'config.rule_age_hold_protect.prox';
898 result.success := FALSE;
911 $func$ LANGUAGE plpgsql;
914 -- Evergreen DB patch 0727.function.xml_pretty_print.sql
916 -- A simple pretty printer for XML.
917 -- Particularly useful for debugging the biblio.record_entry.marc field.
920 -- check whether patch can be applied
921 SELECT evergreen.upgrade_deps_block_check('0727', :eg_version);
923 CREATE OR REPLACE FUNCTION evergreen.xml_pretty_print(input XML)
927 SELECT xslt_process($1::text,
928 $$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
930 <xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
931 <xsl:strip-space elements="*"/>
932 <xsl:template match="@*|node()">
934 <xsl:apply-templates select="@*|node()"/>
941 COMMENT ON FUNCTION evergreen.xml_pretty_print(input XML) IS
942 'Simple pretty printer for XML, as written by Andrew Dunstan at http://goo.gl/zBHIk';
945 SELECT evergreen.upgrade_deps_block_check('0728', :eg_version);
947 INSERT INTO actor.search_filter_group (owner, code, label)
948 VALUES (1, 'kpac_main', 'Kid''s OPAC Search Filter');
950 INSERT INTO actor.search_query (label, query_text)
951 VALUES ('Children''s Materials', 'audience(a,b,c)');
952 INSERT INTO actor.search_query (label, query_text)
953 VALUES ('Young Adult Materials', 'audience(j,d)');
954 INSERT INTO actor.search_query (label, query_text)
955 VALUES ('General/Adult Materials', 'audience(e,f,g, )');
957 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
959 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
960 (SELECT id FROM actor.search_query WHERE label = 'Children''s Materials'),
963 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
965 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
966 (SELECT id FROM actor.search_query WHERE label = 'Young Adult Materials'),
969 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
971 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
972 (SELECT id FROM actor.search_query WHERE label = 'General/Adult Materials'),
977 -- Evergreen DB patch 0729.vr_format_value_maps.sql
980 -- check whether patch can be applied
981 SELECT evergreen.upgrade_deps_block_check('0729', :eg_version);
983 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$
985 current_row config.coded_value_map%ROWTYPE;
987 -- Look for a current value
988 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
990 IF FOUND AND NOT add_only THEN
991 -- Update anything we were handed
992 current_row.value := COALESCE(current_row.value, in_value);
993 current_row.description := COALESCE(current_row.description, in_description);
994 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
995 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
996 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
997 UPDATE config.coded_value_map
999 value = current_row.value,
1000 description = current_row.description,
1001 opac_visible = current_row.opac_visible,
1002 search_label = current_row.search_label,
1003 is_simple = current_row.is_simple
1004 WHERE id = current_row.id;
1006 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
1007 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
1010 $f$ LANGUAGE PLPGSQL;
1012 SELECT config.update_coded_value_map('vr_format', 'a', 'Beta', add_only := TRUE);
1013 SELECT config.update_coded_value_map('vr_format', 'b', 'VHS', add_only := TRUE);
1014 SELECT config.update_coded_value_map('vr_format', 'c', 'U-matic', add_only := TRUE);
1015 SELECT config.update_coded_value_map('vr_format', 'd', 'EIAJ', add_only := TRUE);
1016 SELECT config.update_coded_value_map('vr_format', 'e', 'Type C', add_only := TRUE);
1017 SELECT config.update_coded_value_map('vr_format', 'f', 'Quadruplex', add_only := TRUE);
1018 SELECT config.update_coded_value_map('vr_format', 'g', 'Laserdisc', add_only := TRUE);
1019 SELECT config.update_coded_value_map('vr_format', 'h', 'CED videodisc', add_only := TRUE);
1020 SELECT config.update_coded_value_map('vr_format', 'i', 'Betacam', add_only := TRUE);
1021 SELECT config.update_coded_value_map('vr_format', 'j', 'Betacam SP', add_only := TRUE);
1022 SELECT config.update_coded_value_map('vr_format', 'k', 'Super-VHS', add_only := TRUE);
1023 SELECT config.update_coded_value_map('vr_format', 'm', 'M-II', add_only := TRUE);
1024 SELECT config.update_coded_value_map('vr_format', 'o', 'D-2', add_only := TRUE);
1025 SELECT config.update_coded_value_map('vr_format', 'p', '8 mm.', add_only := TRUE);
1026 SELECT config.update_coded_value_map('vr_format', 'q', 'Hi-8 mm.', add_only := TRUE);
1027 SELECT config.update_coded_value_map('vr_format', 's', 'Blu-ray disc', add_only := TRUE);
1028 SELECT config.update_coded_value_map('vr_format', 'u', 'Unknown', add_only := TRUE);
1029 SELECT config.update_coded_value_map('vr_format', 'v', 'DVD', add_only := TRUE);
1030 SELECT config.update_coded_value_map('vr_format', 'z', 'Other', add_only := TRUE);
1031 SELECT config.update_coded_value_map('vr_format', ' ', 'Unspecified', add_only := TRUE);
1035 SELECT evergreen.upgrade_deps_block_check('0730', :eg_version);
1037 DROP FUNCTION acq.propagate_funds_by_org_tree (INT, INT, INT);
1038 DROP FUNCTION acq.propagate_funds_by_org_unit (INT, INT, INT);
1040 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
1043 org_unit_id INTEGER,
1044 include_desc BOOL DEFAULT TRUE
1045 ) RETURNS VOID AS $$
1056 IF old_year IS NULL THEN
1057 RAISE EXCEPTION 'Input year argument is NULL';
1058 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1059 RAISE EXCEPTION 'Input year is out of range';
1062 IF user_id IS NULL THEN
1063 RAISE EXCEPTION 'Input user id argument is NULL';
1066 IF org_unit_id IS NULL THEN
1067 RAISE EXCEPTION 'Org unit id argument is NULL';
1069 SELECT TRUE INTO org_found
1071 WHERE id = org_unit_id;
1073 IF org_found IS NULL THEN
1074 RAISE EXCEPTION 'Org unit id is invalid';
1078 -- Loop over the applicable funds
1080 FOR old_fund in SELECT * FROM acq.fund
1084 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1085 OR (NOT include_desc AND org = org_unit_id ) )
1089 INSERT INTO acq.fund (
1097 balance_warning_percent,
1098 balance_stop_percent
1103 old_fund.currency_type,
1107 old_fund.balance_warning_percent,
1108 old_fund.balance_stop_percent
1110 RETURNING id INTO new_id;
1112 WHEN unique_violation THEN
1113 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1116 --RAISE NOTICE 'Propagating fund % to fund %',
1117 -- old_fund.code, new_id;
1120 $$ LANGUAGE plpgsql;
1122 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$
1123 SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE );
1127 DROP FUNCTION acq.rollover_funds_by_org_tree (INT, INT, INT);
1128 DROP FUNCTION acq.rollover_funds_by_org_unit (INT, INT, INT);
1131 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
1134 org_unit_id INTEGER,
1135 encumb_only BOOL DEFAULT FALSE,
1136 include_desc BOOL DEFAULT TRUE
1137 ) RETURNS VOID AS $$
1141 new_year INT := old_year + 1;
1144 xfer_amount NUMERIC := 0;
1153 IF old_year IS NULL THEN
1154 RAISE EXCEPTION 'Input year argument is NULL';
1155 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1156 RAISE EXCEPTION 'Input year is out of range';
1159 IF user_id IS NULL THEN
1160 RAISE EXCEPTION 'Input user id argument is NULL';
1163 IF org_unit_id IS NULL THEN
1164 RAISE EXCEPTION 'Org unit id argument is NULL';
1167 -- Validate the org unit
1172 WHERE id = org_unit_id;
1174 IF org_found IS NULL THEN
1175 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
1176 ELSIF encumb_only THEN
1177 SELECT INTO perm_ous value::BOOL FROM
1178 actor.org_unit_ancestor_setting(
1179 'acq.fund.allow_rollover_without_money', org_unit_id
1181 IF NOT FOUND OR NOT perm_ous THEN
1182 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
1187 -- Loop over the propagable funds to identify the details
1188 -- from the old fund plus the id of the new one, if it exists.
1192 oldf.id AS old_fund,
1198 newf.id AS new_fund_id
1201 LEFT JOIN acq.fund AS newf
1202 ON ( oldf.code = newf.code )
1204 oldf.year = old_year
1206 AND newf.year = new_year
1207 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1208 OR (NOT include_desc AND oldf.org = org_unit_id ) )
1210 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
1212 IF roll_fund.new_fund_id IS NULL THEN
1214 -- The old fund hasn't been propagated yet. Propagate it now.
1216 INSERT INTO acq.fund (
1224 balance_warning_percent,
1225 balance_stop_percent
1230 roll_fund.currency_type,
1234 roll_fund.balance_warning_percent,
1235 roll_fund.balance_stop_percent
1237 RETURNING id INTO new_fund;
1239 new_fund = roll_fund.new_fund_id;
1242 -- Determine the amount to transfer
1246 FROM acq.fund_spent_balance
1247 WHERE fund = roll_fund.old_fund;
1249 IF xfer_amount <> 0 THEN
1250 IF NOT encumb_only AND roll_fund.rollover THEN
1252 -- Transfer balance from old fund to new
1254 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
1256 PERFORM acq.transfer_fund(
1266 -- Transfer balance from old fund to the void
1268 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
1270 PERFORM acq.transfer_fund(
1276 'Rollover into the void'
1281 IF roll_fund.rollover THEN
1283 -- Move any lineitems from the old fund to the new one
1284 -- where the associated debit is an encumbrance.
1286 -- Any other tables tying expenditure details to funds should
1287 -- receive similar treatment. At this writing there are none.
1289 UPDATE acq.lineitem_detail
1292 fund = roll_fund.old_fund -- this condition may be redundant
1298 fund = roll_fund.old_fund
1302 -- Move encumbrance debits from the old fund to the new fund
1304 UPDATE acq.fund_debit
1307 fund = roll_fund.old_fund
1311 -- Mark old fund as inactive, now that we've closed it
1315 WHERE id = roll_fund.old_fund;
1318 $$ LANGUAGE plpgsql;
1320 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 $$
1321 SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE );
1324 INSERT into config.org_unit_setting_type
1325 (name, grp, label, description, datatype)
1327 'acq.fund.allow_rollover_without_money',
1330 'acq.fund.allow_rollover_without_money',
1331 'Allow funds to be rolled over without bringing the money along',
1336 'acq.fund.allow_rollover_without_money',
1337 '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.',
1344 -- 0731.schema.vandelay_item_overlay.sql
1346 SELECT evergreen.upgrade_deps_block_check('0731', :eg_version);
1348 ALTER TABLE vandelay.import_item_attr_definition
1349 ADD COLUMN internal_id TEXT;
1351 ALTER TABLE vandelay.import_item
1352 ADD COLUMN internal_id BIGINT;
1354 INSERT INTO permission.perm_list ( id, code, description ) VALUES
1355 ( 536, 'IMPORT_OVERLAY_COPY', oils_i18n_gettext( 536,
1356 'Allows a user to overlay copy data in MARC import', 'ppl', 'description'));
1358 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1361 item_data vandelay.import_item%ROWTYPE;
1364 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
1368 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1370 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1371 INSERT INTO vandelay.import_item (
1398 item_data.definition,
1399 item_data.owning_lib,
1401 item_data.call_number,
1402 item_data.copy_number,
1405 item_data.circulate,
1407 item_data.deposit_amount,
1412 item_data.circ_modifier,
1413 item_data.circ_as_type,
1414 item_data.alert_message,
1416 item_data.priv_note,
1417 item_data.internal_id,
1418 item_data.opac_visible,
1419 item_data.import_error,
1420 item_data.error_detail
1426 $func$ LANGUAGE PLPGSQL;
1429 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
1440 deposit_amount TEXT;
1454 tmp_attr_set RECORD;
1455 attr_set vandelay.import_item%ROWTYPE;
1462 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
1466 attr_set.definition := attr_def.id;
1468 -- Build the combined XPath
1472 WHEN attr_def.owning_lib IS NULL THEN 'null()'
1473 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
1474 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
1479 WHEN attr_def.circ_lib IS NULL THEN 'null()'
1480 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
1481 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
1486 WHEN attr_def.call_number IS NULL THEN 'null()'
1487 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
1488 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
1493 WHEN attr_def.copy_number IS NULL THEN 'null()'
1494 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
1495 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
1500 WHEN attr_def.status IS NULL THEN 'null()'
1501 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
1502 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
1507 WHEN attr_def.location IS NULL THEN 'null()'
1508 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
1509 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
1514 WHEN attr_def.circulate IS NULL THEN 'null()'
1515 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
1516 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
1521 WHEN attr_def.deposit IS NULL THEN 'null()'
1522 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
1523 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
1528 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
1529 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
1530 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
1535 WHEN attr_def.ref IS NULL THEN 'null()'
1536 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
1537 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
1542 WHEN attr_def.holdable IS NULL THEN 'null()'
1543 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
1544 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
1549 WHEN attr_def.price IS NULL THEN 'null()'
1550 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
1551 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
1556 WHEN attr_def.barcode IS NULL THEN 'null()'
1557 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
1558 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
1563 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
1564 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
1565 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
1570 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
1571 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
1572 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
1577 WHEN attr_def.alert_message IS NULL THEN 'null()'
1578 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
1579 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
1584 WHEN attr_def.opac_visible IS NULL THEN 'null()'
1585 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
1586 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
1591 WHEN attr_def.pub_note IS NULL THEN 'null()'
1592 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
1593 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
1597 WHEN attr_def.priv_note IS NULL THEN 'null()'
1598 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
1599 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
1604 WHEN attr_def.internal_id IS NULL THEN 'null()'
1605 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
1606 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
1612 owning_lib || '|' ||
1614 call_number || '|' ||
1615 copy_number || '|' ||
1620 deposit_amount || '|' ||
1625 circ_modifier || '|' ||
1626 circ_as_type || '|' ||
1627 alert_message || '|' ||
1630 internal_id || '|' ||
1635 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
1636 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
1637 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
1638 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
1641 attr_set.import_error := NULL;
1642 attr_set.error_detail := NULL;
1643 attr_set.deposit_amount := NULL;
1644 attr_set.copy_number := NULL;
1645 attr_set.price := NULL;
1647 IF tmp_attr_set.pr != '' THEN
1648 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
1649 IF tmp_str = '' THEN
1650 attr_set.import_error := 'import.item.invalid.price';
1651 attr_set.error_detail := tmp_attr_set.pr; -- original value
1652 RETURN NEXT attr_set; CONTINUE;
1654 attr_set.price := tmp_str::NUMERIC(8,2);
1657 IF tmp_attr_set.dep_amount != '' THEN
1658 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
1659 IF tmp_str = '' THEN
1660 attr_set.import_error := 'import.item.invalid.deposit_amount';
1661 attr_set.error_detail := tmp_attr_set.dep_amount;
1662 RETURN NEXT attr_set; CONTINUE;
1664 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
1667 IF tmp_attr_set.cnum != '' THEN
1668 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
1669 IF tmp_str = '' THEN
1670 attr_set.import_error := 'import.item.invalid.copy_number';
1671 attr_set.error_detail := tmp_attr_set.cnum;
1672 RETURN NEXT attr_set; CONTINUE;
1674 attr_set.copy_number := tmp_str::INT;
1677 IF tmp_attr_set.ol != '' THEN
1678 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
1680 attr_set.import_error := 'import.item.invalid.owning_lib';
1681 attr_set.error_detail := tmp_attr_set.ol;
1682 RETURN NEXT attr_set; CONTINUE;
1686 IF tmp_attr_set.clib != '' THEN
1687 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
1689 attr_set.import_error := 'import.item.invalid.circ_lib';
1690 attr_set.error_detail := tmp_attr_set.clib;
1691 RETURN NEXT attr_set; CONTINUE;
1695 IF tmp_attr_set.cs != '' THEN
1696 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
1698 attr_set.import_error := 'import.item.invalid.status';
1699 attr_set.error_detail := tmp_attr_set.cs;
1700 RETURN NEXT attr_set; CONTINUE;
1704 IF tmp_attr_set.circ_mod != '' THEN
1705 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
1707 attr_set.import_error := 'import.item.invalid.circ_modifier';
1708 attr_set.error_detail := tmp_attr_set.circ_mod;
1709 RETURN NEXT attr_set; CONTINUE;
1713 IF tmp_attr_set.circ_as != '' THEN
1714 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
1716 attr_set.import_error := 'import.item.invalid.circ_as_type';
1717 attr_set.error_detail := tmp_attr_set.circ_as;
1718 RETURN NEXT attr_set; CONTINUE;
1722 IF tmp_attr_set.cl != '' THEN
1724 -- search up the org unit tree for a matching copy location
1725 WITH RECURSIVE anscestor_depth AS (
1729 FROM actor.org_unit ou
1730 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1731 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
1736 FROM actor.org_unit ou
1737 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1738 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
1739 ) SELECT cpl.id INTO attr_set.location
1740 FROM anscestor_depth a
1741 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
1742 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
1743 ORDER BY a.depth DESC
1747 attr_set.import_error := 'import.item.invalid.location';
1748 attr_set.error_detail := tmp_attr_set.cs;
1749 RETURN NEXT attr_set; CONTINUE;
1753 attr_set.circulate :=
1754 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
1755 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
1758 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
1759 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
1761 attr_set.holdable :=
1762 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
1763 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
1765 attr_set.opac_visible :=
1766 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
1767 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
1770 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
1771 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
1773 attr_set.call_number := tmp_attr_set.cn; -- TEXT
1774 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1775 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1776 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1777 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1778 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1779 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
1781 RETURN NEXT attr_set;
1790 $$ LANGUAGE PLPGSQL;
1794 -- 0732.schema.acq-lineitem-summary.sql
1796 SELECT evergreen.upgrade_deps_block_check('0732', :eg_version);
1798 CREATE OR REPLACE VIEW acq.lineitem_summary AS
1802 SELECT COUNT(lid.id)
1803 FROM acq.lineitem_detail lid
1804 WHERE lineitem = li.id
1807 SELECT COUNT(lid.id)
1808 FROM acq.lineitem_detail lid
1809 WHERE recv_time IS NOT NULL AND lineitem = li.id
1812 SELECT COUNT(lid.id)
1813 FROM acq.lineitem_detail lid
1814 WHERE cancel_reason IS NOT NULL AND lineitem = li.id
1817 SELECT COUNT(lid.id)
1818 FROM acq.lineitem_detail lid
1819 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1820 WHERE NOT debit.encumbrance AND lineitem = li.id
1823 SELECT COUNT(DISTINCT(lid.id))
1824 FROM acq.lineitem_detail lid
1825 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
1826 WHERE lineitem = li.id
1829 SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
1830 FROM acq.lineitem_detail lid
1831 WHERE lid.cancel_reason IS NULL AND lineitem = li.id
1832 ) AS estimated_amount,
1834 SELECT SUM(debit.amount)::NUMERIC(8,2)
1835 FROM acq.lineitem_detail lid
1836 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1837 WHERE debit.encumbrance AND lineitem = li.id
1838 ) AS encumbrance_amount,
1840 SELECT SUM(debit.amount)::NUMERIC(8,2)
1841 FROM acq.lineitem_detail lid
1842 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1843 WHERE NOT debit.encumbrance AND lineitem = li.id
1846 FROM acq.lineitem AS li;
1851 -- Template update included here for reference only.
1852 -- The stock JEDI template is not updated here (see WHERE clause)
1853 -- We do update the environment, though, for easier local template
1854 -- updating. No env fields are removed (that aren't otherwise replaced).
1858 SELECT evergreen.upgrade_deps_block_check('0733', :eg_version);
1860 UPDATE action_trigger.event_definition SET template =
1862 [%# start JEDI document
1863 # Vendor specific kludges:
1864 # BT - vendcode goes to NAD/BY *suffix* w/ 91 qualifier
1865 # INGRAM - vendcode goes to NAD/BY *segment* w/ 91 qualifier (separately)
1866 # BRODART - vendcode goes to FTX segment (lineitem level)
1869 IF target.provider.edi_default.vendcode && target.provider.code == 'BRODART';
1870 xtra_ftx = target.provider.edi_default.vendcode;
1873 [%- BLOCK big_block -%]
1875 "recipient":"[% target.provider.san %]",
1876 "sender":"[% target.ordering_agency.mailing_address.san %]",
1878 "ORDERS":[ "order", {
1879 "po_number":[% target.id %],
1880 "date":"[% date.format(date.now, '%Y%m%d') %]",
1882 [% IF target.provider.edi_default.vendcode && (target.provider.code == 'BT' || target.provider.name.match('(?i)^BAKER & TAYLOR')) -%]
1883 {"id-qualifier": 91, "id":"[% target.ordering_agency.mailing_address.san _ ' ' _ target.provider.edi_default.vendcode %]"}
1884 [%- ELSIF target.provider.edi_default.vendcode && target.provider.code == 'INGRAM' -%]
1885 {"id":"[% target.ordering_agency.mailing_address.san %]"},
1886 {"id-qualifier": 91, "id":"[% target.provider.edi_default.vendcode %]"}
1888 {"id":"[% target.ordering_agency.mailing_address.san %]"}
1892 [%- # target.provider.name (target.provider.id) -%]
1893 "[% target.provider.san %]",
1894 {"id-qualifier": 92, "id":"[% target.provider.id %]"}
1896 "currency":"[% target.provider.currency_type %]",
1899 [%- FOR li IN target.lineitems %]
1901 "line_index":"[% li.id %]",
1902 "identifiers":[ [%-# li.isbns = helpers.get_li_isbns(li.attributes) %]
1903 [% FOR isbn IN helpers.get_li_isbns(li.attributes) -%]
1904 [% IF isbn.length == 13 -%]
1905 {"id-qualifier":"EN","id":"[% isbn %]"},
1907 {"id-qualifier":"IB","id":"[% isbn %]"},
1910 {"id-qualifier":"IN","id":"[% li.id %]"}
1912 "price":[% li.estimated_unit_price || '0.00' %],
1914 {"BTI":"[% helpers.get_li_attr_jedi('title', '', li.attributes) %]"},
1915 {"BPU":"[% helpers.get_li_attr_jedi('publisher', '', li.attributes) %]"},
1916 {"BPD":"[% helpers.get_li_attr_jedi('pubdate', '', li.attributes) %]"},
1917 {"BPH":"[% helpers.get_li_attr_jedi('pagination','', li.attributes) %]"}
1920 FOR note IN li.lineitem_notes;
1921 NEXT UNLESS note.vendor_public == 't';
1922 ftx_vals.push(note.value);
1924 IF xtra_ftx; ftx_vals.unshift(xtra_ftx); END;
1925 IF ftx_vals.size == 0; ftx_vals.unshift(''); END; # BT needs FTX+LIN for every LI, even if it is an empty one
1929 [% FOR note IN ftx_vals -%] "[% note %]"[% UNLESS loop.last %], [% END %][% END %]
1931 "quantity":[% li.lineitem_details.size %],
1934 [%- FOR lid IN li.lineitem_details;
1935 fund = lid.fund.code;
1936 item_type = lid.circ_modifier;
1937 callnumber = lid.cn_label;
1938 owning_lib = lid.owning_lib.shortname;
1939 location = lid.location;
1941 # when we have real copy data, treat it as authoritative
1942 acp = lid.eg_copy_id;
1944 item_type = acp.circ_modifier;
1945 callnumber = acp.call_number.label;
1946 location = acp.location.name;
1948 { [%- IF fund %] "fund" : "[% fund %]",[% END -%]
1949 [%- IF callnumber %] "call_number" : "[% callnumber %]", [% END -%]
1950 [%- IF item_type %] "item_type" : "[% item_type %]", [% END -%]
1951 [%- IF location %] "copy_location" : "[% location %]", [% END -%]
1952 [%- IF owning_lib %] "owning_lib" : "[% owning_lib %]", [% END -%]
1953 [%- #chomp %]"copy_id" : "[% lid.id %]" }[% ',' UNLESS loop.last %]
1957 }[% UNLESS loop.last %],[% END %]
1958 [%-# TODO: lineitem details (later) -%]
1961 "line_items":[% target.lineitems.size %]
1962 }] [%# close ORDERS array %]
1963 }] [%# close body array %]
1966 [% tempo = PROCESS big_block; helpers.escape_json(tempo) %]
1968 WHERE id = 23 AND FALSE; -- DON'T PERFORM THE UPDATE
1971 -- add copy-related fields to the environment if they're not already there.
1975 FROM action_trigger.environment
1978 path = 'lineitems.lineitem_details.owning_lib';
1980 INSERT INTO action_trigger.environment (event_def, path)
1981 VALUES (23, 'lineitems.lineitem_details.owning_lib');
1985 FROM action_trigger.environment
1988 path = 'lineitems.lineitem_details.fund';
1990 INSERT INTO action_trigger.environment (event_def, path)
1991 VALUES (23, 'lineitems.lineitem_details.fund');
1995 FROM action_trigger.environment
1998 path = 'lineitems.lineitem_details.location';
2000 INSERT INTO action_trigger.environment (event_def, path)
2001 VALUES (23, 'lineitems.lineitem_details.location');
2005 FROM action_trigger.environment
2008 path = 'lineitems.lineitem_details.eg_copy_id.location';
2010 INSERT INTO action_trigger.environment (event_def, path)
2011 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.location');
2015 FROM action_trigger.environment
2018 path = 'lineitems.lineitem_details.eg_copy_id.call_number';
2020 INSERT INTO action_trigger.environment (event_def, path)
2021 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.call_number');
2026 -- remove redundant entry
2027 DELETE FROM action_trigger.environment
2028 WHERE event_def = 23 AND path = 'lineitems.lineitem_details';
2033 -- Evergreen DB patch 0734.tpac_holdable_check.sql
2036 -- check whether patch can be applied
2037 SELECT evergreen.upgrade_deps_block_check('0734', :eg_version);
2039 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2044 JOIN asset.call_number acn ON acp.call_number = acn.id
2045 JOIN asset.copy_location acpl ON acp.location = acpl.id
2046 JOIN config.copy_status ccs ON acp.status = ccs.id
2049 AND acp.holdable = true
2050 AND acpl.holdable = true
2051 AND ccs.holdable = true
2052 AND acp.deleted = false
2059 $f$ LANGUAGE PLPGSQL;
2061 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2066 JOIN asset.call_number acn ON acp.call_number = acn.id
2067 JOIN asset.copy_location acpl ON acp.location = acpl.id
2068 JOIN config.copy_status ccs ON acp.status = ccs.id
2069 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
2071 mmsm.metarecord = rid
2072 AND acp.holdable = true
2073 AND acpl.holdable = true
2074 AND ccs.holdable = true
2075 AND acp.deleted = false
2082 $f$ LANGUAGE PLPGSQL;
2084 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
2088 depth INT DEFAULT NULL,
2089 includes TEXT[] DEFAULT NULL::TEXT[],
2090 slimit HSTORE DEFAULT NULL,
2091 soffset HSTORE DEFAULT NULL,
2092 include_xmlns BOOL DEFAULT TRUE,
2093 pref_lib INT DEFAULT NULL
2099 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2100 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
2101 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
2105 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
2108 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2110 FROM asset.opac_ou_record_copy_count($2, $1)
2114 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2116 FROM asset.staff_ou_record_copy_count($2, $1)
2120 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2122 FROM asset.opac_ou_record_copy_count($9, $1)
2127 WHEN ('bmp' = ANY ($5)) THEN
2129 name monograph_parts,
2130 (SELECT XMLAGG(bmp) FROM (
2131 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)
2132 FROM biblio.monograph_part
2140 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
2142 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
2143 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
2146 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
2147 FROM evergreen.located_uris($1, $2, $9) AS uris
2150 CASE WHEN ('ssub' = ANY ($5)) THEN
2153 (SELECT XMLAGG(ssub) FROM (
2154 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
2155 FROM serial.subscription
2156 WHERE record_entry = $1
2160 CASE WHEN ('acp' = ANY ($5)) THEN
2162 name foreign_copies,
2163 (SELECT XMLAGG(acp) FROM (
2164 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
2165 FROM biblio.peer_bib_copy_map p
2166 JOIN asset.copy c ON (p.target_copy = c.id)
2167 WHERE NOT c.deleted AND p.peer_record = $1
2168 LIMIT ($6 -> 'acp')::INT
2169 OFFSET ($7 -> 'acp')::INT
2174 $F$ LANGUAGE SQL STABLE;
2176 -- Evergreen DB patch 0735.data.search_filter_group_perms.sql
2179 -- check whether patch can be applied
2180 SELECT evergreen.upgrade_deps_block_check('0735', :eg_version);
2182 INSERT INTO permission.perm_list ( id, code, description )
2185 'ADMIN_SEARCH_FILTER_GROUP',
2188 'Allows staff to manage search filter groups and entries',
2195 'VIEW_SEARCH_FILTER_GROUP',
2198 'Allows staff to view search filter groups and entries',
2205 -- check whether patch can be applied
2206 SELECT evergreen.upgrade_deps_block_check('0737', :eg_version);
2208 UPDATE action_trigger.event_definition
2212 # target is the bookbag itself. The 'items' variable does not need to be in
2213 # the environment because a special reactor will take care of filling it in.
2216 bibxml = helpers.unapi_bre(item.target_biblio_record_entry, {flesh => '{mra}'});
2218 FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
2219 title = title _ part.textContent;
2221 author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
2222 item_type = bibxml.findnodes('//*[local-name()="attributes"]/*[local-name()="field"][@name="item_type"]').getAttribute('coded-value');
2224 FOR pdatum IN bibxml.findnodes('//*[@tag="260"]/*[@code="c"]');
2226 pub_date = pub_date _ ", " _ pdatum.textContent;
2228 pub_date = pdatum.textContent;
2231 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";
2234 WHERE name = 'Bookbag CSV';
2238 \qecho Evergreen depends heavily on each bibliographic record containing
2239 \qecho a 901 field with a subfield "c" to hold the record ID. The following
2240 \qecho query identifies the bibs that are missing 901s or whose first
2241 \qecho 901$c is not equal to the bib ID. This *will* take a long time in a
2242 \qecho big database; as the schema updates are over now, you can cancel this
2243 \qecho if you are in a rush.
2246 FROM biblio.record_entry
2248 (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
2250 (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)
2253 \qecho If there are records with missing or incorrect 901$c values, you can
2254 \qecho generally rely on the triggers in the biblio.record_entry table to
2255 \qecho populate the 901$c properly; for each offending record, run:
2256 \qecho UPDATE biblio.record_entry SET marc = marc WHERE id = <id>;