RT# 73422 Changes to report Customer Contacts

7 files changed, 233 insertions, 27 deletions

diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm
index a82d8a2..7c9868d 100644
--- a/FS/FS/cust_main.pm
+++ b/FS/FS/cust_main.pm
@@ -3072,14 +3072,15 @@ sub contact_list {
 
   # WHERE ...
   # AND (
-  #   ( cust_contact.classnum IN (1,2,3) )
-  #   OR
-  #   ( cust_contact.classnum IS NULL )
-  #
+  #   (
+  #     cust_contact.classnum IN (1,2,3)
+  #     OR
+  #     cust_contact.classnum IS NULL
+  #   )
   #   AND (
-  #     ( cust_contact.invoice_dest = 'Y' )
+  #     cust_contact.invoice_dest = 'Y'
   #     OR
-  #     ( cust_contact.message_dest = 'Y' )
+  #     cust_contact.message_dest = 'Y'
   #   )
   # )
 
@@ -3105,12 +3106,14 @@ sub contact_list {
     $search->{extra_sql} .= ' AND ( ';
 
       if (@or_classnum) {
-        $search->{extra_sql} .= join ' OR ', map {" ($_) "} @or_classnum;
+        $search->{extra_sql} .= ' ( ';
+        $search->{extra_sql} .= join ' OR ', map {" $_ "} @or_classnum;
+        $search->{extra_sql} .= ' ) ';
         $search->{extra_sql} .= ' AND ( ' if @and_dest;
       }
 
       if (@and_dest) {
-        $search->{extra_sql} .= join ' OR ', map {" ($_) "} @and_dest;
+        $search->{extra_sql} .= join ' OR ', map {" $_ "} @and_dest;
         $search->{extra_sql} .= ' ) ' if @or_classnum;
       }
 
diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm
index 2ec87cd..815304b 100644
--- 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
@@ -1390,4 +1427,3 @@ L<FS::cust_main>, L<FS::Record>
 =cut
 
 1;
-
diff --git a/httemplate/elements/select-multiple-contact_class.html b/httemplate/elements/select-multiple-contact_class.html
new file mode 100644
index 0000000..81a71cc
--- /dev/null
+++ b/httemplate/elements/select-multiple-contact_class.html
@@ -0,0 +1,21 @@
+<%doc>
+
+Display a multi-select box containing all Email Types listed in
+the contact_class table.
+
+NOTE:
+  Don't confuse "Contact Type" (contact_email.classnum) with
+  "Customer Class" (cust_main.classnum)
+
+</%doc>
+<% include( '/elements/select-table.html',
+    table        => 'contact_class',
+    hashref      => { disabled => '' },
+    name_col     => 'classname',
+    field        => 'classnum',
+    pre_options  => [ 0 => '(No Type)' ],
+    multiple     => 1,
+    all_selected => 1,
+    @_,
+  )
+%>
diff --git a/httemplate/elements/tr-select-multiple-contact_class.html b/httemplate/elements/tr-select-multiple-contact_class.html
new file mode 100644
index 0000000..5de1293
--- /dev/null
+++ b/httemplate/elements/tr-select-multiple-contact_class.html
@@ -0,0 +1,32 @@
+<%doc>
+
+  Displays Contact Types as a multi-select box.
+
+  If no non-disabled Contact Types have been defined in contact_class table,
+  renders a hidden input field with a blank value.
+
+</%doc>
+
+% if ($has_types) {
+<TR>
+  <TD ALIGN="right"><% $opt{'label'} || emt('Contact Type') %></TD>
+  <TD>
+    <% include( '/elements/select-multiple-contact_class.html', %opt ) %>
+  </TD>
+</TR>
+% } else {
+<INPUT TYPE="hidden" NAME="<% $opt{field} %>" VALUE="">
+% }
+
+<%init>
+
+my %opt = @_;
+$opt{field} ||= $opt{element_name} ||= 'classnum';
+
+my $has_types =()= qsearch({
+  table     => 'contact_class',
+  hashref   => { disabled => '' },
+  extra_sql => ' LIMIT 1 ',
+});
+
+</%init>
diff --git a/httemplate/misc/email-customers.html b/httemplate/misc/email-customers.html
index dc53f6d..4520c75 100644
--- a/httemplate/misc/email-customers.html
+++ b/httemplate/misc/email-customers.html
@@ -174,7 +174,7 @@ Template:
      <& /elements/checkboxes.html,
        'style'               => 'display: inline; vertical-align: top',
        'disable_links'       => 1,
-       'names_list'          => \@contact_checkboxes,
+       'names_list'          => \@optin_checkboxes,
        'element_name_prefix' => 'contact_class_',
        'checked_callback'    => sub {
          # Called for each checkbox
@@ -199,6 +199,27 @@ Template:
    </div>
 % }
  </TD>
+% if (@active_classes) {
+</tr>
+<tr>
+<TD>Contact Type:</TD>
+<TD>
+  <div id="contactclassesdiv">
+    <& /elements/checkboxes.html,
+      'style'               => 'display: inline; vertical-align: top',
+      'disable_links'       => 1,
+      'names_list'          => \@classnum_checkboxes,
+      'element_name_prefix' => 'contact_class_',
+      'checked_callback'    => sub {
+        # Called for each checkbox
+        # Return true to default as checked, false as unchecked
+        my($cgi, $name) = @_;
+        exists $classnum_ischecked{$name};
+      },
+    &>
+  </div>
+</TD>
+% }
 </TR>
 </TABLE>
 <BR>
@@ -342,6 +363,21 @@ if ( !$cgi->param('preview') ) {
 
 } else {
 
+  my @checked_email_dest;
+  my @checked_contact_type;
+  for ($cgi->param) {
+    if (/^contact_class_(.+)$/) {
+      my $f = $1;
+      if ($f eq 'invoice' || $f eq 'message') {
+        push @checked_email_dest, $f;
+      } elsif ( $f =~ /^\d+$/ ) {
+        push @checked_contact_type, $f;
+      }
+    }
+  }
+  $search{with_email_dest} = \@checked_email_dest if @checked_email_dest;
+  $search{with_contact_type} = \@checked_contact_type if @checked_contact_type;
+
   my $sql_query = "FS::$table"->search(\%search);
   my $count_query = delete($sql_query->{'count_query'});
   my $count_sth = dbh->prepare($count_query)
@@ -391,6 +427,8 @@ if ( !$cgi->param('preview') ) {
     $sql_query->{'select'} = "$table.*";
     $sql_query->{'order_by'} = '';
     my $object = qsearchs($sql_query);
+    # Could use better error handling here...
+    die "No customers match the search criteria" unless ref $object;
     $cust = $object->cust_main;
     my %msgopts = (
       'cust_main' => $cust,
@@ -435,16 +473,35 @@ if ( !$cgi->param('preview') ) {
   }
 }
 
-my @contact_checkboxes = (
+# Build data structures for "Opt In" and "Contact Type" checkboxes
+#
+# By default, message recipients will be selected, this is a message.
+# By default, all Contact Types will be selected, but this may be
+#   overridden by passing 'classnums' get/post values.  If no contact
+#   types have been defined, the option will not be presented.
+
+my @active_classes = qsearch(contact_class => {disabled => ''} );
+
+$CGI::LIST_CONTEXT_WARN = 0;
+my @classnums = grep{ /^\d+$/ } $cgi->param('classnums');
+
+my %classnum_ischecked;
+if (@classnums) {
+  # values passed to form
+  $classnum_ischecked{$_} = 1 for @classnums;
+} else {
+  # default values
+  $classnum_ischecked{$_->classnum} = 1 for @active_classes;
+  $classnum_ischecked{0} = 1;
+}
+
+my @optin_checkboxes = (
   [ 'message' => { label => 'Message recipients' } ],
   [ 'invoice' => { label => 'Invoice recipients' } ],
 );
-
-foreach my $class (qsearch('contact_class', { disabled => '' })) {
-  push @contact_checkboxes, [
-    $class->classnum,
-    { label => $class->classname }
-  ];
-}
+my @classnum_checkboxes = (
+  [ '0' => { label => '(None)' }],
+  map { [ $_->classnum => {label => $_->classname} ] } @active_classes,
+);
 
 </%init>
diff --git a/httemplate/search/contact.html b/httemplate/search/contact.html
index 5f02fef..9abbcfa 100644
--- a/httemplate/search/contact.html
+++ b/httemplate/search/contact.html
@@ -11,12 +11,19 @@
   header        => \@header,
   fields        => \@fields,
   links         => \@links,
+  html_init     => $send_email_link,
 &>
 <%init>
 
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List contacts');
 
+# Catch classnum values from multi-select box
+# A classnum of 0 indicates to include rows where classnum IS NULL
+$CGI::LIST_CONTEXT_WARN = 0;
+my @classnum      = grep{ /^\d+$/ && $_ > 0 } $cgi->param('classnum');
+my $classnum_null = grep{ $_ eq 0           } $cgi->param('classnum');
+
 my @select = 'contact.contactnum AS contact_contactnum'; #if we select it as bare contactnum, the multi-customer listings go away
 push @select, map "contact.$_", qw( first last title );
 my %hash = ();
@@ -53,6 +60,18 @@ my $home_phone_sub = sub {
   join(', ', map $_->phonenum, @contact_homephone);
 };
 
+my $invoice_dest_sub = sub {
+  my $contact = shift;
+  my $cust_contact = qsearchs(cust_contact => {custnum => $contact->custnum});
+  $cust_contact->invoice_dest ? 'Y' : 'N';
+};
+
+my $message_dest_sub = sub {
+  my $contact = shift;
+  my $cust_contact = qsearchs(cust_contact => {custnum => $contact->custnum});
+  $cust_contact->message_dest ? 'Y' : 'N';
+};
+
 my $link; #for closure in this sub, we'll define it later
 my $contact_classname_sub = sub {
   my $contact = shift;
@@ -63,14 +82,14 @@ my $contact_classname_sub = sub {
   } elsif ( $link eq 'prospect_main' ) {
     $X_contact = qsearchs('prospect_contact', { %hash, 'prospectnum' => $contact->prospectnum } );
   } else {
-    die 'guru meditation #5555';
+    die "guru meditation #5555 (\$link: $link)";
   }
   $X_contact->contact_classname;
 };
 
-my @header = ( 'First', 'Last', 'Title', 'Email', 'Work Phone', 'Mobile Phone', 'Home Phone', 'Type' );
-my @fields = ( 'first', 'last', 'title', $email_sub, $work_phone_sub, $mobile_phone_sub, $home_phone_sub, $contact_classname_sub );
-my @links = ( '', '', '', '', '', '', '', '', );
+my @header = ( 'First', 'Last', 'Title', 'Email', 'Work Phone', 'Mobile Phone', 'Home Phone', 'Type', 'Invoice Destination', 'Message Destination');
+my @fields = ( 'first', 'last', 'title', $email_sub, $work_phone_sub, $mobile_phone_sub, $home_phone_sub, $contact_classname_sub, $invoice_dest_sub, $message_dest_sub );
+my @links = ( '', '', '', '', '', '', '', '', '', '');
 
 my $company_link = '';
 
@@ -93,6 +112,14 @@ if ( $link ) {
       ' LEFT JOIN cust_contact USING ( contactnum ) '.
       ' LEFT JOIN cust_main ON ( cust_contact.custnum = cust_main.custnum )';
     $extra_sql = ' cust_contact.custnum IS NOT NULL ';
+    if (@classnum || $classnum_null) {
+      $extra_sql .= ' AND ( ';
+      $extra_sql .= ' cust_contact.classnum IN ('.join(',',@classnum).') '
+        if @classnum;
+      $extra_sql .= ' OR ' if $classnum_null && @classnum;
+      $extra_sql .= ' cust_contact.classnum IS NULL ' if $classnum_null;
+      $extra_sql .= ' ) ';
+    }
     $company_link  = [ $p.'view/cust_main.cgi?', 'custnum' ];
   } elsif ( $link eq 'prospect_main' ) {
     push @header, 'Prospect';
@@ -103,6 +130,14 @@ if ( $link ) {
       ' LEFT JOIN prospect_contact USING ( contactnum ) '.
       ' LEFT JOIN prospect_main ON ( prospect_contact.prospectnum = prospect_main.prospectnum )';
     $extra_sql = ' prospect_contact.prospectnum IS NOT NULL ';
+    if (@classnum || $classnum_null) {
+      $extra_sql .= ' AND ( ';
+      $extra_sql .= ' prospect_contact.classnum IN ('.join(',',@classnum).') '
+        if @classnum;
+      $extra_sql .= ' OR ' if $classnum_null && @classnum;
+      $extra_sql .= ' prospect_contact.classnum IS NULL ' if $classnum_null;
+      $extra_sql .= ' ) ';
+    }
     $company_link  = [ $p.'view/prospect_main.html?', 'prospectnum' ];
   } else {
     die "don't know how to report on contacts linked to specified table";
@@ -123,4 +158,21 @@ push @fields, 'comment';
 $extra_sql = (keys(%hash) ? ' AND ' : ' WHERE '). $extra_sql
  if $extra_sql;
 
+my $classnum_url_part;
+if (@classnum) {
+  $classnum_url_part = join '', map{ "&classnums=$_" } @classnum;
+  $classnum_url_part .= '&classnums=0' if $classnum_null;
+}
+my $send_email_link =
+  "<a href=\"${fsurl}misc/email-customers.html?".
+    'table=cust_main'.
+    '&POST=on'.
+    '&all_pkg_classnums=0'.
+    '&all_tags=0'.
+    '&any_pkg_status=0'.
+    '&refnum=1'.
+    '&with_email=on'.
+    $classnum_url_part.
+  "\">Email a notice to these customers</a>";
+
 </%init>
diff --git a/httemplate/search/report_contact.html b/httemplate/search/report_contact.html
index 3583bb4..ba91b4e 100644
--- a/httemplate/search/report_contact.html
+++ b/httemplate/search/report_contact.html
@@ -21,6 +21,11 @@
        'curr_value' => scalar( $cgi->param('link') ),
   &>
 
+  <& /elements/tr-select-multiple-contact_class.html,
+    label => 'Contact Type',
+    field => 'classnum',
+  &>
+
 </FORM>
 
 </TABLE>