From 6e2e2e192be2ac9267db44c736de34082262cc65 Mon Sep 17 00:00:00 2001 From: Ivan Kohler Date: Sun, 3 Feb 2013 12:53:24 -0800 Subject: [PATCH] advanced phone number search, RT#21054 --- FS/FS/svc_Common.pm | 210 +++++++++++++++++++--- httemplate/elements/tr-select-part_svc.html | 2 +- httemplate/search/elements/report_svc_Common.html | 38 +++- httemplate/search/svc_phone.cgi | 72 ++------ 4 files changed, 240 insertions(+), 82 deletions(-) diff --git a/FS/FS/svc_Common.pm b/FS/FS/svc_Common.pm index ef37351a5..af655e7b6 100644 --- a/FS/FS/svc_Common.pm +++ b/FS/FS/svc_Common.pm @@ -43,27 +43,6 @@ inherit from, i.e. FS::svc_acct. FS::svc_Common inherits from FS::Record. =over 4 -=item search_sql_field FIELD STRING - -Class method which returns an SQL fragment to search for STRING in FIELD. - -It is now case-insensitive by default. - -=cut - -sub search_sql_field { - my( $class, $field, $string ) = @_; - my $table = $class->table; - my $q_string = dbh->quote($string); - "LOWER($table.$field) = LOWER($q_string)"; -} - -#fallback for services that don't provide a search... -sub search_sql { - #my( $class, $string ) = @_; - '1 = 0'; #false -} - =item new =cut @@ -1282,6 +1261,195 @@ sub nms_ip_delete { #XXX not yet implemented } +=item search_sql_field FIELD STRING + +Class method which returns an SQL fragment to search for STRING in FIELD. + +It is now case-insensitive by default. + +=cut + +sub search_sql_field { + my( $class, $field, $string ) = @_; + my $table = $class->table; + my $q_string = dbh->quote($string); + "LOWER($table.$field) = LOWER($q_string)"; +} + +#fallback for services that don't provide a search... +sub search_sql { + #my( $class, $string ) = @_; + '1 = 0'; #false +} + +=item search HASHREF + +Class method which returns a qsearch hash expression to search for parameters +specified in HASHREF. + +Parameters: + +=over 4 + +=item unlinked - set to search for all unlinked services. Overrides all other options. + +=item agentnum + +=item custnum + +=item svcpart + +=item ip_addr + +=item pkgpart - arrayref + +=item routernum - arrayref + +=item sectornum - arrayref + +=item towernum - arrayref + +=item order_by + +=back + +=cut + +# based on FS::svc_acct::search, both that and svc_broadband::search should +# eventually use this instead +sub search { + my ($class, $params) = @_; + + my @from = ( + 'LEFT JOIN cust_svc USING ( svcnum )', + 'LEFT JOIN part_svc USING ( svcpart )', + 'LEFT JOIN cust_pkg USING ( pkgnum )', + 'LEFT JOIN cust_main USING ( custnum )', + ); + + my @where = (); + +# # domain +# if ( $params->{'domain'} ) { +# my $svc_domain = qsearchs('svc_domain', { 'domain'=>$params->{'domain'} } ); +# #preserve previous behavior & bubble up an error if $svc_domain not found? +# push @where, 'domsvc = '. $svc_domain->svcnum if $svc_domain; +# } +# +# # domsvc +# if ( $params->{'domsvc'} =~ /^(\d+)$/ ) { +# push @where, "domsvc = $1"; +# } + + #unlinked + push @where, 'pkgnum IS NULL' if $params->{'unlinked'}; + + #agentnum + if ( $params->{'agentnum'} =~ /^(\d+)$/ && $1 ) { + push @where, "cust_main.agentnum = $1"; + } + + #custnum + if ( $params->{'custnum'} =~ /^(\d+)$/ && $1 ) { + push @where, "custnum = $1"; + } + + #customer status + if ( $params->{'cust_status'} =~ /^([a-z]+)$/ ) { + push @where, FS::cust_main->cust_status_sql . " = '$1'"; + } + + #customer balance + if ( $params->{'balance'} =~ /^\s*(\d*(\.\d{1,2})?)\s*$/ && length($1) ) { + my $balance = $1; + + my $age = ''; + if ( $params->{'balance_days'} =~ /^\s*(\d*(\.\d{1,3})?)\s*$/ && length($1) ) { + $age = time - 86400 * $1; + } + push @where, FS::cust_main->balance_date_sql($age) . " > $balance"; + } + + #payby + if ( $params->{'payby'} && scalar(@{ $params->{'payby'} }) ) { + my @payby = map "'$_'", grep /^(\w+)$/, @{ $params->{'payby'} }; + push @where, 'payby IN ('. join(',', @payby ). ')'; + } + + #pkgpart + if ( $params->{'pkgpart'} && scalar(@{ $params->{'pkgpart'} }) ) { + my @pkgpart = grep /^(\d+)$/, @{ $params->{'pkgpart'} }; + push @where, 'cust_pkg.pkgpart IN ('. join(',', @pkgpart ). ')'; + } + + # svcpart + if ( $params->{'svcpart'} && scalar(@{ $params->{'svcpart'} }) ) { + my @svcpart = grep /^(\d+)$/, @{ $params->{'svcpart'} }; + push @where, 'svcpart IN ('. join(',', @svcpart ). ')'; + } + + if ( $params->{'exportnum'} =~ /^(\d+)$/ ) { + push @from, ' LEFT JOIN export_svc USING ( svcpart )'; + push @where, "exportnum = $1"; + } + +# # sector and tower +# my @where_sector = $class->tower_sector_sql($params); +# if ( @where_sector ) { +# push @where, @where_sector; +# push @from, ' LEFT JOIN tower_sector USING ( sectornum )'; +# } + + # here is the agent virtualization + #if ($params->{CurrentUser}) { + # my $access_user = + # qsearchs('access_user', { username => $params->{CurrentUser} }); + # + # if ($access_user) { + # push @where, $access_user->agentnums_sql('table'=>'cust_main'); + # }else{ + # push @where, "1=0"; + # } + #} else { + push @where, $FS::CurrentUser::CurrentUser->agentnums_sql( + 'table' => 'cust_main', + 'null_right' => 'View/link unlinked services', + ); + #} + + push @where, @{ $params->{'where'} } if $params->{'where'}; + + my $addl_from = join(' ', @from); + my $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : ''; + + my $table = $class->table; + + my $count_query = "SELECT COUNT(*) FROM $table $addl_from $extra_sql"; + #if ( keys %svc_X ) { + # $count_query .= ' WHERE '. + # join(' AND ', map "$_ = ". dbh->quote($svc_X{$_}), + # keys %svc_X + # ); + #} + + { + 'table' => $table, + 'hashref' => {}, + 'select' => join(', ', + "$table.*", + 'part_svc.svc', + 'cust_main.custnum', + @{ $params->{'addl_select'} || [] }, + FS::UI::Web::cust_sql_fields($params->{'cust_fields'}), + ), + 'addl_from' => $addl_from, + 'extra_sql' => $extra_sql, + 'order_by' => $params->{'order_by'}, + 'count_query' => $count_query, + }; + +} + =back =head1 BUGS diff --git a/httemplate/elements/tr-select-part_svc.html b/httemplate/elements/tr-select-part_svc.html index 9d4508091..959ac8dd9 100644 --- a/httemplate/elements/tr-select-part_svc.html +++ b/httemplate/elements/tr-select-part_svc.html @@ -5,7 +5,7 @@ % } else { - <% $opt{'label'} || 'Package definition' %> + <% $opt{'label'} || 'Service definition' %> <% include( '/elements/select-part_svc.html', 'multiple' => 1, diff --git a/httemplate/search/elements/report_svc_Common.html b/httemplate/search/elements/report_svc_Common.html index 04feb9e45..434197078 100644 --- a/httemplate/search/elements/report_svc_Common.html +++ b/httemplate/search/elements/report_svc_Common.html @@ -27,22 +27,50 @@ Example: % unless ( $custnum ) { + <& /elements/tr-select-agent.html, - 'curr_value' => scalar( $cgi->param('agentnum') ), - 'disable_empty' => 0, + curr_value => scalar( $cgi->param('agentnum') ), + disable_empty => 0, + &> + + <& /elements/tr-select-cust_main-status.html, + label => 'Customer Status', + field => 'cust_status', + &> + + <& /elements/tr-select-payby.html, + label => emt('Payment method:'), + payby_type => 'cust', + multiple => 1, + all_selected => 1, + &> + + <& /elements/tr-input-money.html, + label => 'Balance over', + field => 'balance', + &> + + <& /elements/tr-input-text.html, + label => 'Balance age (days)', + field => 'balance_days', + size => 4, &> +% } + % # just this customer's domains? %# <& /elements/tr-select-domain.html, %# 'element_name' => 'domsvc', %# 'curr_value' => scalar( $cgi->param('domsvc') ), %# 'disable_empty' => 0, %# &> -% } <& /elements/tr-selectmultiple-part_pkg.html &> - <& /elements/tr-select-part_svc.html, 'svcdb'=>$svcdb &> + <& /elements/tr-select-part_svc.html, + 'svcdb' => $svcdb, + 'label' => 'Services', + &>   @@ -80,7 +108,7 @@ die "access denied" my $title = $opt{'title'}; #false laziness w/report_cust_pkg.html -my $custnum = ''; +my( $custnum, $cust_main) = ('', ''); if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { $custnum = $1; my $cust_main = qsearchs({ diff --git a/httemplate/search/svc_phone.cgi b/httemplate/search/svc_phone.cgi index 7fadbbbb7..65aa1ae14 100644 --- a/httemplate/search/svc_phone.cgi +++ b/httemplate/search/svc_phone.cgi @@ -56,8 +56,6 @@ die "access denied" my $conf = new FS::Conf; my @select = (); -my %svc_phone = (); -my @extra_sql = (); my $orderby = 'ORDER BY svcnum'; my @header = (); @@ -65,9 +63,12 @@ my @fields = (); my $link = [ "${p}view/svc_phone.cgi?", 'svcnum' ]; my $redirect = $link; +my %search_hash = (); +my @extra_sql = (); + if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { - push @extra_sql, 'pkgnum IS NULL' + $search_hash{'unlinked'} = 1 if $cgi->param('magic') eq 'unlinked'; if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { @@ -121,68 +122,29 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } elsif ( $cgi->param('magic') =~ /^advanced$/ ) { - if ( $cgi->param('agentnum') =~ /^(\d+)$/ ) { - push @extra_sql, "agentnum = $1"; + for (qw( agentnum custnum cust_status balance balance_days cust_fields )) { + $search_hash{$_} = $cgi->param($_) if length($cgi->param($_)); } - my $pkgpart = [ grep /^(\d+)$/, $cgi->param('pkgpart') ]; - if ( @$pkgpart ) { - push @extra_sql, - 'cust_pkg.pkgpart IN ('. join(',', @$pkgpart ). ')'; - } - - my $svcpart = [ grep /^(\d+)$/, $cgi->param('svcpart') ]; - if ( @$svcpart ) { - push @extra_sql, - 'svcpart IN ('. join(',', @$svcpart ). ')'; + for (qw( payby pkgpart svcpart )) { + $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_); } } elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - push @extra_sql, "svcpart = $1"; + $search_hash{'svcpart'} = [ $1 ]; } else { $cgi->param('phonenum') =~ /^([\d\- ]+)$/; - ( $svc_phone{'phonenum'} = $1 ) =~ s/\D//g; + my $phonenum = $1; + $phonenum =~ s/\D//g; + push @extra_sql, "phonenum = '$phonenum'"; } -my $addl_from = ' LEFT JOIN cust_svc USING ( svcnum ) '. - ' LEFT JOIN part_svc USING ( svcpart ) '. - ' LEFT JOIN cust_pkg USING ( pkgnum ) '. - ' LEFT JOIN cust_main USING ( custnum ) '; - -#here is the agent virtualization -push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( - 'null_right' => 'View/link unlinked services' - ); +$search_hash{'addl_select'} = \@select; +$search_hash{'order_by'} = $orderby; +$search_hash{'where'} = \@extra_sql; -my $extra_sql = ''; -if ( @extra_sql ) { - $extra_sql = ( keys(%svc_phone) ? ' AND ' : ' WHERE ' ). - join(' AND ', @extra_sql ); -} - -my $count_query = "SELECT COUNT(*) FROM svc_phone $addl_from "; -if ( keys %svc_phone ) { - $count_query .= ' WHERE '. - join(' AND ', map "$_ = ". dbh->quote($svc_phone{$_}), - keys %svc_phone - ); -} -$count_query .= $extra_sql; - -my $sql_query = { - 'table' => 'svc_phone', - 'hashref' => \%svc_phone, - 'select' => join(', ', - 'svc_phone.*', - 'part_svc.svc', - @select, - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $extra_sql, - 'order_by' => $orderby, - 'addl_from' => $addl_from, -}; +my $sql_query = FS::svc_phone->search(\%search_hash); +my $count_query = delete($sql_query->{'count_query'}); #smaller false laziness w/svc_*.cgi here my $link_cust = sub { -- 2.11.0