(and REALLY fix the line-item links too, whew) fix overreporting of tax invoiced...

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

diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index bb9c8f4..0bec85d 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, )
+              )
           )
 %>
 
@@ -289,16 +288,23 @@ if ( $conf->exists('tax-ship_address') ) {
 
 }
 
-my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0, 0 );
-my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0 );
+my($total, $tot_taxable, $owed, $tax) = ( 0, 0, 0, 0 );
+my( $exempt_cust, $exempt_pkg, $exempt_monthly ) = ( 0, 0, 0 );
 my $out = 'Out of taxable region(s)';
 my %regions = ();
-foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) {
+foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
+                          'extra_sql' => $gotcust,
+                       })
+              )
+{
   #warn $r->county. ' '. $r->state. ' '. $r->country. "\n";
 
   my $label = getlabel($r);
   $regions{$label}->{'label'} = $label;
-  $regions{$label}->{'url_param'} = join(';', map "$_=".$r->$_(), qw( county state country ) );
+  $regions{$label}->{'url_param'} =
+    join(';', map "$_=".uri_escape($r->$_()),
+                  qw( county state country taxname )
+        );
 
   my @param = @base_param;
   my $mywhere = $where;
@@ -307,30 +313,17 @@ foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) {
 
     $mywhere .= " AND taxclass = ? ";
     push @param, 'taxclass';
-    $regions{$label}->{'url_param'} .= ';taxclass='. $r->taxclass
+    $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass)
       if $cgi->param('show_taxclasses');
 
   } else {
 
-    my $same_query = "SELECT COUNT(*) FROM cust_main_county WHERE country = ?";
-    my @same_param = ( '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 $num_same_region = scalar_sql( $r, \@same_param, $same_query );
-
-    if ( $num_same_region > 1 ) {
+    $regions{$label}->{'url_param'} .= ';taxclassNULL=1'
+      if $cgi->param('show_taxclasses');
 
-      $mywhere .= " AND taxclass IS NULL";
+    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' ";
@@ -437,24 +430,31 @@ foreach my $r (qsearch('cust_main_county', {}, '', $gotcust) ) {
 
 }
 
+my $distinct = "country, state, county,
+                CASE WHEN taxname IS NULL THEN '' ELSE taxname END AS taxname";
+my $taxclass_distinct = 
+  #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";
+
+my %qsearch = (
+  'select'    => "DISTINCT $distinct, $taxclass_distinct",
+  'table'     => 'cust_main_county',
+  'hashref'   => {},
+  'extra_sql' => $gotcust,
+);
+
 my $taxwhere = "$from_join_cust $where AND payby != 'COMP' ";
 my @taxparam = @base_param;
-my %base_regions = ();
-#foreach my $label ( keys %regions ) {
-foreach my $r (
-  qsearch( 'cust_main_county',
-           {},
-           "DISTINCT ON ( country, state, county, CASE WHEN taxname IS NULL THEN '' ELSE taxname END ) *",
-           $gotcust
-         )
-) {
 
-  #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n";
-
-  my $label = getlabel($r);
-
-  #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' ";
-  #my @param = @base_param; 
+#should i be a cust_main_county method or something
+#need to pass in $taxwhere & @taxparam???
+my $_taxamount_sub = sub {
+  my $r = shift;
 
   #match itemdesc if necessary!
   my $named_tax =
@@ -465,20 +465,49 @@ foreach my $r (
   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;
+  scalar_sql($r, \@taxparam, $sql );
+};
+
+#foreach my $label ( keys %regions ) {
+foreach my $r ( qsearch(\%qsearch) ) {
+
+  #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n";
+
+  my $label = getlabel($r);
+
+  #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' ";
+  #my @param = @base_param; 
+
+  my $x = &{$_taxamount_sub}($r);
+
+  $tax += $x unless $cgi->param('show_taxclasses');
   $regions{$label}->{'tax'} += $x;
 
-  if ( $cgi->param('show_taxclasses') ) {
+}
+
+my %base_regions = ();
+if ( $cgi->param('show_taxclasses') ) {
+
+  $qsearch{'select'} = "DISTINCT $distinct";
+  foreach my $r ( qsearch(\%qsearch) ) {
+
+    my $x = &{$_taxamount_sub}($r);
+
     my $base_label = getlabel($r, 'no_taxclass'=>1 );
     $base_regions{$base_label}->{'label'} = $base_label;
+
     $base_regions{$base_label}->{'url_param'} =
-      join(';', map "$_=".$r->$_(), qw( county state country ) );
+      join(';', map "$_=". uri_escape($r->$_()),
+                     qw( county state country taxname )
+          );
+
     $base_regions{$base_label}->{'tax'} += $x;
+    $tax += $x;
   }
 
 }
 
+
 #ordering
 my @regions =
   map $regions{$_},
@@ -552,8 +581,6 @@ sub scalar_sql {
   $sth->fetchrow_arrayref->[0] || 0;
 }
 
-
-
 my $dateagentlink = "begin=$beginning;end=$ending";
 $dateagentlink .= ';agentnum='. $cgi->param('agentnum')
   if length($agentname);