From: ivan Date: Sat, 20 Nov 2010 23:00:33 +0000 (+0000) Subject: time worked summary report, RT#7573 X-Git-Tag: TORRUS_1_0_9~77 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=011fc73692cc02924be7f7d786a9a841d4cb2037 time worked summary report, RT#7573 --- diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 19cd2d8d1..46f9a0ca6 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -229,6 +229,7 @@ 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', '' ], + 'Time worked summary' => [ $fsurl.'search/report_rt_ticket.html', '' ], ; tie my %report_ticketing_statistics, 'Tie::IxHash', diff --git a/httemplate/search/report_rt_ticket.html b/httemplate/search/report_rt_ticket.html new file mode 100644 index 000000000..79a601b4b --- /dev/null +++ b/httemplate/search/report_rt_ticket.html @@ -0,0 +1,51 @@ +<% include('/elements/header.html', 'Time worked summary report criteria' ) %> + +
+ + + + <% include ( '/elements/tr-input-beginning_ending.html' ) %> + + <% include ( '/elements/tr-select-otaker.html' ) %> + + + + + + +
Account + +
+ +
+ + +
+ +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + +my $conf = new FS::Conf; + +my @pkgparts = $conf->config('support_packages'); + +my @svc_acct = (); +if ( @pkgparts ) { + @svc_acct = qsearch({ + 'table' => 'svc_acct', + 'addl_from' => ' LEFT JOIN cust_svc USING ( svcnum ) '. + ' LEFT JOIN cust_pkg USING ( pkgnum ) ', + 'extra_sql' => 'WHERE pkgpart IN ('. join(',', @pkgparts). ')', + 'order_by' => 'ORDER BY username', + }); +} + + diff --git a/httemplate/search/report_rt_transaction.html b/httemplate/search/report_rt_transaction.html index 61445bd1e..0232b8070 100644 --- a/httemplate/search/report_rt_transaction.html +++ b/httemplate/search/report_rt_transaction.html @@ -50,6 +50,7 @@ if ( @pkgparts ) { 'addl_from' => ' LEFT JOIN cust_svc USING ( svcnum ) '. ' LEFT JOIN cust_pkg USING ( pkgnum ) ', 'extra_sql' => 'WHERE pkgpart IN ('. join(',', @pkgparts). ')', + 'order_by' => 'ORDER BY username', }); } diff --git a/httemplate/search/rt_ticket.html b/httemplate/search/rt_ticket.html new file mode 100644 index 000000000..62fcce213 --- /dev/null +++ b/httemplate/search/rt_ticket.html @@ -0,0 +1,126 @@ +<% include('elements/search.html', + 'title' => 'Time worked summary', + 'name_singular' => 'ticket', + 'query' => $query, + 'count_query' => $count_query, + 'count_addl' => [ $format_seconds_sub, $format_seconds_sub, ], + 'header' => [ 'Ticket #', + 'Ticket', + 'Time', + 'Applied', + ], + 'fields' => [ 'ticketid', + sub { encode_entities(shift->get('subject')) }, + 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"; +}; + + +<%init> + +#all sorts of false laziness w/rt_transaction.html + +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 Users ON Transactions.Creator = Users.Id '; #. +# 'LEFT JOIN acct_rt_transaction '. +# ' ON Transactions.Id = acct_rt_transaction.transaction_id'; + +my $twhere = " + WHERE objecttype='RT::Ticket' + AND Transactions.ObjectId = Tickets.Id + 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 $support = ''; + +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). "'"; + $twhere .= " AND Transactions.Created >= $beginning "; +} +if ( $ending < 4294967295 ) { + $ending = "TIMESTAMP '". time2str('%Y-%m-%d %X', $ending). "'"; + $twhere .= " AND Transactions.Created <= $ending "; +} + +if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { + $twhere .= " AND Users.name = '$1' "; +} + +if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { + $twhere .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; + $support = "AND svcnum = $1"; +} + +my $transactions = "FROM Transactions $join $twhere"; + +my $where = "WHERE EXISTS ( SELECT 1 $transactions )"; + +my $transaction_time = "( SELECT SUM($transactiontime) $transactions )"; +my $support_time = "( SELECT SUM(support) FROM acct_rt_transaction LEFT JOIN Transactions ON ( transaction_id = Id ) $twhere $support )"; + +my $query = { + 'select' => join(', ', + 'Tickets.Id AS ticketid', + 'Tickets.Subject', + "$transaction_time AS transaction_time", + "$support_time AS support", + ), + 'table' => 'tickets', #Pg-ism + #'table' => 'Tickets', + '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"; + #"SELECT COUNT(*), ( SUM($transactiontime) $transactions ) FROM Tickets"; # $join $where"; + "SELECT COUNT(*), + SUM( $transaction_time ), + SUM( $support_time ) + FROM Tickets $where"; # $join $where"; + +my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ]; + + diff --git a/httemplate/search/rt_transaction.html b/httemplate/search/rt_transaction.html index fb828af84..ab5636347 100644 --- a/httemplate/search/rt_transaction.html +++ b/httemplate/search/rt_transaction.html @@ -3,7 +3,7 @@ 'name_singular' => 'transaction', 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $format_seconds_sub ],#$format_seconds_sub, ], + 'count_addl' => [ $format_seconds_sub, $format_seconds_sub, ], 'header' => [ 'Ticket #', 'Ticket', 'Date', @@ -70,6 +70,8 @@ my $where = " #AND transaction_time != 0 #AND $wheretimeleft +my $support = ''; + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); # TIMESTAMP is Pg-specific... ? if ( $beginning > 0 ) { @@ -86,13 +88,16 @@ if ( $cgi->param('otaker') && $cgi->param('otaker') =~ /^([\w\.\-]+)$/ ) { } if ( $cgi->param('ticketid') =~ /^\s*(\d+)\s*$/ ) { - $where .= " AND Tickets.ID = $1"; + $where .= " AND Tickets.Id = $1"; } if ( $cgi->param('svcnum') =~ /^\s*(\d+)\s*$/ ) { $where .= " AND EXISTS( SELECT 1 FROM acct_rt_transaction WHERE acct_rt_transaction.transaction_id = Transactions.id AND svcnum = $1 )"; + $support = "AND svcnum = $1"; } +my $support_time = "( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id $support )"; + my $query = { 'select' => join(', ', 'Transactions.*', @@ -100,7 +105,7 @@ my $query = { 'Tickets.Subject', 'Users.name AS otaker', "$transactiontime AS transaction_time", - '( SELECT SUM(support) from acct_rt_transaction where transaction_id = Transactions.id ) AS support', + "$support_time AS support", ), 'table' => 'transactions', #Pg-ism #'table' => 'Transactions', @@ -111,7 +116,10 @@ my $query = { my $count_query = #"SELECT COUNT(*), SUM($transactiontime), SUM(acct_rt_transaction.support) FROM Transactions $join $where"; - "SELECT COUNT(*), SUM($transactiontime) FROM Transactions $join $where"; + "SELECT COUNT(*), + SUM($transactiontime), + SUM($support_time) + FROM Transactions $join $where"; my $link = [ "${p}rt/Ticket/Display.html?id=", sub { shift->get('ticketid'); } ];