http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositTransactionWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositTransactionWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositTransactionWorkbookPopulator.java new file mode 100644 index 0000000..cd7dd9b --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositTransactionWorkbookPopulator.java @@ -0,0 +1,235 @@ +/** + * 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.fixeddeposits; + +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TransactionConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.*; +import org.apache.fineract.portfolio.savings.data.SavingsAccountData; +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; +import java.util.Collections; +import java.util.HashMap; +import java.util.List; + +public class FixedDepositTransactionWorkbookPopulator extends AbstractWorkbookPopulator { + private OfficeSheetPopulator officeSheetPopulator; + private ClientSheetPopulator clientSheetPopulator; + private ExtrasSheetPopulator extrasSheetPopulator; + + private List<SavingsAccountData>savingsAccounts; + + public FixedDepositTransactionWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + ClientSheetPopulator clientSheetPopulator, ExtrasSheetPopulator extrasSheetPopulator, + List<SavingsAccountData> savingsAccounts) { + this.officeSheetPopulator = officeSheetPopulator; + this.clientSheetPopulator = clientSheetPopulator; + this.extrasSheetPopulator = extrasSheetPopulator; + this.savingsAccounts=savingsAccounts; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet savingsTransactionSheet = workbook.createSheet(TemplatePopulateImportConstants.FIXED_DEPOSIT_TRANSACTION_SHEET_NAME); + setLayout(savingsTransactionSheet); + officeSheetPopulator.populate(workbook,dateFormat); + clientSheetPopulator.populate(workbook,dateFormat); + extrasSheetPopulator.populate(workbook,dateFormat); + populateSavingsTable(savingsTransactionSheet,dateFormat); + setRules(savingsTransactionSheet,dateFormat); + setDefaults(savingsTransactionSheet); + } + + 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(TransactionConstants.PRODUCT_COL, row, "IF(ISERROR(VLOOKUP($C"+ (rowNo+1) +",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",2,FALSE)),\"\",VLOOKUP($C"+ (rowNo+1) +",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",2,FALSE))"); + writeFormula(TransactionConstants.OPENING_BALANCE_COL, row, "IF(ISERROR(VLOOKUP($C"+ (rowNo+1) +",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",3,FALSE)),\"\",VLOOKUP($C"+ (rowNo+1) +",$Q$2:$S$" + (savingsAccounts.size() + 1) + ",3,FALSE))"); + } + } + + + private void setRules(Sheet worksheet,String dateFormat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + TransactionConstants.OFFICE_NAME_COL, TransactionConstants.OFFICE_NAME_COL); + CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + TransactionConstants.CLIENT_NAME_COL, TransactionConstants.CLIENT_NAME_COL); + CellRangeAddressList accountNumberRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + TransactionConstants.SAVINGS_ACCOUNT_NO_COL, TransactionConstants.SAVINGS_ACCOUNT_NO_COL); + CellRangeAddressList transactionTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + TransactionConstants.TRANSACTION_TYPE_COL, TransactionConstants.TRANSACTION_TYPE_COL); + CellRangeAddressList paymentTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + TransactionConstants.PAYMENT_TYPE_COL, TransactionConstants.PAYMENT_TYPE_COL); + CellRangeAddressList transactionDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + TransactionConstants.TRANSACTION_DATE_COL, TransactionConstants.TRANSACTION_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 transactionTypeConstraint = validationHelper.createExplicitListConstraint(new String[] {"Withdrawal","Deposit"}); + DataValidationConstraint paymentTypeConstraint = validationHelper.createFormulaListConstraint("PaymentTypes"); + DataValidationConstraint transactionDateConstraint = validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($C1,$Q$2:$T$" + (savingsAccounts.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 transactionTypeValidation = validationHelper.createValidation(transactionTypeConstraint, transactionTypeRange); + DataValidation paymentTypeValidation = validationHelper.createValidation(paymentTypeConstraint, paymentTypeRange); + DataValidation transactionDateValidation = validationHelper.createValidation(transactionDateConstraint, transactionDateRange); + + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(clientValidation); + worksheet.addValidationData(accountNumberValidation); + worksheet.addValidationData(transactionTypeValidation); + worksheet.addValidationData(paymentTypeValidation); + worksheet.addValidationData(transactionDateValidation); + } + + private void setNames(Sheet worksheet) { + Workbook savingsTransactionWorkbook = worksheet.getWorkbook(); + List<String> officeNames = officeSheetPopulator.getOfficeNames(); + + //Office Names + Name officeGroup = savingsTransactionWorkbook.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 = savingsTransactionWorkbook.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 savings and starting and end addresses of cells for naming + HashMap<String, Integer[]> clientNameToBeginEndIndexes = new HashMap<>(); + ArrayList<String> clientsWithActiveSavings = new ArrayList<>(); + ArrayList<Long> clientIdsWithActiveSavings = new ArrayList<>(); + int startIndex = 1, endIndex = 1; + String clientName = ""; + Long clientId = null; + for(int i = 0; i < savingsAccounts.size(); i++){ + if(!clientName.equals(savingsAccounts.get(i).getClientName())) { + endIndex = i + 1; + clientNameToBeginEndIndexes.put(clientName, new Integer[]{startIndex, endIndex}); + startIndex = i + 2; + clientName = savingsAccounts.get(i).getClientName(); + clientId = savingsAccounts.get(i).getClientId(); + clientsWithActiveSavings.add(clientName); + clientIdsWithActiveSavings.add(clientId); + } + if(i == savingsAccounts.size()-1) { + endIndex = i + 2; + clientNameToBeginEndIndexes.put(clientName, new Integer[]{startIndex, endIndex}); + } + } + + //Account Number Named after Clients + for(int j = 0; j < clientsWithActiveSavings.size(); j++) { + Name name = savingsTransactionWorkbook.createName(); + name.setNameName("Account_" + clientsWithActiveSavings.get(j).replaceAll(" ", "_") + "_" + clientIdsWithActiveSavings.get(j) + "_"); + name.setRefersToFormula(TemplatePopulateImportConstants.FIXED_DEPOSIT_TRANSACTION_SHEET_NAME+"!$Q$" + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[0] + ":$Q$" + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[1]); + } + + //Payment Type Name + Name paymentTypeGroup = savingsTransactionWorkbook.createName(); + paymentTypeGroup.setNameName("PaymentTypes"); + paymentTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.EXTRAS_SHEET_NAME+"!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1)); + } + + private void populateSavingsTable(Sheet savingsTransactionSheet,String dateFormat) { + Workbook workbook = savingsTransactionSheet.getWorkbook(); + CellStyle dateCellStyle = workbook.createCellStyle(); + short df = workbook.createDataFormat().getFormat(dateFormat); + dateCellStyle.setDataFormat(df); + int rowIndex = 1; + Row row; + Collections.sort(savingsAccounts, SavingsAccountData.ClientNameComparator); + for(SavingsAccountData savingsAccount : savingsAccounts) { + row = savingsTransactionSheet.createRow(rowIndex++); + writeString(TransactionConstants.LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName() + "(" + savingsAccount.getClientId() + ")"); + writeLong(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo())); + writeString(TransactionConstants.LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName()); + if(savingsAccount.getMinRequiredOpeningBalance() != null) + writeBigDecimal(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance()); + writeDate(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, row,"" + + savingsAccount.getTimeline().getActivatedOnDate().getDayOfMonth() + "/" + + savingsAccount.getTimeline().getActivatedOnDate().getMonthOfYear() + "/" + + savingsAccount.getTimeline().getActivatedOnDate().getYear() , dateCellStyle,dateFormat); + } + } + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(TransactionConstants.OFFICE_NAME_COL, 4000); + worksheet.setColumnWidth(TransactionConstants.CLIENT_NAME_COL, 5000); + worksheet.setColumnWidth(TransactionConstants.SAVINGS_ACCOUNT_NO_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.PRODUCT_COL, 4000); + worksheet.setColumnWidth(TransactionConstants.OPENING_BALANCE_COL, 4000); + worksheet.setColumnWidth(TransactionConstants.TRANSACTION_TYPE_COL, 3300); + worksheet.setColumnWidth(TransactionConstants.AMOUNT_COL, 4000); + worksheet.setColumnWidth(TransactionConstants.TRANSACTION_DATE_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.PAYMENT_TYPE_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.ACCOUNT_NO_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.CHECK_NO_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.RECEIPT_NO_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.ROUTING_CODE_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.BANK_NO_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.LOOKUP_CLIENT_NAME_COL, 5000); + worksheet.setColumnWidth(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.LOOKUP_PRODUCT_COL, 3000); + worksheet.setColumnWidth(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, 3700); + worksheet.setColumnWidth(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, 3500); + writeString(TransactionConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(TransactionConstants.CLIENT_NAME_COL, rowHeader, "Client Name*"); + writeString(TransactionConstants.SAVINGS_ACCOUNT_NO_COL, rowHeader, "Account No.*"); + writeString(TransactionConstants.PRODUCT_COL, rowHeader, "Product Name"); + writeString(TransactionConstants.OPENING_BALANCE_COL, rowHeader, "Opening Balance"); + writeString(TransactionConstants.TRANSACTION_TYPE_COL, rowHeader, "Transaction Type*"); + writeString(TransactionConstants.AMOUNT_COL, rowHeader, "Amount*"); + writeString(TransactionConstants.TRANSACTION_DATE_COL, rowHeader, "Date*"); + writeString(TransactionConstants.PAYMENT_TYPE_COL, rowHeader, "Type*"); + writeString(TransactionConstants.ACCOUNT_NO_COL, rowHeader, "Account No"); + writeString(TransactionConstants.CHECK_NO_COL, rowHeader, "Check No"); + writeString(TransactionConstants.RECEIPT_NO_COL, rowHeader, "Receipt No"); + writeString(TransactionConstants.ROUTING_CODE_COL, rowHeader, "Routing Code"); + writeString(TransactionConstants.BANK_NO_COL, rowHeader, "Bank No"); + writeString(TransactionConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Lookup Client"); + writeString(TransactionConstants.LOOKUP_ACCOUNT_NO_COL, rowHeader, "Lookup Account"); + writeString(TransactionConstants.LOOKUP_PRODUCT_COL, rowHeader, "Lookup Product"); + writeString(TransactionConstants.LOOKUP_OPENING_BALANCE_COL, rowHeader, "Lookup Opening Balance"); + writeString(TransactionConstants.LOOKUP_SAVINGS_ACTIVATION_DATE_COL, rowHeader, "Lookup Savings Activation Date"); + } +}
http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositWorkbookPopulator.java new file mode 100644 index 0000000..9afe7dc --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/fixeddeposits/FixedDepositWorkbookPopulator.java @@ -0,0 +1,349 @@ +/** + * 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.fixeddeposits; + +import org.apache.fineract.infrastructure.bulkimport.constants.FixedDepositConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.*; +import org.apache.fineract.portfolio.savings.data.FixedDepositProductData; +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 FixedDepositWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + private ClientSheetPopulator clientSheetPopulator; + private PersonnelSheetPopulator personnelSheetPopulator; + private FixedDepositProductSheetPopulator productSheetPopulator; + + + + public FixedDepositWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + ClientSheetPopulator clientSheetPopulator, PersonnelSheetPopulator personnelSheetPopulator, + FixedDepositProductSheetPopulator fixedDepositProductSheetPopulator) { + this.officeSheetPopulator = officeSheetPopulator; + this.clientSheetPopulator = clientSheetPopulator; + this.personnelSheetPopulator = personnelSheetPopulator; + this.productSheetPopulator = fixedDepositProductSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet fixedDepositSheet = workbook.createSheet(TemplatePopulateImportConstants.FIXED_DEPOSIT_SHEET_NAME); + officeSheetPopulator.populate(workbook,dateFormat); + clientSheetPopulator.populate(workbook,dateFormat); + personnelSheetPopulator.populate(workbook,dateFormat); + productSheetPopulator.populate(workbook,dateFormat); + setRules(fixedDepositSheet,dateFormat); + setDefaults(fixedDepositSheet,dateFormat); + setClientAndGroupDateLookupTable(fixedDepositSheet, clientSheetPopulator.getClients(), null, + FixedDepositConstants.LOOKUP_CLIENT_NAME_COL,FixedDepositConstants.LOOKUP_ACTIVATION_DATE_COL,!TemplatePopulateImportConstants.CONTAINS_CLIENT_EXTERNAL_ID,dateFormat); + setLayout(fixedDepositSheet); + } + + private void setRules(Sheet worksheet,String dateFormat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.OFFICE_NAME_COL,FixedDepositConstants.OFFICE_NAME_COL); + CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.CLIENT_NAME_COL,FixedDepositConstants.CLIENT_NAME_COL); + CellRangeAddressList productNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.PRODUCT_COL, FixedDepositConstants.PRODUCT_COL); + CellRangeAddressList fieldOfficerRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.FIELD_OFFICER_NAME_COL, FixedDepositConstants.FIELD_OFFICER_NAME_COL); + CellRangeAddressList submittedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.SUBMITTED_ON_DATE_COL, FixedDepositConstants.SUBMITTED_ON_DATE_COL); + CellRangeAddressList approvedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.APPROVED_DATE_COL, FixedDepositConstants.APPROVED_DATE_COL); + CellRangeAddressList activationDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.ACTIVATION_DATE_COL, FixedDepositConstants.ACTIVATION_DATE_COL); + CellRangeAddressList interestCompudingPeriodRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL, FixedDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL); + CellRangeAddressList interestPostingPeriodRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.INTEREST_POSTING_PERIOD_COL, FixedDepositConstants.INTEREST_POSTING_PERIOD_COL); + CellRangeAddressList interestCalculationRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.INTEREST_CALCULATION_COL, FixedDepositConstants.INTEREST_CALCULATION_COL); + CellRangeAddressList interestCalculationDaysInYearRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), FixedDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, + FixedDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL); + CellRangeAddressList lockinPeriodFrequencyRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.LOCKIN_PERIOD_FREQUENCY_COL, FixedDepositConstants.LOCKIN_PERIOD_FREQUENCY_COL); + CellRangeAddressList depositAmountRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.DEPOSIT_AMOUNT_COL, FixedDepositConstants.DEPOSIT_AMOUNT_COL); + CellRangeAddressList depositPeriodTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + FixedDepositConstants.DEPOSIT_PERIOD_FREQUENCY_COL, FixedDepositConstants.DEPOSIT_PERIOD_FREQUENCY_COL); + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet); + + setNames(worksheet); + + DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint clientNameConstraint = validationHelper + .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Client_\",$A1))"); + DataValidationConstraint productNameConstraint = validationHelper.createFormulaListConstraint("Products"); + DataValidationConstraint fieldOfficerNameConstraint = validationHelper + .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$A1))"); + DataValidationConstraint submittedDateConstraint = validationHelper.createDateConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($B1,$AF$2:$AG$" + + (clientSheetPopulator.getClientsSize() + 1) + ",2,FALSE)", "=TODAY()", + dateFormat); + DataValidationConstraint approvalDateConstraint = validationHelper.createDateConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=$E1", "=TODAY()", dateFormat); + DataValidationConstraint activationDateConstraint = validationHelper.createDateConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=$F1", "=TODAY()", dateFormat); + DataValidationConstraint interestCompudingPeriodConstraint = validationHelper. + createExplicitListConstraint(new String[] { + TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_DAILY, + TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_MONTHLY, + TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_QUARTERLY, + TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_SEMI_ANNUALLY, + TemplatePopulateImportConstants.INTEREST_COMPOUNDING_PERIOD_ANNUALLY }); + DataValidationConstraint interestPostingPeriodConstraint = validationHelper.createExplicitListConstraint(new String[] { + TemplatePopulateImportConstants.INTEREST_POSTING_PERIOD_MONTHLY, + TemplatePopulateImportConstants.INTEREST_POSTING_PERIOD_QUARTERLY, + TemplatePopulateImportConstants.INTEREST_POSTING_PERIOD_BIANUALLY, + TemplatePopulateImportConstants.INTEREST_POSTING_PERIOD_ANNUALLY }); + DataValidationConstraint interestCalculationConstraint = validationHelper.createExplicitListConstraint(new String[] { + TemplatePopulateImportConstants.INTEREST_CAL_DAILY_BALANCE, + TemplatePopulateImportConstants.INTEREST_CAL_AVG_BALANCE }); + DataValidationConstraint interestCalculationDaysInYearConstraint = validationHelper.createExplicitListConstraint(new String[] { + TemplatePopulateImportConstants.INTEREST_CAL_DAYS_IN_YEAR_360, + TemplatePopulateImportConstants.INTEREST_CAL_DAYS_IN_YEAR_365 }); + DataValidationConstraint frequency = validationHelper.createExplicitListConstraint(new String[] { + TemplatePopulateImportConstants.FREQUENCY_DAYS, + TemplatePopulateImportConstants.FREQUENCY_WEEKS, + TemplatePopulateImportConstants.FREQUENCY_MONTHS, + TemplatePopulateImportConstants.FREQUENCY_YEARS }); + DataValidationConstraint depositConstraint = validationHelper.createDecimalConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, "=INDIRECT(CONCATENATE(\"Min_Deposit_\",$C1))", null); + + DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange); + DataValidation productNameValidation = validationHelper.createValidation(productNameConstraint, productNameRange); + DataValidation fieldOfficerValidation = validationHelper.createValidation(fieldOfficerNameConstraint, fieldOfficerRange); + DataValidation interestCompudingPeriodValidation = validationHelper.createValidation(interestCompudingPeriodConstraint, + interestCompudingPeriodRange); + DataValidation interestPostingPeriodValidation = validationHelper.createValidation(interestPostingPeriodConstraint, + interestPostingPeriodRange); + DataValidation interestCalculationValidation = validationHelper.createValidation(interestCalculationConstraint, + interestCalculationRange); + DataValidation interestCalculationDaysInYearValidation = validationHelper.createValidation( + interestCalculationDaysInYearConstraint, interestCalculationDaysInYearRange); + DataValidation lockinPeriodFrequencyValidation = validationHelper.createValidation(frequency, + lockinPeriodFrequencyRange); + DataValidation depositPeriodTypeValidation = validationHelper.createValidation(frequency, + depositPeriodTypeRange); + DataValidation submittedDateValidation = validationHelper.createValidation(submittedDateConstraint, submittedDateRange); + DataValidation approvalDateValidation = validationHelper.createValidation(approvalDateConstraint, approvedDateRange); + DataValidation activationDateValidation = validationHelper.createValidation(activationDateConstraint, activationDateRange); + DataValidation depositAmountValidation = validationHelper.createValidation(depositConstraint, depositAmountRange); + + + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(clientValidation); + worksheet.addValidationData(productNameValidation); + worksheet.addValidationData(fieldOfficerValidation); + worksheet.addValidationData(submittedDateValidation); + worksheet.addValidationData(approvalDateValidation); + worksheet.addValidationData(activationDateValidation); + worksheet.addValidationData(interestCompudingPeriodValidation); + worksheet.addValidationData(interestPostingPeriodValidation); + worksheet.addValidationData(interestCalculationValidation); + worksheet.addValidationData(interestCalculationDaysInYearValidation); + worksheet.addValidationData(lockinPeriodFrequencyValidation); + worksheet.addValidationData(depositPeriodTypeValidation); + worksheet.addValidationData(depositAmountValidation); + + } + + private void setNames(Sheet worksheet) { + Workbook savingsWorkbook = worksheet.getWorkbook(); + List<String> officeNames = officeSheetPopulator.getOfficeNames(); + List<FixedDepositProductData> products = productSheetPopulator.getProducts(); + + // Office Names + Name officeGroup = savingsWorkbook.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); + Name clientName = savingsWorkbook.createName(); + Name fieldOfficerName = savingsWorkbook.createName(); + if (officeNameToBeginEndIndexesOfStaff != null) { + fieldOfficerName.setNameName("Staff_" + officeNames.get(i).trim().replaceAll("[ )(]", "_")); + fieldOfficerName.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]); + } + } + + // Product Name + Name productGroup = savingsWorkbook.createName(); + productGroup.setNameName("Products"); + productGroup.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$B$2:$B$" + (productSheetPopulator.getProductsSize() + 1)); + + // Default Interest Rate, Interest Compounding Period, Interest Posting + // Period, Interest Calculation, Interest Calculation Days In Year, + // Minimum Deposit, Lockin Period, Lockin Period Frequency + // Names for each product + for (Integer i = 0; i < products.size(); i++) { + Name interestCompoundingPeriodName = savingsWorkbook.createName(); + Name interestPostingPeriodName = savingsWorkbook.createName(); + Name interestCalculationName = savingsWorkbook.createName(); + Name daysInYearName = savingsWorkbook.createName(); + Name lockinPeriodName = savingsWorkbook.createName(); + Name lockinPeriodFrequencyName = savingsWorkbook.createName(); + Name depositName = savingsWorkbook.createName(); + Name minDepositName = savingsWorkbook.createName(); + Name maxDepositName = savingsWorkbook.createName(); + Name minDepositTermTypeName = savingsWorkbook.createName(); + + FixedDepositProductData product = products.get(i); + String productName = product.getName().replaceAll("[ ]", "_"); + + interestCompoundingPeriodName.setNameName("Interest_Compouding_" + productName); + interestPostingPeriodName.setNameName("Interest_Posting_" + productName); + interestCalculationName.setNameName("Interest_Calculation_" + productName); + daysInYearName.setNameName("Days_In_Year_" + productName); + minDepositName.setNameName("Min_Deposit_" + productName); + maxDepositName.setNameName("Max_Deposit_" + productName); + depositName.setNameName("Deposit_" + productName); + interestCompoundingPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$E$" + (i + 2)); + interestPostingPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$F$" + (i + 2)); + interestCalculationName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$G$" + (i + 2)); + daysInYearName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$H$" + (i + 2)); + depositName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$N$" + (i + 2)); + minDepositName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$L$" + (i + 2)); + maxDepositName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$M$" + (i + 2)); + + if(product.getMinDepositTermType() != null) { + minDepositTermTypeName.setNameName("Term_Type_" + productName); + minDepositTermTypeName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$P$" + (i + 2)); + } + if (product.getLockinPeriodFrequency() != null) { + lockinPeriodName.setNameName("Lockin_Period_" + productName); + lockinPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$I$" + (i + 2)); + } + if (product.getLockinPeriodFrequencyType() != null) { + lockinPeriodFrequencyName.setNameName("Lockin_Frequency_" + productName); + lockinPeriodFrequencyName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$J$" + (i + 2)); + } + } + } + + private void setDefaults(Sheet worksheet,String dateFormat) { + Workbook workbook = worksheet.getWorkbook(); + CellStyle dateCellStyle = workbook.createCellStyle(); + short df = workbook.createDataFormat().getFormat(dateFormat); + dateCellStyle.setDataFormat(df); + try { + for (Integer rowNo = 1; rowNo < 1000; rowNo++) { + Row row = worksheet.createRow(rowNo); + writeFormula(FixedDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Compouding_\",$C" + + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Compouding_\",$C" + (rowNo + 1) + ")))"); + writeFormula(FixedDepositConstants.INTEREST_POSTING_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Posting_\",$C" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Posting_\",$C" + (rowNo + 1) + ")))"); + writeFormula(FixedDepositConstants.INTEREST_CALCULATION_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Calculation_\",$C" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Calculation_\",$C" + (rowNo + 1) + ")))"); + writeFormula(FixedDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Days_In_Year_\",$C" + + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"Days_In_Year_\",$C" + (rowNo + 1) + ")))"); + writeFormula(FixedDepositConstants.LOCKIN_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Lockin_Period_\",$C" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Lockin_Period_\",$C" + (rowNo + 1) + ")))"); + writeFormula(FixedDepositConstants.LOCKIN_PERIOD_FREQUENCY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Lockin_Frequency_\",$C" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Lockin_Frequency_\",$C" + (rowNo + 1) + ")))"); + writeFormula(FixedDepositConstants.DEPOSIT_AMOUNT_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Deposit_\",$C" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Deposit_\",$C" + (rowNo + 1) + ")))"); + writeFormula(FixedDepositConstants.DEPOSIT_PERIOD_FREQUENCY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Term_Type_\",$C" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Term_Type_\",$C" + (rowNo + 1) + ")))"); + } + } catch (RuntimeException re) { + re.printStackTrace(); + } + } + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(FixedDepositConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.CLIENT_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.PRODUCT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.FIELD_OFFICER_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.SUBMITTED_ON_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.APPROVED_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.INTEREST_POSTING_PERIOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.INTEREST_CALCULATION_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.LOCKIN_PERIOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.LOCKIN_PERIOD_FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.DEPOSIT_AMOUNT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.DEPOSIT_PERIOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.DEPOSIT_PERIOD_FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + + worksheet.setColumnWidth(FixedDepositConstants.CHARGE_ID_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.CHARGE_AMOUNT_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.CHARGE_DUE_DATE_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.CHARGE_ID_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.CHARGE_AMOUNT_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.CHARGE_DUE_DATE_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + worksheet.setColumnWidth(FixedDepositConstants.LOOKUP_CLIENT_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(FixedDepositConstants.LOOKUP_ACTIVATION_DATE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + writeString(FixedDepositConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(FixedDepositConstants.CLIENT_NAME_COL, rowHeader, "Client Name*"); + writeString(FixedDepositConstants.PRODUCT_COL, rowHeader, "Product*"); + writeString(FixedDepositConstants.FIELD_OFFICER_NAME_COL, rowHeader, "Field Officer*"); + writeString(FixedDepositConstants.SUBMITTED_ON_DATE_COL, rowHeader, "Submitted On*"); + writeString(FixedDepositConstants.APPROVED_DATE_COL, rowHeader, "Approved On*"); + writeString(FixedDepositConstants.ACTIVATION_DATE_COL, rowHeader, "Activation Date*"); + writeString(FixedDepositConstants.INTEREST_COMPOUNDING_PERIOD_COL, rowHeader, "Interest Compounding Period*"); + writeString(FixedDepositConstants.INTEREST_POSTING_PERIOD_COL, rowHeader, "Interest Posting Period*"); + writeString(FixedDepositConstants.INTEREST_CALCULATION_COL, rowHeader, "Interest Calculated*"); + writeString(FixedDepositConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, rowHeader, "# Days in Year*"); + writeString(FixedDepositConstants.LOCKIN_PERIOD_COL, rowHeader, "Locked In For"); + writeString(FixedDepositConstants.DEPOSIT_AMOUNT_COL, rowHeader, "Deposit Amount"); + writeString(FixedDepositConstants.DEPOSIT_PERIOD_COL, rowHeader, "Deposit Period"); + writeString(FixedDepositConstants.EXTERNAL_ID_COL, rowHeader, "External Id"); + + writeString(FixedDepositConstants.CHARGE_ID_1,rowHeader,"Charge Id"); + writeString(FixedDepositConstants.CHARGE_AMOUNT_1, rowHeader, "Charged Amount"); + writeString(FixedDepositConstants.CHARGE_DUE_DATE_1, rowHeader, "Charged On Date"); + writeString(FixedDepositConstants.CHARGE_ID_2,rowHeader,"Charge Id"); + writeString(FixedDepositConstants.CHARGE_AMOUNT_2, rowHeader, "Charged Amount"); + writeString(FixedDepositConstants.CHARGE_DUE_DATE_2, rowHeader, "Charged On Date"); + writeString(FixedDepositConstants.CLOSED_ON_DATE, rowHeader, "Close on Date"); + writeString(FixedDepositConstants.ON_ACCOUNT_CLOSURE_ID,rowHeader,"Action(Account Transfer(200) or cash(100) "); + writeString(FixedDepositConstants.TO_SAVINGS_ACCOUNT_ID,rowHeader, "Transfered Account No."); + writeString(FixedDepositConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Client Name"); + writeString(FixedDepositConstants.LOOKUP_ACTIVATION_DATE_COL, rowHeader, "Client Activation Date"); + } + +} http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/group/GroupsWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/group/GroupsWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/group/GroupsWorkbookPopulator.java new file mode 100644 index 0000000..038e108 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/group/GroupsWorkbookPopulator.java @@ -0,0 +1,254 @@ +/** + * 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.group; + +import org.apache.fineract.infrastructure.bulkimport.constants.GroupConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.*; +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 GroupsWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + private PersonnelSheetPopulator personnelSheetPopulator; + private CenterSheetPopulator centerSheetPopulator; + private ClientSheetPopulator clientSheetPopulator; + + public GroupsWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + PersonnelSheetPopulator personnelSheetPopulator, CenterSheetPopulator centerSheetPopulator, + ClientSheetPopulator clientSheetPopulator) { + this.officeSheetPopulator = officeSheetPopulator; + this.personnelSheetPopulator = personnelSheetPopulator; + this.centerSheetPopulator = centerSheetPopulator; + this.clientSheetPopulator = clientSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet groupSheet = workbook.createSheet(TemplatePopulateImportConstants.GROUP_SHEET_NAME); + personnelSheetPopulator.populate(workbook,dateFormat); + officeSheetPopulator.populate(workbook,dateFormat); + centerSheetPopulator.populate(workbook,dateFormat); + clientSheetPopulator.populate(workbook,dateFormat); + setLayout(groupSheet); + setLookupTable(groupSheet,dateFormat); + setRules(groupSheet,dateFormat); + + } + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(GroupConstants.NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.STAFF_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.CENTER_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.ACTIVE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.SUBMITTED_ON_DATE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.MEETING_START_DATE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.IS_REPEATING_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.INTERVAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.REPEATS_ON_DAY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.STATUS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.GROUP_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.FAILURE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.CLIENT_NAMES_STARTING_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.LOOKUP_OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.LOOKUP_OFFICE_OPENING_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.LOOKUP_REPEAT_NORMAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.LOOKUP_REPEAT_MONTHLY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GroupConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + + writeString(GroupConstants.NAME_COL, rowHeader, "Group Name*"); + writeString(GroupConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(GroupConstants.STAFF_NAME_COL, rowHeader, "Staff Name*"); + writeString(GroupConstants.CENTER_NAME_COL, rowHeader, "Center Name"); + writeString(GroupConstants.EXTERNAL_ID_COL, rowHeader, "External ID"); + writeString(GroupConstants.ACTIVE_COL, rowHeader, "Active*"); + writeString(GroupConstants.ACTIVATION_DATE_COL, rowHeader, "Activation Date*"); + writeString(GroupConstants.SUBMITTED_ON_DATE_COL,rowHeader,"Submitted On Date *"); + writeString(GroupConstants.MEETING_START_DATE_COL, rowHeader, "Meeting Start Date* (On or After)"); + writeString(GroupConstants.IS_REPEATING_COL, rowHeader, "Repeat*"); + writeString(GroupConstants.FREQUENCY_COL, rowHeader, "Frequency*"); + writeString(GroupConstants.INTERVAL_COL, rowHeader, "Interval*"); + writeString(GroupConstants.REPEATS_ON_DAY_COL, rowHeader, "Repeats On*"); + writeString(GroupConstants.CLIENT_NAMES_STARTING_COL, rowHeader, "Client Names* (Enter in consecutive cells horizontally)"); + writeString(GroupConstants.LOOKUP_OFFICE_NAME_COL, rowHeader, "Office Name"); + writeString(GroupConstants.LOOKUP_OFFICE_OPENING_DATE_COL, rowHeader, "Opening Date"); + writeString(GroupConstants.LOOKUP_REPEAT_NORMAL_COL, rowHeader, "Repeat Normal Range"); + writeString(GroupConstants.LOOKUP_REPEAT_MONTHLY_COL, rowHeader, "Repeat Monthly Range"); + writeString(GroupConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, rowHeader, "If Repeat Weekly Range"); + + } + private void setLookupTable(Sheet groupSheet,String dateFormat) { + setOfficeDateLookupTable(groupSheet, officeSheetPopulator.getOffices(),GroupConstants.LOOKUP_OFFICE_NAME_COL, + GroupConstants. LOOKUP_OFFICE_OPENING_DATE_COL,dateFormat); + int rowIndex; + for(rowIndex = 1; rowIndex <= 11; rowIndex++) { + Row row = groupSheet.getRow(rowIndex); + if(row == null) + row = groupSheet.createRow(rowIndex); + writeInt(GroupConstants.LOOKUP_REPEAT_MONTHLY_COL, row, rowIndex); + } + for(rowIndex = 1; rowIndex <= 3; rowIndex++) + writeInt(GroupConstants.LOOKUP_REPEAT_NORMAL_COL, groupSheet.getRow(rowIndex), rowIndex); + String[] days = new String[]{ + TemplatePopulateImportConstants.MONDAY, + TemplatePopulateImportConstants.TUESDAY, + TemplatePopulateImportConstants.WEDNESDAY, + TemplatePopulateImportConstants.THURSDAY, + TemplatePopulateImportConstants.FRIDAY, + TemplatePopulateImportConstants.SATURDAY, + TemplatePopulateImportConstants.SUNDAY}; + for(rowIndex = 1; rowIndex <= 7; rowIndex++) + writeString(GroupConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, groupSheet.getRow(rowIndex), days[rowIndex-1]); + } + + private void setRules(Sheet worksheet,String dateFormat){ + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.OFFICE_NAME_COL, GroupConstants.OFFICE_NAME_COL); + CellRangeAddressList staffNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.STAFF_NAME_COL, GroupConstants.STAFF_NAME_COL); + CellRangeAddressList centerNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.CENTER_NAME_COL, GroupConstants.CENTER_NAME_COL); + CellRangeAddressList activeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.ACTIVE_COL, GroupConstants.ACTIVE_COL); + CellRangeAddressList activationDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.ACTIVATION_DATE_COL,GroupConstants.ACTIVATION_DATE_COL); + CellRangeAddressList submittedOnDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.SUBMITTED_ON_DATE_COL,GroupConstants.SUBMITTED_ON_DATE_COL); + CellRangeAddressList meetingStartDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.MEETING_START_DATE_COL,GroupConstants.MEETING_START_DATE_COL); + CellRangeAddressList isRepeatRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.IS_REPEATING_COL, GroupConstants.IS_REPEATING_COL); + CellRangeAddressList repeatsRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants. FREQUENCY_COL, GroupConstants.FREQUENCY_COL); + CellRangeAddressList repeatsEveryRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.INTERVAL_COL,GroupConstants. INTERVAL_COL); + CellRangeAddressList repeatsOnRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GroupConstants.REPEATS_ON_DAY_COL, GroupConstants.REPEATS_ON_DAY_COL); + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet)worksheet); + List<OfficeData> offices = officeSheetPopulator.getOffices(); + setNames(worksheet, offices); + + DataValidationConstraint centerNameConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Center_\",$B1))"); + DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint staffNameConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$B1))"); + DataValidationConstraint booleanConstraint = validationHelper.createExplicitListConstraint(new String[]{"True", "False"}); + DataValidationConstraint activationDateConstraint = validationHelper.createDateConstraint + (DataValidationConstraint.OperatorType.BETWEEN, + "=VLOOKUP($B1,$IR$2:$IS" + (offices.size() + 1)+",2,FALSE)", "=TODAY()", dateFormat); + DataValidationConstraint submittedOnDateConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL, + "=$G1" ,null,dateFormat); + DataValidationConstraint meetingStartDateConstraint = validationHelper. + createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, + "=$G1", "=TODAY()", dateFormat); + DataValidationConstraint repeatsConstraint = validationHelper.createExplicitListConstraint(new String[]{ + TemplatePopulateImportConstants.FREQUENCY_DAILY, + TemplatePopulateImportConstants.FREQUENCY_WEEKLY, + TemplatePopulateImportConstants.FREQUENCY_MONTHLY, + TemplatePopulateImportConstants.FREQUENCY_YEARLY}); + DataValidationConstraint repeatsEveryConstraint = validationHelper.createFormulaListConstraint("INDIRECT($K1)"); + DataValidationConstraint repeatsOnConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE($K1,\"_DAYS\"))"); + + DataValidation centerValidation=validationHelper.createValidation(centerNameConstraint, centerNameRange); + DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation staffValidation = validationHelper.createValidation(staffNameConstraint, staffNameRange); + DataValidation activationDateValidation = validationHelper.createValidation(activationDateConstraint, activationDateRange); + DataValidation activeValidation = validationHelper.createValidation(booleanConstraint, activeRange); + DataValidation submittedOnDateValidation=validationHelper.createValidation(submittedOnDateConstraint,submittedOnDateRange); + DataValidation meetingStartDateValidation = validationHelper.createValidation(meetingStartDateConstraint, meetingStartDateRange); + DataValidation isRepeatValidation = validationHelper.createValidation(booleanConstraint, isRepeatRange); + DataValidation repeatsValidation = validationHelper.createValidation(repeatsConstraint, repeatsRange); + DataValidation repeatsEveryValidation = validationHelper.createValidation(repeatsEveryConstraint, repeatsEveryRange); + DataValidation repeatsOnValidation = validationHelper.createValidation(repeatsOnConstraint, repeatsOnRange); + + worksheet.addValidationData(centerValidation); + worksheet.addValidationData(activeValidation); + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(staffValidation); + worksheet.addValidationData(activationDateValidation); + worksheet.addValidationData(submittedOnDateValidation); + worksheet.addValidationData(meetingStartDateValidation); + worksheet.addValidationData(isRepeatValidation); + worksheet.addValidationData(repeatsValidation); + worksheet.addValidationData(repeatsEveryValidation); + worksheet.addValidationData(repeatsOnValidation); + } + + private void setNames(Sheet worksheet, List<OfficeData> offices) { + Workbook centerWorkbook = worksheet.getWorkbook(); + Name officeCenter = centerWorkbook.createName(); + officeCenter.setNameName("Office"); + officeCenter.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (offices.size() + 1)); + + + //Repeat constraint names + Name repeatsDaily = centerWorkbook.createName(); + repeatsDaily.setNameName("Daily"); + repeatsDaily.setRefersToFormula(TemplatePopulateImportConstants.GROUP_SHEET_NAME+"!$IT$2:$IT$4"); + Name repeatsWeekly = centerWorkbook.createName(); + repeatsWeekly.setNameName("Weekly"); + repeatsWeekly.setRefersToFormula(TemplatePopulateImportConstants.GROUP_SHEET_NAME+"!$IT$2:$IT$4"); + Name repeatYearly = centerWorkbook.createName(); + repeatYearly.setNameName("Yearly"); + repeatYearly.setRefersToFormula(TemplatePopulateImportConstants.GROUP_SHEET_NAME+"!$IT$2:$IT$4"); + Name repeatsMonthly = centerWorkbook.createName(); + repeatsMonthly.setNameName("Monthly"); + repeatsMonthly.setRefersToFormula(TemplatePopulateImportConstants.GROUP_SHEET_NAME+"!$IU$2:$IU$12"); + Name repeatsOnWeekly = centerWorkbook.createName(); + repeatsOnWeekly.setNameName("Weekly_Days"); + repeatsOnWeekly.setRefersToFormula(TemplatePopulateImportConstants.GROUP_SHEET_NAME+"!$IV$2:$IV$8"); + + + //Staff Names for each office & center Names for each office + for(Integer i = 0; i < offices.size(); i++) { + Integer[] officeNameToBeginEndIndexesOfCenters =centerSheetPopulator.getOfficeNameToBeginEndIndexesOfCenters().get(i); + Integer[] officeNameToBeginEndIndexesOfStaff = personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i); + + Name loanOfficerName = centerWorkbook.createName(); + Name centerName=centerWorkbook.createName(); + + if(officeNameToBeginEndIndexesOfStaff != null) { + loanOfficerName.setNameName("Staff_" + offices.get(i).name().trim().replaceAll("[ )(]", "_")); + loanOfficerName.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+ + "!$B$" + officeNameToBeginEndIndexesOfStaff[0] + ":$B$" + officeNameToBeginEndIndexesOfStaff[1]); + } + if (officeNameToBeginEndIndexesOfCenters!=null) { + centerName.setNameName("Center_" + offices.get(i).name().trim().replaceAll("[ )(]", "_")); + centerName.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+ + "!$B$" + officeNameToBeginEndIndexesOfCenters[0] + ":$B$" + officeNameToBeginEndIndexesOfCenters[1]); + } + } + + } + +} \ 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/guarantor/GuarantorWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/guarantor/GuarantorWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/guarantor/GuarantorWorkbookPopulator.java new file mode 100644 index 0000000..912eb2b --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/guarantor/GuarantorWorkbookPopulator.java @@ -0,0 +1,314 @@ +/** + * 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.guarantor; + +import org.apache.fineract.infrastructure.bulkimport.constants.GuarantorConstants; +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.OfficeSheetPopulator; +import org.apache.fineract.infrastructure.codes.data.CodeValueData; +import org.apache.fineract.portfolio.loanaccount.data.LoanAccountData; +import org.apache.fineract.portfolio.savings.data.SavingsAccountData; +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; +import java.util.Collections; +import java.util.HashMap; +import java.util.List; + +public class GuarantorWorkbookPopulator extends AbstractWorkbookPopulator { + private OfficeSheetPopulator officeSheetPopulator; + private ClientSheetPopulator clientSheetPopulator; + private List<LoanAccountData> loans; + private List<SavingsAccountData> savings; + private List<CodeValueData>guarantorRelationshipTypes; + + public GuarantorWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + ClientSheetPopulator clientSheetPopulator, + List<LoanAccountData> loans, List<SavingsAccountData> savings, + List<CodeValueData> guarantorRelationshipTypes) { + this.officeSheetPopulator = officeSheetPopulator; + this.clientSheetPopulator = clientSheetPopulator; + this.loans = loans; + this.savings = savings; + this.guarantorRelationshipTypes=guarantorRelationshipTypes; + + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet addGuarantorSheet = workbook.createSheet(TemplatePopulateImportConstants.GUARANTOR_SHEET_NAME); + setLayout(addGuarantorSheet); + officeSheetPopulator.populate(workbook,dateFormat); + clientSheetPopulator.populate(workbook,dateFormat); + populateLoansTable(addGuarantorSheet,dateFormat); + populateSavingsTable(addGuarantorSheet,dateFormat); + populateGuarantorRelationshipTypes(addGuarantorSheet,dateFormat); + setRules(addGuarantorSheet); + + } + + + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(0); + worksheet.setColumnWidth(GuarantorConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.CLIENT_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.LOAN_ACCOUNT_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.GUARANTO_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.CLIENT_RELATIONSHIP_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.ENTITY_OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.ENTITY_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.FIRST_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.LAST_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.ADDRESS_LINE_1_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.ADDRESS_LINE_2_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.CITY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.DOB_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.ZIP_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.SAVINGS_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.AMOUNT, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.LOOKUP_CLIENT_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.LOOKUP_ACCOUNT_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.LOOKUP_SAVINGS_CLIENT_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(GuarantorConstants.LOOKUP_SAVINGS_ACCOUNT_NO_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(GuarantorConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(GuarantorConstants.CLIENT_NAME_COL, rowHeader, "Client Name*"); + writeString(GuarantorConstants.LOAN_ACCOUNT_NO_COL, rowHeader, " Loan Account NO"); + writeString(GuarantorConstants.GUARANTO_TYPE_COL, rowHeader, "Guranter_type*"); + writeString(GuarantorConstants.CLIENT_RELATIONSHIP_TYPE_COL, rowHeader, "Client Relationship type*"); + writeString(GuarantorConstants.ENTITY_OFFICE_NAME_COL, rowHeader, "Guranter office"); + writeString(GuarantorConstants.ENTITY_ID_COL, rowHeader, "Gurantor client id*"); + writeString(GuarantorConstants.FIRST_NAME_COL, rowHeader, "First Name*"); + writeString(GuarantorConstants.LAST_NAME_COL, rowHeader, "Last Name"); + writeString(GuarantorConstants.ADDRESS_LINE_1_COL, rowHeader, "ADDRESS LINE 1"); + writeString(GuarantorConstants.ADDRESS_LINE_2_COL, rowHeader, "ADDRESS LINE 2"); + writeString(GuarantorConstants.CITY_COL, rowHeader, "City"); + writeString(GuarantorConstants.DOB_COL, rowHeader, "Date of Birth"); + writeString(GuarantorConstants.ZIP_COL, rowHeader, "Zip*"); + writeString(GuarantorConstants.SAVINGS_ID_COL, rowHeader, "Savings Account Id"); + writeString(GuarantorConstants.AMOUNT, rowHeader, "Amount"); + writeString(GuarantorConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Lookup Client"); + writeString(GuarantorConstants.LOOKUP_ACCOUNT_NO_COL, rowHeader, "Lookup Loan Account"); + writeString(GuarantorConstants.LOOKUP_SAVINGS_CLIENT_NAME_COL, rowHeader, "Savings Lookup Client"); + writeString(GuarantorConstants.LOOKUP_SAVINGS_ACCOUNT_NO_COL, rowHeader, "Savings Lookup Account"); + + } + private void populateSavingsTable(Sheet addGuarantorSheet,String dateFormat) { + Workbook workbook = addGuarantorSheet.getWorkbook(); + CellStyle dateCellStyle = workbook.createCellStyle(); + short df = workbook.createDataFormat().getFormat(dateFormat); + dateCellStyle.setDataFormat(df); + int rowIndex = 1; + Row row; + Collections.sort(savings, SavingsAccountData.ClientNameComparator); + for(SavingsAccountData savingsAccount : savings) { + if(addGuarantorSheet.getRow(rowIndex)==null) { + row = addGuarantorSheet.createRow(rowIndex++); + } + else { + row=addGuarantorSheet.getRow(rowIndex++); + } + writeString(GuarantorConstants.LOOKUP_SAVINGS_CLIENT_NAME_COL, row, savingsAccount.getClientName() + "(" + savingsAccount.getClientId() + ")"); + writeLong(GuarantorConstants.LOOKUP_SAVINGS_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo())); + } + + } + private void populateLoansTable(Sheet addGuarantorSheet,String dateFormat) { + Workbook workbook = addGuarantorSheet.getWorkbook(); + CellStyle dateCellStyle = workbook.createCellStyle(); + short df = workbook.createDataFormat().getFormat(dateFormat); + dateCellStyle.setDataFormat(df); + int rowIndex = 1; + Row row; + Collections.sort(loans, LoanAccountData.ClientNameComparator); + for(LoanAccountData loan : loans) { + if(addGuarantorSheet.getRow(rowIndex)==null){ + row = addGuarantorSheet.createRow(rowIndex++); + } + else{ + row= addGuarantorSheet.getRow(rowIndex++); + } + writeString(GuarantorConstants.LOOKUP_CLIENT_NAME_COL, row, loan.getClientName() + "(" + loan.getClientId() + ")"); + writeString(GuarantorConstants.LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(loan.getAccountNo())+"-"+loan.getStatusStringValue()); + } + } + private void populateGuarantorRelationshipTypes(Sheet addGuarantorSheet, String dateFormat) { + Workbook workbook = addGuarantorSheet.getWorkbook(); + CellStyle dateCellStyle = workbook.createCellStyle(); + short df = workbook.createDataFormat().getFormat(dateFormat); + dateCellStyle.setDataFormat(df); + int rowIndex = 1; + Row row; + for (CodeValueData relationshipType:guarantorRelationshipTypes) { + if (addGuarantorSheet.getRow(rowIndex)==null){ + row=addGuarantorSheet.createRow(rowIndex++); + }else { + row=addGuarantorSheet.getRow(rowIndex++); + } + writeString(GuarantorConstants.LOOKUP_GUARANTOR_RELATIONSHIPS,row,relationshipType.getName()+"-"+relationshipType.getId()); + } + + } + private void setRules(Sheet worksheet) { + + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GuarantorConstants.OFFICE_NAME_COL, GuarantorConstants.OFFICE_NAME_COL); + CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GuarantorConstants.CLIENT_NAME_COL, GuarantorConstants.CLIENT_NAME_COL); + CellRangeAddressList entityofficeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GuarantorConstants.ENTITY_OFFICE_NAME_COL, GuarantorConstants.ENTITY_OFFICE_NAME_COL); + CellRangeAddressList entityclientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GuarantorConstants.ENTITY_ID_COL, GuarantorConstants.ENTITY_ID_COL); + CellRangeAddressList accountNumberRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GuarantorConstants.LOAN_ACCOUNT_NO_COL, GuarantorConstants.LOAN_ACCOUNT_NO_COL); + CellRangeAddressList savingsaccountNumberRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GuarantorConstants.SAVINGS_ID_COL, GuarantorConstants.SAVINGS_ID_COL); + CellRangeAddressList guranterTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GuarantorConstants.GUARANTO_TYPE_COL, GuarantorConstants.GUARANTO_TYPE_COL); + CellRangeAddressList guranterRelationshipTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + GuarantorConstants.CLIENT_RELATIONSHIP_TYPE_COL, GuarantorConstants.CLIENT_RELATIONSHIP_TYPE_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 savingsaccountNumberConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"SavingsAccount_\",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($G1,\" \",\"_\"),\"(\",\"_\"),\")\",\"_\")))"); + DataValidationConstraint guranterTypeConstraint = validationHelper.createExplicitListConstraint(new String[] { + TemplatePopulateImportConstants.GUARANTOR_INTERNAL, + TemplatePopulateImportConstants.GUARANTOR_EXTERNAL}); + DataValidationConstraint guarantorRelationshipConstraint = validationHelper.createFormulaListConstraint("GuarantorRelationship"); + DataValidationConstraint entityofficeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint entityclientNameConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Client_\",$F1))"); + + DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange); + DataValidation accountNumberValidation = validationHelper.createValidation(accountNumberConstraint, accountNumberRange); + DataValidation savingsaccountNumberValidation = validationHelper.createValidation(savingsaccountNumberConstraint, savingsaccountNumberRange); + DataValidation guranterTypeValidation = validationHelper.createValidation(guranterTypeConstraint, guranterTypeRange); + DataValidation guarantorRelationshipValidation=validationHelper.createValidation(guarantorRelationshipConstraint,guranterRelationshipTypeRange); + DataValidation entityofficeValidation = validationHelper.createValidation(entityofficeNameConstraint, entityofficeNameRange); + DataValidation entityclientValidation = validationHelper.createValidation(entityclientNameConstraint, entityclientNameRange); + + + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(clientValidation); + worksheet.addValidationData(accountNumberValidation); + worksheet.addValidationData(guranterTypeValidation); + worksheet.addValidationData(guarantorRelationshipValidation); + worksheet.addValidationData(entityofficeValidation); + worksheet.addValidationData(entityclientValidation); + worksheet.addValidationData(savingsaccountNumberValidation); + + } + private void setNames(Sheet worksheet) { + Workbook addGurarantorWorkbook = worksheet.getWorkbook(); + ArrayList<String> officeNames = new ArrayList<String>(officeSheetPopulator.getOfficeNames()); + + //Office Names + Name officeGroup = addGurarantorWorkbook.createName(); + officeGroup.setNameName("Office"); + officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (officeNames.size() + 1)); + + //GurantorRelationshipTypes Names + Name guarantorRelationshipsGroup = addGurarantorWorkbook.createName(); + guarantorRelationshipsGroup.setNameName("GuarantorRelationship"); + guarantorRelationshipsGroup.setRefersToFormula(TemplatePopulateImportConstants.GUARANTOR_SHEET_NAME+"!$CH$2:$CH$" + (guarantorRelationshipTypes.size() + 1)); + + //Clients Named after Offices + for(Integer i = 0; i < officeNames.size(); i++) { + Integer[] officeNameToBeginEndIndexesOfClients = clientSheetPopulator.getOfficeNameToBeginEndIndexesOfClients().get(i); + Name name = addGurarantorWorkbook.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 < loans.size(); i++){ + if(!clientName.equals(loans.get(i).getClientName())) { + endIndex = i + 1; + clientNameToBeginEndIndexes.put(clientName, new Integer[]{startIndex, endIndex}); + startIndex = i + 2; + clientName = loans.get(i).getClientName(); + clientId = loans.get(i).getClientId().toString(); + clientsWithActiveLoans.add(clientName); + clientIdsWithActiveLoans.add(clientId); + } + if(i == loans.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 = addGurarantorWorkbook.createName(); + name.setNameName("Account_" + clientsWithActiveLoans.get(j).replaceAll(" ", "_") + "_" + clientIdsWithActiveLoans.get(j) + "_"); + name.setRefersToFormula(TemplatePopulateImportConstants.GUARANTOR_SHEET_NAME+"!$CE$" + clientNameToBeginEndIndexes.get(clientsWithActiveLoans.get(j))[0] + + ":$CE$" + clientNameToBeginEndIndexes.get(clientsWithActiveLoans.get(j))[1]); + } + ///savings + //Counting clients with active savings and starting and end addresses of cells for naming + ArrayList<String> clientsWithActiveSavings = new ArrayList<String>(); + ArrayList<String> clientIdsWithActiveSavings = new ArrayList<String>(); + clientName=""; + clientId=""; + for(int i = 0; i < savings.size(); i++){ + if(!clientName.equals(savings.get(i).getClientName())) { + endIndex = i + 1; + clientNameToBeginEndIndexes.put(clientName, new Integer[]{startIndex, endIndex}); + startIndex = i + 2; + clientName = savings.get(i).getClientName(); + clientId = savings.get(i).getClientId().toString(); + clientsWithActiveSavings.add(clientName); + clientIdsWithActiveSavings.add(clientId); + } + if(i == savings.size()-1) { + endIndex = i + 2; + clientNameToBeginEndIndexes.put(clientName, new Integer[]{startIndex, endIndex}); + } + } + //Account Number Named after Clients + for(int j = 0; j < clientsWithActiveSavings.size(); j++) { + Name name = addGurarantorWorkbook.createName(); + name.setNameName("SavingsAccount_" + clientsWithActiveSavings.get(j).replaceAll(" ", "_") + "_" + clientIdsWithActiveSavings.get(j) + "_"); + name.setRefersToFormula(TemplatePopulateImportConstants.GUARANTOR_SHEET_NAME+"!$CG$" + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[0] + + ":$CG$" + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[1]); + } + + } + +} \ No newline at end of file
