/**
* Calculates the sum of un-reconciled bank statement lines applying the following filters:
*
* <p>- They belong to the financial account of the reconciliation.
*
* <p>- The transaction date must be lower than the ending date of the reconciliation.
*
* <p>- They are not matched with any transaction.
*
* @param recon Reconciliation
* @return Sum of the un-reconciled bank statement lines.
*/
private BigDecimal getUnreconciledBankStatmentLinesTotal(FIN_Reconciliation recon) {
BigDecimal total = BigDecimal.ZERO;
OBContext.setAdminMode(true);
try {
OBCriteria<FIN_BankStatementLine> obcBsl =
OBDal.getInstance().createCriteria(FIN_BankStatementLine.class);
obcBsl.createAlias(FIN_BankStatementLine.PROPERTY_BANKSTATEMENT, "bs");
obcBsl.createAlias(
FIN_BankStatementLine.PROPERTY_FINANCIALACCOUNTTRANSACTION, "tr", OBCriteria.LEFT_JOIN);
obcBsl.add(
Restrictions.le(FIN_BankStatementLine.PROPERTY_TRANSACTIONDATE, recon.getEndingDate()));
List<FIN_Reconciliation> afterReconciliations =
MatchTransactionDao.getReconciliationListAfterDate(recon);
if (afterReconciliations.size() > 0) {
obcBsl.add(
Restrictions.or(
Restrictions.isNull(FIN_BankStatementLine.PROPERTY_FINANCIALACCOUNTTRANSACTION),
Restrictions.in(
"tr." + FIN_FinaccTransaction.PROPERTY_RECONCILIATION, afterReconciliations)));
} else {
obcBsl.add(Restrictions.isNull(FIN_BankStatementLine.PROPERTY_FINANCIALACCOUNTTRANSACTION));
}
obcBsl.add(Restrictions.eq("bs." + FIN_BankStatement.PROPERTY_ACCOUNT, recon.getAccount()));
obcBsl.add(Restrictions.eq("bs." + FIN_BankStatement.PROPERTY_PROCESSED, true));
ProjectionList projections = Projections.projectionList();
projections.add(Projections.sum(FIN_BankStatementLine.PROPERTY_CRAMOUNT));
projections.add(Projections.sum(FIN_BankStatementLine.PROPERTY_DRAMOUNT));
obcBsl.setProjection(projections);
if (obcBsl.list() != null && obcBsl.list().size() > 0) {
@SuppressWarnings("rawtypes")
List o = obcBsl.list();
Object[] resultSet = (Object[]) o.get(0);
BigDecimal credit = (resultSet[0] != null) ? (BigDecimal) resultSet[0] : BigDecimal.ZERO;
BigDecimal debit = (resultSet[1] != null) ? (BigDecimal) resultSet[1] : BigDecimal.ZERO;
total = credit.subtract(debit);
}
} finally {
OBContext.restorePreviousMode();
}
return total;
}
/**
* Calculates the sum of outstanding payments/deposits applying the following filters:
*
* <p>- They belong to the financial account of the reconciliation.
*
* <p>- The transaction date must be lower than the ending date of the reconciliation.
*
* <p>- They do not belong to any reconciliation.
*
* @param recon Reconciliation
* @return List with 2 values. The first one is the sum of outstanding payments (transactions) and
* the second is the sum of outstanding deposits (transactions).
*/
private List<BigDecimal> getOutstandingPaymentAndDepositTotal(FIN_Reconciliation recon) {
List<BigDecimal> outList = new ArrayList<BigDecimal>();
OBContext.setAdminMode(true);
try {
OBCriteria<FIN_FinaccTransaction> obcTrans =
OBDal.getInstance().createCriteria(FIN_FinaccTransaction.class);
obcTrans.add(Restrictions.eq(FIN_FinaccTransaction.PROPERTY_ACCOUNT, recon.getAccount()));
obcTrans.add(
Restrictions.le(FIN_FinaccTransaction.PROPERTY_TRANSACTIONDATE, recon.getEndingDate()));
List<FIN_Reconciliation> afterReconciliations =
MatchTransactionDao.getReconciliationListAfterDate(recon);
if (afterReconciliations.size() > 0) {
obcTrans.add(
Restrictions.or(
Restrictions.isNull(FIN_FinaccTransaction.PROPERTY_RECONCILIATION),
Restrictions.in(
FIN_FinaccTransaction.PROPERTY_RECONCILIATION, afterReconciliations)));
} else {
obcTrans.add(Restrictions.isNull(FIN_FinaccTransaction.PROPERTY_RECONCILIATION));
}
ProjectionList projections = Projections.projectionList();
projections.add(Projections.sum(FIN_FinaccTransaction.PROPERTY_PAYMENTAMOUNT));
projections.add(Projections.sum(FIN_FinaccTransaction.PROPERTY_DEPOSITAMOUNT));
obcTrans.setProjection(projections);
if (obcTrans.list() != null && obcTrans.list().size() > 0) {
@SuppressWarnings("rawtypes")
List o = obcTrans.list();
Object[] resultSet = (Object[]) o.get(0);
BigDecimal paymentAmt =
(resultSet[0] != null) ? (BigDecimal) resultSet[0] : BigDecimal.ZERO;
BigDecimal depositAmt =
(resultSet[1] != null) ? (BigDecimal) resultSet[1] : BigDecimal.ZERO;
outList.add(paymentAmt);
outList.add(depositAmt);
}
} finally {
OBContext.restorePreviousMode();
}
return outList;
}