RT# 73422 Fix report Customer Contacts

diff --git a/httemplate/elements/tr-checkbox-multiple.html b/httemplate/elements/tr-checkbox-multiple.html
index 4d754b0..baf18f9 100644 (file)
--- a/httemplate/elements/tr-checkbox-multiple.html
+++ b/httemplate/elements/tr-checkbox-multiple.html
@@ -1,3 +1,23 @@
+<%doc>
+
+Display a <tr> containing multiple checkboxes
+
+USAGE:
+
+<& /elements/tr-checkbox-multipe.html,
+  label => emt('Label'),
+  field => 'field_name',
+  options => ['opt1', 'opt2'],
+  labels => {
+    opt1 => 'Option 1',
+    opt2 => 'Option 2',
+  },
+  value => {
+    opt2 => '1', # opt2 defaults as checked
+  }
+&>
+
+</%doc>

 <% include('tr-td-label.html', @_ ) %>
 
   <TD <% $style %>>
 <% include('tr-td-label.html', @_ ) %>
 
   <TD <% $style %>>

diff --git a/httemplate/misc/email-customers.html b/httemplate/misc/email-customers.html
index 4520c75..f52c6b3 100644 (file)
--- a/httemplate/misc/email-customers.html
+++ b/httemplate/misc/email-customers.html
@@ -180,7 +180,7 @@ Template:
          # Called for each checkbox
          # Return true to default as checked, false as unchecked
          my($cgi, $name) = @_;
          # Called for each checkbox
          # Return true to default as checked, false as unchecked
          my($cgi, $name) = @_;

-         $name eq 'message'
+         exists $dest_ischecked{$name};

        },
      &>
    </div>
        },
      &>
    </div>


@@ -466,7 +466,9 @@ if ( !$cgi->param('preview') ) {
           push @contact_classname, 'Message recipients';
         } else {
           my $contact_class = FS::contact_class->by_key($1);
           push @contact_classname, 'Message recipients';
         } else {
           my $contact_class = FS::contact_class->by_key($1);

-          push @contact_classname, encode_entities($contact_class->classname);
+          push @contact_classname, encode_entities(
+            $contact_class ? $contact_class->classname : '(none)'
+          );

         }
       }
     }
         }
       }
     }


@@ -482,19 +484,30 @@ if ( !$cgi->param('preview') ) {
 
 my @active_classes = qsearch(contact_class => {disabled => ''} );
 
 
 my @active_classes = qsearch(contact_class => {disabled => ''} );
 

+my %classnum_ischecked;
+my %dest_ischecked;
+

 $CGI::LIST_CONTEXT_WARN = 0;
 $CGI::LIST_CONTEXT_WARN = 0;

-my @classnums = grep{ /^\d+$/ } $cgi->param('classnums');
+if ( my @in_classnums = $cgi->param('classnums') ) {
+  # Set checked boxes from form input
+  for my $v (@in_classnums) {
+
+    if ( $v =~ /^\d+$/ ) {
+      $classnum_ischecked{$v} = 1
+    } elsif ( $v =~ /^(invoice|message)$/ ) {
+      $dest_ischecked{$v} = 1;
+    }

 
 

-my %classnum_ischecked;
-if (@classnums) {
-  # values passed to form
-  $classnum_ischecked{$_} = 1 for @classnums;
+  }

 } else {
 } else {

-  # default values
+  # Checked boxes default values

   $classnum_ischecked{$_->classnum} = 1 for @active_classes;
   $classnum_ischecked{0} = 1;
 }
 
   $classnum_ischecked{$_->classnum} = 1 for @active_classes;
   $classnum_ischecked{0} = 1;
 }
 

+# At least one destination is required
+$dest_ischecked{message} = 1 unless %dest_ischecked;
+

 my @optin_checkboxes = (
   [ 'message' => { label => 'Message recipients' } ],
   [ 'invoice' => { label => 'Invoice recipients' } ],
 my @optin_checkboxes = (
   [ 'message' => { label => 'Message recipients' } ],
   [ 'invoice' => { label => 'Invoice recipients' } ],

diff --git a/httemplate/search/contact.html b/httemplate/search/contact.html
index 9abbcfa..e028333 100644 (file)
--- a/httemplate/search/contact.html
+++ b/httemplate/search/contact.html
@@ -1,178 +1,272 @@
 <& elements/search.html,
 <& elements/search.html,

-  title         => 'Contacts',
+  title         => emt('Contacts'),

   name_singular => 'contact',
   name_singular => 'contact',

-  query         => { select    => join(', ', @select),
-                     table     => 'contact',
-                     addl_from => $addl_from,
-                     hashref   => \%hash,
-                     extra_sql => $extra_sql,
-                   },
-  count_query   => "SELECT COUNT(*) FROM contact $addl_from $extra_sql", #XXX
-  header        => \@header,
-  fields        => \@fields,
-  links         => \@links,
-  html_init     => $send_email_link,
+  query         => {
+    select    => join(', ', @select),
+    table     => 'contact',
+    addl_from => $addl_from,
+    hashref   => {}, #\%hash,
+    extra_sql => "WHERE $extra_sql",
+    order_by  => "ORDER BY contact_last,contact_first,contact_email_emailaddress"
+  },
+  count_query => "
+    SELECT COUNT(*)
+    FROM contact
+    $addl_from
+    WHERE $extra_sql
+  ",
+  header    => \@header,
+  fields    => \@fields,
+  links     => \@links,
+  html_init => $send_email_link,
+#  agent_virt    => 1, # Not supported unless table is cust_main/prospect_main

 &>
 <%init>
 
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List contacts');
 
 &>
 <%init>
 
 die "access denied"
   unless $FS::CurrentUser::CurrentUser->access_right('List contacts');
 

+my $DEBUG = 0;
+

 # 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');
 
 # 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 = ();
-my $addl_from = '';
+# 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;

 
 

-my $email_sub = sub {
-  my $contact = shift;
-  #can't because contactnum is in the wrong field #my @contact_email = $contact->contact_email;
-  my @contact_email = qsearch('contact_email', { 'contactnum' => $contact->contact_contactnum } );
-  join(', ', map $_->emailaddress, @contact_email);
-};

 
 

-my $work_phone_sub = sub {
-  my $contact = shift;
-  my $phone_type = qsearchs('phone_type', { 'typename' => 'Work' });
-  #can't because contactnum is in the wrong field
-  my @contact_workphone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } );
-  join(', ', map $_->phonenum, @contact_workphone);
-};
+# Cache the contact_class table
+my %classname =
+  map {$_->classnum => $_->classname}
+  qsearch(contact_class => {disabled => ''});

 
 

-my $mobile_phone_sub = sub {
-  my $contact = shift;
-  my $phone_type = qsearchs('phone_type', { 'typename' => 'Mobile' });
-  #can't because contactnum is in the wrong field
-  my @contact_mobilephone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } );
-  join(', ', map $_->phonenum, @contact_mobilephone);
-};
+# This data structure is used to generate the sql query parameters
+my %colmap = (
+  # These are included regardless of which tables we're viewing
+  common => {
+    cols => {
+      contact => [qw/first last title contactnum/],
+      contact_email => [qw/emailaddress/],
+    },
+    joinsql => "
+      LEFT JOIN contact_email
+        ON (contact.contactnum = contact_email.contactnum)
+    ",
+  },

 
 

-my $home_phone_sub = sub {
-  my $contact = shift;
-  my $phone_type = qsearchs('phone_type', { 'typename' => 'Home' });
-  #can't because contactnum is in the wrong field
-  my @contact_homephone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } );
-  join(', ', map $_->phonenum, @contact_homephone);
-};
+  # These are included if we're viewing customer records
+  customer => {
+    cols => {
+      cust_main => [qw/first last company/],
+      cust_contact => [qw/
+        custnum classnum invoice_dest message_dest selfservice_access comment
+      /],
+    },
+    joinsql => "
+      LEFT JOIN cust_contact
+        ON (contact.contactnum = cust_contact.contactnum)
+      LEFT JOIN cust_main
+        ON (cust_contact.custnum = cust_main.custnum)
+    ",
+  },

 
 

-my $invoice_dest_sub = sub {
-  my $contact = shift;
-  my $cust_contact = qsearchs(cust_contact => {custnum => $contact->custnum});
-  $cust_contact->invoice_dest ? 'Y' : 'N';
-};
+  # These are included if we're viewing prospect records
+  prospect => {
+    cols => {
+      prospect_main => [qw/company/],
+      prospect_contact => [qw/prospectnum classnum comment/],
+    },
+    joinsql => "
+      LEFT JOIN prospect_contact
+        ON (contact.contactnum = prospect_contact.contactnum)
+      LEFT JOIN prospect_main
+        ON (prospect_contact.prospectnum = prospect_main.prospectnum)
+    ",
+  },
+);

 
 

-my $message_dest_sub = sub {
-  my $contact = shift;
-  my $cust_contact = qsearchs(cust_contact => {custnum => $contact->custnum});
-  $cust_contact->message_dest ? 'Y' : 'N';
-};
+my @select;
+my $addl_from;
+my $extra_sql;
+my $hashref;
+my $link = $cgi->param('link'); # cust_main, prospect_main or both
+
+my @rectypes = ('common');
+push @rectypes,'customer' if $link eq 'cust_main'     || $link eq 'both';
+push @rectypes,'prospect' if $link eq 'prospect_main' || $link eq 'both';

 
 

-my $link; #for closure in this sub, we'll define it later
-my $contact_classname_sub = sub {
-  my $contact = shift;
-  my %hash = ( 'contactnum' => $contact->contact_contactnum );
-  my $X_contact;
-  if ( $link eq 'cust_main' ) {
-    $X_contact = qsearchs('cust_contact', { %hash, 'custnum' => $contact->custnum } );
-  } elsif ( $link eq 'prospect_main' ) {
-    $X_contact = qsearchs('prospect_contact', { %hash, 'prospectnum' => $contact->prospectnum } );
-  } else {
-    die "guru meditation #5555 (\$link: $link)";
+# Build @select and $addl_from
+for my $key (@rectypes) {
+  $addl_from .= $colmap{$key}->{joinsql};
+  my $cols = $colmap{$key}->{cols};
+  for my $tbl (keys %{$cols}) {
+    push @select, map{ "$tbl.$_ AS ${tbl}_$_" } @{$cols->{$tbl}};

   }
   }

-  $X_contact->contact_classname;
-};
+}

 
 

-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 = ( '', '', '', '', '', '', '', '', '', '');
+# Filter for custnum/prospectnum
+$extra_sql .= ' (';
+$extra_sql .= "cust_contact.custnum IS NOT NULL"
+  if $link eq 'cust_main' || $link eq 'both';
+$extra_sql .= " OR " if $link eq 'both';
+$extra_sql .= "prospect_contact.prospectnum IS NOT NULL"
+  if $link eq 'prospect_main' || $link eq 'both';
+$extra_sql .= ') ';

 
 

-my $company_link = '';
+# Filter for Contact Type
+if (@classnum || $classnum_null) {
+  my @stm;

 
 

-if ( $cgi->param('selfservice_access') eq 'Y' ) {
-  $hash{'selfservice_access'} = 'Y';
-}
+  push @stm, 'cust_contact.classnum IN ('.join(',',@classnum).')'
+    if @classnum && ($link eq 'cust_main' || $link eq 'both');

 
 

-my $extra_sql = '';
-$link = $cgi->param('link');
-if ( $link ) {
-
-  my $as       = ') AS prospect_or_customer';
-
-  if ( $link eq 'cust_main' ) {
-    push @header, 'Customer';
-    push @select,
-       "COALESCE( cust_main.company, cust_main.first||' '||cust_main.last $as",
-       map "cust_contact.$_", qw( custnum classnum comment selfservice_access );
-    $addl_from =
-      ' 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';
-    push @select,
-      "COALESCE( prospect_main.company, contact.first||'  '||contact.last $as",
-      map "prospect_contact.$_", qw( prospectnum classnum comment );
-    $addl_from =
-      ' 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";
-  }
+  push @stm, 'prospect_contact.classnum IN ('.join(',',@classnum).')'
+    if @classnum && ($link eq 'prospect_main' || $link eq 'both');
+
+  push @stm, 'cust_contact.classnum IS NULL'
+    if $classnum_null && ($link eq 'cust_main' || $link eq 'both');
+
+  push @stm, 'prospect_contact.classnum IS NULL'
+    if $classnum_null && ($link eq 'prospect_main' || $link eq 'both');

 
 

-  #because right now its harder to show it for both kinds of contacts
-  push @fields, 'prospect_or_customer';
-  push @links, $company_link; 
+  $extra_sql .= "\nAND (" . join(' OR ',@stm) . ') ';
+}

 
 

+# Filter for destination
+if (@dest && ($link eq 'cust_main' || $link eq 'both')) {
+  my @stm;
+  push @stm, "cust_contact.${_}_dest IS NOT NULL" for @dest;
+  push @stm, "prospect_contact.prospectnum IS NOT NULL" if $link eq 'both';
+  $extra_sql .= "\nAND (".join(' OR ',@stm).') ';

 }
 
 }
 

-push @header, 'Self-service';
-push @fields, 'selfservice_access';
+if ($DEBUG) {
+  print "<pre>\n";
+  print "select \n";
+  print join ",\n",@select;
+  print "\n";
+  print "from contact \n";
+  print "$addl_from\n";
+  print "WHERE \n $extra_sql\n";
+  print "</pre>\n";
+}

 
 

-push @header, 'Comment';
-push @fields, 'comment';
+# Prepare to display phone numbers
+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}
+    });
+    @p ? (join ', ',map{$_->phonenum} @p) : undef;
+  };
+};

 
 

-$extra_sql = (keys(%hash) ? ' AND ' : ' WHERE '). $extra_sql
- if $extra_sql;
+# Cache contact types
+my %classname =
+  map {$_->classnum => $_->classname}
+  qsearch(contact_class => {disabled => ''});
+
+# And now for something completly different:
+my @report = (
+  { label => 'First', field => sub { shift->contact_first }},
+  { label => 'Last', field => sub { shift->contact_last }},
+  { label => 'Title', field => sub { shift->contact_title }},
+  { label => 'E-Mail', field => sub { shift->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') },
+  { label => 'Type',
+    field => sub {
+      my $rec = shift;
+      if ($rec->cust_contact_custnum) {
+        return $rec->cust_contact_classnum
+               ? $classname{$rec->cust_contact_classnum}
+               : undef;
+      } else {
+        return $rec->prospect_contact_classnum
+               ? $classname{$rec->prospect_contact_classnum}
+               : undef;
+      }
+  }},
+  { label => 'Send Invoices',
+    field => sub {
+      my $rec = shift;
+      return 'N/A' if $rec->prospect_contact_prospectnum;
+      $rec->cust_contact_invoice_dest ? 'Y' : 'N';
+    }},
+  { label => 'Send Messages',
+    field => sub {
+      my $rec = shift;
+      return 'N/A' if $rec->prospect_contact_prospectnum;
+      $rec->cust_contact_message_dest ? 'Y' : 'N';
+    }},
+  { label => 'Customer',
+    link  => sub {
+      my $rec = shift;
+      $rec->prospect_contact_prospectnum
+      ? ["${p}view/prospect_main.html?", 'prospect_contact_prospectnum' ]
+      : ["${p}view/cust_main.cgi?", 'cust_contact_custnum' ];
+    },
+    field => sub {
+      my $rec = shift;
+      if ($rec->prospect_contact_prospectnum) {
+        return $rec->contact_company
+          || $rec->contact_last.' '.$rec->contact_first;
+      }
+      $rec->cust_main_company || $rec->cust_main_last.' '.$rec->cust_main_first;
+    }},
+  { label => 'Self-service',
+    field => sub {
+      my $rec = shift;
+      return 'N/A' if $rec->prospect_contact_prospectnum;
+      $rec->cust_contact_selfservice_access ? 'Y' : 'N';
+    }},
+  { label => 'Comment',
+    field => sub {
+      my $rec = shift;
+      $rec->prospect_contact_prospectnum
+      ? $rec->prospect_contact_comment
+      : $rec->cust_contact_comment;
+    }},
+);
+
+my (@header, @fields, @links);
+for my $col (@report) {
+  push @header, emt($col->{label});
+  push @fields, $col->{field};
+  push @links, ($col->{link} || "");
+}

 
 my $classnum_url_part;
 if (@classnum) {
 
 my $classnum_url_part;
 if (@classnum) {

-  $classnum_url_part = join '', map{ "&classnums=$_" } @classnum;
+  $classnum_url_part = join '', map{ "&classnums=$_" } @classnum, @dest;

   $classnum_url_part .= '&classnums=0' if $classnum_null;
 }
   $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>";
+
+# E-mail pipeline doesn't support mixing prospects and customers in one go
+my $send_email_link = undef;
+if ($link eq 'cust_main') {
+  $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>
 
 </%init>

diff --git a/httemplate/search/report_contact.html b/httemplate/search/report_contact.html
index ba91b4e..309f11e 100644 (file)
--- a/httemplate/search/report_contact.html
+++ b/httemplate/search/report_contact.html
@@ -4,25 +4,39 @@
 
 <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
 
 
 <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
 

-  <& /elements/tr-select-agent.html,
-                 'curr_value'    => scalar( $cgi->param('agentnum') ),
-                 'label'         => emt('Contacts for agent: '),
-                 'disable_empty' => 0,
-  &>
+%#  This has never been actually supported on this report.
+%#  Remove the selectbox until support is implemented
+%#
+%#  <& /elements/tr-select-agent.html,
+%#                 'curr_value'    => scalar( $cgi->param('agentnum') ),
+%#                 'label'         => emt('Contacts for agent: '),
+%#                 'disable_empty' => 0,
+%#  &>

 
   <& /elements/tr-select.html,
 
   <& /elements/tr-select.html,

-       'label'      => 'Contact source', #??? not "type" - contacts have a type
+       'label'      => 'Contact source:', #??? not "type" - contacts have a type

        'field'      => 'link',
        'field'      => 'link',

-       'options'    => [ 'prospect_main', 'cust_main', '' ],
+       'options'    => [ 'prospect_main', 'cust_main', 'both' ],

        'labels'     => { 'prospect_main' => 'Prospect contacts',
                          'cust_main'     => 'Customer contacts',
        'labels'     => { 'prospect_main' => 'Prospect contacts',
                          'cust_main'     => 'Customer contacts',

-                         ''              => 'All contacts',
+                         'both'          => 'All contacts',

                        },
        'curr_value' => scalar( $cgi->param('link') ),
   &>
 
                        },
        'curr_value' => scalar( $cgi->param('link') ),
   &>
 

+  <& /elements/tr-checkbox-multiple.html,
+    label => emt('Destinations').':',
+    field => 'dest',
+    options => [ 'message', 'invoice' ],
+    labels => {
+      invoice => 'Invoice recipients',
+      message => 'Message recipients',
+    },
+    value => { message => 1 },
+  &>
+

   <& /elements/tr-select-multiple-contact_class.html,
   <& /elements/tr-select-multiple-contact_class.html,

-    label => 'Contact Type',
+    label => emt('Contact Type').':',

     field => 'classnum',
   &>
 
     field => 'classnum',
   &>