diff options
| -rw-r--r-- | FS/FS/cust_main_county.pm | 41 | ||||
| -rw-r--r-- | httemplate/search/cust_bill_pkg.cgi | 5 | ||||
| -rwxr-xr-x | httemplate/search/report_tax.cgi | 36 |
3 files changed, 53 insertions, 29 deletions
diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm index 17f346071..22c91e82f 100644 --- a/FS/FS/cust_main_county.pm +++ b/FS/FS/cust_main_county.pm @@ -151,6 +151,47 @@ sub recurtax { return ''; } +=item sql_taxclass_sameregion + +Returns an SQL WHERE fragment or the empty string to search for entries +with different tax classes. + +=cut + +#hmm, description above could be better... + +sub sql_taxclass_sameregion { + my $self = shift; + + my $same_query = 'SELECT taxclass FROM cust_main_county '. + ' WHERE taxnum != ? AND country = ?'; + my @same_param = ( 'taxnum', 'country' ); + foreach my $opt_field (qw( state county )) { + if ( $self->$opt_field() ) { + $same_query .= " AND $opt_field = ?"; + push @same_param, $opt_field; + } else { + $same_query .= " AND $opt_field IS NULL"; + } + } + + my @taxclasses = $self->_list_sql( \@same_param, $same_query ); + + return '' unless scalar(@taxclasses); + + '( taxclass IS NULL OR ( '. #only if !$self->taxclass ?? + join(' AND ', map { 'taxclass != '.dbh->quote($_) } @taxclasses ). + ' ) ) '; +} + +sub _list_sql { + my( $self, $param, $sql ) = @_; + my $sth = dbh->prepare($sql) or die dbh->errstr; + $sth->execute( map $self->$_(), @$param ) + or die "Unexpected error executing statement $sql: ". $sth->errstr; + map $_->[0], @{ $sth->fetchall_arrayref }; +} + =back =head1 SUBROUTINES diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi index 5a86dc6e2..306bb3340 100644 --- a/httemplate/search/cust_bill_pkg.cgi +++ b/httemplate/search/cust_bill_pkg.cgi @@ -126,6 +126,11 @@ if ( $cgi->param('out') ) { $where .= ' AND taxclass = '. dbh->quote( $cgi->param('taxclass') ) if $cgi->param('taxclass'); + if ( $cgi->param('taxclassNULL') ) { + my $same_sql = $r->sql_taxclass_sameregion; + push @where, $same_sql if $same_sql; + } + } $where .= ' AND pkgnum != 0' if $cgi->param('nottax'); diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi index a49c8506d..73fdfe3ac 100755 --- a/httemplate/search/report_tax.cgi +++ b/httemplate/search/report_tax.cgi @@ -311,25 +311,12 @@ foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) { } else { - my $same_query = 'SELECT taxclass FROM cust_main_county '. - ' WHERE taxnum != ? AND country = ?'; - my @same_param = ( 'taxnum', 'country' ); - foreach my $opt_field (qw( state county )) { - if ( $r->$opt_field() ) { - $same_query .= " AND $opt_field = ?"; - push @same_param, $opt_field; - } else { - $same_query .= " AND $opt_field IS NULL"; - } - } - - my @taxclasses = list_sql( $r, \@same_param, $same_query ); - - if ( scalar(@taxclasses) ) { - $mywhere .= ' AND '. join(' AND ', map ' taxclass != ? ', @taxclasses ); - push @param, map \$_, @taxclasses; - } - + $regions{$label}->{'url_param'} .= ';taxclassNULL=1' + if $cgi->param('show_taxclasses'); + + my $same_sql = $r->sql_taxclass_sameregion; + $mywhere .= " AND $same_sql" if $same_sql; + } my $fromwhere = $from_join_cust. $join_pkg. $mywhere. " AND payby != 'COMP' "; @@ -559,18 +546,9 @@ sub scalar_sql { my( $r, $param, $sql ) = @_; #warn "$sql\n"; my $sth = dbh->prepare($sql) or die dbh->errstr; - $sth->execute( map { ref($_) ? ${$_} : $r->$_() } @$param ) - or die "Unexpected error executing statement $sql: ". $sth->errstr; - $sth->fetchrow_arrayref->[0] || 0; -} - -sub list_sql { - my( $r, $param, $sql ) = @_; - #warn "$sql\n"; - my $sth = dbh->prepare($sql) or die dbh->errstr; $sth->execute( map $r->$_(), @$param ) or die "Unexpected error executing statement $sql: ". $sth->errstr; - map $_->[0], @{ $sth->fetchall_arrayref }; + $sth->fetchrow_arrayref->[0] || 0; } my $dateagentlink = "begin=$beginning;end=$ending"; |