From 0b3af908a2c5373695eab11ddfcad525dd3a4553 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Mon, 12 Jan 2015 08:44:28 -0500 Subject: [PATCH] adding script to correct copy location ownership --- sql/correct_copy_locations.sql | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) create mode 100644 sql/correct_copy_locations.sql diff --git a/sql/correct_copy_locations.sql b/sql/correct_copy_locations.sql new file mode 100644 index 0000000..108998e --- /dev/null +++ b/sql/correct_copy_locations.sql @@ -0,0 +1,24 @@ +-- credit to Thomas Berezansky for this function + +DO $FUNC$ + DECLARE + asset_copy asset.copy%ROWTYPE; + copy_owning_lib INT; + new_copy_location INT; + BEGIN + FOR asset_copy IN SELECT acp.* + 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 + WHERE acn.owning_lib != acpl.owning_lib AND acp.circ_lib != acpl.owning_lib AND acp.circ_lib IN (SELECT id FROM actor.org_unit WHERE parent_ou IN (SELECT id FROM actor.org_unit WHERE shortname = 'HOU')) LOOP + SELECT INTO copy_owning_lib owning_lib FROM asset.call_number WHERE id = asset_copy.call_number; + SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN (SELECT * FROM actor.org_unit_ancestors_distance(copy_owning_lib) UNION SELECT * FROM actor.org_unit_ancestors_distance(asset_copy.circ_lib)) aouad ON acpl.owning_lib = aouad.id WHERE name = (SELECT name FROM asset.copy_location WHERE id = asset_copy.location) ORDER BY distance LIMIT 1; + IF NOT FOUND OR new_copy_location = asset_copy.location THEN + CONTINUE; + END IF; + RAISE NOTICE 'OLD % NEW % COPY %', asset_copy.location, new_copy_location, asset_copy.id; + UPDATE asset.copy SET location = new_copy_location WHERE id = asset_copy.id; + END LOOP; + END; +$FUNC$; + -- 2.11.0