dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #39567
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 20032: Analytics, program indicators. Ignoring missing event values by replacing with 0 in expressions.
------------------------------------------------------------
revno: 20032
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2015-09-07 19:36:38 +0200
message:
Analytics, program indicators. Ignoring missing event values by replacing with 0 in expressions.
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicator.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicatorService.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java
dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/DefaultProgramIndicatorService.java
dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== 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 ) );
