From cc70bf34df1b1306f9a41295fa893f903737d69c Mon Sep 17 00:00:00 2001 From: Mark Wells Date: Tue, 9 Dec 2014 18:52:21 -0600 Subject: [PATCH] Hold off on tax report change until I'm sure. This reverts commit e5734c42c1a30f0bc0798adac0ef1a728ac17a98. --- FS/FS/Report/Tax.pm | 79 +++++++++++-------------------------- httemplate/search/cust_bill_pkg.cgi | 58 ++++++++++++--------------- 2 files changed, 49 insertions(+), 88 deletions(-) diff --git a/FS/FS/Report/Tax.pm b/FS/FS/Report/Tax.pm index 43337a621..713be02df 100644 --- a/FS/FS/Report/Tax.pm +++ b/FS/FS/Report/Tax.pm @@ -95,10 +95,6 @@ sub report_internal { my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ". "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum"; - # This just calculates the sum of credit applications to a line item. - my $pkg_credited = "SELECT SUM(amount) AS credited, billpkgnum ". - "FROM cust_credit_bill_pkg GROUP BY billpkgnum"; - my $where = "WHERE cust_bill._date >= $beginning AND cust_bill._date <= $ending ". "AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ". "AND cust_main_county.country = '$country'"; @@ -180,40 +176,25 @@ sub report_internal { $all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/; # taxable sales - # (sale - exemptions - credits, except not negative) $sql{taxable} = "$select - SUM( - cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - ) - FROM cust_bill_pkg - LEFT JOIN ($pkg_tax) AS pkg_tax - ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) - LEFT JOIN ($pkg_credited) AS pkg_credited - ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) - LEFT JOIN cust_main_county - ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) + ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum + AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) $join_cust_pkg $where AND $nottax $group"; $all_sql{taxable} = "$select_all - SUM( - cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - ) - FROM cust_bill_pkg - LEFT JOIN ($pkg_tax) AS pkg_tax - ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) + SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) - LEFT JOIN ($pkg_credited) AS pkg_credited - ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) - LEFT JOIN cust_main_county - ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) + ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum + AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) $join_cust_pkg $where AND $nottax $group_all"; @@ -223,39 +204,27 @@ sub report_internal { # estimated tax (taxable * rate) $sql{estimated} = "$select SUM(cust_main_county.tax / 100 * - ( cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - ) + (cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) ) - FROM cust_bill_pkg - LEFT JOIN ($pkg_tax) AS pkg_tax - ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) - LEFT JOIN ($pkg_credited) AS pkg_credited - ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) - LEFT JOIN cust_main_county - ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) + ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum + AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) $join_cust_pkg $where AND $nottax $group"; $all_sql{estimated} = "$select_all SUM(cust_main_county.tax / 100 * - ( cust_bill_pkg.setup + cust_bill_pkg.recur - - COALESCE(exempt_charged, 0) - - COALESCE(credited, 0) - ) + (cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0)) ) - FROM cust_bill_pkg - LEFT JOIN ($pkg_tax) AS pkg_tax - ON (cust_bill_pkg.billpkgnum = pkg_tax.billpkgnum) + FROM cust_main_county + JOIN ($pkg_tax) AS pkg_tax USING (taxnum) + JOIN cust_bill_pkg USING (billpkgnum) LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt - ON (cust_bill_pkg.billpkgnum = pkg_tax_exempt.billpkgnum) - LEFT JOIN ($pkg_credited) AS pkg_credited - ON (cust_bill_pkg.billpkgnum = pkg_credited.billpkgnum) - LEFT JOIN cust_main_county - ON (COALESCE(pkg_tax.taxnum, pkg_tax_exempt.taxnum) = cust_main_county.taxnum) + ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum + AND pkg_tax_exempt.taxnum = cust_main_county.taxnum) $join_cust_pkg $where AND $nottax $group_all"; 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 -- 2.11.0