From d32d0810e0beba37f3c5b13d4b99d883da5080c0 Mon Sep 17 00:00:00 2001 From: ivan Date: Fri, 4 Dec 2009 04:45:42 +0000 Subject: [PATCH] customer-specific account report (and some small refactoring of method names to clash less), RT#6180 --- FS/FS/svc_acct.pm | 139 +++++++++++++++++++++++++++++++++++++++++ httemplate/search/svc_acct.cgi | 75 +++++----------------- 2 files changed, 153 insertions(+), 61 deletions(-) diff --git a/FS/FS/svc_acct.pm b/FS/FS/svc_acct.pm index 3af41bac6..efe66505e 100644 --- a/FS/FS/svc_acct.pm +++ b/FS/FS/svc_acct.pm @@ -30,6 +30,7 @@ use FS::Conf; 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; @@ -2607,6 +2608,144 @@ sub virtual_maildir { =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 diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 7ddb3f000..e2abf5625 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -64,6 +64,7 @@ my $link_cust = sub { } }; +my %search_hash = (); my @extra_sql = (); my @header = ( '#', 'Service', 'Account', 'UID', 'Last Login' ); @@ -73,19 +74,8 @@ my $align = 'rlllr'; 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 = ''; @@ -93,7 +83,7 @@ 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 = ''; @@ -101,7 +91,7 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { $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"; } @@ -166,16 +156,11 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } } 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); @@ -201,11 +186,9 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { $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 { @@ -260,40 +243,10 @@ $align .= FS::UI::Web::cust_aligns(); 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'}); -- 2.11.0