nidhiii128 commented on code in PR #5410:
URL: https://github.com/apache/fineract/pull/5410#discussion_r2749832981
##########
fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientReadPlatformServiceImpl.java:
##########
@@ -601,6 +602,31 @@ public Long retrieveClientIdByExternalId(final ExternalId
externalId) {
return clientRepositoryWrapper.findIdByExternalId(externalId);
}
+ @Override
+ public ClientPerformanceData retrieveClientPerformance(final Long
clientId) {
+ try {
+ final String checkClientSql = "SELECT count(*) FROM m_client WHERE
id = ?";
+ final Integer count =
this.jdbcTemplate.queryForObject(checkClientSql, Integer.class, clientId);
+ if (count == null || count == 0) {
+ throw new ClientNotFoundException(clientId);
+ }
+ final String sql = """
+ SELECT
+ (SELECT COUNT(*) FROM m_loan WHERE client_id = c.id
AND loan_status_id = 300) as activeLoans,
+ (SELECT COALESCE(SUM(principal_outstanding_derived +
interest_outstanding_derived + fee_charges_outstanding_derived +
penalty_charges_outstanding_derived), 0)
+ FROM m_loan WHERE client_id = c.id AND loan_status_id
= 300) as totalOutstandingBalance
+ FROM m_client c WHERE c.id = ?
+ """;
+ return this.jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
+ final Integer activeLoans = rs.getInt("activeLoans");
+ BigDecimal totalOutstandingBalance =
rs.getBigDecimal("totalOutstandingBalance");
+ return ClientPerformanceData.instance(activeLoans,
totalOutstandingBalance);
+ }, clientId);
+ } catch (EmptyResultDataAccessException e) {
+ throw new ClientNotFoundException(clientId,e);
+ }
+ }
Review Comment:
@Aman-Mittal , thank you for the feedback. I have updated the implementation
with the following optimizations:
1. Rewrote the logic to use a single LEFT JOIN with GROUP BY. This avoids
redundant database round trips and eliminates the duplicate scans of the m_loan
table.
2. Added a Liquibase migration
(0209_add_index_for_client_performance_api.xml) to create a compound index on
m_loan(client_id, loan_status_id). This ensures the query remains performant
even for clients with a large number of loan accounts. ( taking reference from
the existing implementations in codebase)
Refined the exception handling to use EmptyResultDataAccessException for
detecting missing clients while preserving the ClientNotFoundException for API
consistency.
Verified the changes locally with Checkstyle and compilation.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
