-- ## 41: My Tickets (user centric) ## --

-- Tickets of interest as a user. That is, tickets that the current user reported, commented on, or marked for CC.

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,
   (status = 'closed') AS _is_closed,
   (CASE status
       WHEN 'closed' THEN changetime
       ELSE (-1) * CAST(p.value AS int)
    END) AS _close_time_or_open_neg_prio
  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' AND tc.field = 'comment')
  ORDER BY _is_closed,  _close_time_or_open_neg_prio DESC