[freeside] session_history.cgi
ivan at 420.am
ivan at 420.am
Wed Sep 3 04:27:57 PDT 2003
On Sun, Aug 31, 2003 at 11:00:52PM -0400, troyh at netsignia.net wrote:
> I am including a script called session_history.cgi that shows the session
> history from a sqlradius service. I cant figure out how to get the dbh
> config from the sqlradius export so I hard coaded it in. Can someone help
> me our or just point me int he direction on how to do this?
You can have zero, one, or many sqlradius exports - you can't assume
just one. Your code needs to take this into account (probably by
listing the logins for each export in a separate table).
See FS::cust_svc::seconds_since_sqlradacct for an example:
my @part_export = $svc_acct->cust_svc->part_svc->part_export('sqlradius')
or die "no sqlradius export configured for this service type";
foreach my $part_export ( @part_export ) {
my $sqlradius_dbh = DBI->connect( map { $part_export->option($_) }
qw(datasrc username password) )
or die "can't connect to sqlradius database: ". $DBI::errstr;
}
In addition, please address the following issues:
1. Instead of coding this all in one file using:
if ( $cgi->param('magic') && $cgi->param('magic') eq '_date' ) {
get_session_history();
} else {
datemenu();
}
Split the "datemenu" and "get_session_history" into their own files,
perhaps misc/session_history.cgi and misc/process/session_history.cgi
2. You include an optional "cancel this unaudited account" link lifted
from the Account View page, but don't include the corresponding
JavaScript. I think you should include a link to the Account View page
itself instead (under all circumstances), and forgo the optional "cancel
this unaudited account" link entirely.
3. Move all the code that gets the session data from the database out of
the .cgi file here and into proper methods in FS::cust_svc, like
FS::cust_svc::seconds_since_sqlradacct. The .cgi should call methods on
objects, not touch the database directly.
I look forward to applying an updated patch with the above
modifications. Thanks.
--
_ivan
>
> Thanks,
> Troy Hammonds
>
> <%
>
> 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" }
> %>
>
> <%
> sub datemenu {
>
> print "
> <TABLE border=2>
> <FORM ACTION=\"session_history.cgi?$svcnum\" METHOD=\"post\">
> <INPUT TYPE=\"hidden\" NAME=\"magic\" VALUE=\"_date\">
> <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>
>
> ";
> }
> %>
>
>
> <%
>
> sub get_session_history {
>
> 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(
> ( ( $pkgnum || $custnum )
> ? ( "View this package (#$pkgnum)" => "${p}view/cust_pkg.cgi?$pkgnum",
> "View this customer (#$custnum)" =>
> "${p}view/cust_main.cgi?$custnum",
> )
> : ( "Cancel this (unaudited) account" =>
> "javascript:areyousure(\'${p}misc/cancel-unaudited.cgi?$svcnum\')"
> )
> ),
> "Main menu" => $p,
> )) %>
>
> <%
>
> #I know there is a way to get the global conf but I cant figure it out
> my $dbh = DBI->connect("DBI:Pg:dbname=radius;host=db.hostname.net",
> "user",
> "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 ';
> }
>
>
> #get a list of all accounting info
> my $sth = $dbh->prepare("
> SELECT acctstarttime, acctstoptime, acctsessiontime,
> acctinputoctets,
> acctoutputoctets, framedipaddress
> FROM radacct
> WHERE username = ?
> AND $str2time AcctStartTime) >= ?
> AND $str2time AcctStopTime) <= ?
> ORDER BY AcctStartTime DESC
> ") || die $dbh->errstr;
>
> 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");
>
> $sth->execute($username, $session_start_date, $session_end_date) or die
> $sth->errstr;
>
> 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>";
>
> while (($acctstarttime, $acctstoptime, $acctsessiontime, $acctinputoctets,
> $acctoutputoctets, $framedipaddress) = $sth->fetchrow_array) {
>
> my $bytesin = $acctinputoctets/16;
> my $bytesout = $acctoutputoctets/16;
>
> print "<TR> <TD>$acctstarttime</TD> <TD>$acctstoptime</TD>
> <TD>$acctsessiontime</TD>
> <TD>$framedipaddress</TD> <TD>$bytesin</TD> <TD>$bytesout</TD></TR>";
>
> }
> print "</table>";
>
> }
> %>
>
> <%
>
> if ( $cgi->param('magic') && $cgi->param('magic') eq '_date' ) {
> get_session_history();
> } else {
> datemenu();
> }
>
> %>
>
>
--
_ivan
More information about the freeside-users
mailing list