[rt-users] Problems with script
Mathew Snyder
theillien at yahoo.com
Sat Mar 31 02:17:38 EDT 2007
I'm getting all kinds of errors:
DBD::mysql::st execute failed: Incorrect information in file:
'./rt3_devel/Users.frm' at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505.
RT::Handle=HASH(0x99e5a80) couldn't execute the query 'SELECT * FROM Users
WHERE Name = ?' at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm
line 518
DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x99e5a80)',
'SELECT * FROM Users WHERE Name = ?', 'RT_System') called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1208
DBIx::SearchBuilder::Record::_LoadFromSQL('RT::CurrentUser=HASH(0x99be9ec)',
'SELECT * FROM Users WHERE Name = ?', 'RT_System') called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1127
DBIx::SearchBuilder::Record::LoadByCols('RT::CurrentUser=HASH(0x99be9ec)',
'Name', 'RT_System') called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record/Cachable.pm line 119
DBIx::SearchBuilder::Record::Cachable::LoadByCols('RT::CurrentUser=HASH(0x99be9ec)',
'Name', 'RT_System') called at /usr/local/rt-3.6.3/lib/RT/Record.pm line 395
RT::Record::LoadByCols('RT::CurrentUser=HASH(0x99be9ec)', 'Name',
'RT_System') called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1072
DBIx::SearchBuilder::Record::LoadByCol('RT::CurrentUser=HASH(0x99be9ec)',
'Name', 'RT_System') called at /usr/local/rt-3.6.3/lib/RT/CurrentUser.pm line 253
RT::CurrentUser::LoadByName('RT::CurrentUser=HASH(0x99be9ec)',
'RT_System') called at /usr/local/rt-3.6.3/lib/RT.pm line 181
RT::Init() called at ./user_timesheet.pl line 13
DBD::mysql::st execute failed: Incorrect information in file:
'./rt3_devel/Users.frm' at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm line 505.
RT::Handle=HASH(0x99e5a80) couldn't execute the query 'SELECT * FROM Users
WHERE Name = ?' at /usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Handle.pm
line 518
DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x99e5a80)',
'SELECT * FROM Users WHERE Name = ?', 'Nobody') called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1208
DBIx::SearchBuilder::Record::_LoadFromSQL('RT::CurrentUser=HASH(0x99ecf20)',
'SELECT * FROM Users WHERE Name = ?', 'Nobody') called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1127
DBIx::SearchBuilder::Record::LoadByCols('RT::CurrentUser=HASH(0x99ecf20)',
'Name', 'Nobody') called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record/Cachable.pm line 119
DBIx::SearchBuilder::Record::Cachable::LoadByCols('RT::CurrentUser=HASH(0x99ecf20)',
'Name', 'Nobody') called at /usr/local/rt-3.6.3/lib/RT/Record.pm line 395
RT::Record::LoadByCols('RT::CurrentUser=HASH(0x99ecf20)', 'Name',
'Nobody') called at
/usr/lib/perl5/vendor_perl/5.8.8/DBIx/SearchBuilder/Record.pm line 1072
DBIx::SearchBuilder::Record::LoadByCol('RT::CurrentUser=HASH(0x99ecf20)',
'Name', 'Nobody') called at /usr/local/rt-3.6.3/lib/RT/CurrentUser.pm line 253
RT::CurrentUser::LoadByName('RT::CurrentUser=HASH(0x99ecf20)', 'Nobody')
called at /usr/local/rt-3.6.3/lib/RT.pm line 185
RT::Init() called at ./user_timesheet.pl line 13
What might the problem be? Script:
#!/usr/bin/perl
use warnings;
use strict;
use lib '/usr/local/rt-3.6.3/lib';
use lib '/usr/local/rt-3.6.3/local/lib';
use RT;
use RT::Tickets;
use RT::Users;
use MIME::Lite;
RT::LoadConfig();
RT::Init();
# Declare our global variables
my (@days, @months, @years, @date);
my $time = time();
my %environment;
my %timeworked;
for (1 .. 7) {
$time -= 24*60*60;
@date = (localtime($time))[3 .. 5];
push @days, (sprintf '%02d', $date[0]);
push @months,(sprintf '%02d',$date[1] + 1);
push @years, $date[2] + 1900;
}
my $startDate = join "-", $months[$#months], $days[$#days], $years[$#years];
my $endDate = join "-", $months[0], $days[0], $years[0];
my $searchStart = join "-", $years[$#years], $months[$#months], $days[$#days];
my $searchEnd = join "-", $years[0], $months[0], $days[0];
print "Search Start -> $searchStart\n";
print "Search End -> $searchEnd\n";
my $emailTo = "msnyder\@servervault.com"; #"rbates\@servervault.com,
jcurran\@servervault.com";
my $emailFrom = "root";
#my $emailBcc = "msnyder\@servervault.com";
my $emailSubj = "RT User Timesheet for The Week Ending $endDate";
my $emailMsg = "Attached is a file containing billable time committed by
each user for the week ending $endDate. You'll find it to be a bit rudimentary
but it contains the data I've been asked to extract. There are two problems
left to solve at this point. One is to convert each user's ID number to his or
her name. The other is to get the query to work with any date and cover
anything that is open and has been worked on in the last week as well as
anything resoved in the last week. I have a help request in for both of these.";
my $tix = new RT::Tickets(RT::SystemUser);
$tix->FromSQL('Queue = "CustomerCare" AND (LastUpdated > "' . $searchStart . '"
AND LastUpdated < "' . $searchEnd . '")');
while (my $ticket = $tix->Next) {
my $env = $ticket->FirstCustomFieldValue('Environment');
unless ($env) {
warn "warning " . $ticket->id. " has no environment";
next
}
my $transactions = $ticket->Transactions;
while (my $transaction = $transactions->Next) {
my $creator = $transactions->CreatorObj;
next unless ($transaction->TimeTaken);
next unless $creator->Privileged;
$timeworked{$creator->Name} += $transaction->TimeWorked;
}
foreach my $user (keys %timeworked) {
my @endTime;
my $temp = $timeworked{$user} / 60;
my @temp = split /\./, $temp;
$endTime[0] = $temp[0];
$endTime[1] = $timeworked{$user} % 60;
my $wholeTime = sprintf '%d:%02d', @endTime[0,1];
$environment{$env}{$user} = $wholeTime;
}
}
# The nitty gritty of the script
open TIMESHEET, ">/work_reports/weekly/timesheet_weekof_$endDate.txt";
print TIMESHEET "Timesheet for $startDate to $endDate\n\n\n";
#printf TIMESHEET "\nUser Timesheet for $startDate to $endDate\n";
foreach my $env (sort keys %environment) {
#Print the header for our data
print TIMESHEET "$env", "\n";
printf TIMESHEET "%10s%8s\n", "User", "hh:mm";
print TIMESHEET ("-" x 30);
print TIMESHEET "\n";
foreach my $name (sort keys %{ $environment{$env} }) {
printf TIMESHEET "%10s%8s\n", "$name", "$environment{$env}{$name}";
}
print TIMESHEET "\n";
}
close TIMESHEET;
# Prepare and send the email which with the report to all necessary parties.
my $fullEmail = new MIME::Lite(From => $emailFrom,
To => $emailTo,
# Bcc => $emailBcc,
Subject => $emailSubj,
Type => "multipart/mixed");
$fullEmail->attach(Type => "TEXT",
Data => $emailMsg);
$fullEmail->attach(Type => "text/plain",
Path =>
"/work_reports/weekly/timesheet_weekof_$endDate.txt",
Disposition => "attachment");
$fullEmail->send("sendmail", "/usr/sbin/sendmail -t");
exit;
Mathew
--
If we don't protect the freedom of speech, how will we know who the assholes are?
http://theillien.blogspot.com
More information about the rt-users
mailing list