Index: /branches/eam_branches/ipp-20211108/ippScripts/scripts/ipp_cleanup.pl =================================================================== --- /branches/eam_branches/ipp-20211108/ippScripts/scripts/ipp_cleanup.pl (revision 42067) +++ /branches/eam_branches/ipp-20211108/ippScripts/scripts/ipp_cleanup.pl (revision 42068) @@ -70,5 +70,5 @@ } -$ipprc->redirect_output($logfile) or +$ipprc->redirect_to_logfile($logfile) or &my_die("Unable to redirect ouput", $stage, $stage_id, $PS_EXIT_UNKNOWN_ERROR) if $logfile; Index: anches/eam_branches/ipp-20211108/ippScripts/scripts/night_report.pl =================================================================== --- /branches/eam_branches/ipp-20211108/ippScripts/scripts/night_report.pl (revision 42067) +++ (revision ) @@ -1,1428 +1,0 @@ -#!/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 - 'report' => \$report, # Formatted for ps-obs report - '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 ); - -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 = 'ippdb05'; -$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; - -# 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 (ALWAYS do this) -$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"; - } - if ($verbose2) { print "Reference exp_id: $refExpID\n"; } -} - -################## -# 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,suba.uniNvis1,suba.uniNvis2,suba.uniNvis3,suba.uniNvis4 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, COUNT(DISTINCT CASE WHEN rawExp.comment LIKE '%visit 1%' THEN rawExp.comment END) AS uniNvis1,"; - $query .= " COUNT(DISTINCT CASE WHEN rawExp.comment LIKE '%visit 2%' THEN rawExp.comment END) AS uniNvis2,COUNT(DISTINCT CASE WHEN rawExp.comment LIKE '%visit 3%' THEN rawExp.comment END) AS uniNvis3,"; - $query .= " COUNT(DISTINCT CASE WHEN rawExp.comment LIKE '%visit 4%' THEN rawExp.comment END) AS uniNvis4"; - $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 AND subc.Nwarpskycell > 0,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 "%30s | %11s | %5s | %5s | %7s | %5s | %7s | %5s | %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 $uniNvis1 = 0; - my $uniNvis2 = 0; - my $uniNvis3 = 0; - my $uniNvis4 = 0; - - 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]}; - if($row[9] gt 0) {$uniNvis1 = $row[9]}; - if($row[10] gt 0) {$uniNvis2 = $row[10]}; - if($row[11] gt 0) {$uniNvis3 = $row[11]}; - if($row[12] gt 0) {$uniNvis4 = $row[12]}; - - #set flags if present - if($Ncambad >= 1) {$badcamflag = 1}; - if($Nwarpbad >= 1) {$badwarpflag = 1}; - - #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 (($uniNvis1 ne $uniNvis2) or ($uniNvis1 ne $uniNvis3) or ($uniNvis1 ne $uniNvis4)) { - if ($NWWdiffpub ne $NexpWWdiff) {printf "%30s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%2s | %2s%1s%2s | %8s | %2s%1s%2s | %8s | %2s%1s%2s | %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 "%30s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%2s | %2s%1s%2s | %8s | %2s%1s%2s | %8s | %2s%1s%2s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk incomplete, done";} - } - if (($uniNvis1 eq $uniNvis2) and ($uniNvis1 eq $uniNvis3) and ($uniNvis1 eq $uniNvis4)) { - if ($NWWdiffpub ne $NexpWWdiff) {printf "%30s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%2s | %2s%1s%2s | %8s | %2s%1s%2s | %8s | %2s%1s%2s | %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 "%30s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%2s | %2s%1s%2s | %8s | %2s%1s%2s | %8s | %2s%1s%2s | %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 "%15s | %9s | %7s | %11s | %11s | %15s\n", "exp_name","cam_id", "quality", "sigma_ra", "sigma_dec", "comment"; - $doHeader = 0; - } - printf "%15s | %9s | %7d | %11s | %11s | %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] > 0) { - if ($row[2] == $row[3]) { - if ($doHeader) { - print "Bad quality warpRun exposures:\n"; - printf "%15s | %9s | %15s\n", "exp_name", "warp_id", "comment"; - $doHeader = 0; - } - printf "%15s | %9d | %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 "%15s | %9s | %9s | %9s | %9s | %9s | %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 "%15s | %9d | %9d | %9d | %9d | %9d | %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 "%15s | %9d | %9d | %9d | %9d | %9d | %15s \n", $row[5], $row[0], $row[1], $row[2], $row[3], $row[4], $row[6];} - } - } - } - if (!$doHeader) { print "\n"; } - } - - #----------------------------------------- - #check for overridden exposures - my $query = "SELECT COUNT(comment), comment"; - $query .= " FROM rawExp"; - $query .= " JOIN chipRun USING (exp_id)"; - $query .= " JOIN camRun USING (chip_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 .= " GROUP BY comment"; - - my $result = &mysql_select ($query); - - $doHeader = 1; - while (@row = $result->fetchrow_array()) { - if ($row[0] > 1) { - my $query = "SELECT exp_name,dateobs, comment"; - $query .= " FROM rawExp"; - $query .= " JOIN chipRun USING (exp_id)"; - $query .= " JOIN camRun USING (chip_id)"; - $query .= " WHERE dateobs LIKE '$date%' AND comment LIKE '$row[1]'"; - $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 .= " ORDER BY dateobs DESC"; - - my $result = &mysql_select ($query); - - $doskip = 0; - while (@row2 = $result->fetchrow_array()) { - if ($doskip) { - if ($doHeader) { - print "Overridden exposures:\n"; - printf "%15s | %11s | %15s\n", "exp_name", "dateobs", "comment"; - $doHeader = 0; - } - printf "%15s | %11s | %15s\n", $row2[0], $row2[1], $row2[2]; - } - $doskip = 1; - } - } - } - if (!$doHeader) { print "\n"; } - -} - - -print "\n\n\n"; -print "--- IPP Report for $dbname : $date ---\n\n"; - -###################### - -# check the status of summit exposures: -if (1) { - if ($verbose) { print "Exposures at summit:\n"; } - - $summitSkipped = 0; - $summitPending = 0; - - my $query = "SELECT exp_type, fault, state, count(*)"; - $query .= " FROM summitExp"; - $query .= " LEFT JOIN pzDownloadExp USING (summit_id)"; - $query .= " WHERE dateobs like '$date%'"; - $query .= " AND (state != 'drop' or state IS NULL)"; - $query .= " AND fault != 1042"; - $query .= " GROUP BY exp_type, fault, state"; - - my $result = &mysql_select ($query); - - if ($verbose) { printf "%8s %15s %6s %4s\n", "exp_type", "fault", "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 "run") { - $summitPending += $row[3]; - } - if ($row[2] eq "") { - $summitSkipped += $row[3]; - } - } - if ($summitSkipped || $summitPending) { - if ($verbose) {print "*** WARNING: $summitSkipped skipped rawExp, $summitPending incomplete rawExp ***\n\n";} - - my $query = "SELECT summitExp.exp_name,exp_type, fault, state"; - $query .= " FROM summitExp"; - $query .= " LEFT JOIN pzDownloadExp USING (summit_id)"; - $query .= " WHERE dateobs like '$date%'"; - $query .= " AND (state like 'new' or state IS NULL)"; - $query .= " AND fault != 1042"; - - my $result = &mysql_select ($query); - if ($verbose) {printf "%12s %8s %15s %6s\n", "exp_name", "exp_type", "fault", "state";} - while (@row = $result->fetchrow_array()) { - if ($row[3] eq "") { $row[3] = "NULL"; } - if ($verbose) {printf "%12s %8s %15s %6s\n", $row[0], $row[1], $row[2], $row[3];} - } - } - print "\n"; -} - -# check for exposures not sent to chip: -if ($verbose2) { - - my $query = "SELECT summitExp.exp_name, exp_type, fault, state"; - $query .= " FROM summitExp"; - $query .= " LEFT JOIN pzDownloadExp USING (summit_id)"; - $query .= " WHERE dateobs like '$date%'"; -# $query .= " AND (state != 'drop' or state IS NULL)"; - $query .= " AND fault != 1042"; - $query .= " AND state IS NULL"; - - print "$query\n"; - - 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 %14s\n", "exp_name", "exp_type", "fault", "state"; - $doHeader = 0; - } - printf "%11s %15s %14s %14s\n", $row[0], $row[1], $row[2], $row[3]; - } - if (! $doHeader) { print "\n"; } -} - -# check the status of exposures: -if (1) { - if ($report) { print "General exposure overview:\n"; } - if ($report) { print "Distribution is split between clean=0/1 (fullbundles vs catalog)\n"; } - - my $query = "SELECT exp_type, obs_mode,(CASE WHEN position(' ' in comment)>1 THEN substr(comment, 1, position(' ' in comment)-1) WHEN position('.' in comment)>1 THEN substr(comment, 1, position('.' in comment)-1) ELSE comment END) as cmt, state, count(*)"; - $query .= " FROM rawExp"; - $query .= " WHERE dateobs like '$date%'"; - $query .= " AND exp_id > $refExpID"; # REF - $query .= " GROUP BY exp_type, obs_mode,cmt"; - $query .= " ORDER BY exp_type, obs_mode"; - - my $result = &mysql_select ($query); - - if ($report) { printf "%8s | %12s | %30s | %7s | %7s | %7s | %9s %9s | %9s %13s |\n", "exp_type", "obs_mode","comment", "N(raw)", "N(chip)", "N(cam)", "N(wrp)", "N(wrpdist)", "N(WSdiff)", "N(WSdiffdist)"; } - while (@row = $result->fetchrow_array()) { - #query chip stage - my $query = "SELECT exp_type, obs_mode, chipRun.state, count(*)"; - $query .= " FROM rawExp"; - $query .= " JOIN chipRun using (exp_id)"; - $query .= " WHERE dateobs LIKE '$date%'"; - $query .= " AND exp_id > $refExpID"; #REF - $query .= " AND obs_mode LIKE '$row[1]'"; - if ($row[2] ne "") { - $query .= " AND comment LIKE BINARY '$row[2]%'"; - } - my $result2 = &mysql_select ($query); - @row2 = $result2->fetchrow_array(); - - #query cam stage - my $query = "SELECT exp_type, obs_mode, camRun.state, count(*)"; - $query .= " FROM rawExp 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 '$row[1]'"; - if ($row[2] ne "") { - $query .= " AND comment LIKE BINARY '$row[2]%'"; - } - $query .= " AND (camProcessedExp.quality IS NULL OR camProcessedExp.quality = 0)"; - - my $result3 = &mysql_select ($query); - @row3 = $result3->fetchrow_array(); - - #query warp stage - my $query = "SELECT exp_type, obs_mode, warpRun.state, count(DISTINCT warpRun.warp_id) AS Nwarp,count(if(distRun.state LIKE 'full' and distRun.stage LIKE 'warp' and distRun.clean = 0,1,NULL)) AS Ndist0,count(if(distRun.state LIKE 'full' and distRun.stage LIKE 'warp' and distRun.clean = 1,1,NULL)) AS Ndist1"; - $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 .= " JOIN warpRun using (fake_id)"; - $query .= " LEFT JOIN distRun ON (warp_id = stage_id)"; - $query .= " WHERE dateobs LIKE '$date%'"; - $query .= " AND exp_id > $refExpID"; #REF - $query .= " AND obs_mode LIKE BINARY '$row[1]'"; - if ($row[2] ne "") { - $query .= " AND comment LIKE '$row[2]%'"; - } - my $result4 = &mysql_select ($query); - @row4 = $result4->fetchrow_array(); - - #query warp-stack diff stage and distribution status - my $query = "SELECT diff_id,warp1,stack1,warp2,stack2"; - $query .= " FROM diffInputSkyfile"; - $query .= " JOIN warpRun ON warp1=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 .= " LEFT JOIN distRun ON (diffRun.diff_id = stage_id)"; - $query .= " WHERE rawExp.dateobs LIKE '$date%'"; - $query .= " AND exp_id > $refExpID"; #REF - $query .= " AND stack2 > 0"; - $query .= " AND obs_mode LIKE BINARY '$row[1]'"; - if ($row[2] ne "") { - $query .= " AND comment LIKE '$row[2]%'"; - } - $query .= " GROUP BY diff_id"; - - my $result5 = &mysql_select ($query); - #@row5 = $result5->fetchrow_array(); - - my $NWSdiffgood = 0; - my $NWSdiffbad = 0; - my $NWSdiffdist0 = 0; - my $NWSdiffdist1 = 0; - my %dfcnt = (); - - while (@row5 = $result5->fetchrow_array()) { - if ($row5[4] > 0) { - #find the total number of skycell and the number of skycell with a bad quality flag as well as the full dist state - my $query2 = "SELECT count(DISTINCT diffSkyfile.skycell_id) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(distRun.state LIKE 'full' and distRun.clean = 0,1,NULL)) AS Ndist0,count(if(distRun.state LIKE 'full' and distRun.clean = 1,1,NULL)) AS Ndist1 FROM "; - $query2 .= " diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN distRun ON (diffRun.diff_id = stage_id)"; - $query2 .= " WHERE diff_id='$row5[0]' AND distRun.stage LIKE 'diff' GROUP BY diffRun.diff_id"; - my $result6 = &mysql_select ($query2); - while (@row6 = $result6->fetchrow_array()) { - #printf("%6s,%2s,%2s,%2s,%2s, %3d %3d %3d %3d\n",$row5[0],$row6[0],$row6[1],$row6[2],$row6[3],$NWSdiffgood,$NWSdiffbad,$NWSdiffdist1,$NWSdiffdist2); - #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 ($row6[1]<$row6[0]) {$NWSdiffgood ++;} - if ($row6[1]==$row6[0]) {$NWSdiffbad ++;} - if ($row6[2]==$row6[0]) {$NWSdiffdist0 ++;} - if ($row6[3]==$row6[0]) {$NWSdiffdist1 ++;} - - #if this wsdiff is published, record its corresponding warp_ids to see how many unique ones there are - $dfcnt{$row5[0]}++; # record the presence of diff_id - } - } - } - #pull out the unique warp_ids - $Ndiffs = keys %dfcnt; - - if ($report) { printf "%8s | %12s | %30s | %7d | %7d | %7d | %9d %4d%1s%4d | %9d %6d%1s%6d |\n", $row[0], $row[1], $row[2], $row[4], $row2[3], $row3[3], $row4[3],$row4[4],'/',$row4[5],$Ndiffs,$NWSdiffdist0,'/',$NWSdiffdist1 ; } - } - print "\n"; -} - -# check the status of raw exposures: -if (1) { - if ($verbose or $report) { 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 or $report) { printf "%8s %15s %6s %4s\n", "exp_type", "obs_mode", "state", "N(exp)"; } - while (@row = $result->fetchrow_array()) { - if ($verbose or $report) { 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: -{ - if ($report) { - print "\n"; - } else { - 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%' OR obs_mode LIKE '%BRIGHT%')"; - $query .= " AND rawExp.comment NOT LIKE '%XSS%'"; - $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 = "tuple has"; } else { $verb = "tuples have"; } - printf "%3d %-11s 1 visit, no WWdiffs\n", $row[1], $verb; - } elsif ($row[0] == 2) { - if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples have"; } - printf "%3d %-11s 2 visits, expect $row[1] WWdiff(s)\n", $row[1], $verb; - $diffExpect += $row[1]; - } elsif ($row[0] == 3) { - if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples have"; } - printf "%3d %-11s 3 visits, expect %d WWdiffs (2 per tuple)\n", $row[1], $verb, 2 * $row[1]; - $diffExpect += $row[1]*2; - } elsif ($row[0] == 4) { - if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples have"; } - printf "%3d %-11s 4 visits, expect %d WWdiffs (2 per tuple)\n", $row[1], $verb, 2 * $row[1]; - $diffExpect += $row[1]*2; - } else { - # print "I don't understand, there are more visits than I expect\n"; - if ($row[1] == 1) { $verb = "tuple has"; } else { $verb = "tuples have"; } - if ($row[0] % 2) { $Ndiffs = int($row[0] / 2) + 1; } else { $Ndiffs = int($row[0] / 2); } - printf "%3d %-11s %d visits, expect %d WWdiffs (2 per quad)\n", $row[1], $verb, $row[0], $Ndiffs; - $diffExpect += $Ndiffs; - } - } - print "\n"; - print " * $diffExpect diffRuns expected *\n"; - if ($verbose) { print "\n"; } - - - -} - -# List chunks with duplicate visits -{ - # my $result = &mysql_select (""); -# my $query = "SELECT Nvisit, count(*) FROM"; -# $query .= " ("; - $query .= " SELECT object, filter, chunk, Nexp_in_chunk FROM"; - $query .= " ("; - $query .= " SELECT object, filter, substr(comment, 1, position(' ' in comment)) as chunk, count(*) as Nexp_in_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%' OR obs_mode LIKE '%BRIGHT%')"; - $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 where (Nexp_in_chunk > 1)"; -# $query .= " ) AS tableVisits GROUP BY Nvisit"; - - my $result = &mysql_select ($query); - - $doHeader = 1; - while (@row = $result->fetchrow_array()) { - if ($doHeader) { - print "chunks with duplicate visits:\n"; - printf "%11s %15s %14s %5s\n", "object", "filter", "chunk", "Nexp in chunk"; - $doHeader = 0; - } - printf "%11s %15s %14s %5s\n", $row[0], $row[1], $row[2], $row[3]; - } - if (! $doHeader) { print "\n"; } -} - -# List exposures which may be used for WWdiffs -if (0) { - print "List exposures for WWdiffs excluding camRuns with bad quality:\n"; - - # my $result = &mysql_select (""); - my $query = ""; - $query .= " SELECT 'EXAMPLE', exp_name, 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%' OR obs_mode LIKE '%BRIGHT%')"; - $query .= " AND rawExp.comment like '%visit%'"; - $query .= " AND (camProcessedExp.quality IS NULL or camProcessedExp.quality = 0)"; - - my $result = &mysql_select ($query); - &mysql_dump_result ($result); -} - -# 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 .= " AND label NOT LIKE 'goto_cleaned'"; - $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"; - - if ($verbose) { - print "Published diffs split by Nvisit :\n"; - printf "%8s %10s\n", "Nvisit", "N_pubdiff"; - - my $query = "SELECT Nvisit, SUM(Ndiffs) FROM "; - $query .= " (SELECT object, filter, chunk, count(*) as Nvisit,count(DISTINCT diff_id) as Ndiffs FROM"; - $query .= " (SELECT expall.object, expall.filter, expall.chunk as chunk, expall.exp_name,exppub.diff_id from"; - $query .= " ("; - $query .= " SELECT object, filter,exp_name, substr(comment, 1, position(' ' in comment)) as chunk, dateobs, warp_id"; - $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 .= " JOIN warpRun using (fake_id)"; - $query .= " WHERE dateobs like '$date%'"; - $query .= " AND exp_id > $refExpID"; - $query .= " AND (camProcessedExp.quality = 0)"; - $query .= " AND (comment NOT LIKE '%twilight%')"; - $query .= " ) AS expall"; - $query .= " JOIN"; - $query .= " (SELECT * FROM ("; - $query .= " (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM"; - $query .= " diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)"; - $query .= " JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)"; - $query .= " JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)"; - $query .= " WHERE rawExp.dateobs like '$date%' AND diffRun.label NOT LIKE '%WS.nigh%' AND diffRun.label NOT LIKE 'goto_cleaned'"; - $query .= " AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')"; - $query .= " UNION"; - $query .= " (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM"; - $query .= " diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)"; - $query .= " JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)"; - $query .= " JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)"; - $query .= " WHERE rawExp.dateobs like '$date%' AND diffRun.label NOT LIKE '%WS.nigh%' AND diffRun.label NOT LIKE 'goto_cleaned'"; - $query .= " AND stack2 IS NULL AND diffRun.data_group LIKE '%.$dataGroupDate')"; - $query .= " ) AS diffgather"; - $query .= " GROUP BY diffgather.exp_name)"; - $query .= " AS exppub"; - $query .= " ON expall.exp_name = exppub.exp_name"; - $query .= " ) AS pubdiffgather GROUP BY object, filter, chunk"; - $query .= " ) AS tableVisits GROUP BY Nvisit"; - - my $result = &mysql_select ($query); - while (@row = $result->fetchrow_array()) { - printf "%8s %10s\n", $row[0], $row[1]; - } - - #------------------------------------------------------------- - print "\n"; - print "Listing objects with 1< Nvisits < 4:\n"; - printf "| %12s | %8s | %19s | %8s |\n", "object", "filter", "chunk", "Nvisit"; - - 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 LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')"; - $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 WHERE Nvisit > 1 AND Nvisit < 4 ORDER BY chunk,Nvisit "; - my $result = &mysql_select ($query); - - while (@row = $result->fetchrow_array()) { - printf "| %12s | %8s | %19s | %8s |\n", $row[0], $row[1], $row[2], $row[3]; - } - print "\n"; - } - - } else { - if (not $report) { 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 label NOT LIKE 'goto_cleaned'"; - $query .= " AND label NOT LIKE '%test%'"; - $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(); - if (not $report) { 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 .= " AND diffRun.label NOT LIKE 'goto_cleaned'"; - $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, chipRun.workdir,exp_id,cam_id"; - $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 or $report) && $doHeader) { - print "Bad quality camRun exposures:\n"; - printf "%11s %7s | %15s\n", "exp_name", "quality", "comment"; - $doHeader = 0; - } - if ($verbose or $report) { - printf "%11s %7d | %15s\n", $row[0], $row[1], $row[2]; - my $loc = `neb-ls -p $row[3]/$row[0].$row[4]/$row[0].$row[4].cm.$row[5].log`; - my $faultline = `grep -e "Setting quality to $row[1]" $loc`; - print "$faultline"; - print "----------------------------------------------------------\n"; - } else { $NbadCam ++; } - } - if (($verbose or $report) && !$doHeader) { print "\n"; } - if (!($verbose or $report)) { print "$NbadCam bad quality camera exposures\n\n"; } -} - -# check the status of diff runs NOT for Solar System: -if ($verbose or $report) { - # 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%' AND obs_mode NOT LIKE '%BRIGHT%')"; - $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 "%16s %-8s %20s | %8s\n", "Object", "Filter", "Chunk", "N(visit)"; - $doHeader = 0; - } - printf "%16s %-8s %20s | %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, expall.warp_id, exppub.diff_id, exppub.pubstate from"; - $query .= " ("; - $query .= " SELECT exp_name, comment, dateobs, warp_id"; - $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 .= " JOIN warpRun using (fake_id)"; - $query .= " WHERE dateobs like '$date%'"; - $query .= " AND exp_id > $refExpID"; #REF - $query .= " AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')"; - $query .= " AND (camProcessedExp.quality = 0)"; - $query .= " AND (comment NOT LIKE '%twilight%')"; - $query .= " AND (comment NOT LIKE '%XSS%')"; - $query .= " ) AS expall"; - $query .= " LEFT JOIN"; - $query .= " (SELECT * FROM ("; - $query .= " (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM"; - $query .= " diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)"; - $query .= " JOIN warpRun ON (warp1=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)"; - $query .= " 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.data_group LIKE '%.$dataGroupDate')"; - $query .= " UNION"; - $query .= " (SELECT exp_name, warpRun.warp_id, diffRun.diff_id, publishRun.state as pubstate FROM"; - $query .= " diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id)"; - $query .= " JOIN warpRun ON (warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id)"; - $query .= " 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.data_group LIKE '%.$dataGroupDate')"; - $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 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 WW diffs (obs_mode like %SS% or obs_mode like %BRIGHT%, excluding XSS) with good camera quality :\n"; - printf "%12s %12s %12s | %-19s | %-16s\n", "exp_name", "warp_id", "diff_id", "dateobs", "comment"; - $doHeader = 0; - } - if ($row[3] eq "") { $row[3] = "NULL"; } - if ($row[4] eq "") { $row[4] = "NULL"; } - printf "%12s %12s %12s | %-19s | %-16s\n", $row[0], $row[3], $row[4], $row[1], $row[2] - } -} -print "\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%' OR obs_mode LIKE '%BRIGHT%')"; - $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"; -} - -# count the number of chunks, and their status: -if ($verbose or $report) { - print "Current status of SS, Euclid and BRIGHT chunks:\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 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,suba.uniNvis1,suba.uniNvis2,suba.uniNvis3,suba.uniNvis4 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, COUNT(DISTINCT CASE WHEN rawExp.comment LIKE '%visit 1%' THEN rawExp.comment END) AS uniNvis1,"; - $query .= " COUNT(DISTINCT CASE WHEN rawExp.comment LIKE '%visit 2%' THEN rawExp.comment END) AS uniNvis2,COUNT(DISTINCT CASE WHEN rawExp.comment LIKE '%visit 3%' THEN rawExp.comment END) AS uniNvis3,"; - $query .= " COUNT(DISTINCT CASE WHEN rawExp.comment LIKE '%visit 4%' THEN rawExp.comment END) AS uniNvis4"; - $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 AND exp_type = 'OBJECT' AND rawExp.comment like '%visit%'"; - $query .= " AND (obs_mode LIKE '%SS%' OR obs_mode 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 AND subc.Nwarpskycell > 0,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 AND exp_type = 'OBJECT' AND rawExp.comment like '%visit%'"; - $query .= " AND (obs_mode LIKE '%SS%' OR obs_mode 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 "%30s | %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 $uniNvis1 = 0; - my $uniNvis2 = 0; - my $uniNvis3 = 0; - my $uniNvis4 = 0; - - 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]}; - if($row[9] gt 0) {$uniNvis1 = $row[9]}; - if($row[10] gt 0) {$uniNvis2 = $row[10]}; - if($row[11] gt 0) {$uniNvis3 = $row[11]}; - if($row[12] gt 0) {$uniNvis4 = $row[12]}; - - #set flags if present - if($Ncambad >= 1) {$badcamflag = 1}; - if($Nwarpbad >= 1) {$badwarpflag = 1}; - - #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 exp_type = 'OBJECT' AND rawExp.comment like '%visit%' AND (obs_mode LIKE '%SS%' OR obs_mode 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 exp_type = 'OBJECT' AND rawExp.comment like '%visit%' AND (obs_mode LIKE '%SS%' OR obs_mode 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 (($uniNvis1 ne $uniNvis2) or ($uniNvis1 ne $uniNvis3) or ($uniNvis1 ne $uniNvis4)) { - if ($NWWdiffpub ne $NexpWWdiff) {printf "%30s | %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 "%30s | %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 (($uniNvis1 eq $uniNvis2) and ($uniNvis1 eq $uniNvis3) and ($uniNvis1 eq $uniNvis4)) { - if ($NWWdiffpub ne $NexpWWdiff) {printf "%30s | %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 "%30s | %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"; - -} - - - -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; -} Index: /branches/eam_branches/ipp-20211108/ippScripts/scripts/nightly_science.pl =================================================================== --- /branches/eam_branches/ipp-20211108/ippScripts/scripts/nightly_science.pl (revision 42067) +++ /branches/eam_branches/ipp-20211108/ippScripts/scripts/nightly_science.pl (revision 42068) @@ -1518,5 +1518,5 @@ my $this_quality = ${ $this_warp }[5]; my $this_state = ${ $this_warp }[4]; - if (($this_quality != 0) || ($this_state eq 'fail') ) { + if (($this_quality != 0) || ($this_state eq 'drop') ) { print STDERR "diff_queue: excluding $this_exp_id for $this_object due to non-zero cam.quality $this_quality or state $this_state\n"; } @@ -1708,5 +1708,5 @@ my $this_quality = ${ $this_warp }[5]; my $this_state = ${ $this_warp }[4]; - if (($this_quality != 0) || ($this_state eq 'fail') ) { + if (($this_quality != 0) || ($this_state eq 'drop') ) { print STDERR "desp_diff_singles: excluding $this_exp_id for $this_object due to non-zero cam.quality $this_quality or state $this_state\n"; } @@ -1925,5 +1925,5 @@ my $this_quality = ${ $this_warp }[5]; my $this_state = ${ $this_warp }[4]; - if (($this_quality != 0) || ($this_state eq 'fail') ) { + if (($this_quality != 0) || ($this_state eq 'drop') ) { print STDERR "desp_diff_queue: excluding $this_exp_id for $this_object due to non-zero cam.quality $this_quality or state $this_state\n"; } Index: /branches/eam_branches/ipp-20211108/ippScripts/scripts/warp_overlap.pl =================================================================== --- /branches/eam_branches/ipp-20211108/ippScripts/scripts/warp_overlap.pl (revision 42067) +++ /branches/eam_branches/ipp-20211108/ippScripts/scripts/warp_overlap.pl (revision 42068) @@ -130,5 +130,21 @@ my @matchlist = get_overlaps($astromFile, $tess_dir_abs, $astromAccept,$maxCerror); # List of overlaps if (! @matchlist) { - &my_die("Unable to perform dvoImageOverlaps: missing astrometry", $warp_id, $PS_EXIT_DATA_ERROR); + # OLD: &my_die("Unable to perform dvoImageOverlaps: missing astrometry", $warp_id, $PS_EXIT_DATA_ERROR); + warn("no overlaps found (bad astrometry); setting warpRun state to 'drop'\n"); + + # Add the processed file to the database + unless ($no_update) { + my $command = "$warptool -updaterun -set_state drop -warp_id $warp_id"; # Command to run warptool + $command .= " -dbname $dbname" if defined $dbname; + + my ( $success, $error_code, $full_buf, $stdout_buf, $stderr_buf ) = + run(command => $command, verbose => $verbose); + unless ($success) { + $error_code = (($error_code >> 8) or $PS_EXIT_PROG_ERROR); + warn("Unable to perform warptool -updaterun -set_state fail: $error_code\n"); + exit($error_code); + } + } + exit(0); } # Match each of the imfiles to this list (the input images may be split, but the astrometry is not) @@ -154,9 +170,9 @@ # we set the warpRun state to 'fail' since no warpSkyCellMap can be generated. if (scalar @overlaps == 0) { - warn("no overlaps found (bad astrometry); setting warpRun state to 'fail'\n"); + warn("no overlaps found (bad astrometry); setting warpRun state to 'drop'\n"); # Add the processed file to the database unless ($no_update) { - my $command = "$warptool -updaterun -set_state fail"; # Command to run warptool + my $command = "$warptool -updaterun -set_state drop -warp_id $warp_id"; # Command to run warptool $command .= " -dbname $dbname" if defined $dbname;