path: root/httemplate/search/sqlradius_usage.html

% 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 (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
    $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;

</%init>