use DBI;
use Getopt::Std;
+$DEBUG = 0;
+
my %opt;
-getopts('e:H:U:P:D:T:', \%opt);
+getopts('e:H:U:P:D:T:V:', \%opt);
my $user = shift or die &usage;
my $engine = $opt{e} || 'mysql';
if ( $engine =~ /^mysql/ ) {
my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
if( ! @$status ) {
- print "Adding freesidestatus column...\n";
+ warn "Adding freesidestatus column...\n" if $DEBUG;
$dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
or die $dbi->errstr;
} else {
- print "freesidestatus column present\n";
+ warn "freesidestatus column present\n" if $DEBUG;
+ }
+}
+
+my @cols = ();
+if ( $opt{V} >= 12 ) {
+ push @cols, qw( id start answer end );
+} else {
+ push @cols, qw( calldate );
+}
+
+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 @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";
-my $sth = $sql->prepare($sql);
+my $sql =
+ 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
+my $sth = $dbi->prepare($sql);
$sth->execute;
-print "Importing ".$sth->rows." records...\n";
+warn "Importing ".$sth->rows." records...\n" if $DEBUG;
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;
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));
+
+ 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) {
- print "failed import: $error\n";
- }
- else {
+ if ($error) {
+ warn "failed import: $error\n";
+ } 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 ) {
+ $usql .= ' id = '. $row->{'id'};
+ @args = ( undef );
+ } 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 {
+ warn "failed to set status: ".$dbi->errstr."\n";
}
+
}
+
}
-print "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
+
+warn "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
$dbi->disconnect;
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
=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.
+
=cut
1;