-my $nottax = 'cust_bill_pkg.pkgnum != 0';
-
-# one query for each column of the report
-# plus separate queries for the totals row
-my (%sql, %all_sql);
-
-# SALES QUERIES (taxable sales, all types of exempt sales)
-# -------------
-
-# general form
-my $exempt = "$select SUM(exempt_charged)
- FROM cust_main_county
- JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
- USING (taxnum)
- JOIN cust_bill_pkg USING (billpkgnum)
- $join_cust_pkg $where AND $nottax
- $group";
-
-my $all_exempt = "$select_all SUM(exempt_charged)
- FROM cust_main_county
- JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
- USING (taxnum)
- JOIN cust_bill_pkg USING (billpkgnum)
- $join_cust_pkg $where AND $nottax
- $group_all";
-
-# sales to tax-exempt customers
-$sql{exempt_cust} = $exempt;
-$sql{exempt_cust} =~ s/EXEMPT_WHERE/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'/;
-
-# sales of tax-exempt packages
-$sql{exempt_pkg} = $exempt;
-$sql{exempt_pkg} =~ s/EXEMPT_WHERE/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'/;
-
-# monthly per-customer exemptions
-$sql{exempt_monthly} = $exempt;
-$sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
-$all_sql{exempt_monthly} = $all_exempt;
-$all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
-
-# taxable sales
-$sql{taxable} = "$select
- 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 (invnum, pkgnum)
- LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
- 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))
- FROM cust_main_county
- JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
- JOIN cust_bill_pkg USING (invnum, pkgnum)
- LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
- 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} =~ s/EXEMPT_WHERE//; # unrestricted
-$all_sql{taxable} =~ s/EXEMPT_WHERE//;
-
-# there isn't one for 'sales', because we calculate sales by adding up
-# the taxable and exempt columns.
-
-# TAX QUERIES (billed tax, credited tax)
-# -----------
-
-# sum of billed tax:
-# join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location
-my $taxfrom = " FROM cust_bill_pkg
- $join_cust
- LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
- LEFT JOIN cust_main_county USING ( taxnum )";
-
-if ( $breakdown{pkgclass} ) {
- # If we're not grouping by package class, this is unnecessary, and
- # probably really expensive.
- $taxfrom .= "
- LEFT JOIN cust_bill_pkg AS taxable
- ON (cust_bill_pkg_tax_location.taxable_billpkgnum = taxable.billpkgnum)
- LEFT JOIN cust_pkg ON (taxable.pkgnum = cust_pkg.pkgnum)
- LEFT JOIN part_pkg USING (pkgpart)";
-}