X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fsvc_acct.cgi;h=74cdb9918c725b2b8f7c104aac8f74d3c4493926;hb=37591b61f8ad6fc5c5acb2fd8f520d2dbe4f1539;hp=c2b3d8fa071e8dbf2b5f1787b57d2623d6d5f568;hpb=1853ca72ecbd2d47bc09d196374534ab8ada08ce;p=freeside.git diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index c2b3d8fa0..74cdb9918 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -10,6 +10,7 @@ 'align' => $align, 'color' => \@color, 'style' => \@style, + 'footer' => \@footer, ) %> <%once> @@ -39,7 +40,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; @@ -63,6 +65,7 @@ my $link_cust = sub { } }; +my %search_hash = (); my @extra_sql = (); my @header = ( '#', 'Service', 'Account', 'UID', 'Last Login' ); @@ -71,17 +74,10 @@ my @links = ( $link, $link, $link, $link, $link ); my $align = 'rlllr'; my @color = ( '', '', '', '', '' ); my @style = ( '', '', '', '', '' ); +my @footer = (); -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; - } +for (qw( domain domsvc agentnum custnum popnum svcpart cust_fields )) { + $search_hash{$_} = $cgi->param($_) if length($cgi->param($_)); } my $timepermonth = ''; @@ -89,7 +85,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 = ''; @@ -97,22 +93,32 @@ 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' ) { + my $tot_time = 0; #push @header, 'Time remaining'; push @header, 'Time'; - push @fields, sub { my $svc_acct = shift; format_time($svc_acct->seconds) }; + 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, ''; + @footer = ( '', 'Total', '', '', '', + sub { format_time($tot_time) }, #time + ); + my $conf = new FS::Conf; if ( $conf->exists('svc_acct-display_paid_time_remaining') ) { + my $tot_paid_time = 0; + my %tot = ( '30'=>0, '60'=>0, '90'=>0 ); push @header, 'Paid time', 'Last 30', 'Last 60', 'Last 90'; push @fields, sub { @@ -120,16 +126,23 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { my $seconds = $svc_acct->seconds; my $cust_pkg = $svc_acct->cust_svc->cust_pkg; my $part_pkg = $cust_pkg->part_pkg; + #my $timepermonth = $part_pkg->option('seconds'); $timepermonth = $part_pkg->option('seconds'); $timepermonth = $timepermonth / $part_pkg->freq if $part_pkg->freq =~ /^\d+$/ && $part_pkg->freq != 0; - return format_time($seconds) unless $timepermonth; + #my $recur = $part_pkg->calc_recur($cust_pkg); my $recur = $part_pkg->base_recur($cust_pkg); + + 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 ); }, @@ -141,6 +154,12 @@ 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 + ; } } @@ -156,16 +175,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); @@ -191,11 +205,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 { @@ -250,40 +262,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'});