顛末付きレポート
TracのレポートはSQLで何でもかけて非常に便利。
だけどwiki構文をparseして表示するdescriptionは一つしか表示出来ないので、よくある障碍内容と解決策を一覧にした表が作れない。
でも以下のSQLをコピペすれば大丈夫!
チケットの概要と最終コメントが一覧になって表示されます!
引用で異常に横に長いものがあると、恐ろしく横長な表になるのはご愛敬(汗)
#□を全角スペースに置き換えて、ご利用ください。
-- ## 顛末付きチケット一覧サンプル ## --
-- * 優先度別の色付けを行っています。
-- * チケットに着手済みであれば、担当者名に '*' が付与されます。
-- * 概要及び最終コメントが表示されます。SELECT
status as 状況,
p.value AS __color__,
(CASE status
WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
ELSE
(CASE owner WHEN '$USER' THEN 'font-weight: bold' END)
END) AS __style__,component as コンポーネント,
t.type AS 分類,
id AS ticket, summary,
time as created,
reporter AS 報告者,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS _担当者,
changetime AS _changetime,
reporter AS _reporter,
(select count(*) from ticket_change tc
where tc.ticket = t.id
and field='comment'
and newvalue<>''
) as *,
(CASE
(select count(*)
from ticket_change
where ticket = t.id
and field='comment'
and newvalue<>''
)
WHEN 0
THEN
(t.description||'BR`□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□`')
ELSE
(description ||'BR'||'□'||'||'||'最終コメント'||
(select tc1.author
from ticket_change tc1
where tc1.ticket = t.id and tc1.field='comment'
and tc1.time=(
SELECT time FROM ticket_change
where ticket = t.id
AND field = 'comment'
and newvalue<>''
order by time desc limit 1 offset 0
)
)
||'||'||' '||
(select date(tc2.time, 'unixepoch', 'localtime')
from ticket_change tc2
where tc2.ticket = t.id and tc2.field='comment'
and tc2.time=(
SELECT time FROM ticket_change
where ticket = t.id
AND field = 'comment'
and newvalue<>''
order by time desc limit 1 offset 0
)
)
||'`□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□`BRBR'||
(select tc3.newvalue from ticket_change tc3
where tc3.ticket = t.id and tc3.field='comment'
and tc3.time=(
SELECT time FROM ticket_change
where ticket = t.id
AND field = 'comment'
and newvalue<>''
order by time desc limit 1 offset 0
)
)
)
END
) as description_
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
LEFT JOIN enum tp ON tp.name = t.type and tp.type = 'ticket_type'
LEFT JOIN enum sts ON sts.name= status and sts.type= 'status'ORDER BY
(status = 'closed'),
(CASE status WHEN 'closed' THEN changetime ELSE (-1)*p.value END) DESC
,p.value,sts.value, t.type, time