<& elements/search.html, title => emt('Contacts'), name_singular => 'contact', query => { select => join(', ', @select), table => $link, addl_from => $addl_from, hashref => {}, extra_sql => "WHERE $extra_sql", order_by => "ORDER BY contact_last,contact_first,contact_email_emailaddress" }, count_query => " SELECT COUNT(*) FROM $link $addl_from WHERE $extra_sql ", header => \@header, fields => \@fields, links => \@links, html_init => $send_email_link, agent_virt => 1, agent_pos => 11, &> <%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 destination values from dest multi-checkbox, default to message # irrelevant to prospect contacts my @dest = grep{ /^(message|invoice)$/ } $cgi->param('dest'); # Cache the contact_class table my %classname = map {$_->classnum => $_->classname} qsearch(contact_class => {disabled => ''}); # 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 => "", }, # These are included if we're viewing customer records cust_main => { 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 (cust_main.custnum = cust_contact.custnum) LEFT JOIN contact on (cust_contact.contactnum = contact.contactnum) LEFT JOIN contact_email ON (cust_contact.contactnum = contact_email.contactnum) ", }, # These are included if we're viewing prospect records prospect_main => { cols => { prospect_main => [qw/company/], prospect_contact => [qw/prospectnum classnum comment/], }, joinsql => " LEFT JOIN prospect_contact ON (prospect_main.prospectnum = prospect_contact.prospectnum) LEFT JOIN contact on (prospect_contact.contactnum = contact.contactnum) LEFT JOIN contact_email ON (prospect_contact.contactnum = contact_email.contactnum) ", }, ); my @select; my $addl_from; my $extra_sql; my $hashref; my $link = $cgi->param('link'); # cust_main or prospect_main push @select,'agentnum'; # this shouldn't happen without funny-busines die "Invalid \$link type ($link)" unless $link eq 'cust_main' || $link eq 'prospect_main'; # Build @select and $addl_from for my $key ('common', $link) { $addl_from .= $colmap{$key}->{joinsql}; my $cols = $colmap{$key}->{cols}; for my $tbl (keys %{$cols}) { push @select, map{ "$tbl.$_ AS ${tbl}_$_" } @{$cols->{$tbl}}; } } # Filter for Contact Type if (@classnum || $classnum_null) { my @stm; my $tbl = $link eq 'cust_main' ? 'cust_contact' : 'prospect_contact'; push @stm, "${tbl}.classnum IN (".join(',',@classnum).')' if @classnum; push @stm, "${tbl}.classnum IS NULL" if $classnum_null; $extra_sql .= " (" . join(' OR ',@stm) . ') '; } # Filter for destination if (@dest && $link eq 'cust_main') { my @stm; push @stm, "cust_contact.${_}_dest IS NOT NULL" for @dest; $extra_sql .= "\nAND (".join(' AND ',@stm).') '; } if ($DEBUG) { print "
\n";
  print "select \n";
  print join ",\n",@select;
  print "\n";
  print "from $link \n";
  print "$addl_from\n";
  print "WHERE \n $extra_sql\n";
  print "
\n"; } # Prepare to display phone numbers # adds 3 additional queries per table record :-( my $get_phone_sub = sub { my $type = shift; return sub { my $rec = shift; my @p = qsearch('contact_phone', { contactnum => $rec->contact_contactnum, phonetypenum => $type, }); @p ? (join ', ',map{$_->phonenum_pretty} @p) : undef; }; }; my @phones; foreach my $phone_type ( FS::phone_type->get_phone_types() ) { push @phones, { label => $phone_type->typename.' Phone', field => $get_phone_sub->($phone_type->phonetypenum), }; } # Cache contact types my %classname = map {$_->classnum => $_->classname} qsearch(contact_class => {disabled => ''}); # And now for something completly different: my @report = ( { label => 'First', field => 'contact_first' }, { label => 'Last', field => 'contact_last' }, { label => 'Title', field => 'contact_title' }, { label => 'E-Mail', field => 'contact_email_emailaddress' }, @phones, { 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 => [ "${fsurl}view/", sub { my $row = shift; $row->cust_contact_custnum ? 'cust_main.cgi?'.$row->cust_contact_custnum : 'prospect_main.html?'.$row->prospect_contact_prospectnum } ], field => sub { my $rec = shift; if ($rec->prospect_contact_prospectnum) { return encode_entities( $rec->contact_company || $rec->contact_last.' '.$rec->contact_first ); } encode_entities( $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; encode_entities( $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 .= '&classnums=0' if $classnum_null; } my $dest_url_part; if (@dest) { $dest_url_part = join '', map{ "&dest=$_" } @dest; } # E-mail pipeline, from email-customers.html through to email queue job, # doesn't support cust_prospect table my $send_email_link = undef; if ($link eq 'cust_main') { $send_email_link = "Email a notice to these customers"; }