=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java 2013-02-19 11:54:49 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java 2013-02-21 18:18:15 +0000 @@ -38,6 +38,7 @@ public static final String TABLE_TEMP_SUFFIX = "_temp"; public static final String ANALYTICS_TABLE_NAME = "analytics"; public static final String COMPLETENESS_TABLE_NAME = "completeness"; + public static final String COMPLETENESS_TARGET_TABLE_NAME = "completenesstarget"; /** * Returns the base table name. === 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 2013-02-19 16:12:47 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2013-02-21 18:18:15 +0000 @@ -28,6 +28,7 @@ */ import static org.hisp.dhis.analytics.AggregationType.AVERAGE_INT_DISAGGREGATION; +import static org.hisp.dhis.analytics.DimensionType.*; import java.util.ArrayList; import java.util.Arrays; @@ -68,6 +69,8 @@ public static final List DATA_DIMS = Arrays.asList( INDICATOR_DIM_ID, DATAELEMENT_DIM_ID, DATASET_DIM_ID ); public static final List FIXED_DIMS = Arrays.asList( DATA_X_DIM_ID, INDICATOR_DIM_ID, DATAELEMENT_DIM_ID, DATASET_DIM_ID, PERIOD_DIM_ID, ORGUNIT_DIM_ID ); + private static final List COMPLETENESS_DIMENSION_TYTPES = Arrays.asList( DATASET, ORGANISATIONUNIT, ORGANISATIONUNIT_GROUPSET ); + private static final DimensionOption[] DIM_OPT_ARR = new DimensionOption[0]; private static final DimensionOption[][] DIM_OPT_2D_ARR = new DimensionOption[0][]; @@ -91,6 +94,8 @@ private transient PeriodType dataPeriodType; + private transient boolean skipPartitioning; + // ------------------------------------------------------------------------- // Constructors // ------------------------------------------------------------------------- @@ -110,6 +115,7 @@ this.periodType = params.getPeriodType(); this.organisationUnitLevel = params.getOrganisationUnitLevel(); this.dataPeriodType = params.getDataPeriodType(); + this.skipPartitioning = params.isSkipPartitioning(); } // ------------------------------------------------------------------------- @@ -170,6 +176,42 @@ return list; } + + /** + * Creates a list of dimensions which are relevant to completeness queries. + */ + public List getCompletenessDimensions() + { + List list = new ArrayList(); + + for ( Dimension dim : dimensions ) + { + if ( COMPLETENESS_DIMENSION_TYTPES.contains( dim.getType() ) ) + { + list.add( dim ); + } + } + + return list; + } + + /** + * Creates a list of dimensions which are relevant to completeness queries. + */ + public List getCompletenessFilters() + { + List list = new ArrayList(); + + for ( Dimension dim : filters ) + { + if ( COMPLETENESS_DIMENSION_TYTPES.contains( dim.getType() ) ) + { + list.add( dim ); + } + } + + return list; + } /** * Removes the dimension with the given identifier. @@ -689,7 +731,17 @@ { this.dataPeriodType = dataPeriodType; } - + + public boolean isSkipPartitioning() + { + return skipPartitioning; + } + + public void setSkipPartitioning( boolean skipPartitioning ) + { + this.skipPartitioning = skipPartitioning; + } + // ------------------------------------------------------------------------- // Get and set helpers for dimensions or filter // ------------------------------------------------------------------------- === 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 2013-02-21 15:21:16 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2013-02-21 18:18:15 +0000 @@ -29,6 +29,7 @@ import static org.hisp.dhis.analytics.AnalyticsTableManager.ANALYTICS_TABLE_NAME; import static org.hisp.dhis.analytics.AnalyticsTableManager.COMPLETENESS_TABLE_NAME; +import static org.hisp.dhis.analytics.AnalyticsTableManager.COMPLETENESS_TARGET_TABLE_NAME; import static org.hisp.dhis.analytics.DataQueryParams.CATEGORYOPTIONCOMBO_DIM_ID; import static org.hisp.dhis.analytics.DataQueryParams.DATAELEMENT_DIM_ID; import static org.hisp.dhis.analytics.DataQueryParams.DATASET_DIM_ID; @@ -242,15 +243,22 @@ // --------------------------------------------------------------------- if ( params.getDataSets() != null ) - { + { DataQueryParams dataSourceParams = new DataQueryParams( params ); dataSourceParams.removeDimension( INDICATOR_DIM_ID ); dataSourceParams.removeDimension( DATAELEMENT_DIM_ID ); - dataSourceParams.removeDimension( CATEGORYOPTIONCOMBO_DIM_ID ); dataSourceParams.setAggregationType( AggregationType.COUNT ); Map aggregatedDataMap = getAggregatedCompletenessValueMap( dataSourceParams ); + DataQueryParams dataTargetParams = new DataQueryParams( params ); + dataTargetParams.setDimensions( dataTargetParams.getCompletenessDimensions() ); + dataTargetParams.setFilters( dataTargetParams.getCompletenessFilters() ); + dataTargetParams.setAggregationType( AggregationType.COUNT ); + dataTargetParams.setSkipPartitioning( true ); + + Map targetMap = getAggregatedCompletenessTargetMap( dataTargetParams ); //TODO + for ( Map.Entry entry : aggregatedDataMap.entrySet() ) { List row = new ArrayList( Arrays.asList( entry.getKey().split( DIMENSION_SEP ) ) ); @@ -302,6 +310,12 @@ { return getAggregatedValueMap( params, COMPLETENESS_TABLE_NAME ); } + + private Map getAggregatedCompletenessTargetMap( DataQueryParams params ) + throws IllegalQueryException, Exception + { + return getAggregatedValueMap( params, COMPLETENESS_TARGET_TABLE_NAME ); + } /** * Generates a mapping between a dimension key and the aggregated value. The === 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 2013-02-19 13:19:21 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2013-02-21 18:18:15 +0000 @@ -85,7 +85,7 @@ throw new IllegalQueryException( "Dimensions cannot be specified as dimension and filter simultaneously: " + params.dimensionsAsFilters() ); } - if ( !params.hasPeriods() ) + if ( !params.hasPeriods() && !params.isSkipPartitioning() ) { throw new IllegalQueryException( "At least one period must be specified as dimension or filter" ); } @@ -112,7 +112,7 @@ // --------------------------------------------------------------------- params = new DataQueryParams( params ); - + List queries = new ArrayList(); List groupedByPartition = groupByPartition( params, tableName ); @@ -233,7 +233,12 @@ { List queries = new ArrayList(); - if ( params.getPeriods() != null && !params.getPeriods().isEmpty() ) + if ( params.isSkipPartitioning() ) + { + params.setTableName( tableName ); + queries.add( params ); + } + else if ( params.getPeriods() != null && !params.getPeriods().isEmpty() ) { ListMap tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getPeriods(), tableName ); @@ -273,7 +278,11 @@ { List queries = new ArrayList(); - if ( params.getPeriods() != null && !params.getPeriods().isEmpty() ) + if ( params.isSkipPartitioning() ) + { + queries.add( params ); + } + else if ( params.getPeriods() != null && !params.getPeriods().isEmpty() ) { ListMap periodTypePeriodMap = getPeriodTypePeriodMap( params.getPeriods() ); === 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 2013-02-21 14:37:09 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2013-02-21 18:18:15 +0000 @@ -92,19 +92,19 @@ { ListMap dataPeriodAggregationPeriodMap = params.getDataPeriodAggregationPeriodMap(); params.replaceAggregationPeriodsWithDataPeriods( dataPeriodAggregationPeriodMap ); - + params.populateDimensionNames(); - - List queryDimensions = params.getQueryDimensions(); - + + List dimensions = params.getQueryDimensions(); + SqlHelper sqlHelper = new SqlHelper(); - int days = PeriodType.getPeriodTypeByName( params.getPeriodType() ).getFrequencyOrder(); - - String sql = "select " + getCommaDelimitedString( queryDimensions ) + ", "; + String sql = "select " + getCommaDelimitedString( dimensions ) + ", "; if ( params.isAggregationType( AVERAGE_INT ) ) { + int days = PeriodType.getPeriodTypeByName( params.getPeriodType() ).getFrequencyOrder(); + sql += "sum(daysxvalue) / " + days; } else if ( params.isAggregationType( AVERAGE_BOOL ) ) @@ -119,10 +119,10 @@ { sql += "sum(value)"; } - + sql += " as value from " + params.getTableName() + " "; - for ( Dimension dim : queryDimensions ) + for ( Dimension dim : dimensions ) { if ( !dim.isAllOptions() ) { @@ -138,7 +138,7 @@ } } - sql += "group by " + getCommaDelimitedString( queryDimensions ); + sql += "group by " + getCommaDelimitedString( dimensions ); log.info( sql ); @@ -168,7 +168,7 @@ StringBuilder key = new StringBuilder(); - for ( Dimension dim : queryDimensions ) + for ( Dimension dim : dimensions ) { key.append( rowSet.getString( dim.getDimensionName() ) + DIMENSION_SEP ); } === 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 2013-02-19 11:54:49 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2013-02-21 18:18:15 +0000 @@ -69,8 +69,7 @@ // Implementation // ------------------------------------------------------------------------- - //TODO create stack with index tasks for partition and column - // run continuously from stack until empty + //TODO use statement builder for double column type public String getTableName() { @@ -129,23 +128,21 @@ final String start = DateUtils.getMediumDateString( startDate ); final String end = DateUtils.getMediumDateString( endDate ); - String insert = "insert into " + tableName + " ("; - - for ( String[] col : getDimensionColumns() ) - { - insert += col[0] + ","; - } - - insert += "daysxvalue, daysno, value) "; - - String select = "select "; - - for ( String[] col : getDimensionColumns() ) - { - select += col[2] + ","; - } - - select += + String sql = "insert into " + tableName + " ("; + + for ( String[] col : getDimensionColumns() ) + { + sql += col[0] + ","; + } + + sql += "daysxvalue, daysno, value) select "; + + for ( String[] col : getDimensionColumns() ) + { + sql += col[2] + ","; + } + + sql += valueExpression + " * ps.daysno as daysxvalue, " + "ps.daysno as daysno, " + valueExpression + " as value " + @@ -163,8 +160,6 @@ "and dv.value is not null " + "and " + clause; - final String sql = insert + select; - log.info( "Populate SQL: "+ sql ); jdbcTemplate.execute( sql ); === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java 2013-02-19 11:54:49 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java 2013-02-21 18:18:15 +0000 @@ -59,7 +59,7 @@ sqlCreate += col[0] + " " + col[1] + ","; } - sqlCreate = sqlCreate.substring( 0, sqlCreate.length() - 1 ) + ")"; + sqlCreate += "value double precision)"; log.info( "Create SQL: " + sqlCreate ); @@ -85,18 +85,15 @@ sql += col[0] + ","; } - sql = sql.substring( 0, sql.length() - 1 ); - - sql += ") select "; + sql += "value) select "; for ( String[] col : getDimensionColumns() ) { sql += col[2] + ","; } - - sql = sql.substring( 0, sql.length() - 1 ) + " "; - + sql += + "1 as value " + "from datasetsource dss " + "left join dataset ds on dss.datasetid=ds.datasetid " + "left join _orgunitstructure ous on dss.sourceid=ous.organisationunitid " +