+ # 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, "COALESCE(part_fee.taxclass, $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')";
+
+ } 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, 'COALESCE(cust_main_county.taxname, \'Tax\') = '.
+ dbh->quote($cgi->param('taxname'));
+ }
+
+ # 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;
+ }
+
+ # specific taxnums
+ if ( $cgi->param('taxnum') =~ /^([0-9,]+)$/ ) {
+ push @tax_where, "cust_main_county.taxnum IN ($1)";
+ }
+
+ # 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.
+
+ if ( $cgi->param('out') ) {
+ # separate from the rest, in that we're not going to join cust_main_county
+ # in the outer query
+
+ my @exclude = ( 'cust_tax_exempt_pkg.billpkgnum',
+ 'cust_bill_pkg_tax_location.taxable_billpkgnum'
+ );
+ foreach my $col (@exclude) {
+ my ($table) = split(/\./, $col);
+ my $this_where = 'WHERE ' . join(' AND ',
+ "$col = cust_bill_pkg.billpkgnum",
+ @tax_where
+ );
+
+ push @where,
+ "NOT EXISTS(SELECT 1 FROM $table
+ JOIN cust_main_county USING (taxnum)
+ $this_where
+ )";
+ }
+
+ } else {
+ # everything that returns things joined to a tax definition
+
+ my $exempt_sub;
+ if ( @exempt_where or @tax_where or $cgi->param('taxable') ) {
+
+ # 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)';
+
+ $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
+ if (@tax_where or @exempt_where);
+
+ $exempt_sub .= ' GROUP BY billpkgnum';
+
+ $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
+ USING (billpkgnum)";
+ }
+
+ if ( @tax_where or $cgi->param('taxable') ) {
+ # 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)";
+ }
+
+ # now do something with that
+ if ( @exempt_where ) {
+
+ 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";
+
+ } elsif ( $cgi->param('taxable') ) {
+
+ my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
+ '- COALESCE(item_exempt.exempt_amount, 0)';
+
+ 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";
+
+ } elsif ( @tax_where ) {
+ # union of taxable + all exempt_ cases
+ push @where,
+ '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';