From: ivan Date: Tue, 5 May 2009 10:40:15 +0000 (+0000) Subject: finish the import portion of excel rate edit, RT#5108 X-Git-Tag: root_of_svc_elec_features~1211 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=791b34f7cb476e7c645d8eb7019dde9fdf5cf663 finish the import portion of excel rate edit, RT#5108 --- diff --git a/FS/FS/rate_detail.pm b/FS/FS/rate_detail.pm index 62c0fa1ec..b7b23babe 100644 --- a/FS/FS/rate_detail.pm +++ b/FS/FS/rate_detail.pm @@ -1,14 +1,17 @@ package FS::rate_detail; use strict; -use vars qw( @ISA ); -use FS::Record qw( qsearch qsearchs ); +use vars qw( @ISA $DEBUG $me ); +use FS::Record qw( qsearch qsearchs dbh ); use FS::rate; use FS::rate_region; use Tie::IxHash; @ISA = qw(FS::Record); +$DEBUG = 0; +$me = '[FS::rate_detail]'; + =head1 NAME FS::rate_detail - Object methods for rate_detail records @@ -229,6 +232,344 @@ sub granularities { %granularities; } +use Storable qw(thaw); +use Data::Dumper; +use MIME::Base64; +sub process_edit_import { + my $job = shift; + + #do we actually belong in rate_detail, like 'table' says? even though we + # can possible create new rate records, that's a side effect, mostly we + # do edit rate_detail records in batch... + + my $opt = { 'table' => 'rate_detail', + 'params' => [], #required, apparantly + 'formats' => { 'default' => [ + 'dest_regionnum', + '', #regionname + '', #country + '', #prefixes + #loop these + 'min_included', + 'min_charge', + sub { + my( $rate_detail, $g ) = @_; + $g = 0 if $g =~ /^\s*(per-)?call\s*$/i; + $g = 60 if $g =~ /^\s*minute\s*$/i; + $g =~ /^(\d+)/ or die "can't parse granularity: $g". + " for record ". Dumper($rate_detail); + $rate_detail->sec_granularity($1); + }, + 'classnum', + ] }, + 'format_headers' => { 'default' => 1, }, + 'format_types' => { 'default' => 'xls' }, + }; + + #false laziness w/ + #FS::Record::process_batch_import( $job, $opt, @_ ); + + my $table = $opt->{table}; + my @pass_params = @{ $opt->{params} }; + my %formats = %{ $opt->{formats} }; + + my $param = thaw(decode_base64(shift)); + warn Dumper($param) if $DEBUG; + + my $files = $param->{'uploaded_files'} + or die "No files provided.\n"; + + my (%files) = map { /^(\w+):([\.\w]+)$/ ? ($1,$2):() } split /,/, $files; + + my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/'; + my $file = $dir. $files{'file'}; + + my $error = + #false laziness w/ + #FS::Record::batch_import( { + FS::rate_detail::edit_import( { + #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; + + die "$error\n" if $error; + +} + +#false laziness w/ #FS::Record::batch_import, grep "edit_import" for differences +#could be turned into callbacks or something +use Text::CSV_XS; +sub edit_import { + my $param = shift; + + warn "$me edit_import call with params: \n". Dumper($param) + if $DEBUG; + + my $table = $param->{table}; + my $formats = $param->{formats}; + + my $job = $param->{job}; + my $file = $param->{file}; + my $format = $param->{'format'}; + my $params = $param->{params} || {}; + + die "unknown format $format" unless exists $formats->{ $format }; + + my $type = $param->{'format_types'} + ? $param->{'format_types'}{ $format } + : $param->{type} || 'csv'; + + 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; + my $parser; + my @buffer = (); + my @header = (); #edit_import + if ( $type eq 'csv' || $type eq 'fixedlength' ) { + + if ( $type eq 'csv' ) { + + my %attr = (); + $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 $fixedlength_format; + + } else { + die "Unknown file type $type\n"; + } + + @buffer = split(/\r?\n/, slurp($file) ); + splice(@buffer, 0, ($header || 0) ); + $count = scalar(@buffer); + + } elsif ( $type eq 'xls' ) { + + eval "use Spreadsheet::ParseExcel;"; + die $@ if $@; + + eval "use DateTime::Format::Excel;"; + #for now, just let the error be thrown if it is used, since only CDR + # formats bill_west and troop use it, not other excel-parsing things + #die $@ if $@; + + my $excel = Spreadsheet::ParseExcel::Workbook->new->Parse($file); + + $parser = $excel->{Worksheet}[0]; #first sheet + + $count = $parser->{MaxRow} || $parser->{MinRow}; + $count++; + + $row = $header || 0; + + #edit_import - need some magic to parse the header + if ( $header ) { + my @header_row = @{ $parser->{Cells}[$0] }; + @header = map $_->{Val}, @header_row; + } + + } else { + die "Unknown file type $type\n"; + } + + #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; + + #edit_import - use the header to setup looping over different rates + my @rate = (); + if ( @header ) { + splice(@header,0,4); # # Region Country Prefixes + while ( my @next = splice(@header,0,4) ) { + my $rate; + if ( $next[0] =~ /^(\d+):\s*([^:]+):/ ) { + $rate = qsearchs('rate', { 'ratenum' => $1 } ) + or die "unknown ratenum $1"; + } elsif ( $next[0] =~ /^(NEW:)?\s*([^:]+)/i ) { + $rate = new FS::rate { 'ratename' => $2 }; + my $error = $rate->insert; + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "error inserting new rate: $error\n"; + } + } + push @rate, $rate; + } + } + die unless @rate; + + my $line; + my $imported = 0; + my( $last, $min_sec ) = ( time, 5 ); #progressbar foo + while (1) { + + my @columns = (); + if ( $type eq 'csv' ) { + + last unless scalar(@buffer); + $line = shift(@buffer); + + $parser->parse($line) or do { + $dbh->rollback if $oldAutoCommit; + return "can't parse: ". $parser->error_input(); + }; + @columns = $parser->fields(); + + } elsif ( $type eq 'fixedlength' ) { + + @columns = $parser->parse($line); + + } elsif ( $type eq 'xls' ) { + + last if $row > ($parser->{MaxRow} || $parser->{MinRow}) + || ! $parser->{Cells}[$row]; + + my @row = @{ $parser->{Cells}[$row] }; + @columns = map $_->{Val}, @row; + + #my $z = 'A'; + #warn $z++. ": $_\n" for @columns; + + } else { + die "Unknown file type $type\n"; + } + + #edit_import loop + + my @repeat = @columns[0..3]; + + foreach my $rate ( @rate ) { + + my @later = (); + my %hash = %$params; + + foreach my $field ( @fields ) { + + my $value = shift @columns; + + if ( ref($field) eq 'CODE' ) { + #&{$field}(\%hash, $value); + push @later, $field, $value; + #} else { + } elsif ($field) { #edit_import + #??? $hash{$field} = $value if length($value); + $hash{$field} = $value if defined($value) && length($value); + } + + } + + unshift @columns, @repeat; #edit_import put these back on for next time + + my $class = "FS::$table"; + + my $record = $class->new( \%hash ); + + $record->ratenum($rate->ratenum); #edit_import + + #edit_improt n/a my $param = {}; + while ( scalar(@later) ) { + my $sub = shift @later; + my $data = shift @later; + #&{$sub}($record, $data, $conf, $param);# $record->&{$sub}($data, $conf); + &{$sub}($record, $data); #edit_import - don't have $conf + #edit_import wrong loop last if exists( $param->{skiprow} ); + } + #edit_import wrong loop next if exists( $param->{skiprow} ); + + #edit_import update or insert, not just insert + my $old = qsearchs({ + 'table' => $table, + 'hashref' => { map { $_ => $record->$_() } qw(ratenum dest_regionnum) }, + }); + + my $error; + if ( $old ) { + $record->ratedetailnum($old->ratedetailnum); + $error = $record->replace($old) + } else { + $record->insert; + } + + if ( $error ) { + $dbh->rollback if $oldAutoCommit; + return "can't insert record". ( $line ? " for $line" : '' ). ": $error"; + } + + } + + $row++; + $imported++; + + if ( $job && time - $min_sec > $last ) { #progress bar + $job->update_statustext( int(100 * $imported / $count) ); + $last = time; + } + + } + + $dbh->commit or die $dbh->errstr if $oldAutoCommit;; + + return "Empty file!" unless $imported || $param->{empty_ok}; + + ''; #no error + +} + + =back diff --git a/httemplate/elements/file-upload.html b/httemplate/elements/file-upload.html index c8b026d04..7e2eeefcd 100644 --- a/httemplate/elements/file-upload.html +++ b/httemplate/elements/file-upload.html @@ -55,7 +55,7 @@ % foreach (@field) { - <% shift @label %> + <% shift @label %> % } diff --git a/httemplate/misc/process/rate_edit_excel.html b/httemplate/misc/process/rate_edit_excel.html new file mode 100644 index 000000000..acd5f4995 --- /dev/null +++ b/httemplate/misc/process/rate_edit_excel.html @@ -0,0 +1,10 @@ +<% $server->process %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('Configuration'); + +my $server = new FS::UI::Web::JSRPC 'FS::rate_detail::process_edit_import', $cgi; + + + diff --git a/httemplate/misc/rate_edit_excel.html b/httemplate/misc/rate_edit_excel.html index 7d3255301..e73133c05 100644 --- a/httemplate/misc/rate_edit_excel.html +++ b/httemplate/misc/rate_edit_excel.html @@ -4,7 +4,7 @@ 'name' => 'RateImportForm', 'action' => 'process/rate_edit_excel.html', 'num_files' => 1, - 'fields' => [ 'format' ], #? + 'fields' => [ 'format' ], 'message' => 'Rate edit successful', 'url' => $p."browse/rate_region.html", ) @@ -23,12 +23,22 @@ 2. Edit rates with Excel (or other .XLS-compatible application) + + +     - To add rates, add four columns like an existing rate, with headers starting with "NEW: Rate Name" or "Rate Name".
+     - For rate addition, protection can be turned off in Excel via the Tools->Protection->Unprotect Sheet menu command. Note that only new rates can be added; modified grayed out cells will not be imported. + + + <% include( '/elements/file-upload.html', 'field' => 'file', - 'label' => '3. Upload edited rate file: '. ( ' 'x4 ), #yuck + 'label' => '3. Upload edited rate file: ', + 'label_align' => 'left', ) %> + +