From 948b8acdd4b9b3864342062d0c397a11f57c5700 Mon Sep 17 00:00:00 2001 From: ivan Date: Tue, 4 Dec 2007 20:51:51 +0000 Subject: [PATCH] add some time-worked reporting --- httemplate/elements/menu.html | 1 + httemplate/elements/select-otaker.html | 27 ++++++++ httemplate/elements/tr-select-otaker.html | 10 +++ httemplate/search/elements/search.html | 12 +++- httemplate/search/report_cust_credit.html | 18 ++---- httemplate/search/report_rt_transaction.html | 22 +++++++ httemplate/search/rt_transaction.html | 96 ++++++++++++++++++++++++++++ 7 files changed, 171 insertions(+), 15 deletions(-) create mode 100644 httemplate/elements/select-otaker.html create mode 100644 httemplate/elements/tr-select-otaker.html create mode 100644 httemplate/search/report_rt_transaction.html create mode 100644 httemplate/search/rt_transaction.html diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index da8f58054..b40b01986 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -162,6 +162,7 @@ $report_packages{'Advanced package reports'} = [ $fsurl.'search/report_cust_pkg tie my %report_rating, 'Tie::IxHash', 'RADIUS sessions' => [ $fsurl.'search/sqlradius.html', '' ], 'Call Detail Records (CDRs)' => [ $fsurl.'search/report_cdr.html', '' ], + 'Time worked' => [ $fsurl.'search/report_rt_transaction.html', '' ], ; tie my %report_bill_event, 'Tie::IxHash', diff --git a/httemplate/elements/select-otaker.html b/httemplate/elements/select-otaker.html new file mode 100644 index 000000000..2a689f39d --- /dev/null +++ b/httemplate/elements/select-otaker.html @@ -0,0 +1,27 @@ + + +<%init> + +my %opt = @_; + +unless ( $opt{'otakers'} ) { + + my $sth = dbh->prepare("SELECT username FROM access_user". + " WHERE disabled = '' or disabled IS NULL") + or die dbh->errstr; + $sth->execute or die $sth->errstr; + $opt{'otakers'} = [ map { $_->[0] } @{$sth->fetchall_arrayref} ]; + +} + + diff --git a/httemplate/elements/tr-select-otaker.html b/httemplate/elements/tr-select-otaker.html new file mode 100644 index 000000000..edf62dceb --- /dev/null +++ b/httemplate/elements/tr-select-otaker.html @@ -0,0 +1,10 @@ + + <% $opt{'label'} || 'Employee: ' %> + <% include('select-otaker.html', %opt) %> + + +<%init> + +my %opt = @_; + + diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html index 7b1a515df..5649c8579 100644 --- a/httemplate/search/elements/search.html +++ b/httemplate/search/elements/search.html @@ -45,7 +45,7 @@ Example: #required unless 'query' is an SQL query string (shouldn't be...) 'count_query' => 'SELECT COUNT(*) FROM tablename', - 'count_addl' => [], #additional count fields listref of sprintf strings + 'count_addl' => [], #additional count fields listref of sprintf strings or coderefs # [ $money_char.'%.2f total paid', ], #listref of column labels, @@ -313,8 +313,14 @@ Example:
% if ( $opt{'count_addl'} ) { -% my $n=0; foreach my $count ( @{$opt{'count_addl'}} ) { - <% sprintf( $count, $count_arrayref->[++$n] ) %>
+% my $n=0; +% foreach my $count ( @{$opt{'count_addl'}} ) { +% my $data = $count_arrayref->[++$n]; +% if ( ref($count) ) { + <% &{ $count }( $data ) %> +% } else { + <% sprintf( $count, $data ) %>
+% } % } % } diff --git a/httemplate/search/report_cust_credit.html b/httemplate/search/report_cust_credit.html index 0490f4323..be02e9fbd 100644 --- a/httemplate/search/report_cust_credit.html +++ b/httemplate/search/report_cust_credit.html @@ -4,18 +4,12 @@ - - - - - + + <% include( '/elements/tr-select-otaker.html', + 'label' => 'Credits by employee: ', + 'otakers' => \@otakers, + ) + %> <% include( '/elements/tr-select-agent.html', 'curr_value' => scalar( $cgi->param('agentnum') ), diff --git a/httemplate/search/report_rt_transaction.html b/httemplate/search/report_rt_transaction.html new file mode 100644 index 000000000..89629e843 --- /dev/null +++ b/httemplate/search/report_rt_transaction.html @@ -0,0 +1,22 @@ +<% include('/elements/header.html', 'Time worked report criteria' ) %> + + + +
Credits by employee: - -
+ + <% include ( '/elements/tr-input-beginning_ending.html' ) %> + + <% include ( '/elements/tr-select-otaker.html' ) %> + +
+ +
+ + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + + diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html new file mode 100644 index 000000000..31ad10d1a --- /dev/null +++ b/httemplate/search/rt_transaction.html @@ -0,0 +1,96 @@ +<% include('elements/search.html', + 'title' => 'Time worked', + 'name_singular' => 'transaction', + 'query' => $query, + 'count_query' => $count_query, + 'count_addl' => [ $format_seconds_sub, ], + 'header' => [ 'Ticket #', + 'Ticket', + 'Date', + 'Time', + ], + 'fields' => [ 'ticketid', + sub { encode_entities(shift->get('subject')) }, + 'created', + sub { my $seconds = shift->get('transaction_time'); + &{ $format_seconds_sub }( $seconds ); + }, + ], + 'links' => [ + $link, + $link, + '', + '', + ], + ) +%> +<%once> + +my $format_seconds_sub = sub { + my $seconds = shift; + (($seconds < 0) ? '-' : '') . concise(duration($seconds)); +}; + + +<%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 '; + +my $where = " + WHERE objecttype='RT::Ticket' + AND ( ( Transactions.Type = 'Set' + AND Transactions.Field = 'TimeWorked' + AND Transactions.NewValue != Transactions.OldValue ) + 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' "; +} + +my $query = { + 'select' => "Transactions.*, Tickets.Id AS ticketid, Tickets.Subject, Users.name as otaker, $transactiontime AS transaction_time", + #'table' => 'Transactions', + 'table' => 'transactions', + 'addl_from' => $join. + 'LEFT JOIN acct_rt_transaction '. + ' ON Transactions.Id = acct_rt_transaction.transaction_id', + 'extra_sql' => $where, + 'order by' => 'ORDER BY Created', +}; + +my $count_query = + "SELECT COUNT(*), SUM($transactiontime) FROM Transactions $join $where"; + +my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('id'); } ]; + + -- 2.11.0