diff options
Diffstat (limited to 'httemplate/search/timeworked.html')
-rw-r--r-- | httemplate/search/timeworked.html | 130 |
1 files changed, 130 insertions, 0 deletions
diff --git a/httemplate/search/timeworked.html b/httemplate/search/timeworked.html new file mode 100644 index 0000000..d07cd4f --- /dev/null +++ b/httemplate/search/timeworked.html @@ -0,0 +1,130 @@ +<% include( 'elements/search.html', + 'title' => 'Time Worked', + 'name' => 'time', + 'html_form' => qq!<FORM NAME="timeForm" ACTION="${p}misc/timeworked.html" METHOD="POST">!, + 'query' => $query, + 'count_query' => $count_query, + 'header' => [ '#', + 'Ticket', + 'Date', + 'Time', + '', # checkbox column + ], + 'fields' => [ sub { shift->[0] }, + sub { encode_entities(shift->[1]) }, + sub { shift->[2] }, + sub { my $seconds = shift->[3]; + (($seconds < 0) ? '-' : '') . + concise(duration($seconds)); + }, + sub { + my $row = shift; + my $seconds = $row->[3]; + my $id = $row->[4]; + qq!<INPUT NAME="transactionid$id" TYPE="checkbox" VALUE="1">!. + qq!<INPUT NAME="seconds$id" TYPE="hidden" VALUE="$seconds">!; + }, + ], + 'links' => [ + $link, + $link, + '', + '', + '', + ], + 'html_foot' => $html_foot, + ) + +%> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Time queue'); + +my @groupby = (); + +my $transactiontime = " + CASE Transactions.Type WHEN 'Set' + THEN (TO_NUMBER(NewValue,'999999')-TO_NUMBER(OldValue, '999999')) * 60 + ELSE TimeTaken*60 + END +"; + +push @groupby, qw( Transactions.Type NewValue OldValue TimeTaken ); + +my $appliedtimeclause = "COALESCE (SUM(acct_rt_transaction.seconds), 0)"; + +my $appliedtimeselect = " + COALESCE( + ( SELECT SUM(seconds) FROM acct_rt_transaction + WHERE transaction_id = Transactions.id + ), + 0 + ) +"; + +push @groupby, "Transactions.id"; + +my $wheretimeleft = "$transactiontime != $appliedtimeselect"; + +push @groupby, "Tickets.id"; +push @groupby, "Tickets.Subject"; +push @groupby, "Transactions.Created"; + +my $groupby = join(',', @groupby); + +my $where = " + WHERE ObjectType='RT::Ticket' + AND ( ( Transactions.Type='Set' AND Field='TimeWorked' ) + OR Transactions.Type='Create' + OR Transactions.Type='Comment' + OR Transactions.Type='Correspond' + ) + AND $wheretimeleft +"; + #AND $wheretimeleft + +my $str2time_sql = str2time_sql; +my $closing = str2time_sql_closing; + +my($begin, $end) = FS::UI::Web::parse_beginning_ending($cgi); +$where .= " AND $str2time_sql Transactions.Created $closing >= $begin ". + " AND $str2time_sql Transactions.Created $closing <= $end "; + +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 acct_rt_transaction + ON Transactions.id = acct_rt_transaction.transaction_id + $where + GROUP BY $groupby + ORDER BY Transactions.Created +"; + +my $count_query = "SELECT COUNT(*) FROM Transactions $where"; + +my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->[0]; } ]; + +my $html_foot = qq' + <INPUT TYPE="hidden" NAME="begin" VALUE="$begin"> + <INPUT TYPE="hidden" NAME="end" VALUE="$end"> + <BR> + <INPUT TYPE="button" VALUE="select all" onClick="setAll(true)"> + <INPUT TYPE="button" VALUE="unselect all" onClick="setAll(false)"> + <BR> + <INPUT TYPE="submit" NAME="action" VALUE="Assign to accounts"><BR> + <SCRIPT TYPE="text/javascript"> + function setAll(setTo) { + theForm = document.timeForm; + for (i=0,n=theForm.elements.length;i<n;i++) + if (theForm.elements[i].name.indexOf("transactionid") != -1) + theForm.elements[i].checked = setTo; + } + </SCRIPT> +'; + +</%init> |