This is an automated email from the ASF dual-hosted git repository.

arnold pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/fineract.git


The following commit(s) were added to refs/heads/develop by this push:
     new d202145a8 rectify query for cashier-teller transactions
d202145a8 is described below

commit d202145a88cfa83ffd03f64434ca44b49602ea7b
Author: Wilfred Kigenyi <[email protected]>
AuthorDate: Sun Apr 28 13:49:03 2024 +0300

    rectify query for cashier-teller transactions
---
 .../organisation/teller/api/TellerApiResource.java |   8 +-
 .../teller/exception/CashierNotFoundException.java |   1 +
 .../teller/exception/TellerNotFoundException.java  |   5 +
 .../database/DatabaseSpecificSQLGenerator.java     |   9 ++
 .../TellerManagementReadPlatformServiceImpl.java   | 131 ++++++++-------------
 .../teller/CashierSummaryAndTransactionsTest.java  |  65 ++++++++++
 .../teller/CashierTransactionsHelper.java          | 124 +++++++++++++++++++
 7 files changed, 259 insertions(+), 84 deletions(-)

diff --git 
a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
 
b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
index 283a9a0a3..f91020457 100644
--- 
a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
+++ 
b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/api/TellerApiResource.java
@@ -300,9 +300,9 @@ public class TellerApiResource {
     @Path("{tellerId}/cashiers/{cashierId}/transactions")
     @Consumes({ MediaType.APPLICATION_JSON })
     @Produces(MediaType.APPLICATION_JSON)
-    @Operation(summary = "Retrieve Cashier Transaction", description = "")
+    @Operation(summary = "Retrieve Cashier Transactions", description = "")
     @ApiResponses({
-            @ApiResponse(responseCode = "200", description = "OK", content = 
@Content(array = @ArraySchema(schema = @Schema(implementation = 
TellerApiResourceSwagger.GetTellersTellerIdCashiersCashiersIdTransactionsResponse.class))))
 })
+            @ApiResponse(responseCode = "200", description = "OK", content = 
@Content(schema = @Schema(implementation = 
TellerApiResourceSwagger.GetTellersTellerIdCashiersCashiersIdTransactionsResponse.class)))
 })
     public String getTransactionsForCashier(@PathParam("tellerId") 
@Parameter(description = "tellerId") final Long tellerId,
             @PathParam("cashierId") @Parameter(description = "cashierId") 
final Long cashierId,
             @QueryParam("currencyCode") @Parameter(description = 
"currencyCode") final String currencyCode,
@@ -331,10 +331,10 @@ public class TellerApiResource {
     @Path("{tellerId}/cashiers/{cashierId}/summaryandtransactions")
     @Consumes({ MediaType.APPLICATION_JSON })
     @Produces(MediaType.APPLICATION_JSON)
-    @Operation(summary = "Transactions Wtih Summary For Cashier", description 
= "")
+    @Operation(summary = "Retrieve Transactions With Summary For Cashier", 
description = "")
     @ApiResponses({
             @ApiResponse(responseCode = "200", description = "OK", content = 
@Content(schema = @Schema(implementation = 
TellerApiResourceSwagger.GetTellersTellerIdCashiersCashiersIdSummaryAndTransactionsResponse.class)))
 })
-    public String getTransactionsWtihSummaryForCashier(@PathParam("tellerId") 
@Parameter(description = "tellerId") final Long tellerId,
+    public String getTransactionsWithSummaryForCashier(@PathParam("tellerId") 
@Parameter(description = "tellerId") final Long tellerId,
             @PathParam("cashierId") @Parameter(description = "cashierId") 
final Long cashierId,
             @QueryParam("currencyCode") @Parameter(description = 
"currencyCode") final String currencyCode,
             @QueryParam("offset") @Parameter(description = "offset") final 
Integer offset,
diff --git 
a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/exception/CashierNotFoundException.java
 
b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/exception/CashierNotFoundException.java
index 4f00281aa..247844ce4 100644
--- 
a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/exception/CashierNotFoundException.java
+++ 
b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/exception/CashierNotFoundException.java
@@ -40,4 +40,5 @@ public class CashierNotFoundException extends 
AbstractPlatformResourceNotFoundEx
     public CashierNotFoundException(Long cashierId) {
         super(ERROR_MESSAGE_CODE, DEFAULT_ERROR_MESSAGE, cashierId);
     }
+
 }
diff --git 
a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/exception/TellerNotFoundException.java
 
b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/exception/TellerNotFoundException.java
index 231c4018b..677ec723e 100644
--- 
a/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/exception/TellerNotFoundException.java
+++ 
b/fineract-branch/src/main/java/org/apache/fineract/organisation/teller/exception/TellerNotFoundException.java
@@ -19,6 +19,7 @@
 package org.apache.fineract.organisation.teller.exception;
 
 import 
org.apache.fineract.infrastructure.core.exception.AbstractPlatformResourceNotFoundException;
+import org.springframework.dao.EmptyResultDataAccessException;
 
 /**
  * Indicates that a teller could not be found.
@@ -40,4 +41,8 @@ public class TellerNotFoundException extends 
AbstractPlatformResourceNotFoundExc
     public TellerNotFoundException(Long tellerId) {
         super(ERROR_MESSAGE_CODE, DEFAULT_ERROR_MESSAGE, tellerId);
     }
+
+    public TellerNotFoundException(Long id, EmptyResultDataAccessException e) {
+        super("error.msg.teller.id.invalid", "Teller with identifier " + id + 
" does not exist", id, e);
+    }
 }
diff --git 
a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseSpecificSQLGenerator.java
 
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseSpecificSQLGenerator.java
index 0da46a80b..5bf4b2ad3 100644
--- 
a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseSpecificSQLGenerator.java
+++ 
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/DatabaseSpecificSQLGenerator.java
@@ -289,4 +289,13 @@ public class DatabaseSpecificSQLGenerator {
             }
         };
     }
+
+    public String incrementDateByOneDay(String dateColumn) {
+        return switch (getDialect()) {
+            case POSTGRESQL -> " " + dateColumn + "+1";
+            case MYSQL -> " DATE_ADD(" + dateColumn + ", INTERVAL 1 DAY) ";
+        };
+
+    }
+
 }
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 f9b9b5b5d..f0fb0ff28 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
@@ -398,23 +398,13 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
     @Override
     public CashierTransactionsWithSummaryData 
retrieveCashierTransactionsWithSummary(final Long cashierId, final boolean 
includeAllTellers,
             final LocalDate fromDate, final LocalDate toDate, final String 
currencyCode, final SearchParameters searchParameters) {
-        CashierData cashierData = findCashier(cashierId);
-        Long staffId = cashierData.getStaffId();
-        StaffData staffData = staffReadPlatformService.retrieveStaff(staffId);
-        OfficeData officeData = 
officeReadPlatformService.retrieveOffice(staffData.getOfficeId());
-        final String hierarchy = officeData.getHierarchy();
-        String hierarchySearchString;
-        if (includeAllTellers) {
-            hierarchySearchString = "." + "%";
-        } else {
-            hierarchySearchString = hierarchy;
-        }
-        final CashierTransactionSummaryMapper ctsm = new 
CashierTransactionSummaryMapper();
-        final String sql = "select " + ctsm.cashierTxnSummarySchema() + " 
limit 1000";
 
+        final String nextDay = 
sqlGenerator.incrementDateByOneDay("c.end_date");
+
+        final CashierTransactionSummaryMapper ctsm = new 
CashierTransactionSummaryMapper();
+        final String sql = "SELECT " + ctsm.cashierTxnSummarySchema(nextDay) + 
" LIMIT 1000";
         Collection<CashierTransactionTypeTotalsData> cashierTxnTypeTotals = 
this.jdbcTemplate.query(sql, ctsm, // NOSONAR
-                new Object[] { cashierId, currencyCode, hierarchySearchString, 
cashierId, currencyCode, hierarchySearchString, cashierId,
-                        currencyCode, hierarchySearchString, cashierId, 
currencyCode, hierarchySearchString });
+                new Object[] { cashierId, currencyCode, cashierId, 
currencyCode, cashierId, currencyCode, cashierId, currencyCode });
 
         Iterator<CashierTransactionTypeTotalsData> itr = 
cashierTxnTypeTotals.iterator();
         BigDecimal allocAmount = new BigDecimal(0);
@@ -451,38 +441,24 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
     @Override
     public Page<CashierTransactionData> retrieveCashierTransactions(final Long 
cashierId, final boolean includeAllTellers,
             final LocalDate fromDate, final LocalDate toDate, final String 
currencyCode, final SearchParameters searchParameters) {
-        CashierData cashierData = findCashier(cashierId);
-        Long staffId = cashierData.getStaffId();
-        StaffData staffData = staffReadPlatformService.retrieveStaff(staffId);
-        OfficeData officeData = 
officeReadPlatformService.retrieveOffice(staffData.getOfficeId());
-        final String hierarchy = officeData.getHierarchy();
-        String hierarchySearchString = null;
-        if (includeAllTellers) {
-            hierarchySearchString = "." + "%";
-        } else {
-            hierarchySearchString = hierarchy;
-        }
+
+        final String nextDay = 
sqlGenerator.incrementDateByOneDay("c.end_date");
 
         final CashierTransactionMapper ctm = new CashierTransactionMapper();
 
-        String sql = "select * from (select " + ctm.cashierTxnSchema()
-                + " where txn.cashier_id = ? and txn.currency_code = ? and 
o.hierarchy like ? "
-                + "AND ((case when c.full_day then Date(txn.created_date) 
between c.start_date AND c.end_date else ( Date(txn.created_date) between 
c.start_date AND c.end_date"
-                + " ) and ( TIME(txn.created_date) between TIME(c.start_time) 
AND TIME(c.end_time)) end) or txn.txn_type = 101))  cashier_txns "
-                + " union (select " + ctm.savingsTxnSchema()
-                + " where sav_txn.is_reversed = false 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) "
+        String sql = "SELECT * FROM (SELECT " + ctm.cashierTxnSchema() + " 
WHERE txn.cashier_id = ? AND txn.currency_code = ? "
+                + "AND ((txn.created_date between c.start_date AND c.end_date  
) or txn.txn_type = 101))  cashier_txns " + " union (select "
+                + ctm.savingsTxnSchema() + " where sav_txn.is_reversed = false 
and c.id = ? and sav.currency_code = ? "
+                + "and sav_txn.transaction_date between c.start_date and " + 
nextDay
                 + " 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 = true) " + " AND acnttrans.id IS NULL ) "
-                + " union (select " + ctm.loansTxnSchema()
-                + " where loan_txn.is_reversed = false 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_REPAYMENT','DISBURSEMENT', 
'CHARGE_PAYMENT', 'WAIVE_CHARGES', 'WAIVE_INTEREST', 'WRITEOFF') "
+                + " and (sav_txn.payment_detail_id IS NULL OR 
payType.is_cash_payment = true) AND acnttrans.id IS NULL ) "
+                + " union (select " + ctm.loansTxnSchema() + " where 
loan_txn.is_reversed = false and c.id = ? and loan.currency_code = ? "
+                + "and loan_txn.transaction_date between c.start_date and " + 
nextDay
+                + " 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 = true) " + " AND acnttrans.id IS NULL ) "
                 + " union (select " + ctm.clientTxnSchema()
-                + " where cli_txn.is_reversed = false 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') "
+                + " where cli_txn.is_reversed = false and c.id = ? and 
cli_txn.currency_code = ? " + "and cli_txn.transaction_date "
+                + " between c.start_date and  " + nextDay + " and 
renum.enum_value IN ('PAY_CHARGE', 'WAIVE_CHARGE') "
                 + " and (cli_txn.payment_detail_id IS NULL OR 
payType.is_cash_payment = true) ) " + " order by created_date ";
 
         if (searchParameters.hasLimit()) {
@@ -498,8 +474,8 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
         // hierarchySearchString, cashierId, currencyCode,
         // hierarchySearchString, cashierId, currencyCode, 
hierarchySearchString
         // });
-        Object[] params = new Object[] { cashierId, currencyCode, 
hierarchySearchString, cashierId, currencyCode, hierarchySearchString,
-                cashierId, currencyCode, hierarchySearchString, cashierId, 
currencyCode, hierarchySearchString };
+        Object[] params = new Object[] { cashierId, currencyCode, cashierId, 
currencyCode, cashierId, currencyCode, cashierId,
+                currencyCode, };
         return this.paginationHelper.fetchPage(this.jdbcTemplate, sql, params, 
ctm);
     }
 
@@ -547,13 +523,13 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
 
             final StringBuilder sqlBuilder = new StringBuilder(400);
 
-            sqlBuilder.append(" txn.id as txn_id, txn.cashier_id as 
cashier_id, ");
-            sqlBuilder.append(" txn.txn_type as txn_type, ");
-            sqlBuilder.append(" txn.txn_amount as txn_amount, txn.txn_date as 
txn_date, txn.txn_note as txn_note, ");
-            sqlBuilder.append(" txn.entity_type as entity_type, txn.entity_id 
as entity_id, txn.created_date as created_date, ");
+            sqlBuilder.append(" txn.id AS txn_id, txn.cashier_id AS 
cashier_id, ");
+            sqlBuilder.append(" txn.txn_type AS txn_type, ");
+            sqlBuilder.append(" txn.txn_amount AS txn_amount, txn.txn_date AS 
txn_date, txn.txn_note as txn_note, ");
+            sqlBuilder.append(" txn.entity_type AS entity_type, txn.entity_id 
AS entity_id, txn.created_date AS created_date, ");
             sqlBuilder.append(
-                    " o.id as office_id, o.name as office_name, t.id as 
teller_id, t.name as teller_name, s.display_name as cashier_name ");
-            sqlBuilder.append(" from m_cashier_transactions txn ");
+                    " o.id AS office_id, o.name AS office_name, t.id AS 
teller_id, t.name AS teller_name, s.display_name AS cashier_name ");
+            sqlBuilder.append(" FROM m_cashier_transactions AS txn ");
             sqlBuilder.append(" left join m_cashiers c on c.id = 
txn.cashier_id ");
             sqlBuilder.append(" left join m_tellers t on t.id = c.teller_id ");
             sqlBuilder.append(" left join m_office o on o.id = t.office_id ");
@@ -588,8 +564,8 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
             sqlBuilder.append(" left join m_savings_account sav on 
sav_txn.savings_account_id = sav.id ");
             sqlBuilder.append(" left join m_client cl on sav.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 sav_txn.appuser_id 
= user.id ");
-            sqlBuilder.append(" left join m_staff staff on user.staff_id = 
staff.id ");
+            sqlBuilder.append(" left join m_appuser user_ on 
sav_txn.created_by = 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 ");
@@ -626,8 +602,8 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
             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.created_by = user.id ");
-            sqlBuilder.append(" left join m_staff staff on user.staff_id = 
staff.id ");
+            sqlBuilder.append(" left join m_appuser user_ on 
loan_txn.created_by = 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 ");
@@ -662,8 +638,8 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
                     " 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.created_by 
= user.id ");
-            sqlBuilder.append(" left join m_staff staff on user.staff_id = 
staff.id ");
+            sqlBuilder.append(" left join m_appuser user_ on 
cli_txn.created_by = 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 ");
@@ -707,29 +683,27 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
 
     private static final class CashierTransactionSummaryMapper implements 
RowMapper<CashierTransactionTypeTotalsData> {
 
-        public String cashierTxnSummarySchema() {
+        public String cashierTxnSummarySchema(String nextDay) {
 
             final StringBuilder sqlBuilder = new StringBuilder(400);
 
-            sqlBuilder.append(" cash_txn_type, sum(txn_amount) as txn_total 
from ");
-            sqlBuilder.append(" (select * from ");
-            sqlBuilder.append(" (select txn.id as txn_id, txn.cashier_id as 
cashier_id, ");
-            sqlBuilder.append("    txn.txn_type as cash_txn_type, ");
-            sqlBuilder.append("    txn.txn_amount as txn_amount, txn.txn_date 
as txn_date, txn.txn_note as txn_note, ");
-            sqlBuilder.append("    txn.entity_type as entity_type, 
txn.entity_id as entity_id, txn.created_date as created_date, ");
+            sqlBuilder.append(" cash_txn_type, sum(txn_amount) AS txn_total 
FROM ");
+            sqlBuilder.append(" (SELECT * FROM ");
+            sqlBuilder.append(" (SELECT txn.id AS txn_id, txn.cashier_id AS 
cashier_id, ");
+            sqlBuilder.append("  txn.txn_type AS cash_txn_type, ");
+            sqlBuilder.append("  txn.txn_amount AS txn_amount, txn.txn_date AS 
txn_date, txn.txn_note AS txn_note, ");
+            sqlBuilder.append("  txn.entity_type AS entity_type, txn.entity_id 
AS entity_id, txn.created_date AS created_date, ");
             sqlBuilder.append(
-                    "    o.id as office_id, o.name as office_name, t.id as 
teller_id, t.name as teller_name, s.display_name as cashier_name ");
-            sqlBuilder.append("    from m_cashier_transactions txn ");
+                    "  o.id as office_id, o.name AS office_name, t.id AS 
teller_id, t.name AS teller_name, s.display_name AS cashier_name ");
+            sqlBuilder.append("    FROM m_cashier_transactions txn ");
             sqlBuilder.append("    left join m_cashiers c on c.id = 
txn.cashier_id ");
             sqlBuilder.append("    left join m_tellers t on t.id = c.teller_id 
");
             sqlBuilder.append("    left join m_office o on o.id = t.office_id 
");
             sqlBuilder.append("    left join m_staff s on s.id = c.staff_id ");
             sqlBuilder.append("    where txn.cashier_id = ? ");
-            sqlBuilder.append(" AND (( case when c.full_day then 
Date(txn.created_date) between c.start_date AND c.end_date ");
-            sqlBuilder.append(
-                    " else ( Date(txn.created_date) between c.start_date AND 
c.end_date) and  ( TIME(txn.created_date) between TIME(c.start_time) AND 
TIME(c.end_time))  end) or txn.txn_type = 101) ");
+            sqlBuilder.append(" AND ((  txn.created_date between c.start_date 
AND c.end_date ) or txn.txn_type = 101) ");
             sqlBuilder.append(" and   txn.currency_code = ? ");
-            sqlBuilder.append("    and o.hierarchy like ?  ) cashier_txns ");
+            sqlBuilder.append(" ) cashier_txns ");
             sqlBuilder.append("    UNION ");
             sqlBuilder.append("    (select sav_txn.id as txn_id, c.id as 
cashier_id, ");
             sqlBuilder.append("    case ");
@@ -752,8 +726,8 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
             sqlBuilder.append("    left join m_savings_account sav on 
sav_txn.savings_account_id = sav.id ");
             sqlBuilder.append("    left join m_client cl on sav.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 
sav_txn.appuser_id = user.id ");
-            sqlBuilder.append("    left join m_staff staff on user.staff_id = 
staff.id ");
+            sqlBuilder.append("    left join m_appuser user_ on 
sav_txn.created_by = 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 ");
@@ -762,8 +736,7 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
             sqlBuilder.append(" or acnttrans.to_savings_transaction_id = 
sav_txn.id) ");
             sqlBuilder.append("    where sav_txn.is_reversed = false 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.transaction_date between 
c.start_date and c.end_date ");
             sqlBuilder.append("    and (sav_txn.payment_detail_id IS NULL OR 
payType.is_cash_payment = true) ");
             sqlBuilder.append("    AND acnttrans.id IS NULL  ");
             sqlBuilder.append("    ) ");
@@ -791,8 +764,8 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
             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.created_by = user.id ");
-            sqlBuilder.append("    left join m_staff staff on user.staff_id = 
staff.id ");
+            sqlBuilder.append("    left join m_appuser user_ on 
loan_txn.created_by = 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 ");
@@ -801,8 +774,7 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
             sqlBuilder.append(" or acnttrans.to_loan_transaction_id = 
loan_txn.id) ");
             sqlBuilder.append("    where loan_txn.is_reversed = false 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.transaction_date between 
c.start_date and " + nextDay);
             sqlBuilder.append("    and (loan_txn.payment_detail_id IS NULL OR 
payType.is_cash_payment = true) ");
             sqlBuilder.append("    AND acnttrans.id IS NULL  ");
             sqlBuilder.append("    ) ");
@@ -828,15 +800,14 @@ public class TellerManagementReadPlatformServiceImpl 
implements TellerManagement
                     "    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.created_by = user.id ");
-            sqlBuilder.append("    left join m_staff staff ON user.staff_id = 
staff.id ");
+            sqlBuilder.append("    left join m_appuser user_ ON 
cli_txn.created_by = 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 = false 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.transaction_date between 
c.start_date and " + nextDay);
             sqlBuilder.append(" and (cli_txn.payment_detail_id IS NULL OR 
payType.is_cash_payment = true)  ");
             sqlBuilder.append("    ) ");
             sqlBuilder.append("    ) txns ");
diff --git 
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/organization/teller/CashierSummaryAndTransactionsTest.java
 
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/organization/teller/CashierSummaryAndTransactionsTest.java
new file mode 100644
index 000000000..c001f8b89
--- /dev/null
+++ 
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/organization/teller/CashierSummaryAndTransactionsTest.java
@@ -0,0 +1,65 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.integrationtests.organization.teller;
+
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+
+import io.restassured.builder.RequestSpecBuilder;
+import io.restassured.builder.ResponseSpecBuilder;
+import io.restassured.http.ContentType;
+import io.restassured.specification.RequestSpecification;
+import io.restassured.specification.ResponseSpecification;
+import 
org.apache.fineract.client.models.GetTellersTellerIdCashiersCashiersIdTransactionsResponse;
+import org.apache.fineract.integrationtests.common.Utils;
+import org.apache.fineract.integrationtests.common.organisation.StaffHelper;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+public class CashierSummaryAndTransactionsTest {
+
+    private CashierTransactionsHelper cashierTransactionsHelper;
+
+    private ResponseSpecification responseSpecification;
+    private RequestSpecification requestSpecification;
+
+    @BeforeEach
+    public void setup() {
+        Utils.initializeRESTAssured();
+
+        requestSpecification = new 
RequestSpecBuilder().setContentType(ContentType.JSON).build();
+        requestSpecification.header("Authorization", "Basic " + 
Utils.loginIntoServerAndGetBase64EncodedAuthenticationKey());
+        responseSpecification = new 
ResponseSpecBuilder().expectStatusCode(200).build();
+        cashierTransactionsHelper = new 
CashierTransactionsHelper(requestSpecification, responseSpecification);
+        StaffHelper.createStaff(requestSpecification, responseSpecification);
+        cashierTransactionsHelper.createTeller(requestSpecification, 
responseSpecification);
+        cashierTransactionsHelper.createCashier(requestSpecification, 
responseSpecification);
+
+    }
+
+    @Test
+    public void testGetCashierTransactions() {
+        Long tellerId = 1L;
+        Long cashierId = 1L;
+
+        final GetTellersTellerIdCashiersCashiersIdTransactionsResponse result 
= cashierTransactionsHelper
+                
.getTellersTellerIdCashiersCashiersIdTransactionsResponse(tellerId, cashierId, 
"UGX", 0, 0, null, null);
+        assertNotNull(result);
+    }
+
+}
diff --git 
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/organization/teller/CashierTransactionsHelper.java
 
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/organization/teller/CashierTransactionsHelper.java
new file mode 100644
index 000000000..9b853b30e
--- /dev/null
+++ 
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/organization/teller/CashierTransactionsHelper.java
@@ -0,0 +1,124 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.integrationtests.organization.teller;
+
+import com.google.gson.Gson;
+import io.restassured.specification.RequestSpecification;
+import io.restassured.specification.ResponseSpecification;
+import java.util.HashMap;
+import java.util.Map;
+import 
org.apache.fineract.client.models.GetTellersTellerIdCashiersCashiersIdSummaryAndTransactionsResponse;
+import 
org.apache.fineract.client.models.GetTellersTellerIdCashiersCashiersIdTransactionsResponse;
+import org.apache.fineract.integrationtests.client.IntegrationTest;
+import org.apache.fineract.integrationtests.common.Utils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+public class CashierTransactionsHelper extends IntegrationTest {
+
+    private final ResponseSpecification responseSpecification;
+    private final RequestSpecification requestSpecification;
+
+    private static final String CREATE_CASHIER_URL = 
"/fineract-provider/api/v1/tellers/1/cashiers";
+    private static final String CREATE_TELLER_URL = 
"/fineract-provider/api/v1/tellers";
+    private static final Logger LOG = 
LoggerFactory.getLogger(CashierTransactionsHelper.class);
+
+    public CashierTransactionsHelper(final RequestSpecification 
requestSpecification, final ResponseSpecification responseSpecification) {
+        this.requestSpecification = requestSpecification;
+        this.responseSpecification = responseSpecification;
+    }
+
+    public GetTellersTellerIdCashiersCashiersIdTransactionsResponse 
getTellersTellerIdCashiersCashiersIdTransactionsResponse(Long tellerId,
+            Long cashierId, String currencyCode, int offset, int limit, String 
orderBy, String sortOrder) {
+        return ok(fineract().tellers.getTransactionsForCashier(tellerId, 
cashierId, currencyCode, offset, limit, orderBy, sortOrder));
+    }
+
+    public GetTellersTellerIdCashiersCashiersIdSummaryAndTransactionsResponse 
getTellersTellerIdCashiersCashiersIdSummaryAndTransactionsResponse(
+            Long tellerId, Long cashierId, String currencyCode, int offset, 
int limit, String orderBy, String sortOrder) {
+        return 
ok(fineract().tellers.getTransactionsWithSummaryForCashier(tellerId, cashierId, 
currencyCode, offset, limit, orderBy,
+                sortOrder));
+    }
+
+    public static Integer createTeller(final RequestSpecification requestSpec, 
final ResponseSpecification responseSpec) {
+        return (Integer) createTellerWithJson(requestSpec, responseSpec, 
createTellerAsJSON()).get("resourceId");
+    }
+
+    public static Map<String, Object> createTellerWithJson(final 
RequestSpecification requestSpec, final ResponseSpecification responseSpec,
+            final String json) {
+
+        final String url = CREATE_TELLER_URL + "?" + Utils.TENANT_IDENTIFIER;
+        return Utils.performServerPost(requestSpec, responseSpec, url, json, 
"");
+    }
+
+    public static String createTellerAsJSON() {
+
+        final Map<String, Object> map = getMapWithStartDate();
+
+        map.put("officeId", 1);
+        map.put("name", Utils.uniqueRandomStringGenerator("Teller 1", 5));
+        map.put("description", Utils.uniqueRandomStringGenerator("Teller For 
Testing", 4));
+        map.put("status", 300);
+
+        LOG.info("map :  {}", map);
+        return new Gson().toJson(map);
+    }
+
+    public static Map<String, Object> getMapWithStartDate() {
+        HashMap<String, Object> map = new HashMap<>();
+
+        map.put("locale", "en");
+        map.put("dateFormat", "dd MMMM yyyy");
+        map.put("startDate", "20 September 2011");
+
+        return map;
+    }
+
+    public static Integer createCashier(final RequestSpecification 
requestSpec, final ResponseSpecification responseSpec) {
+        return (Integer) createCashierWithJson(requestSpec, responseSpec, 
createCashierAsJSON()).get("resourceId");
+    }
+
+    public static Map<String, Object> createCashierWithJson(final 
RequestSpecification requestSpec,
+            final ResponseSpecification responseSpec, final String json) {
+        final String url = CREATE_CASHIER_URL + "?" + Utils.TENANT_IDENTIFIER;
+        return Utils.performServerPost(requestSpec, responseSpec, url, json, 
"");
+    }
+
+    public static String createCashierAsJSON() {
+
+        final Map<String, Object> map = getMapWithDates();
+
+        map.put("staffId", 1);
+        map.put("description", Utils.uniqueRandomStringGenerator("test__", 4));
+        LOG.info("map :  {}", map);
+        return new Gson().toJson(map);
+    }
+
+    public static Map<String, Object> getMapWithDates() {
+        HashMap<String, Object> map = new HashMap<>();
+
+        map.put("locale", "en");
+        map.put("dateFormat", "dd MMMM yyyy");
+        map.put("startDate", "01 January 2023");
+        map.put("endDate", "31 December 2023");
+        map.put("isFullDay", true);
+
+        return map;
+    }
+
+}


Reply via email to