X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fpart_export%2Fsqlradius.pm;h=15aa9862053beae95d30d48858ff67fcd80ac9f7;hb=90393980e5f2859ee1e186fa461f48f5129e803e;hp=4f67ac6c3a44c3dd1bfdc2e623d2b29b791b4208;hpb=63a268637b2d51a8766412617724b9436439deb6;p=freeside.git diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm index 4f67ac6c3..15aa98620 100644 --- a/FS/FS/part_export/sqlradius.pm +++ b/FS/FS/part_export/sqlradius.pm @@ -18,10 +18,23 @@ tie %options, 'Tie::IxHash', 'datasrc' => { label=>'DBI data source ' }, 'username' => { label=>'Database username' }, 'password' => { label=>'Database password' }, + 'usergroup' => { label => 'Group table', + type => 'select', + options => [qw( usergroup radusergroup ) ], + }, 'ignore_accounting' => { 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', @@ -43,7 +56,7 @@ tie %options, 'Tie::IxHash', ; $notes1 = <<'END'; -Real-time export of radcheck, radreply and usergroup +Real-time export of radcheck, radreply and usergroup/radusergroup tables to any SQL database for FreeRADIUS or ICRADIUS. @@ -112,9 +125,10 @@ sub _export_insert { cluck localtime(). ": queuing usergroup_insert for ". $svc_x->svcnum. " (". $self->export_username($svc_x). " with ". join(", ", @groups) if $DEBUG; + my $usergroup = $self->option('usergroup') || 'usergroup'; my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'usergroup_insert', - $self->export_username($svc_x), @groups ); + $self->export_username($svc_x), $usergroup, @groups ); return $err_or_queue unless ref($err_or_queue); } ''; @@ -136,8 +150,9 @@ sub _export_replace { my $jobnum = ''; if ( $self->export_username($old) ne $self->export_username($new) ) { + my $usergroup = $self->option('usergroup') || 'usergroup'; my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'rename', - $self->export_username($new), $self->export_username($old) ); + $self->export_username($new), $self->export_username($old), $usergroup ); unless ( ref($err_or_queue) ) { $dbh->rollback if $oldAutoCommit; return $err_or_queue; @@ -286,8 +301,9 @@ sub _export_unsuspend { sub _export_delete { my( $self, $svc_x ) = (shift, shift); + my $usergroup = $self->option('usergroup') || 'usergroup'; my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'delete', - $self->export_username($svc_x) ); + $self->export_username($svc_x), $usergroup ); ref($err_or_queue) ? '' : $err_or_queue; } @@ -378,14 +394,16 @@ sub sqlradius_insert { #subroutine, not method sub sqlradius_usergroup_insert { #subroutine, not method my $dbh = sqlradius_connect(shift, shift, shift); - my( $username, @groups ) = @_; + my $username = shift; + my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup'; + my @groups = @_; my $s_sth = $dbh->prepare( - "SELECT COUNT(*) FROM usergroup WHERE UserName = ? AND GroupName = ?" + "SELECT COUNT(*) FROM $usergroup WHERE UserName = ? AND GroupName = ?" ) or die $dbh->errstr; my $sth = $dbh->prepare( - "INSERT INTO usergroup ( UserName, GroupName ) VALUES ( ?, ? )" + "INSERT INTO $usergroup ( UserName, GroupName ) VALUES ( ?, ? )" ) or die $dbh->errstr; foreach my $group ( @groups ) { @@ -399,15 +417,25 @@ sub sqlradius_usergroup_insert { #subroutine, not method $sth->execute( $username, $group ) or die "can't insert into groupname table: ". $sth->errstr; } + if ( $s_sth->{Active} ) { + warn "sqlradius s_sth still active; calling ->finish()"; + $s_sth->finish; + } + if ( $sth->{Active} ) { + warn "sqlradius sth still active; calling ->finish()"; + $sth->finish; + } $dbh->disconnect; } sub sqlradius_usergroup_delete { #subroutine, not method my $dbh = sqlradius_connect(shift, shift, shift); - my( $username, @groups ) = @_; + my $username = shift; + my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup'; + my @groups = @_; my $sth = $dbh->prepare( - "DELETE FROM usergroup WHERE UserName = ? AND GroupName = ?" + "DELETE FROM $usergroup WHERE UserName = ? AND GroupName = ?" ) or die $dbh->errstr; foreach my $group ( @groups ) { $sth->execute( $username, $group ) @@ -418,8 +446,9 @@ sub sqlradius_usergroup_delete { #subroutine, not method sub sqlradius_rename { #subroutine, not method my $dbh = sqlradius_connect(shift, shift, shift); - my($new_username, $old_username) = @_; - foreach my $table (qw(radreply radcheck usergroup )) { + my($new_username, $old_username) = (shift, shift); + my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup'; + foreach my $table (qw(radreply radcheck), $usergroup ) { my $sth = $dbh->prepare("UPDATE $table SET Username = ? WHERE UserName = ?") or die $dbh->errstr; $sth->execute($new_username, $old_username) @@ -445,8 +474,9 @@ sub sqlradius_attrib_delete { #subroutine, not method sub sqlradius_delete { #subroutine, not method my $dbh = sqlradius_connect(shift, shift, shift); my $username = shift; + my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup'; - foreach my $table (qw( radcheck radreply usergroup )) { + foreach my $table (qw( radcheck radreply), $usergroup ) { my $sth = $dbh->prepare( "DELETE FROM $table WHERE UserName = ?" ); $sth->execute($username) or die "can't delete from $table table: ". $sth->errstr; @@ -475,9 +505,11 @@ sub sqlreplace_usergroups { push @delgroups, $oldgroup; } + my $usergroup = $self->option('usergroup') || 'usergroup'; + if ( @delgroups ) { my $err_or_queue = $self->sqlradius_queue( $svcnum, 'usergroup_delete', - $username, @delgroups ); + $username, $usergroup, @delgroups ); return $err_or_queue unless ref($err_or_queue); if ( $jobnum ) { @@ -491,7 +523,7 @@ sub sqlreplace_usergroups { "with ". join(", ", @newgroups) if $DEBUG; my $err_or_queue = $self->sqlradius_queue( $svcnum, 'usergroup_insert', - $username, @newgroups ); + $username, $usergroup, @newgroups ); return $err_or_queue unless ref($err_or_queue); if ( $jobnum ) { @@ -617,7 +649,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 +658,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 +756,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); + } } }