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 @@ + + + - - - - 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 @@ + + + + + + +

+ 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 organizations, - String recOrPay, boolean showDoubtfulDebt, boolean excludeVoid) throws IOException, + String recOrPay, boolean showDoubtfulDebt, boolean excludeVoids) throws IOException, ServletException { // Initialization of some variables List paidStatus = FIN_Utility.getListPaymentConfirmed(); HashMap agingBalanceData = new HashMap(); - FieldProvider[] data = null; + FieldProvider[] dataFP = null; List 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 allData = new ArrayList(); - for (Entry 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 allData = new ArrayList(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 psData = new HashMap(); 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 organizations, List bPartners, - String strAccSchema, List 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 organizations, List bPartners, - String strAccSchema, List 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 organizations, List bPartners, + private Query getCreditPaymentsInfo(Set organizations, List bPartners, String strAccSchema, List 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 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 @@ + + + + + + + + + 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 + ]]> + + + + + + + + + + + + + + + + + + + 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 { 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();