Fixes issue 26681: Some records are not ordered properly in widgets
authorShankar Balachandran <shankar.balachandran@openbravo.com>
Fri, 01 Aug 2014 11:48:12 +0530
changeset 24151 4f31d2d162f9
parent 24150 7a2993961171
child 24152 bae6b944d95a
Fixes issue 26681: Some records are not ordered properly in widgets

orderby clause is done at the query level and not after fetching data.
Sorting works in paginated records.
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	Thu Jul 31 22:31:26 2014 +0530
+++ b/modules/org.openbravo.client.querylist/src/org/openbravo/client/querylist/QueryListDataSource.java	Fri Aug 01 11:48:12 2014 +0530
@@ -170,6 +170,11 @@
         // the values for the summary fields
         HQL = updateHQLWithSummaryFields(HQL, parameters.get(JsonConstants.SUMMARY_PARAMETER));
       }
+
+      if (parameters.containsKey(JsonConstants.SORTBY_PARAMETER)) {
+        HQL = updateSortByFields(HQL, parameters.get(JsonConstants.SORTBY_PARAMETER));
+      }
+
       Query widgetQuery = OBDal.getInstance().getSession().createQuery(HQL);
       String[] queryAliases = widgetQuery.getReturnAliases();
 
@@ -286,10 +291,6 @@
           result.add(data);
         }
       }
-      String sortBy = parameters.get("_sortBy");
-      if (StringUtils.isNotEmpty(sortBy)) {
-        sort(sortBy, result);
-      }
       return result;
     } finally {
       OBContext.restorePreviousMode();
@@ -297,6 +298,47 @@
   }
 
   /**
+   * Updates the order by clause of the HQL query so that it obtains the values for the summary
+   * fields. If the HQL query already contains order by fields, the new fields are appended for the
+   * existing fields.
+   * 
+   * @param hQL
+   *          original HQL query
+   * @param sortByParametersString
+   *          parameter that contains sortBy field values
+   * @return an updated HQL query that will set the order by fields
+   */
+  private String updateSortByFields(String hql, String sortBy) {
+    String[] fieldList = null;
+    String sortByClause = "", hqlString = hql;
+    if (sortBy.contains(",")) {
+      fieldList = sortBy.split(",");
+    }
+    if (hqlString.contains("order by")) {
+      if (fieldList == null) {
+        sortByClause = sortBy.startsWith("-") ? sortBy.substring(1, sortBy.length()) + " desc "
+            : sortBy;
+      } else {
+        // sort by multiple columns
+        for (String field : fieldList) {
+          sortByClause = field.startsWith("-") ? sortByClause.concat(field.substring(1,
+              field.length()))
+              + " desc " : sortByClause.concat(field);
+        }
+      }
+      hqlString = hqlString.replace("order by", "order by " + sortByClause + ",");
+    } else {
+      // if limit is present in the hql, append order by before it
+      if (hqlString.contains("limit")) {
+        hqlString.replace("limit", sortByClause + " limit");
+      } else {
+        hqlString = hqlString.concat(" order by " + sortByClause);
+      }
+    }
+    return hqlString;
+  }
+
+  /**
    * Updates the select clause of the HQL query so that it obtains the values for the summary fields
    * 
    * @param hQL