5 use Date::Parse 'str2time';
6 use Date::Format 'time2str';
7 use FS::UID qw(adminsuidsetup dbh);
13 getopts('H:U:P:D:T:', \%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})
25 my $fsdbh = FS::UID::dbh;
27 # check for existence of freesidestatus
28 my $table = $opt{T} || 'acc';
29 my $status = $mysql->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
31 print "Adding freesidestatus column...\n";
32 $mysql->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
33 or die $mysql->errstr;
36 print "freesidestatus column present\n";
40 id caller_id callee_id method from_tag to_tag callid sip_code sip_reason
44 my $sql = 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL AND sip_code = 200"; # only want successful calls
45 my $sth = $mysql->prepare($sql);
47 print "Importing ".$sth->rows." records...\n";
49 my $cdr_batch = new FS::cdr_batch({
50 'cdrbatch' => 'mysql-import-'. time2str('%Y/%m/%d-%T',time),
52 my $error = $cdr_batch->insert;
54 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
60 while ( $row = $sth->fetchrow_hashref ) {
61 my ($callid) = $row->{'callid'} =~ /(.*)@/;
62 my ($src) = $row->{'caller_id'} =~ /^sip:(\d+)@/;
63 my ($dst) = $row->{'callee_id'} =~ /^sip:(\d+)@/;
65 my $cdr = $cdrs{$callid};
67 $cdr = $cdrs{$callid} = FS::cdr->new ({
69 cdrbatchnum => $cdrbatchnum,
72 my $date = str2time($row->{'time'});
73 if ( $row->{'method'} eq 'INVITE' ) {
74 $cdr->startdate($date);
78 elsif ( $row->{'method'} eq 'ACK' ) {
79 $cdr->answerdate($date);
81 elsif ( $row->{'method'} eq 'BYE' ) {
84 if ( $cdr->startdate and $cdr->answerdate and $cdr->enddate ) {
85 $cdr->duration($cdr->enddate - $cdr->startdate);
86 $cdr->billsec($cdr->enddate - $cdr->answerdate);
87 my $error = $cdr->insert;
89 print "failed import: $error\n";
93 if( $updates += $mysql->do("UPDATE $table SET freesidestatus = 'done'
94 WHERE sip_code = 200 AND callid = ?",
100 print "failed to set status: ".$mysql->errstr."\n";
102 delete $cdrs{$callid};
106 print "Done.\nImported $imports CDRs, marked $updates accounting events as done.\n";
108 print "Skipped ".scalar(keys(%cdrs))." incomplete calls.\n";
113 "Usage: \n cdr-opensips.import\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";