//
// C/C++ headers
//
#include <iostream>
#include <iomanip>
#include <fstream>
#include <sstream>
//
// ROOT headers
//
#include <TString.h>
#include <TTimeStamp.h>
#include <TSQLServer.h>
#include <TFile.h>
#include <TSystem.h>
//
// Detector's package headers
//
#include <GLTables.h>
//
using namespace std;
//
//
//
#include <YodaProfilerVerl2.h>
//
// Usage subroutine
//
void r2d2usage(){
  printf("\nUsage:\n");
  printf("\n R2-D2 [ options ]\n");
  printf("\n Options are:\n\n");
  printf(" --version        print informations about compilation and exit\n");
  printf(" -h | --help      print this help and exit \n");
  printf(" -v | --verbose   be verbose [default]\n");
  printf(" -n | --neat      used with '-filename' returns only the run ID number\n");
  printf(" -idRun run       ID_RUN: ID number of the run \n");
  printf(" -filename file   output yoda filename \n");
  printf(" -l2filename file output amidala filename \n");
  printf(" -host            name of the DB host [default = $PAM_DBHOST or mysql://localhost/pamelaprod]\n");
  printf(" -user            username for the DB connection [default = $PAM_DBUSER or \"anonymous\"] \n");
  printf(" -psw             password for the DB connection [default = $PAM_DBPSW or \"\"]\n");
  printf(" -deps file       shows dependencies of the given file\n");
  printf(" -splitat file    shows dependenices of the given file and the following files (given by -nsplit)\n");
  printf(" -nsplit number   number of files to be shown by -splitat [default 20]\n");
  printf(" -tzone timezone  the time zone: UTC,GMT,MSK,MSD,CET,CEST are accepted \n");
  printf(" -convert dbtime  convert the dbtime given in seconds (from the DB) to a string\n");
  printf(" -runat date      returns run number which contains the given date,\n");
  printf("                  for date use the SQL format \"yyyy-mm-dd hh:mm:ss\" \n");
  printf(" -runatDB time    returns run number which contains the given DB time\n");
  printf(" -tsfile file     yoda filename for the time sync (to be used with -obt)\n");
  printf(" -obt OBT         OBT in ms returns a date (to be used with -tsfile)\n");
  printf(" -getRTime OBT    OBT in ms returns Resurs time (to be used with -tsfile)\n");
  printf(" -dumpTLEfor date save into file tle.txt the TLE for the given date,\n");
  printf("                  for date use the SQL format \"yyyy-mm-dd hh:mm:ss\" \n");
  printf("\nExamples: \n");
  printf("      R2-D2 -idRun 1085 \n");
  printf("      R2-D2 -filename DW_050208_00900.root \n");
  printf("      R2-D2 -idRun 1085 -filename DW_050208_00900.root \n\n");
};
//
// Here the main
//
int main(int numinp, char *inps[]){
  //
  // Variables booking
  //
  TString message;
  Int_t error = 0;
  //
  UInt_t run = 0ULL;
  //
  TString filename = "";
  TString l2filename = "";
  TString splitat = "";
  UInt_t nsplit = 20;
  //
  TSQLServer *dbc = 0;
  TString host = "mysql://localhost/pamelaprod";
  TString user = "anonymous";
  TString psw = "";
  //
  const char *pamdbhost=gSystem->Getenv("PAM_DBHOST");
  const char *pamdbuser=gSystem->Getenv("PAM_DBUSER");
  const char *pamdbpsw=gSystem->Getenv("PAM_DBPSW");
  if ( !pamdbhost ) pamdbhost = "";
  if ( !pamdbuser ) pamdbuser = "";
  if ( !pamdbpsw ) pamdbpsw = "";
  if ( strcmp(pamdbhost,"") ) host = pamdbhost;
  if ( strcmp(pamdbuser,"") ) user = pamdbuser;
  if ( strcmp(pamdbpsw,"") ) psw = pamdbpsw;
  //
  //  printf(" host %s user %s psw %s \n",host.Data(),user.Data(),psw.Data());
  //
  TString tzone = "UTC";
  TString runtime = "1970-01-01 00:00:00";
  TString tletime = "1970-01-01 00:00:00";
  UInt_t dbti = 0;
  Bool_t convert = false;
  Bool_t convres = false;
  Bool_t ruti = false;
  Bool_t dtle = false;
  Bool_t ruti2 = false;
  Bool_t convobt = false;
  Bool_t convobtts = false;
  Bool_t neat = false;
  //
  UInt_t runtime2 = 0;
  UInt_t obt = 0;
  UInt_t res = 0;
  TString tsfile = "";
  //
  TSQLResult *pResult;
  TSQLRow *Row;
  TSQLResult *pResult2 = 0;
  TSQLRow *Row2;
  int t;
  int r;
  stringstream myquery;
  //
  // Checking input parameters
  //
  Int_t i = 0;
  if ( numinp > 1 ){
    while ( i < numinp ){
      if ( !strcmp(inps[i],"--version") ){
	YodaProfilerInfo(true);
	exit(0);
      };
      if ( !strcmp(inps[i],"-h") || !strcmp(inps[i],"--help") ){
	r2d2usage();
	exit(0);
      };
      if ( !strcmp(inps[i],"-n") || !strcmp(inps[i],"--neat") ){
	neat = true;
      };
      if ( !strcmp(inps[i],"-idRun") ) {
	if ( numinp-1 < i+1 ) {
	  r2d2usage();
	  exit(-3);
	};
	run = (UInt_t)atoll(inps[i+1]);	
      };
      if ( !strcmp(inps[i],"-filename") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	filename = (TString)inps[i+1];	
      };
      if ( !strcmp(inps[i],"-splitat") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	splitat = (TString)inps[i+1];	
      };
      //
      if ( !strcmp(inps[i],"-nsplit") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	nsplit = (UInt_t)atoll(inps[i+1]);	
      };
      if ( !strcmp(inps[i],"-deps") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	nsplit = 1;
	splitat = (TString)inps[i+1];	
      };
      if ( !strcmp(inps[i],"-l2filename") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	l2filename = (TString)inps[i+1];	
      };
      if ( !strcmp(inps[i],"-host") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	host = (TString)inps[i+1];	
      };
      if ( !strcmp(inps[i],"-user") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	user = (TString)inps[i+1];	
      };
      if ( !strcmp(inps[i],"-psw") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	psw = (TString)inps[i+1];	
      };
      //
      if ( !strcmp(inps[i],"-tsfile") ) {
	convobtts = true;
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	tsfile = (TString)inps[i+1];	
      };
      //
      if ( !strcmp(inps[i],"-obt") ) {
	convobt = true;
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	obt = (UInt_t)atoll(inps[i+1]);	
      };
      //
     if ( !strcmp(inps[i],"-getRTime") ) {
	convres = true;
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	res = (UInt_t)atoll(inps[i+1]);	
      };
      //
      //
      if ( !strcmp(inps[i],"-tzone") ) {
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	tzone = (TString)inps[i+1];	
      };
      //
      if ( !strcmp(inps[i],"-convert") ) {
	convert = true;
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	dbti = (UInt_t)atoll(inps[i+1]);	
      };
      //
      if ( !strcmp(inps[i],"-runat") ) {
	ruti = true;
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	runtime = (TString)inps[i+1];	
      };
      //
      if ( !strcmp(inps[i],"-dumpTLEfor") ) {
	dtle = true;
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	tletime = (TString)inps[i+1];	
      };
      //
      //
      if ( !strcmp(inps[i],"-runatDB") ) {
	ruti2 = true;
	if ( numinp-1 < i+1 ){
	  r2d2usage();
	  exit(-3);
	};
	runtime2 = (UInt_t)atoll(inps[i+1]);	
      };
      //
      i++;
    };
    //
  } else {
    //
    // no input parameters exit with error, we need at least the run id.
    //
    r2d2usage();
    exit(-2);
  };
  //
  // Connect to the DB
  //
  dbc = TSQLServer::Connect(host.Data(),user.Data(),psw.Data());
  if( !dbc ) throw -2;    
  //
  bool connect = dbc->IsConnected();
  //
  if( !connect ){
    printf(" Error, not connected to DB\n");
    exit(-1);
  };    
  //
  myquery.str("");
  myquery << "SET time_zone='+0:00';";
  delete dbc->Query(myquery.str().c_str());
  delete dbc->Query("SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';");
  //
  GL_ROOT *glroot = new GL_ROOT();
  GL_RUN *glrun = new GL_RUN();
  GL_TIMESYNC *dbtime = new GL_TIMESYNC();
  UInt_t nr = 0;
  UInt_t rlist[500];
  //
  // At which date correspond the DB time "dbti"?
  //
  if ( convert ){
    printf("\n DB time %u is %s %s \n",dbti,dbtime->ConvertTime(tzone,dbti).Data(),tzone.Data());
  };
  //
  // convert OBT to date
  //
  if ( convobt && convobtts ){
    UInt_t id = 0;
    myquery.str("");
    myquery << "select ";
    myquery << " ID"; 
    myquery << " from GL_ROOT where NAME=\"" << tsfile.Data() << "\";";    
    pResult = dbc->Query(myquery.str().c_str());
    if ( pResult ){
      Row = pResult->Next();      
      if ( Row ){
	id = (UInt_t)atoll(Row->GetField(0));
	delete pResult;       
	GL_TIMESYNC *ctime = new GL_TIMESYNC(id,"ID",dbc);
	UInt_t abtime = ctime->DBabsTime(obt);
	TString thetime = dbtime->ConvertTime(tzone,abtime);
	printf("\n OBT %u in the file %s corresponds to DBtime %u and date %s %s \n",obt,tsfile.Data(),abtime,thetime.Data(),tzone.Data());
	delete ctime;
      };
    };
  };
  if ( (convobt && !convobtts) ){
    printf("\n To convert a OBT to a date you must provide both OBT and file to be used for time sync \n");
  };
  //
  // convert OBT to Resurs seconds
  //
  if ( convres && convobtts ){
    UInt_t id = 0;
    myquery.str("");
    myquery << "select ";
    myquery << " ID"; 
    myquery << " from GL_ROOT where NAME=\"" << tsfile.Data() << "\";";    
    pResult = dbc->Query(myquery.str().c_str());
    if ( pResult ){
      Row = pResult->Next();      
      if ( Row ){
	id = (UInt_t)atoll(Row->GetField(0));
	delete pResult;       
	GL_TIMESYNC *ctime = new GL_TIMESYNC(id,"ID",dbc);
	UInt_t restime = ctime->ResursTime(res);
	//UInt_t abtime = ctime->DBabsTime(obt);
	//
	//	TString thetime = dbtime->ConvertTime(tzone,abtime);
	printf("\n OBT %u in the file %s corresponds to Resurs time %u  \n",res,tsfile.Data(),restime);
	delete ctime;
      };
    };
  };
  if ( (convres && !convobtts) ){
    printf("\n To convert a OBT to the Resurs time you must provide both OBT and file to be used for time sync \n");
  };
  //
  // Which run contains the date "runtime"?
  //
  if ( ruti ){
    //
    TDatime ti = TDatime(runtime.Data());
    //
    TTimeStamp *time = new TTimeStamp((UInt_t)ti.GetYear(),(UInt_t)ti.GetMonth(),(UInt_t)ti.GetDay(),(UInt_t)ti.GetHour(),(UInt_t)ti.GetMinute(),(UInt_t)ti.GetSecond(),0,true,0);
    //
    UInt_t dbti = time->GetSec();
    //
    TString thetime = dbtime->UnConvertTime(tzone,dbti);
    //
    ti = TDatime(thetime.Data());
    TTimeStamp *time2 = new TTimeStamp((UInt_t)ti.GetYear(),(UInt_t)ti.GetMonth(),(UInt_t)ti.GetDay(),(UInt_t)ti.GetHour(),(UInt_t)ti.GetMinute(),(UInt_t)ti.GetSecond(),0,true,0);
    //
    UInt_t mytime = time2->GetSec();
    //
    Bool_t found = false;
    //
    myquery.str("");
    myquery << "select ";
    myquery << " ID "; 
    myquery << " from GL_RUN where RUNHEADER_TIME<=" << mytime << " AND "
	    << " RUNTRAILER_TIME>=" << mytime  << " ;";
    //    printf("myquery is %s \n",myquery.str().c_str());
    pResult = dbc->Query(myquery.str().c_str());
    for( r=0; r < 1000; r++){ 
      Row = pResult->Next();      
      if( Row == NULL ) break;
      found = true;
      printf("\n Date %s %s (DB time %u ) is contained in run %u \n",runtime.Data(),tzone.Data(),mytime,(UInt_t)atoll(Row->GetField(0)));
    };    
    myquery.str("");
    myquery << "select ";
    myquery << " ID "; 
    myquery << " from GL_RUN_TRASH where BELONGED_TO='GL_RUN' AND RUNHEADER_TIME<=" << mytime << " AND "
	    << " RUNTRAILER_TIME>=" << mytime  << " ;";
    //    printf("myquery is %s \n",myquery.str().c_str());
    pResult = dbc->Query(myquery.str().c_str());
    for( r=0; r < 1000; r++){ 
      Row = pResult->Next();      
      if( Row == NULL ) break;
      printf("\n Date %s %s (DB time %u ) is contained in run %u in the GL_RUN_TRASH table \n",runtime.Data(),tzone.Data(),mytime,(UInt_t)atoll(Row->GetField(0)));
      found = true;
    };    
    //
    if ( !found ){
      printf("\n No run contains date %s %s (DB time %u )\n",runtime.Data(),tzone.Data(),mytime);
    };
    //
  };
  //
  // Which tle must be used for the date "tletime"?
  //
  if ( dtle ){
    //
    //
    TDatime ti = TDatime(tletime.Data());
    //
    TTimeStamp *time = new TTimeStamp((UInt_t)ti.GetYear(),(UInt_t)ti.GetMonth(),(UInt_t)ti.GetDay(),(UInt_t)ti.GetHour(),(UInt_t)ti.GetMinute(),(UInt_t)ti.GetSecond(),0,true,0);
    //
    UInt_t dbti = time->GetSec();
    //
    TString thetime = dbtime->UnConvertTime(tzone,dbti);
    //
    ti = TDatime(thetime.Data());
    TTimeStamp *time2 = new TTimeStamp((UInt_t)ti.GetYear(),(UInt_t)ti.GetMonth(),(UInt_t)ti.GetDay(),(UInt_t)ti.GetHour(),(UInt_t)ti.GetMinute(),(UInt_t)ti.GetSecond(),0,true,0);
    //
    UInt_t mytime = time2->GetSec();
    //
    myquery.str("");
    myquery << " select ID,TLE1,TLE2,TLE3 from GL_TLE where FROM_TIME<='" << time2->AsString("s") << "' ORDER BY FROM_TIME DESC LIMIT 1;";
    //    myquery << " from GL_TLE where FROM_TIME>=" << mytime << " ORDER BY FROM_TIME ASC LIMIT 1;";
    //    printf("myquery is %s \n",myquery.str().c_str());
    pResult = dbc->Query(myquery.str().c_str());
    Row = pResult->Next();      
    if ( !Row ){
      printf("\n No TLE in the DB for date %s %s (DB time %u )\n",tletime.Data(),tzone.Data(),mytime);
    };
    printf("\n Date %s %s (DB time %u ) is contained in TLE %u \n",tletime.Data(),tzone.Data(),mytime,(UInt_t)atoll(Row->GetField(0)));
    printf("\n%s\n",Row->GetField(1));
    printf("%s\n",Row->GetField(2));
    printf("%s\n",Row->GetField(3));
    //
    FILE *tlefile;
    tlefile = fopen("tle.txt","w"); 
    fprintf(tlefile,"%s\n",Row->GetField(1));
    fprintf(tlefile,"%s\n",Row->GetField(2));
    fprintf(tlefile,"%s\n",Row->GetField(3));
    fclose (tlefile);
    //
    printf("\n TLE has been dumped in file tle.txt \n");
  };
  //
  // Which run contains the dbtime "runtime2"?
  //
  if ( ruti2 ){
    //
    UInt_t mytime = runtime2;
    //
    Bool_t found = false;
    myquery.str("");
    myquery << "select ";
    myquery << " ID "; 
    myquery << " from GL_RUN where RUNHEADER_TIME<=" << mytime << " AND "
	    << " RUNTRAILER_TIME>=" << mytime  << " ;";
    //    printf("myquery is %s \n",myquery.str().c_str());
    pResult = dbc->Query(myquery.str().c_str());
    for( r=0; r < 1000; r++){ 
      Row = pResult->Next();      
      if( Row == NULL ) break;
      printf("\n DB time %u is contained in run %u \n",mytime,(UInt_t)atoll(Row->GetField(0)));
      found = true;
    };    
    //
    myquery.str("");
    myquery << "select ";
    myquery << " ID "; 
    myquery << " from GL_RUN_TRASH where BELONGED_TO='GL_RUN' AND RUNHEADER_TIME<=" << mytime << " AND "
	    << " RUNTRAILER_TIME>=" << mytime  << " ;";
    //    printf("myquery is %s \n",myquery.str().c_str());
    pResult = dbc->Query(myquery.str().c_str());
    for( r=0; r < 1000; r++){ 
      Row = pResult->Next();      
      if( Row == NULL ) break;
      printf("\n DB time %u is contained in run %u in the GL_RUN_TRASH table \n",mytime,(UInt_t)atoll(Row->GetField(0)));
      found = true;
    };    
    //
    if ( !found ){
      printf("\n No run contains DB time %u \n",mytime);
    };
    //
  };
  //
  // To which file the run "run" belongs?
  //
  if ( run != 0 ){
    Bool_t found = false;
    glrun->Clear();
    error = glrun->Query_GL_RUN(run,dbc);
    glroot->Clear();
    error = glroot->Query_GL_ROOT(glrun->ID_ROOT_L0,dbc);
    if ( glrun->ID_ROOT_L0 ){
      if ( error ){
	printf(" Error querying the DB! \n");
	exit(-4);
      };
      printf("\n Run %u belongs to file %s \n",run,(glroot->PATH+glroot->NAME).Data());
      //filename = glroot->NAME;
      found = true;
    };
    //
    myquery.str("");
    myquery << "select ";
    myquery << " ID,FILENAMEL0,FILENAMEL2 "; 
    myquery << " from GL_RUN_TRASH where BELONGED_TO='GL_RUN' AND ID=" << run << "; ";
    pResult = dbc->Query(myquery.str().c_str());
    for( r=0; r < 1000; r++){ 
      Row = pResult->Next();      
      if( Row == NULL ) break;
      printf("\n RUN %u has been deleted and now is contained in the GL_RUN_TRASH table \n",run);
      printf("\n RUN %u belonged to L0 file %s and L2 file %s \n",run,Row->GetField(1),Row->GetField(2));
      found = true;
    };    
    //
    if ( !found ) printf("\n No run with ID=%u in the DB!\n",run);
  };
  //
  // Which runs are contained in the file "filename"?
  //
  if ( strcmp(filename.Data(),"") ){
    // ----------------
    Bool_t found = false;
    Int_t ID = 0;
    Int_t ID_RAW = 0;
    //    
    const char *rawpath = "";
    const char *rawname = "";
    //
    myquery.str("");
    myquery << "select ";
    myquery << " ID"; 
    myquery << ",ID_RAW"; 
    myquery << ",PATH"; 
    myquery << ",NAME"; 
    myquery << " from GL_ROOT where NAME=\"" << filename.Data() << "\";";    
    pResult = dbc->Query(myquery.str().c_str());
    for( r=0; r < 1000; r++){ 
      Row = pResult->Next();      
      if( Row == NULL ) break;
      for( t = 0; t < pResult->GetFieldCount(); t++){ 
	if(t==0) ID     = atoi(Row->GetField(t));
	if(t==1) ID_RAW = atoi(Row->GetField(t));
      };
    };
    delete pResult;   
    if ( !ID && !ID_RAW ){
      if ( !neat ) printf("\n No file with name %s in the DB!\n",filename.Data());
    } else {
      myquery.str("");
      myquery << "select ";
      myquery << " ID,RUNHEADER_TIME,RUNTRAILER_TIME,NEVENTS "; 
      myquery << " from GL_RUN where ID_ROOT_L0=" << ID << ";";    
      pResult = dbc->Query(myquery.str().c_str());
      for( r=0; r < 1000; r++){ 
	Row = pResult->Next();      
	if( Row == NULL ) break;
	found = true;
	if ( !r && !neat ) printf("\n File %s contains the following runs: \n\n",filename.Data());
	TString UTC=tzone.Data();
	if ( !neat ) printf(" => ID = %u --> the run started at %s %s ended at %s %s NEV = %u \n\n",(UInt_t)atoll(Row->GetField(0)),dbtime->ConvertTime(UTC,(UInt_t)atoll(Row->GetField(1))).Data(),tzone.Data(),dbtime->ConvertTime(UTC,(UInt_t)atoll(Row->GetField(2))).Data(),tzone.Data(),(UInt_t)atoll(Row->GetField(3)));
	if ( neat ) printf("%u\n",(UInt_t)atoll(Row->GetField(0)));
      };
      delete pResult;   
      myquery.str("");
      myquery << "select ";
      myquery << " ID,RUNHEADER_TIME,RUNTRAILER_TIME "; 
      myquery << " from GL_RUN_TRASH where BELONGED_TO='GL_RUN' AND FILENAMEL0='" << filename.Data() << "' ;";    
      pResult = dbc->Query(myquery.str().c_str());
      for( r=0; r < 1000; r++){ 
	Row = pResult->Next();      
	if( Row == NULL ) break;
	if ( !r && !neat ) printf("\n File %s contains the following DELETED runs: \n\n",filename.Data());
	TString UTC=tzone.Data();
	if ( !neat ) printf(" => ID = %i --> the run started at %s %s ended at %s %s \n\n",(UInt_t)atoll(Row->GetField(0)),dbtime->ConvertTime(UTC,(UInt_t)atoll(Row->GetField(1))).Data(),tzone.Data(),dbtime->ConvertTime(UTC,(UInt_t)atoll(Row->GetField(2))).Data(),tzone.Data());
      };
      //
      if ( !found ){
	if ( !neat ) printf("\n No run associated to the file %s \n",filename.Data());
      };
      myquery.str("");
      myquery << "select ";
      myquery << " PATH,NAME"; 
      myquery << " from GL_RAW where ID=" << ID_RAW << ";";    
      pResult = dbc->Query(myquery.str().c_str());
      for( r=0; r < 1000; r++){ 
	Row = pResult->Next();      
	if( Row == NULL ) break;
	for( t = 0; t < pResult->GetFieldCount(); t++){ 
	  if(t==0) rawpath = Row->GetField(t);
	  if(t==1) rawname = Row->GetField(t);
	};
      };
      delete pResult;   
      if ( !neat ) printf("\n File %s belongs to raw data file %s/%s \n",filename.Data(),rawpath,rawname);
    };
  };  
  //
  // Which runs are contained in the file "l2filename"?
  //
  if ( strcmp(l2filename.Data(),"") ){
    // ----------------
    //    Bool_t found = false;
    Int_t ID = 0;
    //    Int_t ID_RAW = 0;
    //    
    const char *rawpath = "";
    const char *rawname = "";
    //
    myquery.str("");
    myquery << "select ";
    myquery << " ID"; 
    myquery << ",ID_RAW"; 
    myquery << ",PATH"; 
    myquery << ",NAME"; 
    myquery << " from GL_ROOT where NAME=\"" << l2filename.Data() << "\" order by INSERT_TIME desc limit 1;";    
    pResult = dbc->Query(myquery.str().c_str());
    for( r=0; r < 1000; r++){ 
      Row = pResult->Next();      
      if( Row == NULL ) break;
      for( t = 0; t < pResult->GetFieldCount(); t++){ 
	if(t==0) ID     = atoi(Row->GetField(t));
        //	if(t==1) ID_RAW = atoi(Row->GetField(t));
      };
    };
    delete pResult;   
    if ( !ID ){
      printf("\n No file with name %s in the DB!\n",l2filename.Data());
    } else {
      nr = 0;
      rlist[0] = 0;
      myquery.str("");
      myquery << "select ";
      myquery << " ID,ID_ROOT_L0 "; 
      myquery << " from GL_RUN where ID_ROOT_L2=" << ID << ";";    
      pResult = dbc->Query(myquery.str().c_str());
      for( r=0; r < pResult->GetRowCount(); r++){ 
	Row = pResult->Next();      
	if( Row == NULL ) break;
        //	found = true;
	if ( !r ) printf("\n File %s contains the following runs: \n\n",l2filename.Data());
	printf(" %u ",(UInt_t)atoll(Row->GetField(0)));
	if ( r < (pResult->GetRowCount()-1) ){
	  printf("-");
	} else {
	  printf("\n\n");
	};
	if ( (UInt_t)atoll(Row->GetField(1)) != rlist[nr] ){
	  nr++;
	  rlist[nr] = (UInt_t)atoll(Row->GetField(1));
	};
      };
      delete pResult;   
      //
      printf("\n File %s contains runs from L0 files: \n\n",l2filename.Data());
      for (UInt_t l=1; l<nr+1; l++){
	myquery.str("");
	myquery << "select ";
	myquery << " PATH,NAME"; 
	myquery << " from GL_ROOT where ID=" << rlist[l] << ";";    
	pResult = dbc->Query(myquery.str().c_str());
	for( r=0; r < pResult->GetRowCount(); r++){ 
	  Row = pResult->Next();      
	  if( Row == NULL ) break;
	  for( t = 0; t < pResult->GetFieldCount(); t++){ 
	    if(t==0) rawpath = Row->GetField(t);
	    if(t==1) rawname = Row->GetField(t);
	};
	  printf(" %s/%s \n",rawpath,rawname);
	};
	delete pResult;   
      };
    };
  };  
  //
  //
  // Show relationship between files around file "splitat"
  //
  if ( strcmp(splitat.Data(),"") ){
    // ----------------
    Int_t ID = 0;
    Int_t IDR = 0;
    TString PATH;
    TString NAME;
    UInt_t atime1 = 0;
    UInt_t atime2 = 0;
    UInt_t minid = 0;
    UInt_t maxid = 0;
    //    
    myquery.str("");
    myquery << " select ID,PATH,NAME from GL_ROOT where NAME>=\"" << splitat.Data() << "\" order by NAME asc limit " << nsplit << ";";    
    //    printf(" myquery is %s \n",myquery.str().c_str());
    pResult = dbc->Query(myquery.str().c_str());
    GL_TIMESYNC *dbtime;
    GL_S4_CALIB *glS4calib;
    GL_ROOT *glroot;
    for( r=0; r < 1000; r++){ 
      Row = pResult->Next();      
      if( Row == NULL ) break;
      printf("\n\n#################################################\n");
      Int_t s=0;
      Int_t t=0;
      Int_t c=0;
      TString *s4files[500];
      TString *tfiles[500];
      TString *cfiles[2000];
      ID     = atoi(Row->GetField(0));
      PATH = Row->GetField(1);
      NAME = Row->GetField(2);
      printf("\n FILE: %s/%s \n",PATH.Data(),NAME.Data());
      myquery.str("");
      myquery << " select ID,RUNHEADER_OBT,RUNTRAILER_OBT from GL_RUN where ID_ROOT_L0=" << ID << " order by ID;";    
      pResult2 = dbc->Query(myquery.str().c_str());
      for( Int_t run=0; run < pResult2->GetRowCount(); run++){ 	
 	Row2 = pResult2->Next();      
 	if( Row2 == NULL ) break;
 	IDR = atoi(Row2->GetField(0));
	if ( run == 0 ) minid = IDR;
	if ( run == pResult2->GetRowCount()-1 ) maxid = IDR;
 	//
 	// here we make queries to DB looking for needed files and print results
 	//
	dbtime = new GL_TIMESYNC(ID,"ID",dbc);
	//
	atime1 = dbtime->DBabsTime((UInt_t)atoll(Row2->GetField(1)));  
	atime2 = dbtime->DBabsTime((UInt_t)atoll(Row2->GetField(2)));  
	//	printf(" atime1 %u atime2 %u field 1 %s field2 %s \n",atime1,atime2,Row2->GetField(1),Row2->GetField(2));
	//
	// S4
	//
	glS4calib = new GL_S4_CALIB();
	glS4calib->Query_GL_S4_CALIB(atime1, dbc);
	glroot = new GL_ROOT();
	glroot->Query_GL_ROOT(glS4calib->ID_ROOT_L0,dbc);
	//
	if ( s > 0 ){
	  Bool_t found = false;
	  for (Int_t g=0; g<s; g++){
	    if ( !strcmp(glroot->NAME.Data(),s4files[g]->Data()) ){
	      found = true;
	    };
	  };
	  if ( !found && strcmp(glroot->NAME.Data(),NAME.Data()) ){
	    s4files[s] = new TString(glroot->NAME.Data());
	    s++;
	  };
	} else {
	  if ( strcmp(glroot->NAME.Data(),NAME.Data()) ){
	    s4files[s] = new TString(glroot->NAME.Data());
	    s++;
	  };
	};
 	//
	delete glS4calib;
	delete dbtime;
	delete glroot;
	//
	// TRACKER
	//
	GL_TRK_CALIB q2;
	q2.Query_GL_TRK_CALIB(atime1,dbc);
	GL_ROOT q3;
	q3.Query_GL_ROOT(q2.ID_ROOT_L0,dbc);
	//
	if ( t > 0 ){
	  Bool_t found = false;
	  for (Int_t gt=0; gt<t; gt++){
	    if ( !strcmp(q3.NAME.Data(),tfiles[gt]->Data()) ){
	      found = true;
	    };
	  };
	  if ( !found && strcmp(q3.NAME.Data(),NAME.Data()) ){
	    tfiles[t] = new TString(q3.NAME.Data());
	    t++;
	  };
	} else {
	  if ( strcmp(q3.NAME.Data(),NAME.Data()) ){
	    tfiles[t] = new TString(q3.NAME.Data());
	    t++;
	  };
	};
 	//
	// CALO
	//
	GL_CALO_CALIB *glcalo = new GL_CALO_CALIB();
	GL_CALOPULSE_CALIB *glp = new GL_CALOPULSE_CALIB();
	GL_ROOT *glroot = new GL_ROOT();  
	//
	for (Int_t sc=0; sc<4; sc++){
	  //
	  UInt_t pampli = 0;
	  glcalo->Query_GL_CALO_CALIB(atime1,pampli,sc,dbc);
	  glroot->Query_GL_ROOT(glcalo->ID_ROOT_L0,dbc);
	  if ( c > 0 ){
	    Bool_t found = false;
	    for (Int_t gt=0; gt<c; gt++){
	      if ( !strcmp(glroot->NAME.Data(),cfiles[gt]->Data()) ){
		found = true;
	      };
	    };
	    if ( !found && strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  } else {
	    if ( strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  };
	  //
	  pampli = 0;
	  glcalo->Query_GL_CALO_CALIB(atime2,pampli,sc,dbc);
	  glroot->Query_GL_ROOT(glcalo->ID_ROOT_L0,dbc);
	  if ( c > 0 ){
	    Bool_t found = false;
	    for (Int_t gt=0; gt<c; gt++){
	      if ( !strcmp(glroot->NAME.Data(),cfiles[gt]->Data()) ){
		found = true;
	      };
	    };
	    if ( !found && strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  } else {
	    if ( strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  };
	  //
	  pampli = 2;
	  glp->Query_GL_CALOPULSE_CALIB(atime1,sc,pampli,dbc);
	  glroot->Query_GL_ROOT(glp->ID_ROOT_L0,dbc);
	  if ( c > 0 ){
	    Bool_t found = false;
	    for (Int_t gt=0; gt<c; gt++){
	      if ( !strcmp(glroot->NAME.Data(),cfiles[gt]->Data()) ){
		found = true;
	      };
	    };
	    if ( !found && strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  } else {
	    if ( strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  };
	  //
	  pampli = 0;
	  glp->Query_GL_CALOPULSE_CALIB(atime1,sc,pampli,dbc);
	  glroot->Query_GL_ROOT(glp->ID_ROOT_L0,dbc);
	  if ( c > 0 ){
	    Bool_t found = false;
	    for (Int_t gt=0; gt<c; gt++){
	      if ( !strcmp(glroot->NAME.Data(),cfiles[gt]->Data()) ){
		found = true;
	      };
	    };
	    if ( !found && strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  } else {
	    if ( strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  };
	  //
	  pampli = 2;
	  glp->Query_GL_CALOPULSE_CALIB(atime2,sc,pampli,dbc);
	  glroot->Query_GL_ROOT(glp->ID_ROOT_L0,dbc);
	  if ( c > 0 ){
	    Bool_t found = false;
	    for (Int_t gt=0; gt<c; gt++){
	      if ( !strcmp(glroot->NAME.Data(),cfiles[gt]->Data()) ){
		found = true;
	      };
	    };
	    if ( !found && strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  } else {
	    if ( strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  };
	  //
	  pampli = 0;
	  glp->Query_GL_CALOPULSE_CALIB(atime2,sc,pampli,dbc);
	  glroot->Query_GL_ROOT(glp->ID_ROOT_L0,dbc);
	  if ( c > 0 ){
	    Bool_t found = false;
	    for (Int_t gt=0; gt<c; gt++){
	      if ( !strcmp(glroot->NAME.Data(),cfiles[gt]->Data()) ){
		found = true;
	      };
	    };
	    if ( !found && strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  } else {
	    if ( strcmp(glroot->NAME.Data(),NAME.Data()) ){
	      cfiles[c] = new TString(glroot->NAME.Data());
	      c++;
	    };
	  };
	  //
	};
      };
      printf(" S4 requires:");
      for (Int_t f=0;f<s; f++){
	printf(" %s",s4files[f]->Data());
	delete s4files[f];
      };      
      printf("\n");
      printf(" Tracker requires:");
      for (Int_t f=0;f<t; f++){
	printf(" %s",tfiles[f]->Data());
	delete tfiles[f];
      };      
      printf("\n");
      printf(" Calorimeter requires:");
      for (Int_t f=0;f<c; f++){
	printf(" %s",cfiles[f]->Data());
	delete cfiles[f];
      };      
      printf("\n\n");
      printf(" Minimum ID number in the file: %u \n",minid);
      printf(" Maximum ID number in the file: %u \n",maxid);
      printf("\n#################################################\n");
      //
    };
    delete pResult;   
    if ( pResult2 ) delete pResult2;   
  };  
  //
  // Close the DB connection
  //
  if ( dbc ){
    dbc->Close();
    delete dbc;
    dbc = 0;
  }
  //
  if ( !neat ) printf("\n");
  //
  exit(0);
}