[rt-users] Query in script not working

[Roy El-Hames]

SetToNow is in Date.pm, 3 rd function in the 3.6.4 version :¬)
GoBack functions seems I have added them some time in the past couple of 
years, (possibly of the wiki.. the code is too neat to be mine), I have 
attached my Date_Vendor in case its helpful to you and anyone else

Roy

Mathew Snyder wrote:
> 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
>>>>     
>>>>         
>>>   
>>>       
>
>   

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: Date_Vendor..txt
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080226/d5403c9a/attachment.txt>