rest of sqlradius data usage report

diff --git a/httemplate/search/report_sqlradius_usage.html b/httemplate/search/report_sqlradius_usage.html
new file mode 100644 (file)
index 0000000..01215e8
--- /dev/null
+++ b/httemplate/search/report_sqlradius_usage.html
@@ -0,0 +1,40 @@
+<& /elements/header.html, mt($title) &>
+
+<FORM ACTION="sqlradius_usage.html" METHOD="GET">
+
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0
+
+<& /elements/tr-select-agent.html,
+  'empty_label'   => '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 &>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+
+</FORM>
+
+<& /elements/footer.html &>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Usage: RADIUS sessions');
+  # yes?
+
+my $title = 'Data Usage Report';
+
+</%init>

diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html
new file mode 100644 (file)
index 0000000..dd06c10
--- /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
+<DIV WIDTH="100%" STYLE="page-break-after: always">
+<FONT SIZE=6><% $agent->agent %></FONT><BR><BR>
+  <& sqlradius_usage.html, 
+      export            => $export,
+      agentnum          => $agent->agentnum,
+      nohtmlheader      => 1,
+      usage_by_username => \%usage_by_username,
+      download_label    => 'Download this section',
+      &>
+</DIV>
+<BR><BR>
+%  }
+<& /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',
+  ('&nbsp;') x $skip_cols,
+  map { 
+    sub { # defer this until the rows have been processed
+        FS::UI::bytecount::display_bytecount($total_usage[$_])
+    }
+  } (0,1,2)
+);
+
+</%init>