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 #can't because contactnum is in the wrong field
50 my @contact_workphone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => '1' } );
51 join(', ', map $_->phonenum, @contact_workphone);
54 my $mobile_phone_sub = sub {
56 #can't because contactnum is in the wrong field
57 my @contact_mobilephone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => '3' } );
58 join(', ', map $_->phonenum, @contact_mobilephone);
61 my $home_phone_sub = sub {
63 #can't because contactnum is in the wrong field
64 my @contact_homephone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => '2' } );
65 join(', ', map $_->phonenum, @contact_homephone);
68 my $link; #for closure in this sub, we'll define it later
69 my $contact_classname_sub = sub {
71 my %hash = ( 'contactnum' => $contact->contact_contactnum );
73 if ( $link eq 'cust_main' ) {
74 $X_contact = qsearchs('cust_contact', { %hash, 'custnum' => $contact->custnum } );
75 } elsif ( $link eq 'prospect_main' ) {
76 $X_contact = qsearchs('prospect_contact', { %hash, 'prospectnum' => $contact->prospectnum } );
78 die 'guru meditation #5555';
80 $X_contact->contact_classname;
83 my @header = ( 'First', 'Last', 'Title', 'Email', 'Work Phone', 'Mobile Phone', 'Home Phone', 'Type' );
84 my @fields = ( 'first', 'last', 'title', $email_sub, $work_phone_sub, $mobile_phone_sub, $home_phone_sub, $contact_classname_sub );
85 my @links = ( '', '', '', '', '', '', '', '', );
90 select => 'contact.first',
97 select => 'contact.last',
104 select => 'contact.title',
111 select => 'contact_email.emailaddress',
112 fields => 'emailaddress',
117 { # Column: Work Phone
119 ( SELECT contact_phone.phonenum
121 WHERE contact.contactnum = contact_phone.contactnum
125 fields => sub { $format_phone_sub->( shift->work_phone ) },
126 header => 'Work Phone',
130 { # Column: Mobile Phone
132 ( SELECT contact_phone.phonenum
134 WHERE contact.contactnum = contact_phone.contactnum
138 fields => sub { $format_phone_sub->( shift->mobile_phone ) },
139 header => 'Mobile Phone',
144 # ( skipped, contact edit screen does not include this )
146 { # Column: Contact Type (contact_class)
147 select => 'contact_class.classname',
148 fields => 'classname',
153 { # Column: Send invoices
154 select => 'cust_main.invoice_noemail',
156 # Prospects cannot opt out (not implemented)
157 # Contacts cannot opt out, but the attached cust_main records can.
158 # Therefore, always YES unless cust_main record is opt-out
160 return 'No' if $row->invoice_noemail && $row->invoice_noemail eq 'Y';
163 header => 'Receive Invoices',
167 { # Column: Send messages
168 select => 'cust_main.message_noemail',
170 # Same as invoice_noemail, see above
172 return 'No' if $row->message_noemail && $row->message_noemail eq 'Y';
175 header => 'Receive Messages',
180 # The first of these with a value will be displayed:
181 # 1) cust_main.company
182 # 2) cust_main.first + cust_main.last
183 # 3) prospect_main.company
184 # 4) contact.first + contact.last
187 prospect_main.prospectnum,
190 prospect_main.company,
191 cust_main.first||' '||cust_main.last,
192 contact.first||' '||contact.last
195 fields => 'customer_name',
196 header => 'Customer',
202 ? 'cust_main.cgi?'.$row->custnum
203 : 'prospect_main.html?'.$row->prospectnum
209 # Inserted by search.html (hopefully)
211 { # Column: Self-service
212 select => 'contact.selfservice_access',
213 fields => sub { shift->selfservice_access eq 'Y' ? 'Yes' : 'No' },
214 header => 'Self-Service',
219 select => 'contact.comment',
226 my $agentnum_coalesce = 'COALESCE( cust_main.agentnum, prospect_main.agentnum )';
229 if ( scalar $cgi->param('agentnum') =~ /^(\d+)$/ ) {
230 push @where, "$agentnum_coalesce = $1";
233 if ( my $contact_source = scalar $cgi->param('contact_source') ) {
234 my $col = $contact_source eq 'prospect_main'
235 ? 'prospect_main.prospectnum'
236 : 'cust_main.custnum';
237 push @where, "$col IS NOT NULL"
240 # SQL to filter classnums is only invoked if at least one classnum
241 # checkbox is selected
244 map{ /^contact_classnum_(null|\d+)$/ ? $1 : () }
248 for my $classnum ( @classnums ) {
249 push @where_classnum,
251 ? ' contact.classnum IS NULL '
252 : sprintf( ' contact.classnum = %s ', $dbh->quote( $classnum ));
257 join( ' OR ', @where_classnum )
262 my $select = join ', ', ( map{ $_->{select} } @report );
264 LEFT JOIN contact_email USING (contactnum)
265 LEFT JOIN cust_main USING (custnum)
266 LEFT JOIN prospect_main USING (prospectnum)
267 LEFT JOIN contact_class ON contact.classnum = contact_class.classnum
270 my @header = map{ $_->{header} } @report;
271 my @fields = map{ $_->{fields} } @report;
272 my @links = map{ $_->{links} } @report;
273 my $extra_sql = 'WHERE ( ' . join( ' AND ', @where ) . ' ) '