diff options
Diffstat (limited to 'httemplate/search/rt_transaction.html')
-rw-r--r-- | httemplate/search/rt_transaction.html | 110 |
1 files changed, 110 insertions, 0 deletions
diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html new file mode 100644 index 0000000..8dda4ba --- /dev/null +++ b/httemplate/search/rt_transaction.html @@ -0,0 +1,110 @@ +<% include('elements/search.html', + 'title' => 'Time worked', + 'name_singular' => 'transaction', + 'query' => $query, + 'count_query' => $count_query, + 'count_addl' => [ $format_seconds_sub, $format_seconds_sub, ], + 'header' => [ 'Ticket #', + 'Ticket', + 'Date', + 'Time', + 'Applied', + ], + 'fields' => [ 'ticketid', + sub { encode_entities(shift->get('subject')) }, + 'created', + sub { my $seconds = shift->get('transaction_time'); + &{ $format_seconds_sub }( $seconds ); + }, + sub { my $seconds = shift->get('support'); + &{ $format_seconds_sub }( $seconds ); + }, + ], + 'links' => [ + $link, + $link, + '', + '', + '', + ], + ) +%> +<%once> + +my $format_seconds_sub = sub { + my $seconds = shift; + #(($seconds < 0) ? '-' : '') . concise(duration($seconds)); + (($seconds < 0) ? '-' : '' ). int(abs($seconds)/3600)."h".sprintf("%02d",(abs( +$seconds)%3600)/60)."m"; +}; + +</%once> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + +#some amount of false laziness w/timeworked.html... + +my $transactiontime = " + CASE transactions.type when 'Set' + THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60 + ELSE timetaken*60 + END +"; + +my $join = 'JOIN Tickets ON Transactions.ObjectId = Tickets.Id '. + 'JOIN Users ON Transactions.Creator = Users.Id '. + 'LEFT JOIN acct_rt_transaction '. + ' ON Transactions.Id = acct_rt_transaction.transaction_id'; +my $where = " + WHERE objecttype='RT::Ticket' + AND ( ( Transactions.Type = 'Set' + AND Transactions.Field = 'TimeWorked' + AND Transactions.NewValue != Transactions.OldValue ) + OR ( ( Transactions.Type='Create' OR Transactions.Type='Comment' OR Transactions.Type='Correspond' ) + AND Transactions.TimeTaken > 0 + ) + ) +"; +#AND transaction_time != 0 +#AND $wheretimeleft + +my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); +# TIMESTAMP is Pg-specific... ? +if ( $beginning > 0 ) { + $beginning = "TIMESTAMP '". time2str('%Y-%m-%d %X', $beginning). "'"; + $where .= " AND Transactions.Created >= $beginning "; +} +if ( $ending < 4294967295 ) { + $ending = "TIMESTAMP '". time2str('%Y-%m-%d %X', $ending). "'"; + $where .= " AND Transactions.Created <= $ending "; +} + +if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { + $where .= " AND Users.name = '$1' "; +} + +if ( $cgi->param('ticketid') =~ /^\s*(\d+)\s*$/ ) { + $where .= " AND Tickets.ID = $1"; +} + +if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { + $where .= " AND acct_rt_transaction.svcnum = $1"; +} + +my $query = { + 'select' => "Transactions.*, Tickets.Id AS ticketid, Tickets.Subject, Users.name as otaker, $transactiontime AS transaction_time, acct_rt_transaction.support", + #'table' => 'Transactions', + 'table' => 'transactions', + 'addl_from' => $join, + 'extra_sql' => $where, + 'order by' => 'ORDER BY Created', +}; + +my $count_query = + "SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where"; + +my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ]; + +</%init> |