From: Mark Wells Date: Tue, 26 Jul 2016 18:49:18 +0000 (-0700) Subject: per-customer RADIUS data usage report, #42310 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=4e2e2afe2b9d54e971f433350f215df156e64f60 per-customer RADIUS data usage report, #42310 --- diff --git a/FS/FS/svc_Common.pm b/FS/FS/svc_Common.pm index 1dd9ffb63..f2456a56f 100644 --- a/FS/FS/svc_Common.pm +++ b/FS/FS/svc_Common.pm @@ -1481,8 +1481,12 @@ sub search { } #svcnum - if ( $params->{'svcnum'} =~ /^(\d+)$/ ) { - push @where, "svcnum = $1"; + if ( $params->{'svcnum'} ) { + my @svcnum = ref( $params->{'svcnum'} ) + ? @{ $params->{'svcnum'} } + : $params->{'svcnum'}; + @svcnum = grep /^\d+$/, @svcnum; + push @where, 'svcnum IN ('. join(',', @svcnum) . ')' if @svcnum; } # svcpart diff --git a/httemplate/search/report_sqlradius_usage-custnum.html b/httemplate/search/report_sqlradius_usage-custnum.html new file mode 100644 index 000000000..a71012dd4 --- /dev/null +++ b/httemplate/search/report_sqlradius_usage-custnum.html @@ -0,0 +1,71 @@ +<& /elements/header-popup.html, mt($title) &> + +
+ +<& /elements/hidden.html, + 'field' => 'custnum', + 'value' => $custnum, +&> + + +% if ( scalar(@exports) == 1 ) { + +% } else { +<& /elements/tr-select-table.html, + 'label' => 'Export', # kind of non-indicative... + 'table' => 'part_export', + 'name_col' => 'label', + 'value_col' => 'exportnum', + 'records' => \@exports, + 'disable_empty' => 1, +&> +% } +<& /elements/tr-input-beginning_ending.html &> + +
+<& /elements/hidden.html, + 'field' => 'exportnum', + 'value' => $exports[0]->exportnum, +&> +
+ +
+ + +
+ +<& /elements/footer.html &> +<%init> + +my $curuser = $FS::CurrentUser::CurrentUser; +die "access denied" + unless $curuser->access_right('Usage: RADIUS sessions'); + # yes? + +my $title = 'Data Usage Report'; +my $custnum; +if ($cgi->keywords) { + ($custnum) = $cgi->keywords; +} else { + $custnum = $cgi->param('custnum'); +} +$custnum =~ /^(\d+)$/ + or die "illegal custnum $custnum"; +my $cust_main = qsearchs( { + 'table' => 'cust_main', + 'hashref' => { 'custnum' => $custnum }, + 'extra_sql' => ' AND '. $curuser->agentnums_sql, +}); +# get all exports that apply to this customer's services--should be fast, as +# everything here is indexed +my @exports = qsearch({ + 'table' => 'part_export', + 'select' => 'DISTINCT part_export.*', + 'addl_from' => ' JOIN export_svc USING (exportnum) + JOIN cust_svc USING (svcpart) + JOIN cust_pkg USING (pkgnum) ', + 'extra_sql' => ' WHERE cust_pkg.custnum = '.$custnum, +}); +@exports = grep { $_->can('usage_sessions') } @exports; + + diff --git a/httemplate/search/report_sqlradius_usage.html b/httemplate/search/report_sqlradius_usage.html index e818fb57d..89b60847b 100644 --- a/httemplate/search/report_sqlradius_usage.html +++ b/httemplate/search/report_sqlradius_usage.html @@ -1,3 +1,4 @@ +%# some overlap with report_sqlradius_usage_custnum.html <& /elements/header.html, mt($title) &>
diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html index 29ef4c0e8..b5551ade6 100644 --- a/httemplate/search/sqlradius_usage.html +++ b/httemplate/search/sqlradius_usage.html @@ -59,8 +59,8 @@ my %opt = @_; -die "access denied" unless - $FS::CurrentUser::CurrentUser->access_right('List services'); +my $curuser = $FS::CurrentUser::CurrentUser; +die "access denied" unless $curuser->access_right('List services'); my $title = 'Data Usage Report - '; my $agentnum; @@ -92,6 +92,40 @@ if ( $ending == 4294967295 ) { $title .= time2str('%h %o %Y', $ending); } +# can also show a specific customer / service. the main query will handle +# agent restrictions, but we need a list of the services to ask the export +# for usage data. +my ($cust_main, @svc_x); +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + $cust_main = qsearchs( { + 'table' => 'cust_main', + 'hashref' => { 'custnum' => $1 }, + 'extra_sql' => ' AND '. $curuser->agentnums_sql, + }); + die "Customer not found!" unless $cust_main; + # then only report on this agent + $agentnum = $cust_main->agentnum; + @include_agents = (); + # and announce that we're doing it + $title .= ' - ' . $cust_main->name_short; + + # yes, we'll query the database once for each service the customer has, + # even non-radacct'd services. probably less bad than a single query that + # pulls records for every service for every customer. + foreach my $cust_pkg ($cust_main->all_pkgs) { + foreach my $cust_svc ($cust_pkg->cust_svc) { + push @svc_x, $cust_svc->svc_x; + } + } +} +foreach ($cgi->param('svcnum')) { + if (/^(\d+)$/) { + my $cust_svc = FS::cust_svc->by_key($1) + or die "service #$1 not found."; # or continue? + push @svc_x, $cust_svc->svc_x; + } +} + my $export; my %usage_by_username; if ( exists($opt{usage_by_username}) ) { @@ -109,16 +143,28 @@ if ( exists($opt{usage_by_username}) ) { or die "exportnum ".$export->exportnum." is type ".$export->exporttype. ", not sqlradius"; - my $usage = $export->usage_sessions( { + my %usage_param = ( stoptime_start => $beginning, stoptime_end => $ending, summarize => 1 - } ); - # arrayref of hashrefs of + ); + # usage_sessions() returns an arrayref of hashrefs of # (username, acctsessiontime, acctinputoctets, acctoutputoctets) # (XXX needs to include 'realm' for sqlradius_withdomain) - # rearrange to be indexed by username. + my $usage; + if ( @svc_x ) { + # then query once per service + $usage = []; + foreach my $svc ( @svc_x ) { + $usage_param{'svc'} = $svc; + push @$usage, @{ $export->usage_sessions(\%usage_param) }; + } + } else { + # one query, get everyone's data + my $usage = $export->usage_sessions(\%usage_param); + } + # rearrange to be indexed by username. foreach (@$usage) { my $username = $_->{'username'}; my @row = ( @@ -171,10 +217,22 @@ my @svc_fields = @{ $svc_fields{$svcdb} }; my %search_hash = ( 'agentnum' => $agentnum, 'exportnum' => $export->exportnum ); +if ($cust_main) { + $search_hash{'custnum'} = $cust_main->custnum; +} +if (@svc_x) { + $search_hash{'svcnum'} = [ map { $_->get('svcnum') } @svc_x ]; +} + my $sql_query = $class->search(\%search_hash); $sql_query->{'select'} .= ', part_pkg.pkg'; $sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)'; +if ( @svc_x ) { + my $svcnums = join(',', map { $_->get('svcnum') } @svc_x); + $sql_query->{'extra_sql'} .= ' AND svcnum IN('.$svcnums.')'; +} + my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ]; my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ]; @@ -182,9 +240,10 @@ my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ]; # columns between the customer name and the usage fields my $skip_cols = 1 + scalar(@svc_header); +my $num_rows = FS::Record->scalar_sql($sql_query->{count_query}); my @footer = ( '', - FS::Record->scalar_sql($sql_query->{count_query}) . ' services', + emt('[quant,_1,service]', $num_rows), ('') x $skip_cols, map { my $i = $_; @@ -198,4 +257,7 @@ sub bytes_to_gb { $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : ''; } +warn Dumper \%usage_by_username; + +