=== 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-08-21 01:47:02 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicator.java 2015-08-22 13:42:01 +0000 @@ -67,16 +67,17 @@ public static String SEP_OBJECT = ":"; - public static final String EXPRESSION_REGEXP = "(" + KEY_DATAELEMENT + "|" + KEY_ATTRIBUTE + "|" + KEY_PROGRAM_VARIABLE + "|" + KEY_CONSTANT + ")\\{(\\w+|" + + private static final String EXPRESSION_REGEXP = "(" + KEY_DATAELEMENT + "|" + KEY_ATTRIBUTE + "|" + KEY_PROGRAM_VARIABLE + "|" + KEY_CONSTANT + ")\\{(\\w+|" + INCIDENT_DATE + "|" + ENROLLMENT_DATE + "|" + CURRENT_DATE + ")" + SEPARATOR_ID + "?(\\w*)\\}"; + private static final String SQL_FUNC_REGEXP = "d2:(.+?)\\((.+?)\\)"; - public static final Pattern EXPRESSION_PATTERN = Pattern.compile( EXPRESSION_REGEXP ); + public static final Pattern EXPRESSION_PATTERN = Pattern.compile( EXPRESSION_REGEXP ); + 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 VALUECOUNT_PATTERN = Pattern.compile( "V\\{(" + VAR_VALUE_COUNT + "|" + VAR_ZERO_POS_VALUE_COUNT + ")\\}" ); public static final String VALID = "valid"; - public static final String EXPRESSION_NOT_WELL_FORMED = "expression_not_well_formed"; public static final String INVALID_IDENTIFIERS_IN_EXPRESSION = "invalid_identifiers_in_expression"; public static final String FILTER_NOT_EVALUATING_TO_TRUE_OR_FALSE = "filter_not_evaluating_to_true_or_false"; === 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-08-21 03:14:08 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/DefaultProgramIndicatorService.java 2015-08-22 13:42:01 +0000 @@ -38,6 +38,9 @@ import java.util.Set; import java.util.regex.Matcher; +import org.hisp.dhis.commons.sqlfunc.OneIfZeroOrPositiveSqlFunction; +import org.hisp.dhis.commons.sqlfunc.SqlFunction; +import org.hisp.dhis.commons.sqlfunc.ZeroIfNegativeSqlFunction; import org.hisp.dhis.commons.util.ExpressionUtils; import org.hisp.dhis.commons.util.TextUtils; import org.hisp.dhis.constant.Constant; @@ -57,12 +60,18 @@ import org.hisp.dhis.trackedentitydatavalue.TrackedEntityDataValueService; import org.springframework.transaction.annotation.Transactional; +import com.google.common.collect.ImmutableMap; + /** * @author Chau Thu Tran */ public class DefaultProgramIndicatorService implements ProgramIndicatorService { + private static final Map SQL_FUNC_MAP = ImmutableMap.builder(). + put( ZeroIfNegativeSqlFunction.KEY, new ZeroIfNegativeSqlFunction() ). + put( OneIfZeroOrPositiveSqlFunction.KEY, new OneIfZeroOrPositiveSqlFunction() ).build(); + // ------------------------------------------------------------------------- // Dependencies // ------------------------------------------------------------------------- @@ -339,7 +348,11 @@ { return null; } - + + // --------------------------------------------------------------------- + // Data elements, attributes, constants + // --------------------------------------------------------------------- + StringBuffer buffer = new StringBuffer(); Matcher matcher = ProgramIndicator.EXPRESSION_PATTERN.matcher( expression ); @@ -370,9 +383,36 @@ } } - matcher.appendTail( buffer ); - - return buffer.toString(); + expression = TextUtils.appendTail( matcher, buffer ); + + // --------------------------------------------------------------------- + // Functions + // --------------------------------------------------------------------- + + buffer = new StringBuffer(); + + matcher = ProgramIndicator.SQL_FUNC_PATTERN.matcher( expression ); + + while ( matcher.find() ) + { + String func = matcher.group( 1 ); + String column = matcher.group( 2 ); + + SqlFunction function = SQL_FUNC_MAP.get( func ); + + if ( function == null ) + { + throw new IllegalStateException( "Function not recognized: " + func ); + } + + String result = function.evaluate( column ); + + matcher.appendReplacement( buffer, result ); + } + + expression = TextUtils.appendTail( matcher, buffer ); + + return expression; } @Override === 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-07-19 14:54:19 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java 2015-08-22 13:42:01 +0000 @@ -504,11 +504,40 @@ public void testGetAnalyticsSQl() { String expected = COL_QUOTE + deA.getUid() + COL_QUOTE + " + " + COL_QUOTE + atA.getUid() + COL_QUOTE + " > 10"; - String sql = programIndicatorService.getAnalyticsSQl( indicatorE.getFilter() ); - System.out.println("SQL " + sql); - assertEquals( expected, sql ); - } - + + assertEquals( expected, programIndicatorService.getAnalyticsSQl( indicatorE.getFilter() ) ); + } + + @Test + public void testGetAnalyticsSqlWithFunctionsA() + { + String col = COL_QUOTE + deA.getUid() + COL_QUOTE; + String expected = "case when " + col + " < 0 then 0 else " + col + " end"; + String expression = "d2:zing(" + col + ")"; + + assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) ); + } + + @Test + public void testGetAnalyticsSqlWithFunctionsB() + { + String col = COL_QUOTE + deA.getUid() + COL_QUOTE; + String expected = "case when " + col + " >= 0 then 1 else " + col + " end"; + String expression = "d2:oizp(" + col + ")"; + + assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) ); + } + + @Test( expected = IllegalStateException.class ) + public void testGetAnalyticsSqlWithFunctionsC() + { + String col = COL_QUOTE + deA.getUid() + COL_QUOTE; + String expected = "case when " + col + " >= 0 then 1 else " + col + " end"; + String expression = "d2:xyza(" + col + ")"; + + assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) ); + } + @Test public void testExpressionIsValid() { === added directory 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc' === added file 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/OneIfZeroOrPositiveSqlFunction.java' --- dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/OneIfZeroOrPositiveSqlFunction.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/OneIfZeroOrPositiveSqlFunction.java 2015-08-22 14:51:53 +0000 @@ -0,0 +1,47 @@ +package org.hisp.dhis.commons.sqlfunc; + +/* + * Copyright (c) 2004-2015, 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. + */ + +/** + * Function which evaluates numerical values to one if zero or positive, zero + * otherwise. + * + * @author Lars Helge Overland + */ +public class OneIfZeroOrPositiveSqlFunction + implements SqlFunction +{ + public static final String KEY = "oizp"; + + @Override + public String evaluate( String column ) + { + return "case when " + column + " >= 0 then 1 else 0 end"; + } +} === added file 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/SqlFunction.java' --- dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/SqlFunction.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/SqlFunction.java 2015-08-22 13:42:01 +0000 @@ -0,0 +1,43 @@ +package org.hisp.dhis.commons.sqlfunc; + +/* + * Copyright (c) 2004-2015, 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 interface SqlFunction +{ + /** + * Evaluates the function using the given column name. + * + * @param column the quoted column name. + * @return the result of the evaluation. + */ + String evaluate( String column ); +} === added file 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java' --- dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java 2015-08-22 14:51:53 +0000 @@ -0,0 +1,47 @@ +package org.hisp.dhis.commons.sqlfunc; + +/* + * Copyright (c) 2004-2015, 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. + */ + +/** + * Function which evaluates numerical values to zero if negative, unchanged if + * zero or positive. + * + * @author Lars Helge Overland + */ +public class ZeroIfNegativeSqlFunction + implements SqlFunction +{ + public static final String KEY = "zing"; + + @Override + public String evaluate( String column ) + { + return "case when " + column + " < 0 then 0 else " + column + " end"; + } +} === modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/programIndicatorForm.vm' --- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/programIndicatorForm.vm 2015-08-20 01:06:34 +0000 +++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/programIndicatorForm.vm 2015-08-22 14:45:42 +0000 @@ -5,7 +5,7 @@ $i18n.getString( "expression" ) -
The expression defines how the indicator is calculated
+
The expression defines how the indicator is calculated.
@@ -114,7 +114,7 @@ - $i18n.getString( "expression" ) Tip: use abs(x) sin(x) cos(x) tan(x) ln(x) log(x) sqrt(x) mod(x,y) zing(x) + $i18n.getString( "expression" ) Tip: use zing(x) oizp(x) @@ -234,7 +234,7 @@ - $i18n.getString( "filter" ) Tip: use abs(x) sin(x) cos(x) tan(x) ln(x) log(x) sqrt(x) mod(x,y) oizp(x) zing(x) + $i18n.getString( "filter" ) Tip: use oizp(x) zing(x)