-<%= 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>
- <% } %>
+# %breakdown: short name => field identifier
+foreach ($cgi->param('breakdown')) {
+ if ( $_ eq 'taxclass' ) {
+ $breakdown{'taxclass'} = 'part_pkg.taxclass';
+ } elsif ( $_ eq 'pkgclass' ) {
+ $breakdown{'pkgclass'} = 'part_pkg.classnum';
+ } elsif ( $_ eq 'city' ) {
+ $breakdown{'city'} = 'cust_main_county.city';
+ $breakdown{'district'} = 'cust_main_county.district';
+ }
+}
+# always break these down
+$breakdown{'state'} = 'cust_main_county.state';
+$breakdown{'county'} = 'cust_main_county.county';
+
+my $join_cust = ' JOIN cust_bill USING ( invnum )
+ LEFT JOIN cust_main USING ( custnum ) ';
+
+my $join_cust_pkg = $join_cust.
+ ' LEFT JOIN cust_pkg USING ( pkgnum )
+ LEFT JOIN part_pkg USING ( pkgpart ) ';
+
+my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg ";
+
+# all queries MUST be linked to both cust_bill and cust_main_county
+
+# Either or both of these can be used to link cust_bill_pkg to
+# cust_main_county. This one links a taxed line item (billpkgnum) to a tax rate
+# (taxnum), and gives the amount of tax charged on that line item under that
+# rate (as tax_amount).
+my $pkg_tax = "SELECT SUM(amount) as tax_amount, taxnum, ".
+ "taxable_billpkgnum AS billpkgnum ".
+ "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ".
+ "GROUP BY taxable_billpkgnum, taxnum";
+
+# This one links a tax-exempted line item (billpkgnum) to a tax rate (taxnum),
+# and gives the amount of the tax exemption. EXEMPT_WHERE should be replaced
+# with a real WHERE clause to further limit the tax exemptions that will be
+# included.
+my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ".
+ "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum";
+
+my $where = "WHERE _date >= $beginning AND _date <= $ending ".
+ "AND COALESCE(cust_main_county.taxname,'Tax') = '$taxname' ".
+ "AND cust_main_county.country = '$country'";
+# SELECT/GROUP clauses for first-level queries
+my $select = "SELECT ";
+my $group = "GROUP BY ";
+foreach (qw(pkgclass taxclass state county city district)) {
+ if ( $breakdown{$_} ) {
+ $select .= "$breakdown{$_} AS $_, ";
+ $group .= "$breakdown{$_}, ";
+ } else {
+ $select .= "NULL AS $_, ";
+ }
+}
+$select .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, ";
+$group =~ s/, $//;
+
+# SELECT/GROUP clauses for second-level (totals) queries
+# breakdown by package class only, if anything
+my $select_all = "SELECT NULL AS pkgclass, ";
+my $group_all = "";
+if ( $breakdown{pkgclass} ) {
+ $select_all = "SELECT $breakdown{pkgclass} AS pkgclass, ";
+ $group_all = "GROUP BY $breakdown{pkgclass}";
+}
+$select_all .= "array_to_string(array_agg(DISTINCT(cust_main_county.taxnum)), ',') AS taxnums, ";
+
+my $agentname = '';
+if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+ my $agent = qsearchs('agent', { 'agentnum' => $1 } );
+ die "agent not found" unless $agent;
+ $agentname = $agent->agent;
+ $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
+}