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
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
? @{ $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;
}
-=item email_search_result HASHREF
-
-(Class method)
-
-Emails a notice to the specified customers.
-
-Valid parameters are those of the L<search> 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
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]';
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
}
+=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<search()> method. Required.
+
+=item msgnum
+
+Message template number (see L<FS::msg_template>). 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
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.
--- /dev/null
+% if ( $FS::CurrentUser::CurrentUser->access_right('Bulk send customer notices') ) {
+<A HREF="<%$p%>misc/email-customers.html?table=<%$table%>&<%$query%>"><%$label%></A>
+% }
+<%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');
+</%init>
+
];
}
- if ( $svcdb eq 'svc_acct' ) {
+ if ( $svcdb eq 'svc_acct' || $svcdb eq 'svc_broadband' ) {
$report_svc{"Advanced $lcsname reports"} =
[ $fsurl."search/report_$svcdb.html", '' ];
}
<% include('/elements/header.html', $title) %>
<FORM NAME="OneTrueForm" ACTION="email-customers.html" METHOD="POST">
-% foreach my $key ( keys %search ) {
-% my @values = ref($search{$key}) ? @{$search{$key}} : ( $search{$key} );
-% foreach my $value ( @values ) {
- <INPUT TYPE="hidden" NAME="<% $key %>" VALUE="<% $value %>">
-% }
-% }
+<INPUT TYPE="hidden" NAME="table" VALUE="<% $table %>">
+%# 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.
+<INPUT TYPE="hidden" NAME="search" VALUE="<% encode_base64(nfreeze(\%search)) %>">
-% if ( $cgi->param('magic') eq 'send' ) {
+% if ( $cgi->param('action') eq 'send' ) {
<FONT SIZE="+2">Sending notice</FONT>
<% 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' ) {
<FONT SIZE="+2">Preview notice</FONT>
% }
-% if ( $cgi->param('magic') ) {
+% if ( $cgi->param('action') ) {
<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+% 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 ),
)
%>
- <INPUT TYPE="hidden" NAME="html_body" VALUE="<% $cgi->param('html_body') |h %>">
+ <INPUT TYPE="hidden" NAME="html_body" VALUE="<% $html_body |h %>">
<TR>
<TD ALIGN="right" VALIGN="top">Message (HTML display): </TD>
- <TD CLASS="background" ALIGN="left"><% $cgi->param('html_body') %></TD>
+ <TD CLASS="background" ALIGN="left"><% $html_body %></TD>
</TR>
% my $text_body = HTML::FormatText->new(leftmargin=>0)->format(
% HTML::TreeBuilder->new_from_content(
-% $cgi->param('html_body')
+% $html_body
% )
% );
<INPUT TYPE="hidden" NAME="text_body" VALUE="<% $text_body |h %>">
<TR>
<TD ALIGN="right" VALIGN="top">Message (Text display): </TD>
- <TD CLASS="background" ALIGN="left"><PRE><% $text_body %></PRE></TD>
+ <TD CLASS="background"
STYLE="background-color:white" ALIGN="left"><PRE><% $text_body %></PRE></TD>
</TR>
</TABLE>
-% if ( $cgi->param('magic') eq 'preview' ) {
+% if ( $cgi->param('action') eq 'preview' ) {
<SCRIPT>
function areyousure(href) {
</SCRIPT>
<BR>
- <INPUT TYPE="hidden" NAME="magic" VALUE="send">
+ <INPUT TYPE="hidden" NAME="action" VALUE="send">
<INPUT TYPE="submit" VALUE="Send notice" onClick="return areyousure()">
% }
% } else {
- <TABLE BGCOLOR="#cccccc" CELLSPACING=0 WIDTH="100%">
+<SCRIPT TYPE="text/javascript">
+function toggle(obj) {
+ document.getElementById('table_no_template').style.display = (obj.value == 0) ? '' : 'none';
+}
+</SCRIPT>
+Template:
+ <% include('/elements/select-table.html',
+ 'label' => 'Template:',
+ 'table' => 'msg_template',
+ 'name_col' => 'msgname',
+ 'empty_label' => '(none)',
+ 'onchange' => 'toggle(this)',
+ )
+ %><BR>
+ <TABLE BGCOLOR="#cccccc" CELLSPACING=0 WIDTH="100%" id="table_no_template">
<% include('/elements/tr-input-text.html',
'field' => 'from',
'label' => 'From:',
%#Substitution vars:
- <BR><BR>
- <INPUT TYPE="hidden" NAME="magic" VALUE="preview">
+ <INPUT TYPE="hidden" NAME="action" VALUE="preview">
<INPUT TYPE="submit" VALUE="Preview notice">
% }
</FORM>
-% if ( $cgi->param('magic') eq 'send' ) {
+% if ( $cgi->param('action') eq 'send' ) {
<SCRIPT TYPE="text/javascript">
process();
</SCRIPT>
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;
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'};
+ }
}
</%init>
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;
</%init>
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",
}
'height' => 210,
). '<BR>';
}
+ $text .= include( '/elements/email-link.html',
+ 'search_hash' => \%search_hash,
+ 'table' => 'cust_pkg',
+ );
}
return $text;
};
--- /dev/null
+<% include('/elements/header.html', $title ) %>
+
+<FORM ACTION="svc_broadband.cgi" METHOD="GET">
+<INPUT TYPE="hidden" NAME="magic" VALUE="advanced">
+<INPUT TYPE="hidden" NAME="custnum" VALUE="<% $custnum %>">
+%# extensive false laziness with svc_acct
+ <TABLE BGCOLOR="#cccccc" CELLSPACING=0>
+
+ <TR>
+ <TH CLASS="background" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Search options</FONT></TH>
+ </TR>
+
+% 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,
+ )
+ %>
+
+ <TR>
+ <TH CLASS="background" COLSPAN=2> </TH>
+ </TR>
+
+ <TR>
+ <TH CLASS="background" COLSPAN=2 ALIGN="left"><FONT SIZE="+1">Display options</FONT></TH>
+ </TR>
+
+% #move to /elements/tr-select-cust_pkg-fields if anything else needs it...
+ <TR>
+ <TD ALIGN="right">Package fields</TD>
+ <TD>
+ <SELECT NAME="cust_pkg_fields">
+ <OPTION VALUE="">(none)
+ <OPTION VALUE="setup,last_bill,bill,cancel">Setup date | Last bill date | Next bill date | Cancel date
+ </SELECT>
+ </TD>
+ </TR>
+
+ <% include( '/elements/tr-select-cust-fields.html' ) %>
+
+ </TABLE>
+
+<BR>
+<INPUT TYPE="submit" VALUE="Get Report">
+
+</FORM>
+
+<% 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
+)",
+);
+
+</%init>
+<%once>
+
+</%once>
<% 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',
%>
<%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', {})) {
my $link_cust = [ $p.'view/cust_main.cgi?', 'custnum' ];
+my $html_init = include('/elements/email-link.html',
+ 'search_hash' => \%search_hash,
+ 'table' => 'svc_broadband'
+ );
+
</%init>
projects / freeside.git / commitdiff