Merge pull request #1 from markusgeiss/develop
some new reports
diff --git a/.gitignore b/.gitignore
index f9d7cba..1ef178c 100644
--- a/.gitignore
+++ b/.gitignore
@@ -14,3 +14,5 @@
*.log
*.toDelete
+*.class
+out/
\ No newline at end of file
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
new file mode 100644
index 0000000..ba800f4
--- /dev/null
+++ b/api/src/test/java/io/mifos/reporting/api/v1/domain/Sample.java
@@ -0,0 +1,20 @@
+package io.mifos.reporting.api.v1.domain;
+
+public class Sample {
+ private Object identifier;
+ private Object payload;
+ public Sample(){
+ super();
+ }
+ public static Sample create(String xxxx, String yyy) {
+ return new Sample();
+ }
+
+ public void setIdentifier(Object identifier) {
+ this.identifier = identifier;
+ }
+
+ public void setPayload(String payload) {
+ this.payload = payload;
+ }
+}
diff --git a/component-test/src/main/java/io/mifos/reporting/TestSavingListReportSpecification.java b/component-test/src/main/java/io/mifos/reporting/TestSavingListReportSpecification.java
new file mode 100644
index 0000000..4627617
--- /dev/null
+++ b/component-test/src/main/java/io/mifos/reporting/TestSavingListReportSpecification.java
@@ -0,0 +1,21 @@
+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/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
new file mode 100644
index 0000000..42dabfa
--- /dev/null
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/DepositListReportSpecification.java
@@ -0,0 +1,377 @@
+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 = "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 OFFICE = "Office";
+ private static final String DATE_RANGE = "Date created";
+ private static final String LAST_ACCOUNT_ACTIVITY = "Last account activity";
+
+ 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> allColumnMapping = new HashMap<>();
+
+
+ @Autowired
+ public DepositListReportSpecification(@Qualifier(ServiceConstants.LOGGER_NAME) final Logger logger, final EntityManager entityManager) {
+ this.entityManager = entityManager;
+ this.logger = logger;
+ this.initializeMapping();
+ }
+
+
+ @Override
+ public ReportDefinition getReportDefinition() {
+
+ final ReportDefinition reportDefinition = new ReportDefinition();
+ reportDefinition.setIdentifier("Listing");
+ reportDefinition.setName("Deposit Account Listing");
+ reportDefinition.setDescription("List of all deposit accounts.");
+ 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 depositAccountQuery = this.entityManager.createNativeQuery(this.buildAccountQuery(reportRequest, pageIndex, size));
+
+ final List<?> depositAccountResultList = depositAccountQuery.getResultList();
+ reportPage.setRows(this.buildRows(reportRequest, depositAccountResultList));
+
+
+ reportPage.setHasMore(
+ !this.entityManager.createNativeQuery(this.buildAccountQuery(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.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.officeColumnMapping.put(OFFICE, "cst.assigned_office");
+
+ this.employeeColumnMapping.put(EMPLOYEE, "pi.created_by");
+
+ this.accountColumnMapping.put(ACCOUNT_NUMBER, "pi.customer_identifier, pi.account_identifier");
+ this.accountColumnMapping.put(STATE, " pi.a_state");
+ this.accountColumnMapping.put(ACCOUNT_TYPE, "pi.product_definition_id");
+ this.accountColumnMapping.put(LAST_ACCOUNT_ACTIVITY, "acc_entry.transaction_date, acc_entry.message, acc_entry.amount, acc_entry.balance");
+ this.accountColumnMapping.put(DATE_RANGE, "pi.created_on");
+
+ this.allColumnMapping.putAll(customerColumnMapping);
+ this.allColumnMapping.putAll(officeColumnMapping);
+ this.allColumnMapping.putAll(employeeColumnMapping);
+ this.allColumnMapping.putAll(accountColumnMapping);
+ }
+ private Header createHeader(final List<DisplayableField> displayableFields) {
+ final Header header = new Header();
+ header.setColumnNames(
+ displayableFields
+ .stream()
+ .map(DisplayableField::getName)
+ .collect(Collectors.toList())
+ );
+ return header;
+ }
+
+
+ private List<Row> buildRows(final ReportRequest reportRequest, final List<?> depositAccountResultList) {
+ final ArrayList<Row> rows =new ArrayList<>();
+ depositAccountResultList.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[]) {
+ final Object[] resultValues = (Object[]) result;
+
+ customerIdentifier = 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 {
+
+ customerIdentifier = result.toString();
+ final Value value;
+ value = new Value();
+ value.setValues(new String[]{result.toString()});
+ row.getValues().add(value);
+ }
+
+ final String accountIdentifier;
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues = (Object[]) result;
+
+ accountIdentifier = resultValues[2].toString();
+
+ for (final Object resultValue : resultValues) {
+ final Value value;
+ value = new Value();
+ if (resultValue != null)
+ value.setValues(new String[]{resultValue.toString()});
+ else {
+ value.setValues(new String[]{});
+ }
+
+ row.getValues().add(value);
+ }
+ } else {
+
+ accountIdentifier = result.toString();
+ final Value value = new Value();
+ value.setValues(new String[]{result.toString()});
+ row.getValues().add(value);
+ }
+
+ final Query customerQuery = this.entityManager.createNativeQuery(this.buildCustomerQuery(reportRequest, customerIdentifier));
+ final List<?> accountResultList = customerQuery.getResultList();
+ final ArrayList<String> values = new ArrayList<>();
+ accountResultList.forEach(customerResult -> {
+ if (customerResult instanceof Object[]) {
+ final Object[] customerResultValues = (Object[]) customerResult;
+ final String customerValue = customerResultValues[0].toString();
+ values.add(customerValue);
+ }
+ });
+ final Value customerValue = new Value();
+ customerValue.setValues(values.toArray(new String[values.size()]));
+ row.getValues().add(customerValue);
+
+ final String officeQueryString = this.buildOfficeQuery(reportRequest, customerIdentifier);
+ if (officeQueryString != null) {
+ final Query officeQuery;
+ officeQuery = this.entityManager.createNativeQuery(officeQueryString);
+ final List<?> resultList = officeQuery.getResultList();
+ final Value officeValue = new Value();
+ officeValue.setValues(new String[]{resultList.get(0).toString()});
+ row.getValues().add(officeValue);
+ }
+
+ final Query lastAccountActivivityQueryString = this.entityManager.createNativeQuery(this.buildLastAccountActivity(reportRequest, accountIdentifier));
+ final List<?> lastActivityResultList = lastAccountActivivityQueryString.getResultList();
+ final ArrayList<String> val = new ArrayList<>();
+ lastActivityResultList.forEach( lastActivityResult -> {
+ if (lastActivityResult instanceof Object[]){
+ final Object[] lastActivityResultValues = (Object[]) lastActivityResult;
+ final String lastActivityValue = lastActivityResultValues[1].toString();
+ val.add(lastActivityValue);
+ }
+ });
+ final Value lastActivityValue = new Value();
+ lastActivityValue.setValues(val.toArray(new String[values.size()]));
+ row.getValues().add(lastActivityValue);
+
+ rows.add(row);
+ });
+
+ return rows;
+ }
+
+ private String buildAccountQuery(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.accountColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .append("shed_product_instances pi");
+ 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)
+ );
+ criteria.add(
+ CriteriaBuilder.buildCriteria(this.employeeColumnMapping.get(queryParameter.getName()), queryParameter)
+ );
+ }
+ });
+
+ if (!criteria.isEmpty()) {
+ query.append(" WHERE ");
+ query.append(criteria.stream().collect(Collectors.joining(" AND ")));
+ }
+
+ }
+ query.append(" ORDER BY pi.customer_identifier");
+
+ query.append(" LIMIT ");
+ query.append(size);
+ if (pageIndex > 0) {
+ query.append(" OFFSET ");
+ query.append(size * pageIndex);
+ }
+
+ 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) {
+ 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);
+ }
+ });
+
+ return "SELECT " + columns.stream().collect(Collectors.joining(", ")) + " " +
+ "FROM maat_customers cst " +
+ "WHERE cst.identifier ='" + customerIdentifier + "' " +
+ "ORDER BY cst.identifier";
+ }
+
+ private String buildLastAccountActivity(final ReportRequest reportRequest, final String accountIdentifier){
+ final List<DisplayableField> displayableFields = new ArrayList<>();
+ final ArrayList<String> columns = new ArrayList<>();
+ displayableFields.forEach(displayableField -> {
+ final String column = this.accountColumnMapping.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";
+ }
+
+ private List<DisplayableField> buildDisplayableFields() {
+
+ 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(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(OFFICE, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(DATE_RANGE, Type.DATE).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()
+ );
+ }
+
+
+}
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
new file mode 100644
index 0000000..91669ec
--- /dev/null
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/IncomeStatementReportSpecification.java
@@ -0,0 +1,221 @@
+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.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 = "Incomestatement")
+public class IncomeStatementReportSpecification 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> accountColumnMapping = new HashMap<>();
+ private final HashMap<String, String> allColumnMapping = new HashMap<>();
+
+
+ public IncomeStatementReportSpecification(@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("Incomestatement");
+ reportDefinition.setName("Income Statement");
+ reportDefinition.setDescription("Income statement listing.");
+ 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.buildAccountQuery(reportRequest, pageIndex, size));
+ final List<?> accountResultList = accountQuery.getResultList();
+ reportPage.setRows(this.buildRows(reportRequest, accountResultList));
+
+ reportPage.setHasMore(
+ !this.entityManager.createNativeQuery(this.buildAccountQuery(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.accountColumnMapping.put(DATE_RANGE, "acc.created_on");
+ this.accountColumnMapping.put(TYPE, "acc.a_type");
+ this.accountColumnMapping.put(IDENTIFIER, "acc.identifier");
+ this.accountColumnMapping.put(NAME, "acc.a_name");
+ this.accountColumnMapping.put(HOLDER, "acc.holders");
+ this.accountColumnMapping.put(BALANCE, "acc.balance");
+ this.accountColumnMapping.put(STATE, "acc.a_state");
+
+ this.allColumnMapping.putAll(accountColumnMapping);
+ }
+
+ 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<>();
+ accountResultList.forEach(result -> {
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues;
+ resultValues = (Object[]) result;
+
+ for(final Object resultVal : resultValues) {
+ final Value val;
+ val = new Value();
+
+ 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);
+ });
+
+ return rows;
+ }
+
+ private String buildAccountQuery(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.accountColumnMapping.get(displayableField.getName());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ 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)
+ );
+ }
+ });
+
+ if (!criteria.isEmpty()) {
+ query.append(" WHERE ");
+ query.append(criteria.stream().collect(Collectors.joining(" AND ")));
+ }
+
+ }
+ query.append(" ORDER BY acc.identifier");
+
+ query.append(" LIMIT ");
+ query.append(size);
+ if (pageIndex > 0) {
+ query.append(" OFFSET ");
+ query.append(size * pageIndex);
+ }
+
+ return query.toString();
+ }
+
+ private List<DisplayableField> buildDisplayableFields() {
+ return Arrays.asList(
+ DisplayableFieldBuilder.create(TYPE, Type.TEXT).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()
+ );
+ }
+
+ 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()
+ );
+ }
+}
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
new file mode 100644
index 0000000..0b6509b
--- /dev/null
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/LoanListReportSpecification.java
@@ -0,0 +1,295 @@
+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.text.DecimalFormat;
+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 = "Loan", identifier = "Listing")
+public class LoanListReportSpecification implements ReportSpecification {
+
+ private static final String DATE_RANGE = "Date created";
+ private static final String CUSTOMER = "Customer";
+ private static final String FIRST_NAME = "First name";
+ private static final String MIDDLE_NAME = "Middle name";
+ private static final String LAST_NAME = "Last name";
+ private static final String EMPLOYEE = "Employee";
+ private static final String LOAN_STATE = "State";
+ private static final String LOAN_ACCOUNT_NUMBER = "Account number";
+ private static final String LOAN_TYPE = "Account type";
+ private static final String LOAN_TERM = "Loan term";
+ private static final String OFFICE = "Office";
+
+
+ private 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> allColumnMapping = new HashMap<>();
+
+ @Autowired
+ public LoanListReportSpecification(@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("Listing");
+ reportDefinition.setName("Loan Account Listing");
+ reportDefinition.setDescription("List of all loan accounts.");
+ 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 customerQuery;
+ 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.buildCustomerQuery(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.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);
+ }
+
+ 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<?> customerResultList) {
+ final ArrayList<Row> rows = new ArrayList<>();
+
+ customerResultList.forEach(result -> {
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ final String customerIdentifier;
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues;
+ resultValues = (Object[]) result;
+
+ customerIdentifier = 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 {
+ customerIdentifier = 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);
+
+ rows.add(row);
+ });
+
+ return rows;
+ }
+
+ private List<DisplayableField> buildDisplayableFields() {
+ 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()
+ );
+ }
+
+ 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()
+ );
+ }
+
+ private String buildCustomerQuery(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());
+ if (column != null) {
+ columns.add(column);
+ }
+ });
+
+ query.append(columns.stream().collect(Collectors.joining(", ")))
+ .append(" FROM ")
+ .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.customerColumnMapping.get(queryParameter.getName()), queryParameter)
+ );
+ }
+ });
+
+ if (!criteria.isEmpty()) {
+ query.append(" WHERE ");
+ query.append(criteria.stream().collect(Collectors.joining(" AND ")));
+ }
+
+ }
+ query.append(" ORDER BY cst.identifier");
+
+ query.append(" LIMIT ");
+ query.append(size);
+ if (pageIndex > 0) {
+ query.append(" OFFSET ");
+ query.append(size * pageIndex);
+ }
+
+ return query.toString();
+ }
+
+ private String buildLoanAccountQuery(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.loanColumnMapping.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){
+ 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
new file mode 100644
index 0000000..591351e
--- /dev/null
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerCashierDailyBalanceReportSpecification.java
@@ -0,0 +1,72 @@
+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
new file mode 100644
index 0000000..9c854e0
--- /dev/null
+++ b/service/src/main/java/io/mifos/reporting/service/internal/specification/TellerListReportSpecification.java
@@ -0,0 +1,221 @@
+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 = "Listing")
+public class TellerListReportSpecification implements ReportSpecification {
+
+ 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 static final String STATE = "State";
+ private static final String DATE_RANGE = "Date";
+
+ private final Logger logger;
+
+ private final EntityManager entityManager;
+
+ private final HashMap<String, String> tellerColumnMapping = new HashMap<>();
+ private final HashMap<String, String> allColumnMapping = new HashMap<>();
+
+
+ @Autowired
+ public TellerListReportSpecification(@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("Listing");
+ reportDefinition.setName("Teller Listing");
+ reportDefinition.setDescription("List of all Tellers.");
+ 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(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.tellerColumnMapping.put(TELLER, "tl.identifier");
+ this.tellerColumnMapping.put(OFFICE, "tl.office_identifier");
+ this.tellerColumnMapping.put(CASHDRAW_LIMIT, "tl.cashdraw_limit");
+ this.tellerColumnMapping.put(EMPLOYEE, "tl.assigned_employee_identifier");
+ this.tellerColumnMapping.put(STATE, "tl.a_state");
+ this.tellerColumnMapping.put(DATE_RANGE, "tl.created_on");
+
+ this.allColumnMapping.putAll(tellerColumnMapping);
+ }
+
+ 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<?> tellerResultList) {
+ final ArrayList<Row> rows = new ArrayList<>();
+ tellerResultList.forEach(result -> {
+ final Row row = new Row();
+ row.setValues(new ArrayList<>());
+
+ if (result instanceof Object[]) {
+ final Object[] resultValues = (Object[]) result;
+
+ for(final Object resultVal : resultValues) {
+ final Value val;
+ val = new Value();
+
+ 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);
+ });
+
+ return rows;
+ }
+
+ 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(TELLER, Type.TEXT).mandatory().build(),
+ DisplayableFieldBuilder.create(OFFICE, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(EMPLOYEE, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(CASHDRAW_LIMIT, Type.TEXT).build(),
+ DisplayableFieldBuilder.create(STATE, Type.TEXT).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 tl ");
+
+ 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.tellerColumnMapping.get(queryParameter.getName()), queryParameter)
+ );
+ }
+ });
+
+ if (!criteria.isEmpty()) {
+ query.append(" WHERE ");
+ query.append(criteria.stream().collect(Collectors.joining(" AND ")));
+ }
+
+ }
+ query.append(" ORDER BY tl.identifier");
+
+ query.append(" LIMIT ");
+ query.append(size);
+ if (pageIndex > 0) {
+ query.append(" OFFSET ");
+ query.append(size * pageIndex);
+ }
+
+ return query.toString();
+ }
+}
