RT# 73422 Changes to report Customer Contacts

[freeside.git] / FS / FS / cust_main / Search.pm

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index e8b1bde..815304b 100644 (file)
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -1,6 +1,7 @@
 package FS::cust_main::Search;
 
 use strict;
+use Carp qw( croak );
 use base qw( Exporter );
 use vars qw( @EXPORT_OK $DEBUG $me $conf @fuzzyfields );
 use String::Approx qw(amatch);
@@ -804,15 +805,51 @@ sub search {
     unless $params->{'cancelled_pkgs'};
 
   ##
-  # "with email address(es)" checkbox
+  # "with email address(es)" checkbox,
+  #    also optionally: with_email_dest and with_contact_type
   ##
 
-  push @where,
-    'EXISTS ( SELECT 1 FROM contact_email
+  if ($params->{with_email}) {
+    my @email_dest;
+    my $email_dest_sql;
+    my $contact_type_sql;
+
+    if ($params->{with_email_dest}) {
+      croak unless ref $params->{with_email_dest} eq 'ARRAY';
+
+      @email_dest = @{$params->{with_email_dest}};
+      $email_dest_sql =
+        " AND ( ".
+        join(' OR ',map(" cust_contact.${_}_dest IS NOT NULL ", @email_dest)).
+        " ) ";
+        # Can't use message_dist = 'Y' because single quotes are escaped later
+    }
+    if ($params->{with_contact_type}) {
+      croak unless ref $params->{with_contact_type} eq 'ARRAY';
+
+      my @contact_type = grep {/^\d+$/ && $_ > 0} @{$params->{with_contact_type}};
+      my $has_null_type = 0;
+      $has_null_type = 1 if grep { $_ eq 0 } @{$params->{with_contact_type}};
+      my $hnt_sql;
+      if ($has_null_type) {
+        $hnt_sql  = ' OR ' if @contact_type;
+        $hnt_sql .= ' cust_contact.classnum IS NULL ';
+      }
+
+      $contact_type_sql =
+        " AND ( ".
+        join(' OR ', map(" cust_contact.classnum = $_ ", @contact_type)).
+        $hnt_sql.
+        " ) ";
+    }
+    push @where,
+      "EXISTS ( SELECT 1 FROM contact_email
                 JOIN cust_contact USING (contactnum)
                 WHERE cust_contact.custnum = cust_main.custnum
-            )'
-    if $params->{'with_email'};
+                $email_dest_sql
+                $contact_type_sql
+              ) ";
+  }
 
   ##
   # "with postal mail invoices" checkbox
@@ -1049,8 +1086,48 @@ sub search {
                  FS::UI::Web::cust_sql_fields($params->{'cust_fields'}),
                );
 
-  my(@extra_headers) = ();
-  my(@extra_fields)  = ();
+  my @extra_headers     = ();
+  my @extra_fields      = ();
+  my @extra_sort_fields = ();
+
+  ## search contacts
+  if ($params->{'contacts'}) {
+    my $contact_params = $params->{'contacts'};
+
+    $addl_from .=
+      ' LEFT JOIN cust_contact ON ( cust_main.custnum = cust_contact.custnum ) ';
+
+    if ($contact_params->{'contacts_firstname'} || $contact_params->{'contacts_lastname'}) {
+      $addl_from .= ' LEFT JOIN contact ON ( cust_contact.contactnum = contact.contactnum ) ';
+      my $first_query = " AND contact.first = '" . $contact_params->{'contacts_firstname'} . "'"
+        unless !$contact_params->{'contacts_firstname'};
+      my $last_query = " AND contact.last = '" . $contact_params->{'contacts_lastname'} . "'"
+        unless !$contact_params->{'contacts_lastname'};
+      $extra_sql .= " AND ( '1' $first_query $last_query )";
+    }
+
+    if ($contact_params->{'contacts_email'}) {
+      $addl_from .= ' LEFT JOIN contact_email ON ( cust_contact.contactnum = contact_email.contactnum ) ';
+      $extra_sql .= " AND ( contact_email.emailaddress = '" . $contact_params->{'contacts_email'} . "' )";
+    }
+
+    if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) {
+      $addl_from .= ' LEFT JOIN contact_phone ON ( cust_contact.contactnum = contact_phone.contactnum ) ';
+      my $contacts_mobilephone;
+      foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) {
+        (my $num = $contact_params->{$phone}) =~ s/\W//g;
+        if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; }
+      }
+      my $home_query = " AND ( contact_phone.phonetypenum = '2' AND contact_phone.phonenum = '" . $contact_params->{'contacts_homephone'} . "' )"
+        unless !$contact_params->{'contacts_homephone'};
+      my $work_query = " AND ( contact_phone.phonetypenum = '1' AND contact_phone.phonenum = '" . $contact_params->{'contacts_workphone'} . "' )"
+        unless !$contact_params->{'contacts_workphone'};
+      my $mobile_query = " AND ( contact_phone.phonetypenum = '3' AND contact_phone.phonenum = '" . $contact_params->{'contacts_mobilephone'} . "' )"
+        unless !$contact_params->{'contacts_mobilephone'};
+      $extra_sql .= " AND ( '1' $home_query $work_query $mobile_query )";
+    }
+
+  }
 
   if ($params->{'flattened_pkgs'}) {
 
@@ -1095,6 +1172,7 @@ sub search {
                                          my $p = $a[!.--$headercount. q!];
                                          $p;
                                         };!;
+      unshift @extra_sort_fields, '';
     }
 
   }
@@ -1110,21 +1188,23 @@ sub search {
 
     unshift @extra_headers, 'Referrals';
     unshift @extra_fields, 'num_referrals';
+    unshift @extra_sort_fields, 'num_referrals';
 
   }
 
   my $select = join(', ', @select);
 
   my $sql_query = {
-    'table'         => 'cust_main',
-    'select'        => $select,
-    'addl_from'     => $addl_from,
-    'hashref'       => {},
-    'extra_sql'     => $extra_sql,
-    'order_by'      => $orderby,
-    'count_query'   => $count_query,
-    'extra_headers' => \@extra_headers,
-    'extra_fields'  => \@extra_fields,
+    'table'             => 'cust_main',
+    'select'            => $select,
+    'addl_from'         => $addl_from,
+    'hashref'           => {},
+    'extra_sql'         => $extra_sql,
+    'order_by'          => $orderby,
+    'count_query'       => $count_query,
+    'extra_headers'     => \@extra_headers,
+    'extra_fields'      => \@extra_fields,
+    'extra_sort_fields' => \@extra_sort_fields,
   };
   $sql_query;
 
@@ -1347,4 +1427,3 @@ L<FS::cust_main>, L<FS::Record>
 =cut
 
 1;
-