hopefully put reprinting issues to rest for ejourney, yow

diff --git a/FS/FS/cust_bill.pm b/FS/FS/cust_bill.pm
index af95d35..1f6f85b 100644 (file)
--- a/FS/FS/cust_bill.pm
+++ b/FS/FS/cust_bill.pm
@@ -2729,53 +2729,7 @@ sub re_X {
   my $distinct = '';
   my $orderby = 'ORDER BY cust_bill._date';
 
-  my @where;
-
-  if ( $param{'begin'} =~ /^(\d+)$/ ) {
-    push @where, "cust_bill._date >= $1";
-  }
-  if ( $param{'end'} =~ /^(\d+)$/ ) {
-    push @where, "cust_bill._date < $1";
-  }
-  if ( $param{'invnum_min'} =~ /^(\d+)$/ ) {
-    push @where, "cust_bill.invnum >= $1";
-  }
-  if ( $param{'invnum_max'} =~ /^(\d+)$/ ) {
-    push @where, "cust_bill.invnum <= $1";
-  }
-  if ( $param{'agentnum'} =~ /^(\d+)$/ ) {
-    push @where, "cust_main.agentnum = $1";
-  }
-
-  push @where, '0 != '. FS::cust_bill->owed_sql
-    if $param{'open'};
-
-  push @where, '0 != '. FS::cust_bill->net_sql
-    if $param{'net'};
-
-  push @where, "cust_bill._date < ". (time-86400*$param{'days'})
-    if $param{'days'};
-
-  if ( $param{'newest_percust'} ) {
-
-    #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
-    #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
-
-    my @newest_where = map { s/\bcust_bill\./newest_cust_bill./g; }
-                           grep ! /^cust_main./, @where;
-    my $newest_where = scalar(@newest_where)
-                         ? ' AND '. join(' AND ', @newest_where)
-                        : '';
-
-    push @where, "cust_bill._date = (
-      SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
-        WHERE newest_cust_bill.custnum = cust_bill.custnum
-          $newest_where
-    )";
-
-  }
-
-  my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+  my $extra_sql = ' WHERE '. FS::cust_bill->search_sql(\%param);
 
   my $addl_from = 'left join cust_main using ( custnum )';
      
@@ -2859,6 +2813,94 @@ sub credited_sql {
        WHERE cust_bill.invnum = cust_credit_bill.invnum   )";
 }
 
+=item search_sql HASHREF
+
+Class method which returns an SQL WHERE fragment to search for parameters
+specified in HASHREF.  Valid parameters are
+
+=over 4
+
+=item begin - epoch date (UNIX timestamp) setting a lower bound for _date values
+
+=item end - epoch date (UNIX timestamp) setting an upper bound for _date values
+
+=item invnum_min
+
+=item invnum_max
+
+=item agentnum
+
+=item owed
+
+=item net
+
+=item days
+
+=item newest_percust
+
+=back
+
+Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
+
+=cut
+
+sub search_sql {
+  my($class, $param) = @_;
+  my @search = ();
+
+  if ( $param->{'begin'} =~ /^(\d+)$/ ) {
+    push @search, "cust_bill._date >= $1";
+  }
+  if ( $param->{'end'} =~ /^(\d+)$/ ) {
+    push @search, "cust_bill._date < $1";
+  }
+  if ( $param->{'invnum_min'} =~ /^(\d+)$/ ) {
+    push @search, "cust_bill.invnum >= $1";
+  }
+  if ( $param->{'invnum_max'} =~ /^(\d+)$/ ) {
+    push @search, "cust_bill.invnum <= $1";
+  }
+  if ( $param->{'agentnum'} =~ /^(\d+)$/ ) {
+    push @search, "cust_main.agentnum = $1";
+  }
+
+  push @search, '0 != '. FS::cust_bill->owed_sql
+    if $param->{'open'};
+
+  push @search, '0 != '. FS::cust_bill->net_sql
+    if $param->{'net'};
+
+  push @search, "cust_bill._date < ". (time-86400*$param->{'days'})
+    if $param->{'days'};
+
+  if ( $param->{'newest_percust'} ) {
+
+    #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
+    #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
+
+    my @newest_where = map { my $x = $_;
+                             $x = s/\bcust_bill\./newest_cust_bill./g;
+                             $x;
+                           }
+                           grep ! /^cust_main./, @search;
+    my $newest_where = scalar(@newest_where)
+                         ? ' AND '. join(' AND ', @newest_where)
+                        : '';
+
+    push @search, "cust_bill._date = (
+      SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
+        WHERE newest_cust_bill.custnum = cust_bill.custnum
+          $newest_where
+    )";
+
+  }
+
+  push @search, $FS::CurrentUser::CurrentUser->agentnums_sql;
+
+  join(' AND ', @search );
+
+}
+
 =back
 
 =head1 BUGS

diff --git a/httemplate/search/cust_bill.html b/httemplate/search/cust_bill.html
index 9589efd..b7eac57 100755 (executable)
--- a/httemplate/search/cust_bill.html
+++ b/httemplate/search/cust_bill.html
@@ -89,87 +89,49 @@ if ( $cgi->param('invnum') =~ /^\s*(FS-)?(\d+)\s*$/ ) {
   if ( $cgi->param('beginning')
        && $cgi->param('beginning') =~ /^([ 0-9\-\/]{0,10})$/ ) {
     $search{'begin'} = str2time($1);
-    push @where, 'cust_bill._date >= '. $search{'begin'};
   }
   if ( $cgi->param('ending')
         && $cgi->param('ending') =~ /^([ 0-9\-\/]{0,10})$/ ) {
     $search{'end'} = str2time($1) + 86399;
-    push @where, 'cust_bill._date < '. $search{'end'};
   }
 
   if ( $cgi->param('begin') =~ /^(\d+)$/ ) {
     $search{'begin'} = $1;
-    push @where, 'cust_bill._date >= '. $search{'begin'};
   }
   if ( $cgi->param('end') =~ /^(\d+)$/ ) {
     $search{'end'} = $1;
-    push @where, 'cust_bill._date < '. $search{'end'};
   }
 
   if ( $cgi->param('invnum_min') =~ /^\s*(\d+)\s*$/ ) {
     $search{'invnum_min'} = $1;
-    push @where, 'cust_bill.invnum >= '. $search{'invnum_min'};
   }
   if ( $cgi->param('invnum_max') =~ /^\s*(\d+)\s*$/ ) {
     $search{'invnum_max'} = $1;
-    push @where, 'cust_bill.invnum <= '. $search{'invnum_max'};
   }
 
   if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
     $search{'agentnum'} = $1;
-    push @where, 'cust_main.agentnum = '. $search{'agentnum'};
   }
 
-  if ( $cgi->param('open') ) {
-    push @where, '0 != '. FS::cust_bill->owed_sql;
-    $search{'open'} = 1;
-  }
-
-  if ( $cgi->param('net') ) {
-    push @where, '0 != '. FS::cust_bill->net_sql;
-    $search{'net'} = 1;
-  }
+  $search{'open'} = 1 if $cgi->param('open');
+  $search{'net'}  = 1 if $cgi->param('net' );
 
   my($query) = $cgi->keywords;
   if ( $query =~ /^(OPEN(\d*)_)?(invnum|date|custnum)$/ ) {
-    ($search{'open'}, $search{'days'}, my $field) = ($1, $2, $3);
+    $search{'open'} = 1 if $1;
+    ($search{'days'}, my $field) = ($2, $3);
     $field = "_date" if $field eq 'date';
     $orderby = "ORDER BY cust_bill.$field";
-    push @where, '0 != '. FS::cust_bill->owed_sql if $search{'open'};
-    push @where, "cust_bill._date < ". (time-86400*$search{'days'})
-      if $search{'days'};
   }
 
+  $search{'newest_percust'} = 1 if $cgi->param('newest_percust');
+
   if ( $cgi->param('newest_percust') ) {
     $search{'newest_percust'} = 1;
-
-    #$distinct = 'DISTINCT ON ( cust_bill.custnum )';
-    #$orderby = 'ORDER BY cust_bill.custnum ASC, cust_bill._date DESC';
-    #$count_query = "SELECT 'N/A', 'N/A', 'N/A'"; #XXXXXXX fix
-    
-    my @newest_where = map { my $x = $_;
-                             $x =~ s/\bcust_bill\./newest_cust_bill./g;
-                             $x;
-                          }
-                           grep ! /^cust_main./, @where;
-    my $newest_where = scalar(@newest_where)
-                         ? ' AND '. join(' AND ', @newest_where)
-                        : '';
-
-    push @where, "cust_bill._date = (
-      SELECT(MAX(newest_cust_bill._date)) FROM cust_bill AS newest_cust_bill
-        WHERE newest_cust_bill.custnum = cust_bill.custnum
-          $newest_where
-    )";
-
-
     $count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'";
   }
 
-  #here is the agent virtualization
-  push @where, $agentnums_sql;
-
-  my $extra_sql = scalar(@where) ? 'WHERE '. join(' AND ', @where) : '';
+  my $extra_sql = ' WHERE '. FS::cust_bill->search_sql( \%search );
 
   unless ( $count_query ) {
     $count_query = 'SELECT COUNT(*), '. join(', ',