0013361: The General Ledger Report does not work well using the group by option with a big amount of data

diff -r 17a70403b6d3 src-db/database/sourcedata/AD_MESSAGE.xml --- a/src-db/database/sourcedata/AD_MESSAGE.xml Wed Mar 31 12:07:41 2010 +0200 +++ b/src-db/database/sourcedata/AD_MESSAGE.xml Wed Jul 28 10:21:00 2010 +0200 @@ -31827,6 +31827,17 @@  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_MESSAGE> +<AD_MESSAGE> + <AD_MESSAGE_ID><![CDATA[AC594E632F5C4970A881AAEC763BC705]]></AD_MESSAGE_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <VALUE><![CDATA[MAX_ROWS_LIMIT_EXCEED]]></VALUE> + <MSGTEXT><![CDATA[Maximum number of rows exceeded. Please modify filtering criteria.]]></MSGTEXT> + <MSGTYPE><![CDATA[W]]></MSGTYPE> + <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +</AD_MESSAGE> + <AD_MESSAGE>  <AD_MESSAGE_ID><![CDATA[AC63454AE63A49CDA0EB25806B656B6D]]></AD_MESSAGE_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java --- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java Wed Jul 28 10:21:00 2010 +0200 @@ -46,6 +46,7 @@ public class ReportGeneralLedger extends HttpSecureAppServlet { private static final long serialVersionUID = 1L; + private static final int MAX_XLS_ROWS = 64500; public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { @@ -292,13 +293,13 @@ strcelementvaluetodes = ""; vars.setSessionValue("inpElementValueIdTo_DES", strcelementvaluetodes); } - data = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, toDatePlusOne, - strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, - null, null, pgLimit, oraLimit1, oraLimit2); + data = ReportGeneralLedgerData.select(this, rowNum, strGroupBy, strGroupByText, strDateFrom, + toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, + strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, null, null, pgLimit, oraLimit1, + oraLimit2); if (log4j.isDebugEnabled()) log4j.debug("RecordNo: " + initRecordNumber); // In case this is not the first screen to show, initial balance may need to include amounts @@ -306,15 +307,14 @@ ReportGeneralLedgerData[] dataTotal = null; if (data != null && data.length > 1) { - dataTotal = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strYearInitialDate, DateTimeData - .nDaysAfter(this, data[0].dateacct, "1"), strOrgFamily, strcBpartnerId, - strmProductId, strcProjectId, strAmtFrom, strAmtTo, data[0].id, data[0].dateacctnumber - + data[0].factAcctGroupId + data[0].description + data[0].isdebit, - data[0].groupbyid, null, null, null); + dataTotal = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, + strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strYearInitialDate, DateTimeData.nDaysAfter(this, data[0].dateacct, + "1"), strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, + strAmtTo, data[0].id, data[0].dateacctnumber + data[0].factAcctGroupId + + data[0].description + data[0].isdebit, data[0].groupbyid, null, null, null); } // Now dataTotal is covered adding debit and credit amounts for (int i = 0; dataTotal != null && i < dataTotal.length; i++) { @@ -495,80 +495,84 @@ vars.getLanguage()) : (strGroupBy.equals("Project") ? Utility.messageBD(this, "Project", vars.getLanguage()) : ""))); String strAllaccounts = "Y"; + try { + if (!strDateFrom.equals("") && !strDateTo.equals("")) { + strOrgFamily = getFamily(strTreeOrg, strOrg); + if (!strHide.equals("Y")) + strHide = "N"; + if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { + if (strcelementvalueto.equals("")) + strcelementvalueto = strcelementvaluefrom; + strAllaccounts = "N"; + } + data = ReportGeneralLedgerData.select(this, "ROWNUM", strGroupByText, strGroupBy, + strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, + strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, null, null, null, null, null); + } + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars + .getLanguage())); + } else { + String strOld = ""; + BigDecimal totalDebit = BigDecimal.ZERO; + BigDecimal totalCredit = BigDecimal.ZERO; + BigDecimal subTotal = BigDecimal.ZERO; - if (!strDateFrom.equals("") && !strDateTo.equals("")) { - strOrgFamily = getFamily(strTreeOrg, strOrg); - if (!strHide.equals("Y")) - strHide = "N"; - if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { - if (strcelementvalueto.equals("")) - strcelementvalueto = strcelementvaluefrom; - strAllaccounts = "N"; + subreport = new ReportGeneralLedgerData[data.length]; + for (int i = 0; data != null && i < data.length; i++) { + if (!strOld.equals(data[i].groupbyid + data[i].id)) { + subreport = ReportGeneralLedgerData.selectTotal(this, strDateFrom, DateTimeData + .nDaysAfter(this, strDateTo, "1"), (strGroupBy.equals("BPartner") ? "('" + + data[i].groupbyid + "')" : strcBpartnerId), (strGroupBy.equals("Product") ? "('" + + data[i].groupbyid + "')" : strmProductId), (strGroupBy.equals("Project") ? "('" + + data[i].groupbyid + "')" : strcProjectId), strcAcctSchemaId, data[i].id, + strYearInitialDate, strDateFrom, strOrgFamily, strHide); + totalDebit = BigDecimal.ZERO; + totalCredit = BigDecimal.ZERO; + subTotal = BigDecimal.ZERO; + } + totalDebit = totalDebit.add(new BigDecimal(data[i].amtacctdr)); + data[i].totalacctdr = new BigDecimal(subreport[0].totalacctdr).add(totalDebit).toString(); + totalCredit = totalCredit.add(new BigDecimal(data[i].amtacctcr)); + data[i].totalacctcr = new BigDecimal(subreport[0].totalacctcr).add(totalCredit) + .toString(); + subTotal = subTotal.add(new BigDecimal(data[i].total)); + data[i].totalacctsub = new BigDecimal(subreport[0].total).add(subTotal).toString(); + data[i].previousdebit = subreport[0].totalacctdr; + data[i].previouscredit = subreport[0].totalacctcr; + data[i].previoustotal = subreport[0].total; + strOld = data[i].groupbyid + data[i].id; + } + + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.jrxml"; + response.setHeader("Content-disposition", "inline; filename=ReportGeneralLedgerPDF.pdf"); + + HashMap<String, Object> parameters = new HashMap<String, Object>(); + + String strLanguage = vars.getLanguage(); + + parameters.put("ShowGrouping", new Boolean(!strGroupBy.equals(""))); + parameters.put("Title", classInfo.name); + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + parameters.put("Previous", Utility.messageBD(this, "Initial Balance", strLanguage)); + parameters.put("Total", Utility.messageBD(this, "Total", strLanguage)); + String strDateFormat; + strDateFormat = vars.getJavaDateFormat(); + parameters.put("strDateFormat", strDateFormat); + renderJR(vars, response, strReportName, "pdf", parameters, data, null); } - data = ReportGeneralLedgerData.select(this, "0", strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, toDatePlusOne, - strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, - null, null, null, null, null); - } - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { - String strOld = ""; - BigDecimal totalDebit = BigDecimal.ZERO; - BigDecimal totalCredit = BigDecimal.ZERO; - BigDecimal subTotal = BigDecimal.ZERO; - - subreport = new ReportGeneralLedgerData[data.length]; - for (int i = 0; data != null && i < data.length; i++) { - if (!strOld.equals(data[i].groupbyid + data[i].id)) { - subreport = ReportGeneralLedgerData.selectTotal(this, strDateFrom, DateTimeData - .nDaysAfter(this, strDateTo, "1"), (strGroupBy.equals("BPartner") ? "('" - + data[i].groupbyid + "')" : strcBpartnerId), (strGroupBy.equals("Product") ? "('" - + data[i].groupbyid + "')" : strmProductId), (strGroupBy.equals("Project") ? "('" - + data[i].groupbyid + "')" : strcProjectId), strcAcctSchemaId, data[i].id, - strYearInitialDate, strDateFrom, strOrgFamily, strHide); - totalDebit = BigDecimal.ZERO; - totalCredit = BigDecimal.ZERO; - subTotal = BigDecimal.ZERO; - } - totalDebit = totalDebit.add(new BigDecimal(data[i].amtacctdr)); - data[i].totalacctdr = new BigDecimal(subreport[0].totalacctdr).add(totalDebit).toString(); - totalCredit = totalCredit.add(new BigDecimal(data[i].amtacctcr)); - data[i].totalacctcr = new BigDecimal(subreport[0].totalacctcr).add(totalCredit).toString(); - subTotal = subTotal.add(new BigDecimal(data[i].total)); - data[i].totalacctsub = new BigDecimal(subreport[0].total).add(subTotal).toString(); - data[i].previousdebit = subreport[0].totalacctdr; - data[i].previouscredit = subreport[0].totalacctcr; - data[i].previoustotal = subreport[0].total; - strOld = data[i].groupbyid + data[i].id; - } - - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.jrxml"; - response.setHeader("Content-disposition", "inline; filename=ReportGeneralLedgerPDF.pdf"); - - HashMap<String, Object> parameters = new HashMap<String, Object>(); - - String strLanguage = vars.getLanguage(); - - parameters.put("ShowGrouping", new Boolean(!strGroupBy.equals(""))); - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - parameters.put("Previous", Utility.messageBD(this, "Initial Balance", strLanguage)); - parameters.put("Total", Utility.messageBD(this, "Total", strLanguage)); - String strDateFormat; - strDateFormat = vars.getJavaDateFormat(); - parameters.put("strDateFormat", strDateFormat); - - renderJR(vars, response, strReportName, "pdf", parameters, data, null); + } catch (Throwable e) { + log4j.error("Error creating the PDF report in General Ledger"); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } } @@ -587,50 +591,68 @@ String strYearInitialDate = ReportGeneralLedgerData.yearInitialDate(this, vars .getSessionValue("#AD_SqlDateFormat"), strDateFrom, Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strOrgFamily); - if (strYearInitialDate.equals("")) - strYearInitialDate = strDateFrom; - String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1"); + String oraLimit1 = ""; + String pgLimit = ""; + if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) { + oraLimit1 = oraLimit1 + (MAX_XLS_ROWS + 1); + } else { + pgLimit = "0 OFFSET " + (MAX_XLS_ROWS + 1); + } - String strAllaccounts = "Y"; + try { + if (strYearInitialDate.equals("")) + strYearInitialDate = strDateFrom; + String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1"); - if (!strDateFrom.equals("") && !strDateTo.equals("")) { - if (!strHide.equals("Y")) - strHide = "N"; - if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { - if (strcelementvalueto.equals("")) - strcelementvalueto = strcelementvaluefrom; - strAllaccounts = "N"; + String strAllaccounts = "Y"; + + if (!strDateFrom.equals("") && !strDateTo.equals("")) { + if (!strHide.equals("Y")) + strHide = "N"; + if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { + if (strcelementvalueto.equals("")) + strcelementvalueto = strcelementvaluefrom; + strAllaccounts = "N"; + } + data = ReportGeneralLedgerData.selectXLS(this, strDateFrom, toDatePlusOne, strAllaccounts, + strcelementvaluefrom, strcelementvalueto, Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportGeneralLedger"), Utility.getContext(this, vars, + "#User_Client", "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, + toDatePlusOne, strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, + strAmtTo, oraLimit1, pgLimit); } - data = ReportGeneralLedgerData.selectXLS(this, vars.getLanguage(), strDateFrom, - toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, Utility - .getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility - .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, - strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, - strmProductId, strcProjectId, strAmtFrom, strAmtTo); - } - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars + .getLanguage())); + } else if (data.length > MAX_XLS_ROWS) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); + } else { - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml"; + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml"; - HashMap<String, Object> parameters = new HashMap<String, Object>(); + HashMap<String, Object> parameters = new HashMap<String, Object>(); - String strLanguage = vars.getLanguage(); + String strLanguage = vars.getLanguage(); - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - String strDateFormat; - strDateFormat = vars.getJavaDateFormat(); - parameters.put("strDateFormat", strDateFormat); + parameters.put("Title", classInfo.name); + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + String strDateFormat; + strDateFormat = vars.getJavaDateFormat(); + parameters.put("strDateFormat", strDateFormat); - renderJR(vars, response, strReportName, "xls", parameters, data, null); + renderJR(vars, response, strReportName, "xls", parameters, data, null); + } + } catch (Throwable e) { + log4j.error("Error creating the XLS report in General Ledger", e); + + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } } diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql Wed Jul 28 10:21:00 2010 +0200 @@ -26,10 +26,18 @@ <![CDATA[ SELECT * FROM ( SELECT * - FROM ( SELECT '0' AS RN1, C.* FROM ( + FROM ( SELECT '0' AS RN1, C.NAME, C.VALUE, C.DATEACCT, C.AMTACCTDR, C.AMTACCTCR, C.TOTAL, C.FACT_ACCT_GROUP_ID, C.ID, C.GROUPBYID, + C.DESCRIPTION, C.ISDEBIT, C.TOTALACCTDR, C.TOTALACCTCR, C.TOTALACCTSUB, C.PREVIOUSDEBIT, C.PREVIOUSCREDIT, C.PREVIOUSTOTAL, + C.FINALDEBIT, C.FINALCREDIT, C.FINALTOTAL, C.DATEACCTNUMBER, C.groupby, C.bpid, C.bpname, C.pdid, C.pdname, C.pjid, C.pjname, + CASE ? + WHEN 'BPartner' THEN TO_CHAR(C_Bpartner.NAME) + WHEN 'Product' THEN TO_CHAR(M_Product.name) + WHEN 'Project' THEN TO_CHAR(C_Project.NAME) + ELSE '' END AS groupbyname + FROM ( SELECT VALUE, NAME, DATEACCT, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, (SUM(AMTACCTDR)-SUM(AMTACCTCR)) AS TOTAL, - FACT_ACCT_GROUP_ID, ID, groupbyid, groupbyname, + FACT_ACCT_GROUP_ID, ID, groupbyid, DESCRIPTION, ISDEBIT, 0 AS TOTALACCTDR, 0 AS TOTALACCTCR, 0 AS TOTALACCTSUB, 0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL, @@ -41,20 +49,12 @@ DATEACCT, AMTACCTDR, AMTACCTCR, FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id + WHEN 'BPartner' THEN FACT_ACCT.c_bpartner_id + WHEN 'Product' THEN FACT_ACCT.m_product_id + WHEN 'Project' THEN FACT_ACCT.c_project_id ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN (case AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) end) - WHEN 'Product' THEN (case AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) end) - WHEN 'Project' THEN (case AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) end) - ELSE '' END AS groupbyname, CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT FROM FACT_ACCT - LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID LEFT JOIN( select account_id, record_id2, sum(amtacctdr-amtacctcr) as sum from fact_acct f1 @@ -76,24 +76,21 @@ AND 2=2 AND 3=3) D WHERE 6=6 - GROUP BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + GROUP BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 - ORDER BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + ORDER BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT ) C + LEFT JOIN C_BPARTNER ON C.groupbyid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON C.groupbyid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON C.groupbyid = C_PROJECT.C_PROJECT_ID ) B WHERE 1=1 ) A ORDER BY groupbyname, groupbyid, VALUE, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" /> + <Parameter name="groupby"/> <Parameter name="groupbytext"/> <Parameter name="groupby"/> - <Parameter name="groupby"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="parDateFrom" optional="true" after="where 5=5"><![CDATA[AND DATEACCT >= TO_DATE(?) ]]></Parameter> <Parameter name="parDateTo" optional="true" after="where 5=5"><![CDATA[AND DATEACCT < TO_DATE(?) ]]></Parameter> <Parameter name="allaccounts"/> @@ -115,7 +112,7 @@ <Parameter name="factAcctId" optional="true" after="WHERE 6=6"><![CDATA[and to_char(DATEACCT,'J')||FACT_ACCT_GROUP_ID||DESCRIPTION||ISDEBIT < ? ]]></Parameter> <Parameter name="cBPartner" optional="true" after="WHERE 6=6"><![CDATA[AND groupbyid = ? ]]></Parameter> <Parameter name="pgLimit" type="argument" optional="true" after=", FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter> - <Parameter name="oraLimit1" type="argument" optional="true" after=") C"><![CDATA[ WHERE ROWNUM <= ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="C_PROJECT.C_PROJECT_ID"><![CDATA[ WHERE ROWNUM <= ]]></Parameter> <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter> </SqlMethod> @@ -123,21 +120,21 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT VALUE, NAME, DATEACCT, + SELECT VALUE, NAME, DATEACCT, AMTACCTDR, AMTACCTCR, + FACT_ACCT_GROUP_ID, ID, bpid, bpname, pdid, pdname, pjid, pjname, + DESCRIPTION + FROM( + SELECT D.VALUE, D.NAME, DATEACCT, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - FACT_ACCT_GROUP_ID, ID, bpid, bpname, pdid, pdname, pjid, pjname, - DESCRIPTION + FACT_ACCT_GROUP_ID, ID, bpid, C_BPARTNER.NAME AS bpname, pdid, M_PRODUCT.NAME AS pdname, pjid, C_PROJECT.NAME AS pjname, + D.DESCRIPTION, ISDEBIT FROM (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME, DATEACCT, AMTACCTDR, AMTACCTCR, FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - (case AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) end) as bpname, (case AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) end) as pdname, (case AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) end) as pjname, + FACT_ACCT.c_bpartner_id as bpid, FACT_ACCT.m_product_id as pdid, FACT_ACCT.c_project_id as pjid, CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT FROM FACT_ACCT - LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID LEFT JOIN( select account_id, record_id2, sum(amtacctdr-amtacctcr) as sum from fact_acct f1 @@ -158,18 +155,16 @@ AND FACT_ACCT.AD_ORG_ID IN ('2') AND 2=2 AND 3=3) D + LEFT JOIN C_BPARTNER ON D.bpid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON D.pdid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON D.pjid = C_PROJECT.C_PROJECT_ID WHERE 6=6 - GROUP BY VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT - HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 + GROUP BY D.VALUE, D.NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, D.description, C_BPARTNER.NAME, bpid, M_PRODUCT.NAME, pdid, C_PROJECT.NAME, pjid, ISDEBIT + HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0) + WHERE 7=7 ORDER BY VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="parDateFrom" optional="true" after="where 5=5"><![CDATA[AND DATEACCT >= TO_DATE(?) ]]></Parameter> <Parameter name="parDateTo" optional="true" after="where 5=5"><![CDATA[AND DATEACCT < TO_DATE(?) ]]></Parameter> <Parameter name="allaccounts"/> @@ -187,6 +182,8 @@ <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter> <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)>=TO_NUMBER(?) ]]></Parameter> <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="WHERE 7=7"><![CDATA[ AND ROWNUM <= ]]></Parameter> + <Parameter name="pgLimit" type="argument" optional="true" after=", pdname, pdid, pjname, pjid, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter> </SqlMethod> <SqlMethod name="set" type="constant" return="multiple"> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html Wed Jul 28 10:21:00 2010 +0200 @@ -24,7 +24,7 @@ <title>Balance Sheet</title> <link rel="shortcut icon" href="../../../../../web/images/favicon.ico" type="image/x-icon" /> <link rel="stylesheet" type="text/css" href="../../../../../web/skins/Default/Openbravo_ERP_250.css" id="paramCSS" /> - + <script language="JavaScript" type="text/javascript" id="paramDirectory"> var baseDirectory = "../../../../../web/"; </script> @@ -54,8 +54,8 @@ function imprimir() { - openPDFFiltered("ReportTrialBalance.html?Command=PDF","PDF"); - return true; + openPDFFiltered("ReportTrialBalance.html?Command=PDF","PDF"); + return true; } </script> <script language="JavaScript" type="text/javascript"> @@ -118,14 +118,21 @@ if (getReadyStateHandler(XMLHttpRequestObj)) { try { if (XMLHttpRequestObj.responseText) strText = XMLHttpRequestObj.responseText; - } catch (e) { + } catch (e) { } - if (paramXMLParticular!=null && paramXMLParticular.length>0) { - id = paramXMLParticular[0]; - imageId = paramXMLParticular[1]; - } - addRows(strText,id); - showHideLayer(id, imageId); + if (paramXMLParticular!=null && paramXMLParticular.length>0) { + id = paramXMLParticular[0]; + imageId = paramXMLParticular[1]; + } + addRows(strText,id, true, "0"); + var obj = getReference(imageId); + if (obj!=null) { + if (obj.className == "datawarehouseopen") { + obj.className = "datawarehouseclose"; + } else { + obj.className = "datawarehouseopen"; + } + } } return true; } @@ -134,49 +141,78 @@ var obj = getReference(imageId); if (obj!=null) { if (obj.className == "datawarehouseopen") { - obj.className = "datawarehouseclose"; - showHideRows(id, "none"); - } else { - obj.className = "datawarehouseopen"; - showHideRows(id, ""); - } + obj.className = "datawarehouseclose"; + showHideRows(id, "none"); + } else { + obj.className = "datawarehouseopen"; + showHideRows(id, ""); + } } } -function addRows(htmlText, id) { +function addRows(htmlText, id, byDefault, startRow) { var jsonTable = eval(htmlText); if (typeof jsonTable != undefined) { - for (i=0; i<jsonTable.rows.length; i++) { - var node=document.getElementById('funcEvenOddRow1'+id); - var elem=document.createElement('TR'); - node.parentNode.insertBefore(elem,node.nextSibling); - elem.id = "funcEvenOddRow2"+ id +"_"+(i+1); - var newTD1 = document.createElement('TD'); - newTD1.innerHTML = jsonTable.rows[i].td1 - newTD1.className = jsonTable.config.classDefault; - newTD1.colSpan = "2"; - var newTD2 = document.createElement('TD'); - newTD2.innerHTML = jsonTable.rows[i].td2; - newTD2.className = jsonTable.config.classAmount; - var newTD3 = document.createElement('TD'); - newTD3.innerHTML = jsonTable.rows[i].td3; - newTD3.className = jsonTable.config.classAmount; - var newTD4 = document.createElement('TD'); - newTD4.innerHTML = jsonTable.rows[i].td4; - newTD4.className = jsonTable.config.classAmount; - var newTD5 = document.createElement('TD'); - newTD5.innerHTML = jsonTable.rows[i].td5; - newTD5.className = jsonTable.config.classAmount; - var newTD6 = document.createElement('TD'); - newTD6.innerHTML = ""; - newTD6.className = jsonTable.config.classDefault; - elem.appendChild(newTD1); - elem.appendChild(newTD2); - elem.appendChild(newTD3); - elem.appendChild(newTD4); - elem.appendChild(newTD5); - elem.appendChild(newTD6); - } + var node; + var range = Number(jsonTable.config.range); + var totalRecords = Number(jsonTable.config.totalRecords); + var intStartRow = Number(startRow); + if (byDefault) { + node = document.getElementById('funcEvenOddRow1'+id); + } else { + node = document.getElementById('more__'+id+"__"+startRow); + node.style.display = "none"; + } + if (jsonTable.rows != undefined) { + if (jsonTable.rows.length > 0 && totalRecords>=range && range!=0) { + var next = Number(startRow) + Number(totalRecords); + var elem=document.createElement('TR'); + node.parentNode.insertBefore(elem,node.nextSibling); + elem.id="more__"+id+"__"+next; + var newTD7 = document.createElement('TD'); + newTD7.className = jsonTable.config.classDefault; + newTD7.colSpan = "7"; + var a = document.createElement('a'); + a.innerHTML = "..."; + a.href="#"; + a.id=id+"__"+next; + a.onclick = function(){moreLines(this.id);return false;}; + newTD7.appendChild(a); + elem.appendChild(newTD7); + } + var cont = intStartRow; + for (i=0; i<jsonTable.rows.length; i++) { + var elem=document.createElement('TR'); + node.parentNode.insertBefore(elem,node.nextSibling); + elem.id = "funcEvenOddRow2"+ id +"_"+(cont+1); + var newTD1 = document.createElement('TD'); + newTD1.innerHTML = jsonTable.rows[i].td1 + newTD1.className = jsonTable.config.classDefault; + newTD1.colSpan = "2"; + var newTD2 = document.createElement('TD'); + newTD2.innerHTML = jsonTable.rows[i].td2; + newTD2.className = jsonTable.config.classAmount; + var newTD3 = document.createElement('TD'); + newTD3.innerHTML = jsonTable.rows[i].td3; + newTD3.className = jsonTable.config.classAmount; + var newTD4 = document.createElement('TD'); + newTD4.innerHTML = jsonTable.rows[i].td4; + newTD4.className = jsonTable.config.classAmount; + var newTD5 = document.createElement('TD'); + newTD5.innerHTML = jsonTable.rows[i].td5; + newTD5.className = jsonTable.config.classAmount; + var newTD6 = document.createElement('TD'); + newTD6.innerHTML = ""; + newTD6.className = jsonTable.config.classDefault; + elem.appendChild(newTD1); + elem.appendChild(newTD2); + elem.appendChild(newTD3); + elem.appendChild(newTD4); + elem.appendChild(newTD5); + elem.appendChild(newTD6); + cont = cont + 1; + } + } } } @@ -186,14 +222,19 @@ var txt = ''; while (fila != undefined) { i++; - fila.style.display = showHide; - fila = document.getElementById('funcEvenOddRow2'+id+'_'+i); + fila.style.display = showHide; + fila = document.getElementById('funcEvenOddRow2'+id+'_'+i); + var moreFila = document.getElementById("more__"+id+"__"+i); + if (moreFila != null) { + moreFila.style.display = showHide; + } } } function updateData(CommandValue, identifier) { var frm = document.frmMain; frm.inpcAccountId.value = identifier; + frm.initRecord.value = "0"; var obj = getReference("buttonTree" + identifier); if (obj!=null && obj.className == "datawarehouseclose") { var paramXMLReq = new Array(identifier, 'buttonTree' + identifier); @@ -203,6 +244,36 @@ } } +function moreLines(identifier) { + var frm = document.frmMain; + var x = identifier.split("__"); + frm.inpcAccountId.value = x[0]; + frm.initRecord.value = x[1]; + try { + var paramXMLReq = new Array(x[0], x[1]); + return submitXmlHttpRequest(callbackMore, frm, "OPEN", "ReportTrialBalance.html", false, null, paramXMLReq); + } catch (e) { + alert(e); + } +} + +function callbackMore(paramXMLParticular, XMLHttpRequestObj) { + var strText = ""; + var id = ""; + var numRows = ""; + if (getReadyStateHandler(XMLHttpRequestObj)) { + try { + if (XMLHttpRequestObj.responseText) strText = XMLHttpRequestObj.responseText; + } catch (e) {} + if (paramXMLParticular!=null && paramXMLParticular.length>0) { + id = paramXMLParticular[0]; + numRows = paramXMLParticular[1]; + } + addRows(strText, id, false, numRows); + } + return true; +} + function showHideFilters(identifier) { var obj = getReference(identifier); if (obj!=null) { @@ -222,43 +293,52 @@ </script> <script language="JavaScript" type="text/javascript"> - function onLoadDo(){ - this.windowTables = new Array( - new windowTableId('client', 'buttonHTML') - ); - setWindowTableParentElement(); - this.tabsTables = new Array( - new tabTableId('tdtopTabs') - ); - setTabTableParentElement(); - enableShortcuts('edition'); - setBrowserAutoComplete(false); + function onLoadDo(){ + this.windowTables = new Array( + new windowTableId('client', 'buttonHTML') + ); + setWindowTableParentElement(); + this.tabsTables = new Array( + new tabTableId('tdtopTabs') + ); + setTabTableParentElement(); + enableShortcuts('edition'); + setBrowserAutoComplete(false); - var selectedGroupBy = document.getElementById("paramSelectedGroupBy").value; - var groupby = document.getElementById("inpGroupBy"); - for (i=0;i<groupby.length;i++){ - if (groupby[i].value == selectedGroupBy) - groupby[i].selected = true; - } + var selectedGroupBy = document.getElementById("paramSelectedGroupBy").value; + var groupby = document.getElementById("inpGroupBy"); + for (i=0;i<groupby.length;i++){ + if (groupby[i].value == selectedGroupBy) + groupby[i].selected = true; + } - try { - onloadFunctions(); - } catch (e) {} - resizeArea(); - updateMenuIcon('buttonMenu'); + try { + onloadFunctions(); + } catch (e) {} + resizeArea(); + updateMenuIcon('buttonMenu'); - setWindowElementFocus('firstElement'); - } + setWindowElementFocus('firstElement'); + } - function onResizeDo(){ - resizeArea(); - } + function onResizeDo(){ + resizeArea(); + } + function submitCommandWithMarkChecked(elementValue){ + var frm = document.frmMain; + markCheckedAllElements(frm.inpcBPartnerId_IN); + markCheckedAllElements(frm.inpcProjectId_IN); + markCheckedAllElements(frm.inpmProductId_IN); + submitCommandFormParameter('FIND', frm.inpcElementValueIdFrom, elementValue,false, frm, 'ReportGeneralLedger.html', '_self', false, true); + } + </script> </head> <body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" onload="onLoadDo();" onresize="onResizeDo();"> <form method="post" action="ReportTrialBalance.html" name="frmMain" target="_self"> <input type="hidden" name="Command"></input> <input type="hidden" name="inpcAccountId"></input> + <input type="hidden" name="initRecord" value="0"></input> <table height="100%" border="0" cellpadding="0" cellspacing="0" id="main"> <tr> <td valign="top" id="tdleftTabs"></td> @@ -427,7 +507,7 @@ </tr> </table> </td> - <td class="TitleCell"></td> + <td class="TitleCell"></td> <td class="TitleCell"><span class="LabelText">To Date</span></td> <td class="TextBox_btn_ContentCell"> <table border="0" cellspacing="0" cellpadding="0" summary="" style="padding-top: 0px;"> @@ -453,21 +533,20 @@ <td class="TitleCell"/> </tr> <tr> + <td class="TitleCell"><span class="LabelText">Organization</span></td> + <td class="Combo_ContentCell"> + <select name="inpOrg" id="inpOrg" class="ComboKey Combo_TwoCells_width" required="true"> + <option value=""> <div id="reportAD_ORGID"></div></option> + </select></td> + </tr> + <tr> <td class="TitleCell"> <span class="LabelText">Accounting schema</span></td> <td class="Combo_ContentCell" colspan="2"> <select name="inpcAcctSchemaId" id="inpcAcctSchemaId" class="ComboKey Combo_TwoCells_width" required="true"> <option value=""> <div id="reportC_ACCTSCHEMA_ID"></div></option> </select> </td> - </tr> - <tr> - <td class="TitleCell"><span class="LabelText">Organization</span></td> - <td class="Combo_ContentCell"> - <select name="inpOrg" id="inpOrg" class="ComboKey Combo_OneCell_width" required="true"> - <option value=""> <div id="reportAD_ORGID"></div></option> - </select></td> - <td class="TitleCell"></td> - <td class="TitleCell"><span class="LabelText">Account Level</span></td> - <td class="Combo_ContentCell"> + <td class="TitleCell"><span class="LabelText">Account Level</span></td> + <td class="Combo_ContentCell"> <select name="inpLevel" id="inpLevel" class="ComboKey Combo_OneCell_width" required="true" onchange="displayLogic(); return true;"> <option value=""> <div id="reportLevel"></div></option> </select></td> @@ -519,7 +598,7 @@ </table> </td> </tr> - + <tr id="Filter1"> <td colspan="6"> <table class="FieldGroup" cellspacing="0" cellpadding="0" border="0"> @@ -541,7 +620,7 @@ </table> </td> </tr> - + <tr id="Filter2"> <td class="TitleCell"><span class="LabelText">Business Partner</span></td> <td class="List_ContentCell" colspan="2"> @@ -630,7 +709,7 @@ </tr> </table> </td> - +  <td class="TitleCell"><span class="LabelText">Product</span></td> <td class="List_ContentCell" colspan="2"> @@ -721,7 +800,7 @@ </table> </td> </tr> - +  <tr id="Filter3"> <td class="TitleCell"><span class="LabelText">Project</span></td> @@ -813,12 +892,12 @@ </tr> </table> - </td> + </td> <td class="ContentCell"></td> <td class="ContentCell"></td> <td class="ContentCell"></td> </tr> - +  <tr id="Filter4"> <td class="TitleCell"><span class="LabelText">Group By</span></td> @@ -835,7 +914,7 @@ <td class="ContentCell"></td> <td class="ContentCell"></td> </tr> - + <tr> <td colspan="6"> <table class="FieldGroup" cellspacing="0" cellpadding="0" border="0"> @@ -857,18 +936,18 @@ <td class="TitleCell"></td> <td class="Button_CenterAlign_ContentCell"> <div> - <button type="button" - id="buttonHTML" - class="ButtonLink" - onclick="submitCommandForm('FIND', true, frmMain, 'ReportTrialBalance.html', '_self');return false;" - onfocus="buttonEvent('onfocus', this); window.status='View Results in a New Window'; return true;" - onblur="buttonEvent('onblur', this);" - onkeyup="buttonEvent('onkeyup', this);" - onkeydown="buttonEvent('onkeydown', this);" - onkeypress="buttonEvent('onkeypress', this);" - onmouseup="buttonEvent('onmouseup', this);" - onmousedown="buttonEvent('onmousedown', this);" - onmouseover="buttonEvent('onmouseover', this); window.status='View Results in a New Window'; return true;" + <button type="button" + id="buttonHTML" + class="ButtonLink" + onclick="submitCommandForm('FIND', true, frmMain, 'ReportTrialBalance.html', '_self');return false;" + onfocus="buttonEvent('onfocus', this); window.status='View Results in a New Window'; return true;" + onblur="buttonEvent('onblur', this);" + onkeyup="buttonEvent('onkeyup', this);" + onkeydown="buttonEvent('onkeydown', this);" + onkeypress="buttonEvent('onkeypress', this);" + onmouseup="buttonEvent('onmouseup', this);" + onmousedown="buttonEvent('onmousedown', this);" + onmouseover="buttonEvent('onmouseover', this); window.status='View Results in a New Window'; return true;" onmouseout="buttonEvent('onmouseout', this);"> <table class="Button"> <tr> @@ -889,7 +968,7 @@ <tr> </table> - +  <table class="Main_Client_TableEdition"> <tr> @@ -898,8 +977,8 @@  <div id="sectionGridView"> <table cellspacing="0" cellpadding="0" width="100%" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;"> - <tr class="DataGrid_Body_Row"> - <th height="17" colspan="7" class="DataGrid_Header_Cell">Balance</th> + <tr class="DataGrid_Body_Row"> + <th height="17" colspan="7" class="DataGrid_Header_Cell">Balance</th> </tr> <tr class="DataGrid_Body_Row"> <th width="90" class="DataGrid_Header_Cell">ACCOUNT CODE</th> @@ -914,10 +993,10 @@ <div id="sectionDetail"> <tr class="DataGrid_Body_Row DataGrid_Body_Row_yy" id="funcEvenOddRow1xx"> <td width="90" class="DataGrid_Body_Cell"> - <span id="showExpand" style="display: table-cell; display: -moz-inline-box;"><a href="#" onclick="updateData('OPEN', 'hhqq');return false;" onMouseOver="window.status='Open';return true;" onMouseOut="window.status='';return true;" id="expandButton"> + <span id="showExpand" style="display: table-cell; display: -moz-inline-box;"><a href="#" onclick="updateData('OPEN', 'hhqq', '0');return false;" onMouseOver="window.status='Open';return true;" onMouseOut="window.status='';return true;" id="expandButton"> <span id="buttonTreemmm" class="datawarehouseclose"></span> </a></span> - <a href="#" onclick="submitCommandFormParameter('FIND', document.frmMain.inpcElementValueIdFrom, 'xx', false, document.frmMain, 'ReportGeneralLedger.html', '_self', false, true);return false;" onmouseover="window.status='General Ledger';return true;" onmouseout="window.status='';return true;" class="LabelLink" id="fieldId1"> + <a href="#" onclick="submitCommandWithMarkChecked('xx');return false;" onmouseover="window.status='General Ledger';return true;" onmouseout="window.status='';return true;" class="LabelLink" id="fieldId1"> <span id="fieldAccount">xx70000</span> </a> <span id="fieldDescAccount">xx600</span> @@ -932,7 +1011,7 @@ </td> </tr> </div> - <tr class="DataGrid_Body_Row"> + <tr class="DataGrid_Body_Row"> <th colspan="2" class="DataGrid_Header_Cell">ADD AND KEEP GOING...</th> <th width="105" id="fieldTotalSaldoInicial" class="DataGrid_Header_Cell_Amount">xx14500.34</th> <th width="105" id="fieldTotalAmtacctdr" class="DataGrid_Header_Cell_Amount">xx14500.34</th> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Wed Jul 28 10:21:00 2010 +0200 @@ -4,14 +4,14 @@ * Version 1.0 (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 + * 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) 2001-2009 Openbravo SLU + * 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-2010 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -55,6 +55,7 @@ public class ReportTrialBalance extends HttpSecureAppServlet { private static final long serialVersionUID = 1L; + private static final int MAX_XLS_ROWS = 64500; public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { @@ -185,8 +186,12 @@ "ReportTrialBalance|cProjectId", "", IsIDFilter.instance); String strGroupBy = vars.getRequestGlobalVariable("inpGroupBy", "ReportTrialBalance|GroupBy"); + String strInitRecord = vars.getRequiredStringParameter("initRecord"); + String strRecordRange = Utility.getContext(this, vars, "#RecordRange", "ReportTrialBalance"); + printPageOpen(response, vars, strDateFrom, strDateTo, strOrg, strLevel, strcBpartnerId, - strmProductId, strcProjectId, strcAcctSchemaId, strGroupBy, strAccountId); + strmProductId, strcProjectId, strcAcctSchemaId, strGroupBy, strAccountId, strInitRecord, + strRecordRange); } else { pageError(response); @@ -196,20 +201,38 @@ private void printPageOpen(HttpServletResponse response, VariablesSecureApp vars, String strDateFrom, String strDateTo, String strOrg, String strLevel, String strcBpartnerId, String strmProductId, String strcProjectId, String strcAcctSchemaId, String strGroupBy, - String strAccountId) throws IOException, ServletException { + String strAccountId, String strInitRecord, String strRecordRange) throws IOException, + ServletException { ReportTrialBalanceData[] data = null; String strTreeOrg = TreeData.getTreeOrg(this, vars.getClient()); String strOrgFamily = getFamily(strTreeOrg, strOrg); + // built limit/offset parameters for oracle/postgres + String rowNum = "0"; + String oraLimit1 = null; + String oraLimit2 = null; + String pgLimit = null; + int intRecordRange = (strRecordRange.equals("0") ? 0 : Integer.parseInt(strRecordRange)); + int intInitRecord = (strInitRecord.equals("") ? 0 : Integer.parseInt(strInitRecord)); + if (intRecordRange != 0) { + if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) { + rowNum = "ROWNUM"; + oraLimit1 = String.valueOf(intInitRecord + intRecordRange); + oraLimit2 = (intInitRecord + 1) + " AND " + oraLimit1; + } else { + rowNum = "0"; + pgLimit = intRecordRange + " OFFSET " + intInitRecord; + } + } + log4j.debug("Output: Expand subaccount details " + strAccountId); - data = ReportTrialBalanceData.selectAccountLines(this, strGroupBy, vars.getLanguage(), - strLevel, strOrgFamily, Utility - .getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility.getContext(this, - vars, "#AccessibleOrgTree", "ReportTrialBalance"), null, null, strDateFrom, - strAccountId, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData - .nDaysAfter(this, strDateTo, "1")); + data = ReportTrialBalanceData.selectAccountLines(this, rowNum, strGroupBy, strLevel, + strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), null, null, + strDateFrom, strAccountId, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, + DateTimeData.nDaysAfter(this, strDateTo, "1"), oraLimit1, oraLimit2, pgLimit); if (data == null) { data = ReportTrialBalanceData.set(); @@ -244,6 +267,8 @@ Map<String, String> props = new HashMap<String, String>(); props.put("classAmount", "DataGrid_Body_Cell_Amount"); props.put("classDefault", "DataGrid_Body_Cell"); + props.put("totalRecords", String.valueOf(data.length)); + props.put("range", String.valueOf(intRecordRange)); table.put("config", props); } catch (JSONException e) { @@ -306,12 +331,12 @@ } } else { if (strLevel.equals("S")) { // SubAccount selected - data = ReportTrialBalanceData.selectAccountLines(this, "", vars.getLanguage(), strLevel, - strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), - Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), + data = ReportTrialBalanceData.selectAccountLines(this, "0", "", strLevel, strOrgFamily, + Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), strAccountFromValue, strAccountToValue, strDateFrom, null, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, - strDateTo, "1")); + strDateTo, "1"), null, null, null); if (strGroupBy.equals("")) discard[2] = "showExpand"; @@ -454,50 +479,58 @@ + strTreeAccount); log4j.debug("strcBpartnerId: " + strcBpartnerId + "strmProductId: " + strmProductId + "strcProjectId: " + strcProjectId); + try { + if (!strDateFrom.equals("") && !strDateTo.equals("") && !strOrg.equals("") + && !strcAcctSchemaId.equals("")) { - if (!strDateFrom.equals("") && !strDateTo.equals("") && !strOrg.equals("") - && !strcAcctSchemaId.equals("")) { + if (strLevel.equals("S")) { + data = ReportTrialBalanceData.selectXLS(this, strLevel, strOrgFamily, Utility.getContext( + this, vars, "#User_Client", "ReportTrialBalance"), Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportTrialBalance"), strAccountFromValue, strAccountToValue, + strDateFrom, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, + DateTimeData.nDaysAfter(this, strDateTo, "1")); + showDimensions = true; + } else { + data = getDataWhenNotSubAccount(vars, strDateFrom, strDateTo, strOrg, strOrgFamily, + strcAcctSchemaId, strLevel, strTreeAccount); + } - if (strLevel.equals("S")) { - data = ReportTrialBalanceData.selectXLS(this, vars.getLanguage(), strLevel, strOrgFamily, - Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility - .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), - strAccountFromValue, strAccountToValue, strDateFrom, strcBpartnerId, strmProductId, - strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, strDateTo, "1")); - showDimensions = true; + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); + } else if (data.length > MAX_XLS_ROWS) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); + } else { + + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml"; + + HashMap<String, Object> parameters = new HashMap<String, Object>(); + + String strLanguage = vars.getLanguage(); + + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg) + ")"); + parameters.put("Title", classInfo.name); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + parameters.put("SHOWTOTALS", false); + parameters.put("SHOWDIMENSIONS", showDimensions); + + renderJR(vars, response, strReportName, "xls", parameters, data, null); + } } else { - data = getDataWhenNotSubAccount(vars, strDateFrom, strDateTo, strOrg, strOrgFamily, - strcAcctSchemaId, strLevel, strTreeAccount); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } + } catch (Exception e) { + log4j.error("Error creating the XLS report in TrialBalanceReport"); - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { - - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml"; - - HashMap<String, Object> parameters = new HashMap<String, Object>(); - - String strLanguage = vars.getLanguage(); - - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - parameters.put("SHOWTOTALS", false); - parameters.put("SHOWDIMENSIONS", showDimensions); - - renderJR(vars, response, strReportName, "xls", parameters, data, null); - } - } else { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } - } private void printPageDataPDF(HttpServletRequest request, HttpServletResponse response, @@ -528,12 +561,12 @@ && !strcAcctSchemaId.equals("")) { if (strLevel.equals("S")) { - data = ReportTrialBalanceData.selectAccountLines(this, strGroupBy, vars.getLanguage(), - strLevel, strOrgFamily, Utility.getContext(this, vars, "#User_Client", - "ReportTrialBalance"), Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportTrialBalance"), strAccountFromValue, strAccountToValue, strDateFrom, null, - strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData - .nDaysAfter(this, strDateTo, "1")); + data = ReportTrialBalanceData.selectAccountLines(this, "0", strGroupBy, strLevel, + strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), + strAccountFromValue, strAccountToValue, strDateFrom, null, strcBpartnerId, + strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, + strDateTo, "1"), null, null, null); if (!strGroupBy.equals("")) strIsSubAccount = true; @@ -543,21 +576,21 @@ } if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } else { String strLanguage = vars.getLanguage(); String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalancePDF.jrxml"; HashMap<String, Object> parameters = new HashMap<String, Object>(); - parameters.put("Title", classInfo.name); parameters.put("TOTAL", Utility.messageBD(this, "Total", strLanguage)); StringBuilder strSubTitle = new StringBuilder(); strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + "\n"); strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg)); + parameters.put("Title", classInfo.name); parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); parameters.put("DEFAULTVIEW", !strIsSubAccount); @@ -568,8 +601,8 @@ } } else { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } } diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql Wed Jul 28 10:21:00 2010 +0200 @@ -5,15 +5,15 @@ * Version 1.0 (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 + * 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. + * 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-2009 Openbravo SLU - * All Rights Reserved. + * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ --> @@ -24,14 +24,15 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT MAX(PARENT_ID) AS PARENT_ID, ID, NAME, ACCOUNT_ID, ELEMENTLEVEL, SUM(AMTACCTDR) AS AMTACCTDR, + SELECT MAX(PARENT_ID) AS PARENT_ID, ID, NAME, ACCOUNT_ID, ELEMENTLEVEL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, 0 AS SALDO_INICIAL,0 AS TOTALAMTACCTDR, 0 AS TOTALAMTACCTCR, 0 AS SALDO_FINAL, TO_DATE(?) AS DATE_FROM, TO_DATE(?) AS DATE_TO, ? AS AD_ORG_ID, '' AS BP, '' AS PADRE, '' AS groupbyname, '' AS groupbyid, '' AS groupbytext, - '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname + '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname, + '' as RN1 FROM - (SELECT AD_TREENODE.PARENT_ID, C_ELEMENTVALUE.C_ELEMENTVALUE_ID AS ID, C_ELEMENTVALUE.ELEMENTLEVEL, - C_ELEMENTVALUE.NAME AS NAME, C_ELEMENTVALUE.VALUE AS ACCOUNT_ID, 0 AS AMTACCTDR, + (SELECT AD_TREENODE.PARENT_ID, C_ELEMENTVALUE.C_ELEMENTVALUE_ID AS ID, C_ELEMENTVALUE.ELEMENTLEVEL, + C_ELEMENTVALUE.NAME AS NAME, C_ELEMENTVALUE.VALUE AS ACCOUNT_ID, 0 AS AMTACCTDR, 0 AS AMTACCTCR FROM AD_TREENODE, C_ELEMENTVALUE WHERE AD_TREENODE.NODE_ID = C_ELEMENTVALUE.C_ELEMENTVALUE_ID @@ -39,10 +40,10 @@ AND AD_TREENODE.ISACTIVE = 'Y' AND C_ELEMENTVALUE.ISACTIVE = 'Y' AND (select max(c_element_id) from c_acctschema_element where c_acctschema_id = ? and ELEMENTTYPE = 'AC') = C_ELEMENTVALUE.C_ELEMENT_ID - UNION - SELECT '0' AS PARENT_ID, F.ACCOUNT_ID AS ID, EV.ELEMENTLEVEL, EV.NAME AS NAME, EV.VALUE AS ACCOUNT_ID, - SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE F.AMTACCTDR END)) AS AMTACCTDR, - SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE f.AMTACCTCR END)) AS AMTACCTCR + UNION + SELECT '0' AS PARENT_ID, F.ACCOUNT_ID AS ID, EV.ELEMENTLEVEL, EV.NAME AS NAME, EV.VALUE AS ACCOUNT_ID, + SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE F.AMTACCTDR END)) AS AMTACCTDR, + SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE f.AMTACCTCR END)) AS AMTACCTCR FROM FACT_ACCT F, C_ELEMENTVALUE EV WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND f.AD_ORG_ID IN('2') @@ -61,7 +62,7 @@ <Parameter name="dateTo"/> <Parameter name="org"/> <Parameter name="treeAcct"/> - <Parameter name="acctSchema"/> + <Parameter name="acctSchema"/> <Parameter name="orgFamily" type="replace" optional="true" after="f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="F.AD_CLIENT_ID IN (" text="'1'"/> <Parameter name="adUserOrg" type="replace" optional="true" after="F.AD_ORG_ID IN(" text="'1'"/> @@ -69,9 +70,9 @@ <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[AND F.DATEACCT < TO_DATE(?)]]></Parameter> <Parameter name="accountFrom" optional="true" after="AND 1=1"><![CDATA[AND EV.VALUE >= ?]]></Parameter> <Parameter name="accountTo" optional="true" after="AND 1=1"><![CDATA[AND EV.VALUE <= ?]]></Parameter> - <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> + <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> </SqlMethod> - + <SqlMethod name="set" type="constant" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql></Sql> @@ -81,52 +82,35 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT ID, ACCOUNT_ID, NAME, + SELECT * + FROM ( SELECT '0' AS RN1, D.*, + CASE ? + WHEN 'BPartner' THEN TO_CHAR(bp.name) + WHEN 'Product' THEN TO_CHAR(pd.name) + WHEN 'Project' THEN TO_CHAR(pj.name) + ELSE '' + END AS groupbyname + FROM ( + SELECT ID, ACCOUNT_ID, C.NAME AS NAME, SUM(SALDO_INICIAL) AS SALDO_INICIAL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, SUM(SALDO_INICIAL+AMTACCTDR-AMTACCTCR) AS SALDO_FINAL, - groupbyid, - CASE ? - WHEN 'BPartner' THEN - CASE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - WHEN 'Product' THEN - CASE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - WHEN 'Project' THEN - CASE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - ELSE '' - END AS groupbyname + groupbyid FROM ((SELECT ID, ACCOUNT_ID, NAME, - 0 AS AMTACCTDR, 0 AS AMTACCTCR, + 0 AS AMTACCTDR, 0 AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL, - groupbyname, groupbyid + groupbyid FROM ((SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -138,19 +122,11 @@ (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -159,29 +135,21 @@ AND 3=3 AND F.ISACTIVE = 'Y' AND F.FACTACCTTYPE = 'O')) A - GROUP BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + GROUP BY ACCOUNT_ID, ID, NAME, groupbyid HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 ) UNION (SELECT ID, ACCOUNT_ID, NAME, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - 0 AS SALDO_INICIAL, groupbyname, groupbyid + 0 AS SALDO_INICIAL, groupbyid FROM (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -192,19 +160,19 @@ AND F.FACTACCTTYPE <> 'C' AND F.FACTACCTTYPE <> 'O' AND F.ISACTIVE = 'Y') B - GROUP BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + GROUP BY ACCOUNT_ID, ID, NAME, groupbyid HAVING SUM(AMTACCTDR) <> 0 OR SUM(AMTACCTCR) <> 0 )) C - GROUP BY ACCOUNT_ID, ID, groupbyid, groupbyname, NAME - ORDER BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + WHERE 4=4 + GROUP BY ACCOUNT_ID, ID, C.NAME, groupbyid ) D + LEFT JOIN C_BPARTNER bp ON groupbyid = bp.C_BPARTNER_ID + LEFT JOIN M_PRODUCT pd ON groupbyid = pd.M_PRODUCT_ID + LEFT JOIN C_PROJECT pj ON groupbyid = pj.C_PROJECT_ID + ORDER BY ACCOUNT_ID, ID, D.NAME, groupbyname, groupbyid + ) E + WHERE 5=5 AND 6=6 ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="groupby"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> + <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" /> <Parameter name="groupby"/> <Parameter name="groupby"/> <Parameter name="accountLevel"/> @@ -221,7 +189,6 @@ <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter>  <Parameter name="groupby"/> - <Parameter name="groupby"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after=" f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after=" F.AD_CLIENT_ID IN (" text="'1'"/> @@ -236,7 +203,6 @@ <Parameter name="acctSchema" optional="true" after="AND 3=3"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter>  <Parameter name="groupby"/> - <Parameter name="groupby"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="AND F.AD_CLIENT_ID IN (" text="'1'"/> @@ -250,44 +216,34 @@ <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.M_PRODUCT_ID IN]]></Parameter> <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.C_PROJECT_ID IN]]></Parameter> <Parameter name="acctSchema" optional="true" after="AND 2=2"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="WHERE 4=4"><![CDATA[ AND ROWNUM <= ]]></Parameter> + <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 5=5"><![CDATA[ AND RN1 BETWEEN ]]></Parameter> + <Parameter name="pgLimit" type="argument" optional="true" after="6=6"><![CDATA[ LIMIT ]]></Parameter> </SqlMethod> - + <SqlMethod name="selectXLS" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT ID, ACCOUNT_ID, NAME, + SELECT ID, ACCOUNT_ID, C.NAME AS NAME, SUM(SALDO_INICIAL) AS SALDO_INICIAL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, SUM(SALDO_INICIAL+AMTACCTDR-AMTACCTCR) AS SALDO_FINAL, bpid, pdid, pjid, - CASE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(bpid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(bpid), TO_CHAR(?)) - END AS bpname, - CASE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(pdid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(pdid), TO_CHAR(?)) - END AS pdname, - CASE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(pjid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(pjid), TO_CHAR(?)) - END AS pjname + c_bpartner.name AS bpname, + m_product.name AS pdname, + c_project.name AS pjname FROM ((SELECT ID, ACCOUNT_ID, NAME, - 0 AS AMTACCTDR, 0 AS AMTACCTCR, + 0 AS AMTACCTDR, 0 AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL, - bpid, pdid, pjid, bpname, pdname + bpid, pdid, pjid FROM ((SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -298,12 +254,8 @@ UNION (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -312,22 +264,18 @@ AND 3=3 AND F.ISACTIVE = 'Y' AND F.FACTACCTTYPE = 'O')) A - GROUP BY ACCOUNT_ID, ID, bpid, pdid, pjid, bpname, pdname, NAME + GROUP BY ACCOUNT_ID, ID, NAME, bpid, pdid, pjid HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 ) UNION (SELECT ID, ACCOUNT_ID, NAME, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - 0 AS SALDO_INICIAL, bpid, pdid, pjid, bpname, pdname + 0 AS SALDO_INICIAL, bpid, pdid, pjid FROM (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -338,17 +286,14 @@ AND F.FACTACCTTYPE <> 'C' AND F.FACTACCTTYPE <> 'O' AND F.ISACTIVE = 'Y') B - GROUP BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid )) C - GROUP BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid - ORDER BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid + GROUP BY ACCOUNT_ID, ID, NAME, bpid, pdid, pjid )) C + LEFT JOIN C_BPARTNER ON bpid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON pdid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON pjid = C_PROJECT.C_PROJECT_ID + GROUP BY ACCOUNT_ID, ID, C.NAME, bpid, pdid, pjid, c_bpartner.name, m_product.name, c_project.name + ORDER BY ACCOUNT_ID, ID, C.NAME, bpname, bpid, pdname, pdid, pjname, pjid ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after="f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="F.AD_CLIENT_ID IN (" text="'1'"/> @@ -386,10 +331,10 @@ <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.C_PROJECT_ID IN]]></Parameter> <Parameter name="acctSchema" optional="true" after="AND 2=2"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> </SqlMethod> - + <SqlMethod name="selectInitialBalance" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> - <Sql><![CDATA[ + <Sql><![CDATA[ SELECT ACCOUNT_ID, COALESCE(SUM(AMTACCTDR),0) AS AMTACCTDR, COALESCE(SUM(AMTACCTCR),0) AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL FROM ( @@ -402,7 +347,7 @@ AND F.AD_ORG_ID IN ('1') AND F.AD_CLIENT_ID IN ('1') AND F.ISACTIVE = 'Y') - UNION + UNION ALL (SELECT F.ACCOUNT_ID AS ACCOUNT_ID, F.AMTACCTDR AS AMTACCTDR, F.AMTACCTCR AS AMTACCTCR, F.DATEACCT, F.FACTACCTTYPE FROM FACT_ACCT F @@ -433,7 +378,7 @@ <Parameter name="orgFamily" type="replace" optional="true" after="AND F.AD_ORG_ID IN (" text="'1'"/> <Parameter name="clientFamily" type="replace" optional="true" after="AND F.AD_CLIENT_ID IN (" text="'1'"/> </SqlMethod> - + <SqlMethod name="treeAccount" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql> @@ -463,7 +408,7 @@ </Sql> <Parameter name="organization"/> </SqlMethod> - + <SqlMethod name="selectAcctSchemaName" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql> @@ -473,7 +418,7 @@ </Sql> <Parameter name="acctSchema"/> </SqlMethod> - + <SqlMethod name="selectAccountingName" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ @@ -484,24 +429,24 @@ ]]></Sql> <Parameter name="cElementvalueId"/> </SqlMethod> - + <SqlMethod name="selectLastAccount" type="preparedStatement" return="string" default=""> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ - SELECT C_ELEMENTVALUE.C_ELEMENTVALUE_ID + SELECT C_ELEMENTVALUE.C_ELEMENTVALUE_ID FROM C_ELEMENTVALUE WHERE C_ELEMENTVALUE.VALUE = ( SELECT MAX(C_ELEMENTVALUE.VALUE) FROM C_ELEMENTVALUE - WHERE C_ELEMENTVALUE.AD_Org_ID IN('1') - AND C_ELEMENTVALUE.AD_Client_ID IN('1') + WHERE C_ELEMENTVALUE.AD_Org_ID IN('1') + AND C_ELEMENTVALUE.AD_Client_ID IN('1') AND C_ELEMENTVALUE.IsSummary='N' AND C_ELEMENTVALUE.ISACTIVE='Y') ]]></Sql> <Parameter name="adOrgClient" type="replace" optional="true" after="C_ELEMENTVALUE.AD_Org_ID IN(" text="'1'"/> <Parameter name="adUserClient" type="replace" optional="true" after="C_ELEMENTVALUE.AD_Client_ID IN(" text="'1'"/> </SqlMethod> - + <SqlMethod name="selectSubaccountDescription" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ @@ -511,7 +456,7 @@ ]]></Sql> <Parameter name="elementValueId"/> </SqlMethod> - + <SqlMethod name="selectAccountValue" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[

diff -r 17a70403b6d3 src-db/database/sourcedata/AD_MESSAGE.xml --- a/src-db/database/sourcedata/AD_MESSAGE.xml Wed Mar 31 12:07:41 2010 +0200 +++ b/src-db/database/sourcedata/AD_MESSAGE.xml Mon Sep 20 16:31:57 2010 +0530 @@ -30760,6 +30760,17 @@  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_MESSAGE> +<AD_MESSAGE> + <AD_MESSAGE_ID><![CDATA[534CDD9CFDC2472291C939D898221A85]]></AD_MESSAGE_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <VALUE><![CDATA[ReportsLimitBody]]></VALUE> + <MSGTEXT><![CDATA[This report cannot be rendered because the filtering criteria you have specified return a number of rows that exceeds the maximum allowed. The limit of rows to display in a report is @limit@, and your criteria resulted in an estimated number of @rows@ rows. Please, use more selective filtering criteria and execute the report again.]]></MSGTEXT> + <MSGTYPE><![CDATA[I]]></MSGTYPE> + <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +</AD_MESSAGE> + <AD_MESSAGE>  <AD_MESSAGE_ID><![CDATA[548069F5D9E04ECF80B3EA2EA7218036]]></AD_MESSAGE_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -31072,6 +31083,17 @@  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_MESSAGE> +<AD_MESSAGE> + <AD_MESSAGE_ID><![CDATA[652772F68182407FAEEE9F89CB87E53F]]></AD_MESSAGE_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <VALUE><![CDATA[ReportsLimitHeader]]></VALUE> + <MSGTEXT><![CDATA[Report records limit reached]]></MSGTEXT> + <MSGTYPE><![CDATA[E]]></MSGTYPE> + <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +</AD_MESSAGE> + <AD_MESSAGE>  <AD_MESSAGE_ID><![CDATA[6775556A78264AB192CAD83F25E37E7B]]></AD_MESSAGE_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -31827,6 +31849,17 @@  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_MESSAGE> +<AD_MESSAGE> + <AD_MESSAGE_ID><![CDATA[AC594E632F5C4970A881AAEC763BC705]]></AD_MESSAGE_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <VALUE><![CDATA[MAX_ROWS_LIMIT_EXCEED]]></VALUE> + <MSGTEXT><![CDATA[Maximum number of rows exceeded. Please modify filtering criteria.]]></MSGTEXT> + <MSGTYPE><![CDATA[W]]></MSGTYPE> + <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +</AD_MESSAGE> + <AD_MESSAGE>  <AD_MESSAGE_ID><![CDATA[AC63454AE63A49CDA0EB25806B656B6D]]></AD_MESSAGE_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java --- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java Mon Sep 20 16:31:57 2010 +0530 @@ -46,6 +46,8 @@ public class ReportGeneralLedger extends HttpSecureAppServlet { private static final long serialVersionUID = 1L; + private static final int MAX_XLS_ROWS = 64500; + private static final int PDF_REPORT_LIMIT = 0; public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { @@ -292,13 +294,13 @@ strcelementvaluetodes = ""; vars.setSessionValue("inpElementValueIdTo_DES", strcelementvaluetodes); } - data = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, toDatePlusOne, - strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, - null, null, pgLimit, oraLimit1, oraLimit2); + data = ReportGeneralLedgerData.select(this, rowNum, strGroupBy, strGroupByText, strDateFrom, + toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, + strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, null, null, pgLimit, oraLimit1, + oraLimit2); if (log4j.isDebugEnabled()) log4j.debug("RecordNo: " + initRecordNumber); // In case this is not the first screen to show, initial balance may need to include amounts @@ -306,15 +308,14 @@ ReportGeneralLedgerData[] dataTotal = null; if (data != null && data.length > 1) { - dataTotal = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strYearInitialDate, DateTimeData - .nDaysAfter(this, data[0].dateacct, "1"), strOrgFamily, strcBpartnerId, - strmProductId, strcProjectId, strAmtFrom, strAmtTo, data[0].id, data[0].dateacctnumber - + data[0].factAcctGroupId + data[0].description + data[0].isdebit, - data[0].groupbyid, null, null, null); + dataTotal = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, + strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strYearInitialDate, DateTimeData.nDaysAfter(this, data[0].dateacct, + "1"), strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, + strAmtTo, data[0].id, data[0].dateacctnumber + data[0].factAcctGroupId + + data[0].description + data[0].isdebit, data[0].groupbyid, null, null, null); } // Now dataTotal is covered adding debit and credit amounts for (int i = 0; dataTotal != null && i < dataTotal.length; i++) { @@ -495,80 +496,110 @@ vars.getLanguage()) : (strGroupBy.equals("Project") ? Utility.messageBD(this, "Project", vars.getLanguage()) : ""))); String strAllaccounts = "Y"; + try { + if (!strDateFrom.equals("") && !strDateTo.equals("")) { + strOrgFamily = getFamily(strTreeOrg, strOrg); + if (!strHide.equals("Y")) + strHide = "N"; + if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { + if (strcelementvalueto.equals("")) + strcelementvalueto = strcelementvaluefrom; + strAllaccounts = "N"; + } + int limit = 10; + int mycount = 0; + try { + // limit = Integer.parseInt(Utility.getPreference(vars, "ReportsLimit", "")); + limit = PDF_REPORT_LIMIT; + if (limit > 0) { + mycount = Integer.parseInt(ReportGeneralLedgerData.selectCount(this, "ROWNUM", + strGroupByText, strGroupBy, strDateFrom, toDatePlusOne, strAllaccounts, + strcelementvaluefrom, strcelementvalueto, Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportGeneralLedger"), Utility.getContext(this, vars, + "#User_Client", "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, + toDatePlusOne, strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, + strAmtFrom, strAmtTo, null, null, null, null, null, null)); + } + } catch (NumberFormatException e) { - if (!strDateFrom.equals("") && !strDateTo.equals("")) { - strOrgFamily = getFamily(strTreeOrg, strOrg); - if (!strHide.equals("Y")) - strHide = "N"; - if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { - if (strcelementvalueto.equals("")) - strcelementvalueto = strcelementvaluefrom; - strAllaccounts = "N"; + } + if (limit > 0 && mycount > limit) { + String msgbody = Utility.messageBD(this, "ReportsLimitBody", vars.getLanguage()); + msgbody = msgbody.replace("@rows@", Integer.toString(mycount)); + msgbody = msgbody.replace("@limit@", Integer.toString(limit)); + advisePopUp(request, response, "ERROR", Utility.messageBD(this, "ReportsLimitHeader", + vars.getLanguage()), msgbody); + } else { + data = ReportGeneralLedgerData.select(this, "ROWNUM", strGroupByText, strGroupBy, + strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, + strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, null, null, null, null, + null); + } } - data = ReportGeneralLedgerData.select(this, "0", strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, toDatePlusOne, - strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, - null, null, null, null, null); - } - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { - String strOld = ""; - BigDecimal totalDebit = BigDecimal.ZERO; - BigDecimal totalCredit = BigDecimal.ZERO; - BigDecimal subTotal = BigDecimal.ZERO; + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars + .getLanguage())); + } else { + String strOld = ""; + BigDecimal totalDebit = BigDecimal.ZERO; + BigDecimal totalCredit = BigDecimal.ZERO; + BigDecimal subTotal = BigDecimal.ZERO; - subreport = new ReportGeneralLedgerData[data.length]; - for (int i = 0; data != null && i < data.length; i++) { - if (!strOld.equals(data[i].groupbyid + data[i].id)) { - subreport = ReportGeneralLedgerData.selectTotal(this, strDateFrom, DateTimeData - .nDaysAfter(this, strDateTo, "1"), (strGroupBy.equals("BPartner") ? "('" - + data[i].groupbyid + "')" : strcBpartnerId), (strGroupBy.equals("Product") ? "('" - + data[i].groupbyid + "')" : strmProductId), (strGroupBy.equals("Project") ? "('" - + data[i].groupbyid + "')" : strcProjectId), strcAcctSchemaId, data[i].id, - strYearInitialDate, strDateFrom, strOrgFamily, strHide); - totalDebit = BigDecimal.ZERO; - totalCredit = BigDecimal.ZERO; - subTotal = BigDecimal.ZERO; + subreport = new ReportGeneralLedgerData[data.length]; + for (int i = 0; data != null && i < data.length; i++) { + if (!strOld.equals(data[i].groupbyid + data[i].id)) { + subreport = ReportGeneralLedgerData.selectTotal(this, strDateFrom, DateTimeData + .nDaysAfter(this, strDateTo, "1"), (strGroupBy.equals("BPartner") ? "('" + + data[i].groupbyid + "')" : strcBpartnerId), (strGroupBy.equals("Product") ? "('" + + data[i].groupbyid + "')" : strmProductId), (strGroupBy.equals("Project") ? "('" + + data[i].groupbyid + "')" : strcProjectId), strcAcctSchemaId, data[i].id, + strYearInitialDate, strDateFrom, strOrgFamily, strHide); + totalDebit = BigDecimal.ZERO; + totalCredit = BigDecimal.ZERO; + subTotal = BigDecimal.ZERO; + } + totalDebit = totalDebit.add(new BigDecimal(data[i].amtacctdr)); + data[i].totalacctdr = new BigDecimal(subreport[0].totalacctdr).add(totalDebit).toString(); + totalCredit = totalCredit.add(new BigDecimal(data[i].amtacctcr)); + data[i].totalacctcr = new BigDecimal(subreport[0].totalacctcr).add(totalCredit) + .toString(); + subTotal = subTotal.add(new BigDecimal(data[i].total)); + data[i].totalacctsub = new BigDecimal(subreport[0].total).add(subTotal).toString(); + data[i].previousdebit = subreport[0].totalacctdr; + data[i].previouscredit = subreport[0].totalacctcr; + data[i].previoustotal = subreport[0].total; + strOld = data[i].groupbyid + data[i].id; } - totalDebit = totalDebit.add(new BigDecimal(data[i].amtacctdr)); - data[i].totalacctdr = new BigDecimal(subreport[0].totalacctdr).add(totalDebit).toString(); - totalCredit = totalCredit.add(new BigDecimal(data[i].amtacctcr)); - data[i].totalacctcr = new BigDecimal(subreport[0].totalacctcr).add(totalCredit).toString(); - subTotal = subTotal.add(new BigDecimal(data[i].total)); - data[i].totalacctsub = new BigDecimal(subreport[0].total).add(subTotal).toString(); - data[i].previousdebit = subreport[0].totalacctdr; - data[i].previouscredit = subreport[0].totalacctcr; - data[i].previoustotal = subreport[0].total; - strOld = data[i].groupbyid + data[i].id; + + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.jrxml"; + response.setHeader("Content-disposition", "inline; filename=ReportGeneralLedgerPDF.pdf"); + + HashMap<String, Object> parameters = new HashMap<String, Object>(); + + String strLanguage = vars.getLanguage(); + + parameters.put("ShowGrouping", new Boolean(!strGroupBy.equals(""))); + parameters.put("Title", classInfo.name); + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + parameters.put("Previous", Utility.messageBD(this, "Initial Balance", strLanguage)); + parameters.put("Total", Utility.messageBD(this, "Total", strLanguage)); + String strDateFormat; + strDateFormat = vars.getJavaDateFormat(); + parameters.put("strDateFormat", strDateFormat); + renderJR(vars, response, strReportName, "pdf", parameters, data, null); } - - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.jrxml"; - response.setHeader("Content-disposition", "inline; filename=ReportGeneralLedgerPDF.pdf"); - - HashMap<String, Object> parameters = new HashMap<String, Object>(); - - String strLanguage = vars.getLanguage(); - - parameters.put("ShowGrouping", new Boolean(!strGroupBy.equals(""))); - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - parameters.put("Previous", Utility.messageBD(this, "Initial Balance", strLanguage)); - parameters.put("Total", Utility.messageBD(this, "Total", strLanguage)); - String strDateFormat; - strDateFormat = vars.getJavaDateFormat(); - parameters.put("strDateFormat", strDateFormat); - - renderJR(vars, response, strReportName, "pdf", parameters, data, null); + } catch (Throwable e) { + log4j.error("Error creating the PDF report in General Ledger"); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } } @@ -587,50 +618,68 @@ String strYearInitialDate = ReportGeneralLedgerData.yearInitialDate(this, vars .getSessionValue("#AD_SqlDateFormat"), strDateFrom, Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strOrgFamily); - if (strYearInitialDate.equals("")) - strYearInitialDate = strDateFrom; - String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1"); + String oraLimit1 = ""; + String pgLimit = ""; + if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) { + oraLimit1 = oraLimit1 + (MAX_XLS_ROWS + 1); + } else { + pgLimit = "0 OFFSET " + (MAX_XLS_ROWS + 1); + } - String strAllaccounts = "Y"; + try { + if (strYearInitialDate.equals("")) + strYearInitialDate = strDateFrom; + String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1"); - if (!strDateFrom.equals("") && !strDateTo.equals("")) { - if (!strHide.equals("Y")) - strHide = "N"; - if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { - if (strcelementvalueto.equals("")) - strcelementvalueto = strcelementvaluefrom; - strAllaccounts = "N"; + String strAllaccounts = "Y"; + + if (!strDateFrom.equals("") && !strDateTo.equals("")) { + if (!strHide.equals("Y")) + strHide = "N"; + if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { + if (strcelementvalueto.equals("")) + strcelementvalueto = strcelementvaluefrom; + strAllaccounts = "N"; + } + data = ReportGeneralLedgerData.selectXLS(this, strDateFrom, toDatePlusOne, strAllaccounts, + strcelementvaluefrom, strcelementvalueto, Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportGeneralLedger"), Utility.getContext(this, vars, + "#User_Client", "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, + toDatePlusOne, strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, + strAmtTo, oraLimit1, pgLimit); } - data = ReportGeneralLedgerData.selectXLS(this, vars.getLanguage(), strDateFrom, - toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, Utility - .getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility - .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, - strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, - strmProductId, strcProjectId, strAmtFrom, strAmtTo); - } - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars + .getLanguage())); + } else if (data.length > MAX_XLS_ROWS) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); + } else { - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml"; + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml"; - HashMap<String, Object> parameters = new HashMap<String, Object>(); + HashMap<String, Object> parameters = new HashMap<String, Object>(); - String strLanguage = vars.getLanguage(); + String strLanguage = vars.getLanguage(); - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - String strDateFormat; - strDateFormat = vars.getJavaDateFormat(); - parameters.put("strDateFormat", strDateFormat); + parameters.put("Title", classInfo.name); + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + String strDateFormat; + strDateFormat = vars.getJavaDateFormat(); + parameters.put("strDateFormat", strDateFormat); - renderJR(vars, response, strReportName, "xls", parameters, data, null); + renderJR(vars, response, strReportName, "xls", parameters, data, null); + } + } catch (Throwable e) { + log4j.error("Error creating the XLS report in General Ledger", e); + + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } } diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql Mon Sep 20 16:31:57 2010 +0530 @@ -26,10 +26,18 @@ <![CDATA[ SELECT * FROM ( SELECT * - FROM ( SELECT '0' AS RN1, C.* FROM ( + FROM ( SELECT '0' AS RN1, C.NAME, C.VALUE, C.DATEACCT, C.AMTACCTDR, C.AMTACCTCR, C.TOTAL, C.FACT_ACCT_GROUP_ID, C.ID, C.GROUPBYID, + C.DESCRIPTION, C.ISDEBIT, C.TOTALACCTDR, C.TOTALACCTCR, C.TOTALACCTSUB, C.PREVIOUSDEBIT, C.PREVIOUSCREDIT, C.PREVIOUSTOTAL, + C.FINALDEBIT, C.FINALCREDIT, C.FINALTOTAL, C.DATEACCTNUMBER, C.groupby, C.bpid, C.bpname, C.pdid, C.pdname, C.pjid, C.pjname, + CASE ? + WHEN 'BPartner' THEN TO_CHAR(C_Bpartner.NAME) + WHEN 'Product' THEN TO_CHAR(M_Product.name) + WHEN 'Project' THEN TO_CHAR(C_Project.NAME) + ELSE '' END AS groupbyname + FROM ( SELECT VALUE, NAME, DATEACCT, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, (SUM(AMTACCTDR)-SUM(AMTACCTCR)) AS TOTAL, - FACT_ACCT_GROUP_ID, ID, groupbyid, groupbyname, + FACT_ACCT_GROUP_ID, ID, groupbyid, DESCRIPTION, ISDEBIT, 0 AS TOTALACCTDR, 0 AS TOTALACCTCR, 0 AS TOTALACCTSUB, 0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL, @@ -41,20 +49,12 @@ DATEACCT, AMTACCTDR, AMTACCTCR, FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id + WHEN 'BPartner' THEN FACT_ACCT.c_bpartner_id + WHEN 'Product' THEN FACT_ACCT.m_product_id + WHEN 'Project' THEN FACT_ACCT.c_project_id ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN (case AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) end) - WHEN 'Product' THEN (case AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) end) - WHEN 'Project' THEN (case AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) end) - ELSE '' END AS groupbyname, CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT FROM FACT_ACCT - LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID LEFT JOIN( select account_id, record_id2, sum(amtacctdr-amtacctcr) as sum from fact_acct f1 @@ -76,24 +76,21 @@ AND 2=2 AND 3=3) D WHERE 6=6 - GROUP BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + GROUP BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 - ORDER BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + ORDER BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT ) C + LEFT JOIN C_BPARTNER ON C.groupbyid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON C.groupbyid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON C.groupbyid = C_PROJECT.C_PROJECT_ID ) B WHERE 1=1 ) A ORDER BY groupbyname, groupbyid, VALUE, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" /> + <Parameter name="groupby"/> <Parameter name="groupbytext"/> <Parameter name="groupby"/> - <Parameter name="groupby"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="parDateFrom" optional="true" after="where 5=5"><![CDATA[AND DATEACCT >= TO_DATE(?) ]]></Parameter> <Parameter name="parDateTo" optional="true" after="where 5=5"><![CDATA[AND DATEACCT < TO_DATE(?) ]]></Parameter> <Parameter name="allaccounts"/> @@ -115,7 +112,7 @@ <Parameter name="factAcctId" optional="true" after="WHERE 6=6"><![CDATA[and to_char(DATEACCT,'J')||FACT_ACCT_GROUP_ID||DESCRIPTION||ISDEBIT < ? ]]></Parameter> <Parameter name="cBPartner" optional="true" after="WHERE 6=6"><![CDATA[AND groupbyid = ? ]]></Parameter> <Parameter name="pgLimit" type="argument" optional="true" after=", FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter> - <Parameter name="oraLimit1" type="argument" optional="true" after=") C"><![CDATA[ WHERE ROWNUM <= ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="C_PROJECT.C_PROJECT_ID"><![CDATA[ WHERE ROWNUM <= ]]></Parameter> <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter> </SqlMethod> @@ -123,21 +120,21 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT VALUE, NAME, DATEACCT, + SELECT VALUE, NAME, DATEACCT, AMTACCTDR, AMTACCTCR, + FACT_ACCT_GROUP_ID, ID, bpid, bpname, pdid, pdname, pjid, pjname, + DESCRIPTION + FROM( + SELECT D.VALUE, D.NAME, DATEACCT, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - FACT_ACCT_GROUP_ID, ID, bpid, bpname, pdid, pdname, pjid, pjname, - DESCRIPTION + FACT_ACCT_GROUP_ID, ID, bpid, C_BPARTNER.NAME AS bpname, pdid, M_PRODUCT.NAME AS pdname, pjid, C_PROJECT.NAME AS pjname, + D.DESCRIPTION, ISDEBIT FROM (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME, DATEACCT, AMTACCTDR, AMTACCTCR, FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - (case AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) end) as bpname, (case AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) end) as pdname, (case AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) end) as pjname, + FACT_ACCT.c_bpartner_id as bpid, FACT_ACCT.m_product_id as pdid, FACT_ACCT.c_project_id as pjid, CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT FROM FACT_ACCT - LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID LEFT JOIN( select account_id, record_id2, sum(amtacctdr-amtacctcr) as sum from fact_acct f1 @@ -158,18 +155,16 @@ AND FACT_ACCT.AD_ORG_ID IN ('2') AND 2=2 AND 3=3) D + LEFT JOIN C_BPARTNER ON D.bpid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON D.pdid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON D.pjid = C_PROJECT.C_PROJECT_ID WHERE 6=6 - GROUP BY VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT - HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 + GROUP BY D.VALUE, D.NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, D.description, C_BPARTNER.NAME, bpid, M_PRODUCT.NAME, pdid, C_PROJECT.NAME, pjid, ISDEBIT + HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0) + WHERE 7=7 ORDER BY VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="parDateFrom" optional="true" after="where 5=5"><![CDATA[AND DATEACCT >= TO_DATE(?) ]]></Parameter> <Parameter name="parDateTo" optional="true" after="where 5=5"><![CDATA[AND DATEACCT < TO_DATE(?) ]]></Parameter> <Parameter name="allaccounts"/> @@ -187,6 +182,8 @@ <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter> <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)>=TO_NUMBER(?) ]]></Parameter> <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="WHERE 7=7"><![CDATA[ AND ROWNUM <= ]]></Parameter> + <Parameter name="pgLimit" type="argument" optional="true" after=", pdname, pdid, pjname, pjid, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter> </SqlMethod> <SqlMethod name="set" type="constant" return="multiple"> @@ -280,4 +277,104 @@ ]]></Sql> <Parameter name="elementValueId"/> </SqlMethod> + + <SqlMethod name="selectCount" type="preparedStatement" return="string"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT COUNT(NAME || VALUE || DATEACCT || AMTACCTDR || AMTACCTCR || TOTAL || FACT_ACCT_GROUP_ID || ID || GROUPBYID || + DESCRIPTION || ISDEBIT || TOTALACCTDR || TOTALACCTCR || TOTALACCTSUB || PREVIOUSDEBIT || PREVIOUSCREDIT || PREVIOUSTOTAL || + FINALDEBIT || FINALCREDIT || FINALTOTAL || DATEACCTNUMBER || groupby || bpid || bpname || pdid || pdname || pjid || + pjname || groupbyname) AS COUNT + FROM ( + SELECT * + FROM ( SELECT '0' AS RN1, C.NAME, C.VALUE, C.DATEACCT, C.AMTACCTDR, C.AMTACCTCR, C.TOTAL, C.FACT_ACCT_GROUP_ID, C.ID, C.GROUPBYID, + C.DESCRIPTION, C.ISDEBIT, C.TOTALACCTDR, C.TOTALACCTCR, C.TOTALACCTSUB, C.PREVIOUSDEBIT, C.PREVIOUSCREDIT, C.PREVIOUSTOTAL, + C.FINALDEBIT, C.FINALCREDIT, C.FINALTOTAL, C.DATEACCTNUMBER, C.groupby, C.bpid, C.bpname, C.pdid, C.pdname, C.pjid, C.pjname, + CASE ? + WHEN 'BPartner' THEN TO_CHAR(C_Bpartner.NAME) + WHEN 'Product' THEN TO_CHAR(M_Product.name) + WHEN 'Project' THEN TO_CHAR(C_Project.NAME) + ELSE '' END AS groupbyname + FROM ( + SELECT VALUE, NAME, DATEACCT, + SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, (SUM(AMTACCTDR)-SUM(AMTACCTCR)) AS TOTAL, + FACT_ACCT_GROUP_ID, ID, groupbyid, + DESCRIPTION, ISDEBIT, + 0 AS TOTALACCTDR, 0 AS TOTALACCTCR, 0 AS TOTALACCTSUB, + 0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL, + 0 AS FINALDEBIT, 0 AS FINALCREDIT, 0 AS FINALTOTAL, + TO_CHAR(DATEACCT,'J') AS DATEACCTNUMBER, + ? AS groupby, '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname + FROM + (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME, + DATEACCT, AMTACCTDR, AMTACCTCR, + FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION, + CASE ? + WHEN 'BPartner' THEN FACT_ACCT.c_bpartner_id + WHEN 'Product' THEN FACT_ACCT.m_product_id + WHEN 'Project' THEN FACT_ACCT.c_project_id + ELSE '' END AS groupbyid, + CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT + FROM FACT_ACCT + LEFT JOIN( + select account_id, record_id2, sum(amtacctdr-amtacctcr) as sum + from fact_acct f1 + where 5=5 + group by account_id, record_id2 + ) f2 ON fact_acct.account_id = f2.account_id and fact_acct.record_id2 = f2.record_id2 + WHERE (?='Y' OR FACT_ACCT.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 FACT_ACCT.AD_ORG_ID IN ('1') + AND FACT_ACCT.AD_CLIENT_ID IN ('1') + AND (? = 'N' OR (fact_acct.RECORD_ID2 IS NULL) OR (SUM != 0)) + AND 1=1 + AND FACT_ACCT.AD_ORG_ID IN ('2') + AND 2=2 + AND 3=3) D + WHERE 6=6 + GROUP BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 + ORDER BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + ) C + LEFT JOIN C_BPARTNER ON C.groupbyid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON C.groupbyid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON C.groupbyid = C_PROJECT.C_PROJECT_ID + ) B WHERE 1=1 + ) A ORDER BY groupbyname, groupbyid, VALUE, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + ]]></Sql> + <Field name="rownum" value="count"/> + <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" /> + <Parameter name="groupby"/> + <Parameter name="groupbytext"/> + <Parameter name="groupby"/> + <Parameter name="parDateFrom" optional="true" after="where 5=5"><![CDATA[AND DATEACCT >= TO_DATE(?) ]]></Parameter> + <Parameter name="parDateTo" optional="true" after="where 5=5"><![CDATA[AND DATEACCT < TO_DATE(?) ]]></Parameter> + <Parameter name="allaccounts"/> + <Parameter name="accountFrom"/> + <Parameter name="accountTo"/> + <Parameter name="adUserOrg" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/> + <Parameter name="adUserClient" type="replace" optional="true" after="AND FACT_ACCT.AD_CLIENT_ID IN (" text="'1'"/> + <Parameter name="hideMatched"/> + <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.C_ACCTSCHEMA_ID = ? ]]></Parameter> + <Parameter name="parDateFrom2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT >= TO_DATE(?) ]]></Parameter> + <Parameter name="parDateTo2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT < TO_DATE(?) ]]></Parameter> + <Parameter name="org" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'2'"/> + <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter> + <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.M_PRODUCT_ID IN]]></Parameter> + <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter> + <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)>=TO_NUMBER(?) ]]></Parameter> + <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter> + <Parameter name="accountId" optional="true" after="WHERE 6=6"><![CDATA[AND ID = ? ]]></Parameter> + <Parameter name="factAcctId" optional="true" after="WHERE 6=6"><![CDATA[and to_char(DATEACCT,'J')||FACT_ACCT_GROUP_ID||DESCRIPTION||ISDEBIT < ? ]]></Parameter> + <Parameter name="cBPartner" optional="true" after="WHERE 6=6"><![CDATA[AND groupbyid = ? ]]></Parameter> + <Parameter name="pgLimit" type="argument" optional="true" after=", FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="C_PROJECT.C_PROJECT_ID"><![CDATA[ WHERE ROWNUM <= ]]></Parameter> + <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter> + </SqlMethod> </SqlClass> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html Mon Sep 20 16:31:57 2010 +0530 @@ -24,7 +24,7 @@ <title>Balance Sheet</title> <link rel="shortcut icon" href="../../../../../web/images/favicon.ico" type="image/x-icon" /> <link rel="stylesheet" type="text/css" href="../../../../../web/skins/Default/Openbravo_ERP_250.css" id="paramCSS" /> - + <script language="JavaScript" type="text/javascript" id="paramDirectory"> var baseDirectory = "../../../../../web/"; </script> @@ -54,8 +54,8 @@ function imprimir() { - openPDFFiltered("ReportTrialBalance.html?Command=PDF","PDF"); - return true; + openPDFFiltered("ReportTrialBalance.html?Command=PDF","PDF"); + return true; } </script> <script language="JavaScript" type="text/javascript"> @@ -118,14 +118,21 @@ if (getReadyStateHandler(XMLHttpRequestObj)) { try { if (XMLHttpRequestObj.responseText) strText = XMLHttpRequestObj.responseText; - } catch (e) { + } catch (e) { } - if (paramXMLParticular!=null && paramXMLParticular.length>0) { - id = paramXMLParticular[0]; - imageId = paramXMLParticular[1]; - } - addRows(strText,id); - showHideLayer(id, imageId); + if (paramXMLParticular!=null && paramXMLParticular.length>0) { + id = paramXMLParticular[0]; + imageId = paramXMLParticular[1]; + } + addRows(strText,id, true, "0"); + var obj = getReference(imageId); + if (obj!=null) { + if (obj.className == "datawarehouseopen") { + obj.className = "datawarehouseclose"; + } else { + obj.className = "datawarehouseopen"; + } + } } return true; } @@ -134,49 +141,78 @@ var obj = getReference(imageId); if (obj!=null) { if (obj.className == "datawarehouseopen") { - obj.className = "datawarehouseclose"; - showHideRows(id, "none"); - } else { - obj.className = "datawarehouseopen"; - showHideRows(id, ""); - } + obj.className = "datawarehouseclose"; + showHideRows(id, "none"); + } else { + obj.className = "datawarehouseopen"; + showHideRows(id, ""); + } } } -function addRows(htmlText, id) { +function addRows(htmlText, id, byDefault, startRow) { var jsonTable = eval(htmlText); if (typeof jsonTable != undefined) { - for (i=0; i<jsonTable.rows.length; i++) { - var node=document.getElementById('funcEvenOddRow1'+id); - var elem=document.createElement('TR'); - node.parentNode.insertBefore(elem,node.nextSibling); - elem.id = "funcEvenOddRow2"+ id +"_"+(i+1); - var newTD1 = document.createElement('TD'); - newTD1.innerHTML = jsonTable.rows[i].td1 - newTD1.className = jsonTable.config.classDefault; - newTD1.colSpan = "2"; - var newTD2 = document.createElement('TD'); - newTD2.innerHTML = jsonTable.rows[i].td2; - newTD2.className = jsonTable.config.classAmount; - var newTD3 = document.createElement('TD'); - newTD3.innerHTML = jsonTable.rows[i].td3; - newTD3.className = jsonTable.config.classAmount; - var newTD4 = document.createElement('TD'); - newTD4.innerHTML = jsonTable.rows[i].td4; - newTD4.className = jsonTable.config.classAmount; - var newTD5 = document.createElement('TD'); - newTD5.innerHTML = jsonTable.rows[i].td5; - newTD5.className = jsonTable.config.classAmount; - var newTD6 = document.createElement('TD'); - newTD6.innerHTML = ""; - newTD6.className = jsonTable.config.classDefault; - elem.appendChild(newTD1); - elem.appendChild(newTD2); - elem.appendChild(newTD3); - elem.appendChild(newTD4); - elem.appendChild(newTD5); - elem.appendChild(newTD6); - } + var node; + var range = Number(jsonTable.config.range); + var totalRecords = Number(jsonTable.config.totalRecords); + var intStartRow = Number(startRow); + if (byDefault) { + node = document.getElementById('funcEvenOddRow1'+id); + } else { + node = document.getElementById('more__'+id+"__"+startRow); + node.style.display = "none"; + } + if (jsonTable.rows != undefined) { + if (jsonTable.rows.length > 0 && totalRecords>=range && range!=0) { + var next = Number(startRow) + Number(totalRecords); + var elem=document.createElement('TR'); + node.parentNode.insertBefore(elem,node.nextSibling); + elem.id="more__"+id+"__"+next; + var newTD7 = document.createElement('TD'); + newTD7.className = jsonTable.config.classDefault; + newTD7.colSpan = "7"; + var a = document.createElement('a'); + a.innerHTML = "..."; + a.href="#"; + a.id=id+"__"+next; + a.onclick = function(){moreLines(this.id);return false;}; + newTD7.appendChild(a); + elem.appendChild(newTD7); + } + var cont = intStartRow; + for (i=0; i<jsonTable.rows.length; i++) { + var elem=document.createElement('TR'); + node.parentNode.insertBefore(elem,node.nextSibling); + elem.id = "funcEvenOddRow2"+ id +"_"+(cont+1); + var newTD1 = document.createElement('TD'); + newTD1.innerHTML = jsonTable.rows[i].td1 + newTD1.className = jsonTable.config.classDefault; + newTD1.colSpan = "2"; + var newTD2 = document.createElement('TD'); + newTD2.innerHTML = jsonTable.rows[i].td2; + newTD2.className = jsonTable.config.classAmount; + var newTD3 = document.createElement('TD'); + newTD3.innerHTML = jsonTable.rows[i].td3; + newTD3.className = jsonTable.config.classAmount; + var newTD4 = document.createElement('TD'); + newTD4.innerHTML = jsonTable.rows[i].td4; + newTD4.className = jsonTable.config.classAmount; + var newTD5 = document.createElement('TD'); + newTD5.innerHTML = jsonTable.rows[i].td5; + newTD5.className = jsonTable.config.classAmount; + var newTD6 = document.createElement('TD'); + newTD6.innerHTML = ""; + newTD6.className = jsonTable.config.classDefault; + elem.appendChild(newTD1); + elem.appendChild(newTD2); + elem.appendChild(newTD3); + elem.appendChild(newTD4); + elem.appendChild(newTD5); + elem.appendChild(newTD6); + cont = cont + 1; + } + } } } @@ -186,14 +222,19 @@ var txt = ''; while (fila != undefined) { i++; - fila.style.display = showHide; - fila = document.getElementById('funcEvenOddRow2'+id+'_'+i); + fila.style.display = showHide; + fila = document.getElementById('funcEvenOddRow2'+id+'_'+i); + var moreFila = document.getElementById("more__"+id+"__"+i); + if (moreFila != null) { + moreFila.style.display = showHide; + } } } function updateData(CommandValue, identifier) { var frm = document.frmMain; frm.inpcAccountId.value = identifier; + frm.initRecord.value = "0"; var obj = getReference("buttonTree" + identifier); if (obj!=null && obj.className == "datawarehouseclose") { var paramXMLReq = new Array(identifier, 'buttonTree' + identifier); @@ -203,6 +244,36 @@ } } +function moreLines(identifier) { + var frm = document.frmMain; + var x = identifier.split("__"); + frm.inpcAccountId.value = x[0]; + frm.initRecord.value = x[1]; + try { + var paramXMLReq = new Array(x[0], x[1]); + return submitXmlHttpRequest(callbackMore, frm, "OPEN", "ReportTrialBalance.html", false, null, paramXMLReq); + } catch (e) { + alert(e); + } +} + +function callbackMore(paramXMLParticular, XMLHttpRequestObj) { + var strText = ""; + var id = ""; + var numRows = ""; + if (getReadyStateHandler(XMLHttpRequestObj)) { + try { + if (XMLHttpRequestObj.responseText) strText = XMLHttpRequestObj.responseText; + } catch (e) {} + if (paramXMLParticular!=null && paramXMLParticular.length>0) { + id = paramXMLParticular[0]; + numRows = paramXMLParticular[1]; + } + addRows(strText, id, false, numRows); + } + return true; +} + function showHideFilters(identifier) { var obj = getReference(identifier); if (obj!=null) { @@ -222,43 +293,52 @@ </script> <script language="JavaScript" type="text/javascript"> - function onLoadDo(){ - this.windowTables = new Array( - new windowTableId('client', 'buttonHTML') - ); - setWindowTableParentElement(); - this.tabsTables = new Array( - new tabTableId('tdtopTabs') - ); - setTabTableParentElement(); - enableShortcuts('edition'); - setBrowserAutoComplete(false); + function onLoadDo(){ + this.windowTables = new Array( + new windowTableId('client', 'buttonHTML') + ); + setWindowTableParentElement(); + this.tabsTables = new Array( + new tabTableId('tdtopTabs') + ); + setTabTableParentElement(); + enableShortcuts('edition'); + setBrowserAutoComplete(false); - var selectedGroupBy = document.getElementById("paramSelectedGroupBy").value; - var groupby = document.getElementById("inpGroupBy"); - for (i=0;i<groupby.length;i++){ - if (groupby[i].value == selectedGroupBy) - groupby[i].selected = true; - } + var selectedGroupBy = document.getElementById("paramSelectedGroupBy").value; + var groupby = document.getElementById("inpGroupBy"); + for (i=0;i<groupby.length;i++){ + if (groupby[i].value == selectedGroupBy) + groupby[i].selected = true; + } - try { - onloadFunctions(); - } catch (e) {} - resizeArea(); - updateMenuIcon('buttonMenu'); + try { + onloadFunctions(); + } catch (e) {} + resizeArea(); + updateMenuIcon('buttonMenu'); - setWindowElementFocus('firstElement'); - } + setWindowElementFocus('firstElement'); + } - function onResizeDo(){ - resizeArea(); - } + function onResizeDo(){ + resizeArea(); + } + function submitCommandWithMarkChecked(elementValue){ + var frm = document.frmMain; + markCheckedAllElements(frm.inpcBPartnerId_IN); + markCheckedAllElements(frm.inpcProjectId_IN); + markCheckedAllElements(frm.inpmProductId_IN); + submitCommandFormParameter('FIND', frm.inpcElementValueIdFrom, elementValue,false, frm, 'ReportGeneralLedger.html', '_self', false, true); + } + </script> </head> <body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" onload="onLoadDo();" onresize="onResizeDo();"> <form method="post" action="ReportTrialBalance.html" name="frmMain" target="_self"> <input type="hidden" name="Command"></input> <input type="hidden" name="inpcAccountId"></input> + <input type="hidden" name="initRecord" value="0"></input> <table height="100%" border="0" cellpadding="0" cellspacing="0" id="main"> <tr> <td valign="top" id="tdleftTabs"></td> @@ -427,7 +507,7 @@ </tr> </table> </td> - <td class="TitleCell"></td> + <td class="TitleCell"></td> <td class="TitleCell"><span class="LabelText">To Date</span></td> <td class="TextBox_btn_ContentCell"> <table border="0" cellspacing="0" cellpadding="0" summary="" style="padding-top: 0px;"> @@ -453,21 +533,20 @@ <td class="TitleCell"/> </tr> <tr> + <td class="TitleCell"><span class="LabelText">Organization</span></td> + <td class="Combo_ContentCell"> + <select name="inpOrg" id="inpOrg" class="ComboKey Combo_TwoCells_width" required="true"> + <option value=""> <div id="reportAD_ORGID"></div></option> + </select></td> + </tr> + <tr> <td class="TitleCell"> <span class="LabelText">Accounting schema</span></td> <td class="Combo_ContentCell" colspan="2"> <select name="inpcAcctSchemaId" id="inpcAcctSchemaId" class="ComboKey Combo_TwoCells_width" required="true"> <option value=""> <div id="reportC_ACCTSCHEMA_ID"></div></option> </select> </td> - </tr> - <tr> - <td class="TitleCell"><span class="LabelText">Organization</span></td> - <td class="Combo_ContentCell"> - <select name="inpOrg" id="inpOrg" class="ComboKey Combo_OneCell_width" required="true"> - <option value=""> <div id="reportAD_ORGID"></div></option> - </select></td> - <td class="TitleCell"></td> - <td class="TitleCell"><span class="LabelText">Account Level</span></td> - <td class="Combo_ContentCell"> + <td class="TitleCell"><span class="LabelText">Account Level</span></td> + <td class="Combo_ContentCell"> <select name="inpLevel" id="inpLevel" class="ComboKey Combo_OneCell_width" required="true" onchange="displayLogic(); return true;"> <option value=""> <div id="reportLevel"></div></option> </select></td> @@ -519,7 +598,7 @@ </table> </td> </tr> - + <tr id="Filter1"> <td colspan="6"> <table class="FieldGroup" cellspacing="0" cellpadding="0" border="0"> @@ -541,7 +620,7 @@ </table> </td> </tr> - + <tr id="Filter2"> <td class="TitleCell"><span class="LabelText">Business Partner</span></td> <td class="List_ContentCell" colspan="2"> @@ -630,7 +709,7 @@ </tr> </table> </td> - +  <td class="TitleCell"><span class="LabelText">Product</span></td> <td class="List_ContentCell" colspan="2"> @@ -721,7 +800,7 @@ </table> </td> </tr> - +  <tr id="Filter3"> <td class="TitleCell"><span class="LabelText">Project</span></td> @@ -813,12 +892,12 @@ </tr> </table> - </td> + </td> <td class="ContentCell"></td> <td class="ContentCell"></td> <td class="ContentCell"></td> </tr> - +  <tr id="Filter4"> <td class="TitleCell"><span class="LabelText">Group By</span></td> @@ -835,7 +914,7 @@ <td class="ContentCell"></td> <td class="ContentCell"></td> </tr> - + <tr> <td colspan="6"> <table class="FieldGroup" cellspacing="0" cellpadding="0" border="0"> @@ -857,18 +936,18 @@ <td class="TitleCell"></td> <td class="Button_CenterAlign_ContentCell"> <div> - <button type="button" - id="buttonHTML" - class="ButtonLink" - onclick="submitCommandForm('FIND', true, frmMain, 'ReportTrialBalance.html', '_self');return false;" - onfocus="buttonEvent('onfocus', this); window.status='View Results in a New Window'; return true;" - onblur="buttonEvent('onblur', this);" - onkeyup="buttonEvent('onkeyup', this);" - onkeydown="buttonEvent('onkeydown', this);" - onkeypress="buttonEvent('onkeypress', this);" - onmouseup="buttonEvent('onmouseup', this);" - onmousedown="buttonEvent('onmousedown', this);" - onmouseover="buttonEvent('onmouseover', this); window.status='View Results in a New Window'; return true;" + <button type="button" + id="buttonHTML" + class="ButtonLink" + onclick="submitCommandForm('FIND', true, frmMain, 'ReportTrialBalance.html', '_self');return false;" + onfocus="buttonEvent('onfocus', this); window.status='View Results in a New Window'; return true;" + onblur="buttonEvent('onblur', this);" + onkeyup="buttonEvent('onkeyup', this);" + onkeydown="buttonEvent('onkeydown', this);" + onkeypress="buttonEvent('onkeypress', this);" + onmouseup="buttonEvent('onmouseup', this);" + onmousedown="buttonEvent('onmousedown', this);" + onmouseover="buttonEvent('onmouseover', this); window.status='View Results in a New Window'; return true;" onmouseout="buttonEvent('onmouseout', this);"> <table class="Button"> <tr> @@ -889,7 +968,7 @@ <tr> </table> - +  <table class="Main_Client_TableEdition"> <tr> @@ -898,8 +977,8 @@  <div id="sectionGridView"> <table cellspacing="0" cellpadding="0" width="100%" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;"> - <tr class="DataGrid_Body_Row"> - <th height="17" colspan="7" class="DataGrid_Header_Cell">Balance</th> + <tr class="DataGrid_Body_Row"> + <th height="17" colspan="7" class="DataGrid_Header_Cell">Balance</th> </tr> <tr class="DataGrid_Body_Row"> <th width="90" class="DataGrid_Header_Cell">ACCOUNT CODE</th> @@ -914,10 +993,10 @@ <div id="sectionDetail"> <tr class="DataGrid_Body_Row DataGrid_Body_Row_yy" id="funcEvenOddRow1xx"> <td width="90" class="DataGrid_Body_Cell"> - <span id="showExpand" style="display: table-cell; display: -moz-inline-box;"><a href="#" onclick="updateData('OPEN', 'hhqq');return false;" onMouseOver="window.status='Open';return true;" onMouseOut="window.status='';return true;" id="expandButton"> + <span id="showExpand" style="display: table-cell; display: -moz-inline-box;"><a href="#" onclick="updateData('OPEN', 'hhqq', '0');return false;" onMouseOver="window.status='Open';return true;" onMouseOut="window.status='';return true;" id="expandButton"> <span id="buttonTreemmm" class="datawarehouseclose"></span> </a></span> - <a href="#" onclick="submitCommandFormParameter('FIND', document.frmMain.inpcElementValueIdFrom, 'xx', false, document.frmMain, 'ReportGeneralLedger.html', '_self', false, true);return false;" onmouseover="window.status='General Ledger';return true;" onmouseout="window.status='';return true;" class="LabelLink" id="fieldId1"> + <a href="#" onclick="submitCommandWithMarkChecked('xx');return false;" onmouseover="window.status='General Ledger';return true;" onmouseout="window.status='';return true;" class="LabelLink" id="fieldId1"> <span id="fieldAccount">xx70000</span> </a> <span id="fieldDescAccount">xx600</span> @@ -932,7 +1011,7 @@ </td> </tr> </div> - <tr class="DataGrid_Body_Row"> + <tr class="DataGrid_Body_Row"> <th colspan="2" class="DataGrid_Header_Cell">ADD AND KEEP GOING...</th> <th width="105" id="fieldTotalSaldoInicial" class="DataGrid_Header_Cell_Amount">xx14500.34</th> <th width="105" id="fieldTotalAmtacctdr" class="DataGrid_Header_Cell_Amount">xx14500.34</th> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Mon Sep 20 16:31:57 2010 +0530 @@ -4,14 +4,14 @@ * Version 1.0 (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 + * 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) 2001-2009 Openbravo SLU + * 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-2010 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -55,6 +55,7 @@ public class ReportTrialBalance extends HttpSecureAppServlet { private static final long serialVersionUID = 1L; + private static final int MAX_XLS_ROWS = 64500; public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { @@ -185,8 +186,12 @@ "ReportTrialBalance|cProjectId", "", IsIDFilter.instance); String strGroupBy = vars.getRequestGlobalVariable("inpGroupBy", "ReportTrialBalance|GroupBy"); + String strInitRecord = vars.getRequiredStringParameter("initRecord"); + String strRecordRange = Utility.getContext(this, vars, "#RecordRange", "ReportTrialBalance"); + printPageOpen(response, vars, strDateFrom, strDateTo, strOrg, strLevel, strcBpartnerId, - strmProductId, strcProjectId, strcAcctSchemaId, strGroupBy, strAccountId); + strmProductId, strcProjectId, strcAcctSchemaId, strGroupBy, strAccountId, strInitRecord, + strRecordRange); } else { pageError(response); @@ -196,20 +201,38 @@ private void printPageOpen(HttpServletResponse response, VariablesSecureApp vars, String strDateFrom, String strDateTo, String strOrg, String strLevel, String strcBpartnerId, String strmProductId, String strcProjectId, String strcAcctSchemaId, String strGroupBy, - String strAccountId) throws IOException, ServletException { + String strAccountId, String strInitRecord, String strRecordRange) throws IOException, + ServletException { ReportTrialBalanceData[] data = null; String strTreeOrg = TreeData.getTreeOrg(this, vars.getClient()); String strOrgFamily = getFamily(strTreeOrg, strOrg); + // built limit/offset parameters for oracle/postgres + String rowNum = "0"; + String oraLimit1 = null; + String oraLimit2 = null; + String pgLimit = null; + int intRecordRange = (strRecordRange.equals("0") ? 0 : Integer.parseInt(strRecordRange)); + int intInitRecord = (strInitRecord.equals("") ? 0 : Integer.parseInt(strInitRecord)); + if (intRecordRange != 0) { + if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) { + rowNum = "ROWNUM"; + oraLimit1 = String.valueOf(intInitRecord + intRecordRange); + oraLimit2 = (intInitRecord + 1) + " AND " + oraLimit1; + } else { + rowNum = "0"; + pgLimit = intRecordRange + " OFFSET " + intInitRecord; + } + } + log4j.debug("Output: Expand subaccount details " + strAccountId); - data = ReportTrialBalanceData.selectAccountLines(this, strGroupBy, vars.getLanguage(), - strLevel, strOrgFamily, Utility - .getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility.getContext(this, - vars, "#AccessibleOrgTree", "ReportTrialBalance"), null, null, strDateFrom, - strAccountId, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData - .nDaysAfter(this, strDateTo, "1")); + data = ReportTrialBalanceData.selectAccountLines(this, rowNum, strGroupBy, strLevel, + strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), null, null, + strDateFrom, strAccountId, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, + DateTimeData.nDaysAfter(this, strDateTo, "1"), oraLimit1, oraLimit2, pgLimit); if (data == null) { data = ReportTrialBalanceData.set(); @@ -244,6 +267,8 @@ Map<String, String> props = new HashMap<String, String>(); props.put("classAmount", "DataGrid_Body_Cell_Amount"); props.put("classDefault", "DataGrid_Body_Cell"); + props.put("totalRecords", String.valueOf(data.length)); + props.put("range", String.valueOf(intRecordRange)); table.put("config", props); } catch (JSONException e) { @@ -306,12 +331,12 @@ } } else { if (strLevel.equals("S")) { // SubAccount selected - data = ReportTrialBalanceData.selectAccountLines(this, "", vars.getLanguage(), strLevel, - strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), - Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), + data = ReportTrialBalanceData.selectAccountLines(this, "0", "", strLevel, strOrgFamily, + Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), strAccountFromValue, strAccountToValue, strDateFrom, null, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, - strDateTo, "1")); + strDateTo, "1"), null, null, null); if (strGroupBy.equals("")) discard[2] = "showExpand"; @@ -454,50 +479,58 @@ + strTreeAccount); log4j.debug("strcBpartnerId: " + strcBpartnerId + "strmProductId: " + strmProductId + "strcProjectId: " + strcProjectId); + try { + if (!strDateFrom.equals("") && !strDateTo.equals("") && !strOrg.equals("") + && !strcAcctSchemaId.equals("")) { - if (!strDateFrom.equals("") && !strDateTo.equals("") && !strOrg.equals("") - && !strcAcctSchemaId.equals("")) { + if (strLevel.equals("S")) { + data = ReportTrialBalanceData.selectXLS(this, strLevel, strOrgFamily, Utility.getContext( + this, vars, "#User_Client", "ReportTrialBalance"), Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportTrialBalance"), strAccountFromValue, strAccountToValue, + strDateFrom, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, + DateTimeData.nDaysAfter(this, strDateTo, "1")); + showDimensions = true; + } else { + data = getDataWhenNotSubAccount(vars, strDateFrom, strDateTo, strOrg, strOrgFamily, + strcAcctSchemaId, strLevel, strTreeAccount); + } - if (strLevel.equals("S")) { - data = ReportTrialBalanceData.selectXLS(this, vars.getLanguage(), strLevel, strOrgFamily, - Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility - .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), - strAccountFromValue, strAccountToValue, strDateFrom, strcBpartnerId, strmProductId, - strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, strDateTo, "1")); - showDimensions = true; + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); + } else if (data.length > MAX_XLS_ROWS) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); + } else { + + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml"; + + HashMap<String, Object> parameters = new HashMap<String, Object>(); + + String strLanguage = vars.getLanguage(); + + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg) + ")"); + parameters.put("Title", classInfo.name); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + parameters.put("SHOWTOTALS", false); + parameters.put("SHOWDIMENSIONS", showDimensions); + + renderJR(vars, response, strReportName, "xls", parameters, data, null); + } } else { - data = getDataWhenNotSubAccount(vars, strDateFrom, strDateTo, strOrg, strOrgFamily, - strcAcctSchemaId, strLevel, strTreeAccount); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } + } catch (Exception e) { + log4j.error("Error creating the XLS report in TrialBalanceReport"); - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { - - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml"; - - HashMap<String, Object> parameters = new HashMap<String, Object>(); - - String strLanguage = vars.getLanguage(); - - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - parameters.put("SHOWTOTALS", false); - parameters.put("SHOWDIMENSIONS", showDimensions); - - renderJR(vars, response, strReportName, "xls", parameters, data, null); - } - } else { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } - } private void printPageDataPDF(HttpServletRequest request, HttpServletResponse response, @@ -528,12 +561,12 @@ && !strcAcctSchemaId.equals("")) { if (strLevel.equals("S")) { - data = ReportTrialBalanceData.selectAccountLines(this, strGroupBy, vars.getLanguage(), - strLevel, strOrgFamily, Utility.getContext(this, vars, "#User_Client", - "ReportTrialBalance"), Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportTrialBalance"), strAccountFromValue, strAccountToValue, strDateFrom, null, - strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData - .nDaysAfter(this, strDateTo, "1")); + data = ReportTrialBalanceData.selectAccountLines(this, "0", strGroupBy, strLevel, + strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), + strAccountFromValue, strAccountToValue, strDateFrom, null, strcBpartnerId, + strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, + strDateTo, "1"), null, null, null); if (!strGroupBy.equals("")) strIsSubAccount = true; @@ -543,21 +576,21 @@ } if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } else { String strLanguage = vars.getLanguage(); String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalancePDF.jrxml"; HashMap<String, Object> parameters = new HashMap<String, Object>(); - parameters.put("Title", classInfo.name); parameters.put("TOTAL", Utility.messageBD(this, "Total", strLanguage)); StringBuilder strSubTitle = new StringBuilder(); strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + "\n"); strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg)); + parameters.put("Title", classInfo.name); parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); parameters.put("DEFAULTVIEW", !strIsSubAccount); @@ -568,8 +601,8 @@ } } else { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } } diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql Mon Sep 20 16:31:57 2010 +0530 @@ -5,15 +5,15 @@ * Version 1.0 (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 + * 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. + * 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-2009 Openbravo SLU - * All Rights Reserved. + * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ --> @@ -24,14 +24,15 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT MAX(PARENT_ID) AS PARENT_ID, ID, NAME, ACCOUNT_ID, ELEMENTLEVEL, SUM(AMTACCTDR) AS AMTACCTDR, + SELECT MAX(PARENT_ID) AS PARENT_ID, ID, NAME, ACCOUNT_ID, ELEMENTLEVEL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, 0 AS SALDO_INICIAL,0 AS TOTALAMTACCTDR, 0 AS TOTALAMTACCTCR, 0 AS SALDO_FINAL, TO_DATE(?) AS DATE_FROM, TO_DATE(?) AS DATE_TO, ? AS AD_ORG_ID, '' AS BP, '' AS PADRE, '' AS groupbyname, '' AS groupbyid, '' AS groupbytext, - '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname + '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname, + '' as RN1 FROM - (SELECT AD_TREENODE.PARENT_ID, C_ELEMENTVALUE.C_ELEMENTVALUE_ID AS ID, C_ELEMENTVALUE.ELEMENTLEVEL, - C_ELEMENTVALUE.NAME AS NAME, C_ELEMENTVALUE.VALUE AS ACCOUNT_ID, 0 AS AMTACCTDR, + (SELECT AD_TREENODE.PARENT_ID, C_ELEMENTVALUE.C_ELEMENTVALUE_ID AS ID, C_ELEMENTVALUE.ELEMENTLEVEL, + C_ELEMENTVALUE.NAME AS NAME, C_ELEMENTVALUE.VALUE AS ACCOUNT_ID, 0 AS AMTACCTDR, 0 AS AMTACCTCR FROM AD_TREENODE, C_ELEMENTVALUE WHERE AD_TREENODE.NODE_ID = C_ELEMENTVALUE.C_ELEMENTVALUE_ID @@ -39,10 +40,10 @@ AND AD_TREENODE.ISACTIVE = 'Y' AND C_ELEMENTVALUE.ISACTIVE = 'Y' AND (select max(c_element_id) from c_acctschema_element where c_acctschema_id = ? and ELEMENTTYPE = 'AC') = C_ELEMENTVALUE.C_ELEMENT_ID - UNION - SELECT '0' AS PARENT_ID, F.ACCOUNT_ID AS ID, EV.ELEMENTLEVEL, EV.NAME AS NAME, EV.VALUE AS ACCOUNT_ID, - SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE F.AMTACCTDR END)) AS AMTACCTDR, - SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE f.AMTACCTCR END)) AS AMTACCTCR + UNION + SELECT '0' AS PARENT_ID, F.ACCOUNT_ID AS ID, EV.ELEMENTLEVEL, EV.NAME AS NAME, EV.VALUE AS ACCOUNT_ID, + SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE F.AMTACCTDR END)) AS AMTACCTDR, + SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE f.AMTACCTCR END)) AS AMTACCTCR FROM FACT_ACCT F, C_ELEMENTVALUE EV WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND f.AD_ORG_ID IN('2') @@ -61,7 +62,7 @@ <Parameter name="dateTo"/> <Parameter name="org"/> <Parameter name="treeAcct"/> - <Parameter name="acctSchema"/> + <Parameter name="acctSchema"/> <Parameter name="orgFamily" type="replace" optional="true" after="f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="F.AD_CLIENT_ID IN (" text="'1'"/> <Parameter name="adUserOrg" type="replace" optional="true" after="F.AD_ORG_ID IN(" text="'1'"/> @@ -69,9 +70,9 @@ <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[AND F.DATEACCT < TO_DATE(?)]]></Parameter> <Parameter name="accountFrom" optional="true" after="AND 1=1"><![CDATA[AND EV.VALUE >= ?]]></Parameter> <Parameter name="accountTo" optional="true" after="AND 1=1"><![CDATA[AND EV.VALUE <= ?]]></Parameter> - <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> + <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> </SqlMethod> - + <SqlMethod name="set" type="constant" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql></Sql> @@ -81,52 +82,35 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT ID, ACCOUNT_ID, NAME, + SELECT * + FROM ( SELECT '0' AS RN1, D.*, + CASE ? + WHEN 'BPartner' THEN TO_CHAR(bp.name) + WHEN 'Product' THEN TO_CHAR(pd.name) + WHEN 'Project' THEN TO_CHAR(pj.name) + ELSE '' + END AS groupbyname + FROM ( + SELECT ID, ACCOUNT_ID, C.NAME AS NAME, SUM(SALDO_INICIAL) AS SALDO_INICIAL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, SUM(SALDO_INICIAL+AMTACCTDR-AMTACCTCR) AS SALDO_FINAL, - groupbyid, - CASE ? - WHEN 'BPartner' THEN - CASE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - WHEN 'Product' THEN - CASE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - WHEN 'Project' THEN - CASE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - ELSE '' - END AS groupbyname + groupbyid FROM ((SELECT ID, ACCOUNT_ID, NAME, - 0 AS AMTACCTDR, 0 AS AMTACCTCR, + 0 AS AMTACCTDR, 0 AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL, - groupbyname, groupbyid + groupbyid FROM ((SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -138,19 +122,11 @@ (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -159,29 +135,21 @@ AND 3=3 AND F.ISACTIVE = 'Y' AND F.FACTACCTTYPE = 'O')) A - GROUP BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + GROUP BY ACCOUNT_ID, ID, NAME, groupbyid HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 ) UNION (SELECT ID, ACCOUNT_ID, NAME, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - 0 AS SALDO_INICIAL, groupbyname, groupbyid + 0 AS SALDO_INICIAL, groupbyid FROM (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -192,19 +160,19 @@ AND F.FACTACCTTYPE <> 'C' AND F.FACTACCTTYPE <> 'O' AND F.ISACTIVE = 'Y') B - GROUP BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + GROUP BY ACCOUNT_ID, ID, NAME, groupbyid HAVING SUM(AMTACCTDR) <> 0 OR SUM(AMTACCTCR) <> 0 )) C - GROUP BY ACCOUNT_ID, ID, groupbyid, groupbyname, NAME - ORDER BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + WHERE 4=4 + GROUP BY ACCOUNT_ID, ID, C.NAME, groupbyid ) D + LEFT JOIN C_BPARTNER bp ON groupbyid = bp.C_BPARTNER_ID + LEFT JOIN M_PRODUCT pd ON groupbyid = pd.M_PRODUCT_ID + LEFT JOIN C_PROJECT pj ON groupbyid = pj.C_PROJECT_ID + ORDER BY ACCOUNT_ID, ID, D.NAME, groupbyname, groupbyid + ) E + WHERE 5=5 AND 6=6 ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="groupby"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> + <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" /> <Parameter name="groupby"/> <Parameter name="groupby"/> <Parameter name="accountLevel"/> @@ -221,7 +189,6 @@ <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter>  <Parameter name="groupby"/> - <Parameter name="groupby"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after=" f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after=" F.AD_CLIENT_ID IN (" text="'1'"/> @@ -236,7 +203,6 @@ <Parameter name="acctSchema" optional="true" after="AND 3=3"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter>  <Parameter name="groupby"/> - <Parameter name="groupby"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="AND F.AD_CLIENT_ID IN (" text="'1'"/> @@ -250,44 +216,34 @@ <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.M_PRODUCT_ID IN]]></Parameter> <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.C_PROJECT_ID IN]]></Parameter> <Parameter name="acctSchema" optional="true" after="AND 2=2"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="WHERE 4=4"><![CDATA[ AND ROWNUM <= ]]></Parameter> + <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 5=5"><![CDATA[ AND RN1 BETWEEN ]]></Parameter> + <Parameter name="pgLimit" type="argument" optional="true" after="6=6"><![CDATA[ LIMIT ]]></Parameter> </SqlMethod> - + <SqlMethod name="selectXLS" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT ID, ACCOUNT_ID, NAME, + SELECT ID, ACCOUNT_ID, C.NAME AS NAME, SUM(SALDO_INICIAL) AS SALDO_INICIAL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, SUM(SALDO_INICIAL+AMTACCTDR-AMTACCTCR) AS SALDO_FINAL, bpid, pdid, pjid, - CASE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(bpid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(bpid), TO_CHAR(?)) - END AS bpname, - CASE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(pdid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(pdid), TO_CHAR(?)) - END AS pdname, - CASE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(pjid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(pjid), TO_CHAR(?)) - END AS pjname + c_bpartner.name AS bpname, + m_product.name AS pdname, + c_project.name AS pjname FROM ((SELECT ID, ACCOUNT_ID, NAME, - 0 AS AMTACCTDR, 0 AS AMTACCTCR, + 0 AS AMTACCTDR, 0 AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL, - bpid, pdid, pjid, bpname, pdname + bpid, pdid, pjid FROM ((SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -298,12 +254,8 @@ UNION (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -312,22 +264,18 @@ AND 3=3 AND F.ISACTIVE = 'Y' AND F.FACTACCTTYPE = 'O')) A - GROUP BY ACCOUNT_ID, ID, bpid, pdid, pjid, bpname, pdname, NAME + GROUP BY ACCOUNT_ID, ID, NAME, bpid, pdid, pjid HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 ) UNION (SELECT ID, ACCOUNT_ID, NAME, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - 0 AS SALDO_INICIAL, bpid, pdid, pjid, bpname, pdname + 0 AS SALDO_INICIAL, bpid, pdid, pjid FROM (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -338,17 +286,14 @@ AND F.FACTACCTTYPE <> 'C' AND F.FACTACCTTYPE <> 'O' AND F.ISACTIVE = 'Y') B - GROUP BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid )) C - GROUP BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid - ORDER BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid + GROUP BY ACCOUNT_ID, ID, NAME, bpid, pdid, pjid )) C + LEFT JOIN C_BPARTNER ON bpid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON pdid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON pjid = C_PROJECT.C_PROJECT_ID + GROUP BY ACCOUNT_ID, ID, C.NAME, bpid, pdid, pjid, c_bpartner.name, m_product.name, c_project.name + ORDER BY ACCOUNT_ID, ID, C.NAME, bpname, bpid, pdname, pdid, pjname, pjid ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after="f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="F.AD_CLIENT_ID IN (" text="'1'"/> @@ -386,10 +331,10 @@ <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.C_PROJECT_ID IN]]></Parameter> <Parameter name="acctSchema" optional="true" after="AND 2=2"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> </SqlMethod> - + <SqlMethod name="selectInitialBalance" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> - <Sql><![CDATA[ + <Sql><![CDATA[ SELECT ACCOUNT_ID, COALESCE(SUM(AMTACCTDR),0) AS AMTACCTDR, COALESCE(SUM(AMTACCTCR),0) AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL FROM ( @@ -402,7 +347,7 @@ AND F.AD_ORG_ID IN ('1') AND F.AD_CLIENT_ID IN ('1') AND F.ISACTIVE = 'Y') - UNION + UNION ALL (SELECT F.ACCOUNT_ID AS ACCOUNT_ID, F.AMTACCTDR AS AMTACCTDR, F.AMTACCTCR AS AMTACCTCR, F.DATEACCT, F.FACTACCTTYPE FROM FACT_ACCT F @@ -433,7 +378,7 @@ <Parameter name="orgFamily" type="replace" optional="true" after="AND F.AD_ORG_ID IN (" text="'1'"/> <Parameter name="clientFamily" type="replace" optional="true" after="AND F.AD_CLIENT_ID IN (" text="'1'"/> </SqlMethod> - + <SqlMethod name="treeAccount" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql> @@ -463,7 +408,7 @@ </Sql> <Parameter name="organization"/> </SqlMethod> - + <SqlMethod name="selectAcctSchemaName" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql> @@ -473,7 +418,7 @@ </Sql> <Parameter name="acctSchema"/> </SqlMethod> - + <SqlMethod name="selectAccountingName" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ @@ -484,24 +429,24 @@ ]]></Sql> <Parameter name="cElementvalueId"/> </SqlMethod> - + <SqlMethod name="selectLastAccount" type="preparedStatement" return="string" default=""> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ - SELECT C_ELEMENTVALUE.C_ELEMENTVALUE_ID + SELECT C_ELEMENTVALUE.C_ELEMENTVALUE_ID FROM C_ELEMENTVALUE WHERE C_ELEMENTVALUE.VALUE = ( SELECT MAX(C_ELEMENTVALUE.VALUE) FROM C_ELEMENTVALUE - WHERE C_ELEMENTVALUE.AD_Org_ID IN('1') - AND C_ELEMENTVALUE.AD_Client_ID IN('1') + WHERE C_ELEMENTVALUE.AD_Org_ID IN('1') + AND C_ELEMENTVALUE.AD_Client_ID IN('1') AND C_ELEMENTVALUE.IsSummary='N' AND C_ELEMENTVALUE.ISACTIVE='Y') ]]></Sql> <Parameter name="adOrgClient" type="replace" optional="true" after="C_ELEMENTVALUE.AD_Org_ID IN(" text="'1'"/> <Parameter name="adUserClient" type="replace" optional="true" after="C_ELEMENTVALUE.AD_Client_ID IN(" text="'1'"/> </SqlMethod> - + <SqlMethod name="selectSubaccountDescription" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ @@ -511,7 +456,7 @@ ]]></Sql> <Parameter name="elementValueId"/> </SqlMethod> - + <SqlMethod name="selectAccountValue" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[

diff -r 17a70403b6d3 src-db/database/sourcedata/AD_MESSAGE.xml --- a/src-db/database/sourcedata/AD_MESSAGE.xml Wed Mar 31 12:07:41 2010 +0200 +++ b/src-db/database/sourcedata/AD_MESSAGE.xml Tue Sep 28 10:24:54 2010 +0530 @@ -30760,6 +30760,17 @@  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_MESSAGE> +<AD_MESSAGE> + <AD_MESSAGE_ID><![CDATA[534CDD9CFDC2472291C939D898221A85]]></AD_MESSAGE_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <VALUE><![CDATA[ReportsLimitBody]]></VALUE> + <MSGTEXT><![CDATA[This report cannot be rendered because the filtering criteria you have specified return a number of rows that exceeds the maximum allowed. The limit of rows to display in a report is @limit@, and your criteria resulted in an estimated number of @rows@ rows. Please, use more selective filtering criteria and execute the report again.]]></MSGTEXT> + <MSGTYPE><![CDATA[I]]></MSGTYPE> + <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +</AD_MESSAGE> + <AD_MESSAGE>  <AD_MESSAGE_ID><![CDATA[548069F5D9E04ECF80B3EA2EA7218036]]></AD_MESSAGE_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -31072,6 +31083,17 @@  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_MESSAGE> +<AD_MESSAGE> + <AD_MESSAGE_ID><![CDATA[652772F68182407FAEEE9F89CB87E53F]]></AD_MESSAGE_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <VALUE><![CDATA[ReportsLimitHeader]]></VALUE> + <MSGTEXT><![CDATA[Report records limit reached]]></MSGTEXT> + <MSGTYPE><![CDATA[E]]></MSGTYPE> + <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +</AD_MESSAGE> + <AD_MESSAGE>  <AD_MESSAGE_ID><![CDATA[6775556A78264AB192CAD83F25E37E7B]]></AD_MESSAGE_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> @@ -31827,6 +31849,17 @@  <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> </AD_MESSAGE> +<AD_MESSAGE> + <AD_MESSAGE_ID><![CDATA[AC594E632F5C4970A881AAEC763BC705]]></AD_MESSAGE_ID> + <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> + <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID> + <ISACTIVE><![CDATA[Y]]></ISACTIVE> + <VALUE><![CDATA[MAX_ROWS_LIMIT_EXCEED]]></VALUE> + <MSGTEXT><![CDATA[Maximum number of rows exceeded. Please modify filtering criteria.]]></MSGTEXT> + <MSGTYPE><![CDATA[W]]></MSGTYPE> + <AD_MODULE_ID><![CDATA[0]]></AD_MODULE_ID> +</AD_MESSAGE> + <AD_MESSAGE>  <AD_MESSAGE_ID><![CDATA[AC63454AE63A49CDA0EB25806B656B6D]]></AD_MESSAGE_ID>  <AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java --- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java Tue Sep 28 10:24:54 2010 +0530 @@ -46,6 +46,8 @@ public class ReportGeneralLedger extends HttpSecureAppServlet { private static final long serialVersionUID = 1L; + private static final int MAX_XLS_ROWS = 64500; + private static final int PDF_REPORT_LIMIT = 0; public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { @@ -292,13 +294,13 @@ strcelementvaluetodes = ""; vars.setSessionValue("inpElementValueIdTo_DES", strcelementvaluetodes); } - data = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, toDatePlusOne, - strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, - null, null, pgLimit, oraLimit1, oraLimit2); + data = ReportGeneralLedgerData.select(this, rowNum, strGroupBy, strGroupByText, strDateFrom, + toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, + strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, null, null, pgLimit, oraLimit1, + oraLimit2); if (log4j.isDebugEnabled()) log4j.debug("RecordNo: " + initRecordNumber); // In case this is not the first screen to show, initial balance may need to include amounts @@ -306,15 +308,14 @@ ReportGeneralLedgerData[] dataTotal = null; if (data != null && data.length > 1) { - dataTotal = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strYearInitialDate, DateTimeData - .nDaysAfter(this, data[0].dateacct, "1"), strOrgFamily, strcBpartnerId, - strmProductId, strcProjectId, strAmtFrom, strAmtTo, data[0].id, data[0].dateacctnumber - + data[0].factAcctGroupId + data[0].description + data[0].isdebit, - data[0].groupbyid, null, null, null); + dataTotal = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, + strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strYearInitialDate, DateTimeData.nDaysAfter(this, data[0].dateacct, + "1"), strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, + strAmtTo, data[0].id, data[0].dateacctnumber + data[0].factAcctGroupId + + data[0].description + data[0].isdebit, data[0].groupbyid, null, null, null); } // Now dataTotal is covered adding debit and credit amounts for (int i = 0; dataTotal != null && i < dataTotal.length; i++) { @@ -495,80 +496,116 @@ vars.getLanguage()) : (strGroupBy.equals("Project") ? Utility.messageBD(this, "Project", vars.getLanguage()) : ""))); String strAllaccounts = "Y"; + try { + if (!strDateFrom.equals("") && !strDateTo.equals("")) { + strOrgFamily = getFamily(strTreeOrg, strOrg); + if (!strHide.equals("Y")) + strHide = "N"; + if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { + if (strcelementvalueto.equals("")) + strcelementvalueto = strcelementvaluefrom; + strAllaccounts = "N"; + } + String rowNum = "0"; + if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) { + rowNum = "ROWNUM"; + } else { + rowNum = "0"; + } + int limit = 10; + int mycount = 0; + try { + // limit = Integer.parseInt(Utility.getPreference(vars, "ReportsLimit", "")); + limit = PDF_REPORT_LIMIT; + if (limit > 0) { + mycount = Integer.parseInt(ReportGeneralLedgerData.selectCount(this, rowNum, + strGroupByText, strGroupBy, strDateFrom, toDatePlusOne, strAllaccounts, + strcelementvaluefrom, strcelementvalueto, Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportGeneralLedger"), Utility.getContext(this, vars, + "#User_Client", "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, + toDatePlusOne, strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, + strAmtFrom, strAmtTo, null, null, null, null, null, null)); + } + } catch (NumberFormatException e) { - if (!strDateFrom.equals("") && !strDateTo.equals("")) { - strOrgFamily = getFamily(strTreeOrg, strOrg); - if (!strHide.equals("Y")) - strHide = "N"; - if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { - if (strcelementvalueto.equals("")) - strcelementvalueto = strcelementvaluefrom; - strAllaccounts = "N"; + } + if (limit > 0 && mycount > limit) { + String msgbody = Utility.messageBD(this, "ReportsLimitBody", vars.getLanguage()); + msgbody = msgbody.replace("@rows@", Integer.toString(mycount)); + msgbody = msgbody.replace("@limit@", Integer.toString(limit)); + advisePopUp(request, response, "ERROR", Utility.messageBD(this, "ReportsLimitHeader", + vars.getLanguage()), msgbody); + } else { + data = ReportGeneralLedgerData.select(this, rowNum, strGroupByText, strGroupBy, + strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility + .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, + strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, + strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, null, null, null, null, + null); + } } - data = ReportGeneralLedgerData.select(this, "0", strGroupByText, strGroupBy, vars - .getLanguage(), strDateFrom, toDatePlusOne, strAllaccounts, strcelementvaluefrom, - strcelementvalueto, Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportGeneralLedger"), Utility.getContext(this, vars, "#User_Client", - "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, toDatePlusOne, - strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, strAmtTo, null, - null, null, null, null, null); - } - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { - String strOld = ""; - BigDecimal totalDebit = BigDecimal.ZERO; - BigDecimal totalCredit = BigDecimal.ZERO; - BigDecimal subTotal = BigDecimal.ZERO; + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars + .getLanguage())); + } else { + String strOld = ""; + BigDecimal totalDebit = BigDecimal.ZERO; + BigDecimal totalCredit = BigDecimal.ZERO; + BigDecimal subTotal = BigDecimal.ZERO; - subreport = new ReportGeneralLedgerData[data.length]; - for (int i = 0; data != null && i < data.length; i++) { - if (!strOld.equals(data[i].groupbyid + data[i].id)) { - subreport = ReportGeneralLedgerData.selectTotal(this, strDateFrom, DateTimeData - .nDaysAfter(this, strDateTo, "1"), (strGroupBy.equals("BPartner") ? "('" - + data[i].groupbyid + "')" : strcBpartnerId), (strGroupBy.equals("Product") ? "('" - + data[i].groupbyid + "')" : strmProductId), (strGroupBy.equals("Project") ? "('" - + data[i].groupbyid + "')" : strcProjectId), strcAcctSchemaId, data[i].id, - strYearInitialDate, strDateFrom, strOrgFamily, strHide); - totalDebit = BigDecimal.ZERO; - totalCredit = BigDecimal.ZERO; - subTotal = BigDecimal.ZERO; + subreport = new ReportGeneralLedgerData[data.length]; + for (int i = 0; data != null && i < data.length; i++) { + if (!strOld.equals(data[i].groupbyid + data[i].id)) { + subreport = ReportGeneralLedgerData.selectTotal(this, strDateFrom, DateTimeData + .nDaysAfter(this, strDateTo, "1"), (strGroupBy.equals("BPartner") ? "('" + + data[i].groupbyid + "')" : strcBpartnerId), (strGroupBy.equals("Product") ? "('" + + data[i].groupbyid + "')" : strmProductId), (strGroupBy.equals("Project") ? "('" + + data[i].groupbyid + "')" : strcProjectId), strcAcctSchemaId, data[i].id, + strYearInitialDate, strDateFrom, strOrgFamily, strHide); + totalDebit = BigDecimal.ZERO; + totalCredit = BigDecimal.ZERO; + subTotal = BigDecimal.ZERO; + } + totalDebit = totalDebit.add(new BigDecimal(data[i].amtacctdr)); + data[i].totalacctdr = new BigDecimal(subreport[0].totalacctdr).add(totalDebit).toString(); + totalCredit = totalCredit.add(new BigDecimal(data[i].amtacctcr)); + data[i].totalacctcr = new BigDecimal(subreport[0].totalacctcr).add(totalCredit) + .toString(); + subTotal = subTotal.add(new BigDecimal(data[i].total)); + data[i].totalacctsub = new BigDecimal(subreport[0].total).add(subTotal).toString(); + data[i].previousdebit = subreport[0].totalacctdr; + data[i].previouscredit = subreport[0].totalacctcr; + data[i].previoustotal = subreport[0].total; + strOld = data[i].groupbyid + data[i].id; } - totalDebit = totalDebit.add(new BigDecimal(data[i].amtacctdr)); - data[i].totalacctdr = new BigDecimal(subreport[0].totalacctdr).add(totalDebit).toString(); - totalCredit = totalCredit.add(new BigDecimal(data[i].amtacctcr)); - data[i].totalacctcr = new BigDecimal(subreport[0].totalacctcr).add(totalCredit).toString(); - subTotal = subTotal.add(new BigDecimal(data[i].total)); - data[i].totalacctsub = new BigDecimal(subreport[0].total).add(subTotal).toString(); - data[i].previousdebit = subreport[0].totalacctdr; - data[i].previouscredit = subreport[0].totalacctcr; - data[i].previoustotal = subreport[0].total; - strOld = data[i].groupbyid + data[i].id; + + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.jrxml"; + response.setHeader("Content-disposition", "inline; filename=ReportGeneralLedgerPDF.pdf"); + + HashMap<String, Object> parameters = new HashMap<String, Object>(); + + String strLanguage = vars.getLanguage(); + + parameters.put("ShowGrouping", new Boolean(!strGroupBy.equals(""))); + parameters.put("Title", classInfo.name); + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + parameters.put("Previous", Utility.messageBD(this, "Initial Balance", strLanguage)); + parameters.put("Total", Utility.messageBD(this, "Total", strLanguage)); + String strDateFormat; + strDateFormat = vars.getJavaDateFormat(); + parameters.put("strDateFormat", strDateFormat); + renderJR(vars, response, strReportName, "pdf", parameters, data, null); } - - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.jrxml"; - response.setHeader("Content-disposition", "inline; filename=ReportGeneralLedgerPDF.pdf"); - - HashMap<String, Object> parameters = new HashMap<String, Object>(); - - String strLanguage = vars.getLanguage(); - - parameters.put("ShowGrouping", new Boolean(!strGroupBy.equals(""))); - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - parameters.put("Previous", Utility.messageBD(this, "Initial Balance", strLanguage)); - parameters.put("Total", Utility.messageBD(this, "Total", strLanguage)); - String strDateFormat; - strDateFormat = vars.getJavaDateFormat(); - parameters.put("strDateFormat", strDateFormat); - - renderJR(vars, response, strReportName, "pdf", parameters, data, null); + } catch (Throwable e) { + log4j.error("Error creating the PDF report in General Ledger"); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } } @@ -587,50 +624,68 @@ String strYearInitialDate = ReportGeneralLedgerData.yearInitialDate(this, vars .getSessionValue("#AD_SqlDateFormat"), strDateFrom, Utility.getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strOrgFamily); - if (strYearInitialDate.equals("")) - strYearInitialDate = strDateFrom; - String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1"); + String oraLimit1 = ""; + String pgLimit = ""; + if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) { + oraLimit1 = oraLimit1 + (MAX_XLS_ROWS + 1); + } else { + pgLimit = (MAX_XLS_ROWS + 1) + " OFFSET 0"; + } - String strAllaccounts = "Y"; + try { + if (strYearInitialDate.equals("")) + strYearInitialDate = strDateFrom; + String toDatePlusOne = DateTimeData.nDaysAfter(this, strDateTo, "1"); - if (!strDateFrom.equals("") && !strDateTo.equals("")) { - if (!strHide.equals("Y")) - strHide = "N"; - if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { - if (strcelementvalueto.equals("")) - strcelementvalueto = strcelementvaluefrom; - strAllaccounts = "N"; + String strAllaccounts = "Y"; + + if (!strDateFrom.equals("") && !strDateTo.equals("")) { + if (!strHide.equals("Y")) + strHide = "N"; + if (strcelementvaluefrom != null && !strcelementvaluefrom.equals("")) { + if (strcelementvalueto.equals("")) + strcelementvalueto = strcelementvaluefrom; + strAllaccounts = "N"; + } + data = ReportGeneralLedgerData.selectXLS(this, strDateFrom, toDatePlusOne, strAllaccounts, + strcelementvaluefrom, strcelementvalueto, Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportGeneralLedger"), Utility.getContext(this, vars, + "#User_Client", "ReportGeneralLedger"), strHide, strcAcctSchemaId, strDateFrom, + toDatePlusOne, strOrgFamily, strcBpartnerId, strmProductId, strcProjectId, strAmtFrom, + strAmtTo, oraLimit1, pgLimit); } - data = ReportGeneralLedgerData.selectXLS(this, vars.getLanguage(), strDateFrom, - toDatePlusOne, strAllaccounts, strcelementvaluefrom, strcelementvalueto, Utility - .getContext(this, vars, "#AccessibleOrgTree", "ReportGeneralLedger"), Utility - .getContext(this, vars, "#User_Client", "ReportGeneralLedger"), strHide, - strcAcctSchemaId, strDateFrom, toDatePlusOne, strOrgFamily, strcBpartnerId, - strmProductId, strcProjectId, strAmtFrom, strAmtTo); - } - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars + .getLanguage())); + } else if (data.length > MAX_XLS_ROWS) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); + } else { - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml"; + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml"; - HashMap<String, Object> parameters = new HashMap<String, Object>(); + HashMap<String, Object> parameters = new HashMap<String, Object>(); - String strLanguage = vars.getLanguage(); + String strLanguage = vars.getLanguage(); - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - String strDateFormat; - strDateFormat = vars.getJavaDateFormat(); - parameters.put("strDateFormat", strDateFormat); + parameters.put("Title", classInfo.name); + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportGeneralLedgerData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportGeneralLedgerData.selectOrganization(this, strOrg) + ")"); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + String strDateFormat; + strDateFormat = vars.getJavaDateFormat(); + parameters.put("strDateFormat", strDateFormat); - renderJR(vars, response, strReportName, "xls", parameters, data, null); + renderJR(vars, response, strReportName, "xls", parameters, data, null); + } + } catch (Throwable e) { + log4j.error("Error creating the XLS report in General Ledger", e); + + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } } diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql Tue Sep 28 10:24:54 2010 +0530 @@ -26,10 +26,18 @@ <![CDATA[ SELECT * FROM ( SELECT * - FROM ( SELECT '0' AS RN1, C.* FROM ( + FROM ( SELECT '0' AS RN1, C.NAME, C.VALUE, C.DATEACCT, C.AMTACCTDR, C.AMTACCTCR, C.TOTAL, C.FACT_ACCT_GROUP_ID, C.ID, C.GROUPBYID, + C.DESCRIPTION, C.ISDEBIT, C.TOTALACCTDR, C.TOTALACCTCR, C.TOTALACCTSUB, C.PREVIOUSDEBIT, C.PREVIOUSCREDIT, C.PREVIOUSTOTAL, + C.FINALDEBIT, C.FINALCREDIT, C.FINALTOTAL, C.DATEACCTNUMBER, C.groupby, C.bpid, C.bpname, C.pdid, C.pdname, C.pjid, C.pjname, + CASE ? + WHEN 'BPartner' THEN TO_CHAR(C_Bpartner.NAME) + WHEN 'Product' THEN TO_CHAR(M_Product.name) + WHEN 'Project' THEN TO_CHAR(C_Project.NAME) + ELSE '' END AS groupbyname + FROM ( SELECT VALUE, NAME, DATEACCT, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, (SUM(AMTACCTDR)-SUM(AMTACCTCR)) AS TOTAL, - FACT_ACCT_GROUP_ID, ID, groupbyid, groupbyname, + FACT_ACCT_GROUP_ID, ID, groupbyid, DESCRIPTION, ISDEBIT, 0 AS TOTALACCTDR, 0 AS TOTALACCTCR, 0 AS TOTALACCTSUB, 0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL, @@ -41,20 +49,12 @@ DATEACCT, AMTACCTDR, AMTACCTCR, FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id + WHEN 'BPartner' THEN FACT_ACCT.c_bpartner_id + WHEN 'Product' THEN FACT_ACCT.m_product_id + WHEN 'Project' THEN FACT_ACCT.c_project_id ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN (case AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) end) - WHEN 'Product' THEN (case AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) end) - WHEN 'Project' THEN (case AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) end) - ELSE '' END AS groupbyname, CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT FROM FACT_ACCT - LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID LEFT JOIN( select account_id, record_id2, sum(amtacctdr-amtacctcr) as sum from fact_acct f1 @@ -76,24 +76,21 @@ AND 2=2 AND 3=3) D WHERE 6=6 - GROUP BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + GROUP BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 - ORDER BY groupbyname, groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + ORDER BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT ) C + LEFT JOIN C_BPARTNER ON C.groupbyid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON C.groupbyid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON C.groupbyid = C_PROJECT.C_PROJECT_ID ) B WHERE 1=1 ) A ORDER BY groupbyname, groupbyid, VALUE, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT ]]></Sql> <Field name="rownum" value="count"/> <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" /> + <Parameter name="groupby"/> <Parameter name="groupbytext"/> <Parameter name="groupby"/> - <Parameter name="groupby"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="parDateFrom" optional="true" after="where 5=5"><![CDATA[AND DATEACCT >= TO_DATE(?) ]]></Parameter> <Parameter name="parDateTo" optional="true" after="where 5=5"><![CDATA[AND DATEACCT < TO_DATE(?) ]]></Parameter> <Parameter name="allaccounts"/> @@ -115,7 +112,7 @@ <Parameter name="factAcctId" optional="true" after="WHERE 6=6"><![CDATA[and to_char(DATEACCT,'J')||FACT_ACCT_GROUP_ID||DESCRIPTION||ISDEBIT < ? ]]></Parameter> <Parameter name="cBPartner" optional="true" after="WHERE 6=6"><![CDATA[AND groupbyid = ? ]]></Parameter> <Parameter name="pgLimit" type="argument" optional="true" after=", FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter> - <Parameter name="oraLimit1" type="argument" optional="true" after=") C"><![CDATA[ WHERE ROWNUM <= ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="C_PROJECT.C_PROJECT_ID"><![CDATA[ WHERE ROWNUM <= ]]></Parameter> <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter> </SqlMethod> @@ -123,21 +120,21 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT VALUE, NAME, DATEACCT, + SELECT VALUE, NAME, DATEACCT, AMTACCTDR, AMTACCTCR, + FACT_ACCT_GROUP_ID, ID, bpid, bpname, pdid, pdname, pjid, pjname, + DESCRIPTION + FROM( + SELECT D.VALUE, D.NAME, DATEACCT, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - FACT_ACCT_GROUP_ID, ID, bpid, bpname, pdid, pdname, pjid, pjname, - DESCRIPTION + FACT_ACCT_GROUP_ID, ID, bpid, C_BPARTNER.NAME AS bpname, pdid, M_PRODUCT.NAME AS pdname, pjid, C_PROJECT.NAME AS pjname, + D.DESCRIPTION, ISDEBIT FROM (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME, DATEACCT, AMTACCTDR, AMTACCTCR, FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - (case AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Bpartner',c_bpartner.c_bpartner_id, ?) end) as bpname, (case AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('M_Product',m_product.m_product_id, ?) end) as pdname, (case AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) when '**' then '' else AD_COLUMN_IDENTIFIER('C_Project',c_project.c_project_id, ?) end) as pjname, + FACT_ACCT.c_bpartner_id as bpid, FACT_ACCT.m_product_id as pdid, FACT_ACCT.c_project_id as pjid, CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT FROM FACT_ACCT - LEFT JOIN C_BPARTNER ON FACT_ACCT.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON FACT_ACCT.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON FACT_ACCT.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID LEFT JOIN( select account_id, record_id2, sum(amtacctdr-amtacctcr) as sum from fact_acct f1 @@ -158,18 +155,16 @@ AND FACT_ACCT.AD_ORG_ID IN ('2') AND 2=2 AND 3=3) D + LEFT JOIN C_BPARTNER ON D.bpid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON D.pdid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON D.pjid = C_PROJECT.C_PROJECT_ID WHERE 6=6 - GROUP BY VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT - HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 + GROUP BY D.VALUE, D.NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, D.description, C_BPARTNER.NAME, bpid, M_PRODUCT.NAME, pdid, C_PROJECT.NAME, pjid, ISDEBIT + HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0) A + WHERE 7=7 ORDER BY VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, description, bpname, bpid, pdname, pdid, pjname, pjid, ISDEBIT ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="parDateFrom" optional="true" after="where 5=5"><![CDATA[AND DATEACCT >= TO_DATE(?) ]]></Parameter> <Parameter name="parDateTo" optional="true" after="where 5=5"><![CDATA[AND DATEACCT < TO_DATE(?) ]]></Parameter> <Parameter name="allaccounts"/> @@ -187,6 +182,8 @@ <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter> <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)>=TO_NUMBER(?) ]]></Parameter> <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="WHERE 7=7"><![CDATA[ AND ROWNUM <= ]]></Parameter> + <Parameter name="pgLimit" type="argument" optional="true" after=", pdname, pdid, pjname, pjid, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter> </SqlMethod> <SqlMethod name="set" type="constant" return="multiple"> @@ -280,4 +277,103 @@ ]]></Sql> <Parameter name="elementValueId"/> </SqlMethod> + + <SqlMethod name="selectCount" type="preparedStatement" return="string"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT COUNT(NAME || VALUE || DATEACCT || AMTACCTDR || AMTACCTCR || TOTAL || FACT_ACCT_GROUP_ID || ID || GROUPBYID || + DESCRIPTION || ISDEBIT || TOTALACCTDR || TOTALACCTCR || TOTALACCTSUB || PREVIOUSDEBIT || PREVIOUSCREDIT || PREVIOUSTOTAL || + FINALDEBIT || FINALCREDIT || FINALTOTAL || DATEACCTNUMBER || groupbyname) AS COUNT + FROM ( + SELECT * + FROM ( SELECT '0' AS RN1, C.NAME, C.VALUE, C.DATEACCT, C.AMTACCTDR, C.AMTACCTCR, C.TOTAL, C.FACT_ACCT_GROUP_ID, C.ID, C.GROUPBYID, + C.DESCRIPTION, C.ISDEBIT, C.TOTALACCTDR, C.TOTALACCTCR, C.TOTALACCTSUB, C.PREVIOUSDEBIT, C.PREVIOUSCREDIT, C.PREVIOUSTOTAL, + C.FINALDEBIT, C.FINALCREDIT, C.FINALTOTAL, C.DATEACCTNUMBER, C.groupby, C.bpid, C.bpname, C.pdid, C.pdname, C.pjid, C.pjname, + CASE ? + WHEN 'BPartner' THEN TO_CHAR(C_Bpartner.NAME) + WHEN 'Product' THEN TO_CHAR(M_Product.name) + WHEN 'Project' THEN TO_CHAR(C_Project.NAME) + ELSE '' END AS groupbyname + FROM ( + SELECT VALUE, NAME, DATEACCT, + SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, (SUM(AMTACCTDR)-SUM(AMTACCTCR)) AS TOTAL, + FACT_ACCT_GROUP_ID, ID, groupbyid, + DESCRIPTION, ISDEBIT, + 0 AS TOTALACCTDR, 0 AS TOTALACCTCR, 0 AS TOTALACCTSUB, + 0 AS PREVIOUSDEBIT, 0 AS PREVIOUSCREDIT, 0 AS PREVIOUSTOTAL, + 0 AS FINALDEBIT, 0 AS FINALCREDIT, 0 AS FINALTOTAL, + TO_CHAR(DATEACCT,'J') AS DATEACCTNUMBER, + ? AS groupby, '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname + FROM + (SELECT FACT_ACCT.ACCTVALUE AS VALUE, FACT_ACCT.ACCTDESCRIPTION AS NAME, + DATEACCT, AMTACCTDR, AMTACCTCR, + FACT_ACCT_GROUP_ID, FACT_ACCT.ACCOUNT_ID AS ID, FACT_ACCT.DESCRIPTION, + CASE ? + WHEN 'BPartner' THEN FACT_ACCT.c_bpartner_id + WHEN 'Product' THEN FACT_ACCT.m_product_id + WHEN 'Project' THEN FACT_ACCT.c_project_id + ELSE '' END AS groupbyid, + CASE WHEN AMTACCTDR <> 0 THEN 'Y' ELSE 'N' END AS ISDEBIT + FROM FACT_ACCT + LEFT JOIN( + select account_id, record_id2, sum(amtacctdr-amtacctcr) as sum + from fact_acct f1 + where 5=5 + group by account_id, record_id2 + ) f2 ON fact_acct.account_id = f2.account_id and fact_acct.record_id2 = f2.record_id2 + WHERE (?='Y' OR FACT_ACCT.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 FACT_ACCT.AD_ORG_ID IN ('1') + AND FACT_ACCT.AD_CLIENT_ID IN ('1') + AND (? = 'N' OR (fact_acct.RECORD_ID2 IS NULL) OR (SUM != 0)) + AND 1=1 + AND FACT_ACCT.AD_ORG_ID IN ('2') + AND 2=2 + AND 3=3) D + WHERE 6=6 + GROUP BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 + ORDER BY groupbyid, VALUE, NAME, ID, DATEACCT, FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT + ) C + LEFT JOIN C_BPARTNER ON C.groupbyid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON C.groupbyid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON C.groupbyid = C_PROJECT.C_PROJECT_ID + ) B WHERE 1=1 + ) A + ]]></Sql> + <Field name="rownum" value="count"/> + <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" /> + <Parameter name="groupby"/> + <Parameter name="groupbytext"/> + <Parameter name="groupby"/> + <Parameter name="parDateFrom" optional="true" after="where 5=5"><![CDATA[AND DATEACCT >= TO_DATE(?) ]]></Parameter> + <Parameter name="parDateTo" optional="true" after="where 5=5"><![CDATA[AND DATEACCT < TO_DATE(?) ]]></Parameter> + <Parameter name="allaccounts"/> + <Parameter name="accountFrom"/> + <Parameter name="accountTo"/> + <Parameter name="adUserOrg" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'1'"/> + <Parameter name="adUserClient" type="replace" optional="true" after="AND FACT_ACCT.AD_CLIENT_ID IN (" text="'1'"/> + <Parameter name="hideMatched"/> + <Parameter name="acctschema" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.C_ACCTSCHEMA_ID = ? ]]></Parameter> + <Parameter name="parDateFrom2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT >= TO_DATE(?) ]]></Parameter> + <Parameter name="parDateTo2" optional="true" after="AND 1=1"><![CDATA[AND FACT_ACCT.DATEACCT < TO_DATE(?) ]]></Parameter> + <Parameter name="org" type="replace" optional="true" after="AND FACT_ACCT.AD_ORG_ID IN (" text="'2'"/> + <Parameter name="cBpartnerId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_BPARTNER_ID IN]]></Parameter> + <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.M_PRODUCT_ID IN]]></Parameter> + <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND FACT_ACCT.C_PROJECT_ID IN]]></Parameter> + <Parameter name="paramAmtFrom" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)>=TO_NUMBER(?) ]]></Parameter> + <Parameter name="paramAmtTo" optional="true" after="3=3"><![CDATA[ AND (FACT_ACCT.AMTACCTDR + FACT_ACCT.AMTACCTCR)<=TO_NUMBER(?) ]]></Parameter> + <Parameter name="accountId" optional="true" after="WHERE 6=6"><![CDATA[AND ID = ? ]]></Parameter> + <Parameter name="factAcctId" optional="true" after="WHERE 6=6"><![CDATA[and to_char(DATEACCT,'J')||FACT_ACCT_GROUP_ID||DESCRIPTION||ISDEBIT < ? ]]></Parameter> + <Parameter name="cBPartner" optional="true" after="WHERE 6=6"><![CDATA[AND groupbyid = ? ]]></Parameter> + <Parameter name="pgLimit" type="argument" optional="true" after=", FACT_ACCT_GROUP_ID, DESCRIPTION, ISDEBIT"><![CDATA[ LIMIT ]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="C_PROJECT.C_PROJECT_ID"><![CDATA[ WHERE ROWNUM <= ]]></Parameter> + <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 1=1"><![CDATA[ AND RN1 BETWEEN ]]></Parameter> + </SqlMethod> </SqlClass> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.html Tue Sep 28 10:24:54 2010 +0530 @@ -24,7 +24,7 @@ <title>Balance Sheet</title> <link rel="shortcut icon" href="../../../../../web/images/favicon.ico" type="image/x-icon" /> <link rel="stylesheet" type="text/css" href="../../../../../web/skins/Default/Openbravo_ERP_250.css" id="paramCSS" /> - + <script language="JavaScript" type="text/javascript" id="paramDirectory"> var baseDirectory = "../../../../../web/"; </script> @@ -54,8 +54,8 @@ function imprimir() { - openPDFFiltered("ReportTrialBalance.html?Command=PDF","PDF"); - return true; + openPDFFiltered("ReportTrialBalance.html?Command=PDF","PDF"); + return true; } </script> <script language="JavaScript" type="text/javascript"> @@ -118,14 +118,21 @@ if (getReadyStateHandler(XMLHttpRequestObj)) { try { if (XMLHttpRequestObj.responseText) strText = XMLHttpRequestObj.responseText; - } catch (e) { + } catch (e) { } - if (paramXMLParticular!=null && paramXMLParticular.length>0) { - id = paramXMLParticular[0]; - imageId = paramXMLParticular[1]; - } - addRows(strText,id); - showHideLayer(id, imageId); + if (paramXMLParticular!=null && paramXMLParticular.length>0) { + id = paramXMLParticular[0]; + imageId = paramXMLParticular[1]; + } + addRows(strText,id, true, "0"); + var obj = getReference(imageId); + if (obj!=null) { + if (obj.className == "datawarehouseopen") { + obj.className = "datawarehouseclose"; + } else { + obj.className = "datawarehouseopen"; + } + } } return true; } @@ -134,49 +141,78 @@ var obj = getReference(imageId); if (obj!=null) { if (obj.className == "datawarehouseopen") { - obj.className = "datawarehouseclose"; - showHideRows(id, "none"); - } else { - obj.className = "datawarehouseopen"; - showHideRows(id, ""); - } + obj.className = "datawarehouseclose"; + showHideRows(id, "none"); + } else { + obj.className = "datawarehouseopen"; + showHideRows(id, ""); + } } } -function addRows(htmlText, id) { +function addRows(htmlText, id, byDefault, startRow) { var jsonTable = eval(htmlText); if (typeof jsonTable != undefined) { - for (i=0; i<jsonTable.rows.length; i++) { - var node=document.getElementById('funcEvenOddRow1'+id); - var elem=document.createElement('TR'); - node.parentNode.insertBefore(elem,node.nextSibling); - elem.id = "funcEvenOddRow2"+ id +"_"+(i+1); - var newTD1 = document.createElement('TD'); - newTD1.innerHTML = jsonTable.rows[i].td1 - newTD1.className = jsonTable.config.classDefault; - newTD1.colSpan = "2"; - var newTD2 = document.createElement('TD'); - newTD2.innerHTML = jsonTable.rows[i].td2; - newTD2.className = jsonTable.config.classAmount; - var newTD3 = document.createElement('TD'); - newTD3.innerHTML = jsonTable.rows[i].td3; - newTD3.className = jsonTable.config.classAmount; - var newTD4 = document.createElement('TD'); - newTD4.innerHTML = jsonTable.rows[i].td4; - newTD4.className = jsonTable.config.classAmount; - var newTD5 = document.createElement('TD'); - newTD5.innerHTML = jsonTable.rows[i].td5; - newTD5.className = jsonTable.config.classAmount; - var newTD6 = document.createElement('TD'); - newTD6.innerHTML = ""; - newTD6.className = jsonTable.config.classDefault; - elem.appendChild(newTD1); - elem.appendChild(newTD2); - elem.appendChild(newTD3); - elem.appendChild(newTD4); - elem.appendChild(newTD5); - elem.appendChild(newTD6); - } + var node; + var range = Number(jsonTable.config.range); + var totalRecords = Number(jsonTable.config.totalRecords); + var intStartRow = Number(startRow); + if (byDefault) { + node = document.getElementById('funcEvenOddRow1'+id); + } else { + node = document.getElementById('more__'+id+"__"+startRow); + node.style.display = "none"; + } + if (jsonTable.rows != undefined) { + if (jsonTable.rows.length > 0 && totalRecords>=range && range!=0) { + var next = Number(startRow) + Number(totalRecords); + var elem=document.createElement('TR'); + node.parentNode.insertBefore(elem,node.nextSibling); + elem.id="more__"+id+"__"+next; + var newTD7 = document.createElement('TD'); + newTD7.className = jsonTable.config.classDefault; + newTD7.colSpan = "7"; + var a = document.createElement('a'); + a.innerHTML = "..."; + a.href="#"; + a.id=id+"__"+next; + a.onclick = function(){moreLines(this.id);return false;}; + newTD7.appendChild(a); + elem.appendChild(newTD7); + } + var cont = intStartRow; + for (i=0; i<jsonTable.rows.length; i++) { + var elem=document.createElement('TR'); + node.parentNode.insertBefore(elem,node.nextSibling); + elem.id = "funcEvenOddRow2"+ id +"_"+(cont+1); + var newTD1 = document.createElement('TD'); + newTD1.innerHTML = jsonTable.rows[i].td1 + newTD1.className = jsonTable.config.classDefault; + newTD1.colSpan = "2"; + var newTD2 = document.createElement('TD'); + newTD2.innerHTML = jsonTable.rows[i].td2; + newTD2.className = jsonTable.config.classAmount; + var newTD3 = document.createElement('TD'); + newTD3.innerHTML = jsonTable.rows[i].td3; + newTD3.className = jsonTable.config.classAmount; + var newTD4 = document.createElement('TD'); + newTD4.innerHTML = jsonTable.rows[i].td4; + newTD4.className = jsonTable.config.classAmount; + var newTD5 = document.createElement('TD'); + newTD5.innerHTML = jsonTable.rows[i].td5; + newTD5.className = jsonTable.config.classAmount; + var newTD6 = document.createElement('TD'); + newTD6.innerHTML = ""; + newTD6.className = jsonTable.config.classDefault; + elem.appendChild(newTD1); + elem.appendChild(newTD2); + elem.appendChild(newTD3); + elem.appendChild(newTD4); + elem.appendChild(newTD5); + elem.appendChild(newTD6); + cont = cont + 1; + } + } } } @@ -186,14 +222,19 @@ var txt = ''; while (fila != undefined) { i++; - fila.style.display = showHide; - fila = document.getElementById('funcEvenOddRow2'+id+'_'+i); + fila.style.display = showHide; + fila = document.getElementById('funcEvenOddRow2'+id+'_'+i); + var moreFila = document.getElementById("more__"+id+"__"+i); + if (moreFila != null) { + moreFila.style.display = showHide; + } } } function updateData(CommandValue, identifier) { var frm = document.frmMain; frm.inpcAccountId.value = identifier; + frm.initRecord.value = "0"; var obj = getReference("buttonTree" + identifier); if (obj!=null && obj.className == "datawarehouseclose") { var paramXMLReq = new Array(identifier, 'buttonTree' + identifier); @@ -203,6 +244,36 @@ } } +function moreLines(identifier) { + var frm = document.frmMain; + var x = identifier.split("__"); + frm.inpcAccountId.value = x[0]; + frm.initRecord.value = x[1]; + try { + var paramXMLReq = new Array(x[0], x[1]); + return submitXmlHttpRequest(callbackMore, frm, "OPEN", "ReportTrialBalance.html", false, null, paramXMLReq); + } catch (e) { + alert(e); + } +} + +function callbackMore(paramXMLParticular, XMLHttpRequestObj) { + var strText = ""; + var id = ""; + var numRows = ""; + if (getReadyStateHandler(XMLHttpRequestObj)) { + try { + if (XMLHttpRequestObj.responseText) strText = XMLHttpRequestObj.responseText; + } catch (e) {} + if (paramXMLParticular!=null && paramXMLParticular.length>0) { + id = paramXMLParticular[0]; + numRows = paramXMLParticular[1]; + } + addRows(strText, id, false, numRows); + } + return true; +} + function showHideFilters(identifier) { var obj = getReference(identifier); if (obj!=null) { @@ -222,43 +293,52 @@ </script> <script language="JavaScript" type="text/javascript"> - function onLoadDo(){ - this.windowTables = new Array( - new windowTableId('client', 'buttonHTML') - ); - setWindowTableParentElement(); - this.tabsTables = new Array( - new tabTableId('tdtopTabs') - ); - setTabTableParentElement(); - enableShortcuts('edition'); - setBrowserAutoComplete(false); + function onLoadDo(){ + this.windowTables = new Array( + new windowTableId('client', 'buttonHTML') + ); + setWindowTableParentElement(); + this.tabsTables = new Array( + new tabTableId('tdtopTabs') + ); + setTabTableParentElement(); + enableShortcuts('edition'); + setBrowserAutoComplete(false); - var selectedGroupBy = document.getElementById("paramSelectedGroupBy").value; - var groupby = document.getElementById("inpGroupBy"); - for (i=0;i<groupby.length;i++){ - if (groupby[i].value == selectedGroupBy) - groupby[i].selected = true; - } + var selectedGroupBy = document.getElementById("paramSelectedGroupBy").value; + var groupby = document.getElementById("inpGroupBy"); + for (i=0;i<groupby.length;i++){ + if (groupby[i].value == selectedGroupBy) + groupby[i].selected = true; + } - try { - onloadFunctions(); - } catch (e) {} - resizeArea(); - updateMenuIcon('buttonMenu'); + try { + onloadFunctions(); + } catch (e) {} + resizeArea(); + updateMenuIcon('buttonMenu'); - setWindowElementFocus('firstElement'); - } + setWindowElementFocus('firstElement'); + } - function onResizeDo(){ - resizeArea(); - } + function onResizeDo(){ + resizeArea(); + } + function submitCommandWithMarkChecked(elementValue){ + var frm = document.frmMain; + markCheckedAllElements(frm.inpcBPartnerId_IN); + markCheckedAllElements(frm.inpcProjectId_IN); + markCheckedAllElements(frm.inpmProductId_IN); + submitCommandFormParameter('FIND', frm.inpcElementValueIdFrom, elementValue,false, frm, 'ReportGeneralLedger.html', '_self', false, true); + } + </script> </head> <body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" onload="onLoadDo();" onresize="onResizeDo();"> <form method="post" action="ReportTrialBalance.html" name="frmMain" target="_self"> <input type="hidden" name="Command"></input> <input type="hidden" name="inpcAccountId"></input> + <input type="hidden" name="initRecord" value="0"></input> <table height="100%" border="0" cellpadding="0" cellspacing="0" id="main"> <tr> <td valign="top" id="tdleftTabs"></td> @@ -427,7 +507,7 @@ </tr> </table> </td> - <td class="TitleCell"></td> + <td class="TitleCell"></td> <td class="TitleCell"><span class="LabelText">To Date</span></td> <td class="TextBox_btn_ContentCell"> <table border="0" cellspacing="0" cellpadding="0" summary="" style="padding-top: 0px;"> @@ -453,21 +533,20 @@ <td class="TitleCell"/> </tr> <tr> + <td class="TitleCell"><span class="LabelText">Organization</span></td> + <td class="Combo_ContentCell"> + <select name="inpOrg" id="inpOrg" class="ComboKey Combo_TwoCells_width" required="true"> + <option value=""> <div id="reportAD_ORGID"></div></option> + </select></td> + </tr> + <tr> <td class="TitleCell"> <span class="LabelText">Accounting schema</span></td> <td class="Combo_ContentCell" colspan="2"> <select name="inpcAcctSchemaId" id="inpcAcctSchemaId" class="ComboKey Combo_TwoCells_width" required="true"> <option value=""> <div id="reportC_ACCTSCHEMA_ID"></div></option> </select> </td> - </tr> - <tr> - <td class="TitleCell"><span class="LabelText">Organization</span></td> - <td class="Combo_ContentCell"> - <select name="inpOrg" id="inpOrg" class="ComboKey Combo_OneCell_width" required="true"> - <option value=""> <div id="reportAD_ORGID"></div></option> - </select></td> - <td class="TitleCell"></td> - <td class="TitleCell"><span class="LabelText">Account Level</span></td> - <td class="Combo_ContentCell"> + <td class="TitleCell"><span class="LabelText">Account Level</span></td> + <td class="Combo_ContentCell"> <select name="inpLevel" id="inpLevel" class="ComboKey Combo_OneCell_width" required="true" onchange="displayLogic(); return true;"> <option value=""> <div id="reportLevel"></div></option> </select></td> @@ -519,7 +598,7 @@ </table> </td> </tr> - + <tr id="Filter1"> <td colspan="6"> <table class="FieldGroup" cellspacing="0" cellpadding="0" border="0"> @@ -541,7 +620,7 @@ </table> </td> </tr> - + <tr id="Filter2"> <td class="TitleCell"><span class="LabelText">Business Partner</span></td> <td class="List_ContentCell" colspan="2"> @@ -630,7 +709,7 @@ </tr> </table> </td> - +  <td class="TitleCell"><span class="LabelText">Product</span></td> <td class="List_ContentCell" colspan="2"> @@ -721,7 +800,7 @@ </table> </td> </tr> - +  <tr id="Filter3"> <td class="TitleCell"><span class="LabelText">Project</span></td> @@ -813,12 +892,12 @@ </tr> </table> - </td> + </td> <td class="ContentCell"></td> <td class="ContentCell"></td> <td class="ContentCell"></td> </tr> - +  <tr id="Filter4"> <td class="TitleCell"><span class="LabelText">Group By</span></td> @@ -835,7 +914,7 @@ <td class="ContentCell"></td> <td class="ContentCell"></td> </tr> - + <tr> <td colspan="6"> <table class="FieldGroup" cellspacing="0" cellpadding="0" border="0"> @@ -857,18 +936,18 @@ <td class="TitleCell"></td> <td class="Button_CenterAlign_ContentCell"> <div> - <button type="button" - id="buttonHTML" - class="ButtonLink" - onclick="submitCommandForm('FIND', true, frmMain, 'ReportTrialBalance.html', '_self');return false;" - onfocus="buttonEvent('onfocus', this); window.status='View Results in a New Window'; return true;" - onblur="buttonEvent('onblur', this);" - onkeyup="buttonEvent('onkeyup', this);" - onkeydown="buttonEvent('onkeydown', this);" - onkeypress="buttonEvent('onkeypress', this);" - onmouseup="buttonEvent('onmouseup', this);" - onmousedown="buttonEvent('onmousedown', this);" - onmouseover="buttonEvent('onmouseover', this); window.status='View Results in a New Window'; return true;" + <button type="button" + id="buttonHTML" + class="ButtonLink" + onclick="submitCommandForm('FIND', true, frmMain, 'ReportTrialBalance.html', '_self');return false;" + onfocus="buttonEvent('onfocus', this); window.status='View Results in a New Window'; return true;" + onblur="buttonEvent('onblur', this);" + onkeyup="buttonEvent('onkeyup', this);" + onkeydown="buttonEvent('onkeydown', this);" + onkeypress="buttonEvent('onkeypress', this);" + onmouseup="buttonEvent('onmouseup', this);" + onmousedown="buttonEvent('onmousedown', this);" + onmouseover="buttonEvent('onmouseover', this); window.status='View Results in a New Window'; return true;" onmouseout="buttonEvent('onmouseout', this);"> <table class="Button"> <tr> @@ -889,7 +968,7 @@ <tr> </table> - +  <table class="Main_Client_TableEdition"> <tr> @@ -898,8 +977,8 @@  <div id="sectionGridView"> <table cellspacing="0" cellpadding="0" width="100%" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;"> - <tr class="DataGrid_Body_Row"> - <th height="17" colspan="7" class="DataGrid_Header_Cell">Balance</th> + <tr class="DataGrid_Body_Row"> + <th height="17" colspan="7" class="DataGrid_Header_Cell">Balance</th> </tr> <tr class="DataGrid_Body_Row"> <th width="90" class="DataGrid_Header_Cell">ACCOUNT CODE</th> @@ -914,10 +993,10 @@ <div id="sectionDetail"> <tr class="DataGrid_Body_Row DataGrid_Body_Row_yy" id="funcEvenOddRow1xx"> <td width="90" class="DataGrid_Body_Cell"> - <span id="showExpand" style="display: table-cell; display: -moz-inline-box;"><a href="#" onclick="updateData('OPEN', 'hhqq');return false;" onMouseOver="window.status='Open';return true;" onMouseOut="window.status='';return true;" id="expandButton"> + <span id="showExpand" style="display: table-cell; display: -moz-inline-box;"><a href="#" onclick="updateData('OPEN', 'hhqq', '0');return false;" onMouseOver="window.status='Open';return true;" onMouseOut="window.status='';return true;" id="expandButton"> <span id="buttonTreemmm" class="datawarehouseclose"></span> </a></span> - <a href="#" onclick="submitCommandFormParameter('FIND', document.frmMain.inpcElementValueIdFrom, 'xx', false, document.frmMain, 'ReportGeneralLedger.html', '_self', false, true);return false;" onmouseover="window.status='General Ledger';return true;" onmouseout="window.status='';return true;" class="LabelLink" id="fieldId1"> + <a href="#" onclick="submitCommandWithMarkChecked('xx');return false;" onmouseover="window.status='General Ledger';return true;" onmouseout="window.status='';return true;" class="LabelLink" id="fieldId1"> <span id="fieldAccount">xx70000</span> </a> <span id="fieldDescAccount">xx600</span> @@ -932,7 +1011,7 @@ </td> </tr> </div> - <tr class="DataGrid_Body_Row"> + <tr class="DataGrid_Body_Row"> <th colspan="2" class="DataGrid_Header_Cell">ADD AND KEEP GOING...</th> <th width="105" id="fieldTotalSaldoInicial" class="DataGrid_Header_Cell_Amount">xx14500.34</th> <th width="105" id="fieldTotalAmtacctdr" class="DataGrid_Header_Cell_Amount">xx14500.34</th> diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance.java Tue Sep 28 10:24:54 2010 +0530 @@ -4,14 +4,14 @@ * Version 1.0 (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 + * 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) 2001-2009 Openbravo SLU + * 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-2010 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -55,6 +55,7 @@ public class ReportTrialBalance extends HttpSecureAppServlet { private static final long serialVersionUID = 1L; + private static final int MAX_XLS_ROWS = 64500; public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { @@ -185,8 +186,12 @@ "ReportTrialBalance|cProjectId", "", IsIDFilter.instance); String strGroupBy = vars.getRequestGlobalVariable("inpGroupBy", "ReportTrialBalance|GroupBy"); + String strInitRecord = vars.getRequiredStringParameter("initRecord"); + String strRecordRange = Utility.getContext(this, vars, "#RecordRange", "ReportTrialBalance"); + printPageOpen(response, vars, strDateFrom, strDateTo, strOrg, strLevel, strcBpartnerId, - strmProductId, strcProjectId, strcAcctSchemaId, strGroupBy, strAccountId); + strmProductId, strcProjectId, strcAcctSchemaId, strGroupBy, strAccountId, strInitRecord, + strRecordRange); } else { pageError(response); @@ -196,20 +201,38 @@ private void printPageOpen(HttpServletResponse response, VariablesSecureApp vars, String strDateFrom, String strDateTo, String strOrg, String strLevel, String strcBpartnerId, String strmProductId, String strcProjectId, String strcAcctSchemaId, String strGroupBy, - String strAccountId) throws IOException, ServletException { + String strAccountId, String strInitRecord, String strRecordRange) throws IOException, + ServletException { ReportTrialBalanceData[] data = null; String strTreeOrg = TreeData.getTreeOrg(this, vars.getClient()); String strOrgFamily = getFamily(strTreeOrg, strOrg); + // built limit/offset parameters for oracle/postgres + String rowNum = "0"; + String oraLimit1 = null; + String oraLimit2 = null; + String pgLimit = null; + int intRecordRange = (strRecordRange.equals("0") ? 0 : Integer.parseInt(strRecordRange)); + int intInitRecord = (strInitRecord.equals("") ? 0 : Integer.parseInt(strInitRecord)); + if (intRecordRange != 0) { + if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) { + rowNum = "ROWNUM"; + oraLimit1 = String.valueOf(intInitRecord + intRecordRange); + oraLimit2 = (intInitRecord + 1) + " AND " + oraLimit1; + } else { + rowNum = "0"; + pgLimit = intRecordRange + " OFFSET " + intInitRecord; + } + } + log4j.debug("Output: Expand subaccount details " + strAccountId); - data = ReportTrialBalanceData.selectAccountLines(this, strGroupBy, vars.getLanguage(), - strLevel, strOrgFamily, Utility - .getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility.getContext(this, - vars, "#AccessibleOrgTree", "ReportTrialBalance"), null, null, strDateFrom, - strAccountId, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData - .nDaysAfter(this, strDateTo, "1")); + data = ReportTrialBalanceData.selectAccountLines(this, rowNum, strGroupBy, strLevel, + strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), null, null, + strDateFrom, strAccountId, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, + DateTimeData.nDaysAfter(this, strDateTo, "1"), oraLimit1, oraLimit2, pgLimit); if (data == null) { data = ReportTrialBalanceData.set(); @@ -244,6 +267,8 @@ Map<String, String> props = new HashMap<String, String>(); props.put("classAmount", "DataGrid_Body_Cell_Amount"); props.put("classDefault", "DataGrid_Body_Cell"); + props.put("totalRecords", String.valueOf(data.length)); + props.put("range", String.valueOf(intRecordRange)); table.put("config", props); } catch (JSONException e) { @@ -306,12 +331,12 @@ } } else { if (strLevel.equals("S")) { // SubAccount selected - data = ReportTrialBalanceData.selectAccountLines(this, "", vars.getLanguage(), strLevel, - strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), - Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), + data = ReportTrialBalanceData.selectAccountLines(this, "0", "", strLevel, strOrgFamily, + Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility + .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), strAccountFromValue, strAccountToValue, strDateFrom, null, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, - strDateTo, "1")); + strDateTo, "1"), null, null, null); if (strGroupBy.equals("")) discard[2] = "showExpand"; @@ -454,50 +479,58 @@ + strTreeAccount); log4j.debug("strcBpartnerId: " + strcBpartnerId + "strmProductId: " + strmProductId + "strcProjectId: " + strcProjectId); + try { + if (!strDateFrom.equals("") && !strDateTo.equals("") && !strOrg.equals("") + && !strcAcctSchemaId.equals("")) { - if (!strDateFrom.equals("") && !strDateTo.equals("") && !strOrg.equals("") - && !strcAcctSchemaId.equals("")) { + if (strLevel.equals("S")) { + data = ReportTrialBalanceData.selectXLS(this, strLevel, strOrgFamily, Utility.getContext( + this, vars, "#User_Client", "ReportTrialBalance"), Utility.getContext(this, vars, + "#AccessibleOrgTree", "ReportTrialBalance"), strAccountFromValue, strAccountToValue, + strDateFrom, strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, + DateTimeData.nDaysAfter(this, strDateTo, "1")); + showDimensions = true; + } else { + data = getDataWhenNotSubAccount(vars, strDateFrom, strDateTo, strOrg, strOrgFamily, + strcAcctSchemaId, strLevel, strTreeAccount); + } - if (strLevel.equals("S")) { - data = ReportTrialBalanceData.selectXLS(this, vars.getLanguage(), strLevel, strOrgFamily, - Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), Utility - .getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), - strAccountFromValue, strAccountToValue, strDateFrom, strcBpartnerId, strmProductId, - strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, strDateTo, "1")); - showDimensions = true; + if (data == null || data.length == 0) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); + } else if (data.length > MAX_XLS_ROWS) { + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); + } else { + + String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml"; + + HashMap<String, Object> parameters = new HashMap<String, Object>(); + + String strLanguage = vars.getLanguage(); + + StringBuilder strSubTitle = new StringBuilder(); + strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); + strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); + strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg) + ")"); + parameters.put("Title", classInfo.name); + parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); + parameters.put("SHOWTOTALS", false); + parameters.put("SHOWDIMENSIONS", showDimensions); + + renderJR(vars, response, strReportName, "xls", parameters, data, null); + } } else { - data = getDataWhenNotSubAccount(vars, strDateFrom, strDateTo, strOrg, strOrgFamily, - strcAcctSchemaId, strLevel, strTreeAccount); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } + } catch (Exception e) { + log4j.error("Error creating the XLS report in TrialBalanceReport"); - if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); - } else { - - String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml"; - - HashMap<String, Object> parameters = new HashMap<String, Object>(); - - String strLanguage = vars.getLanguage(); - - parameters.put("Title", classInfo.name); - StringBuilder strSubTitle = new StringBuilder(); - strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom - + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + " ("); - strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); - strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg) + ")"); - parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); - parameters.put("SHOWTOTALS", false); - parameters.put("SHOWDIMENSIONS", showDimensions); - - renderJR(vars, response, strReportName, "xls", parameters, data, null); - } - } else { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "MAX_ROWS_LIMIT_EXCEED", vars.getLanguage())); } - } private void printPageDataPDF(HttpServletRequest request, HttpServletResponse response, @@ -528,12 +561,12 @@ && !strcAcctSchemaId.equals("")) { if (strLevel.equals("S")) { - data = ReportTrialBalanceData.selectAccountLines(this, strGroupBy, vars.getLanguage(), - strLevel, strOrgFamily, Utility.getContext(this, vars, "#User_Client", - "ReportTrialBalance"), Utility.getContext(this, vars, "#AccessibleOrgTree", - "ReportTrialBalance"), strAccountFromValue, strAccountToValue, strDateFrom, null, - strcBpartnerId, strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData - .nDaysAfter(this, strDateTo, "1")); + data = ReportTrialBalanceData.selectAccountLines(this, "0", strGroupBy, strLevel, + strOrgFamily, Utility.getContext(this, vars, "#User_Client", "ReportTrialBalance"), + Utility.getContext(this, vars, "#AccessibleOrgTree", "ReportTrialBalance"), + strAccountFromValue, strAccountToValue, strDateFrom, null, strcBpartnerId, + strmProductId, strcProjectId, strcAcctSchemaId, DateTimeData.nDaysAfter(this, + strDateTo, "1"), null, null, null); if (!strGroupBy.equals("")) strIsSubAccount = true; @@ -543,21 +576,21 @@ } if (data == null || data.length == 0) { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } else { String strLanguage = vars.getLanguage(); String strReportName = "@basedesign@/org/openbravo/erpCommon/ad_reports/ReportTrialBalancePDF.jrxml"; HashMap<String, Object> parameters = new HashMap<String, Object>(); - parameters.put("Title", classInfo.name); parameters.put("TOTAL", Utility.messageBD(this, "Total", strLanguage)); StringBuilder strSubTitle = new StringBuilder(); strSubTitle.append(Utility.messageBD(this, "DateFrom", strLanguage) + ": " + strDateFrom + " - " + Utility.messageBD(this, "DateTo", strLanguage) + ": " + strDateTo + "\n"); strSubTitle.append(ReportTrialBalanceData.selectCompany(this, vars.getClient()) + " - "); strSubTitle.append(ReportTrialBalanceData.selectOrgName(this, strOrg)); + parameters.put("Title", classInfo.name); parameters.put("REPORT_SUBTITLE", strSubTitle.toString()); parameters.put("DEFAULTVIEW", !strIsSubAccount); @@ -568,8 +601,8 @@ } } else { - advisePopUp(request, response, "WARNING", Utility.messageBD(this, "NoDataFound", vars - .getLanguage())); + advisePopUp(request, response, "WARNING", Utility.messageBD(this, "ProcessStatus-W", vars + .getLanguage()), Utility.messageBD(this, "NoDataFound", vars.getLanguage())); } } diff -r 17a70403b6d3 src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql --- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql Wed Mar 31 12:07:41 2010 +0200 +++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalance_data.xsql Tue Sep 28 10:24:54 2010 +0530 @@ -5,15 +5,15 @@ * Version 1.0 (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 + * 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. + * 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-2009 Openbravo SLU - * All Rights Reserved. + * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ --> @@ -24,14 +24,15 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT MAX(PARENT_ID) AS PARENT_ID, ID, NAME, ACCOUNT_ID, ELEMENTLEVEL, SUM(AMTACCTDR) AS AMTACCTDR, + SELECT MAX(PARENT_ID) AS PARENT_ID, ID, NAME, ACCOUNT_ID, ELEMENTLEVEL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, 0 AS SALDO_INICIAL,0 AS TOTALAMTACCTDR, 0 AS TOTALAMTACCTCR, 0 AS SALDO_FINAL, TO_DATE(?) AS DATE_FROM, TO_DATE(?) AS DATE_TO, ? AS AD_ORG_ID, '' AS BP, '' AS PADRE, '' AS groupbyname, '' AS groupbyid, '' AS groupbytext, - '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname + '' as bpid, '' as bpname, '' as pdid, '' as pdname, '' as pjid, '' as pjname, + '' as RN1 FROM - (SELECT AD_TREENODE.PARENT_ID, C_ELEMENTVALUE.C_ELEMENTVALUE_ID AS ID, C_ELEMENTVALUE.ELEMENTLEVEL, - C_ELEMENTVALUE.NAME AS NAME, C_ELEMENTVALUE.VALUE AS ACCOUNT_ID, 0 AS AMTACCTDR, + (SELECT AD_TREENODE.PARENT_ID, C_ELEMENTVALUE.C_ELEMENTVALUE_ID AS ID, C_ELEMENTVALUE.ELEMENTLEVEL, + C_ELEMENTVALUE.NAME AS NAME, C_ELEMENTVALUE.VALUE AS ACCOUNT_ID, 0 AS AMTACCTDR, 0 AS AMTACCTCR FROM AD_TREENODE, C_ELEMENTVALUE WHERE AD_TREENODE.NODE_ID = C_ELEMENTVALUE.C_ELEMENTVALUE_ID @@ -39,10 +40,10 @@ AND AD_TREENODE.ISACTIVE = 'Y' AND C_ELEMENTVALUE.ISACTIVE = 'Y' AND (select max(c_element_id) from c_acctschema_element where c_acctschema_id = ? and ELEMENTTYPE = 'AC') = C_ELEMENTVALUE.C_ELEMENT_ID - UNION - SELECT '0' AS PARENT_ID, F.ACCOUNT_ID AS ID, EV.ELEMENTLEVEL, EV.NAME AS NAME, EV.VALUE AS ACCOUNT_ID, - SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE F.AMTACCTDR END)) AS AMTACCTDR, - SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE f.AMTACCTCR END)) AS AMTACCTCR + UNION + SELECT '0' AS PARENT_ID, F.ACCOUNT_ID AS ID, EV.ELEMENTLEVEL, EV.NAME AS NAME, EV.VALUE AS ACCOUNT_ID, + SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE F.AMTACCTDR END)) AS AMTACCTDR, + SUM((CASE f.FACTACCTTYPE WHEN 'O' THEN 0 ELSE f.AMTACCTCR END)) AS AMTACCTCR FROM FACT_ACCT F, C_ELEMENTVALUE EV WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND f.AD_ORG_ID IN('2') @@ -61,7 +62,7 @@ <Parameter name="dateTo"/> <Parameter name="org"/> <Parameter name="treeAcct"/> - <Parameter name="acctSchema"/> + <Parameter name="acctSchema"/> <Parameter name="orgFamily" type="replace" optional="true" after="f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="F.AD_CLIENT_ID IN (" text="'1'"/> <Parameter name="adUserOrg" type="replace" optional="true" after="F.AD_ORG_ID IN(" text="'1'"/> @@ -69,9 +70,9 @@ <Parameter name="parDateTo" optional="true" after="AND 1=1"><![CDATA[AND F.DATEACCT < TO_DATE(?)]]></Parameter> <Parameter name="accountFrom" optional="true" after="AND 1=1"><![CDATA[AND EV.VALUE >= ?]]></Parameter> <Parameter name="accountTo" optional="true" after="AND 1=1"><![CDATA[AND EV.VALUE <= ?]]></Parameter> - <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> + <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> </SqlMethod> - + <SqlMethod name="set" type="constant" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql></Sql> @@ -81,52 +82,35 @@ <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT ID, ACCOUNT_ID, NAME, + SELECT * + FROM ( SELECT '0' AS RN1, D.*, + CASE ? + WHEN 'BPartner' THEN TO_CHAR(bp.name) + WHEN 'Product' THEN TO_CHAR(pd.name) + WHEN 'Project' THEN TO_CHAR(pj.name) + ELSE '' + END AS groupbyname + FROM ( + SELECT ID, ACCOUNT_ID, C.NAME AS NAME, SUM(SALDO_INICIAL) AS SALDO_INICIAL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, SUM(SALDO_INICIAL+AMTACCTDR-AMTACCTCR) AS SALDO_FINAL, - groupbyid, - CASE ? - WHEN 'BPartner' THEN - CASE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - WHEN 'Product' THEN - CASE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - WHEN 'Project' THEN - CASE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(groupbyid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(groupbyid), TO_CHAR(?)) - END - ELSE '' - END AS groupbyname + groupbyid FROM ((SELECT ID, ACCOUNT_ID, NAME, - 0 AS AMTACCTDR, 0 AS AMTACCTCR, + 0 AS AMTACCTDR, 0 AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL, - groupbyname, groupbyid + groupbyid FROM ((SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -138,19 +122,11 @@ (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -159,29 +135,21 @@ AND 3=3 AND F.ISACTIVE = 'Y' AND F.FACTACCTTYPE = 'O')) A - GROUP BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + GROUP BY ACCOUNT_ID, ID, NAME, groupbyid HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 ) UNION (SELECT ID, ACCOUNT_ID, NAME, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - 0 AS SALDO_INICIAL, groupbyname, groupbyid + 0 AS SALDO_INICIAL, groupbyid FROM (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, CASE ? - WHEN 'BPartner' THEN c_bpartner.c_bpartner_id - WHEN 'Product' THEN m_product.m_product_id - WHEN 'Project' THEN c_project.c_project_id - ELSE '' END AS groupbyid, - CASE ? - WHEN 'BPartner' THEN to_char(c_bpartner.name) - WHEN 'Product' THEN to_char(m_product.name) - WHEN 'Project' THEN to_char(c_project.name) - ELSE '' END AS groupbyname + WHEN 'BPartner' THEN F.c_bpartner_id + WHEN 'Product' THEN F.m_product_id + WHEN 'Project' THEN F.c_project_id + ELSE '' END AS groupbyid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -192,19 +160,19 @@ AND F.FACTACCTTYPE <> 'C' AND F.FACTACCTTYPE <> 'O' AND F.ISACTIVE = 'Y') B - GROUP BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + GROUP BY ACCOUNT_ID, ID, NAME, groupbyid HAVING SUM(AMTACCTDR) <> 0 OR SUM(AMTACCTCR) <> 0 )) C - GROUP BY ACCOUNT_ID, ID, groupbyid, groupbyname, NAME - ORDER BY ACCOUNT_ID, ID, groupbyname, groupbyid, NAME + WHERE 4=4 + GROUP BY ACCOUNT_ID, ID, C.NAME, groupbyid ) D + LEFT JOIN C_BPARTNER bp ON groupbyid = bp.C_BPARTNER_ID + LEFT JOIN M_PRODUCT pd ON groupbyid = pd.M_PRODUCT_ID + LEFT JOIN C_PROJECT pj ON groupbyid = pj.C_PROJECT_ID + ORDER BY ACCOUNT_ID, ID, D.NAME, groupbyname, groupbyid + ) E + WHERE 5=5 AND 6=6 ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="groupby"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> + <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" /> <Parameter name="groupby"/> <Parameter name="groupby"/> <Parameter name="accountLevel"/> @@ -221,7 +189,6 @@ <Parameter name="acctSchema" optional="true" after="AND 1=1"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter>  <Parameter name="groupby"/> - <Parameter name="groupby"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after=" f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after=" F.AD_CLIENT_ID IN (" text="'1'"/> @@ -236,7 +203,6 @@ <Parameter name="acctSchema" optional="true" after="AND 3=3"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter>  <Parameter name="groupby"/> - <Parameter name="groupby"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after="AND f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="AND F.AD_CLIENT_ID IN (" text="'1'"/> @@ -250,44 +216,34 @@ <Parameter name="mProductId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.M_PRODUCT_ID IN]]></Parameter> <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.C_PROJECT_ID IN]]></Parameter> <Parameter name="acctSchema" optional="true" after="AND 2=2"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> + <Parameter name="oraLimit1" type="argument" optional="true" after="WHERE 4=4"><![CDATA[ AND ROWNUM <= ]]></Parameter> + <Parameter name="oraLimit2" type="argument" optional="true" after="WHERE 5=5"><![CDATA[ AND RN1 BETWEEN ]]></Parameter> + <Parameter name="pgLimit" type="argument" optional="true" after="6=6"><![CDATA[ LIMIT ]]></Parameter> </SqlMethod> - + <SqlMethod name="selectXLS" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> <Sql> <![CDATA[ - SELECT ID, ACCOUNT_ID, NAME, + SELECT ID, ACCOUNT_ID, C.NAME AS NAME, SUM(SALDO_INICIAL) AS SALDO_INICIAL, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, SUM(SALDO_INICIAL+AMTACCTDR-AMTACCTCR) AS SALDO_FINAL, bpid, pdid, pjid, - CASE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(bpid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_BPARTNER'), TO_CHAR(bpid), TO_CHAR(?)) - END AS bpname, - CASE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(pdid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('M_PRODUCT'), TO_CHAR(pdid), TO_CHAR(?)) - END AS pdname, - CASE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(pjid), TO_CHAR(?)) - WHEN '**' THEN '' - ELSE ad_column_identifier(TO_CHAR('C_PROJECT'), TO_CHAR(pjid), TO_CHAR(?)) - END AS pjname + c_bpartner.name AS bpname, + m_product.name AS pdname, + c_project.name AS pjname FROM ((SELECT ID, ACCOUNT_ID, NAME, - 0 AS AMTACCTDR, 0 AS AMTACCTCR, + 0 AS AMTACCTDR, 0 AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL, - bpid, pdid, pjid, bpname, pdname + bpid, pdid, pjid FROM ((SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -298,12 +254,8 @@ UNION (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, F.DATEACCT, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -312,22 +264,18 @@ AND 3=3 AND F.ISACTIVE = 'Y' AND F.FACTACCTTYPE = 'O')) A - GROUP BY ACCOUNT_ID, ID, bpid, pdid, pjid, bpname, pdname, NAME + GROUP BY ACCOUNT_ID, ID, NAME, bpid, pdid, pjid HAVING SUM(AMTACCTDR) - SUM(AMTACCTCR) <> 0 ) UNION (SELECT ID, ACCOUNT_ID, NAME, SUM(AMTACCTDR) AS AMTACCTDR, SUM(AMTACCTCR) AS AMTACCTCR, - 0 AS SALDO_INICIAL, bpid, pdid, pjid, bpname, pdname + 0 AS SALDO_INICIAL, bpid, pdid, pjid FROM (SELECT F.ACCOUNT_ID AS ID, EV.VALUE AS ACCOUNT_ID, EV.NAME AS NAME, F.AMTACCTDR, F.AMTACCTCR, F.FACTACCTTYPE, - c_bpartner.c_bpartner_id as bpid, m_product.m_product_id as pdid, c_project.c_project_id as pjid, - c_bpartner.name as bpname, m_product.name as pdname + F.c_bpartner_id as bpid, F.m_product_id as pdid, F.c_project_id as pjid FROM C_ELEMENTVALUE EV, FACT_ACCT F - LEFT JOIN C_BPARTNER ON f.C_BPARTNER_ID = C_BPARTNER.C_BPARTNER_ID - LEFT JOIN M_PRODUCT ON f.M_PRODUCT_ID = M_PRODUCT.M_PRODUCT_ID - LEFT JOIN C_PROJECT ON f.C_PROJECT_ID = C_PROJECT.C_PROJECT_ID WHERE F.ACCOUNT_ID = EV.C_ELEMENTVALUE_ID AND EV.ELEMENTLEVEL = ? AND f.AD_ORG_ID IN('2') @@ -338,17 +286,14 @@ AND F.FACTACCTTYPE <> 'C' AND F.FACTACCTTYPE <> 'O' AND F.ISACTIVE = 'Y') B - GROUP BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid )) C - GROUP BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid - ORDER BY ACCOUNT_ID, ID, NAME, bpname, bpid, pdname, pdid, pjid + GROUP BY ACCOUNT_ID, ID, NAME, bpid, pdid, pjid )) C + LEFT JOIN C_BPARTNER ON bpid = C_BPARTNER.C_BPARTNER_ID + LEFT JOIN M_PRODUCT ON pdid = M_PRODUCT.M_PRODUCT_ID + LEFT JOIN C_PROJECT ON pjid = C_PROJECT.C_PROJECT_ID + GROUP BY ACCOUNT_ID, ID, C.NAME, bpid, pdid, pjid, c_bpartner.name, m_product.name, c_project.name + ORDER BY ACCOUNT_ID, ID, C.NAME, bpname, bpid, pdname, pdid, pjname, pjid ]]></Sql> <Field name="rownum" value="count"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> - <Parameter name="adLanguage"/> <Parameter name="accountLevel"/> <Parameter name="orgFamily" type="replace" optional="true" after="f.AD_ORG_ID IN(" text="'2'"/> <Parameter name="adUserClient" type="replace" optional="true" after="F.AD_CLIENT_ID IN (" text="'1'"/> @@ -386,10 +331,10 @@ <Parameter name="cProjectId" optional="true" type="argument" after="AND 2=2"><![CDATA[ AND F.C_PROJECT_ID IN]]></Parameter> <Parameter name="acctSchema" optional="true" after="AND 2=2"><![CDATA[ AND F.C_ACCTSCHEMA_ID = ?]]></Parameter> </SqlMethod> - + <SqlMethod name="selectInitialBalance" type="preparedStatement" return="multiple"> <SqlMethodComment></SqlMethodComment> - <Sql><![CDATA[ + <Sql><![CDATA[ SELECT ACCOUNT_ID, COALESCE(SUM(AMTACCTDR),0) AS AMTACCTDR, COALESCE(SUM(AMTACCTCR),0) AS AMTACCTCR, COALESCE(SUM(AMTACCTDR-AMTACCTCR), 0) AS SALDO_INICIAL FROM ( @@ -402,7 +347,7 @@ AND F.AD_ORG_ID IN ('1') AND F.AD_CLIENT_ID IN ('1') AND F.ISACTIVE = 'Y') - UNION + UNION ALL (SELECT F.ACCOUNT_ID AS ACCOUNT_ID, F.AMTACCTDR AS AMTACCTDR, F.AMTACCTCR AS AMTACCTCR, F.DATEACCT, F.FACTACCTTYPE FROM FACT_ACCT F @@ -433,7 +378,7 @@ <Parameter name="orgFamily" type="replace" optional="true" after="AND F.AD_ORG_ID IN (" text="'1'"/> <Parameter name="clientFamily" type="replace" optional="true" after="AND F.AD_CLIENT_ID IN (" text="'1'"/> </SqlMethod> - + <SqlMethod name="treeAccount" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql> @@ -463,7 +408,7 @@ </Sql> <Parameter name="organization"/> </SqlMethod> - + <SqlMethod name="selectAcctSchemaName" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql> @@ -473,7 +418,7 @@ </Sql> <Parameter name="acctSchema"/> </SqlMethod> - + <SqlMethod name="selectAccountingName" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ @@ -484,24 +429,24 @@ ]]></Sql> <Parameter name="cElementvalueId"/> </SqlMethod> - + <SqlMethod name="selectLastAccount" type="preparedStatement" return="string" default=""> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ - SELECT C_ELEMENTVALUE.C_ELEMENTVALUE_ID + SELECT C_ELEMENTVALUE.C_ELEMENTVALUE_ID FROM C_ELEMENTVALUE WHERE C_ELEMENTVALUE.VALUE = ( SELECT MAX(C_ELEMENTVALUE.VALUE) FROM C_ELEMENTVALUE - WHERE C_ELEMENTVALUE.AD_Org_ID IN('1') - AND C_ELEMENTVALUE.AD_Client_ID IN('1') + WHERE C_ELEMENTVALUE.AD_Org_ID IN('1') + AND C_ELEMENTVALUE.AD_Client_ID IN('1') AND C_ELEMENTVALUE.IsSummary='N' AND C_ELEMENTVALUE.ISACTIVE='Y') ]]></Sql> <Parameter name="adOrgClient" type="replace" optional="true" after="C_ELEMENTVALUE.AD_Org_ID IN(" text="'1'"/> <Parameter name="adUserClient" type="replace" optional="true" after="C_ELEMENTVALUE.AD_Client_ID IN(" text="'1'"/> </SqlMethod> - + <SqlMethod name="selectSubaccountDescription" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[ @@ -511,7 +456,7 @@ ]]></Sql> <Parameter name="elementValueId"/> </SqlMethod> - + <SqlMethod name="selectAccountValue" type="preparedStatement" return="string"> <SqlMethodComment></SqlMethodComment> <Sql><![CDATA[

Issue History
Date Modified	Username	Field	Change
2010-05-18 12:10	networkb	New Issue
2010-05-18 12:10	networkb	Assigned To	=> dalsasua
2010-05-18 13:54	adrianromero	Note Added: 0027424
2010-05-18 16:49	networkb	Steps to Reproduce Updated	View Revisions
2010-05-18 16:52	adrianromero	Status	new => scheduled
2010-05-18 16:52	adrianromero	fix_in_branch	=> pi
2010-06-02 17:23	jonalegriaesarte	Assigned To	dalsasua => eduardo_Argal
2010-06-21 16:37	eduardo_Argal	Note Added: 0028633
2010-06-21 16:37	eduardo_Argal	Status	scheduled => resolved
2010-06-21 16:37	eduardo_Argal	Fixed in SCM revision	=>
2010-06-21 16:37	eduardo_Argal	Resolution	open => fixed
2010-07-02 12:18	dmitry_mezentsev	Relationship added	related to 0013652
2010-09-07 13:27	jonalegriaesarte	Note Added: 0030760
2010-09-07 13:27	jonalegriaesarte	Status	resolved => new
2010-09-07 13:27	jonalegriaesarte	Resolution	fixed => open
2010-09-09 13:12	adrianromero	Relationship added	blocks 0013657
2010-09-09 13:12	adrianromero	Relationship deleted	blocks 0013657
2010-09-09 13:13	adrianromero	Relationship added	related to 0013657
2010-09-09 13:35	adrianromero	File Added: MaxRowsExceeded.diff
2010-09-09 13:36	adrianromero	Note Added: 0030896
2010-09-09 13:36	adrianromero	Assigned To	eduardo_Argal => adrianromero
2010-09-09 13:37	adrianromero	Status	new => scheduled
2010-09-10 10:07	emartinez	Issue Monitored: emartinez
2010-09-10 10:30	emartinez	Note Added: 0030928
2010-09-20 14:59	sivaraman	File Added: Fixes_Issue_13361.diff
2010-09-20 14:59	sivaraman	Assigned To	adrianromero => sivaraman
2010-09-20 15:15	sivaraman	Note Added: 0031232
2010-09-23 13:06	adrianromero	Status	scheduled => resolved
2010-09-23 13:06	adrianromero	Resolution	open => fixed
2010-09-28 10:48	adrianromero	File Added: Fixes_Issue_13361 (updated).diff
2010-12-14 18:31	psarobe	Status	resolved => closed
2010-12-16 22:15	anonymous	sf_bug_id	0 => 3138771

Notes
(0027424) adrianromero (manager) 2010-05-18 13:54	What does "Do not work well" mean. Could you please be more detailed. http://wiki.openbravo.com/wiki/Bug_Reporting_Guidelines#Quick_guide_to_writing_good_bug_reports [^]

(0028633) eduardo_Argal (developer) 2010-06-21 16:37	Diff sent to customer. Once it is validated on their side changes will be applied to pi

(0030760) jonalegriaesarte (reporter) 2010-09-07 13:27	The problem persists. If you filter by Sep.2009 and you generate the html report -> OK, if you generate the xls report ->KO and if you generate the pdf report -> KO. Thanks.

(0030896) adrianromero (manager) 2010-09-09 13:36	The attached file MaxRowsExceeded.diff contains the solution proposed. It is only missing to apply the limit for the PDF report.

(0030928) emartinez (reporter) 2010-09-10 10:30	The export to excel does not work neither.

(0031232) sivaraman (reporter) 2010-09-20 15:15	The uploaded patch (Fixes_Issue_13361.diff) has included a check that will not allow PDF report if row count exceeds PDF_REPORT_LIMIT. Actually, this report limit is supposed to be taken from preference. As we don't have the option in MP14, it has been hard coded in ReportGeneralLedger.java. Kindly change the value before to test the limitation.