comparison: src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
src/main/kotlin/de/uapcore/lightpit/dao/PostgresDataAccessObject.kt
- changeset 190
- a83f1ab56898
- parent 189
- f7de8158b41c
- child 203
- 7d5b16379768
equal
deleted
inserted
replaced
| 189:f7de8158b41c | 190:a83f1ab56898 |
|---|---|
| 127 setInt(4, id) | 127 setInt(4, id) |
| 128 } | 128 } |
| 129 executeUpdate() | 129 executeUpdate() |
| 130 } | 130 } |
| 131 } | 131 } |
| 132 //</editor-fold> | 132 //</editor-fold> |
| 133 | 133 |
| 134 //<editor-fold desc="Version"> | 134 //<editor-fold desc="Version"> |
| 135 //language=SQL | 135 //language=SQL |
| 136 private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version" | 136 private val versionQuery = "select versionid, project, name, node, ordinal, status from lpit_version" |
| 137 | 137 |
| 151 | 151 |
| 152 override fun listVersionSummaries(project: Project): List<VersionSummary> = | 152 override fun listVersionSummaries(project: Project): List<VersionSummary> = |
| 153 withStatement( | 153 withStatement( |
| 154 """ | 154 """ |
| 155 with version_map(issueid, versionid, isresolved) as ( | 155 with version_map(issueid, versionid, isresolved) as ( |
| 156 select issueid, versionid, 1 | 156 select issueid, versionid, true |
| 157 from lpit_issue_resolved_version | 157 from lpit_issue_resolved_version |
| 158 union | 158 union |
| 159 select issueid, versionid, 0 | 159 select issueid, versionid, false |
| 160 from lpit_issue_affected_version | 160 from lpit_issue_affected_version |
| 161 ), | 161 ), |
| 162 issues as ( | 162 issues as ( |
| 163 select versionid, phase, isresolved, count(issueid) as total | 163 select versionid, phase, isresolved, count(issueid) as total |
| 164 from lpit_issue | 164 from lpit_issue |
| 168 ), | 168 ), |
| 169 summary as ( | 169 summary as ( |
| 170 select versionid, phase, isresolved, total | 170 select versionid, phase, isresolved, total |
| 171 from lpit_version v | 171 from lpit_version v |
| 172 left join issues using (versionid) | 172 left join issues using (versionid) |
| 173 where v.project = ? | |
| 174 ) | 173 ) |
| 175 select versionid, project, name, node, ordinal, status, phase, isresolved, total | 174 select v.versionid, project, name, node, ordinal, status, |
| 176 from lpit_version | 175 ro.total as resolved_open, ra.total as resolved_active, rd.total as resolved_done, |
| 177 join summary using (versionid) | 176 ao.total as affected_open, aa.total as affected_active, ad.total as affected_done |
| 177 from lpit_version v | |
| 178 left join summary ro on ro.versionid = v.versionid and ro.phase = 0 and ro.isresolved | |
| 179 left join summary ra on ra.versionid = v.versionid and ra.phase = 1 and ra.isresolved | |
| 180 left join summary rd on rd.versionid = v.versionid and rd.phase = 2 and rd.isresolved | |
| 181 left join summary ao on ao.versionid = v.versionid and ao.phase = 0 and not ao.isresolved | |
| 182 left join summary aa on aa.versionid = v.versionid and aa.phase = 1 and not aa.isresolved | |
| 183 left join summary ad on ad.versionid = v.versionid and ad.phase = 2 and not ad.isresolved | |
| 184 where v.project = ? | |
| 178 order by ordinal, name | 185 order by ordinal, name |
| 179 """.trimIndent() | 186 """.trimIndent() |
| 180 ) { | 187 ) { |
| 181 setInt(1, project.id) | 188 setInt(1, project.id) |
| 182 executeQuery().use { rs -> | 189 queryAll { rs -> |
| 183 sequence { | 190 VersionSummary(rs.extractVersion()).apply { |
| 184 // TODO: fix bug: this extractor is not grouping the results | 191 reportedTotal.open = rs.getInt("affected_open") |
| 185 val versionSummary = VersionSummary(rs.extractVersion()) | 192 reportedTotal.active = rs.getInt("affected_active") |
| 186 val phase = rs.getInt("phase") | 193 reportedTotal.done = rs.getInt("affected_done") |
| 187 val total = rs.getInt("total") | 194 resolvedTotal.open = rs.getInt("resolved_open") |
| 188 val issueSummary = | 195 resolvedTotal.active = rs.getInt("resolved_active") |
| 189 if (rs.getBoolean("isresolved")) versionSummary.resolvedTotal else versionSummary.reportedTotal | 196 resolvedTotal.done = rs.getInt("resolved_done") |
| 190 when (phase) { | 197 } |
| 191 0 -> issueSummary.open = total | |
| 192 1 -> issueSummary.active = total | |
| 193 2 -> issueSummary.done = total | |
| 194 } | |
| 195 yield(versionSummary) | |
| 196 }.toList() | |
| 197 } | 198 } |
| 198 } | 199 } |
| 199 | 200 |
| 200 override fun findVersion(id: Int): Version? = | 201 override fun findVersion(id: Int): Version? = |
| 201 withStatement("$versionQuery where versionid = ?") { | 202 withStatement("$versionQuery where versionid = ?") { |
| 234 setInt(5, id) | 235 setInt(5, id) |
| 235 } | 236 } |
| 236 executeUpdate() | 237 executeUpdate() |
| 237 } | 238 } |
| 238 } | 239 } |
| 239 //</editor-fold> | 240 //</editor-fold> |
| 240 | 241 |
| 241 //<editor-fold desc="Component"> | 242 //<editor-fold desc="Component"> |
| 242 //language=SQL | 243 //language=SQL |
| 243 private val componentQuery = | 244 private val componentQuery = |
| 244 """ | 245 """ |
| 292 ), | 293 ), |
| 293 summary as ( | 294 summary as ( |
| 294 select c.id, phase, total | 295 select c.id, phase, total |
| 295 from lpit_component c | 296 from lpit_component c |
| 296 left join issues i on c.id = i.component | 297 left join issues i on c.id = i.component |
| 297 where c.project = ? | |
| 298 ) | 298 ) |
| 299 select c.id, project, name, node, color, ordinal, description, | 299 select c.id, project, name, node, color, ordinal, description, |
| 300 userid, username, givenname, lastname, mail, phase, total | 300 userid, username, givenname, lastname, mail, |
| 301 open.total as open, active.total as active, done.total as done | |
| 301 from lpit_component c | 302 from lpit_component c |
| 302 left join lpit_user on lead = userid | 303 left join lpit_user on lead = userid |
| 303 join summary s on c.id = s.id | 304 left join summary open on c.id = open.id and open.phase = 0 |
| 305 left join summary active on c.id = active.id and active.phase = 1 | |
| 306 left join summary done on c.id = done.id and done.phase = 2 | |
| 307 where c.project = ? | |
| 304 order by ordinal, name | 308 order by ordinal, name |
| 305 """.trimIndent() | 309 """.trimIndent() |
| 306 ) { | 310 ) { |
| 307 setInt(1, project.id) | 311 setInt(1, project.id) |
| 308 executeQuery().use { rs -> | 312 queryAll { rs -> |
| 309 // TODO: fix bug: this extractor is not grouping the results | 313 ComponentSummary(rs.extractComponent()).apply { |
| 310 sequence { | 314 issueSummary.open = rs.getInt("open") |
| 311 val componentSummary = ComponentSummary(rs.extractComponent()).also { | 315 issueSummary.active = rs.getInt("active") |
| 312 val phase = rs.getInt("phase") | 316 issueSummary.done = rs.getInt("done") |
| 313 val total = rs.getInt("total") | 317 } |
| 314 when (phase) { | |
| 315 0 -> it.issueSummary.open = total | |
| 316 1 -> it.issueSummary.active = total | |
| 317 2 -> it.issueSummary.done = total | |
| 318 } | |
| 319 } | |
| 320 yield(componentSummary) | |
| 321 }.toList() | |
| 322 } | 318 } |
| 323 } | 319 } |
| 324 | 320 |
| 325 override fun findComponent(id: Int): Component? = | 321 override fun findComponent(id: Int): Component? = |
| 326 withStatement("$componentQuery where id = ?") { | 322 withStatement("$componentQuery where id = ?") { |
