#!/usr/bin/perl

################################################################
# File:     monthly_timesheet.pl
# Version:  2.1
# Author:   Mathew Snyder
# Date:     June 1, 2007
# Comments: A script to gather up the tickets created under 
#	    every customer's various environments and write 
#	    the results to a file.  The file will then be
#	    mailed to the necessary recipients.  There are
#	    probably ways to make this smaller and more 
#	    concise but this one works.  It could also use
#	    an error log.
################################################################

#Set up our environment
use warnings;
use strict;
use lib '/usr/local/rt-3.6.1/lib';
use lib '/usr/local/rt-3.6.1/local/lib';
use RT;
use RT::Users;
use RT::Tickets;
use MIME::Lite;
use Date::Leapyear;

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

#Declare the variables we'll need
my %timeWorked;
my %ticCount;
my $count = 1;
my $febDays;
my @days;
my %time;
my %tickets;
my %months31 = (
        "01" => undef,
        "03" => undef,
        "05" => undef,
        "07" => undef,
        "08" => undef,
        "10" => undef,
        "12" => undef,
);
my %months30 = (
        "04" => undef,
        "06" => undef,
        "09" => undef,
        "11" => undef,
);
my %months = (
        "01" => "Jan",
        "02" => "Feb",
        "03" => "Mar",
        "04" => "Apr",
        "05" => "May",
        "06" => "Jun",
        "07" => "Jul",
        "08" => "Aug",
        "09" => "Sept",
        "10" => "Oct",
        "11" => "Nov",
        "12" => "Dec",
);
my @date; #  = (localtime)[4,5];
my $month = (sprintf '%02d', (localtime)[4]); #$date[0]);
my $year      = (localtime)[5] + 1900; #$date[1] + 1900;

# If the value of the 'month' variable is '00' we are in January so need
# to set the $month to 12 in order to get December's data.  Any other
# value is spot on.  The reason being that  while (localtime) will produce
# a value based  on a startpoint of '00', we are shifting this back one by
# making '00' equal to '12' causing all other numbers to match up to their
# respective month.
if ($month == '00') {
        $month = 12;
        $year--;
}

# We need to determine if the current year is a leap year so we use the
# right number of days for Feb.
if (isleap($year)) {
        $febDays = 29;
}else{
        $febDays = 28;
}

# Set our variables for the email
my $emailTo      = "user\@company.com";
my $emailFrom    = "RT";
my $emailBcc     = "user\@company.com
my $emailSubj    = "RT Customer Timesheet for $months{$month} $year";
my $emailMsg     = "Attached is a file containing customer billable time for the month of $month.";


# Determine if the month requested has 31, 30 or 28 days and build
# our days array to match
if (exists($months31{$month})){
        while ($count <= 31) {
                push @days, (sprintf '%02d', $count);
                $count++;
        }
}elsif (exists($months30{$month})){
        while ($count <= 30) {
                push @days, (sprintf '%02d', $count);
                $count++;
        }
}else{
        while ($count <= $febDays) {
                push @days, (sprintf '%02d', $count);
                $count++;
        }
}

# Build the date into the same form used by the RT database
foreach my $day (@days) {
        my $date = join "-", $year, $month, $day;
        push @date, $date;
}

# Build our query
my $tix = new RT::Tickets(RT::SystemUser);
$tix->FromSQL('Queue = "CustomerCare" AND Resolved >= "' . $date[0] . '" AND Resolved =< "' . $date[$#date] . ')');

foreach my $day (@date) {
        # This will build our time and tickets hashes which we use later to compile
        # the total
        while (my $ticket = $tix->Next) {
                # Get the customer name from the custom field that holds it..
                my $env = $ticket->FirstCustomFieldValue('Environment');
                next unless $env;
                # If the ticket is resolved, there won't be anything past the resolved
                # date so it will be the LastUpdated date too.  Although, we could probably
                # use $ticket->Resolved instead.
                (my $updt = $ticket->LastUpdated) =~ s/\s.*$//;
                if ($updt eq $day) {
                        $ticCount{$env}++;
                        my $transactions = $ticket->Transactions;
			# If we got this far we need to make sure we're only adding up 
			# the time on transactions that took place during the month
                        while (my $transaction  = $transactions->Next) {
                                (my $date = $transaction->Created) =~ s/\s.*$//;
                                next unless ($transaction->TimeTaken);
                                next unless ($date eq $day);
                                $timeWorked{$env} += $transaction->TimeTaken;
                        }
                }

        }

}

open TIMESHEET,  ">/work_reports/monthly/timesheet_for_$months{$month}" . "-" . $year . ".txt";

# Print the header for our data
printf TIMESHEET "\nCustomer Timesheet for $months{$month}, " . $year . "\n\n";
printf TIMESHEET "%33s%10s\n", "Time", "Avg Time";
printf TIMESHEET "%18s%8s%7s%10s\n", "Environment", "Tkts", "hh:mm", "hh:mm";
print TIMESHEET ("-" x 44);
print TIMESHEET "\n\n";

# Process each environment by converting the time, which is in minutes,
# to an hh:mm format then cleanly output each , the number of tickets
# submitted by the customer durning the month and the formatted time and
# average time.
foreach my $enviro (sort keys %timeWorked) {
        my @endTime;
        my @endTime2;
        my $temp          = $timeWorked{$enviro};
        my $temp2         = $temp / 60;
        my @temp          = split /\./, $temp2;
        $endTime[0]       = $temp[0];
        $endTime[1]       = $temp % 60;
        my $meanTemp = int(($temp / $ticCount{$enviro}) + 0.5);
        my @meanTime  = split /\./, ($meanTemp / 60);
        $endTime2[0]  = $meanTime[0];
        $endTime2[1]  = $meanTemp % 60;
        my $endTime   = sprintf '%d:%02d', @endTime[0,1];
        my $mean      = sprintf '%d:%02d', @endTime2[0,1];
        printf TIMESHEET "%18s%6s%8s%11s\n", $enviro, $ticCount{$enviro}, $endTime, $mean;
}

close TIMESHEET;

my $fullEmail    = new MIME::Lite(From    => $emailFrom,
                                  To      => $emailTo,
                                  Bcc     => $emailBcc,
                                  Subject => $emailSubj,
                                  Data    => $emailMsg,
                                  Type    => "multipart/mixed");

$fullEmail->attach(Type        => "text/plain",
                   Path        => "/work_reports/monthly/timesheet_for_$months{$month}" . "-" . $year . ".txt",
                   Disposition => "attachment");

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

print "\n";

exit;