TimeWorked

TimeWorked report is a custom report used mainly by managers to find out how much time users spent solving tickets. It has been write for RT 3.6.1 . The code has been written by Şubredu Manuel (diablo - packages.ro) Code updated for RT-3.8.2 by Disaster

INSTALL

Add to /path/to/rt/share/html/Tools/Reports/Elements/Tabs the following content:

d => {
    title => loc('Time worked'),
    path  => 'Tools/Reports/TimeWorked.html',
},

Add to /path/to/rt/share/html/Tools/Reports/index.html the following content:

D => {
    title       => loc('Time worked'),
    path        => '/Tools/Reports/TimeWorked.html',
    description => loc('Show how much time has been worked on resolved tickets for a given interval'),
},

Now, create /path/to/rt/share/html/Tools/Reports/TimeWorked.html with the following content:

<%args>
  $StartDate => undef
  $EndDate   => undef
 </%args>
 
 <& /Elements/Header, Title => $title &>
 <& /Tools/Reports/Elements/Tabs, current_tab => 'Tools/Reports/TimeWorked.html', Title => $title &>
 <hr>
 <%init>
 my ($start_date, $end_date, $title);
 
 $title = loc('Time worked in all queues');
 
 $start_date = RT::Date->new($session{'CurrentUser'});
 $end_date   = RT::Date->new($session{'CurrentUser'});
 
 if ($StartDate) {
   $start_date->Set(Format => 'unknown', Value => $StartDate);
   $StartDate = $start_date->AsString;
 }
 
 if ($EndDate) {
   $end_date->Set(Format => 'unknown', Value => $EndDate);
   $EndDate = $end_date->AsString;
 }
 
 </%init>
 
 <form method="post" action="TimeWorked.html">
  <br />
   <&|/l&>Start date</&>:
   <& /Elements/SelectDate, Name => 'StartDate', Default => ($StartDate) ? $start_date->ISO : ''&>
  <br />
   <&|/l&>End date</&>:
   <& /Elements/SelectDate, Name => 'EndDate', Default => ($EndDate) ? $end_date->ISO : ''&>
 
 <& /Elements/Submit&>
 
 </form>
 <%perl>
 my ($uhash, $ulist, $q_list, $queue_time);
 
 # First let's prepare to take the users list
 $ulist = new RT::Users($session{'CurrentUser'});
 
 # We consider only the priviledged users
 $ulist->LimitToPrivileged;
 
 # initialize the users hash information. This hash is going to be used
 # to hold all the time worked information
 
 while (my $user = $ulist->Next) {
   my %ustat;
 
   $ustat{'real_name'} = $user->RealName;
   $ustat{'total'}     = 0;
   $ustat{'queues'}    = ();
 
   $uhash->{$user->Id} = \%ustat;
 }
 
 # prepare to take the queues list
 $q_list = RT::Queues->new($session{'CurrentUser'});
 $q_list->UnLimit;
 
 # for each queue
 while(my $queue = $q_list->Next) {
   next if ($queue->Disabled);
 
   my ($sql_query, $tickets);
 
   $sql_query = q{};
   $tickets = RT::Tickets->new($session{'CurrentUser'});
   $queue_time->{$queue->Name} = 0;
 
   # select only the RESOLVED tickets from the current queue
   $sql_query = sprintf("Status = 'resolved' AND Queue = '%s'", $queue->Name);
   if ($StartDate) {
     $sql_query .= sprintf(" AND Resolved >= '%s'", $start_date->ISO);
   }
   if ($EndDate) {
     $sql_query .= sprintf(" AND Resolved <= '%s'", $end_date->ISO);
   }
 
   $tickets->FromSQL($sql_query);
 
   # cycle trough the tickets from the current queue
   while(my $ticket = $tickets->Next()) {
     my $user_id;
 
     $user_id = $ticket->Owner;
 
     # adjust the total time worked by all users on the current queue
     $queue_time->{$queue->Name} += $ticket->TimeWorked;
 
     # adjust the total time worked (for all queues)
     $uhash->{$user_id}->{total} += $ticket->TimeWorked;
 
     # adjust the time spent on the current queue
     if ($uhash->{$user_id}->{queues}->{$queue->Name}) {
       $uhash->{$user_id}->{queues}->{$queue->Name} += $ticket->TimeWorked;
     }
     else {
       $uhash->{$user_id}->{queues}->{$queue->Name} = $ticket->TimeWorked;
     }
   }
 }
 foreach(keys(%{ $uhash })) {
   my $user_id = $_;
 
   next if ($uhash->{$user_id}->{total}        == 0);
   print '<h2>',$uhash->{$user_id}->{real_name},'</h2>';
   print '<table><tdata>',$/;
 
   foreach(keys(%{ $uhash->{$user_id}->{queues} })) {
 
     next if ($uhash->{$user_id}->{queues}->{$_} == 0);
 
     print '<tr>',
            '<td>',$_,'</td>',
            '<td>',$uhash->{$user_id}->{queues}->{$_},'</td>',
           '</tr>',$/;
   }
   print '<tr style="font-weight: bold;"><td>Total</td><td>',$uhash->{$user_id}->{total},'</td></tr>',
         '</tdata></table>',$/;
 }
 
 if (keys(%{ $queue_time })) {
   my $total_queues_time = 0;
 
   print '<h2>Queue times</h2>',
         '<table><tdata>',$/;
   foreach(keys(%{ $queue_time })) {
     print '<tr>',
            '<td>',$_,'</td>',
            '<td>',$queue_time->{$_},'</td>',
           '</tr>',$/;
 
     $total_queues_time += $queue_time->{$_};
   }
   print '<tr style="font-weight: bold">',
          '<td>Total</td>',
          '<td>',$total_queues_time,'</td>',
         '</tr>',$/,
        '</tdata></table>',$/;
 }
 
 </%perl>