--- a/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Sat Jan 23 14:47:59 2021 +0100
+++ b/src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt Fri Apr 02 11:59:14 2021 +0200
@@ -26,8 +26,11 @@
package de.uapcore.lightpit.dao
import de.uapcore.lightpit.entities.*
-import de.uapcore.lightpit.filter.*
import de.uapcore.lightpit.types.WebColor
+import de.uapcore.lightpit.util.*
+import de.uapcore.lightpit.viewmodel.ComponentSummary
+import de.uapcore.lightpit.viewmodel.IssueSummary
+import de.uapcore.lightpit.viewmodel.VersionSummary
import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
@@ -129,15 +132,19 @@
//</editor-fold>
//<editor-fold desc="Version">
+
+ private fun obtainVersion(rs: ResultSet) =
+ Version(rs.getInt("versionid"), rs.getInt("project")).apply {
+ name = rs.getString("name")
+ node = rs.getString("node")
+ ordinal = rs.getInt("ordinal")
+ status = rs.getEnum("status")
+ }
+
private fun selectVersions(stmt: PreparedStatement) = sequence {
stmt.executeQuery().use { rs ->
while (rs.next()) {
- yield(Version(rs.getInt("versionid"), rs.getInt("project")).apply {
- name = rs.getString("name")
- node = rs.getString("node")
- ordinal = rs.getInt("ordinal")
- status = rs.getEnum("status")
- })
+ yield(obtainVersion(rs))
}
}
}
@@ -163,6 +170,36 @@
"""
)
}
+ private val stmtVersionSummaries by lazy {
+ connection.prepareStatement(
+ """
+ with version_map(issueid, versionid, isresolved) as (
+ select issueid, versionid, 1
+ from lpit_issue_resolved_version
+ union
+ select issueid, versionid, 0
+ from lpit_issue_affected_version
+ ),
+ issues as (
+ select versionid, phase, isresolved, count(issueid) as total
+ from lpit_issue
+ join version_map using (issueid)
+ join lpit_issue_phases using (status)
+ group by versionid, phase, isresolved
+ ),
+ summary as (
+ select versionid, phase, isresolved, total
+ from lpit_version v
+ left join issues using (versionid)
+ where v.project = ?
+ )
+ select versionid, project, name, node, ordinal, status, phase, isresolved, total
+ from lpit_version
+ join summary using (versionid)
+ order by ordinal, name
+ """
+ )
+ }
private val stmtVersionByID by lazy {
connection.prepareStatement(
"""${versionQuery}
@@ -199,6 +236,27 @@
return selectVersions(stmtVersions).toList()
}
+ override fun listVersionSummaries(project: Project): List<VersionSummary> {
+ stmtVersionSummaries.setInt(1, project.id)
+ return sequence {
+ stmtVersionSummaries.executeQuery().use { rs ->
+ while (rs.next()) {
+ val versionSummary = VersionSummary(obtainVersion(rs))
+ val phase = rs.getInt("phase")
+ val total = rs.getInt("total")
+ val issueSummary =
+ if (rs.getBoolean("isresolved")) versionSummary.resolvedTotal else versionSummary.reportedTotal
+ when (phase) {
+ 0 -> issueSummary.open = total
+ 1 -> issueSummary.active = total
+ 2 -> issueSummary.done = total
+ }
+ yield(versionSummary)
+ }
+ }
+ }.toList()
+ }
+
override fun findVersion(id: Int): Version? {
stmtVersionByID.setInt(1, id)
return selectVersions(stmtVersionByID).firstOrNull()
@@ -224,21 +282,25 @@
//</editor-fold>
//<editor-fold desc="Component">
+
+ private fun obtainComponent(rs: ResultSet): Component =
+ Component(rs.getInt("id"), rs.getInt("project")).apply {
+ name = rs.getString("name")
+ node = rs.getString("node")
+ color = try {
+ WebColor(rs.getString("color"))
+ } catch (ex: IllegalArgumentException) {
+ WebColor("000000")
+ }
+ ordinal = rs.getInt("ordinal")
+ description = rs.getString("description")
+ lead = selectUserInfo(rs)
+ }
+
private fun selectComponents(stmt: PreparedStatement) = sequence {
stmt.executeQuery().use { rs ->
while (rs.next()) {
- yield(Component(rs.getInt("id"), rs.getInt("project")).apply {
- name = rs.getString("name")
- node = rs.getString("node")
- color = try {
- WebColor(rs.getString("color"))
- } catch (ex: IllegalArgumentException) {
- WebColor("000000")
- }
- ordinal = rs.getInt("ordinal")
- description = rs.getString("description")
- lead = selectUserInfo(rs)
- })
+ yield(obtainComponent(rs))
}
}
}
@@ -272,6 +334,30 @@
"""
)
}
+ private val stmtComponentSummaries by lazy {
+ connection.prepareStatement(
+ """
+ with issues as (
+ select component, phase, count(issueid) as total
+ from lpit_issue
+ join lpit_issue_phases using (status)
+ group by component, phase
+ ),
+ summary as (
+ select c.id, phase, total
+ from lpit_component c
+ left join issues i on c.id = i.component
+ where c.project = ?
+ )
+ select c.id, project, name, node, color, ordinal, description,
+ userid, username, givenname, lastname, mail, phase, total
+ from lpit_component c
+ left join lpit_user on lead = userid
+ join summary s on c.id = s.id
+ order by ordinal, name
+ """
+ )
+ }
private val stmtComponentById by lazy {
connection.prepareStatement(
"""${componentQuery}
@@ -305,6 +391,25 @@
return selectComponents(stmtComponents).toList()
}
+ override fun listComponentSummaries(project: Project): List<ComponentSummary> {
+ stmtComponentSummaries.setInt(1, project.id)
+ return sequence {
+ stmtComponentSummaries.executeQuery().use { rs ->
+ while (rs.next()) {
+ val componentSummary = ComponentSummary(obtainComponent(rs))
+ val phase = rs.getInt("phase")
+ val total = rs.getInt("total")
+ when (phase) {
+ 0 -> componentSummary.issueSummary.open = total
+ 1 -> componentSummary.issueSummary.active = total
+ 2 -> componentSummary.issueSummary.done = total
+ }
+ yield(componentSummary)
+ }
+ }
+ }.toList()
+ }
+
override fun findComponent(id: Int): Component? {
stmtComponentById.setInt(1, id)
return selectComponents(stmtComponentById).firstOrNull()
@@ -471,7 +576,7 @@
node = rs.getString("componentnode")
}
}
- val issue = Issue(rs.getInt("issueid"), proj, comp).apply {
+ val issue = Issue(rs.getInt("issueid"), proj).apply {
component = comp
status = rs.getEnum("status")
category = rs.getEnum("category")
@@ -672,14 +777,15 @@
}
}
- override fun insertIssue(issue: Issue) {
+ override fun insertIssue(issue: Issue): Int {
val col = setIssueFields(stmtInsertIssue, issue)
stmtInsertIssue.setInt(col, issue.project.id)
- stmtInsertIssue.executeQuery().use { rs ->
+ val id = stmtInsertIssue.executeQuery().use { rs ->
rs.next()
- issue.id = rs.getInt(1)
+ rs.getInt(1)
}
insertVersionInfo(issue)
+ return id
}
override fun updateIssue(issue: Issue) {