Index: trunk/ippScripts/scripts/night_report.pl
===================================================================
--- trunk/ippScripts/scripts/night_report.pl	(revision 40904)
+++ trunk/ippScripts/scripts/night_report.pl	(revision 40972)
@@ -17,4 +17,5 @@
     'very-verbose'      => \$verbose2, # Print to stdout
     'chunks'            => \$chunks,   # give information on the current status of the chunks
+    'mopsreport'            => \$mopsreport,   # return a report in the new style requested by MOPS
     ) or pod2usage( 2 );
 
@@ -40,7 +41,4 @@
 $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";
@@ -69,4 +67,261 @@
 
 ##################
+# testing how to get more information parsed by chunk
+if ($mopsreport or $report) {
+    print "\n\n\n";
+    print "--- MOPS report for $dbname : $date ---\n\n";
+
+    my $badcamflag = 0;
+    my $badwarpflag = 0;
+    my $baddiffflag = 0;
+
+    #do a query of raw/chip/cam/warp counts for this night, split by chunk. 
+    #We want to stick to only MOPS products, so we do labels %SS%, %EU% and %Bright.% (to avoid BrightTwi and Bright3PI which are not in quads)
+    #we need to do a subquery to find the total number of skycells in warpSkyfile, as well as the number of those with a bad quality
+    my $query = "SELECT suba.chunk,suba.Nvis1,suba.Nvis2,suba.Nvis3,suba.Nvis4,suba.Ncamgood,suba.Ncambad,subb.Nwarpgood,subb.Nwarpbad FROM ";
+    $query .= "    (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(if(rawExp.comment LIKE '%visit 1%',1,NULL)) AS Nvis1,";
+    $query .= "    count(if(rawExp.comment LIKE '%visit 2%',1,NULL)) AS Nvis2,count(if(rawExp.comment LIKE '%visit 3%',1,NULL)) AS Nvis3,";
+    $query .= "    count(if(rawExp.comment LIKE '%visit 4%',1,NULL)) AS Nvis4, count(if(camProcessedExp.sigma_ra <= 5 AND camProcessedExp.sigma_dec <= 5 AND camProcessedExp.quality = 0,1,NULL)) AS Ncamgood,";
+    $query .= "    count(if(camProcessedExp.sigma_ra > 5 OR camProcessedExp.sigma_dec > 5 OR camProcessedExp.quality > 0,1,NULL)) AS Ncambad";
+    $query .= "    FROM rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) JOIN camProcessedExp USING (cam_id) LEFT JOIN fakeRun USING (cam_id) LEFT JOIN warpRun USING (fake_id)";
+    $query .= "    WHERE dateobs like '$date%' AND exp_id > $refExpID";
+    $query .= "    AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
+    $query .= "    GROUP BY chunk ORDER BY dateobs) as suba ";
+    $query .= "LEFT JOIN";
+    $query .= "    (SELECT subc.chunk, count(if(subc.Nwarpskycellbad < subc.Nwarpskycell,1,NULL)) AS Nwarpgood, count(if(subc.Nwarpskycellbad = subc.Nwarpskycell,1,NULL)) AS Nwarpbad";
+    $query .= "    FROM ";
+    $query .= "      (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(warpSkyfile.quality) AS Nwarpskycell,count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad";
+    $query .= "      FROM rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) LEFT JOIN fakeRun USING (cam_id) LEFT JOIN warpRun USING (fake_id) JOIN warpSkyfile USING (warp_id)";
+    $query .= "      WHERE dateobs like '$date%' AND exp_id > $refExpID";
+    $query .= "      AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
+    $query .= "      GROUP BY exp_name) as subc";
+    $query .= "    GROUP BY chunk) AS subb ";
+    $query .= "ON suba.chunk=subb.chunk";
+ 
+    my $result = &mysql_select ($query);
+
+    printf "%18s | %11s | %4s | %4s | %7s | %4s | %7s | %4s | %7s |\n", "chunk", "Nobs", "Ncam","Nwrp","Nwrp_pub", "Nwwd", "Nwwd_pub", "Nwsd", "Nwsd_pub"; 
+    while (@row = $result->fetchrow_array()) {
+          my $Nvis1 = 0;
+          my $Nvis2 = 0;
+          my $Nvis3 = 0;
+          my $Nvis4 = 0;
+          my $Ncamgood = 0;
+          my $Ncambad = 0;
+          my $Nwarpgood = 0;
+          my $Nwarpbad = 0;
+          my $NexpWWdiff = 0;
+          my $Npubwarps = 0;
+          my $uniwarps = undef;
+
+          my $chunkname = $row[0];
+          if($row[1] gt 0) {$Nvis1 = $row[1]};
+          if($row[2] gt 0) {$Nvis2 = $row[2]};
+          if($row[3] gt 0) {$Nvis3 = $row[3]};
+          if($row[4] gt 0) {$Nvis4 = $row[4]};
+          if($row[5] gt 0) {$Ncamgood = $row[5]};
+          if($row[6] gt 0) {$Ncambad = $row[6]};
+          if($row[7] gt 0) {$Nwarpgood = $row[7]};
+          if($row[8] gt 0) {$Nwarpbad = $row[8]};
+
+          #set flags if present
+          if($Ncambad >= 1) {$badcamflag = 1};
+          if($Nwarpbad >= 1) {$badwarpflag = 1};
+
+          #Find the total published number of unique warps for this chunk
+          #my $query0 = "     SELECT count(DISTINCT warpRun.warp_id) FROM ";
+          #$query0 .= "           diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) JOIN publishRun ON (diffInputSkyfile.diff_id = stage_id)";
+          #$query0 .= "           WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%')";
+          #$query0 .= "            AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL' AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' group by warpRun.warp_id";
+          #my $result0 = &mysql_select ($query0);
+          #while (@row = $result0->fetchrow_array()) {$Npubwarps = $row[0];}
+
+
+          #Find the expected number of diffs for this chunk
+          my $query1 = "SELECT suba.chunk, SUM(IF(suba.Nvisits = 2,1,NULL)) as totvis2, SUM(IF(suba.Nvisits = 3 OR suba.Nvisits = 4,2,NULL)) as totvis34 FROM";
+          $query1 .= "    (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nvisits FROM";
+          $query1 .= "    rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) WHERE dateobs like '$date%'";
+          $query1 .= "    AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
+          $query1 .= "    AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' group by object) as suba group by chunk;";
+          my $result1 = &mysql_select ($query1);
+          while (@row = $result1->fetchrow_array()) {$NexpWWdiff = $row[1] + $row[2];}
+
+          #find the unique diff_ids for each chunk, to be matched to the diffRun catalog
+          my $query2 = "     SELECT diff_id,warp1,stack1,warp2,stack2 FROM ";
+          $query2 .= "           diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
+          $query2 .= "           WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%')";
+          $query2 .= "            AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL' AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' GROUP By diff_id";
+          my $result2 = &mysql_select ($query2);
+
+          my $NWWdiffgood = 0;
+          my $NWWdiffbad = 0;
+          my $NWWdiffpub = 0;
+
+          my $NWSdiffgood = 0;
+          my $NWSdiffbad = 0;
+          my $NWSdiffpub = 0;
+          my %wrpcnt = ();
+
+          while (@row2 = $result2->fetchrow_array()) {
+              #distinguish between WW and WS diffs based on stack2
+              if ($row2[4] == 0) {
+                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state 
+                  my $query3 = "     SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM ";
+                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
+                  $query3 .= "           WHERE diff_id='$row2[0]' GROUP BY diffRun.diff_id";
+                  my $result3 = &mysql_select ($query3);
+                  while (@row3 = $result3->fetchrow_array()) {
+                      #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published 
+                      if ($row3[1]<$row3[0]) {$NWWdiffgood ++;}
+                      if ($row3[1]==$row3[0]) {$NWWdiffbad ++;}
+                      if ($row3[2]==$row3[0]) {$NWWdiffpub ++;}
+
+                      #if this wwdiff is published, record its corresponding warp_ids to see how many unique ones there are
+                      if ($row3[2]==$row3[0]) {$wrpcnt{$row2[1]}++;}  # record the presence of warp1
+                      if ($row3[2]==$row3[0]) {$wrpcnt{$row2[3]}++;}  # record the presence of warp2
+                  }
+              }
+
+              if ($row2[4] > 0) {
+                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state 
+                  my $query3 = "     SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM ";
+                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
+                  $query3 .= "           WHERE diff_id='$row2[0]' GROUP BY diffRun.diff_id";
+                  my $result3 = &mysql_select ($query3);
+                  while (@row3 = $result3->fetchrow_array()) {
+                      #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published 
+                      if ($row3[1]<$row3[0]) {$NWSdiffgood ++;}
+                      if ($row3[1]==$row3[0]) {$NWSdiffbad ++;}
+                      if ($row3[2]==$row3[0]) {$NWSdiffpub ++;}
+                  }
+              }
+          }
+          #pull out the unique warp_ids
+          $Npubwarps = keys %wrpcnt;
+
+          #check for bad diffs 
+          if(($NWWdiffbad >= 1) or ($NWSdiffbad >= 1)) {$baddiffflag = 1;}
+
+          if ($Nvis1 ne $Nvis4) {
+            if ($NWWdiffpub ne $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk incomplete, not done";}  
+            if ($NWWdiffpub eq $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk incomplete, done";}
+          }
+          if ($Nvis1 eq $Nvis4) {
+            if ($NWWdiffpub ne $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk complete, not done";}
+            if ($NWWdiffpub eq $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk complete, done";}
+          }
+    }
+
+    print "\n";
+
+    #-----------------------------------------
+    #list bad quality cam exposures
+    if ($badcamflag != 0) {
+        my $query = "SELECT exp_name,cam_id, camProcessedExp.quality, camProcessedExp.sigma_ra, camProcessedExp.sigma_dec, 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"; 
+        $query .= " AND exp_type = 'OBJECT'";
+        $query .= " AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
+        $query .= " AND (camProcessedExp.quality > 0 OR camProcessedExp.sigma_ra > 5 OR camProcessedExp.sigma_dec > 5)";
+        $query .= " ORDER BY comment";
+
+        my $result = &mysql_select ($query);
+
+        $doHeader = 1; 
+
+        while (@row = $result->fetchrow_array()) {
+            if ($doHeader) {
+                print "Bad quality camRun exposures:\n";
+                printf "%11s | %7s | %7s | %9s | %9s | %15s\n", "exp_name","cam_id", "quality", "sigma_ra", "sigma_dec", "comment";
+                $doHeader = 0;
+	    }
+            printf "%11s | %7s | %7d | %9s | %9s | %15s\n", $row[0], $row[1], $row[2], $row[3], $row[4], $row[5]; 
+        }
+        if (!$doHeader) { print "\n"; }
+    }
+
+    #-----------------------------------------
+    #list bad quality warp exposures
+    if ($badwarpflag != 0) {
+        $query = "SELECT exp_name,warp_id,count(warpSkyfile.quality) AS Nwarpskycell,count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad, comment";
+        $query .= "      FROM rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) LEFT JOIN fakeRun USING (cam_id) LEFT JOIN warpRun USING (fake_id) JOIN warpSkyfile USING (warp_id)";
+        $query .= "      WHERE dateobs like '$date%' AND exp_id > $refExpID";
+        $query .= "      AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
+        $query .= "      GROUP BY exp_name ORDER BY comment";
+
+        my $result = &mysql_select ($query);
+
+        $doHeader = 1; 
+
+        while (@row = $result->fetchrow_array()) {
+    	if ($row[2] == $row[3]) {
+                if ($doHeader) {
+                    print "Bad quality warpRun exposures:\n";
+                    printf "%11s | %7s | %15s\n", "exp_name", "warp_id", "comment";
+                    $doHeader = 0;
+	        }
+                printf "%11s | %7d | %15s \n", $row[0], $row[1], $row[4]; 
+            }
+        }
+        if (!$doHeader) { print "\n"; }
+    }
+
+    #-----------------------------------------
+    #list bad quality diff exposures
+    if ($baddiffflag != 0) {
+        my $query = "     SELECT diff_id,warp1,stack1,warp2,stack2,exp_name,comment FROM ";
+        $query .= "           diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
+        $query .= "           WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
+        $query .= "           GROUP By diff_id";
+        my $result = &mysql_select ($query);
+
+        $doHeader = 1; 
+
+        while (@row = $result->fetchrow_array()) {
+            if ($doHeader) {
+                print "Bad quality diffRun exposures:\n";
+                printf "%11s | %7s | %7s | %7s | %7s | %7s | %15s\n", "exp_name", "diff_id", "warp1", "stack1", "warp2", "stack2", "comment";
+                $doHeader = 0;
+            }
+
+            #distinguish between WW and WS diffs based on stack2
+            if ($row[4] == 0) {
+                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state 
+                  my $query3 = "     SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM ";
+                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
+                  $query3 .= "           WHERE diff_id='$row[0]' GROUP BY diffRun.diff_id";
+                  my $result3 = &mysql_select ($query3);
+                  while (@row3 = $result3->fetchrow_array()) {
+                      #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published 
+                      if ($row3[1]==$row3[0]) {printf "%11s | %7d | %7d | %7d | %7d | %7d | %15s \n", $row[5], $row[0], $row[1], $row[2], $row[3], $row[4], $row[6];}
+                  }
+            }
+
+            if ($row[4] > 0) {
+                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state 
+                  my $query3 = "     SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM ";
+                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
+                  $query3 .= "           WHERE diff_id='$row[0]' GROUP BY diffRun.diff_id";
+                  my $result3 = &mysql_select ($query3);
+                  while (@row3 = $result3->fetchrow_array()) {
+                      #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published 
+                      if ($row3[1]==$row3[0]) {printf "%11s | %7d | %7d | %7d | %7d | %7d | %15s \n", $row[5], $row[0], $row[1], $row[2], $row[3], $row[4], $row[6];}
+                  }
+            }
+        }
+        if (!$doHeader) { print "\n"; }
+    }
+}
+
+
+print "\n\n\n";
+print "--- IPP Report for $dbname : $date ---\n\n";
+
+
+
 ### testing the diff grouping -- this does not work for the Euclid data taken 2018.07.29 because the OBJECT
 ### field is missing
@@ -574,5 +829,5 @@
            print "\n"; 
 	   print "Listing objects with 1< Nvisits < 4:\n"; 
-           printf "| %8s | %8s | %17s | %8s |\n", "object", "filter", "chunk", "Nvisit";
+           printf "| %12s | %8s | %19s | %8s |\n", "object", "filter", "chunk", "Nvisit";
 
            my $query = "SELECT * FROM";
@@ -594,9 +849,9 @@
            $query .= "     GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
            $query .= "   ) AS tableChunks GROUP BY object, filter, chunk";
-           $query .= " ) AS tableVisits WHERE Nvisit > 1 AND Nvisit < 4 ORDER BY Nvisit ";
+           $query .= " ) AS tableVisits WHERE Nvisit > 1 AND Nvisit < 4 ORDER BY chunk,Nvisit ";
            my $result = &mysql_select ($query);
 
            while (@row = $result->fetchrow_array()) {
-         	printf "| %8s | %8s | %17s | %8s |\n", $row[0], $row[1], $row[2], $row[3]; 
+         	printf "| %12s | %8s | %19s | %8s |\n", $row[0], $row[1], $row[2], $row[3]; 
            }
            print "\n"; 
@@ -864,5 +1119,5 @@
 
 # count the number of chunks, and their status:
-if ($verbose or $chunks) {
+if ($verbose or $report) {
     print "Current status of SS and Euclid chunks:\n";
 
@@ -937,4 +1192,5 @@
 
 
+
 exit 0;