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, 
      exports           => \@exports,
      agentnum          => $agent->agentnum,
      nohtmlheader      => 1,
      download_label    => 'Download this section',
      &>
</DIV>
<BR><BR>
%  }
<& /elements/footer.html &>
% } else {
<& elements/search.html,
  'title'       => $title,
  'name'        => $combine_svcs ? 'packages' : 'services',
  'query'       => $sql_query,
  'count_query' => $count_query,
  'header'      => [ #FS::UI::Web::cust_header(),
                     '#',
                     'Customer',
                     'Package',
                     'User',
                     'Upload (GB)',
                     'Download (GB)',
                     'Total (GB)',
                   ],
  'footer'      => \@footer,
  'fields'      => [ #\&FS::UI::Web::cust_fields,
                     'display_custnum',
                     'name',
                     'pkg',
                     @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,
                     $link_pkg,
                     $link_svc,
                     '',
                     '',
                   ],
  'align'       => #FS::UI::Web::cust_aligns() .
                   'rlcrrr',
  '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 packages');

my $title = 'Data Usage Report - '; 
my $agentnum = '';
my @include_agents = ();

my $combine_svcs = $cgi->param('combine_svcs') ? 1 : 0;

if ( $opt{'agentnum'} =~ /^(\d+)$/ ) {
  $agentnum = $opt{'agentnum'};
} 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";
}

# 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;
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;
}

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;
}

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;
  }
  $export_svcparts{ $export->exportnum } = \%seen;
}

# 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 @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) : '';
  };
}

my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];

# 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 $group_by = ' GROUP BY pkgnum';
if ( !$combine_svcs ) {
  $group_by .= ', 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 $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

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 = (
  '',
  emt("[quant,_1,$itemname]", $num_rows), 
  '', #pkg label
  '', #username
  map {
    my $i = $_;
    sub { # defer this until the rows have been processed
      bytes_to_gb($total_usage[$i])
    }
  } (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>