Fixes Issue 37199:Date filtering criteria not properly carried when
authorAtul Gaware <atul.gaware@openbravo.com>
Tue, 07 Nov 2017 23:31:08 +0530
changeset 32966 7ba2441f5afa
parent 32965 8968fc3eb9fe
child 32967 f1b82b7deb61
Fixes Issue 37199:Date filtering criteria not properly carried when
navigating from General Ledger Report to Journal Entries Report

** Accounting Schema Filter is mandatory in Journal Entries Report
so far fetching strFactAcctGroupId is it necessary to remove
the isEmpty check for strcAcctSchemaId as it wont be empty
in any case.

** In case of printing pdf or xls report when factacctgroup id or
record id is not passed (default), a reportslimit check is done to
avoid performance problem.
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 Nov 08 17:46:31 2017 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal.java	Tue Nov 07 23:31:08 2017 +0530
@@ -362,10 +362,10 @@
        * strFactAcctGroupId(will take care of criteria from current screen)
        */
       String strFactAcctGroupId = "";
-      if (StringUtils.isEmpty(strcAcctSchemaId) && StringUtils.isEmpty(strDateFrom)
-          && StringUtils.isEmpty(strDocument) && StringUtils.equals(strOrg, "0")
-          && StringUtils.isEmpty(strShowClosing) && StringUtils.isEmpty(strShowReg)
-          && StringUtils.isEmpty(strShowOpening) && StringUtils.isEmpty(strRecord)) {
+      if (StringUtils.isEmpty(strDateFrom) && StringUtils.isEmpty(strDocument)
+          && StringUtils.equals(strOrg, "0") && StringUtils.isEmpty(strShowClosing)
+          && StringUtils.isEmpty(strShowReg) && StringUtils.isEmpty(strShowOpening)
+          && StringUtils.isEmpty(strRecord)) {
 
         int currentHistoryIndex = new Integer(
             new VariablesHistory(request).getCurrentHistoryIndex()).intValue();
@@ -845,14 +845,36 @@
       } else if (StringUtils.isEmpty(strRecord)) {
         String strCheck = buildCheck(strShowClosing, strShowReg, strShowOpening, strShowRegular,
             strShowDivideUp);
-        scrollData = ReportGeneralLedgerJournalData.select(readOnlyCP, "0",
-            StringUtils.equals(strShowDescription, "Y") ? "'Y'" : "'N'",
+        int recordCount = Integer.parseInt(ReportGeneralLedgerJournalData.selectCount2(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,
             strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
-            vars.getLanguage(), null, null, null);
+            StringUtils.equals(strShowDescription, "Y") ? "'Y'" : "'N'"));
+        int limit = Integer.parseInt(Utility.getPreference(vars, "ReportsLimit", ""));
+        if (vars.commandIn("XLS") && recordCount > 65532) {
+          advisePopUp(request, response, "ERROR",
+              Utility.messageBD(readOnlyCP, "ProcessStatus-E", vars.getLanguage()),
+              Utility.messageBD(readOnlyCP, "numberOfRowsExceeded", vars.getLanguage()));
+        } else if (limit > 0 && recordCount > limit) {
+          advisePopUp(
+              request,
+              response,
+              "WARNING",
+              Utility.messageBD(readOnlyCP, "ProcessStatus-W", vars.getLanguage()),
+              Utility.messageBD(readOnlyCP, "ReportsLimit", vars.getLanguage()).replace("@limit@",
+                  String.valueOf(limit)));
+        } else {
+          scrollData = ReportGeneralLedgerJournalData.select(readOnlyCP, "0",
+              StringUtils.equals(strShowDescription, "Y") ? "'Y'" : "'N'",
+              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,
+              strOrgFamily, strCheck, strAllaccounts, strcelementvaluefrom, strcelementvalueto,
+              vars.getLanguage(), null, null, null);
+        }
       } else {
         scrollData = ReportGeneralLedgerJournalData.selectDirect(readOnlyCP, "0",
             StringUtils.equals(strShowDescription, "Y") ? "'Y'" : "'N'",
@@ -866,15 +888,10 @@
       }
       data = new ReportGeneralLedgerJournalData[res.size()];
       res.copyInto(data);
-
       if (data == null || data.length == 0) {
         advisePopUp(request, response, "WARNING",
             Utility.messageBD(readOnlyCP, "ProcessStatus-W", vars.getLanguage()),
             Utility.messageBD(readOnlyCP, "NoDataFound", vars.getLanguage()));
-      } else if (vars.commandIn("XLS") && data.length > 65532) {
-        advisePopUp(request, response, "ERROR",
-            Utility.messageBD(readOnlyCP, "ProcessStatus-E", vars.getLanguage()),
-            Utility.messageBD(readOnlyCP, "numberOfRowsExceeded", vars.getLanguage()));
       } else {
         String strSubtitle = (Utility.messageBD(readOnlyCP, "LegalEntity", vars.getLanguage()) + ": ")
             + ReportGeneralLedgerJournalData.selectCompany(readOnlyCP, vars.getClient()) + "\n";
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql	Wed Nov 08 17:46:31 2017 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerJournal_data.xsql	Tue Nov 07 23:31:08 2017 +0530
@@ -99,6 +99,59 @@
     <Parameter name="oraLimit1" type="argument" optional="true" after=") B"><![CDATA[WHERE ROWNUM <= ]]></Parameter>
     <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter>
   </SqlMethod>
+  <SqlMethod name="selectCount2" type="preparedStatement" return="string" default="0">
+    <SqlMethodComment></SqlMethodComment>
+    <Sql>
+    <![CDATA[
+      SELECT COUNT(1) AS TOTAL
+      FROM (SELECT 1
+      FROM FACT_ACCT F
+      WHERE F.AD_CLIENT_ID IN ('1')
+      AND F.AD_ORG_ID IN('1')
+      AND 1=1
+      AND f.AD_ORG_ID IN('2')
+      AND F.FactAcctType IN ('C','N','O','R','D')
+      AND (?='Y' OR F.FACT_ACCT_GROUP_ID IN (
+                SELECT FACT_ACCT_GROUP_ID FROM FACT_ACCT WHERE ACCOUNT_ID IN (
+                  SELECT c_elementvalue_id as name
+                  FROM C_ELEMENTVALUE
+                  WHERE value >= (  select value from c_elementvalue where c_elementvalue_id = ?)
+                    and value <= (  select value from c_elementvalue where c_elementvalue_id = ?)
+                    and c_elementvalue.ELEMENTLEVEL = 'S')
+                  AND AD_CLIENT_ID IN ('3')
+                  AND AD_ORG_ID IN ('3')
+                  AND 3=3
+                  AND AD_ORG_ID IN('4')
+                  AND FactAcctType IN ('C','N','O','R','D')
+                ))
+      GROUP BY f.C_ACCTSCHEMA_ID, F.AD_TABLE_ID, F.DATEACCT, CASE WHEN 'Y' = 'Y' THEN TO_CHAR(F.DESCRIPTION) ELSE TO_CHAR('') END,
+      F.DOCBASETYPE, F.RECORD_ID,
+      F.FACT_ACCT_GROUP_ID, F.ACCOUNT_ID,F.FACTACCTTYPE,
+      (CASE F.AMTACCTDR WHEN 0 THEN (CASE SIGN(F.AMTACCTCR) WHEN -1 THEN 1 ELSE 2 END) ELSE (CASE SIGN(F.AMTACCTDR) WHEN -1 THEN 3 ELSE 4 END) END), F.AD_CLIENT_ID
+      HAVING sum(F.AMTACCTDR) <> 0 OR sum(F.AMTACCTCR) <> 0) AA
+      ]]></Sql>
+    <Parameter name="adUserClient" type="replace" optional="true" after="WHERE F.AD_CLIENT_ID IN (" text="'1'"/>
+    <Parameter name="adUserOrg" type="replace" optional="true" after="AND F.AD_ORG_ID IN(" text="'1'"/>
+    <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="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'"/>
+    <Parameter name="allaccounts"/>
+    <Parameter name="accountFrom"/>
+    <Parameter name="accountTo"/>
+    <Parameter name="adUserClient" type="replace" optional="true" after="AND AD_CLIENT_ID IN (" text="'3'"/>
+    <Parameter name="adUserOrg" type="replace" optional="true" after="AND AD_ORG_ID IN (" text="'3'"/>
+    <Parameter name="parDateFrom" optional="true" after="AND 3=3"><![CDATA[ AND dateacct >= TO_DATE(?)]]></Parameter>
+    <Parameter name="parDateTo" optional="true" after="AND 3=3"><![CDATA[ AND dateacct < TO_DATE(?)]]></Parameter>
+    <Parameter name="docbasetype" optional="true" after="AND 3=3"><![CDATA[ AND DOCBASETYPE = ?]]></Parameter>
+    <Parameter name="acctschema" optional="true" after="AND 3=3"><![CDATA[ AND C_ACCTSCHEMA_ID = ?]]></Parameter>
+    <Parameter name="orgFamily" type="replace" optional="true" after="AND AD_ORG_ID IN(" text="'4'"/>
+    <Parameter name="checks" type="replace" optional="true" after="AND FactAcctType IN (" text="'C','N','O','R','D'"/>
+    <Parameter name="descriptionGrouping" type="replace" optional="true" after="F.DATEACCT, CASE WHEN " text="'Y'"/>
+  </SqlMethod>
   <SqlMethod name="selectCountGroupedLines" type="preparedStatement" return="scrollable">
     <SqlMethodComment></SqlMethodComment>
     <Sql>