1.1 --- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGVersionDao.java Sat May 23 14:13:09 2020 +0200
1.2 +++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGVersionDao.java Sun May 24 15:30:43 2020 +0200
1.3 @@ -29,9 +29,7 @@
1.4 package de.uapcore.lightpit.dao.postgres;
1.5
1.6 import de.uapcore.lightpit.dao.VersionDao;
1.7 -import de.uapcore.lightpit.entities.Project;
1.8 -import de.uapcore.lightpit.entities.Version;
1.9 -import de.uapcore.lightpit.entities.VersionStatus;
1.10 +import de.uapcore.lightpit.entities.*;
1.11
1.12 import java.sql.Connection;
1.13 import java.sql.PreparedStatement;
1.14 @@ -44,13 +42,14 @@
1.15 public final class PGVersionDao implements VersionDao {
1.16
1.17 private final PreparedStatement insert, update, list, find;
1.18 + private final PreparedStatement issuesAffected, issuesScheduled, issuesResolved;
1.19
1.20 public PGVersionDao(Connection connection) throws SQLException {
1.21 list = connection.prepareStatement(
1.22 "select versionid, project, name, ordinal, status " +
1.23 "from lpit_version " +
1.24 "where project = ? " +
1.25 - "order by ordinal, lower(name)");
1.26 + "order by ordinal desc, lower(name) desc");
1.27
1.28 find = connection.prepareStatement(
1.29 "select versionid, project, name, ordinal, status " +
1.30 @@ -63,6 +62,28 @@
1.31 update = connection.prepareStatement(
1.32 "update lpit_version set name = ?, ordinal = ?, status = ?::version_status where versionid = ?"
1.33 );
1.34 +
1.35 + issuesAffected = connection.prepareStatement(
1.36 + "select category, status, count(*) as issuecount " +
1.37 + "from lpit_issue_affected_version " +
1.38 + "join lpit_issue using (issueid) " +
1.39 + "where versionid = ? " +
1.40 + "group by category, status"
1.41 + );
1.42 + issuesScheduled = connection.prepareStatement(
1.43 + "select category, status, count(*) as issuecount " +
1.44 + "from lpit_issue_scheduled_version " +
1.45 + "join lpit_issue using (issueid) " +
1.46 + "where versionid = ? " +
1.47 + "group by category, status"
1.48 + );
1.49 + issuesResolved = connection.prepareStatement(
1.50 + "select category, status, count(*) as issuecount " +
1.51 + "from lpit_issue_resolved_version " +
1.52 + "join lpit_issue using (issueid) " +
1.53 + "where versionid = ? " +
1.54 + "group by category, status"
1.55 + );
1.56 }
1.57
1.58 private Version mapColumns(ResultSet result) throws SQLException {
1.59 @@ -74,6 +95,20 @@
1.60 return version;
1.61 }
1.62
1.63 + private VersionStatistics versionStatistics(Version version, PreparedStatement stmt) throws SQLException {
1.64 + stmt.setInt(1, version.getId());
1.65 + final var result = stmt.executeQuery();
1.66 + final var stats = new VersionStatistics(version);
1.67 + while (result.next()) {
1.68 + stats.setIssueCount(
1.69 + IssueCategory.valueOf(result.getString("category")),
1.70 + IssueStatus.valueOf(result.getString("status")),
1.71 + result.getInt("issuecount")
1.72 + );
1.73 + }
1.74 + return stats;
1.75 + }
1.76 +
1.77 @Override
1.78 public void save(Version instance) throws SQLException {
1.79 Objects.requireNonNull(instance.getName());
1.80 @@ -121,4 +156,19 @@
1.81 }
1.82 }
1.83 }
1.84 +
1.85 + @Override
1.86 + public VersionStatistics statsOpenedIssues(Version version) throws SQLException {
1.87 + return versionStatistics(version, issuesAffected);
1.88 + }
1.89 +
1.90 + @Override
1.91 + public VersionStatistics statsScheduledIssues(Version version) throws SQLException {
1.92 + return versionStatistics(version, issuesScheduled);
1.93 + }
1.94 +
1.95 + @Override
1.96 + public VersionStatistics statsResolvedIssues(Version version) throws SQLException {
1.97 + return versionStatistics(version, issuesResolved);
1.98 + }
1.99 }
