#!/usr/bin/perl
use DBI();
use Time::Local;
use List::Util qw(sum);
use CGI qw/:standard/;
my $query = new CGI;
# You can hardcode the dates below: the format is 'YYYY-MM-DD' eg 2008-07-02
my $DateFrom = param('datefrom');
my $DateTo = param('dateto');
my $ticket = ""; # Current working ticket number
my $CreateTime = ""; # Holds ticket creation time
my $OpenTime = ""; # Holds ticket open time (may be blank)
my $ResolveTime = ""; # Holds ticket resolve time (may rarely be blank)
my @TimeToResponse = ""; #Holds list of time to response
my @TimeToResolve = ""; # Holds list of time to resolve values
my %OwnersTimeToResponse = (); #owner user statisticts
my %OwnersTimeToResolve = ();
my $diff = "";
my $RTUrl = "http://192.168.165.42";
print "Content-type: text/html\n\n";
my $dbh = DBI->connect("DBI:mysql:database=rtdb;host=localhost","user","password");
# There are a few things that can be changed in here. The only tricky part is that I hardcore the queue IDs, so you will need to change the queue logic below.
my $sth = $dbh->prepare("SELECT ObjectID, Transactions.Type, OldValue, NewValue, Transactions.Created, Subject, Users.Name, Queues.Name FROM Transactions JOIN Tickets ON EffectiveId = ObjectID JOIN Users ON Tickets.Owner = Users.ID JOIN Queues on Tickets.Queue = Queues.id WHERE ObjectType LIKE CONVERT(_utf8 \'%Ticket%\' USING latin1) COLLATE latin1_swedish_ci AND (Transactions.Type LIKE CONVERT(_utf8 \'Create\' USING latin1) COLLATE latin1_swedish_ci OR Transactions.Type LIKE CONVERT(_utf8 \'Status\' USING latin1) COLLATE latin1_swedish_ci) AND Transactions.Created > \'$DateFrom\' AND Transactions.Created < \'$DateTo\' AND (Tickets.Queue = 1 OR Tickets.Queue = 3 OR Tickets.Queue = 5) ORDER BY ObjectID ASC, Transactions.Created ASC");
$sth->execute();
# Array output per loop iteration:
# 0 - ObjectID eg 1236
# 1 - Type eg Create || Status
# 2 - OldValue eg open || new || resolved (not used)
# 3 - NewValue eg open || new || resolved
# 4 - Created eg '2008-06-06 17:34:42'
# 5 - Subject
# 6 - Owner
# 7 - Queue
print "Ticket Data
\n";
print "
\n";
while (my @ref = $sth->fetchrow_array()) {
if ($ref[1] eq "Create") {
# We are starting a new ticket in this month!
$ticket = $ref[0]; # Hold the ticket number for subsiquent loop interations
$CreateTime = $ref[4];
$TimeToResolve = "";
$TimeToResponse = "";
$DiffOpen = "";
$DiffResolved = "";
} elsif ($ref[1] eq "Status" && $ref[3] eq "open" && $ticket == $ref[0]) {
#We have a valid open!
#if ($ref[6] eq "michaelt") {
# print "\n\nDEBUG: $ref[0] $ticket\n\n";
#}
$OpenTime = $ref[4];
$DiffOpen = &ReturnSecondsDiff($OpenTime, $CreateTime);
push (@TimeToResponse,$DiffOpen);
push (@{$OwnersTimeToResponse{$ref[6]}},$DiffOpen);
} elsif ($ref[1] eq "Status" && $ref[3] eq "resolved" && $ticket == $ref[0]) {
$ResolvedTime = $ref[4];
$DiffResolved = &ReturnSecondsDiff($ResolvedTime, $CreateTime);
push (@TimeToResolve,$DiffResolved);
push (@{$OwnersTimeToResolve{$ref[6]}},$DiffResolved);
print "| $ticket | $ref[5] | $ref[6] | ", &round($DiffOpen / 60 / 60), " | ", &round($DiffResolved / 60 / 60), " |
\n";
}
}
print "
\n\n";
$sth->finish();
#print "
\n\nDebug: @{$OwnersTimeToResolve{michaelt}}
\n\n";
print "\n\nTOTALS:
\n\n";
print "Total Tickets Responded to: ", $#TimeToResponse + 1,"
\n";
print "Total Tickets Resolved: ", $#TimeToResolve + 1,"
\n";
my $TotalResponseAverage = sum(@TimeToResponse) / @TimeToResponse;
print "Average Response Time: ",$TotalResponseAverage / 60 / 60, "
\n";
my $TotalResolveAverage = sum (@TimeToResolve) / @TimeToResolve;
print "Average Resolve Time: ",$TotalResolveAverage / 60 / 60, "
\n\n";
#my $ChrisTotalResponseAverage = sum (@{$OwnersTimeToResponse{chrisc}}) / @{$OwnersTimeToResponse{chrisc}};
#print "Chris\' Average Response Time: ",$ChrisTotalResponseAverage / 60 / 60, "\n";
#my $ChrisTotalResolveAverage = sum (@{$OwnersTimeToResolve{chrisc}}) / @{$OwnersTimeToResolve{chrisc}};
#print "Chris\' Average Resolve Time: ",$ChrisTotalResolveAverage / 60 / 60, "\n";
while (my ($key, $value) = each(%OwnersTimeToResolve) ) {
if (@{$OwnersTimeToResponse{$key}}) {
print "$key\'s Responded Tickets: ", $#{$OwnersTimeToResponse{$key}} + 1,"
\n";
print "$key\'s Resolved Tickets: ", $#{$OwnersTimeToResolve{$key}} + 1,"
\n";
print "$key\'s Average Response Time: ", (sum(@{$OwnersTimeToResponse{$key}}) / @{$OwnersTimeToResponse{$key}}) / 60 / 60, "
\n";
print "$key\'s Average Resolve Time: ", (sum(@{$OwnersTimeToResolve{$key}}) / @{$OwnersTimeToResolve{$key}}) / 60 / 60, "
\n\n";
}
}
print "";
$dbh->disconnect();
sub ReturnSecondsDiff {
my $date1 = "";
my $date2 = "";
@_[0] =~ /(\d{4})-(\d{2})-(\d{2})\s(\d{2})\:(\d{2})\:(\d{2})/;
$date1 = timelocal($6, $5, $4, $3, $2, $1);
@_[1] =~ /(\d{4})-(\d{2})-(\d{2})\s(\d{2})\:(\d{2})\:(\d{2})/;
$date2 = timelocal($6, $5, $4, $3, $2, $1);
return $date1 - $date2;
}
sub round {
my($number) = shift;
return int($number + .5);
}