diff options
Diffstat (limited to 'httemplate')
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 58 |
1 files changed, 25 insertions, 33 deletions
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 9094c4f2c..0664197ed 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -174,8 +174,7 @@ Filtering parameters: 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, and subtract tax exemptions - and credits. + cust_bill_pkg_tax_location record exists. - credit: Limit to line items that received a credit application. The amount of the credit will also be shown. @@ -424,38 +423,36 @@ if ( $cgi->param('nottax') ) { # 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)'; + # 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 .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) + if (@tax_where or @exempt_where); - $exempt_sub .= ' GROUP BY billpkgnum'; + $exempt_sub .= ' GROUP BY billpkgnum'; - $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt - ON (cust_bill_pkg.billpkgnum = item_exempt.billpkgnum)"; - - my $credit_sub = 'SELECT SUM(amount) AS credit_amount, billpkgnum - FROM cust_credit_bill_pkg GROUP BY billpkgnum'; - - $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit - ON (cust_bill_pkg.billpkgnum = item_credit.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 taxable_billpkgnum AS billpkgnum + 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 taxable_billpkgnum"; + " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum"; $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax - ON (cust_bill_pkg.billpkgnum = item_tax.billpkgnum)" + ON (item_tax.invnum = cust_bill_pkg.invnum AND + item_tax.pkgnum = cust_bill_pkg.pkgnum)"; } # now do something with that @@ -468,28 +465,23 @@ if ( $cgi->param('nottax') ) { push @total, 'SUM(exempt_amount)'; push @total_desc, "$money_char%.2f tax-exempt"; - } elsif ( @tax_where or $cgi->param('taxable') ) { + } elsif ( $cgi->param('taxable') ) { my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. - '- COALESCE(item_exempt.exempt_amount, 0) '. - '- COALESCE(item_credit.credit_amount, 0)'; + '- COALESCE(item_exempt.exempt_amount, 0)'; - push @where, "(item_tax.billpkgnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)"; + push @where, 'item_tax.invnum IS NOT NULL'; push @select, "($taxable) AS taxable_amount"; push @peritem, 'taxable_amount'; push @peritem_desc, 'Taxable'; - - if ( $cgi->param('taxable') ) { - push @where, "($taxable) > 0"; - } else { - push @total, 'SUM('. - 'cust_bill_pkg.setup + cust_bill_pkg.recur '. - '- COALESCE(item_credit.credit_amount, 0) )'; - push @total_desc, "$money_char%.2f net sales"; - } - 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)'; + } } # handle all joins to cust_main_county |