From e803d5f00368e951f7a4b82d5d390b53c4a6c827 Mon Sep 17 00:00:00 2001 From: Mitch Jackson Date: Tue, 30 Jan 2018 09:27:42 -0600 Subject: [PATCH] RT# 73422 Changes to report Customer Contacts --- FS/FS/cust_main.pm | 19 +++--- FS/FS/cust_main/Search.pm | 48 ++++++++++++-- .../elements/select-multiple-contact_class.html | 21 ++++++ .../elements/tr-select-multiple-contact_class.html | 32 +++++++++ httemplate/misc/email-customers.html | 75 +++++++++++++++++++--- httemplate/search/contact.html | 60 +++++++++++++++-- httemplate/search/report_contact.html | 5 ++ 7 files changed, 233 insertions(+), 27 deletions(-) create mode 100644 httemplate/elements/select-multiple-contact_class.html create mode 100644 httemplate/elements/tr-select-multiple-contact_class.html diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index a82d8a225..7c9868d7a 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 2ec87cd14..815304bb4 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, L =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 000000000..81a71cc25 --- /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) + + +<% 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 000000000..5de129324 --- /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. + + + +% if ($has_types) { + + <% $opt{'label'} || emt('Contact Type') %> + + <% include( '/elements/select-multiple-contact_class.html', %opt ) %> + + +% } else { + +% } + +<%init> + +my %opt = @_; +$opt{field} ||= $opt{element_name} ||= 'classnum'; + +my $has_types =()= qsearch({ + table => 'contact_class', + hashref => { disabled => '' }, + extra_sql => ' LIMIT 1 ', +}); + + diff --git a/httemplate/misc/email-customers.html b/httemplate/misc/email-customers.html index dc53f6d55..4520c7514 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: % } +% if (@active_classes) { + + +Contact Type: + +
+ <& /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}; + }, + &> +
+ +% }
@@ -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, +); diff --git a/httemplate/search/contact.html b/httemplate/search/contact.html index 5f02fef2f..9abbcfa1d 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 = + "Email a notice to these customers"; + diff --git a/httemplate/search/report_contact.html b/httemplate/search/report_contact.html index 3583bb428..ba91b4e7e 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', + &> + -- 2.11.0