| | 6 | |
| | 7 | == User Report == |
| | 8 | |
| | 9 | When one visits a Trac system as a user of a product and not as a developer of the product, one often wants to search for those tickets of personal interest. Such tickets could be defined as those tickets where the user is the Reporter, in the CC or additionally as a commenter. |
| | 10 | |
| | 11 | Query for Report: |
| | 12 | {{{ |
| | 13 | #!sql |
| | 14 | SELECT |
| | 15 | DISTINCT |
| | 16 | p.value AS __color__, |
| | 17 | (CASE status |
| | 18 | WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;' |
| | 19 | END) AS __style__, |
| | 20 | id AS ticket, summary, component, milestone, status, resolution, |
| | 21 | t.time AS created, changetime AS modified, |
| | 22 | priority AS _priority, reporter AS _reporter, cc AS _cc |
| | 23 | FROM ticket t |
| | 24 | LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' |
| | 25 | LEFT JOIN ticket_change tc ON id = tc.ticket |
| | 26 | WHERE reporter = '$USER' |
| | 27 | OR cc LIKE '$USER,%' |
| | 28 | OR cc LIKE '% $USER' |
| | 29 | OR cc LIKE '% $USER,%' |
| | 30 | OR cc = '$USER' |
| | 31 | OR (tc.author='$USER' AND tc.field = 'comment') |
| | 32 | ORDER BY |
| | 33 | (status = 'closed'), |
| | 34 | (CASE status |
| | 35 | WHEN 'closed' THEN changetime |
| | 36 | ELSE (-1) * CAST(p.value AS int) |
| | 37 | END) DESC |
| | 38 | }}} |
| | 39 | |
| | 40 | To include tickets that the user made any changes to, whether a comment was included or not, change the following: |
| | 41 | {{{ |
| | 42 | #!diff |
| | 43 | --- Report.sql |
| | 44 | +++ Report.sql |
| | 45 | @@ -15,7 +15,7 @@ |
| | 46 | OR cc LIKE '% $USER' |
| | 47 | OR cc LIKE '% $USER,%' |
| | 48 | OR cc = '$USER' |
| | 49 | - OR (tc.author='$USER' AND tc.field = 'comment') |
| | 50 | + OR tc.author='$USER' |
| | 51 | ORDER BY |
| | 52 | (status = 'closed'), |
| | 53 | (CASE status |
| | 54 | }}} |
| | 55 | To exclude tickets that the user made comments to and thus just those they are the reporter or in the CC, delete the following: |
| | 56 | {{{ |
| | 57 | #!diff |
| | 58 | --- Report.sql |
| | 59 | +++ Report.sql |
| | 60 | @@ -9,13 +9,11 @@ |
| | 61 | priority AS _priority, reporter AS _reporter, cc AS _cc |
| | 62 | FROM ticket t |
| | 63 | LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' |
| | 64 | - LEFT JOIN ticket_change tc ON id = tc.ticket |
| | 65 | WHERE reporter = '$USER' |
| | 66 | OR cc LIKE '$USER,%' |
| | 67 | OR cc LIKE '% $USER' |
| | 68 | OR cc LIKE '% $USER,%' |
| | 69 | OR cc = '$USER' |
| | 70 | - OR (tc.author='$USER' AND tc.field = 'comment') |
| | 71 | ORDER BY |
| | 72 | (status = 'closed'), |
| | 73 | (CASE status |
| | 74 | }}} |
