Mercurial > hg > LightPIT / file diff

--- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGVersionDao.java	Sat May 23 14:13:09 2020 +0200
+++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGVersionDao.java	Sun May 24 15:30:43 2020 +0200
@@ -29,9 +29,7 @@
 package de.uapcore.lightpit.dao.postgres;
 
 import de.uapcore.lightpit.dao.VersionDao;
-import de.uapcore.lightpit.entities.Project;
-import de.uapcore.lightpit.entities.Version;
-import de.uapcore.lightpit.entities.VersionStatus;
+import de.uapcore.lightpit.entities.*;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
@@ -44,13 +42,14 @@
 public final class PGVersionDao implements VersionDao {
 
     private final PreparedStatement insert, update, list, find;
+    private final PreparedStatement issuesAffected, issuesScheduled, issuesResolved;
 
     public PGVersionDao(Connection connection) throws SQLException {
         list = connection.prepareStatement(
                 "select versionid, project, name, ordinal, status " +
                         "from lpit_version " +
                         "where project = ? " +
-                        "order by ordinal, lower(name)");
+                        "order by ordinal desc, lower(name) desc");
 
         find = connection.prepareStatement(
                 "select versionid, project, name, ordinal, status " +
@@ -63,6 +62,28 @@
         update = connection.prepareStatement(
                 "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?"
         );
+
+        issuesAffected = connection.prepareStatement(
+                "select category, status, count(*) as issuecount " +
+                        "from lpit_issue_affected_version " +
+                        "join lpit_issue using (issueid) " +
+                        "where versionid = ? " +
+                        "group by category, status"
+        );
+        issuesScheduled = connection.prepareStatement(
+                "select category, status, count(*) as issuecount " +
+                        "from lpit_issue_scheduled_version " +
+                        "join lpit_issue using (issueid) " +
+                        "where versionid = ? " +
+                        "group by category, status"
+        );
+        issuesResolved = connection.prepareStatement(
+                "select category, status, count(*) as issuecount " +
+                        "from lpit_issue_resolved_version " +
+                        "join lpit_issue using (issueid) " +
+                        "where versionid = ? " +
+                        "group by category, status"
+        );
     }
 
     private Version mapColumns(ResultSet result) throws SQLException {
@@ -74,6 +95,20 @@
         return version;
     }
 
+    private VersionStatistics versionStatistics(Version version, PreparedStatement stmt) throws SQLException {
+        stmt.setInt(1, version.getId());
+        final var result = stmt.executeQuery();
+        final var stats = new VersionStatistics(version);
+        while (result.next()) {
+            stats.setIssueCount(
+                    IssueCategory.valueOf(result.getString("category")),
+                    IssueStatus.valueOf(result.getString("status")),
+                    result.getInt("issuecount")
+            );
+        }
+        return stats;
+    }
+
     @Override
     public void save(Version instance) throws SQLException {
         Objects.requireNonNull(instance.getName());
@@ -121,4 +156,19 @@
             }
         }
     }
+
+    @Override
+    public VersionStatistics statsOpenedIssues(Version version) throws SQLException {
+        return versionStatistics(version, issuesAffected);
+    }
+
+    @Override
+    public VersionStatistics statsScheduledIssues(Version version) throws SQLException {
+        return versionStatistics(version, issuesScheduled);
+    }
+
+    @Override
+    public VersionStatistics statsResolvedIssues(Version version) throws SQLException {
+        return versionStatistics(version, issuesResolved);
+    }
 }