# report

title: Active Tickets
description:
  * List all active tickets by priority.
  * Color each row based on priority.

query:
  SELECT p.value AS __color__,
     id AS ticket, summary, component, version, milestone, t.type AS type, 
     owner, status,
     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 <> 'closed'
    ORDER BY CAST(p.value AS integer), milestone, t.type, time


title: Active Tickets by Version
query:
  SELECT p.value AS __color__,
     version AS __group__,
     id AS ticket, summary, component, version, t.type AS type, 
     owner, status,
     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 <> 'closed'
    ORDER BY (version IS NULL),version, CAST(p.value AS integer), t.type, time

description:
  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.


title: Active Tickets by Milestone
query:
  SELECT p.value AS __color__,
     'Milestone '||milestone AS __group__,
     id AS ticket, summary, component, version, t.type AS type, 
     owner, status,
     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 <> 'closed' 
    ORDER BY (milestone IS NULL),milestone, CAST(p.value AS integer), t.type, time

description:
  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.


title: Accepted, Active Tickets by Owner
description: List accepted tickets, group by ticket owner, sorted by priority.
query:
  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 = 'accepted'
    ORDER BY owner, CAST(p.value AS integer), t.type, time


title: Accepted, Active Tickets by Owner (Full Description)
query:
  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 = 'accepted'
    ORDER BY owner, CAST(p.value AS integer), t.type, time

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


title: All Tickets By Milestone (Including closed)
description: A more complex example to show how to make advanced reports.
query:
  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 changetime ELSE (-1) * CAST(p.value AS integer) END) DESC


title: My Tickets
query:
  SELECT p.value AS __color__,
     (CASE status WHEN 'accepted' THEN 'Accepted' 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 <> 'closed' AND owner = $USER
    ORDER BY (status = 'accepted') DESC, CAST(p.value AS integer), milestone, t.type, time

description:
  This report demonstrates the use of the automatically set 
  USER dynamic variable, replaced with the username of the
  logged in user when executed.


title: Active Tickets, Mine first
query:
  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, 
     owner, status,
     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 <> 'closed' 
    ORDER BY (COALESCE(owner, '') = $USER) DESC, CAST(p.value AS integer), milestone, t.type, time

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


