View Tickets - SQL Query for Report

{1}활성화된 모든 티켓들Active Tickets

* 활성화된 전체 티켓들을 볼 수 있습니다.
* 급한 티켓은 각 색을 기본으로 정렬됩니다.
* 티켓이 통과되어지면, 담당자의 이름 뒤에 '*' 가 붙습니다.

* List all active tickets by priority.
* Color each row based on priority.
* If a ticket has been accepted, a '*' is appended after the owner's name
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
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened')
ORDER BY p.value, milestone, t.type, time


{2}Active Tickets by Version
This report shows how to color results by priority,
while grouping results by version.

Last modification time, description and reporter are included as hidden fields
for useful RSS export.
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
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened')
ORDER BY (version IS NULL),version, p.value, t.type, time



{3}마일스톤 별 활성화된 모든 티켓들Active Tickets by Milestone
마일스톤 별 활성화된 모든 티켓들을 보여줍니다.

This report shows how to color results by priority,
while grouping results by milestone.

Last modification time, description and reporter are included as hidden fields
for useful RSS export.
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
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened')
ORDER BY (milestone IS NULL),milestone, p.value, t.type, time



{4}담당자별 활성화된 티켓들Assigned, Active Tickets by Owner

담당자별로 활성화된 티켓들을 보여줍니다.

List assigned tickets, group by ticket owner, sorted by priority.
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
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status = 'assigned'
ORDER BY owner, p.value, t.type, time

{5}
담당자별 활성화된 티켓들 자세히
Assigned, Active Tickets by Owner (Full Description)

담당자별로 활성화된 티켓들을 보여줍니다.

List tickets assigned, group by ticket owner.
This report demonstrates the use of full-row display.

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
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status = 'assigned'
ORDER BY owner, p.value, t.type, time



{6}
마일스톤별 모든 티켓들
All Tickets By Milestone (Including closed)

마일스톤별로 해결된 티켓들까지 모두 보여줍니다.

A more complex example to show how to make advanced reports.

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
LEFT JOIN enum p ON 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


{7}
나에게 할당된 티켓들


이 리포트는 실행될 때 로그온한 사용자이름으로 자동으로 변경되는, $USER 동적 변수의 사용 방법을 보여줍니다.

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 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
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE t.status IN ('new', 'assigned', 'reopened') AND owner = $USER
ORDER BY (status = 'assigned') DESC, p.value, milestone, t.type, time


{8}활성화된 티켓, 나에게 할당된 티켓들을 먼저 보여줍니다 Active Tickets, Mine first

* 우선순위별로 활성화된 모든 티켓들을 보여줍니다.
* 로그인한 사용자가 소유한 티켓들을 먼저 보여줍니다.

* List all active tickets by priority.
* Show all tickets owned by the logged in user in a group first.

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
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
WHERE status IN ('new', 'assigned', 'reopened')
ORDER BY (owner = $USER) DESC, p.value, milestone, t.type, time


-