Aman-Mittal commented on code in PR #5410:
URL: https://github.com/apache/fineract/pull/5410#discussion_r2749597944
##########
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:
Current implementation performs two queries and scans m_loan twice via
correlated subqueries. This can be optimized into a single LEFT JOIN +
aggregate, avoiding redundant DB round trips and duplicate table scans.
Why this is important? because it can degrade the performance if client have
huge amount of data,
It is also recommended that indexes are maintained before using this type of
aggregation.
--
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]
