--- /dev/null
+--Upgrade Script for 2.2 to 2.3.0
+BEGIN;
+INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.0', :eg_version);
+-- Evergreen DB patch 0703.tpac_value_maps.sql
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('0703', :eg_version);
+
+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;
+
+
+
+SELECT evergreen.upgrade_deps_block_check('0712', :eg_version);
+
+-- 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)
+);
+
+
+SELECT evergreen.upgrade_deps_block_check('0713', :eg_version);
+
+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'
+);
+
+
+
+SELECT evergreen.upgrade_deps_block_check('0714', :eg_version);
+
+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'
+ );
+
+-- Evergreen DB patch 0718.data.add-to-permanent-bookbag.sql
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('0718', :eg_version);
+
+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'
+);
+
+
+SELECT evergreen.upgrade_deps_block_check('0719', :eg_version);
+
+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'
+ )
+ );
+
+
+SELECT evergreen.upgrade_deps_block_check('0720', :eg_version);
+
+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;
+BEGIN
+ -- 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;
+BEGIN
+ -- 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;
+
+
+
+
+SELECT evergreen.upgrade_deps_block_check('0721', :eg_version);
+
+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;
+BEGIN
+ 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;
+
+
+-- 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
+SELECT evergreen.upgrade_deps_block_check('0727', :eg_version);
+
+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';
+
+
+SELECT evergreen.upgrade_deps_block_check('0728', :eg_version);
+
+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
+ );
+
+
+-- Evergreen DB patch 0729.vr_format_value_maps.sql
+--
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('0729', :eg_version);
+
+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;
+BEGIN
+ -- 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);
+
+
+
+SELECT evergreen.upgrade_deps_block_check('0730', :eg_version);
+
+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;
+--
+BEGIN
+ --
+ -- 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
+ BEGIN
+ 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;
+--
+BEGIN
+ --
+ -- 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'
+ );
+
+-- 0731.schema.vandelay_item_overlay.sql
+
+SELECT evergreen.upgrade_deps_block_check('0731', :eg_version);
+
+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;
+BEGIN
+
+ 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;
+
+BEGIN
+
+ 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;
+
+
+
+-- 0732.schema.acq-lineitem-summary.sql
+
+SELECT evergreen.upgrade_deps_block_check('0732', :eg_version);
+
+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;
+
+
+
+-- 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).
+--
+
+
+SELECT evergreen.upgrade_deps_block_check('0733', :eg_version);
+
+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 $$
+BEGIN
+ 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 $$;
+
+
+-- Evergreen DB patch 0734.tpac_holdable_check.sql
+--
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('0734', :eg_version);
+
+CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
+BEGIN
+ 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$
+BEGIN
+ 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;
+
+-- Evergreen DB patch 0735.data.search_filter_group_perms.sql
+--
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('0735', :eg_version);
+
+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'
+ )
+
+ );
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('0737', :eg_version);
+
+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';
+
+COMMIT;
projects / transitory.git / blobdiff