)
)
%>
+<TD ALIGN="right">
+Download full results<BR>
+as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel spreadsheet</A>
+</TD>
<% include('/elements/table-grid.html') %>
<TR>
- <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
<TH CLASS="grid" BGCOLOR="#cccccc" COLSPAN=9>Sales</TH>
- <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Rate</TH>
- <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax owed</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Rate</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax owed</TH>
% unless ( $cgi->param('show_taxclasses') ) {
- <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax invoiced</TH>
- <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax credited</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax invoiced</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax credited</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=3>Tax collected</TH>
% }
</TR>
<TR>
- <TH CLASS="grid" BGCOLOR="#cccccc">Total</TH>
- <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt customer)</FONT></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(tax-exempt package)</FONT></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc">Non-taxable<BR><FONT SIZE=-1>(monthly exemption)</FONT></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
- <TH CLASS="grid" BGCOLOR="#cccccc">Taxable</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Total</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=1>Non-taxable</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Taxable</TH>
+ </TR>
+
+ <TR>
+ <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(tax-exempt customer)</FONT></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(tax-exempt package)</FONT></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"><FONT SIZE=-1>(monthly exemption)</FONT></TH>
</TR>
% my $bgcolor1 = '#eeeeee';
<A HREF="<% $baselink. $invlink %>;istax=1"
><% &$money_sprintf( $region->{'tax'} ) %></A>
</TD>
+ <<%$tdh%>><FONT SIZE="+1"><B> - </B></FONT></TD>
<<%$tdh%> ALIGN="right">
- <A HREF="<% $baselink. $invlink %>;istax=1;iscredit=1"
+ <A HREF="<% $creditlink. $invlink %>;istax=1"
><% &$money_sprintf( $region->{'credit'} ) %></A>
</TD>
+ <<%$tdh%>><FONT SIZE="+1"><B> = </B></FONT></TD>
+ <<%$tdh%> ALIGN="right">
+ <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %>
+ </TD>
% }
</TR>
<TR>
<TH CLASS="grid" BGCOLOR="#cccccc"></TH>
<TH CLASS="grid" BGCOLOR="#cccccc">Tax invoiced</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
<TH CLASS="grid" BGCOLOR="#cccccc">Tax credited</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc"></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc">Tax collected</TH>
</TR>
% #some false laziness w/above
% $bgcolor = $bgcolor1;
% }
% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor");
+% my $tdh = qq(TD CLASS="grid" BGCOLOR="$bgcolor");
+%
+% #?
+% my $invlink = $region->{'url_param_inv'}
+% ? ';'. $region->{'url_param_inv'}
+% : $link;
<TR>
<<%$td%>><% $region->{'label'} %></TD>
<A HREF="<% $baselink. $link %>;istax=1"
><% &$money_sprintf( $region->{'tax'} ) %></A>
</TD>
+ <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
+ <<%$tdh%> ALIGN="right">
+ <A HREF="<% $creditlink. $invlink %>;istax=1"
+ ><% &$money_sprintf( $region->{'credit'} ) %></A>
+ </TD>
+ <<%$td%>><FONT SIZE="+1"><B> = </B></FONT></TD>
+ <<%$tdh%> ALIGN="right">
+ <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %>
+ </TD>
</TR>
% }
<A HREF="<% $baselink %>;istax=1"
><% &$money_sprintf( $tot_tax ) %></A>
</TD>
+ <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
+ <<%$td%> ALIGN="right">
+ <A HREF="<% $creditlink %>;istax=1"
+ ><% &$money_sprintf( $tot_credit ) %></A>
+ </TD>
+ <<%$td%>><FONT SIZE="+1"><B> = </B></FONT></TD>
+ <<%$td%> ALIGN="right">
+ <% &$money_sprintf( $tot_tax - $tot_credit ) %>
+ </TD>
</TR>
</TABLE>
my $table = shift;
my $prefix = @_ ? shift : '';
"
- ( $table.${prefix}county = cust_main_county.county
+ ( $table.district = cust_main_county.district
+ OR cust_main_county.district = ''
+ OR cust_main_county.district IS NULL )
+ AND ( $table.${prefix}city = cust_main_county.city
+ OR cust_main_county.city = ''
+ OR cust_main_county.city IS NULL )
+ AND ( $table.${prefix}county = cust_main_county.county
OR cust_main_county.county = ''
OR cust_main_county.county IS NULL )
AND ( $table.${prefix}state = cust_main_county.state
}
my $out = 'Out of taxable region(s)';
+# these are actually tax labels, not regions
my %regions = ();
foreach my $r ( qsearch({ 'table' => 'cust_main_county',
{
#warn $r->county. ' '. $r->state. ' '. $r->country. "\n";
+ # set up a %regions entry for this region's tax label
my $label = getlabel($r);
$regions{$label}->{'label'} = $label;
$regions{$label}->{$_} = $r->$_() for (qw( county state country )); #taxname?
+ my @url_param = qw( county state country taxname );
+ push @url_param, 'city' if $cgi->param('show_cities') && $r->city();
+
$regions{$label}->{'url_param'} =
- join(';', map "$_=".uri_escape($r->$_()),
- qw( county state country taxname )
- );
+ join(';', map "$_=".uri_escape($r->$_()), @url_param );
my @param = @base_param;
my $mywhere = $where;
} else {
- $regions{$label}->{'url_param'} .= ';taxclassNULL=1'
- if $cgi->param('show_taxclasses');
-
+ # SQL for "taxclass doesn't match any other tax in the region"
my $same_sql = $r->sql_taxclass_sameregion;
$mywhere .= " AND $same_sql" if $same_sql;
+ $regions{$label}->{'url_param'} .= ';taxclassNULL=1'
+ if $cgi->param('show_taxclasses')
+ || $same_sql;
+
}
+ # FROM cust_bill_pkg JOIN (whatever is needed to determine tax location)
+ # WHERE (matches tax location and agentnum and taxclass)
+ # takes parameters in @base_param, plus taxclass if there is one
my $fromwhere = "$from_join_cust_pkg $mywhere"; # AND payby != 'COMP' ";
-# my $label = getlabel($r);
-# $regions{$label}->{'label'} = $label;
-
my $nottax = 'pkgnum != 0';
- ## calculate total for this region
+ ## calculate total of sales (non-tax line items) for this region
my $t_sql =
"SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax";
my $t = scalar_sql($r, \@param, $t_sql);
$regions{$label}->{'total'} += $t;
- #if ( $label eq $out ) # && $t ) {
- # warn "adding $t for ".
- # join('/', map $r->$_, qw( taxclass county state country ) ). "\n";
- # #warn $t_sql if $r->state eq 'FL';
- #}
+ #$regions{$label}->{subtotals}->{$r->taxnum} = $t; #useful debug
## calculate customer-exemption for this region
-## my $taxable = $t;
-
-# my($taxable, $x_cust) = (0, 0);
-# foreach my $e ( grep { $r->get($_.'tax') !~ /^Y/i }
-# qw( cust_bill_pkg.setup cust_bill_pkg.recur ) ) {
-# $taxable += scalar_sql($r, \@param,
-# "SELECT SUM($e) $fromwhere AND $nottax AND ( tax != 'Y' OR tax IS NULL )"
-# );
-#
-# $x_cust += scalar_sql($r, \@param,
-# "SELECT SUM($e) $fromwhere AND $nottax AND tax = 'Y'"
-# );
-# }
-
#false laziness -ish w/report_tax.cgi
my $cust_exempt;
if ( $r->taxname ) {
} else {
$regions{$label}->{'rate'} = $r->tax.'%';
}
-
}
+#warn Dumper(\%regions);
+# $regions{$label} now contains 'total', 'exempt_cust', 'exempt_pkg',
+# 'exempt_monthly', summed over each set of regions with the same label.
-my $distinct = "country, state, county,
+my $distinct = "country, state, county, city, district,
CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname";
my $taxclass_distinct =
#a little bit unsure of this part... test?
$taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' ";
$creditfromwhere .= " $taxwhere AND billpkgtaxratelocationnum IS NULL"; #AND payby != 'COMP' ";
-my @taxparam = @base_param;
+#should i be a cust_main_county method or something
+# yes. yes, you should.
+# $taxfromwhere: Most of a query to find cust_bill_pkg records linked to a
+# customer matching a given state/county/city/district (and within the date
+# range for the report).
+# @base_param: A list of the fields from cust_main_county to use as parameters.
+
+# $_taxamount_sub: Takes a cust_main_county and returns the sum of taxes billed
+# within the report period for all customers located in that county. If
+# the cust_main_county has a taxname, limits to taxes with that name; otherwise
+# includes all line items with pkgnum = 0 and description either 'Tax' or empty.
-#should i be a cust_main_county method or something
-#need to pass in $taxfromwhere & @taxparam???
my $_taxamount_sub = sub {
my $r = shift;
my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ".
" $taxfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax";
- scalar_sql($r, \@taxparam, $sql );
+ scalar_sql($r, [ @base_param ], $sql );
};
+# $_creditamount_sub: As above, but returns the sum of credits applied
+
my $_creditamount_sub = sub {
my $r = shift;
my $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
" $creditfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax";
- scalar_sql($r, \@taxparam, $sql );
+ scalar_sql($r, [ @base_param ], $sql );
};
#tax-report_groups filtering
my $label;
if (
$r->tax == 0
- && ! scalar( qsearch('cust_main_county', { 'state' => $r->state,
+ && ! scalar( qsearch('cust_main_county', { 'district'=> $r->district,
+ 'city' => $r->city,
'county' => $r->county,
+ 'state' => $r->state,
'country' => $r->country,
'tax' => { op=>'>', value=>0 },
}
#kludge to avoid "will not stay shared" warning
my $out = 'Out of taxable region(s)';
$label = $out;
-# } elsif ( $r->taxname && count_taxname($r->taxname) == 1 ) {
-# $label = $r->taxname;
-## $regions{$label}->{'taxname'} = $label;
-## push @{$regions{$label}->{$_}}, $r->$_() foreach qw( county state country );
} else {
$label = $r->country;
$label = $r->state.", $label" if $r->state;
$label = $r->county." county, $label" if $r->county;
+ $label = $r->city. ", $label" if $r->city && $cgi->param('show_cities');
$label = "$label (". $r->taxclass. ")"
if $r->taxclass
&& $cgi->param('show_taxclasses')
if length($agentname);
my $baselink = $p. "search/cust_bill_pkg.cgi?$dateagentlink";
my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink";
+my $creditlink = $p. "search/cust_credit_bill_pkg.html?$dateagentlink";
</%init>
projects / freeside.git / blobdiff