diff options
| -rw-r--r-- | FS/FS/Schema.pm | 22 | ||||
| -rw-r--r-- | FS/FS/Upgrade.pm | 3 | ||||
| -rw-r--r-- | FS/FS/cust_bill_ApplicationCommon.pm | 5 | ||||
| -rw-r--r-- | FS/FS/cust_bill_pkg.pm | 486 | ||||
| -rw-r--r-- | FS/FS/cust_credit_bill_pkg.pm | 62 | ||||
| -rw-r--r-- | FS/FS/cust_main/Billing.pm | 125 | ||||
| -rw-r--r-- | FS/FS/cust_main_county.pm | 241 | ||||
| -rw-r--r-- | FS/FS/cust_tax_exempt_pkg.pm | 85 | ||||
| -rw-r--r-- | FS/FS/cust_tax_exempt_pkg_void.pm | 9 | ||||
| -rw-r--r-- | FS/FS/h_cust_main_exemption.pm | 19 | ||||
| -rw-r--r-- | FS/FS/h_part_pkg.pm | 37 | ||||
| -rw-r--r-- | FS/MANIFEST | 1 | ||||
| -rwxr-xr-x | bin/tax_location.upgrade | 31 | ||||
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 786 | ||||
| -rw-r--r-- | httemplate/search/cust_tax_exempt_pkg.cgi | 8 | ||||
| -rwxr-xr-x | httemplate/search/report_tax.cgi | 748 |
16 files changed, 1709 insertions, 959 deletions
diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 7be8c66..a6a1cda 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -1133,7 +1133,7 @@ sub tables_hashref { # 'middle', 'varchar', 'NULL', $char_d, '', '', 'first', 'varchar', '', $char_d, '', '', 'title', 'varchar', 'NULL', $char_d, '', '', #eg Head Bottle Washer - 'comment', 'varchar', 'NULL', $char_d, '', '', + 'comment', 'varchar', 'NULL', 255, '', '', 'disabled', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'contactnum', @@ -2740,10 +2740,16 @@ sub tables_hashref { #'custnum', 'int', '', '', '', '' 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', - 'year', 'int', '', '', '', '', - 'month', 'int', '', '', '', '', + 'year', 'int', 'NULL', '', '', '', + 'month', 'int', 'NULL', '', '', '', 'creditbillpkgnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', + # exemption type flags + 'exempt_cust', 'char', 'NULL', 1, '', '', + 'exempt_setup', 'char', 'NULL', 1, '', '', + 'exempt_recur', 'char', 'NULL', 1, '', '', + 'exempt_cust_taxname', 'char', 'NULL', 1, '', '', + 'exempt_monthly', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'exemptpkgnum', 'unique' => [], @@ -2760,10 +2766,16 @@ sub tables_hashref { #'custnum', 'int', '', '', '', '' 'billpkgnum', 'int', '', '', '', '', 'taxnum', 'int', '', '', '', '', - 'year', 'int', '', '', '', '', - 'month', 'int', '', '', '', '', + 'year', 'int', 'NULL', '', '', '', + 'month', 'int', 'NULL', '', '', '', 'creditbillpkgnum', 'int', 'NULL', '', '', '', 'amount', @money_type, '', '', + # exemption type flags + 'exempt_cust', 'char', 'NULL', 1, '', '', + 'exempt_setup', 'char', 'NULL', 1, '', '', + 'exempt_recur', 'char', 'NULL', 1, '', '', + 'exempt_cust_taxname', 'char', 'NULL', 1, '', '', + 'exempt_monthly', 'char', 'NULL', 1, '', '', ], 'primary_key' => 'exemptpkgnum', 'unique' => [], diff --git a/FS/FS/Upgrade.pm b/FS/FS/Upgrade.pm index 417b202..8e697d3 100644 --- a/FS/FS/Upgrade.pm +++ b/FS/FS/Upgrade.pm @@ -278,6 +278,9 @@ sub upgrade_data { #set up payment gateways if needed 'pay_batch' => [], + + #flag monthly tax exemptions + 'cust_tax_exempt_pkg' => [], ; \%hash; diff --git a/FS/FS/cust_bill_ApplicationCommon.pm b/FS/FS/cust_bill_ApplicationCommon.pm index cadb8a7..cb07050 100644 --- a/FS/FS/cust_bill_ApplicationCommon.pm +++ b/FS/FS/cust_bill_ApplicationCommon.pm @@ -337,6 +337,7 @@ sub calculate_applications { # could expand @open above, instead, for a slightly different magic effect my @result = (); foreach my $apply ( @apply ) { + # $apply = [ FS::cust_bill_pkg_tax_location record, amount ] my @sub_lines = $apply->[0]->cust_bill_pkg_tax_Xlocation; my $amount = $apply->[1]; warn "applying ". $apply->[1]. " to ". $apply->[0]->desc @@ -346,6 +347,10 @@ sub calculate_applications { my $owed = $subline->owed; push @result, [ $apply->[0], sprintf('%.2f', min($amount, $owed) ), + # $subline->primary_key is "billpkgtaxlocationnum" + # or "billpkgtaxratelocationnum" + # This is the ONLY place either of those fields will + # be set. { $subline->primary_key => $subline->get($subline->primary_key) }, ]; $amount -= $owed; 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 diff --git a/FS/FS/cust_credit_bill_pkg.pm b/FS/FS/cust_credit_bill_pkg.pm index 64f1f29..4189007 100644 --- a/FS/FS/cust_credit_bill_pkg.pm +++ b/FS/FS/cust_credit_bill_pkg.pm @@ -103,18 +103,22 @@ sub insert { return $error; } - my $payable = $self->cust_bill_pkg->payable($self->setuprecur); - my $taxable = $self->_is_taxable ? $payable : 0; - my $part_pkg = $self->cust_bill_pkg->part_pkg; - my $freq = $self->cust_bill_pkg->freq; + my $cust_bill_pkg = $self->cust_bill_pkg; + #'payable' is the amount charged (either setup or recur) + # minus any credit applications, including this one + my $payable = $cust_bill_pkg->payable($self->setuprecur); + my $part_pkg = $cust_bill_pkg->part_pkg; + my $freq = $cust_bill_pkg->freq; unless ($freq) { $freq = $part_pkg ? ($part_pkg->freq || 1) : 1;#fallback.. assumes unchanged } - my $taxable_per_month = sprintf("%.2f", $taxable / $freq ); + my $taxable_per_month = sprintf("%.2f", $payable / $freq ); my $credit_per_month = sprintf("%.2f", $self->amount / $freq ); #pennies? if ($taxable_per_month >= 0) { #panic if its subzero? - my $groupby = 'taxnum,year,month'; + my $groupby = join(',', + qw(taxnum year month exempt_monthly exempt_cust + exempt_cust_taxname exempt_setup exempt_recur)); my $sum = 'SUM(amount)'; my @exemptions = qsearch( { @@ -124,25 +128,55 @@ sub insert { 'extra_sql' => "GROUP BY $groupby HAVING $sum > 0", } ); + # each $exemption is now the sum of all monthly exemptions applied to + # this line item for a particular taxnum and month. foreach my $exemption ( @exemptions ) { - next if $taxable_per_month >= $exemption->amount; - my $amount = $exemption->amount - $taxable_per_month; - if ($amount > $credit_per_month) { - "cust_bill_pkg ". $self->billpkgnum. " Reducing.\n"; - $amount = $credit_per_month; + my $amount = 0; + if ( $exemption->exempt_monthly ) { + # finite exemptions + # $taxable_per_month is AFTER inserting the credit application, so + # if it's still larger than the exemption, we don't need to adjust + next if $taxable_per_month >= $exemption->amount; + # the amount of 'excess' exemption already in place (above the + # remaining charged amount). We'll de-exempt that much, or the + # amount of the new credit, whichever is smaller. + $amount = $exemption->amount - $taxable_per_month; + # $amount is the amount of 'excess' exemption already existing + # (above the remaining taxable charge amount). We'll "de-exempt" + # that much, or the amount of the new credit, whichever is smaller. + if ($amount > $credit_per_month) { + "cust_bill_pkg ". $self->billpkgnum. " Reducing.\n"; + $amount = $credit_per_month; + } + } elsif ( $exemption->exempt_setup or $exemption->exempt_recur ) { + # package defined exemptions: may be setup only, recur only, or both + my $method = 'exempt_'.$self->setuprecur; + if ( $exemption->$method ) { + # then it's exempt from the portion of the charge that this + # credit is being applied to + $amount = $self->amount; + } + } else { + # other types of exemptions: always equal to the amount of + # the charge + $amount = $self->amount; } + next if $amount == 0; + + # create a negative exemption my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg { + $exemption->hash, # for exempt_ flags, taxnum, month/year 'billpkgnum' => $self->billpkgnum, 'creditbillpkgnum' => $self->creditbillpkgnum, 'amount' => sprintf('%.2f', 0-$amount), - map { $_ => $exemption->$_ } split(',', $groupby) }; + my $error = $cust_tax_exempt_pkg->insert; if ( $error ) { $dbh->rollback if $oldAutoCommit; return "error inserting cust_tax_exempt_pkg: $error"; } - } + } #foreach $exemption } $dbh->commit or die $dbh->errstr if $oldAutoCommit; @@ -233,7 +267,7 @@ sub delete { return "error calculating taxes: $hashref_or_error"; } - push @generated_exemptions, @{ $cust_bill_pkg->_cust_tax_exempt_pkg || [] }; + push @generated_exemptions, @{ $cust_bill_pkg->cust_tax_exempt_pkg }; } foreach my $taxnum ( keys %seen ) { diff --git a/FS/FS/cust_main/Billing.pm b/FS/FS/cust_main/Billing.pm index bab94c3..02774c9 100644 --- a/FS/FS/cust_main/Billing.pm +++ b/FS/FS/cust_main/Billing.pm @@ -735,21 +735,25 @@ sub calculate_taxes { my @tax_line_items = (); # keys are tax names (as printed on invoices / itemdesc ) - # values are listrefs of taxlisthash keys (internal identifiers) + # values are arrayrefs of taxlisthash keys (internal identifiers) my %taxname = (); # keys are taxlisthash keys (internal identifiers) # values are (cumulative) amounts - my %tax = (); + my %tax_amount = (); # keys are taxlisthash keys (internal identifiers) - # values are listrefs of cust_bill_pkg_tax_location hashrefs + # values are arrayrefs 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 + # values are arrayrefs of cust_bill_pkg_tax_rate_location hashrefs my %tax_rate_location = (); + # keys are taxnums (not internal identifiers!) + # values are arrayrefs of cust_tax_exempt_pkg objects + my %tax_exemption; + foreach my $tax ( keys %$taxlisthash ) { # $tax is a tax identifier my $tax_object = shift @{ $taxlisthash->{$tax} }; @@ -759,14 +763,24 @@ sub calculate_taxes { warn "found ". $tax_object->taxname. " as $tax\n" if $DEBUG > 2; warn " ". join('/', @{ $taxlisthash->{$tax} } ). "\n" if $DEBUG > 2; # taxline calculates the tax on all cust_bill_pkgs in the - # first (arrayref) argument + # first (arrayref) argument, and returns a hashref of 'name' + # (the line item description) and 'amount'. + # It also calculates exemptions and attaches them to the cust_bill_pkgs + # in the argument. + my $taxables = $taxlisthash->{$tax}; + my $exemptions = $tax_exemption{$tax_object->taxnum} ||= []; my $hashref_or_error = - $tax_object->taxline( $taxlisthash->{$tax}, + $tax_object->taxline( $taxables, 'custnum' => $self->custnum, - 'invoice_time' => $invoice_time + 'invoice_time' => $invoice_time, + 'exemptions' => $exemptions, ); return $hashref_or_error unless ref($hashref_or_error); + # then collect any new exemptions generated for this tax + push @$exemptions, @{ $_->cust_tax_exempt_pkg } + foreach @$taxables; + unshift @{ $taxlisthash->{$tax} }, $tax_object; my $name = $hashref_or_error->{'name'}; @@ -776,7 +790,7 @@ sub calculate_taxes { $taxname{ $name } ||= []; push @{ $taxname{ $name } }, $tax; - $tax{ $tax } += $amount; + $tax_amount{ $tax } += $amount; # link records between cust_main_county/tax_rate and cust_location $tax_location{ $tax } ||= []; @@ -809,17 +823,21 @@ sub calculate_taxes { #move the cust_tax_exempt_pkg records to the cust_bill_pkgs we will commit my %packagemap = map { $_->pkgnum => $_ } @$cust_bill_pkg; foreach my $tax ( keys %$taxlisthash ) { - foreach ( @{ $taxlisthash->{$tax} }[1 ... scalar(@{ $taxlisthash->{$tax} })] ) { - next unless ref($_) eq 'FS::cust_bill_pkg'; - - my @cust_tax_exempt_pkg = splice( @{ $_->_cust_tax_exempt_pkg } ); + my $taxables = $taxlisthash->{$tax}; + my $tax_object = shift @$taxables; # the rest are line items + foreach my $cust_bill_pkg ( @$taxables ) { + next unless ref($cust_bill_pkg) eq 'FS::cust_bill_pkg'; + + my @cust_tax_exempt_pkg = splice @{ $cust_bill_pkg->cust_tax_exempt_pkg }; - next unless @cust_tax_exempt_pkg; #just avoiding the prob when irrelevant? - die "can't distribute tax exemptions: no line item for ". Dumper($_). - " in packagemap ". join(',', sort {$a<=>$b} keys %packagemap). "\n" - unless $packagemap{$_->pkgnum}; + next unless @cust_tax_exempt_pkg; + # get the non-disintegrated version + my $real_cust_bill_pkg = $packagemap{$cust_bill_pkg->pkgnum} + or die "can't distribute tax exemptions: no line item for ". + Dumper($_). " in packagemap ". + join(',', sort {$a<=>$b} keys %packagemap). "\n"; - push @{ $packagemap{$_->pkgnum}->_cust_tax_exempt_pkg }, + push @{ $real_cust_bill_pkg->cust_tax_exempt_pkg }, @cust_tax_exempt_pkg; } } @@ -827,15 +845,15 @@ sub calculate_taxes { #consolidate and create tax line items warn "consolidating and generating...\n" if $DEBUG > 2; foreach my $taxname ( keys %taxname ) { - my $tax = 0; + my $tax_total = 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}++; - warn "adding $tax{$taxitem}\n" if $DEBUG > 1; - $tax += $tax{$taxitem}; + warn "adding $tax_amount{$taxitem}\n" if $DEBUG > 1; + $tax_total += $tax_amount{$taxitem}; push @cust_bill_pkg_tax_location, map { new FS::cust_bill_pkg_tax_location $_ } @{ $tax_location{ $taxitem } }; @@ -843,9 +861,9 @@ sub calculate_taxes { map { new FS::cust_bill_pkg_tax_rate_location $_ } @{ $tax_rate_location{ $taxitem } }; } - next unless $tax; + next unless $tax_total; - $tax = sprintf('%.2f', $tax ); + $tax_total = sprintf('%.2f', $tax_total ); my $pkg_category = qsearchs( 'pkg_category', { 'categoryname' => $taxname, 'disabled' => '', @@ -866,7 +884,7 @@ sub calculate_taxes { push @tax_line_items, new FS::cust_bill_pkg { 'pkgnum' => 0, - 'setup' => $tax, + 'setup' => $tax_total, 'recur' => 0, 'sdate' => '', 'edate' => '', @@ -1197,8 +1215,11 @@ sub _handle_taxes { my $exempt = $conf->exists('cust_class-tax_exempt') ? ( $self->cust_class ? $self->cust_class->tax : '' ) : $self->tax; + # standardize this just to be sure + $exempt = ($exempt eq 'Y') ? 'Y' : ''; - if ( $exempt !~ /Y/i && $self->payby ne 'COMP' ) { + #if ( $exempt !~ /Y/i && $self->payby ne 'COMP' ) { + if ( $self->payby ne 'COMP' ) { if ( $conf->exists('enable_taxproducts') && ( scalar($part_pkg->part_pkg_taxoverride) @@ -1207,19 +1228,26 @@ sub _handle_taxes { ) { - foreach my $class (@classes) { - my $err_or_ref = $self->_gather_taxes( $part_pkg, $class, $cust_pkg ); - return $err_or_ref unless ref($err_or_ref); - $taxes{$class} = $err_or_ref; - } + if ( !$exempt ) { + + foreach my $class (@classes) { + my $err_or_ref = $self->_gather_taxes( $part_pkg, $class, $cust_pkg ); + return $err_or_ref unless ref($err_or_ref); + $taxes{$class} = $err_or_ref; + } + + unless (exists $taxes{''}) { + my $err_or_ref = $self->_gather_taxes( $part_pkg, '', $cust_pkg ); + return $err_or_ref unless ref($err_or_ref); + $taxes{''} = $err_or_ref; + } - unless (exists $taxes{''}) { - my $err_or_ref = $self->_gather_taxes( $part_pkg, '', $cust_pkg ); - return $err_or_ref unless ref($err_or_ref); - $taxes{''} = $err_or_ref; } - } else { + } else { # cust_main_county tax system + + # We fetch taxes even if the customer is completely exempt, + # because we need to record that fact. my @loc_keys = qw( district city county state country ); my $location = $cust_pkg->tax_location; @@ -1246,17 +1274,11 @@ sub _handle_taxes { } while ( !scalar(@taxes) && scalar(@elim) ); - @taxes = grep { ! $_->taxname or ! $self->tax_exemption($_->taxname) } - @taxes - if $self->cust_main_exemption; #just to be safe - - # all packages now have a locationnum and should get a - # cust_bill_pkg_tax_location record. The tax_locationnum - # may be the package's locationnum, or the customer's bill - # or service location. foreach (@taxes) { - $_->set('pkgnum', $cust_pkg->pkgnum); - $_->set('locationnum', $cust_pkg->tax_locationnum); + # These could become cust_bill_pkg_tax_location records, + # or cust_tax_exempt_pkg. We'll decide later. + $_->set('pkgnum', $cust_pkg->pkgnum); + $_->set('locationnum', $cust_pkg->tax_locationnum); } $taxes{''} = [ @taxes ]; @@ -1273,7 +1295,7 @@ sub _handle_taxes { } #if $conf->exists('enable_taxproducts') ... - } + } # if $self->payby eq 'COMP' #what's this doing in the middle of _handle_taxes? probably should split #this into three parts above in _make_lines @@ -1296,14 +1318,15 @@ sub _handle_taxes { # this is the tax identifier, not the taxname my $taxname = ref( $tax ). ' '. $tax->taxnum; -# $taxname .= ' pkgnum'. $cust_pkg->pkgnum. -# ' locationnum'. $cust_pkg->locationnum -# if $conf->exists('tax-pkg_address') && $cust_pkg->locationnum; + $taxname .= ' pkgnum'. $cust_pkg->pkgnum; + # We need to create a separate $taxlisthash entry for each pkgnum + # on the invoice, so that cust_bill_pkg_tax_location records will + # be linked correctly. - # $taxlisthash: keys are "setup", "recur", and usage classes - # values are arrayrefs, first the tax object (cust_main_county + # $taxlisthash: keys are "setup", "recur", and usage classes. + # Values are arrayrefs, first the tax object (cust_main_county # or tax_rate) and then any cust_bill_pkg objects that the - # tax applies to + # tax applies to. $taxlisthash->{ $taxname } ||= [ $tax ]; push @{ $taxlisthash->{ $taxname } }, $tax_cust_bill_pkg; diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm index 6316f23..143f62e 100644 --- a/FS/FS/cust_main_county.pm +++ b/FS/FS/cust_main_county.pm @@ -4,7 +4,7 @@ use strict; use vars qw( @ISA @EXPORT_OK $conf @cust_main_county %cust_main_county $countyflag ); # $cityflag ); use Exporter; -use FS::Record qw( qsearch dbh ); +use FS::Record qw( qsearch qsearchs dbh ); use FS::cust_bill_pkg; use FS::cust_bill; use FS::cust_pkg; @@ -164,6 +164,57 @@ sub recurtax { return ''; } +=item label OPTIONS + +Returns a label looking like "Anytown, Alameda County, CA, US". + +If the taxname field is set, it will look like +"CA Sales Tax (Anytown, Alameda County, CA, US)". + +If the taxclass is set, then it will be +"Anytown, Alameda County, CA, US (International)". + +Currently it will not contain the district, even if the city+county+state +is not unique. + +OPTIONS may contain "no_taxclass" (hides taxclass) and/or "no_city" +(hides city). It may also contain "out", in which case, if this +region (district+city+county+state+country) contains no non-zero +taxes, the label will read "Out of taxable region(s)". + +=cut + +sub label { + my ($self, %opt) = @_; + if ( $opt{'out'} + and $self->tax == 0 + and !defined(qsearchs('cust_main_county', { + 'district' => $self->district, + 'city' => $self->city, + 'county' => $self->county, + 'state' => $self->state, + 'country' => $self->country, + 'tax' => { op => '>', value => 0 }, + })) ) + { + return 'Out of taxable region(s)'; + } + my $label = $self->country; + $label = $self->state.", $label" if $self->state; + $label = $self->county." County, $label" if $self->county; + if (!$opt{no_city}) { + $label = $self->city.", $label" if $self->city; + } + # ugly labels when taxclass and taxname are both non-null... + # but this is how the tax report does it + if (!$opt{no_taxclass}) { + $label = "$label (".$self->taxclass.')' if $self->taxclass; + } + $label = $self->taxname." ($label)" if $self->taxname; + + $label; +} + =item sql_taxclass_sameregion Returns an SQL WHERE fragment or the empty string to search for entries @@ -207,21 +258,30 @@ sub _list_sql { =item taxline TAXABLES_ARRAYREF, [ OPTION => VALUE ... ] -Returns a listref of a name and an amount of tax calculated for the list of -packages or amounts referenced by TAXABLES_ARRAYREF. Returns a scalar error -message on error. +Returns an hashref of a name and an amount of tax calculated for the +line items (L<FS::cust_bill_pkg> objects) in TAXABLES_ARRAYREF. The line +items must come from the same invoice. Returns a scalar error message +on error. + +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 include custnum and invoice_date and are hints to this method +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 this should just return a cust_bill_pkg object for the tax, +# but that requires changing stuff in tax_rate.pm also. + sub taxline { my( $self, $taxables, %opt ) = @_; + return 'taxline called with no line items' unless @$taxables; - my @exemptions = (); - push @exemptions, @{ $_->_cust_tax_exempt_pkg } - for grep { ref($_) } @$taxables; - local $SIG{HUP} = 'IGNORE'; local $SIG{INT} = 'IGNORE'; local $SIG{QUIT} = 'IGNORE'; @@ -236,29 +296,92 @@ sub taxline { my $name = $self->taxname || 'Tax'; my $amount = 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; + foreach my $cust_bill_pkg (@$taxables) { my $cust_pkg = $cust_bill_pkg->cust_pkg; - my $cust_bill = $cust_pkg->cust_bill if $cust_pkg; - my $custnum = $cust_pkg ? $cust_pkg->custnum : $opt{custnum}; my $part_pkg = $cust_bill_pkg->part_pkg; - my $invoice_date = $cust_bill ? $cust_bill->_date : $opt{invoice_date}; - - my $taxable_charged = 0; - $taxable_charged += $cust_bill_pkg->setup - unless $part_pkg->setuptax =~ /^Y$/i - || $self->setuptax =~ /^Y$/i; - $taxable_charged += $cust_bill_pkg->recur - unless $part_pkg->recurtax =~ /^Y$/i - || $self->recurtax =~ /^Y$/i; - - next unless $taxable_charged; + + 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 ) { + 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 @@ -294,6 +417,7 @@ sub taxline { AND taxnum = ? AND year = ? AND month = ? + AND exempt_monthly = 'Y' "; my $sth = dbh->prepare($sql) or do { $dbh->rollback if $oldAutoCommit; @@ -302,7 +426,7 @@ sub taxline { $sth->execute( $custnum, $self->taxnum, - 1900+$year, + $year, $mon, ) or do { $dbh->rollback if $oldAutoCommit; @@ -311,9 +435,10 @@ sub taxline { my $existing_exemption = $sth->fetchrow_arrayref->[0] || 0; foreach ( grep { $_->taxnum == $self->taxnum && + $_->exempt_monthly eq 'Y' && $_->month == $mon && - $_->year == 1900+$year - } @exemptions + $_->year == $year + } @existing_exemptions ) { $existing_exemption += $_->amount; @@ -325,42 +450,50 @@ sub taxline { 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; - - my $cust_tax_exempt_pkg = new FS::cust_tax_exempt_pkg ( { - 'taxnum' => $self->taxnum, - 'year' => 1900+$year, - 'month' => $mon, - 'amount' => sprintf('%.2f', $addl ), - } ); - if ($cust_bill_pkg->billpkgnum) { - $cust_tax_exempt_pkg->billpkgnum($cust_bill_pkg->billpkgnum); - my $error = $cust_tax_exempt_pkg->insert; - if ( $error ) { - $dbh->rollback if $oldAutoCommit; - return "fatal: can't insert cust_tax_exempt_pkg: $error"; - } - }else{ - push @exemptions, $cust_tax_exempt_pkg; - push @{ $cust_bill_pkg->_cust_tax_exempt_pkg }, $cust_tax_exempt_pkg; - } # if $cust_bill_pkg->billpkgnum - } # if $remaining_exemption > 0 - - #++ + } + 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++; - #until ( $mon < 12 ) { $mon -= 12; $year++; } - until ( $mon < 13 ) { $mon -= 12; $year++; } + if ( $mon > 12 ) { + $mon -= 12; + $year++; + } } #foreach $which_month + } # if exempt_amount + + $_->taxnum($self->taxnum) foreach @new_exemptions; + + if ( $cust_bill_pkg->billpkgnum ) { + die "tried to calculate tax exemptions on a previously billed line item\n"; + # this is unnecessary +# foreach my $cust_tax_exempt_pkg (@new_exemptions) { +# my $error = $cust_tax_exempt_pkg->insert; +# if ( $error ) { +# $dbh->rollback if $oldAutoCommit; +# return "can't insert cust_tax_exempt_pkg: $error"; +# } +# } + } - } #if $tax->exempt_amount + # attach them to the line item + push @{ $cust_bill_pkg->cust_tax_exempt_pkg }, @new_exemptions; + push @existing_exemptions, @new_exemptions; + # If we were smart, we'd also generate a cust_bill_pkg_tax_location + # record at this point, but that would require redesigning more stuff. $taxable_charged = sprintf( "%.2f", $taxable_charged); - $amount += $taxable_charged * $self->tax / 100 - } - - $dbh->commit or die $dbh->errstr if $oldAutoCommit; + $amount += $taxable_charged * $self->tax / 100; + } #foreach $cust_bill_pkg return { 'name' => $name, diff --git a/FS/FS/cust_tax_exempt_pkg.pm b/FS/FS/cust_tax_exempt_pkg.pm index e63b84b..bbabb5b 100644 --- a/FS/FS/cust_tax_exempt_pkg.pm +++ b/FS/FS/cust_tax_exempt_pkg.pm @@ -7,6 +7,10 @@ use FS::cust_main_Mixin; use FS::cust_bill_pkg; use FS::cust_main_county; use FS::cust_credit_bill_pkg; +use FS::UID qw(dbh); +use FS::upgrade_journal; + +# some kind of common ancestor with cust_bill_pkg_tax_location would make sense @ISA = qw( FS::cust_main_Mixin FS::Record ); @@ -32,22 +36,45 @@ FS::cust_tax_exempt_pkg - Object methods for cust_tax_exempt_pkg records =head1 DESCRIPTION An FS::cust_tax_exempt_pkg object represents a record of a customer tax -exemption. Currently this is only used for "texas tax". FS::cust_tax_exempt -inherits from FS::Record. The following fields are currently supported: +exemption. Whenever a package would be taxed (based on its location and +taxclass), but some or all of it is exempt from taxation, an +FS::cust_tax_exempt_pkg record is created. + +FS::cust_tax_exempt inherits from FS::Record. The following fields are +currently supported: =over 4 =item exemptpkgnum - primary key -=item billpkgnum - invoice line item (see L<FS::cust_bill_pkg>) +=item billpkgnum - invoice line item (see L<FS::cust_bill_pkg>) that +was exempted from tax. =item taxnum - tax rate (see L<FS::cust_main_county>) -=item year +=item year - the year in which the exemption occurred. NULL if this +is a customer or package exemption rather than a monthly exemption. + +=item month - the month in which the exemption occurred. NULL if this +is a customer or package exemption. + +=item amount - the amount of revenue exempted. For monthly exemptions +this may be anything up to the monthly exemption limit defined in +L<FS::cust_main_county> for this tax. For customer exemptions it is +always the full price of the line item. For package exemptions it +may be the setup fee, the recurring fee, or the sum of those. + +=item exempt_cust - flag indicating that the customer is tax-exempt +(cust_main.tax = 'Y'). -=item month +=item exempt_cust_taxname - flag indicating that the customer is exempt +from the tax with this name (see L<FS::cust_main_exemption). -=item amount +=item exempt_setup, exempt_recur: flag indicating that the package's setup +or recurring fee is not taxable (part_pkg.setuptax and part_pkg.recurtax). + +=item exempt_monthly: flag indicating that this is a monthly per-customer +exemption (Texas tax). =back @@ -109,18 +136,44 @@ and replace methods. sub check { my $self = shift; - $self->ut_numbern('exemptnum') -# || $self->ut_foreign_key('custnum', 'cust_main', 'custnum') + my $error = $self->ut_numbern('exemptnum') || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg', 'billpkgnum') || $self->ut_foreign_key('taxnum', 'cust_main_county', 'taxnum') || $self->ut_foreign_keyn('creditbillpkgnum', 'cust_credit_bill_pkg', 'creditbillpkgnum') - || $self->ut_number('year') #check better - || $self->ut_number('month') #check better + || $self->ut_numbern('year') #check better + || $self->ut_numbern('month') #check better || $self->ut_money('amount') + || $self->ut_flag('exempt_cust') + || $self->ut_flag('exempt_setup') + || $self->ut_flag('exempt_recur') + || $self->ut_flag('exempt_cust_taxname') || $self->SUPER::check ; + + return $error if $error; + + if ( $self->get('exempt_cust') ) { + $self->set($_ => '') for qw( + exempt_cust_taxname exempt_setup exempt_recur exempt_monthly month year + ); + } elsif ( $self->get('exempt_cust_taxname') ) { + $self->set($_ => '') for qw( + exempt_setup exempt_recur exempt_monthly month year + ); + } elsif ( $self->get('exempt_setup') || $self->get('exempt_recur') ) { + $self->set($_ => '') for qw(exempt_monthly month year); + } elsif ( $self->get('exempt_monthly') ) { + $self->year =~ /^\d{4}$/ + or return "illegal exemption year: '".$self->year."'"; + $self->month >= 1 && $self->month <= 12 + or return "illegal exemption month: '".$self->month."'"; + } else { + return "no exemption type selected"; + } + + ''; } =item cust_main_county @@ -135,6 +188,18 @@ sub cust_main_county { qsearchs( 'cust_main_county', { 'taxnum', $self->taxnum } ); } +sub _upgrade_data { + my $class = shift; + + my $journal = 'cust_tax_exempt_pkg_flags'; + if ( !FS::upgrade_journal->is_done($journal) ) { + my $sql = "UPDATE cust_tax_exempt_pkg SET exempt_monthly = 'Y' ". + "WHERE month IS NOT NULL"; + dbh->do($sql) or die dbh->errstr; + FS::upgrade_journal->set_done($journal); + } +} + =back =head1 BUGS diff --git a/FS/FS/cust_tax_exempt_pkg_void.pm b/FS/FS/cust_tax_exempt_pkg_void.pm index 51c85b4..bfbc8c7 100644 --- a/FS/FS/cust_tax_exempt_pkg_void.pm +++ b/FS/FS/cust_tax_exempt_pkg_void.pm @@ -114,10 +114,15 @@ sub check { $self->ut_number('exemptpkgnum') || $self->ut_foreign_key('billpkgnum', 'cust_bill_pkg_void', 'billpkgnum' ) || $self->ut_foreign_key('taxnum', 'cust_main_county', 'taxnum') - || $self->ut_number('year') - || $self->ut_number('month') + || $self->ut_numbern('year') + || $self->ut_numbern('month') || $self->ut_numbern('creditbillpkgnum') #no FK check, will have been del'ed || $self->ut_money('amount') + || $self->ut_flag('exempt_cust') + || $self->ut_flag('exempt_setup') + || $self->ut_flag('exempt_recur') + || $self->ut_flag('exempt_cust_taxname') + || $self->ut_flag('exempt_monthly') ; return $error if $error; diff --git a/FS/FS/h_cust_main_exemption.pm b/FS/FS/h_cust_main_exemption.pm new file mode 100644 index 0000000..072c412 --- /dev/null +++ b/FS/FS/h_cust_main_exemption.pm @@ -0,0 +1,19 @@ +package FS::h_cust_main_exemption; + +use strict; +use base qw( FS::h_Common FS::cust_main_exemption ); + +sub table { 'h_cust_main_exemption' }; + +=head1 NAME + +FS::h_cust_main_exemption - Historical customer tax exemption records. + +=head1 SEE ALSO + +L<FS::cust_main_exemption>, L<FS::h_Common>, L<FS::Record>. + +=cut + +1; + diff --git a/FS/FS/h_part_pkg.pm b/FS/FS/h_part_pkg.pm new file mode 100644 index 0000000..2c0e65f --- /dev/null +++ b/FS/FS/h_part_pkg.pm @@ -0,0 +1,37 @@ +package FS::h_part_pkg; + +use strict; +use vars qw( @ISA ); +use base qw(FS::h_Common FS::part_pkg); + +sub table { 'h_part_pkg' }; + +sub _rebless {}; # don't try to rebless these + +=head1 NAME + +FS::h_part_pkg - Historical record of package definition. + +=head1 SYNOPSIS + +=head1 DESCRIPTION + +An FS::h_part_pkg object represents historical changes to package +definitions. + +=head1 BUGS + +Many important properties of a part_pkg are in other tables, especially +plan options, service allotments, and link/bundle relationships. The +methods to access those from the part_pkg will work, but they're +really accessing current, not historical, data. Be careful. + +=head1 SEE ALSO + +L<FS::part_pkg>, L<FS::h_Common>, L<FS::Record>, schema.html from the base +documentation. + +=cut + +1; + diff --git a/FS/MANIFEST b/FS/MANIFEST index 479dcad..b5ee87e 100644 --- a/FS/MANIFEST +++ b/FS/MANIFEST @@ -94,6 +94,7 @@ FS/h_cust_pkg_reason.pm FS/h_cust_svc.pm FS/h_cust_tax_exempt.pm FS/h_domain_record.pm +FS/h_part_pkg.pm FS/h_svc_acct.pm FS/h_svc_broadband.pm FS/h_svc_domain.pm diff --git a/bin/tax_location.upgrade b/bin/tax_location.upgrade new file mode 100755 index 0000000..8140945 --- /dev/null +++ b/bin/tax_location.upgrade @@ -0,0 +1,31 @@ +#!/usr/bin/perl + +use FS::UID qw(adminsuidsetup); +use FS::Record; +use FS::cust_bill_pkg; +use Date::Parse qw(str2time); +use Getopt::Std; +getopts('s:e:'); +my $username = shift @ARGV; + +if (!$username) { + print +"Usage: tax_location.upgrade [ -s START ] [ -e END ] username + +This script creates cust_bill_pkg_tax_location and cust_tax_exempt_pkg records +for existing sales tax records prior to the 3.0 cust_location changes. Changes +will be committed immediately; back up your data and run 'make +install-perl-modules' and 'freeside-upgrade' before running this script. +START and END specify an optional range of invoice dates to upgrade. + +"; + exit(1); +} + +my %opt; +$opt{s} = str2time($opt_s) if $opt_s; +$opt{e} = str2time($opt_e) if $opt_e; + +adminsuidsetup($username); +FS::cust_bill_pkg->upgrade_tax_location(%opt); +1; diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index b6b70a0..4c0fa4a 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -3,14 +3,10 @@ 'name' => emt('line items'), 'query' => $query, 'count_query' => $count_query, - 'count_addl' => [ $money_char. '%.2f total', ], + 'count_addl' => \@total_desc, 'header' => [ emt('Description'), - emt('Setup charge'), - ( $use_usage eq 'usage' - ? emt('Usage charge') - : emt('Recurring charge') - ), + @peritem_desc, emt('Invoice'), emt('Date'), FS::UI::Web::cust_header(), @@ -22,41 +18,21 @@ }, #strikethrough or "N/A ($amount)" or something these when # they're not applicable to pkg_tax search - sub { my $cust_bill_pkg = shift; - sprintf($money_char.'%.2f', $cust_bill_pkg->setup ); - }, - sub { my $row = shift; - my $value = 0; - if ( $use_usage eq 'recurring' ) { - $value = $row->recur - $row->usage; - } elsif ( $use_usage eq 'usage' ) { - $value = $row->usage; - } else { - $value = $row->recur; - } - sprintf($money_char.'%.2f', $value ); - }, + @peritem_sub, 'invnum', sub { time2str('%b %d %Y', shift->_date ) }, \&FS::UI::Web::cust_fields, ], 'sort_fields' => [ '', - 'setup', - ( $use_usage eq 'recurring' - ? 'recur - usage' : - $use_usage eq 'usage' - ? 'usage' - : 'recur' - ), + @peritem, 'invnum', '_date', ], 'links' => [ #'', '', - '', - '', + @peritem_null, $ilink, $ilink, ( map { $_ ne 'Cust. Status' ? $clink : '' } @@ -64,15 +40,14 @@ ), ], #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(), - 'align' => 'lr'. - 'r'. + 'align' => 'l'. + $peritem_align. 'rc'. FS::UI::Web::cust_aligns(), 'color' => [ #'', '', - '', - '', + @peritem_null, '', '', FS::UI::Web::cust_colors(), @@ -80,37 +55,126 @@ 'style' => [ #'', '', - '', - '', + @peritem_null, '', '', FS::UI::Web::cust_styles(), ], &> -<%init> +<%doc> + +Output parameters: +- distribute: Boolean. If true, recurring fees will be "prorated" for the + portion of the package date range (sdate-edate) that falls within the date + range of the report. Line items will be limited to those for which this + portion is > 0. This disables filtering on invoice date. + +- use_usage: Separate usage (cust_bill_pkg_detail records) from + recurring charges. If set to "usage", will show usage instead of + recurring charges. If set to "recurring", will deduct usage and only + show the flat rate charge. If not passed, the "recurring charge" column + will include usage charges also. + +Filtering parameters: +- begin, end: Date range. Applies to invoice date, not necessarily package + date range. But see "distribute". + +- status: Customer status (active, suspended, etc.). This will filter on + _current_ customer status, not status at the time the invoice was generated. + +- agentnum: Filter on customer agent. + +- refnum: Filter on customer reference source. + +- classnum: Filter on package class. + +- use_override: Apply "classnum" and "taxclass" filtering based on the + override (bundle) pkgpart, rather than always using the true pkgpart. + +- nottax: Limit to items that are not taxes (pkgnum > 0). + +- istax: Limit to items that are taxes (pkgnum == 0). + +- taxnum: Limit to items whose tax definition matches this taxnum. + With "nottax" that means items that are subject to that tax; + with "istax" it's the tax charges themselves. Can be specified + more than once to include multiple taxes. + +- country, state, county, city: Limit to items whose tax location + matches these fields. If "nottax" it's the tax location of the package; + if "istax" the location of the tax. + +- taxname, taxnameNULL: With "nottax", limit to items whose tax location + matches a tax with this name. With "istax", limit to items that have + this tax name. taxnameNULL is equivalent to "taxname = '' OR taxname + = 'Tax'". + +- out: With "nottax", limit to items that don't match any tax definition. + With "istax", find tax items that are unlinked to their tax definitions. + Current Freeside (> July 2012) always creates tax links, but unlinked + items may result from an incomplete upgrade of legacy data. + +- locationtaxid: With "nottax", limit to packages matching this + tax_rate_location ID; with "tax", limit to taxes generated from that + location. + +- taxclass: Filter on package taxclass. + +- taxclassNULL: With "nottax", limit to items that would be subject to the + tax with taxclass = NULL. This doesn't necessarily mean part_pkg.taxclass + is NULL; it also includes taxclasses that don't have a tax in this region. + +- itemdesc: Limit to line items with this description. Note that non-tax + packages usually have a description of NULL. (Deprecated.) + +- report_group: Can contain '=' or '!=' followed by a string to limit to + line items where itemdesc starts with, or doesn't start with, the string. + +- cust_tax: Limit to customers who are tax-exempt. If "taxname" is also + specified, limit to customers who are also specifically exempt from that + tax. + +- pkg_tax: Limit to packages that are tax-exempt, and only include the + exempt portion (setup, recurring, or both) when calculating totals. + +- taxable: Limit to packages that are subject to tax, i.e. where a + cust_bill_pkg_tax_location record exists. -#LOTS of false laziness below w/cust_credit_bill_pkg.cgi +- credit: Limit to line items that received a credit application. The + amount of the credit will also be shown. + +</%doc> +<%init> die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); my $conf = new FS::Conf; +my $money_char = $conf->config('money_char') || '$'; my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' ); +my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)'); +my @total_desc = ( '%d line items', $money_char.'%.2f total' ); # sprintf strings +my @peritem = ( 'setup', 'recur' ); +my @peritem_desc = ( 'Setup charge', 'Recurring charge' ); my ($join_cust, $join_pkg ) = ('', ''); +my $use_usage; + +# valid in both the tax and non-tax cases +$join_cust = + " LEFT JOIN cust_bill USING (invnum) + LEFT JOIN cust_main USING (custnum) + "; -#here is the agent virtualization +#agent virtualization my $agentnums_sql = $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' ); my @where = ( $agentnums_sql ); +# date range my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); -if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { - push @where, FS::cust_main->cust_status_sql . " = '$1'"; -} - if ( $cgi->param('distribute') == 1 ) { push @where, "sdate <= $ending", "edate > $beginning", @@ -121,381 +185,371 @@ else { "cust_bill._date <= $ending"; } +# status +if ( $cgi->param('status') =~ /^([a-z]+)$/ ) { + push @where, FS::cust_main->cust_status_sql . " = '$1'"; +} + +# agentnum if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { push @where, "cust_main.agentnum = $1"; } +# refnum if ( $cgi->param('refnum') =~ /^(\d+)$/ ) { push @where, "cust_main.refnum = $1"; } -#classnum -# not specified: all classes -# 0: empty class -# N: classnum -my $use_override = $cgi->param('use_override'); -if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { - my $comparison = ''; - if ( $1 == 0 ) { - $comparison = "IS NULL"; - } else { - $comparison = "= $1"; - } +# the non-tax case +if ( $cgi->param('nottax') ) { - if ( $use_override ) { - push @where, "( - part_pkg.classnum $comparison AND pkgpart_override IS NULL OR - override.classnum $comparison AND pkgpart_override IS NOT NULL + push @where, 'cust_bill_pkg.pkgnum > 0'; + + # then we want the package and its definition + $join_pkg = +' LEFT JOIN cust_pkg USING (pkgnum) + LEFT JOIN part_pkg USING (pkgpart)'; + + my $part_pkg = 'part_pkg'; + if ( $cgi->param('use_override') ) { + # still need the real part_pkg for tax applicability, + # so alias this one + $join_pkg .= " LEFT JOIN part_pkg AS override ON ( + COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart )"; - } else { - push @where, "part_pkg.classnum $comparison"; + $part_pkg = 'override'; } -} + push @select, 'part_pkg.pkg'; # or should this use override? -if ( $cgi->param('taxclass') - && ! $cgi->param('istax') #no part_pkg.taxclass in this case - #(should we save a taxclass or a link to taxnum - # in cust_bill_pkg or something like - # cust_bill_pkg_tax_location?) - ) -{ + my @tax_where; # will go into a subquery + my @exempt_where; # will also go into a subquery - #override taxclass when use_override is specified? probably + # classnum (of override pkgpart if applicable) + # not specified: all classes + # 0: empty class + # N: classnum + if ( $cgi->param('classnum') =~ /^(\d+)$/ ) { + push @where, "COALESCE($part_pkg.classnum, 0) = $1"; + } - push @where, ' part_pkg.taxclass IN ( '. - join(', ', map dbh->quote($_), $cgi->param('taxclass') ). - ' ) '; + # taxclass + if ( $cgi->param('taxclassNULL') ) { + # a little different from 'taxclass' in that it applies to the + # effective taxclass, not the real one + push @tax_where, 'cust_main_county.taxclass IS NULL' + } elsif ( $cgi->param('taxclass') ) { + push @tax_where, "$part_pkg.taxclass IN (" . + join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ). + ')'; + } -} + if ( $cgi->param('exempt_cust') eq 'Y' ) { + # tax-exempt customers + push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')"; -my @loc_param = qw( district city county state country ); + } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package + # non-taxable package charges + push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')"; + } + # we don't handle exempt_monthly here + + if ( $cgi->param('taxname') ) { # specific taxname + push @tax_where, 'cust_main_county.taxname = '. + dbh->quote($cgi->param('taxname')); + } elsif ( $cgi->param('taxnameNULL') ) { + push @tax_where, 'cust_main_county.taxname IS NULL OR '. + 'cust_main_county.taxname = \'Tax\''; + } -if ( $cgi->param('out') ) { + # country:state:county:city:district (may be repeated) + # You can also pass a big list of taxnums but that leads to huge URLs. + # Note that this means "packages whose tax is in this region", not + # "packages in this region". It's meant for links from the tax report. + if ( $cgi->param('region') ) { + my @orwhere; + foreach ( $cgi->param('region') ) { + my %loc; + @loc{qw(country state county city district)} = + split(':', $cgi->param('region')); + my $string = join(' AND ', + map { + if ( $loc{$_} ) { + "$_ = ".dbh->quote($loc{$_}); + } else { + "$_ IS NULL"; + } + } keys(%loc) + ); + push @orwhere, "($string)"; + } + push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere; + } - my ( $loc_sql, @param ) = FS::cust_location->in_county_sql( 'ornull' => 1 ); -# while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution -# $loc_sql =~ s/\?/'cust_main_county.'.shift(@param)/e; -# } + # specific taxnums + if ( $cgi->param('taxnum') ) { + my $taxnum_in = join(',', + grep /^\d+$/, $cgi->param('taxnum') + ); + push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)" + if $taxnum_in; + } - push @where, " - 0 = ( - SELECT COUNT(*) FROM cust_main_county - WHERE cust_main_county.tax > 0 - AND $loc_sql - ) - "; + # If we're showing exempt items, we need to find those with + # cust_tax_exempt_pkg records matching the selected taxes. + # If we're showing taxable items, we need to find those with + # cust_bill_pkg_tax_location records. We also need to find the + # exemption records so that we can show the taxable amount. + # If we're showing all items, we need the union of those. + # If we're showing 'out' (items that aren't region/class taxable), + # then we need the set of all items minus the union of those. - #not linked to by anything, but useful for debugging "out of taxable region" - if ( grep $cgi->param($_), @loc_param ) { + my $exempt_sub; - my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param; + if ( @exempt_where or @tax_where + or $cgi->param('taxable') or $cgi->param('out') ) + { + # process exemption restrictions, including @tax_where + my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum + FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)'; - my ( $loc_sql, @param ) = FS::cust_location->in_county_sql(param => 1); - while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution - $loc_sql =~ s/\?/$ph{shift(@param)}/e; - } + $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) + if (@tax_where or @exempt_where); - push @where, $loc_sql; + $exempt_sub .= ' GROUP BY billpkgnum'; + $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt + USING (billpkgnum)"; + } + + if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) { + # process tax restrictions + unshift @tax_where, + 'cust_main_county.tax > 0'; + + my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum + FROM cust_bill_pkg_tax_location + JOIN cust_bill_pkg AS tax_item USING (billpkgnum) + JOIN cust_main_county USING (taxnum) + WHERE ". join(' AND ', @tax_where). + " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum"; + + $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax + ON (item_tax.invnum = cust_bill_pkg.invnum AND + item_tax.pkgnum = cust_bill_pkg.pkgnum)"; } -} elsif ( $cgi->param('country') ) { # and not $cgi->param('out') + # now do something with that + if ( @exempt_where ) { - my @counties = $cgi->param('county'); - - if ( scalar(@counties) > 1 ) { + push @where, 'item_exempt.billpkgnum IS NOT NULL'; + push @select, 'item_exempt.exempt_amount'; + push @peritem, 'exempt_amount'; + push @peritem_desc, 'Exempt'; + push @total, 'SUM(exempt_amount)'; + push @total_desc, "$money_char%.2f tax-exempt"; - #hacky, could be more efficient. care if it is ever used for more than the - # tax-report_groups filtering kludge + } elsif ( $cgi->param('taxable') ) { - my $locs_sql = - ' ( '. join(' OR ', map { + my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. + '- COALESCE(item_exempt.exempt_amount, 0)'; - my %ph = ( 'county' => dbh->quote($_), - map { $_ => dbh->quote( $cgi->param($_) ) } - qw( district city state country ) - ); + push @where, 'item_tax.invnum IS NOT NULL'; + push @select, "($taxable) AS taxable_amount"; + push @peritem, 'taxable_amount'; + push @peritem_desc, 'Taxable'; + push @total, "SUM($taxable)"; + push @total_desc, "$money_char%.2f taxable"; - my ( $loc_sql, @param ) = FS::cust_location->in_county_sql(param => 1); - while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution - $loc_sql =~ s/\?/$ph{shift(@param)}/e; - } + } elsif ( $cgi->param('out') ) { + + push @where, 'item_tax.invnum IS NULL', + 'item_exempt.billpkgnum IS NULL'; - $loc_sql; + } elsif ( @tax_where ) { - } @counties + # union of taxable + all exempt_ cases + push @where, + '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)'; - ). ' ) '; + } - push @where, $locs_sql; + # recur/usage separation + $use_usage = $cgi->param('usage'); + if ( $use_usage eq 'recurring' ) { - } else { #scalar(@counties) <= 1 + my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1); + push @select, "($recur_no_usage) AS recur_no_usage"; + $peritem[1] = 'recur_no_usage'; + $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)"; + $total_desc[1] .= ' (excluding usage)'; - my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param; + } elsif ( $use_usage eq 'usage' ) { - - my ( $loc_sql, @param ) = FS::cust_location->in_county_sql(param => 1); - while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution - $loc_sql =~ s/\?/$ph{shift(@param)}/e; - } + my $usage = FS::cust_bill_pkg->usage_sql(); + push @select, "($usage) AS _usage"; + # there's already a method named 'usage' + $peritem[1] = '_usage'; + $peritem_desc[1] = 'Usage charge'; + $total[1] = "SUM($usage)"; + $total_desc[1] .= ' usage charges'; + } - push @where, $loc_sql; +} elsif ( $cgi->param('istax') ) { - } - - if ( $cgi->param('istax') ) { - if ( $cgi->param('taxname') ) { - push @where, 'itemdesc = '. dbh->quote( $cgi->param('taxname') ); - #} elsif ( $cgi->param('taxnameNULL') { - } else { - push @where, "( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - } - } elsif ( $cgi->param('nottax') ) { - #what can we usefully do with "taxname" ???? look up a class??? - } else { - #warn "neither nottax nor istax parameters specified"; - } + @peritem = ( 'setup' ); # taxes only have setup + @peritem_desc = ( 'Tax charge' ); - if ( $cgi->param('taxclassNULL') - && ! $cgi->param('istax') #no part_pkg.taxclass in this case - #(see comment above?) - ) - { - my %hash = ( 'country' => scalar($cgi->param('country')) ); - foreach (qw( state county )) { - $hash{$_} = scalar($cgi->param($_)) if $cgi->param($_); - } - my $cust_main_county = qsearchs('cust_main_county', \%hash); - die "unknown base region for empty taxclass" unless $cust_main_county; + push @where, 'cust_bill_pkg.pkgnum = 0'; - my $same_sql = $cust_main_county->sql_taxclass_sameregion; - $same_sql =~ s/taxclass/part_pkg.taxclass/g; - push @where, $same_sql if $same_sql; + # tax location when using tax_rate_location + if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { - } + $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. + ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )'; + push @where, FS::tax_rate_location->location_sql( + map { $_ => (scalar($cgi->param($_)) || '') } + qw( district city county state locationtaxid ) + ); -} elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { -# and not $cgi->param('out' or 'country') + $total[1] = 'SUM( + COALESCE(cust_bill_pkg_tax_rate_location.amount, + cust_bill_pkg.setup + cust_bill_pkg.recur) + )'; - push @where, FS::tax_rate_location->location_sql( - map { $_ => (scalar($cgi->param($_)) || '') } - qw( district city county state locationtaxid ) - ); + } elsif ( $cgi->param('out') ) { -} + $join_pkg = ' + LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) + '; + push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL'; -if ( $cgi->param('itemdesc') ) { - if ( $cgi->param('itemdesc') eq 'Tax' ) { - push @where, "(itemdesc='Tax' OR itemdesc is null)"; - } else { - push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc')); + # each billpkgnum should appear only once + $total[0] = 'COUNT(*)'; + $total[1] = 'SUM(cust_bill_pkg.setup)'; + + } else { # not locationtaxid or 'out'--the normal case + + $join_pkg = ' + LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) + JOIN cust_main_county USING (taxnum) + '; + + # don't double-count the components of consolidated taxes + $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)'; + $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)'; } -} -if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ && $cgi->param('istax') ) { - my ( $group_op, $group_value ) = ( $1, $2 ); - if ( $group_op eq '=' ) { - #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%'); - push @where, 'itemdesc = '. dbh->quote($group_value); - } elsif ( $group_op eq '!=' ) { - push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )'; - } else { - die "guru meditation #00de: group_op $group_op\n"; + # taxclass + if ( $cgi->param('taxclassNULL') ) { + push @where, 'cust_main_county.taxclass IS NULL'; } - -} -push @where, 'cust_bill_pkg.pkgnum != 0' if $cgi->param('nottax'); -push @where, 'cust_bill_pkg.pkgnum = 0' if $cgi->param('istax'); - -if ( $cgi->param('cust_tax') ) { - #false laziness -ish w/report_tax.cgi - my $cust_exempt; - if ( $cgi->param('taxname') ) { - my $q_taxname = dbh->quote($cgi->param('taxname')); - $cust_exempt = - "( tax = 'Y' - OR EXISTS ( SELECT 1 FROM cust_main_exemption - WHERE cust_main_exemption.custnum = cust_main.custnum - AND cust_main_exemption.taxname = $q_taxname ) - ) - "; - } else { - $cust_exempt = " tax = 'Y' "; + # taxname + if ( $cgi->param('taxnameNULL') ) { + push @where, 'cust_main_county.taxname IS NULL OR '. + 'cust_main_county.taxname = \'Tax\''; + } elsif ( $cgi->param('taxname') ) { + push @where, 'cust_main_county.taxname = '. + dbh->quote($cgi->param('taxname')); } - push @where, $cust_exempt; -} + # specific taxnums + if ( $cgi->param('taxnum') ) { + my $taxnum_in = join(',', + grep /^\d+$/, $cgi->param('taxnum') + ); + push @where, "cust_main_county.taxnum IN ($taxnum_in)" + if $taxnum_in; + } -my $use_usage = $cgi->param('use_usage'); - -my $count_query; -if ( $cgi->param('pkg_tax') ) { - - $count_query = - "SELECT COUNT(*), - SUM( - ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 - END - ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 - END - ) - ) - "; - - push @where, "( ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) )", - "( tax != 'Y' OR tax IS NULL )"; - -} elsif ( $cgi->param('taxable') ) { - - my $setup_taxable = "( - CASE WHEN part_pkg.setuptax = 'Y' - THEN 0 - ELSE cust_bill_pkg.setup - END - )"; - - my $recur_taxable = "( - CASE WHEN part_pkg.recurtax = 'Y' - THEN 0 - ELSE cust_bill_pkg.recur - END - )"; - - my $exempt = "( - SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg - WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum - )"; - - $count_query = - "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )"; - - push @where, - #not tax-exempt package (setup or recur) - "( - ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL ) - AND cust_bill_pkg.setup > 0 ) - OR - ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL ) - AND cust_bill_pkg.recur > 0 ) - )", - #not a tax_exempt customer - "( tax != 'Y' OR tax IS NULL )", # assume this was intended? - #not covered in full by a monthly tax exemption (texas tax) - "0 < ( $setup_taxable + $recur_taxable - $exempt )"; - -} else { - - if ( $use_usage ) { - $count_query = "SELECT COUNT(*), "; - } else { - $count_query = "SELECT COUNT(DISTINCT billpkgnum), "; + # report group (itemdesc) + if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) { + my ( $group_op, $group_value ) = ( $1, $2 ); + if ( $group_op eq '=' ) { + #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%'); + push @where, 'itemdesc = '. dbh->quote($group_value); + } elsif ( $group_op eq '!=' ) { + push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )'; + } else { + die "guru meditation #00de: group_op $group_op\n"; + } } - if ( $use_usage eq 'recurring' ) { - $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - usage)"; - } elsif ( $use_usage eq 'usage' ) { - $count_query .= "SUM(usage)"; - } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) { - $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))"; - } elsif ( $cgi->param('iscredit') eq 'rate') { - $count_query .= "SUM( cust_credit_bill_pkg.amount )"; - } else { - $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)"; + # itemdesc, for some reason + if ( $cgi->param('itemdesc') ) { + if ( $cgi->param('itemdesc') eq 'Tax' ) { + push @where, "(itemdesc='Tax' OR itemdesc is null)"; + } else { + push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc')); + } } -} +} # nottax / istax -$join_cust = ' JOIN cust_bill USING ( invnum ) - LEFT JOIN cust_main USING ( custnum ) '; +# credit +if ( $cgi->param('credit') ) { -if ( $cgi->param('nottax') ) { + my $credit_sub; - $join_pkg .= ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN part_pkg AS override - ON pkgpart_override = override.pkgpart - LEFT JOIN cust_location - ON cust_location.locationnum = '. - FS::cust_pkg->tax_locationnum_sql; + if ( $cgi->param('istax') ) { + # then we need to group/join by billpkgtaxlocationnum, to get only the + # relevant part of partial taxes + my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount, + reason.reason as reason_text, access_user.username AS username_text, + billpkgtaxlocationnum, billpkgnum + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_credit USING (crednum) + LEFT JOIN reason USING (reasonnum) + LEFT JOIN access_user USING (usernum) + GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason, + access_user.username"; + + if ( $cgi->param('out') ) { + + # find credits that are applied to the line items, but not to + # a cust_bill_pkg_tax_location link + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit + USING (billpkgnum)"; + push @where, 'item_credit.billpkgtaxlocationnum IS NULL'; -} elsif ( $cgi->param('istax') ) { + } else { - #false laziness w/report_tax.cgi $taxfromwhere - if ( scalar( grep( /locationtaxid/, $cgi->param ) ) || - $cgi->param('iscredit') eq 'rate') { + # find credits that are applied to the CBPTL links that are + # considered "interesting" by the report criteria + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit + USING (billpkgtaxlocationnum)"; - # using tax_rate_location and friends - $join_pkg .= - ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '. - ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) '; + } - #} elsif ( $conf->exists('tax-pkg_address') ) { } else { - - # using cust_bill_pkg_tax_location to relate tax items to locations - # ...but for consolidated taxes we don't want to duplicate this - my $tax_item_location = '(SELECT DISTINCT billpkgnum, locationnum - FROM cust_bill_pkg_tax_location) AS tax_item_location'; - - $join_pkg .= " LEFT JOIN $tax_item_location USING ( billpkgnum ) - LEFT JOIN cust_location - ON tax_item_location.locationnum = - cust_location.locationnum "; - - #quelle kludge, somewhat false laziness w/report_tax.cgi - s/cust_pkg\.locationnum/tax_item_location.locationnum/g for @where; - } - - if ( $cgi->param('iscredit') ) { - $join_pkg .= ' JOIN cust_credit_bill_pkg USING ( billpkgnum'; - if ( $cgi->param('iscredit') eq 'rate' ) { - $join_pkg .= ', billpkgtaxratelocationnum )'; - } elsif ( $conf->exists('tax-pkg_address') ) { - $join_pkg .= ', billpkgtaxlocationnum )'; - push @where, "billpkgtaxratelocationnum IS NULL"; - } else { - $join_pkg .= ' )'; - push @where, "billpkgtaxratelocationnum IS NULL"; - } + # then only group by billpkgnum + my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount, + reason.reason as reason_text, access_user.username AS username_text, + billpkgnum + FROM cust_credit_bill_pkg + JOIN cust_credit_bill USING (creditbillnum) + JOIN cust_credit USING (crednum) + LEFT JOIN reason USING (reasonnum) + LEFT JOIN access_user USING (usernum) + GROUP BY billpkgnum, reason.reason, access_user.username"; + $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)"; } -} else { - - #die? - warn "neither nottax nor istax parameters specified"; - #same as before? - $join_pkg = ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) '; - -} - -my $where = ' WHERE '. join(' AND ', @where); - -if ($use_usage) { - $count_query .= - " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur, - ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail - WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum - ) AS usage FROM cust_bill_pkg $join_cust $join_pkg $where - ) AS countquery"; -} else { - $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where"; -} + push @where, 'item_credit.billpkgnum IS NOT NULL'; + push @select, 'item_credit.credit_amount', + 'item_credit.username_text', + 'item_credit.reason_text'; + push @peritem, 'credit_amount', 'username_text', 'reason_text'; + push @peritem_desc, 'Credited', 'By', 'Reason'; + push @total, 'SUM(credit_amount)'; + push @total_desc, "$money_char%.2f credited"; +} # if credit -push @select, 'part_pkg.pkg', - 'part_pkg.freq', - unless $cgi->param('istax'); +push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields(); -push @select, 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(); +my $where = join(' AND ', @where); +$where &&= "WHERE $where"; my $query = { 'table' => 'cust_bill_pkg', @@ -503,25 +557,31 @@ my $query = { 'hashref' => {}, 'select' => join(",\n", @select ), 'extra_sql' => $where, - 'order_by' => 'ORDER BY cust_bill._date, billpkgnum', + 'order_by' => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum', }; +my $count_query = + 'SELECT ' . join(',', @total) . + " FROM cust_bill_pkg $join_cust $join_pkg + $where"; + +shift @total_desc; #the first one is implicit + +@peritem_desc = map {emt($_)} @peritem_desc; +my @peritem_sub = map { + my $field = $_; + if ($field =~ /_text$/) { # kludge for credit reason/username fields + sub {$_[0]->get($field)}; + } else { + sub { sprintf($money_char.'%.2f', $_[0]->get($field)) } + } +} @peritem; +my @peritem_null = map { '' } @peritem; # placeholders +my $peritem_align = 'r' x scalar(@peritem); + my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ]; my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ]; -my $conf = new FS::Conf; -my $money_char = $conf->config('money_char') || '$'; - -my $owed_sub = sub { - $money_char . shift->get('owed') # owed_recur is not correct here -}; -my $payment_date_sub = sub { - #my $cust_bill_pkg = shift; - my @cust_pay = sort { $a->_date <=> $b->_date } - map $_->cust_bill_pay->cust_pay, - shift->cust_bill_pay_pkg('recur') #recur :/ - or return ''; - time2str('%b %d %Y', $cust_pay[-1]->_date ); -}; -warn $count_query; +warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n" + if $cgi->param('debug'); </%init> diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi index 3a5155a..1b767f8 100644 --- a/httemplate/search/cust_tax_exempt_pkg.cgi +++ b/httemplate/search/cust_tax_exempt_pkg.cgi @@ -103,7 +103,7 @@ my $join = " die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('View customer tax exemptions'); -my @where = (); +my @where = ("exempt_monthly = 'Y'"); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); if ( $beginning || $ending ) { @@ -121,6 +121,7 @@ if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { } if ( $cgi->param('out') ) { + # wtf? how would you ever get exemptions on a non-taxable package location? push @where, " 0 = ( @@ -151,6 +152,11 @@ if ( $cgi->param('out') ) { push @where, 'taxclass = '. dbh->quote( $cgi->param('taxclass') ) if $cgi->param('taxclass'); +} elsif ( $cgi->param('taxnum') ) { + + my $taxnum_in = join(',', grep /^\d+$/, $cgi->param('taxnum') ); + push @where, "taxnum IN ($taxnum_in)" if $taxnum_in; + } my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 2786f57..42a52d1 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -60,9 +60,9 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea % my $link = ''; % if ( $region->{'label'} eq $out ) { % $link = ';out=1'; -% } else { -% $link = ';'. $region->{'url_param'} -% if $region->{'url_param'}; +% } elsif ( $region->{'taxnums'} ) { +% # might be nicer to specify this as country:state:city +% $link = ';'.join(';', map { "taxnum=$_" } @{ $region->{'taxnums'} }); % } % % if ( $bgcolor eq $bgcolor1 ) { @@ -71,15 +71,12 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea % $bgcolor = $bgcolor1; % } % -% #my $diff = 0; % my $hicolor = $bgcolor; % unless ( $cgi->param('show_taxclasses') ) { % my $diff = abs( sprintf( '%.2f', $region->{'owed'} ) % - sprintf( '%.2f', $region->{'tax'} ) % ); % if ( $diff > 0.02 ) { -% # $hicolor = $hicolor eq '#eeeeee' ? '#eeee66' : '#ffff99'; -% #} elsif ( $diff ) { % $hicolor = $hicolor eq '#eeeeee' ? '#eeee99' : '#ffffcc'; % } % } @@ -94,16 +91,19 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <<%$td%>><% $region->{'label'} %></TD> <<%$td%> ALIGN="right"> <A HREF="<% $baselink. $link %>;nottax=1" - ><% &$money_sprintf( $region->{'total'} ) %></A> + ><% &$money_sprintf( $region->{'sales'} ) %></A> </TD> +% if ( $region->{'label'} eq $out ) { + <<%$td%> COLSPAN=12></TD> +% } else { #not $out <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD> <<%$td%> ALIGN="right"> - <A HREF="<% $baselink. $link %>;nottax=1;cust_tax=Y" + <A HREF="<% $baselink. $link %>;nottax=1;exempt_cust=Y" ><% &$money_sprintf( $region->{'exempt_cust'} ) %></A> </TD> <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD> <<%$td%> ALIGN="right"> - <A HREF="<% $baselink. $link %>;nottax=1;pkg_tax=Y" + <A HREF="<% $baselink. $link %>;nottax=1;exempt_pkg=Y" ><% &$money_sprintf( $region->{'exempt_pkg'} ) %></A> </TD> <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD> @@ -122,12 +122,24 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'owed'} ) %> </TD> - -% unless ( $cgi->param('show_taxclasses') ) { +% } # if !$out +% unless ( $cgi->param('show_taxclasses') ) { % my $invlink = $region->{'url_param_inv'} % ? ';'. $region->{'url_param_inv'} % : $link; +% if ( $region->{'label'} eq $out ) { + <<%$td%> ALIGN="right"> + <A HREF="<% $baselink. $invlink %>;istax=1" + ><% &$money_sprintf_nonzero( $region->{'tax'} ) %></A> + </TD> + <<%$td%>></TD> + <<%$td%> ALIGN="right"> + <A HREF="<% $creditlink. $invlink %>;istax=1" + ><% &$money_sprintf_nonzero( $region->{'credit'} ) %></A> + </TD> + <<%$td%> COLSPAN=2></TD> +% } else { #not $out <<%$tdh%> ALIGN="right"> <A HREF="<% $baselink. $invlink %>;istax=1" ><% &$money_sprintf( $region->{'tax'} ) %></A> @@ -141,7 +153,8 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> </TD> -% } +% } +% } # not $out </TR> % } @@ -190,6 +203,18 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <TR> <<%$td%>><% $region->{'label'} %></TD> +% if ( $region->{'label'} eq $out ) { + <<%$td%> ALIGN="right"> + <A HREF="<% $baselink. $invlink %>;istax=1" + ><% &$money_sprintf_nonzero( $region->{'tax'} ) %></A> + </TD> + <<%$td%>></TD> + <<%$td%> ALIGN="right"> + <A HREF="<% $creditlink. $invlink %>;istax=1" + ><% &$money_sprintf_nonzero( $region->{'credit'} ) %></A> + </TD> + <<%$td%> COLSPAN=2></TD> +% } else { #not $out <<%$td%> ALIGN="right"> <A HREF="<% $baselink. $link %>;istax=1" ><% &$money_sprintf( $region->{'tax'} ) %></A> @@ -204,70 +229,52 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> </TD> </TR> - -% } - -% if ( $bgcolor eq $bgcolor1 ) { -% $bgcolor = $bgcolor2; -% } else { -% $bgcolor = $bgcolor1; -% } -% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor"); - - <TR> - <<%$td%>>Total</TD> - <<%$td%> ALIGN="right"> - <A HREF="<% $baselink %>;istax=1" - ><% &$money_sprintf( $tot_tax ) %></A> - </TD> - <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD> - <<%$td%> ALIGN="right"> - <A HREF="<% $creditlink %>;istax=1" - ><% &$money_sprintf( $tot_credit ) %></A> - </TD> - <<%$td%>><FONT SIZE="+1"><B> = </B></FONT></TD> - <<%$td%> ALIGN="right"> - <% &$money_sprintf( $tot_tax - $tot_credit ) %> - </TD> - </TR> +% } # if $out +% } #foreach $region </TABLE> -% } +% } # if show_taxclasses <% include('/elements/footer.html') %> <%init> -my $DEBUG = $cgi->param('debug') || 0; - die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Financial reports'); +my $DEBUG = $cgi->param('debug') || 0; + my $conf = new FS::Conf; -my $user = getotaker; +my $out = 'Out of taxable region(s)'; + +my %label_opt = ( out => 1 ); #enable 'Out of Taxable Region' label +$label_opt{no_city} = 1 unless $cgi->param('show_cities'); +$label_opt{no_taxclass} = 1 unless $cgi->param('show_taxclasses'); my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi); my $join_cust = ' JOIN cust_bill USING ( invnum ) LEFT JOIN cust_main USING ( custnum ) '; + my $join_cust_pkg = $join_cust. ' LEFT JOIN cust_pkg USING ( pkgnum ) - LEFT JOIN part_pkg USING ( pkgpart ) - LEFT JOIN cust_location - ON ( cust_location.locationnum = ' . - FS::cust_pkg->tax_locationnum_sql . ' )'; + LEFT JOIN part_pkg USING ( pkgpart ) '; my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; -my $where = "WHERE _date >= $beginning AND _date <= $ending "; +# either or both of these can be used to link cust_bill_pkg to cust_main_county +my $pkg_tax = "SELECT SUM(amount) as tax_amount, invnum, taxnum, ". + "cust_bill_pkg_tax_location.pkgnum ". + "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ". + "GROUP BY billpkgnum, invnum, taxnum, cust_bill_pkg_tax_location.pkgnum"; -# this query will be run once per cust_main_county, -# or maybe once per country/state/city tuple, -# or maybe once per country/state...it's hard to say. -my ($location_sql, @base_param) = FS::cust_location->in_county_sql(param => 1); -$where .= " AND $location_sql "; +my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". + "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum"; + +my $where = "WHERE _date >= $beginning AND _date <= $ending "; +my $group = "GROUP BY cust_main_county.taxnum"; my $agentname = ''; if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { @@ -277,270 +284,188 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { $where .= ' AND cust_main.agentnum = '. $agent->agentnum; } -sub gotcust { - my $table = shift; - my $prefix = @_ ? shift : ''; - " - ( $table.district = cust_main_county.district - OR cust_main_county.district = '' - OR cust_main_county.district IS NULL ) - AND ( $table.${prefix}city = cust_main_county.city - OR cust_main_county.city = '' - OR cust_main_county.city IS NULL ) - AND ( $table.${prefix}county = cust_main_county.county - OR cust_main_county.county = '' - OR cust_main_county.county IS NULL ) - AND ( $table.${prefix}state = cust_main_county.state - OR cust_main_county.state = '' - OR cust_main_county.state IS NULL ) - AND ( $table.${prefix}country = cust_main_county.country ) - "; -} - -#non-parameterized form -my $location_in_county = FS::cust_location->in_county_sql; -my $gotcust = "WHERE EXISTS( - SELECT 1 FROM cust_location WHERE $location_in_county AND disabled IS NULL +my $nottax = 'cust_bill_pkg.pkgnum != 0'; + +# one query for each column of the report +# plus separate queries for the totals row +my (%sql, %all_sql); + +# general form +my $exempt = "SELECT cust_main_county.taxnum, SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust $where AND $nottax $group"; + +my $all_exempt = "SELECT SUM(exempt_charged) + FROM cust_main_county + JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust $where AND $nottax"; + +# sales to tax-exempt customers +$sql{exempt_cust} = $exempt; +$sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; +$all_sql{exempt_cust} = $all_exempt; +$all_sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + +# sales of tax-exempt packages +$sql{exempt_pkg} = $exempt; +$sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; +$all_sql{exempt_pkg} = $all_exempt; +$all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; + +# monthly per-customer exemptions +$sql{exempt_monthly} = $exempt; +$sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; +$all_sql{exempt_monthly} = $all_exempt; +$all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; + +# taxable sales +$sql{taxable} = "SELECT cust_main_county.taxnum, + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt + ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum + AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) + $join_cust $where AND $nottax $group"; + +# Here we're going to sum all line items that are taxable _at all_, +# under any tax. exempt_charged is the sum of all exemptions for a +# particular billpkgnum + taxnum; we take the taxnum that has the +# smallest sum of exemptions and subtract that from the charged amount. +$all_sql{taxable} = "SELECT + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(min_exempt, 0)) + FROM cust_bill_pkg + JOIN ( + SELECT invnum, pkgnum, MIN(exempt_charged) AS min_exempt + FROM ($pkg_tax) AS pkg_tax + JOIN cust_bill_pkg USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum) + GROUP BY invnum, pkgnum + ) AS pkg_is_taxable + USING (invnum, pkgnum) + $join_cust $where AND $nottax"; + # we don't join pkg_tax_exempt.taxnum here, because + +$sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted +$all_sql{taxable} =~ s/EXEMPT_WHERE//; + +# there isn't one for 'sales', because we calculate sales by adding up +# the taxable and exempt columns. + +# sum of billed tax: +# join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location +my $taxfrom = " FROM cust_bill_pkg + $join_cust + LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) + LEFT JOIN cust_main_county USING ( taxnum )"; + +my $istax = "cust_bill_pkg.pkgnum = 0"; +my $named_tax = "( + taxname = itemdesc + OR ( taxname IS NULL + AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' ) + ) )"; -my $out = 'Out of taxable region(s)'; -# these are actually tax labels, not regions -my %regions = (); - -# Phase 1: Taxable and exempt sales -# Collect for each cust_main_county, and assign to a bin based on label. -# Note that "label" includes city if show_cities is on, and taxclass if -# show_taxclasses is on. -foreach my $r ( qsearch({ 'table' => 'cust_main_county', - 'extra_sql' => $gotcust, - 'debug' => $DEBUG, - }) - ) -{ - warn $r->county. ' '. $r->state. ' '. $r->country. "\n" if $DEBUG > 1; - - # set up a %regions entry for this region's tax label - my $label = getlabel($r); - $regions{$label}->{'label'} = $label; - - $regions{$label}->{$_} = $r->$_() for (qw( county state country )); #taxname? - - my @url_param = qw( county state country taxname ); - push @url_param, 'city' if $cgi->param('show_cities') && $r->city(); - - $regions{$label}->{'url_param'} = - join(';', map "$_=".uri_escape($r->$_()), @url_param ); - - my @param = @base_param; - my $mywhere = $where; - - if ( $r->taxclass ) { - - $mywhere .= " AND taxclass = ? "; - push @param, 'taxclass'; - $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass); - #no, always# if $cgi->param('show_taxclasses'); - - $regions{$label}->{'taxclass'} = $r->taxclass; - - } else { - - # SQL for "taxclass doesn't match any other tax in the region" - my $same_sql = $r->sql_taxclass_sameregion; - $mywhere .= " AND $same_sql" if $same_sql; - - $regions{$label}->{'url_param'} .= ';taxclassNULL=1' - if $cgi->param('show_taxclasses') - || $same_sql; - - } - - # FROM cust_bill_pkg JOIN (whatever is needed to determine tax location) - # WHERE (matches tax location and agentnum and taxclass) - # takes parameters in @base_param, plus taxclass if there is one - my $fromwhere = "$from_join_cust_pkg $mywhere"; # AND payby != 'COMP' "; - - my $nottax = 'pkgnum != 0'; - - ## calculate total of sales (non-tax line items) for this region - - my $t_sql = - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax"; - my $t = scalar_sql($r, \@param, $t_sql); - $regions{$label}->{'total'} += $t; - - #$regions{$label}->{subtotals}->{$r->taxnum} = $t; #useful debug - - ## calculate customer-exemption for this region - - #false laziness -ish w/report_tax.cgi - my $cust_exempt; - if ( $r->taxname ) { - my $q_taxname = dbh->quote($r->taxname); - $cust_exempt = - "( tax = 'Y' - OR EXISTS ( SELECT 1 FROM cust_main_exemption - WHERE cust_main_exemption.custnum = cust_main.custnum - AND cust_main_exemption.taxname = $q_taxname - ) - ) - "; - } else { - $cust_exempt = " tax = 'Y' "; - } - - my $x_cust = scalar_sql($r, \@param, - "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) - $fromwhere AND $nottax AND $cust_exempt " - ); - - $regions{$label}->{'exempt_cust'} += $x_cust; - - ## calculate package-exemption for this region - - my $x_pkg = scalar_sql($r, \@param, - "SELECT SUM( - ( CASE WHEN part_pkg.setuptax = 'Y' - THEN cust_bill_pkg.setup - ELSE 0 - END - ) - + - ( CASE WHEN part_pkg.recurtax = 'Y' - THEN cust_bill_pkg.recur - ELSE 0 - END - ) - ) - $fromwhere - AND $nottax - AND ( - ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 ) - OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) - ) - AND ( tax != 'Y' OR tax IS NULL ) - " - ); - $regions{$label}->{'exempt_pkg'} += $x_pkg; - - ## calculate monthly exemption (texas tax) for this region - - # count up all the cust_tax_exempt_pkg records associated with - # the actual line items. - - my $x_monthly = scalar_sql($r, \@param, - "SELECT SUM(amount) - FROM cust_tax_exempt_pkg - JOIN cust_bill_pkg USING ( billpkgnum ) - $join_cust_pkg - $mywhere" - ); - $regions{$label}->{'exempt_monthly'} += $x_monthly; - - my $taxable = $t - $x_cust - $x_pkg - $x_monthly; - $regions{$label}->{'taxable'} += $taxable; - - $regions{$label}->{'owed'} += $taxable * ($r->tax/100); - - if ( defined($regions{$label}->{'rate'}) - && $regions{$label}->{'rate'} != $r->tax.'%' ) { - $regions{$label}->{'rate'} = 'variable'; - } else { - $regions{$label}->{'rate'} = $r->tax.'%'; - } +$sql{tax} = "SELECT cust_main_county.taxnum, + SUM(cust_bill_pkg_tax_location.amount) + $taxfrom + $where AND $istax AND $named_tax + $group"; + +$all_sql{tax} = "SELECT SUM(cust_bill_pkg.setup) + FROM cust_bill_pkg + $join_cust + $where AND $istax"; + +# sum of credits applied against billed tax +my $creditfrom = $taxfrom . + ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)'; +my $creditfromwhere = $where . + ' AND billpkgtaxratelocationnum IS NULL'; + +$sql{credit} = "SELECT cust_main_county.taxnum, + SUM(cust_credit_bill_pkg.amount) + $creditfrom + $creditfromwhere AND $istax AND $named_tax + $group"; + +$all_sql{credit} = "SELECT SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust + $where AND $istax"; + +my %data; +my %total = (owed => 0); +foreach my $k (keys(%sql)) { + my $stmt = $sql{$k}; + warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG; + my $sth = dbh->prepare($stmt); + # two columns => key/value + $sth->execute + or die "failed to execute $k query: ".$sth->errstr; + $data{$k} = +{ map { @$_ } @{ $sth->fetchall_arrayref([]) } }; + + warn "\n".$all_sql{$k}."\n" if $DEBUG; + $total{$k} = FS::Record->scalar_sql( $all_sql{$k} ); + warn Dumper($data{$k}) if $DEBUG > 1; } -warn Dumper(\%regions) if $DEBUG > 1; -# $regions{$label} now contains 'total', 'exempt_cust', 'exempt_pkg', -# 'exempt_monthly', summed over each set of regions with the same label. - -my $distinct = "country, state, county, city, district, - CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname"; -my $taxclass_distinct = - #a little bit unsure of this part... test? - #ah, it looks like it winds up being irrelevant as ->{'tax'} - # from $regions is not displayed when show_taxclasses is on - ( $cgi->param('show_taxclasses') - ? " CASE WHEN taxclass IS NULL THEN '' ELSE taxclass END " - : " '' " - )." AS taxclass"; - - -# Phase 2: invoiced/credited tax items -# Collect this data for each country/state/city/district/taxname(/taxclass). -my %qsearch = ( - 'select' => "DISTINCT $distinct, $taxclass_distinct", - 'table' => 'cust_main_county', - 'hashref' => {}, - 'extra_sql' => $gotcust, - 'debug' => $DEBUG, +# so $data{tax}, for example, is now a hash with one entry +# for each taxnum, containing the tax billed on that taxnum. + +# oddball cases: +# "out of taxable region" sales +my %out; +my $out_sales_sql = + "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM (cust_bill_pkg $join_cust) + LEFT JOIN ($pkg_tax) AS pkg_tax USING (invnum, pkgnum) + LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum) + $where AND $nottax + AND pkg_tax.taxnum IS NULL AND pkg_tax_exempt.taxnum IS NULL" +; + +$out_sales_sql =~ s/EXEMPT_WHERE//; + +$out{sales} = FS::Record->scalar_sql($out_sales_sql); + +# unlinked tax collected (for diagnostics) +my $out_tax_sql = + "SELECT SUM(cust_bill_pkg.setup) + FROM (cust_bill_pkg $join_cust) + LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) + $where AND $istax AND cust_bill_pkg_tax_location.billpkgnum IS NULL" +; +$out{tax} = FS::Record->scalar_sql($out_tax_sql); +# unlinked tax credited (for diagnostics) +my $out_credit_sql = + "SELECT SUM(cust_credit_bill_pkg.amount) + FROM cust_credit_bill_pkg + JOIN cust_bill_pkg USING (billpkgnum) + $join_cust + $where AND $istax AND cust_credit_bill_pkg.billpkgtaxlocationnum IS NULL" +; +$out{credit} = FS::Record->scalar_sql($out_credit_sql); + +# all sales +$total{sales} = FS::Record->scalar_sql( + "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur) + FROM cust_bill_pkg $join_cust $where AND $nottax" ); -# Join to cust_main the same as before (we need agentnum) -# but not to cust_pkg (because tax line items don't have a package) -# and then to cust_location via cust_bill_pkg_tax_location -my $taxfromwhere = "FROM cust_bill_pkg $join_cust - LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum ) - LEFT JOIN cust_location USING ( locationnum ) - "; -my $taxwhere = $where; - -my $creditfromwhere = $taxfromwhere. - " JOIN cust_credit_bill_pkg USING (billpkgnum, billpkgtaxlocationnum)"; - -$taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' "; -$creditfromwhere .= " $taxwhere AND billpkgtaxratelocationnum IS NULL"; #AND payby != 'COMP' "; - -#should i be a cust_main_county method or something -# yes. yes, you should. - -# $taxfromwhere: Most of a query to find cust_bill_pkg records linked to a -# customer matching a given state/county/city/district (and within the date -# range for the report). -# @base_param: A list of the fields from cust_main_county to use as parameters. - -# $_taxamount_sub: Takes a cust_main_county and returns the sum of taxes billed -# within the report period for all customers located in that county. If -# the cust_main_county has a taxname, limits to taxes with that name; otherwise -# includes all line items with pkgnum = 0 and description either 'Tax' or empty. - -my $_taxamount_sub = sub { - my $r = shift; - - #match itemdesc if necessary! - my $named_tax = - $r->taxname - ? 'AND itemdesc = '. dbh->quote($r->taxname) - : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - - my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ". - " $taxfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax"; - - scalar_sql($r, [ @base_param ], $sql ); -}; - -# $_creditamount_sub: As above, but returns the sum of credits applied - -my $_creditamount_sub = sub { - my $r = shift; - - #match itemdesc if necessary! - my $named_tax = - $r->taxname - ? 'AND itemdesc = '. dbh->quote($r->taxname) - : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )"; - - my $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ". - " $creditfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax"; - - scalar_sql($r, [ @base_param ], $sql ); -}; - #tax-report_groups filtering my($group_op, $group_value) = ( '', '' ); if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) { ( $group_op, $group_value ) = ( $1, $2 ); } -my $group_test = sub { +my $group_test = sub { # to be applied to a tax label my $label = shift; return 1 unless $group_op; #in case we get called inadvertantly if ( $label eq $out ) { #don't display "out of taxable region" in this case @@ -554,90 +479,83 @@ my $group_test = sub { } }; +# if show_taxclasses is on, %base_regions will contain the same data +# as %regions, but with taxclasses merged together (and ignoring report_group +# filtering). +my (%regions, %base_regions); my $tot_tax = 0; my $tot_credit = 0; -#foreach my $label ( keys %regions ) { -foreach my $r ( qsearch(\%qsearch) ) { - #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n"; +my @loc_params = qw(country state county); +push @loc_params, qw(city district) if $cgi->param('show_cities'); - my $label = getlabel($r); - if ( $group_op ) { - next unless &{$group_test}($label); +foreach my $r ( qsearch({ 'table' => 'cust_main_county', })) { + my $taxnum = $r->taxnum; + # set up a %regions entry for this region's tax label + my $label = $r->label(%label_opt); + next if $label eq $out; + $regions{$label} ||= { label => $label }; + + $regions{$label}->{$_} = $r->get($_) foreach @loc_params; + $regions{$label}->{taxnums} ||= []; + push @{ $regions{$label}->{taxnums} }, $r->taxnum; + + my %x; # keys are data items (like 'tax', 'exempt_cust', etc.) + foreach my $k (keys %data) { + next unless exists($data{$k}->{$taxnum}); + $x{$k} = $data{$k}->{$taxnum}; + $regions{$label}->{$k} += $x{$k}; + if ( $k eq 'taxable' or $k =~ /^exempt/ ) { + $regions{$label}->{'sales'} += $x{$k}; + } } - #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' "; - #my @param = @base_param; + my $owed = $data{'taxable'}->{$taxnum} * ($r->tax/100); + $regions{$label}->{'owed'} += $owed; + $total{'owed'} += $owed; - my $x = &{$_taxamount_sub}($r); - - $regions{$label}->{'tax'} += $x; - $tot_tax += $x unless $cgi->param('show_taxclasses'); - - ## calculate credit for this region - - $x = &{$_creditamount_sub}($r); - - $regions{$label}->{'credit'} += $x; - $tot_credit += $x unless $cgi->param('show_taxclasses'); - -} - -# Phase 3: Non-taxclassed totals for invoiced/credited tax -# (If show_taxclasses is not in use, this was phase 2, but it -# displays somewhere different.) -# Don't filter by report_groups. -my %base_regions = (); -if ( $cgi->param('show_taxclasses') ) { - - $qsearch{'select'} = "DISTINCT $distinct"; - foreach my $r ( qsearch(\%qsearch) ) { - - my $x = &{$_taxamount_sub}($r); - - my $base_label = getlabel($r, 'no_taxclass'=>1 ); - $base_regions{$base_label}->{'label'} = $base_label; - - $base_regions{$base_label}->{'url_param'} = - join(';', map "$_=". uri_escape($r->$_()), - qw( county state country taxname ) - ); - - $base_regions{$base_label}->{'tax'} += $x; - $tot_tax += $x; - - ## calculate credit for this region - - $x = &{$_creditamount_sub}($r); - - $base_regions{$base_label}->{'credit'} += $x; - $tot_credit += $x; + if ( defined($regions{$label}->{'rate'}) + && $regions{$label}->{'rate'} != $r->tax.'%' ) { + $regions{$label}->{'rate'} = 'variable'; + } else { + $regions{$label}->{'rate'} = $r->tax.'%'; + } + if ( $cgi->param('show_taxclasses') ) { + my $base_label = $r->label(%label_opt, 'no_taxclass' => 1); + $base_regions{$base_label} ||= + { + label => $base_label, + tax => 0, + credit => 0, + }; + $base_regions{$base_label}->{tax} += $x{tax}; + $base_regions{$base_label}->{credit} += $x{credit}; } } -my @regions = keys %regions; +my @regions = map { $_->{label} } + sort { + ($b eq $out) <=> ($a eq $out) + or $a->{country} cmp $b->{country} + or $a->{state} cmp $b->{state} + or $a->{county} cmp $b->{county} + or $a->{city} cmp $b->{city} + } + grep { $_->{sales} > 0 or $_->{tax} > 0 or $_->{credit} > 0 } + values %regions; #tax-report_groups filtering @regions = grep &{$group_test}($_), @regions if $group_op; #calculate totals -my( $total, $tot_taxable, $tot_owed ) = ( 0, 0, 0 ); -my( $exempt_cust, $exempt_pkg, $exempt_monthly, $tot_credit ) = ( 0, 0, 0, 0 ); my %taxclasses = (); my %county = (); my %state = (); my %country = (); -foreach (@regions) { - $total += $regions{$_}->{'total'}; - $tot_taxable += $regions{$_}->{'taxable'}; - $tot_owed += $regions{$_}->{'owed'}; - $exempt_cust += $regions{$_}->{'exempt_cust'}; - $exempt_pkg += $regions{$_}->{'exempt_pkg'}; - $exempt_monthly += $regions{$_}->{'exempt_monthly'}; - $tot_credit += $regions{$_}->{'credit'}; +foreach my $label (@regions) { $taxclasses{$regions{$_}->{'taxclass'}} = 1 if $regions{$_}->{'taxclass'}; $county{$regions{$_}->{'county'}} = 1; @@ -672,29 +590,27 @@ if ( $group_op ) { #ordering @regions = map $regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } + sort { $a cmp $b } @regions; my @base_regions = map $base_regions{$_}, - sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) } + sort { $a cmp $b } keys %base_regions; -#add total line -push @regions, { - 'label' => 'Total', - 'url_param' => $total_url_param, - 'url_param_inv' => $total_url_param_invoiced, - 'total' => $total, - 'exempt_cust' => $exempt_cust, - 'exempt_pkg' => $exempt_pkg, - 'exempt_monthly' => $exempt_monthly, - 'taxable' => $tot_taxable, - 'rate' => '', - 'owed' => $tot_owed, - 'tax' => $tot_tax, - 'credit' => $tot_credit, -}; +#add "Out of taxable" and total lines +%out = ( %out, + 'label' => $out, + 'rate' => '' +); +%total = ( %total, + 'label' => 'Total', + 'url_param' => $total_url_param, + 'url_param_inv' => $total_url_param_invoiced, + 'rate' => '', +); +push @regions, \%out, \%total; +push @base_regions, \%out, \%total; #-- @@ -702,69 +618,15 @@ my $money_char = $conf->config('money_char') || '$'; my $money_sprintf = sub { $money_char. sprintf('%.2f', shift ); }; - -sub getlabel { - my $r = shift; - my %opt = @_; - - my $label; - if ( - $r->tax == 0 - && ! scalar( qsearch('cust_main_county', { 'district'=> $r->district, - 'city' => $r->city, - 'county' => $r->county, - 'state' => $r->state, - 'country' => $r->country, - 'tax' => { op=>'>', value=>0 }, - } - ) - ) - - ) { - #kludge to avoid "will not stay shared" warning - my $out = 'Out of taxable region(s)'; - $label = $out; - } else { - $label = $r->country; - $label = $r->state.", $label" if $r->state; - $label = $r->county." county, $label" if $r->county; - $label = $r->city. ", $label" if $r->city && $cgi->param('show_cities'); - $label = "$label (". $r->taxclass. ")" - if $r->taxclass - && $cgi->param('show_taxclasses') - && ! $opt{'no_taxclass'}; - $label = $r->taxname. " ($label)" if $r->taxname; - } - return $label; -} - -#my %count_taxname = (); #cache -#sub count_taxname { -# my $taxname = shift; -# return $count_taxname{$taxname} if exists $count_taxname{$taxname}; -# my $sql = 'SELECT COUNT(*) FROM cust_main_county WHERE taxname = ?'; -# my $sth = dbh->prepare($sql) or die dbh->errstr; -# $sth->execute( $taxname ) -# or die "Unexpected error executing statement $sql: ". $sth->errstr; -# $count_taxname{$taxname} = $sth->fetchrow_arrayref->[0]; -#} - -#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up -#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; - $sth->fetchrow_arrayref->[0] || 0; -} +my $money_sprintf_nonzero = sub { + $_[0] == 0 ? '' : &$money_sprintf($_[0]) +}; my $dateagentlink = "begin=$beginning;end=$ending"; $dateagentlink .= ';agentnum='. $cgi->param('agentnum') if length($agentname); my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink"; my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink"; -my $creditlink = $p. "search/cust_credit_bill_pkg.html?$dateagentlink"; +my $creditlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;credit=1"; </%init> |