From: Mitch Jackson Date: Sun, 4 Feb 2018 02:16:47 +0000 (-0600) Subject: RT# 73422 Fix report Customer Contacts X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=560af69840e7e3fcebf65d5d56ca4efc0851efec RT# 73422 Fix report Customer Contacts --- diff --git a/httemplate/elements/tr-checkbox-multiple.html b/httemplate/elements/tr-checkbox-multiple.html index 4d754b007..baf18f916 100644 --- a/httemplate/elements/tr-checkbox-multiple.html +++ b/httemplate/elements/tr-checkbox-multiple.html @@ -1,3 +1,23 @@ +<%doc> + +Display a 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 + } +&> + + <% include('tr-td-label.html', @_ ) %> > diff --git a/httemplate/misc/email-customers.html b/httemplate/misc/email-customers.html index 4520c7514..f52c6b36a 100644 --- 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) = @_; - $name eq 'message' + exists $dest_ischecked{$name}; }, &> @@ -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, 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 %classnum_ischecked; +my %dest_ischecked; + $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 { - # default values + # Checked boxes default values $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' } ], diff --git a/httemplate/search/contact.html b/httemplate/search/contact.html index 9abbcfa1d..e02833319 100644 --- a/httemplate/search/contact.html +++ b/httemplate/search/contact.html @@ -1,178 +1,272 @@ <& elements/search.html, - title => 'Contacts', + title => emt('Contacts'), 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'); +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'); -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 "
\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 "
\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) { - $classnum_url_part = join '', map{ "&classnums=$_" } @classnum; + $classnum_url_part = join '', map{ "&classnums=$_" } @classnum, @dest; $classnum_url_part .= '&classnums=0' if $classnum_null; } -my $send_email_link = - "Email a notice to these customers"; + +# 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 = + "Email a notice to these customers"; +} diff --git a/httemplate/search/report_contact.html b/httemplate/search/report_contact.html index ba91b4e7e..309f11e96 100644 --- a/httemplate/search/report_contact.html +++ b/httemplate/search/report_contact.html @@ -4,25 +4,39 @@ - <& /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, - 'label' => 'Contact source', #??? not "type" - contacts have a type + 'label' => 'Contact source:', #??? not "type" - contacts have a type 'field' => 'link', - 'options' => [ 'prospect_main', 'cust_main', '' ], + 'options' => [ 'prospect_main', 'cust_main', 'both' ], 'labels' => { 'prospect_main' => 'Prospect contacts', 'cust_main' => 'Customer contacts', - '' => 'All contacts', + 'both' => 'All contacts', }, '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, - label => 'Contact Type', + label => emt('Contact Type').':', field => 'classnum', &>