Edgewall Software

Changes between Version 15 and Version 16 of TracIniReportCustomFieldSample


Ignore:
Timestamp:
Apr 16, 2015, 8:27:22 PM (9 years ago)
Author:
figaro
Comment:

Cosmetic changes

Legend:

Unmodified
Added
Removed
Modified

  • TracIniReportCustomFieldSample

    v15 v16  
    1 = Report with TracTicketsCustomFields =
     1= Report with TracTicketsCustomFields
    22
    33Here are some examples of how to generate simple custom reports from TracTicketsCustomFields.
    44
    5 == Example 1 ==
     5== Example 1
    66
    77A report to show a Milestone summarize time inverted, new field test_one is Time to resolve a Ticket.
    88
    9 Enjoy!
     9'''Trac.ini:'''
     10{{{
     11#!ini
     12[ticket-custom]
     13test_one = text
     14test_one.label = Inverted Time
     15}}}
     16
     17'''Custom SQL:'''
     18{{{#!sql
     19SELECT
     20   milestone AS Project,
     21   SUM(p.value) AS Inverted_Time
     22FROM ticket t, ticket_custom p
     23WHERE p.ticket = t.id AND p.name = 'test_one'
     24GROUP BY milestone
     25ORDER BY milestone
     26}}}
     27
     28== Example 2
     29
     30I have four fields in my ticket-custom and I want them all to be seen in the report. If the existing field was a NULL, the report code adds the word "None" to the output, so I'm getting rid of that with a CASE statement. The main magic is the series of LEFT OUTER JOINs, which let me access each custom field in turn.
    1031
    1132'''Trac.ini:'''
    1233{{{
    13 
    14 [ticket-custom]
    15 test_one = text
    16 test_one.label = Inverted Time
    17 
    18 }}}
    19 
    20 '''Custom SQL:'''
    21 {{{
    22 SELECT
    23    milestone as Project,
    24    sum(p.value) as Inverted_Time
    25    FROM ticket t, ticket_custom p
    26    WHERE p.ticket = t.id and p.name = 'test_one'
    27    group by milestone
    28    ORDER BY milestone
    29 
    30 }}}
    31 
    32 
    33 == Example 2 ==
    34 
    35 Here's another longer example ... I have four fields in my ticket-custom, and I want them all to be seen in the report. If the existing field was a NULL, the report code adds the word "None" to the output, so I'm getting rid of that with a CASE statement. The main magic is the series of LEFT OUTER JOINs, which let me access each custom field in turn.
    36 
    37 '''Trac.ini:'''
    38 {{{
    39 
     34#!ini
    4035[ticket-custom]
    4136charge = text
    … …  
    5449
    5550'''Custom SQL:'''
    56 {{{
     51{{{#!sql
    5752SELECT DISTINCT
    58 
    5953   id AS ticket,
    6054   (CASE WHEN c.value ISNULL THEN '' ELSE c.value END) AS charge,
    … …  
    6458   milestone AS customer,
    6559   summary, component, status
    66 
    67   FROM ticket t,enum p
    68 
     60FROM ticket t, enum p
    6961  LEFT OUTER JOIN ticket_custom c ON
    70        (t.id=c.ticket AND c.name='charge')
    71 
     62       (t.id = c.ticket AND c.name = 'charge')
    7263  LEFT OUTER JOIN ticket_custom n ON
    73        (t.id=n.ticket AND n.name='noncharge')
    74 
     64       (t.id = n.ticket AND n.name = 'noncharge')
    7565  LEFT OUTER JOIN ticket_custom s ON
    76        (t.id=s.ticket AND s.name='sitevisit')
    77 
     66       (t.id = s.ticket AND s.name = 'sitevisit')
    7867  LEFT OUTER JOIN ticket_custom o ON
    79        (t.id=o.ticket AND o.name='oohours')
    80 
    81   WHERE (p.name=t.priority AND p.type='priority' AND status='closed')
    82  
    83   ORDER BY milestone
    84 
     68       (t.id = o.ticket AND o.name = 'oohours')
     69WHERE p.name = t.priority
     70  AND p.type = 'priority'
     71  AND status = 'closed'
     72ORDER BY milestone
    8573}}}
    8674
    87 == Example 3 ==
     75== Example 3
    8876
    89 Going by the philosophy, you can never have too many examples, here's another one:
    90 
    91 I added a custom ticket field to provide an idea of the percentage of the ticket completed so far.  This is a simple dropdown list where the developer can log their progress to the nearest 20%.
     77I added a custom ticket field to provide an idea of the percentage of the ticket completed so far. This is a simple dropdown list where the developer can log their progress to the nearest 20%.
    9278
    9379'''trac.ini:'''
    94 {{{
     80{{{#!ini
    9581[ticket-custom]
    9682progress = select
    … …  
    10490'''Custom SQL:'''
    10591
    106 {{{
     92{{{#!sql
    10793SELECT p.value AS __color__,
    10894   id AS ticket, summary, component, version, milestone, severity,
    10995   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
    11096   time AS created,
    111    changetime AS _changetime, description AS _description,
     97   changetime AS _changetime,
     98   description AS _description,
    11299   reporter AS _reporter,
    113   (CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS progress
    114   FROM ticket t LEFT OUTER JOIN ticket_custom c ON t.id = c.ticket AND c.name = 'progress'
    115      JOIN enum p ON p.name = t.priority AND p.type='priority'
    116   WHERE status IN ('new', 'assigned', 'reopened')
    117   ORDER BY p.value, milestone, severity, time
     100   (CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS progress
     101FROM ticket t LEFT OUTER JOIN ticket_custom c ON t.id = c.ticket AND c.name = 'progress'
     102   JOIN enum p ON p.name = t.priority AND p.type = 'priority'
     103WHERE status IN ('new', 'assigned', 'reopened')
     104ORDER BY p.value, milestone, severity, time
    118105}}}
    119106
    120107Notes
    121  * the OUTER JOIN is the key to getting this to work
    122  * I'm not totally fluent in SQL, so this may not be the simplest way, but it works for me
     108 * The OUTER JOIN is the key to getting this to work.
     109 * This may not be the simplest way, but it works for me.
    123110
    124 == Example 4 ==
    125 I have added a few custom fields to track roughly the amount of work spent on each ticket. One field is the estimated work (in days) and the iother is the actual work (in days). I needed a simple report to summarize the total amount of work for each milestone: estimated and actual.
     111== Example 4
     112
     113I have added a few custom fields to track roughly the amount of work spent on each ticket. One field is the estimated work (in days) and the other is the actual work (in days). I need a simple report to summarize the total amount of work for each milestone: estimated and actual.
    126114
    127115'''trac.ini:'''
    128 {{{
     116{{{#!ini
    129117[ticket-custom]
    130118estimate = text
    … …  
    137125'''Custom SQL:'''
    138126
    139 {{{
     127{{{#!sql
    140128SELECT
    141129   milestone AS Iteration,
    142130   SUM(estimate.value) AS Estimate,
    143131   SUM(actual.value) AS Actual
    144 
    145   FROM ticket t
    146 
    147   LEFT OUTER JOIN ticket_custom estimate ON
    148        (t.id=estimate.ticket AND estimate.name='estimate')
    149 
    150   LEFT OUTER JOIN ticket_custom actual ON
    151        (t.id=actual.ticket AND actual.name='actual')
    152 
    153   GROUP BY milestone
    154 
     132FROM ticket t LEFT OUTER JOIN ticket_custom estimate ON (t.id = estimate.ticket AND estimate.name = 'estimate')
     133   LEFT OUTER JOIN ticket_custom actual ON (t.id = actual.ticket AND actual.name = 'actual')
     134GROUP BY milestone
    155135}}}
    156136
    157 == Example 6 ==
     137== Example 6
     138
    158139We wanted to add the ability to add a due date to a ticket.
    159 Due dates can be added in either date format m/d/y or y/m/d
    160 The report groups the tickets in three buckets: Due Today, Over Due, and Future
     140Due dates can be added in either date format m/d/y or y/m/d.
     141The report groups the tickets in three buckets: Due Today, Over Due, and Future.
    161142
    162143'''trac.ini:'''
    163 {{{
     144{{{#!ini
    164145[ticket-custom]
    165146duedate = text
    … …  
    167148}}}
    168149
    169 
    170150'''Custom SQL:'''
    171151
    172 {{{
     152{{{#!sql
    173153SELECT p.value AS __color__,
    174154   (CASE
    175155     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00',
    176               STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE()
     156              STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) > CURDATE()
    177157          THEN 'Future Tickets'
    178158     WHEN IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00',
    179               STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE()
     159              STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) = CURDATE()
    180160          THEN 'Today Tickets'
    181161          ELSE 'Over Due Tickets'
    182162    END) AS __group__,
    183163   id AS ticket, IF (STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00',
    184    STR_TO_DATE(d.value,'%m/%d/%Y'),STR_TO_DATE(d.value,'%Y/%m/%d')) AS 'Due Date',
     164   STR_TO_DATE(d.value,'%m/%d/%Y'), STR_TO_DATE(d.value,'%Y/%m/%d')) AS 'Due Date',
    185165   summary, component, version, milestone, t.type AS type, owner, status,
    186166   time AS created, changetime AS _changetime, description AS _description,
    187167   reporter AS _reporter
    188   FROM (ticket t)
     168FROM (ticket t)
    189169  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
    190170  LEFT JOIN ticket_custom d ON t.id = d.ticket AND d.name = 'duedate'
    191   WHERE status <> 'closed' AND (STR_TO_DATE(d.value,'%Y/%m/%d') <> '0000-00-00'
    192   OR STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00')
    193   GROUP BY ticket
    194   ORDER BY __group__ DESC, CAST(p.value AS signed), milestone, t.type, time
     171WHERE status <> 'closed'
     172  AND (STR_TO_DATE(d.value,'%Y/%m/%d') <> '0000-00-00' OR STR_TO_DATE(d.value,'%m/%d/%Y') <> '0000-00-00')
     173GROUP BY ticket
     174ORDER BY __group__ DESC, CAST(p.value AS signed), milestone, t.type, time
    195175}}}