Hi. I have created a modification of this script that groups and summarizes the time by a custom field (named "Customer"). I have attached the modified script.
/Johan > -----Original Message----- > From: [email protected] [mailto:rt-users- > [email protected]] On Behalf Of Johnny.five > Sent: den 29 december 2011 16:09 > To: [email protected] > Subject: [rt-users] Limiting by custom field on Time Worked report > > > Hello! > > I'm trying to figure out how to get a custom field onto the Time Worked > report here: > > http://requesttracker.wikia.com/wiki/TimeWorked > > The idea is to allow filtering of results by a custom field in a ticket. > I've been trying to wrap my head around the processes/syntax or RT for > hours > - any guru out there willing to share some knowledge? > > ~John > -- > View this message in context: http://old.nabble.com/Limiting-by-custom- > field-on-Time-Worked-report-tp33050563p33050563.html > Sent from the Request Tracker - User mailing list archive at Nabble.com. > > -------- > RT Training Sessions (http://bestpractical.com/services/training.html) > * Boston March 5 & 6, 2012<%args> $startdate => undef $enddate => undef $queues => undef <& /Elements/Header, Title => $title &> <& /Tools/Reports/Elements/Tabs, current_tab => 'Tools/Reports/TimeWorkedCustomer.html', Title => $title &>
<%init> my ($start_date, $end_date, $effective_end_date, $title); $title = loc('Time worked report'); $start_date = RT::Date->new($session{'CurrentUser'}); $end_date = RT::Date->new($session{'CurrentUser'}); # If we have a value for start date, parse it into an RT::Date object if ($startdate) { $start_date->Set(Format => 'unknown', Value => $startdate); # And then get it back as an ISO string for display purposes, in the form field and # report header $startdate = $start_date->AsString(Format => 'ISO', Timezone => 'server'); } # Same treatment for end date if ($enddate) { $end_date->Set(Format => 'unknown', Value => $enddate); $enddate = $end_date->AsString(Format => 'ISO', Timezone => 'server'); }
<&|/l&>Start date: <& /Elements/SelectDate, Name => 'startdate', Default => ($startdate) ? $start_date->AsString(Format => 'ISO', Timezone => 'server') : ''&> (report will start from midnight on this day unless you indicate otherwise)
<&|/l&>End date: <& /Elements/SelectDate, Name => 'enddate', Default => ($enddate) ? $end_date->AsString(Format => 'ISO', Timezone => 'server') : ''&> (report will -not- be inclusive of this day unless you change the time from midnight)
<&|/l&>Queues: <& /Elements/SelectMultiQueue, Name => 'queues', Default => ($queues) ? $queues : ''&> <& /Elements/Submit&> <%perl> # TimeWorkedReport # Version 0.04 2009-09-28 # # Fran Fabrizio, UAB CIS, [email protected] use strict; # if we are just getting here and the form values are empty, we are done if (!$startdate || !$enddate) { return; } # get the queue object(s) my $queuesobj = new RT::Queues($session{CurrentUser}); my ($queuelist, %queuesofinterest); # The user's choice of queues will come in from the web form in the $queues variable, which is # mapped to the SELECT field on the web interface for the report. Unfortunately, if the user # chooses just one queue, $queues will have a scalar value, but if the user chooses multiple # queues, it will be an arrayref. So we need to check for each case and process differently. # # What we want to construct is the %queuesofinterest simple lookup hash which defines a key # that is the queue ID for each queue selected, and the $queuelist string, which is just for # displaying the list of queues in the report header if (ref $queues) { # multiple queues selected for (@$queues) { $queuesobj->Limit(FIELD => "Id", OPERATOR => "=", VALUE => $_, ENTRYAGGREGATOR => "OR"); $queuesofinterest{$_} = 1; } $queuelist = join ", ", map {$_->Name} @{$queuesobj->ItemsArrayRef}; } else { my $queue = new RT::Queue; $queue->Load(Id => $queues); $queuesofinterest{$queues} = 1; $queuelist = $queue->Name; } # hash to hold statistics # %stats will be a multilevel hash - first level keys are the usernames, second level keys are # the ticket IDs, and for each ticket, we store an anonymous hash with keys Subject and TimeWorked # (this implies that a single ticket can live under two+ users if they both worked the ticket) my %stats; # Get a new transactions object to hold transaction search results for this ticket my $trans = new RT::Transactions($session{'CurrentUser'}); # only in the period of interest $trans->Limit(FIELD => 'Created', OPERATOR => '>', VALUE => $startdate); $trans->Limit(FIELD => 'Created', OPERATOR => '<', VALUE => $enddate, ENTRYAGGREGATOR => 'AND'); # now start counting all the TimeTaken by examining transactions associated with this ticket while (my $tr = $trans->Next) { # did this transaction take any time? RT records this -either- in TimeTaken column or by # indicating "TimeWorked" in the Field column, depending on how the user inputted the time. if ($tr->ObjectType eq 'RT::Ticket') { # Got a hot one - what ticket is this? my $t = new RT::Ticket($session{'CurrentUser'}); $t->Load($tr->ObjectId); if (!$t) { # unable to retrieve a ticket for this transaction # hopefully we don't ever reach here! next; } else { # Is this ticket in a queue we care about? if (!$queuesofinterest{$t->Queue}) { next; } } # what customer is this? my $c = ""; my $cfs = $t->CustomFieldValues; while (my $cf = $cfs->Next) { if ($cf->CustomField == 19) { $c = $cf->Content; } } if (!$c) { # unable to retrieve a customer for this ticket # hopefully we don't ever reach here! next; } # we've got some time to account for # is this the first time this person is charging time to this ticket? # if so, add this ticket subject to the data structure if (!exists($stats{$c}{$t->id}{Subject})) { $stats{$c}{$t->id}{Subject} = $t->Subject; } if ($tr->TimeTaken != 0) { # this was a comment or correspondence where the user also added some time worked # value of interest appears in Transaction's TimeTaken column $stats{$c}{$t->id}{TimeWorked} += $tr->TimeTaken; } elsif ($tr->Field && $tr->Field eq 'TimeWorked') { # this was a direct update of the time worked field from the Basics or Jumbo ticket update page # values of interest appear in Transaction's OldValue and NewValue columns # RT does not use the TimeTaken column in this instance. $stats{$c}{$t->id}{TimeWorked} += $tr->NewValue - $tr->OldValue; } else { $stats{$c}{$t->id}{TimeWorked} += 0; } } } # report output starts here # output: # normal user: their own time worked report, most worked ticket to least worked ticket # superuser: everyone's time worked report, in username alpha order, then by most worked to least worked # superuser+byticket: most worked ticket first, with everyone's contribution ranked by biggest contribution to smallest print "
TIME WORKED REPORT FOR QUEUE(S) " . $queuelist . "
"; print "Date Range: $startdate TO $enddate
"; # the existing %stats data structure is perfect for the default report, no data transform needed for my $customer (sort keys %stats) { print "" . $customer . "
"; print "| ID | HOURS | SUBJECT | ||
|---|---|---|---|---|
| $tid | " . sprintf("%.1f",($minutes/60)) . " | " . # " | $subject | |
| $tid | $hours | " . " | $subject | |
| TOTAL | " . sprintf("%.1f",($totalMinutes/60)) . " |
-------- RT Training Sessions (http://bestpractical.com/services/training.html) * Boston March 5 & 6, 2012
