1 package FS::cdr::Import;
4 use Date::Format 'time2str';
5 use FS::UID qw(adminsuidsetup dbh);
10 use vars qw( $DEBUG );
15 FS::cdr::Import - CDR importing
21 FS::cdr::Import->dbi_import(
22 'dbd' => 'Pg', #mysql, Sybase, etc.
23 'table' => 'TABLE_NAME',
24 'primary_key' => 'BILLING_ID',
25 'column_map' => { #freeside => remote_db
26 'freeside_column' => 'remote_db_column',
27 'freeside_column' => sub { my $row = shift; $row->{remote_db_column}; },
43 my %args = @_; #args are specifed by the script using this sub
45 my %opt; #opt is specified for each install / run of the script
46 getopts('H:U:P:D:T:c:L:S:', \%opt);
48 my $user = shift(@ARGV) or die $class->cli_usage;
49 my $database = $opt{D} || $args{database};
50 my $table = $opt{T} || $args{table};
51 my $pkey = $args{primary_key};
52 my $pkey_info = $args{primary_key_info} ? $args{primary_key_info} : 'BIGINT';
53 my $status_table = $opt{S} || $args{status_table};
54 my $dbd_type = $args{'dbd'} ? $args{'dbd'} : 'Pg';
55 my $status_column = $args{status_column} ? $args{status_column} : 'freesidestatus';
56 my $status_column_info = $args{status_column_info} ? $args{status_column} : 'VARCHAR(32)';
58 my $queries = get_queries({
61 'status_column' => $status_column,
62 'status_column_info' => $status_column_info,
63 'status_table' => $status_table,
64 'primary_key' => $pkey,
65 'primary_key_info' => $pkey_info,
68 my $dsn = 'dbi:'. $dbd_type;
69 $dsn .= $queries->{connect_type} . "=$opt{H}";
70 $dsn .= ";database=$database" if $database;
72 my $dbi = DBI->connect($dsn, $opt{U}, $opt{P})
77 ## check for status table if using. if not there create it.
79 my $status = $dbi->selectall_arrayref( $queries->{check_statustable} );
81 print "Adding status table $status_table ...\n";
82 $dbi->do( $queries->{create_statustable} )
86 ## check for column freeside status if not using status table and create it if not there.
88 my $status = $dbi->selectall_arrayref( $queries->{check_statuscolumn} );
90 print "Adding $status_column column...\n";
91 $dbi->do( $queries->{create_statuscolumn} )
96 #my @cols = values %{ $args{column_map} };
97 my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ".
98 $sql .= "LEFT JOIN $status_table ON ( $table.$pkey = $status_table.$pkey ) "
100 $sql .= "WHERE $status_column IS NULL ";
102 #my @cols = values %{ $args{column_map} };
103 my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ".
104 $sql .= 'LEFT JOIN '. $args{status_table}.
105 " ON ( $table.$pkey = ". $args{status_table}. ".$pkey )"
106 if $args{status_table};
107 $sql .= ' WHERE freesidestatus IS NULL ';
109 #$sql .= ' LIMIT '. $opt{L} if $opt{L};
110 my $sth = $dbi->prepare($sql);
111 $sth->execute or die $sth->errstr. " executing $sql";
113 my $cdr_batch = new FS::cdr_batch({
114 'cdrbatch' => 'IVR-import-'. time2str('%Y/%m/%d-%T',time),
116 my $error = $cdr_batch->insert;
117 die $error if $error;
118 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
122 while ( $row = $sth->fetchrow_hashref ) {
124 my %hash = ( 'cdrbatchnum' => $cdrbatchnum );
125 foreach my $field ( keys %{ $args{column_map} } ) {
126 my $col_or_coderef = $args{column_map}->{$field};
127 if ( ref($col_or_coderef) eq 'CODE' ) {
128 $hash{$field} = &{ $col_or_coderef }( $row );
130 $hash{$field} = $row->{ $col_or_coderef };
132 $hash{$field} = '' if $hash{$field} =~ /^\s+$/; #IVR (MSSQL?) bs
135 my $cdr = FS::cdr->new(\%hash);
137 $cdr->cdrtypenum($opt{c}) if $opt{c};
139 my $pkey_value = $row->{$pkey};
141 #print "$pkey_value\n" if $opt{v};
142 my $error = $cdr->insert;
146 #die "$pkey_value: failed import: $error\n";
147 print "$pkey_value: failed import: $error\n";
154 if ( $args{status_table} ) {
157 'INSERT INTO '. $status_table. " ( $pkey, $status_column ) ".
158 " VALUES ( ?, 'done' )";
162 $st_sql = "UPDATE $table SET $status_column = 'done' WHERE $pkey = ?";
166 my $updated = $dbi->do($st_sql, undef, $pkey_value );
167 #$updates += $updated;
168 die "failed to set status: ".$dbi->errstr."\n" unless $updated;
172 if ( $opt{L} && $imported >= $opt{L} ) {
179 print "Imported $imported CDRs.\n" if $imported;
186 #"Usage: \n $0\n\t[ -H hostname ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
187 #"Usage: \n $0\n\t-H hostname\n\t-D database\n\t-U user\n\t-P password\n\t[ -c cdrtypenum ]\n\tfreesideuser\n";
188 "Usage: \n $0\n\t-H hostname\n\t[ -D database ]\n\t-U user\n\t-P password\n\t[ -c cdrtypenum ]\n\t[ -L num_cdrs_limit ]\n\tfreesideuser\n";
192 #my ($dbd, $table, $column, $column_create_info, $status_table, $primary_key, $primary_key_info) = @_;
195 #do we want to add more types? or add as we go?
196 my %dbi_connect_types = (
197 'Sybase' => ':server',
201 #Check for freeside status table Sybase has not been tested
202 my %dbi_check_statustable = (
203 'Sybase' => "SELECT * FROM sysobjects WHERE name = $info->{status_table}",
204 'Pg' => "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$info->{status_table}' AND column_name = '$info->{status_column}'",
207 #Check for freeside status table Sybase has not been tested
208 my %dbi_create_statustable = (
209 'Sybase' => "CREATE TABLE $info->{status_table} ( $info->{primary_key} $info->{primary_key_info}, $info->{status_column} $info->{status_column_info} )",
210 'Pg' => "CREATE TABLE $info->{status_table} ( $info->{primary_key} $info->{primary_key_info}, $info->{status_column} $info->{status_column_info} )",
213 #Check for freeside status column Sybase has not been tested
214 my %dbi_check_statuscolumn = (
215 'Sybase' => "SELECT syscolumns.name FROM sysobjects
216 JOIN syscolumns ON sysobjects.id = syscolumns.id
217 WHERE sysobjects.name LIKE '$info->{table}' AND syscolumns.name = $info->{status_column}",
218 'Pg' => "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$info->{table}' AND column_name = '$info->{status_column}' ",
221 #Check for freeside status column Sybase has not been tested
222 my %dbi_create_statuscolumn = (
223 'Sybase' => "ALTER TABLE $info->{table} ADD $info->{status_column} $info->{status_column_info}",
224 'Pg' => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}",
228 'connect_type' => $dbi_connect_types{$info->{dbd}},
229 'check_statustable' => $dbi_check_statustable{$info->{dbd}},
230 'create_statustable' => $dbi_create_statustable{$info->{dbd}},
231 'check_statuscolumn' => $dbi_check_statuscolumn{$info->{dbd}},
232 'create_statuscolumn' => $dbi_create_statuscolumn{$info->{dbd}},
240 This has only been test with Pg -> postgresql databases
242 Sparse documentation.