From 6c9cd1c36adbb9fc950fcf0a0b269fa6f16838a1 Mon Sep 17 00:00:00 2001 From: mark Date: Fri, 17 Sep 2010 18:12:08 +0000 Subject: [PATCH] email_search_result for cust_pkg and svc_broadband, RT#8736 --- FS/FS/Mason.pm | 1 + FS/FS/cust_main.pm | 162 +--------------------- FS/FS/cust_main_Mixin.pm | 196 +++++++++++++++++++++++++++ FS/FS/svc_broadband.pm | 120 ++++++++++++++++ httemplate/elements/email-link.html | 16 +++ httemplate/elements/menu.html | 2 +- httemplate/misc/email-customers.html | 108 +++++++++++---- httemplate/misc/process/email-customers.html | 2 +- httemplate/search/cust_main.html | 2 +- httemplate/search/cust_pkg.cgi | 4 + httemplate/search/report_svc_broadband.html | 100 ++++++++++++++ httemplate/search/svc_broadband.cgi | 75 +++------- 12 files changed, 546 insertions(+), 242 deletions(-) create mode 100644 httemplate/elements/email-link.html create mode 100755 httemplate/search/report_svc_broadband.html diff --git a/FS/FS/Mason.pm b/FS/FS/Mason.pm index 7be78aa03..d769d8514 100644 --- a/FS/FS/Mason.pm +++ b/FS/FS/Mason.pm @@ -113,6 +113,7 @@ if ( -e $addl_handler_use_file ) { use Locale::Country; use Business::US::USPS::WebTools::AddressStandardization; use LWP::UserAgent; + use Storable qw( nfreeze thaw ); use FS; use FS::UID qw( getotaker dbh datasrc driver_name ); use FS::Record qw( qsearch qsearchs fields dbdef diff --git a/FS/FS/cust_main.pm b/FS/FS/cust_main.pm index f4b9c5993..007beec92 100644 --- a/FS/FS/cust_main.pm +++ b/FS/FS/cust_main.pm @@ -2,7 +2,11 @@ package FS::cust_main; require 5.006; use strict; -use base qw( FS::otaker_Mixin FS::payinfo_Mixin FS::Record ); +use base qw( FS::otaker_Mixin + FS::payinfo_Mixin + FS::cust_main_Mixin + FS::Record + ); use vars qw( @EXPORT_OK $DEBUG $me $conf @encrypted_fields $import $ignore_expired_card @@ -8048,7 +8052,7 @@ sub search { ? @{ $params->{'payby'} } : ( $params->{'payby'} ); - @payby = grep /^([A-Z]{4})$/, @{ $params->{'payby'} }; + @payby = grep /^([A-Z]{4})$/, @payby; push @where, '( '. join(' OR ', map "cust_main.payby = '$_'", @payby). ' )' if @payby; @@ -8183,160 +8187,6 @@ sub search { } -=item email_search_result HASHREF - -(Class method) - -Emails a notice to the specified customers. - -Valid parameters are those of the L method, plus the following: - -=over 4 - -=item from - -From: address - -=item subject - -Email Subject: - -=item html_body - -HTML body - -=item text_body - -Text body - -=item job - -Optional job queue job for status updates. - -=back - -Returns an error message, or false for success. - -If an error occurs during any email, stops the enture send and returns that -error. Presumably if you're getting SMTP errors aborting is better than -retrying everything. - -=cut - -sub email_search_result { - my($class, $params) = @_; - - my $from = delete $params->{from}; - my $subject = delete $params->{subject}; - my $html_body = delete $params->{html_body}; - my $text_body = delete $params->{text_body}; - my $error = ''; - - my $job = delete $params->{'job'} - or die "email_search_result must run from the job queue.\n"; - - $params->{'payby'} = [ split(/\0/, $params->{'payby'}) ] - unless ref($params->{'payby'}); - - my $sql_query = $class->search($params); - - my $count_query = delete($sql_query->{'count_query'}); - my $count_sth = dbh->prepare($count_query) - or die "Error preparing $count_query: ". dbh->errstr; - $count_sth->execute - or die "Error executing $count_query: ". $count_sth->errstr; - my $count_arrayref = $count_sth->fetchrow_arrayref; - my $num_cust = $count_arrayref->[0]; - - #my @extra_headers = @{ delete($sql_query->{'extra_headers'}) }; - #my @extra_fields = @{ delete($sql_query->{'extra_fields'}) }; - - - my( $num, $last, $min_sec ) = (0, time, 5); #progresbar foo - my @retry_jobs = (); - my $success = 0; - - #eventually order+limit magic to reduce memory use? - foreach my $cust_main ( qsearch($sql_query) ) { - - #progressbar first, so that the count is right - $num++; - if ( time - $min_sec > $last ) { - my $error = $job->update_statustext( - int( 100 * $num / $num_cust ) - ); - die $error if $error; - $last = time; - } - - my $to = $cust_main->invoicing_list_emailonly_scalar; - - if( $to ) { - my @message = ( - 'from' => $from, - 'to' => $to, - 'subject' => $subject, - 'html_body' => $html_body, - 'text_body' => $text_body, - ); - - $error = send_email( generate_email( @message ) ); - - if($error) { - # queue the sending of this message so that the user can see what we - # tried to do, and retry if desired - my $queue = new FS::queue { - 'job' => 'FS::Misc::process_send_email', - 'custnum' => $cust_main->custnum, - 'status' => 'failed', - 'statustext' => $error, - }; - $queue->insert(@message); - push @retry_jobs, $queue; - } - else { - $success++; - } - } - - if($success == 0 and - (scalar(@retry_jobs) > 10 or $num == $num_cust) - ) { - # 10 is arbitrary, but if we have enough failures, that's - # probably a configuration or network problem, and we - # abort the batch and run away screaming. - # We NEVER do this if anything was successfully sent. - $_->delete foreach (@retry_jobs); - return "multiple failures: '$error'\n"; - } - } - - if(@retry_jobs) { - # fail the job, but with a status message that makes it clear - # something was sent. - return "Sent $success, failed ".scalar(@retry_jobs).". Failed attempts placed in job queue.\n"; - } - - return ''; -} - -sub process_email_search_result { - my $job = shift; - #warn "$me process_re_X $method for job $job\n" if $DEBUG; - - my $param = thaw(decode_base64(shift)); - warn Dumper($param) if $DEBUG; - - $param->{'job'} = $job; - - $param->{'payby'} = [ split(/\0/, $param->{'payby'}) ] - unless ref($param->{'payby'}); - - my $error = FS::cust_main->email_search_result( $param ); - die $error if $error; - -} - =item fuzzy_search FUZZY_HASHREF [ HASHREF, SELECT, EXTRA_SQL, CACHE_OBJ ] Performs a fuzzy (approximate) search and returns the matching FS::cust_main diff --git a/FS/FS/cust_main_Mixin.pm b/FS/FS/cust_main_Mixin.pm index 3dde95f2e..b446d6965 100644 --- a/FS/FS/cust_main_Mixin.pm +++ b/FS/FS/cust_main_Mixin.pm @@ -5,6 +5,8 @@ use vars qw( $DEBUG $me ); use Carp qw( confess ); use FS::UID qw(dbh); use FS::cust_main; +use FS::Record qw( qsearch qsearchs ); +use FS::Misc qw( send_email generate_email ); $DEBUG = 0; $me = '[FS::cust_main_Mixin]'; @@ -33,6 +35,11 @@ for example, from a JOINed search. See httemplate/search/ for examples. sub cust_unlinked_msg { '(unlinked)'; } sub cust_linked { $_[0]->custnum; } +sub cust_main { + my $self = shift; + $self->cust_linked ? qsearchs('cust_main', {custnum => $self->custnum}) : ''; +} + =item display_custnum Given an object that contains fields from cust_main (say, from a JOINed @@ -330,6 +337,195 @@ sub cust_search_sql { } +=item email_search_result HASHREF + +Emails a notice to the specified customers. Customers without +invoice email destinations will be skipped. + +Parameters: + +=over 4 + +=item job + +Queue job for status updates. Required. + +=item search + +Hashref of params to the L method. Required. + +=item msgnum + +Message template number (see L). Overrides all +of the following options. + +=item from + +From: address + +=item subject + +Email Subject: + +=item html_body + +HTML body + +=item text_body + +Text body + +=back + +Returns an error message, or false for success. + +If any messages fail to send, they will be queued as individual +jobs which can be manually retried. If the first ten messages +in the job fail, the entire job will abort and return an error. + +=cut + +use Storable qw(thaw); +use MIME::Base64; +use Data::Dumper qw(Dumper); + +sub email_search_result { + my($class, $param) = @_; + + my $msgnum = $param->{msgnum}; + my $from = delete $param->{from}; + my $subject = delete $param->{subject}; + my $html_body = delete $param->{html_body}; + my $text_body = delete $param->{text_body}; + my $error = ''; + + my $job = delete $param->{'job'} + or die "email_search_result must run from the job queue.\n"; + + my $msg_template; + if ( $msgnum ) { + $msg_template = qsearchs('msg_template', { msgnum => $msgnum } ) + or die "msgnum $msgnum not found\n"; + } + + $param->{'payby'} = [ split(/\0/, $param->{'payby'}) ] + unless ref($param->{'payby'}); + + my $sql_query = $class->search($param->{'search'}); + + my $count_query = delete($sql_query->{'count_query'}); + my $count_sth = dbh->prepare($count_query) + or die "Error preparing $count_query: ". dbh->errstr; + $count_sth->execute + or die "Error executing $count_query: ". $count_sth->errstr; + my $count_arrayref = $count_sth->fetchrow_arrayref; + my $num_cust = $count_arrayref->[0]; + + my( $num, $last, $min_sec ) = (0, time, 5); #progresbar foo + my @retry_jobs = (); + my $success = 0; + + #eventually order+limit magic to reduce memory use? + foreach my $obj ( qsearch($sql_query) ) { + + #progressbar first, so that the count is right + $num++; + if ( time - $min_sec > $last ) { + my $error = $job->update_statustext( + int( 100 * $num / $num_cust ) + ); + die $error if $error; + $last = time; + } + + my $cust_main = $obj->cust_main; + my @message; + if ( !$cust_main ) { + next; # unlinked object; nothing else we can do + } + + if ( $msg_template ) { + # XXX add support for other context objects? + @message = $msg_template->prepare( 'cust_main' => $cust_main ); + } + else { + my $to = $cust_main->invoicing_list_emailonly_scalar; + next if !$to; + + @message = ( + 'from' => $from, + 'to' => $to, + 'subject' => $subject, + 'html_body' => $html_body, + 'text_body' => $text_body, + ); + } #if $msg_template + + $error = send_email( generate_email( @message ) ); + + if($error) { + # queue the sending of this message so that the user can see what we + # tried to do, and retry if desired + my $queue = new FS::queue { + 'job' => 'FS::Misc::process_send_email', + 'custnum' => $cust_main->custnum, + 'status' => 'failed', + 'statustext' => $error, + }; + $queue->insert(@message); + push @retry_jobs, $queue; + } + else { + $success++; + } + + if($success == 0 and + (scalar(@retry_jobs) > 10 or $num == $num_cust) + ) { + # 10 is arbitrary, but if we have enough failures, that's + # probably a configuration or network problem, and we + # abort the batch and run away screaming. + # We NEVER do this if anything was successfully sent. + $_->delete foreach (@retry_jobs); + return "multiple failures: '$error'\n"; + } + } # foreach $obj + + if(@retry_jobs) { + # fail the job, but with a status message that makes it clear + # something was sent. + return "Sent $success, failed ".scalar(@retry_jobs).". Failed attempts placed in job queue.\n"; + } + + return ''; +} + +sub process_email_search_result { + my $job = shift; + #warn "$me process_re_X $method for job $job\n" if $DEBUG; + + my $param = thaw(decode_base64(shift)); + warn Dumper($param) if $DEBUG; + + $param->{'job'} = $job; + + $param->{'search'} = thaw(decode_base64($param->{'search'})) + or die "process_email_search_result requires search params.\n"; + +# $param->{'payby'} = [ split(/\0/, $param->{'payby'}) ] +# unless ref($param->{'payby'}); + + my $table = $param->{'table'} + or die "process_email_search_result requires table.\n"; + + eval "use FS::$table;"; + die "error loading FS::$table: $@\n" if $@; + + my $error = "FS::$table"->email_search_result( $param ); + die $error if $error; + +} + =back =head1 BUGS diff --git a/FS/FS/svc_broadband.pm b/FS/FS/svc_broadband.pm index 74cedfc77..5ffe0e452 100755 --- a/FS/FS/svc_broadband.pm +++ b/FS/FS/svc_broadband.pm @@ -113,6 +113,126 @@ sub table { 'svc_broadband'; } sub table_dupcheck_fields { ( 'mac_addr' ); } +=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 order_by + +=back + +=cut + +sub search { + my ($class, $params) = @_; + my @where = (); + 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 )', + ); + + # based on FS::svc_acct::search, probably the most mature of the bunch + #unlinked + push @where, 'pkgnum IS NULL' if $params->{'unlinked'}; + + #agentnum + if ( $params->{'agentnum'} =~ /^(\d+)$/ and $1 ) { + push @where, "agentnum = $1"; + } + push @where, $FS::CurrentUser::CurrentUser->agentnums_sql( + 'null_right' => 'View/link unlinked services', + 'table' => 'cust_main' + ); + + #custnum + if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) { + push @where, "custnum = $1"; + } + + #pkgpart, now properly untainted, can be arrayref + for my $pkgpart ( $params->{'pkgpart'} ) { + if ( ref $pkgpart ) { + my $where = join(',', map { /^(\d+)$/ ? $1 : () } @$pkgpart ); + push @where, "cust_pkg.pkgpart IN ($where)" if $where; + } + elsif ( $pkgpart =~ /^(\d+)$/ ) { + push @where, "cust_pkg.pkgpart = $1"; + } + } + + #routernum, can be arrayref + for my $routernum ( $params->{'routernum'} ) { + push @from, 'LEFT JOIN addr_block USING ( blocknum )'; + if ( ref $routernum and grep { $_ } @$routernum ) { + my $where = join(',', map { /^(\d+)$/ ? $1 : () } @$routernum ); + push @where, "addr_block.routernum IN ($where)" if $where; + } + elsif ( $routernum =~ /^(\d+)$/ ) { + push @where, "addr_block.routernum = $1"; + } + } + + #svcnum + if ( $params->{'svcnum'} =~ /^(\d+)$/ ) { + push @where, "svcnum = $1"; + } + + #svcpart + if ( $params->{'svcpart'} =~ /^(\d+)$/ ) { + push @where, "svcpart = $1"; + } + + #ip_addr + if ( $params->{'ip_addr'} =~ /^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/ ) { + push @where, "ip_addr = '$1'"; + } + + #custnum + if ( $params->{'custnum'} =~ /^(\d+)$/ and $1) { + push @where, "custnum = $1"; + } + + my $addl_from = join(' ', @from); + my $extra_sql = ''; + $extra_sql = 'WHERE '.join(' AND ', @where) if @where; + my $count_query = "SELECT COUNT(*) FROM svc_broadband $addl_from $extra_sql"; + return( { + 'table' => 'svc_broadband', + 'hashref' => {}, + 'select' => join(', ', + 'svc_broadband.*', + 'part_svc.svc', + 'cust_main.custnum', + FS::UI::Web::cust_sql_fields($params->{'cust_fields'}), + ), + 'extra_sql' => $extra_sql, + 'addl_from' => $addl_from, + 'order_by' => "ORDER BY ".($params->{'order_by'} || 'svcnum'), + 'count_query' => $count_query, + } ); +} + =item search_sql STRING Class method which returns an SQL fragment to search for the given string. diff --git a/httemplate/elements/email-link.html b/httemplate/elements/email-link.html new file mode 100644 index 000000000..692e5bc2e --- /dev/null +++ b/httemplate/elements/email-link.html @@ -0,0 +1,16 @@ +% if ( $FS::CurrentUser::CurrentUser->access_right('Bulk send customer notices') ) { +<%$label%> +% } +<%init> +my %opt = @_; +my $table = $opt{'table'}; +my $search_hash = $opt{'search_hash'}; +die "'table' required" if !$table; +die "'search_hash' required" if !$search_hash; + +my $uri = new URI; +$uri->query_form($search_hash); +my $query = $uri->query; +my $label = ($opt{'label'} || 'Email a notice to these customers'); + + diff --git a/httemplate/elements/menu.html b/httemplate/elements/menu.html index 7cd6f898c..d3e00f3de 100644 --- a/httemplate/elements/menu.html +++ b/httemplate/elements/menu.html @@ -185,7 +185,7 @@ foreach my $svcdb ( FS::part_svc->svc_tables() ) { ]; } - if ( $svcdb eq 'svc_acct' ) { + if ( $svcdb eq 'svc_acct' || $svcdb eq 'svc_broadband' ) { $report_svc{"Advanced $lcsname reports"} = [ $fsurl."search/report_$svcdb.html", '' ]; } diff --git a/httemplate/misc/email-customers.html b/httemplate/misc/email-customers.html index 201aed428..0ec8bb9df 100644 --- a/httemplate/misc/email-customers.html +++ b/httemplate/misc/email-customers.html @@ -1,69 +1,77 @@ <% include('/elements/header.html', $title) %>
-% foreach my $key ( keys %search ) { -% my @values = ref($search{$key}) ? @{$search{$key}} : ( $search{$key} ); -% foreach my $value ( @values ) { - -% } -% } + +%# Mixing search params with from address, subject, etc. required special-case +%# handling of those, risked name conflicts, and caused massive problems with +%# multi-valued search params. We are no longer in search context, so we +%# pack the search into a Storable string for later use. + -% if ( $cgi->param('magic') eq 'send' ) { +% if ( $cgi->param('action') eq 'send' ) { Sending notice <% include('/elements/progress-init.html', 'OneTrueForm', - [ keys(%search), qw( from subject html_body text_body ) ], + [ qw( search table from subject html_body text_body msgnum ) ], 'process/email-customers.html', { 'message' => "Notice sent" }, #would be nice to show #, but.. ) %> -% } elsif ( $cgi->param('magic') eq 'preview' ) { +% } elsif ( $cgi->param('action') eq 'preview' ) { Preview notice % } -% if ( $cgi->param('magic') ) { +% if ( $cgi->param('action') ) { +% if ( $msg_template ) { + <% include('/elements/tr-fixed.html', + 'label' => 'Template:', + 'value' => $msg_template->msgname, + ) + %> +% } + <% include('/elements/tr-fixed.html', 'field' => 'from', 'label' => 'From:', - 'value' => scalar( $cgi->param('from') ), + 'value' => scalar( $from ), ) %> <% include('/elements/tr-fixed.html', 'field' => 'subject', 'label' => 'Subject:', - 'value' => scalar( $cgi->param('subject') ), + 'value' => scalar( $subject ), ) %> - + - + % my $text_body = HTML::FormatText->new(leftmargin=>0)->format( % HTML::TreeBuilder->new_from_content( -% $cgi->param('html_body') +% $html_body % ) % ); - +
Message (HTML display): <% $cgi->param('html_body') %><% $html_body %>
Message (Text display): 
<% $text_body %>
<% $text_body %>
-% if ( $cgi->param('magic') eq 'preview' ) { +% if ( $cgi->param('action') eq 'preview' ) {
- + % } % } else { - + +Template: + <% include('/elements/select-table.html', + 'label' => 'Template:', + 'table' => 'msg_template', + 'name_col' => 'msgname', + 'empty_label' => '(none)', + 'onchange' => 'toggle(this)', + ) + %>
+
<% include('/elements/tr-input-text.html', 'field' => 'from', 'label' => 'From:', @@ -102,15 +124,14 @@ %#Substitution vars: -

- + % } -% if ( $cgi->param('magic') eq 'send' ) { +% if ( $cgi->param('action') eq 'send' ) { @@ -123,16 +144,32 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Bulk send customer notices'); -my %search = $cgi->Vars; -delete $search{$_} for qw( magic from subject html_body text_body ); -$search{$_} = [ split(/\0/, $search{$_}) ] - foreach grep { $_ eq 'payby' || $search{$_} =~ /\0/ } keys %search; - -my $title = 'Bulk send customer notices'; +my $table = $cgi->param('table') or die "'table' required"; +my %search; +if ( $cgi->param('search') ) { + %search = %{ thaw(decode_base64($cgi->param('search'))) }; +} +else { + %search = $cgi->Vars; + delete $search{$_} for qw( action table from subject html_body text_body ); + # FS::$table->search is expected to know which parameters might be + # multi-valued, and to accept scalar values for them also. No good + # solution to this since CGI can't tell whether a parameter _might_ + # have had multiple values, only whether it does. + @search{keys %search} = map { /\0/ ? [ split /\0/, $_ ] : $_ } values %search; +} + +my $title = 'Send bulk customer notices'; my $num_cust; -if ( $cgi->param('magic') eq 'preview' ) { - my $sql_query = FS::cust_main->search(\%search); +my $from = $cgi->param('from') || ''; +my $subject = $cgi->param('subject') || ''; +my $html_body = $cgi->param('html_body') || ''; + +my $msg_template = ''; + +if ( $cgi->param('action') eq 'preview' ) { + my $sql_query = "FS::$table"->search(\%search); my $count_query = delete($sql_query->{'count_query'}); my $count_sth = dbh->prepare($count_query) or die "Error preparing $count_query: ". dbh->errstr; @@ -140,6 +177,17 @@ if ( $cgi->param('magic') eq 'preview' ) { or die "Error executing $count_query: ". $count_sth->errstr; my $count_arrayref = $count_sth->fetchrow_arrayref; $num_cust = $count_arrayref->[0]; + + if ( $cgi->param('msgnum') ) { + $msg_template = qsearchs('msg_template', + { msgnum => $cgi->param('msgnum') } ) + or die "template not found: ".$cgi->param('msgnum'); + $sql_query->{'extra_sql'} .= ' LIMIT 1'; + $sql_query->{'order_by'} = ''; + my $cust = qsearchs($sql_query)->cust_main; + my %message = $msg_template->prepare( 'cust_main' => $cust ); + ($from, $subject, $html_body) = @message{'from', 'subject', 'html_body'}; + } } diff --git a/httemplate/misc/process/email-customers.html b/httemplate/misc/process/email-customers.html index c54bc6dca..de2bb926b 100644 --- a/httemplate/misc/process/email-customers.html +++ b/httemplate/misc/process/email-customers.html @@ -4,6 +4,6 @@ die "access denied" unless $FS::CurrentUser::CurrentUser->access_right('Bulk send customer notices'); -my $server = new FS::UI::Web::JSRPC 'FS::cust_main::process_email_search_result', $cgi; +my $server = new FS::UI::Web::JSRPC 'FS::cust_main_Mixin::process_email_search_result', $cgi; diff --git a/httemplate/search/cust_main.html b/httemplate/search/cust_main.html index 270fc38cc..4465c3324 100755 --- a/httemplate/search/cust_main.html +++ b/httemplate/search/cust_main.html @@ -104,7 +104,7 @@ if ( $FS::CurrentUser::CurrentUser->access_right('Bulk send customer notices') ) my $query = $uri->query; push @$menubar, 'Email a notice to these customers' => - "${p}misc/email-customers.html?$query", + "${p}misc/email-customers.html?table=cust_main&$query", } diff --git a/httemplate/search/cust_pkg.cgi b/httemplate/search/cust_pkg.cgi index bd8ea9aa6..da4371f5e 100755 --- a/httemplate/search/cust_pkg.cgi +++ b/httemplate/search/cust_pkg.cgi @@ -290,6 +290,10 @@ my $html_init = sub { 'height' => 210, ). '
'; } + $text .= include( '/elements/email-link.html', + 'search_hash' => \%search_hash, + 'table' => 'cust_pkg', + ); } return $text; }; diff --git a/httemplate/search/report_svc_broadband.html b/httemplate/search/report_svc_broadband.html new file mode 100755 index 000000000..8571ef184 --- /dev/null +++ b/httemplate/search/report_svc_broadband.html @@ -0,0 +1,100 @@ +<% include('/elements/header.html', $title ) %> + + + + +%# extensive false laziness with svc_acct +
+ + + + + +% unless ( $custnum ) { + <% include( '/elements/tr-select-agent.html', + 'curr_value' => scalar( $cgi->param('agentnum') ), + 'disable_empty' => 0, + ) + %> + + <% include( '/elements/tr-select-table.html', + 'label' => 'Routers', + 'table' => 'router', + 'name_col' => 'routername', + 'curr_value' => $routernum, + 'hashref' => {}, + 'multiple' => 'multiple', + ) + %> +% } + + <% include( '/elements/tr-selectmultiple-part_pkg.html', + %pkg_search, + ) + %> + + + + + + + + + +% #move to /elements/tr-select-cust_pkg-fields if anything else needs it... + + + + + + <% include( '/elements/tr-select-cust-fields.html' ) %> + +
Search options
 
Display options
Package fields + +
+ +
+ + + + +<% include('/elements/footer.html') %> +<%init> + +die "access denied" + unless $FS::CurrentUser::CurrentUser->access_right('List packages'); #? + +my $title = 'Broadband Service Report'; +my $routernum = [ $cgi->param('routernum') || '' ]; +$routernum = join(',', @$routernum); + +#false laziness w/report_cust_pkg.html +my $custnum = ''; +if ( $cgi->param('custnum') =~ /^(\d+)$/ ) { + $custnum = $1; + my $cust_main = qsearchs({ + 'table' => 'cust_main', + 'hashref' => { 'custnum' => $custnum }, + 'extra_sql' => ' AND '. $FS::CurrentUser::CurrentUser->agentnums_sql, + }) or die "unknown custnum $custnum"; + $title .= ': '. $cust_main->name; +} + +# exclude one-time charges, disabled packages, and packages with no +# broadband services +my %pkg_search = ( + 'extra_sql' => " +WHERE freq != '0' AND disabled IS NULL AND 0 < ( + SELECT COUNT(*) FROM part_svc JOIN pkg_svc USING ( svcpart ) + WHERE pkg_svc.pkgpart = part_pkg.pkgpart AND part_svc.svcdb = 'svc_broadband' + AND pkg_svc.quantity > 0 +)", +); + + +<%once> + + diff --git a/httemplate/search/svc_broadband.cgi b/httemplate/search/svc_broadband.cgi index d0b102957..7026f52e3 100755 --- a/httemplate/search/svc_broadband.cgi +++ b/httemplate/search/svc_broadband.cgi @@ -1,8 +1,9 @@ <% include( 'elements/search.html', 'title' => 'Broadband Search Results', 'name' => 'broadband services', + 'html_init' => $html_init, 'query' => $sql_query, - 'count_query' => $count_query, + 'count_query' => $sql_query->{'count_query'}, 'redirect' => [ popurl(2). "view/svc_broadband.cgi?", 'svcnum' ], 'header' => [ '#', 'Service', @@ -43,66 +44,29 @@ %> <%init> -die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('List services'); +die "access denied" unless + $FS::CurrentUser::CurrentUser->access_right('List services'); my $conf = new FS::Conf; -my $orderby = 'ORDER BY svcnum'; -my %svc_broadband = (); -my @extra_sql = (); -if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { - - push @extra_sql, 'pkgnum IS NULL' - if $cgi->param('magic') eq 'unlinked'; - - if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { - my $sortby = $1; - $orderby = "ORDER BY $sortby"; +my %search_hash; +if ( $cgi->param('magic') eq 'unlinked' ) { + %search_hash = ( 'unlinked' => 1 ); +} +else { + foreach (qw(custnum agentnum svcpart)) { + $search_hash{$_} = $cgi->param($_) if $cgi->param($_); + } + foreach (qw(pkgpart routernum)) { + $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_); } - -} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) { - push @extra_sql, "svcpart = $1"; -} elsif ( $cgi->param('ip_addr') =~ /^(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})$/ ) { - push @extra_sql, "ip_addr = '$1'"; } -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 ) '; - -push @extra_sql, $FS::CurrentUser::CurrentUser->agentnums_sql( - 'null_right' => 'View/link unlinked services' - ); - -my $extra_sql = ''; -if ( @extra_sql ) { - $extra_sql = ( keys(%svc_broadband) ? ' AND ' : ' WHERE ' ). - join(' AND ', @extra_sql ); +if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + $search_hash{'order_by'} = $1; } -my $count_query = "SELECT COUNT(*) FROM svc_broadband $addl_from "; -#if ( keys %svc_broadband ) { -# $count_query .= ' WHERE '. -# join(' AND ', map "$_ = ". dbh->quote($svc_broadband{$_}), -# keys %svc_broadband -# ); -#} -$count_query .= $extra_sql; - -my $sql_query = { - 'table' => 'svc_broadband', - 'hashref' => {}, #\%svc_broadband, - 'select' => join(', ', - 'svc_broadband.*', - 'part_svc.svc', - 'cust_main.custnum', - FS::UI::Web::cust_sql_fields(), - ), - 'extra_sql' => $extra_sql, - 'addl_from' => $addl_from, -}; +my $sql_query = FS::svc_broadband->search(\%search_hash); my %routerbyblock = (); foreach my $router (qsearch('router', {})) { @@ -120,4 +84,9 @@ my $link_router = sub { my $routernum = $routerbyblock{shift->blocknum}->routern my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ]; +my $html_init = include('/elements/email-link.html', + 'search_hash' => \%search_hash, + 'table' => 'svc_broadband' + ); + -- 2.11.0