voxlinesystems CDRs and quantity bs

[freeside.git] / httemplate / search / report_tax.cgi

diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index bb9c8f4..b029ec0 100755 (executable)
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -7,8 +7,7 @@
               ( $ending == 4294967295
                   ? 'now'
                   : time2str('%h %o %Y', $ending )
-              ),
-            menubar( 'Main Menu'=>$p, )
+              )
           )
 %>
 
@@ -312,8 +311,9 @@ foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) {
 
   } else {
 
-    my $same_query = "SELECT COUNT(*) FROM cust_main_county WHERE country = ?";
-    my @same_param = ( 'country' );
+    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 = ?";
@@ -323,12 +323,11 @@ foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) {
       }
     }
 
-    my $num_same_region = scalar_sql( $r, \@same_param, $same_query );
-
-    if ( $num_same_region > 1 ) {
-
-      $mywhere .= " AND taxclass IS NULL";
+    my @taxclasses = list_sql( $r, \@same_param, $same_query );
 
+    if ( scalar(@taxclasses) ) {
+      $mywhere .= ' AND '. join(' AND ', map ' taxclass != ? ', @taxclasses );
+      push @param, @taxclasses;
     }
   
   }
@@ -444,7 +443,20 @@ my %base_regions = ();
 foreach my $r (
   qsearch( 'cust_main_county',
            {},
-           "DISTINCT ON ( country, state, county, CASE WHEN taxname IS NULL THEN '' ELSE taxname END ) *",
+           "DISTINCT
+              country,
+              state,
+              county,
+              CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname,".
+
+             #a little bit unsure of this part... test?
+             #ah, it looks like it winds up being irrelevant as ->{'tax'} 
+             # from $regions is not displayed when show_taxclasses is on
+             ( $cgi->param('show_taxclasses')
+                  ? " CASE WHEN taxclass IS NULL THEN '' ELSE taxclass END "
+                  : " '' "
+                     )." AS taxclass"
+           ,
            $gotcust
          )
 ) {
@@ -552,7 +564,14 @@ sub scalar_sql {
   $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 };
+}
 
 my $dateagentlink = "begin=$beginning;end=$ending";
 $dateagentlink .= ';agentnum='. $cgi->param('agentnum')