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 $format_phone_sub = sub {
49 my $pn = $_[0] || return '';
51 my @pn = split //, $pn;
55 join( '', @pn[0..2] ),
56 join( '', @pn[3..5] ),
63 join( '', @pn[1..3] ),
64 join( '', @pn[4..6] ),
65 join( '', @pn[7..10] )
66 ) if @pn == 11 && $pn[0] == 1;
68 encode_entities $_[0];
75 select => 'contact.first',
82 select => 'contact.last',
89 select => 'contact.title',
96 select => 'contact_email.emailaddress',
97 fields => 'emailaddress',
102 { # Column: Work Phone
104 ( SELECT contact_phone.phonenum
106 WHERE contact.contactnum = contact_phone.contactnum
110 fields => sub { $format_phone_sub->( shift->work_phone ) },
111 header => 'Work Phone',
115 { # Column: Mobile Phone
117 ( SELECT contact_phone.phonenum
119 WHERE contact.contactnum = contact_phone.contactnum
123 fields => sub { $format_phone_sub->( shift->mobile_phone ) },
124 header => 'Mobile Phone',
129 # ( skipped, contact edit screen does not include this )
131 { # Column: Contact Type (contact_class)
132 select => 'contact_class.classname',
133 fields => 'classname',
138 { # Column: Send invoices
139 select => 'cust_main.invoice_noemail',
141 # Prospects cannot opt out (not implemented)
142 # Contacts cannot opt out, but the attached cust_main records can.
143 # Therefore, always YES unless cust_main record is opt-out
145 return 'No' if $row->invoice_noemail && $row->invoice_noemail eq 'Y';
148 header => 'Receive Invoices',
152 { # Column: Send messages
153 select => 'cust_main.message_noemail',
155 # Same as invoice_noemail, see above
157 return 'No' if $row->message_noemail && $row->message_noemail eq 'Y';
160 header => 'Receive Messages',
165 # The first of these with a value will be displayed:
166 # 1) cust_main.company
167 # 2) cust_main.first + cust_main.last
168 # 3) prospect_main.company
169 # 4) contact.first + contact.last
172 prospect_main.prospectnum,
175 prospect_main.company,
176 cust_main.first||' '||cust_main.last,
177 contact.first||' '||contact.last
180 fields => 'customer_name',
181 header => 'Customer',
187 ? 'cust_main.cgi?'.$row->custnum
188 : 'prospect_main.html?'.$row->prospectnum
194 # Inserted by search.html (hopefully)
196 { # Column: Self-service
197 select => 'contact.selfservice_access',
198 fields => sub { shift->selfservice_access eq 'Y' ? 'Yes' : 'No' },
199 header => 'Self-Service',
204 select => 'contact.comment',
211 my $agentnum_coalesce = 'COALESCE( cust_main.agentnum, prospect_main.agentnum )';
214 if ( scalar $cgi->param('agentnum') =~ /^(\d+)$/ ) {
215 push @where, "$agentnum_coalesce = $1";
218 if ( my $contact_source = scalar $cgi->param('contact_source') ) {
219 my $col = $contact_source eq 'prospect_main'
220 ? 'prospect_main.prospectnum'
221 : 'cust_main.custnum';
222 push @where, "$col IS NOT NULL"
225 # SQL to filter classnums is only invoked if at least one classnum
226 # checkbox is selected
229 map{ /^contact_classnum_(null|\d+)$/ ? $1 : () }
233 for my $classnum ( @classnums ) {
234 push @where_classnum,
236 ? ' contact.classnum IS NULL '
237 : sprintf( ' contact.classnum = %s ', $dbh->quote( $classnum ));
242 join( ' OR ', @where_classnum )
247 my $select = join ', ', ( map{ $_->{select} } @report );
249 LEFT JOIN contact_email USING (contactnum)
250 LEFT JOIN cust_main USING (custnum)
251 LEFT JOIN prospect_main USING (prospectnum)
252 LEFT JOIN contact_class ON contact.classnum = contact_class.classnum
255 my @header = map{ $_->{header} } @report;
256 my @fields = map{ $_->{fields} } @report;
257 my @links = map{ $_->{links} } @report;
258 my $extra_sql = 'WHERE ( ' . join( ' AND ', @where ) . ' ) '