[freeside] session_history.cgi
troyh at netsignia.net
troyh at netsignia.net
Thu Sep 4 22:14:08 PDT 2003
Ivan,
I think this takes care of everything that you sugested
################# svc_acct.pm #################
=item get_session_history TIMESTAMP_START TIMESTAMP_END
=cut
sub get_session_history {
my $self = shift;
$self->cust_svc->get_session_history(@_);
}
#####################################
############# cust_svc.pm ##################
=item get_session_history TIMESTAMP_START TIMESTAMP_END
=cut
sub get_session_history {
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 %sessions;
my $acctstarttime;
my $acctstoptime;
my $acctsessiontime;
my $acctinputoctets;
my $acctoutputoctets;
my $framedipaddress;
my $count = 10;
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 acctstarttime, acctstoptime,
acctsessiontime, acctinputoctets, acctoutputoctets, framedipaddress
FROM radacct
WHERE UserName = ?
AND $str2time AcctStopTime ) >= ?
AND $str2time AcctStopTime ) <= ?
ORDER BY AcctStartTime DESC
") or die $dbh->errstr;
$sth->execute($username, $start, $end) or die $sth->errstr;
while (($acctstarttime, $acctstoptime,
$acctsessiontime, $acctinputoctets, $acctoutputoctets,
$framedipaddress) = $sth->fetchrow_array) {
$sessions{ $count }{ 'acctstarttime' } =
$acctstarttime;
$sessions{ $count }{ 'acctstoptime' } =
$acctstoptime;
$sessions{ $count }{ 'acctsessiontime' } =
$acctsessiontime;
$sessions{ $count }{ 'acctinputoctets' } =
$acctinputoctets;
$sessions{ $count }{ 'acctoutputoctets' } =
$acctoutputoctets;
$sessions{ $count }{ 'framedipaddress' } =
$framedipaddress;
$count++
}
}
return \%sessions;
}
#######################################################
######### misc/session_history.cgi #####################
<%
my $conf = new FS::Conf;
my $mydomain = $conf->config('domain');
my($query) = $cgi->keywords;
$query =~ /^(\d+)$/;
my $svcnum = $1;
@MONTHS = ( 'January','February','March','April',
'May','June','July','August',
'September','October','November','December' );
@months = ( '01','02','03','04','05','06',
'07','08','09','10','11','12' );
@days = ( '01','02','03','04','05','06',
'07','08','09','10','11','12',
'13','14','15','16','17','18',
'19','20','21','22','23','24',
'25','26','27','28','29','30',
'31' );
( $s,$m,$h,$mday,$mon,$year,$wday,$yday,$isdst ) = localtime( time );
$mon++;
if( $mday < 10 ) { $mday = "0$mday" }
if( $mon < 10 ) { $mon = "0$mon" }
%>
<%
print "
<TABLE border=2>
<FORM ACTION=\"process/session_history.cgi?$svcnum\"
METHOD=\"post\">
<INPUT TYPE=\"hidden\" NAME=\"svcnum\" VALUE=\"$svcnum\">
<TR>
<TD colspan=2><H1>Select Date Range</H1></TD>
</TR>
<TR>
<TD><B>From:</TD>
<TD>
<SELECT NAME=\"startmon\">
";
$count = 0;
foreach $month (@months) {
if ($month eq $mon) {
print "<OPTION VALUE=\"$month\" SELECTED>$MONTHS[
$count ]</OPTION>";
} else {
print "<OPTION VALUE=\"$month\">$MONTHS[ $count ]
</OPTION>";
}
$count++;
}
print "
</SELECT>
<SELECT NAME=\"startday\">
";
foreach $dayslist (@days) {
print "<OPTION VALUE=\"$dayslist\">$dayslist</OPTION>";
}
print "
</SELECT>
<SELECT NAME=\"startyear\">
<OPTION VALUE=\"2003\">2003</OPTION>
</SELECT>
</TD>
</TR>
<TR>
<TD><B>To:</TD>
<TD>
<SELECT NAME=\"endmon\">
";
$count = 0;
foreach $month (@months) {
if ($month eq $mon) {
print "<OPTION VALUE=\"$month\" SELECTED>$MONTHS[
$count ]</OPTION>";
} else {
print "<OPTION VALUE=\"$month\">$MONTHS[ $count ]
</OPTION>";
}
$count++;
}
print "
</SELECT>
<SELECT NAME=\"endday\">
";
foreach $dayslist (@days) {
if ($dayslist eq $mday) {
print "<OPTION VALUE=\"$dayslist\"
SELECTED>$dayslist</OPTION>";
} else {
print "<OPTION VALUE=\"$dayslist\">$dayslist</OPTION>";
}
}
print "
</SELECT>
<SELECT NAME=\"endyear\">
<OPTION VALUE=\"2003\">2003</OPTION>
</SELECT>
</TD>
</TR>
<TR>
<TD colspan=2 align=\"center\"><INPUT TYPE=\"submit\"
VALUE=\"Get Report\"></TD>
</TR>
</FORM>
</TABLE>
";
%>
####################################################
############## misc/process/session_history.cgi ##############
<%
my $conf = new FS::Conf;
my $mydomain = $conf->config('domain');
my($query) = $cgi->keywords;
$query =~ /^(\d+)$/;
my $svcnum = $cgi->param('svcnum');
my $svc_acct = qsearchs('svc_acct',{'svcnum'=>$svcnum});
die "Unknown svcnum $svcnum" unless $svc_acct;
#false laziness w/all svc_*.cgi
my $cust_svc = qsearchs( 'cust_svc' , { 'svcnum' => $svcnum } );
my $pkgnum = $cust_svc->getfield('pkgnum');
my($cust_pkg, $custnum);
if ($pkgnum) {
$cust_pkg = qsearchs( 'cust_pkg', { 'pkgnum' => $pkgnum } );
$custnum = $cust_pkg->custnum;
} else {
$cust_pkg = '';
$custnum = '';
}
#eofalse
my $part_svc = qsearchs('part_svc',{'svcpart'=> $cust_svc->svcpart } );
die "Unknown svcpart" unless $part_svc;
my $username = $svc_acct->username;
%>
<%= header('Session History', menubar(
"View this package (#$pkgnum)" => "/view/cust_pkg.cgi?$pkgnum",
"View this customer (#$custnum)" => "/view/cust_main.cgi?$custnum",
"Main menu" => "/"
)) %>
<%
my $startmon = $cgi->param('startmon');
my $startday = $cgi->param('startday');
my $startyear = $cgi->param('startyear');
my $session_start_date = str2time("$startmon/$startday/$startyear");
my $endmon = $cgi->param('endmon');
my $endday = $cgi->param('endday');
my $endyear = $cgi->param('endyear');
my $session_end_date = str2time("$endmon/$endday/$endyear 23:59");
my $seconds = $svc_acct->seconds_since_sqlradacct( $session_start_date,
$session_end_date );
my $h = int($seconds/3600);
my $m = int( ($seconds%3600) / 60 );
my $s = $seconds%60;
if( $h < 10 ) { $h = "0$h" }
if( $m < 10 ) { $m = "0$m" }
if( $s < 10 ) { $s = "0$s" }
my $sessions = $svc_acct->get_session_history
($session_start_date,$session_end_date);
print "<table border=\"1\" width=\"100%\" align=\"center\"";
print "<TR> <TD><B>Logged On</TD> <TD><B>Logged Off</TD>
<TD><B>Time</TD> <TD><B>IP Address</TD> <TD><B>Bytes Sent</TD>
<TD><B>Bytes Received</TD> </TR>";
print "<TR> <TD><B>Totals for: $username</TD> <TD><B></TD>
<TD><B>$h:$m:$s</TD> <TD><B></TD> <TD><B></TD> <TD><B></TD> </TR>";
for $key ( sort keys %$sessions ) {
my $acctstarttime = "$sessions->{$key}->{'acctstarttime'}";
my $acctstoptime = "$sessions->{$key}->{'acctstoptime'}";
my $acctsessiontime = "$sessions->{$key}->{'acctsessiontime'}";
my $acctinputoctets = "$sessions->{$key}->{'acctinputoctets'}";
my $acctoutputoctets = "$sessions->{$key}->{'acctoutputoctets'}";
my $framedipaddress = "$sessions->{$key}->{'framedipaddress'}";
my $bytesin = $acctinputoctets/16;
my $bytesout = $acctoutputoctets/16;
my $h = int($acctsessiontime/3600);
my $m = int( ($acctsessiontime%3600) / 60 );
my $s = $acctsessiontime%60;
if( $h < 10 ) { $h = "0$h" }
if( $m < 10 ) { $m = "0$m" }
if( $s < 10 ) { $s = "0$s" }
print "<TR> <TD>$acctstarttime</TD> <TD>$acctstoptime</TD>
<TD>$h:$m:$s</TD> <TD>$framedipaddress</TD> <TD>$bytesin</TD>
<TD>$bytesout</TD></TR>";
}
print "</table>";
%>
##########################################################
--
Troy Hammonds
Netsignia Online
troyh at netsignia.net
More information about the freeside-users
mailing list