% 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 '';
}
};
%init>