my $conf = new FS::Conf;
# '?' placeholders in _location_sql_where
- my @bill_param;
- if ( $ornull ) {
- @bill_param = qw( county county state state state country );
- } else {
- @bill_param = qw( county state state country );
- }
- unshift @bill_param, 'county'; # unless $nec;
+ my $x = $ornull ? 3 : 2;
+ my @bill_param = ( ('city')x3, ('county')x$x, ('state')x$x, 'country' );
my $main_where;
my @main_param;
$ornull = $ornull ? ' OR ? IS NULL ' : '';
+ my $or_empty_city = " OR ( ? = '' AND $table.${prefix}city IS NULL ) ";
my $or_empty_county = " OR ( ? = '' AND $table.${prefix}county IS NULL ) ";
my $or_empty_state = " OR ( ? = '' AND $table.${prefix}state IS NULL ) ";
+# ( $table.${prefix}city = ? $or_empty_city $ornull )
"
- ( $table.${prefix}county = ? $or_empty_county $ornull )
+ ( $table.${prefix}city = ? OR ? = '' OR CAST(? AS text) IS NULL )
+ AND ( $table.${prefix}county = ? $or_empty_county $ornull )
AND ( $table.${prefix}state = ? $or_empty_state $ornull )
AND $table.${prefix}country = ?
";
}
+my @loc_param = qw( city county state country );
+
if ( $cgi->param('out') ) {
my ( $loc_sql, @param ) = FS::cust_pkg->location_sql( 'ornull' => 1 );
";
#not linked to by anything, but useful for debugging "out of taxable region"
- if ( grep $cgi->param($_), qw( county state country ) ) {
+ if ( grep $cgi->param($_), @loc_param ) {
- my %ph = map { $_ => dbh->quote( $cgi->param($_) ) }
- qw( county state country );
+ my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param;
my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
my %ph = ( 'county' => dbh->quote($_),
map { $_ => dbh->quote( $cgi->param($_) ) }
- qw( state country )
+ qw( city state country )
);
my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
} else {
- my %ph = map { $_ => dbh->quote( $cgi->param($_) ) }
- qw( county state country );
+ my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param;
my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
);
my %ph = map { ( $pn{$_} => dbh->quote( $cgi->param($_) || '' ) ) }
- qw( county state city locationtaxid );
+ qw( city county state locationtaxid );
push @where,
join( ' AND ', map { "( $_ = $ph{$_} OR $ph{$_} = '' AND $_ IS NULL)" }
<TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>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></TH>
<TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax credited</TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2></TH>
+ <TH CLASS="grid" BGCOLOR="#cccccc" ROWSPAN=2>Tax collected</TH>
% }
</TR>
<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"
><% &$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="<% $baselink. $invlink %>;istax=1;iscredit=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="<% $baselink %>;istax=1;iscredit=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.${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
$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;
}
-my $distinct = "country, state, county,
+my $distinct = "country, state, county, city,
CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname";
my $taxclass_distinct =
#a little bit unsure of this part... test?
my $label;
if (
$r->tax == 0
- && ! scalar( qsearch('cust_main_county', { 'state' => $r->state,
+ && ! scalar( qsearch('cust_main_county', { 'city' => $r->city,
'county' => $r->county,
+ 'state' => $r->state,
'country' => $r->country,
'tax' => { op=>'>', value=>0 },
}
$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')