From: Christopher Burger <burgerc@freeside.biz>
Date: Tue, 19 Sep 2017 16:12:16 +0000 (-0400)
Subject: RT# 77532 - Updated advanced customer report to search contacts
X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=498c7b956b39a5a7e13d299f847f0827bf0b32ba

RT# 77532 - Updated advanced customer report to search contacts
---

diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index e8b1bde15..21d598618 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -1052,6 +1052,45 @@ sub search {
   my(@extra_headers) = ();
   my(@extra_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'}) {
 
     #my $pkg_join = '';
diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html
index c89787953..7197bbde7 100755
--- a/httemplate/search/cust_main.html
+++ b/httemplate/search/cust_main.html
@@ -68,6 +68,11 @@ for my $param (qw( classnum refnum pkg_classnum )) {
   $search_hash{$param} = [ $cgi->param($param) ];
 }
 
+#contacts
+$search_hash{'contacts'} = {
+  map { $_ => $cgi->param($_), } grep { /^(contacts_*)/ && $cgi->param($_) } keys $cgi->Vars
+};
+
 #tags
 my $params = $cgi->Vars;
 $search_hash{'tagnum'} = [
diff --git a/httemplate/search/elements/options_cust_contacts.html b/httemplate/search/elements/options_cust_contacts.html
new file mode 100644
index 000000000..cfbf834b0
--- /dev/null
+++ b/httemplate/search/elements/options_cust_contacts.html
@@ -0,0 +1,36 @@
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('First name') |h %></TH>
+      <TD><INPUT TYPE="text" NAME="<%$field_prefix%>firstname" SIZE=54></TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Last name') |h %></TH>
+      <TD><INPUT TYPE="text" NAME="<%$field_prefix%>lastname" SIZE=54></TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Email') |h %></TH>
+      <TD><INPUT TYPE="text" NAME="<%$field_prefix%>email" SIZE=54></TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Home Phone') |h %></TH>
+      <TD><INPUT TYPE="text" NAME="<%$field_prefix%>homephone" SIZE=54></TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Work Phone') |h %></TH>
+      <TD><INPUT TYPE="text" NAME="<%$field_prefix%>workphone" SIZE=54></TD>
+    </TR>
+
+    <TR>
+      <TH ALIGN="right" VALIGN="center"><% mt('Mobile Phone') |h %></TH>
+      <TD><INPUT TYPE="text" NAME="<%$field_prefix%>mobilephone" SIZE=54></TD>
+    </TR>
+
+<%init>
+
+my %opt = @_;
+my $field_prefix = $opt{'pre_fix'} ? $opt{'pre_fix'} : '';
+
+</%init>
\ No newline at end of file
diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html
index 14e784454..0a6726215 100755
--- a/httemplate/search/report_cust_main.html
+++ b/httemplate/search/report_cust_main.html
@@ -166,6 +166,14 @@
   </TABLE>
   <BR>
 
+  <FONT CLASS="fsinnerbox-title"><% emt('Contacts search options') %></FONT>
+  <TABLE CLASS="fsinnerbox">
+    <& elements/options_cust_contacts.html,
+        'pre_fix'   => 'contacts_',
+    &>
+  </TABLE>
+  <BR>
+
   <FONT CLASS="fsinnerbox-title"><% emt('Billing search options') %></FONT>
   <TABLE CLASS="fsinnerbox">