From 1d5f7cb129a7fade6ef9283977b2781ece183797 Mon Sep 17 00:00:00 2001 From: ivan Date: Fri, 2 Jan 2009 22:03:58 +0000 Subject: [PATCH] add troop CDRs, RT#4413 --- FS/FS/Record.pm | 131 +++++++++++++++++----------------- FS/FS/cdr.pm | 61 ++++++++++------ FS/FS/cdr/bell_west.pm | 25 ++++--- FS/FS/cdr/troop.pm | 186 ++++++++++++++++++++++++++++-------------------- bin/cdr.import | 8 +-- bin/cdr.sftp_and_import | 10 ++- 6 files changed, 237 insertions(+), 184 deletions(-) diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index f0b2efe90..3327f18dc 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -1431,47 +1431,23 @@ sub process_batch_import { my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/'; my $file = $dir. $files{'file'}; - my $type = $opt->{'format_types'} - ? $opt->{'format_types'}{ $param->{'format'} } - : ''; - - unless ( $type ) { - if ( $file =~ /\.(\w+)$/i ) { - $type = lc($1); - } else { - #or error out??? - warn "can't parse file type from filename $file; defaulting to CSV"; - $type = 'csv'; - } - $type = 'csv' - if $opt->{'default_csv'} && $type ne 'xls'; - } - - my $header = $opt->{'format_headers'} - ? $opt->{'format_headers'}{ $param->{'format'} } - : 0; - - my $sep_char = $opt->{'format_sep_chars'} - ? $opt->{'format_sep_chars'}{ $param->{'format'} } - : ','; - - my $fixedlength_format = - $opt->{'format_fixedlength_formats'} - ? $opt->{'format_fixedlength_formats'}{ $param->{'format'} } - : ''; - my $error = FS::Record::batch_import( { - table => $table, - formats => \%formats, - job => $job, - file => $file, - type => $type, - format => $param->{format}, - header => $header, - sep_char => $sep_char, - fixedlength_format => $fixedlength_format, - params => { map { $_ => $param->{$_} } @pass_params }, + #class-static + table => $table, + formats => \%formats, + format_types => $opt->{format_types}, + format_headers => $opt->{format_headers}, + format_sep_chars => $opt->{format_sep_chars}, + format_fixedlength_formats => $opt->{format_fixedlength_formats}, + #per-import + job => $job, + file => $file, + #type => $type, + format => $param->{format}, + params => { map { $_ => $param->{$_} } @pass_params }, + #? + default_csv => $opt->{default_csv}, } ); unlink $file; @@ -1489,13 +1465,21 @@ Class method for batch imports. Available params: =item formats +=item format_types + +=item format_headers + +=item format_sep_chars + +=item format_fixedlength_formats + =item params =item job FS::queue object, will be updated with progress -=item filename +=item file =item type @@ -1503,12 +1487,6 @@ csv, xls or fixedlength =item format -=item header - -=item sep_char - -=item fixedlength_format - =item empty_ok =back @@ -1521,19 +1499,46 @@ sub batch_import { warn "$me batch_import call with params: \n". Dumper($param) if $DEBUG; - my $table = $param->{table}; - my $formats = $param->{formats}; - my $params = $param->{params}; + my $table = $param->{table}; + my $formats = $param->{formats}; - my $job = $param->{job}; + my $job = $param->{job}; + my $file = $param->{file}; + my $format = $param->{'format'}; + my $params = $param->{params}; - my $filename = $param->{file}; - my $type = $param->{type} || 'csv'; + die "unknown format $format" unless exists $formats->{ $format }; - my $format = $param->{'format'}; + my $type = $param->{'format_types'} + ? $param->{'format_types'}{ $format } + : $param->{type} || 'csv'; - die "unknown format $format" unless exists $formats->{ $format }; - my @fields = @{ $formats->{ $format } }; + unless ( $type ) { + if ( $file =~ /\.(\w+)$/i ) { + $type = lc($1); + } else { + #or error out??? + warn "can't parse file type from filename $file; defaulting to CSV"; + $type = 'csv'; + } + $type = 'csv' + if $param->{'default_csv'} && $type ne 'xls'; + } + + my $header = $param->{'format_headers'} + ? $param->{'format_headers'}{ $param->{'format'} } + : 0; + + my $sep_char = $param->{'format_sep_chars'} + ? $param->{'format_sep_chars'}{ $param->{'format'} } + : ','; + + my $fixedlength_format = + $param->{'format_fixedlength_formats'} + ? $param->{'format_fixedlength_formats'}{ $param->{'format'} } + : ''; + + my @fields = @{ $formats->{ $format } }; my $row = 0; my $count; @@ -1544,24 +1549,21 @@ sub batch_import { if ( $type eq 'csv' ) { my %attr = (); - foreach ( grep exists($param->{$_}), qw( sep_char ) ) { - $attr{$_} = $param->{$_}; - } - + $attr{sep_char} = $sep_char if $sep_char; $parser = new Text::CSV_XS \%attr; } elsif ( $type eq 'fixedlength' ) { eval "use Parse::FixedLength;"; die $@ if $@; - $parser = new Parse::FixedLength $param->{'fixedlength_format'}; + $parser = new Parse::FixedLength $fixedlength_format; } else { die "Unknown file type $type\n"; } - @buffer = split(/\r?\n/, slurp($filename) ); - splice(@buffer, 0, ($param->{'header'} || 0) ); + @buffer = split(/\r?\n/, slurp($file) ); + splice(@buffer, 0, ($header || 0) ); $count = scalar(@buffer); } elsif ( $type eq 'xls' ) { @@ -1574,14 +1576,14 @@ sub batch_import { # formats bill_west and troop use it, not other excel-parsing things #die $@ if $@; - my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($filename); + my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($file); $parser = $excel->{Worksheet}[0]; #first sheet $count = $parser->{MaxRow} || $parser->{MinRow}; $count++; - $row = $param->{'header'} || 0; + $row = $header || 0; } else { die "Unknown file type $type\n"; @@ -1647,6 +1649,7 @@ sub batch_import { #&{$field}(\%hash, $value); push @later, $field, $value; } else { + #??? $hash{$field} = $value if length($value); $hash{$field} = $value if defined($value) && length($value); } diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm index c2a3d00ee..5bfd91da8 100644 --- a/FS/FS/cdr.pm +++ b/FS/FS/cdr.pm @@ -672,7 +672,7 @@ Imports CDR records. Available options are: =over 4 -=item filehandle +=item file =item format @@ -680,35 +680,56 @@ Imports CDR records. Available options are: =cut -sub process_batch_import { - my $job = shift; - my $opt = { - 'table' => 'cdr', - 'params' => [ 'format', 'cdrbatch' ], +my %import_options = ( + 'table' => 'cdr', + + 'formats' => { map { $_ => $cdr_info{$_}->{'import_fields'}; } + keys %cdr_info + }, - 'formats' => { map { $_ => $cdr_info{$_}->{'import_fields'}; } - keys %cdr_info - }, + #drop the || 'csv' to allow auto xls for csv types? + 'format_types' => { map { $_ => ( lc($cdr_info{$_}->{'type'}) || 'csv' ); } + keys %cdr_info + }, - #drop the || 'csv' to allow auto xls for csv types? - 'format_types' => { map { $_ => ( lc($cdr_info{$_}->{'type'}) || 'csv' ); } + 'format_headers' => { map { $_ => ( $cdr_info{$_}->{'header'} || 0 ); } keys %cdr_info }, - 'format_headers' => { map { $_ => ( $cdr_info{$_}->{'header'} || 0 ); } + 'format_sep_chars' => { map { $_ => $cdr_info{$_}->{'sep_char'}; } keys %cdr_info }, - 'format_sep_chars' => { map { $_ => $cdr_info{$_}->{'sep_char'}; } - keys %cdr_info - }, + 'format_fixedlength_formats' => + { map { $_ => $cdr_info{$_}->{'fixedlength_format'}; } + keys %cdr_info + }, +); - 'format_fixedlength_formats' => - { map { $_ => $cdr_info{$_}->{'fixedlength_format'}; } - keys %cdr_info - }, - }; +sub _import_options { + \%import_options; +} + +sub batch_import { + my $opt = shift; + + my $iopt = _import_options; + $opt->{$_} = $iopt->{$_} foreach keys %$iopt; + + FS::Record::batch_import( $opt ); + +} + +=item process_batch_import + +=cut + +sub process_batch_import { + my $job = shift; + + my $opt = _import_options; + $opt->{'params'} = [ 'format', 'cdrbatch' ]; FS::Record::process_batch_import( $job, $opt, @_ ); diff --git a/FS/FS/cdr/bell_west.pm b/FS/FS/cdr/bell_west.pm index 4c3899bbf..f745bb190 100644 --- a/FS/FS/cdr/bell_west.pm +++ b/FS/FS/cdr/bell_west.pm @@ -4,13 +4,13 @@ use strict; use base qw( FS::cdr ); use vars qw( %info $tmp_mon $tmp_mday $tmp_year ); use Time::Local; -use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker ); +#use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker ); %info = ( 'name' => 'Bell West', 'weight' => 210, - 'header' => 1, #0 default, set to 1 to ignore the first line - 'type' => 'xls', #csv (default), fixedlength or xls + 'header' => 1, + 'type' => 'xls', 'import_fields' => [ @@ -23,13 +23,9 @@ use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker ); sub {}, # DATE / Yes / "DATE" Excel date format MM/DD/YYYY + # XXX false laziness w/troop.pm sub { my($cdr, $date) = @_; - #$date =~ /^(\d{1,2})\/(\d{1,2})\/(\d\d(\d\d)?)$/ - # or die "unparsable date: $date"; #maybe we shouldn't die... - ##$cdr->startdate( timelocal(0, 0, 0 ,$2, $1-1, $3) ); - #($tmp_mday, $tmp_mon, $tmp_year) = ( $2, $1-1, $3 ); - my $datetime = DateTime::Format::Excel->parse_datetime( $date ); $tmp_mon = $datetime->mon_0; $tmp_mday = $datetime->mday; @@ -49,22 +45,24 @@ use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker ); ); }, - # BTN / Yes / Main billing number but not DID or real number (I guess put in SRC field) + # BTN / Yes / Main billing number but not DID or real number + # (put in SRC field) 'src', # ORIG CITY / No / We will use your Freeside rating and description name 'channel', - # TERM / YES / All calls should be billed, however all calls are missing "1+" and "011+" & DIR ASST = "411" + # TERM / YES / All calls should be billed, however all calls are + # missing "1+" and "011+" & DIR ASST = "411" 'dst', # TERM CITY / No / We will use your Freeside rating and description name 'dstchannel', - # WTN / Yes / Bill to number (I guess put in "charged_party") + # WTN / Yes / Bill to number (put in "charged_party") 'charged_party', - # CODE / Yes / Account Code (security) and we need on invoice (suggestions ?) + # CODE / Yes / Account Code (security) and we need on invoice 'accountcode', # PROV/COUNTRY / No / We will use your Freeside rating and description name @@ -74,7 +72,8 @@ use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker ); $cdr->dst( $pre. $cdr->dst ) unless $cdr->dst =~ /^$pre/; }, - # CALL TYPE / Possibly / Not sure if you need this to determine correct billing method ? + # CALL TYPE / Possibly / Not sure if you need this to determine correct + # billing method ? # DDD normal call (Direct Dial Dsomething? ="LD"?) # TF Toll Free # (toll free dst# should be sufficient to rate) diff --git a/FS/FS/cdr/troop.pm b/FS/FS/cdr/troop.pm index 88dc3d90d..020af2b20 100644 --- a/FS/FS/cdr/troop.pm +++ b/FS/FS/cdr/troop.pm @@ -1,96 +1,128 @@ -package FS::cdr::cdr_template; +package FS::cdr::troop; use strict; use base qw( FS::cdr ); -use vars qw( %info ); -use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker ); +use vars qw( %info $tmp_mon $tmp_mday $tmp_year ); +use Time::Local; +#use FS::cdr qw( _cdr_date_parser_maker _cdr_min_parser_maker ); %info = ( 'name' => 'Troop', 'weight' => 220, - 'header' => 0, #0 default, set to 1 to ignore the first line - 'type' => 'csv', #csv (default), fixedlength or xls - 'sep_char' => ',', #for csv, defaults to , - 'disabled' => 0, #0 default, set to 1 to disable + 'header' => 2, + 'type' => 'xls', - #listref of what to do with each field from the CDR, in order 'import_fields' => [ - - #place data directly in the specified field - 'freeside_cdr_fieldname', - #subroutine reference - sub { my($cdr, $field_data) = @_; - #do something to $field_data - $cdr->fieldname($field_data); + # CDR FIELD / REQUIRED / Notes + + # / No / CDR sequence number + sub {}, + + # WTN / Yes + 'charged_party', + + # Account Code / Yes / Account Code (security) and we need on invoice + 'accountcode', + + # DT / Yes / "DATE" Excel + # XXX false laziness w/bell_west.pm + sub { my($cdr, $date) = @_; + + my $datetime = DateTime::Format::Excel->parse_datetime( $date ); + $tmp_mon = $datetime->mon_0; + $tmp_mday = $datetime->mday; + $tmp_year = $datetime->year; }, - #premade subref factory for date parsing - _cdr_date_parser_maker('startddate'), #for example - - #premade subref factory for decimal minute parsing - _cdr_min_parser_maker, #defaults to billsec and duration - _cdr_min_parser_maker('fieldname'), #one field - _cdr_min_parser_maker(['billsec', 'duration']), #listref for multiple fields + # Time / Yes / "TIME" excel + sub { my($cdr, $time) = @_; + #my($sec, $min, $hour, $mday, $mon, $year)= localtime($cdr->startdate); - ], + #$sec = $time * 86400; + my $sec = int( $time * 86400 + .5); - #Text::FixedLength field descriptions & lengths, for type=>'fixedlength' only - 'fixedlength_format' => [qw( - Type:2:1:2 - Sequence:4:3:6 - )], + #$cdr->startdate( timelocal($3, $2, $1 ,$mday, $mon, $year) ); + $cdr->startdate( + timelocal(0, 0, 0, $tmp_mday, $tmp_mon, $tmp_year) + $sec + ); + }, + + + # Dur. / Yes / Units = seconds + 'billsec', + + # OVS Type / Maybe / add "011" to international calls + # N = DOM LD / normal + # Z = INTL LD + # O = INTL LD + # others...? + sub { my($cdr, $ovs) = @_; + my $pre = ( $ovs =~ /^\s*[OZ]\s*$/i ) ? '011' : '1'; + $cdr->dst( $pre. $cdr->dst ) unless $cdr->dst =~ /^$pre/; + }, + + # Number / YES + 'src', + + # City / No + 'channel', + + # Prov/State / No / We will use your Freeside rating and description name + sub { my($cdr, $state) = @_; + $cdr->channel( $cdr->channel. ", $state" ) + if $state; + }, + + # Number / Yes + 'dst', + + # City / No + 'dstchannel', + + # Prov/State / No / We will use your Freeside rating and description name + sub { my($cdr, $state) = @_; + $cdr->dstchannel( $cdr->dstchannel. ", $state" ) + if $state; + }, + + # OVS / Maybe + # Would help to add "011" to international calls (if you are willing) + # (using ovs above) + sub { my($cdr, $ovs) = @_; + my @ignore = ( 'BELL', 'CANADA', 'UNITED STATES', ); + $cdr->dstchannel( $cdr->dstchannel. ", $ovs" ) + if $ovs && ! grep { $ovs =~ /^\s*$_\s*$/ } @ignore; + }, + + # CC Ind. / No / Does show if Calling card but should not be required + #'N' or 'E' + sub {}, + + # Call Charge / No / Bell billing info and is not required + 'upstream_price', + + # Account # / No / Bell billing info and is not required + sub {}, + + # Net Charge / No / Bell billing info and is not required + sub {}, + + # Surcharge / No / Taxes and is not required + sub {}, + + # GST / No / Taxes and is not required + sub {}, + + # PST / No / Taxes and is not required + sub {}, + + # HST / No / Taxes and is not required + sub {}, + + ], ); 1; -__END__ - -list of freeside CDR fields, useful ones marked with * - - acctid - primary key -*[1] calldate - Call timestamp (SQL timestamp) - clid - Caller*ID with text -* src - Caller*ID number / Source number -* dst - Destination extension - dcontext - Destination context - channel - Channel used - dstchannel - Destination channel if appropriate - lastapp - Last application if appropriate - lastdata - Last application data -* startdate - Start of call (UNIX-style integer timestamp) - answerdate - Answer time of call (UNIX-style integer timestamp) -* enddate - End time of call (UNIX-style integer timestamp) -* duration - Total time in system, in seconds -* billsec - Total time call is up, in seconds -*[2] disposition - What happened to the call: ANSWERED, NO ANSWER, BUSY - amaflags - What flags to use: BILL, IGNORE etc, specified on a per - channel basis like accountcode. -*[3] accountcode - CDR account number to use: account - uniqueid - Unique channel identifier (Unitel/RSLCOM Event ID) - userfield - CDR user-defined field - cdr_type - CDR type - see FS::cdr_type (Usage = 1, S&E = 7, OC&C = 8) -*[4] charged_party - Service number to be billed - upstream_currency - Wholesale currency from upstream -*[5] upstream_price - Wholesale price from upstream - upstream_rateplanid - Upstream rate plan ID - rated_price - Rated (or re-rated) price - distance - km (need units field?) - islocal - Local - 1, Non Local = 0 -*[6] calltypenum - Type of call - see FS::cdr_calltype - description - Description (cdr_type 7&8 only) (used for - cust_bill_pkg.itemdesc) - quantity - Number of items (cdr_type 7&8 only) - carrierid - Upstream Carrier ID (see FS::cdr_carrier) - upstream_rateid - Upstream Rate ID - svcnum - Link to customer service (see FS::cust_svc) - freesidestatus - NULL, done (or something) - -[1] Auto-populated from startdate if not present -[2] Package options available to ignore calls without a specific disposition -[3] When using 'cdr-charged_party-accountcode' config -[4] Auto-populated from src (normal calls) or dst (toll free calls) if not present -[5] When using 'upstream_simple' rating method. -[6] Set to usage class classnum when using pre-rated CDRs and usage class-based - taxation (local/intrastate/interstate/international) diff --git a/bin/cdr.import b/bin/cdr.import index 5c5e6ab63..b2e8d9127 100644 --- a/bin/cdr.import +++ b/bin/cdr.import @@ -1,7 +1,7 @@ #!/usr/bin/perl # # Usage: -# cdr.import user format *STDIN{IO}, - 'format' => $format, + 'file' => shift, + 'format' => $format, } ); die $error if $error; sub usage { - "Usage: \n cdr.import user format *FILE{IO}, - 'format' => $format, - 'cdrbatch' => $filename, - 'empty_ok' => 1, + 'file' => "$cachedir/$filename" + 'format' => $format, + 'cdrbatch' => $filename, + 'empty_ok' => 1, } ); die $error if $error; -- 2.11.0