X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;fp=sql%2Fbase%2Fbase.sql;h=49bdae22c7b3b76774a55a4feeb9aeec52cd8f70;hp=e07575733887769ba476dfe8e1586d560e4114c3;hb=800369fb2cc71890cc5742c753a69122ee6e3073;hpb=3004a3ef40abe502b859e22d6bb45f51ead594f5

diff --git a/sql/base/base.sql b/sql/base/base.sql
index e075757..49bdae2 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -2561,3 +2561,24 @@ BEGIN
 END
 $func$  LANGUAGE plpgsql;
 
+DROP FUNCTION migration_tools.find_actor_stat_cat(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER);
+CREATE OR REPLACE FUNCTION migration_tools.find_actor_stat_cat(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER)
+  RETURNS INTEGER AS
+$func$
+DECLARE
+    f INTEGER;
+BEGIN
+    EXECUTE 'SELECT COALESCE((SELECT id FROM ' || migration_schema || '.actor_stat_cat WHERE owner IN (SELECT id FROM actor.org_unit_ancestors_distance(' || owner_org_id || ') WHERE distance <= ' || depth || ') AND name ILIKE ''' || stat_cat_name || '''),-1)' INTO f;
+
+    IF (f = -1) THEN
+        EXECUTE 'SELECT COALESCE((SELECT id FROM ' || migration_schema || '.actor_stat_cat WHERE owner IN (SELECT id FROM actor.org_unit_ancestors_distance(' || owner_org_id || ') WHERE distance <= ' || depth || ') AND name ILIKE ''' || stat_cat_name || '''),-1)' INTO f;
+    END IF;
+
+    IF (f = -1) THEN
+        RAISE 'The stat cat you was not found.';
+    ELSE
+        RETURN f;
+    END IF;
+
+END
+$func$  LANGUAGE plpgsql;