-- ## 29: All tickets of your interest (only shows closed) ## --

-- 
-- 
-- http://stackoverflow.com/questions/193341/how-can-i-best-take-advantage-of-trac/534447#534447

  SELECT p.value AS __color__,
   (CASE owner WHEN '$USER' THEN 
     (CASE status 
       WHEN 'assigned' 
       THEN 'Tickets that you accepted' 
       ELSE 'Tickets that were assigned to you, please accept or reassign' 
      END) 
     ELSE 'Tickets, that have your name in the cc' END) 
     AS __group__,
   id AS ticket, summary, component, version, milestone,
   tic.type AS type, priority, time AS created,
   changetime AS _changetime, description AS _description,
   reporter AS _reporter
  FROM ticket tic
  LEFT JOIN enum p ON p.name = tic.priority AND p.type = 'priority'
  WHERE tic.status = 'closed' AND (owner = '$USER' OR cc like '%$USER%')
  ORDER BY owner, (status = 'assigned') DESC, p.value, milestone, tic.type, time