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' => 'mysql', #Pg, 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:', \%opt);
47 my $user = shift(@ARGV) or die $class->cli_usage;
49 $opt{D} ||= $args{database};
51 my $dsn = 'dbi:'. $args{dbd};
52 #$dsn .= ":host=$opt{H}"; #if $opt{H};
53 $dsn .= ":server=$opt{H}"; #if $opt{H};
54 $dsn .= ";database=$opt{D}" if $opt{D};
56 my $dbi = DBI->connect($dsn, $opt{U}, $opt{P})
61 #my $fsdbh = FS::UID::dbh;
63 my $table = $opt{T} || $args{table};
64 my $pkey = $args{primary_key};
66 #just doing this manually with IVR MSSQL databases for now
67 # # check for existence of freesidestatus
68 # my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
70 # print "Adding freesidestatus column...\n";
71 # $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
72 # or die $dbi->errstr;
75 # print "freesidestatus column present\n";
77 # or if using a status_table:
78 # CREATE TABLE FREESIDE_BILLING (
80 # FREESIDESTATUS VARCHAR(32)
83 #my @cols = values %{ $args{column_map} };
84 my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ".
85 $sql .= 'LEFT JOIN '. $args{status_table}.
86 " ON ( $table.$pkey = ". $args{status_table}. ".$pkey )"
87 if $args{status_table};
88 $sql .= ' WHERE freesidestatus IS NULL ';
90 #$sql .= ' LIMIT '. $opt{L} if $opt{L};
91 my $sth = $dbi->prepare($sql);
92 $sth->execute or die $sth->errstr. " executing $sql";
93 #MySQL-specific print "Importing ".$sth->rows." records...\n";
95 my $cdr_batch = new FS::cdr_batch({
96 'cdrbatch' => 'IVR-import-'. time2str('%Y/%m/%d-%T',time),
98 my $error = $cdr_batch->insert;
100 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
104 while ( $row = $sth->fetchrow_hashref ) {
106 my %hash = ( 'cdrbatchnum' => $cdrbatchnum );
107 foreach my $field ( keys %{ $args{column_map} } ) {
108 my $col_or_coderef = $args{column_map}->{$field};
109 if ( ref($col_or_coderef) eq 'CODE' ) {
110 $hash{$field} = &{ $col_or_coderef }( $row );
112 $hash{$field} = $row->{ $col_or_coderef };
114 $hash{$field} = '' if $hash{$field} =~ /^\s+$/; #IVR (MSSQL?) bs
116 my $cdr = FS::cdr->new(\%hash);
118 $cdr->cdrtypenum($opt{c}) if $opt{c};
120 my $pkey_value = $row->{$pkey};
122 #print "$pkey_value\n" if $opt{v};
123 my $error = $cdr->insert;
127 #die "$pkey_value: failed import: $error\n";
128 print "$pkey_value: failed import: $error\n";
135 if ( $args{status_table} ) {
138 'INSERT INTO '. $args{status_table}. " ( $pkey, freesidestatus ) ".
139 " VALUES ( ?, 'done' )";
143 $st_sql = "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?";
147 my $updated = $dbi->do($st_sql, undef, $pkey_value );
148 #$updates += $updated;
149 die "failed to set status: ".$dbi->errstr."\n" unless $updated;
153 if ( $opt{L} && $imported >= $opt{L} ) {
160 print "Imported $imported CDRs.\n" if $imported;
167 #"Usage: \n $0\n\t[ -H hostname ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
168 #"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";
169 "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";
174 Not everything has been refactored out of the various bin/cdr-*.import scripts,
175 let alone other places.
177 Sparse documentation.