diff options
| -rw-r--r-- | FS/FS/cust_main/Search.pm | 133 | ||||
| -rw-r--r-- | httemplate/elements/searchbar-cust_main.html | 2 | ||||
| -rw-r--r-- | httemplate/search/contact.html | 30 | ||||
| -rwxr-xr-x | httemplate/search/cust_main.html | 13 | ||||
| -rw-r--r-- | httemplate/search/elements/options_cust_contacts.html | 36 | ||||
| -rwxr-xr-x | httemplate/search/report_cust_main.html | 8 |
6 files changed, 153 insertions, 69 deletions
diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index 68471ec30..2ec87cd14 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -93,7 +93,7 @@ sub smart_search { my $phonenum = "$1$2$3"; #my $extension = $4; - #cust_main phone numbers + #cust_main phone numbers and contact phone number push @cust_main, qsearch( { 'table' => 'cust_main', 'hashref' => { %options }, @@ -102,20 +102,12 @@ sub smart_search { join(' OR ', map "$_ = '$phonen'", qw( daytime night mobile fax ) ). + " OR phonenum = '$phonenum' ". ' ) '. " AND $agentnums_sql", #agent virtualization + 'addl_from' => ' left join cust_contact using (custnum) left join contact_phone using (contactnum) ', } ); - #contact phone numbers - push @cust_main, - grep $agentnums_href->{$_->agentnum}, #agent virt - grep $_, #skip contacts that don't have cust_main records - map $_->contact->cust_main, - qsearch({ - 'table' => 'contact_phone', - 'hashref' => { 'phonenum' => $phonenum }, - }); - unless ( @cust_main || $phonen =~ /x\d+$/ ) { #no exact match #try looking for matches with extensions unless one was specified @@ -136,28 +128,20 @@ sub smart_search { } - if ( $search =~ /@/ ) { #email address - - # invoicing email address - push @cust_main, - grep $agentnums_href->{$_->agentnum}, #agent virt - map $_->cust_main, - qsearch( { - 'table' => 'cust_main_invoice', - 'hashref' => { 'dest' => $search }, - } - ); - - # contact email address - push @cust_main, - grep $agentnums_href->{$_->agentnum}, #agent virt - grep $_, #skip contacts that don't have cust_main records - map $_->contact->cust_main, - qsearch( { - 'table' => 'contact_email', - 'hashref' => { 'emailaddress' => $search }, - } - ); + if ( $search =~ /@/ ) { #email address from cust_main_invoice and contact_email + + push @cust_main, qsearch( { + 'table' => 'cust_main', + 'hashref' => { %options }, + 'extra_sql' => ( scalar(keys %options) ? ' AND ' : ' WHERE ' ). + ' ( '. + join(' OR ', map "$_ = '$search'", + qw( dest emailaddress ) + ). + ' ) '. + " 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 # legacy cust "numbers" have letters @@ -281,8 +265,8 @@ sub smart_search { } elsif ( ! $NameParse->parse($value) ) { my %name = $NameParse->components; - $first = $name{'given_name_1'} || $name{'initials_1'}; #wtf NameParse, Ed? - $last = $name{'surname_1'}; + $first = lc($name{'given_name_1'}) || $name{'initials_1'}; #wtf NameParse, Ed? + $last = lc($name{'surname_1'}); } @@ -292,28 +276,18 @@ sub smart_search { #exact my $sql = scalar(keys %options) ? ' AND ' : ' WHERE '; - $sql .= "( LOWER(cust_main.last) = $q_last AND LOWER(cust_main.first) = $q_first )"; + $sql .= "( (LOWER(cust_main.last) = $q_last AND LOWER(cust_main.first) = $q_first) + OR (LOWER(contact.last) = $q_last AND LOWER(contact.first) = $q_first) )"; - #cust_main + #cust_main and contacts push @cust_main, qsearch( { 'table' => 'cust_main', - 'hashref' => \%options, + 'select' => 'cust_main.*, cust_contact.*, contact.contactnum, contact.last as contact_last, contact.first as contact_first, contact.title', + '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) ', } ); - #contacts - push @cust_main, - grep $agentnums_href->{$_->agentnum}, #agent virt - grep $_, #skip contacts that don't have cust_main records - map $_->cust_main, - qsearch( { - 'table' => 'contact', - 'hashref' => { 'first' => $first, - 'last' => $last, - }, - } - ); - # or it just be something that was typed in... (try that in a sec) } @@ -326,7 +300,9 @@ sub smart_search { OR LOWER(cust_main.last) = $q_value OR LOWER(cust_main.company) = $q_value OR LOWER(cust_main.ship_company) = $q_value - "; + OR LOWER(contact.first) = $q_value + OR LOWER(contact.last) = $q_value + )"; #address1 (yes, it's a kludge) $sql .= " OR EXISTS ( @@ -336,20 +312,12 @@ sub smart_search { )" if $conf->exists('address1-search'); - #contacts (look, another kludge) - $sql .= " OR EXISTS ( SELECT 1 FROM contact - WHERE ( LOWER(contact.first) = $q_value - OR LOWER(contact.last) = $q_value - ) - AND contact.custnum IS NOT NULL - AND contact.custnum = cust_main.custnum - ) - ) "; - push @cust_main, qsearch( { 'table' => 'cust_main', - 'hashref' => \%options, + 'select' => 'cust_main.*, cust_contact.*, contact.contactnum, contact.last as contact_last, contact.first as contact_first, contact.title', + '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 @@ -1085,6 +1053,45 @@ sub search { 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 = ''; diff --git a/httemplate/elements/searchbar-cust_main.html b/httemplate/elements/searchbar-cust_main.html index 5bfef484a..7f8f9d850 100644 --- a/httemplate/elements/searchbar-cust_main.html +++ b/httemplate/elements/searchbar-cust_main.html @@ -26,7 +26,7 @@ my $curuser = $FS::CurrentUser::CurrentUser; my $menu_position = $curuser->option('menu_position') || 'top'; my $cust_width = 246; -my $cust_label = '(cust #, name, company'; +my $cust_label = '(cust #, name, company, email'; if ( $conf->exists('address1-search') ) { $cust_label .= ', address'; $cust_width += 56; diff --git a/httemplate/search/contact.html b/httemplate/search/contact.html index 759f09521..5f02fef2f 100644 --- a/httemplate/search/contact.html +++ b/httemplate/search/contact.html @@ -29,6 +29,30 @@ my $email_sub = sub { join(', ', map $_->emailaddress, @contact_email); }; +my $work_phone_sub = sub { + my $contact = shift; + my $phone_type = qsearchs('phone_type', { 'typename' => 'Work' }); + #can't because contactnum is in the wrong field + my @contact_workphone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } ); + join(', ', map $_->phonenum, @contact_workphone); +}; + +my $mobile_phone_sub = sub { + my $contact = shift; + my $phone_type = qsearchs('phone_type', { 'typename' => 'Mobile' }); + #can't because contactnum is in the wrong field + my @contact_mobilephone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } ); + join(', ', map $_->phonenum, @contact_mobilephone); +}; + +my $home_phone_sub = sub { + my $contact = shift; + my $phone_type = qsearchs('phone_type', { 'typename' => 'Home' }); + #can't because contactnum is in the wrong field + my @contact_homephone = qsearch('contact_phone', { 'contactnum' => $contact->contact_contactnum, 'phonetypenum' => $phone_type->phonetypenum } ); + join(', ', map $_->phonenum, @contact_homephone); +}; + my $link; #for closure in this sub, we'll define it later my $contact_classname_sub = sub { my $contact = shift; @@ -44,9 +68,9 @@ my $contact_classname_sub = sub { $X_contact->contact_classname; }; -my @header = ( 'First', 'Last', 'Title', 'Email', 'Type' ); -my @fields = ( 'first', 'last', 'title', $email_sub, $contact_classname_sub ); -my @links = ( '', '', '', '', '', ); +my @header = ( 'First', 'Last', 'Title', 'Email', 'Work Phone', 'Mobile Phone', 'Home Phone', 'Type' ); +my @fields = ( 'first', 'last', 'title', $email_sub, $work_phone_sub, $mobile_phone_sub, $home_phone_sub, $contact_classname_sub ); +my @links = ( '', '', '', '', '', '', '', '', ); my $company_link = ''; diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html index 22996a20c..30162506f 100755 --- a/httemplate/search/cust_main.html +++ b/httemplate/search/cust_main.html @@ -72,11 +72,20 @@ for my $param (qw( classnum refnum pkg_classnum )) { $search_hash{$param} = [ $cgi->param($param) ]; } -#tags my $params = $cgi->Vars; + +#contacts +$search_hash{'contacts'} = { + map { $_ => $cgi->param($_), } + grep { /^(contacts_*)/ && $cgi->param($_) } + keys %$params +}; + +#tags $search_hash{'tagnum'} = [ map { /^tagnum(\d+)/ && $1 } - grep { /^tagnum(\d+)/ && $cgi->param($_) } keys %$params + grep { /^tagnum(\d+)/ && $cgi->param($_) } + keys %$params ]; ### diff --git a/httemplate/search/elements/options_cust_contacts.html b/httemplate/search/elements/options_cust_contacts.html new file mode 100644 index 000000000..cfbf834b0 --- /dev/null +++ b/httemplate/search/elements/options_cust_contacts.html @@ -0,0 +1,36 @@ + <TR> + <TH ALIGN="right" VALIGN="center"><% mt('First name') |h %></TH> + <TD><INPUT TYPE="text" NAME="<%$field_prefix%>firstname" SIZE=54></TD> + </TR> + + <TR> + <TH ALIGN="right" VALIGN="center"><% mt('Last name') |h %></TH> + <TD><INPUT TYPE="text" NAME="<%$field_prefix%>lastname" SIZE=54></TD> + </TR> + + <TR> + <TH ALIGN="right" VALIGN="center"><% mt('Email') |h %></TH> + <TD><INPUT TYPE="text" NAME="<%$field_prefix%>email" SIZE=54></TD> + </TR> + + <TR> + <TH ALIGN="right" VALIGN="center"><% mt('Home Phone') |h %></TH> + <TD><INPUT TYPE="text" NAME="<%$field_prefix%>homephone" SIZE=54></TD> + </TR> + + <TR> + <TH ALIGN="right" VALIGN="center"><% mt('Work Phone') |h %></TH> + <TD><INPUT TYPE="text" NAME="<%$field_prefix%>workphone" SIZE=54></TD> + </TR> + + <TR> + <TH ALIGN="right" VALIGN="center"><% mt('Mobile Phone') |h %></TH> + <TD><INPUT TYPE="text" NAME="<%$field_prefix%>mobilephone" SIZE=54></TD> + </TR> + +<%init> + +my %opt = @_; +my $field_prefix = $opt{'pre_fix'} ? $opt{'pre_fix'} : ''; + +</%init>
\ No newline at end of file diff --git a/httemplate/search/report_cust_main.html b/httemplate/search/report_cust_main.html index 14e784454..0a6726215 100755 --- a/httemplate/search/report_cust_main.html +++ b/httemplate/search/report_cust_main.html @@ -166,6 +166,14 @@ </TABLE> <BR> + <FONT CLASS="fsinnerbox-title"><% emt('Contacts search options') %></FONT> + <TABLE CLASS="fsinnerbox"> + <& elements/options_cust_contacts.html, + 'pre_fix' => 'contacts_', + &> + </TABLE> + <BR> + <FONT CLASS="fsinnerbox-title"><% emt('Billing search options') %></FONT> <TABLE CLASS="fsinnerbox"> |