| 1 | # -*- coding: utf-8 -*- |
|---|
| 2 | # |
|---|
| 3 | # Copyright (c) 2007-2012 Colin Guthrie <trac@colin.guthr.ie> |
|---|
| 4 | # Copyright (c) 2011-2016 Ryan J Ollos <ryan.j.ollos@gmail.com> |
|---|
| 5 | # All rights reserved. |
|---|
| 6 | # |
|---|
| 7 | # This software is licensed as described in the file COPYING, which |
|---|
| 8 | # you should have received as part of this distribution. |
|---|
| 9 | |
|---|
| 10 | # IF YOU ADD A NEW SECTION OF REPORTS, You will need to make |
|---|
| 11 | # sure that section is also added to the all_reports hashtable |
|---|
| 12 | # near the bottom |
|---|
| 13 | |
|---|
| 14 | #Please try to keep this clean" |
|---|
| 15 | |
|---|
| 16 | billing_reports = [ |
|---|
| 17 | { |
|---|
| 18 | "title":"Ticket Work Summary", |
|---|
| 19 | "reportnumber":None, |
|---|
| 20 | "version":12, |
|---|
| 21 | "sql":""" |
|---|
| 22 | SELECT __ticket__ as __group__, __style__, __ticket__, |
|---|
| 23 | newvalue as Work_added, author, time, _ord |
|---|
| 24 | FROM( |
|---|
| 25 | SELECT '' as __style__, author, t.id as __ticket__, |
|---|
| 26 | CAST(newvalue as REAL) as newvalue, ticket_change.time as time, 0 as _ord |
|---|
| 27 | FROM ticket_change |
|---|
| 28 | JOIN ticket t on t.id = ticket_change.ticket |
|---|
| 29 | LEFT JOIN ticket_custom as billable on billable.ticket = t.id |
|---|
| 30 | and billable.name = 'billable' |
|---|
| 31 | WHERE field = 'hours' and |
|---|
| 32 | t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 33 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 34 | AND ticket_change.time >= $STARTDATE |
|---|
| 35 | AND ticket_change.time < $ENDDATE |
|---|
| 36 | |
|---|
| 37 | UNION |
|---|
| 38 | |
|---|
| 39 | SELECT 'background-color:#DFE;' as __style__, |
|---|
| 40 | 'Total work done on the ticket in the selected time period ' as author, |
|---|
| 41 | t.id as __ticket__, sum( CAST(newvalue as real) ) as newvalue, |
|---|
| 42 | NULL as time, 1 as _ord |
|---|
| 43 | FROM ticket_change |
|---|
| 44 | JOIN ticket t on t.id = ticket_change.ticket |
|---|
| 45 | LEFT JOIN ticket_custom as billable on billable.ticket = t.id |
|---|
| 46 | and billable.name = 'billable' |
|---|
| 47 | WHERE field = 'hours' and |
|---|
| 48 | t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 49 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 50 | AND ticket_change.time >= $STARTDATE |
|---|
| 51 | AND ticket_change.time < $ENDDATE |
|---|
| 52 | GROUP By t.id |
|---|
| 53 | ) as tbl |
|---|
| 54 | ORDER BY __ticket__, _ord ASC, time ASC |
|---|
| 55 | |
|---|
| 56 | """ |
|---|
| 57 | },#END Ticket work summary |
|---|
| 58 | { |
|---|
| 59 | "title":"Milestone Work Summary", |
|---|
| 60 | "reportnumber":None, |
|---|
| 61 | "version":11, |
|---|
| 62 | "sql":""" |
|---|
| 63 | |
|---|
| 64 | SELECT |
|---|
| 65 | milestone as __group__, __style__, ticket, summary, newvalue as Work_added, |
|---|
| 66 | time, _ord |
|---|
| 67 | FROM( |
|---|
| 68 | SELECT '' as __style__, t.id as ticket, |
|---|
| 69 | SUM(CAST(newvalue as real)) as newvalue, t.summary as summary, |
|---|
| 70 | MAX(ticket_change.time) as time, t.milestone as milestone, 0 as _ord |
|---|
| 71 | FROM ticket_change |
|---|
| 72 | JOIN ticket t on t.id = ticket_change.ticket |
|---|
| 73 | LEFT JOIN ticket_custom as billable on billable.ticket = t.id |
|---|
| 74 | and billable.name = 'billable' |
|---|
| 75 | WHERE field = 'hours' and |
|---|
| 76 | t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 77 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 78 | AND ticket_change.time >= $STARTDATE |
|---|
| 79 | AND ticket_change.time < $ENDDATE |
|---|
| 80 | GROUP BY t.milestone, t.id, t.summary |
|---|
| 81 | |
|---|
| 82 | UNION |
|---|
| 83 | |
|---|
| 84 | SELECT 'background-color:#DFE;' as __style__, NULL as ticket, |
|---|
| 85 | sum(CAST(newvalue as real)) as newvalue, 'Total work done' as summary, |
|---|
| 86 | NULL as time, t.milestone as milestone, 1 as _ord |
|---|
| 87 | FROM ticket_change |
|---|
| 88 | JOIN ticket t on t.id = ticket_change.ticket |
|---|
| 89 | LEFT JOIN ticket_custom as billable on billable.ticket = t.id |
|---|
| 90 | and billable.name = 'billable' |
|---|
| 91 | WHERE field = 'hours' and |
|---|
| 92 | t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 93 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 94 | AND ticket_change.time >= $STARTDATE |
|---|
| 95 | AND ticket_change.time < $ENDDATE |
|---|
| 96 | GROUP By t.milestone |
|---|
| 97 | ) as tbl |
|---|
| 98 | ORDER BY milestone, _ord ASC, ticket, time |
|---|
| 99 | |
|---|
| 100 | |
|---|
| 101 | |
|---|
| 102 | """ |
|---|
| 103 | },#END Milestone work summary |
|---|
| 104 | |
|---|
| 105 | { |
|---|
| 106 | "title":"Developer Work Summary", |
|---|
| 107 | "reportnumber":None, |
|---|
| 108 | "version":11, |
|---|
| 109 | "sql":""" |
|---|
| 110 | SELECT author as __group__,__style__, ticket, |
|---|
| 111 | newvalue as Work_added, time as time, _ord |
|---|
| 112 | FROM( |
|---|
| 113 | SELECT '' as __style__, author, cast(t.id as text) as ticket, |
|---|
| 114 | CAST(newvalue as REAL) as newvalue, ticket_change.time as time, 0 as _ord |
|---|
| 115 | FROM ticket_change |
|---|
| 116 | JOIN ticket t on t.id = ticket_change.ticket |
|---|
| 117 | LEFT JOIN ticket_custom as billable on billable.ticket = t.id |
|---|
| 118 | and billable.name = 'billable' |
|---|
| 119 | WHERE field = 'hours' and |
|---|
| 120 | t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 121 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 122 | AND ticket_change.time >= $STARTDATE |
|---|
| 123 | AND ticket_change.time < $ENDDATE |
|---|
| 124 | |
|---|
| 125 | UNION |
|---|
| 126 | |
|---|
| 127 | SELECT 'background-color:#DFE;' as __style__, author, NULL as ticket, |
|---|
| 128 | sum(CAST(newvalue as real)) as newvalue, NULL as time, 1 as _ord |
|---|
| 129 | FROM ticket_change |
|---|
| 130 | JOIN ticket t on t.id = ticket_change.ticket |
|---|
| 131 | LEFT JOIN ticket_custom as billable on billable.ticket = t.id |
|---|
| 132 | and billable.name = 'billable' |
|---|
| 133 | WHERE field = 'hours' and |
|---|
| 134 | t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 135 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 136 | AND ticket_change.time >= $STARTDATE |
|---|
| 137 | AND ticket_change.time < $ENDDATE |
|---|
| 138 | GROUP By author |
|---|
| 139 | ) as tbl |
|---|
| 140 | ORDER BY author, _ord ASC, time |
|---|
| 141 | |
|---|
| 142 | """ |
|---|
| 143 | },#END Hours Per Developer |
|---|
| 144 | ] |
|---|
| 145 | ticket_hours_reports = [ |
|---|
| 146 | { |
|---|
| 147 | "title": "Ticket Hours", |
|---|
| 148 | "reportnumber": None, |
|---|
| 149 | "version":8, |
|---|
| 150 | "sql": """ |
|---|
| 151 | SELECT __color__, __style__, ticket, summary, component ,version, severity, |
|---|
| 152 | milestone, status, owner, Estimated_work, Total_work, billable,_ord |
|---|
| 153 | FROM ( |
|---|
| 154 | SELECT p.value AS __color__, |
|---|
| 155 | '' as __style__, |
|---|
| 156 | t.id AS ticket, summary AS summary, -- ## Break line here |
|---|
| 157 | component,version, severity, milestone, status, owner, |
|---|
| 158 | CAST(EstimatedHours.value as REAL) as Estimated_work, |
|---|
| 159 | CAST(totalhours.value as REAL) as Total_work, |
|---|
| 160 | CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable, |
|---|
| 161 | time AS created, changetime AS modified, -- ## Dates are formatted |
|---|
| 162 | description AS _description_, -- ## Uses a full row |
|---|
| 163 | changetime AS _changetime, |
|---|
| 164 | reporter AS _reporter |
|---|
| 165 | ,0 as _ord |
|---|
| 166 | |
|---|
| 167 | FROM ticket as t |
|---|
| 168 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 169 | |
|---|
| 170 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 171 | AND EstimatedHours.Ticket = t.Id |
|---|
| 172 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 173 | AND totalhours.Ticket = t.Id |
|---|
| 174 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 175 | AND billable.Ticket = t.Id |
|---|
| 176 | |
|---|
| 177 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 178 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 179 | |
|---|
| 180 | |
|---|
| 181 | UNION |
|---|
| 182 | |
|---|
| 183 | SELECT '1' AS __color__, |
|---|
| 184 | 'background-color:#DFE;' as __style__, |
|---|
| 185 | NULL as ticket, 'Total' AS summary, |
|---|
| 186 | NULL as component,NULL as version, NULL as severity, NULL as milestone, NULL as status, NULL as owner, |
|---|
| 187 | SUM(CAST(EstimatedHours.value as real)) as Estimated_work, |
|---|
| 188 | SUM(CAST(totalhours.value as real)) as Total_work, |
|---|
| 189 | NULL as billable, |
|---|
| 190 | NULL as created, NULL as modified, -- ## Dates are formatted |
|---|
| 191 | |
|---|
| 192 | NULL AS _description_, |
|---|
| 193 | NULL AS _changetime, |
|---|
| 194 | NULL AS _reporter |
|---|
| 195 | ,1 as _ord |
|---|
| 196 | FROM ticket as t |
|---|
| 197 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 198 | |
|---|
| 199 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 200 | AND EstimatedHours.Ticket = t.Id |
|---|
| 201 | |
|---|
| 202 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 203 | AND totalhours.Ticket = t.Id |
|---|
| 204 | |
|---|
| 205 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 206 | AND billable.Ticket = t.Id |
|---|
| 207 | |
|---|
| 208 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 209 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 210 | ) as tbl |
|---|
| 211 | ORDER BY _ord ASC, ticket |
|---|
| 212 | """ |
|---|
| 213 | }, |
|---|
| 214 | #END Ticket Hours |
|---|
| 215 | { |
|---|
| 216 | "title": "Ticket Hours with Description", |
|---|
| 217 | "reportnumber": None, |
|---|
| 218 | "version":9, |
|---|
| 219 | "sql": """ |
|---|
| 220 | SELECT __color__, __style__, ticket, summary, component ,version, severity, |
|---|
| 221 | milestone, status, owner, Estimated_work, Total_work, billable |
|---|
| 222 | --,created, modified, -- ## Dates are formatted |
|---|
| 223 | ,_description_ |
|---|
| 224 | -- _changetime, |
|---|
| 225 | -- _reporter |
|---|
| 226 | ,_ord |
|---|
| 227 | |
|---|
| 228 | FROM ( |
|---|
| 229 | SELECT p.value AS __color__, |
|---|
| 230 | '' as __style__, |
|---|
| 231 | t.id AS ticket, summary AS summary, -- ## Break line here |
|---|
| 232 | component,version, severity, milestone, status, owner, |
|---|
| 233 | CAST(EstimatedHours.value as REAL) as Estimated_work, |
|---|
| 234 | CAST(totalhours.value as REAL) as Total_work, |
|---|
| 235 | CASE WHEN billable.value = 1 THEN 'Y' |
|---|
| 236 | else 'N' |
|---|
| 237 | END as billable, |
|---|
| 238 | time AS created, changetime AS modified, -- ## Dates are formatted |
|---|
| 239 | description AS _description_, -- ## Uses a full row |
|---|
| 240 | changetime AS _changetime, |
|---|
| 241 | reporter AS _reporter |
|---|
| 242 | ,0 as _ord |
|---|
| 243 | |
|---|
| 244 | FROM ticket as t |
|---|
| 245 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 246 | |
|---|
| 247 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 248 | AND EstimatedHours.Ticket = t.Id |
|---|
| 249 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 250 | AND totalhours.Ticket = t.Id |
|---|
| 251 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 252 | AND billable.Ticket = t.Id |
|---|
| 253 | |
|---|
| 254 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 255 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 256 | |
|---|
| 257 | |
|---|
| 258 | UNION |
|---|
| 259 | |
|---|
| 260 | SELECT '1' AS __color__, |
|---|
| 261 | 'background-color:#DFE;' as __style__, |
|---|
| 262 | NULL as ticket, 'Total' AS summary, |
|---|
| 263 | NULL as component,NULL as version, NULL as severity, NULL as milestone, NULL as status, NULL as owner, |
|---|
| 264 | SUM(CAST(EstimatedHours.value as real)) as Estimated_work, |
|---|
| 265 | SUM(CAST(totalhours.value as real)) as Total_work, |
|---|
| 266 | NULL as billable, |
|---|
| 267 | NULL as created, NULL as modified, -- ## Dates are formatted |
|---|
| 268 | |
|---|
| 269 | NULL AS _description_, |
|---|
| 270 | NULL AS _changetime, |
|---|
| 271 | NULL AS _reporter |
|---|
| 272 | ,1 as _ord |
|---|
| 273 | FROM ticket as t |
|---|
| 274 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 275 | |
|---|
| 276 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 277 | AND EstimatedHours.Ticket = t.Id |
|---|
| 278 | |
|---|
| 279 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 280 | AND totalhours.Ticket = t.Id |
|---|
| 281 | |
|---|
| 282 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 283 | AND billable.Ticket = t.Id |
|---|
| 284 | |
|---|
| 285 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 286 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 287 | ) as tbl |
|---|
| 288 | ORDER BY _ord ASC, ticket |
|---|
| 289 | """ |
|---|
| 290 | }, |
|---|
| 291 | #END Ticket Hours |
|---|
| 292 | |
|---|
| 293 | { |
|---|
| 294 | "title":"Ticket Hours Grouped By Component", |
|---|
| 295 | "reportnumber":None, |
|---|
| 296 | "version":8, |
|---|
| 297 | "sql": """ |
|---|
| 298 | SELECT __color__, __group__, __style__, ticket, summary, __component__ ,version, |
|---|
| 299 | severity, milestone, status, owner, Estimated_work, total_work, billable, |
|---|
| 300 | _ord |
|---|
| 301 | |
|---|
| 302 | FROM ( |
|---|
| 303 | SELECT p.value AS __color__, |
|---|
| 304 | t.component AS __group__, |
|---|
| 305 | '' as __style__, |
|---|
| 306 | t.id AS ticket, summary AS summary, -- ## Break line here |
|---|
| 307 | component as __component__,version, severity, milestone, status, owner, |
|---|
| 308 | CAST(EstimatedHours.value as REAL) as Estimated_work, |
|---|
| 309 | CAST(totalhours.value as REAL) as Total_work, |
|---|
| 310 | CASE WHEN billable.value = 1 THEN 'Y' |
|---|
| 311 | else 'N' |
|---|
| 312 | END as billable, |
|---|
| 313 | time AS created, changetime AS modified, -- ## Dates are formatted |
|---|
| 314 | description AS _description_, -- ## Uses a full row |
|---|
| 315 | changetime AS _changetime, |
|---|
| 316 | reporter AS _reporter |
|---|
| 317 | ,0 as _ord |
|---|
| 318 | |
|---|
| 319 | FROM ticket as t |
|---|
| 320 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 321 | |
|---|
| 322 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 323 | AND EstimatedHours.Ticket = t.Id |
|---|
| 324 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 325 | AND totalhours.Ticket = t.Id |
|---|
| 326 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 327 | AND billable.Ticket = t.Id |
|---|
| 328 | |
|---|
| 329 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 330 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 331 | |
|---|
| 332 | |
|---|
| 333 | UNION |
|---|
| 334 | |
|---|
| 335 | SELECT '1' AS __color__, |
|---|
| 336 | t.component AS __group__, |
|---|
| 337 | 'background-color:#DFE;' as __style__, |
|---|
| 338 | NULL as ticket, 'Total work' AS summary, |
|---|
| 339 | t.component as __component__, NULL as version, NULL as severity, |
|---|
| 340 | NULL as milestone, NULL as status, |
|---|
| 341 | NULL as owner, |
|---|
| 342 | SUM(CAST(EstimatedHours.value as real)) as Estimated_work, |
|---|
| 343 | SUM(CAST(totalhours.value as real)) as Total_work, |
|---|
| 344 | NULL as billable, |
|---|
| 345 | NULL as created, |
|---|
| 346 | NULL as modified, -- ## Dates are formatted |
|---|
| 347 | |
|---|
| 348 | NULL AS _description_, |
|---|
| 349 | NULL AS _changetime, |
|---|
| 350 | NULL AS _reporter |
|---|
| 351 | ,1 as _ord |
|---|
| 352 | FROM ticket as t |
|---|
| 353 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 354 | |
|---|
| 355 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 356 | AND EstimatedHours.Ticket = t.Id |
|---|
| 357 | |
|---|
| 358 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 359 | AND totalhours.Ticket = t.Id |
|---|
| 360 | |
|---|
| 361 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 362 | AND billable.Ticket = t.Id |
|---|
| 363 | |
|---|
| 364 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 365 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 366 | GROUP BY t.component |
|---|
| 367 | ) as tbl |
|---|
| 368 | ORDER BY __component__, _ord ASC,ticket |
|---|
| 369 | """ |
|---|
| 370 | }, |
|---|
| 371 | # END Ticket Hours GROUPED BY COMPONENT |
|---|
| 372 | |
|---|
| 373 | { |
|---|
| 374 | "title":"Ticket Hours Grouped By Component with Description", |
|---|
| 375 | "reportnumber":None, |
|---|
| 376 | "version":7, |
|---|
| 377 | "sql": """ |
|---|
| 378 | SELECT __color__, __group__, __style__, ticket, summary, __component__ , |
|---|
| 379 | version, severity, milestone, status, owner, Estimated_work, Total_work, |
|---|
| 380 | billable, _description_, _ord |
|---|
| 381 | |
|---|
| 382 | FROM ( |
|---|
| 383 | SELECT p.value AS __color__, |
|---|
| 384 | t.component AS __group__, |
|---|
| 385 | '' as __style__, |
|---|
| 386 | t.id AS ticket, summary AS summary, -- ## Break line here |
|---|
| 387 | component as __component__, version, severity, milestone, status, owner, |
|---|
| 388 | CAST(EstimatedHours.value as REAL) as Estimated_work, |
|---|
| 389 | CAST(totalhours.value as REAL) as Total_work, |
|---|
| 390 | CASE WHEN billable.value = 1 THEN 'Y' else 'N' END as billable, |
|---|
| 391 | time AS created, changetime AS modified, -- ## Dates are formatted |
|---|
| 392 | description AS _description_, -- ## Uses a full row |
|---|
| 393 | changetime AS _changetime, |
|---|
| 394 | reporter AS _reporter |
|---|
| 395 | ,0 as _ord |
|---|
| 396 | |
|---|
| 397 | FROM ticket as t |
|---|
| 398 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 399 | |
|---|
| 400 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 401 | AND EstimatedHours.Ticket = t.Id |
|---|
| 402 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 403 | AND totalhours.Ticket = t.Id |
|---|
| 404 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 405 | AND billable.Ticket = t.Id |
|---|
| 406 | |
|---|
| 407 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 408 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 409 | |
|---|
| 410 | |
|---|
| 411 | UNION |
|---|
| 412 | |
|---|
| 413 | SELECT '1' AS __color__, |
|---|
| 414 | t.component AS __group__, |
|---|
| 415 | 'background-color:#DFE;' as __style__, |
|---|
| 416 | NULL as ticket, 'Total work' AS summary, |
|---|
| 417 | t.component as __component__, NULL as version, NULL as severity, |
|---|
| 418 | NULL as milestone, NULL as status, NULL as owner, |
|---|
| 419 | SUM(CAST(EstimatedHours.value as real)) as Estimated_work, |
|---|
| 420 | SUM(CAST(totalhours.value as real)) as Total_work, |
|---|
| 421 | NULL as billable, |
|---|
| 422 | NULL as created, NULL as modified, -- ## Dates are formatted |
|---|
| 423 | |
|---|
| 424 | NULL AS _description_, |
|---|
| 425 | NULL AS _changetime, |
|---|
| 426 | NULL AS _reporter |
|---|
| 427 | ,1 as _ord |
|---|
| 428 | FROM ticket as t |
|---|
| 429 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 430 | |
|---|
| 431 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 432 | AND EstimatedHours.Ticket = t.Id |
|---|
| 433 | |
|---|
| 434 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 435 | AND totalhours.Ticket = t.Id |
|---|
| 436 | |
|---|
| 437 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 438 | AND billable.Ticket = t.Id |
|---|
| 439 | |
|---|
| 440 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 441 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 442 | GROUP BY t.component |
|---|
| 443 | ) as tbl |
|---|
| 444 | ORDER BY __component__, _ord ASC, ticket |
|---|
| 445 | """ |
|---|
| 446 | }, |
|---|
| 447 | # END Ticket Hours Grouped BY Component with Description |
|---|
| 448 | { |
|---|
| 449 | "title":"Ticket Hours Grouped By Milestone", |
|---|
| 450 | "reportnumber":None, |
|---|
| 451 | "version":8, |
|---|
| 452 | "sql": """ |
|---|
| 453 | SELECT __color__, __group__, __style__, ticket, summary, component ,version, |
|---|
| 454 | severity, __milestone__, status, owner, Estimated_work, Total_work, billable, |
|---|
| 455 | _ord |
|---|
| 456 | |
|---|
| 457 | FROM ( |
|---|
| 458 | SELECT p.value AS __color__, |
|---|
| 459 | t.milestone AS __group__, |
|---|
| 460 | '' as __style__, |
|---|
| 461 | t.id AS ticket, summary AS summary, -- ## Break line here |
|---|
| 462 | component,version, severity, milestone as __milestone__, status, owner, |
|---|
| 463 | CAST(EstimatedHours.value as REAL) as Estimated_work, |
|---|
| 464 | CAST(totalhours.value as REAL) as Total_work, |
|---|
| 465 | CASE WHEN billable.value = 1 THEN 'Y' |
|---|
| 466 | else 'N' |
|---|
| 467 | END as billable, |
|---|
| 468 | time AS created, changetime AS modified, -- ## Dates are formatted |
|---|
| 469 | description AS _description_, -- ## Uses a full row |
|---|
| 470 | changetime AS _changetime, |
|---|
| 471 | reporter AS _reporter, 0 as _ord |
|---|
| 472 | |
|---|
| 473 | FROM ticket as t |
|---|
| 474 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 475 | |
|---|
| 476 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 477 | AND EstimatedHours.Ticket = t.Id |
|---|
| 478 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 479 | AND totalhours.Ticket = t.Id |
|---|
| 480 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 481 | AND billable.Ticket = t.Id |
|---|
| 482 | |
|---|
| 483 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 484 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 485 | |
|---|
| 486 | |
|---|
| 487 | UNION |
|---|
| 488 | |
|---|
| 489 | SELECT '1' AS __color__, |
|---|
| 490 | t.milestone AS __group__, |
|---|
| 491 | 'background-color:#DFE;' as __style__, |
|---|
| 492 | NULL as ticket, 'Total work' AS summary, |
|---|
| 493 | NULL as component,NULL as version, NULL as severity, |
|---|
| 494 | t.milestone as __milestone__, NULL as status, NULL as owner, |
|---|
| 495 | SUM(CAST(EstimatedHours.value as real)) as Estimated_work, |
|---|
| 496 | SUM(CAST(totalhours.value as real)) as Total_work, |
|---|
| 497 | NULL as billable, |
|---|
| 498 | NULL as created, NULL as modified, -- ## Dates are formatted |
|---|
| 499 | |
|---|
| 500 | NULL AS _description_, |
|---|
| 501 | NULL AS _changetime, |
|---|
| 502 | NULL AS _reporter |
|---|
| 503 | ,1 as _ord |
|---|
| 504 | FROM ticket as t |
|---|
| 505 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 506 | |
|---|
| 507 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 508 | AND EstimatedHours.Ticket = t.Id |
|---|
| 509 | |
|---|
| 510 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 511 | AND totalhours.Ticket = t.Id |
|---|
| 512 | |
|---|
| 513 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 514 | AND billable.Ticket = t.Id |
|---|
| 515 | |
|---|
| 516 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 517 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 518 | GROUP BY t.milestone |
|---|
| 519 | ) as tbl |
|---|
| 520 | ORDER BY __milestone__, _ord ASC, ticket |
|---|
| 521 | """ |
|---|
| 522 | }, |
|---|
| 523 | #END Ticket Hours Grouped By MileStone |
|---|
| 524 | { |
|---|
| 525 | "title":"Ticket Hours Grouped By MileStone with Description", |
|---|
| 526 | "reportnumber":None, |
|---|
| 527 | "version":8, |
|---|
| 528 | "sql": """ |
|---|
| 529 | SELECT __color__, __group__, __style__, ticket, summary, component ,version, severity, |
|---|
| 530 | __milestone__, status, owner, Estimated_work, Total_work, billable, |
|---|
| 531 | _description_, _ord |
|---|
| 532 | |
|---|
| 533 | FROM ( |
|---|
| 534 | SELECT p.value AS __color__, |
|---|
| 535 | t.milestone AS __group__, |
|---|
| 536 | '' as __style__, |
|---|
| 537 | t.id AS ticket, summary AS summary, -- ## Break line here |
|---|
| 538 | component,version, severity, milestone as __milestone__, status, owner, |
|---|
| 539 | CAST(EstimatedHours.value as REAL) as Estimated_work, |
|---|
| 540 | CAST(totalhours.value as REAL) as Total_work, |
|---|
| 541 | CASE WHEN billable.value = 1 THEN 'Y' |
|---|
| 542 | else 'N' |
|---|
| 543 | END as billable, |
|---|
| 544 | time AS created, changetime AS modified, -- ## Dates are formatted |
|---|
| 545 | description AS _description_, -- ## Uses a full row |
|---|
| 546 | changetime AS _changetime, |
|---|
| 547 | reporter AS _reporter |
|---|
| 548 | ,0 as _ord |
|---|
| 549 | |
|---|
| 550 | FROM ticket as t |
|---|
| 551 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 552 | |
|---|
| 553 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 554 | AND EstimatedHours.Ticket = t.Id |
|---|
| 555 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 556 | AND totalhours.Ticket = t.Id |
|---|
| 557 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 558 | AND billable.Ticket = t.Id |
|---|
| 559 | |
|---|
| 560 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 561 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 562 | |
|---|
| 563 | |
|---|
| 564 | UNION |
|---|
| 565 | |
|---|
| 566 | SELECT '1' AS __color__, |
|---|
| 567 | t.milestone AS __group__, |
|---|
| 568 | 'background-color:#DFE;' as __style__, |
|---|
| 569 | NULL as ticket, 'Total work' AS summary, |
|---|
| 570 | NULL as component,NULL as version, NULL as severity, |
|---|
| 571 | t.milestone as __milestone__, |
|---|
| 572 | NULL as status, NULL as owner, |
|---|
| 573 | SUM(CAST(EstimatedHours.value as real)) as Estimated_work, |
|---|
| 574 | SUM(CAST(totalhours.value as real)) as Total_work, |
|---|
| 575 | NULL as billable, |
|---|
| 576 | NULL as created, NULL as modified, -- ## Dates are formatted |
|---|
| 577 | NULL AS _description_, |
|---|
| 578 | NULL AS _changetime, |
|---|
| 579 | NULL AS _reporter, 1 as _ord |
|---|
| 580 | FROM ticket as t |
|---|
| 581 | JOIN enum as p ON p.name=t.priority AND p.type='priority' |
|---|
| 582 | |
|---|
| 583 | LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours' |
|---|
| 584 | AND EstimatedHours.Ticket = t.Id |
|---|
| 585 | |
|---|
| 586 | LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours' |
|---|
| 587 | AND totalhours.Ticket = t.Id |
|---|
| 588 | |
|---|
| 589 | LEFT JOIN ticket_custom as billable ON billable.name='billable' |
|---|
| 590 | AND billable.Ticket = t.Id |
|---|
| 591 | |
|---|
| 592 | WHERE t.status IN ($NEW, $ASSIGNED, $REOPENED, $CLOSED) |
|---|
| 593 | AND billable.value in ($BILLABLE, $UNBILLABLE) |
|---|
| 594 | GROUP BY t.milestone |
|---|
| 595 | ) as tbl |
|---|
| 596 | ORDER BY __milestone__, _ord ASC, ticket |
|---|
| 597 | """ |
|---|
| 598 | } |
|---|
| 599 | #END Ticket Hours Grouped By MileStone with Description |
|---|
| 600 | ] |
|---|
| 601 | |
|---|
| 602 | all_reports = [ |
|---|
| 603 | {"title": "Billing Reports", |
|---|
| 604 | "reports": billing_reports}, |
|---|
| 605 | {"title": "Ticket/Hour Reports", |
|---|
| 606 | "reports": ticket_hours_reports} |
|---|
| 607 | ] |
|---|
