1 --Upgrade Script for 2.2 to 2.3.0
3 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.0', :eg_version);
4 -- Evergreen DB patch 0703.tpac_value_maps.sql
6 -- check whether patch can be applied
7 SELECT evergreen.upgrade_deps_block_check('0703', :eg_version);
9 ALTER TABLE config.coded_value_map
10 ADD COLUMN opac_visible BOOL NOT NULL DEFAULT TRUE,
11 ADD COLUMN search_label TEXT,
12 ADD COLUMN is_simple BOOL NOT NULL DEFAULT FALSE;
16 SELECT evergreen.upgrade_deps_block_check('0712', :eg_version);
18 -- General purpose query container. Any table the needs to store
19 -- a QueryParser query should store it here. This will be the
20 -- source for top-level and QP sub-search inclusion queries.
21 CREATE TABLE actor.search_query (
22 id SERIAL PRIMARY KEY,
23 label TEXT NOT NULL, -- i18n
24 query_text TEXT NOT NULL -- QP text
27 -- e.g. "Reading Level"
28 CREATE TABLE actor.search_filter_group (
29 id SERIAL PRIMARY KEY,
30 owner INT NOT NULL REFERENCES actor.org_unit (id)
31 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
32 code TEXT NOT NULL, -- for CGI, etc.
33 label TEXT NOT NULL, -- i18n
34 create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
35 CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
36 CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
39 -- e.g. "Adult", "Teen", etc.
40 CREATE TABLE actor.search_filter_group_entry (
41 id SERIAL PRIMARY KEY,
42 grp INT NOT NULL REFERENCES actor.search_filter_group(id)
43 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
44 pos INT NOT NULL DEFAULT 0,
45 query INT NOT NULL REFERENCES actor.search_query(id)
46 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
47 CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
51 SELECT evergreen.upgrade_deps_block_check('0713', :eg_version);
53 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
54 'ui.grid_columns.circ.hold_pull_list',
58 'ui.grid_columns.circ.hold_pull_list',
64 'ui.grid_columns.circ.hold_pull_list',
65 'Hold Pull List Saved Column Settings',
74 SELECT evergreen.upgrade_deps_block_check('0714', :eg_version);
76 INSERT into config.org_unit_setting_type
77 (name, grp, label, description, datatype)
79 'opac.patron.auto_overide_hold_events',
82 'opac.patron.auto_overide_hold_events',
83 'Auto-Override Permitted Hold Blocks (Patrons)',
88 'opac.patron.auto_overide_hold_events',
89 'When a patron places a hold that fails and the patron has the correct permission ' ||
90 'to override the hold, automatically override the hold without presenting a message ' ||
91 'to the patron and requiring that the patron make a decision to override',
98 -- Evergreen DB patch 0718.data.add-to-permanent-bookbag.sql
100 -- check whether patch can be applied
101 SELECT evergreen.upgrade_deps_block_check('0718', :eg_version);
103 INSERT into config.org_unit_setting_type
104 (name, grp, label, description, datatype)
106 'opac.patron.temporary_list_warn',
109 'opac.patron.temporary_list_warn',
110 'Warn patrons when adding to a temporary book list',
115 'opac.patron.temporary_list_warn',
116 'Present a warning dialog to the patron when a patron adds a book to a temporary book bag.',
123 INSERT INTO config.usr_setting_type
124 (name,grp,opac_visible,label,description,datatype)
126 'opac.temporary_list_no_warn',
130 'opac.temporary_list_no_warn',
131 'Opt out of warning when adding a book to a temporary book list',
136 'opac.temporary_list_no_warn',
137 'Opt out of warning when adding a book to a temporary book list',
144 INSERT INTO config.usr_setting_type
145 (name,grp,opac_visible,label,description,datatype)
152 'Default list to use when adding to a bookbag',
158 'Default list to use when adding to a bookbag',
166 SELECT evergreen.upgrade_deps_block_check('0719', :eg_version);
168 INSERT INTO config.org_unit_setting_type (
169 name, label, grp, description, datatype
171 'circ.staff.max_visible_event_age',
172 'Maximum visible age of User Trigger Events in Staff Interfaces',
174 '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.',
178 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
179 'ui.grid_columns.actor.user.event_log',
183 'ui.grid_columns.actor.user.event_log',
189 'ui.grid_columns.actor.user.event_log',
190 'User Event Log Saved Column Settings',
197 INSERT INTO permission.perm_list ( id, code, description )
200 'VIEW_TRIGGER_EVENT',
203 'Allows a user to view circ- and hold-related action/trigger events',
210 SELECT evergreen.upgrade_deps_block_check('0720', :eg_version);
212 ALTER TABLE config.circ_matrix_weights
213 ADD COLUMN copy_location NUMERIC(6,2) NOT NULL DEFAULT 5.0;
214 UPDATE config.circ_matrix_weights
215 SET copy_location = 0.0 WHERE name = 'All_Equal';
216 ALTER TABLE config.circ_matrix_weights
217 ALTER COLUMN copy_location DROP DEFAULT; -- for consistency w/ baseline schema
219 ALTER TABLE config.circ_matrix_matchpoint
220 ADD COLUMN copy_location INTEGER REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED;
222 DROP INDEX config.ccmm_once_per_paramset;
224 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;
226 -- Linkage between limit sets and circ mods
227 CREATE TABLE config.circ_limit_set_copy_loc_map (
228 id SERIAL PRIMARY KEY,
229 limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
230 copy_loc INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
231 CONSTRAINT cl_once_per_set UNIQUE (limit_set, copy_loc)
234 -- Add support for checking config.circ_limit_set_copy_loc_map's
235 CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL )
236 RETURNS SETOF action.circ_matrix_test_result AS $func$
238 user_object actor.usr%ROWTYPE;
239 standing_penalty config.standing_penalty%ROWTYPE;
240 item_object asset.copy%ROWTYPE;
241 item_status_object config.copy_status%ROWTYPE;
242 item_location_object asset.copy_location%ROWTYPE;
243 result action.circ_matrix_test_result;
244 circ_test action.found_circ_matrix_matchpoint;
245 circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
246 circ_limit_set config.circ_limit_set%ROWTYPE;
247 hold_ratio action.hold_stats%ROWTYPE;
250 context_org_list INT[];
253 -- Assume success unless we hit a failure condition
254 result.success := TRUE;
256 -- Need user info to look up matchpoints
257 SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
259 -- (Insta)Fail if we couldn't find the user
260 IF user_object.id IS NULL THEN
261 result.fail_part := 'no_user';
262 result.success := FALSE;
268 -- Need item info to look up matchpoints
269 SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
271 -- (Insta)Fail if we couldn't find the item
272 IF item_object.id IS NULL THEN
273 result.fail_part := 'no_item';
274 result.success := FALSE;
280 SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
282 circ_matchpoint := circ_test.matchpoint;
283 result.matchpoint := circ_matchpoint.id;
284 result.circulate := circ_matchpoint.circulate;
285 result.duration_rule := circ_matchpoint.duration_rule;
286 result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
287 result.max_fine_rule := circ_matchpoint.max_fine_rule;
288 result.hard_due_date := circ_matchpoint.hard_due_date;
289 result.renewals := circ_matchpoint.renewals;
290 result.grace_period := circ_matchpoint.grace_period;
291 result.buildrows := circ_test.buildrows;
293 -- (Insta)Fail if we couldn't find a matchpoint
294 IF circ_test.success = false THEN
295 result.fail_part := 'no_matchpoint';
296 result.success := FALSE;
302 -- All failures before this point are non-recoverable
303 -- Below this point are possibly overridable failures
305 -- Fail if the user is barred
306 IF user_object.barred IS TRUE THEN
307 result.fail_part := 'actor.usr.barred';
308 result.success := FALSE;
313 -- Fail if the item can't circulate
314 IF item_object.circulate IS FALSE THEN
315 result.fail_part := 'asset.copy.circulate';
316 result.success := FALSE;
321 -- Fail if the item isn't in a circulateable status on a non-renewal
322 IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
323 result.fail_part := 'asset.copy.status';
324 result.success := FALSE;
327 -- Alternately, fail if the item isn't checked out on a renewal
328 ELSIF renewal AND item_object.status <> 1 THEN
329 result.fail_part := 'asset.copy.status';
330 result.success := FALSE;
335 -- Fail if the item can't circulate because of the shelving location
336 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
337 IF item_location_object.circulate IS FALSE THEN
338 result.fail_part := 'asset.copy_location.circulate';
339 result.success := FALSE;
344 -- Use Circ OU for penalties and such
345 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
348 penalty_type = '%RENEW%';
350 penalty_type = '%CIRC%';
353 FOR standing_penalty IN
354 SELECT DISTINCT csp.*
355 FROM actor.usr_standing_penalty usp
356 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
357 WHERE usr = match_user
358 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
359 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
360 AND csp.block_list LIKE penalty_type LOOP
362 result.fail_part := standing_penalty.name;
363 result.success := FALSE;
368 -- Fail if the test is set to hard non-circulating
369 IF circ_matchpoint.circulate IS FALSE THEN
370 result.fail_part := 'config.circ_matrix_test.circulate';
371 result.success := FALSE;
376 -- Fail if the total copy-hold ratio is too low
377 IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
378 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
379 IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
380 result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
381 result.success := FALSE;
387 -- Fail if the available copy-hold ratio is too low
388 IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
389 IF hold_ratio.hold_count IS NULL THEN
390 SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
392 IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
393 result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
394 result.success := FALSE;
400 -- Fail if the user has too many items out by defined limit sets
401 FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
402 JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
403 WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
404 ( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
406 IF circ_limit_set.items_out > 0 AND NOT renewal THEN
407 SELECT INTO context_org_list ARRAY_AGG(aou.id)
408 FROM actor.org_unit_full_path( circ_ou ) aou
409 JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
410 WHERE aout.depth >= circ_limit_set.depth;
411 IF circ_limit_set.global THEN
412 WITH RECURSIVE descendant_depth AS (
415 FROM actor.org_unit ou
416 WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
420 FROM actor.org_unit ou
421 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
422 ) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
424 SELECT INTO items_out COUNT(DISTINCT circ.id)
425 FROM action.circulation circ
426 JOIN asset.copy copy ON (copy.id = circ.target_copy)
427 LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
428 WHERE circ.usr = match_user
429 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
430 AND circ.checkin_time IS NULL
431 AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
432 AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
433 OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
434 OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
436 IF items_out >= circ_limit_set.items_out THEN
437 result.fail_part := 'config.circ_matrix_circ_mod_test';
438 result.success := FALSE;
443 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;
446 -- If we passed everything, return the successful matchpoint
453 $func$ LANGUAGE plpgsql;
456 -- adding copy_loc to circ_matrix_matchpoint
457 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$
459 cn_object asset.call_number%ROWTYPE;
460 rec_descriptor metabib.rec_descriptor%ROWTYPE;
461 cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
462 matchpoint config.circ_matrix_matchpoint%ROWTYPE;
463 weights config.circ_matrix_weights%ROWTYPE;
465 my_item_age INTERVAL;
466 denominator NUMERIC(6,2);
468 result action.found_circ_matrix_matchpoint;
471 result.success = false;
474 SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
475 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
477 -- Pre-generate this so we only calc it once
478 IF user_object.dob IS NOT NULL THEN
479 SELECT INTO user_age age(user_object.dob);
483 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
485 -- Grab the closest set circ weight setting.
486 SELECT INTO weights cw.*
487 FROM config.weight_assoc wa
488 JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
489 JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
494 -- No weights? Bad admin! Defaults to handle that anyway.
495 IF weights.id IS NULL THEN
497 weights.org_unit := 10.0;
498 weights.circ_modifier := 5.0;
499 weights.copy_location := 5.0;
500 weights.marc_type := 4.0;
501 weights.marc_form := 3.0;
502 weights.marc_bib_level := 2.0;
503 weights.marc_vr_format := 2.0;
504 weights.copy_circ_lib := 8.0;
505 weights.copy_owning_lib := 8.0;
506 weights.user_home_ou := 8.0;
507 weights.ref_flag := 1.0;
508 weights.juvenile_flag := 6.0;
509 weights.is_renewal := 7.0;
510 weights.usr_age_lower_bound := 0.0;
511 weights.usr_age_upper_bound := 0.0;
512 weights.item_age := 0.0;
515 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
516 -- If you break your org tree with funky parenting this may be wrong
517 -- 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
518 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
519 WITH all_distance(distance) AS (
520 SELECT depth AS distance FROM actor.org_unit_type
522 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
524 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
526 -- Loop over all the potential matchpoints
527 FOR cur_matchpoint IN
529 FROM config.circ_matrix_matchpoint m
530 /*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
531 /*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
532 LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
533 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
534 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
537 -- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
539 -- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
540 AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
541 AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
542 AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
544 AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
545 -- Static User Checks
546 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
547 AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
548 AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
549 -- Static Item Checks
550 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
551 AND (m.copy_location IS NULL OR m.copy_location = item_object.location)
552 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
553 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
554 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
555 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
556 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
557 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
560 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
562 CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
563 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
564 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
565 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
566 -- Circ Type -- Note: 4^x is equiv to 2^(2*x)
567 CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
568 -- Static User Checks
569 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
570 CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
571 CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
572 -- Static Item Checks
573 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
574 CASE WHEN m.copy_location IS NOT NULL THEN 4^weights.copy_location ELSE 0.0 END +
575 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
576 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
577 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
578 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
579 -- Item age has a slight adjustment to weight based on value.
580 -- This should ensure that a shorter age limit comes first when all else is equal.
581 -- NOTE: This assumes that intervals will normally be in days.
582 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,
583 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
584 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
587 -- Record the full matching row list
588 row_list := row_list || cur_matchpoint.id;
590 -- No matchpoint yet?
591 IF matchpoint.id IS NULL THEN
592 -- Take the entire matchpoint as a starting point
593 matchpoint := cur_matchpoint;
594 CONTINUE; -- No need to look at this row any more.
597 -- Incomplete matchpoint?
598 IF matchpoint.circulate IS NULL THEN
599 matchpoint.circulate := cur_matchpoint.circulate;
601 IF matchpoint.duration_rule IS NULL THEN
602 matchpoint.duration_rule := cur_matchpoint.duration_rule;
604 IF matchpoint.recurring_fine_rule IS NULL THEN
605 matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
607 IF matchpoint.max_fine_rule IS NULL THEN
608 matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
610 IF matchpoint.hard_due_date IS NULL THEN
611 matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
613 IF matchpoint.total_copy_hold_ratio IS NULL THEN
614 matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
616 IF matchpoint.available_copy_hold_ratio IS NULL THEN
617 matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
619 IF matchpoint.renewals IS NULL THEN
620 matchpoint.renewals := cur_matchpoint.renewals;
622 IF matchpoint.grace_period IS NULL THEN
623 matchpoint.grace_period := cur_matchpoint.grace_period;
627 -- Check required fields
628 IF matchpoint.circulate IS NOT NULL AND
629 matchpoint.duration_rule IS NOT NULL AND
630 matchpoint.recurring_fine_rule IS NOT NULL AND
631 matchpoint.max_fine_rule IS NOT NULL THEN
632 -- All there? We have a completed match.
633 result.success := true;
636 -- Include the assembled matchpoint, even if it isn't complete
637 result.matchpoint := matchpoint;
639 -- Include (for debugging) the full list of matching rows
640 result.buildrows := row_list;
642 -- Hand the result back to caller
645 $func$ LANGUAGE plpgsql;
650 SELECT evergreen.upgrade_deps_block_check('0721', :eg_version);
652 UPDATE config.standing_penalty
653 SET block_list = REPLACE(block_list, 'HOLD', 'HOLD|CAPTURE')
655 -- STAFF_ penalties have names that match their block list
656 name NOT LIKE 'STAFF_%'
657 -- belt & suspenders, also good for testing
658 AND block_list NOT LIKE '%CAPTURE%';
660 -- CIRC|FULFILL is now the same as CIRC previously was by itself
661 UPDATE config.standing_penalty
662 SET block_list = REPLACE(block_list, 'CIRC', 'CIRC|FULFILL')
664 -- STAFF_ penalties have names that match their block list
665 name NOT LIKE 'STAFF_%'
666 -- belt & suspenders, also good for testing
667 AND block_list NOT LIKE '%FULFILL%';
670 -- apply the HOLD vs CAPTURE block logic
671 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$
674 user_object actor.usr%ROWTYPE;
675 age_protect_object config.rule_age_hold_protect%ROWTYPE;
676 standing_penalty config.standing_penalty%ROWTYPE;
677 transit_range_ou_type actor.org_unit_type%ROWTYPE;
678 transit_source actor.org_unit%ROWTYPE;
679 item_object asset.copy%ROWTYPE;
680 item_cn_object asset.call_number%ROWTYPE;
681 item_status_object config.copy_status%ROWTYPE;
682 item_location_object asset.copy_location%ROWTYPE;
683 ou_skip actor.org_unit_setting%ROWTYPE;
684 result action.matrix_test_result;
685 hold_test config.hold_matrix_matchpoint%ROWTYPE;
686 use_active_date TEXT;
687 age_protect_date TIMESTAMP WITH TIME ZONE;
689 hold_transit_prox INT;
690 frozen_hold_count INT;
691 context_org_list INT[];
695 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
696 SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
698 result.success := TRUE;
700 -- The HOLD penalty block only applies to new holds.
701 -- The CAPTURE penalty block applies to existing holds.
702 hold_penalty := 'HOLD';
704 hold_penalty := 'CAPTURE';
707 -- Fail if we couldn't find a user
708 IF user_object.id IS NULL THEN
709 result.fail_part := 'no_user';
710 result.success := FALSE;
716 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
718 -- Fail if we couldn't find a copy
719 IF item_object.id IS NULL THEN
720 result.fail_part := 'no_item';
721 result.success := FALSE;
727 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
728 result.matchpoint := matchpoint_id;
730 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
732 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
733 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
734 result.fail_part := 'circ.holds.target_skip_me';
735 result.success := FALSE;
741 -- Fail if user is barred
742 IF user_object.barred IS TRUE THEN
743 result.fail_part := 'actor.usr.barred';
744 result.success := FALSE;
750 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
751 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
752 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
754 -- Fail if we couldn't find any matchpoint (requires a default)
755 IF matchpoint_id IS NULL THEN
756 result.fail_part := 'no_matchpoint';
757 result.success := FALSE;
763 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
765 IF hold_test.holdable IS FALSE THEN
766 result.fail_part := 'config.hold_matrix_test.holdable';
767 result.success := FALSE;
772 IF item_object.holdable IS FALSE THEN
773 result.fail_part := 'item.holdable';
774 result.success := FALSE;
779 IF item_status_object.holdable IS FALSE THEN
780 result.fail_part := 'status.holdable';
781 result.success := FALSE;
786 IF item_location_object.holdable IS FALSE THEN
787 result.fail_part := 'location.holdable';
788 result.success := FALSE;
793 IF hold_test.transit_range IS NOT NULL THEN
794 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
795 IF hold_test.distance_is_from_owner THEN
796 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;
798 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
801 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
804 result.fail_part := 'transit_range';
805 result.success := FALSE;
811 FOR standing_penalty IN
812 SELECT DISTINCT csp.*
813 FROM actor.usr_standing_penalty usp
814 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
815 WHERE usr = match_user
816 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
817 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
818 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
820 result.fail_part := standing_penalty.name;
821 result.success := FALSE;
826 IF hold_test.stop_blocked_user IS TRUE THEN
827 FOR standing_penalty IN
828 SELECT DISTINCT csp.*
829 FROM actor.usr_standing_penalty usp
830 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
831 WHERE usr = match_user
832 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
833 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
834 AND csp.block_list LIKE '%CIRC%' LOOP
836 result.fail_part := standing_penalty.name;
837 result.success := FALSE;
843 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
844 SELECT INTO hold_count COUNT(*)
845 FROM action.hold_request
846 WHERE usr = match_user
847 AND fulfillment_time IS NULL
848 AND cancel_time IS NULL
849 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
851 IF hold_count >= hold_test.max_holds THEN
852 result.fail_part := 'config.hold_matrix_test.max_holds';
853 result.success := FALSE;
859 IF item_object.age_protect IS NOT NULL THEN
860 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
861 IF hold_test.distance_is_from_owner THEN
862 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
864 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
866 IF use_active_date = 'true' THEN
867 age_protect_date := COALESCE(item_object.active_date, NOW());
869 age_protect_date := item_object.create_date;
871 IF age_protect_date + age_protect_object.age > NOW() THEN
872 IF hold_test.distance_is_from_owner THEN
873 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
874 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
876 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
879 IF hold_transit_prox > age_protect_object.prox THEN
880 result.fail_part := 'config.rule_age_hold_protect.prox';
881 result.success := FALSE;
894 $func$ LANGUAGE plpgsql;
897 -- Evergreen DB patch 0727.function.xml_pretty_print.sql
899 -- A simple pretty printer for XML.
900 -- Particularly useful for debugging the biblio.record_entry.marc field.
903 -- check whether patch can be applied
904 SELECT evergreen.upgrade_deps_block_check('0727', :eg_version);
906 CREATE OR REPLACE FUNCTION evergreen.xml_pretty_print(input XML)
910 SELECT xslt_process($1::text,
911 $$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
913 <xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
914 <xsl:strip-space elements="*"/>
915 <xsl:template match="@*|node()">
917 <xsl:apply-templates select="@*|node()"/>
924 COMMENT ON FUNCTION evergreen.xml_pretty_print(input XML) IS
925 'Simple pretty printer for XML, as written by Andrew Dunstan at http://goo.gl/zBHIk';
928 SELECT evergreen.upgrade_deps_block_check('0728', :eg_version);
930 INSERT INTO actor.search_filter_group (owner, code, label)
931 VALUES (1, 'kpac_main', 'Kid''s OPAC Search Filter');
933 INSERT INTO actor.search_query (label, query_text)
934 VALUES ('Children''s Materials', 'audience(a,b,c)');
935 INSERT INTO actor.search_query (label, query_text)
936 VALUES ('Young Adult Materials', 'audience(j,d)');
937 INSERT INTO actor.search_query (label, query_text)
938 VALUES ('General/Adult Materials', 'audience(e,f,g, )');
940 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
942 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
943 (SELECT id FROM actor.search_query WHERE label = 'Children''s Materials'),
946 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
948 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
949 (SELECT id FROM actor.search_query WHERE label = 'Young Adult Materials'),
952 INSERT INTO actor.search_filter_group_entry (grp, query, pos)
954 (SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
955 (SELECT id FROM actor.search_query WHERE label = 'General/Adult Materials'),
960 -- Evergreen DB patch 0729.vr_format_value_maps.sql
963 -- check whether patch can be applied
964 SELECT evergreen.upgrade_deps_block_check('0729', :eg_version);
966 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$
968 current_row config.coded_value_map%ROWTYPE;
970 -- Look for a current value
971 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
973 IF FOUND AND NOT add_only THEN
974 -- Update anything we were handed
975 current_row.value := COALESCE(current_row.value, in_value);
976 current_row.description := COALESCE(current_row.description, in_description);
977 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
978 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
979 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
980 UPDATE config.coded_value_map
982 value = current_row.value,
983 description = current_row.description,
984 opac_visible = current_row.opac_visible,
985 search_label = current_row.search_label,
986 is_simple = current_row.is_simple
987 WHERE id = current_row.id;
989 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
990 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
993 $f$ LANGUAGE PLPGSQL;
995 SELECT config.update_coded_value_map('vr_format', 'a', 'Beta', add_only := TRUE);
996 SELECT config.update_coded_value_map('vr_format', 'b', 'VHS', add_only := TRUE);
997 SELECT config.update_coded_value_map('vr_format', 'c', 'U-matic', add_only := TRUE);
998 SELECT config.update_coded_value_map('vr_format', 'd', 'EIAJ', add_only := TRUE);
999 SELECT config.update_coded_value_map('vr_format', 'e', 'Type C', add_only := TRUE);
1000 SELECT config.update_coded_value_map('vr_format', 'f', 'Quadruplex', add_only := TRUE);
1001 SELECT config.update_coded_value_map('vr_format', 'g', 'Laserdisc', add_only := TRUE);
1002 SELECT config.update_coded_value_map('vr_format', 'h', 'CED videodisc', add_only := TRUE);
1003 SELECT config.update_coded_value_map('vr_format', 'i', 'Betacam', add_only := TRUE);
1004 SELECT config.update_coded_value_map('vr_format', 'j', 'Betacam SP', add_only := TRUE);
1005 SELECT config.update_coded_value_map('vr_format', 'k', 'Super-VHS', add_only := TRUE);
1006 SELECT config.update_coded_value_map('vr_format', 'm', 'M-II', add_only := TRUE);
1007 SELECT config.update_coded_value_map('vr_format', 'o', 'D-2', add_only := TRUE);
1008 SELECT config.update_coded_value_map('vr_format', 'p', '8 mm.', add_only := TRUE);
1009 SELECT config.update_coded_value_map('vr_format', 'q', 'Hi-8 mm.', add_only := TRUE);
1010 SELECT config.update_coded_value_map('vr_format', 's', 'Blu-ray disc', add_only := TRUE);
1011 SELECT config.update_coded_value_map('vr_format', 'u', 'Unknown', add_only := TRUE);
1012 SELECT config.update_coded_value_map('vr_format', 'v', 'DVD', add_only := TRUE);
1013 SELECT config.update_coded_value_map('vr_format', 'z', 'Other', add_only := TRUE);
1014 SELECT config.update_coded_value_map('vr_format', ' ', 'Unspecified', add_only := TRUE);
1018 SELECT evergreen.upgrade_deps_block_check('0730', :eg_version);
1020 DROP FUNCTION acq.propagate_funds_by_org_tree (INT, INT, INT);
1021 DROP FUNCTION acq.propagate_funds_by_org_unit (INT, INT, INT);
1023 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
1026 org_unit_id INTEGER,
1027 include_desc BOOL DEFAULT TRUE
1028 ) RETURNS VOID AS $$
1039 IF old_year IS NULL THEN
1040 RAISE EXCEPTION 'Input year argument is NULL';
1041 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1042 RAISE EXCEPTION 'Input year is out of range';
1045 IF user_id IS NULL THEN
1046 RAISE EXCEPTION 'Input user id argument is NULL';
1049 IF org_unit_id IS NULL THEN
1050 RAISE EXCEPTION 'Org unit id argument is NULL';
1052 SELECT TRUE INTO org_found
1054 WHERE id = org_unit_id;
1056 IF org_found IS NULL THEN
1057 RAISE EXCEPTION 'Org unit id is invalid';
1061 -- Loop over the applicable funds
1063 FOR old_fund in SELECT * FROM acq.fund
1067 AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1068 OR (NOT include_desc AND org = org_unit_id ) )
1072 INSERT INTO acq.fund (
1080 balance_warning_percent,
1081 balance_stop_percent
1086 old_fund.currency_type,
1090 old_fund.balance_warning_percent,
1091 old_fund.balance_stop_percent
1093 RETURNING id INTO new_id;
1095 WHEN unique_violation THEN
1096 --RAISE NOTICE 'Fund % already propagated', old_fund.id;
1099 --RAISE NOTICE 'Propagating fund % to fund %',
1100 -- old_fund.code, new_id;
1103 $$ LANGUAGE plpgsql;
1105 CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$
1106 SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE );
1110 DROP FUNCTION acq.rollover_funds_by_org_tree (INT, INT, INT);
1111 DROP FUNCTION acq.rollover_funds_by_org_unit (INT, INT, INT);
1114 CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
1117 org_unit_id INTEGER,
1118 encumb_only BOOL DEFAULT FALSE,
1119 include_desc BOOL DEFAULT TRUE
1120 ) RETURNS VOID AS $$
1124 new_year INT := old_year + 1;
1127 xfer_amount NUMERIC := 0;
1136 IF old_year IS NULL THEN
1137 RAISE EXCEPTION 'Input year argument is NULL';
1138 ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
1139 RAISE EXCEPTION 'Input year is out of range';
1142 IF user_id IS NULL THEN
1143 RAISE EXCEPTION 'Input user id argument is NULL';
1146 IF org_unit_id IS NULL THEN
1147 RAISE EXCEPTION 'Org unit id argument is NULL';
1150 -- Validate the org unit
1155 WHERE id = org_unit_id;
1157 IF org_found IS NULL THEN
1158 RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
1159 ELSIF encumb_only THEN
1160 SELECT INTO perm_ous value::BOOL FROM
1161 actor.org_unit_ancestor_setting(
1162 'acq.fund.allow_rollover_without_money', org_unit_id
1164 IF NOT FOUND OR NOT perm_ous THEN
1165 RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
1170 -- Loop over the propagable funds to identify the details
1171 -- from the old fund plus the id of the new one, if it exists.
1175 oldf.id AS old_fund,
1181 newf.id AS new_fund_id
1184 LEFT JOIN acq.fund AS newf
1185 ON ( oldf.code = newf.code )
1187 oldf.year = old_year
1189 AND newf.year = new_year
1190 AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
1191 OR (NOT include_desc AND oldf.org = org_unit_id ) )
1193 --RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
1195 IF roll_fund.new_fund_id IS NULL THEN
1197 -- The old fund hasn't been propagated yet. Propagate it now.
1199 INSERT INTO acq.fund (
1207 balance_warning_percent,
1208 balance_stop_percent
1213 roll_fund.currency_type,
1217 roll_fund.balance_warning_percent,
1218 roll_fund.balance_stop_percent
1220 RETURNING id INTO new_fund;
1222 new_fund = roll_fund.new_fund_id;
1225 -- Determine the amount to transfer
1229 FROM acq.fund_spent_balance
1230 WHERE fund = roll_fund.old_fund;
1232 IF xfer_amount <> 0 THEN
1233 IF NOT encumb_only AND roll_fund.rollover THEN
1235 -- Transfer balance from old fund to new
1237 --RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
1239 PERFORM acq.transfer_fund(
1249 -- Transfer balance from old fund to the void
1251 -- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
1253 PERFORM acq.transfer_fund(
1259 'Rollover into the void'
1264 IF roll_fund.rollover THEN
1266 -- Move any lineitems from the old fund to the new one
1267 -- where the associated debit is an encumbrance.
1269 -- Any other tables tying expenditure details to funds should
1270 -- receive similar treatment. At this writing there are none.
1272 UPDATE acq.lineitem_detail
1275 fund = roll_fund.old_fund -- this condition may be redundant
1281 fund = roll_fund.old_fund
1285 -- Move encumbrance debits from the old fund to the new fund
1287 UPDATE acq.fund_debit
1290 fund = roll_fund.old_fund
1294 -- Mark old fund as inactive, now that we've closed it
1298 WHERE id = roll_fund.old_fund;
1301 $$ LANGUAGE plpgsql;
1303 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 $$
1304 SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE );
1307 INSERT into config.org_unit_setting_type
1308 (name, grp, label, description, datatype)
1310 'acq.fund.allow_rollover_without_money',
1313 'acq.fund.allow_rollover_without_money',
1314 'Allow funds to be rolled over without bringing the money along',
1319 'acq.fund.allow_rollover_without_money',
1320 '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.',
1327 -- 0731.schema.vandelay_item_overlay.sql
1329 SELECT evergreen.upgrade_deps_block_check('0731', :eg_version);
1331 ALTER TABLE vandelay.import_item_attr_definition
1332 ADD COLUMN internal_id TEXT;
1334 ALTER TABLE vandelay.import_item
1335 ADD COLUMN internal_id BIGINT;
1337 INSERT INTO permission.perm_list ( id, code, description ) VALUES
1338 ( 536, 'IMPORT_OVERLAY_COPY', oils_i18n_gettext( 536,
1339 'Allows a user to overlay copy data in MARC import', 'ppl', 'description'));
1341 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1344 item_data vandelay.import_item%ROWTYPE;
1347 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
1351 SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1353 FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1354 INSERT INTO vandelay.import_item (
1381 item_data.definition,
1382 item_data.owning_lib,
1384 item_data.call_number,
1385 item_data.copy_number,
1388 item_data.circulate,
1390 item_data.deposit_amount,
1395 item_data.circ_modifier,
1396 item_data.circ_as_type,
1397 item_data.alert_message,
1399 item_data.priv_note,
1400 item_data.internal_id,
1401 item_data.opac_visible,
1402 item_data.import_error,
1403 item_data.error_detail
1409 $func$ LANGUAGE PLPGSQL;
1412 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
1423 deposit_amount TEXT;
1437 tmp_attr_set RECORD;
1438 attr_set vandelay.import_item%ROWTYPE;
1445 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
1449 attr_set.definition := attr_def.id;
1451 -- Build the combined XPath
1455 WHEN attr_def.owning_lib IS NULL THEN 'null()'
1456 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
1457 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
1462 WHEN attr_def.circ_lib IS NULL THEN 'null()'
1463 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
1464 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
1469 WHEN attr_def.call_number IS NULL THEN 'null()'
1470 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
1471 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
1476 WHEN attr_def.copy_number IS NULL THEN 'null()'
1477 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
1478 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
1483 WHEN attr_def.status IS NULL THEN 'null()'
1484 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
1485 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
1490 WHEN attr_def.location IS NULL THEN 'null()'
1491 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
1492 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
1497 WHEN attr_def.circulate IS NULL THEN 'null()'
1498 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
1499 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
1504 WHEN attr_def.deposit IS NULL THEN 'null()'
1505 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
1506 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
1511 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
1512 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
1513 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
1518 WHEN attr_def.ref IS NULL THEN 'null()'
1519 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
1520 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
1525 WHEN attr_def.holdable IS NULL THEN 'null()'
1526 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
1527 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
1532 WHEN attr_def.price IS NULL THEN 'null()'
1533 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
1534 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
1539 WHEN attr_def.barcode IS NULL THEN 'null()'
1540 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
1541 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
1546 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
1547 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
1548 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
1553 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
1554 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
1555 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
1560 WHEN attr_def.alert_message IS NULL THEN 'null()'
1561 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
1562 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
1567 WHEN attr_def.opac_visible IS NULL THEN 'null()'
1568 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
1569 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
1574 WHEN attr_def.pub_note IS NULL THEN 'null()'
1575 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
1576 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
1580 WHEN attr_def.priv_note IS NULL THEN 'null()'
1581 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
1582 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
1587 WHEN attr_def.internal_id IS NULL THEN 'null()'
1588 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
1589 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
1595 owning_lib || '|' ||
1597 call_number || '|' ||
1598 copy_number || '|' ||
1603 deposit_amount || '|' ||
1608 circ_modifier || '|' ||
1609 circ_as_type || '|' ||
1610 alert_message || '|' ||
1613 internal_id || '|' ||
1618 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
1619 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
1620 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
1621 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
1624 attr_set.import_error := NULL;
1625 attr_set.error_detail := NULL;
1626 attr_set.deposit_amount := NULL;
1627 attr_set.copy_number := NULL;
1628 attr_set.price := NULL;
1630 IF tmp_attr_set.pr != '' THEN
1631 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
1632 IF tmp_str = '' THEN
1633 attr_set.import_error := 'import.item.invalid.price';
1634 attr_set.error_detail := tmp_attr_set.pr; -- original value
1635 RETURN NEXT attr_set; CONTINUE;
1637 attr_set.price := tmp_str::NUMERIC(8,2);
1640 IF tmp_attr_set.dep_amount != '' THEN
1641 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
1642 IF tmp_str = '' THEN
1643 attr_set.import_error := 'import.item.invalid.deposit_amount';
1644 attr_set.error_detail := tmp_attr_set.dep_amount;
1645 RETURN NEXT attr_set; CONTINUE;
1647 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
1650 IF tmp_attr_set.cnum != '' THEN
1651 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
1652 IF tmp_str = '' THEN
1653 attr_set.import_error := 'import.item.invalid.copy_number';
1654 attr_set.error_detail := tmp_attr_set.cnum;
1655 RETURN NEXT attr_set; CONTINUE;
1657 attr_set.copy_number := tmp_str::INT;
1660 IF tmp_attr_set.ol != '' THEN
1661 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
1663 attr_set.import_error := 'import.item.invalid.owning_lib';
1664 attr_set.error_detail := tmp_attr_set.ol;
1665 RETURN NEXT attr_set; CONTINUE;
1669 IF tmp_attr_set.clib != '' THEN
1670 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
1672 attr_set.import_error := 'import.item.invalid.circ_lib';
1673 attr_set.error_detail := tmp_attr_set.clib;
1674 RETURN NEXT attr_set; CONTINUE;
1678 IF tmp_attr_set.cs != '' THEN
1679 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
1681 attr_set.import_error := 'import.item.invalid.status';
1682 attr_set.error_detail := tmp_attr_set.cs;
1683 RETURN NEXT attr_set; CONTINUE;
1687 IF tmp_attr_set.circ_mod != '' THEN
1688 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
1690 attr_set.import_error := 'import.item.invalid.circ_modifier';
1691 attr_set.error_detail := tmp_attr_set.circ_mod;
1692 RETURN NEXT attr_set; CONTINUE;
1696 IF tmp_attr_set.circ_as != '' THEN
1697 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
1699 attr_set.import_error := 'import.item.invalid.circ_as_type';
1700 attr_set.error_detail := tmp_attr_set.circ_as;
1701 RETURN NEXT attr_set; CONTINUE;
1705 IF tmp_attr_set.cl != '' THEN
1707 -- search up the org unit tree for a matching copy location
1708 WITH RECURSIVE anscestor_depth AS (
1712 FROM actor.org_unit ou
1713 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1714 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
1719 FROM actor.org_unit ou
1720 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1721 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
1722 ) SELECT cpl.id INTO attr_set.location
1723 FROM anscestor_depth a
1724 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
1725 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
1726 ORDER BY a.depth DESC
1730 attr_set.import_error := 'import.item.invalid.location';
1731 attr_set.error_detail := tmp_attr_set.cs;
1732 RETURN NEXT attr_set; CONTINUE;
1736 attr_set.circulate :=
1737 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
1738 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
1741 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
1742 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
1744 attr_set.holdable :=
1745 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
1746 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
1748 attr_set.opac_visible :=
1749 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
1750 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
1753 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
1754 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
1756 attr_set.call_number := tmp_attr_set.cn; -- TEXT
1757 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1758 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1759 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1760 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1761 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1762 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
1764 RETURN NEXT attr_set;
1773 $$ LANGUAGE PLPGSQL;
1777 -- 0732.schema.acq-lineitem-summary.sql
1779 SELECT evergreen.upgrade_deps_block_check('0732', :eg_version);
1781 CREATE OR REPLACE VIEW acq.lineitem_summary AS
1785 SELECT COUNT(lid.id)
1786 FROM acq.lineitem_detail lid
1787 WHERE lineitem = li.id
1790 SELECT COUNT(lid.id)
1791 FROM acq.lineitem_detail lid
1792 WHERE recv_time IS NOT NULL AND lineitem = li.id
1795 SELECT COUNT(lid.id)
1796 FROM acq.lineitem_detail lid
1797 WHERE cancel_reason IS NOT NULL AND lineitem = li.id
1800 SELECT COUNT(lid.id)
1801 FROM acq.lineitem_detail lid
1802 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1803 WHERE NOT debit.encumbrance AND lineitem = li.id
1806 SELECT COUNT(DISTINCT(lid.id))
1807 FROM acq.lineitem_detail lid
1808 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
1809 WHERE lineitem = li.id
1812 SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
1813 FROM acq.lineitem_detail lid
1814 WHERE lid.cancel_reason IS NULL AND lineitem = li.id
1815 ) AS estimated_amount,
1817 SELECT SUM(debit.amount)::NUMERIC(8,2)
1818 FROM acq.lineitem_detail lid
1819 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1820 WHERE debit.encumbrance AND lineitem = li.id
1821 ) AS encumbrance_amount,
1823 SELECT SUM(debit.amount)::NUMERIC(8,2)
1824 FROM acq.lineitem_detail lid
1825 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
1826 WHERE NOT debit.encumbrance AND lineitem = li.id
1829 FROM acq.lineitem AS li;
1834 -- Template update included here for reference only.
1835 -- The stock JEDI template is not updated here (see WHERE clause)
1836 -- We do update the environment, though, for easier local template
1837 -- updating. No env fields are removed (that aren't otherwise replaced).
1841 SELECT evergreen.upgrade_deps_block_check('0733', :eg_version);
1843 UPDATE action_trigger.event_definition SET template =
1845 [%# start JEDI document
1846 # Vendor specific kludges:
1847 # BT - vendcode goes to NAD/BY *suffix* w/ 91 qualifier
1848 # INGRAM - vendcode goes to NAD/BY *segment* w/ 91 qualifier (separately)
1849 # BRODART - vendcode goes to FTX segment (lineitem level)
1852 IF target.provider.edi_default.vendcode && target.provider.code == 'BRODART';
1853 xtra_ftx = target.provider.edi_default.vendcode;
1856 [%- BLOCK big_block -%]
1858 "recipient":"[% target.provider.san %]",
1859 "sender":"[% target.ordering_agency.mailing_address.san %]",
1861 "ORDERS":[ "order", {
1862 "po_number":[% target.id %],
1863 "date":"[% date.format(date.now, '%Y%m%d') %]",
1865 [% IF target.provider.edi_default.vendcode && (target.provider.code == 'BT' || target.provider.name.match('(?i)^BAKER & TAYLOR')) -%]
1866 {"id-qualifier": 91, "id":"[% target.ordering_agency.mailing_address.san _ ' ' _ target.provider.edi_default.vendcode %]"}
1867 [%- ELSIF target.provider.edi_default.vendcode && target.provider.code == 'INGRAM' -%]
1868 {"id":"[% target.ordering_agency.mailing_address.san %]"},
1869 {"id-qualifier": 91, "id":"[% target.provider.edi_default.vendcode %]"}
1871 {"id":"[% target.ordering_agency.mailing_address.san %]"}
1875 [%- # target.provider.name (target.provider.id) -%]
1876 "[% target.provider.san %]",
1877 {"id-qualifier": 92, "id":"[% target.provider.id %]"}
1879 "currency":"[% target.provider.currency_type %]",
1882 [%- FOR li IN target.lineitems %]
1884 "line_index":"[% li.id %]",
1885 "identifiers":[ [%-# li.isbns = helpers.get_li_isbns(li.attributes) %]
1886 [% FOR isbn IN helpers.get_li_isbns(li.attributes) -%]
1887 [% IF isbn.length == 13 -%]
1888 {"id-qualifier":"EN","id":"[% isbn %]"},
1890 {"id-qualifier":"IB","id":"[% isbn %]"},
1893 {"id-qualifier":"IN","id":"[% li.id %]"}
1895 "price":[% li.estimated_unit_price || '0.00' %],
1897 {"BTI":"[% helpers.get_li_attr_jedi('title', '', li.attributes) %]"},
1898 {"BPU":"[% helpers.get_li_attr_jedi('publisher', '', li.attributes) %]"},
1899 {"BPD":"[% helpers.get_li_attr_jedi('pubdate', '', li.attributes) %]"},
1900 {"BPH":"[% helpers.get_li_attr_jedi('pagination','', li.attributes) %]"}
1903 FOR note IN li.lineitem_notes;
1904 NEXT UNLESS note.vendor_public == 't';
1905 ftx_vals.push(note.value);
1907 IF xtra_ftx; ftx_vals.unshift(xtra_ftx); END;
1908 IF ftx_vals.size == 0; ftx_vals.unshift(''); END; # BT needs FTX+LIN for every LI, even if it is an empty one
1912 [% FOR note IN ftx_vals -%] "[% note %]"[% UNLESS loop.last %], [% END %][% END %]
1914 "quantity":[% li.lineitem_details.size %],
1917 [%- FOR lid IN li.lineitem_details;
1918 fund = lid.fund.code;
1919 item_type = lid.circ_modifier;
1920 callnumber = lid.cn_label;
1921 owning_lib = lid.owning_lib.shortname;
1922 location = lid.location;
1924 # when we have real copy data, treat it as authoritative
1925 acp = lid.eg_copy_id;
1927 item_type = acp.circ_modifier;
1928 callnumber = acp.call_number.label;
1929 location = acp.location.name;
1931 { [%- IF fund %] "fund" : "[% fund %]",[% END -%]
1932 [%- IF callnumber %] "call_number" : "[% callnumber %]", [% END -%]
1933 [%- IF item_type %] "item_type" : "[% item_type %]", [% END -%]
1934 [%- IF location %] "copy_location" : "[% location %]", [% END -%]
1935 [%- IF owning_lib %] "owning_lib" : "[% owning_lib %]", [% END -%]
1936 [%- #chomp %]"copy_id" : "[% lid.id %]" }[% ',' UNLESS loop.last %]
1940 }[% UNLESS loop.last %],[% END %]
1941 [%-# TODO: lineitem details (later) -%]
1944 "line_items":[% target.lineitems.size %]
1945 }] [%# close ORDERS array %]
1946 }] [%# close body array %]
1949 [% tempo = PROCESS big_block; helpers.escape_json(tempo) %]
1951 WHERE id = 23 AND FALSE; -- DON'T PERFORM THE UPDATE
1954 -- add copy-related fields to the environment if they're not already there.
1958 FROM action_trigger.environment
1961 path = 'lineitems.lineitem_details.owning_lib';
1963 INSERT INTO action_trigger.environment (event_def, path)
1964 VALUES (23, 'lineitems.lineitem_details.owning_lib');
1968 FROM action_trigger.environment
1971 path = 'lineitems.lineitem_details.fund';
1973 INSERT INTO action_trigger.environment (event_def, path)
1974 VALUES (23, 'lineitems.lineitem_details.fund');
1978 FROM action_trigger.environment
1981 path = 'lineitems.lineitem_details.location';
1983 INSERT INTO action_trigger.environment (event_def, path)
1984 VALUES (23, 'lineitems.lineitem_details.location');
1988 FROM action_trigger.environment
1991 path = 'lineitems.lineitem_details.eg_copy_id.location';
1993 INSERT INTO action_trigger.environment (event_def, path)
1994 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.location');
1998 FROM action_trigger.environment
2001 path = 'lineitems.lineitem_details.eg_copy_id.call_number';
2003 INSERT INTO action_trigger.environment (event_def, path)
2004 VALUES (23, 'lineitems.lineitem_details.eg_copy_id.call_number');
2009 -- remove redundant entry
2010 DELETE FROM action_trigger.environment
2011 WHERE event_def = 23 AND path = 'lineitems.lineitem_details';
2016 -- Evergreen DB patch 0734.tpac_holdable_check.sql
2019 -- check whether patch can be applied
2020 SELECT evergreen.upgrade_deps_block_check('0734', :eg_version);
2022 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2027 JOIN asset.call_number acn ON acp.call_number = acn.id
2028 JOIN asset.copy_location acpl ON acp.location = acpl.id
2029 JOIN config.copy_status ccs ON acp.status = ccs.id
2032 AND acp.holdable = true
2033 AND acpl.holdable = true
2034 AND ccs.holdable = true
2035 AND acp.deleted = false
2042 $f$ LANGUAGE PLPGSQL;
2044 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
2049 JOIN asset.call_number acn ON acp.call_number = acn.id
2050 JOIN asset.copy_location acpl ON acp.location = acpl.id
2051 JOIN config.copy_status ccs ON acp.status = ccs.id
2052 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
2054 mmsm.metarecord = rid
2055 AND acp.holdable = true
2056 AND acpl.holdable = true
2057 AND ccs.holdable = true
2058 AND acp.deleted = false
2065 $f$ LANGUAGE PLPGSQL;
2067 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
2071 depth INT DEFAULT NULL,
2072 includes TEXT[] DEFAULT NULL::TEXT[],
2073 slimit HSTORE DEFAULT NULL,
2074 soffset HSTORE DEFAULT NULL,
2075 include_xmlns BOOL DEFAULT TRUE,
2076 pref_lib INT DEFAULT NULL
2082 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2083 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
2084 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
2088 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
2091 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2093 FROM asset.opac_ou_record_copy_count($2, $1)
2097 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2099 FROM asset.staff_ou_record_copy_count($2, $1)
2103 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2105 FROM asset.opac_ou_record_copy_count($9, $1)
2110 WHEN ('bmp' = ANY ($5)) THEN
2112 name monograph_parts,
2113 (SELECT XMLAGG(bmp) FROM (
2114 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)
2115 FROM biblio.monograph_part
2123 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
2125 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
2126 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
2129 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
2130 FROM evergreen.located_uris($1, $2, $9) AS uris
2133 CASE WHEN ('ssub' = ANY ($5)) THEN
2136 (SELECT XMLAGG(ssub) FROM (
2137 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
2138 FROM serial.subscription
2139 WHERE record_entry = $1
2143 CASE WHEN ('acp' = ANY ($5)) THEN
2145 name foreign_copies,
2146 (SELECT XMLAGG(acp) FROM (
2147 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
2148 FROM biblio.peer_bib_copy_map p
2149 JOIN asset.copy c ON (p.target_copy = c.id)
2150 WHERE NOT c.deleted AND p.peer_record = $1
2151 LIMIT ($6 -> 'acp')::INT
2152 OFFSET ($7 -> 'acp')::INT
2157 $F$ LANGUAGE SQL STABLE;
2159 -- Evergreen DB patch 0735.data.search_filter_group_perms.sql
2162 -- check whether patch can be applied
2163 SELECT evergreen.upgrade_deps_block_check('0735', :eg_version);
2165 INSERT INTO permission.perm_list ( id, code, description )
2168 'ADMIN_SEARCH_FILTER_GROUP',
2171 'Allows staff to manage search filter groups and entries',
2178 'VIEW_SEARCH_FILTER_GROUP',
2181 'Allows staff to view search filter groups and entries',
2188 -- check whether patch can be applied
2189 SELECT evergreen.upgrade_deps_block_check('0737', :eg_version);
2191 UPDATE action_trigger.event_definition
2195 # target is the bookbag itself. The 'items' variable does not need to be in
2196 # the environment because a special reactor will take care of filling it in.
2199 bibxml = helpers.unapi_bre(item.target_biblio_record_entry, {flesh => '{mra}'});
2201 FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
2202 title = title _ part.textContent;
2204 author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
2205 item_type = bibxml.findnodes('//*[local-name()="attributes"]/*[local-name()="field"][@name="item_type"]').getAttribute('coded-value');
2207 FOR pdatum IN bibxml.findnodes('//*[@tag="260"]/*[@code="c"]');
2209 pub_date = pub_date _ ", " _ pdatum.textContent;
2211 pub_date = pdatum.textContent;
2214 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";
2217 WHERE name = 'Bookbag CSV';