Aleksandar Vidakovic created FINERACT-3045:
----------------------------------------------
Summary: Type safe query: [SQL 009]
LoanProductProvisioningEntryMapper schema
(ProvisioningEntriesReadPlatformServiceImpl:70)
Key: FINERACT-3045
URL: https://issues.apache.org/jira/browse/FINERACT-3045
Project: Apache Fineract
Issue Type: Sub-task
Reporter: Aleksandar Vidakovic
*Entry 9* of the native SQL inventory \(SELECT, module fineract\-accounting\).
Source:
[ProvisioningEntriesReadPlatformServiceImpl.java#L70|https://github.com/apache/fineract/blob/0f677b99aa453ea5be4f2e0115562e85ad1db012/fineract-accounting/src/main/java/org/apache/fineract/accounting/provisioning/service/ProvisioningEntriesReadPlatformServiceImpl.java#L70]
Status: Translation generated automatically; needs review and compilation.
h3. Native SQL \(as built by the Java code\)
{code:language=sql}
select (CASE WHEN loan.loan_type_enum=1 THEN mclient.office_id ELSE
mgroup.office_id END) 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
JOIN m_loanproduct_provisioning_mapping lpm on lpm.product_id = loan.product_id
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)
{code}
h3. Proposed QueryDSL translation
{code:language=java}
// imports
import com.querydsl.core.Tuple;
import com.querydsl.core.types.dsl.CaseBuilder;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.sql.SQLExpressions;
import com.querydsl.sql.SQLQueryFactory;
import java.util.List;
import org.apache.fineract.querydsl.QMClient; // generated Q-type, see
README.txt
import org.apache.fineract.querydsl.QMGroup; // generated Q-type, see README.txt
import org.apache.fineract.querydsl.QMLoan; // generated Q-type, see README.txt
import org.apache.fineract.querydsl.QMLoanRepaymentSchedule; // generated
Q-type, see README.txt
import org.apache.fineract.querydsl.QMLoanproductProvisioningMapping; //
generated Q-type, see README.txt
import org.apache.fineract.querydsl.QMProvisioningCriteriaDefinition; //
generated Q-type, see README.txt
// method parameters used below
// Object param1
// Object param2
// Object param3
QMLoanRepaymentSchedule sch = new QMLoanRepaymentSchedule("sch");
QMLoan loan = new QMLoan("loan");
QMLoanproductProvisioningMapping lpm = new
QMLoanproductProvisioningMapping("lpm");
QMProvisioningCriteriaDefinition pcd = new
QMProvisioningCriteriaDefinition("pcd");
QMClient mclient = new QMClient("mclient");
QMGroup mgroup = new QMGroup("mgroup");
QMLoanRepaymentSchedule sch1 = new QMLoanRepaymentSchedule("sch1");
List<Tuple> result = queryFactory.select(
new CaseBuilder()
.when(loan.loanTypeEnum.eq((short) 1)).then(mclient.officeId)
.otherwise(mgroup.officeId).as("office_id"),
loan.loanTypeEnum,
pcd.criteriaId.as("criteriaid"),
loan.productId,
loan.currencyCode,
Expressions.numberTemplate(Integer.class, "GREATEST(DATEDIFF({0},
{1}), 0)", Expressions.constant(param1), sch.duedate).as("numberofdaysoverdue"),
sch.duedate,
pcd.categoryId,
pcd.provisionPercentage,
loan.totalOutstandingDerived.as("outstandingbalance"),
pcd.liabilityAccount,
pcd.expenseAccount)
.from(sch)
.leftJoin(loan).on(sch.loanId.eq(loan.id))
.innerJoin(lpm).on(lpm.productId.eq(loan.productId))
.innerJoin(pcd).on(pcd.criteriaId.eq(lpm.criteriaId)
.and(pcd.minAge.loe(Expressions.numberTemplate(Integer.class,
"GREATEST(DATEDIFF({0}, {1}), 0)", Expressions.constant(param2), sch.duedate)))
.and(Expressions.numberTemplate(Integer.class, "GREATEST(DATEDIFF({0},
{1}), 0)", Expressions.constant(param3), sch.duedate).loe(pcd.maxAge))
.and(pcd.criteriaId.isNotNull()))
.leftJoin(mclient).on(mclient.id.eq(loan.clientId))
.leftJoin(mgroup).on(mgroup.id.eq(loan.groupId))
.where(loan.loanStatusId.eq((short) 300),
sch.duedate.eq(SQLExpressions.select(sch1.duedate.min())
.from(sch1)
.where(sch1.loanId.eq(loan.id),
sch1.completedDerived.isFalse())))
.fetch();
{code}
h3. Done when
* QueryDSL version compiles against the generated Q\-types
* Behaviour matches the native query \(same rows, same parameters\) on
MySQL/MariaDB and PostgreSQL
* Existing tests pass, or a test covering the query was added
* The native SQL string is removed
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
