API tools faq
paste
Advertisement
KumaranathFernando

Untitled

KumaranathFernando
Jul 22nd, 2018
132
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 7.33 KB | None | 0 0
raw download clone embed print report
  1. @Transactional
  2.     @Override
  3.     @CronTarget(jobName = JobName.UPDATE_LOAN_SUMMARY)
  4.     public void updateLoanSummaryDetails() {
  5.  
  6.         final JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSourceServiceFactory.determineDataSourceService().retrieveDataSource());
  7.  
  8.         final StringBuilder updateSqlBuilder = new StringBuilder(900);
  9.         updateSqlBuilder.append("update m_loan ");
  10.         updateSqlBuilder.append("join (");
  11.         updateSqlBuilder.append("SELECT ml.id AS loanId,");
  12.         updateSqlBuilder.append("SUM(mr.principal_amount) as principal_disbursed_derived, ");
  13.         updateSqlBuilder.append("SUM(IFNULL(mr.principal_completed_derived,0)) as principal_repaid_derived, ");
  14.         updateSqlBuilder.append("SUM(IFNULL(mr.principal_writtenoff_derived,0)) as principal_writtenoff_derived,");
  15.         updateSqlBuilder.append("SUM(IFNULL(mr.interest_amount,0)) as interest_charged_derived,");
  16.         updateSqlBuilder.append("SUM(IFNULL(mr.interest_completed_derived,0)) as interest_repaid_derived,");
  17.         updateSqlBuilder.append("SUM(IFNULL(mr.interest_waived_derived,0)) as interest_waived_derived,");
  18.         updateSqlBuilder.append("SUM(IFNULL(mr.interest_writtenoff_derived,0)) as interest_writtenoff_derived,");
  19.         updateSqlBuilder
  20.                 .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,");
  21.         updateSqlBuilder
  22.                 .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,");
  23.         updateSqlBuilder.append("SUM(IFNULL(mr.fee_charges_waived_derived,0)) as fee_charges_waived_derived,");
  24.         updateSqlBuilder.append("SUM(IFNULL(mr.fee_charges_writtenoff_derived,0)) as fee_charges_writtenoff_derived,");
  25.         updateSqlBuilder.append("SUM(IFNULL(mr.penalty_charges_amount,0)) as penalty_charges_charged_derived,");
  26.         updateSqlBuilder.append("SUM(IFNULL(mr.penalty_charges_completed_derived,0)) as penalty_charges_repaid_derived,");
  27.         updateSqlBuilder.append("SUM(IFNULL(mr.penalty_charges_waived_derived,0)) as penalty_charges_waived_derived,");
  28.         updateSqlBuilder.append("SUM(IFNULL(mr.penalty_charges_writtenoff_derived,0)) as penalty_charges_writtenoff_derived ");
  29.         updateSqlBuilder.append(" FROM m_loan ml ");
  30.         updateSqlBuilder.append("INNER JOIN m_loan_repayment_schedule mr on mr.loan_id = ml.id ");
  31.         updateSqlBuilder.append("WHERE ml.disbursedon_date is not null ");
  32.         updateSqlBuilder.append("GROUP BY ml.id ");
  33.         updateSqlBuilder.append(") x on x.loanId = m_loan.id ");
  34.  
  35.         updateSqlBuilder.append("SET m_loan.principal_disbursed_derived = x.principal_disbursed_derived,");
  36.         updateSqlBuilder.append("m_loan.principal_repaid_derived = x.principal_repaid_derived,");
  37.         updateSqlBuilder.append("m_loan.principal_writtenoff_derived = x.principal_writtenoff_derived,");
  38.         updateSqlBuilder
  39.                 .append("m_loan.principal_outstanding_derived = (x.principal_disbursed_derived - (x.principal_repaid_derived + x.principal_writtenoff_derived)),");
  40.         updateSqlBuilder.append("m_loan.interest_charged_derived = x.interest_charged_derived,");
  41.         updateSqlBuilder.append("m_loan.interest_repaid_derived = x.interest_repaid_derived,");
  42.         updateSqlBuilder.append("m_loan.interest_waived_derived = x.interest_waived_derived,");
  43.         updateSqlBuilder.append("m_loan.interest_writtenoff_derived = x.interest_writtenoff_derived,");
  44.         updateSqlBuilder
  45.                 .append("m_loan.interest_outstanding_derived = (x.interest_charged_derived - (x.interest_repaid_derived + x.interest_waived_derived + x.interest_writtenoff_derived)),");
  46.         updateSqlBuilder.append("m_loan.fee_charges_charged_derived = x.fee_charges_charged_derived,");
  47.         updateSqlBuilder.append("m_loan.fee_charges_repaid_derived = x.fee_charges_repaid_derived,");
  48.         updateSqlBuilder.append("m_loan.fee_charges_waived_derived = x.fee_charges_waived_derived,");
  49.         updateSqlBuilder.append("m_loan.fee_charges_writtenoff_derived = x.fee_charges_writtenoff_derived,");
  50.         updateSqlBuilder
  51.                 .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)),");
  52.         updateSqlBuilder.append("m_loan.penalty_charges_charged_derived = x.penalty_charges_charged_derived,");
  53.         updateSqlBuilder.append("m_loan.penalty_charges_repaid_derived = x.penalty_charges_repaid_derived,");
  54.         updateSqlBuilder.append("m_loan.penalty_charges_waived_derived = x.penalty_charges_waived_derived,");
  55.         updateSqlBuilder.append("m_loan.penalty_charges_writtenoff_derived = x.penalty_charges_writtenoff_derived,");
  56.         updateSqlBuilder
  57.                 .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)),");
  58.         updateSqlBuilder
  59.                 .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),");
  60.         updateSqlBuilder
  61.                 .append("m_loan.total_repayment_derived = (x.principal_repaid_derived + x.interest_repaid_derived + x.fee_charges_repaid_derived + x.penalty_charges_repaid_derived),");
  62.         updateSqlBuilder
  63.                 .append("m_loan.total_expected_costofloan_derived = (x.interest_charged_derived + x.fee_charges_charged_derived + x.penalty_charges_charged_derived),");
  64.         updateSqlBuilder
  65.                 .append("m_loan.total_costofloan_derived = (x.interest_repaid_derived + x.fee_charges_repaid_derived + x.penalty_charges_repaid_derived),");
  66.         updateSqlBuilder
  67.                 .append("m_loan.total_waived_derived = (x.interest_waived_derived + x.fee_charges_waived_derived + x.penalty_charges_waived_derived),");
  68.         updateSqlBuilder
  69.                 .append("m_loan.total_writtenoff_derived = (x.interest_writtenoff_derived +  x.fee_charges_writtenoff_derived + x.penalty_charges_writtenoff_derived),");
  70.         updateSqlBuilder.append("m_loan.total_outstanding_derived=");
  71.         updateSqlBuilder.append(" (x.principal_disbursed_derived - (x.principal_repaid_derived + x.principal_writtenoff_derived)) + ");
  72.         updateSqlBuilder
  73.                 .append(" (x.interest_charged_derived - (x.interest_repaid_derived + x.interest_waived_derived + x.interest_writtenoff_derived)) +");
  74.         updateSqlBuilder
  75.                 .append(" (x.fee_charges_charged_derived - (x.fee_charges_repaid_derived + x.fee_charges_waived_derived + x.fee_charges_writtenoff_derived)) +");
  76.         updateSqlBuilder
  77.                 .append(" (x.penalty_charges_charged_derived - (x.penalty_charges_repaid_derived + x.penalty_charges_waived_derived + x.penalty_charges_writtenoff_derived))");
  78.  
  79.         final int result = jdbcTemplate.update(updateSqlBuilder.toString());
  80.  
  81.         logger.info(ThreadLocalContextUtil.getTenant().getName() + ": Results affected by update: " + result);
  82.     }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!