Merge branch 'master' of git.freeside.biz:/home/git/freeside

30 files changed, 1605 insertions, 1109 deletions

diff --git a/httemplate/search/477.html b/httemplate/search/477.html
index 250e71811..6f5fcdf3b 100755
--- a/httemplate/search/477.html
+++ b/httemplate/search/477.html
@@ -1,33 +1,24 @@
-% unless ( $type eq 'xml' ) {
-<% include( '/elements/header.html', 'FCC Form 477 Results') %>
-%}else{
+% if ( $type eq 'xml' ) {
 <?xml version="1.0" encoding="ISO-8859-1"?>
 <Form_477_submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://specialreports.fcc.gov/wcb/Form477/XMLSchema-instance/form_477_upload_Schema.xsd" >
-%}
-% if ( $type eq 'html' || $type eq 'html-print' ) {
+% } else { #html
+<& /elements/header.html, "FCC Form 477 Results - $state" &>
 <TABLE WIDTH="100%">
-  <TR><TD></TD>
-%}elsif ( $type eq 'xml' ) {
-%}
-%           unless ( $type eq 'html-print' || $type eq 'xml' ) {
+  <TR>
+    <TD></TD>
+    <TD ALIGN="right" CLASS="noprint">
+      Download full results<BR>
+%   $cgi->param('_type', 'xml');
+      as <A HREF="<% $cgi->self_url %>">XML file</A><BR>
 
-              <TD ALIGN="right">
+%   $cgi->param('_type', 'html-print');
+      as <A HREF="<% $cgi->self_url %>">printable copy</A>
 
-                Download full results<BR>
-%               $cgi->param('_type', 'xml');
-                as <A HREF="<% $cgi->self_url %>">XML file</A><BR>
-
-%               $cgi->param('_type', 'html-print');
-                as <A HREF="<% $cgi->self_url %>">printable copy</A>
-
-              </TD>
-%             $cgi->param('_type', $type );
-%           }
-% if ( $type eq 'html' || $type eq 'html-print' ) {
+    </TD>
+%   $cgi->param('_type', $type );
   </TR>
 </TABLE>
-%}elsif ( $type eq 'xml' ) {
-%}
+% } #html
 % foreach my $part ( @parts ) {
 %   if ( $part{$part} ) {
 %
@@ -47,8 +38,8 @@
 %         if ( $type eq 'xml' ) {
 <<% 'Part_IA_'. chr(65 + $tech) %>>
 %         }
-<% include( "477part${part}_summary.html", 'tech_code' => $tech, 'url' => $url ) %>
-<% include( "477part${part}_detail.html", 'tech_code' => $tech, 'url' => $url ) %>
+<& "477part${part}_summary.html", 'tech_code' => $tech, 'url' => $url &>
+<& "477part${part}_detail.html", 'tech_code' => $tech, 'url' => $url &>
 %         if ( $type eq 'xml' ) {
 </<% 'Part_IA_'. chr(65 + $tech) %>>
 %         }
@@ -58,7 +49,7 @@
 <<% 'Part_'. $part %>>
 %       }
 %       my $url = &{$url_mangler}($part);
-<% include( "477part${part}.html", 'url' => $url ) %>
+<& "477part${part}.html", 'url' => $url &>
 %       if ( $type eq 'xml' ) {
 </<% 'Part_'. $part %>>
 %       }
@@ -66,11 +57,11 @@
 %   }
 % }
 %
-% if ( $type eq 'html' || $type eq 'html-print' ) {
-<% include( '/elements/footer.html') %>
-%}elsif ( $type eq 'xml' ) {
+% if ( $type eq 'xml' ) {
 </Form_477_submission>
-%}
+% } else {
+<& /elements/footer.html &>
+% }
 <%init>
 
 my $curuser = $FS::CurrentUser::CurrentUser;
@@ -78,6 +69,9 @@ my $curuser = $FS::CurrentUser::CurrentUser;
 die "access denied"
   unless $curuser->access_right('List packages');
 
+my $state = uc($cgi->param('state'));
+$state =~ /^[A-Z]{2}$/ or die "illegal state: $state";
+
 my %part = map { $_ => 1 } grep { /^\w+$/ } $cgi->param('part');
 my $type = $cgi->param('_type') || 'html';
 my $xlsname = '477report';
diff --git a/httemplate/search/477partIA_detail.html b/httemplate/search/477partIA_detail.html
index 2eca1072b..66f3a8651 100755
--- a/httemplate/search/477partIA_detail.html
+++ b/httemplate/search/477partIA_detail.html
@@ -23,9 +23,10 @@ die "access denied"
 my %opt = @_;
 my %search_hash = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 
diff --git a/httemplate/search/477partIA_summary.html b/httemplate/search/477partIA_summary.html
index ecacaefad..f5c2bc251 100755
--- a/httemplate/search/477partIA_summary.html
+++ b/httemplate/search/477partIA_summary.html
@@ -40,9 +40,10 @@ die "access denied"
 my %opt = @_;
 my %search_hash = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 
 my @column_option = grep { /^\d+$/ } $cgi->param('part1_column_option')
diff --git a/httemplate/search/477partIIA.html b/httemplate/search/477partIIA.html
index 9b363ad5e..d2cc8c3e9 100755
--- a/httemplate/search/477partIIA.html
+++ b/httemplate/search/477partIIA.html
@@ -22,9 +22,10 @@ die "access denied"
 my $html_init = '<H2>Part IIA</H2>';
 my %search_hash = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 
 my @row_option = grep { /^\d+$/ } $cgi->param('part2a_row_option')
diff --git a/httemplate/search/477partIIB.html b/httemplate/search/477partIIB.html
index 94aa818fb..c58310d36 100755
--- a/httemplate/search/477partIIB.html
+++ b/httemplate/search/477partIIB.html
@@ -1,17 +1,44 @@
-<% include( 'elements/search.html',
-                  'html_init'        => $html_init,
-                  'name'             => 'lines',
-                  'query'            => $query,
-                  'count_query'      => 'SELECT 11',
-                  'really_disable_download' => 1,
-                  'disable_download' => 1,
-                  'nohtmlheader'     => 1,
-                  'disable_total'    => 1,
-                  'header'           => [ @headers ],
-                  'xml_elements'     => [ @xml_elements ],
-                  'fields'           => [ @fields ],
-              )
-%>
+% if ( $cgi->param('_type') eq 'xml' ) {
+%   my @cols = qw(a b c);
+%   for ( my $row = 0; $row < scalar(@rows); $row++ ) {
+%     for my $col (0..2) {
+%       if ( exists($data[$col][$row]) ) {
+<PartII_<% $row %><% $cols[$col] %>>
+%       }
+</PartII_<% $row %><% $cols[$col] %>>
+%     } #for $col
+%   } #for $row
+% } else { # HTML mode
+% # fake up the search-html.html header
+<H2>Part IIB</H2>
+<TABLE>
+  <TR><TD VALIGN="bottom"><BR></TD></TR>
+  <TR><TD COLSPAN=2>
+  <TABLE CLASS="grid" CELLSPACING=0 STYLE="border: 1px solid #cccccc;" BGCOLOR="#cccccc">
+    <TR>
+% foreach (@headers) {
+      <TH class="grid"><% $_ %></TH>
+% }
+    </TR>
+% my @bgcolor = ('eeeeee','ffffff');
+% my $row = 0;
+% foreach my $rowhead (@rows) {
+    <TR> 
+      <TD CLASS="grid" BGCOLOR="#<% $bgcolor[$row % 2] %>"><% $rowhead %></TD>
+%     for my $col (0..2) {
+      <TD CLASS="grid" BGCOLOR="#<% $bgcolor[$row % 2] %>">
+%       if ( exists($data[$col][$row]) ) {
+      <% $data[$col][$row] %>
+%       }
+      </TD>
+%     } # for $col
+    </TR>
+%   $row++;
+% } #for $rowhead
+  </TABLE>
+  </TD></TR>
+</TABLE>
+% } #XML/HTML
 <%init>
 
 my $curuser = $FS::CurrentUser::CurrentUser;
@@ -19,67 +46,89 @@ my $curuser = $FS::CurrentUser::CurrentUser;
 die "access denied"
   unless $curuser->access_right('List packages');
 
-my $html_init = '<H2>Part IIB</H2>';
 my %search_hash = ();
-  
-for ( qw(agentnum magic) ) {
-  $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
-}
-$search_hash{'classnum'} = [ $cgi->param('classnum') ];
-
-my @row_option = grep { /^\d+$/ } $cgi->param('part2b_row_option')
-  if $cgi->param('part2b_row_option');
-
-# fudge in 2nd row
-unshift @row_option, $row_option[0];
-
-my $query = 'SELECT '. join(' UNION SELECT ', 1..8);
-
-my $total_count = 0;
-my $column_value = sub {
-  my $row = shift;
-
-  my @report_option = ( $row_option[$row - 1] || '' );
 
-  my $sql_query = FS::cust_pkg->search(
-    { %search_hash, 'report_option' => join(',', @report_option) }
-  );
-
-  my $count_sql = delete($sql_query->{'count_query'});
-  if ( $row == 2 ) {
-    $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN cust_main.company IS NULL OR cust_main.company = '' THEN   CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END   ELSE 0 END, 0) ) FROM/
-      or die "couldn't parse count_sql";
-  } else {
-    $count_sql =~ s/COUNT\(\*\) FROM/sum(COALESCE(CASE WHEN part_pkg.fcc_ds0s IS NOT NULL AND part_pkg.fcc_ds0s > 0 THEN part_pkg.fcc_ds0s WHEN pkg_class.fcc_ds0s IS NOT NULL AND pkg_class.fcc_ds0s > 0 THEN pkg_class.fcc_ds0s ELSE 0 END, 0)) FROM/
-      or die "couldn't parse count_sql";
-  }
-
-  my $count_sth = dbh->prepare($count_sql)
-    or die "Error preparing $count_sql: ". dbh->errstr;
-  $count_sth->execute
-    or die "Error executing $count_sql: ". $count_sth->errstr;
-  my $count_arrayref = $count_sth->fetchrow_arrayref;
-  my $count = $count_arrayref->[0];
+$search_hash{'agentnum'} = $cgi->param('agentnum');
+$search_hash{'state'}    = $cgi->param('state');
+$search_hash{'classnum'} = [ $cgi->param('classnum') ];
+$search_hash{'status'}   = 'active';
 
-  $total_count = $count if $row == 1;
-  $count = sprintf('%.2f', $total_count ? 100*$count/$total_count : 0)
-    if $row != 1;
+my @row_option;
+foreach ($cgi->param('part2b_row_option')) {
+  push @row_option, (/^\d+$/ ? $_ : undef);
+}
 
-  return "$count";
+my $is_residential = "AND COALESCE(cust_main.company, '') = ''";
+my $has_report_option = sub {
+  map {
+    defined($row_option[$_]) ?
+    "AND EXISTS(
+      SELECT 1 FROM part_pkg_option 
+      WHERE part_pkg_option.pkgpart = part_pkg.pkgpart
+      AND optionname = 'report_option_" . $row_option[$_]."'
+      AND optionvalue = '1'
+    )" : 'AND FALSE'
+  } @_
 };
 
-my @headers = (
-  '',
-  'without broadband',
-  'with broadband',
-  'wholesale',
+# an arrayref for each column
+my @data;
+# get the skeleton of the query
+my $sql_query = FS::cust_pkg->search(\%search_hash);
+my $from_where = $sql_query->{'count_query'};
+$from_where =~ s/^SELECT COUNT\(\*\) //;
+# columns 1 and 2
+my $query_ds0 = "SELECT SUM(COALESCE(part_pkg.fcc_ds0s, pkg_class.fcc_ds0s, 0))
+  $from_where";
+# column 3
+my $query_custnum = "SELECT COUNT(DISTINCT cust_pkg.custnum) $from_where";
+
+my @base_queries = ($query_ds0, $query_ds0, $query_custnum);
+my @col_conds = (
+  # column 1
+  [
+    '',
+    $is_residential,
+    $has_report_option->(0), # nomadic
+  ],
+  # column 2
+  [
+    '',
+    $is_residential,
+    $has_report_option->(0..5),
+  ],
+  # column 3
+  [
+    ''
+  ]
 );
 
-my @xml_elements = (
-  sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}a" },
-  sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}b" },
-  sub { my $row = shift; my $rownum = $row->[0] + 1; "PartII_${rownum}c" },
-);
+my $col = 0;
+foreach (@col_conds) {
+  my @col_data;
+  my $row = 0;
+  foreach my $cond (@{ $col_conds[$col] }) {
+    # three parts: the select expression, the VoIP class (column selection),
+    # and the row selection
+    my $query = $base_queries[$col] . 
+                " AND part_pkg.fcc_voip_class = '".($col+1)."'
+                $cond";
+    my $count = FS::Record->scalar_sql($query) || 0;
+    if ( $row == 0 ) {
+      $col_data[$row] = $count; # the raw count
+    } else {
+      if ( $col_data[0] == 0 ) {
+        $col_data[$row] = ''; # show nothing in this row, then
+      } else {
+        $col_data[$row] = sprintf('%.2f', 100 * $count / $col_data[0]) . '%';
+      }
+    } #if $row == 0
+    $row++;
+  }
+  $data[$col] = \@col_data;
+  $col++;
+}
+
 
 my @rows = (
   'total number',
@@ -92,12 +141,11 @@ my @rows = (
   '% other broadband',
 );
 
-my @fields = (
-  sub { my $row = shift; $rows[$row->[0] - 1]; },
-  sub { 0; },
-  sub { my $row = shift; &{$column_value}($row->[0]); },
-  sub { 0; },
+my @headers = (
+  '',
+  'without broadband',
+  'with broadband',
+  'wholesale',
 );
 
-shift @fields if $cgi->param('_type') eq 'xml';
 </%init>
diff --git a/httemplate/search/477partV.html b/httemplate/search/477partV.html
index 0987fea44..2fd5119d1 100755
--- a/httemplate/search/477partV.html
+++ b/httemplate/search/477partV.html
@@ -10,7 +10,7 @@
                   'no_field_elements' => 1,
                   'fields'            => [ 'zip' ],
                   'url'               => $opt{url} || '',
-                  'disable_download'  => 1,
+                  'really_disable_download'  => 1,
 
               )
 %>
@@ -27,9 +27,10 @@ my %search_hash = ();
 my @sql_query = ();
 my @count_query = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ];
 $search_hash{report_option} = $cgi->param('partv_report_option')
   if $cgi->param('partv_report_option');
diff --git a/httemplate/search/477partVI_census.html b/httemplate/search/477partVI_census.html
index 4d1fb2136..8425c4b48 100755
--- a/httemplate/search/477partVI_census.html
+++ b/httemplate/search/477partVI_census.html
@@ -23,6 +23,7 @@
                   'links'           => \@links,
                   'url'             => $opt{url} || '',
                   'xml_row_element' => 'Datarow',
+                  'really_disable_download' => 1,
               )
 %>
 <%init>
@@ -80,9 +81,10 @@ push @fields,
 my %search_hash = ();
 my @sql_query = ();
   
-for ( qw(agentnum magic) ) {
+for ( qw(agentnum magic state) ) {
   $search_hash{$_} = $cgi->param($_) if $cgi->param($_);
 }
+$search_hash{'country'} = 'US';
 $search_hash{'classnum'} = [ $cgi->param('classnum') ]
   if grep { $_ eq 'classnum' } $cgi->param;
 
@@ -115,10 +117,10 @@ foreach my $row ( @row_option ) {
       );
       my $extracolumns = "$rowcount AS upload, $columncount AS download, $tech_code as technology_code";
       my $percent = "CASE WHEN count(*) > 0 THEN 100-100*cast(count(cust_main.company) as numeric)/cast(count(*) as numeric) ELSE cast(0 as numeric) END AS residential";
-      $sql_query->{select} = "count(*) AS quantity, $extracolumns, censustract, $percent";
+      $sql_query->{select} = "count(*) AS quantity, $extracolumns, cust_location.censustract, $percent";
       $sql_query->{order_by} =~ /^(.*)(ORDER BY pkgnum)(.*)$/s
         or die "couldn't parse order_by";
-      $sql_query->{order_by} = "$1 GROUP BY censustract $3";
+      $sql_query->{order_by} = "$1 GROUP BY cust_location.censustract $3";
       push @sql_query, $sql_query;
     }
     $columncount++;
@@ -131,7 +133,8 @@ my $count_query = 'SELECT count(*) FROM ( ('.
       map { my $addl_from = $_->{addl_from};
             my $extra_sql = $_->{extra_sql};
             my $order_by  = $_->{order_by};
-            "SELECT censustract from cust_pkg $addl_from $extra_sql $order_by";
+            "SELECT cust_location.censustract from cust_pkg $addl_from 
+            $extra_sql $order_by";
           }
       @sql_query
    ). ') ) AS foo';
diff --git a/httemplate/search/cust_bill_pkg.cgi b/httemplate/search/cust_bill_pkg.cgi
index 1a46b0097..4c0fa4a56 100644
--- a/httemplate/search/cust_bill_pkg.cgi
+++ b/httemplate/search/cust_bill_pkg.cgi
@@ -3,25 +3,10 @@
                  'name'        => emt('line items'),
                  'query'       => $query,
                  'count_query' => $count_query,
-                 'count_addl'  => [ $money_char. '%.2f total',
-                                    $unearned ? ( $money_char. '%.2f unearned revenue' ) : (),
-                                  ],
+                 'count_addl'  => \@total_desc,
                  'header'      => [
                    emt('Description'),
-                   ( $unearned
-                     ? ( emt('Unearned'), 
-                         emt('Owed'), # useful in 'paid' mode?
-                         emt('Payment date') )
-                     : ( emt('Setup charge') )
-                   ),
-                   ( $use_usage eq 'usage'
-                     ? emt('Usage charge')
-                     : emt('Recurring charge')
-                   ),
-                   ( $unearned
-                     ? ( emt('Charge start'), emt('Charge end') )
-                     : ()
-                   ),
+                   @peritem_desc,
                    emt('Invoice'),
                    emt('Date'),
                    FS::UI::Web::cust_header(),
@@ -33,65 +18,21 @@
                        },
                    #strikethrough or "N/A ($amount)" or something these when
                    # they're not applicable to pkg_tax search
-                   sub { my $cust_bill_pkg = shift;
-                         if ( $unearned ) {
-
-                           sprintf($money_char.'%.2f', 
-                             $cust_bill_pkg->unearned_revenue)
-
-                         } else {
-                           sprintf($money_char.'%.2f', $cust_bill_pkg->setup );
-                         }
-                       },
-                   ( $unearned
-                     ? ( $owed_sub, $payment_date_sub, )
-                     : ()
-                   ),
-                   sub { my $row = shift;
-                         my $value = 0;
-                         if ( $use_usage eq 'recurring' or $unearned ) {
-                           $value = $row->recur - $row->usage;
-                         } elsif ( $use_usage eq 'usage' ) {
-                           $value = $row->usage;
-                         } else {
-                           $value = $row->recur;
-                         }
-                         sprintf($money_char.'%.2f', $value );
-                       },
-                   ( $unearned
-                     ? ( sub { time2str('%b %d %Y', shift->sdate ) },
-                       # shift edate back a day
-                       # 82799 = 3600*23 - 1
-                       # (to avoid skipping a day during DST)
-                         sub { time2str('%b %d %Y', shift->edate - 82799 ) },
-                       )
-                     : ()
-                   ),
+                   @peritem_sub,
                    'invnum',
                    sub { time2str('%b %d %Y', shift->_date ) },
                    \&FS::UI::Web::cust_fields,
                  ],
                  'sort_fields' => [
                    '',
-                   'setup', #broken in $unearned case i guess
-                   ( $unearned ? ('', '') : () ),
-                   ( $use_usage eq 'recurring' or $unearned
-                        ? 'recur - usage' :
-                     $use_usage eq 'usage' 
-                        ? 'usage'
-                        : 'recur'
-                   ),
-                   ( $unearned ? ('sdate', 'edate') : () ),
+                   @peritem,
                    'invnum',
                    '_date',
                  ],
                  'links'       => [
                    #'',
                    '',
-                   '',
-                   ( $unearned ? ( '', '' ) : () ),
-                   '',
-                   ( $unearned ? ( '', '' ) : () ),
+                   @peritem_null,
                    $ilink,
                    $ilink,
                    ( map { $_ ne 'Cust. Status' ? $clink : '' }
@@ -99,19 +40,14 @@
                    ),
                  ],
                  #'align' => 'rlrrrc'.FS::UI::Web::cust_aligns(),
-                 'align' => 'lr'.
-                            ( $unearned ? 'rc' : '' ).
-                            'r'.
-                            ( $unearned ? 'cc' : '' ).
+                 'align' => 'l'.
+                            $peritem_align.
                             'rc'.
                             FS::UI::Web::cust_aligns(),
                  'color' => [ 
                               #'',
                               '',
-                              '',
-                              ( $unearned ? ( '', '' ) : () ),
-                              '',
-                              ( $unearned ? ( '', '' ) : () ),
+                              @peritem_null,
                               '',
                               '',
                               FS::UI::Web::cust_colors(),
@@ -119,44 +55,126 @@
                  'style' => [ 
                               #'',
                               '',
-                              '',
-                              ( $unearned ? ( '', '' ) : () ),
-                              '',
-                              ( $unearned ? ( '', '' ) : () ),
+                              @peritem_null,
                               '',
                               '',
                               FS::UI::Web::cust_styles(),
                             ],
 &>
-<%init>
+<%doc>
+
+Output parameters:
+- distribute: Boolean.  If true, recurring fees will be "prorated" for the 
+  portion of the package date range (sdate-edate) that falls within the date
+  range of the report.  Line items will be limited to those for which this 
+  portion is > 0.  This disables filtering on invoice date.
+
+- use_usage: Separate usage (cust_bill_pkg_detail records) from
+  recurring charges.  If set to "usage", will show usage instead of 
+  recurring charges.  If set to "recurring", will deduct usage and only
+  show the flat rate charge.  If not passed, the "recurring charge" column
+  will include usage charges also.
+
+Filtering parameters:
+- begin, end: Date range.  Applies to invoice date, not necessarily package
+  date range.  But see "distribute".
+
+- status: Customer status (active, suspended, etc.).  This will filter on 
+  _current_ customer status, not status at the time the invoice was generated.
+
+- agentnum: Filter on customer agent.
+
+- refnum: Filter on customer reference source.
+
+- classnum: Filter on package class.
+
+- use_override: Apply "classnum" and "taxclass" filtering based on the 
+  override (bundle) pkgpart, rather than always using the true pkgpart.
+
+- nottax: Limit to items that are not taxes (pkgnum > 0).
+
+- istax: Limit to items that are taxes (pkgnum == 0).
+
+- taxnum: Limit to items whose tax definition matches this taxnum.
+  With "nottax" that means items that are subject to that tax;
+  with "istax" it's the tax charges themselves.  Can be specified 
+  more than once to include multiple taxes.
+
+- country, state, county, city: Limit to items whose tax location 
+  matches these fields.  If "nottax" it's the tax location of the package;
+  if "istax" the location of the tax.
+
+- taxname, taxnameNULL: With "nottax", limit to items whose tax location
+  matches a tax with this name.  With "istax", limit to items that have
+  this tax name.  taxnameNULL is equivalent to "taxname = '' OR taxname 
+  = 'Tax'".
+
+- out: With "nottax", limit to items that don't match any tax definition.
+  With "istax", find tax items that are unlinked to their tax definitions.
+  Current Freeside (> July 2012) always creates tax links, but unlinked
+  items may result from an incomplete upgrade of legacy data.
+
+- locationtaxid: With "nottax", limit to packages matching this 
+  tax_rate_location ID; with "tax", limit to taxes generated from that 
+  location.
+
+- taxclass: Filter on package taxclass.
+
+- taxclassNULL: With "nottax", limit to items that would be subject to the
+  tax with taxclass = NULL.  This doesn't necessarily mean part_pkg.taxclass
+  is NULL; it also includes taxclasses that don't have a tax in this region.
+
+- itemdesc: Limit to line items with this description.  Note that non-tax
+  packages usually have a description of NULL.  (Deprecated.)
 
-#LOTS of false laziness below w/cust_credit_bill_pkg.cgi
+- report_group: Can contain '=' or '!=' followed by a string to limit to 
+  line items where itemdesc starts with, or doesn't start with, the string.
+
+- cust_tax: Limit to customers who are tax-exempt.  If "taxname" is also
+  specified, limit to customers who are also specifically exempt from that 
+  tax.
+
+- pkg_tax: Limit to packages that are tax-exempt, and only include the 
+  exempt portion (setup, recurring, or both) when calculating totals.
+
+- taxable: Limit to packages that are subject to tax, i.e. where a
+  cust_bill_pkg_tax_location record exists.
+
+- credit: Limit to line items that received a credit application.  The
+  amount of the credit will also be shown.
+
+</%doc>
+<%init>
 
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
 my $conf = new FS::Conf;
-
-my $unearned = '';
-my $unearned_mode = '';
-my $unearned_base = '';
-my $unearned_sql = '';
+my $money_char = $conf->config('money_char') || '$';
 
 my @select = ( 'cust_bill_pkg.*', 'cust_bill._date' );
+my @total = ( 'COUNT(*)', 'SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)');
+my @total_desc = ( '%d line items', $money_char.'%.2f total' ); # sprintf strings
+my @peritem = ( 'setup', 'recur' );
+my @peritem_desc = ( 'Setup charge', 'Recurring charge' );
 my ($join_cust, $join_pkg ) = ('', '');
+my $use_usage;
+
+# valid in both the tax and non-tax cases
+$join_cust = 
+  " LEFT JOIN cust_bill USING (invnum)
+    LEFT JOIN cust_main USING (custnum)
+  ";
 
-#here is the agent virtualization
+#agent virtualization
 my $agentnums_sql =
   $FS::CurrentUser::CurrentUser->agentnums_sql( 'table' => 'cust_main' );
 
 my @where = ( $agentnums_sql );
 
+# date range
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 
-if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
-  push @where, FS::cust_main->cust_status_sql . " = '$1'";
-}
-
 if ( $cgi->param('distribute') == 1 ) {
   push @where, "sdate <= $ending",
                "edate >  $beginning",
@@ -167,456 +185,371 @@ else {
                "cust_bill._date <= $ending";
 }
 
+# status
+if ( $cgi->param('status') =~ /^([a-z]+)$/ ) {
+  push @where, FS::cust_main->cust_status_sql . " = '$1'";
+}
+
+# agentnum
 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.agentnum = $1";
 }
 
+# refnum
 if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.refnum = $1";
 }
 
-#classnum
-# not specified: all classes
-# 0: empty class
-# N: classnum
-my $use_override = $cgi->param('use_override');
-if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
-  my $comparison = '';
-  if ( $1 == 0 ) {
-    $comparison = "IS NULL";
-  } else {
-    $comparison = "= $1";
-  }
-
-  if ( $use_override ) {
-    push @where, "(
-      part_pkg.classnum $comparison AND pkgpart_override IS NULL OR
-      override.classnum $comparison AND pkgpart_override IS NOT NULL
-    )";
-  } else {
-    push @where, "part_pkg.classnum $comparison";
-  }
-}
+# the non-tax case
+if ( $cgi->param('nottax') ) {
 
-if ( $cgi->param('taxclass')
-     && ! $cgi->param('istax')  #no part_pkg.taxclass in this case
-                                #(should we save a taxclass or a link to taxnum
-                                # in cust_bill_pkg or something like
-                                # cust_bill_pkg_tax_location?)
-   )
-{
-
-  #override taxclass when use_override is specified?  probably
-  #if ( $use_override ) {
-  #
-  #  push @where,
-  #    ' ( '. join(' OR ',
-  #                  map {
-  #                        ' (    part_pkg.taxclass = '. dbh->quote($_).
-  #                        '      AND pkgpart_override IS NULL '.
-  #                        '   OR '.
-  #                        '      override.taxclass = '. dbh->quote($_).
-  #                        '      AND pkgpart_override IS NOT NULL '.
-  #                        ' ) '
-  #                      }
-  #                      $cgi->param('taxclass')
-  #               ).
-  #    ' ) ';
-  #
-  #} else {
-
-    push @where, ' part_pkg.taxclass IN ( '.
-                   join(', ', map dbh->quote($_), $cgi->param('taxclass') ).
-                 ' ) ';
-
-  #}
+  push @where, 'cust_bill_pkg.pkgnum > 0';
 
-}
+  # then we want the package and its definition
+  $join_pkg = 
+' LEFT JOIN cust_pkg      USING (pkgnum) 
+  LEFT JOIN part_pkg      USING (pkgpart)';
 
-my @loc_param = qw( district city county state country );
+  my $part_pkg = 'part_pkg';
+  if ( $cgi->param('use_override') ) {
+    # still need the real part_pkg for tax applicability, 
+    # so alias this one
+    $join_pkg .= " LEFT JOIN part_pkg AS override ON (
+    COALESCE(cust_bill_pkg.pkgpart_override, cust_pkg.pkgpart, 0) = part_pkg.pkgpart
+    )";
+    $part_pkg = 'override';
+  }
+  push @select, 'part_pkg.pkg'; # or should this use override?
 
-if ( $cgi->param('out') ) {
+  my @tax_where; # will go into a subquery
+  my @exempt_where; # will also go into a subquery
 
-  my ( $loc_sql, @param ) = FS::cust_pkg->location_sql( 'ornull' => 1 );
-  while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
-    $loc_sql =~ s/\?/'cust_main_county.'.shift(@param)/e;
+  # classnum (of override pkgpart if applicable)
+  # not specified: all classes
+  # 0: empty class
+  # N: classnum
+  if ( $cgi->param('classnum') =~ /^(\d+)$/ ) {
+    push @where, "COALESCE($part_pkg.classnum, 0) = $1";
   }
 
-  $loc_sql =~ s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g
-    if $cgi->param('istax');
-
-  push @where, "
-    0 = (
-          SELECT COUNT(*) FROM cust_main_county
-           WHERE cust_main_county.tax > 0
-             AND $loc_sql
-        )
-  ";
+  # taxclass
+  if ( $cgi->param('taxclassNULL') ) {
+    # a little different from 'taxclass' in that it applies to the
+    # effective taxclass, not the real one
+    push @tax_where, 'cust_main_county.taxclass IS NULL'
+  } elsif ( $cgi->param('taxclass') ) {
+    push @tax_where, "$part_pkg.taxclass IN (" .
+                 join(', ', map {dbh->quote($_)} $cgi->param('taxclass') ).
+                 ')';
+  }
 
-  #not linked to by anything, but useful for debugging "out of taxable region"
-  if ( grep $cgi->param($_), @loc_param ) {
+  if ( $cgi->param('exempt_cust') eq 'Y' ) {
+    # tax-exempt customers
+    push @exempt_where, "(exempt_cust = 'Y' OR exempt_cust_taxname = 'Y')";
 
-    my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param;
+  } elsif ( $cgi->param('exempt_pkg') eq 'Y' ) { # non-taxable package
+    # non-taxable package charges
+    push @exempt_where, "(exempt_setup = 'Y' OR exempt_recur = 'Y')";
+  }
+  # we don't handle exempt_monthly here
+  
+  if ( $cgi->param('taxname') ) { # specific taxname
+      push @tax_where, 'cust_main_county.taxname = '.
+                        dbh->quote($cgi->param('taxname'));
+  } elsif ( $cgi->param('taxnameNULL') ) {
+      push @tax_where, 'cust_main_county.taxname IS NULL OR '.
+                       'cust_main_county.taxname = \'Tax\'';
+  }
 
-    my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
-    while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
-      $loc_sql =~ s/\?/$ph{shift(@param)}/e;
+  # country:state:county:city:district (may be repeated)
+  # You can also pass a big list of taxnums but that leads to huge URLs.
+  # Note that this means "packages whose tax is in this region", not 
+  # "packages in this region".  It's meant for links from the tax report.
+  if ( $cgi->param('region') ) {
+    my @orwhere;
+    foreach ( $cgi->param('region') ) {
+      my %loc;
+      @loc{qw(country state county city district)} = 
+        split(':', $cgi->param('region'));
+      my $string = join(' AND ',
+            map { 
+              if ( $loc{$_} ) {
+                "$_ = ".dbh->quote($loc{$_});
+              } else {
+                "$_ IS NULL";
+              }
+            } keys(%loc)
+      );
+      push @orwhere, "($string)";
     }
+    push @tax_where, '(' . join(' OR ', @orwhere) . ')' if @orwhere;
+  }
 
-    push @where, $loc_sql;
-
+  # specific taxnums
+  if ( $cgi->param('taxnum') ) {
+    my $taxnum_in = join(',', 
+      grep /^\d+$/, $cgi->param('taxnum')
+    );
+    push @tax_where, "cust_main_county.taxnum IN ($taxnum_in)"
+      if $taxnum_in;
   }
 
-} elsif ( $cgi->param('country') ) {
+  # If we're showing exempt items, we need to find those with 
+  # cust_tax_exempt_pkg records matching the selected taxes.
+  # If we're showing taxable items, we need to find those with 
+  # cust_bill_pkg_tax_location records.  We also need to find the 
+  # exemption records so that we can show the taxable amount.
+  # If we're showing all items, we need the union of those.
+  # If we're showing 'out' (items that aren't region/class taxable),
+  # then we need the set of all items minus the union of those.
 
-  my @counties = $cgi->param('county');
-   
-  if ( scalar(@counties) > 1 ) {
+  my $exempt_sub;
 
-    #hacky, could be more efficient.  care if it is ever used for more than the
-    # tax-report_groups filtering kludge
+  if ( @exempt_where or @tax_where 
+    or $cgi->param('taxable') or $cgi->param('out') )
+  {
+    # process exemption restrictions, including @tax_where
+    my $exempt_sub = 'SELECT SUM(amount) as exempt_amount, billpkgnum 
+    FROM cust_tax_exempt_pkg JOIN cust_main_county USING (taxnum)';
 
-    my $locs_sql =
-      ' ( '. join(' OR ', map {
+    $exempt_sub .= ' WHERE '.join(' AND ', @tax_where, @exempt_where)
+      if (@tax_where or @exempt_where);
 
-          my %ph = ( 'county' => dbh->quote($_),
-                     map { $_ => dbh->quote( $cgi->param($_) ) }
-                       qw( district city state country )
-                   );
+    $exempt_sub .= ' GROUP BY billpkgnum';
 
-          my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
-          while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
-            $loc_sql =~ s/\?/$ph{shift(@param)}/e;
-          }
+    $join_pkg .= " LEFT JOIN ($exempt_sub) AS item_exempt
+    USING (billpkgnum)";
+  }
+ 
+  if ( @tax_where or $cgi->param('taxable') or $cgi->param('out') ) { 
+    # process tax restrictions
+    unshift @tax_where,
+      'cust_main_county.tax > 0';
+
+    my $tax_sub = "SELECT invnum, cust_bill_pkg_tax_location.pkgnum
+    FROM cust_bill_pkg_tax_location
+    JOIN cust_bill_pkg AS tax_item USING (billpkgnum)
+    JOIN cust_main_county USING (taxnum)
+    WHERE ". join(' AND ', @tax_where).
+    " GROUP BY invnum, cust_bill_pkg_tax_location.pkgnum";
+
+    $join_pkg .= " LEFT JOIN ($tax_sub) AS item_tax
+    ON (item_tax.invnum = cust_bill_pkg.invnum AND
+        item_tax.pkgnum = cust_bill_pkg.pkgnum)";
+  }
 
-          $loc_sql;
+  # now do something with that
+  if ( @exempt_where ) {
 
-        } @counties
+    push @where,    'item_exempt.billpkgnum IS NOT NULL';
+    push @select,   'item_exempt.exempt_amount';
+    push @peritem,  'exempt_amount';
+    push @peritem_desc, 'Exempt';
+    push @total,    'SUM(exempt_amount)';
+    push @total_desc, "$money_char%.2f tax-exempt";
 
-      ). ' ) ';
+  } elsif ( $cgi->param('taxable') ) {
 
-    push @where, $locs_sql;
+    my $taxable = 'cust_bill_pkg.setup + cust_bill_pkg.recur '.
+                  '- COALESCE(item_exempt.exempt_amount, 0)';
 
-  } else {
+    push @where,    'item_tax.invnum IS NOT NULL';
+    push @select,   "($taxable) AS taxable_amount";
+    push @peritem,  'taxable_amount';
+    push @peritem_desc, 'Taxable';
+    push @total,    "SUM($taxable)";
+    push @total_desc, "$money_char%.2f taxable";
 
-    my %ph = map { $_ => dbh->quote( scalar($cgi->param($_)) ) } @loc_param;
+  } elsif ( $cgi->param('out') ) {
+  
+    push @where,    'item_tax.invnum IS NULL',
+                    'item_exempt.billpkgnum IS NULL';
 
-    my ( $loc_sql, @param ) = FS::cust_pkg->location_sql;
-    while ( $loc_sql =~ /\?/ ) { #easier to do our own substitution
-      $loc_sql =~ s/\?/$ph{shift(@param)}/e;
-    }
+  } elsif ( @tax_where ) {
 
-    push @where, $loc_sql;
+    # union of taxable + all exempt_ cases
+    push @where,
+      '(item_tax.invnum IS NOT NULL OR item_exempt.billpkgnum IS NOT NULL)';
 
   }
-   
-  if ( $cgi->param('istax') ) {
-    if ( $cgi->param('taxname') ) {
-      push @where, 'itemdesc = '. dbh->quote( $cgi->param('taxname') );
-    #} elsif ( $cgi->param('taxnameNULL') {
-    } else {
-      push @where, "( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
-    }
-  } elsif ( $cgi->param('nottax') ) {
-    #what can we usefully do with "taxname" ????  look up a class???
-  } else {
-    #warn "neither nottax nor istax parameters specified";
-  }
 
-  if ( $cgi->param('taxclassNULL')
-       && ! $cgi->param('istax')  #no part_pkg.taxclass in this case
-                                  #(see comment above?)
-     )
-  {
-    my %hash = ( 'country' => scalar($cgi->param('country')) );
-    foreach (qw( state county )) {
-      $hash{$_} = scalar($cgi->param($_)) if $cgi->param($_);
-    }
-    my $cust_main_county = qsearchs('cust_main_county', \%hash);
-    die "unknown base region for empty taxclass" unless $cust_main_county;
+  # recur/usage separation
+  $use_usage = $cgi->param('usage');
+  if ( $use_usage eq 'recurring' ) {
 
-    my $same_sql = $cust_main_county->sql_taxclass_sameregion;
-    $same_sql =~ s/taxclass/part_pkg.taxclass/g;
-    push @where, $same_sql if $same_sql;
+    my $recur_no_usage = FS::cust_bill_pkg->charged_sql('', '', no_usage => 1);
+    push @select, "($recur_no_usage) AS recur_no_usage";
+    $peritem[1] = 'recur_no_usage';
+    $total[1] = "SUM(cust_bill_pkg.setup + $recur_no_usage)";
+    $total_desc[1] .= ' (excluding usage)';
+
+  } elsif ( $use_usage eq 'usage' ) {
 
+    my $usage = FS::cust_bill_pkg->usage_sql();
+    push @select, "($usage) AS _usage";
+    # there's already a method named 'usage'
+    $peritem[1] = '_usage';
+    $peritem_desc[1] = 'Usage charge';
+    $total[1] = "SUM($usage)";
+    $total_desc[1] .= ' usage charges';
   }
 
-} elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
+} elsif ( $cgi->param('istax') ) {
 
-  push @where, FS::tax_rate_location->location_sql(
-                 map { $_ => (scalar($cgi->param($_)) || '') }
-                   qw( district city county state locationtaxid )
-               );
+  @peritem = ( 'setup' ); # taxes only have setup
+  @peritem_desc = ( 'Tax charge' );
 
-}
+  push @where, 'cust_bill_pkg.pkgnum = 0';
 
-# unearned revenue mode
-if ( $cgi->param('unearned_now') =~ /^(\d+)$/ ) {
+  # tax location when using tax_rate_location
+  if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
 
-  $unearned = $1;
-  $unearned_mode = $cgi->param('mode');
+    $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
+                 ' LEFT JOIN tax_rate_location USING ( taxratelocationnum )';
+    push @where, FS::tax_rate_location->location_sql(
+                   map { $_ => (scalar($cgi->param($_)) || '') }
+                     qw( district city county state locationtaxid )
+                 );
 
-  push @where, "cust_bill_pkg.sdate < $unearned",
-               "cust_bill_pkg.edate > $unearned",
-               "cust_bill_pkg.recur != 0",
-               "part_pkg.freq != '0'";
+    $total[1] = 'SUM(
+      COALESCE(cust_bill_pkg_tax_rate_location.amount, 
+               cust_bill_pkg.setup + cust_bill_pkg.recur)
+    )';
 
-  if ( !$cgi->param('include_monthly') ) {
-    push @where,
-               "part_pkg.freq != '1'",
-               "part_pkg.freq NOT LIKE '%h'",
-               "part_pkg.freq NOT LIKE '%d'",
-               "part_pkg.freq NOT LIKE '%w'";
-  }
+  } elsif ( $cgi->param('out') ) {
 
-  my $usage_sql = FS::cust_bill_pkg->usage_sql;
-  push @select, "($usage_sql) AS usage"; # we need this
-  my $paid_sql = 'GREATEST(' .
-    FS::cust_bill_pkg->paid_sql($unearned, '', setuprecur => 'recur') .
-    " - $usage_sql, 0)";
+    $join_pkg = '
+      LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
+    ';
+    push @where, 'cust_bill_pkg_tax_location.billpkgnum IS NULL';
 
-  push @select, "$paid_sql AS paid_no_usage"; # need this either way
+    # each billpkgnum should appear only once
+    $total[0] = 'COUNT(*)';
+    $total[1] = 'SUM(cust_bill_pkg.setup)';
 
-  if ( $unearned_mode eq 'paid' ) {
-    # then use the amount paid, minus usage charges
-    $unearned_base = $paid_sql;
-  }
-  else {
-    # use the amount billed, minus usage charges and credits
-    $unearned_base = "GREATEST( cust_bill_pkg.recur - ".
-      FS::cust_bill_pkg->credited_sql($unearned, '', setuprecur => 'recur') .
-      " - $usage_sql, 0)";
-      # include only rows that have some non-usage, non-credited portion
-  }
-  # whatever we're using as the base, only show rows where it's positive
-  push @where, "$unearned_base > 0";
-
-  my $period = "CAST(cust_bill_pkg.edate - cust_bill_pkg.sdate AS REAL)";
-  my $elapsed = "GREATEST( $unearned - cust_bill_pkg.sdate, 0 )";
-  my $remaining = "(1 - $elapsed/$period)";
-
-  $unearned_sql = "CAST( $unearned_base * $remaining AS DECIMAL(10,2) )";
-  push @select, "$unearned_sql AS unearned_revenue";
-
-  # last payment/credit date
-  my %t = (pay => 'cust_bill_pay', credit => 'cust_credit_bill');
-  foreach my $x (qw(pay credit)) {
-    my $table = $t{$x};
-    my $link = $table.'_pkg';
-    my $pkey = dbdef->table($table)->primary_key;
-    my $last_date_sql = "SELECT MAX(_date) 
-    FROM $table JOIN $link USING ($pkey)
-    WHERE $link.billpkgnum = cust_bill_pkg.billpkgnum 
-    AND $table._date <= $unearned";
-    push @select, "($last_date_sql) AS last_$x";
-  }
+  } else { # not locationtaxid or 'out'--the normal case
 
-}
+    $join_pkg = '
+      LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
+      JOIN cust_main_county           USING (taxnum)
+    ';
 
-if ( $cgi->param('itemdesc') ) {
-  if ( $cgi->param('itemdesc') eq 'Tax' ) {
-    push @where, "(itemdesc='Tax' OR itemdesc is null)";
-  } else {
-    push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
+    # don't double-count the components of consolidated taxes
+    $total[0] = 'COUNT(DISTINCT cust_bill_pkg.billpkgnum)';
+    $total[1] = 'SUM(cust_bill_pkg_tax_location.amount)';
   }
-}
 
-if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ && $cgi->param('istax') ) {
-  my ( $group_op, $group_value ) = ( $1, $2 );
-  if ( $group_op eq '=' ) {
-    #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
-    push @where, 'itemdesc = '. dbh->quote($group_value);
-  } elsif ( $group_op eq '!=' ) {
-    push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
-  } else {
-    die "guru meditation #00de: group_op $group_op\n";
+  # taxclass
+  if ( $cgi->param('taxclassNULL') ) {
+    push @where, 'cust_main_county.taxclass IS NULL';
   }
-  
-}
 
-push @where, 'cust_bill_pkg.pkgnum != 0' if $cgi->param('nottax');
-push @where, 'cust_bill_pkg.pkgnum  = 0' if $cgi->param('istax');
-
-if ( $cgi->param('cust_tax') ) {
-  #false laziness -ish w/report_tax.cgi
-  my $cust_exempt;
-  if ( $cgi->param('taxname') ) {
-    my $q_taxname = dbh->quote($cgi->param('taxname'));
-    $cust_exempt =
-      "( tax = 'Y'
-         OR EXISTS ( SELECT 1 FROM cust_main_exemption
-                       WHERE cust_main_exemption.custnum = cust_main.custnum
-                         AND cust_main_exemption.taxname = $q_taxname )
-       )
-      ";
-  } else {
-    $cust_exempt = " tax = 'Y' ";
+  # taxname
+  if ( $cgi->param('taxnameNULL') ) {
+    push @where, 'cust_main_county.taxname IS NULL OR '.
+                 'cust_main_county.taxname = \'Tax\'';
+  } elsif ( $cgi->param('taxname') ) {
+    push @where, 'cust_main_county.taxname = '.
+                  dbh->quote($cgi->param('taxname'));
   }
 
-  push @where, $cust_exempt;
-}
-
-my $use_usage = $cgi->param('use_usage');
-
-my $count_query;
-if ( $cgi->param('pkg_tax') ) {
-
-  $count_query =
-    "SELECT COUNT(*),
-            SUM(
-                 ( CASE WHEN part_pkg.setuptax = 'Y'
-                        THEN cust_bill_pkg.setup
-                        ELSE 0
-                   END
-                 )
-                 +
-                 ( CASE WHEN part_pkg.recurtax = 'Y'
-                        THEN cust_bill_pkg.recur
-                        ELSE 0
-                   END
-                 )
-               )
-    ";
-
-  push @where, "(    ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 )
-                  OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 ) )",
-               "( tax != 'Y' OR tax IS NULL )";
-
-} elsif ( $cgi->param('taxable') ) {
-
-  my $setup_taxable = "(
-    CASE WHEN part_pkg.setuptax = 'Y'
-         THEN 0
-         ELSE cust_bill_pkg.setup
-    END
-  )";
-
-  my $recur_taxable = "(
-    CASE WHEN part_pkg.recurtax = 'Y'
-         THEN 0
-         ELSE cust_bill_pkg.recur
-    END
-  )";
-
-  my $exempt = "(
-    SELECT COALESCE( SUM(amount), 0 ) FROM cust_tax_exempt_pkg
-      WHERE cust_tax_exempt_pkg.billpkgnum = cust_bill_pkg.billpkgnum
-  )";
-
-  $count_query =
-    "SELECT COUNT(*), SUM( $setup_taxable + $recur_taxable - $exempt )";
-
-  push @where,
-    #not tax-exempt package (setup or recur)
-    "(
-          ( ( part_pkg.setuptax != 'Y' OR part_pkg.setuptax IS NULL )
-            AND cust_bill_pkg.setup > 0 )
-       OR
-          ( ( part_pkg.recurtax != 'Y' OR part_pkg.recurtax IS NULL )
-            AND cust_bill_pkg.recur > 0 )
-    )",
-    #not a tax_exempt customer
-    "( tax != 'Y' OR tax IS NULL )";
-    #not covered in full by a monthly tax exemption (texas tax)
-    "0 < ( $setup_taxable + $recur_taxable - $exempt )",
-
-} else {
-
-  if ( $use_usage ) {
-    $count_query = "SELECT COUNT(*), ";
-  } else {
-    $count_query = "SELECT COUNT(DISTINCT billpkgnum), ";
+  # specific taxnums
+  if ( $cgi->param('taxnum') ) {
+    my $taxnum_in = join(',', 
+      grep /^\d+$/, $cgi->param('taxnum')
+    );
+    push @where, "cust_main_county.taxnum IN ($taxnum_in)"
+      if $taxnum_in;
   }
 
-  if ( $unearned ) {
-    $count_query .= "SUM( $unearned_base ), SUM( $unearned_sql )";
-  } elsif ( $use_usage eq 'recurring' ) {
-    $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - usage)";
-  } elsif ( $use_usage eq 'usage' ) {
-    $count_query .= "SUM(usage)";
-  } elsif ( scalar( grep( /locationtaxid/, $cgi->param ) ) ) {
-    $count_query .= "SUM( COALESCE(cust_bill_pkg_tax_rate_location.amount, cust_bill_pkg.setup + cust_bill_pkg.recur))";
-  } elsif ( $cgi->param('iscredit') eq 'rate') {
-    $count_query .= "SUM( cust_credit_bill_pkg.amount )";
-  } else {
-    $count_query .= "SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)";
+  # report group (itemdesc)
+  if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
+    my ( $group_op, $group_value ) = ( $1, $2 );
+    if ( $group_op eq '=' ) {
+      #push @where, 'itemdesc LIKE '. dbh->quote($group_value.'%');
+      push @where, 'itemdesc = '. dbh->quote($group_value);
+    } elsif ( $group_op eq '!=' ) {
+      push @where, '( itemdesc != '. dbh->quote($group_value) .' OR itemdesc IS NULL )';
+    } else {
+      die "guru meditation #00de: group_op $group_op\n";
+    }
   }
 
-}
-
-$join_cust =  '        JOIN cust_bill USING ( invnum )
-                  LEFT JOIN cust_main USING ( custnum ) ';
-
-if ( $cgi->param('nottax') ) {
-
-  $join_pkg .=  ' LEFT JOIN cust_pkg USING ( pkgnum )
-                  LEFT JOIN part_pkg USING ( pkgpart )
-                  LEFT JOIN part_pkg AS override
-                    ON pkgpart_override = override.pkgpart ';
-  $join_pkg .= ' LEFT JOIN cust_location USING ( locationnum ) '
-    if $conf->exists('tax-pkg_address');
-
-} elsif ( $cgi->param('istax') ) {
-
-  #false laziness w/report_tax.cgi $taxfromwhere
-  if ( scalar( grep( /locationtaxid/, $cgi->param ) ) ||
-            $cgi->param('iscredit') eq 'rate') {
+  # itemdesc, for some reason
+  if ( $cgi->param('itemdesc') ) {
+    if ( $cgi->param('itemdesc') eq 'Tax' ) {
+      push @where, "(itemdesc='Tax' OR itemdesc is null)";
+    } else {
+      push @where, 'itemdesc='. dbh->quote($cgi->param('itemdesc'));
+    }
+  }
 
-    $join_pkg .=
-      ' LEFT JOIN cust_bill_pkg_tax_rate_location USING ( billpkgnum ) '.
-      ' LEFT JOIN tax_rate_location USING ( taxratelocationnum ) ';
+} # nottax / istax
 
-  } elsif ( $conf->exists('tax-pkg_address') ) {
+# credit
+if ( $cgi->param('credit') ) {
 
-    $join_pkg .= ' LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
-                   LEFT JOIN cust_location              USING ( locationnum ) ';
+  my $credit_sub;
 
-    #quelle kludge, somewhat false laziness w/report_tax.cgi
-    s/cust_pkg\.locationnum/cust_bill_pkg_tax_location.locationnum/g for @where;
-  }
+  if ( $cgi->param('istax') ) {
+    # then we need to group/join by billpkgtaxlocationnum, to get only the 
+    # relevant part of partial taxes
+    my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
+      reason.reason as reason_text, access_user.username AS username_text,
+      billpkgtaxlocationnum, billpkgnum
+    FROM cust_credit_bill_pkg
+      JOIN cust_credit_bill USING (creditbillnum)
+      JOIN cust_credit USING (crednum)
+      LEFT JOIN reason USING (reasonnum)
+      LEFT JOIN access_user USING (usernum)
+    GROUP BY billpkgnum, billpkgtaxlocationnum, reason.reason, 
+      access_user.username";
+
+    if ( $cgi->param('out') ) {
+
+      # find credits that are applied to the line items, but not to 
+      # a cust_bill_pkg_tax_location link
+      $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
+        USING (billpkgnum)";
+      push @where, 'item_credit.billpkgtaxlocationnum IS NULL';
 
-  if ( $cgi->param('iscredit') ) {
-    $join_pkg .= ' JOIN cust_credit_bill_pkg USING ( billpkgnum';
-    if ( $cgi->param('iscredit') eq 'rate' ) {
-      $join_pkg .= ', billpkgtaxratelocationnum )';
-    } elsif ( $conf->exists('tax-pkg_address') ) {
-      $join_pkg .= ', billpkgtaxlocationnum )';
-      push @where, "billpkgtaxratelocationnum IS NULL";
     } else {
-      $join_pkg .= ' )';
-      push @where, "billpkgtaxratelocationnum IS NULL";
-    }
-  }
 
-} else { 
+      # find credits that are applied to the CBPTL links that are 
+      # considered "interesting" by the report criteria
+      $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit
+        USING (billpkgtaxlocationnum)";
 
-  #die?
-  warn "neiether nottax nor istax parameters specified";
-  #same as before?
-  $join_pkg =  ' LEFT JOIN cust_pkg USING ( pkgnum )
-                 LEFT JOIN part_pkg USING ( pkgpart ) ';
+    }
 
-}
+  } else {
+    # then only group by billpkgnum
+    my $credit_sub = "SELECT SUM(cust_credit_bill_pkg.amount) AS credit_amount,
+      reason.reason as reason_text, access_user.username AS username_text,
+      billpkgnum
+    FROM cust_credit_bill_pkg
+      JOIN cust_credit_bill USING (creditbillnum)
+      JOIN cust_credit USING (crednum)
+      LEFT JOIN reason USING (reasonnum)
+      LEFT JOIN access_user USING (usernum)
+    GROUP BY billpkgnum, reason.reason, access_user.username";
+    $join_pkg .= " LEFT JOIN ($credit_sub) AS item_credit USING (billpkgnum)";
+  }
 
-my $where = ' WHERE '. join(' AND ', @where);
-
-if ($use_usage) {
-  $count_query .=
-    " FROM (SELECT cust_bill_pkg.setup, cust_bill_pkg.recur, 
-             ( SELECT COALESCE( SUM(amount), 0 ) FROM cust_bill_pkg_detail
-               WHERE cust_bill_pkg.billpkgnum = cust_bill_pkg_detail.billpkgnum
-             ) AS usage FROM cust_bill_pkg  $join_cust $join_pkg $where
-           ) AS countquery";
-} else {
-  $count_query .= " FROM cust_bill_pkg $join_cust $join_pkg $where";
-}
+  push @where,    'item_credit.billpkgnum IS NOT NULL';
+  push @select,   'item_credit.credit_amount',
+                  'item_credit.username_text',
+                  'item_credit.reason_text';
+  push @peritem,  'credit_amount', 'username_text', 'reason_text';
+  push @peritem_desc, 'Credited', 'By', 'Reason';
+  push @total,    'SUM(credit_amount)';
+  push @total_desc, "$money_char%.2f credited";
+} # if credit
 
-push @select, 'part_pkg.pkg',
-              'part_pkg.freq',
-  unless $cgi->param('istax');
+push @select, 'cust_main.custnum', FS::UI::Web::cust_sql_fields();
 
-push @select, 'cust_main.custnum',
-              FS::UI::Web::cust_sql_fields();
+my $where = join(' AND ', @where);
+$where &&= "WHERE $where";
 
 my $query = {
   'table'     => 'cust_bill_pkg',
@@ -624,25 +557,31 @@ my $query = {
   'hashref'   => {},
   'select'    => join(",\n", @select ),
   'extra_sql' => $where,
-  'order_by'  => 'ORDER BY cust_bill._date, billpkgnum',
+  'order_by'  => 'ORDER BY cust_bill._date, cust_bill_pkg.billpkgnum',
 };
 
-my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
-my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
+my $count_query =
+  'SELECT ' . join(',', @total) .
+  " FROM cust_bill_pkg $join_cust $join_pkg
+  $where";
 
-my $conf = new FS::Conf;
-my $money_char = $conf->config('money_char') || '$';
+shift @total_desc; #the first one is implicit
 
-my $owed_sub = sub {
-  $money_char . shift->get('owed') # owed_recur is not correct here
-};
-my $payment_date_sub = sub {
-  #my $cust_bill_pkg = shift;
-  my @cust_pay = sort { $a->_date <=> $b->_date }
-                      map $_->cust_bill_pay->cust_pay,
-                          shift->cust_bill_pay_pkg('recur') #recur :/
-    or return '';
-  time2str('%b %d %Y', $cust_pay[-1]->_date );
-};
+@peritem_desc = map {emt($_)} @peritem_desc;
+my @peritem_sub = map {
+  my $field = $_;
+  if ($field =~ /_text$/) { # kludge for credit reason/username fields
+    sub {$_[0]->get($field)};
+  } else {
+    sub { sprintf($money_char.'%.2f', $_[0]->get($field)) }
+  }
+} @peritem;
+my @peritem_null = map { '' } @peritem; # placeholders
+my $peritem_align = 'r' x scalar(@peritem);
+
+my $ilink = [ "${p}view/cust_bill.cgi?", 'invnum' ];
+my $clink = [ "${p}view/cust_main.cgi?", 'custnum' ];
 
+warn "\n\nQUERY:\n".Dumper($query)."\n\nCOUNT_QUERY:\n$count_query\n\n"
+  if $cgi->param('debug');
 </%init>
diff --git a/httemplate/search/cust_bill_pkg_referral.html b/httemplate/search/cust_bill_pkg_referral.html
index 3cb434caa..77b486021 100644
--- a/httemplate/search/cust_bill_pkg_referral.html
+++ b/httemplate/search/cust_bill_pkg_referral.html
@@ -146,6 +146,16 @@ if ( @status_where ) {
     ') IN (' . join(',', @status_where) .')';
 }
 
+my @refnum;
+foreach my $refnum ($cgi->param('refnum')) {
+  if ( $refnum =~ /^\d+$/ ) {
+    push @refnum, $refnum;
+  }
+}
+if ( @refnum ) {
+  push @where, 'cust_main.refnum IN ('.join(',', @refnum).')';
+}
+
 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   push @where, "cust_main.agentnum = $1";
 }
diff --git a/httemplate/search/cust_main-zip.html b/httemplate/search/cust_main-zip.html
index c317dc36f..08800d431 100644
--- a/httemplate/search/cust_main-zip.html
+++ b/httemplate/search/cust_main-zip.html
@@ -4,8 +4,8 @@
                  'query'       => $sql_query,
                  'count_query' => $count_sql,
                  'header'      => [ 'Zip code', 'Customers', ],
-                 #'fields'      => [ 'zip', 'num_cust', ],
-                 #'links'       => [ '', sub { 'somewhere'; }  ],
+                 'fields'      => [ 0, 1 ],
+                 'links'       => [ '', $link  ],
              )
 %>
 <%init>
@@ -63,48 +63,36 @@ sub strip_plus4 {
   END";
 }
 
-my( $zip, $czip);
-if ( $cgi->param('column') eq 'ship_zip' ) {
-
-  my $casewhen_noship =
-    "CASE WHEN ( ship_last IS NULL OR ship_last = '' ) THEN ";
-
-  $czip = "$casewhen_noship zip ELSE ship_zip END";
-
-  if ( $cgi->param('ignore_plus4') ) {
-    $zip = $casewhen_noship. strip_plus4('zip').
-                   " ELSE ". strip_plus4('ship_zip'). ' END';
-
-  } else {
-    $zip = $casewhen_noship. fieldorempty('zip').
-                   " ELSE ". fieldorempty('ship_zip'). ' END';
-  }
+$cgi->param('column') =~ /^(bill|ship)$/;
+my $location = $1 || 'bill';
+$location .= '_locationnum';
 
+my $zip;
+if ( $cgi->param('ignore_plus4') ) {
+  $zip = strip_plus4('cust_location.zip');
 } else {
-
-  $czip = 'zip';
-
-  if ( $cgi->param('ignore_plus4') ) {
-    $zip = strip_plus4('zip');
-  } else {
-    $zip = fieldorempty('zip');
-  }
-
+  $zip = fieldorempty('cust_location.zip');
 }
 
 # construct the queries and send 'em off
 
+my $join = "JOIN cust_location ON (cust_main.$location = cust_location.locationnum)";
+
 my $sql_query = 
   "SELECT $zip AS zipcode,
           COUNT(*) AS num_cust
      FROM cust_main
+     $join
      $where
      GROUP BY zipcode
-     ORDER BY num_cust DESC
+     ORDER BY num_cust DESC, $zip ASC
   ";
 
-my $count_sql = "select count(distinct $czip) from cust_main $where";
+my $count_sql = 
+  "SELECT COUNT(DISTINCT cust_location.zip)
+    FROM cust_main $join $where";
 
-# XXX should link...
+my $link = [ $p.'search/cust_main.html?zip=', 
+             sub { $_[0]->[0] } ];
 
 </%init>
diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi
index 859ef04e6..7c3ad3384 100755
--- a/httemplate/search/cust_main.cgi
+++ b/httemplate/search/cust_main.cgi
@@ -81,13 +81,8 @@
       <TR>
         <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('#') |h %></TH>
         <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Status') |h %></TH>
-        <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('(bill) name') |h %></TH>
-        <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('company') |h %></TH>
-
-%if ( defined dbdef->table('cust_main')->column('ship_last') ) {
-      <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('(service) name') |h %></TH>
-      <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('company') |h %></TH>
-%}
+        <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Name') |h %></TH>
+        <TH CLASS="grid" BGCOLOR="#cccccc"><% mt('Company') |h %></TH>
 
 %foreach my $addl_header ( @addl_headers ) {
     <TH CLASS="grid" BGCOLOR="#cccccc"><% $addl_header %></TH>
@@ -172,25 +167,6 @@
         <% $pcompany %>
       </TD>
 
-%    if ( defined dbdef->table('cust_main')->column('ship_last') ) {
-%      my($ship_last,$ship_first,$ship_company)=(
-%        $cust_main->ship_last || $cust_main->getfield('last'),
-%        $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first,
-%        $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company,
-%      );
-%      my $pship_company = $ship_company
-%        ? qq!<A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A>!
-%        : '<FONT SIZE=-1>&nbsp;</FONT>';
-%      
-
-      <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan %>>
-        <A HREF="<% $view %>"><FONT SIZE=-1><% "$ship_last, $ship_first" %></FONT></A>
-      </TD>
-      <TD CLASS="grid" BGCOLOR="<% $bgcolor %>" ROWSPAN=<% $rowspan %>>
-        <% $pship_company %></A>
-      </TD>
-% }
-%
 %    foreach my $addl_col ( @addl_cols ) { 
 % if ( $addl_col eq 'tickets' ) { 
 % if ( @custom_priorities ) { 
@@ -492,9 +468,10 @@ if ( $cgi->param('browse')
   if ( $cgi->param('search_cust') ) {
     $sortby = \*company_sort;
     $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )";
-    push @cust_main, smart_search( 'search' => scalar($cgi->param('search_cust')),
-                                   'no_fuzzy_on_exact' => 1, #pref?
-                                 );
+    push @cust_main, smart_search(
+      'search'            => scalar($cgi->param('search_cust')),
+      'no_fuzzy_on_exact' => ! $curuser->option('enable_fuzzy_on_exact'),
+    );
   }
 
   @cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main
diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html
index e164b98f4..fa79b4dfb 100755
--- a/httemplate/search/cust_main.html
+++ b/httemplate/search/cust_main.html
@@ -41,7 +41,7 @@ my %search_hash = ();
 
 #scalars
 my @scalars = qw (
-  agentnum status address paydate_year paydate_month invoice_terms
+  agentnum status address zip paydate_year paydate_month invoice_terms
   no_censustract with_geocode custbatch usernum
   cancelled_pkgs
   cust_fields flattened_pkgs
@@ -61,7 +61,7 @@ for my $param (qw( classnum refnum payby tagnum )) {
 # parse dates
 ###
 
-foreach my $field (qw( signupdate birthdate spouse_birthdate )) {
+foreach my $field (qw( signupdate birthdate spouse_birthdate anniversary_date )) {
 
   my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field);
 
diff --git a/httemplate/search/cust_pay_pending.html b/httemplate/search/cust_pay_pending.html
index 8b7350853..2afce0ce9 100755
--- a/httemplate/search/cust_pay_pending.html
+++ b/httemplate/search/cust_pay_pending.html
@@ -5,7 +5,6 @@
                 'name_verb'     => 'pending',
                 'disable_link'  => 1,
                 'disable_by'    => 1, #add otaker to cust_pay_pending?
-                'html_init'     => include('/elements/init_overlib.html'),
                 'addl_header'   => [ 'Time', 'Payment Status', ],
                 'addl_fields'   => [ sub { time2str('%r', shift->_date ) },
                                      $status_sub,
diff --git a/httemplate/search/cust_tax_exempt_pkg.cgi b/httemplate/search/cust_tax_exempt_pkg.cgi
index 3a5155ae8..1b767f846 100644
--- a/httemplate/search/cust_tax_exempt_pkg.cgi
+++ b/httemplate/search/cust_tax_exempt_pkg.cgi
@@ -103,7 +103,7 @@ my $join = "
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('View customer tax exemptions');
 
-my @where = ();
+my @where = ("exempt_monthly = 'Y'");
 
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 if ( $beginning || $ending ) {
@@ -121,6 +121,7 @@ if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
 }
 
 if ( $cgi->param('out') ) {
+  # wtf? how would you ever get exemptions on a non-taxable package location?
 
   push @where, "
     0 = (
@@ -151,6 +152,11 @@ if ( $cgi->param('out') ) {
   push @where, 'taxclass = '. dbh->quote( $cgi->param('taxclass') )
     if $cgi->param('taxclass');
 
+} elsif ( $cgi->param('taxnum') ) {
+
+  my $taxnum_in = join(',', grep /^\d+$/, $cgi->param('taxnum') );
+  push @where, "taxnum IN ($taxnum_in)" if $taxnum_in;
+
 }
 
 my $where = scalar(@where) ? 'WHERE '.join(' AND ', @where) : '';
diff --git a/httemplate/search/elements/cust_pay_batch_top.html b/httemplate/search/elements/cust_pay_batch_top.html
index 739e65b50..1dcc37ac1 100644
--- a/httemplate/search/elements/cust_pay_batch_top.html
+++ b/httemplate/search/elements/cust_pay_batch_top.html
@@ -33,6 +33,7 @@ Download batch in format <SELECT NAME="format">
               'action'    => "${p}misc/upload-batch.cgi",
               'num_files' => 1,
               'fields'    => [ 'batchnum', 'format', 'gatewaynum' ],
+              'url'       => $cgi->self_url,
               'message'   => 'Batch results uploaded.',
 ) %>
 Upload results<BR></TR>
@@ -87,7 +88,7 @@ Batch is <% $statustext{$status} %><BR>
 
 <%def .select_gateway>
 % if ( $show_gateways ) {
- or from gateway
+ or for gateway
 <& /elements/select-table.html,
   empty_label => ' ',
   field     => 'gatewaynum',
diff --git a/httemplate/search/elements/search-csv.html b/httemplate/search/elements/search-csv.html
index 9eb1b66d1..90230e6dc 100644
--- a/httemplate/search/elements/search-csv.html
+++ b/httemplate/search/elements/search-csv.html
@@ -27,10 +27,21 @@
 %     $csv->combine(@$row); #or die $csv->status;
 %   }
 %
-%      
 <% $csv->string %>\
 %
 % }
+% 
+% if ( $opt{'footer'} and !$opt{'no_csv_header'} ) {
+%   my @footer;
+%   foreach my $item (@{ $opt{'footer'} }) {
+%     if ( ref($item) eq 'CODE' ) {
+%       $item = &{$item}();
+%     }
+%     push @footer, $item;
+%   }
+%   $csv->combine(@footer);
+<% $csv->string %>\
+% }
 <%init>
 
 my %args = @_;
diff --git a/httemplate/search/elements/search-html.html b/httemplate/search/elements/search-html.html
index 53167c26e..d7e81282b 100644
--- a/httemplate/search/elements/search-html.html
+++ b/httemplate/search/elements/search-html.html
@@ -134,9 +134,9 @@
 %                 and !$opt{'disable_download'}
 %                 and $type ne 'html-print' ) { 
 
-              <TD ALIGN="right">
+              <TD ALIGN="right" CLASS="noprint">
 
-                Download full results<BR>
+                <% $opt{'download_label'} || 'Download full results' %><BR>
 
 %               $cgi->param('_type', "$xlsname.xls" ); 
                 as <A HREF="<% "$self_url?". $cgi->query_string %>">Excel spreadsheet</A><BR>
@@ -337,6 +337,11 @@
 %                       map {
 %                             if ( ref($_) eq 'CODE' ) {
 %                               &{$_}($row);
+%                             } elsif ( ref($row) eq 'ARRAY' and 
+%                                       $_ =~ /^\d+$/ ) {
+%                             # for the 'straight SQL' case: specify fields
+%                             # by position
+%                               $row->[$_];
 %                             } else {
 %                               $row->$_();
 %                             }
@@ -345,7 +350,8 @@
 %
 %                     ) {
 %
-%                       my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid';
+%#                       my $class = ( $field =~ /^<TABLE/i ) ? 'inv' : 'grid';
+%                       my $class = 'grid';
 %
 %                       my $align = $aligns ? shift @$aligns : '';
 %                       $align = " ALIGN=$align" if $align;
diff --git a/httemplate/search/elements/search-xls.html b/httemplate/search/elements/search-xls.html
index 09dbe46e0..94d88b096 100644
--- a/httemplate/search/elements/search-xls.html
+++ b/httemplate/search/elements/search-xls.html
@@ -55,6 +55,10 @@ my $writer = sub {
   # Wrapper for $worksheet->write.
   # Do any massaging of the value/format here.
   my ($r, $c, $value, $format) = @_;
+  # convert HTML entities
+  # both Spreadsheet::WriteExcel and Excel::Writer::XLSX accept UTF-8 strings
+  $value = decode_entities($value);
+
   if ( $value =~ /^\Q$money_char\E(-?\d+\.?\d*)$/ ) {
     # Currency: strip the symbol, clone the requested format,
     # and format it for currency
@@ -130,6 +134,17 @@ foreach my $row ( @$rows ) {
 
 }
 
+if ( $opt{'footer'} ) {
+  $r++;
+  $c = 0;
+  foreach my $item (@{ $opt{'footer'} }) {
+    if ( ref($item) eq 'CODE' ) {
+      $item = &{$item}();
+    }
+    $writer->( $r, $c++, $item, $header_format );
+  }
+}
+
 $workbook->close();# or die "Error creating .xls file: $!";
 
 http_header('Content-Length' => length($data) );
diff --git a/httemplate/search/elements/search.html b/httemplate/search/elements/search.html
index 9bc66b6fa..eca68a2f8 100644
--- a/httemplate/search/elements/search.html
+++ b/httemplate/search/elements/search.html
@@ -162,7 +162,11 @@ Example:
     # Excel-specific listref of ( hashrefs or coderefs )
     # each hashref: http://search.cpan.org/dist/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#Format_methods_and_Format_properties
     'xls_format' => => [],
-    
+   
+
+    # miscellany
+   'download_label' => 'Download this report',
+                        # defaults to 'Download full results' 
   &>
 
 </%doc>
diff --git a/httemplate/search/quotation.html b/httemplate/search/quotation.html
new file mode 100755
index 000000000..259c85c22
--- /dev/null
+++ b/httemplate/search/quotation.html
@@ -0,0 +1,268 @@
+<& elements/search.html,
+                 'title'       => emt('Quotation Search Results'),
+                 'html_init'   => $html_init,
+                 'menubar'     => $menubar,
+                 'name'        => 'quotations',
+                 'query'       => $sql_query,
+                 'count_query' => $count_query,
+                 'count_addl'  => $count_addl,
+                 'redirect'    => $link,
+                 'header'      => [ emt('Quotation #'),
+                                    emt('Setup'),
+                                    emt('Recurring'),
+                                    emt('Date'),
+                                    emt('Prospect'),
+                                    emt('Customer'),
+                                  ],
+                 'fields'      => [
+                   'quotationnum',
+                   sub { $money_char. shift->total_setup },
+                   sub { $money_char. shift->total_recur },
+                   sub { time2str('%b %d %Y', shift->_date ) },
+                   sub { my $prospect_main = shift->prospect_main;
+                         $prospect_main ? $prospect_main->name : '';
+                       },
+                   sub { my $cust_main = shift->cust_main;
+                         $cust_main ? $cust_main->name : '';
+                       },
+                   #\&FS::UI::Web::cust_fields,
+                 ],
+                 'sort_fields' => [
+                   'quotationnum',
+                   '', #FS::quotation->total_setup_sql,
+                   '', #FS::quotation->total_recur_sql,
+                   '_date',
+                   '',
+                   '',
+                 ],
+                 'align' => 'rrrrll', #.FS::UI::Web::cust_aligns(),
+                 'links' => [
+                   $link,
+                   $link,
+                   $link,
+                   $link,
+                   $prospect_link,
+                   $cust_link,
+                   #( map { $_ ne 'Cust. Status' ? $clink : '' }
+                   #      FS::UI::Web::cust_header()
+                   #),
+                 ],
+#                 'color' => [ 
+#                              '',
+#                              '',
+#                              '',
+#                              '',
+#                              '',
+#                              FS::UI::Web::cust_colors(),
+#                            ],
+#                 'style' => [ 
+#                              '',
+#                              '',
+#                              '',
+#                              '',
+#                              '',
+#                              FS::UI::Web::cust_styles(),
+#                            ],
+&>
+<%init>
+
+my $curuser = $FS::CurrentUser::CurrentUser;
+
+die "access denied"
+  unless $curuser->access_right('List quotations');
+
+my $join_prospect_main = 'LEFT JOIN prospect_main USING ( prospectnum )';
+my $join_cust_main = 'LEFT JOIN cust_main ON ( quotation.custnum = cust_main.custnum )';
+
+#here is the agent virtualization
+my $agentnums_sql = ' (    '. $curuser->agentnums_sql( table=>'prospect_main' ).
+                    '   OR '. $curuser->agentnums_sql( table=>'cust_main' ).
+                    ' )    ';
+
+my( $count_query, $sql_query );
+my $count_addl = '';
+my %search;
+
+#if ( $cgi->param('quotationnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) {
+#
+#  my $where = "WHERE quotationnum = $2 AND $agentnums_sql";
+#  
+#  $count_query = "SELECT COUNT(*) FROM quotation $join_prospect_main $join_cust_main $where";
+#
+#  $sql_query = {
+#    'table'     => 'quotation',
+#    'addl_from' => "$join_prospect_main $join_cust_main",
+#    'hashref'   => {},
+#    'extra_sql' => $where,
+#  };
+#
+#} else {
+
+  #some false laziness w/cust_bill::re_X
+  my $orderby = 'ORDER BY quotation._date';
+
+  if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+    $search{'agentnum'} = $1;
+  }
+
+#  if ( $cgi->param('refnum') =~ /^(\d+)$/ ) {
+#    $search{'refnum'} = $1;
+#  }
+
+  if ( $cgi->param('prospectnum') =~ /^(\d+)$/ ) {
+    $search{'prospectnum'} = $1;
+  }
+
+  if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
+    $search{'custnum'} = $1;
+  }
+
+  # begin/end/beginning/ending
+  my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, '');
+  $search{'_date'} = [ $beginning, $ending ]
+    unless $beginning == 0 && $ending == 4294967295;
+
+  if ( $cgi->param('quotationnum_min') =~ /^\s*(\d+)\s*$/ ) {
+    $search{'quotationnum_min'} = $1;
+  }
+  if ( $cgi->param('quotationnum_max') =~ /^\s*(\d+)\s*$/ ) {
+    $search{'quotationnum_max'} = $1;
+  }
+
+  #amounts
+  $search{$_} = [ FS::UI::Web::parse_lt_gt($cgi, $_) ]
+    foreach qw( total_setup total_recur );
+
+#  my($query) = $cgi->keywords;
+#  if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) {
+#    $search{'open'} = 1 if $1;
+#    ($search{'days'}, my $field) = ($2, $3);
+#    $field = "_date" if $field eq 'date';
+#    $orderby = "ORDER BY cust_bill.$field";
+#  }
+
+#  if ( $cgi->param('newest_percust') ) {
+#    $search{'newest_percust'} = 1;
+#    $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'";
+#  }
+
+  my $extra_sql = ' WHERE '. FS::quotation->search_sql_where( \%search );
+
+  unless ( $count_query ) {
+    $count_query = 'SELECT COUNT(*)';
+  }
+  $count_query .=  " FROM quotation $join_prospect_main $join_cust_main $extra_sql";
+
+  $sql_query = {
+    'table'     => 'quotation',
+    'addl_from' => "$join_prospect_main $join_cust_main",
+    'hashref'   => {},
+    'select'    => join(', ',
+                     'quotation.*',
+                     #( map "cust_main.$_", qw(custnum last first company) ),
+                     'prospect_main.prospectnum as prospect_main_prospectnum',
+                     'cust_main.custnum as cust_main_custnum',
+                     #FS::UI::Web::cust_sql_fields(),
+                   ),
+    'extra_sql' => $extra_sql,
+    'order_by'  => $orderby,
+  };
+
+#}
+
+my $link  = [ "${p}view/quotation.html?", 'quotationnum', ];
+my $prospect_link = sub {
+  my $quotation = shift;
+  $quotation->prospect_main_prospectnum
+    ? [ "${p}view/prospect_main.html?", 'prospectnum' ]
+    : '';
+};
+
+my $cust_link = sub {
+  my $quotation = shift;
+  $quotation->cust_main_custnum
+    ? [ "${p}view/cust_main.cgi?", 'custnum' ]
+    : '';
+};
+
+my $conf = new FS::Conf;
+my $money_char = $conf->config('money_char') || '$';
+
+my $html_init = join("\n", map {
+ ( my $action = $_ ) =~ s/_$//;
+ include('/elements/progress-init.html',
+           $_.'form',
+           [ keys %search ],
+           "../misc/${_}invoices.cgi",
+           { 'message' => "Invoices re-${action}ed" }, #would be nice to show the number of them, but...
+           $_, #key
+        ),
+ qq!<FORM NAME="${_}form">!,
+ ( map { my $f = $_;
+         my @values = ref($search{$f}) ? @{ $search{$f} } : $search{$f};
+         map qq!<INPUT TYPE="hidden" NAME="$f" VALUE="$_">!, @values;
+       }
+       keys %search
+ ),
+ qq!</FORM>!
+} qw( print_ email_ fax_ ftp_ spool_ ) ). 
+
+'<SCRIPT TYPE="text/javascript">
+
+function confirm_print_process() {
+  if ( ! confirm('.js_mt("Are you sure you want to reprint these invoices?").') ) {
+    return;
+  }
+  print_process();
+}
+function confirm_email_process() {
+  if ( ! confirm('.js_mt("Are you sure you want to re-email these invoices?").') ) {
+    return;
+  }
+  email_process();
+}
+function confirm_fax_process() {
+  if ( ! confirm('.js_mt("Are you sure you want to re-fax these invoices?").') ) {
+    return;
+  }
+  fax_process();
+}
+function confirm_ftp_process() {
+  if ( ! confirm('.js_mt("Are you sure you want to re-FTP these invoices?").') ) {
+    return;
+  }
+  ftp_process();
+}
+function confirm_spool_process() {
+  if ( ! confirm('.js_mt("Are you sure you want to re-spool these invoices?").') ) {
+    return;
+  }
+  spool_process();
+}
+
+</SCRIPT>';
+
+my $menubar = [];
+
+#if ( $curuser->access_right('Resend quotations') ) {
+#
+#  push @$menubar, emt('Print these invoices') =>
+#                    "javascript:confirm_print_process()",
+#                  emt('Email these invoices') =>
+#                    "javascript:confirm_email_process()";
+#
+#  push @$menubar, emt('Fax these invoices') =>
+#                    "javascript:confirm_fax_process()"
+#    if $conf->exists('hylafax');
+#
+#  push @$menubar, emt('FTP these invoices') =>
+#                    "javascript:confirm_ftp_process()"
+#    if $conf->exists('cust_bill-ftpformat');
+#
+#  push @$menubar, emt('Spool these invoices') =>
+#                    "javascript:confirm_spool_process()"
+#    if $conf->exists('cust_bill-spoolformat');
+#
+#}
+
+</%init>
diff --git a/httemplate/search/report_477.html b/httemplate/search/report_477.html
index c9d97c5eb..f593a94d8 100755
--- a/httemplate/search/report_477.html
+++ b/httemplate/search/report_477.html
@@ -17,6 +17,18 @@
                )
     %>
 
+%   # not tr-select-state, we only want to choose from among those that 
+%   # have customers
+    <& /elements/tr-select-table.html,
+        'label'         => 'State',
+        'field'         => 'state',
+        'table'         => 'cust_location',
+        'name_col'      => 'state',
+        'value_col'     => 'state',
+        'disable_empty' => 1,
+        'records'       => \@states,
+    &>
+
     <% include( '/elements/tr-select-pkg_class.html',
                    'multiple'       => 1,
                    'empty_label' => '(empty class)',
@@ -252,4 +264,10 @@
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List packages');
 
+my @states = qsearch({
+  'table'   => 'cust_location',
+  'select'  => 'DISTINCT(state)',
+  'hashref' => { 'country' => 'US' }, # 477 report isn't relevant elsewhere
+});
+
 </%init>
diff --git a/httemplate/search/report_cdr.html b/httemplate/search/report_cdr.html
index e3418a7d4..0e1693b9c 100644
--- a/httemplate/search/report_cdr.html
+++ b/httemplate/search/report_cdr.html
@@ -24,9 +24,12 @@
       <SELECT NAME="freesidestatus">
         <OPTION VALUE="">(all)</OPTION>
         <OPTION VALUE="NULL">unprocessed</OPTION>
+%# <OPTION VALUE="processing-tiered">processing</OPTION>
         <OPTION VALUE="rated">prerated
-        <OPTION VALUE="done">processed</OPTION>
-        <OPTION VALUE="failed">skipped</OPTION>
+        <OPTION VALUE="no-charge">processed (included)</OPTION>
+        <OPTION VALUE="done">processed (billed)</OPTION>
+        <OPTION VALUE="skipped">skipped</OPTION>
+        <OPTION VALUE="failed">failed</OPTION>
       </SELECT>
     </TD>
   </TR>
diff --git a/httemplate/search/report_cust_bill_pkg_referral.html b/httemplate/search/report_cust_bill_pkg_referral.html
index ff2caa1fa..b4716d4fc 100644
--- a/httemplate/search/report_cust_bill_pkg_referral.html
+++ b/httemplate/search/report_cust_bill_pkg_referral.html
@@ -18,6 +18,11 @@
   'disable_empty' => 1,
 &>
 
+<& /elements/tr-select-part_referral.html,
+  'multiple'      => 1,
+  'disable_empty' => 1,
+&>
+
 <& /elements/tr-select-pkg_class.html,
   'pre_options' => [ '' => 'all', '0' => '(empty class)' ],
   'disable_empty' => 1,
diff --git a/httemplate/search/report_cust_main-zip.html b/httemplate/search/report_cust_main-zip.html
index 00cb9ed2c..8bad332a9 100644
--- a/httemplate/search/report_cust_main-zip.html
+++ b/httemplate/search/report_cust_main-zip.html
@@ -8,8 +8,8 @@
         <TD ALIGN="right">Billing or service zip</TD>
         <TD>
           <SELECT NAME="column">
-            <OPTION VALUE="zip">Billing zip
-            <OPTION VALUE="ship_zip">Service zip
+            <OPTION VALUE="bill">Billing zip
+            <OPTION VALUE="ship">Service zip
           </SELECT>
         </TD>
       </TR>
diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html
index 39cf695d8..3e7181d4f 100755
--- a/httemplate/search/report_cust_main.html
+++ b/httemplate/search/report_cust_main.html
@@ -28,13 +28,19 @@
     <& /elements/tr-select-part_referral.html,
                   'label'        => emt('Advertising Source'),
                   'multiple'     => 1,
-                  'all_selected' => 1,
+                  #no, causes customers with disabled ones to disappear
+                  #'all_selected' => 1,
     &>
 
     <TR>
       <TD ALIGN="right" VALIGN="center"><% mt('Address') |h %></TD>
       <TD><INPUT TYPE="text" NAME="address" SIZE=54></TD>
     </TR>
+    
+    <TR>
+      <TD ALIGN="right" VALIGN="center"><% mt('Zip') |h %></TD>
+      <TD><INPUT TYPE="text" NAME="zip" SIZE=12></TD>
+    </TR>
 
     <TR>
         <TD ALIGN="right" VALIGN="center"><% mt('Signup date') |h %></TD>
@@ -76,6 +82,20 @@
       </TR>
 %   }
 
+%    if ( $conf->exists('cust_main-enable_anniversary_date') ) {
+      <TR>
+          <TD ALIGN="right" VALIGN="center"><% mt('Anniversary Date') |h %></TD>
+          <TD>
+          <TABLE>
+              <& /elements/tr-input-beginning_ending.html,
+                        prefix   => 'anniversary_date',
+                        layout   => 'horiz',
+              &>
+          </TABLE>
+          </TD>
+      </TR>
+%   }
+
     <& /elements/tr-select-cust_tag.html,
                   'cgi'		 => $cgi,
                   'is_report'    => 1,
diff --git a/httemplate/search/report_quotation.html b/httemplate/search/report_quotation.html
new file mode 100644
index 000000000..1be904dc3
--- /dev/null
+++ b/httemplate/search/report_quotation.html
@@ -0,0 +1,75 @@
+<& /elements/header.html, mt($title, @title_arg) &>
+
+<FORM ACTION="quotation.html" METHOD="GET">
+<INPUT TYPE="hidden" NAME="magic" VALUE="_date">
+<INPUT TYPE="hidden" NAME="prospectnum" VALUE="<% $prospectnum %>">
+<INPUT TYPE="hidden" NAME="custnum" VALUE="<% $custnum %>">
+
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0
+
+% unless ( $custnum ) {
+  <& /elements/tr-select-agent.html,
+                 'curr_value'    => scalar( $cgi->param('agentnum') ),
+                 'label'         => emt('Quotations for agent: '),
+                 'disable_empty' => 0,
+  &>
+% }
+
+  <& /elements/tr-input-beginning_ending.html &>
+
+  <& /elements/tr-input-lessthan_greaterthan.html,
+                label   => emt('Setup'),
+                field   => 'total_setup',
+  &>
+
+  <& /elements/tr-input-lessthan_greaterthan.html,
+                label   => emt('Recurring'),
+                field   => 'total_recur',
+  &>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+
+</FORM>
+
+<& /elements/footer.html &>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('List quotations');
+
+my $conf = new FS::Conf;
+
+my $title = 'Quotation Report';
+#false laziness w/report_cust_pkg.html
+my @title_arg = ();
+
+my $prospectnum = '';
+my $prospect_main = '';
+if ( $cgi->param('prospectnum') =~ /^(\d+)$/ ) {
+  $prospectnum = $1;
+  $prospect_main = qsearchs({
+    'table'     => 'prospect_main', 
+    'hashref'   => { 'prospectnum' => $prospectnum },
+    'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql,
+  }) or die "unknown prospectnum $prospectnum";
+  $title .= ': [_1]';
+  push @title_arg, $prospect_main->name;
+}
+
+my $custnum = '';
+my $cust_main = '';
+if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
+  $custnum = $1;
+  $cust_main = qsearchs({
+    'table'     => 'cust_main', 
+    'hashref'   => { 'custnum' => $custnum },
+    'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql,
+  }) or die "unknown custnum $custnum";
+  $title .= ': [_1]';
+  push @title_arg, $cust_main->name;
+}
+
+</%init>
diff --git a/httemplate/search/report_rt_ticket.html b/httemplate/search/report_rt_ticket.html
index f0d7a4200..a4ceaa6a4 100644
--- a/httemplate/search/report_rt_ticket.html
+++ b/httemplate/search/report_rt_ticket.html
@@ -59,7 +59,6 @@ if ( @pkgparts ) {
 }
 
 # get a list of TimeValue-type custom fields
-RT::Init();
 my $CurrentUser = RT::CurrentUser->new();
 $CurrentUser->LoadByName($FS::CurrentUser::CurrentUser->username);
 die "RT not configured" unless $CurrentUser->id;
diff --git a/httemplate/search/report_sqlradius_usage.html b/httemplate/search/report_sqlradius_usage.html
new file mode 100644
index 000000000..01215e834
--- /dev/null
+++ b/httemplate/search/report_sqlradius_usage.html
@@ -0,0 +1,40 @@
+<& /elements/header.html, mt($title) &>
+
+<FORM ACTION="sqlradius_usage.html" METHOD="GET">
+
+<TABLE BGCOLOR="#cccccc" CELLSPACING=0
+
+<& /elements/tr-select-agent.html,
+  'empty_label'   => 'all',
+&>
+
+% my @exporttypes = map { "'$_'" } qw(sqlradius broadband_sqlradius);
+<& /elements/tr-select-table.html,
+  'label'         => 'Export',
+  'table'         => 'part_export',
+  'name_col'      => 'label',
+  'hashref'       => {},
+  'extra_sql'     => ' WHERE exporttype IN('.join(',', @exporttypes).')',
+  'disable_empty' => 1,
+  'order_by'      => 'ORDER BY exportnum',
+&>
+
+<& /elements/tr-input-beginning_ending.html &>
+
+</TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="<% mt('Get Report') |h %>">
+
+</FORM>
+
+<& /elements/footer.html &>
+<%init>
+
+die "access denied"
+  unless $FS::CurrentUser::CurrentUser->access_right('Usage: RADIUS sessions');
+  # yes?
+
+my $title = 'Data Usage Report';
+
+</%init>
diff --git a/httemplate/search/report_tax.cgi b/httemplate/search/report_tax.cgi
index 2786f571b..42a52d154 100755
--- a/httemplate/search/report_tax.cgi
+++ b/httemplate/search/report_tax.cgi
@@ -60,9 +60,9 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea
 %   my $link = '';
 %   if ( $region->{'label'} eq $out ) {
 %     $link = ';out=1';
-%   } else {
-%     $link = ';'. $region->{'url_param'}
-%       if $region->{'url_param'};
+%   } elsif ( $region->{'taxnums'} ) {
+%     # might be nicer to specify this as country:state:city
+%     $link = ';'.join(';', map { "taxnum=$_" } @{ $region->{'taxnums'} });
 %   }
 %
 %   if ( $bgcolor eq $bgcolor1 ) {
@@ -71,15 +71,12 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea
 %     $bgcolor = $bgcolor1;
 %   }
 %
-%   #my $diff = 0;
 %   my $hicolor = $bgcolor;
 %   unless ( $cgi->param('show_taxclasses') ) {
 %     my $diff = abs(   sprintf( '%.2f', $region->{'owed'} )
 %                     - sprintf( '%.2f', $region->{'tax'}  )
 %                   );
 %     if ( $diff > 0.02 ) {
-%     #  $hicolor = $hicolor eq '#eeeeee' ? '#eeee66' : '#ffff99';
-%     #} elsif ( $diff ) {
 %       $hicolor = $hicolor eq '#eeeeee' ? '#eeee99' : '#ffffcc';
 %     }
 %   }
@@ -94,16 +91,19 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea
       <<%$td%>><% $region->{'label'} %></TD>
       <<%$td%> ALIGN="right">
         <A HREF="<% $baselink. $link %>;nottax=1"
-        ><% &$money_sprintf( $region->{'total'} ) %></A>
+        ><% &$money_sprintf( $region->{'sales'} ) %></A>
       </TD>
+%     if ( $region->{'label'} eq $out ) {
+      <<%$td%> COLSPAN=12></TD>
+%     } else { #not $out
       <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
       <<%$td%> ALIGN="right">
-        <A HREF="<% $baselink. $link %>;nottax=1;cust_tax=Y"
+        <A HREF="<% $baselink. $link %>;nottax=1;exempt_cust=Y"
         ><% &$money_sprintf( $region->{'exempt_cust'} ) %></A>
       </TD>
       <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
       <<%$td%> ALIGN="right">
-        <A HREF="<% $baselink. $link %>;nottax=1;pkg_tax=Y"
+        <A HREF="<% $baselink. $link %>;nottax=1;exempt_pkg=Y"
         ><% &$money_sprintf( $region->{'exempt_pkg'} ) %></A>
       </TD>
       <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
@@ -122,12 +122,24 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea
       <<%$tdh%> ALIGN="right">
         <% &$money_sprintf( $region->{'owed'} ) %>
       </TD>
-
-% unless ( $cgi->param('show_taxclasses') ) { 
+%   } # if !$out
+%   unless ( $cgi->param('show_taxclasses') ) { 
 %       my $invlink = $region->{'url_param_inv'}
 %                       ? ';'. $region->{'url_param_inv'}
 %                       : $link;
 
+%     if ( $region->{'label'} eq $out ) {
+        <<%$td%> ALIGN="right">
+          <A HREF="<% $baselink. $invlink %>;istax=1"
+          ><% &$money_sprintf_nonzero( $region->{'tax'} ) %></A>
+        </TD>
+        <<%$td%>></TD>
+        <<%$td%> ALIGN="right">
+          <A HREF="<% $creditlink. $invlink %>;istax=1"
+          ><% &$money_sprintf_nonzero( $region->{'credit'} ) %></A>
+        </TD>
+        <<%$td%> COLSPAN=2></TD>
+%     } else { #not $out
         <<%$tdh%> ALIGN="right">
           <A HREF="<% $baselink. $invlink %>;istax=1"
           ><% &$money_sprintf( $region->{'tax'} ) %></A>
@@ -141,7 +153,8 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea
         <<%$tdh%> ALIGN="right">
           <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %>
         </TD>
-% } 
+%   }
+% } # not $out
 
     </TR>
 % } 
@@ -190,6 +203,18 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea
 
       <TR>
         <<%$td%>><% $region->{'label'} %></TD>
+%     if ( $region->{'label'} eq $out ) {
+        <<%$td%> ALIGN="right">
+          <A HREF="<% $baselink. $invlink %>;istax=1"
+          ><% &$money_sprintf_nonzero( $region->{'tax'} ) %></A>
+        </TD>
+        <<%$td%>></TD>
+        <<%$td%> ALIGN="right">
+          <A HREF="<% $creditlink. $invlink %>;istax=1"
+          ><% &$money_sprintf_nonzero( $region->{'credit'} ) %></A>
+        </TD>
+        <<%$td%> COLSPAN=2></TD>
+%     } else { #not $out
         <<%$td%> ALIGN="right">
           <A HREF="<% $baselink. $link %>;istax=1"
           ><% &$money_sprintf( $region->{'tax'} ) %></A>
@@ -204,70 +229,52 @@ as <A HREF="<% $p.'search/report_tax-xls.cgi?'.$cgi->query_string%>">Excel sprea
           <% &$money_sprintf( $region->{'tax'} - $region->{'credit'} ) %>
         </TD>
       </TR>
-
-% } 
-
-% if ( $bgcolor eq $bgcolor1 ) {
-%   $bgcolor = $bgcolor2;
-% } else {
-%   $bgcolor = $bgcolor1;
-% }
-% my $td = qq(TD CLASS="grid" BGCOLOR="$bgcolor");
-
-  <TR>
-   <<%$td%>>Total</TD>
-   <<%$td%> ALIGN="right">
-     <A HREF="<% $baselink %>;istax=1"
-     ><% &$money_sprintf( $tot_tax ) %></A>
-   </TD>
-        <<%$td%>><FONT SIZE="+1"><B> - </B></FONT></TD>
-   <<%$td%> ALIGN="right">
-     <A HREF="<% $creditlink %>;istax=1"
-     ><% &$money_sprintf( $tot_credit ) %></A>
-   </TD>
-        <<%$td%>><FONT SIZE="+1"><B> = </B></FONT></TD>
-   <<%$td%> ALIGN="right">
-     <% &$money_sprintf( $tot_tax - $tot_credit ) %>
-   </TD>
-  </TR>
+%     } # if $out
+%   } #foreach $region
 
   </TABLE>
 
-% } 
+% } # if show_taxclasses
 
 <% include('/elements/footer.html') %>
 
 <%init>
 
-my $DEBUG = $cgi->param('debug') || 0;
-
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('Financial reports');
 
+my $DEBUG = $cgi->param('debug') || 0;
+
 my $conf = new FS::Conf;
 
-my $user = getotaker;
+my $out = 'Out of taxable region(s)';
+
+my %label_opt = ( out => 1 ); #enable 'Out of Taxable Region' label
+$label_opt{no_city} = 1     unless $cgi->param('show_cities');
+$label_opt{no_taxclass} = 1 unless $cgi->param('show_taxclasses');
 
 my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi);
 
 my $join_cust =     '     JOIN cust_bill      USING ( invnum  ) 
                       LEFT JOIN cust_main     USING ( custnum ) ';
+
 my $join_cust_pkg = $join_cust.
                     ' LEFT JOIN cust_pkg      USING ( pkgnum  )
-                      LEFT JOIN part_pkg      USING ( pkgpart ) 
-                      LEFT JOIN cust_location 
-                        ON ( cust_location.locationnum = ' .
-                        FS::cust_pkg->tax_locationnum_sql . ' )';
+                      LEFT JOIN part_pkg      USING ( pkgpart ) ';
 
 my $from_join_cust_pkg = " FROM cust_bill_pkg $join_cust_pkg "; 
 
-my $where = "WHERE _date >= $beginning AND _date <= $ending ";
+# either or both of these can be used to link cust_bill_pkg to cust_main_county
+my $pkg_tax = "SELECT SUM(amount) as tax_amount, invnum, taxnum, ".
+  "cust_bill_pkg_tax_location.pkgnum ".
+  "FROM cust_bill_pkg_tax_location JOIN cust_bill_pkg USING (billpkgnum) ".
+  "GROUP BY billpkgnum, invnum, taxnum, cust_bill_pkg_tax_location.pkgnum";
 
-# this query will be run once per cust_main_county,
-# or maybe once per country/state/city tuple,
-# or maybe once per country/state...it's hard to say.
-my ($location_sql, @base_param) = FS::cust_location->in_county_sql(param => 1);
-$where .= " AND $location_sql ";
+my $pkg_tax_exempt = "SELECT SUM(amount) AS exempt_charged, billpkgnum, taxnum ".
+  "FROM cust_tax_exempt_pkg EXEMPT_WHERE GROUP BY billpkgnum, taxnum";
+
+my $where = "WHERE _date >= $beginning AND _date <= $ending ";
+my $group = "GROUP BY cust_main_county.taxnum";
 
 my $agentname = '';
 if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
@@ -277,270 +284,188 @@ if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
   $where .= ' AND cust_main.agentnum = '. $agent->agentnum;
 }
 
-sub gotcust {
-  my $table = shift;
-  my $prefix = @_ ? shift : '';
-  "
-        ( $table.district = cust_main_county.district
-          OR cust_main_county.district = ''
-          OR cust_main_county.district IS NULL )
-    AND ( $table.${prefix}city  = cust_main_county.city
-          OR cust_main_county.city = ''
-          OR cust_main_county.city IS NULL )
-    AND ( $table.${prefix}county  = cust_main_county.county
-          OR cust_main_county.county = ''
-          OR cust_main_county.county IS NULL )
-    AND ( $table.${prefix}state   = cust_main_county.state
-          OR cust_main_county.state = ''
-          OR cust_main_county.state IS NULL )
-    AND ( $table.${prefix}country = cust_main_county.country )
-  ";
-}
-
-#non-parameterized form
-my $location_in_county = FS::cust_location->in_county_sql;
-my $gotcust = "WHERE EXISTS(
-  SELECT 1 FROM cust_location WHERE $location_in_county AND disabled IS NULL
+my $nottax = 'cust_bill_pkg.pkgnum != 0';
+
+# one query for each column of the report
+# plus separate queries for the totals row
+my (%sql, %all_sql);
+
+# general form
+my $exempt = "SELECT cust_main_county.taxnum, SUM(exempt_charged)
+  FROM cust_main_county
+  JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
+  USING (taxnum)
+  JOIN cust_bill_pkg USING (billpkgnum)
+  $join_cust $where AND $nottax $group";
+
+my $all_exempt = "SELECT SUM(exempt_charged)
+  FROM cust_main_county
+  JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
+  USING (taxnum)
+  JOIN cust_bill_pkg USING (billpkgnum)
+  $join_cust $where AND $nottax";
+
+# sales to tax-exempt customers
+$sql{exempt_cust} = $exempt;
+$sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/;
+$all_sql{exempt_cust} = $all_exempt;
+$all_sql{exempt_cust} =~ s/EXEMPT_WHERE/WHERE exempt_cust = 'Y' OR exempt_cust_taxname = 'Y'/;
+
+# sales of tax-exempt packages
+$sql{exempt_pkg} = $exempt;
+$sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/;
+$all_sql{exempt_pkg} = $all_exempt;
+$all_sql{exempt_pkg} =~ s/EXEMPT_WHERE/WHERE exempt_setup = 'Y' OR exempt_recur = 'Y'/;
+
+# monthly per-customer exemptions
+$sql{exempt_monthly} = $exempt;
+$sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
+$all_sql{exempt_monthly} = $all_exempt;
+$all_sql{exempt_monthly} =~ s/EXEMPT_WHERE/WHERE exempt_monthly = 'Y'/;
+
+# taxable sales
+$sql{taxable} = "SELECT cust_main_county.taxnum, 
+  SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(exempt_charged, 0))
+  FROM cust_main_county
+  JOIN ($pkg_tax) AS pkg_tax USING (taxnum)
+  JOIN cust_bill_pkg USING (invnum, pkgnum)
+  LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt
+    ON (pkg_tax_exempt.billpkgnum = cust_bill_pkg.billpkgnum 
+        AND pkg_tax_exempt.taxnum = cust_main_county.taxnum)
+  $join_cust $where AND $nottax $group";
+
+# Here we're going to sum all line items that are taxable _at all_,
+# under any tax.  exempt_charged is the sum of all exemptions for a 
+# particular billpkgnum + taxnum; we take the taxnum that has the 
+# smallest sum of exemptions and subtract that from the charged amount.
+$all_sql{taxable} = "SELECT
+  SUM(cust_bill_pkg.setup + cust_bill_pkg.recur - COALESCE(min_exempt, 0))
+  FROM cust_bill_pkg
+  JOIN (
+    SELECT invnum, pkgnum, MIN(exempt_charged) AS min_exempt
+    FROM ($pkg_tax) AS pkg_tax
+    JOIN cust_bill_pkg USING (invnum, pkgnum)
+    LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum, taxnum)
+    GROUP BY invnum, pkgnum
+  ) AS pkg_is_taxable 
+  USING (invnum, pkgnum)
+  $join_cust $where AND $nottax";
+  # we don't join pkg_tax_exempt.taxnum here, because
+
+$sql{taxable} =~ s/EXEMPT_WHERE//; # unrestricted
+$all_sql{taxable} =~ s/EXEMPT_WHERE//;
+
+# there isn't one for 'sales', because we calculate sales by adding up 
+# the taxable and exempt columns.
+
+# sum of billed tax:
+# join cust_bill_pkg to cust_main_county via cust_bill_pkg_tax_location
+my $taxfrom = " FROM cust_bill_pkg 
+                $join_cust 
+                LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
+                LEFT JOIN cust_main_county USING ( taxnum )";
+
+my $istax = "cust_bill_pkg.pkgnum = 0";
+my $named_tax = "(
+  taxname = itemdesc
+  OR ( taxname IS NULL 
+    AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )
+  )
 )";
 
-my $out = 'Out of taxable region(s)';
-# these are actually tax labels, not regions
-my %regions = ();
-
-# Phase 1: Taxable and exempt sales
-# Collect for each cust_main_county, and assign to a bin based on label.
-# Note that "label" includes city if show_cities is on, and taxclass if
-# show_taxclasses is on.
-foreach my $r ( qsearch({ 'table'     => 'cust_main_county',
-                          'extra_sql' => $gotcust,
-                          'debug' => $DEBUG,
-                       })
-              )
-{
-  warn $r->county. ' '. $r->state. ' '. $r->country. "\n" if $DEBUG > 1;
-
-  # set up a %regions entry for this region's tax label
-  my $label = getlabel($r);
-  $regions{$label}->{'label'} = $label;
-
-  $regions{$label}->{$_} = $r->$_() for (qw( county state country )); #taxname?
-
-  my @url_param = qw( county state country taxname );
-  push @url_param, 'city' if $cgi->param('show_cities') && $r->city();
-
-  $regions{$label}->{'url_param'} =
-    join(';', map "$_=".uri_escape($r->$_()), @url_param );
-
-  my @param = @base_param;
-  my $mywhere = $where;
-
-  if ( $r->taxclass ) {
-
-    $mywhere .= " AND taxclass = ? ";
-    push @param, 'taxclass';
-    $regions{$label}->{'url_param'} .= ';taxclass='. uri_escape($r->taxclass);
-    #no, always#  if $cgi->param('show_taxclasses');
-
-    $regions{$label}->{'taxclass'} = $r->taxclass;
-
-  } else {
-
-    # SQL for "taxclass doesn't match any other tax in the region"
-    my $same_sql = $r->sql_taxclass_sameregion;
-    $mywhere .= " AND $same_sql" if $same_sql;
-
-    $regions{$label}->{'url_param'} .= ';taxclassNULL=1'
-      if $cgi->param('show_taxclasses')
-      || $same_sql;
-
-  }
-
-  # FROM cust_bill_pkg JOIN (whatever is needed to determine tax location)
-  # WHERE (matches tax location and agentnum and taxclass)
-  # takes parameters in @base_param, plus taxclass if there is one
-  my $fromwhere = "$from_join_cust_pkg $mywhere"; # AND payby != 'COMP' ";
-
-  my $nottax = 'pkgnum != 0';
-
-  ## calculate total of sales (non-tax line items) for this region
-
-  my $t_sql =
-   "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) $fromwhere AND $nottax";
-  my $t = scalar_sql($r, \@param, $t_sql);
-  $regions{$label}->{'total'} += $t;
-
-  #$regions{$label}->{subtotals}->{$r->taxnum} = $t; #useful debug
-
-  ## calculate customer-exemption for this region
-
-  #false laziness -ish w/report_tax.cgi
-  my $cust_exempt;
-  if ( $r->taxname ) {
-    my $q_taxname = dbh->quote($r->taxname);
-    $cust_exempt =
-      "( tax = 'Y'
-         OR EXISTS ( SELECT 1 FROM cust_main_exemption
-                       WHERE cust_main_exemption.custnum = cust_main.custnum
-                         AND cust_main_exemption.taxname = $q_taxname
-                   )
-       )
-      ";
-  } else {
-    $cust_exempt = " tax = 'Y' ";
-  }
-
-  my $x_cust = scalar_sql($r, \@param,
-    "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur)
-     $fromwhere AND $nottax AND $cust_exempt "
-  );
-
-  $regions{$label}->{'exempt_cust'} += $x_cust;
-  
-  ## calculate package-exemption for this region
-
-  my $x_pkg = scalar_sql($r, \@param,
-    "SELECT SUM(
-                 ( CASE WHEN part_pkg.setuptax = 'Y'
-                        THEN cust_bill_pkg.setup
-                        ELSE 0
-                   END
-                 )
-                 +
-                 ( CASE WHEN part_pkg.recurtax = 'Y'
-                        THEN cust_bill_pkg.recur
-                        ELSE 0
-                   END
-                 )
-               )
-       $fromwhere
-       AND $nottax
-       AND (
-                ( part_pkg.setuptax = 'Y' AND cust_bill_pkg.setup > 0 )
-             OR ( part_pkg.recurtax = 'Y' AND cust_bill_pkg.recur > 0 )
-           )
-       AND ( tax != 'Y' OR tax IS NULL )
-    "
-  );
-  $regions{$label}->{'exempt_pkg'} += $x_pkg;
-
-  ## calculate monthly exemption (texas tax) for this region
-
-  # count up all the cust_tax_exempt_pkg records associated with
-  # the actual line items.
-
-  my $x_monthly = scalar_sql($r, \@param,
-    "SELECT SUM(amount)
-       FROM cust_tax_exempt_pkg
-       JOIN cust_bill_pkg USING ( billpkgnum )
-       $join_cust_pkg
-     $mywhere"
-  );
-  $regions{$label}->{'exempt_monthly'} += $x_monthly;
-
-  my $taxable = $t - $x_cust - $x_pkg - $x_monthly;
-  $regions{$label}->{'taxable'} += $taxable;
-
-  $regions{$label}->{'owed'} += $taxable * ($r->tax/100);
-
-  if ( defined($regions{$label}->{'rate'})
-       && $regions{$label}->{'rate'} != $r->tax.'%' ) {
-    $regions{$label}->{'rate'} = 'variable';
-  } else {
-    $regions{$label}->{'rate'} = $r->tax.'%';
-  }
+$sql{tax} = "SELECT cust_main_county.taxnum, 
+             SUM(cust_bill_pkg_tax_location.amount)
+             $taxfrom
+             $where AND $istax AND $named_tax
+             $group";
+
+$all_sql{tax} = "SELECT SUM(cust_bill_pkg.setup)
+             FROM cust_bill_pkg
+             $join_cust
+             $where AND $istax";
+
+# sum of credits applied against billed tax
+my $creditfrom = $taxfrom .
+   ' JOIN cust_credit_bill_pkg USING (billpkgtaxlocationnum)';
+my $creditfromwhere = $where . 
+   ' AND billpkgtaxratelocationnum IS NULL';
+
+$sql{credit} = "SELECT cust_main_county.taxnum,
+                SUM(cust_credit_bill_pkg.amount)
+                $creditfrom
+                $creditfromwhere AND $istax AND $named_tax
+                $group";
+
+$all_sql{credit} = "SELECT SUM(cust_credit_bill_pkg.amount)
+                FROM cust_credit_bill_pkg
+                JOIN cust_bill_pkg USING (billpkgnum)
+                $join_cust
+                $where AND $istax";
+
+my %data;
+my %total = (owed => 0);
+foreach my $k (keys(%sql)) {
+  my $stmt = $sql{$k};
+  warn "\n".uc($k).":\n".$stmt."\n" if $DEBUG;
+  my $sth = dbh->prepare($stmt);
+  # two columns => key/value
+  $sth->execute 
+    or die "failed to execute $k query: ".$sth->errstr;
+  $data{$k} = +{ map { @$_ } @{ $sth->fetchall_arrayref([]) } };
+
+  warn "\n".$all_sql{$k}."\n" if $DEBUG;
+  $total{$k} = FS::Record->scalar_sql( $all_sql{$k} );
+  warn Dumper($data{$k}) if $DEBUG > 1;
 }
-warn Dumper(\%regions) if $DEBUG > 1;
-# $regions{$label} now contains 'total', 'exempt_cust', 'exempt_pkg', 
-# 'exempt_monthly', summed over each set of regions with the same label.
-
-my $distinct = "country, state, county, city, district,
-                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";
-
-
-# Phase 2: invoiced/credited tax items
-# Collect this data for each country/state/city/district/taxname(/taxclass).
-my %qsearch = (
-  'select'    => "DISTINCT $distinct, $taxclass_distinct",
-  'table'     => 'cust_main_county',
-  'hashref'   => {},
-  'extra_sql' => $gotcust,
-  'debug' => $DEBUG,
+# so $data{tax}, for example, is now a hash with one entry
+# for each taxnum, containing the tax billed on that taxnum.
+
+# oddball cases:
+# "out of taxable region" sales
+my %out;
+my $out_sales_sql = 
+  "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)
+  FROM (cust_bill_pkg $join_cust)
+  LEFT JOIN ($pkg_tax) AS pkg_tax USING (invnum, pkgnum)
+  LEFT JOIN ($pkg_tax_exempt) AS pkg_tax_exempt USING (billpkgnum)
+  $where AND $nottax
+  AND pkg_tax.taxnum IS NULL AND pkg_tax_exempt.taxnum IS NULL"
+;
+
+$out_sales_sql =~ s/EXEMPT_WHERE//;
+
+$out{sales} = FS::Record->scalar_sql($out_sales_sql);
+
+# unlinked tax collected (for diagnostics)
+my $out_tax_sql =
+  "SELECT SUM(cust_bill_pkg.setup)
+  FROM (cust_bill_pkg $join_cust)
+  LEFT JOIN cust_bill_pkg_tax_location USING (billpkgnum)
+  $where AND $istax AND cust_bill_pkg_tax_location.billpkgnum IS NULL"
+;
+$out{tax} = FS::Record->scalar_sql($out_tax_sql);
+# unlinked tax credited (for diagnostics)
+my $out_credit_sql =
+  "SELECT SUM(cust_credit_bill_pkg.amount)
+  FROM cust_credit_bill_pkg
+  JOIN cust_bill_pkg USING (billpkgnum)
+  $join_cust
+  $where AND $istax AND cust_credit_bill_pkg.billpkgtaxlocationnum IS NULL"
+;
+$out{credit} = FS::Record->scalar_sql($out_credit_sql);
+
+# all sales
+$total{sales} = FS::Record->scalar_sql(
+  "SELECT SUM(cust_bill_pkg.setup + cust_bill_pkg.recur)
+  FROM cust_bill_pkg $join_cust $where AND $nottax"
 );
 
-# Join to cust_main the same as before (we need agentnum)
-# but not to cust_pkg (because tax line items don't have a package)
-# and then to cust_location via cust_bill_pkg_tax_location
-my $taxfromwhere = "FROM cust_bill_pkg $join_cust 
-                    LEFT JOIN cust_bill_pkg_tax_location USING ( billpkgnum )
-                    LEFT JOIN cust_location USING ( locationnum )
-                    ";
-my $taxwhere = $where;
-
-my $creditfromwhere = $taxfromwhere. 
-   " JOIN cust_credit_bill_pkg USING (billpkgnum, billpkgtaxlocationnum)";
-
-$taxfromwhere .= " $taxwhere "; #AND payby != 'COMP' ";
-$creditfromwhere .= " $taxwhere AND billpkgtaxratelocationnum IS NULL"; #AND payby != 'COMP' ";
-
-#should i be a cust_main_county method or something
-# yes. yes, you should.
-
-# $taxfromwhere: Most of a query to find cust_bill_pkg records linked to a 
-# customer matching a given state/county/city/district (and within the date 
-# range for the report).
-# @base_param: A list of the fields from cust_main_county to use as parameters.
-
-# $_taxamount_sub: Takes a cust_main_county and returns the sum of taxes billed
-# within the report period for all customers located in that county.  If 
-# the cust_main_county has a taxname, limits to taxes with that name; otherwise
-# includes all line items with pkgnum = 0 and description either 'Tax' or empty.
-
-my $_taxamount_sub = sub {
-  my $r = shift;
-
-  #match itemdesc if necessary!
-  my $named_tax =
-    $r->taxname
-      ? 'AND itemdesc = '. dbh->quote($r->taxname)
-      : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
-
-  my $sql = "SELECT SUM(cust_bill_pkg.setup+cust_bill_pkg.recur) ".
-            " $taxfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax";
-
-  scalar_sql($r, [ @base_param ], $sql );
-};
-
-# $_creditamount_sub: As above, but returns the sum of credits applied 
-
-my $_creditamount_sub = sub {
-  my $r = shift;
-
-  #match itemdesc if necessary!
-  my $named_tax =
-    $r->taxname
-      ? 'AND itemdesc = '. dbh->quote($r->taxname)
-      : "AND ( itemdesc IS NULL OR itemdesc = '' OR itemdesc = 'Tax' )";
-
-  my $sql = "SELECT SUM(cust_credit_bill_pkg.amount) ".
-            " $creditfromwhere AND cust_bill_pkg.pkgnum = 0 $named_tax";
-
-  scalar_sql($r, [ @base_param ], $sql );
-};
-
 #tax-report_groups filtering
 my($group_op, $group_value) = ( '', '' );
 if ( $cgi->param('report_group') =~ /^(=|!=) (.*)$/ ) {
   ( $group_op, $group_value ) = ( $1, $2 );
 }
-my $group_test = sub {
+my $group_test = sub { # to be applied to a tax label
   my $label = shift;
   return 1 unless $group_op; #in case we get called inadvertantly
   if ( $label eq $out ) { #don't display "out of taxable region" in this case
@@ -554,90 +479,83 @@ my $group_test = sub {
   }
 };
 
+# if show_taxclasses is on, %base_regions will contain the same data
+# as %regions, but with taxclasses merged together (and ignoring report_group
+# filtering).
+my (%regions, %base_regions);
 my $tot_tax = 0;
 my $tot_credit = 0;
-#foreach my $label ( keys %regions ) {
-foreach my $r ( qsearch(\%qsearch) ) {
 
-  #warn join('-', map { $r->$_() } qw( country state county taxname ) )."\n";
+my @loc_params = qw(country state county);
+push @loc_params, qw(city district) if $cgi->param('show_cities');
 
-  my $label = getlabel($r);
-  if ( $group_op ) {
-    next unless &{$group_test}($label);
+foreach my $r ( qsearch({ 'table'     => 'cust_main_county', })) {
+  my $taxnum = $r->taxnum;
+  # set up a %regions entry for this region's tax label
+  my $label = $r->label(%label_opt);
+  next if $label eq $out;
+  $regions{$label} ||= { label => $label };
+
+  $regions{$label}->{$_} = $r->get($_) foreach @loc_params;
+  $regions{$label}->{taxnums} ||= [];
+  push @{ $regions{$label}->{taxnums} }, $r->taxnum;
+
+  my %x; # keys are data items (like 'tax', 'exempt_cust', etc.)
+  foreach my $k (keys %data) {
+    next unless exists($data{$k}->{$taxnum});
+    $x{$k} = $data{$k}->{$taxnum};
+    $regions{$label}->{$k} += $x{$k};
+    if ( $k eq 'taxable' or $k =~ /^exempt/ ) {
+      $regions{$label}->{'sales'} += $x{$k};
+    }
   }
 
-  #my $fromwhere = $join_pkg. $where. " AND payby != 'COMP' ";
-  #my @param = @base_param; 
+  my $owed = $data{'taxable'}->{$taxnum} * ($r->tax/100);
+  $regions{$label}->{'owed'} += $owed;
+  $total{'owed'} += $owed;
 
-  my $x = &{$_taxamount_sub}($r);
-
-  $regions{$label}->{'tax'} += $x;
-  $tot_tax += $x unless $cgi->param('show_taxclasses');
-
-  ## calculate credit for this region
-
-  $x = &{$_creditamount_sub}($r);
-
-  $regions{$label}->{'credit'} += $x;
-  $tot_credit += $x unless $cgi->param('show_taxclasses');
-
-}
-
-# Phase 3: Non-taxclassed totals for invoiced/credited tax
-# (If show_taxclasses is not in use, this was phase 2, but it 
-# displays somewhere different.)
-# Don't filter by report_groups.
-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 "$_=". uri_escape($r->$_()),
-                     qw( county state country taxname )
-          );
-
-    $base_regions{$base_label}->{'tax'} += $x;
-    $tot_tax += $x;
-
-    ## calculate credit for this region
-
-    $x = &{$_creditamount_sub}($r);
-
-    $base_regions{$base_label}->{'credit'} += $x;
-    $tot_credit += $x;
+  if ( defined($regions{$label}->{'rate'})
+       && $regions{$label}->{'rate'} != $r->tax.'%' ) {
+    $regions{$label}->{'rate'} = 'variable';
+  } else {
+    $regions{$label}->{'rate'} = $r->tax.'%';
+  }
 
+  if ( $cgi->param('show_taxclasses') ) {
+    my $base_label = $r->label(%label_opt, 'no_taxclass' => 1);
+    $base_regions{$base_label} ||=
+    {
+      label   => $base_label,
+      tax     => 0,
+      credit  => 0,
+    };
+    $base_regions{$base_label}->{tax}    += $x{tax};
+    $base_regions{$base_label}->{credit} += $x{credit};
   }
 
 }
 
-my @regions = keys %regions;
+my @regions = map { $_->{label} }
+  sort {
+    ($b eq $out) <=> ($a eq $out)
+    or $a->{country} cmp $b->{country}
+    or $a->{state}   cmp $b->{state}
+    or $a->{county}  cmp $b->{county}
+    or $a->{city}    cmp $b->{city}
+  } 
+  grep { $_->{sales} > 0 or $_->{tax} > 0 or $_->{credit} > 0 }
+  values %regions;
 
 #tax-report_groups filtering
 @regions = grep &{$group_test}($_), @regions
   if $group_op;
 
 #calculate totals
-my( $total, $tot_taxable, $tot_owed ) = ( 0, 0, 0 );
-my( $exempt_cust, $exempt_pkg, $exempt_monthly, $tot_credit ) = ( 0, 0, 0, 0 );
 my %taxclasses = ();
 my %county = ();
 my %state = ();
 my %country = ();
-foreach (@regions) {
-  $total          += $regions{$_}->{'total'};
-  $tot_taxable    += $regions{$_}->{'taxable'};
-  $tot_owed       += $regions{$_}->{'owed'};
-  $exempt_cust    += $regions{$_}->{'exempt_cust'};
-  $exempt_pkg     += $regions{$_}->{'exempt_pkg'};
-  $exempt_monthly += $regions{$_}->{'exempt_monthly'};
-  $tot_credit     += $regions{$_}->{'credit'};
+foreach my $label (@regions) {
   $taxclasses{$regions{$_}->{'taxclass'}} = 1
     if $regions{$_}->{'taxclass'};
   $county{$regions{$_}->{'county'}} = 1;
@@ -672,29 +590,27 @@ if ( $group_op ) {
 #ordering
 @regions =
   map $regions{$_},
-  sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) }
+  sort { $a cmp $b }
   @regions;
 
 my @base_regions =
   map $base_regions{$_},
-  sort { ( ($a eq $out) cmp ($b eq $out) ) || ($b cmp $a) }
+  sort { $a cmp $b }
   keys %base_regions;
 
-#add total line
-push @regions, {
-  'label'          => 'Total',
-  'url_param'      => $total_url_param,
-  'url_param_inv'  => $total_url_param_invoiced,
-  'total'          => $total,
-  'exempt_cust'    => $exempt_cust,
-  'exempt_pkg'     => $exempt_pkg,
-  'exempt_monthly' => $exempt_monthly,
-  'taxable'        => $tot_taxable,
-  'rate'           => '',
-  'owed'           => $tot_owed,
-  'tax'            => $tot_tax,
-  'credit'         => $tot_credit,
-};
+#add "Out of taxable" and total lines
+%out = ( %out,
+  'label' => $out,
+  'rate' => ''
+);
+%total = ( %total, 
+  'label'         => 'Total',
+  'url_param'     => $total_url_param,
+  'url_param_inv' => $total_url_param_invoiced,
+  'rate'          => '',
+);
+push @regions, \%out, \%total;
+push @base_regions, \%out, \%total;
 
 #-- 
 
@@ -702,69 +618,15 @@ my $money_char = $conf->config('money_char') || '$';
 my $money_sprintf = sub {
   $money_char. sprintf('%.2f', shift );
 };
-
-sub getlabel {
-  my $r = shift;
-  my %opt = @_;
-
-  my $label;
-  if (
-    $r->tax == 0 
-    && ! scalar( qsearch('cust_main_county', { 'district'=> $r->district,
-                                               'city'    => $r->city,
-                                               'county'  => $r->county,
-                                               'state'   => $r->state,
-                                               'country' => $r->country,
-                                               'tax' => { op=>'>', value=>0 },
-                                             }
-                        )
-               )
-
-  ) {
-    #kludge to avoid "will not stay shared" warning
-    my $out = 'Out of taxable region(s)';
-    $label = $out;
-  } else {
-    $label = $r->country;
-    $label = $r->state.", $label" if $r->state;
-    $label = $r->county." county, $label" if $r->county;
-    $label = $r->city. ", $label" if $r->city && $cgi->param('show_cities');
-    $label = "$label (". $r->taxclass. ")"
-      if $r->taxclass
-      && $cgi->param('show_taxclasses')
-      && ! $opt{'no_taxclass'};
-    $label = $r->taxname. " ($label)" if $r->taxname;
-  }
-  return $label;
-}
-
-#my %count_taxname = (); #cache
-#sub count_taxname {
-#  my $taxname = shift;
-#  return $count_taxname{$taxname} if exists $count_taxname{$taxname};
-#  my $sql = 'SELECT COUNT(*) FROM cust_main_county WHERE taxname = ?';
-#  my $sth = dbh->prepare($sql) or die dbh->errstr;
-#  $sth->execute( $taxname )
-#    or die "Unexpected error executing statement $sql: ". $sth->errstr;
-#  $count_taxname{$taxname} = $sth->fetchrow_arrayref->[0];
-#}
-
-#false laziness w/FS::Report::Table::Monthly (sub should probably be moved up
-#to FS::Report or FS::Record or who the fuck knows where)
-sub scalar_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;
-  $sth->fetchrow_arrayref->[0] || 0;
-}
+my $money_sprintf_nonzero = sub {
+  $_[0] == 0 ? '' : &$money_sprintf($_[0])
+};
 
 my $dateagentlink = "begin=$beginning;end=$ending";
 $dateagentlink .= ';agentnum='. $cgi->param('agentnum')
   if length($agentname);
 my $baselink   = $p. "search/cust_bill_pkg.cgi?$dateagentlink";
 my $exemptlink = $p. "search/cust_tax_exempt_pkg.cgi?$dateagentlink";
-my $creditlink = $p. "search/cust_credit_bill_pkg.html?$dateagentlink";
+my $creditlink = $p. "search/cust_bill_pkg.cgi?$dateagentlink;credit=1";
 
 </%init>
diff --git a/httemplate/search/sqlradius_usage.html b/httemplate/search/sqlradius_usage.html
new file mode 100644
index 000000000..29ef4c0e8
--- /dev/null
+++ b/httemplate/search/sqlradius_usage.html
@@ -0,0 +1,201 @@
+% if ( @include_agents ) {
+%   # jumbo report
+<& /elements/header.html, $title &>
+%   foreach my $agent ( @include_agents ) {
+% $cgi->param('agentnum', $agent->agentnum); #for download links
+<DIV WIDTH="100%" STYLE="page-break-after: always">
+<FONT SIZE=6><% $agent->agent %></FONT><BR><BR>
+  <& sqlradius_usage.html, 
+      export            => $export,
+      agentnum          => $agent->agentnum,
+      nohtmlheader      => 1,
+      usage_by_username => \%usage_by_username,
+      download_label    => 'Download this section',
+      &>
+</DIV>
+<BR><BR>
+%  }
+<& /elements/footer.html &>
+% } else {
+<& elements/search.html,
+  'title'       => $title,
+  'name'        => 'services',
+  'query'       => $sql_query,
+  'count_query' => $sql_query->{'count_query'},
+  'header'      => [ #FS::UI::Web::cust_header(),
+                     '#',
+                     'Customer',
+                     'Package',
+                     @svc_header,
+                     'Upload (GB)',
+                     'Download (GB)',
+                     'Total (GB)',
+                   ],
+  'footer'      => \@footer,
+  'fields'      => [ #\&FS::UI::Web::cust_fields,
+                     'display_custnum',
+                     'name',
+                     'pkg',
+                     @svc_fields,
+                     @svc_usage,
+                   ],
+  'links'       => [ #( map { $_ ne 'Cust. Status' ? $link_cust : '' }
+                     #  FS::UI::Web::cust_header() ),
+                     $link_cust,
+                     $link_cust,
+                     '', #package
+                     ( map { $link_svc } @svc_header ),
+                     '',
+                     '',
+                     '',
+                   ],
+  'align'       => #FS::UI::Web::cust_aligns() .
+                   'rlc' . ('l' x scalar(@svc_header)) . 'rrr' ,
+  'nohtmlheader'    => ($opt{'nohtmlheader'} || 0),
+  'download_label'  => $opt{'download_label'},
+&>
+% }
+<%init>
+
+my %opt = @_;
+
+die "access denied" unless
+  $FS::CurrentUser::CurrentUser->access_right('List services');
+
+my $title = 'Data Usage Report - '; 
+my $agentnum;
+my @include_agents;
+
+if ( $opt{'agentnum'} ) {
+  $agentnum = $opt{'agentnum'};
+} elsif ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
+  $agentnum = $1;
+}
+
+if ( $agentnum ) {
+  my $agent = FS::agent->by_key($agentnum);
+  $title = $agent->agent." $title";
+} else {
+  @include_agents = qsearch('agent', {});
+}
+
+# usage query params
+my( $beginning, $ending ) = FS::UI::Web::parse_beginning_ending($cgi);
+
+if ( $beginning ) {
+  $title .= time2str('%h %o %Y ', $beginning);
+}
+$title .= 'through ';
+if ( $ending == 4294967295 ) {
+  $title .= 'now';
+} else {
+  $title .= time2str('%h %o %Y', $ending);
+}
+
+my $export;
+my %usage_by_username;
+if ( exists($opt{usage_by_username}) ) {
+  # There's no agent separation in the radacct data.  So in the jumbo report
+  # do this procedure once, and pass the hash into all the per-agent sections.
+  %usage_by_username = %{ $opt{usage_by_username} };
+  $export  = $opt{export};
+} else {
+
+  $cgi->param('exportnum') =~ /^(\d+)$/
+    or die "illegal export: '".$cgi->param('exportnum')."'";
+  $export = FS::part_export->by_key($1)
+    or die "exportnum $1 not found";
+  $export->exporttype =~ /sqlradius/
+    or die "exportnum ".$export->exportnum." is type ".$export->exporttype.
+           ", not sqlradius";
+
+  my $usage = $export->usage_sessions( {
+      stoptime_start  => $beginning,
+      stoptime_end    => $ending,
+      summarize       => 1
+  } );
+  # arrayref of hashrefs of
+  # (username, acctsessiontime, acctinputoctets, acctoutputoctets)
+  # (XXX needs to include 'realm' for sqlradius_withdomain)
+  # rearrange to be indexed by username.
+
+  foreach (@$usage) {
+    my $username = $_->{'username'};
+    my @row = (
+      $_->{'acctinputoctets'},
+      $_->{'acctoutputoctets'},
+      $_->{'acctinputoctets'} + $_->{'acctoutputoctets'}
+    );
+    $usage_by_username{$username} = \@row;
+  }
+}
+
+#warn Dumper(\%usage_by_username);
+my @total_usage = (0, 0, 0, 0); # session time, input, output, input + output
+my @svc_usage = map {
+  my $i = $_;
+  sub {
+    my $username = $export->export_username(shift);
+    return '' if !exists($usage_by_username{$username});
+    my $value = $usage_by_username{ $username }->[$i];
+    $total_usage[$i] += $value;
+    # for now, always show in GB, rounded to 3 digits
+    bytes_to_gb($value);
+  }
+} (0,1,2);
+
+# set up svcdb-specific stuff
+my $export_username = sub {
+  $export->export_username(shift); # countrycode + phone, formatted MAC, etc.
+};
+
+my %svc_header = (
+  svc_acct      => [ 'Username' ],
+  svc_broadband => [ 'MAC address', 'IP address' ],
+#  svc_phone     => [ 'Phone' ], #not yet supported, no search method
+                                 # (not sure input/output octets is relevant)
+);
+my %svc_fields = (
+  svc_acct      => [ $export_username ],
+  svc_broadband => [ $export_username, 'ip_addr' ],
+#  svc_phone     => [ $export_username ],
+);
+
+# what kind of service we're operating on
+my $svcdb = FS::part_export::export_info()->{$export->exporttype}->{'svc'};
+my $class = "FS::$svcdb";
+my @svc_header = @{ $svc_header{$svcdb} };
+my @svc_fields = @{ $svc_fields{$svcdb} };
+
+# svc_x search params
+my %search_hash = ( 'agentnum' => $agentnum,
+                    'exportnum' => $export->exportnum );
+
+my $sql_query = $class->search(\%search_hash);
+$sql_query->{'select'}    .= ', part_pkg.pkg';
+$sql_query->{'addl_from'} .= ' LEFT JOIN part_pkg USING (pkgpart)';
+
+my $link_svc = [ $p.'view/cust_svc.cgi?', 'svcnum' ];
+
+my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
+
+# columns between the customer name and the usage fields
+my $skip_cols = 1 + scalar(@svc_header);
+
+my @footer = (
+  '',
+  FS::Record->scalar_sql($sql_query->{count_query}) . ' services',
+  ('') x $skip_cols,
+  map {
+    my $i = $_;
+    sub { # defer this until the rows have been processed
+      bytes_to_gb($total_usage[$i])
+    }
+  } (0,1,2)
+);
+
+sub bytes_to_gb {
+  $_[0] ?  sprintf('%.3f', $_[0] / (1024*1024*1024.0)) : '';
+}
+
+</%init>