FINERACT-1724 Improve cashier data validator
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionDataValidator.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionDataValidator.java
index dc50061..39c7933 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionDataValidator.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/data/CashierTransactionDataValidator.java
@@ -21,13 +21,15 @@
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.OffsetDateTime;
+import java.util.HashMap;
+import java.util.Map;
import org.apache.fineract.infrastructure.core.api.JsonCommand;
import org.apache.fineract.infrastructure.core.service.DateUtils;
import org.apache.fineract.infrastructure.core.service.MathUtil;
import org.apache.fineract.infrastructure.core.service.SearchParameters;
import org.apache.fineract.organisation.teller.domain.Cashier;
import org.apache.fineract.organisation.teller.domain.Teller;
-import org.apache.fineract.organisation.teller.exception.CashierAlreadyAlloacated;
+import org.apache.fineract.organisation.teller.exception.CashierAlreadyAllocated;
import org.apache.fineract.organisation.teller.exception.CashierDateRangeOutOfTellerDateRangeException;
import org.apache.fineract.organisation.teller.exception.CashierInsufficientAmountException;
import org.apache.fineract.organisation.teller.service.TellerManagementReadPlatformService;
@@ -36,19 +38,19 @@
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
-import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;
@Component
public class CashierTransactionDataValidator {
private final TellerManagementReadPlatformService tellerManagementReadPlatformService;
- private final JdbcTemplate jdbcTemplate;
+ private final NamedParameterJdbcTemplate jdbcTemplate;
private static final Logger LOG = LoggerFactory.getLogger(CashierTransactionDataValidator.class);
@Autowired
public CashierTransactionDataValidator(final TellerManagementReadPlatformService tellerManagementReadPlatformService,
- final JdbcTemplate jdbcTemplate) {
+ final NamedParameterJdbcTemplate jdbcTemplate) {
this.tellerManagementReadPlatformService = tellerManagementReadPlatformService;
this.jdbcTemplate = jdbcTemplate;
}
@@ -80,25 +82,35 @@
final LocalDate endDate = cashier.getEndDate();
final LocalDate tellerFromDate = teller.getStartDate();
final LocalDate tellerEndDate = teller.getEndDate();
- // to validate cashier date range in range of teller date range
+ // Validate cashier date range in range of teller date range
if (DateUtils.isBefore(fromDate, tellerFromDate) || DateUtils.isBefore(endDate, tellerFromDate)
|| (tellerEndDate != null && (DateUtils.isAfter(fromDate, tellerEndDate) || DateUtils.isAfter(endDate, tellerEndDate)))) {
throw new CashierDateRangeOutOfTellerDateRangeException();
}
- // to validate cashier has not been assigned for same duration
- String sql = "select count(*) from m_cashiers c where c.staff_id = " + staffId + " AND " + "(('" + fromDate
- + "' BETWEEN c.start_date AND c.end_date OR '" + endDate + "' BETWEEN c.start_date AND c.end_date )"
- + " OR ( c.start_date BETWEEN '" + fromDate + "' AND '" + endDate + "' OR c.end_date BETWEEN '" + fromDate + "' AND '"
- + endDate + "'))";
+
+ // Validate cashier has not been assigned for the same duration
+ String sql = "SELECT COUNT(*) FROM m_cashiers c WHERE c.staff_id = :staffId AND "
+ + "((:fromDate BETWEEN c.start_date AND c.end_date OR :endDate BETWEEN c.start_date AND c.end_date) "
+ + "OR (c.start_date BETWEEN :fromDate AND :endDate OR c.end_date BETWEEN :fromDate AND :endDate))";
+
if (!cashier.getIsFullDay()) {
- String startTime = cashier.getStartTime();
- String endTime = cashier.getEndTime();
- sql = sql + " AND ( Time(c.start_time) BETWEEN TIME('" + startTime + "') and TIME('" + endTime
- + "') or Time(c.end_time) BETWEEN TIME('" + startTime + "') and TIME('" + endTime + "')) ";
+ sql += " AND (TIME(c.start_time) BETWEEN TIME(:startTime) AND TIME(:endTime) "
+ + "OR TIME(c.end_time) BETWEEN TIME(:startTime) AND TIME(:endTime))";
}
- int count = this.jdbcTemplate.queryForObject(sql, Integer.class); // NOSONAR
- if (count > 0) {
- throw new CashierAlreadyAlloacated();
+
+ Map<String, Object> paramMap = new HashMap<>();
+ paramMap.put("staffId", staffId);
+ paramMap.put("fromDate", fromDate);
+ paramMap.put("endDate", endDate);
+
+ if (!cashier.getIsFullDay()) {
+ paramMap.put("startTime", cashier.getStartTime());
+ paramMap.put("endTime", cashier.getEndTime());
+ }
+
+ Integer count = jdbcTemplate.queryForObject(sql, paramMap, Integer.class);
+ if (count != null && count > 0) {
+ throw new CashierAlreadyAllocated();
}
}
@@ -106,12 +118,18 @@
LocalDate tenantDate = DateUtils.getLocalDateOfTenant();
OffsetDateTime tenantDateTime = DateUtils.getOffsetDateTimeOfTenant();
if (user.getStaff() != null) {
- String sql = "select c.id from m_cashiers c where c.staff_id = " + user.getStaff().getId() + " AND (case when c.full_day then '"
- + tenantDate + "' BETWEEN c.start_date AND c.end_date else ('" + tenantDate
- + "' BETWEEN c.start_date AND c.end_date and TIME('" + tenantDateTime
- + "') BETWEEN TIME(c.start_time) AND TIME(c.end_time)) end)";
+ String sql = "SELECT c.id FROM m_cashiers c WHERE c.staff_id = :staffId "
+ + "AND (CASE WHEN c.full_day THEN :tenantDate BETWEEN c.start_date AND c.end_date "
+ + "ELSE (:tenantDate BETWEEN c.start_date AND c.end_date AND "
+ + "TIME(:tenantDateTime) BETWEEN TIME(c.start_time) AND TIME(c.end_time)) END)";
+
+ Map<String, Object> paramMap = new HashMap<>();
+ paramMap.put("staffId", user.getStaff().getId());
+ paramMap.put("tenantDate", tenantDate);
+ paramMap.put("tenantDateTime", tenantDateTime);
+
try {
- Long cashierId = this.jdbcTemplate.queryForObject(sql, Long.class); // NOSONAR
+ Long cashierId = jdbcTemplate.queryForObject(sql, paramMap, Long.class);
validateSettleCashAndCashOutTransactions(cashierId, currencyCode, transactionAmount);
} catch (EmptyResultDataAccessException e) {
LOG.error("Problem occurred in validateOnLoanDisbursal function", e);
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/exception/CashierAlreadyAlloacated.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/exception/CashierAlreadyAllocated.java
similarity index 89%
rename from fineract-provider/src/main/java/org/apache/fineract/organisation/teller/exception/CashierAlreadyAlloacated.java
rename to fineract-provider/src/main/java/org/apache/fineract/organisation/teller/exception/CashierAlreadyAllocated.java
index 54b9c3b..5c19e79 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/exception/CashierAlreadyAlloacated.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/teller/exception/CashierAlreadyAllocated.java
@@ -21,9 +21,9 @@
import org.apache.fineract.infrastructure.core.exception.AbstractPlatformDomainRuleException;
@SuppressWarnings("serial")
-public class CashierAlreadyAlloacated extends AbstractPlatformDomainRuleException {
+public class CashierAlreadyAllocated extends AbstractPlatformDomainRuleException {
- public CashierAlreadyAlloacated() {
+ public CashierAlreadyAllocated() {
super("cashier.already.allocated.for.given.data.and.time.exception", "Cashier already allocated for given date and time range.");
}
