=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicator.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicator.java 2015-09-07 11:47:06 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicator.java 2015-09-07 17:36:38 +0000 @@ -79,6 +79,7 @@ public static final Pattern SQL_FUNC_PATTERN = Pattern.compile( SQL_FUNC_REGEXP ); public static final Pattern DATAELEMENT_PATTERN = Pattern.compile( KEY_DATAELEMENT + "\\{(\\w{11})" + SEPARATOR_ID + "(\\w{11})\\}" ); public static final Pattern ATTRIBUTE_PATTERN = Pattern.compile( KEY_ATTRIBUTE + "\\{(\\w{11})\\}" ); + public static final Pattern VARIABLE_PATTERN = Pattern.compile( KEY_PROGRAM_VARIABLE + "\\{([\\w\\_]+)}" ); public static final Pattern VALUECOUNT_PATTERN = Pattern.compile( "V\\{(" + VAR_VALUE_COUNT + "|" + VAR_ZERO_POS_VALUE_COUNT + ")\\}" ); public static final String VALID = "valid"; === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicatorService.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicatorService.java 2015-09-07 12:10:27 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicatorService.java 2015-09-07 17:36:38 +0000 @@ -30,10 +30,6 @@ import java.util.List; import java.util.Map; -import java.util.Set; - -import org.hisp.dhis.constant.Constant; -import org.hisp.dhis.trackedentity.TrackedEntityAttribute; /** * @author Chau Thu Tran @@ -128,14 +124,24 @@ * @return The description */ String getExpressionDescription( String expression ); + + /** + * Get the expression as an analytics SQL clause. Ignores missing numeric + * values for data elements and attributes. + * + * @param expression the expression. + * @return the SQL string. + */ + String getAnalyticsSQl( String expression ); /** * Get the expression as an analytics SQL clause. * * @param expression the expression. + * @param whether to ignore missing values for data elements and attributes. * @return the SQL string. */ - String getAnalyticsSQl( String expression ); + String getAnalyticsSQl( String expression, boolean ignoreMissingValues ); /** * Indicates whether the given program indicator expression is valid. === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java 2015-09-03 16:16:57 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java 2015-09-07 17:36:38 +0000 @@ -537,7 +537,7 @@ if ( params.hasProgramIndicatorDimension() && params.getProgramIndicator().hasFilter() ) { - String filter = programIndicatorService.getAnalyticsSQl( params.getProgramIndicator().getFilter() ); + String filter = programIndicatorService.getAnalyticsSQl( params.getProgramIndicator().getFilter(), false ); String sqlFilter = ExpressionUtils.asSql( filter ); === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/DefaultProgramIndicatorService.java' --- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/DefaultProgramIndicatorService.java 2015-09-07 12:10:27 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/DefaultProgramIndicatorService.java 2015-09-07 17:36:38 +0000 @@ -67,6 +67,8 @@ import com.google.common.collect.ImmutableMap; +import static org.apache.commons.lang3.StringUtils.trim; + /** * @author Chau Thu Tran */ @@ -462,70 +464,43 @@ @Override public String getAnalyticsSQl( String expression ) { - if ( expression == null ) - { - return null; - } - - // --------------------------------------------------------------------- - // Data elements, attributes, constants - // --------------------------------------------------------------------- - + return getAnalyticsSQl( expression, true ); + } + + @Override + public String getAnalyticsSQl( String expression, boolean ignoreMissingValues ) + { + if ( expression == null ) + { + return null; + } + + expression = getSubstitutedVariablesForAnalyticsSql( expression ); + + expression = getSubstitutedFunctionsAnalyticsSql( expression, false ); + + expression = getSubstitutedElementsAnalyticsSql( expression, ignoreMissingValues ); + + return expression; + } + + private String getSubstitutedFunctionsAnalyticsSql( String expression, boolean ignoreMissingValues ) + { + if ( expression == null ) + { + return null; + } + StringBuffer buffer = new StringBuffer(); - Matcher matcher = ProgramIndicator.EXPRESSION_PATTERN.matcher( expression ); - - while ( matcher.find() ) - { - String key = matcher.group( 1 ); - String val = matcher.group( 2 ); - - if ( ProgramIndicator.KEY_DATAELEMENT.equals( key ) ) - { - String de = statementBuilder.columnQuote( matcher.group( 3 ) ); - - matcher.appendReplacement( buffer, de ); - } - else if ( ProgramIndicator.KEY_ATTRIBUTE.equals( key ) ) - { - matcher.appendReplacement( buffer, statementBuilder.columnQuote( val ) ); - } - else if ( ProgramIndicator.KEY_CONSTANT.equals( key ) ) - { - Constant constant = constantService.getConstant( val ); - - if ( constant != null ) - { - matcher.appendReplacement( buffer, String.valueOf( constant.getValue() ) ); - } - } - else if ( ProgramIndicator.KEY_PROGRAM_VARIABLE.equals( key ) ) - { - String sql = getVariableAsSql( val, expression ); - - if ( sql != null ) - { - matcher.appendReplacement( buffer, sql ); - } - } - } - - expression = TextUtils.appendTail( matcher, buffer ); - - // --------------------------------------------------------------------- - // Functions - // --------------------------------------------------------------------- - - buffer = new StringBuffer(); - - matcher = ProgramIndicator.SQL_FUNC_PATTERN.matcher( expression ); + Matcher matcher = ProgramIndicator.SQL_FUNC_PATTERN.matcher( expression ); while ( matcher.find() ) { String func = StringUtils.trim( matcher.group( 1 ) ); - String arg1 = StringUtils.trim( matcher.group( 2 ) ); - String arg2 = StringUtils.trim( matcher.group( 3 ) ); - String arg3 = StringUtils.trim( matcher.group( 4 ) ); + String arg1 = getSubstitutedElementsAnalyticsSql( trim( matcher.group( 2 ) ), false ); + String arg2 = getSubstitutedElementsAnalyticsSql( trim( matcher.group( 3 ) ), false ); + String arg3 = getSubstitutedElementsAnalyticsSql( trim( matcher.group( 4 ) ), false ); SqlFunction function = SQL_FUNC_MAP.get( func ); @@ -539,11 +514,78 @@ matcher.appendReplacement( buffer, result ); } - expression = TextUtils.appendTail( matcher, buffer ); - - return expression; - } - + return TextUtils.appendTail( matcher, buffer ); + } + + private String getSubstitutedVariablesForAnalyticsSql( String expression ) + { + if ( expression == null ) + { + return null; + } + + StringBuffer buffer = new StringBuffer(); + + Matcher matcher = ProgramIndicator.VARIABLE_PATTERN.matcher( expression ); + + while ( matcher.find() ) + { + String var = matcher.group( 1 ); + + String sql = getVariableAsSql( var, expression ); + + if ( sql != null ) + { + matcher.appendReplacement( buffer, sql ); + } + } + + return TextUtils.appendTail( matcher, buffer ); + } + + private String getSubstitutedElementsAnalyticsSql( String expression, boolean ignoreMissingValues ) + { + if ( expression == null ) + { + return null; + } + + StringBuffer buffer = new StringBuffer(); + + Matcher matcher = ProgramIndicator.EXPRESSION_PATTERN.matcher( expression ); + + while ( matcher.find() ) + { + String key = matcher.group( 1 ); + String el1 = matcher.group( 2 ); + String el2 = matcher.group( 3 ); + + if ( ProgramIndicator.KEY_DATAELEMENT.equals( key ) ) + { + String de = ignoreMissingValues ? getIgnoreNullSql( statementBuilder.columnQuote( el2 ) ) : statementBuilder.columnQuote( el2 ); + + matcher.appendReplacement( buffer, de ); + } + else if ( ProgramIndicator.KEY_ATTRIBUTE.equals( key ) ) + { + String at = ignoreMissingValues ? getIgnoreNullSql( statementBuilder.columnQuote( el1 ) ) : statementBuilder.columnQuote( el1 ); + + matcher.appendReplacement( buffer, at ); + } + else if ( ProgramIndicator.KEY_CONSTANT.equals( key ) ) + { + Constant constant = constantService.getConstant( el1 ); + + if ( constant != null ) + { + matcher.appendReplacement( buffer, String.valueOf( constant.getValue() ) ); + } + } + } + + return TextUtils.appendTail( matcher, buffer ); + } + @Override @Transactional public String expressionIsValid( String expression ) @@ -692,7 +734,7 @@ sql += "case when " + statementBuilder.columnQuote( uid ) + " is not null then 1 else 0 end + "; } - return TextUtils.removeLast( sql, "+" ) + "),0)"; + return TextUtils.removeLast( sql, "+" ).trim() + "),0)"; } else if ( ProgramIndicator.VAR_ZERO_POS_VALUE_COUNT.equals( var ) ) { @@ -703,12 +745,17 @@ sql += "case when " + statementBuilder.columnQuote( uid ) + " > 0 then 1 else 0 end + "; } - return TextUtils.removeLast( sql, "+" ) + "),0)"; + return TextUtils.removeLast( sql, "+" ).trim() + "),0)"; } return null; } + private String getIgnoreNullSql( String column ) + { + return "coalesce(" + column + ",0)"; + } + private boolean isZeroOrPositive( String value ) { return MathUtils.isNumeric( value ) && Double.valueOf( value ) >= 0d; === modified file 'dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java' --- dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java 2015-09-07 12:10:27 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java 2015-09-07 17:36:38 +0000 @@ -470,12 +470,36 @@ @Test public void testGetAnalyticsSQl() { - String expected = COL_QUOTE + deA.getUid() + COL_QUOTE + " + " + COL_QUOTE + atA.getUid() + COL_QUOTE + " > 10"; + String expected = "coalesce(\"" + deA.getUid() + "\",0) + coalesce(\"" + atA.getUid() + "\",0) > 10"; assertEquals( expected, programIndicatorService.getAnalyticsSQl( indicatorE.getFilter() ) ); } @Test + public void testGetAnalyticsSQlRespectMissingValues() + { + String expected = "\"" + deA.getUid() + "\" + \"" + atA.getUid() + "\" > 10"; + + assertEquals( expected, programIndicatorService.getAnalyticsSQl( indicatorE.getFilter(), false ) ); + } + + @Test + public void testGetAnalyticsWithVariables() + { + String expected = + "coalesce(case when \"EZq9VbPWgML\" < 0 then 0 else \"EZq9VbPWgML\" end, 0) + " + + "coalesce(\"GCyeKSqlpdk\",0) + " + + "nullif((case when \"EZq9VbPWgML\" > 0 then 1 else 0 end + case when \"GCyeKSqlpdk\" > 0 then 1 else 0 end),0)"; + + String expression = + "d2:zing(#{OXXcwl6aPCQ.EZq9VbPWgML}) + " + + "#{OXXcwl6aPCQ.GCyeKSqlpdk} + " + + "V{zero_pos_value_count}"; + + assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) ); + } + + @Test public void testGetAnalyticsSqlWithFunctionsZingA() { String col = COL_QUOTE + deA.getUid() + COL_QUOTE; @@ -563,7 +587,7 @@ @Test public void testGetAnalyticsSqlWithVariables() { - String expected = "\"EZq9VbPWgML\" + (executiondate - enrollmentdate)"; + String expected = "coalesce(\"EZq9VbPWgML\",0) + (executiondate - enrollmentdate)"; String expression = "#{OXXcwl6aPCQ.EZq9VbPWgML} + (V{execution_date} - V{enrollment_date})"; assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) );