X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=27f23de36b0126108595da63c7b56588fd2232db;hp=65db0a815b96df97a34f24cdb7c1924cdb03690a;hb=6083603212ea08b80c0f48ad61f4421aae85c31d;hpb=d73c1796de9df0ed14469f745d985cd706137d6d

diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi
index 65db0a815..27f23de36 100755
--- a/httemplate/search/cust_main.cgi
+++ b/httemplate/search/cust_main.cgi
@@ -1,27 +1,7 @@
 <%
-#<!-- $Id: cust_main.cgi,v 1.20 2002-01-09 13:29:34 ivan Exp $ -->
-
-use strict;
-#use vars qw( $conf %ncancelled_pkgs %all_pkgs $cgi @cust_main $sortby );
-#use vars qw( $conf %all_pkgs $cgi @cust_main $sortby );
-use vars qw( $conf %all_pkgs $cgi @cust_main $sortby
-             $orderby $maxrecords $limit $offset );
-use CGI;
-use CGI::Carp qw(fatalsToBrowser);
-use IO::Handle;
-use String::Approx qw(amatch);
-use FS::UID qw(dbh cgisuidsetup);
-use FS::Conf;
-use FS::Record qw(qsearch qsearchs dbdef jsearch);
-use FS::CGI qw(header menubar eidiot popurl table);
-use FS::cust_main;
-use FS::cust_svc;
-
-$cgi = new CGI;
-cgisuidsetup($cgi);
-
-$conf = new FS::Conf;
-$maxrecords = $conf->config('maxsearchrecordsperpage');
+
+my $conf = new FS::Conf;
+my $maxrecords = $conf->config('maxsearchrecordsperpage');
 
 #my $cache;
 
@@ -59,31 +39,51 @@ $maxrecords = $conf->config('maxsearchrecordsperpage');
 #) using (custnum)
 #END
 
-$orderby = ''; #removeme
-
-$limit = '';
+my $limit = '';
 $limit .= "LIMIT $maxrecords" if $maxrecords;
 
-$offset = $cgi->param('offset') || 0;
+my $offset = $cgi->param('offset') || 0;
 $limit .= " OFFSET $offset" if $offset;
 
-my $total;
-
-if ( $cgi->param('browse') ) {
-  my $query = $cgi->param('browse');
-  if ( $query eq 'custnum' ) {
-    $sortby=\*custnum_sort;
-    $orderby = 'ORDER BY custnum';
-  } elsif ( $query eq 'last' ) {
-    $sortby=\*last_sort;
-    $orderby = 'ORDER BY last';
-  } elsif ( $query eq 'company' ) {
-    $sortby=\*company_sort;
-    $orderby = 'ORDER BY company';
+my $total = 0;
+
+my(@cust_main, $sortby, $orderby);
+if ( $cgi->param('browse')
+     || $cgi->param('otaker_on')
+     || $cgi->param('agentnum_on')
+) {
+
+  my %search = ();
+  if ( $cgi->param('browse') ) {
+    my $query = $cgi->param('browse');
+    if ( $query eq 'custnum' ) {
+      $sortby=\*custnum_sort;
+      $orderby = "ORDER BY custnum";
+    } elsif ( $query eq 'last' ) {
+      $sortby=\*last_sort;
+      $orderby = "ORDER BY LOWER(last || ' ' || first)";
+    } elsif ( $query eq 'company' ) {
+      $sortby=\*company_sort;
+      $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )";
+    } else {
+      die "unknown browse field $query";
+    }
   } else {
-    die "unknown browse field $query";
+    $sortby = \*last_sort; #??
+    $orderby = "ORDER BY LOWER(last || ' ' || first)"; #??
+    if ( $cgi->param('otaker_on') ) {
+      $cgi->param('otaker') =~ /^(\w{1,32})$/ or eidiot "Illegal otaker\n";
+      $search{otaker} = $1;
+    } elsif ( $cgi->param('agentnum_on') ) {
+      $cgi->param('agentnum') =~ /^(\d+)$/ or eidiot "Illegal agentnum\n";
+      $search{agentnum} = $1;
+    } else {
+      die "unknown query...";
+    }
   }
 
+  my @qual = ();
+
   my $ncancelled = '';
 
   if (  $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me
@@ -91,32 +91,53 @@ if ( $cgi->param('browse') ) {
              && ! $cgi->param('showcancelledcustomers') )
      ) {
     #grep { $_->ncancelled_pkgs || ! $_->all_pkgs }
-    #needed for MySQL???    OR cust_pkg.cancel = \"\"
-    $ncancelled = "
-      WHERE 0 < ( SELECT COUNT(*) FROM cust_pkg
-                    WHERE cust_pkg.custnum = cust_main.custnum
-                      AND ( cust_pkg.cancel IS NULL
-                            OR cust_pkg.cancel = 0
-                          )
-                )
+    push @qual, "
+       ( 0 < ( SELECT COUNT(*) FROM cust_pkg
+                      WHERE cust_pkg.custnum = cust_main.custnum
+                        AND ( cust_pkg.cancel IS NULL
+                              OR cust_pkg.cancel = 0
+                            )
+             )
          OR 0 = ( SELECT COUNT(*) FROM cust_pkg
                     WHERE cust_pkg.custnum = cust_main.custnum
                 )
+       )
     ";
-  }
+   }
+
+  push @qual, FS::cust_main->cancel_sql   if $cgi->param('cancelled');
+  push @qual, FS::cust_main->prospect_sql if $cgi->param('prospect');
+  push @qual, FS::cust_main->active_sql   if $cgi->param('active');
+  push @qual, FS::cust_main->susp_sql     if $cgi->param('suspended');
+
+  #EWWWWWW
+  my $qual = join(' AND ',
+            map { "$_ = ". dbh->quote($search{$_}) } keys %search );
+
+  my $addl_qual = join(' AND ', @qual);
 
-  my $statement = "SELECT COUNT(*) FROM cust_main $ncancelled";
-  my $sth = dbh->prepare($statement)
-    or die dbh->errstr. " doing $statement";
+  if ( $addl_qual ) {
+    $qual .= ' AND ' if $qual;
+    $qual .= $addl_qual;
+  }
+    
+  $qual = " WHERE $qual" if $qual;
+  my $statement = "SELECT COUNT(*) FROM cust_main $qual";
+  my $sth = dbh->prepare($statement) or die dbh->errstr." preparing $statement";
   $sth->execute or die "Error executing \"$statement\": ". $sth->errstr;
 
   $total = $sth->fetchrow_arrayref->[0];
 
-  my @just_cust_main = qsearch('cust_main',{}, '',
-    "$ncancelled $orderby $limit"
-  );    
+  if ( $addl_qual ) {
+    if ( %search ) {
+      $addl_qual = " AND $addl_qual";
+    } else {
+      $addl_qual = " WHERE $addl_qual";
+    }
+  }
 
-  @cust_main = @just_cust_main;
+  @cust_main = qsearch('cust_main', \%search, '',   
+                         "$addl_qual $orderby $limit" );
 
 #  foreach my $cust_main ( @just_cust_main ) {
 #
@@ -135,17 +156,41 @@ if ( $cgi->param('browse') ) {
 
 } else {
   @cust_main=();
-  &cardsearch if $cgi->param('card_on') && $cgi->param('card');
-  &lastsearch if $cgi->param('last_on') && $cgi->param('last_text');
-  &companysearch if $cgi->param('company_on') && $cgi->param('company_text');
-  &referralsearch if $cgi->param('referral_custnum');
+  $sortby = \*last_sort;
+
+  push @cust_main, @{&custnumsearch}
+    if $cgi->param('custnum_on') && $cgi->param('custnum_text');
+  push @cust_main, @{&cardsearch}
+    if $cgi->param('card_on') && $cgi->param('card');
+  push @cust_main, @{&lastsearch}
+    if $cgi->param('last_on') && $cgi->param('last_text');
+  push @cust_main, @{&companysearch}
+    if $cgi->param('company_on') && $cgi->param('company_text');
+  push @cust_main, @{&address2search}
+    if $cgi->param('address2_on') && $cgi->param('address2_text');
+  push @cust_main, @{&phonesearch}
+    if $cgi->param('phone_on') && $cgi->param('phone_text');
+  push @cust_main, @{&referralsearch}
+    if $cgi->param('referral_custnum');
+
+  if ( $cgi->param('company_on') && $cgi->param('company_text') ) {
+    $sortby = \*company_sort;
+    push @cust_main, @{&companysearch};
+  }
 
   @cust_main = grep { $_->ncancelled_pkgs || ! $_->all_pkgs } @cust_main
-    if $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me
-       || ( $conf->exists('hidecancelledcustomers')
-             && ! $cgi->param('showcancelledcustomers') );
+    if ! $cgi->param('cancelled')
+       && (
+         $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me
+         || ( $conf->exists('hidecancelledcustomers')
+               && ! $cgi->param('showcancelledcustomers') )
+       );
+
+  my %saw = ();
+  @cust_main = grep { !$saw{$_->custnum}++ } @cust_main;
 }
 
+my %all_pkgs;
 if ( $conf->exists('hidecancelledpackages' ) ) {
   %all_pkgs = map { $_->custnum => [ $_->ncancelled_pkgs ] } @cust_main;
 } else {
@@ -159,10 +204,16 @@ if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) {
   } else {
     print $cgi->redirect(popurl(2). "view/cust_main.cgi?". $cust_main[0]->custnum);
   }
-  exit;
+  #exit;
 } elsif ( scalar(@cust_main) == 0 ) {
+%>
+<!-- mason kludge -->
+<%
   eidiot "No matching customers found!\n";
 } else { 
+%>
+<!-- mason kludge -->
+<%
 
   $total ||= scalar(@cust_main);
   print header("Customer Search Results",menubar(
@@ -195,19 +246,22 @@ if ( scalar(@cust_main) == 1 && ! $cgi->param('referral_custnum') ) {
     }
   }
   #end pager
-  
-  if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me
-       || ( $conf->exists('hidecancelledcustomers')
-            && ! $cgi->param('showcancelledcustomers')
-          )
-     ) {
-    $cgi->param('showcancelledcustomers', 1);
-    $cgi->param('offset', 0);
-    print qq!( <a href="!. $cgi->self_url. qq!">show cancelled customers</a> )!;
-  } else {
-    $cgi->param('showcancelledcustomers', 0);
-    $cgi->param('offset', 0);
-    print qq!( <a href="!. $cgi->self_url. qq!">hide cancelled customers</a> )!;
+
+  unless ( $cgi->param('cancelled') ) {
+    if ( $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me
+         || ( $conf->exists('hidecancelledcustomers')
+              && ! $cgi->param('showcancelledcustomers')
+            )
+       ) {
+      $cgi->param('showcancelledcustomers', 1);
+      $cgi->param('offset', 0);
+      print qq!( <a href="!. $cgi->self_url. qq!">show!;
+    } else {
+      $cgi->param('showcancelledcustomers', 0);
+      $cgi->param('offset', 0);
+      print qq!( <a href="!. $cgi->self_url. qq!">hide!;
+    }
+    print ' cancelled customers</a> )';
   }
   if ( $cgi->param('referral_custnum') ) {
     $cgi->param('referral_custnum') =~ /^(\d+)$/
@@ -292,11 +346,14 @@ END
     } else {
       $view = $p. 'view/cust_main.cgi?'. $custnum;
     }
+    my $pcompany = $company
+      ? qq!<A HREF="$view"><FONT SIZE=-1>$company</FONT></A>!
+      : '<FONT SIZE=-1>&nbsp;</FONT>';
     print <<END;
     <TR>
       <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$custnum</FONT></A></TD>
       <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$last, $first</FONT></A></TD>
-      <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$company</FONT></A></TD>
+      <TD ROWSPAN=$rowspan>$pcompany</TD>
 END
     if ( defined dbdef->table('cust_main')->column('ship_last') ) {
       my($ship_last,$ship_first,$ship_company)=(
@@ -304,9 +361,12 @@ END
         $cust_main->ship_last ? $cust_main->ship_first : $cust_main->first,
         $cust_main->ship_last ? $cust_main->ship_company : $cust_main->company,
       );
-print <<END;
+      my $pship_company = $ship_company
+        ? qq!<A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A>!
+        : '<FONT SIZE=-1>&nbsp;</FONT>';
+      print <<END;
       <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$ship_last, $ship_first</FONT></A></TD>
-      <TD ROWSPAN=$rowspan><A HREF="$view"><FONT SIZE=-1>$ship_company</FONT></A></TD>
+      <TD ROWSPAN=$rowspan>$pship_company</A></TD>
 END
     }
 
@@ -318,7 +378,7 @@ END
 
       my $pkg = $part_pkg->pkg;
       my $comment = $part_pkg->comment;
-      my $pkgview = $p. 'view/cust_pkg.cgi?'. $pkgnum;
+      my $pkgview = "${p}view/cust_main.cgi?$custnum#cust_pkg$pkgnum";
       my @cust_svc = @{shift @lol_cust_svc};
       #my(@cust_svc) = qsearch( 'cust_svc', { 'pkgnum' => $_->pkgnum } );
       my $rowspan = scalar(@cust_svc) || 1;
@@ -348,19 +408,32 @@ END
 #
 
 sub last_sort {
-  $a->getfield('last') cmp $b->getfield('last');
+  lc($a->getfield('last')) cmp lc($b->getfield('last'))
+  || lc($a->first) cmp lc($b->first);
 }
 
 sub company_sort {
   return -1 if $a->company && ! $b->company;
   return 1 if ! $a->company && $b->company;
-  $a->getfield('company') cmp $b->getfield('company');
+  lc($a->company) cmp lc($b->company)
+  || lc($a->getfield('last')) cmp lc($b->getfield('last'))
+  || lc($a->first) cmp lc($b->first);;
 }
 
 sub custnum_sort {
   $a->getfield('custnum') <=> $b->getfield('custnum');
 }
 
+sub custnumsearch {
+
+  my $custnum = $cgi->param('custnum_text');
+  $custnum =~ s/\D//g;
+  $custnum =~ /^(\d{1,23})$/ or eidiot "Illegal customer number\n";
+  $custnum = $1;
+  
+  [ qsearchs('cust_main', { 'custnum' => $custnum } ) ];
+}
+
 sub cardsearch {
 
   my($card)=$cgi->param('card');
@@ -368,8 +441,9 @@ sub cardsearch {
   $card =~ /^(\d{13,16})$/ or eidiot "Illegal card number\n";
   my($payinfo)=$1;
 
-  push @cust_main, qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'});
-  $sortby=\*last_sort;
+  [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}),
+    qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'})
+  ];
 }
 
 sub referralsearch {
@@ -385,12 +459,12 @@ sub referralsearch {
   } else {
     $depth = 1;
   }
-  push @cust_main, $cust_main->referral_cust_main($depth);
-  $sortby=\*last_sort;
+  [ $cust_main->referral_cust_main($depth) ];
 }
 
 sub lastsearch {
   my(%last_type);
+  my @cust_main;
   foreach ( $cgi->param('last_type') ) {
     $last_type{$_}++;
   }
@@ -399,87 +473,136 @@ sub lastsearch {
     or eidiot "Illegal last name";
   my($last)=$1;
 
-#  if ( $last_type{'Exact'}
-#       && ! $last_type{'Fuzzy'} 
-#     #  && ! $last_type{'Sound-alike'}
-#  ) {
+  if ( $last_type{'Exact'} || $last_type{'Fuzzy'} ) {
+    push @cust_main, qsearch( 'cust_main',
+                              { 'last' => { 'op'    => 'ILIKE',
+                                            'value' => $last    } } );
 
-    push @cust_main, qsearch('cust_main',{'last'=>$last});
-
-    push @cust_main, qsearch('cust_main',{'ship_last'=>$last})
+    push @cust_main, qsearch( 'cust_main',
+                              { 'ship_last' => { 'op'    => 'ILIKE',
+                                                 'value' => $last    } } )
       if defined dbdef->table('cust_main')->column('ship_last');
+  }
 
-#  } else {
-  if ( $last_type{'Fuzzy'} ) {
-
-    &FS::cust_main::check_and_rebuild_fuzzyfiles;
-    my $all_last = &FS::cust_main::all_last;
+  if ( $last_type{'Substring'} || $last_type{'All'} ) {
 
-    my %last;
-    if ($last_type{'Fuzzy'}) { 
-      foreach ( amatch($last, [ qw(i) ], @$all_last) ) {
-        $last{$_}++; 
-      }
-    }
+    push @cust_main, qsearch( 'cust_main',
+                              { 'last' => { 'op'    => 'ILIKE',
+                                            'value' => "%$last%" } } );
 
-    #if ($last_type{'Sound-alike'}) {
-    #}
+    push @cust_main, qsearch( 'cust_main',
+                              { 'ship_last' => { 'op'    => 'ILIKE',
+                                                 'value' => "%$last%" } } )
+      if defined dbdef->table('cust_main')->column('ship_last');
 
-    foreach ( keys %last ) {
-      push @cust_main, qsearch('cust_main',{'last'=>$_});
-      push @cust_main, qsearch('cust_main',{'ship_last'=>$_})
-        if defined dbdef->table('cust_main')->column('ship_last');
-    }
+  }
 
+  if ( $last_type{'Fuzzy'} || $last_type{'All'} ) {
+    push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } );
   }
-  $sortby=\*last_sort;
+
+  #if ($last_type{'Sound-alike'}) {
+  #}
+
+  \@cust_main;
 }
 
 sub companysearch {
 
   my(%company_type);
+  my @cust_main;
   foreach ( $cgi->param('company_type') ) {
     $company_type{$_}++ 
   };
 
-  $cgi->param('company_text') =~ /^([\w \,\.\-\']*)$/
-    or eidiot "Illegal company";
-  my($company)=$1;
+  $cgi->param('company_text') =~
+    /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/
+      or eidiot "Illegal company";
+  my $company = $1;
+
+  if ( $company_type{'Exact'} || $company_type{'Fuzzy'} ) {
+    push @cust_main, qsearch( 'cust_main',
+                              { 'company' => { 'op'    => 'ILIKE',
+                                               'value' => $company } } );
+
+    push @cust_main, qsearch( 'cust_main',
+                              { 'ship_company' => { 'op'    => 'ILIKE',
+                                                    'value' => $company } } )
+      if defined dbdef->table('cust_main')->column('ship_last');
+  }
 
-#  if ( $company_type{'Exact'}
-#       && ! $company_type{'Fuzzy'} 
-#     #  && ! $company_type{'Sound-alike'}
-#  ) {
+  if ( $company_type{'Substring'} || $company_type{'All'} ) {
 
-    push @cust_main, qsearch('cust_main',{'company'=>$company});
+    push @cust_main, qsearch( 'cust_main',
+                              { 'company' => { 'op'    => 'ILIKE',
+                                               'value' => "%$company%" } } );
 
-    push @cust_main, qsearch('cust_main',{'ship_company'=>$company})
+    push @cust_main, qsearch( 'cust_main',
+                              { 'ship_company' => { 'op'    => 'ILIKE',
+                                                    'value' => "%$company%" } })
       if defined dbdef->table('cust_main')->column('ship_last');
 
-#  } else {
-  if ( $company_type{'Fuzzy'} ) {
+  }
 
-    &FS::cust_main::check_and_rebuild_fuzzyfiles;
-    my $all_company = &FS::cust_main::all_company;
+  if ( $company_type{'Fuzzy'} || $company_type{'All'} ) {
+    push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } );
+  }
 
-    my %company;
-    if ($company_type{'Fuzzy'}) { 
-      foreach ( amatch($company, [ qw(i) ], @$all_company ) ) {
-        $company{$_}++;
-      }
-    }
+  if ($company_type{'Sound-alike'}) {
+  }
+
+  \@cust_main;
+}
 
-    #if ($company_type{'Sound-alike'}) {
-    #}
+sub address2search {
+  my @cust_main;
 
-    foreach ( keys %company ) {
-      push @cust_main, qsearch('cust_main',{'company'=>$_});
-      push @cust_main, qsearch('cust_main',{'ship_company'=>$_})
-        if defined dbdef->table('cust_main')->column('ship_last');
-    }
+  $cgi->param('address2_text') =~
+    /^([\w \!\@\#\$\%\&\(\)\-\+\;\:\'\"\,\.\?\/\=]*)$/
+      or eidiot "Illegal address2";
+  my $address2 = $1;
 
+  push @cust_main, qsearch( 'cust_main',
+                            { 'address2' => { 'op'    => 'ILIKE',
+                                              'value' => $address2 } } );
+  push @cust_main, qsearch( 'cust_main',
+                            { 'address2' => { 'op'    => 'ILIKE',
+                                              'value' => $address2 } } )
+    if defined dbdef->table('cust_main')->column('ship_last');
+
+  \@cust_main;
+}
+
+sub phonesearch {
+  my @cust_main;
+
+  my $phone = $cgi->param('phone_text');
+
+  #(no longer really) false laziness with Record::ut_phonen
+  #only works with US/CA numbers...
+  $phone =~ s/\D//g;
+  if ( $phone =~ /^(\d{3})(\d{3})(\d{4})(\d*)$/ ) {
+    $phone = "$1-$2-$3";
+    $phone .= " x$4" if $4;
+  } elsif ( $phone =~ /^(\d{3})(\d{4})$/ ) {
+    $phone = "$1-$2";
+  } elsif ( $phone =~ /^(\d{3,4})$/ ) {
+    $phone = $1;
+  } else {
+    eidiot gettext('illegal_phone'). ": $phone";
   }
-  $sortby=\*company_sort;
 
+  my @fields = qw(daytime night fax);
+  push @fields, qw(ship_daytime ship_night ship_fax)
+    if defined dbdef->table('cust_main')->column('ship_last');
+
+  for my $field ( @fields ) {
+    push @cust_main, qsearch ( 'cust_main', 
+                               { $field => { 'op'    => 'LIKE',
+                                             'value' => "%$phone%" } } );
+  }
+
+  \@cust_main;
 }
+
 %>