dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 5289
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2011-12-05 16:42:24 +0100
message:
  Fixed issue with aggregated map value queries
modified:
  dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/aggregation/jdbc/JdbcAggregatedDataValueStore.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-services/dhis-service-core/src/main/java/org/hisp/dhis/aggregation/jdbc/JdbcAggregatedDataValueStore.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/aggregation/jdbc/JdbcAggregatedDataValueStore.java	2011-12-05 12:21:38 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/aggregation/jdbc/JdbcAggregatedDataValueStore.java	2011-12-05 15:42:24 +0000
@@ -439,12 +439,13 @@
     public Collection<AggregatedMapValue> getAggregatedDataMapValues( int dataElementId, int periodId, Collection<Integer> organisationUnitIds )
     {
         final String sql = 
-            "SELECT a.periodid, o.organisationunitid, o.name, a.value " +
+            "SELECT a.periodid, o.organisationunitid, o.name, SUM(a.value) AS value " +
             "FROM aggregateddatavalue AS a " +
             "JOIN organisationunit AS o ON (a.organisationunitid=o.organisationunitid) " +
             "WHERE a.dataelementid  = " + dataElementId + " " +
             "AND a.periodid = " + periodId + " " + 
-            "AND a.organisationunitid IN (" + getCommaDelimitedString( organisationUnitIds ) + ")";
+            "AND a.organisationunitid IN (" + getCommaDelimitedString( organisationUnitIds ) + ") " +
+            "GROUP BY a.periodid, o.organisationunitid, o.name";
         
         return jdbcTemplate.query( sql, new AggregatedDataMapValueRowMapper() );        
     }
@@ -452,12 +453,13 @@
     public Collection<AggregatedMapValue> getAggregatedDataMapValues( Collection<Integer> dataElementIds, int periodId, int organisationUnitId )
     {
         final String sql = 
-            "SELECT d.name, a.value, a.periodid " +
+            "SELECT d.name, a.periodid, SUM(a.value) AS value " +
             "FROM aggregateddatavalue AS a " +
             "JOIN dataelement AS d ON (a.dataelementid = d.dataelementid) " +
             "WHERE a.dataelementid IN (" + getCommaDelimitedString( dataElementIds ) + ") " +
             "AND a.periodid = " + periodId + " " + 
-            "AND a.organisationunitid = " + organisationUnitId;
+            "AND a.organisationunitid = " + organisationUnitId + " " +
+            "GROUP BY d.name, a.periodid";
         
         return jdbcTemplate.query( sql, new org.springframework.jdbc.core.RowMapper<AggregatedMapValue>()
         {
@@ -466,8 +468,8 @@
             {
                 AggregatedMapValue value = new AggregatedMapValue();
                 value.setDataElementName( resultSet.getString( 1 ) );
-                value.setValue( resultSet.getDouble( 2 ) );
-                value.setPeriodId( resultSet.getInt( 3 ) );                
+                value.setPeriodId( resultSet.getInt( 2 ) );
+                value.setValue( resultSet.getDouble( 3 ) );
                 return value;
             }
         } );