'dbd' => 'mysql', #Pg, Sybase, etc.
'table' => 'TABLE_NAME',
'primary_key' => 'BILLING_ID',
+ 'status_table' = > 'STATUS_TABLE_NAME', # if using a table rather than field in main table
'column_map' => { #freeside => remote_db
'freeside_column' => 'remote_db_column',
'freeside_column' => sub { my $row = shift; $row->{remote_db_column}; },
},
+ 'batch_name' => 'batch_name', # cdr_batch name -import-date gets appended.
);
=head1 DESCRIPTION
=head1 CLASS METHODS
-=item do_cli_import
+=item dbi_import
=cut
my %args = @_; #args are specifed by the script using this sub
my %opt; #opt is specified for each install / run of the script
- getopts('H:U:P:D:T:c:L:', \%opt);
+ getopts('H:U:P:D:T:c:L:S:', \%opt);
my $user = shift(@ARGV) or die $class->cli_usage;
$opt{D} ||= $args{database};
+ #do we want to add more types? or add as we go?
+ my %dbi_connect_types = {
+ 'Sybase' => ':server',
+ 'Pg' => ':host',
+ };
+
my $dsn = 'dbi:'. $args{dbd};
- #$dsn .= ":host=$opt{H}"; #if $opt{H};
- $dsn .= ":server=$opt{H}"; #if $opt{H};
+
+ my $dbi_connect_type = $dbi_connect_types{$args{'dbd'}} ? $dbi_connect_types{$args{'dbd'}} : ':host';
+ $dsn .= $dbi_connect_type . "=$opt{H}";
$dsn .= ";database=$opt{D}" if $opt{D};
my $dbi = DBI->connect($dsn, $opt{U}, $opt{P})
my $table = $opt{T} || $args{table};
my $pkey = $args{primary_key};
+ my $status_table = $opt{S} || $args{status_table};
#just doing this manually with IVR MSSQL databases for now
# # check for existence of freesidestatus
# else {
# print "freesidestatus column present\n";
# }
+ # or if using a status_table:
+ # CREATE TABLE FREESIDE_BILLING (
+ # BILLING_ID BIGINT,
+ # FREESIDESTATUS VARCHAR(32)
+ # )
#my @cols = values %{ $args{column_map} };
- my $sql = "SELECT * FROM $table ". # join(',', @cols). " FROM $table ".
+ my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ".
+ $sql .= 'LEFT JOIN '. $status_table.
+ " ON ( $table.$pkey = ". $status_table. ".$pkey )"
+ if $status_table;
+ $sql .= ' WHERE freesidestatus IS NULL ';
- ' WHERE freesidestatus IS NULL ';
#$sql .= ' LIMIT '. $opt{L} if $opt{L};
my $sth = $dbi->prepare($sql);
$sth->execute or die $sth->errstr. " executing $sql";
#MySQL-specific print "Importing ".$sth->rows." records...\n";
my $cdr_batch = new FS::cdr_batch({
- 'cdrbatch' => 'IVR-import-'. time2str('%Y/%m/%d-%T',time),
+ 'cdrbatch' => $args{batch_name} . '-import-'. time2str('%Y/%m/%d-%T',time),
});
my $error = $cdr_batch->insert;
die $error if $error;
$cdr->cdrtypenum($opt{c}) if $opt{c};
- #print $row->{$pkey},"\n" if $opt{v};
+ my $pkey_value = $row->{$pkey};
+
+ #print "$pkey_value\n" if $opt{v};
my $error = $cdr->insert;
+
if ($error) {
- #die $row->{$pkey} . ": failed import: $error\n";
- print $row->{$pkey} . ": failed import: $error\n";
+
+ #die "$pkey_value: failed import: $error\n";
+ print "$pkey_value: failed import: $error\n";
+
} else {
+
$imported++;
- my $updated = $dbi->do(
- "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?",
- undef,
- $row->{'$pkey'}
- );
+ my $st_sql;
+ if ( $status_table ) {
+
+ $st_sql =
+ 'INSERT INTO '. $status_table. " ( $pkey, freesidestatus ) ".
+ " VALUES ( ?, 'done' )";
+
+ } else {
+
+ $st_sql = "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?";
+
+ }
+
+ my $updated = $dbi->do($st_sql, undef, $pkey_value );
#$updates += $updated;
die "failed to set status: ".$dbi->errstr."\n" unless $updated;
+
}
if ( $opt{L} && $imported >= $opt{L} ) {
sub cli_usage {
#"Usage: \n $0\n\t[ -H hostname ]\n\t-D database\n\t-U user\n\t-P password\n\tfreesideuser\n";
#"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";
- "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";
+ "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";
}
=head1 BUGS
=cut
-1;
+1;
\ No newline at end of file