details: https://code.openbravo.com/erp/devel/pi/rev/23a3621892b3 changeset: 32297:23a3621892b3 user: Mark <markmm82 <at> gmail.com> date: Fri Jun 09 12:19:57 2017 -0400 summary: Fixes issue 35764: The browser get stucked when printing the receivable aging schedule report showing details and having a lot of invoices pending.
After printing the receivable/payable aging schedule report on an environment with a lot of invoices pending to be paid, the report took too long to be printed. Most of the time was consumed by chrome rendering the server response. In order to reduce the memory usage, was limited the displayed data with the value of the "ReportsLimit" preference. When record count exceeds this limit, is shown a warning informing data has been limited. As problem happens when the report runs in HTML format, data only will be limited in this case and not when report runs in PDF or XLS formats. As Aging Schedule reports are defined as Process Definition, and results of run as HTML is shown in a new window containing the JR with results of the selected filters, was added to the affected JRXML files a new hidden Text Field that will be shown only when the data is limited. This way, user will be notified after executing the report in both windows, selected filters and report results. To achieve this functionality the following changes were done: -Adapted AgingDao_data and AgingDao to make possible to limit queries to PG and Oracle databases. -Adapted AgingBalanceReportActionHandler to limit data if report is created in HTML format. If the data is limited are stored as a session variables to make it available to AgingDao methods. Then a warning message will be shown in the affected reports if the limit is reached. -Added a new "warning" parameter to AgingScheduleDetailHTML and AgingScheduleHTML reports. It is linked to a hidden text that only will be displayed if the maximum of records count defined in the preference to reports is reached. details: https://code.openbravo.com/erp/devel/pi/rev/91dbb09a074c changeset: 32298:91dbb09a074c user: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com> date: Mon Jun 12 20:45:35 2017 +0200 summary: Related to issue 35764: Code review improvements Create new getReportsLimit() and runReport() methods to avoid code duplication. diffstat: src/org/openbravo/common/actionhandler/AgingBalanceReportActionHandler.java | 53 +++++++++- src/org/openbravo/erpCommon/ad_reports/AgingDao.java | 33 +++++- src/org/openbravo/erpCommon/ad_reports/AgingDao_data.xsql | 14 ++- src/org/openbravo/erpCommon/ad_reports/AgingScheduleDetailHTML.jrxml | 18 +++ src/org/openbravo/erpCommon/ad_reports/AgingScheduleHTML.jrxml | 20 +++- 5 files changed, 127 insertions(+), 11 deletions(-) diffs (truncated from 370 to 300 lines): diff -r 93272d393045 -r 91dbb09a074c src/org/openbravo/common/actionhandler/AgingBalanceReportActionHandler.java --- a/src/org/openbravo/common/actionhandler/AgingBalanceReportActionHandler.java Mon Jun 12 16:27:26 2017 +0000 +++ b/src/org/openbravo/common/actionhandler/AgingBalanceReportActionHandler.java Mon Jun 12 20:45:35 2017 +0200 @@ -89,6 +89,7 @@ private static final String PARAM_COLUMN2 = "Column2"; private static final String PARAM_COLUMN3 = "Column3"; private static final String PARAM_COLUMN4 = "Column4"; + private static final String PARAM_WARNING = "warning"; private static final String TRUE = "true"; private static final String FALSE = "false"; private static final String BLANK = ""; @@ -213,6 +214,9 @@ } finally { OBContext.restorePreviousMode(); } + // Save in session report limit + RequestContext.get().getVariablesSecureApp() + .setSessionObject("reportsLimit", getReportsLimit(content)); data = dao.getOpenReceivablesAgingScheduleDetails(getReportConnectionProvider(), currentDate, dateFormat, convCurrency, new OrganizationStructureProvider().getChildTree( getParameter(PARAM_ORGANIZATION, content), true), @@ -226,6 +230,7 @@ private JSONObject printPageDetails(Map<String, Object> parameters) { JSONObject result = new JSONObject(); String content = (String) parameters.get(CONTENT_PARAM); + FieldProvider[] data; try { data = getPageDetailsData(content); @@ -237,8 +242,7 @@ result.put(MESSAGE_SHOW_RESULTS_IN_PROCESS_VIEW_PROPERTY, true); result.put(MESSAGE_MESSAGE_PROPERTY, msg); } else { - parameters.put(KEY_REPORT_DATA, data); - result = super.doExecute(parameters, content); + result = runReport(data, parameters, content); } } catch (Exception ex) { JSONObject msg = new JSONObject(); @@ -263,6 +267,10 @@ cal.setTime(currentDate); boolean showDoubtful = TRUE.equals(getParameter(PARAM_DOUBTFUL, content)); boolean excludeVoid = FALSE.equals(getParameter(PARAM_SHOWVOID, content)); + + // Save in session report limit + RequestContext.get().getVariablesSecureApp() + .setSessionObject("reportsLimit", getReportsLimit(content)); data = dao.getOpenReceivablesAgingSchedule(getReportConnectionProvider(), getParameter(PARAM_BP, content), getParameter(PARAM_GL, content), currentDate, getParameter(PARAM_COLUMN1, content), getParameter(PARAM_COLUMN2, content), @@ -277,6 +285,7 @@ JSONObject result = new JSONObject(); FieldProvider[] data; String content = (String) parameters.get(CONTENT_PARAM); + try { data = getPageScheduleData(content); if (data.length == 0) { @@ -287,8 +296,7 @@ result.put(MESSAGE_SHOW_RESULTS_IN_PROCESS_VIEW_PROPERTY, true); result.put(MESSAGE_MESSAGE_PROPERTY, msg); } else { - parameters.put(KEY_REPORT_DATA, data); - result = super.doExecute(parameters, content); + result = runReport(data, parameters, content); } } catch (Exception ex) { JSONObject msg = new JSONObject(); @@ -490,8 +498,45 @@ } parameters.put("USER_ORG", Utility.getContext(conn, vars, "#User_Org", BLANK)); parameters.put("REPORT_TITLE", parameters.get("title")); + if (((JSONObject) jsonContent.get(PARAM_PARAM)).has(PARAM_WARNING)) { + parameters.put("warning", getParameter(PARAM_WARNING, jsonContent)); + } } catch (Exception e) { throw new OBException(e); } } + + private int getReportsLimit(String content) throws JSONException { + if (StringUtils.equals(getParameter(PARAM_ACTION, content), "HTML")) { + final VariablesSecureApp vars = RequestContext.get().getVariablesSecureApp(); + String limitPreference = Utility.getPreference(vars, "ReportsLimit", ""); + return Integer.parseInt(StringUtils.isEmpty(limitPreference) ? "0" : limitPreference); + } + return 0; + } + + private JSONObject runReport(FieldProvider[] data, Map<String, Object> parameters, String content) + throws JSONException { + JSONObject msg = null; + JSONObject paramContent = new JSONObject(content); + int limit = (int) RequestContext.get().getVariablesSecureApp().getSessionObject("reportsLimit"); + + if (limit > 0 && data.length > limit) { + msg = new JSONObject(); + String msgbody = OBMessageUtils.messageBD("ReportsLimit"); + msgbody = msgbody.replace("@limit@", String.valueOf(limit)); + // Add warning to the report + ((JSONObject) paramContent.get(PARAM_PARAM)).put(PARAM_WARNING, msgbody); + msg.put(MESSAGE_SEVERITY_PROPERTY, MESSAGE_SEVERITY_WARNING); + msg.put(MESSAGE_TEXT_PROPERTY, msgbody); + } + + parameters.put(KEY_REPORT_DATA, data); + JSONObject result = super.doExecute(parameters, paramContent.toString()); + if (msg != null) { + result.put(MESSAGE_MESSAGE_PROPERTY, msg); + } + + return result; + } } diff -r 93272d393045 -r 91dbb09a074c src/org/openbravo/erpCommon/ad_reports/AgingDao.java --- a/src/org/openbravo/erpCommon/ad_reports/AgingDao.java Mon Jun 12 16:27:26 2017 +0000 +++ b/src/org/openbravo/erpCommon/ad_reports/AgingDao.java Mon Jun 12 20:45:35 2017 +0200 @@ -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) 2012-2016 Openbravo SLU + * All portions are Copyright (C) 2012-2017 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -41,6 +41,8 @@ import org.apache.log4j.Logger; import org.openbravo.advpaymentmngt.utility.FIN_Utility; import org.openbravo.base.exception.OBException; +import org.openbravo.base.secureApp.VariablesSecureApp; +import org.openbravo.client.kernel.RequestContext; import org.openbravo.dal.core.OBContext; import org.openbravo.dal.service.OBDal; import org.openbravo.data.FieldProvider; @@ -98,6 +100,17 @@ AgingDaoData dataSR = null; AgingDaoData dataCreditSR = null; long init = System.currentTimeMillis(); + final VariablesSecureApp vars = RequestContext.get().getVariablesSecureApp(); + int limit = (int) vars.getSessionObject("reportsLimit"); + String pgLimit = null, oraLimit = null; + if (limit > 0) { + if (connectionProvider.getRDBMS().equalsIgnoreCase("ORACLE")) { + oraLimit = String.valueOf(limit + 1); + } else { + pgLimit = String.valueOf(limit + 1); + } + } + try { // Amounts coming from normal PSD (non credit) dataSR = AgingDaoData.select(connectionProvider, showDoubtfulDebt ? "Y" : "N", OBDateUtils @@ -107,7 +120,7 @@ .formatDate(convertToDate(currentDate, strcolumn3)), OBDateUtils .formatDate(convertToDate(currentDate, strcolumn4)), Utility .getInStrSet(organizations), strcBpartnerId, StringUtils.equals(recOrPay, - "RECEIVABLES") ? "Y" : "N", excludeVoids ? "excludeVoids" : ""); + "RECEIVABLES") ? "Y" : "N", excludeVoids ? "excludeVoids" : "", pgLimit, oraLimit); log4j.debug("Query: " + (System.currentTimeMillis() - init)); init = System.currentTimeMillis(); int i = 0; @@ -143,7 +156,7 @@ dataCreditSR = AgingDaoData.selectCredit(connectionProvider, convCurrency.getId(), Utility .getInStrSet(organizations), Utility.getInStrSet(new HashSet<String>(paidStatus)), OBDateUtils.formatDate(currentDate), StringUtils.equals(recOrPay, "RECEIVABLES") ? "Y" - : "N", strcBpartnerId); + : "N", strcBpartnerId, pgLimit, oraLimit); log4j.debug("Credit Query: " + (System.currentTimeMillis() - init)); init = System.currentTimeMillis(); i = 0; @@ -210,6 +223,16 @@ OBContext.setAdminMode(true); long init = System.currentTimeMillis(); + int limit = (int) RequestContext.get().getVariablesSecureApp().getSessionObject("reportsLimit"); + String pgLimit = null, oraLimit = null; + if (limit > 0) { + if (connectionProvider.getRDBMS().equalsIgnoreCase("ORACLE")) { + oraLimit = String.valueOf(limit + 1); + } else { + pgLimit = String.valueOf(limit + 1); + } + } + try { // Amounts coming from normal PSD (non credit) dataSR = AgingDaoData.selectDetail(connectionProvider, convCurrency.getId(), @@ -219,7 +242,7 @@ .formatDate(convertToDate(currentDate, strcolumn3)), OBDateUtils .formatDate(convertToDate(currentDate, strcolumn4)), Utility .getInStrSet(organizations), strcBpartnerId, StringUtils.equals(recOrPay, - "RECEIVABLES") ? "Y" : "N", excludeVoid ? "excludeVoids" : ""); + "RECEIVABLES") ? "Y" : "N", excludeVoid ? "excludeVoids" : "", pgLimit, oraLimit); log4j.debug("Query Detail: " + (System.currentTimeMillis() - init)); init = System.currentTimeMillis(); @@ -257,7 +280,7 @@ dataCreditSR = AgingDaoData.selectCredit(connectionProvider, convCurrency.getId(), Utility .getInStrSet(organizations), Utility.getInStrSet(new HashSet<String>(paidStatus)), OBDateUtils.formatDate(currentDate), StringUtils.equals(recOrPay, "RECEIVABLES") ? "Y" - : "N", strcBpartnerId); + : "N", strcBpartnerId, pgLimit, oraLimit); log4j.debug("Credit Query: " + (System.currentTimeMillis() - init)); init = System.currentTimeMillis(); i = 0; diff -r 93272d393045 -r 91dbb09a074c src/org/openbravo/erpCommon/ad_reports/AgingDao_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/AgingDao_data.xsql Mon Jun 12 16:27:26 2017 +0000 +++ b/src/org/openbravo/erpCommon/ad_reports/AgingDao_data.xsql Mon Jun 12 20:45:35 2017 +0200 @@ -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) 2016 Openbravo SLU + * All portions are Copyright (C) 2016-2017 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -24,6 +24,7 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ + SELECT B.* FROM ( SELECT A.bpId, bp.name as bpName, SUM(A.amount) as amount, A.scope, 0 as credit, '' as invoiceId, '' as docNo, '' as dateInvoiced, SUM(doubtfuldebtamt) as doubtfuldebt FROM ( SELECT i.c_bpartner_id AS bpId, @@ -65,6 +66,7 @@ ) A INNER JOIN c_bpartner bp on (A.bpId = bp.c_bpartner_id) GROUP BY A.bpId, bp.name, A.scope + ) B ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="showDoubtfulDebt"/> @@ -85,12 +87,15 @@ <Parameter name="excludeVoids" type="none" optional="true" after="AND 1=1" text="AND i.DOCSTATUS NOT IN ('VO')"/> <Parameter name="asOfDate"/> <Parameter name="asOfDate"/> + <Parameter name="pgLimit" type="argument" optional="true" after=") B"><![CDATA[LIMIT ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after=") B"><![CDATA[WHERE ROWNUM <= ]]></Parameter> </SqlMethod> <SqlMethod name="selectCredit" type="preparedStatement" return="scrollable"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ + SELECT B.* FROM ( SELECT A.credit, bp.c_bpartner_id as bpId, bp.name as bpName, A.dateInvoiced as dateInvoiced, A.invoiceId as invoiceId, A.docNo FROM ( SELECT FIN_AGING_PAYMENTCURRENCY_RATE(P.fin_payment_id, ?, P.c_currency_id) * SUM(P.GENERATED_CREDIT - coalesce(C.USED_CREDIT, 0)) AS credit, P.c_bpartner_id, @@ -144,6 +149,7 @@ ) A INNER JOIN c_bpartner bp ON A.c_bpartner_id = bp.c_bpartner_id WHERE A.credit <> 0 + ) B ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="currencyId"/> @@ -154,12 +160,15 @@ <Parameter name="cbPartnerId" optional="true" type="argument" after="AND 1=1"><![CDATA[ AND fp.C_BPARTNER_ID IN]]></Parameter> <Parameter name="asOfDate"/> <Parameter name="paidStatus" type="replace" optional="true" after="OR ( fp1.status IN (" text="'1'"/> + <Parameter name="pgLimit" type="argument" optional="true" after=") B"><![CDATA[LIMIT ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after=") B"><![CDATA[WHERE ROWNUM <= ]]></Parameter> </SqlMethod> <SqlMethod name="selectDetail" type="preparedStatement" return="scrollable"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ + SELECT C.* FROM ( SELECT B.bpId, bp.name as bpName, FIN_AGING_INVOICECURRENCY_RATE(B.c_invoice_id, ?, B.c_currency_id) * B.amount as amount, B.scope, B.c_invoice_id as invoiceId, B.documentNo as docNo, B.dateacct as dateInvoiced, FIN_AGING_INVOICECURRENCY_RATE(B.c_invoice_id, ?, B.c_currency_id) * (B.doubtfuldebtamt) as doubtfuldebt FROM ( @@ -210,6 +219,7 @@ GROUP BY A.c_invoice_id, A.c_currency_id, A.bpId, A.documentNo, A.dateacct, A.scope ) B INNER JOIN c_bpartner bp on (B.bpId = bp.c_bpartner_id) + ) C ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="currencyId"/> @@ -229,6 +239,8 @@ <Parameter name="excludeVoids" type="none" optional="true" after="AND 1=1" text="AND i.DOCSTATUS NOT IN ('VO')"/> <Parameter name="asOfDate"/> <Parameter name="asOfDate"/> + <Parameter name="pgLimit" type="argument" optional="true" after=") C"><![CDATA[LIMIT ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after=") C"><![CDATA[WHERE ROWNUM <= ]]></Parameter> </SqlMethod> </SqlClass> diff -r 93272d393045 -r 91dbb09a074c src/org/openbravo/erpCommon/ad_reports/AgingScheduleDetailHTML.jrxml --- a/src/org/openbravo/erpCommon/ad_reports/AgingScheduleDetailHTML.jrxml Mon Jun 12 16:27:26 2017 +0000 +++ b/src/org/openbravo/erpCommon/ad_reports/AgingScheduleDetailHTML.jrxml Mon Jun 12 20:45:35 2017 +0200 @@ -69,6 +69,7 @@ <parameter name="dateFromUI" class="java.lang.String" isForPrompting="false"> <parameterDescription><![CDATA[]]></parameterDescription> </parameter> + <parameter name="warning" class="java.lang.String"/> <queryString> ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Openbravo-commits mailing list Openbravo-commits@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-commits
