TimeWorkedReport

From Request Tracker Wiki
Jump to navigation Jump to search

SUMMARY

Update

This contribution has now been properly packaged for RT as an extension. It has been updated and configured for RT 4. Refer to the link in See Also.

This report allows the user to specify a datetime range and one or more queues, and displays the time worked for each ticket in the selected queue(s) which has > 0 minutes time worked in the datetime range. It will show non-superusers their own report and superusers a report of all users.

It also will allow superusers to organize the report by ticket, with a breakdown of the contributions to that ticket per-person, rather than the default by-person organization.

Written by Fran Fabrizio, fabrizio -at- uab -dot- edu.

REQUIREMENTS

This is known to work with RT 3.8.5. The changes needed to make it work with RT 4.0.x are outlined below.

KNOWN BUGS

At least two users have reported a problem with RT 3.6.x and errors related to undefined principal within the HasRight sub of Queue_Overlay.pm. The RT developers changed HasRight between 3.6 and 3.8 and the code changes seem centered around undefined principals, so if anyone knows what this is and how I can code around it in a way that would work for 3.6, please contact me.

SEE ALSO

http://search.cpan.org/dist/RT-Extension-ActivityReports/

Updated version below:

https://metacpan.org/pod/RT::Extension::TimeWorkedReport

If you upgrade to the new version and had the old one previously instaled, you will need to remove the files you created under ../local both for html and callbacks, and then clear the mason object cache in order for the new one to start working.

TODO

This extension is still in active development. Currently, the TODO list is:

  • Figure out a better way to format this wiki page and include the code here (possible line break issues, careful when cutting and pasting)
  • Package this as an extension with an installer
  • Clean up the interface (align the form fields better, create a prettier report)
  • Create a new role, something like TimeWorkedManager, rather than only showing the all-users report to SuperUsers
  • Allow users to control extension behavior via RT's Configuration area

FEEDBACK

If you have other suggestions/requests, please email me at fabrizio -at- uab -dot- edu. This is still early in development; expect rough edges.

INSTALLATION

  1. Copy $RT_HOME/share/html/Tools/Reports/index.html to $RT_HOME/local/html/Tools/Reports/index.html

Edit $RT_HOME/local/html/Tools/Reports/index.html and add the following lines to the anonymous hash pointed to by the $tabs variable (on or around line 56 of the file):

1  D => {
2      title       => loc('Time Worked Report'),
3      path        => '/Tools/Reports/TimeWorkedReport.html',
4      description => loc('A Time Worked Report'),
5  },

You may need to change the "D" to the next available letter, depending on other mods that you may have made.

  1. Copy $RT_HOME/share/html/Tools/Reports/Elements/Tabs to $RT_HOME/local/html/Tools/Reports/Elements/Tabs

Edit $RT_HOME/local/html/Tools/Reports/Elements/Tabs and add the following lines to the anonymous hash pointed to by the $tabs variable (on or around line 55 of that file):

1  d => {
2      title => loc('Time Worked Report'),
3      path  => 'Tools/Reports/TimeWorkedReport.html',
4  },

Again, you may need to change the "d" to the next available letter depending on other mods you may have made.

  1. Create the file $RT_HOME/local/html/Elements/SelectMultiQueue with the following content:
  1       %# BEGIN BPS TAGGED BLOCK {{{
  2       %#
  3       %# COPYRIGHT:
  4       %#
  5       %# This software is Copyright (c) 1996-2009 Best Practical Solutions, LLC
  6       %#                                          <jesse@bestpractical.com>
  7       %#
  8       %# (Except where explicitly superseded by other copyright notices)
  9       %#
 10       %#
 11       %# LICENSE:
 12       %#
 13       %# This work is made available to you under the terms of Version 2 of
 14       %# the GNU General Public License. A copy of that license should have
 15       %# been provided with this software, but in any event can be snarfed
 16       %# from www.gnu.org.
 17       %#
 18       %# This work is distributed in the hope that it will be useful, but
 19       %# WITHOUT ANY WARRANTY; without even the implied warranty of
 20       %# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 21       %# General Public License for more details.
 22       %#
 23       %# You should have received a copy of the GNU General Public License
 24       %# along with this program; if not, write to the Free Software
 25       %# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 26       %# 02110-1301 or visit their web page on the internet at
 27       %# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
 28       %#
 29       %#
 30       %# CONTRIBUTION SUBMISSION POLICY:
 31       %#
 32       %# (The following paragraph is not intended to limit the rights granted
 33       %# to you to modify and distribute this software under the terms of
 34       %# the GNU General Public License and is only of importance to you if
 35       %# you choose to contribute your changes and enhancements to the
 36       %# community by submitting them to Best Practical Solutions, LLC.)
 37       %#
 38       %# By intentionally submitting any modifications, corrections or
 39       %# derivatives to this work, or any other work intended for use with
 40       %# Request Tracker, to Best Practical Solutions, LLC, you confirm that
 41       %# you are the copyright holder for those contributions and you grant
 42       %# Best Practical Solutions,  LLC a nonexclusive, worldwide, irrevocable,
 43       %# royalty-free, perpetual, license to use, copy, create derivative
 44       %# works based on those contributions, and sublicense and distribute
 45       %# those contributions and any derivatives thereof.
 46       %#
 47       %# END BPS TAGGED BLOCK }}}
 48       % if ($Lite) {
 49       %     my $d = new RT::Queue($session{'CurrentUser'});
 50       %     $d->Load($Default);
 51       <input name="<%$Name%>" size="25" value="<%$d->Name%>" class="<%$Class%>" />
 52       % }
 53       % else {
 54       %     # $Default will be an arrayref if multiple queues are selected, or a
 55       %     # scalar if 0-1 queues are selected.  Hence, this ugly processing logic.
 56       %     my %selected;
 57       %     if (ref $Default) {
 58       %       for (@$Default) {
 59       %         $selected{$_} = 1;
 60       %       }
 61       %     } else {
 62       %       $selected{$Default} = 1;
 63       %     }
 64       <select name="<%$Name%>" <% ($OnChange) ? 'onchange="'.$OnChange.'"' : '' |n %> class="  <%$Class%>" MULTIPLE>
 65       %     if ($ShowNullOption) {
 66         <option value="">-</option>
 67       %     }
 68       %     for my $queue (@{$session{$cache_key}}) {
 69         <option value="<% ($NamedValues ? $queue->{Name} : $queue->{Id}) %>"
 70 
 71 <code><pre>  %# if ($queue-&amp;gt;{Id} eq ($Default||'') || $queue-&amp;gt;{Name} eq ($Default||'')) {
 72   % if($selected{$queue-&amp;gt;{Id}}) {
 73    selected="selected"
 74   % }
 75 
 76   &amp;gt;
 77       &amp;lt;%$queue-&amp;gt;{Name}%&amp;gt;
 78 
 79   %             if ($Verbose and $queue-&amp;gt;{Description}) {
 80       (&amp;lt;%$queue-&amp;gt;{Description}%&amp;gt;)
 81   %             }
 82     &amp;lt;/option&amp;gt;
 83   %     }
 84   &amp;lt;/select&amp;gt;
 85   % }
 86   &amp;lt;%args&amp;gt;
 87   $CheckQueueRight =&amp;gt; 'CreateTicket'
 88   $ShowNullOption =&amp;gt; 1
 89   $ShowAllQueues =&amp;gt; 1
 90   $Name =&amp;gt; undef
 91   $Verbose =&amp;gt; undef
 92   $NamedValues =&amp;gt; 0
 93   $Default =&amp;gt; 0
 94   $Lite =&amp;gt; 0
 95   $OnChange =&amp;gt; undef
 96   $Class =&amp;gt; 'select-queue'
 97   &amp;lt;/%args&amp;gt;
 98   &amp;lt;%init&amp;gt;
 99   my $cache_key = "SelectQueue---"
100                   . $session{'CurrentUser'}-&amp;gt;Id
101                   . "---$CheckQueueRight---$ShowAllQueues";
102 
103   if (not defined $session{$cache_key} and not $Lite) {
104       my $q = new RT::Queues($session{'CurrentUser'});
105       $q-&amp;gt;UnLimit;
106 
107       while (my $queue = $q-&amp;gt;Next) {
108           if ($ShowAllQueues || $queue-&amp;gt;CurrentUserHasRight($CheckQueueRight)) {
109               push @{$session{$cache_key}}, {
110                   Id          =&amp;gt; $queue-&amp;gt;Id,
111                   Name        =&amp;gt; $queue-&amp;gt;Name,
112                   Description =&amp;gt; $queue-&amp;gt;Description,
113               };
114           }
115       }
116   }
117   &amp;lt;/%init&amp;gt;
118 </pre></code>

This is a very slightly modified form of SelectQueue which ships with RT in the same directory. You can diff the two to see what I have changed.

  1. Create the file $RT_HOME/local/html/Tools/Reports/TimeWorkedReport.html with the following content:
  1        &lt;%args&gt;
  2        $startdate =&gt; undef
  3        $enddate   =&gt; undef
  4        $queues    =&gt; undef
  5        $byticket  =&gt; undef
  6       &lt;/%args&gt;
  7 
  8 <code><pre>  &amp;lt;&amp;amp; /Elements/Header, Title =&amp;gt; $title &amp;amp;&amp;gt;
  9   &amp;lt;&amp;amp; /Tools/Reports/Elements/Tabs, current_tab =&amp;gt; 'Tools/Reports/TimeWorkedReport.html', Title  =&amp;gt; $title &amp;amp;&amp;gt;
 10   &amp;lt;hr&amp;gt;
 11 
 12   &amp;lt;%init&amp;gt;
 13   my ($start_date, $end_date, $effective_end_date, $title);
 14 
 15   $title = loc('Time worked report');
 16 
 17   $start_date = RT::Date-&amp;gt;new($session{'CurrentUser'});
 18   $end_date   = RT::Date-&amp;gt;new($session{'CurrentUser'});
 19 
 20   # If we have a value for start date, parse it into an RT::Date object
 21   if ($startdate) {
 22     $start_date-&amp;gt;Set(Format =&amp;gt; 'unknown', Value =&amp;gt; $startdate);
 23     # And then get it back as an ISO string for display purposes, in the form field and
 24     # report header
 25     $startdate = $start_date-&amp;gt;AsString(Format =&amp;gt; 'ISO', Timezone =&amp;gt; 'server');
 26   }
 27 
 28   # Same treatment for end date
 29   if ($enddate) {
 30     $end_date-&amp;gt;Set(Format =&amp;gt; 'unknown', Value =&amp;gt; $enddate);
 31     $enddate = $end_date-&amp;gt;AsString(Format =&amp;gt; 'ISO', Timezone =&amp;gt; 'server');
 32   }
 33 
 34   &amp;lt;/%init&amp;gt;
 35 
 36   &amp;lt;form method="post" action="TimeWorkedReport.html"&amp;gt;
 37    &amp;lt;br /&amp;gt;
 38     &amp;lt;&amp;amp;|/l&amp;amp;&amp;gt;Start date&amp;lt;/&amp;amp;&amp;gt;:
 39     &amp;lt;&amp;amp; /Elements/SelectDate, Name =&amp;gt; 'startdate', Default =&amp;gt; ($startdate) ?  $start_date-&amp;gt;AsString(Format =&amp;gt; 'ISO', Timezone =&amp;gt; 'server') : ''&amp;amp;&amp;gt;
 40     (report will start from midnight on this day unless you indicate otherwise)
 41    &amp;lt;br /&amp;gt;
 42     &amp;lt;&amp;amp;|/l&amp;amp;&amp;gt;End date&amp;lt;/&amp;amp;&amp;gt;:
 43     &amp;lt;&amp;amp; /Elements/SelectDate, Name =&amp;gt; 'enddate', Default =&amp;gt; ($enddate) ?  $end_date-&amp;gt;AsString(Format =&amp;gt; 'ISO', Timezone =&amp;gt; 'server') : ''&amp;amp;&amp;gt;
 44     (report will -not- be inclusive of this day unless you change the time from midnight)
 45    &amp;lt;br /&amp;gt;
 46     &amp;lt;&amp;amp;|/l&amp;amp;&amp;gt;Queues&amp;lt;/&amp;amp;&amp;gt;:
 47     &amp;lt;&amp;amp; /Elements/SelectMultiQueue, Name =&amp;gt; 'queues', Default =&amp;gt; ($queues) ? $queues : ''&amp;amp;&amp;gt;
 48    &amp;lt;br /&amp;gt;
 49     &amp;lt;&amp;amp; /Elements/Checkbox, Name =&amp;gt; 'byticket', Default =&amp;gt; ($byticket) ? 'checked' : ''&amp;amp;&amp;gt;
 50     Organize report by ticket instead of by person
 51   &amp;lt;&amp;amp; /Elements/Submit&amp;amp;&amp;gt;
 52 
 53   &amp;lt;/form&amp;gt;
 54 
 55 
 56   &amp;lt;%perl&amp;gt;
 57   # TimeWorkedReport
 58   # Version 0.04  2009-09-28
 59   #
 60   # Fran Fabrizio, UAB CIS, fran@cis.uab.edu
 61 
 62   use strict;
 63 
 64   # if we are just getting here and the form values are empty, we are done
 65   if (!$startdate || !$enddate) {
 66     return;
 67   }
 68 
 69   # get the queue object(s)
 70   my $queuesobj = new RT::Queues($session{CurrentUser});
 71   my ($queuelist, %queuesofinterest);
 72 
 73   # The user's choice of queues will come in from the web form in the $queues variable, which is
 74   # mapped to the SELECT field on the web interface for the report.  Unfortunately, if the user
 75   # chooses just one queue, $queues will have a scalar value, but if the user chooses multiple
 76   # queues, it will be an arrayref.  So we need to check for each case and process differently.
 77   #
 78   # What we want to construct is the %queuesofinterest simple lookup hash which defines a key
 79   # that is the queue ID for each queue selected, and the $queuelist string, which is just for
 80   # displaying the list of queues in the report header
 81   $queues = [ $queues ] unless ref($queues);
 82 
 83   for (@$queues) {
 84     $queuesobj-&gt;Limit(FIELD =&gt; "Id", OPERATOR =&gt; "=", VALUE =&gt; $_, ENTRYAGGREGATOR =&gt; "OR");
 85     $queuesofinterest{$_} = 1;
 86   }
 87   $queuelist = join ", ", map {$_-&gt;Name} @{$queuesobj-&gt;ItemsArrayRef};
 88 
 89   # hash to hold statistics
 90   # %stats will be a multilevel hash - first level keys are the usernames, second level keys are
 91   # the ticket IDs, and for each ticket, we store an anonymous hash with keys Subject and  TimeWorked
 92   # (this implies that a single ticket can live under two+ users if they both worked the ticket)
 93   my %stats;
 94 
 95   # Get a new transactions object to hold transaction search results for this ticket
 96   my $trans = new RT::Transactions($session{'CurrentUser'});
 97 
 98   # only in the period of interest
 99   $trans-&amp;gt;Limit(FIELD =&amp;gt; 'Created', OPERATOR =&amp;gt; '&amp;gt;', VALUE =&amp;gt; $startdate);
100   $trans-&amp;gt;Limit(FIELD =&amp;gt; 'Created', OPERATOR =&amp;gt; '&amp;lt;', VALUE =&amp;gt; $enddate, ENTRYAGGREGATOR =&amp;gt;  'AND');
101 
102   # now start counting all the TimeTaken by examining transactions associated with this ticket
103   while (my $tr = $trans-&amp;gt;Next) {
104 
105    # did this transaction take any time?  RT records this -either- in TimeTaken column or by
106    # indicating "TimeWorked" in the Field column, depending on how the user inputted the time.
107    if (($tr-&amp;gt;TimeTaken != 0) || ($tr-&amp;gt;Field &amp;amp;&amp;amp; $tr-&amp;gt;Field eq 'TimeWorked')) {
108      # Got a hot one - what ticket is this?
109      my $t = new RT::Ticket($session{'CurrentUser'});
110      $t-&amp;gt;Load($tr-&amp;gt;ObjectId);
111 
112      if (!$t) {
113        # unable to retrieve a ticket for this transaction
114        # hopefully we don't ever reach here!
115        next;
116      } else {
117        # Is a queue selected and is this ticket in a queue we care about?
118        if ($queuelist &amp;&amp; !$queuesofinterest{$t-&amp;gt;Queue}) {
119          next;
120        }
121      }
122 
123      # If this is time logged by user RT_System, it's the result of a ticket merge
124      # In order to avoid double-counting minutes in --byticket mode, or the less serious
125      # issue of displaying a report for user RT_System in normal mode, we skip this entirely
126      if ($tr-&amp;gt;CreatorObj-&amp;gt;Name eq 'RT_System') {
127        next;
128      }
129 
130      # we've got some time to account for
131 
132      # is this the first time this person is charging time to this ticket?
133      # if so, add this ticket subject to the data structure
134      if (!exists($stats{$tr-&amp;gt;CreatorObj-&amp;gt;Name}{$t-&amp;gt;id}{Subject})) {
135        $stats{$tr-&amp;gt;CreatorObj-&amp;gt;Name}{$t-&amp;gt;id}{Subject} = $t-&amp;gt;Subject;
136      }
137 
138      if ($tr-&amp;gt;TimeTaken != 0) {
139        # this was a comment or correspondence where the user also added some time worked
140        # value of interest appears in Transaction's TimeTaken column
141        $stats{$tr-&amp;gt;CreatorObj-&amp;gt;Name}{$t-&amp;gt;id}{TimeWorked} += $tr-&amp;gt;TimeTaken;
142      } else {
143        # this was a direct update of the time worked field from the Basics or Jumbo ticket update page
144        # values of interest appear in Transaction's OldValue and NewValue columns
145        # RT does not use the TimeTaken column in this instance.
146        $stats{$tr-&amp;gt;CreatorObj-&amp;gt;Name}{$t-&amp;gt;id}{TimeWorked} += $tr-&amp;gt;NewValue - $tr-&amp;gt;OldValue;
147      }
148    }
149   }
150 
151   # report output starts here
152   # output:
153   #  normal user: their own time worked report, most worked ticket to least worked ticket
154   #  superuser:   everyone's time worked report, in username alpha order, then by most worked to least worked
155   #  superuser+byticket: most worked ticket first, with everyone's contribution ranked by  biggest contribution to smallest
156 
157   print "&amp;lt;h2&amp;gt;TIME WORKED REPORT FOR QUEUE(S) " . $queuelist . "&amp;lt;/h2&amp;gt;";
158   print "&amp;lt;h3&amp;gt;Date Range: $startdate TO $enddate&amp;lt;/h3&amp;gt;";
159   if ($byticket) {
160     print "&amp;lt;h3&amp;gt;Organized by Ticket&amp;lt;/h3&amp;gt;";
161   }
162   print "&amp;lt;hr&amp;gt;";
163 
164   # if this person is not a superuser, we should only show them the report for themselves
165   # which means we should remove all keys from %stats except their own username
166   if (!($session{'CurrentUser'}-&amp;gt;HasRight(Right =&amp;gt; 'SuperUser', Object =&amp;gt; $RT::System))) {
167     my %tempstats;
168     $tempstats{$session{CurrentUser}-&amp;gt;Name} = $stats{$session{CurrentUser}-&amp;gt;Name};
169     %stats = %tempstats;
170   }
171 
172   if ($byticket) {
173     # if we're going to organize this by ticket, we need to transform the data first
174     # HAVE ENTRIES LIKE:  $stats{JoeUser}{12345}{TimeWorked} = 150
175     #                     $stats{JoeUser}{12345}{Subject} = "Fix the Fubar Widget"
176     # WANT ENTRIES LIKE:  $tstats{12345}{TotalTime} = 250
177     #                     $tstats{12345}{Subject} = "Fix the Fubar Widget"
178     #                     $tstats{12345}{People}{JoeUser} = 150
179     #                     $tstats{12345}{People}{JaneDoe} = 100
180 
181     my %tstats;
182     for my $person (keys %stats) {
183       for my $tid (keys %{$stats{$person}}) {
184         # grab the subject line if you don't have it already
185         if (!exists($tstats{$tid}{Subject})) {
186           $tstats{$tid}{Subject} = $stats{$person}{$tid}{Subject};
187         }
188         # now increment total time for this ticket
189         $tstats{$tid}{TotalTime} += $stats{$person}{$tid}{TimeWorked};
190         # and record this user's contribution to this ticket
191         $tstats{$tid}{People}{$person} = $stats{$person}{$tid}{TimeWorked};
192       }
193     }
194 
195     # Now emit the report
196     for my $tid (sort {$tstats{$b}{TotalTime} &amp;lt;=&amp;gt; $tstats{$a}{TotalTime}} keys %tstats) {
197       my $subject = $tstats{$tid}{Subject};
198       print "&amp;lt;H3&amp;gt;&amp;lt;A TARGET=\"_TimeWorked\" HREF=\"/Ticket/Display.html?id=$tid\"&amp;gt;$tid:  $subject&amp;lt;/A&amp;gt;&amp;lt;/H3&amp;gt;";
199       print "&amp;lt;TABLE BORDER=0 CELLSPACING=5&amp;gt;";
200       printf("&amp;lt;TR&amp;gt;&amp;lt;TH WIDTH=30&amp;gt;&amp;lt;/TH&amp;gt;&amp;lt;TH&amp;gt;%dm&amp;lt;/TH&amp;gt;&amp;lt;TH&amp;gt;%.1fh&amp;lt;/TH&amp;gt;&amp;lt;TH&amp;gt;TOTAL TIME&amp;lt;/TH&amp;gt;&amp;lt;/TR&amp;gt;",  $tstats{$tid}{TotalTime},($tstats{$tid}{TotalTime} / 60));
201       for my $person (sort {$tstats{$tid}{People}{$b} &amp;lt;=&amp;gt; $tstats{$tid}{People}{$a}} keys %{$tstats{$tid}{People}}) {
202         my $minutes = $tstats{$tid}{People}{$person};
203         printf("&amp;lt;TR&amp;gt;&amp;lt;TD&amp;gt;&amp;lt;/TD&amp;gt;&amp;lt;TD&amp;gt;%dm&amp;lt;/TD&amp;gt;&amp;lt;TD&amp;gt;%.1fh&amp;lt;/TD&amp;gt;&amp;lt;TD&amp;gt;%s&amp;lt;/TD&amp;gt;&amp;lt;/TR&amp;gt;",$minutes,($minutes /60),$person);
204       }
205       print "&amp;lt;/TABLE&amp;gt;";
206     }
207   } else {
208     # the existing %stats data structure is perfect for the default report, no data transform  needed
209     for my $person (sort keys %stats) {
210       # get the person object, so we can get the FriendlyName to use as header
211       my $personobj = new RT::User($session{CurrentUser});
212       $personobj-&amp;gt;Load($person);
213 
214       print "&amp;lt;h3&amp;gt;" . $personobj-&amp;gt;FriendlyName . "&amp;lt;/h3&amp;gt;";
215       print "&amp;lt;TABLE BORDER=0 CELLSPACING=5&amp;gt;";
216       print "&amp;lt;TR&amp;gt;&amp;lt;TH&amp;gt;MINUTES&amp;lt;/TH&amp;gt;&amp;lt;TH&amp;gt;HOURS&amp;lt;/TH&amp;gt;&amp;lt;TH&amp;gt;TICKET&amp;lt;/TH&amp;gt;&amp;lt;/TR&amp;gt;";
217       my $totalMinutes = 0;
218       for my $tid (sort {$stats{$person}{$b}{TimeWorked} &amp;lt;=&amp;gt; $stats{$person}{$a}{TimeWorked}}  keys %{$stats{$person}}) {
219         my $minutes = $stats{$person}{$tid}{TimeWorked};
220         my $subject = $stats{$person}{$tid}{Subject};
221         print "&amp;lt;TR&amp;gt;&amp;lt;TD ALIGN=RIGHT&amp;gt;${minutes}m&amp;lt;/TD&amp;gt;&amp;lt;TD ALIGN=RIGHT&amp;gt;" . sprintf("%.1fh",($minutes/60)) . "&amp;lt;/TD&amp;gt;" .
222                   "&amp;lt;TD&amp;gt;&amp;lt;A TARGET=\"_TimeWorked\" HREF=\"/Ticket/Display.html?id=$tid\"&amp;gt;$tid:  $subject&amp;lt;/A&amp;gt;&amp;lt;/TD&amp;gt;&amp;lt;/TR&amp;gt;";
223         $totalMinutes += $minutes;
224       }
225       print "&amp;lt;TR&amp;gt;&amp;lt;TD ALIGN=RIGHT&amp;gt;&amp;lt;B&amp;gt;${totalMinutes}m&amp;lt;/B&amp;gt;&amp;lt;/TD&amp;gt;&amp;lt;TD ALIGN=RIGHT&amp;gt;&amp;lt;B&amp;gt;" . sprintf("%.1fh",($totalMinutes/60)) . "&amp;lt;/B&amp;gt;&amp;lt;/TD&amp;gt;&amp;lt;TD&amp;gt;&amp;lt;B&amp;gt;TOTALS&amp;lt;/B&amp;gt;&amp;lt;/TD&amp;gt;&amp;lt;/TR&amp;gt;";
226       print "&amp;lt;/TABLE&amp;gt;";
227     }
228   }
229 
230   ##### helper functions below
231 
232   sub form_date_string {
233    # expects seven input params - year, month, day, hour, minute, second, offset
234    my $year = $_[0] - 1900;
235    my $mon = $_[1] - 1;
236    my $day = $_[2];
237    my $hour = $_[3] ? $_[3] : 0;
238    my $min = $_[4] ? $_[4] : 0;
239    my $sec = $_[5] ? $_[5] : 0;
240    my $offset = $_[6] ? $_[6] : 0;
241 
242    # convert to seconds since epoch, then adjust for the $offset, which is also in seconds
243    # we do this so we don't have to do fancy date arithmetic - we can just subtract one seconds
244    # value from the other seconds value
245    my $starttime = timelocal($sec,$min,$hour,$day,$mon,$year) - $offset;
246 
247    # convert back to component parts now that we've adjusted for offset
248    # this gives us the components which represent the GMT time for the local time that was entered
249    # on the command line
250    ($sec,$min,$hour,$day,$mon,$year) = localtime($starttime);
251 
252    # format the date string, padding with zeros if needed
253    return sprintf("%04d-%02d-%02d %02d:%02d:%02d", ($year+1900), ($mon+1), $day, $hour, $min, $sec);
254   }
255 
256   &amp;lt;/%perl&amp;gt;
257 </pre></code>
  1. Restart your server (unless you are using SetDevelMode, in which case restart is unnecessary).

TWEAKS FOR RT4 COMPATIBILITY

  • Skip installation steps 1 and 2 above. (RT4 uses a different mechanism for building menus and navigation. More on that later.)
  • Skip installation step 3. (This functionality is now built into the SelectQueue element included in RT4, so a separate element is no longer needed.)
  • Install the TimeWorkedReport.html as instructed in step 4, making the following modifications:
      • Line 9
        • currently begins with <& /Tools/Reports/Elements/Tabs...
        • Replace with <& /Elements/Tabs &>
      • Line 47
        • currently begins with <& /Elements/SelectMultiQueue...
        • Replace with <& /Elements/SelectQueue, Multiple => 1, Name => 'queues', Default => ($queues) ? $queues : '' &>
  • Restart server as instructed in step 5. At this point, you should be able to access the report by going directly to its url ($WebBaseURL/Tools/Reports/TimeWorkedReport.html). Next, we'll add it to the Tools menu.
  • Create the folder $RT_HOME/local/html/Callbacks/TimeWorkedReport/Elements/Tabs
  • In that folder, create a file called Privileged with the following contents:
1  &lt;%init&gt;
2  my $tools = Menu()-&gt;child('tools');
3  $tools-&gt;child( timeworked =&gt; title =&gt; 'Time Worked Report', path =&gt; '/Tools/Reports/TimeWorkedReport.html', description =&gt; 'Time Worked Report' );
4  &lt;/%init&gt;
5  &lt;%args&gt;
6  $Actions =&gt; undef
7  &lt;/%args&gt;
  • Restart the server again as instructed in step 5.

Much below this line needs cleanup and dates to when this was a standalone CLI script

GENERAL IMPLEMENTATION STRATEGY

I took a transaction-based approach. I use a SearchBuilder to grab all of the transactions that took place in the time period of interest (see TIME VALUES IN RT / HOW TO SPECIFY DATE RANGES below for time zone issue discussion). I then look at each one and go through the following workflow:

  1. Does this transaction represent a time worked modification? (see INCONSISTENT RECORDING OF TIME WORKED below for issues here) If no, skip to next transaction.
  2. Use this transaction's object ID to retrieve the associated ticket object. If this fails, report the error and skip to next transaction.
  3. Is this ticket in a queue of interest? If no, skip to next transaction.
  4. Was this transaction entered by the user RT_System? If so, it is the result of a merge operation. Don't count this time and skip to the next transaction. (The time will be captured elsewhere, under the original user that entered it, if it originally occurred in this time period of interest). Ok, if we got this far, we have real time to account for.
  5. If this is the first time we are seeing this person for this ticket, create the multilevel data structure to store ticket and time worked info.
  6. Increment the time worked value (see INCONSISTENT RECORDING OF TIME WORKED below for issues here).

TIME VALUES IN RT / HOW TO SPECIFY DATE RANGES

Internally in the database, RT stores time values as gmtime. This has implications for this script, which are best illustrated by an example.

I live in the US/Central time zone which is -18000 seconds, or -5 hours, off from GM time. I also like to work late at night. If I enter some time worked onto a ticket at 11pm on August 12th, or more formally, at "2009-08-12 23:00:00" in US Central time, that will be entered in the transaction table with a timestamp of "2009-08-13 04:00:00".

Now if I run this script with a startdate of "2009-08-12 00:00:00" and an enddate of "2009-08-12 23:59:59", I would reasonably expect to get all of the time I worked on August 12th, 2009. However, I would miss the last 5 hours in the day worth of transactions, because RT would have internally shifted the stored timestamp to a time in August 13th (which it already was, over in London!)

So, I assume that users of this script will be entering their time in their local location, and automatically adjust for this. In my case, this means when I enter a start or end date like "2009-08-12 00:00:00", this script will convert that to "2009-08-12 05:00:00".

Unrelated to this issue but also relevant to the area of time values, MySQL will treat the absence of the HH:MM:SS as 00:00:00.

In sum, if you want all the time worked for a week, let's say, from Sunday, August 9th through Saturday, August 15th (inclusive), if you use the start and end values "2009-08-09" and "2009-08-16" (Note: 16 not 15!) this script will do the right thing. To be more clear, you might use "2009-08-09" and "2009-08-15 23:59:59", but I am lazy and don't mind counting the first second of the 16th as part of the 15th. :-)

INCONSISTENT RECORDING OF TIME WORKED

Time can be entered on tickets in two main ways.

  1. Putting a value in the Time Worked field as part of a Comment or Correspondence transaction.
  2. Directly editing the Time Worked field as part of a Basic or Jumbo ticket update.

Unfortunately, the way that the time gets recorded is different for each scenario.

For scenario 1, in the transaction table the transaction will store the new time worked in the TimeTaken field as minutes.

For scenario 2, in the transaction table the transaction will use the OldValue and NewValue fields to store the old and new values.

So, the script needs to check for both cases, and in scenario 2, has to do a little math to figure out how much time was added.