details: https://code.openbravo.com/erp/devel/pi/rev/3e75e4d92c64
changeset: 23416:3e75e4d92c64
user: Eduardo Argal Guibert <eduardo.argal <at> openbravo.com>
date: Mon Jun 02 12:50:56 2014 +0200
summary: Fixes issue 25976: Open Balances
diffstat:
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_PROCESS.xml
| 24 +
modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/RecordID2Filling.java
| 461 +
referencedata/sampledata/F_B_International_Group/FACT_ACCT.xml
| 21427 ++++++---
src-db/database/model/tables/FACT_ACCT.xml
| 13 +
src-db/database/sourcedata/AD_COLUMN.xml
| 45 +-
src-db/database/sourcedata/AD_ELEMENT.xml
| 14 +
src-db/database/sourcedata/AD_TEXTINTERFACES.xml
| 11 +
src-test/org/openbravo/test/AllAntTaskTests.java
| 4 +
src-test/org/openbravo/test/accounting/RecordID2Test.java
| 663 +
src/org/openbravo/erpCommon/ad_forms/AcctServer.java
| 2 +
src/org/openbravo/erpCommon/ad_forms/DocFINFinAccTransaction.java
| 11 +-
src/org/openbravo/erpCommon/ad_forms/DocFINPayment.java
| 8 +-
src/org/openbravo/erpCommon/ad_forms/DocFINReconciliation.java
| 14 +-
src/org/openbravo/erpCommon/ad_forms/DocInvoice.java
| 2 +-
src/org/openbravo/erpCommon/ad_forms/DocInvoice_data.xsql
| 10 +-
src/org/openbravo/erpCommon/ad_forms/FactLine.java
| 6 +
src/org/openbravo/erpCommon/ad_forms/FactLine_data.xsql
| 25 +
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.html
| 3 +
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.java
| 45 +-
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger.xml
| 2 +
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedger_data.xsql
| 2 +
src/org/openbravo/financial/ResetAccounting.java
| 14 +-
22 files changed, 14675 insertions(+), 8131 deletions(-)
diffs (truncated from 65242 to 300 lines):
diff -r 123836b0013c -r 3e75e4d92c64
modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_PROCESS.xml
---
a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_PROCESS.xml
Mon Jun 02 12:57:42 2014 +0200
+++
b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_PROCESS.xml
Mon Jun 02 12:50:56 2014 +0200
@@ -345,6 +345,30 @@
<!--7AC7BE9024E448A0BB863C159DA762F9-->
<IS_EXPLICIT_ACCESS><![CDATA[N]]></IS_EXPLICIT_ACCESS>
<!--7AC7BE9024E448A0BB863C159DA762F9--></AD_PROCESS>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--><AD_PROCESS>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<AD_PROCESS_ID><![CDATA[AA64E3BA23F24D4F8E6B06970500FC70]]></AD_PROCESS_ID>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <AD_ORG_ID><![CDATA[0]]></AD_ORG_ID>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <ISACTIVE><![CDATA[Y]]></ISACTIVE>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <VALUE><![CDATA[recordid2]]></VALUE>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <NAME><![CDATA[Open Balances: Review
historical data]]></NAME>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <DESCRIPTION><![CDATA[Review
historical data to fulfill required information for 'Open Balances'
project]]></DESCRIPTION>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <HELP><![CDATA[Open Balances:
Record_ID2 and DateBalanced Filling]]></HELP>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<ACCESSLEVEL><![CDATA[3]]></ACCESSLEVEL>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<ISUSERSTARTABLE><![CDATA[N]]></ISUSERSTARTABLE>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <ISREPORT><![CDATA[N]]></ISREPORT>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<ISDIRECTPRINT><![CDATA[N]]></ISDIRECTPRINT>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<CLASSNAME><![CDATA[org.openbravo.advpaymentmngt.process.RecordID2Filling]]></CLASSNAME>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<ISBACKGROUND><![CDATA[Y]]></ISBACKGROUND>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <ISJASPER><![CDATA[N]]></ISJASPER>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<ISEXTERNALSERVICE><![CDATA[N]]></ISEXTERNALSERVICE>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<AD_MODULE_ID><![CDATA[A918E3331C404B889D69AA9BFAFB23AC]]></AD_MODULE_ID>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--> <UIPATTERN><![CDATA[M]]></UIPATTERN>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<ISADVANCEDFEATURE><![CDATA[N]]></ISADVANCEDFEATURE>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<PREVENTCONCURRENT><![CDATA[Y]]></PREVENTCONCURRENT>
+<!--AA64E3BA23F24D4F8E6B06970500FC70-->
<IS_EXPLICIT_ACCESS><![CDATA[N]]></IS_EXPLICIT_ACCESS>
+<!--AA64E3BA23F24D4F8E6B06970500FC70--></AD_PROCESS>
+
<!--B54318B49E984B9CB855AEFB1F474CD6--><AD_PROCESS>
<!--B54318B49E984B9CB855AEFB1F474CD6-->
<AD_PROCESS_ID><![CDATA[B54318B49E984B9CB855AEFB1F474CD6]]></AD_PROCESS_ID>
<!--B54318B49E984B9CB855AEFB1F474CD6-->
<AD_CLIENT_ID><![CDATA[0]]></AD_CLIENT_ID>
diff -r 123836b0013c -r 3e75e4d92c64
modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/RecordID2Filling.java
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++
b/modules/org.openbravo.advpaymentmngt/src/org/openbravo/advpaymentmngt/process/RecordID2Filling.java
Mon Jun 02 12:50:56 2014 +0200
@@ -0,0 +1,461 @@
+package org.openbravo.advpaymentmngt.process;
+
+import java.util.HashSet;
+import java.util.List;
+import java.util.Set;
+
+import org.hibernate.Query;
+import org.hibernate.ScrollMode;
+import org.hibernate.ScrollableResults;
+import org.hibernate.Session;
+import org.hibernate.criterion.Restrictions;
+import org.openbravo.dal.core.OBContext;
+import org.openbravo.dal.service.OBCriteria;
+import org.openbravo.dal.service.OBDal;
+import org.openbravo.dal.service.OBQuery;
+import org.openbravo.model.financialmgmt.accounting.AccountingFact;
+import org.openbravo.model.financialmgmt.accounting.coa.AccountingCombination;
+import org.openbravo.model.financialmgmt.accounting.coa.AcctSchema;
+import org.openbravo.model.financialmgmt.payment.FIN_FinaccTransaction;
+import org.openbravo.model.financialmgmt.payment.FIN_FinancialAccount;
+import org.openbravo.model.financialmgmt.payment.FIN_Payment;
+import org.openbravo.model.financialmgmt.payment.FIN_PaymentMethod;
+import org.openbravo.model.financialmgmt.payment.FIN_PaymentScheduleDetail;
+import org.openbravo.model.financialmgmt.payment.FinAccPaymentMethod;
+import org.openbravo.scheduling.ProcessBundle;
+import org.openbravo.scheduling.ProcessLogger;
+import org.openbravo.service.db.DalBaseProcess;
+
+public class RecordID2Filling extends DalBaseProcess {
+
+ private static final Object TRANSACTION_TABLE_ID =
"4D8C3B3C31D1410DA046140C9F024D17";
+ private ProcessLogger logger;
+
+ @Override
+ protected void doExecute(ProcessBundle bundle) throws Exception {
+ logger = bundle.getLogger();
+ final StringBuilder hqlInvoices = new StringBuilder();
+ Set<AcctSchema> schemas = getSchemas();
+ Set<String> bpAccounts = new HashSet<String>();
+ Set<String> faAccounts = new HashSet<String>();
+ for (AcctSchema acctSchema : schemas) {
+ bpAccounts.addAll(getBPAccountList(true, acctSchema.getId()));
+ bpAccounts.addAll(getBPAccountList(false, acctSchema.getId()));
+ faAccounts.addAll(getFAAccountList(true, acctSchema.getId()));
+ faAccounts.addAll(getFAAccountList(false, acctSchema.getId()));
+ }
+
+ hqlInvoices.append(" update FinancialMgmtAccountingFact as f set
f.recordID2 = f.lineID ");
+ hqlInvoices.append(" where f.table.id = '318' ");
+ hqlInvoices
+ .append(" and exists (select 1 from FIN_Payment_Schedule as ps where
ps.id = f.lineID)");
+ hqlInvoices.append(" and f.account.id in :accounts");
+ hqlInvoices.append(" and f.recordID2 is null");
+ Query updateInvoices =
OBDal.getInstance().getSession().createQuery(hqlInvoices.toString());
+ updateInvoices.setParameterList("accounts", bpAccounts);
+ int numberInvoices = updateInvoices.executeUpdate();
+ logger.logln("Number of invoice entries updated: " + numberInvoices);
+ OBDal.getInstance().flush();
+
+ final StringBuilder hqlPayments = new StringBuilder();
+ hqlPayments
+ .append(" update FinancialMgmtAccountingFact as f set f.recordID2 = ("
+ + "select case when psd.invoicePaymentSchedule is null then
psd.orderPaymentSchedule else psd.invoicePaymentSchedule end "
+ + "from FIN_Payment_ScheduleDetail as psd join psd.paymentDetails
as pd"
+ + " where pd.id = f.lineID)");
+ hqlPayments.append("where f.table.id = 'D1A97202E832470285C9B1EB026D54E2'
");
+ hqlPayments.append(" and f.account.id in :accounts");
+ hqlPayments.append(" and f.recordID2 is null");
+ Query updatePayments =
OBDal.getInstance().getSession().createQuery(hqlPayments.toString());
+ updatePayments.setParameterList("accounts", bpAccounts);
+ int numberPayments = updatePayments.executeUpdate();
+
+ logger.logln("Number of payment entries updated: " + numberPayments);
+ OBDal.getInstance().flush();
+
+ // Updates in transit accounts (record_id2)
+ final StringBuilder hqlPaymentsInTransit = new StringBuilder();
+ hqlPaymentsInTransit
+ .append(" update FinancialMgmtAccountingFact as f set f.recordID2 =
f.recordID");
+ hqlPaymentsInTransit.append(" where f.lineID is null ");
+ hqlPaymentsInTransit.append(" and f.recordID2 is null");
+ hqlPaymentsInTransit
+ .append(" and exists (select 1 from FIN_Payment as p where p.id =
f.recordID "
+ + "and not exists( select 1 from FIN_Payment_Credit as pc where
pc.creditPaymentUsed = p))");
+ Query updatePaymentsinTransit = OBDal.getInstance().getSession()
+ .createQuery(hqlPaymentsInTransit.toString());
+ int numberPaymentsInTransit = updatePaymentsinTransit.executeUpdate();
+
+ System.out
+ .println("Number of payment entries updated (In Transit): " +
numberPaymentsInTransit);
+ OBDal.getInstance().flush();
+
+ final StringBuilder hqlTrxRec = new StringBuilder();
+ hqlTrxRec.append(" select f from FinancialMgmtAccountingFact as f ");
+ hqlTrxRec.append(" where f.recordID2 is null ");
+ hqlTrxRec
+ .append(" and exists (select 1 from FIN_Finacc_Transaction as t
where t.id = f.lineID)");
+ hqlTrxRec.append(" and account.id in :accounts");
+ Query query =
OBDal.getInstance().getSession().createQuery(hqlTrxRec.toString());
+
+ query.setParameterList("accounts", bpAccounts);
+ int i = 0;
+ int j = 0;
+ query.setFetchSize(1000);
+ final ScrollableResults scroller = query.scroll(ScrollMode.FORWARD_ONLY);
+ try {
+ while (scroller.next()) {
+ OBContext.setAdminMode(false);
+ final AccountingFact accountingEntry = (AccountingFact)
scroller.get()[0];
+ try {
+ FIN_FinaccTransaction trx =
OBDal.getInstance().get(FIN_FinaccTransaction.class,
+ accountingEntry.getLineID());
+ if (trx != null && trx.getFinPayment() != null) {
+ if (trx.getFinPayment().getFINPaymentDetailList().size() == 1
+ && (trx.getFinPayment().getFINPaymentDetailList().get(0)
+
.getFINPaymentScheduleDetailList().get(0).getInvoicePaymentSchedule() != null
|| trx
+ .getFinPayment().getFINPaymentDetailList().get(0)
+
.getFINPaymentScheduleDetailList().get(0).getOrderPaymentSchedule() != null)) {
+ accountingEntry
+
.setRecordID2(trx.getFinPayment().getFINPaymentDetailList().get(0)
+
.getFINPaymentScheduleDetailList().get(0).getInvoicePaymentSchedule() == null ?
trx
+ .getFinPayment().getFINPaymentDetailList().get(0)
+
.getFINPaymentScheduleDetailList().get(0).getOrderPaymentSchedule().getId()
+ : trx.getFinPayment().getFINPaymentDetailList().get(0)
+
.getFINPaymentScheduleDetailList().get(0).getInvoicePaymentSchedule()
+ .getId());
+ OBDal.getInstance().save(accountingEntry);
+ j++;
+ } else {
+ FIN_PaymentScheduleDetail psd =
getOrderedPSDs(trx.getFinPayment()).get(
+ getAccountingEntryPosition(accountingEntry, bpAccounts));
+
accountingEntry.setRecordID2(psd.getPaymentDetails().isPrepayment() ? psd
+ .getOrderPaymentSchedule().getId() :
psd.getInvoicePaymentSchedule().getId());
+ OBDal.getInstance().save(accountingEntry);
+ j++;
+ }
+ }
+ // clear the session every 100 records
+ if ((i % 100) == 0 && i != 0) {
+ logger.logln(String.valueOf(i + 1) + " - " + String.valueOf(j));
+ logger.logln(String.valueOf(i + 1) + " - " + String.valueOf(j));
+ OBDal.getInstance().flush();
+ OBDal.getInstance().getSession().clear();
+ }
+ i++;
+ } catch (Exception e) {
+ logger.logln("Entry not updated: " + accountingEntry.getId());
+ continue;
+ }
+ }
+ OBDal.getInstance().flush();
+ OBDal.getInstance().getSession().clear();
+ } finally {
+ OBContext.restorePreviousMode();
+ scroller.close();
+ }
+ // Update Transactions and Reconciliations
+ final StringBuilder hqlTrxRecInTransit = new StringBuilder();
+ hqlTrxRecInTransit.append(" select f from FinancialMgmtAccountingFact as f
");
+ hqlTrxRecInTransit.append(" where f.recordID2 is null ");
+ hqlTrxRecInTransit
+ .append(" and (exists (select 1 from FIN_Finacc_Transaction as t
where t.id = f.lineID) or (f.lineID is null and f.table.id = '"
+ + TRANSACTION_TABLE_ID + "'))");
+ hqlTrxRecInTransit.append(" and account.id in :accounts");
+ Query queryInTransit = OBDal.getInstance().getSession()
+ .createQuery(hqlTrxRecInTransit.toString());
+
+ queryInTransit.setParameterList("accounts", faAccounts);
+ i = 0;
+ j = 0;
+ queryInTransit.setFetchSize(1000);
+ final ScrollableResults scrollerInTransit =
queryInTransit.scroll(ScrollMode.FORWARD_ONLY);
+ try {
+ while (scrollerInTransit.next()) {
+ OBContext.setAdminMode(false);
+ final AccountingFact accountingEntry = (AccountingFact)
scrollerInTransit.get()[0];
+ try {
+ FIN_FinaccTransaction trx = OBDal.getInstance().get(
+ FIN_FinaccTransaction.class,
+ accountingEntry.getLineID() == null ?
accountingEntry.getRecordID() : accountingEntry
+ .getLineID());
+ if (trx != null && trx.getFinPayment() != null) {
+ // logger.logln("Table: " + accountingEntry.getTable().getName());
+ // logger.logln("Accounting entry: ");
+ // logger.logln(accountingEntry.getAccountingEntryDescription() +
" - "
+ // + accountingEntry.getDebit().toString() + " - "
+ // + accountingEntry.getCredit().toString());
+ // logger.logln("Payment Method: " +
trx.getFinPayment().getPaymentMethod().getName());
+ // logger.logln("Financial Account: " +
trx.getAccount().getName());
+
+ Set<String> paymentAccount =
getFAAccountList(trx.getFinPayment().isReceipt(),
+ accountingEntry.getAccountingSchema().getId(),
trx.getAccount().getId(), trx
+ .getFinPayment().getPaymentMethod().getId(), "PAY");
+ Set<String> transactionAccount =
getFAAccountList(trx.getFinPayment().isReceipt(),
+ accountingEntry.getAccountingSchema().getId(),
trx.getAccount().getId(), trx
+ .getFinPayment().getPaymentMethod().getId(), "TRX");
+
+ // logger.logln("Payment event account: " + paymentAccount);
+ // logger.logln("Transaction event account: " +
transactionAccount);
+ if (paymentAccount.contains(accountingEntry.getAccount().getId()))
{
+ logger.logln("Use: Payment ID= " + trx.getFinPayment().getId());
+ accountingEntry.setRecordID2(trx.getFinPayment().getId());
+ OBDal.getInstance().save(accountingEntry);
+ j++;
+ } else if
(transactionAccount.contains(accountingEntry.getAccount().getId())) {
+ logger.logln("Use: Transaction ID= " + trx.getId());
+ accountingEntry.setRecordID2(trx.getId());
+ OBDal.getInstance().save(accountingEntry);
+ j++;
+ }
+ }
+ // clear the session every 100 records
+ if ((i % 100) == 0 && i != 0) {
+ logger.logln(String.valueOf(i + 1) + " - " + String.valueOf(j));
+ logger.logln(String.valueOf(i + 1) + " - " + String.valueOf(j));
+ OBDal.getInstance().flush();
+ OBDal.getInstance().getSession().clear();
+ }
+ i++;
+ } catch (Exception e) {
+ logger.logln("Entry not updated: " + accountingEntry.getId());
+ continue;
+ }
+ }
+ OBDal.getInstance().flush();
+ OBDal.getInstance().getSession().clear();
+ } finally {
+ OBContext.restorePreviousMode();
+ scrollerInTransit.close();
+ }
+
+ // Update date balancing
+ final StringBuilder hqlDateBalanced = new StringBuilder();
+ hqlDateBalanced
+ .append(" update FinancialMgmtAccountingFact as f set f.dateBalanced =
"
+ + "(select max(f2.accountingDate) from FinancialMgmtAccountingFact
as f2 "
+ + "where f2.recordID2 = f.recordID2 and f2.accountingSchema =
f.accountingSchema and f2.account = f.account group by f2.recordID2 having
sum(f2.credit-f2.debit)=0) ");
+ hqlDateBalanced
+ .append(" where exists (select 1 from FinancialMgmtAccountingFact as
f3 "
+ + "where f3.recordID2 = f.recordID2 and f3.accountingSchema =
f.accountingSchema and f3.account = f.account group by f3.recordID2 having
sum(f3.credit-f3.debit)=0)");
+ hqlDateBalanced.append(" and f.dateBalanced is null");
+ Query updateDateBalanced = OBDal.getInstance().getSession()
+ .createQuery(hqlDateBalanced.toString());
+ int numberBalanced = updateDateBalanced.executeUpdate();
+ logger.logln("Number of date balanced entries: " + numberBalanced);
+ OBDal.getInstance().flush();
+ }
+
+ private Set<AcctSchema> getSchemas() {
+ OBCriteria<AcctSchema> obc =
OBDal.getInstance().createCriteria(AcctSchema.class);
+ return new HashSet<AcctSchema>(obc.list());
+ }
+
+ private int getAccountingEntryPosition(AccountingFact accountingEntry,
Set<String> accounts) {
+ final StringBuilder hqlString = new StringBuilder();
+ hqlString.append(" as f");
+ hqlString.append(" where f.account.id in :accounts");
+ hqlString.append(" and f.recordID = :recordID");
+ hqlString.append(" order by abs(f.debit-f.credit), f.creationDate");
+ final OBQuery<AccountingFact> query =
OBDal.getInstance().createQuery(AccountingFact.class,
+ hqlString.toString());
+ query.setNamedParameter("accounts", accounts);
------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/NeoTech
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
