-%>
-
-<%= header( "Sales Tax Report - $pbeginning through ".($pending||'now'),
- menubar( 'Main Menu'=>$p, ) ) %>
-<%= table() %>
- <TR>
- <TH ROWSPAN=2></TH>
- <TH COLSPAN=3>Sales</TH>
- <TH ROWSPAN=2>Rate</TH>
- <TH ROWSPAN=2>Tax owed</TH>
- <TH ROWSPAN=2>Tax invoiced</TH>
- </TR>
- <TR>
- <TH>Total</TH>
- <TH>Non-taxable</TH>
- <TH>Taxable</TH>
- </TR>
- <% foreach my $region ( @regions ) { %>
- <TR>
- <TD><%= $region->{'label'} %></TD>
- <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'total'} ) %></TD>
- <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'exempt'} ) %></TD>
- <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'taxable'} ) %></TD>
- <TD ALIGN="right"><%= $region->{'rate'} %></TD>
- <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'owed'} ) %></TD>
- <TD ALIGN="right">$<%= sprintf('%.2f', $region->{'tax'} ) %></TD>
- </TR>
- <% } %>
-
-</TABLE>
-
-<% if ( $monthly_exempt_warning ) { %>
- <BR>
- Partial-month tax reports (except for current month) may not be correct due
- to month-granularity tax exemption (usually "texas tax"). For an accurate
- report, start on the first of a month and end on the last day of a month (or
- leave blank for to now).
-<% } %>
+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)";
+}