# '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',
#'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' => [],
#'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' => [],
#set up payment gateways if needed
'pay_batch' => [],
+
+ #flag monthly tax exemptions
+ 'cust_tax_exempt_pkg' => [],
;
\%hash;
# 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
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;
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;
use FS::cust_bill_pkg_tax_rate_location_void;
use FS::cust_tax_exempt_pkg_void;
-
$DEBUG = 0;
$me = '[FS::cust_bill_pkg]';
}
}
- 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";
}
}
}
-# 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
}
+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
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
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(
{
'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;
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 ) {
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} };
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'};
$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 } ||= [];
#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;
}
}
#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 } };
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' => '',
push @tax_line_items, new FS::cust_bill_pkg {
'pkgnum' => 0,
- 'setup' => $tax,
+ 'setup' => $tax_total,
'recur' => 0,
'sdate' => '',
'edate' => '',
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)
)
{
- 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;
} 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 ];
} #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
# 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;
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;
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
=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';
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
AND taxnum = ?
AND year = ?
AND month = ?
+ AND exempt_monthly = 'Y'
";
my $sth = dbh->prepare($sql) or do {
$dbh->rollback if $oldAutoCommit;
$sth->execute(
$custnum,
$self->taxnum,
- 1900+$year,
+ $year,
$mon,
) or do {
$dbh->rollback if $oldAutoCommit;
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;
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,
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 );
=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
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
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
$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;
--- /dev/null
+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;
+
--- /dev/null
+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;
+
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
--- /dev/null
+#!/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;
'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(),
},
#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 : '' }
),
],
#'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(),
'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",
"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',
'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>
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 ) {
}
if ( $cgi->param('out') ) {
+ # wtf? how would you ever get exemptions on a non-taxable package location?
push @where, "
0 = (
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) : '';
% 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 ) {
% $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';
% }
% }
<<%$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>
<<%$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>
<<%$tdh%> ALIGN="right">
<% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %>
</TD>
-% }
+% }
+% } # not $out
</TR>
% }
<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>
<% &$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+)$/ ) {
$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
}
};
+# 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;
#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;
#--
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>
projects / freeside.git / commitdiff