[rt-users] Query in script not working

[Mathew Snyder]

Hmmm...that certainly would have been helpful.  But I'm looking at the perldoc
on Date.pm and I don't see SetToNow or GoBackDays

Roy El-Hames wrote:
> Hi Mathew;
> 
> Not sure if you are aware of it, but in case you did not RT gives you a
> very powerful Date functions in Date.pm,
> from a perl script you can do the below:
> 
> use RT::Date;
> my $date = new RT::Date($RT::SystemUser);
> $date->SetToNow();
> my $now = $date->ISO;
> $date->GoBackDays(1);
> my $yesterday = $date->ISO;
> 
> You can also set to midnight, change zone etc ..look up lib/RT/Date.pm
> 
> Roy
> 
> 
> Mathew Snyder wrote:
>> 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