[rt-users] Script assistance Please

[Mathew Snyder]

I can't get this script to do what I need which is to create a weekly tally of
time spent by each user on each of our customers.  The goal is to have it print
out each customer name as a header and below that the time spent on them by each
user in the previous week.  However, what it does now is print out the total
time spent on *all* customers by each user.

So, instead of output saying that user1 spent 20 minutes on customer1 and 30
minutes on customer2, I get user1 spent 50 minutes total working on all customers.

Can someone take a look and help me out?

#!/usr/bin/perl

use warnings;
use strict;
use lib '/usr/local/rt-3.6.3/lib';
use lib '/usr/local/rt-3.6.3/local/lib';
use RT;
use RT::Tickets;
use RT::Users;

RT::LoadConfig();
RT::Init();

# Declare our global variables
my (@days, @months, @years, @date, @displayDate);
my $time = time();

for (1 .. 7) {
  $time -= 24*60*60;
  @date = (localtime($time))[3 .. 5];
  push @days, (sprintf '%02d', $date[0]);
  push @months,(sprintf '%02d',$date[1] + 1);
  push @years, $date[2] + 1900;
  push @displayDate, join "-", (sprintf '%02d', $date[1] + 1), (sprintf '%02d',
$date[0]), $date[2] + 1900;
}
my $endDate     = join "-", $years[0], $months[0], $days[0];
my $startDate   = join "-", $years[$#years], $months[$#months], $years[$#years];

my $emailTo      = "user1\@company.com, user2\@company.com";
my $emailFrom    = "root";
my $emailBcc     = "user3\@company.com";
my $emailSubj    = "RT User Timesheet for The Week Ending $endDate";
my $emailMsg     = "Attached is a file containing billable time committed by
each user for the week ending $endDate.";

my $tix = new RT::Tickets(RT::SystemUser);
$tix->FromSQL('Queue = "CustomerCare" AND Status = "open" AND Created <
"2007-03-03"');
#$tix->FromSQL('Queue = "CustomerCare" AND (Status = "resolved" OR Status =
"open") AND (LastUpdated >= "' .  $startDate . '" AND LastUpdated < "' .
$endDate. '")');

my @environment;
my %environment;
my %timeworked;
my $users = new RT::Users(RT::SystemUser);
$users->LimitToPrivileged;

# The nitty gritty of the script
while (my $ticket = $tix->Next) {
        my $environment = $ticket->FirstCustomFieldValue('Environment');
        my $user;
        my $transaction;
        unless ($environment) {
                warn "warning " . $ticket->id. " has no environment";
                next
       }

        foreach my $key (keys %environment) {
                if (exists $environment{$key}) {
                        next;
                }else{
                        $environment{$key} = 0;
                }
        }
        foreach my $key (keys %environment) {
                print $key . " -> " . $environment{$key} . "\n";
        }

        my $transactions = $ticket->Transactions;
        foreach my $enviro (keys %environment) {
                while ($transaction = $transactions->Next) {
                        next unless (($transaction->TimeTaken) && ($enviro ==
$ticket->FirstCustomFieldValue('Environment'))) ;
                        print "Working on " . $ticket . " for " . $enviro . "\n";
                        $timeworked{$transaction->Creator} +=
$transaction->TimeTaken;
                }
        }

        foreach my $user (keys %timeworked) {
                foreach my $cust (keys %environment) {
                        $environment{$cust}{$user} = $timeworked{$user};
                }
        }
}


open TIMESHEET,  ">/work_reports/weekly/timesheet_weekof_$endDate.txt";

#print "\nTimesheet for $weekStart to $weekEnd\n";
printf TIMESHEET "\nUser Timesheet for $startDate to $endDate\n";

foreach my $user (keys %timeworked) {
        # Print the header for our data
#       print "\n\n" . $displayDate . "\n";
        printf TIMESHEET "\n\n" . (shift @displayDate) . "\n";
#       printf "%32s%11s\n", "Time", "Avg Time";
        printf TIMESHEET "%32s%11s\n", "Time";
#       printf "%18s%7s%7s%11s\n", "Profile", "Tkts", "hh:mm", "hh:mm";
        printf TIMESHEET "%18s%7s%7s%11s\n", "Profile", "Tkts", "hh:mm";
#       print ("-" x 44);
        print TIMESHEET ("-" x 44);
#       print "\n";
        print TIMESHEET "\n";


        foreach my $env (sort keys %environment) {
                print $env . " -> " . $environment{$env}{$user} . "\n";
        }

# Prepare and send the email which with the report to all necessary parties.
my $fullEmail    = new MIME::Lite(From    => $emailFrom,
                                  To      => $emailTo,
                                  Bcc     => $emailBcc,
                                  Subject => $emailSubj,
                                  Type    => "multipart/mixed");

$fullEmail->attach(Type        => "TEXT",
                   Data        => $emailMsg);

$fullEmail->attach(Type        => "text/plain",
                   Path        =>
"/work_reports/weekly/timesheet_weekof_$endDate.txt",
                   Disposition => "attachment");

$fullEmail->send("sendmail", "/usr/sbin/sendmail -t");}

--
Mathew