X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=httemplate%2Fsearch%2Fsvc_acct.cgi;h=7f423d521e6a7ed0581e2979df6d48dd036807a4;hp=d8451f2100df2da0ebb1cbead2ac8d752788c0d5;hb=4639e25a658d9a0bf295415642fae8e8cdad846a;hpb=3502f19e6371bee6d974f05b2d3f22f8ccd03891 diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index d8451f210..7f423d521 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -1,6 +1,6 @@ -<% include( 'elements/search.html', - 'title' => 'Account Search Results', - 'name' => 'accounts', +<& elements/svc_Common.html, + 'title' => emt('Account Search Results'), + 'name' => emt('accounts'), 'query' => $sql_query, 'count_query' => $count_query, 'redirect' => $link, @@ -10,8 +10,12 @@ 'align' => $align, 'color' => \@color, 'style' => \@style, - ) -%> + 'footer' => \@footer, + 'html_init' => include('/elements/email-link.html', + 'search_hash' => \%search_hash, + 'table' => 'svc_acct' + ), +&> <%once> #false laziness w/ClientAPI/MyAccount.pm @@ -39,7 +43,8 @@ sub timelast { my $seconds = $sth->fetchrow_arrayref->[0]; - my $return = (($seconds < 0) ? '-' : '') . concise(duration($seconds)); + #my $return = (($seconds < 0) ? '-' : '') . concise(duration($seconds)); + my $return = (($seconds < 0) ? '-' : '') . format_time($seconds); $return .= sprintf(' (%.2fx)', $seconds / $permonth ) if $permonth; @@ -50,8 +55,11 @@ sub timelast { <%init> -die "access denied" - unless $FS::CurrentUser::CurrentUser->access_right('List services'); +my $curuser = $FS::CurrentUser::CurrentUser; + +die "access denied" unless $curuser->access_right('List services'); + +my %cust_pkg_cache; my $link = [ "${p}view/svc_acct.cgi?", 'svcnum' ]; my $link_cust = sub { @@ -63,33 +71,71 @@ my $link_cust = sub { } }; +my %search_hash = (); my @extra_sql = (); -my @header = ( '#', 'Service', 'Account', 'UID', 'Last Login' ); -my @fields = ( 'svcnum', 'svc', 'email', 'uid', 'last_login_text' ); -my @links = ( $link, $link, $link, $link, $link ); -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; - } +my @header = ( 'Service', 'Account' ); +my @fields = ( 'svc', 'email' ); +my @links = ( $link, $link ); +my $align = 'll'; +my @color = ( '', '' ); +my @style = ( '', '' ); +my @footer = (); + +my $conf = new FS::Conf; + +if ( $conf->exists('report-showpasswords') #its a terrible idea + && $curuser->access_right('List service passwords') #but if you insist... + ) +{ + push @header, emt('Password'); + push @fields, 'get_cleartext_password'; + push @links, $link; + $align .= 'l'; + push @color, ''; + push @style, ''; +} + +push @header, emt('Real Name'); +push @fields, 'finger'; +push @links, $link; +$align .= 'l'; +push @color, ''; +push @style, ''; + +#hide the UID, its much less useful these days +if ( $cgi->param('show_uid') ) { #XXX add a checkbox + push @header, emt('UID'); + push @fields, 'uid'; + push @links, $link; + $align .= 'l'; + push @color, ''; + push @style, ''; +} + +push @header, emt('Last Login'); +push @fields, 'last_login_text'; +push @links, $link; +$align .= 'r'; +push @color, ''; +push @style, ''; + + +for (qw( domain domsvc agentnum custnum popnum svcpart cust_fields )) { + $search_hash{$_} = $cgi->param($_) if length($cgi->param($_)); +} + +for (qw( towernum sectornum )) { + $search_hash{$_} = [ $cgi->param($_) ] if $cgi->param($_); } my $timepermonth = ''; my $orderby = 'ORDER BY svcnum'; +my $addl_from = ''; if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { - push @extra_sql, 'pkgnum IS NULL' + $search_hash{'unlinked'} = 1 if $cgi->param('magic') eq 'unlinked'; my $sortby = ''; @@ -97,23 +143,55 @@ 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"; } if ( $sortby eq 'seconds' ) { - #push @header, 'Time remaining'; - push @header, 'Time'; - push @fields, sub { my $svc_acct = shift; format_time($svc_acct->seconds) }; + my $tot_time = 0; + push @header, emt('Time'); + + if ( $conf->exists('svc_acct-display_paid_time_remaining') ) { + push @fields, sub { my $svc_acct = shift; + my $seconds = $svc_acct->seconds; + my $cust_pkg = $svc_acct->cust_svc->cust_pkg; + my $part_pkg = $cust_pkg->part_pkg; + + $tot_time += $svc_acct->seconds; + + $timepermonth = $part_pkg->option('seconds'); + $timepermonth = $timepermonth / $part_pkg->freq + if $part_pkg->freq =~ /^\d+$/ && $part_pkg->freq != 0; + my $recur = $part_pkg->base_recur($cust_pkg); + + return format_time($seconds) + unless $timepermonth && $recur; + + format_time($seconds). + sprintf(' (%.2fx monthly)', $seconds / $timepermonth ); + + }; + } else { + push @fields, sub { my $svc_acct = shift; + $tot_time += $svc_acct->seconds; + format_time($svc_acct->seconds); + }; + } + push @links, ''; $align .= 'r'; push @color, ''; push @style, ''; - my $conf = new FS::Conf; + @footer = ( 'Total', '', '', '', + sub { format_time($tot_time) }, #time + ); + if ( $conf->exists('svc_acct-display_paid_time_remaining') ) { - push @header, 'Paid time', 'Last 30', 'Last 60', 'Last 90'; + my $tot_paid_time = 0; + my %tot = ( '30'=>0, '60'=>0, '90'=>0 ); + push @header, emt('Paid time'), emt('Last 30'), emt('Last 60'), emt('Last 90'); push @fields, sub { my $svc_acct = shift; @@ -132,8 +210,11 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { return format_time($seconds) unless $timepermonth && $recur; my $balance = $cust_pkg->cust_main->balance; - my $months_unpaid = $balance / $recur; - my $time_unpaid = $months_unpaid * $timepermonth; + my $periods_unpaid = $balance / $recur; + my $time_unpaid = $periods_unpaid * $timepermonth; + $time_unpaid *= $part_pkg->freq + if $part_pkg->freq =~ /^\d+$/ && $part_pkg->freq != 0; + $tot_paid_time += $seconds-$time_unpaid; format_time($seconds-$time_unpaid). sprintf(' (%.2fx monthly)', ( $seconds-$time_unpaid ) / $timepermonth ); }, @@ -145,8 +226,16 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { $align .= 'rrrr'; push @color, '', '', '', ''; push @style, '', '', '', ''; + push @footer, + sub { format_time($tot_paid_time) }, #paid time + '', #XXX sub { $tot{'30'} }, #30 + '', #XXX sub { $tot{'60'} }, #60 + '', #XXX sub { $tot{'90'} }, #90 + ; } + push @footer, '', ''; + } } elsif ( $cgi->param('magic') =~ /^nologin$/ ) { @@ -160,16 +249,13 @@ 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') ]; + + $search_hash{'svcpart'} = [ $cgi->param('svcpart') ]; - 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); @@ -195,13 +281,14 @@ 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"; + if ( defined($cgi->param('cancelled')) ) { + $search_hash{'cancelled'} = $cgi->param('cancelled') ? 1 : 0; + } } else { $orderby = "ORDER BY uid"; @@ -213,13 +300,13 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } $cgi->param('username') =~ /^([\w\-\.\&]+)$/; #untaint username_text - my $username = $1; + my $username = lc($1); - push @username_sql, "username ILIKE '$username'" + push @username_sql, "LOWER(username) LIKE '$username'" if $username_type{'Exact'} || $username_type{'Fuzzy'}; - push @username_sql, "username ILIKE '\%$username\%'" + push @username_sql, "LOWER(username) LIKE '\%$username\%'" if $username_type{'Substring'} || $username_type{'All'}; @@ -246,6 +333,44 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } +my $date_format = $conf->config('date_format') || '%m/%d/%Y'; + +$cgi->param('cust_pkg_fields') =~ /^([\w\,]*)$/ or die "bad cust_pkg_fields"; +my @pkg_fields = split(',', $1); +foreach my $pkg_field ( @pkg_fields ) { + ( my $header = ucfirst($pkg_field) ) =~ s/_/ /; #:/ + push @header, $header; + + #not the most efficient to do it every field, but this is of niche use. so far + push @fields, sub { my $svc_acct = shift; + my $cust_pkg = $svc_acct->cust_svc->cust_pkg or return ''; + my $value = $cust_pkg->get($pkg_field);#closures help alot + $value ? time2str('%b %d %Y', $value ) : ''; + }; + + push @links, ''; + $align .= 'c'; + push @color, ''; + push @style, ''; + +} + +push @header, emt('Pkg. Status'); +push @fields, sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + return '' unless $cust_pkg_cache{$_[0]->svcnum}; + $cust_pkg_cache{$_[0]->svcnum}->ucfirst_status; +}; +push @links, ''; +$align .= 'r'; +push @color, sub { + $cust_pkg_cache{$_[0]->svcnum} ||= $_[0]->cust_svc->cust_pkg; + return '' unless $cust_pkg_cache{$_[0]->svcnum}; + my $c = FS::cust_pkg::statuscolors; + $c->{$cust_pkg_cache{$_[0]->svcnum}->status }; +}; +push @style, 'b'; + push @header, FS::UI::Web::cust_header($cgi->param('cust_fields')); push @fields, \&FS::UI::Web::cust_fields, push @links, map { $_ ne 'Cust. Status' ? $link_cust : '' } @@ -254,40 +379,11 @@ $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; +$search_hash{'addl_from'} = $addl_from; + +my $sql_query = FS::svc_acct->search(\%search_hash); +my $count_query = delete($sql_query->{'count_query'});