From: Mark Wells Date: Wed, 25 Apr 2012 18:26:36 +0000 (-0700) Subject: consider districts in tax liability report, #17051 X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=5fd66da492a1b206fa4e944eb0caa2e2642d454a consider districts in tax liability report, #17051 --- diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm index e937b205c..6316f239a 100644 --- a/FS/FS/cust_main_county.pm +++ b/FS/FS/cust_main_county.pm @@ -176,7 +176,7 @@ with different tax classes. sub sql_taxclass_sameregion { my $self = shift; - my $same_query = 'SELECT taxclass FROM cust_main_county '. + my $same_query = 'SELECT DISTINCT taxclass FROM cust_main_county '. ' WHERE taxnum != ? AND country = ?'; my @same_param = ( 'taxnum', 'country' ); foreach my $opt_field (qw( state county )) { diff --git a/FS/FS/cust_pkg.pm b/FS/FS/cust_pkg.pm index bee1b82fb..612ffa0f6 100644 --- a/FS/FS/cust_pkg.pm +++ b/FS/FS/cust_pkg.pm @@ -3445,7 +3445,13 @@ sub location_sql { # '?' placeholders in _location_sql_where my $x = $ornull ? 3 : 2; - my @bill_param = ( ('city')x3, ('county')x$x, ('state')x$x, 'country' ); + my @bill_param = ( + ('district')x3, + ('city')x3, + ('county')x$x, + ('state')x$x, + 'country' + ); my $main_where; my @main_param; @@ -3504,16 +3510,17 @@ 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 ) "; + 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}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 = ? + ( $table.${prefix}district = ? OR ? = '' OR CAST(? AS text) IS NULL ) + AND ( $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 94860d3f2..b08024cb0 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -218,7 +218,7 @@ if ( $cgi->param('taxclass') } -my @loc_param = qw( city county state country ); +my @loc_param = qw( district city county state country ); if ( $cgi->param('out') ) { @@ -266,7 +266,7 @@ if ( $cgi->param('out') ) { my %ph = ( 'county' => dbh->quote($_), map { $_ => dbh->quote( $cgi->param($_) ) } - qw( city state country ) + qw( district city state country ) ); my ( $loc_sql, @param ) = FS::cust_pkg->location_sql; @@ -330,7 +330,7 @@ if ( $cgi->param('out') ) { push @where, FS::tax_rate_location->location_sql( map { $_ => (scalar($cgi->param($_)) || '') } - qw( city county state locationtaxid ) + qw( district city county state locationtaxid ) ); } elsif ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) { diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index 0cd652d83..bfbc5fe5a 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -275,7 +275,10 @@ sub gotcust { my $table = shift; my $prefix = @_ ? shift : ''; " - ( $table.${prefix}city = cust_main_county.city + ( $table.${prefix}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 @@ -332,6 +335,7 @@ if ( $conf->exists('tax-pkg_address') ) { } my $out = 'Out of taxable region(s)'; +# these are actually tax labels, not regions my %regions = (); foreach my $r ( qsearch({ 'table' => 'cust_main_county', @@ -341,6 +345,7 @@ 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; @@ -366,6 +371,7 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', } else { + # 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; @@ -375,42 +381,24 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', } + # 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 ) { @@ -486,10 +474,12 @@ foreach my $r ( qsearch({ 'table' => 'cust_main_county', } 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, city, +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? @@ -528,11 +518,19 @@ $creditfromwhere .= ")"; $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; @@ -545,9 +543,11 @@ my $_taxamount_sub = sub { 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; @@ -560,7 +560,7 @@ my $_creditamount_sub = sub { 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 @@ -734,7 +734,8 @@ sub getlabel { my $label; if ( $r->tax == 0 - && ! scalar( qsearch('cust_main_county', { 'city' => $r->city, + && ! scalar( qsearch('cust_main_county', { 'district'=> $r->district, + 'city' => $r->city, 'county' => $r->county, 'state' => $r->state, 'country' => $r->country, @@ -747,10 +748,6 @@ sub getlabel { #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;