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 <[email protected]>
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) {
