//*-- AUTHOR : Ilse Koenig
//*-- Created : 26/11/2004 by I.Koenig

//////////////////////////////////////////////////////////////////////////////
// FairOraInfo
//
// Class for additional information from database Oracle
// (uses the Oracle C/C++ precompiler)
// Functions in this class are not used by the analysis but can be called
// by the user on the ROOT interpreter to get additional information from
// Oracle tables.
//////////////////////////////////////////////////////////////////////////////

using namespace std;
#include "FairOraInfo.h"
#include "FairOraConn.h"
#include "FairOraRunInfo.h"
#include <iostream>
#include <iomanip>

#define SQLCA_STORAGE_CLASS extern
#define ORACA_STORAGE_CLASS extern

// Oracle communication area
#include <oraca.h>
// Include the SQL Communications Area
#include <sqlca.h>
 
ClassImp(FairOraInfo)

#define NMAX_INFO 2000
 
void FairOraInfo::showSqlError(char* f) {
  // shows SQL error messages
  Error(f,"\n%s",sqlca.sqlerrm.sqlerrmc);
}


void FairOraInfo::showRunStart(Int_t run) {
  // prints the start date and time of given run
  EXEC SQL BEGIN DECLARE SECTION;
    int id; 
    char runDate[21];
    short runDate_Ind=-1;
  EXEC SQL END DECLARE SECTION;
  id=run;
  EXEC SQL WHENEVER SQLERROR DO
    showSqlError("showRunStart(Int_t)");
  EXEC SQL WHENEVER NOT FOUND GOTO notfound;
  EXEC SQL SELECT TO_CHAR(run_start,'DD-MON-YYYY HH24:MI:SS')
           INTO :runDate INDICATOR :runDate_Ind
           FROM hanal.simul_ref_run
           WHERE run_id = :id;
  if (runDate_Ind>=0) cout<<"Run "<<run<<" started at "<<runDate<<'\n';
  else cout << "no Run_Start for run "<<run<<'\n';
  return;
notfound:
  cout << "Run not found" <<endl;
  return;
}

Int_t FairOraInfo::getRunId(Text_t* filename) {
  // returns the run id of a daq file
  EXEC SQL BEGIN DECLARE SECTION;
    char* runname;
    int run;
  EXEC SQL END DECLARE SECTION;
  runname=filename;
  EXEC SQL WHENEVER SQLERROR DO
    showSqlError("getRunId(Text_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO notfound;
  EXEC SQL SELECT run_id INTO :run
           FROM hanal.simul_ref_run
           WHERE LOWER(ref_run_name) = LOWER(:runname);
  return run;
notfound:
  Error("getRunId(Text_t*)","No such file %s",filename);
  return -1;
}


TList* FairOraInfo::getListOfRuns(const char* projectname,
                               const char* startAt, const char* endAt) {
  // returns the list of simulation reference runs (only last generation)
  //   for the specifies project and range
  // accepts dates (format DD-MON-YYYY HH24:MI:SS), filenames and run ids
  if (strlen(projectname)==0) return 0;
  TString pName(projectname);
  pName.ToUpper();
  TString rangeStart;
  TString rangeEnd;
  Bool_t rc=kTRUE;
  if (strlen(startAt)!=0) rc=getRefRunStart(startAt,rangeStart);
  else rangeStart="01-JAN-1990 00:00:00";
  if (!rc) return 0;
  if (strlen(endAt)!=0) rc=getRefRunStart(endAt,rangeEnd);
  else rangeEnd="01-JAN-4000 00:00:00";
  if (!rc) return 0;
  EXEC SQL BEGIN DECLARE SECTION;
    char* project;
    char* pstart;
    char* pend;
    struct {
      int     rid[NMAX_INFO];
      varchar rname[NMAX_INFO][81];
      char    ts[NMAX_INFO][21];
      char    te[NMAX_INFO][21];
    } refruns;
    struct {
      short rid_Ind[NMAX_INFO];
      short rname_Ind[NMAX_INFO];
      short ts_Ind[NMAX_INFO];
      short te_Ind[NMAX_INFO];
    } refruns_Ind;
  EXEC SQL END DECLARE SECTION;
  project=(char*)projectname;
  pstart=(char*)rangeStart.Data();
  pend=(char*)rangeEnd.Data();
  Int_t nTot=0;
  Int_t nLast=0;
  TList* runsList=new TList();
  EXEC SQL WHENEVER SQLERROR GOTO errorfound;
  EXEC SQL WHENEVER NOT FOUND continue;
  EXEC SQL DECLARE refruns_cursor CURSOR FOR
           SELECT ref_run_id, ref_run_name,
                  TO_CHAR(run_start,'DD-MON-YYYY HH24:MI:SS'),
                  TO_CHAR(run_stop,'DD-MON-YYYY HH24:MI:SS')
           FROM hanal.simul_project p,
                hanal.simul_ref_run rr
           WHERE upper(project_id)= :project
             AND p.project_id = rr.project_id
             AND run_start BETWEEN TO_DATE(:pstart,'DD-MON-YYYY HH24:MI:SS')
                           AND TO_DATE(:pend,'DD-MON-YYYY HH24:MI:SS')
             AND rr.generation = ( SELECT MAX(generation)
                                   FROM hanal.simul_ref_run
                                   WHERE subproject_id = p.subproject_id )
           ORDER BY ref_run_id;
  EXEC SQL OPEN refruns_cursor;
  do {
    EXEC SQL FETCH refruns_cursor INTO :refruns INDICATOR :refruns_Ind;
    nLast=sqlca.sqlerrd[2]-nTot;
    for (Int_t i=0;i<nLast;i++) {
      refruns.rname[i].arr[refruns.rname[i].len]='\0';
      FairOraRunInfo* p=new FairOraRunInfo((char*)(refruns.rname[i].arr));
      p->setRunId(refruns.rid[i]);
      refruns.ts[i][20]='\0';
      p->setStartTime((char*)(refruns.ts[i]));
      if (refruns_Ind.te_Ind[i]!=-1) {
        refruns.te[i][20]='\0';
        p->setEndTime((char*)(refruns.te[i]));
      }
      runsList->Add(p);
      nTot++;
    }
  } while (nLast==NMAX_INFO);
  EXEC SQL CLOSE refruns_cursor;
  cout<<"*** Total number of runs: "<<nTot<<endl;
  return runsList;
errorfound:
  pConn->showSqlError("getListOfRuns");
  if (runsList) {
    runsList->Delete();
    delete runsList;
    runsList=0;
  }
  return 0;
}


Bool_t FairOraInfo::getRefRunStart(const char* startAt,TString& pTime) {
  // gets the run start for a simulation reference run
  TString s(startAt);
  EXEC SQL BEGIN DECLARE SECTION;
    char* pfile;
    int   pid;    
    char  ts[21];
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR GOTO errorfound;
  EXEC SQL WHENEVER NOT FOUND GOTO notfound;
  pfile=0;
  pid=-1;
  if (s.Contains("-")) {
    pTime=startAt;
  } else {
    char c=startAt[0];
    if (c=='1'||c=='2'||c=='3'||c=='4'||c=='5'||c=='6'||c=='7'||c=='8'||c=='9') {
      pid=atoi(startAt);
      EXEC SQL SELECT TO_CHAR(run_start,'DD-MON-YYYY HH24:MI:SS')
               INTO :ts
               FROM hanal.simul_ref_run
               WHERE ref_run_id = :pid;
    } else {
      pfile=(char*)startAt;
      EXEC SQL SELECT TO_CHAR(run_start,'DD-MON-YYYY HH24:MI:SS')
               INTO :ts
               FROM hanal.simul_ref_run
               WHERE ref_run_name = :pfile;
    }
    ts[20]='\0';    
    pTime=ts;
  }
  return kTRUE;
errorfound:
  pConn->showSqlError("getRefRunStart");
  return kFALSE;
notfound:
  Error("getRefRunStart","Simulation reference run not found for %s",startAt);
  return kFALSE;
}