=== 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-25 14:09:25 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicator.java 2015-08-27 17:18:00 +0000 @@ -74,7 +74,7 @@ private static final String EXPRESSION_REGEXP = "(" + KEY_DATAELEMENT + "|" + KEY_ATTRIBUTE + "|" + KEY_PROGRAM_VARIABLE + "|" + KEY_CONSTANT + ")\\{(\\w+|" + VAR_INCIDENT_DATE + "|" + VAR_ENROLLMENT_DATE + "|" + VAR_CURRENT_DATE + ")" + SEPARATOR_ID + "?(\\w*)\\}"; - private static final String SQL_FUNC_REGEXP = "d2:(.+?)\\((.+?)\\)"; + private static final String SQL_FUNC_REGEXP = "d2:(.+?)\\(([^,]+)\\,?([^,]*)\\,?([^,]*)\\)"; public static final Pattern EXPRESSION_PATTERN = Pattern.compile( EXPRESSION_REGEXP ); public static final Pattern SQL_FUNC_PATTERN = Pattern.compile( SQL_FUNC_REGEXP ); === 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-25 18:37:50 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/program/DefaultProgramIndicatorService.java 2015-08-27 17:18:00 +0000 @@ -38,6 +38,7 @@ import java.util.Set; import java.util.regex.Matcher; +import org.hisp.dhis.commons.sqlfunc.DaysBetweenSqlFunction; import org.hisp.dhis.commons.sqlfunc.OneIfZeroOrPositiveSqlFunction; import org.hisp.dhis.commons.sqlfunc.SqlFunction; import org.hisp.dhis.commons.sqlfunc.ZeroIfNegativeSqlFunction; @@ -73,7 +74,8 @@ { private static final Map SQL_FUNC_MAP = ImmutableMap.builder(). put( ZeroIfNegativeSqlFunction.KEY, new ZeroIfNegativeSqlFunction() ). - put( OneIfZeroOrPositiveSqlFunction.KEY, new OneIfZeroOrPositiveSqlFunction() ).build(); + put( OneIfZeroOrPositiveSqlFunction.KEY, new OneIfZeroOrPositiveSqlFunction() ). + put( DaysBetweenSqlFunction.KEY, new DaysBetweenSqlFunction() ).build(); // ------------------------------------------------------------------------- // Dependencies @@ -398,7 +400,9 @@ while ( matcher.find() ) { String func = matcher.group( 1 ); - String column = matcher.group( 2 ); + String arg1 = matcher.group( 2 ); + String arg2 = matcher.group( 3 ); + String arg3 = matcher.group( 4 ); SqlFunction function = SQL_FUNC_MAP.get( func ); @@ -407,7 +411,7 @@ throw new IllegalStateException( "Function not recognized: " + func ); } - String result = function.evaluate( column ); + String result = function.evaluate( arg1, arg2, arg3 ); matcher.appendReplacement( buffer, result ); } @@ -481,7 +485,7 @@ if ( programStage != null && dataElement != null ) { - String sample = dataElement.isNumericType() ? String.valueOf( 1 ) : "'A'"; + String sample = dataElement.isNumericType() ? String.valueOf( 1 ) : dataElement.isDateType() ? "'2000-01-01'" : "'A'"; matcher.appendReplacement( expr, sample ); } @@ -496,7 +500,7 @@ if ( attribute != null ) { - String sample = attribute.isNumericType() ? String.valueOf( 1 ) : "'A'"; + String sample = attribute.isNumericType() ? String.valueOf( 1 ) : attribute.isDateType() ? "'2000-01-01'" : "'A'"; matcher.appendReplacement( expr, sample ); } === 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-08-23 13:22:07 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java 2015-08-27 17:18:00 +0000 @@ -528,8 +528,19 @@ assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) ); } + @Test + public void testGetAnalyticsSqlWithFunctionsC() + { + String col1 = COL_QUOTE + deA.getUid() + COL_QUOTE; + String col2 = COL_QUOTE + deB.getUid() + COL_QUOTE; + String expected = "(cast(" + col2 + " as date) - cast(" + col1 + " as date))"; + String expression = "d2:daysBetween(" + col1 + "," + col2 + ")"; + + assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) ); + } + @Test( expected = IllegalStateException.class ) - public void testGetAnalyticsSqlWithFunctionsC() + public void testGetAnalyticsSqlWithFunctionsInvalid() { String col = COL_QUOTE + deA.getUid() + COL_QUOTE; String expected = "case when " + col + " >= 0 then 1 else " + col + " end"; === added file 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/DaysBetweenSqlFunction.java' --- dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/DaysBetweenSqlFunction.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/DaysBetweenSqlFunction.java 2015-08-27 17:18:00 +0000 @@ -0,0 +1,44 @@ +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 class DaysBetweenSqlFunction + implements SqlFunction +{ + public static final String KEY = "daysBetween"; + + @Override + public String evaluate( String arg1, String arg2, String arg3 ) + { + return "(cast(" + arg2 + " as date) - cast(" + arg1 + " as date))"; + } +} === modified 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 2015-08-22 14:51:53 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/OneIfZeroOrPositiveSqlFunction.java 2015-08-27 17:18:00 +0000 @@ -40,8 +40,8 @@ public static final String KEY = "oizp"; @Override - public String evaluate( String column ) + public String evaluate( String arg1, String arg2, String arg ) { - return "case when " + column + " >= 0 then 1 else 0 end"; + return "case when " + arg1 + " >= 0 then 1 else 0 end"; } } === modified 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 2015-08-22 13:42:01 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/SqlFunction.java 2015-08-27 17:18:00 +0000 @@ -36,8 +36,11 @@ /** * Evaluates the function using the given column name. * - * @param column the quoted column name. + * @param arg1 argument 1. + * @param arg2 argument 2. + * @param arg3 argument 3. + * * @return the result of the evaluation. */ - String evaluate( String column ); + String evaluate( String arg1, String arg2, String arg ); } === modified 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 2015-08-22 14:51:53 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java 2015-08-27 17:18:00 +0000 @@ -40,8 +40,8 @@ public static final String KEY = "zing"; @Override - public String evaluate( String column ) + public String evaluate( String arg1, String arg2, String arg ) { - return "case when " + column + " < 0 then 0 else " + column + " end"; + return "case when " + arg1 + " < 0 then 0 else " + arg1 + " end"; } } === modified file 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/util/ExpressionFunctions.java' --- dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/util/ExpressionFunctions.java 2015-07-14 09:47:47 +0000 +++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/util/ExpressionFunctions.java 2015-08-27 17:18:00 +0000 @@ -28,6 +28,13 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.Date; + +/** + * @author Lars Helge Overland + */ public class ExpressionFunctions { public static final String NAMESPACE = "d2"; @@ -64,4 +71,23 @@ return ( value.doubleValue() >= 0d ) ? 1d : 0d; } + + /** + * Function which will return the number of days between the two given dates. + * + * @param start the start date. + * @param end the end date. + * @return number of days between dates. + */ + public static Long daysBetween( String start, String end ) + throws ParseException + { + SimpleDateFormat format = new SimpleDateFormat(); + format.applyPattern( "yyyy-MM-dd" ); + + Date startDate = format.parse( start ); + Date endDate = format.parse( end ); + + return ( startDate.getTime() - endDate.getTime() ) / 31536000000l; + } } === modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/DateUtils.java' --- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/DateUtils.java 2015-07-08 05:02:25 +0000 +++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/DateUtils.java 2015-08-27 17:18:00 +0000 @@ -53,7 +53,6 @@ /** * @author Lars Helge Overland - * @version $Id$ */ public class DateUtils { === modified file 'dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/ExpressionUtilsTest.java' --- dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/ExpressionUtilsTest.java 2015-08-21 12:47:08 +0000 +++ dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/ExpressionUtilsTest.java 2015-08-27 17:18:00 +0000 @@ -155,6 +155,7 @@ assertTrue( ExpressionUtils.isValid( "2 + 8", null ) ); assertTrue( ExpressionUtils.isValid( "3 - v1", vars ) ); assertTrue( ExpressionUtils.isValid( "d2:zing(1)", null ) ); + assertTrue( ExpressionUtils.isValid( "d2:daysBetween('2015-02-01','2015-04-02')", null ) ); assertTrue( ExpressionUtils.isValid( "(d2:zing(1)+d2:zing(1))*50/1", null ) ); assertTrue( ExpressionUtils.isValid( "1/(1/100)", null ) ); assertTrue( ExpressionUtils.isValid( "SUM(1)", null ) ); === 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-25 14:09:25 +0000 +++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/programIndicatorForm.vm 2015-08-27 17:18:00 +0000 @@ -115,7 +115,7 @@ - $i18n.getString( "expression" ) Tip: use zing(x) oizp(x) + $i18n.getString( "expression" ) Tip: use d2:daysBetween(x,y) d2:zing(x) d2:oizp(x) @@ -259,7 +259,7 @@ - $i18n.getString( "filter" ) Tip: use oizp(x) zing(x) + $i18n.getString( "filter" ) Tip: use d2:daysBetween(x,y) d2:zing(x) d2:oizp(x)