SELECT p.value AS __color__,
(CASE status
WHEN 'assigned' THEN '速攻で片付けろ'
ELSE '分割してからAssignしろ'
END) AS __group__,
id AS ticket, summary, component,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
changetime AS _changetime, description AS _description,
reporter AS _reporter,
(CASE WHEN c.value = 'YYYY/MM/DD' THEN 'None' ELSE c.value END) AS 開始日,
(CASE WHEN d.value = 'YYYY/MM/DD' THEN 'None' ELSE d.value END) AS 終了日
FROM ticket t
LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name = 'due_assign')
LEFT OUTER JOIN ticket_custom d ON (t.id = d.ticket AND d.name = 'due_close')
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened')
ORDER BY (status = 'assigned') DESC, d.value, c.value, p.value
