Index: trunk/ippScripts/scripts/night_report_json.pl =================================================================== --- trunk/ippScripts/scripts/night_report_json.pl (revision 41338) +++ trunk/ippScripts/scripts/night_report_json.pl (revision 41338) @@ -0,0 +1,595 @@ +#!/usr/bin/env perl + +use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt ); +use Pod::Usage qw( pod2usage ); + +use DBI; +use JSON; +use POSIX; +use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock'; # Socket for mysql +use DateTime; + +# this saves the original arguments for later reporting +my @ARGS = @ARGV; + +GetOptions( + 'date=s' => \$date, + 'dbname|d=s' => \$dbname, # Database name + 'final' => \$final, # Final version for the day + 'verbose' => \$verbose, # Print output to screen + ) 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 = 'ippdb08'; +$dbname = 'gpc1' unless defined $dbname; +$dbuser = 'ippuser'; +$dbpass = 'ippuser'; + +# final report boolean +$final = 0 unless defined $final; + + +# 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 (1) { + if($verbose) {print "\n\n\n"}; + if($verbose) {print "--- MOPS report for $dbname : $date ---\n\n"}; + + my $badcamflag = 0; + my $badwarpflag = 0; + my $baddiffflag = 0; + + #compute the truncated julian date + my ($year,$month,$day) = split /-/, $date; + + #grab the current data/time in the ISO8601 format + my $datetimenow = DateTime->now()->iso8601().'Z'; + + # In leap years, -1 for Jan, Feb, else 0 + $L = POSIX::ceil(($month - 14) / 12); + + #convert to JD + $p1 = $day - 32075 + POSIX::floor (1461 * ($year + 4800 + $L) / 4); + $p2 = POSIX::floor (367 * ($month - 2 - $L * 12) / 12); + $p3 = 3 * POSIX::floor (POSIX::floor (($year + 4900 + $L) / 100) / 4); + $julian = $p1 + $p2 - $p3; + $julian = $julian + ($hr / 24.0) - 0.5; + $tjd = $julian - 2440000.5; + $tjd = $tjd - 10000; + + #add a JSON version number ("a.b.c". Bug fixes: c->c+1; Non-breaking changes in JSON format: b->b+1, c=0; Breaking changes in JSON format: a->a+1, b=0, c=0) + $version = "0.0.1"; + + my $struct ={ + date => $datetimenow, + tjd => $tjd, + telescope => $dbname, + version => $version, + final => $final + }; + + #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,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); + + if($verbose) {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]; + $chunkname =~ s/^\s+|\s+$//g; + 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) { + if($verbose) {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"}; + push @{ $struct->{chunk} }, { + chunkname => $chunkname, + Nvis1 => $Nvis1*1, + Nvis2 => $Nvis2*1, + Nvis3 => $Nvis3*1, + Nvis4 => $Nvis4*1, + Ncamgood => $Ncamgood*1, + Ncambad => $Ncambad*1, + Nwarpgood => $Nwarpgood*1, + Nwarpbad => $Nwarpbad*1, + Npubwarps => $Npubwarps*1, + NWWdiffgood => $NWWdiffgood*1, + NWWdiffbad => $NWWdiffbad*1, + NWWdiffpub => $NWWdiffpub*1, + NWSdiffgood => $NWSdiffgood*1, + NWSdiffbad => $NWSdiffbad*1, + NWSdiffpub => $NWSdiffpub*1, + ippstatus => "incomplete", + chunkcomp => "not done", + }; + } + if ($NWWdiffpub eq $NexpWWdiff) { + if($verbose) {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"}; + push @{ $struct->{chunk} }, { + chunkname => $chunkname, + Nvis1 => $Nvis1*1, + Nvis2 => $Nvis2*1, + Nvis3 => $Nvis3*1, + Nvis4 => $Nvis4*1, + Ncamgood => $Ncamgood*1, + Ncambad => $Ncambad*1, + Nwarpgood => $Nwarpgood*1, + Nwarpbad => $Nwarpbad*1, + Npubwarps => $Npubwarps*1, + NWWdiffgood => $NWWdiffgood*1, + NWWdiffbad => $NWWdiffbad*1, + NWWdiffpub => $NWWdiffpub*1, + NWSdiffgood => $NWSdiffgood*1, + NWSdiffbad => $NWSdiffbad*1, + NWSdiffpub => $NWSdiffpub*1, + ippstatus => "incomplete", + chunkcomp => "done", + }; + } + } + if (($uniNvis1 eq $uniNvis2) and ($uniNvis1 eq $uniNvis3) and ($uniNvis1 eq $uniNvis4)) { + if ($NWWdiffpub ne $NexpWWdiff) { + if($verbose) {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"}; + push @{ $struct->{chunk} }, { + chunkname => $chunkname, + Nvis1 => $Nvis1*1, + Nvis2 => $Nvis2*1, + Nvis3 => $Nvis3*1, + Nvis4 => $Nvis4*1, + Ncamgood => $Ncamgood*1, + Ncambad => $Ncambad*1, + Nwarpgood => $Nwarpgood*1, + Nwarpbad => $Nwarpbad*1, + Npubwarps => $Npubwarps*1, + NWWdiffgood => $NWWdiffgood*1, + NWWdiffbad => $NWWdiffbad*1, + NWWdiffpub => $NWWdiffpub*1, + NWSdiffgood => $NWSdiffgood*1, + NWSdiffbad => $NWSdiffbad*1, + NWSdiffpub => $NWSdiffpub*1, + ippstatus => "complete", + chunkcomp => "not done", + }; + + } + if ($NWWdiffpub eq $NexpWWdiff) { + if($verbose) {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"}; + push @{ $struct->{chunk} }, { + chunkname => $chunkname, + Nvis1 => $Nvis1*1, + Nvis2 => $Nvis2*1, + Nvis3 => $Nvis3*1, + Nvis4 => $Nvis4*1, + Ncamgood => $Ncamgood*1, + Ncambad => $Ncambad*1, + Nwarpgood => $Nwarpgood*1, + Nwarpbad => $Nwarpbad*1, + Npubwarps => $Npubwarps*1, + NWWdiffgood => $NWWdiffgood*1, + NWWdiffbad => $NWWdiffbad*1, + NWWdiffpub => $NWWdiffpub*1, + NWSdiffgood => $NWSdiffgood*1, + NWSdiffbad => $NWSdiffbad*1, + NWSdiffpub => $NWSdiffpub*1, + ippstatus => "complete", + chunkcomp => "done", + }; + + } + } + } + + if($verbose) {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()) { + push @{ $struct->{badcam} }, { + exp_name => $row[0], + cam_id => $row[1]*1, + quality => $row[2]*1, + sigma_ra => $row[3]*1, + sigma_dec => $row[4]*1, + comment => $row[5], + }; + + if ($doHeader) { + if($verbose) {print "Bad quality camRun exposures:\n"}; + if($verbose) {printf "%11s | %7s | %7s | %9s | %9s | %15s\n", "exp_name","cam_id", "quality", "sigma_ra", "sigma_dec", "comment"}; + $doHeader = 0; + } + if($verbose) {printf "%11s | %7s | %7d | %9s | %9s | %15s\n", $row[0], $row[1], $row[2], $row[3], $row[4], $row[5]}; + } + if (!$doHeader) { print "\n"; } + } + + #----------------------------------------- + #list bad quality warp exposures + if ($badwarpflag != 0) { + $query = "SELECT exp_name,warp_id,count(warpSkyfile.quality) AS Nwarpskycell,count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad, comment"; + $query .= " FROM rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) LEFT JOIN fakeRun USING (cam_id) LEFT JOIN warpRun USING (fake_id) JOIN warpSkyfile USING (warp_id)"; + $query .= " WHERE dateobs like '$date%' AND exp_id > $refExpID"; + $query .= " AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'"; + $query .= " GROUP BY exp_name ORDER BY comment"; + + my $result = &mysql_select ($query); + + $doHeader = 1; + + while (@row = $result->fetchrow_array()) { + if ($row[2] == $row[3]) { + push @{ $struct->{badwarp} }, { + exp_name => $row[0], + warp_id => $row[1]*1, + comment => $row[4], + }; + + if ($doHeader) { + if($verbose) {print "Bad quality warpRun exposures:\n"}; + if($verbose) {printf "%11s | %7s | %15s\n", "exp_name", "warp_id", "comment"}; + $doHeader = 0; + } + if($verbose) {printf "%11s | %7d | %15s \n", $row[0], $row[1], $row[4]}; + } + } + if (!$doHeader) { print "\n"; } + } + + #----------------------------------------- + #list bad quality diff exposures + if ($baddiffflag != 0) { + my $query = " SELECT diff_id,warp1,stack1,warp2,stack2,exp_name,comment FROM "; + $query .= " diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)"; + $query .= " WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID AND (camRun.label LIKE '%SS%' OR camRun.label LIKE '%EU%' OR camRun.label LIKE '%Bright.%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'"; + $query .= " GROUP By diff_id"; + my $result = &mysql_select ($query); + + $doHeader = 1; + + while (@row = $result->fetchrow_array()) { + if ($doHeader) { + if($verbose) {print "Bad quality diffRun exposures:\n"}; + if($verbose) {printf "%11s | %7s | %7s | %7s | %7s | %7s | %15s\n", "exp_name", "diff_id", "warp1", "stack1", "warp2", "stack2", "comment"}; + $doHeader = 0; + } + + #distinguish between WW and WS diffs based on stack2 + if ($row[4] == 0) { + #find the total number of skycell and the number of skycell with a bad quality flag as well as the full publish state + my $query3 = " SELECT count(diffSkyfile.quality) AS Ndiff,count(if(diffSkyfile.quality > 0,1,NULL)) AS Ndiffbad,count(if(publishRun.state LIKE 'full',1,NULL)) AS Npub FROM "; + $query3 .= " diffRun JOIN diffSkyfile USING (diff_id) LEFT JOIN publishRun ON (diffRun.diff_id = stage_id)"; + $query3 .= " WHERE diff_id='$row[0]' GROUP BY diffRun.diff_id"; + my $result3 = &mysql_select ($query3); + while (@row3 = $result3->fetchrow_array()) { + #if all skycells have bad quality, this is a bad diff. If all the skycells are in a full state in publishRun, it is fully published + if ($row3[1]==$row3[0]) { + if($verbose) {printf "%11s | %7d | %7d | %7d | %7d | %7d | %15s \n", $row[5], $row[0], $row[1], $row[2], $row[3], $row[4], $row[6]}; + + push @{ $struct->{badwwdiff} }, { + exp_name => $row[5], + diff_id => $row[0]*1, + warp1 => $row[1]*1, + stack1 => $row[2]*1, + warp2 => $row[3]*1, + stack2 => $row[4]*1, + comment => $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]) { + if($verbose) {printf "%11s | %7d | %7d | %7d | %7d | %7d | %15s \n", $row[5], $row[0], $row[1], $row[2], $row[3], $row[4], $row[6]}; + + push @{ $struct->{badwsdiff} }, { + exp_name => $row[5], + diff_id => $row[0]*1, + warp1 => $row[1]*1, + stack1 => $row[2]*1, + warp2 => $row[3]*1, + stack2 => $row[4]*1, + comment => $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) { + push @{ $struct->{overridden} }, { + exp_name => $row2[0], + dateobs => $row2[1], + comment => $row2[2], + }; + + if ($doHeader) { + if($verbose) {print "Overridden exposures:\n"}; + if($verbose) {printf "%11s | %9s | %15s\n", "exp_name", "dateobs", "comment"}; + $doHeader = 0; + } + if($verbose) {printf "%11s | %9s | %15s\n", $row2[0], $row2[1], $row2[2]}; + } + $doskip = 1; + } + } + } + if (!$doHeader) { print "\n"; } + + + my $jsonizer = JSON->new->pretty; + $jsonizer = $jsonizer->canonical(); + my $outname = "ippmopsreport_".$dbname."_".$tjd; + if($final) {$outname = $outname."_final"}; + $outname = $outname.".json"; + + open my $fh, ">", $outname; + print $fh $jsonizer->encode($struct); + close $fh; + + #Copy the file to the MOPS cluster nmops15 + my $cpcmd = "scp -r $outname ippmonitor\@nmops15.ifa.hawaii.edu:/home/ippmonitor/ippMopsReports/0.0.1/."; + system($cpcmd); + +} + +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; +}