X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fsqlradius_usage.html;h=b810a8bf641532e3872a6407d7f15e1639798fb4;hp=dd06c10fa4086d79e46e0ebe8b05253d1dd2698e;hb=HEAD;hpb=db80d13447786bc554d40d4817a1b0d7a5b5e09f diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html index dd06c10fa..b810a8bf6 100644 --- a/httemplate/search/sqlradius_usage.html +++ b/httemplate/search/sqlradius_usage.html @@ -6,10 +6,9 @@
<% $agent->agent %>

<& sqlradius_usage.html, - export => $export, + exports => \@exports, agentnum => $agent->agentnum, nohtmlheader => 1, - usage_by_username => \%usage_by_username, download_label => 'Download this section', &>
@@ -19,38 +18,37 @@ % } else { <& elements/search.html, 'title' => $title, - 'name' => 'services', + 'name' => $combine_svcs ? 'packages' : 'services', 'query' => $sql_query, - 'count_query' => $sql_query->{'count_query'}, + 'count_query' => $count_query, 'header' => [ #FS::UI::Web::cust_header(), '#', 'Customer', 'Package', - @svc_header, - 'Upload', - 'Download', - 'Total', + 'User', + 'Upload (GB)', + 'Download (GB)', + 'Total (GB)', ], 'footer' => \@footer, 'fields' => [ #\&FS::UI::Web::cust_fields, 'display_custnum', 'name', 'pkg', - @svc_fields, - @svc_usage, + @pkg_usage, # username, upload, download, total ], + 'order_by_sql' => $order_by_sql, 'links' => [ #( map { $_ ne 'Cust. Status' ? $link_cust : '' } # FS::UI::Web::cust_header() ), $link_cust, $link_cust, - '', #package - ( map { $link_svc } @svc_header ), - '', + $link_pkg, + $link_svc, '', '', ], 'align' => #FS::UI::Web::cust_aligns() . - 'rlc' . ('l' x scalar(@svc_header)) . 'rrr' , + 'rlcrrr', 'nohtmlheader' => ($opt{'nohtmlheader'} || 0), 'download_label' => $opt{'download_label'}, &> @@ -59,24 +57,42 @@ 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 packages'); my $title = 'Data Usage Report - '; -my $agentnum; -my @include_agents; +my $agentnum = ''; +my @include_agents = (); + +my $combine_svcs = $cgi->param('combine_svcs') ? 1 : 0; -if ( $opt{'agentnum'} ) { +if ( $opt{'agentnum'} =~ /^(\d+)$/ ) { $agentnum = $opt{'agentnum'}; -} elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - $agentnum = $1; +} else { + + my @agentnums = grep /^(\d+)$/, $cgi->param('agentnum'); + + if ( ! @agentnums ) { + @include_agents = qsearch({ 'table' => 'agent', + 'hashref' => { 'disabled'=>'' }, + 'extra_sql' => ' AND '. $curuser->agentnums_sql, + }); + } elsif ( scalar(@agentnums) == 1 ) { + $agentnum = $agentnums[0]; + } else { + @include_agents = qsearch({ 'table' => 'agent', + 'hashref' => { 'disabled' => '', }, + 'extra_sql' => 'AND agentnum IN ('. + join(',',@agentnums). ') '. + ' AND '. $curuser->agentnums_sql, + }); + } + } if ( $agentnum ) { my $agent = FS::agent->by_key($agentnum); $title = $agent->agent." $title"; -} else { - @include_agents = qsearch('agent', {}); } # usage query params @@ -92,103 +108,193 @@ if ( $ending == 4294967295 ) { $title .= time2str('%h %o %Y', $ending); } -my $export; -my %usage_by_username; -if ( exists($opt{usage_by_username}) ) { - # There's no agent separation in the radacct data. So in the jumbo report - # do this procedure once, and pass the hash into all the per-agent sections. - %usage_by_username = %{ $opt{usage_by_username} }; - $export = $opt{export}; -} else { +# 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; +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; +} - $cgi->param('exportnum') =~ /^(\d+)$/ - or die "illegal export: '".$cgi->param('exportnum')."'"; - $export = FS::part_export->by_key($1) - or die "exportnum $1 not found"; - $export->exporttype =~ /sqlradius/ - or die "exportnum ".$export->exportnum." is type ".$export->exporttype. - ", not sqlradius"; - - my $usage = $export->usage_sessions( { - stoptime_start => $beginning, - stoptime_end => $ending, - summarize => 1 - } ); - # arrayref of hashrefs of - # (username, acctsessiontime, acctinputoctets, acctoutputoctets) - # (XXX needs to include 'realm' for sqlradius_withdomain) - # rearrange to be indexed by username. - - foreach (@$usage) { - my $username = $_->{'username'}; - my @row = ( - $_->{'acctinputoctets'}, - $_->{'acctoutputoctets'}, - $_->{'acctinputoctets'} + $_->{'acctoutputoctets'} - ); - $usage_by_username{$username} = \@row; +my @exports; +if ( $opt{exports} ) { + @exports = @{ $opt{exports} }; +} elsif ( $cgi->param('exportnum') ) { + foreach my $exportnum ($cgi->param('exportnum')) { + $exportnum =~ /^(\d+)$/ + or die "illegal export: '".$cgi->param('exportnum')."'"; + my $export = FS::part_export->by_key($1) + or die "exportnum $1 not found"; + $export->exporttype =~ /sqlradius/ + or die "exportnum ".$export->exportnum." is type ".$export->exporttype. + ", not sqlradius"; + push @exports, $export; } + die "exportnum required" unless @exports; +} else { + # do something sensible if no exports were selected + @exports = FS::part_export::sqlradius->all_sqlradius; } -#warn Dumper(\%usage_by_username); -my @total_usage = (0, 0, 0, 0); # session time, input, output, input + output -my @svc_usage = map { - my $i = $_; - sub { - my $username = $export->export_username(shift); - return '' if !exists($usage_by_username{$username}); - my $value = $usage_by_username{ $username }->[$i]; - $total_usage[$i] += $value; - FS::UI::bytecount::display_bytecount($value); +my %usage_param = ( + stoptime_start => $beginning, + stoptime_end => $ending, + summarize => 1 +); + +my @total_usage = ('', 0, 0, 0); # username, input, output, input + output + +# remember which exports apply to which services, so we don't inappropriately +# ask the wrong ones for usage stats +my %export_svcparts; +foreach my $export (@exports) { + my %seen; + foreach ($export->export_svc) { + $seen{ $_->svcpart } = 1; } -} (0,1,2); + $export_svcparts{ $export->exportnum } = \%seen; +} -# set up svcdb-specific stuff -my $export_username = sub { - $export->export_username(shift); # countrycode + phone, formatted MAC, etc. +# a single sub to collect data for each package, aggregated across both +# services and exports. when we add per-service breakdown, this should also +# keep the per-service data, but not needed yet +my $cust_pkg_stats_sub = sub { + my $cust_pkg = shift; + if (! $cust_pkg->get('_stats') ) { + my ($upbytes, $downbytes, $totalbytes) = (0, 0, 0); + my $display_username; + foreach my $svcnum ( split(',', $cust_pkg->get('svcnums_concat')) ) { + my $cust_svc = FS::cust_svc->by_key($svcnum); + my $svc = $cust_svc->svc_x; + foreach my $export (@exports) { + if ( $export_svcparts{ $export->exportnum }{ $cust_svc->svcpart } ) { + my $username = $export->export_username($svc); + my $usage = $export->usage_sessions({ %usage_param, 'svc' => $svc }); + # returns arrayref with one row + $upbytes += $usage->[0]->{'acctinputoctets'}; + $downbytes += $usage->[0]->{'acctoutputoctets'}; + # in combined services mode with multiple users/MAC addresses per + # package, this will just show one of them arbitrarily. + $display_username ||= $username; + } + # else this export doesn't apply so skip it + } + } + $total_usage[1] += $upbytes; + $total_usage[2] += $downbytes; + $total_usage[3] += $upbytes + $downbytes; + $cust_pkg->set('_stats', [ $display_username, + $upbytes, + $downbytes, + $upbytes + $downbytes ]); + } + return $cust_pkg->get('_stats'); }; -my %svc_header = ( - svc_acct => [ 'Username' ], - svc_broadband => [ 'MAC address', 'IP address' ], -# svc_phone => [ 'Phone' ], #not yet supported, no search method - # (not sure input/output octets is relevant) -); -my %svc_fields = ( - svc_acct => [ $export_username ], - svc_broadband => [ $export_username, 'ip_addr' ], -# svc_phone => [ $export_username ], -); +my @pkg_usage; +$pkg_usage[0] = sub { # username + return &{ $cust_pkg_stats_sub }(shift)->[0]; +}; +foreach my $i (1, 2, 3) { # numeric fields + $pkg_usage[$i] = sub { # cust_pkg arg + my $value = &{ $cust_pkg_stats_sub }(shift)->[$i]; + # for now, always show in GB, rounded to 3 digits + $value ? bytes_to_gb($value) : ''; + }; +} -# what kind of service we're operating on -my $svcdb = FS::part_export::export_info()->{$export->exporttype}->{'svc'}; -my $class = "FS::$svcdb"; -my @svc_header = @{ $svc_header{$svcdb} }; -my @svc_fields = @{ $svc_fields{$svcdb} }; +my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ]; -# svc_x search params +# cust_pkg search params my %search_hash = ( 'agentnum' => $agentnum ); +if ($cust_main) { + $search_hash{'custnum'} = $cust_main->custnum; +} + +# construct a subquery for services/packages with relevant exports -my $sql_query = $class->search(\%search_hash); -$sql_query->{'select'} .= ', part_pkg.pkg'; -$sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)'; +my $group_by = ' GROUP BY pkgnum'; +if ( !$combine_svcs ) { + $group_by .= ', svcnum'; +} -my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ]; +my $exportnums = join(',', map { $_->get('exportnum') } @exports); +my $svcnums_table = 'SELECT pkgnum, ' . FS::Record::group_concat_sql('DISTINCT svcnum', ',') . ' AS svcnums_concat +FROM cust_svc + JOIN part_svc USING (svcpart) + JOIN export_svc USING (svcpart) +WHERE exportnum IN(' . $exportnums . ')' . $group_by; -my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ]; +my $sql_query = FS::cust_pkg->search( \%search_hash ); +# also get the svcnum-list column +$sql_query->{'select'} .= ', svcnums_concat' . +# and a workaround for the implicit DISTINCTing that happens in qsearch + ', NULL AS pkgnum, pkgnum AS real_pkgnum'; +$sql_query->{'addl_from'} .= " JOIN ($svcnums_table) AS svcnums +USING (pkgnum)"; +$sql_query->{'order_by'} = ' ORDER BY cust_pkg.pkgnum, svcnums_concat'; # for stability -# columns between the customer name and the usage fields -my $skip_cols = 1 + scalar(@svc_header); +my $count_query = "SELECT COUNT(*) FROM cust_pkg ". + $sql_query->{addl_from} . + $sql_query->{extra_sql}; +my $num_rows = FS::Record->scalar_sql($count_query); +my $itemname = $combine_svcs ? 'package' : 'service'; my @footer = ( '', - FS::Record->scalar_sql($sql_query->{count_query}) . ' services', - (' ') x $skip_cols, - map { + emt("[quant,_1,$itemname]", $num_rows), + '', #pkg label + '', #username + map { + my $i = $_; sub { # defer this until the rows have been processed - FS::UI::bytecount::display_bytecount($total_usage[$_]) + bytes_to_gb($total_usage[$i]) } - } (0,1,2) + } (1,2,3) ); +sub bytes_to_gb { + $_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : ''; +} + +my $conf = new FS::Conf; +my $order_by_sql = { + 'name' => "CASE WHEN cust_main.company IS NOT NULL + AND cust_main.company != '' + THEN CONCAT(cust_main.company,' (',cust_main.last,', ',cust_main.first,')') + ELSE CONCAT(cust_main.last,', ',cust_main.first) + END", + 'display_custnum' => $conf->exists('cust_main-default_agent_custid') + ? "CASE WHEN cust_main.agent_custid IS NOT NULL + AND cust_main.agent_custid != '' + AND cust_main.agent_custid ". regexp_sql. " '^[0-9]+\$' + THEN CAST(cust_main.agent_custid AS BIGINT) + ELSE cust_main.custnum + END" + : "custnum", +}; + +my $link_pkg = sub { + my $self = shift; + [ "${p}view/cust_pkg.cgi?", 'real_pkgnum' ]; +}; + +my $link_svc = sub { + my $self = shift; + if ($self->svcnums_concat =~ /^(\d+)$/) { + return [ $p.'view/cust_svc.cgi?' . $1 ]; + } else { + return ''; + } +};