5 use Date::Parse 'str2time';
6 use Date::Format 'time2str';
7 use FS::UID qw(adminsuidsetup dbh);
15 getopts('e:H:U:P:D:T:', \%opt);
16 my $user = shift or die &usage;
18 my $engine = $opt{e} || 'mysql';
20 my $dsn = "dbi:$engine";
21 $dsn .= ":database=$opt{D}"; # if $opt{D};
22 $dsn .= ";host=$opt{H}" if $opt{H};
24 my $dbi = DBI->connect($dsn, $opt{U}, $opt{P})
29 my $fsdbh = FS::UID::dbh;
31 my $table = $opt{T} || 'cdr';
33 # check for existence of freesidestatus
34 if ( $engine =~ /^mysql/ ) {
35 my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
37 warn "Adding freesidestatus column...\n" if $DEBUG;
38 $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
41 warn "freesidestatus column present\n" if $DEBUG;
46 calldate clid src dst dcontext channel lastapp lastdata duration
47 billsec disposition amaflags accountcode userfield
50 # check for existence of uniqueid
51 if ( $engine =~ /^mysql/ ) {
52 my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'uniqueid'");
54 push @cols, 'uniqueid';
57 #assume there's a uniqueid
58 push @cols, 'uniqueid';
62 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
63 my $sth = $dbi->prepare($sql);
65 warn "Importing ".$sth->rows." records...\n" if $DEBUG;
67 my $cdr_batch = new FS::cdr_batch({
68 'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time),
70 my $error = $cdr_batch->insert;
72 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
77 while ( $row = $sth->fetchrow_hashref ) {
78 my $cdr = FS::cdr->new($row);
79 $cdr->startdate(str2time($cdr->calldate));
80 $cdr->cdrbatchnum($cdrbatchnum);
81 my $error = $cdr->insert;
83 warn "failed import: $error\n";
87 if( $dbi->do("UPDATE cdr SET freesidestatus = 'done'
88 WHERE calldate = ? AND src = ? AND dst = ?",
97 warn "failed to set status: ".$dbi->errstr."\n";
102 warn "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
106 "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";
111 freeside-cdr-asterisk_sql - Import CDRs from an Asterisk SQL database
115 freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database [ -T table ] -U user -P password [ -s ] freesideuser
119 Imports CDR records from an Asterisk SQL database.
123 -e: Database engine (default mysql)
127 -T: table name (default cdr)