#!/usr/bin/perl

use DBI;

# ------------------------------------------------------------
# Configurable variables

# The default name of the detail file to summarize
# If you define $DBSource, the file wil be ignored, and the data will come
# from an SQL database
# IF filename has a .gz extension it wil be decompressed with
# $gzip_prog
$filename = 'arbo_unie.2000.02.acct.gz';

# If you define these, we get the data from SQL, rather than a 
# flat file
# The config of this is still pretty rough and ready.
# You will probably need to tune the table name and queries to 
# suit your database. What we present here will suit the simple
# tables that are created by the sample schemas in the goodies 
# directory.

# Example mySQL config
$DBSource = 'dbi:mysql:radmin';
$DBUsername = 'root';
$DBAuth = '';
$DBTableName = 'RADUSAGE';  # Name of the accounting table 

# The name of the gzip program that will be used to unzip compressed
# detail files (ones with .gz extension)
$gzip_prog = '/bin/gzip -dc';

# End of Configurable variables
# ------------------------------------------------------------

# open connection to db
$dbh = DBI->connect($DBSource,
            $DBUsername,
            $DBAuth);
die "Could not DBI->connect to $DBSource: $DBI::errstr"
    if !$dbh;                                                                   

$/ = '';           # Read 1 paragraph at a time

&read_file;

exit 0;

###############################################################
# Read the detail file, and accumulate or print details
sub read_file
{
    # Use gzip if its gzipped
    my $f = $filename;
    $f =~ s/(.*\.gz)\s*$/$gzip_prog $1|/;

    open(FILE, $f)
	|| &fatalError("Could not open detail file '$filename': $!<br>Perhaps you should alter \$filename in the script or user the filename tag");
    while (<FILE>)
    {
#print ".";
	$User_Name = undef;
#	($Ddd, $Mmm, $Dd, $Time, $Yyyy) =
	    /^(\w+)\s+(\w+)\s+(\d+)\s+([\d:]+)\s+(\d+)/;
	($Acct_Session_Id)      = /Acct-Session-Id = "([^"]+)"/;
	($Acct_Session_Time)    = /Acct-Session-Time = (\d+)/;     # Stop only
	($Acct_Terminate_Cause) = /Ascend-Disconnect-Cause = (\d+)/;  # Stop only
	($Acct_Delay_Time)      = /Acct-Delay-Time = (\d+)/;       # Stop only
	($Acct_Status_Type)     = /Acct-Status-Type = (\w+)/;      # Start/Stop
	($Client_Port_Id)       = /NAS-Port = (\d+)/;
	($Nas_Identifier)       = /NAS-Identifier = "([^"]*)"/;

	($Framed_IP_Address)    = /Framed-IP-Address = ([\d.]+)/;
	($User_Name)            = /User-Name = \"([^"]+)\"/;
#	($User_Name)            = /User-Name = (\S+)/ if !$User_Name;
	# Ascend only.
	
	($InOctets)             = /Acct-Input-Octets = (\d+)/;
	($OutOctets)            = /Acct-Output-Octets = (\d+)/;
	($InPackets)            = /Acct-Input-Packets = (\d+)/;
	($OutPackets)           = /Acct-Output-Packets = (\d+)/;
	($Called_Id)            = /Called-Station-Id = "([^"]*)"/;
	($Caller_Id)            = /Calling-Station-Id = "([^"]*)"/;

	($Nas_Port_Type)		= /NAS-Port-Type = (\w+)/;
	($Timestamp)			= /Timestamp = (\d+)/;
					      
    next if $Acct_Status_Type ne "Stop";
	$User_Name .= "\@arbounie.nl" if $User_Name !~ /.*arbounie\.nl$/;

    my $q = "insert into RADUSAGE
(ACCTDELAYTIME,ACCTINPUTOCTETS,ACCTOUTPUTOCTETS,ACCTSESSIONID,ACCTSESSIONTIME,ACCTSTATUSTYPE,ACCTTERMINATECAUSE,DNIS,FRAMEDIPADDRESS,NASIDENTIFIER,NASPORT,TIME_STAMP,USERNAME) values ('$Acct_Delay_Time','$InOctets','$OutOctets','$Acct_Session_Id','$Acct_Session_Time','$Acct_Status_Type','$Acct_Terminate_Cause','$Caller_Id','$Framed_IP_Address','$Nas_Identifier','$Client_Port_Id','$Timestamp','$User_Name')";

#    print $q,"\n";
    
    if (!$dbh->do($q))
    {
#    if ($opt_u)
    if (0)
    {
        # The insert failed, try an update
        $q = "update $DBTablename set
$DBPasswordColumn = '$password' $extraclauses
where $DBUsernameColumn = '$username'";
        print "$q\n" if $opt_v;
        $dbh->do($q)
        || print STDERR "Update user $username failed: $DBI::errstr\n";
    }
    else
    {
        print STDERR "Insert user $username failed: $DBI::errstr\n";
last;
    }                                                   
	}
	}

}

###############################################################
# Format a time period in seconds into hh:mm:ss
sub formatDuration
{
    my ($duration) = @_;
    return sprintf('%d:%02d:%02d', 
		   int($duration / 3600), 
		   int(($duration / 60) % 60), 
		   $duration % 60);
}

#####################################################################
# Convenience function to prepare and execute a query.
# If it fails to execute, return undef, else a statement handle
sub prepareAndExecute
{
    my ($dbh, $q) = @_;

    my $sth = $dbh->prepare($q);
    if (!$sth)
    {
	&fatalError("Prepare failed for '$q': $DBI::errstr");
	return undef;
    }
    my $rc = $sth->execute;
    if (!$rc)
    {
	&fatalError("Execute failed for '$q': $DBI::errstr");
	return undef;
    }
    return $sth;
}