=== 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 getAggregatedValueMapOrgUnitGroups( List dataElements, List indicators, - List periods, Collection groups, OrganisationUnit organisationUnit ) + List periods, Collection groups, OrganisationUnit organisationUnit, DataElementCategoryCombo categoryCombo, + boolean isDimensional, boolean doTotal ) { + Assert.notNull( organisationUnit ); + Map map = new HashMap(); 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 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(), new ArrayList(), periods, relativePeriods, new ArrayList(), new ArrayList(), + 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 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(), new ArrayList(), periods, relativePeriods, new ArrayList(), new ArrayList(), + 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() {