<?php
{
    function CreateQuery($_GET, $alias, $checkwhere, $checkgroup, $checkstatusgroup, $needs)
    {
        $fromtable="Sequences";

        $groups = 0;
        foreach ($checkgroup as $element)
            if ($element==-1)
                $groups++;

        $statusgroups = 0;
        foreach ($checkstatusgroup as $element)
            if ($element==-1)
                $statusgroups++;

        $query0 = "SELECT ";

        if ($groups>0)
        {
            foreach ($checkgroup as $key => $element)
                if ($element==-1)
                    $query0 .=  $key . " as '" . $alias[$key] . "' " . ", ";
            //--------------------------------------------------
            $query0 .= "SUM(fRunTime)/3600 as '" . $alias["SUM(fRunTime)/3600"] . "', ";
            //--------------------------------------------------
            $query0 .= " SUM(fNumEvents) as '" . $alias["SUM(fNumEvents)"] . "', ";
            $query0 .= " Min(fZenithDistanceMin) as '" . $alias["Min(fZenithDistanceMin)"] . "', ";
            $query0 .= " Max(fZenithDistanceMax) as '" . $alias["Max(fZenithDistanceMax)"] . "', ";
            $query0 .= " COUNT(*) as '# Sequ' ";
        }
        else
        {
            if ($statusgroups>0)
            {
                foreach ($checkstatusgroup as $key => $element)
                    if ($element==-1)
                        $query0 .= " (if(IsNull(" . $key . "), if(isnull(fStartTime), 'not done', if(isnull(fFailedTime),if(isnull(" . $needs[$key] . "),'not done',if(date_sub(Now(),interval 12 hour) < fStartTime,'running','crashed')),if(isnull(" . $needs[$key] . "),'not done','failed'))) ,if(" . $key . "='1970-01-01 00:00:00','dont do','done'))) as '" . $alias[$key] . "', ";
//                        $query0 .= " (if(IsNull(" . $key . "), 'not done' ,if(" . $key . "='1970-01-01 00:00:00','dont do','done'))) as '" . $alias[$key] . "', ";

                $query0 .= " COUNT(*) as '# Sequ'";

            }
            else
            {
                $query0 .= " " . $fromtable . ".fSequenceFirst as '" . $alias["fSequenceFirst"] . "' ";
                if (!empty($_GET["fLinks"]))
                {
                    $query0 .= ", CONCAT('<A&ws;HREF=\"runinfo-aio.php?',  'fRunStart=On', '&amp;fZenithDistance=On', '&amp;fRunMin=', Sequences.fSequenceFirst, '&amp;fMeanTriggerRate=On', '&amp;fTest=On', '&amp;fRunTypeName=On', '&amp;fRunMax=', fSequenceLast, '&amp;fNumEvents=On', '&amp;fSourceName=On&amp;', 'fExcludedFDAKEY=1', '&amp;fSequenceFirst=On', '&amp;fRawFileAvail=On', '&amp;fCCFileAvail=On', '&amp;fCaCoFileAvail=On', '&amp;fNumResults=500\">r</A>' ";
                    //                        $query0 .= " as " . $alias["fSequenceFirst"];
                    $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/callisto/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/', LPAD(CONCAT(Sequences.fSequenceFirst, '/'), 9,'0'), 'callisto', LPAD(CONCAT(Sequences.fSequenceFirst , '.html'), 13,'0') , '\">cl</A>'";
                    $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/callisto/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/', LPAD(CONCAT(Sequences.fSequenceFirst, '/'), 9,'0'), '\">c</A>'";
                    $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/star/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/', LPAD(CONCAT(Sequences.fSequenceFirst, '/'), 9,'0'), 'star', LPAD(CONCAT(Sequences.fSequenceFirst , '.html'), 13,'0') , '\">sl</A>'";
                    $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/star/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/', LPAD(CONCAT(Sequences.fSequenceFirst, '/'), 9,'0'), '\">s</A>'";
                    $query0 .= ", '&nbsp;<A&ws;HREF=\"http://www.astro.uni-wuerzburg.de/datacenter/sequences/' , LEFT(LPAD(CONCAT(Sequences.fSequenceFirst, '.'), 9,'0'),4), '/sequence', LPAD(CONCAT(Sequences.fSequenceFirst , '.txt'), 12,'0') , '\">', 'f </A>' ";
                    $query0 .= ", '&nbsp;<A&ws;HREF=\"sequence.php?fSequenceNo=', Sequences.fSequenceFirst , '&amp;fAll=On\">i</A>') ";
                    $query0 .= " as 'Links'";
                }

                foreach ($_GET as $key => $element)
                    if ($_GET[$key]=="On")
                        if ($key!="fLinks" && $key!="fOff" && $key!="fOnlyOff")
                            if (empty($checkwhere[$key]) || $checkwhere[$key]==0)
                                $query0 .= ", " . $key . " as '" . $alias[$key] . "' ";
            }
        }

        $query0 .= " FROM " . $fromtable;

        $query0 .= " LEFT JOIN SequenceProcessStatus USING(fSequenceFirst,fTelescopeNumber) ";
        $query0 .= " LEFT JOIN Calibration USING(fSequenceFirst,fTelescopeNumber) ";
        $query0 .= " LEFT JOIN Star USING(fSequenceFirst,fTelescopeNumber) ";

        foreach ($_GET as $key => $element)
            if (($_GET[$key]=="On" || $groups>0))// && !empty(GetJoin($fromtable,$key)))
                $query0 .= GetJoin($fromtable,$key);

        if ($_GET["fTest"]!="On")
        {
            if ($_GET["fSourceName"]!="On")
                $query0 .= " LEFT JOIN Source USING(fSourceKEY) ";
            $query0 .= " WHERE fTest='no'";
        }

        if ($_GET["fOff"]=="Off")
        {
            if (strpos($query0, " WHERE ")==FALSE)
                $query0 .= " WHERE ";
            else
                $query0 .= " AND ";

            $query0 .= " NOT (fSourceName like '%Off%')";
        }

        if ($_GET["fOnlyOff"]=="On")
        {
            if (strpos($query0, " WHERE ")==FALSE)
                $query0 .= " WHERE ";
            else
                $query0 .= " AND ";

            $query0 .= " fSourceName like '%Off%'";
        }

        foreach ($checkwhere as $key => $element)
        {
            if (empty($element) || $element<=0)
                continue;

            if (strpos($query0, " WHERE ")==FALSE)
                $query0 .= " WHERE ";
            else
                if ($element!=-1)
                    if (strrpos($query0, " AND ")!=strlen($query0)-5)
                        $query0 .= " AND ";

            if ($element!=-1)
                $query0 .= GetCheck($fromtable, $key) . "=" . $element;
        }

        if (strpos($query0, " WHERE ")==FALSE)
            $query0 .= " WHERE ";
        else
            $query0 .= " AND ";

        $query0 .= StatusQuery("fSequenceFileWritten", $needs);
        $query0 .= StatusQuery("fAllFilesAvail", $needs);
        $query0 .= StatusQuery("fCallisto", $needs);
        $query0 .= StatusQuery("fFillCallisto", $needs);
        $query0 .= StatusQuery("fStar", $needs);
        $query0 .= StatusQuery("fFillStar", $needs);

        if (!empty($_GET["fRunMin"]) && !empty($_GET["fRunMax"]))
            $query0 .= "Sequences.fSequenceFirst BETWEEN " . $_GET["fRunMin"] . " AND " . $_GET["fRunMax"] . " ";
        else
            $query0 = substr($query0, 0, -4);

        if ((!empty($_GET["fZDMin"]) || $_GET["fZDMin"]==0) && !empty($_GET["fZDMax"]))
            $query0 .= "AND (fZenithDistanceMin >= " . $_GET["fZDMin"] . " AND fZenithDistanceMax <= " . $_GET["fZDMax"] . ") ";

        if (!empty($_GET["fSourceN"]))
            $query0 .= " AND fSourceName REGEXP \"^" . $_GET["fSourceN"] . "\" ";

        if (!empty($_GET["fStartDate"]))
        {
            if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
                $query0 .= " AND ";

            $startdate=substr($_GET["fStartDate"], 0, 10);
            if ($startdate=="0000-00-00")
                $query0 .=" fRunStart >= '" . $startdate . " 00:00:00' ";
            else
                $query0 .= " fRunStart >= ADDDATE('" . $startdate . " 13:00:00', INTERVAL -1 DAY) ";
        }

        if (!empty($_GET["fStopDate"]))
        {
            if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
                $query0 .= " AND ";

            $stopdate=substr($_GET["fStopDate"], 0, 10);
            $query0 .= " fRunStart < '" . $stopdate . " 13:00:00' ";
        }

        if (!empty($_GET["fStarStart"]))
        {
            if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
                $query0 .= " AND ";

            $starstart=substr($_GET["fStarStart"], 0, 10);
            $query0 .=" fStar >= '" . $starstart . " 00:00:00' ";
        }

        if (!empty($_GET["fStarStop"]))
        {
            if (strpos(strrev($query0), " DNA ")!=0 || !strpos(strrev($query0), " DNA "))
                $query0 .= " AND ";

            $starstop=substr($_GET["fStarStop"], 0, 10);
            $query0 .= " fStar < '" . $starstop . " 23:59:59' ";
        }

        if ($groups>0)
        {
            $query0 .= " GROUP BY ";
            $num = $groups;
            foreach ($checkgroup as $key => $element)
                if ($element==-1)
                {
                    $query0 .= GetCheck($fromtable,$key);
                    if ($num-->1)
                        $query0 .= ", ";
                }
        }

        if ($statusgroups>0)
        {
            $query0 .= " GROUP BY ";
            $num = $statusgroups;
            foreach ($checkstatusgroup as $key => $element)
                if ($element==-1)
                {
                    $query0 .= $alias[$key];
                    if ($num-->1)
                        $query0 .= ", ";
                }
        }

        if (!empty($_GET["fSortBy"]))
        {
            $val=substr($_GET["fSortBy"], 0, -1);
            $query0 .= " ORDER BY " . GetTable($fromtable,$val) . " ";
            if (substr($_GET["fSortBy"], -1)=="-")
                $query0 .= "DESC";
        }

        if (empty($_GET["fSortBy"]) && $groups==0 && $statusgroups==0)
            $query0 .= "ORDER BY Sequences.fSequenceFirst DESC ";

        if (empty($_GET["fNumStart"]))
            $_GET["fNumStart"]=0;

        if (empty($_GET["fSendTxt"]))
            $query0 .= " LIMIT " . $_GET["fNumStart"] . ", " . $_GET["fNumResults"];

        return $query0;
    }

    function InitGet($_GET)
    {
        // Find out whether it is the first call to the php script
        $first = empty($_GET["fRunMin"]) && empty($_GET["fRunMax"]);

        if (empty($_GET["fNumResults"]))
            $_GET["fNumResults"]="100";

        if (empty($_GET["fRunStart"]))
            $_GET["fRunStart"]=$first?"On":"";

        if (empty($_GET["fRunTime/60"]))
            $_GET["fRunTime/60"]=$first?"On":"";

        if (empty($_GET["fZenithDistanceMin"]))
            $_GET["fZenithDistanceMin"]=$first?"On":"";

        if (empty($_GET["fZenithDistanceMax"]))
            $_GET["fZenithDistanceMax"]=$first?"On":"";

        if (empty($_GET["fSourceName"]))
            $_GET["fSourceName"]=$first?"On":"";

        if (empty($_GET["fObservationModeName"]))
            $_GET["fObservationModeName"]=$first?"On":"";

        if (empty($_GET["fUnsuitableInner"]))
            $_GET["fUnsuitableInner"]=$first?"On":"";

        if (empty($_GET["fUnreliableInner"]))
            $_GET["fUnreliableInner"]=$first?"On":"";

        if (empty($_GET["fIsolatedInner"]))
            $_GET["fIsolatedInner"]=$first?"On":"";

        if (empty($_GET["fIsolatedMaxCluster"]))
            $_GET["fIsolatedMaxCluster"]=$first?"On":"";

//        if (empty($_GET["fArrTimeMeanInner"]))
//            $_GET["fArrTimeMeanInner"]=$first?"On":"";

        if (empty($_GET["fArrTimeRmsInner"]))
            $_GET["fArrTimeRmsInner"]=$first?"On":"";

        if (empty($_GET["fMeanPedRmsInner"]))
            $_GET["fMeanPedRmsInner"]=$first?"On":"";

        if (empty($_GET["fPulsePosMean"]))
            $_GET["fPulsePosMean"]=$first?"On":"";

        if (empty($_GET["fConvFactorInner"]))
            $_GET["fConvFactorInner"]=$first?"On":"";

        if (empty($_GET["fInhomogeneity"]))
            $_GET["fInhomogeneity"]=$first?"On":"";

        if (empty($_GET["fPSF"]))
            $_GET["fPSF"]=$first?"On":"";

        if (empty($_GET["fMuonNumber"]))
            $_GET["fMuonNumber"]=$first?"On":"";

        if (empty($_GET["fEffOnTime/fRunTime"]))
            $_GET["fEffOnTime/fRunTime"]=$first?"On":"";

        if (empty($_GET["fMuonRate"]))
            $_GET["fMuonRate"]=$first?"On":"";

        if (empty($_GET["fDataRate"]))
            $_GET["fDataRate"]=$first?"On":"";

//        if (empty($_GET["fMaxHumidity"]))
//            $_GET["fMaxHumidity"]=$first?"On":"";

        if (empty($_GET["fBrightnessMed"]))
            $_GET["fBrightnessMed"]=$first?"On":"";

        if (empty($_GET["fNumStarsMed"]))
            $_GET["fNumStarsMed"]=$first?"On":"";

        if (empty($_GET["fNumStarsCorMed"]))
            $_GET["fNumStarsCorMed"]=$first?"On":"";

        if (empty($_GET["fOff"]))
            $_GET["fOff"]=$first?"On":"";

        if (empty($_GET["fLinks"]))
            $_GET["fLinks"]=$first?"On":"";

        if (empty($_GET["fOnlyOff"]))
            $_GET["fOnlyOff"]=$first?"Off":"";

        InitFindOffData($_GET, $first);
    }

    function PrintForm($_GET, $host, $user, $pw, $db, $limitsmean, $limitsmin, $limitsmax, $alias)
    {
        printf("<center>\n");
        printf("<form action='builddatasets.php' METHOD='GET'>\n");
        printf("<input id='sh' type='hidden' name='fShowHide' value='");
        if (!empty($_GET["fShowHide"]))
            printf("%s", $_GET["fShowHide"]);
        else
            printf("1000000000");
        printf("'>\n");
        printf("<img id='allbutton' src='minus.png' alt='-' onclick='showhide(\"all\")'> <b>Menu</b>&nbsp;&nbsp;&nbsp;&nbsp;\n");
        printf("&nbsp;&nbsp;<img id='infobutton'   src='plus.png' alt='+' onClick='showhide(\"info\");showhide(\"info2\")'> SequInfo   \n");
        printf("&nbsp;&nbsp;<img id='statbutton'   src='plus.png' alt='+' onClick='showhide(\"stat\");showhide(\"fail\")'>  StatusInfo \n");
        printf("&nbsp;&nbsp;<img id='calbutton'    src='plus.png' alt='+' onClick='showhide(\"cal\")'>                      CalInfo    \n");
        printf("&nbsp;&nbsp;<img id='starbutton'   src='plus.png' alt='+' onClick='showhide(\"star\")'>                     StarInfo   \n");
        printf("&nbsp;&nbsp;<img id='limitsbutton' src='plus.png' alt='+' onClick='showhide(\"limits\")'>                   Limits     \n");
        printf("&nbsp;&nbsp;<img id='rangesbutton' src='plus.png' alt='+' onClick='showhide(\"ranges\")'>                   Ranges     \n");

        printf(" <div id='all' style='display:block'>");

        PrintSequInfo2Menu($host,$user,$pw,$db);
        PrintSequInfoMenu();
        PrintSequStatMenu();
        PrintFailMenu();
        PrintCalMenu();
        PrintStarMenu();
        PrintLimitsMenu($limitsmean, $limitsmin, $limitsmax, $alias, "");

        printf(" <div id='ranges' style='display:none'>");
        PrintZdRangeMenu($host,$user,$pw,$db);
        PrintSequRangeMenu($host,$user,$pw,$db);
        printf("<p>");
        PrintSourceMenu($host,$user,$pw,$db);
        PrintNightRangeMenu($host,$user,$pw,$db, "Sequences");
        PrintStarRangeMenu($host,$user,$pw,$db);
        printf("<p>");
        printf("</div>");

        printf("</div>");
        printf(" <P>\n");

        PrintNumResPullDown();

        ini_set("mysql.trace_mode", "Off");
        ini_set("display_errors", "Off");

        PrintButtons("builddatasets.php");

        $sequon=" ";
        $sequoff=" ";
        foreach($_POST as $key => $val)
            if ($val=="On")
            {
                if (ereg("ON$", $key))
                    $sequon.=str_replace("ON", " ", $key);
                if (ereg("Off$", $key))
                    $sequoff.=str_replace("Off", " ", $key);
            }
        printf("<input name='Set' type='hidden' value='%s'>\n", $sequon);
        printf("<input name='Set2' type='hidden' value='%s'>\n", $sequoff);
        printf("<input name='name' type='hidden' value='%s'>\n", $_POST["name"]);
        printf("<input name='comment' type='hidden' value='%s'>\n", $_POST["comment"]);
        printf("<input name='obsmode' type='hidden' value='%s'>\n", $_POST["fObservationModeKEY"]);
        printf("<input name='username' type='hidden' value='%s'>\n", $_POST["fUserKEY"]);
        printf("</form>\n");
        printf("</center>\n");
        printf("</td>\n");
        printf("</tr>\n");
        printf("<tr class='Block'>\n");
        printf("<td>\n");
    }

    function PrintPage($html, $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs)
    {
        $db_id = mysql_pconnect($host, $user, $pw);
        if ($db_id==FALSE)
        {
            printf("mysql_connect returned the following error: %s\n", mysql_error());
            die("");
        }
        mysql_select_db($db);

        $query0 = CreateQuery($_GET, $alias, $checkwhere, $checkgroup, $checkstatusgroup, $needs);

        $result0 = mysql_query($query0, $db_id);

        if ($result0)
        {
            if ($html=="1" || $html=="2")
            {
                PrintMagicTable($result0, $alias, $rightalign, $limitsmean, $limitsmin, $limitsmax, "yes", $_GET);
                foreach($_POST as $key => $val)
                    if ($val=="On")
                    {
                        if (ereg("ON$", $key))
                            $sequon.=str_replace("ON", " ", $key);
                        if (ereg("Off$", $key))
                            $sequoff.=str_replace("Off", " ", $key);
                    }

                printf("<input type='submit' value='Get Dataset'><br><br>\n");
                printf("<input name='submit' type='hidden' value='post'>\n");

                $on=str_replace(" ", ",", trim($sequon));
                $off=str_replace(" ", ",", trim($sequoff));

                $values=array("Sum(fRunTime)/60"         => $runtime,
                              "Min(fRunTime)/60"         => $runtimemin,
                              "Max(fUnsuitableInner)"    => $unsuitablemax,
                              "Max(fUnreliableInner)"    => $unreliablemax,
                              "Max(fIsolatedInner)"      => $isolatedmax,
                              "Max(fIsolatedMaxCluster)" => $imcmax,
                              "Max(fArrTimeMeanInner)"   => $arrtimemax,
                              "Min(fArrTimeMeanInner)"   => $arrtimemin,
                              "Max(fArrTimeRmsInner)"    => $arrtimermsmax,
                              "Avg(fMeanPedRmsInner)"    => $pedrmsmean,
                              "Std(fMeanPedRmsInner)"    => $pedrmsrms,
                              "Min(fMeanPedRmsInner)"    => $pedrmsmin,
                              "Max(fMeanPedRmsInner)"    => $pedrmsmax,
                              "Min(fPulsePosMean)"       => $pulseposmin,
                              "Max(fPulsePosMean)"       => $pulseposmax,
                              "Max(fPulsePosRms)"        => $pulseposrmsmax,
                              "Max(fMaxHumidity)"        => $hummax,
                              "Max(fInhomogeneity)"      => $inhommax,
                              "Avg(fInhomogeneity)"      => $inhommean,
                              "Std(fInhomogeneity)"      => $inhomrms,
                              "Min(fMeanNumberIslands)"  => $numislmin,
                              "Max(fMeanNumberIslands)"  => $numislmax,
                              "Min(fDataRate)"           => $clratemin,
                              "Max(fDataRate)"           => $clratemax,
                              "Min(fMuonRate)"           => $muonratemin,
                              "Max(fMuonRate)"           => $muonratemax,
                              "Min(fMuonNumber)"         => $muonnumbermin,
                              "Avg(fPSF)"                => $psfmean,
                              "Std(fPSF)"                => $psfrms,
                              "Min(fPSF)"                => $psfmin,
                              "Max(fPSF)"                => $psfmax,
                              "Min(fEffOnTime/fRunTime)" => $relontimemin,
                              "Max(fEffOnTime/fRunTime)" => $relontimemax,
                              "Min(fBrightnessMed)"      => $brightmin,
                              "Max(fBrightnessMed)"      => $brightmax,
                              "Min(fNumStarsMed)"        => $idstarsmin,
                              "Max(fNumStarsMed)"        => $idstarsmax,
                              "Min(fNumStarsCorMed)"     => $corstarsmin,
                              "Max(fNumStarsCorMed)"     => $corstarsmax,
                              "Min(fZenithDistanceMin)"  => $zdmin,
                              "Max(fZenithDistanceMax)"  => $zdmax,
                              "Min(fRunStart)"           => $starttime,
                              "Max(fRunStop)"            => $stoptime,
                             );
                //get runtimes of dataset
                function GetSequenceValues($db_id, $sequences, $txt, &$values)
                {
                    $query="SELECT COUNT(*) ";
                    foreach($values as $name => $varname)
                        $query.=", " . $name;
                    $query.=" FROM Sequences ";
                    $query.=" LEFT JOIN Calibration USING(fSequenceFirst,fTelescopeNumber) ";
                    $query.=" LEFT JOIN Star USING(fSequenceFirst,fTelescopeNumber) ";
                    $query.=" WHERE Sequences.fSequenceFirst IN (" . $sequences . ")";
                    $result = mysql_query($query, $db_id);
                    $row = mysql_fetch_assoc($result);
                    foreach($values as $name => $varname)
                        $values[$name][$txt]=$row[$name];
                    mysql_free_result($result);
                }

                //get next dataset#
                $query="SELECT fDataSetNumber+1 FROM DataSets ORDER BY fDataSetNumber DESC LIMIT 0,1";
                $result = mysql_query($query, $db_id);
                $row = mysql_fetch_assoc($result);
                $dataset=$row["fDataSetNumber+1"];
                if (empty($dataset))
                    $dataset=1;
                mysql_free_result($result);

                //get sourcenames
                function GetSources($db_id, $sequences)
                {
                    $query="SELECT fSourceName FROM Source LEFT JOIN Sequences USING(fSourceKEY) ";
                    $query.="WHERE fSequenceFirst IN (" . $sequences . ") GROUP BY fSourceName";
                    $result = mysql_query($query, $db_id);
                    while ($row = mysql_fetch_assoc($result))
                        $sources.=" " . $row["fSourceName"];
                    mysql_free_result($result);
                    return $sources;
                }
                $sourceson=GetSources($db_id, $on);
                $sourcesoff=GetSources($db_id, $off);

                //get sourcename for dataset
                $son=str_replace(" ", "','", trim($sourceson));
                $query="SELECT fRealSourceKEY, Count(*) FROM Source where fSourcename IN ('" . $son . "') GROUP BY fRealSourceKEY";
//                printf("q: %s <br>", $query);
                $result = mysql_query($query, $db_id);
                $row = mysql_fetch_assoc($result);
                $numsources=$row["Count(*)"];
                $numrealkeys=mysql_num_rows($result);
                $realsourcekey=$row["fRealSourceKEY"];
                mysql_free_result($result);
//                printf("found %s sources with real sourcekey %s", $numsources, $realsourcekey);
//                printf("found %s keys", $numrealkeys);

                //check observationmode for on-sequences
                $query="SELECT fObservationModeKEY FROM Sequences where fSequenceFirst IN (" . $on . ") GROUP BY fObservationModeKEY";
                $result = mysql_query($query, $db_id);
                $numobskeys=mysql_num_rows($result);
                mysql_free_result($result);

                //check dt for on-sequences
                $query="SELECT fDiscriminatorThresholdTableKEY FROM Sequences where fSequenceFirst IN (" . $on . ") GROUP BY fDiscriminatorThresholdTableKEY";
                $result = mysql_query($query, $db_id);
                $numdtkeys=mysql_num_rows($result);
                mysql_free_result($result);


                //set limit
                $runtimelimit=5;
                $unsuitablemaxlimit=15;
                $isolatedlimit=0;
                $imclimit=0;
                $pedrmsrms=0.09;
                $scalelimit=1.3;
                $inhomlimit=13;
                $numstarslimit=20;
                $numstarscorlimit=10;


                //check values for infos, warnings and errors
                $numerr=0;
                if (!empty($on))
                {
                    $color=array("INFO"  => "#000000",
                                 "WARN"  => "#FF9900",
                                 "ERROR" => "#FF0000");
                    printf("<div align='left'><font><ul>\n");
                    $vals=array("on", "off");
                    foreach($vals as $num => $val)
                    {
                        if (empty(${$val}))
                            continue;
                        GetSequenceValues($db_id, ${$val}, $val, $values);

                        if ($values["Min(fNumStarsCorMed)"][$val]<$numstarscorlimit)
                            printf("<li style='color:%s'>%s: one of your %s-sequences has less than %s correlated stars (%s)</li>",
                                   $color["WARN"], "WARN", $val, $numstarscorlimit, $values["Min(fNumStarsCorMed)"][$val]);
                        if ($values["Min(fNumStarsMed)"][$val]<$numstarslimit)
                            printf("<li style='color:%s'>%s: one of your %s-sequences has less than %s identified stars (%s)</li>",
                                   $color["WARN"], "WARN", $val, $numstarslimit, $values["Min(fNumStarsMed)"][$val]);
                        if ($values["Min(fRunTime)/60"][$val]<$runtimelimit)
                            printf("<li style='color:%s'>%s: one of your %s-sequences is shorter than %s min (%s min)</li>",
                                   $color["INFO"], "INFO", $val, $runtimelimit, $values["Min(fRunTime)/60"][$val]);
                        if ($values["Max(fInhomogeneity)"][$val]>$inhomlimit)
                            printf("<li style='color:%s'>%s: one of your %s-sequences has an inhomogeneity larger than %s (%s)</li>",
                                   $color["WARN"], "WARN", $val, $inhomlimit, $values["Max(fInhomogeneity)"][$val]);
                        if ($values["Max(fUnsuitableInner)"][$val]>$unsuitablemaxlimit)
                            printf("<li style='color:%s'>%s: one of your %s-sequences has more than %s unsuitable inner pixel (%s)</li>",
                                   $color["WARN"], "WARN", $val, $unsuitablemaxlimit, $values["Max(fUnsuitableInner)"][$val]);
                        if ($values["Max(fIsolatedInner)"][$val]>$isolatedlimit)
                            printf("<li style='color:%s'>%s: one of your %s-sequences has more than %s isolated inner pixel (%s)</li>",
                                   $color["WARN"], "WARN", $val, $isolatedlimit, $values["Max(fIsolatedInner)"][$val]);
                        if ($values["Max(fIsolatedMaxCluster)"][$val]>$imclimit)
                            printf("<li style='color:%s'>%s: one of your %s-sequences has more than %s isolated max cluster (%s)</li>",
                                   $color["WARN"], "WARN", $val, $imclimit, $values["Max(fIsolatedMaxCluster)"][$val]);
                    }
                    if (!empty($on) && $values["Max(fMeanPedRmsInner)"]["on"]>$values["Avg(fMeanPedRmsInner)"]["on"]+$pedrmsrms)
                        printf("<li style='color:%s'>%s: one of your on-sequences has a PedRms larger than %s (%s)</li>",
                               $color["WARN"], "WARN", $values["Avg(fMeanPedRmsInner)"]["on"]+$pedrmsrms, $values["Max(fMeanPedRmsInner)"]["on"]);
                    if (!empty($off) && $values["Max(fMeanPedRmsInner)"]["off"]>$values["Avg(fMeanPedRmsInner)"]["on"]+$pedrmsrms)
                        printf("<li style='color:%s'>%s: one of your off-sequences has a PedRms larger than %s (%s)</li>",
                               $color["WARN"], "WARN", $values["Avg(fMeanPedRmsInner)"]["on"]+$pedrmsrms, $values["Max(fMeanPedRmsInner)"]["off"]);
                    if (!empty($off) && $values["Min(fMeanPedRmsInner)"]["off"]<$values["Avg(fMeanPedRmsInner)"]["on"]-$pedrmsrms)
                        printf("<li style='color:%s'>%s: one of your off-sequences has a PedRms smaller than %s (%s)</li>",
                               $color["WARN"], "WARN", $values["Avg(fMeanPedRmsInner)"]["on"]-$pedrmsrms, $values["Min(fMeanPedRmsInner)"]["off"]);
                    $scale=$values["Sum(fRunTime)/60"]["on"]/$values["Sum(fRunTime)/60"]["off"];
                    if ($scale > $scalelimit)
                        printf("<li style='color:%s'>%s: Your scale factor is larger than %s (%0.2f). Try to find more offdata!",
                               $color["WARN"], "WARN", $scalelimit, $scale);
                    /*
                    if (str_word_count($sourceson) > 1)
                        printf("<li style='color:%s'>%s: You have selected more than one (%s) on source",
                               $color["WARN"], "WARN", str_word_count($sourceson));
                    */
                    $doubleseq=0;
//                    printf("finding: %s<br>", $sequon);
                    foreach(explode(" ", $sequon) as $n => $s)
                    {
//                        printf("finding %s in %s <br>", $s, $sequoff);
                        if (ereg($s, $sequoff))
                        $doubleseq++;
                    }
                    if ($doubleseq>0)
                    {
                        printf("<li style='color:%s'>%s: You have selected sequences (%s) as On AND Off",
                               $color["ERROR"], "ERROR", $doubleseq);
                        $numerr=$numerr+1;
                    }
                    if ($numrealkeys>1)
                    {
                        printf("<li style='color:%s'>%s: You have selected more than one (%s) on source",
                               $color["ERROR"], "ERROR", $numrealkeys);
                        $numerr=$numerr+1;
                    }
                    if ($numobskeys>1)
                    {
                        printf("<li style='color:%s'>%s: You have selected more than one (%s) different observation modes for your on sequences",
                               $color["ERROR"], "WARN", $numobskeys);
//                        $numerr=$numerr+1;
                    }
                    if ($numdtkeys>1)
                    {
                        printf("<li style='color:%s'>%s: Your selected on sequences have more than one (%s) different discriminator threshold tables ",
                               $color["ERROR"], "WARN", $numdtkeys);
                    }
                    if (empty($_POST["fObservationModeKEY"]) && !empty($_GET["obsmode"]))
                        $_POST["fObservationModeKEY"]=$_GET["obsmode"];
                    if ($_POST["fObservationModeKEY"]==3 && empty($off))
                    {
                        printf("<li style='color:%s'>%s: You have selected on/off mode, but you haven't chosen any off sequences.",
                               $color["ERROR"], "WARN");
//                        $numerr=$numerr+1;
                    }
                    if (empty($realsourcekey))
                    {
                        printf("<li style='color:%s'>%s: The source you selected doesn't have a real sourcekey yet. Please insert fRealSourceKEY in the DB for %s",
                               $color["ERROR"], "ERROR", $sourceson);
                        $numerr=$numerr+1;
                    }
                    if (empty($_POST["fObservationModeKEY"]))
                    {
                        printf("<li style='color:%s'>%s: You have to choose an observation mode.",
                               $color["ERROR"], "ERROR");
                        $numerr=$numerr+1;
                    }
                    if (empty($_POST["fUserKEY"]))
                    {
                        printf("<li style='color:%s'>%s: You have to choose your username.",
                               $color["ERROR"], "ERROR");
                        $numerr=$numerr+1;
                    }
                    if (empty($_POST["name"]))
                    {
                        printf("<li style='color:%s'>%s: You have to choose a name.",
                               $color["ERROR"], "ERROR");
                        $numerr=$numerr+1;
                    }
                    if (empty($_POST["comment"]))
                    {
                        printf("<li style='color:%s'>%s: You have to comment your dataset.",
                               $color["ERROR"], "ERROR");
                        $numerr=$numerr+1;
                    }
                    //                printf("<li></li>");
                    printf("</ul></font>\n");

                    printf("<div align='left'>");
                    printf("SequencesOn: %s<br>", $sequon);
                    printf("SequencesOff: %s<br>", $sequoff);
                    $insquery[0]="INSERT DataSets SET fDataSetNumber=" . $dataset . ", fUserKEY=" . $_POST["fUserKEY"];
                    $insquery[0].=", fComment='" . $_POST["comment"] . "', fObservationModeKEY=" . $_POST["fObservationModeKEY"];
                    $insquery[0].=", fDataSetName='" . $_POST["name"] . "', fSourceKEY= " . $realsourcekey;
                    $insquery[0].=", fRunStart='" . $values["Min(fRunStart)"]["on"];
                    $insquery[0].="', fRunStop='" . $values["Max(fRunStop)"]["on"];
                    $insquery[0].="', fZenithDistanceMin=" . $values["Min(fZenithDistanceMin)"]["on"];
                    $insquery[0].=", fZenithDistanceMax=" . $values["Max(fZenithDistanceMax)"]["on"];
                    $insquery[0].=", fRunTime=" . $values["Sum(fRunTime)/60"]["on"];
                    $insquery[1]="INSERT DataSetProcessStatus SET fDataSetNumber=" . $dataset . ", fDataSetInserted=Now()";
                    $i=2;
                    foreach(explode(" ", trim($sequon)) as $key => $sequ)
                    {
                        $insquery[$i]="INSERT DataSetSequenceMapping SET fDataSetNumber=" . $dataset;
                        $insquery[$i].=", fSequenceFirst=" . $sequ . ", fOnOff=1";
                        $i=$i+1;
                    }
                    if (!empty($off))
                    {
                        foreach(explode(" ", trim($sequoff)) as $key => $sequ)
                        {
                            $insquery[$i]="INSERT DataSetSequenceMapping SET fDataSetNumber=" . $dataset;
                            $insquery[$i].=", fSequenceFirst=" . $sequ . ", fOnOff=2";
                            $i=$i+1;
                        }
                    }
                    foreach($insquery as $num => $q)
                    {
                        if (!empty($_POST["insert"]) && $numerr==0)
                        {
                            printf("Inserted Query #%s: %s<br>", $num, $q);
                            $insresult=mysql_query($q);
                            if (mysql_errno()>0)
                                printf("&nbsp; Errorno: %s <br>", mysql_errno());
                            if (mysql_error()!="")
                                printf("&nbsp; Errormsg: %s <br>", mysql_error());
                            if (mysql_info()!="")
                                printf("&nbsp; Info: %s <br>", mysql_info());
                            printf("&nbsp; Affected Rows: %s <br>", mysql_affected_rows());
                            mysql_free_result($insresult);
                            if (mysql_errno()>0)
                            {
                                printf("ERROR => stop inserting");
                                break;
                            }
                        }
                    }
                    printf("</div>");
                    if (empty($_POST["name"]) && !empty($_GET["name"]))
                        $_POST["name"]=$_GET["name"];
                    if (empty($_POST["comment"]) && !empty($_GET["comment"]))
                        $_POST["comment"]=$_GET["comment"];
                    if (empty($_POST["fUserKEY"]) && !empty($_GET["username"]))
                        $_POST["fUserKEY"]=$_GET["username"];
                    printf("Name: &nbsp;<input name='name' type='text' size='20' maxlength='20' value='%s'><br>\n", $_POST["name"]);
                    printf("Comment: &nbsp;<input name='comment' type='text' size='50' maxlength='255' value='%s'><br>\n", $_POST["comment"]);

                    $query   = "SELECT fObservationModeKEY, fObservationModeName FROM ObservationMode ORDER BY fObservationModeName";
                    $result  = mysql_query($query);
                    if (!$result)
                        printf("-N/A-");

                    $numrows = mysql_num_rows($result);

                    printf("ObservationMode&nbsp;<select name='fObservationModeKEY' size='1' class='Width'>\n");
                    while ($row = mysql_fetch_row($result))
                    {
                        if (!empty($_POST["fObservationModeKEY"]) && $_POST["fObservationModeKEY"]==$row[0])
                            printf("<option value='%s' selected>%s</option>\n", $row[0], $row[1]);
                        else
                            printf("<option value='%s'>%s</option>\n", $row[0], $row[1]);
                    }
                    printf("</select><br>\n");
                    mysql_free_result($result);

                    $query   = "SELECT fUserKEY, fUserName FROM User ORDER BY fUserKEY";
                    $result  = mysql_query($query);
                    if (!$result)
                        printf("-N/A-");

                    $numrows = mysql_num_rows($result);

                    printf("UserName&nbsp;<select name='fUserKEY' size='1' class='Width'>\n");
                    while ($row = mysql_fetch_row($result))
                    {
                        if (!empty($_POST["fUserKEY"]) && $_POST["fUserKEY"]==$row[0])
                            printf("<option value='%s' selected>%s</option>\n", $row[0], $row[1]);
                        else
                            printf("<option value='%s'>%s</option>\n", $row[0], $row[1]);
                    }
                    printf("</select><br>\n");
                    mysql_free_result($result);

                    if ($numerr!=0)
                    {
                        printf("Remark: only datasets without errors can be inserted.<br>");
                        printf("Your dataset has %s error(s).<br>", $numerr);
                    }
                    printf("<input type='checkbox' name='insert' value='On'>insert\n");

                    printf("<table><tr><td colspan='2' align='center'>Dataset</td></tr>");
                    printf("<tr><td>proposed DataSet#: </td><td>%s</td></tr>", $dataset);
                    printf("<tr><td valign='top'>SequencesOn:</td><td> %s</td></tr>", $sequon);
                    if (!empty($sequoff))
                        printf("<tr><td valign='top'>SequencesOff:</td><td> %s</td></tr>", $sequoff);
                    printf("<tr><td>Scale: </td><td>%0.2f</td></tr>", $scale);
                    printf("<tr><td>SourcenamesOn[%s]: </td><td>%s</td></tr>", str_word_count($sourceson), $sourceson);
                    printf("<tr><td>SourcenamesOff[%s]: </td><td>%s</td></tr>", str_word_count($sourcesoff), $sourcesoff);
                    printf("</table>\n");

                    printf("<table border='1'>\n");
                    printf("<tr><td>Value</td><td>On</td>");
                    if (!empty($off))
                        printf("<td>Off</td></tr>\n");
                    foreach($values as $name => $varname)
                    {
                        printf("<tr><td>%s</td>\n", $name);
                        foreach($vals as $num => $val)
                            if (!empty(${$val}))
                                printf("<td>%s</td>\n", $varname[$val]);
                        printf("</tr>\n");
                    }
                    printf("</table>\n");
                }

                printf("</div>\n");
                /*
                printf("<table border='1'><tr>\n");
                printf("<td>--</td>\n");
                foreach($values as $name => $varname)
                        printf("<td>%s</td>\n", $name);
                $vals=array("on", "off");
                foreach($vals as $num => $val)
                {
                    GetSequenceValues($db_id, ${$val}, $val, $values);
                    printf("</tr><tr>\n");
                    printf("<td>On</td>\n");
                    foreach($values as $name => $varname)
                        printf("<td>%s</td>\n", $varname[$val]);
                }
                printf("</tr></table>\n");
                */

                printf("</center>\n");
                printf("</td>\n");
                printf("</tr>\n");

                printf("</form>\n");
            }
            else
                PrintText($result0);

            mysql_free_result($result0);
        }
        mysql_close($db_id);

        PrintSubmittedQuery($query0, $html, $db, "");
    }

    include ("include.php");
    include ("menu.php");
    include ("db.php");
    include ("magicdefs.php");

    ini_set("display_errors", "On");
    ini_set("mysql.trace_mode", "On");

    $sitepw="\$1\$ml/Gld67\$zOvhC4vFrLCkbAzQs2swo1";
    $siteuser="dcdb";


    if (!isset($_SERVER['PHP_AUTH_USER']) || crypt($_SERVER['PHP_AUTH_PW'], $sitepw)!=$sitepw || $_SERVER['PHP_AUTH_USER']!=$siteuser)
    {
        header('WWW-Authenticate: Basic realm="Build Datasets"');
        header('HTTP/1.0 401 Unauthorized');
        echo 'Cancelled.';
        return;
    }
    else
    {
//    echo "<p>Hello {$_SERVER['PHP_AUTH_USER']}.</p>";
//    echo "<p>You entered {$_SERVER['PHP_AUTH_PW']} as your password.</p>";
//    printf("pw: %s", crypt($_SERVER['PHP_AUTH_PW']));

        if (crypt($_SERVER['PHP_AUTH_PW'], $sitepw)!=$sitepw || $_SERVER['PHP_AUTH_USER']!=$siteuser)
        {
            printf("<br>pw or user incorrect<br>");
            return;
        }
    }

    if (!empty($_GET["fSendTxt"]))
    {
        header("Content-type: application/octet");
        header("Content-Disposition: attachment; filename=query-result.txt");

        PrintPage("0", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
    }
    else
    {
        echo (file_get_contents("index-header.html"));

        $environment = sizeof($_GET);

        InitGet($_GET);
        if (empty($_GET["fPrintTable"]))
            PrintForm($_GET, $host, $user, $pw, $db, $limitsmean, $limitsmin, $limitsmax, $alias);

        if ($environment==0)
            printf("No query submitted yet.<BR>");
        else
        {
            if (empty($_GET["fPrintTable"]))
                PrintPage("1", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
            else
                PrintPage("2", $host, $user, $pw, $db, $alias, $rightalign, $checkwhere, $checkgroup, $checkstatusgroup, $limitsmean, $limitsmin, $limitsmax, $needs);
        }

        echo (file_get_contents("index-footer.html"));
    }

    ini_set("display_errors", "Off");
    ini_set("mysql.trace_mode", "Off");
}
?>
