This is an automated email from the ASF dual-hosted git repository. avikg pushed a commit to branch develop in repository https://gitbox.apache.org/repos/asf/fineract.git
The following commit(s) were added to refs/heads/develop by this push: new 346279c FINERACT-1400 Bulkimport to import opening balances for Ledgers (#1890) 346279c is described below commit 346279caf94085e2fe953c1ac52d522085d82c8b Author: ankita10r <92036361+ankita...@users.noreply.github.com> AuthorDate: Wed Oct 13 21:42:34 2021 +0530 FINERACT-1400 Bulkimport to import opening balances for Ledgers (#1890) --- .../glaccount/domain/GLAccountRepository.java | 3 + .../domain/GLAccountRepositoryWrapper.java | 4 + .../exception/GLAccountNotFoundException.java | 4 + .../journalentry/data/JournalEntryData.java | 47 ++++++ .../constants/ChartOfAcountsConstants.java | 24 ++- .../ChartOfAccountsImportHandler.java | 166 ++++++++++++++++++--- .../chartofaccounts/ChartOfAccountsWorkbook.java | 96 ++++++++++-- .../BulkImportWorkbookPopulatorServiceImpl.java | 8 +- 8 files changed, 309 insertions(+), 43 deletions(-) diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/GLAccountRepository.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/GLAccountRepository.java index a309f06..cf258bd 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/GLAccountRepository.java +++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/GLAccountRepository.java @@ -18,9 +18,12 @@ */ package org.apache.fineract.accounting.glaccount.domain; +import java.util.Optional; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; public interface GLAccountRepository extends JpaRepository<GLAccount, Long>, JpaSpecificationExecutor<GLAccount> { // no added behaviour + //adding behaviour to fetch id by glcode for opening balance bulk import + Optional<GLAccount> findOneByGlCode(String glCode); } diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/GLAccountRepositoryWrapper.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/GLAccountRepositoryWrapper.java index d1973a8..1482c84 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/GLAccountRepositoryWrapper.java +++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/domain/GLAccountRepositoryWrapper.java @@ -40,5 +40,9 @@ public class GLAccountRepositoryWrapper { public GLAccount findOneWithNotFoundDetection(final Long id) { return this.repository.findById(id).orElseThrow(() -> new GLAccountNotFoundException(id)); } + //finding account id by glcode for opening balance bulk import + public GLAccount findOneByGlCodeWithNotFoundDetection(final String glCode) { + return this.repository.findOneByGlCode(glCode).orElseThrow(() -> new GLAccountNotFoundException(glCode)); + } } diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/exception/GLAccountNotFoundException.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/exception/GLAccountNotFoundException.java index 01b7c63..1e4c450 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/exception/GLAccountNotFoundException.java +++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/exception/GLAccountNotFoundException.java @@ -33,4 +33,8 @@ public class GLAccountNotFoundException extends AbstractPlatformResourceNotFound public GLAccountNotFoundException(final Long id, EmptyResultDataAccessException e) { super("error.msg.glaccount.id.invalid", "General Ledger account with identifier " + id + " does not exist ", id, e); } + + public GLAccountNotFoundException(final String glCode) { + super("error.msg.glaccount.code.invalid", "General Ledger account with GlCode " + glCode + " does not exist ", glCode); + } } diff --git a/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/data/JournalEntryData.java b/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/data/JournalEntryData.java index 3c00ab3..22865ed 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/data/JournalEntryData.java +++ b/fineract-provider/src/main/java/org/apache/fineract/accounting/journalentry/data/JournalEntryData.java @@ -90,6 +90,48 @@ public class JournalEntryData { private String routingCode; private String receiptNumber; private String bankNumber; + //for opening bal bulk import + public JournalEntryData(Long officeId, LocalDate transactionDate, String currencyCode, List<CreditDebit> credits, + List<CreditDebit> debits, String locale, String dateFormat) { + this.officeId = officeId; + this.dateFormat = dateFormat; + this.locale = locale; + this.transactionDate = transactionDate; + this.currencyCode = currencyCode; + this.credits = credits; + this.debits = debits; + this.rowIndex = null; + this.paymentTypeId = null; + this.accountNumber = null; + this.checkNumber = null; + this.routingCode = null; + this.receiptNumber = null; + this.bankNumber = null; + this.comments = null; + this.id = null; + this.officeName = null; + this.glAccountName = null; + this.glAccountId = null; + this.glAccountCode = null; + this.glAccountType = null; + this.entryType = null; + this.amount = null; + this.currency = null; + this.transactionId = null; + this.manualEntry = null; + this.entityType = null; + this.entityId = null; + this.createdByUserId = null; + this.createdDate = null; + this.createdByUserName = null; + this.reversed = null; + this.referenceNumber = null; + this.officeRunningBalance = null; + this.organizationRunningBalance = null; + this.runningBalanceComputed = null; + this.transactionDetails = null; + + } public static JournalEntryData importInstance(Long officeId, LocalDate transactionDate, String currencyCode, Long paymentTypeId, Integer rowIndex, List<CreditDebit> credits, List<CreditDebit> debits, String accountNumber, String checkNumber, @@ -98,6 +140,11 @@ public class JournalEntryData { checkNumber, routingCode, receiptNumber, bankNumber, comments, locale, dateFormat); } + public static JournalEntryData importInstance1(Long officeId, LocalDate transactionDate, String currencyCode, List<CreditDebit> credits, + List<CreditDebit> debits, String locale, String dateFormat) { + return new JournalEntryData(officeId, transactionDate, currencyCode, credits, debits, locale, dateFormat); + } + private JournalEntryData(Long officeId, LocalDate transactionDate, String currencyCode, Long paymentTypeId, Integer rowIndex, List<CreditDebit> credits, List<CreditDebit> debits, String accountNumber, String checkNumber, String routingCode, String receiptNumber, String bankNumber, String comments, String locale, String dateFormat) { diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/constants/ChartOfAcountsConstants.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/constants/ChartOfAcountsConstants.java index 59a9032..72b5414 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/constants/ChartOfAcountsConstants.java +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/constants/ChartOfAcountsConstants.java @@ -34,10 +34,22 @@ public final class ChartOfAcountsConstants { public static final int TAG_COL = 7;// H public static final int TAG_ID_COL = 8;// I public static final int DESCRIPTION_COL = 9;// J - public static final int LOOKUP_ACCOUNT_TYPE_COL = 15;// P - public static final int LOOKUP_ACCOUNT_NAME_COL = 16; // Q - public static final int LOOKUP_ACCOUNT_ID_COL = 17;// R - public static final int LOOKUP_TAG_COL = 18; // S - public static final int LOOKUP_TAG_ID_COL = 19; // T - public static final int STATUS_COL = 20; + //adding for opening balance bulk import + public static final int OFFICE_COL = 10; // K + public static final int OFFICE_COL_ID = 11; // L + public static final int CURRENCY_CODE = 12; // M + public static final int DEBIT_AMOUNT = 13; // N + public static final int CREDIT_AMOUNT = 14; // O + + public static final int LOOKUP_ACCOUNT_TYPE_COL = 18;// S + public static final int LOOKUP_ACCOUNT_NAME_COL = 19; // T + public static final int LOOKUP_ACCOUNT_ID_COL = 20;// U + public static final int LOOKUP_TAG_COL = 21; // V + public static final int LOOKUP_TAG_ID_COL = 22; // W + + //adding for opening balance bulk import + public static final int LOOKUP_OFFICE_COL = 23; // X + public static final int LOOKUP_OFFICE_ID_COL = 24; // Y + + public static final int STATUS_COL = 25; } diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java index 02d99bc..c0b264a 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java @@ -19,11 +19,18 @@ package org.apache.fineract.infrastructure.bulkimport.importhandler.chartofaccounts; import com.google.gson.GsonBuilder; +import java.math.BigDecimal; +import java.time.LocalDate; +import java.time.ZoneId; import java.util.ArrayList; import java.util.List; import org.apache.fineract.accounting.glaccount.data.GLAccountData; +import org.apache.fineract.accounting.glaccount.domain.GLAccount; +import org.apache.fineract.accounting.glaccount.domain.GLAccountRepositoryWrapper; import org.apache.fineract.accounting.glaccount.domain.GLAccountType; import org.apache.fineract.accounting.glaccount.domain.GLAccountUsage; +import org.apache.fineract.accounting.journalentry.data.CreditDebit; +import org.apache.fineract.accounting.journalentry.data.JournalEntryData; import org.apache.fineract.commands.domain.CommandWrapper; import org.apache.fineract.commands.service.CommandWrapperBuilder; import org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService; @@ -33,10 +40,13 @@ import org.apache.fineract.infrastructure.bulkimport.data.Count; import org.apache.fineract.infrastructure.bulkimport.importhandler.ImportHandler; import org.apache.fineract.infrastructure.bulkimport.importhandler.ImportHandlerUtils; import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.CodeValueDataIdSerializer; +import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.CurrencyDateCodeSerializer; +import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.DateSerializer; import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.EnumOptionDataIdSerializer; import org.apache.fineract.infrastructure.codes.data.CodeValueData; import org.apache.fineract.infrastructure.core.data.CommandProcessingResult; import org.apache.fineract.infrastructure.core.data.EnumOptionData; +import org.apache.fineract.organisation.monetary.data.CurrencyData; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; @@ -53,24 +63,40 @@ public class ChartOfAccountsImportHandler implements ImportHandler { private static final Logger LOG = LoggerFactory.getLogger(ChartOfAccountsImportHandler.class); private List<GLAccountData> glAccounts; private Workbook workbook; + private LocalDate transactionDate; + + // for opening bal + int flagForOpBal = 0; + private List<JournalEntryData> gltransaction; + List<CreditDebit> credits = new ArrayList<>(); + List<CreditDebit> debits = new ArrayList<>(); + String locale; private final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService; + private final GLAccountRepositoryWrapper glAccountRepository; @Autowired - public ChartOfAccountsImportHandler(final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService) { + public ChartOfAccountsImportHandler(final PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService, + GLAccountRepositoryWrapper glAccountRepository) { this.commandsSourceWritePlatformService = commandsSourceWritePlatformService; + this.glAccountRepository = glAccountRepository; } @Override public Count process(Workbook workbook, String locale, String dateFormat) { this.glAccounts = new ArrayList<>(); this.workbook = workbook; + // for opening bal + gltransaction = new ArrayList<>(); + credits = new ArrayList<>(); + debits = new ArrayList<>(); + this.locale = locale; + readExcelFile(); - return importEntity(); + return importEntity(dateFormat); } - public void readExcelFile() { - + private void readExcelFile() { Sheet chartOfAccountsSheet = workbook.getSheet(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME); Integer noOfEntries = ImportHandlerUtils.getNumberOfRows(chartOfAccountsSheet, TemplatePopulateImportConstants.FIRST_COLUMN_INDEX); for (int rowIndex = 1; rowIndex <= noOfEntries; rowIndex++) { @@ -83,6 +109,7 @@ public class ChartOfAccountsImportHandler implements ImportHandler { } private GLAccountData readGlAccounts(Row row) { + String accountType = ImportHandlerUtils.readAsString(ChartOfAcountsConstants.ACCOUNT_TYPE_COL, row); EnumOptionData accountTypeEnum = GLAccountType.fromString(accountType); String accountName = ImportHandlerUtils.readAsString(ChartOfAcountsConstants.ACCOUNT_NAME_COL, row); @@ -109,44 +136,137 @@ public class ChartOfAccountsImportHandler implements ImportHandler { tagIdCodeValueData = new CodeValueData(tagId); } String description = ImportHandlerUtils.readAsString(ChartOfAcountsConstants.DESCRIPTION_COL, row); + if (ImportHandlerUtils.readAsString(ChartOfAcountsConstants.OFFICE_COL, row) != null) { + flagForOpBal = 1; + } else { + flagForOpBal = 0; + } return GLAccountData.importInstance(accountName, parentId, glCode, manualEntriesAllowed, accountTypeEnum, usageEnum, description, tagIdCodeValueData, row.getRowNum()); } - public Count importEntity() { + public Count importEntity(String dateFormat) { Sheet chartOfAccountsSheet = workbook.getSheet(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME); GsonBuilder gsonBuilder = new GsonBuilder(); gsonBuilder.registerTypeAdapter(EnumOptionData.class, new EnumOptionDataIdSerializer()); gsonBuilder.registerTypeAdapter(CodeValueData.class, new CodeValueDataIdSerializer()); + gsonBuilder.registerTypeAdapter(LocalDate.class, new DateSerializer(dateFormat)); + gsonBuilder.registerTypeAdapter(CurrencyData.class, new CurrencyDateCodeSerializer()); int successCount = 0; int errorCount = 0; String errorMessage = ""; - for (GLAccountData glAccount : glAccounts) { - try { - String payload = gsonBuilder.create().toJson(glAccount); - final CommandWrapper commandRequest = new CommandWrapperBuilder() // - .createGLAccount() // - .withJson(payload) // - .build(); // - final CommandProcessingResult result = commandsSourceWritePlatformService.logCommandSource(commandRequest); - successCount++; - Cell statusCell = chartOfAccountsSheet.getRow(glAccount.getRowIndex()).createCell(ChartOfAcountsConstants.STATUS_COL); - statusCell.setCellValue(TemplatePopulateImportConstants.STATUS_CELL_IMPORTED); - statusCell.setCellStyle(ImportHandlerUtils.getCellStyle(workbook, IndexedColors.LIGHT_GREEN)); - } catch (RuntimeException ex) { - errorCount++; - LOG.error("Problem occurred in importEntity function", ex); - errorMessage = ImportHandlerUtils.getErrorMessage(ex); - ImportHandlerUtils.writeErrorMessage(chartOfAccountsSheet, glAccount.getRowIndex(), errorMessage, - ChartOfAcountsConstants.STATUS_COL); + + if (glAccounts != null) { + for (GLAccountData glAccount : glAccounts) { + try { + String payload = gsonBuilder.create().toJson(glAccount); + final CommandWrapper commandRequest = new CommandWrapperBuilder() // + .createGLAccount() // + .withJson(payload) // + .build(); // + final CommandProcessingResult result = commandsSourceWritePlatformService.logCommandSource(commandRequest); + successCount++; + Cell statusCell = chartOfAccountsSheet.getRow(glAccount.getRowIndex()).createCell(ChartOfAcountsConstants.STATUS_COL); + statusCell.setCellValue(TemplatePopulateImportConstants.STATUS_CELL_IMPORTED); + statusCell.setCellStyle(ImportHandlerUtils.getCellStyle(workbook, IndexedColors.LIGHT_GREEN)); + } catch (RuntimeException ex) { + errorCount++; + LOG.error("Problem occurred in importEntity function", ex); + errorMessage = ImportHandlerUtils.getErrorMessage(ex); + ImportHandlerUtils.writeErrorMessage(chartOfAccountsSheet, glAccount.getRowIndex(), errorMessage, + ChartOfAcountsConstants.STATUS_COL); + } + } + if (flagForOpBal > 0) { + + try { + readExcelFileForOpBal(locale, dateFormat); + JournalEntryData transaction = gltransaction.get(gltransaction.size() - 1); + String payload = gsonBuilder.create().toJson(transaction); + + final CommandWrapper commandRequest = new CommandWrapperBuilder().defineOpeningBalanceForJournalEntry() + .withJson(payload).build(); + final CommandProcessingResult result = commandsSourceWritePlatformService.logCommandSource(commandRequest); + successCount++; + Cell statusCell = chartOfAccountsSheet.getRow(1).createCell(ChartOfAcountsConstants.STATUS_COL); + statusCell.setCellValue(TemplatePopulateImportConstants.STATUS_CELL_IMPORTED); + statusCell.setCellStyle(ImportHandlerUtils.getCellStyle(workbook, IndexedColors.LIGHT_GREEN)); + } catch (RuntimeException ex) { + errorCount++; + LOG.error("Problem occurred in importEntity function", ex); + errorMessage = ImportHandlerUtils.getErrorMessage(ex); + ImportHandlerUtils.writeErrorMessage(chartOfAccountsSheet, 1, errorMessage, ChartOfAcountsConstants.STATUS_COL); + } } + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.STATUS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); + ImportHandlerUtils.writeString(ChartOfAcountsConstants.STATUS_COL, + chartOfAccountsSheet.getRow(TemplatePopulateImportConstants.ROWHEADER_INDEX), + TemplatePopulateImportConstants.STATUS_COLUMN_HEADER); + return Count.instance(successCount, errorCount); } + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.STATUS_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); ImportHandlerUtils.writeString(ChartOfAcountsConstants.STATUS_COL, chartOfAccountsSheet.getRow(TemplatePopulateImportConstants.ROWHEADER_INDEX), TemplatePopulateImportConstants.STATUS_COLUMN_HEADER); return Count.instance(successCount, errorCount); + } + // for opening balance + public void readExcelFileForOpBal(final String locale, final String dateFormat) { + + Sheet chartOfAccountsSheet = workbook.getSheet(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME); + Integer noOfEntries = ImportHandlerUtils.getNumberOfRows(chartOfAccountsSheet, TemplatePopulateImportConstants.FIRST_COLUMN_INDEX); + for (int rowIndex = 1; rowIndex <= noOfEntries; rowIndex++) { + Row row; + row = chartOfAccountsSheet.getRow(rowIndex); + + // + JournalEntryData journalEntry = null; + journalEntry = readAddJournalEntries(row, locale, dateFormat); + gltransaction.add(journalEntry); + } + + } + + // for opening balance + private JournalEntryData readAddJournalEntries(Row row, String locale, String dateFormat) { + LocalDate transactionDateCheck = LocalDate.now(ZoneId.systemDefault()); + if (transactionDateCheck != null) { + transactionDate = transactionDateCheck; + } + + String officeName = ImportHandlerUtils.readAsString(ChartOfAcountsConstants.OFFICE_COL, row); + Long officeId = ImportHandlerUtils.readAsLong(ChartOfAcountsConstants.OFFICE_COL_ID, row); + + String currencyCode = ImportHandlerUtils.readAsString(ChartOfAcountsConstants.CURRENCY_CODE, row); + String accountToBeDebitedCredited = ImportHandlerUtils.readAsString(ChartOfAcountsConstants.ACCOUNT_NAME_COL, row); + String glCode = ImportHandlerUtils.readAsString(ChartOfAcountsConstants.GL_CODE_COL, row); + GLAccount glAccount = this.glAccountRepository.findOneByGlCodeWithNotFoundDetection(glCode); + Long glAccountIdToDebitedCredited = glAccount.getId(); + if (glAccountIdToDebitedCredited == null) { + throw new RuntimeException("Account does not exist"); + } + + // String credit = + // readAsString(JournalEntryConstants.GL_ACCOUNT_ID_CREDIT_COL, row); + // String debit = + // readAsString(JournalEntryConstants.GL_ACCOUNT_ID_DEBIT_COL, row); + + if (accountToBeDebitedCredited != null) { + if (ImportHandlerUtils.readAsLong(ChartOfAcountsConstants.CREDIT_AMOUNT, row) != null) { + credits.add(new CreditDebit(glAccountIdToDebitedCredited, + BigDecimal.valueOf(ImportHandlerUtils.readAsLong(ChartOfAcountsConstants.CREDIT_AMOUNT, row)))); + + } else if (ImportHandlerUtils.readAsLong(ChartOfAcountsConstants.DEBIT_AMOUNT, row) != null) { + debits.add(new CreditDebit(glAccountIdToDebitedCredited, + BigDecimal.valueOf(ImportHandlerUtils.readAsLong(ChartOfAcountsConstants.DEBIT_AMOUNT, row)))); + } + } + + return JournalEntryData.importInstance1(officeId, transactionDate, currencyCode, credits, debits, locale, dateFormat); + + } } 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 index 576e589..68a775d 100644 --- 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 @@ -29,6 +29,8 @@ 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.fineract.organisation.monetary.data.CurrencyData; +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; @@ -47,12 +49,16 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { private static final Logger LOG = LoggerFactory.getLogger(ChartOfAccountsWorkbook.class); private final List<GLAccountData> glAccounts; + private final List<OfficeData> offices; // adding opening balance office tag + private List<CurrencyData> currencies; // adding opening balance currency code private Map<String, List<String>> accountTypeToAccountNameAndTag; private Map<Integer, Integer[]> accountTypeToBeginEndIndexesofAccountNames; private List<String> accountTypesNoDuplicatesList; - public ChartOfAccountsWorkbook(List<GLAccountData> glAccounts) { + public ChartOfAccountsWorkbook(List<GLAccountData> glAccounts, List<OfficeData> offices, List<CurrencyData> currencies) { this.glAccounts = glAccounts; + this.offices = offices; // opening balance offices names + this.currencies = currencies; // opening balance currency codes } @Override @@ -63,6 +69,7 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { setLookupTable(chartOfAccountsSheet); setRules(chartOfAccountsSheet); setDefaults(chartOfAccountsSheet); + } private void setAccountTypeToAccountNameAndTag() { @@ -84,6 +91,16 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { } } + private String[] getCurrency() { + String[] currencyCode = new String[currencies.size()]; + int currencyIndex = 0; + for (CurrencyData currencies : currencies) { + currencyCode[currencyIndex] = currencies.code(); + currencyIndex++; + } + return currencyCode; + } + private void setRules(Sheet chartOfAccountsSheet) { CellRangeAddressList accountTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), ChartOfAcountsConstants.ACCOUNT_TYPE_COL, ChartOfAcountsConstants.ACCOUNT_TYPE_COL); @@ -95,9 +112,13 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { ChartOfAcountsConstants.PARENT_COL, ChartOfAcountsConstants.PARENT_COL); CellRangeAddressList tagRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), ChartOfAcountsConstants.TAG_COL, ChartOfAcountsConstants.TAG_COL); + CellRangeAddressList officeNameRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + ChartOfAcountsConstants.OFFICE_COL, ChartOfAcountsConstants.OFFICE_COL); // validation for opening bal office column + CellRangeAddressList currencyCodeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), + ChartOfAcountsConstants.CURRENCY_CODE, ChartOfAcountsConstants.CURRENCY_CODE);// validation for currency code for opening balance DataValidationHelper validationHelper = new HSSFDataValidationHelper((HSSFSheet) chartOfAccountsSheet); - setNames(chartOfAccountsSheet, accountTypesNoDuplicatesList); + setNames(chartOfAccountsSheet, accountTypesNoDuplicatesList, offices); DataValidationConstraint accountTypeConstraint = validationHelper .createExplicitListConstraint(new String[] { GLAccountType.ASSET.toString(), GLAccountType.LIABILITY.toString(), @@ -108,38 +129,48 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { DataValidationConstraint parentConstraint = validationHelper .createFormulaListConstraint("INDIRECT(CONCATENATE(\"AccountName_\",$A1))"); DataValidationConstraint tagConstraint = validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Tags_\",$A1))"); + DataValidationConstraint officeNameConstraint = validationHelper.createFormulaListConstraint("Office"); + DataValidationConstraint currencyCodeConstraint = validationHelper.createExplicitListConstraint(getCurrency()); 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); + DataValidation officeNameValidation = validationHelper.createValidation(officeNameConstraint, officeNameRange); + DataValidation currencyCodeValidation = validationHelper.createValidation(currencyCodeConstraint, currencyCodeRange); chartOfAccountsSheet.addValidationData(accountTypeValidation); chartOfAccountsSheet.addValidationData(accountUsageValidation); chartOfAccountsSheet.addValidationData(manualEntriesValidation); chartOfAccountsSheet.addValidationData(parentValidation); chartOfAccountsSheet.addValidationData(tagValidation); + chartOfAccountsSheet.addValidationData(officeNameValidation); + chartOfAccountsSheet.addValidationData(currencyCodeValidation); } - private void setNames(Sheet chartOfAccountsSheet, List<String> accountTypesNoDuplicatesList) { + private void setNames(Sheet chartOfAccountsSheet, List<String> accountTypesNoDuplicatesList, List<OfficeData> offices) { Workbook chartOfAccountsWorkbook = chartOfAccountsSheet.getWorkbook(); for (Integer i = 0; i < accountTypesNoDuplicatesList.size(); i++) { Name tags = chartOfAccountsWorkbook.createName(); Integer[] tagValueBeginEndIndexes = accountTypeToBeginEndIndexesofAccountNames.get(i); if (accountTypeToBeginEndIndexesofAccountNames != null) { setSanitized(tags, "Tags_" + accountTypesNoDuplicatesList.get(i)); - tags.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME + "!$S$" + tagValueBeginEndIndexes[0] - + ":$S$" + tagValueBeginEndIndexes[1]); + tags.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME + "!$V$" + tagValueBeginEndIndexes[0] + + ":$V$" + tagValueBeginEndIndexes[1]); } Name accountNames = chartOfAccountsWorkbook.createName(); Integer[] accountNamesBeginEndIndexes = accountTypeToBeginEndIndexesofAccountNames.get(i); if (accountNamesBeginEndIndexes != null) { setSanitized(accountNames, "AccountName_" + accountTypesNoDuplicatesList.get(i)); - accountNames.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME + "!$Q$" - + accountNamesBeginEndIndexes[0] + ":$Q$" + accountNamesBeginEndIndexes[1]); + accountNames.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME + "!$T$" + + accountNamesBeginEndIndexes[0] + ":$T$" + accountNamesBeginEndIndexes[1]); } } + Name officeGroup = chartOfAccountsWorkbook.createName(); + officeGroup.setNameName("Office"); + officeGroup.setRefersToFormula(TemplatePopulateImportConstants.CHART_OF_ACCOUNTS_SHEET_NAME + "!$X$2:$X$" + (offices.size() + 1)); + } private void setDefaults(Sheet worksheet) { @@ -150,11 +181,15 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { 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)))"); + "IF(ISERROR(VLOOKUP($E" + (rowNo + 1) + ",$T$2:$U$" + (glAccounts.size() + 1) + ",2,FALSE))," + "\"\",(VLOOKUP($E" + + (rowNo + 1) + ",$T$2:$U$" + (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)))"); + "IF(ISERROR(VLOOKUP($H" + (rowNo + 1) + ",$V$2:$W$" + (glAccounts.size() + 1) + ",2,FALSE))," + "\"\",(VLOOKUP($H" + + (rowNo + 1) + ",$V$2:$W$" + (glAccounts.size() + 1) + ",2,FALSE)))"); + //auto populate office id for bulk import of opening balance + writeFormula(ChartOfAcountsConstants.OFFICE_COL_ID, row, + "IF(ISERROR(VLOOKUP($K" + (rowNo + 1) + ",$X$2:$Y$" + (offices.size() + 1) + ",2,FALSE)),\"\",(VLOOKUP($K" + + (rowNo + 1) + ",$X$2:$Y$" + (offices.size() + 1) + ",2,FALSE)))"); } } catch (Exception e) { LOG.error("Problem occurred in setDefaults function", e); @@ -201,9 +236,29 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { accountTypeIndex++; } } + //opening balance lookup table of offices + startIndex = 1; + rowIndex = 1; + for (OfficeData office : offices) { + startIndex = rowIndex + 1; + if (chartOfAccountsSheet.getRow(rowIndex) != null) { + Row row = chartOfAccountsSheet.getRow(rowIndex); + writeString(ChartOfAcountsConstants.LOOKUP_OFFICE_COL, row, office.name()); + writeLong(ChartOfAcountsConstants.LOOKUP_OFFICE_ID_COL, row, office.getId()); + rowIndex++; + + } else { + Row row = chartOfAccountsSheet.createRow(rowIndex); + writeString(ChartOfAcountsConstants.LOOKUP_OFFICE_COL, row, office.name()); + writeLong(ChartOfAcountsConstants.LOOKUP_OFFICE_ID_COL, row, office.getId()); + rowIndex++; + } + + } } 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); @@ -216,6 +271,11 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { 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.OFFICE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.OFFICE_COL_ID, TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.CURRENCY_CODE, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.DEBIT_AMOUNT, TemplatePopulateImportConstants.SMALL_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.CREDIT_AMOUNT, TemplatePopulateImportConstants.SMALL_COL_SIZE); chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_ACCOUNT_TYPE_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_ACCOUNT_NAME_COL, @@ -223,6 +283,9 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { 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); + //adding lookup for opening balance bulk import + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_OFFICE_COL, TemplatePopulateImportConstants.MEDIUM_COL_SIZE); + chartOfAccountsSheet.setColumnWidth(ChartOfAcountsConstants.LOOKUP_OFFICE_ID_COL, TemplatePopulateImportConstants.SMALL_COL_SIZE); writeString(ChartOfAcountsConstants.ACCOUNT_TYPE_COL, rowHeader, "Account Type*"); writeString(ChartOfAcountsConstants.GL_CODE_COL, rowHeader, "GL Code *"); @@ -234,11 +297,22 @@ public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator { writeString(ChartOfAcountsConstants.TAG_COL, rowHeader, "Tag"); writeString(ChartOfAcountsConstants.TAG_ID_COL, rowHeader, "Tag Id"); writeString(ChartOfAcountsConstants.DESCRIPTION_COL, rowHeader, "Description *"); + //adding data for opening balance bulk import + writeString(ChartOfAcountsConstants.OFFICE_COL, rowHeader, "Parent Office for Opening Balance"); + writeString(ChartOfAcountsConstants.OFFICE_COL_ID, rowHeader, "Parent Office Code Opening Balance"); + writeString(ChartOfAcountsConstants.CURRENCY_CODE, rowHeader, "Currency Code"); + writeString(ChartOfAcountsConstants.DEBIT_AMOUNT, rowHeader, "Debit Amount"); + writeString(ChartOfAcountsConstants.CREDIT_AMOUNT, rowHeader, "Credit Amount"); + 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"); + //adding lookup for opening balance bulk import + writeString(ChartOfAcountsConstants.LOOKUP_OFFICE_COL, rowHeader, "Lookup Office Name"); + writeString(ChartOfAcountsConstants.LOOKUP_OFFICE_ID_COL, rowHeader, "Lookup Office Id"); } + } diff --git a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java index 4d0a59d..0bff745 100644 --- a/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java +++ b/fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java @@ -196,7 +196,7 @@ public class BulkImportWorkbookPopulatorServiceImpl implements BulkImportWorkboo } else if (entityType.trim().equalsIgnoreCase(GlobalEntityType.OFFICES.toString())) { populator = populateOfficeWorkbook(); } else if (entityType.trim().equalsIgnoreCase(GlobalEntityType.CHART_OF_ACCOUNTS.toString())) { - populator = populateChartOfAccountsWorkbook(); + populator = populateChartOfAccountsWorkbook(officeId); } else if (entityType.trim().equalsIgnoreCase(GlobalEntityType.STAFF.toString())) { populator = populateStaffWorkbook(officeId); } else if (entityType.trim().equalsIgnoreCase(GlobalEntityType.SHARE_ACCOUNTS.toString())) { @@ -503,10 +503,12 @@ public class BulkImportWorkbookPopulatorServiceImpl implements BulkImportWorkboo return new OfficeWorkbookPopulator(offices); } - private WorkbookPopulator populateChartOfAccountsWorkbook() { + private WorkbookPopulator populateChartOfAccountsWorkbook(Long officeId) { this.context.authenticatedUser().validateHasReadPermission(TemplatePopulateImportConstants.GL_ACCOUNT_ENTITY_TYPE); List<GLAccountData> glAccounts = fetchGLAccounts(); - return new ChartOfAccountsWorkbook(glAccounts); + List<OfficeData> offices = fetchOffices(null); + return new ChartOfAccountsWorkbook(glAccounts, offices, + (List<CurrencyData>) this.currencyReadPlatformService.retrieveAllowedCurrencies()); } private WorkbookPopulator populateStaffWorkbook(Long officeId) {