From: Rogan Hamby <rhamby@esilibrary.com>
Date: Tue, 27 Feb 2018 20:59:12 +0000 (-0500)
Subject: find shelf function
X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=8a1bd596dff08951e274da83276e9a9f2506b6bb

find shelf function
---

diff --git a/sql/base/base.sql b/sql/base/base.sql
index e734e45..4cc945d 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -3108,3 +3108,26 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TE
     END;
 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 
+-- convenience functions for adding shelving locations
+DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.find_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 asset.copy_location 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;
+