http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/savings/SavingsWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/savings/SavingsWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/savings/SavingsWorkbookPopulator.java new file mode 100644 index 0000000..17be76b --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/savings/SavingsWorkbookPopulator.java @@ -0,0 +1,411 @@ +/** + * 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.savings; + +import org.apache.fineract.infrastructure.bulkimport.constants.SavingsConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.*; +import org.apache.fineract.portfolio.savings.data.SavingsProductData; +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 SavingsWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + private ClientSheetPopulator clientSheetPopulator; + private GroupSheetPopulator groupSheetPopulator; + private PersonnelSheetPopulator personnelSheetPopulator; + private SavingsProductSheetPopulator productSheetPopulator; + + + + public SavingsWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, ClientSheetPopulator clientSheetPopulator, + GroupSheetPopulator groupSheetPopulator, PersonnelSheetPopulator personnelSheetPopulator, + SavingsProductSheetPopulator savingsProductSheetPopulator) { + this.officeSheetPopulator=officeSheetPopulator; + this.clientSheetPopulator=clientSheetPopulator; + this.groupSheetPopulator=groupSheetPopulator; + this.personnelSheetPopulator=personnelSheetPopulator; + this.productSheetPopulator=savingsProductSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet savingsSheet = workbook.createSheet(TemplatePopulateImportConstants.SAVINGS_ACCOUNTS_SHEET_NAME); + officeSheetPopulator.populate(workbook,dateFormat); + clientSheetPopulator.populate(workbook,dateFormat); + groupSheetPopulator.populate(workbook,dateFormat); + personnelSheetPopulator.populate(workbook,dateFormat); + productSheetPopulator.populate(workbook,dateFormat); + setRules(savingsSheet,dateFormat); + setDefaults(savingsSheet,dateFormat); + setClientAndGroupDateLookupTable(savingsSheet, clientSheetPopulator.getClients(), groupSheetPopulator.getGroups(), + SavingsConstants.LOOKUP_CLIENT_NAME_COL, SavingsConstants.LOOKUP_ACTIVATION_DATE_COL,!TemplatePopulateImportConstants.CONTAINS_CLIENT_EXTERNAL_ID,dateFormat); + setLayout(savingsSheet); + } + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(SavingsConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.SAVINGS_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.CLIENT_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.PRODUCT_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.FIELD_OFFICER_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.SUBMITTED_ON_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.APPROVED_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.CURRENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.DECIMAL_PLACES_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.IN_MULTIPLES_OF_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + + worksheet.setColumnWidth(SavingsConstants.NOMINAL_ANNUAL_INTEREST_RATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.INTEREST_COMPOUNDING_PERIOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.INTEREST_POSTING_PERIOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.INTEREST_CALCULATION_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.MIN_OPENING_BALANCE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.LOCKIN_PERIOD_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.LOCKIN_PERIOD_FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.APPLY_WITHDRAWAL_FEE_FOR_TRANSFERS, TemplatePopulateImportConstants.SMALL_COL_SIZE); + + worksheet.setColumnWidth(SavingsConstants.LOOKUP_CLIENT_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.LOOKUP_ACTIVATION_DATE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + worksheet.setColumnWidth(SavingsConstants.ALLOW_OVER_DRAFT_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.OVER_DRAFT_LIMIT_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + worksheet.setColumnWidth(SavingsConstants.CHARGE_ID_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.CHARGE_AMOUNT_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.CHARGE_DUE_DATE_1, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.CHARGE_ID_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.CHARGE_AMOUNT_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(SavingsConstants.CHARGE_DUE_DATE_2, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + writeString(SavingsConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(SavingsConstants.SAVINGS_TYPE_COL, rowHeader, "Individual/Group*"); + writeString(SavingsConstants.CLIENT_NAME_COL, rowHeader, "Client Name*"); + writeString(SavingsConstants.PRODUCT_COL, rowHeader, "Product*"); + writeString(SavingsConstants.FIELD_OFFICER_NAME_COL, rowHeader, "Field Officer*"); + writeString(SavingsConstants.SUBMITTED_ON_DATE_COL, rowHeader, "Submitted On*"); + writeString(SavingsConstants.APPROVED_DATE_COL, rowHeader, "Approved On*"); + writeString(SavingsConstants.ACTIVATION_DATE_COL, rowHeader, "Activation Date*"); + writeString(SavingsConstants.CURRENCY_COL, rowHeader, "Currency"); + writeString(SavingsConstants.DECIMAL_PLACES_COL, rowHeader, "Decimal Places"); + writeString(SavingsConstants.IN_MULTIPLES_OF_COL, rowHeader, "In Multiples Of"); + writeString(SavingsConstants.NOMINAL_ANNUAL_INTEREST_RATE_COL, rowHeader, "Interest Rate %*"); + writeString(SavingsConstants.INTEREST_COMPOUNDING_PERIOD_COL, rowHeader, "Interest Compounding Period*"); + writeString(SavingsConstants.INTEREST_POSTING_PERIOD_COL, rowHeader, "Interest Posting Period*"); + writeString(SavingsConstants.INTEREST_CALCULATION_COL, rowHeader, "Interest Calculated*"); + writeString(SavingsConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, rowHeader, "# Days in Year*"); + writeString(SavingsConstants.MIN_OPENING_BALANCE_COL, rowHeader, "Min Opening Balance"); + writeString(SavingsConstants.LOCKIN_PERIOD_COL, rowHeader, "Locked In For"); + writeString(SavingsConstants.APPLY_WITHDRAWAL_FEE_FOR_TRANSFERS, rowHeader, "Apply Withdrawal Fee For Transfers"); + + writeString(SavingsConstants.LOOKUP_CLIENT_NAME_COL, rowHeader, "Client Name"); + writeString(SavingsConstants.LOOKUP_ACTIVATION_DATE_COL, rowHeader, "Client Activation Date"); + writeString(SavingsConstants.EXTERNAL_ID_COL, rowHeader, "External Id"); + + writeString(SavingsConstants.ALLOW_OVER_DRAFT_COL, rowHeader, "Is Overdraft Allowed "); + writeString(SavingsConstants.OVER_DRAFT_LIMIT_COL, rowHeader," Maximum Overdraft Amount Limit "); + + writeString(SavingsConstants.CHARGE_ID_1,rowHeader,"Charge Id"); + writeString(SavingsConstants.CHARGE_AMOUNT_1, rowHeader, "Charged Amount"); + writeString(SavingsConstants.CHARGE_DUE_DATE_1, rowHeader, "Charged On Date"); + writeString(SavingsConstants.CHARGE_ID_2,rowHeader,"Charge Id"); + writeString(SavingsConstants.CHARGE_AMOUNT_2, rowHeader, "Charged Amount"); + writeString(SavingsConstants.CHARGE_DUE_DATE_2, rowHeader, "Charged On Date"); + + } + + private void setDefaults(Sheet worksheet,String dateFormat) { + Workbook workbook = worksheet.getWorkbook(); + CellStyle dateCellStyle = workbook.createCellStyle(); + short df = workbook.createDataFormat().getFormat(dateFormat); + dateCellStyle.setDataFormat(df); + for (Integer rowNo = 1; rowNo < 1000; rowNo++) { + Row row = worksheet.createRow(rowNo); + writeFormula(SavingsConstants.CURRENCY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Currency_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Currency_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.DECIMAL_PLACES_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Decimal_Places_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Decimal_Places_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.IN_MULTIPLES_OF_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"In_Multiples_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"In_Multiples_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.NOMINAL_ANNUAL_INTEREST_RATE_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Rate_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Rate_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.INTEREST_COMPOUNDING_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Compouding_\",$D" + + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Compouding_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.INTEREST_POSTING_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Posting_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Posting_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.INTEREST_CALCULATION_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Interest_Calculation_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Interest_Calculation_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Days_In_Year_\",$D" + + (rowNo + 1) + "))),\"\",INDIRECT(CONCATENATE(\"Days_In_Year_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.MIN_OPENING_BALANCE_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Min_Balance_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Min_Balance_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.LOCKIN_PERIOD_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Lockin_Period_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Lockin_Period_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.LOCKIN_PERIOD_FREQUENCY_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Lockin_Frequency_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Lockin_Frequency_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.APPLY_WITHDRAWAL_FEE_FOR_TRANSFERS, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Withdrawal_Fee_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Withdrawal_Fee_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.ALLOW_OVER_DRAFT_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Overdraft_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Overdraft_\",$D" + (rowNo + 1) + ")))"); + writeFormula(SavingsConstants.OVER_DRAFT_LIMIT_COL, row, "IF(ISERROR(INDIRECT(CONCATENATE(\"Overdraft_Limit_\",$D" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"Overdraft_Limit_\",$D" + (rowNo + 1) + ")))"); + } + } + + private void setRules(Sheet worksheet,String dateFormat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.OFFICE_NAME_COL, SavingsConstants.OFFICE_NAME_COL); + CellRangeAddressList savingsTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.SAVINGS_TYPE_COL, SavingsConstants.SAVINGS_TYPE_COL); + CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.CLIENT_NAME_COL, SavingsConstants.CLIENT_NAME_COL); + CellRangeAddressList productNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.PRODUCT_COL, SavingsConstants.PRODUCT_COL); + CellRangeAddressList fieldOfficerRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.FIELD_OFFICER_NAME_COL, SavingsConstants.FIELD_OFFICER_NAME_COL); + CellRangeAddressList submittedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.SUBMITTED_ON_DATE_COL, SavingsConstants.SUBMITTED_ON_DATE_COL); + CellRangeAddressList approvedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.APPROVED_DATE_COL, SavingsConstants.APPROVED_DATE_COL); + CellRangeAddressList activationDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.ACTIVATION_DATE_COL, SavingsConstants.ACTIVATION_DATE_COL); + CellRangeAddressList interestCompudingPeriodRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.INTEREST_COMPOUNDING_PERIOD_COL, SavingsConstants.INTEREST_COMPOUNDING_PERIOD_COL); + CellRangeAddressList interestPostingPeriodRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.INTEREST_POSTING_PERIOD_COL, SavingsConstants.INTEREST_POSTING_PERIOD_COL); + CellRangeAddressList interestCalculationRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.INTEREST_CALCULATION_COL, SavingsConstants.INTEREST_CALCULATION_COL); + CellRangeAddressList interestCalculationDaysInYearRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), SavingsConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL, + SavingsConstants.INTEREST_CALCULATION_DAYS_IN_YEAR_COL); + CellRangeAddressList lockinPeriodFrequencyRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SavingsConstants.LOCKIN_PERIOD_FREQUENCY_COL, SavingsConstants.LOCKIN_PERIOD_FREQUENCY_COL); + CellRangeAddressList applyWithdrawalFeeForTransfersRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), SavingsConstants.APPLY_WITHDRAWAL_FEE_FOR_TRANSFERS, SavingsConstants.APPLY_WITHDRAWAL_FEE_FOR_TRANSFERS); + CellRangeAddressList allowOverdraftRange = new CellRangeAddressList(1,SpreadsheetVersion.EXCEL97.getLastRowIndex(),SavingsConstants.ALLOW_OVER_DRAFT_COL,SavingsConstants.ALLOW_OVER_DRAFT_COL); + + + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet); + + setNames(worksheet); + + DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint savingsTypeConstraint = validationHelper.createExplicitListConstraint(new String[] { "Individual", + "Group" }); + DataValidationConstraint clientNameConstraint = validationHelper + .createFormulaListConstraint("IF($B1=\"Individual\",INDIRECT(CONCATENATE(\"Client_\",$A1)),INDIRECT(CONCATENATE(\"Group_\",$A1)))"); + DataValidationConstraint productNameConstraint = validationHelper.createFormulaListConstraint("Products"); + DataValidationConstraint fieldOfficerNameConstraint = validationHelper + .createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$A1))"); + DataValidationConstraint submittedDateConstraint = validationHelper.createDateConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($C1,$AF$2:$AG$" + + (clientSheetPopulator.getClientsSize() + groupSheetPopulator.getGroupsSize() + 1) + ",2,FALSE)", "=TODAY()", + dateFormat); + DataValidationConstraint approvalDateConstraint = validationHelper.createDateConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=$F1", "=TODAY()", dateFormat); + DataValidationConstraint activationDateConstraint = validationHelper.createDateConstraint( + DataValidationConstraint.OperatorType.BETWEEN, "=$G1", "=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 lockinPeriodFrequencyConstraint = validationHelper.createExplicitListConstraint(new String[] { + TemplatePopulateImportConstants.FREQUENCY_DAYS, + TemplatePopulateImportConstants.FREQUENCY_WEEKS, + TemplatePopulateImportConstants.FREQUENCY_MONTHS, + TemplatePopulateImportConstants.FREQUENCY_YEARS }); + DataValidationConstraint applyWithdrawalFeeForTransferConstraint = validationHelper.createExplicitListConstraint(new String[] { + "True", "False" }); + DataValidationConstraint allowOverdraftConstraint = validationHelper.createExplicitListConstraint(new String[] { + "True", "False" }); + + DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation savingsTypeValidation = validationHelper.createValidation(savingsTypeConstraint, savingsTypeRange); + 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(lockinPeriodFrequencyConstraint, + lockinPeriodFrequencyRange); + DataValidation applyWithdrawalFeeForTransferValidation = validationHelper.createValidation( + applyWithdrawalFeeForTransferConstraint, applyWithdrawalFeeForTransfersRange); + DataValidation submittedDateValidation = validationHelper.createValidation(submittedDateConstraint, submittedDateRange); + DataValidation approvalDateValidation = validationHelper.createValidation(approvalDateConstraint, approvedDateRange); + DataValidation activationDateValidation = validationHelper.createValidation(activationDateConstraint, activationDateRange); + DataValidation allowOverdraftValidation = validationHelper.createValidation( + allowOverdraftConstraint, allowOverdraftRange); + + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(savingsTypeValidation); + 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(applyWithdrawalFeeForTransferValidation); + worksheet.addValidationData(allowOverdraftValidation); + } + + private void setNames(Sheet worksheet) { + Workbook savingsWorkbook = worksheet.getWorkbook(); + List<String> officeNames = officeSheetPopulator.getOfficeNames(); + List<SavingsProductData> 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); + Integer[] officeNameToBeginEndIndexesOfGroups = groupSheetPopulator.getOfficeNameToBeginEndIndexesOfGroups().get(i); + Name clientName = savingsWorkbook.createName(); + Name fieldOfficerName = savingsWorkbook.createName(); + Name groupName = 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]); + } + 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 = 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 Opening Balance, Lockin Period, Lockin Period Frequency, + // Withdrawal Fee Amount, Withdrawal Fee Type, Annual Fee, Annual Fee on + // Date + // Names for each product + for (Integer i = 0; i < products.size(); i++) { + Name interestRateName = savingsWorkbook.createName(); + Name interestCompoundingPeriodName = savingsWorkbook.createName(); + Name interestPostingPeriodName = savingsWorkbook.createName(); + Name interestCalculationName = savingsWorkbook.createName(); + Name daysInYearName = savingsWorkbook.createName(); + Name minOpeningBalanceName = savingsWorkbook.createName(); + Name lockinPeriodName = savingsWorkbook.createName(); + Name lockinPeriodFrequencyName = savingsWorkbook.createName(); + Name currencyName = savingsWorkbook.createName(); + Name decimalPlacesName = savingsWorkbook.createName(); + Name inMultiplesOfName = savingsWorkbook.createName(); + Name withdrawalFeeName = savingsWorkbook.createName(); + Name allowOverdraftName = savingsWorkbook.createName(); + Name overdraftLimitName = savingsWorkbook.createName(); + SavingsProductData product = products.get(i); + String productName = product.getName().replaceAll("[ ]", "_"); + if (product.getNominalAnnualInterestRate() != null) { + interestRateName.setNameName("Interest_Rate_" + productName); + interestRateName.setRefersToFormula("Products!$C$" + (i + 2)); + } + interestCompoundingPeriodName.setNameName("Interest_Compouding_" + productName); + interestPostingPeriodName.setNameName("Interest_Posting_" + productName); + interestCalculationName.setNameName("Interest_Calculation_" + productName); + daysInYearName.setNameName("Days_In_Year_" + productName); + currencyName.setNameName("Currency_" + productName); + decimalPlacesName.setNameName("Decimal_Places_" + productName); + withdrawalFeeName.setNameName("Withdrawal_Fee_" + productName); + allowOverdraftName.setNameName("Overdraft_" + productName); + + interestCompoundingPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$D$" + (i + 2)); + interestPostingPeriodName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$E$" + (i + 2)); + interestCalculationName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$F$" + (i + 2)); + daysInYearName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$G$" + (i + 2)); + currencyName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$K$" + (i + 2)); + decimalPlacesName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$L$" + (i + 2)); + withdrawalFeeName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$N$" + (i + 2)); + allowOverdraftName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$O$" + (i + 2)); + if (product.getOverdraftLimit() != null) { + overdraftLimitName.setNameName("Overdraft_Limit_" + productName); + overdraftLimitName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$P$" + (i + 2)); + } + if (product.getMinRequiredOpeningBalance() != null) { + minOpeningBalanceName.setNameName("Min_Balance_" + productName); + minOpeningBalanceName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$H$" + (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)); + } + if (product.getCurrency().currencyInMultiplesOf() != null) { + inMultiplesOfName.setNameName("In_Multiples_" + productName); + inMultiplesOfName.setRefersToFormula(TemplatePopulateImportConstants.PRODUCT_SHEET_NAME+"!$M$" + (i + 2)); + } + } + } + +} + +
http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/shareaccount/SharedAccountWorkBookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/shareaccount/SharedAccountWorkBookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/shareaccount/SharedAccountWorkBookPopulator.java new file mode 100644 index 0000000..376d04f --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/shareaccount/SharedAccountWorkBookPopulator.java @@ -0,0 +1,240 @@ +/** + * 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.shareaccount; + +import org.apache.fineract.infrastructure.bulkimport.constants.SharedAccountsConstants; +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.SavingsAccountSheetPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.SharedProductsSheetPopulator; +import org.apache.fineract.portfolio.client.data.ClientData; +import org.apache.fineract.portfolio.shareproducts.data.ShareProductData; +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 SharedAccountWorkBookPopulator extends AbstractWorkbookPopulator { + + private SharedProductsSheetPopulator sharedProductsSheetPopulator; + private ClientSheetPopulator clientSheetPopulator; + private SavingsAccountSheetPopulator savingsAccountSheetPopulator; + + public SharedAccountWorkBookPopulator(SharedProductsSheetPopulator sharedProductsSheetPopulator, + ClientSheetPopulator clientSheetPopulator,SavingsAccountSheetPopulator savingsAccountSheetPopulator) { + this.sharedProductsSheetPopulator=sharedProductsSheetPopulator; + this.clientSheetPopulator=clientSheetPopulator; + this.savingsAccountSheetPopulator=savingsAccountSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet sharedAccountSheet= workbook.createSheet(TemplatePopulateImportConstants.SHARED_ACCOUNTS_SHEET_NAME); + sharedProductsSheetPopulator.populate(workbook,dateFormat); + clientSheetPopulator.populate(workbook,dateFormat); + savingsAccountSheetPopulator.populate(workbook,dateFormat); + setLayout(sharedAccountSheet); + setRules(sharedAccountSheet,dateFormat); + setDefaults(sharedAccountSheet); + } + + private void setDefaults(Sheet sharedAccountSheet) { + for (Integer rowNo=1;rowNo<3000;rowNo++){ + Row row=sharedAccountSheet.createRow(rowNo); + writeFormula(SharedAccountsConstants.CURRENCY_COL,row,"IF(ISERROR(INDIRECT(CONCATENATE(\"CURRENCY_\",$B" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"CURRENCY_\",$B" + (rowNo + 1) + ")))"); + writeFormula(SharedAccountsConstants.DECIMAL_PLACES_COL,row,"IF(ISERROR(INDIRECT(CONCATENATE(\"DECIMAL_PLACES_\",$B" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"DECIMAL_PLACES_\",$B" + (rowNo + 1) + ")))"); + writeFormula(SharedAccountsConstants.TODAYS_PRICE_COL,row,"IF(ISERROR(INDIRECT(CONCATENATE(\"TODAYS_PRICE_\",$B" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"TODAYS_PRICE_\",$B" + (rowNo + 1) + ")))"); + writeFormula(SharedAccountsConstants.CURRENCY_IN_MULTIPLES_COL,row,"IF(ISERROR(INDIRECT(CONCATENATE(\"CURRENCY_IN_MULTIPLES_\",$B" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"CURRENCY_IN_MULTIPLES_\",$B" + (rowNo + 1) + ")))"); + writeFormula(SharedAccountsConstants.CHARGES_NAME_1_COL,row,"IF(ISERROR(INDIRECT(CONCATENATE(\"CHARGES_NAME_1_\",$B" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"CHARGES_NAME_1_\",$B" + (rowNo + 1) + ")))"); + writeFormula(SharedAccountsConstants.CHARGES_NAME_2_COL,row,"IF(ISERROR(INDIRECT(CONCATENATE(\"CHARGES_NAME_2_\",$B" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"CHARGES_NAME_2_\",$B" + (rowNo + 1) + ")))"); + writeFormula(SharedAccountsConstants.CHARGES_NAME_3_COL,row,"IF(ISERROR(INDIRECT(CONCATENATE(\"CHARGES_NAME_3_\",$B" + (rowNo + 1) + + "))),\"\",INDIRECT(CONCATENATE(\"CHARGES_NAME_3_\",$B" + (rowNo + 1) + ")))"); + } + } + + private void setRules(Sheet sharedAccountSheet,String dateFormat) { + CellRangeAddressList clientNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SharedAccountsConstants.CLIENT_NAME_COL,SharedAccountsConstants.CLIENT_NAME_COL); + CellRangeAddressList productRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SharedAccountsConstants.PRODUCT_COL,SharedAccountsConstants.PRODUCT_COL); + CellRangeAddressList submittedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SharedAccountsConstants.SUBMITTED_ON_COL, SharedAccountsConstants.SUBMITTED_ON_COL); + CellRangeAddressList lockingFrequencyTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SharedAccountsConstants.LOCK_IN_PERIOD_FREQUENCY_TYPE,SharedAccountsConstants. LOCK_IN_PERIOD_FREQUENCY_TYPE); + CellRangeAddressList applicationDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SharedAccountsConstants.APPLICATION_DATE_COL, SharedAccountsConstants.APPLICATION_DATE_COL); + CellRangeAddressList allowDividendCalcRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + SharedAccountsConstants.ALLOW_DIVIDEND_CALCULATION_FOR_INACTIVE_CLIENTS_COL, SharedAccountsConstants.ALLOW_DIVIDEND_CALCULATION_FOR_INACTIVE_CLIENTS_COL); + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) sharedAccountSheet); + setNames(sharedAccountSheet); + + DataValidationConstraint clientNameConstraint = validationHelper.createFormulaListConstraint("Clients"); + DataValidationConstraint productNameConstraint = validationHelper.createFormulaListConstraint("Products"); + DataValidationConstraint dateConstraint = validationHelper.createDateConstraint + (DataValidationConstraint.OperatorType.LESS_OR_EQUAL,"=TODAY()",null, dateFormat); + DataValidationConstraint frequencyConstraint = validationHelper.createExplicitListConstraint(new String[] { + TemplatePopulateImportConstants.FREQUENCY_DAYS, + TemplatePopulateImportConstants.FREQUENCY_WEEKS, + TemplatePopulateImportConstants.FREQUENCY_MONTHS, + TemplatePopulateImportConstants.FREQUENCY_YEARS }); + DataValidationConstraint booleanConstraint=validationHelper.createExplicitListConstraint(new String[]{"True","False"}); + DataValidation clientValidation = validationHelper.createValidation(clientNameConstraint, clientNameRange); + DataValidation productValidation=validationHelper.createValidation(productNameConstraint,productRange); + DataValidation submittedOnValidation=validationHelper.createValidation(dateConstraint,submittedDateRange); + DataValidation frequencyValidation=validationHelper.createValidation(frequencyConstraint,lockingFrequencyTypeRange); + DataValidation applicationDateValidation=validationHelper.createValidation(dateConstraint,applicationDateRange); + DataValidation allowDividendValidation=validationHelper.createValidation(booleanConstraint,allowDividendCalcRange); + + sharedAccountSheet.addValidationData(clientValidation); + sharedAccountSheet.addValidationData(productValidation); + sharedAccountSheet.addValidationData(submittedOnValidation); + sharedAccountSheet.addValidationData(frequencyValidation); + sharedAccountSheet.addValidationData(applicationDateValidation); + sharedAccountSheet.addValidationData(allowDividendValidation); + + } + + private void setNames(Sheet sharedAccountSheet) { + List<ClientData> clients=clientSheetPopulator.getClients(); + List<ShareProductData> products=sharedProductsSheetPopulator.getSharedProductDataList(); + Workbook sharedAccountWorkbook=sharedAccountSheet.getWorkbook(); + + Name clientsGroup=sharedAccountWorkbook.createName(); + clientsGroup.setNameName("Clients"); + clientsGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_SHEET_NAME+"!$B$2:$B$"+clients.size()+1); + + Name productGroup=sharedAccountWorkbook.createName(); + productGroup.setNameName("Products"); + productGroup.setRefersToFormula(TemplatePopulateImportConstants.SHARED_PRODUCTS_SHEET_NAME+"!$B$2:$B$"+products.size()+1); + + for (Integer i=0;i<products.size();i++) { + Name currecyName=sharedAccountWorkbook.createName(); + Name decimalPlacesName=sharedAccountWorkbook.createName(); + Name todaysPriceName=sharedAccountWorkbook.createName(); + Name currencyInMultiplesName=sharedAccountWorkbook.createName(); + Name chargesName1=sharedAccountWorkbook.createName(); + Name chargesName2=sharedAccountWorkbook.createName(); + Name chargesName3=sharedAccountWorkbook.createName(); + + String productName=products.get(i).getName().replaceAll("[ ]", "_"); + + currecyName.setNameName("CURRENCY_"+productName); + currecyName.setRefersToFormula(TemplatePopulateImportConstants.SHARED_PRODUCTS_SHEET_NAME+"!$C$"+(i+2)); + + decimalPlacesName.setNameName("DECIMAL_PLACES_"+productName); + decimalPlacesName.setRefersToFormula(TemplatePopulateImportConstants.SHARED_PRODUCTS_SHEET_NAME+"!$D$"+(i+2)); + + todaysPriceName.setNameName("TODAYS_PRICE_"+productName); + todaysPriceName.setRefersToFormula(TemplatePopulateImportConstants.SHARED_PRODUCTS_SHEET_NAME+"!$E$"+(i+2)); + + currencyInMultiplesName.setNameName("CURRENCY_IN_MULTIPLES_"+productName); + currencyInMultiplesName.setRefersToFormula(TemplatePopulateImportConstants.SHARED_PRODUCTS_SHEET_NAME+"!$F$"+(i+2)); + + chargesName1.setNameName("CHARGES_NAME_1_"+productName); + chargesName1.setRefersToFormula(TemplatePopulateImportConstants.SHARED_PRODUCTS_SHEET_NAME+"!$I$"+(i+2)); + + chargesName2.setNameName("CHARGES_NAME_2_"+productName); + chargesName2.setRefersToFormula(TemplatePopulateImportConstants.SHARED_PRODUCTS_SHEET_NAME+"!$K$"+(i+2)); + + chargesName3.setNameName("CHARGES_NAME_3_"+productName); + chargesName3.setRefersToFormula(TemplatePopulateImportConstants.SHARED_PRODUCTS_SHEET_NAME+"!$M$"+(i+2)); + } + + } + + private void setLayout(Sheet worksheet) { + Row rowHeader=worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + + worksheet.setColumnWidth(SharedAccountsConstants.CLIENT_NAME_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.CLIENT_NAME_COL,rowHeader,"Client Name *"); + + worksheet.setColumnWidth(SharedAccountsConstants.PRODUCT_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.PRODUCT_COL,rowHeader,"Shared Product *"); + + worksheet.setColumnWidth(SharedAccountsConstants.SUBMITTED_ON_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.SUBMITTED_ON_COL,rowHeader,"Submitted On Date *"); + + worksheet.setColumnWidth(SharedAccountsConstants.EXTERNAL_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.EXTERNAL_ID_COL,rowHeader,"External Id "); + + worksheet.setColumnWidth(SharedAccountsConstants.CURRENCY_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + writeString(SharedAccountsConstants.CURRENCY_COL,rowHeader,"Currency "); + + worksheet.setColumnWidth(SharedAccountsConstants.DECIMAL_PLACES_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.DECIMAL_PLACES_COL,rowHeader,"Decimal places "); + + worksheet.setColumnWidth(SharedAccountsConstants.TOTAL_NO_SHARES_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + writeString(SharedAccountsConstants.TOTAL_NO_SHARES_COL,rowHeader,"Total No. of Shares *"); + + worksheet.setColumnWidth(SharedAccountsConstants.TODAYS_PRICE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.TODAYS_PRICE_COL,rowHeader,"Today's price *"); + + worksheet.setColumnWidth(SharedAccountsConstants.CURRENCY_IN_MULTIPLES_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + writeString(SharedAccountsConstants.CURRENCY_IN_MULTIPLES_COL,rowHeader,"Currency in multiples "); + + worksheet.setColumnWidth(SharedAccountsConstants.DEFAULT_SAVINGS_AC_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.DEFAULT_SAVINGS_AC_COL,rowHeader,"Savings account *"); + + worksheet.setColumnWidth(SharedAccountsConstants.MINIMUM_ACTIVE_PERIOD_IN_DAYS_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + writeString(SharedAccountsConstants.MINIMUM_ACTIVE_PERIOD_IN_DAYS_COL,rowHeader,"Minimum active period(in days) "); + + worksheet.setColumnWidth(SharedAccountsConstants.LOCK_IN_PERIOD_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.LOCK_IN_PERIOD_COL,rowHeader,"Lock in period "); + + worksheet.setColumnWidth(SharedAccountsConstants.LOCK_IN_PERIOD_FREQUENCY_TYPE,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + writeString(SharedAccountsConstants.LOCK_IN_PERIOD_FREQUENCY_TYPE,rowHeader,"Lock in Period Frequency "); + + worksheet.setColumnWidth(SharedAccountsConstants.APPLICATION_DATE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.APPLICATION_DATE_COL,rowHeader,"Application Date *"); + + worksheet.setColumnWidth(SharedAccountsConstants.ALLOW_DIVIDEND_CALCULATION_FOR_INACTIVE_CLIENTS_COL,TemplatePopulateImportConstants.LARGE_COL_SIZE); + writeString(SharedAccountsConstants.ALLOW_DIVIDEND_CALCULATION_FOR_INACTIVE_CLIENTS_COL,rowHeader,"Allow dividends for inactive clients"); + + worksheet.setColumnWidth(SharedAccountsConstants.CHARGES_NAME_1_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.CHARGES_NAME_1_COL,rowHeader,"Charges 1 "); + + worksheet.setColumnWidth(SharedAccountsConstants.CHARGES_AMOUNT_1_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.CHARGES_AMOUNT_1_COL,rowHeader,"Amount 1 "); + + worksheet.setColumnWidth(SharedAccountsConstants.CHARGES_NAME_2_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.CHARGES_NAME_2_COL,rowHeader,"Charge 2"); + + worksheet.setColumnWidth(SharedAccountsConstants.CHARGES_AMOUNT_2_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.CHARGES_AMOUNT_2_COL,rowHeader,"Amount 2"); + + worksheet.setColumnWidth(SharedAccountsConstants.CHARGES_NAME_3_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.CHARGES_NAME_3_COL,rowHeader,"Charge 3"); + + worksheet.setColumnWidth(SharedAccountsConstants.CHARGES_AMOUNT_3_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(SharedAccountsConstants.CHARGES_AMOUNT_3_COL,rowHeader,"Amount 3 "); + + } +} http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/staff/StaffWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/staff/StaffWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/staff/StaffWorkbookPopulator.java new file mode 100644 index 0000000..9ca5097 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/staff/StaffWorkbookPopulator.java @@ -0,0 +1,120 @@ +/** + * 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.staff; + +import org.apache.fineract.infrastructure.bulkimport.constants.StaffConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator; +import org.apache.fineract.organisation.office.data.OfficeData; +import org.apache.fineract.template.domain.Template; +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 StaffWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + + public StaffWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator) { + this.officeSheetPopulator=officeSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet staffSheet=workbook.createSheet(TemplatePopulateImportConstants.EMPLOYEE_SHEET_NAME); + officeSheetPopulator.populate(workbook,dateFormat); + setLayout(staffSheet); + setRules(staffSheet,dateFormat); + } + + + private void setRules(Sheet staffSheet,String dateFormat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + StaffConstants.OFFICE_NAME_COL, StaffConstants.OFFICE_NAME_COL); + CellRangeAddressList isLoanOfficerNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + StaffConstants.IS_LOAN_OFFICER, StaffConstants.IS_LOAN_OFFICER); + CellRangeAddressList joinedOnNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + StaffConstants.JOINED_ON_COL, StaffConstants.JOINED_ON_COL); + CellRangeAddressList isActiveNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + StaffConstants.IS_ACTIVE_COL, StaffConstants.IS_ACTIVE_COL); + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) staffSheet); + + List<OfficeData> offices = officeSheetPopulator.getOffices(); + setNames(staffSheet, offices); + + DataValidationConstraint officeNameConstraint = + validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint isLoanOfficerConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + DataValidationConstraint joinedOnConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL, + "=TODAY()",null, dateFormat); + DataValidationConstraint isActiveConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + + DataValidation officeValidation = + validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation isLoanOfficerValidation = + validationHelper.createValidation(isLoanOfficerConstraint, isLoanOfficerNameRange); + DataValidation joinedOnValidation = + validationHelper.createValidation(joinedOnConstraint, joinedOnNameRange); + DataValidation isActiveValidation = + validationHelper.createValidation(isActiveConstraint, isActiveNameRange); + + staffSheet.addValidationData(officeValidation); + staffSheet.addValidationData(isLoanOfficerValidation); + staffSheet.addValidationData(joinedOnValidation); + staffSheet.addValidationData(isActiveValidation); + + } + + private void setNames(Sheet staffSheet, List<OfficeData> offices) { + Workbook staffWorkBook=staffSheet.getWorkbook(); + Name officeGroup=staffWorkBook.createName(); + officeGroup.setNameName("Office"); + officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (offices.size() + 1)); + } + + private void setLayout(Sheet staffSheet) { + Row rowHeader = staffSheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + staffSheet.setColumnWidth(StaffConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + staffSheet.setColumnWidth(StaffConstants.FIRST_NAME_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + staffSheet.setColumnWidth(StaffConstants.LAST_NAME_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + staffSheet.setColumnWidth(StaffConstants.IS_LOAN_OFFICER,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + staffSheet.setColumnWidth(StaffConstants.MOBILE_NO_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + staffSheet.setColumnWidth(StaffConstants.JOINED_ON_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + staffSheet.setColumnWidth(StaffConstants.EXTERNAL_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + staffSheet.setColumnWidth(StaffConstants.IS_ACTIVE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(StaffConstants.OFFICE_NAME_COL,rowHeader,"Office Name *"); + writeString(StaffConstants.FIRST_NAME_COL,rowHeader,"First Name *"); + writeString(StaffConstants.LAST_NAME_COL,rowHeader,"Last Name *"); + writeString(StaffConstants.IS_LOAN_OFFICER,rowHeader,"Is Loan Officer *"); + writeString(StaffConstants.MOBILE_NO_COL,rowHeader, "Mobile no"); + writeString(StaffConstants.JOINED_ON_COL,rowHeader,"Joined on *"); + writeString(StaffConstants.EXTERNAL_ID_COL,rowHeader,"External Id *"); + writeString(StaffConstants.IS_ACTIVE_COL,rowHeader,"Is Active *"); + } +} http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/users/UserWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/users/UserWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/users/UserWorkbookPopulator.java new file mode 100644 index 0000000..5fbd460 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/users/UserWorkbookPopulator.java @@ -0,0 +1,133 @@ +/** + * 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.users; + +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.UserConstants; +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 UserWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + private PersonnelSheetPopulator personnelSheetPopulator; + private RoleSheetPopulator roleSheetPopulator; + + public UserWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + PersonnelSheetPopulator personnelSheetPopulator, + RoleSheetPopulator roleSheetPopulator) { + this.officeSheetPopulator=officeSheetPopulator; + this.personnelSheetPopulator=personnelSheetPopulator; + this.roleSheetPopulator=roleSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet usersheet=workbook.createSheet(TemplatePopulateImportConstants.USER_SHEET_NAME); + personnelSheetPopulator.populate(workbook,dateFormat); + officeSheetPopulator.populate(workbook,dateFormat); + roleSheetPopulator.populate(workbook,dateFormat); + setLayout(usersheet); + setRules(usersheet); + } + + private void setRules(Sheet usersheet) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + UserConstants.OFFICE_NAME_COL, UserConstants.OFFICE_NAME_COL); + CellRangeAddressList staffNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + UserConstants.STAFF_NAME_COL, UserConstants.STAFF_NAME_COL); + CellRangeAddressList autoGenPwRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + UserConstants.AUTO_GEN_PW_COL, UserConstants.AUTO_GEN_PW_COL); + CellRangeAddressList overridePwExpiryPolicyRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + UserConstants.OVERRIDE_PW_EXPIRY_POLICY_COL, UserConstants.OVERRIDE_PW_EXPIRY_POLICY_COL); + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet)usersheet); + List<OfficeData> offices = officeSheetPopulator.getOffices(); + setNames(usersheet, offices); + + DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint staffNameConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$A1))"); + DataValidationConstraint booleanConstraint = validationHelper.createExplicitListConstraint(new String[]{"True", "False"}); + + DataValidation officeValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation staffValidation = validationHelper.createValidation(staffNameConstraint, staffNameRange); + DataValidation autoGenPwValidation=validationHelper.createValidation(booleanConstraint,autoGenPwRange); + DataValidation overridePwExpiryPolicyValidation=validationHelper.createValidation(booleanConstraint,overridePwExpiryPolicyRange); + + usersheet.addValidationData(officeValidation); + usersheet.addValidationData(staffValidation); + usersheet.addValidationData(autoGenPwValidation); + usersheet.addValidationData(overridePwExpiryPolicyValidation); + } + + private void setNames(Sheet usersheet, List<OfficeData> offices) { + Workbook userWorkbook=usersheet.getWorkbook(); + Name officeUser = userWorkbook.createName(); + + officeUser.setNameName("Office"); + officeUser.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (offices.size() + 1)); + + //Staff Names for each office + for (Integer i=0;i<offices.size();i++){ + Integer[] officeNameToBeginEndIndexesOfStaff = personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i); + + Name userOfficeName=userWorkbook.createName(); + + if(officeNameToBeginEndIndexesOfStaff != null) { + userOfficeName.setNameName("Staff_" + offices.get(i).name().trim().replaceAll("[ )(]", "_")); + userOfficeName.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+ + "!$B$" + officeNameToBeginEndIndexesOfStaff[0] + ":$B$" + officeNameToBeginEndIndexesOfStaff[1]); + } + } + } + + private void setLayout(Sheet usersheet) { + Row rowHeader = usersheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + + usersheet.setColumnWidth(UserConstants.OFFICE_NAME_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.STAFF_NAME_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.USER_NAME_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.FIRST_NAME_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.LAST_NAME_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.EMAIL_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.AUTO_GEN_PW_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.OVERRIDE_PW_EXPIRY_POLICY_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.STATUS_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + usersheet.setColumnWidth(UserConstants.ROLE_NAME_START_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + writeString(UserConstants.OFFICE_NAME_COL,rowHeader,"Office Name *"); + writeString(UserConstants.STAFF_NAME_COL,rowHeader,"Staff Name"); + writeString(UserConstants.USER_NAME_COL,rowHeader,"User name"); + writeString(UserConstants.FIRST_NAME_COL,rowHeader,"First name *"); + writeString(UserConstants.LAST_NAME_COL,rowHeader,"Last name *"); + writeString(UserConstants.EMAIL_COL,rowHeader,"Email *"); + writeString(UserConstants.AUTO_GEN_PW_COL,rowHeader,"Auto Gen. Password"); + writeString(UserConstants.OVERRIDE_PW_EXPIRY_POLICY_COL,rowHeader, "Override pw expiry policy"); + writeString(UserConstants.ROLE_NAME_START_COL,rowHeader,"Role Name *(Enter in consecutive cells horizontally)"); + } + +} http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportEventListener.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportEventListener.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportEventListener.java new file mode 100644 index 0000000..7834439 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportEventListener.java @@ -0,0 +1,174 @@ +/** + * 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.service; + +import java.io.ByteArrayInputStream; +import java.io.ByteArrayOutputStream; +import java.io.IOException; +import java.net.URLConnection; +import java.util.HashSet; +import java.util.Set; + +import org.apache.fineract.infrastructure.bulkimport.data.BulkImportEvent; +import org.apache.fineract.infrastructure.bulkimport.data.Count; +import org.apache.fineract.infrastructure.bulkimport.data.GlobalEntityType; +import org.apache.fineract.infrastructure.bulkimport.domain.ImportDocument; +import org.apache.fineract.infrastructure.bulkimport.domain.ImportDocumentRepository; +import org.apache.fineract.infrastructure.bulkimport.importhandler.ImportHandler; +import org.apache.fineract.infrastructure.core.domain.FineractPlatformTenant; +import org.apache.fineract.infrastructure.core.exception.GeneralPlatformDomainRuleException; +import org.apache.fineract.infrastructure.core.service.DateUtils; +import org.apache.fineract.infrastructure.core.service.ThreadLocalContextUtil; +import org.apache.fineract.infrastructure.documentmanagement.command.DocumentCommand; +import org.apache.fineract.infrastructure.documentmanagement.domain.Document; +import org.apache.fineract.infrastructure.documentmanagement.service.DocumentWritePlatformService; +import org.apache.fineract.infrastructure.security.service.TenantDetailsService; +import org.apache.poi.ss.usermodel.Workbook; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.context.ApplicationContext; +import org.springframework.context.ApplicationListener; +import org.springframework.stereotype.Service; + +@Service +public class BulkImportEventListener implements ApplicationListener<BulkImportEvent> { + + + private final TenantDetailsService tenantDetailsService; + private final ApplicationContext applicationContext; + private final ImportDocumentRepository importRepository; + private final DocumentWritePlatformService documentService; + + @Autowired + public BulkImportEventListener( + final TenantDetailsService tenantDetailsService, + final ApplicationContext context, + final ImportDocumentRepository importRepository, + final DocumentWritePlatformService documentService) { + this.tenantDetailsService = tenantDetailsService; + this.applicationContext = context; + this.importRepository = importRepository; + this.documentService = documentService; + } + + @Override + public void onApplicationEvent(final BulkImportEvent event) { + + final String tenantIdentifier = event.getTenantIdentifier(); + final FineractPlatformTenant tenant = this.tenantDetailsService + .loadTenantById(tenantIdentifier); + ThreadLocalContextUtil.setTenant(tenant); + ImportHandler importHandler = null; + final ImportDocument importDocument = this.importRepository.findOne(event.getImportId()); + final GlobalEntityType entityType = GlobalEntityType.fromInt(importDocument.getEntityType()); + + switch(entityType) { + case OFFICES : + importHandler = this.applicationContext.getBean("officeImportHandler", ImportHandler.class); + break; + case CENTERS: + importHandler=this.applicationContext.getBean("centerImportHandler",ImportHandler.class); + break; + case CHART_OF_ACCOUNTS: + importHandler=this.applicationContext.getBean("chartOfAccountsImportHandler",ImportHandler.class); + break; + case CLIENTS_ENTTTY: + importHandler=this.applicationContext.getBean("clientEntityImportHandler",ImportHandler.class); + break; + case CLIENTS_PERSON: + importHandler=this.applicationContext.getBean("clientPersonImportHandler",ImportHandler.class); + break; + case FIXED_DEPOSIT_ACCOUNTS: + importHandler=this.applicationContext.getBean("fixedDepositImportHandler",ImportHandler.class); + break; + case FIXED_DEPOSIT_TRANSACTIONS: + importHandler=this.applicationContext.getBean("fixedDepositTransactionImportHandler",ImportHandler.class); + break; + case GROUPS: + importHandler=this.applicationContext.getBean("groupImportHandler",ImportHandler.class); + break; + case GUARANTORS: + importHandler=this.applicationContext.getBean("guarantorImportHandler",ImportHandler.class); + break; + case GL_JOURNAL_ENTRIES: + importHandler=this.applicationContext.getBean("journalEntriesImportHandler",ImportHandler.class); + break; + case LOANS: + importHandler=this.applicationContext.getBean("loanImportHandler",ImportHandler.class); + break; + case LOAN_TRANSACTIONS: + importHandler=this.applicationContext.getBean("loanRepaymentImportHandler",ImportHandler.class); + break; + case RECURRING_DEPOSIT_ACCOUNTS: + importHandler=this.applicationContext.getBean("recurringDepositImportHandler",ImportHandler.class); + break; + case RECURRING_DEPOSIT_ACCOUNTS_TRANSACTIONS: + importHandler=this.applicationContext.getBean("recurringDepositTransactionImportHandler",ImportHandler.class); + break; + case SAVINGS_ACCOUNT: + importHandler=this.applicationContext.getBean("savingsImportHandler",ImportHandler.class); + break; + case SAVINGS_TRANSACTIONS: + importHandler=this.applicationContext.getBean("savingsTransactionImportHandler",ImportHandler.class); + break; + case SHARE_ACCOUNTS: + importHandler=this.applicationContext.getBean("sharedAccountImportHandler",ImportHandler.class); + break; + case STAFF: + importHandler=this.applicationContext.getBean("staffImportHandler",ImportHandler.class); + break; + case USERS: + importHandler=this.applicationContext.getBean("userImportHandler",ImportHandler.class); + break; + default : throw new GeneralPlatformDomainRuleException("error.msg.unable.to.find.resource", + "Unable to find requested resource"); + + } + + final Workbook workbook = event.getWorkbook(); + final Count count = importHandler.process(workbook, event.getLocale(), event.getDateFormat()); + importDocument.update(DateUtils.getLocalDateTimeOfTenant(), count.getSuccessCount(), count.getErrorCount()); + this.importRepository.save(importDocument); + + final Set<String> modifiedParams = new HashSet<>(); + modifiedParams.add("fileName"); + modifiedParams.add("size"); + modifiedParams.add("type"); + modifiedParams.add("location"); + Document document = importDocument.getDocument(); + + DocumentCommand documentCommand = new DocumentCommand(modifiedParams, document.getId(), entityType.name(), null, + document.getName(), document.getFileName(), document.getSize(), URLConnection.guessContentTypeFromName(document.getFileName()), + null, null); + + final ByteArrayOutputStream bos = new ByteArrayOutputStream(); + try { + try { + workbook.write(bos); + } finally { + bos.close(); + } + } catch (IOException io) { + io.printStackTrace(); + } + byte[] bytes = bos.toByteArray(); + ByteArrayInputStream bis = new ByteArrayInputStream(bytes); + this.documentService.updateDocument(documentCommand, bis); + } + +} \ 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/service/BulkImportWorkbookPopulatorService.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorService.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorService.java new file mode 100644 index 0000000..bca137a --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorService.java @@ -0,0 +1,26 @@ +/** + * 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.service; + +import javax.ws.rs.core.Response; + +public interface BulkImportWorkbookPopulatorService { + + public Response getTemplate(final String entityType, final Long officeId, final Long staffId,final String dateFormat); +} \ No newline at end of file