X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Fpart_export%2Fsqlradius.pm;h=10bccb0347b3b221ec279335057538a716925874;hb=673b9a458d9138523026963df6fa3b4683e09bae;hp=fd5bb89fd4d05cdddb7c3cc9dca756e105879aae;hpb=dabdf357484badff95afcae50b08ec1c3bb58343;p=freeside.git diff --git a/FS/FS/part_export/sqlradius.pm b/FS/FS/part_export/sqlradius.pm index fd5bb89fd..10bccb034 100644 --- a/FS/FS/part_export/sqlradius.pm +++ b/FS/FS/part_export/sqlradius.pm @@ -1,27 +1,43 @@ package FS::part_export::sqlradius; -use vars qw(@ISA %info %options $notes1 $notes2); +use vars qw(@ISA $DEBUG %info %options $notes1 $notes2); use Tie::IxHash; -use FS::Record qw( dbh ); +use FS::Record qw( dbh qsearch ); use FS::part_export; +use FS::svc_acct; +use FS::export_svc; @ISA = qw(FS::part_export); +$DEBUG = 0; + tie %options, 'Tie::IxHash', 'datasrc' => { label=>'DBI data source ' }, 'username' => { label=>'Database username' }, 'password' => { label=>'Database password' }, 'ignore_accounting' => { - type => 'checkbox', - label=>'Ignore accounting records from this database' + type => 'checkbox', + label => 'Ignore accounting records from this database' + }, + 'hide_ip' => { + type => 'checkbox', + label => 'Hide IP address information on session reports', + }, + 'hide_data' => { + type => 'checkbox', + label => 'Hide download/upload information on session reports', + }, + 'show_called_station' => { + type => 'checkbox', + label => 'Show the Called-Station-ID on session reports', }, ; $notes1 = <<'END'; -Real-time export of radcheck, radreply and usergroup tables to any SQL database -for FreeRADIUS, -ICRADIUS -or Radiator. +Real-time export of radcheck, radreply and usergroup +tables to any SQL database for +FreeRADIUS +or ICRADIUS. END $notes2 = <<'END'; @@ -50,7 +66,7 @@ END %info = ( 'svc' => 'svc_acct', - 'desc' => 'Real-time export to SQL-backed RADIUS (FreeRADIUS, ICRADIUS, Radiator)', + 'desc' => 'Real-time export to SQL-backed RADIUS (FreeRADIUS, ICRADIUS)', 'options' => \%options, 'nodomain' => 'Y', 'notes' => $notes1. @@ -63,6 +79,7 @@ sub rebless { shift; } sub export_username { my($self, $svc_acct) = (shift, shift); + warn "export_username called on $self with arg $svc_acct" if $DEBUG; $svc_acct->username; } @@ -333,5 +350,203 @@ sub sqlradius_connect { DBI->connect(@_) or die $DBI::errstr; } +#-- + +=item usage_sessions TIMESTAMP_START TIMESTAMP_END [ SVC_ACCT [ IP [ PREFIX [ SQL_SELECT ] ] ] ] + +TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see +L. Also see L and L for conversion +functions. + +SVC_ACCT, if specified, limits the results to the specified account. + +IP, if specified, limits the results to the specified IP address. + +PREFIX, if specified, limits the results to records with a matching +Called-Station-ID. + +#SQL_SELECT defaults to * if unspecified. It can be useful to set it to +#SUM(acctsessiontime) or SUM(AcctInputOctets), etc. + +Returns an arrayref of hashrefs with the following fields: + +=over 4 + +=item username + +=item framedipaddress + +=item acctstarttime + +=item acctstoptime + +=item acctsessiontime + +=item acctinputoctets + +=item acctoutputoctets + +=item calledstationid + +=back + +=cut + +#some false laziness w/cust_svc::seconds_since_sqlradacct + +sub usage_sessions { + my( $self, $start, $end ) = splice(@_, 0, 3); + my $svc_acct = @_ ? shift : ''; + my $ip = @_ ? shift : ''; + my $prefix = @_ ? shift : ''; + #my $select = @_ ? shift : '*'; + + $end ||= 2147483647; + + return [] if $self->option('ignore_accounting'); + + my $dbh = sqlradius_connect( map $self->option($_), + qw( datasrc username password ) ); + + #select a unix time conversion function based on database type + my $str2time; + if ( $dbh->{Driver}->{Name} =~ /^mysql(PP)?$/ ) { + $str2time = 'UNIX_TIMESTAMP('; + } elsif ( $dbh->{Driver}->{Name} eq 'Pg' ) { + $str2time = 'EXTRACT( EPOCH FROM '; + } else { + warn "warning: unknown database type ". $dbh->{Driver}->{Name}. + "; guessing how to convert to UNIX timestamps"; + $str2time = 'extract(epoch from '; + } + + my @fields = ( + qw( username realm framedipaddress + acctsessiontime acctinputoctets acctoutputoctets + calledstationid + ), + "$str2time acctstarttime ) as acctstarttime", + "$str2time acctstoptime ) as acctstoptime", + ); + + my @param = (); + my $where = ''; + + if ( $svc_acct ) { + my $username = $self->export_username($svc_acct); + if ( $svc_acct =~ /^([^@]+)\@([^@]+)$/ ) { + $where = '( UserName = ? OR ( UserName = ? AND Realm = ? ) ) AND'; + push @param, $username, $1, $2; + } else { + $where = 'UserName = ? AND'; + push @param, $username; + } + } + + if ( length($ip) ) { + $where .= ' FramedIPAddress = ? AND'; + push @param, $ip; + } + + if ( length($prefix) ) { + #assume sip: for now, else things get ugly trying to match /^\w+:$prefix/ + $where .= " CalledStationID LIKE 'sip:$prefix\%' AND"; + } + + push @param, $start, $end; + + my $sth = $dbh->prepare('SELECT '. join(', ', @fields). + " FROM radacct + WHERE $where + $str2time AcctStopTime ) >= ? + AND $str2time AcctStopTime ) <= ? + ORDER BY AcctStartTime DESC + ") or die $dbh->errstr; + $sth->execute(@param) or die $sth->errstr; + + [ map { { %$_ } } @{ $sth->fetchall_arrayref({}) } ]; + +} + +=item update_svc_acct + +=cut + +sub update_svc_acct { + my $self = shift; + + my $dbh = sqlradius_connect( map $self->option($_), + qw( datasrc username password ) ); + + my @fields = qw( radacctid username realm acctsessiontime ); + + my @param = (); + my $where = ''; + + my $sth = $dbh->prepare(" + SELECT RadAcctId, UserName, Realm, AcctSessionTime + FROM radacct + WHERE FreesideStatus IS NULL + AND AcctStopTime != 0 + ") or die $dbh->errstr; + $sth->execute() or die $sth->errstr; + + while ( my $row = $sth->fetchrow_arrayref ) { + my($RadAcctId, $UserName, $Realm, $AcctSessionTime) = @$row; + warn "processing record: ". + "$RadAcctId ($UserName\@$Realm for ${AcctSessionTime}s" + if $DEBUG; + + my %search = ( 'username' => $UserName ); + my $extra_sql = ''; + if ( ref($self) =~ /withdomain/ ) { #well... + $extra_sql = " AND '$Realm' = ( SELECT domain FROM svc_domain + WHERE svc_domain.svcnum = svc_acct.domsvc ) "; + my $svc_domain = qsearch + } + + my @svc_acct = + grep { qsearch( 'export_svc', { 'exportnum' => $self->exportnum, + 'svcpart' => $_->cust_svc->svcpart, } ) + } + qsearch( 'svc_acct', + { 'username' => $UserName }, + '', + $extra_sql + ); + + 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; + if ( $svc_acct->seconds !~ /^$/ ) { + warn " svc_acct.seconds found (". $svc_acct->seconds. + ") - decrementing\n" + if $DEBUG; + my $error = $svc_acct->decrement_seconds($AcctSessionTime); + die $error if $error; + $status = 'done'; + } else { + warn " no existing seconds value for svc_acct - skiping\n" if $DEBUG; + } + } + + warn "setting FreesideStatus to $status $errinfo\n" if $DEBUG; + my $psth = $dbh->prepare("UPDATE radacct + SET FreesideStatus = ? + WHERE RadAcctId = ?" + ) or die $dbh->errstr; + $psth->execute($status, $RadAcctId) or die $psth->errstr; + + } + +} + 1;