You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@fineract.apache.org by av...@apache.org on 2021/10/13 16:12:43 UTC
[fineract] branch develop updated: FINERACT-1400 Bulkimport to
import opening balances for Ledgers (#1890)
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 <92...@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) {