From 4e6df79299c8fb45bcab705ab284582d067ef7f0 Mon Sep 17 00:00:00 2001 From: Ben Ostrowsky Date: Tue, 13 Sep 2011 17:55:19 +0000 Subject: [PATCH] Added new function: check_ou_depth --- sql/base/base.sql | 45 +++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 45 insertions(+), 0 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index d9c9e12..14d0dcd 100644 --- 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; -- 1.7.2.5