Changeset 40757 for trunk/ippScripts

Timestamp:

May 29, 2019, 9:26:18 AM (7 years ago)

Author:

tdeboer

Message:

script adjusted to handle duplicate comment strings

File:

• trunk/ippScripts/scripts/night_report.pl (modified) (3 diffs)

trunk/ippScripts/scripts/night_report.pl

@@ -771 +771 @@
     print "Current status of SS and Euclid chunks:\n";
 
-    #This is a long query, but it efficiently gathers all the necessary information
-    my $query = "SELECT groupchunk.chunk,groupchunk.Nquad_firstvisit,group4.Nquad_fourvisits,groupdiff.Ndiffs FROM";
+    #First find the number of chunks and their names
+    my $query = "SELECT groupchunk.chunk,groupchunk.Nquad_firstvisit,group4.Nquad_fourvisits FROM";
     $query .= "   (";
-    $query .= "     SELECT dateobs,comment,substr(comment, 1, position(' ' in comment)) AS chunk,count(*) AS Nquad_firstvisit";
+    $query .= "     SELECT dateobs,comment,substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nquad_firstvisit";
     $query .= "     FROM rawExp";
     $query .= "     WHERE dateobs LIKE '$date%'";
@@ -784 +784 @@
     $query .= "   (";
     $query .= "     SELECT groupobj.chunk,count(*) AS Nquad_fourvisits FROM ";
-    $query .= "       (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(*) AS Nvisits";
+    $query .= "       (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nvisits";
     $query .= "         FROM rawExp";
     $query .= "         WHERE dateobs LIKE '$date%' AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
@@ -794 +794 @@
     $query .= "   ) AS group4";
     $query .= "   on groupchunk.chunk=group4.chunk";
-    $query .= "   LEFT JOIN";
-    $query .= "   (";
-    $query .= "     SELECT substr(expall.comment, 1, position(' ' in expall.comment)) AS chunk,count(*) AS Ndiffs FROM ";
-    $query .= "       (SELECT exp_name,comment,dateobs";
-    $query .= "         FROM rawExp ";
-    $query .= "         WHERE dateobs LIKE '$date%' AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING'";
-    $query .= "       ) AS expall ";
-    $query .= "     LEFT JOIN";
-    $query .= "     (SELECT * FROM ";
-    $query .= "       (";
-    $query .= "         (SELECT exp_name FROM ";
-    $query .= "           diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id) JOIN warpRun ON (warp1=warp_id)";
-    $query .= "           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 (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
-    $query .= "           AND stack2 IS NULL AND diffRun.diff_id=stage_id";
-    $query .= "         )  ";
-    $query .= "         UNION ";
-    $query .= "         (SELECT exp_name FROM";
-    $query .= "           diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id) JOIN warpRun ON (warp2=warp_id)";
-    $query .= "           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 (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
-    $query .= "           AND stack2 IS NULL AND diffRun.diff_id=stage_id";
-    $query .= "         )";
-    $query .= "       ) AS diffgather ";
-    $query .= "       GROUP BY diffgather.exp_name";
-    $query .= "     ) AS exppub";
-    $query .= "     ON expall.exp_name=exppub.exp_name";
-    $query .= "     WHERE exppub.exp_name=expall.exp_name";
-    $query .= "     GROUP BY chunk ";
-    $query .= "   ) AS groupdiff";
-    $query .= "   ON groupchunk.chunk=groupdiff.chunk";
-
+ 
     my $result = &mysql_select ($query);
 
     printf "%18s | %18s | %18s | %18s |\n", "chunk", "Nquad_firstvisit", "Nquad_fourvisits", "Ndiffs done"; 
     while (@row = $result->fetchrow_array()) {
-          if ($row[1] ne $row[2]) {
-            if ($row[3] ne ($row[1]*4)) {printf "%18s | %18s | %18s | %14s%4s | %18s\n", $row[0], $row[1], $row[2], $row[3], "/?  ", "chunk not finished";}  # chunk    Nquad_firstvisit        Nquad_fourvisits        Ndiffs
-            if ($row[3] eq ($row[1]*4)) {printf "%18s | %18s | %18s | %14s%4s | %18s\n", $row[0], $row[1], $row[2], $row[3], "/?  ", "chunk done";}          # chunk    Nquad_firstvisit        Nquad_fourvisits        Ndiffs
+          my $chunkname = $row[0];
+          my $Nfirstvisits = $row[1];
+          my $Nfourvisits = $row[2];
+
+          my $query2 .= "     SELECT warp_id FROM ";
+          $query2 .= "           warpRun 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 (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
+          $query2 .= "           AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%'";
+          my $result2 = &mysql_select ($query2);
+
+          my $Ndiffs = 0;
+          while (@row2 = $result2->fetchrow_array()) {
+              my $query3 .= "     SELECT diff_id FROM ";
+              $query3 .= "           diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id) JOIN warpRun ON (warp1=warp_id)";
+              $query3 .= "           WHERE stack2 IS NULL AND warp_id='$row2[0]' GROUP BY diff_id";
+              my $result3 = &mysql_select ($query3);
+              while (@row3 = $result3->fetchrow_array()) {if ($row3[0] !=0) {$Ndiffs ++;}}
+
+              my $query3 .= "     SELECT diff_id FROM ";
+              $query3 .= "           diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id) JOIN warpRun ON (warp2=warp_id)";
+              $query3 .= "           WHERE stack2 IS NULL AND warp_id='$row2[0]' GROUP BY diff_id";
+              my $result3 = &mysql_select ($query3);
+              while (@row3 = $result3->fetchrow_array()) {if ($row3[0] !=0) {$Ndiffs ++;}}
           }
-          if ($row[1] eq $row[2]) {
-            if ($row[3] ne ($row[1]*4)) {printf "%18s | %18s | %18s | %14s%1s%3s | %18s\n", $row[0], $row[1], $row[2], $row[3],"/",$row[1]*4, "chunk not finished";}  # chunk   Nquad_firstvisit        Nquad_fourvisits        Ndiffs
-            if ($row[3] eq ($row[1]*4)) {printf "%18s | %18s | %18s | %14s%1s%3s | %18s\n", $row[0], $row[1], $row[2], $row[3],"/",$row[1]*4, "chunk done";}          # chunk   Nquad_firstvisit        Nquad_fourvisits        Ndiffs
+
+          if ($Nfirstvisits ne $Nfourvisits) {
+            if ($Ndiffs ne ($Nfirstvisits*4)) {printf "%18s | %18s | %18s | %14s%4s | %18s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs, "/?  ", "chunk not finished";}  # chunk        Nquad_firstvisit        Nquad_fourvisits        Ndiffs
+            if ($Ndiffs eq ($Nfirstvisits*4)) {printf "%18s | %18s | %18s | %14s%4s | %18s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs, "/?  ", "chunk done";}          # chunk        Nquad_firstvisit        Nquad_fourvisits        Ndiffs
+          }
+          if ($Nfirstvisits eq $Nfourvisits) {
+            if ($Ndiffs ne ($Nfirstvisits*4)) {printf "%18s | %18s | %18s | %14s%1s%3s | %18s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs,"/",$Nfirstvisits*4, "chunk not finished";}  # chunk Nquad_firstvisit        Nquad_fourvisits        Ndiffs
+            if ($Ndiffs eq ($Nfirstvisits*4)) {printf "%18s | %18s | %18s | %14s%1s%3s | %18s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs,"/",$Nfirstvisits*4, "chunk done";}          # chunk Nquad_firstvisit        Nquad_fourvisits        Ndiffs
           }
     }