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

$ID = checkID();

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

// connect to czarDb 
$czardb = dbconnect($CZARDBNAME); 
$lastUpdateTime = getLastUpdateTime($czardb);

$projectdb = 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.czar.dat";}

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

$stages=array(
        "chip",
        "cam",
        "fake",
        "warp",
        "stack",
        "staticsky",
        "fullforce",
        "diff",
        "dist", 
        "pub",
	);
        # "chipbackground");

$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,
);	     

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

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

// The Update mode of czartool is obsolete since we don't use the update
// pantasks. 
// $selectedMode = $_GET[mode];
$selectedMode = "stdscience";

$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=\"900\" align=\"middle\" border=\"1\" cellspacing=\"0\" cellpadding=\"4\" style=\"font-size:80%\">\n";

// $labels is generated with sequential integer key values
$labels = getLabels($czardb, $proj, $selectedMode);
$distLabelsIndex = getLabels($czardb, $proj, "distribution");
for ($i = 0; $i < count($distLabelsIndex); $i++) {
  $distLabelsExist[$distLabelsIndex[$i]] = true;
}

// publishing is in stdscience now
# $pubLabelsIndex = getLabels($czardb, "publishing");
$pubLabelsIndex = $labels;
for ($i = 0; $i < count($pubLabelsIndex); $i++) {
  $pubLabelsExist[$pubLabelsIndex[$i]] = true;
}

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

// The Update mode of czartool is obsolete. selectedMode is hardcoded to stdscience above.
$isUpdate = ($selectedMode == "update");
$isUpdate = 0;

// hijack the text and link at the top to create a link to the czartool monitoring the other project database
// I.E. Switch from gpc1 to gpc2 or gpc2 to gpc1

$modeText = "Monitoring $proj";
if ($proj == 'gpc1') {
    $newproj = 'gpc2';
} else {
    $newproj = 'gpc1';
}
$link = "czartool_labels.php?pass=".$pass."&mode=".$selectedMode."&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 "<h2 align=\"middle\">$modeText (change to <a href=\"$link&proj=$newproj\"><font color=\"blue\">$newproj</font></a>)</h2>";
echo "</td>";
echo "</tr>";

// PLOTS
echo "<tr valign=\"top\">";
echo "<td style=width:100px;text-align:top;\">";
 # time series plot
echo "<br><img src=\"czartool_getplot.php?mode=$selectedMode&type=t&label=$selectedLabel&proj=$proj&stage=$selectedStage&plottype=linear\"><br>";
# log time series plot
echo "<br><img src=\"czartool_getplot.php?mode=$selectedMode&type=t&label=$selectedLabel&proj=$proj&stage=$selectedStage&plottype=log\"><br>";
 # rate time series plot
echo "<br><img src=\"czartool_getplot.php?mode=$selectedMode&type=rt&label=$selectedLabel&proj=$proj&stage=$selectedStage&plottype=linear\"><br>";
 # histogram plot
# echo "<img src=\"czartool_getplot.php?mode=$selectedMode&type=h&label=$selectedLabel&proj=$proj&stage=$selectedStage&plottype=linear\"><br>";
 # storage plot
echo "<br><a href=\"czartool_getplot.php?type=sl\"><img title=\"Click to enlarge me\" src=\"czartool_getplot.php?type=s\"></a><br>";
 # storage timeseries
echo "<br><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;\">";

/////////////////////////////////////////
// timer begin for status and links
/////////////////////////////////////////
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

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

echo "<br>";
echo $table;
createTableTitle("Status and links", 2);
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\" target=new><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\" target=new><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> 
              <a href=\"http://ganglia.pan-starrs.ifa.hawaii.edu/?m=mem_report&r=hour&s=by%2520name&c=IPP+Production&h=&sh=1&hc=8&z=small\">
              <font color=\"blue\">mem_report</font></a></td></tr>";
echo "<tr><td>IPP metrics</td>
          <td><a href=\"https://ps1wiki.ifa.hawaii.edu/data/metrics/index.html\"><font color=\"blue\" target=new>here</font></a></td></tr>";
echo "<tr><td>Czar log pages</td>
          <td><a href=\"https://confluence.ipp.ifa.hawaii.edu/pages/viewrecentblogposts.action?key=IPPCZAR\" target=new><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>";
echo "<tr><td>Confluence</td>
          <td><a href=\"https://confluence.ipp.ifa.hawaii.edu/display/IPPCZAR/IPP-Czaring+Home\" target=new><font color=\"blue\">here</font></a></td></tr>";
echo "<tr><td>JIRA</td>
          <td><a href=\"https://jira.ipp.ifa.hawaii.edu/secure/Dashboard.jspa\" target=new><font color=\"blue\">here</font></a></td></tr>";
echo "<tr><td>data store</td>
          <td><a href=\"https://svn.ifa.hawaii.edu/\" target=new><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";

// timer finished
echo "<tr><th colspan=4>";
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 3);
echo "loading in $total_time seconds.";
echo "</th></tr>";
echo "</table><br>";

createLabelsTable($pass, $proj, $czardb, $selectedMode, $labels, 
  $distLabelsExist, $pubLabelsExist, $stages, "new", $selectedLabel, 
  $selectedStage, $plotType);
if ($selectedMode == "update") {echo "<br>"; createPStampDataTable();} 

# XXX EAM : 2017.09.17 : debugging slowness
# CCL : 2019.11.09 : debugging slowness
//createSummitDataTable($projectdb); // this is slow because of the join between summitExp and rawExp, but not the major one
createSummitDataTable2($projectdb); // this is slow because of the join between summitExp and rawExp, but not the major one

createServersTable($pass, $proj,$selectedMode,$czardb, $servers, $selectedLabel, $selectedStage, $plotType); 
// this was the major slowness part due to large table in czardb

createChunkDataTable($projectdb);  // this is another part for slowness during nightly processing

createDatesTable($czardb, $proj); 

/////////////////////////////////////////
// timer begin for Database status
/////////////////////////////////////////
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

// status table at top
  echo $table;
  echo "<tr>";
  createTableTitle("Database status (update fix log <a href=\"http://svn.pan-starrs.ifa.hawaii.edu/trac/ipp/wiki/Replication_Issues\">here</a>)", 2);
  
  echo "<tr>";
  createTableColumnHeader("Database");
  createTableColumnHeader("Status (sec behind master)");
  showDatabaseStatus($HOST_GPC1, $USER_GPC1, $PASSWORD_GPC1, $DBNAME_GPC1);
  showReplicationsStatus($REPL_HOST_GPC1, $REPL_USER_GPC1, $REPL_PASSWORD_GPC1, $REPL_DBNAME_GPC1);
  showReplicationsStatus($REPL_HOST_GPC1_SECONDARY, $REPL_USER_GPC1_SECONDARY, $REPL_PASSWORD_GPC1_SECONDARY, $REPL_DBNAME_GPC1_SECONDARY);
  
  echo "<tr>";
  showDatabaseStatus($HOST_NEBULOUS, $USER_NEBULOUS, $PASSWORD_NEBULOUS, $DBNAME_NEBULOUS);
  showReplicationsStatus($REPL_HOST_NEBULOUS, $REPL_USER_NEBULOUS, $REPL_PASSWORD_NEBULOUS, $REPL_DBNAME_NEBULOUS);
  showReplicationsStatus($REPL_HOST_NEBULOUS_SECONDARY, $REPL_USER_NEBULOUS_SECONDARY, $REPL_PASSWORD_NEBULOUS_SECONDARY, $REPL_DBNAME_NEBULOUS_SECONDARY);
  showReplicationsStatus($REPL_HOST_DATASTORE_SECONDARY, $REPL_USER_DATASTORE_SECONDARY, $REPL_PASSWORD_DATASTORE_SECONDARY, $REPL_DBNAME_DATASTORE_SECONDARY);
  
//  some mysql replication slaves to add when they are returned to service
//  showReplicationsStatus($REPL_HOST_PSTAMP, $REPL_USER_PSTAMP, $REPL_PASSWORD_PSTAMP, $REPL_DBNAME_PSTAMP);
//  showReplicationsStatus($REPL_HOST_ISP, $REPL_USER_ISP, $REPL_PASSWORD_ISP, $REPL_DBNAME_ISP);

// timer finished
echo "<tr><th colspan=2>";
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 3);
echo "loading in $total_time seconds.";
echo "</th></tr>";
echo "</table><br>";

/////////////////////////////////////////
// timer begin for critical machines
/////////////////////////////////////////
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;

// status table at top
  $PingStatus = "<font style=\"BACKGROUND-COLOR: yellow\"  color=\"red\">DOWN</font>";
  echo $table;
  echo "<tr>";
  createTableTitle("Critical machine status", 3);

  echo "<tr>";
  createTableColumnHeader("Hostname");
  createTableColumnHeader("Status");
  createTableColumnHeader("Loading time");
  showPingStatus($HOST_GATEWAY, $DBNAME_GATEWAY);
  showPingStatus($HOST_GATEWAY_SECONDARY, $DBNAME_GATEWAY_SECONDARY);
  showPingStatus($HOST_APACHE1, $DBNAME_APACHE1);
  showPingStatus($HOST_APACHE2, $DBNAME_APACHE2);
  showPingStatus($HOST_APACHE3, $DBNAME_APACHE3);
  showPingStatus($HOST_APACHE4, $DBNAME_APACHE4);
  showPingStatus($HOST_APACHE5, $DBNAME_APACHE5);
  showPingStatus($HOST_APACHE6, $DBNAME_APACHE6);
  showPingStatus($HOST_APACHE7, $DBNAME_APACHE7);
  showPingStatus($HOST_APACHE8, $DBNAME_APACHE8);
//     echo "<td>ippc70 (apache node) <td> $PingStatus <td> 0 seconds ";  // comment out if ippc70 is back
  showPingStatus($HOST_PROXY, $DBNAME_PROXY);
  showPingStatus($HOST_SVN, $DBNAME_SVN);
  showPingStatus($HOST_CONFLUENCE, $DBNAME_CONFLUENCE);

// timer finished
echo "<tr><th colspan=3>";
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 3);
echo "loading in $total_time seconds.";
echo "</th></tr>";
echo "</table><br>";
    

menu_end();


#############################################################################
#############################  Functions Below  #############################
#############################################################################

###########################################################################
#
# 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@ipp113/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 chunk progress
#
###########################################################################
function createChunkDataTable($projectdb) {

    // timer start
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $start = $time;

    // function begin
    #First find the reference exposure ID to speed up the subsequent queries
    $date = gmdate("Y-m-d");
    $sql = "SELECT MAX(exp_id) FROM rawExp WHERE dateobs >= date_sub('$date', interval 20 day) AND dateobs <= date_sub('$date', interval 10 day)";
    $qry = $projectdb->query($sql);
    if (dberror($qry)) {
        print "*** WARNING: no data in period 10-20 days before requested date (query will be slower) **\n";
        $refExpID = 0; 
    }
    $qry->fetchInto($refExpID);
    if ($refExpID[0] = 'NULL') {
        $refExpID[0] = 0;
    }

    #do a query of raw/chip/cam/warp counts for this night, split by chunk. 
    #we need to do a subquery to find the total number of skycells in warpSkyfile, as well as the number of those with a bad quality
    $query = "SELECT suba.chunk,suba.Nvis1,suba.Nvis2,suba.Nvis3,suba.Nvis4,suba.Ncamgood,suba.Ncambad,subb.Nwarpgood,subb.Nwarpbad FROM ";
    $query .= "    (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(if(rawExp.comment LIKE '%visit 1%',1,NULL)) AS Nvis1,";
    $query .= "    count(if(rawExp.comment LIKE '%visit 2%',1,NULL)) AS Nvis2,count(if(rawExp.comment LIKE '%visit 3%',1,NULL)) AS Nvis3,";
    $query .= "    count(if(rawExp.comment LIKE '%visit 4%',1,NULL)) AS Nvis4, count(if(camProcessedExp.sigma_ra <= 5 AND camProcessedExp.sigma_dec <= 5 AND camProcessedExp.quality = 0,1,NULL)) AS Ncamgood,";
    $query .= "    count(if(camProcessedExp.sigma_ra > 5 OR camProcessedExp.sigma_dec > 5 OR camProcessedExp.quality > 0,1,NULL)) AS Ncambad";
    $query .= "    FROM rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) JOIN camProcessedExp USING (cam_id) LEFT JOIN fakeRun USING (cam_id) LEFT JOIN warpRun USING (fake_id)";
    $query .= "    WHERE dateobs like '$date%' AND exp_id > $refExpID[0] AND exp_type = 'OBJECT' AND rawExp.comment like '%visit%'";
    $query .= "    AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
    $query .= "    GROUP BY chunk ORDER BY dateobs) as suba ";
    $query .= "LEFT JOIN";
    $query .= "    (SELECT subc.chunk, count(if(subc.Nwarpskycellbad < subc.Nwarpskycell,1,NULL)) AS Nwarpgood, count(if(subc.Nwarpskycellbad = subc.Nwarpskycell,1,NULL)) AS Nwarpbad";
    $query .= "    FROM ";
    $query .= "      (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(warpSkyfile.quality) AS Nwarpskycell,count(if(warpSkyfile.quality > 0,1,NULL)) AS Nwarpskycellbad";
    $query .= "      FROM rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) LEFT JOIN fakeRun USING (cam_id) LEFT JOIN warpRun USING (fake_id) JOIN warpSkyfile USING (warp_id)";
    $query .= "      WHERE dateobs like '$date%' AND exp_id > $refExpID[0] AND exp_type = 'OBJECT' AND rawExp.comment like '%visit%'";
    $query .= "      AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
    $query .= "      GROUP BY exp_name) as subc";
    $query .= "    GROUP BY chunk) AS subb ";
    $query .= "ON suba.chunk=subb.chunk";

    $qry = $projectdb->query($query);
    if (dberror($qry)) {
        echo "<b>error querying SQL table</b><br>\n";
        echo "<br><small><b> table query : $query </b></small><br>\n";
        menu_end();
    }

    // set up the table
    global $table;
    echo $table;
    echo "<tr>";
    createTableTitle("Status of last night's chunks", 10);
    echo "<tr>";
    createTableColumnHeader("Chunk");
    createTableColumnHeader("Nobs");
    createTableColumnHeader("Ncam");
    createTableColumnHeader("Nwrp");
    createTableColumnHeader("wrp_pub");
    createTableColumnHeader("Nwwd");
    createTableColumnHeader("wwd_pub");
    createTableColumnHeader("Nwsd");
    createTableColumnHeader("wsd_pub");
    createTableColumnHeader("Completion");

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

    // list the results
    while ($qry->fetchInto($row)) {
        $Nvis1 = 0;
        $Nvis2 = 0;
        $Nvis3 = 0;
        $Nvis4 = 0;
        $Ncamgood = 0;
        $Ncambad = 0;
        $Nwarpgood = 0;
        $Nwarpbad = 0;
        $NexpWWdiff = 0;
        $Npubwarps = 0;

        $chunkname = $row[0];
        if($row[1] > 0) $Nvis1 = $row[1];
        if($row[2] > 0) $Nvis2 = $row[2];
        if($row[3] > 0) $Nvis3 = $row[3];
        if($row[4] > 0) $Nvis4 = $row[4];
        if($row[5] > 0) $Ncamgood = $row[5];
        if($row[6] > 0) $Ncambad = $row[6];
        if($row[7] > 0) $Nwarpgood = $row[7];
        if($row[8] > 0) $Nwarpbad = $row[8];

        #set flags if present
        if($Ncambad >= 1) $badcamflag = 1;
        if($Nwarpbad >= 1) $badwarpflag = 1;

        $query1 = "SELECT suba.chunk, SUM(IF(suba.Nvisits = 2,1,NULL)) as totvis2, SUM(IF(suba.Nvisits = 3 OR suba.Nvisits = 4,2,NULL)) as totvis34 FROM";
        $query1 .= "    (SELECT substr(comment, 1, position(' ' in comment)) AS chunk,count(distinct(comment)) AS Nvisits FROM";
        $query1 .= "    rawExp LEFT JOIN chipRun USING (exp_id) LEFT JOIN camRun USING (chip_id) WHERE dateobs like '$date%'";
        $query1 .= "    AND exp_id > $refExpID[0] AND exp_type = 'OBJECT' AND rawExp.comment like '%visit%' AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%') AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL'";
        $query1 .= "    AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' group by object) as suba group by chunk;";
        $qry1 = $projectdb->query($query1);
        while ($qry1->fetchInto($row)) {$NexpWWdiff = $row[1] + $row[2];}

        #find the unique diff_ids for each chunk, to be matched to the diffRun catalog
        $query2 = "     SELECT diff_id,warp1,stack1,warp2,stack2 FROM ";
        $query2 .= "           diffInputSkyfile JOIN warpRun ON (warp1=warp_id OR warp2=warp_id) JOIN fakeRun USING (fake_id) JOIN camRun USING (cam_id) JOIN camProcessedExp USING (cam_id) JOIN chipRun USING (chip_id) JOIN rawExp USING (exp_id)";
        $query2 .= "           WHERE rawExp.dateobs LIKE '$date%' AND exp_id > $refExpID[0] AND exp_type = 'OBJECT' AND rawExp.comment like '%visit%' AND (obs_mode LIKE '%SS%' OR obs_mode LIKE '%BRIGHT%')";
        $query2 .= "            AND obs_mode NOT LIKE 'ENGINEERING' AND obs_mode NOT LIKE 'MANUAL' AND substr(comment, 1, position(' ' in comment)) LIKE '$chunkname%' GROUP By diff_id";
        $qry2 = $projectdb->query($query2);

        $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 ++;
                  }
              }
        }
        #pull out the unique warp_ids
        $Npubwarps = count($wrpcnt);

        #check for bad diffs 
        if(($NWWdiffbad >= 1) or ($NWSdiffbad >= 1)) $baddiffflag = 1;

        if ($Nvis1 != $Nvis4) {
          if ($NWWdiffpub != $NexpWWdiff) $complete = "chunk incomplete, not done";  
          if ($NWWdiffpub == $NexpWWdiff) $complete = "chunk incomplete, done";  
        }
        if ($Nvis1 == $Nvis4) {
          if ($NWWdiffpub != $NexpWWdiff) $complete = "chunk complete, not done";  
          if ($NWWdiffpub == $NexpWWdiff) $complete = "chunk complete, done";  

        }

        echo "<tr>";

        echo "<td>$chunkname</td>";
        echo "<td>$Nvis1/$Nvis2/$Nvis3/$Nvis4</td>";
        echo "<td>$Ncamgood/$Ncambad</td>";
        echo "<td>$Nwarpgood/$Nwarpbad</td>";
        echo "<td>$Npubwarps</td>";
        echo "<td>$NWWdiffgood/$NWWdiffbad</td>";
        echo "<td>$NWWdiffpub</td>";
        echo "<td>$NWSdiffgood/$NWSdiffbad</td>";
        echo "<td>$NWSdiffpub</td>";
        echo "<td>$complete</td>";

        echo "</tr>";
    }


    // timer finished
    echo "<tr><th colspan=10>";
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $finish = $time;
    $total_time = round(($finish - $start), 3);
    echo "loading in $total_time seconds.";
    $start= $finish;
    echo "</th></tr>";
    echo "</table></br>";

}

###########################################################################
#
# Gets labels for this server
#
###########################################################################
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<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, 
        $distLabelsExist, 
        $pubLabelsExist, 
        $stages, 
        $selectedState, 
        $selectedLabel, $selectedStage, $plotType) {

    // timer start
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $start = $time;

    // function begin
    $isUpdate = ($selectedMode == "update");
    global $headerColor;
    global $columnHeaderColor;
    global $getFailures;
    global $mainTable;
    global $addSearchStage;

    // 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 = ($distLabelsExist[$thisLabel] != NULL);
        $publishing = ($pubLabelsExist[$thisLabel] != NULL);

        // 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; 

        foreach ($stages as &$stage) {
          // 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];
          # echo "myTable: $myTable";

	  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;
            }
          }
	  // echo "getStateAndFaults $db, $thisLabel, $proj, $selectedState, $stage, $str, $anyFaults<br>";
	  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 "</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);

    foreach ($stages as &$stage) {

        $reverting = getRevertStatus($db, $proj, $stage);
        if(!$reverting) {$label =  "";}
        if($reverting) {$label = "reverting";}
        unset($reverting);
        createFormattedTableCell(0, null, $label, 0, "$columnHeaderColor");
    }

    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";

    // timer finished
    echo "<tr><th colspan=11>";
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $finish = $time;
    $total_time = round(($finish - $start), 3);
    echo "loading in $total_time seconds.";
    $start= $finish;
    echo "</th></tr>";
    echo "</table><br>";
}


###########################################################################
#
# 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, $proj, $stage, $reverting) {

    $sql = "UPDATE science_reverts SET reverting = $reverting WHERE stage LIKE '$stage' AND telescope LIKE '$proj'";
    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, $proj) {

    $sql = "SELECT status FROM science_nightlyscience WHERE telescope LIKE '$proj'";
    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, $proj, $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, $proj, $stage, $currentRevertMode);
}

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

    $anyFaults = false;

    $sql = "SELECT reverting FROM science_reverts WHERE stage LIKE '$stage' AND telescope LIKE '$proj'";
    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, $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<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, $proj) {

    // timer start
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $start = $time;

    // function begin
    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, $proj, "stdscience");
    createServerDateRow($db, $proj, "registration");

    // timer finished
    echo "<tr><th colspan=11>";
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $finish = $time;
    $total_time = round(($finish - $start), 3);
    echo "loading in $total_time seconds.";
    $start= $finish;
    echo "</th></tr>";
    echo "</table><br>";

}


###########################################################################
#
# Checks the status of all the pantasks servers 
#
###########################################################################
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<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) {

    // timer start
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $start = $time;

    // function begin
    // 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,$proj, $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";
    }

    // timer finished
    echo "<tr><th colspan=5>";
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $finish = $time;
    $total_time = round(($finish - $start), 3);
    echo "loading in $total_time seconds.";
    $start= $finish;
    echo "</th></tr>";
    echo "</table><br>\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,$proj, $server, &$alive, &$running) {

#    $sql = "SELECT alive, running FROM science_servers WHERE server LIKE '$server' AND telescope LIKE '$proj' ORDER BY timestamp DESC LIMIT 1";
    $sql = "SELECT alive, running FROM live_servers WHERE server LIKE '$server' AND telescope LIKE '$proj' ORDER BY timestamp DESC LIMIT 1";
    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;

    }

    # note: mysql user must have either SUPER or REPLICATION CLIENT privileges on the database 
    $res = $dbRepl->query('SHOW SLAVE STATUS');
    if (dberror($res)) {
        echo "<tr><td>$replDatabaseName</td><td bgcolor=\"red\">MySQL query error - invalid user? check configuration</td></tr>";
        //die("MySQL DB connection error: Check the configuration for $replDatabaseName");
        //die("");
        return -1;
    }

    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];
	if ($sec_behind===NULL) {
	    $sec_behind = 99999999;
	    $errorStatusInMySql = 999999999;
	}   
        $replStatus = ($errorStatusInMySql==0?"OK ($sec_behind)":"<font style=\"BACKGROUND-COLOR: yellow\"  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();
}

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

    }
    $Status = ($errorStatusInMySql==0?"OK":"<font style=\"BACKGROUND-COLOR: yellow\"  color=\"red\">PROBLEM</font>");
    echo "<tr><td>$DatabaseName</td><td>$Status</td></tr>";

    $db->disconnect();
}

###########################################################################
#
# Shows the status of a machine using ping
#
###########################################################################
function showPingStatus($Host, $DatabaseName) {
    // timer start
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $start = $time;

    // function begin
    exec("ping -c 1 $Host", $res, $rval);
    if ($rval == 0){
      $PingStatus= "UP";
    }else{
      $PingStatus = "<font style=\"BACKGROUND-COLOR: yellow\"  color=\"red\">DOWN</font>";
    }
    echo "<tr><td>$DatabaseName</td>";

    echo "</td><td>$PingStatus</td>";
    // timer finished
    echo "<td>";
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $finish = $time;
    $total_time = round(($finish - $start), 3);
    echo "$total_time seconds";
    $start= $finish;
    echo "</td></tr>";
}


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

    // timer start
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $start = $time;

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

    $qry = $projectdb->query($sql);
    if (dberror($qry)) {
        echo "<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("Data status > $date", 4);
    echo "<tr>";
    createTableColumnHeader("Exposure type");
    createTableColumnHeader("At summit");
    createTableColumnHeader("Registered at MHPCC");
    createTableColumnHeader("Summit (Drop/Null)");

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

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

        $sql = "SELECT COUNT(*) FROM summitExp LEFT JOIN pzDownloadExp USING (summit_id) 
                WHERE dateobs > '$date' AND exp_type = '$expType[0]'";
             //   AND state != 'drop' AND state is not null AND fault != 1042";
        $qry2 = $projectdb->query($sql);
        $qry2->fetchInto($summit);

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

        $sql = "SELECT COUNT(*) FROM summitExp LEFT JOIN pzDownloadExp USING (summit_id) 
                WHERE dateobs > '$date' AND exp_type = '$expType[0]' AND (state = 'drop' OR state is null)";
        $qry2 = $projectdb->query($sql);
        $qry2->fetchInto($dropped);


        #Query with 30 minutes delay to check during the night if any exposures are missing.
        $datehour = gmdate("Y-m-d H:i:s");
        $sql = "SELECT COUNT(*) FROM summitExp LEFT JOIN pzDownloadExp USING (summit_id) 
                WHERE dateobs > '$date' AND dateobs <= date_sub('$datehour', interval 30 minute) 
                AND exp_type = '$expType[0]' AND (state != 'drop' OR state is null) and fault != 1042";

        $qry2 = $projectdb->query($sql);
        $qry2->fetchInto($summitdelay);

        $sql = "SELECT COUNT(*) FROM summitExp JOIN rawExp ON summitExp.exp_name = rawExp.exp_name 
                WHERE summitExp.dateobs > '$date' AND summitExp.dateobs <= date_sub('$datehour', interval 30 minute) 
                AND rawExp.dateobs > '$date' AND summitExp.exp_type = '$expType[0]'";
        $qry2 = $projectdb->query($sql);
        $qry2->fetchInto($mhpccdelay);

        echo "<tr>";

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

        $delayDLdiff = $summitdelay[0] - $mhpccdelay[0];
        if ($delayDLdiff == 0) $errordelay = 0;
        else $errordelay = 1;

        echo "<td>$expType[0]</td>";
        echo "<td>$summit[0]</td>";
        if ($delayDLdiff==0) {
           createFormattedTableCell("", "", $mhpcc[0], $error, 0);
        } else {
           $DLStatus = "<font style=\"BACKGROUND-COLOR: yellow\"  color=\"red\">$mhpcc[0] ($delayDLdiff)</font>";
           echo "<td>$DLStatus</td>";
        }

        echo "<td>$dropped[0]</td>";

        echo "</tr>";
    }

    // timer finished
    echo "<tr><th colspan=4>";
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $finish = $time;
    $total_time = round(($finish - $start), 3);
    echo "loading in $total_time seconds.";
    $start= $finish;
    echo "</th></tr>";
    echo "</table><br>";
}


function createSummitDataTable2($projectdb) {

    // timer start
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $start = $time;

    // function begin
    $date = date("Y-m-d");
    // set up the table
    global $table;
    global $columnHeaderColor;
    echo $table;
    echo "<tr>";
    createTableTitle("Data status > $date", 4);
    echo "<tr>";
    createTableColumnHeader("Exp_type at summit");
    createTableColumnHeader("Status at summit");
    createTableColumnHeader("Status at mhpcc");
    createTableColumnHeader("Number");
    $sql = "SELECT summitExp.exp_type,pzDownloadExp.state,rawExp.state,COUNT(*) FROM summitExp 
            LEFT JOIN pzDownloadExp USING (summit_id) LEFT JOIN rawExp on summitExp.exp_name = rawExp.exp_name 
            WHERE summitExp.dateobs > '$date' group by summitExp.exp_type,pzDownloadExp.state,rawExp.state";
    $qry = $projectdb->query($sql);
    while ($qry->fetchInto($tmp)) {
        if ($tmp[1] == NULL) { 
          echo  "<tr><td>$tmp[0]<td> null <td> null <td bgcolor=\"yellow\"> <font color=\"red\">$tmp[3]";
        }
        else {
          if ($tmp[1] == 'run') { 
            echo  "<tr><td>$tmp[0]<td> loading <td> null <td bgcolor=$columnHeaderColor> <font color=\"red\">$tmp[3]"; 
          }
          else {
            if ($tmp[2] == NULL) { 
              echo  "<tr><td>$tmp[0] <td> $tmp[1] <td> loading <td bgcolor=$columnHeaderColor> <font color=\"blue\">$tmp[3]";
            }
            else {
              echo  "<tr><td>$tmp[0]<td> $tmp[1] <td> downloaded <td> $tmp[3]";
            }
          }
        }
    }

    // timer finished
    echo "<tr><th colspan=4>";
    $time = microtime();
    $time = explode(' ', $time);
    $time = $time[1] + $time[0];
    $finish = $time;
    $total_time = round(($finish - $start), 3);
    echo "loading in $total_time seconds.";
    $start= $finish;
    echo "</th></tr>";
    echo "</table><br>";
}
?>

