=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/AggregationType.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/AggregationType.java 2014-07-15 18:31:16 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/AggregationType.java 2014-09-22 12:42:22 +0000 @@ -41,7 +41,8 @@ STDDEV( "stddev" ), VARIANCE( "variance" ), MIN( "min" ), - MAX( "max" ); + MAX( "max" ), + NONE( "none" ); private final String value; === added file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/DataType.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/DataType.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/DataType.java 2014-09-22 12:42:22 +0000 @@ -0,0 +1,37 @@ +package org.hisp.dhis.analytics; + +/* + * Copyright (c) 2004-2014, University of Oslo + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * Redistributions of source code must retain the above copyright notice, this + * list of conditions and the following disclaimer. + * + * Redistributions in binary form must reproduce the above copyright notice, + * this list of conditions and the following disclaimer in the documentation + * and/or other materials provided with the distribution. + * Neither the name of the HISP project nor the names of its contributors may + * be used to endorse or promote products derived from this software without + * specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND + * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR + * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON + * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +/** + * @author Lars Helge Overland + */ +public enum DataType +{ + NUMERIC, TEXT; +} === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java 2014-03-18 08:10:10 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java 2014-09-22 15:30:14 +0000 @@ -43,12 +43,12 @@ * Retrieves aggregated data values for the given query. The data is returned * as a mapping where the key is concatenated from the dimension options for * all dimensions, and the value is the data value. This method is invoked - * asynchronously. + * asynchronously. The value class can be Double or String. * * @param params the query to retrieve aggregated data for. * @return a map. */ - Future> getAggregatedDataValues( DataQueryParams params ); + Future> getAggregatedDataValues( DataQueryParams params ); /** * Inserts entries for the aggregation periods mapped to each data period @@ -59,5 +59,5 @@ * @param dataPeriodAggregationPeriodMap the mapping between data periods and * aggregation periods for this query. */ - void replaceDataPeriodsWithAggregationPeriods( Map dataValueMap, DataQueryParams params, ListMap dataPeriodAggregationPeriodMap ); + void replaceDataPeriodsWithAggregationPeriods( Map dataValueMap, DataQueryParams params, ListMap dataPeriodAggregationPeriodMap ); } === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2014-08-25 10:18:10 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2014-09-22 12:42:22 +0000 @@ -106,7 +106,7 @@ protected List filters = new ArrayList<>(); protected AggregationType aggregationType; - + private Map measureCriteria = new HashMap<>(); /** @@ -155,6 +155,11 @@ protected transient Partitions partitions; /** + * The data type for this query. + */ + protected transient DataType dataType; + + /** * The aggregation period type for this query. */ protected transient String periodType; @@ -201,6 +206,7 @@ params.hideEmptyRows = this.hideEmptyRows; params.partitions = new Partitions( this.partitions ); + params.dataType = this.dataType; params.periodType = this.periodType; params.dataPeriodType = this.dataPeriodType; params.skipPartitioning = this.skipPartitioning; @@ -604,6 +610,14 @@ return index == -1 ? null : index; } + + /** + * Indicates whether this object is of the given data type. + */ + public boolean isDataType( DataType dataType ) + { + return this.dataType != null && this.dataType.equals( dataType ); + } /** * Indicates whether this object is of the given aggregation type. @@ -916,6 +930,15 @@ this.dataApprovalLevels = new HashMap<>(); } + /** + * Indicates whether this params requires aggregation of data. No aggregation + * takes place if aggregation type is none or if data type is text. + */ + public boolean isAggregation() + { + return !( AggregationType.NONE.equals( aggregationType ) || DataType.TEXT.equals( dataType ) ); + } + // ------------------------------------------------------------------------- // Static methods // ------------------------------------------------------------------------- @@ -1152,6 +1175,16 @@ this.partitions = partitions; } + public DataType getDataType() + { + return dataType; + } + + public void setDataType( DataType dataType ) + { + this.dataType = dataType; + } + public String getPeriodType() { return periodType; === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2014-09-22 04:51:49 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2014-09-22 15:30:14 +0000 @@ -338,13 +338,13 @@ dataSourceParams.removeDimension( INDICATOR_DIM_ID ); dataSourceParams.removeDimension( DATASET_DIM_ID ); - Map aggregatedDataMap = getAggregatedDataValueMap( dataSourceParams ); + Map aggregatedDataMap = getAggregatedDataValueMapObjectTyped( dataSourceParams ); - for ( Map.Entry entry : aggregatedDataMap.entrySet() ) + for ( Map.Entry entry : aggregatedDataMap.entrySet() ) { grid.addRow(); grid.addValues( entry.getKey().split( DIMENSION_SEP ) ); - grid.addValue( params.isSkipRounding() ? entry.getValue() : MathUtils.getRounded( entry.getValue() ) ); + grid.addValue( params.isSkipRounding() ? entry.getValue() : getRounded( entry.getValue() ) ); } } } @@ -648,6 +648,19 @@ */ private Map getAggregatedDataValueMap( DataQueryParams params ) { + return getDoubleMap( getAggregatedValueMap( params, ANALYTICS_TABLE_NAME ) ); + } + + /** + * Generates aggregated values for the given query. Creates a mapping between + * a dimension key and the aggregated value. The dimension key is a + * concatenation of the identifiers of the dimension items separated by "-". + * + * @param params the data query parameters. + * @return a mapping between a dimension key and the aggregated value. + */ + private Map getAggregatedDataValueMapObjectTyped( DataQueryParams params ) + { return getAggregatedValueMap( params, ANALYTICS_TABLE_NAME ); } @@ -661,7 +674,7 @@ */ private Map getAggregatedCompletenessValueMap( DataQueryParams params ) { - return getAggregatedValueMap( params, COMPLETENESS_TABLE_NAME ); + return getDoubleMap( getAggregatedValueMap( params, COMPLETENESS_TABLE_NAME ) ); } /** @@ -674,7 +687,7 @@ */ private Map getAggregatedCompletenessTargetMap( DataQueryParams params ) { - return getAggregatedValueMap( params, COMPLETENESS_TARGET_TABLE_NAME ); + return getDoubleMap( getAggregatedValueMap( params, COMPLETENESS_TARGET_TABLE_NAME ) ); } /** @@ -688,7 +701,7 @@ */ private Map getAggregatedOrganisationUnitTargetMap( DataQueryParams params ) { - return getAggregatedValueMap( params, ORGUNIT_TARGET_TABLE_NAME ); + return getDoubleMap( getAggregatedValueMap( params, ORGUNIT_TARGET_TABLE_NAME ) ); } /** @@ -698,7 +711,7 @@ * * @param params the data query parameters. */ - private Map getAggregatedValueMap( DataQueryParams params, String tableName ) + private Map getAggregatedValueMap( DataQueryParams params, String tableName ) { queryPlanner.validateMaintenanceMode(); @@ -710,22 +723,22 @@ t.getSplitTime( "Planned query, got: " + queryGroups.getLargestGroupSize() + " for optimal: " + optimalQueries ); - Map map = new HashMap<>(); + Map map = new HashMap<>(); for ( List queries : queryGroups.getSequentialQueries() ) { - List>> futures = new ArrayList<>(); + List>> futures = new ArrayList<>(); for ( DataQueryParams query : queries ) { futures.add( analyticsManager.getAggregatedDataValues( query ) ); } - for ( Future> future : futures ) + for ( Future> future : futures ) { try { - Map taskValues = future.get(); + Map taskValues = future.get(); if ( taskValues != null ) { @@ -1230,7 +1243,7 @@ * in the given grid. Returns an empty map if the grid or cocIndex parameters * are null. * - * @param grid the grid. + * @param grid the grid. * @param cocIndex the category option combo index in the grid. */ private Map getCocNameMap( Grid grid, Integer cocIndex ) @@ -1263,4 +1276,36 @@ return (cores == null || cores == 0) ? SystemUtils.getCpuCores() : cores; } + + /** + * Converts a String, Object map into a specific String, Double map. + * + * @param map the map to convert. + */ + private Map getDoubleMap( Map map ) + { + Map typedMap = new HashMap<>(); + + for ( Map.Entry entry : map.entrySet() ) + { + final Object value = entry.getValue(); + + if ( value != null && Double.class.equals( value.getClass() ) ) + { + typedMap.put( entry.getKey(), (Double) entry.getValue() ); + } + } + + return typedMap; + } + + /** + * Returns the given value. If of class Double the value is rounded. + * + * @param value the value to return and potentially round. + */ + private Object getRounded( Object value ) + { + return value != null && Double.class.equals( value.getClass() ) ? MathUtils.getRounded( (Double) value ) : value; + } } === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2014-08-25 10:18:10 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2014-09-22 15:30:14 +0000 @@ -52,6 +52,7 @@ import org.hisp.dhis.analytics.AggregationType; import org.hisp.dhis.analytics.DataQueryGroups; import org.hisp.dhis.analytics.DataQueryParams; +import org.hisp.dhis.analytics.DataType; import org.hisp.dhis.analytics.Partitions; import org.hisp.dhis.analytics.QueryPlanner; import org.hisp.dhis.analytics.partition.PartitionManager; @@ -233,29 +234,34 @@ for ( DataQueryParams byPeriodType : groupedByPeriodType ) { - List groupedByAggregationType = groupByAggregationType( byPeriodType ); - - for ( DataQueryParams byAggregationType : groupedByAggregationType ) + List groupedByDataType = groupByDataType( byPeriodType ); + + for ( DataQueryParams byDataType : groupedByDataType ) { - if ( AVERAGE_INT_DISAGGREGATION.equals( byAggregationType.getAggregationType() ) ) - { - List groupedByDataPeriodType = groupByDataPeriodType( byAggregationType ); - - for ( DataQueryParams byDataPeriodType : groupedByDataPeriodType ) - { - byDataPeriodType.setPartitions( byPartition.getPartitions() ); - byDataPeriodType.setPeriodType( byPeriodType.getPeriodType() ); - byDataPeriodType.setAggregationType( byAggregationType.getAggregationType() ); - - queries.add( byDataPeriodType ); - } - } - else - { - byAggregationType.setPartitions( byPartition.getPartitions() ); - byAggregationType.setPeriodType( byPeriodType.getPeriodType() ); - - queries.add( byAggregationType ); + List groupedByAggregationType = groupByAggregationType( byDataType ); + + for ( DataQueryParams byAggregationType : groupedByAggregationType ) + { + if ( AVERAGE_INT_DISAGGREGATION.equals( byAggregationType.getAggregationType() ) ) + { + List groupedByDataPeriodType = groupByDataPeriodType( byAggregationType ); + + for ( DataQueryParams byDataPeriodType : groupedByDataPeriodType ) + { + byDataPeriodType.setPartitions( byPartition.getPartitions() ); + byDataPeriodType.setPeriodType( byPeriodType.getPeriodType() ); + byDataPeriodType.setAggregationType( byAggregationType.getAggregationType() ); + + queries.add( byDataPeriodType ); + } + } + else + { + byAggregationType.setPartitions( byPartition.getPartitions() ); + byAggregationType.setPeriodType( byPeriodType.getPeriodType() ); + + queries.add( byAggregationType ); + } } } } @@ -500,7 +506,38 @@ return queries; } - + + private List groupByDataType( DataQueryParams params ) + { + List queries = new ArrayList<>(); + + if ( params.getDataElements() != null && !params.getDataElements().isEmpty() ) + { + ListMap dataTypeDataElementMap = getDataTypeDataElementMap( params.getDataElements() ); + + for ( DataType dataType : dataTypeDataElementMap.keySet() ) + { + DataQueryParams query = params.instance(); + query.setDataElements( dataTypeDataElementMap.get( dataType ) ); + query.setDataType( dataType ); + queries.add( query ); + } + } + else + { + DataQueryParams query = params.instance(); + query.setDataType( DataType.NUMERIC ); + queries.add( query ); + } + + if ( queries.size() > 1 ) + { + log.debug( "Split on data type: " + queries.size() ); + } + + return queries; + } + /** * Groups the given query in sub queries based on the aggregation type of its * data elements. The aggregation type can be sum, average aggregation or @@ -578,7 +615,7 @@ return queries; } - + /** * Groups the given query in sub queries based on the period type of its * data elements. Sets the data period type on each query. @@ -634,7 +671,26 @@ return map; } - + + /** + * Creates a mapping between data type and data element for the given data elements. + */ + private ListMap getDataTypeDataElementMap( Collection dataElements ) + { + ListMap map = new ListMap<>(); + + for ( NameableObject element : dataElements ) + { + DataElement de = (DataElement) element; + + DataType dataType = DataElement.VALUE_TYPE_STRING.equals( de.getType() ) ? DataType.TEXT : DataType.NUMERIC; + + map.putValue( dataType, de ); + } + + return map; + } + /** * Creates a mapping between the aggregation type and data element for the * given data elements and period type. === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2014-08-15 07:40:20 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2014-09-22 15:30:14 +0000 @@ -32,11 +32,13 @@ import static org.hisp.dhis.analytics.AggregationType.AVERAGE_INT; import static org.hisp.dhis.analytics.AggregationType.AVERAGE_INT_DISAGGREGATION; import static org.hisp.dhis.analytics.AggregationType.COUNT; +import static org.hisp.dhis.analytics.AggregationType.MAX; +import static org.hisp.dhis.analytics.AggregationType.MIN; import static org.hisp.dhis.analytics.AggregationType.STDDEV; import static org.hisp.dhis.analytics.AggregationType.VARIANCE; -import static org.hisp.dhis.analytics.AggregationType.MIN; -import static org.hisp.dhis.analytics.AggregationType.MAX; +import static org.hisp.dhis.analytics.DataQueryParams.LEVEL_PREFIX; import static org.hisp.dhis.analytics.DataQueryParams.VALUE_ID; +import static org.hisp.dhis.analytics.DataType.TEXT; import static org.hisp.dhis.analytics.MeasureFilter.EQ; import static org.hisp.dhis.analytics.MeasureFilter.GE; import static org.hisp.dhis.analytics.MeasureFilter.GT; @@ -47,7 +49,6 @@ import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString; import static org.hisp.dhis.system.util.TextUtils.removeLastOr; import static org.hisp.dhis.system.util.TextUtils.trimEnd; -import static org.hisp.dhis.analytics.DataQueryParams.LEVEL_PREFIX; import java.util.Collection; import java.util.HashMap; @@ -110,7 +111,7 @@ // ------------------------------------------------------------------------- @Async - public Future> getAggregatedDataValues( DataQueryParams params ) + public Future> getAggregatedDataValues( DataQueryParams params ) { try { @@ -133,7 +134,7 @@ log.debug( sql ); - Map map = null; + Map map = null; try { @@ -143,7 +144,7 @@ { log.info( "Query failed, likely because the requested analytics table does not exist", ex ); - return new AsyncResult>( new HashMap() ); + return new AsyncResult>( new HashMap() ); } replaceDataPeriodsWithAggregationPeriods( map, params, dataPeriodAggregationPeriodMap ); @@ -158,7 +159,7 @@ } } - public void replaceDataPeriodsWithAggregationPeriods( Map dataValueMap, DataQueryParams params, ListMap dataPeriodAggregationPeriodMap ) + public void replaceDataPeriodsWithAggregationPeriods( Map dataValueMap, DataQueryParams params, ListMap dataPeriodAggregationPeriodMap ) { if ( params.isAggregationType( AVERAGE_INT_DISAGGREGATION ) ) { @@ -181,7 +182,7 @@ Assert.notNull( periods, dataPeriodAggregationPeriodMap.toString() ); - Double value = dataValueMap.get( key ); + Object value = dataValueMap.get( key ); for ( NameableObject period : periods ) { @@ -205,6 +206,24 @@ private String getSelectClause( DataQueryParams params ) { String sql = "select " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() ) + ", "; + + if ( params.isDataType( TEXT ) ) + { + sql += "textvalue"; + } + else // NUMERIC + { + sql += getNumericValueColumn( params ); + } + + sql += " as value "; + + return sql; + } + + private String getNumericValueColumn( DataQueryParams params ) + { + String sql = ""; if ( params.isAggregationType( AVERAGE_INT ) ) { @@ -241,9 +260,7 @@ sql += "sum(value)"; } - sql += " as value "; - - return sql; + return sql; } /** @@ -347,7 +364,12 @@ */ private String getGroupByClause( DataQueryParams params ) { - String sql = "group by " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() ); + String sql = ""; + + if ( params.isAggregation() ) + { + sql = "group by " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() ); + } return sql; } @@ -356,10 +378,10 @@ * Retrieves data from the database based on the given query and SQL and puts * into a value key and value mapping. */ - private Map getKeyValueMap( DataQueryParams params, String sql ) + private Map getKeyValueMap( DataQueryParams params, String sql ) throws BadSqlGrammarException { - Map map = new HashMap<>(); + Map map = new HashMap<>(); Timer t = new Timer().start(); @@ -369,13 +391,6 @@ while ( rowSet.next() ) { - Double value = rowSet.getDouble( VALUE_ID ); - - if ( !measureCriteriaSatisfied( params, value ) ) - { - continue; - } - StringBuilder key = new StringBuilder(); for ( DimensionalObject dim : params.getQueryDimensions() ) @@ -385,7 +400,26 @@ key.deleteCharAt( key.length() - 1 ); - map.put( key.toString(), value ); + if ( params.isDataType( TEXT ) ) + { + String value = rowSet.getString( VALUE_ID ); + + map.put( key.toString(), value ); + } + else // NUMERIC + { + Double value = rowSet.getDouble( VALUE_ID ); + + if ( value != null && Double.class.equals( value.getClass() ) ) + { + if ( !measureCriteriaSatisfied( params, (Double) value ) ) + { + continue; + } + } + + map.put( key.toString(), value ); + } } return map; === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2014-09-12 15:26:18 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2014-09-22 12:42:22 +0000 @@ -117,7 +117,7 @@ sqlCreate += col[0] + " " + col[1] + ","; } - sqlCreate += "daysxvalue " + dbl + ", daysno integer not null, value " + dbl + ") "; + sqlCreate += "daysxvalue " + dbl + ", daysno integer not null, value " + dbl + ", textvalue varchar(50000)) "; sqlCreate += statementBuilder.getTableOptions( false ); @@ -144,13 +144,15 @@ "dv.value " + statementBuilder.getRegexpMatch() + " '" + MathUtils.NUMERIC_LENIENT_REGEXP + "' " + "and ( dv.value != '0' or de.aggregationtype = 'average' or de.zeroissignificant = true ) "; - populateTable( table, "cast(dv.value as " + dbl + ")", "int", intClause ); + populateTable( table, "cast(dv.value as " + dbl + ")", "null", "int", intClause ); - populateTable( table, "1" , DataElement.VALUE_TYPE_BOOL, "dv.value = 'true'" ); + populateTable( table, "1", "null", DataElement.VALUE_TYPE_BOOL, "dv.value = 'true'" ); - populateTable( table, "0" , DataElement.VALUE_TYPE_BOOL, "dv.value = 'false'" ); - - populateTable( table, "1" , DataElement.VALUE_TYPE_TRUE_ONLY, "dv.value = 'true'" ); + populateTable( table, "0", "null", DataElement.VALUE_TYPE_BOOL, "dv.value = 'false'" ); + + populateTable( table, "1", "null", DataElement.VALUE_TYPE_TRUE_ONLY, "dv.value = 'true'" ); + + populateTable( table, "null", "dv.value", DataElement.VALUE_TYPE_STRING, null ); } return null; @@ -158,7 +160,16 @@ // TODO join categoryoptiongroupsetstructure on both categoryoptioncomboid and attributeoptioncomboid - private void populateTable( AnalyticsTable table, String valueExpression, String valueType, String clause ) + /** + * Populates the given analytics table. + * + * @param table analytics table to populate. + * @param valueExpression numeric value expression. + * @param textValueExpression textual value expression. + * @param valueType data element value type to include data for. + * @param whereClause where clause to constrain data query. + */ + private void populateTable( AnalyticsTable table, String valueExpression, String textValueExpression, String valueType, String whereClause ) { final String start = DateUtils.getMediumDateString( table.getPeriod().getStartDate() ); final String end = DateUtils.getMediumDateString( table.getPeriod().getEndDate() ); @@ -170,7 +181,7 @@ sql += col[0] + ","; } - sql += "daysxvalue, daysno, value) select "; + sql += "daysxvalue, daysno, value, textvalue) select "; for ( String[] col : getDimensionColumns( table ) ) { @@ -180,7 +191,8 @@ sql += valueExpression + " * ps.daysno as daysxvalue, " + "ps.daysno as daysno, " + - valueExpression + " as value " + + valueExpression + " as value, " + + textValueExpression + " as textvalue " + "from datavalue dv " + "left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid " + "left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid " + @@ -196,8 +208,12 @@ "and de.domaintype = 'AGGREGATE' " + "and pe.startdate >= '" + start + "' " + "and pe.startdate <= '" + end + "' " + - "and dv.value is not null " + - "and " + clause; + "and dv.value is not null "; + + if ( whereClause != null ) + { + sql += "and " + whereClause; + } log.info( "Populate SQL: "+ sql ); === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/AnalyticsManagerTest.java' --- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/AnalyticsManagerTest.java 2014-08-15 07:40:20 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/AnalyticsManagerTest.java 2014-09-22 13:22:24 +0000 @@ -67,7 +67,7 @@ params.setDataPeriodType( new YearlyPeriodType() ); params.setAggregationType( AggregationType.AVERAGE_INT_DISAGGREGATION ); - Map dataValueMap = new HashMap<>(); + Map dataValueMap = new HashMap<>(); dataValueMap.put( BASE_UID + "A-2012-" + BASE_UID + "A", 1d ); dataValueMap.put( BASE_UID + "B-2012-" + BASE_UID + "A", 1d ); === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java' --- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2014-08-15 07:40:20 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2014-09-22 12:42:22 +0000 @@ -36,7 +36,9 @@ import static org.hisp.dhis.common.NameableObjectUtils.getList; import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_AVERAGE; import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_SUM; +import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_NONE; import static org.hisp.dhis.dataelement.DataElement.VALUE_TYPE_INT; +import static org.hisp.dhis.dataelement.DataElement.VALUE_TYPE_STRING; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertTrue; @@ -111,6 +113,8 @@ private DataElement deB; private DataElement deC; private DataElement deD; + private DataElement deE; + private DataElement deF; private DataSet dsA; private DataSet dsB; @@ -140,11 +144,14 @@ deB = createDataElement( 'B', VALUE_TYPE_INT, AGGREGATION_OPERATOR_SUM ); deC = createDataElement( 'C', VALUE_TYPE_INT, AGGREGATION_OPERATOR_AVERAGE ); deD = createDataElement( 'D', VALUE_TYPE_INT, AGGREGATION_OPERATOR_AVERAGE ); + deE = createDataElement( 'E', VALUE_TYPE_STRING, AGGREGATION_OPERATOR_NONE ); + deF = createDataElement( 'F', VALUE_TYPE_STRING, AGGREGATION_OPERATOR_NONE ); dataElementService.addDataElement( deA ); dataElementService.addDataElement( deB ); dataElementService.addDataElement( deC ); dataElementService.addDataElement( deD ); + dataElementService.addDataElement( deE ); dsA = createDataSet( 'A', pt ); dsB = createDataSet( 'B', pt ); @@ -592,7 +599,7 @@ /** * Query spans 3 period types. Splits in 3 queries for each period type, then * splits in 4 queries on data elements units to satisfy optimal for a total - * of 12 queries, because query has 2 different aggregation types. + * of 12 queries, because query has 2 different aggregation types. */ @Test public void planQueryI() @@ -653,7 +660,33 @@ assertDimensionNameNotNull( query ); } } - + + /** + * Splits in 2 queries for each aggregation type, then 2 queries for each + * data type, then 2 queries for each organisation unit to satisfy optimal + * for a total of 4 queries across 2 sequential queries. + */ + @Test + public void planQueryL() + { + DataQueryParams params = new DataQueryParams(); + params.setDataElements( getList( deA, deB, deE, deF ) ); + params.setOrganisationUnits( getList( ouA, ouB, ouC, ouD ) ); + params.setFilterPeriods( getList( createPeriod( "2000Q1" ) ) ); + + DataQueryGroups queryGroups = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); + + assertEquals( 8, queryGroups.getAllQueries().size() ); + assertEquals( 2, queryGroups.getSequentialQueries().size() ); + assertEquals( 4, queryGroups.getLargestGroupSize() ); + + for ( DataQueryParams query : queryGroups.getAllQueries() ) + { + assertDimensionNameNotNull( query ); + assertNotNull( query.getDataType() ); + } + } + // ------------------------------------------------------------------------- // Supportive methods // -------------------------------------------------------------------------