X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fcust_main.cgi;h=a3256674bf738e0822635469b5de4be5d8c4c310;hb=f2ccd32479007116f0008ffe67e933300b763b33;hp=247cf8fd68368af321e7f9b67b00bfca93705579;hpb=afe61aa1e4158a49ba67740d947a991e285d5fb1;p=freeside.git

diff --git a/httemplate/search/cust_main.cgi b/httemplate/search/cust_main.cgi
index 247cf8fd6..a3256674b 100755
--- a/httemplate/search/cust_main.cgi
+++ b/httemplate/search/cust_main.cgi
@@ -48,8 +48,12 @@ $limit .= " OFFSET $offset" if $offset;
 my $total = 0;
 
 my(@cust_main, $sortby, $orderby);
+my @select = ();
+my @addl_headers = ();
+my @addl_cols = ();
 if ( $cgi->param('browse')
      || $cgi->param('otaker_on')
+     || $cgi->param('agentnum_on')
 ) {
 
   my %search = ();
@@ -64,6 +68,12 @@ if ( $cgi->param('browse')
     } elsif ( $query eq 'company' ) {
       $sortby=\*company_sort;
       $orderby = "ORDER BY LOWER(company || ' ' || last || ' ' || first )";
+    } elsif ( $query eq 'tickets' ) {
+      $sortby = \*tickets_sort;
+      $orderby = "ORDER BY tickets DESC";
+      push @select, FS::TicketSystem->sql_num_customer_tickets. " as tickets";
+      push @addl_headers, 'Tickets';
+      push @addl_cols, 'tickets';
     } else {
       die "unknown browse field $query";
     }
@@ -73,105 +83,77 @@ if ( $cgi->param('browse')
     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 $ncancelled = '';
+  my @qual = ();
 
-  if ( driver_name eq 'mysql' ) {
-
-       my $query = "CREATE TEMPORARY TABLE temp1_$$ TYPE=MYISAM
-                      SELECT cust_pkg.custnum,COUNT(*) as count
-                        FROM cust_pkg,cust_main
-                          WHERE cust_pkg.custnum = cust_main.custnum
-                                AND ( cust_pkg.cancel IS NULL
-                                      OR cust_pkg.cancel = 0 )
-                          GROUP BY cust_pkg.custnum";
-       my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query";
-       $sth->execute or die "Error executing \"$query\": ". $sth->errstr;
-       $query = "CREATE TEMPORARY TABLE temp2_$$ TYPE=MYISAM
-                   SELECT cust_pkg.custnum,COUNT(*) as count
-                     FROM cust_pkg,cust_main
-                       WHERE cust_pkg.custnum = cust_main.custnum
-                       GROUP BY cust_pkg.custnum";
-       my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query";
-       $sth->execute or die "Error executing \"$query\": ". $sth->errstr;
-  }
+  my $ncancelled = '';
 
   if (  $cgi->param('showcancelledcustomers') eq '0' #see if it was set by me
        || ( $conf->exists('hidecancelledcustomers')
              && ! $cgi->param('showcancelledcustomers') )
      ) {
     #grep { $_->ncancelled_pkgs || ! $_->all_pkgs }
-    if ( driver_name eq 'mysql' ) {
-       $ncancelled = "
-          temp1_$$.custnum = cust_main.custnum
-               AND temp2_$$.custnum = cust_main.custnum
-               AND (temp1_$$.count > 0
-                       OR temp2_$$.count = 0 )
-       ";
-    } else {
-       $ncancelled = "
-          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, "
+       ( 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 );
 
-  if ( $ncancelled ) {
+  my $addl_qual = join(' AND ', @qual);
+
+  if ( $addl_qual ) {
     $qual .= ' AND ' if $qual;
-    $qual .= $ncancelled;
+    $qual .= $addl_qual;
   }
     
   $qual = " WHERE $qual" if $qual;
-  my $statement;
-  if ( driver_name eq 'mysql' ) {
-    $statement = "SELECT COUNT(*) FROM cust_main";
-    $statement .= ", temp1_$$, temp2_$$ $qual" if $qual;
-  } else {
-    $statement = "SELECT COUNT(*) FROM cust_main $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];
 
-  if ( $ncancelled ) {
+  if ( $addl_qual ) {
     if ( %search ) {
-      $ncancelled = " AND $ncancelled";
+      $addl_qual = " AND $addl_qual";
     } else {
-      $ncancelled = " WHERE $ncancelled";
+      $addl_qual = " WHERE $addl_qual";
     }
   }
 
-  my @just_cust_main;
-  if ( driver_name eq 'mysql' ) {
-    @just_cust_main = qsearch('cust_main', \%search, 'cust_main.*',
-                              ",temp1_$$,temp2_$$ $ncancelled $orderby $limit");
+  my $select;
+  if ( @select ) {
+    $select = 'cust_main.*, '. join (', ', @select);
   } else {
-    @just_cust_main = qsearch('cust_main', \%search, '',   
-                              "$ncancelled $orderby $limit" );
+    $select = '*';
   }
-  if ( driver_name eq 'mysql' ) {
-    $query = "DROP TABLE temp1_$$,temp2_$$;";
-    my $sth = dbh->prepare($query) or die dbh->errstr. " preparing $query";
-    $sth->execute or die "Error executing \"$query\": ". $sth->errstr;
-  }
-  @cust_main = @just_cust_main;
+
+  @cust_main = qsearch('cust_main', \%search, $select,   
+                         "$addl_qual $orderby $limit" );
 
 #  foreach my $cust_main ( @just_cust_main ) {
 #
@@ -200,6 +182,10 @@ if ( $cgi->param('browse')
     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');
 
@@ -209,9 +195,12 @@ if ( $cgi->param('browse')
   }
 
   @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;
@@ -273,26 +262,30 @@ 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+)$/
       or eidiot "Illegal referral_custnum\n";
     my $referral_custnum = $1;
     my $cust_main = qsearchs('cust_main', { custnum => $referral_custnum } );
-    print '<FORM METHOD=POST>'.
+    print '<FORM METHOD="GET">'.
           qq!<INPUT TYPE="hidden" NAME="referral_custnum" VALUE="$referral_custnum">!.
           'referrals of <A HREF="'. popurl(2).
           "view/cust_main.cgi?$referral_custnum\">$referral_custnum: ".
@@ -322,6 +315,13 @@ END
           '</FORM>';
   }
 
+  my @custom_priorities = ();
+  if ( $conf->config('ticket_system-custom_priority_field')
+       && @{[ $conf->config('ticket_system-custom_priority_field-values') ]} ) {
+    @custom_priorities =
+      $conf->config('ticket_system-custom_priority_field-values');
+  }
+
   print "<BR><BR>". $pager. &table(). <<END;
       <TR>
         <TH></TH>
@@ -336,6 +336,10 @@ if ( defined dbdef->table('cust_main')->column('ship_last') ) {
 END
 }
 
+foreach my $addl_header ( @addl_headers ) {
+  print "<TH>$addl_header</TH>";
+}
+
 print <<END;
         <TH>Packages</TH>
         <TH COLSPAN=2>Services</TH>
@@ -343,7 +347,6 @@ print <<END;
 END
 
   my(%saw,$cust_main);
-  my $p = popurl(2);
   foreach $cust_main (
     sort $sortby grep(!$saw{$_->custnum}++, @cust_main)
   ) {
@@ -394,6 +397,50 @@ END
 END
     }
 
+    foreach my $addl_col ( @addl_cols ) {
+      print "<TD ROWSPAN=$rowspan ALIGN=right><FONT SIZE=-1>";
+      if ( $addl_col eq 'tickets' ) {
+        if ( @custom_priorities ) {
+          print &itable('', 0);
+          foreach my $priority ( @custom_priorities, '' ) {
+          
+            my $num =
+              FS::TicketSystem->num_customer_tickets($custnum,$priority);
+            my $ahref = '';
+            $ahref= '<A HREF="'.
+                    FS::TicketSystem->href_customer_tickets($custnum,$priority).
+                    '">'
+              if $num;
+
+            print '<TR>'.
+                  "  <TD ALIGN=right><FONT SIZE=-1>$ahref$num</A></FONT></TD>".
+                  "<TD ALIGN=left><FONT SIZE=-1>$ahref".
+                  ( $priority || '<i>(none)</i>' ).
+                  "</A></FONT></TD></TR>";
+
+          }
+          print '<TR><TD BGCOLOR="#000000" COLSPAN=2></TD></TR>'.
+                '<TR><TD ALIGN=right><FONT SIZE=-1>';
+        }
+
+        my $ahref = '';
+        $ahref = '<A HREF="'.
+                    FS::TicketSystem->href_customer_tickets($custnum).
+                    '">'
+          if $cust_main->get($addl_col);
+
+        print $ahref. $cust_main->get($addl_col). '</A>';
+        print "</FONT></TD><TD ALIGN=left>".
+              "<FONT SIZE=-1>${ahref}Total</A><FONT>".
+              "</TD></TR></TABLE>"
+          if @custom_priorities;
+
+      } else {
+        print $cust_main->get($addl_col);
+      }
+      print "</FONT></TD>";
+    }
+
     my($n1)='';
     foreach ( @{$all_pkgs{$custnum}} ) {
       my $pkgnum = $_->pkgnum;
@@ -402,7 +449,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;
@@ -448,12 +495,16 @@ sub custnum_sort {
   $a->getfield('custnum') <=> $b->getfield('custnum');
 }
 
+sub tickets_sort {
+  $b->getfield('tickets') <=> $a->getfield('tickets');
+}
+
 sub custnumsearch {
 
   my $custnum = $cgi->param('custnum_text');
   $custnum =~ s/\D//g;
   $custnum =~ /^(\d{1,23})$/ or eidiot "Illegal customer number\n";
-  my $custnum = $1;
+  $custnum = $1;
   
   [ qsearchs('cust_main', { 'custnum' => $custnum } ) ];
 }
@@ -465,7 +516,9 @@ sub cardsearch {
   $card =~ /^(\d{13,16})$/ or eidiot "Illegal card number\n";
   my($payinfo)=$1;
 
-  [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}) ];
+  [ qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'CARD'}),
+    qsearch('cust_main',{'payinfo'=>$payinfo, 'payby'=>'DCRD'})
+  ];
 }
 
 sub referralsearch {
@@ -520,28 +573,12 @@ sub lastsearch {
   }
 
   if ( $last_type{'Fuzzy'} || $last_type{'All'} ) {
-
-    &FS::cust_main::check_and_rebuild_fuzzyfiles;
-    my $all_last = &FS::cust_main::all_last;
-
-    my %last;
-    if ( $last_type{'Fuzzy'} || $last_type{'All'} ) { 
-      foreach ( amatch($last, [ qw(i) ], @$all_last) ) {
-        $last{$_}++; 
-      }
-    }
-
-    #if ($last_type{'Sound-alike'}) {
-    #}
-
-    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');
-    }
-
+    push @cust_main, FS::cust_main->fuzzy_search( { 'last' => $last } );
   }
 
+  #if ($last_type{'Sound-alike'}) {
+  #}
+
   \@cust_main;
 }
 
@@ -553,9 +590,10 @@ sub companysearch {
     $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',
@@ -582,28 +620,64 @@ sub companysearch {
   }
 
   if ( $company_type{'Fuzzy'} || $company_type{'All'} ) {
+    push @cust_main, FS::cust_main->fuzzy_search( { 'company' => $company } );
+  }
 
-    &FS::cust_main::check_and_rebuild_fuzzyfiles;
-    my $all_company = &FS::cust_main::all_company;
+  if ($company_type{'Sound-alike'}) {
+  }
 
-    my %company;
-    if ( $company_type{'Fuzzy'} || $company_type{'All'} ) { 
-      foreach ( amatch($company, [ qw(i) ], @$all_company ) ) {
-        $company{$_}++;
-      }
-    }
+  \@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";
+  }
+
+  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;
 }
+
 %>