From ce1fa204e25ae8605bdb4f3dbfdf9bd1bff1a590 Mon Sep 17 00:00:00 2001 From: ivan Date: Sat, 12 Dec 2009 21:38:42 +0000 Subject: [PATCH] reporting with city taxes, RT#6776 --- FS/FS/cust_pkg.pm | 14 ++++---- httemplate/search/cust_bill_pkg.cgi | 14 ++++---- httemplate/search/report_tax.cgi | 53 +++++++++++++++++++++++++---- 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 @@ Tax owed % unless ( $cgi->param('show_taxclasses') ) { Tax invoiced + Tax credited + + Tax collected % } @@ -119,10 +122,15 @@ <% &$money_sprintf( $region->{'tax'} ) %> + <<%$tdh%>> - <<%$tdh%> ALIGN="right"> <% &$money_sprintf( $region->{'credit'} ) %> + <<%$tdh%>> = + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> + % } @@ -137,7 +145,10 @@ Tax invoiced + Tax credited + + Tax collected % #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; <<%$td%>><% $region->{'label'} %> @@ -167,6 +184,15 @@ <% &$money_sprintf( $region->{'tax'} ) %> + <<%$td%>> - + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'credit'} ) %> + + <<%$td%>> = + <<%$tdh%> ALIGN="right"> + <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %> + % } @@ -183,6 +209,15 @@ <<%$td%> ALIGN="right"> <% &$money_sprintf( $tot_tax ) %> + + <<%$td%>> - + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $tot_credit ) %> + + <<%$td%>> = + <<%$td%> ALIGN="right"> + <% &$money_sprintf( $tot_tax - $tot_credit ) %> @@ -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 ) { + + + Show cities + +% } + % if ( $conf->exists('enable_taxclasses') ) { @@ -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]; + -- 2.20.1