http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/journalentry/JournalEntriesWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/journalentry/JournalEntriesWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/journalentry/JournalEntriesWorkbookPopulator.java new file mode 100644 index 0000000..23ae440 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/journalentry/JournalEntriesWorkbookPopulator.java @@ -0,0 +1,191 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.fineract.infrastructure.bulkimport.populator.journalentry; + +import org.apache.fineract.infrastructure.bulkimport.constants.JournalEntryConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.ExtrasSheetPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.GlAccountSheetPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator; +import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.ss.SpreadsheetVersion; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.util.CellRangeAddressList; + +import java.util.ArrayList; + + +public class JournalEntriesWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + private GlAccountSheetPopulator glAccountSheetPopulator; + private ExtrasSheetPopulator extrasSheetPopulator; + + public JournalEntriesWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + GlAccountSheetPopulator glAccountSheetPopulator, ExtrasSheetPopulator extrasSheetPopulator) { + this.officeSheetPopulator = officeSheetPopulator; + this.glAccountSheetPopulator = glAccountSheetPopulator; + this.extrasSheetPopulator = extrasSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet addJournalEntriesSheet = workbook.createSheet(TemplatePopulateImportConstants.JOURNAL_ENTRY_SHEET_NAME); + officeSheetPopulator.populate(workbook,dateFormat); + glAccountSheetPopulator.populate(workbook,dateFormat); + extrasSheetPopulator.populate(workbook,dateFormat); + setRules(addJournalEntriesSheet); + setDefaults(addJournalEntriesSheet); + setLayout(addJournalEntriesSheet); + } + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(JournalEntryConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.TRANSACION_ON_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.CURRENCY_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.PAYMENT_TYPE_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.TRANSACTION_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.GL_ACCOUNT_ID_CREDIT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.AMOUNT_CREDIT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.GL_ACCOUNT_ID_DEBIT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.AMOUNT_DEBIT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.ACCOUNT_NO_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.CHECK_NO_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.ROUTING_CODE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.RECEIPT_NO_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.BANK_NO_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(JournalEntryConstants.COMMENTS_COL,TemplatePopulateImportConstants.EXTRALARGE_COL_SIZE); + + writeString(JournalEntryConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(JournalEntryConstants.TRANSACION_ON_DATE_COL, rowHeader, "Transaction On *"); + writeString(JournalEntryConstants.CURRENCY_NAME_COL, rowHeader, "Currecy Type*"); + writeString(JournalEntryConstants.PAYMENT_TYPE_ID_COL, rowHeader, "Payment Type*"); + writeString(JournalEntryConstants.TRANSACTION_ID_COL, rowHeader, "Transaction Id*"); + writeString(JournalEntryConstants.GL_ACCOUNT_ID_CREDIT_COL, rowHeader, "Credit Account Type*"); + writeString(JournalEntryConstants.AMOUNT_CREDIT_COL, rowHeader, "Amount*"); + writeString(JournalEntryConstants.GL_ACCOUNT_ID_DEBIT_COL, rowHeader, "Debit Account Type*"); + writeString(JournalEntryConstants.AMOUNT_DEBIT_COL, rowHeader, "Amount*"); + writeString(JournalEntryConstants.ACCOUNT_NO_COL,rowHeader,"Account#"); + writeString(JournalEntryConstants.CHECK_NO_COL,rowHeader,"Cheque#"); + writeString(JournalEntryConstants.ROUTING_CODE_COL,rowHeader,"Routing code"); + writeString(JournalEntryConstants.RECEIPT_NO_COL,rowHeader,"Receipt#"); + writeString(JournalEntryConstants.BANK_NO_COL,rowHeader,"Bank#"); + writeString(JournalEntryConstants.COMMENTS_COL,rowHeader,"Comments"); + + // TODO Auto-generated method stub + + } + + private void setRules(Sheet worksheet) { + + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), + JournalEntryConstants.OFFICE_NAME_COL,JournalEntryConstants. OFFICE_NAME_COL); + + CellRangeAddressList currencyCodeRange = new CellRangeAddressList( + 1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + JournalEntryConstants.CURRENCY_NAME_COL, JournalEntryConstants.CURRENCY_NAME_COL); + + CellRangeAddressList paymenttypeRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), + JournalEntryConstants.PAYMENT_TYPE_ID_COL, JournalEntryConstants.PAYMENT_TYPE_ID_COL); + + CellRangeAddressList glaccountCreditRange = new CellRangeAddressList( + 1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + JournalEntryConstants.GL_ACCOUNT_ID_CREDIT_COL, JournalEntryConstants.GL_ACCOUNT_ID_CREDIT_COL); + + CellRangeAddressList glaccountDebitRange = new CellRangeAddressList( + 1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + JournalEntryConstants.GL_ACCOUNT_ID_DEBIT_COL, JournalEntryConstants.GL_ACCOUNT_ID_DEBIT_COL); + + DataValidationHelper validationHelper = new HSSFDataValidationHelper( + (HSSFSheet) worksheet); + + setNames(worksheet); + + DataValidationConstraint officeNameConstraint = validationHelper + .createFormulaListConstraint("Office"); + DataValidationConstraint currencyCodeConstraint = validationHelper + .createFormulaListConstraint("Currency"); + DataValidationConstraint paymentTypeConstraint = validationHelper + .createFormulaListConstraint("PaymentType"); + + DataValidationConstraint glaccountConstraint = validationHelper + .createFormulaListConstraint("GlAccounts"); + + DataValidation officeValidation = validationHelper + .createValidation(officeNameConstraint, officeNameRange); + DataValidation currencyCodeValidation = validationHelper + .createValidation(currencyCodeConstraint, currencyCodeRange); + DataValidation paymentTypeValidation = validationHelper + .createValidation(paymentTypeConstraint, paymenttypeRange); + + DataValidation glaccountCreditValidation = validationHelper + .createValidation(glaccountConstraint, glaccountCreditRange); + DataValidation glaccountDebitValidation = validationHelper + .createValidation(glaccountConstraint, glaccountDebitRange); + + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(currencyCodeValidation); + worksheet.addValidationData(paymentTypeValidation); + + worksheet.addValidationData(glaccountCreditValidation); + worksheet.addValidationData(glaccountDebitValidation); + } + + private void setNames(Sheet worksheet) { + Workbook addJournalEntriesWorkbook = worksheet.getWorkbook(); + ArrayList<String> officeNames = new ArrayList<>(officeSheetPopulator.getOfficeNames()); + // Office Names + Name officeGroup = addJournalEntriesWorkbook.createName(); + officeGroup.setNameName("Office"); + officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + + (officeNames.size() + 1)); + // Payment Type Name + Name paymentTypeGroup = addJournalEntriesWorkbook.createName(); + paymentTypeGroup.setNameName("PaymentType"); + paymentTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.EXTRAS_SHEET_NAME+"!$D$2:$D$" + + (extrasSheetPopulator.getPaymentTypesSize() + 1)); + // Currency Type Name + Name currencyGroup = addJournalEntriesWorkbook.createName(); + currencyGroup.setNameName("Currency"); + currencyGroup.setRefersToFormula(TemplatePopulateImportConstants.EXTRAS_SHEET_NAME+"!$F$2:$F$" + + (extrasSheetPopulator.getCurrenciesSize() + 1)); + + // Account Name + Name glaccountGroup = addJournalEntriesWorkbook.createName(); + glaccountGroup.setNameName("GlAccounts"); + glaccountGroup.setRefersToFormula(TemplatePopulateImportConstants.GL_ACCOUNTS_SHEET_NAME+"!$B$2:$B$" + + (glAccountSheetPopulator.getGlAccountNamesSize() + 1)); + } + + private void setDefaults(Sheet worksheet) { + for (Integer rowNo = 1; rowNo < 1000; rowNo++) { + Row row = worksheet.createRow(rowNo); + } + + } + + + +}
http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loan/LoanWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loan/LoanWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loan/LoanWorkbookPopulator.java new file mode 100644 index 0000000..409eed5 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loan/LoanWorkbookPopulator.java @@ -0,0 +1,565 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.fineract.infrastructure.bulkimport.populator.loan; + +import org.apache.fineract.infrastructure.bulkimport.constants.LoanConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.*; +import org.apache.fineract.portfolio.client.data.ClientData; +import org.apache.fineract.portfolio.loanproduct.data.LoanProductData; +import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.ss.SpreadsheetVersion; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.util.CellRangeAddressList; + +import java.util.List; + +public class LoanWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + private ClientSheetPopulator clientSheetPopulator; + private GroupSheetPopulator groupSheetPopulator; + private PersonnelSheetPopulator personnelSheetPopulator; + private LoanProductSheetPopulator productSheetPopulator; + private ExtrasSheetPopulator extrasSheetPopulator; + + + public LoanWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, ClientSheetPopulator clientSheetPopulator, + GroupSheetPopulator groupSheetPopulator, PersonnelSheetPopulator personnelSheetPopulator, + LoanProductSheetPopulator productSheetPopulator, ExtrasSheetPopulator extrasSheetPopulator) { + this.officeSheetPopulator = officeSheetPopulator; + this.clientSheetPopulator = clientSheetPopulator; + this.groupSheetPopulator = groupSheetPopulator; + this.personnelSheetPopulator = personnelSheetPopulator; + this.productSheetPopulator = productSheetPopulator; + this.extrasSheetPopulator = extrasSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet loanSheet = workbook.createSheet(TemplatePopulateImportConstants.LOANS_SHEET_NAME); + officeSheetPopulator.populate(workbook,dateFormat); + clientSheetPopulator.populate(workbook,dateFormat); + groupSheetPopulator.populate(workbook,dateFormat); + personnelSheetPopulator.populate(workbook,dateFormat); + productSheetPopulator.populate(workbook,dateFormat); + extrasSheetPopulator.populate(workbook,dateFormat); + setLayout(loanSheet); + setRules(loanSheet,dateFormat); + setDefaults(loanSheet); + setClientAndGroupDateLookupTable(loanSheet, clientSheetPopulator.getClients(), groupSheetPopulator.getGroups(), + LoanConstants.LOOKUP_CLIENT_NAME_COL, LoanConstants.LOOKUP_ACTIVATION_DATE_COL, + TemplatePopulateImportConstants.CONTAINS_CLIENT_EXTERNAL_ID,dateFormat); + } + + private void setRules(Sheet worksheet,String dateFormat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanConstants.OFFICE_NAME_COL, LoanConstants.OFFICE_NAME_COL); + CellRangeAddressList loanTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanConstants.LOAN_TYPE_COL, LoanConstants.LOAN_TYPE_COL); + CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanConstants.CLIENT_NAME_COL, LoanConstants.CLIENT_NAME_COL); + CellRangeAddressList productNameRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.PRODUCT_COL, LoanConstants.PRODUCT_COL); + CellRangeAddressList loanOfficerRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.LOAN_OFFICER_NAME_COL, LoanConstants.LOAN_OFFICER_NAME_COL); + CellRangeAddressList submittedDateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.SUBMITTED_ON_DATE_COL,LoanConstants. SUBMITTED_ON_DATE_COL); + CellRangeAddressList fundNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanConstants.FUND_NAME_COL, LoanConstants.FUND_NAME_COL); + CellRangeAddressList principalRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanConstants.PRINCIPAL_COL,LoanConstants.PRINCIPAL_COL); + CellRangeAddressList noOfRepaymentsRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.NO_OF_REPAYMENTS_COL, LoanConstants.NO_OF_REPAYMENTS_COL); + CellRangeAddressList repaidFrequencyRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.REPAID_EVERY_FREQUENCY_COL, LoanConstants.REPAID_EVERY_FREQUENCY_COL); + CellRangeAddressList loanTermRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanConstants.LOAN_TERM_COL, LoanConstants.LOAN_TERM_COL); + CellRangeAddressList loanTermFrequencyRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.LOAN_TERM_FREQUENCY_COL, LoanConstants.LOAN_TERM_FREQUENCY_COL); + CellRangeAddressList interestFrequencyRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),LoanConstants.NOMINAL_INTEREST_RATE_FREQUENCY_COL, + LoanConstants.NOMINAL_INTEREST_RATE_FREQUENCY_COL); + CellRangeAddressList interestRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanConstants.NOMINAL_INTEREST_RATE_COL, LoanConstants.NOMINAL_INTEREST_RATE_COL); + CellRangeAddressList amortizationRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.AMORTIZATION_COL, LoanConstants.AMORTIZATION_COL); + CellRangeAddressList interestMethodRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.INTEREST_METHOD_COL, LoanConstants.INTEREST_METHOD_COL); + CellRangeAddressList intrestCalculationPeriodRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.INTEREST_CALCULATION_PERIOD_COL, + LoanConstants.INTEREST_CALCULATION_PERIOD_COL); + CellRangeAddressList repaymentStrategyRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.REPAYMENT_STRATEGY_COL,LoanConstants. REPAYMENT_STRATEGY_COL); + CellRangeAddressList arrearsToleranceRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.ARREARS_TOLERANCE_COL,LoanConstants. ARREARS_TOLERANCE_COL); + CellRangeAddressList graceOnPrincipalPaymentRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.GRACE_ON_PRINCIPAL_PAYMENT_COL, + LoanConstants.GRACE_ON_PRINCIPAL_PAYMENT_COL); + CellRangeAddressList graceOnInterestPaymentRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.GRACE_ON_INTEREST_PAYMENT_COL, + LoanConstants.GRACE_ON_INTEREST_PAYMENT_COL); + CellRangeAddressList graceOnInterestChargedRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.GRACE_ON_INTEREST_CHARGED_COL, + LoanConstants.GRACE_ON_INTEREST_CHARGED_COL); + CellRangeAddressList approvedDateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.APPROVED_DATE_COL, LoanConstants.APPROVED_DATE_COL); + CellRangeAddressList disbursedDateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.DISBURSED_DATE_COL, LoanConstants.DISBURSED_DATE_COL); + CellRangeAddressList paymentTypeRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.DISBURSED_PAYMENT_TYPE_COL, LoanConstants.DISBURSED_PAYMENT_TYPE_COL); + CellRangeAddressList repaymentTypeRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.REPAYMENT_TYPE_COL,LoanConstants. REPAYMENT_TYPE_COL); + CellRangeAddressList lastrepaymentDateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanConstants.LAST_REPAYMENT_DATE_COL, LoanConstants.LAST_REPAYMENT_DATE_COL); + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet); + + setNames(worksheet); + + DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint loanTypeConstraint = validationHelper + .createExplicitListConstraint(new String[] { + LoanConstants.LOAN_TYPE_INDIVIDUAL, + LoanConstants.LOAN_TYPE_GROUP, + LoanConstants.LOAN_TYPE_JLG}); + DataValidationConstraint clientNameConstraint = validationHelper.createFormulaListConstraint( + "IF($B1=\"Group\",INDIRECT(CONCATENATE(\"Group_\",$A1)),INDIRECT(CONCATENATE(\"Client_\",$A1)))"); + DataValidationConstraint productNameConstraint = validationHelper.createFormulaListConstraint("Products"); + DataValidationConstraint loanOfficerNameConstraint = validationHelper + .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$A1))"); + DataValidationConstraint submittedDateConstraint = validationHelper.createDateConstraint( + DataValidationConstraint.OperatorType.BETWEEN, + "=IF(INDIRECT(CONCATENATE(\"START_DATE_\",$E1))>VLOOKUP($C1,$AR$2:$AT$" + + (clientSheetPopulator.getClientsSize() + groupSheetPopulator.getGroupsSize() + 1) + + ",3,FALSE),INDIRECT(CONCATENATE(\"START_DATE_\",$E1)),VLOOKUP($C1,$AR$2:$AT$" + + (clientSheetPopulator.getClientsSize() + groupSheetPopulator.getGroupsSize() + 1) + + ",3,FALSE))", + "=TODAY()", dateFormat); + DataValidationConstraint approvalDateConstraint = validationHelper + .createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=$G1", "=TODAY()", dateFormat); + DataValidationConstraint disbursedDateConstraint = validationHelper + .createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=$H1", "=TODAY()", dateFormat); + DataValidationConstraint paymentTypeConstraint = validationHelper.createFormulaListConstraint("PaymentTypes"); + DataValidationConstraint fundNameConstraint = validationHelper.createFormulaListConstraint("Funds"); + DataValidationConstraint principalConstraint = validationHelper.createDecimalConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=INDIRECT(CONCATENATE(\"MIN_PRINCIPAL_\",$E1))", + "=INDIRECT(CONCATENATE(\"MAX_PRINCIPAL_\",$E1))"); + DataValidationConstraint noOfRepaymentsConstraint = validationHelper.createIntegerConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=INDIRECT(CONCATENATE(\"MIN_REPAYMENT_\",$E1))", + "=INDIRECT(CONCATENATE(\"MAX_REPAYMENT_\",$E1))"); + DataValidationConstraint frequencyConstraint = validationHelper + .createExplicitListConstraint(new String[] { "Days", "Weeks", "Months" }); + DataValidationConstraint loanTermConstraint = validationHelper + .createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "=$M1*$N1", null); + DataValidationConstraint interestFrequencyConstraint = validationHelper + .createFormulaListConstraint("INDIRECT(CONCATENATE(\"INTEREST_FREQUENCY_\",$E1))"); + DataValidationConstraint interestConstraint = validationHelper.createIntegerConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=INDIRECT(CONCATENATE(\"MIN_INTEREST_\",$E1))", + "=INDIRECT(CONCATENATE(\"MAX_INTEREST_\",$E1))"); + DataValidationConstraint amortizationConstraint = validationHelper + .createExplicitListConstraint(new String[] { "Equal principal payments", "Equal installments" }); + DataValidationConstraint interestMethodConstraint = validationHelper + .createExplicitListConstraint(new String[] { "Flat", "Declining Balance" }); + DataValidationConstraint interestCalculationPeriodConstraint = validationHelper + .createExplicitListConstraint(new String[] { "Daily", "Same as repayment period" }); + DataValidationConstraint repaymentStrategyConstraint = validationHelper.createExplicitListConstraint( + new String[] { "Penalties, Fees, Interest, Principal order", "HeavensFamily Unique", "Creocore Unique", + "Overdue/Due Fee/Int,Principal", "Principal, Interest, Penalties, Fees Order", + "Interest, Principal, Penalties, Fees Order", "Early Repayment Strategy" }); + DataValidationConstraint arrearsToleranceConstraint = validationHelper + .createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0", null); + DataValidationConstraint graceOnPrincipalPaymentConstraint = validationHelper + .createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0", null); + DataValidationConstraint graceOnInterestPaymentConstraint = validationHelper + .createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0", null); + DataValidationConstraint graceOnInterestChargedConstraint = validationHelper + .createIntegerConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "0", null); + DataValidationConstraint lastRepaymentDateConstraint = validationHelper + .createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=$I1", "=TODAY()", dateFormat); + + DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation loanTypeValidation = validationHelper.createValidation(loanTypeConstraint, loanTypeRange); + DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange); + DataValidation productNameValidation = validationHelper.createValidation(productNameConstraint, + productNameRange); + DataValidation loanOfficerValidation = validationHelper.createValidation(loanOfficerNameConstraint, + loanOfficerRange); + DataValidation fundNameValidation = validationHelper.createValidation(fundNameConstraint, fundNameRange); + DataValidation repaidFrequencyValidation = validationHelper.createValidation(frequencyConstraint, + repaidFrequencyRange); + DataValidation loanTermFrequencyValidation = validationHelper.createValidation(frequencyConstraint, + loanTermFrequencyRange); + DataValidation amortizationValidation = validationHelper.createValidation(amortizationConstraint, + amortizationRange); + DataValidation interestMethodValidation = validationHelper.createValidation(interestMethodConstraint, + interestMethodRange); + DataValidation interestCalculationPeriodValidation = validationHelper + .createValidation(interestCalculationPeriodConstraint, intrestCalculationPeriodRange); + DataValidation repaymentStrategyValidation = validationHelper.createValidation(repaymentStrategyConstraint, + repaymentStrategyRange); + DataValidation paymentTypeValidation = validationHelper.createValidation(paymentTypeConstraint, + paymentTypeRange); + DataValidation repaymentTypeValidation = validationHelper.createValidation(paymentTypeConstraint, + repaymentTypeRange); + DataValidation submittedDateValidation = validationHelper.createValidation(submittedDateConstraint, + submittedDateRange); + DataValidation approvalDateValidation = validationHelper.createValidation(approvalDateConstraint, + approvedDateRange); + DataValidation disbursedDateValidation = validationHelper.createValidation(disbursedDateConstraint, + disbursedDateRange); + DataValidation lastRepaymentDateValidation = validationHelper.createValidation(lastRepaymentDateConstraint, + lastrepaymentDateRange); + DataValidation principalValidation = validationHelper.createValidation(principalConstraint, principalRange); + DataValidation loanTermValidation = validationHelper.createValidation(loanTermConstraint, loanTermRange); + DataValidation noOfRepaymentsValidation = validationHelper.createValidation(noOfRepaymentsConstraint, + noOfRepaymentsRange); + DataValidation interestValidation = validationHelper.createValidation(interestConstraint, interestRange); + DataValidation arrearsToleranceValidation = validationHelper.createValidation(arrearsToleranceConstraint, + arrearsToleranceRange); + DataValidation graceOnPrincipalPaymentValidation = validationHelper + .createValidation(graceOnPrincipalPaymentConstraint, graceOnPrincipalPaymentRange); + DataValidation graceOnInterestPaymentValidation = validationHelper + .createValidation(graceOnInterestPaymentConstraint, graceOnInterestPaymentRange); + DataValidation graceOnInterestChargedValidation = validationHelper + .createValidation(graceOnInterestChargedConstraint, graceOnInterestChargedRange); + DataValidation interestFrequencyValidation = validationHelper.createValidation(interestFrequencyConstraint, + interestFrequencyRange); + + interestFrequencyValidation.setSuppressDropDownArrow(true); + + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(loanTypeValidation); + worksheet.addValidationData(clientValidation); + worksheet.addValidationData(productNameValidation); + worksheet.addValidationData(loanOfficerValidation); + worksheet.addValidationData(submittedDateValidation); + worksheet.addValidationData(approvalDateValidation); + worksheet.addValidationData(disbursedDateValidation); + worksheet.addValidationData(paymentTypeValidation); + worksheet.addValidationData(fundNameValidation); + worksheet.addValidationData(principalValidation); + worksheet.addValidationData(repaidFrequencyValidation); + worksheet.addValidationData(loanTermFrequencyValidation); + worksheet.addValidationData(noOfRepaymentsValidation); + worksheet.addValidationData(loanTermValidation); + worksheet.addValidationData(interestValidation); + worksheet.addValidationData(interestFrequencyValidation); + worksheet.addValidationData(amortizationValidation); + worksheet.addValidationData(interestMethodValidation); + worksheet.addValidationData(interestCalculationPeriodValidation); + worksheet.addValidationData(repaymentStrategyValidation); + worksheet.addValidationData(arrearsToleranceValidation); + worksheet.addValidationData(graceOnPrincipalPaymentValidation); + worksheet.addValidationData(graceOnInterestPaymentValidation); + worksheet.addValidationData(graceOnInterestChargedValidation); + worksheet.addValidationData(lastRepaymentDateValidation); + worksheet.addValidationData(repaymentTypeValidation); + + } + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(LoanConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LOAN_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.CLIENT_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.CLIENT_EXTERNAL_ID,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.PRODUCT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LOAN_OFFICER_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.SUBMITTED_ON_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.APPROVED_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.DISBURSED_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.DISBURSED_PAYMENT_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.FUND_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.PRINCIPAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LOAN_TERM_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LOAN_TERM_FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.NO_OF_REPAYMENTS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.REPAID_EVERY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.REPAID_EVERY_FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.NOMINAL_INTEREST_RATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.NOMINAL_INTEREST_RATE_FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.AMORTIZATION_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.INTEREST_METHOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.INTEREST_CALCULATION_PERIOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.ARREARS_TOLERANCE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.REPAYMENT_STRATEGY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.GRACE_ON_PRINCIPAL_PAYMENT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.GRACE_ON_INTEREST_PAYMENT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.GRACE_ON_INTEREST_CHARGED_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.INTEREST_CHARGED_FROM_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.FIRST_REPAYMENT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.TOTAL_AMOUNT_REPAID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LAST_REPAYMENT_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.REPAYMENT_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LOOKUP_CLIENT_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LOOKUP_CLIENT_EXTERNAL_ID,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LOOKUP_ACTIVATION_DATE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.CHARGE_ID_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.CHARGE_AMOUNT_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.CHARGE_DUE_DATE_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.CHARGE_ID_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.CHARGE_AMOUNT_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.CHARGE_DUE_DATE_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.GROUP_ID, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanConstants.LINK_ACCOUNT_ID, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + writeString(LoanConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(LoanConstants.LOAN_TYPE_COL, rowHeader, "Loan Type*"); + writeString(LoanConstants.CLIENT_NAME_COL, rowHeader, "Client/Group Name*"); + writeString(LoanConstants.CLIENT_EXTERNAL_ID,rowHeader,"Client ExternalID"); + writeString(LoanConstants.PRODUCT_COL, rowHeader, "Product*"); + writeString(LoanConstants.LOAN_OFFICER_NAME_COL, rowHeader, "Loan Officer*"); + writeString(LoanConstants.SUBMITTED_ON_DATE_COL, rowHeader, "Submitted On*"); + writeString(LoanConstants.APPROVED_DATE_COL, rowHeader, "Approved On"); + writeString(LoanConstants.DISBURSED_DATE_COL, rowHeader, "Disbursed Date"); + writeString(LoanConstants.DISBURSED_PAYMENT_TYPE_COL, rowHeader, "Payment Type*"); + writeString(LoanConstants.FUND_NAME_COL, rowHeader, "Fund Name"); + writeString(LoanConstants.PRINCIPAL_COL, rowHeader, "Principal*"); + writeString(LoanConstants.LOAN_TERM_COL, rowHeader, "Loan Term*"); + writeString(LoanConstants.NO_OF_REPAYMENTS_COL, rowHeader, "# of Repayments*"); + writeString(LoanConstants.REPAID_EVERY_COL, rowHeader, "Repaid Every*"); + writeString(LoanConstants.NOMINAL_INTEREST_RATE_COL, rowHeader, "Nominal Interest %*"); + writeString(LoanConstants.AMORTIZATION_COL, rowHeader, "Amortization*"); + writeString(LoanConstants.INTEREST_METHOD_COL, rowHeader, "Interest Method*"); + writeString(LoanConstants.INTEREST_CALCULATION_PERIOD_COL, rowHeader, "Interest Calculation Period*"); + writeString(LoanConstants.ARREARS_TOLERANCE_COL, rowHeader, "Arrears Tolerance"); + writeString(LoanConstants.REPAYMENT_STRATEGY_COL, rowHeader, "Repayment Strategy*"); + writeString(LoanConstants.GRACE_ON_PRINCIPAL_PAYMENT_COL, rowHeader, "Grace-Principal Payment"); + writeString(LoanConstants.GRACE_ON_INTEREST_PAYMENT_COL, rowHeader, "Grace-Interest Payment"); + writeString(LoanConstants.GRACE_ON_INTEREST_CHARGED_COL, rowHeader, "Interest-Free Period(s)"); + writeString(LoanConstants.INTEREST_CHARGED_FROM_COL, rowHeader, "Interest Charged From"); + writeString(LoanConstants.FIRST_REPAYMENT_COL, rowHeader, "First Repayment On"); + writeString(LoanConstants.TOTAL_AMOUNT_REPAID_COL, rowHeader, "Amount Repaid"); + writeString(LoanConstants.LAST_REPAYMENT_DATE_COL, rowHeader, "Date-Last Repayment"); + writeString(LoanConstants.REPAYMENT_TYPE_COL, rowHeader, "Repayment Type"); + writeString(LoanConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Client Name"); + writeString(LoanConstants.LOOKUP_CLIENT_EXTERNAL_ID,rowHeader,"Lookup Client ExternalID"); + writeString(LoanConstants.LOOKUP_ACTIVATION_DATE_COL, rowHeader, "Client Activation Date"); + writeString(LoanConstants.EXTERNAL_ID_COL, rowHeader, "External Id"); + writeString(LoanConstants.CHARGE_ID_1, rowHeader, "Charge Id"); + writeString(LoanConstants.CHARGE_AMOUNT_1, rowHeader, "Charged Amount"); + writeString(LoanConstants.CHARGE_DUE_DATE_1, rowHeader, "Charged On Date"); + writeString(LoanConstants.CHARGE_ID_2, rowHeader, "Charge Id"); + writeString(LoanConstants.CHARGE_AMOUNT_2, rowHeader, "Charged Amount"); + writeString(LoanConstants.CHARGE_DUE_DATE_2, rowHeader, "Charged On Date"); + writeString(LoanConstants.GROUP_ID, rowHeader, "GROUP ID"); + writeString(LoanConstants.LINK_ACCOUNT_ID, rowHeader, "Linked Account No."); + + CellStyle borderStyle = worksheet.getWorkbook().createCellStyle(); + CellStyle doubleBorderStyle = worksheet.getWorkbook().createCellStyle(); + borderStyle.setBorderBottom(CellStyle.BORDER_THIN); + doubleBorderStyle.setBorderBottom(CellStyle.BORDER_THIN); + doubleBorderStyle.setBorderRight(CellStyle.BORDER_THICK); + for (int colNo = 0; colNo < 35; colNo++) { + Cell cell = rowHeader.getCell(colNo); + if (cell == null) + rowHeader.createCell(colNo); + rowHeader.getCell(colNo).setCellStyle(borderStyle); + } + rowHeader.getCell(LoanConstants.FIRST_REPAYMENT_COL).setCellStyle(doubleBorderStyle); + rowHeader.getCell(LoanConstants.REPAYMENT_TYPE_COL).setCellStyle(doubleBorderStyle); + } + + private void setDefaults(Sheet worksheet) { + + for (Integer rowNo = 1; rowNo < 1000; rowNo++) { + Row row = worksheet.createRow(rowNo); + writeFormula(LoanConstants.CLIENT_EXTERNAL_ID, row, + "IF(ISERROR(VLOOKUP($C"+(rowNo+1)+",$AR$2:$AS$"+(clientSheetPopulator.getClients().size()+1)+",2,FALSE))," + + "\"\",(VLOOKUP($C"+(rowNo+1)+",$AR$2:$AS$"+(clientSheetPopulator.getClients().size()+1)+",2,FALSE)))"); + writeFormula(LoanConstants.FUND_NAME_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"FUND_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"FUND_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.PRINCIPAL_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"PRINCIPAL_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"PRINCIPAL_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.REPAID_EVERY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"REPAYMENT_EVERY_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"REPAYMENT_EVERY_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.REPAID_EVERY_FREQUENCY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"REPAYMENT_FREQUENCY_\",$E" + + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"REPAYMENT_FREQUENCY_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.NO_OF_REPAYMENTS_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"NO_REPAYMENT_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"NO_REPAYMENT_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.LOAN_TERM_COL, row, "IF(ISERROR($M" + (rowNo + 1) + "*$N" + (rowNo + 1) + "),\"\",$M" + + (rowNo + 1) + "*$N" + (rowNo + 1) + ")"); + writeFormula(LoanConstants.LOAN_TERM_FREQUENCY_COL, row, "$O" + (rowNo + 1)); + writeFormula(LoanConstants.NOMINAL_INTEREST_RATE_FREQUENCY_COL, row, + "IF(ISERROR(INDIRECT(CONCATENATE(\"INTEREST_FREQUENCY_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"INTEREST_FREQUENCY_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.NOMINAL_INTEREST_RATE_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"INTEREST_\",$E" + + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"INTEREST_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.AMORTIZATION_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"AMORTIZATION_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"AMORTIZATION_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.INTEREST_METHOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"INTEREST_TYPE_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"INTEREST_TYPE_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.INTEREST_CALCULATION_PERIOD_COL, row, + "IF(ISERROR(INDIRECT(CONCATENATE(\"INTEREST_CALCULATION_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"INTEREST_CALCULATION_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.ARREARS_TOLERANCE_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"ARREARS_TOLERANCE_\",$E" + + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"ARREARS_TOLERANCE_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.REPAYMENT_STRATEGY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"STRATEGY_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"STRATEGY_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.GRACE_ON_PRINCIPAL_PAYMENT_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"GRACE_PRINCIPAL_\",$E" + + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"GRACE_PRINCIPAL_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.GRACE_ON_INTEREST_PAYMENT_COL, row, + "IF(ISERROR(INDIRECT(CONCATENATE(\"GRACE_INTEREST_PAYMENT_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"GRACE_INTEREST_PAYMENT_\",$E" + (rowNo + 1) + ")))"); + writeFormula(LoanConstants.GRACE_ON_INTEREST_CHARGED_COL, row, + "IF(ISERROR(INDIRECT(CONCATENATE(\"GRACE_INTEREST_CHARGED_\",$E" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"GRACE_INTEREST_CHARGED_\",$E" + (rowNo + 1) + ")))"); + + } + } + + private void setNames(Sheet worksheet) { + Workbook loanWorkbook = worksheet.getWorkbook(); + List<String> officeNames = officeSheetPopulator.getOfficeNames(); + List<LoanProductData> products = productSheetPopulator.getProducts(); + + // Office Names + Name officeGroup = loanWorkbook.createName(); + officeGroup.setNameName("Office"); + officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (officeNames.size() + 1)); + + // Client and Loan Officer Names for each office + for (Integer i = 0; i < officeNames.size(); i++) { + Integer[] officeNameToBeginEndIndexesOfClients = clientSheetPopulator + .getOfficeNameToBeginEndIndexesOfClients().get(i); + Integer[] officeNameToBeginEndIndexesOfStaff = personnelSheetPopulator + .getOfficeNameToBeginEndIndexesOfStaff().get(i); + Integer[] officeNameToBeginEndIndexesOfGroups = groupSheetPopulator.getOfficeNameToBeginEndIndexesOfGroups() + .get(i); + Name clientName = loanWorkbook.createName(); + Name loanOfficerName = loanWorkbook.createName(); + Name groupName = loanWorkbook.createName(); + + if (officeNameToBeginEndIndexesOfStaff != null) { + loanOfficerName.setNameName("Staff_" + officeNames.get(i).trim().replaceAll("[ )(]", "_")); + loanOfficerName.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+"!$B$" + officeNameToBeginEndIndexesOfStaff[0] + ":$B$" + + officeNameToBeginEndIndexesOfStaff[1]); + } + if (officeNameToBeginEndIndexesOfClients != null) { + clientName.setNameName("Client_" + officeNames.get(i).trim().replaceAll("[ )(]", "_")); + clientName.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_SHEET_NAME+"!$B$" + officeNameToBeginEndIndexesOfClients[0] + ":$B$" + + officeNameToBeginEndIndexesOfClients[1]); + } + if (officeNameToBeginEndIndexesOfGroups != null) { + groupName.setNameName("Group_" + officeNames.get(i).trim().replaceAll("[ )(]", "_")); + groupName.setRefersToFormula(TemplatePopulateImportConstants.GROUP_SHEET_NAME+"!$B$" + officeNameToBeginEndIndexesOfGroups[0] + ":$B$" + + officeNameToBeginEndIndexesOfGroups[1]); + } + + } + + // Product Name + Name productGroup = loanWorkbook.createName(); + productGroup.setNameName("Products"); + productGroup.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$B$2:$B$" + (productSheetPopulator.getProductsSize() + 1)); + + // Fund Name + Name fundGroup = loanWorkbook.createName(); + fundGroup.setNameName("Funds"); + fundGroup.setRefersToFormula(TemplatePopulateImportConstants.EXTRAS_SHEET_NAME+"!$B$2:$B$" + (extrasSheetPopulator.getFundsSize() + 1)); + + // Payment Type Name + Name paymentTypeGroup = loanWorkbook.createName(); + paymentTypeGroup.setNameName("PaymentTypes"); + paymentTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.EXTRAS_SHEET_NAME+"!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1)); + + // Default Fund, Default Principal, Min Principal, Max Principal, + // Default No. of Repayments, Min Repayments, Max Repayments, Repayment + // Every, + // Repayment Every Frequency, Interest Rate, Min Interest Rate, Max + // Interest Rate, Interest Frequency, Amortization, Interest Type, + // Interest Calculation Period, Transaction Processing Strategy, Arrears + // Tolerance, GraceOnPrincipalPayment, GraceOnInterestPayment, + // GraceOnInterestCharged, StartDate Names for each loan product + for (Integer i = 0; i < products.size(); i++) { + Name fundName = loanWorkbook.createName(); + Name principalName = loanWorkbook.createName(); + Name minPrincipalName = loanWorkbook.createName(); + Name maxPrincipalName = loanWorkbook.createName(); + Name noOfRepaymentName = loanWorkbook.createName(); + Name minNoOfRepayment = loanWorkbook.createName(); + Name maxNoOfRepaymentName = loanWorkbook.createName(); + Name repaymentEveryName = loanWorkbook.createName(); + Name repaymentFrequencyName = loanWorkbook.createName(); + Name interestName = loanWorkbook.createName(); + Name minInterestName = loanWorkbook.createName(); + Name maxInterestName = loanWorkbook.createName(); + Name interestFrequencyName = loanWorkbook.createName(); + Name amortizationName = loanWorkbook.createName(); + Name interestTypeName = loanWorkbook.createName(); + Name interestCalculationPeriodName = loanWorkbook.createName(); + Name transactionProcessingStrategyName = loanWorkbook.createName(); + Name arrearsToleranceName = loanWorkbook.createName(); + Name graceOnPrincipalPaymentName = loanWorkbook.createName(); + Name graceOnInterestPaymentName = loanWorkbook.createName(); + Name graceOnInterestChargedName = loanWorkbook.createName(); + Name startDateName = loanWorkbook.createName(); + String productName = products.get(i).getName().replaceAll("[ ]", "_"); + fundName.setNameName("FUND_" + productName); + principalName.setNameName("PRINCIPAL_" + productName); + minPrincipalName.setNameName("MIN_PRINCIPAL_" + productName); + maxPrincipalName.setNameName("MAX_PRINCIPAL_" + productName); + noOfRepaymentName.setNameName("NO_REPAYMENT_" + productName); + minNoOfRepayment.setNameName("MIN_REPAYMENT_" + productName); + maxNoOfRepaymentName.setNameName("MAX_REPAYMENT_" + productName); + repaymentEveryName.setNameName("REPAYMENT_EVERY_" + productName); + repaymentFrequencyName.setNameName("REPAYMENT_FREQUENCY_" + productName); + interestName.setNameName("INTEREST_" + productName); + minInterestName.setNameName("MIN_INTEREST_" + productName); + maxInterestName.setNameName("MAX_INTEREST_" + productName); + interestFrequencyName.setNameName("INTEREST_FREQUENCY_" + productName); + amortizationName.setNameName("AMORTIZATION_" + productName); + interestTypeName.setNameName("INTEREST_TYPE_" + productName); + interestCalculationPeriodName.setNameName("INTEREST_CALCULATION_" + productName); + transactionProcessingStrategyName.setNameName("STRATEGY_" + productName); + arrearsToleranceName.setNameName("ARREARS_TOLERANCE_" + productName); + graceOnPrincipalPaymentName.setNameName("GRACE_PRINCIPAL_" + productName); + graceOnInterestPaymentName.setNameName("GRACE_INTEREST_PAYMENT_" + productName); + graceOnInterestChargedName.setNameName("GRACE_INTEREST_CHARGED_" + productName); + startDateName.setNameName("START_DATE_" + productName); + if (products.get(i).getFundName() != null) + fundName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$C$" + (i + 2)); + principalName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$D$" + (i + 2)); + minPrincipalName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$E$" + (i + 2)); + maxPrincipalName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$F$" + (i + 2)); + noOfRepaymentName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$G$" + (i + 2)); + minNoOfRepayment.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$H$" + (i + 2)); + maxNoOfRepaymentName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$I$" + (i + 2)); + repaymentEveryName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$J$" + (i + 2)); + repaymentFrequencyName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$K$" + (i + 2)); + interestName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$L$" + (i + 2)); + minInterestName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$M$" + (i + 2)); + maxInterestName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$N$" + (i + 2)); + interestFrequencyName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$O$" + (i + 2)); + amortizationName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$P$" + (i + 2)); + interestTypeName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$Q$" + (i + 2)); + interestCalculationPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$R$" + (i + 2)); + transactionProcessingStrategyName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$T$" + (i + 2)); + arrearsToleranceName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$S$" + (i + 2)); + graceOnPrincipalPaymentName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$U$" + (i + 2)); + graceOnInterestPaymentName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$V$" + (i + 2)); + graceOnInterestChargedName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$W$" + (i + 2)); + startDateName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$X$" + (i + 2)); + } + } + +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loanrepayment/LoanRepaymentWorkbookPopulator.java ---------------------------------------------------------------------- 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 new file mode 100644 index 0000000..04646fb --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/loanrepayment/LoanRepaymentWorkbookPopulator.java @@ -0,0 +1,277 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.fineract.infrastructure.bulkimport.populator.loanrepayment; + +import org.apache.fineract.infrastructure.bulkimport.constants.LoanRepaymentConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.ClientSheetPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.ExtrasSheetPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.comparator.LoanComparatorByStatusActive; +import org.apache.fineract.portfolio.client.data.ClientData; +import org.apache.fineract.portfolio.loanaccount.data.LoanAccountData; +import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.ss.SpreadsheetVersion; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.util.CellRangeAddressList; + +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.*; + +public class LoanRepaymentWorkbookPopulator extends AbstractWorkbookPopulator { + private OfficeSheetPopulator officeSheetPopulator; + private ClientSheetPopulator clientSheetPopulator; + private ExtrasSheetPopulator extrasSheetPopulator; + private List<LoanAccountData> allloans; + private Map<Long,String> clientIdToClientExternalId; + + public LoanRepaymentWorkbookPopulator(List<LoanAccountData> loans, OfficeSheetPopulator officeSheetPopulator, + ClientSheetPopulator clientSheetPopulator, ExtrasSheetPopulator extrasSheetPopulator) { + this.allloans = loans; + this.officeSheetPopulator = officeSheetPopulator; + this.clientSheetPopulator = clientSheetPopulator; + this.extrasSheetPopulator = extrasSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet loanRepaymentSheet = workbook.createSheet(TemplatePopulateImportConstants.LOAN_REPAYMENT_SHEET_NAME); + setLayout(loanRepaymentSheet); + officeSheetPopulator.populate(workbook,dateFormat); + clientSheetPopulator.populate(workbook,dateFormat); + extrasSheetPopulator.populate(workbook,dateFormat); + setClientIdToClientExternalId(); + populateLoansTable(loanRepaymentSheet,dateFormat); + setRules(loanRepaymentSheet,dateFormat); + setDefaults(loanRepaymentSheet); + } + + private void setClientIdToClientExternalId() { + clientIdToClientExternalId =new HashMap<>(); + List<ClientData>allclients=clientSheetPopulator.getClients(); + for (ClientData client: allclients) { + if (client.getExternalId()!=null) + 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 setRules(Sheet worksheet,String dateFormat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanRepaymentConstants.OFFICE_NAME_COL, LoanRepaymentConstants.OFFICE_NAME_COL); + CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + LoanRepaymentConstants.CLIENT_NAME_COL, LoanRepaymentConstants.CLIENT_NAME_COL); + CellRangeAddressList accountNumberRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanRepaymentConstants.LOAN_ACCOUNT_NO_COL, LoanRepaymentConstants.LOAN_ACCOUNT_NO_COL); + CellRangeAddressList repaymentTypeRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanRepaymentConstants.REPAYMENT_TYPE_COL, LoanRepaymentConstants.REPAYMENT_TYPE_COL); + CellRangeAddressList repaymentDateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), LoanRepaymentConstants.REPAID_ON_DATE_COL, LoanRepaymentConstants.REPAID_ON_DATE_COL); + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet); + + setNames(worksheet); + + DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint clientNameConstraint = validationHelper + .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Client_\",$A1))"); + DataValidationConstraint accountNumberConstraint = validationHelper.createFormulaListConstraint( + "INDIRECT(CONCATENATE(\"Account_\",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B1,\" \",\"_\"),\"(\",\"_\"),\")\",\"_\")))"); + DataValidationConstraint paymentTypeConstraint = validationHelper.createFormulaListConstraint("PaymentTypes"); + DataValidationConstraint repaymentDateConstraint = validationHelper.createDateConstraint( + DataValidationConstraint.OperatorType.BETWEEN, + "=VLOOKUP($D1,$R$2:$U$" + (allloans.size() + 1) + ",4,FALSE)", "=TODAY()", dateFormat); + + DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange); + DataValidation accountNumberValidation = validationHelper.createValidation(accountNumberConstraint, + accountNumberRange); + DataValidation repaymentTypeValidation = validationHelper.createValidation(paymentTypeConstraint, + repaymentTypeRange); + DataValidation repaymentDateValidation = validationHelper.createValidation(repaymentDateConstraint, + repaymentDateRange); + + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(clientValidation); + worksheet.addValidationData(accountNumberValidation); + worksheet.addValidationData(repaymentTypeValidation); + worksheet.addValidationData(repaymentDateValidation); + + } + + private void setNames(Sheet worksheet) { + ArrayList<String> officeNames = new ArrayList<>(officeSheetPopulator.getOfficeNames()); + Workbook loanRepaymentWorkbook = worksheet.getWorkbook(); + // Office Names + Name officeGroup = loanRepaymentWorkbook.createName(); + officeGroup.setNameName("Office"); + officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (officeNames.size() + 1)); + + // Clients Named after Offices + for (Integer i = 0; i < officeNames.size(); i++) { + Integer[] officeNameToBeginEndIndexesOfClients = clientSheetPopulator + .getOfficeNameToBeginEndIndexesOfClients().get(i); + Name name = loanRepaymentWorkbook.createName(); + if (officeNameToBeginEndIndexesOfClients != null) { + name.setNameName("Client_" + officeNames.get(i).trim().replaceAll("[ )(]", "_")); + name.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_SHEET_NAME+"!$B$" + officeNameToBeginEndIndexesOfClients[0] + ":$B$" + + officeNameToBeginEndIndexesOfClients[1]); + } + } + + // Counting clients with active loans and starting and end addresses of + // cells + HashMap<String, Integer[]> clientNameToBeginEndIndexes = new HashMap<String, Integer[]>(); + ArrayList<String> clientsWithActiveLoans = new ArrayList<String>(); + ArrayList<String> clientIdsWithActiveLoans = new ArrayList<String>(); + int startIndex = 1, endIndex = 1; + String clientName = ""; + String clientId = ""; + for (int i = 0; i < allloans.size(); i++) { + if (!clientName.equals(allloans.get(i).getClientName())) { + endIndex = i + 1; + clientNameToBeginEndIndexes.put(clientName, new Integer[] { startIndex, endIndex }); + startIndex = i + 2; + clientName = allloans.get(i).getClientName(); + clientId = allloans.get(i).getClientId().toString(); + if (!clientsWithActiveLoans.contains(clientName)) { + clientsWithActiveLoans.add(clientName); + clientIdsWithActiveLoans.add(clientId); + } + } + if (i == allloans.size() - 1) { + endIndex = i + 2; + clientNameToBeginEndIndexes.put(clientName, new Integer[] { startIndex, endIndex }); + } + } + + // 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$" + + clientNameToBeginEndIndexes.get(clientsWithActiveLoans.get(j))[1]); + } + + // Payment Type Name + Name paymentTypeGroup = loanRepaymentWorkbook.createName(); + paymentTypeGroup.setNameName("PaymentTypes"); + paymentTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.EXTRAS_SHEET_NAME+"!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1)); + } + + private void populateLoansTable(Sheet loanRepaymentSheet,String dateFormat) { + int rowIndex = 1; + Row row; + Workbook workbook = loanRepaymentSheet.getWorkbook(); + CellStyle dateCellStyle = workbook.createCellStyle(); + short df = workbook.createDataFormat().getFormat(dateFormat); + dateCellStyle.setDataFormat(df); + SimpleDateFormat outputFormat = new SimpleDateFormat(dateFormat); + SimpleDateFormat inputFormat = new SimpleDateFormat("yyyy-MM-dd"); + Date date = null; + Collections.sort(allloans,new LoanComparatorByStatusActive()); + for (LoanAccountData loan : allloans) { + row = loanRepaymentSheet.createRow(rowIndex++); + writeString(LoanRepaymentConstants.LOOKUP_CLIENT_NAME_COL, row, loan.getClientName() + "(" + loan.getClientId() + ")"); + writeString(LoanRepaymentConstants.LOOKUP_CLIENT_EXTERNAL_ID,row, clientIdToClientExternalId.get(loan.getClientId())); + 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.getDisbursementDate() != null) { + try { + date = inputFormat.parse(loan.getDisbursementDate().toString()); + } catch (ParseException e) { + e.printStackTrace(); + } + writeDate(LoanRepaymentConstants.LOOKUP_LOAN_DISBURSEMENT_DATE_COL, row, + outputFormat.format(date), dateCellStyle,dateFormat); + } + } + } + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(LoanRepaymentConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.CLIENT_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.CLIENT_EXTERNAL_ID,TemplatePopulateImportConstants.SMALL_COL_SIZE); + 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.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); + worksheet.setColumnWidth(LoanRepaymentConstants.ACCOUNT_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.CHECK_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.RECEIPT_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.ROUTING_CODE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.BANK_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.LOOKUP_CLIENT_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(LoanRepaymentConstants.LOOKUP_CLIENT_EXTERNAL_ID,TemplatePopulateImportConstants.SMALL_COL_SIZE); + 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_LOAN_DISBURSEMENT_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(LoanRepaymentConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(LoanRepaymentConstants.CLIENT_NAME_COL, rowHeader, "Client Name*"); + writeString(LoanRepaymentConstants.CLIENT_EXTERNAL_ID,rowHeader,"Client Ext.Id"); + 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.AMOUNT_COL, rowHeader, "Amount Repaid*"); + writeString(LoanRepaymentConstants.REPAID_ON_DATE_COL, rowHeader, "Date*"); + writeString(LoanRepaymentConstants.REPAYMENT_TYPE_COL, rowHeader, "Type*"); + writeString(LoanRepaymentConstants.ACCOUNT_NO_COL, rowHeader, "Account No"); + writeString(LoanRepaymentConstants.CHECK_NO_COL, rowHeader, "Check No"); + writeString(LoanRepaymentConstants.RECEIPT_NO_COL, rowHeader, "Receipt No"); + writeString(LoanRepaymentConstants.ROUTING_CODE_COL, rowHeader, "Routing Code"); + writeString(LoanRepaymentConstants.BANK_NO_COL, rowHeader, "Bank No"); + writeString(LoanRepaymentConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Lookup Client"); + writeString(LoanRepaymentConstants.LOOKUP_CLIENT_EXTERNAL_ID,rowHeader,"Lookup ClientExtId"); + 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_LOAN_DISBURSEMENT_DATE_COL, rowHeader, "Lookup Loan Disbursement Date"); + + } + +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/office/OfficeWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/office/OfficeWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/office/OfficeWorkbookPopulator.java new file mode 100644 index 0000000..977a23a --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/office/OfficeWorkbookPopulator.java @@ -0,0 +1,114 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.fineract.infrastructure.bulkimport.populator.office; + +import org.apache.fineract.infrastructure.bulkimport.constants.OfficeConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator; +import org.apache.fineract.organisation.office.data.OfficeData; +import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper; +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.ss.SpreadsheetVersion; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.util.CellRangeAddressList; + +import java.util.List; + +public class OfficeWorkbookPopulator extends AbstractWorkbookPopulator { + private List<OfficeData> offices; + + public OfficeWorkbookPopulator(List<OfficeData> offices) { + this.offices=offices; + } + + @Override + public void populate(final Workbook workbook,final String dateFormat) { + Sheet officeSheet=workbook.createSheet(TemplatePopulateImportConstants.OFFICE_SHEET_NAME); + setLayout(officeSheet); + setLookupTable(officeSheet); + setRules(officeSheet,dateFormat); + setDefaults(officeSheet); + } + + private void setLookupTable(final Sheet officeSheet) { + int rowIndex=1; + for (OfficeData office:offices) { + Row row=officeSheet.createRow(rowIndex); + writeString(OfficeConstants.LOOKUP_OFFICE_COL,row,office.name()); + writeLong(OfficeConstants.LOOKUP_OFFICE_ID_COL,row,office.getId()); + rowIndex++; + } + } + + private void setLayout(final Sheet worksheet){ + Row rowHeader=worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + worksheet.setColumnWidth(OfficeConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(OfficeConstants.PARENT_OFFICE_NAME_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(OfficeConstants.PARENT_OFFICE_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(OfficeConstants.OPENED_ON_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(OfficeConstants.EXTERNAL_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(OfficeConstants.LOOKUP_OFFICE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(OfficeConstants.LOOKUP_OFFICE_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + + writeString(OfficeConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(OfficeConstants.PARENT_OFFICE_NAME_COL, rowHeader, "Parent Office*"); + writeString(OfficeConstants.PARENT_OFFICE_ID_COL,rowHeader,"Parent OfficeId*"); + writeString(OfficeConstants.OPENED_ON_COL, rowHeader, "Opened On Date*"); + writeString(OfficeConstants.EXTERNAL_ID_COL, rowHeader, "External Id*"); + writeString(OfficeConstants.LOOKUP_OFFICE_COL, rowHeader, "Lookup Offices"); + writeString(OfficeConstants.LOOKUP_OFFICE_ID_COL,rowHeader, "Lookup OfficeId*"); + } + + private void setRules(Sheet workSheet, final String dateFormat){ + CellRangeAddressList parentOfficeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + OfficeConstants.PARENT_OFFICE_NAME_COL, OfficeConstants.PARENT_OFFICE_NAME_COL); + CellRangeAddressList OpenedOndateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + OfficeConstants.OPENED_ON_COL,OfficeConstants.OPENED_ON_COL); + + DataValidationHelper validationHelper=new HSSFDataValidationHelper((HSSFSheet) workSheet); + setNames(workSheet); + + DataValidationConstraint parentOfficeNameConstraint=validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint openDateConstraint=validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL,"=TODAY()",null,dateFormat); + + DataValidation parentOfficeValidation=validationHelper.createValidation(parentOfficeNameConstraint,parentOfficeNameRange); + DataValidation openDateValidation=validationHelper.createValidation(openDateConstraint,OpenedOndateRange); + + workSheet.addValidationData(parentOfficeValidation); + workSheet.addValidationData(openDateValidation); + } + + private void setNames(final Sheet workSheet) { + Workbook officeWorkbook=workSheet.getWorkbook(); + Name parentOffice=officeWorkbook.createName(); + parentOffice.setNameName("Office"); + parentOffice.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$H$2:$H$"+(offices.size()+1)); + } + + private void setDefaults(final Sheet worksheet) { + for (Integer rowNo = 1; rowNo < 3000; rowNo++) { + Row row = worksheet.getRow(rowNo); + if (row == null) + row = worksheet.createRow(rowNo); + writeFormula(OfficeConstants.PARENT_OFFICE_ID_COL, row, + "IF(ISERROR(VLOOKUP($B"+(rowNo+1)+",$H$2:$I$"+(offices.size()+1)+",2,FALSE)),\"\",(VLOOKUP($B"+(rowNo+1)+",$H$2:$I$"+(offices.size()+1)+",2,FALSE)))"); + } + + } +} \ No newline at end of file
