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;
+ }
+
+}