BEGIN; SELECT evergreen.upgrade_deps_block_check('0912', :eg_version); ALTER TABLE asset.copy_location ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT FALSE; CREATE OR REPLACE RULE protect_copy_location_delete AS ON DELETE TO asset.copy_location DO INSTEAD ( UPDATE asset.copy_location SET deleted = TRUE WHERE OLD.id = asset.copy_location.id; UPDATE acq.lineitem_detail SET location = NULL WHERE location = OLD.id; DELETE FROM asset.copy_location_order WHERE location = OLD.id; DELETE FROM asset.copy_location_group_map WHERE location = OLD.id; DELETE FROM config.circ_limit_set_copy_loc_map WHERE copy_loc = OLD.id; ); ALTER TABLE asset.copy_location DROP CONSTRAINT acl_name_once_per_lib; CREATE UNIQUE INDEX acl_name_once_per_lib ON asset.copy_location (name, owning_lib) WHERE deleted = FALSE OR deleted IS FALSE; CREATE OR REPLACE FUNCTION asset.acp_location_fixer() RETURNS TRIGGER AS $$ DECLARE new_copy_location INT; BEGIN IF (TG_OP = 'UPDATE') THEN IF NEW.location = OLD.location AND NEW.call_number = OLD.call_number AND NEW.circ_lib = OLD.circ_lib THEN RETURN NEW; END IF; END IF; SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance((SELECT owning_lib FROM asset.call_number WHERE id = NEW.call_number)) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1; IF new_copy_location IS NULL THEN SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance(NEW.circ_lib) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1; END IF; IF new_copy_location IS NOT NULL THEN NEW.location = new_copy_location; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY SELECT ans.depth, ans.id, COUNT( cp.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) GROUP BY 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; RETURN; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY SELECT -1, ans.id, COUNT( cp.id ), SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END), trans FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted) JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted) JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted) GROUP BY 1,2,6; IF NOT FOUND THEN RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; END IF; END LOOP; RETURN; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) 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 AND acpl.deleted = false AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top())))) 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, ou INT DEFAULT NULL) 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 AND acpl.deleted = false AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top())))) LIMIT 1; IF FOUND THEN RETURN true; END IF; RETURN FALSE; END; $f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION asset.refresh_opac_visible_copies_mat_view () RETURNS VOID AS $$ TRUNCATE TABLE asset.opac_visible_copies; INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record) SELECT cp.id, cp.circ_lib, cn.record FROM asset.copy cp JOIN asset.call_number cn ON (cn.id = cp.call_number) JOIN actor.org_unit a ON (cp.circ_lib = a.id) JOIN asset.copy_location cl ON (cp.location = cl.id) JOIN config.copy_status cs ON (cp.status = cs.id) JOIN biblio.record_entry b ON (cn.record = b.id) WHERE NOT cp.deleted AND NOT cl.deleted AND NOT cn.deleted AND NOT b.deleted AND cs.opac_visible AND cl.opac_visible AND cp.opac_visible AND a.opac_visible UNION SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record FROM asset.copy cp JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id) JOIN actor.org_unit a ON (cp.circ_lib = a.id) JOIN asset.copy_location cl ON (cp.location = cl.id) JOIN config.copy_status cs ON (cp.status = cs.id) WHERE NOT cp.deleted AND NOT cl.deleted AND cs.opac_visible AND cl.opac_visible AND cp.opac_visible AND a.opac_visible; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$ DECLARE add_front TEXT; add_back TEXT; add_base_query TEXT; add_peer_query TEXT; remove_query TEXT; do_add BOOLEAN := false; do_remove BOOLEAN := false; BEGIN add_base_query := $$ SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status FROM asset.copy cp JOIN asset.call_number cn ON (cn.id = cp.call_number) JOIN actor.org_unit a ON (cp.circ_lib = a.id) JOIN asset.copy_location cl ON (cp.location = cl.id) JOIN config.copy_status cs ON (cp.status = cs.id) JOIN biblio.record_entry b ON (cn.record = b.id) WHERE NOT cp.deleted AND NOT cl.deleted AND NOT cn.deleted AND NOT b.deleted AND cs.opac_visible AND cl.opac_visible AND cp.opac_visible AND a.opac_visible $$; add_peer_query := $$ SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status FROM asset.copy cp JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id) JOIN actor.org_unit a ON (cp.circ_lib = a.id) JOIN asset.copy_location cl ON (cp.location = cl.id) JOIN config.copy_status cs ON (cp.status = cs.id) WHERE NOT cp.deleted AND NOT cl.deleted AND cs.opac_visible AND cl.opac_visible AND cp.opac_visible AND a.opac_visible $$; add_front := $$ INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record) SELECT DISTINCT ON (id, record) id, circ_lib, record FROM ( $$; add_back := $$ ) AS x $$; remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$; IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN IF TG_OP = 'INSERT' THEN add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.peer_record = ' || NEW.peer_record; EXECUTE add_front || add_peer_query || add_back; RETURN NEW; ELSE remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';'; EXECUTE remove_query; RETURN OLD; END IF; END IF; IF TG_OP = 'INSERT' THEN IF TG_TABLE_NAME IN ('copy', 'unit') THEN add_base_query := add_base_query || ' AND cp.id = ' || NEW.id; EXECUTE add_front || add_base_query || add_back; END IF; RETURN NEW; END IF; -- handle items first, since with circulation activity -- their statuses change frequently IF TG_TABLE_NAME IN ('copy', 'unit') THEN IF OLD.location <> NEW.location OR OLD.call_number <> NEW.call_number OR OLD.status <> NEW.status OR OLD.circ_lib <> NEW.circ_lib THEN -- any of these could change visibility, but -- we'll save some queries and not try to calculate -- the change directly do_remove := true; do_add := true; ELSE IF OLD.deleted <> NEW.deleted THEN IF NEW.deleted THEN do_remove := true; ELSE do_add := true; END IF; END IF; IF OLD.opac_visible <> NEW.opac_visible THEN IF OLD.opac_visible THEN do_remove := true; ELSIF NOT do_remove THEN -- handle edge case where deleted item -- is also marked opac_visible do_add := true; END IF; END IF; END IF; IF do_remove THEN DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id; END IF; IF do_add THEN add_base_query := add_base_query || ' AND cp.id = ' || NEW.id; add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id; EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; END IF; RETURN NEW; END IF; IF TG_TABLE_NAME IN ('call_number', 'copy_location', 'record_entry') THEN -- these have a 'deleted' column IF OLD.deleted AND NEW.deleted THEN -- do nothing RETURN NEW; ELSIF NEW.deleted THEN -- remove rows IF TG_TABLE_NAME = 'call_number' THEN DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id); ELSIF TG_TABLE_NAME = 'copy_location' THEN DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE location = NEW.id); ELSIF TG_TABLE_NAME = 'record_entry' THEN DELETE FROM asset.opac_visible_copies WHERE record = NEW.id; END IF; RETURN NEW; ELSIF OLD.deleted THEN -- add rows IF TG_TABLE_NAME = 'call_number' THEN add_base_query := add_base_query || ' AND cn.id = ' || NEW.id; EXECUTE add_front || add_base_query || add_back; ELSIF TG_TABLE_NAME = 'copy_location' THEN add_base_query := add_base_query || 'AND cl.id = ' || NEW.id; EXECUTE add_front || add_base_query || add_back; ELSIF TG_TABLE_NAME = 'record_entry' THEN add_base_query := add_base_query || ' AND cn.record = ' || NEW.id; add_peer_query := add_peer_query || ' AND pbcm.peer_record = ' || NEW.id; EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; END IF; RETURN NEW; END IF; END IF; IF TG_TABLE_NAME = 'call_number' THEN IF OLD.record <> NEW.record THEN -- call number is linked to different bib remove_query := remove_query || 'call_number = ' || NEW.id || ');'; EXECUTE remove_query; add_base_query := add_base_query || ' AND cn.id = ' || NEW.id; EXECUTE add_front || add_base_query || add_back; END IF; RETURN NEW; END IF; IF TG_TABLE_NAME IN ('record_entry') THEN RETURN NEW; -- don't have 'opac_visible' END IF; -- actor.org_unit, asset.copy_location, asset.copy_status IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing RETURN NEW; ELSIF NEW.opac_visible THEN -- add rows IF TG_TABLE_NAME = 'org_unit' THEN add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id; add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id; ELSIF TG_TABLE_NAME = 'copy_location' THEN add_base_query := add_base_query || ' AND cp.location = ' || NEW.id; add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id; ELSIF TG_TABLE_NAME = 'copy_status' THEN add_base_query := add_base_query || ' AND cp.status = ' || NEW.id; add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id; END IF; EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back; ELSE -- delete rows IF TG_TABLE_NAME = 'org_unit' THEN remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';'; ELSIF TG_TABLE_NAME = 'copy_location' THEN remove_query := remove_query || 'location = ' || NEW.id || ');'; ELSIF TG_TABLE_NAME = 'copy_status' THEN remove_query := remove_query || 'status = ' || NEW.id || ');'; END IF; EXECUTE remove_query; END IF; RETURN NEW; END; $func$ LANGUAGE PLPGSQL; -- updated copy location validity test to disallow deleted locations 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; attr_set.circ_modifier := NULL; attr_set.location := NULL; attr_set.barcode := NULL; attr_set.call_number := 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 COALESCE(tmp_attr_set.circ_mod, '') = '' THEN -- no circ mod defined, see if we should apply a default SELECT INTO attr_set.circ_modifier TRIM(BOTH '"' FROM value) FROM actor.org_unit_ancestor_setting( 'vandelay.item.circ_modifier.default', attr_set.owning_lib ); -- make sure the value from the org setting is still valid PERFORM 1 FROM config.circ_modifier WHERE code = attr_set.circ_modifier; 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; ELSE 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 COALESCE(tmp_attr_set.cl, '') = '' THEN -- no location specified, see if we should apply a default SELECT INTO attr_set.location TRIM(BOTH '"' FROM value) FROM actor.org_unit_ancestor_setting( 'vandelay.item.copy_location.default', attr_set.owning_lib ); -- make sure the value from the org setting is still valid PERFORM 1 FROM asset.copy_location WHERE id = attr_set.location AND NOT deleted; 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; ELSE -- 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) AND NOT cpl.deleted 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; COMMIT;