From: jeff Date: Thu, 5 Aug 2010 04:17:33 +0000 (+0000) Subject: add options to only process account records from a particular realm and to ignore... X-Git-Tag: freeside_1_9_5~82 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=d6c890ac02a88f1355c49380b1e0d70aa02f35ac add options to only process account records from a particular realm and to ignore sessions that span billing periods RT8082 --- diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm index 3ce13144c..29ade29f2 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); + } } }