dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 18397
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Tue 2015-02-24 22:18:42 +0100
message:
  Event analytics tables, added support for data element legends/ranges.
modified:
  dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/Program.java
  dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java
  dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java


--
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

=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/Program.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/Program.java	2015-02-24 13:57:25 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/Program.java	2015-02-24 21:18:42 +0000
@@ -186,7 +186,7 @@
 
     /**
      * Returns data elements which are part of the stages of this program which
-     * have a legend set.
+     * have a legend set and is of numeric value type.
      */
     public Set<DataElement> getDataElementsWithLegendSet()
     {
@@ -194,7 +194,7 @@
 
         for ( DataElement element : getAllDataElements() )
         {
-            if ( element != null && element.hasLegendSet() )
+            if ( element != null && element.hasLegendSet() && element.isNumericType() )
             {
                 elements.add( element );
             }

=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java	2015-02-24 13:57:25 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java	2015-02-24 21:18:42 +0000
@@ -261,22 +261,34 @@
             String dataClause = dataElement.isNumericType() ? numericClause : "";
             String select = dataElement.isNumericType() ? doubleSelect : "value";
 
-            String sql = "(select " + select + " from trackedentitydatavalue where programstageinstanceid="
-                + "psi.programstageinstanceid and dataelementid=" + dataElement.getId() + dataClause + ") as "
-                + quote( dataElement.getUid() );
+            String sql = "(select " + select + " from trackedentitydatavalue where programstageinstanceid=" + 
+                "psi.programstageinstanceid and dataelementid=" + dataElement.getId() + dataClause + ") as " + quote( dataElement.getUid() );
 
             String[] col = { quote( dataElement.getUid() ), dataType, sql };
             columns.add( col );
         }
-        
+
+        for ( DataElement dataElement : table.getProgram().getDataElementsWithLegendSet() )
+        {
+            String column = quote( dataElement.getUid() + PartitionUtils.SEP + dataElement.getLegendSet().getUid() );
+            
+            String sql = "(select l.name from maplegend l inner join maplegendsetmaplegend lsl on l.maplegendid=lsl.maplegendid " +
+                "inner join trackedentitydatavalue dv on l.startvalue <= " + doubleSelect + " and l.endvalue > " + doubleSelect + " " +
+                "and lsl.legendsetid=" + dataElement.getLegendSet().getId() + " and dv.programstageinstanceid=" + 
+                "psi.programstageinstanceid and dv.dataelementid=" + dataElement.getId() + numericClause + ") as " + column;
+                
+            String[] col = { column, "character varying(230)", sql };
+            columns.add( col );
+        }
+
         for ( TrackedEntityAttribute attribute : table.getProgram().getTrackedEntityAttributes() )
         {
             String dataType = attribute.isNumericType() ? dbl : text;
             String dataClause = attribute.isNumericType() ? numericClause : "";
             String select = attribute.isNumericType() ? doubleSelect : "value";
 
-            String sql = "(select " + select + " from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and "
-                + "trackedentityattributeid=" + attribute.getId() + dataClause + ") as " + quote( attribute.getUid() );
+            String sql = "(select " + select + " from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and " + 
+                "trackedentityattributeid=" + attribute.getId() + dataClause + ") as " + quote( attribute.getUid() );
 
             String[] col = { quote( attribute.getUid() ), dataType, sql };
             columns.add( col );

=== modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java	2015-02-24 13:16:58 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataapproval/hibernate/HibernateDataApprovalStore.java	2015-02-24 21:18:42 +0000
@@ -302,8 +302,6 @@
             testAncestors += "or o" + i + ".organisationunitid is not null ";
         }
 
-        final String dsCategoryComboIdMatches = isDefaultCombo ? "" : "and ds.categorycomboid = a.categorycomboid "; // Default option combo matches any data set.
-
         String readyBelowSubquery = "true"; // Ready below if this is the lowest (highest number) approval orgUnit level.
 
         int orgUnitLevelAbove = 0;
@@ -327,10 +325,9 @@
                 boolean acceptanceRequiredForApproval = (Boolean) systemSettingManager.getSystemSetting( KEY_ACCEPTANCE_REQUIRED_FOR_APPROVAL, false );
 
                 readyBelowSubquery = "not exists (select 1 from _orgunitstructure ous " +
-                        "join dataset ds on ds.datasetid in (" + dataSetIds + ") " + dsCategoryComboIdMatches +
                         "left join dataapproval da on da.organisationunitid = ous.organisationunitid " +
                         "and da.dataapprovallevelid = " + dal.getId() + " and da.periodid in (" + periodIds + ") " +
-                        "and da.datasetid = ds.datasetid " +
+                        "and da.datasetid in (" + dataSetIds + ") " +
                         "and da.attributeoptioncomboid = a.categoryoptioncomboid " +
                         "where ous.idlevel" + orgUnitLevel + " = a.organisationunitid " +
                         "and ous.level = " + dal.getOrgUnitLevel() + " " +
@@ -345,23 +342,20 @@
         {
             approvedAboveSubquery = "exists(select 1 from dataapproval da " +
                     "join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
-                    "join dataset ds on ds.datasetid = da.datasetid and ds.datasetid in (" + dataSetIds + ") " + dsCategoryComboIdMatches +
                     "join _orgunitstructure ou on ou.organisationunitid = a.organisationunitid and ou.idlevel" + orgUnitLevelAbove + " = da.organisationunitid " +
-                    "where da.periodid in (" + periodIds + ") and da.attributeoptioncomboid = a.categoryoptioncomboid) ";
+                    "where da.periodid in (" + periodIds + ") and da.datasetid in (" + dataSetIds + ") and da.attributeoptioncomboid = a.categoryoptioncomboid) ";
         }
 
         final String sql =
                 "select a.categoryoptioncomboid, a.organisationunitid, " +
                 "(select min(coalesce(dal.level, 0)) from period p " +
-                    "join dataset ds on ds.datasetid in (" + dataSetIds + ") " + dsCategoryComboIdMatches +
-                    "left join dataapproval da on da.datasetid = ds.datasetid and da.periodid = p.periodid " +
+                    "left join dataapproval da on da.datasetid in (" + dataSetIds + ") and da.periodid = p.periodid " +
                         "and da.attributeoptioncomboid = a.categoryoptioncomboid and da.organisationunitid = a.organisationunitid " +
                     "left join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
                     "where p.periodid in (" + periodIds + ") " +
                 ") as highest_approved_level, " +
                 "(select substring(min(concat(100000 + coalesce(dal.level, 0), coalesce(da.accepted, FALSE))) from 7) from period p " +
-                    "join dataset ds on ds.datasetid in (" + dataSetIds + ") " + dsCategoryComboIdMatches +
-                    "left join dataapproval da on da.datasetid = ds.datasetid and da.periodid = p.periodid " +
+                    "left join dataapproval da on da.datasetid in (" + dataSetIds + ") and da.periodid = p.periodid " +
                         "and da.attributeoptioncomboid = a.categoryoptioncomboid and da.organisationunitid = a.organisationunitid " +
                     "left join dataapprovallevel dal on dal.dataapprovallevelid = da.dataapprovallevelid " +
                     "where p.periodid in (" + periodIds + ") " +