finish the import portion of excel rate edit, RT#5108

4 files changed, 366 insertions, 5 deletions

diff --git a/FS/FS/rate_detail.pm b/FS/FS/rate_detail.pm
index 62c0fa1..b7b23ba 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 c8b026d..7e2eeef 100644
--- a/httemplate/elements/file-upload.html
+++ b/httemplate/elements/file-upload.html
@@ -55,7 +55,7 @@
 
 % foreach (@field) {
     <TR>
-      <TH ALIGN="right"><% shift @label %></TH>
+      <TH ALIGN="<% $param{'label_align'} || 'right' %>"><% shift @label %></TH>
       <TD><INPUT TYPE="file" NAME="<% $_ %>" /></TD>
     </TR>
 % }
diff --git a/httemplate/misc/process/rate_edit_excel.html b/httemplate/misc/process/rate_edit_excel.html
new file mode 100644
index 0000000..acd5f49
--- /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;
+
+</%init>
+
diff --git a/httemplate/misc/rate_edit_excel.html b/httemplate/misc/rate_edit_excel.html
index 7d32553..e73133c 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 @@
     <TH ALIGN="left" COLSPAN=2>2. Edit rates with Excel (or other .XLS-compatible application)</TH>
   </TR>
 
+  <TR>
+    <TD ALIGN="left" COLSPAN=2>
+      &nbsp;&nbsp;&nbsp;&nbsp;-&nbsp;To add rates, add four columns like an existing rate, with headers starting with "NEW: Rate Name" or "Rate Name".<BR>
+     &nbsp;&nbsp;&nbsp;&nbsp;-&nbsp;<FONT SIZE="-2"><I>For rate addition, protection can be turned off in Excel via the Tools-&gt;Protection-&gt;Unprotect Sheet menu command.  Note that only new rates can be added; modified grayed out cells will not be imported.</I></FONT>
+    </TD>
+  </TR>
+
   <% include( '/elements/file-upload.html',
                 'field' => 'file',
-                'label' => '3. Upload edited rate file: '. ( '&nbsp'x4 ), #yuck
+                'label' => '3. Upload edited rate file: ',
+                'label_align' => 'left',
             )
   %>
 
+  <INPUT TYPE="hidden" NAME="format" VALUE="default">
+
   <TR>
     <TD COLSPAN=2 ALIGN="center" STYLE="padding-top:6px">
       <INPUT TYPE    = "submit"