You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by pt...@apache.org on 2020/09/06 00:52:38 UTC
[fineract] branch develop updated: Removed string concatenated SQL
& sqlSearch from Staff (Fineract-854)
This is an automated email from the ASF dual-hosted git repository.
ptuomola 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 e74204e Removed string concatenated SQL & sqlSearch from Staff (Fineract-854)
e74204e is described below
commit e74204e7403ebb575a9674af50a0e4150b985e65
Author: Manthan Surkar <ma...@gmail.com>
AuthorDate: Sun Sep 6 01:12:53 2020 +0530
Removed string concatenated SQL & sqlSearch from Staff (Fineract-854)
---
.../BulkImportWorkbookPopulatorServiceImpl.java | 2 +-
.../organisation/staff/api/StaffApiResource.java | 3 +-
.../staff/service/StaffReadPlatformService.java | 2 +-
.../service/StaffReadPlatformServiceImpl.java | 66 +++++++++-------------
4 files changed, 30 insertions(+), 43 deletions(-)
diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java
index 743e612..34b451c 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java
@@ -281,7 +281,7 @@ public class BulkImportWorkbookPopulatorServiceImpl implements BulkImportWorkboo
private List<StaffData> fetchStaff(final Long staffId) {
List<StaffData> staff = null;
if (staffId == null) {
- staff = (List) this.staffReadPlatformService.retrieveAllStaff(null, null, Boolean.FALSE, null);
+ staff = (List) this.staffReadPlatformService.retrieveAllStaff(null, Boolean.FALSE, null);
} else {
staff = new ArrayList<>();
staff.add(this.staffReadPlatformService.retrieveStaff(staffId));
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/api/StaffApiResource.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/api/StaffApiResource.java
index dc1f190..6a0f0ea 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/api/StaffApiResource.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/api/StaffApiResource.java
@@ -118,7 +118,6 @@ public class StaffApiResource {
@ApiResponse(responseCode = "200", description = "OK", content = @Content(array = @ArraySchema(schema = @Schema(implementation = StaffApiResourceSwagger.GetStaffResponse.class)))),
@ApiResponse(responseCode = "200", description = "GET https://DomainName/api/v1/staff?status={ACTIVE|INACTIVE|ALL}", content = @Content(schema = @Schema(implementation = StaffApiResourceSwagger.GetStaffResponse.class))) })
public String retrieveStaff(@Context final UriInfo uriInfo,
- @QueryParam("sqlSearch") @Parameter(description = "sqlSearch") final String sqlSearch,
@QueryParam("officeId") @Parameter(description = "officeId") final Long officeId,
@DefaultValue("false") @QueryParam("staffInOfficeHierarchy") @Parameter(description = "staffInOfficeHierarchy") final boolean staffInOfficeHierarchy,
@DefaultValue("false") @QueryParam("loanOfficersOnly") @Parameter(description = "loanOfficersOnly") final boolean loanOfficersOnly,
@@ -130,7 +129,7 @@ public class StaffApiResource {
if (staffInOfficeHierarchy) {
staff = this.readPlatformService.retrieveAllStaffInOfficeAndItsParentOfficeHierarchy(officeId, loanOfficersOnly);
} else {
- staff = this.readPlatformService.retrieveAllStaff(sqlSearch, officeId, loanOfficersOnly, status);
+ staff = this.readPlatformService.retrieveAllStaff(officeId, loanOfficersOnly, status);
}
final ApiRequestJsonSerializationSettings settings = this.apiRequestParameterHelper.process(uriInfo.getQueryParameters());
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/service/StaffReadPlatformService.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/service/StaffReadPlatformService.java
index 4d838eb..a06411f 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/service/StaffReadPlatformService.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/service/StaffReadPlatformService.java
@@ -34,7 +34,7 @@ public interface StaffReadPlatformService {
*/
Collection<StaffData> retrieveAllStaffInOfficeAndItsParentOfficeHierarchy(Long officeId, boolean loanOfficersOnly);
- Collection<StaffData> retrieveAllStaff(String sqlSearch, Long officeId, boolean loanOfficersOnly, String status);
+ Collection<StaffData> retrieveAllStaff(Long officeId, boolean loanOfficersOnly, String status);
Object[] hasAssociatedItems(Long staffId);
}
diff --git a/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/service/StaffReadPlatformServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/service/StaffReadPlatformServiceImpl.java
index d76165f..1d46ec5 100644
--- a/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/service/StaffReadPlatformServiceImpl.java
+++ b/fineract-provider/src/main/java/org/apache/fineract/organisation/staff/service/StaffReadPlatformServiceImpl.java
@@ -24,12 +24,12 @@ import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
-import org.apache.commons.lang3.StringUtils;
import org.apache.fineract.infrastructure.core.domain.JdbcSupport;
import org.apache.fineract.infrastructure.core.exception.UnrecognizedQueryParamException;
import org.apache.fineract.infrastructure.core.service.RoutingDataSource;
import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
import org.apache.fineract.infrastructure.security.utils.ColumnValidator;
+import org.apache.fineract.infrastructure.security.utils.SQLBuilder;
import org.apache.fineract.organisation.staff.data.StaffData;
import org.apache.fineract.organisation.staff.exception.StaffNotFoundException;
import org.apache.fineract.portfolio.client.domain.ClientStatus;
@@ -159,7 +159,10 @@ public class StaffReadPlatformServiceImpl implements StaffReadPlatformService {
@Override
public Collection<StaffData> retrieveAllLoanOfficersInOfficeById(final Long officeId) {
- return retrieveAllStaff(" office_id = ? and is_loan_officer=1 and o.hierarchy like ?", officeId);
+ SQLBuilder extraCriteria = new SQLBuilder();
+ extraCriteria.addCriteria(" office_id = ", officeId);
+ extraCriteria.addCriteria(" is_loan_officer= = ", 1);
+ return retrieveAllStaff(extraCriteria);
}
@Override
@@ -204,67 +207,52 @@ public class StaffReadPlatformServiceImpl implements StaffReadPlatformService {
}
@Override
- public Collection<StaffData> retrieveAllStaff(final String sqlSearch, final Long officeId, final boolean loanOfficersOnly,
- final String status) {
- final String extraCriteria = getStaffCriteria(sqlSearch, officeId, loanOfficersOnly, status);
- return retrieveAllStaff(extraCriteria, officeId);
+ public Collection<StaffData> retrieveAllStaff(final Long officeId, final boolean loanOfficersOnly, final String status) {
+ final SQLBuilder extraCriteria = getStaffCriteria(officeId, loanOfficersOnly, status);
+ return retrieveAllStaff(extraCriteria);
}
- private Collection<StaffData> retrieveAllStaff(final String extraCriteria, Long officeId) {
+ private Collection<StaffData> retrieveAllStaff(final SQLBuilder extraCriteria) {
final StaffMapper rm = new StaffMapper();
String sql = "select " + rm.schema();
+
final String hierarchy = this.context.authenticatedUser().getOffice().getHierarchy() + "%";
- if (StringUtils.isNotBlank(extraCriteria)) {
- sql += " where " + extraCriteria;
- }
- sql = sql + " order by s.lastname";
- if (officeId == null) {
- return this.jdbcTemplate.query(sql, rm, new Object[] { hierarchy });
- }
- return this.jdbcTemplate.query(sql, rm, new Object[] { officeId, hierarchy });
+ // adding the Authorization criteria so that a user cannot see an
+ // employee who does not belong to his office or a sub office for his
+ // office.
+ extraCriteria.addCriteria(" o.hierarchy like ", hierarchy);
+
+ sql += " " + extraCriteria.getSQLTemplate();
+ sql = sql + " order by s.lastname ";
+
+ return this.jdbcTemplate.query(sql, rm, extraCriteria.getArguments());
}
- private String getStaffCriteria(final String sqlSearch, final Long officeId, final boolean loanOfficersOnly, final String status) {
+ private SQLBuilder getStaffCriteria(final Long officeId, final boolean loanOfficersOnly, final String status) {
- final StringBuilder extraCriteria = new StringBuilder(200);
+ final SQLBuilder extraCriteria = new SQLBuilder();
+
+ extraCriteria.addNonNullCriteria(" s.office_id = ", officeId);
- if (sqlSearch != null) {
- extraCriteria.append(" and (").append(sqlSearch).append(")");
- final StaffMapper rm = new StaffMapper();
- this.columnValidator.validateSqlInjection(rm.schema(), sqlSearch);
- }
- if (officeId != null) {
- extraCriteria.append(" and s.office_id = ? ");
- }
if (loanOfficersOnly) {
- extraCriteria.append(" and s.is_loan_officer is true ");
+ extraCriteria.addCriteria(" s.is_loan_officer is ", " true ");
}
// Passing status parameter to get ACTIVE (By Default), INACTIVE or ALL
// (Both active and Inactive) employees
if (status != null) {
if (status.equalsIgnoreCase("active")) {
- extraCriteria.append(" and s.is_active = 1 ");
+ extraCriteria.addCriteria(" s.is_active =", 1);
} else if (status.equalsIgnoreCase("inActive")) {
- extraCriteria.append(" and s.is_active = 0 ");
+ extraCriteria.addCriteria(" s.is_active =", 0);
} else {
if (!status.equalsIgnoreCase("all")) {
throw new UnrecognizedQueryParamException("status", status, new Object[] { "all", "active", "inactive" });
}
}
}
- // adding the Authorization criteria so that a user cannot see an
- // employee who does not belong to his office or a sub office for his
- // office.
-
- extraCriteria.append(" and o.hierarchy like ? ");
-
- if (StringUtils.isNotBlank(extraCriteria.toString())) {
- extraCriteria.delete(0, 4);
- }
- // remove begin four letter including a space from the string.
- return extraCriteria.toString();
+ return extraCriteria;
}
@Override