dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 1252
committer: Jason Pickering <jason@jason-laptop>
branch nick: dhis2
timestamp: Sat 2009-12-19 14:06:29 +0200
message:
  Added Postgresql SP for decomposition of datavalues into an entity, attribute value triplet dataset for a given dataelement and sourceid
added:
  resources/sql/create_eav_dataset.sql


--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription.

=== added file 'resources/sql/create_eav_dataset.sql'
--- resources/sql/create_eav_dataset.sql	1970-01-01 00:00:00 +0000
+++ resources/sql/create_eav_dataset.sql	2009-12-19 12:06:29 +0000
@@ -0,0 +1,92 @@
+-- Function: create_eav_datavalue(integer, integer, integer, integer)
+--Returns an (objectid, attribute, value)  triplet for  a given 
+-- dataelement and sourceid
+-- DROP FUNCTION create_eav_datavalueset(integer, integer, integer, integer);
+-- CREATE TYPE eav_text as (objectid integer, attribute text, "value" text);
+
+CREATE OR REPLACE FUNCTION create_eav_datavalueset(mydataelementid integer, mysourceid integer)
+  RETURNS SETOF eav_text AS
+$BODY$
+
+DECLARE
+this_objectid integer DEFAULT 0;
+periods record;
+categoryoptioncomboids record;
+
+
+BEGIN
+ EXECUTE 'DROP TABLE IF EXISTS _eav_dataset';
+ EXECUTE 'CREATE TABLE _eav_dataset ( objectid integer, attribute text, "value" text)';
+
+FOR periods IN 
+
+SELECT DISTINCT periodid FROM datavalue where dataelementid = mydataelementid AND
+	sourceid = mysourceid LOOP
+	FOR categoryoptioncomboids IN 
+	SELECT DISTINCT categoryoptioncomboid FROM datavalue 
+	where dataelementid = mydataelementid
+	 LOOP
+
+EXECUTE 'INSERT INTO _eav_dataset (objectid, attribute, "value")'
+          || 'SELECT ' 
+          || this_objectid 
+          || ', ''dataelementname''::text, "name"::text'
+          || ' FROM dataelement where dataelementid = '
+          || mydataelementid
+          || ' UNION' 
+          || ' SELECT ' 
+          || this_objectid
+          || ', ''startdate''::text, startdate::text FROM period where periodid = ' 
+          || periods.periodid
+          || ' UNION SELECT '
+          || this_objectid 
+          ||', ''enddatte''::text, enddate::text '
+	  || ' FROM period where periodid = ' 
+	  || periods.periodid
+	  ||' UNION'
+          ||' SELECT ' 
+          || this_objectid 
+          || ', ''orgunitname''::text, "name"::text' 
+          || ' FROM organisationunit where organisationunitid = ' 
+          || mysourceid
+          || ' UNION '
+	  || ' SELECT ' 
+	  || this_objectid 
+	  || ', ''value''::text, "value"::text'
+          || ' FROM datavalue where sourceid = ' 
+          || mysourceid
+          || ' AND periodid = ' 
+          || periods.periodid 
+          ||  'AND dataelementid = ' 
+          || mydataelementid
+          || ' AND categoryoptioncomboid = ' 
+          || categoryoptioncomboids.categoryoptioncomboid
+          || 'UNION '
+          || ' SELECT ' 
+          || this_objectid 
+          || ' , dataelementcategory.name, dataelementcategoryoption.name 
+		FROM categories_categoryoptions
+		INNER JOIN dataelementcategory ON 
+		dataelementcategory.categoryid = categories_categoryoptions.categoryid
+		INNER JOIN dataelementcategoryoption ON  
+		dataelementcategoryoption.categoryoptionid = categories_categoryoptions.categoryoptionid
+		WHERE categories_categoryoptions.categoryoptionid IN
+		(SELECT categoryoptionid FROM categoryoptioncombos_categoryoptions
+		 where categoryoptioncomboid = '
+          || categoryoptioncomboids.categoryoptioncomboid  ||')'
+
+           ;
+         this_objectid := this_objectid + 1;
+   END LOOP;
+
+END LOOP;
+
+END;
+
+
+  $BODY$
+  LANGUAGE 'plpgsql' VOLATILE
+  COST 100
+  ROWS 1000;
+ALTER FUNCTION create_eav_datavalue(integer, integer, integer, integer) OWNER TO postgres;
+