-- ## 42: Active tickets for 1.0, by last modified date ## --

-- '''Those are the tickets for the next Trac release, [milestone:1.0], [milestone:1.0.1], [milestone:1.0.2], etc.'''
-- 
-- Notes:
--  - the ''[query:?milestone=1.0&keywords=~needinfo&status=!closed needinfo]'' tickets have been excluded from this report (see also {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.version, 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 - t.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 _milestone
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 LIKE '1.0%' OR t.milestone = 'next-stable-1.0.x')
ORDER BY changetime desc, p.value, t.severity, t.time