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

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.Configuration;
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 configuration - configuration from which table will be created.
     * @return List of contingencyTableRow.
     */
    @Override
    public List<ContingencyTableRow> getContingencyTableRow(Configuration configuration) {
        long startTime = System.nanoTime();

        Map<String, TableColumn> tableColumns = this.databaseInterface.executeQueryAndReturnTableColumns(
                generateFullSQLQuery(configuration.getAssembly().getSQLQuery(), configuration.getParametersInConfiguration())
        );

        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<>(configuration.getParametersInConfiguration());

        // 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, true);
        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);
    }

    private void initializeContingencyTableDataLists(List<NameUserName> rowNames, List<NameUserName> columnNames,
                                                     List<ValueFunction> valueFunctions, List<ParameterInConfiguration> parametersInConfiguration) {
        for (ParameterInConfiguration parameterInConfiguration : parametersInConfiguration) {
            if (parameterInConfiguration.getLocation() != null && parameterInConfiguration.getLocation().getName().equals("Řádek")) {
                rowNames.add(new NameUserName(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
                        parameterInConfiguration.getColumnName()));
            } else if (parameterInConfiguration.getLocation() != null && parameterInConfiguration.getLocation().getName().equals("Sloupec")) {
                columnNames.add(new NameUserName(parameterInConfiguration.getParameter().getNameOfSelect().toUpperCase(),
                        parameterInConfiguration.getColumnName()));
            } else if (parameterInConfiguration.getLocation() != 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", ""));
        }
    }

    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();
    }

    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;
    }

    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;
    }

    private String likeNotLike(String[] values, String type) {
        StringBuilder SQLQueryPart = new StringBuilder();
        addQuotes(type, SQLQueryPart);
        SQLQueryPart.append(values[0]);
        addQuotes(type, SQLQueryPart);

        return SQLQueryPart.toString();
    }

    private String inNotIn(String[] values, String type) {
        StringBuilder SQLQueryPart = new StringBuilder();
        SQLQueryPart.append("(");
        for (int i = 0; i < values.length; i++) {
            addQuotes(type, SQLQueryPart);
            SQLQueryPart.append(values[i]);
            addQuotes(type, SQLQueryPart);
            if (i < values.length - 1) {
                SQLQueryPart.append(",");
            }
        }
        SQLQueryPart.append(")");

        return SQLQueryPart.toString();
    }

    private String betweenNotBetween(String[] values, String type) {
        StringBuilder SQLQueryPart = new StringBuilder();

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

        SQLQueryPart.append(" AND ");

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

        return SQLQueryPart.toString();
    }

    private String otherOperators(String[] values, String type) {
        StringBuilder SQLQueryPart = new StringBuilder();

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

        return SQLQueryPart.toString();
    }

    private void addQuotes(String type, StringBuilder SQLQueryPart) {
        if (type.equals("Text") || type.equals("Datum")) {
            SQLQueryPart.append("'");
        }
    }
}