httemplate/search/timeworked.html

   1 <% include( 'elements/search.html',
   2                  'title'       => 'Time Worked',
   3                  'menubar'     => [ 'Main menu' => $p, ],
   4                  'name'        => 'time',
   5                  'html_form'   => qq!<FORM NAME="timeForm" ACTION="${p}misc/timeworked.html" METHOD="POST">!,
   6                  'query'       => $query,
   7                  'count_query' => $count_query,
   8                  'header' => [ '#',
   9                                'Ticket',
  10                                'Date',
  11                                'Time',
  12                                '', # checkbox column
  13                              ],
  14                  'fields' => [ sub { shift->[0] },
  15                                sub { shift->[1] },
  16                                sub { shift->[2] },
  17                                sub { my $seconds = shift->[3];
  18                                      (($seconds < 0) ? '-' : '') .
  19                                      concise(duration($seconds));
  20                                    },
  21                                sub {
  22                                  my $row = shift;
  23                                  my $seconds = $row->[3];
  24                                  my $id = $row->[4];
  25                                  qq!<INPUT NAME="transactionid$id" TYPE="checkbox" VALUE="1">!.
  26                                  qq!<INPUT NAME="seconds$id" TYPE="hidden" VALUE="$seconds">!;
  27                                },
  28                              ],
  29                  'html_foot' => sub {
  30                                   '<BR><INPUT TYPE="button" VALUE="select all" onClick="setAll(true)">'.
  31                                   '<INPUT TYPE="button" VALUE="unselect all" onClick="setAll(false)">'.
  32                                   '<BR><INPUT TYPE="submit" NAME="action" VALUE="Assign to accounts"><BR>'.
  33                                   '<SCRIPT TYPE="text/javascript">'.
  34                                   '  function setAll(setTo) { '.
  35                                   '    theForm = document.timeForm;'.
  36                                   '    for (i=0,n=theForm.elements.length;i<n;i++)'.
  37                                   '      if (theForm.elements[i].name.indexOf("transactionid") != -1)'.
  38                                   '        theForm.elements[i].checked = setTo;'.
  39                                   '  }'.
  40                                   '</SCRIPT>';
  41                                 },
  42              )
  43
  44 %>
  45 <%init>
  46
  47 die "access denied"
  48   unless $FS::CurrentUser::CurrentUser->access_right('Time queue');
  49
  50 my @groupby = ();
  51
  52 my $transactiontime = "CASE transactions.type when 'Set' THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60 ELSE timetaken*60 END";
  53 push @groupby, "transactions.type";
  54 push @groupby, "newvalue";
  55 push @groupby, "oldvalue";
  56 push @groupby, "timetaken";
  57
  58 my $appliedtimeclause = "coalesce (sum(svc_acct_rt_transaction.seconds), 0)";
  59
  60 my $appliedtimeselect = "SELECT sum(seconds) FROM svc_acct_rt_transaction where transaction_id = transactions.id";
  61 push @groupby, "transactions.id";
  62
  63 my $wheretimeleft = "($transactiontime != ($appliedtimeselect) OR ($appliedtimeselect) is NULL)";
  64
  65 push @groupby, "tickets.id";
  66 push @groupby, "tickets.subject";
  67 push @groupby, "transactions.created";
  68
  69 my $groupby = join(',', @groupby);
  70
  71 my $query = "SELECT tickets.id,tickets.subject,to_char(transactions.created, 'Dy Mon DD HH24:MI:SS YYYY'),$transactiontime-$appliedtimeclause,transactions.id FROM transactions JOIN tickets ON transactions.objectid = tickets.id LEFT JOIN svc_acct_rt_transaction on transactions.id = svc_acct_rt_transaction.transaction_id WHERE objecttype='RT::Ticket' AND ((transactions.type='Set' AND field='TimeWorked') OR transactions.type='Comment' OR transactions.type='Correspond') AND ($wheretimeleft) GROUP BY $groupby ORDER BY transactions.created";
  72 my $count_query = "SELECT count(*) FROM transactions WHERE objecttype='RT::Ticket' AND ((transactions.type='Set' AND field='TimeWorked') OR transactions.type='Comment' OR transactions.type='Correspond') AND ($wheretimeleft)";
  73
  74 </%init>