% if ( @include_agents ) { % # jumbo report <& /elements/header.html, $title &> % foreach my $agent ( @include_agents ) { % $cgi->param('agentnum', $agent->agentnum); #for download links
<% $agent->agent %>

<& sqlradius_usage.html, exports => \@exports, agentnum => $agent->agentnum, nohtmlheader => 1, download_label => 'Download this section', &>


% } <& /elements/footer.html &> % } else { <& elements/search.html, 'title' => $title, 'name' => $combine_svcs ? 'packages' : 'services', 'query' => $sql_query, 'count_query' => $count_query, 'header' => [ #FS::UI::Web::cust_header(), '#', 'Customer', 'Package', 'User', 'Upload (GB)', 'Download (GB)', 'Total (GB)', ], 'footer' => \@footer, 'fields' => [ #\&FS::UI::Web::cust_fields, 'display_custnum', 'name', 'pkg', @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, $link_pkg, $link_svc, '', '', ], 'align' => #FS::UI::Web::cust_aligns() . 'rlcrrr', 'nohtmlheader' => ($opt{'nohtmlheader'} || 0), 'download_label' => $opt{'download_label'}, &> % } <%init> my %opt = @_; 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 $combine_svcs = $cgi->param('combine_svcs') ? 1 : 0; if ( $opt{'agentnum'} =~ /^(\d+)$/ ) { $agentnum = $opt{'agentnum'}; } 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"; } # usage query params my( $beginning, $ending ) = FS::UI::Web::parse_beginning_ending($cgi); if ( $beginning ) { $title .= time2str('%h %o %Y ', $beginning); } $title .= 'through '; if ( $ending == 4294967295 ) { $title .= 'now'; } else { $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; 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; } 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; } 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; } $export_svcparts{ $export->exportnum } = \%seen; } # 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 @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) : ''; }; } my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ]; # 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 $group_by = ' GROUP BY pkgnum'; if ( !$combine_svcs ) { $group_by .= ', 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 $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 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 = ( '', emt("[quant,_1,$itemname]", $num_rows), '', #pkg label '', #username map { my $i = $_; sub { # defer this until the rows have been processed bytes_to_gb($total_usage[$i]) } } (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 ''; } };