From 9531335fb25595f8883967c21f9692d4331f54ce Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Fri, 26 Jun 2015 12:26:31 -0700 Subject: [PATCH] support and unclassified time per customer for a date range --- httemplate/elements/menu.html | 3 +- httemplate/search/report_rt_cust.html | 40 ++++++++ httemplate/search/rt_cust.html | 174 ++++++++++++++++++++++++++++++++++ 3 files changed, 216 insertions(+), 1 deletion(-) create mode 100644 httemplate/search/report_rt_cust.html create mode 100644 httemplate/search/rt_cust.html diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 4d0d97958..a01530e2f 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -291,7 +291,8 @@ $report_rating{'Unrateable CDRs'} = [ $fsurl.'search/cdr.html?freesidestatus=fai if $curuser->access_right("Usage: Unrateable CDRs"); if ( $curuser->access_right("Usage: Time worked") ) { $report_rating{'Time worked'} = [ $fsurl.'search/report_rt_transaction.html', '' ]; - $report_rating{'Time worked summary'} = [ $fsurl.'search/report_rt_ticket.html', '' ]; + $report_rating{'Time worked summary per ticket'} = [ $fsurl.'search/report_rt_ticket.html', '' ]; + $report_rating{'Time worked summary per customer'} = [ $fsurl.'search/report_rt_cust.html', '' ]; } tie my %report_ticketing_statistics, 'Tie::IxHash', diff --git a/httemplate/search/report_rt_cust.html b/httemplate/search/report_rt_cust.html new file mode 100644 index 000000000..07d497fc5 --- /dev/null +++ b/httemplate/search/report_rt_cust.html @@ -0,0 +1,40 @@ +<& /elements/header.html, 'Time worked per-customer summary' &> + +
+ + + + <& /elements/tr-select-cust_main-status.html, + 'label' => emt('Status'), + &> + + <& /elements/tr-input-beginning_ending.html &> + + + +
+ +
+ + +
+ +<& /elements/footer.html &> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data'); + +my $conf = new FS::Conf; + + diff --git a/httemplate/search/rt_cust.html b/httemplate/search/rt_cust.html new file mode 100644 index 000000000..7c31e976b --- /dev/null +++ b/httemplate/search/rt_cust.html @@ -0,0 +1,174 @@ +<& elements/search.html, + 'title' => 'Time worked per-customer summary', + 'name_singular' => 'customer', + 'query' => $sql_query, + 'count_query' => $count_query, + 'header' => [ FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ), + @extra_headers, + 'Support time', + #'Development time', + 'Unclassified time', + ], + 'fields' => [ + \&FS::UI::Web::cust_fields, + @extra_fields, + $support_time_sub, + $unclass_time_sub, + ], + 'color' => [ FS::UI::Web::cust_colors(), + map '', @extra_fields + ], + 'style' => [ FS::UI::Web::cust_styles(), + map '', @extra_fields + ], + 'align' => [ FS::UI::Web::cust_aligns(), + map '', @extra_fields + ], + 'links' => [ ( map { $_ ne 'Cust. Status' ? $link : '' } + FS::UI::Web::cust_header( + $cgi->param('cust_fields') + ) + ), + map '', @extra_fields + ], + +&> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List rating data') +; + +#false laziness w/cust_main.html (we're really only filtering on status for now) + +my %search_hash = (); + +#$search_hash{'query'} = $cgi->keywords; + +#scalars +my @scalars = qw ( + agentnum salesnum status address city county state zip country + invoice_terms + no_censustract with_geocode with_email tax no_tax POST no_POST + custbatch usernum + cancelled_pkgs + cust_fields flattened_pkgs + all_tags + all_pkg_classnums + any_pkg_status +); + +for my $param ( @scalars ) { + $search_hash{$param} = scalar( $cgi->param($param) ) + if length($cgi->param($param)); +} + +#lists +for my $param (qw( classnum refnum tagnum pkg_classnum )) { + $search_hash{$param} = [ $cgi->param($param) ]; +} + +### +# etc +### + +my $sql_query = FS::cust_main::Search->search(\%search_hash); +my $count_query = delete($sql_query->{'count_query'}); +my @extra_headers = @{ delete($sql_query->{'extra_headers'}) }; +my @extra_fields = @{ delete($sql_query->{'extra_fields'}) }; + +my $link = [ "${p}view/cust_main.cgi?", 'custnum' ]; + +#eofalse (cust_main.html) + +#false laziness / cribbed from search/rt_ticket.html + +my $twhere = " + WHERE Transactions.ObjectType = 'RT::Ticket' +"; #AND Transactions.ObjectId = Tickets.Id + +my $transaction_time = " +CASE transactions.type when 'Set' + THEN (to_number(newvalue,'999999')-to_number(oldvalue, '999999')) * 60 + ELSE timetaken*60 +END"; + +$twhere .= " + AND ( ( Transactions.Type = 'Set' + AND Transactions.Field = 'TimeWorked' + AND Transactions.NewValue != Transactions.OldValue ) + OR ( Transactions.Type IN ( 'Create', 'Comment', 'Correspond', 'Touch' ) + AND Transactions.TimeTaken > 0 + ) + )"; + +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 "; +} + +my $transactions = "FROM Transactions $twhere"; + +#eofalse (rt_ticket.html) + +my $support_time_sub = sub { + my $cust_main = shift; + my $sec = 0; + foreach my $ticket ($cust_main->tickets) { + + my $TimeType = FS::Record->scalar_sql( + "SELECT Content FROM ObjectCustomFieldValues + JOIN CustomFields + ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) + WHERE CustomFields.Name = 'TimeType' + AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket' + AND ObjectCustomFieldValues.Disabled = 0 + AND ObjectId = ". $ticket->{id} + ); + next unless $TimeType eq 'support'; + + $sec += FS::Record->scalar_sql( + "SELECT SUM($transaction_time) $transactions ". + " AND Transactions.ObjectId = ". $ticket->{id} + ); + } + + (($sec < 0) ? '-' : '' ). int(abs($sec)/3600)."h".sprintf("%02d",(abs($sec)%3600)/60)."m"; + +}; + +my $unclass_time_sub = sub { + my $cust_main = shift; + my $sec = 0; + foreach my $ticket ($cust_main->tickets) { + + my $TimeType = FS::Record->scalar_sql( + "SELECT Content FROM ObjectCustomFieldValues + JOIN CustomFields + ON (ObjectCustomFieldValues.CustomField = CustomFields.Id) + WHERE CustomFields.Name = 'TimeType' + AND ObjectCustomFieldValues.ObjectType = 'RT::Ticket' + AND ObjectCustomFieldValues.Disabled = 0 + AND ObjectId = ". $ticket->{id} + ); + next unless $TimeType eq ''; + + $sec += FS::Record->scalar_sql( + "SELECT SUM($transaction_time) $transactions ". + " AND Transactions.ObjectId = ". $ticket->{id} + ); + } + + (($sec < 0) ? '-' : '' ). int(abs($sec)/3600)."h".sprintf("%02d",(abs($sec)%3600)/60)."m"; + +}; + + -- 2.11.0