source: timingandestimationplugin/branches/trac0.11-Permissions/timingandestimationplugin/reports.py

Last change on this file was 4382, checked in by Russ Tyndall, 15 years ago

fixes #3833 changed all the null as tickets to 0 as ticket (this still seems like a bug in trac, but one easily enough fixed
File size: 25.0 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 ) 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 ) 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 ) 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 ) 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 ) 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 ) 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 =16
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 ) END as Estimated_work,
167    CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
168      ELSE CAST( totalhours.value AS DECIMAL ) 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    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 ) END) -
200       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
201         ELSE CAST( totalhours.value AS DECIMAL ) 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 ) END) as Estimated_work,
205         SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
206      ELSE CAST( totalhours.value AS DECIMAL ) 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    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 ) END as Estimated_work,
253       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
254      ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END) -
288       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
289         ELSE CAST( totalhours.value AS DECIMAL ) 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 ) END) as Estimated_work,
293       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
294         ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END as Estimated_work,
339       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
340         ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END) -
375       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
376         ELSE CAST( totalhours.value AS DECIMAL ) 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 ) END) as Estimated_work,
380       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
381         ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END as Estimated_work,
428       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
429         ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END) -
462       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
463         ELSE CAST( totalhours.value AS DECIMAL ) 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 ) END) as Estimated_work,
467       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
468         ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END as Estimated_work,
513       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
514         ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END) -
548       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
549         ELSE CAST( totalhours.value AS DECIMAL ) 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 ) END) as Estimated_work,
553       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
554         ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END as Estimated_work,
599       CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
600         ELSE CAST( totalhours.value AS DECIMAL ) 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  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 ) END) -
636       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
637         ELSE CAST( totalhours.value AS DECIMAL ) 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 ) END) as Estimated_work,
641       SUM(CASE WHEN totalhours.value = '' OR totalhours.value IS NULL THEN 0
642         ELSE CAST( totalhours.value AS DECIMAL ) 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  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     "reports":billing_reports},
673    {"title":"Ticket/Hour Reports",
674     "reports": ticket_hours_reports}
675    ]
Note: See TracBrowser for help on using the repository browser.