X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;ds=sidebyside;f=httemplate%2Fsearch%2Fsqlradius_usage.html;h=b810a8bf641532e3872a6407d7f15e1639798fb4;hb=8572f2347f879d414a60e8e0dc111fa927d66c14;hp=dd06c10fa4086d79e46e0ebe8b05253d1dd2698e;hpb=db80d13447786bc554d40d4817a1b0d7a5b5e09f;p=freeside.git
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 '';
+ }
+};
%init>