1 --Upgrade Script for 2.2 to 2.3.0
4 INSERT INTO config.settings_group (name, label) VALUES
5 ('acq', oils_i18n_gettext('config.settings_group.system', 'Acquisitions', 'coust', 'label'));
8 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.0', :eg_version);
9 -- Evergreen DB patch 0703.tpac_value_maps.sql
11 -- check whether patch can be applied
12 SELECT evergreen.upgrade_deps_block_check('0703', :eg_version);
14 ALTER TABLE config.coded_value_map
15 ADD COLUMN opac_visible BOOL NOT NULL DEFAULT TRUE,
16 ADD COLUMN search_label TEXT,
17 ADD COLUMN is_simple BOOL NOT NULL DEFAULT FALSE;
21 SELECT evergreen.upgrade_deps_block_check('0712', :eg_version);
23 -- General purpose query container. Any table the needs to store
24 -- a QueryParser query should store it here. This will be the
25 -- source for top-level and QP sub-search inclusion queries.
26 CREATE TABLE actor.search_query (
27 id SERIAL PRIMARY KEY,
28 label TEXT NOT NULL, -- i18n
29 query_text TEXT NOT NULL -- QP text
32 -- e.g. "Reading Level"
33 CREATE TABLE actor.search_filter_group (
34 id SERIAL PRIMARY KEY,
35 owner INT NOT NULL REFERENCES actor.org_unit (id)
36 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
37 code TEXT NOT NULL, -- for CGI, etc.
38 label TEXT NOT NULL, -- i18n
39 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
40 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
41 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
44 -- e.g. "Adult", "Teen", etc.
45 CREATE TABLE actor.search_filter_group_entry (
46 id SERIAL PRIMARY KEY,
47 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
48 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
49 pos INT NOT NULL DEFAULT 0,
50 query INT NOT NULL REFERENCES actor.search_query(id)
51 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
52 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
56 SELECT evergreen.upgrade_deps_block_check('0713', :eg_version);
58 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
59 'ui.grid_columns.circ.hold_pull_list',
63 'ui.grid_columns.circ.hold_pull_list',
69 'ui.grid_columns.circ.hold_pull_list',
70 'Hold Pull List Saved Column Settings',
79 SELECT evergreen.upgrade_deps_block_check('0714', :eg_version);
81 INSERT into config.org_unit_setting_type
82 (name, grp, label, description, datatype)
84 'opac.patron.auto_overide_hold_events',
87 'opac.patron.auto_overide_hold_events',
88 'Auto-Override Permitted Hold Blocks (Patrons)',
93 'opac.patron.auto_overide_hold_events',
94 'When a patron places a hold that fails and the patron has the correct permission ' ||
95 'to override the hold, automatically override the hold without presenting a message ' ||
96 'to the patron and requiring that the patron make a decision to override',
103 -- Evergreen DB patch 0718.data.add-to-permanent-bookbag.sql
105 -- check whether patch can be applied
106 SELECT evergreen.upgrade_deps_block_check('0718', :eg_version);
108 INSERT into config.org_unit_setting_type
109 (name, grp, label, description, datatype)
111 'opac.patron.temporary_list_warn',
114 'opac.patron.temporary_list_warn',
115 'Warn patrons when adding to a temporary book list',
120 'opac.patron.temporary_list_warn',
121 'Present a warning dialog to the patron when a patron adds a book to a temporary book bag.',
128 INSERT INTO config.usr_setting_type
129 (name,grp,opac_visible,label,description,datatype)
131 'opac.temporary_list_no_warn',
135 'opac.temporary_list_no_warn',
136 'Opt out of warning when adding a book to a temporary book list',
141 'opac.temporary_list_no_warn',
142 'Opt out of warning when adding a book to a temporary book list',
149 INSERT INTO config.usr_setting_type
150 (name,grp,opac_visible,label,description,datatype)
157 'Default list to use when adding to a bookbag',
163 'Default list to use when adding to a bookbag',
171 SELECT evergreen.upgrade_deps_block_check('0719', :eg_version);
173 INSERT INTO config.org_unit_setting_type (
174 name, label, grp, description, datatype
176 'circ.staff.max_visible_event_age',
177 'Maximum visible age of User Trigger Events in Staff Interfaces',
179 '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.',
183 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
184 'ui.grid_columns.actor.user.event_log',
188 'ui.grid_columns.actor.user.event_log',
194 'ui.grid_columns.actor.user.event_log',
195 'User Event Log Saved Column Settings',
202 INSERT INTO permission.perm_list ( id, code, description )
205 'VIEW_TRIGGER_EVENT',
208 'Allows a user to view circ- and hold-related action/trigger events',
215 SELECT evergreen.upgrade_deps_block_check('0720', :eg_version);
217 ALTER TABLE config.circ_matrix_weights
218 ADD COLUMN copy_location NUMERIC(6,2) NOT NULL DEFAULT 5.0;
219 UPDATE config.circ_matrix_weights
220 SET copy_location = 0.0 WHERE name = 'All_Equal';
221 ALTER TABLE config.circ_matrix_weights
222 ALTER COLUMN copy_location DROP DEFAULT; -- for consistency w/ baseline schema
224 ALTER TABLE config.circ_matrix_matchpoint
225 ADD COLUMN copy_location INTEGER REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED;
227 DROP INDEX config.ccmm_once_per_paramset;
229 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;
231 -- Linkage between limit sets and circ mods
232 CREATE TABLE config.circ_limit_set_copy_loc_map (
233 id SERIAL PRIMARY KEY,
234 limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
235 copy_loc INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
236 CONSTRAINT cl_once_per_set UNIQUE (limit_set, copy_loc)
239 -- Add support for checking config.circ_limit_set_copy_loc_map's
240 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL )
241 RETURNS SETOF action.circ_matrix_test_result AS $func$
243 user_object actor.usr%ROWTYPE;
244 standing_penalty config.standing_penalty%ROWTYPE;
245 item_object asset.copy%ROWTYPE;
246 item_status_object config.copy_status%ROWTYPE;
247 item_location_object asset.copy_location%ROWTYPE;
248 result action.circ_matrix_test_result;
249 circ_test action.found_circ_matrix_matchpoint;
250 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
251 circ_limit_set config.circ_limit_set%ROWTYPE;
252 hold_ratio action.hold_stats%ROWTYPE;
255 context_org_list INT[];
258 -- Assume success unless we hit a failure condition
259 result.success := TRUE;
261 -- Need user info to look up matchpoints
262 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
264 -- (Insta)Fail if we couldn't find the user
265 IF user_object.id IS NULL THEN
266 result.fail_part := 'no_user';
267 result.success := FALSE;
273 -- Need item info to look up matchpoints
274 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
276 -- (Insta)Fail if we couldn't find the item
277 IF item_object.id IS NULL THEN
278 result.fail_part := 'no_item';
279 result.success := FALSE;
285 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
287 circ_matchpoint := circ_test.matchpoint;
288 result.matchpoint := circ_matchpoint.id;
289 result.circulate := circ_matchpoint.circulate;
290 result.duration_rule := circ_matchpoint.duration_rule;
291 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
292 result.max_fine_rule := circ_matchpoint.max_fine_rule;
293 result.hard_due_date := circ_matchpoint.hard_due_date;
294 result.renewals := circ_matchpoint.renewals;
295 result.grace_period := circ_matchpoint.grace_period;
296 result.buildrows := circ_test.buildrows;
298 -- (Insta)Fail if we couldn't find a matchpoint
299 IF circ_test.success = false THEN
300 result.fail_part := 'no_matchpoint';
301 result.success := FALSE;
307 -- All failures before this point are non-recoverable
308 -- Below this point are possibly overridable failures
310 -- Fail if the user is barred
311 IF user_object.barred IS TRUE THEN
312 result.fail_part := 'actor.usr.barred';
313 result.success := FALSE;
318 -- Fail if the item can't circulate
319 IF item_object.circulate IS FALSE THEN
320 result.fail_part := 'asset.copy.circulate';
321 result.success := FALSE;
326 -- Fail if the item isn't in a circulateable status on a non-renewal
327 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
328 result.fail_part := 'asset.copy.status';
329 result.success := FALSE;
332 -- Alternately, fail if the item isn't checked out on a renewal
333 ELSIF renewal AND item_object.status <> 1 THEN
334 result.fail_part := 'asset.copy.status';
335 result.success := FALSE;
340 -- Fail if the item can't circulate because of the shelving location
341 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
342 IF item_location_object.circulate IS FALSE THEN
343 result.fail_part := 'asset.copy_location.circulate';
344 result.success := FALSE;
349 -- Use Circ OU for penalties and such
350 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
353 penalty_type = '%RENEW%';
355 penalty_type = '%CIRC%';
358 FOR standing_penalty IN
359 SELECT DISTINCT csp.*
360 FROM actor.usr_standing_penalty usp
361 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
362 WHERE usr = match_user
363 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
364 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
365 AND csp.block_list LIKE penalty_type LOOP
367 result.fail_part := standing_penalty.name;
368 result.success := FALSE;
373 -- Fail if the test is set to hard non-circulating
374 IF circ_matchpoint.circulate IS FALSE THEN
375 result.fail_part := 'config.circ_matrix_test.circulate';
376 result.success := FALSE;
381 -- Fail if the total copy-hold ratio is too low
382 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
383 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
384 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
385 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
386 result.success := FALSE;
392 -- Fail if the available copy-hold ratio is too low
393 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
394 IF hold_ratio.hold_count IS NULL THEN
395 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
397 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
398 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
399 result.success := FALSE;
405 -- Fail if the user has too many items out by defined limit sets
406 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
407 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
408 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
409 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
411 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
412 SELECT INTO context_org_list ARRAY_AGG(aou.id)
413 FROM actor.org_unit_full_path( circ_ou ) aou
414 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
415 WHERE aout.depth >= circ_limit_set.depth;
416 IF circ_limit_set.global THEN
417 WITH RECURSIVE descendant_depth AS (
420 FROM actor.org_unit ou
421 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
425 FROM actor.org_unit ou
426 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
427 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
429 SELECT INTO items_out COUNT(DISTINCT circ.id)
430 FROM action.circulation circ
431 JOIN asset.copy copy ON (copy.id = circ.target_copy)
432 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
433 WHERE circ.usr = match_user
434 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
435 AND circ.checkin_time IS NULL
436 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
437 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
438 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
439 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
441 IF items_out >= circ_limit_set.items_out THEN
442 result.fail_part := 'config.circ_matrix_circ_mod_test';
443 result.success := FALSE;
448 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;
451 -- If we passed everything, return the successful matchpoint
458 $func$ LANGUAGE plpgsql;
461 -- adding copy_loc to circ_matrix_matchpoint
462 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$
464 cn_object asset.call_number%ROWTYPE;
465 rec_descriptor metabib.rec_descriptor%ROWTYPE;
466 cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
467 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
468 weights config.circ_matrix_weights%ROWTYPE;
470 my_item_age INTERVAL;
471 denominator NUMERIC(6,2);
473 result action.found_circ_matrix_matchpoint;
476 result.success = false;
479 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
480 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
482 -- Pre-generate this so we only calc it once
483 IF user_object.dob IS NOT NULL THEN
484 SELECT INTO user_age age(user_object.dob);
488 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
490 -- Grab the closest set circ weight setting.
491 SELECT INTO weights cw.*
492 FROM config.weight_assoc wa
493 JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
494 JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
499 -- No weights? Bad admin! Defaults to handle that anyway.
500 IF weights.id IS NULL THEN
502 weights.org_unit := 10.0;
503 weights.circ_modifier := 5.0;
504 weights.copy_location := 5.0;
505 weights.marc_type := 4.0;
506 weights.marc_form := 3.0;
507 weights.marc_bib_level := 2.0;
508 weights.marc_vr_format := 2.0;
509 weights.copy_circ_lib := 8.0;
510 weights.copy_owning_lib := 8.0;
511 weights.user_home_ou := 8.0;
512 weights.ref_flag := 1.0;
513 weights.juvenile_flag := 6.0;
514 weights.is_renewal := 7.0;
515 weights.usr_age_lower_bound := 0.0;
516 weights.usr_age_upper_bound := 0.0;
517 weights.item_age := 0.0;
520 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
521 -- If you break your org tree with funky parenting this may be wrong
522 -- 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
523 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
524 WITH all_distance(distance) AS (
525 SELECT depth AS distance FROM actor.org_unit_type
527 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
529 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
531 -- Loop over all the potential matchpoints
532 FOR cur_matchpoint IN
534 FROM config.circ_matrix_matchpoint m
535 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
536 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
537 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
538 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
539 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
542 -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
544 -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
545 AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
546 AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
547 AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
549 AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
550 -- Static User Checks
551 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
552 AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
553 AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
554 -- Static Item Checks
555 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
556 AND (m.copy_location IS NULL OR m.copy_location = item_object.location)
557 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
558 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
559 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
560 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
561 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
562 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
565 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
567 CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
568 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
569 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
570 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
571 -- Circ Type -- Note: 4^x is equiv to 2^(2*x)
572 CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
573 -- Static User Checks
574 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
575 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
576 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
577 -- Static Item Checks
578 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
579 CASE WHEN m.copy_location IS NOT NULL THEN 4^weights.copy_location ELSE 0.0 END +
580 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
581 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
582 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
583 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
584 -- Item age has a slight adjustment to weight based on value.
585 -- This should ensure that a shorter age limit comes first when all else is equal.
586 -- NOTE: This assumes that intervals will normally be in days.
587 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,
588 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
589 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
592 -- Record the full matching row list
593 row_list := row_list || cur_matchpoint.id;
595 -- No matchpoint yet?
596 IF matchpoint.id IS NULL THEN
597 -- Take the entire matchpoint as a starting point
598 matchpoint := cur_matchpoint;
599 CONTINUE; -- No need to look at this row any more.
602 -- Incomplete matchpoint?
603 IF matchpoint.circulate IS NULL THEN
604 matchpoint.circulate := cur_matchpoint.circulate;
606 IF matchpoint.duration_rule IS NULL THEN
607 matchpoint.duration_rule := cur_matchpoint.duration_rule;
609 IF matchpoint.recurring_fine_rule IS NULL THEN
610 matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
612 IF matchpoint.max_fine_rule IS NULL THEN
613 matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
615 IF matchpoint.hard_due_date IS NULL THEN
616 matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
618 IF matchpoint.total_copy_hold_ratio IS NULL THEN
619 matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
621 IF matchpoint.available_copy_hold_ratio IS NULL THEN
622 matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
624 IF matchpoint.renewals IS NULL THEN
625 matchpoint.renewals := cur_matchpoint.renewals;
627 IF matchpoint.grace_period IS NULL THEN
628 matchpoint.grace_period := cur_matchpoint.grace_period;
632 -- Check required fields
633 IF matchpoint.circulate IS NOT NULL AND
634 matchpoint.duration_rule IS NOT NULL AND
635 matchpoint.recurring_fine_rule IS NOT NULL AND
636 matchpoint.max_fine_rule IS NOT NULL THEN
637 -- All there? We have a completed match.
638 result.success := true;
641 -- Include the assembled matchpoint, even if it isn't complete
642 result.matchpoint := matchpoint;
644 -- Include (for debugging) the full list of matching rows
645 result.buildrows := row_list;
647 -- Hand the result back to caller
650 $func$ LANGUAGE plpgsql;
655 SELECT evergreen.upgrade_deps_block_check('0721', :eg_version);
657 UPDATE config.standing_penalty
658 SET block_list = REPLACE(block_list, 'HOLD', 'HOLD|CAPTURE')
660 -- STAFF_ penalties have names that match their block list
661 name NOT LIKE 'STAFF_%'
662 -- belt & suspenders, also good for testing
663 AND block_list NOT LIKE '%CAPTURE%';
665 -- CIRC|FULFILL is now the same as CIRC previously was by itself
666 UPDATE config.standing_penalty
667 SET block_list = REPLACE(block_list, 'CIRC', 'CIRC|FULFILL')
669 -- STAFF_ penalties have names that match their block list
670 name NOT LIKE 'STAFF_%'
671 -- belt & suspenders, also good for testing
672 AND block_list NOT LIKE '%FULFILL%';
675 -- apply the HOLD vs CAPTURE block logic
676 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$
679 user_object actor.usr%ROWTYPE;
680 age_protect_object config.rule_age_hold_protect%ROWTYPE;
681 standing_penalty config.standing_penalty%ROWTYPE;
682 transit_range_ou_type actor.org_unit_type%ROWTYPE;
683 transit_source actor.org_unit%ROWTYPE;
684 item_object asset.copy%ROWTYPE;
685 item_cn_object asset.call_number%ROWTYPE;
686 item_status_object config.copy_status%ROWTYPE;
687 item_location_object asset.copy_location%ROWTYPE;
688 ou_skip actor.org_unit_setting%ROWTYPE;
689 result action.matrix_test_result;
690 hold_test config.hold_matrix_matchpoint%ROWTYPE;
691 use_active_date TEXT;
692 age_protect_date TIMESTAMP WITH TIME ZONE;
694 hold_transit_prox INT;
695 frozen_hold_count INT;
696 context_org_list INT[];
700 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
701 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
703 result.success := TRUE;
705 -- The HOLD penalty block only applies to new holds.
706 -- The CAPTURE penalty block applies to existing holds.
707 hold_penalty := 'HOLD';
709 hold_penalty := 'CAPTURE';
712 -- Fail if we couldn't find a user
713 IF user_object.id IS NULL THEN
714 result.fail_part := 'no_user';
715 result.success := FALSE;
721 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
723 -- Fail if we couldn't find a copy
724 IF item_object.id IS NULL THEN
725 result.fail_part := 'no_item';
726 result.success := FALSE;
732 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
733 result.matchpoint := matchpoint_id;
735 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
737 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
738 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
739 result.fail_part := 'circ.holds.target_skip_me';
740 result.success := FALSE;
746 -- Fail if user is barred
747 IF user_object.barred IS TRUE THEN
748 result.fail_part := 'actor.usr.barred';
749 result.success := FALSE;
755 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
756 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
757 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
759 -- Fail if we couldn't find any matchpoint (requires a default)
760 IF matchpoint_id IS NULL THEN
761 result.fail_part := 'no_matchpoint';
762 result.success := FALSE;
768 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
770 IF hold_test.holdable IS FALSE THEN
771 result.fail_part := 'config.hold_matrix_test.holdable';
772 result.success := FALSE;
777 IF item_object.holdable IS FALSE THEN
778 result.fail_part := 'item.holdable';
779 result.success := FALSE;
784 IF item_status_object.holdable IS FALSE THEN
785 result.fail_part := 'status.holdable';
786 result.success := FALSE;
791 IF item_location_object.holdable IS FALSE THEN
792 result.fail_part := 'location.holdable';
793 result.success := FALSE;
798 IF hold_test.transit_range IS NOT NULL THEN
799 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
800 IF hold_test.distance_is_from_owner THEN
801 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;
803 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
806 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
809 result.fail_part := 'transit_range';
810 result.success := FALSE;
816 FOR standing_penalty IN
817 SELECT DISTINCT csp.*
818 FROM actor.usr_standing_penalty usp
819 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
820 WHERE usr = match_user
821 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
822 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
823 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
825 result.fail_part := standing_penalty.name;
826 result.success := FALSE;
831 IF hold_test.stop_blocked_user IS TRUE THEN
832 FOR standing_penalty IN
833 SELECT DISTINCT csp.*
834 FROM actor.usr_standing_penalty usp
835 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
836 WHERE usr = match_user
837 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
838 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
839 AND csp.block_list LIKE '%CIRC%' LOOP
841 result.fail_part := standing_penalty.name;
842 result.success := FALSE;
848 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
849 SELECT INTO hold_count COUNT(*)
850 FROM action.hold_request
851 WHERE usr = match_user
852 AND fulfillment_time IS NULL
853 AND cancel_time IS NULL
854 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
856 IF hold_count >= hold_test.max_holds THEN
857 result.fail_part := 'config.hold_matrix_test.max_holds';
858 result.success := FALSE;
864 IF item_object.age_protect IS NOT NULL THEN
865 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
866 IF hold_test.distance_is_from_owner THEN
867 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
869 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
871 IF use_active_date = 'true' THEN
872 age_protect_date := COALESCE(item_object.active_date, NOW());
874 age_protect_date := item_object.create_date;
876 IF age_protect_date + age_protect_object.age > NOW() THEN
877 IF hold_test.distance_is_from_owner THEN
878 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
879 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
881 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
884 IF hold_transit_prox > age_protect_object.prox THEN
885 result.fail_part := 'config.rule_age_hold_protect.prox';
886 result.success := FALSE;
899 $func$ LANGUAGE plpgsql;
902 -- Evergreen DB patch 0727.function.xml_pretty_print.sql
904 -- A simple pretty printer for XML.
905 -- Particularly useful for debugging the biblio.record_entry.marc field.
908 -- check whether patch can be applied
909 SELECT evergreen.upgrade_deps_block_check('0727', :eg_version);
911 CREATE OR REPLACE FUNCTION evergreen.xml_pretty_print(input XML)
915 SELECT xslt_process($1::text,
916 $$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
918 <xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
919 <xsl:strip-space elements="*"/>
920 <xsl:template match="@*|node()">
922 <xsl:apply-templates select="@*|node()"/>
929 COMMENT ON FUNCTION evergreen.xml_pretty_print(input XML) IS
930 'Simple pretty printer for XML, as written by Andrew Dunstan at http://goo.gl/zBHIk';
933 SELECT evergreen.upgrade_deps_block_check('0728', :eg_version);
935 INSERT INTO actor.search_filter_group (owner, code, label)
936 VALUES (1, 'kpac_main', 'Kid''s OPAC Search Filter');
938 INSERT INTO actor.search_query (label, query_text)
939 VALUES ('Children''s Materials', 'audience(a,b,c)');
940 INSERT INTO actor.search_query (label, query_text)
941 VALUES ('Young Adult Materials', 'audience(j,d)');
942 INSERT INTO actor.search_query (label, query_text)
943 VALUES ('General/Adult Materials', 'audience(e,f,g, )');
945 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
947 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
948 (SELECT id FROM actor.search_query WHERE label = 'Children''s Materials'),
951 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
953 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
954 (SELECT id FROM actor.search_query WHERE label = 'Young Adult Materials'),
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 = 'General/Adult Materials'),
965 -- Evergreen DB patch 0729.vr_format_value_maps.sql
968 -- check whether patch can be applied
969 SELECT evergreen.upgrade_deps_block_check('0729', :eg_version);
971 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$
973 current_row config.coded_value_map%ROWTYPE;
975 -- Look for a current value
976 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
978 IF FOUND AND NOT add_only THEN
979 -- Update anything we were handed
980 current_row.value := COALESCE(current_row.value, in_value);
981 current_row.description := COALESCE(current_row.description, in_description);
982 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
983 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
984 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
985 UPDATE config.coded_value_map
987 value = current_row.value,
988 description = current_row.description,
989 opac_visible = current_row.opac_visible,
990 search_label = current_row.search_label,
991 is_simple = current_row.is_simple
992 WHERE id = current_row.id;
994 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
995 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
998 $f$ LANGUAGE PLPGSQL;
1000 SELECT config.update_coded_value_map('vr_format', 'a', 'Beta', add_only := TRUE);
1001 SELECT config.update_coded_value_map('vr_format', 'b', 'VHS', add_only := TRUE);
1002 SELECT config.update_coded_value_map('vr_format', 'c', 'U-matic', add_only := TRUE);
1003 SELECT config.update_coded_value_map('vr_format', 'd', 'EIAJ', add_only := TRUE);
1004 SELECT config.update_coded_value_map('vr_format', 'e', 'Type C', add_only := TRUE);
1005 SELECT config.update_coded_value_map('vr_format', 'f', 'Quadruplex', add_only := TRUE);
1006 SELECT config.update_coded_value_map('vr_format', 'g', 'Laserdisc', add_only := TRUE);
1007 SELECT config.update_coded_value_map('vr_format', 'h', 'CED videodisc', add_only := TRUE);
1008 SELECT config.update_coded_value_map('vr_format', 'i', 'Betacam', add_only := TRUE);
1009 SELECT config.update_coded_value_map('vr_format', 'j', 'Betacam SP', add_only := TRUE);
1010 SELECT config.update_coded_value_map('vr_format', 'k', 'Super-VHS', add_only := TRUE);
1011 SELECT config.update_coded_value_map('vr_format', 'm', 'M-II', add_only := TRUE);
1012 SELECT config.update_coded_value_map('vr_format', 'o', 'D-2', add_only := TRUE);
1013 SELECT config.update_coded_value_map('vr_format', 'p', '8 mm.', add_only := TRUE);
1014 SELECT config.update_coded_value_map('vr_format', 'q', 'Hi-8 mm.', add_only := TRUE);
1015 SELECT config.update_coded_value_map('vr_format', 's', 'Blu-ray disc', add_only := TRUE);
1016 SELECT config.update_coded_value_map('vr_format', 'u', 'Unknown', add_only := TRUE);
1017 SELECT config.update_coded_value_map('vr_format', 'v', 'DVD', add_only := TRUE);
1018 SELECT config.update_coded_value_map('vr_format', 'z', 'Other', add_only := TRUE);
1019 SELECT config.update_coded_value_map('vr_format', ' ', 'Unspecified', add_only := TRUE);
1023 SELECT evergreen.upgrade_deps_block_check('0730', :eg_version);
1025 DROP FUNCTION acq.propagate_funds_by_org_tree (INT, INT, INT);
1026 DROP FUNCTION acq.propagate_funds_by_org_unit (INT, INT, INT);
1028 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
1031 org_unit_id INTEGER,
1032 include_desc BOOL DEFAULT TRUE
1033 ) RETURNS VOID AS $$
1044 IF old_year IS NULL THEN
1045 RAISE EXCEPTION 'Input year argument is NULL';
1046 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1047 RAISE EXCEPTION 'Input year is out of range';
1050 IF user_id IS NULL THEN
1051 RAISE EXCEPTION 'Input user id argument is NULL';
1054 IF org_unit_id IS NULL THEN
1055 RAISE EXCEPTION 'Org unit id argument is NULL';
1057 SELECT TRUE INTO org_found
1059 WHERE id = org_unit_id;
1061 IF org_found IS NULL THEN
1062 RAISE EXCEPTION 'Org unit id is invalid';
1066 -- Loop over the applicable funds
1068 FOR old_fund in SELECT * FROM acq.fund
1072 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1073 OR (NOT include_desc AND org = org_unit_id ) )
1077 INSERT INTO acq.fund (
1085 balance_warning_percent,
1086 balance_stop_percent
1091 old_fund.currency_type,
1095 old_fund.balance_warning_percent,
1096 old_fund.balance_stop_percent
1098 RETURNING id INTO new_id;
1100 WHEN unique_violation THEN
1101 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1104 --RAISE NOTICE 'Propagating fund % to fund %',
1105 -- old_fund.code, new_id;
1108 $$ LANGUAGE plpgsql;
1110 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$
1111 SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE );
1115 DROP FUNCTION acq.rollover_funds_by_org_tree (INT, INT, INT);
1116 DROP FUNCTION acq.rollover_funds_by_org_unit (INT, INT, INT);
1119 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
1122 org_unit_id INTEGER,
1123 encumb_only BOOL DEFAULT FALSE,
1124 include_desc BOOL DEFAULT TRUE
1125 ) RETURNS VOID AS $$
1129 new_year INT := old_year + 1;
1132 xfer_amount NUMERIC := 0;
1141 IF old_year IS NULL THEN
1142 RAISE EXCEPTION 'Input year argument is NULL';
1143 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1144 RAISE EXCEPTION 'Input year is out of range';
1147 IF user_id IS NULL THEN
1148 RAISE EXCEPTION 'Input user id argument is NULL';
1151 IF org_unit_id IS NULL THEN
1152 RAISE EXCEPTION 'Org unit id argument is NULL';
1155 -- Validate the org unit
1160 WHERE id = org_unit_id;
1162 IF org_found IS NULL THEN
1163 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
1164 ELSIF encumb_only THEN
1165 SELECT INTO perm_ous value::BOOL FROM
1166 actor.org_unit_ancestor_setting(
1167 'acq.fund.allow_rollover_without_money', org_unit_id
1169 IF NOT FOUND OR NOT perm_ous THEN
1170 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
1175 -- Loop over the propagable funds to identify the details
1176 -- from the old fund plus the id of the new one, if it exists.
1180 oldf.id AS old_fund,
1186 newf.id AS new_fund_id
1189 LEFT JOIN acq.fund AS newf
1190 ON ( oldf.code = newf.code )
1192 oldf.year = old_year
1194 AND newf.year = new_year
1195 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1196 OR (NOT include_desc AND oldf.org = org_unit_id ) )
1198 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
1200 IF roll_fund.new_fund_id IS NULL THEN
1202 -- The old fund hasn't been propagated yet. Propagate it now.
1204 INSERT INTO acq.fund (
1212 balance_warning_percent,
1213 balance_stop_percent
1218 roll_fund.currency_type,
1222 roll_fund.balance_warning_percent,
1223 roll_fund.balance_stop_percent
1225 RETURNING id INTO new_fund;
1227 new_fund = roll_fund.new_fund_id;
1230 -- Determine the amount to transfer
1234 FROM acq.fund_spent_balance
1235 WHERE fund = roll_fund.old_fund;
1237 IF xfer_amount <> 0 THEN
1238 IF NOT encumb_only AND roll_fund.rollover THEN
1240 -- Transfer balance from old fund to new
1242 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
1244 PERFORM acq.transfer_fund(
1254 -- Transfer balance from old fund to the void
1256 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
1258 PERFORM acq.transfer_fund(
1264 'Rollover into the void'
1269 IF roll_fund.rollover THEN
1271 -- Move any lineitems from the old fund to the new one
1272 -- where the associated debit is an encumbrance.
1274 -- Any other tables tying expenditure details to funds should
1275 -- receive similar treatment. At this writing there are none.
1277 UPDATE acq.lineitem_detail
1280 fund = roll_fund.old_fund -- this condition may be redundant
1286 fund = roll_fund.old_fund
1290 -- Move encumbrance debits from the old fund to the new fund
1292 UPDATE acq.fund_debit
1295 fund = roll_fund.old_fund
1299 -- Mark old fund as inactive, now that we've closed it
1303 WHERE id = roll_fund.old_fund;
1306 $$ LANGUAGE plpgsql;
1308 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 $$
1309 SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE );
1312 INSERT into config.org_unit_setting_type
1313 (name, grp, label, description, datatype)
1315 'acq.fund.allow_rollover_without_money',
1318 'acq.fund.allow_rollover_without_money',
1319 'Allow funds to be rolled over without bringing the money along',
1324 'acq.fund.allow_rollover_without_money',
1325 '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.',
1332 -- 0731.schema.vandelay_item_overlay.sql
1334 SELECT evergreen.upgrade_deps_block_check('0731', :eg_version);
1336 ALTER TABLE vandelay.import_item_attr_definition
1337 ADD COLUMN internal_id TEXT;
1339 ALTER TABLE vandelay.import_item
1340 ADD COLUMN internal_id BIGINT;
1342 INSERT INTO permission.perm_list ( id, code, description ) VALUES
1343 ( 536, 'IMPORT_OVERLAY_COPY', oils_i18n_gettext( 536,
1344 'Allows a user to overlay copy data in MARC import', 'ppl', 'description'));
1346 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1349 item_data vandelay.import_item%ROWTYPE;
1352 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
1356 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1358 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1359 INSERT INTO vandelay.import_item (
1386 item_data.definition,
1387 item_data.owning_lib,
1389 item_data.call_number,
1390 item_data.copy_number,
1393 item_data.circulate,
1395 item_data.deposit_amount,
1400 item_data.circ_modifier,
1401 item_data.circ_as_type,
1402 item_data.alert_message,
1404 item_data.priv_note,
1405 item_data.internal_id,
1406 item_data.opac_visible,
1407 item_data.import_error,
1408 item_data.error_detail
1414 $func$ LANGUAGE PLPGSQL;
1417 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
1428 deposit_amount TEXT;
1442 tmp_attr_set RECORD;
1443 attr_set vandelay.import_item%ROWTYPE;
1450 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
1454 attr_set.definition := attr_def.id;
1456 -- Build the combined XPath
1460 WHEN attr_def.owning_lib IS NULL THEN 'null()'
1461 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
1462 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
1467 WHEN attr_def.circ_lib IS NULL THEN 'null()'
1468 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
1469 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
1474 WHEN attr_def.call_number IS NULL THEN 'null()'
1475 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
1476 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
1481 WHEN attr_def.copy_number IS NULL THEN 'null()'
1482 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
1483 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
1488 WHEN attr_def.status IS NULL THEN 'null()'
1489 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
1490 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
1495 WHEN attr_def.location IS NULL THEN 'null()'
1496 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
1497 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
1502 WHEN attr_def.circulate IS NULL THEN 'null()'
1503 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
1504 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
1509 WHEN attr_def.deposit IS NULL THEN 'null()'
1510 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
1511 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
1516 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
1517 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
1518 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
1523 WHEN attr_def.ref IS NULL THEN 'null()'
1524 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
1525 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
1530 WHEN attr_def.holdable IS NULL THEN 'null()'
1531 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
1532 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
1537 WHEN attr_def.price IS NULL THEN 'null()'
1538 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
1539 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
1544 WHEN attr_def.barcode IS NULL THEN 'null()'
1545 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
1546 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
1551 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
1552 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
1553 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
1558 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
1559 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
1560 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
1565 WHEN attr_def.alert_message IS NULL THEN 'null()'
1566 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
1567 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
1572 WHEN attr_def.opac_visible IS NULL THEN 'null()'
1573 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
1574 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
1579 WHEN attr_def.pub_note IS NULL THEN 'null()'
1580 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
1581 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
1585 WHEN attr_def.priv_note IS NULL THEN 'null()'
1586 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
1587 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
1592 WHEN attr_def.internal_id IS NULL THEN 'null()'
1593 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
1594 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
1600 owning_lib || '|' ||
1602 call_number || '|' ||
1603 copy_number || '|' ||
1608 deposit_amount || '|' ||
1613 circ_modifier || '|' ||
1614 circ_as_type || '|' ||
1615 alert_message || '|' ||
1618 internal_id || '|' ||
1623 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
1624 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
1625 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
1626 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
1629 attr_set.import_error := NULL;
1630 attr_set.error_detail := NULL;
1631 attr_set.deposit_amount := NULL;
1632 attr_set.copy_number := NULL;
1633 attr_set.price := NULL;
1635 IF tmp_attr_set.pr != '' THEN
1636 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
1637 IF tmp_str = '' THEN
1638 attr_set.import_error := 'import.item.invalid.price';
1639 attr_set.error_detail := tmp_attr_set.pr; -- original value
1640 RETURN NEXT attr_set; CONTINUE;
1642 attr_set.price := tmp_str::NUMERIC(8,2);
1645 IF tmp_attr_set.dep_amount != '' THEN
1646 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
1647 IF tmp_str = '' THEN
1648 attr_set.import_error := 'import.item.invalid.deposit_amount';
1649 attr_set.error_detail := tmp_attr_set.dep_amount;
1650 RETURN NEXT attr_set; CONTINUE;
1652 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
1655 IF tmp_attr_set.cnum != '' THEN
1656 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
1657 IF tmp_str = '' THEN
1658 attr_set.import_error := 'import.item.invalid.copy_number';
1659 attr_set.error_detail := tmp_attr_set.cnum;
1660 RETURN NEXT attr_set; CONTINUE;
1662 attr_set.copy_number := tmp_str::INT;
1665 IF tmp_attr_set.ol != '' THEN
1666 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
1668 attr_set.import_error := 'import.item.invalid.owning_lib';
1669 attr_set.error_detail := tmp_attr_set.ol;
1670 RETURN NEXT attr_set; CONTINUE;
1674 IF tmp_attr_set.clib != '' THEN
1675 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
1677 attr_set.import_error := 'import.item.invalid.circ_lib';
1678 attr_set.error_detail := tmp_attr_set.clib;
1679 RETURN NEXT attr_set; CONTINUE;
1683 IF tmp_attr_set.cs != '' THEN
1684 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
1686 attr_set.import_error := 'import.item.invalid.status';
1687 attr_set.error_detail := tmp_attr_set.cs;
1688 RETURN NEXT attr_set; CONTINUE;
1692 IF tmp_attr_set.circ_mod != '' THEN
1693 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
1695 attr_set.import_error := 'import.item.invalid.circ_modifier';
1696 attr_set.error_detail := tmp_attr_set.circ_mod;
1697 RETURN NEXT attr_set; CONTINUE;
1701 IF tmp_attr_set.circ_as != '' THEN
1702 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
1704 attr_set.import_error := 'import.item.invalid.circ_as_type';
1705 attr_set.error_detail := tmp_attr_set.circ_as;
1706 RETURN NEXT attr_set; CONTINUE;
1710 IF tmp_attr_set.cl != '' THEN
1712 -- search up the org unit tree for a matching copy location
1713 WITH RECURSIVE anscestor_depth AS (
1717 FROM actor.org_unit ou
1718 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1719 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
1724 FROM actor.org_unit ou
1725 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1726 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
1727 ) SELECT cpl.id INTO attr_set.location
1728 FROM anscestor_depth a
1729 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
1730 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
1731 ORDER BY a.depth DESC
1735 attr_set.import_error := 'import.item.invalid.location';
1736 attr_set.error_detail := tmp_attr_set.cs;
1737 RETURN NEXT attr_set; CONTINUE;
1741 attr_set.circulate :=
1742 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
1743 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
1746 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
1747 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
1749 attr_set.holdable :=
1750 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
1751 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
1753 attr_set.opac_visible :=
1754 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
1755 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
1758 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
1759 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
1761 attr_set.call_number := tmp_attr_set.cn; -- TEXT
1762 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1763 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1764 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1765 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1766 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1767 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
1769 RETURN NEXT attr_set;
1778 $$ LANGUAGE PLPGSQL;
1782 -- 0732.schema.acq-lineitem-summary.sql
1784 SELECT evergreen.upgrade_deps_block_check('0732', :eg_version);
1786 CREATE OR REPLACE VIEW acq.lineitem_summary AS
1790 SELECT COUNT(lid.id)
1791 FROM acq.lineitem_detail lid
1792 WHERE lineitem = li.id
1795 SELECT COUNT(lid.id)
1796 FROM acq.lineitem_detail lid
1797 WHERE recv_time IS NOT NULL AND lineitem = li.id
1800 SELECT COUNT(lid.id)
1801 FROM acq.lineitem_detail lid
1802 WHERE cancel_reason IS NOT NULL AND lineitem = li.id
1805 SELECT COUNT(lid.id)
1806 FROM acq.lineitem_detail lid
1807 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1808 WHERE NOT debit.encumbrance AND lineitem = li.id
1811 SELECT COUNT(DISTINCT(lid.id))
1812 FROM acq.lineitem_detail lid
1813 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
1814 WHERE lineitem = li.id
1817 SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
1818 FROM acq.lineitem_detail lid
1819 WHERE lid.cancel_reason IS NULL AND lineitem = li.id
1820 ) AS estimated_amount,
1822 SELECT SUM(debit.amount)::NUMERIC(8,2)
1823 FROM acq.lineitem_detail lid
1824 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1825 WHERE debit.encumbrance AND lineitem = li.id
1826 ) AS encumbrance_amount,
1828 SELECT SUM(debit.amount)::NUMERIC(8,2)
1829 FROM acq.lineitem_detail lid
1830 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1831 WHERE NOT debit.encumbrance AND lineitem = li.id
1834 FROM acq.lineitem AS li;
1839 -- Template update included here for reference only.
1840 -- The stock JEDI template is not updated here (see WHERE clause)
1841 -- We do update the environment, though, for easier local template
1842 -- updating. No env fields are removed (that aren't otherwise replaced).
1846 SELECT evergreen.upgrade_deps_block_check('0733', :eg_version);
1848 UPDATE action_trigger.event_definition SET template =
1850 [%# start JEDI document
1851 # Vendor specific kludges:
1852 # BT - vendcode goes to NAD/BY *suffix* w/ 91 qualifier
1853 # INGRAM - vendcode goes to NAD/BY *segment* w/ 91 qualifier (separately)
1854 # BRODART - vendcode goes to FTX segment (lineitem level)
1857 IF target.provider.edi_default.vendcode && target.provider.code == 'BRODART';
1858 xtra_ftx = target.provider.edi_default.vendcode;
1861 [%- BLOCK big_block -%]
1863 "recipient":"[% target.provider.san %]",
1864 "sender":"[% target.ordering_agency.mailing_address.san %]",
1866 "ORDERS":[ "order", {
1867 "po_number":[% target.id %],
1868 "date":"[% date.format(date.now, '%Y%m%d') %]",
1870 [% IF target.provider.edi_default.vendcode && (target.provider.code == 'BT' || target.provider.name.match('(?i)^BAKER & TAYLOR')) -%]
1871 {"id-qualifier": 91, "id":"[% target.ordering_agency.mailing_address.san _ ' ' _ target.provider.edi_default.vendcode %]"}
1872 [%- ELSIF target.provider.edi_default.vendcode && target.provider.code == 'INGRAM' -%]
1873 {"id":"[% target.ordering_agency.mailing_address.san %]"},
1874 {"id-qualifier": 91, "id":"[% target.provider.edi_default.vendcode %]"}
1876 {"id":"[% target.ordering_agency.mailing_address.san %]"}
1880 [%- # target.provider.name (target.provider.id) -%]
1881 "[% target.provider.san %]",
1882 {"id-qualifier": 92, "id":"[% target.provider.id %]"}
1884 "currency":"[% target.provider.currency_type %]",
1887 [%- FOR li IN target.lineitems %]
1889 "line_index":"[% li.id %]",
1890 "identifiers":[ [%-# li.isbns = helpers.get_li_isbns(li.attributes) %]
1891 [% FOR isbn IN helpers.get_li_isbns(li.attributes) -%]
1892 [% IF isbn.length == 13 -%]
1893 {"id-qualifier":"EN","id":"[% isbn %]"},
1895 {"id-qualifier":"IB","id":"[% isbn %]"},
1898 {"id-qualifier":"IN","id":"[% li.id %]"}
1900 "price":[% li.estimated_unit_price || '0.00' %],
1902 {"BTI":"[% helpers.get_li_attr_jedi('title', '', li.attributes) %]"},
1903 {"BPU":"[% helpers.get_li_attr_jedi('publisher', '', li.attributes) %]"},
1904 {"BPD":"[% helpers.get_li_attr_jedi('pubdate', '', li.attributes) %]"},
1905 {"BPH":"[% helpers.get_li_attr_jedi('pagination','', li.attributes) %]"}
1908 FOR note IN li.lineitem_notes;
1909 NEXT UNLESS note.vendor_public == 't';
1910 ftx_vals.push(note.value);
1912 IF xtra_ftx; ftx_vals.unshift(xtra_ftx); END;
1913 IF ftx_vals.size == 0; ftx_vals.unshift(''); END; # BT needs FTX+LIN for every LI, even if it is an empty one
1917 [% FOR note IN ftx_vals -%] "[% note %]"[% UNLESS loop.last %], [% END %][% END %]
1919 "quantity":[% li.lineitem_details.size %],
1922 [%- FOR lid IN li.lineitem_details;
1923 fund = lid.fund.code;
1924 item_type = lid.circ_modifier;
1925 callnumber = lid.cn_label;
1926 owning_lib = lid.owning_lib.shortname;
1927 location = lid.location;
1929 # when we have real copy data, treat it as authoritative
1930 acp = lid.eg_copy_id;
1932 item_type = acp.circ_modifier;
1933 callnumber = acp.call_number.label;
1934 location = acp.location.name;
1936 { [%- IF fund %] "fund" : "[% fund %]",[% END -%]
1937 [%- IF callnumber %] "call_number" : "[% callnumber %]", [% END -%]
1938 [%- IF item_type %] "item_type" : "[% item_type %]", [% END -%]
1939 [%- IF location %] "copy_location" : "[% location %]", [% END -%]
1940 [%- IF owning_lib %] "owning_lib" : "[% owning_lib %]", [% END -%]
1941 [%- #chomp %]"copy_id" : "[% lid.id %]" }[% ',' UNLESS loop.last %]
1945 }[% UNLESS loop.last %],[% END %]
1946 [%-# TODO: lineitem details (later) -%]
1949 "line_items":[% target.lineitems.size %]
1950 }] [%# close ORDERS array %]
1951 }] [%# close body array %]
1954 [% tempo = PROCESS big_block; helpers.escape_json(tempo) %]
1956 WHERE id = 23 AND FALSE; -- DON'T PERFORM THE UPDATE
1959 -- add copy-related fields to the environment if they're not already there.
1963 FROM action_trigger.environment
1966 path = 'lineitems.lineitem_details.owning_lib';
1968 INSERT INTO action_trigger.environment (event_def, path)
1969 VALUES (23, 'lineitems.lineitem_details.owning_lib');
1973 FROM action_trigger.environment
1976 path = 'lineitems.lineitem_details.fund';
1978 INSERT INTO action_trigger.environment (event_def, path)
1979 VALUES (23, 'lineitems.lineitem_details.fund');
1983 FROM action_trigger.environment
1986 path = 'lineitems.lineitem_details.location';
1988 INSERT INTO action_trigger.environment (event_def, path)
1989 VALUES (23, 'lineitems.lineitem_details.location');
1993 FROM action_trigger.environment
1996 path = 'lineitems.lineitem_details.eg_copy_id.location';
1998 INSERT INTO action_trigger.environment (event_def, path)
1999 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.location');
2003 FROM action_trigger.environment
2006 path = 'lineitems.lineitem_details.eg_copy_id.call_number';
2008 INSERT INTO action_trigger.environment (event_def, path)
2009 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.call_number');
2014 -- remove redundant entry
2015 DELETE FROM action_trigger.environment
2016 WHERE event_def = 23 AND path = 'lineitems.lineitem_details';
2021 -- Evergreen DB patch 0734.tpac_holdable_check.sql
2024 -- check whether patch can be applied
2025 SELECT evergreen.upgrade_deps_block_check('0734', :eg_version);
2027 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2032 JOIN asset.call_number acn ON acp.call_number = acn.id
2033 JOIN asset.copy_location acpl ON acp.location = acpl.id
2034 JOIN config.copy_status ccs ON acp.status = ccs.id
2037 AND acp.holdable = true
2038 AND acpl.holdable = true
2039 AND ccs.holdable = true
2040 AND acp.deleted = false
2047 $f$ LANGUAGE PLPGSQL;
2049 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2054 JOIN asset.call_number acn ON acp.call_number = acn.id
2055 JOIN asset.copy_location acpl ON acp.location = acpl.id
2056 JOIN config.copy_status ccs ON acp.status = ccs.id
2057 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
2059 mmsm.metarecord = rid
2060 AND acp.holdable = true
2061 AND acpl.holdable = true
2062 AND ccs.holdable = true
2063 AND acp.deleted = false
2070 $f$ LANGUAGE PLPGSQL;
2072 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
2076 depth INT DEFAULT NULL,
2077 includes TEXT[] DEFAULT NULL::TEXT[],
2078 slimit HSTORE DEFAULT NULL,
2079 soffset HSTORE DEFAULT NULL,
2080 include_xmlns BOOL DEFAULT TRUE,
2081 pref_lib INT DEFAULT NULL
2087 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2088 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
2089 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
2093 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
2096 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2098 FROM asset.opac_ou_record_copy_count($2, $1)
2102 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2104 FROM asset.staff_ou_record_copy_count($2, $1)
2108 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2110 FROM asset.opac_ou_record_copy_count($9, $1)
2115 WHEN ('bmp' = ANY ($5)) THEN
2117 name monograph_parts,
2118 (SELECT XMLAGG(bmp) FROM (
2119 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)
2120 FROM biblio.monograph_part
2128 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
2130 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
2131 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
2134 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
2135 FROM evergreen.located_uris($1, $2, $9) AS uris
2138 CASE WHEN ('ssub' = ANY ($5)) THEN
2141 (SELECT XMLAGG(ssub) FROM (
2142 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
2143 FROM serial.subscription
2144 WHERE record_entry = $1
2148 CASE WHEN ('acp' = ANY ($5)) THEN
2150 name foreign_copies,
2151 (SELECT XMLAGG(acp) FROM (
2152 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
2153 FROM biblio.peer_bib_copy_map p
2154 JOIN asset.copy c ON (p.target_copy = c.id)
2155 WHERE NOT c.deleted AND p.peer_record = $1
2156 LIMIT ($6 -> 'acp')::INT
2157 OFFSET ($7 -> 'acp')::INT
2162 $F$ LANGUAGE SQL STABLE;
2164 -- Evergreen DB patch 0735.data.search_filter_group_perms.sql
2167 -- check whether patch can be applied
2168 SELECT evergreen.upgrade_deps_block_check('0735', :eg_version);
2170 INSERT INTO permission.perm_list ( id, code, description )
2173 'ADMIN_SEARCH_FILTER_GROUP',
2176 'Allows staff to manage search filter groups and entries',
2183 'VIEW_SEARCH_FILTER_GROUP',
2186 'Allows staff to view search filter groups and entries',
2193 -- check whether patch can be applied
2194 SELECT evergreen.upgrade_deps_block_check('0737', :eg_version);
2196 UPDATE action_trigger.event_definition
2200 # target is the bookbag itself. The 'items' variable does not need to be in
2201 # the environment because a special reactor will take care of filling it in.
2204 bibxml = helpers.unapi_bre(item.target_biblio_record_entry, {flesh => '{mra}'});
2206 FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
2207 title = title _ part.textContent;
2209 author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
2210 item_type = bibxml.findnodes('//*[local-name()="attributes"]/*[local-name()="field"][@name="item_type"]').getAttribute('coded-value');
2212 FOR pdatum IN bibxml.findnodes('//*[@tag="260"]/*[@code="c"]');
2214 pub_date = pub_date _ ", " _ pdatum.textContent;
2216 pub_date = pdatum.textContent;
2219 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";
2222 WHERE name = 'Bookbag CSV';