Help optimizing Tickets->Limit queries

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:

white -> no tickets for that requestor
red -> some new/open tickets for that requestor found
green -> only resolved tickets for that requestor
yellow -> tickets for that requestor that are not new/open/resolved
(matching stalled/rejected tickets found)

|sub get_colour {
my ($email,$current_user) = @_;
my ($colour,$remaining_tickets_count);
my $tickets = new RT::Tickets($current_user);
$tickets->LimitWatcher(TYPE => ‘Requestor’, VALUE => $email);
my $total_tickets_count = $tickets->Count();
if($total_tickets_count > 0) {
#$RT::Logger->debug("$total_tickets_count tickets for $email");
$tickets = new RT::Tickets($current_user);
$tickets->LimitWatcher(TYPE => ‘Requestor’, VALUE => $email);
$tickets->LimitStatus(VALUE => ‘open’);
$tickets->LimitStatus(VALUE => ‘new’);
$remaining_tickets_count = $tickets->Count();
#$RT::Logger->debug("$remaining_tickets_count open/new tickets for
$email.");
if($remaining_tickets_count > 0) {
#There are open/new tickets for this requestor
$colour = ‘red’;
} else {
$tickets = new RT::Tickets($current_user);
$tickets->LimitWatcher(TYPE => ‘Requestor’, VALUE => $email);
$tickets->LimitStatus(VALUE => ‘resolved’);
$remaining_tickets_count = $tickets->Count();
#$RT::Logger->debug("$remaining_tickets_count resolved tickets for
$email.");
if($remaining_tickets_count == $total_tickets_count) {
#All the tickets are resolved
$colour = ‘green’;
} else {
#There must be unresolved tickets here
$colour = ‘yellow’;
}
}
} else {
#there were no tickets at all.
$colour = ‘white’;
}
}|

A query similar to the following would at least be efficient sql (except
that I want the requestor, not the owner)
SELECT Status,COUNT(*) FROM Tickets where Tickets.Owner = ‘12’ GROUP BY
Status;

Any help very much appreciated!

Paul Boldra
ticketsystem.de