+sub _list_sql {
+ my( $self, $param, $sql ) = @_;
+ my $sth = dbh->prepare($sql) or die dbh->errstr;
+ $sth->execute( map $self->$_(), @$param )
+ or die "Unexpected error executing statement $sql: ". $sth->errstr;
+ map $_->[0], @{ $sth->fetchall_arrayref };
+}
+
+=item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ]
+
+Takes an arrayref of L<FS::cust_bill_pkg> objects representing taxable
+line items, and returns a new L<FS::cust_bill_pkg> object representing
+the tax on them under this tax rate.
+
+This will have a pseudo-field, "cust_bill_pkg_tax_location", containing
+an arrayref of L<FS::cust_bill_pkg_tax_location> objects. Each of these
+will in turn have a "taxable_cust_bill_pkg" pseudo-field linking it to one
+of the taxable items. All of these links must be resolved as the objects
+are inserted.
+
+In addition to calculating the tax for the line items, this will calculate
+any appropriate tax exemptions and attach them to the line items.
+
+Options may include 'custnum' and 'invoice_date' in case the cust_bill_pkg
+objects belong to an invoice that hasn't been inserted yet.
+
+Options may include 'exemptions', an arrayref of L<FS::cust_tax_exempt_pkg>
+objects belonging to the same customer, to be counted against the monthly
+tax exemption limit if there is one.
+
+=cut
+
+# XXX change tax_rate.pm to work like this
+
+sub taxline {
+ my( $self, $taxables, %opt ) = @_;
+ return 'taxline called with no line items' unless @$taxables;
+
+ 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;
+
+ my $name = $self->taxname || 'Tax';
+ my $taxable_cents = 0;
+ my $tax_cents = 0;
+
+ my $cust_bill = $taxables->[0]->cust_bill;
+ my $custnum = $cust_bill ? $cust_bill->custnum : $opt{'custnum'};
+ my $invoice_date = $cust_bill ? $cust_bill->_date : $opt{'invoice_date'};
+ my $cust_main = FS::cust_main->by_key($custnum) if $custnum > 0;
+ if (!$cust_main) {
+ # better way to handle this? should we just assume that it's taxable?
+ die "unable to calculate taxes for an unknown customer\n";
+ }
+
+ # set a flag if the customer is tax-exempt
+ my $exempt_cust;
+ my $conf = FS::Conf->new;
+ if ( $conf->exists('cust_class-tax_exempt') ) {
+ my $cust_class = $cust_main->cust_class;
+ $exempt_cust = $cust_class->tax if $cust_class;
+ } else {
+ $exempt_cust = $cust_main->tax;
+ }
+
+ # set a flag if the customer is exempt from this tax here
+ my $exempt_cust_taxname = $cust_main->tax_exemption($self->taxname)
+ if $self->taxname;
+
+ # Gather any exemptions that are already attached to these cust_bill_pkgs
+ # so that we can deduct them from the customer's monthly limit.
+ my @existing_exemptions = @{ $opt{'exemptions'} };
+ push @existing_exemptions, @{ $_->cust_tax_exempt_pkg }
+ for @$taxables;
+
+ my $tax_item = FS::cust_bill_pkg->new({
+ 'pkgnum' => 0,
+ 'recur' => 0,
+ 'sdate' => '',
+ 'edate' => '',
+ 'itemdesc' => $name,
+ });
+ my @tax_location;
+
+ foreach my $cust_bill_pkg (@$taxables) {
+
+ my $cust_pkg = $cust_bill_pkg->cust_pkg;
+ my $part_pkg = $cust_bill_pkg->part_pkg;
+
+ my @new_exemptions;
+ my $taxable_charged = $cust_bill_pkg->setup + $cust_bill_pkg->recur
+ or next; # don't create zero-amount exemptions
+
+ # XXX the following procedure should probably be in cust_bill_pkg
+
+ if ( $exempt_cust ) {
+
+ push @new_exemptions, FS::cust_tax_exempt_pkg->new({
+ amount => $taxable_charged,
+ exempt_cust => 'Y',
+ });
+ $taxable_charged = 0;
+
+ } elsif ( $exempt_cust_taxname ) {
+
+ push @new_exemptions, FS::cust_tax_exempt_pkg->new({
+ amount => $taxable_charged,
+ exempt_cust_taxname => 'Y',
+ });
+ $taxable_charged = 0;
+
+ }
+
+ if ( ($part_pkg->setuptax eq 'Y' or $self->setuptax eq 'Y')
+ and $cust_bill_pkg->setup > 0 and $taxable_charged > 0 ) {
+
+ push @new_exemptions, FS::cust_tax_exempt_pkg->new({
+ amount => $cust_bill_pkg->setup,
+ exempt_setup => 'Y'
+ });
+ $taxable_charged -= $cust_bill_pkg->setup;
+
+ }
+ if ( ($part_pkg->recurtax eq 'Y' or $self->recurtax eq 'Y')
+ and $cust_bill_pkg->recur > 0 and $taxable_charged > 0 ) {
+
+ push @new_exemptions, FS::cust_tax_exempt_pkg->new({
+ amount => $cust_bill_pkg->recur,
+ exempt_recur => 'Y'
+ });
+ $taxable_charged -= $cust_bill_pkg->recur;
+
+ }
+
+ if ( $self->exempt_amount && $self->exempt_amount > 0
+ and $taxable_charged > 0 ) {
+ #my ($mon,$year) = (localtime($cust_bill_pkg->sdate) )[4,5];
+ my ($mon,$year) =
+ (localtime( $cust_bill_pkg->sdate || $invoice_date ) )[4,5];
+ $mon++;
+ $year += 1900;
+ my $freq = $cust_bill_pkg->freq;
+ unless ($freq) {
+ $freq = $part_pkg->freq || 1; # less trustworthy fallback
+ }
+ if ( $freq !~ /(\d+)$/ ) {
+ $dbh->rollback if $oldAutoCommit;
+ return "daily/weekly package definitions not (yet?)".
+ " compatible with monthly tax exemptions";
+ }
+ my $taxable_per_month =
+ sprintf("%.2f", $taxable_charged / $freq );
+
+ #call the whole thing off if this customer has any old
+ #exemption records...
+ my @cust_tax_exempt =
+ qsearch( 'cust_tax_exempt' => { custnum=> $custnum } );
+ if ( @cust_tax_exempt ) {
+ $dbh->rollback if $oldAutoCommit;
+ return
+ 'this customer still has old-style tax exemption records; '.
+ 'run bin/fs-migrate-cust_tax_exempt?';
+ }
+
+ foreach my $which_month ( 1 .. $freq ) {
+
+ #maintain the new exemption table now
+ my $sql = "
+ SELECT SUM(amount)
+ FROM cust_tax_exempt_pkg
+ LEFT JOIN cust_bill_pkg USING ( billpkgnum )
+ LEFT JOIN cust_bill USING ( invnum )
+ WHERE custnum = ?
+ AND taxnum = ?
+ AND year = ?
+ AND month = ?
+ AND exempt_monthly = 'Y'
+ ";
+ my $sth = dbh->prepare($sql) or do {
+ $dbh->rollback if $oldAutoCommit;
+ return "fatal: can't lookup exising exemption: ". dbh->errstr;
+ };
+ $sth->execute(
+ $custnum,
+ $self->taxnum,
+ $year,
+ $mon,
+ ) or do {
+ $dbh->rollback if $oldAutoCommit;
+ return "fatal: can't lookup exising exemption: ". dbh->errstr;
+ };
+ my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0;
+
+ foreach ( grep { $_->taxnum == $self->taxnum &&
+ $_->exempt_monthly eq 'Y' &&
+ $_->month == $mon &&
+ $_->year == $year
+ } @existing_exemptions
+ )
+ {
+ $existing_exemption += $_->amount;
+ }
+
+ my $remaining_exemption =
+ $self->exempt_amount - $existing_exemption;
+ if ( $remaining_exemption > 0 ) {
+ my $addl = $remaining_exemption > $taxable_per_month
+ ? $taxable_per_month
+ : $remaining_exemption;
+ push @new_exemptions, FS::cust_tax_exempt_pkg->new({
+ amount => sprintf('%.2f', $addl),
+ exempt_monthly => 'Y',
+ year => $year,
+ month => $mon,
+ });
+ $taxable_charged -= $addl;
+ }
+ last if $taxable_charged < 0.005;
+ # if they're using multiple months of exemption for a multi-month
+ # package, then record the exemptions in separate months
+ $mon++;
+ if ( $mon > 12 ) {
+ $mon -= 12;
+ $year++;
+ }
+
+ } #foreach $which_month
+ } # if exempt_amount
+
+ $_->taxnum($self->taxnum) foreach @new_exemptions;
+
+ # attach them to the line item
+ push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions;
+ push @existing_exemptions, @new_exemptions;
+
+ $taxable_charged = sprintf( "%.2f", $taxable_charged);
+ next if $taxable_charged == 0;
+
+ my $this_tax_cents = int($taxable_charged * $self->tax);
+ my $location = FS::cust_bill_pkg_tax_location->new({
+ 'taxnum' => $self->taxnum,
+ 'taxtype' => ref($self),
+ 'cents' => $this_tax_cents,
+ 'pkgnum' => $cust_bill_pkg->pkgnum,
+ 'locationnum' => $cust_bill_pkg->cust_pkg->tax_locationnum,
+ 'taxable_cust_bill_pkg' => $cust_bill_pkg,
+ 'tax_cust_bill_pkg' => $tax_item,
+ });
+ push @tax_location, $location;
+
+ $taxable_cents += $taxable_charged;
+ $tax_cents += $this_tax_cents;
+ } #foreach $cust_bill_pkg
+
+ # now round and distribute
+ my $extra_cents = sprintf('%.2f', $taxable_cents * $self->tax / 100) * 100
+ - $tax_cents;
+ # make sure we have an integer
+ $extra_cents = sprintf('%.0f', $extra_cents);
+ if ( $extra_cents < 0 ) {
+ die "nonsense extra_cents value $extra_cents";
+ }
+ $tax_cents += $extra_cents;
+ my $i = 0;
+ foreach (@tax_location) { # can never require more than a single pass, yes?
+ my $cents = $_->get('cents');
+ if ( $extra_cents > 0 ) {
+ $cents++;
+ $extra_cents--;
+ }
+ $_->set('amount', sprintf('%.2f', $cents/100));
+ }
+ $tax_item->set('setup' => sprintf('%.2f', $tax_cents / 100));
+ $tax_item->set('cust_bill_pkg_tax_location', \@tax_location);
+
+ return $tax_item;