1 --Upgrade Script for 2.2 to 2.3.0
3 \set eg_version '''2.3.0'''
6 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.0', :eg_version);
7 -- Evergreen DB patch 0703.tpac_value_maps.sql
9 -- check whether patch can be applied
10 SELECT evergreen.upgrade_deps_block_check('0703', :eg_version);
12 ALTER TABLE config.coded_value_map
13 ADD COLUMN opac_visible BOOL NOT NULL DEFAULT TRUE,
14 ADD COLUMN search_label TEXT,
15 ADD COLUMN is_simple BOOL NOT NULL DEFAULT FALSE;
19 SELECT evergreen.upgrade_deps_block_check('0712', :eg_version);
21 -- General purpose query container. Any table the needs to store
22 -- a QueryParser query should store it here. This will be the
23 -- source for top-level and QP sub-search inclusion queries.
24 CREATE TABLE actor.search_query (
25 id SERIAL PRIMARY KEY,
26 label TEXT NOT NULL, -- i18n
27 query_text TEXT NOT NULL -- QP text
30 -- e.g. "Reading Level"
31 CREATE TABLE actor.search_filter_group (
32 id SERIAL PRIMARY KEY,
33 owner INT NOT NULL REFERENCES actor.org_unit (id)
34 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
35 code TEXT NOT NULL, -- for CGI, etc.
36 label TEXT NOT NULL, -- i18n
37 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
38 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
39 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
42 -- e.g. "Adult", "Teen", etc.
43 CREATE TABLE actor.search_filter_group_entry (
44 id SERIAL PRIMARY KEY,
45 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
46 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
47 pos INT NOT NULL DEFAULT 0,
48 query INT NOT NULL REFERENCES actor.search_query(id)
49 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
54 SELECT evergreen.upgrade_deps_block_check('0713', :eg_version);
56 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
57 'ui.grid_columns.circ.hold_pull_list',
61 'ui.grid_columns.circ.hold_pull_list',
67 'ui.grid_columns.circ.hold_pull_list',
68 'Hold Pull List Saved Column Settings',
77 SELECT evergreen.upgrade_deps_block_check('0714', :eg_version);
79 INSERT into config.org_unit_setting_type
80 (name, grp, label, description, datatype)
82 'opac.patron.auto_overide_hold_events',
85 'opac.patron.auto_overide_hold_events',
86 'Auto-Override Permitted Hold Blocks (Patrons)',
91 'opac.patron.auto_overide_hold_events',
92 'When a patron places a hold that fails and the patron has the correct permission ' ||
93 'to override the hold, automatically override the hold without presenting a message ' ||
94 'to the patron and requiring that the patron make a decision to override',
101 -- Evergreen DB patch 0718.data.add-to-permanent-bookbag.sql
103 -- check whether patch can be applied
104 SELECT evergreen.upgrade_deps_block_check('0718', :eg_version);
106 INSERT into config.org_unit_setting_type
107 (name, grp, label, description, datatype)
109 'opac.patron.temporary_list_warn',
112 'opac.patron.temporary_list_warn',
113 'Warn patrons when adding to a temporary book list',
118 'opac.patron.temporary_list_warn',
119 'Present a warning dialog to the patron when a patron adds a book to a temporary book bag.',
126 INSERT INTO config.usr_setting_type
127 (name,grp,opac_visible,label,description,datatype)
129 'opac.temporary_list_no_warn',
133 'opac.temporary_list_no_warn',
134 'Opt out of warning when adding a book to a temporary book list',
139 'opac.temporary_list_no_warn',
140 'Opt out of warning when adding a book to a temporary book list',
147 INSERT INTO config.usr_setting_type
148 (name,grp,opac_visible,label,description,datatype)
155 'Default list to use when adding to a bookbag',
161 'Default list to use when adding to a bookbag',
169 SELECT evergreen.upgrade_deps_block_check('0719', :eg_version);
171 INSERT INTO config.org_unit_setting_type (
172 name, label, grp, description, datatype
174 'circ.staff.max_visible_event_age',
175 'Maximum visible age of User Trigger Events in Staff Interfaces',
177 '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.',
181 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
182 'ui.grid_columns.actor.user.event_log',
186 'ui.grid_columns.actor.user.event_log',
192 'ui.grid_columns.actor.user.event_log',
193 'User Event Log Saved Column Settings',
200 INSERT INTO permission.perm_list ( id, code, description )
203 'VIEW_TRIGGER_EVENT',
206 'Allows a user to view circ- and hold-related action/trigger events',
213 SELECT evergreen.upgrade_deps_block_check('0720', :eg_version);
215 ALTER TABLE config.circ_matrix_weights
216 ADD COLUMN copy_location NUMERIC(6,2) NOT NULL DEFAULT 5.0;
217 UPDATE config.circ_matrix_weights
218 SET copy_location = 0.0 WHERE name = 'All_Equal';
219 ALTER TABLE config.circ_matrix_weights
220 ALTER COLUMN copy_location DROP DEFAULT; -- for consistency w/ baseline schema
222 ALTER TABLE config.circ_matrix_matchpoint
223 ADD COLUMN copy_location INTEGER REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED;
225 DROP INDEX config.ccmm_once_per_paramset;
227 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;
229 -- Linkage between limit sets and circ mods
230 CREATE TABLE config.circ_limit_set_copy_loc_map (
231 id SERIAL PRIMARY KEY,
232 limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
233 copy_loc INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
234 CONSTRAINT cl_once_per_set UNIQUE (limit_set, copy_loc)
237 -- Add support for checking config.circ_limit_set_copy_loc_map's
238 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL )
239 RETURNS SETOF action.circ_matrix_test_result AS $func$
241 user_object actor.usr%ROWTYPE;
242 standing_penalty config.standing_penalty%ROWTYPE;
243 item_object asset.copy%ROWTYPE;
244 item_status_object config.copy_status%ROWTYPE;
245 item_location_object asset.copy_location%ROWTYPE;
246 result action.circ_matrix_test_result;
247 circ_test action.found_circ_matrix_matchpoint;
248 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
249 circ_limit_set config.circ_limit_set%ROWTYPE;
250 hold_ratio action.hold_stats%ROWTYPE;
253 context_org_list INT[];
256 -- Assume success unless we hit a failure condition
257 result.success := TRUE;
259 -- Need user info to look up matchpoints
260 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
262 -- (Insta)Fail if we couldn't find the user
263 IF user_object.id IS NULL THEN
264 result.fail_part := 'no_user';
265 result.success := FALSE;
271 -- Need item info to look up matchpoints
272 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
274 -- (Insta)Fail if we couldn't find the item
275 IF item_object.id IS NULL THEN
276 result.fail_part := 'no_item';
277 result.success := FALSE;
283 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
285 circ_matchpoint := circ_test.matchpoint;
286 result.matchpoint := circ_matchpoint.id;
287 result.circulate := circ_matchpoint.circulate;
288 result.duration_rule := circ_matchpoint.duration_rule;
289 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
290 result.max_fine_rule := circ_matchpoint.max_fine_rule;
291 result.hard_due_date := circ_matchpoint.hard_due_date;
292 result.renewals := circ_matchpoint.renewals;
293 result.grace_period := circ_matchpoint.grace_period;
294 result.buildrows := circ_test.buildrows;
296 -- (Insta)Fail if we couldn't find a matchpoint
297 IF circ_test.success = false THEN
298 result.fail_part := 'no_matchpoint';
299 result.success := FALSE;
305 -- All failures before this point are non-recoverable
306 -- Below this point are possibly overridable failures
308 -- Fail if the user is barred
309 IF user_object.barred IS TRUE THEN
310 result.fail_part := 'actor.usr.barred';
311 result.success := FALSE;
316 -- Fail if the item can't circulate
317 IF item_object.circulate IS FALSE THEN
318 result.fail_part := 'asset.copy.circulate';
319 result.success := FALSE;
324 -- Fail if the item isn't in a circulateable status on a non-renewal
325 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
326 result.fail_part := 'asset.copy.status';
327 result.success := FALSE;
330 -- Alternately, fail if the item isn't checked out on a renewal
331 ELSIF renewal AND item_object.status <> 1 THEN
332 result.fail_part := 'asset.copy.status';
333 result.success := FALSE;
338 -- Fail if the item can't circulate because of the shelving location
339 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
340 IF item_location_object.circulate IS FALSE THEN
341 result.fail_part := 'asset.copy_location.circulate';
342 result.success := FALSE;
347 -- Use Circ OU for penalties and such
348 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
351 penalty_type = '%RENEW%';
353 penalty_type = '%CIRC%';
356 FOR standing_penalty IN
357 SELECT DISTINCT csp.*
358 FROM actor.usr_standing_penalty usp
359 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
360 WHERE usr = match_user
361 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
362 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
363 AND csp.block_list LIKE penalty_type LOOP
365 result.fail_part := standing_penalty.name;
366 result.success := FALSE;
371 -- Fail if the test is set to hard non-circulating
372 IF circ_matchpoint.circulate IS FALSE THEN
373 result.fail_part := 'config.circ_matrix_test.circulate';
374 result.success := FALSE;
379 -- Fail if the total copy-hold ratio is too low
380 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
381 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
382 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
383 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
384 result.success := FALSE;
390 -- Fail if the available copy-hold ratio is too low
391 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
392 IF hold_ratio.hold_count IS NULL THEN
393 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
395 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
396 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
397 result.success := FALSE;
403 -- Fail if the user has too many items out by defined limit sets
404 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
405 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
406 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
407 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
409 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
410 SELECT INTO context_org_list ARRAY_AGG(aou.id)
411 FROM actor.org_unit_full_path( circ_ou ) aou
412 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
413 WHERE aout.depth >= circ_limit_set.depth;
414 IF circ_limit_set.global THEN
415 WITH RECURSIVE descendant_depth AS (
418 FROM actor.org_unit ou
419 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
423 FROM actor.org_unit ou
424 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
425 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
427 SELECT INTO items_out COUNT(DISTINCT circ.id)
428 FROM action.circulation circ
429 JOIN asset.copy copy ON (copy.id = circ.target_copy)
430 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
431 WHERE circ.usr = match_user
432 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
433 AND circ.checkin_time IS NULL
434 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
435 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
436 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
437 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
439 IF items_out >= circ_limit_set.items_out THEN
440 result.fail_part := 'config.circ_matrix_circ_mod_test';
441 result.success := FALSE;
446 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;
449 -- If we passed everything, return the successful matchpoint
456 $func$ LANGUAGE plpgsql;
459 -- adding copy_loc to circ_matrix_matchpoint
460 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$
462 cn_object asset.call_number%ROWTYPE;
463 rec_descriptor metabib.rec_descriptor%ROWTYPE;
464 cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
465 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
466 weights config.circ_matrix_weights%ROWTYPE;
468 my_item_age INTERVAL;
469 denominator NUMERIC(6,2);
471 result action.found_circ_matrix_matchpoint;
474 result.success = false;
477 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
478 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
480 -- Pre-generate this so we only calc it once
481 IF user_object.dob IS NOT NULL THEN
482 SELECT INTO user_age age(user_object.dob);
486 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
488 -- Grab the closest set circ weight setting.
489 SELECT INTO weights cw.*
490 FROM config.weight_assoc wa
491 JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
492 JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
497 -- No weights? Bad admin! Defaults to handle that anyway.
498 IF weights.id IS NULL THEN
500 weights.org_unit := 10.0;
501 weights.circ_modifier := 5.0;
502 weights.copy_location := 5.0;
503 weights.marc_type := 4.0;
504 weights.marc_form := 3.0;
505 weights.marc_bib_level := 2.0;
506 weights.marc_vr_format := 2.0;
507 weights.copy_circ_lib := 8.0;
508 weights.copy_owning_lib := 8.0;
509 weights.user_home_ou := 8.0;
510 weights.ref_flag := 1.0;
511 weights.juvenile_flag := 6.0;
512 weights.is_renewal := 7.0;
513 weights.usr_age_lower_bound := 0.0;
514 weights.usr_age_upper_bound := 0.0;
515 weights.item_age := 0.0;
518 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
519 -- If you break your org tree with funky parenting this may be wrong
520 -- 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
521 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
522 WITH all_distance(distance) AS (
523 SELECT depth AS distance FROM actor.org_unit_type
525 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
527 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
529 -- Loop over all the potential matchpoints
530 FOR cur_matchpoint IN
532 FROM config.circ_matrix_matchpoint m
533 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
534 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
535 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
536 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
537 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
540 -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
542 -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
543 AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
544 AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
545 AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
547 AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
548 -- Static User Checks
549 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
550 AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
551 AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
552 -- Static Item Checks
553 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
554 AND (m.copy_location IS NULL OR m.copy_location = item_object.location)
555 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
556 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
557 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
558 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
559 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
560 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
563 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
565 CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
566 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
567 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
568 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
569 -- Circ Type -- Note: 4^x is equiv to 2^(2*x)
570 CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
571 -- Static User Checks
572 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
573 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
574 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
575 -- Static Item Checks
576 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
577 CASE WHEN m.copy_location IS NOT NULL THEN 4^weights.copy_location ELSE 0.0 END +
578 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
579 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
580 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
581 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
582 -- Item age has a slight adjustment to weight based on value.
583 -- This should ensure that a shorter age limit comes first when all else is equal.
584 -- NOTE: This assumes that intervals will normally be in days.
585 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,
586 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
587 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
590 -- Record the full matching row list
591 row_list := row_list || cur_matchpoint.id;
593 -- No matchpoint yet?
594 IF matchpoint.id IS NULL THEN
595 -- Take the entire matchpoint as a starting point
596 matchpoint := cur_matchpoint;
597 CONTINUE; -- No need to look at this row any more.
600 -- Incomplete matchpoint?
601 IF matchpoint.circulate IS NULL THEN
602 matchpoint.circulate := cur_matchpoint.circulate;
604 IF matchpoint.duration_rule IS NULL THEN
605 matchpoint.duration_rule := cur_matchpoint.duration_rule;
607 IF matchpoint.recurring_fine_rule IS NULL THEN
608 matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
610 IF matchpoint.max_fine_rule IS NULL THEN
611 matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
613 IF matchpoint.hard_due_date IS NULL THEN
614 matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
616 IF matchpoint.total_copy_hold_ratio IS NULL THEN
617 matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
619 IF matchpoint.available_copy_hold_ratio IS NULL THEN
620 matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
622 IF matchpoint.renewals IS NULL THEN
623 matchpoint.renewals := cur_matchpoint.renewals;
625 IF matchpoint.grace_period IS NULL THEN
626 matchpoint.grace_period := cur_matchpoint.grace_period;
630 -- Check required fields
631 IF matchpoint.circulate IS NOT NULL AND
632 matchpoint.duration_rule IS NOT NULL AND
633 matchpoint.recurring_fine_rule IS NOT NULL AND
634 matchpoint.max_fine_rule IS NOT NULL THEN
635 -- All there? We have a completed match.
636 result.success := true;
639 -- Include the assembled matchpoint, even if it isn't complete
640 result.matchpoint := matchpoint;
642 -- Include (for debugging) the full list of matching rows
643 result.buildrows := row_list;
645 -- Hand the result back to caller
648 $func$ LANGUAGE plpgsql;
653 SELECT evergreen.upgrade_deps_block_check('0721', :eg_version);
655 UPDATE config.standing_penalty
656 SET block_list = REPLACE(block_list, 'HOLD', 'HOLD|CAPTURE')
658 -- STAFF_ penalties have names that match their block list
659 name NOT LIKE 'STAFF_%'
660 -- belt & suspenders, also good for testing
661 AND block_list NOT LIKE '%CAPTURE%';
663 -- CIRC|FULFILL is now the same as CIRC previously was by itself
664 UPDATE config.standing_penalty
665 SET block_list = REPLACE(block_list, 'CIRC', 'CIRC|FULFILL')
667 -- STAFF_ penalties have names that match their block list
668 name NOT LIKE 'STAFF_%'
669 -- belt & suspenders, also good for testing
670 AND block_list NOT LIKE '%FULFILL%';
673 -- apply the HOLD vs CAPTURE block logic
674 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$
677 user_object actor.usr%ROWTYPE;
678 age_protect_object config.rule_age_hold_protect%ROWTYPE;
679 standing_penalty config.standing_penalty%ROWTYPE;
680 transit_range_ou_type actor.org_unit_type%ROWTYPE;
681 transit_source actor.org_unit%ROWTYPE;
682 item_object asset.copy%ROWTYPE;
683 item_cn_object asset.call_number%ROWTYPE;
684 item_status_object config.copy_status%ROWTYPE;
685 item_location_object asset.copy_location%ROWTYPE;
686 ou_skip actor.org_unit_setting%ROWTYPE;
687 result action.matrix_test_result;
688 hold_test config.hold_matrix_matchpoint%ROWTYPE;
689 use_active_date TEXT;
690 age_protect_date TIMESTAMP WITH TIME ZONE;
692 hold_transit_prox INT;
693 frozen_hold_count INT;
694 context_org_list INT[];
698 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
699 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
701 result.success := TRUE;
703 -- The HOLD penalty block only applies to new holds.
704 -- The CAPTURE penalty block applies to existing holds.
705 hold_penalty := 'HOLD';
707 hold_penalty := 'CAPTURE';
710 -- Fail if we couldn't find a user
711 IF user_object.id IS NULL THEN
712 result.fail_part := 'no_user';
713 result.success := FALSE;
719 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
721 -- Fail if we couldn't find a copy
722 IF item_object.id IS NULL THEN
723 result.fail_part := 'no_item';
724 result.success := FALSE;
730 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
731 result.matchpoint := matchpoint_id;
733 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
735 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
736 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
737 result.fail_part := 'circ.holds.target_skip_me';
738 result.success := FALSE;
744 -- Fail if user is barred
745 IF user_object.barred IS TRUE THEN
746 result.fail_part := 'actor.usr.barred';
747 result.success := FALSE;
753 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
754 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
755 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
757 -- Fail if we couldn't find any matchpoint (requires a default)
758 IF matchpoint_id IS NULL THEN
759 result.fail_part := 'no_matchpoint';
760 result.success := FALSE;
766 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
768 IF hold_test.holdable IS FALSE THEN
769 result.fail_part := 'config.hold_matrix_test.holdable';
770 result.success := FALSE;
775 IF item_object.holdable IS FALSE THEN
776 result.fail_part := 'item.holdable';
777 result.success := FALSE;
782 IF item_status_object.holdable IS FALSE THEN
783 result.fail_part := 'status.holdable';
784 result.success := FALSE;
789 IF item_location_object.holdable IS FALSE THEN
790 result.fail_part := 'location.holdable';
791 result.success := FALSE;
796 IF hold_test.transit_range IS NOT NULL THEN
797 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
798 IF hold_test.distance_is_from_owner THEN
799 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;
801 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
804 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
807 result.fail_part := 'transit_range';
808 result.success := FALSE;
814 FOR standing_penalty IN
815 SELECT DISTINCT csp.*
816 FROM actor.usr_standing_penalty usp
817 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
818 WHERE usr = match_user
819 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
820 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
821 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
823 result.fail_part := standing_penalty.name;
824 result.success := FALSE;
829 IF hold_test.stop_blocked_user IS TRUE THEN
830 FOR standing_penalty IN
831 SELECT DISTINCT csp.*
832 FROM actor.usr_standing_penalty usp
833 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
834 WHERE usr = match_user
835 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
836 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
837 AND csp.block_list LIKE '%CIRC%' LOOP
839 result.fail_part := standing_penalty.name;
840 result.success := FALSE;
846 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
847 SELECT INTO hold_count COUNT(*)
848 FROM action.hold_request
849 WHERE usr = match_user
850 AND fulfillment_time IS NULL
851 AND cancel_time IS NULL
852 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
854 IF hold_count >= hold_test.max_holds THEN
855 result.fail_part := 'config.hold_matrix_test.max_holds';
856 result.success := FALSE;
862 IF item_object.age_protect IS NOT NULL THEN
863 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
864 IF hold_test.distance_is_from_owner THEN
865 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
867 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
869 IF use_active_date = 'true' THEN
870 age_protect_date := COALESCE(item_object.active_date, NOW());
872 age_protect_date := item_object.create_date;
874 IF age_protect_date + age_protect_object.age > NOW() THEN
875 IF hold_test.distance_is_from_owner THEN
876 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
877 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
879 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
882 IF hold_transit_prox > age_protect_object.prox THEN
883 result.fail_part := 'config.rule_age_hold_protect.prox';
884 result.success := FALSE;
897 $func$ LANGUAGE plpgsql;
900 -- Evergreen DB patch 0727.function.xml_pretty_print.sql
902 -- A simple pretty printer for XML.
903 -- Particularly useful for debugging the biblio.record_entry.marc field.
906 -- check whether patch can be applied
907 SELECT evergreen.upgrade_deps_block_check('0727', :eg_version);
909 CREATE OR REPLACE FUNCTION evergreen.xml_pretty_print(input XML)
913 SELECT xslt_process($1::text,
914 $$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
916 <xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
917 <xsl:strip-space elements="*"/>
918 <xsl:template match="@*|node()">
920 <xsl:apply-templates select="@*|node()"/>
927 COMMENT ON FUNCTION evergreen.xml_pretty_print(input XML) IS
928 'Simple pretty printer for XML, as written by Andrew Dunstan at http://goo.gl/zBHIk';
931 SELECT evergreen.upgrade_deps_block_check('0728', :eg_version);
933 INSERT INTO actor.search_filter_group (owner, code, label)
934 VALUES (1, 'kpac_main', 'Kid''s OPAC Search Filter');
936 INSERT INTO actor.search_query (label, query_text)
937 VALUES ('Children''s Materials', 'audience(a,b,c)');
938 INSERT INTO actor.search_query (label, query_text)
939 VALUES ('Young Adult Materials', 'audience(j,d)');
940 INSERT INTO actor.search_query (label, query_text)
941 VALUES ('General/Adult Materials', 'audience(e,f,g, )');
943 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
945 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
946 (SELECT id FROM actor.search_query WHERE label = 'Children''s Materials'),
949 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
951 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
952 (SELECT id FROM actor.search_query WHERE label = 'Young Adult Materials'),
955 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
957 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
958 (SELECT id FROM actor.search_query WHERE label = 'General/Adult Materials'),
963 -- Evergreen DB patch 0729.vr_format_value_maps.sql
966 -- check whether patch can be applied
967 SELECT evergreen.upgrade_deps_block_check('0729', :eg_version);
969 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$
971 current_row config.coded_value_map%ROWTYPE;
973 -- Look for a current value
974 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
976 IF FOUND AND NOT add_only THEN
977 -- Update anything we were handed
978 current_row.value := COALESCE(current_row.value, in_value);
979 current_row.description := COALESCE(current_row.description, in_description);
980 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
981 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
982 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
983 UPDATE config.coded_value_map
985 value = current_row.value,
986 description = current_row.description,
987 opac_visible = current_row.opac_visible,
988 search_label = current_row.search_label,
989 is_simple = current_row.is_simple
990 WHERE id = current_row.id;
992 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
993 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
996 $f$ LANGUAGE PLPGSQL;
998 SELECT config.update_coded_value_map('vr_format', 'a', 'Beta', add_only := TRUE);
999 SELECT config.update_coded_value_map('vr_format', 'b', 'VHS', add_only := TRUE);
1000 SELECT config.update_coded_value_map('vr_format', 'c', 'U-matic', add_only := TRUE);
1001 SELECT config.update_coded_value_map('vr_format', 'd', 'EIAJ', add_only := TRUE);
1002 SELECT config.update_coded_value_map('vr_format', 'e', 'Type C', add_only := TRUE);
1003 SELECT config.update_coded_value_map('vr_format', 'f', 'Quadruplex', add_only := TRUE);
1004 SELECT config.update_coded_value_map('vr_format', 'g', 'Laserdisc', add_only := TRUE);
1005 SELECT config.update_coded_value_map('vr_format', 'h', 'CED videodisc', add_only := TRUE);
1006 SELECT config.update_coded_value_map('vr_format', 'i', 'Betacam', add_only := TRUE);
1007 SELECT config.update_coded_value_map('vr_format', 'j', 'Betacam SP', add_only := TRUE);
1008 SELECT config.update_coded_value_map('vr_format', 'k', 'Super-VHS', add_only := TRUE);
1009 SELECT config.update_coded_value_map('vr_format', 'm', 'M-II', add_only := TRUE);
1010 SELECT config.update_coded_value_map('vr_format', 'o', 'D-2', add_only := TRUE);
1011 SELECT config.update_coded_value_map('vr_format', 'p', '8 mm.', add_only := TRUE);
1012 SELECT config.update_coded_value_map('vr_format', 'q', 'Hi-8 mm.', add_only := TRUE);
1013 SELECT config.update_coded_value_map('vr_format', 's', 'Blu-ray disc', add_only := TRUE);
1014 SELECT config.update_coded_value_map('vr_format', 'u', 'Unknown', add_only := TRUE);
1015 SELECT config.update_coded_value_map('vr_format', 'v', 'DVD', add_only := TRUE);
1016 SELECT config.update_coded_value_map('vr_format', 'z', 'Other', add_only := TRUE);
1017 SELECT config.update_coded_value_map('vr_format', ' ', 'Unspecified', add_only := TRUE);
1021 SELECT evergreen.upgrade_deps_block_check('0730', :eg_version);
1023 DROP FUNCTION acq.propagate_funds_by_org_tree (INT, INT, INT);
1024 DROP FUNCTION acq.propagate_funds_by_org_unit (INT, INT, INT);
1026 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
1029 org_unit_id INTEGER,
1030 include_desc BOOL DEFAULT TRUE
1031 ) RETURNS VOID AS $$
1042 IF old_year IS NULL THEN
1043 RAISE EXCEPTION 'Input year argument is NULL';
1044 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1045 RAISE EXCEPTION 'Input year is out of range';
1048 IF user_id IS NULL THEN
1049 RAISE EXCEPTION 'Input user id argument is NULL';
1052 IF org_unit_id IS NULL THEN
1053 RAISE EXCEPTION 'Org unit id argument is NULL';
1055 SELECT TRUE INTO org_found
1057 WHERE id = org_unit_id;
1059 IF org_found IS NULL THEN
1060 RAISE EXCEPTION 'Org unit id is invalid';
1064 -- Loop over the applicable funds
1066 FOR old_fund in SELECT * FROM acq.fund
1070 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1071 OR (NOT include_desc AND org = org_unit_id ) )
1075 INSERT INTO acq.fund (
1083 balance_warning_percent,
1084 balance_stop_percent
1089 old_fund.currency_type,
1093 old_fund.balance_warning_percent,
1094 old_fund.balance_stop_percent
1096 RETURNING id INTO new_id;
1098 WHEN unique_violation THEN
1099 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1102 --RAISE NOTICE 'Propagating fund % to fund %',
1103 -- old_fund.code, new_id;
1106 $$ LANGUAGE plpgsql;
1108 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$
1109 SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE );
1113 DROP FUNCTION acq.rollover_funds_by_org_tree (INT, INT, INT);
1114 DROP FUNCTION acq.rollover_funds_by_org_unit (INT, INT, INT);
1117 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
1120 org_unit_id INTEGER,
1121 encumb_only BOOL DEFAULT FALSE,
1122 include_desc BOOL DEFAULT TRUE
1123 ) RETURNS VOID AS $$
1127 new_year INT := old_year + 1;
1130 xfer_amount NUMERIC := 0;
1139 IF old_year IS NULL THEN
1140 RAISE EXCEPTION 'Input year argument is NULL';
1141 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1142 RAISE EXCEPTION 'Input year is out of range';
1145 IF user_id IS NULL THEN
1146 RAISE EXCEPTION 'Input user id argument is NULL';
1149 IF org_unit_id IS NULL THEN
1150 RAISE EXCEPTION 'Org unit id argument is NULL';
1153 -- Validate the org unit
1158 WHERE id = org_unit_id;
1160 IF org_found IS NULL THEN
1161 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
1162 ELSIF encumb_only THEN
1163 SELECT INTO perm_ous value::BOOL FROM
1164 actor.org_unit_ancestor_setting(
1165 'acq.fund.allow_rollover_without_money', org_unit_id
1167 IF NOT FOUND OR NOT perm_ous THEN
1168 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
1173 -- Loop over the propagable funds to identify the details
1174 -- from the old fund plus the id of the new one, if it exists.
1178 oldf.id AS old_fund,
1184 newf.id AS new_fund_id
1187 LEFT JOIN acq.fund AS newf
1188 ON ( oldf.code = newf.code )
1190 oldf.year = old_year
1192 AND newf.year = new_year
1193 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1194 OR (NOT include_desc AND oldf.org = org_unit_id ) )
1196 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
1198 IF roll_fund.new_fund_id IS NULL THEN
1200 -- The old fund hasn't been propagated yet. Propagate it now.
1202 INSERT INTO acq.fund (
1210 balance_warning_percent,
1211 balance_stop_percent
1216 roll_fund.currency_type,
1220 roll_fund.balance_warning_percent,
1221 roll_fund.balance_stop_percent
1223 RETURNING id INTO new_fund;
1225 new_fund = roll_fund.new_fund_id;
1228 -- Determine the amount to transfer
1232 FROM acq.fund_spent_balance
1233 WHERE fund = roll_fund.old_fund;
1235 IF xfer_amount <> 0 THEN
1236 IF NOT encumb_only AND roll_fund.rollover THEN
1238 -- Transfer balance from old fund to new
1240 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
1242 PERFORM acq.transfer_fund(
1252 -- Transfer balance from old fund to the void
1254 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
1256 PERFORM acq.transfer_fund(
1262 'Rollover into the void'
1267 IF roll_fund.rollover THEN
1269 -- Move any lineitems from the old fund to the new one
1270 -- where the associated debit is an encumbrance.
1272 -- Any other tables tying expenditure details to funds should
1273 -- receive similar treatment. At this writing there are none.
1275 UPDATE acq.lineitem_detail
1278 fund = roll_fund.old_fund -- this condition may be redundant
1284 fund = roll_fund.old_fund
1288 -- Move encumbrance debits from the old fund to the new fund
1290 UPDATE acq.fund_debit
1293 fund = roll_fund.old_fund
1297 -- Mark old fund as inactive, now that we've closed it
1301 WHERE id = roll_fund.old_fund;
1304 $$ LANGUAGE plpgsql;
1306 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 $$
1307 SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE );
1310 INSERT into config.org_unit_setting_type
1311 (name, grp, label, description, datatype)
1313 'acq.fund.allow_rollover_without_money',
1316 'acq.fund.allow_rollover_without_money',
1317 'Allow funds to be rolled over without bringing the money along',
1322 'acq.fund.allow_rollover_without_money',
1323 '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.',
1330 -- 0731.schema.vandelay_item_overlay.sql
1332 SELECT evergreen.upgrade_deps_block_check('0731', :eg_version);
1334 ALTER TABLE vandelay.import_item_attr_definition
1335 ADD COLUMN internal_id TEXT;
1337 ALTER TABLE vandelay.import_item
1338 ADD COLUMN internal_id BIGINT;
1340 INSERT INTO permission.perm_list ( id, code, description ) VALUES
1341 ( 536, 'IMPORT_OVERLAY_COPY', oils_i18n_gettext( 536,
1342 'Allows a user to overlay copy data in MARC import', 'ppl', 'description'));
1344 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1347 item_data vandelay.import_item%ROWTYPE;
1350 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
1354 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1356 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1357 INSERT INTO vandelay.import_item (
1384 item_data.definition,
1385 item_data.owning_lib,
1387 item_data.call_number,
1388 item_data.copy_number,
1391 item_data.circulate,
1393 item_data.deposit_amount,
1398 item_data.circ_modifier,
1399 item_data.circ_as_type,
1400 item_data.alert_message,
1402 item_data.priv_note,
1403 item_data.internal_id,
1404 item_data.opac_visible,
1405 item_data.import_error,
1406 item_data.error_detail
1412 $func$ LANGUAGE PLPGSQL;
1415 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
1426 deposit_amount TEXT;
1440 tmp_attr_set RECORD;
1441 attr_set vandelay.import_item%ROWTYPE;
1448 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
1452 attr_set.definition := attr_def.id;
1454 -- Build the combined XPath
1458 WHEN attr_def.owning_lib IS NULL THEN 'null()'
1459 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
1460 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
1465 WHEN attr_def.circ_lib IS NULL THEN 'null()'
1466 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
1467 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
1472 WHEN attr_def.call_number IS NULL THEN 'null()'
1473 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
1474 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
1479 WHEN attr_def.copy_number IS NULL THEN 'null()'
1480 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
1481 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
1486 WHEN attr_def.status IS NULL THEN 'null()'
1487 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
1488 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
1493 WHEN attr_def.location IS NULL THEN 'null()'
1494 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
1495 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
1500 WHEN attr_def.circulate IS NULL THEN 'null()'
1501 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
1502 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
1507 WHEN attr_def.deposit IS NULL THEN 'null()'
1508 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
1509 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
1514 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
1515 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
1516 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
1521 WHEN attr_def.ref IS NULL THEN 'null()'
1522 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
1523 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
1528 WHEN attr_def.holdable IS NULL THEN 'null()'
1529 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
1530 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
1535 WHEN attr_def.price IS NULL THEN 'null()'
1536 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
1537 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
1542 WHEN attr_def.barcode IS NULL THEN 'null()'
1543 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
1544 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
1549 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
1550 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
1551 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
1556 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
1557 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
1558 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
1563 WHEN attr_def.alert_message IS NULL THEN 'null()'
1564 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
1565 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
1570 WHEN attr_def.opac_visible IS NULL THEN 'null()'
1571 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
1572 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
1577 WHEN attr_def.pub_note IS NULL THEN 'null()'
1578 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
1579 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
1583 WHEN attr_def.priv_note IS NULL THEN 'null()'
1584 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
1585 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
1590 WHEN attr_def.internal_id IS NULL THEN 'null()'
1591 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
1592 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
1598 owning_lib || '|' ||
1600 call_number || '|' ||
1601 copy_number || '|' ||
1606 deposit_amount || '|' ||
1611 circ_modifier || '|' ||
1612 circ_as_type || '|' ||
1613 alert_message || '|' ||
1616 internal_id || '|' ||
1621 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
1622 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
1623 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
1624 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
1627 attr_set.import_error := NULL;
1628 attr_set.error_detail := NULL;
1629 attr_set.deposit_amount := NULL;
1630 attr_set.copy_number := NULL;
1631 attr_set.price := NULL;
1633 IF tmp_attr_set.pr != '' THEN
1634 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
1635 IF tmp_str = '' THEN
1636 attr_set.import_error := 'import.item.invalid.price';
1637 attr_set.error_detail := tmp_attr_set.pr; -- original value
1638 RETURN NEXT attr_set; CONTINUE;
1640 attr_set.price := tmp_str::NUMERIC(8,2);
1643 IF tmp_attr_set.dep_amount != '' THEN
1644 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
1645 IF tmp_str = '' THEN
1646 attr_set.import_error := 'import.item.invalid.deposit_amount';
1647 attr_set.error_detail := tmp_attr_set.dep_amount;
1648 RETURN NEXT attr_set; CONTINUE;
1650 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
1653 IF tmp_attr_set.cnum != '' THEN
1654 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
1655 IF tmp_str = '' THEN
1656 attr_set.import_error := 'import.item.invalid.copy_number';
1657 attr_set.error_detail := tmp_attr_set.cnum;
1658 RETURN NEXT attr_set; CONTINUE;
1660 attr_set.copy_number := tmp_str::INT;
1663 IF tmp_attr_set.ol != '' THEN
1664 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
1666 attr_set.import_error := 'import.item.invalid.owning_lib';
1667 attr_set.error_detail := tmp_attr_set.ol;
1668 RETURN NEXT attr_set; CONTINUE;
1672 IF tmp_attr_set.clib != '' THEN
1673 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
1675 attr_set.import_error := 'import.item.invalid.circ_lib';
1676 attr_set.error_detail := tmp_attr_set.clib;
1677 RETURN NEXT attr_set; CONTINUE;
1681 IF tmp_attr_set.cs != '' THEN
1682 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
1684 attr_set.import_error := 'import.item.invalid.status';
1685 attr_set.error_detail := tmp_attr_set.cs;
1686 RETURN NEXT attr_set; CONTINUE;
1690 IF tmp_attr_set.circ_mod != '' THEN
1691 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
1693 attr_set.import_error := 'import.item.invalid.circ_modifier';
1694 attr_set.error_detail := tmp_attr_set.circ_mod;
1695 RETURN NEXT attr_set; CONTINUE;
1699 IF tmp_attr_set.circ_as != '' THEN
1700 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
1702 attr_set.import_error := 'import.item.invalid.circ_as_type';
1703 attr_set.error_detail := tmp_attr_set.circ_as;
1704 RETURN NEXT attr_set; CONTINUE;
1708 IF tmp_attr_set.cl != '' THEN
1710 -- search up the org unit tree for a matching copy location
1711 WITH RECURSIVE anscestor_depth AS (
1715 FROM actor.org_unit ou
1716 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1717 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
1722 FROM actor.org_unit ou
1723 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1724 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
1725 ) SELECT cpl.id INTO attr_set.location
1726 FROM anscestor_depth a
1727 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
1728 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
1729 ORDER BY a.depth DESC
1733 attr_set.import_error := 'import.item.invalid.location';
1734 attr_set.error_detail := tmp_attr_set.cs;
1735 RETURN NEXT attr_set; CONTINUE;
1739 attr_set.circulate :=
1740 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
1741 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
1744 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
1745 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
1747 attr_set.holdable :=
1748 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
1749 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
1751 attr_set.opac_visible :=
1752 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
1753 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
1756 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
1757 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
1759 attr_set.call_number := tmp_attr_set.cn; -- TEXT
1760 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1761 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1762 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1763 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1764 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1765 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
1767 RETURN NEXT attr_set;
1776 $$ LANGUAGE PLPGSQL;
1780 -- 0732.schema.acq-lineitem-summary.sql
1782 SELECT evergreen.upgrade_deps_block_check('0732', :eg_version);
1784 CREATE OR REPLACE VIEW acq.lineitem_summary AS
1788 SELECT COUNT(lid.id)
1789 FROM acq.lineitem_detail lid
1790 WHERE lineitem = li.id
1793 SELECT COUNT(lid.id)
1794 FROM acq.lineitem_detail lid
1795 WHERE recv_time IS NOT NULL AND lineitem = li.id
1798 SELECT COUNT(lid.id)
1799 FROM acq.lineitem_detail lid
1800 WHERE cancel_reason IS NOT NULL AND lineitem = li.id
1803 SELECT COUNT(lid.id)
1804 FROM acq.lineitem_detail lid
1805 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1806 WHERE NOT debit.encumbrance AND lineitem = li.id
1809 SELECT COUNT(DISTINCT(lid.id))
1810 FROM acq.lineitem_detail lid
1811 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
1812 WHERE lineitem = li.id
1815 SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
1816 FROM acq.lineitem_detail lid
1817 WHERE lid.cancel_reason IS NULL AND lineitem = li.id
1818 ) AS estimated_amount,
1820 SELECT SUM(debit.amount)::NUMERIC(8,2)
1821 FROM acq.lineitem_detail lid
1822 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1823 WHERE debit.encumbrance AND lineitem = li.id
1824 ) AS encumbrance_amount,
1826 SELECT SUM(debit.amount)::NUMERIC(8,2)
1827 FROM acq.lineitem_detail lid
1828 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1829 WHERE NOT debit.encumbrance AND lineitem = li.id
1832 FROM acq.lineitem AS li;
1837 -- Template update included here for reference only.
1838 -- The stock JEDI template is not updated here (see WHERE clause)
1839 -- We do update the environment, though, for easier local template
1840 -- updating. No env fields are removed (that aren't otherwise replaced).
1844 SELECT evergreen.upgrade_deps_block_check('0733', :eg_version);
1846 UPDATE action_trigger.event_definition SET template =
1848 [%# start JEDI document
1849 # Vendor specific kludges:
1850 # BT - vendcode goes to NAD/BY *suffix* w/ 91 qualifier
1851 # INGRAM - vendcode goes to NAD/BY *segment* w/ 91 qualifier (separately)
1852 # BRODART - vendcode goes to FTX segment (lineitem level)
1855 IF target.provider.edi_default.vendcode && target.provider.code == 'BRODART';
1856 xtra_ftx = target.provider.edi_default.vendcode;
1859 [%- BLOCK big_block -%]
1861 "recipient":"[% target.provider.san %]",
1862 "sender":"[% target.ordering_agency.mailing_address.san %]",
1864 "ORDERS":[ "order", {
1865 "po_number":[% target.id %],
1866 "date":"[% date.format(date.now, '%Y%m%d') %]",
1868 [% IF target.provider.edi_default.vendcode && (target.provider.code == 'BT' || target.provider.name.match('(?i)^BAKER & TAYLOR')) -%]
1869 {"id-qualifier": 91, "id":"[% target.ordering_agency.mailing_address.san _ ' ' _ target.provider.edi_default.vendcode %]"}
1870 [%- ELSIF target.provider.edi_default.vendcode && target.provider.code == 'INGRAM' -%]
1871 {"id":"[% target.ordering_agency.mailing_address.san %]"},
1872 {"id-qualifier": 91, "id":"[% target.provider.edi_default.vendcode %]"}
1874 {"id":"[% target.ordering_agency.mailing_address.san %]"}
1878 [%- # target.provider.name (target.provider.id) -%]
1879 "[% target.provider.san %]",
1880 {"id-qualifier": 92, "id":"[% target.provider.id %]"}
1882 "currency":"[% target.provider.currency_type %]",
1885 [%- FOR li IN target.lineitems %]
1887 "line_index":"[% li.id %]",
1888 "identifiers":[ [%-# li.isbns = helpers.get_li_isbns(li.attributes) %]
1889 [% FOR isbn IN helpers.get_li_isbns(li.attributes) -%]
1890 [% IF isbn.length == 13 -%]
1891 {"id-qualifier":"EN","id":"[% isbn %]"},
1893 {"id-qualifier":"IB","id":"[% isbn %]"},
1896 {"id-qualifier":"IN","id":"[% li.id %]"}
1898 "price":[% li.estimated_unit_price || '0.00' %],
1900 {"BTI":"[% helpers.get_li_attr_jedi('title', '', li.attributes) %]"},
1901 {"BPU":"[% helpers.get_li_attr_jedi('publisher', '', li.attributes) %]"},
1902 {"BPD":"[% helpers.get_li_attr_jedi('pubdate', '', li.attributes) %]"},
1903 {"BPH":"[% helpers.get_li_attr_jedi('pagination','', li.attributes) %]"}
1906 FOR note IN li.lineitem_notes;
1907 NEXT UNLESS note.vendor_public == 't';
1908 ftx_vals.push(note.value);
1910 IF xtra_ftx; ftx_vals.unshift(xtra_ftx); END;
1911 IF ftx_vals.size == 0; ftx_vals.unshift(''); END; # BT needs FTX+LIN for every LI, even if it is an empty one
1915 [% FOR note IN ftx_vals -%] "[% note %]"[% UNLESS loop.last %], [% END %][% END %]
1917 "quantity":[% li.lineitem_details.size %],
1920 [%- FOR lid IN li.lineitem_details;
1921 fund = lid.fund.code;
1922 item_type = lid.circ_modifier;
1923 callnumber = lid.cn_label;
1924 owning_lib = lid.owning_lib.shortname;
1925 location = lid.location;
1927 # when we have real copy data, treat it as authoritative
1928 acp = lid.eg_copy_id;
1930 item_type = acp.circ_modifier;
1931 callnumber = acp.call_number.label;
1932 location = acp.location.name;
1934 { [%- IF fund %] "fund" : "[% fund %]",[% END -%]
1935 [%- IF callnumber %] "call_number" : "[% callnumber %]", [% END -%]
1936 [%- IF item_type %] "item_type" : "[% item_type %]", [% END -%]
1937 [%- IF location %] "copy_location" : "[% location %]", [% END -%]
1938 [%- IF owning_lib %] "owning_lib" : "[% owning_lib %]", [% END -%]
1939 [%- #chomp %]"copy_id" : "[% lid.id %]" }[% ',' UNLESS loop.last %]
1943 }[% UNLESS loop.last %],[% END %]
1944 [%-# TODO: lineitem details (later) -%]
1947 "line_items":[% target.lineitems.size %]
1948 }] [%# close ORDERS array %]
1949 }] [%# close body array %]
1952 [% tempo = PROCESS big_block; helpers.escape_json(tempo) %]
1954 WHERE id = 23 AND FALSE; -- DON'T PERFORM THE UPDATE
1957 -- add copy-related fields to the environment if they're not already there.
1961 FROM action_trigger.environment
1964 path = 'lineitems.lineitem_details.owning_lib';
1966 INSERT INTO action_trigger.environment (event_def, path)
1967 VALUES (23, 'lineitems.lineitem_details.owning_lib');
1971 FROM action_trigger.environment
1974 path = 'lineitems.lineitem_details.fund';
1976 INSERT INTO action_trigger.environment (event_def, path)
1977 VALUES (23, 'lineitems.lineitem_details.fund');
1981 FROM action_trigger.environment
1984 path = 'lineitems.lineitem_details.location';
1986 INSERT INTO action_trigger.environment (event_def, path)
1987 VALUES (23, 'lineitems.lineitem_details.location');
1991 FROM action_trigger.environment
1994 path = 'lineitems.lineitem_details.eg_copy_id.location';
1996 INSERT INTO action_trigger.environment (event_def, path)
1997 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.location');
2001 FROM action_trigger.environment
2004 path = 'lineitems.lineitem_details.eg_copy_id.call_number';
2006 INSERT INTO action_trigger.environment (event_def, path)
2007 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.call_number');
2012 -- remove redundant entry
2013 DELETE FROM action_trigger.environment
2014 WHERE event_def = 23 AND path = 'lineitems.lineitem_details';
2019 -- Evergreen DB patch 0734.tpac_holdable_check.sql
2022 -- check whether patch can be applied
2023 SELECT evergreen.upgrade_deps_block_check('0734', :eg_version);
2025 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2030 JOIN asset.call_number acn ON acp.call_number = acn.id
2031 JOIN asset.copy_location acpl ON acp.location = acpl.id
2032 JOIN config.copy_status ccs ON acp.status = ccs.id
2035 AND acp.holdable = true
2036 AND acpl.holdable = true
2037 AND ccs.holdable = true
2038 AND acp.deleted = false
2045 $f$ LANGUAGE PLPGSQL;
2047 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2052 JOIN asset.call_number acn ON acp.call_number = acn.id
2053 JOIN asset.copy_location acpl ON acp.location = acpl.id
2054 JOIN config.copy_status ccs ON acp.status = ccs.id
2055 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
2057 mmsm.metarecord = rid
2058 AND acp.holdable = true
2059 AND acpl.holdable = true
2060 AND ccs.holdable = true
2061 AND acp.deleted = false
2068 $f$ LANGUAGE PLPGSQL;
2070 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
2074 depth INT DEFAULT NULL,
2075 includes TEXT[] DEFAULT NULL::TEXT[],
2076 slimit HSTORE DEFAULT NULL,
2077 soffset HSTORE DEFAULT NULL,
2078 include_xmlns BOOL DEFAULT TRUE,
2079 pref_lib INT DEFAULT NULL
2085 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2086 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
2087 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
2091 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
2094 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2096 FROM asset.opac_ou_record_copy_count($2, $1)
2100 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2102 FROM asset.staff_ou_record_copy_count($2, $1)
2106 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2108 FROM asset.opac_ou_record_copy_count($9, $1)
2113 WHEN ('bmp' = ANY ($5)) THEN
2115 name monograph_parts,
2116 (SELECT XMLAGG(bmp) FROM (
2117 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)
2118 FROM biblio.monograph_part
2126 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
2128 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
2129 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
2132 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
2133 FROM evergreen.located_uris($1, $2, $9) AS uris
2136 CASE WHEN ('ssub' = ANY ($5)) THEN
2139 (SELECT XMLAGG(ssub) FROM (
2140 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
2141 FROM serial.subscription
2142 WHERE record_entry = $1
2146 CASE WHEN ('acp' = ANY ($5)) THEN
2148 name foreign_copies,
2149 (SELECT XMLAGG(acp) FROM (
2150 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
2151 FROM biblio.peer_bib_copy_map p
2152 JOIN asset.copy c ON (p.target_copy = c.id)
2153 WHERE NOT c.deleted AND p.peer_record = $1
2154 LIMIT ($6 -> 'acp')::INT
2155 OFFSET ($7 -> 'acp')::INT
2160 $F$ LANGUAGE SQL STABLE;
2162 -- Evergreen DB patch 0735.data.search_filter_group_perms.sql
2165 -- check whether patch can be applied
2166 SELECT evergreen.upgrade_deps_block_check('0735', :eg_version);
2168 INSERT INTO permission.perm_list ( id, code, description )
2171 'ADMIN_SEARCH_FILTER_GROUP',
2174 'Allows staff to manage search filter groups and entries',
2181 'VIEW_SEARCH_FILTER_GROUP',
2184 'Allows staff to view search filter groups and entries',
2191 -- check whether patch can be applied
2192 SELECT evergreen.upgrade_deps_block_check('0737', :eg_version);
2194 UPDATE action_trigger.event_definition
2198 # target is the bookbag itself. The 'items' variable does not need to be in
2199 # the environment because a special reactor will take care of filling it in.
2202 bibxml = helpers.unapi_bre(item.target_biblio_record_entry, {flesh => '{mra}'});
2204 FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
2205 title = title _ part.textContent;
2207 author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
2208 item_type = bibxml.findnodes('//*[local-name()="attributes"]/*[local-name()="field"][@name="item_type"]').getAttribute('coded-value');
2210 FOR pdatum IN bibxml.findnodes('//*[@tag="260"]/*[@code="c"]');
2212 pub_date = pub_date _ ", " _ pdatum.textContent;
2214 pub_date = pdatum.textContent;
2217 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";
2220 WHERE name = 'Bookbag CSV';