source: trunk/MagicSoft/Mars/datacenter/macros/doexclusions.C@ 7630

/* ======================================================================== *\
!
! *
! * 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
!
!
\* ======================================================================== */

/////////////////////////////////////////////////////////////////////////////
//
// doexclusions.C
// ==============
//
// this macro sets the ExcludedFDA flag for runs, that can be excluded
// automatically
// the information which runs have to be excluded is retrieved from the
// resource file automatic-exclusions.rc
//
// the macro can be executed either for a night or for a range of runnumbers
// or for all nights
// .x doexclusions.C+("night")
// .x doexclusions.C+(startrun,stoprun)
// .x doexclusions.C+
//
// resource file: automatic-exclustions.rc
// for each exclusion-reason there is one block of 6 lines in this file:
// example for one exclusion-reason:
//  #NumEventsSmaller10:                   (name of exclusion)
//  key15.Column: fNumEvents               (name of the affected column)
//  #key15.Join1:                          (name of column that has to 
//  #key15.Join2:                                             be joined)
//  key15.Cond: fNumEvents between 2 and 9 (condition that fulfils exclusion)
//  #key15.SpecialRunCond:                 (special condition, if exclusion
//                                          is needed only for certain runs)
// if a value is not needed for an exclusion (like the joins and the special
// condition in this example), the line is commented out
//
// Returns 2 in case of failure, 1 in case of success and 0 if the connection
// to the database is not working.
//
/////////////////////////////////////////////////////////////////////////////
#include <iostream>
#include <iomanip>
#include <fstream>

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

#include <TEnv.h>
#include <TSystem.h>

using namespace std;

//get minimum or maximum runnumber of the runs of a night
int GetRunNumber(MSQLServer &serv, TString date, TString value)
{
    TString query(Form("SELECT %s(fRunNumber) FROM RunData ", value.Data()));

    if (date!="NULL")
    {
        TString day=date+" 13:00:00";
        query+=Form(" WHERE (fRunStart>ADDDATE(\"%s\", INTERVAL -1 DAY) AND fRunStart<\"%s\")",
                    day.Data(), day.Data());
    }

    cout << "query: " << query << endl;

    TSQLResult *res = serv.Query(query);
    if (!res)
    {
        cout << "Error - could not get run#" << endl;
        return -1;
    }

    TSQLRow *row =res->Next();
    cout << (void*)row << endl;
    if (TString((*row)[0]).IsNull())
    {
        cout << "No run available for this date" << endl;
        delete res;
        return 0;
    }
    delete res;
    return atoi((*row)[0]);
}

//get part of a query (left join of tables)
TString GetJoin(TString table)
{
    TString query(Form("left join %s ON RunData.f%sKEY=%s.f%sKEY ",
                       table.Data(), table.Data(), table.Data(), table.Data()));
    return query;
}

int doexclusions(Int_t startrun, Int_t stoprun, TString date="NULL")
{
    TEnv env("sql.rc");
    TEnv rc("automatic-exclusions.rc");

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

    cout << "doexclusions" << endl;
    cout << "------------" << endl;
    cout << endl;
    cout << "Connected to " << serv.GetName() << endl;

    //if neither start- nor stoprun is given, the minimum and maximum runnumber
    // is queried from the database to do the exclusions for all runs
    // if a night is given for all runs of this night
    if (startrun==0 && stoprun==0)
    {
        startrun=GetRunNumber(serv, date, "min");
        stoprun=GetRunNumber(serv, date, "max");
    }
    //check format of start- and stoprun
    if (startrun<0 || stoprun<0)
    {
        cout << "wrong format of runno" << endl;
        return 2;
    }
    //if no run for date is available, GetRunNumber() returns 0
    if (startrun==0 || stoprun==0)
        return 1;

    //get the condition for the runnumber range
    TString runcond(Form("AND fRunNumber BETWEEN %d AND %d ", startrun, stoprun));

    //get exclusions-reasons (stored in the table ExcludedFDA) from the database
    //the exclusions which can be done automatically are marked with the flag fExcludedFDAAutomatic='yes'
    //and with an importance (one run may be excluded for several reasons,
    //the reason is chosen according to the importance)
    TString query="SELECT fExcludedFDAKEY from ExcludedFDA where fExcludedFDAAutomatic='yes'";
    TSQLResult *res = serv.Query(query);
    if (!res)
    {
        cout << "Error - could not do any automatic excludes." << endl;
        return 2;
    }

    //update the exclusion-reasons for all runs
    TSQLRow *row=0;
    while ((row = res->Next()))
    {
        //read in values from the resource file automatic-exclusions (explanation see above)
        TString key=(*row)[0];
        TString column=rc.GetValue("key"+key+".Column", "");
        TString join1=rc.GetValue("key"+key+".Join1", "");
        TString join2=rc.GetValue("key"+key+".Join2", "");
        TString border=rc.GetValue("key"+key+".SpecialRunCond", "");

        //get importance of exclusion-reason from database
        TString query(Form("SELECT fExcludedFDAImportance from ExcludedFDA where fExcludedFDAKEY=%s ", key.Data()));
        TSQLResult *res = serv.Query(query);
        if (!res)
        {
            cout << "Error - could not get importance." << endl;
            return 2;
        }

        TSQLRow *row2=res->Next();
        Int_t newimp=atoi((*row2)[0]);
        delete res;

        //get current importance from database
        //for the runs which match the exclusion-reason
        query="SELECT fRunNumber, fExcludedFDAImportance ";
        if (!column.IsNull())
            query+=Form(", %s", column.Data());
        if (!join1.IsNull())
            query+=Form(", f%sName", join1.Data());
        if (!join2.IsNull())
            query+=Form(", f%sName", join2.Data());
        query +=" FROM RunData ";
        query +=GetJoin("ExcludedFDA");
        if (!join1.IsNull())
            query+=GetJoin(join1.Data());
        if (!join2.IsNull())
            query+=GetJoin(join2.Data());
        query +=Form("WHERE (%s) ", rc.GetValue("key"+key+".Cond", ""));
        if (!border.IsNull())
            query+=Form(" AND fRunNumber BETWEEN IF(%s>%d, %d, %s) AND IF (%s<%d, %s, %d) ",
                        border.Data(), startrun, startrun, border.Data(),
                        border.Data(), stoprun, border.Data(), stoprun);
        else
            query +=runcond;

        cout << query << endl;

        res = serv.Query(query);
        if (!res)
        {
            cout << "Error - no runs to exclude" << endl;
            return 2;
        }

        //compare new and old importance
        //change or keep the exclusion-reason accordingly
        while ((row2 = res->Next()))
        {
            if (TString((*row2)[1]).IsNull() || atoi((*row2)[1])>newimp)
            {
                //change exclusion-reason
                TString query(Form("UPDATE RunData SET fExcludedFDAKEY=%s WHERE fRunNumber=%s",
                                   key.Data(), (*row2)[0]));
                cout << "QU: " << query << endl;
                TSQLResult *res = serv.Query(query);
                if (!res)
                {
                    cout << "Error - could not insert exclusion" << endl;
                    return 2;
                }
                delete res;
                continue;
            }
            //keep exclusion-reason
            cout << "run#: " << (*row2)[0] << " reason for exclusion is still the same" << endl;
        }
        delete res;
    }
    delete res;
    return 1;
}

//run doexclusions for one night
int doexclusions(TString date="NULL")
{
    return doexclusions(0, 0, date);
}