quasi experimental stat cat and entry functions

diff --git a/sql/base/base.sql b/sql/base/base.sql
index f7744f1..e075757 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -2484,3 +2484,80 @@ CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TE
     END;
 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 
+DROP FUNCTION migration_tools.add_actor_stat_cat(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER, required_flag BOOLEAN, user_summary BOOLEAN);
+CREATE OR REPLACE FUNCTION migration_tools.add_actor_stat_cat(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER, required_flag BOOLEAN, user_summary BOOLEAN)
+  RETURNS bool AS
+$func$
+DECLARE
+    f INTEGER;
+    aou_id 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 RETURN FALSE;
+    END IF;
+
+    IF EXISTS (SELECT 1 FROM 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)
+        THEN RETURN FALSE;
+    END IF;
+
+    SELECT id FROM actor.org_unit_ancestors_distance(owner_org_id) WHERE distance = depth INTO aou_id;
+
+    EXECUTE 'INSERT INTO ' || migration_schema || '.actor_stat_cat (owner,name,required,usr_summary) VALUES (' || aou_id || ',''' || stat_cat_name || ''',' || required_flag || ',' || user_summary || ');';
+    RETURN TRUE;
+
+END
+$func$  LANGUAGE plpgsql;
+
+DROP FUNCTION migration_tools.add_actor_stat_cat(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER);
+CREATE OR REPLACE FUNCTION migration_tools.add_actor_stat_cat(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER)
+  RETURNS bool AS
+$func$
+DECLARE
+    result BOOLEAN;
+BEGIN
+
+    SELECT INTO result * FROM migration_tools.add_actor_stat_cat(migration_schema,owner_org_id,stat_cat_name,depth,FALSE,FALSE);
+    RETURN result;
+
+END
+$func$  LANGUAGE plpgsql;
+
+DROP FUNCTION migration_tools.add_actor_stat_cat_entry(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER, entry_value TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.add_actor_stat_cat_entry(migration_schema TEXT, owner_org_id INTEGER, stat_cat_name TEXT, depth INTEGER, entry_value TEXT)
+  RETURNS TEXT AS
+$func$
+DECLARE
+    f INTEGER;
+    aou_id INTEGER;
+    stat_cat_id INTEGER;
+BEGIN
+
+    CREATE TEMP TABLE stat_cat_list (id INTEGER, owner INTEGER, name TEXT) ON COMMIT DROP;
+
+    INSERT INTO stat_cat_list (id,owner,name)
+        (SELECT id,owner,name FROM actor.stat_cat WHERE owner IN (SELECT id FROM actor.org_unit_ancestors(owner_org_id)) AND name ILIKE stat_cat_name);
+    EXECUTE 'INSERT INTO stat_cat_list (id,owner,name) 
+        (SELECT id,owner,name FROM ' || migration_schema || '.actor_stat_cat WHERE owner IN (SELECT id FROM actor.org_unit_ancestors(' || owner_org_id || ')) AND name ILIKE ''' || stat_cat_name || ''');';
+    IF EXISTS (SELECT 1 FROM stat_cat_list WHERE id IS NOT NULL limit 1) THEN
+        EXECUTE 'SELECT id FROM stat_cat_list WHERE owner = (SELECT MAX(id) FROM actor.org_unit_ancestors_distance(' || owner_org_id || ') WHERE distance >= ' || depth || ') AND name ILIKE ''' || stat_cat_name || ''' ' INTO stat_cat_id;
+    ELSE
+        RETURN 'No matching Actor Stat Cat exists.';
+    END IF;
+
+    EXECUTE 'SELECT COALESCE((SELECT id FROM ' || migration_schema || '.actor_stat_cat_entry WHERE owner IN (SELECT id FROM actor.org_unit_ancestors_distance(' || owner_org_id || ') WHERE distance >= ' || depth || ') AND value ILIKE ''' || entry_value || ''' AND stat_cat = ' || stat_cat_id || '),-1)' INTO f;
+
+
+    SELECT id FROM actor.org_unit_ancestors_distance(owner_org_id) WHERE distance = depth INTO aou_id;
+
+    IF (f > -1)
+        THEN RETURN 'Matching Actor Stat Cat Entry already exists.';
+    ELSE
+        EXECUTE 'INSERT INTO ' || migration_schema || '.actor_stat_cat_entry (stat_cat,owner,value) VALUES (' || stat_cat_id || ',' || aou_id || ',''' || entry_value || ''');';
+        RETURN 'Actor Stat Cat Entry created successfully.';
+    END IF;
+
+END
+$func$  LANGUAGE plpgsql;
+