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:Q', \%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 ) {
63 push @cols, qw( start answer );
65 if ( $engine =~ /^Pg/ ) {
72 push @cols, qw( calldate );
76 clid src dst dcontext channel lastapp lastdata duration
77 billsec disposition amaflags accountcode userfield
80 # check for existence of uniqueid
81 if ( $engine =~ /^mysql/ ) {
82 my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'uniqueid'");
84 push @cols, 'uniqueid';
87 #assume there's a uniqueid
88 push @cols, 'uniqueid';
92 'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
93 my $sth = $dbi->prepare($sql);
95 or log_and_die( error => $sth->errstr );
97 log_msg( info => sprintf 'Importing %s records', $sth->rows );
99 my $cdr_batch = new FS::cdr_batch({
100 'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time),
102 if ( my $error = $cdr_batch->insert ) {
103 log_and_die( error => $error );
106 sprintf 'cdrbatch %s %s',
107 $cdr_batch->cdrbatch,
108 $cdr_batch->cdrbatchnum
112 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
117 while ( my $row = $sth->fetchrow_hashref ) {
119 my $cdr = FS::cdr->new($row);
121 if ( $opt{V} >= 12 ) {
122 $cdr->calldate($cdr->start);
123 $cdr->startdate(str2time($cdr->start));
124 $cdr->answerdate(str2time($cdr->answer));
125 $cdr->enddate(str2time($cdr->end));
126 $cdr->cdrid($cdr->id);
128 $cdr->startdate(str2time($cdr->calldate));
131 $cdr->cdrbatchnum($cdrbatchnum);
133 $cdr->amaflags('') unless $cdr->amaflags =~ /^(\d+)$/;
135 if ( my $error = $cdr->insert ) {
136 log_and_die( error =>
137 sprintf 'Fatal failure to import uniqueid(%s) from table(%s): %s',
146 my $usql = "UPDATE $table SET freesidestatus = 'done' WHERE ";
148 if ( $opt{V} >= 12 ) {
149 if ( $row->{'id'} ) {
150 $usql .= ' id = '. $row->{'id'};
153 $usql .= ' uniqueid = ?';
154 @args = ( undef, $row->{'uniqueid'} );
157 $usql .= ' calldate = ? AND src = ? AND dst = ?';
158 @args = ( undef, $row->{'calldate'}, $row->{'src'}, $row->{'dst'}, );
161 if ( $dbi->do($usql, @args) ) {
164 log_and_die( error =>
165 sprintf 'Fatal failure set status(done) uniqueid(%s) table(%s): %s',
177 info => "Done.\nImported $imports CDRs, marked $updates CDRs as done"
183 my ( $level, $message ) = @_;
184 $log->$level( $message );
185 die "[$level] $message\n";
189 my ( $level, $message ) = @_;
190 $log->$level( $message );
191 warn "[$level] $message\n"
196 my ( $level, $message ) = @_;
197 $log->$level( $message );
203 "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";
208 freeside-cdr-asterisk_sql - Import CDRs from an Asterisk SQL database
212 freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database [ -T table ][ -V asterisk_version ] -U user -P password freesideuser
216 Imports CDR records from an Asterisk SQL database.
220 -e: Database engine (default mysql)
224 -T: table name (default cdr)
230 -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.
232 -Q: Use uniqueid field instead of id (Asterisk v12 only). Less efficient, but useful if the field is missing.