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
|
#!/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} || 'cdr';
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(
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 = $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 $row;
while ( $row = $sth->fetchrow_hashref ) {
my $cdr = FS::cdr->new($row);
$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 cdr SET freesidestatus = 'done'
WHERE calldate = ? AND src = ? AND dst = ?",
undef,
$row->{'calldate'},
$row->{'src'},
$row->{'dst'},
) ) {
$updates++;
}
else {
print "failed to set status: ".$mysql->errstr."\n";
}
}
}
print "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
$mysql->disconnect;
sub usage {
"Usage: \n cdr-mysql.import\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
}
|
