#14:
SELECT COUNT(*) FROM (
SELECT
ticket,
newvalue
FROM ticket_change
WHERE FIELD = 'comment' AND (
newvalue LIKE '=%NO%PATCH%NO%FIX%' OR
newvalue LIKE '=%!%=' OR
newvalue LIKE '% SPAMMER %' OR
newvalue LIKE '% TROLL %' OR
newvalue LIKE '%Where is your patch?%' OR
newvalue LIKE '%<a href=%' OR
newvalue LIKE '%[url=%'
)
GROUP BY ticket ORDER BY TIME DESC, ticket DESC
) AS tab
#13:
SELECT COUNT(*) FROM (
SELECT p.VALUE AS __color__,
id AS ticket, summary, component, version, milestone,
(CASE STATUS WHEN 'assigned' THEN owner || ' *' ELSE owner END) AS owner,
(CASE WHEN STRFTIME('%s','now') - (changetime / 1000000) > 86400
THEN CAST((STRFTIME('%s','now') - (changetime / 1000000))/86400 AS INTEGER) || ' days'
WHEN STRFTIME('%s','now') - (changetime / 1000000) > 3600
THEN CAST((STRFTIME('%s','now') - (changetime / 1000000))/3600 AS INTEGER) || ' hours'
ELSE CAST((STRFTIME('%s','now') - (changetime / 1000000))/60 AS INTEGER) || ' mins'
END) AS Modified,
(CASE WHEN author LIKE '%@%' THEN SUBSTR(author, 0, 3) || '...' ELSE author END) AS Modified_By,
description AS _description,
reporter AS _reporter
FROM
ticket t, enum p,
(SELECT ticket, author, MAX(TIME) FROM ticket_change WHERE FIELD = 'comment' GROUP BY ticket) tc
WHERE STATUS IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.TYPE = 'priority'
AND t.id = tc.ticket
ORDER BY changetime DESC, p.VALUE, milestone, TIME
) AS tab
#6:
SELECT COUNT(*) FROM (
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 ''||%s||'' THEN 'font-weight: bold' END)
END) AS __style__,
id AS ticket, summary, component, STATUS,
resolution,version, t.TYPE AS TYPE, severity, priority, owner,
changetime AS modified,
TIME AS _time,reporter AS _reporter
FROM ticket t,enum p
WHERE p.name=t.priority AND p.TYPE='priority'
ORDER BY (milestone IS NULL), milestone DESC, (STATUS = 'closed'),
(CASE STATUS WHEN 'closed' THEN modified ELSE -p.VALUE END) DESC
) AS tab