X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2Fbin%2Ffreeside-cdr-asterisk_sql;h=a5a570d60400cb8eac16045a59786029b1a92926;hp=4d4d6487e9126cb4e21e346ff473545e939946e0;hb=1f1190b8fb6d06852dbd16c6a288f9acd9cc83dd;hpb=6de06472ab43534bd889e531ae060bbd4c935518 diff --git a/FS/bin/freeside-cdr-asterisk_sql b/FS/bin/freeside-cdr-asterisk_sql index 4d4d6487e..a5a570d60 100755 --- a/FS/bin/freeside-cdr-asterisk_sql +++ b/FS/bin/freeside-cdr-asterisk_sql @@ -5,12 +5,15 @@ use vars qw( $DEBUG ); use Date::Parse 'str2time'; use Date::Format 'time2str'; use FS::UID qw(adminsuidsetup dbh); +use FS::Log; use FS::cdr; -use DBI; +use FS::DBI; use Getopt::Std; +$DEBUG = 0; + my %opt; -getopts('e:H:U:P:D:T:', \%opt); +getopts('e:H:U:P:D:T:V:Q', \%opt); my $user = shift or die &usage; my $engine = $opt{e} || 'mysql'; @@ -19,11 +22,22 @@ my $dsn = "dbi:$engine"; $dsn .= ":database=$opt{D}"; # if $opt{D}; $dsn .= ";host=$opt{H}" if $opt{H}; -my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) - or die $DBI::errstr; - adminsuidsetup $user; +my $log = FS::Log->new( 'freeside-cdr-asterisk_sql' ); + +my $dbi = FS::DBI->connect($dsn, $opt{U}, $opt{P}) ; + +if ( $dbi ) { + log_msg( info => "Established connection to CDR database at dsn($dsn)" ); +} else { + log_and_die( error => + sprintf 'Fatal error connecting to CDR database at dsn(%s): %s', + $dsn, + $FS::DBI::errstr + ); +} + my $fsdbh = FS::UID::dbh; my $table = $opt{T} || 'cdr'; @@ -32,62 +46,159 @@ my $table = $opt{T} || 'cdr'; if ( $engine =~ /^mysql/ ) { my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'"); if( ! @$status ) { - print "Adding freesidestatus column...\n"; + log_msg( warn => "Adding freesidestatus column" ); $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)") - or die $dbi->errstr; + or log_and_die( error => $dbi->errstr ); + } else { + log_msg( info => "freesidestatus column present" ); + } +} + +my @cols = (); +if ( $opt{V} >= 12 ) { + + push @cols, qw( id ) + unless $opt{Q}; + + push @cols, qw( start answer ); + + if ( $engine =~ /^Pg/ ) { + push @cols, "'end'"; } else { - print "freesidestatus column present\n"; + push @cols, "end"; } + +} else { + push @cols, qw( calldate ); } -my @cols = ( qw( -calldate clid src dst dcontext channel lastapp lastdata duration - billsec disposition amaflags accountcode uniqueid userfield) ); -my $sql = 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL"; +push @cols, qw( + clid src dst dcontext channel lastapp lastdata duration + billsec disposition amaflags accountcode userfield +); + +# check for existence of uniqueid +if ( $engine =~ /^mysql/ ) { + my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'uniqueid'"); + if( @$status ) { + push @cols, 'uniqueid'; + } +} else { + #assume there's a uniqueid + push @cols, 'uniqueid'; +} + +my $sql = + 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL"; my $sth = $dbi->prepare($sql); -$sth->execute; -print "Importing ".$sth->rows." records...\n"; +$sth->execute + or log_and_die( error => $sth->errstr ); + +log_msg( info => sprintf 'Importing %s records', $sth->rows ); my $cdr_batch = new FS::cdr_batch({ 'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time), }); -my $error = $cdr_batch->insert; -die $error if $error; +if ( my $error = $cdr_batch->insert ) { + log_and_die( error => $error ); +} else { + log_msg( info => + sprintf 'cdrbatch %s %s', + $cdr_batch->cdrbatch, + $cdr_batch->cdrbatchnum + ); +} + my $cdrbatchnum = $cdr_batch->cdrbatchnum; + my $imports = 0; my $updates = 0; -my $row; -while ( $row = $sth->fetchrow_hashref ) { +while ( my $row = $sth->fetchrow_hashref ) { + my $cdr = FS::cdr->new($row); - $cdr->startdate(str2time($cdr->calldate)); - $cdr->cdrbatchnum($cdrbatchnum); - my $error = $cdr->insert; - if($error) { - print "failed import: $error\n"; + + if ( $opt{V} >= 12 ) { + $cdr->calldate($cdr->start); + $cdr->startdate(str2time($cdr->start)); + $cdr->answerdate(str2time($cdr->answer)); + $cdr->enddate(str2time($cdr->end)); + $cdr->cdrid($cdr->id); + } else { + $cdr->startdate(str2time($cdr->calldate)); } - else { + + $cdr->cdrbatchnum($cdrbatchnum); + + if ( my $error = $cdr->insert ) { + log_msg( error => + sprintf 'Non-fatal failure to import acctid(%s) from table(%s): %s', + $row->acctid, + $table, + $error + ); + } else { + $imports++; - if( $dbi->do("UPDATE cdr SET freesidestatus = 'done' - WHERE calldate = ? AND src = ? AND dst = ?", - undef, - $row->{'calldate'}, - $row->{'src'}, - $row->{'dst'}, - - ) ) { - $updates++; + + my $usql = "UPDATE $table SET freesidestatus = 'done' WHERE "; + my @args = (); + if ( $opt{V} >= 12 ) { + if ( $row->{'id'} ) { + $usql .= ' id = '. $row->{'id'}; + @args = ( undef ); + } else { + $usql .= ' uniqueid = ?'; + @args = ( $row->{'uniqueid'} ); + } + } else { + $usql .= ' calldate = ? AND src = ? AND dst = ?'; + @args = ( undef, $row->{'calldate'}, $row->{'src'}, $row->{'dst'}, ); } - else { - print "failed to set status: ".$dbi->errstr."\n"; + + if ( $dbi->do($usql, @args) ) { + $updates++; + } else { + log_msg( error => + sprintf 'Non-fatal failure set status(done) acctid(%s) table(%s): %s', + $row->acctid, + $table, + $dbi->errstr + ); } + } + } -print "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n"; + +log_and_warn( + info => "Done.\nImported $imports CDRs, marked $updates CDRs as done" +); + $dbi->disconnect; +sub log_and_die { + my ( $level, $message ) = @_; + $log->$level( $message ); + die "[$level] $message\n"; +} + +sub log_msg { + my ( $level, $message ) = @_; + $log->$level( $message ); + warn "[$level] $message\n" + if $opt{v}; +} + +sub log_and_warn { + my ( $level, $message ) = @_; + $log->$level( $message ); + warn "$message\n"; +} + + sub usage { - "Usage: \n freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n"; + "Usage: \n freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]\n\t-D database\n\t[ -T table ]\n\t[ -V asterisk_version]\n\t-U user\n\t-P password\n\tfreesideuser\n"; } =head1 NAME @@ -96,12 +207,28 @@ freeside-cdr-asterisk_sql - Import CDRs from an Asterisk SQL database =head1 SYNOPSIS - freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database -U user -P password freesideuser + freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database [ -T table ][ -V asterisk_version ] -U user -P password freesideuser =head1 DESCRIPTION Imports CDR records from an Asterisk SQL database. +-H: hostname + +-e: Database engine (default mysql) + +-D: database name + +-T: table name (default cdr) + +-U: username + +-P: password + +-V: Asterisk version. Values of 12 or higher have a new database schema which no longer includes calldate. Currently defaults to 11, but may default to 12+ or be required in a future, so best to always specify the version. + +-Q: Use uniqueid field instead of id (Asterisk v12 only). Less efficient, but useful if the field is missing. + =cut 1;