[rt-users] Query in script not working

[Mathew Snyder]

FWIW, this is the script I came up with:

#!/usr/bin/perl

###########################################
#  Name:         daily_transactions.pl
#  Version:      0.1
#  Author:       Mathew Snyder
#  Date:         February 24, 2008
#  Comments: This is a script which lists
#                        how many transactions are
#                        performed per hour.
##########################################

use warnings;
use strict;
use lib '/usr/local/rt3/lib';
use lib '/usr/local/rt3/local/lib';
use lib '/usr/local/lib';
use RT;
use RT::Tickets;
use RT::Users;
use Date::Parse;
use Reports::Emails;
use MIME::Lite;

RT::LoadConfig();
RT::Init();

my (%userID,%transCount);
my @date = (localtime(time - 86400))[3 .. 5];
my $yesterday = join "-", ($date[2] + 1900, (sprintf '%02d',$date[1] + 1),
(sprintf '%02d', $date[0]));

my $tix = new RT::Tickets(RT::SystemUser);
$tix->FromSQL("(Queue = 'CustomerCare' OR Queue = 'TechOps') AND LastUpdated =
'" . $yesterday . "'");

my $users = new RT::Users(RT::SystemUser);
$users->LimitToPrivileged;

while (my $user = $users->Next) {
        next if $user->Name eq 'root' || $user->Name eq 'RT_System';
        $userID{$user->Name} = undef;
}

while (my $ticket = $tix->Next) {
        my $transactions = $ticket->Transactions;
        while (my $transaction = $transactions->Next) {
                my $creator = $transaction->CreatorObj;
                my $user = $creator->Name;
                next unless exists($userID{$user});
                next if $transaction->Creator == '1';
                (my $tranDate = $transaction->Created) =~ s/\s.*$//;
                next unless $tranDate eq $yesterday;
                my $tranTime = (str2time($transaction->Created) - 18000);
                my $tranHour = (localtime($tranTime))[2];
                my $hour = sprintf("%02d", $tranHour);
                $hour .= "00";
                $transCount{$hour} += 1;
        }
}

open TRANSLOG, ">/work_reports/daily/transaction_report_$yesterday.txt";
print TRANSLOG "Report of the hourly ticket transactions committed on
$yesterday\n\n";
printf TRANSLOG "%15s\n", "Trans";
printf TRANSLOG "%6s%9s\n", "Hour", "Count";

foreach my $time (sort keys %transCount) {
        printf TRANSLOG "%6s%7s\n", $time, $transCount{$time};
}

close TRANSLOG;

my $emailSubj = "Ticket Transaction Count for $yesterday";
my $emailMsg  = "Attached is a log of the transactions committed per hour
yesterday.  If an hour does not appear it is safe to assume no transactions were
created during that time.";

# Prepare and send the email which with the report to all necessary parties.
my $fullEmail    = new MIME::Lite(From          => $emailFrom,
                                                                  To
=> 'user at company.com',
                                                                  Bcc
=> 'user2 at company.com',
                                                                  Subject
=> $emailSubj,
                                                                  Type
=> "multipart/mixed");

$fullEmail->attach(Type => "TEXT",
                                   Data => $emailMsg);

$fullEmail->attach(Type                 => "text/plain",
                                   Path                 =>
"/work_reports/daily/transaction_report_$yesterday.txt",
                                   Disposition  => "attachment");

$fullEmail->send("sendmail", "/usr/sbin/sendmail -t");

Stephen Turner wrote:
> Quoting Mathew Snyder <mathew.snyder at gmail.com>:
> 
>> I've got the dates working.  However, the tickets retrieved are still
>> based on
>> the day starting at 05:00:00 and ending the following morning at the
>> same time.
>> How can I tell it to only return tickets from between 00:00:00 and
>> 23:59:00?
>>
>> Mathew
>>
>>
> 
> You are actually getting the tickets you want - the dates are stored in the
> database as GMT and so appear different by 5 hours from what you see on the
> screen. It looks a bit strange, but it works fine.
> 
> BTW - what was the problem that you fixed with the dates?
> 
> Steve

-- 
Keep up with me and what I'm up to: http://theillien.blogspot.com