This is an automated email from the ASF dual-hosted git repository.
adamsaghy 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 f1e70111b FINERACT-1910 Support Pagination, sorting and filtering for
Datatable
f1e70111b is described below
commit f1e70111b4f66915b067646a112ec1dd172ab7c9
Author: jmarta <[email protected]>
AuthorDate: Mon Aug 28 13:31:29 2023 +0200
FINERACT-1910 Support Pagination, sorting and filtering for Datatable
---
.../core/service/PagedLocalRequest.java | 26 +-
.../core/service/database/JdbcJavaType.java | 4 +-
.../core/service/database/SqlOperator.java | 67 ++--
.../dataqueries/data/DatatableSearchRequest.java | 24 +-
.../dataqueries/api/DatatablesApiResource.java | 16 +
.../dataqueries/data/DataTableValidator.java | 65 +++-
.../dataqueries/data/EntityTables.java | 13 +-
.../service/ReadWriteNonCoreDataService.java | 11 +
.../service/ReadWriteNonCoreDataServiceImpl.java | 133 ++++++-
.../api/SavingsAccountTransactionsApiResource.java | 18 +
.../SavingsAccountTransactionSearchService.java | 5 +
...avingsAccountTransactionsSearchServiceImpl.java | 134 ++++++-
.../portfolio/search/data/AdvancedQueryData.java | 53 +++
.../search/data/AdvancedQueryRequest.java | 46 +++
.../data/TableQueryData.java} | 28 +-
.../portfolio/search/service/SearchUtil.java | 126 ++++---
.../common/savings/SavingsAccountHelper.java | 7 +
.../common/system/DatatableHelper.java | 7 +
.../datatable/DatatableAdvancedQueryTest.java | 387 +++++++++++++++++++++
.../datatable/DatatableIntegrationTest.java | 141 ++++----
20 files changed, 1115 insertions(+), 196 deletions(-)
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/PagedLocalRequest.java
similarity index 58%
copy from
fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
copy to
fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/PagedLocalRequest.java
index 3bf5765cf..b2c645442 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
+++
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/PagedLocalRequest.java
@@ -16,14 +16,26 @@
* specific language governing permissions and limitations
* under the License.
*/
-package org.apache.fineract.portfolio.savings.service.search;
+package org.apache.fineract.infrastructure.core.service;
-import jakarta.validation.constraints.NotNull;
-import
org.apache.fineract.portfolio.savings.data.SavingsAccountTransactionData;
-import org.apache.fineract.portfolio.search.data.TransactionSearchRequest;
-import org.springframework.data.domain.Page;
+import java.util.Locale;
+import lombok.Getter;
+import lombok.RequiredArgsConstructor;
+import lombok.Setter;
+import org.apache.fineract.infrastructure.core.serialization.JsonParserHelper;
-public interface SavingsAccountTransactionSearchService {
+@Getter
+@Setter
+@RequiredArgsConstructor
+public class PagedLocalRequest<T> extends PagedRequest<T> {
- Page<SavingsAccountTransactionData> searchTransactions(@NotNull Long
savingsId, @NotNull TransactionSearchRequest searchParameters);
+ private String dateFormat;
+
+ private String dateTimeFormat;
+
+ private String locale;
+
+ public Locale getLocaleObject() {
+ return locale == null ? null :
JsonParserHelper.localeFromString(locale);
+ }
}
diff --git
a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/JdbcJavaType.java
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/JdbcJavaType.java
index ebfbade9d..a03834de0 100644
---
a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/JdbcJavaType.java
+++
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/JdbcJavaType.java
@@ -30,14 +30,14 @@ public enum JdbcJavaType {
@Override
public Object toJdbcValueImpl(@NotNull DatabaseType dialect, Object
value) {
- return Boolean.TRUE.equals(value) ? 1 : 0;
+ return value == null ? null : (Boolean.TRUE.equals(value) ? 1 : 0);
}
},
BOOLEAN(JavaType.BOOLEAN, new DialectType(JDBCType.BIT), new
DialectType(JDBCType.BOOLEAN, null, "BOOL")) { //
@Override
public Object toJdbcValueImpl(@NotNull DatabaseType dialect, Object
value) {
- return dialect.isMySql() ? (Boolean.TRUE.equals(value) ? 1 : 0) :
super.toJdbcValueImpl(dialect, value);
+ return (value != null && dialect.isMySql()) ?
(Boolean.TRUE.equals(value) ? 1 : 0) : super.toJdbcValueImpl(dialect, value);
}
},
SMALLINT(JavaType.SHORT, new DialectType(JDBCType.SMALLINT, true), new
DialectType(JDBCType.SMALLINT, null, "INT2")), //
diff --git
a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/SqlOperator.java
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/SqlOperator.java
index 9c4d65d20..3a2e101c8 100644
---
a/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/SqlOperator.java
+++
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/SqlOperator.java
@@ -47,7 +47,7 @@ public enum SqlOperator {
}
@Override
- public String formatPlaceholderImpl(String definition, String
placeholder) {
+ public String formatPlaceholderImpl(String definition, int paramCount,
String placeholder) {
return format("%s %s CONCAT('%%', %s, '%%')", definition,
getSymbol(), placeholder);
}
},
@@ -60,7 +60,7 @@ public enum SqlOperator {
}
@Override
- public String formatPlaceholderImpl(String definition, String
placeholder) {
+ public String formatPlaceholderImpl(String definition, int paramCount,
String placeholder) {
return format("%s %s CONCAT('%%', %s, '%%')", definition,
getSymbol(), placeholder);
}
},
@@ -74,7 +74,7 @@ public enum SqlOperator {
}
@Override
- public String formatPlaceholderImpl(String definition, String
placeholder) {
+ public String formatPlaceholderImpl(String definition, int paramCount,
String placeholder) {
return format("%s %s %s AND %s", definition, getSymbol(),
placeholder, placeholder);
}
},
@@ -88,7 +88,7 @@ public enum SqlOperator {
}
@Override
- public String formatPlaceholderImpl(String definition, String
placeholder) {
+ public String formatPlaceholderImpl(String definition, int paramCount,
String placeholder) {
return format("%s %s %s AND %s", definition, getSymbol(),
placeholder, placeholder);
}
},
@@ -102,8 +102,13 @@ public enum SqlOperator {
}
@Override
- public boolean isPlaceholderSupported() {
- return false;
+ protected String formatPlaceholderImpl(String definition, int
paramCount, String placeholder) {
+ return format("%s %s (%s)", definition, getSymbol(), placeholder +
(", " + placeholder).repeat(paramCount - 1));
+ }
+
+ @Override
+ protected String formatNamedParamImpl(String definition, int
paramCount, String namedParam) {
+ return format("%s %s (%s)", definition, getSymbol(), namedParam);
}
},
NIN("NOT IN", -1) { //
@@ -116,8 +121,13 @@ public enum SqlOperator {
}
@Override
- public boolean isPlaceholderSupported() {
- return false;
+ protected String formatPlaceholderImpl(String definition, int
paramCount, String placeholder) {
+ return format("%s %s (%s)", definition, getSymbol(), placeholder +
(", " + placeholder).repeat(paramCount - 1));
+ }
+
+ @Override
+ protected String formatNamedParamImpl(String definition, int
paramCount, String namedParam) {
+ return format("%s %s (%s)", definition, getSymbol(), namedParam);
}
},
NULL("IS NULL", 0), //
@@ -160,38 +170,45 @@ public enum SqlOperator {
: format("%s %s %s", definition, symbol,
sqlGenerator.formatValue(columnType, values[0]));
}
- public boolean isPlaceholderSupported() {
- return true;
+ public String formatNamedParam(@NotNull DatabaseSpecificSQLGenerator
sqlGenerator, String definition, int paramCount, String alias) {
+ validateParamCount(paramCount);
+ if (paramCount > 1) {
+ throw new
PlatformServiceUnavailableException("error.msg.database.operator.named.invalid",
+ "Named parameter is not allowed on " + this);
+ }
+ return
formatNamedParamImpl(sqlGenerator.alias(sqlGenerator.escape(definition),
alias), paramCount, ":" + definition);
+ }
+
+ protected String formatNamedParamImpl(String definition, int paramCount,
String namedParam) {
+ return formatPlaceholderImpl(definition, paramCount, namedParam);
}
- public String formatPlaceholder(@NotNull DatabaseSpecificSQLGenerator
sqlGenerator, String definition, String alias) {
- return formatPlaceholder(sqlGenerator, definition, alias, "?");
+ public String formatPlaceholder(@NotNull DatabaseSpecificSQLGenerator
sqlGenerator, String definition, int paramCount, String alias) {
+ return formatPlaceholder(sqlGenerator, definition, paramCount, alias,
"?");
}
- public String formatPlaceholder(@NotNull DatabaseSpecificSQLGenerator
sqlGenerator, String definition, String alias,
+ public String formatPlaceholder(@NotNull DatabaseSpecificSQLGenerator
sqlGenerator, String definition, int paramCount, String alias,
String placeholder) {
- return
formatPlaceholderImpl(sqlGenerator.alias(sqlGenerator.escape(definition),
alias), placeholder);
+ validateParamCount(paramCount);
+ return
formatPlaceholderImpl(sqlGenerator.alias(sqlGenerator.escape(definition),
alias), paramCount, placeholder);
}
- protected String formatPlaceholderImpl(String definition, String
placeholder) {
- if (!isPlaceholderSupported()) {
- throw new UnsupportedOperationException("Placeholder is not
supported for this operator");
- }
+ protected String formatPlaceholderImpl(String definition, int paramCount,
String placeholder) {
return paramCount == 0 ? format("%s %s", definition, symbol) :
format("%s %s %s", definition, symbol, placeholder);
}
public void validateValues(String... values) {
- if (values == null ? paramCount != 0 : (paramCount < 0 ? values.length
< -paramCount : values.length != paramCount)) {
- throw new
PlatformServiceUnavailableException("error.msg.database.operator.invalid",
- "Number of parameters " + Arrays.toString(values) + " must
be " + Math.abs(paramCount) + " on " + this);
- }
+ validateParamCount(values == null ? 0 : values.length);
}
public void validateValues(List<?> values) {
- int size = values == null ? 0 : values.size();
- if (paramCount < 0 ? size < -paramCount : size != paramCount) {
+ validateParamCount(values == null ? 0 : values.size());
+ }
+
+ public void validateParamCount(int paramCount) {
+ if (this.paramCount < 0 ? paramCount < -this.paramCount : paramCount
!= this.paramCount) {
throw new
PlatformServiceUnavailableException("error.msg.database.operator.invalid",
- "Number of parameters " + size + " must be " +
Math.abs(paramCount) + " on " + this);
+ "Number of parameters " + paramCount + " must be " +
Math.abs(this.paramCount) + " on " + this);
}
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/DatatableSearchRequest.java
similarity index 61%
copy from
fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
copy to
fineract-core/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/DatatableSearchRequest.java
index 3bf5765cf..87b4a9121 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
+++
b/fineract-core/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/DatatableSearchRequest.java
@@ -16,14 +16,24 @@
* specific language governing permissions and limitations
* under the License.
*/
-package org.apache.fineract.portfolio.savings.service.search;
+package org.apache.fineract.infrastructure.dataqueries.data;
-import jakarta.validation.constraints.NotNull;
-import
org.apache.fineract.portfolio.savings.data.SavingsAccountTransactionData;
-import org.apache.fineract.portfolio.search.data.TransactionSearchRequest;
-import org.springframework.data.domain.Page;
+import java.io.Serializable;
+import java.util.List;
+import lombok.Data;
+import lombok.NoArgsConstructor;
-public interface SavingsAccountTransactionSearchService {
+/**
+ * Immutable data object representing datatable data.
+ */
+@Data
+@NoArgsConstructor
+public final class DatatableSearchRequest implements Serializable {
+
+ private List<ColumnFilter> columnFilters;
+
+ private List<String> resultColumns;
+
+ private String datatable;
- Page<SavingsAccountTransactionData> searchTransactions(@NotNull Long
savingsId, @NotNull TransactionSearchRequest searchParameters);
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResource.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResource.java
index 843753010..f7c0919ff 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResource.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/api/DatatablesApiResource.java
@@ -52,11 +52,14 @@ import
org.apache.fineract.infrastructure.core.api.ApiParameterHelper;
import org.apache.fineract.infrastructure.core.data.CommandProcessingResult;
import
org.apache.fineract.infrastructure.core.data.CommandProcessingResultBuilder;
import
org.apache.fineract.infrastructure.core.serialization.ToApiJsonSerializer;
+import org.apache.fineract.infrastructure.core.service.PagedLocalRequest;
import org.apache.fineract.infrastructure.dataqueries.data.DatatableData;
import
org.apache.fineract.infrastructure.dataqueries.data.GenericResultsetData;
import
org.apache.fineract.infrastructure.dataqueries.service.GenericDataService;
import
org.apache.fineract.infrastructure.dataqueries.service.ReadWriteNonCoreDataService;
import
org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryData;
+import org.springframework.data.domain.Page;
import org.springframework.stereotype.Component;
@Path("/v1/datatables")
@@ -225,6 +228,19 @@ public class DatatablesApiResource {
return
this.toApiJsonSerializer.serializePretty(ApiParameterHelper.prettyPrint(uriInfo.getQueryParameters()),
result);
}
+ @POST
+ @Path("{datatable}/query")
+ @Consumes({ MediaType.APPLICATION_JSON })
+ @Produces({ MediaType.APPLICATION_JSON })
+ @Operation(summary = "Query Data Table values", description = "Query
values from a registered data table.")
+ @ApiResponses({
+ @ApiResponse(responseCode = "200", description = "OK", content =
@Content(schema = @Schema(implementation = List.class))) })
+ public String advancedQuery(@PathParam("datatable") @Parameter(description
= "datatable") final String datatable,
+ PagedLocalRequest<AdvancedQueryData> queryRequest, @Context final
UriInfo uriInfo) {
+ final Page<JsonObject> result =
this.readWriteNonCoreDataService.queryDataTableAdvanced(datatable,
queryRequest);
+ return
this.toApiJsonSerializer.serializePretty(ApiParameterHelper.prettyPrint(uriInfo.getQueryParameters()),
result);
+ }
+
@GET
@Path("{datatable}/{apptableId}")
@Consumes({ MediaType.APPLICATION_JSON })
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/DataTableValidator.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/DataTableValidator.java
index c6e9b8213..0a5a552cc 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/DataTableValidator.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/DataTableValidator.java
@@ -18,8 +18,16 @@
*/
package org.apache.fineract.infrastructure.dataqueries.data;
+import static
org.apache.fineract.portfolio.search.SearchConstants.API_PARAM_COLUMN;
+import static
org.apache.fineract.portfolio.search.SearchConstants.API_PARAM_FILTERS;
+import static
org.apache.fineract.portfolio.search.SearchConstants.API_PARAM_OPERATOR;
+import static
org.apache.fineract.portfolio.search.SearchConstants.API_PARAM_QUERY;
+import static
org.apache.fineract.portfolio.search.SearchConstants.API_PARAM_RESULTCOLUMNS;
+import static
org.apache.fineract.portfolio.search.SearchConstants.API_PARAM_TABLE;
+
import com.google.gson.JsonElement;
import com.google.gson.reflect.TypeToken;
+import jakarta.validation.constraints.NotNull;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Arrays;
@@ -29,9 +37,13 @@ import java.util.Map;
import java.util.Set;
import org.apache.fineract.infrastructure.core.data.ApiParameterError;
import org.apache.fineract.infrastructure.core.data.DataValidatorBuilder;
-import
org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidationException;
import org.apache.fineract.infrastructure.core.serialization.FromJsonHelper;
import org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryData;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryRequest;
+import org.apache.fineract.portfolio.search.data.ColumnFilterData;
+import org.apache.fineract.portfolio.search.data.FilterData;
+import org.apache.fineract.portfolio.search.data.TableQueryData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
@@ -64,9 +76,56 @@ public class DataTableValidator {
baseDataValidator.reset().parameter(DataTableApiConstant.categoryParamName).value(category).isOneOfTheseValues(objectArray);
}
- if (!dataValidationErrors.isEmpty()) {
- throw new PlatformApiDataValidationException(dataValidationErrors);
+ baseDataValidator.throwValidationErrors();
+ }
+
+ public void validateTableSearch(@NotNull AdvancedQueryRequest
queryRequest) {
+ final List<ApiParameterError> errors = new ArrayList<>();
+ final DataValidatorBuilder validator = new
DataValidatorBuilder(errors).resource(DataTableApiConstant.DATATABLE_RESOURCE_NAME);
+ AdvancedQueryData baseQuery = queryRequest.getBaseQuery();
+ if (baseQuery != null) {
+ validateQueryData(baseQuery, validator);
}
+ List<TableQueryData> datatableQueries =
queryRequest.getDatatableQueries();
+ if (datatableQueries != null) {
+ for (TableQueryData datatableQuery : datatableQueries) {
+
validator.reset().parameter(API_PARAM_TABLE).value(datatableQuery.getTable()).notBlank();
+ AdvancedQueryData queryData = datatableQuery.getQuery();
+
validator.reset().parameter(API_PARAM_QUERY).value(queryData).notBlank();
+ if (queryData != null) {
+ validateQueryData(queryData, validator);
+ }
+ }
+ }
+ validator.throwValidationErrors();
+ }
+ public void validateTableSearch(@NotNull AdvancedQueryData queryData) {
+ final DataValidatorBuilder validator = new DataValidatorBuilder(new
ArrayList<>())
+ .resource(DataTableApiConstant.DATATABLE_RESOURCE_NAME);
+ validateQueryData(queryData, validator);
+ validator.throwValidationErrors();
+ }
+
+ private void validateQueryData(@NotNull AdvancedQueryData queryData,
@NotNull DataValidatorBuilder validator) {
+ List<ColumnFilterData> columnFilters = queryData.getColumnFilters();
+ if (columnFilters != null) {
+ for (ColumnFilterData columnFilter : columnFilters) {
+
validator.reset().parameter(API_PARAM_COLUMN).value(columnFilter.getColumn()).notNull();
+ List<FilterData> filters = columnFilter.getFilters();
+ validator.reset().parameter(API_PARAM_FILTERS).value(filters
== null ? null : filters.toArray()).notNull().arrayNotEmpty();
+ if (filters != null) {
+ for (FilterData filter : filters) {
+
validator.reset().parameter(API_PARAM_OPERATOR).value(filter.getOperator()).notNull();
+ }
+ }
+ }
+ List<String> resultColumns = queryData.getResultColumns();
+ if (resultColumns != null) {
+ for (String resultColumn : resultColumns) {
+
validator.reset().parameter(API_PARAM_RESULTCOLUMNS).value(resultColumn).notBlank();
+ }
+ }
+ }
}
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/EntityTables.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/EntityTables.java
index fde2316ab..2db319073 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/EntityTables.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/data/EntityTables.java
@@ -56,15 +56,20 @@ public enum EntityTables {
private static final Map<String, EntityTables> BY_ENTITY_NAME =
Arrays.stream(VALUES)
.collect(Collectors.toMap(EntityTables::getName, e -> e));
+ @NotNull
private final String name;
+ @NotNull
private final String apptableName;
+ @NotNull
private final String foreignKeyColumnNameOnDatatable;
+ @NotNull
private final String refColumn; // referenced column name on apptable
private final ImmutableList<StatusEnum> checkStatuses;
- EntityTables(String name, String apptableName, String
foreignKeyColumnNameOnDatatable, String refColumn, StatusEnum... statuses) {
+ EntityTables(@NotNull String name, @NotNull String apptableName, @NotNull
String foreignKeyColumnNameOnDatatable,
+ @NotNull String refColumn, StatusEnum... statuses) {
this.name = name;
this.apptableName = apptableName;
this.foreignKeyColumnNameOnDatatable = foreignKeyColumnNameOnDatatable;
@@ -72,22 +77,26 @@ public enum EntityTables {
this.checkStatuses = statuses == null ? ImmutableList.of() :
ImmutableList.copyOf(statuses);
}
- EntityTables(String name, String foreignKeyColumnNameOnDatatable, String
refColumn, StatusEnum... statuses) {
+ EntityTables(@NotNull String name, @NotNull String
foreignKeyColumnNameOnDatatable, @NotNull String refColumn, StatusEnum...
statuses) {
this(name, name, foreignKeyColumnNameOnDatatable, refColumn, statuses);
}
+ @NotNull
public String getName() {
return name;
}
+ @NotNull
public String getApptableName() {
return apptableName;
}
+ @NotNull
public String getForeignKeyColumnNameOnDatatable() {
return this.foreignKeyColumnNameOnDatatable;
}
+ @NotNull
public String getRefColumn() {
return refColumn;
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataService.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataService.java
index e10aa9a06..cef0a1f4f 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataService.java
@@ -21,10 +21,15 @@ package
org.apache.fineract.infrastructure.dataqueries.service;
import com.google.gson.JsonObject;
import jakarta.validation.constraints.NotNull;
import java.util.List;
+import java.util.Locale;
import org.apache.fineract.infrastructure.core.api.JsonCommand;
import org.apache.fineract.infrastructure.core.data.CommandProcessingResult;
+import org.apache.fineract.infrastructure.core.service.PagedLocalRequest;
import org.apache.fineract.infrastructure.dataqueries.data.DatatableData;
+import org.apache.fineract.infrastructure.dataqueries.data.EntityTables;
import
org.apache.fineract.infrastructure.dataqueries.data.GenericResultsetData;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryData;
+import org.springframework.data.domain.Page;
import org.springframework.security.access.prepost.PreAuthorize;
public interface ReadWriteNonCoreDataService {
@@ -75,4 +80,10 @@ public interface ReadWriteNonCoreDataService {
List<JsonObject> queryDataTable(@NotNull String datatable, @NotNull String
columnName, String columnValue,
@NotNull String resultColumns);
+
+ Page<JsonObject> queryDataTableAdvanced(@NotNull String datatable,
@NotNull PagedLocalRequest<AdvancedQueryData> pagedRequest);
+
+ boolean buildDataQueryEmbedded(@NotNull EntityTables entityTable, @NotNull
String datatable, @NotNull AdvancedQueryData request,
+ @NotNull List<String> selectColumns, @NotNull StringBuilder
select, @NotNull StringBuilder from, @NotNull StringBuilder where,
+ @NotNull List<Object> params, String mainAlias, String alias,
String dateFormat, String dateTimeFormat, Locale locale);
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
index bf2a82db6..fb8184c32 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/dataqueries/service/ReadWriteNonCoreDataServiceImpl.java
@@ -83,6 +83,7 @@ import
org.apache.fineract.infrastructure.core.exception.PlatformServiceUnavaila
import
org.apache.fineract.infrastructure.core.serialization.DatatableCommandFromApiJsonDeserializer;
import org.apache.fineract.infrastructure.core.serialization.FromJsonHelper;
import org.apache.fineract.infrastructure.core.service.MathUtil;
+import org.apache.fineract.infrastructure.core.service.PagedLocalRequest;
import
org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import org.apache.fineract.infrastructure.core.service.database.DatabaseType;
import
org.apache.fineract.infrastructure.core.service.database.DatabaseTypeResolver;
@@ -101,11 +102,17 @@ import
org.apache.fineract.infrastructure.security.service.PlatformSecurityConte
import
org.apache.fineract.infrastructure.security.service.SqlInjectionPreventerService;
import org.apache.fineract.infrastructure.security.utils.ColumnValidator;
import org.apache.fineract.infrastructure.security.utils.SQLInjectionValidator;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryData;
+import org.apache.fineract.portfolio.search.data.ColumnFilterData;
import org.apache.fineract.portfolio.search.service.SearchUtil;
import org.apache.fineract.useradministration.domain.AppUser;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.EmptyResultDataAccessException;
+import org.springframework.data.domain.Page;
+import org.springframework.data.domain.PageRequest;
+import org.springframework.data.domain.Sort;
+import org.springframework.data.support.PageableExecutionUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
@@ -199,20 +206,16 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
public List<JsonObject> queryDataTable(@NotNull String datatable, @NotNull
String columnName, String columnValueString,
@NotNull String resultColumnsString) {
datatable = validateDatatableRegistered(datatable);
- Map<String, ResultsetColumnHeaderData> columnHeaders = SearchUtil
+ Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil
.mapHeadersToName(genericDataService.fillResultsetColumnHeaders(datatable));
- List<ApiParameterError> errors = new ArrayList<>();
- columnName = SearchUtil.validateToJdbcColumn(columnName,
columnHeaders, errors, false);
List<String> resultColumns = asList(resultColumnsString.split(","));
- List<String> selectColumns =
SearchUtil.validateToJdbcColumns(resultColumns, columnHeaders, errors, false);
- if (!errors.isEmpty()) {
- throw new PlatformApiDataValidationException(errors);
- }
+ List<String> selectColumns =
SearchUtil.validateToJdbcColumnNames(resultColumns, headersByName, false);
+ ResultsetColumnHeaderData column =
SearchUtil.validateToJdbcColumn(columnName, headersByName, false);
- Object columnValue =
SearchUtil.parseAndValidateJdbcColumnValue(columnName, columnValueString,
columnHeaders, false, sqlGenerator);
+ Object columnValue = SearchUtil.parseJdbcColumnValue(column,
columnValueString, null, null, null, false, sqlGenerator);
String sql = sqlGenerator.buildSelect(selectColumns, null, false) + "
" + sqlGenerator.buildFrom(datatable, null, false) + " WHERE "
- + EQ.formatPlaceholder(sqlGenerator, columnName, null);
+ + EQ.formatPlaceholder(sqlGenerator, column.getColumnName(),
1, null);
SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sql, columnValue);
List<JsonObject> results = new ArrayList<>();
@@ -222,6 +225,110 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
return results;
}
+ @Override
+ public Page<JsonObject> queryDataTableAdvanced(@NotNull String datatable,
@NotNull PagedLocalRequest<AdvancedQueryData> pagedRequest) {
+ datatable = validateDatatableRegistered(datatable);
+
context.authenticatedUser().validateHasDatatableReadPermission(datatable);
+
+ AdvancedQueryData request = pagedRequest.getRequest().orElseThrow();
+ dataTableValidator.validateTableSearch(request);
+
+ Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil
+
.mapHeadersToName(genericDataService.fillResultsetColumnHeaders(datatable));
+ String pkColumn = SearchUtil.getFiltered(headersByName.values(),
ResultsetColumnHeaderData::getIsColumnPrimaryKey).getColumnName();
+
+ List<ColumnFilterData> columnFilters = request.getNonNullFilters();
+ columnFilters.forEach(e ->
e.setColumn(SearchUtil.validateToJdbcColumnName(e.getColumn(), headersByName,
false)));
+
+ List<String> resultColumns = request.getNonNullResultColumns();
+ List<String> selectColumns;
+ if (resultColumns.isEmpty()) {
+ resultColumns.add(pkColumn);
+ selectColumns = new ArrayList<>();
+ selectColumns.add(pkColumn);
+ } else {
+ selectColumns =
SearchUtil.validateToJdbcColumnNames(resultColumns, headersByName, false);
+ }
+ PageRequest pageable = pagedRequest.toPageable();
+ PageRequest sortPageable;
+ if (pageable.getSort().isSorted()) {
+ List<Sort.Order> orders = pageable.getSort().toList();
+ sortPageable = pageable.withSort(Sort.by(orders.stream()
+ .map(e ->
e.withProperty(SearchUtil.validateToJdbcColumnName(e.getProperty(),
headersByName, false))).toList()));
+ } else {
+ pageable = pageable.withSort(Sort.Direction.DESC, pkColumn);
+ sortPageable = pageable;
+ }
+
+ String dateFormat = pagedRequest.getDateFormat();
+ String dateTimeFormat = pagedRequest.getDateTimeFormat();
+ Locale locale = pagedRequest.getLocaleObject();
+
+ String select = sqlGenerator.buildSelect(selectColumns, null, false);
+ String from = " " + sqlGenerator.buildFrom(datatable, null, false);
+ StringBuilder where = new StringBuilder();
+ ArrayList<Object> params = new ArrayList<>();
+ SearchUtil.buildQueryCondition(columnFilters, where, params, null,
headersByName, dateFormat, dateTimeFormat, locale, false,
+ sqlGenerator);
+
+ List<JsonObject> results = new ArrayList<>();
+ Object[] args = params.toArray();
+
+ // Execute the count Query
+ String countQuery = "SELECT COUNT(*)" + from + where;
+ Integer totalElements = jdbcTemplate.queryForObject(countQuery,
Integer.class, args);
+ if (totalElements == null || totalElements == 0) {
+ return PageableExecutionUtils.getPage(results, pageable, () -> 0);
+ }
+
+ StringBuilder query = new
StringBuilder().append(select).append(from).append(where);
+ query.append("
").append(sqlGenerator.buildOrderBy(sortPageable.getSort().toList(), null,
false));
+ if (pageable.isPaged()) {
+ query.append("
").append(sqlGenerator.limit(pageable.getPageSize(), (int)
pageable.getOffset()));
+ }
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet(query.toString(), args);
+
+ while (rowSet.next()) {
+ SearchUtil.extractJsonResult(rowSet, selectColumns, resultColumns,
results);
+ }
+ return PageableExecutionUtils.getPage(results, pageable, () ->
totalElements);
+ }
+
+ @Override
+ public boolean buildDataQueryEmbedded(@NotNull EntityTables entityTable,
@NotNull String datatable, @NotNull AdvancedQueryData request,
+ @NotNull List<String> selectColumns, @NotNull StringBuilder
select, @NotNull StringBuilder from, @NotNull StringBuilder where,
+ @NotNull List<Object> params, String mainAlias, String alias,
String dateFormat, String dateTimeFormat, Locale locale) {
+ List<String> resultColumns = request.getResultColumns();
+ List<ColumnFilterData> columnFilters = request.getColumnFilters();
+ if ((resultColumns == null || resultColumns.isEmpty()) &&
(columnFilters == null || columnFilters.isEmpty())) {
+ return false;
+ }
+
+ datatable = validateDatatableRegistered(datatable);
+
context.authenticatedUser().validateHasDatatableReadPermission(datatable);
+
+ Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil
+
.mapHeadersToName(genericDataService.fillResultsetColumnHeaders(datatable));
+
+ List<String> thisSelectColumns =
SearchUtil.validateToJdbcColumnNames(resultColumns, headersByName, true);
+ if (columnFilters != null) {
+ columnFilters.forEach(e ->
e.setColumn(SearchUtil.validateToJdbcColumnName(e.getColumn(), headersByName,
false)));
+ }
+
+ select.append(sqlGenerator.buildSelect(thisSelectColumns, alias,
true));
+ selectColumns.addAll(thisSelectColumns);
+
+ String joinType = "LEFT";
+ if (SearchUtil.buildQueryCondition(columnFilters, where, params,
alias, headersByName, dateFormat, dateTimeFormat, locale, true,
+ sqlGenerator)) {
+ joinType = null; // INNER
+ }
+ from.append(sqlGenerator.buildJoin(datatable, alias,
entityTable.getForeignKeyColumnNameOnDatatable(), mainAlias,
+ entityTable.getRefColumn(), joinType));
+ return true;
+ }
+
private void logAsErrorUnexpectedDataIntegrityException(final Exception
dve) {
log.error("Error occurred.", dve);
}
@@ -1830,7 +1937,7 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
columnHeaderUnderscored =
this.genericDataService.replace(columnHeader.getColumnName(), space,
underscore);
if
(queryParamColumnUnderscored.equalsIgnoreCase(columnHeaderUnderscored)) {
pValue = queryParams.get(key);
- validatedValue =
SearchUtil.parseAndValidateColumnValue(columnHeader, pValue, dateFormat,
+ validatedValue =
SearchUtil.parseColumnValue(columnHeader, pValue, dateFormat, dateFormat,
clientApplicationLocale, true,
sqlGenerator);
affectedColumns.put(columnHeader.getColumnName(),
validatedValue);
notFound = false;
@@ -1890,7 +1997,7 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
+ " JOIN m_entity_datatable_check edc ON
edc.x_registered_table_name = xrt.registered_table_name"
+ " WHERE edc.x_registered_table_name = '" + datatableName +
"'";
final Long count = this.jdbcTemplate.queryForObject(sql, Long.class);
- return count > 0;
+ return count != null && count > 0;
}
// --- DbUtils ---
@@ -1940,8 +2047,8 @@ public class ReadWriteNonCoreDataServiceImpl implements
ReadWriteNonCoreDataServ
private boolean isRegisteredDataTable(final String datatable) {
final String sql = "SELECT COUNT(application_table_name) FROM " +
TABLE_REGISTERED_TABLE + " WHERE registered_table_name = ?";
- final int count = jdbcTemplate.queryForObject(sql, Integer.class,
datatable);
- return count > 0;
+ final Integer count = jdbcTemplate.queryForObject(sql, Integer.class,
datatable);
+ return count != null && count > 0;
}
private void validateDataTableExists(final String datatableName) {
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/SavingsAccountTransactionsApiResource.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/SavingsAccountTransactionsApiResource.java
index a1ed7911d..42e737eba 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/SavingsAccountTransactionsApiResource.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/api/SavingsAccountTransactionsApiResource.java
@@ -18,6 +18,7 @@
*/
package org.apache.fineract.portfolio.savings.api;
+import com.google.gson.JsonObject;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.media.ArraySchema;
@@ -39,12 +40,14 @@ import jakarta.ws.rs.core.MediaType;
import jakarta.ws.rs.core.UriInfo;
import java.math.BigDecimal;
import java.util.Collection;
+import java.util.List;
import java.util.Locale;
import lombok.RequiredArgsConstructor;
import org.apache.commons.lang3.StringUtils;
import org.apache.fineract.commands.domain.CommandWrapper;
import org.apache.fineract.commands.service.CommandWrapperBuilder;
import
org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService;
+import org.apache.fineract.infrastructure.core.api.ApiParameterHelper;
import org.apache.fineract.infrastructure.core.api.ApiRequestParameterHelper;
import org.apache.fineract.infrastructure.core.data.CommandProcessingResult;
import
org.apache.fineract.infrastructure.core.exception.PlatformDataIntegrityException;
@@ -52,6 +55,7 @@ import
org.apache.fineract.infrastructure.core.exception.UnrecognizedQueryParamE
import
org.apache.fineract.infrastructure.core.serialization.ApiRequestJsonSerializationSettings;
import
org.apache.fineract.infrastructure.core.serialization.DefaultToApiJsonSerializer;
import org.apache.fineract.infrastructure.core.serialization.JsonParserHelper;
+import org.apache.fineract.infrastructure.core.service.PagedLocalRequest;
import
org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
import org.apache.fineract.portfolio.paymenttype.data.PaymentTypeData;
import
org.apache.fineract.portfolio.paymenttype.service.PaymentTypeReadPlatformService;
@@ -60,6 +64,7 @@ import
org.apache.fineract.portfolio.savings.SavingsApiConstants;
import
org.apache.fineract.portfolio.savings.data.SavingsAccountTransactionData;
import
org.apache.fineract.portfolio.savings.service.SavingsAccountReadPlatformService;
import
org.apache.fineract.portfolio.savings.service.search.SavingsAccountTransactionSearchService;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryRequest;
import org.apache.fineract.portfolio.search.data.TransactionSearchRequest;
import org.springframework.dao.CannotAcquireLockException;
import org.springframework.data.domain.Page;
@@ -159,6 +164,19 @@ public class SavingsAccountTransactionsApiResource {
return toApiJsonSerializer.serialize(transactionsData);
}
+ @POST
+ @Path("query")
+ @Consumes({ MediaType.APPLICATION_JSON })
+ @Produces({ MediaType.APPLICATION_JSON })
+ @Operation(summary = "Advanced search Savings Account Transactions")
+ @ApiResponses({
+ @ApiResponse(responseCode = "200", description = "OK", content =
@Content(schema = @Schema(implementation = List.class))) })
+ public String advancedQuery(@PathParam("savingsId") @Parameter(description
= "savingsId") final Long savingsId,
+ PagedLocalRequest<AdvancedQueryRequest> queryRequest, @Context
final UriInfo uriInfo) {
+ final Page<JsonObject> result =
transactionsSearchService.queryAdvanced(savingsId, queryRequest);
+ return
this.toApiJsonSerializer.serializePretty(ApiParameterHelper.prettyPrint(uriInfo.getQueryParameters()),
result);
+ }
+
@POST
@Consumes({ MediaType.APPLICATION_JSON })
@Produces({ MediaType.APPLICATION_JSON })
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
index 3bf5765cf..73e35bd55 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
@@ -18,12 +18,17 @@
*/
package org.apache.fineract.portfolio.savings.service.search;
+import com.google.gson.JsonObject;
import jakarta.validation.constraints.NotNull;
+import org.apache.fineract.infrastructure.core.service.PagedLocalRequest;
import
org.apache.fineract.portfolio.savings.data.SavingsAccountTransactionData;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryRequest;
import org.apache.fineract.portfolio.search.data.TransactionSearchRequest;
import org.springframework.data.domain.Page;
public interface SavingsAccountTransactionSearchService {
Page<SavingsAccountTransactionData> searchTransactions(@NotNull Long
savingsId, @NotNull TransactionSearchRequest searchParameters);
+
+ Page<JsonObject> queryAdvanced(@NotNull Long savingsId, @NotNull
PagedLocalRequest<AdvancedQueryRequest> pagedRequest);
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionsSearchServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionsSearchServiceImpl.java
index ba06548d8..db724134d 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionsSearchServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionsSearchServiceImpl.java
@@ -20,27 +20,33 @@ package
org.apache.fineract.portfolio.savings.service.search;
import static
org.apache.fineract.portfolio.savings.SavingsApiConstants.SAVINGS_ACCOUNT_RESOURCE_NAME;
+import com.google.gson.JsonObject;
import jakarta.validation.constraints.NotNull;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
+import java.util.Locale;
import java.util.Map;
import java.util.function.Predicate;
import lombok.RequiredArgsConstructor;
-import org.apache.fineract.infrastructure.core.data.ApiParameterError;
-import
org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidationException;
import org.apache.fineract.infrastructure.core.service.DateUtils;
import org.apache.fineract.infrastructure.core.service.MathUtil;
+import org.apache.fineract.infrastructure.core.service.PagedLocalRequest;
import
org.apache.fineract.infrastructure.core.service.database.DatabaseSpecificSQLGenerator;
import org.apache.fineract.infrastructure.core.service.database.SqlOperator;
+import org.apache.fineract.infrastructure.dataqueries.data.DataTableValidator;
import org.apache.fineract.infrastructure.dataqueries.data.EntityTables;
import
org.apache.fineract.infrastructure.dataqueries.data.ResultsetColumnHeaderData;
import
org.apache.fineract.infrastructure.dataqueries.service.GenericDataService;
+import
org.apache.fineract.infrastructure.dataqueries.service.ReadWriteNonCoreDataServiceImpl;
import
org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
import org.apache.fineract.portfolio.savings.SavingsAccountTransactionType;
import
org.apache.fineract.portfolio.savings.data.SavingsAccountTransactionData;
import
org.apache.fineract.portfolio.savings.service.SavingsAccountReadPlatformServiceImpl;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryData;
+import org.apache.fineract.portfolio.search.data.AdvancedQueryRequest;
import org.apache.fineract.portfolio.search.data.ColumnFilterData;
+import org.apache.fineract.portfolio.search.data.TableQueryData;
import org.apache.fineract.portfolio.search.data.TransactionSearchRequest;
import org.apache.fineract.portfolio.search.service.SearchUtil;
import org.jetbrains.annotations.Nullable;
@@ -49,6 +55,7 @@ import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.support.PageableExecutionUtils;
import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@@ -60,6 +67,8 @@ public class SavingsAccountTransactionsSearchServiceImpl
implements SavingsAccou
private final PlatformSecurityContext context;
private final GenericDataService genericDataService;
private final DatabaseSpecificSQLGenerator sqlGenerator;
+ private final ReadWriteNonCoreDataServiceImpl datatableService;
+ private final DataTableValidator dataTableValidator;
private final JdbcTemplate jdbcTemplate;
@Override
@@ -68,19 +77,15 @@ public class SavingsAccountTransactionsSearchServiceImpl
implements SavingsAccou
context.authenticatedUser().validateHasReadPermission(SAVINGS_ACCOUNT_RESOURCE_NAME);
String apptable = EntityTables.SAVINGS_TRANSACTION.getApptableName();
- Map<String, ResultsetColumnHeaderData> columnHeaders = SearchUtil
+ Map<String, ResultsetColumnHeaderData> headersByName = SearchUtil
.mapHeadersToName(genericDataService.fillResultsetColumnHeaders(apptable));
PageRequest pageable = searchParameters.getPageable();
PageRequest sortPageable;
if (pageable.getSort().isSorted()) {
- List<ApiParameterError> errors = new ArrayList<>();
List<Sort.Order> orders = pageable.getSort().toList();
sortPageable = pageable.withSort(Sort.by(orders.stream()
- .map(e ->
e.withProperty(SearchUtil.validateToJdbcColumn(e.getProperty(), columnHeaders,
errors, false))).toList()));
- if (!errors.isEmpty()) {
- throw new PlatformApiDataValidationException(errors);
- }
+ .map(e ->
e.withProperty(SearchUtil.validateToJdbcColumnName(e.getProperty(),
headersByName, false))).toList()));
} else {
pageable = pageable.withSort(Sort.Direction.DESC,
"transaction_date", "created_date", "id");
sortPageable = pageable;
@@ -102,9 +107,9 @@ public class SavingsAccountTransactionsSearchServiceImpl
implements SavingsAccou
}
String alias = "tr";
- StringBuilder where = new StringBuilder(" WHERE ");
+ StringBuilder where = new StringBuilder();
ArrayList<Object> params = new ArrayList<>();
- SearchUtil.buildQueryCondition(columnFilters, where, params, alias,
columnHeaders, false, false, sqlGenerator);
+ SearchUtil.buildQueryCondition(columnFilters, where, params, alias,
headersByName, null, null, null, false, sqlGenerator);
SavingsAccountReadPlatformServiceImpl.SavingsAccountTransactionsMapper
tm = new
SavingsAccountReadPlatformServiceImpl.SavingsAccountTransactionsMapper();
Object[] args = params.toArray();
@@ -171,4 +176,113 @@ public class SavingsAccountTransactionsSearchServiceImpl
implements SavingsAccou
}
return false;
}
+
+ @Override
+ public Page<JsonObject> queryAdvanced(@NotNull Long savingsId, @NotNull
PagedLocalRequest<AdvancedQueryRequest> pagedRequest) {
+
context.authenticatedUser().validateHasReadPermission(SAVINGS_ACCOUNT_RESOURCE_NAME);
+ String apptable = EntityTables.SAVINGS_TRANSACTION.getApptableName();
+
+ AdvancedQueryRequest queryRequest =
pagedRequest.getRequest().orElseThrow();
+ dataTableValidator.validateTableSearch(queryRequest);
+
+ List<ResultsetColumnHeaderData> columnHeaders =
genericDataService.fillResultsetColumnHeaders(apptable);
+ Map<String, ResultsetColumnHeaderData> headersByName =
SearchUtil.mapHeadersToName(columnHeaders);
+ String pkColumn = SearchUtil.getFiltered(columnHeaders,
ResultsetColumnHeaderData::getIsColumnPrimaryKey).getColumnName();
+
+ AdvancedQueryData baseQuery = queryRequest.getBaseQuery();
+ List<TableQueryData> datatableQueries =
queryRequest.getDatatableQueries();
+
+ List<ColumnFilterData> columnFilters;
+ List<String> resultColumns;
+ List<String> selectColumns;
+ if (baseQuery == null) {
+ columnFilters = new ArrayList<>();
+ resultColumns = new ArrayList<>();
+ selectColumns = new ArrayList<>();
+ } else {
+ columnFilters = baseQuery.getNonNullFilters();
+ columnFilters.forEach(e ->
e.setColumn(SearchUtil.validateToJdbcColumnName(e.getColumn(), headersByName,
false)));
+ resultColumns = baseQuery.getNonNullResultColumns();
+ selectColumns = new
ArrayList<>(SearchUtil.validateToJdbcColumnNames(resultColumns, headersByName,
true));
+ }
+ columnFilters.add(0, ColumnFilterData.eq("savings_account_id",
savingsId.toString()));
+ if (resultColumns.isEmpty() && !queryRequest.hasResultColumn()) {
+ resultColumns.add(pkColumn);
+ selectColumns.add(pkColumn);
+ }
+ PageRequest pageable = pagedRequest.toPageable();
+ PageRequest sortPageable;
+ if (pageable.getSort().isSorted()) {
+ List<Sort.Order> orders = pageable.getSort().toList();
+ sortPageable = pageable.withSort(Sort.by(orders.stream()
+ .map(e ->
e.withProperty(SearchUtil.validateToJdbcColumnName(e.getProperty(),
headersByName, false))).toList()));
+ } else {
+ pageable = pageable.withSort(Sort.Direction.DESC, pkColumn);
+ sortPageable = pageable;
+ }
+
+ String alias = "main";
+ String dateFormat = pagedRequest.getDateFormat();
+ String dateTimeFormat = pagedRequest.getDateTimeFormat();
+ Locale locale = pagedRequest.getLocaleObject();
+ StringBuilder select = new
StringBuilder(sqlGenerator.buildSelect(selectColumns, alias, false));
+ StringBuilder from = new StringBuilder("
").append(sqlGenerator.buildFrom(apptable, alias, false));
+ StringBuilder where = new StringBuilder();
+ ArrayList<Object> params = new ArrayList<>();
+ SearchUtil.buildQueryCondition(columnFilters, where, params, alias,
headersByName, dateFormat, dateTimeFormat, locale, false,
+ sqlGenerator);
+
+ if (datatableQueries != null) {
+ StringBuilder dataSelect = new StringBuilder();
+ StringBuilder dataFrom = new StringBuilder();
+ StringBuilder dataWhere = new StringBuilder();
+ ArrayList<Object> dataParams = new ArrayList<>();
+ for (int i = 0; i < datatableQueries.size(); i++) {
+ TableQueryData tableQuery = datatableQueries.get(i);
+ boolean added =
datatableService.buildDataQueryEmbedded(EntityTables.SAVINGS_TRANSACTION,
tableQuery.getTable(),
+ tableQuery.getQuery(), selectColumns, dataSelect,
dataFrom, dataWhere, dataParams, alias, ("d" + i), dateFormat,
+ dateTimeFormat, locale);
+ if (added) {
+ if (!dataSelect.isEmpty()) {
+ select.append(select.isEmpty() ? "SELECT " : ",
").append(dataSelect);
+ }
+ if (!dataFrom.isEmpty()) {
+ from.append(" ").append(dataFrom);
+ }
+ if (!dataWhere.isEmpty()) {
+ where.append(where.isEmpty() ? " WHERE " : " AND
").append(dataWhere);
+ }
+ params.addAll(dataParams);
+ dataSelect.setLength(0);
+ dataFrom.setLength(0);
+ dataWhere.setLength(0);
+ dataParams.clear();
+ }
+
resultColumns.addAll(tableQuery.getQuery().getNonNullResultColumns());
+ }
+ }
+
+ List<JsonObject> results = new ArrayList<>();
+ Object[] args = params.toArray();
+
+ // Execute the count Query
+ String countQuery = "SELECT COUNT(*)" + from + where;
+ Integer totalElements = jdbcTemplate.queryForObject(countQuery,
Integer.class, args);
+ if (totalElements == null || totalElements == 0) {
+ return PageableExecutionUtils.getPage(results, pageable, () -> 0);
+ }
+
+ StringBuilder query = new
StringBuilder().append(select).append(from).append(where);
+ query.append("
").append(sqlGenerator.buildOrderBy(sortPageable.getSort().toList(), null,
false));
+ if (pageable.isPaged()) {
+ query.append("
").append(sqlGenerator.limit(pageable.getPageSize(), (int)
pageable.getOffset()));
+ }
+
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet(query.toString(), args);
+
+ while (rowSet.next()) {
+ SearchUtil.extractJsonResult(rowSet, selectColumns, resultColumns,
results);
+ }
+ return PageableExecutionUtils.getPage(results, pageable, () ->
totalElements);
+ }
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/AdvancedQueryData.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/AdvancedQueryData.java
new file mode 100644
index 000000000..b5a9a21c4
--- /dev/null
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/AdvancedQueryData.java
@@ -0,0 +1,53 @@
+/**
+ * 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.portfolio.search.data;
+
+import java.io.Serializable;
+import java.util.ArrayList;
+import java.util.List;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+/**
+ * Immutable data object representing datatable data.
+ */
+@Data
+@NoArgsConstructor
+public final class AdvancedQueryData implements Serializable {
+
+ private List<ColumnFilterData> columnFilters;
+
+ private List<String> resultColumns;
+
+ public List<ColumnFilterData> getNonNullFilters() {
+ return columnFilters == null ? new ArrayList<>() : columnFilters;
+ }
+
+ public boolean hasFilter() {
+ return columnFilters != null && !columnFilters.isEmpty();
+ }
+
+ public List<String> getNonNullResultColumns() {
+ return resultColumns == null ? new ArrayList<>() : resultColumns;
+ }
+
+ public boolean hasResultColumn() {
+ return resultColumns != null && !resultColumns.isEmpty();
+ }
+}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/AdvancedQueryRequest.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/AdvancedQueryRequest.java
new file mode 100644
index 000000000..4e5c1ef7b
--- /dev/null
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/AdvancedQueryRequest.java
@@ -0,0 +1,46 @@
+/**
+ * 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.portfolio.search.data;
+
+import java.io.Serializable;
+import java.util.List;
+import lombok.Data;
+import lombok.NoArgsConstructor;
+
+/**
+ * Immutable data object representing datatable data.
+ */
+@Data
+@NoArgsConstructor
+public final class AdvancedQueryRequest implements Serializable {
+
+ private AdvancedQueryData baseQuery;
+
+ private List<TableQueryData> datatableQueries;
+
+ public boolean hasFilter() {
+ return (baseQuery != null && baseQuery.hasFilter())
+ || (datatableQueries != null &&
datatableQueries.stream().anyMatch(TableQueryData::hasFilter));
+ }
+
+ public boolean hasResultColumn() {
+ return (baseQuery != null && baseQuery.hasResultColumn())
+ || (datatableQueries != null &&
datatableQueries.stream().anyMatch(TableQueryData::hasResultColumn));
+ }
+}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/TableQueryData.java
similarity index 60%
copy from
fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
copy to
fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/TableQueryData.java
index 3bf5765cf..fe682442c 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/savings/service/search/SavingsAccountTransactionSearchService.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/data/TableQueryData.java
@@ -16,14 +16,28 @@
* specific language governing permissions and limitations
* under the License.
*/
-package org.apache.fineract.portfolio.savings.service.search;
+package org.apache.fineract.portfolio.search.data;
-import jakarta.validation.constraints.NotNull;
-import
org.apache.fineract.portfolio.savings.data.SavingsAccountTransactionData;
-import org.apache.fineract.portfolio.search.data.TransactionSearchRequest;
-import org.springframework.data.domain.Page;
+import java.io.Serializable;
+import lombok.Data;
+import lombok.NoArgsConstructor;
-public interface SavingsAccountTransactionSearchService {
+/**
+ * Immutable data object representing datatable data.
+ */
+@Data
+@NoArgsConstructor
+public final class TableQueryData implements Serializable {
+
+ private String table;
+
+ private AdvancedQueryData query;
+
+ public boolean hasFilter() {
+ return query != null && query.hasFilter();
+ }
- Page<SavingsAccountTransactionData> searchTransactions(@NotNull Long
savingsId, @NotNull TransactionSearchRequest searchParameters);
+ public boolean hasResultColumn() {
+ return query != null && query.hasResultColumn();
+ }
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchUtil.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchUtil.java
index 2c3ac2211..880613f0d 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchUtil.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchUtil.java
@@ -25,7 +25,6 @@ import static
org.apache.fineract.infrastructure.core.service.DateUtils.DEFAULT_
import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_FIELD_MANDATORY;
import static
org.apache.fineract.portfolio.search.SearchConstants.API_PARAM_COLUMN;
-import com.google.common.base.Predicate;
import com.google.gson.JsonObject;
import jakarta.validation.constraints.NotNull;
import java.math.BigDecimal;
@@ -38,6 +37,7 @@ import java.util.Collection;
import java.util.List;
import java.util.Locale;
import java.util.Map;
+import java.util.function.Predicate;
import java.util.stream.Collectors;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
@@ -116,47 +116,75 @@ public final class SearchUtil {
}
@NotNull
- public static List<String> validateToJdbcColumns(List<String> columns,
Map<String, ResultsetColumnHeaderData> columnHeaders,
- List<ApiParameterError> errors, boolean allowEmpty) {
- List<String> result = new ArrayList<>();
+ public static List<String> validateToJdbcColumnNames(List<String> columns,
Map<String, ResultsetColumnHeaderData> headersByName,
+ boolean allowEmpty) {
+ List<ResultsetColumnHeaderData> columnHeaders =
validateToJdbcColumns(columns, headersByName, allowEmpty);
+ return columnHeaders.stream().map(e -> e == null ? null :
e.getColumnName()).toList();
+ }
+
+ @NotNull
+ public static List<ResultsetColumnHeaderData>
validateToJdbcColumns(List<String> columns,
+ Map<String, ResultsetColumnHeaderData> headersByName, boolean
allowEmpty) {
+ final List<ApiParameterError> errors = new ArrayList<>();
+
+ List<ResultsetColumnHeaderData> result = new ArrayList<>();
if (columns == null || columns.isEmpty()) {
if (!allowEmpty) {
errors.add(parameterErrorWithValue("error.msg.columns.empty",
"Columns list is empty", API_PARAM_COLUMN, null));
}
} else {
- columns.forEach(rcn -> result.add(validateToJdbcColumn(rcn,
columnHeaders, errors, allowEmpty)));
+ columns.forEach(rcn -> result.add(validateToJdbcColumnImpl(rcn,
headersByName, errors, allowEmpty)));
+ }
+ if (!errors.isEmpty()) {
+ throw new PlatformApiDataValidationException(errors);
}
return result;
}
- public static String validateToJdbcColumn(String column, Map<String,
ResultsetColumnHeaderData> columnHeaders,
- List<ApiParameterError> errors, boolean allowEmpty) {
+ public static String validateToJdbcColumnName(String column, Map<String,
ResultsetColumnHeaderData> headersByName, boolean allowEmpty) {
+ ResultsetColumnHeaderData columnHeader = validateToJdbcColumn(column,
headersByName, allowEmpty);
+ return columnHeader == null ? null : columnHeader.getColumnName();
+ }
+
+ public static ResultsetColumnHeaderData validateToJdbcColumn(String column,
+ @NotNull Map<String, ResultsetColumnHeaderData> headersByName,
boolean allowEmpty) {
+ final List<ApiParameterError> errors = new ArrayList<>();
+ ResultsetColumnHeaderData columnHeader =
validateToJdbcColumnImpl(column, headersByName, errors, allowEmpty);
+ if (!errors.isEmpty()) {
+ throw new PlatformApiDataValidationException(errors);
+ }
+ return columnHeader;
+ }
+
+ private static ResultsetColumnHeaderData validateToJdbcColumnImpl(String
column,
+ @NotNull Map<String, ResultsetColumnHeaderData> headersByName,
@NotNull List<ApiParameterError> errors, boolean allowEmpty) {
if (!allowEmpty && column == null) {
errors.add(parameterErrorWithValue("error.msg.column.empty",
"Column filter is empty", API_PARAM_COLUMN, null));
}
- if (column != null) {
- if (!columnHeaders.containsKey(column)) {
- column = camelToSnake(column);
- if (!columnHeaders.containsKey(column)) {
- errors.add(
-
parameterErrorWithValue("error.msg.invalid.column", "Column not exist in
database", API_PARAM_COLUMN, column));
- }
- }
+ if (column == null) {
+ return null;
+ }
+ Collection<ResultsetColumnHeaderData> columnHeaders =
headersByName.values();
+ ResultsetColumnHeaderData columnHeader;
+ if ((columnHeader = findFiltered(columnHeaders, e ->
e.isNamed(column))) == null
+ && (columnHeader = findFiltered(columnHeaders, e ->
e.isNamed(column.replaceAll(" ", "_")))) == null
+ && (columnHeader = findFiltered(columnHeaders, e ->
e.isNamed(camelToSnake(column.replaceAll(" ", ""))))) == null) {
+ errors.add(parameterErrorWithValue("error.msg.invalid.column",
"Column not exist in database", API_PARAM_COLUMN, column));
}
- return column;
+ return columnHeader;
}
public static boolean buildQueryCondition(List<ColumnFilterData>
columnFilters, @NotNull StringBuilder where,
- @NotNull List<Object> params, String alias, Map<String,
ResultsetColumnHeaderData> columnHeaders, boolean embedded,
- boolean strict, @NotNull DatabaseSpecificSQLGenerator
sqlGenerator) {
+ @NotNull List<Object> params, String alias, Map<String,
ResultsetColumnHeaderData> headersByName, String dateFormat,
+ String dateTimeFormat, Locale locale, boolean embedded, @NotNull
DatabaseSpecificSQLGenerator sqlGenerator) {
if (columnFilters == null) {
return false;
}
boolean added = false;
int isize = columnFilters.size();
for (int i = 0; i < isize; i++) {
- boolean addedFilter = buildFilterCondition(columnFilters.get(i),
where, params, alias, columnHeaders, embedded, strict,
- sqlGenerator);
+ boolean addedFilter = buildFilterCondition(columnFilters.get(i),
where, params, alias, headersByName, dateFormat,
+ dateTimeFormat, locale, embedded, sqlGenerator);
if (addedFilter && i < isize - 1) {
where.append(" AND ");
}
@@ -166,8 +194,8 @@ public final class SearchUtil {
}
public static boolean buildFilterCondition(ColumnFilterData columnFilter,
@NotNull StringBuilder where, @NotNull List<Object> params,
- String alias, Map<String, ResultsetColumnHeaderData>
columnHeaders, boolean embedded, boolean strict,
- @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
+ String alias, Map<String, ResultsetColumnHeaderData>
headersByName, String dateFormat, String dateTimeFormat, Locale locale,
+ boolean embedded, @NotNull DatabaseSpecificSQLGenerator
sqlGenerator) {
String columnName = columnFilter.getColumn();
List<FilterData> filters = columnFilter.getFilters();
int size = filters.size();
@@ -175,26 +203,18 @@ public final class SearchUtil {
if (!embedded && where.isEmpty()) {
where.append(" WHERE ");
}
+ ResultsetColumnHeaderData columnHeader =
validateToJdbcColumn(columnName, headersByName, false);
+
FilterData filter = filters.get(i);
SqlOperator operator = filter.getOperator();
List<String> values = filter.getValues();
List<Object> objectValues = values == null ? null
- : values.stream().map(e ->
parseAndValidateJdbcColumnValue(columnName, e, columnHeaders, strict,
sqlGenerator))
+ : values.stream()
+ .map(e -> parseJdbcColumnValue(columnHeader, e,
dateFormat, dateTimeFormat, locale, false, sqlGenerator))
.toList();
- operator.validateValues(values);
- if (operator.isPlaceholderSupported()) {
- where.append(operator.formatPlaceholder(sqlGenerator,
columnName, alias));
- if (objectValues != null) {
- if (operator.isListType()) {
- params.add(objectValues);
- } else {
- params.addAll(objectValues);
- }
- }
- } else {
- where.append(operator.formatSql(sqlGenerator,
columnHeaders.get(columnName).getColumnType(), columnName, alias, values));
- }
+ buildCondition(columnHeader.getColumnName(),
columnHeader.getColumnType(), operator, objectValues, where, params, alias,
+ sqlGenerator);
if (i < size - 1) {
where.append(" AND ");
}
@@ -202,22 +222,24 @@ public final class SearchUtil {
return size > 0;
}
- public static Object parseAndValidateJdbcColumnValue(String column, String
columnValue,
- Map<String, ResultsetColumnHeaderData> columnHeaders, boolean
strict, @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
- List<ApiParameterError> errors = new ArrayList<>();
- column = validateToJdbcColumn(column, columnHeaders, errors, false);
- if (!errors.isEmpty()) {
- throw new PlatformApiDataValidationException(errors);
+ public static void buildCondition(@NotNull String definition, JdbcJavaType
columnType, @NotNull SqlOperator operator,
+ List<Object> values, @NotNull StringBuilder where, @NotNull
List<Object> params, String alias,
+ @NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
+ int paramCount = values == null ? 0 : values.size();
+ where.append(operator.formatPlaceholder(sqlGenerator, definition,
paramCount, alias));
+ if (values != null) {
+ params.addAll(values);
}
- ResultsetColumnHeaderData columnHeader = columnHeaders.get(column);
- String dateFormat = columnHeader.getColumnType().isDateTimeType() ?
DEFAULT_DATETIME_FORMAT : DEFAULT_DATE_FORMAT;
+ }
+
+ public static Object parseJdbcColumnValue(@NotNull
ResultsetColumnHeaderData columnHeader, String columnValue, String dateFormat,
+ String dateTimeFormat, Locale locale, boolean strict, @NotNull
DatabaseSpecificSQLGenerator sqlGenerator) {
return
columnHeader.getColumnType().toJdbcValue(sqlGenerator.getDialect(),
- parseAndValidateColumnValue(columnHeader, columnValue,
dateFormat, ENGLISH, strict, sqlGenerator), false);
+ parseColumnValue(columnHeader, columnValue, dateFormat,
dateTimeFormat, locale, strict, sqlGenerator), false);
}
- public static Object parseAndValidateColumnValue(final
ResultsetColumnHeaderData columnHeader, final String pValue,
- final String dateFormat, final Locale locale, boolean strict,
@NotNull DatabaseSpecificSQLGenerator sqlGenerator) {
- String columnValue = pValue;
+ public static Object parseColumnValue(@NotNull ResultsetColumnHeaderData
columnHeader, String columnValue, String dateFormat,
+ String dateTimeFormat, Locale locale, boolean strict, @NotNull
DatabaseSpecificSQLGenerator sqlGenerator) {
JdbcJavaType colType = columnHeader.getColumnType();
if (!colType.isStringType() || !columnHeader.isMandatory()) {
columnValue = StringUtils.trimToNull(columnValue);
@@ -257,12 +279,14 @@ public final class SearchUtil {
+ " - Invalid Type " +
colType.getJdbcName(sqlGenerator.getDialect()) + " (neither varchar nor int)");
}
}
-
+ locale = locale == null ? ENGLISH : locale;
if (colType.isDateType()) {
- return JsonParserHelper.convertFrom(columnValue,
columnHeader.getColumnName(), dateFormat, locale);
+ String format = dateFormat == null ? DEFAULT_DATE_FORMAT :
dateFormat;
+ return JsonParserHelper.convertFrom(columnValue,
columnHeader.getColumnName(), format, locale);
}
if (colType.isDateTimeType()) {
- return JsonParserHelper.convertDateTimeFrom(columnValue,
columnHeader.getColumnName(), dateFormat, locale);
+ String format = dateTimeFormat == null ? DEFAULT_DATETIME_FORMAT :
dateTimeFormat;
+ return JsonParserHelper.convertDateTimeFrom(columnValue,
columnHeader.getColumnName(), format, locale);
}
if (colType.isAnyIntegerType()) {
return helper.convertToInteger(columnValue,
columnHeader.getColumnName(), locale);
diff --git
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/savings/SavingsAccountHelper.java
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/savings/SavingsAccountHelper.java
index 48d0ccb6d..db72cf6d8 100644
---
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/savings/SavingsAccountHelper.java
+++
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/savings/SavingsAccountHelper.java
@@ -23,6 +23,7 @@ import static
org.apache.fineract.integrationtests.common.Utils.TENANT_PARAM_NAM
import com.google.common.reflect.TypeToken;
import com.google.gson.Gson;
+import io.restassured.path.json.JsonPath;
import io.restassured.specification.RequestSpecification;
import io.restassured.specification.ResponseSpecification;
import jakarta.ws.rs.core.HttpHeaders;
@@ -39,6 +40,7 @@ import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
+import org.apache.fineract.client.models.PagedLocalRequestAdvancedQueryRequest;
import
org.apache.fineract.client.models.SavingsAccountTransactionsSearchResponse;
import org.apache.fineract.client.util.JSON;
import org.apache.fineract.integrationtests.client.IntegrationTest;
@@ -661,6 +663,11 @@ public class SavingsAccountHelper extends IntegrationTest {
return GSON.fromJson(response,
SavingsAccountTransactionsSearchResponse.class);
}
+ public Map<String, Object> querySavingsTransactions(Integer savingsId,
PagedLocalRequestAdvancedQueryRequest request) {
+ String response =
ok(fineract().savingsTransactions.advancedQuery1(savingsId.longValue(),
request));
+ return JsonPath.from(response).get("");
+ }
+
public List<HashMap> getSavingsTransactions(final Integer savingsID) {
final Object get = getSavingsCollectionAttribute(savingsID,
"transactions");
final String json = new Gson().toJson(get);
diff --git
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/system/DatatableHelper.java
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/system/DatatableHelper.java
index 22eae4d56..5bdd75972 100644
---
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/system/DatatableHelper.java
+++
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/common/system/DatatableHelper.java
@@ -24,6 +24,7 @@ import static org.junit.jupiter.api.Assertions.assertEquals;
import com.google.gson.Gson;
import com.google.gson.JsonElement;
+import io.restassured.path.json.JsonPath;
import io.restassured.specification.RequestSpecification;
import io.restassured.specification.ResponseSpecification;
import java.util.ArrayList;
@@ -31,8 +32,10 @@ import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
+import java.util.Map;
import java.util.Objects;
import org.apache.fineract.client.models.GetDataTablesResponse;
+import org.apache.fineract.client.models.PagedLocalRequestAdvancedQueryData;
import org.apache.fineract.client.models.PostDataTablesAppTableIdResponse;
import org.apache.fineract.client.models.PostDataTablesRequest;
import org.apache.fineract.client.models.PostDataTablesResponse;
@@ -294,4 +297,8 @@ public class DatatableHelper extends IntegrationTest {
return GSON.fromJson(response, PutDataTablesResponse.class);
}
+ public Map<String, Object> queryDatatable(String dataTableName,
PagedLocalRequestAdvancedQueryData request) {
+ String response =
ok(fineract().dataTables.advancedQuery(dataTableName, request));
+ return JsonPath.from(response).get("");
+ }
}
diff --git
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableAdvancedQueryTest.java
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableAdvancedQueryTest.java
new file mode 100644
index 000000000..5df73fb1d
--- /dev/null
+++
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableAdvancedQueryTest.java
@@ -0,0 +1,387 @@
+/**
+ * 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.datatable;
+
+import static org.apache.fineract.client.models.FilterData.OperatorEnum.BTW;
+import static org.apache.fineract.client.models.FilterData.OperatorEnum.EQ;
+import static org.apache.fineract.client.models.FilterData.OperatorEnum.GT;
+import static org.apache.fineract.client.models.FilterData.OperatorEnum.GTE;
+import static org.apache.fineract.client.models.FilterData.OperatorEnum.IN;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_FIELD_TYPE_BOOLEAN;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_FIELD_TYPE_DATE;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_FIELD_TYPE_DECIMAL;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_FIELD_TYPE_NUMBER;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_FIELD_TYPE_STRING;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_FIELD_TYPE_TEXT;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_PARAM_APPTABLE_NAME;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_PARAM_COLUMNS;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_PARAM_DATATABLE_NAME;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_PARAM_MULTIROW;
+import static
org.apache.fineract.infrastructure.dataqueries.api.DataTableApiConstant.API_PARAM_SUBTYPE;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertFalse;
+import static org.junit.jupiter.api.Assertions.assertNotNull;
+import static org.junit.jupiter.api.Assertions.assertNull;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import com.google.gson.Gson;
+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 java.math.BigDecimal;
+import java.time.LocalDate;
+import java.time.ZoneId;
+import java.time.temporal.ChronoUnit;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+import org.apache.fineract.client.models.AdvancedQueryData;
+import org.apache.fineract.client.models.AdvancedQueryRequest;
+import org.apache.fineract.client.models.ColumnFilterData;
+import org.apache.fineract.client.models.FilterData;
+import org.apache.fineract.client.models.GetDataTablesResponse;
+import org.apache.fineract.client.models.PagedLocalRequestAdvancedQueryData;
+import org.apache.fineract.client.models.PagedLocalRequestAdvancedQueryRequest;
+import org.apache.fineract.client.models.PostDataTablesResponse;
+import org.apache.fineract.client.models.ResultsetColumnHeaderData;
+import org.apache.fineract.client.models.SortOrder;
+import org.apache.fineract.client.models.TableQueryData;
+import org.apache.fineract.infrastructure.businessdate.domain.BusinessDateType;
+import org.apache.fineract.infrastructure.core.service.DateUtils;
+import org.apache.fineract.infrastructure.dataqueries.data.EntityTables;
+import org.apache.fineract.integrationtests.common.BusinessDateHelper;
+import org.apache.fineract.integrationtests.common.ClientHelper;
+import org.apache.fineract.integrationtests.common.CommonConstants;
+import org.apache.fineract.integrationtests.common.GlobalConfigurationHelper;
+import org.apache.fineract.integrationtests.common.Utils;
+import
org.apache.fineract.integrationtests.common.savings.SavingsAccountHelper;
+import
org.apache.fineract.integrationtests.common.savings.SavingsProductHelper;
+import
org.apache.fineract.integrationtests.common.savings.SavingsStatusChecker;
+import org.apache.fineract.integrationtests.common.system.DatatableHelper;
+import org.jetbrains.annotations.NotNull;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+public class DatatableAdvancedQueryTest {
+
+ private static final Logger LOG =
LoggerFactory.getLogger(DatatableAdvancedQueryTest.class);
+
+ private static final String SAVINGS_TRANSACTION_APP_TABLE_NAME =
EntityTables.SAVINGS_TRANSACTION.getName();
+ public static final String ACCOUNT_TYPE_INDIVIDUAL = "INDIVIDUAL";
+ public static final String SAVINGS_DATE_FORMAT = "dd MMM yyyy";
+
+ private static final String COLUMN_STRING = "aString";
+ private static final String COLUMN_TEXT = "aText";
+ private static final String COLUMN_DATE = "aDate";
+ private static final String COLUMN_BOOLEAN = "aBoolean";
+ private static final String COLUMN_INTEGER = "aNumber";
+ private static final String COLUMN_DECIMAL = "aDecimal";
+ private static final String COLUMN_TRANSACTION_ID =
"savings_transaction_id";
+ private static final String COLUMN_ID = "id";
+ private static final String COLUMN_SUBMITTED_DATE = "submitted_on_date";
+ private static final String COLUMN_AMOUNT = "amount";
+
+ private RequestSpecification requestSpec;
+ private ResponseSpecification responseSpec;
+ private DatatableHelper datatableHelper;
+ private SavingsProductHelper savingsProductHelper;
+ private SavingsAccountHelper savingsAccountHelper;
+
+ @BeforeEach
+ public void setup() {
+ Utils.initializeRESTAssured();
+ requestSpec = new
RequestSpecBuilder().setContentType(ContentType.JSON).build();
+ requestSpec.header("Authorization", "Basic " +
Utils.loginIntoServerAndGetBase64EncodedAuthenticationKey());
+ responseSpec = new ResponseSpecBuilder().expectStatusCode(200).build();
+ datatableHelper = new DatatableHelper(requestSpec, responseSpec);
+ savingsAccountHelper = new SavingsAccountHelper(requestSpec,
responseSpec);
+ savingsProductHelper = new SavingsProductHelper();
+ }
+
+ @Test
+ public void testDatatableAdvancedQuery() {
+ String datatable =
createAndVerifyDatatable(SAVINGS_TRANSACTION_APP_TABLE_NAME, null, false);
+
+ LocalDate today = LocalDate.now(ZoneId.systemDefault());
+ String todayS = DateUtils.format(today, SAVINGS_DATE_FORMAT);
+ LocalDate yesterday = today.minus(1, ChronoUnit.DAYS);
+ String yesterdayS = DateUtils.format(yesterday, SAVINGS_DATE_FORMAT);
+ try {
+ GlobalConfigurationHelper.updateIsBusinessDateEnabled(requestSpec,
responseSpec, true);
+ BusinessDateHelper.updateBusinessDate(requestSpec, responseSpec,
BusinessDateType.BUSINESS_DATE, today);
+
+ final Integer clientId = ClientHelper.createClient(requestSpec,
responseSpec, yesterdayS);
+ assertNotNull(clientId);
+ final Integer savingsId =
createSavingsAccountDailyPosting(clientId, yesterdayS);
+ assertNotNull(savingsId);
+
+ final Integer transactionIdD1 = (Integer)
savingsAccountHelper.depositToSavingsAccount(savingsId, "100", yesterdayS,
+ CommonConstants.RESPONSE_RESOURCE_ID);
+ assertNotNull(transactionIdD1);
+ BigDecimal decValue1 = new BigDecimal("1.111");
+ createDatatableEntry(datatable, transactionIdD1, yesterday, true,
1, decValue1);
+ final Integer transactionIdD2 = (Integer)
savingsAccountHelper.depositToSavingsAccount(savingsId, "300", yesterdayS,
+ CommonConstants.RESPONSE_RESOURCE_ID);
+ assertNotNull(transactionIdD2);
+ createDatatableEntry(datatable, transactionIdD2, yesterday, false,
2, new BigDecimal("2.2"));
+ final Integer transactionIdW1 = (Integer)
savingsAccountHelper.withdrawalFromSavingsAccount(savingsId, "100", todayS,
+ CommonConstants.RESPONSE_RESOURCE_ID);
+ assertNotNull(transactionIdW1);
+ createDatatableEntry(datatable, transactionIdW1, today, true, 3,
new BigDecimal("3"));
+
+ String yesterdayIsoS = DateUtils.format(yesterday,
DateUtils.DEFAULT_DATE_FORMAT);
+ String todayIsoS = DateUtils.format(today,
DateUtils.DEFAULT_DATE_FORMAT);
+ AdvancedQueryData query = new AdvancedQueryData()
+ .resultColumns(List.of(COLUMN_TRANSACTION_ID,
COLUMN_STRING, COLUMN_TEXT, COLUMN_DATE, COLUMN_BOOLEAN, COLUMN_INTEGER,
+ COLUMN_DECIMAL))
+ .addColumnFiltersItem(new
ColumnFilterData().column(COLUMN_TEXT)
+ .addFiltersItem(new
FilterData().operator(EQ).values(List.of(transactionIdD1.toString()))))
+ .addColumnFiltersItem(new
ColumnFilterData().column(COLUMN_DATE)
+ .addFiltersItem(new
FilterData().operator(BTW).values(List.of(yesterdayIsoS, yesterdayIsoS))));
+ PagedLocalRequestAdvancedQueryData pagedQuery = new
PagedLocalRequestAdvancedQueryData().page(0).size(3)
+ .addSortsItem(new
SortOrder().property("created_at").direction(SortOrder.DirectionEnum.DESC))
+ .addSortsItem(new
SortOrder().property(COLUMN_TRANSACTION_ID).direction(SortOrder.DirectionEnum.DESC)).request(query);
+ Map<String, Object> response =
datatableHelper.queryDatatable(datatable, pagedQuery);
+
+ assertEquals(1, response.get("total"));
+ List content = (List) response.get("content");
+ assertNotNull(content);
+ assertEquals(1, content.size());
+ Map<String, Object> first = (Map<String, Object>) content.get(0);
+ assertEquals(transactionIdD1, first.get(COLUMN_TRANSACTION_ID));
+ assertEquals(transactionIdD1.toString(), first.get(COLUMN_TEXT));
+ assertEquals(yesterdayIsoS, first.get(COLUMN_DATE));
+ assertTrue((Boolean) first.get(COLUMN_BOOLEAN));
+ assertEquals(1, first.get(COLUMN_INTEGER));
+ assertEquals(decValue1.floatValue(), first.get(COLUMN_DECIMAL));
+
+ query.resultColumns(List.of(COLUMN_TRANSACTION_ID));
+ query.columnFilters(List.of(
+ new
ColumnFilterData().column(COLUMN_INTEGER).addFiltersItem(new
FilterData().operator(GTE).values(List.of("1"))),
+ new ColumnFilterData().column(COLUMN_DATE)
+ .addFiltersItem(new
FilterData().operator(BTW).values(List.of(yesterdayIsoS, todayIsoS)))));
+ response = datatableHelper.queryDatatable(datatable, pagedQuery);
+
+ assertEquals(3, response.get("total"));
+ content = (List) response.get("content");
+ assertNotNull(content);
+ assertEquals(3, content.size());
+ first = (Map) content.get(0);
+ assertEquals(transactionIdW1, first.get(COLUMN_TRANSACTION_ID));
+ assertNull(first.get(COLUMN_TEXT));
+ assertNull(first.get(COLUMN_DATE));
+ assertNull(first.get(COLUMN_BOOLEAN));
+ assertNull(first.get(COLUMN_INTEGER));
+ assertNull(first.get(COLUMN_DECIMAL));
+ assertEquals(transactionIdD2, ((Map)
content.get(1)).get(COLUMN_TRANSACTION_ID));
+
+ deleteDatatable(datatable, transactionIdD1, transactionIdD2,
transactionIdW1);
+
+ } finally {
+ GlobalConfigurationHelper.updateIsBusinessDateEnabled(requestSpec,
responseSpec, false);
+ }
+ }
+
+ @Test
+ public void testApptableWithDatatableAdvancedQuery() {
+ String datatable =
createAndVerifyDatatable(SAVINGS_TRANSACTION_APP_TABLE_NAME, null, false);
+
+ LocalDate today = LocalDate.now(ZoneId.systemDefault());
+ String todayS = DateUtils.format(today, SAVINGS_DATE_FORMAT);
+ LocalDate yesterday = today.minus(1, ChronoUnit.DAYS);
+ String yesterdayS = DateUtils.format(yesterday, SAVINGS_DATE_FORMAT);
+ try {
+ GlobalConfigurationHelper.updateIsBusinessDateEnabled(requestSpec,
responseSpec, true);
+ BusinessDateHelper.updateBusinessDate(requestSpec, responseSpec,
BusinessDateType.BUSINESS_DATE, today);
+
+ final Integer clientId = ClientHelper.createClient(requestSpec,
responseSpec, yesterdayS);
+ assertNotNull(clientId);
+ final Integer savingsId =
createSavingsAccountDailyPosting(clientId, yesterdayS);
+ assertNotNull(savingsId);
+
+ final Integer transactionIdD1 = (Integer)
savingsAccountHelper.depositToSavingsAccount(savingsId, "100", yesterdayS,
+ CommonConstants.RESPONSE_RESOURCE_ID);
+ assertNotNull(transactionIdD1);
+ BigDecimal decValue1 = new BigDecimal("1.111");
+ createDatatableEntry(datatable, transactionIdD1, yesterday, true,
1, decValue1);
+ final Integer transactionIdD2 = (Integer)
savingsAccountHelper.depositToSavingsAccount(savingsId, "300", yesterdayS,
+ CommonConstants.RESPONSE_RESOURCE_ID);
+ assertNotNull(transactionIdD2);
+ BigDecimal decValue2 = new BigDecimal("2.2");
+ createDatatableEntry(datatable, transactionIdD2, yesterday, false,
2, decValue2);
+ final Integer transactionIdW1 = (Integer)
savingsAccountHelper.withdrawalFromSavingsAccount(savingsId, "100", todayS,
+ CommonConstants.RESPONSE_RESOURCE_ID);
+ assertNotNull(transactionIdW1);
+ createDatatableEntry(datatable, transactionIdW1, today, true, 3,
new BigDecimal("3"));
+
+ String yesterdayIsoS = DateUtils.format(yesterday,
DateUtils.DEFAULT_DATE_FORMAT);
+ String todayIsoS = DateUtils.format(today,
DateUtils.DEFAULT_DATE_FORMAT);
+
+ AdvancedQueryData baseQuery = new
AdvancedQueryData().resultColumns(List.of(COLUMN_ID, COLUMN_SUBMITTED_DATE))
+ .addColumnFiltersItem(new
ColumnFilterData().column(COLUMN_AMOUNT)
+ .addFiltersItem(new
FilterData().operator(GT).values(List.of("100"))))
+ .addColumnFiltersItem(new
ColumnFilterData().column(COLUMN_SUBMITTED_DATE)
+ .addFiltersItem(new
FilterData().operator(BTW).values(List.of(todayIsoS, todayIsoS))));
+ AdvancedQueryData dataQuery = new AdvancedQueryData()
+ .resultColumns(List.of(COLUMN_TRANSACTION_ID,
COLUMN_STRING, COLUMN_TEXT, COLUMN_DATE, COLUMN_BOOLEAN, COLUMN_INTEGER,
+ COLUMN_DECIMAL))
+ .addColumnFiltersItem(new
ColumnFilterData().column(COLUMN_TEXT).addFiltersItem(
+ new
FilterData().operator(IN).values(List.of(transactionIdD1.toString(),
transactionIdD2.toString()))))
+ .addColumnFiltersItem(new
ColumnFilterData().column(COLUMN_DATE)
+ .addFiltersItem(new
FilterData().operator(BTW).values(List.of(yesterdayIsoS, yesterdayIsoS))));
+ AdvancedQueryRequest queryRequest = new
AdvancedQueryRequest().baseQuery(baseQuery)
+ .datatableQueries(List.of(new
TableQueryData().table(datatable).query(dataQuery)));
+ PagedLocalRequestAdvancedQueryRequest pagedRequest = new
PagedLocalRequestAdvancedQueryRequest().page(0).size(2)
+ .addSortsItem(new
SortOrder().property(COLUMN_SUBMITTED_DATE).direction(SortOrder.DirectionEnum.DESC))
+ .addSortsItem(new
SortOrder().property(COLUMN_ID).direction(SortOrder.DirectionEnum.DESC)).request(queryRequest);
+ Map<String, Object> response =
savingsAccountHelper.querySavingsTransactions(savingsId, pagedRequest);
+
+ assertEquals(1, response.get("total"));
+ List content = (List) response.get("content");
+ assertNotNull(content);
+ assertEquals(1, content.size());
+ Map<String, Object> first = (Map<String, Object>) content.get(0);
+ assertEquals(transactionIdD2, first.get(COLUMN_ID));
+ assertEquals(todayIsoS, first.get(COLUMN_SUBMITTED_DATE));
+ assertEquals(transactionIdD2, first.get(COLUMN_TRANSACTION_ID));
+ assertEquals(transactionIdD2.toString(), first.get(COLUMN_TEXT));
+ assertEquals(yesterdayIsoS, first.get(COLUMN_DATE));
+ assertFalse((Boolean) first.get(COLUMN_BOOLEAN));
+ assertEquals(2, first.get(COLUMN_INTEGER));
+ assertEquals(decValue2.floatValue(), first.get(COLUMN_DECIMAL));
+
+ baseQuery.columnFilters(List.of(
+ new
ColumnFilterData().column(COLUMN_AMOUNT).addFiltersItem(new
FilterData().operator(GTE).values(List.of("100"))),
+ new ColumnFilterData().column(COLUMN_SUBMITTED_DATE)
+ .addFiltersItem(new
FilterData().operator(BTW).values(List.of(todayIsoS, todayIsoS)))));
+ dataQuery.resultColumns(List.of(COLUMN_TRANSACTION_ID));
+ dataQuery.columnFilters(List.of(
+ new
ColumnFilterData().column(COLUMN_INTEGER).addFiltersItem(new
FilterData().operator(GTE).values(List.of("1"))),
+ new ColumnFilterData().column(COLUMN_DATE)
+ .addFiltersItem(new
FilterData().operator(BTW).values(List.of(yesterdayIsoS, todayIsoS)))));
+ response =
savingsAccountHelper.querySavingsTransactions(savingsId, pagedRequest);
+
+ assertEquals(3, response.get("total"));
+ content = (List) response.get("content");
+ assertNotNull(content);
+ assertEquals(2, content.size()); // page size 2
+ first = (Map) content.get(0);
+ assertEquals(transactionIdW1, first.get(COLUMN_ID));
+ assertEquals(todayIsoS, first.get(COLUMN_SUBMITTED_DATE));
+ assertEquals(transactionIdW1, first.get(COLUMN_TRANSACTION_ID));
+ assertNull(first.get(COLUMN_TEXT));
+ assertNull(first.get(COLUMN_DATE));
+ assertNull(first.get(COLUMN_BOOLEAN));
+ assertNull(first.get(COLUMN_INTEGER));
+ assertNull(first.get(COLUMN_DECIMAL));
+ assertEquals(transactionIdD2, ((Map)
content.get(1)).get(COLUMN_TRANSACTION_ID));
+
+ deleteDatatable(datatable, transactionIdD1, transactionIdD2,
transactionIdW1);
+
+ } finally {
+ GlobalConfigurationHelper.updateIsBusinessDateEnabled(requestSpec,
responseSpec, false);
+ }
+ }
+
+ private String createAndVerifyDatatable(String apptable, String subType,
boolean multiRow) {
+ // creating datatable for apptable entity
+ final HashMap<String, Object> request = new HashMap<>();
+ request.put(API_PARAM_DATATABLE_NAME,
Utils.uniqueRandomStringGenerator("dt_" + apptable + "_", 5));
+ request.put(API_PARAM_APPTABLE_NAME, apptable);
+ if (subType != null) {
+ request.put(API_PARAM_SUBTYPE, subType);
+ }
+ request.put(API_PARAM_MULTIROW, multiRow);
+
+ final List<HashMap<String, Object>> datatableColumns = new
ArrayList<>();
+
DatatableHelper.addDatatableColumnsWithUniqueAndIndex(datatableColumns,
COLUMN_STRING, API_FIELD_TYPE_STRING, true, 50, null,
+ !multiRow, true);
+ DatatableHelper.addDatatableColumns(datatableColumns, COLUMN_TEXT,
API_FIELD_TYPE_TEXT, false, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumns, COLUMN_DATE,
API_FIELD_TYPE_DATE, true, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumns, COLUMN_BOOLEAN,
API_FIELD_TYPE_BOOLEAN, false, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumns, COLUMN_INTEGER,
API_FIELD_TYPE_NUMBER, false, null, null);
+ DatatableHelper.addDatatableColumns(datatableColumns, COLUMN_DECIMAL,
API_FIELD_TYPE_DECIMAL, false, null, null);
+ request.put(API_PARAM_COLUMNS, datatableColumns);
+
+ String requestJson = new Gson().toJson(request);
+ LOG.info("map : {}", requestJson);
+
+ PostDataTablesResponse response =
datatableHelper.createDatatable(requestJson);
+ String datatable = response.getResourceIdentifier();
+ assertNotNull(datatable);
+ GetDataTablesResponse dataTable =
datatableHelper.getDataTableDetails(datatable);
+ List<ResultsetColumnHeaderData> columnHeaderData =
dataTable.getColumnHeaderData();
+ assertNotNull(columnHeaderData);
+ // pk column and 2 audit columns were added automatically
+ assertEquals(9, columnHeaderData.size());
+ return datatable;
+ }
+
+ @NotNull
+ private HashMap<String, Object> createDatatableEntry(String datatable,
Integer apptableId, LocalDate dateValue, Boolean boolValue,
+ Integer intValue, BigDecimal decValue) {
+ final HashMap<String, Object> request = new HashMap<>();
+ request.put(COLUMN_STRING,
Utils.uniqueRandomStringGenerator(apptableId.toString() + "_", 5));
+ request.put(COLUMN_TEXT, apptableId);
+ request.put(COLUMN_DATE, DateUtils.format(dateValue,
SAVINGS_DATE_FORMAT));
+ request.put(COLUMN_BOOLEAN, boolValue);
+ request.put(COLUMN_INTEGER, intValue == null ? null :
intValue.toString());
+ request.put(COLUMN_DECIMAL, decValue == null ? null :
decValue.toString());
+ request.put("locale", "en");
+ request.put("dateFormat", SAVINGS_DATE_FORMAT);
+
+ String requestJson = new Gson().toJson(request);
+ HashMap<String, Object> response =
datatableHelper.createDatatableEntry(datatable, apptableId, true, requestJson);
+ assertNotNull(response.get("resourceId"));
+ return response;
+ }
+
+ private void deleteDatatable(String datatable, Integer... apptableIds) {
+ for (Integer apptableId : apptableIds) {
+ Integer deletedId =
this.datatableHelper.deleteDatatableEntries(datatable, apptableId,
"resourceId");
+ assertEquals(apptableId, deletedId, "ERROR IN DELETING THE
DATATABLE ENTRY");
+ }
+ String deletedDatatable =
this.datatableHelper.deleteDatatable(datatable);
+ assertEquals(datatable, deletedDatatable, "ERROR IN DELETING THE
DATATABLE");
+ }
+
+ private Integer createSavingsProductDailyPosting() {
+ final String savingsProductJSON =
savingsProductHelper.withInterestCompoundingPeriodTypeAsDaily()
+
.withInterestPostingPeriodTypeAsDaily().withInterestCalculationPeriodTypeAsDailyBalance().build();
+ return SavingsProductHelper.createSavingsProduct(savingsProductJSON,
requestSpec, responseSpec);
+ }
+
+ private Integer createSavingsAccountDailyPosting(final Integer clientID,
final String startDate) {
+ final Integer savingsProductID = createSavingsProductDailyPosting();
+ assertNotNull(savingsProductID);
+ final Integer savingsId =
savingsAccountHelper.applyForSavingsApplicationOnDate(clientID,
savingsProductID, ACCOUNT_TYPE_INDIVIDUAL,
+ startDate);
+ assertNotNull(savingsId);
+ HashMap savingsStatusHashMap =
savingsAccountHelper.approveSavingsOnDate(savingsId, startDate);
+ SavingsStatusChecker.verifySavingsIsApproved(savingsStatusHashMap);
+ savingsStatusHashMap =
savingsAccountHelper.activateSavingsAccount(savingsId, startDate);
+ SavingsStatusChecker.verifySavingsIsActive(savingsStatusHashMap);
+ return savingsId;
+ }
+}
diff --git
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableIntegrationTest.java
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableIntegrationTest.java
index dbca64a63..3a0f6c5be 100644
---
a/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableIntegrationTest.java
+++
b/integration-tests/src/test/java/org/apache/fineract/integrationtests/datatable/DatatableIntegrationTest.java
@@ -456,83 +456,86 @@ public class DatatableIntegrationTest extends
IntegrationTest {
// creating new client datatable entry
final boolean genericResultSet = true;
- HashMap<String, Object> datatableEntryMap = new HashMap<>();
- datatableEntryMap.put("itsABoolean", null);
- datatableEntryMap.put("itsADate", null);
- datatableEntryMap.put("itsADatetime", null);
- datatableEntryMap.put("itsADecimal", null);
- datatableEntryMap.put("TST_TST_TST_cd_itsADropdown", null);
- datatableEntryMap.put("itsANumber", null);
- datatableEntryMap.put("itsAString", null);
- datatableEntryMap.put("itsAText", null);
-
- datatableEntryMap.put("locale", "en");
- datatableEntryMap.put("dateFormat", "yyyy-MM-dd");
-
- String datatableEntryRequestJsonString = new
GsonBuilder().serializeNulls().create().toJson(datatableEntryMap);
- LOG.info("map : {}", datatableEntryRequestJsonString);
-
- HashMap<String, Object> datatableEntryResponseFirst =
this.datatableHelper.createDatatableEntry(datatableName, loanID,
- genericResultSet, datatableEntryRequestJsonString);
-
- datatableEntryMap = new HashMap<>();
- datatableEntryMap.put("itsABoolean", "");
- datatableEntryMap.put("itsADate", "");
- datatableEntryMap.put("itsADatetime", "");
- datatableEntryMap.put("itsADecimal", "");
- datatableEntryMap.put("TST_TST_TST_cd_itsADropdown", "");
- datatableEntryMap.put("itsANumber", "");
- datatableEntryMap.put("itsAString", "");
- datatableEntryMap.put("itsAText", "");
-
- datatableEntryMap.put("locale", "en");
- datatableEntryMap.put("dateFormat", "yyyy-MM-dd");
-
- datatableEntryRequestJsonString = new
GsonBuilder().serializeNulls().create().toJson(datatableEntryMap);
- HashMap<String, Object> datatableEntryResponseSecond =
this.datatableHelper.createDatatableEntry(datatableName, loanID,
- genericResultSet, datatableEntryRequestJsonString);
- assertNotNull(datatableEntryResponseFirst.get("resourceId"), "ERROR IN
CREATING THE ENTITY DATATABLE RECORD");
- assertNotNull(datatableEntryResponseSecond.get("resourceId"), "ERROR
IN CREATING THE ENTITY DATATABLE RECORD");
+ HashMap<String, Object> firstEntryMap = new HashMap<>();
+ firstEntryMap.put("itsABoolean", null);
+ firstEntryMap.put("itsADate", null);
+ firstEntryMap.put("itsADatetime", null);
+ firstEntryMap.put("itsADecimal", null);
+ firstEntryMap.put("TST_TST_TST_cd_itsADropdown", null);
+ firstEntryMap.put("itsANumber", null);
+ firstEntryMap.put("itsAString", null);
+ firstEntryMap.put("itsAText", null);
+
+ firstEntryMap.put("locale", "en");
+ firstEntryMap.put("dateFormat", "yyyy-MM-dd");
+
+ String firstEntryRequestJsonString = new
GsonBuilder().serializeNulls().create().toJson(firstEntryMap);
+ LOG.info("map : {}", firstEntryRequestJsonString);
+
+ HashMap<String, Object> firstEntryResponse =
this.datatableHelper.createDatatableEntry(datatableName, loanID,
genericResultSet,
+ firstEntryRequestJsonString);
+ assertNotNull(firstEntryResponse.get("resourceId"), "ERROR IN CREATING
THE ENTITY DATATABLE RECORD");
+
+ HashMap<String, Object> secondEntryMap = new HashMap<>();
+ secondEntryMap.put("itsABoolean", "");
+ secondEntryMap.put("itsADate", "");
+ secondEntryMap.put("itsADatetime", "");
+ secondEntryMap.put("itsADecimal", "");
+ secondEntryMap.put("TST_TST_TST_cd_itsADropdown", "");
+ secondEntryMap.put("itsANumber", "");
+ secondEntryMap.put("itsAString", "");
+ secondEntryMap.put("itsAText", "");
+
+ secondEntryMap.put("locale", "en");
+ secondEntryMap.put("dateFormat", "yyyy-MM-dd");
+
+ String secondEntryRequestJsonString = new
GsonBuilder().serializeNulls().create().toJson(secondEntryMap);
+ HashMap<String, Object> secondEntryResponse =
this.datatableHelper.createDatatableEntry(datatableName, loanID,
genericResultSet,
+ secondEntryRequestJsonString);
+ assertNotNull(secondEntryResponse.get("resourceId"), "ERROR IN
CREATING THE ENTITY DATATABLE RECORD");
// Read the Datatable entry generated with genericResultSet in true
(default)
HashMap<String, Object> items =
this.datatableHelper.readDatatableEntry(datatableName, loanID,
genericResultSet, null, "");
assertNotNull(items);
assertEquals(2, ((List) items.get("data")).size());
- assertEquals("id", ((Map) ((List)
items.get("columnHeaders")).get(0)).get("columnName"));
- assertEquals(1, ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(0));
- assertEquals("loan_id", ((Map) ((List)
items.get("columnHeaders")).get(1)).get("columnName"));
- assertEquals(loanID, ((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(1));
- assertEquals("itsABoolean", ((Map) ((List)
items.get("columnHeaders")).get(2)).get("columnName"));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(2));
- assertEquals("itsADate", ((Map) ((List)
items.get("columnHeaders")).get(3)).get("columnName"));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(3));
- assertEquals("itsADatetime", ((Map) ((List)
items.get("columnHeaders")).get(4)).get("columnName"));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(4));
- assertEquals("itsADecimal", ((Map) ((List)
items.get("columnHeaders")).get(5)).get("columnName"));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(5));
- assertEquals("TST_TST_TST_cd_itsADropdown", ((Map) ((List)
items.get("columnHeaders")).get(6)).get("columnName"));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(6));
- assertEquals("itsANumber", ((Map) ((List)
items.get("columnHeaders")).get(7)).get("columnName"));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(7));
- assertEquals("itsAString", ((Map) ((List)
items.get("columnHeaders")).get(8)).get("columnName"));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(8));
- assertEquals("itsAText", ((Map) ((List)
items.get("columnHeaders")).get(9)).get("columnName"));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(0)).get("row")).get(9));
-
- assertEquals(2, ((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(0));
- assertEquals(loanID, ((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(1));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(2));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(3));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(4));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(5));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(6));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(7));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(8));
- assertNull(((List) ((Map) ((List)
items.get("data")).get(1)).get("row")).get(9));
+ List headers = (List) items.get("columnHeaders");
+ List firstEntryValues = (List) ((Map) ((List)
items.get("data")).get(0)).get("row");
+ assertEquals("id", ((Map) headers.get(0)).get("columnName"));
+ assertEquals(1, firstEntryValues.get(0));
+ assertEquals("loan_id", ((Map) headers.get(1)).get("columnName"));
+ assertEquals(loanID, firstEntryValues.get(1));
+ assertEquals("itsABoolean", ((Map) headers.get(2)).get("columnName"));
+ assertNull(firstEntryValues.get(2));
+ assertEquals("itsADate", ((Map) headers.get(3)).get("columnName"));
+ assertNull(firstEntryValues.get(3));
+ assertEquals("itsADatetime", ((Map) headers.get(4)).get("columnName"));
+ assertNull(firstEntryValues.get(4));
+ assertEquals("itsADecimal", ((Map) headers.get(5)).get("columnName"));
+ assertNull(firstEntryValues.get(5));
+ assertEquals("TST_TST_TST_cd_itsADropdown", ((Map)
headers.get(6)).get("columnName"));
+ assertNull(firstEntryValues.get(6));
+ assertEquals("itsANumber", ((Map) headers.get(7)).get("columnName"));
+ assertNull(firstEntryValues.get(7));
+ assertEquals("itsAString", ((Map) headers.get(8)).get("columnName"));
+ assertNull(firstEntryValues.get(8));
+ assertEquals("itsAText", ((Map) headers.get(9)).get("columnName"));
+ assertNull(firstEntryValues.get(9));
+
+ List secondEntryValues = (List) ((Map) ((List)
items.get("data")).get(1)).get("row");
+ assertEquals(2, secondEntryValues.get(0));
+ assertEquals(loanID, secondEntryValues.get(1));
+ assertNull(secondEntryValues.get(2));
+ assertNull(secondEntryValues.get(3));
+ assertNull(secondEntryValues.get(4));
+ assertNull(secondEntryValues.get(5));
+ assertNull(secondEntryValues.get(6));
+ assertNull(secondEntryValues.get(7));
+ assertNull(secondEntryValues.get(8));
+ assertNull(secondEntryValues.get(9));
PutDataTablesAppTableIdDatatableIdResponse
updatedDatatableEntryResponse =
this.datatableHelper.updateDatatableEntry(datatableName,
- loanID, 1, datatableEntryRequestJsonString);
+ loanID, 1, secondEntryRequestJsonString);
assertNotNull(updatedDatatableEntryResponse);
assertEquals(0, updatedDatatableEntryResponse.getChanges().size());
}
