=== added file 'resources/zm_hmis/sql/report_completeness.txt'
--- resources/zm_hmis/sql/report_completeness.txt	1970-01-01 00:00:00 +0000
+++ resources/zm_hmis/sql/report_completeness.txt	2011-04-07 05:07:00 +0000
@@ -0,0 +1,126 @@
+-- Function: materialize_report_completeness(integer, date, date)
+
+-- DROP FUNCTION materialize_report_completeness(integer, date, date);
+
+CREATE OR REPLACE FUNCTION materialize_report_completeness(datasetid integer, startdate date, enddate date)
+  RETURNS integer AS
+$BODY$
+BEGIN
+EXECUTE 'DELETE FROM report_completeness where datasetid = ' || $1 || ';';
+EXECUTE 'INSERT INTO report_completeness (periodid, organisationunitid, datasetid)
+SELECT p.periodid, dss.sourceid, '
+|| $1
+||' FROM datasetsource dss CROSS JOIN (SELECT periodid FROM period where startdate >= '
+|| ''''
+||$2
+|| ''''
+||' and enddate <= '
+|| ''''
+||$3
+|| ''''
+||' and periodtypeid = (SELECT periodtypeid from dataset where datasetid = '
+||$1
+||')) as p where datasetid = '
+|| $1
+||';';
+
+EXECUTE 'UPDATE report_completeness SET expected = (SELECT COUNT(*) FROM datasetmembers where datasetid = '|| $1 || ');';
+EXECUTE 'UPDATE report_completeness a SET expected = b.expected from (SELECT ou.organisationunitid, p.periodid, 0::integer as expected 
+ from organisationunit ou
+CROSS JOIN (SELECT periodid, startdate FROM period where startdate >= '
+|| ''''
+|| $2 
+|| ''''
+|| ' and enddate <= '
+|| ''''
+|| $3
+|| ''''
+||') p WHERE ou.openingdate >= p.startdate) b
+where a.organisationunitid = b.organisationunitid
+and a.periodid = b.periodid
+and a.datasetid = '
+|| $1
+||';';
+
+
+
+EXECUTE 'UPDATE report_completeness a SET actual = b.actual FROM (SELECT periodid, sourceid as organisationunitid, '
+|| $1
+|| 'as datasetid, COUNT(*) as actual FROM datavalue where dataelementid IN (SELECT dataelementid from datasetmembers where datasetid = '
+|| $1
+||') GROUP BY periodid, sourceid, datasetid) b
+WHERE a.periodid = b.periodid
+AND a.organisationunitid = b.organisationunitid
+AND a.datasetid = b.datasetid;';
+
+EXECUTE 'UPDATE report_completeness SET actual = 0 where actual IS NULL and datasetid = ' || $1 || ';';
+EXECUTE 'UPDATE report_completeness SET anything = 0 WHERE actual = 0 and datasetid = ' || $1 || ';';
+EXECUTE 'UPDATE report_completeness SET anything = 1 where actual !=0 and datasetid = ' || $1 || ';';
+EXECUTE 'UPDATE report_completeness SET reported_elements_ratio = actual::numeric / expected::numeric WHERE datasetid = ' || $1 || ' and expected !=0;';
+EXECUTE 'UPDATE report_completeness SET reported_elements_ratio = -1 WHERE datasetid = ' || $1 || ' and expected =0 and actual > 0;';
+EXECUTE 'UPDATE report_completeness SET reported_elements_ratio = 0 WHERE datasetid = ' || $1 || ' and expected =0 and actual = 0;';
+
+RETURN 1;
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION materialize_report_completeness(integer, date, date) OWNER TO postgres;
+
+
+
+//R
+library(lattice)
+library(latticeExtra)
+library(RODBC)
+channel<-odbcConnect("dhis2")
+dis_hia2<-sqlQuery(channel,"SELECT p.startdate, p.enddate, ou2.name as province, ou3.name as district, ds.name as dataset,
+sum(rc.anything)::numeric/count(rc.organisationunitid)::numeric as report_completeness, 
+avg(rc.reported_elements_ratio) as avg_completeness
+ FROM report_completeness rc
+ INNER JOIN period p on p.periodid = rc.periodid
+ INNER JOIN _orgunitstructure ous on rc.organisationunitid = ous.organisationunitid
+ INNER JOIN organisationunit ou2 on ous.idlevel2 = ou2.organisationunitid
+ INNER JOIN organisationunit ou3 on ous.idlevel3 = ou3.organisationunitid
+  INNER JOIN dataset ds on rc.datasetid = ds.datasetid
+  GROUP BY startdate, enddate, province, district, dataset
+  ORDER BY province,district,  startdate;")
+png(filename="hia2_rc_2011_any.png", width=1024,heigh=768)
+xyplot(report_completeness ~ startdate | district,
+	data = dis_hia2, main='HIA2 Facility Report completeness (Oct 2009-Dec 2010)',
+		layout=c(8,9),xlab="Month", ylab = "Ratio HIA2 report submitted",
+		scales = list(x = list(rot=90)))
+dev.off()
+png(filename="hia2_rc_2011_dataset.png", width=1024,heigh=768)
+xyplot(avg_completeness ~ startdate | district,
+	data = dis_hia2, main='HIA2 Dataset completeness (Oct 2009-Dec 2010)',
+		layout=c(8,9),xlab="Month", ylab = "Avg ratio HIA2 elements submitted",
+		scales = list(x = list(rot=90)))
+dev.off()
+choma<-sqlQuery(channel,"SELECT p.startdate, p.enddate, ou2.name as province, ou3.name as district, ou4.shortname as facility , ds.name as dataset,
+rc.expected, rc.actual, rc.anything, rc.reported_elements_ratio
+
+  FROM report_completeness rc
+ INNER JOIN period p on p.periodid = rc.periodid
+ INNER JOIN _orgunitstructure ous on rc.organisationunitid = ous.organisationunitid
+  INNER JOIN organisationunit ou2 on ous.idlevel2 = ou2.organisationunitid
+INNER JOIN organisationunit ou3 on ous.idlevel3 = ou3.organisationunitid
+   INNER JOIN organisationunit ou4 on ous.idlevel4 = ou4.organisationunitid
+   INNER JOIN dataset ds on rc.datasetid = ds.datasetid
+   WHERE ou3.name ~*('Choma')
+   ORDER BY province,district, facility, startdate
+   ;")
+png(filename="hia2_rc_2011_choma.png", width=1024,heigh=768)
+xyplot(reported_elements_ratio ~ startdate | facility,
+	data = choma, main='HIA2 Dataset completeness, Choma District (Oct 2009-Dec 2010)',
+,xlab="Month", ylab = "Ratio HIA2 elements submitted ",
+		scales = list(x = list(rot=90)))
+dev.off()
+png(filename="hia2_rc_2011_dens.png", width=1024,heigh=768)
+histogram( ~ reported_elements_ratio | district, data=facility, type="density",xlab="Ratio elements submitted", main="HIA2 Facility dataset report completeness",
+panel = function(x, ...) {
+panel.histogram(x,...)
+panel.mathdensity(dmath = dnorm, col="black",
+args = list(mean=mean(x),sd=sd(x)))
+})
+dev.off()
\ No newline at end of file