Re: Convert detail->MySQL radacct script

[Ruslan Balkin]

From: Ruslan Balkin <[EMAIL PROTECTED]>
To: "David C. Troy" <[EMAIL PROTECTED]>
Subject: Re: Convert detail->MySQL radacct script
Date: Mon, 26 Aug 2002 12:52:59 +0400
Organization: OOO Dartel
X-Mailer: Sylpheed version 0.8.0 (GTK+ 1.2.10; i586-alt-linux)

Hello again.
Sorry for delay in posting this script.
It accepts detail files on STDIN and outputs SQL to STDOUT.
Sorry David, I send this script to you, not to list, by mistake or
because of broken thread :(

--
Balkin Ruslan

-- CUT --
From: root
To: [EMAIL PROTECTED]
Subject: script
Date: Mon, 26 Aug 2002 12:44:50 +0400 (MSD)

#!/usr/bin/perl
# detailtosql. Converts detail files from STDIN to SQL statements on STDOUT.
# Author: Balkin Ruslan, 2002.

$| = 1; # ���������������� ����/�����
$/=""; #�������� �� ������ ������

while ( $file_record = <STDIN> ) {
  $DIR = $arg{'o'};
  @record = ();
  @record = split(/\n/, $file_record);
  process_record(@record);

  if ( $AcctStatusType eq "Start") 
  {
    $AcctStartTime = $Timestamp;
    $AcctStartDelay = $AcctDelayTime;
    $AcctStartConnectInfo = $ConnectInfo;
    $query="
INSERT INTO radacct ( acctsessionid, username, nasipaddress, nasportid, nasporttype, 
acctstarttime,
    connectinfo_start, servicetype, framedprotocol, framedipaddress, acctstartdelay ) 
VALUES (
    '$AcctSessionId', '$UserName', '$NASIPAddress', '$NASPort', '$NASPortType', 
timestamp($AcctStartTime),
    '$AcctStartConnectInfo', '$ServiceType', '$FramedProtocol', '$FramedIPAddress', 
$AcctStartDelay
);";
    print $query."\n";
  } else
  {
    $AcctStopTime = $Timestamp;
    $AcctStopConnectInfo = $ConnectInfo;
    $AcctStopDelay = $AcctDelayTime;
    $query = "
UPDATE radacct SET 
    acctstoptime=timestamp($AcctStopTime),
    acctsessiontime=$AcctSessionTime,
    connectinfo_stop='$AcctStopConnectInfo',
    acctinputoctets=$AcctInputOctets,
    acctoutputoctets=$AcctOutputOctets,
    acctstopdelay=$AcctStopDelay
WHERE
    acctsessionid='$AcctSessionId' AND username='$UserName' AND nasportid='$NASPort'
;";
    print $query."\n";
    $AcctStartTime=$AcctStopTime-$AcctSessionTime;
    system ("echo /opt/billing/bin/charge-one-session.pl $UserName $AcctStartTime 
$AcctStopTime >>/tmp/coollog");
  }
}

exit;

sub process_record 
{
  my @new_record = @_;

  $AcctSessionId = ""; $UserName = ""; $NASPort=""; $NASPortType="";
  $NASIPAddress = "";
  $AcctStatusType=""; $AcctSessionTime=""; $AcctInputOctets="";
  $AcctOutputOctets=""; $AcctTerminateCause=""; $ServiceType="";
  $FramedProtocol=""; $FramedIPAddress=""; $Timestamp=""; $AcctDelayTime="";
  $ConnectInfo=""; $REC=""; $VALUE="";
  foreach (@new_record) 
  {
    s/^\s+//;
    chomp;
    ($REC, $VALUE) = split(" = ", $_, 2);
    next if ( ! $VALUE );
    $REC =~ s/-//g;
    ${$REC} = $VALUE;
  }
  $UserName =~ s/\"//g;
  $AcctSessionId =~ s/\"//g;
  $ConnectInfo =~ s/\"//g;
  $Timestamp -= $AcctDelayTime;
  $AcctDelayTime = 0;
  if ($NASPort eq '') { $NASPort = '0'; }
}
-- CUT --

- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html