Revize c20f287b

re #8113 re #7885 all functions implemented, contingency table generation improved; re #8154 value filtering added; no comments

src/main/java/vldc/aswi/service/SqlQueryManagerImpl.java

@@ -4 +4 @@
 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.Configuration;
 import vldc.aswi.domain.Function;
 import vldc.aswi.domain.parameter.ParameterInConfiguration;
@@ -14 +13 @@
 import vldc.aswi.model.table.contingencyTable.ContingencyTableRow;
 import vldc.aswi.utils.Converter;
 
-import javax.sql.DataSource;
 import java.util.*;
 import java.util.concurrent.TimeUnit;
 
@@ -23 +21 @@
  */
 @Service
 @Slf4j
-public class SqlQueryManagerImpl implements SqlQueryManager{
+public class SqlQueryManagerImpl implements SqlQueryManager {
 
-    /** Autowired databaseInterface component. */
+    /**
+     * 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(
-                configuration.getAssembly().getSQLQuery()
+                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<>();
@@ -49 +52 @@
         // sort parameters in configuration to have them in order set by user
         Collections.sort(configuration.getParametersInConfiguration());
 
-        // TODO: 28.05.2020 map would be better
-        for(ParameterInConfiguration parameterInConfiguration : configuration.getParametersInConfiguration()) {
-            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(),
-                            function.getName().toUpperCase()));
-                }
-            }
-        }
+        initializeContingencyTableDataLists(rowNames, columnNames, valueFunctions, configuration.getParametersInConfiguration());
 
-        Converter converter = new Converter();
-        return converter.convertTableColumnsToContingencyTableRows(tableColumns, rowNames, columnNames, valueFunctions);
+        Converter converter = new Converter(tableColumns, rowNames, columnNames, valueFunctions);
+        return converter.convertTableColumnsToContingencyTableRows();
     }
 
     /**
      * Validate given SQL query.
+     *
      * @param sqlQuery - Validated SQL query.
      * @return true if SQL query is ok, false if not.
      */
@@ -83 +71 @@
 
     /**
      * 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()));
+                }
+            }
+        }
+    }
+
+    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.getOperator() != null && !parameterInConfiguration.getOperator().getName().equals("zadny") &&
+                    parameterInConfiguration.getOperatorValue() != null && !parameterInConfiguration.getOperatorValue().equals("")) {
+                String[] values = parameterInConfiguration.getOperatorValue().split(";");
+                if (!isWhereDefined) {
+                    finalSQLQuery.append(" WHERE ");
+                    isWhereDefined = true;
+                } else {
+                    finalSQLQuery.append(" AND ");
+                }
+
+                finalSQLQuery.append(parameterInConfiguration.getParameter().getNameOfSelect());
+                finalSQLQuery.append(" ");
+                finalSQLQuery.append(parameterInConfiguration.getOperator().getName());
+                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 finalSQLQuery.toString();
+    }
+
+    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("'");
+        }
+    }
 }