fineract-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From nazeer1100...@apache.org
Subject incubator-fineract git commit: [MIFOSX-2784] Including all transactions in Teller Cashier transaction list
Date Thu, 22 Sep 2016 06:41:53 GMT
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.raju@confluxtechnologies.com>
Authored: Wed Sep 21 17:07:55 2016 +0530
Committer: Adi Narayana Raju <adi.raju@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 ");


Mime
View raw message