From 5b420b40e5c62e0d61b12e206b931cb2af3eff96 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Wed, 5 Nov 2014 15:27:46 -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 | 182 ++++++++++++++++++------------------ httemplate/search/report_tax.cgi | 6 +- 3 files changed, 100 insertions(+), 92 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 c254b2dee..0664197ed 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -356,11 +356,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) @@ -401,75 +398,93 @@ 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 + ); - 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)'; + 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 - $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) - if (@tax_where or @exempt_where); + my $exempt_sub; + if ( @exempt_where or @tax_where or $cgi->param('taxable') ) { - $exempt_sub .= ' GROUP BY billpkgnum'; + # 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)'; - $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)"; - } + $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where) + if (@tax_where or @exempt_where); - # now do something with that - if ( @exempt_where ) { + $exempt_sub .= ' GROUP BY billpkgnum'; - 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"; + $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)"; + } - } elsif ( $cgi->param('taxable') ) { + # now do something with that + if ( @exempt_where ) { - my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. - '- COALESCE(item_exempt.exempt_amount, 0)'; + 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"; - 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 ( $cgi->param('taxable') ) { - } elsif ( $cgi->param('out') ) { - - push @where, 'item_tax.invnum IS NULL', - 'item_exempt.billpkgnum IS NULL'; + my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '. + '- COALESCE(item_exempt.exempt_amount, 0)'; - } elsif ( @tax_where ) { + 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"; - # union of taxable + all exempt_ cases - push @where, - '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT 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)'; - } + } + + } # handle all joins to cust_main_county # recur/usage separation if ( $cgi->param('usage') eq 'recurring' ) { @@ -515,18 +530,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) @@ -536,26 +540,26 @@ 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)'; - } - # taxclass - if ( $cgi->param('taxclassNULL') ) { - push @where, 'cust_main_county.taxclass IS NULL'; - } + # taxclass + if ( $cgi->param('taxclassNULL') ) { + push @where, 'cust_main_county.taxclass IS NULL'; + } - # 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')); - } + # 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')); + } - # specific taxnums - if ( $cgi->param('taxnum') =~ /^([0-9,]+)$/ ) { - push @where, "cust_main_county.taxnum IN ($1)"; - } + # specific taxnums + if ( $cgi->param('taxnum') =~ /^([0-9,]+)$/ ) { + push @where, "cust_main_county.taxnum IN ($1)"; + } + } # the normal case # report group (itemdesc) if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) { 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