You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by na...@apache.org on 2016/09/22 06:41:53 UTC
incubator-fineract git commit: [MIFOSX-2784] Including all
transactions in Teller Cashier transaction list
Repository: incubator-fineract
Updated Branches:
refs/heads/develop 025ff9c70 -> 8d3f7af16
[MIFOSX-2784] Including all transactions in Teller Cashier transaction list
Project: http://git-wip-us.apache.org/repos/asf/incubator-fineract/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-fineract/commit/8d3f7af1
Tree: http://git-wip-us.apache.org/repos/asf/incubator-fineract/tree/8d3f7af1
Diff: http://git-wip-us.apache.org/repos/asf/incubator-fineract/diff/8d3f7af1
Branch: refs/heads/develop
Commit: 8d3f7af16f7dbd1e8607e4fd78f400054e8f0dc4
Parents: 025ff9c
Author: Adi Narayana Raju <ad...@confluxtechnologies.com>
Authored: Wed Sep 21 17:07:55 2016 +0530
Committer: Adi Narayana Raju <ad...@confluxtechnologies.com>
Committed: Wed Sep 21 17:07:55 2016 +0530
----------------------------------------------------------------------
.../teller/api/TellerApiResource.java | 21 ++-
.../CashierTransactionsWithSummaryData.java | 11 +-
.../TellerManagementReadPlatformService.java | 8 +-
...TellerManagementReadPlatformServiceImpl.java | 159 ++++++++++++++++---
4 files changed, 162 insertions(+), 37 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-fineract/blob/8d3f7af1/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
index 0ea3d8b..a69dc1f 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
@@ -37,6 +37,8 @@ import org.apache.fineract.commands.service.CommandWrapperBuilder;
import org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService;
import org.apache.fineract.infrastructure.core.data.CommandProcessingResult;
import org.apache.fineract.infrastructure.core.serialization.DefaultToApiJsonSerializer;
+import org.apache.fineract.infrastructure.core.service.Page;
+import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
import org.apache.fineract.organisation.teller.data.CashierData;
import org.apache.fineract.organisation.teller.data.CashierTransactionData;
@@ -248,15 +250,16 @@ public class TellerApiResource {
@Consumes({ MediaType.APPLICATION_JSON })
@Produces(MediaType.APPLICATION_JSON)
public String getTransactionsForCashier(@PathParam("tellerId") final Long tellerId, @PathParam("cashierId") final Long cashierId,
- @QueryParam("currencyCode") final String currencyCode) {
+ @QueryParam("currencyCode") final String currencyCode, @QueryParam("offset") final Integer offset, @QueryParam("limit") final Integer limit,
+ @QueryParam("orderBy") final String orderBy, @QueryParam("sortOrder") final String sortOrder) {
final TellerData teller = this.readPlatformService.findTeller(tellerId);
final CashierData cashier = this.readPlatformService.findCashier(cashierId);
final Date fromDate = null;
final Date toDate = null;
-
- final Collection<CashierTransactionData> cashierTxns = this.readPlatformService.retrieveCashierTransactions(cashierId, false,
- fromDate, toDate, currencyCode);
+ final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
+ final Page<CashierTransactionData> cashierTxns = this.readPlatformService.retrieveCashierTransactions(cashierId, false,
+ fromDate, toDate, currencyCode, searchParameters);
return this.jsonSerializer.serialize(cashierTxns);
}
@@ -266,15 +269,19 @@ public class TellerApiResource {
@Consumes({ MediaType.APPLICATION_JSON })
@Produces(MediaType.APPLICATION_JSON)
public String getTransactionsWtihSummaryForCashier(@PathParam("tellerId") final Long tellerId,
- @PathParam("cashierId") final Long cashierId, @QueryParam("currencyCode") final String currencyCode) {
+ @PathParam("cashierId") final Long cashierId, @QueryParam("currencyCode") final String currencyCode,
+ @QueryParam("offset") final Integer offset, @QueryParam("limit") final Integer limit,
+ @QueryParam("orderBy") final String orderBy, @QueryParam("sortOrder") final String sortOrder) {
final TellerData teller = this.readPlatformService.findTeller(tellerId);
final CashierData cashier = this.readPlatformService.findCashier(cashierId);
final Date fromDate = null;
final Date toDate = null;
+
+ final SearchParameters searchParameters = SearchParameters.forPagination(offset, limit, orderBy, sortOrder);
- final CashierTransactionsWithSummaryData cashierTxnWithSummary = this.readPlatformService
- .retrieveCashierTransactionsWithSummary(cashierId, false, fromDate, toDate, currencyCode);
+ final CashierTransactionsWithSummaryData cashierTxnWithSummary = this.readPlatformService.retrieveCashierTransactionsWithSummary(
+ cashierId, false, fromDate, toDate, currencyCode, searchParameters);
return this.jsonSerializer.serialize(cashierTxnWithSummary);
}
http://git-wip-us.apache.org/repos/asf/incubator-fineract/blob/8d3f7af1/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionsWithSummaryData.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionsWithSummaryData.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionsWithSummaryData.java
index fcdc23d..10baa83 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionsWithSummaryData.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionsWithSummaryData.java
@@ -20,7 +20,8 @@ package org.apache.fineract.organisation.teller.data;
import java.io.Serializable;
import java.math.BigDecimal;
-import java.util.Collection;
+
+import org.apache.fineract.infrastructure.core.service.Page;
public final class CashierTransactionsWithSummaryData implements Serializable {
@@ -35,10 +36,10 @@ public final class CashierTransactionsWithSummaryData implements Serializable {
private final long cashierId;
private final String cashierName;
- private final Collection<CashierTransactionData> cashierTransactions;
+ private final Page<CashierTransactionData> cashierTransactions;
private CashierTransactionsWithSummaryData(
- final Collection<CashierTransactionData> cashierTransactions,
+ final Page<CashierTransactionData> cashierTransactions,
final BigDecimal sumCashAllocation,
final BigDecimal sumInwardCash,
final BigDecimal sumOutwardCash,
@@ -64,7 +65,7 @@ public final class CashierTransactionsWithSummaryData implements Serializable {
}
public static CashierTransactionsWithSummaryData instance(
- final Collection<CashierTransactionData> cashierTransactions,
+ final Page<CashierTransactionData> cashierTransactions,
final BigDecimal sumCashAllocation,
final BigDecimal sumInwardCash,
final BigDecimal sumOutwardCash,
@@ -128,7 +129,7 @@ public final class CashierTransactionsWithSummaryData implements Serializable {
public String getCashierName() {
return cashierName;
}
- public Collection<CashierTransactionData> getCashierTransactions() {
+ public Page<CashierTransactionData> getCashierTransactions() {
return cashierTransactions;
}
http://git-wip-us.apache.org/repos/asf/incubator-fineract/blob/8d3f7af1/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformService.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformService.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformService.java
index 57ce7fa..6265636 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformService.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformService.java
@@ -18,6 +18,8 @@
*/
package org.apache.fineract.organisation.teller.service;
+import org.apache.fineract.infrastructure.core.service.Page;
+import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.organisation.teller.data.CashierData;
import org.apache.fineract.organisation.teller.data.CashierTransactionData;
import org.apache.fineract.organisation.teller.data.CashierTransactionsWithSummaryData;
@@ -60,10 +62,10 @@ public interface TellerManagementReadPlatformService {
public Collection<CashierData> retrieveCashiersForTellers(String sqlSearch, Long tellerId);
- public Collection<CashierTransactionData> retrieveCashierTransactions(Long cashierId, boolean includeAllTellers, Date fromDate,
- Date toDate, String currencyCode);
+ public Page<CashierTransactionData> retrieveCashierTransactions(Long cashierId, boolean includeAllTellers, Date fromDate,
+ Date toDate, String currencyCode, final SearchParameters searchParameters);
public CashierTransactionsWithSummaryData retrieveCashierTransactionsWithSummary(Long cashierId, boolean includeAllTellers,
- Date fromDate, Date toDate, String currencyCode);
+ Date fromDate, Date toDate, String currencyCode, final SearchParameters searchParameters);
}
http://git-wip-us.apache.org/repos/asf/incubator-fineract/blob/8d3f7af1/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java
----------------------------------------------------------------------
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java
index c52cb04..75488a3 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/service/TellerManagementReadPlatformServiceImpl.java
@@ -28,7 +28,10 @@ import java.util.Iterator;
import org.apache.commons.lang.StringUtils;
import org.apache.fineract.infrastructure.core.domain.JdbcSupport;
import org.apache.fineract.infrastructure.core.exception.UnrecognizedQueryParamException;
+import org.apache.fineract.infrastructure.core.service.Page;
+import org.apache.fineract.infrastructure.core.service.PaginationHelper;
import org.apache.fineract.infrastructure.core.service.RoutingDataSource;
+import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
import org.apache.fineract.organisation.monetary.data.CurrencyData;
import org.apache.fineract.organisation.monetary.service.CurrencyReadPlatformService;
@@ -66,6 +69,7 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
private final OfficeReadPlatformService officeReadPlatformService;
private final StaffReadPlatformService staffReadPlatformService;
private final CurrencyReadPlatformService currencyReadPlatformService;
+ private final PaginationHelper<CashierTransactionData> paginationHelper = new PaginationHelper<>();
@Autowired
public TellerManagementReadPlatformServiceImpl(final PlatformSecurityContext context, final RoutingDataSource dataSource,
@@ -439,7 +443,7 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
@Override
public CashierTransactionsWithSummaryData retrieveCashierTransactionsWithSummary(final Long cashierId, final boolean includeAllTellers,
- final Date fromDate, final Date toDate, final String currencyCode) {
+ final Date fromDate, final Date toDate, final String currencyCode, final SearchParameters searchParameters) {
CashierData cashierData = findCashier(cashierId);
Long staffId = cashierData.getStaffId();
StaffData staffData = staffReadPlatformService.retrieveStaff(staffId);
@@ -456,7 +460,7 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
Collection<CashierTransactionTypeTotalsData> cashierTxnTypeTotals = this.jdbcTemplate.query(sql, ctsm, new Object[] { cashierId,
currencyCode, hierarchySearchString, cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode,
- hierarchySearchString });
+ hierarchySearchString, cashierId, currencyCode, hierarchySearchString });
Iterator<CashierTransactionTypeTotalsData> itr = cashierTxnTypeTotals.iterator();
BigDecimal allocAmount = new BigDecimal(0);
@@ -479,8 +483,8 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
}
}
- final Collection<CashierTransactionData> cashierTransactions = retrieveCashierTransactions(cashierId, includeAllTellers, fromDate,
- toDate, currencyCode);
+ final Page<CashierTransactionData> cashierTransactions = retrieveCashierTransactions(cashierId, includeAllTellers, fromDate,
+ toDate, currencyCode, searchParameters);
CashierTransactionData cashierTxnTemplate = retrieveCashierTxnTemplate(cashierId);
@@ -491,8 +495,8 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
}
@Override
- public Collection<CashierTransactionData> retrieveCashierTransactions(final Long cashierId, final boolean includeAllTellers,
- final Date fromDate, final Date toDate, final String currencyCode) {
+ public Page<CashierTransactionData> retrieveCashierTransactions(final Long cashierId, final boolean includeAllTellers,
+ final Date fromDate, final Date toDate, final String currencyCode, final SearchParameters searchParameters) {
CashierData cashierData = findCashier(cashierId);
Long staffId = cashierData.getStaffId();
StaffData staffData = staffReadPlatformService.retrieveStaff(staffId);
@@ -507,20 +511,41 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
final CashierTransactionMapper ctm = new CashierTransactionMapper();
- final String sql = "select * from (select " + ctm.cashierTxnSchema()
+ String sql = "select * from (select " + ctm.cashierTxnSchema()
+ " where txn.cashier_id = ? and txn.currency_code = ? and o.hierarchy like ? ) cashier_txns " + " union (select "
+ ctm.savingsTxnSchema()
+ " where sav_txn.is_reversed = 0 and c.id = ? and sav.currency_code = ? and o.hierarchy like ? and "
+ " sav_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) "
- + " and renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'withdrawal') ) " + " union (select "
+ + " and renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'withdrawal', 'Annual Fee', 'Waive Charge', 'Interest Posting', 'Overdraft Interest') "
+ + " and (sav_txn.payment_detail_id IS NULL OR payType.is_cash_payment = 1) "
+ + " AND acnttrans.id IS NULL ) "
+ + " union (select "
+ ctm.loansTxnSchema()
+ " where loan_txn.is_reversed = 0 and c.id = ? and loan.currency_code = ? and o.hierarchy like ? and "
+ " loan_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) "
- + " and renum.enum_value in ('Repayment At Disbursement','Repayment', 'Recovery Payment','Disbursement') ) "
+ + " and renum.enum_value in ('REPAYMENT_AT_DISBURSEMENT','REPAYMENT', 'RECOVERY_REPAYMENT','DISBURSEMENT', 'CHARGE_PAYMENT', 'WAIVE_CHARGES', 'WAIVE_INTEREST', 'WRITEOFF') "
+ + " and (loan_txn.payment_detail_id IS NULL OR payType.is_cash_payment = 1) "
+ + " AND acnttrans.id IS NULL ) "
+ + " union (select "
+ + ctm.clientTxnSchema()
+ + " where cli_txn.is_reversed = 0 and c.id = ? and cli_txn.currency_code = ? and o.hierarchy like ? and cli_txn.transaction_date "
+ + " between c.start_date and date_add(c.end_date, interval 1 day) "
+ + " and renum.enum_value in ('PAY_CHARGE', 'WAIVE_CHARGE') "
+ + " and (cli_txn.payment_detail_id IS NULL OR payType.is_cash_payment = 1) ) "
+ " order by created_date ";
-
- return this.jdbcTemplate.query(sql, ctm, new Object[] { cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode,
- hierarchySearchString, cashierId, currencyCode, hierarchySearchString });
+
+ if (searchParameters.isLimited()) {
+ sql = sql + " limit " + searchParameters.getLimit();
+ if (searchParameters.isOffset()) {
+ sql = sql + " offset " + searchParameters.getOffset();
+ }
+ }
+ final String sqlCountRows = "SELECT FOUND_ROWS()";
+// return this.jdbcTemplate.query(sql, ctm, new Object[] { cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode,
+// hierarchySearchString, cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode, hierarchySearchString });
+ Object[] params = new Object[] {cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode,
+ hierarchySearchString, cashierId, currencyCode, hierarchySearchString, cashierId, currencyCode, hierarchySearchString };
+ return this.paginationHelper.fetchPage(this.jdbcTemplate, sqlCountRows, sql, params, ctm);
}
private static final class CashierMapper implements RowMapper<CashierData> {
@@ -592,9 +617,9 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
sqlBuilder.append(" sav_txn.id as txn_id, null as cashier_id, ");
sqlBuilder.append(" case ");
- sqlBuilder.append(" when renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge') ");
+ sqlBuilder.append(" when renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'Annual Fee') ");
sqlBuilder.append(" then 103 ");
- sqlBuilder.append(" when renum.enum_value in ('withdrawal', '') ");
+ sqlBuilder.append(" when renum.enum_value in ('withdrawal', 'Waive Charge', 'Interest Posting', 'Overdraft Interest', '') ");
sqlBuilder.append(" then 104 ");
sqlBuilder.append(" else ");
sqlBuilder.append(" 105 ");
@@ -614,6 +639,11 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
sqlBuilder.append(" left join m_appuser user on sav_txn.appuser_id = user.id ");
sqlBuilder.append(" left join m_staff staff on user.staff_id = staff.id ");
sqlBuilder.append(" left join m_cashiers c on c.staff_id = staff.id ");
+ sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = sav_txn.payment_detail_id ");
+ sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
+ sqlBuilder.append(" left join m_account_transfer_transaction acnttrans ");
+ sqlBuilder.append(" on (acnttrans.from_savings_transaction_id = sav_txn.id ");
+ sqlBuilder.append(" or acnttrans.to_savings_transaction_id = sav_txn.id) ");
return sqlBuilder.toString();
}
@@ -624,9 +654,9 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
sqlBuilder.append(" loan_txn.id as txn_id, c.id as cashier_id, ");
sqlBuilder.append(" case ");
- sqlBuilder.append(" when renum.enum_value in ('Repayment At Disbursement','Repayment', 'Recovery Payment') ");
+ sqlBuilder.append(" when renum.enum_value in ('REPAYMENT_AT_DISBURSEMENT','REPAYMENT', 'RECOVERY_REPAYMENT', 'CHARGE_PAYMENT') ");
sqlBuilder.append(" then 103 ");
- sqlBuilder.append(" when renum.enum_value in ('Disbursement') ");
+ sqlBuilder.append(" when renum.enum_value in ('DISBURSEMENT', 'WAIVE_INTEREST', 'WRITEOFF', 'WAIVE_CHARGES') ");
sqlBuilder.append(" then 104 ");
sqlBuilder.append(" else ");
sqlBuilder.append(" 105 ");
@@ -639,13 +669,51 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
.append(" o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
sqlBuilder.append(" from m_loan_transaction loan_txn ");
sqlBuilder
- .append(" left join r_enum_value renum on loan_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'transaction_type_enum' ");
+ .append(" left join r_enum_value renum on loan_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'loan_transaction_type_enum' ");
sqlBuilder.append(" left join m_loan loan on loan_txn.loan_id = loan.id ");
sqlBuilder.append(" left join m_client cl on loan.client_id = cl.id ");
sqlBuilder.append(" left join m_office o on cl.office_id = o.id ");
sqlBuilder.append(" left join m_appuser user on loan_txn.appuser_id = user.id ");
sqlBuilder.append(" left join m_staff staff on user.staff_id = staff.id ");
sqlBuilder.append(" left join m_cashiers c on c.staff_id = staff.id ");
+ sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = loan_txn.payment_detail_id ");
+ sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
+ sqlBuilder.append(" left join m_account_transfer_transaction acnttrans ");
+ sqlBuilder.append(" on (acnttrans.from_loan_transaction_id = loan_txn.id ");
+ sqlBuilder.append(" or acnttrans.to_loan_transaction_id = loan_txn.id) ");
+
+ return sqlBuilder.toString();
+ }
+
+ public String clientTxnSchema() {
+
+ final StringBuilder sqlBuilder = new StringBuilder(400);
+
+ sqlBuilder.append(" cli_txn.id AS txn_id, c.id AS cashier_id, ");
+ sqlBuilder.append(" case ");
+ sqlBuilder.append(" when renum.enum_value in ('PAY_CHARGE') ");
+ sqlBuilder.append(" then 103 ");
+ sqlBuilder.append(" when renum.enum_value in ('WAIVE_CHARGE') ");
+ sqlBuilder.append(" then 104 ");
+ sqlBuilder.append(" else ");
+ sqlBuilder.append(" 105 ");
+ sqlBuilder.append(" end as cash_txn_type, ");
+ sqlBuilder.append(" cli_txn.amount as txn_amount, cli_txn.transaction_date as txn_date, ");
+ sqlBuilder
+ .append(" concat (renum.enum_value, ', Client:', cl.id, '-', cl.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note, ");
+ sqlBuilder.append(" 'client' as entity_type, cl.id as entity_id, cli_txn.created_date as created_date, ");
+ sqlBuilder
+ .append(" o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
+ sqlBuilder.append(" from m_client_transaction cli_txn ");
+ sqlBuilder
+ .append(" left join r_enum_value renum on cli_txn.transaction_type_enum = renum.enum_id AND renum.enum_name = 'client_transaction_type_enum' ");
+ sqlBuilder.append(" left join m_client cl on cli_txn.client_id = cl.id ");
+ sqlBuilder.append(" left join m_office o on cl.office_id = o.id ");
+ sqlBuilder.append(" left join m_appuser user on cli_txn.appuser_id = user.id ");
+ sqlBuilder.append(" left join m_staff staff on user.staff_id = staff.id ");
+ sqlBuilder.append(" left join m_cashiers c on c.staff_id = staff.id ");
+ sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = cli_txn.payment_detail_id ");
+ sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
return sqlBuilder.toString();
}
@@ -709,9 +777,9 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
sqlBuilder.append(" UNION ");
sqlBuilder.append(" (select sav_txn.id as txn_id, c.id as cashier_id, ");
sqlBuilder.append(" case ");
- sqlBuilder.append(" when renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge') ");
+ sqlBuilder.append(" when renum.enum_value in ('deposit','withdrawal fee', 'Pay Charge', 'Annual Fee') ");
sqlBuilder.append(" then 103 ");
- sqlBuilder.append(" when renum.enum_value in ('withdrawal') ");
+ sqlBuilder.append(" when renum.enum_value in ('withdrawal', 'Waive Charge', 'Interest Posting', 'Overdraft Interest') ");
sqlBuilder.append(" then 104 ");
sqlBuilder.append(" else ");
sqlBuilder.append(" 105 ");
@@ -731,18 +799,25 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
sqlBuilder.append(" left join m_appuser user on sav_txn.appuser_id = user.id ");
sqlBuilder.append(" left join m_staff staff on user.staff_id = staff.id ");
sqlBuilder.append(" left join m_cashiers c on c.staff_id = staff.id ");
+ sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = sav_txn.payment_detail_id ");
+ sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
+ sqlBuilder.append(" left join m_account_transfer_transaction acnttrans ");
+ sqlBuilder.append(" on (acnttrans.from_savings_transaction_id = sav_txn.id ");
+ sqlBuilder.append(" or acnttrans.to_savings_transaction_id = sav_txn.id) ");
sqlBuilder.append(" where sav_txn.is_reversed = 0 and c.id = ? ");
sqlBuilder.append(" and sav.currency_code = ? ");
sqlBuilder.append(" and o.hierarchy like ? ");
sqlBuilder.append(" and sav_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) ");
+ sqlBuilder.append(" and (sav_txn.payment_detail_id IS NULL OR payType.is_cash_payment = 1) ");
+ sqlBuilder.append(" AND acnttrans.id IS NULL ");
sqlBuilder.append(" ) ");
sqlBuilder.append(" UNION ");
sqlBuilder.append(" ( ");
sqlBuilder.append(" select loan_txn.id as txn_id, c.id as cashier_id, ");
sqlBuilder.append(" case ");
- sqlBuilder.append(" when renum.enum_value in ('Repayment At Disbursement','Repayment', 'Recovery Payment') ");
+ sqlBuilder.append(" when renum.enum_value in ('REPAYMENT_AT_DISBURSEMENT','REPAYMENT', 'RECOVERY_REPAYMENT', 'CHARGE_PAYMENT') ");
sqlBuilder.append(" then 103 ");
- sqlBuilder.append(" when renum.enum_value in ('Disbursement') ");
+ sqlBuilder.append(" when renum.enum_value in ('DISBURSEMENT', 'WAIVE_INTEREST', 'WRITEOFF', 'WAIVE_CHARGES') ");
sqlBuilder.append(" then 104 ");
sqlBuilder.append(" else ");
sqlBuilder.append(" 105 ");
@@ -755,17 +830,57 @@ public class TellerManagementReadPlatformServiceImpl implements TellerManagement
.append(" o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
sqlBuilder.append(" from m_loan_transaction loan_txn ");
sqlBuilder
- .append(" left join r_enum_value renum on loan_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'transaction_type_enum' ");
+ .append(" left join r_enum_value renum on loan_txn.transaction_type_enum = renum.enum_id and renum.enum_name = 'loan_transaction_type_enum' ");
sqlBuilder.append(" left join m_loan loan on loan_txn.loan_id = loan.id ");
sqlBuilder.append(" left join m_client cl on loan.client_id = cl.id ");
sqlBuilder.append(" left join m_office o on cl.office_id = o.id ");
sqlBuilder.append(" left join m_appuser user on loan_txn.appuser_id = user.id ");
sqlBuilder.append(" left join m_staff staff on user.staff_id = staff.id ");
sqlBuilder.append(" left join m_cashiers c on c.staff_id = staff.id ");
+ sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = loan_txn.payment_detail_id ");
+ sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
+ sqlBuilder.append(" left join m_account_transfer_transaction acnttrans ");
+ sqlBuilder.append(" on (acnttrans.from_loan_transaction_id = loan_txn.id ");
+ sqlBuilder.append(" or acnttrans.to_loan_transaction_id = loan_txn.id) ");
sqlBuilder.append(" where loan_txn.is_reversed = 0 and c.id = ? ");
sqlBuilder.append(" and loan.currency_code = ? ");
sqlBuilder.append(" and o.hierarchy like ? ");
sqlBuilder.append(" and loan_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) ");
+ sqlBuilder.append(" and (loan_txn.payment_detail_id IS NULL OR payType.is_cash_payment = 1) ");
+ sqlBuilder.append(" AND acnttrans.id IS NULL ");
+ sqlBuilder.append(" ) ");
+ sqlBuilder.append(" UNION ");
+ sqlBuilder.append(" ( ");
+ sqlBuilder.append(" SELECT cli_txn.id AS txn_id, c.id AS cashier_id, ");
+ sqlBuilder.append(" case ");
+ sqlBuilder.append(" WHEN renum.enum_value IN ('PAY_CHARGE') ");
+ sqlBuilder.append(" then 103 ");
+ sqlBuilder.append(" WHEN renum.enum_value IN ('WAIVE_CHARGE') ");
+ sqlBuilder.append(" then 104 ");
+ sqlBuilder.append(" else ");
+ sqlBuilder.append(" 105 ");
+ sqlBuilder.append(" end as cash_txn_type, ");
+ sqlBuilder.append(" cli_txn.amount as txn_amount, cli_txn.transaction_date as txn_date, ");
+ sqlBuilder
+ .append(" concat (renum.enum_value, ', Client:', cl.id, '-', cl.account_no, ',Client:', cl.id, '-',cl.display_name) as txn_note, ");
+ sqlBuilder.append(" 'client' as entity_type, cl.id as entity_id, cli_txn.created_date as created_date, ");
+ sqlBuilder
+ .append(" o.id as office_id, o.name as office_name, null as teller_id, null as teller_name, staff.display_name as cashier_name ");
+ sqlBuilder.append(" from m_client_transaction cli_txn ");
+ sqlBuilder
+ .append(" left join r_enum_value renum ON cli_txn.transaction_type_enum = renum.enum_id AND renum.enum_name = 'client_transaction_type_enum' ");
+ sqlBuilder.append(" left join m_client cl ON cli_txn.client_id = cl.id ");
+ sqlBuilder.append(" left join m_office o ON cl.office_id = o.id ");
+ sqlBuilder.append(" left join m_appuser user ON cli_txn.appuser_id = user.id ");
+ sqlBuilder.append(" left join m_staff staff ON user.staff_id = staff.id ");
+ sqlBuilder.append(" left join m_cashiers c ON c.staff_id = staff.id ");
+ sqlBuilder.append(" left join m_payment_detail payDetails on payDetails.id = cli_txn.payment_detail_id ");
+ sqlBuilder.append(" left join m_payment_type payType on payType.id = payDetails.payment_type_id ");
+ sqlBuilder.append(" where cli_txn.is_reversed = 0 AND c.id = ? ");
+ sqlBuilder.append(" and cli_txn.currency_code = ? ");
+ sqlBuilder.append(" and o.hierarchy LIKE ? ");
+ sqlBuilder.append(" and cli_txn.transaction_date between c.start_date and date_add(c.end_date, interval 1 day) ");
+ sqlBuilder.append(" and (cli_txn.payment_detail_id IS NULL OR payType.is_cash_payment = 1) ");
sqlBuilder.append(" ) ");
sqlBuilder.append(" ) txns ");
sqlBuilder.append(" group by cash_txn_type ");