source: trunk/MagicSoft/Mars/datacenter/macros/buildsequenceentries.C@ 7961

/* ======================================================================== *\
!
! *
! * This file is part of MARS, the MAGIC Analysis and Reconstruction
! * Software. It is distributed to you in the hope that it can be a useful
! * and timesaving tool in analysing Data of imaging Cerenkov telescopes.
! * It is distributed WITHOUT ANY WARRANTY.
! *
! * Permission to use, copy, modify and distribute this software and its
! * documentation for any purpose is hereby granted without fee,
! * provided that the above copyright notice appear in all copies and
! * that both that copyright notice and this permission notice appear
! * in supporting documentation. It is provided "as is" without express
! * or implied warranty.
! *
!
!
!   Author(s): Thomas Bretz, 08/2004 <mailto:tbretz@astro.uni-wuerzburg.de>
!   Author(s): Daniela Dorner, 08/2004 <mailto:dorner@astro.uni-wuerzburg.de>
!
!   Copyright: MAGIC Software Development, 2000-2006
!
!
\* ======================================================================== */

/////////////////////////////////////////////////////////////////////////////
//
// buildsequenceentries.C
// ======================
//
// to group the runs of one night into sequences, this marco:
//  - reads the runinformation of one night from the database
//  - group the runs into sets of following runs with the same conditions
//  - groups the runs in this sets to sequences such that each run belongs
//    to the nearest (in time) calibration run
//  - check if the runs with the same runtype have the same calibration script
//    and the same trigger tables
//  if sequence is okay:
//  - check if in the range of the runnumbers of this sequence other sequences
//    exist in the database
//  if there are no sequences, insert the new sequence, else:
//  - delete overlaping sequences
//  if there's only one sequence in the same runnumber range:
//  - check if the new and the old sequence are identical
//    if they are identical, do nothing, if not, delete the old sequence and
//    insert the new one
//
// remark: deleting sequences includes the following steps:
// - delete entries from the tables Sequences, SequenceProcessStatus,
//   Calibration and Star
// - updating the sequence number (fSequenceFirst) in the table RunData
// - remove the Sequence File, the calibrated data and the image files from
//   the disk
//
// the macro can be executed either for all nights or for one single night
// .x buildsequenceentries.C+( "datapath", "sequpath", Bool_t dummy=kTRUE)
// .x buildsequenceentries.C+( "night", "datapath", "sequpath")
//
// the Bool_t dummy:
//  kTRUE:  dummy-mode, i.e. nothing is inserted into the database, but the
//          commands, that would be executed are returned
//  kFALSE: the information is inserted into the database and the files of
//          removed sequences is deleted
// be careful with this option - for tests use always kTRUE
//
// TString datapath, TString sequpath:
//  datapath: path, where the processed data is stored in the datacenter
//  sequpath: path, where the sequence files are stored in the datacenter
// the datapath (standard: /magic/data/) and the sequencepath (standard:
// /magic/sequences) have to be given, that the sequence file, the
// calibrated data and the star files can be removed, when an old sequence
// has to be removed from the database
//
// If nothing failes 1 is returned. In the case of an error 2 and if
// there's no connection to the database 0 is returned.
// This is needed for the scripts that execute the macro.
//
/////////////////////////////////////////////////////////////////////////////
#include <iostream>
#include <iomanip>
#include <fstream>

#include <MSQLServer.h>
#include <TSQLRow.h>
#include <TSQLResult.h>

#include <TEnv.h>
#include <TMath.h>
#include <TExMap.h>
#include <TArrayI.h>
#include <TRegexp.h>
#include <TSystem.h>
#include <TObjString.h>
#include <TObjArray.h>

#include <MTime.h>
#include <MDirIter.h>

using namespace std;

int debug = 0;

Bool_t DeleteSequence(MSQLServer &serv, TString datapath, TString sequpath, Int_t sequ, Bool_t dummy)
{
    //queries to delete information from the database
    TString query1(Form("DELETE FROM Calibration WHERE fSequenceFirst=%d", sequ));
    TString query2(Form("DELETE FROM Star WHERE fSequenceFirst=%d", sequ));
    TString query3(Form("DELETE FROM SequenceProcessStatus WHERE fSequenceFirst=%d", sequ));
    TString query4(Form("UPDATE RunData SET fSequenceFirst=0 WHERE fSequenceFirst=%d", sequ));
    TString query5(Form("DELETE FROM Sequences WHERE fSequenceFirst=%d AND fManuallyChangedKEY=1", sequ));

    //commands to delete files from the disk
    TString fname(Form("%s/%04d/sequence%08d.txt", sequpath.Data(),sequ/10000, sequ));
    TString command(Form("rm -r %s/callisto/%04d/%08d/", datapath.Data(), sequ/10000, sequ));
    TString command2(Form("rm -r %s/star/%04d/%08d/", datapath.Data(), sequ/10000, sequ));

    if (dummy)
    {
        cout << "not using dummy=kTRUE the following commands would be executed: " << endl;
        cout << "queries: " << endl;
        cout << query1 << endl;
        cout << query2 << endl;
        cout << query3 << endl;
        cout << query4 << endl;
        cout << query5 << endl;
        cout << "removing files:" << endl;
        cout << "unlink " << fname << endl;
        cout << command << endl;
        cout << command2 << endl;
        return kTRUE;
    }

    TSQLResult *res = serv.Query(query1);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query2);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query3);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query4);
    if (!res)
        return kFALSE;
    delete res;

    res = serv.Query(query5);
    if (!res)
        return kFALSE;
    delete res;

    gSystem->Unlink(fname);

    gSystem->Exec(command);
    gSystem->Exec(command2);

    return kTRUE;
}

Int_t DoCheck(TSQLResult &res)
{
    TArrayI data(5);
    Int_t n = 0;

    TSQLRow *row=0;
    while ((row=res.Next()))
    {
        n++;

        if (data[0]==0)
        {
            for (int i=0; i<data.GetSize(); i++)
                data[i] = atoi((*row)[i]);
            continue;
        }

        for (int i=1; i<data.GetSize(); i++)
        {
            if (data[i] != atoi((*row)[i]))
                return i+1;
        }
    }
    return n==0 ? 0 : -1;
}


//check values, that can be different for different runtypes
Bool_t CheckRuns(MSQLServer &serv, Int_t from, Int_t to, Int_t type)
{
    TString query("SELECT fRunNumber, fL1TriggerTableKEY, fL2TriggerTableKEY,"
                  " fCalibrationScriptKEY, fProjectKEY FROM RunData");
    query += Form(" WHERE fRunTypeKEY=%d AND fExcludedFDAKEY=1 AND "
                  " (fRunNumber BETWEEN %d AND %d)"
                  " ORDER BY fRunNumber", type, from, to);

    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    Int_t rc = DoCheck(*res);
    delete res;

    switch (rc)
    {
    case 0: cout << "ERROR - No runs found for check!"             << endl; break;
    case 1: cout << "ERROR - fRunNumber doesn't match!"            << endl; break;
    case 2: cout << "ERROR - fL1TriggerTableKEY doesn't match!"    << endl; break;
    case 3: cout << "ERROR - fL2TriggerTableKEY doesn't match!"    << endl; break;
    case 4: cout << "ERROR - fCalibrationScriptKEY doesn't match!" << endl; break;
    case 5: cout << "ERROR - fProjectKEY doesn't match!"           << endl; break;
    }

    return rc<0;
}

Int_t CheckSequence(MSQLServer &serv, TString datapath, TString sequpath, Int_t from, Int_t to, Bool_t dummy)
{
    Int_t rc=0;
    //rc=0 means sequence not found -> insert
    //rc=1 means deleting sequence(s) worked -> insert
    //rc=2 means sequence is still the same -> insert not neccessary
    //if deleting sequence doesn't work -> return -1


    //getting # of sequence (in sequDB) between from and to
    TString query("SELECT fSequenceFirst FROM Sequences WHERE (fSequenceFirst ");
    query += Form("BETWEEN %d and %d OR fSequenceLast BETWEEN %d and %d) AND "
                  "fManuallyChangedKEY=1 ", from, to, from, to);

    TSQLResult *res = serv.Query(query);
    if (!res)
        return -1;

    TArrayI sequences;
    Int_t numsequ=0;

    TSQLRow *row=0;
    while ((row=res->Next()))
    {
        numsequ++;
        sequences.Set(numsequ);
        sequences.AddAt(atoi((*row)[0]), numsequ-1);
    }
    delete res;

    //if there's no sequence in the table Sequences -> check other tables
    //if there's one sequence -> check if the sequence is identical
    //if there are more sequences -> delete them
    switch (numsequ)
    {
    case 0:
        //FIXME: like this the check is of no use, but when doing it
        //       without the check for manuallychanged, all manually
        //       changed sequences would be deleted
        cout << "   + found no sequence in Sequ-DB -> check other tables" << endl;
        cout << "     deleting every sequence found in Calibration, Star or" << endl;
        cout << "     SequenceProcessStatus between " << from << " and " << to << endl;

        //calibration table
        query=Form("SELECT Calibration.fSequenceFirst FROM Calibration "
                   " LEFT JOIN Sequences ON Calibration.fSequenceFirst=Sequences.fSequenceFirst "
                   " WHERE fManuallyChangedKEY=1 AND Calibration.fSequenceFirst BETWEEN %d and %d",
                   from, to);
        res = serv.Query(query);
        if (!res)
            return -1;
        row=0;
        while ((row=res->Next()))
        {
            if (!DeleteSequence(serv, datapath, sequpath, atoi((*row)[0]), dummy))
                return -1;
            rc=1;
        }
        delete res;

        //Star table
        query=Form("SELECT Star.fSequenceFirst FROM Star "
                   " LEFT JOIN Sequences ON Star.fSequenceFirst=Sequences.fSequenceFirst "
                   " WHERE fManuallyChangedKEY=1 AND  Star.fSequenceFirst BETWEEN %d and %d",
                   from, to);
        res = serv.Query(query);
        if (!res)
            return -1;
        row=0;
        while ((row=res->Next()))
        {
            if (!DeleteSequence(serv, datapath, sequpath, atoi((*row)[0]), dummy))
                return -1;
            rc=1;
        }
        delete res;

        //SequenceProcessStatus table
        query=Form("SELECT SequenceProcessStatus.fSequenceFirst FROM SequenceProcessStatus "
                   " LEFT JOIN Sequences ON SequenceProcessStatus.fSequenceFirst=Sequences.fSequenceFirst "
                   " WHERE fManuallyChangedKEY=1 AND  SequenceProcessStatus.fSequenceFirst BETWEEN %d and %d",
                   from, to);
        res = serv.Query(query);
        if (!res)
            return -1;
        row=0;
        while ((row=res->Next()))
        {
            if (!DeleteSequence(serv, datapath, sequpath, atoi((*row)[0]), dummy))
                return -1;
            rc=1;
        }
        delete res;
        break;

    case 1:
        cout << "   + found one sequence: " << sequences.At(0) << " -> check sequence." << endl;
        if (sequences.At(0)!=from)
        {
            if (!DeleteSequence(serv, datapath, sequpath, sequences.At(0), dummy))
                return -1;
            rc=1;
        }
        else
        {
            cout << "     * sequence is the same -> checking the runs." << endl;

            //getting olf runs
            query=Form("SELECT fRunNumber FROM RunData WHERE fSequenceFirst=%d ", from);
            res = serv.Query(query);
            if (!res)
                return -1;

            TArrayI oldruns;
            Int_t count=0;
            row=0;
            while ((row=res->Next()))
            {
                count++;
                oldruns.Set(count);
                oldruns.AddAt(atoi((*row)[0]), count-1);
            }
            delete res;

            //getting new runs
            query=Form("SELECT fRunNumber FROM RunData WHERE fRunNumber BETWEEN %d and %d AND fExcludedFDAKEY=1", from, to);
            res = serv.Query(query);
            if (!res)
                return -1;
            TArrayI newruns;
            count=0;
            row=0;
            while ((row=res->Next()))
            {
                count++;
                newruns.Set(count);
                newruns.AddAt(atoi((*row)[0]), count-1);
            }
            delete res;

            //comparing old and new runs (first the # of runs, if it is the same, also the single runnumbers
            if (oldruns.GetSize()!=newruns.GetSize())
            {
//                cout << " number of runs (" << oldruns.GetSize() << " - " << newruns.GetSize()
//                    << ") is not the same -> deleting sequence " << sequences.At(0) << endl;
                if (!DeleteSequence(serv, datapath, sequpath, sequences.At(0), dummy))
                    return -1;
                rc=1;
            }
            else
            {
                cout << "       · number of runs is the same -> checking the single runnumbers." << endl;

                for (Int_t i=0;i<newruns.GetSize();i++)
                {
//                    cout << "i: " << i << " - new: " << newruns.At(i) << " - old: " << oldruns.At(i) << endl;
                    if (newruns.At(i)==oldruns.At(i))
                        continue;

                    cout << "       · " << i << ". run is not the same ( " << oldruns.At(i) << " -- " << newruns.At(i)
                        << ") -> deleting sequence " << sequences.At(0) << endl;
                    if (!DeleteSequence(serv, datapath, sequpath, sequences.At(0), dummy))
                        return -1;
                    return 1;
                }
            }
        }
        return 2;

    default:
        cout << " - found " << numsequ << " sequences -> deleting them " << endl;

        for (Int_t i=0;i<sequences.GetSize();i++)
        {
                cout << "   + deleting sequence " << sequences.At(i) << "... <" << i << ">" << endl;
                if (!DeleteSequence(serv, datapath, sequpath, sequences.At(i), dummy))
                    return -1;
                rc=1;
        }
    }

    return rc;
}

Bool_t InsertSequence(MSQLServer &serv, Int_t from, Int_t to)
{
    cout << "       · Inserting sequence " << from << " ... " << endl;

    // ========== Request number of events ==========
    TString query("SELECT SUM(fNumEvents), "
                  " SUM(if(TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart)<0,"
                  "  TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart)+24*60*60,"
                  "  TIME_TO_SEC(fRunStop)-TIME_TO_SEC(fRunStart))), ");
    query +=      " MIN(fZenithDistance), MAX(fZenithDistance), ";
    query +=      " MIN(fAzimuth), MAX(fAzimuth) ";
    query += Form(" FROM RunData WHERE fRunTypeKEY=2 AND "
                  " (fRunNumber BETWEEN %d AND %d) AND fExcludedFDAKEY=1",
                  from, to);

    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    TSQLRow *row = res->Next();
    if (!row || !(*row)[0])
    {
        cout << "ERROR - No result from query: " << query << endl;
        return kFALSE;
    }

    TString nevts = (*row)[0];
    TString secs  = (*row)[1];
    TString zdmin = (*row)[2];
    TString zdmax = (*row)[3];
    TString azmin = (*row)[4];
    TString azmax = (*row)[5];

    delete res;

    // ========== Request start time of sequence ==========
    query = Form("SELECT fRunStart FROM RunData WHERE fRunNumber=%d AND fExcludedFDAKEY=1", from);

    res = serv.Query(query);
    if (!res)
        return kFALSE;

    row = res->Next();
    if (!row || !(*row)[0])
    {
        cout << "ERROR - No result from query: " << query << endl;
        return kFALSE;
    }

    TString start((*row)[0]);

    delete res;

    // ========== Request data of sequence ==========
    query = Form("SELECT fSourceKEY, fProjectKEY, "
                 " fL1TriggerTableKEY, fL2TriggerTableKEY,"
                 " fHvSettingsKEY, fDiscriminatorThresholdTableKEY,"
                 " fTriggerDelayTableKEY, fLightConditionsKEY, "
                 " fTestFlagKEY, fObservationModeKEY "
                 " FROM RunData"
                 " WHERE fRunTypeKEY=2 AND fExcludedFDAKEY=1 AND (fRunNumber BETWEEN %d AND %d)"
                 " LIMIT 1", from, to);

    res = serv.Query(query);
    if (!res)
        return kFALSE;

    row = res->Next();
    if (!row)
    {
        cout << "ERROR - No result from query: " << query << endl;
        return kFALSE;
    }

    TString query1("INSERT Sequences SET");
    query1 += Form(" fSequenceFirst=%d,  fSequenceLast=%d,", from, to);
    query1 += Form(" fSourceKEY=%s,",                      (*row)[0]);
    query1 += Form(" fProjectKEY=%s,",                     (*row)[1]);
    query1 += Form(" fNumEvents=%s,",                      nevts.Data());
    query1 += Form(" fRunTime=%s,",                        secs.Data());
    query1 += Form(" fRunStart=\"%s\",",                   start.Data());
    query1 += Form(" fZenithDistanceMin=%s,",              zdmin.Data());
    query1 += Form(" fZenithDistanceMax=%s,",              zdmax.Data());
    query1 += Form(" fAzimuthMin=%s,",                     azmin.Data());
    query1 += Form(" fAzimuthMax=%s,",                     azmax.Data());
    query1 += Form(" fL1TriggerTableKEY=%s,",              (*row)[2]);
    query1 += Form(" fL2TriggerTableKEY=%s,",              (*row)[3]);
    query1 += Form(" fHvSettingsKEY=%s,",                  (*row)[4]);
    query1 += Form(" fDiscriminatorThresholdTableKEY=%s,", (*row)[5]);
    query1 += Form(" fTriggerDelayTableKEY=%s,",           (*row)[6]);
    query1 += Form(" fLightConditionsKEY=%s,",             (*row)[7]);
    query1 += Form(" fTestFlagKEY=%s,",                    (*row)[8]);
    query1 += Form(" fObservationModeKEY=%s, ",            (*row)[9]);
    query1+="fManuallyChangedKEY=1";


    TString query2 = Form("UPDATE RunData SET fSequenceFirst=%d WHERE"
                          "  (fRunNumber  BETWEEN %d AND %d) AND"
                          "  (fRunTypeKEY BETWEEN  2 AND  4) AND"
                          "  fSourceKEY=%s AND fHvSettingsKEY=%s AND fExcludedFDAKEY=1",
                          from, from, to, (*row)[0], (*row)[4]);

    TString query3 = Form("INSERT SequenceProcessStatus SET fSequenceFirst=%d ", from);

    delete res;

    /*
     cout << "q1: " << query1 << endl;
     cout << "q2: " << query2 << endl;
     cout << "q3: " << query3 << endl;
     */

    res = serv.Query(query1);
    if (!res)
    {
        cout << "ERROR - Could not insert Sequence into Sequences." << endl;
        return kFALSE;
    }
    delete res;

    res = serv.Query(query2);
    if (!res)
    {
        cout << "ERROR - Could not update RunData." << endl;
        return kFALSE;
    }
    delete res;

    res = serv.Query(query3);
    if (!res)
    {
        cout << "ERROR - Could not insert Sequence into SequenceProcessStatus." << endl;
        return kFALSE;
    }
    delete res;

    return kTRUE;
}

//
// Handling new sequence (checking runs; checking for old sequence; inserting sequence, if everything is okay)
//
Bool_t NewSequence(MSQLServer &serv, TString datapath, TString sequpath, Int_t from, Int_t to, TList &sequlist, Bool_t dummy)
{
    cout << " - Found Sequence (" << from << ", " << to << ") ... checking runs..." << flush;

    if (!CheckRuns(serv, from, to, 2))
    {
        cout << "Warning - Found inconsistency in data-runs (" << from << ", " << to << ")" << endl;
        //sequence is not built, but kTRUE is returned, to allow
        //the automatic processing of the other sequences of this day
        return kTRUE;
    }
    if (!CheckRuns(serv, from, to, 3))
    {
        cout << "Warning - Found inconsistency in ped-runs (" << from << ", " << to << ")" << endl;
        //sequence is not built, but kTRUE is returned, to allow
        //the automatic processing of the other sequences of this day
        return kTRUE;
    }

    cout << "ok." << endl;


    cout << "   + checking sequence..." << endl;

    TObject *sequ;
    Bool_t rc=kFALSE;
    switch (CheckSequence(serv, datapath, sequpath, from, to, dummy))
    {
    case 0:
        cout << "       · sequence not found -> inserting " << from << flush;
        if (dummy)
        {
            cout << " <dummy> " << endl;
            return kTRUE;
        }
        cout << endl;
        if ((sequ=sequlist.Remove(sequlist.FindObject(Form("%d", from)))))
            delete sequ;
        return InsertSequence(serv, from, to);

    case 1:
        cout << "       · deleting successfully finished -> inserting sequence " << from << flush;
        if (dummy)
        {
            cout << " <dummy> " << endl;
            return kTRUE;
        }
        cout << endl;
        if ((sequ=sequlist.Remove(sequlist.FindObject(Form("%d", from)))))
            delete sequ;
        return InsertSequence(serv, from, to);

    case 2:
        cout << "       · sequence " << from << " is already existing -> inserting not necessary" << endl;
        if ((sequ=sequlist.Remove(sequlist.FindObject(Form("%d", from)))))
            delete sequ;
        return kTRUE;

    case -1:
        cout << "       · deleting went wrong " << endl;
        return kFALSE;
    }


    return rc;
}

//
// Build Sequences in range of runs
//
Bool_t Process(MSQLServer &serv, TString datapath, TString sequpath, Int_t from, Int_t to,  TList &sequlist, Bool_t dummy)
{

    TString query(Form("SELECT fRunNumber, fRunTypeKEY, fRunStart, fRunStop"
                       " FROM RunData"
                       " WHERE fRunNumber BETWEEN %d AND %d AND "
                       " fExcludedFDAKEY=1 AND (fRunTypeKEY BETWEEN 2 AND 4)"
                       " ORDER BY fRunNumber", from, to));

    TSQLResult *res = serv.Query(query);
    if (!res)
        return kFALSE;

    TExMap map;

    Int_t start=0;
    Int_t stop=0;
    Int_t last=0;
    Int_t first=0;

    MTime lasttime;

    TSQLRow *row=0;

    enum { UNKNOWN, PED=3, CAL=4, DATA=2 };
    Char_t status = UNKNOWN;

    Int_t nblocks = 0;

    while ((row=res->Next()))
    {
        if (!(*row)[1])
            continue;

        if (start==0)
        {
            first = atoi((*row)[0]);
            if (debug)
                cout << "First Run: " << first << endl;
        }

        switch (atoi((*row)[1]))
        {
        case CAL: // ---------- CALIBRATION ----------
            if (status!=CAL)
            {
                start = stop = atoi((*row)[0]);
                if (!(*row)[2])
                    cout << "No time available... skipped." << endl;
                else
                {
                    MTime *tm = new MTime;
                    tm->SetSqlDateTime((*row)[2]);
                    map.Add((ULong_t)map.GetSize(), (Long_t)tm, (Long_t)nblocks);
                }
            }
            status = CAL;
            break;
        default:
            if (status==CAL)
            {
                MTime *tm = new MTime(lasttime);
                map.Add((ULong_t)map.GetSize(), (Long_t)tm, (Long_t)nblocks);

                stop = last;
                nblocks++;
                if (debug)
                    cout << "Cal Block #" << nblocks << " from " << start << " to " << last << endl;
            }
            status = UNKNOWN;
            break;
        }
        last = atoi((*row)[0]);
        lasttime.SetSqlDateTime((*row)[3]);
    }
    if (status==CAL)
    {
        stop = last;
        nblocks++;
        if (debug)
            cout << "Cal Block #" << nblocks << " from " << start << " to " << stop << endl;
    }

    if (debug)
        cout << "Last Run: " << last << endl;
    delete res;

    if (debug)
        cout << "Found " << nblocks << " calibration blocks" << endl;

    res = serv.Query(query);
    if (!res)
        return kFALSE;

    Int_t n = -1;

    Bool_t rc = kTRUE;

    start = first;
    while ((row=res->Next()))
    {
        if (!(*row)[1])
            continue;

        MTime tstart, tstop;
        tstart.SetSqlDateTime((*row)[2]);
        tstop.SetSqlDateTime((*row)[3]);

        MTime    min;
        Int_t    nmin = -1;
        Double_t dmin = 1e35;

        Long_t key, val;
        TExMapIter nmap(&map);
        while (nmap.Next(key, val))
        {
            MTime *t = (MTime*)key;

            if (nmin==-1)
            {
                nmin = val;
                min  = *(MTime*)key;
                dmin = fabs((Double_t)*t-(Double_t)tstart);
            }

            if (fabs((Double_t)*t-(Double_t)tstart) < dmin)
            {
                nmin = val;
                dmin = fabs((Double_t)*t-(Double_t)tstart);
                min = *t;
            }
            if (fabs((Double_t)*t-(Double_t)tstop) < dmin)
            {
                nmin = val;
                dmin = fabs((Double_t)*t-(Double_t)tstop);
                min = *t;
            }
        }

        if (n!=nmin)
        {
            if (n!=-1)
            {
                if (!NewSequence(serv, datapath, sequpath, start, last, sequlist, dummy))
                {
                    rc = kFALSE;
                    //continue;
                }
            }
            n = nmin;
            start = atoi((*row)[0]);
        }
        last = atoi((*row)[0]);
    }

    delete res;

    if (n!=-1 && start!=last)
    {
        if (!NewSequence(serv, datapath, sequpath, start, last, sequlist, dummy))
            rc = kFALSE;
    }

    if (debug)
        cout << endl;

    return rc; 
}

void buildblocks(TSQLResult *res, TExMap &blocks)
{
    //build blocks of runs, which have the same values
    //for each block the first and the last run are stored in a TExMap
    //the values are checked with the help of an array of TStrings
    TString keys[7];

    Long_t runstart = -1;
    Long_t runstop  = -1;

    // "Result set closed" means "Result set empty"

    // Loop over runs
    TSQLRow *row=0;
    while ((row=res->Next()))
    {
        // Check whether all keys for this run fit
        for (Int_t i=1; i<8; i++)
        {
            // Do not check project key for this runs
            // (this condition is never true for the first run)
            if (i==2 && runstart>20100 && runstop<45100)
                continue;

            // Check whether key has changed for this run
            // (this condition is never true for the first run)
            if (keys[i-1] == (*row)[i])
                continue;

            // Found one block with unique keys, fill values into TExMap
            // (except if this is the first run processed)
            if (runstart>0)
                blocks.Add((ULong_t)blocks.GetSize(), runstart, runstop);

            // This is the runnumber of the first run in the new block
            runstart=atoi((*row)[0]);

            // These are the keys corresponding to the first run in the new block
            for (int i=1; i<8; i++)
                keys[i-1] = (*row)[i];

            break;
        }

        // This is the new runnumber of the last run in this block
        runstop=atoi((*row)[0]);
    }

    if (runstart!=runstop)
    {
        //fill values into TExMap (last value)
        blocks.Add((ULong_t)blocks.GetSize(), runstart, runstop);
    }
}

//
// Build Sequences for the night given by TString day
//
int buildsequenceentries(TString day, TString datapath, TString sequpath, Bool_t dummy=kTRUE)
{
    TEnv env("sql.rc");

    MSQLServer serv(env);
    if (!serv.IsConnected())
    {
        cout << "ERROR - Connection to database failed." << endl;
        return 0;
    }

    cout << "buildsequences" << endl;
    cout << "--------------" << endl;
    cout << endl;
    cout << "Connected to " << serv.GetName() << endl;
    cout << "Night of sunrise at: " << day << endl;
    cout << endl;

    day += " 13:00:00";
    const TString cond(Form("(fRunStart>ADDDATE(\"%s\", INTERVAL -1 DAY) AND fRunStart<\"%s\")",
                            day.Data(), day.Data()));

    TString elts  = "fSourceKEY";
    TString eltp2 = "fProjectKEY";

    //get all values from the database, that are relevant for building sequences
    TString query("SELECT fRunNumber, ");
    query += elts;
    query += ", ";
    query += eltp2;
    query += Form(", fHvSettingsKEY, fLightConditionsKEY, fDiscriminatorThresholdTableKEY, "
                  "fTriggerDelayTableKEY, fObservationModeKEY FROM RunData "
                  "WHERE %s AND fExcludedFDAKEY=1 order by fRunNumber", cond.Data());

    TSQLResult *res = serv.Query(query);
    if (!res)
        return 2;

    TExMap blocks;
    buildblocks(res, blocks);
    delete res;

    // ---
    // Now the blocks containing the same observation conditions
    // are created...
    // ---

    //get list of current sequence of this night from the database and store it in sequlist
    TList sequlist;
    query=Form("SELECT fSequenceFirst FROM Sequences WHERE fManuallyChangedKEY=1 AND %s order by fSequenceFirst",
               cond.Data());

    res = serv.Query(query);
    if (!res)
        return 2;

    cout << "Old sequences: " << flush;
    TSQLRow *row=0;
    while ((row=res->Next()))
    {
        const char *str =  (*row)[0];
        cout << str << " " << flush;
        sequlist.Add(new TObjString(str));
    }
    cout << endl;

    delete res;

    Bool_t rc = kTRUE;

    //build sequences in each block of runs
    Long_t key, val;
    TExMapIter nblocks(&blocks);
    while (nblocks.Next(key, val))
    {
        const Int_t runstart = (Int_t)key;
        const Int_t runstop  = (Int_t)val;

        cout << endl << "Datablock from " << runstart << " to " << runstop << ":" << endl;

        if (!Process(serv, datapath, sequpath, runstart, runstop, sequlist, dummy))
            rc = kFALSE;

    }

    //newly build or remaining sequences are removed from sequlist while building sequences
    //delete sequences that remained in the list
    //this is necessary if e.g. all runs of one old sequence have been excluded in the meantime
    TIter Next(&sequlist);
    TObject *obj = 0;
    while ((obj=Next()))
    {
        cout << " - Sequence " << obj->GetName() << " deleting... " << endl;
        if (!DeleteSequence(serv, datapath, sequpath, atoi(obj->GetName()), dummy))
            return 2;
    }

    return rc ? 1 : 2;
}

//
// Build Sequences for all Nights
//
int buildsequenceentries(TString datapath, TString sequpath, Bool_t dummy=kTRUE)
{
    TEnv env("sql.rc");

    MSQLServer serv(env);
    if (!serv.IsConnected())
    {
        cout << "ERROR - Connection to database failed." << endl;
        return 0;
    }

    //get all dates from the database
    TString query="SELECT fDate FROM SequenceBuildStatus";

    TSQLResult *res = serv.Query(query);
    if (!res)
        return 2;

    //execute buildsequenceentries for all dates
    TString date;
    TSQLRow *row=0;
    while ((row=res->Next()))
    {
        date=(*row)[0];
        buildsequenceentries(date, datapath, sequpath, dummy);
    }

    delete res;

    return 1;
}