diff --git a/modules/org.openbravo.advpaymentmngt/src-db/database/model/modifiedTables/FIN_FINANCIAL_ACCOUNT.xml b/modules/org.openbravo.advpaymentmngt/src-db/database/model/modifiedTables/FIN_FINANCIAL_ACCOUNT.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/model/modifiedTables/FIN_FINANCIAL_ACCOUNT.xml
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/model/modifiedTables/FIN_FINANCIAL_ACCOUNT.xml
@@ -33,5 +33,9 @@
+
+
+
+
diff --git a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_COLUMN.xml
@@ -102,6 +102,40 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
diff --git a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_ELEMENT.xml b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_ELEMENT.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_ELEMENT.xml
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_ELEMENT.xml
@@ -154,6 +154,18 @@
+
+
+
+
+
+
+
+
+
+
+
+
diff --git a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_FIELD.xml b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_FIELD.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_FIELD.xml
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/AD_FIELD.xml
@@ -12720,6 +12720,34 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
diff --git a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/OBUIAPP_PARAMETER.xml b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/OBUIAPP_PARAMETER.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/OBUIAPP_PARAMETER.xml
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/OBUIAPP_PARAMETER.xml
@@ -852,6 +852,31 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
@@ -1051,6 +1076,30 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
diff --git a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/OBUIAPP_PROCESS.xml b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/OBUIAPP_PROCESS.xml
--- a/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/OBUIAPP_PROCESS.xml
+++ b/modules/org.openbravo.advpaymentmngt/src-db/database/sourcedata/OBUIAPP_PROCESS.xml
@@ -138,6 +138,24 @@
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
diff --git a/src/org/openbravo/advpaymentmngt/actionHandler/ReconcileAllHandler.java b/src/org/openbravo/advpaymentmngt/actionHandler/ReconcileAllHandler.java
new file mode 100644
--- /dev/null
+++ b/src/org/openbravo/advpaymentmngt/actionHandler/ReconcileAllHandler.java
@@ -0,0 +1,464 @@
+/*
+ *************************************************************************
+ * The contents of this file are subject to the Openbravo Public License
+ * Version 1.0 (the "License"), being the Mozilla Public License
+ * Version 1.1 with a permitted attribution clause; you may not use this
+ * file except in compliance with the License. You may obtain a copy of
+ * the License at http://www.openbravo.com/legal/license.html
+ * 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) 2015 Openbravo SLU
+ * All Rights Reserved.
+ * Contributor(s): ______________________________________.
+ *************************************************************************
+ */
+
+package org.openbravo.advpaymentmngt.actionHandler;
+
+import java.io.IOException;
+import java.math.BigDecimal;
+import java.text.SimpleDateFormat;
+import java.util.Calendar;
+import java.util.Date;
+import java.util.HashSet;
+import java.util.List;
+import java.util.Map;
+import java.util.Set;
+
+import javax.servlet.ServletException;
+
+import org.apache.commons.lang.time.DateUtils;
+import org.apache.log4j.Logger;
+import org.codehaus.jettison.json.JSONArray;
+import org.codehaus.jettison.json.JSONObject;
+import org.hibernate.criterion.Restrictions;
+import org.openbravo.advpaymentmngt.APRM_FinaccTransactionV;
+import org.openbravo.advpaymentmngt.dao.TransactionsDao;
+import org.openbravo.advpaymentmngt.utility.FIN_Utility;
+import org.openbravo.base.exception.OBException;
+import org.openbravo.client.application.process.BaseProcessActionHandler;
+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.data.FieldProvider;
+import org.openbravo.erpCommon.ad_forms.AcctServer;
+import org.openbravo.erpCommon.utility.OBDateUtils;
+import org.openbravo.erpCommon.utility.OBError;
+import org.openbravo.erpCommon.utility.OBMessageUtils;
+import org.openbravo.model.financialmgmt.accounting.FIN_FinancialAccountAccounting;
+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_PaymentDetail;
+import org.openbravo.model.financialmgmt.payment.FIN_PaymentScheduleDetail;
+import org.openbravo.model.financialmgmt.payment.FIN_Reconciliation;
+import org.openbravo.model.financialmgmt.payment.FIN_ReconciliationLine_v;
+import org.openbravo.model.financialmgmt.payment.FinAccPaymentMethod;
+import org.openbravo.service.db.DbUtility;
+import org.openbravo.service.json.JsonUtils;
+
+public class ReconcileAllHandler extends BaseProcessActionHandler {
+ private static final Logger log4j = Logger.getLogger(ReconcileAllHandler.class);
+ private static final SimpleDateFormat jsDateFormat = JsonUtils.createDateFormat();
+
+ Set transactionsToBePosted = new HashSet();
+
+ @Override
+ protected JSONObject doExecute(Map parameters, String data) {
+ try {
+ OBContext.setAdminMode(true);
+ final JSONObject jsonData = new JSONObject(data);
+ final JSONObject jsonparams = jsonData.getJSONObject("_params");
+
+ final Date statementDate = jsDateFormat.parse(jsonparams.getString("statementDate"));
+ final String strEndBalance = jsonparams.getString("endingBalance");
+ final String accountId = jsonData.getString("inpfinFinancialAccountId");
+
+ FieldProvider[] transactionFP = TransactionsDao.getTransactionsFiltered(OBDal.getInstance()
+ .get(FIN_FinancialAccount.class, accountId), DateUtils.addDays(statementDate, 1), true);
+
+ int i = 0;
+ for (FieldProvider fp : transactionFP) {
+ updateTransactionStatus(accountId, fp.getField("transactionId"), true);
+ i++;
+ if (i % 100 == 0) {
+ OBDal.getInstance().flush();
+ OBDal.getInstance().getSession().clear();
+ log4j.debug("flush");
+ }
+ }
+ OBDal.getInstance().flush();
+ OBDal.getInstance().getSession().clear();
+
+ processReconciliation(accountId, statementDate, strEndBalance, true);
+
+ // Success Message
+ return getSuccessMessage(OBMessageUtils.messageBD("Success"));
+
+ } catch (Exception e) {
+ OBDal.getInstance().rollbackAndClose();
+ log4j.error("Exception in reconcile all", e);
+
+ try {
+ Throwable ex = DbUtility.getUnderlyingSQLException(e);
+ String message = OBMessageUtils.translateError(ex.getMessage()).getMessage();
+ return getErrorMessage(message);
+ } catch (Exception ignore) {
+ }
+ } finally {
+ OBContext.restorePreviousMode();
+ }
+
+ return new JSONObject();
+ }
+
+ /**
+ * Returns a JSONObject with the success message to be printed
+ */
+ private static JSONObject getSuccessMessage(final String msgText) {
+ final JSONObject result = new JSONObject();
+ try {
+ final JSONArray actions = new JSONArray();
+ final JSONObject msgInBPTab = new JSONObject();
+ msgInBPTab.put("msgType", "success");
+ msgInBPTab.put("msgTitle", OBMessageUtils.messageBD("success"));
+ msgInBPTab.put("msgText", msgText);
+ final JSONObject msgInBPTabAction = new JSONObject();
+ msgInBPTabAction.put("showMsgInProcessView", msgInBPTab);
+ actions.put(msgInBPTabAction);
+ result.put("responseActions", actions);
+ } catch (Exception e) {
+ log4j.error(e);
+ }
+
+ return result;
+ }
+
+ /**
+ * Returns a JSONObject with the error message to be printed and retry execution
+ */
+ private static JSONObject getErrorMessage(final String msgText) {
+ final JSONObject result = new JSONObject();
+ try {
+ final JSONObject msg = new JSONObject();
+ msg.put("severity", "error");
+ msg.put("text", msgText);
+ result.put("message", msg);
+ result.put("retryExecution", true);
+ } catch (Exception e) {
+ log4j.error(e);
+ }
+ return result;
+ }
+
+ private void updateTransactionStatus(String strFinancialAccountId, String strSelectedTransId,
+ boolean isChecked) {
+ OBContext.setAdminMode();
+ try {
+ if (strSelectedTransId != "") {
+ FIN_FinaccTransaction trans = OBDal.getInstance().get(FIN_FinaccTransaction.class,
+ strSelectedTransId);
+ String newStatus = "RPPC";
+ if (!isChecked) {
+ newStatus = (trans.getPaymentAmount().compareTo(trans.getDepositAmount()) >= 0) ? "PWNC"
+ : "RDNC";
+ trans.setReconciliation(null);
+ if (trans.getFinPayment() != null) {
+ trans.getFinPayment().setStatus((trans.getFinPayment().isReceipt()) ? "RDNC" : "PWNC");
+ }
+ } else {
+ FIN_FinancialAccount account = OBDal.getInstance().get(FIN_FinancialAccount.class,
+ strFinancialAccountId);
+ FIN_Reconciliation reconciliation = TransactionsDao.getLastReconciliation(account, "N");
+ trans.setReconciliation(reconciliation);
+ if (trans.getFinPayment() != null) {
+ trans.getFinPayment().setStatus("RPPC");
+ }
+ }
+
+ trans.setStatus(newStatus);
+ OBDal.getInstance().save(trans);
+ }
+
+ } catch (Exception e) {
+ throw new OBException(e);
+ } finally {
+ OBContext.restorePreviousMode();
+ }
+ }
+
+ private void processReconciliation(String strFinancialAccountId, Date statementDate,
+ String strEndBalance, boolean process) throws IOException, ServletException {
+
+ log4j
+ .debug("Output: Process or Save button pressed on Financial Account || Transaction || Reconciliation manual window");
+
+ OBError msg = new OBError();
+ OBContext.setAdminMode();
+ try {
+
+ FIN_FinancialAccount account = OBDal.getInstance().get(FIN_FinancialAccount.class,
+ strFinancialAccountId);
+
+ FIN_Reconciliation reconciliation = TransactionsDao.getLastReconciliation(account, "N");
+
+ FIN_Reconciliation lastProcessedReconciliation = TransactionsDao.getLastReconciliation(
+ account, "Y");
+
+ reconciliation.setEndingBalance(new BigDecimal(strEndBalance));
+ reconciliation.setTransactionDate(statementDate);
+ reconciliation.setEndingDate(statementDate);
+ reconciliation.setDocumentStatus("DR");
+ reconciliation.setProcessed(false);
+ reconciliation.setAPRMProcessReconciliation("P");
+ reconciliation.setAprmProcessRec("P");
+ OBDal.getInstance().save(reconciliation);
+ OBDal.getInstance().flush();
+
+ if (process) { // Validations
+ String strMessage = "";
+ boolean raiseException = false;
+
+ Calendar calCurrent = Calendar.getInstance();
+ calCurrent.setTime(statementDate);
+
+ if (lastProcessedReconciliation != null) {
+ Calendar calLast = Calendar.getInstance();
+ calLast.setTime(lastProcessedReconciliation.getEndingDate());
+ if (calCurrent.before(calLast)) {
+ strMessage = "@APRM_ReconcileInFutureOrPast@";
+ raiseException = true;
+ }
+ }
+
+ Calendar tomorrow = Calendar.getInstance();
+ tomorrow.add(Calendar.DATE, 1);
+ tomorrow.setTime(DateUtils.truncate(tomorrow.getTime(), Calendar.DATE));
+ if (calCurrent.after(tomorrow)) {
+ strMessage = "@APRM_ReconcileInFutureOrPast@";
+ raiseException = true;
+ }
+ if (raiseException) {
+ msg.setType("Error");
+ msg.setTitle(OBMessageUtils.messageBD("Error"));
+ msg.setMessage(OBMessageUtils.translateError(strMessage).getMessage());
+ msg = null;
+ throw new OBException(msg.getMessage());
+ }
+
+ boolean orgLegalWithAccounting = FIN_Utility.periodControlOpened(
+ FIN_Reconciliation.TABLE_NAME, reconciliation.getId(), FIN_Reconciliation.TABLE_NAME
+ + "_ID", "LE");
+
+ boolean documentEnabled = getDocumentConfirmation(reconciliation.getId());
+ if (documentEnabled
+ && !FIN_Utility.isPeriodOpen(reconciliation.getClient().getId(),
+ AcctServer.DOCTYPE_Reconciliation, reconciliation.getOrganization().getId(),
+ OBDateUtils.formatDate(statementDate)) && orgLegalWithAccounting) {
+ msg.setType("Error");
+ msg.setTitle(OBMessageUtils.messageBD("Error"));
+ msg.setMessage(OBMessageUtils.translateError("@PeriodNotAvailable@").getMessage());
+ throw new OBException(msg.getMessage());
+ }
+
+ if (documentEnabled && orgLegalWithAccounting) {
+ String identifier = linesInNotAvailablePeriod(reconciliation.getId());
+ if (!identifier.equalsIgnoreCase("")) {
+ msg.setType("Error");
+ msg.setTitle(OBMessageUtils.messageBD("Error"));
+ msg.setMessage(String.format(
+ OBMessageUtils.messageBD("APRM_PeriodNotAvailableClearedItem"), identifier));
+ throw new OBException(msg.getMessage());
+
+ }
+ }
+
+ for (APRM_FinaccTransactionV finacctrxv : reconciliation.getAPRMFinaccTransactionVList()) {
+ if (reconciliation.getEndingDate().compareTo(
+ finacctrxv.getFinancialAccountTransaction().getTransactionDate()) < 0) {
+ FIN_FinaccTransaction trans = finacctrxv.getFinancialAccountTransaction();
+ // We set processed to false before changing dates to avoid trigger exception
+ boolean posted = "Y".equals(trans.getPosted());
+ if (posted) {
+ trans.setPosted("N");
+ OBDal.getInstance().save(trans);
+ OBDal.getInstance().flush();
+ }
+ trans.setProcessed(false);
+ OBDal.getInstance().save(trans);
+ OBDal.getInstance().flush();
+ trans.setTransactionDate(reconciliation.getEndingDate());
+ trans.setDateAcct(reconciliation.getEndingDate());
+ OBDal.getInstance().save(trans);
+ OBDal.getInstance().flush();
+ // We set processed to true afterwards
+ trans.setProcessed(true);
+ OBDal.getInstance().save(trans);
+ OBDal.getInstance().flush();
+ if (posted) {
+ trans.setPosted("Y");
+ OBDal.getInstance().save(trans);
+ OBDal.getInstance().flush();
+ }
+ // Changing dates for accounting entries as well
+ TransactionsDao.updateAccountingDate(trans);
+ }
+ Boolean invoicePaidold = false;
+ if (finacctrxv.getPayment() != null) {
+ for (FIN_PaymentDetail pd : finacctrxv.getPayment().getFINPaymentDetailList()) {
+ for (FIN_PaymentScheduleDetail psd : pd.getFINPaymentScheduleDetailList()) {
+ invoicePaidold = psd.isInvoicePaid();
+ if (!invoicePaidold) {
+ if ((FIN_Utility.invoicePaymentStatus(finacctrxv.getPayment().getPaymentMethod(),
+ reconciliation.getAccount(), finacctrxv.getPayment().isReceipt())
+ .equals(finacctrxv.getPayment().getStatus()))) {
+ psd.setInvoicePaid(true);
+ }
+ if (psd.isInvoicePaid()) {
+ FIN_Utility.updatePaymentAmounts(psd);
+ }
+ }
+ }
+ FIN_Utility.updateBusinessPartnerCredit(finacctrxv.getPayment());
+ }
+ }
+ }
+
+ reconciliation.setDocumentStatus("CO");
+ reconciliation.setProcessed(true);
+ reconciliation.setAPRMProcessReconciliation("R");
+ reconciliation.setAprmProcessRec("R");
+ OBDal.getInstance().save(reconciliation);
+ OBDal.getInstance().flush();
+
+ }
+ } catch (Exception e) {
+ throw new OBException(e);
+ } finally {
+ OBContext.restorePreviousMode();
+ }
+ }
+
+ public List getTransactionList(FIN_Reconciliation reconciliation) {
+ OBContext.setAdminMode();
+ List transactions = null;
+ try {
+ OBCriteria trans = OBDal.getInstance().createCriteria(
+ FIN_FinaccTransaction.class);
+ trans.add(Restrictions.eq(FIN_FinaccTransaction.PROPERTY_RECONCILIATION, reconciliation));
+ trans.setFilterOnReadableClients(false);
+ trans.setFilterOnReadableOrganization(false);
+ transactions = trans.list();
+ } finally {
+ OBContext.restorePreviousMode();
+ }
+ return transactions;
+ }
+
+ /*
+ * Checks if this step (Reconciliation) is configured to generate accounting for the selected
+ * financial account
+ */
+ public boolean getDocumentConfirmation(String strRecordId) {
+ OBContext.setAdminMode();
+ try {
+ FIN_Reconciliation reconciliation = OBDal.getInstance().get(FIN_Reconciliation.class,
+ strRecordId);
+ List transactions = getTransactionList(reconciliation);
+ List accounts = reconciliation.getAccount()
+ .getFINFinancialAccountAcctList();
+ for (FIN_FinaccTransaction transaction : transactions) {
+ FIN_Payment payment = transaction.getFinPayment();
+ // If payment exists, check Payment Method + financial Account Configuration
+ if (payment != null) {
+ OBCriteria obCriteria = OBDal.getInstance().createCriteria(
+ FinAccPaymentMethod.class);
+ obCriteria.add(Restrictions.eq(FinAccPaymentMethod.PROPERTY_ACCOUNT,
+ reconciliation.getAccount()));
+ obCriteria.add(Restrictions.eq(FinAccPaymentMethod.PROPERTY_PAYMENTMETHOD,
+ payment.getPaymentMethod()));
+ obCriteria.setFilterOnReadableClients(false);
+ obCriteria.setFilterOnReadableOrganization(false);
+ List lines = obCriteria.list();
+ for (FIN_FinancialAccountAccounting account : accounts) {
+ if (payment.isReceipt()) {
+ if (("INT").equals(lines.get(0).getINUponClearingUse())
+ && account.getInTransitPaymentAccountIN() != null) {
+ transactionsToBePosted.add(transaction);
+ } else if (("DEP").equals(lines.get(0).getINUponClearingUse())
+ && account.getDepositAccount() != null) {
+ transactionsToBePosted.add(transaction);
+ } else if (("CLE").equals(lines.get(0).getINUponClearingUse())
+ && account.getClearedPaymentAccount() != null) {
+ transactionsToBePosted.add(transaction);
+ }
+ } else {
+ if (("INT").equals(lines.get(0).getOUTUponClearingUse())
+ && account.getFINOutIntransitAcct() != null) {
+ transactionsToBePosted.add(transaction);
+ } else if (("WIT").equals(lines.get(0).getOUTUponClearingUse())
+ && account.getWithdrawalAccount() != null) {
+ transactionsToBePosted.add(transaction);
+ } else if (("CLE").equals(lines.get(0).getOUTUponClearingUse())
+ && account.getClearedPaymentAccountOUT() != null) {
+ transactionsToBePosted.add(transaction);
+ }
+ }
+ }
+ } else if (transaction.getGLItem() != null) {
+ for (FIN_FinancialAccountAccounting account : accounts) {
+ if ("BPD".equals(transaction.getTransactionType())
+ && account.getClearedPaymentAccount() != null) {
+ transactionsToBePosted.add(transaction);
+ } else if ("BPW".equals(transaction.getTransactionType())
+ && account.getClearedPaymentAccountOUT() != null) {
+ transactionsToBePosted.add(transaction);
+ }
+ }
+ } else {
+ for (FIN_FinancialAccountAccounting account : accounts) {
+ if ("BF".equals(transaction.getTransactionType())
+ && account.getClearedPaymentAccountOUT() != null) {
+ transactionsToBePosted.add(transaction);
+ }
+ }
+ }
+ }
+ } catch (Exception e) {
+ return false;
+ } finally {
+ OBContext.restorePreviousMode();
+ }
+ if (transactionsToBePosted.size() == 0) {
+ return false;
+ }
+ return true;
+ }
+
+ private String linesInNotAvailablePeriod(String reconciliationId) {
+ final StringBuilder hql = new StringBuilder();
+
+ hql.append(" as rl ");
+ hql.append(" where rl.reconciliation.id = '").append(reconciliationId).append("' ");
+ hql.append(" and c_chk_open_period(rl.organization, rl.transactionDate, 'REC', null) = 0 ");
+ hql.append(" order by rl.transactionDate");
+
+ final OBQuery obqRL = OBDal.getInstance().createQuery(
+ FIN_ReconciliationLine_v.class, hql.toString());
+ obqRL.setMaxResult(1);
+
+ List obqRLlist = obqRL.list();
+
+ if (obqRLlist.size() == 0) {
+ return "";
+ } else {
+ return obqRLlist.get(0).getIdentifier();
+ }
+
+ }
+}