improved taxproduct tax report RT#4783

13 files changed, 695 insertions, 46 deletions

diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm
index e70a12c..a9b5469 100644
--- a/FS/FS/Schema.pm
+++ b/FS/FS/Schema.pm
@@ -560,7 +560,7 @@ sub tables_hashref {
         'billpkgtaxlocationnum', 'serial',      '', '', '', '',
         'billpkgnum',               'int',      '', '', '', '',
         'taxnum',                   'int',      '', '', '', '',
-        'taxtype',              'varchar', $char_d, '', '', '',
+        'taxtype',              'varchar',      '', $char_d, '', '',
         'pkgnum',                   'int',      '', '', '', '',
         'locationnum',              'int',      '', '', '', '', #redundant?
         'amount',                   @money_type,        '', '',
@@ -570,6 +570,21 @@ sub tables_hashref {
       'index'  => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'pkgnum' ], [ 'locationnum' ] ],
     },
 
+    'cust_bill_pkg_tax_rate_location' => {
+      'columns' => [
+        'billpkgtaxratelocationnum', 'serial',      '', '', '', '',
+        'billpkgnum',                   'int',      '', '', '', '',
+        'taxnum',                       'int',      '', '', '', '',
+        'taxtype',                  'varchar',      '', $char_d, '', '',
+        'locationtaxid',            'varchar',  'NULL', $char_d, '', '',
+        'taxratelocationnum',           'int',      '', '', '', '',
+        'amount',                       @money_type,        '', '',
+      ],
+      'primary_key' => 'billpkgtaxratelocationnum',
+      'unique' => [],
+      'index'  => [ [ 'billpkgnum' ], [ 'taxnum' ], [ 'taxratelocationnum' ] ],
+    },
+
     'cust_credit' => {
       'columns' => [
         'crednum',  'serial', '', '', '', '', 
@@ -805,6 +820,21 @@ sub tables_hashref {
       'index' => [ ['taxclassnum'], ['data_vendor', 'geocode'] ],
     },
 
+    'tax_rate_location' => { 
+      'columns' => [
+        'taxratelocationnum', 'serial',  '',     '', '', '', 
+        'data_vendor',        'varchar', 'NULL', $char_d, '', '',
+        'geocode',            'varchar', '',     20,      '', '', 
+        'city',               'varchar', 'NULL', $char_d, '', '',
+        'county',             'varchar', 'NULL', $char_d, '', '',
+        'state',              'char',    '',     2,  '', '', 
+        'disabled',           'char',    'NULL', 1, '', '',
+      ],
+      'primary_key' => 'taxratelocationnum',
+      'unique' => [],
+      'index' => [ [ 'data_vendor', 'geocode', 'disabled' ] ],
+    },
+
     'cust_tax_location' => { 
       'columns' => [
         'custlocationnum', 'serial',  '',     '', '', '', 
diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm
index 6c0589a..6bd39d4 100644
--- a/FS/FS/cust_bill_pkg.pm
+++ b/FS/FS/cust_bill_pkg.pm
@@ -162,6 +162,19 @@ sub insert {
     }
   }
 
+  my $tax_rate_location = $self->get('cust_bill_pkg_tax_rate_location');
+  if ( $tax_rate_location ) {
+    foreach my $cust_bill_pkg_tax_rate_location ( @$tax_rate_location ) {
+      $cust_bill_pkg_tax_rate_location->billpkgnum($self->billpkgnum);
+      $error = $cust_bill_pkg_tax_rate_location->insert;
+      warn $error;
+      if ( $error ) {
+        $dbh->rollback if $oldAutoCommit;
+        return $error;
+      }
+    }
+  }
+
   $dbh->commit or die $dbh->errstr if $oldAutoCommit;
   '';
 
diff --git a/FS/FS/cust_bill_pkg_tax_rate_location.pm b/FS/FS/cust_bill_pkg_tax_rate_location.pm
new file mode 100644
index 0000000..fc5734f
--- /dev/null
+++ b/FS/FS/cust_bill_pkg_tax_rate_location.pm
@@ -0,0 +1,136 @@
+package FS::cust_bill_pkg_tax_rate_location;
+
+use strict;
+use base qw( FS::Record );
+use FS::Record qw( qsearch qsearchs );
+use FS::cust_bill_pkg;
+use FS::cust_pkg;
+use FS::cust_location;
+
+=head1 NAME
+
+FS::cust_bill_pkg_tax_rate_location - Object methods for cust_bill_pkg_tax_rate_location records
+
+=head1 SYNOPSIS
+
+  use FS::cust_bill_pkg_tax_rate_location;
+
+  $record = new FS::cust_bill_pkg_tax_rate_location \%hash;
+  $record = new FS::cust_bill_pkg_tax_rate_location { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::cust_bill_pkg_tax_rate_location object represents an record of taxation
+based on package location.  FS::cust_bill_pkg_tax_rate_location inherits from
+FS::Record.  The following fields are currently supported:
+
+=over 4
+
+=item billpkgtaxratelocationnum
+
+billpkgtaxratelocationnum
+
+=item billpkgnum
+
+billpkgnum
+
+=item taxnum
+
+taxnum
+
+=item taxtype
+
+taxtype
+
+=item locationtaxid
+
+locationtaxid
+
+=item taxratelocationnum
+
+taxratelocationnum
+
+=item amount
+
+amount
+
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new record.  To add the record to the database, see L<"insert">.
+
+Note that this stores the hash reference, not a distinct copy of the hash it
+points to.  You can ask the object for a copy with the I<hash> method.
+
+=cut
+
+sub table { 'cust_bill_pkg_tax_rate_location'; }
+
+=item insert
+
+Adds this record to the database.  If there is an error, returns the error,
+otherwise returns false.
+
+=item delete
+
+Delete this record from the database.
+
+=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 record.  If there is
+an error, returns the error, otherwise returns false.  Called by the insert
+and replace methods.
+
+=cut
+
+# the check method should currently be supplied - FS::Record contains some
+# data checking routines
+
+sub check {
+  my $self = shift;
+
+  my $error = 
+    $self->ut_numbern('billpkgtaxratelocationnum')
+    || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg', 'billpkgnum' )
+    || $self->ut_number('taxnum') #cust_bill_pkg/tax_rate key, based on taxtype
+    || $self->ut_enum('taxtype', [ qw( FS::cust_main_county FS::tax_rate ) ] )
+    || $self->ut_textn('locationtaxid')
+    || $self->ut_foreign_key('taxratelocationnum', 'tax_rate_location', 'taxratelocationnum' )
+    || $self->ut_money('amount')
+  ;
+  return $error if $error;
+
+  $self->SUPER::check;
+}
+
+=back
+
+=head1 BUGS
+
+=head1 SEE ALSO
+
+L<FS::Record>, schema.html from the base documentation.
+
+=cut
+
+1;
+
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index 6f05fd5..a1bb926 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -30,6 +30,7 @@ use FS::cust_bill;
 use FS::cust_bill_pkg;
 use FS::cust_bill_pkg_display;
 use FS::cust_bill_pkg_tax_location;
+use FS::cust_bill_pkg_tax_rate_location;
 use FS::cust_pay;
 use FS::cust_pay_pending;
 use FS::cust_pay_void;
@@ -40,6 +41,7 @@ use FS::part_referral;
 use FS::cust_main_county;
 use FS::cust_location;
 use FS::tax_rate;
+use FS::tax_rate_location;
 use FS::cust_tax_location;
 use FS::part_pkg_taxrate;
 use FS::agent;
@@ -2461,6 +2463,10 @@ sub bill {
   # values are listrefs of cust_bill_pkg_tax_location hashrefs
   my %tax_location = ();
 
+  # keys are taxlisthash keys (internal identifiers)
+  # values are listrefs of cust_bill_pkg_tax_rate_location hashrefs
+  my %tax_rate_location = ();
+
   foreach my $tax ( keys %taxlisthash ) {
     my $tax_object = shift @{ $taxlisthash{$tax} };
     warn "found ". $tax_object->taxname. " as $tax\n" if $DEBUG > 2;
@@ -2497,6 +2503,20 @@ sub bill {
         };
     }
 
+    $tax_rate_location{ $tax } ||= [];
+    if ( ref($tax_object) eq 'FS::tax_rate' ) {
+      my $taxratelocationnum =
+        $tax_object->tax_rate_location->taxratelocationnum;
+      push @{ $tax_rate_location{ $tax }  },
+        {
+          'taxnum'             => $tax_object->taxnum, 
+          'taxtype'            => ref($tax_object),
+          'amount'             => sprintf('%.2f', $amount ),
+          'locationtaxid'      => $tax_object->location,
+          'taxratelocationnum' => $taxratelocationnum,
+        };
+    }
+
   }
 
   #move the cust_tax_exempt_pkg records to the cust_bill_pkgs we will commit
@@ -2516,6 +2536,7 @@ sub bill {
     my $tax = 0;
     my %seen = ();
     my @cust_bill_pkg_tax_location = ();
+    my @cust_bill_pkg_tax_rate_location = ();
     warn "adding $taxname\n" if $DEBUG > 1;
     foreach my $taxitem ( @{ $taxname{$taxname} } ) {
       next if $seen{$taxitem}++;
@@ -2524,6 +2545,9 @@ sub bill {
       push @cust_bill_pkg_tax_location,
         map { new FS::cust_bill_pkg_tax_location $_ }
             @{ $tax_location{ $taxitem } };
+      push @cust_bill_pkg_tax_rate_location,
+        map { new FS::cust_bill_pkg_tax_rate_location $_ }
+            @{ $tax_rate_location{ $taxitem } };
     }
     next unless $tax;
 
@@ -2538,6 +2562,7 @@ sub bill {
       'edate'    => '',
       'itemdesc' => $taxname,
       'cust_bill_pkg_tax_location' => \@cust_bill_pkg_tax_location,
+      'cust_bill_pkg_tax_rate_location' => \@cust_bill_pkg_tax_rate_location,
     };
 
   }
diff --git a/FS/FS/tax_rate.pm b/FS/FS/tax_rate.pm
index dfa7d5f..80a0f4b 100644
--- a/FS/FS/tax_rate.pm
+++ b/FS/FS/tax_rate.pm
@@ -19,6 +19,7 @@ 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::cust_main;
 use FS::Misc qw( csv_from_fixed );
@@ -538,6 +539,26 @@ sub tax_on_tax {
 
 }
 
+=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
@@ -845,7 +866,8 @@ sub process_batch_import {
     my $error = '';
     my $have_location = 0;
 
-    my @list = ( 'CODE',     'codefile',  \&FS::tax_class::batch_import,
+    my @list = ( 'GEOCODE',  'geofile',   \&FS::tax_rate_location::batch_import,
+                 '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,
@@ -887,7 +909,8 @@ sub process_batch_import {
     my @insert_list = ();
     my @delete_list = ();
 
-    my @list = ( 'CODE',     'codefile',  \&FS::tax_class::batch_import,
+    my @list = ( 'GEOCODE',  'geofile',   \&FS::tax_rate_location::batch_import,
+                 '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,
diff --git a/FS/FS/tax_rate_location.pm b/FS/FS/tax_rate_location.pm
new file mode 100644
index 0000000..65bef7b
--- /dev/null
+++ b/FS/FS/tax_rate_location.pm
@@ -0,0 +1,309 @@
+package FS::tax_rate_location;
+
+use strict;
+use base qw( FS::Record );
+use FS::Record qw( qsearch qsearchs dbh );
+
+=head1 NAME
+
+FS::tax_rate_location - Object methods for tax_rate_location records
+
+=head1 SYNOPSIS
+
+  use FS::tax_rate_location;
+
+  $record = new FS::tax_rate_location \%hash;
+  $record = new FS::tax_rate_location { 'column' => 'value' };
+
+  $error = $record->insert;
+
+  $error = $new_record->replace($old_record);
+
+  $error = $record->delete;
+
+  $error = $record->check;
+
+=head1 DESCRIPTION
+
+An FS::tax_rate_location object represents an example.  FS::tax_rate_location inherits from
+FS::Record.  The following fields are currently supported:
+
+=over 4
+
+=item taxratelocationnum
+
+Primary key (assigned automatically for new tax_rate_locations)
+
+=item data_vendor
+
+The tax data vendor
+
+=item geocode
+
+A unique geographic location code provided by the data vendor
+
+=item city
+
+City
+
+=item county
+
+County
+
+=item state
+
+State
+
+=item disabled
+
+If 'Y' this record is no longer active.
+
+
+=back
+
+=head1 METHODS
+
+=over 4
+
+=item new HASHREF
+
+Creates a new tax rate location.  To add the record to the database, see
+ L<"insert">.
+
+Note that this stores the hash reference, not a distinct copy of the hash it
+points to.  You can ask the object for a copy with the I<hash> method.
+
+=cut
+
+sub table { 'tax_rate_location'; }
+
+=item insert
+
+Adds this record to the database.  If there is an error, returns the error,
+otherwise returns false.
+
+=cut
+
+=item delete
+
+Delete this record from the database.
+
+=cut
+
+sub delete {
+  return "Can't delete tax rate locations.  Set disable to 'Y' instead.";
+  # check that it is unused in any cust_bill_pkg_tax_location records instead?
+}
+
+=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.
+
+=cut
+
+=item check
+
+Checks all fields to make sure this is a valid tax rate location.  If there is
+an error, returns the error, otherwise returns false.  Called by the insert
+and replace methods.
+
+=cut
+
+sub check {
+  my $self = shift;
+
+  my $error = 
+    $self->ut_numbern('taxratelocationnum')
+    || $self->ut_textn('data_vendor')
+    || $self->ut_alpha('geocode')
+    || $self->ut_textn('city')
+    || $self->ut_textn('county')
+    || $self->ut_textn('state')
+    || $self->ut_enum('disabled', [ '', 'Y' ])
+  ;
+  return $error if $error;
+
+  my $t = qsearchs( 'tax_rate_location',
+                    { map { $_ => $self->$_ } qw( data_vendor geocode ) },
+                  );
+
+  return "geocode already in use for this vendor"
+    if ( $t && $t->taxratelocationnum != $self->taxratelocationnum );
+
+  return "may only be disabled"
+    if ( $t && scalar( grep { $t->$_ ne $self->$_ } 
+                       grep { $_ ne 'disabled' }
+                       $self->fields
+                     )
+       );
+
+  $self->SUPER::check;
+}
+
+=back
+
+=head1 SUBROUTINES
+
+=over 4
+
+=item batch_import
+
+=cut
+
+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 $trim = sub { my $r = shift; $r =~ s/^\s*//; $r =~ s/\s*$//; $r };
+    push @column_lengths, qw( 28 25 2 10 );
+    push @column_lengths, 1 if $format eq 'cch-update';
+    push @column_callbacks, $trim foreach (@column_lengths);
+  }
+
+  my $line;
+  my ( $count, $last, $min_sec ) = (0, time, 5); #progressbar
+  if ( $job || scalar(@column_callbacks) ) {
+    my $error =
+      csv_from_fixed(\$fh, \$count, \@column_lengths, \@column_callbacks);
+    return $error if $error;
+  }
+
+  if ( $format eq 'cch' || $format eq 'cch-update' ) {
+    @fields = qw( city county state geocode );
+    push @fields, 'actionflag' if $format eq 'cch-update';
+
+    $hook = sub {
+      my $hash = shift;
+
+      $hash->{'data_vendor'} ='cch';
+
+      if (exists($hash->{'actionflag'}) && $hash->{'actionflag'} eq 'D') {
+        delete($hash->{actionflag});
+
+        $hash->{deleted} = '';
+        my $tax_rate_location = qsearchs('tax_rate_location', $hash);
+        return "Can't find tax_rate_location to delete: ".
+               join(" ", map { "$_ => ". $hash->{$_} } @fields)
+          unless $tax_rate_location;
+
+        $tax_rate_location->disabled('Y');
+        my $error = $tax_rate_location->replace;
+        return $error if $error;
+
+        delete($hash->{$_}) foreach (keys %$hash);
+      }
+
+      delete($hash->{'actionflag'});
+
+      '';
+
+    };
+
+  } 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 )
+        );
+        die $error if $error;
+        $last = time;
+      }
+    }
+
+    my @columns = $csv->fields();
+
+    my %tax_rate_location = ();
+    foreach my $field ( @fields ) {
+      $tax_rate_location{$field} = shift @columns;
+    }
+    if ( scalar( @columns ) ) {
+      $dbh->rollback if $oldAutoCommit;
+      return "Unexpected trailing columns in line (wrong format?): $line";
+    }
+
+    my $error = &{$hook}(\%tax_rate_location);
+    if ( $error ) {
+      $dbh->rollback if $oldAutoCommit;
+      return $error;
+    }
+
+    if (scalar(keys %tax_rate_location)) { #inserts only
+
+      my $tax_rate_location = new FS::tax_rate_location( \%tax_rate_location );
+      $error = $tax_rate_location->insert;
+
+      if ( $error ) {
+        $dbh->rollback if $oldAutoCommit;
+        return "can't insert 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
+
+}
+
+=head1 BUGS
+
+Currently somewhat specific to CCH supplied data.
+
+=head1 SEE ALSO
+
+L<FS::Record>, schema.html from the base documentation.
+
+=cut
+
+1;
+
diff --git a/FS/MANIFEST b/FS/MANIFEST
index 4b9fd91..b5c9046 100644
--- a/FS/MANIFEST
+++ b/FS/MANIFEST
@@ -434,3 +434,7 @@ FS/cust_location.pm
 t/cust_location.t
 FS/cust_bill_pkg_tax_location.pm
 t/cust_bill_pkg_tax_location.t
+FS/tax_rate_location.pm
+t/tax_rate_location.t
+FS/cust_bill_pkg_tax_rate_location.pm
+t/cust_bill_pkg_tax_rate_location.t
diff --git a/FS/t/cust_bill_pkg_tax_rate_location.t b/FS/t/cust_bill_pkg_tax_rate_location.t
new file mode 100644
index 0000000..3250db9
--- /dev/null
+++ b/FS/t/cust_bill_pkg_tax_rate_location.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::cust_bill_pkg_tax_rate_location;
+$loaded=1;
+print "ok 1\n";
diff --git a/FS/t/tax_rate_location.t b/FS/t/tax_rate_location.t
new file mode 100644
index 0000000..f4ee910
--- /dev/null
+++ b/FS/t/tax_rate_location.t
@@ -0,0 +1,5 @@
+BEGIN { $| = 1; print "1..1\n" }
+END {print "not ok 1\n" unless $loaded;}
+use FS::tax_rate_location;
+$loaded=1;
+print "ok 1\n";
diff --git a/bin/tax_rate_location.import b/bin/tax_rate_location.import
new file mode 100755
index 0000000..439d27c
--- /dev/null
+++ b/bin/tax_rate_location.import
@@ -0,0 +1,48 @@
+#!/usr/bin/perl -Tw
+
+use strict;
+use vars qw($opt_g $opt_f);
+use vars qw($DEBUG);
+use Getopt::Std;
+use FS::UID qw(adminsuidsetup);
+use FS::Conf;
+use FS::tax_rate_location;
+
+getopts('f:g:');
+
+my $user = shift or die &usage;
+my $dbh = adminsuidsetup $user;
+
+my ($format) = $opt_f =~ /^([-\w]+)$/;
+
+my @list = (
+  'GEOCODE',      $opt_g, \&FS::tax_rate_location::batch_import,
+);
+
+my $oldAutoCommit = $FS::UID::AutoCommit;
+local $FS::UID::AutoCommit = 0;
+
+my $error = '';
+
+while(@list) {
+  my ($name, $file, $method) = splice(@list, 0, 3);
+
+  my $fh;
+
+  $file =~ /^([\s\d\w.]+)$/ or die "Illegal filename: $file\n";
+  $file = $1;
+
+  open $fh, '<', $file or die "can't open $name file: $!\n";
+  $error ||= &{$method}( { filehandle => $fh, 'format' => $format, } );
+
+  die "error while processing $file: $error" if $error;
+  close $fh;
+}
+
+if ($error) {
+  $dbh->rollback or die $dbh->errstr if $oldAutoCommit;
+}else{
+  $dbh->commit or die $dbh->errstr if $oldAutoCommit;
+}
+
+sub usage { die "Usage:\ntax_rates_location.import -f FORMAT -g GEOCODEFILE user\n\n"; }
diff --git a/httemplate/misc/tax-import.cgi b/httemplate/misc/tax-import.cgi
index a695e97..5116e54 100644
--- a/httemplate/misc/tax-import.cgi
+++ b/httemplate/misc/tax-import.cgi
@@ -6,7 +6,7 @@ Import a CSV file set containing tax rate records.
 <% include( '/elements/form-file_upload.html',
               'name'      => 'TaxRateUpload',
               'action'    => 'process/tax-import.cgi', 
-              'num_files' => 5,
+              'num_files' => 6,
               'fields'    => [ 'format', ],
               'message'   => 'Tax rates imported',
           )
@@ -27,13 +27,15 @@ Import a CSV file set containing tax rate records.
   </TR>
 
   <% include( '/elements/file-upload.html',
-                'field'    => [ 'codefile',
+                'field'    => [ 'geofile',
+                                'codefile',
                                 'plus4file',
                                 'zipfile',
                                 'txmatrix',
                                 'detail',
                               ],
-                'label'    => [ 'code filename',
+                'label'    => [ 'geocode filename',
+                                'code filename',
                                 'plus4 filename',
                                 'zip filename',
                                 'txmatrix filename',
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index 89901ac..9d48432 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -189,6 +189,26 @@ if ( $cgi->param('out') ) {
 
   }
 
+ } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
+
+  # this should really be shoved out to FS::cust_pkg->location_sql or something
+  # along with the code in report_newtax.cgi
+
+  my %pn = (
+   'county'        => 'tax_rate_location.county',
+   'state'         => 'tax_rate_location.state',
+   'city'          => 'tax_rate_location.city',
+   'locationtaxid' => 'cust_bill_pkg_tax_rate_location.locationtaxid',
+  );
+
+  my %ph = map { ( $pn{$_} => dbh->quote( $cgi->param($_) || '' ) ) }
+           qw( county state city locationtaxid );
+
+  push @where,
+    join( ' AND ', map { "( $_ = $ph{$_} OR $ph{$_} = '' AND $_ IS NULL)" }
+                   keys %ph
+    );
+
 }
 
 if ($cgi->param('itemdesc')) {
@@ -295,6 +315,10 @@ if ( $cgi->param('nottax') ) {
 
     #quelle kludge, false laziness w/report_tax.cgi
     $where =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g; 
+  } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
+    $join_pkg .=
+      ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
+      ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) ';
   }
 
 } else { 
diff --git a/httemplate/search/report_newtax.cgi b/httemplate/search/report_newtax.cgi
index 586fddd..0fb5483 100755
--- a/httemplate/search/report_newtax.cgi
+++ b/httemplate/search/report_newtax.cgi
@@ -15,6 +15,7 @@
 
   <TR>
     <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+    <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
     <TH CLASS="grid" BGCOLOR="#cccccc">Tax collected</TH>
   </TR>
 % my $bgcolor1 = '#eeeeee';
@@ -37,9 +38,11 @@
 
     <TR>
       <TD CLASS="grid" BGCOLOR="<% $bgcolor %>"><% $tax->{'label'} %></TD>
+      <% $tax->{base} ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
       <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ALIGN="right">
         <A HREF="<% $baselink. $link %>;istax=1"><% $money_char %><% sprintf('%.2f', $tax->{'tax'} ) %></A>
       </TD>
+      <% !($tax->{base}) ? qq!<TD CLASS="grid" BGCOLOR="$bgcolor"></TD>! : '' %>
     </TR>
 % } 
 
@@ -61,10 +64,11 @@ my $join_cust = "
     JOIN cust_bill USING ( invnum ) 
     LEFT JOIN cust_main USING ( custnum )
 ";
-my $from_join_cust = "
-    FROM cust_bill_pkg
-    $join_cust
-"; 
+
+my $join_loc = "LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum )";
+my $join_tax_loc = "LEFT JOIN tax_rate_location USING ( taxratelocationnum )";
+
+my $addl_from = " $join_cust $join_loc $join_tax_loc "; 
 
 my $where = "WHERE _date >= $beginning AND _date <= $ending ";
 
@@ -76,65 +80,87 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
 }
 
+# my ( $location_sql, @location_param ) = FS::cust_pkg->location_sql;
+# $where .= " AND $location_sql";
+#my @taxparam = ( 'itemdesc', @location_param );
+# now something along the lines of geocode matching ?
+#$where .= FS::cust_pkg->_location_sql_where('cust_tax_location');;
+my @taxparam = ( 'itemdesc', 'tax_rate_location.state', 'tax_rate_location.county', 'tax_rate_location.city', 'cust_bill_pkg_tax_rate_location.locationtaxid' );
+
+my $select = 'DISTINCT itemdesc,locationtaxid,tax_rate_location.state,tax_rate_location.county,tax_rate_location.city';
+
 my $tax = 0;
 my %taxes = ();
+my %basetaxes = ();
 foreach my $t (qsearch({ table     => 'cust_bill_pkg',
+                         select    => $select,
                          hashref   => { pkgpart => 0 },
-                         addl_from => $join_cust,
+                         addl_from => $addl_from,
                          extra_sql => $where,
                       })
               )
 {
-  #warn $t->itemdesc. "\n";
+  my @params = map { my $f = $_; $f =~ s/.*\.//; $f } @taxparam;
+  my $label = join('~', map { $t->$_ } @params);
+  $label = 'Tax'. $label if $label =~ /^~/;
+  unless ( exists( $taxes{$label} ) ) {
+    my ($baselabel, @trash) = split /~/, $label;
 
-  my $label = $t->itemdesc;
-  $label ||= 'Tax';
-  $taxes{$label}->{'label'} = $label;
-  $taxes{$label}->{'url_param'} = "itemdesc=$label";
+    $taxes{$label}->{'label'} = join(', ', split(/~/, $label) );
+    $taxes{$label}->{'url_param'} =
+      join(';', map { "$_=". uri_escape($t->$_) } @params);
 
-  # calculate total for this tax 
-  # calculate customer-exemption for this tax
-  # calculate package-exemption for this tax
-  # calculate monthly exemption (texas tax) for this tax
-  # count up all the cust_tax_exempt_pkg records associated with
-  # the actual line items.
-}
+    my $taxwhere = "FROM cust_bill_pkg $addl_from $where AND payby != 'COMP' ".
+      "AND ". join( ' AND ', map { "( $_ = ? OR ? = '' AND $_ IS NULL)" } @taxparam );
 
+    my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ".
+              " $taxwhere AND cust_bill_pkg.pkgnum = 0";
 
-foreach my $t (qsearch({ table     => 'cust_bill_pkg',
-                         select    => 'DISTINCT itemdesc',
-                         hashref   => { pkgpart => 0 },
-                         addl_from => $join_cust,
-                         extra_sql => $where,
-                      })
-              )
-{
+    my $x = scalar_sql($t, [ map { $_, $_ } @params ], $sql );
+    $tax += $x;
+    $taxes{$label}->{'tax'} += $x;
+
+    unless ( exists( $taxes{$baselabel} ) ) {
 
-  my $label = $t->itemdesc;
-  $label ||= 'Tax';
-  my @taxparam = ( 'itemdesc' );
-  my $taxwhere = "$from_join_cust $where AND payby != 'COMP' ".
-    "AND itemdesc = ?" ;
+      $basetaxes{$baselabel}->{'label'} = $baselabel;
+      $basetaxes{$baselabel}->{'url_param'} = "itemdesc=$baselabel";
+      $basetaxes{$baselabel}->{'base'} = 1;
 
-  my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ".
-            " $taxwhere AND pkgnum = 0";
+    }
 
-  my $x = scalar_sql($t, \@taxparam, $sql );
-  $tax += $x;
-  $taxes{$label}->{'tax'} += $x;
+    $basetaxes{$baselabel}->{'tax'} += $x;
+      
+  }
 
+  # calculate customer-exemption for this tax
+  # calculate package-exemption for this tax
+  # calculate monthly exemption (texas tax) for this tax
+  # count up all the cust_tax_exempt_pkg records associated with
+  # the actual line items.
 }
 
+
 #ordering
-my @taxes =
-  map $taxes{$_},
-  sort { ($b cmp $a) }
-  keys %taxes;
+my @taxes = ();
+
+foreach my $tax ( sort { $a cmp $b } keys %taxes ) {
+  my ($base, @trash) = split '~', $tax;
+  my $basetax = delete( $basetaxes{$base} );
+  if ($basetax) {
+    if ( $basetax->{tax} == $taxes{$tax}->{tax} ) {
+      $taxes{$tax}->{base} = 1;
+    } else {
+      push @taxes, $basetax;
+    }
+  }
+  push @taxes, $taxes{$tax};
+}
 
 push @taxes, {
   'label'          => 'Total',
   'url_param'      => '',
   'tax'            => $tax,
+  'base'           => 1,
 };
 
 #-- 
@@ -143,7 +169,6 @@ push @taxes, {
 #to FS::Report or FS::Record or who the fuck knows where)
 sub scalar_sql {
   my( $r, $param, $sql ) = @_;
-  #warn "$sql\n";
   my $sth = dbh->prepare($sql) or die dbh->errstr;
   $sth->execute( map $r->$_(), @$param )
     or die "Unexpected error executing statement $sql: ". $sth->errstr;