6 use Date::Format 'time2str';
7 use Date::Parse 'str2time';
10 use FS::Record qw(qsearchs dbh);
11 use FS::UID qw(adminsuidsetup);
17 "Import cdrs from an Evariste CSRP postgres database.
19 Date range defaults from the enddate of the last evariste import
20 batch to the most recent midnight. Imports cdrs for calls that
21 ended on or after startdate, before enddate.
24 freeside-cdr-evariste -d database -h host -u dbusername -p dbpass
25 [-s startdate] [-e enddate] freesideuser
29 my ($db,$host,$username,$password,$startdate,$enddate,$verbose);
32 "enddate=s" => \$enddate,
34 "password=s" => \$password,
35 "startdate=s" => \$startdate,
36 "username=s" => \$username
39 my $fsuser = $ARGV[-1];
41 die usage() unless $db && $host && $password && $username && $fsuser;
43 adminsuidsetup($fsuser);
46 $startdate = str2time($startdate) or die "Can't parse startdate $startdate";
47 $startdate = time2str("%Y-%m-%d %H:%M:%S",$startdate);
50 my $lastbatch = qsearchs({
51 'table' => 'cdr_batch',
52 'hashref' => { 'cdrbatch' => {op=>'like', value=>"evariste-import-$host-$db\%"}},
53 'order_by' => 'ORDER BY _date DESC LIMIT 1',
55 $startdate = time2str("%Y-%m-%d %H:%M:%S", $lastbatch->_date) if $lastbatch;
57 $startdate ||= '2010-01-01 00:00:00'; #seems decently in the past
59 my @now = localtime();
60 my $now = timelocal(0,0,0,$now[3],$now[4],$now[5]); #most recent midnight
62 $enddate = str2time($enddate) or die "Can't parse enddate $enddate";
64 $enddate = time2str("%Y-%m-%d %H:%M:%S",$enddate);
66 $enddate ||= time2str("%Y-%m-%d %H:%M:%S",$now);
68 my $cdbh = DBI->connect("dbi:Pg:database=$db;host=$host", $username, $password)
71 # selecting by end_time rather than start_time
72 # so we don't lose records between batches
73 my $csth = $cdbh->prepare('SELECT c.*, cp.* FROM cdr c
74 LEFT JOIN cdr_rate_postproc cp ON cp.cdr_id = c.id
75 WHERE end_time >= ? AND end_time < ?')
78 $csth->execute($startdate,$enddate)
81 $FS::UID::AutoCommit = 0;
83 my $cdrbatchname = "evariste-import-$host-$db-". time2str('%Y/%m/%d-%T',$now);
84 die "Batch $cdrbatchname already exists, please specify a different end date. \n\n" . usage()
85 if FS::cdr_batch->row_exists('cdrbatch = ?', $cdrbatchname);
86 my $cdr_batch = new FS::cdr_batch({
87 'cdrbatch' => $cdrbatchname,
90 my $error = $cdr_batch->insert;
93 die "Error creating batch: $error";
96 while (my $row = $csth->fetchrow_hashref) {
97 next if FS::cdr->row_exists('uniqueid = ?', $row->{'id'});
98 my $cdr = FS::cdr->new ({
100 'cdrbatchnum' => $cdr_batch->cdrbatchnum,
101 'uniqueid' => $row->{'id'},
102 'src' => $row->{'src'},
103 'dst' => $row->{'routing_target'} || $row->{'dest'}, # dest_orig? dest_trans?
104 'startdate' => int(str2time($row->{'start_time'})),
105 'answerdate' => int(str2time($row->{'answer_time'})),
106 'enddate' => int(str2time($row->{'end_time'})),
107 'duration' => $row->{'duration_sec'},
108 'accountcode' => $row->{'customer_id'},
109 'src_ip_addr' => $row->{'src_ip'},
110 'dst_ip_addr' => $row->{'dest_ip'},
111 # from cdr_rate_postproc table
112 'billsec' => $row->{'rate_bill_sec'},
113 'upstream_price' => $row->{'rate_cost_net'},
115 $error = $cdr->insert;
117 dbh->rollback or die dbh->errstr;
118 die "Error inserting cdr: $error";
124 dbh->commit or die dbh->errstr;