<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
I'm sure this code is terrible; if for no other reason than it
instantiates three RT::Tickets objects. The purpose is to display a
coloured gif on an related website based on the status of any tickets
in RT with a matching requestor, all according to the following key:<br>
<br>
white -> no tickets for that requestor<br>
red -> some new/open tickets for that requestor found<br>
green -> only resolved tickets for that requestor<br>
yellow -> tickets for that requestor that are not new/open/resolved
(matching stalled/rejected tickets found)<br>
<br>
<code>sub get_colour {<br>
my ($email,$current_user) = @_;<br>
my ($colour,$remaining_tickets_count);<br>
my $tickets = new RT::Tickets($current_user);<br>
$tickets->LimitWatcher(TYPE => 'Requestor', VALUE => $email);<br>
my $total_tickets_count = $tickets->Count();<br>
if($total_tickets_count > 0) {<br>
#$RT::Logger->debug("$total_tickets_count tickets for $email");<br>
$tickets = new RT::Tickets($current_user);<br>
$tickets->LimitWatcher(TYPE => 'Requestor', VALUE =>
$email);<br>
$tickets->LimitStatus(VALUE => 'open'); <br>
$tickets->LimitStatus(VALUE => 'new'); <br>
$remaining_tickets_count = $tickets->Count();<br>
#$RT::Logger->debug("$remaining_tickets_count open/new tickets
for $email.");<br>
if($remaining_tickets_count > 0) {<br>
#There are open/new tickets for this requestor<br>
$colour = 'red';<br>
} else {<br>
$tickets = new RT::Tickets($current_user);<br>
$tickets->LimitWatcher(TYPE => 'Requestor', VALUE =>
$email);<br>
$tickets->LimitStatus(VALUE => 'resolved');<br>
$remaining_tickets_count = $tickets->Count();<br>
#$RT::Logger->debug("$remaining_tickets_count resolved tickets
for $email.");<br>
if($remaining_tickets_count == $total_tickets_count) {<br>
#All the tickets are resolved<br>
$colour = 'green';<br>
} else {<br>
#There must be unresolved tickets here<br>
$colour = 'yellow';<br>
}<br>
}<br>
} else {<br>
#there were no tickets at all.<br>
$colour = 'white';<br>
}<br>
}</code><br>
<br>
A query similar to the following would at least be efficient sql
(except that I want the requestor, not the owner)<br>
SELECT Status,COUNT(*) FROM Tickets where Tickets.Owner = '12' GROUP BY
Status;<br>
<br>
Any help very much appreciated!<br>
<br>
Paul Boldra<br>
ticketsystem.de<br>
</body>
</html>