-- ## 13: All related to current user ## --

-- Query all reports related to current user (a bit deeper than the normal related to user query).

SELECT 
  DISTINCT 
   p.value AS __color__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
    END) AS __style__,
   id AS ticket, summary, component, milestone, status, resolution, 
   t.time AS created, changetime AS modified,
   priority AS _priority, reporter AS _reporter, cc AS _cc,
   CASE status WHEN 'closed' THEN 1 ELSE 0 END AS _status,
   CASE status WHEN 'closed' THEN changetime ELSE (-1) * CAST(p.value AS int) END AS _ctime
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_change tc ON id = tc.ticket
  WHERE reporter = '$USER' 
   OR cc LIKE '$USER,%'
   OR cc LIKE '% $USER'
   OR cc LIKE '% $USER,%'
   OR cc = '$USER'
   OR tc.author='$USER'
  ORDER BY
    _status,
    _ctime DESC,
    changetime DESC