0033581: slow defaults retrieval in add payment

# HG changeset patch
# User Víctor Martínez Romanos <victor.martinez@openbravo.com>
# Date 1488973821 -3600
#      Wed Mar 08 12:50:21 2017 +0100
# Node ID 0b13c0775882cba122a1a0f274e081c0b57cd320
# Parent  2d002737ee99169df4fb03ea8f76aee2a2d7869b
Fixed bug 33581: slow defaults retrieval in add payment

The FIN_Payment table didn't have an index on the c_bpartner_id column, thus forcing a sequencial scan in any query run over this table.
Added an index on the c_bpartner_id column to fix that problem.

This index should fix most of the cases, however when for example a concrete business partner has an important percentage of the records in that table, the previous index would be useless, and the db might decide to run a seq. scan.
To control this scenario we have extended the previous index with a function to distinguish credit payments (generated credit != used credit). This way we could filter by the credit records for the business partner directly using the index.
Note that the function obequals is declared in the prescript because it must be defined as IMMUTABLE/DETERMINISTIC and this syntaxis is not supported yet by the DBSM.

The AdvPaymentMngtDao.getCustomerPaymentsWithCredit() has been rewritten to take advantage of the new index.

diff --git a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/dao/AdvPaymentMngtDao.java b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/dao/AdvPaymentMngtDao.java
--- a/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/dao/AdvPaymentMngtDao.java
+++ b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/dao/AdvPaymentMngtDao.java
@@ -11,7 +11,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2010-2016 Openbravo SLU
+ * All portions are Copyright (C) 2010-2017 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  Enterprise Intelligence Systems (http://www.eintel.com.au).
  *************************************************************************
@@ -23,11 +23,15 @@
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Date;
+import java.util.HashMap;
 import java.util.Iterator;
 import java.util.List;
+import java.util.Map;
 import java.util.Set;
 
 import org.apache.commons.lang.StringUtils;
+import org.hibernate.Query;
+import org.hibernate.Session;
 import org.hibernate.criterion.Criterion;
 import org.hibernate.criterion.DetachedCriteria;
 import org.hibernate.criterion.Projections;
@@ -1970,24 +1974,41 @@
 
     try {
       OBContext.setAdminMode(true);
-      OBCriteria<FIN_Payment> obcPayment = OBDal.getInstance().createCriteria(FIN_Payment.class);
-      obcPayment.add(Restrictions.eq(FIN_Payment.PROPERTY_BUSINESSPARTNER, bp));
-      obcPayment.add(Restrictions.eq(FIN_Payment.PROPERTY_RECEIPT, isReceipt));
-      obcPayment.add(Restrictions.ne(FIN_Payment.PROPERTY_GENERATEDCREDIT, BigDecimal.ZERO));
-      obcPayment.add(Restrictions.neProperty(FIN_Payment.PROPERTY_GENERATEDCREDIT,
-          FIN_Payment.PROPERTY_USEDCREDIT));
+
       final Organization legalEntity = FIN_Utility.getLegalEntityOrg(org);
       Set<String> orgIds = OBContext.getOBContext().getOrganizationStructureProvider()
           .getChildTree(legalEntity.getId(), true);
-      obcPayment.add(Restrictions.in("organization.id", orgIds));
+
+      final Map<String, Object> params = new HashMap<String, Object>();
+
+      final StringBuffer hql = new StringBuffer("select p ");
+      hql.append(" from " + FIN_Payment.ENTITY_NAME + " as p ");
+      hql.append(" where p." + FIN_Payment.PROPERTY_BUSINESSPARTNER + ".id = :bpartnerId ");
+      hql.append("  and p." + FIN_Payment.PROPERTY_RECEIPT + " = :isReceipt ");
+      hql.append("  and p." + FIN_Payment.PROPERTY_ORGANIZATION + ".id in (:orgIds) ");
+      hql.append("  and obequals(p." + FIN_Payment.PROPERTY_GENERATEDCREDIT + ", p."
+          + FIN_Payment.PROPERTY_USEDCREDIT + ") = 'N' ");
+      hql.append("  and p." + FIN_Payment.PROPERTY_GENERATEDCREDIT + " <> 0 ");
       if (currency != null) {
-        obcPayment.add(Restrictions.eq(FIN_Payment.PROPERTY_CURRENCY, currency));
+        hql.append(" and p." + FIN_Payment.PROPERTY_CURRENCY + ".id = :currencyId");
+        params.put("currencyId", currency.getId());
       }
-      obcPayment.addOrderBy(FIN_Payment.PROPERTY_PAYMENTDATE, true);
-      obcPayment.addOrderBy(FIN_Payment.PROPERTY_DOCUMENTNO, true);
 
-      List<FIN_Payment> paymentList = new ArrayList<FIN_Payment>();
-      for (FIN_Payment fp : obcPayment.list()) {
+      hql.append(" order by p." + FIN_Payment.PROPERTY_PAYMENTDATE + " asc, ");
+      hql.append(" p." + FIN_Payment.PROPERTY_DOCUMENTNO + " asc ");
+
+      params.put("bpartnerId", bp.getId());
+      params.put("isReceipt", isReceipt);
+      params.put("orgIds", orgIds);
+
+      final Session session = OBDal.getInstance().getSession();
+      final Query query = session.createQuery(hql.toString());
+      query.setProperties(params);
+
+      @SuppressWarnings("unchecked")
+      final List<FIN_Payment> queryList = query.list();
+      final List<FIN_Payment> paymentList = new ArrayList<FIN_Payment>();
+      for (FIN_Payment fp : queryList) {
         if ((FIN_Utility.seqnumberpaymentstatus(fp.getStatus())) >= (FIN_Utility
             .seqnumberpaymentstatus(FIN_Utility.invoicePaymentStatus(fp)))) {
           paymentList.add(fp);
diff --git a/src-db/database/model/excludeFilter.xml b/src-db/database/model/excludeFilter.xml
--- a/src-db/database/model/excludeFilter.xml
+++ b/src-db/database/model/excludeFilter.xml
@@ -86,4 +86,5 @@
     <excludedFunction name="SIMILARITY"/>
     <excludedFunction name="SIMILARITY_DIST"/>
     <excludedFunction name="SIMILARITY_OP"/>
+    <excludedFunction name="OBEQUALS"/>
   </vector>
diff --git a/src-db/database/model/prescript-Oracle.sql b/src-db/database/model/prescript-Oracle.sql
--- a/src-db/database/model/prescript-Oracle.sql
+++ b/src-db/database/model/prescript-Oracle.sql
@@ -234,3 +234,38 @@
   RETURN p_date + p_hours/24 + p_minutes/1440 + p_seconds/86400;
 end ADD_HMS;
 /-- END
+
+create or replace
+FUNCTION OBEQUALS(p_number_a IN NUMBER, p_number_b IN NUMBER) RETURN CHAR DETERMINISTIC
+
+AS
+/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+* Version 1.1  with a permitted attribution clause; you may not  use this
+* file except in compliance with the License. You  may  obtain  a copy of
+* the License at http://www.openbravo.com/legal/license.html
+* Software distributed under the License  is  distributed  on  an "AS IS"
+* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+* License for the specific  language  governing  rights  and  limitations
+* under the License.
+* The Original Code is Openbravo ERP.
+* The Initial Developer of the Original Code is Openbravo SLU
+* All portions are Copyright (C) 2017 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+/**
+* Returns 'Y' when both numbers are equals, else returns 'N'
+* This function is used as index in FIN_Payment table
+**/
+ v_dif NUMBER;
+begin
+    v_dif := coalesce(p_number_a, 0) - coalesce(p_number_b, 0);
+    IF (v_dif = 0) THEN
+     return 'Y';
+    ELSE
+     return 'N';
+    END IF;
+end OBEQUALS;
+/-- END
diff --git a/src-db/database/model/prescript-PostgreSql.sql b/src-db/database/model/prescript-PostgreSql.sql
--- a/src-db/database/model/prescript-PostgreSql.sql
+++ b/src-db/database/model/prescript-PostgreSql.sql
@@ -1536,3 +1536,40 @@
 -- INSTALL PG_TRGM EXTENSION
 CREATE EXTENSION IF NOT EXISTS "pg_trgm"
 /-- END
+
+CREATE OR REPLACE FUNCTION obequals(
+    p_number_a numeric,
+    p_number_b numeric)
+  RETURNS char AS
+$BODY$ DECLARE
+/*************************************************************************
+* The contents of this file are subject to the Openbravo  Public  License
+* Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
+* Version 1.1  with a permitted attribution clause; you may not  use this
+* file except in compliance with the License. You  may  obtain  a copy of
+* the License at http://www.openbravo.com/legal/license.html
+* Software distributed under the License  is  distributed  on  an "AS IS"
+* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
+* License for the specific  language  governing  rights  and  limitations
+* under the License.
+* The Original Code is Openbravo ERP.
+* The Initial Developer of the Original Code is Openbravo SLU
+* All portions are Copyright (C) 2017 Openbravo SLU
+* All Rights Reserved.
+* Contributor(s):  ______________________________________.
+************************************************************************/
+/**
+* Returns 'Y' when both numbers are equals, else returns 'N'
+* This function is used as index in FIN_Payment table
+**/
+ v_dif NUMERIC;
+BEGIN
+    v_dif := coalesce(p_number_a, 0) - coalesce(p_number_b, 0);
+    IF (v_dif = 0) THEN
+     return 'Y';
+    ELSE
+     return 'N';
+    END IF;
+END; $BODY$
+  LANGUAGE plpgsql IMMUTABLE
+/-- END
diff --git a/src-db/database/model/tables/FIN_PAYMENT.xml b/src-db/database/model/tables/FIN_PAYMENT.xml
--- a/src-db/database/model/tables/FIN_PAYMENT.xml
+++ b/src-db/database/model/tables/FIN_PAYMENT.xml
@@ -187,6 +187,10 @@
       <foreign-key foreignTable="USER2" name="FIN_PAYMENT_USER2">
         <reference local="USER2_ID" foreign="USER2_ID"/>
       </foreign-key>
+      <index name="FIN_PAYMENT_BPARTNER_CREDIT" unique="false">
+        <index-column name="C_BPARTNER_ID"/>
+        <index-column name="functionBasedColumn" functionExpression="OBEQUALS(GENERATED_CREDIT,USED_CREDIT)"/>
+      </index>
       <index name="FIN_PAYMENT_POSTED" unique="false">
         <index-column name="POSTED"/>
       </index>