improved tax report, #23349

1 files changed, 31 insertions, 52 deletions

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index fc74b542e..d86641de3 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -144,8 +144,8 @@ Filtering parameters:
 
 - 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.
+  with "istax" it's the tax charges themselves.  Can be a comma-separated
+  list 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;
@@ -333,7 +333,7 @@ if ( $cgi->param('nottax') ) {
   # 0: empty class
   # N: classnum
   if ( grep { $_ eq 'classnum' } $cgi->param ) {
-    my @classnums = grep /^\d+$/, $cgi->param('classnum');
+    my @classnums = grep /^\d*$/, $cgi->param('classnum');
     push @where, "COALESCE($part_pkg.classnum, 0) IN ( ".
                      join(',', @classnums ).
                  ' )'
@@ -373,11 +373,8 @@ if ( $cgi->param('nottax') ) {
   # we don't handle exempt_monthly here
   
   if ( $cgi->param('taxname') ) { # specific taxname
-      push @tax_where, 'cust_main_county.taxname = '.
+      push @tax_where, "COALESCE(cust_main_county.taxname, 'Tax') = ".
                         dbh->quote($cgi->param('taxname'));
-  } elsif ( $cgi->param('taxnameNULL') ) {
-      push @tax_where, 'cust_main_county.taxname IS NULL OR '.
-                       'cust_main_county.taxname = \'Tax\'';
   }
 
   # country:state:county:city:district (may be repeated)
@@ -405,12 +402,8 @@ if ( $cgi->param('nottax') ) {
   }
 
   # 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;
+  if ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
+    push @tax_where, "cust_main_county.taxnum IN ($1)";
   }
 
   # If we're showing exempt items, we need to find those with 
@@ -440,22 +433,16 @@ if ( $cgi->param('nottax') ) {
     USING (billpkgnum)";
   }
  
-  if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) { 
-    # process tax restrictions
-    unshift @tax_where,
-      'cust_main_county.tax > 0';
+  # process tax restrictions
+  unshift @tax_where,
+    'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum',
+    'cust_main_county.tax > 0';
 
-    my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
+  my $tax_sub = "SELECT 1
     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)";
-  }
+    WHERE ". join(' AND ', @tax_where);
 
   # now do something with that
   if ( @exempt_where ) {
@@ -472,23 +459,17 @@ if ( $cgi->param('nottax') ) {
     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 @where,    "EXISTS($tax_sub)";
     push @peritem,  'taxable_amount';
     push @peritem_desc, 'Taxable';
     push @total,    "SUM($taxable)";
     push @total_desc, "$money_char%.2f taxable";
 
-  } elsif ( $cgi->param('out') ) {
-  
-    push @where,    'item_tax.invnum IS NULL',
-                    'item_exempt.billpkgnum IS NULL';
-
   } elsif ( @tax_where ) {
 
     # union of taxable + all exempt_ cases
-    push @where,
-      '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
+    push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)";
 
   }
 
@@ -555,6 +536,21 @@ if ( $cgi->param('nottax') ) {
     # 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)';
+
+    # package classnum
+    if ( grep { $_ eq 'classnum' } $cgi->param ) {
+      my @classnums = grep /^\d*$/, $cgi->param('classnum');
+      $join_pkg .= '
+        JOIN cust_pkg AS taxed_pkg 
+          ON (cust_bill_pkg_tax_location.pkgnum = taxed_pkg.pkgnum)
+        JOIN part_pkg AS taxed_part_pkg
+          ON (taxed_pkg.pkgpart = taxed_part_pkg.pkgpart)
+      ';
+      push @where, "COALESCE(taxed_part_pkg.classnum, 0) IN ( ".
+                       join(',', @classnums ).
+                   ' )'
+        if @classnums;
+    }
   }
 
   # taxclass
@@ -572,25 +568,8 @@ if ( $cgi->param('nottax') ) {
   }
 
   # 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;
-  }
-
-  # 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 ( $cgi->param('taxnum') =~ /^([\d,]+)$/) {
+    push @where, "cust_main_county.taxnum IN ($1)";
   }
 
   # itemdesc, for some reason