details: https://code.openbravo.com/erp/devel/pi/rev/35bc60aaf9f1 changeset: 13492:35bc60aaf9f1 user: Ioritz Cia <ioritz.cia <at> openbravo.com> date: Wed Aug 10 10:59:33 2011 +0200 summary: Fixes issue 18211: in payment report, the payments are repeated.
details: https://code.openbravo.com/erp/devel/pi/rev/0d9d11f4ab72 changeset: 13493:0d9d11f4ab72 user: Ioritz Cia <ioritz.cia <at> openbravo.com> date: Wed Aug 10 11:48:32 2011 +0200 summary: Fixes issue 17995: The Payment Report does not filter properly by Due Date. diffstat: modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReport.java | 2 +- modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java | 178 ++++++++- 2 files changed, 147 insertions(+), 33 deletions(-) diffs (254 lines): diff -r 51047ceddf8f -r 0d9d11f4ab72 modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReport.java --- a/modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReport.java Wed Aug 10 11:29:52 2011 +0200 +++ b/modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReport.java Wed Aug 10 11:48:32 2011 +0200 @@ -261,7 +261,7 @@ strFinancialAccountId, strcCurrency, strConvertCurrency, strConversionDate, strPaymType, strOverdue, strGroupCrit, strOrdCrit); - if (data.length == 1 && data[0] == null) { + if (data.length == 0) { discardAL.add("sectionGroupCrit"); discardAL.add("sectionStatus"); diff -r 51047ceddf8f -r 0d9d11f4ab72 modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java --- a/modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java Wed Aug 10 11:29:52 2011 +0200 +++ b/modules/org.openbravo.financial.paymentreport/src/org/openbravo/financial/paymentreport/erpCommon/ad_reports/PaymentReportDao.java Wed Aug 10 11:48:32 2011 +0200 @@ -91,6 +91,7 @@ Currency transCurrency; BigDecimal transAmount = null; ConversionRate convRate = null; + ArrayList<FieldProvider> groupedData = new ArrayList<FieldProvider>(); OBContext.setAdminMode(); try { @@ -142,34 +143,16 @@ if (!strDueDateFrom.isEmpty()) { hsqlScript.append(" and invps."); hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE); - hsqlScript.append(" > ?"); + hsqlScript.append(" >= ?"); parameters.add(FIN_Utility.getDate(strDueDateFrom)); } if (!strDueDateTo.isEmpty()) { hsqlScript.append(" and invps."); hsqlScript.append(FIN_PaymentSchedule.PROPERTY_DUEDATE); - hsqlScript.append(" < ?"); + hsqlScript.append(" <= ?"); parameters.add(FIN_Utility.getDate(strDueDateTo)); } - // amount from - amount to - if (!strAmountFrom.isEmpty()) { - hsqlScript.append(" and coalesce(pay."); - hsqlScript.append(FIN_Payment.PROPERTY_AMOUNT); - hsqlScript.append(", invps."); - hsqlScript.append(FIN_PaymentSchedule.PROPERTY_AMOUNT); - hsqlScript.append(") > "); - hsqlScript.append(strAmountFrom); - } - if (!strAmountTo.isEmpty()) { - hsqlScript.append(" and coalesce(pay."); - hsqlScript.append(FIN_Payment.PROPERTY_AMOUNT); - hsqlScript.append(", invps."); - hsqlScript.append(FIN_PaymentSchedule.PROPERTY_AMOUNT); - hsqlScript.append(") < "); - hsqlScript.append(strAmountTo); - } - // document date from - document date to if (!strDocumentDateFrom.isEmpty()) { hsqlScript.append(" and coalesce(inv."); @@ -391,8 +374,16 @@ FIN_PaymentScheduleDetail[] FIN_PaymentScheduleDetail = new FIN_PaymentScheduleDetail[0]; FIN_PaymentScheduleDetail = obqPSDList.toArray(FIN_PaymentScheduleDetail); + FIN_PaymentDetail finPaymDetail; + Boolean mustGroup; + String previousFPSDInvoiceId = null; + String previousPaymentId = null; + BigDecimal amountSum = BigDecimal.ZERO; + FieldProvider previousRow = null; + ConversionRate previousConvRate = null; long milisecDayConv = (1000 * 60 * 60 * 24); boolean isReceipt = false; + boolean isAmtInLimit = false; for (int i = 0; i < data.length; i++) { if (FIN_PaymentScheduleDetail[i].getPaymentDetails() != null) { @@ -562,6 +553,7 @@ boolean sameCurrency = baseCurrency.getISOCode().equalsIgnoreCase( transCurrency.getISOCode()); + if (!sameCurrency) { convRate = this.getConversionRate(transCurrency, baseCurrency, strConversionDate); @@ -605,28 +597,150 @@ // baseCurrency FieldProviderFactory.setField(data[i], "TRANS_CURRENCY", transCurrency.getISOCode()); + finPaymDetail = FIN_PaymentScheduleDetail[i].getPaymentDetails(); + + // Payment Schedule Detail grouping criteria + if (finPaymDetail != null + && FIN_PaymentScheduleDetail[i].getInvoicePaymentSchedule() != null) { + mustGroup = finPaymDetail.getFinPayment().getId().equalsIgnoreCase(previousPaymentId) + && FIN_PaymentScheduleDetail[i].getInvoicePaymentSchedule().getId() + .equalsIgnoreCase(previousFPSDInvoiceId); + previousFPSDInvoiceId = FIN_PaymentScheduleDetail[i].getInvoicePaymentSchedule().getId(); + previousPaymentId = finPaymDetail.getFinPayment().getId(); + } else if (finPaymDetail != null + && FIN_PaymentScheduleDetail[i].getInvoicePaymentSchedule() == null) { + mustGroup = finPaymDetail.getFinPayment().getId().equalsIgnoreCase(previousPaymentId) + && previousFPSDInvoiceId == null; + previousPaymentId = finPaymDetail.getFinPayment().getId(); + previousFPSDInvoiceId = null; + } else if (finPaymDetail == null + && FIN_PaymentScheduleDetail[i].getInvoicePaymentSchedule() != null) { + mustGroup = previousPaymentId == null + && FIN_PaymentScheduleDetail[i].getInvoicePaymentSchedule().getId() + .equalsIgnoreCase(previousFPSDInvoiceId); + previousPaymentId = null; + previousFPSDInvoiceId = FIN_PaymentScheduleDetail[i].getInvoicePaymentSchedule().getId(); + } else { + mustGroup = false; + } + + if (mustGroup) { + amountSum = amountSum.add(transAmount); + } else { + if (previousRow != null) { + // The current row has nothing to do with the previous one. Because of that, the + // previous row has to be added to grouped data. + if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y")) + FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.toString()); + else + FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.negate() + .toString()); + if (previousConvRate == null) { + if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y")) + FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.toString()); + else + FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.negate() + .toString()); + } else { + if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y")) + FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", + amountSum.multiply(previousConvRate.getMultipleRateBy()).toString()); + else + FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", + amountSum.multiply(previousConvRate.getMultipleRateBy()).negate().toString()); + } + + if (strAmountFrom.isEmpty() && strAmountTo.isEmpty()) { + isAmtInLimit = true; + } else if (!strAmountFrom.isEmpty() && strAmountTo.isEmpty()) { + isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double + .parseDouble(strAmountFrom); + } else if (strAmountFrom.isEmpty() && !strAmountTo.isEmpty()) { + isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double + .parseDouble(strAmountTo); + } else { + isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double + .parseDouble(strAmountFrom) + && Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double + .parseDouble(strAmountTo); + } + if (isAmtInLimit) { + groupedData.add(previousRow); + isAmtInLimit = false; + } + } + previousRow = data[i]; + previousConvRate = convRate; + amountSum = transAmount; + } + // group_crit_id this is the column that has the ids of the grouping criteria selected if (strGroupCrit.equalsIgnoreCase("APRM_FATS_BPARTNER")) { - FieldProviderFactory.setField(data[i], "GROUP_CRIT_ID", data[i].getField("BPARTNER")); - FieldProviderFactory.setField(data[i], "GROUP_CRIT", "Business Partner"); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", + previousRow.getField("BPARTNER")); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Business Partner"); } else if (strGroupCrit.equalsIgnoreCase("Project")) { - FieldProviderFactory.setField(data[i], "GROUP_CRIT_ID", data[i].getField("PROJECT")); - FieldProviderFactory.setField(data[i], "GROUP_CRIT", "Project"); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", + previousRow.getField("PROJECT")); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Project"); } else if (strGroupCrit.equalsIgnoreCase("FINPR_BPartner_Category")) { - FieldProviderFactory.setField(data[i], "GROUP_CRIT_ID", data[i].getField("BP_GROUP")); - FieldProviderFactory.setField(data[i], "GROUP_CRIT", "Business Partner Category"); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", + previousRow.getField("BP_GROUP")); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Business Partner Category"); } else if (strGroupCrit.equalsIgnoreCase("INS_CURRENCY")) { - FieldProviderFactory.setField(data[i], "GROUP_CRIT_ID", - data[i].getField("TRANS_CURRENCY")); - FieldProviderFactory.setField(data[i], "GROUP_CRIT", "Currency"); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", + previousRow.getField("TRANS_CURRENCY")); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT", "Currency"); } else { - FieldProviderFactory.setField(data[i], "GROUP_CRIT_ID", ""); + FieldProviderFactory.setField(previousRow, "GROUP_CRIT_ID", ""); + } + + } + if (previousRow != null) { + // The current row has nothing to do with the previous one. Because of that, the + // previous row has to be added to grouped data. + if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y")) + FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.toString()); + else + FieldProviderFactory.setField(previousRow, "TRANS_AMOUNT", amountSum.negate().toString()); + if (previousConvRate == null) { + if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y")) + FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", amountSum.toString()); + else + FieldProviderFactory + .setField(previousRow, "BASE_AMOUNT", amountSum.negate().toString()); + } else { + if (previousRow.getField("ISRECEIPT").equalsIgnoreCase("Y")) + FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", + amountSum.multiply(previousConvRate.getMultipleRateBy()).toString()); + else + FieldProviderFactory.setField(previousRow, "BASE_AMOUNT", + amountSum.multiply(previousConvRate.getMultipleRateBy()).negate().toString()); + } + + if (strAmountFrom.isEmpty() && strAmountTo.isEmpty()) { + isAmtInLimit = true; + } else if (!strAmountFrom.isEmpty() && strAmountTo.isEmpty()) { + isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double + .parseDouble(strAmountFrom); + } else if (strAmountFrom.isEmpty() && !strAmountTo.isEmpty()) { + isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double + .parseDouble(strAmountTo); + } else { + isAmtInLimit = Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) >= Double + .parseDouble(strAmountFrom) + && Double.parseDouble(previousRow.getField("TRANS_AMOUNT")) <= Double + .parseDouble(strAmountTo); + } + if (isAmtInLimit) { + groupedData.add(previousRow); + isAmtInLimit = false; } } } finally { OBContext.restorePreviousMode(); } - return data; + return (FieldProvider[]) groupedData.toArray(new FieldProvider[groupedData.size()]); } public ConversionRate getConversionRate(Currency transCurrency, Currency baseCurrency, @@ -799,4 +913,4 @@ } return bp; } -} \ No newline at end of file +} ------------------------------------------------------------------------------ uberSVN's rich system and user administration capabilities and model configuration take the hassle out of deploying and managing Subversion and the tools developers use with it. Learn more about uberSVN and get a free download at: http://p.sf.net/sfu/wandisco-dev2dev _______________________________________________ Openbravo-commits mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/openbravo-commits
