Problems using LimitXXX Statements

Hi everybody,

I am having problems with LimitKeyword and LimitDate statements when
using them in reports or cronjobs to generate mail. Here are the two
examples:

  1. I was trying to write a report component, that gives me back the
    number
    of tickets, that are having a specific keyword assigned to them, but I
    did not succeed. All I managed is to get either the total number of
    tickets or zero.

I set up the keyword tree like this and assigned the keyword tree
global, single childs. =20

LeadStatus(1)
LeadStatus/positive(2)
LeadStatus/negative(3)

Afterwards i looked in the SQL Table KeywordSelects, for the Id to use
in LimitKeyword. Then I used this code:

<%PERL>
my $num=3D0;
my $open =3D new RT::Tickets($session{‘CurrentUser’});

$open->LimitStatus(VALUE =3D> ‘resolved’);
$open->LimitStatus(VALUE =3D> ‘open’);
$open->LimitStatus(VALUE =3D> ‘new’);

$open->LimitKeyword(KEYWORD =3D> 2, OPERATOR =3D> ‘IS’, KEYWORDSELECT =
=3D> 1);
$open->OrderBy(FIELD =3D> ‘Created’, ORDER =3D> ‘asc’);

$num =3D $open->Count();

</%PERL>
<%$num%>

  1. The second thing was a script the should be run by cron and send a
    mail to every user owning at ticket with a due date of today. I modified
    tickets to very date one could think of and user all operators (=><) but
    i allways got the same tickets or no ticket. Any ideas?

use strict;
use Carp;

use lib “/opt/rt2/lib”;
use lib “/opt/rt2/etc”;

use RT::Interface::CLI qw(CleanEnv LoadConfig DBConnect
GetCurrentUser GetMessageContent);

#Clean out all the nasties from the environment
CleanEnv();

#Load etc/config.pm and drop privs
LoadConfig();

#Connect to the database and get RT::SystemUser and RT::Nobody loaded
DBConnect();

use RT::Date;
use RT::Tickets;

my $now = new RT::Date($RT::SystemUser);
$now->SetToNow();

my $tickets = new RT::Tickets($RT::SystemUser);
$tickets->LimitStatus(VALUE => ‘open’);
$tickets->LimitStatus(VALUE => ‘new’);
$tickets->LimitStatus(VALUE => ‘stalled’);
$tickets->LimitDate(FIELD => ‘Due’, OPERATOR => ‘=’, VALUE => $now);

while (my $ticket = $tickets->Next) {

    open(MAIL,"|/usr/sbin/sendmail -t");   
    print MAIL $ticket->Id."\n";
    print MAIL "X-Priority: 1\n";
    print MAIL "Priority: Urgent\n";
    print MAIL "Importance: high\n";
    print MAIL "From:\n";
    print MAIL "To:jhein\@netways.de\n";
    print MAIL "Subject: [netways #".$ticket->Id."] Ticket

due!\n\n";
print MAIL “The following ticket has reached its due date.
Please resolve imediatly!\n\n”;
print MAIL " Ticket: [netways #".$ticket->Id."]\n";
print MAIL " Queue: “.$ticket->QueueObj->Name.”\n";
print MAIL " Subject: “.$ticket->Subject.”\n";
print MAIL " Owner: “.$ticket->OwnerAsString.”\n";
print MAIL " Requestor:
".$ticket->Requestors->EmailsAsString."\n";
print MAIL “Ticket URL:
http://www.rt.netways.de/rt2/Ticket/Display.html?id=".$ticket->Id."\n”;
print MAIL " DueDate: “.$ticket->Due.”\n";
close(MAIL);
}

$RT::Handle->Disconnect();

Thanks a lot, Julian
Julian Hein NETWAYS GmbH
Managing Director Deutschherrnstr. 47a
Fon.0911/92885-0 D-90429 Nürnberg
Fax.0911/92885-31
jhein@netways.de www.netways.de

  1. The second thing was a script the should be run by cron and send a
    mail to every user owning at ticket with a due date of today. I modified
    tickets to very date one could think of and user all operators (=><) but
    i allways got the same tickets or no ticket. Any ideas?

my $tickets = new RT::Tickets($RT::SystemUser);
$tickets->LimitStatus(VALUE => ‘open’);
$tickets->LimitStatus(VALUE => ‘new’);
$tickets->LimitStatus(VALUE => ‘stalled’);
$tickets->LimitDate(FIELD => ‘Due’, OPERATOR => ‘=’, VALUE => $now);

Ah. The ‘Due’ column is datetime. So your script will happily match
anything thats ‘Due’ that very second (ignoring that $now should be
$now->ISO). This is possibly not what you want.

Assuming that you run this script once per day, you should be able to:

my $yesterday = $now;
$yesterday->AddDays(-1);
$tickets->LimitDate(FIELD => 'Due',
	OPERATOR => '>=',
	VALUE => $yesterday->ISO,
	ENTRYAGGREGATOR => 'AND' );
$tickets->LimitDate(FIELD => 'Due',
	OPERATOR => '<=',
	VALUE => $now->ISO,
	ENTRYAGGREGATOR => 'AND' );

That will match anything due within the last 24hrs, and it should be
simple to change it to match anything due within the next 24 hours as
well.

Regards,

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B                      Operations