Added new function: check_ou_depth

diff --git a/sql/base/base.sql b/sql/base/base.sql
index d9c9e12..14d0dcd 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -1185,3 +1185,48 @@ CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETUR
   
 $$ LANGUAGE PLPERLU STABLE;
 
+
+CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
+
+DECLARE
+  ou  INT;
+       org_unit_depth INT;
+       ou_parent INT;
+       parent_depth INT;
+  errors_found BOOLEAN;
+       ou_shortname TEXT;
+       parent_shortname TEXT;
+       ou_type_name TEXT;
+       parent_type TEXT;
+
+BEGIN
+
+       errors_found := FALSE;
+
+  FOR ou IN EXECUTE ('SELECT DISTINCT id FROM actor.org_unit ORDER BY 1;') LOOP
+
+               SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
+               SELECT depth FROM actor.org_unit_type WHERE id = ou INTO org_unit_depth;
+               SELECT depth FROM actor.org_unit_type WHERE id = ou_parent INTO parent_depth;
+
+               IF ou_parent IS NOT NULL AND org_unit_depth - parent_depth <> 1 THEN
+                       SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
+                       SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
+                       SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
+                       SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
+                       RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', 
+                               ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
+                       errors_found := TRUE;
+               END IF;
+
+  END LOOP;
+
+       IF NOT errors_found THEN
+               RAISE INFO 'No errors found.';
+       END IF;
+
+  RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;