=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/datamart/DataMartStore.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/datamart/DataMartStore.java 2009-11-19 15:25:06 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/datamart/DataMartStore.java 2009-11-19 17:30:24 +0000 @@ -34,6 +34,7 @@ import org.hisp.dhis.aggregation.AggregatedIndicatorValue; import org.hisp.dhis.aggregation.AggregatedMapValue; import org.hisp.dhis.dataelement.DataElement; +import org.hisp.dhis.dataelement.DataElementCategoryOption; import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; import org.hisp.dhis.dataelement.Operand; import org.hisp.dhis.datavalue.DataValue; @@ -62,9 +63,21 @@ * @param dataElement The DataElement. * @param period The Period. * @param organisationUnit The OrganisationUnit. - * @return the aggregated value, or -1 if no value exists. - */ - Double getTotalAggregatedValue( final DataElement dataElement, final Period period, final OrganisationUnit organisationUnit ); + * @return the aggregated value. + */ + Double getTotalAggregatedValue( DataElement dataElement, Period period, OrganisationUnit organisationUnit ); + + /** + * Gets the total aggregated value from the datamart table for the given parameters. + * + * @param dataElement The DataElement. + * @param categoryOption the DataElementCategoryOption. + * @param period The Period. + * @param organisationUnit The OrganisationUnit. + * @return the aggregated value. + */ + Double getTotalAggregatedValue( final DataElement dataElement, + final DataElementCategoryOption categoryOption, final Period period, final OrganisationUnit organisationUnit ); /** * Gets the aggregated value from the datamart table for the given parameters. === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/reporttable/ReportTable.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/reporttable/ReportTable.java 2009-11-19 15:25:06 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/reporttable/ReportTable.java 2009-11-19 17:30:24 +0000 @@ -27,6 +27,9 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import static org.hisp.dhis.dimension.DimensionSet.TYPE_CATEGORY_COMBO; +import static org.hisp.dhis.dimension.DimensionSet.TYPE_GROUP_SET; + import java.io.Serializable; import java.util.ArrayList; import java.util.HashMap; @@ -41,6 +44,7 @@ import org.hisp.dhis.dataelement.DataElementGroupSet; import org.hisp.dhis.dataset.DataSet; import org.hisp.dhis.dimension.Dimension; +import org.hisp.dhis.dimension.DimensionOption; import org.hisp.dhis.dimension.DimensionOptionElement; import org.hisp.dhis.dimension.DimensionSet; import org.hisp.dhis.i18n.I18nFormat; @@ -48,8 +52,6 @@ import org.hisp.dhis.organisationunit.OrganisationUnit; import org.hisp.dhis.period.Period; -import static org.hisp.dhis.dimension.DimensionSet.*; - /** * @author Lars Helge Overland * @version $Id$ @@ -78,11 +80,12 @@ public static final String MODE_DATAELEMENTS = "dataelements"; public static final String MODE_INDICATORS = "indicators"; public static final String MODE_DATASETS = "datasets"; + + public static final String TOTAL_COLUMN_NAME = "total"; + public static final String TOTAL_COLUMN_PREFIX = "total_"; public static final String REGRESSION_COLUMN_PREFIX = "regression_"; - public static final String NAME_TOTAL_COLUMN = "total"; - private static final String EMPTY_REPLACEMENT = "_"; private static final String TABLE_PREFIX = "_report_"; private static final String REGEX_NUMERIC = "([0-9]*)"; @@ -250,6 +253,16 @@ */ private List dimensionalDataElements = new ArrayList(); + /** + * The dimension options. + */ + private List dimensionOptions = new ArrayList(); + + /** + * The dimension option column names. + */ + private List dimensionOptionColumns = new ArrayList(); + // ------------------------------------------------------------------------- // Constructors // ------------------------------------------------------------------------- @@ -499,6 +512,24 @@ } } } + + // --------------------------------------------------------------------- + // Init dimensionOptions and dimensionOptionColumns + // --------------------------------------------------------------------- + + if ( doTotal() ) + { + List dimensions = isDimensional( TYPE_CATEGORY_COMBO ) ? categoryCombo.getDimensions() : dataElementGroupSets; + + for ( Dimension dimension : dimensions ) + { + for ( DimensionOption dimensionOption : dimension.getDimensionOptions() ) + { + dimensionOptions.add( dimensionOption ); + dimensionOptionColumns.add( databaseEncode( TOTAL_COLUMN_PREFIX + dimensionOption.getName() ) ); + } + } + } } // ------------------------------------------------------------------------- @@ -656,14 +687,23 @@ } /** + * Tests whether this ReportTable is multi-dimensional and of the argument + * dimension set type. + */ + public boolean isDimensional( String dimensionSetType ) + { + return isDimensional() && dimensionSetType.equals( dimensionSetType ); + } + + /** * Tests whether a total column should be included. */ public boolean doTotal() { return !isDoIndicators() && !isDoPeriods() && !isDoUnits() && isDoCategoryOptionCombos() && - isDimensional() && mode.equals( MODE_DATAELEMENTS ); + isDimensional( DimensionSet.TYPE_CATEGORY_COMBO ) && mode.equals( MODE_DATAELEMENTS ); } - + // ------------------------------------------------------------------------- // Supportive methods // ------------------------------------------------------------------------- @@ -784,35 +824,28 @@ if ( metaObject != null ) { - buffer.append( databaseEncode( metaObject.getShortName() ) + SEPARATOR ); + buffer.append( metaObject.getShortName() + SEPARATOR ); } if ( categoryOptionCombo != null ) { - buffer.append( databaseEncode( categoryOptionCombo.getShortName() ) + SEPARATOR ); + buffer.append( categoryOptionCombo.getShortName() + SEPARATOR ); } if ( period != null ) { String periodName = period.getName() != null ? period.getName() : i18nFormat.formatPeriod( period ); - buffer.append( databaseEncode( periodName ) + SEPARATOR ); + buffer.append( periodName + SEPARATOR ); } if ( unit != null ) { - buffer.append( databaseEncode( unit.getShortName() ) + SEPARATOR ); - } - - // --------------------------------------------------------------------- - // Columns cannot start with numeric character - // --------------------------------------------------------------------- - - if ( buffer.length() > 0 && buffer.substring( 0, 1 ).matches( REGEX_NUMERIC ) ) - { - buffer.insert( 0, SEPARATOR ); - } + buffer.append( unit.getShortName() + SEPARATOR ); + } + + String column = databaseEncode( buffer.toString() ); - return buffer.length() > 0 ? buffer.substring( 0, buffer.lastIndexOf( SEPARATOR ) ) : buffer.toString(); + return column.length() > 0 ? column.substring( 0, column.lastIndexOf( SEPARATOR ) ) : column; } - + /** * Generates a column identifier based on the internal identifiers of the * argument objects. Null arguments are ignored in the identifier. @@ -842,7 +875,10 @@ } /** - * Database encodes the argument string. + * Database encodes the argument string. Remove non-character data from the + * string, prefixes the string if it starts with a numeric charater and + * truncates the string if it is longer than 255 characters. + * */ private String databaseEncode( String string ) { @@ -868,7 +904,20 @@ string = buffer.toString(); string = string.replaceAll( EMPTY_REPLACEMENT + "+", EMPTY_REPLACEMENT ); - + + // ----------------------------------------------------------------- + // Cannot start with numeric character + // ----------------------------------------------------------------- + + if ( string.length() > 0 && string.substring( 0, 1 ).matches( REGEX_NUMERIC ) ) + { + string = SEPARATOR + string; + } + + // ----------------------------------------------------------------- + // Cannot be longer than 255 characters + // ----------------------------------------------------------------- + if ( string.length() > 255 ) { string = string.substring( 0, 255 ); @@ -1244,4 +1293,14 @@ { this.reportingMonthName = reportingMonthName; } + + public List getDimensionOptions() + { + return dimensionOptions; + } + + public List getDimensionOptionColumns() + { + return dimensionOptionColumns; + } } === modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/jdbc/JdbcDataMartStore.java' --- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/jdbc/JdbcDataMartStore.java 2009-11-19 15:25:06 +0000 +++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/jdbc/JdbcDataMartStore.java 2009-11-19 17:30:24 +0000 @@ -27,6 +27,7 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import static org.hisp.dhis.system.util.ConversionUtils.getIdentifiers; import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString; import java.sql.ResultSet; @@ -42,6 +43,7 @@ import org.hisp.dhis.aggregation.AggregatedIndicatorValue; import org.hisp.dhis.aggregation.AggregatedMapValue; import org.hisp.dhis.dataelement.DataElement; +import org.hisp.dhis.dataelement.DataElementCategoryOption; import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; import org.hisp.dhis.dataelement.Operand; import org.hisp.dhis.datamart.CrossTabDataValue; @@ -108,6 +110,22 @@ return statementManager.getHolder().queryForDouble( sql ); } + + public Double getTotalAggregatedValue( final DataElement dataElement, + final DataElementCategoryOption categoryOption, final Period period, final OrganisationUnit organisationUnit ) + { + String ids = getCommaDelimitedString( getIdentifiers( DataElementCategoryOptionCombo.class, categoryOption.getCategoryOptionCombos() ) ); + + final String sql = + "SELECT " + functionMap.get( dataElement.getAggregationOperator() ) + "(value)" + + "FROM aggregateddatavalue " + + "WHERE dataelementid = " + dataElement.getId() + " " + + "AND categoryoptioncomboid IN (" + ids + ") " + + "AND periodid = " + period.getId() + " " + + "AND organisationunitid = " + organisationUnit.getId(); + + return statementManager.getHolder().queryForDouble( sql ); + } public double getAggregatedValue( final DataElement dataElement, final Period period, final OrganisationUnit organisationUnit ) { === 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 2009-11-19 15:25:06 +0000 +++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/impl/DefaultReportTableService.java 2009-11-19 17:30:24 +0000 @@ -44,10 +44,12 @@ import org.hisp.dhis.common.IdentifiableObject; import org.hisp.dhis.completeness.DataSetCompletenessService; import org.hisp.dhis.dataelement.DataElement; +import org.hisp.dhis.dataelement.DataElementCategoryOption; import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo; import org.hisp.dhis.datamart.DataMartService; import org.hisp.dhis.datamart.DataMartStore; import org.hisp.dhis.dataset.DataSet; +import org.hisp.dhis.dimension.DimensionOption; import org.hisp.dhis.i18n.I18nFormat; import org.hisp.dhis.indicator.Indicator; import org.hisp.dhis.jdbc.batchhandler.GenericBatchHandler; @@ -552,10 +554,8 @@ private Grid getGrid( ReportTable reportTable ) { - Grid grid = new Grid(); + final Grid grid = new Grid(); - Map map = null; - for ( final IdentifiableObject metaObject : reportTable.getReportIndicators() ) { for ( final DataElementCategoryOptionCombo categoryOptionCombo : reportTable.getReportCategoryOptionCombos() ) @@ -644,7 +644,7 @@ // Values // ----------------------------------------------------- - map = reportTableManager.getAggregatedValueMap( reportTable, metaObject, categoryOptionCombo, period, unit ); + Map map = reportTableManager.getAggregatedValueMap( reportTable, metaObject, categoryOptionCombo, period, unit ); for ( String identifier : reportTable.getCrossTabIdentifiers() ) { @@ -657,6 +657,12 @@ if ( reportTable.doTotal() ) { + for ( DimensionOption dimensionOption : reportTable.getDimensionOptions() ) + { + grid.addValue( String.valueOf( dataMartStore. + getTotalAggregatedValue( (DataElement) metaObject, (DataElementCategoryOption) dimensionOption, period, unit ) ) ); + } + grid.addValue( String.valueOf( dataMartStore.getTotalAggregatedValue( (DataElement) metaObject, period, unit ) ) ); } } === modified file 'dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/statement/CreateReportTableStatement.java' --- dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/statement/CreateReportTableStatement.java 2009-11-19 15:25:06 +0000 +++ dhis-2/dhis-services/dhis-service-reporting/src/main/java/org/hisp/dhis/reporttable/statement/CreateReportTableStatement.java 2009-11-19 17:30:24 +0000 @@ -102,7 +102,12 @@ if ( reportTable.doTotal() ) { - buffer.append( ReportTable.NAME_TOTAL_COLUMN + SPACE + statementBuilder.getDoubleColumnType() + SEPARATOR ); + for ( String column : reportTable.getDimensionOptionColumns() ) + { + buffer.append( column + SPACE + statementBuilder.getDoubleColumnType() + SEPARATOR ); + } + + buffer.append( ReportTable.TOTAL_COLUMN_NAME + SPACE + statementBuilder.getDoubleColumnType() + SEPARATOR ); } // ---------------------------------------------------------------------