aswi2020vldc-gitlab/src/main/java/vldc/aswi/service/SqlQueryManagerImpl.java @ ea163a4c

package vldc.aswi.service;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import vldc.aswi.database.DatabaseInterface;
import vldc.aswi.domain.Assembly;
import vldc.aswi.domain.Function;
import vldc.aswi.domain.parameter.ParameterInConfiguration;
import vldc.aswi.model.table.NameUserName;
import vldc.aswi.model.table.TableColumn;
import vldc.aswi.model.table.ValueFunction;
import vldc.aswi.model.table.contingencyTable.ContingencyTableRow;
import vldc.aswi.utils.Converter;

import java.util.*;
import java.util.concurrent.TimeUnit;

/**
 * Manager for SqlQuery.
 */
@Service
@Slf4j
public class SqlQueryManagerImpl implements SqlQueryManager {

    /**
     * Autowired databaseInterface component.
     */
    @Autowired
    private DatabaseInterface databaseInterface;

    /**
     * Get list of  contingencyTableRow.
     * @param assembly Assembly from which configuration was created.
     * @param parametersInConfiguration Parameters in configuration with their locations and operators.
     * @param isNotRemoveEmpty Sets if empty rows and columns should not be removed.
     * @param tableName Name of the table.
     * @return List of contingencyTableRow or null, if error in SQL query occurred.
     */
    @Override
    public List<ContingencyTableRow> getContingencyTableRow(Assembly assembly,
                                                            List<ParameterInConfiguration> parametersInConfiguration,
                                                            boolean isNotRemoveEmpty, String tableName) {
        long startTime = System.nanoTime();

        Map<String, TableColumn> tableColumns = this.databaseInterface.executeQueryAndReturnTableColumns(
                generateFullSQLQuery(assembly.getSQLQuery(), parametersInConfiguration)
        );

        // there was an error in the query
        if (tableColumns == null) {
            return null;
        }

        // no rows were returned by query
        if (tableColumns.values().iterator().next().getValues().size() == 0) {
            return new ArrayList<>();
        }

        long stopTime = System.nanoTime();
        System.out.println("Select:" + TimeUnit.MILLISECONDS.convert((stopTime - startTime), TimeUnit.NANOSECONDS));
        List<NameUserName> rowNames = new ArrayList<>();
        List<NameUserName> columnNames = new ArrayList<>();
        List<ValueFunction> valueFunctions = new ArrayList<>();

        // Create a copy of list.
        List<ParameterInConfiguration> copiedParametersInConfiguration = new ArrayList<>(parametersInConfiguration);

        // sort parameters in configuration to have them in order set by user
        Collections.sort(copiedParametersInConfiguration);

        initializeContingencyTableDataLists(rowNames, columnNames, valueFunctions, copiedParametersInConfiguration);

        Converter converter = new Converter(tableColumns, rowNames, columnNames, valueFunctions, isNotRemoveEmpty, tableName);
        return converter.convertTableColumnsToContingencyTableRows();
    }

    /**
     * Validate given SQL query.
     * @param sqlQuery - Validated SQL query.
     * @return true if SQL query is ok, false if not.
     */
    @Override
    public boolean validateSQLQuery(String sqlQuery) {
        return this.databaseInterface.validateSQLQuery(sqlQuery);
    }

    /**
     * Get list of table column names from SQL query.
     * @param sqlQuery - SQL query.
     * @return List of table column names.
     */
    public List<String> getNameOfColumnsFromQuery(String sqlQuery) {
        return this.databaseInterface.getNameOfColumnsFromQuery(sqlQuery);
    }

    /**
     * Initializes list with values.
     * @param rowNames List of selected rows.
     * @param columnNames List of selected columns.
     * @param valueFunctions List of selected values and functions.
     * @param parametersInConfiguration List of parameters in configuration.
     */
    private void initializeContingencyTableDataLists(List<NameUserName> rowNames, List<NameUserName> columnNames,
                                                     List<ValueFunction> valueFunctions, List<ParameterInConfiguration> parametersInConfiguration) {
        for (ParameterInConfiguration parameterInConfiguration : parametersInConfiguration) {
            if (parameterInConfiguration.getLocation().getName() != null && parameterInConfiguration.getLocation().getName().equals("Řádek")) {
                if (parameterInConfiguration.getColumnName() != null && !parameterInConfiguration.getColumnName().equals("")) {
                    rowNames.add(new NameUserName(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
                            parameterInConfiguration.getColumnName()));
                }
                else {
                    rowNames.add(new NameUserName(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
                            parameterInConfiguration.getParameter().getName()));
                }
            } else if (parameterInConfiguration.getLocation().getName() != null && parameterInConfiguration.getLocation().getName().equals("Sloupec")) {
                if (parameterInConfiguration.getColumnName() != null && !parameterInConfiguration.getColumnName().equals("")) {
                    columnNames.add(new NameUserName(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
                            parameterInConfiguration.getColumnName()));
                }
                else {
                    columnNames.add(new NameUserName(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
                            parameterInConfiguration.getParameter().getName()));
                }
            } else if (parameterInConfiguration.getLocation().getName() != null && parameterInConfiguration.getLocation().getName().equals("Hodnota")) {
                for (Function function : parameterInConfiguration.getFunctions()) {
                    valueFunctions.add(new ValueFunction(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
                            parameterInConfiguration.getParameter().getName(), function.getName().toUpperCase(),
                            parameterInConfiguration.getParameter().getParameterType().getName()));
                }
                if (valueFunctions.size() == 0) {
                    valueFunctions.add(new ValueFunction(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
                            parameterInConfiguration.getParameter().getName(), "",
                            parameterInConfiguration.getParameter().getParameterType().getName()));
                }
            }
        }

        if (valueFunctions.size() == 0) {
            valueFunctions.add(new ValueFunction("", "nic", "NIC", ""));
        }
    }

    /**
     * Generates full SQL query with all the filters
     * @param basicSQLQuery SQL query saved in assembly
     * @param parametersInConfiguration Parameters in configuration.
     * @return SQL query with filters.
     */
    private String generateFullSQLQuery(String basicSQLQuery, List<ParameterInConfiguration> parametersInConfiguration) {
        StringBuilder finalSQLQuery = new StringBuilder("SELECT * FROM (");
        finalSQLQuery.append(basicSQLQuery);
        finalSQLQuery.append(")");

        boolean isWhereDefined = false;

        for (ParameterInConfiguration parameterInConfiguration : parametersInConfiguration) {
            if (parameterInConfiguration.getParameter().getParameterType().getName().equals("Výčet") && false) {
                isWhereDefined = processListing(parameterInConfiguration, finalSQLQuery, isWhereDefined);
            }
            else if (parameterInConfiguration.getOperator() != null && !parameterInConfiguration.getOperator().getName().equals("zadny") &&
                    parameterInConfiguration.getOperatorValue() != null && !parameterInConfiguration.getOperatorValue().equals("")) {
                isWhereDefined = processFilter(parameterInConfiguration, finalSQLQuery, isWhereDefined);
            }
        }

        return finalSQLQuery.toString();
    }

    /**
     * Processes listing (enum) type.
     * @param parameterInConfiguration Listing type parameter iin configuration.
     * @param finalSQLQuery Resulting SQL query.
     * @param isWhereDefined Variable storing information if WHERE clause was defined in the resulting SQL query.
     * @return Information that WHERE clause was defined.
     */
    private boolean processListing(ParameterInConfiguration parameterInConfiguration, StringBuilder finalSQLQuery, boolean isWhereDefined) {
        if (!isWhereDefined) {
            finalSQLQuery.append(" WHERE ");
        } else {
            finalSQLQuery.append(" AND ");
        }

        finalSQLQuery.append(parameterInConfiguration.getParameter().getNameOfSelect());
        finalSQLQuery.append(" IN ");
        inNotIn(null, "Text");

        return true;
    }

    /**
     * Process filter of parameter in configuration.
     * @param parameterInConfiguration Parameter in configuration.
     * @param finalSQLQuery Resulting SQL query.
     * @param isWhereDefined Variable storing information if WHERE clause was defined in the resulting SQL query.
     * @return Information that WHERE clause was defined
     */
    private boolean processFilter(ParameterInConfiguration parameterInConfiguration, StringBuilder finalSQLQuery, boolean isWhereDefined) {
        String[] values = parameterInConfiguration.getOperatorValue().split(";");
        if (!isWhereDefined) {
            finalSQLQuery.append(" WHERE ");
        } else {
            finalSQLQuery.append(" AND ");
        }

        finalSQLQuery.append(parameterInConfiguration.getParameter().getNameOfSelect());
        finalSQLQuery.append(" ");
        finalSQLQuery.append(parameterInConfiguration.getOperator().getName());
        if (parameterInConfiguration.getOperator().getName().equals("IS NULL") ||
                parameterInConfiguration.getOperator().getName().equals("IS NOT NULL")) {
            return true;
        }
        finalSQLQuery.append(" ");
        switch (parameterInConfiguration.getOperator().getName()) {
            case "BETWEEN":
            case "NOT BETWEEN":
                finalSQLQuery.append(betweenNotBetween(values, parameterInConfiguration.getParameter().getParameterType().getName()));
                break;
            case "IN":
            case "NOT IN":
                finalSQLQuery.append(inNotIn(values, parameterInConfiguration.getParameter().getParameterType().getName()));
                break;
            case "LIKE":
            case "NOT LIKE":
                finalSQLQuery.append(likeNotLike(values, parameterInConfiguration.getParameter().getParameterType().getName()));
                break;
            default:
                finalSQLQuery.append(otherOperators(values, parameterInConfiguration.getParameter().getParameterType().getName()));
                break;
        }

        return true;
    }

    /**
     * Validate LIKE and NOT LIKE operator.
     * @param values Values set by user.
     * @param type Type of the parameter.
     * @return String to be added to SQL query.
     */
    private String likeNotLike(String[] values, String type) {
        StringBuilder SQLQueryPart = new StringBuilder();
        addQuotes(type, SQLQueryPart);
        SQLQueryPart.append(values[0]);
        addQuotes(type, SQLQueryPart);

        return SQLQueryPart.toString();
    }

    /**
     * Validate IN and NOT IN operator.
     * @param values Values set by user.
     * @param type Type of the parameter.
     * @return String to be added to SQL query.
     */
    private String inNotIn(String[] values, String type) {
        StringBuilder SQLQueryPart = new StringBuilder();
        SQLQueryPart.append("(");
        for (int i = 0; i < values.length; i++) {
            if (type.equals("Datum")) {
                SQLQueryPart.append("date ");
            }

            addQuotes(type, SQLQueryPart);
            SQLQueryPart.append(values[i]);
            addQuotes(type, SQLQueryPart);
            if (i < values.length - 1) {
                SQLQueryPart.append(",");
            }
        }
        SQLQueryPart.append(")");

        return SQLQueryPart.toString();
    }

    /**
     * Validate BETWEEN and NOT BETWEEN operator.
     * @param values Values set by user.
     * @param type Type of the parameter.
     * @return String to be added to SQL query.
     */
    private String betweenNotBetween(String[] values, String type) {
        StringBuilder SQLQueryPart = new StringBuilder();

        if (type.equals("Datum")) {
            SQLQueryPart.append("date ");
        }

        addQuotes(type, SQLQueryPart);
        SQLQueryPart.append(values[0]);
        addQuotes(type, SQLQueryPart);

        SQLQueryPart.append(" AND ");

        if (type.equals("Datum")) {
            SQLQueryPart.append("date ");
        }

        addQuotes(type, SQLQueryPart);
        SQLQueryPart.append(values[1]);
        addQuotes(type, SQLQueryPart);

        return SQLQueryPart.toString();
    }

    /**
     * Validate other operators.
     * @param values Values set by user.
     * @param type Type of the parameter.
     * @return String to be added to SQL query.
     */
    private String otherOperators(String[] values, String type) {
        StringBuilder SQLQueryPart = new StringBuilder();

        if (type.equals("Datum")) {
            SQLQueryPart.append("date ");
        }

        addQuotes(type, SQLQueryPart);
        SQLQueryPart.append(values[0]);
        addQuotes(type, SQLQueryPart);

        return SQLQueryPart.toString();
    }

    /**
     * Adds quote to the value.
     * @param type Type Type of the parameter.
     * @param SQLQueryPart Appends quote to the final SQL query.
     */
    private void addQuotes(String type, StringBuilder SQLQueryPart) {
        if (type.equals("Text") || type.equals("Datum")) {
            SQLQueryPart.append("'");
        }
    }
}