Solve 1st test case

2 files changed, 88 insertions, 62 deletions

diff --git a/src/main/java/com/mavlushechka/a1qa/utils/Database.java b/src/main/java/com/mavlushechka/a1qa/utils/Database.java
new file mode 100644
index 0000000..1926618
--- /dev/null
+++ b/src/main/java/com/mavlushechka/a1qa/utils/Database.java
@@ -0,0 +1,88 @@
+package com.mavlushechka.a1qa.utils;
+
+import com.mavlushechka.a1qa.models.User;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.time.LocalDate;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+
+public class Database {
+
+    private static final String name = JsonParser.parseData("config", "database.name");
+    private static final Connection connection;
+    private static final User user = new User(JsonParser.parseData("config", "database.user.name"),
+            JsonParser.parseData("config", "database.user.password"));
+
+
+    static {
+        try {
+            Class.forName(JsonParser.parseData("config", "database.driver"));
+            connection = DriverManager.getConnection(JsonParser.parseData("config", "database.url") + "/" + name, user.name(), user.password());
+        } catch (SQLException | ClassNotFoundException e) {
+            throw new RuntimeException(e);
+        }
+    }
+
+
+    private Database() {
+    }
+
+    public static List<String> getTests() {
+        return executeQuery("SELECT project.name, test.name, TIMESTAMPDIFF(MICROSECOND, test.start_time, test.end_time) as min_working_time"
+                + " FROM project INNER JOIN test ON project.id = test.project_id ORDER BY project.name, test.name;",
+                Map.of("project.name", String.class,"test.name", String.class,"min_working_time", Long.class));
+    }
+
+    public static List<String> getTestsCount() {
+        return executeQuery("SELECT project.name, (SELECT COUNT(*) FROM test WHERE test.project_id = project.id) AS `tests_count` FROM project;",
+                Map.of("project.name", String.class, "tests_count", Long.class));
+    }
+
+    public static List<String> getTestsByLowerDate(LocalDate localDate) {
+        return executeQuery("SELECT project.name, test.name, test.start_time FROM project JOIN test ON project.id = test.project_id"
+                + " WHERE test.start_time >= '%s' ORDER BY project.name, test.name;".formatted(localDate),
+                Map.of("project.name", String.class, "test.name", String.class, "start_time", LocalDate.class));
+    }
+
+    public static List<String> getBrowserTestsCount() {
+        return executeQuery("SELECT test.browser, COUNT(*) as count FROM test WHERE test.browser = 'firefox'"
+                + " UNION"
+                + " SELECT test.browser, COUNT(*) as count FROM test WHERE test.browser = 'chrome';",
+                Map.of("browser", String.class, "count", Long.class));
+    }
+
+    private static List<String> executeQuery(String sql, Map<String, Class<?>> expectedData) {
+        ArrayList<String> foundData = new ArrayList<>();
+
+        try (PreparedStatement preparedStatement = connection.prepareStatement(sql);
+             ResultSet resultSet = preparedStatement.executeQuery()) {
+            while (resultSet.next()) {
+                StringBuilder dataStringRepresentationBuilder = new StringBuilder();
+
+                dataStringRepresentationBuilder.append("{");
+                for (String expectedDataKey : expectedData.keySet()) {
+                    dataStringRepresentationBuilder
+                            .append(expectedDataKey)
+                            .append("=")
+                            .append(resultSet.getObject(expectedDataKey, expectedData.get(expectedDataKey)))
+                            .append(",");
+                }
+                dataStringRepresentationBuilder.deleteCharAt(dataStringRepresentationBuilder.toString().length() - 1);
+                dataStringRepresentationBuilder.append("}");
+
+                foundData.add(dataStringRepresentationBuilder.toString());
+            }
+        } catch (SQLException sqlException) {
+            sqlException.printStackTrace();
+        }
+
+        return foundData;
+    }
+
+}
diff --git a/src/main/java/com/mavlushechka/a1qa/utils/VkApiUtils.java b/src/main/java/com/mavlushechka/a1qa/utils/VkApiUtils.java
deleted file mode 100644
index 845ecc7..0000000
--- a/src/main/java/com/mavlushechka/a1qa/utils/VkApiUtils.java
+++ /dev/null
@@ -1,62 +0,0 @@
-package com.mavlushechka.a1qa.utils;
-
-import com.mavlushechka.a1qa.constants.Method;
-import com.mavlushechka.a1qa.models.Liker;
-import com.mavlushechka.a1qa.models.Post;
-import com.mavlushechka.a1qa.models.User;
-import org.json.JSONObject;
-
-import java.io.IOException;
-
-public class VkApiUtils {
-
-    private final static double apiVersion = 5.131;
-    private final static String url = "https://api.vk.com/method/%s?v=" + apiVersion + "&%s&access_token=" + JsonParser.parseData("testData", "vkontakte.account.token");
-
-
-    public static User getCurrentUser() throws IOException {
-        return JsonParser.convertToObject(
-                new JSONObject(UrlConnectionManager.get(url.formatted(Method.USERS_GET.name, "")))
-                        .getJSONArray("response").get(0).toString(), User.class
-        );
-    }
-
-    public static int createPost(String message) throws IOException {
-        return new JSONObject(UrlConnectionManager.get(url.formatted(Method.WALL_POST.name, "message=" + message)))
-                .getJSONObject("response").getInt("post_id");
-    }
-
-    public static void editPost(Post post, Post editedPost) throws IOException {
-        UrlConnectionManager.get(
-                url.formatted(
-                        Method.WALL_EDIT.name,
-                        "post_id=" + post.id() + "&message=" + editedPost.message() + "&attachments=" + editedPost.attachment()
-                )
-        );
-    }
-
-    public static void deletePost(Post post) throws IOException {
-        UrlConnectionManager.get(url.formatted(Method.WALL_DELETE.name, "post_id=" + post.id()));
-    }
-
-    public static int createComment(int postId, String message) throws IOException {
-        return new JSONObject(UrlConnectionManager.get(url.formatted(Method.WALL_CREATE_COMMENT.name, "post_id=" + postId +  "&message=" + message)))
-                .getJSONObject("response").getInt("comment_id");
-    }
-
-    public static boolean containsLike(Post post, User user) throws IOException {
-        Object[] users = JsonParser.convertArray(new JSONObject(UrlConnectionManager.get(
-                    url.formatted(Method.WALL_GET_LIKES.name, "post_id=" + post.id())
-                )).getJSONObject("response").getJSONArray("users").toString(),
-                Liker.class
-        );
-
-        for (Object userObject : users) {
-            if (((Liker) userObject).getUid() == user.getId()) {
-                return true;
-            }
-        }
-        return false;
-    }
-
-}