[freeside.git] / FS / FS / tax_rate.pm

package FS::tax_rate;

use strict;
use vars qw( @ISA $DEBUG $me
             %tax_unittypes %tax_maxtypes %tax_basetypes %tax_authorities
             %tax_passtypes %GetInfoType $keep_cch_files );
use Date::Parse;
use DateTime;
use DateTime::Format::Strptime;
use Storable qw( thaw nfreeze );
use IO::File;
use File::Temp;
use LWP::UserAgent;
use HTTP::Request;
use HTTP::Response;
use MIME::Base64;
use DBIx::DBSchema;
use DBIx::DBSchema::Table;
use DBIx::DBSchema::Column;
use FS::Record qw( qsearch qsearchs dbh dbdef );
use FS::tax_class;
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 );

@ISA = qw( FS::Record );

$DEBUG = 0;
$me = '[FS::tax_rate]';
$keep_cch_files = 0;

=head1 NAME

FS::tax_rate - Object methods for tax_rate objects

=head1 SYNOPSIS

  use FS::tax_rate;

  $record = new FS::tax_rate \%hash;
  $record = new FS::tax_rate { 'column' => 'value' };

  $error = $record->insert;

  $error = $new_record->replace($old_record);

  $error = $record->delete;

  $error = $record->check;

=head1 DESCRIPTION

An FS::tax_rate object represents a tax rate, defined by locale.
FS::tax_rate inherits from FS::Record.  The following fields are
currently supported:

=over 4

=item taxnum

primary key (assigned automatically for new tax rates)

=item geocode

a geographic location code provided by a tax data vendor

=item data_vendor

the tax data vendor

=item location

a location code provided by a tax authority

=item taxclassnum

a foreign key into FS::tax_class - the type of tax
referenced but FS::part_pkg_taxrate
eitem effective_date

the time after which the tax applies

=item tax

percentage

=item excessrate

second bracket percentage 

=item taxbase

the amount to which the tax applies (first bracket)

=item taxmax

a cap on the amount of tax if a cap exists

=item usetax

percentage on out of jurisdiction purchases

=item useexcessrate

second bracket percentage on out of jurisdiction purchases

=item unittype

one of the values in %tax_unittypes

=item fee

amount of tax per unit

=item excessfee

second bracket amount of tax per unit

=item feebase

the number of units to which the fee applies (first bracket)

=item feemax

the most units to which fees apply (first and second brackets)

=item maxtype

a value from %tax_maxtypes indicating how brackets accumulate (i.e. monthly, per invoice, etc)

=item taxname

if defined, printed on invoices instead of "Tax"

=item taxauth

a value from %tax_authorities

=item basetype

a value from %tax_basetypes indicating the tax basis

=item passtype

a value from %tax_passtypes indicating how the tax should displayed to the customer

=item passflag

'Y', 'N', or blank indicating the tax can be passed to the customer

=item setuptax

if 'Y', this tax does not apply to setup fees

=item recurtax

if 'Y', this tax does not apply to recurring fees

=item manual

if 'Y', has been manually edited

=back

=head1 METHODS

=over 4

=item new HASHREF

Creates a new tax rate.  To add the tax rate to the database, see L<"insert">.

=cut

sub table { 'tax_rate'; }

=item insert

Adds this tax rate to the database.  If there is an error, returns the error,
otherwise returns false.

=item delete

Deletes this tax rate from the database.  If there is an error, returns the
error, otherwise returns false.

=item replace OLD_RECORD

Replaces the OLD_RECORD with this one in the database.  If there is an error,
returns the error, otherwise returns false.

=item check

Checks all fields to make sure this is a valid tax rate.  If there is an error,
returns the error, otherwise returns false.  Called by the insert and replace
methods.

=cut

sub check {
  my $self = shift;

  foreach (qw( taxbase taxmax )) {
    $self->$_(0) unless $self->$_;
  }

  $self->ut_numbern('taxnum')
    || $self->ut_text('geocode')
    || $self->ut_textn('data_vendor')
    || $self->ut_textn('location')
    || $self->ut_foreign_key('taxclassnum', 'tax_class', 'taxclassnum')
    || $self->ut_snumbern('effective_date')
    || $self->ut_float('tax')
    || $self->ut_floatn('excessrate')
    || $self->ut_money('taxbase')
    || $self->ut_money('taxmax')
    || $self->ut_floatn('usetax')
    || $self->ut_floatn('useexcessrate')
    || $self->ut_numbern('unittype')
    || $self->ut_floatn('fee')
    || $self->ut_floatn('excessfee')
    || $self->ut_floatn('feemax')
    || $self->ut_numbern('maxtype')
    || $self->ut_textn('taxname')
    || $self->ut_numbern('taxauth')
    || $self->ut_numbern('basetype')
    || $self->ut_numbern('passtype')
    || $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
    ;

}

=item taxclass_description

Returns the human understandable value associated with the related
FS::tax_class.

=cut

sub taxclass_description {
  my $self = shift;
  my $tax_class = qsearchs('tax_class', {'taxclassnum' => $self->taxclassnum });
  $tax_class ? $tax_class->description : '';
}

=item unittype_name

Returns the human understandable value associated with the unittype column

=cut

%tax_unittypes = ( '0' => 'access line',
                   '1' => 'minute',
                   '2' => 'account',
);

sub unittype_name {
  my $self = shift;
  $tax_unittypes{$self->unittype};
}

=item maxtype_name

Returns the human understandable value associated with the maxtype column

=cut

%tax_maxtypes = ( '0' => 'receipts per invoice',
                  '1' => 'receipts per item',
                  '2' => 'total utility charges per utility tax year',
                  '3' => 'total charges per utility tax year',
                  '4' => 'receipts per access line',
                  '9' => 'monthly receipts per location',
);

sub maxtype_name {
  my $self = shift;
  $tax_maxtypes{$self->maxtype};
}

=item basetype_name

Returns the human understandable value associated with the basetype column

=cut

%tax_basetypes = ( '0'  => 'sale price',
                   '1'  => 'gross receipts',
                   '2'  => 'sales taxable telecom revenue',
                   '3'  => 'minutes carried',
                   '4'  => 'minutes billed',
                   '5'  => 'gross operating revenue',
                   '6'  => 'access line',
                   '7'  => 'account',
                   '8'  => 'gross revenue',
                   '9'  => 'portion gross receipts attributable to interstate service',
                   '10' => 'access line',
                   '11' => 'gross profits',
                   '12' => 'tariff rate',
                   '14' => 'account',
                   '15' => 'prior year gross receipts',
);

sub basetype_name {
  my $self = shift;
  $tax_basetypes{$self->basetype};
}

=item taxauth_name

Returns the human understandable value associated with the taxauth column

=cut

%tax_authorities = ( '0' => 'federal',
                     '1' => 'state',
                     '2' => 'county',
                     '3' => 'city',
                     '4' => 'local',
                     '5' => 'county administered by state',
                     '6' => 'city administered by state',
                     '7' => 'city administered by county',
                     '8' => 'local administered by state',
                     '9' => 'local administered by county',
);

sub taxauth_name {
  my $self = shift;
  $tax_authorities{$self->taxauth};
}

=item passtype_name

Returns the human understandable value associated with the passtype column

=cut

%tax_passtypes = ( '0' => 'separate tax line',
                   '1' => 'separate surcharge line',
                   '2' => 'surcharge not separated',
                   '3' => 'included in base rate',
);

sub passtype_name {
  my $self = shift;
  $tax_passtypes{$self->passtype};
}

=item taxline TAXABLES, [ OPTIONSHASH ]

Returns a listref of a name and an amount of tax calculated for the list
of packages/amounts referenced by TAXABLES.  If an error occurs, a message
is returned as a scalar.

=cut

sub taxline {
  my $self = shift;

  my $taxables;
  my %opt = ();

  if (ref($_[0]) eq 'ARRAY') {
    $taxables = shift;
    %opt = @_;
  }else{
    $taxables = [ @_ ];
    #exemptions would be broken in this case
  }

  my $name = $self->taxname;
  $name = 'Other surcharges'
    if ($self->passtype == 2);
  my $amount = 0;
  
  if ( $self->disabled ) { # we always know how to handle disabled taxes
    return {
      'name'   => $name,
      'amount' => $amount,
    };
  }

  my $taxable_charged = 0;
  my @cust_bill_pkg = grep { $taxable_charged += $_ unless ref; ref; }
                      @$taxables;

  warn "calculating taxes for ". $self->taxnum. " on ".
    join (",", map { $_->pkgnum } @cust_bill_pkg)
    if $DEBUG;

  if ($self->passflag eq 'N') {
    # return "fatal: can't (yet) handle taxes not passed to the customer";
    # until someone needs to track these in freeside
    return {
      'name'   => $name,
      'amount' => 0,
    };
  }

  my $maxtype = $self->maxtype || 0;
  if ($maxtype != 0 && $maxtype != 9) {
    return $self->_fatal_or_null( 'tax with "'.
                                    $self->maxtype_name. '" threshold'
                                );
  }

  if ($maxtype == 9) {
    return
      $self->_fatal_or_null( 'tax with "'. $self->maxtype_name. '" threshold' );
                                                                # "texas" tax
  }

  # we treat gross revenue as gross receipts and expect the tax data
  # to DTRT (i.e. tax on tax rules)
  if ($self->basetype != 0 && $self->basetype != 1 &&
      $self->basetype != 5 && $self->basetype != 6 &&
      $self->basetype != 7 && $self->basetype != 8 &&
      $self->basetype != 14
  ) {
    return
      $self->_fatal_or_null( 'tax with "'. $self->basetype_name. '" basis' );
  }

  unless ($self->setuptax =~ /^Y$/i) {
    $taxable_charged += $_->setup foreach @cust_bill_pkg;
  }
  unless ($self->recurtax =~ /^Y$/i) {
    $taxable_charged += $_->recur foreach @cust_bill_pkg;
  }

  my $taxable_units = 0;
  unless ($self->recurtax =~ /^Y$/i) {
    if (( $self->unittype || 0 ) == 0) {
      my %seen = ();
      foreach (@cust_bill_pkg) {
        $taxable_units += $_->units
          unless $seen{$_->pkgnum};
        $seen{$_->pkgnum}++;
      }
    }elsif ($self->unittype == 1) {
      return $self->_fatal_or_null( 'fee with minute unit type' );
    }elsif ($self->unittype == 2) {
      $taxable_units = 1;
    }else {
      return $self->_fatal_or_null( 'unknown unit type in tax'. $self->taxnum );
    }
  }

  #
  # XXX insert exemption handling here
  #
  # the tax or fee is applied to taxbase or feebase and then
  # the excessrate or excess fee is applied to taxmax or feemax
  #

  $amount += $taxable_charged * $self->tax;
  $amount += $taxable_units * $self->fee;
  
  warn "calculated taxes as [ $name, $amount ]\n"
    if $DEBUG;

  return {
    'name'   => $name,
    'amount' => $amount,
  };

}

sub _fatal_or_null {
  my ($self, $error) = @_;

  my $conf = new FS::Conf;

  $error = "can't yet handle $error";
  my $name = $self->taxname;
  $name = 'Other surcharges'
    if ($self->passtype == 2);

  if ($conf->exists('ignore_incalculable_taxes')) {
    warn "WARNING: $error; billing anyway per ignore_incalculable_taxes conf\n";
    return { name => $name, amount => 0 };
  } else {
    return "fatal: $error";
  }
}

=item tax_on_tax CUST_MAIN

Returns a list of taxes which are candidates for taxing taxes for the
given customer (see L<FS::cust_main>)

=cut

sub tax_on_tax {
  my $self = shift;
  my $cust_main = shift;

  warn "looking up taxes on tax ". $self->taxnum. " for customer ".
    $cust_main->custnum
    if $DEBUG;

  my $geocode = $cust_main->geocode($self->data_vendor);

  # CCH oddness in m2m
  my $dbh = dbh;
  my $extra_sql = ' AND ('.
    join(' OR ', map{ 'geocode = '. $dbh->quote(substr($geocode, 0, $_)) }
                 qw(10 5 2)
        ).
    ')';

  my $order_by = 'ORDER BY taxclassnum, length(geocode) desc';
  my $select   = 'DISTINCT ON(taxclassnum) *';

  # should qsearch preface columns with the table to facilitate joins?
  my @taxclassnums = map { $_->taxclassnum }
    qsearch( { 'table'     => 'part_pkg_taxrate',
               'select'    => $select,
               'hashref'   => { 'data_vendor'      => $self->data_vendor,
                                'taxclassnumtaxed' => $self->taxclassnum,
                              },
               'extra_sql' => $extra_sql,
               'order_by'  => $order_by,
           } );

  return () unless @taxclassnums;

  $extra_sql =
    "AND (".  join(' OR ', map { "taxclassnum = $_" } @taxclassnums ). ")";

  qsearch({ 'table'     => 'tax_rate',
            'hashref'   => { 'geocode' => $geocode, },
            'extra_sql' => $extra_sql,
         })

}

=item tax_rate_location

Returns an object representing the location associated with this tax
(see L<FS::tax_rate_location>)

=cut

sub tax_rate_location {
  my $self = shift;

  qsearchs({ 'table'     => 'tax_rate_location',
             'hashref'   => { 'data_vendor' => $self->data_vendor, 
                              'geocode'     => $self->geocode,
                              'disabled'    => '',
                            },
          }) ||
  new FS::tax_rate_location;

}

=back

=head1 SUBROUTINES

=over 4

=item batch_import

=cut

sub _progressbar_foo {
  return (0, time, 5);
}

sub batch_import {
  my ($param, $job) = @_;

  my $fh = $param->{filehandle};
  my $format = $param->{'format'};

  my %insert = ();
  my %delete = ();

  my @fields;
  my $hook;

  my @column_lengths = ();
  my @column_callbacks = ();
  if ( $format eq 'cch-fixed' || $format eq 'cch-fixed-update' ) {
    $format =~ s/-fixed//;
    my $date_format = sub { my $r='';
                            /^(\d{4})(\d{2})(\d{2})$/ && ($r="$3/$2/$1");
                            $r;
                          };
    my $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
    push @column_lengths, qw( 10 1 1 8 8 5 8 8 8 1 2 2 30 8 8 10 2 8 2 1 2 2 );
    push @column_lengths, 1 if $format eq 'cch-update';
    push @column_callbacks, $trim foreach (@column_lengths); # 5, 6, 15, 17 esp
    $column_callbacks[8] = $date_format;
  }
  
  my $line;
  my ( $count, $last, $min_sec ) = _progressbar_foo();
  if ( $job || scalar(@column_callbacks) ) {
    my $error =
      csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
    return $error if $error;
  }
  $count *=2;

  if ( $format eq 'cch' || $format eq 'cch-update' ) {
    @fields = qw( geocode inoutcity inoutlocal tax location taxbase taxmax
                  excessrate effective_date taxauth taxtype taxcat taxname
                  usetax useexcessrate fee unittype feemax maxtype passflag
                  passtype basetype );
    push @fields, 'actionflag' if $format eq 'cch-update';

    $hook = sub {
      my $hash = shift;

      $hash->{'actionflag'} ='I' if ($hash->{'data_vendor'} eq 'cch');
      $hash->{'data_vendor'} ='cch';
      my $parser = new DateTime::Format::Strptime( pattern => "%m/%d/%Y",
                                                   time_zone => 'floating',
                                                 );
      my $dt = $parser->parse_datetime( $hash->{'effective_date'} );
      $hash->{'effective_date'} = $dt ? $dt->epoch : '';

      $hash->{$_} =~ s/\s//g foreach qw( inoutcity inoutlocal ) ; 
      $hash->{$_} = sprintf("%.2f", $hash->{$_}) foreach qw( taxbase taxmax );

      my $taxclassid =
        join(':', map{ $hash->{$_} } qw(taxtype taxcat) );

      my %tax_class = ( 'data_vendor'  => 'cch', 
                        'taxclass' => $taxclassid,
                      );

      my $tax_class = qsearchs( 'tax_class', \%tax_class );
      return "Error updating tax rate: no tax class $taxclassid"
        unless $tax_class;

      $hash->{'taxclassnum'} = $tax_class->taxclassnum;

      foreach (qw( taxtype taxcat )) {
        delete($hash->{$_});
      }

      my %passflagmap = ( '0' => '',
                          '1' => 'Y',
                          '2' => 'N',
                        );
      $hash->{'passflag'} = $passflagmap{$hash->{'passflag'}}
        if exists $passflagmap{$hash->{'passflag'}};

      foreach (keys %$hash) {
        $hash->{$_} = substr($hash->{$_}, 0, 80)
          if length($hash->{$_}) > 80;
      }

      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 };
      }elsif ($actionflag eq 'D') {
        $delete{ $hash->{'geocode'}. ':'. $hash->{'taxclassnum'} } = { %$hash };
      }else{
        return "Unexpected action flag: ". $hash->{'actionflag'};
      }

      delete($hash->{$_}) for keys %$hash;

      '';

    };

  } elsif ( $format eq 'extended' ) {
    die "unimplemented\n";
    @fields = qw( );
    $hook = sub {};
  } else {
    die "unknown format $format";
  }

  eval "use Text::CSV_XS;";
  die $@ if $@;

  my $csv = new Text::CSV_XS;

  my $imported = 0;

  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;
  
  while ( defined($line=<$fh>) ) {
    $csv->parse($line) or do {
      $dbh->rollback if $oldAutoCommit;
      return "can't parse: ". $csv->error_input();
    };

    if ( $job ) {  # progress bar
      if ( time - $min_sec > $last ) {
        my $error = $job->update_statustext(
          int( 100 * $imported / $count ). ",Importing tax rates"
        );
        if ($error) {
          $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
          die $error;
        }
        $last = time;
      }
    }

    my @columns = $csv->fields();

    my %tax_rate = ( 'data_vendor' => $format );
    foreach my $field ( @fields ) {
      $tax_rate{$field} = shift @columns; 
    }
    if ( scalar( @columns ) ) {
      $dbh->rollback if $oldAutoCommit;
      return "Unexpected trailing columns in line (wrong format?): $line";
    }

    my $error = &{$hook}(\%tax_rate);
    if ( $error ) {
      $dbh->rollback if $oldAutoCommit;
      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++;

  }

  for (grep { !exists($delete{$_}) } keys %insert) {
    if ( $job ) {  # progress bar
      if ( time - $min_sec > $last ) {
        my $error = $job->update_statustext(
          int( 100 * $imported / $count ). ",Importing tax rates"
        );
        if ($error) {
          $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
          die $error;
        }
        $last = time;
      }
    }

    my $tax_rate = new FS::tax_rate( $insert{$_} );
    my $error = $tax_rate->insert;

    if ( $error ) {
      $dbh->rollback if $oldAutoCommit;
      my $hashref = $insert{$_};
      $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
      return "can't insert tax_rate for $line: $error";
    }

    $imported++;
  }

  for (grep { exists($delete{$_}) } keys %insert) {
    if ( $job ) {  # progress bar
      if ( time - $min_sec > $last ) {
        my $error = $job->update_statustext(
          int( 100 * $imported / $count ). ",Importing tax rates"
        );
        if ($error) {
          $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
          die $error;
        }
        $last = time;
      }
    }

    my $old = qsearchs( 'tax_rate', $delete{$_} );
    unless ($old) {
      $dbh->rollback if $oldAutoCommit;
      $old = $delete{$_};
      return "can't find tax_rate to replace for: ".
        #join(" ", map { "$_ => ". $old->{$_} } @fields);
        join(" ", map { "$_ => ". $old->{$_} } keys(%$old) );
    }
    my $new = new FS::tax_rate({ $old->hash, %{$insert{$_}}, 'manual' => ''  });
    $new->taxnum($old->taxnum);
    my $error = $new->replace($old);

    if ( $error ) {
      $dbh->rollback if $oldAutoCommit;
      my $hashref = $insert{$_};
      $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
      return "can't replace tax_rate for $line: $error";
    }

    $imported++;
    $imported++;
  }

  for (grep { !exists($insert{$_}) } keys %delete) {
    if ( $job ) {  # progress bar
      if ( time - $min_sec > $last ) {
        my $error = $job->update_statustext(
          int( 100 * $imported / $count ). ",Importing tax rates"
        );
        if ($error) {
          $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
          die $error;
        }
        $last = time;
      }
    }

    my $tax_rate = qsearchs( 'tax_rate', $delete{$_} );
    unless ($tax_rate) {
      $dbh->rollback if $oldAutoCommit;
      $tax_rate = $delete{$_};
      return "can't find tax_rate to delete for: ".
        #join(" ", map { "$_ => ". $tax_rate->{$_} } @fields);
        join(" ", map { "$_ => ". $tax_rate->{$_} } keys(%$tax_rate) );
    }
    my $error = $tax_rate->delete;

    if ( $error ) {
      $dbh->rollback if $oldAutoCommit;
      my $hashref = $delete{$_};
      $line = join(", ", map { "$_ => ". $hashref->{$_} } keys(%$hashref) );
      return "can't delete tax_rate for $line: $error";
    }

    $imported++;
  }

  $dbh->commit or die $dbh->errstr if $oldAutoCommit;

  return "Empty file!" unless ($imported || $format eq 'cch-update');

  ''; #no error

}

=item process_batch_import

Load a batch import as a queued JSRPC job

=cut

sub process_batch_import {
  my $job = shift;

  my $oldAutoCommit = $FS::UID::AutoCommit;
  local $FS::UID::AutoCommit = 0;
  my $dbh = dbh;

  my $param = thaw(decode_base64(shift));
  my $args = '$job, encode_base64( nfreeze( $param ) )';

  my $method = '_perform_batch_import';
  if ( $param->{reload} ) {
    $method = 'process_batch_reload';
  }

  eval "$method($args);";
  if ($@) {
    $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
    die $@;
  }

  #success!
  $dbh->commit or die $dbh->errstr if $oldAutoCommit;
}

sub _perform_batch_import {
  my $job = shift;

  my $param = thaw(decode_base64(shift));
  my $format = $param->{'format'};        #well... this is all cch specific

  my $files = $param->{'uploaded_files'}
    or die "No files provided.";

  my (%files) = map { /^(\w+):((taxdata\/\w+\.\w+\/)?[\.\w]+)$/ ? ($1,$2):() }
                split /,/, $files;

  if ( $format eq 'cch' || $format eq 'cch-fixed'
    || $format eq 'cch-update' || $format eq 'cch-fixed-update' )
  {

    my $oldAutoCommit = $FS::UID::AutoCommit;
    local $FS::UID::AutoCommit = 0;
    my $dbh = dbh;
    my $error = '';
    my @insert_list = ();
    my @delete_list = ();
    my @predelete_list = ();
    my $insertname = '';
    my $deletename = '';
    my $dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc;

    my @list = ( '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,
                 'TXMATRIX', \&FS::part_pkg_taxrate::batch_import,
                 'DETAIL',   \&FS::tax_rate::batch_import,
               );
    while( scalar(@list) ) {
      my ( $name, $import_sub ) = splice( @list, 0, 2 );
      my $file = lc($name). 'file';

      unless ($files{$file}) {
        $error = "No $name supplied";
        next;
      }
      next if $name eq 'DETAIL' && $format =~ /update/;

      my $filename = "$dir/".  $files{$file};

      if ( $format =~ /update/ ) {

        ( $error, $insertname, $deletename ) =
          _perform_cch_insert_delete_split( $name, $filename, $dir, $format )
          unless $error;
        last if $error;

        unlink $filename or warn "Can't delete $filename: $!"
          unless $keep_cch_files;
        push @insert_list, $name, $insertname, $import_sub, $format;
        if ( $name eq 'GEOCODE' ) { #handle this whole ordering issue better
          unshift @predelete_list, $name, $deletename, $import_sub;
        } else {
          unshift @delete_list, $name, $deletename, $import_sub;
        }

      } else {

        push @insert_list, $name, $filename, $import_sub, $format;

      }

    }

    push @insert_list,
      'DETAIL', "$dir/".$files{detail}, \&FS::tax_rate::batch_import, $format
      if $format =~ /update/;

    $error ||= _perform_cch_tax_import( $job,
                                        [ @predelete_list ],
                                        [ @insert_list ],
                                        [ @delete_list ],
    );
    
    
    @list = ( @predelete_list, @insert_list, @delete_list );
    while( !$keep_cch_files && scalar(@list) ) {
      my ( undef, $file, undef, undef ) = splice( @list, 0, 4 );
      unlink $file or warn "Can't delete $file: $!";
    }

    if ($error) {
      $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
      die $error;
    }else{
      $dbh->commit or die $dbh->errstr if $oldAutoCommit;
    }

  }else{
    die "Unknown format: $format";
  }

}


sub _perform_cch_tax_import {
  my ( $job, $predelete_list, $insert_list, $delete_list ) = @_;

  my $error = '';
  foreach my $list ($predelete_list, $insert_list, $delete_list) {
    while( scalar(@$list) ) {
      my ( $name, $file, $method, $format ) = splice( @$list, 0, 4 );
      my $fmt = "$format-update";
      $fmt = $format. ( lc($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;
    }
  }

  return $error;
}

sub _perform_cch_insert_delete_split {
  my ($name, $filename, $dir, $format) = @_;

  my $error = '';

  open my $fh, "< $filename"
    or $error ||= "Can't open $name file $filename: $!";

  my $ifh = new File::Temp( TEMPLATE => "$name.insert.XXXXXXXX",
                            DIR      => $dir,
                            UNLINK   => 0,     #meh
                          ) or die "can't open temp file: $!\n";
  my $insertname = $ifh->filename;

  my $dfh = new File::Temp( TEMPLATE => "$name.delete.XXXXXXXX",
                            DIR      => $dir,
                            UNLINK   => 0,     #meh
                          ) or die "can't open temp file: $!\n";
  my $deletename = $dfh->filename;

  my $insert_pattern = ($format eq 'cch-update') ? qr/"I"\s*$/ : qr/I\s*$/;
  my $delete_pattern = ($format eq 'cch-update') ? qr/"D"\s*$/ : qr/D\s*$/;
  while(<$fh>) {
    my $handle = '';
    $handle = $ifh if $_ =~ /$insert_pattern/;
    $handle = $dfh if $_ =~ /$delete_pattern/;
    unless ($handle) {
      $error = "bad input line: $_" unless $handle;
      last;
    }
    print $handle $_;
  }
  close $fh;
  close $ifh;
  close $dfh;

  return ($error, $insertname, $deletename);
}

sub _perform_cch_diff {
  my ($name, $newdir, $olddir) = @_;

  my %oldlines = ();

  if ($olddir) {
    open my $oldcsvfh, "$olddir/$name.txt"
      or die "failed to open $olddir/$name.txt: $!\n";

    while(<$oldcsvfh>) {
      chomp;
      $oldlines{$_} = 1;
    }
    close $oldcsvfh;
  }

  open my $newcsvfh, "$newdir/$name.txt"
    or die "failed to open $newdir/$name.txt: $!\n";
    
  my $dfh = new File::Temp( TEMPLATE => "$name.diff.XXXXXXXX",
                            DIR      => "$newdir",
                            UNLINK   => 0,     #meh
                          ) or die "can't open temp file: $!\n";
  my $diffname = $dfh->filename;

  while(<$newcsvfh>) {
    chomp;
    if (exists($oldlines{$_})) {
      $oldlines{$_} = 0;
    } else {
      print $dfh $_, ',"I"', "\n";
    }
  }
  close $newcsvfh;

  for (keys %oldlines) {
    print $dfh $_, ',"D"', "\n" if $oldlines{$_};
  }

  close $dfh;

  return $diffname;
}

sub _cch_fetch_and_unzip {
  my ( $job, $urls, $secret, $dir ) = @_;

  my $ua = new LWP::UserAgent;
  foreach my $url (split ',', $urls) {
    my @name = split '/', $url;  #somewhat restrictive
    my $name = pop @name;
    $name =~ /([\w.]+)/; # untaint that which we don't trust so much any more
    $name = $1;
      
    open my $taxfh, ">$dir/$name" or die "Can't open $dir/$name: $!\n";
     
    my ( $imported, $last, $min_sec ) = _progressbar_foo();
    my $res = $ua->request(
      new HTTP::Request( GET => $url ),
      sub {
            print $taxfh $_[0] or die "Can't write to $dir/$name: $!\n";
            my $content_length = $_[1]->content_length;
            $imported += length($_[0]);
            if ( time - $min_sec > $last ) {
              my $error = $job->update_statustext(
                ($content_length ? int(100 * $imported/$content_length) : 0 ).
                ",Downloading data from CCH"
              );
              die $error if $error;
              $last = time;
            }
      },
    );
    die "download of $url failed: ". $res->status_line
      unless $res->is_success;
      
    close $taxfh;
    my $error = $job->update_statustext( "0,Unpacking data" );
    die $error if $error;
    $secret =~ /([\w.]+)/; # untaint that which we don't trust so much any more
    $secret = $1;
    system('unzip', "-P", $secret, "-d", "$dir",  "$dir/$name") == 0
      or die "unzip -P $secret -d $dir $dir/$name failed";
    #unlink "$dir/$name";
  }
}
 
sub _cch_extract_csv_from_dbf {
  my ( $job, $dir, $name ) = @_;

  eval "use Text::CSV_XS;";
  die $@ if $@;

  eval "use XBase;";
  die $@ if $@;

  my ( $imported, $last, $min_sec ) = _progressbar_foo();
  my $error = $job->update_statustext( "0,Unpacking $name" );
  die $error if $error;
  warn "opening $dir.new/$name.dbf\n" if $DEBUG;
  my $table = new XBase 'name' => "$dir.new/$name.dbf";
  die "failed to access $dir.new/$name.dbf: ". XBase->errstr
    unless defined($table);
  my $count = $table->last_record; # approximately;
  open my $csvfh, ">$dir.new/$name.txt"
    or die "failed to open $dir.new/$name.txt: $!\n";

  my $csv = new Text::CSV_XS { 'always_quote' => 1 };
  my @fields = $table->field_names;
  my $cursor = $table->prepare_select;
  my $format_date =
    sub { my $date = shift;
          $date =~ /^(\d{4})(\d{2})(\d{2})$/ && ($date = "$2/$3/$1");
          $date;
        };
  while (my $row = $cursor->fetch_hashref) {
    $csv->combine( map { ($table->field_type($_) eq 'D')
                         ? &{$format_date}($row->{$_}) 
                         : $row->{$_}
                       }
                   @fields
    );
    print $csvfh $csv->string, "\n";
    $imported++;
    if ( time - $min_sec > $last ) {
      my $error = $job->update_statustext(
        int(100 * $imported/$count).  ",Unpacking $name"
      );
      die $error if $error;
      $last = time;
    }
  }
  $table->close;
  close $csvfh;
}

sub _remember_disabled_taxes {
  my ( $job, $format, $disabled_tax_rate ) = @_;

  # cch specific hash

  my ( $imported, $last, $min_sec ) = _progressbar_foo();

  my @items = qsearch( { table   => 'tax_rate',
                         hashref => { disabled => 'Y',
                                      data_vendor => $format,
                                    },
                         select  => 'geocode, taxclassnum',
                       }
                     );
  my $count = scalar(@items);
  foreach my $tax_rate ( @items ) {
    if ( time - $min_sec > $last ) {
      $job->update_statustext(
        int( 100 * $imported / $count ). ",Remembering disabled taxes"
      );
      $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;
  }
}

sub _remember_tax_products {
  my ( $job, $format, $taxproduct ) = @_;

  # XXX FIXME  this loop only works when cch is the only data provider

  my ( $imported, $last, $min_sec ) = _progressbar_foo();

  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 != '' )";
  my @items = qsearch( { table => 'part_pkg',
                         select  => 'DISTINCT pkgpart,taxproductnum',
                         hashref => {},
                         extra_sql => $extra_sql,
                       }
                     );
  my $count = scalar(@items);
  foreach my $part_pkg ( @items ) {
    if ( time - $min_sec > $last ) {
      $job->update_statustext(
        int( 100 * $imported / $count ). ",Remembering tax products"
      );
      $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 && $part_pkg_taxproduct->data_vendor eq $format;

    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 && $part_pkg_taxproduct->data_vendor eq $format;
    }
  }
}

sub _restore_remembered_tax_products {
  my ( $job, $format, $taxproduct ) = @_;

  # cch specific

  my ( $imported, $last, $min_sec ) = _progressbar_foo();
  my $count = scalar(keys %$taxproduct);
  foreach my $pkgpart ( keys %$taxproduct ) {
    warn "restoring taxproductnums on pkgpart $pkgpart\n" if $DEBUG;
    if ( time - $min_sec > $last ) {
      $job->update_statustext(
        int( 100 * $imported / $count ). ",Restoring tax products"
      );
      $last = time;
    }
    $imported++;

    my $part_pkg = qsearchs('part_pkg', { pkgpart => $pkgpart } );
    unless ( $part_pkg ) {
      return "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 ) {
        return "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,
    );
      
    return $error if $error;

  }

  '';
}

sub _restore_remembered_disabled_taxes {
  my ( $job, $format, $disabled_tax_rate ) = @_;

  my ( $imported, $last, $min_sec ) = _progressbar_foo();
  my $count = scalar(keys %$disabled_tax_rate);
  foreach my $key (keys %$disabled_tax_rate) {
    if ( time - $min_sec > $last ) {
      $job->update_statustext(
        int( 100 * $imported / $count ). ",Disabling tax rates"
      );
      $last = time;
    }
    $imported++;
    my ($geocode,$taxclass) = split /:/, $key, 2;
    my @tax_class = qsearch( 'tax_class', { data_vendor => $format,
                                            taxclass    => $taxclass,
                                          } );
    return "found multiple tax_class records for format $format class $taxclass"
      if scalar(@tax_class) > 1;
      
    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) {
      return "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;
      return $error if $error;
    }
  }
}

sub _remove_old_tax_data {
  my ( $job, $format ) = @_;

  my $dbh = dbh;
  my $error = $job->update_statustext( "0,Removing old tax data" );
  die $error if $error;

  my $sql = "UPDATE public.tax_rate_location SET disabled='Y' ".
    "WHERE data_vendor = ".  $dbh->quote($format);
  $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;

  my @table = qw(
    tax_rate part_pkg_taxrate part_pkg_taxproduct tax_class cust_tax_location
  );
  foreach my $table ( @table ) {
    $sql = "DELETE FROM public.$table WHERE data_vendor = ".
      $dbh->quote($format);
    $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
  }

  if ( $format eq 'cch' ) {
    $sql = "DELETE FROM public.cust_tax_location WHERE data_vendor = ".
      $dbh->quote("$format-zip");
    $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
  }

  '';
}

sub _create_temporary_tables {
  my ( $job, $format ) = @_;

  my $dbh = dbh;
  my $error = $job->update_statustext( "0,Creating temporary tables" );
  die $error if $error;

  my @table = qw( tax_rate
                  tax_rate_location
                  part_pkg_taxrate
                  part_pkg_taxproduct
                  tax_class
                  cust_tax_location
  );
  foreach my $table ( @table ) {
    my $sql =
      "CREATE TEMPORARY TABLE $table ( LIKE $table INCLUDING DEFAULTS )";
    $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
  }

  '';
}

sub _copy_from_temp {
  my ( $job, $format ) = @_;

  my $dbh = dbh;
  my $error = $job->update_statustext( "0,Making permanent" );
  die $error if $error;

  my @table = qw( tax_rate
                  tax_rate_location
                  part_pkg_taxrate
                  part_pkg_taxproduct
                  tax_class
                  cust_tax_location
  );
  foreach my $table ( @table ) {
    my $sql =
      "INSERT INTO public.$table SELECT * from $table";
    $dbh->do($sql) or return "Failed to execute $sql: ". $dbh->errstr;
  }

  '';
}

=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 {
  _process_reload('process_download_and_update', @_);
}

  
=item process_batch_reload

Load and process a tax update from the provided files as a queued JSRPC job
after wiping the existing wipable tax data.

=cut

sub process_batch_reload {
  _process_reload('_perform_batch_import', @_);
}

  
sub _process_reload {
  my ( $method, $job ) = ( shift, shift );

  my $param = thaw(decode_base64($_[0]));
  my $format = $param->{'format'};        #well... this is all cch specific

  my ( $imported, $last, $min_sec ) = _progressbar_foo();

  if ( $job ) {  # progress bar
    my $error = $job->update_statustext( 0 );
    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 = ();
  $error ||= _remember_disabled_taxes( $job, $format, \%disabled_tax_rate );

  #remember tax products
  my %taxproduct = ();
  $error ||= _remember_tax_products( $job, $format, \%taxproduct );

  #create temp tables
  $error ||= _create_temporary_tables( $job, $format );

  #import new data
  unless ($error) {
    my $args = '$job, @_';
    eval "$method($args);";
    $error = $@ if $@;
  }

  #restore taxproducts
  $error ||= _restore_remembered_tax_products( $job, $format, \%taxproduct );

  #disable tax_rates
  $error ||=
   _restore_remembered_disabled_taxes( $job, $format, \%disabled_tax_rate );

  #wipe out the old data
  $error ||= _remove_old_tax_data( $job, $format ); 

  #untemporize
  $error ||= _copy_from_temp( $job, $format );

  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

=cut

sub process_download_and_update {
  my $job = shift;

  my $param = thaw(decode_base64(shift));
  my $format = $param->{'format'};        #well... this is all cch specific

  my ( $imported, $last, $min_sec ) = _progressbar_foo();

  if ( $job ) {  # progress bar
    my $error = $job->update_statustext( 0);
    die $error if $error;
  }

  my $cache_dir = '%%%FREESIDE_CACHE%%%/cache.'. $FS::UID::datasrc. '/';
  my $dir = $cache_dir. 'taxdata';
  unless (-d $dir) {
    mkdir $dir or die "can't create $dir: $!\n";
  }

  if ($format eq 'cch') {

    my @namelist = qw( code detail geocode plus4 txmatrix zip );

    my $conf = new FS::Conf;
    die "direct download of tax data not enabled\n" 
      unless $conf->exists('taxdatadirectdownload');
    my ( $urls, $username, $secret, $states ) =
      $conf->config('taxdatadirectdownload');
    die "No tax download URL provided.  ".
        "Did you set the taxdatadirectdownload configuration value?\n"
      unless $urls;

    $dir .= '/cch';

    my $dbh = dbh;
    my $error = '';

    # really should get a table EXCLUSIVE lock here
    # check if initial import or update
    #
    # relying on mkdir "$dir.new" as a mutex
    
    my $sql = "SELECT count(*) from tax_rate WHERE data_vendor='$format'";
    my $sth = $dbh->prepare($sql) or die $dbh->errstr;
    $sth->execute() or die $sth->errstr;
    my $update = $sth->fetchrow_arrayref->[0];

    # create cache and/or rotate old tax data

    if (-d $dir) {

      if (-d "$dir.4") {
        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");
        }
        closedir($dirh);
        rmdir "$dir.4";
      }

      for (3, 2, 1) {
        if ( -e "$dir.$_" ) {
          rename "$dir.$_", "$dir.". ($_+1) or die "can't rename $dir.$_: $!\n";
        }
      }
      rename "$dir", "$dir.1" or die "can't rename $dir: $!\n";

    } else {

      die "can't find previous tax data\n" if $update;

    }

    mkdir "$dir.new" or die "can't create $dir.new: $!\n";
    
    # fetch and unpack the zip files

    _cch_fetch_and_unzip( $job, $urls, $secret, "$dir.new" );
 
    # extract csv files from the dbf files

    foreach my $name ( @namelist ) {
      _cch_extract_csv_from_dbf( $job, $dir, $name ); 
    }

    # generate the diff files

    my @list = ();
    foreach my $name ( @namelist ) {
      my $difffile = "$dir.new/$name.txt";
      if ($update) {
        my $error = $job->update_statustext( "0,Comparing to previous $name" );
        die $error if $error;
        warn "processing $dir.new/$name.txt\n" if $DEBUG;
        my $olddir = $update ? "$dir.1" : "";
        $difffile = _perform_cch_diff( $name, "$dir.new", $olddir );
      } else {
        $difffile =~ s/^$cache_dir//;
        push @list, "${name}file:$difffile";
      }
    }

    # perform the import
    local $keep_cch_files = 1;
    $param->{uploaded_files} = join( ',', @list );
    $param->{format} .= '-update' if $update;
    $error ||=
      _perform_batch_import( $job, encode_base64( nfreeze( $param ) ) );
    
    rename "$dir.new", "$dir"
      or die "cch tax update processed, but can't rename $dir.new: $!\n";

  }else{
    die "Unknown format: $format";
  }
}

=item browse_queries PARAMS

Returns a list consisting of a hashref suited for use as the argument
to qsearch, and sql query string.  Each is based on the PARAMS hashref
of keys and values which frequently would be passed as C<scalar($cgi->Vars)>
from a form.  This conveniently creates the query hashref and count_query
string required by the browse and search elements.  As a side effect, 
the PARAMS hashref is untainted and keys with unexpected values are removed.

=cut

sub browse_queries {
  my $params = shift;

  my $query = {
                'table'     => 'tax_rate',
                'hashref'   => {},
                'order_by'  => 'ORDER BY geocode, taxclassnum',
              },

  my $extra_sql = '';

  if ( $params->{data_vendor} =~ /^(\w+)$/ ) {
    $extra_sql .= ' WHERE data_vendor = '. dbh->quote($1);
  } else {
    delete $params->{data_vendor};
  }
   
  if ( $params->{geocode} =~ /^(\w+)$/ ) {
    $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
                    'geocode LIKE '. dbh->quote($1.'%');
  } else {
    delete $params->{geocode};
  }

  if ( $params->{taxclassnum} =~ /^(\d+)$/ &&
       qsearchs( 'tax_class', {'taxclassnum' => $1} )
     )
  {
    $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
                  ' taxclassnum  = '. dbh->quote($1)
  } else {
    delete $params->{taxclassnun};
  }

  my $tax_type = $1
    if ( $params->{tax_type} =~ /^(\d+)$/ );
  delete $params->{tax_type}
    unless $tax_type;

  my $tax_cat = $1
    if ( $params->{tax_cat} =~ /^(\d+)$/ );
  delete $params->{tax_cat}
    unless $tax_cat;

  my @taxclassnum = ();
  if ($tax_type || $tax_cat ) {
    my $compare = "LIKE '". ( $tax_type || "%" ). ":". ( $tax_cat || "%" ). "'";
    $compare = "= '$tax_type:$tax_cat'" if ($tax_type && $tax_cat);
    @taxclassnum = map { $_->taxclassnum } 
                   qsearch({ 'table'     => 'tax_class',
                             'hashref'   => {},
                             'extra_sql' => "WHERE taxclass $compare",
                          });
  }

  $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ). '( '.
                join(' OR ', map { " taxclassnum  = $_ " } @taxclassnum ). ' )'
    if ( @taxclassnum );

  unless ($params->{'showdisabled'}) {
    $extra_sql .= ( $extra_sql =~ /WHERE/i ? ' AND ' : ' WHERE ' ).
                  "( disabled = '' OR disabled IS NULL )";
  }

  $query->{extra_sql} = $extra_sql;

  return ($query, "SELECT COUNT(*) FROM tax_rate $extra_sql");
}

# _upgrade_data
#
# Used by FS::Upgrade to migrate to a new database.
#
#

sub _upgrade_data {  # class method
  my ($self, %opts) = @_;
  my $dbh = dbh;

  warn "$me upgrading $self\n" if $DEBUG;

  my @column = qw ( tax excessrate usetax useexcessrate fee excessfee
                    feebase feemax );

  if ( $dbh->{Driver}->{Name} eq 'Pg' ) {

    eval "use DBI::Const::GetInfoType;";
    die $@ if $@;

    my $major_version = 0;
    $dbh->get_info( $GetInfoType{SQL_DBMS_VER} ) =~ /^(\d{2})/
      && ( $major_version = sprintf("%d", $1) );

    if ( $major_version > 7 ) {

      # 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;
          my $sql = "ALTER TABLE tax_rate ALTER $column TYPE numeric(14,8)";
          my $sth = $dbh->prepare($sql) or die $dbh->errstr;
          $sth->execute or die $sth->errstr;

          warn "updating h_tax_rate column $column to numeric\n" if $DEBUG;
          $sql = "ALTER TABLE h_tax_rate ALTER $column TYPE numeric(14,8)";
          $sth = $dbh->prepare($sql) or die $dbh->errstr;
          $sth->execute or die $sth->errstr;

        }
      }

    } 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";

    }

  } else {

    warn "WARNING: tax_rate table upgrade only supported for Pg 8+\n";

  }

  '';

}

=back

=head1 BUGS

  Mixing automatic and manual editing works poorly at present.

=head1 SEE ALSO

L<FS::Record>, L<FS::cust_main>, L<FS::cust_bill>, schema.html from the base
documentation.

=cut

1;