<& elements/search.html,
title => emt('Contacts'),
name_singular => 'contact',
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');
# 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;
# 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 => "
LEFT JOIN contact_email
ON (contact.contactnum = contact_email.contactnum)
",
},
# 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)
",
},
# 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 @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';
# 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}};
}
}
# 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 .= ') ';
# Filter for Contact Type
if (@classnum || $classnum_null) {
my @stm;
push @stm, 'cust_contact.classnum IN ('.join(',',@classnum).')'
if @classnum && ($link eq 'cust_main' || $link eq 'both');
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');
$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).') ';
}
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";
}
# 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;
};
};
# 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, @dest;
$classnum_url_part .= '&classnums=0' if $classnum_null;
}
# 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";
}