This is an automated email from the ASF dual-hosted git repository.
aleks pushed a commit to branch develop
in repository https://gitbox.apache.org/repos/asf/fineract.git
The following commit(s) were added to refs/heads/develop by this push:
new 3472918 FINERACT-984-5: More database independence
3472918 is described below
commit 34729186d799da770a0b7a6ed90d6f7661b4fb26
Author: Arnold Galovics <[email protected]>
AuthorDate: Mon Feb 21 14:37:10 2022 +0100
FINERACT-984-5: More database independence
---
.../PortfolioAccountReadPlatformServiceImpl.java | 16 +-
...tandingInstructionWritePlatformServiceImpl.java | 2 +-
...etailsReadPlatformServiceJpaRepositoryImpl.java | 10 +-
.../service/ChargeReadPlatformServiceImpl.java | 3 +-
...hargeWritePlatformServiceJpaRepositoryImpl.java | 12 +-
.../ClientMustBePendingToBeDeletedException.java | 2 +-
.../ClientChargeReadPlatformServiceImpl.java | 1 +
...elfBeneficiariesTPTReadPlatformServiceImpl.java | 2 +-
.../service/ScheduledJobRunnerServiceImpl.java | 214 +++++++++++----------
9 files changed, 137 insertions(+), 125 deletions(-)
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/PortfolioAccountReadPlatformServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/PortfolioAccountReadPlatformServiceImpl.java
index 1c7c0d1..28ccc9a 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/PortfolioAccountReadPlatformServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/PortfolioAccountReadPlatformServiceImpl.java
@@ -298,17 +298,17 @@ public class PortfolioAccountReadPlatformServiceImpl
implements PortfolioAccount
PortfolioLoanAccountRefundByTransferMapper() {
final StringBuilder amountQueryString = new StringBuilder(400);
- amountQueryString.append("(select
(SUM(ifnull(mr.principal_completed_derived, 0)) +");
-
amountQueryString.append("SUM(ifnull(mr.interest_completed_derived, 0)) + ");
-
amountQueryString.append("SUM(ifnull(mr.fee_charges_completed_derived, 0)) + ");
- amountQueryString.append("
SUM(ifnull(mr.penalty_charges_completed_derived, 0))) as
total_in_advance_derived");
+ amountQueryString.append("(select
(SUM(COALESCE(mr.principal_completed_derived, 0)) +");
+
amountQueryString.append("SUM(COALESCE(mr.interest_completed_derived, 0)) + ");
+
amountQueryString.append("SUM(COALESCE(mr.fee_charges_completed_derived, 0)) +
");
+ amountQueryString.append("
SUM(COALESCE(mr.penalty_charges_completed_derived, 0))) as
total_in_advance_derived");
amountQueryString.append(" from m_loan ml INNER JOIN
m_loan_repayment_schedule mr on mr.loan_id = ml.id");
amountQueryString.append(" where ml.id=? and ml.loan_status_id =
300");
amountQueryString.append(" and mr.duedate >= CURDATE() group by
ml.id having");
- amountQueryString.append("
(SUM(ifnull(mr.principal_completed_derived, 0)) + ");
- amountQueryString.append("
SUM(ifnull(mr.interest_completed_derived, 0)) + ");
-
amountQueryString.append("SUM(ifnull(mr.fee_charges_completed_derived, 0)) + ");
-
amountQueryString.append("SUM(ifnull(mr.penalty_charges_completed_derived, 0)))
> 0) as totalOverpaid ");
+ amountQueryString.append("
(SUM(COALESCE(mr.principal_completed_derived, 0)) + ");
+ amountQueryString.append("
SUM(COALESCE(mr.interest_completed_derived, 0)) + ");
+
amountQueryString.append("SUM(COALESCE(mr.fee_charges_completed_derived, 0)) +
");
+
amountQueryString.append("SUM(COALESCE(mr.penalty_charges_completed_derived,
0))) > 0) as totalOverpaid ");
final StringBuilder sqlBuilder = new StringBuilder(400);
sqlBuilder.append("la.id as id, la.account_no as accountNo,
la.external_id as externalId, ");
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionWritePlatformServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionWritePlatformServiceImpl.java
index 137c654..16919cf 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionWritePlatformServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/account/service/StandingInstructionWritePlatformServiceImpl.java
@@ -261,7 +261,7 @@ public class StandingInstructionWritePlatformServiceImpl
implements StandingInst
boolean transferCompleted = true;
StringBuilder errorLog = new StringBuilder();
StringBuilder updateQuery = new StringBuilder(
- "INSERT INTO
`m_account_transfer_standing_instructions_history` (`standing_instruction_id`,
`status`, `amount`,`execution_time`, `error_log`) VALUES (");
+ "INSERT INTO m_account_transfer_standing_instructions_history
(standing_instruction_id, `status`, amount,execution_time, error_log) VALUES
(");
try {
this.accountTransfersWritePlatformService.transferFunds(accountTransferDTO);
} catch (final PlatformApiDataValidationException e) {
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/accountdetails/service/AccountDetailsReadPlatformServiceJpaRepositoryImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/accountdetails/service/AccountDetailsReadPlatformServiceJpaRepositoryImpl.java
index 57d5199..4cad313 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/accountdetails/service/AccountDetailsReadPlatformServiceJpaRepositoryImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/accountdetails/service/AccountDetailsReadPlatformServiceJpaRepositoryImpl.java
@@ -81,7 +81,7 @@ public class
AccountDetailsReadPlatformServiceJpaRepositoryImpl implements Accou
// currently handles loans of exisiting types.
final String savingswhereClause = " where sa.client_id = ? order by
sa.status_enum ASC, sa.account_no ASC";
- final String guarantorWhereClause = " where g.entity_id = ? and
g.is_active = 1 order by l.account_no ASC";
+ final String guarantorWhereClause = " where g.entity_id = ? and
g.is_active = true order by l.account_no ASC";
final List<LoanAccountSummaryData> glimAccounts =
retrieveLoanAccountDetails(glimLoanClause, new Object[] { clientId });
final List<LoanAccountSummaryData> loanAccounts =
retrieveLoanAccountDetails(loanwhereClause, new Object[] { clientId });
@@ -102,8 +102,8 @@ public class
AccountDetailsReadPlatformServiceJpaRepositoryImpl implements Accou
final String savingswhereClauseForGroup = " where sa.group_id = ? and
sa.client_id is null order by sa.status_enum ASC, sa.account_no ASC";
final String savingswhereClauseForMembers = " where sa.group_id = ?
and sa.client_id is not null order by sa.status_enum ASC, sa.account_no ASC";
- final String guarantorWhereClauseForGroup = " where l.group_id = ? and
l.client_id is null and g.is_active = 1 order by l.account_no ASC";
- final String guarantorWhereClauseForMembers = " where l.group_id = ?
and l.client_id is not null and g.is_active = 1 order by l.account_no ASC";
+ final String guarantorWhereClauseForGroup = " where l.group_id = ? and
l.client_id is null and g.is_active = true order by l.account_no ASC";
+ final String guarantorWhereClauseForMembers = " where l.group_id = ?
and l.client_id is not null and g.is_active = true order by l.account_no ASC";
final List<LoanAccountSummaryData> glimAccounts =
retrieveLoanAccountDetails(loanWhereClauseForGroupAndLoanType,
new Object[] { groupId });
@@ -345,9 +345,9 @@ public class
AccountDetailsReadPlatformServiceJpaRepositoryImpl implements Accou
accountsSummary.append("avbu.firstname as activatedByFirstname,
avbu.lastname as activatedByLastname,");
accountsSummary.append("sa.sub_status_enum as subStatusEnum, ");
- accountsSummary.append("(select
IFNULL(max(sat.transaction_date),sa.activatedon_date) ");
+ accountsSummary.append("(select
coalesce(max(sat.transaction_date),sa.activatedon_date) ");
accountsSummary.append("from m_savings_account_transaction as sat
");
- accountsSummary.append("where sat.is_reversed = 0 ");
+ accountsSummary.append("where sat.is_reversed = false ");
accountsSummary.append("and sat.transaction_type_enum in (1,2) ");
accountsSummary.append("and sat.savings_account_id = sa.id) as
lastActiveTransactionDate, ");
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/charge/service/ChargeReadPlatformServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/charge/service/ChargeReadPlatformServiceImpl.java
index cf9ab4e..116987f 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/charge/service/ChargeReadPlatformServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/charge/service/ChargeReadPlatformServiceImpl.java
@@ -425,7 +425,8 @@ public class ChargeReadPlatformServiceImpl implements
ChargeReadPlatformService
public Collection<ChargeData> retrieveSavingsProductCharges(final Long
savingsProductId) {
final ChargeMapper rm = new ChargeMapper();
- String sql = "select " + rm.savingsProductChargeSchema() + " where
c.is_deleted=0 and c.is_active=1 and spc.savings_product_id=? ";
+ String sql = "select " + rm.savingsProductChargeSchema()
+ + " where c.is_deleted=false and c.is_active=true and
spc.savings_product_id=? ";
sql +=
addInClauseToSQL_toLimitChargesMappedToOffice_ifOfficeSpecificProductsEnabled();
return this.jdbcTemplate.query(sql, rm, new Object[] {
savingsProductId });
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/charge/service/ChargeWritePlatformServiceJpaRepositoryImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/charge/service/ChargeWritePlatformServiceJpaRepositoryImpl.java
index e11d44a..29fcdef 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/charge/service/ChargeWritePlatformServiceJpaRepositoryImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/charge/service/ChargeWritePlatformServiceJpaRepositoryImpl.java
@@ -273,29 +273,25 @@ public class ChargeWritePlatformServiceJpaRepositoryImpl
implements ChargeWriteP
}
private boolean isAnyLoansAssociateWithThisCharge(final Long chargeId) {
-
- final String sql = "select if((exists (select 1 from m_loan_charge lc
where lc.charge_id = ? and lc.is_active = 1)) = 1, 'true', 'false')";
+ final String sql = "select (CASE WHEN exists (select 1 from
m_loan_charge lc where lc.charge_id = ? and lc.is_active = true) THEN 'true'
ELSE 'false' END)";
final String isLoansUsingCharge =
this.jdbcTemplate.queryForObject(sql, String.class, new Object[] { chargeId });
return Boolean.valueOf(isLoansUsingCharge);
}
private boolean isAnySavingsAssociateWithThisCharge(final Long chargeId) {
-
- final String sql = "select if((exists (select 1 from
m_savings_account_charge sc where sc.charge_id = ? and sc.is_active = 1)) = 1,
'true', 'false')";
+ final String sql = "select (CASE WHEN exists (select 1 from
m_savings_account_charge sc where sc.charge_id = ? and sc.is_active = true)
THEN 'true' ELSE 'false' END)";
final String isSavingsUsingCharge =
this.jdbcTemplate.queryForObject(sql, String.class, new Object[] { chargeId });
return Boolean.valueOf(isSavingsUsingCharge);
}
private boolean isAnyLoanProductsAssociateWithThisCharge(final Long
chargeId) {
-
- final String sql = "select if((exists (select 1 from
m_product_loan_charge lc where lc.charge_id = ?)) = 1, 'true', 'false')";
+ final String sql = "select (CASE WHEN exists (select 1 from
m_product_loan_charge lc where lc.charge_id = ?) THEN 'true' ELSE 'false' END)";
final String isLoansUsingCharge =
this.jdbcTemplate.queryForObject(sql, String.class, new Object[] { chargeId });
return Boolean.valueOf(isLoansUsingCharge);
}
private boolean isAnySavingsProductsAssociateWithThisCharge(final Long
chargeId) {
-
- final String sql = "select if((exists (select 1 from
m_savings_product_charge sc where sc.charge_id = ?)) = 1, 'true', 'false')";
+ final String sql = "select (CASE WHEN (exists (select 1 from
m_savings_product_charge sc where sc.charge_id = ?)) = 1 THEN 'true' ELSE
'false' END)";
final String isSavingsUsingCharge =
this.jdbcTemplate.queryForObject(sql, String.class, new Object[] { chargeId });
return Boolean.valueOf(isSavingsUsingCharge);
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/exception/ClientMustBePendingToBeDeletedException.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/exception/ClientMustBePendingToBeDeletedException.java
index e453f52..8113727 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/exception/ClientMustBePendingToBeDeletedException.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/exception/ClientMustBePendingToBeDeletedException.java
@@ -26,7 +26,7 @@ import
org.apache.fineract.infrastructure.core.exception.AbstractPlatformDomainR
public class ClientMustBePendingToBeDeletedException extends
AbstractPlatformDomainRuleException {
public ClientMustBePendingToBeDeletedException(final Long id) {
- super("error.msg.clients.cannot.be.deleted", "Client with identifier "
+ id + " cannot be deleted as it is not in `Pending` state.",
+ super("error.msg.clients.cannot.be.deleted", "Client with identifier "
+ id + " cannot be deleted as it is not in Pending state.",
id);
}
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
index 16a772b..abd069a 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/client/service/ClientChargeReadPlatformServiceImpl.java
@@ -154,6 +154,7 @@ public class ClientChargeReadPlatformServiceImpl implements
ClientChargeReadPlat
sqlBuilder.append(" order by cc.charge_time_enum ASC,
cc.charge_due_date DESC, cc.is_penalty ASC ");
// apply limit and offsets
+
if (searchParameters.isLimited()) {
sqlBuilder.append(" limit ").append(searchParameters.getLimit());
if (searchParameters.isOffset()) {
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/account/service/SelfBeneficiariesTPTReadPlatformServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/account/service/SelfBeneficiariesTPTReadPlatformServiceImpl.java
index fd9b8c7..dc8e44e 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/account/service/SelfBeneficiariesTPTReadPlatformServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/portfolio/self/account/service/SelfBeneficiariesTPTReadPlatformServiceImpl.java
@@ -184,7 +184,7 @@ public class SelfBeneficiariesTPTReadPlatformServiceImpl
implements SelfBenefici
sqlBuilder.append(" where b.app_user_id = ? ");
sqlBuilder.append(" and b.account_id = ? ");
sqlBuilder.append(" and b.account_type = ? ");
- sqlBuilder.append(" and b.is_active = 1; ");
+ sqlBuilder.append(" and b.is_active = true; ");
return this.jdbcTemplate.queryForObject(sqlBuilder.toString(),
Long.class, appUserId, accountId, accountType);
}
diff --git
a/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerServiceImpl.java
b/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerServiceImpl.java
index 434a9b8..0ffec88 100644
---
a/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerServiceImpl.java
+++
b/fineract-provider/src/main/java/org/apache/fineract/scheduledjobs/service/ScheduledJobRunnerServiceImpl.java
@@ -38,6 +38,7 @@ import
org.apache.fineract.infrastructure.core.exception.PlatformApiDataValidati
import org.apache.fineract.infrastructure.core.service.DateUtils;
import
org.apache.fineract.infrastructure.core.service.RoutingDataSourceServiceFactory;
import org.apache.fineract.infrastructure.core.service.ThreadLocalContextUtil;
+import
org.apache.fineract.infrastructure.core.service.database.DatabaseTypeResolver;
import org.apache.fineract.infrastructure.jobs.annotation.CronTarget;
import org.apache.fineract.infrastructure.jobs.domain.ScheduledJobDetail;
import
org.apache.fineract.infrastructure.jobs.domain.ScheduledJobDetailRepository;
@@ -82,6 +83,7 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
private final JobRegisterService jobRegisterService;
private final ScheduledJobDetailRepository scheduledJobDetailsRepository;
private final FineractProperties fineractProperties;
+ private final DatabaseTypeResolver databaseTypeResolver;
@Autowired
public ScheduledJobRunnerServiceImpl(final RoutingDataSourceServiceFactory
dataSourceServiceFactory,
@@ -92,7 +94,8 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
final ShareAccountDividendReadPlatformService
shareAccountDividendReadPlatformService,
final ShareAccountSchedularService shareAccountSchedularService,
final TrialBalanceRepositoryWrapper trialBalanceRepositoryWrapper,
@Lazy final JobRegisterService jobRegisterService,
- final ScheduledJobDetailRepository scheduledJobDetailsRepository,
final FineractProperties fineractProperties) {
+ final ScheduledJobDetailRepository scheduledJobDetailsRepository,
final FineractProperties fineractProperties,
+ DatabaseTypeResolver databaseTypeResolver) {
this.dataSourceServiceFactory = dataSourceServiceFactory;
this.savingsAccountWritePlatformService =
savingsAccountWritePlatformService;
this.savingsAccountChargeReadPlatformService =
savingsAccountChargeReadPlatformService;
@@ -104,6 +107,7 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
this.jobRegisterService = jobRegisterService;
this.scheduledJobDetailsRepository = scheduledJobDetailsRepository;
this.fineractProperties = fineractProperties;
+ this.databaseTypeResolver = databaseTypeResolver;
}
@Transactional
@@ -113,77 +117,80 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
final JdbcTemplate jdbcTemplate = new
JdbcTemplate(this.dataSourceServiceFactory.determineDataSourceService().retrieveDataSource());
- final StringBuilder updateSqlBuilder = new StringBuilder(900);
- updateSqlBuilder.append("update m_loan ");
- updateSqlBuilder.append("join (");
- updateSqlBuilder.append("SELECT ml.id AS loanId,");
- updateSqlBuilder.append("SUM(mr.principal_amount) as
principal_disbursed_derived, ");
- updateSqlBuilder.append("SUM(IFNULL(mr.principal_completed_derived,0))
as principal_repaid_derived, ");
-
updateSqlBuilder.append("SUM(IFNULL(mr.principal_writtenoff_derived,0)) as
principal_writtenoff_derived,");
- updateSqlBuilder.append("SUM(IFNULL(mr.interest_amount,0)) as
interest_charged_derived,");
- updateSqlBuilder.append("SUM(IFNULL(mr.interest_completed_derived,0))
as interest_repaid_derived,");
- updateSqlBuilder.append("SUM(IFNULL(mr.interest_waived_derived,0)) as
interest_waived_derived,");
- updateSqlBuilder.append("SUM(IFNULL(mr.interest_writtenoff_derived,0))
as interest_writtenoff_derived,");
- updateSqlBuilder.append(
- "SUM(IFNULL(mr.fee_charges_amount,0)) + IFNULL((select
SUM(lc.amount) from m_loan_charge lc where lc.loan_id=ml.id and lc.is_active=1
and lc.charge_time_enum=1),0) as fee_charges_charged_derived,");
- updateSqlBuilder.append(
- "SUM(IFNULL(mr.fee_charges_completed_derived,0)) +
IFNULL((select SUM(lc.amount_paid_derived) from m_loan_charge lc where
lc.loan_id=ml.id and lc.is_active=1 and lc.charge_time_enum=1),0) as
fee_charges_repaid_derived,");
- updateSqlBuilder.append("SUM(IFNULL(mr.fee_charges_waived_derived,0))
as fee_charges_waived_derived,");
-
updateSqlBuilder.append("SUM(IFNULL(mr.fee_charges_writtenoff_derived,0)) as
fee_charges_writtenoff_derived,");
- updateSqlBuilder.append("SUM(IFNULL(mr.penalty_charges_amount,0)) as
penalty_charges_charged_derived,");
-
updateSqlBuilder.append("SUM(IFNULL(mr.penalty_charges_completed_derived,0)) as
penalty_charges_repaid_derived,");
-
updateSqlBuilder.append("SUM(IFNULL(mr.penalty_charges_waived_derived,0)) as
penalty_charges_waived_derived,");
-
updateSqlBuilder.append("SUM(IFNULL(mr.penalty_charges_writtenoff_derived,0))
as penalty_charges_writtenoff_derived ");
- updateSqlBuilder.append(" FROM m_loan ml ");
- updateSqlBuilder.append("INNER JOIN m_loan_repayment_schedule mr on
mr.loan_id = ml.id ");
- updateSqlBuilder.append("WHERE ml.disbursedon_date is not null ");
- updateSqlBuilder.append("GROUP BY ml.id ");
- updateSqlBuilder.append(") x on x.loanId = m_loan.id ");
-
- updateSqlBuilder.append("SET m_loan.principal_disbursed_derived =
x.principal_disbursed_derived,");
- updateSqlBuilder.append("m_loan.principal_repaid_derived =
x.principal_repaid_derived,");
- updateSqlBuilder.append("m_loan.principal_writtenoff_derived =
x.principal_writtenoff_derived,");
- updateSqlBuilder.append(
- "m_loan.principal_outstanding_derived =
(x.principal_disbursed_derived - (x.principal_repaid_derived +
x.principal_writtenoff_derived)),");
- updateSqlBuilder.append("m_loan.interest_charged_derived =
x.interest_charged_derived,");
- updateSqlBuilder.append("m_loan.interest_repaid_derived =
x.interest_repaid_derived,");
- updateSqlBuilder.append("m_loan.interest_waived_derived =
x.interest_waived_derived,");
- updateSqlBuilder.append("m_loan.interest_writtenoff_derived =
x.interest_writtenoff_derived,");
- updateSqlBuilder.append(
- "m_loan.interest_outstanding_derived =
(x.interest_charged_derived - (x.interest_repaid_derived +
x.interest_waived_derived + x.interest_writtenoff_derived)),");
- updateSqlBuilder.append("m_loan.fee_charges_charged_derived =
x.fee_charges_charged_derived,");
- updateSqlBuilder.append("m_loan.fee_charges_repaid_derived =
x.fee_charges_repaid_derived,");
- updateSqlBuilder.append("m_loan.fee_charges_waived_derived =
x.fee_charges_waived_derived,");
- updateSqlBuilder.append("m_loan.fee_charges_writtenoff_derived =
x.fee_charges_writtenoff_derived,");
- updateSqlBuilder.append(
- "m_loan.fee_charges_outstanding_derived =
(x.fee_charges_charged_derived - (x.fee_charges_repaid_derived +
x.fee_charges_waived_derived + x.fee_charges_writtenoff_derived)),");
- updateSqlBuilder.append("m_loan.penalty_charges_charged_derived =
x.penalty_charges_charged_derived,");
- updateSqlBuilder.append("m_loan.penalty_charges_repaid_derived =
x.penalty_charges_repaid_derived,");
- updateSqlBuilder.append("m_loan.penalty_charges_waived_derived =
x.penalty_charges_waived_derived,");
- updateSqlBuilder.append("m_loan.penalty_charges_writtenoff_derived =
x.penalty_charges_writtenoff_derived,");
- updateSqlBuilder.append(
- "m_loan.penalty_charges_outstanding_derived =
(x.penalty_charges_charged_derived - (x.penalty_charges_repaid_derived +
x.penalty_charges_waived_derived + x.penalty_charges_writtenoff_derived)),");
- updateSqlBuilder.append(
- "m_loan.total_expected_repayment_derived =
(x.principal_disbursed_derived + x.interest_charged_derived +
x.fee_charges_charged_derived + x.penalty_charges_charged_derived),");
- updateSqlBuilder.append(
- "m_loan.total_repayment_derived = (x.principal_repaid_derived
+ x.interest_repaid_derived + x.fee_charges_repaid_derived +
x.penalty_charges_repaid_derived),");
- updateSqlBuilder.append(
- "m_loan.total_expected_costofloan_derived =
(x.interest_charged_derived + x.fee_charges_charged_derived +
x.penalty_charges_charged_derived),");
- updateSqlBuilder.append(
- "m_loan.total_costofloan_derived = (x.interest_repaid_derived
+ x.fee_charges_repaid_derived + x.penalty_charges_repaid_derived),");
- updateSqlBuilder.append(
- "m_loan.total_waived_derived = (x.interest_waived_derived +
x.fee_charges_waived_derived + x.penalty_charges_waived_derived),");
- updateSqlBuilder.append(
- "m_loan.total_writtenoff_derived =
(x.interest_writtenoff_derived + x.fee_charges_writtenoff_derived +
x.penalty_charges_writtenoff_derived),");
- updateSqlBuilder.append("m_loan.total_outstanding_derived=");
- updateSqlBuilder.append(" (x.principal_disbursed_derived -
(x.principal_repaid_derived + x.principal_writtenoff_derived)) + ");
- updateSqlBuilder.append(
- " (x.interest_charged_derived - (x.interest_repaid_derived +
x.interest_waived_derived + x.interest_writtenoff_derived)) +");
- updateSqlBuilder.append(
- " (x.fee_charges_charged_derived -
(x.fee_charges_repaid_derived + x.fee_charges_waived_derived +
x.fee_charges_writtenoff_derived)) +");
- updateSqlBuilder.append(
- " (x.penalty_charges_charged_derived -
(x.penalty_charges_repaid_derived + x.penalty_charges_waived_derived +
x.penalty_charges_writtenoff_derived))");
-
+ boolean isMySQL = databaseTypeResolver.isMySQL();
+
+ String alias = isMySQL ? "l." : "";
+ final String selectPart = new StringBuilder().append(" (SELECT ml.id
AS loan_id, ")
+ .append("SUM(mr.principal_amount) as
principal_disbursed_derived, ")
+ .append("SUM(COALESCE(mr.principal_completed_derived,0)) as
principal_repaid_derived, ")
+ .append("SUM(COALESCE(mr.principal_writtenoff_derived,0)) as
principal_writtenoff_derived, ")
+ .append("SUM(COALESCE(mr.interest_amount,0)) as
interest_charged_derived, ")
+ .append("SUM(COALESCE(mr.interest_completed_derived,0)) as
interest_repaid_derived, ")
+ .append("SUM(COALESCE(mr.interest_waived_derived,0)) as
interest_waived_derived, ")
+ .append("SUM(COALESCE(mr.interest_writtenoff_derived,0)) as
interest_writtenoff_derived, ")
+ .append("SUM(COALESCE(mr.fee_charges_amount,0)) + ")
+ .append("COALESCE((select SUM(lc.amount) from m_loan_charge lc
where lc.loan_id = ml.id and lc.is_active = true")
+ .append(" and lc.charge_time_enum=1),0) as
fee_charges_charged_derived, ")
+ .append("SUM(COALESCE(mr.fee_charges_completed_derived,0)) + ")
+ .append("COALESCE((select SUM(lc.amount_paid_derived) from
m_loan_charge lc where lc.loan_id = ml.id and lc.is_active = true")
+ .append(" and lc.charge_time_enum = 1), 0) as
fee_charges_repaid_derived, ")
+ .append("SUM(COALESCE(mr.fee_charges_waived_derived,0)) as
fee_charges_waived_derived, ")
+ .append("SUM(COALESCE(mr.fee_charges_writtenoff_derived,0)) as
fee_charges_writtenoff_derived, ")
+ .append("SUM(COALESCE(mr.penalty_charges_amount,0)) as
penalty_charges_charged_derived, ")
+ .append("SUM(COALESCE(mr.penalty_charges_completed_derived,0))
as penalty_charges_repaid_derived, ")
+ .append("SUM(COALESCE(mr.penalty_charges_waived_derived,0)) as
penalty_charges_waived_derived, ")
+
.append("SUM(COALESCE(mr.penalty_charges_writtenoff_derived,0)) as
penalty_charges_writtenoff_derived ")
+ .append("FROM m_loan ml ").append("INNER JOIN
m_loan_repayment_schedule mr on mr.loan_id = ml.id ")
+ .append("WHERE ml.disbursedon_date is not null
").append("GROUP BY ml.id").append(") x").toString();
+
+ final String setPart = new StringBuilder().append(" SET
").append(alias)
+ .append("principal_disbursed_derived =
x.principal_disbursed_derived, ").append(alias)
+ .append("principal_repaid_derived =
x.principal_repaid_derived, ").append(alias)
+ .append("principal_writtenoff_derived =
x.principal_writtenoff_derived, ").append(alias)
+ .append("principal_outstanding_derived =
(x.principal_disbursed_derived - (x.principal_repaid_derived +
x.principal_writtenoff_derived)), ")
+ .append(alias).append("interest_charged_derived =
x.interest_charged_derived, ").append(alias)
+ .append("interest_repaid_derived = x.interest_repaid_derived,
").append(alias)
+ .append("interest_waived_derived = x.interest_waived_derived,
").append(alias)
+ .append("interest_writtenoff_derived =
x.interest_writtenoff_derived, ").append(alias)
+ .append("interest_outstanding_derived =
(x.interest_charged_derived - (x.interest_repaid_derived +
x.interest_waived_derived + x.interest_writtenoff_derived)), ")
+ .append(alias).append("fee_charges_charged_derived =
x.fee_charges_charged_derived, ").append(alias)
+ .append("fee_charges_repaid_derived =
x.fee_charges_repaid_derived, ").append(alias)
+ .append("fee_charges_waived_derived =
x.fee_charges_waived_derived, ").append(alias)
+ .append("fee_charges_writtenoff_derived =
x.fee_charges_writtenoff_derived, ").append(alias)
+ .append("fee_charges_outstanding_derived =
(x.fee_charges_charged_derived - (x.fee_charges_repaid_derived +
x.fee_charges_waived_derived + x.fee_charges_writtenoff_derived)), ")
+ .append(alias).append("penalty_charges_charged_derived =
x.penalty_charges_charged_derived, ").append(alias)
+ .append("penalty_charges_repaid_derived =
x.penalty_charges_repaid_derived, ").append(alias)
+ .append("penalty_charges_waived_derived =
x.penalty_charges_waived_derived, ").append(alias)
+ .append("penalty_charges_writtenoff_derived =
x.penalty_charges_writtenoff_derived, ").append(alias)
+ .append("penalty_charges_outstanding_derived =
(x.penalty_charges_charged_derived - (x.penalty_charges_repaid_derived +
x.penalty_charges_waived_derived + x.penalty_charges_writtenoff_derived)), ")
+ .append(alias)
+ .append("total_expected_repayment_derived =
(x.principal_disbursed_derived + x.interest_charged_derived +
x.fee_charges_charged_derived + x.penalty_charges_charged_derived), ")
+ .append(alias)
+ .append("total_repayment_derived = (x.principal_repaid_derived
+ x.interest_repaid_derived + x.fee_charges_repaid_derived +
x.penalty_charges_repaid_derived), ")
+ .append(alias)
+ .append("total_expected_costofloan_derived =
(x.interest_charged_derived + x.fee_charges_charged_derived +
x.penalty_charges_charged_derived), ")
+ .append(alias)
+ .append("total_costofloan_derived = (x.interest_repaid_derived
+ x.fee_charges_repaid_derived + x.penalty_charges_repaid_derived), ")
+ .append(alias)
+ .append("total_waived_derived = (x.interest_waived_derived +
x.fee_charges_waived_derived + x.penalty_charges_waived_derived), ")
+ .append(alias)
+ .append("total_writtenoff_derived =
(x.interest_writtenoff_derived + x.fee_charges_writtenoff_derived +
x.penalty_charges_writtenoff_derived), ")
+ .append(alias)
+ .append("total_outstanding_derived =
(x.principal_disbursed_derived - (x.principal_repaid_derived +
x.principal_writtenoff_derived)) + ")
+ .append("(x.interest_charged_derived -
(x.interest_repaid_derived + x.interest_waived_derived +
x.interest_writtenoff_derived)) + ")
+ .append("(x.fee_charges_charged_derived -
(x.fee_charges_repaid_derived + x.fee_charges_waived_derived +
x.fee_charges_writtenoff_derived)) + ")
+ .append("(x.penalty_charges_charged_derived -
(x.penalty_charges_repaid_derived + x.penalty_charges_waived_derived +
x.penalty_charges_writtenoff_derived))")
+ .toString();
+
+ String wherePart = " x.loan_id = l.id";
+
+ final StringBuilder updateSqlBuilder = new StringBuilder();
+ updateSqlBuilder.append("UPDATE m_loan l");
+ if (isMySQL) {
+ updateSqlBuilder.append(" JOIN").append(selectPart).append("
ON").append(wherePart).append(' ').append(setPart);
+ } else {
+ updateSqlBuilder.append(setPart).append("
FROM").append(selectPart).append(" WHERE").append(wherePart);
+ }
final int result = jdbcTemplate.update(updateSqlBuilder.toString());
LOG.info("{}: Records affected by updateLoanSummaryDetails: {}",
ThreadLocalContextUtil.getTenant().getName(), result);
@@ -203,21 +210,21 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
updateSqlBuilder.append(
"INSERT INTO m_loan_paid_in_advance(loan_id,
principal_in_advance_derived, interest_in_advance_derived,
fee_charges_in_advance_derived, penalty_charges_in_advance_derived,
total_in_advance_derived)");
updateSqlBuilder.append(" select ml.id as loanId,");
- updateSqlBuilder.append(" SUM(ifnull(mr.principal_completed_derived,
0)) as principal_in_advance_derived,");
- updateSqlBuilder.append(" SUM(ifnull(mr.interest_completed_derived,
0)) as interest_in_advance_derived,");
- updateSqlBuilder.append(" SUM(ifnull(mr.fee_charges_completed_derived,
0)) as fee_charges_in_advance_derived,");
- updateSqlBuilder.append("
SUM(ifnull(mr.penalty_charges_completed_derived, 0)) as
penalty_charges_in_advance_derived,");
+ updateSqlBuilder.append(" SUM(coalesce(mr.principal_completed_derived,
0)) as principal_in_advance_derived,");
+ updateSqlBuilder.append(" SUM(coalesce(mr.interest_completed_derived,
0)) as interest_in_advance_derived,");
+ updateSqlBuilder.append("
SUM(coalesce(mr.fee_charges_completed_derived, 0)) as
fee_charges_in_advance_derived,");
+ updateSqlBuilder.append("
SUM(coalesce(mr.penalty_charges_completed_derived, 0)) as
penalty_charges_in_advance_derived,");
updateSqlBuilder.append(
- " (SUM(ifnull(mr.principal_completed_derived, 0)) +
SUM(ifnull(mr.interest_completed_derived, 0)) +
SUM(ifnull(mr.fee_charges_completed_derived, 0)) +
SUM(ifnull(mr.penalty_charges_completed_derived, 0))) as
total_in_advance_derived");
+ " (SUM(coalesce(mr.principal_completed_derived, 0)) +
SUM(coalesce(mr.interest_completed_derived, 0)) +
SUM(coalesce(mr.fee_charges_completed_derived, 0)) +
SUM(coalesce(mr.penalty_charges_completed_derived, 0))) as
total_in_advance_derived");
updateSqlBuilder.append(" FROM m_loan ml ");
updateSqlBuilder.append(" INNER JOIN m_loan_repayment_schedule mr on
mr.loan_id = ml.id ");
updateSqlBuilder.append(" WHERE ml.loan_status_id = 300 ");
updateSqlBuilder.append(" and mr.duedate >= CURDATE() ");
updateSqlBuilder.append(" GROUP BY ml.id");
updateSqlBuilder
- .append(" HAVING (SUM(ifnull(mr.principal_completed_derived,
0)) + SUM(ifnull(mr.interest_completed_derived, 0)) +");
- updateSqlBuilder
- .append(" SUM(ifnull(mr.fee_charges_completed_derived, 0)) +
SUM(ifnull(mr.penalty_charges_completed_derived, 0))) > 0.0");
+ .append(" HAVING (SUM(coalesce(mr.principal_completed_derived,
0)) + SUM(coalesce(mr.interest_completed_derived, 0)) +");
+ updateSqlBuilder.append(
+ " SUM(coalesce(mr.fee_charges_completed_derived, 0)) +
SUM(coalesce(mr.penalty_charges_completed_derived, 0))) > 0.0");
final int result = jdbcTemplate.update(updateSqlBuilder.toString());
@@ -285,28 +292,35 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
final JdbcTemplate jdbcTemplate = new
JdbcTemplate(this.dataSourceServiceFactory.determineDataSourceService().retrieveDataSource());
- final StringBuilder resetNPASqlBuilder = new StringBuilder(900);
+ final StringBuilder resetNPASqlBuilder = new StringBuilder();
resetNPASqlBuilder.append("update m_loan loan ");
- resetNPASqlBuilder.append("left join m_loan_arrears_aging laa on
laa.loan_id = loan.id ");
- resetNPASqlBuilder.append("inner join m_product_loan mpl on mpl.id =
loan.product_id and mpl.overdue_days_for_npa is not null ");
- resetNPASqlBuilder.append("set loan.is_npa = 0 ");
- resetNPASqlBuilder.append("where loan.loan_status_id = 300 and
mpl.account_moves_out_of_npa_only_on_arrears_completion = 0 ");
- resetNPASqlBuilder
- .append("or
(mpl.account_moves_out_of_npa_only_on_arrears_completion = 1 and
laa.overdue_since_date_derived is null)");
-
+ String fromPart = " (SELECT loan2.* FROM m_loan loan2 left join
m_loan_arrears_aging laa on laa.loan_id = loan2.id "
+ + "inner join m_product_loan mpl on mpl.id = loan2.product_id
and mpl.overdue_days_for_npa is not null "
+ + "WHERE loan2.loan_status_id = 300 and
mpl.account_moves_out_of_npa_only_on_arrears_completion = false"
+ + " or
(mpl.account_moves_out_of_npa_only_on_arrears_completion = true"
+ + " and laa.overdue_since_date_derived is null)) sl";
+ String wherePart = " where loan.id = sl.id ";
+
+ if (databaseTypeResolver.isMySQL()) {
+ resetNPASqlBuilder.append(", ").append(fromPart).append(" set
loan.is_npa = false").append(wherePart);
+ } else {
+ resetNPASqlBuilder.append("set is_npa = false").append(" FROM
").append(fromPart).append(wherePart);
+ }
jdbcTemplate.update(resetNPASqlBuilder.toString());
final StringBuilder updateSqlBuilder = new StringBuilder(900);
- updateSqlBuilder.append("UPDATE m_loan as ml,");
- updateSqlBuilder.append(" (select loan.id ");
- updateSqlBuilder.append("from m_loan_arrears_aging laa");
- updateSqlBuilder.append(" INNER JOIN m_loan loan on laa.loan_id =
loan.id ");
- updateSqlBuilder.append(" INNER JOIN m_product_loan mpl on mpl.id =
loan.product_id AND mpl.overdue_days_for_npa is not null ");
- updateSqlBuilder.append("WHERE loan.loan_status_id = 300 and ");
- updateSqlBuilder.append("laa.overdue_since_date_derived <
SUBDATE(CURDATE(),INTERVAL ifnull(mpl.overdue_days_for_npa,0) day) ");
- updateSqlBuilder.append("group by loan.id) as sl ");
- updateSqlBuilder.append("SET ml.is_npa=1 where ml.id=sl.id ");
+ fromPart = " (select loan.id " + " FROM m_loan_arrears_aging laa" + "
INNER JOIN m_loan loan on laa.loan_id = loan.id "
+ + " INNER JOIN m_product_loan mpl on mpl.id = loan.product_id
AND mpl.overdue_days_for_npa is not null "
+ + "WHERE loan.loan_status_id = 300 and " +
"laa.overdue_since_date_derived < "
+ + "SUBDATE(CURDATE(),INTERVAL
COALESCE(mpl.overdue_days_for_npa, 0) day)" + " group by loan.id) as sl ";
+ wherePart = " where ml.id=sl.id ";
+ updateSqlBuilder.append("UPDATE m_loan as ml ");
+ if (databaseTypeResolver.isMySQL()) {
+ updateSqlBuilder.append(", ").append(fromPart).append(" SET
ml.is_npa = true").append(wherePart);
+ } else {
+ updateSqlBuilder.append(" SET is_npa = true").append(" FROM
").append(fromPart).append(wherePart);
+ }
final int result = jdbcTemplate.update(updateSqlBuilder.toString());
@@ -343,7 +357,7 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
public void generateRDSchedule() {
final JdbcTemplate jdbcTemplate = new
JdbcTemplate(this.dataSourceServiceFactory.determineDataSourceService().retrieveDataSource());
final Collection<Map<String, Object>> scheduleDetails =
this.depositAccountReadPlatformService.retriveDataForRDScheduleCreation();
- String insertSql = "INSERT INTO `m_mandatory_savings_schedule`
(`savings_account_id`, `duedate`, `installment`, `deposit_amount`,
`completed_derived`, `created_date`, `lastmodified_date`) VALUES ";
+ String insertSql = "INSERT INTO m_mandatory_savings_schedule
(savings_account_id, duedate, installment, deposit_amount, completed_derived,
created_date, lastmodified_date) VALUES ";
StringBuilder sb = new StringBuilder();
String currentDate =
formatterWithTime.format(DateUtils.getLocalDateTimeOfTenant());
int iterations = 0;
@@ -437,7 +451,7 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
final JdbcTemplate jdbcTemplate = new
JdbcTemplate(this.dataSourceServiceFactory.determineDataSourceService().retrieveDataSource());
final StringBuilder tbGapSqlBuilder = new StringBuilder(500);
tbGapSqlBuilder.append("select distinct(je.transaction_date)
").append("from acc_gl_journal_entry je ")
- .append("where je.transaction_date > (select
IFNULL(MAX(created_date),'2010-01-01') from m_trial_balance)");
+ .append("where je.transaction_date > (select
coalesce(MAX(created_date),'2010-01-01') from m_trial_balance)");
final List<Date> tbGaps =
jdbcTemplate.queryForList(tbGapSqlBuilder.toString(), Date.class);
@@ -450,7 +464,7 @@ public class ScheduledJobRunnerServiceImpl implements
ScheduledJobRunnerService
final String formattedDate = new
SimpleDateFormat("yyyy-MM-dd").format(tbGap);
final StringBuilder sqlBuilder = new StringBuilder(600);
sqlBuilder.append("Insert Into m_trial_balance(office_id,
account_id, Amount, entry_date, created_date,closing_balance) ")
- .append("Select je.office_id, je.account_id,
sum(if(je.type_enum=1, (-1) * je.amount, je.amount)) ")
+ .append("Select je.office_id, je.account_id, SUM(CASE WHEN
je.type_enum=1 THEN (-1) * je.amount ELSE je.amount END) ")
.append("as Amount, Date(je.entry_date) as 'Entry_Date',
je.transaction_date as 'Created_Date',sum(je.amount) as closing_balance ")
.append("from acc_gl_journal_entry je WHERE
je.transaction_date = ? ")
.append("group by je.account_id, je.office_id,
je.transaction_date, Date(je.entry_date)");
