BEGIN TRANSACTION; DELETE FROM report WHERE id <= 8; INSERT INTO report VALUES(1,NULL,'未解決チケット',' SELECT p.value AS __color__, id AS ticket, summary, component, version, milestone, t.type AS type, (CASE status WHEN ''assigned'' THEN owner||'' *'' ELSE owner END) AS owner, time AS created, changetime AS _changetime, description AS _description, reporter AS _reporter FROM ticket t, enum p WHERE status IN (''new'', ''assigned'', ''reopened'') AND p.name = t.priority AND p.type = ''priority'' ORDER BY p.value, milestone, t.type, time ','*'' が付与されます。 '); INSERT INTO report VALUES(2,NULL,'未解決チケット(バージョン別)',' SELECT p.value AS __color__, version AS __group__, id AS ticket, summary, component, version, t.type AS type, (CASE status WHEN ''assigned'' THEN owner||'' *'' ELSE owner END) AS owner, time AS created, changetime AS _changetime, description AS _description, reporter AS _reporter FROM ticket t, enum p WHERE status IN (''new'', ''assigned'', ''reopened'') AND p.name = t.priority AND p.type = ''priority'' ORDER BY (version IS NULL),version, p.value, t.type, time ',' このレポートはバージョン別にグルーピングする時、 優先度に色付けを行うやり方の例です。 最終更新日時、チケットの説明、報告者が隠しフィールドとして含まれています。 これらのフィールドはWebブラウザでは表示されませんが、RSSには出力されます。 '); INSERT INTO report VALUES(3,NULL,'未解決チケット(マイルストーン別)',' SELECT p.value AS __color__, milestone||'' Release'' AS __group__, id AS ticket, summary, component, version, t.type AS type, (CASE status WHEN ''assigned'' THEN owner||'' *'' ELSE owner END) AS owner, time AS created, changetime AS _changetime, description AS _description, reporter AS _reporter FROM ticket t, enum p WHERE status IN (''new'', ''assigned'', ''reopened'') AND p.name = t.priority AND p.type = ''priority'' ORDER BY (milestone IS NULL),milestone, p.value, t.type, time ',' このレポートはマイルストーン別にグルーピングする時、 優先度に色付けを行うやり方の例です。 最終更新日時、チケットの説明、報告者が隠しフィールドとして含まれています。 これらのフィールドはWebブラウザでは表示されませんが、RSSには出力されます。 '); INSERT INTO report VALUES(4,NULL,'担当者別アサイン済みチケット',' SELECT p.value AS __color__, owner AS __group__, id AS ticket, summary, component, milestone, t.type AS type, time AS created, changetime AS _changetime, description AS _description, reporter AS _reporter FROM ticket t,enum p WHERE status = ''assigned'' AND p.name=t.priority AND p.type=''priority'' ORDER BY owner, p.value, t.type, time ',' 担当者別に優先度順に並べた、アサイン済みチケットの一覧です。 '); INSERT INTO report VALUES(5,NULL,'担当者別アサイン済みチケット(説明文付き)',' SELECT p.value AS __color__, owner AS __group__, id AS ticket, summary, component, milestone, t.type AS type, time AS created, description AS _description_, changetime AS _changetime, reporter AS _reporter FROM ticket t, enum p WHERE status = ''assigned'' AND p.name = t.priority AND p.type = ''priority'' ORDER BY owner, p.value, t.type, time ',' 担当者別に優先度順に並べた、アサイン済みチケットの一覧です。 このレポートでは、全列結合表示の使用例です。 '); INSERT INTO report VALUES(6,NULL,'マイルストーン別全チケット(解決済みチケットを含む)',' SELECT p.value AS __color__, t.milestone AS __group__, (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__, id AS ticket, summary, component, status, resolution,version, t.type AS type, priority, owner, changetime AS modified, time AS _time,reporter AS _reporter FROM ticket t,enum p WHERE p.name=t.priority AND p.type=''priority'' ORDER BY (milestone IS NULL), milestone DESC, (status = ''closed''), (CASE status WHEN ''closed'' THEN modified ELSE (-1)*p.value END) DESC ',' 高度なレポートを作成するための例です。 '); INSERT INTO report VALUES(7,NULL,'私の未解決チケット',' SELECT p.value AS __color__, (CASE status WHEN ''assigned'' THEN ''Assigned'' ELSE ''Owned'' END) AS __group__, id AS ticket, summary, component, version, milestone, t.type AS type, priority, time AS created, changetime AS _changetime, description AS _description, reporter AS _reporter FROM ticket t, enum p WHERE t.status IN (''new'', ''assigned'', ''reopened'') AND p.name = t.priority AND p.type = ''priority'' AND owner = ''$USER'' ORDER BY (status = ''assigned'') DESC, p.value, milestone, t.type, time ',' このレポートは、実行される際のログインユーザ名で、 動的に置き換えられる変数 $USER を使用した例です。 '); INSERT INTO report VALUES(8,NULL,'未解決チケット(私のチケットを優先して表示)',' SELECT p.value AS __color__, (CASE owner WHEN ''$USER'' THEN ''My Tickets'' ELSE ''Active Tickets'' END) AS __group__, id AS ticket, summary, component, version, milestone, t.type AS type, (CASE status WHEN ''assigned'' THEN owner||'' *'' ELSE owner END) AS owner, time AS created, changetime AS _changetime, description AS _description, reporter AS _reporter FROM ticket t, enum p WHERE status IN (''new'', ''assigned'', ''reopened'') AND p.name = t.priority AND p.type = ''priority'' ORDER BY (owner = ''$USER'') DESC, p.value, milestone, t.type, time ',' * 全ての未解決チケットを優先度順に表示します。 * ログインユーザが担当者になっているチケットを最初のグループで表示します。 '); COMMIT;