TimeWorkedReport

1. 1. 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.

1. 1. 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.

1. 1. 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.

1. 1. 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.

1. 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

1. 1. 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.

1. 1. 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):

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

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

2. 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):

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

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

3. Create the file $RT_HOME/local/html/Elements/SelectMultiQueue with the following content:

      %# BEGIN BPS TAGGED BLOCK {{{
      %#
      %# COPYRIGHT:
      %#
      %# This software is Copyright (c) 1996-2009 Best Practical Solutions, LLC
      %#                                          <jesse@bestpractical.com>
      %#
      %# (Except where explicitly superseded by other copyright notices)
      %#
      %#
      %# LICENSE:
      %#
      %# This work is made available to you under the terms of Version 2 of
      %# the GNU General Public License. A copy of that license should have
      %# been provided with this software, but in any event can be snarfed
      %# from www.gnu.org.
      %#
      %# This work is distributed in the hope that it will be useful, but
      %# WITHOUT ANY WARRANTY; without even the implied warranty of
      %# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
      %# General Public License for more details.
      %#
      %# You should have received a copy of the GNU General Public License
      %# along with this program; if not, write to the Free Software
      %# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
      %# 02110-1301 or visit their web page on the internet at
      %# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html.
      %#
      %#
      %# CONTRIBUTION SUBMISSION POLICY:
      %#
      %# (The following paragraph is not intended to limit the rights granted
      %# to you to modify and distribute this software under the terms of
      %# the GNU General Public License and is only of importance to you if
      %# you choose to contribute your changes and enhancements to the
      %# community by submitting them to Best Practical Solutions, LLC.)
      %#
      %# By intentionally submitting any modifications, corrections or
      %# derivatives to this work, or any other work intended for use with
      %# Request Tracker, to Best Practical Solutions, LLC, you confirm that
      %# you are the copyright holder for those contributions and you grant
      %# Best Practical Solutions,  LLC a nonexclusive, worldwide, irrevocable,
      %# royalty-free, perpetual, license to use, copy, create derivative
      %# works based on those contributions, and sublicense and distribute
      %# those contributions and any derivatives thereof.
      %#
      %# END BPS TAGGED BLOCK }}}
      % if ($Lite) {
      %     my $d = new RT::Queue($session{'CurrentUser'});
      %     $d->Load($Default);
      <input name="<%$Name%>" size="25" value="<%$d->Name%>" class="<%$Class%>" />
      % }
      % else {
      %     # $Default will be an arrayref if multiple queues are selected, or a
      %     # scalar if 0-1 queues are selected.  Hence, this ugly processing logic.
      %     my %selected;
      %     if (ref $Default) {
      %       for (@$Default) {
      %         $selected{$_} = 1;
      %       }
      %     } else {
      %       $selected{$Default} = 1;
      %     }
      <select name="<%$Name%>" <% ($OnChange) ? 'onchange="'.$OnChange.'"' : '' |n %> class="  <%$Class%>" MULTIPLE>
      %     if ($ShowNullOption) {
        <option value="">-</option>
      %     }
      %     for my $queue (@{$session{$cache_key}}) {
        <option value="<% ($NamedValues ? $queue->{Name} : $queue->{Id}) %>"
      
      %# if ($queue->{Id} eq ($Default||'') || $queue->{Name} eq ($Default||'')) {
      % if($selected{$queue->{Id}}) {
       selected="selected"
      % }
      
      >
          <%$queue->{Name}%>
      
      %             if ($Verbose and $queue->{Description}) {
          (<%$queue->{Description}%>)
      %             }
        </option>
      %     }
      </select>
      % }
      <%args>
      $CheckQueueRight => 'CreateTicket'
      $ShowNullOption => 1
      $ShowAllQueues => 1
      $Name => undef
      $Verbose => undef
      $NamedValues => 0
      $Default => 0
      $Lite => 0
      $OnChange => undef
      $Class => 'select-queue'
      </%args>
      <%init>
      my $cache_key = "SelectQueue---"
                      . $session{'CurrentUser'}->Id
                      . "---$CheckQueueRight---$ShowAllQueues";
      
      if (not defined $session{$cache_key} and not $Lite) {
          my $q = new RT::Queues($session{'CurrentUser'});
          $q->UnLimit;
      
          while (my $queue = $q->Next) {
              if ($ShowAllQueues || $queue->CurrentUserHasRight($CheckQueueRight)) {
                  push @{$session{$cache_key}}, {
                      Id          => $queue->Id,
                      Name        => $queue->Name,
                      Description => $queue->Description,
                  };
              }
          }
      }
      </%init>

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.

4. Create the file $RT_HOME/local/html/Tools/Reports/TimeWorkedReport.html with the following content:

       <%args>
       $startdate => undef
       $enddate   => undef
       $queues    => undef
       $byticket  => undef
      </%args>
      
      <& /Elements/Header, Title => $title &>
      <& /Tools/Reports/Elements/Tabs, current_tab => 'Tools/Reports/TimeWorkedReport.html', Title  => $title &>
      <hr>
      
      <%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');
      }
      
      </%init>
      
      <form method="post" action="TimeWorkedReport.html">
       <br />
        <&|/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)
       <br />
        <&|/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)
       <br />
        <&|/l&>Queues</&>:
        <& /Elements/SelectMultiQueue, Name => 'queues', Default => ($queues) ? $queues : ''&>
       <br />
        <& /Elements/Checkbox, Name => 'byticket', Default => ($byticket) ? 'checked' : ''&>
        Organize report by ticket instead of by person
      <& /Elements/Submit&>
      
      </form>
      
      
      <%perl>
      # TimeWorkedReport
      # Version 0.04  2009-09-28
      #
      # Fran Fabrizio, UAB CIS, fran@cis.uab.edu
      
      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
      $queues = [ $queues ] unless ref($queues);
     
      for (@$queues) {
        $queuesobj->Limit(FIELD => "Id", OPERATOR => "=", VALUE => $_, ENTRYAGGREGATOR => "OR");
        $queuesofinterest{$_} = 1;
      }
      $queuelist = join ", ", map {$_->Name} @{$queuesobj->ItemsArrayRef};
      
      # 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->TimeTaken != 0) || ($tr->Field && $tr->Field eq 'TimeWorked')) {
         # 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 a queue selected and is this ticket in a queue we care about?
           if ($queuelist && !$queuesofinterest{$t->Queue}) {
             next;
           }
         }
      
         # If this is time logged by user RT_System, it's the result of a ticket merge
         # In order to avoid double-counting minutes in --byticket mode, or the less serious
         # issue of displaying a report for user RT_System in normal mode, we skip this entirely
         if ($tr->CreatorObj->Name eq 'RT_System') {
           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{$tr->CreatorObj->Name}{$t->id}{Subject})) {
           $stats{$tr->CreatorObj->Name}{$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{$tr->CreatorObj->Name}{$t->id}{TimeWorked} += $tr->TimeTaken;
         } else {
           # 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{$tr->CreatorObj->Name}{$t->id}{TimeWorked} += $tr->NewValue - $tr->OldValue;
         }
       }
      }
      
      # 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 "<h2>TIME WORKED REPORT FOR QUEUE(S) " . $queuelist . "</h2>";
      print "<h3>Date Range: $startdate TO $enddate</h3>";
      if ($byticket) {
        print "<h3>Organized by Ticket</h3>";
      }
      print "<hr>";
      
      # if this person is not a superuser, we should only show them the report for themselves
      # which means we should remove all keys from %stats except their own username
      if (!($session{'CurrentUser'}->HasRight(Right => 'SuperUser', Object => $RT::System))) {
        my %tempstats;
        $tempstats{$session{CurrentUser}->Name} = $stats{$session{CurrentUser}->Name};
        %stats = %tempstats;
      }
      
      if ($byticket) {
        # if we're going to organize this by ticket, we need to transform the data first
        # HAVE ENTRIES LIKE:  $stats{JoeUser}{12345}{TimeWorked} = 150
        #                     $stats{JoeUser}{12345}{Subject} = "Fix the Fubar Widget"
        # WANT ENTRIES LIKE:  $tstats{12345}{TotalTime} = 250
        #                     $tstats{12345}{Subject} = "Fix the Fubar Widget"
        #                     $tstats{12345}{People}{JoeUser} = 150
        #                     $tstats{12345}{People}{JaneDoe} = 100
      
        my %tstats;
        for my $person (keys %stats) {
          for my $tid (keys %{$stats{$person}}) {
            # grab the subject line if you don't have it already
            if (!exists($tstats{$tid}{Subject})) {
              $tstats{$tid}{Subject} = $stats{$person}{$tid}{Subject};
            }
            # now increment total time for this ticket
            $tstats{$tid}{TotalTime} += $stats{$person}{$tid}{TimeWorked};
            # and record this user's contribution to this ticket
            $tstats{$tid}{People}{$person} = $stats{$person}{$tid}{TimeWorked};
          }
        }
      
        # Now emit the report
        for my $tid (sort {$tstats{$b}{TotalTime} <=> $tstats{$a}{TotalTime}} keys %tstats) {
          my $subject = $tstats{$tid}{Subject};
          print "<H3><A TARGET=\"_TimeWorked\" HREF=\"/Ticket/Display.html?id=$tid\">$tid:  $subject</A></H3>";
          print "<TABLE BORDER=0 CELLSPACING=5>";
          printf("<TR><TH WIDTH=30></TH><TH>%dm</TH><TH>%.1fh</TH><TH>TOTAL TIME</TH></TR>",  $tstats{$tid}{TotalTime},($tstats{$tid}{TotalTime} / 60));
          for my $person (sort {$tstats{$tid}{People}{$b} <=> $tstats{$tid}{People}{$a}} keys %{$tstats{$tid}{People}}) {
            my $minutes = $tstats{$tid}{People}{$person};
            printf("<TR><TD></TD><TD>%dm</TD><TD>%.1fh</TD><TD>%s</TD></TR>",$minutes,($minutes /60),$person);
          }
          print "</TABLE>";
        }
      } else {
        # the existing %stats data structure is perfect for the default report, no data transform  needed
        for my $person (sort keys %stats) {
          # get the person object, so we can get the FriendlyName to use as header
          my $personobj = new RT::User($session{CurrentUser});
          $personobj->Load($person);
      
          print "<h3>" . $personobj->FriendlyName . "</h3>";
          print "<TABLE BORDER=0 CELLSPACING=5>";
          print "<TR><TH>MINUTES</TH><TH>HOURS</TH><TH>TICKET</TH></TR>";
          my $totalMinutes = 0;
          for my $tid (sort {$stats{$person}{$b}{TimeWorked} <=> $stats{$person}{$a}{TimeWorked}}  keys %{$stats{$person}}) {
            my $minutes = $stats{$person}{$tid}{TimeWorked};
            my $subject = $stats{$person}{$tid}{Subject};
            print "<TR><TD ALIGN=RIGHT>${minutes}m</TD><TD ALIGN=RIGHT>" . sprintf("%.1fh",($minutes/60)) . "</TD>" .
                      "<TD><A TARGET=\"_TimeWorked\" HREF=\"/Ticket/Display.html?id=$tid\">$tid:  $subject</A></TD></TR>";
            $totalMinutes += $minutes;
          }
          print "<TR><TD ALIGN=RIGHT><B>${totalMinutes}m</B></TD><TD ALIGN=RIGHT><B>" . sprintf("%.1fh",($totalMinutes/60)) . "</B></TD><TD><B>TOTALS</B></TD></TR>";
          print "</TABLE>";
        }
      }
      
      ##### helper functions below
      
      sub form_date_string {
       # expects seven input params - year, month, day, hour, minute, second, offset
       my $year = $_[0] - 1900;
       my $mon = $_[1] - 1;
       my $day = $_[2];
       my $hour = $_[3] ? $_[3] : 0;
       my $min = $_[4] ? $_[4] : 0;
       my $sec = $_[5] ? $_[5] : 0;
       my $offset = $_[6] ? $_[6] : 0;
      
       # convert to seconds since epoch, then adjust for the $offset, which is also in seconds
       # we do this so we don't have to do fancy date arithmetic - we can just subtract one seconds
       # value from the other seconds value
       my $starttime = timelocal($sec,$min,$hour,$day,$mon,$year) - $offset;
      
       # convert back to component parts now that we've adjusted for offset
       # this gives us the components which represent the GMT time for the local time that was entered
       # on the command line
       ($sec,$min,$hour,$day,$mon,$year) = localtime($starttime);
      
       # format the date string, padding with zeros if needed
       return sprintf("%04d-%02d-%02d %02d:%02d:%02d", ($year+1900), ($mon+1), $day, $hour, $min, $sec);
      }
      
      </%perl>

5. Restart your server (unless you are using SetDevelMode, in which case restart is unnecessary).

1. 1. 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:

 <%init>
 my $tools = Menu()->child('tools');
 $tools->child( timeworked => title => 'Time Worked Report', path => '/Tools/Reports/TimeWorkedReport.html', description => 'Time Worked Report' );
 </%init>
 <%args>
 $Actions => undef
 </%args>

• 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

1. 1. 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).

1. 1. 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. :-)

1. 1. 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.