From db80d13447786bc554d40d4817a1b0d7a5b5e09f Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Fri, 10 Aug 2012 17:09:46 -0700 Subject: [PATCH] rest of sqlradius data usage report --- httemplate/search/report_sqlradius_usage.html | 40 ++++++ httemplate/search/sqlradius_usage.html | 194 ++++++++++++++++++++++++++ 2 files changed, 234 insertions(+) create mode 100644 httemplate/search/report_sqlradius_usage.html create mode 100644 httemplate/search/sqlradius_usage.html diff --git a/httemplate/search/report_sqlradius_usage.html b/httemplate/search/report_sqlradius_usage.html new file mode 100644 index 000000000..01215e834 --- /dev/null +++ b/httemplate/search/report_sqlradius_usage.html @@ -0,0 +1,40 @@ +<& /elements/header.html, mt($title) &> + +
+ + 'all', +&> + +% my @exporttypes = map { "'$_'" } qw(sqlradius broadband_sqlradius); +<& /elements/tr-select-table.html, + 'label' => 'Export', + 'table' => 'part_export', + 'name_col' => 'label', + 'hashref' => {}, + 'extra_sql' => ' WHERE exporttype IN('.join(',', @exporttypes).')', + 'disable_empty' => 1, + 'order_by' => 'ORDER BY exportnum', +&> + +<& /elements/tr-input-beginning_ending.html &> + +
+ +
+ + +
+ +<& /elements/footer.html &> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Usage: RADIUS sessions'); + # yes? + +my $title = 'Data Usage Report'; + + diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html new file mode 100644 index 000000000..dd06c10fa --- /dev/null +++ b/httemplate/search/sqlradius_usage.html @@ -0,0 +1,194 @@ +% 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, + export => $export, + agentnum => $agent->agentnum, + nohtmlheader => 1, + usage_by_username => \%usage_by_username, + download_label => 'Download this section', + &> +
+

+% } +<& /elements/footer.html &> +% } else { +<& elements/search.html, + 'title' => $title, + 'name' => 'services', + 'query' => $sql_query, + 'count_query' => $sql_query->{'count_query'}, + 'header' => [ #FS::UI::Web::cust_header(), + '#', + 'Customer', + 'Package', + @svc_header, + 'Upload', + 'Download', + 'Total', + ], + 'footer' => \@footer, + 'fields' => [ #\&FS::UI::Web::cust_fields, + 'display_custnum', + 'name', + 'pkg', + @svc_fields, + @svc_usage, + ], + 'links' => [ #( map { $_ ne 'Cust. Status' ? $link_cust : '' } + # FS::UI::Web::cust_header() ), + $link_cust, + $link_cust, + '', #package + ( map { $link_svc } @svc_header ), + '', + '', + '', + ], + 'align' => #FS::UI::Web::cust_aligns() . + 'rlc' . ('l' x scalar(@svc_header)) . 'rrr' , + 'nohtmlheader' => ($opt{'nohtmlheader'} || 0), + 'download_label' => $opt{'download_label'}, +&> +% } +<%init> + +my %opt = @_; + +die "access denied" unless + $FS::CurrentUser::CurrentUser->access_right('List services'); + +my $title = 'Data Usage Report - '; +my $agentnum; +my @include_agents; + +if ( $opt{'agentnum'} ) { + $agentnum = $opt{'agentnum'}; +} elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { + $agentnum = $1; +} + +if ( $agentnum ) { + my $agent = FS::agent->by_key($agentnum); + $title = $agent->agent." $title"; +} else { + @include_agents = qsearch('agent', {}); +} + +# 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); +} + +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 { + + $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; + } +} + +#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); + } +} (0,1,2); + +# set up svcdb-specific stuff +my $export_username = sub { + $export->export_username(shift); # countrycode + phone, formatted MAC, etc. +}; + +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 ], +); + +# 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} }; + +# svc_x search params +my %search_hash = ( 'agentnum' => $agentnum ); + +my $sql_query = $class->search(\%search_hash); +$sql_query->{'select'} .= ', part_pkg.pkg'; +$sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)'; + +my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ]; + +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 @footer = ( + '', + FS::Record->scalar_sql($sql_query->{count_query}) . ' services', + (' ') x $skip_cols, + map { + sub { # defer this until the rows have been processed + FS::UI::bytecount::display_bytecount($total_usage[$_]) + } + } (0,1,2) +); + + -- 2.11.0