fix line-item reports on taxclass-less regions

diff --git a/FS/FS/cust_main_county.pm b/FS/FS/cust_main_county.pm
index 172eac9..796e0c4 100644 (file)
--- a/FS/FS/cust_main_county.pm
+++ b/FS/FS/cust_main_county.pm
@@ -157,6 +157,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 };
+}
+
 =item taxline CUST_BILL_PKG, ...
 
 Returns a listref of a name and an amount of tax calculated for the list of

diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index d048881..4ac9d9f 100644 (file)
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -122,6 +122,11 @@ if ( $cgi->param('out') ) {
   push @where, ' 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;
+  }
+
 }
 
 push @where, 'pkgnum != 0' if $cgi->param('nottax');

diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index 05c2723..3d37c47 100755 (executable)
--- 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";