add options to only process account records from a particular realm and to ignore sessions that span billing periods RT8082

2 files changed, 95 insertions, 38 deletions

diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm
index c0766e582..7b866fad3 100644
--- a/FS/FS/cust_svc.pm
+++ b/FS/FS/cust_svc.pm
@@ -539,15 +539,24 @@ sub seconds_since_sqlradacct {
     warn "$mes finding closed sessions completely within the given range\n"
       if $DEBUG;
   
+    my $realm = '';
+    my $realmparam = '';
+    if ($part_export->option('process_single_realm')) {
+      $realm = 'AND Realm = ?';
+      $realmparam = $part_export->option('realm');
+    }
+
     my $sth = $dbh->prepare("SELECT SUM(acctsessiontime)
                                FROM radacct
                                WHERE UserName = ?
+                                 $realm
                                  AND $str2time AcctStartTime) >= ?
                                  AND $str2time AcctStopTime ) <  ?
                                  AND $str2time AcctStopTime ) > 0
                                  AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
-    $sth->execute($username, $start, $end) or die $sth->errstr;
+    $sth->execute($username, ($realm ? $realmparam : ()), $start, $end)
+      or die $sth->errstr;
     my $regular = $sth->fetchrow_arrayref->[0];
   
     warn "$mes finding open sessions which start in the range\n"
@@ -557,13 +566,19 @@ sub seconds_since_sqlradacct {
     $query = "SELECT SUM( ? - $str2time AcctStartTime ) )
                 FROM radacct
                 WHERE UserName = ?
+                  $realm
                   AND $str2time AcctStartTime ) >= ?
                   AND $str2time AcctStartTime ) <  ?
                   AND ( ? - $str2time AcctStartTime ) ) < 86400
                   AND (    $str2time AcctStopTime ) = 0
                                     OR AcctStopTime IS NULL )";
     $sth = $dbh->prepare($query) or die $dbh->errstr;
-    $sth->execute($end, $username, $start, $end, $end)
+    $sth->execute( $end,
+                   $username,
+                   ($realm ? $realmparam : ()),
+                   $start,
+                   $end,
+                   $end )
       or die $sth->errstr. " executing query $query";
     my $start_during = $sth->fetchrow_arrayref->[0];
   
@@ -574,13 +589,20 @@ sub seconds_since_sqlradacct {
     $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime ) - ? ) 
                             FROM radacct
                             WHERE UserName = ?
+                              $realm
                               AND $str2time AcctStartTime ) < ?
                               AND $str2time AcctStopTime  ) >= ?
                               AND $str2time AcctStopTime  ) <  ?
                               AND $str2time AcctStopTime ) > 0
                               AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
-    $sth->execute($start, $username, $start, $start, $end ) or die $sth->errstr;
+    $sth->execute( $start,
+                   $username,
+                   ($realm ? $realmparam : ()),
+                   $start,
+                   $start,
+                   $end )
+      or die $sth->errstr;
     my $end_during = $sth->fetchrow_arrayref->[0];
   
     warn "$mes finding closed sessions which start before the range but stop after\n"
@@ -591,13 +613,15 @@ sub seconds_since_sqlradacct {
     $sth = $dbh->prepare("SELECT COUNT(*)
                             FROM radacct
                             WHERE UserName = ?
+                              $realm
                               AND $str2time AcctStartTime ) < ?
                               AND ( $str2time AcctStopTime ) >= ?
                                                                   )"
                               #      OR AcctStopTime =  0
                               #      OR AcctStopTime IS NULL       )"
     ) or die $dbh->errstr;
-    $sth->execute($username, $start, $end ) or die $sth->errstr;
+    $sth->execute($username, ($realm ? $realmparam : ()), $start, $end )
+      or die $sth->errstr;
     my $entire_range = ($end-$start) * $sth->fetchrow_arrayref->[0];
 
     $seconds += $regular + $end_during + $start_during + $entire_range;
@@ -658,14 +682,23 @@ sub attribute_since_sqlradacct {
     warn "$mes SUMing $attrib sessions\n"
       if $DEBUG;
 
+    my $realm = '';
+    my $realmparam = '';
+    if ($part_export->option('process_single_realm')) {
+      $realm = 'AND Realm = ?';
+      $realmparam = $part_export->option('realm');
+    }
+
     my $sth = $dbh->prepare("SELECT SUM($attrib)
                                FROM radacct
                                WHERE UserName = ?
+                                 $realm
                                  AND $str2time AcctStopTime ) >= ?
                                  AND $str2time AcctStopTime ) <  ?
                                  AND AcctStopTime IS NOT NULL"
     ) or die $dbh->errstr;
-    $sth->execute($username, $start, $end) or die $sth->errstr;
+    $sth->execute($username, ($realm ? $realmparam : ()), $start, $end)
+      or die $sth->errstr;
 
     my $row = $sth->fetchrow_arrayref;
     $sum += $row->[0] if defined($row->[0]);
diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm
index 4f67ac6c3..d8c5e0424 100644
--- a/FS/FS/part_export/sqlradius.pm
+++ b/FS/FS/part_export/sqlradius.pm
@@ -22,6 +22,15 @@ tie %options, 'Tie::IxHash',
     type  => 'checkbox',
     label => 'Ignore accounting records from this database'
   },
+  'process_single_realm' => {
+    type  => 'checkbox',
+    label => 'Only process one realm of accounting records',
+  },
+  'realm' => { label => 'The realm of of accounting records to be processed' },
+  'ignore_long_sessions' => {
+    type  => 'checkbox',
+    label => 'Ignore sessions which span billing periods',
+  },
   'hide_ip' => {
     type  => 'checkbox',
     label => 'Hide IP address information on session reports',
@@ -617,7 +626,7 @@ sub usage_sessions {
 
   if ( $svc_acct ) {
     my $username = $self->export_username($svc_acct);
-    if ( $svc_acct =~ /^([^@]+)\@([^@]+)$/ ) {
+    if ( $username =~ /^([^@]+)\@([^@]+)$/ ) {
       push @where, '( UserName = ? OR ( UserName = ? AND Realm = ? ) )';
       push @param, $username, $1, $2;
     } else {
@@ -626,6 +635,11 @@ sub usage_sessions {
     }
   }
 
+  if ($self->option('process_single_realm')) {
+    push @where, 'Realm = ?';
+    push @param, $self->option('realm');
+  }
+
   if ( length($ip) ) {
     push @where, ' FramedIPAddress = ?';
     push @param, $ip;
@@ -719,43 +733,53 @@ sub update_svc {
     my $oldAutoCommit = $FS::UID::AutoCommit; # can't undo side effects, but at
     local $FS::UID::AutoCommit = 0;           # least we can avoid over counting
 
-    my @svc_acct =
-      grep { qsearch( 'export_svc', { 'exportnum' => $self->exportnum,
-                                      'svcpart'   => $_->cust_svc->svcpart, } )
-           }
-      qsearch( 'svc_acct',
-                 { 'username' => $UserName },
-                 '',
-                 $extra_sql
-               );
-
+    my $status = 'skipped';
     my $errinfo = "for RADIUS detail RadAcctID $RadAcctId ".
                   "(UserName $UserName, Realm $Realm)";
-    my $status = 'skipped';
-    if ( !@svc_acct ) {
-      warn "WARNING: no svc_acct record found $errinfo - skipping\n";
-    } elsif ( scalar(@svc_acct) > 1 ) {
-      warn "WARNING: multiple svc_acct records found $errinfo - skipping\n";
-    } else {
-
-      my $svc_acct = $svc_acct[0];
-      warn "found svc_acct ". $svc_acct->svcnum. " $errinfo\n" if $DEBUG;
 
-      $svc_acct->last_login($AcctStartTime);
-      $svc_acct->last_logout($AcctStopTime);
-
-      my $cust_pkg = $svc_acct->cust_svc->cust_pkg;
-      if ( $cust_pkg && $AcctStopTime < (    $cust_pkg->last_bill
-                                          || $cust_pkg->setup     )  ) {
-        $status = 'skipped (too old)';
+    if (    $self->option('process_single_realm')
+         && $self->option('realm') ne $Realm )
+    {
+      warn "WARNING: wrong realm $errinfo - skipping\n" if $DEBUG;
+    } else {
+      my @svc_acct =
+        grep { qsearch( 'export_svc', { 'exportnum' => $self->exportnum,
+                                        'svcpart'   => $_->cust_svc->svcpart, } )
+             }
+        qsearch( 'svc_acct',
+                   { 'username' => $UserName },
+                   '',
+                   $extra_sql
+                 );
+
+      if ( !@svc_acct ) {
+        warn "WARNING: no svc_acct record found $errinfo - skipping\n";
+      } elsif ( scalar(@svc_acct) > 1 ) {
+        warn "WARNING: multiple svc_acct records found $errinfo - skipping\n";
       } else {
-        my @st;
-        push @st, _try_decrement($svc_acct, 'seconds',    $AcctSessionTime   );
-        push @st, _try_decrement($svc_acct, 'upbytes',    $AcctInputOctets   );
-        push @st, _try_decrement($svc_acct, 'downbytes',  $AcctOutputOctets  );
-        push @st, _try_decrement($svc_acct, 'totalbytes', $AcctInputOctets
+
+        my $svc_acct = $svc_acct[0];
+        warn "found svc_acct ". $svc_acct->svcnum. " $errinfo\n" if $DEBUG;
+
+        $svc_acct->last_login($AcctStartTime);
+        $svc_acct->last_logout($AcctStopTime);
+
+        my $session_time = $AcctStopTime;
+        $session_time = $AcctStartTime if $self->option('ignore_long_sessions');
+
+        my $cust_pkg = $svc_acct->cust_svc->cust_pkg;
+        if ( $cust_pkg && $session_time < (    $cust_pkg->last_bill
+                                            || $cust_pkg->setup     )  ) {
+          $status = 'skipped (too old)';
+        } else {
+          my @st;
+          push @st, _try_decrement($svc_acct, 'seconds',    $AcctSessionTime);
+          push @st, _try_decrement($svc_acct, 'upbytes',    $AcctInputOctets);
+          push @st, _try_decrement($svc_acct, 'downbytes',  $AcctOutputOctets);
+          push @st, _try_decrement($svc_acct, 'totalbytes', $AcctInputOctets
                                                           + $AcctOutputOctets);
-        $status=join(' ', @st);
+          $status=join(' ', @st);
+        }
       }
     }