0044610: Bad performance into the selector on PutAway Process

--- src/org/openbravo/warehouse/advancedwarehouseoperations/mobile/classes/TaskGeneratorLine.java.old	2018-11-15 00:23:44.495621824 +0400
+++ src/org/openbravo/warehouse/advancedwarehouseoperations/mobile/classes/TaskGeneratorLine.java	2020-07-04 02:28:11.166873612 +0400
@@ -23,77 +16,62 @@ public class TaskGeneratorLine {
   Date lineDate;
   String action;
   JSONObject jsonInfo;
-
   public String getId() {
     return id;
   }
-
   public void setId(String id) {
     this.id = id;
   }
-
   public String getSource() {
     return source;
   }
-
   public void setSource(String source) {
     this.source = source;
   }
-
   public String getIdentifier() {
     return identifier;
   }
-
   public void setIdentifier(String identifier) {
     this.identifier = identifier;
   }
-
   public String getDocumentType() {
     return documentType;
   }
-
   public void setDocumentType(String documentType) {
     this.documentType = documentType;
   }
-
   public String getDocumentNo() {
     return documentNo;
   }
-
   public void setDocumentNo(String documentNo) {
     this.documentNo = documentNo;
   }
-
   public String getDescription() {
     return description;
   }
-
   public void setDescription(String description) {
     this.description = description;
   }
-
   public Date getLineDate() {
     return lineDate;
   }
-
   public void setLineDate(Date lineDate) {
     this.lineDate = lineDate;
   }
-
   public String getAction() {
     return action;
   }
-
   public void setAction(String action) {
     this.action = action;
   }
-
   public void setJsonInfo(JSONObject json) {
     this.jsonInfo = json;
   }
-
   public JSONObject getJsonInfo() {
     return jsonInfo;
   }
-
+  @Override
+  public boolean equals(Object o) {
+    return id.compareTo(((TaskGeneratorLine) o).getId()) == 0;
+  }
 }

--- src/org/openbravo/warehouse/advancedwarehouseoperations/mobile/datasources/taskgenerationcandidates/TGC_StorageDetail_PA.java.old	2018-11-15 00:23:44.495621824 +0400
+++ src/org/openbravo/warehouse/advancedwarehouseoperations/mobile/datasources/taskgenerationcandidates/TGC_StorageDetail_PA.java	2020-07-04 02:31:58.437204431 +0400
@@ -55,6 +35,215 @@ public class TGC_StorageDetail_PA extend
     ScrollableResults scroll = null;
     String additionalWhereClause = "";
     if (!StringUtils.isEmpty(filterCond)) {
+      filterCond = filterCond.trim();
+      if (useStrictSearch) {
+        lstResults = getCandidatesProduct(warehouseId, filterCond, limit, timeout);
+      } else {
+        List<TaskGeneratorLine> lstProd = getCandidatesProduct(warehouseId, filterCond, limit, timeout);
+        List<TaskGeneratorLine> lstBin = getCandidatesBin(warehouseId, filterCond, limit, timeout);
+        List<TaskGeneratorLine> lstAttr = getCandidatesAttr(warehouseId, filterCond, limit, timeout);
+        lstResults = mergeLists (lstProd,lstBin,lstAttr);
+      }
+    } else {
+      lstResults = getCandidatesAll(warehouseId, filterCond, limit, timeout);
+    }
+    return lstResults;
+  }
+public List<TaskGeneratorLine> mergeLists(List<TaskGeneratorLine> lstProd, List<TaskGeneratorLine> lstBin,List<TaskGeneratorLine> lstAttr) {
+  List<TaskGeneratorLine> lstResults = new ArrayList<TaskGeneratorLine>();
+  if (lstProd != null)
+    lstResults.addAll(lstProd);
+  if (lstBin != null)
+    lstResults.addAll(lstBin);
+  if (lstAttr != null)
+    lstResults.addAll(lstAttr);
+  return removeDuplicates(lstResults);
+}
+private List<TaskGeneratorLine> removeDuplicates(List<TaskGeneratorLine> list) {
+   // Create a new ArrayList
+   List<TaskGeneratorLine> newList = new ArrayList<TaskGeneratorLine>();
+   // Traverse through the first list
+   for (TaskGeneratorLine element : list) {
+     // If this element is not present in newList
+     // then add it
+     if (!newList.contains(element)) {
+       newList.add(element);
+     }
+   }
+   // return the new list
+   return newList;
+ }
+public List<TaskGeneratorLine> getCandidatesAll(String warehouseId, String filterCond, int limit,
+      int timeout) {
+    boolean useContains = this.useContains();
+    boolean useStrictSearch = this.useStrictSearch();
+    List<TaskGeneratorLine> lstResults = new ArrayList<TaskGeneratorLine>();
+    HQLPropertyList selectProperties = this.getSelectClause();
+    ScrollableResults scroll = null;
+    String hql = "SELECT ";
+    hql += selectProperties.getHqlSelect();
+    hql += " FROM MaterialMgmtStorageDetail sd ";
+    hql += "left join sd.product as pr ";
+    hql += "left join sd.storageBin as sb ";
+    hql += "left join sb.warehouse as wh ";
+    hql += "left join sd.attributeSetValue as att ";
+    hql += "left join sd.uOM as uom ";
+    hql += "left join sd.referencedInventory as ref ";
+    hql += "WHERE sd.quantityOnHand > 0 ";
+    hql += "AND sb.warehouse.id = :warehouseId ";
+    hql += "AND ((sd.quantityOnHand - (SELECT coalesce(sum(t.expectedQuantity), 0) FROM OBAWO_Task t WHERE t.status = 'AV' AND t.storageDetail.id = sd.id) > 0)) ";
+    hql += "ORDER BY sb.searchKey asc";
+    final Query queryPACandidates = OBDal.getInstance().getSession().createQuery(hql);
+    queryPACandidates.setParameter("warehouseId", warehouseId);
+    if (filterCond.length() > 0) {
+      if (useContains) {
+        queryPACandidates.setParameter("filterParam", "%" + filterCond.toUpperCase() + "%");
+      } else {
+        queryPACandidates.setParameter("filterParam", filterCond);
+      }
+    }
+    queryPACandidates.setMaxResults(limit);
+    scroll = queryPACandidates.scroll(ScrollMode.FORWARD_ONLY);
+    while (scroll.next()) {
+      TaskGeneratorLine taskGenLine = new TaskGeneratorLine();
+      taskGenLine.setId((String) scroll.getString(selectProperties.getHqlPropertyIndex("id")));
+      taskGenLine.setDocumentNo(null);
+      taskGenLine.setLineDate(null);
+      taskGenLine.setIdentifier(null);
+      taskGenLine.setAction(this.getAction());
+      taskGenLine.setSource(this.getSource());
+      taskGenLine.setDescription(null);
+      taskGenLine.setJsonInfo(this.generateJsonResponse(scroll, selectProperties));
+      lstResults.add(taskGenLine);
+    }
+    scroll.close();
+    return lstResults;
+  }
+  public List<TaskGeneratorLine> getCandidatesAttr(String warehouseId, String filterCond, int limit,
+      int timeout) {
+    boolean useContains = this.useContains();
+    boolean useStrictSearch = this.useStrictSearch();
+    List<TaskGeneratorLine> lstResults = new ArrayList<TaskGeneratorLine>();
+    HQLPropertyList selectProperties = this.getSelectClause();
+    ScrollableResults scroll = null;
+    String additionalWhereClause = "";
+    if (!StringUtils.isEmpty(filterCond)) {
+        if (useContains) {
+          additionalWhereClause += "( upper(att.description) like :filterParam )";
+        } else {
+          additionalWhereClause += "( att.description = :filterParam )";
+        }
+    } else {
+      additionalWhereClause += "1 = 1";
+    }
+    additionalWhereClause += " ";
+    String hql = "SELECT ";
+    hql += selectProperties.getHqlSelect();
+    hql += " FROM MaterialMgmtStorageDetail sd ";
+    hql += "left join sd.product as pr ";
+    hql += "left join sd.storageBin as sb ";
+    hql += "left join sb.warehouse as wh ";
+    hql += "left join sd.attributeSetValue as att ";
+    hql += "left join sd.uOM as uom ";
+    hql += "left join sd.referencedInventory as ref ";
+    hql += "WHERE sd.quantityOnHand > 0 ";
+    hql += " AND " + additionalWhereClause;
+    hql += "AND sb.warehouse.id = :warehouseId ";
+    hql += "AND ((sd.quantityOnHand - (SELECT coalesce(sum(t.expectedQuantity), 0) FROM OBAWO_Task t WHERE t.status = 'AV' AND t.storageDetail.id = sd.id) > 0)) ";
+    hql += "ORDER BY sb.searchKey asc";
+    final Query queryPACandidates = OBDal.getInstance().getSession().createQuery(hql);
+    queryPACandidates.setParameter("warehouseId", warehouseId);
+    if (filterCond.length() > 0) {
+      if (useContains) {
+        queryPACandidates.setParameter("filterParam", "%" + filterCond.toUpperCase() + "%");
+      } else {
+        queryPACandidates.setParameter("filterParam", filterCond);
+      }
+    }
+    queryPACandidates.setMaxResults(limit);
+    scroll = queryPACandidates.scroll(ScrollMode.FORWARD_ONLY);
+    while (scroll.next()) {
+      TaskGeneratorLine taskGenLine = new TaskGeneratorLine();
+      taskGenLine.setId((String) scroll.getString(selectProperties.getHqlPropertyIndex("id")));
+      taskGenLine.setDocumentNo(null);
+      taskGenLine.setLineDate(null);
+      taskGenLine.setIdentifier(null);
+      taskGenLine.setAction(this.getAction());
+      taskGenLine.setSource(this.getSource());
+      taskGenLine.setDescription(null);
+      taskGenLine.setJsonInfo(this.generateJsonResponse(scroll, selectProperties));
+      lstResults.add(taskGenLine);
+    }
+    scroll.close();
+    return lstResults;
+  }
+  public List<TaskGeneratorLine> getCandidatesBin(String warehouseId, String filterCond, int limit,
+      int timeout) {
+    boolean useContains = this.useContains();
+    boolean useStrictSearch = this.useStrictSearch();
+    List<TaskGeneratorLine> lstResults = new ArrayList<TaskGeneratorLine>();
+    HQLPropertyList selectProperties = this.getSelectClause();
+    ScrollableResults scroll = null;
+    String additionalWhereClause = "";
+    if (!StringUtils.isEmpty(filterCond)) {
+        if (useContains) {
+          additionalWhereClause += "( upper(sb.searchKey) like :filterParam )";
+        } else {
+          additionalWhereClause += "( sb.searchKey = :filterParam )";
+        }
+    } else {
+      additionalWhereClause += "1 = 1";
+    }
+    additionalWhereClause += " ";
+    String hql = "SELECT ";
+    hql += selectProperties.getHqlSelect();
+    hql += " FROM MaterialMgmtStorageDetail sd ";
+    hql += "left join sd.product as pr ";
+    hql += "left join sd.storageBin as sb ";
+    hql += "left join sb.warehouse as wh ";
+    hql += "left join sd.attributeSetValue as att ";
+    hql += "left join sd.uOM as uom ";
+    hql += "left join sd.referencedInventory as ref ";
+    hql += "WHERE sd.quantityOnHand > 0 ";
+    hql += " AND " + additionalWhereClause;
+    hql += "AND sb.warehouse.id = :warehouseId ";
+    hql += "AND ((sd.quantityOnHand - (SELECT coalesce(sum(t.expectedQuantity), 0) FROM OBAWO_Task t WHERE t.status = 'AV' AND t.storageDetail.id = sd.id) > 0)) ";
+    hql += "ORDER BY sb.searchKey asc";
+    final Query queryPACandidates = OBDal.getInstance().getSession().createQuery(hql);
+    queryPACandidates.setParameter("warehouseId", warehouseId);
+    if (filterCond.length() > 0) {
+      if (useContains) {
+        queryPACandidates.setParameter("filterParam", "%" + filterCond.toUpperCase() + "%");
+      } else {
+        queryPACandidates.setParameter("filterParam", filterCond);
+      }
+    }
+    queryPACandidates.setMaxResults(limit);
+    scroll = queryPACandidates.scroll(ScrollMode.FORWARD_ONLY);
+    while (scroll.next()) {
+      TaskGeneratorLine taskGenLine = new TaskGeneratorLine();
+      taskGenLine.setId((String) scroll.getString(selectProperties.getHqlPropertyIndex("id")));
+      taskGenLine.setDocumentNo(null);
+      taskGenLine.setLineDate(null);
+      taskGenLine.setIdentifier(null);
+      taskGenLine.setAction(this.getAction());
+      taskGenLine.setSource(this.getSource());
+      taskGenLine.setDescription(null);
+      taskGenLine.setJsonInfo(this.generateJsonResponse(scroll, selectProperties));
+      lstResults.add(taskGenLine);
+    }
+    scroll.close();
+    return lstResults;
+  }
+  public List<TaskGeneratorLine> getCandidatesProduct(String warehouseId, String filterCond, int limit,
+      int timeout) {
+    boolean useContains = this.useContains();
+    boolean useStrictSearch = this.useStrictSearch();
+    List<TaskGeneratorLine> lstResults = new ArrayList<TaskGeneratorLine>();
+    HQLPropertyList selectProperties = this.getSelectClause();
+    ScrollableResults scroll = null;
+    String additionalWhereClause = "";
+    if (!StringUtils.isEmpty(filterCond)) {
       if (useStrictSearch) {
         if (useContains) {
           additionalWhereClause += "(upper(pr.uPCEAN) like :filterParam)";
@@ -68,29 +257,19 @@ public class TGC_StorageDetail_PA extend
           additionalWhereClause += "upper(pr.searchKey) like :filterParam OR ";
           additionalWhereClause += "upper(pr.uPCEAN) like :filterParam OR ";
           // Locator: SearchKey
-          additionalWhereClause += "upper(pr.name) like :filterParam OR ";
-          additionalWhereClause += "upper(sb.searchKey) like :filterParam OR ";
-          // Att: Has certain att value in associated att set instance
-          additionalWhereClause += "upper(att.description) like :filterParam";
-          additionalWhereClause += ")";
+          additionalWhereClause += "upper(pr.name) like :filterParam )";
         } else {
           additionalWhereClause += "(";
           // Product: SearchKey/UPC/Name
           additionalWhereClause += "pr.searchKey = :filterParam OR ";
           additionalWhereClause += "pr.uPCEAN = :filterParam OR ";
-          additionalWhereClause += "pr.name = :filterParam OR ";
-          // Locator: SearchKey
-          additionalWhereClause += "sb.searchKey = :filterParam OR ";
-          // Att: Has certain att value in associated att set instance
-          additionalWhereClause += "att.description = :filterParam";
-          additionalWhereClause += ")";
+          additionalWhereClause += "pr.name = :filterParam )";
         }
       }
     } else {
       additionalWhereClause += "1 = 1";
     }
     additionalWhereClause += " ";
-
     String hql = "SELECT ";
     hql += selectProperties.getHqlSelect();
     hql += " FROM MaterialMgmtStorageDetail sd ";