5 use Date::Parse 'str2time';
6 use Date::Format 'time2str';
7 use FS::UID qw(adminsuidsetup dbh);
13 getopts('H:U:P:D:T:s:e:c:', \%opt);
14 my $user = shift or die &usage;
16 my $dsn = 'dbi:mysql';
17 $dsn .= ":database=$opt{D}" if $opt{D};
18 $dsn .= ":host=$opt{H}" if $opt{H};
20 my $mysql = DBI->connect($dsn, $opt{U}, $opt{P})
23 my ($start, $end) = ('', '');
25 $start = str2time($opt{s}) or die "can't parse start date $opt{s}\n";
26 $start = time2str('%Y-%m-%d', $start);
29 $end = str2time($opt{e}) or die "can't parse end date $opt{e}\n";
30 $end = time2str('%Y-%m-%d', $end);
35 my $fsdbh = FS::UID::dbh;
37 # check for existence of freesidestatus
38 my $table = $opt{T} || 'acc';
39 my $status = $mysql->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
41 print "Adding freesidestatus column...\n";
42 $mysql->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
43 or die $mysql->errstr;
46 print "freesidestatus column present\n";
50 id caller_id callee_id method from_tag to_tag callid sip_code sip_reason
54 my $sql = 'SELECT '.join(',', @cols). " FROM $table".
55 ' WHERE freesidestatus IS NULL' .
56 ' AND sip_code = 200 ' . # only want successful calls
57 ($start && " AND time >= '$start'") .
58 ($end && " AND time < '$end'") .
59 ' ORDER BY time'; # should ensure INVITE/ACK/BYE order
60 my $sth = $mysql->prepare($sql);
62 print "Importing ".$sth->rows." records...\n";
64 my $cdr_batch = new FS::cdr_batch({
65 'cdrbatch' => 'mysql-import-'. time2str('%Y/%m/%d-%T',time),
67 my $error = $cdr_batch->insert;
69 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
75 while ( $row = $sth->fetchrow_hashref ) {
76 my ($callid) = $row->{'callid'};
79 warn $row->{'time'} . ": no callid, skipped.\n";
83 #i guess now we're NANPA-centric, but at least we warn on non-numeric numbers
85 if ( $row->{'caller_id'} =~ /^sip:(\+1)?(\d+)@/ ) {
88 warn "unparseable caller_id ". $row->{'caller_id'}. "\n";
92 if ( $row->{'callee_id'} =~ /^sip:(\+1)?(\d+)@/ ) {
95 warn "unparseable callee_id ". $row->{'callee_id'}. "\n";
98 my $cdr = $cdrs{$callid};
100 $cdr = $cdrs{$callid} = FS::cdr->new ({
102 cdrbatchnum => $cdrbatchnum,
104 $cdr->cdrtypenum($opt{c}) if $opt{c};
106 my $date = str2time($row->{'time'});
107 if ( $row->{'method'} eq 'INVITE' ) {
108 $cdr->startdate($date);
112 elsif ( $row->{'method'} eq 'ACK' ) {
113 $cdr->answerdate($date);
114 next if !check_cdr($cdr, $src, $dst);
116 elsif ( $row->{'method'} eq 'BYE' ) {
117 $cdr->enddate($date);
118 next if !check_cdr($cdr, $src, $dst);
120 if ( $cdr->startdate and $cdr->answerdate and $cdr->enddate ) {
121 $cdr->duration($cdr->enddate - $cdr->startdate);
122 $cdr->billsec($cdr->enddate - $cdr->answerdate);
123 my $error = $cdr->insert;
125 print "failed import: $error\n";
129 if( $updates += $mysql->do("UPDATE $table SET freesidestatus = 'done'
130 WHERE sip_code = 200 AND callid = ?",
136 print "failed to set status: ".$mysql->errstr."\n";
138 delete $cdrs{$callid};
142 print "Done.\nImported $imports CDRs, marked $updates accounting events as done.\n";
144 print "Skipped ".scalar(keys(%cdrs))." incomplete calls.\n";
149 "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 ] [ -c cdrtypenum ] \n\tfreesideuser\n";
153 # Verify that these records belong to the same call.
154 # BYE records sometimes have the caller/callee fields swapped.
155 # We allow empty src/dst so as not to make noise about incomplete calls. If
156 # this check fails, something is wrong with the source data.
157 my ($cdr, $a, $b) = @_;
158 if ( ( $cdr->src and $cdr->src ne $a and $cdr->src ne $b )
159 or ( $cdr->dst and $cdr->dst ne $a and $cdr->dst ne $b ) ) {
160 warn $cdr->uniqueid . ": src/dst mismatch, skipped.\n";