Merge branch 'develop' of https://github.com/mifosio/reporting into develop
diff --git a/api/src/test/java/io/mifos/reporting/api/v1/domain/Sample.java b/api/src/test/java/io/mifos/reporting/api/v1/domain/Sample.java
index ba800f4..6f63318 100644
--- a/api/src/test/java/io/mifos/reporting/api/v1/domain/Sample.java
+++ b/api/src/test/java/io/mifos/reporting/api/v1/domain/Sample.java
@@ -1,3 +1,18 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting.api.v1.domain;
public class Sample {
diff --git a/component-test/src/main/java/io/mifos/reporting/TestBalanceSheetReportSpecification.java b/component-test/src/main/java/io/mifos/reporting/TestBalanceSheetReportSpecification.java
new file mode 100644
index 0000000..3e81b69
--- /dev/null
+++ b/component-test/src/main/java/io/mifos/reporting/TestBalanceSheetReportSpecification.java
@@ -0,0 +1,37 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting;
+
+import io.mifos.reporting.api.v1.domain.ReportDefinition;
+import org.junit.Assert;
+import org.junit.Test;
+
+import java.util.List;
+
+public class TestBalanceSheetReportSpecification extends AbstractReportingSpecificationTest {
+
+ public TestBalanceSheetReportSpecification() {
+ super();
+ }
+
+ @Test
+ public void shouldReturnReportDefinition() {
+ final List<ReportDefinition> reportDefinitions = super.testSubject.fetchReportDefinitions("Accounting");
+ Assert.assertTrue(
+ reportDefinitions.stream().anyMatch(reportDefinition -> reportDefinition.getIdentifier().equals("Balancesheet"))
+ );
+ }
+}
diff --git a/component-test/src/main/java/io/mifos/reporting/TestDepositListReportSpecification.java b/component-test/src/main/java/io/mifos/reporting/TestDepositListReportSpecification.java
new file mode 100644
index 0000000..cf49e91
--- /dev/null
+++ b/component-test/src/main/java/io/mifos/reporting/TestDepositListReportSpecification.java
@@ -0,0 +1,36 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting;
+
+import io.mifos.reporting.api.v1.domain.ReportDefinition;
+import org.junit.Assert;
+import org.junit.Test;
+
+import java.util.List;
+
+public class TestDepositListReportSpecification extends AbstractReportingSpecificationTest {
+ public TestDepositListReportSpecification() {
+ super();
+ }
+
+ @Test
+ public void shouldReturnReportDefinition() {
+ final List<ReportDefinition> reportDefinitions = super.testSubject.fetchReportDefinitions("Deposit");
+ Assert.assertTrue(
+ reportDefinitions.stream().anyMatch(reportDefinition -> reportDefinition.getIdentifier().equals("Listing"))
+ );
+ }
+}
diff --git a/component-test/src/main/java/io/mifos/reporting/TestIncomeStatementReportSpecification.java b/component-test/src/main/java/io/mifos/reporting/TestIncomeStatementReportSpecification.java
new file mode 100644
index 0000000..4d3a69b
--- /dev/null
+++ b/component-test/src/main/java/io/mifos/reporting/TestIncomeStatementReportSpecification.java
@@ -0,0 +1,36 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting;
+
+import io.mifos.reporting.api.v1.domain.ReportDefinition;
+import org.junit.Assert;
+import org.junit.Test;
+
+import java.util.List;
+
+public class TestIncomeStatementReportSpecification extends AbstractReportingSpecificationTest {
+ public TestIncomeStatementReportSpecification() {
+ super();
+ }
+
+ @Test
+ public void shouldReturnReportDefinition() {
+ final List<ReportDefinition> reportDefinitions = super.testSubject.fetchReportDefinitions("Accounting");
+ Assert.assertTrue(
+ reportDefinitions.stream().anyMatch(reportDefinition -> reportDefinition.getIdentifier().equals("Incomestatement"))
+ );
+ }
+}
diff --git a/component-test/src/main/java/io/mifos/reporting/TestLoanListReportSpecification.java b/component-test/src/main/java/io/mifos/reporting/TestLoanListReportSpecification.java
new file mode 100644
index 0000000..40fff05
--- /dev/null
+++ b/component-test/src/main/java/io/mifos/reporting/TestLoanListReportSpecification.java
@@ -0,0 +1,36 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting;
+
+import io.mifos.reporting.api.v1.domain.ReportDefinition;
+import org.junit.Assert;
+import org.junit.Test;
+
+import java.util.List;
+
+public class TestLoanListReportSpecification extends AbstractReportingSpecificationTest{
+ public TestLoanListReportSpecification() {
+ super();
+ }
+
+ @Test
+ public void shouldReturnReportDefinition() {
+ final List<ReportDefinition> reportDefinitions = super.testSubject.fetchReportDefinitions("Loan");
+ Assert.assertTrue(
+ reportDefinitions.stream().anyMatch(reportDefinition -> reportDefinition.getIdentifier().equals("Listing"))
+ );
+ }
+}
diff --git a/component-test/src/main/java/io/mifos/reporting/TestSavingListReportSpecification.java b/component-test/src/main/java/io/mifos/reporting/TestSavingListReportSpecification.java
deleted file mode 100644
index 4627617..0000000
--- a/component-test/src/main/java/io/mifos/reporting/TestSavingListReportSpecification.java
+++ /dev/null
@@ -1,21 +0,0 @@
-package io.mifos.reporting;
-
-import io.mifos.reporting.api.v1.domain.ReportDefinition;
-import org.junit.Assert;
-import org.junit.Test;
-
-import java.util.List;
-
-public class TestSavingListReportSpecification extends AbstractReportingSpecificationTest {
- public TestSavingListReportSpecification() {
- super();
- }
-
- @Test
- public void shouldReturnReportDefinition() {
- final List<ReportDefinition> reportDefinitions = super.testSubject.fetchReportDefinitions("Deposit");
- Assert.assertTrue(
- reportDefinitions.stream().anyMatch(reportDefinition -> reportDefinition.getIdentifier().equals("Listing"))
- );
- }
-}
diff --git a/component-test/src/main/java/io/mifos/reporting/TestTellerListReportSpecification.java b/component-test/src/main/java/io/mifos/reporting/TestTellerListReportSpecification.java
new file mode 100644
index 0000000..4b17f3b
--- /dev/null
+++ b/component-test/src/main/java/io/mifos/reporting/TestTellerListReportSpecification.java
@@ -0,0 +1,36 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting;
+
+import io.mifos.reporting.api.v1.domain.ReportDefinition;
+import org.junit.Assert;
+import org.junit.Test;
+
+import java.util.List;
+
+public class TestTellerListReportSpecification extends AbstractReportingSpecificationTest {
+ public TestTellerListReportSpecification() {
+ super();
+ }
+
+ @Test
+ public void shouldReturnReportDefinition() {
+ final List<ReportDefinition> reportDefinitions = super.testSubject.fetchReportDefinitions("Teller");
+ Assert.assertTrue(
+ reportDefinitions.stream().anyMatch(reportDefinition -> reportDefinition.getIdentifier().equals("Listing"))
+ );
+ }
+}
diff --git a/component-test/src/main/java/io/mifos/reporting/TestTellerTransactionReportSpecification.java b/component-test/src/main/java/io/mifos/reporting/TestTellerTransactionReportSpecification.java
new file mode 100644
index 0000000..6ea4da5
--- /dev/null
+++ b/component-test/src/main/java/io/mifos/reporting/TestTellerTransactionReportSpecification.java
@@ -0,0 +1,36 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting;
+
+import io.mifos.reporting.api.v1.domain.ReportDefinition;
+import org.junit.Assert;
+import org.junit.Test;
+
+import java.util.List;
+
+public class TestTellerTransactionReportSpecification extends AbstractReportingSpecificationTest {
+ public TestTellerTransactionReportSpecification() {
+ super();
+ }
+
+ @Test
+ public void shouldReturnReportDefinition() {
+ final List<ReportDefinition> reportDefinitions = super.testSubject.fetchReportDefinitions("Teller");
+ Assert.assertTrue(
+ reportDefinitions.stream().anyMatch(reportDefinition -> reportDefinition.getIdentifier().equals("Transactions"))
+ );
+ }
+}
diff --git a/gradle/wrapper/gradle-wrapper.properties b/gradle/wrapper/gradle-wrapper.properties
index 2888922..988e7e1 100644
--- a/gradle/wrapper/gradle-wrapper.properties
+++ b/gradle/wrapper/gradle-wrapper.properties
@@ -1,6 +1,6 @@
-#Fri Mar 17 17:54:20 CET 2017
+#Sun Sep 10 13:58:36 WAT 2017
distributionBase=GRADLE_USER_HOME
distributionPath=wrapper/dists
zipStoreBase=GRADLE_USER_HOME
zipStorePath=wrapper/dists
-distributionUrl=https\://services.gradle.org/distributions/gradle-3.4.1-bin.zip
+distributionUrl=https\://services.gradle.org/distributions/gradle-3.4.1-all.zip
diff --git a/gradlew b/gradlew
old mode 100644
new mode 100755
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/repository/DummyEntity.java b/service/src/main/java/io/mifos/reporting/service/internal/repository/DummyEntity.java
index 0f36a7e..2e53063 100644
--- a/service/src/main/java/io/mifos/reporting/service/internal/repository/DummyEntity.java
+++ b/service/src/main/java/io/mifos/reporting/service/internal/repository/DummyEntity.java
@@ -1,5 +1,5 @@
/*
- * Copyright 2016 The Mifos Initiative.
+ * Copyright 2017 The Mifos Initiative.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/repository/DummyRepository.java b/service/src/main/java/io/mifos/reporting/service/internal/repository/DummyRepository.java
index 20baf0e..0c70de7 100644
--- a/service/src/main/java/io/mifos/reporting/service/internal/repository/DummyRepository.java
+++ b/service/src/main/java/io/mifos/reporting/service/internal/repository/DummyRepository.java
@@ -1,5 +1,5 @@
/*
- * Copyright 2016 The Mifos Initiative.
+ * Copyright 2017 The Mifos Initiative.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/specification/BalanceSheetReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/BalanceSheetReportSpecification.java
new file mode 100644
index 0000000..1c6810e
--- /dev/null
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/BalanceSheetReportSpecification.java
@@ -0,0 +1,364 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting.service.internal.specification;
+
+import io.mifos.core.api.util.UserContextHolder;
+import io.mifos.core.lang.DateConverter;
+import io.mifos.reporting.api.v1.domain.*;
+import io.mifos.reporting.service.ServiceConstants;
+import io.mifos.reporting.service.spi.*;
+import org.slf4j.Logger;
+import org.springframework.beans.factory.annotation.Qualifier;
+
+import javax.persistence.EntityManager;
+import javax.persistence.Query;
+import java.math.BigDecimal;
+import java.time.Clock;
+import java.time.LocalDateTime;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.HashMap;
+import java.util.List;
+import java.util.stream.Collectors;
+
+@Report(category = "Accounting", identifier = "Balancesheet")
+public class BalanceSheetReportSpecification implements ReportSpecification {
+
+ private static final String DATE_RANGE = "Date range";
+ private static final String TYPE = "Type";
+ private static final String IDENTIFIER = "Identifier";
+ private static final String NAME = "Name";
+ private static final String BALANCE = "Balance";
+
+ private final Logger logger;
+
+ private final EntityManager entityManager;
+
+ private final HashMap<String, String> accountingColumnMapping = new HashMap<>();
+ private final HashMap<String, String> allColumnMapping = new HashMap<>();
+
+
+ public BalanceSheetReportSpecification(@Qualifier(ServiceConstants.LOGGER_NAME) final Logger logger,
+ final EntityManager entityManager){
+ super();
+ this.logger = logger;
+ this.entityManager = entityManager;
+ this.initializeMapping();
+ }
+
+ @Override
+ public ReportDefinition getReportDefinition() {
+ final ReportDefinition reportDefinition = new ReportDefinition();
+ reportDefinition.setIdentifier("Balancesheet");
+ reportDefinition.setName("Balance Sheet");
+ reportDefinition.setDescription("Balance Sheet Report");
+ reportDefinition.setQueryParameters(this.buildQueryParameters());
+ reportDefinition.setDisplayableFields(this.buildDisplayableFields());
+ return reportDefinition;
+ }
+
+ @Override
+ public ReportPage generateReport(ReportRequest reportRequest, int pageIndex, int size) {
+ final ReportDefinition reportDefinition = this.getReportDefinition();
+ this.logger.info("Generating report {0}.", reportDefinition.getIdentifier());
+
+ final ReportPage reportPage = new ReportPage();
+ reportPage.setName(reportDefinition.getName());
+ reportPage.setDescription(reportDefinition.getDescription());
+ reportPage.setHeader(this.createHeader(reportRequest.getDisplayableFields()));
+
+ final Query accountQuery = this.entityManager.createNativeQuery(this.buildAssetQuery(reportRequest, pageIndex, size));
+ final List<?> accountResultList = accountQuery.getResultList();
+ reportPage.setRows(this.buildRows(reportRequest, accountResultList));
+
+ reportPage.setHasMore(
+ !this.entityManager.createNativeQuery(this.buildAssetQuery(reportRequest, pageIndex + 1, size))
+ .getResultList().isEmpty()
+ );
+
+ reportPage.setGeneratedBy(UserContextHolder.checkedGetUser());
+ reportPage.setGeneratedOn(DateConverter.toIsoString(LocalDateTime.now(Clock.systemUTC())));
+ return reportPage;
+ }
+
+ @Override
+ public void validate(ReportRequest reportRequest) throws IllegalArgumentException {
+ final ArrayList<String> unknownFields = new ArrayList<>();
+ reportRequest.getQueryParameters().forEach(queryParameter -> {
+ if (!this.allColumnMapping.keySet().contains(queryParameter.getName())) {
+ unknownFields.add(queryParameter.getName());
+ }
+ });
+
+ reportRequest.getDisplayableFields().forEach(displayableField -> {
+ if (!this.allColumnMapping.keySet().contains(displayableField.getName())) {
+ unknownFields.add(displayableField.getName());
+ }
+ });
+
+ if (!unknownFields.isEmpty()) {
+ throw new IllegalArgumentException(
+ "Unspecified fields requested: " + unknownFields.stream().collect(Collectors.joining(", "))
+ );
+ }
+ }
+
+ private void initializeMapping() {
+ this.accountingColumnMapping.put(DATE_RANGE, "acc.created_on");
+ this.accountingColumnMapping.put(TYPE, "acc.a_type");
+ this.accountingColumnMapping.put(IDENTIFIER, "acc.identifier");
+ this.accountingColumnMapping.put(NAME, "acc.a_name");
+ this.accountingColumnMapping.put(BALANCE, "acc.balance");
+
+ this.allColumnMapping.putAll(accountingColumnMapping);
+ }
+
+ private Header createHeader(List<DisplayableField> displayableFields) {
+ final Header header = new Header();
+ header.setColumnNames(
+ displayableFields
+ .stream()
+ .map(DisplayableField::getName)
+ .collect(Collectors.toList())
+ );
+ return header;
+ }
+
+ private List<Row> buildRows(ReportRequest reportRequest, List<?> accountResultList) {
+ final ArrayList<Row> rows = new ArrayList<>();
+
+ final Row totalAssetRow = new Row();
+ totalAssetRow.setValues(new ArrayList<>());
+
+ final Value subAssetTotal = new Value();
+
+ final BigDecimal[] assetSubTotal = {new BigDecimal("0.000")};
+
+ accountResultList.forEach(result -> {
+
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues;
+ resultValues = (Object[]) result;
+
+ for (int i = 0; i < resultValues.length; i++){
+ final Value assetValue = new Value();
+ if (resultValues[i] != null){
+ assetValue.setValues(new String[]{resultValues[i].toString()});
+ }else assetValue.setValues(new String[]{});
+
+ row.getValues().add(assetValue);
+
+ assetSubTotal[0] = assetSubTotal[0].add((BigDecimal)resultValues[3]);
+
+ }
+ } else {
+ final Value value = new Value();
+ value.setValues(new String[]{result.toString()});
+ row.getValues().add(value);
+ }
+
+ rows.add(row);
+ });
+
+ subAssetTotal.setValues(new String[]{new StringBuilder().append("TOTAL ASSETS ").append(assetSubTotal[0]).toString()});
+ totalAssetRow.getValues().add(subAssetTotal);
+
+ rows.add(totalAssetRow);
+
+
+ final String liabilityQueryString = this.buildLiabilityQuery(reportRequest);
+ final Query liabilityQuery = this.entityManager.createNativeQuery(liabilityQueryString);
+ final List<?> liabilityResultList = liabilityQuery.getResultList();
+
+ final Row totalLiabilityRow = new Row();
+ totalLiabilityRow.setValues(new ArrayList<>());
+ final Value subLiabilityTotal = new Value();
+
+ final BigDecimal[] liabilitySubTotal = {new BigDecimal("0.000")};
+
+ liabilityResultList.forEach(result -> {
+
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues;
+ resultValues = (Object[]) result;
+
+ for (int i = 0; i < resultValues.length; i++){
+ final Value liabilityValue = new Value();
+ if (resultValues[i] != null) liabilityValue.setValues(new String[]{resultValues[i].toString()});
+ else liabilityValue.setValues(new String[]{});
+
+ row.getValues().add(liabilityValue);
+
+ liabilitySubTotal[0] = liabilitySubTotal[0].add((BigDecimal)resultValues[3]);
+
+ }
+ } else {
+ final Value value;
+ value = new Value();
+ value.setValues(new String[]{result.toString()});
+ row.getValues().add(value);
+ }
+
+ rows.add(row);
+ });
+
+ subLiabilityTotal.setValues(new String[]{new StringBuilder().append("TOTAL LIABILITIES ").append(liabilitySubTotal[0]).toString()});
+ totalLiabilityRow.getValues().add(subLiabilityTotal);
+ rows.add(totalLiabilityRow);
+
+
+
+ final String equityQueryString = this.buildEquityQuery(reportRequest);
+ final Query equityQuery = this.entityManager.createNativeQuery(equityQueryString);
+ final List<?> equityResultList = equityQuery.getResultList();
+
+ final Row totalEquityRow = new Row();
+ totalEquityRow.setValues(new ArrayList<>());
+ final Value subEquityTotal = new Value();
+
+ final Row totalLiabilityAndEquityRow = new Row();
+ totalLiabilityAndEquityRow.setValues(new ArrayList<>());
+ final Value totalLiabilityAndEquityValue = new Value();
+
+ final BigDecimal[] equitySubTotal = {new BigDecimal("0.000")};
+
+ equityResultList.forEach(result -> {
+
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues;
+ resultValues = (Object[]) result;
+
+ for (int i = 0; i < resultValues.length; i++){
+ final Value equityValue = new Value();
+ if (resultValues[i] != null) equityValue.setValues(new String[]{resultValues[i].toString()});
+ else equityValue.setValues(new String[]{});
+
+ row.getValues().add(equityValue);
+
+ equitySubTotal[0] = equitySubTotal[0].add((BigDecimal)resultValues[3]);
+
+ }
+ } else {
+ final Value value;
+ value = new Value();
+ value.setValues(new String[]{result.toString()});
+ row.getValues().add(value);
+ }
+
+ rows.add(row);
+ });
+
+ subEquityTotal.setValues(new String[]{new StringBuilder().append("TOTAL EQUITY ").append(equitySubTotal[0]).toString()});
+ totalEquityRow.getValues().add(subEquityTotal);
+ rows.add(totalEquityRow);
+
+
+ final BigDecimal liabilityAndEquity = liabilitySubTotal[0].add(equitySubTotal[0]);
+ totalLiabilityAndEquityValue.setValues(new String[]{new StringBuilder().append("TOTAL LIABILITIES and EQUITY ").append(liabilityAndEquity).toString()});
+ totalLiabilityAndEquityRow.getValues().add(totalLiabilityAndEquityValue);
+ rows.add(totalLiabilityAndEquityRow);
+
+ return rows;
+ }
+
+ private String buildAssetQuery(final ReportRequest reportRequest, int pageIndex, int size) {
+ final StringBuilder query = new StringBuilder("SELECT ");
+
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.accountingColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .append("thoth_accounts acc ")
+ .append("WHERE acc.a_type = 'ASSET' ");
+
+ query.append(" ORDER BY acc.identifier");
+
+ return query.toString();
+ }
+
+ private String buildLiabilityQuery(final ReportRequest reportRequest) {
+ final StringBuilder query = new StringBuilder("SELECT ");
+
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.accountingColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .append("thoth_accounts acc ")
+ .append("WHERE acc.a_type = 'LIABILITY' ");
+
+ query.append(" ORDER BY acc.identifier");
+
+ return query.toString();
+ }
+
+ private String buildEquityQuery(final ReportRequest reportRequest) {
+ final StringBuilder query = new StringBuilder("SELECT ");
+
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.accountingColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .append("thoth_accounts acc ")
+ .append("WHERE acc.a_type = 'EQUITY' ");
+
+ query.append(" ORDER BY acc.identifier");
+
+ return query.toString();
+ }
+
+ private List<DisplayableField> buildDisplayableFields() {
+ return Arrays.asList(
+ DisplayableFieldBuilder.create(TYPE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(IDENTIFIER, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(NAME, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(BALANCE, Type.TEXT).mandatory().build()
+ );
+ }
+
+ private List<QueryParameter> buildQueryParameters() {
+ return Arrays.asList();
+ }
+}
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/specification/DepositListReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/DepositListReportSpecification.java
index 42dabfa..8e37df7 100644
--- a/service/src/main/java/io/mifos/reporting/service/internal/specification/DepositListReportSpecification.java
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/DepositListReportSpecification.java
@@ -1,3 +1,18 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting.service.internal.specification;
import io.mifos.core.api.util.UserContextHolder;
@@ -22,26 +37,25 @@
@Report(category = "Deposit", identifier = "Listing")
public class DepositListReportSpecification implements ReportSpecification {
- private static final String CUSTOMER = "Customer";
- private static final String FIRST_NAME = "First name";
- private static final String MIDDLE_NAME = "Middle name";
- private static final String LAST_NAME = "Last name";
- private static final String EMPLOYEE = "Employee";
- private static final String ACCOUNT_NUMBER = "Account number";
- private static final String ACCOUNT_TYPE = "Account type";
- private static final String STATE = "State";
+ private static final String CUSTOMER = "Customer Account";
+ private static final String FIRST_NAME = "First Name";
+ private static final String MIDDLE_NAME = "Middle Name";
+ private static final String LAST_NAME = "Last Name";
+ private static final String EMPLOYEE = "Created By";
+ private static final String ACCOUNT_NUMBER = "Deposit Account";
+ private static final String PRODUCT = "Product";
+ private static final String ACCOUNT_TYPE = "Deposit Type";
+ private static final String STATE = "Status";
private static final String OFFICE = "Office";
- private static final String DATE_RANGE = "Date created";
- private static final String LAST_ACCOUNT_ACTIVITY = "Last account activity";
+ private static final String DATE_RANGE = "Date Created";
private final EntityManager entityManager;
private final Logger logger;
private final HashMap<String, String> customerColumnMapping = new HashMap<>();
- private final HashMap<String, String> accountColumnMapping = new HashMap<>();
- private final HashMap<String, String> officeColumnMapping = new HashMap<>();
- private final HashMap<String, String> employeeColumnMapping = new HashMap<>();
+ private final HashMap<String, String> depositAccountColumnMapping = new HashMap<>();
+ private final HashMap<String, String> depositProductColumnMapping = new HashMap<>();
private final HashMap<String, String> allColumnMapping = new HashMap<>();
@@ -75,14 +89,13 @@
reportPage.setDescription(reportDefinition.getDescription());
reportPage.setHeader(this.createHeader(reportRequest.getDisplayableFields()));
- final Query depositAccountQuery = this.entityManager.createNativeQuery(this.buildAccountQuery(reportRequest, pageIndex, size));
+ final Query customerQuery = this.entityManager.createNativeQuery(this.buildCustomerQuery(reportRequest, pageIndex, size));
- final List<?> depositAccountResultList = depositAccountQuery.getResultList();
- reportPage.setRows(this.buildRows(reportRequest, depositAccountResultList));
-
+ final List<?> customerResultList = customerQuery.getResultList();
+ reportPage.setRows(this.buildRows(reportRequest, customerResultList));
reportPage.setHasMore(
- !this.entityManager.createNativeQuery(this.buildAccountQuery(reportRequest, pageIndex + 1, size))
+ !this.entityManager.createNativeQuery(this.buildCustomerQuery(reportRequest, pageIndex + 1, size))
.getResultList().isEmpty()
);
@@ -119,21 +132,19 @@
this.customerColumnMapping.put(FIRST_NAME, "cst.given_name");
this.customerColumnMapping.put(MIDDLE_NAME, "cst.middle_name");
this.customerColumnMapping.put(LAST_NAME, "cst.surname");
+ this.customerColumnMapping.put(OFFICE, "cst.assigned_office");
- this.officeColumnMapping.put(OFFICE, "cst.assigned_office");
+ this.depositAccountColumnMapping.put(EMPLOYEE, "pi.created_by");
+ this.depositAccountColumnMapping.put(ACCOUNT_NUMBER, "pi.account_identifier");
+ this.depositAccountColumnMapping.put(STATE, "pi.a_state");
+ this.depositAccountColumnMapping.put(PRODUCT, "pi.product_definition_id");
+ this.depositAccountColumnMapping.put(DATE_RANGE, "pi.created_on");
- this.employeeColumnMapping.put(EMPLOYEE, "pi.created_by");
-
- this.accountColumnMapping.put(ACCOUNT_NUMBER, "pi.customer_identifier, pi.account_identifier");
- this.accountColumnMapping.put(STATE, " pi.a_state");
- this.accountColumnMapping.put(ACCOUNT_TYPE, "pi.product_definition_id");
- this.accountColumnMapping.put(LAST_ACCOUNT_ACTIVITY, "acc_entry.transaction_date, acc_entry.message, acc_entry.amount, acc_entry.balance");
- this.accountColumnMapping.put(DATE_RANGE, "pi.created_on");
+ this.depositProductColumnMapping.put(ACCOUNT_TYPE, "pd.a_name, pd.a_type");
this.allColumnMapping.putAll(customerColumnMapping);
- this.allColumnMapping.putAll(officeColumnMapping);
- this.allColumnMapping.putAll(employeeColumnMapping);
- this.allColumnMapping.putAll(accountColumnMapping);
+ this.allColumnMapping.putAll(depositProductColumnMapping);
+ this.allColumnMapping.putAll(depositAccountColumnMapping);
}
private Header createHeader(final List<DisplayableField> displayableFields) {
final Header header = new Header();
@@ -146,13 +157,13 @@
return header;
}
+ private List<Row> buildRows(final ReportRequest reportRequest, final List<?> customerResultList) {
+ final ArrayList<Row> rows = new ArrayList<>();
- private List<Row> buildRows(final ReportRequest reportRequest, final List<?> depositAccountResultList) {
- final ArrayList<Row> rows =new ArrayList<>();
- depositAccountResultList.forEach(result -> {
+ customerResultList.forEach(result -> {
final Row row = new Row();
row.setValues(new ArrayList<>());
- //Get the customer identifier to use for join queries.
+
final String customerIdentifier;
if (result instanceof Object[]) {
@@ -162,86 +173,104 @@
for (final Object resultValue : resultValues) {
final Value value = new Value();
- if (resultValue != null)
+ if (resultValue != null) {
value.setValues(new String[]{resultValue.toString()});
- else {
+ } else {
value.setValues(new String[]{});
}
row.getValues().add(value);
}
} else {
-
customerIdentifier = result.toString();
- final Value value;
- value = new Value();
- value.setValues(new String[]{result.toString()});
- row.getValues().add(value);
- }
- final String accountIdentifier;
-
- if (result instanceof Object[]) {
- final Object[] resultValues = (Object[]) result;
-
- accountIdentifier = resultValues[2].toString();
-
- for (final Object resultValue : resultValues) {
- final Value value;
- value = new Value();
- if (resultValue != null)
- value.setValues(new String[]{resultValue.toString()});
- else {
- value.setValues(new String[]{});
- }
-
- row.getValues().add(value);
- }
- } else {
-
- accountIdentifier = result.toString();
final Value value = new Value();
value.setValues(new String[]{result.toString()});
row.getValues().add(value);
}
- final Query customerQuery = this.entityManager.createNativeQuery(this.buildCustomerQuery(reportRequest, customerIdentifier));
- final List<?> accountResultList = customerQuery.getResultList();
- final ArrayList<String> values = new ArrayList<>();
- accountResultList.forEach(customerResult -> {
- if (customerResult instanceof Object[]) {
- final Object[] customerResultValues = (Object[]) customerResult;
- final String customerValue = customerResultValues[0].toString();
- values.add(customerValue);
+ final Query accountQuery = this.entityManager.createNativeQuery(this.buildDepositAccountQuery(reportRequest, customerIdentifier));
+ final List<?> accountResultList = accountQuery.getResultList();
+
+ final ArrayList<String> products = new ArrayList<>();
+ final ArrayList<String> depositAccountNumber = new ArrayList<>();
+ final ArrayList<String> depositType = new ArrayList<>();
+ final ArrayList<String> status = new ArrayList<>();
+ final ArrayList<String> createdBy = new ArrayList<>();
+ final ArrayList<String> dateCreated = new ArrayList<>();
+
+ accountResultList.forEach(accountResult -> {
+
+ final String productIdentifier;
+ if (accountResult instanceof Object[]) {
+ final Object[] accountResultValues = (Object[]) accountResult;
+
+ productIdentifier = accountResultValues[0].toString();
+
+ final Query depositProductQuery = this.entityManager.createNativeQuery(this.buildDepositProductQuery(reportRequest, productIdentifier));
+ final List<?> depositProductResultList = depositProductQuery.getResultList();
+
+ depositProductResultList.forEach(product -> {
+ final Object[] productResultValues = (Object[]) product;
+
+ for (int i = 0; i < productResultValues.length; i++) {
+
+ if (i == 0 && productResultValues[0] != null) {
+ products.add(productResultValues[0].toString());
+ }
+
+ if (i == 1 && productResultValues[1] != null) {
+ depositType.add(productResultValues[1].toString());
+ }
+
+ }
+ });
+
+
+ for (int i = 1; i < accountResultValues.length ; i++) {
+ if (i == 1 && accountResultValues[1] != null){
+ depositAccountNumber.add(accountResultValues[1].toString());
+ }
+
+ if (i == 2 && accountResultValues[2] != null){
+ status.add(accountResultValues[2].toString());
+ }
+
+ if (i == 3 && accountResultValues[3] != null){
+ createdBy.add(accountResultValues[3].toString());
+ }
+
+ if (i == 4 && accountResultValues[4] != null){
+ dateCreated.add(accountResultValues[4].toString());
+ }
+
+ }
}
});
- final Value customerValue = new Value();
- customerValue.setValues(values.toArray(new String[values.size()]));
- row.getValues().add(customerValue);
- final String officeQueryString = this.buildOfficeQuery(reportRequest, customerIdentifier);
- if (officeQueryString != null) {
- final Query officeQuery;
- officeQuery = this.entityManager.createNativeQuery(officeQueryString);
- final List<?> resultList = officeQuery.getResultList();
- final Value officeValue = new Value();
- officeValue.setValues(new String[]{resultList.get(0).toString()});
- row.getValues().add(officeValue);
- }
+ final Value productValue = new Value();
+ productValue.setValues(products.toArray(new String[products.size()]));
+ row.getValues().add(productValue);
- final Query lastAccountActivivityQueryString = this.entityManager.createNativeQuery(this.buildLastAccountActivity(reportRequest, accountIdentifier));
- final List<?> lastActivityResultList = lastAccountActivivityQueryString.getResultList();
- final ArrayList<String> val = new ArrayList<>();
- lastActivityResultList.forEach( lastActivityResult -> {
- if (lastActivityResult instanceof Object[]){
- final Object[] lastActivityResultValues = (Object[]) lastActivityResult;
- final String lastActivityValue = lastActivityResultValues[1].toString();
- val.add(lastActivityValue);
- }
- });
- final Value lastActivityValue = new Value();
- lastActivityValue.setValues(val.toArray(new String[values.size()]));
- row.getValues().add(lastActivityValue);
+ final Value depositTypeValue = new Value();
+ depositTypeValue.setValues(depositType.toArray(new String[depositAccountNumber.size()]));
+ row.getValues().add(depositTypeValue);
+
+ final Value depositAccountNumberValue = new Value();
+ depositAccountNumberValue.setValues(depositAccountNumber.toArray(new String[depositType.size()]));
+ row.getValues().add(depositAccountNumberValue);
+
+ final Value statusValue = new Value();
+ statusValue.setValues(status.toArray(new String[status.size()]));
+ row.getValues().add(statusValue);
+
+ final Value createdByValue = new Value();
+ createdByValue.setValues(createdBy.toArray(new String[createdBy.size()]));
+ row.getValues().add(createdByValue);
+
+ final Value dateCreatedValue = new Value();
+ dateCreatedValue.setValues(dateCreated.toArray(new String[dateCreated.size()]));
+ row.getValues().add(dateCreatedValue);
rows.add(row);
});
@@ -249,13 +278,14 @@
return rows;
}
- private String buildAccountQuery(final ReportRequest reportRequest, int pageIndex, int size) {
+ private String buildCustomerQuery(final ReportRequest reportRequest, int pageIndex, int size) {
final StringBuilder query = new StringBuilder("SELECT ");
- final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final List<DisplayableField> displayableFields;
+ displayableFields = reportRequest.getDisplayableFields();
final ArrayList<String> columns = new ArrayList<>();
displayableFields.forEach(displayableField -> {
- final String column = this.accountColumnMapping.get(displayableField.getName());
+ final String column = this.customerColumnMapping.get(displayableField.getName());
if (column != null) {
columns.add(column);
}
@@ -263,17 +293,15 @@
query.append(columns.stream().collect(Collectors.joining(", ")))
.append(" FROM ")
- .append("shed_product_instances pi");
+ .append("maat_customers cst ");
+
final List<QueryParameter> queryParameters = reportRequest.getQueryParameters();
if (!queryParameters.isEmpty()) {
final ArrayList<String> criteria = new ArrayList<>();
queryParameters.forEach(queryParameter -> {
- if (queryParameter.getValue() != null && !queryParameter.getValue().isEmpty()) {
+ if(queryParameter.getValue() != null && !queryParameter.getValue().isEmpty()) {
criteria.add(
- CriteriaBuilder.buildCriteria(this.accountColumnMapping.get(queryParameter.getName()), queryParameter)
- );
- criteria.add(
- CriteriaBuilder.buildCriteria(this.employeeColumnMapping.get(queryParameter.getName()), queryParameter)
+ CriteriaBuilder.buildCriteria(this.customerColumnMapping.get(queryParameter.getName()), queryParameter)
);
}
});
@@ -284,7 +312,7 @@
}
}
- query.append(" ORDER BY pi.customer_identifier");
+ query.append(" ORDER BY cst.identifier");
query.append(" LIMIT ");
query.append(size);
@@ -294,74 +322,56 @@
}
return query.toString();
-
- // return "SELECT ... FROM shed_product_instances pi";
-
}
- private String buildCustomerQuery(final ReportRequest reportRequest, final String customerIdentifier) {
- final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
- final ArrayList<String> columns = new ArrayList<>();
- displayableFields.forEach(displayableField -> {
- final String column = this.customerColumnMapping.get(displayableField.getName());
- if (column != null) {
- columns.add(column);
- }
- });
- return "SELECT " + columns.stream().collect(Collectors.joining(", ")) + " " +
- "FROM maat_customers cst " +
- "LEFT JOIN shed_product_instances pi on cst.identifier = pi.customer_identifier " +
- "WHERE pi.customer_identifier ='" + customerIdentifier + "' " +
- "ORDER BY cst.identifier";
- }
-
- private String buildOfficeQuery(final ReportRequest reportRequest, final String customerIdentifier) {
+ private String buildDepositAccountQuery(final ReportRequest reportRequest, final String customerIdentifier) {
final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
final ArrayList<String> columns = new ArrayList<>();
displayableFields.forEach(displayableField -> {
- final String column = this.officeColumnMapping.get(displayableField.getName());
+ final String column = this.depositAccountColumnMapping.get(displayableField.getName());
if (column != null) {
columns.add(column);
}
});
return "SELECT " + columns.stream().collect(Collectors.joining(", ")) + " " +
- "FROM maat_customers cst " +
+ "FROM shed_product_instances pi " +
+ "LEFT JOIN maat_customers cst on pi.customer_identifier = cst.identifier " +
"WHERE cst.identifier ='" + customerIdentifier + "' " +
- "ORDER BY cst.identifier";
+ "ORDER BY pi.account_identifier";
}
- private String buildLastAccountActivity(final ReportRequest reportRequest, final String accountIdentifier){
- final List<DisplayableField> displayableFields = new ArrayList<>();
+ private String buildDepositProductQuery(final ReportRequest reportRequest, final String productIdentifier){
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
final ArrayList<String> columns = new ArrayList<>();
displayableFields.forEach(displayableField -> {
- final String column = this.accountColumnMapping.get(displayableField.getName());
- if(column != null){
+ final String column = this.depositProductColumnMapping.get(displayableField.getName());
+ if (column != null) {
columns.add(column);
}
});
- return "SELECT " + columns.stream().collect(Collectors.joining(",")) + "" +
- "FROM thoth_account_entries acc_entry" +
- "WHERE acc_entry.account_id ='" + accountIdentifier + "'" +
- "ORDER BY acc_entry.transaction_date";
+ return "SELECT DISTINCT " + columns.stream().collect(Collectors.joining(", ")) + " " +
+ "FROM shed_product_definitions pd " +
+ "LEFT JOIN shed_product_instances pi on pd.id = pi.product_definition_id " +
+ "WHERE pi.product_definition_id ='" + productIdentifier + "' ";
}
private List<DisplayableField> buildDisplayableFields() {
return Arrays.asList(
DisplayableFieldBuilder.create(CUSTOMER, Type.TEXT).mandatory().build(),
- DisplayableFieldBuilder.create(FIRST_NAME, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(FIRST_NAME, Type.TEXT).mandatory().build(),
DisplayableFieldBuilder.create(MIDDLE_NAME, Type.TEXT).build(),
- DisplayableFieldBuilder.create(LAST_NAME, Type.TEXT).build(),
- DisplayableFieldBuilder.create(ACCOUNT_NUMBER, Type.TEXT).mandatory().build(),
-
- DisplayableFieldBuilder.create(STATE,Type.TEXT).build(),
- DisplayableFieldBuilder.create(LAST_ACCOUNT_ACTIVITY, Type.DATE).build(),
-
- DisplayableFieldBuilder.create(EMPLOYEE, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(LAST_NAME, Type.TEXT).mandatory().build(),
DisplayableFieldBuilder.create(OFFICE, Type.TEXT).build(),
- DisplayableFieldBuilder.create(DATE_RANGE, Type.DATE).build()
+
+ DisplayableFieldBuilder.create(PRODUCT, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(ACCOUNT_TYPE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(ACCOUNT_NUMBER, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(STATE,Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(EMPLOYEE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(DATE_RANGE, Type.DATE).mandatory().build()
);
}
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/specification/IncomeStatementReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/IncomeStatementReportSpecification.java
index 91669ec..c535d22 100644
--- a/service/src/main/java/io/mifos/reporting/service/internal/specification/IncomeStatementReportSpecification.java
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/IncomeStatementReportSpecification.java
@@ -1,3 +1,18 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting.service.internal.specification;
import io.mifos.core.api.util.UserContextHolder;
@@ -10,6 +25,7 @@
import javax.persistence.EntityManager;
import javax.persistence.Query;
+import java.math.BigDecimal;
import java.time.Clock;
import java.time.LocalDateTime;
import java.util.ArrayList;
@@ -25,9 +41,7 @@
private static final String TYPE = "Type";
private static final String IDENTIFIER = "Identifier";
private static final String NAME = "Name";
- private static final String HOLDER = "Holder";
private static final String BALANCE = "Balance";
- private static final String STATE = "State";
private final Logger logger;
@@ -50,7 +64,7 @@
final ReportDefinition reportDefinition = new ReportDefinition();
reportDefinition.setIdentifier("Incomestatement");
reportDefinition.setName("Income Statement");
- reportDefinition.setDescription("Income statement listing.");
+ reportDefinition.setDescription("Income statement report");
reportDefinition.setQueryParameters(this.buildQueryParameters());
reportDefinition.setDisplayableFields(this.buildDisplayableFields());
return reportDefinition;
@@ -107,9 +121,7 @@
this.accountColumnMapping.put(TYPE, "acc.a_type");
this.accountColumnMapping.put(IDENTIFIER, "acc.identifier");
this.accountColumnMapping.put(NAME, "acc.a_name");
- this.accountColumnMapping.put(HOLDER, "acc.holders");
this.accountColumnMapping.put(BALANCE, "acc.balance");
- this.accountColumnMapping.put(STATE, "acc.a_state");
this.allColumnMapping.putAll(accountColumnMapping);
}
@@ -127,7 +139,16 @@
private List<Row> buildRows(ReportRequest reportRequest, List<?> accountResultList) {
final ArrayList<Row> rows = new ArrayList<>();
+
+ final Row totalRevenueRow = new Row();
+ totalRevenueRow.setValues(new ArrayList<>());
+
+ final Value subRevenueTotal = new Value();
+
+ final BigDecimal[] revenueSubTotal = {new BigDecimal("0.000")};
+
accountResultList.forEach(result -> {
+
final Row row = new Row();
row.setValues(new ArrayList<>());
@@ -135,28 +156,88 @@
final Object[] resultValues;
resultValues = (Object[]) result;
- for(final Object resultVal : resultValues) {
- final Value val;
- val = new Value();
+ for (int i = 0; i < resultValues.length; i++){
+ final Value revValue = new Value();
+ if (resultValues[i] != null){
+ revValue.setValues(new String[]{resultValues[i].toString()});
+ }else revValue.setValues(new String[]{});
- if (resultVal != null) {
- val.setValues(new String[]{resultVal.toString()});
- } else val.setValues(new String[]{});
-
- row.getValues().add(val);
+ row.getValues().add(revValue);
+
+ revenueSubTotal[0] = revenueSubTotal[0].add((BigDecimal)resultValues[3]);
+
}
} else {
final Value value = new Value();
value.setValues(new String[]{result.toString()});
row.getValues().add(value);
}
+
rows.add(row);
});
+ subRevenueTotal.setValues(new String[]{new StringBuilder().append("TOTAL REVENUES ").append(revenueSubTotal[0]).toString()});
+ totalRevenueRow.getValues().add(subRevenueTotal);
+
+ rows.add(totalRevenueRow);
+
+
+ final String expenseQueryString = this.buildExpenseQuery(reportRequest);
+ final Query expenseQuery = this.entityManager.createNativeQuery(expenseQueryString);
+ final List<?> expenseResultList = expenseQuery.getResultList();
+
+ final Row totalExpenseRow = new Row();
+ totalExpenseRow.setValues(new ArrayList<>());
+ final Value subExpenseTotal = new Value();
+
+ final Row netIncomeRow = new Row();
+ netIncomeRow.setValues(new ArrayList<>());
+ final Value netIncomeTotal = new Value();
+
+ final BigDecimal[] expenseSubTotal = {new BigDecimal("0.000")};
+
+ expenseResultList.forEach(result -> {
+
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues;
+ resultValues = (Object[]) result;
+
+ for (int i = 0; i < resultValues.length; i++){
+ final Value expValue = new Value();
+ if (resultValues[i] != null) expValue.setValues(new String[]{resultValues[i].toString()});
+ else expValue.setValues(new String[]{});
+
+ row.getValues().add(expValue);
+
+ expenseSubTotal[0] = expenseSubTotal[0].add((BigDecimal)resultValues[3]);
+
+ }
+ } else {
+ final Value value;
+ value = new Value();
+ value.setValues(new String[]{result.toString()});
+ row.getValues().add(value);
+ }
+
+ rows.add(row);
+ });
+
+ subExpenseTotal.setValues(new String[]{new StringBuilder().append("TOTAL EXPENSES ").append(expenseSubTotal[0]).toString()});
+ totalExpenseRow.getValues().add(subExpenseTotal);
+ rows.add(totalExpenseRow);
+
+ final BigDecimal netIncome = revenueSubTotal[0].subtract(expenseSubTotal[0]);
+ netIncomeTotal.setValues(new String[]{new StringBuilder().append("NET INCOME ").append(netIncome).toString()});
+ netIncomeRow.getValues().add(netIncomeTotal);
+ rows.add(netIncomeRow);
+
return rows;
}
- private String buildAccountQuery(ReportRequest reportRequest, int pageIndex, int size) {
+ private String buildAccountQuery(final ReportRequest reportRequest, int pageIndex, int size) {
final StringBuilder query = new StringBuilder("SELECT ");
final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
@@ -170,52 +251,46 @@
query.append(columns.stream().collect(Collectors.joining(", ")))
.append(" FROM ")
- .append("thoth_accounts acc ");
+ .append("thoth_accounts acc ")
+ .append("WHERE acc.a_type = 'REVENUE' ");
- final List<QueryParameter> queryParameters = reportRequest.getQueryParameters();
- if (!queryParameters.isEmpty()) {
- final ArrayList<String> criteria = new ArrayList<>();
- queryParameters.forEach(queryParameter -> {
- if(queryParameter.getValue() != null && !queryParameter.getValue().isEmpty()) {
- criteria.add(
- CriteriaBuilder.buildCriteria(this.accountColumnMapping.get(queryParameter.getName()), queryParameter)
- );
- }
- });
-
- if (!criteria.isEmpty()) {
- query.append(" WHERE ");
- query.append(criteria.stream().collect(Collectors.joining(" AND ")));
- }
-
- }
query.append(" ORDER BY acc.identifier");
- query.append(" LIMIT ");
- query.append(size);
- if (pageIndex > 0) {
- query.append(" OFFSET ");
- query.append(size * pageIndex);
- }
+ return query.toString();
+ }
+
+ private String buildExpenseQuery(final ReportRequest reportRequest) {
+ final StringBuilder query = new StringBuilder("SELECT ");
+
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.accountColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .append("thoth_accounts acc ")
+ .append("WHERE acc.a_type = 'EXPENSE' ");
+
+ query.append(" ORDER BY acc.identifier");
return query.toString();
}
private List<DisplayableField> buildDisplayableFields() {
return Arrays.asList(
- DisplayableFieldBuilder.create(TYPE, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(TYPE, Type.TEXT).mandatory().build(),
DisplayableFieldBuilder.create(IDENTIFIER, Type.TEXT).mandatory().build(),
DisplayableFieldBuilder.create(NAME, Type.TEXT).mandatory().build(),
- DisplayableFieldBuilder.create(HOLDER, Type.TEXT).build(),
- DisplayableFieldBuilder.create(BALANCE, Type.TEXT).mandatory().build(),
- DisplayableFieldBuilder.create(STATE, Type.TEXT).mandatory().build()
+ DisplayableFieldBuilder.create(BALANCE, Type.TEXT).mandatory().build()
);
}
private List<QueryParameter> buildQueryParameters() {
- return Arrays.asList(
- QueryParameterBuilder.create(DATE_RANGE, Type.DATE).operator(QueryParameter.Operator.BETWEEN).build(),
- QueryParameterBuilder.create(STATE, Type.TEXT).operator(QueryParameter.Operator.IN).build()
- );
+ return Arrays.asList();
}
}
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/specification/LoanListReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/LoanListReportSpecification.java
index 0b6509b..4c71929 100644
--- a/service/src/main/java/io/mifos/reporting/service/internal/specification/LoanListReportSpecification.java
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/LoanListReportSpecification.java
@@ -1,3 +1,18 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting.service.internal.specification;
import io.mifos.core.api.util.UserContextHolder;
@@ -11,7 +26,6 @@
import javax.persistence.EntityManager;
import javax.persistence.Query;
-import java.text.DecimalFormat;
import java.time.Clock;
import java.time.LocalDateTime;
import java.util.ArrayList;
@@ -23,18 +37,22 @@
@Report(category = "Loan", identifier = "Listing")
public class LoanListReportSpecification implements ReportSpecification {
- private static final String DATE_RANGE = "Date created";
- private static final String CUSTOMER = "Customer";
- private static final String FIRST_NAME = "First name";
- private static final String MIDDLE_NAME = "Middle name";
- private static final String LAST_NAME = "Last name";
- private static final String EMPLOYEE = "Employee";
- private static final String LOAN_STATE = "State";
- private static final String LOAN_ACCOUNT_NUMBER = "Account number";
- private static final String LOAN_TYPE = "Account type";
- private static final String LOAN_TERM = "Loan term";
- private static final String OFFICE = "Office";
+ private static final String CUSTOMER = "Customer";
+ private static final String FIRST_NAME = "First Name";
+ private static final String MIDDLE_NAME = "Middle Name";
+ private static final String LAST_NAME = "Last Name";
+ private static final String LOAN_TERM = "Loan Term";
+ private static final String TIME_UNIT = "Time Unit";
+ private static final String OFFICE = "Office";
+ private static final String PRINCIPAL = "Principal";
+ private static final String CASE = "Case Id";
+
+ private static final String LOAN = "Loan";
+ private static final String PRODUCT = "Type";
+ private static final String STATE = "State";
+ private static final String DATE_RANGE = "Created On";
+ private static final String EMPLOYEE = "Created By";
private final Logger logger;
@@ -42,8 +60,7 @@
private final HashMap<String, String> customerColumnMapping = new HashMap<>();
private final HashMap<String, String> loanColumnMapping = new HashMap<>();
- private final HashMap<String, String> officeColumnMapping = new HashMap<>();
- private final HashMap<String, String> employeeColumnMapping = new HashMap<>();
+ private final HashMap<String, String> caseColumnMapping = new HashMap<>();
private final HashMap<String, String> allColumnMapping = new HashMap<>();
@Autowired
@@ -120,20 +137,20 @@
this.customerColumnMapping.put(LAST_NAME, "cst.surname");
this.customerColumnMapping.put(OFFICE, "cst.assigned_office");
- this.loanColumnMapping.put(DATE_RANGE, "cases.created_on");
- this.loanColumnMapping.put(LOAN_STATE, "cases.current_state");
- this.loanColumnMapping.put(LOAN_TYPE, "cases.product_identifier");
- this.loanColumnMapping.put(EMPLOYEE, "cases.created_by");
- this.loanColumnMapping.put(LOAN_TERM,
- "il_cases.term_range_temporal_unit, " +
- "il_cases.term_range_minimum, " +
- "il_cases.term_range_maximum, " +
- "il_cases.balance_range_maximum");
+ this.loanColumnMapping.put(LOAN_TERM, "il_cases.term_range_maximum");
+ this.loanColumnMapping.put(TIME_UNIT, "il_cases.term_range_temporal_unit");
+ this.loanColumnMapping.put(PRINCIPAL, "il_cases.balance_range_maximum");
+ this.loanColumnMapping.put(CASE, "il_cases.case_id");
- this.loanColumnMapping.put(LOAN_ACCOUNT_NUMBER, "il_cases.case_id");
+ this.caseColumnMapping.put(LOAN, "cases.identifier");
+ this.caseColumnMapping.put(PRODUCT, "cases.product_identifier");
+ this.caseColumnMapping.put(STATE, "cases.current_state");
+ this.caseColumnMapping.put(DATE_RANGE, "cases.created_on");
+ this.caseColumnMapping.put(EMPLOYEE, "cases.created_by");
this.allColumnMapping.putAll(customerColumnMapping);
this.allColumnMapping.putAll(loanColumnMapping);
+ this.allColumnMapping.putAll(caseColumnMapping);
}
private Header createHeader(List<DisplayableField> displayableFields) {
@@ -180,22 +197,55 @@
row.getValues().add(value);
}
- final DecimalFormat decimalFormat = new DecimalFormat("0.00");
final Query accountQuery = this.entityManager.createNativeQuery(this.buildLoanAccountQuery(reportRequest, customerIdentifier));
final List<?> accountResultList = accountQuery.getResultList();
- final ArrayList<String> values = new ArrayList<>();
+
accountResultList.forEach(accountResult -> {
+
+ final String caseIdentifier;
+
if (accountResult instanceof Object[]) {
final Object[] accountResultValues;
accountResultValues = (Object[]) accountResult;
- final String accountValue = accountResultValues[0].toString() + " (" +
- decimalFormat.format(Double.valueOf(accountResultValues[1].toString())) + ")";
- values.add(accountValue);
+
+ caseIdentifier = accountResultValues[0].toString();
+
+ for (final Object loan: accountResultValues) {
+ final Value value = new Value();
+ if (loan != null) {
+ value.setValues(new String[]{loan.toString()});
+ } else {
+ value.setValues(new String[]{});
+ }
+
+ row.getValues().add(value);
+ }
+ }else {
+ caseIdentifier = accountResult.toString();
+
+ final Value value = new Value();
+ value.setValues(new String[]{accountResult.toString()});
+ row.getValues().add(value);
}
+
+ final Query caseQuery = this.entityManager.createNativeQuery(this.buildCaseQuery(reportRequest, caseIdentifier));
+
+ final List<?> caseResultList = caseQuery.getResultList();
+
+ caseResultList.forEach(loanCase -> {
+ final Object[] loanCaseResultValues = (Object[]) loanCase;
+
+ for (final Object loan : loanCaseResultValues) {
+ final Value value = new Value();
+ if (loan != null) {
+ value.setValues(new String[]{loan.toString()});
+ } else {
+ value.setValues(new String[]{});
+ }
+ row.getValues().add(value);
+ }
+ });
});
- final Value accountValue = new Value();
- accountValue.setValues(values.toArray(new String[values.size()]));
- row.getValues().add(accountValue);
rows.add(row);
});
@@ -206,23 +256,25 @@
private List<DisplayableField> buildDisplayableFields() {
return Arrays.asList(
DisplayableFieldBuilder.create(CUSTOMER, Type.TEXT).mandatory().build(),
- DisplayableFieldBuilder.create(FIRST_NAME, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(FIRST_NAME, Type.TEXT).mandatory().build(),
DisplayableFieldBuilder.create(MIDDLE_NAME, Type.TEXT).build(),
- DisplayableFieldBuilder.create(LAST_NAME, Type.TEXT).build(),
- DisplayableFieldBuilder.create(LOAN_TYPE, Type.TEXT).build(),
- DisplayableFieldBuilder.create(LOAN_ACCOUNT_NUMBER, Type.TEXT).build(),
- DisplayableFieldBuilder.create(LOAN_STATE, Type.TEXT).build(),
- DisplayableFieldBuilder.create(LOAN_TERM, Type.TEXT).build(),
- DisplayableFieldBuilder.create(EMPLOYEE, Type.TEXT).build(),
- DisplayableFieldBuilder.create(OFFICE, Type.TEXT).build()
+ DisplayableFieldBuilder.create(LAST_NAME, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(OFFICE, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(CASE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(PRINCIPAL, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(LOAN_TERM, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(TIME_UNIT, Type.TEXT).mandatory().build(),
+
+ DisplayableFieldBuilder.create(LOAN, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(STATE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(EMPLOYEE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(PRODUCT, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(DATE_RANGE, Type.TEXT).mandatory().build()
);
}
private List<QueryParameter> buildQueryParameters() {
- return Arrays.asList(
- QueryParameterBuilder.create(DATE_RANGE, Type.DATE).operator(QueryParameter.Operator.BETWEEN).build(),
- QueryParameterBuilder.create(LOAN_STATE, Type.TEXT).operator(QueryParameter.Operator.IN).build()
- );
+ return Arrays.asList();
}
private String buildCustomerQuery(final ReportRequest reportRequest, int pageIndex, int size){
@@ -285,11 +337,22 @@
"FROM bastet_il_cases il_cases " +
"LEFT JOIN maat_customers cst on il_cases.customer_identifier = cst.identifier " +
"WHERE cst.identifier ='" + customerIdentifier + "' " +
- "ORDER BY il_cases.cases_id";
+ "ORDER BY il_cases.case_id";
}
- //Need this for getting details from cases table
- private String buildLoanCaseQuery(final ReportRequest reportRequest, final String customerIdentifier){
- return null;
+ private String buildCaseQuery(final ReportRequest reportRequest, final String caseIdentifier){
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.caseColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ return "SELECT " + columns.stream().collect(Collectors.joining(", ")) + " " +
+ "FROM bastet_cases cases " +
+ "LEFT JOIN bastet_il_cases il_cases on cases.id = il_cases.case_id " +
+ "WHERE il_cases.case_id ='" + caseIdentifier + "' ";
}
}
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/specification/OfficeListReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/OfficeListReportSpecification.java
new file mode 100644
index 0000000..9b06000
--- /dev/null
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/OfficeListReportSpecification.java
@@ -0,0 +1,278 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting.service.internal.specification;
+
+import io.mifos.core.api.util.UserContextHolder;
+import io.mifos.core.lang.DateConverter;
+import io.mifos.reporting.api.v1.domain.*;
+import io.mifos.reporting.service.ServiceConstants;
+import io.mifos.reporting.service.spi.*;
+import org.slf4j.Logger;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.beans.factory.annotation.Qualifier;
+
+import javax.persistence.EntityManager;
+import javax.persistence.Query;
+import java.time.Clock;
+import java.time.LocalDateTime;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.HashMap;
+import java.util.List;
+import java.util.stream.Collectors;
+@Report(category = "Organization", identifier = "Office")
+public class OfficeListReportSpecification implements ReportSpecification {
+
+ private static final String OFFICE = "Identifier";
+ private static final String OFFICE_NAME = "Office";
+ private static final String DESCRIPTION = "Description";
+ private static final String CREATED_BY = "Created By";
+ // private static final String STREET = "Street";
+ //private static final String CITY = "City";
+ // private static final String REGION = "Region";
+ // private static final String POSTAL_CODE = "Postal Code";
+ // private static final String COUNTRY = "Country";
+ private static final String ADDRESS = "Address";
+
+ private final Logger logger;
+
+ private final EntityManager entityManager;
+ private final HashMap<String, String> officeColumnMapping = new HashMap<>();
+ private final HashMap<String, String> addressColumnMapping = new HashMap<>();
+ private final HashMap<String, String> allColumnMapping = new HashMap<>();
+
+ @Autowired
+ public OfficeListReportSpecification(@Qualifier(ServiceConstants.LOGGER_NAME) final Logger logger,
+ final EntityManager entityManager) {
+ super();
+ this.logger = logger;
+ this.entityManager = entityManager;
+ this.initializeMapping();
+ }
+
+ @Override
+ public ReportDefinition getReportDefinition() {
+ final ReportDefinition reportDefinition = new ReportDefinition();
+ reportDefinition.setIdentifier("Office");
+ reportDefinition.setName("Office Listing");
+ reportDefinition.setDescription("List of all Offices.");
+ reportDefinition.setQueryParameters(this.buildQueryParameters());
+ reportDefinition.setDisplayableFields(this.buildDisplayableFields());
+ return reportDefinition;
+ }
+
+ @Override
+ public ReportPage generateReport(final ReportRequest reportRequest, final int pageIndex, final int size) {
+ final ReportDefinition reportDefinition = this.getReportDefinition();
+ this.logger.info("Generating report {0}.", reportDefinition.getIdentifier());
+
+ final ReportPage reportPage = new ReportPage();
+ reportPage.setName(reportDefinition.getName());
+ reportPage.setDescription(reportDefinition.getDescription());
+ reportPage.setHeader(this.createHeader(reportRequest.getDisplayableFields()));
+
+ final Query customerQuery = this.entityManager.createNativeQuery(this.buildOfficeQuery(reportRequest, pageIndex, size));
+ final List<?> customerResultList = customerQuery.getResultList();
+ reportPage.setRows(this.buildRows(reportRequest, customerResultList));
+
+ reportPage.setHasMore(
+ !this.entityManager.createNativeQuery(this.buildOfficeQuery(reportRequest, pageIndex + 1, size))
+ .getResultList().isEmpty()
+ );
+
+ reportPage.setGeneratedBy(UserContextHolder.checkedGetUser());
+ reportPage.setGeneratedOn(DateConverter.toIsoString(LocalDateTime.now(Clock.systemUTC())));
+ return reportPage;
+ }
+
+ @Override
+ public void validate(final ReportRequest reportRequest) throws IllegalArgumentException {
+ final ArrayList<String> unknownFields = new ArrayList<>();
+ reportRequest.getQueryParameters().forEach(queryParameter -> {
+ if (!this.allColumnMapping.keySet().contains(queryParameter.getName())) {
+ unknownFields.add(queryParameter.getName());
+ }
+ });
+
+ reportRequest.getDisplayableFields().forEach(displayableField -> {
+ if (!this.allColumnMapping.keySet().contains(displayableField.getName())) {
+ unknownFields.add(displayableField.getName());
+ }
+ });
+
+ if (!unknownFields.isEmpty()) {
+ throw new IllegalArgumentException(
+ "Unspecified fields requested: " + unknownFields.stream().collect(Collectors.joining(", "))
+ );
+ }
+ }
+
+ private void initializeMapping() {
+ this.officeColumnMapping.put(OFFICE, "ho.id");
+ this.officeColumnMapping.put(OFFICE_NAME, "ho.a_name");
+ this.officeColumnMapping.put(DESCRIPTION, "ho.description");
+ this.officeColumnMapping.put(CREATED_BY, "ho.created_by");
+
+ this.addressColumnMapping.put(ADDRESS, "CONCAT(IFNULL(ha.street, ', '), " +
+ "IFNULL(ha.postal_code, ', '), IFNULL(ha.city, ', ')," +
+ " IFNULL(ha.region, ', '), IFNULL(ha.country, ','))");
+
+ this.allColumnMapping.putAll(officeColumnMapping);
+ this.allColumnMapping.putAll(addressColumnMapping);
+ }
+
+ private Header createHeader(final List<DisplayableField> displayableFields) {
+ final Header header = new Header();
+ header.setColumnNames(
+ displayableFields
+ .stream()
+ .map(DisplayableField::getName)
+ .collect(Collectors.toList())
+ );
+ return header;
+ }
+
+ private List<QueryParameter> buildQueryParameters() {
+ return Arrays.asList(
+ //QueryParameterBuilder.create(DATE_RANGE, Type.DATE).operator(QueryParameter.Operator.BETWEEN).build(),
+ //QueryParameterBuilder.create(STATE, Type.TEXT).operator(QueryParameter.Operator.IN).build()
+ );
+ }
+
+ private List<DisplayableField> buildDisplayableFields() {
+ return Arrays.asList(
+ DisplayableFieldBuilder.create(OFFICE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(OFFICE_NAME, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(DESCRIPTION, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(CREATED_BY, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(ADDRESS, Type.TEXT).mandatory().build()
+ );
+ }
+
+ private List<Row> buildRows(final ReportRequest reportRequest, final List<?> officeResultList) {
+ final ArrayList<Row> rows = new ArrayList<>();
+
+ officeResultList.forEach(result -> {
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ final String officeIdentifier;
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues = (Object[]) result;
+
+ officeIdentifier = resultValues[0].toString();
+
+ for (final Object resultValue : resultValues) {
+ final Value value = new Value();
+ if (resultValue != null) {
+ value.setValues(new String[]{resultValue.toString()});
+ } else {
+ value.setValues(new String[]{});
+ }
+
+ row.getValues().add(value);
+ }
+ } else {
+ officeIdentifier = result.toString();
+
+ final Value value = new Value();
+ value.setValues(new String[]{result.toString()});
+ row.getValues().add(value);
+ }
+
+ final String addressQueryString = this.buildAddressQuery(reportRequest, officeIdentifier);
+ if (addressQueryString != null) {
+ final Query addressQuery = this.entityManager.createNativeQuery(addressQueryString);
+ final List<?> resultList = addressQuery.getResultList();
+ final Value addressValue = new Value();
+ addressValue.setValues(new String[]{resultList.get(0).toString()});
+ row.getValues().add(addressValue);
+ }
+
+ rows.add(row);
+ });
+
+ return rows;
+ }
+
+ private String buildOfficeQuery(final ReportRequest reportRequest, int pageIndex, int size) {
+ final StringBuilder query = new StringBuilder("SELECT ");
+
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.officeColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .append("horus_offices ho ");
+
+ final List<QueryParameter> queryParameters = reportRequest.getQueryParameters();
+ if (!queryParameters.isEmpty()) {
+ final ArrayList<String> criteria = new ArrayList<>();
+ queryParameters.forEach(queryParameter -> {
+ if(queryParameter.getValue() != null && !queryParameter.getValue().isEmpty()) {
+ criteria.add(
+ CriteriaBuilder.buildCriteria(this.officeColumnMapping.get(queryParameter.getName()), queryParameter)
+ );
+ }
+ });
+
+ if (!criteria.isEmpty()) {
+ query.append(" WHERE ");
+ query.append(criteria.stream().collect(Collectors.joining(" AND ")));
+ }
+
+ }
+ query.append(" ORDER BY ho.a_name");
+
+ query.append(" LIMIT ");
+ query.append(size);
+ if (pageIndex > 0) {
+ query.append(" OFFSET ");
+ query.append(size * pageIndex);
+ }
+
+ return query.toString();
+ }
+
+ private String buildAddressQuery(final ReportRequest reportRequest, final String officeIdentifier) {
+
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.addressColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ if (!columns.isEmpty()) {
+ return "SELECT " + columns.stream().collect(Collectors.joining(", ")) + " " +
+ "FROM horus_addresses ha " +
+ "LEFT JOIN horus_offices ho on ha.office_id = ho.id " +
+ "WHERE ho.id ='" + officeIdentifier + "' ";
+ }
+ return null;
+ }
+}
+
+
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerCashierDailyBalanceReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerCashierDailyBalanceReportSpecification.java
deleted file mode 100644
index 591351e..0000000
--- a/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerCashierDailyBalanceReportSpecification.java
+++ /dev/null
@@ -1,72 +0,0 @@
-package io.mifos.reporting.service.internal.specification;
-
-import io.mifos.reporting.api.v1.domain.*;
-import io.mifos.reporting.service.ServiceConstants;
-import io.mifos.reporting.service.spi.Report;
-import io.mifos.reporting.service.spi.ReportSpecification;
-import org.slf4j.Logger;
-import org.springframework.beans.factory.annotation.Autowired;
-import org.springframework.beans.factory.annotation.Qualifier;
-
-import javax.persistence.EntityManager;
-import java.util.List;
-
-@Report(category = "Teller" , identifier = "Transaction")
-public class TellerCashierDailyBalanceReportSpecification implements ReportSpecification {
-
- private static final String TOTAL_CASH_ON_HAND = "Cash on hand";
- private static final String TOTAL_CASH_RECEIVED = "Cash received";
- private static final String TOTAL_CASH_DISBURSED = "Cash Disbursed";
- private static final String TOTAL_NEGOTIABLE_INSTRUMENT_RECEIVED = "Negotiable instrument received";
- private static final String TOTAL_CHEQUES_RECEIVED = "Total cheques received";
- private static final String TELLER = "Teller";
- private static final String EMPLOYEE = "Employee";
- private static final String OFFICE = "Office";
- private static final String CASHDRAW_LIMIT = "Cashdraw limit";
-
- private final Logger logger;
-
- private final EntityManager entityManager;
-
- @Autowired
- public TellerCashierDailyBalanceReportSpecification(@Qualifier(ServiceConstants.LOGGER_NAME) final Logger logger,
- final EntityManager entityManager) {
- super();
- this.logger = logger;
- this.entityManager = entityManager;
- this.initializeMapping();
- }
-
- private void initializeMapping() {
- }
-
- @Override
- public ReportDefinition getReportDefinition() {
- final ReportDefinition reportDefinition = new ReportDefinition();
- reportDefinition.setIdentifier("Transactions");
- reportDefinition.setName("Teller transactions");
- reportDefinition.setDescription("List total teller/cashier transactions.");
- reportDefinition.setQueryParameters(this.buildQueryParameters());
- reportDefinition.setDisplayableFields(this.buildDisplayableFields());
- return reportDefinition;
- }
-
- private List<DisplayableField> buildDisplayableFields() {
- return null;
- }
-
- private List<QueryParameter> buildQueryParameters() {
-
- return null;
- }
-
- @Override
- public ReportPage generateReport(ReportRequest reportRequest, int pageIndex, int size) {
- return null;
- }
-
- @Override
- public void validate(ReportRequest reportRequest) throws IllegalArgumentException {
-
- }
-}
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerListReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerListReportSpecification.java
index 9c854e0..33cc53c 100644
--- a/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerListReportSpecification.java
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerListReportSpecification.java
@@ -1,3 +1,18 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting.service.internal.specification;
import io.mifos.core.api.util.UserContextHolder;
@@ -69,7 +84,7 @@
final Query tellerQuery = this.entityManager.createNativeQuery(this.buildTellerQuery(reportRequest, pageIndex, size));
final List<?> tellerResultList = tellerQuery.getResultList();
- reportPage.setRows(this.buildRows(reportRequest, tellerResultList));
+ reportPage.setRows(this.buildRows(tellerResultList));
reportPage.setHasMore(
!this.entityManager.createNativeQuery(this.buildTellerQuery(reportRequest, pageIndex + 1, size))
@@ -125,7 +140,7 @@
return header;
}
- private List<Row> buildRows(ReportRequest reportRequest, List<?> tellerResultList) {
+ private List<Row> buildRows(final List<?> tellerResultList) {
final ArrayList<Row> rows = new ArrayList<>();
tellerResultList.forEach(result -> {
final Row row = new Row();
diff --git a/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerTransactionReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerTransactionReportSpecification.java
new file mode 100644
index 0000000..245f3ad
--- /dev/null
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerTransactionReportSpecification.java
@@ -0,0 +1,370 @@
+/*
+ * Copyright 2017 The Mifos Initiative.
+ *
+ * Licensed 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 io.mifos.reporting.service.internal.specification;
+
+import io.mifos.core.api.util.UserContextHolder;
+import io.mifos.core.lang.DateConverter;
+import io.mifos.reporting.api.v1.domain.*;
+import io.mifos.reporting.service.ServiceConstants;
+import io.mifos.reporting.service.spi.*;
+import org.slf4j.Logger;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.beans.factory.annotation.Qualifier;
+
+import javax.persistence.EntityManager;
+import javax.persistence.Query;
+import java.time.Clock;
+import java.time.LocalDateTime;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.HashMap;
+import java.util.List;
+import java.util.stream.Collectors;
+
+@Report(category = "Teller", identifier = "Transactions")
+public class TellerTransactionReportSpecification implements ReportSpecification {
+
+ private static final String TELLER_ID = "Teller Id";
+ private static final String TELLER = "Teller";
+ private static final String TRANSACTION_TYPE = "Transaction Type";
+ private static final String TRANSACTION_DATE = "Transaction Date";
+ private static final String CUSTOMER = "Customer";
+ private static final String SOURCE = "Source Account";
+ private static final String TARGET = "Target Account";
+ private static final String CLERK = "Clerk";
+ private static final String AMOUNT = "Amount";
+ private static final String STATUS = "Status";
+
+ private final Logger logger;
+
+ private final EntityManager entityManager;
+ private final HashMap<String, String> tellerColumnMapping = new HashMap<>();
+ private final HashMap<String, String> transactionColumnMapping = new HashMap<>();
+ private final HashMap<String, String> allColumnMapping = new HashMap<>();
+
+ @Autowired
+ public TellerTransactionReportSpecification(@Qualifier(ServiceConstants.LOGGER_NAME) final Logger logger,
+ final EntityManager entityManager) {
+ super();
+ this.logger = logger;
+ this.entityManager = entityManager;
+ this.initializeMapping();
+ }
+
+ @Override
+ public ReportDefinition getReportDefinition() {
+ final ReportDefinition reportDefinition = new ReportDefinition();
+ reportDefinition.setIdentifier("Transactions");
+ reportDefinition.setName("Teller Transactions");
+ reportDefinition.setDescription("List all teller-cashier transactions.");
+ reportDefinition.setQueryParameters(this.buildQueryParameters());
+ reportDefinition.setDisplayableFields(this.buildDisplayableFields());
+ return reportDefinition;
+ }
+
+
+ @Override
+ public ReportPage generateReport(ReportRequest reportRequest, int pageIndex, int size) {
+ final ReportDefinition reportDefinition = this.getReportDefinition();
+ this.logger.info("Generating report {0}.", reportDefinition.getIdentifier());
+
+ final ReportPage reportPage = new ReportPage();
+ reportPage.setName(reportDefinition.getName());
+ reportPage.setDescription(reportDefinition.getDescription());
+ reportPage.setHeader(this.createHeader(reportRequest.getDisplayableFields()));
+
+ final Query tellerQuery = this.entityManager.createNativeQuery(this.buildTellerQuery(reportRequest, pageIndex, size));
+ final List<?> tellerResultList = tellerQuery.getResultList();
+ reportPage.setRows(this.buildRows(reportRequest, tellerResultList));
+
+ reportPage.setHasMore(
+ !this.entityManager.createNativeQuery(this.buildTellerQuery(reportRequest, pageIndex + 1, size))
+ .getResultList().isEmpty()
+ );
+
+ reportPage.setGeneratedBy(UserContextHolder.checkedGetUser());
+ reportPage.setGeneratedOn(DateConverter.toIsoString(LocalDateTime.now(Clock.systemUTC())));
+ return reportPage;
+ }
+
+ @Override
+ public void validate(ReportRequest reportRequest) throws IllegalArgumentException {
+ final ArrayList<String> unknownFields = new ArrayList<>();
+ reportRequest.getQueryParameters().forEach(queryParameter -> {
+ if (!this.allColumnMapping.keySet().contains(queryParameter.getName())) {
+ unknownFields.add(queryParameter.getName());
+ }
+ });
+
+ reportRequest.getDisplayableFields().forEach(displayableField -> {
+ if (!this.allColumnMapping.keySet().contains(displayableField.getName())) {
+ unknownFields.add(displayableField.getName());
+ }
+ });
+
+ if (!unknownFields.isEmpty()) {
+ throw new IllegalArgumentException(
+ "Unspecified fields requested: " + unknownFields.stream().collect(Collectors.joining(", "))
+ );
+ }
+ }
+
+
+ private void initializeMapping() {
+ this.tellerColumnMapping.put(TELLER_ID, "teller.id");
+ this.tellerColumnMapping.put(TELLER, "teller.identifier");
+
+ this.transactionColumnMapping.put(TRANSACTION_TYPE, "trx.transaction_type");
+ this.transactionColumnMapping.put(TRANSACTION_DATE, "trx.transaction_date");
+ this.transactionColumnMapping.put(CUSTOMER, "trx.customer_identifier");
+ this.transactionColumnMapping.put(SOURCE, "trx.customer_account_identifier");
+ this.transactionColumnMapping.put(TARGET, "trx.target_account_identifier");
+ this.transactionColumnMapping.put(CLERK, "trx.clerk");
+ this.transactionColumnMapping.put(AMOUNT, "trx.amount");
+ this.transactionColumnMapping.put(STATUS, "trx.a_state");
+
+ this.allColumnMapping.putAll(tellerColumnMapping);
+ this.allColumnMapping.putAll(transactionColumnMapping);
+
+ }
+
+
+ private Header createHeader(final List<DisplayableField> displayableFields) {
+ final Header header = new Header();
+ header.setColumnNames(
+ displayableFields
+ .stream()
+ .map(DisplayableField::getName)
+ .collect(Collectors.toList())
+ );
+ return header;
+ }
+
+
+ private List<Row> buildRows(final ReportRequest reportRequest, final List<?> tellerResultList) {
+ final ArrayList<Row> rows = new ArrayList<>();
+
+ tellerResultList.forEach(result -> {
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ final String tellerIdentifier;
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues = (Object[]) result;
+
+ tellerIdentifier = resultValues[0].toString();
+
+ for (final Object resultValue : resultValues) {
+ final Value value = new Value();
+ if (resultValue != null) {
+ value.setValues(new String[]{resultValue.toString()});
+ } else {
+ value.setValues(new String[]{});
+ }
+
+ row.getValues().add(value);
+ }
+ } else {
+ tellerIdentifier = result.toString();
+
+ final Value value = new Value();
+ value.setValues(new String[]{result.toString()});
+ row.getValues().add(value);
+ }
+
+ final String transactionQueryString = this.buildTellerTransactionQuery(reportRequest, tellerIdentifier);
+
+ final Query transactionQuery = this.entityManager.createNativeQuery(transactionQueryString);
+ final List<?> resultList = transactionQuery.getResultList();
+
+ final ArrayList<String> transactionType = new ArrayList<>();
+ final ArrayList<String> transactionDate = new ArrayList<>();
+ final ArrayList<String> customer = new ArrayList<>();
+ final ArrayList<String> source = new ArrayList<>();
+ final ArrayList<String> target = new ArrayList<>();
+ final ArrayList<String> clerk = new ArrayList<>();
+ final ArrayList<String> amount = new ArrayList<>();
+ final ArrayList<String> status = new ArrayList<>();
+ resultList.forEach(transaction -> {
+ final Object[] transactionValue = (Object[]) transaction;
+
+ for (int i = 0; i < transactionValue.length; i++) {
+ if (i == 0 && transactionValue[0] != null) {
+ transactionType.add(transactionValue[0].toString());
+ }
+
+ if (i == 1 && transactionValue[1] != null) {
+ transactionDate.add(transactionValue[1].toString());
+ }
+
+ if (i == 2 && transactionValue[2] != null) {
+ customer.add(transactionValue[2].toString());
+ }
+
+ if (i == 3 && transactionValue[3] != null) {
+ source.add(transactionValue[3].toString());
+ }
+
+ if (i == 4 && transactionValue[4] != null) {
+ target.add(transactionValue[4].toString());
+ }
+ if (i == 5 && transactionValue[5] != null) {
+ clerk.add(transactionValue[5].toString());
+ }
+ if (i == 6 && transactionValue[6] != null) {
+ amount.add(transactionValue[6].toString());
+ }
+ if (i == 7 && transactionValue[7] != null) {
+ status.add(transactionValue[7].toString());
+ }
+ }
+ }
+ );
+
+ final Value transactionTypeValue = new Value();
+ transactionTypeValue.setValues(transactionType.toArray(new String[transactionType.size()]));
+ row.getValues().add(transactionTypeValue);
+
+ final Value transactionDateValue = new Value();
+ transactionDateValue.setValues(transactionDate.toArray(new String[transactionDate.size()]));
+ row.getValues().add(transactionDateValue);
+
+ final Value customerValue = new Value();
+ customerValue.setValues(customer.toArray(new String[customer.size()]));
+ row.getValues().add(customerValue);
+
+ final Value sourceValue = new Value();
+ sourceValue.setValues(source.toArray(new String[source.size()]));
+ row.getValues().add(sourceValue);
+
+ final Value targetValue = new Value();
+ targetValue.setValues(target.toArray(new String[target.size()]));
+ row.getValues().add(targetValue);
+
+ final Value clerkValue = new Value();
+ clerkValue.setValues(clerk.toArray(new String[clerk.size()]));
+ row.getValues().add(clerkValue);
+
+ final Value amountValue = new Value();
+ amountValue.setValues(amount.toArray(new String[amount.size()]));
+ row.getValues().add(amountValue);
+
+ final Value statusValue = new Value();
+ statusValue.setValues(status.toArray(new String[status.size()]));
+ row.getValues().add(statusValue);
+
+ rows.add(row);
+ });
+
+ return rows;
+ }
+
+
+ private List<DisplayableField> buildDisplayableFields() {
+ return Arrays.asList(
+ DisplayableFieldBuilder.create(TELLER_ID, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(TELLER, Type.TEXT).mandatory().build(),
+
+ DisplayableFieldBuilder.create(TRANSACTION_TYPE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(TRANSACTION_DATE, Type.DATE).mandatory().build(),
+ DisplayableFieldBuilder.create(CUSTOMER, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(SOURCE, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(TARGET, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(CLERK, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(AMOUNT, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(STATUS, Type.TEXT).mandatory().build()
+ );
+ }
+
+ private List<QueryParameter> buildQueryParameters() {
+ return Arrays.asList(
+ QueryParameterBuilder.create(TRANSACTION_DATE, Type.DATE).operator(QueryParameter.Operator.BETWEEN).build(),
+ QueryParameterBuilder.create(STATUS, Type.TEXT).operator(QueryParameter.Operator.IN).build()
+ );
+ }
+
+ private String buildTellerQuery(ReportRequest reportRequest, int pageIndex, int size) {
+ final StringBuilder query = new StringBuilder("SELECT ");
+
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.tellerColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .append("tajet_teller teller ");
+
+ query.append(" ORDER BY teller.id");
+
+ query.append(" LIMIT ");
+ query.append(size);
+ if (pageIndex > 0) {
+ query.append(" OFFSET ");
+ query.append(size * pageIndex);
+ }
+
+ return query.toString();
+ }
+
+ private String buildTellerTransactionQuery(final ReportRequest reportRequest, final String tellerIdentifier) {
+
+ final StringBuilder query = new StringBuilder("SELECT ");
+
+ final List<DisplayableField> displayableFields = reportRequest.getDisplayableFields();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.transactionColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .append("tajet_teller_transactions trx " +
+ "LEFT JOIN tajet_teller teller on trx.teller_id = teller.id ");
+
+ query.append("WHERE teller.id ='" + tellerIdentifier + "'");
+
+ final List<QueryParameter> queryParameters = reportRequest.getQueryParameters();
+ if (!queryParameters.isEmpty()) {
+ final ArrayList<String> criteria = new ArrayList<>();
+ queryParameters.forEach(queryParameter -> {
+ if (queryParameter.getValue() != null && !queryParameter.getValue().isEmpty()) {
+ criteria.add(
+ CriteriaBuilder.buildCriteria(this.transactionColumnMapping.get(queryParameter.getName()), queryParameter)
+ );
+ }
+ });
+
+ if (!criteria.isEmpty()) {
+ query.append(" AND ");
+ query.append(criteria.stream().collect(Collectors.joining(" AND ")));
+ }
+
+ }
+
+ return query.toString();
+ }
+
+
+}
diff --git a/service/src/main/resources/ESAPI.properties b/service/src/main/resources/ESAPI.properties
index 1dedfe6..d9a26e3 100644
--- a/service/src/main/resources/ESAPI.properties
+++ b/service/src/main/resources/ESAPI.properties
@@ -1,54 +1,18 @@
#
-# OWASP Enterprise Security API (ESAPI) Properties file -- PRODUCTION Version
+# Copyright 2017 The Mifos Initiative.
#
-# This file is part of the Open Web Application Security Project (OWASP)
-# Enterprise Security API (ESAPI) project. For details, please see
-# http://www.owasp.org/index.php/ESAPI.
+# Licensed 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
#
-# Copyright (c) 2008,2009 - The OWASP Foundation
+# http://www.apache.org/licenses/LICENSE-2.0
#
-# DISCUSS: This may cause a major backwards compatibility issue, etc. but
-# from a name space perspective, we probably should have prefaced
-# all the property names with ESAPI or at least OWASP. Otherwise
-# there could be problems is someone loads this properties file into
-# the System properties. We could also put this file into the
-# esapi.jar file (perhaps as a ResourceBundle) and then allow an external
-# ESAPI properties be defined that would overwrite these defaults.
-# That keeps the application's properties relatively simple as usually
-# they will only want to override a few properties. If looks like we
-# already support multiple override levels of this in the
-# DefaultSecurityConfiguration class, but I'm suggesting placing the
-# defaults in the esapi.jar itself. That way, if the jar is signed,
-# we could detect if those properties had been tampered with. (The
-# code to check the jar signatures is pretty simple... maybe 70-90 LOC,
-# but off course there is an execution penalty (similar to the way
-# that the separate sunjce.jar used to be when a class from it was
-# first loaded). Thoughts?
-###############################################################################
+# 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.
#
-# WARNING: Operating system protection should be used to lock down the .esapi
-# resources directory and all the files inside and all the directories all the
-# way up to the root directory of the file system. Note that if you are using
-# file-based implementations, that some files may need to be read-write as they
-# get updated dynamically.
-#
-# Before using, be sure to update the MasterKey and MasterSalt as described below.
-# N.B.: If you had stored data that you have previously encrypted with ESAPI 1.4,
-# you *must* FIRST decrypt it using ESAPI 1.4 and then (if so desired)
-# re-encrypt it with ESAPI 2.0. If you fail to do this, you will NOT be
-# able to decrypt your data with ESAPI 2.0.
-#
-# YOU HAVE BEEN WARNED!!! More details are in the ESAPI 2.0 Release Notes.
-#
-#===========================================================================
-# ESAPI Configuration
-#
-# If true, then print all the ESAPI properties set here when they are loaded.
-# If false, they are not printed. Useful to reduce output when running JUnit tests.
-# If you need to troubleshoot a properties related problem, turning this on may help.
-# This is 'false' in the src/test/resources/.esapi version. It is 'true' by
-# default for reasons of backward compatibility with earlier ESAPI versions.
-ESAPI.printProperties=true
# ESAPI is designed to be easily extensible. You can use the reference implementation
# or implement your own providers to take advantage of your enterprise's security
diff --git a/service/src/main/resources/validation.properties b/service/src/main/resources/validation.properties
index 0e9e2d3..02dea07 100644
--- a/service/src/main/resources/validation.properties
+++ b/service/src/main/resources/validation.properties
@@ -1,3 +1,19 @@
+#
+# Copyright 2017 The Mifos Initiative.
+#
+# Licensed 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.
+#
+
# The ESAPI validator does many security checks on input, such as canonicalization
# and whitelist validation. Note that all of these validation rules are applied *after*
# canonicalization. Double-encoded characters (even with different encodings involved,
@@ -22,7 +38,7 @@
#
Validator.SafeString=^[.\\p{Alnum}\\p{Space}]{0,1024}$
-# 2 majuscules, 3 minuscules, 2 chiffres, 1 caractère spécial,
+# 2 majuscules, 3 minuscules, 2 chiffres, 1 caract�re sp�cial,
Validator.Password=^(?=.*[A-Z].*[A-Z])(?=.*[.:,;-_+"'?!@#$&*])(?=.*[0-9].*[0-9])(?=.*[a-z].*[a-z].*[a-z]).{8,20}$
Validator.Digit=^[0-9]{1,20}$
Validator.Email=^[A-Za-z0-9._%'-]+@[A-Za-z0-9.-]+\\.[a-zA-Z]{2,4}$
