Index: /tags/ipp-20101215/ippTasks/pstamp.pro
===================================================================
--- /tags/ipp-20101215/ippTasks/pstamp.pro	(revision 30483)
+++ /tags/ipp-20101215/ippTasks/pstamp.pro	(revision 30484)
@@ -905,2 +905,35 @@
     end
 end
+
+task pstamp.save.status
+    host        local
+
+    periods     -poll $LOADPOLL
+    periods     -exec 45
+    periods     -timeout 20
+    npending    1
+
+    task.exec
+        stdout NULL
+        stderr $LOGSUBDIR/pstamp.save.status.log
+
+        $run = pstamp_save_server_status.pl --update-link
+        command $run
+    end
+
+    task.exit $EXIT_SUCCESS
+        # echo nothing to do
+    end
+
+    task.exit   default
+        showcommand failure
+    end
+
+    task.exit   crash
+        showcommand crash
+    end
+
+    task.exit   timeout
+        showcommand timeout
+    end
+end
Index: /tags/ipp-20101215/pstamp/scripts/Makefile.am
===================================================================
--- /tags/ipp-20101215/pstamp/scripts/Makefile.am	(revision 30483)
+++ /tags/ipp-20101215/pstamp/scripts/Makefile.am	(revision 30484)
@@ -17,7 +17,9 @@
 	pstamp_runcommand.sh \
 	pstamp_server_status \
+	pstamp_save_server_status.pl \
 	pstamp_webrequest.pl \
         pstamp_get_image_job.pl \
 	psmkreq \
+	psstatus \
 	pstampstopfaulted \
 	pstamp_checkdependent.pl \
Index: /tags/ipp-20101215/pstamp/scripts/psstatus
===================================================================
--- /tags/ipp-20101215/pstamp/scripts/psstatus	(revision 30484)
+++ /tags/ipp-20101215/pstamp/scripts/psstatus	(revision 30484)
@@ -0,0 +1,283 @@
+#!/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 = 0;
+my $running;
+my $finished;
+my $req_faulted;
+my $job_faulted;
+my $dbname;
+my $dbserver;
+my $dbuser;
+my $dbpassword;
+my $verbose;
+my $finishing;
+
+GetOptions(
+    'running|r',     \$running,
+    'finished|f',    \$finished,
+    'finishing',     \$finishing,
+    '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 ;
+
+$running = 1 if (!$finished and !$finishing and !$running);
+
+my $no_args = ! (defined $running or defined $finished or defined $req_faulted or defined $job_faulted );
+
+die "cannot supply --running and --finished\n" if ($running and $finished);
+    
+
+my $ipprc =  PS::IPP::Config->new();
+my $dbh = getDBHandle();
+
+my $running_query = "
+SELECT 
+    unfinishedReq.req_id,
+    name,
+    label,
+    reqType,
+    priority,
+    state,
+    fault,
+    rowcount AS 'Num Rows',
+    jobcount AS 'Total Jobs',
+    IFNULL(completedJobs,0) AS 'Completed Jobs',
+    IFNULL(runningJobs, 0) AS 'Pending Jobs',
+    IFNULL(faultedJobs, 0) AS 'Faulted Jobs',
+    IFNULL(depcount, 0) AS 'Image updates'
+    , timestamp AS 'last state change (UTC)'
+FROM (
+    SELECT
+        req_id,
+        name,
+        label,
+        reqType,
+        pstampRequest.state,
+        pstampRequest.fault,
+        timestamp,
+        priority
+FROM pstampRequest
+JOIN Label USING(label)
+    WHERE pstampRequest.state = 'run' or pstampRequest.state ='new' 
+   --     OR pstampRequest.state = 'run.wait'
+) as unfinishedReq
+LEFT JOIN (
+    SELECT req_id,
+        count(job_id)          AS jobcount,
+        count(distinct rownum) AS rowcount
+FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state = 'new' 
+        OR pstampRequest.state = 'run.wait')
+    GROUP BY req_id
+) as rowCounts
+ON unfinishedReq.req_id = rowCounts.req_id
+
+LEFT JOIN (
+    SELECT req_id,
+        count(distinct dep_id) AS depcount
+FROM pstampRequest
+    JOIN pstampJob USING(req_id)
+    JOIN pstampDependent USING(dep_id) 
+    WHERE (pstampRequest.state = 'run' or pstampRequest.state = 'new' 
+            OR pstampRequest.state = 'run.wait')
+        AND dep_id > 0
+        AND pstampDependent.state = 'full'
+    GROUP BY req_id
+) as depCounts
+ON unfinishedReq.req_id = depCounts.req_id
+
+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' 
+        OR pstampRequest.state = 'run.wait')
+        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'
+        OR pstampRequest.state = 'run.wait')
+        -- 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'
+        OR pstampRequest.state = 'run.wait')
+        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,
+    rowcount AS 'Num Rows',
+    jobcount AS 'Total Jobs',
+    IFNULL(success, 0) AS 'Successful Jobs',
+    IFNULL(faulted,0) AS 'Faulted Jobs',
+    IFNULL(depcount, 0) AS 'Image updates completed',
+    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 jobcount,
+        count(distinct rownum) AS rowcount
+FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE pstampRequest.state = 'stop'
+        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
+    GROUP BY req_id
+) as rowCounts
+ON finishedReq.req_id = rowCounts.req_id
+
+LEFT JOIN (
+    SELECT req_id,
+        count(distinct dep_id) AS depcount
+FROM pstampRequest JOIN pstampJob USING(req_id)
+    WHERE pstampRequest.state = 'stop'
+        AND date_add(timestamp, interval 1 day) >=utc_timestamp()
+        AND dep_id > 0
+    GROUP BY req_id
+) as depCounts
+ON finishedReq.req_id = depCounts.req_id
+
+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 = $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";
+}
+
+{
+    print STDERR "$sql\n" if $verbose;
+    my @args = ( "--table", "--user=$dbuser",  "--host=$dbserver", "--password=$dbpassword", $dbname);
+
+    push @args, '--verbose' if $verbose;
+
+    open my $mysql_pipe, "|-", 'mysql', @args or die "failed to run mysql\n";
+    print $mysql_pipe "$sql;\n";
+    close $mysql_pipe;
+
+}
+
+exit 0;
+
+
+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;
+}
+
+
Index: /tags/ipp-20101215/pstamp/scripts/pstamp_save_server_status.pl
===================================================================
--- /tags/ipp-20101215/pstamp/scripts/pstamp_save_server_status.pl	(revision 30484)
+++ /tags/ipp-20101215/pstamp/scripts/pstamp_save_server_status.pl	(revision 30484)
@@ -0,0 +1,74 @@
+#!/bin/env perl
+#
+# run pstamp_server_status and save the results in a date stamped file
+# optionally change the symlink in the web directory to point to the new file
+
+use strict;
+use warnings;
+
+use PS::IPP::Config 1.01 qw( :standard );
+use IPC::Cmd 0.36 qw( can_run run );
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+
+# XXX: We should get this from site.config, but that would about double
+# the execution time for this script.
+my $rundir = "/data/ippc17.0/pstamp";
+
+my $status_file = "$rundir/web/status.html";
+
+my $updatelink;
+my $verbose;
+my $save_temps;
+
+GetOptions(
+    'rundir=s'      => \$rundir,
+    'update-link'    => \$updatelink,
+    'verbose'       => \$verbose,
+    'save-temps'    => \$save_temps,
+) or pod2usage( 2 );
+
+my $missing_tools;
+my $pstamp_server_status   = can_run('pstamp_server_status') 
+    or (warn "Can't find pstamp_server_status" and $missing_tools = 1);
+if ($missing_tools) {
+    warn("Can't find required tools.");
+    exit($PS_EXIT_CONFIG_ERROR);
+}
+
+my ($sec, $min, $hour, $mday, $month, $year) = gmtime;
+$year += 1900;
+$month += 1;
+
+my $dir = sprintf "$rundir/work/server_status/%4d/%02d/%02d", $year, $month, $mday;
+
+if (!-e $dir ) {
+    my $rc = system "mkdir -p $dir";
+    if ($rc) {
+        my $status = $rc >> 8;
+        print STDERR "mkdir failed error: $status\n";
+        exit $status;
+    }
+}
+
+my $file = sprintf "$dir/pstamp.status.%4d-%02d-%02dT%02d:%02d:%02d.html",
+    $year, $month, $mday, $hour, $min, $sec;
+
+my $command = "pstamp_server_status > $file";
+my  ( $success, $error_code, $full_buf, $stdout_buf, $stderr_buf ) =
+    run(command => $command, verbose => $verbose);
+unless ($success) {
+    $error_code = (($error_code >> 8) or 1);
+    warn("$command failed. exit status: $error_code");
+    exit $error_code;
+}
+
+if ($updatelink) {
+    if (-e $status_file) {
+        unlink $status_file or die "failed to unlink existing status file $status_file\n";
+    }
+
+    symlink $file, $status_file or die "failed to update staus file link $status_file";
+}
+
+exit 0;
Index: /tags/ipp-20101215/pstamp/scripts/pstamp_server_status
===================================================================
--- /tags/ipp-20101215/pstamp/scripts/pstamp_server_status	(revision 30483)
+++ /tags/ipp-20101215/pstamp/scripts/pstamp_server_status	(revision 30484)
@@ -25,8 +25,4 @@
 
 pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
-#pod2usage( -msg => "Required options: --first --stage",
-#	   -exitval => 3) unless
-#    defined $first and
-#    defined $stage;
 
 # Look for programs we need
@@ -39,5 +35,10 @@
 }
 
+# XXX: Note under pantasks this will be the curent directory, so this
+# isn't necessary if the pstamp/web stuff is configured to point at the 
+# ipp build. But currently it's running out of Bill's build
 my $ipphome = "/home/panstarrs/ipp";
+my $status_cmd = "psstatus";
+
 $rundir = "$ipphome/pstamp" if !$rundir;
 
@@ -50,4 +51,8 @@
     exit 0;
 }
+
+my $now = `date -u`;
+
+print "<b>Status updated: &nbsp;&nbsp;&nbsp;</b> $now<br /><br />\n";
 
 my ($pts, $pantasks_script) = tempfile ('/tmp/pts.XXXX', UNLINK => !$save_temps);
@@ -138,13 +143,24 @@
         print "Task pstamp.job.run not found.<br />\n";
     }
+
+    # now run the script psstatus to check the status of running requests and finished requests
     print "<br /><b>Unfinished Requests</b><br />\n";
     print "<pre>\n";
-    system "/home/panstarrs/bills/ipp/tools/psstatus -r ";
+    system "$status_cmd --running";
     print "</pre>\n";
+
+if (0) {
+    # these are included in running requests above
+    print "<br /><b>Requests building results fileset</b><br/>\n";
+    print "<pre>\n";
+    system "$status_cmd --finishing";
+    print "</pre>\n";
+}
+
     print "<br /><b>Requests completed in last 24 hours (most recently completed first)</b><br />\n";
     print "<pre>\n";
-    # finished requests
-    system "/home/panstarrs/bills/ipp/tools/psstatus -f";
+    system "$status_cmd --finished";
     print "</pre>\n";
+
 } else {
     print "Task pstamp.request.run not found.\n";
@@ -153,4 +169,3 @@
 
 
-
 exit 0;