From acfb0f48c226a5cba64fbe391677391128a6cbf7 Mon Sep 17 00:00:00 2001 From: ivan Date: Thu, 17 Oct 2002 14:16:17 +0000 Subject: [PATCH] radacct update: use sqlradius for datasrc, not plandata options (whew) --- FS/FS/cust_pkg.pm | 19 ++---- FS/FS/cust_svc.pm | 150 ++++++++++++++++++++++++------------------- FS/FS/part_svc.pm | 15 +++-- FS/FS/svc_acct.pm | 15 +++-- httemplate/edit/part_pkg.cgi | 13 +--- httemplate/view/svc_acct.cgi | 16 +---- 6 files changed, 110 insertions(+), 118 deletions(-) diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index 803fa3c16..55ee37d9d 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -511,17 +511,11 @@ sub seconds_since { } -=item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END DATASRC DB_USERNAME DB_PASSWORD +=item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END Returns the numbers of seconds all accounts (see L) in this package have been online between TIMESTAMP_START (inclusive) and TIMESTAMP_END -(exclusive), according to an external SQL radacct table, such as those -generated by ICRADIUS or FreeRADIUS. Sessions which started in the specified -range but are still open are counted from session start to the end of the -range. Also, sessions which end in the range but started earlier are counted -from the start of the range to session end. Finally, sessions which start -before the range but end after (or are still open) are counted for the entire -range. +(exclusive). TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see L. Also see L and L for conversion @@ -531,17 +525,14 @@ functions. =cut sub seconds_since_sqlradacct { - my($self, $start, $end, $datasrc, $db_user, $db_pass) = @_; - - my $dbh = DBI->connect($datasrc, $db_user, $db_pass) - or die "can't connect to $datasrc: ". $DBI::errstr; + my($self, $start, $end) = @_; my $seconds = 0; foreach my $cust_svc ( grep { $_->part_svc->svcdb eq 'svc_acct' } $self->cust_svc ) { - $seconds += $cust_svc->seconds_since_sqlradacct($start, $end, $dbh); + $seconds += $cust_svc->seconds_since_sqlradacct($start, $end); } $seconds; @@ -730,7 +721,7 @@ sub order { =head1 VERSION -$Id: cust_pkg.pm,v 1.26 2002-10-14 06:17:14 ivan Exp $ +$Id: cust_pkg.pm,v 1.27 2002-10-17 14:16:17 ivan Exp $ =head1 BUGS diff --git a/FS/FS/cust_svc.pm b/FS/FS/cust_svc.pm index 4fc663450..50d94452a 100644 --- a/FS/FS/cust_svc.pm +++ b/FS/FS/cust_svc.pm @@ -12,6 +12,7 @@ use FS::svc_acct; use FS::svc_domain; use FS::svc_forward; use FS::domain_record; +use FS::part_export; @ISA = qw( FS::Record ); @@ -336,93 +337,110 @@ sub seconds_since { $sth->fetchrow_arrayref->[0]; } -=item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END ( DBI_DATABASE_HANDLE | DATASRC DB_USERNAME DB_PASSWORD ) +=item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END See L. Equivalent to $cust_svc->svc_x->seconds_since, but more efficient. Meaningless for records where B is not "svc_acct". -NOTE: specifying a DATASRC/USERNAME/PASSWORD instead of a DBI database handle -is not yet implemented. - =cut #note: implementation here, POD in FS::svc_acct sub seconds_since_sqlradacct { - my($self, $start, $end, $dbh) = @_; + my($self, $start, $end) = @_; my $username = $self->svc_x->username; - #select a unix time conversion function based on database type - my $str2time; - if ( $dbh->{Driver}->{Name} eq 'mysql' ) { - $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 @part_export = $self->part_svc->part_export('sqlradius') + or die "no sqlradius export configured for this service type"; + #or return undef; + + my $seconds = 0; + foreach my $part_export ( @part_export ) { + + my $dbh = DBI->connect( map { $part_export->option($_) } + qw(datasrc username password) ) + or die "can't connect to sqlradius database: ". $DBI::errstr; + + #select a unix time conversion function based on database type + my $str2time; + if ( $dbh->{Driver}->{Name} eq 'mysql' ) { + $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 '; + } + + #find closed sessions completely within the given range + my $sth = $dbh->prepare("SELECT SUM(acctsessiontime) + FROM radacct + WHERE UserName = ? + AND $str2time AcctStartTime) >= ? + AND $str2time AcctStopTime ) < ? + AND AcctStopTime =! 0 + AND AcctStopTime IS NOT NULL" + ) or die $dbh->errstr; + $sth->execute($username, $start, $end) or die $sth->errstr; + my $regular = $sth->fetchrow_arrayref->[0]; + + #find open sessions which start in the range, count session start->range end + # don't count them if they are over 1 day old (probably missing stop record) + $sth = $dbh->prepare("SELECT SUM( ? - $str2time AcctStartTime ) ) + FROM radacct + WHERE UserName = ? + AND $str2time AcctStartTime ) >= ? + AND ( ? - $str2time AcctStartTime ) < 86400 + AND ( AcctStopTime = 0 + OR AcctStopTime IS NULL )" + ) or die $dbh->errstr; + $sth->execute($end, $username, $start, $end) or die $sth->errstr; + my $start_during = $sth->fetchrow_arrayref->[0]; + + #find closed sessions which start before the range but stop during, + #count range start->session end + $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime ) - ? ) + FROM radacct + WHERE UserName = ? + AND $str2time AcctStartTime ) < ? + AND $str2time AcctStopTime ) >= ? + AND $str2time AcctStopTime ) < ? + AND AcctStopTime != 0 + AND AcctStopTime IS NOT NULL" + ) or die $dbh->errstr; + $sth->execute($start, $username, $start, $start, $end ) or die $sth->errstr; + my $end_during = $sth->fetchrow_arrayref->[0]; + + #find closed (not anymore - or open) sessions which start before the range + # but stop # after, or are still open, count range start->range end + # don't count open sessions (probably missing stop record) + $sth = $dbh->prepare("SELECT COUNT(*) + FROM radacct + WHERE UserName = ? + 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; + my $entire_range = ($end-$start) * $sth->fetchrow_arrayref->[0]; + + $seconds += $regular + $end_during + $start_during + $entire_range; + } - #find sessions completely within the given range - my $sth = $dbh->prepare("SELECT SUM(acctsessiontime) - FROM radacct - WHERE UserName = ? - AND $str2time AcctStartTime) >= ? - AND $str2time AcctStopTime ) < ? - AND AcctStopTime =! 0 - AND AcctStopTime IS NOT NULL" - ) or die $dbh->errstr; - $sth->execute($username, $start, $end) or die $sth->errstr; - my $regular = $sth->fetchrow_arrayref->[0]; - - #find open sessions which start in the range, count session start->range end - $sth = $dbh->prepare("SELECT SUM( ? - $str2time AcctStartTime ) ) - FROM radacct - WHERE UserName = ? - AND AcctStartTime >= ? - AND ( AcctStopTime = 0 - OR AcctStopTime IS NULL )" - ) or die $dbh->errstr; - $sth->execute($end, $username, $start) or die $sth->errstr; - my $start_during = $sth->fetchrow_arrayref->[0]; - - #find closed sessions which start before the range but stop during, - #count range start->session end - $sth = $dbh->prepare("SELECT SUM( $str2time AcctStopTime ) - ? ) - FROM radacct - WHERE UserName = ? - AND AcctStartTime < ? - AND AcctStopTime >= ? - AND AcctStopTime < ? - AND AcctStopTime != 0 - AND AcctStopTime IS NOT NULL" - ) or die $dbh->errstr; - $sth->execute($start, $username, $start, $start, $end ) or die $sth->errstr; - my $end_during = $sth->fetchrow_arrayref->[0]; - - #find closed or open sessions which start before the range but stop - # after, or are still open, count range start->range end - $sth = $dbh->prepare("SELECT COUNT(*) - FROM radacct - WHERE UserName = ? - AND AcctStartTime < ? - AND ( AcctStopTime >= ? - OR AcctStopTime = 0 - OR AcctStopTime IS NULL )" - ) or die $dbh->errstr; - $sth->execute($username, $start, $end ) or die $sth->errstr; - my $entire_range = ($end-$start) * $sth->fetchrow_arrayref->[0]; + $seconds; - $regular + $end_during + $start_during + $entire_range; } =back =head1 VERSION -$Id: cust_svc.pm,v 1.18 2002-10-12 13:26:45 ivan Exp $ +$Id: cust_svc.pm,v 1.19 2002-10-17 14:16:17 ivan Exp $ =head1 BUGS diff --git a/FS/FS/part_svc.pm b/FS/FS/part_svc.pm index 7c6acdbcd..06c15ed2a 100644 --- a/FS/FS/part_svc.pm +++ b/FS/FS/part_svc.pm @@ -286,22 +286,23 @@ sub all_part_svc_column { qsearch('part_svc_column', { 'svcpart' => $self->svcpart } ); } -=item part_export +=item part_export [ EXPORTTYPE ] + +Returns all exports (see L) for this service, or, if an +export type is specified, only returns exports of the given type. =cut sub part_export { my $self = shift; - map { qsearchs('part_export', { 'exportnum' => $_->exportnum } ) } + my %search; + $search{'exporttype'} = shift if @_; + map { qsearchs('part_export', { 'exportnum' => $_->exportnum, %search } ) } qsearch('export_svc', { 'svcpart' => $self->svcpart } ); } =back -=head1 VERSION - -$Id: part_svc.pm,v 1.14 2002-09-17 09:19:06 ivan Exp $ - =head1 BUGS Delete is unimplemented. @@ -309,7 +310,7 @@ Delete is unimplemented. The list of svc_* tables is hardcoded. When svc_acct_pop is renamed, this should be fixed. -all_part_svc_column and part_export methods should be documented +all_part_svc_column method should be documented =head1 SEE ALSO diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm index 3afee7f64..c41c30602 100644 --- a/FS/FS/svc_acct.pm +++ b/FS/FS/svc_acct.pm @@ -935,16 +935,17 @@ sub seconds_since { $self->cust_svc->seconds_since(@_); } -=item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END DATASRC DB_USERNAME DB_PASSWORD +=item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END Returns the numbers of seconds this account has been online between TIMESTAMP_START (inclusive) and TIMESTAMP_END (exclusive), according to an -external SQL radacct table, such as those generated by ICRADIUS or FreeRADIUS. -Sessions which started in the specified range but are still open are counted -from session start to the end of the range. Also, sessions which end in the -range but started earlier are counted from the start of the range to session -end. Finally, sessions which start before the range but end after (or are -still open) are counted for the entire range. +external SQL radacct table, specified via sqlradius export. Sessions which +started in the specified range but are still open are counted from session +start to the end of the range (unless they are over 1 day old, in which case +they are presumed missing their stop record and not counted). Also, sessions +which end in therange but started earlier are counted from the start of the +range to session end. Finally, sessions which start before the range but end +after are counted for the entire range. TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see L. Also see L and L for conversion diff --git a/httemplate/edit/part_pkg.cgi b/httemplate/edit/part_pkg.cgi index 5fbcd0bd1..187578a35 100755 --- a/httemplate/edit/part_pkg.cgi +++ b/httemplate/edit/part_pkg.cgi @@ -356,7 +356,7 @@ tie my %plans, 'Tie::IxHash', }, 'sqlradacct_hour' => { - 'name' => 'Base charge plus charge per-hour from an external SQL radacct table', + 'name' => 'Base charge plus charge per-hour from an external sqlradius radacct table', 'fields' => { 'setup_fee' => { 'name' => 'Setup fee for this package', 'default' => 0, @@ -364,15 +364,6 @@ tie my %plans, 'Tie::IxHash', 'recur_flat' => { 'name' => 'Base monthly charge for this package', 'default' => 0, }, - 'sql_datasrc' => { 'name' => 'DBI data source', - 'default' => 'DBI:mysql:host=radius.server.name;dbname=radius', - }, - 'sql_username' => { 'name' => 'Database username', - 'default' => 'radius', - }, - 'sql_password' => { 'name' => 'Database password', - 'default' => '', - }, 'recur_included_hours' => { 'name' => 'Hours included', 'default' => 0, }, @@ -382,7 +373,7 @@ tie my %plans, 'Tie::IxHash', }, 'fieldorder' => [ 'setup_fee', 'recur_flat', 'recur_included_hours', 'recur_hourly_charge' ], 'setup' => 'what.setup_fee.value', - 'recur' => '\'my $hours = $cust_pkg->seconds_since_sqlradacct($cust_pkg->last_bill, $sdate, \' + what.sql_datasrc + \', \' + what.sql_username + \', \' + what.sql_password + \' ) / 3600 - \' + what.recur_included_hours.value + \'; $hours = 0 if $hours < 0; \' + what.recur_flat.value + \' + \' + what.recur_hourly_charge.value + \' * $hours;\'', + 'recur' => '\'my $hours = $cust_pkg->seconds_since_sqlradacct($cust_pkg->last_bill, $sdate ) / 3600 - \' + what.recur_included_hours.value + \'; $hours = 0 if $hours < 0; \' + what.recur_flat.value + \' + \' + what.recur_hourly_charge.value + \' * $hours;\'', }, diff --git a/httemplate/view/svc_acct.cgi b/httemplate/view/svc_acct.cgi index e755f87d7..dd242b980 100755 --- a/httemplate/view/svc_acct.cgi +++ b/httemplate/view/svc_acct.cgi @@ -146,21 +146,11 @@ print 'RADIUS groups'. print '

'; -if ( $cust_pkg && $cust_pkg->part_pkg->plan eq 'sqlradacct_hour' ) { - - #false laziness w/httemplate/edit/part_pkg... this stuff doesn't really - #belong in plan data - my %plandata = map { /^(\w+)=(.*)$/; ( $1 => $2 ); } - split("\n", $cust_pkg->part_pkg->plandata ); +#if ( $cust_pkg && $cust_pkg->part_pkg->plan eq 'sqlradacct_hour' ) { +if ( $cust_pkg && $part_svc->part_export('sqlradius') ) { my $last_bill = $cust_pkg->last_bill; - my $seconds = $svc_acct->seconds_since_sqlradacct( - $last_bill, - time, - $plandata{sql_datasrc}, - $plandata{sql_username}, - $plandata{sql_password}, - ); + my $seconds = $svc_acct->seconds_since_sqlradacct( $last_bill, time ); my $h = int($seconds/3600); my $m = int( ($seconds%3600) / 60 ); my $s = $seconds%60; -- 2.11.0