From: ivan Date: Wed, 31 Dec 2008 03:28:57 +0000 (+0000) Subject: bell west CDR format, RT#4403 X-Git-Tag: root_of_webpay_support~165 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=1cf39475a4ba90ed0aa49ed983542077e4609c22 bell west CDR format, RT#4403 --- diff --git a/FS/FS/Record.pm b/FS/FS/Record.pm index acec9458d..a44ef8b69 100644 --- a/FS/FS/Record.pm +++ b/FS/FS/Record.pm @@ -1340,18 +1340,74 @@ sub check { ''; } -=item batch_import PARAM_HASHREF +=item process_batch_import JOB OPTIONS_HASHREF PARAMS -Class method for batch imports. Available params: +Processes a batch import as a queued JSRPC job + +JOB is an FS::queue entry. + +OPTIONS_HASHREF can have the following keys: =over 4 -=item job +=item table -FS::queue object, will be updated with progress +Table name (required). + +=item params + +Listref of field names for static fields. They will be given values from the +PARAMS hashref and passed as a "params" hashref to batch_import. + +=item formats + +Formats hashref. Keys are field names, values are listrefs that define the +format. + +Each listref value can be a column name or a code reference. Coderefs are run +with the row object and data as the two parameters. For example, this coderef +does the same thing as using the "columnname" string: + + sub { + my( $record, $data ) = @_; + $record->columnname( $data ); + }, + +=item format_types + +Optional format hashref of types. Keys are field names, values are "csv", +"xls" or "fixedlength". Overrides automatic determination of file type +from extension. + +=item format_headers + +Optional format hashref of header lines. Keys are field names, values are 0 +for no header, 1 to ignore the first line, or to higher numbers to ignore that +number of lines. + +=item format_sep_chars + +Optional format hashref of CSV sep_chars. Keys are field names, values are the +CSV separation character. + +=item format_fixedlenth_formats + +Optional format hashref of fixed length format defintiions. Keys are field +names, values Parse::FixedLength listrefs of field definitions. + +=item default_csv + +Set true to default to CSV file type if the filename does not contain a +recognizable ".csv" or ".xls" extension (and type is not pre-specified by +format_types). =back +PARAMS is a base64-encoded Storable string containing the POSTed data as +a hash ref. It normally contains at least one field, "uploaded files", +generated by /elements/file-upload.html and containing the list of uploaded +files. Currently only supports a single file named "file". + =cut use Storable qw(thaw); @@ -1375,26 +1431,47 @@ sub process_batch_import { my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/'; my $file = $dir. $files{'file'}; - my $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'; + 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'; } - $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}, - params => { map { $_ => $param->{$_} } @pass_params }, + 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 }, } ); unlink $file; @@ -1402,9 +1479,48 @@ sub process_batch_import { die "$error\n" if $error; } +=item batch_import PARAM_HASHREF + +Class method for batch imports. Available params: + +=over 4 + +=item table + +=item formats + +=item params + +=item job + +FS::queue object, will be updated with progress + +=item filename + +=item type + +csv, xls or fixedlength + +=item format + +=item header + +=item sep_char + +=item fixedlength_format + +=item empty_ok + +=back + +=cut + sub batch_import { my $param = shift; + warn "$me batch_import call with params: \n". Dumper($param) + if $DEBUG; + my $table = $param->{table}; my $formats = $param->{formats}; my $params = $param->{params}; @@ -1419,14 +1535,33 @@ sub batch_import { die "unknown format $format" unless exists $formats->{ $format }; my @fields = @{ $formats->{ $format } }; + my $row = 0; my $count; my $parser; my @buffer = (); - if ( $type eq 'csv' ) { + if ( $type eq 'csv' || $type eq 'fixedlength' ) { + + if ( $type eq 'csv' ) { + + my %attr = (); + foreach ( grep exists($param->{$_}), qw( sep_char ) ) { + $attr{$_} = $param->{$_}; + } + + $parser = new Text::CSV_XS \%attr; + + } elsif ( $type eq 'fixedlength' ) { - $parser = new Text::CSV_XS; + eval "use Parse::FixedLength;"; + die $@ if $@; + $parser = new Parse::FixedLength $param->{'fixedlength_format'}; + + } else { + die "Unknown file type $type\n"; + } @buffer = split(/\r?\n/, slurp($filename) ); + splice(@buffer, 0, ($param->{'header'} || 0) ); $count = scalar(@buffer); } elsif ( $type eq 'xls' ) { @@ -1441,6 +1576,8 @@ sub batch_import { $count = $parser->{MaxRow} || $parser->{MinRow}; $count++; + $row = $param->{'header'} || 0; + } else { die "Unknown file type $type\n"; } @@ -1459,7 +1596,7 @@ sub batch_import { my $dbh = dbh; my $line; - my $row = 0; + my $imported = 0; my( $last, $min_sec ) = ( time, 5 ); #progressbar foo while (1) { @@ -1475,6 +1612,10 @@ sub batch_import { }; @columns = $parser->fields(); + } elsif ( $type eq 'fixedlength' ) { + + @columns = $parser->parse($line); + } elsif ( $type eq 'xls' ) { last if $row > ($parser->{MaxRow} || $parser->{MinRow}) @@ -1490,6 +1631,7 @@ sub batch_import { die "Unknown file type $type\n"; } + my @later = (); my %hash = %$params; foreach my $field ( @fields ) { @@ -1497,7 +1639,8 @@ sub batch_import { my $value = shift @columns; if ( ref($field) eq 'CODE' ) { - &{$field}(\%hash, $value); + #&{$field}(\%hash, $value); + push @later, $field, $value; } else { $hash{$field} = $value if length($value); } @@ -1508,6 +1651,12 @@ sub batch_import { my $record = $class->new( \%hash ); + while ( scalar(@later) ) { + my $sub = shift @later; + my $data = shift @later; + &{$sub}($record, $data); # $record->&{$sub}($data); + } + my $error = $record->insert; if ( $error ) { @@ -1515,10 +1664,10 @@ sub batch_import { return "can't insert record". ( $line ? " for $line" : '' ). ": $error"; } - $row++; + $imported++; if ( $job && time - $min_sec > $last ) { #progress bar - $job->update_statustext( int(100 * $row / $count) ); + $job->update_statustext( int(100 * $imported / $count) ); $last = time; } @@ -1526,7 +1675,7 @@ sub batch_import { $dbh->commit or die $dbh->errstr if $oldAutoCommit;; - return "Empty file!" unless $row; + return "Empty file!" unless $imported || $param->{empty_ok}; ''; #no error diff --git a/FS/FS/cdr.pm b/FS/FS/cdr.pm index 0a2084f34..c2a3d00ee 100644 --- a/FS/FS/cdr.pm +++ b/FS/FS/cdr.pm @@ -234,6 +234,15 @@ sub check { $self->calldate( $self->startdate_sql ) if !$self->calldate && $self->startdate; + #was just for $format eq 'taqua' but can't see the harm... add something to + #disable if it becomes a problem + if ( $self->duration eq '' && $self->enddate && $self->startdate ) { + $self->duration( $self->enddate - $self->startdate ); + } + if ( $self->billsec eq '' && $self->enddate && $self->answerdate ) { + $self->billsec( $self->enddate - $self->answerdate ); + } + my $conf = new FS::Conf; unless ( $self->charged_party ) { @@ -671,138 +680,42 @@ Imports CDR records. Available options are: =cut -sub batch_import { - my $param = shift; - - my $fh = $param->{filehandle}; - my $format = $param->{format}; - my $cdrbatch = $param->{cdrbatch}; - - return "Unknown format $format" - unless exists( $cdr_info{$format} ) - && exists( $cdr_info{$format}->{'import_fields'} ); - - my $info = $cdr_info{$format}; - - my $type = exists($info->{'type'}) ? lc($info->{'type'}) : 'csv'; - - my $parser; - if ( $type eq 'csv' ) { - eval "use Text::CSV_XS;"; - die $@ if $@; - my %attr = (); - foreach ( grep exists($info->{$_}), qw( sep_char ) ) { - $attr{$_} = $info->{$_}; - } - $parser = new Text::CSV_XS \%attr; - } elsif ( $type eq 'fixedlength' ) { - eval "use Parse::FixedLength;"; - die $@ if $@; - $parser = new Parse::FixedLength $info->{'fixedlength_format'}; - } else { - die "Unknown CDR format type $type for format $format\n"; - } - - my $imported = 0; - #my $columns; - - local $SIG{HUP} = 'IGNORE'; - local $SIG{INT} = 'IGNORE'; - local $SIG{QUIT} = 'IGNORE'; - local $SIG{TERM} = 'IGNORE'; - local $SIG{TSTP} = 'IGNORE'; - local $SIG{PIPE} = 'IGNORE'; - - my $oldAutoCommit = $FS::UID::AutoCommit; - local $FS::UID::AutoCommit = 0; - my $dbh = dbh; - - my $header_lines = exists($info->{'header'}) ? $info->{'header'} : 0; - - my $line; - while ( defined($line=<$fh>) ) { - - next if $header_lines-- > 0; #&& $line =~ /^[\w, "]+$/ - - my @columns = (); - if ( $type eq 'csv' ) { - - $parser->parse($line) or do { - $dbh->rollback if $oldAutoCommit; - return "can't parse: ". $parser->error_input(); - }; - - @columns = $parser->fields(); - - } elsif ( $type eq 'fixedlength' ) { +sub process_batch_import { + my $job = shift; - @columns = $parser->parse($line); + my $opt = { + 'table' => 'cdr', + 'params' => [ 'format', 'cdrbatch' ], - } else { - die "Unknown CDR format type $type for format $format\n"; - } - - #warn join('-',@columns); - - if ( $format eq 'simple' ) { #should be a callback or opt in FS::cdr::simple - @columns = map { s/^ +//; $_; } @columns; - } - - my @later = (); - my %cdr = - map { - - my $field_or_sub = $_; - if ( ref($field_or_sub) ) { - push @later, $field_or_sub, shift(@columns); - (); - } else { - ( $field_or_sub => shift @columns ); - } - - } - @{ $info->{'import_fields'} } - ; - - $cdr{cdrbatch} = $cdrbatch; + 'formats' => { map { $_ => $cdr_info{$_}->{'import_fields'}; } + keys %cdr_info + }, - my $cdr = new FS::cdr ( \%cdr ); + #drop the || 'csv' to allow auto xls for csv types? + 'format_types' => { map { $_ => ( lc($cdr_info{$_}->{'type'}) || 'csv' ); } + keys %cdr_info + }, - while ( scalar(@later) ) { - my $sub = shift @later; - my $data = shift @later; - &{$sub}($cdr, $data); # $cdr->&{$sub}($data); - } - - if ( $format eq 'taqua' ) { #should be a callback or opt in FS::cdr::taqua - if ( $cdr->enddate && $cdr->startdate ) { #a bit more? - $cdr->duration( $cdr->enddate - $cdr->startdate ); - } - if ( $cdr->enddate && $cdr->answerdate ) { #a bit more? - $cdr->billsec( $cdr->enddate - $cdr->answerdate ); - } - } - - my $error = $cdr->insert; - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return $error; + 'format_headers' => { map { $_ => ( $cdr_info{$_}->{'header'} || 0 ); } + keys %cdr_info + }, - #or just skip? - #next; - } - - $imported++; - } + 'format_sep_chars' => { map { $_ => $cdr_info{$_}->{'sep_char'}; } + keys %cdr_info + }, - $dbh->commit or die $dbh->errstr if $oldAutoCommit; - - #might want to disable this if we skip records for any reason... - return "Empty file!" unless $imported || $param->{empty_ok}; + 'format_fixedlength_formats' => + { map { $_ => $cdr_info{$_}->{'fixedlength_format'}; } + keys %cdr_info + }, + }; - ''; + FS::Record::process_batch_import( $job, $opt, @_ ); } +# if ( $format eq 'simple' ) { #should be a callback or opt in FS::cdr::simple +# @columns = map { s/^ +//; $_; } @columns; +# } =back diff --git a/FS/FS/cdr/bell_west.pm b/FS/FS/cdr/bell_west.pm new file mode 100644 index 000000000..960851318 --- /dev/null +++ b/FS/FS/cdr/bell_west.pm @@ -0,0 +1,117 @@ +package FS::cdr::bell_west; + +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 ); + +%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 + + 'import_fields' => [ + + # CDR FIELD / REQUIRED / Notes + + # CHG TYPE / No / Internal Code only (no need to import) + sub {}, + + # ACCOUNT # / No / Internal Number only (no need to import) + sub {}, + + # DATE / Yes / "DATE" Excel date format MM/DD/YYYY + 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 ); + }, + + # CUST NO / Yes / "TIME" "075959" Text based time + # Note: This is really the start time but Bell header says "Cust No" which + # is wrong + sub { my($cdr, $time) = @_; + #my($sec, $min, $hour, $mday, $mon, $year)= localtime($cdr->startdate); + $time =~ /^(\d{1,2}):(\d{1,2}):(\d{1,2})$/ + or die "unparsable time: $time"; #maybe we shouldn't die... + #$cdr->startdate( timelocal($3, $2, $1 ,$mday, $mon, $year) ); + $cdr->startdate( + timelocal($3, $2, $1 ,$tmp_mday, $tmp_mon, $tmp_year) + ); + }, + + # BTN / Yes / Main billing number but not DID or real number (I guess 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" + '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") + 'charged_party', + + # CODE / Yes / Account Code (security) and we need on invoice (suggestions ?) + 'accountcode', + + # PROV/COUNTRY / No / We will use your Freeside rating and description name + # (but use this to add "011" for "International" calls) + sub { my( $cdr, $prov ) = @_; + my $pre = ( $prov =~ /^\s*International\s*/i ) ? '011' : '1'; + $cdr->dst( $pre. $cdr->dst ) unless $cdr->dst =~ /^$pre/; + }, + + # 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) + # DAT Directory AssisTance + # (dst# 411 "area code" should be sufficient to rate) + # DNS (Another sort of directory assistance?... only one record with + # "8195551212" in the dst#) + 'dcontext', #probably don't need... map to cdr_type? calltypenum? + + # DURATION Yes Units = seconds + 'billsec', #need to trim .00 ? + + # AMOUNT CHARGED No Will use Freeside rating and description name + sub { my( $cdr, $amount) = @_; + $amount =~ s/^\$//; + $cdr->upstream_price( $amount ); + }, + + ], + +); + +1; + +__END__ + +CHG TYPE (unused) +ACCOUNT # (unused) + +DATE startdate (+ CUST NO) +CUST NO (startdate time) + - Start of call (UNIX-style integer timestamp) + +BTN *src - Caller*ID number / Source number +ORIG CITY channel - Channel used +TERM # *dst - Destination extension +TERM CITY dstchannel - Destination channel if appropriate +WTN *charged_party - Service number to be billed +CODE *accountcode - CDR account number to use: account + +PROV/COUNTRY (used to prefix TERM # w/ 1 or 011) + +CALL TYPE dcontext - Destination context +DURATION *billsec - Total time call is up, in seconds +AMOUNT CHARGED *upstream_price - Wholesale price from upstream + diff --git a/FS/FS/cdr/simple.pm b/FS/FS/cdr/simple.pm index b923405d1..197b0ebba 100644 --- a/FS/FS/cdr/simple.pm +++ b/FS/FS/cdr/simple.pm @@ -13,7 +13,7 @@ use FS::cdr qw(_cdr_min_parser_maker); 'header' => 1, 'import_fields' => [ - # Date + # Date (MM/DD/YY) 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... diff --git a/FS/FS/part_pkg/voip_cdr.pm b/FS/FS/part_pkg/voip_cdr.pm index 8bfeeebc3..4f41764b7 100644 --- a/FS/FS/part_pkg/voip_cdr.pm +++ b/FS/FS/part_pkg/voip_cdr.pm @@ -114,7 +114,7 @@ tie my %temporalities, 'Tie::IxHash', '411_rewrite' => { 'name' => 'Rewrite these (comma-separated) destination numbers to 411 for rating purposes: ', }, - 'output_format' => { 'name' => 'Simple output format', + 'output_format' => { 'name' => 'CDR invoice display format', 'type' => 'select', 'select_options' => { FS::cdr::invoice_formats() }, 'default' => 'default', #XXX test diff --git a/FS/FS/phone_avail.pm b/FS/FS/phone_avail.pm index 136fc24b0..e436bcaa3 100644 --- a/FS/FS/phone_avail.pm +++ b/FS/FS/phone_avail.pm @@ -148,10 +148,13 @@ sub process_batch_import { my $job = shift; my $numsub = sub { - my( $hash, $value ) = @_; + my( $phone_avail, $value ) = @_; $value =~ s/\D//g; $value =~ /^(\d{3})(\d{3})(\d+)$/ or die "unparsable number $value\n"; - ( $hash->{npa}, $hash->{nxx}, $hash->{station} ) = ( $1, $2, $3 ); + #( $hash->{npa}, $hash->{nxx}, $hash->{station} ) = ( $1, $2, $3 ); + $phone_avail->npa($1); + $phone_avail->nxx($2); + $phone_avail->station($3); }; my $opt = { 'table' => 'phone_avail', diff --git a/httemplate/edit/rate_detail.html b/httemplate/edit/rate_detail.html index 4860593ac..dd8c3f6b3 100644 --- a/httemplate/edit/rate_detail.html +++ b/httemplate/edit/rate_detail.html @@ -5,8 +5,8 @@ 'labels' => { 'ratedetailnum' => 'Rate', #should hide... 'dest_regionname' => 'Region', 'dest_prefixes_short' => 'Prefix(es)', - 'min_included' => 'Included minutes', - 'min_charge' => 'Charge per minute', + 'min_included' => 'Included minutes/calls', + 'min_charge' => 'Charge per minute/call', 'sec_granularity' => 'Granularity', 'classnum' => 'Usage class', }, diff --git a/httemplate/misc/cdr-import.html b/httemplate/misc/cdr-import.html index 62e38b29b..7af6c521f 100644 --- a/httemplate/misc/cdr-import.html +++ b/httemplate/misc/cdr-import.html @@ -1,19 +1,50 @@ <% include("/elements/header.html",'Call Detail Record Import') %> -
-Import a CSV file containing Call Detail Records (CDRs).

-CDR Format: - -

- -Filename:

+ +<% include( '/elements/form-file_upload.html', + 'name' => 'CDRImportForm', + 'action' => 'process/cdr-import.html', + 'num_files' => 1, + 'fields' => [ 'format', 'cdrbatch', ], + 'message' => 'CDR import successful', + 'url' => $p."search/cdr.html?cdrbatch=$cdrbatch", + ) +%> + +Import a file containing Call Detail Records (CDRs).

- +<% ntable('#cccccc', 2) %> + + + CDR Format + + + + + + <% include( '/elements/file-upload.html', + 'field' => 'file', + 'label' => 'Filename', + ) + %> + + + + + + + + +
<% include('/elements/footer.html') %> diff --git a/httemplate/misc/process/cdr-import.html b/httemplate/misc/process/cdr-import.html index 7c4bf2b59..edc441e35 100644 --- a/httemplate/misc/process/cdr-import.html +++ b/httemplate/misc/process/cdr-import.html @@ -1,23 +1,9 @@ -% if ( $error ) { -% errorpage($error); -% } else { - <% include("/elements/header.html",'Import successful') %> - - <% include("/elements/footer.html",'Import successful') %> -% } +<% $server->process %> <%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Import'); -my $fh = $cgi->upload('csvfile'); - -my $error = defined($fh) - ? FS::cdr::batch_import( { - 'filehandle' => $fh, - 'format' => scalar($cgi->param('format')), - 'cdrbatch' => scalar($cgi->param('cdrbatch')), - } ) - : 'No file'; +my $server = new FS::UI::Web::JSRPC 'FS::cdr::process_batch_import', $cgi;