Fixes issue 41712: Subquery in getDocumentNo in ReportGeneralLedgerJournal is
authorArmaignac <collazoandy4@gmail.com>
Wed, 04 Sep 2019 01:29:12 -0400
changeset 36559 97b1ce519519
parent 36287 bda45dd2e170
child 36560 b3393dd2ea1e
Fixes issue 41712: Subquery in getDocumentNo in ReportGeneralLedgerJournal is
not using bind-params

Added the missing bind-params
Now all documents id with the same documentNo is used to filter the accounting records
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java	Wed Aug 21 04:47:43 2019 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java	Wed Sep 04 01:29:12 2019 -0400
@@ -37,6 +37,7 @@
 import org.codehaus.jettison.json.JSONArray;
 import org.codehaus.jettison.json.JSONException;
 import org.codehaus.jettison.json.JSONObject;
+import org.hibernate.criterion.Restrictions;
 import org.hibernate.query.Query;
 import org.openbravo.base.secureApp.HttpSecureAppServlet;
 import org.openbravo.base.secureApp.VariablesHistory;
@@ -44,6 +45,7 @@
 import org.openbravo.base.structure.BaseOBObject;
 import org.openbravo.client.kernel.RequestContext;
 import org.openbravo.dal.core.OBContext;
+import org.openbravo.dal.service.OBCriteria;
 import org.openbravo.dal.service.OBDal;
 import org.openbravo.dal.service.OBQuery;
 import org.openbravo.database.ConnectionProvider;
@@ -540,7 +542,7 @@
                 rowNum, Utility.getContext(readOnlyCP, vars, "#User_Client", "ReportGeneralLedger"),
                 Utility.getContext(readOnlyCP, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
                 strDateFrom, DateTimeData.nDaysAfter(readOnlyCP, strDateTo, "1"), strDocument,
-                getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
+                getDocumentIds(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
                 strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
                 null, null, null);
             Vector<ReportGeneralLedgerJournalData> res = new Vector<ReportGeneralLedgerJournalData>();
@@ -619,7 +621,7 @@
               Utility.getContext(readOnlyCP, vars, "#User_Client", "ReportGeneralLedger"),
               Utility.getContext(readOnlyCP, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
               strDateFrom, DateTimeData.nDaysAfter(readOnlyCP, strDateTo, "1"), strDocument,
-              getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
+              getDocumentIds(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
               strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
               vars.getLanguage(), pgLimit, oraLimit1, oraLimit2);
           Vector<ReportGeneralLedgerJournalData> res = new Vector<ReportGeneralLedgerJournalData>();
@@ -633,7 +635,7 @@
                 Utility.getContext(readOnlyCP, vars, "#User_Client", "ReportGeneralLedger"),
                 Utility.getContext(readOnlyCP, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
                 strDateFrom, DateTimeData.nDaysAfter(readOnlyCP, strDateTo, "1"), strDocument,
-                getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
+                getDocumentIds(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
                 strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
                 data[0].dateacct, data[0].identifier);
           }
@@ -918,7 +920,7 @@
             readOnlyCP, Utility.getContext(readOnlyCP, vars, "#User_Client", "ReportGeneralLedger"),
             Utility.getContext(readOnlyCP, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
             strDateFrom, DateTimeData.nDaysAfter(readOnlyCP, strDateTo, "1"), strDocument,
-            getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
+            getDocumentIds(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
             strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
             StringUtils.equals(strShowDescription, "Y") ? "'Y'" : "'N'"));
         // Get the limit of number of records shown for a report based on the preference
@@ -941,7 +943,7 @@
               Utility.getContext(readOnlyCP, vars, "#User_Client", "ReportGeneralLedger"),
               Utility.getContext(readOnlyCP, vars, "#AccessibleOrgTree", "ReportGeneralLedger"),
               strDateFrom, DateTimeData.nDaysAfter(readOnlyCP, strDateTo, "1"), strDocument,
-              getDocumentNo(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
+              getDocumentIds(vars.getClient(), strDocument, strDocumentNo), strcAcctSchemaId,
               strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
               vars.getLanguage(), null, null, null);
         }
@@ -1132,7 +1134,7 @@
   /**
    * Builds dynamic SQL to filter by document No
    */
-  private String getDocumentNo(String strClient, String strDocument, String strDocumentNo) {
+  private String getDocumentIds(String strClient, String strDocument, String strDocumentNo) {
     if (StringUtils.isBlank(strDocument) || StringUtils.isBlank(strDocumentNo)) {
       return null;
     }
@@ -1143,13 +1145,13 @@
       documentNo = documentNo.replaceAll(";", "");
 
       StringBuffer where = new StringBuffer();
-      where.append(" select t." + Table.PROPERTY_DBTABLENAME);
+      where.append(" select t." + Table.PROPERTY_NAME);
       where.append(" from " + DocumentType.ENTITY_NAME + " as d");
       where.append(" join d." + DocumentType.PROPERTY_TABLE + " as t");
       where.append(" where d." + DocumentType.PROPERTY_DOCUMENTCATEGORY + " = :document");
       where.append(" and d." + DocumentType.PROPERTY_CLIENT + ".id = :client");
       where.append(" group by d." + DocumentType.PROPERTY_DOCUMENTCATEGORY);
-      where.append(" , t." + Table.PROPERTY_DBTABLENAME);
+      where.append(" , t." + Table.PROPERTY_NAME);
       Query<String> qry = OBDal.getReadOnlyInstance()
           .getSession()
           .createQuery(where.toString(), String.class);
@@ -1162,11 +1164,10 @@
         return null;
       }
 
-      StringBuffer existsSubQuery = new StringBuffer("( SELECT 1 FROM ");
-      existsSubQuery.append(tablename);
-      existsSubQuery.append(" dt WHERE f.record_id = dt.").append(tablename).append("_id");
-      existsSubQuery.append(" AND dt.documentno = '").append(documentNo).append("' )");
-      return existsSubQuery.toString();
+      OBCriteria<BaseOBObject> query = OBDal.getInstance().createCriteria(tablename);
+      query.add(Restrictions.eq("documentNo", documentNo));
+
+      return Utility.getInStrList(query.list(), true);
     } catch (Exception ignore) {
       return null;
     } finally {
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql	Wed Aug 21 04:47:43 2019 +0000
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql	Wed Sep 04 01:29:12 2019 -0400
@@ -12,7 +12,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) 2001-2018 Openbravo SLU
+ * All portions are Copyright (C) 2001-2019 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  ************************************************************************
@@ -77,7 +77,7 @@
     <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
     <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
     <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
-    <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
+    <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND F.RECORD_ID IN ]]></Parameter>
     <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
     <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
     <Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R','D'"/>    
@@ -137,7 +137,7 @@
     <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
     <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
     <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
-    <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
+    <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND F.RECORD_ID IN ]]></Parameter>
     <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
     <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
     <Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -206,7 +206,7 @@
     <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
     <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
     <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
-    <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
+    <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND F.RECORD_ID IN ]]></Parameter>
     <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
     <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
     <Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R','D'"/>
@@ -266,7 +266,7 @@
     <Parameter name="parDateFrom" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct >= TO_DATE(?)]]></Parameter>
     <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[ AND f.dateacct < TO_DATE(?)]]></Parameter>
     <Parameter name="docbasetype" optional="true" after="AND 1=1"><![CDATA[ AND f.DOCBASETYPE = ?]]></Parameter>
-    <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND EXISTS ]]></Parameter>
+    <Parameter name="documentNo" optional="true" after="AND 1=1" type="argument"><![CDATA[ AND F.RECORD_ID IN ]]></Parameter>
     <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[ AND f.C_ACCTSCHEMA_ID = ?]]></Parameter>
     <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/>
     <Parameter name="checks" type="replace" optional="true" after="AND F.FactAcctType IN (" text="'C','N','O','R','D'"/>