IPP Software Navigation Tools IPP Links Communication Pan-STARRS Links

Ignore:
Timestamp:
Nov 7, 2019, 3:16:03 PM (7 years ago)
Author:
cclin33
Message:

sync from trunk through r40860

Location:
branches/ccl_branches/ipp-20190806
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • branches/ccl_branches/ipp-20190806

  • branches/ccl_branches/ipp-20190806/ippScripts/scripts/night_report.pl

    r40835 r41070  
    1717    'very-verbose'      => \$verbose2, # Print to stdout
    1818    'chunks'            => \$chunks,   # give information on the current status of the chunks
     19    'mopsreport'            => \$mopsreport,   # return a report in the new style requested by MOPS
    1920    ) or pod2usage( 2 );
    2021
    … …  
    4041$dataGroupDate=~ s/\.//g;
    4142
    42 print "\n\n\n";
    43 print "--- Report for $dbname : $date ---\n\n";
    44 
    4543# set up the database connection:
    4644my $dsn = "DBI:mysql:host=$dbhost;database=$dbname";
    … …  
    6967
    7068##################
     69# testing how to get more information parsed by chunk
     70if ($mopsreport or $report) {
     71    print "\n\n\n";
     72    print "--- MOPS report for $dbname : $date ---\n\n";
     73
     74    my $badcamflag = 0;
     75    my $badwarpflag = 0;
     76    my $baddiffflag = 0;
     77
     78    #do a query of raw/chip/cam/warp counts for this night, split by chunk.
     79    #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)
     80    #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
     81    my $query = "SELECT suba.chunk,suba.Nvis1,suba.Nvis2,suba.Nvis3,suba.Nvis4,suba.Ncamgood,suba.Ncambad,subb.Nwarpgood,subb.Nwarpbad FROM ";
     82    $query .= "    (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(if(rawExp.comment LIKE '%visit 1%',1,NULL)) AS Nvis1,";
     83    $query .= "    count(if(rawExp.comment LIKE '%visit 2%',1,NULL)) AS Nvis2,count(if(rawExp.comment LIKE '%visit 3%',1,NULL)) AS Nvis3,";
     84    $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,";
     85    $query .= "    count(if(camProcessedExp.sigma_ra > 5 OR camProcessedExp.sigma_dec > 5 OR camProcessedExp.quality > 0,1,NULL)) AS Ncambad";
     86    $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)";
     87    $query .= "    WHERE dateobs like '$date%' AND exp_id > $refExpID";
     88    $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'";
     89    $query .= "    GROUP BY chunk ORDER BY dateobs) as suba ";
     90    $query .= "LEFT JOIN";
     91    $query .= "    (SELECT subc.chunk, count(if(subc.Nwarpskycellbad < subc.Nwarpskycell,1,NULL)) AS Nwarpgood, count(if(subc.Nwarpskycellbad = subc.Nwarpskycell,1,NULL)) AS Nwarpbad";
     92    $query .= "    FROM ";
     93    $query .= "      (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(warpSkyfile.quality) AS Nwarpskycell,count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad";
     94    $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)";
     95    $query .= "      WHERE dateobs like '$date%' AND exp_id > $refExpID";
     96    $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'";
     97    $query .= "      GROUP BY exp_name) as subc";
     98    $query .= "    GROUP BY chunk) AS subb ";
     99    $query .= "ON suba.chunk=subb.chunk";
     100 
     101    my $result = &mysql_select ($query);
     102
     103    printf "%18s | %11s | %4s | %4s | %7s | %4s | %7s | %4s | %7s |\n", "chunk", "Nobs", "Ncam","Nwrp","Nwrp_pub", "Nwwd", "Nwwd_pub", "Nwsd", "Nwsd_pub";
     104    while (@row = $result->fetchrow_array()) {
     105          my $Nvis1 = 0;
     106          my $Nvis2 = 0;
     107          my $Nvis3 = 0;
     108          my $Nvis4 = 0;
     109          my $Ncamgood = 0;
     110          my $Ncambad = 0;
     111          my $Nwarpgood = 0;
     112          my $Nwarpbad = 0;
     113          my $NexpWWdiff = 0;
     114          my $Npubwarps = 0;
     115          my $uniwarps = undef;
     116
     117          my $chunkname = $row[0];
     118          if($row[1] gt 0) {$Nvis1 = $row[1]};
     119          if($row[2] gt 0) {$Nvis2 = $row[2]};
     120          if($row[3] gt 0) {$Nvis3 = $row[3]};
     121          if($row[4] gt 0) {$Nvis4 = $row[4]};
     122          if($row[5] gt 0) {$Ncamgood = $row[5]};
     123          if($row[6] gt 0) {$Ncambad = $row[6]};
     124          if($row[7] gt 0) {$Nwarpgood = $row[7]};
     125          if($row[8] gt 0) {$Nwarpbad = $row[8]};
     126
     127          #set flags if present
     128          if($Ncambad >= 1) {$badcamflag = 1};
     129          if($Nwarpbad >= 1) {$badwarpflag = 1};
     130
     131          #Find the total published number of unique warps for this chunk
     132          #my $query0 = "     SELECT count(DISTINCT warpRun.warp_id) FROM ";
     133          #$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)";
     134          #$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.%')";
     135          #$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";
     136          #my $result0 = &mysql_select ($query0);
     137          #while (@row = $result0->fetchrow_array()) {$Npubwarps = $row[0];}
     138
     139
     140          #Find the expected number of diffs for this chunk
     141          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";
     142          $query1 .= "    (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nvisits FROM";
     143          $query1 .= "    rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) WHERE dateobs like '$date%'";
     144          $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'";
     145          $query1 .= "    AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' group by object) as suba group by chunk;";
     146          my $result1 = &mysql_select ($query1);
     147          while (@row = $result1->fetchrow_array()) {$NexpWWdiff = $row[1] + $row[2];}
     148
     149          #find the unique diff_ids for each chunk, to be matched to the diffRun catalog
     150          my $query2 = "     SELECT diff_id,warp1,stack1,warp2,stack2 FROM ";
     151          $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)";
     152          $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.%')";
     153          $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";
     154          my $result2 = &mysql_select ($query2);
     155
     156          my $NWWdiffgood = 0;
     157          my $NWWdiffbad = 0;
     158          my $NWWdiffpub = 0;
     159
     160          my $NWSdiffgood = 0;
     161          my $NWSdiffbad = 0;
     162          my $NWSdiffpub = 0;
     163          my %wrpcnt = ();
     164
     165          while (@row2 = $result2->fetchrow_array()) {
     166              #distinguish between WW and WS diffs based on stack2
     167              if ($row2[4] == 0) {
     168                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state
     169                  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 ";
     170                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
     171                  $query3 .= "           WHERE diff_id='$row2[0]' GROUP BY diffRun.diff_id";
     172                  my $result3 = &mysql_select ($query3);
     173                  while (@row3 = $result3->fetchrow_array()) {
     174                      #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
     175                      if ($row3[1]<$row3[0]) {$NWWdiffgood ++;}
     176                      if ($row3[1]==$row3[0]) {$NWWdiffbad ++;}
     177                      if ($row3[2]==$row3[0]) {$NWWdiffpub ++;}
     178
     179                      #if this wwdiff is published, record its corresponding warp_ids to see how many unique ones there are
     180                      if ($row3[2]==$row3[0]) {$wrpcnt{$row2[1]}++;}  # record the presence of warp1
     181                      if ($row3[2]==$row3[0]) {$wrpcnt{$row2[3]}++;}  # record the presence of warp2
     182                  }
     183              }
     184
     185              if ($row2[4] > 0) {
     186                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state
     187                  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 ";
     188                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
     189                  $query3 .= "           WHERE diff_id='$row2[0]' GROUP BY diffRun.diff_id";
     190                  my $result3 = &mysql_select ($query3);
     191                  while (@row3 = $result3->fetchrow_array()) {
     192                      #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
     193                      if ($row3[1]<$row3[0]) {$NWSdiffgood ++;}
     194                      if ($row3[1]==$row3[0]) {$NWSdiffbad ++;}
     195                      if ($row3[2]==$row3[0]) {$NWSdiffpub ++;}
     196                  }
     197              }
     198          }
     199          #pull out the unique warp_ids
     200          $Npubwarps = keys %wrpcnt;
     201
     202          #check for bad diffs
     203          if(($NWWdiffbad >= 1) or ($NWSdiffbad >= 1)) {$baddiffflag = 1;}
     204
     205          if ($Nvis1 ne $Nvis4) {
     206            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";} 
     207            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";}
     208          }
     209          if ($Nvis1 eq $Nvis4) {
     210            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";}
     211            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";}
     212          }
     213    }
     214
     215    print "\n";
     216
     217    #-----------------------------------------
     218    #list bad quality cam exposures
     219    if ($badcamflag != 0) {
     220        my $query = "SELECT exp_name,cam_id, camProcessedExp.quality, camProcessedExp.sigma_ra, camProcessedExp.sigma_dec, comment";
     221        $query .= " FROM rawExp";
     222        $query .= " JOIN chipRun USING (exp_id)";
     223        $query .= " JOIN camRun USING (chip_id)";
     224        $query .= " JOIN camProcessedExp USING (cam_id)";
     225        $query .= " WHERE dateobs LIKE '$date%'";
     226        $query .= " AND exp_id > $refExpID";
     227        $query .= " AND exp_type = 'OBJECT'";
     228        $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'";
     229        $query .= " AND (camProcessedExp.quality > 0 OR camProcessedExp.sigma_ra > 5 OR camProcessedExp.sigma_dec > 5)";
     230        $query .= " ORDER BY comment";
     231
     232        my $result = &mysql_select ($query);
     233
     234        $doHeader = 1;
     235
     236        while (@row = $result->fetchrow_array()) {
     237            if ($doHeader) {
     238                print "Bad quality camRun exposures:\n";
     239                printf "%11s | %7s | %7s | %9s | %9s | %15s\n", "exp_name","cam_id", "quality", "sigma_ra", "sigma_dec", "comment";
     240                $doHeader = 0;
     241            }
     242            printf "%11s | %7s | %7d | %9s | %9s | %15s\n", $row[0], $row[1], $row[2], $row[3], $row[4], $row[5];
     243        }
     244        if (!$doHeader) { print "\n"; }
     245    }
     246
     247    #-----------------------------------------
     248    #list bad quality warp exposures
     249    if ($badwarpflag != 0) {
     250        $query = "SELECT exp_name,warp_id,count(warpSkyfile.quality) AS Nwarpskycell,count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad, comment";
     251        $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)";
     252        $query .= "      WHERE dateobs like '$date%' AND exp_id > $refExpID";
     253        $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'";
     254        $query .= "      GROUP BY exp_name ORDER BY comment";
     255
     256        my $result = &mysql_select ($query);
     257
     258        $doHeader = 1;
     259
     260        while (@row = $result->fetchrow_array()) {
     261        if ($row[2] == $row[3]) {
     262                if ($doHeader) {
     263                    print "Bad quality warpRun exposures:\n";
     264                    printf "%11s | %7s | %15s\n", "exp_name", "warp_id", "comment";
     265                    $doHeader = 0;
     266                }
     267                printf "%11s | %7d | %15s \n", $row[0], $row[1], $row[4];
     268            }
     269        }
     270        if (!$doHeader) { print "\n"; }
     271    }
     272
     273    #-----------------------------------------
     274    #list bad quality diff exposures
     275    if ($baddiffflag != 0) {
     276        my $query = "     SELECT diff_id,warp1,stack1,warp2,stack2,exp_name,comment FROM ";
     277        $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)";
     278        $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'";
     279        $query .= "           GROUP By diff_id";
     280        my $result = &mysql_select ($query);
     281
     282        $doHeader = 1;
     283
     284        while (@row = $result->fetchrow_array()) {
     285            if ($doHeader) {
     286                print "Bad quality diffRun exposures:\n";
     287                printf "%11s | %7s | %7s | %7s | %7s | %7s | %15s\n", "exp_name", "diff_id", "warp1", "stack1", "warp2", "stack2", "comment";
     288                $doHeader = 0;
     289            }
     290
     291            #distinguish between WW and WS diffs based on stack2
     292            if ($row[4] == 0) {
     293                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state
     294                  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 ";
     295                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
     296                  $query3 .= "           WHERE diff_id='$row[0]' GROUP BY diffRun.diff_id";
     297                  my $result3 = &mysql_select ($query3);
     298                  while (@row3 = $result3->fetchrow_array()) {
     299                      #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
     300                      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];}
     301                  }
     302            }
     303
     304            if ($row[4] > 0) {
     305                  #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state
     306                  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 ";
     307                  $query3 .= "           diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)";
     308                  $query3 .= "           WHERE diff_id='$row[0]' GROUP BY diffRun.diff_id";
     309                  my $result3 = &mysql_select ($query3);
     310                  while (@row3 = $result3->fetchrow_array()) {
     311                      #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
     312                      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];}
     313                  }
     314            }
     315        }
     316        if (!$doHeader) { print "\n"; }
     317    }
     318}
     319
     320
     321print "\n\n\n";
     322print "--- IPP Report for $dbname : $date ---\n\n";
     323
     324
     325
    71326### testing the diff grouping -- this does not work for the Euclid data taken 2018.07.29 because the OBJECT
    72327### field is missing
    … …  
    433688    print " * $diffExpect diffRuns expected *\n";
    434689    if ($verbose) { print "\n"; }
     690
     691
     692
    435693}
    436694
    … …  
    520778    if ($diffDone != $diffExpect) {
    521779        print "*** WARNING: Expect $diffExpect diffRuns, complete $diffDone diffRuns: inconsistent ***\n";
     780
     781        if ($verbose) {
     782           print "Published diffs split by Nvisit :\n";
     783           printf "%8s %10s\n", "Nvisit", "N_pubdiff";
     784
     785           my $query = "SELECT Nvisit, SUM(Ndiffs) FROM ";
     786           $query .= " (SELECT object, filter, chunk, count(*) as Nvisit,count(DISTINCT diff_id) as Ndiffs FROM";
     787           $query .= "  (SELECT expall.object, expall.filter, expall.chunk as chunk, expall.exp_name,exppub.diff_id from";
     788           $query .= "  (";
     789           $query .= "    SELECT object, filter,exp_name, substr(comment, 1, position(' ' in comment)) as chunk, dateobs, warp_id";
     790           $query .= "    FROM rawExp";
     791           $query .= "    JOIN chipRun using (exp_id)";
     792           $query .= "    JOIN camRun using (chip_id)";
     793           $query .= "    JOIN camProcessedExp using (cam_id)";
     794           $query .= "    JOIN fakeRun using (cam_id)";
     795           $query .= "    JOIN warpRun using (fake_id)";
     796           $query .= "    WHERE dateobs like '$date%'";
     797           $query .= "    AND exp_id > $refExpID";
     798           $query .= "    AND (camProcessedExp.quality = 0)";
     799           $query .= "    AND (comment NOT LIKE '%twilight%')";
     800           $query .= "  ) AS expall";
     801           $query .= "  JOIN";
     802           $query .= "  (SELECT * FROM (";
     803           $query .= "  (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM";
     804           $query .= "    diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)";
     805           $query .= "    JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)";
     806           $query .= "    JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
     807           $query .= "    WHERE rawExp.dateobs like '$date%' AND diffRun.label NOT LIKE '%WS.nigh%' AND diffRun.label NOT LIKE 'goto_cleaned'";
     808           $query .= "    AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')";
     809           $query .= "  UNION";
     810           $query .= "  (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM";
     811           $query .= "    diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)";
     812           $query .= "    JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)";
     813           $query .= "    JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
     814           $query .= "    WHERE rawExp.dateobs like '$date%' AND diffRun.label NOT LIKE '%WS.nigh%' AND diffRun.label NOT LIKE 'goto_cleaned'";
     815           $query .= "    AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')";
     816           $query .= "  ) AS diffgather";
     817           $query .= "  GROUP BY diffgather.exp_name)";
     818           $query .= "  AS exppub";
     819           $query .= " ON expall.exp_name = exppub.exp_name";
     820           $query .= " ) AS pubdiffgather GROUP BY object, filter, chunk";
     821           $query .= " ) AS tableVisits GROUP BY Nvisit";
     822
     823           my $result = &mysql_select ($query);
     824           while (@row = $result->fetchrow_array()) {
     825                printf "%8s %10s\n", $row[0], $row[1];
     826           }
     827
     828           #-------------------------------------------------------------
     829           print "\n";
     830           print "Listing objects with 1< Nvisits < 4:\n";
     831           printf "| %12s | %8s | %19s | %8s |\n", "object", "filter", "chunk", "Nvisit";
     832
     833           my $query = "SELECT * FROM";
     834           $query .= " (";
     835           $query .= "   SELECT object, filter, chunk, count(*) as Nvisit FROM";
     836           $query .= "   (";
     837           $query .= "     SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk";
     838           $query .= "     FROM rawExp";
     839           $query .= "     LEFT JOIN chipRun using (exp_id)";
     840           $query .= "     LEFT JOIN camRun using (chip_id)";
     841           $query .= "     LEFT JOIN camProcessedExp using (cam_id)";
     842           $query .= "     WHERE dateobs LIKE '$date%'";
     843           $query .= "     AND exp_id > $refExpID"; #REF
     844           $query .= "     AND exp_type = 'OBJECT'";
     845           $query .= "     AND obs_mode NOT LIKE 'ENGINEERING'";
     846           $query .= "     AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
     847           $query .= "     AND rawExp.comment like '%visit%'";
     848           $query .= "     AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)";
     849           $query .= "     GROUP BY comment, filter, substr(comment, 1, position(' ' in comment))";
     850           $query .= "   ) AS tableChunks GROUP BY object, filter, chunk";
     851           $query .= " ) AS tableVisits WHERE Nvisit > 1 AND Nvisit < 4 ORDER BY chunk,Nvisit ";
     852           my $result = &mysql_select ($query);
     853
     854           while (@row = $result->fetchrow_array()) {
     855                printf "| %12s | %8s | %19s | %8s |\n", $row[0], $row[1], $row[2], $row[3];
     856           }
     857           print "\n";
     858        }
     859
    522860    } else {
    523861        if (not $report) { print " * $diffDone of $diffExpect diffRuns completed *\n";  }
    … …  
    7811119
    7821120# count the number of chunks, and their status:
    783 if ($verbose or $chunks) {
     1121if ($verbose or $report) {
    7841122    print "Current status of SS and Euclid chunks:\n";
    7851123
    … …  
    8541192
    8551193
     1194
    8561195exit 0;
    8571196
Note: See TracChangeset for help on using the changeset viewer.