#cust_main phone numbers and contact phone number
push @cust_main, qsearch( {
- 'table' => 'cust_main',
- 'hashref' => { %options },
+ 'select' => 'cust_main.*',
+ 'table' => 'cust_main',
+ 'addl_from' => ' left join cust_contact using (custnum) '.
+ ' left join contact_phone using (contactnum) ',
+ 'hashref' => { %options },
'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
' ( '.
join(' OR ', map "$_ = '$phonen'",
" OR phonenum = '$phonenum' ".
' ) '.
" AND $agentnums_sql", #agent virtualization
- 'addl_from' => ' left join cust_contact using (custnum) left join contact_phone using (contactnum) ',
} );
unless ( @cust_main || $phonen =~ /x\d+$/ ) { #no exact match
#try looking for matches with extensions unless one was specified
push @cust_main, qsearch( {
- 'table' => 'cust_main',
- 'hashref' => { %options },
+ 'table' => 'cust_main',
+ 'hashref' => { %options },
'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
' ( '.
join(' OR ', map "$_ LIKE '$phonen\%'",
if ( $search =~ /@/ ) { #email address from cust_main_invoice and contact_email
push @cust_main, qsearch( {
- 'table' => 'cust_main',
- 'hashref' => { %options },
+ 'select' => 'cust_main.*',
+ 'table' => 'cust_main',
+ 'addl_from' => ' left join cust_main_invoice using (custnum) '.
+ ' left join cust_contact using (custnum) '.
+ ' left join contact_email using (contactnum) ',
+ 'hashref' => { %options },
'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ).
' ( '.
join(' OR ', map "$_ = '$search'",
).
' ) '.
" AND $agentnums_sql", #agent virtualization
- 'addl_from' => ' left join cust_main_invoice using (custnum) left join cust_contact using (custnum) left join contact_email using (contactnum) ',
} );
# custnum search (also try agent_custid), with some tweaking options if your
# probably the Right Thing: return customers that have any associated
# locations matching the string, not just bill/ship location
push @cust_main, qsearch( {
+ 'select' => 'cust_main.*',
'table' => 'cust_main',
'addl_from' => ' JOIN cust_location USING (custnum) ',
'hashref' => { %options, },
#doesn't throw a wrench in the works)
push @cust_main, qsearch( {
- 'table' => 'cust_main',
- 'hashref' => { %options },
- 'extra_sql' =>
+ 'table' => 'cust_main',
+ 'hashref' => { %options },
+ 'extra_sql' =>
( keys(%options) ? ' AND ' : ' WHERE ' ).
join(' AND ',
" LOWER(first) = ". dbh->quote(lc($first)),
" LOWER(company) = ". dbh->quote(lc($company)),
$agentnums_sql,
),
- } ),
+ } );
#contacts?
# probably not necessary for the "something a browser remembered" case
#cust_main and contacts
push @cust_main, qsearch( {
+ 'select' => 'cust_main.*',
'table' => 'cust_main',
- 'select' => 'cust_main.*, cust_contact.*, contact.contactnum, contact.last as contact_last, contact.first as contact_first, contact.title',
+ 'addl_from' => ' left join cust_contact using (custnum) '.
+ ' left join contact using (contactnum) ',
'hashref' => { %options },
'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization
- 'addl_from' => ' left join cust_contact on cust_main.custnum = cust_contact.custnum left join contact using (contactnum) ',
} );
# or it just be something that was typed in... (try that in a sec)
if $conf->exists('address1-search');
push @cust_main, qsearch( {
+ 'select' => 'cust_main.*',
'table' => 'cust_main',
- 'select' => 'cust_main.*, cust_contact.*, contact.contactnum, contact.last as contact_last, contact.first as contact_first, contact.title',
+ 'addl_from' => ' left join cust_contact using (custnum) '.
+ ' left join contact using (contactnum) ',
'hashref' => { %options },
'extra_sql' => "$sql AND $agentnums_sql", #agent virtualization
- 'addl_from' => 'left join cust_contact on cust_main.custnum = cust_contact.custnum left join contact using (contactnum) ',
} );
#no exact match, trying substring/fuzzy
my @extra_fields = ();
my @extra_sort_fields = ();
+ ## search contacts
+ if ($params->{'contacts'}) {
+ my $contact_params = $params->{'contacts'};
+
+ $addl_from .=
+ ' LEFT JOIN cust_contact ON ( cust_main.custnum = cust_contact.custnum ) ';
+
+ if ($contact_params->{'contacts_firstname'} || $contact_params->{'contacts_lastname'}) {
+ $addl_from .= ' LEFT JOIN contact ON ( cust_contact.contactnum = contact.contactnum ) ';
+ my $first_query = " AND contact.first = '" . $contact_params->{'contacts_firstname'} . "'"
+ unless !$contact_params->{'contacts_firstname'};
+ my $last_query = " AND contact.last = '" . $contact_params->{'contacts_lastname'} . "'"
+ unless !$contact_params->{'contacts_lastname'};
+ $extra_sql .= " AND ( '1' $first_query $last_query )";
+ }
+
+ if ($contact_params->{'contacts_email'}) {
+ $addl_from .= ' LEFT JOIN contact_email ON ( cust_contact.contactnum = contact_email.contactnum ) ';
+ $extra_sql .= " AND ( contact_email.emailaddress = '" . $contact_params->{'contacts_email'} . "' )";
+ }
+
+ if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) {
+ $addl_from .= ' LEFT JOIN contact_phone ON ( cust_contact.contactnum = contact_phone.contactnum ) ';
+ my $contacts_mobilephone;
+ foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) {
+ (my $num = $contact_params->{$phone}) =~ s/\W//g;
+ if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; }
+ }
+ my $home_query = " AND ( contact_phone.phonetypenum = '2' AND contact_phone.phonenum = '" . $contact_params->{'contacts_homephone'} . "' )"
+ unless !$contact_params->{'contacts_homephone'};
+ my $work_query = " AND ( contact_phone.phonetypenum = '1' AND contact_phone.phonenum = '" . $contact_params->{'contacts_workphone'} . "' )"
+ unless !$contact_params->{'contacts_workphone'};
+ my $mobile_query = " AND ( contact_phone.phonetypenum = '3' AND contact_phone.phonenum = '" . $contact_params->{'contacts_mobilephone'} . "' )"
+ unless !$contact_params->{'contacts_mobilephone'};
+ $extra_sql .= " AND ( '1' $home_query $work_query $mobile_query )";
+ }
+
+ }
+
if ($params->{'flattened_pkgs'}) {
#my $pkg_join = '';