gistfile1.txt

SELECT COUNT(*) FROM (
SELECT
  CASE
  WHEN keywords LIKE '%needs_unit_tests%' OR keywords LIKE '%needs_docs%'
  THEN 3
  WHEN keywords LIKE '%close%' OR keywords LIKE '%needs_review%'
    OR keywords LIKE '%dev_feedback%' OR keywords LIKE '%2nd_opinion%'
  THEN 5
  WHEN keywords LIKE '%reporter_feedback%'
  THEN 3
  WHEN keywords LIKE '%commit%' OR keywords LIKE '%tested%'
  THEN 1
  WHEN keywords LIKE '%early%'
  THEN 2
  ELSE 3
  END as __color__,
  CASE
  WHEN LOWER(reporter) = LOWER(%s)
    AND LOWER(COALESCE(t.owner, '')) <> LOWER(%s) AND c.name IS NULL
  THEN 'Reported'
  WHEN milestone <= ( SELECT min(name) FROM milestone WHERE completed = 0 AND name NOT LIKE '%.%.%' )
  THEN 'Next Release'
  ELSE 'Future Releases'
  END as __group__,
  t.id AS ticket,
  summary,
  CASE
  WHEN status = 'accepted'
  THEN CONCAT(t.owner, '*')
  WHEN t.owner <> 'anonymous'
  THEN t.owner
  ELSE ''
  END as owner,
  component,
  version as _version,
  priority as priority,
  severity,
  milestone as milestone,
  t.type AS type,
  status AS _status,
  CASE
  WHEN keywords LIKE '%needs_unit_tests%'
  THEN 'needs-unit-tests'
  WHEN keywords LIKE '%needs_docs%'
  THEN 'needs-docs'
  WHEN keywords LIKE '%close%'
  THEN 'close'
  WHEN keywords LIKE '%needs_review%'
  THEN 'needs-review'
  WHEN keywords LIKE '%dev_feedback%' OR keywords LIKE '%2nd_opinion%'
  THEN 'dev-feedback'
  WHEN keywords LIKE '%reporter_feedback%'
  THEN 'reporter-feedback'
  WHEN keywords LIKE '%commit%'
  THEN 'commit'
  WHEN keywords LIKE '%tested%'
  THEN 'tested'
  WHEN keywords LIKE '%has_patch%'
    OR keywords LIKE '%needs_testing%' AND keywords NOT LIKE '%needs_patch%'
  THEN 'has-patch'
  WHEN keywords LIKE '%early%'
  THEN 'early'
  ELSE ''
  END as workflow,
  t.time AS _created,
  changetime AS modified,
  t.description AS _description,
  reporter AS _reporter
FROM ticket t
LEFT JOIN component c ON c.name = t.component AND LOWER(c.owner) = LOWER(%s)
WHERE status <> 'closed' 
  AND ( LOWER(t.owner) = LOWER(%s)
   OR c.name IS NOT NULL
   OR LOWER(reporter) = LOWER(%s)
   )
ORDER BY CASE
  WHEN LOWER(COALESCE(t.owner, '')) <> LOWER(%s) AND c.name IS NULL
    AND LOWER(reporter) = LOWER(%s)
  THEN 3
  WHEN milestone <= ( SELECT min(name) FROM milestone WHERE completed = 0 AND name NOT LIKE '%.%.%' )
  THEN 1
  ELSE 2
  END,
  Component, t.type, summary

) AS tab