ShruthiRajaram closed pull request #470: FINERACT:681 Added total outstanding amount to loan repayment workbook URL: https://github.com/apache/fineract/pull/470
This is a PR merged from a forked repository. As GitHub hides the original diff on merge, it is displayed below for the sake of provenance: As this is a foreign pull request (from a fork), the diff is supplied below (as it won't show otherwise due to GitHub magic): diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/constants/LoanRepaymentConstants.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/constants/LoanRepaymentConstants.java index 4a35d9461..8fe2c6f36 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/constants/LoanRepaymentConstants.java +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/constants/LoanRepaymentConstants.java @@ -26,19 +26,22 @@ public static final int LOAN_ACCOUNT_NO_COL = 3;//D public static final int PRODUCT_COL = 4;//E public static final int PRINCIPAL_COL = 5;//F - public static final int AMOUNT_COL = 6;//G - public static final int REPAID_ON_DATE_COL = 7;//H - public static final int REPAYMENT_TYPE_COL = 8;//I - public static final int ACCOUNT_NO_COL = 9;//J - public static final int CHECK_NO_COL = 10;//K - public static final int ROUTING_CODE_COL = 11;//L - public static final int RECEIPT_NO_COL = 12;//M - public static final int BANK_NO_COL = 13;//N - public static final int STATUS_COL = 14;//O - public static final int LOOKUP_CLIENT_NAME_COL = 15;//P - public static final int LOOKUP_CLIENT_EXTERNAL_ID = 16;//Q - public static final int LOOKUP_ACCOUNT_NO_COL = 17;//R - public static final int LOOKUP_PRODUCT_COL = 18;//S - public static final int LOOKUP_PRINCIPAL_COL = 19;//T - public static final int LOOKUP_LOAN_DISBURSEMENT_DATE_COL = 20;//U + public static final int TOTAL_OUTSTANDING_AMOUNT_COL=6; + public static final int LOAN_DISBURSEMENT_DATE_COL=7; + public static final int AMOUNT_COL = 8;//G + public static final int REPAID_ON_DATE_COL = 9;//H + public static final int REPAYMENT_TYPE_COL = 10;//I + public static final int ACCOUNT_NO_COL = 11;//J + public static final int CHECK_NO_COL = 12;//K + public static final int ROUTING_CODE_COL = 13;//L + public static final int RECEIPT_NO_COL = 14;//M + public static final int BANK_NO_COL = 15;//N + public static final int STATUS_COL = 16;//O + public static final int LOOKUP_CLIENT_NAME_COL = 17;//P + public static final int LOOKUP_CLIENT_EXTERNAL_ID = 18;//Q + public static final int LOOKUP_ACCOUNT_NO_COL = 19;//R + public static final int LOOKUP_PRODUCT_COL = 20;//S + public static final int LOOKUP_PRINCIPAL_COL = 21;//T + public static final int LOOKUP_TOTAL_OUTSTANDING_AMOUNT_COL=22; + public static final int LOOKUP_LOAN_DISBURSEMENT_DATE_COL = 23;//U } diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loanrepayment/LoanRepaymentWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loanrepayment/LoanRepaymentWorkbookPopulator.java index 04646fbcc..adf9b6782 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loanrepayment/LoanRepaymentWorkbookPopulator.java +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loanrepayment/LoanRepaymentWorkbookPopulator.java @@ -45,7 +45,7 @@ private Map<Long,String> clientIdToClientExternalId; public LoanRepaymentWorkbookPopulator(List<LoanAccountData> loans, OfficeSheetPopulator officeSheetPopulator, - ClientSheetPopulator clientSheetPopulator, ExtrasSheetPopulator extrasSheetPopulator) { + ClientSheetPopulator clientSheetPopulator, ExtrasSheetPopulator extrasSheetPopulator) { this.allloans = loans; this.officeSheetPopulator = officeSheetPopulator; this.clientSheetPopulator = clientSheetPopulator; @@ -62,7 +62,7 @@ public void populate(Workbook workbook,String dateFormat) { setClientIdToClientExternalId(); populateLoansTable(loanRepaymentSheet,dateFormat); setRules(loanRepaymentSheet,dateFormat); - setDefaults(loanRepaymentSheet); + setDefaults(loanRepaymentSheet,dateFormat); } private void setClientIdToClientExternalId() { @@ -70,27 +70,41 @@ private void setClientIdToClientExternalId() { List<ClientData>allclients=clientSheetPopulator.getClients(); for (ClientData client: allclients) { if (client.getExternalId()!=null) - clientIdToClientExternalId.put(client.getId(),client.getExternalId()); + clientIdToClientExternalId.put(client.getId(),client.getExternalId()); } } - private void setDefaults(Sheet worksheet) { - for (Integer rowNo = 1; rowNo < 3000; rowNo++) { - Row row = worksheet.getRow(rowNo); - if (row == null) - row = worksheet.createRow(rowNo); - writeFormula(LoanRepaymentConstants.CLIENT_EXTERNAL_ID, row, - "IF(ISERROR(VLOOKUP($B"+(rowNo+1)+",$P$2:$Q$"+(allloans.size()+1)+",2,FALSE))," + - "\"\",(VLOOKUP($B"+(rowNo+1)+",$P$2:$Q$"+(allloans.size()+1)+",2,FALSE)))"); - writeFormula(LoanRepaymentConstants.PRODUCT_COL, row, - "IF(ISERROR(VLOOKUP($D" + (rowNo + 1) + ",$R$2:$T$" + (allloans.size() + 1) - + ",2,FALSE)),\"\",VLOOKUP($D" + (rowNo + 1) + ",$R$2:$T$" + (allloans.size() + 1) - + ",2,FALSE))"); - writeFormula(LoanRepaymentConstants.PRINCIPAL_COL, row, - "IF(ISERROR(VLOOKUP($D" + (rowNo + 1) + ",$R$2:$T$" + (allloans.size() + 1) - + ",3,FALSE)),\"\",VLOOKUP($D" + (rowNo + 1) + ",$R$2:$T$" + (allloans.size() + 1) - + ",3,FALSE))"); - } + private void setDefaults(Sheet worksheet,String dateFormat) { + for (Integer rowNo = 1; rowNo < 3000; rowNo++) { + Row row = worksheet.getRow(rowNo); + if (row == null) + row = worksheet.createRow(rowNo); + writeFormula(LoanRepaymentConstants.CLIENT_EXTERNAL_ID, row, + "IF(ISERROR(VLOOKUP($B"+(rowNo+1)+",$R$2:$S$"+(allloans.size()+1)+",2,FALSE))," + + "\"\",(VLOOKUP($B"+(rowNo+1)+",$R$2:$S$"+(allloans.size()+1)+",2,FALSE)))"); + writeFormula(LoanRepaymentConstants.PRODUCT_COL, row, + "IF(ISERROR(VLOOKUP($D" + (rowNo + 1) + ",$T$2:$X$" + (allloans.size() + 1) + + ",2,FALSE)),\"\",VLOOKUP($D" + (rowNo + 1) + ",$T$2:$X$" + (allloans.size() + 1) + + ",2,FALSE))"); + writeFormula(LoanRepaymentConstants.PRINCIPAL_COL, row, + "IF(ISERROR(VLOOKUP($D" + (rowNo + 1) + ",$T$2:$X$" + (allloans.size() + 1) + + ",3,FALSE)),\"\",VLOOKUP($D" + (rowNo + 1) + ",$T$2:$X$" + (allloans.size() + 1) + + ",3,FALSE))"); + writeFormula(LoanRepaymentConstants.TOTAL_OUTSTANDING_AMOUNT_COL, row, + "IF(ISERROR(VLOOKUP($D" + (rowNo + 1) + ",$T$2:$X$" + (allloans.size() + 1) + + ",4,FALSE)),\"\",VLOOKUP($D" + (rowNo + 1) + ",$T$2:$X$" + (allloans.size() + 1) + + ",4,FALSE))"); + + writeFormula(LoanRepaymentConstants.LOAN_DISBURSEMENT_DATE_COL, row, + "IF(ISERROR(VLOOKUP($D" + (rowNo + 1) + ",$T$2:$X$" + (allloans.size() + 1) + + ",5,FALSE)),\"\",VLOOKUP($D" + (rowNo + 1) + ",$T$2:$X$" + (allloans.size() + 1) + + ",5,FALSE))"); + Workbook workbook=worksheet.getWorkbook(); + CellStyle dateCellStyle = workbook.createCellStyle(); + short df = workbook.createDataFormat().getFormat(dateFormat); + dateCellStyle.setDataFormat(df); + row.getCell(LoanRepaymentConstants.LOAN_DISBURSEMENT_DATE_COL).setCellStyle(dateCellStyle); + } } private void setRules(Sheet worksheet,String dateFormat) { @@ -117,7 +131,7 @@ private void setRules(Sheet worksheet,String dateFormat) { DataValidationConstraint paymentTypeConstraint = validationHelper.createFormulaListConstraint("PaymentTypes"); DataValidationConstraint repaymentDateConstraint = validationHelper.createDateConstraint( DataValidationConstraint.OperatorType.BETWEEN, - "=VLOOKUP($D1,$R$2:$U$" + (allloans.size() + 1) + ",4,FALSE)", "=TODAY()", dateFormat); + "=VLOOKUP($D1,$T$2:$X$" + (allloans.size() + 1) + ",4,FALSE)", "=TODAY()", dateFormat); DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange); @@ -182,13 +196,13 @@ private void setNames(Sheet worksheet) { } } - // Account Number Named after Clients + // Account Number Named after Clients for (int j = 0; j < clientsWithActiveLoans.size(); j++) { Name name = loanRepaymentWorkbook.createName(); name.setNameName("Account_" + clientsWithActiveLoans.get(j).replaceAll(" ", "_") + "_" + clientIdsWithActiveLoans.get(j) + "_"); name.setRefersToFormula( - TemplatePopulateImportConstants.LOAN_REPAYMENT_SHEET_NAME+"!$R$" + clientNameToBeginEndIndexes.get(clientsWithActiveLoans.get(j))[0] + ":$R$" + TemplatePopulateImportConstants.LOAN_REPAYMENT_SHEET_NAME+"!$T$" + clientNameToBeginEndIndexes.get(clientsWithActiveLoans.get(j))[0] + ":$T$" + clientNameToBeginEndIndexes.get(clientsWithActiveLoans.get(j))[1]); } @@ -216,6 +230,8 @@ private void populateLoansTable(Sheet loanRepaymentSheet,String dateFormat) { writeString(LoanRepaymentConstants.LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(loan.getAccountNo())+"-"+loan.getStatusStringValue()); writeString(LoanRepaymentConstants.LOOKUP_PRODUCT_COL, row, loan.getLoanProductName()); writeDouble(LoanRepaymentConstants.LOOKUP_PRINCIPAL_COL, row, loan.getPrincipal().doubleValue()); + if (loan.getTotalOutstandingAmount() != null) + writeBigDecimal(LoanRepaymentConstants.LOOKUP_TOTAL_OUTSTANDING_AMOUNT_COL, row, loan.getTotalOutstandingAmount()); if (loan.getDisbursementDate() != null) { try { date = inputFormat.parse(loan.getDisbursementDate().toString()); @@ -237,6 +253,8 @@ private void setLayout(Sheet worksheet) { worksheet.setColumnWidth(LoanRepaymentConstants.LOAN_ACCOUNT_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); worksheet.setColumnWidth(LoanRepaymentConstants.PRODUCT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); worksheet.setColumnWidth(LoanRepaymentConstants.PRINCIPAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.TOTAL_OUTSTANDING_AMOUNT_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.LOAN_DISBURSEMENT_DATE_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); worksheet.setColumnWidth(LoanRepaymentConstants.AMOUNT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); worksheet.setColumnWidth(LoanRepaymentConstants.REPAID_ON_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); worksheet.setColumnWidth(LoanRepaymentConstants.REPAYMENT_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); @@ -250,6 +268,7 @@ private void setLayout(Sheet worksheet) { worksheet.setColumnWidth(LoanRepaymentConstants.LOOKUP_ACCOUNT_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); worksheet.setColumnWidth(LoanRepaymentConstants.LOOKUP_PRODUCT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); worksheet.setColumnWidth(LoanRepaymentConstants.LOOKUP_PRINCIPAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.LOOKUP_TOTAL_OUTSTANDING_AMOUNT_COL,TemplatePopulateImportConstants.LARGE_COL_SIZE); worksheet.setColumnWidth(LoanRepaymentConstants.LOOKUP_LOAN_DISBURSEMENT_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); writeString(LoanRepaymentConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); writeString(LoanRepaymentConstants.CLIENT_NAME_COL, rowHeader, "Client Name*"); @@ -257,6 +276,8 @@ private void setLayout(Sheet worksheet) { writeString(LoanRepaymentConstants.LOAN_ACCOUNT_NO_COL, rowHeader, "Loan Account No.*"); writeString(LoanRepaymentConstants.PRODUCT_COL, rowHeader, "Product Name"); writeString(LoanRepaymentConstants.PRINCIPAL_COL, rowHeader, "Principal"); + writeString(LoanRepaymentConstants.TOTAL_OUTSTANDING_AMOUNT_COL,rowHeader,"Total Outstanding Amount"); + writeString(LoanRepaymentConstants.LOAN_DISBURSEMENT_DATE_COL,rowHeader,"Loan Disbursement Date"); writeString(LoanRepaymentConstants.AMOUNT_COL, rowHeader, "Amount Repaid*"); writeString(LoanRepaymentConstants.REPAID_ON_DATE_COL, rowHeader, "Date*"); writeString(LoanRepaymentConstants.REPAYMENT_TYPE_COL, rowHeader, "Type*"); @@ -270,6 +291,7 @@ private void setLayout(Sheet worksheet) { writeString(LoanRepaymentConstants.LOOKUP_ACCOUNT_NO_COL, rowHeader, "Lookup Account"); writeString(LoanRepaymentConstants.LOOKUP_PRODUCT_COL, rowHeader, "Lookup Product"); writeString(LoanRepaymentConstants.LOOKUP_PRINCIPAL_COL, rowHeader, "Lookup Principal"); + writeString(LoanRepaymentConstants.LOOKUP_TOTAL_OUTSTANDING_AMOUNT_COL,rowHeader,"Lookup Total Outstanding amount"); writeString(LoanRepaymentConstants.LOOKUP_LOAN_DISBURSEMENT_DATE_COL, rowHeader, "Lookup Loan Disbursement Date"); } diff --git a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/data/LoanAccountData.java b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/data/LoanAccountData.java index 0432cbbb2..4f88adaa1 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/data/LoanAccountData.java +++ b/fineract-provider/src/main/java/org/apache/fineract/portfolio/loanaccount/data/LoanAccountData.java @@ -1806,7 +1806,10 @@ public Long loanProductId() { } public BigDecimal getTotalOutstandingAmount() { - return this.summary.getTotalOutstanding(); + if(this.summary!=null) + return this.summary.getTotalOutstanding(); + else + return null; } public boolean isInterestRecalculationEnabled() { @@ -1871,4 +1874,5 @@ public void setDatatables(final List<DatatableData> datatables) { public String getStatusStringValue(){ return this.status.value(); } + } ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services