details: https://code.openbravo.com/erp/devel/pi/rev/e7d94cdd2474
changeset: 35273:e7d94cdd2474
user: Atul Gaware <atul.gaware <at> openbravo.com>
date: Wed Dec 12 11:55:22 2018 +0530
summary: Fixes Issue 39712:Performance issue in Sales Dimensional Report
For avoiding system crash, data is fetched with limit of preference
ReportLimit. If data count is more than ReportLimit, warning message
is shown to use apply more selective filters.
diffstat:
src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR.java
| 518 ++++-----
src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR_data.xsql
| 112 +-
2 files changed, 257 insertions(+), 373 deletions(-)
diffs (truncated from 857 to 300 lines):
diff -r 068b3a649043 -r e7d94cdd2474
src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR.java
---
a/src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR.java
Wed Dec 26 13:40:46 2018 +0100
+++
b/src/org/openbravo/erpCommon/ad_reports/ReportInvoiceCustomerDimensionalAnalysesJR.java
Wed Dec 12 11:55:22 2018 +0530
@@ -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) 2001-2017 Openbravo SLU
+ * All portions are Copyright (C) 2001-2018 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************
@@ -27,9 +27,6 @@
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
-import net.sf.jasperreports.engine.JRException;
-import net.sf.jasperreports.engine.JasperReport;
-
import org.apache.commons.lang.StringUtils;
import org.openbravo.base.filter.IsIDFilter;
import org.openbravo.base.filter.IsPositiveIntFilter;
@@ -60,6 +57,9 @@
import org.openbravo.utils.Replace;
import org.openbravo.xmlEngine.XmlDocument;
+import net.sf.jasperreports.engine.JRException;
+import net.sf.jasperreports.engine.JasperReport;
+
public class ReportInvoiceCustomerDimensionalAnalysesJR extends
HttpSecureAppServlet {
private static final long serialVersionUID = 1L;
@@ -677,7 +677,7 @@
}
ReportInvoiceCustomerDimensionalAnalysesJRData[] data = null;
- ReportInvoiceCustomerDimensionalAnalysesJRData dataXLS = null;
+ ReportInvoiceCustomerDimensionalAnalysesJRData[] dataXLS = null;
String[] strShownArray = { "", "", "", "", "", "", "", "", "", "" };
if (localStrShown.startsWith("(")) {
localStrShown = localStrShown.substring(1, localStrShown.length() - 1);
@@ -703,9 +703,6 @@
vars.getLanguage(), "");
}
- // Checking report limit first
- StringBuffer levelsconcat = new StringBuffer();
- levelsconcat.append("''");
String[] strLevelLabel = { "", "", "", "", "", "", "", "", "", "" };
String[] strTextShow = { "", "", "", "", "", "", "", "", "", "" };
int intOrder = 0;
@@ -716,21 +713,15 @@
strTextShow[i] = "C_BP_GROUP.NAME";
intOrder++;
strLevelLabel[i] = dimensionLabel[0].name;
- levelsconcat.append(" || ");
- levelsconcat.append("C_BP_GROUP.C_BP_GROUP_ID");
} else if (StringUtils.equals(strShownArray[i], "2")) {
strTextShow[i] = "AD_COLUMN_IDENTIFIER(to_char('C_Bpartner'), to_char(
C_BPARTNER.C_BPARTNER_ID), to_char( '"
+ vars.getLanguage() + "'))";
intOrder++;
strLevelLabel[i] = dimensionLabel[1].name;
- levelsconcat.append(" || ");
- levelsconcat.append("C_BPARTNER.C_BPARTNER_ID");
} else if (StringUtils.equals(strShownArray[i], "3")) {
strTextShow[i] = "M_PRODUCT_CATEGORY.NAME";
intOrder++;
strLevelLabel[i] = dimensionLabel[2].name;
- levelsconcat.append(" || ");
- levelsconcat.append("M_PRODUCT_CATEGORY.M_PRODUCT_CATEGORY_ID");
} else if (StringUtils.equals(strShownArray[i], "4")) {
strTextShow[i] = "AD_COLUMN_IDENTIFIER(to_char('M_Product'), to_char(
M_PRODUCT.M_PRODUCT_ID), to_char( '"
+ vars.getLanguage()
@@ -739,46 +730,32 @@
intOrder++;
intProductLevel = i + 1;
strLevelLabel[i] = dimensionLabel[3].name;
- levelsconcat.append(" || ");
- levelsconcat.append("M_PRODUCT.M_PRODUCT_ID");
} else if (StringUtils.equals(strShownArray[i], "5")) {
strTextShow[i] = "C_INVOICE.DOCUMENTNO";
intOrder++;
strLevelLabel[i] = dimensionLabel[4].name;
- levelsconcat.append(" || ");
- levelsconcat.append("C_INVOICE.C_INVOICE_ID");
} else if (StringUtils.equals(strShownArray[i], "6")) {
strTextShow[i] = "AD_USER.FIRSTNAME||' '||' '||AD_USER.LASTNAME";
intOrder++;
strLevelLabel[i] = dimensionLabel[5].name;
- levelsconcat.append(" || ");
- levelsconcat.append("AD_USER.AD_USER_ID");
} else if (StringUtils.equals(strShownArray[i], "8")) {
strTextShow[i] = "AD_ORG.NAME";
intOrder++;
strLevelLabel[i] = dimensionLabel[6].name;
- levelsconcat.append(" || ");
- levelsconcat.append("AD_ORG.AD_ORG_ID");
} else if (StringUtils.equals(strShownArray[i], "9")) {
strTextShow[i] = "CASE WHEN AD_USER.AD_USER_ID IS NOT NULL THEN
AD_COLUMN_IDENTIFIER(to_char('Ad_User'), to_char( AD_USER.AD_USER_ID), to_char(
'"
+ vars.getLanguage() + "')) ELSE '' END";
intOrder++;
strLevelLabel[i] = dimensionLabel[7].name;
- levelsconcat.append(" || ");
- levelsconcat.append("AD_USER.AD_USER_ID");
} else if (StringUtils.equals(strShownArray[i], "10")) {
strTextShow[i] = "C_PROJECT.NAME";
intOrder++;
strLevelLabel[i] = dimensionLabel[8].name;
- levelsconcat.append(" || ");
- levelsconcat.append("C_PROJECT.C_PROJECT_ID");
} else if (StringUtils.equals(strShownArray[i], "11")) {
strTextShow[i] = "AD_COLUMN_IDENTIFIER(to_char('C_Bpartner_Location'),
to_char( M_INOUT.C_BPARTNER_LOCATION_ID), to_char( '"
+ vars.getLanguage() + "'))";
intOrder++;
strLevelLabel[i] = dimensionLabel[9].name;
- levelsconcat.append(" || ");
- levelsconcat.append("M_INOUT.C_BPARTNER_LOCATION_ID");
} else {
strTextShow[i] = "''";
discard[i] = "display:none;";
@@ -831,102 +808,40 @@
}
strOrderby = strHaving + strOrderby;
- int limit = 0;
- int mycount = 0;
- try {
- limit = Integer.parseInt(Utility.getPreference(vars, "ReportsLimit",
""));
- if (limit > 0) {
- mycount = Integer
- .parseInt((StringUtils.equals(strComparative, "Y")) ?
ReportInvoiceCustomerDimensionalAnalysesJRData
- .selectCount(readOnlyCP, levelsconcat.toString(),
Tree.getMembers(readOnlyCP,
- TreeData.getTreeOrg(readOnlyCP, vars.getClient()),
localStrOrg), Utility
- .getContext(readOnlyCP, vars, "#User_Client",
- "ReportInvoiceCustomerDimensionalAnalysesJR"),
strPartnerGroup,
- strcBpartnerId, productCategoryGroup, strmProductId,
strsalesrepId,
- strPartnerSalesrepId, strcProjectId, strProducttype,
strcDocTypeId, StringUtils
- .equals(strVoid, "Y") ? "" : "VO", strDateFrom,
DateTimeData.nDaysAfter(
- readOnlyCP, strDateTo, "1"), strDateFromRef,
DateTimeData.nDaysAfter(
- readOnlyCP, strDateToRef, "1"), localStrDateFromRef2,
DateTimeData
- .nDaysAfter(readOnlyCP, localStrDateToRef2, "1"),
localStrDateFromRef3,
- DateTimeData.nDaysAfter(readOnlyCP, localStrDateToRef3,
"1"))
- :
ReportInvoiceCustomerDimensionalAnalysesJRData.selectNoComparativeCount(
- readOnlyCP, levelsconcat.toString(),
Tree.getMembers(readOnlyCP,
- TreeData.getTreeOrg(readOnlyCP, vars.getClient()),
localStrOrg), Utility
- .getContext(readOnlyCP, vars, "#User_Client",
- "ReportInvoiceCustomerDimensionalAnalysesJR"),
strPartnerGroup,
- strcBpartnerId, productCategoryGroup, strmProductId,
strsalesrepId,
- strPartnerSalesrepId, strcProjectId, strProducttype,
strcDocTypeId, StringUtils
- .equals(strVoid, "Y") ? "" : "VO", strDateFrom,
DateTimeData.nDaysAfter(
- readOnlyCP, strDateTo, "1")));
- }
- } catch (NumberFormatException e) {
+ int limit = Integer.parseInt(Utility.getPreference(vars, "ReportsLimit",
""));
+ String pgLimit = null, oraLimit = null;
+ if (StringUtils.equalsIgnoreCase(readOnlyCP.getRDBMS(), "ORACLE")) {
+ oraLimit = String.valueOf(limit + 1);
+ } else {
+ pgLimit = String.valueOf(limit + 1);
}
- if (limit > 0 && mycount > limit) {
- String msgbody = Utility.messageBD(readOnlyCP, "ReportsLimitBody",
vars.getLanguage());
- msgbody = msgbody.replace("@rows@", Integer.toString(mycount));
- msgbody = msgbody.replace("@limit@", Integer.toString(limit));
- advisePopUp(request, response, "ERROR",
- Utility.messageBD(readOnlyCP, "ReportsLimitHeader",
vars.getLanguage()), msgbody);
+ // Checks if there is a conversion rate for each of the transactions of
the report
+ String strConvRateErrorMsg = "";
+ OBError myMessage = null;
+ myMessage = new OBError();
+ if (StringUtils.equals(strOutput, "xls")) {
+ try {
+ dataXLS =
ReportInvoiceCustomerDimensionalAnalysesJRData.selectXLS(readOnlyCP,
+ strCurrencyId, vars.getLanguage(), Tree.getMembers(readOnlyCP,
+ TreeData.getTreeOrg(readOnlyCP, vars.getClient()),
localStrOrg), Utility
+ .getContext(readOnlyCP, vars, "#User_Client",
+ "ReportInvoiceCustomerDimensionalAnalysesJR"),
strDateFrom, DateTimeData
+ .nDaysAfter(readOnlyCP, strDateTo, "1"), strPartnerGroup,
strcBpartnerId,
+ productCategoryGroup, strmProductId, strsalesrepId,
strPartnerSalesrepId,
+ strcProjectId, strProducttype, strcDocTypeId,
StringUtils.equals(strVoid, "Y") ? ""
+ : "VO", pgLimit, oraLimit);
+
+ } catch (ServletException ex) {
+ myMessage = Utility.translateError(readOnlyCP, vars,
vars.getLanguage(), ex.getMessage());
+ }
} else {
- // Checks if there is a conversion rate for each of the transactions of
the report
- String strConvRateErrorMsg = "";
- OBError myMessage = null;
- myMessage = new OBError();
- if (StringUtils.equals(strOutput, "xls")) {
+ if (StringUtils.equals(strComparative, "Y")) {
try {
- dataXLS =
ReportInvoiceCustomerDimensionalAnalysesJRData.selectXLS(readOnlyCP,
- strCurrencyId, vars.getLanguage(), Tree.getMembers(readOnlyCP,
- TreeData.getTreeOrg(readOnlyCP, vars.getClient()),
localStrOrg), Utility
- .getContext(readOnlyCP, vars, "#User_Client",
- "ReportInvoiceCustomerDimensionalAnalysesJR"),
strDateFrom, DateTimeData
- .nDaysAfter(readOnlyCP, strDateTo, "1"), strPartnerGroup,
strcBpartnerId,
- productCategoryGroup, strmProductId, strsalesrepId,
strPartnerSalesrepId,
- strcProjectId, strProducttype, strcDocTypeId,
StringUtils.equals(strVoid, "Y") ? ""
- : "VO");
-
- } catch (ServletException ex) {
- myMessage = Utility.translateError(readOnlyCP, vars,
vars.getLanguage(), ex.getMessage());
- }
- } else {
- if (StringUtils.equals(strComparative, "Y")) {
- try {
- if (multiComparative2) {
- if (multiComparative3) {
- // Multi-comparative B: 1 base date, 3 reference dates
- data =
ReportInvoiceCustomerDimensionalAnalysesJRData.select3(readOnlyCP,
- strCurrencyId, strTextShow[0], strTextShow[1],
strTextShow[2], strTextShow[3],
- strTextShow[4], strTextShow[5], strTextShow[6],
strTextShow[7], strTextShow[8],
- strTextShow[9], Tree.getMembers(readOnlyCP,
- TreeData.getTreeOrg(readOnlyCP, vars.getClient()),
localStrOrg), Utility
- .getContext(readOnlyCP, vars, "#User_Client",
- "ReportInvoiceCustomerDimensionalAnalysesJR"),
strDateFrom,
- DateTimeData.nDaysAfter(readOnlyCP, strDateTo, "1"),
strPartnerGroup,
- strcBpartnerId, productCategoryGroup, strmProductId,
strsalesrepId,
- strPartnerSalesrepId, strcProjectId, strProducttype,
strcDocTypeId, StringUtils
- .equals(strVoid, "Y") ? "" : "VO", strDateFromRef,
DateTimeData.nDaysAfter(
- readOnlyCP, strDateToRef, "1"), localStrDateFromRef2,
DateTimeData
- .nDaysAfter(readOnlyCP, localStrDateToRef2, "1"),
localStrDateFromRef3,
- DateTimeData.nDaysAfter(readOnlyCP, localStrDateToRef3,
"1"), strOrderby);
- } else {
- // Multi-comparative A: 1 base date, 2 reference dates
- data =
ReportInvoiceCustomerDimensionalAnalysesJRData.select2(readOnlyCP,
- strCurrencyId, strTextShow[0], strTextShow[1],
strTextShow[2], strTextShow[3],
- strTextShow[4], strTextShow[5], strTextShow[6],
strTextShow[7], strTextShow[8],
- strTextShow[9], Tree.getMembers(readOnlyCP,
- TreeData.getTreeOrg(readOnlyCP, vars.getClient()),
localStrOrg), Utility
- .getContext(readOnlyCP, vars, "#User_Client",
- "ReportInvoiceCustomerDimensionalAnalysesJR"),
strDateFrom,
- DateTimeData.nDaysAfter(readOnlyCP, strDateTo, "1"),
strPartnerGroup,
- strcBpartnerId, productCategoryGroup, strmProductId,
strsalesrepId,
- strPartnerSalesrepId, strcProjectId, strProducttype,
strcDocTypeId, StringUtils
- .equals(strVoid, "Y") ? "" : "VO", strDateFromRef,
DateTimeData.nDaysAfter(
- readOnlyCP, strDateToRef, "1"), localStrDateFromRef2,
DateTimeData
- .nDaysAfter(readOnlyCP, localStrDateToRef2, "1"),
strOrderby);
- }
- } else {
- // Regular comparative: 1 base date, 1 reference date
- data =
ReportInvoiceCustomerDimensionalAnalysesJRData.select(readOnlyCP,
+ if (multiComparative2) {
+ if (multiComparative3) {
+ // Multi-comparative B: 1 base date, 3 reference dates
+ data =
ReportInvoiceCustomerDimensionalAnalysesJRData.select3(readOnlyCP,
strCurrencyId, strTextShow[0], strTextShow[1],
strTextShow[2], strTextShow[3],
strTextShow[4], strTextShow[5], strTextShow[6],
strTextShow[7], strTextShow[8],
strTextShow[9], Tree.getMembers(readOnlyCP,
@@ -937,183 +852,222 @@
productCategoryGroup, strmProductId, strsalesrepId,
strPartnerSalesrepId,
strcProjectId, strProducttype, strcDocTypeId,
StringUtils.equals(strVoid, "Y") ? "" : "VO",
strDateFromRef, DateTimeData
- .nDaysAfter(readOnlyCP, strDateToRef, "1"), strOrderby);
+ .nDaysAfter(readOnlyCP, strDateToRef, "1"),
localStrDateFromRef2,
+ DateTimeData.nDaysAfter(readOnlyCP, localStrDateToRef2, "1"),
+ localStrDateFromRef3, DateTimeData
+ .nDaysAfter(readOnlyCP, localStrDateToRef3, "1"),
strOrderby, pgLimit,
+ oraLimit);
+ } else {
+ // Multi-comparative A: 1 base date, 2 reference dates
+ data =
ReportInvoiceCustomerDimensionalAnalysesJRData.select2(readOnlyCP,
+ strCurrencyId, strTextShow[0], strTextShow[1],
strTextShow[2], strTextShow[3],
+ strTextShow[4], strTextShow[5], strTextShow[6],
strTextShow[7], strTextShow[8],
+ strTextShow[9], Tree.getMembers(readOnlyCP,
+ TreeData.getTreeOrg(readOnlyCP, vars.getClient()),
localStrOrg), Utility
+ .getContext(readOnlyCP, vars, "#User_Client",
+ "ReportInvoiceCustomerDimensionalAnalysesJR"),
strDateFrom, DateTimeData
+ .nDaysAfter(readOnlyCP, strDateTo, "1"),
strPartnerGroup, strcBpartnerId,
+ productCategoryGroup, strmProductId, strsalesrepId,
strPartnerSalesrepId,
+ strcProjectId, strProducttype, strcDocTypeId,
+ StringUtils.equals(strVoid, "Y") ? "" : "VO",
strDateFromRef, DateTimeData
+ .nDaysAfter(readOnlyCP, strDateToRef, "1"),
localStrDateFromRef2,
+ DateTimeData.nDaysAfter(readOnlyCP, localStrDateToRef2,
"1"), strOrderby,
+ pgLimit, oraLimit);
}
- } catch (ServletException ex) {
- myMessage = Utility.translateError(readOnlyCP, vars,
vars.getLanguage(),
- ex.getMessage());
- }
- } else {
- try {
- data =
ReportInvoiceCustomerDimensionalAnalysesJRData.selectNoComparative(readOnlyCP,
- strCurrencyId, strTextShow[0], strTextShow[1], strTextShow[2],
strTextShow[3],
- strTextShow[4], strTextShow[5], strTextShow[6],
strTextShow[7], strTextShow[8],
- strTextShow[9], Tree.getMembers(readOnlyCP,
- TreeData.getTreeOrg(readOnlyCP, vars.getClient()),
localStrOrg), Utility
- .getContext(readOnlyCP, vars, "#User_Client",
- "ReportInvoiceCustomerDimensionalAnalysesJR"),
strDateFrom, DateTimeData
+ } else {
+ // Regular comparative: 1 base date, 1 reference date
+ data =
ReportInvoiceCustomerDimensionalAnalysesJRData.select(readOnlyCP, strCurrencyId,
+ strTextShow[0], strTextShow[1], strTextShow[2],
strTextShow[3], strTextShow[4],
+ strTextShow[5], strTextShow[6], strTextShow[7],
strTextShow[8], strTextShow[9],
+ Tree.getMembers(readOnlyCP, TreeData.getTreeOrg(readOnlyCP,
vars.getClient()),
+ localStrOrg), Utility.getContext(readOnlyCP, vars,
"#User_Client",
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
