#!/usr/bin/perl
#
# $Id: rt3-stats,v 1.4 2004/02/12 06:28:28 carl Exp $
#
# rt3-stats
# Usage: rt3-stats <queue name>|All [<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 2003, Teo de Hesselle <tdehesse@uts.edu.au>
# 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
#
#  I would appreciate it if useful modifications were mailed to me at the
#  email address above. Thank you.
#

if (!defined($ARGV[0])) {
 print "Usage: rt3-stats <queue name>|All [<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 (defined($ARGV[1])) {
	if ($ARGV[1] =~ /\-/) {
		$dayfrom = "\"$ARGV[1]\"";
		$dayrange = "from $dayfrom to ";
		if (defined($ARGV[2])) {
			$dayto = "\"$ARGV[2]\"";
			$dayrange .= $dayto;
		} else {
			$dayto = "CURDATE()";
			$dayrange .= "today";
		}
	} else {
		$dayinterval = $ARGV[1];
		$dayrange = "in the last $dayinterval days";
		$dayfrom = "DATE_SUB(CURDATE(), INTERVAL $dayinterval DAY)";
		$dayto = "CURDATE()";
	}
} else {
	$dayinterval = 7;
	$dayrange = "in the last $dayinterval days";
	$dayfrom = "DATE_SUB(CURDATE(), INTERVAL 7 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] . "\"";
}


open QUEUES, ">&STDOUT";

format QUEUES_TOP =


Tickets created by date for @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$thisqueue

Tickets created @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$dayrange

 Date                    Tickets
 ======================= =========
.

format QUEUES =
 @<<<<<<<<<<<<<<<<<<<<<< @>>>>>>>>
 $ary[0],                $ary[1]
.

$query ="select DATE_FORMAT(created, \"%a %D %b %Y\") as date, count(Id) from Tickets where $queuequery created>=$dayfrom and created <= $dayto group by date order by created;";

$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) {
#	print "$ary[0]\t$ary[1]\n";
	$createdtotal += $ary[1];
	write (QUEUES);
	
}
$ary[0] = "-" x 22;
$ary[1] = "-" x 9;
write (QUEUES);


$ary[0] = "Total";
$ary[1] = $createdtotal;
write (QUEUES);

open DAYS, ">&STDOUT";

%dow = (0 => 'Sunday',
        1 => 'Monday',
	2 => 'Tuesday',
	3 => 'Wednesday',
	4 => 'Thursday',
	5 => 'Friday',
	6 => 'Saturday');


format DAYS_TOP =


Tickets created by weekday for @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$thisqueue

Tickets created @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$dayrange

 Day                     Tickets
 ======================= =========
.

format DAYS =
 @<<<<<<<<<<<<<<<<<<<<<< @>>>>>>>>
 $thisday,               $ary[1]
.

$createdtotal = 0;

$query ="select DATE_FORMAT(created, \"%w\") as date, count(Id) from Tickets where $queuequery created>=$dayfrom and created <= $dayto group by date;";

$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) {
#	print "$ary[0]\t$ary[1]\n";
	$createdtotal += $ary[1];
#	$thisday = $ary[0];
	$thisday = $dow{$ary[0]};
	write (DAYS);
	
}
$thisday = "-" x 22;
$ary[1] = "-" x 9;
write (DAYS);


$thisday = "Total";
$ary[1] = $createdtotal;
write (DAYS);

open RESOLVER, ">&STDOUT";

format RESOLVER_TOP =


Tickets resolved @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$dayrange
by Resolver:

 Name                     Count    Time (H:MM)
 ========================= ======= ===========
.

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

$query = "SELECT COUNT(Tickets.id) as resolvecount, Users.RealName, sum(Tickets.TimeWorked) FROM Transactions, Users, Tickets WHERE Tickets.id=Transactions.Ticket AND Users.id = Transactions.Creator AND Transactions.Created >= $dayfrom and Transactions.Created <= $dayto AND $queuequery NewValue='resolved' GROUP BY Users.Realname ORDER BY resolvecount DESC;";

$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) {
#	print "$ary[0]\t$ary[1]\t";
	$resolvedtotal += $ary[0];
	$totaltime += $ary[2];
	$hour = int($ary[2]/60);
	$min = $ary[2] - ($hour * 60);
	$worked = sprintf("%d:%.2d", $hour, $min);
#	print "$worked\n";
	write (RESOLVER);

}

$ary[0] = "-" x 7;
$ary[1] = "-" x 24;
$worked = "-" x 11;
write (RESOLVER);

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

write (RESOLVER);

$dbh->disconnect;

exit 0;

