Mercurial > hg > LightPIT / file diff

--- a/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java	Sat May 30 18:12:38 2020 +0200
+++ b/src/main/java/de/uapcore/lightpit/dao/postgres/PGIssueDao.java	Mon Jun 01 14:46:58 2020 +0200
@@ -43,7 +43,7 @@
 
 public final class PGIssueDao implements IssueDao {
 
-    private final PreparedStatement insert, update, list, find;
+    private final PreparedStatement insert, update, list, listForVersion, find;
     private final PreparedStatement affectedVersions, scheduledVersions, resolvedVersions;
     private final PreparedStatement clearAffected, clearScheduled, clearResolved;
     private final PreparedStatement insertAffected, insertScheduled, insertResolved;
@@ -56,7 +56,24 @@
                         "from lpit_issue i " +
                         "left join lpit_project p on project = projectid " +
                         "left join lpit_user on userid = assignee " +
-                        "where project = ? ");
+                        "where project = ? "+
+                        "order by eta asc, updated desc");
+
+        listForVersion = connection.prepareStatement(
+                "with issue_version as ( "+
+                        "select issueid, versionid from lpit_issue_affected_version union "+
+                        "select issueid, versionid from lpit_issue_scheduled_version union "+
+                        "select issueid, versionid from lpit_issue_resolved_version) "+
+                        "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
+                        "userid, username, givenname, lastname, mail, " +
+                        "created, updated, eta " +
+                        "from lpit_issue i " +
+                        "join issue_version using (issueid) "+
+                        "left join lpit_project p on project = projectid " +
+                        "left join lpit_user on userid = assignee " +
+                        "where versionid = ? "+
+                        "order by eta asc, updated desc"
+        );
 
         find = connection.prepareStatement(
                 "select issueid, project, p.name as projectname, status, category, subject, i.description, " +
@@ -121,7 +138,8 @@
     private Issue mapColumns(ResultSet result) throws SQLException {
         final var project = new Project(result.getInt("project"));
         project.setName(result.getString("projectname"));
-        final var issue = new Issue(result.getInt("issueid"), project);
+        final var issue = new Issue(result.getInt("issueid"));
+        issue.setProject(project);
         issue.setStatus(IssueStatus.valueOf(result.getString("status")));
         issue.setCategory(IssueCategory.valueOf(result.getString("category")));
         issue.setSubject(result.getString("subject"));
@@ -133,8 +151,8 @@
         return issue;
     }
 
-    private Version mapVersion(ResultSet result, Project project) throws SQLException {
-        final var version = new Version(result.getInt("versionid"), project);
+    private Version mapVersion(ResultSet result) throws SQLException {
+        final var version = new Version(result.getInt("versionid"));
         version.setName(result.getString("name"));
         version.setOrdinal(result.getInt("ordinal"));
         version.setStatus(VersionStatus.valueOf(result.getString("status")));
@@ -203,11 +221,10 @@
         }
     }
 
-    @Override
-    public List<Issue> list(Project project) throws SQLException {
-        list.setInt(1, project.getId());
+    private List<Issue> list(PreparedStatement query, int arg) throws SQLException {
+        query.setInt(1, arg);
         List<Issue> issues = new ArrayList<>();
-        try (var result = list.executeQuery()) {
+        try (var result = query.executeQuery()) {
             while (result.next()) {
                 issues.add(mapColumns(result));
             }
@@ -216,6 +233,16 @@
     }
 
     @Override
+    public List<Issue> list(Project project) throws SQLException {
+        return list(list, project.getId());
+    }
+
+    @Override
+    public List<Issue> list(Version version) throws SQLException {
+        return list(listForVersion, version.getId());
+    }
+
+    @Override
     public Issue find(int id) throws SQLException {
         find.setInt(1, id);
         try (var result = find.executeQuery()) {
@@ -232,7 +259,7 @@
         List<Version> versions = new ArrayList<>();
         try (var result = stmt.executeQuery()) {
             while (result.next()) {
-                versions.add(mapVersion(result, issue.getProject()));
+                versions.add(mapVersion(result));
             }
         }
         return versions;