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 = FS::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 );
60 if ( $engine =~ /^Pg/ ) {
66 push @cols, qw( calldate );
70 clid src dst dcontext channel lastapp lastdata duration
71 billsec disposition amaflags accountcode userfield
74 # check for existence of uniqueid
75 if ( $engine =~ /^mysql/ ) {
76 my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'uniqueid'");
78 push @cols, 'uniqueid';
81 #assume there's a uniqueid
82 push @cols, 'uniqueid';
86 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
87 my $sth = $dbi->prepare($sql);
89 or log_and_die( error => $sth->errstr );
91 log_msg( info => sprintf 'Importing %s records', $sth->rows );
93 my $cdr_batch = new FS::cdr_batch({
94 'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time),
96 if ( my $error = $cdr_batch->insert ) {
97 log_and_die( error => $error );
100 sprintf 'cdrbatch %s %s',
101 $cdr_batch->cdrbatch,
102 $cdr_batch->cdrbatchnum
106 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
111 while ( my $row = $sth->fetchrow_hashref ) {
113 my $cdr = FS::cdr->new($row);
115 if ( $opt{V} >= 12 ) {
116 $cdr->calldate($cdr->start);
117 $cdr->startdate(str2time($cdr->start));
118 $cdr->answerdate(str2time($cdr->answer));
119 $cdr->enddate(str2time($cdr->end));
120 $cdr->cdrid($cdr->id);
122 $cdr->startdate(str2time($cdr->calldate));
125 $cdr->cdrbatchnum($cdrbatchnum);
127 if ( my $error = $cdr->insert ) {
129 sprintf 'Non-fatal failure to import acctid(%s) from table(%s): %s',
138 my $usql = "UPDATE $table SET freesidestatus = 'done' WHERE ";
140 if ( $opt{V} >= 12 ) {
141 $usql .= ' id = '. $row->{'id'};
144 $usql .= ' calldate = ? AND src = ? AND dst = ?';
145 @args = ( undef, $row->{'calldate'}, $row->{'src'}, $row->{'dst'}, );
148 if ( $dbi->do($usql, @args) ) {
152 sprintf 'Non-fatal failure set status(done) acctid(%s) table(%s): %s',
164 info => "Done.\nImported $imports CDRs, marked $updates CDRs as done"
170 my ( $level, $message ) = @_;
171 $log->$level( $message );
172 die "[$level] $message\n";
176 my ( $level, $message ) = @_;
177 $log->$level( $message );
178 warn "[$level] $message\n"
183 my ( $level, $message ) = @_;
184 $log->$level( $message );
190 "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";
195 freeside-cdr-asterisk_sql - Import CDRs from an Asterisk SQL database
199 freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database [ -T table ][ -V asterisk_version ] -U user -P password freesideuser
203 Imports CDR records from an Asterisk SQL database.
207 -e: Database engine (default mysql)
211 -T: table name (default cdr)
217 -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.