<?php
include 'ipp.php';
include 'site.php';

$ID = checkID();

// require an explicit project
if (! $ID['proj']) { projectform ($ID); }

//$db = dbconnect($ID['proj']);
$czardb = dbconnect("czardb"); // HACK to connect to czarDb 
$lastUpdateTime = getLastUpdateTime($czardb);

$gpc1db = dbconnect($ID['proj']);


$PATH = getenv("PATH");
putenv("PATH=$BINDIR:$PATH");

$LD_LIBRARY_PATH = getenv("LD_LIBRARY_PATH");
putenv("LD_LIBRARY_PATH=$LIBDIR:$LD_LIBRARY_PATH");


if ($ID['menu']) {$myMenu = $ID['menu'];}
else {$myMenu = "ipp.imfiles.dat";}

menu($myMenu, 'Czartool on '.$lastUpdateTime, 'ipp.css', $ID['link'], $ID['proj']);

$stages=array(
        "burntool", 
        "chip",
        "cam",
        "fake",
        "warp",
        "stack",
        "diff",
        "magic",
        "magicDS",
        "dist", 
        "pub");

$servers=array(
        "addstar",
        "addstarlap",
        "cleanup",
        "detrend",
        "distribution",
        "pstamp",
        "update",
        "publishing",
        "registration",
        "replication",
        "stack",
        "deepstack",
        "stdscience",
        "summitcopy");

$pass = $ID['pass'];
$proj = $ID['proj'];
$menu = $ID['menu'];

$selectedMode = $_GET[mode];
$selectedStage = $_GET[stage];
$selectedLabel = $_GET[label];
$selectedServer = $_GET[server];
$selectedRevertStage = $_GET[revertstage];
$selectedRevertMode = $_GET[revertmode];
$plotType = $_GET[plottype];
$serverCmd = $_GET[servercmd];
$allServerCmd = $_GET[allservercmd];

$headerColor = "#0080c0";
$columnHeaderColor = "lavender";

if ($selectedMode == "") { $selectedMode = "stdscience"; }
if ($selectedLabel == "") { $selectedLabel = "all"; }
if ($selectedStage == "") { $selectedStage = "all_stages"; }
if ($plotType == "") { $plotType = "linear"; }

// deal with reverts: turn on or off if requested and pass current revert state for this stage onto labels table later
if ($selectedRevertStage && $selectedRevertMode) turnRevertsOnOff($czardb, $selectedRevertStage, $selectedRevertMode);

// tell selected server to stop or run
if ($selectedServer && $serverCmd) serverStopRun($czardb, $selectedServer,  $serverCmd);

// tell all servers to stop or run
if ($allServerCmd) {

    foreach ($servers as &$server) serverStopRun($czardb, $server,  $allServerCmd);
}


$debug = 0;
$table = "<table bgcolor=\"#FFFFFF\" width=\"700\" align=\"middle\" border=\"1\" cellspacing=\"0\" cellpadding=\"4\" style=\"font-size:80%\">\n";

$labels = getLabels($czardb, $selectedMode);
$distLabels = getLabels($czardb, "distribution");
$pubLabels = getLabels($czardb, "publishing");

if ($debug) {
    echo "prog returned $status, and output:<br>";
    for ($i = 0; $i < count($distLabels); $i++) {
        echo "$distLabels[$i]<br>";
    }
}

$isUpdate = ($selectedMode == "update");
$modeText = $isUpdate ? "Showing update processing" : "Showing standard processing";
$modeLinkText = $isUpdate ? "standard" : "update";
$modeLink = $isUpdate ? "stdscience" : "update";
$link = "czartool_labels.php?pass=".$pass."&proj=".$proj."&mode=".$modeLink."&label=all&stage=" . $selectedStage . "&revertstage=" . $stage . "&plottype=$plotType";

// set up the form
echo "<form action=\"czartool_labels.php\" method=\"POST\">\n";

echo "<input type=\"hidden\" name=\"pass\" value=\"$pass\">\n";
echo "<input type=\"hidden\" name=\"proj\" value=\"$proj\">\n";
echo "<input type=\"hidden\" name=\"menu\" value=\"$menu\">\n";
echo "</form>\n";

echo "<table border=\"0\">";

echo "<tr>";
echo "<td colspan=\"3\" style=\"background-color:#0080c0;\">";
echo "<h1 align=\"middle\">Czartool</h1>";
echo "<h4 align=\"middle\">$modeText (change to <a href=\"$link\"><font color=\"blue\">$modeLinkText</font></a>)</h4>";
echo "</td>";
echo "</tr>";

echo "<tr valign=\"top\">";
echo "<td style=width:100px;text-align:top;\">";
 # time series plot
echo "<br>";
 echo "<img src=\"czartool_getplot.php?mode=$selectedMode&type=t&label=$selectedLabel&stage=$selectedStage&plottype=$plotType\"><br>";
 # rate time series plot
echo "<br>";
 echo "<img src=\"czartool_getplot.php?mode=$selectedMode&type=rt&label=$selectedLabel&stage=$selectedStage&plottype=linear\"><br>";
 # rate stacked histo plot
 #echo "<img src=\"czartool_getplot.php?mode=$selectedMode&type=rh&label=$selectedLabel&stage=$selectedStage&plottype=linear\"><br>";
 # histogram plot
 #echo "<img src=\"czartool_getplot.php?mode=$selectedMode&type=h&label=$selectedLabel&stage=$selectedStage&plottype=linear\"><br>";
 # storage plot
echo "<br>";
 echo "<img src=\"czartool_getplot.php?type=s\"><br>";
 # storage timeseries
echo "<br>";
echo "<img src=\"czartool_getplot.php?type=st\"><br>";
 # pantasks server status, if requested
echo "<br>";
 if ($selectedServer && !$serverCmd) showServerStatus($selectedServer);
echo "</td>";
echo "<td style=height:200px;width:10px;text-align:top;\">";
echo "<td style=height:200px;width:400px;text-align:top;\">";

#echo "<br>";

// status table at top
$nsStatus = getNightlyScienceStatus($czardb);
$plotTypeLink = ($plotType == "linear") ? "log" : "linear";
$link = "czartool_labels.php?pass=" . $pass . "&proj=" . $proj . "&label=" . $selectedLabel . "&stage=" . $selectedStage . "&revertstage=" . $stage . "&plottype=$plotTypeLink";
echo "<br>";
echo $table;

createTableTitle("Status and links", 3);
echo "<tr>\n";
echo "<tr><td>Time of last update</td><td>$lastUpdateTime</td></tr>";
echo "<tr><td>ippMonitor SVN version</td><td>"; include 'version.php'; echo "</td></tr>"; 
echo "<tr><td>Current nightly science status</td><td>$nsStatus</td></tr>"; 
echo "<tr><td>Postage stamp server status</td><td><a href=\"http://pstamp.ipp.ifa.hawaii.edu/status.php\"><font color=\"blue\">here</font></a></td></tr>";
echo "<tr><td>Documentation</td><td><a href=\"http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/wiki/Processing\"><font color=\"blue\">here</font></a></td></tr>";
echo "<tr><td>Cluster load</td><td><a href=\"http://ganglia.pan-starrs.ifa.hawaii.edu/?r=hour&s=descending&hc=6&c=IPP%2520Production\"><font color=\"blue\">here</font></a> <a href=\"http://ganglia.pan-starrs.ifa.hawaii.edu/?m=cpu_report&r=hour&s=by%2520name&c=IPP+Production&h=&sh=1&hc=8&z=small\"><font color=\"blue\">cpu_report</font></a></td></tr>";
echo "<tr><td>Who's using the cluster?</td><td><a href=\"http://ippmonitor.ipp.ifa.hawaii.edu/ippMonitor/clusterMonitor/top.html\"><font color=\"blue\">here</font></a></td></tr>";
echo "<tr><td>IPP metrics</td><td><a href=\"http://ipp0022.ifa.hawaii.edu/ps1sc/ippMetrics\"><font color=\"blue\">here</font></a></td></tr>";
echo "<tr><td>Czar log pages</td><td><a href=\"http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/wiki/PS1_IPP_CzarLogs\"><font color=\"blue\">here</font></a></td></tr>";
echo "<tr><td>Exposure summary</td><td><a href=\"czartool_exposures.php?pass=$pass&proj=$proj\"><font color=\"blue\">here</font></a></td></tr>";

$plotTypeLink = ($plotType == "linear") ? "log" : "linear";
$link = "czartool_labels.php?pass=" . $pass . "&proj=" . $proj . "&mode=" . $selectedMode . "&label=" . $selectedLabel . "&stage=" . $selectedStage . "&revertstage=" . $stage . "&plottype=$plotTypeLink";


echo "</table>\n";

echo "<br>";

       createLabelsTable($pass, 
         $proj, 
         $czardb, 
         $selectedMode, 
         $labels, 
         $distLabels, 
         $pubLabels, 
         $stages, 
         "new", 
         $selectedLabel, 
         $selectedStage, 
         $plotType);

if ($selectedMode == "update") {echo "<br>"; createPStampDataTable();}
echo "<br>";
      createServersTable($pass, $proj,$selectedMode,$czardb, $servers, $selectedLabel, $selectedStage, $plotType);
echo "<br>";
      createSummitDataTable($gpc1db);
echo "<br>";
      createDatesTable($czardb);
echo "<br>";
      # This is awful
      echo $table;
      echo "<tr>";
      createTableTitle("Database replication status (update fix log <a href=\"http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/wiki/Replication_Issues\">here</a>)", 2);
#      echo "<th colspan=\"2\"><h2>Database replication status (update fix log <a href=\"http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/wiki/Replication_Issues\">here</a>)</h2></th>";
      echo "<tr>";
      createTableColumnHeader("Database");
      createTableColumnHeader("Status (sec behind master)");
      showReplicationsStatus($REPL_HOST_GPC1, $REPL_USER_GPC1, $REPL_PASSWORD_GPC1, $REPL_DBNAME_GPC1);
      showReplicationsStatus($REPL_HOST_NEBULOUS, $REPL_USER_NEBULOUS, $REPL_PASSWORD_NEBULOUS, $REPL_DBNAME_NEBULOUS);
      showReplicationsStatus($REPL_HOST_PSTAMP, $REPL_USER_PSTAMP, $REPL_PASSWORD_PSTAMP, $REPL_DBNAME_PSTAMP);
      showReplicationsStatus($REPL_HOST_IPPADMIN, $REPL_USER_IPPADMIN, $REPL_PASSWORD_IPPADMIN, $REPL_DBNAME_IPPADMIN);
      echo "</table>";

echo "</table>";

menu_end();


###########################################################################
#
# Creates a postage stamp status table
#
###########################################################################
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@ippc17/ippRequestServer");
    $qry = $pstampdb->query($sql);
    if (dberror($qry)) {
        echo "<b>error reading pstamp table</b><br>\n";
        echo "<br><small><b> table query : $sql </b></small><br>\n";
        menu_end();
    }

    // set up the table
    global $table;
    echo $table;

    #echo "<table width=\"$width\" align=\"middle\" border=\"1\" cellspacing=\"0\" cellpadding=\"4\" style=\"font-size:80%\">\n";
    echo "<tr>";
    createTableTitle("Postage stamp requests", 3);
    echo "<tr>";
    createTableColumnHeader("Label");
    createTableColumnHeader("Unfinished jobs");
    createTableColumnHeader("Priority");

    // list the results
    while ($qry->fetchInto($row)) {

        echo "<tr>";

        echo "<td>$row[0]</td>";
        echo "<td>$row[1]</td>";
        echo "<td>$row[2]</td>";
        
        echo "</tr>";
    }

    echo "</table>\n";

}

###########################################################################
#
# Creates a table of summitExp against rawExp
#
###########################################################################
function createSummitDataTable($gpc1db) {

    $date = date("Y-m-d");
    $sql = "SELECT DISTINCT exp_type FROM summitExp WHERE dateobs > '$date'";

    $qry = $gpc1db->query($sql);
    if (dberror($qry)) {
        echo "<b>error reading newExp table</b><br>\n";
        echo "<br><small><b> table query : $sql </b></small><br>\n";
        menu_end();
    }

    // set up the table
    global $table;
    echo $table;
    echo "<tr>";
    createTableTitle("Status of last night's data", 3);
    echo "<tr>";
    createTableColumnHeader("Exposure type");
    createTableColumnHeader("At summit");
    createTableColumnHeader("Registered at MHPCC");

    $msg = "No science images taken since $date";

    // list the results
    while ($qry->fetchInto($expType)) {

        $sql = "SELECT COUNT(*) FROM summitExp WHERE dateobs > '$date' AND exp_type = '$expType[0]'";
        $qry2 = $gpc1db->query($sql);
        $qry2->fetchInto($summit);
        $sql = "SELECT COUNT(*) FROM summitExp JOIN rawExp ON summitExp.exp_name = rawExp.exp_name WHERE summitExp.dateobs > '$date' AND summitExp.exp_type = '$expType[0]'";
        $qry2 = $gpc1db->query($sql);
        $qry2->fetchInto($mhpcc);

        echo "<tr>";

        if ($summit[0] == $mhpcc[0]) $error = 0;
        else $error = 1;

        echo "<td>$expType[0]</td>";
        echo "<td>$summit[0]</td>";
        
        createFormattedTableCell("", "", $mhpcc[0], $error, 0);

        echo "</tr>";
    }

    echo "</table>\n";

}

###########################################################################
#
# Gets labels for this server
#
###########################################################################
function getLabels($db, $server) {

    # order by descending priority as set in gpc1 database
    $sql = "SELECT label FROM current_labels WHERE server LIKE '$server' ORDER BY priority DESC";
    if ($debug) {echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
    while ($qry->fetchInto($row)) {

        $labels[] = $row[0];
    }

    return $labels;
}

###########################################################################
#
# Creates table for all labels showing all stages for given 'state'
#
###########################################################################
function createLabelsTable(
        $pass, 
        $proj, 
        $db, 
        $selectedMode, 
        $labels, 
        $distLabels, 
        $pubLabels, 
        $stages, 
        $selectedState, 
        $selectedLabel, $selectedStage, $plotType) {

    $isUpdate = ($selectedMode == "update");
    global $headerColor;
    global $columnHeaderColor;

    // setup table
    global $table;
    echo $table;
    createTableTitle("Label status", 13);

    echo "<tr>\n";
    $link = "czartool_labels.php?pass=".$pass
        ."&proj=".$proj
        ."&mode=".$selectedMode
        ."&label=".$selectedLabel
        ."&stage=all_stages"
        ."&plottype=".$plotType;
    createFormattedTableCell(($selectedStage != "all_stages"), $link, "All stages", 0, "$columnHeaderColor");

    #echo "<th>Label (in order of priority)</th>";
    foreach ($stages as &$stage) {

        if ($stage == $selectedStage) $showLink = 0;
        else $showLink = 1;
        $link = 
            "czartool_labels.php?pass=".$pass
            ."&proj=".$proj 
            ."&mode=" .$selectedMode
            ."&label=".$selectedLabel
            ."&stage=".$stage
            ."&plottype=".$plotType;

        createFormattedTableCell($showLink, $link, $stage, 0, "$columnHeaderColor");
#        createTableColumnHeader("Dates");
    }

    // setup default link
    $defaultlink = 
        "czartool_labels.php?pass=".$pass
        ."&proj=".$proj
        ."&mode=" .$selectedMode
        ."&label=".$selectedLabel
        ."&stage=".$selectedStage
        ."&plottype=".$plotType;

    $searchState = "new";
    if ($selectedMode == "update") $searchState = "update";

    // write rows
    foreach ($labels as &$thisLabel) {

        $distributing = false;
        $publishing = false;
        foreach ($distLabels as &$distLabel) {

            if ($thisLabel == $distLabel) { $distributing = true; break;}
        }
        foreach ($pubLabels as &$pubLabel) {

            if ($thisLabel == $pubLabel) { $publishing = true; break;}
        }

        // show distribution/publishing status
        echo "<tr>\n";

        // create link to label summary page for each label
        if ($thisLabel == $selectedLabel) $showLink = 0;
        else $showLink = 1;
        $link = 
            "czartool_labels.php?pass=".$pass
            ."&proj=".$proj
            ."&mode=" .$selectedMode
            ."&label=".$thisLabel
            ."&stage=".$selectedStage
            ."&plottype=".$plotType;
        createFormattedTableCell($showLink, $link, $thisLabel, 0, null);

        $str = "";
        $anyFaults = false; 

        $link = $defaultlink;
        getStateAndFaults($db, $thisLabel, $selectedState, "burntool", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, 0);

        $link = "failedChipProcessedImfile.php?pass=" . $pass . "&proj=" . $proj . "&chipRun.label=" . $thisLabel . "&chipRun.state=".$searchState;
        getStateAndFaults($db, $thisLabel, $selectedState, "chip", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, 0);

        $link = "failedCamProcessedExp.php?pass=" . $pass . "&proj=" . $proj . "&camRun.label=" . $thisLabel . "&camRun.state=".$searchState;
        getStateAndFaults($db, $thisLabel, $selectedState, "cam", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, 0);

        $link =  "failedFakeProcessedImfile.php?pass=" . $pass . "&proj=" . $proj . "&fakeRun.label=" . $thisLabel . "&fakeRun.state=".$searchState;
        getStateAndFaults($db, $thisLabel, $selectedState, "fake", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, 0);

        $link = "failedWarpSkyfiles.php?pass=" . $pass . "&proj=" . $proj . "&warpRun.label=" . $thisLabel . "&warpRun.state=".$searchState;
        getStateAndFaults($db, $thisLabel, $selectedState, "warp", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, 0);

        $link = "failedStackSkyfile.php?pass=" . $pass . "&proj=" . $proj . "&stackRun.label=" . $thisLabel . "&stackRun.state=".$searchState;
        getStateAndFaults($db, $thisLabel, $selectedState, "stack", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, 0);

        $link = "failedDiffSkyfile.php?pass=" . $pass . "&proj=" . $proj . "&diffRun.label=" . $thisLabel . "&diffRun.state=".$searchState;
        getStateAndFaults($db, $thisLabel, $selectedState, "diff", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, 0);

        $link = $defaultlink;
        getStateAndFaults($db, $thisLabel, $selectedState, "magic", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, (!$isUpdate && !$distributing) ? "gray" : null);

        $link = $defaultlink;
        getStateAndFaults($db, $thisLabel, $selectedState, "magicDS", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, (!$isUpdate && !$distributing) ? "gray" : null);

        $link = $defaultlink;
        getStateAndFaults($db, $thisLabel, $selectedState, "dist", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, (!$isUpdate && !$distributing) ? "gray" : null);

        $link = "publishDone.php?pass=" . $pass . "&proj=" . $proj . "&publishRun.label=" . $thisLabel . "&publishRun.state=".$searchState;
        getStateAndFaults($db, $thisLabel, $selectedState, "pub", $str, $anyFaults);
        createFormattedTableCell($anyFaults, $link, $str, $anyFaults, (!$isUpdate && !$publishing) ? "gray" : null);

        echo "</tr>\n";
    }

    echo "<tr>\n";
    $link = "czartool_labels.php?pass=" . $pass . "&proj=" . $proj . "&mode=" . $selectedMode . "&label=all&stage=".$selectedStage."&plottype=".$plotType;
    createFormattedTableCell(($selectedLabel != "all"), $link, "All labels", 0, null);

    createFormattedTableCell(0, null, "Reverting?", 0, "$columnHeaderColor");

    foreach ($stages as &$stage) {

        if ($stage == "burntool") continue;
        $reverting = getRevertStatus($db, $stage);
        #$link = 
        #    "czartool_labels.php?pass=".$pass
        #    ."&proj=".$proj
        #    ."&mode=" .$selectedMode
        #    ."&label=".$selectedLabel
        #    ."&stage=".$selectedStage
        #    ."&plottype=".$plotType
        #    ."&revertstage=".$stage
        #    ."&revertmode=";
#
        #if(!$reverting) {$label =  "Start";$link = $link . "on";}
        #if($reverting) {$label = "Stop";$link = $link . "off";}
        if(!$reverting) {$label =  "no";}
        if($reverting) {$label = "yes";}
        unset($reverting);
        #createFormattedTableCell(1, $link, $label, 0, "$columnHeaderColor");
        createFormattedTableCell(0, null, $label, 0, "$columnHeaderColor");
        #echo "<td><a href=\"$link\"><font color=\"blue\">$label</font></td>";

    }

    echo "</tr>\n";
    if ($selectedLabel == "all") $showLink = 0;
    else  $showLink = 1;
    $link = "czartool_labels.php?pass=".$pass."&proj=".$proj."&mode=".$selectedMode."&label=all&stage=".$selectedStage."&plottype=".$plotType;

    echo "</tr>\n";
    echo "</table>\n";
}


###########################################################################
#
# Creates a table title
#
###########################################################################
function createTableTitle($str, $colSpan) {

    global $headerColor;
    echo "<th colspan=\"$colSpan\"bgcolor=\"$headerColor\"><h2>$str</h2></th>";
}

###########################################################################
#
# Creates a table header cell
#
###########################################################################
function createTableColumnHeader($str) {

    global $columnHeaderColor;
    echo "<th bgcolor=\"$columnHeaderColor\"><h3>$str</h3></th>";
}

###########################################################################
#
# Creates table cell with a red background if an error
#
###########################################################################
function createFormattedTableCell($showLink, $link, $str, $error, $color) {

    $fontColor = $error ? "black" : "blue";

    if ($error) echo "<td bgcolor=\"red\">";
    else if ($color) echo "<td bgcolor=\"$color\">";
    else  echo "<td>";
    if ($showLink) echo "<a href=\"$link\"><font color=\"$fontColor\">$str</font></td>";
    else if ($str) echo "$str</td>";
    else echo "&nbsp;</td>";
}

###########################################################################
#
# Sets reverts status 
#
###########################################################################
function setRevertStatus($db, $stage, $reverting) {

    $sql = "UPDATE reverts SET reverting = $reverting WHERE stage LIKE '$stage'";
    if ($debug) {echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
}

###########################################################################
#
# Returns current nightly science status 
#
###########################################################################
function getNightlyScienceStatus($db) {

    $sql = "SELECT status FROM nightlyscience";
    if ($debug) {echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
    $qry->fetchInto($row);

    return $row[0];
}

###########################################################################
#
#  Turns reverts on or off for a given task
#
###########################################################################
function turnRevertsOnOff($db, $stage, $mode) {

    exec("czartool_revert.pl -t $stage -o $mode", $response, $status);
    $currentRevertMode;
    if ($response[0] == "off") $currentRevertMode = 0;
    else if ($response[0] == "on") $currentRevertMode = 1;

    setRevertStatus($db, $stage, $currentRevertMode);
}

###########################################################################
#
# Returns whether this stage is reverting or not
#
###########################################################################
function getRevertStatus($db, $stage) {

    $anyFaults = false;

    $sql = "SELECT reverting FROM reverts WHERE stage LIKE '$stage'";
    if ($debug) {echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
    $qry->fetchInto($row);

    return $row[0];
}

###########################################################################
#
# Returns state and fault-count (if new) as a string
#
###########################################################################
function getStateAndFaults($db, $label, $state, $stage, &$str, &$anyFaults) {

    $anyFaults = false;
    $sql = "SELECT pending, faults FROM $stage WHERE label LIKE '$label' ORDER BY timestamp DESC LIMIT 1";
    if ($debug) {echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\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;
        }
    }
}

###########################################################################
#
# Populates a tables with the current dates listed in the server_dates table 
#
###########################################################################
function createDatesTable($db) {

    global $table;
    echo $table;
    echo "<tr>\n";
    createTableTitle("Current dates for selected pantasks servers", 3);
    echo "<tr>\n";
    createTableColumnHeader("Server");
    createTableColumnHeader("Dates");
    echo "</tr>\n";

    createServerDateRow($db, "stdscience");
    createServerDateRow($db, "registration");

    echo "</table>\n";
}


###########################################################################
#
# Checks the status of all the pantasks servers 
#
###########################################################################
function createServerDateRow($db, $server) {

    $sql = "SELECT date FROM server_dates WHERE server LIKE '$server' ORDER BY date";
    if ($debug) {echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
    $dates = ""; 
    $firstIn = true;
    while ($qry->fetchInto($row)) {

        if ($firstIn) {

            $dates = $row[0];
            $firstIn = false;
        }
        else $dates = $dates . ", " . $row[0];
    }

    echo "<tr>";
    echo "<td>$server</td>";
    echo "<td>$dates</td>";
    echo "</tr>";

}

###########################################################################
#
# Checks the status of all the pantasks servers 
#
###########################################################################
function createServersTable($pass, $proj, $selectedMode, $db, $servers, $selectedLabel, $selectedStage, $plotType) {

    // set up table columns
    global $table;
    echo $table;
    echo "<tr>";
    createTableTitle("Pantasks server status", 3);
    echo "<tr>";
    createTableColumnHeader("Server");
    createTableColumnHeader("Alive?");
    createTableColumnHeader("Running?");

    //write_header_cell($class, "Server");
    //write_header_cell($class, "Alive?");
    //$link = "czartool_labels.php?pass=".$pass
    //    ."&proj=".$proj
    //    ."&label=".$selectedLabel
    //    ."&stage=".$selectedStage
    //    ."&plottype=".$plotType
    //    ."&allservercmd=stop";

    //$link = ""; # TODO removed links temporarily
    //    write_table_cell($class, '%s', $link, "Stop all");
    //$link = "czartool_labels.php?pass=".$pass
    //    ."&proj=".$proj
    //    ."&label=".$selectedLabel
    //    ."&stage=".$selectedStage
    //    ."&plottype=".$plotType
    //    ."&allservercmd=run";

    //$link = ""; # TODO removed links temporarily
    //    write_table_cell($class, '%s', $link, "Run all");
    echo "</tr>\n";

    foreach ($servers as &$server) {

        getServerStatus($db, $server, $alive, $running);

        echo "<tr>\n";
        $link = "czartool_labels.php?pass=".$pass
            ."&proj=".$proj
            ."&mode=".$selectedMode
            ."&server=".$server
            ."&label=".$selectedLabel
            ."&stage=".$selectedStage
            ."&plottype=".$plotType;

        #$link = ""; # TODO removed links temporarily
        //write_table_cell($class, '%s', $link, $server);
        //write_table_cell($class, '%s', "", $alive ? "yes" : "NO");
        //echo "<td>$server</td>";
        createFormattedTableCell(1, $link, $server, 0, 0);
        createFormattedTableCell(0, "", $alive ? "yes" : "NO", !$alive, 0);
        createFormattedTableCell(0, "", $running ? "yes" : "NO", !$running, 0);

        //if ($alive) {

          //  $link = "czartool_labels.php?pass=".$pass
            //    ."&proj=".$proj
              //  ."&label=".$selectedLabel
              //  ."&stage=".$selectedStage
              //  ."&plottype=".$plotType
              //  ."&server=".$server
              //  ."&servercmd=";

            //if ($running)  {

              //  $link = $link . "stop";
              //  $link = ""; # TODO removed links temporarily
              //      write_table_cell($class, '%s', $link, "stop");
              //  write_table_cell($class, '%s', "", "");
           // }
           // else {

             //   $link = $link . "run";
              //  write_table_cell($class, '%s', "", "");
              //  $link = ""; # TODO removed links temporarily
               //     write_table_cell($class, '%s', $link, "run");
           // }
       // }
       // else {

         //   write_table_cell($class, '%s', "", "");
         //   write_table_cell($class, '%s', "", "");
       // }
        echo "</tr>\n";
    }

    echo "</table>\n";
}

###########################################################################
#
#  Commands a server to stop or run 
#
###########################################################################
function serverStopRun($db, $server, $cmd) {

    exec("czartool_serverstoprun.pl -s $server -c $cmd", $response, $status);
    $alive = 0;
    $running = 0;
    if ($response[0] == "running") {$alive = 1; $running = 1;}
    else if ($response[0] == "stopped") {$alive = 1; $running = 0;} 
    else if ($response[0] == "dead") {$alive = 0; $running = 0;}
    setServerStatus($db, $server, $alive, $running);
}

###########################################################################
#
# Sets server status 
#
###########################################################################
function setServerStatus($db, $server, $alive, $running) {

    $sql = "INSERT INTO servers (server, alive, running) VALUES ('$server', $alive, $running)";

    if ($debug) {echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
}


###########################################################################
#
# Returns state and fault-count (if new) as a string
#
###########################################################################
function getServerStatus($db, $server, &$alive, &$running) {

    $sql = "SELECT alive, running FROM servers WHERE server LIKE '$server' ORDER BY timestamp DESC LIMIT 1";
    if($debug){echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
    $qry->fetchInto($row);

    $alive = $row[0];
    $running = $row[1];
}

###########################################################################
#
# Returns time of last czarDb update 
#
###########################################################################
function getLastUpdateTime($db) {

    $anyFaults = false;
    $sql = "SELECT timestamp FROM chip ORDER BY timestamp DESC LIMIT 1";
    if ($debug) {echo "$sql<br>";}

    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
    $qry->fetchInto($row);

    return $row[0];
}

###########################################################################
#
# Shows the status of the provided pantasks server 
#
###########################################################################
function showServerStatus($server) {

    echo "<p> Status for $server server </p>";

    $results=array();
    exec("czartool_getServerStatus.pl -s $server", $results, $status);

    foreach ($results as &$line) {
        echo "<pre>\n";
        echo "$line\n";
        echo "</pre>\n";

    }
}

###########################################################################
#
# Shows the status of the various replication mysql servers
#
###########################################################################
function showReplicationsStatus($replHost, $replUser, $replPassword, $replDatabaseName) {
    #print "<br>$replHost, $replUser, $replPassword, $replDatabaseName<br/>";
    $dbRepl = DB::connect("mysql://$replUser:$replPassword@$replHost");
    if (PEAR::isError($dbRepl)) {
        echo "<tr><td>$replDatabaseName</td><td bgcolor=\"red\">MySQL DB connection error - check configuration</td></tr>";
        //die("MySQL DB connection error: Check the configuration for $replDatabaseName");
        //die("");
        return -1;

    }
    $res = $dbRepl->query('SHOW SLAVE STATUS');
    while ($res->fetchInto($row)) {
        # Have a look for Last_Errno in http://dev.mysql.com/doc/refman/5.0/en/show-slave-status.html
        $errorStatusInMySql = $row[18];
	$sec_behind = $row[32];
        $replStatus = ($errorStatusInMySql==0?"OK ($sec_behind)":"<font color=\"red\">PROBLEM</font>");
        echo "<tr><td>$replDatabaseName</td><td>$replStatus</td></tr>";
        if ($errorStatusInMySql!=0) {
            echo "<tr><td colspan=\"2\">Connect to $replDatabaseName replication host, execute 'SHOW SLAVE STATUS', and fix the problem.<br/>";
            echo "Information <a href=\"http://dev.mysql.com/doc/refman/5.0/en/show-slave-status.html\">here</a></td></tr>";
        }
    }
    $dbRepl->disconnect();
}

?>

