% 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 (GB)', 'Download (GB)', 'Total (GB)', ], 'footer' => \@footer, 'fields' => [ #\&FS::UI::Web::cust_fields, 'display_custnum', 'name', 'pkg', @svc_fields, @svc_usage, ], '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 ), '', '', '', ], '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 = @_; my $curuser = $FS::CurrentUser::CurrentUser; die "access denied" unless $curuser->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); } # 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, @svc_x); 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; # yes, we'll query the database once for each service the customer has, # even non-radacct'd services. probably less bad than a single query that # pulls records for every service for every customer. foreach my $cust_pkg ($cust_main->all_pkgs) { foreach my $cust_svc ($cust_pkg->cust_svc) { push @svc_x, $cust_svc->svc_x; } } } foreach ($cgi->param('svcnum')) { if (/^(\d+)$/) { my $cust_svc = FS::cust_svc->by_key($1) or die "service #$1 not found."; # or continue? push @svc_x, $cust_svc->svc_x; } } 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_param = ( stoptime_start => $beginning, stoptime_end => $ending, summarize => 1 ); # usage_sessions() returns an arrayref of hashrefs of # (username, acctsessiontime, acctinputoctets, acctoutputoctets) # (XXX needs to include 'realm' for sqlradius_withdomain) my $usage; if ( @svc_x ) { # then query once per service $usage = []; foreach my $svc ( @svc_x ) { $usage_param{'svc'} = $svc; push @$usage, @{ $export->usage_sessions(\%usage_param) }; } } else { # one query, get everyone's data my $usage = $export->usage_sessions(\%usage_param); } # 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; # for now, always show in GB, rounded to 3 digits bytes_to_gb($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, 'exportnum' => $export->exportnum ); if ($cust_main) { $search_hash{'custnum'} = $cust_main->custnum; } if (@svc_x) { $search_hash{'svcnum'} = [ map { $_->get('svcnum') } @svc_x ]; } my $sql_query = $class->search(\%search_hash); $sql_query->{'select'} .= ', part_pkg.pkg'; $sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)'; if ( @svc_x ) { my $svcnums = join(',', map { $_->get('svcnum') } @svc_x); $sql_query->{'extra_sql'} .= ' AND svcnum IN('.$svcnums.')'; } 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 $num_rows = FS::Record->scalar_sql($sql_query->{count_query}); my @footer = ( '', emt('[quant,_1,service]', $num_rows), ('') x $skip_cols, map { my $i = $_; sub { # defer this until the rows have been processed bytes_to_gb($total_usage[$i]) } } (0,1,2) ); 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", }; #warn Dumper \%usage_by_username;