Index: trunk/tools/psstatus
===================================================================
--- trunk/tools/psstatus	(revision 28940)
+++ trunk/tools/psstatus	(revision 30457)
@@ -19,10 +19,11 @@
 my $dbuser;
 my $dbpassword;
-my $use_mysql = 1;
 my $verbose;
+my $finishing;
 
 GetOptions(
     'running|r',     \$running,
     'finished|f',    \$finished,
+    'finishing',     \$finishing,
     'req_faulted',   \$req_faulted,
     'job_faulted',   \$job_faulted,
@@ -32,5 +33,7 @@
 ) or pod2usage (2);
 
-pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
+pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV ;
+
+$running = 1 if (!$finished and !$finishing and !$running);
 
 my $no_args = ! (defined $running or defined $finished or defined $req_faulted or defined $job_faulted );
@@ -42,55 +45,145 @@
 my $dbh = getDBHandle();
 
-my $jobLabel = $running ? "Running jobs" : "Num Jobs";
-my $select = "SELECT req_id, name, label, reqType, pstampRequest.state, pstampRequest.fault, count(job_id) AS '$jobLabel', timestamp";
-
-$select .= ", outdir" if ($verbose);
-my $from = " FROM pstampRequest LEFT JOIN pstampJob USING(req_id)";
-my $where = " WHERE (pstampRequest.state NOT like '%cleaned')";
-
-my $sql = $select . $from . $where;
-
-$limit = 20 if ($no_args);
-# if ($no_args and $limit) {
-if (0 and $limit) {
-    # use mysql to get the last_req_id
-    # this works but it prints out the @last_req_id value which is distracting. Use dbi for the query
-    my $where = "";
-    if ($running) {
-        $where = " WHERE pstampRequest.state = 'run' OR pstampRequest.state = 'new'";
-    } elsif ($finished) {
-        $where = " WHERE pstampRequest.state = 'stop'";
-    }
-    my $stmt = $dbh->prepare("SELECT req_id FROM pstampRequest $where ORDER BY req_id DESC LIMIT 1");
-    $stmt->execute();
-    my $row = $stmt->fetchrow_hashref();
-    die "failed to get last req_id\n"  if !$row;
-    my $last_req_id = $row->{req_id};
-    die "failed to get last req_id\n"  if !$last_req_id;
-
-    my $first_req_id_to_select = $last_req_id - $limit + 1;
-    $sql .= " AND (req_id >= $first_req_id_to_select)";
-}
-if ($running) {
-    $sql .= " AND (pstampRequest.state = 'new' OR pstampRequest.state = 'run') AND (pstampJob.state = 'run' OR pstampJob.state IS NULL)";
-} elsif ($finished) {
-    $sql .= " AND (pstampRequest.state = 'stop') AND date_add(timestamp, interval 1 day) >=utc_timestamp()"
-}
-if ($req_faulted) {
-    $sql .= " AND (pstampRequest.fault > 0)";
-}
-if ($job_faulted) {
-    $sql .= " AND (pstampJob.fault > 0 AND pstampJob.fault < 10)";
-}
-$sql .= " GROUP BY req_id";
-
+my $running_query = "
+SELECT 
+    unfinishedReq.req_id,
+    name,
+    label,
+    reqType
+    state,
+    fault,
+    IFNULL(runningJobs, 0) AS 'Pending Jobs',
+    IFNULL(faultedJobs, 0) AS 'Faulted Jobs',
+    IFNULL(completedJobs,0) AS 'Completed Jobs',
+    timestamp AS 'last state change (UTC)'
+FROM (
+    SELECT
+    req_id,
+    name,
+    label,
+    reqType,
+    pstampRequest.state,
+    pstampRequest.fault,
+    timestamp
+FROM pstampRequest
+    WHERE pstampRequest.state = 'run' or pstampRequest.state ='new'
+) as unfinishedReq
+LEFT JOIN (
+    SELECT req_id,
+        count(job_id) AS runningJobs
+    FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
+        AND pstampJob.state = 'run'
+        AND pstampJob.fault = 0
+    GROUP BY req_id
+) as runningJobs
+ON unfinishedReq.req_id = runningJobs.req_id
+LEFT JOIN (
+    SELECT req_id,
+        count(job_id) AS faultedJobs
+    FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
+        AND pstampJob.state = 'run'
+        AND pstampJob.fault > 0
+    GROUP BY req_id
+) as faultedJobs
+ON unfinishedReq.req_id = faultedJobs.req_id
+LEFT JOIN (
+    SELECT req_id,
+        count(job_id) AS completedJobs
+    FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state ='new')
+        AND pstampJob.state = 'stop'
+    GROUP BY req_id
+) as finishedJobs
+ON unfinishedReq.req_id = finishedJobs.req_id
+
+ORDER by unfinishedReq.req_id
+";
+
+my $finishing_query = "
+SELECT
+    req_id,
+    name,
+    label,
+    reqType,
+    pstampRequest.state,
+    pstampRequest.fault,
+    count(job_id) as 'Finished jobs',
+    timestamp as 'last state change'
+FROM pstampRequest
+    JOIN pstampJob USING(req_id)
+WHERE pstampRequest.state ='run'
+    AND (
+        SELECT count(job_id) FROM pstampJob
+            WHERE pstampJob.req_id = pstampRequest.req_id
+                AND pstampJob.state != 'stop'
+    ) = 0
+GROUP BY req_id
+ORDER by req_id
+";
+
+my $finished_query = "
+SELECT 
+    finishedReq.req_id,
+    name,
+    label,
+    reqType,
+    IFNULL(success, 0) AS 'Successful Jobs',
+    IFNULL(faulted,0) AS 'Faulted Jobs',
+    timestamp AS 'Completion Time (UTC)'
+FROM (
+    SELECT
+    req_id,
+    name,
+    label,
+    reqType,
+--    pstampRequest.state,
+--    pstampRequest.fault,
+    timestamp
+FROM pstampRequest
+    WHERE pstampRequest.state = 'stop'
+        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
+) as finishedReq
+LEFT JOIN (
+    SELECT req_id,
+        count(job_id) AS success
+    FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE pstampRequest.state = 'stop'
+        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
+        AND pstampJob.fault = 0
+    GROUP BY req_id
+) as successfulJobs
+ON finishedReq.req_id = successfulJobs.req_id
+LEFT JOIN (
+    SELECT req_id,
+        count(job_id) AS faulted
+    FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE pstampRequest.state = 'stop'
+        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
+        AND pstampJob.fault > 0
+    GROUP BY req_id
+) as faultedJobs
+ON finishedReq.req_id = faultedJobs.req_id
+ORDER by timestamp desc
+";
+
+
+my $sql;
 if ($finished) {
-    $sql .= " ORDER BY timestamp DESC";
-}
+    $sql = $finished_query;
+} elsif ($running) {
+    $sql = $running_query;
+} elsif ($finishing) {
+    $sql = $finishing_query;
+} else {
+    die "can't happen";
+}
+
 if ($limit and !$no_args) {
     $sql .= " LIMIT $limit";
 }
 
-if ($use_mysql) {
+{
 #    print STDERR "$sql\n";
     my @args = ( "--table", "--user=$dbuser",  "--host=$dbserver", "--password=$dbpassword", $dbname);
@@ -98,47 +191,11 @@
     push @args, '--verbose' if $verbose;
 
-    open my $mysql, "|-", 'mysql', @args or die "failed to run mysql\n";
-    print $mysql "$sql;\n";
-    close $mysql;
-
-} else {
-        # I thought that I wanted custom formatting but in the end I figured I'd just use mysql
-
-        my $stmt = $dbh->prepare($sql);
-        $stmt->execute();
-
-        my $head = "| req_id | name                                                            | label           | reqType   | state | fault  |  numJobs   | outdir";
-        my $head2= "-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------";
-        #           |   5499 | cclin_20100522T011813                                           | WEB.UP          | pstamp    | stop  |      0 |          1 |/data/ippdb02.0/pstamp/work/20100521/5517"
-
-        my $printhead = 1;
-        my $count = 0;
-        while (  my $row = $stmt->fetchrow_hashref() ) {
-            $count++;
-            if ($printhead) {
-                print "$head\n";
-                print "$head2\n";
-                $printhead = 0;
-            }
-            printf "|%7d | %-64s| %-16s| %-10s| %-6s| %6d | %10d | %-s |\n",
-                $row->{req_id}, be_safe($row->{name}), $row->{label}, be_safe($row->{reqType}), $row->{state}, $row->{fault},
-                $row->{numJobs}, $row->{outdir};
-        }
-
-        if (!$count) {
-            print "no requests found\n";
-        } else {
-            print "$head2\n";
-        }
+    open my $mysql_pipe, "|-", 'mysql', @args or die "failed to run mysql\n";
+    print $mysql_pipe "$sql;\n";
+    close $mysql_pipe;
 
 }
 
 exit 0;
-
-sub be_safe {
-    my $str = shift;
-
-    return $str ? $str : 'null';
-}