LightPIT: comparison src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt

-:61669abf277f
+:e8eecee6aadf
 */
 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
 class PostgresDataAccessObject(private val connection: Connection) : DataAccessObject {
 stmtUpdateUser.execute()
 }
 //</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 {
+yield(obtainVersion(rs))
-name = rs.getString("name")
-node = rs.getString("node")
-ordinal = rs.getInt("ordinal")
-status = rs.getEnum("status")
-})
 }
 }
 }
 private fun setVersionFields(stmt: PreparedStatement, obj: Version): Int {
 where project = ?
 order by ordinal desc, lower(name) desc
 """
 )
 }
+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}
 where versionid = ?
 """
 }
 override fun listVersions(project: Project): List<Version> {
 stmtVersions.setInt(1, project.id)
 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()
 stmtUpdateVersion.execute()
 }
 //</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 {
+yield(obtainComponent(rs))
-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 setComponentFields(stmt: PreparedStatement, obj: Component): Int {
 where project = ?
 order by ordinal, lower(name)
 """
 )
 }
+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}
 where id = ?
 """
 }
 override fun listComponents(project: Project): List<Component> {
 stmtComponents.setInt(1, project.id)
 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()
 Component(it, proj.id).apply {
 name = rs.getString("componentname")
 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")
 subject = rs.getString("subject")
 description = rs.getString("description")
 stmtInsertResolvedVersion.setInt(2, it.id)
 stmtInsertResolvedVersion.execute()
 }
 }
-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) {
 val col = setIssueFields(stmtUpdateIssue, issue)
 stmtUpdateIssue.setInt(col, issue.id)

Mercurial > hg > LightPIT / file comparison

comparison: src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt

src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt