Fixes issue 29174: Changes the way summary fields work in widgets
authorAugusto Mauch <augusto.mauch@openbravo.com>
Tue, 17 Mar 2015 18:43:56 +0100
changeset 26198 7c384195efac
parent 26196 fb806ae22a47
child 26199 be3f7a8bed0a
Fixes issue 29174: Changes the way summary fields work in widgets

Before this issue [1] was fixed, widget summary fields were computed in the front end, even if the grid had not loaded all the records. This behaviour was wrong, and to fix that issue i
t was decided to compute the summary fields in the backend, the same way it is done in standard grids.

The problem is that widget summary fields that used to show results before fixing [1] (remember that the result that were shown were not valid) do no longer work now, as it is not possi
ble to create a HQL to compute them in the server side. There were three types of summary fields that were not working:
a) Those based on columns that had an alias in the HQL clause
b) Those based on columns defined by a subquery
c) Those based on columns defined in the HQL clase with a summary function

Columns defined like a) are now supported. In order to work they must have a proper value in its Where Clause Left Part field. Columns defined like b) and c) are now no longer supported
. If there is a column of these types in a widget, none of its summary fields will be shown, and a message will be shown in the log.

[1] https://issues.openbravo.com/view.php?id=26992
modules/org.openbravo.client.querylist/src-db/database/sourcedata/AD_FIELD.xml
modules/org.openbravo.client.querylist/src/org/openbravo/client/querylist/QueryListDataSource.java
--- a/modules/org.openbravo.client.querylist/src-db/database/sourcedata/AD_FIELD.xml	Tue Mar 17 17:53:05 2015 +0100
+++ b/modules/org.openbravo.client.querylist/src-db/database/sourcedata/AD_FIELD.xml	Tue Mar 17 18:43:56 2015 +0100
@@ -977,7 +977,7 @@
 <!--998FF6ADFBD8705DE040007F01006349-->  <AD_COLUMN_ID><![CDATA[998FF6ADFBCC705DE040007F01006349]]></AD_COLUMN_ID>
 <!--998FF6ADFBD8705DE040007F01006349-->  <IGNOREINWAD><![CDATA[N]]></IGNOREINWAD>
 <!--998FF6ADFBD8705DE040007F01006349-->  <ISDISPLAYED><![CDATA[Y]]></ISDISPLAYED>
-<!--998FF6ADFBD8705DE040007F01006349-->  <DISPLAYLOGIC><![CDATA[@can_be_filtered@='Y']]></DISPLAYLOGIC>
+<!--998FF6ADFBD8705DE040007F01006349-->  <DISPLAYLOGIC><![CDATA[@can_be_filtered@='Y'|@summarize_type@!'']]></DISPLAYLOGIC>
 <!--998FF6ADFBD8705DE040007F01006349-->  <DISPLAYLENGTH><![CDATA[255]]></DISPLAYLENGTH>
 <!--998FF6ADFBD8705DE040007F01006349-->  <ISREADONLY><![CDATA[N]]></ISREADONLY>
 <!--998FF6ADFBD8705DE040007F01006349-->  <SEQNO><![CDATA[140]]></SEQNO>
--- a/modules/org.openbravo.client.querylist/src/org/openbravo/client/querylist/QueryListDataSource.java	Tue Mar 17 17:53:05 2015 +0100
+++ b/modules/org.openbravo.client.querylist/src/org/openbravo/client/querylist/QueryListDataSource.java	Tue Mar 17 18:43:56 2015 +0100
@@ -161,21 +161,38 @@
         // Ignore exception.
       }
 
-      String HQL = widgetClass.getOBCQLWidgetQueryList().get(0).getHQL();
+      OBCQL_WidgetQuery widgetQueryInstance = widgetClass.getOBCQLWidgetQueryList().get(0);
+      String HQL = widgetQueryInstance.getHQL();
       // Parse the HQL in case that optional filters are required
       HQL = parseOptionalFilters(HQL, viewMode, parameters, columns, xmlDateFormat);
-
-      if (parameters.containsKey(JsonConstants.SUMMARY_PARAMETER)) {
+      boolean fetchingSummaryFields = parameters.containsKey(JsonConstants.SUMMARY_PARAMETER);
+      if (fetchingSummaryFields) {
         // if the request comes from the summary row, update the select clause so that it obtains
         // the values for the summary fields
-        HQL = updateHQLWithSummaryFields(HQL, parameters.get(JsonConstants.SUMMARY_PARAMETER));
+        HQL = updateHQLWithSummaryFields(HQL, parameters.get(JsonConstants.SUMMARY_PARAMETER),
+            widgetQueryInstance);
       }
 
       if (parameters.containsKey(JsonConstants.SORTBY_PARAMETER)) {
         HQL = updateSortByFields(HQL, parameters.get(JsonConstants.SORTBY_PARAMETER));
       }
 
-      Query widgetQuery = OBDal.getInstance().getSession().createQuery(HQL);
+      Query widgetQuery = null;
+      try {
+        widgetQuery = OBDal.getInstance().getSession().createQuery(HQL);
+      } catch (Exception e) {
+        if (fetchingSummaryFields) {
+          log.error("Exception while fetching the summary columns of the widget "
+              + widgetClass.getWidgetTitle()
+              + ". It is not supported using as summaries columns that are defined using a subquery, or that are defined using a summary function. \n Query = "
+              + HQL);
+        } else {
+          log.error("Exception while executing the HQL query to fetch the data of the widget "
+              + widgetClass.getWidgetTitle() + ". \n Query = " + HQL);
+        }
+        final List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
+        return result;
+      }
       String[] queryAliases = widgetQuery.getReturnAliases();
 
       if (!isExport && "widget".equals(viewMode) && !showAll) {
@@ -220,7 +237,7 @@
 
       final List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
 
-      if (parameters.containsKey(JsonConstants.SUMMARY_PARAMETER)) {
+      if (fetchingSummaryFields) {
         // process the response for the summary row
         Map<String, Object> summaryData = new LinkedHashMap<String, Object>();
         try {
@@ -267,9 +284,8 @@
                   || (!isExport && queryAliases[i].equals(column.getLinkExpression()))) {
                 Object value = resultList[i];
                 if (domainType instanceof DateDomainType || domainType instanceof DateDomainType) {
-                    value = xmlDateFormat.format(value);
-                  }
-                else if (value instanceof Timestamp) {
+                  value = xmlDateFormat.format(value);
+                } else if (value instanceof Timestamp) {
                   value = xmlDateTimeFormat.format(value);
                   value = JsonUtils.convertToCorrectXSDFormat((String) value);
                 }
@@ -342,9 +358,13 @@
    *          original HQL query
    * @param summaryParametersString
    *          parameter that contains pairs of summaryField - summaryFunction values
+   * @param widgetQuery
+   *          the instance of the widget, used to obtain the whereCLauseLeftPart of the summary
+   *          parameter
    * @return an updated HQL query that will obtain the values for the summary fields
    */
-  private String updateHQLWithSummaryFields(String hQL, String summaryParametersString) {
+  private String updateHQLWithSummaryFields(String hQL, String summaryParametersString,
+      OBCQL_WidgetQuery widgetQuery) {
     // get rid of the original select clause, a new one is going to be built
     String updatedHQL = removeSelectClause(hQL);
     // the order clause is not needed when obtaining the values for the summary fields
@@ -356,6 +376,11 @@
       boolean first = true;
       while (summaryFieldNameIterator.hasNext()) {
         String summaryFieldName = (String) summaryFieldNameIterator.next();
+        String whereClauseLeftPart = getWhereClauseLeftPart(widgetQuery, summaryFieldName);
+        // if the column has whereClauseLeftPart, use it to support using columns with aliases
+        // see issue https://issues.openbravo.com/view.php?id=29174
+        String summaryColumnInnerClause = (whereClauseLeftPart.isEmpty() ? summaryFieldName
+            : whereClauseLeftPart);
         String summaryFunction = summaryFieldsObject.getString(summaryFieldName);
         if (!first) {
           selectClause.append(", ");
@@ -367,9 +392,9 @@
         if ("count".equals(summaryFunction)) {
           selectClause.append("count(*)");
         } else if ("sum".equals(summaryFunction)) {
-          selectClause.append("sum(" + summaryFieldName + ")");
+          selectClause.append("sum(" + summaryColumnInnerClause + ")");
         } else if ("avg".equals(summaryFunction)) {
-          selectClause.append("sum(" + summaryFieldName + ")/count(*)");
+          selectClause.append("sum(" + summaryColumnInnerClause + ")/count(*)");
         }
       }
       updatedHQL = selectClause.toString() + " " + updatedHQL;
@@ -379,6 +404,17 @@
     return updatedHQL;
   }
 
+  private String getWhereClauseLeftPart(OBCQL_WidgetQuery widgetQuery, String summaryFieldName) {
+    OBCriteria<OBCQL_QueryColumn> columnCriteria = OBDal.getInstance().createCriteria(
+        OBCQL_QueryColumn.class);
+    columnCriteria.add(Restrictions.eq(OBCQL_QueryColumn.PROPERTY_WIDGETQUERY, widgetQuery));
+    columnCriteria.add(Restrictions.eq(OBCQL_QueryColumn.PROPERTY_DISPLAYEXPRESSION,
+        summaryFieldName));
+    OBCQL_QueryColumn queryColumn = (OBCQL_QueryColumn) columnCriteria.uniqueResult();
+    return (queryColumn != null && queryColumn.getWhereClauseLeftPart() != null ? queryColumn
+        .getWhereClauseLeftPart() : "");
+  }
+
   /**
    * Removes the select clause of a hql query
    *