5 use Date::Parse 'str2time';
6 use Date::Format 'time2str';
7 use FS::UID qw(adminsuidsetup dbh);
16 getopts('e:H:U:P:D:T:V:', \%opt);
17 my $user = shift or die &usage;
19 my $engine = $opt{e} || 'mysql';
21 my $dsn = "dbi:$engine";
22 $dsn .= ":database=$opt{D}"; # if $opt{D};
23 $dsn .= ";host=$opt{H}" if $opt{H};
27 my $log = FS::Log->new( 'freeside-cdr-asterisk_sql' );
29 my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) ;
32 log_msg( info => "Established connection to CDR database at dsn($dsn)" );
35 sprintf 'Fatal error connecting to CDR database at dsn(%s): %s',
41 my $fsdbh = FS::UID::dbh;
43 my $table = $opt{T} || 'cdr';
45 # check for existence of freesidestatus
46 if ( $engine =~ /^mysql/ ) {
47 my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
49 log_msg( warn => "Adding freesidestatus column" );
50 $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
51 or log_and_die( error => $dbi->errstr );
53 log_msg( info => "freesidestatus column present" );
58 if ( $opt{V} >= 12 ) {
59 push @cols, qw( id start answer end );
61 push @cols, qw( calldate );
65 clid src dst dcontext channel lastapp lastdata duration
66 billsec disposition amaflags accountcode userfield
69 # check for existence of uniqueid
70 if ( $engine =~ /^mysql/ ) {
71 my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'uniqueid'");
73 push @cols, 'uniqueid';
76 #assume there's a uniqueid
77 push @cols, 'uniqueid';
81 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
82 my $sth = $dbi->prepare($sql);
84 or log_and_die( error => $sth->errstr );
86 log_msg( info => sprintf 'Importing %s records', $sth->rows );
88 my $cdr_batch = new FS::cdr_batch({
89 'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time),
91 if ( my $error = $cdr_batch->insert ) {
92 log_and_die( error => $error );
95 sprintf 'cdrbatch %s %s',
97 $cdr_batch->cdrbatchnum
101 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
106 while ( my $row = $sth->fetchrow_hashref ) {
108 my $cdr = FS::cdr->new($row);
110 if ( $opt{V} >= 12 ) {
111 $cdr->calldate($cdr->start);
112 $cdr->startdate(str2time($cdr->start));
113 $cdr->answerdate(str2time($cdr->answer));
114 $cdr->enddate(str2time($cdr->end));
115 $cdr->cdrid($cdr->id);
117 $cdr->startdate(str2time($cdr->calldate));
120 $cdr->cdrbatchnum($cdrbatchnum);
122 if ( my $error = $cdr->insert ) {
124 sprintf 'Non-fatal failure to import acctid(%s) from table(%s): %s',
133 my $usql = "UPDATE $table SET freesidestatus = 'done' WHERE ";
135 if ( $opt{V} >= 12 ) {
136 $usql .= ' id = '. $row->{'id'};
139 $usql .= ' calldate = ? AND src = ? AND dst = ?';
140 @args = ( undef, $row->{'calldate'}, $row->{'src'}, $row->{'dst'}, );
143 if ( $dbi->do($usql, @args) ) {
147 sprintf 'Non-fatal failure set status(done) acctid(%s) table(%s): %s',
159 info => "Done.\nImported $imports CDRs, marked $updates CDRs as done"
165 my ( $level, $message ) = @_;
166 $log->$level( $message );
167 die "[$level] $message\n";
171 my ( $level, $message ) = @_;
172 $log->$level( $message );
173 warn "[$level] $message\n"
178 my ( $level, $message ) = @_;
179 $log->$level( $message );
185 "Usage: \n freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]n\t-D database\n\t[ -T table ]\n\t[ -V asterisk_version]\n\t-U user\n\t-P password\n\tfreesideuser\n";
190 freeside-cdr-asterisk_sql - Import CDRs from an Asterisk SQL database
194 freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database [ -T table ][ -V asterisk_version ] -U user -P password freesideuser
198 Imports CDR records from an Asterisk SQL database.
202 -e: Database engine (default mysql)
206 -T: table name (default cdr)
212 -V: Asterisk version. Values of 12 or higher have a new database schema which no longer includes calldate. Currently defaults to 11, but may default to 12+ or be required in a future, so best to always specify the version.