RT 77532 - added contact phone numbers to advanced customer report

Conflicts:
	FS/FS/ConfDefaults.pm
	FS/FS/cust_main.pm

8 files changed, 93 insertions, 45 deletions

diff --git a/FS/FS/ConfDefaults.pm b/FS/FS/ConfDefaults.pm
index 2c24b13..12b1d8a 100644
--- a/FS/FS/ConfDefaults.pm
+++ b/FS/FS/ConfDefaults.pm
@@ -36,9 +36,9 @@ sub cust_fields_avail { (
   'Agent | Agent Cust# or Cust# | Cust. Status | Customer' =>
     'Agent | Agent Cust# | Status | Last, First or Company (Last, First)',
 
-  'Customer | Day phone | Night phone | Mobile phone | Fax number' =>
+  "Customer | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s)" =>
     'Customer | (all phones)',
-  'Cust# | Customer | Day phone | Night phone | Mobile phone | Fax number' =>
+  'Cust# | Customer | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s)' =>
     'custnum | Customer | (all phones)',
 
   'Cust. Status | Name | Company' =>
@@ -56,25 +56,25 @@ sub cust_fields_avail { (
   'Cust# | Cust. Status | Name | Company' =>
     'custnum | Status | Last, First | Company',
 
-  'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Invoicing email(s)' =>
+  'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | Invoicing email(s)' =>
     'custnum | Status | Last, First | Company | (address) | (all phones) | Invoicing email(s)',
 
-  'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Invoicing email(s) | Current Balance' =>
+  'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | Invoicing email(s) | Current Balance' =>
     'custnum | Status | Last, First | Company | (address) | (all phones) | Invoicing email(s) | Current Balance',
 
-  'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s)' =>
+  'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s)' =>
     'custnum | Status | Last, First | Company | (address) | (all phones) | (service address) | Invoicing email(s)',
 
-  'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s) | Current Balance' =>
+  'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s) | Current Balance' =>
     'custnum | Status | Last, First | Company | (address) | (all phones) | (service address) | Invoicing email(s) | Current Balance',
 
-  'Cust# | Agent Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s) | Current Balance' =>
+  'Cust# | Agent Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s) | Current Balance' =>
     'custnum | Agent Cust# | Status | Last, First | Company | (address) | (all phones) | (service address) | Invoicing email(s) | Current Balance',
 
-  'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | (bill) Latitude | (bill) Longitude | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | (service) Latitude | (service) Longitude | Invoicing email(s) | Current Balance' =>
+  'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | (bill) Latitude | (bill) Longitude | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | (service) Latitude | (service) Longitude | Invoicing email(s) | Current Balance' =>
     'custnum | Status | Last, First | Company | (address+coord) | (all phones) | (service address+coord) | Invoicing email(s) | Current Balance',
 
-  'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | (bill) Latitude | (bill) Longitude | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | (service) Latitude | (service) Longitude | Invoicing email(s) | Current Balance | Advertising Source' =>
+  'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | (bill) Latitude | (bill) Longitude | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | (service) Latitude | (service) Longitude | Invoicing email(s) | Current Balance | Advertising Source' =>
     'custnum | Status | Last, First | Company | (address+coord) | (all phones) | (service address+coord) | Invoicing email(s) | Current Balance | Advertising Source',
 
   'Invoicing email(s)' => 'Invoicing email(s)',
diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm
index 158a855..edd3ca8 100644
--- a/FS/FS/UI/Web.pm
+++ b/FS/FS/UI/Web.pm
@@ -7,7 +7,7 @@ use Carp qw( confess );
 use HTML::Entities;
 use FS::Conf;
 use FS::Misc::DateTime qw( parse_datetime day_end );
-use FS::Record qw(dbdef);
+use FS::Record qw(dbdef qsearch);
 use FS::cust_main;  # are sql_balance and sql_date_balance in the right module?
 
 #use vars qw(@ISA);
@@ -354,6 +354,25 @@ sub cust_header {
   $header2method{'Cust#'} = 'display_custnum'
     if $conf->exists('cust_main-default_agent_custid');
 
+foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) {
+  $header2method{'Contact '.$phone_type->typename.' phone(s)'} = sub {
+    my $self = shift;
+    my $num = $phone_type->phonetypenum;
+
+    my @phones;
+    foreach ($self->contact_list_name_phones) {
+      my $data = [
+        {
+          'data'  => $_->first.' '.$_->last.' '.FS::contact_phone::phonenum_pretty($_),
+        },
+      ];
+      push @phones, $data if $_->phonetypenum eq $phone_type->phonetypenum;
+    }
+  return \@phones;
+  };
+
+}
+
   my %header2colormethod = (
     'Cust. Status' => 'cust_statuscolor',
   );
diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index ac300b5..49760a1 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -3376,6 +3376,32 @@ sub contact_list_email {
   @emails;
 }
 
+=item contact_list_name_phones
+
+Returns a list of contact phone numbers.
+{ phonetypenum => '1', phonenum => 'xxxxxxxxxx', first => 'firstname', last => 'lastname', countrycode => '1' }
+
+=cut
+
+sub contact_list_name_phones {
+  my $self = shift;
+  my $phone_type = shift;
+
+  warn "$me contact_list_phones" if $DEBUG;
+
+  return () if !$self->custnum; # not yet inserted
+  return map { $_ }
+    qsearch({
+        table     => 'cust_contact',
+        select    => 'phonetypenum, phonenum, first, last, countrycode',
+        addl_from => ' JOIN contact USING (contactnum) '.
+                     ' JOIN contact_phone USING (contactnum)',
+        hashref   => { 'custnum' => $self->custnum, 'phonetypenum' => $phone_type, },
+        order_by  => 'ORDER BY custcontactnum DESC',
+        extra_sql => '',
+    });
+}
+
 =item referral_custnum_cust_main
 
 Returns the customer who referred this customer (or the empty string, if
diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index ae219c8..63f10fb 100644
--- a/FS/FS/cust_main/Search.pm
+++ b/FS/FS/cust_main/Search.pm
@@ -1090,25 +1090,24 @@ sub search {
               ) ";
     }
 
-    if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) {
-      foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) {
+    if ( grep { /^contacts_phonetypenum(\d+)$/ } keys %{ $contact_params } ) {
+      my $phone_query;
+      foreach my $phone ( grep { /^contacts_phonetypenum(\d+)$/ } keys %{ $contact_params } ) {
+        $phone =~ /^contacts_phonetypenum(\d+)$/ or die "No phone type num $1 from $phone";
+        my $phonetypenum = $1;
         (my $num = $contact_params->{$phone}) =~ s/\W//g;
         if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; }
+        $phone_query .= " AND ( contact_phone.phonetypenum = '".$phonetypenum."' AND contact_phone.phonenum = '" . $contact_params->{$phone} . "' )"
+        unless !$contact_params->{$phone};
       }
-      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'};
       push @where,
       "EXISTS ( SELECT 1 FROM contact_phone
                 JOIN cust_contact USING (contactnum)
                 WHERE cust_contact.custnum = cust_main.custnum
-                $home_query $work_query $mobile_query
+                $phone_query
               ) ";
     }
-}
+  }
 
 
   ##
diff --git a/httemplate/elements/select-cust-fields.html b/httemplate/elements/select-cust-fields.html
index 5e30638..7396808 100644
--- a/httemplate/elements/select-cust-fields.html
+++ b/httemplate/elements/select-cust-fields.html
@@ -1,8 +1,14 @@
 <%init>
   my( $cust_fields, %opt ) = @_;
 
-  use FS::ConfDefaults;
-  $opt{'avail_fields'} ||= [ FS::ConfDefaults->cust_fields_avail() ];
+  my @fields = FS::ConfDefaults->cust_fields_avail();
+  my $contact_phone_list;
+  foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) {
+    $contact_phone_list .= " | Contact ".$phone_type->typename." phone(s)";
+  }
+  @fields = map {s/\| Contact phone\(s\)/$contact_phone_list/g; $_; } @fields;
+
+  $opt{'avail_fields'} ||= [ @fields ];
 
   tie my %hash, 'Tie::IxHash', @{ $opt{'avail_fields'} };
 </%init>
diff --git a/httemplate/elements/tr-select-cust-fields.html b/httemplate/elements/tr-select-cust-fields.html
index dd85133..62b8144 100644
--- a/httemplate/elements/tr-select-cust-fields.html
+++ b/httemplate/elements/tr-select-cust-fields.html
@@ -6,6 +6,13 @@
 
 my( $cust_fields, %opt ) = @_;
 
-$opt{'avail_fields'} ||= [ FS::ConfDefaults->cust_fields_avail() ];
+my @fields = FS::ConfDefaults->cust_fields_avail();
+my $contact_phone_list;
+foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) {
+  $contact_phone_list .= " | Contact ".$phone_type->typename." phone(s)";
+}
+@fields = map {s/\| Contact phone\(s\)/$contact_phone_list/g; $_; } @fields;
+
+$opt{'avail_fields'} ||= [ @fields ];
 
 </%init>
diff --git a/httemplate/search/contact.html b/httemplate/search/contact.html
index 24cb237..0f2b283 100644
--- a/httemplate/search/contact.html
+++ b/httemplate/search/contact.html
@@ -38,7 +38,6 @@ my $classnum_null = grep{ $_ eq 0           } $cgi->param('classnum');
 # Catch destination values from dest multi-checkbox, default to message
 # irrelevant to prospect contacts
 my @dest = grep{ /^(message|invoice)$/ } $cgi->param('dest');
-@dest = ('message') unless @dest;
 
 # Cache the contact_class table
 my %classname =
@@ -125,7 +124,7 @@ if (@classnum || $classnum_null) {
 if (@dest && $link eq 'cust_main') {
   my @stm;
   push @stm, "cust_contact.${_}_dest IS NOT NULL" for @dest;
-  $extra_sql .= "\nAND (".join(' OR ',@stm).') ';
+  $extra_sql .= "\nAND (".join(' AND ',@stm).') ';
 }
 
 if ($DEBUG) {
@@ -141,20 +140,23 @@ if ($DEBUG) {
 
 # Prepare to display phone numbers
 # adds 3 additional queries per table record :-(
-my %phonetype = (qw/1 Work 2 Home 3 Mobile 4 Fax/);
-my %phoneid   = (qw/Work 1 Home 2 Mobile 3 Fax 4/);
 my $get_phone_sub = sub {
   my $type = shift;
   return sub {
     my $rec = shift;
     my @p = qsearch('contact_phone', {
       contactnum => $rec->contact_contactnum,
-      phonetypenum => $phoneid{$type}
+      phonetypenum => $type,
     });
-    @p ? (join ', ',map{$_->phonenum} @p) : undef;
+    @p ? (join ', ',map{$_->phonenum_pretty} @p) : undef;
   };
 };
 
+my @phones;
+foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) {
+  push @phones, { label => $phone_type->typename.' Phone', field => $get_phone_sub->($phone_type->phonetypenum), };
+}
+
 # Cache contact types
 my %classname =
   map {$_->classnum => $_->classname}
@@ -166,9 +168,7 @@ my @report = (
   { label => 'Last',   field => 'contact_last'  },
   { label => 'Title',  field => 'contact_title' },
   { label => 'E-Mail', field => 'contact_email_emailaddress' },
-  { label => 'Work Phone',   field => $get_phone_sub->('Work') },
-  { label => 'Mobile Phone', field => $get_phone_sub->('Mobile') },
-  { label => 'Home Phone',   field => $get_phone_sub->('Home') },
+  @phones,
   { label => 'Type',
     field => sub {
       my $rec = shift;
diff --git a/httemplate/search/elements/options_cust_contacts.html b/httemplate/search/elements/options_cust_contacts.html
index cfbf834..8a6b769 100644
--- a/httemplate/search/elements/options_cust_contacts.html
+++ b/httemplate/search/elements/options_cust_contacts.html
@@ -12,21 +12,12 @@
       <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>
-
+% foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) {
     <TR>
-      <TH ALIGN="right" VALIGN="center"><% mt('Mobile Phone') |h %></TH>
-      <TD><INPUT TYPE="text" NAME="<%$field_prefix%>mobilephone" SIZE=54></TD>
+      <TH ALIGN="right" VALIGN="center"><% $phone_type->typename. ' Phone' |h %></TH>
+      <TD><INPUT TYPE="text" NAME="<% $field_prefix %>phonetypenum<% $phone_type->phonetypenum %>" SIZE=54></TD>
     </TR>
+% }
 
 <%init>