X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=FS%2FFS%2Ftax_rate.pm;h=58be8cba6b7ac8765168a1a5b06b73a532d56575;hb=1642d77450f5ecf931bfe94687386583ac3b367d;hp=887c9af2c827a97063f4862caff3133e71cf34ec;hpb=f6dd863ff944a162b2f135e820c7932b2615e332;p=freeside.git

diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm
index 887c9af2c..58be8cba6 100644
--- a/FS/FS/tax_rate.pm
+++ b/FS/FS/tax_rate.pm
@@ -23,6 +23,7 @@ use FS::cust_bill_pkg;
 use FS::cust_tax_location;
 use FS::tax_rate_location;
 use FS::part_pkg_taxrate;
+use FS::part_pkg_taxproduct;
 use FS::cust_main;
 use FS::Misc qw( csv_from_fixed );
 
@@ -230,6 +231,8 @@ sub check {
     || $self->ut_enum('passflag', [ '', 'Y', 'N' ])
     || $self->ut_enum('setuptax', [ '', 'Y' ] )
     || $self->ut_enum('recurtax', [ '', 'Y' ] )
+    || $self->ut_enum('inoutcity', [ '', 'I', 'O' ] )
+    || $self->ut_enum('inoutlocal', [ '', 'I', 'O' ] )
     || $self->ut_enum('manual', [ '', 'Y' ] )
     || $self->ut_enum('disabled', [ '', 'Y' ] )
     || $self->SUPER::check
@@ -640,7 +643,7 @@ sub batch_import {
 
       $hash->{'taxclassnum'} = $tax_class->taxclassnum;
 
-      foreach (qw( inoutcity inoutlocal taxtype taxcat )) {
+      foreach (qw( taxtype taxcat )) {
         delete($hash->{$_});
       }
 
@@ -714,7 +717,10 @@ sub batch_import {
         my $error = $job->update_statustext(
           int( 100 * $imported / $count ). ",Importing tax rates"
         );
-        die $error if $error;
+        if ($error) {
+          $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+          die $error;
+        }
         $last = time;
       }
     }
@@ -758,7 +764,10 @@ sub batch_import {
         my $error = $job->update_statustext(
           int( 100 * $imported / $count ). ",Importing tax rates"
         );
-        die $error if $error;
+        if ($error) {
+          $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+          die $error;
+        }
         $last = time;
       }
     }
@@ -782,7 +791,10 @@ sub batch_import {
         my $error = $job->update_statustext(
           int( 100 * $imported / $count ). ",Importing tax rates"
         );
-        die $error if $error;
+        if ($error) {
+          $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+          die $error;
+        }
         $last = time;
       }
     }
@@ -816,7 +828,10 @@ sub batch_import {
         my $error = $job->update_statustext(
           int( 100 * $imported / $count ). ",Importing tax rates"
         );
-        die $error if $error;
+        if ($error) {
+          $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+          die $error;
+        }
         $last = time;
       }
     }
@@ -916,6 +931,7 @@ sub process_batch_import {
     my $error = '';
     my @insert_list = ();
     my @delete_list = ();
+    my @predelete_list = ();
 
     my @list = ( 'GEOCODE',  'geofile',   \&FS::tax_rate_location::batch_import,
                  'CODE',     'codefile',  \&FS::tax_class::batch_import,
@@ -971,9 +987,26 @@ sub process_batch_import {
       close $dfh;
 
       push @insert_list, $name, $ifh->filename, $import_sub;
-      unshift @delete_list, $name, $dfh->filename, $import_sub;
+      if ( $name eq 'GEOCODE' ) { #handle this whole ordering issue better
+        unshift @predelete_list, $name, $dfh->filename, $import_sub;
+      } else {
+        unshift @delete_list, $name, $dfh->filename, $import_sub;
+      }
+
+    }
+
+    while( scalar(@predelete_list) ) {
+      my ($name, $file, $import_sub) =
+        (shift @predelete_list, shift @predelete_list, shift @predelete_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' => $fmt }, $job);
+      close $fh;
+      unlink $file or warn "Can't delete $file: $!";
     }
+    
     while( scalar(@insert_list) ) {
       my ($name, $file, $import_sub) =
         (shift @insert_list, shift @insert_list, shift @insert_list);
@@ -1022,6 +1055,316 @@ sub process_batch_import {
 
 }
 
+=item process_download_and_reload
+
+Download and process a tax update as a queued JSRPC job after wiping the
+existing wipable tax data.
+
+=cut
+
+sub process_download_and_reload {
+  my $job = shift;
+
+  my $param = thaw(decode_base64($_[0]));
+  my $format = $param->{'format'};        #well... this is all cch specific
+
+  my ( $count, $last, $min_sec, $imported ) = (0, time, 5, 0); #progressbar
+  $count = 100;
+
+  if ( $job ) {  # progress bar
+    my $error = $job->update_statustext( int( 100 * $imported / $count ) );
+    die $error if $error;
+  }
+
+  my $oldAutoCommit = $FS::UID::AutoCommit;
+  local $FS::UID::AutoCommit = 0;
+  my $dbh = dbh;
+  my $error = '';
+
+  my $sql =
+    "SELECT count(*) FROM part_pkg_taxoverride JOIN tax_class ".
+    "USING (taxclassnum) WHERE data_vendor = '$format'";
+  my $sth = $dbh->prepare($sql) or die $dbh->errstr;
+  $sth->execute
+    or die "Unexpected error executing statement $sql: ". $sth->errstr;
+  die "Don't (yet) know how to handle part_pkg_taxoverride records."
+    if $sth->fetchrow_arrayref->[0];
+
+  # really should get a table EXCLUSIVE lock here
+
+  #remember disabled taxes
+  my %disabled_tax_rate = ();
+  my @items = qsearch( { table   => 'tax_rate',
+                         hashref => { disabled => 'Y',
+                                      data_vendor => $format,
+                                    },
+                         select  => 'geocode, taxclassnum',
+                       }
+                     );
+  $count = scalar(@items);
+  foreach my $tax_rate ( @items ) {
+    if ( time - $min_sec > $last ) {
+      my $error = $job->update_statustext(
+        int( 100 * $imported / $count ). ",Remembering disabled taxes"
+      );
+      if ($error) {
+        $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+        die $error;
+      }
+      $last = time;
+    }
+    $imported++;
+    my $tax_class =
+      qsearchs( 'tax_class', { taxclassnum => $tax_rate->taxclassnum } );
+    unless ( $tax_class ) {
+      warn "failed to find tax_class ". $tax_rate->taxclassnum;
+      next;
+    }
+    $disabled_tax_rate{$tax_rate->geocode. ':'. $tax_class->taxclass} = 1;
+  }
+
+  #remember tax products
+  # XXX FIXME  this loop only works when cch is the only data provider
+  my %taxproduct = ();
+  my $extra_sql = "WHERE taxproductnum IS NOT NULL OR ".
+                  "0 < ( SELECT count(*) from part_pkg_option WHERE ".
+                  "       part_pkg_option.pkgpart = part_pkg.pkgpart AND ".
+                  "       optionname LIKE 'usage_taxproductnum_%' AND ".
+                  "       optionvalue != '' )";
+  @items = qsearch( { table => 'part_pkg',
+                      select  => 'DISTINCT pkgpart,taxproductnum',
+                      hashref => {},
+                      extra_sql => $extra_sql,
+                    }
+                  );
+  $count = scalar(@items);
+  $imported = 0;
+  foreach my $part_pkg ( @items ) {
+    if ( time - $min_sec > $last ) {
+      my $error = $job->update_statustext(
+        int( 100 * $imported / $count ). ",Remembering tax products"
+      );
+      if ($error) {
+        $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+        die $error;
+      }
+      $last = time;
+    }
+    $imported++;
+    warn "working with package part ". $part_pkg->pkgpart.
+      "which has a taxproductnum of ". $part_pkg->taxproductnum. "\n" if $DEBUG;
+    my $part_pkg_taxproduct = $part_pkg->taxproduct('');
+    $taxproduct{$part_pkg->pkgpart}{''} = $part_pkg_taxproduct->taxproduct
+      if $part_pkg_taxproduct;
+
+    foreach my $option ( $part_pkg->part_pkg_option ) {
+      next unless $option->optionname =~ /^usage_taxproductnum_(\w)$/;
+      my $class = $1;
+
+      $part_pkg_taxproduct = $part_pkg->taxproduct($class);
+      $taxproduct{$part_pkg->pkgpart}{$class} = $part_pkg_taxproduct->taxproduct
+        if $part_pkg_taxproduct;
+    }
+  }
+
+  #wipe out the old data
+  $error = $job->update_statustext( "0,Removing old tax data" );
+  if ($error) {
+    $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+    die $error;
+  }
+  foreach my $tax_rate_location ( qsearch( 'tax_rate_location',
+                                           { data_vendor => $format,
+                                             disabled    => '',
+                                           }
+                                         )
+                                )
+  {
+    $tax_rate_location->disabled('Y');
+    my $error = $tax_rate_location->replace;
+    if ( $error ) {
+      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+      die $error;
+    }
+  }
+
+  local $FS::part_pkg_taxproduct::delete_kludge = 1;
+  my @table = qw(
+    tax_rate part_pkg_taxrate part_pkg_taxproduct tax_class cust_tax_location
+  );
+  foreach my $table ( @table ) {
+    my $dbh = dbh;
+#    my $primary_key = dbdef->table($table)->primary_key;
+#    my $sql = "SELECT $primary_key FROM $table WHERE data_vendor = ".
+    my $sql = "DELETE FROM $table WHERE data_vendor = ".
+      $dbh->quote($format);
+    my $sth = $dbh->prepare($sql);
+    unless ($sth) {
+      $error = $dbh->errstr;
+      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+      die $error;
+    }
+    unless ($sth->execute) {
+      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+      die "Failed to execute $sql: ". $sth->errstr;
+    }
+#    foreach my $row ( @{ $sth->fetchall_arrayref } ) {
+#      my $record = qsearchs( $table, { $primary_key => $row->[0] } )
+#        or die "Failed to find $table with $primary_key ". $row->[0];
+#      my $error = $record->delete;
+#      if ( $error ) {
+#        $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+#        die $error;
+#      }
+#    }
+  }
+
+  if ( $format eq 'cch' ) {
+    foreach my $cust_tax_location ( qsearch( 'cust_tax_location',
+                                             { data_vendor => "$format-zip" }
+                                           )
+                                  )
+    {
+      my $error = $cust_tax_location->delete;
+      if ( $error ) {
+        $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+        die $error;
+      }
+    }
+  }
+
+  #import new data
+  my $statement = ' &process_download_and_update($job, @_); ';
+  eval $statement;
+  if ($@) {
+    $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+    die $@;
+  }
+
+  #restore taxproducts
+  $count = scalar(keys %taxproduct);
+  $imported = 0;
+  foreach my $pkgpart ( keys %taxproduct ) {
+    warn "restoring taxproductnums on pkgpart $pkgpart\n" if $DEBUG;
+    if ( time - $min_sec > $last ) {
+      my $error = $job->update_statustext(
+        int( 100 * $imported / $count ). ",Restoring tax products"
+      );
+      if ( $error ) {
+        $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+        die $error;
+      }
+      $last = time;
+    }
+    $imported++;
+
+    my $part_pkg = qsearchs('part_pkg', { pkgpart => $pkgpart } );
+    unless ( $part_pkg ) {
+      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+      die "somehow failed to find part_pkg with pkgpart $pkgpart!\n";
+    }
+
+    my %options = $part_pkg->options;
+    my %pkg_svc = map { $_->svcpart => $_->quantity } $part_pkg->pkg_svc;
+    my $primary_svc = $part_pkg->svcpart;
+    my $new = new FS::part_pkg { $part_pkg->hash };
+
+    foreach my $class ( keys %{ $taxproduct{$pkgpart} } ) {
+      warn "working with class '$class'\n" if $DEBUG;
+      my $part_pkg_taxproduct =
+        qsearchs( 'part_pkg_taxproduct',
+                  { taxproduct  => $taxproduct{$pkgpart}{$class},
+                    data_vendor => $format,
+                  }
+                );
+
+      unless ( $part_pkg_taxproduct ) {
+        $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+        die "failed to find part_pkg_taxproduct ($taxproduct{pkgpart}{$class})".
+            " for pkgpart $pkgpart\n";
+      }
+
+      if ( $class eq '' ) {
+        $new->taxproductnum($part_pkg_taxproduct->taxproductnum);
+        next;
+      }
+
+      $options{"usage_taxproductnum_$class"} =
+        $part_pkg_taxproduct->taxproductnum;
+
+    }
+
+    my $error = $new->replace( $part_pkg,
+                               'pkg_svc' => \%pkg_svc,
+                               'primary_svc' => $primary_svc,
+                               'options' => \%options,
+    );
+      
+    if ( $error ) {
+      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+      die $error;
+    }
+  }
+
+  #disable tax_rates
+  $count = scalar(keys %disabled_tax_rate);
+  $imported = 0;
+  foreach my $key (keys %disabled_tax_rate) {
+    if ( time - $min_sec > $last ) {
+      my $error = $job->update_statustext(
+        int( 100 * $imported / $count ). ",Disabling tax rates"
+      );
+      if ( $error ) {
+        $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+        die $error;
+      }
+      $last = time;
+    }
+    $imported++;
+    my ($geocode,$taxclass) = split /:/, $key, 2;
+    my @tax_class = qsearch( 'tax_class', { data_vendor => $format,
+                                            taxclass    => $taxclass,
+                                          } );
+    if (scalar(@tax_class) > 1) {
+      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+      die "found multiple tax_class records for format $format class $taxclass";
+    }
+      
+    unless (scalar(@tax_class)) {
+      warn "no tax_class for format $format class $taxclass\n";
+      next;
+    }
+
+    my @tax_rate =
+      qsearch('tax_rate', { data_vendor  => $format,
+                            geocode      => $geocode,
+                            taxclassnum  => $tax_class[0]->taxclassnum,
+                          }
+    );
+
+    if (scalar(@tax_rate) > 1) {
+      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+      die "found multiple tax_rate records for format $format geocode $geocode".
+          " and taxclass $taxclass ( taxclassnum ". $tax_class[0]->taxclassnum.
+          " )";
+    }
+      
+    if (scalar(@tax_rate)) {
+      $tax_rate[0]->disabled('Y');
+      my $error = $tax_rate[0]->replace;
+      if ( $error ) {
+        $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+        die $error;
+      }
+    }
+  }
+
+  #success!
+
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+  
+}
+
 =item process_download_and_update
 
 Download and process a tax update as a queued JSRPC job
@@ -1081,7 +1424,7 @@ sub process_download_and_update {
     if (-d $dir) {
 
       if (-d "$dir.4") {
-        opendir(my $dirh, $dir) or die "failed to open $dir.4: $!\n";
+        opendir(my $dirh, "$dir.4") or die "failed to open $dir.4: $!\n";
         foreach my $file (readdir($dirh)) {
           unlink "$dir.4/$file" if (-f "$dir.4/$file");
         }
@@ -1191,9 +1534,10 @@ sub process_download_and_update {
 
     my @insert_list = ();
     my @delete_list = ();
+    my @predelete_list = ();
 
     my @list = (
-                 # 'geocode',  \&FS::tax_rate_location::batch_import, 
+                 'geocode',  \&FS::tax_rate_location::batch_import, 
                  'code',     \&FS::tax_class::batch_import,
                  'plus4',    \&FS::cust_tax_location::batch_import,
                  'zip',      \&FS::cust_tax_location::batch_import,
@@ -1256,13 +1600,31 @@ sub process_download_and_update {
       %oldlines = ();
 
       push @insert_list, $name, $ifh->filename, $method;
-      unshift @delete_list, $name, $dfh->filename, $method
-        unless $name eq 'detail';
+      if ( $name eq 'geocode' ) {
+        unshift @predelete_list, $name, $dfh->filename, $method
+          unless $name eq 'detail';
+      } else {
+        unshift @delete_list, $name, $dfh->filename, $method
+          unless $name eq 'detail';
+      }
 
       close $dfh;
       close $ifh;
     }
 
+    while( scalar(@predelete_list) ) {
+      my ($name, $file, $method) =
+        (shift @predelete_list, shift @predelete_list, shift @predelete_list);
+
+      my $fmt = "$format-update";
+      $fmt = $fmt. ( $name eq 'zip' ? '-zip' : '' );
+      open my $fh, "< $file" or $error ||= "Can't open $name file $file: $!";
+      $error ||=
+        &{$method}({ 'filehandle' => $fh, 'format' => $fmt }, $job);
+      close $fh;
+      #unlink $file or warn "Can't delete $file: $!";
+    }
+
     while( scalar(@insert_list) ) {
       my ($name, $file, $method) =
         (shift @insert_list, shift @insert_list, shift @insert_list);
@@ -1429,6 +1791,50 @@ sub _upgrade_data {  # class method
         }
       }
 
+    } elsif ( $dbh->{pg_server_version} =~ /^704/ ) {
+
+      # ideally this would be supported in DBIx-DBSchema and friends
+
+      foreach my $column ( @column ) {
+        my $columndef = dbdef->table($self->table)->column($column);
+        unless ($columndef->type eq 'numeric') {
+
+          warn "updating tax_rate column $column to numeric\n" if $DEBUG;
+
+          foreach my $table ( qw( tax_rate h_tax_rate ) ) {
+
+            my $sql = "ALTER TABLE $table RENAME $column TO old_$column";
+            my $sth = $dbh->prepare($sql) or die $dbh->errstr;
+            $sth->execute or die $sth->errstr;
+
+            my $def = dbdef->table($table)->column($column);
+            $def->type('numeric');
+            $def->length('14,8'); 
+            my $null = $def->null;
+            $def->null('NULL');
+
+            $sql = "ALTER TABLE $table ADD COLUMN ". $def->line($dbh);
+            $sth = $dbh->prepare($sql) or die $dbh->errstr;
+            $sth->execute or die $sth->errstr;
+
+            $sql = "UPDATE $table SET $column = CAST( old_$column AS numeric )";
+            $sth = $dbh->prepare($sql) or die $dbh->errstr;
+            $sth->execute or die $sth->errstr;
+
+            unless ( $null eq 'NULL' ) {
+              $sql = "ALTER TABLE $table ALTER $column SET NOT NULL";
+              $sth = $dbh->prepare($sql) or die $dbh->errstr;
+              $sth->execute or die $sth->errstr;
+            }
+
+            $sql = "ALTER TABLE $table DROP old_$column";
+            $sth = $dbh->prepare($sql) or die $dbh->errstr;
+            $sth->execute or die $sth->errstr;
+
+          }
+        }
+      }
+
     } else {
 
       warn "WARNING: tax_rate table upgrade unsupported for this Pg version\n";