From bee4494248009b7b998391bc68c5d10a31220057 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Wed, 5 Nov 2014 16:21:09 -0800 Subject: [PATCH] sales tax report: detail link and better accuracy for "out of taxable" calculation, #25935 --- FS/FS/Report/Tax.pm | 4 +- httemplate/search/cust_bill_pkg.cgi | 126 ++++++++++++++++++++---------------- httemplate/search/report_tax.cgi | 6 +- 3 files changed, 77 insertions(+), 59 deletions(-) diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index acf9f4428..713be02df 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -358,13 +358,13 @@ sub report_internal { SELECT 1 FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum) WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum - AND cust_main_county.taxname = '$taxname' + AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ) AND NOT EXISTS( SELECT 1 FROM cust_bill_pkg_tax_location JOIN cust_main_county USING (taxnum) WHERE cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum - AND cust_main_county.taxname = '$taxname' + AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ) "; warn "\nOUTSIDE:\n$sql_outside\n" if $DEBUG; diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 0ce141bbc..67f618cdb 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -422,63 +422,88 @@ if ( $cgi->param('nottax') ) { # 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. - my $exempt_sub; + + 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 - 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 $exempt_sub; + if ( @exempt_where or @tax_where or $cgi->param('taxable') ) { - $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) - if (@tax_where or @exempt_where); + # 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 .= ' GROUP BY billpkgnum'; + $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) + if (@tax_where or @exempt_where); - $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt - USING (billpkgnum)"; - - # process tax restrictions - unshift @tax_where, - 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum', - 'cust_main_county.tax > 0'; - } + $exempt_sub .= ' GROUP BY billpkgnum'; - 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); + $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt + USING (billpkgnum)"; + + # process tax restrictions + unshift @tax_where, + 'cust_bill_pkg_tax_location.taxable_billpkgnum = cust_bill_pkg.billpkgnum', + 'cust_main_county.tax > 0'; + } - # now do something with that - if ( @exempt_where ) { + 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); - 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"; + # now do something with that + if ( @exempt_where ) { - } elsif ( $cgi->param('taxable') ) { + 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"; - my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. - '- COALESCE(item_exempt.exempt_amount, 0)'; + } elsif ( $cgi->param('taxable') ) { - 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"; + my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. + '- COALESCE(item_exempt.exempt_amount, 0)'; - } elsif ( @tax_where ) { + 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"; - # union of taxable + all exempt_ cases - push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)"; + } elsif ( @tax_where ) { - } + # union of taxable + all exempt_ cases + push @where, "(EXISTS($tax_sub) OR item_exempt.billpkgnum IS NOT NULL)"; + + } + + } # handle all joins to cust_main_county # recur/usage separation if ( $cgi->param('usage') eq 'recurring' ) { @@ -524,18 +549,7 @@ if ( $cgi->param('nottax') ) { cust_bill_pkg.setup + cust_bill_pkg.recur) )'; - } 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'; - - # each billpkgnum should appear only once - $total[0] = 'COUNT(*)'; - $total[1] = 'SUM(cust_bill_pkg.setup)'; - - } else { # not locationtaxid or 'out'--the normal case + } else { # the internal-tax case $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum) diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index bf3b3d85c..3e9d7653a 100644 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -149,7 +149,11 @@ TD.rowhead { font-weight: bold; text-align: left; padding: 0px 3px } <% emt('Out of taxable region') %> - <% $money_sprintf->( $report->{outside } ) %> + + + <% $money_sprintf->( $report->{outside } ) %> + + -- 2.11.0