0026981: Impossible to filter amount fields in Query/List widgets

# HG changeset patch
# User Shankar Balachandran <shankar.balachandran@openbravo.com>
# Date 1404370023 -19800
#      Thu Jul 03 12:17:03 2014 +0530
# Node ID d0cb4918040efa52fd9c310c1bd93a46f6cee1a4
# Parent  3948973fcaaf5c000bd347921c5d353486ac8eb4
Fixes issue 26981: Impossible to filter amount fields in Query/List widgets

The operator was not applied for numeric fields, always equals was applied.
Moved getHqlOperator of AdvancedQueryBuilder to Utility class and used it to fetch the operator for query list datasource.
Applied the operator in the where clause of query list datasource.

diff -r 3948973fcaaf -r d0cb4918040e modules/org.openbravo.client.querylist/src/org/openbravo/client/querylist/QueryListDataSource.java
--- a/modules/org.openbravo.client.querylist/src/org/openbravo/client/querylist/QueryListDataSource.java	Wed Jul 02 11:33:10 2014 +0200
+++ b/modules/org.openbravo.client.querylist/src/org/openbravo/client/querylist/QueryListDataSource.java	Thu Jul 03 12:17:03 2014 +0530
@@ -64,6 +64,7 @@
 import org.openbravo.dal.service.OBCriteria;
 import org.openbravo.dal.service.OBDal;
 import org.openbravo.erpCommon.utility.OBMessageUtils;
+import org.openbravo.erpCommon.utility.Utility;
 import org.openbravo.model.ad.access.WindowAccess;
 import org.openbravo.model.ad.datamodel.Column;
 import org.openbravo.model.ad.domain.Reference;
@@ -83,6 +84,7 @@
 public class QueryListDataSource extends ReadOnlyDataSourceService implements PortalAccessible {
   private static final String OPTIONAL_FILTERS = "@optional_filters@";
   private static final Logger log = Logger.getLogger(QueryListDataSource.class);
+  private static final String OPERATOR = "$OPERATOR";
 
   /**
    * Returns the count of objects based on the passed parameters.
@@ -151,6 +153,8 @@
         for (int i = 0; i < criterias.length(); i++) {
           final JSONObject criteria = criterias.getJSONObject(i);
           parameters.put(criteria.getString("fieldName"), criteria.getString("value"));
+          parameters
+              .put(criteria.getString("fieldName") + OPERATOR, criteria.getString("operator"));
         }
       } catch (JSONException e) {
         // Ignore exception.
@@ -159,7 +163,6 @@
       String HQL = widgetClass.getOBCQLWidgetQueryList().get(0).getHQL();
       // Parse the HQL in case that optional filters are required
       HQL = parseOptionalFilters(HQL, viewMode, parameters, columns, xmlDateFormat);
-
       Query widgetQuery = OBDal.getInstance().getSession().createQuery(HQL);
       String[] queryAliases = widgetQuery.getReturnAliases();
 
@@ -371,6 +374,7 @@
       for (OBCQL_QueryColumn column : columns) {
         if (column.isCanBeFiltered()) {
           String value = parameters.get(column.getDisplayExpression());
+          String operator = parameters.get(column.getDisplayExpression() + OPERATOR);
           if (column.getReference().getName().equals("YesNo") && value != null) {
             if (value.equals("true")) {
               value = "Y";
@@ -380,7 +384,7 @@
           }
           String whereClause = " 1=1 ";
           if (value != null) {
-            whereClause = getWhereClause(value, column, xmlDateFormat);
+            whereClause = getWhereClause(value, column, xmlDateFormat, operator);
           }
 
           if (HQL.contains("@" + column.getDisplayExpression() + "@")) {
@@ -396,13 +400,14 @@
   }
 
   private String getWhereClause(String value, OBCQL_QueryColumn column,
-      SimpleDateFormat xmlDateFormat) {
+      SimpleDateFormat xmlDateFormat, String operator) {
     String whereClause = "";
     DomainType domainType = ModelProvider.getInstance().getReference(column.getReference().getId())
         .getDomainType();
     if (domainType.getClass().getSuperclass().equals(BigDecimalDomainType.class)
         || domainType.getClass().equals(LongDomainType.class)) {
-      whereClause = column.getWhereClauseLeftPart() + " = " + value;
+      whereClause = column.getWhereClauseLeftPart() + " " + Utility.getHqlOperator(operator) + " "
+          + value;
     } else if (domainType.getClass().equals(DateDomainType.class)) {
       try {
         final Calendar cal = Calendar.getInstance();
diff -r 3948973fcaaf -r d0cb4918040e modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java
--- a/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java	Wed Jul 02 11:33:10 2014 +0200
+++ b/modules/org.openbravo.service.json/src/org/openbravo/service/json/AdvancedQueryBuilder.java	Thu Jul 03 12:17:03 2014 +0530
@@ -482,7 +482,7 @@
     }
 
     String leftClause = buildFieldClause(properties, property, fieldName, operator);
-    String hqlOperator = getHqlOperator(operator);
+    String hqlOperator = Utility.getHqlOperator(operator);
     // special case
     if (value != null && value.toString().contains(JsonConstants.IN_PARAMETER_SEPARATOR)) {
       hqlOperator = "in";
@@ -990,88 +990,6 @@
         || operator.equals(OPERATOR_NOT) || operator.equals(OPERATOR_NOTINSET);
   }
 
-  private String getHqlOperator(String operator) {
-    if (operator.equals(OPERATOR_EQUALS)) {
-      return "=";
-    } else if (operator.equals(OPERATOR_INSET)) {
-      return "in";
-    } else if (operator.equals(OPERATOR_NOTINSET)) {
-      return "in";
-    } else if (operator.equals(OPERATOR_NOTEQUAL)) {
-      return "!=";
-    } else if (operator.equals(OPERATOR_IEQUALS)) {
-      return "=";
-    } else if (operator.equals(OPERATOR_INOTEQUAL)) {
-      return "!=";
-    } else if (operator.equals(OPERATOR_GREATERTHAN)) {
-      return ">";
-    } else if (operator.equals(OPERATOR_LESSTHAN)) {
-      return "<";
-    } else if (operator.equals(OPERATOR_GREATEROREQUAL)) {
-      return ">=";
-    } else if (operator.equals(OPERATOR_LESSOREQUAL)) {
-      return "<=";
-    } else if (operator.equals(OPERATOR_IGREATERTHAN)) {
-      return ">";
-    } else if (operator.equals(OPERATOR_ILESSTHAN)) {
-      return "<";
-    } else if (operator.equals(OPERATOR_IGREATEROREQUAL)) {
-      return ">=";
-    } else if (operator.equals(OPERATOR_ILESSOREQUAL)) {
-      return "<=";
-    } else if (operator.equals(OPERATOR_CONTAINS)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_STARTSWITH)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_ENDSWITH)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_ICONTAINS)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_ISTARTSWITH)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_IENDSWITH)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_NOTCONTAINS)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_NOTSTARTSWITH)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_NOTENDSWITH)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_INOTCONTAINS)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_INOTSTARTSWITH)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_INOTENDSWITH)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_EQUALSFIELD)) {
-      return "=";
-    } else if (operator.equals(OPERATOR_NOTEQUALFIELD)) {
-      return "!=";
-    } else if (operator.equals(OPERATOR_GREATERTHANFIElD)) {
-      return ">";
-    } else if (operator.equals(OPERATOR_LESSTHANFIELD)) {
-      return "<";
-    } else if (operator.equals(OPERATOR_GREATEROREQUALFIELD)) {
-      return ">=";
-    } else if (operator.equals(OPERATOR_LESSOREQUALFIElD)) {
-      return "<=";
-    } else if (operator.equals(OPERATOR_CONTAINSFIELD)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_STARTSWITHFIELD)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_ENDSWITHFIELD)) {
-      return "like";
-    } else if (operator.equals(OPERATOR_ISNULL)) {
-      return "is";
-    } else if (operator.equals(OPERATOR_NOTNULL)) {
-      return "is not";
-    } else if (operator.equals(OPERATOR_EXISTS)) {
-      return "exists";
-    }
-    // todo throw exception
-    return null;
-  }
-
   private String substituteContextParameters(String currentWhereClause) {
     // This method will check for any remaining @param@s
     // If there are still some in the whereclause, they will be resolved by calling the getContext()
diff -r 3948973fcaaf -r d0cb4918040e src/org/openbravo/erpCommon/utility/Utility.java
--- a/src/org/openbravo/erpCommon/utility/Utility.java	Wed Jul 02 11:33:10 2014 +0200
+++ b/src/org/openbravo/erpCommon/utility/Utility.java	Thu Jul 03 12:17:03 2014 +0530
@@ -112,6 +112,54 @@
   private static List<String> autosaveExcludedPackages = null;
   private static List<String> autosaveExcludedClasses = null;
 
+  private static final String OPERATOR_AND = "and";
+  static final String OPERATOR_OR = "or";
+
+  private static final String OPERATOR_EQUALS = "equals";
+  private static final String OPERATOR_NOTEQUAL = "notEqual";
+  private static final String OPERATOR_IEQUALS = "iEquals";
+  private static final String OPERATOR_INOTEQUAL = "iNotEqual";
+  private static final String OPERATOR_GREATERTHAN = "greaterThan";
+  private static final String OPERATOR_LESSTHAN = "lessThan";
+  private static final String OPERATOR_GREATEROREQUAL = "greaterOrEqual";
+  private static final String OPERATOR_LESSOREQUAL = "lessOrEqual";
+  private static final String OPERATOR_IGREATERTHAN = "iGreaterThan";
+  private static final String OPERATOR_ILESSTHAN = "iLessThan";
+  private static final String OPERATOR_IGREATEROREQUAL = "iGreaterOrEqual";
+  private static final String OPERATOR_ILESSOREQUAL = "iLessOrEqual";
+  private static final String OPERATOR_CONTAINS = "contains";
+  private static final String OPERATOR_STARTSWITH = "startsWith";
+  private static final String OPERATOR_ENDSWITH = "endsWith";
+  private static final String OPERATOR_ICONTAINS = "iContains";
+  private static final String OPERATOR_ISTARTSWITH = "iStartsWith";
+  private static final String OPERATOR_IENDSWITH = "iEndsWith";
+  private static final String OPERATOR_NOTCONTAINS = "notContains";
+  private static final String OPERATOR_NOTSTARTSWITH = "notStartsWith";
+  private static final String OPERATOR_NOTENDSWITH = "notEndsWith";
+  private static final String OPERATOR_INOTCONTAINS = "iNotContains";
+  private static final String OPERATOR_INOTSTARTSWITH = "iNotStartsWith";
+  private static final String OPERATOR_INOTENDSWITH = "iNotEndsWith";
+
+  private static final String OPERATOR_ISNULL = "isNull";
+  private static final String OPERATOR_NOTNULL = "notNull";
+  private static final String OPERATOR_INSET = "inSet";
+  private static final String OPERATOR_NOTINSET = "notInSet";
+  private static final String OPERATOR_EQUALSFIELD = "equalsField";
+  private static final String OPERATOR_NOTEQUALFIELD = "notEqualField";
+  private static final String OPERATOR_GREATERTHANFIElD = "greaterThanField";
+  private static final String OPERATOR_LESSTHANFIELD = "lessThanField";
+  private static final String OPERATOR_GREATEROREQUALFIELD = "greaterOrEqualField";
+  private static final String OPERATOR_LESSOREQUALFIElD = "lessOrEqualField";
+  private static final String OPERATOR_CONTAINSFIELD = "containsField";
+  private static final String OPERATOR_STARTSWITHFIELD = "startsWithField";
+  private static final String OPERATOR_ENDSWITHFIELD = "endsWithField";
+  private static final String OPERATOR_NOT = "not";
+  private static final String OPERATOR_BETWEEN = "between";
+  private static final String OPERATOR_BETWEENINCLUSIVE = "betweenInclusive";
+  private static final String OPERATOR_IBETWEEN = "iBetween";
+  private static final String OPERATOR_IBETWEENINCLUSIVE = "iBetweenInclusive";
+  public static final String OPERATOR_EXISTS = "exists";
+
   // List of excludes packages and classes from Autosave
   // TODO: Define the autosave behavior at object level
   static {
@@ -2625,4 +2673,85 @@
     }
   }
 
+  public static String getHqlOperator(String operator) {
+    if (operator.equals(OPERATOR_EQUALS)) {
+      return "=";
+    } else if (operator.equals(OPERATOR_INSET)) {
+      return "in";
+    } else if (operator.equals(OPERATOR_NOTINSET)) {
+      return "in";
+    } else if (operator.equals(OPERATOR_NOTEQUAL)) {
+      return "!=";
+    } else if (operator.equals(OPERATOR_IEQUALS)) {
+      return "=";
+    } else if (operator.equals(OPERATOR_INOTEQUAL)) {
+      return "!=";
+    } else if (operator.equals(OPERATOR_GREATERTHAN)) {
+      return ">";
+    } else if (operator.equals(OPERATOR_LESSTHAN)) {
+      return "<";
+    } else if (operator.equals(OPERATOR_GREATEROREQUAL)) {
+      return ">=";
+    } else if (operator.equals(OPERATOR_LESSOREQUAL)) {
+      return "<=";
+    } else if (operator.equals(OPERATOR_IGREATERTHAN)) {
+      return ">";
+    } else if (operator.equals(OPERATOR_ILESSTHAN)) {
+      return "<";
+    } else if (operator.equals(OPERATOR_IGREATEROREQUAL)) {
+      return ">=";
+    } else if (operator.equals(OPERATOR_ILESSOREQUAL)) {
+      return "<=";
+    } else if (operator.equals(OPERATOR_CONTAINS)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_STARTSWITH)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_ENDSWITH)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_ICONTAINS)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_ISTARTSWITH)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_IENDSWITH)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_NOTCONTAINS)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_NOTSTARTSWITH)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_NOTENDSWITH)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_INOTCONTAINS)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_INOTSTARTSWITH)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_INOTENDSWITH)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_EQUALSFIELD)) {
+      return "=";
+    } else if (operator.equals(OPERATOR_NOTEQUALFIELD)) {
+      return "!=";
+    } else if (operator.equals(OPERATOR_GREATERTHANFIElD)) {
+      return ">";
+    } else if (operator.equals(OPERATOR_LESSTHANFIELD)) {
+      return "<";
+    } else if (operator.equals(OPERATOR_GREATEROREQUALFIELD)) {
+      return ">=";
+    } else if (operator.equals(OPERATOR_LESSOREQUALFIElD)) {
+      return "<=";
+    } else if (operator.equals(OPERATOR_CONTAINSFIELD)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_STARTSWITHFIELD)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_ENDSWITHFIELD)) {
+      return "like";
+    } else if (operator.equals(OPERATOR_ISNULL)) {
+      return "is";
+    } else if (operator.equals(OPERATOR_NOTNULL)) {
+      return "is not";
+    } else if (operator.equals(OPERATOR_EXISTS)) {
+      return "exists";
+    }
+    // todo throw exception
+    return null;
+  }
 }