You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@fineract.apache.org by "AnvayKharb (via GitHub)" <gi...@apache.org> on 2026/08/01 18:43:41 UTC
[PR] fix(FINERACT-2730): add loan and savings transaction search [fineract]
AnvayKharb opened a new pull request, #6211:
URL: https://github.com/apache/fineract/pull/6211
## Description
This PR extends the global search functionality to support searching loan repayment and savings transactions.
Users can now search transactions using existing transaction identifiers without changing the current search endpoint or affecting existing search behavior.
### Supported searches
**Loan repayments**
- Transaction ID
- External ID
**Savings deposits and withdrawals**
- Transaction ID
- External ID
- Reference Number (refNo)
### Changes
- Added `loanTransactions` and `savingsTransactions` search resources.
- Extended the global search SQL to return matching loan repayment and savings transaction results.
- Added transaction metadata to the search response, including transaction ID, transaction type, account ID, account number, account type, external ID, and reference number where applicable.
- Preserved backward compatibility by adding nullable response fields without changing the existing API contract.
- Added integration tests covering transaction searches by transaction ID, external ID, and reference number.
## Related Issue
FINERACT-2730
## Screenshots
N/A
## Checklist
- [ ] If you have multiple commits, they have been squashed into a single commit.
- [ ] Read and understood the contribution guidelines.
- [ ] Added or updated tests for the implemented functionality.
- [ ] Verified that existing search functionality continues to work.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] FINERACT-2730: add loan and savings transaction search [fineract]
Posted by "adamsaghy (via GitHub)" <gi...@apache.org>.
adamsaghy commented on code in PR #6211:
URL: https://github.com/apache/fineract/pull/6211#discussion_r3705014783
##########
fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadServiceImpl.java:
##########
@@ -152,13 +163,54 @@ public String searchSchema(final SearchConditions searchConditions) {
g.id as entityId, g.display_name as entityName, \
g.external_id as entityExternalId, g.account_no as entityAccountNo, \
g.office_id as parentId, o.name as parentName, null as entityMobileNo, \
- g.status_enum as entityStatusEnum, null as subEntityType, null as parentType \
+ g.status_enum as entityStatusEnum, null as subEntityType, null as parentType, \
+ null as transactionId, null as transactionType, null as transactionExternalId, null as transactionRefNo, \
+ null as accountId, null as accountNo, null as accountType \
from m_group g join m_office o on o.id = g.office_id \
where o.hierarchy like :hierarchy \
and (g.account_no like :search or g.display_name like :search \
or g.external_id like :search)) \
order by g.id desc)""";
+ final String loanTransactionMatchSql = """
+ ( (select 'LOAN_TRANSACTION' as entityType, l.id as entityId, pl.name as entityName, \
+ lt.external_id as entityExternalId, l.account_no as entityAccountNo, \
+ coalesce(c.id,g.id) as parentId, coalesce(c.display_name,g.display_name) as parentName, \
+ null as entityMobileNo, l.loan_status_id as entityStatusEnum, null as subEntityType, \
+ CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType, \
+ lt.id as transactionId, 'repayment' as transactionType, lt.external_id as transactionExternalId, \
+ null as transactionRefNo, l.id as accountId, l.account_no as accountNo, 'loan' as accountType \
+ from m_loan_transaction lt join m_loan l on l.id = lt.loan_id \
+ left join m_client c on l.client_id = c.id \
+ left join m_group g ON l.group_id = g.id \
+ left join m_office o on o.id = coalesce(c.office_id, g.office_id) \
+ left join m_product_loan pl on pl.id=l.product_id \
+ where o.hierarchy like :hierarchy \
+ and lt.transaction_type_enum = 2 \
Review Comment:
Why is there a hardcoded filter for the **repayment** type?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] FINERACT-2730: add loan and savings transaction search [fineract]
Posted by "IOhacker (via GitHub)" <gi...@apache.org>.
IOhacker merged PR #6211:
URL: https://github.com/apache/fineract/pull/6211
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] FINERACT-2730: add loan and savings transaction search [fineract]
Posted by "AnvayKharb (via GitHub)" <gi...@apache.org>.
AnvayKharb commented on code in PR #6211:
URL: https://github.com/apache/fineract/pull/6211#discussion_r3705950203
##########
fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadServiceImpl.java:
##########
@@ -152,13 +163,54 @@ public String searchSchema(final SearchConditions searchConditions) {
g.id as entityId, g.display_name as entityName, \
g.external_id as entityExternalId, g.account_no as entityAccountNo, \
g.office_id as parentId, o.name as parentName, null as entityMobileNo, \
- g.status_enum as entityStatusEnum, null as subEntityType, null as parentType \
+ g.status_enum as entityStatusEnum, null as subEntityType, null as parentType, \
+ null as transactionId, null as transactionType, null as transactionExternalId, null as transactionRefNo, \
+ null as accountId, null as accountNo, null as accountType \
from m_group g join m_office o on o.id = g.office_id \
where o.hierarchy like :hierarchy \
and (g.account_no like :search or g.display_name like :search \
or g.external_id like :search)) \
order by g.id desc)""";
+ final String loanTransactionMatchSql = """
+ ( (select 'LOAN_TRANSACTION' as entityType, l.id as entityId, pl.name as entityName, \
+ lt.external_id as entityExternalId, l.account_no as entityAccountNo, \
+ coalesce(c.id,g.id) as parentId, coalesce(c.display_name,g.display_name) as parentName, \
+ null as entityMobileNo, l.loan_status_id as entityStatusEnum, null as subEntityType, \
+ CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType, \
+ lt.id as transactionId, 'repayment' as transactionType, lt.external_id as transactionExternalId, \
+ null as transactionRefNo, l.id as accountId, l.account_no as accountNo, 'loan' as accountType \
+ from m_loan_transaction lt join m_loan l on l.id = lt.loan_id \
+ left join m_client c on l.client_id = c.id \
+ left join m_group g ON l.group_id = g.id \
+ left join m_office o on o.id = coalesce(c.office_id, g.office_id) \
+ left join m_product_loan pl on pl.id=l.product_id \
+ where o.hierarchy like :hierarchy \
+ and lt.transaction_type_enum = 2 \
Review Comment:
Hi @adamsaghy Thanks for pointing this out. I’ve updated the search SQL to avoid hardcoded transaction type enum values.
The loan transaction search now uses `LoanTransactionType.REPAYMENT`, and savings transaction search uses `SavingsAccountTransactionType.DEPOSIT` and `SavingsAccountTransactionType.WITHDRAWAL` as named SQL parameters.
The search scope is unchanged: loan repayments, savings deposits, and savings withdrawals only.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] FINERACT-2730: add loan and savings transaction search [fineract]
Posted by "AnvayKharb (via GitHub)" <gi...@apache.org>.
AnvayKharb commented on code in PR #6211:
URL: https://github.com/apache/fineract/pull/6211#discussion_r3706689135
##########
fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadServiceImpl.java:
##########
@@ -152,13 +163,54 @@ public String searchSchema(final SearchConditions searchConditions) {
g.id as entityId, g.display_name as entityName, \
g.external_id as entityExternalId, g.account_no as entityAccountNo, \
g.office_id as parentId, o.name as parentName, null as entityMobileNo, \
- g.status_enum as entityStatusEnum, null as subEntityType, null as parentType \
+ g.status_enum as entityStatusEnum, null as subEntityType, null as parentType, \
+ null as transactionId, null as transactionType, null as transactionExternalId, null as transactionRefNo, \
+ null as accountId, null as accountNo, null as accountType \
from m_group g join m_office o on o.id = g.office_id \
where o.hierarchy like :hierarchy \
and (g.account_no like :search or g.display_name like :search \
or g.external_id like :search)) \
order by g.id desc)""";
+ final String loanTransactionMatchSql = """
+ ( (select 'LOAN_TRANSACTION' as entityType, l.id as entityId, pl.name as entityName, \
+ lt.external_id as entityExternalId, l.account_no as entityAccountNo, \
+ coalesce(c.id,g.id) as parentId, coalesce(c.display_name,g.display_name) as parentName, \
+ null as entityMobileNo, l.loan_status_id as entityStatusEnum, null as subEntityType, \
+ CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType, \
+ lt.id as transactionId, 'repayment' as transactionType, lt.external_id as transactionExternalId, \
+ null as transactionRefNo, l.id as accountId, l.account_no as accountNo, 'loan' as accountType \
+ from m_loan_transaction lt join m_loan l on l.id = lt.loan_id \
+ left join m_client c on l.client_id = c.id \
+ left join m_group g ON l.group_id = g.id \
+ left join m_office o on o.id = coalesce(c.office_id, g.office_id) \
+ left join m_product_loan pl on pl.id=l.product_id \
+ where o.hierarchy like :hierarchy \
+ and lt.transaction_type_enum = 2 \
Review Comment:
Hi @adamsaghy , I kept this filter because the current scope is limited to loan repayments, savings deposits, and savings withdrawals. If you'd prefer it to cover all transaction types, I can update it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] FINERACT-2730: add loan and savings transaction search [fineract]
Posted by "adamsaghy (via GitHub)" <gi...@apache.org>.
adamsaghy commented on code in PR #6211:
URL: https://github.com/apache/fineract/pull/6211#discussion_r3705017625
##########
fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadServiceImpl.java:
##########
@@ -152,13 +163,54 @@ public String searchSchema(final SearchConditions searchConditions) {
g.id as entityId, g.display_name as entityName, \
g.external_id as entityExternalId, g.account_no as entityAccountNo, \
g.office_id as parentId, o.name as parentName, null as entityMobileNo, \
- g.status_enum as entityStatusEnum, null as subEntityType, null as parentType \
+ g.status_enum as entityStatusEnum, null as subEntityType, null as parentType, \
+ null as transactionId, null as transactionType, null as transactionExternalId, null as transactionRefNo, \
+ null as accountId, null as accountNo, null as accountType \
from m_group g join m_office o on o.id = g.office_id \
where o.hierarchy like :hierarchy \
and (g.account_no like :search or g.display_name like :search \
or g.external_id like :search)) \
order by g.id desc)""";
+ final String loanTransactionMatchSql = """
+ ( (select 'LOAN_TRANSACTION' as entityType, l.id as entityId, pl.name as entityName, \
+ lt.external_id as entityExternalId, l.account_no as entityAccountNo, \
+ coalesce(c.id,g.id) as parentId, coalesce(c.display_name,g.display_name) as parentName, \
+ null as entityMobileNo, l.loan_status_id as entityStatusEnum, null as subEntityType, \
+ CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType, \
+ lt.id as transactionId, 'repayment' as transactionType, lt.external_id as transactionExternalId, \
+ null as transactionRefNo, l.id as accountId, l.account_no as accountNo, 'loan' as accountType \
+ from m_loan_transaction lt join m_loan l on l.id = lt.loan_id \
+ left join m_client c on l.client_id = c.id \
+ left join m_group g ON l.group_id = g.id \
+ left join m_office o on o.id = coalesce(c.office_id, g.office_id) \
+ left join m_product_loan pl on pl.id=l.product_id \
+ where o.hierarchy like :hierarchy \
+ and lt.transaction_type_enum = 2 \
+ and (lt.id = :searchTransactionId or lt.external_id like :search)) \
+ order by lt.id desc)""";
+
+ final String savingTransactionMatchSql = """
+ ( (select 'SAVINGS_TRANSACTION' as entityType, s.id as entityId, sp.name as entityName, \
+ st.external_id as entityExternalId, s.account_no as entityAccountNo, \
+ coalesce(c.id,g.id) as parentId, coalesce(c.display_name, g.display_name) as parentName, \
+ null as entityMobileNo, s.status_enum as entityStatusEnum, \
+ concat(s.deposit_type_enum, '') as subEntityType, \
+ CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType, \
+ st.id as transactionId, CASE WHEN st.transaction_type_enum = 1 THEN 'deposit' ELSE 'withdrawal' END as transactionType, \
+ st.external_id as transactionExternalId, st.ref_no as transactionRefNo, \
+ s.id as accountId, s.account_no as accountNo, 'savings' as accountType \
+ from m_savings_account_transaction st join m_savings_account s on s.id = st.savings_account_id \
+ left join m_client c on s.client_id = c.id \
+ left join m_group g ON s.group_id = g.id \
+ left join m_office o on o.id = coalesce(c.office_id, g.office_id) \
+ left join m_savings_product sp on sp.id=s.product_id \
+ where o.hierarchy like :hierarchy \
+ and st.transaction_type_enum in (1, 2) \
Review Comment:
Why is there a hardcoded filter for the **Deposit and Withdrawal** type?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] FINERACT-2730: add loan and savings transaction search [fineract]
Posted by "AnvayKharb (via GitHub)" <gi...@apache.org>.
AnvayKharb commented on code in PR #6211:
URL: https://github.com/apache/fineract/pull/6211#discussion_r3706689135
##########
fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadServiceImpl.java:
##########
@@ -152,13 +163,54 @@ public String searchSchema(final SearchConditions searchConditions) {
g.id as entityId, g.display_name as entityName, \
g.external_id as entityExternalId, g.account_no as entityAccountNo, \
g.office_id as parentId, o.name as parentName, null as entityMobileNo, \
- g.status_enum as entityStatusEnum, null as subEntityType, null as parentType \
+ g.status_enum as entityStatusEnum, null as subEntityType, null as parentType, \
+ null as transactionId, null as transactionType, null as transactionExternalId, null as transactionRefNo, \
+ null as accountId, null as accountNo, null as accountType \
from m_group g join m_office o on o.id = g.office_id \
where o.hierarchy like :hierarchy \
and (g.account_no like :search or g.display_name like :search \
or g.external_id like :search)) \
order by g.id desc)""";
+ final String loanTransactionMatchSql = """
+ ( (select 'LOAN_TRANSACTION' as entityType, l.id as entityId, pl.name as entityName, \
+ lt.external_id as entityExternalId, l.account_no as entityAccountNo, \
+ coalesce(c.id,g.id) as parentId, coalesce(c.display_name,g.display_name) as parentName, \
+ null as entityMobileNo, l.loan_status_id as entityStatusEnum, null as subEntityType, \
+ CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType, \
+ lt.id as transactionId, 'repayment' as transactionType, lt.external_id as transactionExternalId, \
+ null as transactionRefNo, l.id as accountId, l.account_no as accountNo, 'loan' as accountType \
+ from m_loan_transaction lt join m_loan l on l.id = lt.loan_id \
+ left join m_client c on l.client_id = c.id \
+ left join m_group g ON l.group_id = g.id \
+ left join m_office o on o.id = coalesce(c.office_id, g.office_id) \
+ left join m_product_loan pl on pl.id=l.product_id \
+ where o.hierarchy like :hierarchy \
+ and lt.transaction_type_enum = 2 \
Review Comment:
@adamsaghy I kept this filter because the current scope is limited to loan repayments, savings deposits, and savings withdrawals. If you'd prefer it to cover all transaction types, I can update it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] FINERACT-2730: add loan and savings transaction search [fineract]
Posted by "adamsaghy (via GitHub)" <gi...@apache.org>.
adamsaghy commented on code in PR #6211:
URL: https://github.com/apache/fineract/pull/6211#discussion_r3706169455
##########
fineract-provider/src/main/java/org/apache/fineract/portfolio/search/service/SearchReadServiceImpl.java:
##########
@@ -152,13 +163,54 @@ public String searchSchema(final SearchConditions searchConditions) {
g.id as entityId, g.display_name as entityName, \
g.external_id as entityExternalId, g.account_no as entityAccountNo, \
g.office_id as parentId, o.name as parentName, null as entityMobileNo, \
- g.status_enum as entityStatusEnum, null as subEntityType, null as parentType \
+ g.status_enum as entityStatusEnum, null as subEntityType, null as parentType, \
+ null as transactionId, null as transactionType, null as transactionExternalId, null as transactionRefNo, \
+ null as accountId, null as accountNo, null as accountType \
from m_group g join m_office o on o.id = g.office_id \
where o.hierarchy like :hierarchy \
and (g.account_no like :search or g.display_name like :search \
or g.external_id like :search)) \
order by g.id desc)""";
+ final String loanTransactionMatchSql = """
+ ( (select 'LOAN_TRANSACTION' as entityType, l.id as entityId, pl.name as entityName, \
+ lt.external_id as entityExternalId, l.account_no as entityAccountNo, \
+ coalesce(c.id,g.id) as parentId, coalesce(c.display_name,g.display_name) as parentName, \
+ null as entityMobileNo, l.loan_status_id as entityStatusEnum, null as subEntityType, \
+ CASE WHEN g.id is null THEN 'client' ELSE 'group' END as parentType, \
+ lt.id as transactionId, 'repayment' as transactionType, lt.external_id as transactionExternalId, \
+ null as transactionRefNo, l.id as accountId, l.account_no as accountNo, 'loan' as accountType \
+ from m_loan_transaction lt join m_loan l on l.id = lt.loan_id \
+ left join m_client c on l.client_id = c.id \
+ left join m_group g ON l.group_id = g.id \
+ left join m_office o on o.id = coalesce(c.office_id, g.office_id) \
+ left join m_product_loan pl on pl.id=l.product_id \
+ where o.hierarchy like :hierarchy \
+ and lt.transaction_type_enum = 2 \
Review Comment:
Why to filter on these types?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@fineract.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org