<% include( 'elements/search.html', 'title' => 'Time Worked', 'name' => 'time', 'html_form' => qq!

!, '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!!. qq!!; }, ], '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'


';