-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);
-
-# general form
-my $exempt = "SELECT cust_main_county.taxnum, 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 $where AND $nottax $group";
-
-my $all_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 $where AND $nottax";
-
-# 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 cust_main_county.taxnum,
- 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 $where AND $nottax $group";
-
-# Here we're going to sum all line items that are taxable _at all_,
-# under any tax. exempt_charged is the sum of all exemptions for a
-# particular billpkgnum + taxnum; we take the taxnum that has the
-# smallest sum of exemptions and subtract that from the charged amount.
-$all_sql{taxable} = "SELECT
- SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(min_exempt, 0))
- FROM cust_bill_pkg
- JOIN (
- SELECT invnum, pkgnum, MIN(exempt_charged) AS min_exempt
- FROM ($pkg_tax) AS pkg_tax
- JOIN cust_bill_pkg USING (invnum, pkgnum)
- LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum)
- GROUP BY invnum, pkgnum
- ) AS pkg_is_taxable
- USING (invnum, pkgnum)
- $join_cust $where AND $nottax";
- # we don't join pkg_tax_exempt.taxnum here, because
-
-$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.
-
-# 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 )";
-
-my $istax = "cust_bill_pkg.pkgnum = 0";
-my $named_tax = "(
- taxname = itemdesc
- OR ( taxname IS NULL
- AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )
- )
-)";
-
-$sql{tax} = "SELECT cust_main_county.taxnum,
- SUM(cust_bill_pkg_tax_location.amount)
- $taxfrom
- $where AND $istax AND $named_tax
- $group";
-
-$all_sql{tax} = "SELECT SUM(cust_bill_pkg.setup)
- FROM cust_bill_pkg
- $join_cust
- $where AND $istax";
-
-# sum of credits applied against billed tax
-my $creditfrom = $taxfrom .
- ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)';
-my $creditfromwhere = $where .
- ' AND billpkgtaxratelocationnum IS NULL';
-
-$sql{credit} = "SELECT cust_main_county.taxnum,
- SUM(cust_credit_bill_pkg.amount)
- $creditfrom
- $creditfromwhere AND $istax AND $named_tax
- $group";
-
-$all_sql{credit} = "SELECT SUM(cust_credit_bill_pkg.amount)
- FROM cust_credit_bill_pkg
- JOIN cust_bill_pkg USING (billpkgnum)
- $join_cust
- $where AND $istax";
-
-my %data;
-my %total = (owed => 0);
-foreach my $k (keys(%sql)) {
- my $stmt = $sql{$k};
- warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG;
- my $sth = dbh->prepare($stmt);
- # two columns => key/value
- $sth->execute
- or die "failed to execute $k query: ".$sth->errstr;
- $data{$k} = +{ map { @$_ } @{ $sth->fetchall_arrayref([]) } };
-
- warn "\n".$all_sql{$k}."\n" if $DEBUG;
- $total{$k} = FS::Record->scalar_sql( $all_sql{$k} );
- warn Dumper($data{$k}) if $DEBUG > 1;