You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@fineract.apache.org by wkk91193 <gi...@git.apache.org> on 2017/08/09 05:13:19 UTC
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
GitHub user wkk91193 opened a pull request:
https://github.com/apache/fineract/pull/403
Extend-mifos-data-import-tool chartOfAccounts populate & import
Feature to integrate data import tool as a core component of Fineract platform
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/wkk91193/incubator-fineract extend-mifos-data-import-tool-branch-chart-of-accounts
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/fineract/pull/403.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #403
----
commit 71da962f6d25973eb2e9a8df47fb17824b9f3459
Author: Kumaranath Fernando <wk...@yahoo.com>
Date: 2017-08-08T04:52:13Z
chartOfAccounts populate & import
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract issue #403: Extend-mifos-data-import-tool chartOfAccounts populate ...
Posted by wkk91193 <gi...@git.apache.org>.
Github user wkk91193 commented on the issue:
https://github.com/apache/fineract/pull/403
Closing PR as I will send in one will all entities
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133645881
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java ---
@@ -0,0 +1,154 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.importhandler.chartofaccounts;
+
+import com.google.gson.GsonBuilder;
+import com.google.gson.JsonElement;
+import com.google.gson.JsonObject;
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.commands.domain.CommandWrapper;
+import org.apache.fineract.commands.service.CommandWrapperBuilder;
+import org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.AbstractImportHandler;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.CodeValueDataIdSerializer;
+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.poi.ss.usermodel.*;
+
+import java.util.ArrayList;
+import java.util.List;
+
+public class ChartOfAccountsImportHandler extends AbstractImportHandler {
+ private final List<GLAccountData> glAccounts;
+ private final Workbook workbook;
+
+ private static final int ACCOUNT_TYPE_COL=0;//A
+ private static final int ACCOUNT_NAME_COL=1;//B
+ private static final int ACCOUNT_USAGE_COL=2;//C
+ private static final int MANUAL_ENTRIES_ALLOWED_COL=3;//D
+ private static final int PARENT_COL=4;//E
+ private static final int PARENT_ID_COL=5;//F
+ private static final int GL_CODE_COL=6;//G
+ private static final int TAG_COL=7;//H
+ private static final int TAG_ID_COL=8;//I
+ private static final int DESCRIPTION_COL=9;//J
+ private static final int LOOKUP_ACCOUNT_TYPE_COL=15;// P
+ private static final int LOOKUP_ACCOUNT_NAME_COL=16; //Q
+ private static final int LOOKUP_ACCOUNT_ID_COL=17;//R
+ private static final int LOOKUP_TAG_COL=18; //S
+ private static final int LOOKUP_TAG_ID_COL=19; //T
+ private static final int STATUS_COL=20;
+
+ public ChartOfAccountsImportHandler(Workbook workbook) {
+ this.glAccounts=new ArrayList<GLAccountData>();
+ this.workbook=workbook;
+ }
+
+ @Override
+ public void readExcelFile() {
+ Sheet chartOfAccountsSheet=workbook.getSheet("ChartOfAccounts");
+ Integer noOfEntries=getNumberOfRows(chartOfAccountsSheet,0);
+ for (int rowIndex=1;rowIndex<noOfEntries;rowIndex++){
+ Row row;
+ try {
+ row=chartOfAccountsSheet.getRow(rowIndex);
+ if (isNotImported(row,STATUS_COL)){
+ glAccounts.add(readGlAccounts(row));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ private GLAccountData readGlAccounts(Row row) {
+ String accountType=readAsString(ACCOUNT_TYPE_COL,row);
+ Long accountTypeId=null;
+ EnumOptionData accountTypeEnum=null;
+ if (accountType!=null && accountType.equalsIgnoreCase("ASSET")){
+ accountTypeId=1L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("LIABILITY")){
+ accountTypeId=2L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("EQUITY")){
+ accountTypeId=3L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }
+ else if(accountType!=null && accountType.equalsIgnoreCase("INCOME")){
+ accountTypeId=4L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("EXPENSE")){
+ accountTypeId=5L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }
+ String accountName=readAsString(ACCOUNT_NAME_COL,row);
+ String usage=readAsString(ACCOUNT_USAGE_COL,row);
+ Long usageId=null;
+ EnumOptionData usageEnum=null;
+ if (usage!=null&& usage.equals("Detail")){
+ usageId=1L;
+ usageEnum=new EnumOptionData(usageId,null,null);
+ }else if (usage!=null&&usage.equals("Header")){
+ usageId=2L;
+ usageEnum=new EnumOptionData(usageId,null,null);
+ }
+ Boolean manualEntriesAllowed=readAsBoolean(MANUAL_ENTRIES_ALLOWED_COL,row);
+ Long parentId=Long.parseLong(readAsString(PARENT_ID_COL,row));
+ String glCode=readAsString(GL_CODE_COL,row);
+ Long tagId=Long.parseLong(readAsString(TAG_ID_COL,row));
+ CodeValueData tagIdCodeValueData=new CodeValueData(tagId);
+ String description=readAsString(DESCRIPTION_COL,row);
+ return new GLAccountData(accountName,parentId,glCode,manualEntriesAllowed,accountTypeEnum,usageEnum,description,tagIdCodeValueData,row.getRowNum());
+ }
+
+ @Override
+ public void Upload(PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService) {
+ Sheet chartOfAccountsSheet=workbook.getSheet("ChartOfAccounts");
+ for (GLAccountData glAccount: glAccounts) {
+ try {
+ GsonBuilder gsonBuilder = new GsonBuilder();
--- End diff --
creation of gsonBuilder and registering type adapter can be done out of loop only once. inside loop just pass toJson(glAccount);
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133630362
--- Diff: fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/api/GLAccountsApiResource.java ---
@@ -219,4 +231,21 @@ private GLAccountData handleTemplate(final GLAccountData glAccountData) {
}
return returnList;
}
+
+ @GET
+ @Path("bulkimporttemplate")
+ @Produces("application/vnd.ms-excel")
+ // @Produces(MediaType.APPLICATION_OCTET_STREAM)
+ public Response getGlAccountsTemplate(@QueryParam("glAccountId") final Long glAccountId) {
+ return bulkImportWorkbookPopulatorService.getTemplate("glaccount",null,null,
+ null,null,null,null,null,null,null,glAccountId);
--- End diff --
create one more method to get template by passing only glaccount and glAccountId
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133639899
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java ---
@@ -0,0 +1,227 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.populator.chartofaccounts;
+
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator;
+import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator {
+ private List<GLAccountData> glAccounts;
+ private Map<String,List<String>> accountTypeToAccountNameAndTag;
+ private Map<Integer,Integer[]>accountTypeToBeginEndIndexesofAccountNames;
+ private List<String> accountTypesNoDuplicateslist;
+
+
+ private static final int ACCOUNT_TYPE_COL=0;//A
+ private static final int ACCOUNT_NAME_COL=1;//B
+ private static final int ACCOUNT_USAGE_COL=2;//C
+ private static final int MANUAL_ENTRIES_ALLOWED_COL=3;//D
+ private static final int PARENT_COL=4;//E
+ private static final int PARENT_ID_COL=5;//F
+ private static final int GL_CODE_COL=6;//G
+ private static final int TAG_COL=7;//H
+ private static final int TAG_ID_COL=8;//I
+ private static final int DESCRIPTION_COL=9;//J
+ private static final int LOOKUP_ACCOUNT_TYPE_COL=15;// P
+ private static final int LOOKUP_ACCOUNT_NAME_COL=16; //Q
+ private static final int LOOKUP_ACCOUNT_ID_COL=17;//R
+ private static final int LOOKUP_TAG_COL=18; //S
+ private static final int LOOKUP_TAG_ID_COL=19; //T
+
+
+ public ChartOfAccountsWorkbook(List<GLAccountData> glAccounts) {
+ this.glAccounts = glAccounts;
+ }
+
+ @Override
+ public void populate(Workbook workbook) {
+ Sheet chartOfAccountsSheet=workbook.createSheet("ChartOfAccounts");
+ setLayout(chartOfAccountsSheet);
+ setAccountTypeToAccountNameAndTag();
+ setLookupTable(chartOfAccountsSheet);
+ setRules(chartOfAccountsSheet);
+ setDefaults(chartOfAccountsSheet);
+ }
+
+ private void setAccountTypeToAccountNameAndTag() {
+ accountTypeToAccountNameAndTag=new HashMap<>();
+ for (GLAccountData glAccount: glAccounts) {
+ addToaccountTypeToAccountNameMap(glAccount.getType().getValue(),glAccount.getName()+"-"+glAccount.getId()+"-"+glAccount.getTagId().getName()+"-"+glAccount.getTagId().getId());
+ }
+ }
+
+ private void addToaccountTypeToAccountNameMap(String key, String value) {
+ List<String> values=accountTypeToAccountNameAndTag.get(key);
+ if (values==null){
+ values=new ArrayList<String>();
+ }
+ if (!values.contains(value)){
+ values.add(value);
+ accountTypeToAccountNameAndTag.put(key,values);
+ }
+ }
+
+ private void setRules(Sheet chartOfAccountsSheet) {
+ CellRangeAddressList accountTypeRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), ACCOUNT_TYPE_COL,ACCOUNT_TYPE_COL);
+ CellRangeAddressList accountUsageRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), ACCOUNT_USAGE_COL,ACCOUNT_USAGE_COL);
+ CellRangeAddressList manualEntriesAllowedRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), MANUAL_ENTRIES_ALLOWED_COL,MANUAL_ENTRIES_ALLOWED_COL);
+ CellRangeAddressList parentRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), PARENT_COL,PARENT_COL);
+ CellRangeAddressList tagRange = new CellRangeAddressList(1, SpreadsheetVersion.EXCEL97.getLastRowIndex(), TAG_COL,TAG_COL);
+
+ DataValidationHelper validationHelper=new HSSFDataValidationHelper((HSSFSheet) chartOfAccountsSheet);
+ setNames(chartOfAccountsSheet,accountTypesNoDuplicateslist);
+
+ DataValidationConstraint accountTypeConstraint=validationHelper.createExplicitListConstraint(new String[]{"ASSET","LIABILITY","EQUITY,INCOME,EXPENSE"});
+ DataValidationConstraint accountUsageConstraint=validationHelper.createExplicitListConstraint(new String[]{"Detail,Header"});
+ DataValidationConstraint booleanConstraint=validationHelper.createExplicitListConstraint(new String[]{"True","False"});
+ //"VLOOKUP($A1,$A$2:$B"+ SpreadsheetVersion.EXCEL97.getLastRowIndex()+",2,TRUE)"
+ DataValidationConstraint parentConstraint=validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"AccountName_\",$A1))");
+ DataValidationConstraint tagConstraint=validationHelper.createFormulaListConstraint("INDIRECT(CONCATENATE(\"Tags_\",$A1))");
+
+ DataValidation accountTypeValidation=validationHelper.createValidation(accountTypeConstraint,accountTypeRange);
+ DataValidation accountUsageValidation=validationHelper.createValidation(accountUsageConstraint,accountUsageRange);
+ DataValidation manualEntriesValidation=validationHelper.createValidation(booleanConstraint,manualEntriesAllowedRange);
+ DataValidation parentValidation=validationHelper.createValidation(parentConstraint,parentRange);
+ DataValidation tagValidation=validationHelper.createValidation(tagConstraint,tagRange);
+
+ chartOfAccountsSheet.addValidationData(accountTypeValidation);
+ chartOfAccountsSheet.addValidationData(accountUsageValidation);
+ chartOfAccountsSheet.addValidationData(manualEntriesValidation);
+ chartOfAccountsSheet.addValidationData(parentValidation);
+ chartOfAccountsSheet.addValidationData(tagValidation);
+ }
+
+ private void setNames(Sheet chartOfAccountsSheet,List<String> accountTypesNoDuplicateslist) {
+ Workbook chartOfAccountsWorkbook=chartOfAccountsSheet.getWorkbook();
+ for (Integer i=0;i<accountTypesNoDuplicateslist.size();i++){
+ Name tags=chartOfAccountsWorkbook.createName();
+ Integer [] tagValueBeginEndIndexes=accountTypeToBeginEndIndexesofAccountNames.get(i);
+ if(accountTypeToBeginEndIndexesofAccountNames!=null){
+ tags.setNameName("Tags_"+accountTypesNoDuplicateslist.get(i));
+ tags.setRefersToFormula("ChartOfAccounts!$S$"+tagValueBeginEndIndexes[0]+":$S$"+tagValueBeginEndIndexes[1]);
+ }
+ Name accountNames=chartOfAccountsWorkbook.createName();
+ Integer [] accountNamesBeginEndIndexes=accountTypeToBeginEndIndexesofAccountNames.get(i);
+ if (accountNamesBeginEndIndexes!=null){
+ accountNames.setNameName("AccountName_"+accountTypesNoDuplicateslist.get(i));
+ accountNames.setRefersToFormula("ChartOfAccounts!$Q$"+accountNamesBeginEndIndexes[0]+":$Q$"+accountNamesBeginEndIndexes[1]);
+ }
+ }
+ }
+ private void setDefaults(Sheet worksheet){
+ try {
+ for (Integer rowNo = 1; rowNo < 3000; rowNo++) {
+ Row row = worksheet.getRow(rowNo);
+ if (row == null)
+ row = worksheet.createRow(rowNo);
+ writeFormula(PARENT_ID_COL, row,
+ "IF(ISERROR(VLOOKUP($E"+(rowNo+1)+",$Q$2:$R$"+(glAccounts.size()+1)+",2,FALSE)),\"\",(VLOOKUP($E"+(rowNo+1)+",$Q$2:$R$"+(glAccounts.size()+1)+",2,FALSE)))");
+ writeFormula(TAG_ID_COL,row,"IF(ISERROR(VLOOKUP($H"+(rowNo+1)+",$S$2:$T$"+(glAccounts.size()+1)+",2,FALSE)),\"\",(VLOOKUP($H"+(rowNo+1)+",$S$2:$T$"+(glAccounts.size()+1)+",2,FALSE)))");
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ private void setLookupTable(Sheet chartOfAccountsSheet) {
+ accountTypesNoDuplicateslist=new ArrayList<>();
+ for (int i = 0; i <glAccounts.size() ; i++) {
+ if (!accountTypesNoDuplicateslist.contains(glAccounts.get(i).getType().getValue())) {
+ accountTypesNoDuplicateslist.add(glAccounts.get(i).getType().getValue());
+ }
+ }
+ int rowIndex=1,startIndex=1,accountTypeIndex=0;
+ accountTypeToBeginEndIndexesofAccountNames= new HashMap<Integer,Integer[]>();
+ for (String accountType:accountTypesNoDuplicateslist) {
+ startIndex=rowIndex+1;
+ Row row =chartOfAccountsSheet.createRow(rowIndex);
+ writeString(LOOKUP_ACCOUNT_TYPE_COL,row,accountType);
+ List<String> accountNamesandTags =accountTypeToAccountNameAndTag.get(accountType);
+ if (!accountNamesandTags.isEmpty()){
+ for (String accountNameandTag:accountNamesandTags) {
+ if (chartOfAccountsSheet.getRow(rowIndex)!=null){
--- End diff --
remove code duplication, before writeString if row is null then create row
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133633554
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java ---
@@ -0,0 +1,154 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.importhandler.chartofaccounts;
+
+import com.google.gson.GsonBuilder;
+import com.google.gson.JsonElement;
+import com.google.gson.JsonObject;
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.commands.domain.CommandWrapper;
+import org.apache.fineract.commands.service.CommandWrapperBuilder;
+import org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.AbstractImportHandler;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.CodeValueDataIdSerializer;
+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.poi.ss.usermodel.*;
+
+import java.util.ArrayList;
+import java.util.List;
+
+public class ChartOfAccountsImportHandler extends AbstractImportHandler {
+ private final List<GLAccountData> glAccounts;
+ private final Workbook workbook;
+
+ private static final int ACCOUNT_TYPE_COL=0;//A
+ private static final int ACCOUNT_NAME_COL=1;//B
+ private static final int ACCOUNT_USAGE_COL=2;//C
+ private static final int MANUAL_ENTRIES_ALLOWED_COL=3;//D
+ private static final int PARENT_COL=4;//E
+ private static final int PARENT_ID_COL=5;//F
+ private static final int GL_CODE_COL=6;//G
+ private static final int TAG_COL=7;//H
+ private static final int TAG_ID_COL=8;//I
+ private static final int DESCRIPTION_COL=9;//J
+ private static final int LOOKUP_ACCOUNT_TYPE_COL=15;// P
+ private static final int LOOKUP_ACCOUNT_NAME_COL=16; //Q
+ private static final int LOOKUP_ACCOUNT_ID_COL=17;//R
+ private static final int LOOKUP_TAG_COL=18; //S
+ private static final int LOOKUP_TAG_ID_COL=19; //T
+ private static final int STATUS_COL=20;
+
+ public ChartOfAccountsImportHandler(Workbook workbook) {
+ this.glAccounts=new ArrayList<GLAccountData>();
+ this.workbook=workbook;
+ }
+
+ @Override
+ public void readExcelFile() {
+ Sheet chartOfAccountsSheet=workbook.getSheet("ChartOfAccounts");
+ Integer noOfEntries=getNumberOfRows(chartOfAccountsSheet,0);
+ for (int rowIndex=1;rowIndex<noOfEntries;rowIndex++){
+ Row row;
+ try {
+ row=chartOfAccountsSheet.getRow(rowIndex);
+ if (isNotImported(row,STATUS_COL)){
+ glAccounts.add(readGlAccounts(row));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ private GLAccountData readGlAccounts(Row row) {
+ String accountType=readAsString(ACCOUNT_TYPE_COL,row);
+ Long accountTypeId=null;
+ EnumOptionData accountTypeEnum=null;
+ if (accountType!=null && accountType.equalsIgnoreCase("ASSET")){
+ accountTypeId=1L;
--- End diff --
these value take from GLAccountType , don't hard code it.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133632867
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/AbstractImportHandler.java ---
@@ -0,0 +1,152 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.importhandler;
+
+import com.google.gson.JsonArray;
+import com.google.gson.JsonElement;
+import com.google.gson.JsonObject;
+import com.google.gson.JsonParser;
+import org.apache.poi.ss.usermodel.*;
+import org.joda.time.LocalDate;
+
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
+import java.util.Date;
+import java.util.Iterator;
+
+public abstract class AbstractImportHandler implements ImportHandler {
+
+ protected Integer getNumberOfRows(Sheet sheet, int primaryColumn) {
+ Integer noOfEntries = 1;
+ // getLastRowNum and getPhysicalNumberOfRows showing false values
+ // sometimes
+ while (sheet.getRow(noOfEntries) !=null && sheet.getRow(noOfEntries).getCell(primaryColumn) != null) {
+ noOfEntries++;
+ }
+
+ return noOfEntries;
+ }
+
+ protected boolean isNotImported(Row row, int statusColumn) {
+ return !readAsString(statusColumn, row).equals("Imported");
+ }
+
+ protected Long readAsLong(int colIndex, Row row) {
+ try {
+ Cell c = row.getCell(colIndex);
+ if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK)
+ return null;
+ FormulaEvaluator eval = row.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
+ if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
+ CellValue val = null;
+ try {
+ val = eval.evaluate(c);
+ } catch (NullPointerException npe) {
+ return null;
+ }
+ return ((Double) val.getNumberValue()).longValue();
+ }
+ return ((Double) c.getNumericCellValue()).longValue();
+ } catch (RuntimeException re) {
+ return Long.parseLong(row.getCell(colIndex).getStringCellValue());
--- End diff --
if cell value is not numeric then in return it will throw exception. Handle the same scenario in other places also.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract issue #403: Extend-mifos-data-import-tool chartOfAccounts populate ...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on the issue:
https://github.com/apache/fineract/pull/403
create a utility class for file related functionality which should contain
1)getNumberOfRows
2)all read as methods readAsInt,readAsLong,readAsDouble,readAsString,readAsDate etc.
3)trimEmptyDecimalPortion,
4)writeString
5) read excel file which should return list of rows;
And use this utility class for import tool
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133634260
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java ---
@@ -0,0 +1,154 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.importhandler.chartofaccounts;
+
+import com.google.gson.GsonBuilder;
+import com.google.gson.JsonElement;
+import com.google.gson.JsonObject;
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.commands.domain.CommandWrapper;
+import org.apache.fineract.commands.service.CommandWrapperBuilder;
+import org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.AbstractImportHandler;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.CodeValueDataIdSerializer;
+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.poi.ss.usermodel.*;
+
+import java.util.ArrayList;
+import java.util.List;
+
+public class ChartOfAccountsImportHandler extends AbstractImportHandler {
+ private final List<GLAccountData> glAccounts;
+ private final Workbook workbook;
+
+ private static final int ACCOUNT_TYPE_COL=0;//A
+ private static final int ACCOUNT_NAME_COL=1;//B
+ private static final int ACCOUNT_USAGE_COL=2;//C
+ private static final int MANUAL_ENTRIES_ALLOWED_COL=3;//D
+ private static final int PARENT_COL=4;//E
+ private static final int PARENT_ID_COL=5;//F
+ private static final int GL_CODE_COL=6;//G
+ private static final int TAG_COL=7;//H
+ private static final int TAG_ID_COL=8;//I
+ private static final int DESCRIPTION_COL=9;//J
+ private static final int LOOKUP_ACCOUNT_TYPE_COL=15;// P
+ private static final int LOOKUP_ACCOUNT_NAME_COL=16; //Q
+ private static final int LOOKUP_ACCOUNT_ID_COL=17;//R
+ private static final int LOOKUP_TAG_COL=18; //S
+ private static final int LOOKUP_TAG_ID_COL=19; //T
+ private static final int STATUS_COL=20;
+
+ public ChartOfAccountsImportHandler(Workbook workbook) {
+ this.glAccounts=new ArrayList<GLAccountData>();
+ this.workbook=workbook;
+ }
+
+ @Override
+ public void readExcelFile() {
+ Sheet chartOfAccountsSheet=workbook.getSheet("ChartOfAccounts");
+ Integer noOfEntries=getNumberOfRows(chartOfAccountsSheet,0);
+ for (int rowIndex=1;rowIndex<noOfEntries;rowIndex++){
+ Row row;
+ try {
+ row=chartOfAccountsSheet.getRow(rowIndex);
+ if (isNotImported(row,STATUS_COL)){
+ glAccounts.add(readGlAccounts(row));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ private GLAccountData readGlAccounts(Row row) {
+ String accountType=readAsString(ACCOUNT_TYPE_COL,row);
+ Long accountTypeId=null;
+ EnumOptionData accountTypeEnum=null;
+ if (accountType!=null && accountType.equalsIgnoreCase("ASSET")){
+ accountTypeId=1L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("LIABILITY")){
+ accountTypeId=2L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("EQUITY")){
+ accountTypeId=3L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }
+ else if(accountType!=null && accountType.equalsIgnoreCase("INCOME")){
+ accountTypeId=4L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("EXPENSE")){
+ accountTypeId=5L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }
+ String accountName=readAsString(ACCOUNT_NAME_COL,row);
+ String usage=readAsString(ACCOUNT_USAGE_COL,row);
+ Long usageId=null;
+ EnumOptionData usageEnum=null;
+ if (usage!=null&& usage.equals("Detail")){
+ usageId=1L;
+ usageEnum=new EnumOptionData(usageId,null,null);
+ }else if (usage!=null&&usage.equals("Header")){
+ usageId=2L;
+ usageEnum=new EnumOptionData(usageId,null,null);
+ }
+ Boolean manualEntriesAllowed=readAsBoolean(MANUAL_ENTRIES_ALLOWED_COL,row);
+ Long parentId=Long.parseLong(readAsString(PARENT_ID_COL,row));
+ String glCode=readAsString(GL_CODE_COL,row);
+ Long tagId=Long.parseLong(readAsString(TAG_ID_COL,row));
+ CodeValueData tagIdCodeValueData=new CodeValueData(tagId);
+ String description=readAsString(DESCRIPTION_COL,row);
+ return new GLAccountData(accountName,parentId,glCode,manualEntriesAllowed,accountTypeEnum,usageEnum,description,tagIdCodeValueData,row.getRowNum());
+ }
+
+ @Override
+ public void Upload(PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService) {
+ Sheet chartOfAccountsSheet=workbook.getSheet("ChartOfAccounts");
--- End diff --
don't hard code .
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133638020
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/populator/chartofaccounts/ChartOfAccountsWorkbook.java ---
@@ -0,0 +1,227 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.populator.chartofaccounts;
+
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.infrastructure.bulkimport.populator.AbstractWorkbookPopulator;
+import org.apache.poi.hssf.usermodel.HSSFDataValidationHelper;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.List;
+import java.util.Map;
+
+public class ChartOfAccountsWorkbook extends AbstractWorkbookPopulator {
+ private List<GLAccountData> glAccounts;
+ private Map<String,List<String>> accountTypeToAccountNameAndTag;
+ private Map<Integer,Integer[]>accountTypeToBeginEndIndexesofAccountNames;
+ private List<String> accountTypesNoDuplicateslist;
--- End diff --
accountTypesNoDuplicateslist follow naming convention
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133633136
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/ImportHandler.java ---
@@ -0,0 +1,26 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.importhandler;
+
+import org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService;
+
+public interface ImportHandler {
+ public void readExcelFile();
+ public void Upload(PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService);
--- End diff --
follow the naming conventions.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133631616
--- Diff: fineract-provider/src/main/java/org/apache/fineract/accounting/glaccount/service/GLAccountReadPlatformServiceImpl.java ---
@@ -123,11 +123,13 @@ public GLAccountData mapRow(final ResultSet rs, @SuppressWarnings("unused") fina
final GLAccountMapper rm = new GLAccountMapper(associationParametersData);
String sql = "select " + rm.schema();
// append SQL statement for fetching account totals
- if (associationParametersData.isRunningBalanceRequired()) {
- sql = sql + " and gl_j.id in (select t1.id from (select t2.account_id, max(t2.id) as id from "
- + "(select id, max(entry_date) as entry_date, account_id from acc_gl_journal_entry where is_running_balance_calculated = 1 "
- + "group by account_id desc) t3 inner join acc_gl_journal_entry t2 on t2.account_id = t3.account_id and t2.entry_date = t3.entry_date "
- + "group by t2.account_id desc) t1)";
+ if(associationParametersData!=null){
+ if (associationParametersData.isRunningBalanceRequired()) {
--- End diff --
use both condition in one if statement
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133640512
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/service/BulkImportWorkbookPopulatorServiceImpl.java ---
@@ -0,0 +1,106 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.service;
+
+import org.apache.fineract.accounting.glaccount.api.GLAccountsApiConstants;
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.accounting.glaccount.service.GLAccountReadPlatformService;
+import org.apache.fineract.infrastructure.bulkimport.populator.*;
+import org.apache.fineract.infrastructure.bulkimport.populator.chartofaccounts.ChartOfAccountsWorkbook;
+import org.apache.fineract.infrastructure.core.exception.GeneralPlatformDomainRuleException;
+import org.apache.fineract.infrastructure.core.service.DateUtils;
+import org.apache.fineract.infrastructure.security.service.PlatformSecurityContext;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import javax.ws.rs.core.Response;
+import javax.ws.rs.core.Response.ResponseBuilder;
+import java.io.ByteArrayOutputStream;
+import java.io.IOException;
+import java.util.ArrayList;
+import java.util.List;
+
+@Service
+public class BulkImportWorkbookPopulatorServiceImpl implements BulkImportWorkbookPopulatorService {
+
+ private final PlatformSecurityContext context;
+ private final GLAccountReadPlatformService glAccountReadPlatformService;
+
+
+ @Autowired
+ public BulkImportWorkbookPopulatorServiceImpl(final PlatformSecurityContext context,
+ final GLAccountReadPlatformService glAccountReadPlatformService) {
+ this.context = context;
+ this.glAccountReadPlatformService=glAccountReadPlatformService;
+
+ }
+
+ @Override
+ public Response getTemplate(final String entityType, final Long officeId, final Long staffId,final Long centerId,
+ final Long clientId,final Long groupId, final Long productId,final Long fundId,
+ final Long paymentTypeId,final String code,final Long glAccountId) {
+
+ WorkbookPopulator populator = null;
+ final Workbook workbook = new HSSFWorkbook();
+ if (entityType.trim().equalsIgnoreCase(GLAccountsApiConstants.GLACCOUNTS_RESOURCE_NAME)) {
+ populator=populateChartOfAccountsWorkbook(glAccountId);
+ }else
+ throw new GeneralPlatformDomainRuleException("error.msg.unable.to.find.resource",
+ "Unable to find requested resource");
+ populator.populate(workbook);
--- End diff --
after throwing exception, what is use of populator.populate(workbook); ?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133632343
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/AbstractImportHandler.java ---
@@ -0,0 +1,152 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.importhandler;
+
+import com.google.gson.JsonArray;
+import com.google.gson.JsonElement;
+import com.google.gson.JsonObject;
+import com.google.gson.JsonParser;
+import org.apache.poi.ss.usermodel.*;
+import org.joda.time.LocalDate;
+
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
+import java.util.Date;
+import java.util.Iterator;
+
+public abstract class AbstractImportHandler implements ImportHandler {
+
+ protected Integer getNumberOfRows(Sheet sheet, int primaryColumn) {
+ Integer noOfEntries = 1;
+ // getLastRowNum and getPhysicalNumberOfRows showing false values
+ // sometimes
+ while (sheet.getRow(noOfEntries) !=null && sheet.getRow(noOfEntries).getCell(primaryColumn) != null) {
+ noOfEntries++;
+ }
+
+ return noOfEntries;
+ }
+
+ protected boolean isNotImported(Row row, int statusColumn) {
+ return !readAsString(statusColumn, row).equals("Imported");
+ }
+
+ protected Long readAsLong(int colIndex, Row row) {
+ try {
+ Cell c = row.getCell(colIndex);
+ if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK)
+ return null;
+ FormulaEvaluator eval = row.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
+ if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
+ CellValue val = null;
+ try {
+ val = eval.evaluate(c);
+ } catch (NullPointerException npe) {
--- End diff --
don't catch null pointer pointer exception, check for null value .
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by wkk91193 <gi...@git.apache.org>.
Github user wkk91193 closed the pull request at:
https://github.com/apache/fineract/pull/403
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133633856
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java ---
@@ -0,0 +1,154 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.importhandler.chartofaccounts;
+
+import com.google.gson.GsonBuilder;
+import com.google.gson.JsonElement;
+import com.google.gson.JsonObject;
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.commands.domain.CommandWrapper;
+import org.apache.fineract.commands.service.CommandWrapperBuilder;
+import org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.AbstractImportHandler;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.CodeValueDataIdSerializer;
+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.poi.ss.usermodel.*;
+
+import java.util.ArrayList;
+import java.util.List;
+
+public class ChartOfAccountsImportHandler extends AbstractImportHandler {
+ private final List<GLAccountData> glAccounts;
+ private final Workbook workbook;
+
+ private static final int ACCOUNT_TYPE_COL=0;//A
+ private static final int ACCOUNT_NAME_COL=1;//B
+ private static final int ACCOUNT_USAGE_COL=2;//C
+ private static final int MANUAL_ENTRIES_ALLOWED_COL=3;//D
+ private static final int PARENT_COL=4;//E
+ private static final int PARENT_ID_COL=5;//F
+ private static final int GL_CODE_COL=6;//G
+ private static final int TAG_COL=7;//H
+ private static final int TAG_ID_COL=8;//I
+ private static final int DESCRIPTION_COL=9;//J
+ private static final int LOOKUP_ACCOUNT_TYPE_COL=15;// P
+ private static final int LOOKUP_ACCOUNT_NAME_COL=16; //Q
+ private static final int LOOKUP_ACCOUNT_ID_COL=17;//R
+ private static final int LOOKUP_TAG_COL=18; //S
+ private static final int LOOKUP_TAG_ID_COL=19; //T
+ private static final int STATUS_COL=20;
+
+ public ChartOfAccountsImportHandler(Workbook workbook) {
+ this.glAccounts=new ArrayList<GLAccountData>();
+ this.workbook=workbook;
+ }
+
+ @Override
+ public void readExcelFile() {
+ Sheet chartOfAccountsSheet=workbook.getSheet("ChartOfAccounts");
+ Integer noOfEntries=getNumberOfRows(chartOfAccountsSheet,0);
+ for (int rowIndex=1;rowIndex<noOfEntries;rowIndex++){
+ Row row;
+ try {
+ row=chartOfAccountsSheet.getRow(rowIndex);
+ if (isNotImported(row,STATUS_COL)){
+ glAccounts.add(readGlAccounts(row));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ private GLAccountData readGlAccounts(Row row) {
+ String accountType=readAsString(ACCOUNT_TYPE_COL,row);
+ Long accountTypeId=null;
+ EnumOptionData accountTypeEnum=null;
+ if (accountType!=null && accountType.equalsIgnoreCase("ASSET")){
+ accountTypeId=1L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("LIABILITY")){
+ accountTypeId=2L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("EQUITY")){
+ accountTypeId=3L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }
+ else if(accountType!=null && accountType.equalsIgnoreCase("INCOME")){
+ accountTypeId=4L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("EXPENSE")){
+ accountTypeId=5L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }
+ String accountName=readAsString(ACCOUNT_NAME_COL,row);
+ String usage=readAsString(ACCOUNT_USAGE_COL,row);
+ Long usageId=null;
+ EnumOptionData usageEnum=null;
+ if (usage!=null&& usage.equals("Detail")){
+ usageId=1L;
--- End diff --
don't hard code , take it from GLAccountUsage
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133634512
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/bulkimport/importhandler/chartofaccounts/ChartOfAccountsImportHandler.java ---
@@ -0,0 +1,154 @@
+/**
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.apache.fineract.infrastructure.bulkimport.importhandler.chartofaccounts;
+
+import com.google.gson.GsonBuilder;
+import com.google.gson.JsonElement;
+import com.google.gson.JsonObject;
+import org.apache.fineract.accounting.glaccount.data.GLAccountData;
+import org.apache.fineract.commands.domain.CommandWrapper;
+import org.apache.fineract.commands.service.CommandWrapperBuilder;
+import org.apache.fineract.commands.service.PortfolioCommandSourceWritePlatformService;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.AbstractImportHandler;
+import org.apache.fineract.infrastructure.bulkimport.importhandler.helper.CodeValueDataIdSerializer;
+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.poi.ss.usermodel.*;
+
+import java.util.ArrayList;
+import java.util.List;
+
+public class ChartOfAccountsImportHandler extends AbstractImportHandler {
+ private final List<GLAccountData> glAccounts;
+ private final Workbook workbook;
+
+ private static final int ACCOUNT_TYPE_COL=0;//A
+ private static final int ACCOUNT_NAME_COL=1;//B
+ private static final int ACCOUNT_USAGE_COL=2;//C
+ private static final int MANUAL_ENTRIES_ALLOWED_COL=3;//D
+ private static final int PARENT_COL=4;//E
+ private static final int PARENT_ID_COL=5;//F
+ private static final int GL_CODE_COL=6;//G
+ private static final int TAG_COL=7;//H
+ private static final int TAG_ID_COL=8;//I
+ private static final int DESCRIPTION_COL=9;//J
+ private static final int LOOKUP_ACCOUNT_TYPE_COL=15;// P
+ private static final int LOOKUP_ACCOUNT_NAME_COL=16; //Q
+ private static final int LOOKUP_ACCOUNT_ID_COL=17;//R
+ private static final int LOOKUP_TAG_COL=18; //S
+ private static final int LOOKUP_TAG_ID_COL=19; //T
+ private static final int STATUS_COL=20;
+
+ public ChartOfAccountsImportHandler(Workbook workbook) {
+ this.glAccounts=new ArrayList<GLAccountData>();
+ this.workbook=workbook;
+ }
+
+ @Override
+ public void readExcelFile() {
+ Sheet chartOfAccountsSheet=workbook.getSheet("ChartOfAccounts");
+ Integer noOfEntries=getNumberOfRows(chartOfAccountsSheet,0);
+ for (int rowIndex=1;rowIndex<noOfEntries;rowIndex++){
+ Row row;
+ try {
+ row=chartOfAccountsSheet.getRow(rowIndex);
+ if (isNotImported(row,STATUS_COL)){
+ glAccounts.add(readGlAccounts(row));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ private GLAccountData readGlAccounts(Row row) {
+ String accountType=readAsString(ACCOUNT_TYPE_COL,row);
+ Long accountTypeId=null;
+ EnumOptionData accountTypeEnum=null;
+ if (accountType!=null && accountType.equalsIgnoreCase("ASSET")){
+ accountTypeId=1L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("LIABILITY")){
+ accountTypeId=2L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("EQUITY")){
+ accountTypeId=3L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }
+ else if(accountType!=null && accountType.equalsIgnoreCase("INCOME")){
+ accountTypeId=4L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }else if(accountType!=null && accountType.equalsIgnoreCase("EXPENSE")){
+ accountTypeId=5L;
+ accountTypeEnum=new EnumOptionData(accountTypeId,null,null);
+ }
+ String accountName=readAsString(ACCOUNT_NAME_COL,row);
+ String usage=readAsString(ACCOUNT_USAGE_COL,row);
+ Long usageId=null;
+ EnumOptionData usageEnum=null;
+ if (usage!=null&& usage.equals("Detail")){
+ usageId=1L;
+ usageEnum=new EnumOptionData(usageId,null,null);
+ }else if (usage!=null&&usage.equals("Header")){
+ usageId=2L;
+ usageEnum=new EnumOptionData(usageId,null,null);
+ }
+ Boolean manualEntriesAllowed=readAsBoolean(MANUAL_ENTRIES_ALLOWED_COL,row);
+ Long parentId=Long.parseLong(readAsString(PARENT_ID_COL,row));
+ String glCode=readAsString(GL_CODE_COL,row);
+ Long tagId=Long.parseLong(readAsString(TAG_ID_COL,row));
+ CodeValueData tagIdCodeValueData=new CodeValueData(tagId);
+ String description=readAsString(DESCRIPTION_COL,row);
+ return new GLAccountData(accountName,parentId,glCode,manualEntriesAllowed,accountTypeEnum,usageEnum,description,tagIdCodeValueData,row.getRowNum());
+ }
+
+ @Override
+ public void Upload(PortfolioCommandSourceWritePlatformService commandsSourceWritePlatformService) {
+ Sheet chartOfAccountsSheet=workbook.getSheet("ChartOfAccounts");
+ for (GLAccountData glAccount: glAccounts) {
+ try {
+ GsonBuilder gsonBuilder = new GsonBuilder();
+ gsonBuilder.registerTypeAdapter(EnumOptionData.class, new EnumOptionDataIdSerializer());
+ gsonBuilder.registerTypeAdapter(CodeValueData.class, new CodeValueDataIdSerializer());
+ String payload=gsonBuilder.create().toJson(glAccount);
+ final CommandWrapper commandRequest = new CommandWrapperBuilder() //
+ .createGLAccount() //
+ .withJson(payload) //
+ .build(); //
+ final CommandProcessingResult result = commandsSourceWritePlatformService.logCommandSource(commandRequest);
+ Cell statusCell = chartOfAccountsSheet.getRow(glAccount.getRowIndex()).createCell(STATUS_COL);
+ statusCell.setCellValue("Imported");
--- End diff --
don't hard code. change other places also.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] fineract pull request #403: Extend-mifos-data-import-tool chartOfAccounts po...
Posted by nazeer1100126 <gi...@git.apache.org>.
Github user nazeer1100126 commented on a diff in the pull request:
https://github.com/apache/fineract/pull/403#discussion_r133640875
--- Diff: fineract-provider/src/main/java/org/apache/fineract/infrastructure/codes/data/CodeValueData.java ---
@@ -37,6 +37,15 @@
private final boolean active;
private final boolean mandatory;
+ public CodeValueData( final Long id){
--- End diff --
don't create new , use existing method and don't use public constructor
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---