From: Christopher Burger Date: Wed, 22 May 2019 16:58:05 +0000 (-0400) Subject: RT 77532 - added contact phone numbers to advanced customer report X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=commitdiff_plain;h=a98de57aee063b3ff737c283336f83b2e50e14a8 RT 77532 - added contact phone numbers to advanced customer report --- diff --git a/FS/FS/ConfDefaults.pm b/FS/FS/ConfDefaults.pm index bd5893439..d81e8e2dc 100644 --- a/FS/FS/ConfDefaults.pm +++ b/FS/FS/ConfDefaults.pm @@ -36,9 +36,9 @@ sub cust_fields_avail { ( 'Agent | Agent Cust# or Cust# | Cust. Status | Customer' => 'Agent | Agent Cust# | Status | Last, First or Company (Last, First)', - 'Customer | Day phone | Night phone | Mobile phone | Fax number' => + "Customer | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s)" => 'Customer | (all phones)', - 'Cust# | Customer | Day phone | Night phone | Mobile phone | Fax number' => + 'Cust# | Customer | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s)' => 'custnum | Customer | (all phones)', 'Cust. Status | Name | Company' => @@ -56,28 +56,28 @@ sub cust_fields_avail { ( 'Cust# | Cust. Status | Name | Company' => 'custnum | Status | Last, First | Company', - 'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Contact email(s) | Invoices | Messages' => + 'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | Contact email(s) | Invoices | Messages' => 'custnum | Status | Last, First | Company | (address) | (all phones) | Contact email(s)', - 'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Invoicing email(s)' => + 'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | Invoicing email(s)' => 'custnum | Status | Last, First | Company | (address) | (all phones) | Invoicing email(s)', - 'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Invoicing email(s) | Current Balance' => + 'Cust# | Cust. Status | Name | Company | Address 1 | Address 2 | City | State | Zip | Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | Invoicing email(s) | Current Balance' => 'custnum | Status | Last, First | Company | (address) | (all phones) | Invoicing email(s) | Current Balance', - 'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s)' => + 'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s)' => 'custnum | Status | Last, First | Company | (address) | (all phones) | (service address) | Invoicing email(s)', - 'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s) | Current Balance' => + 'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s) | Current Balance' => 'custnum | Status | Last, First | Company | (address) | (all phones) | (service address) | Invoicing email(s) | Current Balance', - 'Cust# | Agent Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s) | Current Balance' => + 'Cust# | Agent Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | Invoicing email(s) | Current Balance' => 'custnum | Agent Cust# | Status | Last, First | Company | (address) | (all phones) | (service address) | Invoicing email(s) | Current Balance', - 'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | (bill) Latitude | (bill) Longitude | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | (service) Latitude | (service) Longitude | Invoicing email(s) | Current Balance' => + 'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | (bill) Latitude | (bill) Longitude | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | (service) Latitude | (service) Longitude | Invoicing email(s) | Current Balance' => 'custnum | Status | Last, First | Company | (address+coord) | (all phones) | (service address+coord) | Invoicing email(s) | Current Balance', - 'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | (bill) Latitude | (bill) Longitude | Day phone | Night phone | Mobile phone | Fax number | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | (service) Latitude | (service) Longitude | Invoicing email(s) | Current Balance | Advertising Source' => + 'Cust# | Cust. Status | Name | Company | (bill) Address 1 | (bill) Address 2 | (bill) City | (bill) State | (bill) Zip | (bill) Country | (bill) Latitude | (bill) Longitude | Day phone | Night phone | Mobile phone | Fax number | Contact phone(s) | (service) Address 1 | (service) Address 2 | (service) City | (service) State | (service) Zip | (service) Country | (service) Latitude | (service) Longitude | Invoicing email(s) | Current Balance | Advertising Source' => 'custnum | Status | Last, First | Company | (address+coord) | (all phones) | (service address+coord) | Invoicing email(s) | Current Balance | Advertising Source', 'Invoicing email(s)' => 'Invoicing email(s)', diff --git a/FS/FS/UI/Web.pm b/FS/FS/UI/Web.pm index b6dda8fcc..9eb49c430 100644 --- a/FS/FS/UI/Web.pm +++ b/FS/FS/UI/Web.pm @@ -7,7 +7,7 @@ use Carp qw( confess ); use HTML::Entities; use FS::Conf; use FS::Misc::DateTime qw( parse_datetime day_end ); -use FS::Record qw(dbdef); +use FS::Record qw(dbdef qsearch); use FS::cust_main; # are sql_balance and sql_date_balance in the right module? #use vars qw(@ISA); @@ -357,6 +357,25 @@ sub cust_header { $header2method{'Cust#'} = 'display_custnum' if $conf->exists('cust_main-default_agent_custid'); +foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) { + $header2method{'Contact '.$phone_type->typename.' phone(s)'} = sub { + my $self = shift; + my $num = $phone_type->phonetypenum; + + my @phones; + foreach ($self->contact_list_name_phones) { + my $data = [ + { + 'data' => $_->first.' '.$_->last.' '.FS::contact_phone::phonenum_pretty($_), + }, + ]; + push @phones, $data if $_->phonetypenum eq $phone_type->phonetypenum; + } + return \@phones; + }; + +} + my %header2colormethod = ( 'Cust. Status' => 'cust_statuscolor', ); diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index 10433ed95..57d598a74 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -3175,6 +3175,32 @@ sub contact_list_email_destinations { }); } +=item contact_list_name_phones + +Returns a list of contact phone numbers. +{ phonetypenum => '1', phonenum => 'xxxxxxxxxx', first => 'firstname', last => 'lastname', countrycode => '1' } + +=cut + +sub contact_list_name_phones { + my $self = shift; + my $phone_type = shift; + + warn "$me contact_list_phones" if $DEBUG; + + return () if !$self->custnum; # not yet inserted + return map { $_ } + qsearch({ + table => 'cust_contact', + select => 'phonetypenum, phonenum, first, last, countrycode', + addl_from => ' JOIN contact USING (contactnum) '. + ' JOIN contact_phone USING (contactnum)', + hashref => { 'custnum' => $self->custnum, 'phonetypenum' => $phone_type, }, + order_by => 'ORDER BY custcontactnum DESC', + extra_sql => '', + }); +} + =item contact_list_emailonly Returns an array of hashes containing the emails. Used for displaying contact email field in advanced customer reports. diff --git a/FS/FS/cust_main/Search.pm b/FS/FS/cust_main/Search.pm index ae219c801..63f10fb64 100644 --- a/FS/FS/cust_main/Search.pm +++ b/FS/FS/cust_main/Search.pm @@ -1090,25 +1090,24 @@ sub search { ) "; } - if ($contact_params->{'contacts_homephone'} || $contact_params->{'contacts_workphone'} || $contact_params->{'contacts_mobilephone'}) { - foreach my $phone (qw( contacts_homephone contacts_workphone contacts_mobilephone )) { + if ( grep { /^contacts_phonetypenum(\d+)$/ } keys %{ $contact_params } ) { + my $phone_query; + foreach my $phone ( grep { /^contacts_phonetypenum(\d+)$/ } keys %{ $contact_params } ) { + $phone =~ /^contacts_phonetypenum(\d+)$/ or die "No phone type num $1 from $phone"; + my $phonetypenum = $1; (my $num = $contact_params->{$phone}) =~ s/\W//g; if ( $num =~ /^1?(\d{3})(\d{3})(\d{4})(\d*)$/ ) { $contact_params->{$phone} = "$1$2$3"; } + $phone_query .= " AND ( contact_phone.phonetypenum = '".$phonetypenum."' AND contact_phone.phonenum = '" . $contact_params->{$phone} . "' )" + unless !$contact_params->{$phone}; } - 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'}; push @where, "EXISTS ( SELECT 1 FROM contact_phone JOIN cust_contact USING (contactnum) WHERE cust_contact.custnum = cust_main.custnum - $home_query $work_query $mobile_query + $phone_query ) "; } -} + } ## diff --git a/httemplate/elements/select-cust-fields.html b/httemplate/elements/select-cust-fields.html index 5e3063877..7396808b1 100644 --- a/httemplate/elements/select-cust-fields.html +++ b/httemplate/elements/select-cust-fields.html @@ -1,8 +1,14 @@ <%init> my( $cust_fields, %opt ) = @_; - use FS::ConfDefaults; - $opt{'avail_fields'} ||= [ FS::ConfDefaults->cust_fields_avail() ]; + my @fields = FS::ConfDefaults->cust_fields_avail(); + my $contact_phone_list; + foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) { + $contact_phone_list .= " | Contact ".$phone_type->typename." phone(s)"; + } + @fields = map {s/\| Contact phone\(s\)/$contact_phone_list/g; $_; } @fields; + + $opt{'avail_fields'} ||= [ @fields ]; tie my %hash, 'Tie::IxHash', @{ $opt{'avail_fields'} }; diff --git a/httemplate/elements/tr-select-cust-fields.html b/httemplate/elements/tr-select-cust-fields.html index dd8513316..62b8144e3 100644 --- a/httemplate/elements/tr-select-cust-fields.html +++ b/httemplate/elements/tr-select-cust-fields.html @@ -6,6 +6,13 @@ my( $cust_fields, %opt ) = @_; -$opt{'avail_fields'} ||= [ FS::ConfDefaults->cust_fields_avail() ]; +my @fields = FS::ConfDefaults->cust_fields_avail(); +my $contact_phone_list; +foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) { + $contact_phone_list .= " | Contact ".$phone_type->typename." phone(s)"; +} +@fields = map {s/\| Contact phone\(s\)/$contact_phone_list/g; $_; } @fields; + +$opt{'avail_fields'} ||= [ @fields ]; diff --git a/httemplate/search/contact.html b/httemplate/search/contact.html index 24cb237c3..0f2b283b5 100644 --- a/httemplate/search/contact.html +++ b/httemplate/search/contact.html @@ -38,7 +38,6 @@ my $classnum_null = grep{ $_ eq 0 } $cgi->param('classnum'); # Catch destination values from dest multi-checkbox, default to message # irrelevant to prospect contacts my @dest = grep{ /^(message|invoice)$/ } $cgi->param('dest'); -@dest = ('message') unless @dest; # Cache the contact_class table my %classname = @@ -125,7 +124,7 @@ if (@classnum || $classnum_null) { if (@dest && $link eq 'cust_main') { my @stm; push @stm, "cust_contact.${_}_dest IS NOT NULL" for @dest; - $extra_sql .= "\nAND (".join(' OR ',@stm).') '; + $extra_sql .= "\nAND (".join(' AND ',@stm).') '; } if ($DEBUG) { @@ -141,20 +140,23 @@ if ($DEBUG) { # Prepare to display phone numbers # adds 3 additional queries per table record :-( -my %phonetype = (qw/1 Work 2 Home 3 Mobile 4 Fax/); -my %phoneid = (qw/Work 1 Home 2 Mobile 3 Fax 4/); my $get_phone_sub = sub { my $type = shift; return sub { my $rec = shift; my @p = qsearch('contact_phone', { contactnum => $rec->contact_contactnum, - phonetypenum => $phoneid{$type} + phonetypenum => $type, }); - @p ? (join ', ',map{$_->phonenum} @p) : undef; + @p ? (join ', ',map{$_->phonenum_pretty} @p) : undef; }; }; +my @phones; +foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) { + push @phones, { label => $phone_type->typename.' Phone', field => $get_phone_sub->($phone_type->phonetypenum), }; +} + # Cache contact types my %classname = map {$_->classnum => $_->classname} @@ -166,9 +168,7 @@ my @report = ( { label => 'Last', field => 'contact_last' }, { label => 'Title', field => 'contact_title' }, { label => 'E-Mail', field => 'contact_email_emailaddress' }, - { label => 'Work Phone', field => $get_phone_sub->('Work') }, - { label => 'Mobile Phone', field => $get_phone_sub->('Mobile') }, - { label => 'Home Phone', field => $get_phone_sub->('Home') }, + @phones, { label => 'Type', field => sub { my $rec = shift; diff --git a/httemplate/search/elements/options_cust_contacts.html b/httemplate/search/elements/options_cust_contacts.html index cfbf834b0..8a6b76913 100644 --- a/httemplate/search/elements/options_cust_contacts.html +++ b/httemplate/search/elements/options_cust_contacts.html @@ -12,21 +12,12 @@ <% mt('Email') |h %> - - - <% mt('Home Phone') |h %> - - - - - <% mt('Work Phone') |h %> - - - +% foreach my $phone_type ( qsearch({table=>'phone_type', order_by=>'weight'}) ) { - <% mt('Mobile Phone') |h %> - + <% $phone_type->typename. ' Phone' |h %> + +% } <%init>