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
|
#!/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('e:H:U:P:D:T:', \%opt);
my $user = shift or die &usage;
my $engine = $opt{e} || 'mysql';
my $dsn = "dbi:$engine";
$dsn .= ":database=$opt{D}"; # if $opt{D};
$dsn .= ";host=$opt{H}" if $opt{H};
my $dbi = DBI->connect($dsn, $opt{U}, $opt{P})
or die $DBI::errstr;
adminsuidsetup $user;
my $fsdbh = FS::UID::dbh;
my $table = $opt{T} || 'cdr';
# check for existence of freesidestatus
if ( $engine =~ /^mysql/ ) {
my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
if( ! @$status ) {
print "Adding freesidestatus column...\n";
$dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
or die $dbi->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 = $dbi->prepare($sql);
$sth->execute;
print "Importing ".$sth->rows." records...\n";
my $cdr_batch = new FS::cdr_batch({
'cdrbatch' => 'sql-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( $dbi->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: ".$dbi->errstr."\n";
}
}
}
print "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
$dbi->disconnect;
sub usage {
"Usage: \n freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
}
=head1 NAME
freeside-cdr-asterisk_sql - Import CDRs from an Asterisk SQL database
=head1 SYNOPSIS
freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database -U user -P password freesideuser
=head1 DESCRIPTION
Imports CDR records from an Asterisk SQL database.
=cut
1;
|
