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

$ID = checkID();

if (! $ID['proj']) { projectform ($ID); }
$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";}

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

$date = $_POST['date'];
$survey = $_POST['survey'];

// if no date chosen, use today
if ($date == "") $date = date("Y-m-d");
if ($survey == "") $survey = "";

menu($myMenu, 'Exposure summary for '.$date , 'ipp.css', $ID['link'], $ID['proj']);

// set up the form
echo "<form action=\"czartool_exposures.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 method=post action=czartool_exposures.php?pass=$pass&proj=$proj&date=$date&survey=$survey>";
echo "<p  align=\"center\"> Current status of all exposures taken on (UTC) <input type=text name=date value='$date'>";
echo " for survey <input type=text name=survey value='$survey'> (leave blank for all surveys)";
echo "<input type=submit value=Submit>";
echo "</p>";

echo "</form>\n";

createTableForThisSurvey($gpc1db, $date, $survey);

menu_end();

###########################################################################
#
# Generates a table for the provided survey 
#
###########################################################################
function createTableForThisSurvey($db, $date, $surveyStr) {

    $conditions = "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) 
        LEFT JOIN warpImfile USING (warp_id) 
        WHERE dateobs LIKE '$date%'
        AND comment NOT LIKE '%calcite%'
        AND comment NOT LIKE '%focus%'
        AND comment NOT LIKE '%pointing%'
        AND comment NOT LIKE '%stare%'
        AND comment LIKE '%$surveyStr%'
        AND exp_type = 'OBJECT'";

    $sql = "SELECT COUNT(DISTINCT exp_id) " . $conditions;

    $qry = $db->query($sql);
    $qry->fetchInto($count);

    if ($surveyStr == "") $surveyText = "all surveys";
    else $surveyText = "$surveyStr survey";

    echo "<p  align=\"center\"> Found " . $count[0] . " exposures for $surveyText </p>";

    $chipDist = array(); getChipDist($db, $date, $surveyStr, &$chipDist);
    $camDist = array(); getCamDist($db, $date, $surveyStr, &$camDist);
    $warpDist = array(); getWarpDist($db, $date, $surveyStr, &$warpDist);
    $stackDist = array(); getStackDist($db, $date, $surveyStr, &$stackDist);
    $stack_hash = array(); getStackProduced($db, $date, $surveyStr, &$stack_hash);

    $wsDiffProduced = array();
    $ww1DiffProduced = array();
    getWSDiffDist($db, $date, $surveyStr, &$wsDiffProduced, &$ww1DiffProduced);

    $wsDiffDist = array();
    $ww1DiffDist = array();
    getWSAndWW1DiffDist($db, $date, $surveyStr, &$wsDiffDist, &$ww1DiffDist, $wsDiffProduced);

    $ww2DiffDist = array(); getWW2DiffDist($db, $date, $surveyStr, &$ww2DiffDist);
    $ssDiffDist = array(); getSSDiffDist($db, $date, $surveyStr, &$ssDiffDist);

    $ww2DiffProduced = array(); getWW2DiffProduced($db, $date, $surveyStr, &$ww2DiffProduced);
    $ssDiffProduced = array(); getSSDiffProduced($db, $date, $surveyStr, &$ssDiffProduced); 

    // create table
    echo "<table border=\"1\" cellspacing=\"0\" cellpadding=\"4\" style=\"font-size:80%\">\n";

    // table headers
    echo "<tr><td></td>\n";
    echo "<th></th>";
    echo "<th></th>";
    echo "<th></th>";
    echo "<th></th>";
    echo "<th>Raw</th>";
    echo "<th>Chip</th>";
    echo "<th>Camera</th>";
    echo "<th>Warp</th>";
    echo "<th>Stack</th>";
    echo "<th>Warp-Stack Diff</th>";
    echo "<th>Warp-Warp1 Diff</th>";
    echo "<th>Warp-Warp2 Diff</th>";
    echo "<th>Stack-Stack Diff</th>";
    echo "<tr><td></td>\n";
    echo "<th>Exp ID</th>";
    echo "<th>Exp Name</th>";
    echo "<th>Filter</th>";
    echo "<th>Comment</th>";
    echo "<th>state</th>";
    echo "<th>state/dist?</th>";
    echo "<th>state/dist?</th>";
    echo "<th>state/dist?</th>";
    echo "<th>total/dist</th>";
    echo "<th>total/dist</th>";
    echo "<th>total/dist</th>";
    echo "<th>total/dist</th>";
    echo "<th>total/dist</th>";

    $sql = 
        "SELECT
        exp_id,
        exp_name, 
        filter, 
        comment, 
        rawExp.state, 
        chipRun.state, 
        camRun.state, 
        warpRun.state, 
        COUNT(skycell_id) 
            $conditions 
            GROUP BY exp_name;"; 
            //echo "$sql<br>";

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

    // loop through results in order to make html table
    while ($qry->fetchInto($row)) {

        $exp_id = $row[0];
        $exp_name = $row[1];
        $filter = preg_replace("/([a-z]{1})\.[0-9]+/", "$1", $row[2]);
        $comment = $row[3];
        $rawExp = $row[4];
        $chipState = $row[5];
        $camState = $row[6];
        $warpState = $row[7];

        echo "<tr><td></td>\n";

        echo "<TD>$exp_id</td>";
        echo "<TD>$exp_name</td>";
        echo "<TD>$filter</td>";
        echo "<TD>$comment</td>";
        echo "<TD>$rawExp</td>";

        // chip
        if (!$chipDist[$exp_id]) $chipDistStr = "N";
        else $chipDistStr = $chipDist[$exp_id];
        echo "<TD>$chipState/$chipDistStr</td>";

        // cam
        if (!$camDist[$exp_id]) $camDistStr = "N";
        else $camDistStr = $camDist[$exp_id];
        echo "<TD>$camState/$camDistStr</td>";

        // warp
        if (!$warpDist[$exp_id]) $warpDistStr = "N";
        else $warpDistStr = $warpDist[$exp_id];
        echo "<TD>$warpState/$warpDistStr</td>";

        // stack
        if (!$stack_hash[$exp_id] && !$stackDist[$exp_id]) $stackDistStr = "&nbsp;";
        else $stackDistStr = $stack_hash[$exp_id] . "/" . $stackDist[$exp_id];
        $color = getColour($stack_hash[$exp_id], $stackDist[$exp_id]);
        echo "<TD bgcolor=\"$color\" >$stackDistStr</td>";

        // WS diff
        if (!$wsDiffProduced[$exp_id] && !$wsDiffDist[$exp_id]) $wsDistStr = "&nbsp;";
        else $wsDistStr = $wsDiffProduced[$exp_id] . "/" . $wsDiffDist[$exp_id];
        $color = getColour($wsDiffProduced[$exp_id], $wsDiffDist[$exp_id]);
        echo "<TD bgcolor=\"$color\" >$wsDistStr</td>";

        // WW1 diff
        if (!$ww1DiffProduced[$exp_id] && !$ww1DiffDist[$exp_id]) $ww1DistStr = "&nbsp;";
        else $ww1DistStr = $ww1DiffProduced[$exp_id] . "/" . $ww1DiffDist[$exp_id];
        $color = getColour($ww1DiffProduced[$exp_id], $ww1DiffDist[$exp_id]);
        echo "<TD bgcolor=\"$color\" >$ww1DistStr</td>";

        // WW2 dist
        if (!$ww2DiffProduced[$exp_id] && !$ww2DiffDist[$exp_id]) $ww2DistStr = "&nbsp;";
        else $ww2DistStr =  $ww2DiffProduced[$exp_id] . "/" . $ww2DiffDist[$exp_id];
        $color = getColour($ww2DiffProduced[$exp_id], $ww2DiffDist[$exp_id]);
        echo "<TD bgcolor=\"$color\" >$ww2DistStr</td>";

        // SS dist
        if (!$ssDiffProduced[$exp_id] && !$ssDiffDist[$exp_id]) $ssDistStr = "&nbsp;";
        else $ssDistStr = $ssDiffProduced[$exp_id] . "/" . $ssDiffDist[$exp_id];
        $color = getColour($ssDiffProduced[$exp_id], $ssDiffDist[$exp_id]);
        echo "<TD bgcolor=\"$color\" >$ssDistStr</td>";

        echo "</tr>\n";
    }

    echo "</table>\n";
}

###########################################################################
#
# Gets the right cell colour provided number created and distributed 
#
###########################################################################
function getColour($created, $distributed) {

    if ($distributed > 0 && $created > 0 && $distributed % $created != 0) return "red";
    return "";
}

###########################################################################
#
# Gets count of chips distributed 
#
###########################################################################
function getChipDist($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(stage_id) 
        FROM rawExp 
        LEFT JOIN chipRun USING (exp_id) 
        LEFT JOIN distRun ON chip_id = stage_id 
        AND stage = 'chip' 
        WHERE dateobs like '$date%' 
        AND exp_type = 'OBJECT' 
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id;
    ";

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

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) $hash[$row[0]] = "Y";
        else $hash[$row[0]] = "N";
    }
}

###########################################################################
#
# Gets count of cams distributed
#
###########################################################################
function getCamDist($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(stage_id) 
        FROM rawExp 
        LEFT JOIN chipRun USING (exp_id) 
        LEFT JOIN camRun USING (chip_id) 
        LEFT JOIN distRun ON cam_id = stage_id 
        WHERE dateobs like '$date%' 
        AND stage = 'camera' 
        AND exp_type = 'OBJECT' 
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id;
    ";

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

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) $hash[$row[0]] = "Y";
        else $hash[$row[0]] = "N";
    }
}

###########################################################################
#
# Gets count of warps distributed
#
###########################################################################
function getWarpDist($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(stage_id) 
        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)
        LEFT JOIN distRun ON warp_id = stage_id 
        WHERE dateobs like '$date%' 
        AND stage = 'warp' 
        AND exp_type = 'OBJECT' 
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id;
    ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) $hash[$row[0]] = "Y";
        else $hash[$row[0]] = "N";
    }
}

###########################################################################
#
# Gets count of stacks distributed
#
###########################################################################
function getStackDist($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(stage_id) 
        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) 
        LEFT JOIN stackInputSkyfile USING (warp_id) 
        LEFT JOIN distRun ON stack_id = stage_id 
        WHERE dateobs like '$date%' 
        AND stage = 'stack' 
        AND exp_type = 'OBJECT' 
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id;
    ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) $hash[$row[0]] = $row[2];
    }
}

###########################################################################
#
# Gets count of stacks produced
#
###########################################################################
function getStackProduced($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(stack_id) 
        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) 
        LEFT JOIN stackInputSkyfile USING (warp_id) 
        LEFT JOIN stackRun USING (stack_id) 
        WHERE dateobs like '$date%' 
        AND exp_type = 'OBJECT' 
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id;
    ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) $hash[$row[0]] = $row[2];
    }
}

###########################################################################
#
# Gets count of WS and WW1 diffs distributed
#
###########################################################################
function getWSAndWW1DiffDist($db, $date, $surveyStr, $hash1, $hash2, $wsDiffProduced) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(diff_id), stack1 
        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) 
        LEFT JOIN diffInputSkyfile ON warp_id = warp1 
        LEFT JOIN distRun ON stage_id = diff_id AND stage = 'diff' 
        WHERE dateobs like '$date%' 
        AND exp_type = 'OBJECT' GROUP BY exp_id;
    ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) {

            if ($wsDiffProduced[$row[0]] != null)
                $hash1[$row[0]] = $row[2];
            else 
                $hash2[$row[0]] = $row[2];
        }
    }
}

###########################################################################
#
# Gets count of WW2 diffs distributed
#
###########################################################################
function getWW2DiffDist($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(diff_id) 
        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) 
        LEFT JOIN diffInputSkyfile ON warp_id = warp2 
        LEFT JOIN distRun ON stage_id = diff_id AND stage = 'diff' 
        WHERE dateobs like '$date%' 
        AND comment LIKE '%$surveyStr%'
        AND exp_type = 'OBJECT' 
        GROUP BY exp_id
        ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) $hash[$row[0]] = $row[2];
    }
}

###########################################################################
#
# Gets count of SS diffs distributed
#
###########################################################################
function getSSDiffDist($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(diff_id) 
        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) 
        LEFT JOIN stackInputSkyfile USING (warp_id) 
        LEFT JOIN diffInputSkyfile ON stack_id = stack1 
        LEFT JOIN distRun ON stage_id = diff_id AND stage = 'diff' 
        WHERE dateobs like '$date%' 
        AND exp_type = 'OBJECT'
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id;
    ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) $hash[$row[0]] = $row[2];
    }
}

###########################################################################
#
# Gets count of WW2 diffs produced
#
###########################################################################
function getWW2DiffProduced($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT rawExp.exp_id, COUNT(warp2), diff_mode 
        FROM rawExp, chipRun, camRun, fakeRun, warpRun, diffInputSkyfile, diffRun  
        WHERE rawExp.exp_id = chipRun.exp_id 
        AND chipRun.chip_id = camRun.chip_id 
        AND camRun.cam_id = fakeRun.cam_id 
        AND fakeRun.fake_id = warpRun.fake_id 
        AND diffInputSkyfile.warp2 = warpRun.warp_id 
        AND diffInputSkyfile.diff_id = diffRun.diff_id 
        AND dateobs like '$date%' 
        AND exp_type = 'OBJECT' 
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id, diff_mode
        ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}

    while ($qry->fetchInto($row)) {

        $hash[$row[0]] = $row[1];
    }
}

###########################################################################
#
# Gets count of SS diffs produced
#
###########################################################################
function getSSDiffProduced($db, $date, $surveyStr, $hash) {

    $sql = "
        SELECT exp_id, rawExp.state, COUNT(stack1), diff_mode 
        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) 
        LEFT JOIN stackInputSkyfile USING (warp_id) 
        LEFT JOIN diffInputSkyfile ON stack_id = stack1 
        LEFT JOIN diffRun USING (diff_id) 
        WHERE dateobs like '$date%' 
        AND exp_type = 'OBJECT' 
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id, diff_mode;
    ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}

    while ($qry->fetchInto($row)) {

        if ($row[2] > 0) $hash[$row[0]] = $row[2];
    }
}

###########################################################################
#
# Gets count of WS diffs distributed
#
###########################################################################
function getWSDiffDist($db, $date, $surveyStr, $hash1, $hash2) {

    $sql = "
        SELECT rawExp.exp_id, COUNT(warp1), diff_mode 
        FROM rawExp, chipRun, camRun, fakeRun, warpRun, diffInputSkyfile, diffRun  
        WHERE rawExp.exp_id = chipRun.exp_id 
        AND chipRun.chip_id = camRun.chip_id 
        AND camRun.cam_id = fakeRun.cam_id 
        AND fakeRun.fake_id = warpRun.fake_id 
        AND diffInputSkyfile.warp1 = warpRun.warp_id 
        AND diffInputSkyfile.diff_id = diffRun.diff_id 
        AND dateobs like '$date%' 
        AND exp_type = 'OBJECT' 
        AND comment LIKE '%$surveyStr%'
        GROUP BY exp_id, diff_mode
        ";
    $qry = $db->query($sql);
    if (dberror($qry)) {echo "<b>error with $sql </b><br>\n";}
    $hash = array();
    while ($qry->fetchInto($row)) {

        if ($row[2] == 2) $hash1[$row[0]] = $row[1];
        else  $hash2[$row[0]] = $row[1];
    }
}



