1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
#!/usr/bin/perl
use strict;
use vars qw( $DEBUG );
use Date::Parse 'str2time';
use Date::Format 'time2str';
use FS::UID qw(adminsuidsetup dbh);
use FS::cdr;
use DBI;
use Getopt::Std;
my %opt;
getopts('H:U:P:D:T:', \%opt);
my $user = shift or die &usage;
my $dsn = 'dbi:mysql';
$dsn .= ":database=$opt{D}" if $opt{D};
$dsn .= ":host=$opt{H}" if $opt{H};
my $mysql = DBI->connect($dsn, $opt{U}, $opt{P})
or die $DBI::errstr;
adminsuidsetup $user;
my $fsdbh = FS::UID::dbh;
# check for existence of freesidestatus
my $table = $opt{T} || 'acc';
my $status = $mysql->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
if( ! @$status ) {
print "Adding freesidestatus column...\n";
$mysql->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
or die $mysql->errstr;
}
else {
print "freesidestatus column present\n";
}
my @cols = ( qw(
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 AND sip_code = 200"; # only want successful calls
my $sth = $mysql->prepare($sql);
$sth->execute;
print "Importing ".$sth->rows." records...\n";
my $cdr_batch = new FS::cdr_batch({
'cdrbatch' => 'mysql-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 %cdrs;
my $row;
while ( $row = $sth->fetchrow_hashref ) {
my ($callid) = $row->{'callid'} =~ /(.*)@/;
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,
});
}
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);
}
elsif ( $row->{'method'} eq 'BYE' ) {
$cdr->enddate($date);
}
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 {
$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 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";
}
|
