path: root/bin/aradial-sftp_and_import

#!/usr/bin/perl -w

#i'm kinda like freeside-cdr-sftp_and_import... some parts should be libraried

use strict;
use Getopt::Std;
use Date::Parse;
use Date::Format;
use Text::CSV_XS;
use DBI qw( :sql_types );
use Net::SFTP::Foreign;
#use FS::UID qw( adminsuidsetup datasrc );

#adjusted these for what we're actually seeing in the real log files
our %aradial2db = (
  #'Date' => '',
  #'NASIP' => 'NASIPAddress',
  'NASID' => 'NASIPAddress',
  'AcctSessionId' => 'AcctSessionId',
  'Port' => 'NasPortId',
  #'Status-Type' => 'Acct-Status-Type',
  #'UserID' => 'UserName',
  'User ID' => 'UserName',
  'Authentic' => 'AcctAuthentic',
  'Service-Type' => 'ServiceType',
  'FramedProtocol' => 'FramedProtocol',
  #'FramedCompression' => '', #not handled, needed?  unlikely
  'FramedAddress' => 'FramedIPAddress',
  'Acct-Delay-Time' => 'AcctStartDelay', #?
  'Session-Time' => 'AcctSessionTime',
  #'Input-Gigawords' => '', #XXX handle lots of data
  'Input-Octets' => 'AcctInputOctets',
  #'Output-Gigawords' => '', #XXX handle lots of data
  'Output-Octets' => 'AcctOutputOctets',
  'NAS-Port-Type' => 'NASPortType',
  'Acct-Terminate-Cause' => 'AcctTerminateCause',
);

our %bind_type = (
  'AcctInputOctets'  => SQL_INTEGER,
  'AcctOutputOctets' => SQL_INTEGER,
  'AcctSessionTime'  => SQL_INTEGER,
  'AcctStartDelay'   => SQL_INTEGER,
  'AcctStopDelay'    => SQL_INTEGER,
);

#http://www.iana.org/assignments/radius-types/radius-types.xhtml#radius-types-10
our %status_type = (
   1 => 'Start',
   2 => 'Stop',
   3 => 'Interim-Update',
  #4-6,'Unassigned',
   7 => 'Accounting-On',
   8 => 'Accounting-Off',
   9 => 'Tunnel-Start',
  10 => 'Tunnel-Stop',
  11 => 'Tunnel-Reject',
  12 => 'Tunnel-Link-Start',
  13 => 'Tunnel-Link-Stop',
  14 => 'Tunnel-Link-Reject',
  15 => 'Failed',
);

###
# parse command line
###

use vars qw( $opt_m $opt_a $opt_b $opt_r $opt_d $opt_v $opt_P );
getopts('m:abr:d:P:v:');

my %options = ();

my $user = shift or die &usage;
#adminsuidsetup $user;

# %%%FREESIDE_CACHE%%% & hardcoded datasrc
#my $cachedir = '%%%FREESIDE_CACHE%%%/cache.'. datasrc. '/cdrs';
my $cachedir = '/usr/local/etc/freeside/cache.DBI:Pg:dbname=freeside/cdrs';
mkdir $cachedir unless -d $cachedir;

my $servername = shift or die &usage;

my( $datasrc, $db_user, $db_pass ) = ( shift, shift, shift );
my $dbh = DBI->connect( $datasrc, $db_user, $db_pass)
  or die "can't connect: $DBI::errstr\n";

my $csv = Text::CSV_XS->new;

###
# get the file list
###

warn "Retrieving directory listing\n" if $opt_v;

$opt_m = 'sftp' if !defined($opt_m);
$opt_m = lc($opt_m);

my $ls;

if($opt_m eq 'ftp') {
  $options{'Port'}    = $opt_P if $opt_P;
  $options{'Debug'}   = $opt_v if $opt_v;
  $options{'Passive'} = $opt_a if $opt_a;

  my $ls_ftp = ftp();

  $ls = [ grep { /^.*$/i } $ls_ftp->ls ];
}
elsif($opt_m eq 'sftp') {
  $options{'port'}    = $opt_P if $opt_P;
  $options{'debug'}   = $opt_v if $opt_v;

  my $ls_sftp = sftp();

  $ls_sftp->setcwd($opt_r) or die "can't chdir to $opt_r\n"
    if $opt_r;

  $ls = $ls_sftp->ls('.', no_wanted  => qr/^\.+$/,
                          names_only => 1 );
}
else {
  die "Method '$opt_m' not supported; must be ftp or sftp\n";
}

###
# import each file
###

foreach my $filename ( @$ls ) {

  next if $opt_d && $filename eq $opt_d;

  warn "Downloading $filename\n" if $opt_v;

  #get the file
  if($opt_m eq 'ftp') {
    my $ftp = ftp();
    $ftp->get($filename, "$cachedir/$filename")
      or die "Can't get $filename: ". $ftp->message . "\n";
  }
  else {
    my $sftp = sftp();
    $sftp->get($filename, "$cachedir/$filename")
      or die "Can't get $filename: ". $sftp->error . "\n";
  }

  warn "Processing $filename\n" if $opt_v;
 
  open my $fh, "$cachedir/$filename" or die "$cachedir/$filename: $!";
  my $header = $csv->getline($fh);

  while ( my $row = $csv->getline($fh) ) {

    my $i = 0;
    my %hash = map { $_ => $row->[$i++] } @$header;

    my %dbhash = map { $aradial2db{$_} => $hash{$_} }
                   grep $aradial2db{$_},
                     keys %hash;

    my @keys = keys %dbhash;

    #skip blank records
    next unless grep defined($_), values %dbhash;

    my $date = time2str( '%Y-%m-%d %X', str2time( $hash{'Date'} ) );

    $hash{'Status-Type'} = $status_type{ $hash{'Status-Type'} }
      if exists $status_type{ $hash{'Status-Type'} };

    my $sql;
    my @extra_values = ();
    if ( $hash{'Status-Type'} eq 'Start' ) {

      push @keys, 'AcctStartTime';
      $dbhash{'AcctStartTime'} = $date;

      $sql = 'INSERT INTO radacct ( '. join(',', @keys).
             ' ) VALUES ( '. join(',', map ' ? ', @keys ). ' )';

    } elsif ( $hash{'Status-Type'} eq 'Stop' ) {

      my $AcctSessionId = delete($dbhash{AcctSessionId});

      push @keys, 'AcctStopTime';
      $dbhash{'AcctStopTime'} = $date;

      push @extra_values, $AcctSessionId;

      $sql = 'UPDATE radacct SET '. join(',', map "$_ = ?", @keys ).
             ' WHERE AcctSessionId = ? ';

    } elsif ( $hash{'Status-Type'} eq 'Interim' ) {
      #not handled, but stop should capture the usage.  unless session are
      # normally super-long, extending across month boundaries, or we need
      # real-time-ish data usage detail, it isn't a big deal
    } else {
      warn 'Unknown Status-Type '. $hash{'Status-Type'}. "; skipping\n";
      next;
    }

    my $sth = $dbh->prepare($sql) or die $dbh->errstr;

    my $p_num = 1;
    foreach my $value ( map $dbhash{$_}, @keys ) {
      my $key = shift @keys;
      my $type = exists($bind_type{$key}) ? $bind_type{$key} : SQL_VARCHAR;
      $value ||= 0 if $type == SQL_INTEGER;
      $sth->bind_param($p_num++, $value, $type);
    }
    foreach my $value ( @extra_values ) {
      $sth->bind_param($p_num++, $value);
    }

    $sth->execute or die $sth->errstr;

  }
  
  if ( $opt_d ) {
    my $file_timestamp = $filename.'-'.time2str('%Y-%m-%d', time);
    if ( $opt_m eq 'ftp') {
      my $ftp = ftp();
      $ftp->rename($filename, "$opt_d/$file_timestamp")
        or do {
          unlink "$cachedir/$filename";
          die "Can't move $filename to $opt_d: ".$ftp->message . "\n";
        };
    } else {
      my $sftp = sftp();
      $sftp->rename($filename, "$opt_d/$file_timestamp")
        or do {
          unlink "$cachedir/$filename";
          die "can't move $filename to $opt_d: ". $sftp->error . "\n";
        };
    }
  }

  unlink "$cachedir/$filename";

}

###
# subs
###

sub usage {
  "Usage:
  aradial-sftp_and_import [ -m method ] [ -a ] [ -b ]
    [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ]
    user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass
  ";
}

use vars qw( $sftp $ftp );

sub ftp {
  return $ftp if $ftp && $ftp->pwd;
  
  my ($hostname, $userpass) = reverse split('@', $servername);
  my ($ftp_user, $ftp_pass) = split(':', $userpass);

  my $ftp = Net::FTP->new($hostname, %options) 
    or die "FTP connection to '$hostname' failed.";
  $ftp->login($ftp_user, $ftp_pass) or die "FTP login failed: ".$ftp->message;
  $ftp->cwd($opt_r) or die "can't chdir to $opt_r\n" if $opt_r;
  $ftp->binary or die "can't set BINARY mode: ". $ftp->message if $opt_b;
  return $ftp;
}

sub sftp {

  #reuse connections
  return $sftp if $sftp && $sftp->cwd;

  my %sftp = ( host => $servername );

  $sftp = Net::SFTP::Foreign->new(%sftp);
  $sftp->error and die "SFTP connection failed: ". $sftp->error;

  $sftp;
}

=head1 NAME

freeside-aradial-sftp_and_import - Download Aradial "CDR" (really RADIUS detail) files from a remote server via SFTP

=head1 SYNOPSIS

  aradial-sftp_and_import [ -m method ] [ -a ] [ -b ]
    [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ]
    user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass

=head1 DESCRIPTION

Command line tool to download CDR files from a remote server via SFTP 
or FTP and then import them into the database.

-m: transfer method (sftp or ftp), defaults to sftp

-a: use ftp passive mode

-b: use ftp binary mode

-r: if specified, changes into this remote folder before starting

-d: if specified, moves files to the specified folder when done

-P: if specified, sets the port to use

-v: set verbosity level; this script only has one level, but it will 
    be passed as the 'debug' argument to the transport method

user: freeside username

[sftpuser@]servername: remote server
(or ftpuser:ftppass@servername)

=head1 BUGS

=head1 SEE ALSO

L<FS::cdr>

=cut

1;