/* ======================================================================== *\
!
! *
! * 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): Daniela Dorner, 01/2005 <mailto:dorner@astro.uni-wuerzburg.de>
!
!   Copyright: MAGIC Software Development, 2000-2006
!
!
\* ======================================================================== */

/////////////////////////////////////////////////////////////////////////////
//
// getdolist.C
// ===========
//
// This macro is a key part of the automation concept.
// It queries from the database, for which dates/runs/sequences/datasets a
// certain step has to be done and writes the todo-files.
//
// Usage:
//  .x getdolist.C+("table", "column", "date", "listpath")
// The first and second argument give the table and column in the database
// and specify like this the primary (date/run/sequenc/dataset) and the
// step.
// The third argument specifies the date, for which the macro has to be
// executed. This is currently not used in the automatic analysis. It was
// forseen to give the possibility to process the data night by night. By
// giving date='NULL' the macro is executed for all dates.
// The forth argument gives the path, where teh todo-file is stored.
//
// The macro needs apart from sql.rc the resource file steps.rc
// In this files the interdependencies of the analysis steps are given: To
// execute a certain step other steps have to be executed first and each
// step also influences othere steps - this information is stored in steps.rc
//   example:
// To execute the calibration, all files have to be available on disk (needs).
// And if the calibration is done, fillcallisto and star can be executed
// (influences).
//
// Make sure, that database and password are corretly set in a resource
// file called sql.rc and the resource file is found.
//
// Returns 0 in case of failure and 1 in case of success.
//
/////////////////////////////////////////////////////////////////////////////

#include <iostream>
#include <iomanip>
#include <fstream>

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

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

using namespace std;


int getdolist(TString table, TString column, TString date, TString listpath)
{
    TEnv env("sql.rc");

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

    TEnv rc("steps.rc");

    //read in needs
    TString needs  = rc.GetValue(table+"."+column+".Needs", "");
    cout << "Needs: " << needs  << endl;

    TList l;
    while (!needs.IsNull())
    {
        needs = needs.Strip(TString::kBoth);

        Int_t idx = needs.First(' ');
        if (idx<0)
            idx = needs.Length();

        TString need = needs(0, idx);
        needs.Remove(0, idx);
        l.Add(new TObjString(need));
    }

    //build query
    // this forms a query like e.g.
    //SELECT SequenceProcessStatus.fSequenceFirst FROM SequenceProcessStatus
    // WHERE ISNULL(fCallisto)
    // AND NOT ISNULL(fAllFilesAvail) AND NOT ISNULL(fSequenceFileWritten)
    //which would query all sequences for which Callisto has to be done.
    TString query(Form("SELECT %s.%s FROM %s",
                       table.Data(), rc.GetValue(table+".Primary", ""),
                       table.Data()));

    //get joins
    //if a date is given, the tables, which contain the information about the
    // date (given in step.rc as TimerTable), have to be joined
    if (date!="NULL" && rc.GetValue(table+".TimerTable", "")!="")
        query+=Form(" left join %s on %s.%s=%s.%s ",
                    rc.GetValue(table+".TimerTable", ""), table.Data(),
                    rc.GetValue(table+".Primary", ""),
                    rc.GetValue(table+".TimerTable", ""),
                    rc.GetValue(table+".Primary", ""));
    query+=Form(" WHERE ISNULL(%s) AND ISNULL(fReturnCode) AND ISNULL(fFailedCode) AND ISNULL(fFailedCodeAdd) AND ISNULL(fStartTime) AND ISNULL(fFailedTime) ", column.Data());

    TIter Next(&l);
    TObject *o=0;
    while ((o=Next()))
        query+=Form(" AND NOT ISNULL(%s)", o->GetName());

    //if a date is given another condition is added to the query
    if (date!="NULL")
    {
        if (rc.GetValue(table+".TimerTable", "")!="")
        {
            TString day=date+" 13:00:00";
            query+=Form(" AND (fRunStart>ADDDATE(\"%s\", INTERVAL -1 DAY) AND fRunStart<\"%s\")",
                        day.Data(), day.Data());
        }
        else
            query+=Form(" AND %s=%s ", rc.GetValue(table+".Primary", ""), date.Data());
    }

    //to process the newest data first, the results are ordered descending by the primary
    query+=Form(" ORDER BY %s.%s DESC ", table.Data(), rc.GetValue(table+".Primary", ""));

    //this part of the query is needed, if there are more than 512 results and single
    //todo files are written, as shell scripts have a limitation for arrays
    if ((table=="SequenceProcessStatus" && column=="fCallisto")      ||
        (table=="SequenceProcessStatus" && column=="fStar")          ||
        (table=="RunProcessStatus"      && column=="fDataCheckDone"))
        query+="LIMIT 0, 500";

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

    TSQLResult *res = serv.Query(query);
    if (!res)
    {
        cout << "ERROR - Query failed: " << query << endl;
        return 0;
    }


    //write todo-file
    //depending on the kind of step and primary, this looks different
    //for steps, which do not take much time and can thus be executed
    // for all data at once (e.g. buildsequenceentries) only one todo
    // file is written
    //for steps, which take quite long for one primary (e.g. calibration)
    // or of which a large number has to be executed (e.g. datacheck)
    // one todo-file per primary is written
    TString filename;
    TSQLRow *row=0;

    if ((table=="SequenceProcessStatus" && column=="fCallisto")      ||
        (table=="SequenceProcessStatus" && column=="fStar")          ||
        (table=="RunProcessStatus"      && column=="fDataCheckDone") ||
        (table=="DataSetProcessStatus"  && column=="fGanymed"))
    {
        while ((row = res->Next()))
        {
            filename=Form("%s/ToDo-%s-%s-%s.txt", listpath.Data(), table.Data(), column.Data(), (*row)[0]);
            //remove file, if it is already existing
            // this is needed, that the same step is not executed several times
            gSystem->Unlink(filename);
            ofstream fout(filename, ios::app);
            if (!fout)
            {
                cout << "ERROR - Cannot open file " << filename << endl;
                return 0;
            }
            fout << (*row)[0] << endl;
        }
    }
    else
    {
        filename=Form("%s/ToDo-%s-%s.txt", listpath.Data(), table.Data(), column.Data());
        ofstream fout(filename, ios::app);
        if (!fout)
        {
            cout << "ERROR - Cannot open file " << filename << endl;
            return 0;
        }

        while ((row = res->Next()))
            fout << (*row)[0] << endl;
    }

    delete res;

    return 1;
}