-my $gotcust = "
- WHERE 0 < ( SELECT COUNT(*) FROM cust_main
-";
-if ( $conf->exists('tax-ship_address') ) {
-
- $gotcust .= "
- WHERE
-
- ( cust_main_county.country = cust_main.country
- OR cust_main_county.country = cust_main.ship_country
- )
-
- AND
-
- (
-
- ( ( ship_last IS NULL OR ship_last = '' )
- AND ( cust_main_county.country = cust_main.country )
- AND ( cust_main_county.state = cust_main.state
- OR cust_main_county.state = ''
- OR cust_main_county.state IS NULL )
- AND ( cust_main_county.county = cust_main.county
- OR cust_main_county.county = ''
- OR cust_main_county.county IS NULL )
- )
-
- OR
-
- ( ship_last IS NOT NULL AND ship_last != ''
- AND ( cust_main_county.country = cust_main.ship_country )
- AND ( cust_main_county.state = cust_main.ship_state
- OR cust_main_county.state = ''
- OR cust_main_county.state IS NULL )
- AND ( cust_main_county.county = cust_main.ship_county
- OR cust_main_county.county = ''
- OR cust_main_county.county IS NULL )
- )
-
- )
-
- LIMIT 1
- )
- ";
-
-} else {
-
- $gotcust .= "
- WHERE ( cust_main.county = cust_main_county.county
- OR cust_main_county.county = ''
- OR cust_main_county.county IS NULL )
- AND ( cust_main.state = cust_main_county.state
- OR cust_main_county.state = ''
- OR cust_main_county.state IS NULL )
- AND ( cust_main.country = cust_main_county.country )
- LIMIT 1
- )
- ";
-
+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;