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 <adi.r...@confluxtechnologies.com> Authored: Wed Sep 21 17:07:55 2016 +0530 Committer: Adi Narayana Raju <adi.r...@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 ");
