From 73ef5cfa2d498efcff2530d111943972ef023a06 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 12 Jul 2011 17:39:37 -0400 Subject: [PATCH] Vandelay item import copy location inheritance When importing items that have a copy location configured and no such location exists at the copy's owning/circ lib, Vandelay will now use copy locations from ancestor org units as appropriate. Signed-off-by: Bill Erickson Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/999.functions.global.sql | 27 +- ...ema.vandelay-item-import-copy-loc-ancestors.sql | 292 +++++++++++++++++++++ 3 files changed, 316 insertions(+), 5 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0577.schema.vandelay-item-import-copy-loc-ancestors.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 10038a95fc..7757c97bea 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -86,7 +86,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0576', :eg_version); -- phasefx/gmcharlt +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0577', :eg_version); -- berick CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index f0d95e5c82..a75a635392 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1686,10 +1686,29 @@ BEGIN SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT - SELECT id INTO attr_set.location - FROM asset.copy_location - WHERE LOWER(name) = LOWER(tmp_attr_set.cl) - AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT + + -- 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; attr_set.circulate := LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1') diff --git a/Open-ILS/src/sql/Pg/upgrade/0577.schema.vandelay-item-import-copy-loc-ancestors.sql b/Open-ILS/src/sql/Pg/upgrade/0577.schema.vandelay-item-import-copy-loc-ancestors.sql new file mode 100644 index 0000000000..64a6c763ba --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0577.schema.vandelay-item-import-copy-loc-ancestors.sql @@ -0,0 +1,292 @@ +-- Evergreen DB patch 0577.schema.vandelay-item-import-copy-loc-ancestors.sql +-- +-- Ingest items copy location inheritance +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0577', :eg_version); -- berick + +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; + + attr_def RECORD; + tmp_attr_set RECORD; + attr_set vandelay.import_item%ROWTYPE; + + xpath 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; + + + 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 || '|' || + opac_visible; + + -- RAISE NOTICE 'XPath: %', xpath; + + 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, opac_vis TEXT ) + LOOP + + tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g'); + tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g'); + + tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' ); + tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' ); + + SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT + SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT + SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT + + + -- 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; + + 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.copy_number := tmp_attr_set.cnum::INT; -- INT, + attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2), + attr_set.price := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2), + + attr_set.call_number := tmp_attr_set.cn; -- TEXT + attr_set.barcode := tmp_attr_set.bc; -- TEXT, + attr_set.circ_modifier := tmp_attr_set.circ_mod; -- TEXT, + attr_set.circ_as_type := tmp_attr_set.circ_as; -- 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, + + RETURN NEXT attr_set; + + END LOOP; + + END IF; + + RETURN; + +END; +$$ LANGUAGE PLPGSQL; + + +COMMIT; -- 2.11.0