dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #39049
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 19817: Event analytics. Added support for built-in java sql functions. Impl zing and oizp functions.
Merge authors:
Lars Helge Øverland (larshelge)
------------------------------------------------------------
revno: 19817 [merge]
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Sat 2015-08-22 10:51:53 -0400
message:
Event analytics. Added support for built-in java sql functions. Impl zing and oizp functions.
added:
dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/
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/SqlFunction.java
dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/program/ProgramIndicator.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
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-program/src/main/webapp/dhis-web-maintenance-program/programIndicatorForm.vm
--
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-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<String, SqlFunction> SQL_FUNC_MAP = ImmutableMap.<String, SqlFunction>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 @@
<th>$i18n.getString( "expression" )</th>
</tr>
<tr>
- <td><div class="message message-info" style="margin-top:10px">The expression defines how the indicator is calculated</div></td>
+ <td><div class="message message-info" style="margin-top:10px">The expression defines how the indicator is calculated.</div></td>
</tr>
<tr>
<td>
@@ -114,7 +114,7 @@
</td>
</tr>
<tr>
- <th style="padding-top:15px">$i18n.getString( "expression" ) <span class="tipText">Tip: use abs(x) sin(x) cos(x) tan(x) ln(x) log(x) sqrt(x) mod(x,y) zing(x)</span></th>
+ <th style="padding-top:15px">$i18n.getString( "expression" ) <span class="tipText">Tip: use zing(x) oizp(x)</span></th>
</tr>
<tr>
<td>
@@ -234,7 +234,7 @@
</td>
</tr>
<tr>
- <th style="padding-top:15px">$i18n.getString( "filter" ) <span class="tipText">Tip: use abs(x) sin(x) cos(x) tan(x) ln(x) log(x) sqrt(x) mod(x,y) oizp(x) zing(x)</span></th>
+ <th style="padding-top:15px">$i18n.getString( "filter" ) <span class="tipText">Tip: use oizp(x) zing(x)</span></th>
</tr>
<tr>
<td>
