advanced phone number search, RT#21054

3 files changed, 51 insertions, 61 deletions

diff --git a/httemplate/elements/tr-select-part_svc.html b/httemplate/elements/tr-select-part_svc.html
index 9d4508091..959ac8dd9 100644
--- a/httemplate/elements/tr-select-part_svc.html
+++ b/httemplate/elements/tr-select-part_svc.html
@@ -5,7 +5,7 @@
 % } else { 
 
   <TR>
-    <TD ALIGN="right"><% $opt{'label'} || 'Package definition' %></TD>
+    <TD ALIGN="right"><% $opt{'label'} || 'Service definition' %></TD>
     <TD>
       <% include( '/elements/select-part_svc.html',
                     'multiple' => 1,
diff --git a/httemplate/search/elements/report_svc_Common.html b/httemplate/search/elements/report_svc_Common.html
index 04feb9e45..434197078 100644
--- a/httemplate/search/elements/report_svc_Common.html
+++ b/httemplate/search/elements/report_svc_Common.html
@@ -27,22 +27,50 @@ Example:
     </TR>
 
 % unless ( $custnum ) {
+
     <& /elements/tr-select-agent.html,
-                   'curr_value'    => scalar( $cgi->param('agentnum') ),
-                   'disable_empty' => 0,
+         curr_value    => scalar( $cgi->param('agentnum') ),
+         disable_empty => 0,
+    &>
+
+    <& /elements/tr-select-cust_main-status.html,
+         label         => 'Customer Status',
+         field         => 'cust_status',
+    &>
+
+    <& /elements/tr-select-payby.html,
+         label         => emt('Payment method:'),
+         payby_type    => 'cust',
+         multiple      => 1,
+         all_selected  => 1,
+    &>
+
+    <& /elements/tr-input-money.html,
+         label         => 'Balance over',
+         field         => 'balance',
+    &>
+
+    <& /elements/tr-input-text.html,
+         label         => 'Balance age (days)',
+         field         => 'balance_days',
+         size          => 4,
     &>
 
+% }
+
 %   # just this customer's domains?
 %#    <& /elements/tr-select-domain.html,
 %#                   'element_name'  => 'domsvc',
 %#                   'curr_value'    => scalar( $cgi->param('domsvc') ),
 %#                   'disable_empty' => 0,
 %#    &>
-% }
 
     <& /elements/tr-selectmultiple-part_pkg.html &> 
 
-    <& /elements/tr-select-part_svc.html, 'svcdb'=>$svcdb &> 
+    <& /elements/tr-select-part_svc.html,
+         'svcdb' => $svcdb,
+         'label' => 'Services',
+    &> 
 
     <TR>
       <TH CLASS="background" COLSPAN=2>&nbsp;</TH>
@@ -80,7 +108,7 @@ die "access denied"
 my $title = $opt{'title'};
 
 #false laziness w/report_cust_pkg.html
-my $custnum = '';
+my( $custnum, $cust_main) = ('', '');
 if ( $cgi->param('custnum') =~ /^(\d+)$/ ) {
   $custnum = $1;
   my $cust_main = qsearchs({
diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi
index 7fadbbbb7..65aa1ae14 100644
--- a/httemplate/search/svc_phone.cgi
+++ b/httemplate/search/svc_phone.cgi
@@ -56,8 +56,6 @@ die "access denied"
 my $conf = new FS::Conf;
 
 my @select = ();
-my %svc_phone = ();
-my @extra_sql = ();
 my $orderby = 'ORDER BY svcnum';
 
 my @header = ();
@@ -65,9 +63,12 @@ my @fields = ();
 my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ];
 my $redirect = $link;
 
+my %search_hash = ();
+my @extra_sql = ();
+
 if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
-  push @extra_sql, 'pkgnum IS NULL'
+  $search_hash{'unlinked'} = 1
     if $cgi->param('magic') eq 'unlinked';
 
   if ( $cgi->param('sortby') =~ /^(\w+)$/ ) {
@@ -121,68 +122,29 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
 } elsif ( $cgi->param('magic') =~ /^advanced$/ ) {
 
-  if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) {
-    push @extra_sql, "agentnum = $1";
+  for (qw( agentnum custnum cust_status balance balance_days cust_fields )) {
+    $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
   }
 
-  my $pkgpart = [ grep /^(\d+)$/, $cgi->param('pkgpart') ];
-  if ( @$pkgpart ) {
-    push @extra_sql,
-      'cust_pkg.pkgpart IN ('. join(',', @$pkgpart ). ')';
-  }
-  
-  my $svcpart = [ grep /^(\d+)$/, $cgi->param('svcpart') ];
-  if ( @$svcpart ) {
-    push @extra_sql,
-      'svcpart IN ('. join(',', @$svcpart ). ')';
+  for (qw( payby pkgpart svcpart )) {
+    $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_);
   }
 
 } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) {
-  push @extra_sql, "svcpart = $1";
+  $search_hash{'svcpart'} = [ $1 ];
 } else {
   $cgi->param('phonenum') =~ /^([\d\- ]+)$/; 
-  ( $svc_phone{'phonenum'} = $1 ) =~ s/\D//g;
+  my $phonenum = $1;
+  $phonenum =~ s/\D//g;
+  push @extra_sql, "phonenum = '$phonenum'";
 }
 
-my $addl_from = ' LEFT JOIN cust_svc  USING ( svcnum  ) '.
-                ' LEFT JOIN part_svc  USING ( svcpart ) '.
-                ' LEFT JOIN cust_pkg  USING ( pkgnum  ) '.
-                ' LEFT JOIN cust_main USING ( custnum ) ';
-
-#here is the agent virtualization
-push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql(
-                   'null_right' => 'View/link unlinked services'
-                 );
+$search_hash{'addl_select'} = \@select;
+$search_hash{'order_by'} = $orderby;
+$search_hash{'where'} = \@extra_sql;
 
-my $extra_sql = '';
-if ( @extra_sql ) {
-  $extra_sql = ( keys(%svc_phone) ? ' AND ' : ' WHERE ' ).
-               join(' AND ', @extra_sql );
-}
-
-my $count_query = "SELECT COUNT(*) FROM svc_phone $addl_from ";
-if ( keys %svc_phone ) {
-  $count_query .= ' WHERE '.
-                    join(' AND ', map "$_ = ". dbh->quote($svc_phone{$_}),
-                                      keys %svc_phone
-                        );
-}
-$count_query .= $extra_sql;
-
-my $sql_query = {
-  'table'     => 'svc_phone',
-  'hashref'   => \%svc_phone,
-  'select'    => join(', ',
-                   'svc_phone.*',
-                   'part_svc.svc',
-                   @select,
-                   'cust_main.custnum',
-                   FS::UI::Web::cust_sql_fields(),
-                 ),
-  'extra_sql' => $extra_sql,
-  'order_by'  => $orderby,
-  'addl_from' => $addl_from,
-};
+my $sql_query = FS::svc_phone->search(\%search_hash);
+my $count_query = delete($sql_query->{'count_query'});
 
 #smaller false laziness w/svc_*.cgi here
 my $link_cust = sub {