5 use Date::Parse 'str2time';
6 use Date::Format 'time2str';
7 use FS::UID qw(adminsuidsetup dbh);
8 use FS::Record qw(qsearchs);
14 getopts('H:U:P:D:T:vs:e:', \%opt);
15 my $user = shift or die &usage;
17 my $dsn = 'dbi:mysql';
18 $dsn .= ":database=$opt{D}" if $opt{D};
19 $dsn .= ":host=$opt{H}" if $opt{H};
21 my $mysql = DBI->connect($dsn, $opt{U}, $opt{P})
25 $FS::UID::AutoCommit = 0;
26 my $fsdbh = FS::UID::dbh;
28 # don't use freesidestatus
35 my $last_cdr = qsearchs({
38 'extra_sql' => 'ORDER BY cdrid DESC LIMIT 1',
40 $start_id = $last_cdr ? $last_cdr->cdrid + 1: 1;
43 print "Selecting CDRs from $start_id to ".($end_id || 'end')."...\n";
45 my $table = $opt{T} || 'call_history';
46 # spelled "disposion" in the table
48 id extension_number flow channel partyid start answer duration disposion) );
49 my $sql = 'SELECT '.join(',', @cols). " FROM $table WHERE id >= $start_id";
50 $sql .= " AND id <= $end_id" if $end_id;
51 $sql .= " ORDER BY id";
52 my $sth = $mysql->prepare($sql);
54 print "Importing ".$sth->rows." records...\n";
56 my $cdr_batch = new FS::cdr_batch({
57 'cdrbatch' => 'mysql-import-'. time2str('%Y/%m/%d-%T',time),
59 my $error = $cdr_batch->insert;
61 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
67 while ( $row = $sth->fetchrow_hashref ) {
68 if ( $opt{s} # skip this check if the range doesn't overlap
69 and qsearchs('cdr', { cdrid => $row->{id} } ) ) {
71 print $row->{id} ." (skipped)\n" if $opt{v};
74 my $cdr = FS::cdr->new({
76 channel => $row->{channel},
77 duration => $row->{duration},
78 billsec => $row->{duration},
79 disposition => $row->{disposion},
80 startdate => str2time($row->{start}),
81 answerdate => str2time($row->{answer}),
82 cdrbatchnum => $cdrbatchnum,
85 print $row->{id},"\n" if $opt{v};
86 # Since we're finding these records by ID range instead of flagging them
87 # individually, we can't leave gaps. So rollback and die on any errors
88 # except for duplicate cdrid.
89 if ( $row->{flow} eq 'out' ) {
90 $cdr->src($row->{'extension_number'});
91 $cdr->dst($row->{'partyid'});
93 elsif ( $row->{flow} eq 'in' ) {
94 $cdr->dst($row->{'extension_number'});
95 $cdr->src($row->{'partyid'});
99 die $row->{id} .": invalid flow value: '".$row->{flow}."'\n";
101 my $error = $cdr->insert;
104 die $row->{id} . ": failed import: $error\n";
106 $first ||= $row->{id};
110 $fsdbh->commit or die $fsdbh->errstr;
112 print "Imported $imported CDRs ($first - $last).\n" if $imported;
113 print "Skipped $skipped duplicates.\n" if $skipped;
117 "Usage: \n cdr-mysql.import\n\t[ -v ]\n\t[ -H host ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";