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:S:', \%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};
74 my $status_table = $opt{S} || $args{status_table};
76 #just doing this manually with IVR MSSQL databases for now
77 # # check for existence of freesidestatus
78 # my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
80 # print "Adding freesidestatus column...\n";
81 # $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
82 # or die $dbi->errstr;
85 # print "freesidestatus column present\n";
87 # or if using a status_table:
88 # CREATE TABLE FREESIDE_BILLING (
90 # FREESIDESTATUS VARCHAR(32)
93 #my @cols = values %{ $args{column_map} };
94 my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ".
95 $sql .= 'LEFT JOIN '. $status_table.
96 " ON ( $table.$pkey = ". $status_table. ".$pkey )"
98 $sql .= ' WHERE freesidestatus IS NULL ';
100 #$sql .= ' LIMIT '. $opt{L} if $opt{L};
101 my $sth = $dbi->prepare($sql);
102 $sth->execute or die $sth->errstr. " executing $sql";
103 #MySQL-specific print "Importing ".$sth->rows." records...\n";
105 my $cdr_batch = new FS::cdr_batch({
106 'cdrbatch' => $args{batch_name} . '-import-'. time2str('%Y/%m/%d-%T',time),
108 my $error = $cdr_batch->insert;
109 die $error if $error;
110 my $cdrbatchnum = $cdr_batch->cdrbatchnum;
114 while ( $row = $sth->fetchrow_hashref ) {
116 my %hash = ( 'cdrbatchnum' => $cdrbatchnum );
117 foreach my $field ( keys %{ $args{column_map} } ) {
118 my $col_or_coderef = $args{column_map}->{$field};
119 if ( ref($col_or_coderef) eq 'CODE' ) {
120 $hash{$field} = &{ $col_or_coderef }( $row );
122 $hash{$field} = $row->{ $col_or_coderef };
124 $hash{$field} = '' if $hash{$field} =~ /^\s+$/; #IVR (MSSQL?) bs
126 my $cdr = FS::cdr->new(\%hash);
128 $cdr->cdrtypenum($opt{c}) if $opt{c};
130 my $pkey_value = $row->{$pkey};
132 #print "$pkey_value\n" if $opt{v};
133 my $error = $cdr->insert;
137 #die "$pkey_value: failed import: $error\n";
138 print "$pkey_value: failed import: $error\n";
145 if ( $status_table ) {
148 'INSERT INTO '. $status_table. " ( $pkey, freesidestatus ) ".
149 " VALUES ( ?, 'done' )";
153 $st_sql = "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?";
157 my $updated = $dbi->do($st_sql, undef, $pkey_value );
158 #$updates += $updated;
159 die "failed to set status: ".$dbi->errstr."\n" unless $updated;
163 if ( $opt{L} && $imported >= $opt{L} ) {
170 print "Imported $imported CDRs.\n" if $imported;
177 #"Usage: \n $0\n\t[ -H hostname ]\n\t-D database\n\t-U user\n\t-P password\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\tfreesideuser\n";
179 "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\t[ -T table ]\n\t[ -S status table ]\n\tfreesideuser\n";
184 Not everything has been refactored out of the various bin/cdr-*.import scripts,
185 let alone other places.
187 Sparse documentation.