-- ## 7: My Tickets ## -- -- This report demonstrates the use of the automatically set -- USER dynamic variable, replaced with the username of the -- logged in user when executed. SELECT p.value AS __color__, (CASE WHEN owner = $USER AND status = 'accepted' THEN 'Accepted' WHEN owner = $USER THEN 'Owned' WHEN reporter = $USER THEN 'Reported' ELSE 'Commented' END) AS __group__, t.id AS ticket, summary, component, version, milestone, t.type AS type, priority, t.time AS created, t.changetime AS _changetime, t.description AS _description, reporter AS _reporter FROM ticket t LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' WHERE t.status <> 'closed' AND (owner = $USER OR reporter = $USER OR EXISTS (SELECT * FROM ticket_change tc WHERE tc.ticket = t.id AND tc.author = $USER AND tc.field = 'comment')) ORDER BY (COALESCE(owner, '') = $USER AND status = 'accepted') DESC, COALESCE(owner, '') = $USER DESC, COALESCE(reporter, '') = $USER DESC, CAST(p.value AS signed), milestone, t.type, t.time