From 6815fb35400b1f36abbbb7f392f8be348fef14b9 Mon Sep 17 00:00:00 2001 From: Christopher Burger Date: Fri, 19 Jan 2018 15:52:53 -0500 Subject: [PATCH] RT# 76398 - Added auto status field or status table creation Conflicts: FS/FS/cdr/Import.pm FS/bin/freeside-cdr-freeswitch --- FS/FS/cdr/Import.pm | 135 ++++++++++++++++++++++++++++++----------- FS/bin/freeside-cdr-freeswitch | 74 ++++++++++++++++++++++ 2 files changed, 175 insertions(+), 34 deletions(-) create mode 100644 FS/bin/freeside-cdr-freeswitch diff --git a/FS/FS/cdr/Import.pm b/FS/FS/cdr/Import.pm index 39a7772a3..6f5062d9e 100644 --- a/FS/FS/cdr/Import.pm +++ b/FS/FS/cdr/Import.pm @@ -19,7 +19,7 @@ FS::cdr::Import - CDR importing use FS::cdr::Import; FS::cdr::Import->dbi_import( - 'dbd' => 'mysql', #Pg, Sybase, etc. + 'dbd' => 'Pg', #mysql, Sybase, etc. 'table' => 'TABLE_NAME', 'primary_key' => 'BILLING_ID', 'column_map' => { #freeside => remote_db @@ -43,42 +43,61 @@ sub dbi_import { 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}; + getopts('H:U:P:D:T:c:L:S:', \%opt); - 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 $user = shift(@ARGV) or die $class->cli_usage; + my $database = $opt{D} || $args{database}; + my $table = $opt{T} || $args{table}; + my $pkey = $args{primary_key}; + my $pkey_info = $args{primary_key_info} ? $args{primary_key_info} : 'BIGINT'; + my $status_table = $opt{S} || $args{status_table}; + my $dbd_type = $args{'dbd'} ? $args{'dbd'} : 'Pg'; + my $status_column = $args{status_column} ? $args{status_column} : 'freesidestatus'; + my $status_column_info = $args{status_column_info} ? $args{status_column} : 'VARCHAR(32)'; + + my $queries = get_queries({ + 'dbd' => $dbd_type, + 'table' => $table, + 'status_column' => $status_column, + 'status_column_info' => $status_column_info, + 'status_table' => $status_table, + 'primary_key' => $pkey, + 'primary_key_info' => $pkey_info, + }); + + my $dsn = 'dbi:'. $dbd_type; + $dsn .= $queries->{connect_type} . "=$opt{H}"; + $dsn .= ";database=$database" if $database; 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}; + ## check for status table if using. if not there create it. + if ($status_table) { + my $status = $dbi->selectall_arrayref( $queries->{check_statustable} ); + if( ! @$status ) { + print "Adding status table $status_table ...\n"; + $dbi->do( $queries->{create_statustable} ) + or die $dbi->errstr; + } + } + ## check for column freeside status if not using status table and create it if not there. + else { + my $status = $dbi->selectall_arrayref( $queries->{check_statuscolumn} ); + if( ! @$status ) { + print "Adding $status_column column...\n"; + $dbi->do( $queries->{create_statuscolumn} ) + or die $dbi->errstr; + } + } - #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"; - # } - # 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 $table.* FROM $table "; # join(',', @cols). " FROM $table ". + $sql .= "LEFT JOIN $status_table ON ( $table.$pkey = $status_table.$pkey ) " + if $status_table; + $sql .= "WHERE $status_column IS NULL "; #my @cols = values %{ $args{column_map} }; my $sql = "SELECT $table.* FROM $table "; # join(',', @cols). " FROM $table ". @@ -90,7 +109,6 @@ sub dbi_import { #$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), @@ -113,6 +131,7 @@ sub dbi_import { } $hash{$field} = '' if $hash{$field} =~ /^\s+$/; #IVR (MSSQL?) bs } + my $cdr = FS::cdr->new(\%hash); $cdr->cdrtypenum($opt{c}) if $opt{c}; @@ -135,12 +154,12 @@ sub dbi_import { if ( $args{status_table} ) { $st_sql = - 'INSERT INTO '. $args{status_table}. " ( $pkey, freesidestatus ) ". + 'INSERT INTO '. $status_table. " ( $pkey, $status_column ) ". " VALUES ( ?, 'done' )"; } else { - $st_sql = "UPDATE $table SET freesidestatus = 'done' WHERE $pkey = ?"; + $st_sql = "UPDATE $table SET $status_column = 'done' WHERE $pkey = ?"; } @@ -169,10 +188,58 @@ sub cli_usage { "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"; } +sub get_queries { + #my ($dbd, $table, $column, $column_create_info, $status_table, $primary_key, $primary_key_info) = @_; + my $info = shift; + + #do we want to add more types? or add as we go? + my %dbi_connect_types = ( + 'Sybase' => ':server', + 'Pg' => ':host', + ); + + #Check for freeside status table Sybase has not been tested + my %dbi_check_statustable = ( + 'Sybase' => "SELECT systables.name FROM sysobjects + JOIN systables ON sysobjects.id = systables.id + WHERE sysobjects.name LIKE '$info->{table}' AND systables.name = $info->{status_table}", + 'Pg' => "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$info->{status_table}' AND column_name = '$info->{status_column}'", + ); + + #Check for freeside status table Sybase has not been tested + my %dbi_create_statustable = ( + 'Sybase' => "CREATE TABLE $info->{status_table} ( $info->{primary_key} $info->{primary_key_info}, $info->{status_column} $info->{status_column_info} )", + 'Pg' => "CREATE TABLE $info->{status_table} ( $info->{primary_key} $info->{primary_key_info}, $info->{status_column} $info->{status_column_info} )", + ); + + #Check for freeside status column Sybase has not been tested + my %dbi_check_statuscolumn = ( + 'Sybase' => "SELECT syscolumns.name FROM sysobjects + JOIN syscolumns ON sysobjects.id = syscolumns.id + WHERE sysobjects.name LIKE '$info->{table}' AND syscolumns.name = $info->{status_column}", + 'Pg' => "SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '$info->{table}' AND column_name = '$info->{status_column}' ", + ); + + #Check for freeside status column Sybase has not been tested + my %dbi_create_statuscolumn = ( + 'Sybase' => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}", + 'Pg' => "ALTER TABLE $info->{table} ADD COLUMN $info->{status_column} $info->{status_column_info}", + ); + + my $queries = { + 'connect_type' => $dbi_connect_types{$info->{dbd}}, + 'check_statustable' => $dbi_check_statustable{$info->{dbd}}, + 'create_statustable' => $dbi_create_statustable{$info->{dbd}}, + 'check_statuscolumn' => $dbi_check_statuscolumn{$info->{dbd}}, + 'create_statuscolumn' => $dbi_create_statuscolumn{$info->{dbd}}, + }; + + return $queries; +} + =head1 BUGS -Not everything has been refactored out of the various bin/cdr-*.import scripts, -let alone other places. +This has only been test with Pg -> postgresql databases Sparse documentation. diff --git a/FS/bin/freeside-cdr-freeswitch b/FS/bin/freeside-cdr-freeswitch new file mode 100644 index 000000000..51f360ca7 --- /dev/null +++ b/FS/bin/freeside-cdr-freeswitch @@ -0,0 +1,74 @@ +#!/usr/bin/perl + +use strict; +use Date::Parse 'str2time'; +use FS::cdr::Import; + +FS::cdr::Import->dbi_import( + 'dbd' => 'Pg', + 'database' => 'fusionpbx', + 'table' => 'v_xml_cdr', + 'status_column' => 'freesidestatus', + 'primary_key' => 'uuid',:q + 'column_map' => { #freeside => fusionpbx + #'cdrid' => 'uuid', #Primary key + #'' => 'CALL_SESSION_ID', # Call Session Id (unique per call session – GUID) + 'uniqueid' => 'uuid', # + #'' => 'ENTRY_TYPE', # + 'accountcode' => 'accountcode', # + #'' => 'ACCOUNT', # + #'' => 'ACCOUNT_GROUP', # + 'startdate' => sub { str2time(shift->{'start_stamp'}); }, + 'answerdate' => sub { str2time(shift->{'answer_stamp'}); }, + 'enddate' => sub { str2time(shift->{'end_stamp'}); }, + #'' => '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 ? + #'' => 'PER_CALL_CHARGE', # + #'' => 'PER_MINUTE_CHARGE', # + #'' => 'PER_CALL_SURCHARGE', # + #'' => 'PER_MINUTE_SURCHARGE', # + 'duration' => 'duration', # + 'billsec' => sub { int( shift->{'billsec'} * 60 + .49 ); }, # + #'upstream_price' => 'AMOUNT', # + #'' => 'PACKAGED_BALANCE_INDEX', # + #'upstream_currency' => 'CURRENCY', # + #'' => 'CONVERSION_RATE', # + 'lastapp' => 'last_app', # + #'src' => 'ANI', # 'clid' => 'ANI', # + 'dst' => sub { $_[0]->{'COUNTRY_CODE'} !~ /^1/ + ? "011". $_[0]->{'DETAIL'} + : $_[0]->{'DETAIL'}; + }, + #'' => '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 + }, + 'batch_name' => 'fusionpbx', +); + +1; \ No newline at end of file -- 2.11.0