use Getopt::Std;
my %opt;
-getopts('H:U:P:D:T:', \%opt);
+getopts('H:U:P:D:T:s:e:', \%opt);
my $user = shift or die &usage;
my $dsn = 'dbi:mysql';
my $mysql = DBI->connect($dsn, $opt{U}, $opt{P})
or die $DBI::errstr;
+my ($start, $end) = ('', '');
+if ( $opt{s} ) {
+ $start = str2time($opt{s}) or die "can't parse start date $opt{s}\n";
+ $start = time2str('%Y-%m-%d', $start);
+}
+if ( $opt{e} ) {
+ $end = str2time($opt{e}) or die "can't parse end date $opt{e}\n";
+ $end = time2str('%Y-%m-%d', $end);
+}
+
adminsuidsetup $user;
my $fsdbh = FS::UID::dbh;
# check for existence of freesidestatus
-my $table = $opt{T} || 'cdrs';
+my $table = $opt{T} || 'acc';
my $status = $mysql->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
if( ! @$status ) {
print "Adding freesidestatus column...\n";
}
my @cols = ( qw(
- cdr_id call_start_time duration sip_call_id sip_from_tag
- sip_to_tag created
- ) );
+ id caller_id callee_id method from_tag to_tag callid sip_code sip_reason
+ time )
+);
-my $sql = 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
+my $sql = 'SELECT '.join(',', @cols). " FROM $table".
+ ' WHERE freesidestatus IS NULL' .
+ ' AND sip_code = 200 ' . # only want successful calls
+ ($start && " AND time >= '$start'") .
+ ($end && " AND time < '$end'") .
+ ' ORDER BY time'; # should ensure INVITE/ACK/BYE order
my $sth = $mysql->prepare($sql);
$sth->execute;
print "Importing ".$sth->rows." records...\n";
my $imports = 0;
my $updates = 0;
+my %cdrs;
my $row;
while ( $row = $sth->fetchrow_hashref ) {
- my $cdr = FS::cdr->new({
- calldate => $row->{'call_start_time'},
- src => $row->{'sip_from_tag'},
- dst => $row->{'sip_to_tag'},
- duration => $row->{'duration'},
- billsec => $row->{'duration'},
- uniqueid => $row->{'cdr_id'},
+ my ($callid) = $row->{'callid'};
+ $callid =~ s/@.*//;
+ if ( !$callid ) {
+ warn $row->{'time'} . ": no callid, skipped.\n";
+ next;
+ }
+ my ($src) = $row->{'caller_id'} =~ /^sip:(\d+)@/;
+ my ($dst) = $row->{'callee_id'} =~ /^sip:(\d+)@/;
+
+ my $cdr = $cdrs{$callid};
+ if ( !$cdr ) {
+ $cdr = $cdrs{$callid} = FS::cdr->new ({
+ uniqueid => $callid,
cdrbatchnum => $cdrbatchnum,
});
- $cdr->startdate(str2time($cdr->calldate));
- $cdr->cdrbatchnum($cdrbatchnum);
- my $error = $cdr->insert;
- if($error) {
- print "failed import: $error\n";
}
- else {
- $imports++;
- if( $mysql->do("UPDATE $table SET freesidestatus = 'done'
- WHERE cdr_id = ?",
- undef,
- $row->{'cdr_id'}
- ) ) {
- $updates++;
+ my $date = str2time($row->{'time'});
+ if ( $row->{'method'} eq 'INVITE' ) {
+ $cdr->startdate($date);
+ $cdr->src($src);
+ $cdr->dst($dst);
+ }
+ elsif ( $row->{'method'} eq 'ACK' ) {
+ $cdr->answerdate($date);
+ next if !check_cdr($cdr, $src, $dst);
+ }
+ elsif ( $row->{'method'} eq 'BYE' ) {
+ $cdr->enddate($date);
+ next if !check_cdr($cdr, $src, $dst);
+ }
+ if ( $cdr->startdate and $cdr->answerdate and $cdr->enddate ) {
+ $cdr->duration($cdr->enddate - $cdr->startdate);
+ $cdr->billsec($cdr->enddate - $cdr->answerdate);
+ my $error = $cdr->insert;
+ if($error) {
+ print "failed import: $error\n";
}
else {
- print "failed to set status: ".$mysql->errstr."\n";
+ $imports++;
+ if( $updates += $mysql->do("UPDATE $table SET freesidestatus = 'done'
+ WHERE sip_code = 200 AND callid = ?",
+ undef,
+ $row->{'callid'}
+ ) ) { #nothing
+ }
+ else {
+ print "failed to set status: ".$mysql->errstr."\n";
+ }
+ delete $cdrs{$callid};
}
}
}
-print "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
+print "Done.\nImported $imports CDRs, marked $updates accounting events as done.\n";
+if ( keys(%cdrs) ) {
+ print "Skipped ".scalar(keys(%cdrs))." incomplete calls.\n";
+}
$mysql->disconnect;
sub usage {
- "Usage: \n cdr-opensips.import\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
+ "Usage: \n cdr-opensips.import\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\t[ -s start ] [ -e end ]\n\tfreesideuser\n";
}
+sub check_cdr {
+ # Verify that these records belong to the same call.
+ # BYE records sometimes have the caller/callee fields swapped.
+ # We allow empty src/dst so as not to make noise about incomplete calls. If
+ # this check fails, something is wrong with the source data.
+ my ($cdr, $a, $b) = @_;
+ if ( ( $cdr->src and $cdr->src ne $a and $cdr->src ne $b )
+ or ( $cdr->dst and $cdr->dst ne $a and $cdr->dst ne $b ) ) {
+ warn $cdr->uniqueid . ": src/dst mismatch, skipped.\n";
+ return 0;
+ }
+ return 1;
+}