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

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

/////////////////////////////////////////////////////////////////////////////
//
// doexclusions.C
// ==============
//
/////////////////////////////////////////////////////////////////////////////
#include <iostream>
#include <iomanip>
#include <fstream>

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

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

using namespace std;

int GetRunNumber(MSQLServer &serv, TString date, TString value)
{
    TString query(Form("SELECT %s(fRunNumber) FROM MyMagic.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]);
}

TString GetJoin(TString table)
{
    TString query(Form("left join MyMagic.%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 (startrun==0 && stoprun==0)
    {
        startrun=GetRunNumber(serv, date, "min");
        stoprun=GetRunNumber(serv, date, "max");
    }
    if (startrun<0 || stoprun<0)
    {
        cout << "wrong format of date" << endl;
        return 0;
    }
    if (startrun==0 || stoprun==0)
        return 1;

    TString runcond(Form("AND fRunNumber BETWEEN %d AND %d ", startrun, stoprun));

    TString query="SELECT fExcludedFDAKEY from MyMagic.ExcludedFDA where fExcludedFDAAutomatic='yes'";
    TSQLResult *res = serv.Query(query);
    if (!res)
    {
        cout << "Error - could not do any automatic excludes" << endl;
        return 0;
    }

    TSQLRow *row=0;
    while ((row = res->Next()))
    {
        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", "");

        TString query(Form("SELECT fExcludedFDAImportance from MyMagic.ExcludedFDA where fExcludedFDAKEY=%s ", key.Data()));
        TSQLResult *res = serv.Query(query);
        if (!res)
        {
            cout << "Error" << endl;
            return 0;
        }

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

        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 MyMagic.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 0;
        }

        while ((row2 = res->Next()))
        {
            if (TString((*row2)[1]).IsNull() || atoi((*row2)[1])>newimp)
            {
                TString query(Form("UPDATE MyMagic.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 0;
                }
                delete res;
                continue;
            }
            cout << "run#: " << (*row2)[0] << " reason for exclusion is still the same" << endl;
        }
        delete res;
    }
    delete res;
    return 1;
}

int doexclusions(TString date="NULL")
{
    return doexclusions(0, 0, date);
}