Index: trunk/ippMonitor/raw/czartool_labels.php =================================================================== --- trunk/ippMonitor/raw/czartool_labels.php (revision 42923) +++ trunk/ippMonitor/raw/czartool_labels.php (revision 42972) @@ -25,55 +25,55 @@ menu($myMenu, 'Czartool on '.$lastUpdateTime, 'ipp.css', $ID['link'], $ID['proj']); -$stages=array( - "chip", - "cam", - "fake", - "warp", - "stack", - "staticsky", - "fullforce", - "diff", - "dist", - "pub", - ); - -$getFailures = array( - "chip" => "failedChipProcessedImfile.php", - "cam" => "failedCamProcessedExp.php", - "fake" => "failedFakeProcessedImfile.php", - "warp" => "failedWarpSkyfiles.php", - "stack" => "failedStackSkyfile.php", - "staticsky" => "failedStaticsky.php", - "fullforce" => "failedFullForceSkyfiles.php", - "diff" => "failedDiffSkyfile.php", - "dist" => "NONE", - "pub" => "publishDone.php", -); - -$mainTable = array( - "chip" => "chipRun", - "cam" => "camRun", - "fake" => "fakeRun", - "warp" => "warpRun", - "stack" => "stackRun", - "staticsky" => "staticskyRun", - "fullforce" => "fullForceRun", - "diff" => "diffRun", - "dist" => "distRun", - "pub" => "publishRun", -); - -$addSearchState = array( - "chip" => 0, - "cam" => 0, - "fake" => 1, - "warp" => 0, - "stack" => 0, - "staticsky" => 0, - "fullforce" => 0, - "diff" => 0, - "dist" => 0, - "pub" => 0, -); +$stages = [ + "chip", + "cam", + "fake", + "warp", + "stack", + "staticsky", + "fullforce", + "diff", + "dist", + "pub", +]; + +$getFailures = [ + "chip" => "failedChipProcessedImfile.php", + "cam" => "failedCamProcessedExp.php", + "fake" => "failedFakeProcessedImfile.php", + "warp" => "failedWarpSkyfiles.php", + "stack" => "failedStackSkyfile.php", + "staticsky" => "failedStaticsky.php", + "fullforce" => "failedFullForceSkyfiles.php", + "diff" => "failedDiffSkyfile.php", + "dist" => "NONE", + "pub" => "publishDone.php", +]; + +$mainTable = [ + "chip" => "chipRun", + "cam" => "camRun", + "fake" => "fakeRun", + "warp" => "warpRun", + "stack" => "stackRun", + "staticsky" => "staticskyRun", + "fullforce" => "fullForceRun", + "diff" => "diffRun", + "dist" => "distRun", + "pub" => "publishRun", +]; + +$addSearchState = [ + "chip" => 0, + "cam" => 0, + "fake" => 1, + "warp" => 0, + "stack" => 0, + "staticsky" => 0, + "fullforce" => 0, + "diff" => 0, + "dist" => 0, + "pub" => 0, +]; $pass = $ID['pass']; @@ -159,5 +159,5 @@ // The Update mode of czartool is obsolete. selectedMode is hardcoded to stdscience above. -$isUpdate = ($selectedMode == "update"); +// $isUpdate = ($selectedMode == "update"); $isUpdate = 0; @@ -211,53 +211,76 @@ echo $table; createTableTitle("Status and links", 2); -echo "ippMonitor Notes - PHP: v.$phpversion -  |  SVN: v.$svnversion - -  |  online (ipp113) , -   dev (ipp117) - version "; -echo "Pan-STARRS Data Store - Home -  |  GPC1 -  |  GPC2 -  |  Sky Coverage"; -echo "Observations Metrics - PS1 -  |  PS2 -  |  IPP"; -echo "Postage Stamp Server - Home -  |  Status -  |  Data Store"; -echo "IPP Cluster Ganglia Monitor - Load_reports -  |  CPU_reports -  |  Memory_reports - "; -echo "Documentations for IPP - Czarblog -  |  Czar guidelines -  |  ippMonitor Jira -  |  Hardware -  |  trac - "; -echo "Current nightly science status - ".getNightlyScienceStatus($czardb, $proj).""; -echo "Czardb last update time - $hsttime1 (HST), $utctime1 (UTC), $mjdday (MJD)"; + +$html = << + ippMonitor Notes + + PHP: v.$phpversion  |  + SVN: v.$svnversion  |  + online (grens1)  |  + dev (ipp117) version + + + + Pan-STARRS Data Store + + Home  |  + GPC1  |  + GPC2  |  + Sky Coverage + + + + Observations Metrics + + PS1  |  + PS2  |  + IPP + + + + Postage Stamp Server + + Home  |  + Status  |  + Data Store + + + + IPP Cluster Ganglia Monitor + + Load_reports  |  + CPU_reports  |  + Memory_reports + + + + Documentations for IPP + + Czarblog  |  + Czar guidelines  |  + ippMonitor Jira  |  + Hardware  |  + trac + + + +HTML; +echo $html; +echo "Current nightly science status ".getNightlyScienceStatus($czardb, $proj).""; +echo "Czardb last update time $hsttime1 (HST), $utctime1 (UTC), $mjdday (MJD)"; 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 -createSummitDataTable2($projectdb, $pass, $proj, $debug); // this is slow because of the join between summitExp and rawExp, but not the major one + +createSummitDataTable($projectdb, $pass, $proj, $debug); createChunkDataTable($projectdb, $debug); // this is another part for slowness during nightly processing -// createDatesTable($czardb, $proj); what is this for? +// Postage stamp requests list +// createPStampDataTable(); + +// Current dates assigned for selected pantasks servers +// createDatesTable($czardb, $proj); // Database status @@ -266,5 +289,5 @@ $start = microtime(true); echo ""; - createTableTitle("Database status (update fix log here)", 3); + createTableTitle("Database status (update fix log here)", 3); echo ""; @@ -324,5 +347,5 @@ showPingStatus($HOST_NAGIOS, $LABEL_NAGIOS); showPingStatus($HOST_SVN, $LABEL_SVN); - showPingStatus($HOST_CONFLUENCE, $LABEL_CONFLUENCE); + //showPingStatus($HOST_CONFLUENCE, $LABEL_CONFLUENCE); showPingStatus($HOST_MAILMAN, $LABEL_MAILMAN); @@ -339,9 +362,10 @@ #2nd table column empty contnets -echo "
"; +//echo "
"; #3rd table column for plots echo "
"; -# plots + +// processing times series plots if ($selectedStage == "all_stages" && $selectedLabel == "all") { // Use old method with gnuplots as the rate query is slow with all stages and labels @@ -351,6 +375,6 @@ // Uncomment for dev version with Google Charts - // echo Gettimeseries($czardb, $selectedMode, $selectedLabel, $proj, $selectedStage); - //echo Getrate($czardb, $selectedMode, $selectedLabel, $proj, $selectedStage); + # echo Gettimeseries($czardb, $selectedMode, $selectedLabel, $proj, $selectedStage); + # echo Getrate($czardb, $selectedMode, $selectedLabel, $proj, $selectedStage); } else { // Use Google Charts for specific stage and label @@ -359,5 +383,36 @@ } -// Helper function to generate image tags for plots +require_once 'functions.php'; +// data quality for current day +echo getDataQuality($projectdb, 'today', $proj); + +// storage timeseries +echo getSpaces($czardb); + +// storage plot +echo getHosts($czardb); + + +# timer for main page end +echo ""; +echo "overall loading in " . round((microtime(true) - $mainstart), 3) . " seconds."; +echo ""; +echo ""; + +menu_end(); + + +///////////////////////////////////////////////////////////////////////////// +// // +// Functions // +// // +///////////////////////////////////////////////////////////////////////////// + +########################################################################### +# +# Display images +# Helper function to generate image tags for plots +# +########################################################################### function displayImage($type, $mode, $label, $proj, $stage, $plottype) { $src = "czartool_getplot.php?mode=" . urlencode($mode) . @@ -370,26 +425,4 @@ } -# data quality timeseries -echo getDataQuality2($projectdb); // new version of data quality plots - -# storage timeseries -echo getSpaces($czardb); -# storage plot -echo getHosts($czardb); - -# timer for main page end -echo ""; -echo "overall loading in " . round((microtime(true) - $mainstart), 3) . " seconds."; -echo ""; -echo ""; - -menu_end(); - - -///////////////////////////////////////////////////////////////////////////// -// // -// Functions // -// // -///////////////////////////////////////////////////////////////////////////// ########################################################################### @@ -399,13 +432,35 @@ ########################################################################### function createPStampDataTable() { - - $sql = "SELECT label, COUNT(job_id) AS 'Unfinished Jobs', IFNULL(priority,10000) AS priority FROM pstampRequest JOIN pstampJob USING(req_id) LEFT JOIN Label USING(label) WHERE pstampJob.state ='run' and pstampRequest.state ='run' GROUP by label"; - - $pstampdb = DB::connect("mysql://ippuser:ippuser@ipp113/ippRequestServer"); - $qry = $pstampdb->query($sql); - if (dberror($qry)) { + // timer start + $start = microtime(true); + + $dsn = "mysql:host=ipp113;dbname=ippRequestServer;charset=utf8mb4"; + $pdo = new PDO($dsn, 'ippuser', 'ippuser', [ + PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION + ]); + + // SQL statement + $sql = " + SELECT label, + COUNT(job_id) AS `Unfinished Jobs`, + IFNULL(priority,10000) AS priority + FROM pstampRequest + JOIN pstampJob USING(req_id) + LEFT JOIN Label USING(label) + WHERE pstampJob.state ='run' + AND pstampRequest.state ='run' + GROUP BY label + "; + + try { + // Execute the query + $stmt = $pdo->query($sql); + $rows = $stmt->fetchAll(PDO::FETCH_NUM); + + } catch (PDOException $e) { echo "error reading pstamp table
\n"; - echo "
table query : $sql
\n"; + echo "
table query : " . htmlspecialchars($sql) . "
\n"; menu_end(); + return; // stop executing the rest of the function } @@ -414,5 +469,4 @@ echo $table; - #echo "\n"; echo ""; createTableTitle("Postage stamp requests", 3); @@ -423,14 +477,18 @@ // list the results - while ($qry->fetchInto($row)) { + foreach ($rows as $row) { echo ""; - echo ""; - echo ""; - echo ""; + echo ""; + echo ""; + echo ""; echo ""; } - echo "
$row[0]$row[1]$row[2]" . htmlspecialchars($row[0]) . "" . htmlspecialchars($row[1]) . "" . htmlspecialchars($row[2]) . "
\n"; -} - + + // End timer + echo ""; + $total_time = round(microtime(true) - $start, 3); + echo "
loading in $total_time seconds
"; + echo "
"; +} ########################################################################### @@ -448,51 +506,109 @@ $mjdDay = getMJD(); $expname = "o{$mjdDay}%"; - $date = gmdate("Y-m-d"); - $sql = "SELECT MIN(exp_id) FROM rawExp WHERE exp_name like '$expname' "; - if ($debug) {echo "$sql
";} - $qry = $projectdb->query($sql); - - if (dberror($qry)) { + + $sql = "SELECT MIN(exp_id) FROM rawExp WHERE exp_name LIKE :expname"; + if ($debug) { + echo "$sql
"; + } + + try { + // Prepare and execute + $stmt = $projectdb->prepare($sql); + $stmt->execute([':expname' => $expname]); + + // Fetch the single row + $row = $stmt->fetch(PDO::FETCH_NUM); + + if ($row === false || $row[0] === null) { + echo "*** WARNING: No data for tonight **\n"; + $refExpID = 0; + } else { + $refExpID = $row[0]; + } + } catch (PDOException $e) { echo "*** WARNING: No data for tonight **\n"; $refExpID = 0; - } else { - $qry->fetchInto($refExpID); - $refExpID = isset($refExpID[0]) ? $refExpID[0] : 0; - } + + // (optional) debug + if ($debug) { + echo "DB error: " . $e->getMessage() . "
"; + } + } + // Print the result of the query - if ($debug) {echo "The first exp_id of tonight is: $refExpID
";} - - #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.fwhm_major > 12 OR 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 exp_name like '$expname' 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 exp_name like '$expname' 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"; - - if ($debug) {echo "$query
";} - $qry = $projectdb->query($query); - - if (dberror($qry)) { + if ($debug) { + echo "The first exp_id of tonight is: $refExpID
"; + } + + // Build the query (same SQL, but parameterized for $expname) + $query = "SELECT suba.chunk,suba.Nvis1,suba.Nvis2,suba.Nvis3,suba.Nvis4, + suba.Ncamgood,suba.Ncambad,subb.Nwarpgood,subb.Nwarpbad + FROM ( + SELECT substr(comment, 1, position(' ' in comment)) AS chunk, + count(if(rawExp.comment LIKE '%visit 1%',1,NULL)) AS Nvis1, + count(if(rawExp.comment LIKE '%visit 2%',1,NULL)) AS Nvis2, + count(if(rawExp.comment LIKE '%visit 3%',1,NULL)) AS Nvis3, + 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, + count(if(camProcessedExp.fwhm_major > 12 + OR camProcessedExp.sigma_ra > 5 + OR camProcessedExp.sigma_dec > 5 + OR camProcessedExp.quality > 0,1,NULL)) AS Ncambad + 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) + WHERE exp_name LIKE :expname + 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' + GROUP BY chunk ORDER BY dateobs + ) as suba + LEFT JOIN ( + 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 + FROM ( + SELECT substr(comment, 1, position(' ' in comment)) AS chunk, + count(warpSkyfile.quality) AS Nwarpskycell, + count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad + 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) + WHERE exp_name LIKE :expname + 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' + GROUP BY exp_name + ) as subc + GROUP BY chunk + ) AS subb + ON suba.chunk=subb.chunk"; + + if ($debug) { echo "$query
"; } + + try { + $stmt = $projectdb->prepare($query); + $stmt->execute([':expname' => $expname]); + } catch (PDOException $e) { echo "error querying SQL table
\n"; echo "
table query : $query
\n"; menu_end(); - } - + exit; + } + // set up the table global $table; @@ -512,116 +628,139 @@ createTableColumnHeader("wsd_pub"); createTableColumnHeader("Completion"); - + $msg = "No science images taken since $date"; - + + $badcamflag = 0; + $badwarpflag = 0; + $baddiffflag = 0; // list the results - while ($qry->fetchInto($row)) { - $Nvis1 = 0; - $Nvis2 = 0; - $Nvis3 = 0; - $Nvis4 = 0; - $Ncamgood = 0; - $Ncambad = 0; - $Nwarpgood = 0; - $Nwarpbad = 0; + while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { + $chunkname = $row['chunk'] ?? ''; + $Nvis1 = $row['Nvis1'] ?? 0; + $Nvis2 = $row['Nvis2'] ?? 0; + $Nvis3 = $row['Nvis3'] ?? 0; + $Nvis4 = $row['Nvis4'] ?? 0; + $Ncamgood = $row['Ncamgood'] ?? 0; + $Ncambad = $row['Ncambad'] ?? 0; + $Nwarpgood = $row['Nwarpgood'] ?? 0; + $Nwarpbad = $row['Nwarpbad'] ?? 0; + + 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 ( + SELECT substr(comment, 1, position(' ' in comment)) AS chunk, + count(distinct(comment)) AS Nvisits + FROM rawExp + LEFT JOIN chipRun USING (exp_id) + LEFT JOIN camRun USING (chip_id) + WHERE exp_name LIKE :expname + 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' + AND substr(comment,1,position(' ' in comment)) LIKE :chunkname + GROUP BY object + ) as suba + GROUP BY chunk"; + + $stmt1 = $projectdb->prepare($query1); + $stmt1->execute([':expname'=>$expname, ':chunkname'=>"$chunkname%"]); + $row1 = $stmt1->fetch(PDO::FETCH_ASSOC); $NexpWWdiff = 0; - $Npubwarps = 0; - - $chunkname = $row[0]; - 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) "; - $query1 .= " WHERE exp_name like '$expname'"; - $query1 .= " 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 exp_name like '$expname'"; - $query2 .= " 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); - - $NWWdiffgood = 0; - $NWWdiffbad = 0; - $NWWdiffpub = 0; - - $NWSdiffgood = 0; - $NWSdiffbad = 0; - $NWSdiffpub = 0; - $wrpcnt = array(); - - while ($qry2->fetchInto($row2)) { - #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 ($row1) { + $NexpWWdiff = ($row1['totvis2'] ?? 0) + ($row1['totvis34'] ?? 0); } - #pull out the unique warp_ids + + $query2 = "SELECT diff_id,warp1,stack1,warp2,stack2 + FROM 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) + WHERE exp_name LIKE :expname + 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' + AND substr(comment,1,position(' ' in comment)) LIKE :chunkname + GROUP BY diff_id"; + + $stmt2 = $projectdb->prepare($query2); + $stmt2->execute([':expname'=>$expname, ':chunkname'=>"$chunkname%"]); + + $NWWdiffgood = $NWWdiffbad = $NWWdiffpub = 0; + $NWSdiffgood = $NWSdiffbad = $NWSdiffpub = 0; + $wrpcnt = []; + + while ($row2 = $stmt2->fetch(PDO::FETCH_ASSOC)) { + $diff_id = $row2['diff_id']; + $warp1 = $row2['warp1']; + $warp2 = $row2['warp2']; + $stack2 = $row2['stack2']; + + // Build $query3 (same for WW and WS) + $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 diffRun + JOIN diffSkyfile USING (diff_id) + LEFT JOIN publishRun ON (diffRun.diff_id = stage_id) + WHERE diff_id=:diffid + GROUP BY diffRun.diff_id"; + + $stmt3 = $projectdb->prepare($query3); + $stmt3->execute([':diffid'=>$diff_id]); + $row3 = $stmt3->fetch(PDO::FETCH_ASSOC); + if (!$row3) continue; + + $Ndiff = $row3['Ndiff'] ?? 0; + $Ndiffbad = $row3['Ndiffbad'] ?? 0; + $Npub = $row3['Npub'] ?? 0; + + if ($stack2 == 0) { + if ($Ndiffbad < $Ndiff) $NWWdiffgood++; + if ($Ndiffbad == $Ndiff) $NWWdiffbad++; + if ($Npub == $Ndiff) $NWWdiffpub++; + if ($Npub == $Ndiff) { + $wrpcnt[$warp1] = ($wrpcnt[$warp1] ?? 0) + 1; + $wrpcnt[$warp2] = ($wrpcnt[$warp2] ?? 0) + 1; + } + } else { + if ($Ndiffbad < $Ndiff) $NWSdiffgood++; + if ($Ndiffbad == $Ndiff) $NWSdiffbad++; + if ($Npub == $Ndiff) $NWSdiffpub++; + } + } + + // Pull out the unique warp_ids $Npubwarps = count($wrpcnt); - - #check for bad diffs - if(($NWWdiffbad >= 1) or ($NWSdiffbad >= 1)) $baddiffflag = 1; - + + // Check for bad diffs + if (($NWWdiffbad >= 1) || ($NWSdiffbad >= 1)) { + $baddiffflag = 1; + } + + // Decide on completion text if ($Nvis1 != $Nvis4) { - if ($NWWdiffpub != $NexpWWdiff) $complete = "chunk incomplete, not done"; - if ($NWWdiffpub == $NexpWWdiff) $complete = "chunk incomplete, done"; + $complete = ($NWWdiffpub == $NexpWWdiff) + ? "chunk incomplete, done" + : "chunk incomplete, not done"; + } else { + $complete = ($NWWdiffpub == $NexpWWdiff) + ? "chunk complete, done" + : "chunk complete, not done"; } - if ($Nvis1 == $Nvis4) { - if ($NWWdiffpub != $NexpWWdiff) $complete = "chunk complete, not done"; - if ($NWWdiffpub == $NexpWWdiff) $complete = "chunk complete, done"; - - } - + + // Now output the row echo ""; - - echo "$chunkname"; + echo "" . htmlspecialchars($chunkname) . ""; echo "$Nvis1/$Nvis2/$Nvis3/$Nvis4"; echo "$Ncamgood/$Ncambad"; @@ -633,13 +772,11 @@ echo "$NWSdiffpub"; echo "$complete"; - echo ""; } - - + #----------------------------------------- - #list bad quality cam exposures + # list bad quality cam exposures if ($badcamflag != 0) { - + // set up another table global $table; @@ -654,30 +791,44 @@ createTableColumnHeader("sigma_dec"); createTableColumnHeader("comment"); - - #query the bad cam exposures - $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%' AND exp_id > $refExpID"; - $query .= " WHERE exp_name like '$expname'"; - $query .= " AND exp_type = 'OBJECT'"; - $query .= " AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'"; - $query .= " AND (camProcessedExp.fwhm_major > 12 OR camProcessedExp.quality > 0 OR camProcessedExp.sigma_ra > 5 OR camProcessedExp.sigma_dec > 5)"; - $query .= " ORDER BY comment"; - - $qry1 = $projectdb->query($query); - while ($qry1->fetchInto($row)) { - + + // query the bad cam exposures + $query = " + SELECT exp_name, + cam_id, + camProcessedExp.quality, + camProcessedExp.sigma_ra, + camProcessedExp.sigma_dec, + comment + FROM rawExp + JOIN chipRun USING (exp_id) + JOIN camRun USING (chip_id) + JOIN camProcessedExp USING (cam_id) + WHERE exp_name LIKE :expname + AND exp_type = 'OBJECT' + AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') + AND obs_mode NOT LIKE 'ENGINEERING' + AND obs_mode NOT LIKE 'MANUAL' + AND ( + camProcessedExp.fwhm_major > 12 OR + camProcessedExp.quality > 0 OR + camProcessedExp.sigma_ra > 5 OR + camProcessedExp.sigma_dec > 5 + ) + ORDER BY comment"; + + if ($debug) { echo "$query
"; } + // prepare & execute + $stmt = $projectdb->prepare($query); + $stmt->execute([':expname' => $expname]); + + // output rows + while ($row = $stmt->fetch(PDO::FETCH_NUM)) { echo ""; - - echo "$row[0]"; - echo "$row[1]"; - echo "$row[2]"; - echo "$row[3]"; - echo "$row[4]"; - echo "$row[5]"; - + echo "" . htmlspecialchars($row[0]) . ""; // exp_name + echo "" . htmlspecialchars($row[1]) . ""; // cam_id + echo "" . htmlspecialchars($row[2]) . ""; // quality + echo "" . htmlspecialchars($row[3]) . ""; // sigma_ra + echo "" . htmlspecialchars($row[4]) . ""; // sigma_dec + echo "" . htmlspecialchars($row[5]) . ""; // comment echo ""; } @@ -685,7 +836,7 @@ #----------------------------------------- - #list bad quality warp exposures + # list bad quality warp exposures if ($badwarpflag != 0) { - + // set up another table global $table; @@ -697,27 +848,39 @@ createTableColumnHeader("warp_id"); createTableColumnHeader("comment"); - - #query the bad warps - $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 exp_name like '$expname'"; - $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 ORDER BY comment"; - - $qry1 = $projectdb->query($query); - while ($qry1->fetchInto($row)) { - + + // query the bad warps + $query = " + SELECT exp_name, + warp_id, + COUNT(warpSkyfile.quality) AS Nwarpskycell, + COUNT(IF(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad, + comment + 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) + WHERE exp_name LIKE :expname + AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') + AND obs_mode NOT LIKE 'ENGINEERING' + AND obs_mode NOT LIKE 'MANUAL' + GROUP BY exp_name + ORDER BY comment"; + + // prepare and execute + $stmt = $projectdb->prepare($query); + $stmt->execute([':expname' => $expname]); + + // loop through rows + while ($row = $stmt->fetch(PDO::FETCH_NUM)) { echo ""; - - echo "$row[0]"; - echo "$row[1]"; - echo "$row[2]"; - + echo "" . htmlspecialchars($row[0]) . ""; // exp_name + echo "" . htmlspecialchars($row[1]) . ""; // warp_id + echo "" . htmlspecialchars($row[4]) . ""; // comment echo ""; } } - #----------------------------------------- - #list bad quality diff exposures if ($baddiffflag != 0) { // set up another table @@ -734,64 +897,64 @@ createTableColumnHeader("stack2"); createTableColumnHeader("comment"); - - #query the bad diffs - $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 exp_name like '$expname'"; - $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 diff_id"; - if ($debug) {echo "$query
";} - - $qry1 = $projectdb->query($query); - while ($qry1->fetchInto($row)) { - #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 - $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"; - $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]) { - echo ""; - - echo "$row[5]"; - echo "$row[0]"; - echo "$row[1]"; - echo "$row[2]"; - echo "$row[3]"; - echo "$row[4]"; - echo "$row[6]"; - - echo ""; - } - } - } - - 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 - $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"; - $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]) { - echo ""; - - echo "$row[5]"; - echo "$row[0]"; - echo "$row[1]"; - echo "$row[2]"; - echo "$row[3]"; - echo "$row[4]"; - echo "$row[6]"; - - echo ""; - } - } + + // main query with a placeholder for exp_name + $query = " + SELECT diff_id, warp1, stack1, warp2, stack2, exp_name, comment + FROM 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) + WHERE exp_name LIKE :expname + AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') + AND obs_mode NOT LIKE 'ENGINEERING' + AND obs_mode NOT LIKE 'MANUAL' + GROUP BY diff_id + "; + + if ($debug) { echo "$query
"; } + + // prepare & execute main query + $stmt = $projectdb->prepare($query); + $stmt->execute([':expname' => $expname]); + + // prepare secondary query once, reuse it + $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 diffRun + JOIN diffSkyfile USING (diff_id) + LEFT JOIN publishRun ON (diffRun.diff_id = stage_id) + WHERE diff_id = :diffid + GROUP BY diffRun.diff_id + "; + + if ($debug) { echo "$query3
"; } + + $stmt3 = $projectdb->prepare($query3); + + // iterate through main query results + while ($row = $stmt->fetch(PDO::FETCH_NUM)) { + + // run the nested query regardless of warp2==0 or >0 + $stmt3->execute([':diffid' => $row[0]]); + + while ($row3 = $stmt3->fetch(PDO::FETCH_NUM)) { + // if all skycells have bad quality + if ($row3[1] == $row3[0]) { + echo ""; + echo "" . htmlspecialchars($row[5]) . ""; // exp_name + echo "" . htmlspecialchars($row[0]) . ""; // diff_id + echo "" . htmlspecialchars($row[1]) . ""; // warp1 + echo "" . htmlspecialchars($row[2]) . ""; // stack1 + echo "" . htmlspecialchars($row[3]) . ""; // warp2 + echo "" . htmlspecialchars($row[4]) . ""; // stack2 + echo "" . htmlspecialchars($row[6]) . ""; // comment + echo ""; + } } } @@ -800,58 +963,77 @@ #----------------------------------------- #check for overridden exposures - $query = "SELECT COUNT(comment), comment"; - $query .= " FROM rawExp"; - $query .= " JOIN chipRun USING (exp_id)"; - $query .= " JOIN camRun USING (chip_id)"; - $query .= " WHERE exp_name like '$expname'"; - $query .= " AND exp_type = 'OBJECT'"; - $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 comment"; - - $qry1 = $projectdb->query($query); - $doHeader = 1; - - while ($qry1->fetchInto($row)) { + // first query: count per comment + $query1 = " + SELECT COUNT(comment) AS cnt, comment + FROM rawExp + JOIN chipRun USING (exp_id) + JOIN camRun USING (chip_id) + WHERE exp_name LIKE :expname + AND exp_type = 'OBJECT' + AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') + AND obs_mode NOT LIKE 'ENGINEERING' + AND obs_mode NOT LIKE 'MANUAL' + GROUP BY comment + "; + $stmt1 = $projectdb->prepare($query1); + $stmt1->execute([':expname' => $expname]); + + if ($debug) { echo "$query1
"; } + + $doHeader = 1; + + // loop over comments with duplicates + while ($row = $stmt1->fetch(PDO::FETCH_NUM)) { if ($row[0] > 1) { - $query = "SELECT exp_name,dateobs, comment"; - $query .= " FROM rawExp"; - $query .= " JOIN chipRun USING (exp_id)"; - $query .= " JOIN camRun USING (chip_id)"; - $query .= " WHERE exp_name like '$expname'"; - $query .= " AND comment LIKE '$row[1]'"; - $query .= " AND exp_type = 'OBJECT'"; - $query .= " AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'"; - $query .= " ORDER BY dateobs DESC"; - $qry2 = $projectdb->query($query); + // second query: get exposures with this comment + $query2 = " + SELECT exp_name, dateobs, comment + FROM rawExp + JOIN chipRun USING (exp_id) + JOIN camRun USING (chip_id) + WHERE exp_name LIKE :expname + AND comment LIKE :comment + AND exp_type = 'OBJECT' + AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') + AND obs_mode NOT LIKE 'ENGINEERING' + AND obs_mode NOT LIKE 'MANUAL' + ORDER BY dateobs DESC + "; + $stmt2 = $projectdb->prepare($query2); + $stmt2->execute([ + ':expname' => $expname, + ':comment' => $row[1] + ]); + + if ($debug) { echo "$query2
"; } $doskip = 0; - while ($qry2->fetchInto($row2)) { - if ($doskip) { + while ($row2 = $stmt2->fetch(PDO::FETCH_NUM)) { + if ($doskip) { if ($doHeader) { - // set up another table - global $table; - echo $table; - echo ""; - createTableTitle("Overridden exposures", 3); - echo ""; - createTableColumnHeader("exp_name"); - createTableColumnHeader("dateobs"); - createTableColumnHeader("comment"); - $doHeader = 0; + // set up another table + global $table; + echo $table; + echo ""; + createTableTitle("Overridden exposures", 3); + echo ""; + createTableColumnHeader("exp_name"); + createTableColumnHeader("dateobs"); + createTableColumnHeader("comment"); + $doHeader = 0; } + echo ""; - - echo "$row2[0]"; - echo "$row2[1]"; - echo "$row2[2]"; - + echo "" . htmlspecialchars($row2[0]) . ""; + echo "" . htmlspecialchars($row2[1]) . ""; + echo "" . htmlspecialchars($row2[2]) . ""; echo ""; } - $doskip = 1; + $doskip = 1; // skip the first row of each comment group } } } + if (!$doHeader) { print "\n"; } - // End timer @@ -870,18 +1052,37 @@ # ########################################################################### -function getLabels($db, $proj, $server) { - - # order by descending priority as set in project database - $sql = "SELECT label FROM science_labels WHERE server LIKE '$server' AND telescope LIKE '$proj' ORDER BY priority DESC, label"; - if ($debug) {echo "$sql
";} - - $qry = $db->query($sql); - if (dberror($qry)) {echo "error with $sql
\n";} - while ($qry->fetchInto($row)) { - - $labels[] = $row[0]; - } - - return $labels; +function getLabels(PDO $db, string $proj, string $server, bool $useLike = false, bool $debug = false): array { + // Use equality unless $useLike is true (then add wildcards) + if ($useLike) { + $sql = "SELECT label FROM science_labels + WHERE server LIKE :server AND telescope LIKE :proj + ORDER BY priority DESC, label"; + // add wildcards only if caller didn't already include them + $serverParam = (strpos($server, '%') === false && strpos($server, '_') === false) ? "%{$server}%" : $server; + $projParam = (strpos($proj, '%') === false && strpos($proj, '_') === false) ? "%{$proj}%" : $proj; + } else { + $sql = "SELECT label FROM science_labels + WHERE server = :server AND telescope = :proj + ORDER BY priority DESC, label"; + $serverParam = $server; + $projParam = $proj; + } + + if ($debug) { echo htmlspecialchars($sql) . "
\n"; } + + try { + $stmt = $db->prepare($sql); + $stmt->execute([':server' => $serverParam, ':proj' => $projParam]); + + // fetch all values from the first (and only) column + $labels = $stmt->fetchAll(PDO::FETCH_COLUMN); + return $labels ?: []; // ensure an array is always returned + } catch (PDOException $e) { + // handle/log error in a way that suits your app + if ($debug) { + echo "DB error: " . htmlspecialchars($e->getMessage()) . "
\n"; + } + return []; // fail gracefully + } } @@ -982,29 +1183,29 @@ foreach ($stages as &$stage) { + global $addSearchState; // add this line if inside a function // Note: Do not include the state for chip, cam, and warp so that both new and update faults are listed - $myTable = $mainTable[$stage]; - $myFailures = $getFailures[$stage]; + $myTable = $mainTable[$stage]; + $myFailures = $getFailures[$stage]; # echo "myTable: $myTable"; - if ("$myFailures" == "NONE") { + if ("$myFailures" == "NONE") { $link = $defaultlink; } else { $link = $myFailures . "?pass=" . $pass . "&proj=" . $proj . "&" . $myTable . ".label=" . $thisLabel; - if ($addSearchState[$stage]) { - // Note: Do not include the state for chip, cam, and warp so that both new and update faults are listed - $link .= "&" . $myTable . ".state=" . $searchState; - } + if ($addSearchState[$stage]) { + // Note: Do not include the state for chip, cam, and warp so that both new and update faults are listed + $link .= "&" . $myTable . ".state=" . $searchState; + } } - getStateAndFaults($db, $thisLabel, $proj, $selectedState, $stage, $str, $anyFaults); - //echo "getStateAndFaults $db, $thisLabel, $proj, $selectedState, $stage, $str, $anyFaults
"; - - $cellcolor = 0; - if ("$stage" == "dist") { - $cellcolor = (!$isUpdate && !$distributing) ? "gray" : 0; - } - if ("$stage" == "pub") { - $cellcolor = (!$isUpdate && !$publishing) ? "gray" : 0; - } - createFormattedTableCell($anyFaults, $link, $str, $anyFaults, $cellcolor); + getStateAndFaults($db, $thisLabel, $proj, $selectedState, $stage, $str, $anyFaults); + + $cellcolor = 0; + if ("$stage" == "dist") { + $cellcolor = (!$isUpdate && !$distributing) ? "gray" : 0; + } + if ("$stage" == "pub") { + $cellcolor = (!$isUpdate && !$publishing) ? "gray" : 0; + } + createFormattedTableCell($anyFaults, $link, $str, $anyFaults, $cellcolor); } echo "\n"; @@ -1099,5 +1300,5 @@ # ########################################################################### -function getNightlyScienceStatus($db, $proj) { +function getNightlyScienceStatus_old($db, $proj) { $sql = "SELECT status FROM science_nightlyscience WHERE telescope LIKE '$proj'"; @@ -1109,4 +1310,12 @@ return $row[0]; +} + +function getNightlyScienceStatus(PDO $db, $proj, $debug = false) { + $sql = "SELECT status FROM science_nightlyscience WHERE telescope LIKE :proj"; + $stmt = $db->prepare($sql); + $stmt->execute([':proj' => "%$proj%"]); // add wildcards + $row = $stmt->fetch(PDO::FETCH_ASSOC); + return $row ? $row['status'] : ''; } @@ -1132,47 +1341,67 @@ ########################################################################### function getRevertStatus($db, $proj, $stage) { - + // Note: The $db parameter is now a PDO object. + + // Use a prepared statement to prevent SQL injection. + $sql = "SELECT reverting FROM science_reverts WHERE stage LIKE ? AND telescope LIKE ?"; + + try { + $stmt = $db->prepare($sql); + $stmt->execute([$stage, $proj]); + + // Fetch a single column from the next row + return $stmt->fetchColumn(); + + } catch (PDOException $e) { + // Handle database errors + error_log("Database error in getRevertStatus: " . $e->getMessage()); + return null; // Return a safe default value + } +} + +########################################################################### +# +# Returns state and fault-count (if new) as a string +# +########################################################################### +function getStateAndFaults($db, $label, $proj, $state, $stage, &$str, &$anyFaults) { $anyFaults = false; - $sql = "SELECT reverting FROM science_reverts WHERE stage LIKE '$stage' AND telescope LIKE '$proj'"; - if ($debug) {echo "$sql
";} - - $qry = $db->query($sql); - if (dberror($qry)) {echo "error with $sql
\n";} - $qry->fetchInto($row); - - return $row[0]; -} - -########################################################################### -# -# Returns state and fault-count (if new) as a string -# -########################################################################### -function getStateAndFaults($db, $label, $proj, $state, $stage, &$str, &$anyFaults) { - - $anyFaults = false; - $sql = "SELECT pending, faults FROM $stage WHERE label LIKE '$label' AND dbname LIKE '$proj' ORDER BY timestamp DESC LIMIT 1"; - if ($debug) {echo "$sql
";} - - $qry = $db->query($sql); - if (dberror($qry)) {echo "error with $sql
\n";} - $qry->fetchInto($row); - - $pending = $row[0]; - $faults = $row[1]; - - if ($pending == 0) - $str = ""; - else - $str = "$pending"; - - if ($state == "new") { - - if ($faults > 0) { - - $str = $str."(".$faults.")"; - $anyFaults = true; + // Use a prepared statement to prevent SQL injection. + $sql = "SELECT pending, faults FROM $stage WHERE label LIKE ? AND dbname LIKE ? ORDER BY timestamp DESC LIMIT 1"; + + try { + $stmt = $db->prepare($sql); + $stmt->execute([$label, $proj]); + + // Fetch the result as a numerically indexed array + $row = $stmt->fetch(PDO::FETCH_NUM); + + if ($row) { + $pending = $row[0]; + $faults = $row[1]; + + if ($pending == 0) { + $str = ""; + } else { + $str = "$pending"; + } + + if ($state == "new" && $faults > 0) { + $str .= "($faults)"; + $anyFaults = true; + } + } else { + // Handle case where no row is found + $pending = null; + $faults = null; } + + } catch (PDOException $e) { + // Handle database errors + error_log("Database error in getStateAndFaults: " . $e->getMessage()); + $pending = null; + $faults = null; + // Optionally, you can set a default message for $str and $anyFaults } } @@ -1210,5 +1439,4 @@ } - ########################################################################### # @@ -1217,18 +1445,27 @@ ########################################################################### function createServerDateRow($db, $proj, $server) { - - $sql = "SELECT date FROM science_server_dates WHERE server LIKE '$server' AND telescope LIKE '$proj' ORDER BY date"; - if ($debug) {echo "$sql
";} - - $qry = $db->query($sql); - if (dberror($qry)) {echo "error with $sql
\n";} - $dates = ""; - $firstIn = true; - while ($qry->fetchInto($row)) { - if ($firstIn) { - $dates = $row[0]; - $firstIn = false; + // Note: The $db parameter is now a PDO object. + + // Use a prepared statement to prevent SQL injection. + $sql = "SELECT date FROM science_server_dates WHERE server LIKE ? AND telescope LIKE ? ORDER BY date"; + + try { + $stmt = $db->prepare($sql); + $stmt->execute([$server, $proj]); + + $dates = ""; + $firstIn = true; + while ($row = $stmt->fetch(PDO::FETCH_NUM)) { + if ($firstIn) { + $dates = $row[0]; + $firstIn = false; + } else { + $dates .= ", " . $row[0]; + } } - else $dates = $dates . ", " . $row[0]; + } catch (PDOException $e) { + // Handle database errors + error_log("Database error in createServerDateRow: " . $e->getMessage()); + $dates = "DB ERROR"; } @@ -1237,5 +1474,4 @@ echo "$dates"; echo ""; - } @@ -1333,17 +1569,38 @@ # ########################################################################### -function getServerStatus($db,$proj, $server, &$alive, &$running, &$host) { - -# $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, host FROM live_servers WHERE server LIKE '$server' AND telescope LIKE '$proj' ORDER BY timestamp DESC LIMIT 1"; - if($debug){echo "$sql
";} - - $qry = $db->query($sql); - if (dberror($qry)) {echo "error with $sql
\n";} - $qry->fetchInto($row); - - $alive = $row[0]; - $running = $row[1]; - $host = $row[2]; + +function getServerStatus($db, $proj, $server, &$alive, &$running, &$host) { + // Note: The $db parameter is now expected to be a PDO object. + + // Use a prepared statement to prevent SQL injection. + $sql = "SELECT alive, running, host FROM live_servers WHERE server LIKE ? AND telescope LIKE ? ORDER BY timestamp DESC LIMIT 1"; + + try { + $stmt = $db->prepare($sql); + + // Bind parameters and execute the query + $stmt->execute([$server, $proj]); + + // Fetch the result as a numerically indexed array, matching the old behavior + $row = $stmt->fetch(PDO::FETCH_NUM); + + if ($row) { + $alive = $row[0]; + $running = $row[1]; + $host = $row[2]; + } else { + // Handle case where no data is found for the server + $alive = null; + $running = null; + $host = null; + } + + } catch (PDOException $e) { + // Handle database errors + error_log("Database error in getServerStatus: " . $e->getMessage()); + $alive = null; + $running = null; + $host = null; + } } @@ -1353,15 +1610,20 @@ # ########################################################################### -function getLastUpdateTime($db) { - - $anyFaults = false; +function getLastUpdateTime(PDO $db) { $sql = "SELECT timestamp FROM chip ORDER BY timestamp DESC LIMIT 1"; - if ($debug) {echo "$sql
";} - - $qry = $db->query($sql); - if (dberror($qry)) {echo "error with $sql
\n";} - $qry->fetchInto($row); - - return $row[0]; + + // run the query + $stmt = $db->query($sql); + if (!$stmt) { + // if query fails, print error info + $errorInfo = $db->errorInfo(); + echo "Error with $sql: " . $errorInfo[2] . "
\n"; + return null; + } + + // fetch the first row as numeric array + $row = $stmt->fetch(PDO::FETCH_NUM); + + return $row ? $row[0] : null; // return timestamp or null } @@ -1391,29 +1653,26 @@ ########################################################################### function showReplicationsStatus($replHost, $replUser, $replPassword, $replDatabaseName) { - //print "
$replHost, $replUser, $replPassword, $replDatabaseName
"; global $czardb; - // Prepare storage info early, https://panstarrs.atlassian.net/browse/IPP-2338 $storageInfo = ''; $hostPrefixParts = explode(' ', $replDatabaseName); - $hostPrefix = addslashes($hostPrefixParts[0]); - - $sql = sprintf( - "SELECT host, FORMAT(total,1), FORMAT(available,2), FORMAT(used,2), writable, readable, FORMAT(used/total*100,1) - FROM hosts - WHERE host LIKE '%s%%' order by available desc - LIMIT 1", - $hostPrefix - ); - - $qry = $czardb->query($sql); - if (PEAR::isError($qry)) { - $storageInfo = "Storage query error"; - } else { - if ($qry->fetchInto($row)) { + $hostPrefix = $hostPrefixParts[0]; + + // Using a prepared statement with a placeholder for security + $sql = "SELECT host, FORMAT(total,1), FORMAT(available,2), FORMAT(used,2), writable, readable, FORMAT(used/total*100,1) + FROM hosts + WHERE host LIKE ? + ORDER BY available DESC + LIMIT 1"; + + try { + $stmt = $czardb->prepare($sql); + $stmt->execute(["$hostPrefix%"]); + $row = $stmt->fetch(PDO::FETCH_NUM); + + if ($row) { list($host, $total, $available, $used, $writable, $readable, $ratioStr) = $row; - $ratio = floatval($ratioStr); // convert to number - - // Determine color + $ratio = floatval($ratioStr); + if ($ratio < 80) { $color = 'lightgreen'; @@ -1423,31 +1682,40 @@ $color = 'pink'; } - + $storageInfo = "$ratioStr% of $total"; } else { $storageInfo = "No host storage data"; } - } - - $dbRepl = DB::connect("mysql://$replUser:$replPassword@$replHost"); - if (PEAR::isError($dbRepl)) { + } catch (PDOException $e) { + $storageInfo = "Storage query error"; + } + + // Connect to the replication database using PDO + try { + $dbRepl = new PDO("mysql:host=$replHost;", $replUser, $replPassword); + $dbRepl->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + } catch (PDOException $e) { echo "$replDatabaseName$storageInfoMySQL DB connection error - check configuration"; - return -1; // Don't stop the rest of the script + return -1; } // Query replication status - $res = $dbRepl->query('SHOW SLAVE STATUS'); - if (PEAR::isError($res)) { + try { + $res = $dbRepl->query('SHOW SLAVE STATUS'); + $row = $res->fetch(PDO::FETCH_NUM); + } catch (PDOException $e) { echo "$replDatabaseName$storageInfoMySQL query error - invalid user or privileges"; - $dbRepl->disconnect(); + $dbRepl = null; return -1; } - if (!$res->fetchInto($row)) { + if (!$row) { echo "$replDatabaseName$storageInfoNo replication data available"; - $dbRepl->disconnect(); + $dbRepl = null; return 0; } + // Note: The index for 'Seconds_Behind_Master' and 'Last_IO_Error' might vary based on MySQL version. + // It's safer to use PDO::FETCH_ASSOC and the column names. $errorStatusInMySql = $row[18]; $sec_behind = $row[32]; @@ -1463,5 +1731,4 @@ echo "$replDatabaseName$storageInfo"; - echo "$replStatus"; @@ -1471,8 +1738,6 @@ } - $dbRepl->disconnect(); - -} - + $dbRepl = null; // Close connection +} ########################################################################### # @@ -1481,28 +1746,24 @@ ########################################################################### function showDatabaseStatus($Host, $User, $Password, $DatabaseName) { - //print "
$Host, $User, $Password, $DatabaseName
"; global $czardb; - // Prepare storage info early, https://panstarrs.atlassian.net/browse/IPP-2338 $storageInfo = ''; $hostPrefixParts = explode(' ', $DatabaseName); $hostPrefix = addslashes($hostPrefixParts[0]); - $sql = sprintf( - "SELECT host, FORMAT(total,1), FORMAT(available,2), FORMAT(used,2), writable, readable, FORMAT(used/total*100,1) - FROM hosts - WHERE host LIKE '%s%%' order by available desc - LIMIT 1", - $hostPrefix - ); - - $qry = $czardb->query($sql); - if (PEAR::isError($qry)) { - $storageInfo = "Storage query error"; - } else { - if ($qry->fetchInto($row)) { + $sql = "SELECT host, FORMAT(total,1), FORMAT(available,2), FORMAT(used,2), writable, readable, FORMAT(used/total*100,1) + FROM hosts + WHERE host LIKE ? + ORDER BY available DESC + LIMIT 1"; + + try { + $stmt = $czardb->prepare($sql); + $stmt->execute(["$hostPrefix%"]); + $row = $stmt->fetch(PDO::FETCH_NUM); + + if ($row) { list($host, $total, $available, $used, $writable, $readable, $ratioStr) = $row; - $ratio = floatval($ratioStr); // convert to number - - // Determine color + $ratio = floatval($ratioStr); + if ($ratio < 80) { $color = 'lightgreen'; @@ -1512,20 +1773,25 @@ $color = 'pink'; } - + $storageInfo = "$ratioStr% of $total"; } else { $storageInfo = "No host storage data"; } - } - - $db = DB::connect("mysql://$User:$Password@$Host"); - if (PEAR::isError($db)) { + } catch (PDOException $e) { + $storageInfo = "Storage query error"; + } + + try { + $db = new PDO("mysql:host=$Host;", $User, $Password); + $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + $Status = "OK"; + } catch (PDOException $e) { + $Status = "PROBLEM"; echo "$DatabaseName$storageInfo$Status"; return -1; } - $Status = ($errorStatusInMySql==0?"OK":"PROBLEM"); + echo "$DatabaseName$storageInfo$Status"; - - $db->disconnect(); + $db = null; // Close connection } @@ -1571,20 +1837,17 @@ # ########################################################################### -function createSummitDataTable2($projectdb, $pass, $proj, $debug) { - +function createSummitDataTable($projectdb, $pass, $proj, $debug) { // timer start $start = microtime(true); // Define constants - $mjdDay = getMJD(); - $expname = "%{$mjdDay}%"; - + $mjdDay = getMJD(); + $expname = "%{$mjdDay}%"; $mjdDaym1 = $mjdDay - 1; $expnamem1 = "%{$mjdDaym1}%"; - $mjdDaym5 = $mjdDay - 5; $expnamem5 = "o{$mjdDaym5}%"; - $currentDate = gmdate("Y-m-d H:i:s"); + $currentDate = gmdate("Y-m-d H:i:s"); $previousDate = gmdate("Y-m-d", strtotime("-1 day")); @@ -1597,789 +1860,97 @@ echo ""; createTableColumnHeader("Exposure type"); - createTableColumnHeader("summitExp status"); - createTableColumnHeader("downloadExp status"); - createTableColumnHeader("rawExp / - newExp"); - + createTableColumnHeader("summitExp status"); + createTableColumnHeader("downloadExp status"); + createTableColumnHeader("rawExp / + newExp"); + + // --- Query 1 --- $sql = " - SELECT sum_type, pzstate, rawstate, count(*), sum_exp, pz_exp, raw_exp - FROM - (SELECT summitExp.exp_type as sum_type, pzDownloadExp.state as pzstate, rawExp.state as rawstate, summitExp.exp_name as sum_exp, pzDownloadExp.exp_name as pz_exp, rawExp.exp_name as raw_exp - FROM summitExp - LEFT JOIN pzDownloadExp USING (summit_id) - LEFT JOIN rawExp ON summitExp.exp_name = rawExp.exp_name - WHERE summitExp.summit_id >= ( - SELECT COALESCE(MIN(summit_id), 0) - FROM summitExp - WHERE exp_name LIKE '$expnamem5' - ) - AND summitExp.exp_name LIKE '$expname' ORDER BY summitExp.dateobs desc) as sub + SELECT sum_type, pzstate, rawstate, count(*), sum_exp, pz_exp, raw_exp + FROM (SELECT summitExp.exp_type as sum_type, + pzDownloadExp.state as pzstate, + rawExp.state as rawstate, + summitExp.exp_name as sum_exp, + pzDownloadExp.exp_name as pz_exp, + rawExp.exp_name as raw_exp + FROM summitExp + LEFT JOIN pzDownloadExp USING (summit_id) + LEFT JOIN rawExp ON summitExp.exp_name = rawExp.exp_name + WHERE summitExp.summit_id >= ( + SELECT COALESCE(MIN(summit_id), 0) + FROM summitExp WHERE exp_name LIKE '$expnamem5') + AND summitExp.exp_name LIKE '$expname' + ORDER BY summitExp.dateobs desc) as sub GROUP BY sum_type, pzstate, rawstate"; if ($debug) {echo "$sql
";} - $qry = $projectdb->query($sql); - while ($qry->fetchInto($tmp)) { + while ($tmp = $qry->fetch(PDO::FETCH_NUM)) { list($expType, $downloadState, $rawState, $count, $summit_exp, $pz_exp, $raw_exp) = $tmp; - if ($proj == "gpc1") { - $summit_exp_link = "$summit_exp"; + $summit_exp_link = ($proj == "gpc1") + ? "$summit_exp" + : "$summit_exp"; + + if ($downloadState === NULL) { + echo "$summit_exp_link $expType loading imfiles null $count"; + } elseif ($downloadState === 'run') { + echo "$summit_exp_link $expType $pz_exp copying $pz_exp saving $count"; } else { - $summit_exp_link = "$summit_exp"; + if ($rawState === NULL) { + echo "$summit_exp_link $expType $pz_exp $downloadState $pz_exp registering $count"; + } else { + echo "$summit_exp_link $expType $pz_exp $downloadState $raw_exp $rawState $count"; + } } - if ($downloadState == NULL) { - echo " $summit_exp_link $expType loading imfiles null $count"; - } else { - if ($downloadState == 'run') { - echo " $summit_exp_link $expType $pz_exp copying $pz_exp saving $count"; - } else { - if ($rawState == NULL) { - echo " $summit_exp_link $expType $pz_exp $downloadState $pz_exp registering $count"; - } else { - echo " $summit_exp_link $expType $pz_exp $downloadState $raw_exp $rawState $count"; - } - } - } - } - + } + + // --- Query 2 --- echo "Last night $previousDate (UTC), $mjdDaym1 (MJD)"; $sql = " - SELECT sum_type, pzstate, rawstate, count(*), sum_exp, pz_exp, raw_exp - FROM - (SELECT summitExp.exp_type as sum_type, pzDownloadExp.state as pzstate, rawExp.state as rawstate, summitExp.exp_name as sum_exp, pzDownloadExp.exp_name as pz_exp, rawExp.exp_name as raw_exp - FROM summitExp - LEFT JOIN pzDownloadExp USING (summit_id) - LEFT JOIN rawExp ON summitExp.exp_name = rawExp.exp_name - WHERE summitExp.summit_id >= ( - SELECT COALESCE(MIN(summit_id), 0) - FROM summitExp - WHERE exp_name LIKE '$expnamem5' - ) - AND summitExp.exp_name LIKE '$expnamem1' ORDER BY summitExp.dateobs desc) as sub + SELECT sum_type, pzstate, rawstate, count(*), sum_exp, pz_exp, raw_exp + FROM (SELECT summitExp.exp_type as sum_type, + pzDownloadExp.state as pzstate, + rawExp.state as rawstate, + summitExp.exp_name as sum_exp, + pzDownloadExp.exp_name as pz_exp, + rawExp.exp_name as raw_exp + FROM summitExp + LEFT JOIN pzDownloadExp USING (summit_id) + LEFT JOIN rawExp ON summitExp.exp_name = rawExp.exp_name + WHERE summitExp.summit_id >= ( + SELECT COALESCE(MIN(summit_id), 0) + FROM summitExp WHERE exp_name LIKE '$expnamem5') + AND summitExp.exp_name LIKE '$expnamem1' + ORDER BY summitExp.dateobs desc) as sub GROUP BY sum_type, pzstate, rawstate"; if ($debug) {echo "$sql
";} $qry = $projectdb->query($sql); - while ($qry->fetchInto($tmp)) { + while ($tmp = $qry->fetch(PDO::FETCH_NUM)) { list($expType, $downloadState, $rawState, $count, $summit_exp, $pz_exp, $raw_exp) = $tmp; - if ($proj == "gpc1") { - $summit_exp_link = "$summit_exp"; + $summit_exp_link = ($proj == "gpc1") + ? "$summit_exp" + : "$summit_exp"; + + if ($downloadState === NULL) { + echo "$summit_exp_link $expType loading imfiles null $count"; + } elseif ($downloadState === 'run') { + echo "$summit_exp_link $expType $pz_exp copying $pz_exp saving $count"; } else { - $summit_exp_link = "$summit_exp"; + if ($rawState === NULL) { + echo "$summit_exp_link $expType $pz_exp $downloadState $pz_exp registering $count"; + } else { + echo "$summit_exp_link $expType $pz_exp $downloadState $raw_exp $rawState $count"; + } } - if ($downloadState == NULL) { - echo " $summit_exp_link $expType loading imfiles null $count"; - } else { - if ($downloadState == 'run') { - echo " $summit_exp_link $expType $pz_exp copying $pz_exp saving $count"; - } else { - if ($rawState == NULL) { - echo " $summit_exp_link $expType $pz_exp $downloadState $pz_exp registering $count"; - } else { - echo " $summit_exp_link $expType $pz_exp $downloadState $raw_exp $rawState $count"; - } - } - } - } - + } + + // End timer echo ""; - // End timer $total_time = round(microtime(true) - $start, 3); echo "
loading in $total_time seconds
"; - echo ""; echo "
"; } - -function formatState($state) { - // Formats display of the state for better readability - if (is_null($state)) { - return "null"; - } elseif ($state == 'run') { - return "loading"; - } else { - return $state; - } -} -########################################################################### -# -# Gets disk status -# -########################################################################### -function getHostsold($db) { - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - #echo ""; - echo ""; - echo "
"; - echo "
"; - echo "
"; - echo "
Free
"; - echo "
Used
"; - echo "
> 97% limit
"; - echo "
Down | Repair
"; - echo "
Power Off
"; - echo "
"; - echo "
"; - - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - $start= $finish; - echo "
loading in $total_time seconds
"; -} - -########################################################################### -# -# Gets disk status -# -########################################################################### -function getHosts($db) { - $start = microtime(true); - - - // Set environment variables - putenv("PATH=/usr/local/bin:/usr/bin:/bin:/data/ippc65.0/ipp/src/psconfig//ipp-20210708-gentoo.lin64/bin"); - putenv("PERL5LIB=/data/ippc65.0/ipp/src/psconfig//ipp-20210708-gentoo.lin64/lib"); - - $hostnoteall = array(); - exec("neb-host |cut -b 17-30,86-", $hostnoteall); - - $sql = "SELECT host, format(total, 2), format(available, 2), format(used, 2), writable, readable, format(used/total*100,3) as ratio FROM hosts where xattr <> 3 ORDER BY CASE WHEN ROUND(used/total*100,3) = 100 THEN 0 ELSE 1 END, CASE WHEN host LIKE '%\_bck.0' THEN 1 ELSE 0 END, host;"; - $qry = $db->query($sql); - - if ($qry === false) { - echo "Error with SQL query
"; - return; - } - - $sqlMax = "SELECT MAX(total) AS max_total FROM hosts WHERE xattr <> 3"; - $qryMax = $db->query($sqlMax); - $maxUsed = 0; - - if ($qryMax && $qryMax->fetchInto($rowMax)) { - $maxUsed = $rowMax[0]; // max_total value - } - - $dataRows = array(); - while ($qry->fetchInto($row)) { - // Access by index - $host = $row[0]; // ipp071.0 - $total = $row[1]; // 80.0352 TB - $available = $row[2]; // 10.8096 TB - $used = $row[3]; // 69.2256 TB - $writable = $row[4]; // 0 - $readable = $row[5]; // 1 - $ratio = $row[6]; // 86.4939 % - - // Match notes - $hostnotes = preg_grep("/$host/", $hostnoteall); - $hostnote = reset($hostnotes); - - // Defaults - $styles = "stroke-width: .5; stroke-color: black;"; - $status = "down"; - $usedColor = "lightgrey"; - $freeColor = "lightgrey"; - - // Status logic - if ($writable == 1 && $readable == 1) { - $status = "up"; - $usedColor = ($ratio >= 97) ? "pink" : "yellow"; // red if ≥97% - $freeColor = "#a6ec99"; // green free - } elseif ($writable == 0 && $readable == 1) { - $status = "repair"; - $usedColor = ($ratio >= 97) ? "pink" : "yellow"; - $freeColor = "lightgrey"; // grey free - } elseif ($available < 1) { - $used = $maxUsed; - $available = 0; - $status = "offline"; - $usedColor = "white"; - $freeColor = "white"; - } - - if ($status == "down") { - $tooltipUsed = $host . ": " . round($ratio, 1) . "% of " . $total . " TB used ". - "
" . $hostnote . ""; - $tooltipFree = "" . - $host . ": " . $available . " TB free" . - ""; - } elseif ($status == "offline") { - $tooltipUsed = $host . ": " . round($ratio, 1) . "% of " . $total . " TB used ". - "
" . $hostnote . ""; - $tooltipFree = "" . - $host . ": " . $total . " TB free" . - ""; - } else { - $tooltipUsed = $host . ": " . round($ratio, 1) . "% of " . $total . " TB used
" . $hostnote; - $tooltipFree = $host . ": " . $available . " TB free"; - } - - // Build data row (manual string concatenation for old PHP) - $dataRows[] = - "[ \"" . $host . ":" . $status . "\", " . - $used . ", \"" . $styles . " color: " . $usedColor . ";\", \"" . $tooltipUsed . "\", " . - $available . ", \"" . $styles . " color: " . $freeColor . ";\", \"" . $tooltipFree . "\" ]"; - - } - echo ""; - -echo " -
-
- -
- -
- -
Free
-
Used
-
97% Usage
-
Down/Repair
-
Offline
- - -
- -
"; - - $total_time = round(microtime(true) - $start, 3); - echo "
loading in $total_time seconds
"; -} - -########################################################################### -# -# Gets disk spaces status -# -########################################################################### -function getSpaces($db) { - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - - $db->query("SELECT FORMAT(IFNULL(SUM(available), 0), 1) FROM hosts where host like 'ippb%' and xattr <> 3")->fetchInto($row); - $ippbusable = $row[0]; - $db->query("SELECT FORMAT(IFNULL(SUM(available), 0), 1) FROM hosts where host like 'ippb%' and writable = 1 and xattr <> 3")->fetchInto($row); - $ippbfree = $row[0]; - - $db->query("SELECT FORMAT(IFNULL(SUM(available), 0), 1) FROM hosts where host not like 'ippb%' and xattr <> 3")->fetchInto($row); - $ippusable = $row[0]; - $db->query("SELECT FORMAT(IFNULL(SUM(available), 0), 1) FROM hosts where host not like 'ippb%' and writable = 1 and xattr <> 3")->fetchInto($row); - $ippfree = $row[0]; - - $db->query("SELECT FORMAT(IFNULL(SUM(available), 0), 1) FROM hosts where host like 'ipp%_bck.0' and xattr <> 3")->fetchInto($row); - $bckusable = $row[0]; - $db->query("SELECT FORMAT(IFNULL(SUM(available), 0), 1) FROM hosts where host like 'ipp%_bck.0' and writable = 1 and xattr <> 3")->fetchInto($row); - $bckfree = $row[0]; - - $sql = "select format(used/total*100,1), usable from cluster_space order by timestamp desc limit 1;"; - - if ($debug) {echo "$sql
";} - $qry = $db->query($sql); - if (dberror($qry)) {echo "error with $sql
\n";} - $qry->fetchInto($row); - $allocated = $row[0]; - $usable = $row[1]; - - #echo ""; - echo ""; - echo "
"; - echo "
"; - echo "
"; - echo "
ipp*_bck: $bckusable TB (usable)
"; - echo "
ipp*_bck: $bckfree TB (free)
"; - echo "
"; - echo "
"; - - echo ""; - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - $start= $finish; - echo "
loading in $total_time seconds
"; -} - -########################################################################### -# -# Gets data quality status -# -########################################################################### -function getDataQuality($db) { - // timer start - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $start = $time; - - $date = gmdate("Y-m-d"); - $sql = "SELECT MAX(exp_id) FROM rawExp WHERE dateobs BETWEEN DATE_SUB('$date', INTERVAL 20 DAY) AND DATE_SUB('$date', INTERVAL 10 DAY)"; - if ($debug) {echo "$sql
";} - $qry = $db->query($sql); - - if (dberror($qry)) { - echo "*** WARNING: No data in the period 10-20 days before requested date (query will be slower) **\n"; - $refExpID = 0; - } else { - $qry->fetchInto($refExpID); - $refExpID = isset($refExpID[0]) ? $refExpID[0] : 0; - } - // Print the result of the query - if ($debug) {echo "The result of MAX(exp_id) is: $refExpID
";} - {echo "The result of MAX(exp_id) is: $refExpID
";} - - #Query for the needed data - $DQarray = array(); - - $sql = "SELECT subtime(dateobs,'10:00:00') as time,iq_fwhm_major,iq_fwhm_minor,zpt_obs,zpt_stdev,rawExp.filter,rawExp.exp_time,rawExp.comment FROM rawExp JOIN chipRun using (exp_id) JOIN camRun using (chip_id) JOIN camProcessedExp using (cam_id)"; -# $sql.= " WHERE (rawExp.dateobs > utc_date() - INTERVAL 14 hour) and (rawExp.dateobs < utc_date() + INTERVAL 10 hour)"; - $sql.= " WHERE (rawExp.dateobs > utc_date())"; -# $sql.= " WHERE (rawExp.dateobs > '2024-10-24')"; -# $sql.= " AND (rawExp.dateobs < '2024-10-25')"; - $sql.= " AND exp_id > $refExpID[0]"; - $sql.= " AND exp_type = 'OBJECT' AND camRun.state like 'full' ORDER BY dateobs asc;"; - if ($debug) {echo "$sql
";} - $qry = $db->query($sql); - if (dberror($qry)) {echo "error with $sql
\n";} -# echo "error with $sql
\n"; - while ($qry->fetchInto($row)) { - $DQarray[] = $row; - } - - #also find the median zeropoint -# foreach($DQarray as $row) { -# $zpt[] = $row[3]; -# } - -# sort($zpt); -# $count = sizeof($zpt); // cache the count -# $index = floor($count/2); // cache the index -# $zpt_med = $zpt[$index]; - - #echo ""; - echo ""; - echo "
"; - echo "
"; - echo "
"; - echo "
"; - echo "
"; - - echo ""; - -# --------------------------------------- -# Also the zeropoint - - #echo ""; - echo ""; - echo "
"; - echo "
"; - echo "
"; - echo "
"; - echo "
"; - - echo ""; - -# --------------------------------------- -# And a fixed zoom in on the zeropoint to see details - - #echo ""; - echo ""; - echo "
"; - echo "
"; - echo "
"; - echo "
"; - echo "
"; - - echo ""; - - - - $time = microtime(); - $time = explode(' ', $time); - $time = $time[1] + $time[0]; - $finish = $time; - $total_time = round(($finish - $start), 3); - $start= $finish; - echo "
loading plot in $total_time seconds
"; -} - @@ -3269,370 +2840,4 @@ } - -########################################################################### -# -# Get data quality -# -########################################################################### -function getDataQuality2($db) { - // Start timer - $start = microtime(true); - - $mjdDay = getMJD(); - $expname = "o".$mjdDay."%"; - - $sql = "SELECT SUBTIME(dateobs, '10:00:00') AS time, - round(( 367*YEAR(dateobs)- FLOOR((7 * (YEAR(dateobs) + FLOOR((MONTH(dateobs) + 9) / 12))) / 4) + FLOOR(275 * MONTH(dateobs) / 9) - + DAY(dateobs) + 1721013.5 + (HOUR(dateobs) / 24.0) + (MINUTE(dateobs) / 1440.0) + (SECOND(dateobs) / 86400.0) - 2400000.5), 5) AS MJD, - filter, comment, ra*180/pi() as RA, decl*180/pi() as DECL, exp_name, alt, az, fwhm_major, fwhm_minor, zpt_obs, zpt_stdev, rawExp.exp_id - FROM rawExp JOIN chipRun using (exp_id) JOIN camRun using (chip_id) JOIN camProcessedExp using (cam_id) - WHERE rawExp.exp_name like '$expname' - AND exp_type = 'OBJECT' - AND camRun.state like 'full' - AND camProcessedExp.fault = 0 - AND camProcessedExp.zpt_obs != 0; "; - - $DQarray = array(); - $qry = $db->query($sql); - if (dberror($qry)) { - echo "Error with $sql
\n"; - return; - } - - while ($qry->fetchInto($row)) { - $DQarray[] = $row; - } - - // Ensure $DQarray is not empty - if (empty($DQarray)) { - echo "
No data for MJD: $mjdDay
"; - return; // Stop execution if no data to encode - } - - // Convert data array to JSON for easier handling in JavaScript - $dataJson = json_encode($DQarray); - - if ($dataJson === false) { - echo "Error encoding JSON: " . json_last_error_msg(); // Display any JSON encoding errors - return; // Stop execution if JSON encoding fails - } - - echo ""; // Log JSON output to console for debugging - -echo << - - - -
-
-
-
- -
-
-
-
- -
-
-
-
- -HTML; - // End timer - $total_time = round(microtime(true) - $start, 3); - echo "
loading plot in $total_time seconds
"; - -} - - - function getprocrate ($proc1, $proc0, $trange) { if($proc1 > $proc0) { @@ -3646,4 +2851,3 @@ - ?>