From 033547be1c5a0ba76c5f578fd7d656735e12a9bd Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Tue, 17 Jun 2014 05:31:46 -0700 Subject: [PATCH] IVR CDR import, RT#29055 --- FS/FS/cdr.pm | 4 +- FS/FS/cdr/Import.pm | 161 ++++++++++++++++++++++++++++++++++++++++++++++++++++ bin/cdr-ivr.import | 70 +++++++++++++++++++++++ 3 files changed, 233 insertions(+), 2 deletions(-) create mode 100644 FS/FS/cdr/Import.pm create mode 100755 bin/cdr-ivr.import diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm index 3af776b70..0771a7b32 100644 --- a/FS/FS/cdr.pm +++ b/FS/FS/cdr.pm @@ -1519,8 +1519,8 @@ as keys (for use with batch_import) and "pretty" format names as values. my %cdr_info; foreach my $INC ( @INC ) { - warn "globbing $INC/FS/cdr/*.pm\n" if $DEBUG; - foreach my $file ( glob("$INC/FS/cdr/*.pm") ) { + warn "globbing $INC/FS/cdr/[a-z]*.pm\n" if $DEBUG; + foreach my $file ( glob("$INC/FS/cdr/[a-z]*.pm") ) { warn "attempting to load CDR format info from $file\n" if $DEBUG; $file =~ /\/(\w+)\.pm$/ or do { warn "unrecognized file in $INC/FS/cdr/: $file\n"; diff --git a/FS/FS/cdr/Import.pm b/FS/FS/cdr/Import.pm new file mode 100644 index 000000000..e13c68270 --- /dev/null +++ b/FS/FS/cdr/Import.pm @@ -0,0 +1,161 @@ +package FS::cdr::Import; + +use strict; +use Date::Format 'time2str'; +use FS::UID qw(adminsuidsetup dbh); +use FS::cdr; +use DBI; +use Getopt::Std; + +use vars qw( $DEBUG ); +$DEBUG = 0; + +=head1 NAME + +FS::cdr::Import - CDR importing + +=head1 SYNOPSIS + + use FS::cdr::Import; + + FS::cdr::Import->dbi_import( + 'dbd' => 'mysql', #Pg, Sybase, etc. + 'table' => 'TABLE_NAME', + 'primary_key' => 'BILLING_ID', + 'column_map' => { #freeside => remote_db + 'freeside_column' => 'remote_db_column', + 'freeside_column' => sub { my $row = shift; $row->{remote_db_column}; }, + }, + ); + +=head1 DESCRIPTION + +CDR importing + +=head1 CLASS METHODS + +=item do_cli_import + +=cut + +sub dbi_import { + my $class = shift; + 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); + my $user = shift(@ARGV) or die $class->cli_usage; + + $opt{D} ||= $args{database}; + + my $dsn = 'dbi:'. $args{dbd}; + #$dsn .= ":host=$opt{H}"; #if $opt{H}; + $dsn .= ":server=$opt{H}"; #if $opt{H}; + $dsn .= ";database=$opt{D}" if $opt{D}; + + my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) + or die $DBI::errstr; + + adminsuidsetup $user; + + #my $fsdbh = FS::UID::dbh; + + my $table = $opt{T} || $args{table}; + my $pkey = $args{primary_key}; + + #just doing this manually with IVR MSSQL databases for now + # # check for existence of freesidestatus + # my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'"); + # if( ! @$status ) { + # print "Adding freesidestatus column...\n"; + # $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)") + # or die $dbi->errstr; + # } + # else { + # print "freesidestatus column present\n"; + # } + + #my @cols = values %{ $args{column_map} }; + my $sql = "SELECT * FROM $table ". # join(',', @cols). " FROM $table ". + + ' 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), + }); + my $error = $cdr_batch->insert; + die $error if $error; + my $cdrbatchnum = $cdr_batch->cdrbatchnum; + my $imported = 0; + + my $row; + while ( $row = $sth->fetchrow_hashref ) { + + my %hash = ( 'cdrbatchnum' => $cdrbatchnum ); + foreach my $field ( keys %{ $args{column_map} } ) { + my $col_or_coderef = $args{column_map}->{$field}; + if ( ref($col_or_coderef) eq 'CODE' ) { + $hash{$field} = &{ $col_or_coderef }( $row ); + } else { + $hash{$field} = $row->{ $col_or_coderef }; + } + $hash{$field} = '' if $hash{$field} =~ /^\s+$/; #IVR (MSSQL?) bs + } + my $cdr = FS::cdr->new(\%hash); + + $cdr->cdrtypenum($opt{c}) if $opt{c}; + + #print $row->{$pkey},"\n" if $opt{v}; + my $error = $cdr->insert; + if ($error) { + #die $row->{$pkey} . ": failed import: $error\n"; + print $row->{$pkey} . ": failed import: $error\n"; + } else { + $imported++; + + my $updated = $dbi->do( + "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?", + undef, + $row->{'$pkey'} + ); + #$updates += $updated; + die "failed to set status: ".$dbi->errstr."\n" unless $updated; + } + + if ( $opt{L} && $imported >= $opt{L} ) { + $sth->finish; + last; + } + + } + print "Done.\n"; + print "Imported $imported CDRs.\n" if $imported; + + $dbi->disconnect; + +} + +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"; +} + +=head1 BUGS + +Not everything has been refactored out of the various bin/cdr-*.import scripts, +let alone other places. + +Sparse documentation. + +=head1 SEE ALSO + +L + +=cut + +1; diff --git a/bin/cdr-ivr.import b/bin/cdr-ivr.import new file mode 100755 index 000000000..07c180b1e --- /dev/null +++ b/bin/cdr-ivr.import @@ -0,0 +1,70 @@ +#!/usr/bin/perl + +use strict; +use Date::Parse 'str2time'; +use FS::cdr::Import; + +FS::cdr::Import->dbi_import( + 'dbd' => 'Sybase', + 'database' => 'TEL_DATA', + 'table' => 'BILLING', + 'primary_key' => 'BILLING_ID', + 'column_map' => { #freeside => IVR + 'cdrid' => 'BILLING_ID', #Primary key + #'' => 'CALL_SESSION_ID', # Call Session Id (unique per call session – GUID) + 'uniqueid' => 'CALL_ID', # + #'' => 'ENTRY_TYPE', # + 'accountcode' => 'ACCOUNT_ID', # + #'' => 'ACCOUNT', # + #'' => 'ACCOUNT_GROUP', # + 'startdate' => sub { str2time(shift->{'START_DATE_TIME'}); }, + 'answerdate' => sub { str2time(shift->{'CONNECT_DATE_TIME'}); }, + 'enddate' => sub { str2time(shift->{'DISCONNECT_DATE_TIME'}); }, + #'' => 'PARENT_ACCOUNT_ID', # + #'' => 'PARENT_ACCOUNT', # + #'' => 'LOGIN_NAME', # + #varchars not ints 'upstream_rateid' => 'RATE_SCHEDULE', # + #varchars not ints 'upstream_rateplanid' => 'RATE_PLAN', # + 'channel' => 'NODE', # + #'' => 'NODE_TYPE', # + #'' => 'ORIGIN', # + #'dst' => sub { $_[0]->{COUNTRY_CODE}. $_[0]->{NPA}. + # $_[0]->{NXX}. $_[0]->{LOCAL_NUMBER}; + # }, + 'description' => 'DESCRIPTION', # or upstream_dst_regionname ? + #'' => 'DETAIL', # + #'' => 'PER_CALL_CHARGE', # + #'' => 'PER_MINUTE_CHARGE', # + #'' => 'PER_CALL_SURCHARGE', # + #'' => 'PER_MINUTE_SURCHARGE', # + 'duration' => 'ACTUAL_DURATION', # + 'billsec' => sub { int( shift->{'QUANTITY'} * 60 + .49 ); }, # + 'upstream_price' => 'AMOUNT', # + #'' => 'PACKAGED_BALANCE_INDEX', # + 'upstream_currency' => 'CURRENCY', # + #'' => 'CONVERSION_RATE', # + 'lastapp' => 'MODULE_NAME', # + 'src' => 'ANI', # 'clid' => 'ANI', # + 'dst' => 'DNIS', + #'' => 'SALES_GROUP', # + #'' => 'TAX_GROUP', # + 'userfield' => 'USER_1', # + #'' => 'USER_2', # + #'' => 'USER_3', # + #'' => 'USER_4', # + #'' => 'USER_5', # + #'' => 'USER_6', # + #'' => 'USER_7', # + #'' => 'USER_8', # + #'' => 'USER_9', # + #'' => 'USER_10', # + #'' => 'INFO_DIGITS', # VARCHAR(3) Info digits from the inbound leg, if applicable + #'' => 'RATE_INTERVAL', # TINYINT Rate interval used to bill the call (default: 60 seconds) ... create a new "upstream_granularity" field if we need this + #'' => 'DISCONNECT_CHARGE', # DECIMAL(15,4) The disconnect charge billed for the call + #'' => 'BILLING_DELAY', # SMALLINT Billing delay associated with the call + #'' => 'GRACE_PERIOD', # SMALLINT Grace period associated with the call + #'' => 'ACCOUNT_TYPE', # Account type from ACCOUNT_TYPES table + }, +); + +1; -- 2.11.0