--- /dev/null
+<& /elements/header-popup.html, mt($title) &>
+
+<FORM ACTION="sqlradius_usage.html" METHOD="GET" TARGET="_top">
+
+<& /elements/hidden.html,
+ 'field' => 'custnum',
+ 'value' => $custnum,
+&>
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+% if ( scalar(@exports) == 1 ) {
+<tr><td>
+<& /elements/hidden.html,
+ 'field' => 'exportnum',
+ 'value' => $exports[0]->exportnum,
+&>
+</td></tr>
+% } else {
+<& /elements/tr-select-table.html,
+ 'label' => 'Export', # kind of non-indicative...
+ 'table' => 'part_export',
+ 'name_col' => 'label',
+ 'value_col' => 'exportnum',
+ 'records' => \@exports,
+ 'disable_empty' => 1,
+&>
+% }
+<& /elements/tr-input-beginning_ending.html &>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+
+</FORM>
+
+<& /elements/footer.html &>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+die "access denied"
+ unless $curuser->access_right('Usage: RADIUS sessions');
+ # yes?
+
+my $title = 'Data Usage Report';
+my $custnum;
+if ($cgi->keywords) {
+ ($custnum) = $cgi->keywords;
+} else {
+ $custnum = $cgi->param('custnum');
+}
+$custnum =~ /^(\d+)$/
+ or die "illegal custnum $custnum";
+my $cust_main = qsearchs( {
+ 'table' => 'cust_main',
+ 'hashref' => { 'custnum' => $custnum },
+ 'extra_sql' => ' AND '. $curuser->agentnums_sql,
+});
+# get all exports that apply to this customer's services--should be fast, as
+# everything here is indexed
+my @exports = qsearch({
+ 'table' => 'part_export',
+ 'select' => 'DISTINCT part_export.*',
+ 'addl_from' => ' JOIN export_svc USING (exportnum)
+ JOIN cust_svc USING (svcpart)
+ JOIN cust_pkg USING (pkgnum) ',
+ 'extra_sql' => ' WHERE cust_pkg.custnum = '.$custnum,
+});
+@exports = grep { $_->can('usage_sessions') } @exports;
+
+</%init>
my %opt = @_;
-die "access denied" unless
- $FS::CurrentUser::CurrentUser->access_right('List services');
+my $curuser = $FS::CurrentUser::CurrentUser;
+die "access denied" unless $curuser->access_right('List services');
my $title = 'Data Usage Report - ';
my $agentnum;
$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}) ) {
or die "exportnum ".$export->exportnum." is type ".$export->exporttype.
", not sqlradius";
- my $usage = $export->usage_sessions( {
+ my %usage_param = (
stoptime_start => $beginning,
stoptime_end => $ending,
summarize => 1
- } );
- # arrayref of hashrefs of
+ );
+ # usage_sessions() returns an arrayref of hashrefs of
# (username, acctsessiontime, acctinputoctets, acctoutputoctets)
# (XXX needs to include 'realm' for sqlradius_withdomain)
- # rearrange to be indexed by username.
+ 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 = (
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 = (
'',
- FS::Record->scalar_sql($sql_query->{count_query}) . ' services',
+ emt('[quant,_1,service]', $num_rows),
('') x $skip_cols,
map {
my $i = $_;
$_[0] ? sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
}
+warn Dumper \%usage_by_username;
+
+
</%init>