Index: trunk/tools/checkexp
===================================================================
--- trunk/tools/checkexp	(revision 27928)
+++ trunk/tools/checkexp	(revision 27928)
@@ -0,0 +1,208 @@
+#!/usr/bin/env perl
+# 
+# query the summit data store and the gpc1 database to get information as to
+# the status of summit copy and registration for "today's" exposures.
+# Optionally start and end dates may be applied.
+#
+
+use strict;
+use warnings;
+
+use DBI;
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+use PS::IPP::Config 1.0.1 qw( :standard );
+
+my $ipprc =  PS::IPP::Config->new(); # IPP Configuration
+my $siteConfig = $ipprc->{_siteConfig};
+
+my $startDate;
+my $endDate;
+my $verbose;
+my $dbname = "gpc1";
+
+GetOptions(
+           'start|s=s'    => \$startDate,
+           'end|e=s'      => \$endDate,
+           'dbname|d=s'   => \$dbname,
+           'verbose|v'    => \$verbose,
+) or pod2usage( 0 );
+
+pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
+#pod2usage( -msg => "Required options: --start",
+#           -exitval => 3)
+#    unless defined $startDate;
+
+my $dbh  = getDBHandle($ipprc, $dbname);
+
+# if startDate not provided use today
+if (!$startDate) {
+    my ($sec, $min, $hour, $mday, $month, $year) = gmtime;
+    $year += 1900;
+    $month += 1;
+    $startDate = sprintf "%4d-%02d-%02d", $year, $month, $mday;
+}
+
+
+# find all exposures in summitExp
+my $query = "SELECT exp_name, summitExp.dateobs AS registered, rawExp.dateobs, summitExp.imfiles,
+        summitExp.fault AS summit_fault, 
+        pzDownloadExp.state AS download_state, count(pzDownloadImfile.class_id) AS download_count,
+        newExp.state AS newExp_state, newExp.exp_id
+        FROM summitExp LEFT JOIN pzDownloadExp USING(exp_name) 
+            LEFT JOIN pzDownloadImfile USING(exp_name) 
+            LEFT JOIN newExp ON exp_name = tmp_exp_name 
+            LEFT JOIN rawExp USING(exp_id, exp_name) ";
+
+if ($startDate) {
+    $query .= "  WHERE date(summitExp.dateobs) >= '$startDate'";
+    $query .= " AND date(summitExp.dateobs) <= '$endDate'" if $endDate;
+}
+
+$query .= " GROUP BY exp_name ORDER BY summitExp.dateobs";
+
+print "Summit Copy and Registration Status\n\n";
+my $scode = system "~bills/bin/croot";
+if ($scode) {
+    print STDERR "croot failed with $scode\n";
+    exit 1;
+}
+print "\n";
+
+my $se_stmt = $dbh->prepare($query);
+$se_stmt->execute();
+
+my @summit_exps;
+my @summit_faults;
+my @download_faults;
+my @register_faults;
+
+my $last_exp_name;
+my $last_exp_id;
+my $last_new_exp_name = "";
+my $last_new_exp_id = "";
+my $first_exp_id;
+my $first_exp_name;
+while (my $se_ref = $se_stmt->fetchrow_hashref()) {
+    push @summit_exps, $se_ref;
+
+    my $exp_name = $se_ref->{exp_name};
+    my $summit_fault = $se_ref->{summit_fault};
+    my $download_state = $se_ref->{download_state};
+    my $new_state = $se_ref->{newExp_state};
+    my $exp_id = $se_ref->{exp_id};
+    my $imfiles = $se_ref->{imfiles};
+    my $dateobs = $se_ref->{dateobs};
+    if ($summit_fault) {
+        push @summit_faults, $se_ref;
+    } elsif ( !$download_state or $download_state eq 'run' ) {
+        push @download_faults, $se_ref;
+    } elsif ( !$new_state or $new_state eq 'run' ) {
+        push @register_faults, $se_ref;
+    }
+    if (!$first_exp_name) {
+        $first_exp_id = $exp_id ? $exp_id : "null";
+        $first_exp_name = $exp_name;
+    }
+    if ($exp_name) {
+        $last_exp_name = $exp_name;
+        $last_exp_id = $exp_id ? $exp_id : "not done";
+        if ($last_exp_id ne "not done") {
+            $last_new_exp_id = $exp_id;
+            $last_new_exp_name = $exp_name;
+        }
+    }
+}
+
+my $numSummitExp = scalar @summit_exps;
+my $numSummitFaults = scalar @summit_faults;
+my $numDownloadFaults = scalar @download_faults;
+my $numRegisterFaults = scalar @register_faults;
+
+print "$numSummitExp summit exposures since $startDate";
+print " up to $endDate" if $endDate;
+print "\n";
+
+exit 0 if $numSummitExp == 0;
+
+if ($numSummitFaults) {
+  print "$numSummitFaults summit faults\n";
+  if ($verbose) {
+    print  "  exp_name         registered      fault\n";
+          # c4951g0003d   2009-04-30 12:39:34   110
+    foreach my $s (@summit_faults) {
+        printf "%-12s %20s %5d\n", $s->{exp_name}, $s->{registered}, $s->{summit_fault};
+    }
+  }
+}
+if ($numDownloadFaults) {
+  print "\n$numDownloadFaults incomplete downloads\n";
+  if ($verbose) {
+    print " exp_name    summit_imfiles downloaded imfiles  faulted\n";
+    my $stmt = $dbh->prepare("SELECT exp_name, count(fault) as fault_count  FROM pzDownloadImfile WHERE exp_name = ? AND fault > 0");
+    foreach my $p (@download_faults) {
+        $stmt->execute($p->{exp_name});
+        my $ref = $stmt->fetchrow_hashref();
+        printf "%-12s     %3d          %3d                %3d\n", 
+            $p->{exp_name} ? $p->{exp_name} : "null", 
+            $p->{imfiles} ? $p->{imfiles} : 0,
+            $p->{download_count} ? $p->{download_count} : 0,
+            $ref->{fault_count} ? $ref->{fault_count} : 0;
+    }
+  }
+}
+if ($numRegisterFaults) {
+ print "\n$numRegisterFaults exposures copied but not registered\n";
+ if ($verbose) {
+    my $stmt = $dbh->prepare("SELECT exp_id, count(class_id) as fault_count FROM rawImfile where exp_name = ? and fault > 0");
+    print " exp_name           exp_id  fault_count\n";
+         # c4895g0607o         57173       1
+
+    foreach my $r (@register_faults) {
+        my $exp_name = $r->{exp_name};
+        $stmt->execute($exp_name);
+        my $ref = $stmt->fetchrow_hashref();
+        my $exp_id = $ref->{exp_id};
+        $exp_id = "null" if !defined $exp_id;
+        
+        my $fault_count = $ref->{fault_count};
+        $fault_count = "null" if !defined $fault_count;
+        printf "%-12s %12s     %3d\n", $exp_name, $exp_id, $fault_count;
+    }
+  }
+}
+
+print "\n";
+
+print "first exposure:  $first_exp_name $first_exp_id\n";
+print "last copied:     $last_new_exp_name $last_new_exp_id\n"
+            if $last_new_exp_id ne $last_exp_id;
+print "last exposure:   $last_exp_name $last_exp_id\n";
+
+exit 0;
+
+
+sub getDBHandle {
+    my $ipprc = shift;
+    my $dbname = shift;
+    if (!$dbname) {
+        $dbname = metadataLookupStr($siteConfig, 'DBNAME');
+        if (($dbname eq 'XXX') or ($dbname eq "NONE")) {
+            $dbname = 'gpc1';
+        }
+    }
+    my $dbuser   = metadataLookupStr($siteConfig, 'DBUSER');
+    my $dbpass   = metadataLookupStr($siteConfig, 'DBPASSWORD');
+    my $dbserver = metadataLookupStr($siteConfig, 'DBSERVER');
+    exit ($PS_EXIT_CONFIG_ERROR) unless defined $dbserver and $dbname and $dbuser and $dbpass;
+
+
+    die "database enviornment not set up" unless defined($dbserver) and defined($dbuser)
+        and defined($dbpass) and defined($dbname);
+
+    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";
+
+    my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";
+
+    return $dbh;
+}