-- ## 24: Active tickets for future Trac releases, by last modified date ## --

-- **Those are the tickets for the upcoming Trac releases, [milestone:next-dev-1.3.x], [milestone:next-major-releases]* **
-- 
-- Valid tickets for which no release schedule can be set are also listed here ([milestone:"not applicable"]).
-- 
-- Notes:
--  - the ''needinfo'' tickets have been excluded from this report (see {21}).
--  - the ''Chgs_Auths'' field corresponds to: ''(number of changes) by (number of different change authors)'', and can be used as an indicator of the popularity of the issue

-- Note: this is for a PostgreSQL database, things like:
--
--         (SELECT extract(epoch from current_timestamp) as now) time
--
--       probably won't work with other databases.

SELECT p.value AS __color__,
  t.type,
  t.id AS ticket, t.summary, t.component, t.severity AS severity,
  t.owner, t.status,
  (SELECT (CASE
           WHEN delta > 86400 THEN round(delta/86400.0) || ' days'
           WHEN delta > 3600  THEN round(delta/3600.0) || ' hours'
           ELSE round(delta/60) || ' mins' 
           END)
   FROM (SELECT ((time.now * 1000000 - changetime) / 1000000.0) AS delta
        ) AS ct
  ) AS LastModified,
  COALESCE(tc.change_cnt || ' by ' || tc.author_cnt, '-') as Chgs_Auths,
  t.description AS _description,
  t.reporter AS _reporter,
  t.milestone AS __group__
FROM ticket t LEFT JOIN 
   (SELECT ticket, count(distinct author) as author_cnt, 
          count(*) as change_cnt
    FROM ticket_change 
    WHERE field = 'comment' GROUP BY ticket) tc ON (t.id = tc.ticket), 
   enum p, 
   (SELECT extract(epoch from current_timestamp) as now) time
WHERE t.status != 'closed' 
  AND (t.keywords IS NULL OR t.keywords NOT LIKE '%needinfo%')
  AND p.name = t.priority AND p.type = 'priority'
  AND t.milestone in ('next-dev-1.3.x', 'next-major-releases', 
                      'not applicable', '2.0') 
ORDER BY t.milestone, t.changetime desc, p.value, t.severity, t.time