[rt-users] Script to build timeworked report based on CustomField

[Jason Fenner]

Hi everyone.

I am pretty new at writing scripts to use with RT.  Recently I wrote a 
script that I use to build a report on how much time we have spent 
working for the different departments in the company I work for.  The 
departments are set through a RT::Ticket Custom Field.

This is my first RT script so, I'm sure that it needs a lot of 
improvements.  Feel free to post your thoughts and ideas.  I hope that 
other in the RT community will also find it useful.

My fire proof suit is on and I look forward to hearing your input.

Jason Fenner

------------------------BEGIN SCRIPT-------------------------

#!/usr/bin/perl

##########################################################################
#
#    Author: Jason Fenner
#    Date: 04-05-2006
#    Email: jfenner at vitamix.com
#    License: GPL
#    Description: Creates a report of time worked based on a ticket
#                 custom field named "Department".  Report contains
#                 total time worked and percent of total time per
#                 department.
#
#    Note: Please email me with any improvements.


# Load all modules we are going to use.
use DBI;
use POSIX(strftime);
use FileHandle;
use strict;
use Date::Manip;
use Math::Round;

# I use sendmail here.  Adjust this to match your system.
my $sendmail = "/usr/sbin/sendmail -oi -t";
# Set your email settings, these settings are used to create the email 
containing the report.
my $reply_to = "Reply-to: noreply\@foo.bar.com";
my $subject  = "Subject: RT Department Reporting\n\n";
my $to       = "To: foo\@bar.com\n";
my $cc       = "Cc: foo\@bar.com,foo\@bar.com\n";
my $from     = "From: rt-bot\@foo.bar.com\n";

#
# Define the rest of the variables.
#

my $i;
my $today    = strftime("%a %m/%d/%Y %H:%M:%S",localtime());
my $department;
my $TimeWorkedsql;
my $ttotal;

# This line is here for if you want to use a manual start time.
#my $startdate = "2006-03-13 00:00:00";

my $sdate = DateCalc("today","- 1 month",\ my $err);
my $bdate = &UnixDate("$sdate","%Y:%m:%d");
my $parsedate = ParseDate("$bdate");
my $startdate = Date_ConvTZ($parsedate,"","GMT");
my $refdate = &UnixDate("$sdate","%a %m/%d/%Y %H:%M:%S");
undef $/;
my $percent;
my $prepercent;
my $percentTotal;
my $percentTotalf;


$= = 200000;

#
# Define our report format
#

format STDOUT_TOP =

================================================================================
                             Department Time Worked Report
                                     Foo-Bar RT

                          Generated on: @<<<<<<<<<<<<<<<<<<<
                          $today
             From: @<<<<<<<<<<<<<<<<<<<<<<<<<<<   To: 
@<<<<<<<<<<<<<<<<<<<<<<<
$refdate,$today
================================================================================
           Department                                  Time Worked   % 
of Total
--------------------------------------------------------------------------------
.


format STDOUT=
           @<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<  @<<<<<<<<<<   @<<%
$department,$TimeWorkedsql,$percent
.

format FOOTER=
--------------------------------------------------------------------------------

  Total number of departments worked for: @<<<<<<  Total Percentage: @<<<%
$i,$percentTotalf
  Total time spent for departments: @<<<<<<
$ttotal

================================================================================
.

#
# Open cache file for writing
#

open(TEMP, ">/tmp/testtemp.text") or die "Can't open temp: $!";

#
# SQL Database Access time.
#

# Note: You need to edit the line below here with your db's username and 
password.
#
my $dbh = DBI->connect("dbi:mysql:rt3","dbuser","dbpassword");
my $sth = $dbh->prepare_cached('select CustomFieldValues.Name, 
sum(Transactions.TimeTaken)+sum(if(Transactions.Field = 
"TimeWorked",Transactions.NewValue,0)) AS TotalTime from Transactions 
INNER JOIN CustomFieldValues ON CustomFieldValues.Name = 
ObjectCustomFieldValues.Content INNER JOIN ObjectCustomFieldValues ON 
ObjectCustomFieldValues.ObjectId = Transactions.ObjectId INNER JOIN 
Tickets ON Tickets.id = Transactions.ObjectId where Transactions.Created 
 > ? AND ObjectCustomFieldValues.CustomField =2 GROUP BY 
CustomFieldValues.Name;') or die "Couldn't prepare statement: " . 
$dbh->errstr;

$sth->execute($startdate) or die "Couldn't execute statement: " . 
$sth->errstr;

TEMP->format_name("STDOUT");
TEMP->format_top_name("STDOUT_TOP");
TEMP->format_lines_per_page(20000);

#Read our results

my @datafirst;

#
# Note: Below, I run the SQL query twice.  I think this is not needed 
and slows the script down quite a bit.  If you figure out how not to run it
# twice, please let me know.
#

while (@datafirst = $sth->fetchrow_array()) {
      #print $datafirst[0] . "\n";
      #print $datafirst[1] . "\n";
      $TimeWorkedsql = $datafirst[1];
      $ttotal = $ttotal + $TimeWorkedsql;
      }

#print "Total was: $ttotal\n";

$TimeWorkedsql = 0;
#$ttotal = 0;

$sth->execute($startdate) or die "Couldn't execute statement: " . 
$sth->errstr;
my @data;
while (@data = $sth->fetchrow_array()) {
      $i++;
      $department   = $data[0];
      $TimeWorkedsql = $data[1];
      $prepercent = $TimeWorkedsql / $ttotal;
      $prepercent = $prepercent * 100;
      $percentTotal = $percentTotal + $prepercent;
      $percent = (round($prepercent));
      chomp;
      #$ttotal = $ttotal + $TimeWorkedsql;
      #$content = $content."\t$i: $name\t$address\n";
      write(TEMP);
}

 $percentTotalf = (round($percentTotal));

TEMP->format_name("FOOTER");
write(TEMP);
close(TEMP);

$dbh->disconnect;

#
# Suck our cache file in
#

open(TEMP, "/tmp/testtemp.text") or die "Can't open temp: $!";
my $content = <TEMP>;

#
# Send the report via email
#

open(SENDMAIL, "|$sendmail") or die "Cannot open $sendmail: $!";
print SENDMAIL $from;
print SENDMAIL $to;
print SENDMAIL $cc;
print SENDMAIL $subject;
print SENDMAIL $content;
close(SENDMAIL);
close(TEMP);

#print $to;
#print "Message sent!\n";

------------------------------------END 
SCRIPT-----------------------------------