X-Git-Url: http://git.freeside.biz/gitweb/?a=blobdiff_plain;f=httemplate%2Fsearch%2Fsvc_acct.cgi;h=e2abf5625fb348c37a8ba0db27771f51bdf9bd58;hb=63a268637b2d51a8766412617724b9436439deb6;hp=2c42ef039e03f914aa9cdae457fee8f8db96e729;hpb=d59891eee0e9d8b0bb32b48c554a335b42cb9bd4;p=freeside.git diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi index 2c42ef039..e2abf5625 100755 --- a/httemplate/search/svc_acct.cgi +++ b/httemplate/search/svc_acct.cgi @@ -23,20 +23,8 @@ sub format_time { sub timelast { my( $svc_acct, $last, $permonth ) = @_; - #some false laziness w/search/rt_transaction.html - - my $transactiontime = " - CASE transactions.type when 'Set' - THEN ( to_number(newvalue, '999999') - - to_number(oldvalue, '999999') - ) * 60 - ELSE timetaken*60 - END - "; - - #Transactions my $sql = " - SELECT SUM($transactiontime) FROM acct_rt_transaction + SELECT SUM(support) FROM acct_rt_transaction LEFT JOIN Transactions ON Transactions.Id = acct_rt_transaction.transaction_id WHERE svcnum = ? @@ -51,7 +39,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; @@ -75,25 +64,18 @@ my $link_cust = sub { } }; +my %search_hash = (); my @extra_sql = (); -my @header = ( '#', 'Service', 'Account', 'UID' ); -my @fields = ( 'svcnum', 'svc', 'email', 'uid' ); -my @links = ( $link, $link, $link, $link ); -my $align = 'rlll'; -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', '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 = ( '', '', '', '', '' ); + +for (qw( domain domsvc agentnum custnum popnum svcpart cust_fields )) { + $search_hash{$_} = $cgi->param($_) if length($cgi->param($_)); } my $timepermonth = ''; @@ -101,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 = ''; @@ -109,8 +91,8 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { $sortby = $1; $sortby = "LOWER($sortby)" if $sortby eq 'username'; - push @extra_sql, "$sortby IS NOT NULL" - if $sortby eq 'uid' || $sortby eq 'seconds'; + 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"; } @@ -132,16 +114,22 @@ 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; format_time($seconds-$time_unpaid). sprintf(' (%.2fx monthly)', ( $seconds-$time_unpaid ) / $timepermonth ); }, @@ -157,11 +145,50 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } -} elsif ( $cgi->param('popnum') =~ /^(\d+)$/ ) { - push @extra_sql, "popnum = $1"; +} elsif ( $cgi->param('magic') =~ /^nologin$/ ) { + + if ( $cgi->param('sortby') =~ /^(\w+)$/ ) { + my $sortby = $1; + $sortby = "LOWER($sortby)" + if $sortby eq 'username'; + push @extra_sql, "last_login IS NULL"; + $orderby = "ORDER BY $sortby"; + } + +} elsif ( $cgi->param('magic') =~ /^advanced$/ ) { + + $orderby = ""; + + $search_hash{'pkgpart'} = [ $cgi->param('pkgpart') ]; + + foreach my $field (qw( last_login last_logout )) { + + my($beginning, $ending) = FS::UI::Web::parse_beginning_ending($cgi, $field); + + next if $beginning == 0 && $ending == 4294967295; + + if ($cgi->param($field."_invert")) { + push @extra_sql, + "(svc_acct.$field IS NULL OR ". + "svc_acct.$field < $beginning AND ". + "svc_acct.$field > $ending)"; + } else { + push @extra_sql, + "svc_acct.$field IS NOT NULL", + "svc_acct.$field >= $beginning", + "svc_acct.$field <= $ending"; + } + + $orderby ||= "ORDER BY svc_acct.$field" . + ($cgi->param($field."_invert") ? ' DESC' : ''); + + } + + $orderby ||= "ORDER BY svcnum"; + +} 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 { @@ -208,48 +235,18 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) { } -push @header, FS::UI::Web::cust_header(); +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 : '' } - FS::UI::Web::cust_header(); + FS::UI::Web::cust_header($cgi->param('cust_fields')); $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'});