http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/centers/CentersWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/centers/CentersWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/centers/CentersWorkbookPopulator.java new file mode 100644 index 0000000..27248c6 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/centers/CentersWorkbookPopulator.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.centers; + +import org.apache.fineract.infrastructure.bulkimport.constants.CenterConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.GroupSheetPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.OfficeSheetPopulator; +import org.apache.fineract.infrastructure.bulkimport.populator.PersonnelSheetPopulator; +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 CentersWorkbookPopulator extends AbstractWorkbookPopulator { + + + private OfficeSheetPopulator officeSheetPopulator; + private PersonnelSheetPopulator personnelSheetPopulator; + private GroupSheetPopulator groupSheetPopulator; + + public CentersWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + PersonnelSheetPopulator personnelSheetPopulator,GroupSheetPopulator groupSheetPopulator) { + this.officeSheetPopulator = officeSheetPopulator; + this.personnelSheetPopulator = personnelSheetPopulator; + this.groupSheetPopulator=groupSheetPopulator; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet centerSheet = workbook.createSheet(TemplatePopulateImportConstants.CENTER_SHEET_NAME); + personnelSheetPopulator.populate(workbook,dateFormat); + officeSheetPopulator.populate(workbook,dateFormat); + groupSheetPopulator.populate(workbook,dateFormat); + setLayout(centerSheet); + setLookupTable(centerSheet,dateFormat); + setRules(centerSheet,dateFormat); + } + + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(0); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(CenterConstants.CENTER_NAME_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.STAFF_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.ACTIVE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.SUBMITTED_ON_DATE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.MEETING_START_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.IS_REPEATING_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.FREQUENCY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.INTERVAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.REPEATS_ON_DAY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.STATUS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.CENTER_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.FAILURE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.GROUP_NAMES_STARTING_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.LOOKUP_OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.LOOKUP_OFFICE_OPENING_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.LOOKUP_REPEAT_NORMAL_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.LOOKUP_REPEAT_MONTHLY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(CenterConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + + writeString(CenterConstants.CENTER_NAME_COL, rowHeader, "Center Name*"); + writeString(CenterConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(CenterConstants.STAFF_NAME_COL, rowHeader, "Staff Name*"); + writeString(CenterConstants.EXTERNAL_ID_COL, rowHeader, "External ID"); + writeString(CenterConstants.ACTIVE_COL, rowHeader, "Active*"); + writeString(CenterConstants.ACTIVATION_DATE_COL, rowHeader, "Activation Date*"); + writeString(CenterConstants.SUBMITTED_ON_DATE_COL,rowHeader,"Submitted On Date"); + writeString(CenterConstants.MEETING_START_DATE_COL, rowHeader, "Meeting Start Date* (On or After)"); + writeString(CenterConstants.IS_REPEATING_COL, rowHeader, "Repeat*"); + writeString(CenterConstants.FREQUENCY_COL, rowHeader, "Frequency*"); + writeString(CenterConstants.INTERVAL_COL, rowHeader, "Interval*"); + writeString(CenterConstants.REPEATS_ON_DAY_COL, rowHeader, "Repeats On*"); + writeString(CenterConstants.GROUP_NAMES_STARTING_COL,rowHeader,"Group Names* (Enter in consecutive cells horizontally)"); + writeString(CenterConstants.LOOKUP_OFFICE_NAME_COL, rowHeader, "Office Name"); + writeString(CenterConstants.LOOKUP_OFFICE_OPENING_DATE_COL, rowHeader, "Opening Date"); + writeString(CenterConstants.LOOKUP_REPEAT_NORMAL_COL, rowHeader, "Repeat Normal Range"); + writeString(CenterConstants.LOOKUP_REPEAT_MONTHLY_COL, rowHeader, "Repeat Monthly Range"); + writeString(CenterConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, rowHeader, "If Repeat Weekly Range"); + } + private void setLookupTable(Sheet centerSheet,String dateFormat) { + setOfficeDateLookupTable(centerSheet, officeSheetPopulator.getOffices(), CenterConstants.LOOKUP_OFFICE_NAME_COL,CenterConstants.LOOKUP_OFFICE_OPENING_DATE_COL,dateFormat); + int rowIndex; + for(rowIndex = 1; rowIndex <= 11; rowIndex++) { + Row row = centerSheet.getRow(rowIndex); + if(row == null) + row = centerSheet.createRow(rowIndex); + writeInt(CenterConstants.LOOKUP_REPEAT_MONTHLY_COL, row, rowIndex); + } + for(rowIndex = 1; rowIndex <= 3; rowIndex++) + writeInt(CenterConstants.LOOKUP_REPEAT_NORMAL_COL, centerSheet.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(CenterConstants.LOOKUP_IF_REPEAT_WEEKLY_COL, centerSheet.getRow(rowIndex), days[rowIndex-1]); + + } + private void setRules(Sheet worksheet,String dateFormat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.OFFICE_NAME_COL,CenterConstants. OFFICE_NAME_COL); + CellRangeAddressList staffNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.STAFF_NAME_COL,CenterConstants. STAFF_NAME_COL); + CellRangeAddressList activationDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),CenterConstants. ACTIVATION_DATE_COL,CenterConstants. ACTIVATION_DATE_COL); + CellRangeAddressList activeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.ACTIVE_COL, CenterConstants.ACTIVE_COL); + CellRangeAddressList submittedDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),CenterConstants. SUBMITTED_ON_DATE_COL,CenterConstants.SUBMITTED_ON_DATE_COL); + CellRangeAddressList meetingStartDateRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.MEETING_START_DATE_COL,CenterConstants. MEETING_START_DATE_COL); + CellRangeAddressList isRepeatRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(),CenterConstants. IS_REPEATING_COL,CenterConstants. IS_REPEATING_COL); + CellRangeAddressList repeatsRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.FREQUENCY_COL, CenterConstants.FREQUENCY_COL); + CellRangeAddressList repeatsEveryRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.INTERVAL_COL,CenterConstants. INTERVAL_COL); + CellRangeAddressList repeatsOnRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), CenterConstants.REPEATS_ON_DAY_COL,CenterConstants. REPEATS_ON_DAY_COL); + + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet)worksheet); + List<OfficeData> offices = officeSheetPopulator.getOffices(); + setNames(worksheet, offices); + + + DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint staffNameConstraint = validationHelper. + createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$B1))"); + DataValidationConstraint activationDateConstraint = validationHelper.createDateConstraint + (DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($B1,$IR$2:$IS" + (offices.size() + 1)+",2,FALSE)", + "=TODAY()", dateFormat); + DataValidationConstraint booleanConstraint = validationHelper.createExplicitListConstraint(new String[]{"True", "False"}); + DataValidationConstraint submittedOnDateConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL, + "=$F1", null,dateFormat); + DataValidationConstraint meetingStartDateConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, + "=$F1", "=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($J1)"); + DataValidationConstraint repeatsOnConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE($J1,\"_DAYS\"))"); + + + 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 submittedOnValidation=validationHelper.createValidation(submittedOnDateConstraint,submittedDateRange); + 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(activeValidation); + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(staffValidation); + worksheet.addValidationData(activationDateValidation); + worksheet.addValidationData(submittedOnValidation); + 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.CENTER_SHEET_NAME+"!$IT$2:$IT$4"); + Name repeatsWeekly = centerWorkbook.createName(); + repeatsWeekly.setNameName("Weekly"); + repeatsWeekly.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IT$2:$IT$4"); + Name repeatYearly = centerWorkbook.createName(); + repeatYearly.setNameName("Yearly"); + repeatYearly.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IT$2:$IT$4"); + Name repeatsMonthly = centerWorkbook.createName(); + repeatsMonthly.setNameName("Monthly"); + repeatsMonthly.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IU$2:$IU$12"); + Name repeatsOnWeekly = centerWorkbook.createName(); + repeatsOnWeekly.setNameName("Weekly_Days"); + repeatsOnWeekly.setRefersToFormula(TemplatePopulateImportConstants.CENTER_SHEET_NAME+"!$IV$2:$IV$8"); + + + //Staff Names for each office + for(Integer i = 0; i < offices.size(); i++) { + Integer[] officeNameToBeginEndIndexesOfStaff = personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i); + Name loanOfficerName = 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]); + } + } + + } + + +} \ 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/chartofaccounts/ChartOfAccountsWorkbook.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java new file mode 100644 index 0000000..f65be13 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java @@ -0,0 +1,236 @@ +/** + * 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.chartofaccounts; + +import org.apache.fineract.accounting.glaccount.data.GLAccountData; +import org.apache.fineract.accounting.glaccount.domain.GLAccountType; +import org.apache.fineract.accounting.glaccount.domain.GLAccountUsage; +import org.apache.fineract.infrastructure.bulkimport.constants.ChartOfAcountsConstants; +import org.apache.fineract.infrastructure.bulkimport.constants.TemplatePopulateImportConstants; +import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator; +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.HashMap; +import java.util.List; +import java.util.Map; + +public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { + private List<GLAccountData> glAccounts; + private Map<String,List<String>> accountTypeToAccountNameAndTag; + private Map<Integer,Integer[]>accountTypeToBeginEndIndexesofAccountNames; + private List<String> accountTypesNoDuplicatesList; + + + public ChartOfAccountsWorkbook(List<GLAccountData> glAccounts) { + this.glAccounts = glAccounts; + } + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet chartOfAccountsSheet=workbook.createSheet(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME); + setLayout(chartOfAccountsSheet); + setAccountTypeToAccountNameAndTag(); + setLookupTable(chartOfAccountsSheet); + setRules(chartOfAccountsSheet); + setDefaults(chartOfAccountsSheet); + } + + private void setAccountTypeToAccountNameAndTag() { + accountTypeToAccountNameAndTag=new HashMap<>(); + for (GLAccountData glAccount: glAccounts) { + addToaccountTypeToAccountNameMap(glAccount.getType().getValue(),glAccount.getName()+ + "-"+glAccount.getId()+"-"+glAccount.getTagId().getName()+"-"+glAccount.getTagId().getId()); + } + } + + private void addToaccountTypeToAccountNameMap(String key, String value) { + List<String> values=accountTypeToAccountNameAndTag.get(key); + if (values==null){ + values=new ArrayList<String>(); + } + if (!values.contains(value)){ + values.add(value); + accountTypeToAccountNameAndTag.put(key,values); + } + } + + private void setRules(Sheet chartOfAccountsSheet) { + CellRangeAddressList accountTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + ChartOfAcountsConstants. ACCOUNT_TYPE_COL,ChartOfAcountsConstants.ACCOUNT_TYPE_COL); + CellRangeAddressList accountUsageRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + ChartOfAcountsConstants.ACCOUNT_USAGE_COL,ChartOfAcountsConstants.ACCOUNT_USAGE_COL); + CellRangeAddressList manualEntriesAllowedRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + ChartOfAcountsConstants.MANUAL_ENTRIES_ALLOWED_COL,ChartOfAcountsConstants.MANUAL_ENTRIES_ALLOWED_COL); + CellRangeAddressList parentRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + ChartOfAcountsConstants.PARENT_COL,ChartOfAcountsConstants.PARENT_COL); + CellRangeAddressList tagRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + ChartOfAcountsConstants.TAG_COL,ChartOfAcountsConstants.TAG_COL); + + DataValidationHelper validationHelper=new HSSFDataValidationHelper((HSSFSheet) chartOfAccountsSheet); + setNames(chartOfAccountsSheet, accountTypesNoDuplicatesList); + + DataValidationConstraint accountTypeConstraint=validationHelper. + createExplicitListConstraint(new String[]{ + GLAccountType.ASSET.toString(), + GLAccountType.LIABILITY.toString(), + GLAccountType.EQUITY.toString(), + GLAccountType.INCOME.toString(), + GLAccountType.EXPENSE.toString()}); + DataValidationConstraint accountUsageConstraint=validationHelper. + createExplicitListConstraint(new String[]{ + GLAccountUsage.DETAIL.toString(), + GLAccountUsage.HEADER.toString()}); + DataValidationConstraint booleanConstraint=validationHelper. + createExplicitListConstraint(new String[]{"True","False"}); + DataValidationConstraint parentConstraint=validationHelper. + createFormulaListConstraint("INDIRECT(CONCATENATE(\"AccountName_\",$A1))"); + DataValidationConstraint tagConstraint=validationHelper. + createFormulaListConstraint("INDIRECT(CONCATENATE(\"Tags_\",$A1))"); + + DataValidation accountTypeValidation=validationHelper.createValidation(accountTypeConstraint,accountTypeRange); + DataValidation accountUsageValidation=validationHelper.createValidation(accountUsageConstraint,accountUsageRange); + DataValidation manualEntriesValidation=validationHelper.createValidation(booleanConstraint,manualEntriesAllowedRange); + DataValidation parentValidation=validationHelper.createValidation(parentConstraint,parentRange); + DataValidation tagValidation=validationHelper.createValidation(tagConstraint,tagRange); + + chartOfAccountsSheet.addValidationData(accountTypeValidation); + chartOfAccountsSheet.addValidationData(accountUsageValidation); + chartOfAccountsSheet.addValidationData(manualEntriesValidation); + chartOfAccountsSheet.addValidationData(parentValidation); + chartOfAccountsSheet.addValidationData(tagValidation); + } + + private void setNames(Sheet chartOfAccountsSheet,List<String> accountTypesNoDuplicatesList) { + Workbook chartOfAccountsWorkbook=chartOfAccountsSheet.getWorkbook(); + for (Integer i=0;i<accountTypesNoDuplicatesList.size();i++){ + Name tags=chartOfAccountsWorkbook.createName(); + Integer [] tagValueBeginEndIndexes=accountTypeToBeginEndIndexesofAccountNames.get(i); + if(accountTypeToBeginEndIndexesofAccountNames!=null){ + tags.setNameName("Tags_"+accountTypesNoDuplicatesList.get(i)); + tags.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME+ + "!$S$"+tagValueBeginEndIndexes[0]+":$S$"+tagValueBeginEndIndexes[1]); + } + Name accountNames=chartOfAccountsWorkbook.createName(); + Integer [] accountNamesBeginEndIndexes=accountTypeToBeginEndIndexesofAccountNames.get(i); + if (accountNamesBeginEndIndexes!=null){ + accountNames.setNameName("AccountName_"+accountTypesNoDuplicatesList.get(i)); + accountNames.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME+ + "!$Q$"+accountNamesBeginEndIndexes[0]+":$Q$"+accountNamesBeginEndIndexes[1]); + } + } + } + private void setDefaults(Sheet worksheet){ + try { + for (Integer rowNo = 1; rowNo < 3000; rowNo++) { + Row row = worksheet.getRow(rowNo); + if (row == null) + row = worksheet.createRow(rowNo); + writeFormula(ChartOfAcountsConstants.PARENT_ID_COL, row, + "IF(ISERROR(VLOOKUP($E"+(rowNo+1)+",$Q$2:$R$"+(glAccounts.size()+1)+",2,FALSE))," + + "\"\",(VLOOKUP($E"+(rowNo+1)+",$Q$2:$R$"+(glAccounts.size()+1)+",2,FALSE)))"); + writeFormula(ChartOfAcountsConstants.TAG_ID_COL,row, + "IF(ISERROR(VLOOKUP($H"+(rowNo+1)+",$S$2:$T$"+(glAccounts.size()+1)+",2,FALSE))," + + "\"\",(VLOOKUP($H"+(rowNo+1)+",$S$2:$T$"+(glAccounts.size()+1)+",2,FALSE)))"); + } + } catch (Exception e) { + e.printStackTrace(); + } + } + + private void setLookupTable(Sheet chartOfAccountsSheet) { + accountTypesNoDuplicatesList =new ArrayList<>(); + for (int i = 0; i <glAccounts.size() ; i++) { + if (!accountTypesNoDuplicatesList.contains(glAccounts.get(i).getType().getValue())) { + accountTypesNoDuplicatesList.add(glAccounts.get(i).getType().getValue()); + } + } + int rowIndex=1,startIndex=1,accountTypeIndex=0; + accountTypeToBeginEndIndexesofAccountNames= new HashMap<Integer,Integer[]>(); + for (String accountType: accountTypesNoDuplicatesList) { + startIndex=rowIndex+1; + Row row =chartOfAccountsSheet.createRow(rowIndex); + writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_TYPE_COL,row,accountType); + List<String> accountNamesandTags =accountTypeToAccountNameAndTag.get(accountType); + if (!accountNamesandTags.isEmpty()){ + for (String accountNameandTag:accountNamesandTags) { + if (chartOfAccountsSheet.getRow(rowIndex)!=null){ + String accountNameAndTagAr[]=accountNameandTag.split("-"); + writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL,row,accountNameAndTagAr[0]); + writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_ID_COL,row,accountNameAndTagAr[1]); + writeString(ChartOfAcountsConstants.LOOKUP_TAG_COL,row,accountNameAndTagAr[2]); + writeString(ChartOfAcountsConstants.LOOKUP_TAG_ID_COL,row,accountNameAndTagAr[3]); + rowIndex++; + }else{ + row =chartOfAccountsSheet.createRow(rowIndex); + String accountNameAndTagAr[]=accountNameandTag.split("-"); + writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL,row,accountNameAndTagAr[0]); + writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_ID_COL,row,accountNameAndTagAr[1]); + writeString(ChartOfAcountsConstants.LOOKUP_TAG_COL,row,accountNameAndTagAr[2]); + writeString(ChartOfAcountsConstants.LOOKUP_TAG_ID_COL,row,accountNameAndTagAr[3]); + rowIndex++; + } + } + accountTypeToBeginEndIndexesofAccountNames.put(accountTypeIndex++,new Integer[]{startIndex,rowIndex}); + }else { + accountTypeIndex++; + } + } + } + + private void setLayout(Sheet chartOfAccountsSheet) { + Row rowHeader=chartOfAccountsSheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.ACCOUNT_TYPE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.ACCOUNT_NAME_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.ACCOUNT_USAGE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.MANUAL_ENTRIES_ALLOWED_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.PARENT_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.PARENT_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.GL_CODE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.TAG_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.TAG_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.DESCRIPTION_COL,TemplatePopulateImportConstants.EXTRALARGE_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_ACCOUNT_TYPE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_ACCOUNT_ID_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_TAG_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_TAG_ID_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + + writeString(ChartOfAcountsConstants.ACCOUNT_TYPE_COL,rowHeader,"Account Type*"); + writeString(ChartOfAcountsConstants.GL_CODE_COL,rowHeader,"GL Code *"); + writeString(ChartOfAcountsConstants.ACCOUNT_USAGE_COL,rowHeader,"Account Usage *"); + writeString(ChartOfAcountsConstants.MANUAL_ENTRIES_ALLOWED_COL,rowHeader,"Manual entries allowed *"); + writeString(ChartOfAcountsConstants.PARENT_COL,rowHeader,"Parent"); + writeString(ChartOfAcountsConstants.PARENT_ID_COL,rowHeader,"Parent Id"); + writeString(ChartOfAcountsConstants.ACCOUNT_NAME_COL,rowHeader,"Account Name"); + writeString(ChartOfAcountsConstants.TAG_COL,rowHeader,"Tag *"); + writeString(ChartOfAcountsConstants.TAG_ID_COL,rowHeader,"Tag Id"); + writeString(ChartOfAcountsConstants.DESCRIPTION_COL,rowHeader,"Description *"); + writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_TYPE_COL,rowHeader,"Lookup Account type"); + writeString(ChartOfAcountsConstants.LOOKUP_TAG_COL,rowHeader,"Lookup Tag"); + writeString(ChartOfAcountsConstants.LOOKUP_TAG_ID_COL,rowHeader,"Lookup Tag Id"); + writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL,rowHeader,"Lookup Account name *"); + writeString(ChartOfAcountsConstants.LOOKUP_ACCOUNT_ID_COL,rowHeader,"Lookup Account Id"); + + } +} http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientEntityWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientEntityWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientEntityWorkbookPopulator.java new file mode 100644 index 0000000..9907985 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientEntityWorkbookPopulator.java @@ -0,0 +1,405 @@ +/** + * 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.client; + +import org.apache.fineract.infrastructure.bulkimport.constants.ClientEntityConstants; +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.infrastructure.bulkimport.populator.PersonnelSheetPopulator; +import org.apache.fineract.infrastructure.codes.data.CodeValueData; +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 ClientEntityWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + private PersonnelSheetPopulator personnelSheetPopulator; + private List<CodeValueData> clientTypeCodeValues; + private List<CodeValueData> constitutionCodeValues; + private List<CodeValueData> clientClassificationCodeValues; + private List<CodeValueData> addressTypesCodeValues; + private List<CodeValueData> stateProvinceCodeValues; + private List<CodeValueData> countryCodeValues; + private List<CodeValueData> mainBusinesslineCodeValues; + + + public ClientEntityWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + PersonnelSheetPopulator personnelSheetPopulator,List<CodeValueData>clientTypeCodeValues, + List<CodeValueData>constitutionCodeValues,List<CodeValueData>mainBusinessline , + List<CodeValueData>clientClassification,List<CodeValueData>addressTypesCodeValues, + List<CodeValueData>stateProvinceCodeValues,List<CodeValueData>countryCodeValues ) { + this.officeSheetPopulator = officeSheetPopulator; + this.personnelSheetPopulator = personnelSheetPopulator; + this.clientTypeCodeValues=clientTypeCodeValues; + this.constitutionCodeValues=constitutionCodeValues; + this.clientClassificationCodeValues=clientClassification; + this.addressTypesCodeValues=addressTypesCodeValues; + this.stateProvinceCodeValues=stateProvinceCodeValues; + this.countryCodeValues=countryCodeValues; + this.mainBusinesslineCodeValues=mainBusinessline; + } + + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet clientSheet = workbook.createSheet(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME); + personnelSheetPopulator.populate(workbook,dateFormat); + officeSheetPopulator.populate(workbook,dateFormat); + setLayout(clientSheet); + setOfficeDateLookupTable(clientSheet, officeSheetPopulator.getOffices(), + ClientEntityConstants.RELATIONAL_OFFICE_NAME_COL, ClientEntityConstants.RELATIONAL_OFFICE_OPENING_DATE_COL,dateFormat); + setClientDataLookupTable(clientSheet); + setRules(clientSheet,dateFormat); + } + + private void setClientDataLookupTable(Sheet clientSheet) { + int rowIndex=0; + for (CodeValueData clientTypeCodeValue:clientTypeCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientEntityConstants.LOOKUP_CLIENT_TYPES,row,clientTypeCodeValue.getName()+"-"+clientTypeCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData clientClassificationCodeValue:clientClassificationCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientEntityConstants.LOOKUP_CLIENT_CLASSIFICATION,row, + clientClassificationCodeValue.getName()+"-"+clientClassificationCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData constitutionCodeValue:constitutionCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientEntityConstants.LOOKUP_CONSTITUTION_COL,row, + constitutionCodeValue.getName()+"-"+constitutionCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData mainBusinessCodeValue:mainBusinesslineCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientEntityConstants.LOOKUP_MAIN_BUSINESS_LINE,row, + mainBusinessCodeValue.getName()+"-"+mainBusinessCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData addressTypeCodeValue:addressTypesCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientEntityConstants.LOOKUP_ADDRESS_TYPE,row, + addressTypeCodeValue.getName()+"-"+addressTypeCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData stateCodeValue:stateProvinceCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientEntityConstants.LOOKUP_STATE_PROVINCE,row, + stateCodeValue.getName()+"-"+stateCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData countryCodeValue: countryCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientEntityConstants.LOOKUP_COUNTRY,row, + countryCodeValue.getName()+"-"+countryCodeValue.getId()); + } + + } + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(ClientEntityConstants.NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + writeString(ClientEntityConstants.NAME_COL, rowHeader, "Name*"); + worksheet.setColumnWidth(ClientEntityConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.STAFF_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.INCOPORATION_DATE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.INCOPORATION_VALID_TILL_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.MOBILE_NO_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.CLIENT_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.CLIENT_CLASSIFICATION_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.INCOPORATION_NUMBER_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.MAIN_BUSINESS_LINE,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.CONSTITUTION_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.REMARKS_COL,TemplatePopulateImportConstants.LARGE_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.SUBMITTED_ON_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.ACTIVE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_ENABLED,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.STREET_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_LINE_1_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_LINE_2_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.ADDRESS_LINE_3_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.CITY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.STATE_PROVINCE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.COUNTRY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.POSTAL_CODE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.IS_ACTIVE_ADDRESS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.WARNING_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + worksheet.setColumnWidth(ClientEntityConstants.RELATIONAL_OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.RELATIONAL_OFFICE_OPENING_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_CONSTITUTION_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_CLIENT_TYPES,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_CLIENT_CLASSIFICATION,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_ADDRESS_TYPE,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_STATE_PROVINCE,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_COUNTRY,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientEntityConstants.LOOKUP_MAIN_BUSINESS_LINE,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + writeString(ClientEntityConstants.NAME_COL,rowHeader,"Name"); + writeString(ClientEntityConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(ClientEntityConstants.STAFF_NAME_COL, rowHeader, "Staff Name"); + writeString(ClientEntityConstants.INCOPORATION_DATE_COL,rowHeader,"Incorporation Date"); + writeString(ClientEntityConstants.INCOPORATION_VALID_TILL_COL,rowHeader,"Incorporation Validity Till Date"); + writeString(ClientEntityConstants.MOBILE_NO_COL, rowHeader, "Mobile number"); + writeString(ClientEntityConstants.CLIENT_TYPE_COL, rowHeader, "Client Type "); + writeString(ClientEntityConstants.CLIENT_CLASSIFICATION_COL, rowHeader, "Client Classification "); + writeString(ClientEntityConstants.INCOPORATION_NUMBER_COL,rowHeader,"Incorporation Number"); + writeString(ClientEntityConstants.MAIN_BUSINESS_LINE,rowHeader,"Main Business Line"); + writeString(ClientEntityConstants.CONSTITUTION_COL,rowHeader,"Constitution"); + writeString(ClientEntityConstants.REMARKS_COL,rowHeader,"Remarks"); + writeString(ClientEntityConstants.EXTERNAL_ID_COL, rowHeader, "External ID "); + writeString(ClientEntityConstants.SUBMITTED_ON_COL,rowHeader,"Submitted On Date"); + writeString(ClientEntityConstants.ACTIVE_COL, rowHeader, "Active*"); + writeString(ClientEntityConstants.ACTIVATION_DATE_COL, rowHeader, "Activation Date "); + writeString(ClientEntityConstants.ADDRESS_ENABLED,rowHeader,"Address Enabled "); + writeString(ClientEntityConstants.ADDRESS_TYPE_COL, rowHeader, "Address Type "); + writeString(ClientEntityConstants.STREET_COL, rowHeader, "Street "); + writeString(ClientEntityConstants.ADDRESS_LINE_1_COL, rowHeader, "Address Line 1"); + writeString(ClientEntityConstants.ADDRESS_LINE_2_COL, rowHeader, "Address Line 2"); + writeString(ClientEntityConstants.ADDRESS_LINE_3_COL, rowHeader, "Address Line 3"); + writeString(ClientEntityConstants.CITY_COL, rowHeader, "City"); + writeString(ClientEntityConstants.STATE_PROVINCE_COL, rowHeader, "State/ Province"); + writeString(ClientEntityConstants.COUNTRY_COL, rowHeader, "Country"); + writeString(ClientEntityConstants.POSTAL_CODE_COL, rowHeader, "Postal Code"); + writeString(ClientEntityConstants.IS_ACTIVE_ADDRESS_COL, rowHeader, "Is active Address ? "); + writeString(ClientEntityConstants.WARNING_COL, rowHeader, "All * marked fields are compulsory."); + + writeString(ClientEntityConstants.RELATIONAL_OFFICE_NAME_COL, rowHeader, "Lookup office Name "); + writeString(ClientEntityConstants.RELATIONAL_OFFICE_OPENING_DATE_COL, rowHeader, "Lookup Office Opened Date "); + writeString(ClientEntityConstants.LOOKUP_CONSTITUTION_COL, rowHeader, "Lookup Constitution "); + writeString(ClientEntityConstants.LOOKUP_CLIENT_TYPES, rowHeader, "Lookup Client Types "); + writeString(ClientEntityConstants.LOOKUP_CLIENT_CLASSIFICATION, rowHeader, "Lookup Client Classification "); + writeString(ClientEntityConstants.LOOKUP_ADDRESS_TYPE, rowHeader, "Lookup AddressType "); + writeString(ClientEntityConstants.LOOKUP_STATE_PROVINCE, rowHeader, "Lookup State/Province "); + writeString(ClientEntityConstants.LOOKUP_COUNTRY, rowHeader, "Lookup Country "); + writeString(ClientEntityConstants.LOOKUP_MAIN_BUSINESS_LINE,rowHeader,"Lookup Business Line"); + + + } + + private void setRules(Sheet worksheet,String dateFormat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.OFFICE_NAME_COL, + ClientEntityConstants.OFFICE_NAME_COL); + CellRangeAddressList staffNameRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.STAFF_NAME_COL, ClientEntityConstants.STAFF_NAME_COL); + CellRangeAddressList submittedOnDateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.SUBMITTED_ON_COL,ClientEntityConstants. SUBMITTED_ON_COL); + CellRangeAddressList dateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. ACTIVATION_DATE_COL,ClientEntityConstants. ACTIVATION_DATE_COL); + CellRangeAddressList activeRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. ACTIVE_COL,ClientEntityConstants. ACTIVE_COL); + CellRangeAddressList clientTypeRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. CLIENT_TYPE_COL,ClientEntityConstants. CLIENT_TYPE_COL); + CellRangeAddressList constitutionRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.CONSTITUTION_COL,ClientEntityConstants. CONSTITUTION_COL); + CellRangeAddressList mainBusinessLineRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. MAIN_BUSINESS_LINE,ClientEntityConstants. MAIN_BUSINESS_LINE); + CellRangeAddressList clientClassificationRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.CLIENT_CLASSIFICATION_COL, + ClientEntityConstants.CLIENT_CLASSIFICATION_COL); + CellRangeAddressList enabledAddressRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.ADDRESS_ENABLED, ClientEntityConstants.ADDRESS_ENABLED); + CellRangeAddressList addressTypeRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.ADDRESS_TYPE_COL,ClientEntityConstants. ADDRESS_TYPE_COL); + CellRangeAddressList stateProvinceRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.STATE_PROVINCE_COL,ClientEntityConstants. STATE_PROVINCE_COL); + CellRangeAddressList countryRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. COUNTRY_COL,ClientEntityConstants. COUNTRY_COL); + CellRangeAddressList activeAddressRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientEntityConstants. IS_ACTIVE_ADDRESS_COL,ClientEntityConstants. IS_ACTIVE_ADDRESS_COL); + CellRangeAddressList incorporateDateRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.INCOPORATION_DATE_COL,ClientEntityConstants.INCOPORATION_DATE_COL); + CellRangeAddressList incorporateDateTillRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientEntityConstants.INCOPORATION_VALID_TILL_COL, + ClientEntityConstants.INCOPORATION_VALID_TILL_COL); + + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet); + + List<OfficeData> offices = officeSheetPopulator.getOffices(); + setNames(worksheet, offices); + + DataValidationConstraint officeNameConstraint = + validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint staffNameConstraint = + validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$B1))"); + DataValidationConstraint submittedOnDateConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL, + "=$O1" ,null, dateFormat); + DataValidationConstraint activationDateConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, + "=VLOOKUP($B1,$AJ$2:$AK" + (offices.size() + 1) + ",2,FALSE)", "=TODAY()", dateFormat); + DataValidationConstraint activeConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + DataValidationConstraint clientTypesConstraint = + validationHelper.createFormulaListConstraint("ClientTypes"); + DataValidationConstraint constitutionConstraint = + validationHelper.createFormulaListConstraint("Constitution"); + DataValidationConstraint mainBusinessLineConstraint = + validationHelper.createFormulaListConstraint("MainBusinessLine"); + DataValidationConstraint clientClassificationConstraint = + validationHelper.createFormulaListConstraint("ClientClassification"); + DataValidationConstraint enabledAddressConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + DataValidationConstraint addressTypeConstraint = + validationHelper.createFormulaListConstraint("AddressType"); + DataValidationConstraint stateProvinceConstraint = + validationHelper.createFormulaListConstraint("StateProvince"); + DataValidationConstraint countryConstraint = + validationHelper.createFormulaListConstraint("Country"); + DataValidationConstraint activeAddressConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + DataValidationConstraint incorpDateConstraint= + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL, + "=TODAY()",null,dateFormat); + DataValidationConstraint incorpDateTillConstraint= + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.GREATER_OR_EQUAL, + "=TODAY()",null,dateFormat); + + + DataValidation officeValidation = + validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation staffValidation = + validationHelper.createValidation(staffNameConstraint, staffNameRange); + DataValidation submittedOnDateValidation = + validationHelper.createValidation(submittedOnDateConstraint, submittedOnDateRange); + DataValidation activationDateValidation = + validationHelper.createValidation(activationDateConstraint, dateRange); + DataValidation activeValidation = + validationHelper.createValidation(activeConstraint, activeRange); + DataValidation clientTypeValidation = + validationHelper.createValidation(clientTypesConstraint, clientTypeRange); + DataValidation constitutionValidation = + validationHelper.createValidation(constitutionConstraint,constitutionRange); + DataValidation mainBusinessLineValidation = + validationHelper.createValidation(mainBusinessLineConstraint,mainBusinessLineRange); + DataValidation clientClassificationValidation = + validationHelper.createValidation(clientClassificationConstraint, clientClassificationRange); + DataValidation enabledAddressValidation= + validationHelper.createValidation(enabledAddressConstraint,enabledAddressRange); + DataValidation addressTypeValidation = + validationHelper.createValidation(addressTypeConstraint, addressTypeRange); + DataValidation stateProvinceValidation = + validationHelper.createValidation(stateProvinceConstraint, stateProvinceRange); + DataValidation countryValidation = + validationHelper.createValidation(countryConstraint, countryRange); + DataValidation activeAddressValidation = + validationHelper.createValidation(activeAddressConstraint,activeAddressRange); + DataValidation incorporateDateValidation=validationHelper.createValidation(incorpDateConstraint,incorporateDateRange); + DataValidation incorporateDateTillValidation=validationHelper.createValidation(incorpDateTillConstraint,incorporateDateTillRange); + + worksheet.addValidationData(activeValidation); + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(staffValidation); + worksheet.addValidationData(activationDateValidation); + worksheet.addValidationData(submittedOnDateValidation); + worksheet.addValidationData(clientTypeValidation); + worksheet.addValidationData(constitutionValidation); + worksheet.addValidationData(mainBusinessLineValidation); + worksheet.addValidationData(clientClassificationValidation); + worksheet.addValidationData(enabledAddressValidation); + worksheet.addValidationData(addressTypeValidation); + worksheet.addValidationData(stateProvinceValidation); + worksheet.addValidationData(countryValidation); + worksheet.addValidationData(activeAddressValidation); + worksheet.addValidationData(incorporateDateValidation); + worksheet.addValidationData(incorporateDateTillValidation); + } + + private void setNames(Sheet worksheet, List<OfficeData> offices) { + Workbook clientWorkbook = worksheet.getWorkbook(); + Name officeGroup = clientWorkbook.createName(); + officeGroup.setNameName("Office"); + officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (offices.size() + 1)); + + Name clientTypeGroup = clientWorkbook.createName(); + clientTypeGroup.setNameName("ClientTypes"); + clientTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AN$2:$AN$" + + (clientTypeCodeValues.size() + 1)); + + Name constitutionGroup = clientWorkbook.createName(); + constitutionGroup.setNameName("Constitution"); + constitutionGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AL$2:$AL$" + + (constitutionCodeValues.size() + 1)); + + Name mainBusinessLineGroup = clientWorkbook.createName(); + mainBusinessLineGroup.setNameName("MainBusinessLine"); + mainBusinessLineGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AR$2:$AR$" + + (mainBusinesslineCodeValues.size() + 1)); + + Name clientClassficationGroup = clientWorkbook.createName(); + clientClassficationGroup.setNameName("ClientClassification"); + clientClassficationGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AM$2:$AM$" + + (clientClassificationCodeValues.size() + 1)); + + Name addressTypeGroup = clientWorkbook.createName(); + addressTypeGroup.setNameName("AddressType"); + addressTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+ + "!$AO$2:$AO$" + (addressTypesCodeValues.size() + 1)); + + Name stateProvinceGroup = clientWorkbook.createName(); + stateProvinceGroup.setNameName("StateProvince"); + stateProvinceGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+ + "!$AP$2:$AP$" + (stateProvinceCodeValues.size() + 1)); + + Name countryGroup = clientWorkbook.createName(); + countryGroup.setNameName("Country"); + countryGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_ENTITY_SHEET_NAME+"!$AQ$2:$AQ$" + + (countryCodeValues.size() + 1)); + + for (Integer i = 0; i < offices.size(); i++) { + Integer[] officeNameToBeginEndIndexesOfStaff = + personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i); + if (officeNameToBeginEndIndexesOfStaff != null) { + Name name = clientWorkbook.createName(); + name.setNameName("Staff_" + offices.get(i).name().trim().replaceAll("[ )(]", "_")); + name.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+"!$B$" + + officeNameToBeginEndIndexesOfStaff[0] + ":$B$" + + officeNameToBeginEndIndexesOfStaff[1]); + } + } + } + +} http://git-wip-us.apache.org/repos/asf/fineract/blob/210647d4/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientPersonWorkbookPopulator.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientPersonWorkbookPopulator.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientPersonWorkbookPopulator.java new file mode 100644 index 0000000..69d1119 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/client/ClientPersonWorkbookPopulator.java @@ -0,0 +1,370 @@ +/** + * 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.client; + +import org.apache.fineract.infrastructure.bulkimport.constants.ClientPersonConstants; +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.infrastructure.bulkimport.populator.PersonnelSheetPopulator; +import org.apache.fineract.infrastructure.codes.data.CodeValueData; +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 ClientPersonWorkbookPopulator extends AbstractWorkbookPopulator { + + private OfficeSheetPopulator officeSheetPopulator; + private PersonnelSheetPopulator personnelSheetPopulator; + private List<CodeValueData>clientTypeCodeValues; + private List<CodeValueData>genderCodeValues; + private List<CodeValueData>clientClassificationCodeValues; + private List<CodeValueData>addressTypesCodeValues; + private List<CodeValueData>stateProvinceCodeValues; + private List<CodeValueData>countryCodeValues; + + + public ClientPersonWorkbookPopulator(OfficeSheetPopulator officeSheetPopulator, + PersonnelSheetPopulator personnelSheetPopulator,List<CodeValueData>clientTypeCodeValues, + List<CodeValueData>genderCodeValues, List<CodeValueData>clientClassification,List<CodeValueData>addressTypesCodeValues, + List<CodeValueData>stateProvinceCodeValues,List<CodeValueData>countryCodeValues ) { + this.officeSheetPopulator = officeSheetPopulator; + this.personnelSheetPopulator = personnelSheetPopulator; + this.clientTypeCodeValues=clientTypeCodeValues; + this.genderCodeValues=genderCodeValues; + this.clientClassificationCodeValues=clientClassification; + this.addressTypesCodeValues=addressTypesCodeValues; + this.stateProvinceCodeValues=stateProvinceCodeValues; + this.countryCodeValues=countryCodeValues; + } + + + @Override + public void populate(Workbook workbook,String dateFormat) { + Sheet clientSheet = workbook.createSheet(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME); + personnelSheetPopulator.populate(workbook,dateFormat); + officeSheetPopulator.populate(workbook,dateFormat); + setLayout(clientSheet); + setOfficeDateLookupTable(clientSheet, officeSheetPopulator.getOffices(), + ClientPersonConstants.RELATIONAL_OFFICE_NAME_COL, ClientPersonConstants.RELATIONAL_OFFICE_OPENING_DATE_COL,dateFormat); + setClientDataLookupTable(clientSheet); + setRules(clientSheet,dateFormat); + } + + private void setClientDataLookupTable(Sheet clientSheet) { + int rowIndex=0; + for (CodeValueData clientTypeCodeValue:clientTypeCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientPersonConstants.LOOKUP_CLIENT_TYPES_COL,row,clientTypeCodeValue.getName()+ + "-"+clientTypeCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData clientClassificationCodeValue:clientClassificationCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientPersonConstants.LOOKUP_CLIENT_CLASSIFICATION_COL,row, + clientClassificationCodeValue.getName()+"-"+clientClassificationCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData genderCodeValue:genderCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientPersonConstants.LOOKUP_GENDER_COL,row,genderCodeValue.getName()+"-"+genderCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData addressTypeCodeValue:addressTypesCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientPersonConstants.LOOKUP_ADDRESS_TYPE_COL,row, + addressTypeCodeValue.getName()+"-"+addressTypeCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData stateCodeValue:stateProvinceCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientPersonConstants.LOOKUP_STATE_PROVINCE_COL,row,stateCodeValue.getName()+"-"+stateCodeValue.getId()); + } + rowIndex=0; + for (CodeValueData countryCodeValue: countryCodeValues) { + Row row =clientSheet.getRow(++rowIndex); + if(row==null) + row=clientSheet.createRow(rowIndex); + writeString(ClientPersonConstants.LOOKUP_COUNTRY_COL,row,countryCodeValue.getName()+"-"+countryCodeValue.getId()); + } + + } + + private void setLayout(Sheet worksheet) { + Row rowHeader = worksheet.createRow(TemplatePopulateImportConstants.ROWHEADER_INDEX); + rowHeader.setHeight(TemplatePopulateImportConstants.ROW_HEADER_HEIGHT); + worksheet.setColumnWidth(ClientPersonConstants.FIRST_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.LAST_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.MIDDLE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + writeString(ClientPersonConstants.FIRST_NAME_COL, rowHeader, "First Name*"); + writeString(ClientPersonConstants.LAST_NAME_COL, rowHeader, "Last Name*"); + writeString(ClientPersonConstants.MIDDLE_NAME_COL, rowHeader, "Middle Name"); + worksheet.setColumnWidth(ClientPersonConstants.OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.STAFF_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.EXTERNAL_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.SUBMITTED_ON_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.ACTIVATION_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.ACTIVE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.MOBILE_NO_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.DOB_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.CLIENT_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.GENDER_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.CLIENT_CLASSIFICATION_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.IS_STAFF_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_ENABLED_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.STREET_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_LINE_1_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_LINE_2_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.ADDRESS_LINE_3_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.CITY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.STATE_PROVINCE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.COUNTRY_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.POSTAL_CODE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.IS_ACTIVE_ADDRESS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.WARNING_COL,TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + + worksheet.setColumnWidth(ClientPersonConstants.RELATIONAL_OFFICE_NAME_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.RELATIONAL_OFFICE_OPENING_DATE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_GENDER_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_CLIENT_TYPES_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_CLIENT_CLASSIFICATION_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_ADDRESS_TYPE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_STATE_PROVINCE_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + worksheet.setColumnWidth(ClientPersonConstants.LOOKUP_COUNTRY_COL,TemplatePopulateImportConstants.SMALL_COL_SIZE); + writeString(ClientPersonConstants.OFFICE_NAME_COL, rowHeader, "Office Name*"); + writeString(ClientPersonConstants.STAFF_NAME_COL, rowHeader, "Staff Name"); + writeString(ClientPersonConstants.EXTERNAL_ID_COL, rowHeader, "External ID "); + writeString(ClientPersonConstants.SUBMITTED_ON_COL,rowHeader,"Submitted On Date"); + writeString(ClientPersonConstants.ACTIVATION_DATE_COL, rowHeader, "Activation date"); + writeString(ClientPersonConstants.ACTIVE_COL, rowHeader, "Active*"); + writeString(ClientPersonConstants.MOBILE_NO_COL, rowHeader, "Mobile number"); + writeString(ClientPersonConstants.DOB_COL, rowHeader, "Date of Birth "); + writeString(ClientPersonConstants.CLIENT_TYPE_COL, rowHeader, "Client Type "); + writeString(ClientPersonConstants.IS_STAFF_COL, rowHeader, "Is a staff memeber "); + writeString(ClientPersonConstants.GENDER_COL, rowHeader, "Gender "); + writeString(ClientPersonConstants.ADDRESS_ENABLED_COL,rowHeader,"Address Enabled *"); + writeString(ClientPersonConstants.CLIENT_CLASSIFICATION_COL, rowHeader, "Client Classification "); + writeString(ClientPersonConstants.ADDRESS_TYPE_COL, rowHeader, "Address Type "); + writeString(ClientPersonConstants.STREET_COL, rowHeader, "Street "); + writeString(ClientPersonConstants.ADDRESS_LINE_1_COL, rowHeader, "Address Line 1"); + writeString(ClientPersonConstants.ADDRESS_LINE_2_COL, rowHeader, "Address Line 2"); + writeString(ClientPersonConstants.ADDRESS_LINE_3_COL, rowHeader, "Address Line 3 "); + writeString(ClientPersonConstants.CITY_COL, rowHeader, "City "); + writeString(ClientPersonConstants.STATE_PROVINCE_COL, rowHeader, "State/ Province "); + writeString(ClientPersonConstants.COUNTRY_COL, rowHeader, "Country "); + writeString(ClientPersonConstants.POSTAL_CODE_COL, rowHeader, "Postal Code "); + writeString(ClientPersonConstants.IS_ACTIVE_ADDRESS_COL, rowHeader, "Is active Address ? "); + writeString(ClientPersonConstants.WARNING_COL, rowHeader, "All * marked fields are compulsory."); + + writeString(ClientPersonConstants.RELATIONAL_OFFICE_NAME_COL, rowHeader, "Lookup office Name "); + writeString(ClientPersonConstants.RELATIONAL_OFFICE_OPENING_DATE_COL, rowHeader, "Lookup Office Opened Date "); + writeString(ClientPersonConstants.LOOKUP_GENDER_COL, rowHeader, "Lookup Gender "); + writeString(ClientPersonConstants.LOOKUP_CLIENT_TYPES_COL, rowHeader, "Lookup Client Types "); + writeString(ClientPersonConstants.LOOKUP_CLIENT_CLASSIFICATION_COL, rowHeader, "Lookup Client Classification "); + writeString(ClientPersonConstants.LOOKUP_ADDRESS_TYPE_COL, rowHeader, "Lookup AddressType "); + writeString(ClientPersonConstants.LOOKUP_STATE_PROVINCE_COL, rowHeader, "Lookup State/Province "); + writeString(ClientPersonConstants.LOOKUP_COUNTRY_COL, rowHeader, "Lookup Country "); + + + } + + private void setRules(Sheet worksheet,String dateformat) { + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.OFFICE_NAME_COL, ClientPersonConstants.OFFICE_NAME_COL); + CellRangeAddressList staffNameRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. STAFF_NAME_COL,ClientPersonConstants. STAFF_NAME_COL); + CellRangeAddressList submittedOnDateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. SUBMITTED_ON_COL, ClientPersonConstants.SUBMITTED_ON_COL); + CellRangeAddressList activationDateRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.ACTIVATION_DATE_COL, ClientPersonConstants.ACTIVATION_DATE_COL); + CellRangeAddressList activeRange = new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.ACTIVE_COL,ClientPersonConstants. ACTIVE_COL); + CellRangeAddressList clientTypeRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. CLIENT_TYPE_COL,ClientPersonConstants. CLIENT_TYPE_COL); + CellRangeAddressList dobRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. DOB_COL,ClientPersonConstants. DOB_COL); + CellRangeAddressList isStaffRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. IS_STAFF_COL,ClientPersonConstants. IS_STAFF_COL); + CellRangeAddressList genderRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.GENDER_COL,ClientPersonConstants. GENDER_COL); + CellRangeAddressList clientClassificationRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.CLIENT_CLASSIFICATION_COL, ClientPersonConstants.CLIENT_CLASSIFICATION_COL); + CellRangeAddressList enabledAddressRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.ADDRESS_ENABLED_COL, ClientPersonConstants.ADDRESS_ENABLED_COL); + CellRangeAddressList addressTypeRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. ADDRESS_TYPE_COL, ClientPersonConstants.ADDRESS_TYPE_COL); + CellRangeAddressList stateProvinceRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. STATE_PROVINCE_COL, ClientPersonConstants.STATE_PROVINCE_COL); + CellRangeAddressList countryRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(), ClientPersonConstants.COUNTRY_COL, ClientPersonConstants.COUNTRY_COL); + CellRangeAddressList activeAddressRange=new CellRangeAddressList(1, + SpreadsheetVersion.EXCEL97.getLastRowIndex(),ClientPersonConstants. IS_ACTIVE_ADDRESS_COL,ClientPersonConstants. IS_ACTIVE_ADDRESS_COL); + + + DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) worksheet); + + List<OfficeData> offices = officeSheetPopulator.getOffices(); + setNames(worksheet, offices); + + DataValidationConstraint officeNameConstraint = + validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint staffNameConstraint = + validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Staff_\",$D1))"); + DataValidationConstraint submittedOnDateConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL, + "=$I1" ,null,dateformat); + DataValidationConstraint activationDateConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, + "=VLOOKUP($D1,$AJ$2:$AK" + (offices.size() + 1) + ",2,FALSE)", "=TODAY()", dateformat); + DataValidationConstraint dobDateConstraint = + validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.LESS_OR_EQUAL, + "=TODAY()",null, dateformat); + DataValidationConstraint activeConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + DataValidationConstraint clientTypesConstraint = + validationHelper.createFormulaListConstraint("ClientTypes"); + DataValidationConstraint isStaffConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + DataValidationConstraint genderConstraint = + validationHelper.createFormulaListConstraint("Gender"); + DataValidationConstraint clientClassificationConstraint = + validationHelper.createFormulaListConstraint("ClientClassification"); + DataValidationConstraint enabledAddressConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + DataValidationConstraint addressTypeConstraint = + validationHelper.createFormulaListConstraint("AddressType"); + DataValidationConstraint stateProvinceConstraint = + validationHelper.createFormulaListConstraint("StateProvince"); + DataValidationConstraint countryConstraint = + validationHelper.createFormulaListConstraint("Country"); + DataValidationConstraint activeAddressConstraint = + validationHelper.createExplicitListConstraint(new String[] {"True", "False"}); + + DataValidation officeValidation = + validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation staffValidation = + validationHelper.createValidation(staffNameConstraint, staffNameRange); + DataValidation submittedOnDateValidation = + validationHelper.createValidation(submittedOnDateConstraint, submittedOnDateRange); + DataValidation activationDateValidation = + validationHelper.createValidation(activationDateConstraint, activationDateRange); + DataValidation dobDateValidation = + validationHelper.createValidation(dobDateConstraint, dobRange); + DataValidation activeValidation = + validationHelper.createValidation(activeConstraint, activeRange); + DataValidation clientTypeValidation = + validationHelper.createValidation(clientTypesConstraint, clientTypeRange); + DataValidation isStaffValidation = + validationHelper.createValidation(isStaffConstraint, isStaffRange); + DataValidation genderValidation = + validationHelper.createValidation(genderConstraint, genderRange); + DataValidation clientClassificationValidation = + validationHelper.createValidation(clientClassificationConstraint, clientClassificationRange); + DataValidation enabledAddressValidation= + validationHelper.createValidation(enabledAddressConstraint,enabledAddressRange); + DataValidation addressTypeValidation = + validationHelper.createValidation(addressTypeConstraint, addressTypeRange); + DataValidation stateProvinceValidation = + validationHelper.createValidation(stateProvinceConstraint, stateProvinceRange); + DataValidation countryValidation = + validationHelper.createValidation(countryConstraint, countryRange); + DataValidation activeAddressValidation = + validationHelper.createValidation(activeAddressConstraint,activeAddressRange); + + worksheet.addValidationData(activeValidation); + worksheet.addValidationData(officeValidation); + worksheet.addValidationData(staffValidation); + worksheet.addValidationData(activationDateValidation); + worksheet.addValidationData(submittedOnDateValidation); + worksheet.addValidationData(dobDateValidation); + worksheet.addValidationData(clientTypeValidation); + worksheet.addValidationData(isStaffValidation); + worksheet.addValidationData(genderValidation); + worksheet.addValidationData(clientClassificationValidation); + worksheet.addValidationData(enabledAddressValidation); + worksheet.addValidationData(addressTypeValidation); + worksheet.addValidationData(stateProvinceValidation); + worksheet.addValidationData(countryValidation); + worksheet.addValidationData(activeAddressValidation); + } + + private void setNames(Sheet worksheet, List<OfficeData> offices) { + Workbook clientWorkbook = worksheet.getWorkbook(); + Name officeGroup = clientWorkbook.createName(); + officeGroup.setNameName("Office"); + officeGroup.setRefersToFormula(TemplatePopulateImportConstants.OFFICE_SHEET_NAME+"!$B$2:$B$" + (offices.size() + 1)); + + Name clientTypeGroup = clientWorkbook.createName(); + clientTypeGroup.setNameName("ClientTypes"); + clientTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AN$2:$AN$" + + (clientTypeCodeValues.size() + 1)); + + Name genderGroup = clientWorkbook.createName(); + genderGroup.setNameName("Gender"); + genderGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AL$2:$AL$" + (genderCodeValues.size() + 1)); + + Name clientClassficationGroup = clientWorkbook.createName(); + clientClassficationGroup.setNameName("ClientClassification"); + clientClassficationGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AM$2:$AM$" + + (clientClassificationCodeValues.size() + 1)); + + Name addressTypeGroup = clientWorkbook.createName(); + addressTypeGroup.setNameName("AddressType"); + addressTypeGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AO$2:$AO$" + + (addressTypesCodeValues.size() + 1)); + + Name stateProvinceGroup = clientWorkbook.createName(); + stateProvinceGroup.setNameName("StateProvince"); + stateProvinceGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AP$2:$AP$" + + (stateProvinceCodeValues.size() + 1)); + + Name countryGroup = clientWorkbook.createName(); + countryGroup.setNameName("Country"); + countryGroup.setRefersToFormula(TemplatePopulateImportConstants.CLIENT_PERSON_SHEET_NAME+"!$AQ$2:$AQ$" + + (countryCodeValues.size() + 1)); + + for (Integer i = 0; i < offices.size(); i++) { + Integer[] officeNameToBeginEndIndexesOfStaff = + personnelSheetPopulator.getOfficeNameToBeginEndIndexesOfStaff().get(i); + if (officeNameToBeginEndIndexesOfStaff != null) { + Name name = clientWorkbook.createName(); + name.setNameName("Staff_" + offices.get(i).name().trim().replaceAll("[ )(]", "_")); + name.setRefersToFormula(TemplatePopulateImportConstants.STAFF_SHEET_NAME+"!$B$" + + officeNameToBeginEndIndexesOfStaff[0] + ":$B$" + officeNameToBeginEndIndexesOfStaff[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/comparator/LoanComparatorByStatusActive.java ---------------------------------------------------------------------- diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/comparator/LoanComparatorByStatusActive.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/comparator/LoanComparatorByStatusActive.java new file mode 100644 index 0000000..9a81574 --- /dev/null +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/comparator/LoanComparatorByStatusActive.java @@ -0,0 +1,52 @@ +/** + * 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.comparator; + +import org.apache.fineract.portfolio.loanaccount.data.LoanAccountData; + +import java.util.Comparator; + +/** + * Sorting the loan values based on loan status giving priority to active loans + * */ + +public class LoanComparatorByStatusActive implements Comparator<LoanAccountData> { + + @Override + public int compare(LoanAccountData o1, LoanAccountData o2) { + + boolean isData1StatusActive = o1.getStatusStringValue().equals("Active"); + boolean isData2StatusActive = o2.getStatusStringValue().equals("Active"); + + // if both status active, these have the same rank + if (isData1StatusActive && isData2StatusActive){ + return 0; + } + + if (isData1StatusActive){ + return -1; + } + + if (isData2StatusActive){ + return 1; + } + // if no status active, these have the same rank + return 0; + } +} \ No newline at end of file
