session_history.cgi

[troyh at netsignia.net]

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?

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();
}

%>