From bfceae7dfe2d8bb3dcd37e56b541a69b9a99738c Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 8 Aug 2011 15:05:47 -0400 Subject: [PATCH] Granular invalid data detection for Vandelay item import When an import item contains any data for a field, the code now verifies the data is valid for the selected field. When the data is not valid, we capture information on which field is invalid and what data was used for the field (import_error, error_detail) so it can be reported in the interface. This adds new import_errors: import.item.invalid.status import.item.invalid.price import.item.invalid.deposit_amount import.item.invalid.owning_lib import.item.invalid.circ_lib import.item.invalid.copy_number import.item.invalid.circ_as_type Signed-off-by: Bill Erickson Signed-off-by: Lebbeous Fogle-Weekley --- .../lib/OpenILS/Application/Vandelay.pm | 21 +- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 15 + Open-ILS/src/sql/Pg/999.functions.global.sql | 161 +++++-- ...hema.vandelay-item-import-error-detail.sql | 442 ++++++++++++++++++ 5 files changed, 581 insertions(+), 60 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0596.schema.vandelay-item-import-error-detail.sql diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm index 78c6f3832c..4a3d345781 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Vandelay.pm @@ -1460,7 +1460,10 @@ sub import_record_asset_list_impl { for my $rec_id (@$rec_ids) { my $rec = $roe->retrieve_vandelay_queued_bib_record($rec_id); - my $item_ids = $roe->search_vandelay_import_item({record => $rec->id}, {idlist=>1}); + my $item_ids = $roe->search_vandelay_import_item( + {record => $rec->id, import_error => undef}, + {idlist=>1} + ); for my $item_id (@$item_ids) { my $e = new_editor(requestor => $requestor, xact => 1); @@ -1506,22 +1509,8 @@ sub import_record_asset_list_impl { $copy->circ_modifier($item->circ_modifier); # -------------------------------------------------------------------------------- - # see if a valid circ_modifier was provided + # Check for dupe barcode # -------------------------------------------------------------------------------- - if($copy->circ_modifier and not $e->search_config_circ_modifier({code=>$item->circ_modifier})->[0]) { - $$report_args{evt} = $e->die_event; - $$report_args{import_error} = 'import.item.invalid.circ_modifier'; - respond_with_status($report_args); - next; - } - - if($copy->location and not $e->retrieve_asset_copy_location($copy->location)) { - $$report_args{evt} = $e->die_event; - $$report_args{import_error} = 'import.item.invalid.location'; - respond_with_status($report_args); - next; - } - if($evt = OpenILS::Application::Cat::AssetCommon->create_copy($e, $vol, $copy)) { $$report_args{evt} = $evt; $$report_args{import_error} = 'import.item.duplicate.barcode' diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index b386c5b5c1..8276096a43 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 ('0595', :eg_version); -- phasefx/tsbere +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0596', :eg_version); -- berick/senator CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 01416a0338..1720b8c9be 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -8808,6 +8808,21 @@ INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'import.xml.mal INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.xml.malformed', oils_i18n_gettext('overlay.xml.malformed', 'Malformed record cause Overlay failure', 'vie', 'description') ); INSERT INTO vandelay.import_error ( code, description ) VALUES ( 'overlay.record.quality', oils_i18n_gettext('overlay.record.quality', 'New record had insufficient quality', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.status', oils_i18n_gettext('import.item.invalid.status', 'Invalid value for "status"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.price', oils_i18n_gettext('import.item.invalid.price', 'Invalid value for "price"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.deposit_amount', oils_i18n_gettext('import.item.invalid.deposit_amount', 'Invalid value for "deposit_amount"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.owning_lib', oils_i18n_gettext('import.item.invalid.owning_lib', 'Invalid value for "owning_lib"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.circ_lib', oils_i18n_gettext('import.item.invalid.circ_lib', 'Invalid value for "circ_lib"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.copy_number', oils_i18n_gettext('import.item.invalid.copy_number', 'Invalid value for "copy_number"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.circ_as_type', oils_i18n_gettext('import.item.invalid.circ_as_type', 'Invalid value for "circ_as_type"', 'vie', 'description') ); + INSERT INTO config.org_unit_setting_type ( name, label, description, datatype ) VALUES ( 'ui.cat.volume_copy_editor.horizontal', oils_i18n_gettext( diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index f44dd17416..a58f922580 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1501,6 +1501,7 @@ DECLARE attr_set vandelay.import_item%ROWTYPE; xpath TEXT; + tmp_str TEXT; BEGIN @@ -1666,8 +1667,6 @@ BEGIN 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 ) @@ -1676,39 +1675,117 @@ BEGIN 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.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') @@ -1730,14 +1807,8 @@ BEGIN 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, @@ -1788,7 +1859,9 @@ BEGIN alert_message, pub_note, priv_note, - opac_visible + opac_visible, + import_error, + error_detail ) VALUES ( NEW.id, item_data.definition, @@ -1810,7 +1883,9 @@ BEGIN item_data.alert_message, item_data.pub_note, item_data.priv_note, - item_data.opac_visible + item_data.opac_visible, + item_data.import_error, + item_data.error_detail ); END LOOP; diff --git a/Open-ILS/src/sql/Pg/upgrade/0596.schema.vandelay-item-import-error-detail.sql b/Open-ILS/src/sql/Pg/upgrade/0596.schema.vandelay-item-import-error-detail.sql new file mode 100644 index 0000000000..e7823dc52b --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0596.schema.vandelay-item-import-error-detail.sql @@ -0,0 +1,442 @@ +-- Evergreen DB patch 0596.schema.vandelay-item-import-error-detail.sql +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0596', :eg_version); + +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.status', oils_i18n_gettext('import.item.invalid.status', 'Invalid value for "status"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.price', oils_i18n_gettext('import.item.invalid.price', 'Invalid value for "price"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.deposit_amount', oils_i18n_gettext('import.item.invalid.deposit_amount', 'Invalid value for "deposit_amount"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.owning_lib', oils_i18n_gettext('import.item.invalid.owning_lib', 'Invalid value for "owning_lib"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.circ_lib', oils_i18n_gettext('import.item.invalid.circ_lib', 'Invalid value for "circ_lib"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.copy_number', oils_i18n_gettext('import.item.invalid.copy_number', 'Invalid value for "copy_number"', 'vie', 'description') ); +INSERT INTO vandelay.import_error ( code, description ) VALUES ( + 'import.item.invalid.circ_as_type', oils_i18n_gettext('import.item.invalid.circ_as_type', 'Invalid value for "circ_as_type"', 'vie', 'description') ); + +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; + 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; + + + 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; + + 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 + + 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, + + RETURN NEXT attr_set; + + END LOOP; + + END IF; + + RETURN; + +END; +$$ LANGUAGE PLPGSQL; + +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, + 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.opac_visible, + item_data.import_error, + item_data.error_detail + ); + END LOOP; + + RETURN NULL; +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.43.2