This is an automated email from the ASF dual-hosted git repository. awasum pushed a commit to branch develop in repository https://gitbox.apache.org/repos/asf/fineract-cn-reporting.git
commit 2408d218a7aa88d427b13f5f64d0459c6a12bfe8 Author: Awasum Yannick <[email protected]> AuthorDate: Fri Sep 29 16:25:42 2017 +0100 More Updates Update Loan, Deposit, Teller, Income stat and balance sheet reports --- .../io/mifos/reporting/api/v1/domain/Sample.java | 15 + .../TestBalanceSheetReportSpecification.java | 37 +++ .../TestDepositListReportSpecification.java | 36 ++ .../TestIncomeStatementReportSpecification.java | 36 ++ .../reporting/TestLoanListReportSpecification.java | 36 ++ .../TestSavingListReportSpecification.java | 21 -- .../TestTellerListReportSpecification.java | 36 ++ .../TestTellerTransactionReportSpecification.java | 36 ++ gradle/wrapper/gradle-wrapper.properties | 4 +- gradlew | 0 .../service/internal/repository/DummyEntity.java | 2 +- .../internal/repository/DummyRepository.java | 2 +- .../BalanceSheetReportSpecification.java | 368 ++++++++++++++++++++ .../DepositListReportSpecification.java | 294 ++++++++-------- ...n.java => EmployeeListReportSpecification.java} | 191 +++++------ .../IncomeStatementReportSpecification.java | 162 ++++++--- .../specification/LoanListReportSpecification.java | 154 ++++++--- .../OfficeListReportSpecification.java | 278 ++++++++++++++++ ...llerCashierDailyBalanceReportSpecification.java | 72 ---- .../TellerListReportSpecification.java | 15 + .../TellerTransactionReportSpecification.java | 370 +++++++++++++++++++++ service/src/main/resources/ESAPI.properties | 62 +--- service/src/main/resources/validation.properties | 18 +- 23 files changed, 1769 insertions(+), 476 deletions(-) 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..d8a25d5 --- /dev/null +++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/BalanceSheetReportSpecification.java @@ -0,0 +1,368 @@ +/* + * 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 HOLDER = "Holder"; + private static final String BALANCE = "Balance"; + private static final String STATE = "State"; + + 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(HOLDER, "acc.holders"); + this.accountingColumnMapping.put(BALANCE, "acc.balance"); + this.accountingColumnMapping.put(STATE, "acc.a_state"); + + 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 @@ import java.util.stream.Collectors; @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 @@ public class DepositListReportSpecification implements ReportSpecification { reportPage.setDescription(reportDefinition.getDescription()); reportPage.setHeader(this.createHeader(reportRequest.getDisplayableFields())); - final Query depositAccountQuery = this.entityManager.createNativeQuery(this.buildAccountQuery(reportRequest, pageIndex, size)); - - final List<?> depositAccountResultList = depositAccountQuery.getResultList(); - reportPage.setRows(this.buildRows(reportRequest, depositAccountResultList)); + final Query customerQuery = this.entityManager.createNativeQuery(this.buildCustomerQuery(reportRequest, pageIndex, size)); + 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 @@ public class DepositListReportSpecification implements ReportSpecification { 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.employeeColumnMapping.put(EMPLOYEE, "pi.created_by"); + 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.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 @@ public class DepositListReportSpecification implements ReportSpecification { 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 @@ public class DepositListReportSpecification implements ReportSpecification { 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(); + + final Value value = new Value(); value.setValues(new String[]{result.toString()}); row.getValues().add(value); } - final String accountIdentifier; + final Query accountQuery = this.entityManager.createNativeQuery(this.buildDepositAccountQuery(reportRequest, customerIdentifier)); + final List<?> accountResultList = accountQuery.getResultList(); - if (result instanceof Object[]) { - final Object[] resultValues = (Object[]) result; + 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<>(); - accountIdentifier = resultValues[2].toString(); + accountResultList.forEach(accountResult -> { - 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[]{}); - } + final String productIdentifier; + if (accountResult instanceof Object[]) { + final Object[] accountResultValues = (Object[]) accountResult; - row.getValues().add(value); - } - } else { + productIdentifier = accountResultValues[0].toString(); - accountIdentifier = result.toString(); - final Value value = new Value(); - value.setValues(new String[]{result.toString()}); - row.getValues().add(value); - } + final Query depositProductQuery = this.entityManager.createNativeQuery(this.buildDepositProductQuery(reportRequest, productIdentifier)); + final List<?> depositProductResultList = depositProductQuery.getResultList(); - 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 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); - } + 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()); + } - 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); + 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 lastActivityValue = new Value(); - lastActivityValue.setValues(val.toArray(new String[values.size()])); - row.getValues().add(lastActivityValue); + + final Value productValue = new Value(); + productValue.setValues(products.toArray(new String[products.size()])); + row.getValues().add(productValue); + + 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 @@ public class DepositListReportSpecification implements ReportSpecification { 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 @@ public class DepositListReportSpecification implements ReportSpecification { 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()) { - criteria.add( - CriteriaBuilder.buildCriteria(this.accountColumnMapping.get(queryParameter.getName()), queryParameter) - ); + if(queryParameter.getValue() != null && !queryParameter.getValue().isEmpty()) { criteria.add( - CriteriaBuilder.buildCriteria(this.employeeColumnMapping.get(queryParameter.getName()), queryParameter) + CriteriaBuilder.buildCriteria(this.customerColumnMapping.get(queryParameter.getName()), queryParameter) ); } }); @@ -284,7 +312,7 @@ public class DepositListReportSpecification implements ReportSpecification { } } - query.append(" ORDER BY pi.customer_identifier"); + query.append(" ORDER BY cst.identifier"); query.append(" LIMIT "); query.append(size); @@ -294,74 +322,56 @@ public class DepositListReportSpecification implements ReportSpecification { } 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/LoanListReportSpecification.java b/service/src/main/java/io/mifos/reporting/service/internal/specification/EmployeeListReportSpecification.java similarity index 51% copy from service/src/main/java/io/mifos/reporting/service/internal/specification/LoanListReportSpecification.java copy to service/src/main/java/io/mifos/reporting/service/internal/specification/EmployeeListReportSpecification.java index 0b6509b..be1a58b 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/EmployeeListReportSpecification.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 org.springframework.beans.factory.annotation.Qualifier; import javax.persistence.EntityManager; import javax.persistence.Query; -import java.text.DecimalFormat; import java.time.Clock; import java.time.LocalDateTime; import java.util.ArrayList; @@ -20,35 +34,30 @@ import java.util.HashMap; import java.util.List; import java.util.stream.Collectors; -@Report(category = "Loan", identifier = "Listing") -public class LoanListReportSpecification implements ReportSpecification { +@Report(category = "Organization", identifier = "Employee") +public class EmployeeListReportSpecification 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 USERNAME = "Username"; + 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 CREATED_BY = "Created By"; + private static final String OFFICE = "Office Id"; + private static final String OFFICE_NAME = "Office Name"; private final Logger logger; private final EntityManager entityManager; - 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> officeColumnMapping = new HashMap<>(); private final HashMap<String, String> allColumnMapping = new HashMap<>(); + @Autowired - public LoanListReportSpecification(@Qualifier(ServiceConstants.LOGGER_NAME) final Logger logger, - final EntityManager entityManager) { + public EmployeeListReportSpecification(@Qualifier(ServiceConstants.LOGGER_NAME) final Logger logger, + final EntityManager entityManager) { super(); this.logger = logger; this.entityManager = entityManager; @@ -58,16 +67,16 @@ public class LoanListReportSpecification implements ReportSpecification { @Override public ReportDefinition getReportDefinition() { final ReportDefinition reportDefinition = new ReportDefinition(); - reportDefinition.setIdentifier("Listing"); - reportDefinition.setName("Loan Account Listing"); - reportDefinition.setDescription("List of all loan accounts."); + reportDefinition.setIdentifier("Employee"); + reportDefinition.setName("Employee Listing"); + reportDefinition.setDescription("List of all employees."); reportDefinition.setQueryParameters(this.buildQueryParameters()); reportDefinition.setDisplayableFields(this.buildDisplayableFields()); return reportDefinition; } @Override - public ReportPage generateReport(ReportRequest reportRequest, int pageIndex, int size) { + 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()); @@ -76,13 +85,12 @@ public class LoanListReportSpecification implements ReportSpecification { reportPage.setDescription(reportDefinition.getDescription()); reportPage.setHeader(this.createHeader(reportRequest.getDisplayableFields())); - final Query customerQuery; - customerQuery = this.entityManager.createNativeQuery(this.buildCustomerQuery(reportRequest, pageIndex, size)); + final Query customerQuery = this.entityManager.createNativeQuery(this.buildEmployeeQuery(reportRequest, pageIndex, size)); final List<?> customerResultList = customerQuery.getResultList(); reportPage.setRows(this.buildRows(reportRequest, customerResultList)); reportPage.setHasMore( - !this.entityManager.createNativeQuery(this.buildCustomerQuery(reportRequest, pageIndex + 1, size)) + !this.entityManager.createNativeQuery(this.buildEmployeeQuery(reportRequest, pageIndex + 1, size)) .getResultList().isEmpty() ); @@ -92,7 +100,7 @@ public class LoanListReportSpecification implements ReportSpecification { } @Override - public void validate(ReportRequest reportRequest) throws IllegalArgumentException { + 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())) { @@ -114,29 +122,20 @@ public class LoanListReportSpecification implements ReportSpecification { } private void initializeMapping() { - this.customerColumnMapping.put(CUSTOMER, "cst.identifier"); - 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.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_ACCOUNT_NUMBER, "il_cases.case_id"); - - this.allColumnMapping.putAll(customerColumnMapping); - this.allColumnMapping.putAll(loanColumnMapping); + this.employeeColumnMapping.put(USERNAME, "he.identifier"); + this.employeeColumnMapping.put(FIRST_NAME, "he.given_name"); + this.employeeColumnMapping.put(MIDDLE_NAME, "he.middle_name"); + this.employeeColumnMapping.put(LAST_NAME, "he.surname"); + this.employeeColumnMapping.put(CREATED_BY, "he.created_by"); + this.employeeColumnMapping.put(OFFICE, "he.assigned_office_id"); + + this.officeColumnMapping.put(OFFICE_NAME, "ho.a_name"); + + this.allColumnMapping.putAll(employeeColumnMapping); + this.allColumnMapping.putAll(officeColumnMapping); } - private Header createHeader(List<DisplayableField> displayableFields) { + private Header createHeader(final List<DisplayableField> displayableFields) { final Header header = new Header(); header.setColumnNames( displayableFields @@ -147,20 +146,20 @@ public class LoanListReportSpecification implements ReportSpecification { return header; } - private List<Row> buildRows(ReportRequest reportRequest, List<?> customerResultList) { + + private List<Row> buildRows(final ReportRequest reportRequest, final List<?> employeeResultList) { final ArrayList<Row> rows = new ArrayList<>(); - customerResultList.forEach(result -> { + employeeResultList.forEach(result -> { final Row row = new Row(); row.setValues(new ArrayList<>()); - final String customerIdentifier; + final String officeIdentifier; if (result instanceof Object[]) { - final Object[] resultValues; - resultValues = (Object[]) result; + final Object[] resultValues = (Object[]) result; - customerIdentifier = resultValues[0].toString(); + officeIdentifier = resultValues[0].toString(); for (final Object resultValue : resultValues) { final Value value = new Value(); @@ -173,29 +172,21 @@ public class LoanListReportSpecification implements ReportSpecification { row.getValues().add(value); } } else { - customerIdentifier = result.toString(); + officeIdentifier = result.toString(); final Value value = new Value(); value.setValues(new String[]{result.toString()}); 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 -> { - 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); - } - }); - final Value accountValue = new Value(); - accountValue.setValues(values.toArray(new String[values.size()])); - row.getValues().add(accountValue); + final String officeQueryString = this.buildOfficeQuery(reportRequest, officeIdentifier); + if (officeQueryString != null) { + final Query 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); + } rows.add(row); }); @@ -203,36 +194,32 @@ public class LoanListReportSpecification implements ReportSpecification { return rows; } - private List<DisplayableField> buildDisplayableFields() { + private List<QueryParameter> buildQueryParameters() { return Arrays.asList( - DisplayableFieldBuilder.create(CUSTOMER, Type.TEXT).mandatory().build(), - DisplayableFieldBuilder.create(FIRST_NAME, Type.TEXT).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() + //QueryParameterBuilder.create(DATE_RANGE, Type.DATE).operator(QueryParameter.Operator.BETWEEN).build(), + //QueryParameterBuilder.create(STATE, Type.TEXT).operator(QueryParameter.Operator.IN).build() ); } - private List<QueryParameter> buildQueryParameters() { + private List<DisplayableField> buildDisplayableFields() { 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() + DisplayableFieldBuilder.create(OFFICE, Type.TEXT).mandatory().build(), + DisplayableFieldBuilder.create(USERNAME, Type.TEXT).mandatory().build(), + DisplayableFieldBuilder.create(FIRST_NAME, Type.TEXT).mandatory().build(), + DisplayableFieldBuilder.create(MIDDLE_NAME, Type.TEXT).build(), + DisplayableFieldBuilder.create(LAST_NAME, Type.TEXT).mandatory().build(), + DisplayableFieldBuilder.create(CREATED_BY, Type.TEXT).mandatory().build(), + DisplayableFieldBuilder.create(OFFICE_NAME, Type.TEXT).mandatory().build() ); } - private String buildCustomerQuery(final ReportRequest reportRequest, int pageIndex, int size){ + private String buildEmployeeQuery(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; - column = this.customerColumnMapping.get(displayableField.getName()); + final String column = this.employeeColumnMapping.get(displayableField.getName()); if (column != null) { columns.add(column); } @@ -240,15 +227,15 @@ public class LoanListReportSpecification implements ReportSpecification { query.append(columns.stream().collect(Collectors.joining(", "))) .append(" FROM ") - .append("maat_customers cst "); + .append("horus_employees he "); 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.customerColumnMapping.get(queryParameter.getName()), queryParameter) + CriteriaBuilder.buildCriteria(this.employeeColumnMapping.get(queryParameter.getName()), queryParameter) ); } }); @@ -259,7 +246,7 @@ public class LoanListReportSpecification implements ReportSpecification { } } - query.append(" ORDER BY cst.identifier"); + query.append(" ORDER BY he.identifier"); query.append(" LIMIT "); query.append(size); @@ -271,25 +258,21 @@ public class LoanListReportSpecification implements ReportSpecification { return query.toString(); } - private String buildLoanAccountQuery(final ReportRequest reportRequest, final String customerIdentifier){ + private String buildOfficeQuery(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.loanColumnMapping.get(displayableField.getName()); + final String column = this.officeColumnMapping.get(displayableField.getName()); if (column != null) { columns.add(column); } }); - - return "SELECT " + columns.stream().collect(Collectors.joining(", ")) + " " + - "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"; - } - - //Need this for getting details from cases table - private String buildLoanCaseQuery(final ReportRequest reportRequest, final String customerIdentifier){ + if (!columns.isEmpty()) { + return "SELECT DISTINCT " + columns.get(0).toString() + " " + + "FROM horus_offices ho " + + "LEFT JOIN horus_employees he on ho.id = he.assigned_office_id " + + "WHERE he.assigned_office_id ='" + officeIdentifier + "' "; + } return null; } } 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..00c6472 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 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; @@ -50,7 +66,7 @@ public class IncomeStatementReportSpecification implements ReportSpecification { 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; @@ -127,7 +143,16 @@ public class IncomeStatementReportSpecification implements ReportSpecification { 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 +160,88 @@ public class IncomeStatementReportSpecification implements ReportSpecification { 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[]{}); + + row.getValues().add(revValue); + + revenueSubTotal[0] = revenueSubTotal[0].add((BigDecimal)resultValues[3]); - if (resultVal != null) { - val.setValues(new String[]{resultVal.toString()}); - } else val.setValues(new String[]{}); - - row.getValues().add(val); } } 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 +255,51 @@ public class IncomeStatementReportSpecification implements ReportSpecification { query.append(columns.stream().collect(Collectors.joining(", "))) .append(" FROM ") - .append("thoth_accounts acc "); - - 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) - ); - } - }); + .append("thoth_accounts acc ") + .append("WHERE acc.a_type = 'REVENUE' "); + + query.append(" ORDER BY acc.identifier"); + + return query.toString(); + } + + private String buildExpenseQuery(final ReportRequest reportRequest) { + final StringBuilder query = new StringBuilder("SELECT "); - if (!criteria.isEmpty()) { - query.append(" WHERE "); - query.append(criteria.stream().collect(Collectors.joining(" AND "))); + 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(" ORDER BY acc.identifier"); + query.append(columns.stream().collect(Collectors.joining(", "))) + .append(" FROM ") + .append("thoth_accounts acc ") + .append("WHERE acc.a_type = 'EXPENSE' "); - query.append(" LIMIT "); - query.append(size); - if (pageIndex > 0) { - query.append(" OFFSET "); - query.append(size * pageIndex); - } + 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(HOLDER, Type.TEXT).build(), + DisplayableFieldBuilder.create(BALANCE, Type.TEXT).mandatory().build() + // DisplayableFieldBuilder.create(STATE, 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() + // QueryParameterBuilder.create(DATE_RANGE, Type.DATE).operator(QueryParameter.Operator.BETWEEN).build(), + //QueryParameterBuilder.create(STATE, Type.TEXT).operator(QueryParameter.Operator.IN).build() ); } } 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..fcd6d8b 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 org.springframework.beans.factory.annotation.Qualifier; 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 @@ import java.util.stream.Collectors; @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 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 @@ public class LoanListReportSpecification implements ReportSpecification { 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 @@ public class LoanListReportSpecification implements ReportSpecification { 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 @@ public class LoanListReportSpecification implements ReportSpecification { 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,22 +256,27 @@ public class LoanListReportSpecification implements ReportSpecification { 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() + //QueryParameterBuilder.create(DATE_RANGE, Type.DATE).operator(QueryParameter.Operator.BETWEEN).build() + //QueryParameterBuilder.create(LOAN_STATE, Type.TEXT).operator(QueryParameter.Operator.IN).build() ); } @@ -285,11 +340,22 @@ public class LoanListReportSpecification implements ReportSpecification { "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..7f33edb 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; 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..e24f622 --- /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(ReportRequest reportRequest, 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 -# -# 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. -# -# Copyright (c) 2008,2009 - The OWASP Foundation -# -# 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? -############################################################################### -# -# 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. +# 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. # -#=========================================================================== -# 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}$
