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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
#!/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::Record qw(qsearchs);
use FS::cdr;
use DBI;
use Getopt::Std;
my %opt;
getopts('H:U:P:D:T:vs:e:', \%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;
$FS::UID::AutoCommit = 0;
my $fsdbh = FS::UID::dbh;
# don't use freesidestatus
my $start_id;
if ( $opt{s} ) {
$start_id = $opt{s};
}
else {
my $last_cdr = qsearchs({
'table' => 'cdr',
'hashref' => {},
'extra_sql' => 'ORDER BY cdrid DESC LIMIT 1',
});
$start_id = $last_cdr ? $last_cdr->cdrid + 1: 1;
}
my $end_id = $opt{e};
print "Selecting CDRs from $start_id to ".($end_id || 'end')."...\n";
my $table = $opt{T} || 'call_history';
# spelled "disposion" in the table
my @cols = ( qw(
id extension_number flow channel partyid start answer duration disposion did
client_client_id ) );
my $sql = 'SELECT '.join(',', @cols). " FROM $table WHERE id >= $start_id";
$sql .= " AND id <= $end_id" if $end_id;
$sql .= " ORDER BY id";
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 $imported = 0;
my $skipped = 0;
my $row;
my ($first, $last);
while ( $row = $sth->fetchrow_hashref ) {
if ( $opt{s} # skip this check if the range doesn't overlap
and qsearchs('cdr', { cdrid => $row->{id} } ) ) {
$skipped++;
print $row->{id} ." (skipped)\n" if $opt{v};
next;
}
my $cdr = FS::cdr->new({
cdrid => $row->{id},
channel => $row->{channel},
duration => $row->{duration},
billsec => $row->{duration},
disposition => $row->{disposion},
startdate => str2time($row->{start}),
answerdate => str2time($row->{answer}),
cdrbatchnum => $cdrbatchnum,
accountcode => $row->{client_client_id},
}
);
print $row->{id},"\n" if $opt{v};
if ( $row->{flow} eq 'out' ) {
$cdr->src($row->{'extension_number'});
$cdr->dst($row->{'partyid'});
}
elsif ( $row->{flow} eq 'in' ) {
$cdr->dst($row->{'did'});
$cdr->src($row->{'partyid'});
}
else {
$fsdbh->rollback;
die $row->{id} .": invalid flow value: '".$row->{flow}."'\n";
}
my $error = $cdr->insert;
if($error) {
$fsdbh->rollback;
die $row->{id} . ": failed import: $error\n";
}
$first ||= $row->{id};
$last = $row->{id};
$imported++;
}
$fsdbh->commit or die $fsdbh->errstr;
print "Done.\n";
print "Imported $imported CDRs ($first - $last).\n" if $imported;
print "Skipped $skipped duplicates.\n" if $skipped;
$mysql->disconnect;
sub usage {
"Usage: \n cdr-voipnow.import\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\t[ -v ] [ -s start ] [ -e end ]\n\tfreesideuser\n";
}
=head1 NAME
cdr-voipnow.import - Import call data records (CDRs) from a 4psa VoipNow system
=head1 SYNOPSIS
cdr-voipnow.import [ -H host ] -D database -U user -P password
[ -v ] [ -s start ] [ -e end ] freesideuser
=head1 DESCRIPTION
Connects to a MySQL database and downloads CDRs from the "call_history" table.
The "id" field maps to "cdrid" in Freeside. Other than that, the following
fields are imported: channel, duration, billsec, startdate, answerdate,
disposition, src, dst. src and dst are inferred from the "extension_number"
and "partyid" fields, with the value of the "flow" field (in or out) deciding
which is the source number and which is the destination.
Any import errors (except duplicates) will abort and roll back the
transaction.
=head1 OPTIONS
-H, -D, -U, -P: parameters to connect to the database: host, database name
user, password. Required, except -H, which defaults to localhost.
-s: set the lowest CDR id to import. By default, the script will find
the highest existing cdrid and import all CDRs with ids greater than that.
-s overrides this and turns on duplicate checking.
-e: set the highest CDR id to import. By default, this is unlimited.
-v: report all CDR ids as they are imported.
=cut
|
