#!/usr/bin/perl
#
# $Id: rt3-queue-stats,v 1.3 2004/02/12 06:32:51 carl Exp $
#
# rt3-queue-stats
# Usage: rt3-queue-stats <days>|<date from> [<date to>]
#        <days> is the number of days ago to report from.
#        <date from> and <date to> are in the format yyyy-mm-dd.
#
# This script generates a basic report on ticket creation and resolution
# either for a particular queue or for all queues.  Without extra options
# it generates the report for the last 7 days, however you can specify
# a number of days, a date to report from or a date range.
#
# BUGS:  There is no checking done on values passed to MySQL.
#        DO NOT USE THIS ON A WEBSITE without properly checking parms.
#
# Copyright 2004, Carl Makin <carl@xena.ipaustralia.gov.au>
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 2 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#

if (!defined($ARGV[0])) {
 print "Usage: rt3-queue-stats <days>|<date from> [<date to>]\n";
 print "       <days> is the number of days ago to report from.\n";
 print "       <date from> and <date to> are in the format yyyy-mm-dd\n";
 exit 1;
}

select(STDOUT); $|=1;

use DBI;

my $dayinterval;


if ($ARGV[1] =~ /\-/) {
	$dayfrom = "\"$ARGV[0]\"";
	$dayrange = "From $dayfrom To ";
	if (defined($ARGV[1])) {
		$dayto = "\"$ARGV[1]\"";
		$dayrange .= $dayto;
	} else {
		$dayto = "CURDATE()";
		$dayrange .= "Today";
	}
} else {
	$dayinterval = $ARGV[0];
	$dayrange = "In The Last $dayinterval Days";
	$dayfrom = "DATE_SUB(CURDATE(), INTERVAL $dayinterval DAY)";
	$dayto = "CURDATE()";
}
	
	
my $database = "rt3";
my $hostname = "";
my $port = '';
my $user = "rt_user";
my $password = "rt_pass";

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, 
                        $user, 
                        $password,
                        {'RaiseError' => 1});

my $sth;
my $rc;
my $createdtotal;
my $resolvedtotal;

if ($ARGV[0] =~ /all/i) {
	$queuequery = "";
	$thisqueue = "all queues";
} else {
	$query = "SELECT Id FROM Queues WHERE name=\"$ARGV[0]\"";

	$sth = $dbh->prepare($query) or die "Can't prepare queue query";

	$rc = $sth->execute
	      or die "Can't execute statement: $DBI::errstr";

	while (@ary = $sth->fetchrow_array) {
		$queueid = $ary[0];
	}
	$queuequery = "Tickets.queue=$queueid and";
	$thisqueue = "the queue \"" . ucfirst $ARGV[0] . "\"";
}


format STDOUT_TOP =
Tickets Resolved by Queue.

@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$dayrange

                                  Worked     Avg Elapsed
 Queue                   Tickets  Time(h:mm) Time(h:mm)
 ======================= ======== ========== ==========
.

format STDOUT = 
 @<<<<<<<<<<<<<<<<<<<<<< @>>>>>>> @>>>>>>>>> @>>>>>>>>>
 $ary[0],                $ary[1], $worked,   $elapsed
.


$query ="select Queues.name, count(Tickets.Id), sum(Tickets.TimeWorked), TIME_FORMAT(SEC_TO_TIME(AVG(UNIX_TIMESTAMP(Tickets.Resolved)-UNIX_TIMESTAMP(Tickets.Created))), '%H:%i') AS total_time from Tickets,Queues where Tickets.Status='resolved' and Tickets.queue=Queues.Id and Tickets.Resolved >= $dayfrom and Tickets.Resolved <= $dayto group by Queues.name;";

$sth = $dbh->prepare($query) or die "Can't prepare queue query";

$rc = $sth->execute
      or die "Can't execute statement: $DBI::errstr";

my $totaltime;

while (@ary = $sth->fetchrow_array) {
	$createdtotal += $ary[1];
	$totaltime += $ary[2];
	$hour = int($ary[2]/60);
	$min = $ary[2] - ($hour * 60);
	$worked = sprintf("%d:%.2d", $hour, $min);
	$elapsed = $ary[3];
	write;
}

$ary[0] = "-----------------------";
$ary[1] = "--------";
$worked = "-" x 10;
$elapsed = "-" x 10;
write;

$ary[0] = "Total";
$ary[1] = $createdtotal;
$hour = int($totaltime/60);
$min = $totaltime - ($hour * 60);
$worked = sprintf("%d:%.2d", $hour, $min);
$elapsed = "";

write;

exit 0;


