my $seconds = 0;
foreach my $cust_svc (
- grep { $_->part_svc->svcdb eq 'svc_acct' } $self->cust_svc
+ grep {
+ my $part_svc = $_->part_svc;
+ $part_svc->svcdb eq 'svc_acct'
+ && scalar($part_svc->part_export('sqlradius'));
+ } $self->cust_svc
) {
$seconds += $cust_svc->seconds_since_sqlradacct($start, $end);
}
}
+=item attribute_since_sqlradacct TIMESTAMP_START TIMESTAMP_END ATTRIBUTE
+
+Returns the sum of the given attribute for all accounts (see L<FS::svc_acct>)
+in this package for sessions ending between TIMESTAMP_START (inclusive) and
+TIMESTAMP_END
+(exclusive).
+
+TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see
+L<perlfunc/"time">. Also see L<Time::Local> and L<Date::Parse> for conversion
+functions.
+
+=cut
+
+sub attribute_since_sqlradacct {
+ my($self, $start, $end, $attrib) = @_;
+
+ my $sum = 0;
+
+ foreach my $cust_svc (
+ grep {
+ my $part_svc = $_->part_svc;
+ $part_svc->svcdb eq 'svc_acct'
+ && scalar($part_svc->part_export('sqlradius'));
+ } $self->cust_svc
+ ) {
+ $sum += $cust_svc->attribute_since_sqlradacct($start, $end, $attrib);
+ }
+
+ $sum;
+
+}
+
=back
=head1 SUBROUTINES
=back
-=head1 VERSION
-
-$Id: cust_pkg.pm,v 1.27 2002-10-17 14:16:17 ivan Exp $
-
=head1 BUGS
sub order is not OO. Perhaps it should be moved to FS::cust_main and made so?
Also in sub order, no money is adjusted. Once FS::part_pkg defines a standard
method to pass dates to the recur_prog expression, it should do so.
-FS::svc_acct, FS::svc_domain, FS::svc_www and FS::svc_forward are loaded via
-'use' at compile time, rather than via 'require' in sub
-{ setup, suspend, unsuspend, cancel } because they use %FS::UID::callback to
-load configuration values. Probably need a subroutine which decides what to
-do based on whether or not we've fetched the user yet, rather than a hash.
-See FS::UID and the TODO.
+FS::svc_acct, FS::svc_domain, FS::svc_www, FS::svc_ip and FS::svc_forward are
+loaded via 'use' at compile time, rather than via 'require' in sub { setup,
+suspend, unsuspend, cancel } because they use %FS::UID::callback to load
+configuration values. Probably need a subroutine which decides what to do
+based on whether or not we've fetched the user yet, rather than a hash. See
+FS::UID and the TODO.
Now that things are transactional should the check in the insert method be
moved to check ?
$sth->fetchrow_arrayref->[0];
}
-=item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END
+=item seconds_since_sqlradacct TIMESTAMP_START TIMESTAMP_END
See L<FS::svc_acct/seconds_since_sqlradacct>. Equivalent to
-$cust_svc->svc_x->seconds_since, but more efficient. Meaningless for records
-where B<svcdb> is not "svc_acct".
+$cust_svc->svc_x->seconds_since_sqlradacct, but more efficient. Meaningless
+for records where B<svcdb> is not "svc_acct".
=cut
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' ) {
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
+ # 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 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;
}
}
+=item attribute_since_sqlradacct TIMESTAMP_START TIMESTAMP_END ATTRIBUTE
+
+See L<FS::svc_acct/attribute_since_sqlradacct>. Equivalent to
+$cust_svc->svc_x->attribute_since_sqlradacct, but more efficient. Meaningless
+for records where B<svcdb> is not "svc_acct".
+
+=cut
+
+#note: implementation here, POD in FS::svc_acct
+#(false laziness w/seconds_since_sqlradacct above)
+sub attribute_since_sqlradacct {
+ my($self, $start, $end, $attrib) = @_;
+
+ my $username = $self->svc_x->username;
+
+ my @part_export = $self->part_svc->part_export('sqlradius')
+ or die "no sqlradius export configured for this service type";
+ #or return undef;
+
+ my $sum = 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 ';
+ }
+
+ my $sth = $dbh->prepare("SELECT SUM(?)
+ FROM radacct
+ WHERE UserName = ?
+ AND $str2time AcctStopTime ) >= ?
+ AND $str2time AcctStopTime ) < ?
+ AND AcctStopTime IS NOT NULL"
+ ) or die $dbh->errstr;
+ $sth->execute($attrib, $username, $start, $end) or die $sth->errstr;
+
+ $sum += $sth->fetchrow_arrayref->[0];
+
+ }
+
+ $sum;
+
+}
+
=back
=head1 BUGS
$self->cust_svc->seconds_since_sqlradacct(@_);
}
+=item attribute_since_sqlradacct TIMESTAMP_START TIMESTAMP_END ATTRIBUTE
+
+Returns the sum of the given attribute for all accounts (see L<FS::svc_acct>)
+in this package for sessions ending between TIMESTAMP_START (inclusive) and
+TIMESTAMP_END (exclusive).
+
+TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see
+L<perlfunc/"time">. Also see L<Time::Local> and L<Date::Parse> for conversion
+functions.
+
+=cut
+
+#note: POD here, implementation in FS::cust_svc
+sub attribute_since_sqlradacct {
+ my $self = shift;
+ $self->cust_svc->attribute_since_sqlradacct(@_);
+}
+
=item radius_groups
Returns all RADIUS groups for this account (see L<FS::radius_usergroup>).
},
'sqlradacct_hour' => {
- 'name' => 'Base charge plus charge per-hour from an external sqlradius radacct table',
+ 'name' => 'Base charge plus charge per-hour (and for data) from an external sqlradius radacct table',
'fields' => {
'setup_fee' => { 'name' => 'Setup fee for this package',
'default' => 0,
'recur_hourly_charge' => { 'name' => 'Additional charge per hour',
'default' => 0,
},
+ 'recur_included_input' => { 'name' => 'Input megabytes included',
+ 'default' => 0,
+ },
+ 'recur_input_charge' => { 'name' =>
+ 'Additional charge per input megabyte',
+ 'default' => 0,
+ },
+ 'recur_included_output' => { 'name' => 'Output megabytes included',
+ 'default' => 0,
+ },
+ 'recur_output_charge' => { 'name' =>
+ 'Additional charge per output megabyte',
+ 'default' => 0,
+ },
+ 'recur_included_total' => { 'name' =>
+ 'Total input+output megabytes included',
+ 'default' => 0,
+ },
+ 'recur_total_charge' => { 'name' =>
+ 'Additional charge per input+output megabyte',
+ 'default' => 0,
+ },
},
- 'fieldorder' => [ 'setup_fee', 'recur_flat', 'recur_included_hours', 'recur_hourly_charge' ],
+ 'fieldorder' => [qw( setup_fee recur_flat recur_included_hours recur_hourly_charge recur_included_input recur_input_charge recur_included_input recur_output_charge recur_included_total recur_total_charge )],
'setup' => 'what.setup_fee.value',
- '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;\'',
+ 'recur' => '\'my $last_bill = $cust_pkg->last_bill; my $hours = $cust_pkg->seconds_since_sqlradacct($last_bill, $sdate ) / 3600 - \' + what.recur_included_hours.value + \'; $hours = 0 if $hours < 0; my $input = $cust_pkg->attribute_since_sqlradacct($last_bill, $sdate, "Acct-Input-Octets" ) / 1048576; my $output = $cust_pkg->attribute_since_sqlradacct($last_bill, $sdate, "Acct-Output-Octets" ) / 1048576; my $total = $input + $output - \' + what.recur_included_total.value + \'; $total = 0 if $total < 0; my $input = $input - \' + what.recur_included_input.value + \'; $input = 0 if $input < 0; my $output = $output - \' + what.recur_included_output.value + \'; $output = 0 if $output < 0; \' + what.recur_flat.value + \' + \' + what.recur_hourly_charge.value + \' * $hours + \' + what.recur_input_charge + \' * $input + \' + what.recur_output_charge + \' * $output + \' + what.recur_total_charge + \' * $total ;\'',
},
-
;
my %plandata = map { /^(\w+)=(.*)$/; ( $1 => $2 ); }
my $m = int( ($seconds%3600) / 60 );
my $s = $seconds%60;
+ my $input = $svc_acct->attribute_since_sqlradacct(
+ $last_bill, time, 'Acct-Input-Octets'
+ ) / 1048576;
+ my $output = $svc_acct->attribute_since_sqlradacct(
+ $last_bill, time, 'Acct-Output-Octets'
+ ) / 1048576;
+
if ( $seconds ) {
print "Online <B>$h</B>h <B>$m</B>m <B>$s</B>s";
} else {
if ( $cust_pkg ) {
print ' this billing cycle (since '. time2str("%C", $last_bill). ') - '.
- $plandata{recur_included_hours}. ' total hours in plan<BR><BR>';
+ $plandata{recur_included_hours}. ' total hours in plan<BR>';
} else {
- print ' (no billing cycle available for unaudited account)<BR><BR>';
+ print ' (no billing cycle available for unaudited account)<BR>';
}
+ print 'Input: <B>'. sprintf("%.3f", $input). ' megabytes<BR>';
+ print 'Output: <B>'. sprintf("%.3f", $output). ' megabytes<BR>';
+
+ print '<BR>';
+
}
#print qq!<BR><A HREF="../misc/sendconfig.cgi?$svcnum">Send account information</A>!;
print '</TABLE></TD></TR></TABLE><BR><BR>';
-print join("\n", $conf->config('svc_acct-notes') ). '<BR><BR>'.
+print join("\n", $conf->config('svc_acct-notes') ). '<BR><BR>'.
joblisting({'svcnum'=>$svcnum}, 1). '</BODY></HTML>';
%>
projects / freeside.git / commitdiff