[
https://issues.apache.org/jira/browse/FINERACT-1915?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750237#comment-17750237
]
Peter Santa commented on FINERACT-1915:
---------------------------------------
Márta Jankovics
yesterday
*POST savingsaccounts/<savings id>/transactions/query*
Example uri:
{\{url}}/savingsaccounts/1/transactions/query
Request body: PagedRequest<{{{}AdvancedQueryRequest{}}}>
dateFormat, locale are not supported, but tenant locale and ISO patterns
{{{}yyyy-MM-dd{}}}, {{yyyy-MM-dd HH:mm:ss}} are used
Camel case is supported for column names.
{{{}baseQuery{}}}, {{{}datatableQueries{}}}, {{columnFilters}} are optional,
but {{column}} is mandatory for a filter, {{values}} can be null depending on
the operator ({_}NULL{_}, _NNULL_ do not accept values, {_}BTW{_}, _NBTW_
require two parameters, IN, _NIN_ accepts minimum 1 parameter(s), other
operators require 1 parameter)
{{{}resultColumns{}}}, {{{}page{}}}, {{{}size{}}}, {{sorts}} are optional but
sort {{property}} is mandatory
Result columns can be added from the base table and from each datatable. In the
response, these will be listed without indicating which table the columns
belong.
NOTE: do not add column to the data tables with the same name.
operators:
{_}EQ{_}("="),
{_}NEQ{_}("<>"),
{_}GTE{_}(">="),
{_}LTE{_}("<="),
{_}GT{_}(">"),
{_}LT{_}("<"),
{_}LIKE{_}("LIKE")
{_}NLIKE{_}("NOT LIKE")
{_}BTW{_}("BETWEEN")
{_}NBTW{_}("NOT BETWEEN")
{_}IN{_}("IN")
{_}NIN{_}("NOT IN")
{_}NULL{_}("IS NULL")
{_}NNULL{_}("IS NOT NULL")
Example request:
{code:json}
{
"request" :
{
"baseQuery": {
"columnFilters": [
{
"column": "transactionTypeEnum",
"filters": [
{
"operator": "IN",
"values": [1,2,3,4]
}
]
},
{
"column": "amount",
"filters": [
{
"operator": "BTW",
"values": ["1000", "3000"]
}
]
},
{
"column": "isReversed",
"filters": [
{
"operator": "EQ",
"values": ["false"]
}
]
},
{
"column": "submittedOnDate",
"filters": [
{
"operator": "GT",
"values": ["2023-07-25"]
}
]
}
],
"resultColumns": ["id", "transactionTypeEnum", "submittedOnDate"]
},
"datatableQueries": [
{
"table": "dt_savings_transaction_1",
"query": {
"columnFilters": [
{
"column": "partnerName",
"filters": [
{
"operator": "LIKE",
"values": ["art"]
}
]
},
{
"column": "bankTransactionCode",
"filters": [
{
"operator": "LIKE",
"values": ["123"]
}
]
},
{
"column": "savingsTransactionId",
"filters": [
{
"operator": "GTE",
"values": ["1"]
}
]
},
{
"column": "createdAt",
"filters": [
{
"operator": "BTW",
"values": ["2023-07-26 00:00:00", "2023-07-27
00:00:00"]
},
{
"operator": "LT",
"values": ["2023-07-27 00:00:00"]
}
]
}
],
"resultColumns": ["partnerAccountId", "createdAt"]
}
}]
},
"dateFormat": "yyyy-MM-dd",
"locale": "en",
"page": 0,
"size": 100,
"sorts": [
{
"property": "id",
"direction": "ASC"
},
{
"property": "submittedOnDate",
"direction": "ASC"
},
{
"property": "transactionDate",
"direction": "ASC"
}
]
}
{code}
Response:
Pageable list of requested result columns, defaults to the primary key column.
The result is sorted by the sorts columns, defaults to the primary key column.
Example response:
{code:json}
{
"total": 2,
"content": [
{
"id": 1,
"transactionTypeEnum": 1,
"submittedOnDate": "2023-07-26",
"partnerAccountId": 1234,
"createdAt": "2023-07-26 23:14:12"
},
{
"id": 2,
"transactionTypeEnum": 2,
"submittedOnDate": "2023-07-26",
"partnerAccountId": 12345,
"createdAt": "2023-07-26 23:14:16"
}
],
"pageable": {
"sort": {
"orders": [
{
"direction": "ASC",
"property": "id",
"ignoreCase": false,
"nullHandling": "NATIVE"
},
{
"direction": "ASC",
"property": "submittedOnDate",
"ignoreCase": false,
"nullHandling": "NATIVE"
},
{
"direction": "ASC",
"property": "transactionDate",
"ignoreCase": false,
"nullHandling": "NATIVE"
}
]
},
"page": 0,
"size": 100
}
}
{code}
> Transaction query - considering assigned data table
> ---------------------------------------------------
>
> Key: FINERACT-1915
> URL: https://issues.apache.org/jira/browse/FINERACT-1915
> Project: Apache Fineract
> Issue Type: New Feature
> Components: Savings
> Reporter: Peter Santa
> Priority: Major
> Labels: BeanSalad
>
> h1. Prerequisites
> FINERACT-1911
> FINERACT-1910
> h1. Goal
> Make it possible to filter *Transactions* of Savings account with one
> request, that includes conditions for
> * attributes of the Transaction itself,
> * attributes of the related data tables
> The filtering parameters should be applied with "AND" relation.
> The *result set should contain* data from the *assigned data table* besides
> the Transaction attributes, considering the requested attributes.
> h1. Solution Concept
> Have the solution concept aligned between
> * FINERACT-1910
> * FINERACT-1912
> * FINERACT-1915
> For providing the query parameters, have a similar solution to FIENRACT-1910
> , but let the caller specify sections in the body for each related data
> table, specifying the identifier of the related data table.
> Include the requested attributes in the response from the associated data
> table.
> <Solution Concept to be extended by developers>
> h1. Notes
> Requirements for enhanced result set is included in FINERACT-1916, it might
> worth to consider.
> h1. Acceptance Criteria
> * It is supported to query for {*}transactions{*}, that meets the defined
> conditions, and the assigned data table records meets the defined conditions.
> * The conditions in the request are defined on a way as the prerequisite
> FINERACT-1910 does, and additionally to that it lets the caller specify
> sections in the body for each related data table, specifying its name.
> * The result set contains the requested *attributes from the assigned data
> tables* also.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
