support zip5 tax lookups, correct errors with fixed format cch import, inital import performance improvements, noise reduction on imports, tool for inital import

7 files changed, 130 insertions, 21 deletions

diff --git a/FS/FS/Misc.pm b/FS/FS/Misc.pm
index d79f86e..5231350 100644
--- a/FS/FS/Misc.pm
+++ b/FS/FS/Misc.pm
@@ -799,7 +799,7 @@ sub csv_from_fixed {
   my $template = join('', map {$total += $_; "A$_"} @$lengths) if $lengths;
 
   my $dir = "%%%FREESIDE_CACHE%%%/cache.$FS::UID::datasrc";
-  my $fh = new File::Temp( TEMPLATE => "CODE.csv.XXXXXXXX",
+  my $fh = new File::Temp( TEMPLATE => "FILE.csv.XXXXXXXX",
                            DIR      => $dir,
                            UNLINK   => 0,
                          ) or return "can't open temp file: $!\n"
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index 73f4f26..719f3a8 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -648,6 +648,7 @@ sub tables_hashref {
         'paystate', 'varchar', 'NULL', $char_d, '', '', 
         'paytype',  'varchar', 'NULL', $char_d, '', '', 
         'payip',    'varchar', 'NULL', 15, '', '', 
+        'geocode',  'varchar', 'NULL', 20,  '', '',
         'tax',      'char', 'NULL', 1, '', '', 
         'otaker',   'varchar', '',    32, '', '', 
         'refnum',   'int',  '',     '', '', '', 
@@ -754,11 +755,15 @@ sub tables_hashref {
       'columns' => [
         'custlocationnum', 'serial',  '',     '', '', '', 
         'data_vendor',     'varchar', 'NULL', $char_d, '', '', # update source
+        'city',            'varchar', 'NULL', $char_d, '', '',
+        'postalcity',      'varchar', 'NULL', $char_d, '', '',
+        'county',          'varchar', 'NULL', $char_d, '', '',
         'zip',             'char',    '',     5,  '', '', 
         'state',           'char',    '',     2,  '', '', 
-        'plus4hi',         'char',    '',     4,  '', '', 
-        'plus4lo',         'char',    '',     4,  '', '', 
+        'plus4hi',         'char',    'NULL', 4,  '', '', 
+        'plus4lo',         'char',    'NULL', 4,  '', '', 
         'default_location','char',    'NULL', 1,  '', '', # Y = default for zip
+        'cityflag',        'char',    'NULL', 1,  '', '', # I(n)/O(out)/B(oth)/NULL
         'geocode',         'varchar', '',    20,  '', '', 
       ],
       'primary_key' => 'custlocationnum',
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 8b57b93..c572bea 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -1262,6 +1262,7 @@ sub check {
     || $self->ut_textn('stateid')
     || $self->ut_textn('stateid_state')
     || $self->ut_textn('invoice_terms')
+    || $self->ut_alphan('geocode')
   ;
 
   #barf.  need message catalogs.  i18n.  etc.
@@ -5227,6 +5228,9 @@ Currently this only makes sense for "CCH" as DATA_VENDOR.
 sub geocode {
   my ($self, $data_vendor) = (shift, shift);  #always cch for now
 
+  my $geocode = $self->get('geocode');  #XXX only one data_vendor for geocode
+  return $geocode if $geocode;
+
   my $prefix = ( $conf->exists('tax-ship_address') && length($self->ship_last) )
                ? 'ship_'
                : '';
@@ -5237,7 +5241,6 @@ sub geocode {
   #CCH specific location stuff
   my $extra_sql = "AND plus4lo <= '$plus4' AND plus4hi >= '$plus4'";
 
-  my $geocode = '';
   my @cust_tax_location =
     qsearch( {
                'table'     => 'cust_tax_location', 
diff --git a/FS/FS/cust_tax_location.pm b/FS/FS/cust_tax_location.pm
index cd24cc8..f77ad37 100644
--- a/FS/FS/cust_tax_location.pm
+++ b/FS/FS/cust_tax_location.pm
@@ -113,15 +113,33 @@ sub check {
   my $error = 
     $self->ut_numbern('custlocationnum')
     || $self->ut_text('data_vendor')
-    || $self->ut_number('zip')
+    || $self->ut_textn('city')
+    || $self->ut_textn('postalcity')
+    || $self->ut_textn('county')
     || $self->ut_text('state')
-    || $self->ut_number('plus4hi')
-    || $self->ut_number('plus4lo')
-    || $self->ut_enum('default', [ '', ' ', 'Y' ] )
-    || $self->ut_number('geocode')
+    || $self->ut_numbern('plus4hi')
+    || $self->ut_numbern('plus4lo')
+    || $self->ut_enum('default', [ '', ' ', 'Y' ] ) # wtf?
+    || $self->ut_enum('cityflag', [ '', 'I', 'O', 'B' ] )
+    || $self->ut_alpha('geocode')
   ;
   return $error if $error;
 
+  #ugh!  cch canada weirdness
+  if ($self->state eq 'CN') {
+    $error = "Illegal cch canadian zip"
+     unless $self->zip =~ /^[A-Z]$/;
+  } else {
+    $error = $self->ut_number('zip', $self->state eq 'CN' ? 'CA' : 'US');
+  }
+  return $error if $error;
+
+  #ugh!  cch canada weirdness
+  return "must specify either city/county or plus4lo/plus4hi"
+    unless ( $self->plus4lo && $self->plus4hi || 
+             ($self->city || $self->state eq 'CN') && $self->county
+           );
+
   $self->SUPER::check;
 }
 
@@ -138,15 +156,24 @@ sub batch_import {
 
   my @column_lengths = ();
   my @column_callbacks = ();
-  if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
+  if ( $format =~ /^cch-fixed/ ) {
     $format =~ s/-fixed//;
-    push @column_lengths, qw( 5 2 4 4 10 1 );
-    push @column_lengths, 1 if $format eq 'cch-update';
+    my $f = $format;
+    my $update = 0;
+    $f =~ s/-update// && ($update = 1);
+    if ($f eq 'cch') {
+      push @column_lengths, qw( 5 2 4 4 10 1 );
+    } elsif ( $f eq 'cch-zip' ) {
+      push @column_lengths, qw( 5 28 25 2 28 5 1 1 10 1 2 );
+    } else {
+      return "Unknown format: $format";
+    }
+    push @column_lengths, 1 if $update;
   }
 
   my $line;
   my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
-  if ( $job || scalar(@column_callbacks) ) {
+  if ( $job || scalar(@column_lengths) ) {
     my $error = csv_from_fixed(\$fh, \$count, \@column_lengths);
     return $error if $error;
   }
@@ -182,6 +209,38 @@ sub batch_import {
       
     };
 
+  } elsif ( $format eq 'cch-zip' || $format eq 'cch-update-zip' ) {
+    @fields = qw( zip city county state postalcity countyfips countydef default geocode cityflag unique );
+    push @fields, 'actionflag' if $format eq 'cch-update';
+
+    $imported++ if $format eq 'cch-update'; #empty file ok
+    
+    $hook = sub {
+      my $hash = shift;
+
+      $hash->{'data_vendor'} = 'cch-zip';
+      delete($hash->{$_}) foreach qw( countyfips countydef unique );
+
+      if (exists($hash->{actionflag}) && $hash->{actionflag} eq 'D') {
+        delete($hash->{actionflag});
+
+        my $cust_tax_location = qsearchs('cust_tax_location', $hash);
+        return "Can't find cust_tax_location to delete: ".
+               join(" ", map { "$_ => ". $hash->{$_} } @fields)
+          unless $cust_tax_location;
+
+        my $error = $cust_tax_location->delete;
+        return $error if $error;
+
+        delete($hash->{$_}) foreach (keys %$hash);
+      }
+
+      delete($hash->{'actionflag'});
+
+      '';
+      
+    };
+
   } elsif ( $format eq 'extended' ) {
     die "unimplemented\n";
     @fields = qw( );
diff --git a/FS/FS/part_pkg_taxrate.pm b/FS/FS/part_pkg_taxrate.pm
index 1563621..9e1c723 100644
--- a/FS/FS/part_pkg_taxrate.pm
+++ b/FS/FS/part_pkg_taxrate.pm
@@ -244,7 +244,7 @@ sub batch_import {
       unless ($part_pkg_taxproduct) {
         return "Can't find part_pkg_taxproduct for txmatrix deletion: ".
                join(" ", map { "$_ => ". $hash->{$_} } @fields)
-          if $hash->{'actionflag'} eq 'D';
+          if ($hash->{'actionfield'} && $hash->{'actionflag'} eq 'D');
 
         $part_pkg_taxproduct{'description'} = 
           join(' : ', (map{ $hash->{$_} } qw(groupdesc itemdesc)),
@@ -279,7 +279,9 @@ sub batch_import {
 
         return "Can't find tax class for txmatrix deletion: ".
                join(" ", map { "$_ => ". $hash->{$_} } @fields)
-          if ($hash->{'actionflag'} eq 'D' && !$tax_class && $class ne ':');
+          if ( $hash->{'actionflag'} && $hash->{'actionflag'} eq 'D' &&
+               !$tax_class && $class ne ':'
+             );
 
         delete($hash->{$_}) foreach @{$map{$item}};
       }
diff --git a/FS/FS/tax_class.pm b/FS/FS/tax_class.pm
index 8ce70f5..51d87ab 100644
--- a/FS/FS/tax_class.pm
+++ b/FS/FS/tax_class.pm
@@ -158,7 +158,7 @@ sub batch_import {
 
   my $line;
   my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
-  if ( $job || scalar(@column_callbacks) ) {
+  if ( $job || scalar(@column_lengths) ) {
     my $error = csv_from_fixed(\$fh, \$count, \@column_lengths);
     return $error if $error;
   }
diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm
index f45d014..2837f9c 100644
--- a/FS/FS/tax_rate.pm
+++ b/FS/FS/tax_rate.pm
@@ -570,14 +570,22 @@ sub batch_import {
       }
 
       my $actionflag = delete($hash->{'actionflag'});
+
+      $hash->{'taxname'} =~ s/`/'/g; 
+      $hash->{'taxname'} =~ s|\\|/|g;
+
+      return '' if $format eq 'cch';  # but not cch-update
+
       if ($actionflag eq 'I') {
-        $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
+        $insert{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
       }elsif ($actionflag eq 'D') {
-        $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = $hash;
+        $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
       }else{
         return "Unexpected action flag: ". $hash->{'actionflag'};
       }
 
+      delete($hash->{$_}) for keys %$hash;
+
       '';
 
     };
@@ -641,6 +649,18 @@ sub batch_import {
       return $error;
     }
 
+    if (scalar(keys %tax_rate)) { #inserts only, not updates for cch
+
+      my $tax_rate = new FS::tax_rate( \%tax_rate );
+      $error = $tax_rate->insert;
+
+      if ( $error ) {
+        $dbh->rollback if $oldAutoCommit;
+        return "can't insert tax_rate for $line: $error";
+      }
+
+    }
+
     $imported++;
 
   }
@@ -765,23 +785,30 @@ sub process_batch {
     local $FS::UID::AutoCommit = 0;
     my $dbh = dbh;
     my $error = '';
+    my $have_location = 0;
 
     my @list = ( 'CODE',     'codefile',  \&FS::tax_class::batch_import,
                  'PLUS4',    'plus4file', \&FS::cust_tax_location::batch_import,
+                 'ZIP',      'zipfile',   \&FS::cust_tax_location::batch_import,
                  'TXMATRIX', 'txmatrix',  \&FS::part_pkg_taxrate::batch_import,
                  'DETAIL',   'detail',    \&FS::tax_rate::batch_import,
                );
     while( scalar(@list) ) {
       my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
       unless ($files{$file}) {
+        next if $name eq 'PLUS4';
         $error = "No $name supplied";
+        $error = "Neither PLUS4 nor ZIP supplied"
+          if ($name eq 'ZIP' && !$have_location);
         next;
       }
+      $have_location = 1 if $name eq 'PLUS4';
+      my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
       my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
       my $filename = "$dir/".  $files{$file};
       open my $fh, "< $filename" or $error ||= "Can't open $name file: $!";
 
-      $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
+      $error ||= &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
       close $fh;
       unlink $filename or warn "Can't delete $filename: $!";
     }
@@ -804,12 +831,23 @@ sub process_batch {
 
     my @list = ( 'CODE',     'codefile',  \&FS::tax_class::batch_import,
                  'PLUS4',    'plus4file', \&FS::cust_tax_location::batch_import,
+                 'ZIP',      'zipfile',   \&FS::cust_tax_location::batch_import,
                  'TXMATRIX', 'txmatrix',  \&FS::part_pkg_taxrate::batch_import,
                );
     my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;
     while( scalar(@list) ) {
       my ($name, $file, $import_sub) = (shift @list, shift @list, shift @list);
       unless ($files{$file}) {
+        my $vendor = $name eq 'ZIP' ? 'cch' : 'cch-zip';
+        next     # update expected only for previously installed location data
+          if (   ($name eq 'PLUS4' || $name eq 'ZIP')
+               && !scalar( qsearch( { table => 'cust_tax_location',
+                                      hashref => { data_vendor => $vendor },
+                                      select => 'DISTINCT data_vendor',
+                                  } )
+                         )
+             );
+
         $error = "No $name supplied";
         next;
       }
@@ -849,9 +887,10 @@ sub process_batch {
       my ($name, $file, $import_sub) =
         (shift @insert_list, shift @insert_list, shift @insert_list);
 
+      my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
       open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
       $error ||=
-        &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
+        &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
       close $fh;
       unlink $file or warn "Can't delete $file: $!";
     }
@@ -871,9 +910,10 @@ sub process_batch {
       my ($name, $file, $import_sub) =
         (shift @delete_list, shift @delete_list, shift @delete_list);
 
+      my $fmt = $format. ( $name eq 'ZIP' ? '-zip' : '' );
       open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
       $error ||=
-        &{$import_sub}({ 'filehandle' => $fh, 'format' => $format }, $job);
+        &{$import_sub}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
       close $fh;
       unlink $file or warn "Can't delete $file: $!";
     }