Hi,
Below is one of my ticket report pages. I'm not entirely sure it's the best
approach but works fine for me. What it does is two selects. The inner
select classifies status, priority, severity and difficulty so then I can
order them appropriately. The outer select presents the data in human
readable way.
Note, that I added extra difficulty column to prioritize shorter tasks over
longer ones (you can easily remove that). Also, this report shows only
unclosed tickets assigned to the current user, again easy to change.
Hope it helps.
Cheers,
Jacek
===
SELECT
CASE WHEN status IN ('Open','Verified') THEN '#ffeedd'
WHEN status='Blocked' THEN '#ff'
WHEN status='Review' THEN '#e8e8e8'
WHEN status='Fixed' THEN '#cfffbd'
WHEN status='Tested' THEN '#bdffd6'
WHEN status='Deferred' THEN '#aabaff'
ELSE '#b8b8b8' END AS 'bgcolor',
substr(tkt_uuid,1,10) AS '#',
priority,
severity,
difficulty,
type,
status,
assigned_to,
subsystem,
CASE WHEN length(title) > 60 THEN substr(title,0,57) || '...'
ELSE title END AS 'title'
FROM (
SELECT *,
CASE WHEN status IN ('Open','Verified','Blocked') THEN 0
WHEN status='Review' THEN 1
WHEN status='Fixed' THEN 2
WHEN status='Tested' THEN 3
WHEN status='Deferred' THEN 4
ELSE 5 END AS 'st',
CASE WHEN priority='Immediate' THEN 0
WHEN priority='High' THEN 1
WHEN priority='Medium' THEN 2
WHEN priority='Low' THEN 3
ELSE 4 END AS 'p',
CASE WHEN severity='Critical' THEN 0
WHEN severity='Severe' THEN 1
WHEN severity='Important' THEN 2
WHEN severity='Minor' THEN 3
ELSE 4 END AS 's',
CASE WHEN difficulty='Very_Easy_(0-15mins)' THEN 0
WHEN difficulty='Easy_(15min-2hr)' THEN 1
WHEN difficulty='Medium_(2hr-2days)' THEN 2
WHEN difficulty='Hard_(2days-2weeks)' THEN 3
ELSE 4 END AS 'd'
FROM ticket
WHERE status != 'Closed' AND (assigned_to = user() OR assigned_to =
'unassigned' OR assigned_to IS NULL)
ORDER BY st, p, s, d, tkt_mtime)
==
2014-10-12 22:00 GMT+01:00 :
> 'Lo.
>
> Currently, if I do, in a ticket report:
>
> ORDER BY priority, severity
>
> I definitely get something ordered by priority and severity, but of
> course the ordering relation for both columns is lexicographical. That
> is, "Important" > "Critical" because "Critical" appears earlier in the
> alphabet.
>
> That's pretty awful! Is there a recommended way to get a better
> ordering relation without having to mutilate the ticket system too
> much?
>
> M
> ___
> fossil-users mailing list
> fossil-users@lists.fossil-scm.org
> http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
>
___
fossil-users mailing list
fossil-users@lists.fossil-scm.org
http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users