From 7eb372db1e9b60cf6f25eec7e1233f6f51559be8 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Wed, 28 Feb 2018 12:59:01 -0500 Subject: [PATCH] adding x_shelf and x_org to the shelving location init table --- mig-sql/init/020_common_tables.sql | 2 ++ sql/base/base.sql | 26 ++++++++++++++++++++++++++ 2 files changed, 28 insertions(+), 0 deletions(-) diff --git a/mig-sql/init/020_common_tables.sql b/mig-sql/init/020_common_tables.sql index a8e6b98..c414c17 100644 --- a/mig-sql/init/020_common_tables.sql +++ b/mig-sql/init/020_common_tables.sql @@ -8,6 +8,8 @@ CREATE TABLE map_create_shelving_location ( ,l_circulate TEXT ,l_note TEXT ,x_migrate BOOLEAN NOT NULL DEFAULT TRUE + ,x_shelf INTEGER + ,x_org INTEGER ) INHERITS (asset_copy_location); INSERT INTO gsheet_tracked_table diff --git a/sql/base/base.sql b/sql/base/base.sql index 40a1e8b..0629596 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -3133,3 +3133,29 @@ BEGIN END $$ LANGUAGE plpgsql; +-- may remove later but testing using this with new migration scripts and not loading acls until go live + +DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$ +DECLARE + return_id INT; + d INT; + cur_id INT; +BEGIN + SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id); + WHILE d >= 0 + LOOP + SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d); + + SELECT INTO return_id id FROM + (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x + WHERE owning_lib = cur_id AND name ILIKE shelf_name; + IF return_id IS NOT NULL THEN + RETURN return_id; + END IF; + d := d - 1; + END LOOP; + + RETURN NULL; +END +$$ LANGUAGE plpgsql; -- 1.7.2.5