josemakara2 commented on a change in pull request #1671:
URL: https://github.com/apache/fineract/pull/1671#discussion_r611352674
##########
File path:
fineract-provider/src/main/java/org/apache/fineract/accounting/provisioning/service/ProvisioningEntriesReadPlatformServiceImpl.java
##########
@@ -57,28 +57,27 @@ public ProvisioningEntriesReadPlatformServiceImpl(final
RoutingDataSource dataSo
@Override
public Collection<LoanProductProvisioningEntryData>
retrieveLoanProductsProvisioningData(Date date) {
String formattedDate = new SimpleDateFormat("yyyy-MM-dd").format(date);
- formattedDate = "'" + formattedDate + "'";
- LoanProductProvisioningEntryMapper mapper = new
LoanProductProvisioningEntryMapper(formattedDate);
+ LoanProductProvisioningEntryMapper mapper = new
LoanProductProvisioningEntryMapper();
final String sql = mapper.schema();
- return this.jdbcTemplate.query(sql, mapper, new Object[] {});
+ return this.jdbcTemplate.query(sql, mapper, new Object[] {
formattedDate, formattedDate, formattedDate });
}
private static final class LoanProductProvisioningEntryMapper implements
RowMapper<LoanProductProvisioningEntryData> {
private final StringBuilder sqlQuery;
- private LoanProductProvisioningEntryMapper(String formattedDate) {
+ private LoanProductProvisioningEntryMapper() {
sqlQuery = new StringBuilder().append(
"select if(loan.loan_type_enum=1, mclient.office_id,
mgroup.office_id) as office_id, loan.loan_type_enum, pcd.criteria_id as
criteriaid, loan.product_id,loan.currency_code,")
- .append("GREATEST(datediff(").append(formattedDate)
+ .append("GREATEST(datediff(?")
.append(",sch.duedate),0) as
numberofdaysoverdue,sch.duedate, pcd.category_id, pcd.provision_percentage,")
.append("loan.total_outstanding_derived as
outstandingbalance, pcd.liability_account, pcd.expense_account from
m_loan_repayment_schedule sch")
.append(" LEFT JOIN m_loan loan on sch.loan_id = loan.id")
.append(" JOIN m_loanproduct_provisioning_mapping lpm on
lpm.product_id = loan.product_id")
.append(" JOIN m_provisioning_criteria_definition pcd on
pcd.criteria_id = lpm.criteria_id and ")
- .append("(pcd.min_age <=
GREATEST(datediff(").append(formattedDate).append(",sch.duedate),0) and ")
-
.append("GREATEST(datediff(").append(formattedDate).append(",sch.duedate),0) <=
pcd.max_age) and ")
- .append("pcd.criteria_id is not null ").append("LEFT JOIN
m_client mclient ON mclient.id = loan.client_id ")
+ .append("(pcd.min_age <=
GREATEST(datediff(?").append(",sch.duedate),0) and
").append("GREATEST(datediff(?")
Review comment:
Thank Petri.
:innocent:
It wasn't in first review but if it improves the code base why not
:slightly_smiling_face:. Will add fix tonight when I get to my laptop with this
project.
There are other things there which I didn't bother with initially but can
improve readability. No change to functionality but helps understand things.
Prefer
- lowerCamel case for SQL functions, and uppercase for other keywords
- lowerCamel case for column names - `dueDate` not `duedate`
- Only break line if it violates 120 character line limit in the FINERACT
eclipse formatter
- `StringBuilder` only if it is a conditional query, with if conditions,
otherwise just a single query as _below_ suffice.
Simply return
```
public String schema() {
return "" +
"SELECT if(" +
" loan.loan_type_enum = 1, mclient.office_id,
mgroup.office_id" +
" ) AS office_id," +
" loan.loan_type_enum," +
" pcd.criteria_id AS criteriaid," +
" loan.product_id," +
" loan.currency_code," +
" greatest(dateDiff(?, sch.duedate), 0) AS
numberofdaysoverdue," +
" sch.duedate," +
" pcd.category_id," +
" pcd.provision_percentage," +
" loan.total_outstanding_derived AS
outstandingbalance," +
" pcd.liability_account," +
" pcd.expense_account " +
"FROM m_loan_repayment_schedule sch " +
"LEFT JOIN m_loan loan ON sch.loan_id = loan.id " +
"INNER JOIN m_loanproduct_provisioning_mapping lpm ON
lpm.product_id = loan.product_id " +
"INNER JOIN m_provisioning_criteria_definition pcd " +
" ON pcd.criteria_id = lpm.criteria_id " +
" AND (" +
" pcd.min_age <= greatest(dateDiff(?, sch.duedate),
0) AND greatest(dateDiff(?, sch.duedate), 0) <= pcd.max_age" +
" )" +
" AND pcd.criteria_id IS NOT NULL" +
"LEFT JOIN m_client mclient ON mclient.id =
loan.client_id " +
"LEFT JOIN m_group mgroup ON mgroup.id = loan.group_id "
+
"WHERE loan.loan_status_id = 300 AND sch.duedate = (" +
" SELECT min(sch1.duedate) " +
" FROM m_loan_repayment_schedule sch1 " +
" WHERE sch1.loan_id=loan.id AND sch1.completed_derived
= FALSE" +
")";
}
```
But `fineractdevprojectformatter` won't allows this :grimacing: and instead
its formats is not SQL readable.
as opposed to
```
sqlQuery = new StringBuilder().append(
"select if(loan.loan_type_enum=1, mclient.office_id,
mgroup.office_id) as office_id, loan.loan_type_enum, pcd.criteria_id as
criteriaid, loan.product_id,loan.currency_code,")
.append("GREATEST(datediff(?")
.append(",sch.duedate),0) as
numberofdaysoverdue,sch.duedate, pcd.category_id, pcd.provision_percentage,")
.append("loan.total_outstanding_derived as
outstandingbalance, pcd.liability_account, pcd.expense_account from
m_loan_repayment_schedule sch")
.append(" LEFT JOIN m_loan loan on sch.loan_id =
loan.id")
.append(" JOIN m_loanproduct_provisioning_mapping lpm on
lpm.product_id = loan.product_id")
.append(" JOIN m_provisioning_criteria_definition pcd on
pcd.criteria_id = lpm.criteria_id and ")
.append("(pcd.min_age <=
GREATEST(datediff(?,sch.duedate),0) and GREATEST(datediff(?")
.append(",sch.duedate),0) <= pcd.max_age) and
").append("pcd.criteria_id is not null ")
.append("LEFT JOIN m_client mclient ON mclient.id =
loan.client_id ")
.append("LEFT JOIN m_group mgroup ON mgroup.id =
loan.group_id ")
.append("where loan.loan_status_id=300 and sch.duedate =
")
.append("(select MIN(sch1.duedate) from
m_loan_repayment_schedule sch1 where sch1.loan_id=loan.id and
sch1.completed_derived=false)");
```
We can open a separate jira vote to fix formatter?
--
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.
For queries about this service, please contact Infrastructure at:
[email protected]
