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 'status_table' = > 'STATUS_TABLE_NAME', # if using a table rather than field in main table
26 'column_map' => { #freeside => remote_db
27 'freeside_column' => 'remote_db_column',
28 'freeside_column' => sub { my $row = shift; $row->{remote_db_column}; },
30 'batch_name' => 'batch_name', # cdr_batch name -import-date gets appended.
45 my %args = @_; #args are specifed by the script using this sub
47 my %opt; #opt is specified for each install / run of the script
48 getopts('H:U:P:D:T:c:L:', \%opt);
49 my $user = shift(@ARGV) or die $class->cli_usage;
51 $opt{D} ||= $args{database};
53 #do we want to add more types? or add as we go?
54 my %dbi_connect_types = {
55 'Sybase' => ':server',
59 my $dsn = 'dbi:'. $args{dbd};
61 my $dbi_connect_type = $dbi_connect_types{$args{'dbd'}} ? $dbi_connect_types{$args{'dbd'}} : ':host';
62 $dsn .= $dbi_connect_type . "=$opt{H}";
63 $dsn .= ";database=$opt{D}" if $opt{D};
65 my $dbi = DBI->connect($dsn, $opt{U}, $opt{P})
70 #my $fsdbh = FS::UID::dbh;
72 my $table = $opt{T} || $args{table};
73 my $pkey = $args{primary_key};
75 #just doing this manually with IVR MSSQL databases for now
76 # # check for existence of freesidestatus
77 # my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
79 # print "Adding freesidestatus column...\n";
80 # $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
81 # or die $dbi->errstr;
84 # print "freesidestatus column present\n";
86 # or if using a status_table:
87 # CREATE TABLE FREESIDE_BILLING (
89 # FREESIDESTATUS VARCHAR(32)
92 #my @cols = values %{ $args{column_map} };
93 my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ".
94 $sql .= 'LEFT JOIN '. $args{status_table}.
95 " ON ( $table.$pkey = ". $args{status_table}. ".$pkey )"
96 if $args{status_table};
97 $sql .= ' WHERE freesidestatus IS NULL ';
99 #$sql .= ' LIMIT '. $opt{L} if $opt{L};
100 my $sth = $dbi->prepare($sql);
101 $sth->execute or die $sth->errstr. " executing $sql";
102 #MySQL-specific print "Importing ".$sth->rows." records...\n";
104 my $cdr_batch = new FS::cdr_batch({
105 'cdrbatch' => $args{batch_name} . '-import-'. time2str('%Y/%m/%d-%T',time),
107 my $error = $cdr_batch->insert;
108 die $error if $error;
109 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
113 while ( $row = $sth->fetchrow_hashref ) {
115 my %hash = ( 'cdrbatchnum' => $cdrbatchnum );
116 foreach my $field ( keys %{ $args{column_map} } ) {
117 my $col_or_coderef = $args{column_map}->{$field};
118 if ( ref($col_or_coderef) eq 'CODE' ) {
119 $hash{$field} = &{ $col_or_coderef }( $row );
121 $hash{$field} = $row->{ $col_or_coderef };
123 $hash{$field} = '' if $hash{$field} =~ /^\s+$/; #IVR (MSSQL?) bs
125 my $cdr = FS::cdr->new(\%hash);
127 $cdr->cdrtypenum($opt{c}) if $opt{c};
129 my $pkey_value = $row->{$pkey};
131 #print "$pkey_value\n" if $opt{v};
132 my $error = $cdr->insert;
136 #die "$pkey_value: failed import: $error\n";
137 print "$pkey_value: failed import: $error\n";
144 if ( $args{status_table} ) {
147 'INSERT INTO '. $args{status_table}. " ( $pkey, freesidestatus ) ".
148 " VALUES ( ?, 'done' )";
152 $st_sql = "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?";
156 my $updated = $dbi->do($st_sql, undef, $pkey_value );
157 #$updates += $updated;
158 die "failed to set status: ".$dbi->errstr."\n" unless $updated;
162 if ( $opt{L} && $imported >= $opt{L} ) {
169 print "Imported $imported CDRs.\n" if $imported;
176 #"Usage: \n $0\n\t[ -H hostname ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
177 #"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";
178 "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";
183 Not everything has been refactored out of the various bin/cdr-*.import scripts,
184 let alone other places.
186 Sparse documentation.