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

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

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

$today = date("Y-m-d");

echo "</p>";
echo "<p  align=\"center\"> Current status of all exposures taken $today UTC </p>"; 

createTableForThisSurvey($gpc1db, $today, "");

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

    $class = "list";
    echo "<table class=$class >\n";
    echo "<tr><td></td>\n";
    write_header_cell($class, "");
    write_header_cell($class, "");
    write_header_cell($class, "");
    write_header_cell($class, "");
    write_header_cell($class, "Raw");
    write_header_cell($class, "Chip");
    write_header_cell($class, "Camera");
    write_header_cell($class, "Warp");
    write_header_cell($class, "Stack");
    write_header_cell($class, "Warp-Stack Diff");
    write_header_cell($class, "Warp-Warp1 Diff");
    write_header_cell($class, "Warp-Warp2 Diff");
    write_header_cell($class, "Stack-Stack Diff");
    echo "<tr><td></td>\n";
    write_header_cell($class, "Exp ID");
    write_header_cell($class, "Exp Name");
    write_header_cell($class, "Filter");
    write_header_cell($class, "Comment");
    write_header_cell($class, "state");
    write_header_cell($class, "state/dist?");
    write_header_cell($class, "state/dist?");
    write_header_cell($class, "state/dist?");
    write_header_cell($class, "total/dist");
    write_header_cell($class, "total/dist");
    write_header_cell($class, "total/dist");
    write_header_cell($class, "total/dist");
    write_header_cell($class, "total/dist");

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

        // chip
        if (!$chipDist[$exp_id]) $chipDistStr = "N";
        else $chipDistStr = $chipDist[$exp_id];

        // cam
        if (!$camDist[$exp_id]) $camDistStr = "N";
        else $camDistStr = $camDist[$exp_id];

        // warp
        if (!$warpDist[$exp_id]) $warpDistStr = "N";
        else $warpDistStr = $warpDist[$exp_id];

        // stack
        if (!$stack_hash[$exp_id] && !$stackDist[$exp_id]) $stackDistStr = "";
        else $stackDistStr = $stack_hash[$exp_id] . "/" . $stackDist[$exp_id];

        // WS diff
        if (!$wsDiffProduced[$exp_id] && !$wsDiffDist[$exp_id]) $wsDistStr = "";
        else $wsDistStr = $wsDiffProduced[$exp_id] . "/" . $wsDiffDist[$exp_id];

        // WW1 diff
        if (!$ww1DiffProduced[$exp_id] && !$ww1DiffDist[$exp_id]) $ww1DistStr = "";
        else $ww1DistStr = $ww1DiffProduced[$exp_id] . "/" . $ww1DiffDist[$exp_id];

        // WW2 dist
        if (!$ww2DiffProduced[$exp_id] && !$ww2DiffDist[$exp_id]) $ww2DistStr = "";
        else $ww2DistStr =  $ww2DiffProduced[$exp_id] . "/" . $ww2DiffDist[$exp_id];

        // SS dist
        if (!$ssDiffProduced[$exp_id] && !$ssDiffDist[$exp_id]) $ssDistStr = "";
        else $ssDistStr = $ssDiffProduced[$exp_id] . "/" . $ssDiffDist[$exp_id];

        write_table_cell($class, '%s', "", $exp_id);
        write_table_cell($class, '%s', "", $exp_name);
        write_table_cell($class, '%s', "", $filter);
        write_table_cell($class, '%s', "", $comment);
        write_table_cell($class, '%s', "", $rawExp);
        write_table_cell($class, '%s', "", "$chipState/$chipDistStr");
        write_table_cell($class, '%s', "", "$camState/$camDistStr");
        write_table_cell($class, '%s', "", "$warpState/$warpDistStr");
        write_table_cell($class, '%s', "", $stackDistStr);
        write_table_cell($class, '%s', "", $wsDistStr);
        write_table_cell($class, '%s', "", $ww1DistStr);
        write_table_cell($class, '%s', "", $ww2DistStr);
        write_table_cell($class, '%s', "", $ssDistStr);

        echo "</tr>\n";
    }

    echo "</table>\n";
}


###########################################################################
#
# 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];
    }
}



