add never logged in report

[freeside.git] / httemplate / search / svc_acct.cgi

diff --git a/httemplate/search/svc_acct.cgi b/httemplate/search/svc_acct.cgi
index 292c4a6..a3609a2 100755 (executable)
--- a/httemplate/search/svc_acct.cgi
+++ b/httemplate/search/svc_acct.cgi
@@ -12,6 +12,54 @@
                  'style'       => \@style,
              )
 %>
+<%once>
+
+#false laziness w/ClientAPI/MyAccount.pm
+sub format_time { 
+  my $support = shift;
+  (($support < 0) ? '-' : '' ). int(abs($support)/3600)."h".sprintf("%02d",(abs($support)%3600)/60)."m";
+}
+
+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
+      LEFT JOIN Transactions
+        ON Transactions.Id = acct_rt_transaction.transaction_id
+    WHERE svcnum = ? 
+      AND Transactions.Created >= ?
+  ";
+
+  my $sth = dbh->prepare($sql) or die dbh->errstr;
+  $sth->execute( $svc_acct->svcnum,
+                 time2str('%Y-%m-%d %X', time - $last*86400 ) 
+               )
+    or die $sth->errstr;
+
+  my $seconds = $sth->fetchrow_arrayref->[0];
+
+  my $return = (($seconds < 0) ? '-' : '') . concise(duration($seconds));
+
+  $return .= sprintf(' (%.2fx)', $seconds / $permonth ) if $permonth;
+
+  $return;
+
+}
+
+</%once>
 <%init>
 
 die "access denied"
@@ -29,12 +77,12 @@ my $link_cust = sub {
 
 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 = ( '', '', '', '' );
+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 =
@@ -42,12 +90,14 @@ if ( $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.
-    eidiot "Domain ". $cgi->param('domain'). " not found at all";
+    errorpage("Domain ". $cgi->param('domain'). " not found at all");
   } else {
     push @extra_sql, 'domsvc = '. $svc_domain->svcnum;
   }
 }
 
+my $timepermonth = '';
+
 my $orderby = 'ORDER BY svcnum';
 if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
@@ -56,21 +106,65 @@ if ( $cgi->param('magic') =~ /^(all|unlinked)$/ ) {
 
   my $sortby = '';
   if ( $cgi->param('sortby') =~ /^(\w+)$/ ) {
-    my $sortby = $1;
+    $sortby = $1;
     $sortby = "LOWER($sortby)"
       if $sortby eq 'username';
     push @extra_sql, "$sortby IS NOT NULL"
-      if $sortby eq 'uid' || $sortby eq 'seconds';
+      if $sortby eq 'uid' || $sortby eq 'seconds' || $sortby eq 'last_login';
     $orderby = "ORDER BY $sortby";
   }
 
   if ( $sortby eq 'seconds' ) {
-    push @header, 'Time remaining';
-    push @fields, sub { my $svc_acct = shift; $svc_acct->seconds };
+    #push @header, 'Time remaining';
+    push @header, 'Time';
+    push @fields, sub { my $svc_acct = shift; format_time($svc_acct->seconds) };
     push @links, '';
-    $align .= 'r',
+    $align .= 'r';
     push @color, '';
     push @style, '';
+
+    my $conf = new FS::Conf;
+    if ( $conf->exists('svc_acct-display_paid_time_remaining') ) {
+      push @header, 'Paid time', 'Last 30', 'Last 60', 'Last 90';
+      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;
+          #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);
+          my $balance = $cust_pkg->cust_main->balance;
+          my $months_unpaid = $balance / $recur;
+          my $time_unpaid = $months_unpaid * $timepermonth;
+          format_time($seconds-$time_unpaid).
+            sprintf(' (%.2fx monthly)', ( $seconds-$time_unpaid ) / $timepermonth );
+        },
+        sub { timelast( shift, 30, $timepermonth ); },
+        sub { timelast( shift, 60, $timepermonth ); },
+        sub { timelast( shift, 90, $timepermonth ); },
+      ;
+      push @links, '', '', '', '';
+      $align .= 'rrrr';
+      push @color, '', '', '', '';
+      push @style, '', '', '', '';
+    }
+
+  }
+
+} 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('popnum') =~ /^(\d+)$/ ) {