Ticket #11429: trac_reports_2013_sep_3.sql

4       \N      Assigned, Active Tickets by Owner       \n\nSELECT p.value AS __color__,\n   owner AS __group__,\n   id AS ticket, summary, component, milestone, t.type AS type, time AS created,\n   changetime AS _changetime, description AS _description,\n   reporter AS _reporter\n  FROM ticket t,enum p\n  WHERE status = 'assigned'\nAND p.name=t.priority AND p.type='priority'\n  ORDER BY owner, p.value, t.type, time\n   \nList assigned tickets, group by ticket owner, sorted by priority.\n
5       \N      Assigned, Active Tickets by Owner (Full Description)    \nSELECT p.value AS __color__,\n   owner AS __group__,\n   id AS ticket, summary, component, milestone, t.type AS type, time AS created,\n   description AS _description_,\n   changetime AS _changetime, reporter AS _reporter\n  FROM ticket t, enum p\n  WHERE status = 'assigned'\nAND p.name = t.priority AND p.type = 'priority'\n  ORDER BY owner, p.value, t.type, time\n       \nList tickets assigned, group by ticket owner.\nThis report demonstrates the use of full-row display.\n
1       \N      Active Tickets  \nSELECT p.value AS __color__,\n   id AS ticket, summary, component, version, milestone, t.type AS type, \n   owner, status,\n   time AS created,\n   changetime AS _changetime, description AS _description,\n   reporter AS _reporter\n  FROM ticket t, enum p\n  WHERE status <> 'closed' \nAND p.name = t.priority AND p.type = 'priority'\n  ORDER BY p.value, milestone, t.type, time\n   \n * List all active tickets by priority.\n * Color each row based on priority.\n
17      \N      Keyword count   select k.keywords , count(t.id) \r\n\r\nfrom ticket t, \r\n     (select distinct * from keywords()) as k \r\n\r\nwhere t.keywords ~ \r\n\r\n      ( '^'    || k.keywords || '$|'\r\n        '^'    || k.keywords || '[ ,]|' ||\r\n        '[ ,]' || k.keywords || '[ ,]|'  ||\r\n        '[ ,]' || k.keywords || '$' )::text \r\n\r\nGROUP by k.keywords\r\n\r\nORDER by count DESC;\r\n\r\n    
15      \N      Keywords report SELECT \r\n      \r\n       k.keywords as __group__,\r\n       t.id as ticket,\r\n       t.summary as summary\r\n\r\nFROM   ticket t, \r\n       (select distinct * from keywords()) as k \r\n\r\nWHERE t.keywords ~ \r\n\r\n      ( '^'    || k.keywords || '$|'\r\n        '^'    || k.keywords || '[ ,]|' ||\r\n        '[ ,]' || k.keywords || '[ ,]|'  ||\r\n        '[ ,]' || k.keywords || '$' )::text\r\n\r\n      and k.keywords ~ '.+' \r\n      and not k.keywords ~ 'iteration.+' \r\n      and not k.keywords ~ 'story.+'\r\n\r\nORDER BY __group__, id;   Listing of used keywords.
18      \N      Closed time report      SELECT p.value AS __color__,\r\n   id AS ticket, summary as Summary, component as component, version,\r\nmilestone, t.type AS tipo,\r\n   (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS Owner,\r\n   time AS created,\r\n   changetime AS Changetime, description AS _description,\r\n   reporter AS _reporter\r\n  FROM ticket t, enum p\r\n  WHERE status IN ('closed')\r\nAND p.name = t.priority AND p.type = 'priority'\r\n  ORDER BY changetime        See http://groups.google.com/group/trac-users/browse_thread/thread/e8fef26c4652c66 for more.
16      \N      Active Tickets by $COMPONENT    SELECT p.value AS __color__,\r\n   milestone AS __group__,\r\n   id AS ticket, summary, component, version, milestone,\r\n   t.type AS type, priority, time AS created,\r\n   changetime AS _changetime, description AS _description,\r\n   reporter AS _reporter\r\n\r\nFROM ticket t, enum p\r\n\r\nWHERE t.status <> 'closed' \r\n\r\nAND p.name = t.priority AND p.type = 'priority' AND component = '$COMPONENT'\r\n\r\nORDER BY __group__, p.value, t.type, time\r\n      This report uses the $COMPONENT dynamic variable.\r\n
13      \N      Open tickets by age (oldest first)      SELECT p.value AS __color__,\r\n  id AS ticket,  now()::date-to_timestamp(time)::date as "Days Old", time\r\nas created, owner, summary, milestone,\r\n  changetime AS _changetime, description AS _description,\r\n  reporter AS _reporter\r\n FROM ticket t, enum p\r\n WHERE status <> 'closed'\r\nAND p.name = t.priority AND p.type = 'priority'\r\n ORDER BY time All open tickets by age.\r\nhttp://www.mail-archive.com/trac@lists.edgewall.com/msg01330.html
3       \N      All Tickets by Milestone        \nSELECT p.value AS __color__,\n   milestone||' Release' AS __group__,\n   id AS ticket, summary, component, version, t.type AS type, \n   owner, status,\n   time AS created,\n   changetime AS _changetime, description AS _description,\n   reporter AS _reporter\n  FROM ticket t, enum p\n  WHERE status <> 'closed' \nAND p.name = t.priority AND p.type = 'priority'\n  ORDER BY (milestone IS NULL),milestone, p.value, t.type, time\n  \nThis report shows how to color results by priority,\nwhile grouping results by milestone.\n\nLast modification time, description and reporter are included as hidden fields\nfor useful RSS export.\n
7       \N      QA Bugs / User feedback!        query:?status=accepted\r\n&\r\nstatus=new\r\n&\r\nstatus=reopened\r\n&\r\ncomponent=from+QA\r\n&\r\ngroup=milestone\r\n&\r\nmax=1000\r\n&\r\ncol=id\r\n&\r\ncol=summary\r\n&\r\ncol=status\r\n&\r\ncol=owner\r\n&\r\ncol=time\r\n&\r\nreport=7\r\n&\r\norder=priority   Open items in the "from QA" component. These are machine generated by http://qa.openmicroscopy.org.uk\r\n\r\nDevelopers should handle these tickets as quickly as possible, moving them into the appropriate component and assign valid owners. Once the component has changed from "from QA" they will no longer show up in this report.
8       \N      BUGS! EEK!!     query:?status=accepted\r\n&\r\nstatus=new\r\n&\r\nstatus=reopened\r\n&\r\ntype=task\r\n&\r\nsummary=^bug\r\n&\r\nsummary=^patch\r\n&\r\ngroup=milestone\r\n&\r\nmax=1000\r\n&\r\ncol=id\r\n&\r\ncol=summary\r\n&\r\ncol=owner\r\n&\r\ncol=priority\r\n&\r\ncol=component\r\n&\r\ncol=time\r\n&\r\ncol=changetime\r\n&\r\ncol=remaining_time\r\n&\r\ncol=sprint\r\n&\r\nreport=8\r\n&\r\norder=time      Lists all open tasks (not stories) which are prefixed with "bug" ("BUG:", "bug:", "Bug:", etc) in the current milestone. They are ordered by creation date to show how long they have stuck around.
14      \N      My Story/Task report    SELECT p.value AS __color__,       \r\n       (CASE \r\n          WHEN keywords ~ 'story\\\\d+' and t.type ILIKE 'task'\r\n            THEN substring(keywords from 'story\\\\d+')        \r\n          WHEN t.type ILIKE 'task' \r\n            THEN 'independent tasks (no story)'\r\n          ELSE 'defect' END) AS __group__,\r\n       t.id AS ticket, status, summary AS summary, milestone,               \r\n       component, owner, time AS created, changetime AS modified,\r\n       (CASE \r\n          WHEN keywords ~ 'story\\\\d+' and t.type ILIKE 'task'\r\n            THEN 'Goto story #'||substring(keywords from 'story(\\\\d+)')        \r\n          ELSE '' END) AS description\r\n\r\n  FROM  ticket t,enum p\r\n  WHERE p.name=t.priority AND p.type='priority'\r\n    AND NOT ( t.type ILIKE 'story' )                 -- omit stories\r\n    AND status <> 'closed'    -- active only\r\n    AND owner = '$USER'\r\n  ORDER BY __group__ DESC, p.value, t.severity        Breakdown of active stories and their associated tasks. (Use keyword:story### for linking)
2       \N      Active Tickets by Version       \r\nSELECT p.value AS __color__,\r\n   version AS __group__,\r\n   id AS ticket, summary, component, version, t.type AS type, \r\n   owner, status,\r\n   time AS created,\r\n   changetime AS _changetime, description AS _description,\r\n   reporter AS _reporter\r\n  FROM ticket t, enum p\r\n  WHERE status <> 'closed' \r\nAND p.name = t.priority AND p.type = 'priority'\r\n  ORDER BY (version IS NULL),version, p.value, t.type, time\r\n    \r\nThis report shows how to color results by priority,\r\nwhile grouping results by version.\r\n\r\nLast modification time, description and reporter are included as hidden fields\r\nfor useful RSS export.\r\n
22      \N      Requirement Report      SELECT id AS ticket, summary, status, priority, milestone, component, \r\n       time as created FROM ticket \r\n  WHERE type ILIKE 'requirement'\r\n  ORDER BY priority, time\r\n\r\n  
23      \N      Story Report    SELECT id AS ticket, summary, status, priority, milestone, component, \r\n       time as created FROM ticket \r\n  WHERE type ILIKE 'story'\r\n  ORDER BY priority, time\r\n    
24      \N      My Tickets by Sprint    query:status=assigned&status=new&status=reopened&group=sprint&order=priority&col=id&col=summary&col=status&col=type&col=priority&col=milestone&col=component&col=remaining_time&owner=$USER     
26      \N      Cycle/Iteration report  SELECT p.value AS __color__,\r\n\r\n       (CASE \r\n          WHEN keywords like '%%iteration1%%' THEN t.milestone || ' iteration 1'\r\n          WHEN keywords like '%%iteration2%%' THEN t.milestone || ' iteration 2'\r\n          WHEN keywords like '%%iteration3%%' THEN t.milestone || ' iteration 3'\r\n          WHEN keywords like '%%iteration4%%' THEN t.milestone || ' iteration 4'\r\n          WHEN keywords like '%%iteration5%%' THEN t.milestone || ' iteration 5'\r\n          WHEN keywords like '%%iteration6%%' THEN t.milestone || ' iteration 6'\r\n          ELSE t.milestone || '' END) AS __group__,\r\n\r\n       (CASE \r\n          WHEN keywords ~ 'story\\\\d+' and t.type ILIKE 'task' \r\n          THEN  'color: lightgray;' \r\n          -- or '@import url(http://users.openmicroscopy.org.uk/~jmoore/trac.css)'\r\n          ELSE '' END) AS __style__,\r\n\r\n       (CASE \r\n          WHEN t.type ILIKE 'story' THEN 'story'||t.id\r\n          WHEN keywords ~ 'story\\\\d+' and t.type ILIKE 'task'\r\n            THEN substring(keywords from 'story\\\\d+')||'task'||t.id\r\n          ELSE 'defect' END) AS _story_id,\r\n       \r\n       (CASE \r\n          WHEN keywords ~ 'story\\\\d+' and t.type ILIKE 'task' \r\n          THEN substring(keywords from 'story(\\\\d+)')\r\n          ELSE '' END) AS SN,\r\n\r\n\r\n       t.id AS ticket, status, summary AS summary,              \r\n       component, owner, time AS created, changetime AS modified\r\n\r\n  FROM  ticket t,enum p, milestone m\r\n\r\n  WHERE p.name=t.priority AND p.type='priority' AND\r\n        t.milestone = m.name AND m.completed = 0\r\n\r\n  ORDER BY __group__, p.value, t.severity        Breakdown of cycles and iterations for uncompleted milestones. Use keyword:iteration# for linking.
28      \N      All open tasks in the current milestone query:status=assigned&status=new&status=reopened&max=1000&order=sprint&col=id&col=summary&col=milestone&col=status&col=type&col=owner&col=priority&col=component&col=remaining_time&col=sprint&milestone=OMERO-Beta4.2&type=task        
32      \N      Andrew's Overview       query:?status=accepted\r\n&\r\nstatus=new\r\n&\r\nstatus=reopened\r\n&\r\ncomponent=Specification\r\n&\r\nmilestone=!Unscheduled\r\n&\r\nmilestone=!Future\r\n&\r\nor\r\n&\r\nkeywords=~schema\r\n&\r\nstatus=accepted\r\n&\r\nstatus=new\r\n&\r\nstatus=reopened\r\n&\r\nmilestone=!Unscheduled\r\n&\r\nmilestone=!Future\r\n&\r\nor\r\n&\r\nowner=~ajpatterson\r\n&\r\nstatus=accepted\r\n&\r\nstatus=new\r\n&\r\nstatus=reopened\r\n&\r\ngroup=milestone\r\n&\r\nmax=200\r\n&\r\ncol=id\r\n&\r\ncol=summary\r\n&\r\ncol=keywords\r\n&\r\ncol=component\r\n&\r\ncol=milestone\r\n&\r\ncol=status\r\n&\r\ncol=owner\r\n&\r\ncol=type\r\n&\r\nreport=32\r\n&\r\norder=type      A list of tickets to check.
30      \N      Tickets associated with query:?status=!closed\r\n&\r\ncc=~saloynton\r\n&\r\norder=priority      Tickets CC in 
33      \N      Active tickets, Mine first      \r\nSELECT p.value AS __color__,\r\n   (CASE owner \r\n     WHEN '$USER' THEN 'My Tickets' \r\n     ELSE 'Active Tickets' \r\n    END) AS __group__,\r\n   id AS ticket, summary, component, version, milestone, t.type AS type, \r\n   owner, status,\r\n   time AS created,\r\n   changetime AS _changetime, description AS _description,\r\n   reporter AS _reporter\r\n  FROM ticket t, enum p\r\n  WHERE status <> 'closed' \r\nAND p.name = t.priority AND p.type = 'priority'\r\n  ORDER BY (owner = '$USER') DESC, p.value, milestone, t.type, time\r\n (Copied from other report)\r\n\r\n\r\n * List all active tickets by priority.\r\n * Show all tickets owned by the logged in user in a group first.\r\n
34      \N      My tickets by milestone SELECT p.value AS __color__,\r\n   milestone||' Release' AS __group__,\r\n   id AS ticket, summary, component, version, t.type AS type, \r\n   owner, status,\r\n   time AS created,\r\n   changetime AS _changetime, description AS _description,\r\n   reporter AS _reporter\r\n  FROM ticket t, enum p\r\n  WHERE status <> 'closed' \r\nAND p.name = t.priority AND p.type = 'priority' AND owner = '$USER'\r\n  ORDER BY (milestone IS NULL) DESC,milestone DESC, p.value, t.type, time\r\n        
29      \N      All tickets of your interest (only shows closed)          SELECT p.value AS __color__,\r\n   (CASE owner WHEN '$USER' THEN \r\n     (CASE status \r\n       WHEN 'assigned' \r\n       THEN 'Tickets that you accepted' \r\n       ELSE 'Tickets that were assigned to you, please accept or reassign' \r\n      END) \r\n     ELSE 'Tickets, that have your name in the cc' END) \r\n     AS __group__,\r\n   id AS ticket, summary, component, version, milestone,\r\n   tic.type AS type, priority, time AS created,\r\n   changetime AS _changetime, description AS _description,\r\n   reporter AS _reporter\r\n  FROM ticket tic\r\n  LEFT JOIN enum p ON p.name = tic.priority AND p.type = 'priority'\r\n  WHERE tic.status = 'closed' AND (owner = '$USER' OR cc like '%$USER%')\r\n  ORDER BY owner, (status = 'assigned') DESC, p.value, milestone, tic.type, time   \r\n\r\nhttp://stackoverflow.com/questions/193341/how-can-i-best-take-advantage-of-trac/534447#534447
35      \N      Top-level open tickets (4.3) -- Usability       SELECT t.type as __group__, t.id AS ticket, t.summary as summary, t.component as component, t.owner as owner, t.priority as priority,\r\n\r\n--- Case statemet for the priority coloring\r\n    (CASE t.priority\r\n       WHEN null THEN '--'\r\n       ELSE p.value\r\n    END) as __color__,\r\n\r\n--- Case statement for simple sub-estimates\r\n\r\n    (CASE t.type\r\n       WHEN 'task' THEN ''\r\n       ELSE\r\n           ( SELECT ''||count(al.src)\r\n               FROM agilo_link al\r\n              WHERE al.src = t.id )\r\n    END) as subtickets,\r\n\r\n-- Case statement for totals (only open in this milestone are included,\r\n-- if a ticket has no estimate it will be omitted)\r\n    (CASE t.type\r\n       WHEN 'task' THEN\r\n           (SELECT (case tc.value when null then '?' when '' then '?' else tc.value || 'd' end)\r\n            FROM ticket_custom tc\r\n            WHERE tc.ticket = t.id and tc.name = 'remaining_time') \r\n       WHEN 'story' THEN\r\n           ( SELECT count(al.src) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link al, ticket_custom tc, ticket t2\r\n              WHERE al.src=t.id\r\n              AND tc.ticket = t2.id\r\n              AND tc.name = 'remaining_time'\r\n              AND al.dest=t2.id\r\n              AND t2.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (t2.milestone = 'OMERO-Beta4.3')\r\n           )\r\n       WHEN 'requirement' THEN\r\n           ( SELECT count(Task.id) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link ReqToStory,\r\n                   ticket Story,\r\n                   agilo_link StoryToTask,\r\n                   ticket Task,\r\n                   ticket_custom tc\r\n              WHERE t.id = ReqToStory.src\r\n              AND ReqToStory.dest = Story.id\r\n              AND Story.id = StoryToTask.src\r\n              AND StoryToTask.dest = Task.id\r\n              AND tc.ticket = Task.id\r\n              AND tc.name = 'remaining_time'\r\n              AND Task.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (Task.milestone = 'OMERO-Beta4.3')\r\n           )\r\n       ELSE '???'\r\n    END) as remaining\r\n\r\n   FROM ticket t\r\n   LEFT OUTER JOIN enum p\r\n     ON t.priority = p.name\r\n    AND p.type = 'priority'\r\n  WHERE t.status in ('accepted', 'assigned', 'new', 'reopened')\r\n    AND (t.component is null OR t.component != 'Usability')\r\n    AND (t.milestone = 'OMERO-Beta4.3')\r\n    AND t.id not in\r\n\r\n-- Can't be the target of a link from a ticket in this milestone\r\n (SELECT distinct al2.dest\r\n    FROM agilo_link al2, ticket t3\r\n   WHERE t3.status in ('accepted', 'assigned', 'new', 'reopened')\r\n     AND (t3.milestone = 'OMERO-Beta4.3')\r\n     AND t3.id = al2.src\r\n  )\r\n\r\n  ORDER by t.type, t.priority, t.id\r\n\r\n Tickets grouped into Requirements, Stories, Tasks which are '''NOT''' linked by an open, higher-level ticket in this milestone. (If any bugs have been created by accident these will appear first. Please change them to "Tasks").\r\n\r\nFrom this list, all tickets that are in this milestone are reachable. If a top-level item is closed or moved to another milestone BUT it still contains tickets in the current milestone, those will then appear.
36      \N      My Tickets      SELECT p.value AS __color__,\r\n   (CASE status WHEN 'assigned' THEN 'Assigned' ELSE 'Owned' END) AS __group__,\r\n   id AS ticket, summary, component, version, milestone,\r\n   t.type AS type, priority, time AS created,\r\n   changetime AS _changetime, description AS _description,\r\n   reporter AS _reporter\r\n  FROM ticket t, enum p\r\n  WHERE t.status <> 'closed' \r\nAND p.name = t.priority AND p.type = 'priority' AND owner = '$USER'\r\n  ORDER BY (status = 'assigned') DESC, p.value, milestone, t.type, time    This report demonstrates the use of the automatically set \r\n$USER dynamic variable, replaced with the username of the\r\nlogged in user when executed.\r\n
37      \N      Top-level open tickets (4.3.2)  SELECT t.type as __group__, t.id AS ticket, t.summary as summary, t.component as component, t.owner as owner, t.priority as priority,\r\n\r\n--- Case statemet for the priority coloring\r\n    (CASE t.priority\r\n       WHEN null THEN '--'\r\n       ELSE p.value\r\n    END) as __color__,\r\n\r\n--- Case statement for simple sub-estimates\r\n\r\n    (CASE t.type\r\n       WHEN 'task' THEN ''\r\n       ELSE\r\n           ( SELECT ''||count(al.src)\r\n               FROM agilo_link al\r\n              WHERE al.src = t.id )\r\n    END) as subtickets,\r\n\r\n-- Case statement for totals (only open in this milestone are included,\r\n-- if a ticket has no estimate it will be omitted)\r\n    (CASE t.type\r\n       WHEN 'task' THEN\r\n           (SELECT (case tc.value when null then '?' when '' then '?' else tc.value || 'd' end)\r\n            FROM ticket_custom tc\r\n            WHERE tc.ticket = t.id and tc.name = 'remaining_time') \r\n       WHEN 'story' THEN\r\n           ( SELECT count(al.src) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link al, ticket_custom tc, ticket t2\r\n              WHERE al.src=t.id\r\n              AND tc.ticket = t2.id\r\n              AND tc.name = 'remaining_time'\r\n              AND al.dest=t2.id\r\n              AND t2.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (t2.milestone = 'OMERO-Beta4.3.2')\r\n           )\r\n       WHEN 'requirement' THEN\r\n           ( SELECT count(Task.id) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link ReqToStory,\r\n                   ticket Story,\r\n                   agilo_link StoryToTask,\r\n                   ticket Task,\r\n                   ticket_custom tc\r\n              WHERE t.id = ReqToStory.src\r\n              AND ReqToStory.dest = Story.id\r\n              AND Story.id = StoryToTask.src\r\n              AND StoryToTask.dest = Task.id\r\n              AND tc.ticket = Task.id\r\n              AND tc.name = 'remaining_time'\r\n              AND Task.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (Task.milestone = 'OMERO-Beta4.3.2')\r\n           )\r\n       ELSE '???'\r\n    END) as remaining\r\n\r\n   FROM ticket t\r\n   LEFT OUTER JOIN enum p\r\n     ON t.priority = p.name\r\n    AND p.type = 'priority'\r\n  WHERE t.status in ('accepted', 'assigned', 'new', 'reopened')\r\n    AND (t.milestone = 'OMERO-Beta4.3.2')\r\n    AND t.id not in\r\n\r\n-- Can't be the target of a link from a ticket in this milestone\r\n (SELECT distinct al2.dest\r\n    FROM agilo_link al2, ticket t3\r\n   WHERE t3.status in ('accepted', 'assigned', 'new', 'reopened')\r\n     AND (t3.milestone = 'OMERO-Beta4.3.2')\r\n     AND t3.id = al2.src\r\n  )\r\n\r\n  ORDER by t.type, t.priority, t.id\r\n\r\n        Tickets grouped into Requirements, Stories, Tasks which are '''NOT''' linked by an open, higher-level ticket in this milestone. (If any bugs have been created by accident these will appear first. Please change them to "Tasks").\r\n\r\nFrom this list, all tickets that are in this milestone are reachable. If a top-level item is closed or moved to another milestone BUT it still contains tickets in the current milestone, those will then appear.
11      \N      Top-level open tickets (Future) SELECT t.type as __group__, t.id AS ticket, t.summary as summary, t.component as component, t.owner as owner, t.priority as priority,\r\n\r\n--- Case statemet for the priority coloring\r\n    (CASE t.priority\r\n       WHEN null THEN '--'\r\n       ELSE p.value\r\n    END) as __color__,\r\n\r\n--- Case statement for simple sub-estimates\r\n\r\n    (CASE t.type\r\n       WHEN 'task' THEN ''\r\n       ELSE\r\n           ( SELECT ''||count(al.src)\r\n               FROM agilo_link al\r\n              WHERE al.src = t.id )\r\n    END) as subtickets,\r\n\r\n-- Case statement for totals (only open in this milestone are included,\r\n-- if a ticket has no estimate it will be omitted)\r\n    (CASE t.type\r\n       WHEN 'task' THEN\r\n           (SELECT (case tc.value when null then '?' when '' then '?' else tc.value || 'd' end)\r\n            FROM ticket_custom tc\r\n            WHERE tc.ticket = t.id and tc.name = 'remaining_time') \r\n       WHEN 'story' THEN\r\n           ( SELECT count(al.src) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link al, ticket_custom tc, ticket t2\r\n              WHERE al.src=t.id\r\n              AND tc.ticket = t2.id\r\n              AND tc.name = 'remaining_time'\r\n              AND al.dest=t2.id\r\n              AND t2.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (t2.milestone is null OR (t2.milestone not like 'OMERO-Beta4%'))\r\n           )\r\n       WHEN 'requirement' THEN\r\n           ( SELECT count(Task.id) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link ReqToStory,\r\n                   ticket Story,\r\n                   agilo_link StoryToTask,\r\n                   ticket Task,\r\n                   ticket_custom tc\r\n              WHERE t.id = ReqToStory.src\r\n              AND ReqToStory.dest = Story.id\r\n              AND Story.id = StoryToTask.src\r\n              AND StoryToTask.dest = Task.id\r\n              AND tc.ticket = Task.id\r\n              AND tc.name = 'remaining_time'\r\n              AND Task.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (Task.milestone is null OR (Task.milestone not like 'OMERO-Beta4%'))\r\n           )\r\n       ELSE '???'\r\n    END) as remaining\r\n\r\n   FROM ticket t\r\n   LEFT OUTER JOIN enum p\r\n     ON t.priority = p.name\r\n    AND p.type = 'priority'\r\n  WHERE t.status in ('accepted', 'assigned', 'new', 'reopened')\r\n    AND (t.milestone is null OR (t.milestone not like 'OMERO-Beta4%'))\r\n    AND t.id not in\r\n\r\n-- Can't be the target of a link from a ticket in this milestone\r\n (SELECT distinct al2.dest\r\n    FROM agilo_link al2, ticket t3\r\n   WHERE t3.status in ('accepted', 'assigned', 'new', 'reopened')\r\n     AND (t3.milestone is null OR (t3.milestone not like 'OMERO-Beta4.2.?' AND t3.milestone not like 'OMERO-Beta4%'))\r\n     AND t3.id = al2.src\r\n  )\r\n\r\n  ORDER by t.type, t.priority, t.id\r\n\r\n   Tickets grouped into Requirements, Stories, Tasks which are '''NOT''' linked by an open, higher-level ticket in this milestone. (If any bugs have been created by accident these will appear first. Please change them to "Tasks").\r\n\r\nFrom this list, all tickets that are in this milestone are reachable. If a top-level item is closed or moved to another milestone BUT it still contains tickets in the current milestone, those will then appear.
38      \N      Top-level open tickets (4.5) - No bugs  SELECT t.type as __group__, t.id AS ticket, t.summary as summary, t.component as component, t.owner as owner, t.priority as priority,\r\n\r\n--- Case statemet for the priority coloring\r\n    (CASE t.priority\r\n       WHEN null THEN '--'\r\n       ELSE p.value\r\n    END) as __color__,\r\n\r\n--- Case statement for simple sub-estimates\r\n\r\n    (CASE t.type\r\n       WHEN 'task' THEN ''\r\n       ELSE\r\n           ( SELECT ''||count(al.src)\r\n               FROM agilo_link al\r\n              WHERE al.src = t.id )\r\n    END) as subtickets,\r\n\r\n-- Case statement for totals (only open in this milestone are included,\r\n-- if a ticket has no estimate it will be omitted)\r\n    (CASE t.type\r\n       WHEN 'task' THEN\r\n           (SELECT (case tc.value when null then '?' when '' then '?' else tc.value || 'd' end)\r\n            FROM ticket_custom tc\r\n            WHERE tc.ticket = t.id and tc.name = 'remaining_time') \r\n       WHEN 'story' THEN\r\n           ( SELECT count(al.src) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link al, ticket_custom tc, ticket t2\r\n              WHERE al.src=t.id\r\n              AND tc.ticket = t2.id\r\n              AND tc.name = 'remaining_time'\r\n              AND al.dest=t2.id\r\n              AND t2.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (t2.milestone like 'OMERO-4.5')\r\n           )\r\n       WHEN 'requirement' THEN\r\n           ( SELECT count(Task.id) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link ReqToStory,\r\n                   ticket Story,\r\n                   agilo_link StoryToTask,\r\n                   ticket Task,\r\n                   ticket_custom tc\r\n              WHERE t.id = ReqToStory.src\r\n              AND ReqToStory.dest = Story.id\r\n              AND Story.id = StoryToTask.src\r\n              AND StoryToTask.dest = Task.id\r\n              AND tc.ticket = Task.id\r\n              AND tc.name = 'remaining_time'\r\n              AND Task.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (Task.milestone like 'OMERO-4.5')\r\n           )\r\n       ELSE '???'\r\n    END) as remaining\r\n\r\n   FROM ticket t\r\n   LEFT OUTER JOIN enum p\r\n     ON t.priority = p.name\r\n    AND p.type = 'priority'\r\n  WHERE t.status in ('accepted', 'assigned', 'new', 'reopened')\r\n    AND (t.milestone like 'OMERO-4.5')\r\n    AND (t.summary not ilike 'bug:%')\r\n    AND t.id not in\r\n\r\n-- Can't be the target of a link from a ticket in this milestone\r\n (SELECT distinct al2.dest\r\n    FROM agilo_link al2, ticket t3\r\n   WHERE t3.status in ('accepted', 'assigned', 'new', 'reopened')\r\n     AND (t3.milestone like 'OMERO-4.5')\r\n     AND t3.id = al2.src\r\n  )\r\n\r\n  ORDER by t.type, t.priority, t.id\r\n\r\n\r\n       Tickets grouped into Requirements, Stories, Tasks which are '''NOT''' linked by an open, higher-level ticket in this milestone. (If any bugs have been created by accident these will appear first. Please change them to "Tasks").\r\n\r\nFrom this list, all tickets that are in this milestone are reachable. If a top-level item is closed or moved to another milestone BUT it still contains tickets in the current milestone, those will then appear.
39      \N      All tickets of your interest (shows open)         SELECT p.value AS __color__,\r\n   (CASE owner WHEN '$USER' THEN \r\n     (CASE status \r\n       WHEN 'assigned' \r\n       THEN 'Tickets that you accepted' \r\n       ELSE 'Tickets that were assigned to you, please accept or reassign' \r\n      END) \r\n     ELSE 'Tickets, that have your name in the cc' END) \r\n     AS __group__,\r\n   id AS ticket, summary, component, version, milestone,\r\n   tic.type AS type, priority, time AS created,\r\n   changetime AS _changetime, description AS _description,\r\n   reporter AS _reporter\r\n  FROM ticket tic\r\n  LEFT JOIN enum p ON p.name = tic.priority AND p.type = 'priority'\r\n  WHERE tic.status <> 'closed' AND (owner = '$USER' OR cc like '%$USER%')\r\n  ORDER BY owner, (status = 'assigned') DESC, p.value, milestone, tic.type, time  \r\n\r\nhttp://stackoverflow.com/questions/193341/how-can-i-best-take-advantage-of-trac/534447#534447
40      \N      Top-level open tickets (4.3.2) (copy)   SELECT t.type as __group__, t.id AS ticket, t.summary as summary, t.component as component, t.owner as owner, t.priority as priority,\r\n\r\n--- Case statemet for the priority coloring\r\n    (CASE t.priority\r\n       WHEN null THEN '--'\r\n       ELSE p.value\r\n    END) as __color__,\r\n\r\n--- Case statement for simple sub-estimates\r\n\r\n    (CASE t.type\r\n       WHEN 'task' THEN ''\r\n       ELSE\r\n           ( SELECT ''||count(al.src)\r\n               FROM agilo_link al\r\n              WHERE al.src = t.id )\r\n    END) as subtickets,\r\n\r\n-- Case statement for totals (only open in this milestone are included,\r\n-- if a ticket has no estimate it will be omitted)\r\n    (CASE t.type\r\n       WHEN 'task' THEN\r\n           (SELECT (case tc.value when null then '?' when '' then '?' else tc.value || 'd' end)\r\n            FROM ticket_custom tc\r\n            WHERE tc.ticket = t.id and tc.name = 'remaining_time') \r\n       WHEN 'story' THEN\r\n           ( SELECT count(al.src) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link al, ticket_custom tc, ticket t2\r\n              WHERE al.src=t.id\r\n              AND tc.ticket = t2.id\r\n              AND tc.name = 'remaining_time'\r\n              AND al.dest=t2.id\r\n              AND t2.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (t2.milestone = 'OMERO-Beta4.3.2')\r\n           )\r\n       WHEN 'requirement' THEN\r\n           ( SELECT count(Task.id) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link ReqToStory,\r\n                   ticket Story,\r\n                   agilo_link StoryToTask,\r\n                   ticket Task,\r\n                   ticket_custom tc\r\n              WHERE t.id = ReqToStory.src\r\n              AND ReqToStory.dest = Story.id\r\n              AND Story.id = StoryToTask.src\r\n              AND StoryToTask.dest = Task.id\r\n              AND tc.ticket = Task.id\r\n              AND tc.name = 'remaining_time'\r\n              AND Task.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (Task.milestone = 'OMERO-Beta4.3.2')\r\n           )\r\n       ELSE '???'\r\n    END) as remaining\r\n\r\n   FROM ticket t\r\n   LEFT OUTER JOIN enum p\r\n     ON t.priority = p.name\r\n    AND p.type = 'priority'\r\n  WHERE t.status in ('accepted', 'assigned', 'new', 'reopened')\r\n    AND (t.milestone = 'OMERO-Beta4.3.2')\r\n    AND t.id not in\r\n\r\n-- Can't be the target of a link from a ticket in this milestone\r\n (SELECT distinct al2.dest\r\n    FROM agilo_link al2, ticket t3\r\n   WHERE t3.status in ('accepted', 'assigned', 'new', 'reopened')\r\n     AND (t3.milestone = 'OMERO-Beta4.3.2')\r\n     AND t3.id = al2.src\r\n  )\r\n\r\n  ORDER by t.type, t.owner, t.priority, t.id\r\n   Tickets grouped into Requirements, Stories, Tasks which are '''NOT''' linked by an open, higher-level ticket in this milestone. (If any bugs have been created by accident these will appear first. Please change them to "Tasks").\r\n\r\nFrom this list, all tickets that are in this milestone are reachable. If a top-level item is closed or moved to another milestone BUT it still contains tickets in the current milestone, those will then appear.
41      \N      BUGS not in 4.3.2       query:?status=accepted\r\n&\r\nstatus=new\r\n&\r\nstatus=reopened\r\n&\r\nsummary=~bug:\r\n&\r\nreporter=!omero-qa\r\n&\r\nmilestone=!OMERO-Beta4.3.2\r\n&\r\ncol=id\r\n&\r\ncol=summary\r\n&\r\ncol=milestone\r\n&\r\ncol=status\r\n&\r\ncol=owner\r\n&\r\ncol=priority\r\n&\r\ncol=time\r\n&\r\ncol=changetime\r\n&\r\ncol=reporter\r\n&\r\ndesc=1\r\n&\r\norder=time 
44      \N      Bio-Formats tickets     query:?status=accepted\r\n&\r\nstatus=new\r\n&\r\nstatus=reopened\r\n&\r\ncomponent=Bio-Formats\r\n&\r\ngroup=type\r\n&\r\nmax=1000\r\n&\r\ncol=id\r\n&\r\ncol=summary\r\n&\r\ncol=status\r\n&\r\ncol=owner\r\n&\r\ncol=priority\r\n&\r\ncol=milestone\r\n&\r\ncol=time\r\n&\r\ncol=changetime\r\n&\r\ncol=reporter\r\n&\r\ncol=cc\r\n&\r\nreport=44\r\n&\r\norder=priority     
49      \N      Externally reported tickets     select t.milestone as __group__, t.id AS ticket, t.summary as summary, t.cc as cc, t.component as component, t.owner as owner, t.reporter as reporter, t.time as created, t.changetime as modified, t.priority as priority\r\n\r\n  from ticket t\r\n\r\n where t.type = 'task'\r\n   and t.status in ('accepted', 'assigned', 'new', 'reopened')\r\n   and replace(\r\n             replace(t.cc, '@openmicroscopy.org.uk', '/OME'),\r\n            '@glencoesoftware.com', '/GS')\r\n         like '%@%'\r\n order by t.milestone, t.id\r\n   These are tickets which have external emails CC'd and are still open. Likely these should be checked on frequently in order to respond swiftly to the community.
45      \N      Low-hanging fruit       query:status=accepted&status=new&status=reopened&keywords=~lhf&col=id&col=summary&col=status&col=type&col=priority&col=milestone&col=component&order=priority   Tickets which are good starting points for new developers or external contributors.
46      \N      Current issues: develop query:status=accepted&status=new&status=reopened&keywords=~fs&keywords=~FS&col=id&col=summary&col=owner&col=type&col=priority&col=milestone&col=component&col=time&col=reporter&desc=1&order=time       A list of items which can be shared with external developers to get a feel for what the current problems are with "Develop". At the moment, the "develop" branch is focused on FS (#9818) and therefore FS-related bugs are listed.
9       \N      Top-level open tickets (5.0)    SELECT t.type as __group__, t.id AS ticket, t.summary as summary, t.component as component, t.owner as owner, t.priority as priority,\r\n\r\n--- Case statemet for the priority coloring\r\n    (CASE t.priority\r\n       WHEN null THEN '--'\r\n       ELSE p.value\r\n    END) as __color__,\r\n\r\n--- Case statement for simple sub-estimates\r\n\r\n    (CASE t.type\r\n       WHEN 'task' THEN ''\r\n       ELSE\r\n           ( SELECT ''||count(al.src)\r\n               FROM agilo_link al\r\n              WHERE al.src = t.id )\r\n    END) as subtickets,\r\n\r\n-- Case statement for totals (only open in this milestone are included,\r\n-- if a ticket has no estimate it will be omitted)\r\n    (CASE t.type\r\n       WHEN 'task' THEN\r\n           (SELECT (case tc.value when null then '?' when '' then '?' else tc.value || 'd' end)\r\n            FROM ticket_custom tc\r\n            WHERE tc.ticket = t.id and tc.name = 'remaining_time') \r\n       WHEN 'story' THEN\r\n           ( SELECT count(al.src) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link al, ticket_custom tc, ticket t2\r\n              WHERE al.src=t.id\r\n              AND tc.ticket = t2.id\r\n              AND tc.name = 'remaining_time'\r\n              AND al.dest=t2.id\r\n              AND t2.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (t2.milestone = 'OMERO-5')\r\n           )\r\n       WHEN 'requirement' THEN\r\n           ( SELECT count(Task.id) ||'tix @ '||sum(\r\n              cast(\r\n                  (case tc.value when null then '0' when '' then '0' else tc.value end)\r\n               as float)\r\n              ) || 'd'\r\n              FROM agilo_link ReqToStory,\r\n                   ticket Story,\r\n                   agilo_link StoryToTask,\r\n                   ticket Task,\r\n                   ticket_custom tc\r\n              WHERE t.id = ReqToStory.src\r\n              AND ReqToStory.dest = Story.id\r\n              AND Story.id = StoryToTask.src\r\n              AND StoryToTask.dest = Task.id\r\n              AND tc.ticket = Task.id\r\n              AND tc.name = 'remaining_time'\r\n              AND Task.status in ('accepted', 'assigned', 'new', 'reopened')\r\n              AND (Task.milestone = 'OMERO-5')\r\n           )\r\n       ELSE '???'\r\n    END) as remaining\r\n\r\n   FROM ticket t\r\n   LEFT OUTER JOIN enum p\r\n     ON t.priority = p.name\r\n    AND p.type = 'priority'\r\n  WHERE t.status in ('accepted', 'assigned', 'new', 'reopened')\r\n    AND (t.milestone = 'OMERO-5')\r\n    AND t.id not in\r\n\r\n-- Can't be the target of a link from a ticket in this milestone\r\n (SELECT distinct al2.dest\r\n    FROM agilo_link al2, ticket t3\r\n   WHERE t3.status in ('accepted', 'assigned', 'new', 'reopened')\r\n     AND (t3.milestone = 'OMERO-5')\r\n     AND t3.id = al2.src\r\n  )\r\n\r\n  ORDER by t.type, t.priority, t.id\r\n    Tickets grouped into Requirements, Stories, Tasks which are '''NOT''' linked by an open, higher-level ticket in this milestone. (If any bugs have been created by accident these will appear first. Please change them to "Tasks").\r\n\r\nFrom this list, all tickets that are in this milestone are reachable. If a top-level item is closed or moved to another milestone BUT it still contains tickets in the current milestone, those will then appear.