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
|
#!/usr/bin/perl
use strict;
use FS::DBI;
use Date::Format 'time2str';
use Date::Parse 'str2time';
use Getopt::Long;
use FS::Record qw(qsearchs dbh);
use FS::UID qw(adminsuidsetup);
use FS::cdr;
use FS::cdr_batch;
use Time::Local;
sub usage {
"Import cdrs from an Evariste CSRP postgres database.
Date range defaults from the enddate of the last evariste import
batch to the most recent midnight. Imports cdrs for calls that
ended on or after startdate, before enddate.
Usage:
freeside-cdr-evariste -d database -h host -u dbusername -p dbpass
[-s startdate] [-e enddate] freesideuser
";
}
my ($db,$host,$username,$password,$startdate,$enddate,$verbose);
GetOptions(
"db=s" => \$db,
"enddate=s" => \$enddate,
"host=s" => \$host,
"password=s" => \$password,
"startdate=s" => \$startdate,
"username=s" => \$username
);
my $fsuser = $ARGV[-1];
die usage() unless $db && $host && $password && $username && $fsuser;
adminsuidsetup($fsuser);
if ($startdate) {
$startdate = str2time($startdate) or die "Can't parse startdate $startdate";
$startdate = time2str("%Y-%m-%d %H:%M:%S",$startdate);
}
unless ($startdate) {
my $lastbatch = qsearchs({
'table' => 'cdr_batch',
'hashref' => { 'cdrbatch' => {op=>'like', value=>"evariste-import-$host-$db\%"}},
'order_by' => 'ORDER BY _date DESC LIMIT 1',
});
$startdate = time2str("%Y-%m-%d %H:%M:%S", $lastbatch->_date) if $lastbatch;
}
$startdate ||= '2010-01-01 00:00:00'; #seems decently in the past
my @now = localtime();
my $now = timelocal(0,0,0,$now[3],$now[4],$now[5]); #most recent midnight
if ($enddate) {
$enddate = str2time($enddate) or die "Can't parse enddate $enddate";
$now = $enddate;
$enddate = time2str("%Y-%m-%d %H:%M:%S",$enddate);
}
$enddate ||= time2str("%Y-%m-%d %H:%M:%S",$now);
my $cdbh = FS::DBI->connect("dbi:Pg:database=$db;host=$host", $username, $password)
or die $FS::DBI::errstr;
# selecting by end_time rather than start_time
# so we don't lose records between batches
my $csth = $cdbh->prepare('SELECT c.*, cp.* FROM cdr c
LEFT JOIN cdr_rate_postproc cp ON cp.cdr_id = c.id
WHERE end_time >= ? AND end_time < ?')
or die $cdbh->errstr;
$csth->execute($startdate,$enddate)
or die $csth->errstr;
$FS::UID::AutoCommit = 0;
my $cdrbatchname = "evariste-import-$host-$db-". time2str('%Y/%m/%d-%T',$now);
die "Batch $cdrbatchname already exists, please specify a different end date. \n\n" . usage()
if FS::cdr_batch->row_exists('cdrbatch = ?', $cdrbatchname);
my $cdr_batch = new FS::cdr_batch({
'cdrbatch' => $cdrbatchname,
'_date' => $now,
});
my $error = $cdr_batch->insert;
if ($error) {
dbh->rollback;
die "Error creating batch: $error";
}
while (my $row = $csth->fetchrow_hashref) {
next if FS::cdr->row_exists('uniqueid = ?', $row->{'id'});
my $cdr = FS::cdr->new ({
# from cdr table
'cdrbatchnum' => $cdr_batch->cdrbatchnum,
'uniqueid' => $row->{'id'},
'src' => $row->{'src'},
'dst' => $row->{'routing_target'} || $row->{'dest'}, # dest_orig? dest_trans?
'startdate' => int(str2time($row->{'start_time'})),
'answerdate' => int(str2time($row->{'answer_time'})),
'enddate' => int(str2time($row->{'end_time'})),
'duration' => $row->{'duration_sec'},
'accountcode' => $row->{'customer_id'},
'src_ip_addr' => $row->{'src_ip'},
'dst_ip_addr' => $row->{'dest_ip'},
# from cdr_rate_postproc table
'billsec' => $row->{'rate_bill_sec'},
'upstream_price' => $row->{'rate_cost_net'},
});
$error = $cdr->insert;
if ($error) {
dbh->rollback or die dbh->errstr;
die "Error inserting cdr: $error";
}
}
$csth->finish;
dbh->commit or die dbh->errstr;
exit;
|
