X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fpart_export%2Fsqlradius.pm;h=b868121bf479effd5979dc281e6eee104c8420d2;hb=d54c1ccb1355bd4d977e06054694373a84cb68ff;hp=d8c5e04243184781de93dae1fa857eff1967cbd2;hpb=b90c466e6b0d4477855ff3ff7b8f40937b129364;p=freeside.git diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm index d8c5e0424..b868121bf 100644 --- a/FS/FS/part_export/sqlradius.pm +++ b/FS/FS/part_export/sqlradius.pm @@ -14,10 +14,15 @@ use Carp qw( cluck ); $DEBUG = 0; +my %groups; 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' @@ -43,7 +48,21 @@ tie %options, 'Tie::IxHash', type => 'checkbox', label => 'Show the Called-Station-ID on session reports', }, - 'overlimit_groups' => { label => 'Radius groups to assign to svc_acct which has exceeded its bandwidth or time limit (if not overridden by overlimit_groups global or per-agent config)', } , + 'overlimit_groups' => { + label => 'Radius groups to assign to svc_acct which has exceeded its bandwidth or time limit (if not overridden by overlimit_groups global or per-agent config)', + type => 'select', + multi => 1, + option_label => sub { + $groups{$_[0]}; + }, + option_values => sub { + %groups = ( + map { $_->groupnum, $_->long_description } + qsearch('radius_group', {}), + ); + sort keys (%groups); + }, + } , 'groups_susp_reason' => { label => 'Radius group mapping to reason (via template user) (svcnum|username|username@domain reasonnum|reason)', type => 'textarea', @@ -52,7 +71,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. @@ -121,9 +140,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); } ''; @@ -145,8 +165,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; @@ -230,16 +251,20 @@ sub _export_suspend { local $FS::UID::AutoCommit = 0; my $dbh = dbh; - my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'insert', - 'check', $self->export_username($new), $new->radius_check ); - unless ( ref($err_or_queue) ) { - $dbh->rollback if $oldAutoCommit; - return $err_or_queue; + my @newgroups = $self->suspended_usergroups($svc_acct); + + unless (@newgroups) { #don't change password if assigning to a suspended group + + my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'insert', + 'check', $self->export_username($new), $new->radius_check ); + unless ( ref($err_or_queue) ) { + $dbh->rollback if $oldAutoCommit; + return $err_or_queue; + } + } - my $error; - my (@newgroups) = $self->suspended_usergroups($svc_acct); - $error = + my $error = $self->sqlreplace_usergroups( $new->svcnum, $self->export_username($new), '', @@ -295,8 +320,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; } @@ -387,14 +413,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 ) { @@ -408,15 +436,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 ) @@ -427,8 +465,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) @@ -454,8 +493,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; @@ -484,9 +524,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 ) { @@ -500,7 +542,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 ) { @@ -587,6 +629,7 @@ sub usage_sessions { my $opt = {}; my($start, $end, $svc_acct, $ip, $prefix) = ( '', '', '', '', ''); + my $summarize = 0; if ( ref($_[0]) ) { $opt = shift; $start = $opt->{stoptime_start}; @@ -594,6 +637,7 @@ sub usage_sessions { $svc_acct = $opt->{svc_acct}; $ip = $opt->{ip}; $prefix = $opt->{prefix}; + $summarize = $opt->{summarize}; } else { ( $start, $end ) = splice(@_, 0, 2); $svc_acct = @_ ? shift : ''; @@ -621,6 +665,10 @@ sub usage_sessions { "$str2time acctstoptime ) as acctstoptime", ); + @fields = ( 'username', 'sum(acctsessiontime) as acctsessiontime', 'sum(acctinputoctets) as acctinputoctets', + 'sum(acctoutputoctets) as acctoutputoctets', + ) if $summarize; + my @param = (); my @where = (); @@ -673,11 +721,15 @@ sub usage_sessions { my $where = join(' AND ', @where); $where = "WHERE $where" if $where; + my $groupby = ''; + $groupby = 'GROUP BY username' if $summarize; + + my $orderby = 'ORDER BY AcctStartTime DESC'; + $orderby = '' if $summarize; + my $sth = $dbh->prepare('SELECT '. join(', ', @fields). - " FROM radacct - $where - ORDER BY AcctStartTime DESC - ") or die $dbh->errstr; + " FROM radacct $where $groupby $orderby + ") or die $dbh->errstr; $sth->execute(@param) or die $sth->errstr; [ map { { %$_ } } @{ $sth->fetchall_arrayref({}) } ];