fix line-item reports on taxclass-less regions

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

diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index 918383b..3d37c47 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, )
+              )
           )
 %>
 
@@ -226,7 +225,7 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   my $agent = qsearchs('agent', { 'agentnum' => $1 } );
   die "agent not found" unless $agent;
   $agentname = $agent->agent;
-  $where .= ' AND agentnum = '. $agent->agentnum;
+  $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
 }
 
 my $gotcust = "
@@ -304,10 +303,20 @@ foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) {
   my $mywhere = $where;
 
   if ( $r->taxclass ) {
+
     $mywhere .= " AND taxclass = ? ";
     push @param, 'taxclass';
     $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass
       if $cgi->param('show_taxclasses');
+
+  } else {
+
+    $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' ";
@@ -421,7 +430,20 @@ my %base_regions = ();
 foreach my $r (
   qsearch( 'cust_main_county',
            {},
-           'DISTINCT ON (country, state, county, taxname) *',
+           "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
          )
 ) {
@@ -438,10 +460,11 @@ foreach my $r (
     $r->taxname
       ? 'AND itemdesc = '. dbh->quote($r->taxname)
       : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
-  my $x = scalar_sql($r, \@taxparam,
-    "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $taxwhere ".
-    "AND pkgnum = 0 $named_tax",
-  );
+
+  my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ".
+            " $taxwhere AND pkgnum = 0 $named_tax";
+
+  my $x = scalar_sql($r, \@taxparam, $sql );
   $tax += $x;
   $regions{$label}->{'tax'} += $x;
 
@@ -528,8 +551,6 @@ sub scalar_sql {
   $sth->fetchrow_arrayref->[0] || 0;
 }
 
-
-
 my $dateagentlink = "begin=$beginning;end=$ending";
 $dateagentlink .= ';agentnum='. $cgi->param('agentnum')
   if length($agentname);