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:V:', \%opt);
+getopts('e:H:U:P:D:T:V:Q', \%opt);
my $user = shift or die &usage;
my $engine = $opt{e} || 'mysql';
$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';
if ( $engine =~ /^mysql/ ) {
my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
if( ! @$status ) {
- warn "Adding freesidestatus column...\n" if $DEBUG;
+ 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 {
- warn "freesidestatus column present\n" if $DEBUG;
+ log_msg( info => "freesidestatus column present" );
}
}
my @cols = ();
-if ( $opt{V} > 12 ) {
- push @cols, qw( start answer end );
+if ( $opt{V} >= 12 ) {
+
+ push @cols, qw( id )
+ unless $opt{Q};
+
+ push @cols, qw( start answer );
+
+ if ( $engine =~ /^Pg/ ) {
+ push @cols, "'end'";
+ } else {
+ push @cols, "end";
+ }
+
} else {
push @cols, qw( calldate );
}
my $sql =
'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
my $sth = $dbi->prepare($sql);
-$sth->execute;
-warn "Importing ".$sth->rows." records...\n" if $DEBUG;
+$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 $cdr = FS::cdr->new($row);
- if ( $opt{V} > 12 ) {
+ 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));
}
$cdr->cdrbatchnum($cdrbatchnum);
- my $error = $cdr->insert;
- if ($error) {
- warn "failed import: $error\n";
+ $cdr->amaflags('') unless $cdr->amaflags =~ /^(\d+)$/;
+
+ 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 = ( undef, $row->{'uniqueid'} );
+ }
+ } else {
+ $usql .= ' calldate = ? AND src = ? AND dst = ?';
+ @args = ( undef, $row->{'calldate'}, $row->{'src'}, $row->{'dst'}, );
+ }
+
+ if ( $dbi->do($usql, @args) ) {
+ $updates++;
} else {
- warn "failed to set status: ".$dbi->errstr."\n";
+ log_msg( error =>
+ sprintf 'Non-fatal failure set status(done) acctid(%s) table(%s): %s',
+ $row->acctid,
+ $table,
+ $dbi->errstr
+ );
}
+
}
}
-warn "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[ -T table ]\n\t[ -V asterisk_version]\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
-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;
projects / freeside.git / blobdiff