1 <& elements/search.html,
3 name_singular => 'contact',
8 addl_from => $addl_from,
10 extra_sql => $extra_sql,
12 count_query => "SELECT COUNT(*) FROM contact $addl_from $extra_sql",
19 agent_column => $agentnum_coalesce,
21 agent_null_right => 'View customers of all agents',
22 agent_null_right_link => 'View customer',
33 SELECT COUNT(*) FROM contact <% $addl_from %> <% $extra_sql %>
40 unless $FS::CurrentUser::CurrentUser->access_right('List contacts');
47 my $work_phone_sub = sub {
49 my $phone_type = qsearchs('phone_type', { 'typename' => 'Work' });
50 #can't because contactnum is in the wrong field
51 my @contact_workphone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } );
52 join(', ', map $_->phonenum, @contact_workphone);
55 my $mobile_phone_sub = sub {
57 my $phone_type = qsearchs('phone_type', { 'typename' => 'Mobile' });
58 #can't because contactnum is in the wrong field
59 my @contact_mobilephone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } );
60 join(', ', map $_->phonenum, @contact_mobilephone);
63 my $home_phone_sub = sub {
65 my $phone_type = qsearchs('phone_type', { 'typename' => 'Home' });
66 #can't because contactnum is in the wrong field
67 my @contact_homephone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } );
68 join(', ', map $_->phonenum, @contact_homephone);
71 my $link; #for closure in this sub, we'll define it later
72 my $contact_classname_sub = sub {
74 my %hash = ( 'contactnum' => $contact->contact_contactnum );
76 if ( $link eq 'cust_main' ) {
77 $X_contact = qsearchs('cust_contact', { %hash, 'custnum' => $contact->custnum } );
78 } elsif ( $link eq 'prospect_main' ) {
79 $X_contact = qsearchs('prospect_contact', { %hash, 'prospectnum' => $contact->prospectnum } );
81 die 'guru meditation #5555';
83 $X_contact->contact_classname;
86 my @header = ( 'First', 'Last', 'Title', 'Email', 'Work Phone', 'Mobile Phone', 'Home Phone', 'Type' );
87 my @fields = ( 'first', 'last', 'title', $email_sub, $work_phone_sub, $mobile_phone_sub, $home_phone_sub, $contact_classname_sub );
88 my @links = ( '', '', '', '', '', '', '', '', );
93 select => 'contact.first',
100 select => 'contact.last',
107 select => 'contact.title',
114 select => 'contact_email.emailaddress',
115 fields => 'emailaddress',
120 { # Column: Work Phone
122 ( SELECT contact_phone.phonenum
124 WHERE contact.contactnum = contact_phone.contactnum
128 fields => sub { $format_phone_sub->( shift->work_phone ) },
129 header => 'Work Phone',
133 { # Column: Mobile Phone
135 ( SELECT contact_phone.phonenum
137 WHERE contact.contactnum = contact_phone.contactnum
141 fields => sub { $format_phone_sub->( shift->mobile_phone ) },
142 header => 'Mobile Phone',
147 # ( skipped, contact edit screen does not include this )
149 { # Column: Contact Type (contact_class)
150 select => 'contact_class.classname',
151 fields => 'classname',
156 { # Column: Send invoices
157 select => 'cust_main.invoice_noemail',
159 # Prospects cannot opt out (not implemented)
160 # Contacts cannot opt out, but the attached cust_main records can.
161 # Therefore, always YES unless cust_main record is opt-out
163 return 'No' if $row->invoice_noemail && $row->invoice_noemail eq 'Y';
166 header => 'Receive Invoices',
170 { # Column: Send messages
171 select => 'cust_main.message_noemail',
173 # Same as invoice_noemail, see above
175 return 'No' if $row->message_noemail && $row->message_noemail eq 'Y';
178 header => 'Receive Messages',
183 # The first of these with a value will be displayed:
184 # 1) cust_main.company
185 # 2) cust_main.first + cust_main.last
186 # 3) prospect_main.company
187 # 4) contact.first + contact.last
190 prospect_main.prospectnum,
193 prospect_main.company,
194 cust_main.first||' '||cust_main.last,
195 contact.first||' '||contact.last
198 fields => 'customer_name',
199 header => 'Customer',
205 ? 'cust_main.cgi?'.$row->custnum
206 : 'prospect_main.html?'.$row->prospectnum
212 # Inserted by search.html (hopefully)
214 { # Column: Self-service
215 select => 'contact.selfservice_access',
216 fields => sub { shift->selfservice_access eq 'Y' ? 'Yes' : 'No' },
217 header => 'Self-Service',
222 select => 'contact.comment',
229 my $agentnum_coalesce = 'COALESCE( cust_main.agentnum, prospect_main.agentnum )';
232 if ( scalar $cgi->param('agentnum') =~ /^(\d+)$/ ) {
233 push @where, "$agentnum_coalesce = $1";
236 if ( my $contact_source = scalar $cgi->param('contact_source') ) {
237 my $col = $contact_source eq 'prospect_main'
238 ? 'prospect_main.prospectnum'
239 : 'cust_main.custnum';
240 push @where, "$col IS NOT NULL"
243 # SQL to filter classnums is only invoked if at least one classnum
244 # checkbox is selected
247 map{ /^contact_classnum_(null|\d+)$/ ? $1 : () }
251 for my $classnum ( @classnums ) {
252 push @where_classnum,
254 ? ' contact.classnum IS NULL '
255 : sprintf( ' contact.classnum = %s ', $dbh->quote( $classnum ));
260 join( ' OR ', @where_classnum )
265 my $select = join ', ', ( map{ $_->{select} } @report );
267 LEFT JOIN contact_email USING (contactnum)
268 LEFT JOIN cust_main USING (custnum)
269 LEFT JOIN prospect_main USING (prospectnum)
270 LEFT JOIN contact_class ON contact.classnum = contact_class.classnum
273 my @header = map{ $_->{header} } @report;
274 my @fields = map{ $_->{fields} } @report;
275 my @links = map{ $_->{links} } @report;
276 my $extra_sql = 'WHERE ( ' . join( ' AND ', @where ) . ' ) '