-- ## 43: Active tickets for Trac 1.4, by last modified date ## --

-- '''Those are the tickets for the next Trac release, [milestone:1.4], [milestone:1.4.1], [milestone:1.4.2], etc.'''
-- 
-- 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,
  id AS ticket, summary, component, version, t.severity AS severity,
  owner, 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,
  reporter AS _reporter,
  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.4%' 
       OR t.milestone in ('next-dev-1.3.x', 'next-stable-1.4.x'))
ORDER BY changetime desc, p.value, t.severity, time