Index: trunk/ippMonitor/raw/czartool_labels.php =================================================================== --- trunk/ippMonitor/raw/czartool_labels.php (revision 41133) +++ trunk/ippMonitor/raw/czartool_labels.php (revision 41134) @@ -214,176 +214,105 @@ echo ""; -///////////////////////////////////////// -// timer begin for status and links -///////////////////////////////////////// -$time = microtime(); -$time = explode(' ', $time); -$time = $time[1] + $time[0]; -$start = $time; +#echo "
"; // status table at top $nsStatus = getNightlyScienceStatus($czardb, $proj); $plotTypeLink = ($plotType == "linear") ? "log" : "linear"; -$link = "czartool_labels.php?pass=" . $pass . "&proj=" . $proj . "&label=" . $selectedLabel . - "&stage=" . $selectedStage . "&revertstage=" . $stage . "&plottype=$plotTypeLink"; - +$link = "czartool_labels.php?pass=" . $pass . "&proj=" . $proj . "&label=" . $selectedLabel . "&stage=" . $selectedStage . "&revertstage=" . $stage . "&plottype=$plotTypeLink"; echo "
"; echo $table; -createTableTitle("Status and links", 2); + +createTableTitle("Status and links", 3); echo "\n"; -echo "Time of last update - $lastUpdateTime"; -echo "ippMonitor SVN version - "; include 'version.php'; echo ""; -echo "Current nightly science status - $nsStatus"; -echo "Postage stamp server status - here"; -echo "Documentation - here"; -echo "Cluster load - - here - - cpu_report - - mem_report"; -echo "IPP metrics - here"; -echo "Czar log pages - here"; -echo "Exposure summary - here"; -echo "Confluence - here"; -echo "JIRA - here"; -echo "data store - here"; +echo "Time of last update$lastUpdateTime"; +echo "ippMonitor SVN version"; include 'version.php'; echo ""; +echo "Current nightly science status$nsStatus"; +echo "Postage stamp server statushere"; +echo "Documentationhere"; +echo "Cluster loadhere cpu_report mem_report"; +echo "IPP metricshere"; +echo "Czar log pageshere"; +echo "Exposure summaryhere"; +echo "Confluencehere"; +echo "JIRAhere"; $plotTypeLink = ($plotType == "linear") ? "log" : "linear"; -$link = "czartool_labels.php?pass=" . $pass . "&proj=" . $proj . "&mode=" . $selectedMode . - "&label=" . $selectedLabel . "&stage=" . $selectedStage . "&revertstage=" . $stage . "&plottype=$plotTypeLink"; - -// timer finished -echo ""; -$time = microtime(); -$time = explode(' ', $time); -$time = $time[1] + $time[0]; -$finish = $time; -$total_time = round(($finish - $start), 3); -echo "loading in $total_time seconds."; -echo ""; -echo "
"; - -createLabelsTable($pass, $proj, $czardb, $selectedMode, $labels, - $distLabelsExist, $pubLabelsExist, $stages, "new", $selectedLabel, - $selectedStage, $plotType); -if ($selectedMode == "update") {echo "
"; createPStampDataTable();} - -# XXX EAM : 2017.09.17 : debugging slowness -# CCL : 2019.11.09 : debugging slowness -//createSummitDataTable($projectdb); // this is slow because of the join between summitExp and rawExp, but not the major one -createSummitDataTable2($projectdb); // this is slow because of the join between summitExp and rawExp, but not the major one - -createServersTable($pass, $proj,$selectedMode,$czardb, $servers, $selectedLabel, $selectedStage, $plotType); -// this was the major slowness part due to large table in czardb - -createChunkDataTable($projectdb); // this is another part for slowness during nightly processing - -createDatesTable($czardb, $proj); - -///////////////////////////////////////// -// timer begin for Database status -///////////////////////////////////////// -$time = microtime(); -$time = explode(' ', $time); -$time = $time[1] + $time[0]; -$start = $time; - -// status table at top - echo $table; - echo ""; - createTableTitle("Database status (update fix log here)", 2); - - echo ""; - createTableColumnHeader("Database"); - createTableColumnHeader("Status (sec behind master)"); - showDatabaseStatus($HOST_GPC1, $USER_GPC1, $PASSWORD_GPC1, $DBNAME_GPC1); - showReplicationsStatus($REPL_HOST_GPC1, $REPL_USER_GPC1, $REPL_PASSWORD_GPC1, $REPL_DBNAME_GPC1); - showReplicationsStatus($REPL_HOST_GPC1_SECONDARY, $REPL_USER_GPC1_SECONDARY, $REPL_PASSWORD_GPC1_SECONDARY, $REPL_DBNAME_GPC1_SECONDARY); - - echo ""; - showDatabaseStatus($HOST_NEBULOUS, $USER_NEBULOUS, $PASSWORD_NEBULOUS, $DBNAME_NEBULOUS); - showReplicationsStatus($REPL_HOST_NEBULOUS, $REPL_USER_NEBULOUS, $REPL_PASSWORD_NEBULOUS, $REPL_DBNAME_NEBULOUS); - showReplicationsStatus($REPL_HOST_NEBULOUS_SECONDARY, $REPL_USER_NEBULOUS_SECONDARY, $REPL_PASSWORD_NEBULOUS_SECONDARY, $REPL_DBNAME_NEBULOUS_SECONDARY); - showReplicationsStatus($REPL_HOST_DATASTORE_SECONDARY, $REPL_USER_DATASTORE_SECONDARY, $REPL_PASSWORD_DATASTORE_SECONDARY, $REPL_DBNAME_DATASTORE_SECONDARY); - -// some mysql replication slaves to add when they are returned to service -// showReplicationsStatus($REPL_HOST_PSTAMP, $REPL_USER_PSTAMP, $REPL_PASSWORD_PSTAMP, $REPL_DBNAME_PSTAMP); -// showReplicationsStatus($REPL_HOST_ISP, $REPL_USER_ISP, $REPL_PASSWORD_ISP, $REPL_DBNAME_ISP); - -// timer finished -echo ""; -$time = microtime(); -$time = explode(' ', $time); -$time = $time[1] + $time[0]; -$finish = $time; -$total_time = round(($finish - $start), 3); -echo "loading in $total_time seconds."; -echo ""; -echo "
"; - -///////////////////////////////////////// -// timer begin for critical machines -///////////////////////////////////////// -$time = microtime(); -$time = explode(' ', $time); -$time = $time[1] + $time[0]; -$start = $time; - -// status table at top - $PingStatus = "DOWN"; - echo $table; - echo ""; - createTableTitle("Critical machine status", 3); - - echo ""; - createTableColumnHeader("Hostname"); - createTableColumnHeader("Status"); - createTableColumnHeader("Loading time"); - showPingStatus($HOST_GATEWAY, $DBNAME_GATEWAY); - showPingStatus($HOST_GATEWAY_SECONDARY, $DBNAME_GATEWAY_SECONDARY); - showPingStatus($HOST_APACHE1, $DBNAME_APACHE1); - showPingStatus($HOST_APACHE2, $DBNAME_APACHE2); - showPingStatus($HOST_APACHE3, $DBNAME_APACHE3); - showPingStatus($HOST_APACHE4, $DBNAME_APACHE4); - showPingStatus($HOST_APACHE5, $DBNAME_APACHE5); - showPingStatus($HOST_APACHE6, $DBNAME_APACHE6); - showPingStatus($HOST_APACHE7, $DBNAME_APACHE7); - showPingStatus($HOST_APACHE8, $DBNAME_APACHE8); -// echo "ippc70 (apache node) $PingStatus 0 seconds "; // comment out if ippc70 is back - showPingStatus($HOST_PROXY, $DBNAME_PROXY); - showPingStatus($HOST_SVN, $DBNAME_SVN); - showPingStatus($HOST_CONFLUENCE, $DBNAME_CONFLUENCE); - -// timer finished -echo ""; -$time = microtime(); -$time = explode(' ', $time); -$time = $time[1] + $time[0]; -$finish = $time; -$total_time = round(($finish - $start), 3); -echo "loading in $total_time seconds."; -echo ""; -echo "
"; - - +$link = "czartool_labels.php?pass=" . $pass . "&proj=" . $proj . "&mode=" . $selectedMode . "&label=" . $selectedLabel . "&stage=" . $selectedStage . "&revertstage=" . $stage . "&plottype=$plotTypeLink"; + +echo "\n"; + +echo "
"; + + createLabelsTable($pass, + $proj, + $czardb, + $selectedMode, + $labels, + $distLabelsExist, + $pubLabelsExist, + $stages, + "new", + $selectedLabel, + $selectedStage, + $plotType); + + if ($selectedMode == "update") {echo "
"; createPStampDataTable();} + echo "
"; + + # XXX EAM : 2017.09.17 : debugging slowness + createServersTable($pass, $proj,$selectedMode,$czardb, $servers, $selectedLabel, $selectedStage, $plotType); echo "
"; + createSummitDataTable($projectdb); echo "
"; // this is slow because of the join between summitExp and rawExp + createChunkDataTable($projectdb); echo "
"; + createDatesTable($czardb, $proj); echo "
"; + + echo $table; + echo ""; + createTableTitle("Database status (update fix log here)", 2); + + echo ""; + createTableColumnHeader("Database"); + createTableColumnHeader("Status (sec behind master)"); + showDatabaseStatus($HOST_GPC1, $USER_GPC1, $PASSWORD_GPC1, $DBNAME_GPC1); + showReplicationsStatus($REPL_HOST_GPC1, $REPL_USER_GPC1, $REPL_PASSWORD_GPC1, $REPL_DBNAME_GPC1); + showReplicationsStatus($REPL_HOST_GPC1_SECONDARY, $REPL_USER_GPC1_SECONDARY, $REPL_PASSWORD_GPC1_SECONDARY, $REPL_DBNAME_GPC1_SECONDARY); + + echo ""; + + showDatabaseStatus($HOST_NEBULOUS, $USER_NEBULOUS, $PASSWORD_NEBULOUS, $DBNAME_NEBULOUS); + showReplicationsStatus($REPL_HOST_NEBULOUS, $REPL_USER_NEBULOUS, $REPL_PASSWORD_NEBULOUS, $REPL_DBNAME_NEBULOUS); + showReplicationsStatus($REPL_HOST_NEBULOUS_SECONDARY, $REPL_USER_NEBULOUS_SECONDARY, $REPL_PASSWORD_NEBULOUS_SECONDARY, $REPL_DBNAME_NEBULOUS_SECONDARY); + showReplicationsStatus($REPL_HOST_DATASTORE_SECONDARY, $REPL_USER_DATASTORE_SECONDARY, $REPL_PASSWORD_DATASTORE_SECONDARY, $REPL_DBNAME_DATASTORE_SECONDARY); + +# some mysql replication slaves to add when they are returned to service +# showReplicationsStatus($REPL_HOST_PSTAMP, $REPL_USER_PSTAMP, $REPL_PASSWORD_PSTAMP, $REPL_DBNAME_PSTAMP); +# showReplicationsStatus($REPL_HOST_ISP, $REPL_USER_ISP, $REPL_PASSWORD_ISP, $REPL_DBNAME_ISP); + echo ""; + + echo $table; + echo ""; + createTableTitle("Critical machine status", 2); + + echo ""; + createTableColumnHeader("Hostname"); + createTableColumnHeader("Status"); + showPingStatus($HOST_GATEWAY, $DBNAME_GATEWAY); + showPingStatus($HOST_GATEWAY_SECONDARY, $DBNAME_GATEWAY_SECONDARY); + +# showPingStatus($HOST_APACHE1, $DBNAME_APACHE1); + showPingStatus($HOST_APACHE2, $DBNAME_APACHE2); + showPingStatus($HOST_APACHE3, $DBNAME_APACHE3); + showPingStatus($HOST_APACHE4, $DBNAME_APACHE4); + showPingStatus($HOST_APACHE5, $DBNAME_APACHE5); + showPingStatus($HOST_APACHE6, $DBNAME_APACHE6); + showPingStatus($HOST_APACHE7, $DBNAME_APACHE7); + showPingStatus($HOST_PROXY, $DBNAME_PROXY); + showPingStatus($HOST_SVN, $DBNAME_SVN); + showPingStatus($HOST_CONFLUENCE, $DBNAME_CONFLUENCE); + + echo ""; + +echo ""; menu_end(); - -############################################################################# -############################# Functions Below ############################# -############################################################################# ########################################################################### @@ -432,4 +361,90 @@ } +########################################################################### +# +# Creates a table of summitExp against rawExp +# +########################################################################### +function createSummitDataTable($projectdb) { + + $date = date("Y-m-d"); + $sql = "SELECT DISTINCT exp_type FROM summitExp WHERE dateobs > '$date'"; + + $qry = $projectdb->query($sql); + if (dberror($qry)) { + echo "error reading newExp table
\n"; + echo "
table query : $sql
\n"; + menu_end(); + } + + // set up the table + global $table; + echo $table; + echo ""; + createTableTitle("Status of last night's data", 4); + echo ""; + createTableColumnHeader("Exposure type"); + createTableColumnHeader("At summit"); + createTableColumnHeader("Registered at MHPCC"); + createTableColumnHeader("At summit (Dropped)"); + + $msg = "No science images taken since $date"; + + // list the results + while ($qry->fetchInto($expType)) { + + $sql = "SELECT COUNT(*) FROM summitExp left join pzDownloadExp using (summit_id) WHERE dateobs > '$date' AND exp_type = '$expType[0]' and (state != 'drop' or state is null) and fault != 1042"; +# $sql = "SELECT COUNT(*) FROM summitExp WHERE dateobs > '$date' AND exp_type = '$expType[0]'"; + $qry2 = $projectdb->query($sql); + $qry2->fetchInto($summit); + + + $sql = "SELECT COUNT(*) FROM summitExp JOIN rawExp ON summitExp.exp_name = rawExp.exp_name WHERE summitExp.dateobs > '$date' AND rawExp.dateobs > '$date' AND summitExp.exp_type = '$expType[0]'"; + $qry2 = $projectdb->query($sql); + $qry2->fetchInto($mhpcc); + + $sql = "SELECT COUNT(*) FROM summitExp join pzDownloadExp using (summit_id) WHERE dateobs > '$date' AND exp_type = '$expType[0]' and state = 'drop'"; + $qry2 = $projectdb->query($sql); + $qry2->fetchInto($dropped); + + + #Query with 30 minutes delay to check during the night if any exposures are missing. + $datehour = gmdate("Y-m-d H:i:s"); + $sql = "SELECT COUNT(*) FROM summitExp left join pzDownloadExp using (summit_id) WHERE dateobs > '$date' AND dateobs <= date_sub('$datehour', interval 30 minute) AND exp_type = '$expType[0]' and (state != 'drop' or state is null) and fault != 1042"; + $qry2 = $projectdb->query($sql); + $qry2->fetchInto($summitdelay); + + $sql = "SELECT COUNT(*) FROM summitExp JOIN rawExp ON summitExp.exp_name = rawExp.exp_name WHERE summitExp.dateobs > '$date' AND summitExp.dateobs <= date_sub('$datehour', interval 30 minute) AND rawExp.dateobs > '$date' AND summitExp.exp_type = '$expType[0]'"; + $qry2 = $projectdb->query($sql); + $qry2->fetchInto($mhpccdelay); + + echo ""; + + $DLdiff = $summit[0] - $mhpcc[0]; + if ($DLdiff == 0) $error = 0; + else $error = 1; + + $delayDLdiff = $summitdelay[0] - $mhpccdelay[0]; + if ($delayDLdiff == 0) $errordelay = 0; + else $errordelay = 1; + + echo "$expType[0]"; + echo "$summit[0]"; + #createFormattedTableCell("", "", $mhpcc[0], $error, 0); + if ($delayDLdiff==0) { + createFormattedTableCell("", "", $mhpcc[0], $error, 0); + } else { + $DLStatus = "$mhpcc[0] ($delayDLdiff)"; + echo "$DLStatus"; + } + + echo "$dropped[0]"; + + echo ""; + } + + echo "\n"; + +} ########################################################################### @@ -440,11 +455,4 @@ function createChunkDataTable($projectdb) { - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - // function begin #First find the reference exposure ID to speed up the subsequent queries $date = gmdate("Y-m-d"); @@ -460,33 +468,29 @@ } - #Find the number of chunks - $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[0]"; - $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[0] 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"; - - #$sql = "SELECT DISTINCT substr(comment, 1, position(' ' in comment)) FROM rawExp WHERE dateobs LIKE '$date%' AND exp_id > $refExpID AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING' AND comment LIKE '%visit 1%'"; + #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 + $query = "SELECT suba.chunk,suba.Nvis1,suba.Nvis2,suba.Nvis3,suba.Nvis4,suba.Ncamgood,suba.Ncambad,subb.Nwarpgood,subb.Nwarpbad 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 .= " 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[0] 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[0] 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"; $qry = $projectdb->query($query); if (dberror($qry)) { - echo "error reading rawExp table
\n"; + echo "error querying SQL table
\n"; echo "
table query : $query
\n"; menu_end(); @@ -497,10 +501,15 @@ echo $table; echo ""; - createTableTitle("Status of last night's chunks", 5); + createTableTitle("Status of last night's chunks", 10); echo ""; createTableColumnHeader("Chunk"); - createTableColumnHeader("Nobs_1stvisit"); - createTableColumnHeader("Nobs_4visits(bad cam)"); - createTableColumnHeader("Ndiffs_published"); + createTableColumnHeader("Nobs"); + createTableColumnHeader("Ncam"); + createTableColumnHeader("Nwrp"); + createTableColumnHeader("wrp_pub"); + createTableColumnHeader("Nwwd"); + createTableColumnHeader("wwd_pub"); + createTableColumnHeader("Nwsd"); + createTableColumnHeader("wsd_pub"); createTableColumnHeader("Completion"); @@ -509,91 +518,120 @@ // list the results while ($qry->fetchInto($row)) { - $Nfirstvisits = 0; - $Nfourvisits = 0; - $badcam = 0; + $Nvis1 = 0; + $Nvis2 = 0; + $Nvis3 = 0; + $Nvis4 = 0; + $Ncamgood = 0; + $Ncambad = 0; + $Nwarpgood = 0; + $Nwarpbad = 0; + $NexpWWdiff = 0; + $Npubwarps = 0; $chunkname = $row[0]; - if($row[1] > 0) $Nfirstvisits = $row[1]; - if($row[2] > 0) $Nfourvisits = $row[2]; - - $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[0] AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')"; - $query2 .= " AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%'"; + if($row[1] > 0) $Nvis1 = $row[1]; + if($row[2] > 0) $Nvis2 = $row[2]; + if($row[3] > 0) $Nvis3 = $row[3]; + if($row[4] > 0) $Nvis4 = $row[4]; + if($row[5] > 0) $Ncamgood = $row[5]; + if($row[6] > 0) $Ncambad = $row[6]; + if($row[7] > 0) $Nwarpgood = $row[7]; + if($row[8] > 0) $Nwarpbad = $row[8]; + + #set flags if present + if($Ncambad >= 1) $badcamflag = 1; + if($Nwarpbad >= 1) $badwarpflag = 1; + + $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[0] 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;"; + $qry1 = $projectdb->query($query1); + while ($qry1->fetchInto($row)) {$NexpWWdiff = $row[1] + $row[2];} + + #find the unique diff_ids for each chunk, to be matched to the diffRun catalog + $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[0] 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"; $qry2 = $projectdb->query($query2); - $Ndiffs = 0; + $NWWdiffgood = 0; + $NWWdiffbad = 0; + $NWWdiffpub = 0; + + $NWSdiffgood = 0; + $NWSdiffbad = 0; + $NWSdiffpub = 0; + $wrpcnt = array(); + while ($qry2->fetchInto($row2)) { - $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"; - $qry3 = $projectdb->query($query3); - while ($qry3->fetchInto($row3)) { - if ($row3[0] !=0) $Ndiffs ++; - } - - $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"; - $qry3 = $projectdb->query($query3); - while ($qry3->fetchInto($row3)) { - 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 + $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"; + $qry3 = $projectdb->query($query3); + + while ($qry3->fetchInto($row3)) { + #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 + $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"; + $qry3 = $projectdb->query($query3); + + while ($qry3->fetchInto($row3)) { + #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 ($Ndiffs != ($Nfirstvisits*4)) $complete = "chunk not done"; - if ($Ndiffs == ($Nfirstvisits*4)) $complete = "chunk done"; - - if ($Nfirstvisits != $Nfourvisits) { - $Ndiffs .= "/? "; + #pull out the unique warp_ids + $Npubwarps = count($wrpcnt); + + #check for bad diffs + if(($NWWdiffbad >= 1) or ($NWSdiffbad >= 1)) $baddiffflag = 1; + + if ($Nvis1 != $Nvis4) { + if ($NWWdiffpub != $NexpWWdiff) $complete = "chunk incomplete, not done"; + if ($NWWdiffpub == $NexpWWdiff) $complete = "chunk incomplete, done"; } - if ($Nfirstvisits == $Nfourvisits) { - $Ndiffs .= "/"; - $Ndiffs .= $Nfirstvisits*4; + if ($Nvis1 == $Nvis4) { + if ($NWWdiffpub != $NexpWWdiff) $complete = "chunk complete, not done"; + if ($NWWdiffpub == $NexpWWdiff) $complete = "chunk complete, done"; + } - #also query for bad quality camRun exposures - $query4 = "SELECT count(*) "; - $query4 .= " FROM rawExp"; - $query4 .= " JOIN chipRun USING (exp_id)"; - $query4 .= " JOIN camRun USING (chip_id)"; - $query4 .= " JOIN camProcessedExp USING (cam_id)"; - $query4 .= " WHERE dateobs LIKE '$date%'"; - $query4 .= " AND exp_id > $refExpID[0]"; - $query4 .= " AND exp_type = 'OBJECT'"; - $query4 .= " AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%'"; - $query4 .= " AND (camProcessedExp.quality > 0)"; - $qry4 = $projectdb->query($query4); - $qry4->fetchInto($badcam); - - - echo ""; echo "$chunkname"; - echo "$Nfirstvisits"; - if ($badcam[0]==0) { - echo "$Nfourvisits"; - } else { - $DLStatus = "$Nfourvisits ($badcam[0])"; - echo "$DLStatus"; - } - echo "$Ndiffs"; + echo "$Nvis1/$Nvis2/$Nvis3/$Nvis4"; + echo "$Ncamgood/$Ncambad"; + echo "$Nwarpgood/$Nwarpbad"; + echo "$Npubwarps"; + echo "$NWWdiffgood/$NWWdiffbad"; + echo "$NWWdiffpub"; + echo "$NWSdiffgood/$NWSdiffbad"; + echo "$NWSdiffpub"; echo "$complete"; echo ""; } - - - // timer finished - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - echo "loading in $total_time seconds."; - $start= $finish; - echo ""; echo "
"; @@ -638,11 +676,4 @@ $selectedLabel, $selectedStage, $plotType) { - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - // function begin $isUpdate = ($selectedMode == "update"); global $headerColor; @@ -768,16 +799,5 @@ echo "\n"; - - // timer finished - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - echo "loading in $total_time seconds."; - $start= $finish; - echo ""; - echo "
"; + echo "\n"; } @@ -927,11 +947,4 @@ function createDatesTable($db, $proj) { - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - // function begin global $table; echo $table; @@ -946,16 +959,5 @@ createServerDateRow($db, $proj, "registration"); - // timer finished - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - echo "loading in $total_time seconds."; - $start= $finish; - echo ""; - echo "
"; - + echo "\n"; } @@ -999,11 +1001,4 @@ function createServersTable($pass, $proj, $selectedMode, $db, $servers, $selectedLabel, $selectedStage, $plotType) { - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - // function begin // set up table columns global $table; @@ -1092,15 +1087,5 @@ } - // timer finished - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - echo "loading in $total_time seconds."; - $start= $finish; - echo ""; - echo "
\n"; + echo "\n"; } @@ -1144,6 +1129,5 @@ function getServerStatus($db,$proj, $server, &$alive, &$running) { -# $sql = "SELECT alive, running FROM science_servers WHERE server LIKE '$server' AND telescope LIKE '$proj' ORDER BY timestamp DESC LIMIT 1"; - $sql = "SELECT alive, running FROM live_servers WHERE server LIKE '$server' AND telescope LIKE '$proj' ORDER BY timestamp DESC LIMIT 1"; + $sql = "SELECT alive, running FROM science_servers WHERE server LIKE '$server' AND telescope LIKE '$proj' ORDER BY timestamp DESC LIMIT 1"; if($debug){echo "$sql
";} @@ -1264,11 +1248,4 @@ ########################################################################### function showPingStatus($Host, $DatabaseName) { - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - // function begin exec("ping -c 1 $Host", $res, $rval); if ($rval == 0){ @@ -1277,186 +1254,7 @@ $PingStatus = "DOWN"; } - echo "$DatabaseName"; - - echo "$PingStatus"; - // timer finished - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - echo "$total_time seconds"; - $start= $finish; - echo ""; -} - - -########################################################################### -# -# Creates a table of summitExp against rawExp -# -########################################################################### -function createSummitDataTable($projectdb) { - - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - // function begin - $date = date("Y-m-d"); - $sql = "SELECT DISTINCT exp_type FROM summitExp WHERE dateobs > '$date'"; - - $qry = $projectdb->query($sql); - if (dberror($qry)) { - echo "error reading newExp table
\n"; - echo "
table query : $sql
\n"; - menu_end(); - } - - // set up the table - global $table; - echo $table; - echo ""; - createTableTitle("Data status > $date", 4); - echo ""; - createTableColumnHeader("Exposure type"); - createTableColumnHeader("At summit"); - createTableColumnHeader("Registered at MHPCC"); - createTableColumnHeader("Summit (Drop/Null)"); - - $msg = "No science images taken since $date"; - - // list the results - while ($qry->fetchInto($expType)) { - - $sql = "SELECT COUNT(*) FROM summitExp LEFT JOIN pzDownloadExp USING (summit_id) - WHERE dateobs > '$date' AND exp_type = '$expType[0]'"; - // AND state != 'drop' AND state is not null AND fault != 1042"; - $qry2 = $projectdb->query($sql); - $qry2->fetchInto($summit); - - $sql = "SELECT COUNT(*) FROM summitExp JOIN rawExp ON summitExp.exp_name = rawExp.exp_name - WHERE summitExp.dateobs > '$date' AND rawExp.dateobs > '$date' AND summitExp.exp_type = '$expType[0]'"; - $qry2 = $projectdb->query($sql); - $qry2->fetchInto($mhpcc); - - $sql = "SELECT COUNT(*) FROM summitExp LEFT JOIN pzDownloadExp USING (summit_id) - WHERE dateobs > '$date' AND exp_type = '$expType[0]' AND (state = 'drop' OR state is null)"; - $qry2 = $projectdb->query($sql); - $qry2->fetchInto($dropped); - - - #Query with 30 minutes delay to check during the night if any exposures are missing. - $datehour = gmdate("Y-m-d H:i:s"); - $sql = "SELECT COUNT(*) FROM summitExp LEFT JOIN pzDownloadExp USING (summit_id) - WHERE dateobs > '$date' AND dateobs <= date_sub('$datehour', interval 30 minute) - AND exp_type = '$expType[0]' AND (state != 'drop' OR state is null) and fault != 1042"; - - $qry2 = $projectdb->query($sql); - $qry2->fetchInto($summitdelay); - - $sql = "SELECT COUNT(*) FROM summitExp JOIN rawExp ON summitExp.exp_name = rawExp.exp_name - WHERE summitExp.dateobs > '$date' AND summitExp.dateobs <= date_sub('$datehour', interval 30 minute) - AND rawExp.dateobs > '$date' AND summitExp.exp_type = '$expType[0]'"; - $qry2 = $projectdb->query($sql); - $qry2->fetchInto($mhpccdelay); - - echo ""; - - $DLdiff = $summit[0] - $mhpcc[0]; - if ($DLdiff == 0) $error = 0; - else $error = 1; - - $delayDLdiff = $summitdelay[0] - $mhpccdelay[0]; - if ($delayDLdiff == 0) $errordelay = 0; - else $errordelay = 1; - - echo "$expType[0]"; - echo "$summit[0]"; - if ($delayDLdiff==0) { - createFormattedTableCell("", "", $mhpcc[0], $error, 0); - } else { - $DLStatus = "$mhpcc[0] ($delayDLdiff)"; - echo "$DLStatus"; - } - - echo "$dropped[0]"; - - echo ""; - } - - // timer finished - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - echo "loading in $total_time seconds."; - $start= $finish; - echo ""; - echo "
"; -} - - -function createSummitDataTable2($projectdb) { - - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - // function begin - $date = date("Y-m-d"); - // set up the table - global $table; - global $columnHeaderColor; - echo $table; - echo ""; - createTableTitle("Data status > $date", 4); - echo ""; - createTableColumnHeader("Exp_type at summit"); - createTableColumnHeader("Status at summit"); - createTableColumnHeader("Status at mhpcc"); - createTableColumnHeader("Number"); - $sql = "SELECT summitExp.exp_type,pzDownloadExp.state,rawExp.state,COUNT(*) FROM summitExp - LEFT JOIN pzDownloadExp USING (summit_id) LEFT JOIN rawExp on summitExp.exp_name = rawExp.exp_name - WHERE summitExp.dateobs > '$date' group by summitExp.exp_type,pzDownloadExp.state,rawExp.state"; - $qry = $projectdb->query($sql); - while ($qry->fetchInto($tmp)) { - if ($tmp[1] == NULL) { - echo "$tmp[0] null null $tmp[3]"; - } - else { - if ($tmp[1] == 'run') { - echo "$tmp[0] loading null $tmp[3]"; - } - else { - if ($tmp[2] == NULL) { - echo "$tmp[0] $tmp[1] loading $tmp[3]"; - } - else { - echo "$tmp[0] $tmp[1] downloaded $tmp[3]"; - } - } - } - } - - // timer finished - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - echo "loading in $total_time seconds."; - $start= $finish; - echo ""; - echo "
"; -} + echo "$DatabaseName$PingStatus"; +} + ?>