source: timingandestimationplugin/branches/trac1.0-Permissions/timingandestimationplugin/reports.py

Last change on this file was 17096, checked in by Russ Tyndall, 5 years ago

Fix hour summing in reports to not round partial hours

version 1.5.8(b)

re #13405
File size: 25.7 KB
Line 
1# IF YOU ADD A NEW SECTION OF REPORTS, You will need to make
2# sure that section is also added to the all_reports hashtable
3# near the bottom
4
5#Please try to keep this clean"
6
7billing_reports = [
8        {
9    "uuid":"b24f08c0-d41f-4c63-93a5-25e18a8513c2",
10    "title":"Ticket Work Summary",
11    "version":21,
12    "sql":"""
13SELECT __ticket__ as __group__, __style__, ticket,
14newvalue as Work_added, author, time as datetime, _ord
15FROM(
16  SELECT '' as __style__, author,
17  t.summary as __ticket__,
18  t.id as ticket,
19  CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
20    ELSE CAST( newvalue AS DECIMAL(10,2) ) END AS newvalue,
21  ticket_change.time as time, 0 as _ord
22  FROM ticket_change
23  JOIN ticket t on t.id = ticket_change.ticket
24  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
25    and billable.name = 'billable'
26  WHERE field = 'hours' and
27    t.status IN (#STATUSES#)
28      AND billable.value in ($BILLABLE, $UNBILLABLE)
29      AND ticket_change.time >= $STARTDATE
30      AND ticket_change.time < $ENDDATE
31
32  UNION
33
34  SELECT 'background-color:#DFE;' as __style__,
35    'Total work done on the ticket in the selected time period ' as author,
36    t.summary as __ticket__,
37    t.id as ticket,
38  SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
39         ELSE CAST( newvalue AS DECIMAL(10,2) ) END ) as newvalue,
40    NULL as time, 1 as _ord
41  FROM ticket_change
42  JOIN ticket t on t.id = ticket_change.ticket
43  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
44    and billable.name = 'billable'
45  WHERE field = 'hours' and
46    t.status IN (#STATUSES#)
47      AND billable.value in ($BILLABLE, $UNBILLABLE)
48      AND ticket_change.time >= $STARTDATE
49      AND ticket_change.time < $ENDDATE
50  GROUP By t.id, t.summary
51)  as tbl
52ORDER BY __ticket__, _ord ASC, time ASC
53
54    """
55    },#END Ticket work summary
56        {
57    "uuid":"af13564f-0e36-4a17-96c0-632dc68d8d14",
58    "title":"Milestone Work Summary",
59    "version":18,
60    "sql":"""
61
62SELECT
63  milestone as __group__, __style__,  ticket, summary, newvalue as Work_added,
64  time  as datetime, _ord
65FROM(
66  SELECT '' as __style__, t.id as ticket,
67    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
68         ELSE CAST( newvalue AS DECIMAL(10,2) ) END) as newvalue, t.summary as summary,
69    MAX(ticket_change.time) as time, t.milestone as milestone, 0 as _ord
70  FROM ticket_change
71  JOIN ticket t on t.id = ticket_change.ticket
72  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
73    and billable.name = 'billable'
74  WHERE field = 'hours' and
75    t.status IN (#STATUSES#)
76      AND billable.value in ($BILLABLE, $UNBILLABLE)
77      AND ticket_change.time >= $STARTDATE
78      AND ticket_change.time < $ENDDATE
79  GROUP BY t.milestone, t.id, t.summary
80
81  UNION
82
83  SELECT 'background-color:#DFE;' as __style__, 0 as ticket,
84    sum( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
85         ELSE CAST( newvalue AS DECIMAL(10,2) ) END) 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 (#STATUSES#)
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
98ORDER BY milestone,  _ord ASC, ticket, time
99
100
101
102    """
103    },#END Milestone work summary
104
105    {
106    "uuid":"7bd4b0ce-da6d-4b11-8be3-07e65b540d99",
107    "title":"Developer Work Summary",
108    "version":18,
109    "sql":"""
110SELECT author as __group__,__style__, ticket, summary,
111  newvalue as Work_added, time as datetime, _ord
112FROM(
113  SELECT '' as __style__, author, t.id  as ticket,
114    t.summary as summary,
115    CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
116         ELSE CAST( newvalue AS DECIMAL(10,2) ) END as newvalue,
117    ticket_change.time as time, 0 as _ord
118  FROM ticket_change
119  JOIN ticket t on t.id = ticket_change.ticket
120  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
121    and billable.name = 'billable'
122  WHERE field = 'hours' and
123    t.status IN (#STATUSES#)
124      AND billable.value in ($BILLABLE, $UNBILLABLE)
125      AND ticket_change.time >= $STARTDATE
126      AND ticket_change.time < $ENDDATE
127
128  UNION
129
130  SELECT 'background-color:#DFE;' as __style__, author, 0 as ticket,
131    Null as summary,
132    SUM( CASE WHEN newvalue = '' OR newvalue IS NULL THEN 0
133         ELSE CAST( newvalue AS DECIMAL(10,2) ) END) as newvalue,
134    NULL as time, 1 as _ord
135  FROM ticket_change
136  JOIN ticket t on t.id = ticket_change.ticket
137  LEFT JOIN ticket_custom as billable on billable.ticket = t.id
138    and billable.name = 'billable'
139  WHERE field = 'hours' and
140    t.status IN (#STATUSES#)
141      AND billable.value in ($BILLABLE, $UNBILLABLE)
142      AND ticket_change.time >= $STARTDATE
143      AND ticket_change.time < $ENDDATE
144  GROUP By author
145)  as tbl
146ORDER BY author,  _ord ASC, time
147
148    """
149    },#END Hours Per Developer
150]
151th_version =17
152ticket_hours_reports = [
153{
154    "uuid":"8d785cdb-dcf5-43c9-b2a6-216997b0011a",
155    "title": "Ticket Hours",
156    "version":th_version,
157    "sql": """
158SELECT __color__, __style__, ticket, summary, component ,version, severity,
159  milestone, status, owner, Estimated_work, Total_work, billable,_ord
160FROM (
161  SELECT p.value AS __color__,
162    '' as __style__,
163    t.id AS ticket, summary AS summary,             -- ## Break line here
164    component,version, severity, milestone, status, owner,
165    CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
166      ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END as Estimated_work,
167    CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
168      ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END as Total_work,
169    CASE WHEN billable.value = '1' THEN 'Y' else 'N' END as billable,
170    time AS created, changetime AS modified,         -- ## Dates are formatted
171    description AS _description_,                    -- ## Uses a full row
172    changetime AS _changetime,
173    reporter AS _reporter
174    ,0 as _ord
175
176    FROM ticket as t
177    LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
178
179  LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
180        AND EstimatedHours.Ticket = t.Id
181  LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
182        AND totalhours.Ticket = t.Id
183  LEFT JOIN ticket_custom as billable ON billable.name='billable'
184        AND billable.Ticket = t.Id
185
186    WHERE t.status IN (#STATUSES#)
187      AND billable.value in ($BILLABLE, $UNBILLABLE)
188
189
190  UNION
191
192  SELECT '1' AS __color__,
193         'background-color:#DFE;' as __style__,
194         0 as ticket, 'Total' AS summary,
195         NULL as component,NULL as version, NULL as severity, NULL as  milestone,
196         'Time Remaining: ' as status,
197         CAST(
198       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
199         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) -
200       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
201         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END)
202         AS CHAR(512))  as owner,
203         SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
204      ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) as Estimated_work,
205         SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
206      ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END) as Total_work,
207         NULL as billable,
208         NULL as created, NULL as modified,         -- ## Dates are formatted
209
210         NULL AS _description_,
211         NULL AS _changetime,
212         NULL AS _reporter
213         ,1 as _ord
214    FROM ticket as t
215    LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
216
217  LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
218        AND EstimatedHours.Ticket = t.Id
219
220  LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
221        AND totalhours.Ticket = t.Id
222
223  LEFT JOIN ticket_custom as billable ON billable.name='billable'
224        AND billable.Ticket = t.Id
225
226    WHERE t.status IN (#STATUSES#)
227      AND billable.value in ($BILLABLE, $UNBILLABLE)
228)  as tbl
229ORDER BY  _ord ASC, ticket
230    """
231    },
232#END Ticket Hours
233{
234    "uuid":"71e7c36d-e512-4d0b-b499-087d4d20ff0b",
235    "title": "Ticket Hours with Description",
236    "version":th_version,
237    "sql": """
238SELECT __color__,  __style__,  ticket, summary, component ,version, severity,
239 milestone, status, owner, Estimated_work, Total_work, billable
240-- ## ,created,  modified,         -- ## Dates are formatted
241,_description_
242-- ## _changetime,
243-- ## _reporter
244,_ord
245
246FROM (
247SELECT p.value AS __color__,
248       '' as __style__,
249       t.id AS ticket, summary AS summary,             -- ## Break line here
250       component,version, severity, milestone, status, owner,
251       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
252      ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END as Estimated_work,
253       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
254      ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END as Total_work,
255       CASE WHEN billable.value = '1' THEN 'Y'
256            else 'N'
257       END as billable,
258       time AS created, changetime AS modified,         -- ## Dates are formatted
259       description AS _description_,                    -- ## Uses a full row
260       changetime AS _changetime,
261       reporter AS _reporter
262       ,0 as _ord
263
264  FROM ticket as t
265  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
266
267LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
268      AND EstimatedHours.Ticket = t.Id
269LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
270      AND totalhours.Ticket = t.Id
271LEFT JOIN ticket_custom as billable ON billable.name='billable'
272      AND billable.Ticket = t.Id
273
274  WHERE t.status IN (#STATUSES#)
275    AND billable.value in ($BILLABLE, $UNBILLABLE)
276
277
278UNION
279
280SELECT '1' AS __color__,
281       'background-color:#DFE;' as __style__,
282       0 as ticket, 'Total' AS summary,
283       NULL as component,NULL as version, NULL as severity, NULL as  milestone,
284       'Time Remaining: ' as status,
285       CAST(
286       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
287         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) -
288       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
289         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END)
290         AS CHAR(512))  as owner,
291       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
292         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) as Estimated_work,
293       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
294         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END) as Total_work,
295       NULL as billable,
296       NULL as created, NULL as modified,         -- ## Dates are formatted
297
298       NULL AS _description_,
299       NULL AS _changetime,
300       NULL AS _reporter
301       ,1 as _ord
302  FROM ticket as t
303  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
304
305LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
306      AND EstimatedHours.Ticket = t.Id
307
308LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
309      AND totalhours.Ticket = t.Id
310
311LEFT JOIN ticket_custom as billable ON billable.name='billable'
312      AND billable.Ticket = t.Id
313
314  WHERE t.status IN (#STATUSES#)
315    AND billable.value in ($BILLABLE, $UNBILLABLE)
316)  as tbl
317ORDER BY _ord ASC, ticket
318    """
319    },
320#END Ticket Hours
321
322    {
323    "uuid":"5f33b102-e6a6-47e8-976c-ac7a6794a909",
324    "title":"Ticket Hours Grouped By Component",
325    "version":th_version,
326    "sql": """
327SELECT __color__, __group__, __style__, ticket, summary, __component__ ,version,
328  severity, milestone, status, owner, Estimated_work, total_work, billable,
329  _ord
330
331FROM (
332SELECT p.value AS __color__,
333       t.component AS __group__,
334       '' as __style__,
335       t.id AS ticket, summary AS summary,             -- ## Break line here
336       component as __component__,version, severity, milestone, status, owner,
337       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
338         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END as Estimated_work,
339       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
340         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END as Total_work,
341       CASE WHEN billable.value = '1' THEN 'Y'
342            else 'N'
343       END as billable,
344       time AS created, changetime AS modified,         -- ## Dates are formatted
345       description AS _description_,                    -- ## Uses a full row
346       changetime AS _changetime,
347       reporter AS _reporter
348       ,0 as _ord
349
350  FROM ticket as t
351  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
352
353LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
354      AND EstimatedHours.Ticket = t.Id
355LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
356      AND totalhours.Ticket = t.Id
357LEFT JOIN ticket_custom as billable ON billable.name='billable'
358      AND billable.Ticket = t.Id
359
360  WHERE t.status IN (#STATUSES#)
361    AND billable.value in ($BILLABLE, $UNBILLABLE)
362
363
364UNION
365
366SELECT '1' AS __color__,
367       t.component AS __group__,
368       'background-color:#DFE;' as __style__,
369       0 as ticket, 'Total work' AS summary,
370       t.component as __component__, NULL as version, NULL as severity,
371       NULL as  milestone, 'Time Remaining: ' as status,
372       CAST(
373       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
374         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) -
375       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
376         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END)
377         AS CHAR(512))  as owner,
378       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
379         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) as Estimated_work,
380       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
381         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END) as Total_work,
382       NULL as billable,
383       NULL as created,
384       NULL as modified,         -- ## Dates are formatted
385
386       NULL AS _description_,
387       NULL AS _changetime,
388       NULL AS _reporter
389       ,1 as _ord
390  FROM ticket as t
391  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
392
393LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
394      AND EstimatedHours.Ticket = t.Id
395
396LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
397      AND totalhours.Ticket = t.Id
398
399LEFT JOIN ticket_custom as billable ON billable.name='billable'
400      AND billable.Ticket = t.Id
401
402  WHERE t.status IN (#STATUSES#)
403    AND billable.value in ($BILLABLE, $UNBILLABLE)
404  GROUP BY t.component
405)  as tbl
406ORDER BY __component__, _ord ASC,ticket
407    """
408    },
409# END Ticket Hours  GROUPED BY COMPONENT
410
411    {
412    "uuid":"7816f034-a174-4a94-aed6-358fb648b2fc",
413    "title":"Ticket Hours Grouped By Component with Description",
414    "version":th_version,
415    "sql": """
416SELECT __color__, __group__, __style__,  ticket, summary, __component__ ,
417  version, severity, milestone, status, owner, Estimated_work, Total_work,
418  billable, _description_, _ord
419
420FROM (
421SELECT p.value AS __color__,
422       t.component AS __group__,
423       '' as __style__,
424       t.id AS ticket, summary AS summary,             -- ## Break line here
425       component as __component__, version, severity, milestone, status, owner,
426       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
427         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END as Estimated_work,
428       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
429         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END as Total_work,
430       CASE WHEN billable.value = '1' THEN 'Y' else 'N' END as billable,
431       time AS created, changetime AS modified,         -- ## Dates are formatted
432       description AS _description_,                    -- ## Uses a full row
433       changetime AS _changetime,
434       reporter AS _reporter
435       ,0 as _ord
436
437  FROM ticket as t
438  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
439
440LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
441      AND EstimatedHours.Ticket = t.Id
442LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
443      AND totalhours.Ticket = t.Id
444LEFT JOIN ticket_custom as billable ON billable.name='billable'
445      AND billable.Ticket = t.Id
446
447  WHERE t.status IN (#STATUSES#)
448    AND billable.value in ($BILLABLE, $UNBILLABLE)
449
450
451UNION
452
453SELECT '1' AS __color__,
454       t.component AS __group__,
455       'background-color:#DFE;' as __style__,
456       0 as ticket, 'Total work' AS summary,
457       t.component as __component__, NULL as version, NULL as severity,
458       NULL as  milestone, 'Time Remaining: ' as status,
459       CAST(
460       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
461         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) -
462       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
463         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END)
464         AS CHAR(512))  as owner,
465       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
466         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) as Estimated_work,
467       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
468         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END) as Total_work,
469       NULL as billable,
470       NULL as created, NULL as modified,         -- ## Dates are formatted
471
472       NULL AS _description_,
473       NULL AS _changetime,
474       NULL AS _reporter
475       ,1 as _ord
476  FROM ticket as t
477  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
478
479LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
480      AND EstimatedHours.Ticket = t.Id
481
482LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
483      AND totalhours.Ticket = t.Id
484
485LEFT JOIN ticket_custom as billable ON billable.name='billable'
486      AND billable.Ticket = t.Id
487
488  WHERE t.status IN (#STATUSES#)
489    AND billable.value in ($BILLABLE, $UNBILLABLE)
490  GROUP BY t.component
491)  as tbl
492ORDER BY __component__, _ord ASC, ticket
493    """
494    },
495# END Ticket Hours Grouped BY Component with Description
496    {
497    "uuid":"03815803-7688-4f3a-8e65-8d254cc1d1fb",
498    "title":"Ticket Hours Grouped By Milestone",
499    "version":th_version,
500    "sql": """
501SELECT __color__, __group__, __style__,  ticket, summary, component ,version,
502  severity, __milestone__, status, owner, Estimated_work, Total_work, billable,
503  _ord
504
505FROM (
506SELECT p.value AS __color__,
507       t.milestone AS __group__,
508       '' as __style__,
509       t.id AS ticket, summary AS summary,             -- ## Break line here
510       component,version, severity, milestone as __milestone__, status, owner,
511       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
512         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END as Estimated_work,
513       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
514         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END as Total_work,
515       CASE WHEN billable.value = '1' THEN 'Y'
516            else 'N'
517       END as billable,
518       time AS created, changetime AS modified,         -- ## Dates are formatted
519       description AS _description_,                    -- ## Uses a full row
520       changetime AS _changetime,
521       reporter AS _reporter, 0 as _ord
522
523  FROM ticket as t
524  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
525
526LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
527      AND EstimatedHours.Ticket = t.Id
528LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
529      AND totalhours.Ticket = t.Id
530LEFT JOIN ticket_custom as billable ON billable.name='billable'
531      AND billable.Ticket = t.Id
532
533  WHERE t.status IN (#STATUSES#)
534    AND billable.value in ($BILLABLE, $UNBILLABLE)
535
536
537UNION
538
539SELECT '1' AS __color__,
540       t.milestone AS __group__,
541       'background-color:#DFE;' as __style__,
542       0 as ticket, 'Total work' AS summary,
543       NULL as component,NULL as version, NULL as severity,
544       t.milestone as  __milestone__, 'Time Remaining: ' as status,
545       CAST(
546       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
547         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) -
548       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
549         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END)
550         AS CHAR(512)) as owner,
551       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
552         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) as Estimated_work,
553       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
554         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END) as Total_work,
555       NULL as billable,
556       NULL as created, NULL as modified,         -- ## Dates are formatted
557
558       NULL AS _description_,
559       NULL AS _changetime,
560       NULL AS _reporter
561       ,1 as _ord
562  FROM ticket as t
563  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
564
565LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
566      AND EstimatedHours.Ticket = t.Id
567
568LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
569      AND totalhours.Ticket = t.Id
570
571LEFT JOIN ticket_custom as billable ON billable.name='billable'
572      AND billable.Ticket = t.Id
573
574  WHERE t.status IN (#STATUSES#)
575    AND billable.value in ($BILLABLE, $UNBILLABLE)
576  GROUP BY t.milestone
577)  as tbl
578ORDER BY __milestone__, _ord ASC, ticket
579    """
580    },
581#END Ticket Hours Grouped By MileStone
582        {
583    "uuid":"040c9025-7641-4d18-96ad-2b26b4095566",
584    "title":"Ticket Hours Grouped By MileStone with Description",
585    "version":th_version,
586    "sql": """
587SELECT __color__, __group__, __style__,  ticket, summary, component ,version, severity,
588 __milestone__, status, owner, Estimated_work, Total_work, billable,
589 _description_, _ord
590
591FROM (
592SELECT p.value AS __color__,
593       t.milestone AS __group__,
594       '' as __style__,
595       t.id AS ticket, summary AS summary,             -- ## Break line here
596       component,version, severity, milestone as __milestone__, status, owner,
597       CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
598         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END as Estimated_work,
599       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
600         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END as Total_work,
601       CASE WHEN billable.value = '1' THEN 'Y'
602            else 'N'
603       END as billable,
604       time AS created, changetime AS modified,         -- ## Dates are formatted
605       description AS _description_,                    -- ## Uses a full row
606       changetime AS _changetime,
607       reporter AS _reporter
608       ,0 as _ord
609
610  FROM ticket as t
611  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
612
613LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
614      AND EstimatedHours.Ticket = t.Id
615LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
616      AND totalhours.Ticket = t.Id
617LEFT JOIN ticket_custom as billable ON billable.name='billable'
618      AND billable.Ticket = t.Id
619
620  WHERE t.status IN (#STATUSES#)
621    AND billable.value in ($BILLABLE, $UNBILLABLE)
622
623
624UNION
625
626SELECT '1' AS __color__,
627       t.milestone AS __group__,
628       'background-color:#DFE;' as __style__,
629       0 as ticket, 'Total work' AS summary,
630       NULL as component,NULL as version, NULL as severity,
631       t.milestone as __milestone__,
632       'Time Remaining: ' as status,
633       CAST(
634       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
635         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) -
636       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
637         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END)
638         AS CHAR(512)) as owner,
639       SUM(CASE WHEN EstimatedHours.value = '' OR EstimatedHours.value IS NULL THEN 0
640         ELSE CAST( EstimatedHours.value AS DECIMAL(10,2) ) END) as Estimated_work,
641       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
642         ELSE CAST( totalhours.value AS DECIMAL(10,2) ) END) as Total_work,
643       NULL as billable,
644       NULL as created, NULL as modified,         -- ## Dates are formatted
645       NULL AS _description_,
646       NULL AS _changetime,
647       NULL AS _reporter, 1 as _ord
648  FROM ticket as t
649  LEFT JOIN enum as p ON p.name=t.priority AND p.type='priority'
650
651LEFT JOIN ticket_custom as EstimatedHours ON EstimatedHours.name='estimatedhours'
652      AND EstimatedHours.Ticket = t.Id
653
654LEFT JOIN ticket_custom as totalhours ON totalhours.name='totalhours'
655      AND totalhours.Ticket = t.Id
656
657LEFT JOIN ticket_custom as billable ON billable.name='billable'
658      AND billable.Ticket = t.Id
659
660  WHERE t.status IN (#STATUSES#)
661    AND billable.value in ($BILLABLE, $UNBILLABLE)
662  GROUP BY t.milestone
663)  as tbl
664ORDER BY __milestone__, _ord ASC, ticket
665    """
666    }
667    #END Ticket Hours Grouped By MileStone with Description
668]
669
670all_reports = [
671    {"title":"Billing Reports",
672     "description": "Currently the billing reports are the only time based reports, and are therefore useful for getting an idea of what tickets had times (and totals), and which developers spent their time where. ",
673     "reports":billing_reports},
674    {"title":"Ticket/Hour Reports",
675     "description": "These reports are useful for reviewing estimates on a large scale or getting an idea of the project at large. These reports currently ignore the start/end dates. ",
676     "reports": ticket_hours_reports}
677    ]
Note: See TracBrowser for help on using the repository browser.