0032406: Aging Reports causing tomcat to crash - Openbravo Issue Tracking System

diff --git a/src-db/database/model/functions/AGING_INVOICECURRENCY_RATE.xml b/src-db/database/model/functions/AGING_INVOICECURRENCY_RATE.xml --- a/src-db/database/model/functions/AGING_INVOICECURRENCY_RATE.xml +++ b/src-db/database/model/functions/AGING_INVOICECURRENCY_RATE.xml @@ -4,45 +4,61 @@ <parameter name="p_invoice_id" type="VARCHAR" mode="in"> <default/> </parameter> + <parameter name="p_curto_id" type="VARCHAR" mode="in"> + <default/> + </parameter> <parameter name="p_curfrom_id" type="VARCHAR" mode="in"> <default/> </parameter> - <parameter name="p_curto_id" type="VARCHAR" mode="in"> - <default/> - </parameter> - <body><![CDATA[v_count NUMBER:=0; -v_rate NUMBER:=0; -v_client_id character varying(32); -v_org_id character varying(32); -v_currency_id character varying(32); -v_dateacct timestamp without time zone; + <body><![CDATA[/************************************************************************* +* The contents of this file are subject to the Openbravo Public License +* Version 1.1 (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 +* 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) 2016 Openbravo SLU +* All Rights Reserved. +* Contributor(s): ______________________________________. +************************************************************************/ + +v_count NUMBER:=0; +v_rate NUMBER:=1; +v_client_id c_invoice.ad_client_id%TYPE; +v_org_id c_invoice.ad_org_id%TYPE; +v_currency_id c_invoice.c_currency_id%TYPE; +v_dateacct c_invoice.dateacct%TYPE; BEGIN IF(p_curfrom_id <> p_curto_id) THEN - - SELECT dateacct, ad_client_id, ad_org_id - into v_dateacct, v_client_id, v_org_id - from c_invoice where c_invoice_id = p_invoice_id; - - v_count:=0; - SELECT 1 INTO v_count FROM c_conversion_rate_document + SELECT count(*) + INTO v_count + FROM c_conversion_rate_document WHERE c_invoice_id = p_invoice_id and c_currency_id = p_curfrom_id and c_currency_id_to = p_curto_id; IF(v_count = 1) THEN - SELECT rate INTO v_rate FROM c_conversion_rate_document - WHERE c_invoice_id = p_invoice_id - and c_currency_id = p_curfrom_id - and c_currency_id_to = p_curto_id; + SELECT rate + INTO v_rate + FROM c_conversion_rate_document + WHERE c_invoice_id = p_invoice_id + and c_currency_id = p_curfrom_id + and c_currency_id_to = p_curto_id; ELSE - v_rate:=c_currency_rate(p_curto_id, p_curfrom_id, v_dateacct, 'S', v_client_id, v_org_id); - + SELECT dateacct, ad_client_id, ad_org_id + into v_dateacct, v_client_id, v_org_id + from c_invoice + where c_invoice_id = p_invoice_id; + v_rate:=c_currency_rate(p_curfrom_id, p_curto_id, v_dateacct, 'S', v_client_id, v_org_id); END IF; - ELSE - v_rate:=1; - END IF; +END IF; RETURN v_rate; diff --git a/src-db/database/model/functions/AGING_ISDOUBTFULTDEBT.xml b/src-db/database/model/functions/AGING_ISDOUBTFULTDEBT.xml --- a/src-db/database/model/functions/AGING_ISDOUBTFULTDEBT.xml +++ b/src-db/database/model/functions/AGING_ISDOUBTFULTDEBT.xml @@ -24,19 +24,23 @@ * Contributor(s): ______________________________________. ************************************************************************/ v_count NUMBER:=0; -BEGIN - SELECT 1 INTO v_count - from fin_doubtful_debt_run fddr where exists - (select 1 from fin_doubtful_debt - where fin_doubtful_debt_run_id = fddr.fin_doubtful_debt_run_id - and fin_payment_schedule_id = p_finpaymentschedule_id - and fddr.rundate > p_currentdate); - +BEGIN + SELECT count(*) + INTO v_count + FROM DUAL + WHERE EXISTS (SELECT 1 + FROM fin_doubtful_debt dd + WHERE dd.fin_payment_schedule_id = p_finpaymentschedule_id + AND EXISTS (select 1 + from fin_doubtful_debt_run ddr + where ddr.fin_doubtful_debt_run_id = dd.fin_doubtful_debt_run_id + and ddr.rundate > p_currentdate) + ); - IF v_count = 1 THEN + IF v_count = 0 THEN + RETURN 'N'; + ELSE RETURN 'Y'; - ELSE - RETURN 'N'; END IF; diff --git a/src-db/database/model/modifiedTables/FIN_DOUBTFUL_DEBT.xml b/src-db/database/model/modifiedTables/FIN_DOUBTFUL_DEBT.xml new file mode 100644 --- /dev/null +++ b/src-db/database/model/modifiedTables/FIN_DOUBTFUL_DEBT.xml @@ -0,0 +1,8 @@ +<?xml version="1.0"?> + <database name="MODIFIED TABLE FIN_DOUBTFUL_DEBT"> + <table name="FIN_DOUBTFUL_DEBT" primaryKey="FIN_DOUBTFUL_DEBT_KEY"> + <index name="EM_AGING_DOUBTDBT_PAYSCHED_IDX" unique="false"> + <index-column name="FIN_PAYMENT_SCHEDULE_ID"/> + </index> + </table> + </database> diff --git a/src/org/openbravo/agingbalance/ad_reports/AgingDao.java b/src/org/openbravo/agingbalance/ad_reports/AgingDao.java --- a/src/org/openbravo/agingbalance/ad_reports/AgingDao.java +++ b/src/org/openbravo/agingbalance/ad_reports/AgingDao.java @@ -11,7 +11,7 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU - * All portions are Copyright (C) 2012-2015 Openbravo SLU + * All portions are Copyright (C) 2012-2016 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -25,23 +25,22 @@ import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; +import java.util.Collections; import java.util.Date; import java.util.GregorianCalendar; import java.util.HashMap; import java.util.List; import java.util.Set; -import java.util.Map.Entry; import javax.servlet.ServletException; +import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.hibernate.Query; import org.hibernate.ScrollMode; import org.hibernate.ScrollableResults; import org.hibernate.Session; import org.hibernate.criterion.Restrictions; -import org.hibernate.dialect.function.StandardSQLFunction; -import org.hibernate.type.StandardBasicTypes; import org.openbravo.advpaymentmngt.utility.FIN_Utility; import org.openbravo.base.exception.OBException; import org.openbravo.dal.core.OBContext; @@ -64,6 +63,7 @@ import org.openbravo.model.financialmgmt.payment.DoubtfulDebt; import org.openbravo.model.financialmgmt.payment.FIN_Payment; import org.openbravo.model.financialmgmt.payment.FIN_PaymentScheduleDetail; +import org.openbravo.service.db.DalConnectionProvider; public class AgingDao { @@ -85,19 +85,18 @@ public FieldProvider[] getOpenReceivablesAgingSchedule(String strcBpartnerId, String strAccSchema, Date currentDate, String strcolumn1, String strcolumn2, String strcolumn3, String strcolumn4, String strOrg, Set<String> organizations, - String recOrPay, boolean showDoubtfulDebt, boolean excludeVoid) throws IOException, + String recOrPay, boolean showDoubtfulDebt, boolean excludeVoids) throws IOException, ServletException { // Initialization of some variables List<String> paidStatus = FIN_Utility.getListPaymentConfirmed(); HashMap<String, AgingData> agingBalanceData = new HashMap<String, AgingData>(); - FieldProvider[] data = null; + FieldProvider[] dataFP = null; List<BusinessPartner> bPartners = OBDao.getOBObjectListFromString(BusinessPartner.class, strcBpartnerId); Currency convCurrency = null; - String auxCreditPayment = ""; Organization organization = null; OBContext.setAdminMode(true); try { @@ -113,27 +112,28 @@ } OBContext.setAdminMode(true); - int scale = convCurrency.getStandardPrecision().intValue(); ScrollableResults scroll = null; + AgingDaoData dataSR = null; long init = System.currentTimeMillis(); try { - - // create a Query for retrieving the invoice without currency conversion - Query query = createObCriteria2(organizations, bPartners, strAccSchema, paidStatus, - currentDate, recOrPay, strcBpartnerId, strcolumn1, strcolumn2, strcolumn3, strcolumn4, - excludeVoid, convCurrency.getId(), showDoubtfulDebt); - System.out.println("Create Query: " + (System.currentTimeMillis() - init)); + // Amounts coming from normal PSD (non credit) + dataSR = AgingDaoData.select(new DalConnectionProvider(false), showDoubtfulDebt ? "Y" : "N", + OBDateUtils.formatDate(currentDate), convCurrency.getId(), OBDateUtils + .formatDate(convertToDate(currentDate, strcolumn1)), OBDateUtils + .formatDate(convertToDate(currentDate, strcolumn2)), OBDateUtils + .formatDate(convertToDate(currentDate, strcolumn3)), OBDateUtils + .formatDate(convertToDate(currentDate, strcolumn4)), Utility + .getInStrSet(organizations), StringUtils.equals(recOrPay, "RECEIVABLES") ? "Y" : "N", + excludeVoids ? "excludeVoids" : ""); + int i = 0; + log4j.debug("Query: " + (System.currentTimeMillis() - init)); init = System.currentTimeMillis(); - // loop the data - scroll = query.scroll(ScrollMode.FORWARD_ONLY); - init = System.currentTimeMillis(); - int i = 0; - while (scroll.next()) { - final String strBusinessPartnerId = (String) scroll.get(0); - final String strBpName = (String) scroll.get(1); - final BigDecimal psdAmt = (BigDecimal) scroll.get(2); - int intScope = (Integer) scroll.get(3); - final BigDecimal doubtfulDebtAmt = (BigDecimal) scroll.get(4); + while (dataSR.next()) { + final AgingDaoData dd = dataSR.get(); + final String strBusinessPartnerId = dd.bpid; + final String strBpName = dd.bpname; + final BigDecimal psdAmt = new BigDecimal(dd.amount); + int intScope = Integer.parseInt(dd.scope); // if there is the first time the Business Partner is inserted if (agingBalanceData.containsKey(strBusinessPartnerId)) { @@ -143,66 +143,68 @@ agingBalanceData.put(strBusinessPartnerId, new AgingData(strBusinessPartnerId, strBpName, psdAmt, intScope)); } - agingBalanceData.get(strBusinessPartnerId).addDoubtfulDebt(doubtfulDebtAmt); + i++; if (i % 100 == 0) { - // OBDal.getInstance().flush(); // No need a flush here - System.out.println("records processed: " + i); - OBDal.getInstance().getSession().clear(); + log4j.debug("records processed: " + i); + } + } + log4j.debug("Total records processed: " + i); + log4j.debug("Time to process: " + (System.currentTimeMillis() - init)); + + // Credits: In this section the Credits are going to be processed. + init = System.currentTimeMillis(); + // Query for credit payments + Query query = getCreditPaymentsInfo(organizations, bPartners, strAccSchema, paidStatus, + currentDate, recOrPay, strcBpartnerId, strcolumn1, strcolumn2, strcolumn3, strcolumn4, + excludeVoids); + scroll = query.scroll(ScrollMode.FORWARD_ONLY); + log4j.debug("Credit Query: " + (System.currentTimeMillis() - init)); + init = System.currentTimeMillis(); + i = 0; + while (scroll.next()) { + FIN_Payment payment = (FIN_Payment) scroll.get(0); + String strBusinessPartnerId = (String) scroll.get(1); + String strBpName = (String) scroll.get(2); + if (agingBalanceData.containsKey(strBusinessPartnerId)) { + // if the business partner has been inserted already + agingBalanceData.get(strBusinessPartnerId).addCredit( + getCreditLeft(payment, currentDate, convCurrency, paidStatus)); + } else { + BigDecimal creditLeft = getCreditLeft(payment, currentDate, convCurrency, paidStatus); + if (creditLeft.compareTo(BigDecimal.ZERO) != 0) { + // if there is the first time the Business Partner is inserted + agingBalanceData.put(strBusinessPartnerId, new AgingData(strBusinessPartnerId, + strBpName, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, + BigDecimal.ZERO, BigDecimal.ZERO, creditLeft, BigDecimal.ZERO)); + } } - } - - System.out.println("Total records: " + i); - - // Query for credit payments - - query = createObCriteria4(organizations, bPartners, strAccSchema, paidStatus, currentDate, - recOrPay, strcBpartnerId, strcolumn1, strcolumn2, strcolumn3, strcolumn4, excludeVoid); - - scroll = query.scroll(ScrollMode.FORWARD_ONLY); - - while (scroll.next()) { - String strPaymentId = (String) scroll.get(0); - String strBusinessPartnerId = (String) scroll.get(1); - String strBpName = (String) scroll.get(2); - if (true) { // Credits: In this section the Credits are going to be processed. - FIN_Payment payment = OBDal.getInstance().get(FIN_Payment.class, strPaymentId); - if (!strPaymentId.equals(auxCreditPayment)) { - // If the credit payment has not been processed - if (agingBalanceData.containsKey(strBusinessPartnerId)) { - // if the business partner has been inserted already - agingBalanceData.get(strBusinessPartnerId).addCredit( - getCreditLeft(payment, currentDate, convCurrency, paidStatus)); - } else { - BigDecimal creditLeft = getCreditLeft(payment, currentDate, convCurrency, paidStatus); - if (creditLeft.compareTo(BigDecimal.ZERO) != 0) { - // if there is the first time the Business Partner is inserted - agingBalanceData.put(strBusinessPartnerId, new AgingData(strBusinessPartnerId, - strBpName, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, BigDecimal.ZERO, - BigDecimal.ZERO, BigDecimal.ZERO, creditLeft, BigDecimal.ZERO)); - } - } - auxCreditPayment = strPaymentId; - } + i++; + if (i % 100 == 0) { + OBDal.getInstance().getSession().clear(); + log4j.debug("credit records processed: " + i); } } + log4j.debug("Total credit records: " + i); + log4j.debug("Time to process: " + (System.currentTimeMillis() - init)); } catch (Exception e) { log4j.error("Error", e); throw new OBException("Error", e); } finally { + dataSR.close(); scroll.close(); OBContext.restorePreviousMode(); } - List<AgingData> allData = new ArrayList<AgingData>(); - for (Entry<String, AgingData> entries : agingBalanceData.entrySet()) { - allData.add(entries.getValue()); - } - data = FieldProviderFactory.getFieldProviderArray(allData); - convertAmountsToString(data, allData); - System.out.println("Total Time: " + (System.currentTimeMillis() - init)); - return data; + init = System.currentTimeMillis(); + List<AgingData> allData = new ArrayList<AgingData>(agingBalanceData.values()); + Collections.sort(allData); + dataFP = FieldProviderFactory.getFieldProviderArray(allData); + convertAmountsToString(dataFP, allData); + + log4j.debug("Sorting and transform to Field Provider: " + (System.currentTimeMillis() - init)); + return dataFP; } /** @@ -243,8 +245,7 @@ OrganizationInformation orgInfo = OBDao.getActiveOBObjectList(psd.getOrganization(), Organization.PROPERTY_ORGANIZATIONINFORMATIONLIST) != null ? (OrganizationInformation) OBDao .getActiveOBObjectList(psd.getOrganization(), - Organization.PROPERTY_ORGANIZATIONINFORMATIONLIST).get(0) - : null; + Organization.PROPERTY_ORGANIZATIONINFORMATIONLIST).get(0) : null; if (psd.getInvoicePaymentSchedule() != null) { // Receivables/Payables: In this section the Receivables/Payables are going to be @@ -254,8 +255,8 @@ String strAcctDate = dateFormat.format(invoice.getAccountingDate()); BigDecimal convRate = null; if (!convCurrency.getId().equals(invoice.getCurrency().getId())) { - convRate = getConversionRate(invoice.getCurrencyConversionRateDocList(), invoice - .getCurrency(), convCurrency, strAcctDate); + convRate = getConversionRate(invoice.getCurrencyConversionRateDocList(), + invoice.getCurrency(), convCurrency, strAcctDate); } else { convRate = BigDecimal.ONE; } @@ -271,24 +272,24 @@ && psd.getPaymentDetails().getFinPayment().getPaymentDate().after(currentDate) && psd.getWriteoffAmount().compareTo(BigDecimal.ZERO) != 0) { if (showDoubtfulDebt && isDoubtfultDebtPreviousToDate(psd, currentDate)) { - amount = psd.getAmount().add(psd.getWriteoffAmount()).subtract( - psd.getDoubtfulDebtAmount()).multiply(convRate).setScale(scale, - BigDecimal.ROUND_HALF_UP); - doubtfulDebtAmount = psd.getDoubtfulDebtAmount().multiply(convRate).setScale(scale, - BigDecimal.ROUND_HALF_UP); + amount = psd.getAmount().add(psd.getWriteoffAmount()) + .subtract(psd.getDoubtfulDebtAmount()).multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); + doubtfulDebtAmount = psd.getDoubtfulDebtAmount().multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); } else { - amount = psd.getAmount().add(psd.getWriteoffAmount()).multiply(convRate).setScale( - scale, BigDecimal.ROUND_HALF_UP); + amount = psd.getAmount().add(psd.getWriteoffAmount()).multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); } } else { if (showDoubtfulDebt && isDoubtfultDebtPreviousToDate(psd, currentDate)) { amount = psd.getAmount().subtract(psd.getDoubtfulDebtAmount()).multiply(convRate) .setScale(scale, BigDecimal.ROUND_HALF_UP); - doubtfulDebtAmount = psd.getDoubtfulDebtAmount().multiply(convRate).setScale(scale, - BigDecimal.ROUND_HALF_UP); + doubtfulDebtAmount = psd.getDoubtfulDebtAmount().multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); } else { - amount = psd.getAmount().multiply(convRate).setScale(scale, - BigDecimal.ROUND_HALF_UP); + amount = psd.getAmount().multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); } } group = getScope(psd.getInvoicePaymentSchedule().getDueDate(), strcolumn1, strcolumn2, @@ -329,24 +330,24 @@ && psd.getPaymentDetails().getFinPayment().getPaymentDate().after(currentDate) && psd.getWriteoffAmount().compareTo(BigDecimal.ZERO) != 0) { if (showDoubtfulDebt && isDoubtfultDebtPreviousToDate(psd, currentDate)) { - amount = psd.getAmount().add(psd.getWriteoffAmount()).subtract( - psd.getDoubtfulDebtAmount()).multiply(convRate).setScale(scale, - BigDecimal.ROUND_HALF_UP); - doubtfulDebtAmount = psd.getDoubtfulDebtAmount().multiply(convRate).setScale(scale, - BigDecimal.ROUND_HALF_UP); + amount = psd.getAmount().add(psd.getWriteoffAmount()) + .subtract(psd.getDoubtfulDebtAmount()).multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); + doubtfulDebtAmount = psd.getDoubtfulDebtAmount().multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); } else { - amount = psd.getAmount().add(psd.getWriteoffAmount()).multiply(convRate).setScale( - scale, BigDecimal.ROUND_HALF_UP); + amount = psd.getAmount().add(psd.getWriteoffAmount()).multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); } } else { if (showDoubtfulDebt && isDoubtfultDebtPreviousToDate(psd, currentDate)) { amount = psd.getAmount().subtract(psd.getDoubtfulDebtAmount()).multiply(convRate) .setScale(scale, BigDecimal.ROUND_HALF_UP); - doubtfulDebtAmount = psd.getDoubtfulDebtAmount().multiply(convRate).setScale(scale, - BigDecimal.ROUND_HALF_UP); + doubtfulDebtAmount = psd.getDoubtfulDebtAmount().multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); } else { - amount = psd.getAmount().multiply(convRate).setScale(scale, - BigDecimal.ROUND_HALF_UP); + amount = psd.getAmount().multiply(convRate) + .setScale(scale, BigDecimal.ROUND_HALF_UP); } } String DocumentNo = invoice.getDocumentNo(); @@ -370,9 +371,9 @@ if (creditLeft.compareTo(BigDecimal.ZERO) != 0) { HashMap<String, String> psData = new HashMap<String, String>(); group = 6; - psData = insertData(payment.getDocumentNo(), payment.getId(), payment - .getPaymentDate(), creditLeft, payment.getBusinessPartner(), group, recOrPay - .equals("RECEIVABLES") ? paymentInTab : paymentOutTab, dateFormat, true, + psData = insertData(payment.getDocumentNo(), payment.getId(), + payment.getPaymentDate(), creditLeft, payment.getBusinessPartner(), group, + recOrPay.equals("RECEIVABLES") ? paymentInTab : paymentOutTab, dateFormat, true, BigDecimal.ZERO); hashMapList.add(psData); index++; @@ -406,11 +407,11 @@ psData.put("INVOICE_ID", id); psData.put("INVOICE_DATE", dateFormat.format(date)); psData.put("AMOUNT" + group, amount.compareTo(BigDecimal.ZERO) == 0 ? null : amount.toString()); - psData.put("DOUBTFUL_DEBT", doubtfulDebt.compareTo(BigDecimal.ZERO) == 0 ? null : doubtfulDebt - .toString()); + psData.put("DOUBTFUL_DEBT", + doubtfulDebt.compareTo(BigDecimal.ZERO) == 0 ? null : doubtfulDebt.toString()); BigDecimal percentage = calculatePercentage(amount.add(doubtfulDebt), doubtfulDebt); - psData.put("PERCENTAGE", percentage.compareTo(BigDecimal.ZERO) == 0 ? null : percentage - .toString()); + psData.put("PERCENTAGE", + percentage.compareTo(BigDecimal.ZERO) == 0 ? null : percentage.toString()); if (credits) { psData.put("SHOW_NETDUE", amount.add(doubtfulDebt).toString()); } else { @@ -517,247 +518,36 @@ return query; } - // Invoice that do not need currency conversion - private Query createObCriteria2(Set<String> organizations, List<BusinessPartner> bPartners, - String strAccSchema, List<String> paidStatus, Date currentDate, String recOrPay, - String strcBpartnerId, String strcolumn1, String strcolumn2, String strcolumn3, - String strcolumn4, boolean excludeVoid, String strCurrencyId, boolean showDoubtfulDebt) { - try { - final StringBuilder hsqlScript = new StringBuilder(); - final Session session = OBDal.getInstance().getSession(); - OBDal.getInstance().registerSQLFunction("aging_invoicecurrency_rate", - new StandardSQLFunction("aging_invoicecurrency_rate", StandardBasicTypes.STRING)); - OBDal.getInstance().registerSQLFunction("aging_isdoubtfultdebt", - new StandardSQLFunction("aging_isdoubtfultdebt", StandardBasicTypes.STRING)); - - // 0 - Business Partner - // 1 - Business Partner name - // 2 - PSD Amount - // 3 - Range - // 4 - DoubtfulDebt Amount - - hsqlScript - .append(" select coalesce(bpi.id, bpp.id), coalesce(bpi.name, bpp.name), " - + " (CASE WHEN :showDoubtfulDebt = true and " - + " aging_isdoubtfultdebt(ps.id, :asOfDate) = 'Y' " - + " THEN (aging_invoicecurrency_rate(i.id, :currency, i.currency.id) * sum(psd.amount + psd.writeoffAmount - psd.doubtfulDebtAmount)) " - + " ELSE (aging_invoicecurrency_rate(i.id, :currency, i.currency.id) * sum(psd.amount + psd.writeoffAmount)) END), "); - hsqlScript.append(" (case when trunc(ps.dueDate) > :asOfDate then 5"); - hsqlScript.append(" when trunc(ps.dueDate) > :firstRangeBucket then 4"); - hsqlScript.append(" when trunc(ps.dueDate) > :secondRangeBucket then 3"); - hsqlScript.append(" when trunc(ps.dueDate) > :thirdRangeBucket then 2"); - hsqlScript.append(" when trunc(ps.dueDate) > :fourthRangeBucket then 1"); - hsqlScript.append(" else 0"); - hsqlScript - .append(" end) as daterange, " - + "" - + " CASE WHEN :showDoubtfulDebt = true and " - + " aging_isdoubtfultdebt(ps.id, :asOfDate) = 'Y' " - + " THEN (sum(psd.doubtfulDebtAmount) * aging_invoicecurrency_rate(i.id, :currency, i.currency.id)) " - + " ELSE 0 END "); - - hsqlScript.append(" from FIN_Payment_ScheduleDetail "); - hsqlScript.append(" as psd"); - hsqlScript.append(" left outer join psd.invoicePaymentSchedule as ps"); - hsqlScript.append(" left outer join ps.invoice as i"); - hsqlScript.append(" left outer join i.businessPartner as bpi"); - hsqlScript.append(" left outer join psd.paymentDetails as pd"); - hsqlScript.append(" left outer join pd.finPayment as p"); - hsqlScript.append(" left outer join p.businessPartner as bpp"); - hsqlScript.append(" where psd.active=true"); - hsqlScript.append(" and psd.canceled = false"); - hsqlScript.append(" and psd.organization.id in :organizations"); - // Receivables / Payables - // PaymentScheduleDetail has an invoice - hsqlScript.append(" and (psd.invoicePaymentSchedule is not null"); - if (excludeVoid) - hsqlScript.append(" and i.documentStatus <> 'VO'"); - // Issotrx - hsqlScript.append(" and i.salesTransaction = :recOrPay"); - - // Business Partner filter - if (bPartners.size() > 0) { - hsqlScript.append(" and bpi.id in " + strcBpartnerId); - } - // invoice accounting date is before as of date - hsqlScript.append(" and trunc(i.accountingDate) <= :asOfDate"); - // PaymentScheduleDetail is not fully paid - hsqlScript.append(" and (psd.paymentDetails is null "); - // or the payment is not executed - hsqlScript.append(" or psd.invoicePaid='N'"); - // or the payment is executed, but after as of date - hsqlScript.append(" or (psd.invoicePaid='Y' and trunc(p.paymentDate) > :asOfDate))"); - hsqlScript.append(" )"); - // Order by Business Partner name, DueDate, Accounting Date and Document Number - hsqlScript.append(" group by coalesce(bpi.id, bpp.id), "); - // Order by ID, see issue 26115: is not grouping if there are more than one business - // partner with the same name - hsqlScript.append(" coalesce(bpi.name, bpp.name), 4, ps.id, i.id, i.currency.id "); - hsqlScript.append(" order by coalesce(bpi.id, bpp.id) "); - - final Query query = session.createQuery(hsqlScript.toString()); - query.setParameterList("organizations", organizations); - query.setDate("asOfDate", currentDate); - query.setDate("firstRangeBucket", convertToDate(currentDate, strcolumn1)); - query.setDate("secondRangeBucket", convertToDate(currentDate, strcolumn2)); - query.setDate("thirdRangeBucket", convertToDate(currentDate, strcolumn3)); - query.setDate("fourthRangeBucket", convertToDate(currentDate, strcolumn4)); - if (recOrPay.equals("RECEIVABLES")) { - query.setParameter("recOrPay", true); - } else { - // PAYABLES - query.setParameter("recOrPay", false); - } - query.setParameter("currency", strCurrencyId); - query.setParameter("showDoubtfulDebt", showDoubtfulDebt); - // List myLust = query.list(); - - return query; - } catch (Exception e) { - throw new OBException("Error", e); - } - } - - // Invoice that needs currency conversion - private Query createObCriteria3(Set<String> organizations, List<BusinessPartner> bPartners, - String strAccSchema, List<String> paidStatus, Date currentDate, String recOrPay, - String strcBpartnerId, String strcolumn1, String strcolumn2, String strcolumn3, - String strcolumn4, boolean excludeVoid, String strCurrencyId) { - try { - final StringBuilder hsqlScript = new StringBuilder(); - final Session session = OBDal.getInstance().getSession(); - OBDal.getInstance().registerSQLFunction("ad_column_identifier_std", - new StandardSQLFunction("c_invoicecurrency_rate", StandardBasicTypes.STRING)); - - // 0 - Business Partner - // 1 - Business Partner name - // 2 - PSD Amount - // 3 - Write Off Amount - // 4 - DoubtfulDebt Amount - // 5 - Range - - hsqlScript - .append(" select coalesce(bpi.id, bpp.id), coalesce(bpi.name, bpp.name), " - + " (aging_invoicecurrency_rate(i.id, :currency, i.currency.id) * sum(psd.amount)) as psd, " - + " aging_invoicecurrency_rate(i.id, :currency, i.currency.id) * sum(psd.writeoffAmount), "); - - hsqlScript.append(" (case when trunc(ps.dueDate) > :asOfDate then 5"); - hsqlScript.append(" when trunc(ps.dueDate) > :firstRangeBucket then 4"); - hsqlScript.append(" when trunc(ps.dueDate) > :secondRangeBucket then 3"); - hsqlScript.append(" when trunc(ps.dueDate) > :thirdRangeBucket then 2"); - hsqlScript.append(" when trunc(ps.dueDate) > :fourthRangeBucket then 1"); - hsqlScript.append(" else 0"); - hsqlScript - .append(" end) as daterange, aging_invoicecurrency_rate(i.id, :currency, i.currency.id) * sum(psd.doubtfulDebtAmount) "); - - hsqlScript.append(" from FIN_Payment_ScheduleDetail "); - hsqlScript.append(" as psd"); - hsqlScript.append(" left outer join psd.invoicePaymentSchedule as ps"); - hsqlScript.append(" left outer join ps.invoice as i"); - hsqlScript.append(" left outer join i.businessPartner as bpi"); - hsqlScript.append(" left outer join psd.paymentDetails as pd"); - hsqlScript.append(" left outer join pd.finPayment as p"); - hsqlScript.append(" left outer join p.businessPartner as bpp"); - hsqlScript.append(" where psd.active=true"); - hsqlScript.append(" and psd.canceled = false"); - hsqlScript.append(" and psd.organization.id in :organizations"); - // Receivables / Payables - // PaymentScheduleDetail has an invoice - hsqlScript.append(" and (psd.invoicePaymentSchedule is not null"); - if (excludeVoid) - hsqlScript.append(" and i.documentStatus <> 'VO'"); - // Issotrx - hsqlScript.append(" and i.salesTransaction = :recOrPay"); - // Currency - hsqlScript.append(" and i.currency.id <> :currency"); - - // Business Partner filter - if (bPartners.size() > 0) { - hsqlScript.append(" and bpi.id in " + strcBpartnerId); - } - // invoice accounting date is before as of date - hsqlScript.append(" and trunc(i.accountingDate) <= :asOfDate"); - // PaymentScheduleDetail is not fully paid - hsqlScript.append(" and (psd.paymentDetails is null "); - // or the payment is not executed - hsqlScript.append(" or psd.invoicePaid='N'"); - // or the payment is executed, but after as of date - hsqlScript.append(" or (psd.invoicePaid='Y' and trunc(p.paymentDate) > :asOfDate))"); - hsqlScript.append(" )"); - // Order by Business Partner name, DueDate, Accounting Date and Document Number - hsqlScript.append(" group by coalesce(bpi.id, bpp.id), "); - // Order by ID, see issue 26115: is not grouping if there are more than one business - // partner with the same name - hsqlScript.append(" coalesce(bpi.name, bpp.name), 5, i.id, i.currency.id "); - hsqlScript.append(" order by coalesce(bpi.id, bpp.id) "); - - final Query query = OBDal.getInstance().getSession().createQuery(hsqlScript.toString()); - query.setParameterList("organizations", organizations); - query.setDate("asOfDate", currentDate); - query.setDate("firstRangeBucket", convertToDate(currentDate, strcolumn1)); - query.setDate("secondRangeBucket", convertToDate(currentDate, strcolumn2)); - query.setDate("thirdRangeBucket", convertToDate(currentDate, strcolumn3)); - query.setDate("fourthRangeBucket", convertToDate(currentDate, strcolumn4)); - if (recOrPay.equals("RECEIVABLES")) { - query.setParameter("recOrPay", true); - } else { - // PAYABLES - query.setParameter("recOrPay", false); - } - query.setParameter("currency", strCurrencyId); - // List myLust = query.list(); - - return query; - } catch (Exception e) { - throw new OBException("Error", e); - } - } - // Credit Payments - private Query createObCriteria4(Set<String> organizations, List<BusinessPartner> bPartners, + private Query getCreditPaymentsInfo(Set<String> organizations, List<BusinessPartner> bPartners, String strAccSchema, List<String> paidStatus, Date currentDate, String recOrPay, String strcBpartnerId, String strcolumn1, String strcolumn2, String strcolumn3, String strcolumn4, boolean excludeVoid) { try { final StringBuilder hsqlScript = new StringBuilder(); - // 0 - Business Partner - // 1 - Business Partner name - // 2 - PSD Amount - // 3 - Write Off Amount - // 4 - DoubtfulDebt Amount - // 5 - Range + // 0 - Payment Id + // 1 - Business Partner Id + // 2 - Business Partner Name - hsqlScript.append(" select p.id, bpp.id, bpp.name "); - hsqlScript.append(" from FIN_Payment_ScheduleDetail "); - hsqlScript.append(" as psd"); - hsqlScript.append(" left outer join psd.paymentDetails as pd"); - hsqlScript.append(" left outer join pd.finPayment as p"); - hsqlScript.append(" left outer join p.businessPartner as bpp"); - hsqlScript.append(" where psd.active=true"); - hsqlScript.append(" and psd.canceled = false"); - hsqlScript.append(" and psd.organization.id in :organizations"); - // Receivables / Payables - // PaymentScheduleDetail has an invoice - hsqlScript.append(" and (("); - // Credit generated by Payments - // PaymentScheduleDetail has a payment and PaymentSchedule (Invoice) is null - hsqlScript.append(" psd.paymentDetails is not null"); - hsqlScript.append(" and psd.invoicePaymentSchedule is null"); - // Payment is confirmed and payment date is <= as of Date - hsqlScript.append(" and (p.status in :paidStatus and trunc(p.paymentDate) <= :asOfDate)"); - // Issotrx - hsqlScript.append(" and p.receipt = :recOrPay"); - // Business Partner filter + hsqlScript.append(" select p, bpp.id, bpp.name "); + hsqlScript.append(" from FIN_Payment as p "); + hsqlScript.append(" inner join p.businessPartner as bpp "); + hsqlScript.append(" where p.organization.id in :organizations"); + hsqlScript.append(" and (p.status in :paidStatus and trunc(p.paymentDate) <= :asOfDate)"); + hsqlScript.append(" and p.receipt = :recOrPay"); if (bPartners.size() > 0) { - hsqlScript.append(" and bpp.id in " + strcBpartnerId); + hsqlScript.append(" and bpp.id in " + strcBpartnerId); } - // The Payment has to be related to a Business Partner - hsqlScript.append(" and p.businessPartner is not null"); - // The generated credit of the Payment has to be <> 0 - hsqlScript.append(" and p.generatedCredit <> 0))"); - // Order by Business Partner name, DueDate, Accounting Date and Document Number - hsqlScript.append(" order by p.id, bpp.id "); + hsqlScript.append(" and p.generatedCredit <> 0 "); + hsqlScript.append(" and exists (select 1 "); + hsqlScript.append(" from FIN_Payment_ScheduleDetail as psd "); + hsqlScript.append(" inner join psd.paymentDetails as pd "); + hsqlScript.append(" where pd.finPayment.id = p.id "); + hsqlScript.append(" and psd.active=true "); + hsqlScript.append(" and psd.canceled = false "); + hsqlScript.append(" and psd.invoicePaymentSchedule is null"); + hsqlScript.append(" )"); final Query query = OBDal.getInstance().getSession().createQuery(hsqlScript.toString()); query.setParameterList("organizations", organizations); @@ -770,8 +560,6 @@ query.setParameter("recOrPay", false); } - // List myLust = query.list(); - return query; } catch (Exception e) { throw new OBException("Error", e); @@ -931,8 +719,8 @@ private boolean isDoubtfultDebtPreviousToDate(FIN_PaymentScheduleDetail psd, Date currentDate) { OBCriteria<DoubtfulDebt> obcDD = OBDal.getInstance().createCriteria(DoubtfulDebt.class); - obcDD.add(Restrictions.eq(DoubtfulDebt.PROPERTY_FINPAYMENTSCHEDULE, psd - .getInvoicePaymentSchedule())); + obcDD.add(Restrictions.eq(DoubtfulDebt.PROPERTY_FINPAYMENTSCHEDULE, + psd.getInvoicePaymentSchedule())); DoubtfulDebt dd = (DoubtfulDebt) obcDD.uniqueResult(); if (dd != null && (!dd.getFINDoubtfulDebtRun().getRundate().after(currentDate))) { return true; diff --git a/src/org/openbravo/agingbalance/ad_reports/AgingDao_data.xsql b/src/org/openbravo/agingbalance/ad_reports/AgingDao_data.xsql new file mode 100644 --- /dev/null +++ b/src/org/openbravo/agingbalance/ad_reports/AgingDao_data.xsql @@ -0,0 +1,82 @@ +<?xml version="1.0" encoding="UTF-8" ?> + + +<SqlClass name="AgingDaoData" package="org.openbravo.agingbalance.ad_reports"> + <SqlClassComment></SqlClassComment> + <SqlMethod name="select" type="preparedStatement" return="scrollable"> + <SqlMethodComment></SqlMethodComment> + <Sql> + <![CDATA[ + SELECT A.bpId, bp.name as bpName, SUM(A.amount) as amount, A.scope, 0 as credit + FROM ( + SELECT i.c_bpartner_id AS bpId, + CASE + WHEN to_char('Y') = ? AND psd.DOUBTFULDEBT_AMOUNT <> 0 AND AGING_ISDOUBTFULTDEBT(psd.fin_payment_scheduledetail_id, TO_DATE(?)) = 'Y' THEN Aging_invoicecurrency_rate(i.c_invoice_id, ?, i.c_currency_id) * (psd.amount + psd.writeoffamt - psd.DOUBTFULDEBT_AMOUNT) + ELSE Aging_invoicecurrency_rate(i.c_invoice_id, ?, i.c_currency_id) * (psd.amount + psd.writeoffamt) + END AS amount, + CASE + WHEN Trunc(ps.duedate) > TO_DATE(?) THEN 0 + WHEN Trunc(ps.duedate) > TO_DATE(?) THEN 1 + WHEN Trunc(ps.duedate) > TO_DATE(?) THEN 2 + WHEN Trunc(ps.duedate) > TO_DATE(?) THEN 3 + WHEN Trunc(ps.duedate) > TO_DATE(?) THEN 4 + ELSE 5 + END AS scope + FROM fin_payment_scheduledetail psd + INNER JOIN fin_payment_schedule ps ON psd.fin_payment_schedule_invoice = ps.fin_payment_schedule_id + INNER JOIN c_invoice i ON ps.c_invoice_id = i.c_invoice_id + WHERE psd.isactive = 'Y' + AND psd.iscanceled = 'N' + AND (psd.AD_Org_ID in ('1')) + AND (i.AD_Org_ID in ('1')) + AND i.issotrx = ? + AND 1=1 + AND Trunc(i.dateacct) <= TO_DATE(?) + AND ( psd.fin_payment_detail_id IS NULL + OR psd.isinvoicepaid = 'N' + OR psd.isinvoicepaid = 'Y' + AND ( EXISTS (SELECT 1 + FROM fin_payment_detail pd + INNER JOIN fin_payment p ON pd.fin_payment_id = p.fin_payment_id + WHERE Trunc(p.paymentdate) > TO_DATE(?) + AND pd.fin_payment_detail_id = psd.fin_payment_detail_id) ) ) + ) A + INNER JOIN c_bpartner bp on (A.bpId = bp.c_bpartner_id) + GROUP BY A.bpId, bp.name, A.scope + ]]></Sql> + <Field name="rownum" value="count"/> + <Parameter name="showDoubtfulDebt"/> + <Parameter name="asOfDate"/> + <Parameter name="currencyId"/> + <Parameter name="currencyId"/> + <Parameter name="asOfDate"/> + <Parameter name="firstRangeBucket"/> + <Parameter name="secondRangeBucket"/> + <Parameter name="thirdRangeBucket"/> + <Parameter name="fourthRangeBucket"/> + <Parameter name="org" type="replace" optional="true" after="AND (psd.AD_Org_ID in (" text="'1'"/> + <Parameter name="org" type="replace" optional="true" after="AND (i.AD_Org_ID in (" text="'1'"/> + <Parameter name="recOrPay"/> + <Parameter name="excludeVoids" type="none" optional="true" after="AND 1=1" text="AND i.DOCSTATUS NOT IN ('VO')"/> + <Parameter name="asOfDate"/> + <Parameter name="asOfDate"/> + </SqlMethod> +</SqlClass> + diff --git a/src/org/openbravo/agingbalance/ad_reports/AgingData.java b/src/org/openbravo/agingbalance/ad_reports/AgingData.java --- a/src/org/openbravo/agingbalance/ad_reports/AgingData.java +++ b/src/org/openbravo/agingbalance/ad_reports/AgingData.java @@ -11,7 +11,7 @@ * under the License. * The Original Code is Openbravo ERP. * The Initial Developer of the Original Code is Openbravo SLU - * All portions are Copyright (C) 2012 Openbravo SLU + * All portions are Copyright (C) 2012-2016 Openbravo SLU * All Rights Reserved. * Contributor(s): ______________________________________. ************************************************************************ @@ -22,7 +22,7 @@ import java.math.BigDecimal; import java.math.RoundingMode; -public class AgingData { +public class AgingData implements Comparable<AgingData> { private String bPartnerID; private String bPartner; @@ -220,4 +220,9 @@ return doubtfulDebtAmount.divide(totalAmount, 5, RoundingMode.HALF_UP).multiply( new BigDecimal("100")); } + + @Override + public int compareTo(AgingData arg0) { + return bPartner.compareToIgnoreCase(arg0.getBPartner()); + } } diff --git a/src/org/openbravo/agingbalance/ad_reports/AgingProcess.java b/src/org/openbravo/agingbalance/ad_reports/AgingProcess.java --- a/src/org/openbravo/agingbalance/ad_reports/AgingProcess.java +++ b/src/org/openbravo/agingbalance/ad_reports/AgingProcess.java @@ -244,6 +244,8 @@ new OrganizationStructureProvider().getChildTree(strOrgTrx, true), recOrPay, showDoubtful, excludeVoid); + long init = System.currentTimeMillis(); + // Set the Parameters AcctSchema acctSchema = null; Organization organization = null; @@ -300,6 +302,8 @@ } parameters.put("showDoubtfulDebt", strDoubtful); parameters.put("void", strVoid); + log4j.debug("Parameters: " + (System.currentTimeMillis() - init)); + init = System.currentTimeMillis(); String strReportName = ""; @@ -324,7 +328,7 @@ } else { renderJR(vars, response, strReportName, strOutput, parameters, data, null); } - + log4j.debug("Render: " + (System.currentTimeMillis() - init)); } catch (OBException ex) { if (ex.getMessage().equals("No Conversion Rate")) { final OBError message = new OBError();

Notes
(0085464) aferraz (manager) 2016-04-06 11:19	We are currently working on it

(0086188) hgbot (developer) 2016-05-04 18:51	Repository: erp/mods/org.openbravo.agingbalance Changeset: f23883edecf463326bbea99872df577cf0f401e3 Author: David Miguelez <david.miguelez <at> openbravo.com> Date: Wed May 04 17:58:24 2016 +0200 URL: http://code.openbravo.com/erp/mods/org.openbravo.agingbalance/rev/f23883edecf463326bbea99872df577cf0f401e3 [^] Fixes Issue 32406. Refactor to improve performance Replaced HQL queries by complex SQL queries that return the data almost already processed. Created some SQL functions to directly process the currency conversion and doubtful debt stuff directly in SQL instead of Java. That might increase the time to run the query, but on the other hand it drastically reduces the number of records to be processed in Java and thus the time to process. In several high volume instances, the time to get the report has been reduced by more than a third part. --- M src-db/database/sourcedata/AD_MODULE.xml M src/org/openbravo/agingbalance/ad_reports/AgingDao.java M src/org/openbravo/agingbalance/ad_reports/AgingData.java M src/org/openbravo/agingbalance/ad_reports/AgingProcess.java A src-db/database/model/functions/AGING_INVOICECURRENCY_RATE.xml A src-db/database/model/functions/AGING_ISDOUBTFULTDEBT.xml A src-db/database/model/functions/AGING_PAYMENTCURRENCY_RATE.xml A src-db/database/model/modifiedTables/FIN_DOUBTFUL_DEBT.xml A src/org/openbravo/agingbalance/ad_reports/AgingDao_data.xsql ---

(0086189) dmiguelez (developer) 2016-05-04 18:52	Code Review + Testing Ok

Issue History
Date Modified	Username	Field	Change
2016-03-04 09:55	yogaskarnik	New Issue
2016-03-04 09:55	yogaskarnik	Assigned To	=> Triage Finance
2016-03-04 09:55	yogaskarnik	Modules	=> Core
2016-03-04 09:55	yogaskarnik	Resolution time	=> 1459893600
2016-03-04 09:55	yogaskarnik	Triggers an Emergency Pack	=> No
2016-03-04 09:57	yogaskarnik	Steps to Reproduce Updated	View Revisions
2016-04-05 12:08	aferraz	Assigned To	Triage Finance => AtulOpenbravo
2016-04-06 11:18	aferraz	Assigned To	AtulOpenbravo => vmromanos
2016-04-06 11:18	aferraz	Type	defect => design defect
2016-04-06 11:19	aferraz	Note Added: 0085464
2016-04-06 11:20	vmromanos	Project	Openbravo ERP => Modules
2016-04-06 11:21	vmromanos	Category	09. Financial management => Payment Aging Balance Report
2016-04-07 12:12	vmromanos	File Added: againg_refactor_draft.diff
2016-04-07 12:12	vmromanos	Status	new => acknowledged
2016-04-20 10:12	egoitz	Relationship added	related to 0024343
2016-04-20 10:12	egoitz	Relationship added	blocks 0032174
2016-05-04 18:51	hgbot	Checkin
2016-05-04 18:51	hgbot	Note Added: 0086188
2016-05-04 18:51	hgbot	Status	acknowledged => resolved
2016-05-04 18:51	hgbot	Resolution	open => fixed
2016-05-04 18:51	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/mods/org.openbravo.agingbalance/rev/f23883edecf463326bbea99872df577cf0f401e3 [^]
2016-05-04 18:52	dmiguelez	Review Assigned To	=> dmiguelez
2016-05-04 18:52	dmiguelez	Note Added: 0086189
2016-05-04 18:52	dmiguelez	Status	resolved => closed
2016-06-15 18:09	aferraz	Relationship added	causes 0033278