my $distinct = '';
my $orderby = 'ORDER BY cust_bill._date';
- my $extra_sql = ' WHERE '. FS::cust_bill->search_sql(\%param);
+ my $extra_sql = ' WHERE '. FS::cust_bill->search_sql_where(\%param);
my $addl_from = 'LEFT JOIN cust_main USING ( custnum )';
WHERE cust_bill.invnum = cust_credit_bill.invnum )";
}
-=item search_sql HASHREF
+=item search_sql_where HASHREF
Class method which returns an SQL WHERE fragment to search for parameters
specified in HASHREF. Valid parameters are
=cut
-sub search_sql {
+sub search_sql_where {
my($class, $param) = @_;
if ( $DEBUG ) {
- warn "$me search_sql called with params: \n".
+ warn "$me search_sql_where called with params: \n".
join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n";
}
$self->replace($old);
}
-=item search_sql HASHREF
+=item search_sql_where HASHREF
Class method which returns an SQL WHERE fragment to search for parameters
specified in HASHREF. Valid parameters are
=cut
-sub search_sql {
+sub search_sql_where {
my ($class, $params) = @_;
my @search = ();
sub re_X {
my($method, $param, $job) = @_;
- my $where = FS::cust_bill_event->search_sql($param);
+ my $where = FS::cust_bill_event->search_sql_where($param);
$where = " WHERE plan LIKE 'send%'". ( $where ? " AND $where" : "" );
my $from = 'LEFT JOIN part_bill_event USING ( eventpart )'.
}
-=item search_sql HASHREF
+=item search_sql_where HASHREF
Class method which returns an SQL WHERE fragment to search for parameters
specified in HASHREF. Valid parameters are
#Note: validates all passed-in data; i.e. safe to use with unchecked CGI params.
#sub
-sub search_sql {
+sub search_sql_where {
my($class, $param) = @_;
if ( $DEBUG ) {
- warn "$me search_sql called with params: \n".
+ warn "$me search_sql_where called with params: \n".
join("\n", map { " $_: ". $param->{$_} } keys %$param ). "\n";
}
sub re_X {
my($method, $param, $job) = @_;
- my $search_sql = FS::cust_event->search_sql($param);
+ my $search_sql = FS::cust_event->search_sql_where($param);
#maybe not...? we do want the "re-" action to match the search more closely
# # yuck! hardcoded *AND* sequential scans!
}
-=item search_sql HASHREF
+=item search HASHREF
(Class method)
=cut
-sub search_sql {
+sub search {
my ($class, $params) = @_;
my $dbh = dbh;
}
-=item email_search_sql HASHREF
+=item email_search_result HASHREF
(Class method)
Emails a notice to the specified customers.
-Valid parameters are those of the L<search_sql> method, plus the following:
+Valid parameters are those of the L<search> method, plus the following:
=over 4
=cut
-sub email_search_sql {
+sub email_search_result {
my($class, $params) = @_;
my $from = delete $params->{from};
$params->{'payby'} = [ split(/\0/, $params->{'payby'}) ]
unless ref($params->{'payby'});
- my $sql_query = $class->search_sql($params);
+ my $sql_query = $class->search($params);
my $count_query = delete($sql_query->{'count_query'});
my $count_sth = dbh->prepare($count_query)
use Storable qw(thaw);
use Data::Dumper;
use MIME::Base64;
-sub process_email_search_sql {
+sub process_email_search_result {
my $job = shift;
#warn "$me process_re_X $method for job $job\n" if $DEBUG;
$param->{'payby'} = [ split(/\0/, $param->{'payby'}) ]
unless ref($param->{'payby'});
- my $error = FS::cust_main->email_search_sql( $param );
+ my $error = FS::cust_main->email_search_result( $param );
die $error if $error;
}
#getting complaints searches are not returning enough
unless ( @cust_main && $skip_fuzzy || $conf->exists('disable-fuzzy') ) {
- #still some false laziness w/search_sql (was search/cust_main.cgi)
+ #still some false laziness w/search (was search/cust_main.cgi)
#substring
"cust_pkg.cancel IS NOT NULL AND cust_pkg.cancel != 0";
}
-=item search_sql HASHREF
+=item search HASHREF
(Class method)
=cut
-sub search_sql {
+sub search {
my ($class, $params) = @_;
my @where = ();
if ($access_user) {
push @where, $access_user->agentnums_sql('table'=>'cust_main');
- }else{
+ } else {
push @where, "1=0";
}
- }else{
+ } else {
push @where, $FS::CurrentUser::CurrentUser->agentnums_sql('table'=>'cust_main');
}
use FS::Record qw( qsearch qsearchs fields dbh dbdef );
use FS::Msgcat qw(gettext);
use FS::UI::bytecount;
+use FS::UI::Web;
use FS::part_pkg;
use FS::svc_Common;
use FS::cust_svc;
=back
+=head1 CLASS METHODS
+
+=over 4
+
+=item search HASHREF
+
+Class method which returns a qsearch hash expression to search for parameters
+specified in HASHREF. Valid parameters are
+
+=over 4
+
+=item domain
+
+=item domsvc
+
+=item unlinked
+
+=item agentnum
+
+=item pkgpart
+
+Arrayref of pkgparts
+
+=item pkgpart
+
+=item where
+
+Arrayref of additional WHERE clauses, will be ANDed together.
+
+=item order_by
+
+=item cust_fields
+
+=back
+
+=cut
+
+sub search {
+ my ($class, $params) = @_;
+
+ 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+)$/ and $1 ) {
+ push @where, "agentnum = $1";
+ }
+
+ #custnum
+ if ( $params->{'custnum'} =~ /^(\d+)$/ and $1 ) {
+ push @where, "custnum = $1";
+ }
+
+ #pkgpart
+ if ( $params->{'pkgpart'} && scalar(@{ $params->{'pkgpart'} }) ) {
+ #XXX untaint or sql quote
+ push @where,
+ 'cust_pkg.pkgpart IN ('. join(',', @{ $params->{'pkgpart'} } ). ')';
+ }
+
+ # popnum
+ if ( $params->{'popnum'} =~ /^(\d+)$/ ) {
+ push @where, "popnum = $1";
+ }
+
+ # svcpart
+ if ( $params->{'svcpart'} =~ /^(\d+)$/ ) {
+ push @where, "svcpart = $1";
+ }
+
+
+ # 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 $extra_sql = scalar(@where) ? ' WHERE '. join(' AND ', @where) : '';
+
+ 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 ) ';
+
+ my $count_query = "SELECT COUNT(*) FROM svc_acct $addl_from $extra_sql";
+ #if ( keys %svc_acct ) {
+ # $count_query .= ' WHERE '.
+ # join(' AND ', map "$_ = ". dbh->quote($svc_acct{$_}),
+ # keys %svc_acct
+ # );
+ #}
+
+ my $sql_query = {
+ 'table' => 'svc_acct',
+ 'hashref' => {}, # \%svc_acct,
+ 'select' => join(', ',
+ 'svc_acct.*',
+ 'part_svc.svc',
+ 'cust_main.custnum',
+ 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 SUBROUTINES
=over 4
my $num_cust;
if ( $cgi->param('magic') eq 'preview' ) {
- my $sql_query = FS::cust_main->search_sql(\%search);
+ my $sql_query = FS::cust_main->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;
}
-my $sql_query = FS::cust_pkg->search_sql(\%search_hash);
+my $sql_query = FS::cust_pkg->search(\%search_hash);
$sql_query->{'select'} = 'cust_pkg.pkgnum';
my $error = FS::cust_pkg::bulk_change( [ $cgi->param('new_pkgpart') ],
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_sql', $cgi;
+my $server = new FS::UI::Web::JSRPC 'FS::cust_main::process_email_search_result', $cgi;
</%init>
if $column_option_name{$column};
my $report_option = join(',', @report_option) if @report_option;
- my $sql_query = FS::cust_pkg->search_sql(
+ my $sql_query = FS::cust_pkg->search(
{ %search_hash,
($report_option ? ( 'report_option' => $report_option ) : () ),
}
$count_query = "SELECT COUNT(DISTINCT cust_bill.custnum), 'N/A', 'N/A'";
}
- my $extra_sql = ' WHERE '. FS::cust_bill->search_sql( \%search );
+ my $extra_sql = ' WHERE '. FS::cust_bill->search_sql_where( \%search );
unless ( $count_query ) {
$count_query = 'SELECT COUNT(*), '. join(', ',
$search{invnum} = $1;
}
-my $where = 'WHERE '. FS::cust_bill_event->search_sql( \%search );
+my $where = 'WHERE '. FS::cust_bill_event->search_sql_where( \%search );
my $join = 'LEFT JOIN part_bill_event USING ( eventpart ) '.
'LEFT JOIN cust_bill USING ( invnum ) '.
$search{'beginning'} = $beginning;
$search{'ending'} = $ending;
-my $where = ' WHERE '. FS::cust_event->search_sql( \%search );
+my $where = ' WHERE '. FS::cust_event->search_sql_where( \%search );
my $join = FS::cust_event->join_sql();
# etc
###
-my $sql_query = FS::cust_main->search_sql(\%search_hash);
+my $sql_query = FS::cust_main->search(\%search_hash);
my $count_query = delete($sql_query->{'count_query'});
my @extra_headers = @{ delete($sql_query->{'extra_headers'}) };
my @extra_fields = @{ delete($sql_query->{'extra_fields'}) };
}
-my $sql_query = FS::cust_pkg->search_sql(\%search_hash);
+my $sql_query = FS::cust_pkg->search(\%search_hash);
my $count_query = delete($sql_query->{'count_query'});
my $show = $curuser->default_customer_view =~ /^(jumbo|packages)$/
-<% include('/elements/header.html', 'Account Report' ) %>
+<% include('/elements/header.html', $title ) %>
<FORM ACTION="svc_acct.cgi" METHOD="GET">
<INPUT TYPE="hidden" NAME="magic" VALUE="advanced">
+<INPUT TYPE="hidden" NAME="custnum" VALUE="<% $custnum %>">
<TABLE BGCOLOR="#cccccc" CELLSPACING=0>
<TH BGCOLOR="#e8e8e8" 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,
)
%>
+% # just this customer's domains?
<% include( '/elements/tr-select-domain.html',
'element_name' => 'domsvc',
'curr_value' => scalar( $cgi->param('domsvc') ),
'disable_empty' => 0,
)
%>
-
+% }
<SCRIPT type="text/javascript">
function toggle(what) {
<%init>
die "access denied"
- unless $FS::CurrentUser::CurrentUser->access_right('List packages');
+ unless $FS::CurrentUser::CurrentUser->access_right('List packages'); #?
+
+my $title = 'Account Report';
+
+#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;
+}
</%init>
<%once>
}
};
+my %search_hash = ();
my @extra_sql = ();
my @header = ( '#', 'Service', 'Account', 'UID', 'Last Login' );
my @color = ( '', '', '', '', '' );
my @style = ( '', '', '', '', '' );
-if ( $cgi->param('domain') ) {
- my $svc_domain =
- qsearchs('svc_domain', { 'domain' => $cgi->param('domain') } );
- unless ( $svc_domain ) {
- #it would be nice if this looked more like the other "not found"
- #errors, but this will do for now.
- errorpage("Domain ". $cgi->param('domain'). " not found at all");
- } else {
- push @extra_sql, 'domsvc = '. $svc_domain->svcnum;
- }
-}
-if ( $cgi->param('domsvc') =~ /^(\d+)$/ ) {
- push @extra_sql, "domsvc = $1";
+for (qw( domain domsvc agentnum custnum popnum svcpart cust_fields )) {
+ $search_hash{$_} = $cgi->param($_) if length($cgi->param($_));
}
my $timepermonth = '';
my $orderby = 'ORDER BY svcnum';
if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
- push @extra_sql, 'pkgnum IS NULL'
+ $search_hash{'unlinked'} = 1
if $cgi->param('magic') eq 'unlinked';
my $sortby = '';
$sortby = $1;
$sortby = "LOWER($sortby)"
if $sortby eq 'username';
- push @extra_sql, "$sortby IS NOT NULL"
+ push @extra_sql, "$sortby IS NOT NULL" #XXX search_hash
if $sortby eq 'uid' || $sortby eq 'seconds' || $sortby eq 'last_login';
$orderby = "ORDER BY $sortby";
}
}
} elsif ( $cgi->param('magic') =~ /^advanced$/ ) {
+
$orderby = "";
- if ( $cgi->param('agentnum') =~ /^(\d+)$/ and $1 ) {
- push @extra_sql, "agentnum = $1";
- }
+ $search_hash{'pkgpart'} = [ $cgi->param('pkgpart') ];
- my $pkgpart = join (' OR cust_pkg.pkgpart=',
- grep {$_} map { /^(\d+)$/; } ($cgi->param('pkgpart')));
- push @extra_sql, '(cust_pkg.pkgpart=' . $pkgpart . ')' if $pkgpart;
-
foreach my $field (qw( last_login last_logout )) {
my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field);
$orderby ||= "ORDER BY svcnum";
-} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) {
- push @extra_sql, "popnum = $1";
+} elsif ( $cgi->param('popnum') ) {
$orderby = "ORDER BY LOWER(username)";
-} elsif ( $cgi->param('svcpart') =~ /^(\d+)$/ ) {
- push @extra_sql, "svcpart = $1";
+} elsif ( $cgi->param('svcpart') ) {
$orderby = "ORDER BY uid";
#$orderby = "ORDER BY svcnum";
} else {
push @color, FS::UI::Web::cust_colors();
push @style, FS::UI::Web::cust_styles();
-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'
- );
-
-my $extra_sql =
- scalar(@extra_sql)
- ? ' WHERE '. join(' AND ', @extra_sql )
- : '';
-
-my $count_query = "SELECT COUNT(*) FROM svc_acct $addl_from $extra_sql";
-#if ( keys %svc_acct ) {
-# $count_query .= ' WHERE '.
-# join(' AND ', map "$_ = ". dbh->quote($svc_acct{$_}),
-# keys %svc_acct
-# );
-#}
-
-my $sql_query = {
- 'table' => 'svc_acct',
- 'hashref' => {}, # \%svc_acct,
- 'select' => join(', ',
- 'svc_acct.*',
- 'part_svc.svc',
- 'cust_main.custnum',
- FS::UI::Web::cust_sql_fields(),
- ),
- 'extra_sql' => "$extra_sql $orderby",
- 'addl_from' => $addl_from,
-};
+$search_hash{'order_by'} = $orderby;
+$search_hash{'where'} = \@extra_sql;
+
+my $sql_query = FS::svc_acct->search(\%search_hash);
+my $count_query = delete($sql_query->{'count_query'});
</%init>
</TD>
<TD ALIGN="right">
- <A HREF="<%$p%>search/report_cust_pkg.html?custnum=<% $cust_main->custnum %>">Package reports</A>
+ <A HREF="<%$p%>search/report_cust_pkg.html?custnum=<% $cust_main->custnum %>">Package reports</A><BR>
+ Service reports:
+ <A HREF="<%$p%>search/report_svc_acct.html?custnum=<% $cust_main->custnum %>">accounts</A>
</TD>
</TR>
projects / freeside.git / commitdiff