Index: trunk/ippScripts/scripts/night_report.pl
===================================================================
--- trunk/ippScripts/scripts/night_report.pl	(revision 40444)
+++ trunk/ippScripts/scripts/night_report.pl	(revision 40444)
@@ -0,0 +1,608 @@
+#!/usr/bin/env perl
+
+use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
+use Pod::Usage qw( pod2usage );
+
+use DBI;
+use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock'; # Socket for mysql
+
+# this saves the original arguments for later reporting
+my @ARGS = @ARGV;
+
+GetOptions(
+    'date=s'            => \$date,
+    'dbname|d=s'        => \$dbname,    # Database name
+    'verbose'           => \$verbose,   # Print to stdout
+    'very-verbose'      => \$verbose2,   # Print to stdout
+    ) or pod2usage( 2 );
+
+pod2usage( -msg => "Unknown option(s): @ARGV", -exitval => 2 ) if @ARGV;
+
+# example if I require some options
+# pod2usage( -msg => "Required options: --exp_id --chip_id", -exitval => 3) unless defined $exp_id and defined $chip_id;
+
+$date   = `date --utc --rfc-3339=date` unless defined $date;
+chomp $date;
+$date=~ s/\//-/g;
+$date=~ s/\./-/g;
+
+# database connection information:
+$dbhost = 'ippdb08';
+$dbname = 'gpc1' unless defined $dbname;
+$dbuser = 'ippuser';
+$dbpass = 'ippuser';
+
+# dataGroupDate : formatted to match data_group values (YYYYMMDD)
+$dataGroupDate = $date;
+$dataGroupDate=~ s/-//g;
+$dataGroupDate=~ s/\.//g;
+
+print "\n\n\n";
+print "--- Report for $dbname : $date ---\n\n";
+
+# set up the database connection:
+my $dsn = "DBI:mysql:host=$dbhost;database=$dbname";
+my $dbh = DBI->connect( $dsn, $dbuser, $dbpass ) or die "Unable to connect to database: $DBI::errstr";
+
+# find a useful exp_id
+$refExpID = 0;
+if (1) { 
+    # print "Exposures 10 days ago:\n";
+
+    my $query = "SELECT MAX(exp_id)";
+    $query .= " FROM rawExp";
+    $query .= " WHERE dateobs >= date_sub('$date', interval 20 day)";
+    $query .= " AND dateobs <= date_sub('$date', interval 10 day)";
+
+    my $result = &mysql_select ($query);
+
+    @row = $result->fetchrow_array();
+    $refExpID = $row[0];
+    if ($refExpID eq "") { 
+      $refExpID = 0; 
+      print "*** WARNING: no data in period 10-20 days before requested date (query will be slower) **\n";
+    }
+    # print "Reference exp_id: $refExpID\n";
+}
+
+# check the status of raw exposures:
+if (1) { 
+    if ($verbose) { print "Exposures in rawExp:\n"; }
+
+    $expSkipped = 0;
+    $expPending = 0;
+
+    my $query = "SELECT exp_type, obs_mode, state, count(*)";
+    $query .= " FROM rawExp";
+    $query .= " WHERE dateobs like '$date%'";
+    $query .= " AND exp_id > $refExpID"; # REF
+#   $query .= " AND exp_type = 'OBJECT'";
+    $query .= " GROUP BY exp_type, obs_mode, state";
+    $query .= " ORDER BY exp_type, obs_mode";
+
+    my $result = &mysql_select ($query);
+
+    if ($verbose) { printf "%8s %15s %6s %4s\n", "exp_type", "obs_mode", "state", "N(exp)"; }
+    while (@row = $result->fetchrow_array()) {
+	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; }
+	if ($row[2] eq "new") {
+	    $expPending += $row[3];
+	}
+	if ($row[2] eq "") {
+	    $expSkipped += $row[3];
+	}
+    }
+    if ($expSkipped || $expPending) { print "*** WARNING: $expSkipped skipped rawExp, $expPending incomplete rawExp ***\n\n"; } else { if ($verbose) { print "\n"; }}
+}
+    
+# check the status of chip runs:
+if (1) {
+    if ($verbose) { print "Exposures in chipRun (OBJECT, obs_mode not like ENGINEERING, not NULL):\n"; } # the obs_mode not NULL is implicit given the obs_mode not like ENGINEERING 
+
+    $chipPending = 0;
+    $chipSkipped = 0;
+
+    my $query = "SELECT exp_type, obs_mode, chipRun.state, count(*)";
+    $query .= " FROM rawExp";
+    $query .= " LEFT JOIN chipRun using (exp_id)";
+    $query .= " WHERE dateobs LIKE '$date%'";
+    $query .= " AND exp_id > $refExpID"; #REF
+    $query .= " AND exp_type = 'OBJECT'";
+    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= " GROUP BY obs_mode, chipRun.state";
+
+    my $result = &mysql_select ($query);
+
+    if ($verbose) { printf "%8s %15s %6s %4s", "exp_type", "obs_mode", "state", "N(exp)\n"; }
+    while (@row = $result->fetchrow_array()) {
+	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; }
+	if ($row[2] eq "new") {
+	    $chipPending += $row[3];
+	}
+	if ($row[2] eq "") {
+	    $chipSkipped += $row[3];
+	}
+    }
+    if ($chipPending || $chipSkipped) { print "*** WARNING: $chipSkipped skipped chipRun, $chipPending incomplete chipRun ***\n\n"; } else { if ($verbose) { print "\n"; }}
+}
+
+# check the status of camera runs:
+$camPending = 0;
+$camSkipped = 0;
+{
+    if ($verbose) { print "Exposures in camRun (OBJECT, not ENGINEERING)...\n"; }
+
+    # my $result = &mysql_select ("");
+    my $query = "SELECT exp_type, obs_mode, camRun.state, camProcessedExp.fault, camProcessedExp.quality, count(*)";
+    $query .= " FROM rawExp join chipRun using (exp_id)";
+    $query .= " LEFT JOIN camRun using (chip_id)";
+    $query .= " LEFT JOIN camProcessedExp using (cam_id)";
+    $query .= " WHERE dateobs LIKE '$date%'";
+    $query .= " AND exp_id > $refExpID"; #REF
+    $query .= " AND exp_type = 'OBJECT'";
+    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= " GROUP BY obs_mode, camRun.state, camProcessedExp.fault, camProcessedExp.quality";
+
+    my $result = &mysql_select ($query);
+
+    if ($verbose) { printf "%8s %15s %6s %4s | %5s %5s\n", "exp_type", "obs_mode", "state", "N(exp)", "fault", "quality"; }
+    while (@row = $result->fetchrow_array()) {
+	if ($verbose) { printf "%8s %15s %6s %4d   | %5d %5d\n", $row[0], $row[1], $row[2], $row[5], $row[3], $row[4]; } # exp_type, obs_mode, camRun.state, camProcessedExp.fault, camProcessedExp.quality,  count(*)
+	if ($row[2] eq "new") {
+	    $camPending += $row[5];
+	}
+	if ($row[2] eq "") {
+	    $camSkipped += $row[5];
+	}
+    }
+    if ($camSkipped || $camPending) { print "*** WARNING: $camSkipped skipped camRuns, $camPending pending camRuns ***\n\n"; } else { if ($verbose) { print "\n"; }}
+}
+
+# check the status of fake runs:
+{
+    if ($verbose) { print "Exposures in fakeRun:\n"; }
+
+    $fakePending = 0;
+    $fakeSkipped = 0;
+
+    my $query = "SELECT exp_type, obs_mode, fakeRun.state, count(*)";
+    $query .= " FROM rawExp";
+    $query .= " JOIN chipRun using (exp_id)";
+    $query .= " JOIN camRun using (chip_id)";
+    $query .= " JOIN camProcessedExp using (cam_id)";
+    $query .= " LEFT JOIN fakeRun using (cam_id)";
+    $query .= " WHERE dateobs LIKE '$date%'";
+    $query .= " AND exp_id > $refExpID"; #REF
+    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= " AND exp_type = 'OBJECT'";
+    $query .= " AND (camProcessedExp.quality is null or camProcessedExp.quality = 0)";
+    $query .= " GROUP BY obs_mode, fakeRun.state";
+
+    my $result = &mysql_select ($query);
+
+    if ($verbose) { printf "%8s %15s %6s %4s\n", "exp_type", "obs_mode", "state", "N(exp)"; }
+    while (@row = $result->fetchrow_array()) {
+	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; } # exp_type, obs_mode, fakeRun.state,  count(*)
+	if ($row[2] eq "new") {
+	    $fakePending += $row[3];
+	}
+	if ($row[2] eq "") {
+	    $fakeSkipped += $row[3];
+	}
+    }
+    if ($fakeSkipped || $fakePending) { print "$fakeSkipped skipped fakeRuns, $fakePending pending fakeRuns\n\n"; } else { if ($verbose) { print "\n"; }}
+}
+
+# check the status of warp runs:
+{
+    if ($verbose) { print "Exposures in warpRuns:\n"; }
+
+    $warpPending = 0;
+    $warpSkipped = 0;
+
+    # my $result = &mysql_select ("");
+    my $query = "SELECT exp_type, obs_mode, warpRun.state, count(*)";
+    $query .= " FROM rawExp";
+    $query .= " JOIN chipRun using (exp_id)";
+    $query .= " JOIN camRun using (chip_id)";
+    $query .= " JOIN camProcessedExp using (cam_id)";
+    $query .= " JOIN fakeRun using (cam_id)";
+    $query .= " LEFT JOIN warpRun using (fake_id)";
+    $query .= " WHERE dateobs LIKE '$date%'";
+    $query .= " AND exp_id > $refExpID"; #REF
+    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= " AND exp_type = 'OBJECT'";
+    $query .= " AND (camProcessedExp.quality IS NULL OR camProcessedExp.quality = 0)";
+    $query .= " GROUP BY obs_mode, warpRun.state";
+
+    my $result = &mysql_select ($query);
+
+    if ($verbose) { printf "%8s %15s %6s %4s\n", "exp_type", "obs_mode", "state",  "N(exp)"; }
+    while (@row = $result->fetchrow_array()) {
+	if ($verbose) { printf "%8s %15s %6s %4d\n", $row[0], $row[1], $row[2], $row[3]; } # exp_type, obs_mode, warpRun.state,  count(*)
+	if ($row[2] eq "new") {
+	    $warpPending += $row[3];
+	}
+	if ($row[2] eq "") {
+	    $warpSkipped += $row[3];
+	}
+    }
+    if ($warpSkipped || $warpPending) { print "$warpSkipped skipped warpRuns, $warpPending pending warpRuns\n\n"; } else { if ($verbose) { print "\n"; }}
+}
+
+# count the number of exposures per chunk, determine expected diffRuns:
+{
+    print "Expected number of WWdiffs excluding camRuns with bad quality:\n";
+    print "NOTE: multiple exposures with same comment string (xyz visit a) are treated as 1 exposure\n";
+
+    # my $result = &mysql_select ("");
+    my $query = "SELECT Nvisit, count(*) FROM";
+    $query .= " (";
+    $query .= "   SELECT object, filter, chunk, count(*) as Nvisit FROM";
+    $query .= "   (";
+    $query .= "     SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk";
+    $query .= "     FROM rawExp";
+    $query .= "     LEFT JOIN chipRun using (exp_id)";
+    $query .= "     LEFT JOIN camRun using (chip_id)";
+    $query .= "     LEFT JOIN camProcessedExp using (cam_id)";
+    $query .= "     WHERE dateobs LIKE '$date%'";
+    $query .= "     AND exp_id > $refExpID"; #REF
+    $query .= "     AND exp_type = 'OBJECT'";
+    $query .= "     AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= "     AND obs_mode like '%SS%'";
+    $query .= "     AND rawExp.comment like '%visit%'";
+    $query .= "     AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";
+    $query .= "     GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
+    $query .= "   ) AS tableChunks GROUP BY object, filter, chunk";
+    $query .= " ) AS tableVisits GROUP BY Nvisit";
+
+    my $result = &mysql_select ($query);
+
+    $diffExpect = 0;
+    while (@row = $result->fetchrow_array()) {
+	# printf "%8s %15s : ", $row[0], $row[1]; # c, count(*)
+	if ($row[0] == 1) {
+	    if ($row[1] == 1) { $verb = "chunk has"; } else { $verb = "chunks have"; }
+	    printf "%3d %-11s 1 visit  (visit A       in comments), no WWdiffs\n", $row[1], $verb;
+	} elsif ($row[0] == 2) {
+	    if ($row[1] == 1) { $verb = "chunk has"; } else { $verb = "chunks have"; }
+	    printf "%3d %-11s 2 visits (visit A/B     in comment), expect $row[1] WWdiff(s)\n", $row[1], $verb;
+	    $diffExpect += $row[1];
+	} elsif ($row[0] == 3) {
+	    if ($row[1] == 1) { $verb = "chunk has"; } else { $verb = "chunks have"; }
+	    printf "%3d %-11s 3 visits (visit A/B/C   in comment), expect 2 * %d = %d WWdiffs\n", $row[1], $verb,  $row[1],  2 * $row[1];
+	    $diffExpect += $row[1]*2;
+	} elsif ($row[0] == 4) {
+	    if ($row[1] == 1) { $verb = "chunk has"; } else { $verb = "chunks have"; }
+	    printf "%3d %-11s 4 visits (visit A/B/C/D in comment), expect 2 * %d = %d WWdiffs\n", $row[1], $verb,  $row[1],  2 * $row[1];
+	    $diffExpect += $row[1]*2;
+	} else {
+	    print "I don't understand, there are more visits than I expect\n";
+	}
+    }
+    print "\n";
+    print " * $diffExpect diffRuns expected *\n";
+    if ($verbose) { print "\n"; }
+}
+
+# check the status of diff runs:
+{
+    if ($verbose) { print "diff processing status (not WS):\n"; }
+
+    $diffDone = 0;
+
+    my $query = "SELECT label, state, count(*)";
+    $query .= " FROM diffRun";
+    $query .= " WHERE data_group like '%$dataGroupDate'";
+    $query .= " AND label NOT LIKE '%WS.nigh%'";
+    $query .= " GROUP BY label, state";
+
+    my $result = &mysql_select ($query);
+
+    if ($verbose) { printf "%20s %6s | %8s\n", "label", "state", "N(diffs)"; }
+    while (@row = $result->fetchrow_array()) {
+	if ($verbose) { printf "%20s %6s | %8d\n", $row[0], $row[1], $row[2]; } # label, state, count
+	if (($row[1] ne "new") && ($row[1] ne "")) {
+	    $diffDone += $row[2];
+	}
+    }
+    if ($verbose) { print "\n"; }
+    if ($diffDone != $diffExpect) { 
+	print "*** WARNING: Expect $diffExpect diffRuns, complete $diffDone diffRuns: inconsistent ***\n"; 
+    } else {
+	print " * $diffDone of $diffExpect diffRuns completed *\n"; 
+    }
+}
+
+# count diffRuns to be published:
+{
+    my $query = "SELECT count(*) FROM";
+    $query .= "  (";
+    $query .= "    SELECT label, state, count(*)";
+    $query .= "    FROM diffRun";
+    $query .= "    LEFT JOIN diffSkyfile using (diff_id)";
+    $query .= "    WHERE data_group LIKE '%$dataGroupDate%'";
+    $query .= "    AND label NOT LIKE '%WS.nigh%'";
+    $query .= "    AND (quality IS NULL OR quality = 0)";
+    $query .= "    GROUP BY label, state, diff_id";
+    $query .= "  ) as tableDiffs";
+
+    my $result = &mysql_select ($query);
+
+    @row = $result->fetchrow_array();
+    printf " * %d diffRuns can be published *   (has at least 1 skycell with quality = 0 or quality is NULL)\n", $row[0];
+    if ($verbose) { print "\n"; }
+    $diffRunsToBePublished = $row[0];
+}
+
+# check the status of publish runs:
+{
+    if ($verbose) { print "publishing status (not WS)...\n"; }
+
+    $pubDone = 0;
+
+    my $query = "SELECT diffRun.label, publishRun.state, count(*)";
+    $query .= " FROM diffRun";
+    $query .= " LEFT JOIN publishRun on (diff_id = stage_id)";
+    $query .= " WHERE data_group LIKE '%$dataGroupDate'";
+    $query .= " AND diffRun.label NOT LIKE '%WS.nigh%'";
+    $query .= " GROUP BY diffRun.label, publishRun.state";
+
+    my $result = &mysql_select ($query);
+
+    if ($verbose) { printf "%20s %6s | %8s\n", "label", "state", "N(diffs)"; }
+    while (@row = $result->fetchrow_array()) {
+	if ($verbose) { printf "%20s %6s | %8d\n", $row[0], $row[1], $row[2]; } # label, state, count 
+	if (($row[1] ne "new") && ($row[1] ne "")) {
+	    $pubDone += $row[2];
+	}
+    }
+    if ($verbose) { print "\n"; }
+    if ($pubDone != $diffRunsToBePublished) { 
+	print "*** WARNING: Expect $diffRunsToBePublished diffRuns to be published, only $pubDone pubRuns: inconsistent ***\n\n"; 
+    } else {
+	print " * $pubDone of $diffRunsToBePublished diffRuns published **\n\n"; 
+    }
+}
+
+# check for exposures not sent to chip:
+if ($verbose) {
+
+    my $query = "SELECT exp_name, obs_mode, comment";
+    $query .= " FROM rawExp left join chipRun using (exp_id)";
+    $query .= " WHERE dateobs LIKE '$date%'";
+    $query .= " AND exp_id > $refExpID"; #REF
+    $query .= " AND exp_type = 'OBJECT'";
+    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= " AND chip_id is null";
+
+    my $result = &mysql_select ($query);
+
+    $doHeader = 1; 
+    while (@row = $result->fetchrow_array()) {
+	if ($doHeader) {
+	    print "OBJECT Exposures NOT sent to chipRun:\n";
+	    printf "%11s %15s %14s", "exp_name", "obs_mode", "comment\n";
+	    $doHeader = 0;
+	}
+	printf "%11s %15s %14s\n", $row[0], $row[1], $row[2];
+    }
+    if (! $doHeader) { print "\n"; }
+}
+
+# check for chipRuns not sent to camera
+if ($camSkipped && $verbose) {
+    print "chipRuns NOT sent to camRun:\n";
+
+    my $query = "SELECT exp_name, obs_mode, comment, chipRun.state";
+    $query .= " FROM rawExp";
+    $query .= " JOIN chipRun USING (exp_id)";
+    $query .= " LEFT JOIN camRun using (chip_id)";
+    $query .= " WHERE dateobs LIKE '$date%'";
+    $query .= " AND exp_id > $refExpID"; #REF
+    $query .= " AND exp_type = 'OBJECT'";
+    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= " AND cam_id IS NULL";
+
+    my $result = &mysql_select ($query);
+
+    printf "%11s %15s %10s %14s\n", "exp_name", "obs_mode", "chip.state", "comment";
+    while (@row = $result->fetchrow_array()) {
+	printf "%11s %15s %10s %14s\n", $row[0], $row[1], $row[2], $row[3];
+    }
+    print "\n";
+}
+
+# check for bad quality camera exposurs
+if (1) {
+    my $query = "SELECT exp_name, camProcessedExp.quality, comment";
+    $query .= " FROM rawExp";
+    $query .= " JOIN chipRun USING (exp_id)";
+    $query .= " JOIN camRun USING (chip_id)";
+    $query .= " JOIN camProcessedExp USING (cam_id)";
+    $query .= " WHERE dateobs LIKE '$date%'";
+    $query .= " AND exp_id > $refExpID"; #REF
+    $query .= " AND exp_type = 'OBJECT'";
+    $query .= " AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= " AND (camProcessedExp.quality > 0)";
+    $query .= " ORDER BY comment";
+
+    my $result = &mysql_select ($query);
+
+    $doHeader = 1; 
+    $NbadCam = 0;
+
+    while (@row = $result->fetchrow_array()) {
+	if ($verbose && $doHeader) {
+	    print "Bad quality camRun exposures:\n";
+	    printf "%11s %7s | %15s\n", "exp_name", "quality", "comment";
+	    $doHeader = 0;
+	}
+	if ($verbose) { printf "%11s %7d | %15s\n", $row[0], $row[1], $row[2]; } else { $NbadCam ++; }
+    }
+    if ($verbose && !$doHeader) { print "\n"; }
+    if (!$verbose) { print "$NbadCam bad quality camera exposures\n\n"; }
+}
+
+# check the status of diff runs NOT for Solar System:
+if ($verbose) {
+    # my $result = &mysql_select ("");
+    my $query = "SELECT * FROM";
+    $query .= " (";
+    $query .= "  SELECT object, filter, chunk, count(*) as Nvisit FROM";
+    $query .= "  (";
+    $query .= "   SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk";
+    $query .= "   FROM rawExp";
+    $query .= "   LEFT JOIN chipRun using (exp_id)";
+    $query .= "   LEFT JOIN camRun using (chip_id)";
+    $query .= "   LEFT JOIN camProcessedExp using (cam_id)";
+    $query .= "   WHERE dateobs LIKE '$date%'";
+    $query .= "   AND exp_id > $refExpID"; #REF
+    $query .= "   AND exp_type = 'OBJECT'";
+    $query .= "   AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= "   AND obs_mode NOT like '%SS%'";
+    $query .= "   AND rawExp.comment like '%visit%'";
+    $query .= "   AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";
+    $query .= "   GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
+    $query .= "  ) AS tableChunks GROUP BY object, filter, chunk";
+    $query .= " ) AS tableVisits ORDER BY Nvisit, chunk, object";
+
+    my $result = &mysql_select ($query);
+    
+    $doHeader = 1;
+    while (@row = $result->fetchrow_array()) {
+	if ($doHeader) {
+	    print "Chunks which should not generate WWdiffs (not Solar System diffs)\n";
+	    printf "%8s %8s %8s %8s\n", "Object", "Filter", "Chunk", "N(visit)";
+	    $doHeader = 0;
+	}
+	printf "%8s %8s %8s %4d\n", $row[0], $row[1], $row[2], $row[3];
+    }
+    if (!$doHeader) { print "\n"; }
+}
+
+# check for unpublished solar-system exposures
+{
+    my $query = "SELECT expall.exp_name, expall.dateobs, expall.comment, exppub.pubstate from";
+    $query .= "  (";
+    $query .= "    SELECT exp_name, comment, dateobs";
+    $query .= "    FROM rawExp";
+    $query .= "    JOIN chipRun using (exp_id)";
+    $query .= "    JOIN camRun using (chip_id)";
+    $query .= "    JOIN camProcessedExp using (cam_id)";
+    $query .= "    WHERE dateobs like '$date%'";
+    $query .= "    AND exp_id > $refExpID"; #REF
+    $query .= "    AND obs_mode like '%SS%'";
+    $query .= "    AND (camProcessedExp.quality = 0)";
+    $query .= "  ) AS expall";
+    $query .= "  LEFT JOIN";
+    $query .= "  (";
+    $query .= "    SELECT exp_name, publishRun.state as pubstate";
+    $query .= "    FROM diffRun";
+    $query .= "    JOIN diffInputSkyfile using (diff_id)";
+    $query .= "    JOIN publishRun on (diffRun.diff_id = stage_id)";
+    $query .= "    JOIN warpRun ON ( warp1 = warp_id or warp2 = warp_id)";
+    $query .= "    JOIN fakeRun using (fake_id)";
+    $query .= "    JOIN camRun using (cam_id)";
+    $query .= "    JOIN camProcessedExp using (cam_id)";
+    $query .= "    JOIN chipRun using (chip_id)";
+    $query .= "    JOIN rawExp using (exp_id)";
+    $query .= "    WHERE diffRun.reduction like 'SWEETSPOT'";
+##  $query .= "    AND exp_id > $refExpID"; -- adding this restriction here slows the query way down1
+    $query .= "    AND stack2 IS NULL";
+    $query .= "    AND diffRun.data_group LIKE '%SS.$dataGroupDate'";
+    $query .= "    GROUP BY exp_name";
+    $query .= "   ) AS exppub";
+    $query .= " ON expall.exp_name = exppub.exp_name";
+    $query .= " WHERE exppub.exp_name is NULL";
+    $query .= " OR exppub.pubstate = 'new'";
+
+    my $result = &mysql_select ($query);
+
+    $doHeader = 1;
+    while (@row = $result->fetchrow_array()) {
+	if ($doHeader) {
+	    print "Unpublished Solar System Exposures ( obs_mode like %SS% ) with good camera quality :\n";
+	    printf "%12s %-19s | %-16s\n", "exp_name", "dateobs", "comment";
+	    $doHeader = 0;
+	}
+	printf "%12s %-19s | %-16s\n", $row[0], $row[1], $row[2]
+    }
+}
+print "\n\n";
+
+# count the number of exposures per chunk, determine expected diffRuns:
+if ($verbose2) {
+    print "Number of exposures for each chunk:\n";
+
+    # my $result = &mysql_select ("");
+    my $query = "SELECT object, filter, chunk, count(*) as Nvisit FROM";
+    $query .= "   (";
+    $query .= "     SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk";
+    $query .= "     FROM rawExp";
+    $query .= "     LEFT JOIN chipRun using (exp_id)";
+    $query .= "     LEFT JOIN camRun using (chip_id)";
+    $query .= "     LEFT JOIN camProcessedExp using (cam_id)";
+    $query .= "     WHERE dateobs LIKE '$date%'";
+    $query .= "     AND exp_id > $refExpID"; #REF
+    $query .= "     AND exp_type = 'OBJECT'";
+    $query .= "     AND obs_mode NOT LIKE 'ENGINEERING'";
+    $query .= "     AND obs_mode like '%SS%'";
+    $query .= "     AND rawExp.comment like '%visit%'";
+    $query .= "     AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";
+    $query .= "     GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
+    $query .= "   ) AS tableChunks GROUP BY object, filter, chunk";
+
+    my $result = &mysql_select ($query);
+
+    printf "%11s %7s %18s %-3s\n", "object", "filter", "chunk", "N(visit)";
+    while (@row = $result->fetchrow_array()) {
+	printf "%11s %7s %18s %3d\n", $row[0], $row[1], $row[2], $row[3];
+    }
+    print "\n";
+}
+
+exit 0;
+
+# global dbh?
+sub mysql_select {
+    
+    my $query = $_[0];
+    
+    my $statement = $dbh->prepare($query);
+    
+    $statement->execute();
+    
+    return $statement;
+}
+
+sub mysql_dump_result {
+    
+    my $result = $_[0];
+    
+    $Nfields = $result->{NUM_OF_FIELDS};
+
+    print "Nfields: $Nfields\n";
+
+    for ($i = 0; $i < $Nfields; $i++) {
+	printf "Field %d : %s\n", $i, $result->{NAME}->[$i];
+    }
+
+    while (@row = $result->fetchrow_array()) {
+	for ($i = 0; $i < $Nfields; $i++) {
+	    print "$row[$i]  ";
+	}
+	print "\n";
+    }
+    return;
+}
+
+# example using the dbh prepare, execute, fetch commands:
+if (0) {
+
+    my $result = &mysql_select ("SELECT * from rawExp limit 2");
+    
+    &mysql_dump_result ($result);
+    
+    exit 1;
+}