とた日記

_ TracLightning {9} 未解決チケット(進捗確認用) でユーザIDでなくユーザ名称でレポートを表示する

ユーザアカウントを社員番号にしまったためレポートを見ても誰が担当しているチケットかが分かりづらくなってしまった．そこで以下のように書き換えたところそれっぽく表示されるようになった．

SELECT (CASE owner WHEN 'somebody' THEN '未割当' ELSE sa.value END) as __group__,
   id AS ticket,
   summary as '概要　　　　',
   a.value as '開始予定日',
   c.value as '終了予定日',
   (CASE status WHEN 'assigned' THEN d.value||' *' ELSE d.value END) AS '達成率',
   t.type AS 'タイプ　', 
   t.priority as '優先度',
   changetime AS _changetime, description AS _description,
   reporter AS _reporter,
   (CASE  WHEN c.value ='' THEN 5
          WHEN c.value < strftime('%Y/%m/%d','now') THEN 1
          WHEN c.value < strftime('%Y/%m/%d','now', '7 day') THEN 2
          ELSE 3 END) AS __color__
  FROM ticket t
  LEFT JOIN session_attribute sa ON sa.sid = owner AND sa.name = 'name'
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN ticket_custom a ON a.ticket = t.id AND a.name = 'due_assign' 
  LEFT JOIN ticket_custom c ON c.ticket = t.id AND c.name = 'due_close' 
  LEFT JOIN ticket_custom d ON d.ticket = t.id AND d.name = 'complete'
  WHERE status IN ('new', 'assigned', 'accepted', 'reopened') 
  ORDER BY owner, a.value, p.value, milestone, t.type, time