Index: trunk/tools/psstatus
===================================================================
--- trunk/tools/psstatus	(revision 28355)
+++ trunk/tools/psstatus	(revision 28355)
@@ -0,0 +1,145 @@
+#!/bin/env perl
+
+use strict;
+use warnings;
+
+use DBI;
+use PS::IPP::Config 1.01 qw( :standard );
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+use File::Temp qw(tempfile);
+
+my $limit = 20;
+my $running;
+my $req_faulted;
+my $job_faulted;
+my $dbname;
+my $dbserver;
+my $dbuser;
+my $dbpassword;
+my $use_mysql = 1;
+my $verbose;
+
+GetOptions(
+    'running|r',     \$running,
+    'req_faulted',   \$req_faulted,
+    'job_faulted',   \$job_faulted,
+    'limit|l=i',     \$limit,
+    'dbname=s',      \$dbname,
+    'verbose|v',     \$verbose,
+) or pod2usage (2);
+
+pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
+
+my $no_args = ! (defined $running or defined $req_faulted or defined $job_faulted );
+
+my $ipprc =  PS::IPP::Config->new();
+my $dbh = getDBHandle();
+
+my $select = "SELECT req_id, name, label, reqType, pstampRequest.state, pstampRequest.fault, count(job_id) AS numJobs, 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;
+
+if ($no_args 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 $stmt = $dbh->prepare("SELECT req_id FROM pstampRequest 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')"
+}
+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";
+
+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);
+
+    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";
+        }
+
+}
+
+exit 0;
+
+sub be_safe {
+    my $str = shift;
+
+    return $str ? $str : 'null';
+}
+
+
+sub getDBHandle {
+    $dbserver = metadataLookupStr($ipprc->{_siteConfig}, "PS_DBSERVER");
+    $dbuser = metadataLookupStr($ipprc->{_siteConfig}, "DS_DBUSER");
+    $dbpassword = metadataLookupStr($ipprc->{_siteConfig}, "DS_DBPASSWORD");
+    if (!$dbname) {
+        $dbname = metadataLookupStr($ipprc->{_siteConfig}, "PS_DBNAME");
+    }
+
+    die "database configuration set up" unless defined($dbserver) and defined($dbuser)
+        and defined($dbpassword) and defined($dbname);
+
+    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
+
+    my $dbh = DBI->connect($dsn, $dbuser, $dbpassword) 
+        or die "Cannot connect to database.\n";
+
+    return $dbh;
+}
+
+