tax calculation and reporting for new customer locations, #940

1 files changed, 470 insertions, 16 deletions

diff --git a/FS/FS/cust_bill_pkg.pm b/FS/FS/cust_bill_pkg.pm
index 96fa408a..b8ae81d 100644
--- a/FS/FS/cust_bill_pkg.pm
+++ b/FS/FS/cust_bill_pkg.pm
@@ -4,7 +4,7 @@ use base qw( FS::TemplateItem_Mixin FS::cust_main_Mixin FS::Record );
 use strict;
 use vars qw( @ISA $DEBUG $me );
 use Carp;
-use List::Util qw( sum );
+use List::Util qw( sum min );
 use Text::CSV_XS;
 use FS::Record qw( qsearch qsearchs dbh );
 use FS::cust_pkg;
@@ -26,7 +26,6 @@ use FS::cust_bill_pkg_tax_location_void;
 use FS::cust_bill_pkg_tax_rate_location_void;
 use FS::cust_tax_exempt_pkg_void;
 
-
 $DEBUG = 0;
 $me = '[FS::cust_bill_pkg]';
 
@@ -191,14 +190,12 @@ sub insert {
     }
   }
 
-  if ( $self->_cust_tax_exempt_pkg ) {
-    foreach my $cust_tax_exempt_pkg ( @{$self->_cust_tax_exempt_pkg} ) {
-      $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum);
-      $error = $cust_tax_exempt_pkg->insert;
-      if ( $error ) {
-        $dbh->rollback if $oldAutoCommit;
-        return "error inserting cust_tax_exempt_pkg: $error";
-      }
+  foreach my $cust_tax_exempt_pkg ( @{$self->cust_tax_exempt_pkg} ) {
+    $cust_tax_exempt_pkg->billpkgnum($self->billpkgnum);
+    $error = $cust_tax_exempt_pkg->insert;
+    if ( $error ) {
+      $dbh->rollback if $oldAutoCommit;
+      return "error inserting cust_tax_exempt_pkg: $error";
     }
   }
 
@@ -787,14 +784,10 @@ sub usage_classes {
 
 }
 
-# reserving this name for my friends FS::{tax_rate|cust_main_county}::taxline
-# and FS::cust_main::bill
-sub _cust_tax_exempt_pkg {
+sub cust_tax_exempt_pkg {
   my ( $self ) = @_;
 
-  $self->{Hash}->{_cust_tax_exempt_pkg} or
-  $self->{Hash}->{_cust_tax_exempt_pkg} = [];
-
+  $self->{Hash}->{cust_tax_exempt_pkg} ||= [];
 }
 
 =item cust_bill_pkg_tax_Xlocation
@@ -941,6 +934,465 @@ sub credited_sql {
 
 }
 
+sub upgrade_tax_location {
+  # For taxes that were calculated/invoiced before cust_location refactoring
+  # (May-June 2012), there are no cust_bill_pkg_tax_location records unless
+  # they were calculated on a package-location basis.  Create them here, 
+  # along with any necessary cust_location records and any tax exemption 
+  # records.
+  #
+  # This probably shouldn't run from freeside-upgrade.
+
+  my ($class, %opt) = @_;
+  # %opt may include 's' and 'e': start and end date ranges
+  # and 'X': abort on any error, instead of just rolling back changes to 
+  # that invoice
+  my $dbh = dbh;
+  $FS::UID::AutoCommit = 0;
+
+  eval {
+    use FS::h_cust_main;
+    use FS::h_cust_bill;
+    use FS::h_part_pkg;
+    use FS::h_cust_main_exemption;
+  };
+
+  local $FS::cust_location::import = 1;
+
+  my $conf = FS::Conf->new; # h_conf?
+  return if $conf->exists('enable_taxproducts'); #don't touch this case
+  my $use_ship = $conf->exists('tax-ship_address');
+
+  my $date_where = '';
+  if ($opt{s}) {
+    $date_where .= " AND cust_bill._date >= $opt{s}";
+  }
+  if ($opt{e}) {
+    $date_where .= " AND cust_bill._date < $opt{e}";
+  }
+
+  my $commit_each_invoice = 1 unless $opt{X};
+
+  # if an invoice has either of these kinds of objects, then it doesn't
+  # need to be upgraded...probably
+  my $sub_has_tax_link = 'SELECT 1 FROM cust_bill_pkg_tax_location'.
+  ' JOIN cust_bill_pkg USING (billpkgnum)'.
+  ' WHERE cust_bill_pkg.invnum = cust_bill.invnum';
+  my $sub_has_exempt = 'SELECT 1 FROM cust_tax_exempt_pkg'.
+  ' JOIN cust_bill_pkg USING (billpkgnum)'.
+  ' WHERE cust_bill_pkg.invnum = cust_bill.invnum'.
+  ' AND exempt_monthly IS NULL';
+
+  my @invnums = map { $_->invnum } qsearch({
+      select => 'cust_bill.invnum',
+      table => 'cust_bill',
+      hashref => {},
+      extra_sql => "WHERE NOT EXISTS($sub_has_tax_link) ".
+                   "AND NOT EXISTS($sub_has_exempt) ".
+                    $date_where,
+  });
+
+  print "Processing ".scalar(@invnums)." invoices...\n";
+
+  my $committed;
+  INVOICE:
+  foreach my $invnum (@invnums) {
+    $committed = 0;
+    print STDERR "Invoice #$invnum\n";
+    my $pre = '';
+    my %pkgpart_taxclass; # pkgpart => taxclass
+    my %pkgpart_exempt_setup;
+    my %pkgpart_exempt_recur;
+    my $h_cust_bill = qsearchs('h_cust_bill',
+      { invnum => $invnum,
+        history_action => 'insert' });
+    if (!$h_cust_bill) {
+      warn "no insert record for invoice $invnum; skipped\n";
+      #$date = $cust_bill->_date as a fallback?
+      # We're trying to avoid using non-real dates (-d/-y invoice dates)
+      # when looking up history records in other tables.
+      next INVOICE;
+    }
+    my $custnum = $h_cust_bill->custnum;
+
+    # Determine the address corresponding to this tax region.
+    # It's either the bill or ship address of the customer as of the
+    # invoice date-of-insertion.  (Not necessarily the invoice date.)
+    my $date = $h_cust_bill->history_date;
+    my $h_cust_main = qsearchs('h_cust_main',
+        { custnum => $custnum },
+        FS::h_cust_main->sql_h_searchs($date)
+      );
+    if (!$h_cust_main ) {
+      warn "no historical address for cust#".$h_cust_bill->custnum."; skipped\n";
+      next INVOICE;
+      # fallback to current $cust_main?  sounds dangerous.
+    }
+
+    # This is a historical customer record, so it has a historical address.
+    # If there's no cust_location matching this custnum and address (there 
+    # probably isn't), create one.
+    $pre = 'ship_' if $use_ship and length($h_cust_main->get('ship_last'));
+    my %hash = map { $_ => $h_cust_main->get($pre.$_) }
+                  FS::cust_main->location_fields;
+    # not really needed for this, and often result in duplicate locations
+    delete @hash{qw(censustract censusyear latitude longitude coord_auto)};
+
+    $hash{custnum} = $h_cust_main->custnum;
+    my $tax_loc = qsearchs('cust_location', \%hash) # unlikely
+                  || FS::cust_location->new({ %hash });
+    if ( !$tax_loc->locationnum ) {
+      $tax_loc->disabled('Y');
+      my $error = $tax_loc->insert;
+      if ( $error ) {
+        warn "couldn't create historical location record for cust#".
+        $h_cust_main->custnum.": $error\n";
+        next INVOICE;
+      }
+    }
+    my $exempt_cust = 1 if $h_cust_main->tax;
+
+    # Get any per-customer taxname exemptions that were in effect.
+    my %exempt_cust_taxname = map {
+      $_->taxname => 1
+    } qsearch('h_cust_main_exemption', { 'custnum' => $custnum },
+      FS::h_cust_main_exemption->sql_h_searchs($date)
+    );
+
+    # classify line items
+    my @tax_items;
+    my %nontax_items; # taxclass => array of cust_bill_pkg
+    foreach my $item ($h_cust_bill->cust_bill_pkg) {
+      my $pkgnum = $item->pkgnum;
+
+      if ( $pkgnum == 0 ) {
+
+        push @tax_items, $item;
+
+      } else {
+        # (pkgparts really shouldn't change, right?)
+        my $h_cust_pkg = qsearchs('h_cust_pkg', { pkgnum => $pkgnum },
+          FS::h_cust_pkg->sql_h_searchs($date)
+        );
+        if ( !$h_cust_pkg ) {
+          warn "no historical package #".$item->pkgpart."; skipped\n";
+          next INVOICE;
+        }
+        my $pkgpart = $h_cust_pkg->pkgpart;
+
+        if (!exists $pkgpart_taxclass{$pkgpart}) {
+          my $h_part_pkg = qsearchs('h_part_pkg', { pkgpart => $pkgpart },
+            FS::h_part_pkg->sql_h_searchs($date)
+          );
+          if ( !$h_part_pkg ) {
+            warn "no historical package def #$pkgpart; skipped\n";
+            next INVOICE;
+          }
+          $pkgpart_taxclass{$pkgpart} = $h_part_pkg->taxclass || '';
+          $pkgpart_exempt_setup{$pkgpart} = 1 if $h_part_pkg->setuptax;
+          $pkgpart_exempt_recur{$pkgpart} = 1 if $h_part_pkg->recurtax;
+        }
+        
+        # mark any exemptions that apply
+        if ( $pkgpart_exempt_setup{$pkgpart} ) {
+          $item->set('exempt_setup' => 1);
+        }
+
+        if ( $pkgpart_exempt_recur{$pkgpart} ) {
+          $item->set('exempt_recur' => 1);
+        }
+
+        my $taxclass = $pkgpart_taxclass{ $pkgpart };
+
+        $nontax_items{$taxclass} ||= [];
+        push @{ $nontax_items{$taxclass} }, $item;
+      }
+    }
+    printf("%d tax items: \$%.2f\n", scalar(@tax_items), map {$_->setup} @tax_items);
+
+    # Use a variation on the procedure in 
+    # FS::cust_main::Billing::_handle_taxes to identify taxes that apply 
+    # to this bill.
+    my @loc_keys = qw( district city county state country );
+    my %taxhash = map { $_ => $h_cust_main->get($pre.$_) } @loc_keys;
+    my %taxdef_by_name; # by name, and then by taxclass
+    my %est_tax; # by name, and then by taxclass
+    my %taxable_items; # by taxnum, and then an array
+
+    foreach my $taxclass (keys %nontax_items) {
+      my %myhash = %taxhash;
+      my @elim = qw( district city county state );
+      my @taxdefs; # because there may be several with different taxnames
+      do {
+        $myhash{taxclass} = $taxclass;
+        @taxdefs = qsearch('cust_main_county', \%myhash);
+        if ( !@taxdefs ) {
+          $myhash{taxclass} = '';
+          @taxdefs = qsearch('cust_main_county', \%myhash);
+        }
+        $myhash{ shift @elim } = '';
+      } while scalar(@elim) and !@taxdefs;
+
+      print "Class '$taxclass': ". scalar(@{ $nontax_items{$taxclass} }).
+            " items, ". scalar(@taxdefs)." tax defs found.\n";
+      foreach my $taxdef (@taxdefs) {
+        next if $taxdef->tax == 0;
+        $taxdef_by_name{$taxdef->taxname}{$taxdef->taxclass} = $taxdef;
+
+        $taxable_items{$taxdef->taxnum} ||= [];
+        foreach my $orig_item (@{ $nontax_items{$taxclass} }) {
+          # clone the item so that taxdef-dependent changes don't
+          # change it for other taxdefs
+          my $item = FS::cust_bill_pkg->new({ $orig_item->hash });
+
+          # these flags are already set if the part_pkg declares itself exempt
+          $item->set('exempt_setup' => 1) if $taxdef->setuptax;
+          $item->set('exempt_recur' => 1) if $taxdef->recurtax;
+
+          my @new_exempt;
+          my $taxable = $item->setup + $item->recur;
+          # credits
+          # h_cust_credit_bill_pkg?
+          # NO.  Because if these exemptions HAD been created at the time of 
+          # billing, and then a credit applied later, the exemption would 
+          # have been adjusted by the amount of the credit.  So we adjust
+          # the taxable amount before creating the exemption.
+          # But don't deduct the credit from taxable, because the tax was 
+          # calculated before the credit was applied.
+          foreach my $f (qw(setup recur)) {
+            my $credited = FS::Record->scalar_sql(
+              "SELECT SUM(amount) FROM cust_credit_bill_pkg ".
+              "WHERE billpkgnum = ? AND setuprecur = ?",
+              $item->billpkgnum,
+              $f
+            );
+            $item->set($f, $item->get($f) - $credited) if $credited;
+          }
+          my $existing_exempt = FS::Record->scalar_sql(
+            "SELECT SUM(amount) FROM cust_tax_exempt_pkg WHERE ".
+            "billpkgnum = ? AND taxnum = ?",
+            $item->billpkgnum, $taxdef->taxnum
+          ) || 0;
+          $taxable -= $existing_exempt;
+
+          if ( $taxable and $exempt_cust ) {
+            push @new_exempt, { exempt_cust => 'Y',  amount => $taxable };
+            $taxable = 0;
+          }
+          if ( $taxable and $exempt_cust_taxname{$taxdef->taxname} ){
+            push @new_exempt, { exempt_cust_taxname => 'Y', amount => $taxable };
+            $taxable = 0;
+          }
+          if ( $taxable and $item->exempt_setup ) {
+            push @new_exempt, { exempt_setup => 'Y', amount => $item->setup };
+            $taxable -= $item->setup;
+          }
+          if ( $taxable and $item->exempt_recur ) {
+            push @new_exempt, { exempt_recur => 'Y', amount => $item->recur };
+            $taxable -= $item->recur;
+          }
+
+          $item->set('taxable' => $taxable);
+          push @{ $taxable_items{$taxdef->taxnum} }, $item
+            if $taxable > 0;
+
+          # estimate the amount of tax (this is necessary because different
+          # taxdefs with the same taxname may have different tax rates) 
+          # and sum that for each taxname/taxclass combination
+          # (in cents)
+          $est_tax{$taxdef->taxname} ||= {};
+          $est_tax{$taxdef->taxname}{$taxdef->taxclass} ||= 0;
+          $est_tax{$taxdef->taxname}{$taxdef->taxclass} += 
+            $taxable * $taxdef->tax;
+
+          foreach (@new_exempt) {
+            next if $_->{amount} == 0;
+            my $cust_tax_exempt_pkg = FS::cust_tax_exempt_pkg->new({
+                %$_,
+                billpkgnum  => $item->billpkgnum,
+                taxnum      => $taxdef->taxnum,
+              });
+            my $error = $cust_tax_exempt_pkg->insert;
+            if ($error) {
+              my $pkgnum = $item->pkgnum;
+              warn "error creating tax exemption for inv$invnum pkg$pkgnum:".
+                "\n$error\n\n";
+              next INVOICE;
+            }
+          } #foreach @new_exempt
+        } #foreach $item
+      } #foreach $taxdef
+    } #foreach $taxclass
+
+    # Now go through the billed taxes and match them up with the line items.
+    TAX_ITEM: foreach my $tax_item ( @tax_items )
+    {
+      my $taxname = $tax_item->itemdesc;
+      $taxname = '' if $taxname eq 'Tax';
+
+      if ( !exists( $taxdef_by_name{$taxname} ) ) {
+        # then we didn't find any applicable taxes with this name
+        warn "no definition found for tax item '$taxname'.\n".
+          '('.join(' ', @hash{qw(country state county city district)}).")\n";
+        # possibly all of these should be "next TAX_ITEM", but whole invoices
+        # are transaction protected and we can go back and retry them.
+        next INVOICE;
+      }
+      # classname => cust_main_county
+      my %taxdef_by_class = %{ $taxdef_by_name{$taxname} };
+
+      # Divide the tax item among taxclasses, if necessary
+      # classname => estimated tax amount
+      my $this_est_tax = $est_tax{$taxname};
+      if (!defined $this_est_tax) {
+        warn "no taxable sales found for inv#$invnum, tax item '$taxname'.\n";
+        next INVOICE;
+      }
+      my $est_total = sum(values %$this_est_tax);
+      if ( $est_total == 0 ) {
+        # shouldn't happen
+        warn "estimated tax on invoice #$invnum is zero.\n";
+        next INVOICE;
+      }
+
+      my $real_tax = $tax_item->setup;
+      printf ("Distributing \$%.2f tax:\n", $real_tax);
+      my $cents_remaining = $real_tax * 100; # for rounding error
+      my @tax_links; # partial CBPTL hashrefs
+      foreach my $taxclass (keys %taxdef_by_class) {
+        my $taxdef = $taxdef_by_class{$taxclass};
+        # these items already have "taxable" set to their charge amount
+        # after applying any credits or exemptions
+        my @items = @{ $taxable_items{$taxdef->taxnum} };
+        my $subtotal = sum(map {$_->get('taxable')} @items);
+        printf("\t$taxclass: %.2f\n", $this_est_tax->{$taxclass}/$est_total);
+
+        foreach my $nontax (@items) {
+          my $part = int($real_tax
+                            # class allocation
+                         * ($this_est_tax->{$taxclass}/$est_total) 
+                            # item allocation
+                         * ($nontax->get('taxable'))/$subtotal
+                            # convert to cents
+                         * 100
+                       );
+          $cents_remaining -= $part;
+          push @tax_links, {
+            taxnum => $taxdef->taxnum,
+            pkgnum => $nontax->pkgnum,
+            cents  => $part,
+          };
+        } #foreach $nontax
+      } #foreach $taxclass
+      # Distribute any leftover tax round-robin style, one cent at a time.
+      my $i = 0;
+      my $nlinks = scalar(@tax_links);
+      if ( $nlinks ) {
+        while (int($cents_remaining) > 0) {
+          $tax_links[$i % $nlinks]->{cents} += 1;
+          $cents_remaining--;
+          $i++;
+        }
+      } else {
+        warn "Can't create tax links--no taxable items found.\n";
+        next INVOICE;
+      }
+
+      # Gather credit/payment applications so that we can link them
+      # appropriately.
+      my @unlinked = (
+        qsearch( 'cust_credit_bill_pkg',
+          { billpkgnum => $tax_item->billpkgnum, billpkgtaxlocationnum => '' }
+        ),
+        qsearch( 'cust_bill_pay_pkg',
+          { billpkgnum => $tax_item->billpkgnum, billpkgtaxlocationnum => '' }
+        )
+      );
+
+      # grab the first one
+      my $this_unlinked = shift @unlinked;
+      my $unlinked_cents = int($this_unlinked->amount * 100) if $this_unlinked;
+
+      # Create tax links (yay!)
+      printf("Creating %d tax links.\n",scalar(@tax_links));
+      foreach (@tax_links) {
+        my $link = FS::cust_bill_pkg_tax_location->new({
+            billpkgnum  => $tax_item->billpkgnum,
+            taxtype     => 'FS::cust_main_county',
+            locationnum => $tax_loc->locationnum,
+            taxnum      => $_->{taxnum},
+            pkgnum      => $_->{pkgnum},
+            amount      => sprintf('%.2f', $_->{cents} / 100),
+        });
+        my $error = $link->insert;
+        if ( $error ) {
+          warn "Can't create tax link for inv#$invnum: $error\n";
+          next INVOICE;
+        }
+
+        my $link_cents = $_->{cents};
+        # update/create subitem links
+        #
+        # If $this_unlinked is undef, then we've allocated all of the
+        # credit/payment applications to the tax item.  If $link_cents is 0,
+        # then we've applied credits/payments to all of this package fraction,
+        # so go on to the next.
+        while ($this_unlinked and $link_cents) {
+          # apply as much as possible of $link_amount to this credit/payment
+          # link
+          my $apply_cents = min($link_cents, $unlinked_cents);
+          $link_cents -= $apply_cents;
+          $unlinked_cents -= $apply_cents;
+          # $link_cents or $unlinked_cents or both are now zero
+          $this_unlinked->set('amount' => sprintf('%.2f',$apply_cents/100));
+          $this_unlinked->set('billpkgtaxlocationnum' => $link->billpkgtaxlocationnum);
+          my $pkey = $this_unlinked->primary_key; #creditbillpkgnum or billpaypkgnum
+          if ( $this_unlinked->$pkey ) {
+            # then it's an existing link--replace it
+            $error = $this_unlinked->replace;
+          } else {
+            $this_unlinked->insert;
+          }
+          # what do we do with errors at this stage?
+          if ( $error ) {
+            warn "Error creating tax application link: $error\n";
+            next INVOICE; # for lack of a better idea
+          }
+          
+          if ( $unlinked_cents == 0 ) {
+            # then we've allocated all of this payment/credit application, 
+            # so grab the next one
+            $this_unlinked = shift @unlinked;
+            $unlinked_cents = int($this_unlinked->amount * 100) if $this_unlinked;
+          } elsif ( $link_cents == 0 ) {
+            # then we've covered all of this package tax fraction, so split
+            # off a new application from this one
+            $this_unlinked = $this_unlinked->new({
+                $this_unlinked->hash,
+                $pkey     => '',
+            });
+            # $unlinked_cents is still what it is
+          }
+
+        } #while $this_unlinked and $link_cents
+      } #foreach (@tax_links)
+    } #foreach $tax_item
+
+    $dbh->commit if $commit_each_invoice;
+    $committed = 1;
+
+  } #foreach $invnum
+  continue {
+    if (!$committed) {
+      $dbh->rollback;
+      die "Upgrade halted.\n" unless $commit_each_invoice;
+    }
+  }
+
+  $dbh->commit unless $commit_each_invoice;
+  '';
+}
+
 =back
 
 =head1 BUGS
@@ -958,6 +1410,8 @@ owed_setup and owed_recur could then be repaced by just owed, and
 cust_bill::open_cust_bill_pkg and
 cust_bill_ApplicationCommon::apply_to_lineitems could be simplified.
 
+The upgrade procedure is pretty sketchy.
+
 =head1 SEE ALSO
 
 L<FS::Record>, L<FS::cust_bill>, L<FS::cust_pkg>, L<FS::cust_main>, schema.html