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 <manthan.sur...@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