Schema Changes: 2.3.0
Upgrade: 2.2 → 2.3.0
This release applied 18 migration(s) to the database schema.
| ID | Type | Description |
|---|---|---|
— |
tpac value maps |
|
schema |
search query groups |
|
data |
simplified hold pull list |
|
data |
tpac override hold ou setting |
|
data |
add to permanent bookbag |
|
data |
actor event log settings |
|
schema |
copy loc circ limits |
|
data |
hold cap fill penalty blocks |
|
function |
xml pretty print |
|
data |
kpac filter group |
|
— |
vr format value maps |
|
schema |
acq function dedup |
|
schema |
vandelay item overlay |
|
schema |
acq lineitem summary |
|
data |
jedi with copies |
|
— |
tpac holdable check |
|
data |
search filter group perms |
|
data |
update ContainerCSV template |
Migration Details
703 — tpac value maps
View SQL
-- Evergreen DB patch 0703.tpac_value_maps.sql
-- check whether patch can be applied
ALTER TABLE config.coded_value_map
ADD COLUMN opac_visible BOOL NOT NULL DEFAULT TRUE,
ADD COLUMN search_label TEXT,
ADD COLUMN is_simple BOOL NOT NULL DEFAULT FALSE;712 — search query groups
Type: schema
View SQL
-- General purpose query container. Any table the needs to store
-- a QueryParser query should store it here. This will be the
-- source for top-level and QP sub-search inclusion queries.
CREATE TABLE actor.search_query (
id SERIAL PRIMARY KEY,
label TEXT NOT NULL, -- i18n
query_text TEXT NOT NULL -- QP text
);
-- e.g. "Reading Level"
CREATE TABLE actor.search_filter_group (
id SERIAL PRIMARY KEY,
owner INT NOT NULL REFERENCES actor.org_unit (id)
ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
code TEXT NOT NULL, -- for CGI, etc.
label TEXT NOT NULL, -- i18n
create_date TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT asfg_label_once_per_org UNIQUE (owner, label),
CONSTRAINT asfg_code_once_per_org UNIQUE (owner, code)
);
-- e.g. "Adult", "Teen", etc.
CREATE TABLE actor.search_filter_group_entry (
id SERIAL PRIMARY KEY,
grp INT NOT NULL REFERENCES actor.search_filter_group(id)
ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
pos INT NOT NULL DEFAULT 0,
query INT NOT NULL REFERENCES actor.search_query(id)
ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT asfge_query_once_per_group UNIQUE (grp, query)
);
/*
-- Fictional Example
INSERT INTO actor.search_filter_group (owner, code, label)
VALUES (4, 'reading_level', 'Reading Level');
INSERT INTO actor.search_query (label, query_text)
VALUES ('Children', 'audience(a,b,c) locations(3,4,5,6)');
INSERT INTO actor.search_query (label, query_text)
VALUES ('Juvenile', 'audience(j,d) locations(1,2,7,8)');
INSERT INTO actor.search_query (label, query_text)
VALUES ('General', 'audience(e,f,g)');
INSERT INTO actor.search_filter_group_entry (grp, query)
VALUES (
(SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'),
(SELECT id FROM actor.search_query WHERE label = 'Children')
);
INSERT INTO actor.search_filter_group_entry (grp, query)
VALUES (
(SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'),
(SELECT id FROM actor.search_query WHERE label = 'Juvenile')
);
INSERT INTO actor.search_filter_group_entry (grp, query)
VALUES (
(SELECT id FROM actor.search_filter_group WHERE code = 'reading_level'),
(SELECT id FROM actor.search_query WHERE label = 'General')
);
*/
/*
-- UNDO
DROP TABLE actor.search_filter_group_entry;
DROP TABLE actor.search_filter_group;
DROP TABLE actor.search_query;
*/713 — simplified hold pull list
Type: data
View SQL
INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
'ui.grid_columns.circ.hold_pull_list',
'gui',
FALSE,
oils_i18n_gettext(
'ui.grid_columns.circ.hold_pull_list',
'Hold Pull List',
'cust',
'label'
),
oils_i18n_gettext(
'ui.grid_columns.circ.hold_pull_list',
'Hold Pull List Saved Column Settings',
'cust',
'description'
),
'string'
);714 — tpac override hold ou setting
Type: data
View SQL
INSERT into config.org_unit_setting_type
(name, grp, label, description, datatype)
VALUES (
'opac.patron.auto_overide_hold_events',
'opac',
oils_i18n_gettext(
'opac.patron.auto_overide_hold_events',
'Auto-Override Permitted Hold Blocks (Patrons)',
'coust',
'label'
),
oils_i18n_gettext(
'opac.patron.auto_overide_hold_events',
'When a patron places a hold that fails and the patron has the correct permission ' ||
'to override the hold, automatically override the hold without presenting a message ' ||
'to the patron and requiring that the patron make a decision to override',
'coust',
'description'
),
'bool'
);718 — add to permanent bookbag
Type: data
View SQL
-- Evergreen DB patch 0718.data.add-to-permanent-bookbag.sql
-- check whether patch can be applied
INSERT into config.org_unit_setting_type
(name, grp, label, description, datatype)
VALUES (
'opac.patron.temporary_list_warn',
'opac',
oils_i18n_gettext(
'opac.patron.temporary_list_warn',
'Warn patrons when adding to a temporary book list',
'coust',
'label'
),
oils_i18n_gettext(
'opac.patron.temporary_list_warn',
'Present a warning dialog to the patron when a patron adds a book to a temporary book bag.',
'coust',
'description'
),
'bool'
);
INSERT INTO config.usr_setting_type
(name,grp,opac_visible,label,description,datatype)
VALUES (
'opac.temporary_list_no_warn',
'opac',
TRUE,
oils_i18n_gettext(
'opac.temporary_list_no_warn',
'Opt out of warning when adding a book to a temporary book list',
'cust',
'label'
),
oils_i18n_gettext(
'opac.temporary_list_no_warn',
'Opt out of warning when adding a book to a temporary book list',
'cust',
'description'
),
'bool'
);
INSERT INTO config.usr_setting_type
(name,grp,opac_visible,label,description,datatype)
VALUES (
'opac.default_list',
'opac',
FALSE,
oils_i18n_gettext(
'opac.default_list',
'Default list to use when adding to a bookbag',
'cust',
'label'
),
oils_i18n_gettext(
'opac.default_list',
'Default list to use when adding to a bookbag',
'cust',
'description'
),
'integer'
);719 — actor event log settings
Type: data
View SQL
INSERT INTO config.org_unit_setting_type (
name, label, grp, description, datatype
) VALUES (
'circ.staff.max_visible_event_age',
'Maximum visible age of User Trigger Events in Staff Interfaces',
'circ',
'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.',
'interval'
);
INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
'ui.grid_columns.actor.user.event_log',
'gui',
FALSE,
oils_i18n_gettext(
'ui.grid_columns.actor.user.event_log',
'User Event Log',
'cust',
'label'
),
oils_i18n_gettext(
'ui.grid_columns.actor.user.event_log',
'User Event Log Saved Column Settings',
'cust',
'description'
),
'string'
);
INSERT INTO permission.perm_list ( id, code, description )
VALUES (
535,
'VIEW_TRIGGER_EVENT',
oils_i18n_gettext(
535,
'Allows a user to view circ- and hold-related action/trigger events',
'ppl',
'description'
)
);720 — copy loc circ limits
Type: schema
View SQL
ALTER TABLE config.circ_matrix_weights
ADD COLUMN copy_location NUMERIC(6,2) NOT NULL DEFAULT 5.0;
UPDATE config.circ_matrix_weights
SET copy_location = 0.0 WHERE name = 'All_Equal';
ALTER TABLE config.circ_matrix_weights
ALTER COLUMN copy_location DROP DEFAULT; -- for consistency w/ baseline schema
ALTER TABLE config.circ_matrix_matchpoint
ADD COLUMN copy_location INTEGER REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED;
DROP INDEX config.ccmm_once_per_paramset;
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;
-- Linkage between limit sets and circ mods
CREATE TABLE config.circ_limit_set_copy_loc_map (
id SERIAL PRIMARY KEY,
limit_set INT NOT NULL REFERENCES config.circ_limit_set (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
copy_loc INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT cl_once_per_set UNIQUE (limit_set, copy_loc)
);
-- Add support for checking config.circ_limit_set_copy_loc_map's
CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL )
RETURNS SETOF action.circ_matrix_test_result AS $func$
DECLARE
user_object actor.usr%ROWTYPE;
standing_penalty config.standing_penalty%ROWTYPE;
item_object asset.copy%ROWTYPE;
item_status_object config.copy_status%ROWTYPE;
item_location_object asset.copy_location%ROWTYPE;
result action.circ_matrix_test_result;
circ_test action.found_circ_matrix_matchpoint;
circ_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
circ_limit_set config.circ_limit_set%ROWTYPE;
hold_ratio action.hold_stats%ROWTYPE;
penalty_type TEXT;
items_out INT;
context_org_list INT[];
done BOOL := FALSE;
-- Assume success unless we hit a failure condition
result.success := TRUE;
-- Need user info to look up matchpoints
SELECT INTO user_object * FROM actor.usr WHERE id = match_user AND NOT deleted;
-- (Insta)Fail if we couldn't find the user
IF user_object.id IS NULL THEN
result.fail_part := 'no_user';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
RETURN;
END IF;
-- Need item info to look up matchpoints
SELECT INTO item_object * FROM asset.copy WHERE id = match_item AND NOT deleted;
-- (Insta)Fail if we couldn't find the item
IF item_object.id IS NULL THEN
result.fail_part := 'no_item';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
RETURN;
END IF;
SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, item_object, user_object, renewal);
circ_matchpoint := circ_test.matchpoint;
result.matchpoint := circ_matchpoint.id;
result.circulate := circ_matchpoint.circulate;
result.duration_rule := circ_matchpoint.duration_rule;
result.recurring_fine_rule := circ_matchpoint.recurring_fine_rule;
result.max_fine_rule := circ_matchpoint.max_fine_rule;
result.hard_due_date := circ_matchpoint.hard_due_date;
result.renewals := circ_matchpoint.renewals;
result.grace_period := circ_matchpoint.grace_period;
result.buildrows := circ_test.buildrows;
-- (Insta)Fail if we couldn't find a matchpoint
IF circ_test.success = false THEN
result.fail_part := 'no_matchpoint';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
RETURN;
END IF;
-- All failures before this point are non-recoverable
-- Below this point are possibly overridable failures
-- Fail if the user is barred
IF user_object.barred IS TRUE THEN
result.fail_part := 'actor.usr.barred';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
-- Fail if the item can't circulate
IF item_object.circulate IS FALSE THEN
result.fail_part := 'asset.copy.circulate';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
-- Fail if the item isn't in a circulateable status on a non-renewal
IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN
result.fail_part := 'asset.copy.status';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
-- Alternately, fail if the item isn't checked out on a renewal
ELSIF renewal AND item_object.status <> 1 THEN
result.fail_part := 'asset.copy.status';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
-- Fail if the item can't circulate because of the shelving location
SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
IF item_location_object.circulate IS FALSE THEN
result.fail_part := 'asset.copy_location.circulate';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
-- Use Circ OU for penalties and such
SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( circ_ou );
IF renewal THEN
penalty_type = '%RENEW%';
ELSE
penalty_type = '%CIRC%';
END IF;
FOR standing_penalty IN
SELECT DISTINCT csp.*
FROM actor.usr_standing_penalty usp
JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
WHERE usr = match_user
AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
AND (usp.stop_date IS NULL or usp.stop_date > NOW())
AND csp.block_list LIKE penalty_type LOOP
result.fail_part := standing_penalty.name;
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END LOOP;
-- Fail if the test is set to hard non-circulating
IF circ_matchpoint.circulate IS FALSE THEN
result.fail_part := 'config.circ_matrix_test.circulate';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
-- Fail if the total copy-hold ratio is too low
IF circ_matchpoint.total_copy_hold_ratio IS NOT NULL THEN
SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
IF hold_ratio.total_copy_ratio IS NOT NULL AND hold_ratio.total_copy_ratio < circ_matchpoint.total_copy_hold_ratio THEN
result.fail_part := 'config.circ_matrix_test.total_copy_hold_ratio';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
END IF;
-- Fail if the available copy-hold ratio is too low
IF circ_matchpoint.available_copy_hold_ratio IS NOT NULL THEN
IF hold_ratio.hold_count IS NULL THEN
SELECT INTO hold_ratio * FROM action.copy_related_hold_stats(match_item);
END IF;
IF hold_ratio.available_copy_ratio IS NOT NULL AND hold_ratio.available_copy_ratio < circ_matchpoint.available_copy_hold_ratio THEN
result.fail_part := 'config.circ_matrix_test.available_copy_hold_ratio';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
END IF;
-- Fail if the user has too many items out by defined limit sets
FOR circ_limit_set IN SELECT ccls.* FROM config.circ_limit_set ccls
JOIN config.circ_matrix_limit_set_map ccmlsm ON ccmlsm.limit_set = ccls.id
WHERE ccmlsm.active AND ( ccmlsm.matchpoint = circ_matchpoint.id OR
( ccmlsm.matchpoint IN (SELECT * FROM unnest(result.buildrows)) AND ccmlsm.fallthrough )
) LOOP
IF circ_limit_set.items_out > 0 AND NOT renewal THEN
SELECT INTO context_org_list ARRAY_AGG(aou.id)
FROM actor.org_unit_full_path( circ_ou ) aou
JOIN actor.org_unit_type aout ON aou.ou_type = aout.id
WHERE aout.depth >= circ_limit_set.depth;
IF circ_limit_set.global THEN
WITH RECURSIVE descendant_depth AS (
SELECT ou.id,
ou.parent_ou
FROM actor.org_unit ou
WHERE ou.id IN (SELECT * FROM unnest(context_org_list))
UNION
SELECT ou.id,
ou.parent_ou
FROM actor.org_unit ou
JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
) SELECT INTO context_org_list ARRAY_AGG(ou.id) FROM actor.org_unit ou JOIN descendant_depth USING (id);
END IF;
SELECT INTO items_out COUNT(DISTINCT circ.id)
FROM action.circulation circ
JOIN asset.copy copy ON (copy.id = circ.target_copy)
LEFT JOIN action.circulation_limit_group_map aclgm ON (circ.id = aclgm.circ)
WHERE circ.usr = match_user
AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
AND circ.checkin_time IS NULL
AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL)
AND (copy.circ_modifier IN (SELECT circ_mod FROM config.circ_limit_set_circ_mod_map WHERE limit_set = circ_limit_set.id)
OR copy.location IN (SELECT copy_loc FROM config.circ_limit_set_copy_loc_map WHERE limit_set = circ_limit_set.id)
OR aclgm.limit_group IN (SELECT limit_group FROM config.circ_limit_set_group_map WHERE limit_set = circ_limit_set.id)
);
IF items_out >= circ_limit_set.items_out THEN
result.fail_part := 'config.circ_matrix_circ_mod_test';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
END IF;
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;
END LOOP;
-- If we passed everything, return the successful matchpoint
IF NOT done THEN
RETURN NEXT result;
END IF;
RETURN;
END;
$func$ LANGUAGE plpgsql;
-- adding copy_loc to circ_matrix_matchpoint
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$
DECLARE
cn_object asset.call_number%ROWTYPE;
rec_descriptor metabib.rec_descriptor%ROWTYPE;
cur_matchpoint config.circ_matrix_matchpoint%ROWTYPE;
matchpoint config.circ_matrix_matchpoint%ROWTYPE;
weights config.circ_matrix_weights%ROWTYPE;
user_age INTERVAL;
my_item_age INTERVAL;
denominator NUMERIC(6,2);
row_list INT[];
result action.found_circ_matrix_matchpoint;
-- Assume failure
result.success = false;
-- Fetch useful data
SELECT INTO cn_object * FROM asset.call_number WHERE id = item_object.call_number;
SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = cn_object.record;
-- Pre-generate this so we only calc it once
IF user_object.dob IS NOT NULL THEN
SELECT INTO user_age age(user_object.dob);
END IF;
-- Ditto
SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
-- Grab the closest set circ weight setting.
SELECT INTO weights cw.*
FROM config.weight_assoc wa
JOIN config.circ_matrix_weights cw ON (cw.id = wa.circ_weights)
JOIN actor.org_unit_ancestors_distance( context_ou ) d ON (wa.org_unit = d.id)
WHERE active
ORDER BY d.distance
LIMIT 1;
-- No weights? Bad admin! Defaults to handle that anyway.
IF weights.id IS NULL THEN
weights.grp := 11.0;
weights.org_unit := 10.0;
weights.circ_modifier := 5.0;
weights.copy_location := 5.0;
weights.marc_type := 4.0;
weights.marc_form := 3.0;
weights.marc_bib_level := 2.0;
weights.marc_vr_format := 2.0;
weights.copy_circ_lib := 8.0;
weights.copy_owning_lib := 8.0;
weights.user_home_ou := 8.0;
weights.ref_flag := 1.0;
weights.juvenile_flag := 6.0;
weights.is_renewal := 7.0;
weights.usr_age_lower_bound := 0.0;
weights.usr_age_upper_bound := 0.0;
weights.item_age := 0.0;
END IF;
-- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
-- If you break your org tree with funky parenting this may be wrong
-- 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
-- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
WITH all_distance(distance) AS (
SELECT depth AS distance FROM actor.org_unit_type
UNION
SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
)
SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
-- Loop over all the potential matchpoints
FOR cur_matchpoint IN
SELECT m.*
FROM config.circ_matrix_matchpoint m
/*LEFT*/ JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.grp = upgad.id
/*LEFT*/ JOIN actor.org_unit_ancestors_distance( context_ou ) ctoua ON m.org_unit = ctoua.id
LEFT JOIN actor.org_unit_ancestors_distance( cn_object.owning_lib ) cnoua ON m.copy_owning_lib = cnoua.id
LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.copy_circ_lib = iooua.id
LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
WHERE m.active
-- Permission Groups
-- AND (m.grp IS NULL OR upgad.id IS NOT NULL) -- Optional Permission Group?
-- Org Units
-- AND (m.org_unit IS NULL OR ctoua.id IS NOT NULL) -- Optional Org Unit?
AND (m.copy_owning_lib IS NULL OR cnoua.id IS NOT NULL)
AND (m.copy_circ_lib IS NULL OR iooua.id IS NOT NULL)
AND (m.user_home_ou IS NULL OR uhoua.id IS NOT NULL)
-- Circ Type
AND (m.is_renewal IS NULL OR m.is_renewal = renewal)
-- Static User Checks
AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
AND (m.usr_age_lower_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_lower_bound < user_age))
AND (m.usr_age_upper_bound IS NULL OR (user_age IS NOT NULL AND m.usr_age_upper_bound > user_age))
-- Static Item Checks
AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
AND (m.copy_location IS NULL OR m.copy_location = item_object.location)
AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
ORDER BY
-- Permission Groups
CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.grp - (upgad.distance/denominator)) ELSE 0.0 END +
-- Org Units
CASE WHEN ctoua.distance IS NOT NULL THEN 2^(2*weights.org_unit - (ctoua.distance/denominator)) ELSE 0.0 END +
CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.copy_owning_lib - (cnoua.distance/denominator)) ELSE 0.0 END +
CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.copy_circ_lib - (iooua.distance/denominator)) ELSE 0.0 END +
CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
-- Circ Type -- Note: 4^x is equiv to 2^(2*x)
CASE WHEN m.is_renewal IS NOT NULL THEN 4^weights.is_renewal ELSE 0.0 END +
-- Static User Checks
CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
CASE WHEN m.usr_age_lower_bound IS NOT NULL THEN 4^weights.usr_age_lower_bound ELSE 0.0 END +
CASE WHEN m.usr_age_upper_bound IS NOT NULL THEN 4^weights.usr_age_upper_bound ELSE 0.0 END +
-- Static Item Checks
CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
CASE WHEN m.copy_location IS NOT NULL THEN 4^weights.copy_location ELSE 0.0 END +
CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
-- Item age has a slight adjustment to weight based on value.
-- This should ensure that a shorter age limit comes first when all else is equal.
-- NOTE: This assumes that intervals will normally be in days.
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,
-- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
-- This prevents "we changed the table order by updating a rule, and we started getting different results"
m.id LOOP
-- Record the full matching row list
row_list := row_list || cur_matchpoint.id;
-- No matchpoint yet?
IF matchpoint.id IS NULL THEN
-- Take the entire matchpoint as a starting point
matchpoint := cur_matchpoint;
CONTINUE; -- No need to look at this row any more.
END IF;
-- Incomplete matchpoint?
IF matchpoint.circulate IS NULL THEN
matchpoint.circulate := cur_matchpoint.circulate;
END IF;
IF matchpoint.duration_rule IS NULL THEN
matchpoint.duration_rule := cur_matchpoint.duration_rule;
END IF;
IF matchpoint.recurring_fine_rule IS NULL THEN
matchpoint.recurring_fine_rule := cur_matchpoint.recurring_fine_rule;
END IF;
IF matchpoint.max_fine_rule IS NULL THEN
matchpoint.max_fine_rule := cur_matchpoint.max_fine_rule;
END IF;
IF matchpoint.hard_due_date IS NULL THEN
matchpoint.hard_due_date := cur_matchpoint.hard_due_date;
END IF;
IF matchpoint.total_copy_hold_ratio IS NULL THEN
matchpoint.total_copy_hold_ratio := cur_matchpoint.total_copy_hold_ratio;
END IF;
IF matchpoint.available_copy_hold_ratio IS NULL THEN
matchpoint.available_copy_hold_ratio := cur_matchpoint.available_copy_hold_ratio;
END IF;
IF matchpoint.renewals IS NULL THEN
matchpoint.renewals := cur_matchpoint.renewals;
END IF;
IF matchpoint.grace_period IS NULL THEN
matchpoint.grace_period := cur_matchpoint.grace_period;
END IF;
END LOOP;
-- Check required fields
IF matchpoint.circulate IS NOT NULL AND
matchpoint.duration_rule IS NOT NULL AND
matchpoint.recurring_fine_rule IS NOT NULL AND
matchpoint.max_fine_rule IS NOT NULL THEN
-- All there? We have a completed match.
result.success := true;
END IF;
-- Include the assembled matchpoint, even if it isn't complete
result.matchpoint := matchpoint;
-- Include (for debugging) the full list of matching rows
result.buildrows := row_list;
-- Hand the result back to caller
RETURN result;
END;
$func$ LANGUAGE plpgsql;721 — hold cap fill penalty blocks
Type: data
View SQL
UPDATE config.standing_penalty
SET block_list = REPLACE(block_list, 'HOLD', 'HOLD|CAPTURE')
WHERE
-- STAFF_ penalties have names that match their block list
name NOT LIKE 'STAFF_%'
-- belt & suspenders, also good for testing
AND block_list NOT LIKE '%CAPTURE%';
-- CIRC|FULFILL is now the same as CIRC previously was by itself
UPDATE config.standing_penalty
SET block_list = REPLACE(block_list, 'CIRC', 'CIRC|FULFILL')
WHERE
-- STAFF_ penalties have names that match their block list
name NOT LIKE 'STAFF_%'
-- belt & suspenders, also good for testing
AND block_list NOT LIKE '%FULFILL%';
-- apply the HOLD vs CAPTURE block logic
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$
DECLARE
matchpoint_id INT;
user_object actor.usr%ROWTYPE;
age_protect_object config.rule_age_hold_protect%ROWTYPE;
standing_penalty config.standing_penalty%ROWTYPE;
transit_range_ou_type actor.org_unit_type%ROWTYPE;
transit_source actor.org_unit%ROWTYPE;
item_object asset.copy%ROWTYPE;
item_cn_object asset.call_number%ROWTYPE;
item_status_object config.copy_status%ROWTYPE;
item_location_object asset.copy_location%ROWTYPE;
ou_skip actor.org_unit_setting%ROWTYPE;
result action.matrix_test_result;
hold_test config.hold_matrix_matchpoint%ROWTYPE;
use_active_date TEXT;
age_protect_date TIMESTAMP WITH TIME ZONE;
hold_count INT;
hold_transit_prox INT;
frozen_hold_count INT;
context_org_list INT[];
done BOOL := FALSE;
hold_penalty TEXT;
SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou );
result.success := TRUE;
-- The HOLD penalty block only applies to new holds.
-- The CAPTURE penalty block applies to existing holds.
hold_penalty := 'HOLD';
IF retargetting THEN
hold_penalty := 'CAPTURE';
END IF;
-- Fail if we couldn't find a user
IF user_object.id IS NULL THEN
result.fail_part := 'no_user';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
RETURN;
END IF;
SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
-- Fail if we couldn't find a copy
IF item_object.id IS NULL THEN
result.fail_part := 'no_item';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
RETURN;
END IF;
SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
result.matchpoint := matchpoint_id;
SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
-- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
result.fail_part := 'circ.holds.target_skip_me';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
RETURN;
END IF;
-- Fail if user is barred
IF user_object.barred IS TRUE THEN
result.fail_part := 'actor.usr.barred';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
RETURN;
END IF;
SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
-- Fail if we couldn't find any matchpoint (requires a default)
IF matchpoint_id IS NULL THEN
result.fail_part := 'no_matchpoint';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
RETURN;
END IF;
SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
IF hold_test.holdable IS FALSE THEN
result.fail_part := 'config.hold_matrix_test.holdable';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
IF item_object.holdable IS FALSE THEN
result.fail_part := 'item.holdable';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
IF item_status_object.holdable IS FALSE THEN
result.fail_part := 'status.holdable';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
IF item_location_object.holdable IS FALSE THEN
result.fail_part := 'location.holdable';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
IF hold_test.transit_range IS NOT NULL THEN
SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
IF hold_test.distance_is_from_owner THEN
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;
ELSE
SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
END IF;
PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
IF NOT FOUND THEN
result.fail_part := 'transit_range';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
END IF;
FOR standing_penalty IN
SELECT DISTINCT csp.*
FROM actor.usr_standing_penalty usp
JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
WHERE usr = match_user
AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
AND (usp.stop_date IS NULL or usp.stop_date > NOW())
AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
result.fail_part := standing_penalty.name;
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END LOOP;
IF hold_test.stop_blocked_user IS TRUE THEN
FOR standing_penalty IN
SELECT DISTINCT csp.*
FROM actor.usr_standing_penalty usp
JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
WHERE usr = match_user
AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
AND (usp.stop_date IS NULL or usp.stop_date > NOW())
AND csp.block_list LIKE '%CIRC%' LOOP
result.fail_part := standing_penalty.name;
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END LOOP;
END IF;
IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
SELECT INTO hold_count COUNT(*)
FROM action.hold_request
WHERE usr = match_user
AND fulfillment_time IS NULL
AND cancel_time IS NULL
AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
IF hold_count >= hold_test.max_holds THEN
result.fail_part := 'config.hold_matrix_test.max_holds';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
END IF;
IF item_object.age_protect IS NOT NULL THEN
SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
IF hold_test.distance_is_from_owner THEN
SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
ELSE
SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
END IF;
IF use_active_date = 'true' THEN
age_protect_date := COALESCE(item_object.active_date, NOW());
ELSE
age_protect_date := item_object.create_date;
END IF;
IF age_protect_date + age_protect_object.age > NOW() THEN
IF hold_test.distance_is_from_owner THEN
SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
ELSE
SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
END IF;
IF hold_transit_prox > age_protect_object.prox THEN
result.fail_part := 'config.rule_age_hold_protect.prox';
result.success := FALSE;
done := TRUE;
RETURN NEXT result;
END IF;
END IF;
END IF;
IF NOT done THEN
RETURN NEXT result;
END IF;
RETURN;
END;
$func$ LANGUAGE plpgsql;727 — xml pretty print
Type: function
View SQL
-- Evergreen DB patch 0727.function.xml_pretty_print.sql
--
-- A simple pretty printer for XML.
-- Particularly useful for debugging the biblio.record_entry.marc field.
--
-- check whether patch can be applied
CREATE OR REPLACE FUNCTION evergreen.xml_pretty_print(input XML)
RETURNS XML
LANGUAGE SQL AS
$func$
SELECT xslt_process($1::text,
$$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="@*|node()">
<xsl:copy>
<xsl:apply-templates select="@*|node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
$$::text)::XML
$func$;
COMMENT ON FUNCTION evergreen.xml_pretty_print(input XML) IS
'Simple pretty printer for XML, as written by Andrew Dunstan at http://goo.gl/zBHIk';728 — kpac filter group
Type: data
View SQL
INSERT INTO actor.search_filter_group (owner, code, label)
VALUES (1, 'kpac_main', 'Kid''s OPAC Search Filter');
INSERT INTO actor.search_query (label, query_text)
VALUES ('Children''s Materials', 'audience(a,b,c)');
INSERT INTO actor.search_query (label, query_text)
VALUES ('Young Adult Materials', 'audience(j,d)');
INSERT INTO actor.search_query (label, query_text)
VALUES ('General/Adult Materials', 'audience(e,f,g, )');
INSERT INTO actor.search_filter_group_entry (grp, query, pos)
VALUES (
(SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
(SELECT id FROM actor.search_query WHERE label = 'Children''s Materials'),
0
);
INSERT INTO actor.search_filter_group_entry (grp, query, pos)
VALUES (
(SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
(SELECT id FROM actor.search_query WHERE label = 'Young Adult Materials'),
1
);
INSERT INTO actor.search_filter_group_entry (grp, query, pos)
VALUES (
(SELECT id FROM actor.search_filter_group WHERE code = 'kpac_main'),
(SELECT id FROM actor.search_query WHERE label = 'General/Adult Materials'),
2
);729 — vr format value maps
View SQL
-- Evergreen DB patch 0729.vr_format_value_maps.sql
--
-- check whether patch can be applied
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$
DECLARE
current_row config.coded_value_map%ROWTYPE;
-- Look for a current value
SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
-- If we have one..
IF FOUND AND NOT add_only THEN
-- Update anything we were handed
current_row.value := COALESCE(current_row.value, in_value);
current_row.description := COALESCE(current_row.description, in_description);
current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
current_row.search_label := COALESCE(current_row.search_label, in_search_label);
current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
UPDATE config.coded_value_map
SET
value = current_row.value,
description = current_row.description,
opac_visible = current_row.opac_visible,
search_label = current_row.search_label,
is_simple = current_row.is_simple
WHERE id = current_row.id;
ELSE
INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
(in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
END IF;
END;
$f$ LANGUAGE PLPGSQL;
SELECT config.update_coded_value_map('vr_format', 'a', 'Beta', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'b', 'VHS', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'c', 'U-matic', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'd', 'EIAJ', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'e', 'Type C', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'f', 'Quadruplex', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'g', 'Laserdisc', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'h', 'CED videodisc', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'i', 'Betacam', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'j', 'Betacam SP', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'k', 'Super-VHS', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'm', 'M-II', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'o', 'D-2', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'p', '8 mm.', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'q', 'Hi-8 mm.', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 's', 'Blu-ray disc', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'u', 'Unknown', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'v', 'DVD', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', 'z', 'Other', add_only := TRUE);
SELECT config.update_coded_value_map('vr_format', ' ', 'Unspecified', add_only := TRUE);730 — acq function dedup
Type: schema
View SQL
DROP FUNCTION acq.propagate_funds_by_org_tree (INT, INT, INT);
DROP FUNCTION acq.propagate_funds_by_org_unit (INT, INT, INT);
CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_tree(
old_year INTEGER,
user_id INTEGER,
org_unit_id INTEGER,
include_desc BOOL DEFAULT TRUE
) RETURNS VOID AS $$
DECLARE
--
new_id INT;
old_fund RECORD;
org_found BOOLEAN;
--
--
-- Sanity checks
--
IF old_year IS NULL THEN
RAISE EXCEPTION 'Input year argument is NULL';
ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
RAISE EXCEPTION 'Input year is out of range';
END IF;
--
IF user_id IS NULL THEN
RAISE EXCEPTION 'Input user id argument is NULL';
END IF;
--
IF org_unit_id IS NULL THEN
RAISE EXCEPTION 'Org unit id argument is NULL';
ELSE
SELECT TRUE INTO org_found
FROM actor.org_unit
WHERE id = org_unit_id;
--
IF org_found IS NULL THEN
RAISE EXCEPTION 'Org unit id is invalid';
END IF;
END IF;
--
-- Loop over the applicable funds
--
FOR old_fund in SELECT * FROM acq.fund
WHERE
year = old_year
AND propagate
AND ( ( include_desc AND org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
OR (NOT include_desc AND org = org_unit_id ) )
LOOP
INSERT INTO acq.fund (
org,
name,
year,
currency_type,
code,
rollover,
propagate,
balance_warning_percent,
balance_stop_percent
) VALUES (
old_fund.org,
old_fund.name,
old_year + 1,
old_fund.currency_type,
old_fund.code,
old_fund.rollover,
true,
old_fund.balance_warning_percent,
old_fund.balance_stop_percent
)
RETURNING id INTO new_id;
EXCEPTION
WHEN unique_violation THEN
--RAISE NOTICE 'Fund % already propagated', old_fund.id;
CONTINUE;
END;
--RAISE NOTICE 'Propagating fund % to fund %',
-- old_fund.code, new_id;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION acq.propagate_funds_by_org_unit( old_year INTEGER, user_id INTEGER, org_unit_id INTEGER ) RETURNS VOID AS $$
SELECT acq.propagate_funds_by_org_tree( $1, $2, $3, FALSE );
$$ LANGUAGE SQL;
DROP FUNCTION acq.rollover_funds_by_org_tree (INT, INT, INT);
DROP FUNCTION acq.rollover_funds_by_org_unit (INT, INT, INT);
CREATE OR REPLACE FUNCTION acq.rollover_funds_by_org_tree(
old_year INTEGER,
user_id INTEGER,
org_unit_id INTEGER,
encumb_only BOOL DEFAULT FALSE,
include_desc BOOL DEFAULT TRUE
) RETURNS VOID AS $$
DECLARE
--
new_fund INT;
new_year INT := old_year + 1;
org_found BOOL;
perm_ous BOOL;
xfer_amount NUMERIC := 0;
roll_fund RECORD;
deb RECORD;
detail RECORD;
--
--
-- Sanity checks
--
IF old_year IS NULL THEN
RAISE EXCEPTION 'Input year argument is NULL';
ELSIF old_year NOT BETWEEN 2008 and 2200 THEN
RAISE EXCEPTION 'Input year is out of range';
END IF;
--
IF user_id IS NULL THEN
RAISE EXCEPTION 'Input user id argument is NULL';
END IF;
--
IF org_unit_id IS NULL THEN
RAISE EXCEPTION 'Org unit id argument is NULL';
ELSE
--
-- Validate the org unit
--
SELECT TRUE
INTO org_found
FROM actor.org_unit
WHERE id = org_unit_id;
--
IF org_found IS NULL THEN
RAISE EXCEPTION 'Org unit id % is invalid', org_unit_id;
ELSIF encumb_only THEN
SELECT INTO perm_ous value::BOOL FROM
actor.org_unit_ancestor_setting(
'acq.fund.allow_rollover_without_money', org_unit_id
);
IF NOT FOUND OR NOT perm_ous THEN
RAISE EXCEPTION 'Encumbrance-only rollover not permitted at org %', org_unit_id;
END IF;
END IF;
END IF;
--
-- Loop over the propagable funds to identify the details
-- from the old fund plus the id of the new one, if it exists.
--
FOR roll_fund in
SELECT
oldf.id AS old_fund,
oldf.org,
oldf.name,
oldf.currency_type,
oldf.code,
oldf.rollover,
newf.id AS new_fund_id
FROM
acq.fund AS oldf
LEFT JOIN acq.fund AS newf
ON ( oldf.code = newf.code )
WHERE
oldf.year = old_year
AND oldf.propagate
AND newf.year = new_year
AND ( ( include_desc AND oldf.org IN ( SELECT id FROM actor.org_unit_descendants( org_unit_id ) ) )
OR (NOT include_desc AND oldf.org = org_unit_id ) )
LOOP
--RAISE NOTICE 'Processing fund %', roll_fund.old_fund;
--
IF roll_fund.new_fund_id IS NULL THEN
--
-- The old fund hasn't been propagated yet. Propagate it now.
--
INSERT INTO acq.fund (
org,
name,
year,
currency_type,
code,
rollover,
propagate,
balance_warning_percent,
balance_stop_percent
) VALUES (
roll_fund.org,
roll_fund.name,
new_year,
roll_fund.currency_type,
roll_fund.code,
true,
true,
roll_fund.balance_warning_percent,
roll_fund.balance_stop_percent
)
RETURNING id INTO new_fund;
ELSE
new_fund = roll_fund.new_fund_id;
END IF;
--
-- Determine the amount to transfer
--
SELECT amount
INTO xfer_amount
FROM acq.fund_spent_balance
WHERE fund = roll_fund.old_fund;
--
IF xfer_amount <> 0 THEN
IF NOT encumb_only AND roll_fund.rollover THEN
--
-- Transfer balance from old fund to new
--
--RAISE NOTICE 'Transferring % from fund % to %', xfer_amount, roll_fund.old_fund, new_fund;
--
PERFORM acq.transfer_fund(
roll_fund.old_fund,
xfer_amount,
new_fund,
xfer_amount,
user_id,
'Rollover'
);
ELSE
--
-- Transfer balance from old fund to the void
--
-- RAISE NOTICE 'Transferring % from fund % to the void', xfer_amount, roll_fund.old_fund;
--
PERFORM acq.transfer_fund(
roll_fund.old_fund,
xfer_amount,
NULL,
NULL,
user_id,
'Rollover into the void'
);
END IF;
END IF;
--
IF roll_fund.rollover THEN
--
-- Move any lineitems from the old fund to the new one
-- where the associated debit is an encumbrance.
--
-- Any other tables tying expenditure details to funds should
-- receive similar treatment. At this writing there are none.
--
UPDATE acq.lineitem_detail
SET fund = new_fund
WHERE
fund = roll_fund.old_fund -- this condition may be redundant
AND fund_debit in
(
SELECT id
FROM acq.fund_debit
WHERE
fund = roll_fund.old_fund
AND encumbrance
);
--
-- Move encumbrance debits from the old fund to the new fund
--
UPDATE acq.fund_debit
SET fund = new_fund
wHERE
fund = roll_fund.old_fund
AND encumbrance;
END IF;
--
-- Mark old fund as inactive, now that we've closed it
--
UPDATE acq.fund
SET active = FALSE
WHERE id = roll_fund.old_fund;
END LOOP;
END;
$$ LANGUAGE plpgsql;
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 $$
SELECT acq.rollover_funds_by_org_tree( $1, $2, $3, $4, FALSE );
$$ LANGUAGE SQL;
INSERT into config.org_unit_setting_type
(name, grp, label, description, datatype)
VALUES (
'acq.fund.allow_rollover_without_money',
'acq',
oils_i18n_gettext(
'acq.fund.allow_rollover_without_money',
'Allow funds to be rolled over without bringing the money along',
'coust',
'label'
),
oils_i18n_gettext(
'acq.fund.allow_rollover_without_money',
'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.',
'coust',
'description'
),
'bool'
);731 — vandelay item overlay
Type: schema
View SQL
-- 0731.schema.vandelay_item_overlay.sql
ALTER TABLE vandelay.import_item_attr_definition
ADD COLUMN internal_id TEXT;
ALTER TABLE vandelay.import_item
ADD COLUMN internal_id BIGINT;
INSERT INTO permission.perm_list ( id, code, description ) VALUES
( 536, 'IMPORT_OVERLAY_COPY', oils_i18n_gettext( 536,
'Allows a user to overlay copy data in MARC import', 'ppl', 'description'));
CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
DECLARE
attr_def BIGINT;
item_data vandelay.import_item%ROWTYPE;
IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
RETURN NEW;
END IF;
SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
INSERT INTO vandelay.import_item (
record,
definition,
owning_lib,
circ_lib,
call_number,
copy_number,
status,
location,
circulate,
deposit,
deposit_amount,
ref,
holdable,
price,
barcode,
circ_modifier,
circ_as_type,
alert_message,
pub_note,
priv_note,
internal_id,
opac_visible,
import_error,
error_detail
) VALUES (
NEW.id,
item_data.definition,
item_data.owning_lib,
item_data.circ_lib,
item_data.call_number,
item_data.copy_number,
item_data.status,
item_data.location,
item_data.circulate,
item_data.deposit,
item_data.deposit_amount,
item_data.ref,
item_data.holdable,
item_data.price,
item_data.barcode,
item_data.circ_modifier,
item_data.circ_as_type,
item_data.alert_message,
item_data.pub_note,
item_data.priv_note,
item_data.internal_id,
item_data.opac_visible,
item_data.import_error,
item_data.error_detail
);
END LOOP;
RETURN NULL;
END;
$func$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
DECLARE
owning_lib TEXT;
circ_lib TEXT;
call_number TEXT;
copy_number TEXT;
status TEXT;
location TEXT;
circulate TEXT;
deposit TEXT;
deposit_amount TEXT;
ref TEXT;
holdable TEXT;
price TEXT;
barcode TEXT;
circ_modifier TEXT;
circ_as_type TEXT;
alert_message TEXT;
opac_visible TEXT;
pub_note TEXT;
priv_note TEXT;
internal_id TEXT;
attr_def RECORD;
tmp_attr_set RECORD;
attr_set vandelay.import_item%ROWTYPE;
xpath TEXT;
tmp_str TEXT;
SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
IF FOUND THEN
attr_set.definition := attr_def.id;
-- Build the combined XPath
owning_lib :=
CASE
WHEN attr_def.owning_lib IS NULL THEN 'null()'
WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
END;
circ_lib :=
CASE
WHEN attr_def.circ_lib IS NULL THEN 'null()'
WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
END;
call_number :=
CASE
WHEN attr_def.call_number IS NULL THEN 'null()'
WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
END;
copy_number :=
CASE
WHEN attr_def.copy_number IS NULL THEN 'null()'
WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
END;
status :=
CASE
WHEN attr_def.status IS NULL THEN 'null()'
WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
END;
location :=
CASE
WHEN attr_def.location IS NULL THEN 'null()'
WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
END;
circulate :=
CASE
WHEN attr_def.circulate IS NULL THEN 'null()'
WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
END;
deposit :=
CASE
WHEN attr_def.deposit IS NULL THEN 'null()'
WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
END;
deposit_amount :=
CASE
WHEN attr_def.deposit_amount IS NULL THEN 'null()'
WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
END;
ref :=
CASE
WHEN attr_def.ref IS NULL THEN 'null()'
WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
END;
holdable :=
CASE
WHEN attr_def.holdable IS NULL THEN 'null()'
WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
END;
price :=
CASE
WHEN attr_def.price IS NULL THEN 'null()'
WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
END;
barcode :=
CASE
WHEN attr_def.barcode IS NULL THEN 'null()'
WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
END;
circ_modifier :=
CASE
WHEN attr_def.circ_modifier IS NULL THEN 'null()'
WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
END;
circ_as_type :=
CASE
WHEN attr_def.circ_as_type IS NULL THEN 'null()'
WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
END;
alert_message :=
CASE
WHEN attr_def.alert_message IS NULL THEN 'null()'
WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
END;
opac_visible :=
CASE
WHEN attr_def.opac_visible IS NULL THEN 'null()'
WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
END;
pub_note :=
CASE
WHEN attr_def.pub_note IS NULL THEN 'null()'
WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
END;
priv_note :=
CASE
WHEN attr_def.priv_note IS NULL THEN 'null()'
WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
END;
internal_id :=
CASE
WHEN attr_def.internal_id IS NULL THEN 'null()'
WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
END;
xpath :=
owning_lib || '|' ||
circ_lib || '|' ||
call_number || '|' ||
copy_number || '|' ||
status || '|' ||
location || '|' ||
circulate || '|' ||
deposit || '|' ||
deposit_amount || '|' ||
ref || '|' ||
holdable || '|' ||
price || '|' ||
barcode || '|' ||
circ_modifier || '|' ||
circ_as_type || '|' ||
alert_message || '|' ||
pub_note || '|' ||
priv_note || '|' ||
internal_id || '|' ||
opac_visible;
FOR tmp_attr_set IN
SELECT *
FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
LOOP
attr_set.import_error := NULL;
attr_set.error_detail := NULL;
attr_set.deposit_amount := NULL;
attr_set.copy_number := NULL;
attr_set.price := NULL;
IF tmp_attr_set.pr != '' THEN
tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
IF tmp_str = '' THEN
attr_set.import_error := 'import.item.invalid.price';
attr_set.error_detail := tmp_attr_set.pr; -- original value
RETURN NEXT attr_set; CONTINUE;
END IF;
attr_set.price := tmp_str::NUMERIC(8,2);
END IF;
IF tmp_attr_set.dep_amount != '' THEN
tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
IF tmp_str = '' THEN
attr_set.import_error := 'import.item.invalid.deposit_amount';
attr_set.error_detail := tmp_attr_set.dep_amount;
RETURN NEXT attr_set; CONTINUE;
END IF;
attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
END IF;
IF tmp_attr_set.cnum != '' THEN
tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
IF tmp_str = '' THEN
attr_set.import_error := 'import.item.invalid.copy_number';
attr_set.error_detail := tmp_attr_set.cnum;
RETURN NEXT attr_set; CONTINUE;
END IF;
attr_set.copy_number := tmp_str::INT;
END IF;
IF tmp_attr_set.ol != '' THEN
SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
IF NOT FOUND THEN
attr_set.import_error := 'import.item.invalid.owning_lib';
attr_set.error_detail := tmp_attr_set.ol;
RETURN NEXT attr_set; CONTINUE;
END IF;
END IF;
IF tmp_attr_set.clib != '' THEN
SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
IF NOT FOUND THEN
attr_set.import_error := 'import.item.invalid.circ_lib';
attr_set.error_detail := tmp_attr_set.clib;
RETURN NEXT attr_set; CONTINUE;
END IF;
END IF;
IF tmp_attr_set.cs != '' THEN
SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
IF NOT FOUND THEN
attr_set.import_error := 'import.item.invalid.status';
attr_set.error_detail := tmp_attr_set.cs;
RETURN NEXT attr_set; CONTINUE;
END IF;
END IF;
IF tmp_attr_set.circ_mod != '' THEN
SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
IF NOT FOUND THEN
attr_set.import_error := 'import.item.invalid.circ_modifier';
attr_set.error_detail := tmp_attr_set.circ_mod;
RETURN NEXT attr_set; CONTINUE;
END IF;
END IF;
IF tmp_attr_set.circ_as != '' THEN
SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
IF NOT FOUND THEN
attr_set.import_error := 'import.item.invalid.circ_as_type';
attr_set.error_detail := tmp_attr_set.circ_as;
RETURN NEXT attr_set; CONTINUE;
END IF;
END IF;
IF tmp_attr_set.cl != '' THEN
-- search up the org unit tree for a matching copy location
WITH RECURSIVE anscestor_depth AS (
SELECT ou.id,
out.depth AS depth,
ou.parent_ou
FROM actor.org_unit ou
JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
UNION ALL
SELECT ou.id,
out.depth,
ou.parent_ou
FROM actor.org_unit ou
JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
) SELECT cpl.id INTO attr_set.location
FROM anscestor_depth a
JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
ORDER BY a.depth DESC
LIMIT 1;
IF NOT FOUND THEN
attr_set.import_error := 'import.item.invalid.location';
attr_set.error_detail := tmp_attr_set.cs;
RETURN NEXT attr_set; CONTINUE;
END IF;
END IF;
attr_set.circulate :=
LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
attr_set.deposit :=
LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
attr_set.holdable :=
LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
attr_set.opac_visible :=
LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
attr_set.ref :=
LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
attr_set.call_number := tmp_attr_set.cn; -- TEXT
attr_set.barcode := tmp_attr_set.bc; -- TEXT,
attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
attr_set.pub_note := tmp_attr_set.note; -- TEXT,
attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
RETURN NEXT attr_set;
END LOOP;
END IF;
RETURN;
END;
$$ LANGUAGE PLPGSQL;732 — acq lineitem summary
Type: schema
View SQL
-- 0732.schema.acq-lineitem-summary.sql
CREATE OR REPLACE VIEW acq.lineitem_summary AS
SELECT
li.id AS lineitem,
(
SELECT COUNT(lid.id)
FROM acq.lineitem_detail lid
WHERE lineitem = li.id
) AS item_count,
(
SELECT COUNT(lid.id)
FROM acq.lineitem_detail lid
WHERE recv_time IS NOT NULL AND lineitem = li.id
) AS recv_count,
(
SELECT COUNT(lid.id)
FROM acq.lineitem_detail lid
WHERE cancel_reason IS NOT NULL AND lineitem = li.id
) AS cancel_count,
(
SELECT COUNT(lid.id)
FROM acq.lineitem_detail lid
JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
WHERE NOT debit.encumbrance AND lineitem = li.id
) AS invoice_count,
(
SELECT COUNT(DISTINCT(lid.id))
FROM acq.lineitem_detail lid
JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
WHERE lineitem = li.id
) AS claim_count,
(
SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
FROM acq.lineitem_detail lid
WHERE lid.cancel_reason IS NULL AND lineitem = li.id
) AS estimated_amount,
(
SELECT SUM(debit.amount)::NUMERIC(8,2)
FROM acq.lineitem_detail lid
JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
WHERE debit.encumbrance AND lineitem = li.id
) AS encumbrance_amount,
(
SELECT SUM(debit.amount)::NUMERIC(8,2)
FROM acq.lineitem_detail lid
JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
WHERE NOT debit.encumbrance AND lineitem = li.id
) AS paid_amount
FROM acq.lineitem AS li;733 — jedi with copies
Type: data
View SQL
-- XXX
-- Template update included here for reference only.
-- The stock JEDI template is not updated here (see WHERE clause)
-- We do update the environment, though, for easier local template
-- updating. No env fields are removed (that aren't otherwise replaced).
--
UPDATE action_trigger.event_definition SET template =
$$[%- USE date -%]
[%# start JEDI document
# Vendor specific kludges:
# BT - vendcode goes to NAD/BY *suffix* w/ 91 qualifier
# INGRAM - vendcode goes to NAD/BY *segment* w/ 91 qualifier (separately)
# BRODART - vendcode goes to FTX segment (lineitem level)
-%]
[%-
IF target.provider.edi_default.vendcode && target.provider.code == 'BRODART';
xtra_ftx = target.provider.edi_default.vendcode;
END;
-%]
[%- BLOCK big_block -%]
{
"recipient":"[% target.provider.san %]",
"sender":"[% target.ordering_agency.mailing_address.san %]",
"body": [{
"ORDERS":[ "order", {
"po_number":[% target.id %],
"date":"[% date.format(date.now, '%Y%m%d') %]",
"buyer":[
[% IF target.provider.edi_default.vendcode && (target.provider.code == 'BT' || target.provider.name.match('(?i)^BAKER & TAYLOR')) -%]
{"id-qualifier": 91, "id":"[% target.ordering_agency.mailing_address.san _ ' ' _ target.provider.edi_default.vendcode %]"}
[%- ELSIF target.provider.edi_default.vendcode && target.provider.code == 'INGRAM' -%]
{"id":"[% target.ordering_agency.mailing_address.san %]"},
{"id-qualifier": 91, "id":"[% target.provider.edi_default.vendcode %]"}
[%- ELSE -%]
{"id":"[% target.ordering_agency.mailing_address.san %]"}
[%- END -%]
],
"vendor":[
[%- # target.provider.name (target.provider.id) -%]
"[% target.provider.san %]",
{"id-qualifier": 92, "id":"[% target.provider.id %]"}
],
"currency":"[% target.provider.currency_type %]",
"items":[
[%- FOR li IN target.lineitems %]
{
"line_index":"[% li.id %]",
"identifiers":[ [%-# li.isbns = helpers.get_li_isbns(li.attributes) %]
[% FOR isbn IN helpers.get_li_isbns(li.attributes) -%]
[% IF isbn.length == 13 -%]
{"id-qualifier":"EN","id":"[% isbn %]"},
[% ELSE -%]
{"id-qualifier":"IB","id":"[% isbn %]"},
[%- END %]
[% END %]
{"id-qualifier":"IN","id":"[% li.id %]"}
],
"price":[% li.estimated_unit_price || '0.00' %],
"desc":[
{"BTI":"[% helpers.get_li_attr_jedi('title', '', li.attributes) %]"},
{"BPU":"[% helpers.get_li_attr_jedi('publisher', '', li.attributes) %]"},
{"BPD":"[% helpers.get_li_attr_jedi('pubdate', '', li.attributes) %]"},
{"BPH":"[% helpers.get_li_attr_jedi('pagination','', li.attributes) %]"}
],
[%- ftx_vals = [];
FOR note IN li.lineitem_notes;
NEXT UNLESS note.vendor_public == 't';
ftx_vals.push(note.value);
END;
IF xtra_ftx; ftx_vals.unshift(xtra_ftx); END;
IF ftx_vals.size == 0; ftx_vals.unshift(''); END; # BT needs FTX+LIN for every LI, even if it is an empty one
-%]
"free-text":[
[% FOR note IN ftx_vals -%] "[% note %]"[% UNLESS loop.last %], [% END %][% END %]
],
"quantity":[% li.lineitem_details.size %],
"copies" : [
[%- IF 1 -%]
[%- FOR lid IN li.lineitem_details;
fund = lid.fund.code;
item_type = lid.circ_modifier;
callnumber = lid.cn_label;
owning_lib = lid.owning_lib.shortname;
location = lid.location;
# when we have real copy data, treat it as authoritative
acp = lid.eg_copy_id;
IF acp;
item_type = acp.circ_modifier;
callnumber = acp.call_number.label;
location = acp.location.name;
END -%]
{ [%- IF fund %] "fund" : "[% fund %]",[% END -%]
[%- IF callnumber %] "call_number" : "[% callnumber %]", [% END -%]
[%- IF item_type %] "item_type" : "[% item_type %]", [% END -%]
[%- IF location %] "copy_location" : "[% location %]", [% END -%]
[%- IF owning_lib %] "owning_lib" : "[% owning_lib %]", [% END -%]
[%- #chomp %]"copy_id" : "[% lid.id %]" }[% ',' UNLESS loop.last %]
[% END -%]
[%- END -%]
]
}[% UNLESS loop.last %],[% END %]
[%-# TODO: lineitem details (later) -%]
[% END %]
],
"line_items":[% target.lineitems.size %]
}] [%# close ORDERS array %]
}] [%# close body array %]
}
[% END %]
[% tempo = PROCESS big_block; helpers.escape_json(tempo) %]
$$
WHERE id = 23 AND FALSE; -- DON'T PERFORM THE UPDATE
-- add copy-related fields to the environment if they're not already there.
DO $$
PERFORM 1
FROM action_trigger.environment
WHERE
event_def = 23 AND
path = 'lineitems.lineitem_details.owning_lib';
IF NOT FOUND THEN
INSERT INTO action_trigger.environment (event_def, path)
VALUES (23, 'lineitems.lineitem_details.owning_lib');
END IF;
PERFORM 1
FROM action_trigger.environment
WHERE
event_def = 23 AND
path = 'lineitems.lineitem_details.fund';
IF NOT FOUND THEN
INSERT INTO action_trigger.environment (event_def, path)
VALUES (23, 'lineitems.lineitem_details.fund');
END IF;
PERFORM 1
FROM action_trigger.environment
WHERE
event_def = 23 AND
path = 'lineitems.lineitem_details.location';
IF NOT FOUND THEN
INSERT INTO action_trigger.environment (event_def, path)
VALUES (23, 'lineitems.lineitem_details.location');
END IF;
PERFORM 1
FROM action_trigger.environment
WHERE
event_def = 23 AND
path = 'lineitems.lineitem_details.eg_copy_id.location';
IF NOT FOUND THEN
INSERT INTO action_trigger.environment (event_def, path)
VALUES (23, 'lineitems.lineitem_details.eg_copy_id.location');
END IF;
PERFORM 1
FROM action_trigger.environment
WHERE
event_def = 23 AND
path = 'lineitems.lineitem_details.eg_copy_id.call_number';
IF NOT FOUND THEN
INSERT INTO action_trigger.environment (event_def, path)
VALUES (23, 'lineitems.lineitem_details.eg_copy_id.call_number');
END IF;
-- remove redundant entry
DELETE FROM action_trigger.environment
WHERE event_def = 23 AND path = 'lineitems.lineitem_details';
END $$;734 — tpac holdable check
View SQL
-- Evergreen DB patch 0734.tpac_holdable_check.sql
--
-- check whether patch can be applied
CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
PERFORM 1
FROM
asset.copy acp
JOIN asset.call_number acn ON acp.call_number = acn.id
JOIN asset.copy_location acpl ON acp.location = acpl.id
JOIN config.copy_status ccs ON acp.status = ccs.id
WHERE
acn.record = rid
AND acp.holdable = true
AND acpl.holdable = true
AND ccs.holdable = true
AND acp.deleted = false
LIMIT 1;
IF FOUND THEN
RETURN true;
END IF;
RETURN FALSE;
END;
$f$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
PERFORM 1
FROM
asset.copy acp
JOIN asset.call_number acn ON acp.call_number = acn.id
JOIN asset.copy_location acpl ON acp.location = acpl.id
JOIN config.copy_status ccs ON acp.status = ccs.id
JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
WHERE
mmsm.metarecord = rid
AND acp.holdable = true
AND acpl.holdable = true
AND ccs.holdable = true
AND acp.deleted = false
LIMIT 1;
IF FOUND THEN
RETURN true;
END IF;
RETURN FALSE;
END;
$f$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION unapi.holdings_xml (
bid BIGINT,
ouid INT,
org TEXT,
depth INT DEFAULT NULL,
includes TEXT[] DEFAULT NULL::TEXT[],
slimit HSTORE DEFAULT NULL,
soffset HSTORE DEFAULT NULL,
include_xmlns BOOL DEFAULT TRUE,
pref_lib INT DEFAULT NULL
)
RETURNS XML AS $F$
SELECT XMLELEMENT(
name holdings,
XMLATTRIBUTES(
CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
(SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
),
XMLELEMENT(
name counts,
(SELECT XMLAGG(XMLELEMENT::XML) FROM (
SELECT XMLELEMENT(
name count,
XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
)::text
FROM asset.opac_ou_record_copy_count($2, $1)
UNION
SELECT XMLELEMENT(
name count,
XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
)::text
FROM asset.staff_ou_record_copy_count($2, $1)
UNION
SELECT XMLELEMENT(
name count,
XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
)::text
FROM asset.opac_ou_record_copy_count($9, $1)
ORDER BY 1
)x)
),
CASE
WHEN ('bmp' = ANY ($5)) THEN
XMLELEMENT(
name monograph_parts,
(SELECT XMLAGG(bmp) FROM (
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)
FROM biblio.monograph_part
WHERE record = $1
)x)
)
ELSE NULL
END,
XMLELEMENT(
name volumes,
(SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
-- Physical copies
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
FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9) AS y
UNION ALL
-- Located URIs
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
FROM evergreen.located_uris($1, $2, $9) AS uris
)x)
),
CASE WHEN ('ssub' = ANY ($5)) THEN
XMLELEMENT(
name subscriptions,
(SELECT XMLAGG(ssub) FROM (
SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
FROM serial.subscription
WHERE record_entry = $1
)x)
)
ELSE NULL END,
CASE WHEN ('acp' = ANY ($5)) THEN
XMLELEMENT(
name foreign_copies,
(SELECT XMLAGG(acp) FROM (
SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
FROM biblio.peer_bib_copy_map p
JOIN asset.copy c ON (p.target_copy = c.id)
WHERE NOT c.deleted AND p.peer_record = $1
LIMIT ($6 -> 'acp')::INT
OFFSET ($7 -> 'acp')::INT
)x)
)
ELSE NULL END
);
$F$ LANGUAGE SQL STABLE;735 — search filter group perms
Type: data
View SQL
-- Evergreen DB patch 0735.data.search_filter_group_perms.sql
--
-- check whether patch can be applied
INSERT INTO permission.perm_list ( id, code, description )
VALUES (
537,
'ADMIN_SEARCH_FILTER_GROUP',
oils_i18n_gettext(
537,
'Allows staff to manage search filter groups and entries',
'ppl',
'description'
)
),
(
538,
'VIEW_SEARCH_FILTER_GROUP',
oils_i18n_gettext(
538,
'Allows staff to view search filter groups and entries',
'ppl',
'description'
)
);737 — update ContainerCSV template
Type: data
View SQL
-- check whether patch can be applied
UPDATE action_trigger.event_definition
SET template =
$$
[%-
# target is the bookbag itself. The 'items' variable does not need to be in
# the environment because a special reactor will take care of filling it in.
FOR item IN items;
bibxml = helpers.unapi_bre(item.target_biblio_record_entry, {flesh => '{mra}'});
title = "";
FOR part IN bibxml.findnodes('//*[@tag="245"]/*[@code="a" or @code="b"]');
title = title _ part.textContent;
END;
author = bibxml.findnodes('//*[@tag="100"]/*[@code="a"]').textContent;
item_type = bibxml.findnodes('//*[local-name()="attributes"]/*[local-name()="field"][@name="item_type"]').getAttribute('coded-value');
pub_date = "";
FOR pdatum IN bibxml.findnodes('//*[@tag="260"]/*[@code="c"]');
IF pub_date ;
pub_date = pub_date _ ", " _ pdatum.textContent;
ELSE ;
pub_date = pdatum.textContent;
END;
END;
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";
END -%]
$$
WHERE name = 'Bookbag CSV';