[rt-users] Help with a script needed

[Mathew Snyder]

Basics:
We have a custom field called 'Profiles' which is a list of all our
customers.  When a ticket is received, we add the customer profile to
the ticket.  This allows us to keep track of work we've done for each
customer.

Requested:
We need to have in place something which my boss is calling The End of
the Day button which will allow us to run a script that will poll the
database and pull out information for each user with the amount of time
spent on each customer during the day.  The output would, for example,
look like this:

User              Customer             Ticket             Time
-----             ---------            -------            -----
user01            customer01           41445              20 min
user01            customer02           42322              35 min
user02            customer01           41443              25 min
etc
etc

We are also looking at having, for each user, total time spent on each
customer.  The formatting is secondary to the function, the above
listing is simply to provide an idea of what data I need to pull out.

As a starting point we have a script which pulls the total number of
tickets submitted by a customer and the total time marked in those
tickets.  This the that script:

#!/usr/bin/perl
use DBI;

print "Please enter the Date: ";
my $date = <STDIN>;
chomp($date);
print "Please Enter the Company: ";
my $company = <STDIN>;
chomp($company);

if ($company eq 'ALL') {
        $statement eq "";
        } else {
        $statement = "and ObjectCustomFieldValues.content = \'$company\'";
}

$dbh = DBI->connect ( "dbi:mysql:dbname=rt3;host=XXX.XXX.XXX.XXX",
"ro_account", "xxxxxxxx");
if ( !defined $dbh ) {
die "Cannot connect to database!\n";
}

$sth = $dbh->prepare( "
        SELECT ObjectCustomFieldValues.content, COUNT(DISTINCT
Tickets.effectiveid), SUM(Tickets.timeworked)
        FROM Tickets
        INNER JOIN ObjectCustomFieldValues ON Tickets.effectiveid =
ObjectCustomFieldValues.Objectid
        WHERE Tickets.resolved LIKE '%$date%' $statement
        GROUP BY ObjectCustomFieldValues.content");

if (!defined $sth ) {
die "Cannot prepare statement: $DBI::errstr\n";
}

$sth->execute;

while (( $company, $tickets, $time) = $sth->fetchrow()) {
        $temp = "";
        $temp = $time / 60;
        @temp = split /\./, $temp;
        @endtime[0] = @temp[0];
        @endtime[1] = $time % 60;
        $endtime = @endtime[0] . ":" . @endtime[1];
        printf "%20s%5s%10s\n", $company, $tickets, $endtime;
}

$sth-finish;

It uses a read-only account connecting to our database.  It ask for the
date and a customer name based on what is in the Profiles field.  Using
this it gathers the information.

Can anyone help me modify this to perform the task I need or at least
prod me in the right direction.  My perl skills are rudimentary at best.
 Right now it doesn't necessarily need to be incorporated into the RT
UI.  Instead, I just need to figure out how to pull the data.

Thanks for any help offered
Mathew