dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #18817
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 7991: Report table, impl support for dimensional data for org unit group based tables
------------------------------------------------------------
revno: 7991
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2012-08-31 16:38:21 +0200
message:
Report table, impl support for dimensional data for org unit group based tables
modified:
dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/impl/DefaultReportTableService.java
dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java
dhis-2/dhis-services/dhis-service-reporting/src/test/java/org/hisp/dhis/reporttable/ReportTableGridTest.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-reporting/src/main/java/org/hisp/dhis/reporttable/impl/DefaultReportTableService.java'
--- dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/impl/DefaultReportTableService.java 2012-05-15 20:20:50 +0000
+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/impl/DefaultReportTableService.java 2012-08-31 14:38:21 +0000
@@ -487,8 +487,7 @@
{
for ( DataElementCategoryOption categoryOption : reportTable.getCategoryCombo().getCategoryOptions() )
{
- grid.addValue( map
- .get( getIdentifier( row, DataElementCategoryOption.class, categoryOption.getId() ) ) );
+ grid.addValue( map.get( getIdentifier( row, DataElementCategoryOption.class, categoryOption.getId() ) ) );
}
}
=== modified file 'dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java'
--- dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java 2012-07-30 21:04:50 +0000
+++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/jdbc/JDBCReportTableManager.java 2012-08-31 14:38:21 +0000
@@ -50,6 +50,7 @@
import org.hisp.dhis.system.util.TextUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
+import org.springframework.util.Assert;
/**
* @author Lars Helge Overland
@@ -77,8 +78,8 @@
{
if ( reportTable.isOrganisationUnitGroupBased() )
{
- return getAggregatedValueMapOrgUnitGroups( reportTable.getDataElements(), reportTable.getIndicators(),
- reportTable.getAllPeriods(), reportTable.getAllUnits(), reportTable.getParentOrganisationUnit() );
+ return getAggregatedValueMapOrgUnitGroups( reportTable.getDataElements(), reportTable.getIndicators(), reportTable.getAllPeriods(),
+ reportTable.getAllUnits(), reportTable.getParentOrganisationUnit(), reportTable.getCategoryCombo(), reportTable.isDimensional(), reportTable.doTotal() );
}
else
{
@@ -92,7 +93,7 @@
if ( chart.isOrganisationUnitGroupBased() )
{
return getAggregatedValueMapOrgUnitGroups( chart.getDataElements(), chart.getIndicators(),
- chart.getAllPeriods(), chart.getOrganisationUnitGroupSet().getOrganisationUnitGroups(), chart.getFirstOrganisationUnit() );
+ chart.getAllPeriods(), chart.getOrganisationUnitGroupSet().getOrganisationUnitGroups(), chart.getFirstOrganisationUnit(), null, false, false );
}
else
{
@@ -106,8 +107,11 @@
// -------------------------------------------------------------------------
private Map<String, Double> getAggregatedValueMapOrgUnitGroups( List<DataElement> dataElements, List<Indicator> indicators,
- List<Period> periods, Collection<? extends NameableObject> groups, OrganisationUnit organisationUnit )
+ List<Period> periods, Collection<? extends NameableObject> groups, OrganisationUnit organisationUnit, DataElementCategoryCombo categoryCombo,
+ boolean isDimensional, boolean doTotal )
{
+ Assert.notNull( organisationUnit );
+
Map<String, Double> map = new HashMap<String, Double>();
String dataElementIds = TextUtils.getCommaDelimitedString(
@@ -124,7 +128,7 @@
final String sql = "SELECT dataelementid, periodid, organisationunitgroupid, SUM(value) FROM aggregatedorgunitdatavalue " +
"WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitgroupid IN (" + groupIds + ") " +
"AND organisationunitid = " + organisationUnit.getId() + " " +
- "GROUP BY dataelementid, periodid, organisationunitgroupid"; // Sum of category option combos
+ "GROUP BY dataelementid, periodid, organisationunitgroupid"; // Sum of category option combo
SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
@@ -156,6 +160,52 @@
}
}
+ if ( isDimensional ) // Category option combo values
+ {
+ final String sql = "SELECT dataelementid, categoryoptioncomboid, periodid, organisationunitgroupid, value FROM aggregatedorgunitdatavalue " +
+ "WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitgroupid IN (" + groupIds + ")" +
+ "AND organisationunitid = " + organisationUnit.getId();
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ while ( rowSet.next() )
+ {
+ String id = getIdentifier( getIdentifier( DataElement.class, rowSet.getInt( 1 ) ),
+ getIdentifier( DataElementCategoryOptionCombo.class, rowSet.getInt( 2 ) ),
+ getIdentifier( Period.class, rowSet.getInt( 3 ) ),
+ getIdentifier( OrganisationUnitGroup.class, rowSet.getInt( 4 ) ) );
+
+ map.put( id, rowSet.getDouble( 5 ) );
+ }
+ }
+
+ if ( doTotal ) // Category option sub totals
+ {
+ for ( DataElementCategoryOption categoryOption : categoryCombo.getCategoryOptions() )
+ {
+ String cocIds = TextUtils.getCommaDelimitedString(
+ ConversionUtils.getIdentifiers( DataElementCategoryOptionCombo.class, categoryOption.getCategoryOptionCombos() ) );
+
+ final String sql = "SELECT dataelementid, periodid, organisationunitgroupid, SUM(value) FROM aggregatedorgunitdatavalue " +
+ "WHERE dataelementid IN (" + dataElementIds + ") AND categoryoptioncomboid IN (" + cocIds + ") " +
+ "AND periodid IN (" + periodIds + ") AND organisationunitgroupid IN (" + groupIds + ") " +
+ "AND organisationunitid = " + organisationUnit.getId() + " " +
+ "GROUP BY dataelementid, periodid, organisationunitgroupid";
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+
+ while ( rowSet.next() )
+ {
+ String id = getIdentifier( getIdentifier( DataElement.class, rowSet.getInt( 1 ) ),
+ getIdentifier( Period.class, rowSet.getInt( 2 ) ),
+ getIdentifier( OrganisationUnitGroup.class, rowSet.getInt( 3 ) ),
+ getIdentifier( DataElementCategoryOption.class, categoryOption.getId() ) );
+
+ map.put( id, rowSet.getDouble( 4 ) );
+ }
+ }
+ }
+
return map;
}
@@ -180,11 +230,11 @@
String unitIds = TextUtils.getCommaDelimitedString(
ConversionUtils.getIdentifiers( NameableObject.class, organisationUnits ) );
- if ( dataElementIds != null && !dataElementIds.isEmpty() )
+ if ( dataElementIds != null && !dataElementIds.isEmpty() ) // Data element totals
{
final String sql = "SELECT dataelementid, periodid, organisationunitid, SUM(value) FROM aggregateddatavalue " +
"WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ") " +
- "GROUP BY dataelementid, periodid, organisationunitid"; // Sum of category option combos
+ "GROUP BY dataelementid, periodid, organisationunitid";
SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
@@ -232,7 +282,7 @@
}
}
- if ( isDimensional )
+ if ( isDimensional ) // Category option combo values
{
final String sql = "SELECT dataelementid, categoryoptioncomboid, periodid, organisationunitid, value FROM aggregateddatavalue " +
"WHERE dataelementid IN (" + dataElementIds + ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ")";
@@ -250,17 +300,17 @@
}
}
- if ( doTotal )
+ if ( doTotal ) // Category option sub totals
{
- for ( DataElementCategoryOption categoryOption : categoryCombo.getCategoryOptions() ) //categorycombo
+ for ( DataElementCategoryOption categoryOption : categoryCombo.getCategoryOptions() )
{
String cocIds = TextUtils.getCommaDelimitedString(
ConversionUtils.getIdentifiers( DataElementCategoryOptionCombo.class, categoryOption.getCategoryOptionCombos() ) );
final String sql = "SELECT dataelementid, periodid, organisationunitid, SUM(value) FROM aggregateddatavalue " +
- "WHERE dataelementid IN (" + dataElementIds + ") AND categoryoptioncomboid IN (" + cocIds +
- ") AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds +
- ") GROUP BY dataelementid, periodid, organisationunitid"; // Sum of category option combos
+ "WHERE dataelementid IN (" + dataElementIds + ") AND categoryoptioncomboid IN (" + cocIds + ") " +
+ "AND periodid IN (" + periodIds + ") AND organisationunitid IN (" + unitIds + ") " +
+ "GROUP BY dataelementid, periodid, organisationunitid";
SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
=== modified file 'dhis-2/dhis-services/dhis-service-reporting/src/test/java/org/hisp/dhis/reporttable/ReportTableGridTest.java'
--- dhis-2/dhis-services/dhis-service-reporting/src/test/java/org/hisp/dhis/reporttable/ReportTableGridTest.java 2012-04-05 11:57:23 +0000
+++ dhis-2/dhis-services/dhis-service-reporting/src/test/java/org/hisp/dhis/reporttable/ReportTableGridTest.java 2012-08-31 14:38:21 +0000
@@ -384,6 +384,10 @@
// Tests
// -------------------------------------------------------------------------
+ // -------------------------------------------------------------------------
+ // Org unit group
+ // -------------------------------------------------------------------------
+
@Test
public void testGetOrgUnitIndicatorReportTableA()
{
@@ -531,7 +535,104 @@
assertEquals( 27.0, grid.getRow( 1 ).get( 10 ) );
assertEquals( 28.0, grid.getRow( 1 ).get( 11 ) );
}
+
+ @Test
+ public void testGetCategoryComboOrgUnitReportTableA()
+ {
+ BatchHandler<AggregatedDataValue> dataValueOrgUnitBatchHandler = batchHandlerFactory.createBatchHandler( AggregatedOrgUnitDataValueBatchHandler.class ).init();
+
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdA, categoryOptionComboIdB, periodIdA, 8, unitIdA, groupIdA, 8, 31 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdA, categoryOptionComboIdB, periodIdA, 8, unitIdA, groupIdB, 8, 32 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdA, categoryOptionComboIdB, periodIdB, 8, unitIdA, groupIdA, 8, 33 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdA, categoryOptionComboIdB, periodIdB, 8, unitIdA, groupIdB, 8, 34 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdB, categoryOptionComboIdB, periodIdA, 8, unitIdA, groupIdA, 8, 35 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdB, categoryOptionComboIdB, periodIdA, 8, unitIdA, groupIdB, 8, 36 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdB, categoryOptionComboIdB, periodIdB, 8, unitIdA, groupIdA, 8, 37 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdB, categoryOptionComboIdB, periodIdB, 8, unitIdA, groupIdB, 8, 38 ) );
+
+ dataValueOrgUnitBatchHandler.flush();
+
+ ReportTable reportTable = new ReportTable( "Prescriptions",
+ dataElements, new ArrayList<Indicator>(), new ArrayList<DataSet>(), periods, relativePeriods, new ArrayList<OrganisationUnit>(), new ArrayList<OrganisationUnit>(),
+ groups, categoryComboA, true, true, false, new RelativePeriods(), null, i18nFormat, "january_2000" );
+
+ reportTable.setParentOrganisationUnit( unitA );
+
+ int id = reportTableService.saveReportTable( reportTable );
+
+ Grid grid = reportTableService.getReportTableGrid( id, i18nFormat, date, 0 );
+
+ assertEquals( 21.0, grid.getRow( 0 ).get( 8 ) );
+ assertEquals( 31.0, grid.getRow( 0 ).get( 9 ) );
+ assertEquals( 23.0, grid.getRow( 0 ).get( 10 ) );
+ assertEquals( 33.0, grid.getRow( 0 ).get( 11 ) );
+ assertEquals( 25.0, grid.getRow( 0 ).get( 12 ) );
+ assertEquals( 35.0, grid.getRow( 0 ).get( 13 ) );
+ assertEquals( 27.0, grid.getRow( 0 ).get( 14 ) );
+ assertEquals( 37.0, grid.getRow( 0 ).get( 15 ) );
+
+ assertEquals( 22.0, grid.getRow( 1 ).get( 8 ) );
+ assertEquals( 32.0, grid.getRow( 1 ).get( 9 ) );
+ assertEquals( 24.0, grid.getRow( 1 ).get( 10 ) );
+ assertEquals( 34.0, grid.getRow( 1 ).get( 11 ) );
+ assertEquals( 26.0, grid.getRow( 1 ).get( 12 ) );
+ assertEquals( 36.0, grid.getRow( 1 ).get( 13 ) );
+ assertEquals( 28.0, grid.getRow( 1 ).get( 14 ) );
+ assertEquals( 38.0, grid.getRow( 1 ).get( 15 ) );
+ }
+
+ @Test
+ public void testGetCategoryComboOrgUnitReportTableB()
+ {
+ BatchHandler<AggregatedDataValue> dataValueOrgUnitBatchHandler = batchHandlerFactory.createBatchHandler( AggregatedOrgUnitDataValueBatchHandler.class ).init();
+
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdA, categoryOptionComboIdB, periodIdA, 8, unitIdA, groupIdA, 8, 31 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdA, categoryOptionComboIdB, periodIdA, 8, unitIdA, groupIdB, 8, 32 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdA, categoryOptionComboIdB, periodIdB, 8, unitIdA, groupIdA, 8, 33 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdA, categoryOptionComboIdB, periodIdB, 8, unitIdA, groupIdB, 8, 34 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdB, categoryOptionComboIdB, periodIdA, 8, unitIdA, groupIdA, 8, 35 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdB, categoryOptionComboIdB, periodIdA, 8, unitIdA, groupIdB, 8, 36 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdB, categoryOptionComboIdB, periodIdB, 8, unitIdA, groupIdA, 8, 37 ) );
+ dataValueOrgUnitBatchHandler.addObject( new AggregatedDataValue( dataElementIdB, categoryOptionComboIdB, periodIdB, 8, unitIdA, groupIdB, 8, 38 ) );
+
+ dataValueOrgUnitBatchHandler.flush();
+
+ ReportTable reportTable = new ReportTable( "Prescriptions",
+ dataElements, new ArrayList<Indicator>(), new ArrayList<DataSet>(), periods, relativePeriods, new ArrayList<OrganisationUnit>(), new ArrayList<OrganisationUnit>(),
+ groups, categoryComboA, false, false, true, new RelativePeriods(), null, i18nFormat, "january_2000" );
+
+
+ reportTable.setParentOrganisationUnit( unitA );
+
+ int id = reportTableService.saveReportTable( reportTable );
+
+ Grid grid = reportTableService.getReportTableGrid( id, i18nFormat, date, 0 );
+
+ assertEquals( 21.0, grid.getRow( 0 ).get( 13 ) );
+ assertEquals( 31.0, grid.getRow( 0 ).get( 14 ) );
+ assertEquals( 22.0, grid.getRow( 0 ).get( 15 ) );
+ assertEquals( 32.0, grid.getRow( 0 ).get( 16 ) );
+
+ assertEquals( 23.0, grid.getRow( 1 ).get( 13 ) );
+ assertEquals( 33.0, grid.getRow( 1 ).get( 14 ) );
+ assertEquals( 24.0, grid.getRow( 1 ).get( 15 ) );
+ assertEquals( 34.0, grid.getRow( 1 ).get( 16 ) );
+
+ assertEquals( 25.0, grid.getRow( 2 ).get( 13 ) );
+ assertEquals( 35.0, grid.getRow( 2 ).get( 14 ) );
+ assertEquals( 26.0, grid.getRow( 2 ).get( 15 ) );
+ assertEquals( 36.0, grid.getRow( 2 ).get( 16 ) );
+
+ assertEquals( 27.0, grid.getRow( 3 ).get( 13 ) );
+ assertEquals( 37.0, grid.getRow( 3 ).get( 14 ) );
+ assertEquals( 28.0, grid.getRow( 3 ).get( 15 ) );
+ assertEquals( 38.0, grid.getRow( 3 ).get( 16 ) );
+ }
+ // -------------------------------------------------------------------------
+ // Org unit hierarchy
+ // -------------------------------------------------------------------------
+
@Test
public void testGetIndicatorReportTableA()
{
