3 #i'm kinda like freeside-cdr-sftp_and_import... some parts should be libraried
9 use FS::UID qw( adminsuidsetup datasrc );
13 'NASIP' => 'NASIPAddress',
14 'AcctSessionId' => 'AcctSessionId',
15 'Port' => 'NasPortId',
16 #'Status-Type' => 'Acct-Status-Type',
17 'UserID' => 'UserName',
18 'Authentic' => 'AccdtAuthentic',
19 'Service-Type' => 'ServiceType',
20 'FramedProtocol' => 'FramedProtocol',
21 #'FramedCompression' => '', #not handled, needed? unlikely
22 'FramedAddress' => 'FramedIPAddress',
23 'Acct-Delay-Time' => 'AcctStartDelay', #?
24 'Session-Time' => 'AcctSessionTime',
25 #'Input-Gigawords' => '',
26 'Input-Octets' => 'AcctInputOctets',
27 #'Output-Gigawords' => '',
28 'Output-Octets' => 'AcctOutputOctets',
29 'NAS-Port-Type' => 'NASPortType',
30 'Acct-Terminate-Cause' => 'AcctTerminateCause',
37 use vars qw( $opt_m $opt_a $opt_b $opt_r $opt_d $opt_v $opt_P );
38 getopts('m:abr:dP:v:');
42 my $user = shift or die &usage;
45 # %%%FREESIDE_CACHE%%%
46 my $cachedir = '%%%FREESIDE_CACHE%%%/cache.'. datasrc. '/cdrs';
47 mkdir $cachedir unless -d $cachedir;
49 my $servername = shift or die &usage;
51 my( $datasrc, $db_user, $db_pass ) = ( shift, shift, shift );
52 my $dbh = DBI->connect( $datasrc, $db_user, $db_pass)
53 or die "can't connect: $DBI::errstr\n";
55 my $csv = Text::CSV_XS->new;
61 warn "Retrieving directory listing\n" if $opt_v;
63 $opt_m = 'sftp' if !defined($opt_m);
69 $options{'Port'} = $opt_P if $opt_P;
70 $options{'Debug'} = $opt_v if $opt_v;
71 $options{'Passive'} = $opt_a if $opt_a;
75 $ls = [ grep { /^.*$/i } $ls_ftp->ls ];
77 elsif($opt_m eq 'sftp') {
78 $options{'port'} = $opt_P if $opt_P;
79 $options{'debug'} = $opt_v if $opt_v;
83 $ls_sftp->setcwd($opt_r) or die "can't chdir to $opt_r\n"
86 $ls = $ls_sftp->ls('.', wanted => qr/^$.*\.$/i,
90 die "Method '$opt_m' not supported; must be ftp or sftp\n";
97 foreach my $filename ( @$ls ) {
99 warn "Downloading $filename\n" if $opt_v;
102 if($opt_m eq 'ftp') {
104 $ftp->get($filename, "$cachedir/$filename")
105 or die "Can't get $filename: ". $ftp->message . "\n";
109 $sftp->get($filename, "$cachedir/$filename")
110 or die "Can't get $filename: ". $sftp->error . "\n";
113 warn "Processing $filename\n" if $opt_v;
115 my $file_timestamp = $filename.'-'.time2str('%Y-%m-%d', time);
117 open my $fh, "$cachedir/$filename" or die "$cachedir/$filename: $!";
118 my $header = $csv->getline($fh);
120 while ( my $row = $csv->getline($fh) ) {
123 my %hash = map { $_ => $row->[$i++] } @$header;
125 my %dbhash = map { $aradial2db{$_} => $hash{$_} }
126 grep $aradial2db{$_},
129 my @keys = keys %dbhash;
130 my @values = map $dbhash{$_}, @keys;
132 if ( $hash{'Acct-Status-Type'} eq 'Start' ) {
134 $dbhash{'AcctStartTime'} = $hash{'Date'};
136 my $sql = 'INSERT INTO radacct ( ', join(',', @keys).
137 ' ) VALUES ( '. map( ' ? ', @values ). ' )';
138 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
139 $sth->execute(@values) or die $sth->errstr;
141 } elsif ( $hash{'Acct-Status-Type'} eq 'Stop' ) {
143 my $AcctSessionId = delete($dbhash{AcctSessionId});
144 $dbhash{'AcctStopTime'} = $hash{'Date'};
146 my $sql = 'UPDATE radacct '. join(' , ', map "SET $_ = ?", @keys ).
147 ' WHERE AcctSessionId = ? ';
148 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
149 $sth->execute(@values, $AcctSessionId) or die $sth->errstr;
151 } elsif ( $hash{'Acct-Status-Type'} eq 'Interim' ) {
152 #not handled, but stop should capture the usage. unless session are
153 # normally super-long, extending across month boundaries, or we need
154 # real-time-ish data usage detail, it isn't a big deal
156 die 'Unknown Acct-Status-Type '. $hash{'Acct-Status-Type'}. "\n";
162 if ( $opt_m eq 'ftp') {
164 $ftp->rename($filename, "$opt_d/$file_timestamp")
166 unlink "$cachedir/$filename";
167 die "Can't move $filename to $opt_d: ".$ftp->message . "\n";
171 $sftp->rename($filename, "$opt_d/$file_timestamp")
173 unlink "$cachedir/$filename";
174 die "can't move $filename to $opt_d: ". $sftp->error . "\n";
179 unlink "$cachedir/$filename";
189 aradial-sftp_and_import [ -m method ] [ -a ] [ -b ]
190 [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ]
191 user [sftpuser@]servername
195 use vars qw( $sftp $ftp );
198 return $ftp if $ftp && $ftp->pwd;
200 my ($hostname, $userpass) = reverse split('@', $servername);
201 my ($ftp_user, $ftp_pass) = split(':', $userpass);
203 my $ftp = Net::FTP->new($hostname, %options)
204 or die "FTP connection to '$hostname' failed.";
205 $ftp->login($ftp_user, $ftp_pass) or die "FTP login failed: ".$ftp->message;
206 $ftp->cwd($opt_r) or die "can't chdir to $opt_r\n" if $opt_r;
207 $ftp->binary or die "can't set BINARY mode: ". $ftp->message if $opt_b;
214 return $sftp if $sftp && $sftp->cwd;
216 my %sftp = ( host => $servername );
218 $sftp = Net::SFTP::Foreign->new(%sftp);
219 $sftp->error and die "SFTP connection failed: ". $sftp->error;
226 freeside-aradial-sftp_and_import - Download Aradial "CDR" (really RADIUS detail) files from a remote server via SFTP
230 aradial-sftp_and_import [ -m method ] [ -a ] [ -b ]
231 [ -r remotefolder ] [ -d donefolder ] [ -v level ] [ -P port ]
232 user [sftpuser@]servername dbi_datasrc dbi_username dbi_pass
236 Command line tool to download CDR files from a remote server via SFTP
237 or FTP and then import them into the database.
239 -m: transfer method (sftp or ftp), defaults to sftp
241 -a: use ftp passive mode
243 -b: use ftp binary mode
245 -r: if specified, changes into this remote folder before starting
247 -d: if specified, moves files to the specified folder when done
249 -P: if specified, sets the port to use
251 -v: set verbosity level; this script only has one level, but it will
252 be passed as the 'debug' argument to the transport method
254 user: freeside username
256 [sftpuser@]servername: remote server
257 (or ftpuser:ftppass@servername)