Index: trunk/ippScripts/scripts/night_report.pl =================================================================== --- trunk/ippScripts/scripts/night_report.pl (revision 41199) +++ trunk/ippScripts/scripts/night_report.pl (revision 41200) @@ -79,9 +79,11 @@ #We want to stick to only MOPS products, so we do labels %SS%, %EU% and %Bright.% (to avoid BrightTwi and Bright3PI which are not in quads) #we need to do a subquery to find the total number of skycells in warpSkyfile, as well as the number of those with a bad quality - my $query = "SELECT suba.chunk,suba.Nvis1,suba.Nvis2,suba.Nvis3,suba.Nvis4,suba.Ncamgood,suba.Ncambad,subb.Nwarpgood,subb.Nwarpbad FROM "; + 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"; + $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"; @@ -101,5 +103,5 @@ my $result = &mysql_select ($query); - printf "%18s | %11s | %4s | %4s | %7s | %4s | %7s | %4s | %7s |\n", "chunk", "Nobs", "Ncam","Nwrp","Nwrp_pub", "Nwwd", "Nwwd_pub", "Nwsd", "Nwsd_pub"; + 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; @@ -114,4 +116,8 @@ my $Npubwarps = 0; my $uniwarps = undef; + my $uniNvis1 = 0; + my $uniNvis2 = 0; + my $uniNvis3 = 0; + my $uniNvis4 = 0; my $chunkname = $row[0]; @@ -124,17 +130,12 @@ 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 total published number of unique warps for this chunk - #my $query0 = " SELECT count(DISTINCT warpRun.warp_id) FROM "; - #$query0 .= " diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id) JOIN publishRun ON (diffInputSkyfile.diff_id = stage_id)"; - #$query0 .= " WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%')"; - #$query0 .= " AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL' AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' group by warpRun.warp_id"; - #my $result0 = &mysql_select ($query0); - #while (@row = $result0->fetchrow_array()) {$Npubwarps = $row[0];} - #Find the expected number of diffs for this chunk @@ -203,11 +204,11 @@ if(($NWWdiffbad >= 1) or ($NWSdiffbad >= 1)) {$baddiffflag = 1;} - if ($Nvis1 ne $Nvis4) { - if ($NWWdiffpub ne $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk incomplete, not done";} - if ($NWWdiffpub eq $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk incomplete, done";} + if (($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 ($Nvis1 eq $Nvis4) { - if ($NWWdiffpub ne $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk complete, not done";} - if ($NWWdiffpub eq $NexpWWdiff) {printf "%18s | %2s%1s%2s%1s%2s%1s%2s | %2s%1s%1s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %2s%1s%1s | %8s | %20s\n", $chunkname, $Nvis1, "/",$Nvis2, "/",$Nvis3, "/",$Nvis4,$Ncamgood, "/",$Ncambad,$Nwarpgood, "/",$Nwarpbad,$Npubwarps,$NWWdiffgood, "/",$NWWdiffbad,$NWWdiffpub,$NWSdiffgood, "/",$NWSdiffbad,$NWSdiffpub, "chunk complete, done";} + 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";} } } @@ -316,4 +317,50 @@ 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 "%11s | %9s | %15s\n", "exp_name", "dateobs", "comment"; + $doHeader = 0; + } + printf "%11s | %9s | %15s\n", $row2[0], $row2[1], $row2[2]; + } + $doskip = 1; + } + } + } + if (!$doHeader) { print "\n"; } + } @@ -1120,73 +1167,149 @@ # count the number of chunks, and their status: if ($verbose or $report) { - print "Current status of SS and Euclid chunks:\n"; - - #First find the number of chunks and their names - my $query = "SELECT groupchunk.chunk,groupchunk.Nquad_firstvisit,group4.Nquad_fourvisits FROM"; - $query .= " ("; - $query .= " SELECT dateobs,comment,substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nquad_firstvisit"; - $query .= " FROM rawExp"; - $query .= " WHERE dateobs LIKE '$date%' AND exp_id > $refExpID"; - $query .= " AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING'"; - $query .= " AND comment LIKE '%visit 1%'"; - $query .= " GROUP BY chunk"; - $query .= " ) AS groupchunk"; - $query .= " LEFT JOIN"; - $query .= " ("; - $query .= " SELECT groupobj.chunk,count(*) AS Nquad_fourvisits FROM "; - $query .= " (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nvisits"; - $query .= " FROM rawExp"; - $query .= " WHERE dateobs LIKE '$date%' AND exp_id > $refExpID AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')"; - $query .= " AND obs_mode NOT LIKE 'ENGINEERING' AND comment LIKE '%visit%'"; - $query .= " GROUP BY object, filter, chunk"; - $query .= " ) AS groupobj"; - $query .= " WHERE groupobj.Nvisits = 4 "; - $query .= " GROUP BY groupobj.chunk"; - $query .= " ) AS group4"; - $query .= " on groupchunk.chunk=group4.chunk ORDER BY dateobs"; + 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,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 "%18s | %13s | %13s | %14s |\n", "chunk", "Nobs_1stvisit", "Nobs_4visits", "Ndiffs_publish"; - while (@row = $result->fetchrow_array()) { - my $Nfirstvisits = 0; - my $Nfourvisits = 0; + 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) {$Nfirstvisits = $row[1]}; - if($row[2] gt 0) {$Nfourvisits = $row[2]}; - - my $query2 .= " SELECT warp_id FROM "; - $query2 .= " warpRun JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)"; - $query2 .= " WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')"; - $query2 .= " AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%'"; + 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 $Ndiffs = 0; + 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()) { - my $query3 .= " SELECT diff_id FROM "; - $query3 .= " diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id) JOIN warpRun ON (warp1=warp_id)"; - $query3 .= " WHERE stack2 IS NULL AND publishRun.state LIKE 'full' AND warp_id='$row2[0]' GROUP BY diff_id"; - my $result3 = &mysql_select ($query3); - while (@row3 = $result3->fetchrow_array()) {if ($row3[0] !=0) {$Ndiffs ++;}} - - my $query3 .= " SELECT diff_id FROM "; - $query3 .= " diffRun JOIN diffInputSkyfile USING (diff_id) JOIN publishRun ON (diff_id = stage_id) JOIN warpRun ON (warp2=warp_id)"; - $query3 .= " WHERE stack2 IS NULL AND publishRun.state LIKE 'full' AND warp_id='$row2[0]' GROUP BY diff_id"; - my $result3 = &mysql_select ($query3); - while (@row3 = $result3->fetchrow_array()) {if ($row3[0] !=0) {$Ndiffs ++;}} + #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 ++;} + } + } } - - if ($Nfirstvisits ne $Nfourvisits) { - if ($Ndiffs ne ($Nfirstvisits*4)) {printf "%18s | %13s | %13s | %10s%4s | %14s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs, "/? ", "chunk not done";} # chunk Nquad_firstvisit Nquad_fourvisits Ndiffs - if ($Ndiffs eq ($Nfirstvisits*4)) {printf "%18s | %13s | %13s | %10s%4s | %14s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs, "/? ", "chunk done";} # chunk Nquad_firstvisit Nquad_fourvisits Ndiffs + #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 ($Nfirstvisits eq $Nfourvisits) { - if ($Ndiffs ne ($Nfirstvisits*4)) {printf "%18s | %13s | %13s | %10s%1s%3s | %14s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs,"/",$Nfirstvisits*4, "chunk not done";} # chunk Nquad_firstvisit Nquad_fourvisits Ndiffs - if ($Ndiffs eq ($Nfirstvisits*4)) {printf "%18s | %13s | %13s | %10s%1s%3s | %14s\n", $chunkname, $Nfirstvisits, $Nfourvisits, $Ndiffs,"/",$Nfirstvisits*4, "chunk done";} # chunk Nquad_firstvisit Nquad_fourvisits Ndiffs + 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"; + }