-- 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;