From dde41fbab51d693dda80977a724ee722059d92f7 Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Mon, 5 Jan 2015 15:34:36 -0800 Subject: [PATCH] tax report fix for monthly exemptions + credits, #27698 --- FS/FS/Report/Tax.pm | 37 ++++++++++++++-------- httemplate/search/cust_bill_pkg.cgi | 52 ++++++++++++++++++++----------- httemplate/search/cust_tax_exempt_pkg.cgi | 4 +++ 3 files changed, 62 insertions(+), 31 deletions(-) diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index f53be5d22..f3f441d21 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -88,12 +88,19 @@ sub report_internal { "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ". "GROUP BY taxable_billpkgnum, taxnum"; - # This one links a tax-exempted line item (billpkgnum) to a tax rate (taxnum), - # and gives the amount of the tax exemption. EXEMPT_WHERE should be replaced - # with a real WHERE clause to further limit the tax exemptions that will be - # included. + # This one links a tax-exempted line item (billpkgnum) to a tax rate + # (taxnum), and gives the amount of the tax exemption. EXEMPT_WHERE must + # be replaced with an expression to further limit the tax exemptions + # that will be included, or "TRUE" to not limit them. + # + # Note that tax exemptions with non-null creditbillpkgnum are always + # excluded. Those are "negative exemptions" created by crediting a sale + # that had received an exemption. my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". - "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum"; + "FROM cust_tax_exempt_pkg WHERE + ( EXEMPT_WHERE ) + AND cust_tax_exempt_pkg.creditbillpkgnum IS NULL + GROUP BY billpkgnum, taxnum"; my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending ". "AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ". @@ -161,21 +168,21 @@ sub report_internal { # sales to tax-exempt customers $sql{exempt_cust} = $exempt; - $sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + $sql{exempt_cust} =~ s/EXEMPT_WHERE/exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; $all_sql{exempt_cust} = $all_exempt; - $all_sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; + $all_sql{exempt_cust} =~ s/EXEMPT_WHERE/exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/; # sales of tax-exempt packages $sql{exempt_pkg} = $exempt; - $sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; + $sql{exempt_pkg} =~ s/EXEMPT_WHERE/exempt_setup = 'Y' OR exempt_recur = 'Y'/; $all_sql{exempt_pkg} = $all_exempt; - $all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/; + $all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/exempt_setup = 'Y' OR exempt_recur = 'Y'/; # monthly per-customer exemptions $sql{exempt_monthly} = $exempt; - $sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; + $sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; $all_sql{exempt_monthly} = $all_exempt; - $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; + $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/exempt_monthly = 'Y'/; # taxable sales $sql{taxable} = "$select @@ -200,8 +207,8 @@ sub report_internal { $join_cust_pkg $where AND $nottax $group_all"; - $sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted - $all_sql{taxable} =~ s/EXEMPT_WHERE//; + $sql{taxable} =~ s/EXEMPT_WHERE/TRUE/; # unrestricted + $all_sql{taxable} =~ s/EXEMPT_WHERE/TRUE/; # estimated tax (taxable * rate) $sql{estimated} = "$select @@ -230,6 +237,9 @@ sub report_internal { $join_cust_pkg $where AND $nottax $group_all"; + $sql{estimated} =~ s/EXEMPT_WHERE/TRUE/; # unrestricted + $all_sql{estimated} =~ s/EXEMPT_WHERE/TRUE/; + # there isn't one for 'sales', because we calculate sales by adding up # the taxable and exempt columns. @@ -361,6 +371,7 @@ sub report_internal { JOIN cust_main_county USING (taxnum) WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' + AND cust_tax_exempt_pkg.creditbillpkgnum IS NULL ) AND NOT EXISTS( SELECT 1 FROM cust_bill_pkg_tax_location diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 0664197ed..f4febdbf2 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -398,6 +398,8 @@ 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. + # Always exclude cust_tax_exempt_pkg records with non-NULL creditbillpkgnum. + if ( $cgi->param('out') ) { # separate from the rest, in that we're not going to join cust_main_county # in the outer query @@ -425,12 +427,13 @@ if ( $cgi->param('nottax') ) { my $exempt_sub; if ( @exempt_where or @tax_where or $cgi->param('taxable') ) { + push @exempt_where, "cust_tax_exempt_pkg.creditbillpkgnum IS NULL"; + # 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); $exempt_sub .= ' GROUP BY billpkgnum'; @@ -443,44 +446,45 @@ if ( $cgi->param('nottax') ) { unshift @tax_where, 'cust_main_county.tax > 0'; - my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum + my $tax_sub = "SELECT cust_bill_pkg_tax_location.taxable_billpkgnum 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"; + " GROUP BY taxable_billpkgnum"; $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)"; + ON (item_tax.taxable_billpkgnum = cust_bill_pkg.billpkgnum) + "; } # 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') ) { + if ( $cgi->param('taxable') ) { + # taxable query: needs sale amount - exempt amount 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 @where, 'item_tax.taxable_billpkgnum 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('exempt_cust') or $cgi->param('exempt_pkg') ) { + + 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 ( @tax_where ) { # union of taxable + all exempt_ cases push @where, - '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)'; + '(item_tax.taxable_billpkgnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)'; } @@ -583,6 +587,18 @@ if ( $cgi->param('nottax') ) { } } + # classnum (of underlying package) + # not specified: all classes + # 0: empty class + # N: classnum + if ( grep { $_ eq 'classnum' } $cgi->param ) { + my @classnums = grep /^\d+$/, $cgi->param('classnum'); + push @where, "COALESCE(part_fee.classnum, $part_pkg.classnum, 0) IN ( ". + join(',', @classnums ). + ' )' + if @classnums; + } + } # nottax / istax diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi index 267c9383a..41b7b9192 100644 --- a/httemplate/search/cust_tax_exempt_pkg.cgi +++ b/httemplate/search/cust_tax_exempt_pkg.cgi @@ -163,6 +163,10 @@ if ( $cgi->param('out') ) { } +# no reason ever to show the negative exemptions created by credits. +# they'll just confuse people. +push @where, "creditbillpkgnum IS NULL"; + my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : ''; my $count_query = "SELECT COUNT(*), SUM(amount)". -- 2.11.0