diff options
| -rw-r--r-- | FS/FS/cust_pkg.pm | 14 | ||||
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 14 | ||||
| -rwxr-xr-x | httemplate/search/report_tax.cgi | 53 | ||||
| -rwxr-xr-x | httemplate/search/report_tax.html | 15 |
4 files changed, 75 insertions, 21 deletions
diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index d75113f6e..7632d9ae5 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -2643,13 +2643,8 @@ sub location_sql { 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; @@ -2708,11 +2703,14 @@ sub _location_sql_where { $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 = ? "; diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 975a30713..fd8b04da9 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -157,6 +157,8 @@ if ( $cgi->param('taxclass') } +my @loc_param = qw( city county state country ); + if ( $cgi->param('out') ) { my ( $loc_sql, @param ) = FS::cust_pkg->location_sql( 'ornull' => 1 ); @@ -176,10 +178,9 @@ if ( $cgi->param('out') ) { "; #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 @@ -204,7 +205,7 @@ if ( $cgi->param('out') ) { 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; @@ -222,8 +223,7 @@ if ( $cgi->param('out') ) { } 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 @@ -274,7 +274,7 @@ if ( $cgi->param('out') ) { ); 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)" } diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 557c29cfa..22e6ab268 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -22,7 +22,10 @@ <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> @@ -119,10 +122,15 @@ <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> @@ -137,7 +145,10 @@ <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 @@ -160,6 +171,12 @@ % $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> @@ -167,6 +184,15 @@ <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> % } @@ -184,6 +210,15 @@ <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> @@ -230,7 +265,10 @@ sub gotcust { 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 @@ -298,10 +336,11 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', $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; @@ -439,7 +478,7 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', } -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? @@ -684,8 +723,9 @@ sub getlabel { 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 }, } @@ -704,6 +744,7 @@ sub getlabel { $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') diff --git a/httemplate/search/report_tax.html b/httemplate/search/report_tax.html index 217f48146..2ab0e0b2e 100755 --- a/httemplate/search/report_tax.html +++ b/httemplate/search/report_tax.html @@ -32,6 +32,13 @@ <% include( '/elements/tr-input-beginning_ending.html' ) %> +% if ( $city ) { + <TR> + <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_cities" VALUE="1"></TD> + <TD>Show cities</TD> + </TR> +% } + % if ( $conf->exists('enable_taxclasses') ) { <TR> <TD ALIGN="right"><INPUT TYPE="checkbox" NAME="show_taxclasses" VALUE="1"></TD> @@ -61,4 +68,12 @@ die "access denied" my $conf = new FS::Conf; +my $city_sql = "SELECT COUNT(*) FROM cust_main_county + WHERE city != '' AND city IS NOT NULL + LIMIT 1"; + +my $city_sth = dbh->prepare($city_sql) or die dbh->errstr; +$city_sth->execute or die $city_sth->errstr; +my $city = $city_sth->fetchrow_arrayref->[0]; + </%init> |