YodaProfiler/src/PamelaDBOperations.cpp

//
#include <iomanip>
#include <sstream>
//
#include <iostream>
#include <string>
#include <fstream>
#include <list>
#include <errno.h>
//
#include <TFile.h>
#include <TSystem.h>
#include <TSQLResult.h>
#include <TSQLRow.h>
#include <TTree.h>
#include <TGraph.h>
#include <TDatime.h>
#include <TF1.h>
//
#include <EventHeader.h>
#include <PscuHeader.h>
#include <mcmd/McmdEvent.h>
#include <mcmd/McmdRecord.h>
#include <RunHeaderEvent.h>
#include <RunTrailerEvent.h>
#include <CalibCalPedEvent.h>
#include <CalibS4Event.h>
#include <CalibTrk1Event.h>
#include <CalibTrk2Event.h>
#include <varDump/VarDumpEvent.h>
#include <varDump/VarDumpRecord.h>
#include <physics/S4/S4Event.h>
//
#include <sgp4.h>

#include <PamelaDBOperations.h>
//
using namespace std;
using namespace pamela;

// Some function to work with cTle stuff.
bool compTLE(cTle* tle1, cTle *tle2);
float getTleJulian(cTle *);
string getTleDatetime(cTle*);

/**
 * Constructor.
 * @param host         hostname for the SQL connection.
 * @param user         username for the SQL connection.
 * @param password     password for the SQL connection.
 * @param filerawname  The path and name to the raw file.
 * @param filerootname The path and name of the raw file.
 * @param boot         file BOOT number.
 * @param obt0         file obt0.
 * @param tsync        file timesync.
 * @param debug        debug flag.
 * @param tlefilename  ascii file with TLE 3 line elements.
 */
PamelaDBOperations::PamelaDBOperations(TString host, TString user, TString password, TString filerawname, TString filerootname, UInt_t boot, UInt_t tsync, UInt_t obt0, Bool_t debug, TString tlefilename){
  //
  //
  SetConnection(host,user,password);
  //
  SetDebugFlag(debug);
  //
  glrun = new GL_RUN();
  //
  if ( !boot ) SetNOBOOT(false);
  SetBOOTnumber(boot);
  SetTsync(tsync);
  SetObt0(obt0);
  //
  SetTLEPath(tlefilename);
  //
  //
  INSERT_RAW =!filerawname.IsNull();
  if(INSERT_RAW)SetRawName(filerawname);
  //
  INSERT_ROOT = !filerootname.IsNull();
  if( INSERT_ROOT ){
    this->SetRootName(filerootname);
    this->SetOrbitNo();
    file = TFile::Open(this->GetRootName().Data());
  };
  //
  this->SetID_RAW(0);
  this->SetID_ROOT(0);

  VALIDATE = false;
  
  //
};

/**
 * Destructor
 */
void PamelaDBOperations::Close(){
  if( conn && conn->IsConnected() ) conn->Close();
  delete clean_time;
  delete glrun;
  delete this;
};

//
// SETTERS 
//

//
// must be out of the constructor in order to FORCE the validation of the latest runs in case you run the validation together with the latest file
//
void PamelaDBOperations::CheckValidate(Long64_t olderthan){
  clean_time = new TDatime();
  if(olderthan >= 0){
    VALIDATE = true;
    UInt_t timelim = 0;
    timelim =  (UInt_t)clean_time->Convert() - olderthan;
    clean_time->Set(timelim,false);
  };
};

/**
 * Open the DB connection
 * @param host         hostname for the SQL connection.
 * @param user         username for the SQL connection.
 * @param password     password for the SQL connection.
 */
void PamelaDBOperations::SetConnection(TString host, TString user, TString password){
  if ( IsDebug() ) printf(" Connecting using host = %s user = %s password = %s \n",host.Data(),user.Data(),password.Data());
  conn = TSQLServer::Connect(host.Data(),user.Data(),password.Data());
};

/**
 * Store the ID of the ROOT file.
 * @param idr    ID of the ROOT file
 */
void PamelaDBOperations::SetID_ROOT(UInt_t idr){
  idroot=idr;
};

/**
 * Store the ID of the RAW file.
 * @param idr    ID of the RAW file
 */
void PamelaDBOperations::SetID_RAW(UInt_t idr){
  id=idr;
};

/**
 * Set the debug flag
 * 
 */
void PamelaDBOperations::SetDebugFlag(Bool_t dbg){
  debug = dbg;
};

/**
 * Store the BOOT number of the RAW file.
 * @param boot    BOOT number of the RAW file
 */
void PamelaDBOperations::SetBOOTnumber(UInt_t boot){
  BOOTNO=boot;
};

/**
 * Store the time sync of the RAW file.
 * @param boot    time sync
 */
void PamelaDBOperations::SetTsync(UInt_t ts){
  tsync=ts;
};

/**
 * Store the time sync of the RAW file.
 * @param boot    time sync
 */
void PamelaDBOperations::SetObt0(UInt_t ts){
  obt0=ts;
};

/**
 * Store the RAW filename.
 * @param str    the RAW filename.
 */
void PamelaDBOperations::SetRawName(TString str){
  filerawname=str;
};

/**
 * Store the ROOT filename.
 * @param str    the ROOT filename.
 */
void PamelaDBOperations::SetRootName(TString str){
  filerootname=str;
};

/**
 * Store the downlink orbit number from filename.
 */
void PamelaDBOperations::SetOrbitNo(){
  dworbit = 0;
  TString name = this->GetRootFile();
  Int_t nlength = name.Length();
  if ( nlength < 5 ) return;
  TString dwo = 0;
  for (Int_t i = 0; i<5; i++){
    dwo.Append(name[i],1);
  };
  if ( dwo.IsDigit() ){
    dworbit = (UInt_t)dwo.Atoi();
  } else {
    dwo="";
    for (Int_t i = 8; i<13; i++){
      dwo.Append(name[i],1);
    };     
    if ( dwo.IsDigit() ) dworbit = (UInt_t)dwo.Atoi();
  };
  if ( IsDebug() ) printf(" Downlink orbit is %i (dwo = %s) \n",dworbit,dwo.Data());
  return;
};


/**
 * Store the NOBOOT flag.
 * @param noboot    true/false.
 */
void PamelaDBOperations::SetNOBOOT(Bool_t noboot){
  NOBOOT = noboot;
};

/**
 * Store path to the TLE file.
 */
void PamelaDBOperations::SetTLEPath(TString str){
  tlefilename = str;
};

/**
 * Store the olderthan variable
 * @param olderthan
 */
// void PamelaDBOperations::SetOlderThan(Long64_t oldthan){
//   olderthan = oldthan;
// };

/**
 * Retrieve the ID_RAW, if exists, returns NULL if does not exist.
 */
Bool_t PamelaDBOperations::SetID_RAW(){
  stringstream oss;
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  oss.str("");
  oss << "SELECT ID FROM GL_RAW WHERE "
      << " PATH = '" << this->GetRawPath().Data() << "' AND "
      << " NAME = '" << this->GetRawFile().Data() << "' ";
  result = conn->Query(oss.str().c_str());
  if ( result == NULL ) throw -4;
  row = result->Next();
  if ( !row ) return(false);
  delete result;
  id = (UInt_t)atoll(row->GetField(0));
  return(true);
}

/**
 * 
 * Set the variables which have to be stored in the GL_RUN table and that do not depend on the RUN
 * 
 */
void PamelaDBOperations::SetCommonGLRUN(UInt_t absth, UInt_t abstt){ 
  glrun->SetBOOTNUMBER(BOOTNO);
  glrun->SetRUNHEADER_TIME(absth);
  glrun->SetRUNTRAILER_TIME(abstt);
  glrun->SetID_ROOT_L2(0);
  glrun->SetID_ROOT_L0(idroot);
  glrun->SetVALIDATION(0);
};

/**
 * Patch, look for upper limits to avoid processing retransmitted data
 */
Int_t PamelaDBOperations::SetUpperLimits(){
  UInt_t nevent = 0;
  UInt_t pktlast = 0;
  UInt_t obtlast = 0;
  UInt_t t_pktlast = 0;
  UInt_t t_obtlast = 0;
  UInt_t upperpkt2 = 0;
  ULong64_t upperobt2 = 0;
  UInt_t zomp = 0;
  UInt_t jump = 50000; // was 5000
  EventCounter *code=0;
  //
  UInt_t deltapkt = 5000;
  ULong64_t deltaobt = 50000;
  //
  //   pcksList packetsNames;
  //   pcksList::iterator Iter;
  //   getPacketsNames(packetsNames);
  //
  pktfirst = 0;
  obtfirst = 0;
  //
  TTree *T = 0;
  T = (TTree*)file->Get("Physics");
  if ( !T || T->IsZombie() ) throw -16;
  EventHeader *eh = 0;
  PscuHeader *ph = 0;
  T->SetBranchAddress("Header", &eh);
  nevent = T->GetEntries();
  //
  T->GetEntry(0);
  ph = eh->GetPscuHeader();
  pktfirst = ph->GetCounter();
  obtfirst = ph->GetOrbitalTime();  
  //
  //   code = eh->GetCounter();
  //   UInt_t en = 0;
  //   for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
  //     en = code->Get(GetPacketType(*Iter));
  //     if ( en ) printf(" Packet type is %s, entries: %i \n",*Iter,en);
  //};  
  //
  T->GetEntry(nevent-1);
  ph = eh->GetPscuHeader();
  pktlast = ph->GetCounter();
  obtlast = ph->GetOrbitalTime();  
  //
  upperpkt = PKT(pktlast);
  upperobt = OBT(obtlast);
  upperentry = nevent-1;
  //
  if ( IsDebug() ) printf(" First entries are: OBT %llu pkt_num %i \n",obtfirst,pktfirst); 
  //
  if ( IsDebug() ) printf(" Last entries are: OBT %llu pkt_num %i entry %i\n",upperobt,upperpkt,upperentry); 
  //
  if ( (PKT(pktlast) < PKT(pktfirst) && OBT(obtlast) > OBT(obtfirst)) || (PKT(pktlast) > PKT(pktfirst) && OBT(obtlast) < OBT(obtfirst)) ) return(1);
  //
  if ( !nevent ) return(2);
  //
  if ( nevent < 2 ) return(4);
  //
  if ( (PKT(pktlast) < PKT(pktfirst) && OBT(obtlast) < OBT(obtfirst)) || (labs(PKT(pktlast)-PKT(pktfirst))<deltapkt && labs(OBT(obtlast)-OBT(obtfirst))<deltaobt) ){
    //
    // go back
    zomp = nevent - 2;
    //
    while ( jump > 0 ){
      //
      t_pktlast = PKT(pktlast);
      t_obtlast = OBT(obtlast);    
      //
      for (UInt_t i = zomp; i>1; i-=jump){  
        //
        if ( i >= 0 ) T->GetEntry(i);
        ph = eh->GetPscuHeader();
        upperpkt = PKT(ph->GetCounter());
        upperobt = OBT(ph->GetOrbitalTime());
        upperentry = i;
        //
        if ( (i-1) >= 0 ) T->GetEntry(i-1);
        ph = eh->GetPscuHeader();
        upperpkt2 = PKT(ph->GetCounter());
        upperobt2 = OBT(ph->GetOrbitalTime());
        //
        if ( (t_pktlast < upperpkt && t_obtlast > upperobt) || (t_pktlast < upperpkt2 && t_obtlast > upperobt2) ) throw -13;    
        //
        if ( t_pktlast < upperpkt && t_obtlast < upperobt && t_pktlast < upperpkt2 && t_obtlast < upperobt2 ){
          zomp = i + jump + 1;
          if ( zomp > nevent-2 ) zomp = nevent - 2;
          if ( IsDebug() ) printf(" .-. jump %i zomp %i upperpkt %i pktlast %i upperobt %llu obtlast %u last entry is %i \n",jump,zomp,upperpkt,pktlast,upperobt,obtlast,i);
          break;
        };
        //
        t_pktlast = upperpkt;
        t_obtlast = upperobt;
      };    
      //
      if ( jump == 1 ) jump = 0;
      if ( jump == 10 ) jump = 1;
      if ( jump == 100 ) jump = 10;
      if ( jump == 1000 ) jump = 100;
      if ( jump == 5000 ) jump = 1000;
      if ( jump == 50000 ) jump = 5000;
      //
    };
    //
  };
  //
  // check if last runtrailer is within limits, if not extend limits (one should check for all packets but we need only runtrailer)
  //
  PacketType *pctp=0;
  TTree *rh=(TTree*)file->Get("RunHeader");
  if ( !rh || rh->IsZombie() ) throw -17;
  TTree *rt=(TTree*)file->Get("RunTrailer");
  if ( !rt || rt->IsZombie() ) throw -18;
  //
  rh->SetBranchAddress("RunHeader", &runh);
  rh->SetBranchAddress("Header", &ehh);
  //
  rt->SetBranchAddress("RunTrailer", &runt);
  rt->SetBranchAddress("Header", &eht);
  //
  rhev = rh->GetEntries();
  rtev = rt->GetEntries();
  UInt_t sobtt = 0;
  UInt_t sobth = 0;
  UInt_t spktt = 0;
  UInt_t spkth = 0;
  UInt_t pktt = 0;
  ULong64_t obtt = 0;
  UInt_t pkth = 0;
  ULong64_t obth = 0;
  //
  T->GetEntry(upperentry);
  code = eh->GetCounter();
  Int_t lasttrail = code->Get(pctp->RunTrailer);
  Int_t lasthead = code->Get(pctp->RunHeader);
  if ( lasttrail < rtev ){
    rt->GetEntry(lasttrail);
    pht = eht->GetPscuHeader();
    pktt = PKT(pht->GetCounter());
    obtt = OBT(pht->GetOrbitalTime());
  };
  //
  if ( lasthead < rhev ){
    rh->GetEntry(lasthead);
    phh = ehh->GetPscuHeader();
    pkth = PKT(phh->GetCounter());
    obth = OBT(phh->GetOrbitalTime());
  };
  //
  if ( IsDebug() ) printf(" rhev before %i ph %i upperp %i oh %llu uppero %llu \n",rhev,pkth,upperpkt,obth,upperobt);
  if ( pkth > upperpkt && obth > upperobt ){
    if ( IsDebug() ) printf(" Upper limits extended to include last header: ph %i upperp %i oh %llu uppero %llu \n",pkth,upperpkt,obth,upperobt);
    upperpkt = pkth;
    upperobt = obth;
    rhev = lasthead+1;
  } else {
    rhev = lasthead;
  };
  if ( IsDebug() ) printf(" rhev after %i ph %i upperp %i oh %llu uppero %llu \n",rhev,pkth,upperpkt,obth,upperobt);
  //
  if ( IsDebug() ) printf(" rtev beforev %i  pt %i upperp %i ot %llu uppero %llu \n",rtev,pktt,upperpkt,obtt,upperobt);
  if ( pktt > upperpkt && obtt > upperobt ){
    if ( IsDebug() ) printf(" Upper limits extended to include last trailer: pt %i upperp %i ot %llu uppero %llu \n",pktt,upperpkt,obtt,upperobt);
    upperpkt = pktt;
    upperobt = obtt;
    rtev = lasttrail+1;
  } else {
    rtev = lasttrail;
  };
  if ( IsDebug() ) printf(" rtev after %i  pt %i upperp %i ot %llu uppero %llu \n",rtev,pktt,upperpkt,obtt,upperobt);
  //  goto kikko;
  //
  // 
  // Check if runtrailer/runheader are within lower limits
  //
  //
  pkth = 0;
  obth = 0;
  spkth = 0;
  sobth = 0;
  for (Int_t k=0; k<rhev; k++){
    if ( k > 0 ){
      spkth = pkth;
      sobth = obth;
    };
    rh->GetEntry(k);
    phh = ehh->GetPscuHeader();
    pkth = PKT(phh->GetCounter());
    obth = OBT(phh->GetOrbitalTime());
    //
//    if ( IsDebug() ) printf(" k %i rhev before %i ph %u upperp %u oh %u uppero %u \n",k,rhev,pkth,spkth,obth,sobth);
    //
    if ( pkth < spkth && obth < sobth ){
      if ( IsDebug() ) printf(" RH PROBLEMS determining the event repetition at the end of the file lasthead %i  \n",rhev);
      //
      rhev = k-1;
      rh->GetEntry(rhev);
      pkth = spkth;
      obth = sobth;
      //
      UInt_t evbefh = 0;
      code = ehh->GetCounter();
      evbefh = code->Get(pctp->Physics);    
      if ( evbefh >= 0 ){
        T->GetEntry(evbefh);
        ph = eh->GetPscuHeader();
        t_pktlast = PKT(ph->GetCounter());
        t_obtlast = OBT(ph->GetOrbitalTime());
        if ( t_pktlast <= spkth && t_obtlast <= sobth ){ // jump
          upperpkt = pkth;
          upperobt = obth;
          upperentry = evbefh-1;
        } else {
          while ( t_pktlast > spkth && t_obtlast > sobth && evbefh < nevent ){
            evbefh++;   
            T->GetEntry(evbefh);
            ph = eh->GetPscuHeader();
            t_pktlast = PKT(ph->GetCounter());
            t_obtlast = OBT(ph->GetOrbitalTime());
          };
          T->GetEntry(evbefh-1);
          ph = eh->GetPscuHeader();
          upperpkt = PKT(ph->GetCounter());
          upperobt = OBT(ph->GetOrbitalTime());
          upperentry = evbefh-1;
        };    
      };
      if ( IsDebug() ) printf(" rhev after %i ph %i upperp %i oh %llu uppero %llu \n",rhev,pkth,upperpkt,obth,upperobt);
      goto kikko0;
    };
  };
 kikko0:
  //
  //
  //
  pktt = 0;
  obtt = 0;
  spktt = 0;
  sobtt = 0;
  for (Int_t k=0; k<rtev; k++){
    if ( k > 0 ){
      spktt = pktt;
      sobtt = obtt;
    };
    rt->GetEntry(k);
    pht = eht->GetPscuHeader();
    pktt = PKT(pht->GetCounter());
    obtt = OBT(pht->GetOrbitalTime());
    //
//    if ( IsDebug() ) printf(" k %i rtev beforev %i  pt %i upperp %i ot %llu uppero %llu \n",k,rtev,pktt,spktt,obtt,sobtt);
    //
    if ( pktt < spktt && obtt < sobtt ){
      if ( IsDebug() ) printf(" RT PROBLEMS determining the event repetition at the end of the file lasttrail %i \n",rtev);
      //
      rtev = k-1;
      rt->GetEntry(rtev);
      pktt = spktt;
      obtt = sobtt;
      if ( IsDebug() ) printf(" lasttrail %i pt %i upperp %i ot %llu uppero %llu \n",rtev,pktt,upperpkt,obtt,upperobt);
      //
      UInt_t evbeft = 0;
      code = eht->GetCounter();
      evbeft = code->Get(pctp->Physics);    
      if ( evbeft >= 0 ){
        T->GetEntry(evbeft);
        ph = eh->GetPscuHeader();
        t_pktlast = PKT(ph->GetCounter());
        t_obtlast = OBT(ph->GetOrbitalTime());
        if ( t_pktlast <= spktt && t_obtlast <= sobtt ){ // jump
          upperpkt = pktt;
          upperobt = obtt;
          upperentry = evbeft-1;
        } else {
          while ( t_pktlast > spktt && t_obtlast > sobtt && evbeft < nevent ){
            evbeft++;   
            T->GetEntry(evbeft);
            ph = eh->GetPscuHeader();
            t_pktlast = PKT(ph->GetCounter());
            t_obtlast = OBT(ph->GetOrbitalTime());
          };
          T->GetEntry(evbeft-1);
          ph = eh->GetPscuHeader();
          upperpkt = PKT(ph->GetCounter());
          upperobt = OBT(ph->GetOrbitalTime());
          upperentry = evbeft-1;
        };
      };
      if ( IsDebug() ) printf(" rtev after %i  pt %i upperp %i ot %llu uppero %llu \n",rtev,pktt,upperpkt,obtt,upperobt);
      goto kikko;
      //      break;
      //
    };
    //  
  };
  //
 kikko:
  //
  T->GetEntry(upperentry);
  code = eh->GetCounter();
  lasttrail = code->Get(pctp->RunTrailer);
  lasthead = code->Get(pctp->RunHeader);
  if ( lasttrail < rtev ){
    rt->GetEntry(lasttrail);
    pht = eht->GetPscuHeader();
    pktt = PKT(pht->GetCounter());
    obtt = OBT(pht->GetOrbitalTime());
  };
  //
  if ( lasthead < rhev ){
    rh->GetEntry(lasthead);
    phh = ehh->GetPscuHeader();
    pkth = PKT(phh->GetCounter());
    obth = OBT(phh->GetOrbitalTime());
  };
  //
  if ( IsDebug() ) printf(" rhev before %i ph %i upperp %i oh %llu uppero %llu \n",rhev,pkth,upperpkt,obth,upperobt);
  if ( pkth > upperpkt && obth > upperobt ){
    if ( IsDebug() ) printf(" Upper limits extended to include last header: ph %i upperp %i oh %llu uppero %llu \n",pkth,upperpkt,obth,upperobt);
    upperpkt = pkth;
    upperobt = obth;
    rhev = lasthead+1;
  } else {
    rhev = lasthead;
  };
  if ( IsDebug() ) printf(" rhev after %i ph %i upperp %i oh %llu uppero %llu \n",rhev,pkth,upperpkt,obth,upperobt);
  //
  if ( IsDebug() ) printf(" rtev beforev %i  pt %i upperp %i ot %llu uppero %llu \n",rtev,pktt,upperpkt,obtt,upperobt);
  if ( pktt > upperpkt && obtt > upperobt ){
    if ( IsDebug() ) printf(" Upper limits extended to include last trailer: pt %i upperp %i ot %llu uppero %llu \n",pktt,upperpkt,obtt,upperobt);
    upperpkt = pktt;
    upperobt = obtt;
    rtev = lasttrail+1;
  } else {
    rtev = lasttrail;
  };
  if ( IsDebug() ) printf(" rtev after %i  pt %i upperp %i ot %llu uppero %llu \n",rtev,pktt,upperpkt,obtt,upperobt);
  //
  if ( IsDebug() ) printf(" Upper limits are: OBT %llu pkt_num %i upper entry %i \n",upperobt,upperpkt,upperentry); 
  //
  return(0);
}

/**
 *
 * Trick to have unique RUN ID numbers even when runs are deleted and mysql deamon restarted.
 * Entries in the _RUNID_GEN table are never deleted.
 *
 **/
UInt_t PamelaDBOperations::AssignRunID(){
  //
  TSQLResult *result = 0;
  TSQLRow *row = 0;
  UInt_t runid = 0;
  //
  stringstream   oss;
  //  
  oss.str("");
  oss << "INSERT INTO _RUNID_GEN VALUES (NULL);";
  result = conn->Query(oss.str().c_str());
  if ( !result ) throw -10;
  oss.str("");
  oss << "SELECT ID FROM _RUNID_GEN ORDER BY ID DESC LIMIT 1;";
  result = conn->Query(oss.str().c_str());
  if ( !result ) throw -10;
  //
  row = result->Next();
  //
  if ( !row ) throw -28;
  //
  runid = (UInt_t)atoll(row->GetField(0));
  //
  return(runid);
};

//
// GETTERS
//

/**
 * 
 * Returns the DB absolute time needed to associate calibrations to data
 * 
 */
UInt_t PamelaDBOperations::GetAbsTime(UInt_t obt){  
  //
  return(((UInt_t)(OBT(obt)/1000)+toffset));
  //
};

/**
 * 
 *  List of packet types (just to make easily the loops)
 *
 */
const PacketType* PamelaDBOperations::GetPacketType(const char* type){
  if ( !strcmp(type,"Pscu") ) return(PacketType::Pscu);  
  if ( !strcmp(type,"PhysEndRun") ) return(PacketType::PhysEndRun);
  if ( !strcmp(type,"CalibCalPulse1") ) return(PacketType::CalibCalPulse1);
  if ( !strcmp(type,"CalibCalPulse2") ) return(PacketType::CalibCalPulse2);
  if ( !strcmp(type,"Physics") ) return(PacketType::Physics);
  if ( !strcmp(type,"CalibTrkBoth") ) return(PacketType::CalibTrkBoth);
  if ( !strcmp(type,"CalibTrk1") ) return(PacketType::CalibTrk1);
  if ( !strcmp(type,"CalibTrk2") ) return(PacketType::CalibTrk2);
  if ( !strcmp(type,"CalibTof") ) return(PacketType::CalibTof);
  if ( !strcmp(type,"CalibS4") ) return(PacketType::CalibS4);
  if ( !strcmp(type,"CalibCalPed") ) return(PacketType::CalibCalPed);
  if ( !strcmp(type,"Calib1_Ac1") ) return(PacketType::Calib1_Ac1);
  if ( !strcmp(type,"Calib2_Ac1") ) return(PacketType::Calib2_Ac1);
  if ( !strcmp(type,"Calib1_Ac2") ) return(PacketType::Calib1_Ac2);
  if ( !strcmp(type,"Calib2_Ac2") ) return(PacketType::Calib2_Ac2);
  if ( !strcmp(type,"CalibCal") ) return(PacketType::CalibCal);
  if ( !strcmp(type,"RunHeader") ) return(PacketType::RunHeader);
  if ( !strcmp(type,"RunTrailer") ) return(PacketType::RunTrailer);
  if ( !strcmp(type,"CalibHeader") ) return(PacketType::CalibHeader);
  if ( !strcmp(type,"CalibTrailer") ) return(PacketType::CalibTrailer);
  if ( !strcmp(type,"InitHeader") ) return(PacketType::InitHeader);
  if ( !strcmp(type,"InitTrailer") ) return(PacketType::InitTrailer);
  if ( !strcmp(type,"EventTrk") ) return(PacketType::EventTrk);
  if ( !strcmp(type,"Log") ) return(PacketType::Log);
  if ( !strcmp(type,"VarDump") ) return(PacketType::VarDump);
  if ( !strcmp(type,"ArrDump") ) return(PacketType::ArrDump);
  if ( !strcmp(type,"TabDump") ) return(PacketType::TabDump);
  if ( !strcmp(type,"Tmtc") ) return(PacketType::Tmtc);
  if ( !strcmp(type,"Mcmd") ) return(PacketType::Mcmd);
  if ( !strcmp(type,"ForcedFECmd") ) return(PacketType::ForcedFECmd);
  if ( !strcmp(type,"Ac1Init") ) return(PacketType::Ac1Init);
  if ( !strcmp(type,"CalInit") ) return(PacketType::CalInit);
  if ( !strcmp(type,"TrkInit") ) return(PacketType::TrkInit);
  if ( !strcmp(type,"TofInit") ) return(PacketType::TofInit);
  if ( !strcmp(type,"TrgInit") ) return(PacketType::TrgInit);
  if ( !strcmp(type,"NdInit") ) return(PacketType::NdInit);
  if ( !strcmp(type,"S4Init") ) return(PacketType::S4Init);
  if ( !strcmp(type,"Ac2Init") ) return(PacketType::Ac2Init);
  if ( !strcmp(type,"CalAlarm") ) return(PacketType::CalAlarm);
  if ( !strcmp(type,"Ac1Alarm") ) return(PacketType::Ac1Alarm);
  if ( !strcmp(type,"TrkAlarm") ) return(PacketType::TrkAlarm);
  if ( !strcmp(type,"TrgAlarm") ) return(PacketType::TrgAlarm);
  if ( !strcmp(type,"TofAlarm") ) return(PacketType::TofAlarm);
  if ( !strcmp(type,"S4Alarm") ) return(PacketType::S4Alarm);
  if ( !strcmp(type,"Ac2Alarm") ) return(PacketType::Ac2Alarm);
  if ( !strcmp(type,"TsbT") ) return(PacketType::TsbT);
  if ( !strcmp(type,"TsbB") ) return(PacketType::TsbB);
  return(PacketType::Invalid);
};

//
// PRIVATE FUNCTIONS 
//

// /**
// * Open the ROOT filename for reading
// */
// void PamelaDBOperations::OpenFile(){
//   file = TFile::Open(this->GetRootName().Data());
//   //

void PamelaDBOperations::CheckFile(){  
  if ( !file ) throw -12;
};


/**
 * Check if LEVEL0 file and DB connection have really be opened
 */
void PamelaDBOperations::CheckConnection(){  
  //
  // check connection
  //
  if( !conn ) throw -1;
  bool connect = conn->IsConnected();
  if( !connect ) throw -1;
  if ( !dworbit ) throw -27;
};

/**
 * Return the correct packet number if we went back to zero
 */
UInt_t PamelaDBOperations::PKT(UInt_t pkt_num){  
  //
  //  if ( IsDebug() ) printf(" pkt conversion: pkt_num is %u pktfirst is %u (pktfirst - (UInt_t)(16777212/2)) is %u  \n",pkt_num,pktfirst,(pktfirst - (UInt_t)(16777212/2)));
  //
  if ( pkt_num < (pktfirst/2) && pktfirst > (16777214/2)  ) return((pkt_num+16777215));
  //
  if ( pkt_num > pktfirst*2 && pkt_num > (16777214/2) ){
    if ( (pkt_num-16777215) < 0 ){
      return((16777215-pkt_num));
    } else {
      return((pkt_num-16777215));
    };
  };
  //
  return(pkt_num);
  //
};

/**
 * Return the correct On Board Time if we went back to zero
 */
ULong64_t PamelaDBOperations::OBT(UInt_t obt){  
  //
  if ( obt < (obtfirst/2) && obtfirst > (numeric_limits<UInt_t>::max()/2) ) return((ULong64_t)(obt+numeric_limits<UInt_t>::max()));
  //
  if ( obt > (obtfirst*2) && obt > (numeric_limits<UInt_t>::max()/2) ){
    if ( (obt-numeric_limits<UInt_t>::max()) < 0  ){
      return((ULong64_t)(numeric_limits<UInt_t>::max()-obt));
    } else {
      return((ULong64_t)(obt-numeric_limits<UInt_t>::max()));
    };
  };
  //
  return((ULong64_t)obt);
};

/**
 * 
 *  Fill the glrun class with infos about the run when we have both runtrailer and runheader
 * 
 */
void PamelaDBOperations::FillClass(){
  this->FillClass(false,false,0,0);
};

/**
 * 
 *  Fill the glrun class with infos about the run when we have both runtrailer and runheader
 * 
 */
void PamelaDBOperations::FillClass(Bool_t mishead, Bool_t mistrail, UInt_t firstev, UInt_t lastev){
  //
  TTree *T = 0;
  T = (TTree*)file->Get("Physics");
  if ( !T || T->IsZombie() ) throw -16;
  //
  EventHeader *eh = 0;
  PscuHeader *ph = 0;
  T->SetBranchAddress("Header", &eh);
  PacketType *pctp=0;
  EventCounter *codt=0;
  EventCounter *codh=0;
  UInt_t firstObt = 0;
  UInt_t lastObt = 0;
  UInt_t firstPkt = 0;
  UInt_t lastPkt = 0;
  UInt_t rhtime = 0;
  UInt_t rttime = 0;
  if ( !mishead ){
    codh = ehh->GetCounter();
    firstev = codh->Get(pctp->Physics);
    rhtime = this->GetAbsTime(phh->GetOrbitalTime());
    glrun->Set_GL_RUNH(runh,phh);
    firstObt = glrun->GetRUNHEADER_OBT();
    firstPkt = glrun->GetRUNHEADER_PKT();
  };
  if ( !mistrail ){
    codt = eht->GetCounter();
    lastev = codt->Get(pctp->Physics)-1;
    rttime = this->GetAbsTime(pht->GetOrbitalTime());
    glrun->Set_GL_RUNT(runt,pht);
    lastObt = glrun->GetRUNTRAILER_OBT();
    lastPkt = glrun->GetRUNTRAILER_PKT();
  };
  //
  if ( mishead && mistrail && lastev+1 == firstev ) throw -14; // run with no events, no runtrailer, no runheader... unsupported should never arrive here
  //
  if ( mishead ) {
    glrun->Set_GL_RUNH0();
    //
    if ( lastev+1 == firstev ){      
      firstObt = lastObt;
      firstPkt = lastPkt;
      rhtime = rttime;
    } else {
      T->GetEntry(firstev);
      ph = eh->GetPscuHeader();         
      firstObt = ph->GetOrbitalTime();
      rhtime = this->GetAbsTime(firstObt);
      firstPkt = ph->GetCounter();               
    };
    //
    glrun->SetRUNHEADER_PKT(firstPkt);
    glrun->SetRUNHEADER_OBT(firstObt);
    //
  };
  if ( mistrail ){
    glrun->Set_GL_RUNT0();
    //
    if ( lastev+1 == firstev ){      
      lastObt = firstObt;
      lastPkt = firstPkt;
      rttime = rhtime;
    } else {
      T->GetEntry(lastev);
      ph = eh->GetPscuHeader(); 
      lastObt = ph->GetOrbitalTime();
      rttime = this->GetAbsTime(lastObt);
      lastPkt = ph->GetCounter();                
    };
    //
    glrun->SetRUNTRAILER_OBT(lastObt);
    glrun->SetRUNTRAILER_PKT(lastPkt);
    //    
  };
  glrun->SetEV_FROM(firstev);
  glrun->SetEV_TO(lastev);
  glrun->SetNEVENTS(lastev-firstev+1);
  //
  this->SetCommonGLRUN(rhtime,rttime);
  //
};

//
// PUBLIC FUNCTIONS
//

/**
 * Insert a new row into GL_RAW table.
 */
Int_t PamelaDBOperations::insertPamelaRawFile(){
  //
  stringstream oss;
  //
  Bool_t idr =  this->SetID_RAW();
  if ( idr ) return(1);
  //
  oss.str("");
  oss << "INSERT INTO GL_RAW (PATH, NAME) VALUES ('"
      << this->GetRawPath().Data() << "', '" << this->GetRawFile().Data() << "')";
  if ( conn->Query(oss.str().c_str()) == 0 ) throw -4;
  //
  idr =  this->SetID_RAW();
  if ( !idr ) throw -11;
  //
  return(0);
}


/**
 * Look for one timesync information in the file and
 * fill the GL_TIMESYNC table. It will look for: 1) TS-MCMD 2) TS info in the RunHeader 3) TS info in the runtrailer, if none exists exit with error
 */
Int_t PamelaDBOperations::insertPamelaGL_TIMESYNC(){
  //
  TSQLResult *result = 0;
  TSQLRow *row = 0;
  UInt_t t0 = 0;
  //
  stringstream   oss;
  //  
  if ( this->GetID_RAW() == 0 )  throw -11;
  //
  oss.str("");
  oss << "SELECT OFFSET_DATE FROM GL_RESURS_OFFSET WHERE SPECIAL_FILE='" 
      << this->GetRawFile().Data() << "';";
  if ( IsDebug() ) printf(" %s \n",oss.str().c_str());
  result = conn->Query(oss.str().c_str());
  if ( !result ) throw -10;
  row = result->Next();
  //
  if ( !row ){
    oss.str("");
    oss << "SELECT OFFSET_DATE FROM GL_RESURS_OFFSET WHERE FROM_ORBIT< " 
        << dworbit << " order by FROM_ORBIT desc limit 1;";
    if ( IsDebug() ) printf(" %s \n",oss.str().c_str());
    result = conn->Query(oss.str().c_str());
    if ( !result ) throw -10;
    row = result->Next();
    if ( !row ) throw -10;
  };
  //
  t0 = (UInt_t)TDatime(row->GetField(0)).Convert();
  /*
   * Verify that the TIMESYNC have been not already processed
   */
  oss.str("");
  oss << " SELECT COUNT(GL_TIMESYNC.ID),GL_TIMESYNC.OBT0,GL_TIMESYNC.TIMESYNC FROM GL_TIMESYNC "
      << " LEFT JOIN GL_RAW "
      << " ON GL_RAW.ID = GL_TIMESYNC.ID_RAW "
      << " WHERE GL_TIMESYNC.ID_RAW = " << this->GetID_RAW() 
      << " GROUP BY GL_TIMESYNC.OBT0;";
  if ( IsDebug() ) printf(" check for old timesync: query is \n %s \n",oss.str().c_str());
  result = conn->Query(oss.str().c_str());
  if (result == NULL) throw -10;
  row = result->Next();
  if ((row != NULL) && ((UInt_t)atoll(row->GetField(0)) > 0)){
    toffset = (UInt_t)atoll(row->GetField(2)) - (UInt_t)(this->OBT((UInt_t)atoll(row->GetField(1)))/1000) + t0;
    return(1);
  };
  //
  TTree *T = 0;
  Int_t signal = 0;
  //
  UInt_t nevent = 0;
  UInt_t recEntries = 0;
  //
  UInt_t OBT = 0;
  UInt_t TYPE = 0;
  //
  Double_t minimum = 0.;
  Double_t maximum = 0.;
  Double_t minimum2 = 0.;
  Double_t maximum2 = 0.;
  //
  UInt_t TSYNC = 0;
  //
  pamela::McmdEvent *mc = 0;
  pamela::McmdRecord *mcrc = 0;
  TArrayC *mcmddata = 0;
  //
  minimum = numeric_limits<Double_t>::max();
  maximum = numeric_limits<Double_t>::min();
  minimum2 = numeric_limits<Double_t>::max();
  maximum2 = numeric_limits<Double_t>::min();
  //
  T = (TTree*)file->Get("Mcmd");
  if ( !T || T->IsZombie() ) throw -19;
  T->SetBranchAddress("Mcmd",&mc);
  //
  nevent = T->GetEntries();
  //
  // loop over events
  //
  Bool_t existsts = false;
  //
  for (UInt_t i=0; i<nevent;i++){
    //
    T->GetEntry(i);
    //
    recEntries = mc->Records->GetEntries();
    //
    for (UInt_t j = 0; j < recEntries; j++){
      mcrc = (pamela::McmdRecord*)mc->Records->At(j);
      mcmddata = mcrc->McmdData;
      //
      if (mcrc->ID1 == 0xE0){ // mcmd timesync
        //
        OBT = (Int_t)(mcrc->MCMD_RECORD_OBT);
        //
        TSYNC = (((UInt_t)mcmddata->At(0)<<24)&0xFF000000) +  (((UInt_t)mcmddata->At(1)<<16)&0x00FF0000) + (((UInt_t)mcmddata->At(2)<<8)&0x0000FF00) + (((UInt_t)mcmddata->At(3))&0x000000FF);
        //
        TYPE = 55;//224;
        //
        if ( IsDebug() ) printf("mcmd tsync %i tsync %u obt %u \n",i,TSYNC,OBT);
        //
        if ( TSYNC && OBT ){
          existsts = true;
          goto eout;
        };
        //
      };
    };
  };
  if ( !existsts ) { // try with runheader and runtrailer
    //
    if ( IsDebug() ) printf(" No ts mcmd \n");
    signal = 2;
    //
    TTree *rh=(TTree*)file->Get("RunHeader");
    if ( !rh || rh->IsZombie() ) throw -17;
    TTree *rt=(TTree*)file->Get("RunTrailer");
    if ( !rt || rt->IsZombie() ) throw -18;
    //
    rh->SetBranchAddress("RunHeader", &runh);
    //
    rt->SetBranchAddress("RunTrailer", &runt);
    //
    Int_t nrhev = rh->GetEntries();
    Int_t nrtev = rt->GetEntries();
    if ( IsDebug() ) printf(" ou nevent %i rhev %i rtev %i \n",nevent,nrhev,nrtev);
    //
    if ( nrhev > 0 ){
      for (Int_t i=0; i<nrhev; i++){
        //
        rh->GetEntry(i);
        //
        TSYNC = runh->LAST_TIME_SYNC_INFO;
        OBT = runh->OBT_TIME_SYNC * 1000;
        //
        TYPE = 20;
        //
        if ( IsDebug() ) printf("runheader %i tsync %u obt %u \n",i,TSYNC,OBT);
        //
        if ( TSYNC && OBT ){
          existsts = true;
          goto eout;
        };
      };
      //
    };
    if ( nrtev > 0 ){
      //
      if ( IsDebug() ) printf(" No runheader \n");
      signal = 6;
      //
      for (Int_t i=0; i<nrtev; i++){
        //
        rt->GetEntry(i);
        //
        TSYNC = runt->LAST_TYME_SYNC_INFO;
        OBT = runt->OBT_TYME_SYNC * 1000;
        //
        TYPE = 21;
        //
        if ( IsDebug() ) printf("runtrailer %i tsync %u obt %u \n",i,TSYNC,OBT);
        //
        if ( TSYNC && OBT ){
          existsts = true;
          goto eout;
        };
      };
      //
    } else {
      if ( IsDebug() ) printf(" No runheader \n");
    };
  };
  //
  if ( !existsts ){ // try with inclination mcmd
    //
    if ( IsDebug() ) printf(" No runtrailer \n");
    signal = 14;
    //
    Double_t timesync = 0.;
    for (UInt_t i=0; i<nevent;i++){
      //
      T->GetEntry(i);
      //
      recEntries = mc->Records->GetEntries();
      //      //
      for (UInt_t j = 0; j < recEntries; j++){
        mcrc = (pamela::McmdRecord*)mc->Records->At(j);
        mcmddata = mcrc->McmdData;
        //
        if (mcrc->ID1 == 0xE2){ // mcmd inclination  
          timesync = 0.;
          timesync = (Double_t)(((((UInt_t)mcmddata->At(0) << 24) & 0xFF000000) + (((UInt_t)mcmddata->At(1) << 16) & 0x00FF0000) + (((UInt_t)mcmddata->At(2) << 8) & 0x0000FF00) + ((UInt_t)mcmddata->At(3) & 0x000000FF))/128.0);
          //
          if ( timesync > maximum2){
            maximum2 = timesync;
            OBT = (Int_t)(mcrc->MCMD_RECORD_OBT);
          };
        };
        //
      };
    };
    if ( maximum2 > numeric_limits<Double_t>::min() ){
      TSYNC = (UInt_t)(maximum2 + 0.5); 
      TYPE = 666;
      if ( TSYNC && OBT ){
        existsts = true;
        goto eout;
      };
    };
  };
  //
  if ( !existsts && obt0 ){ // insert timesync by hand
    //
    if ( IsDebug() ) printf(" No incl mcmd \n");
    signal = 30;
    //
    OBT = obt0;
    TSYNC = tsync; 
    TYPE = 999;
    existsts = true;
    goto eout;
  };
  //
 eout:
  //
  if ( !existsts ) throw -3;
  //
  oss.str("");
  oss << "INSERT INTO GL_TIMESYNC (ID_RAW,TYPE,OBT0,TIMESYNC) VALUES ('"
      << this->GetID_RAW() << "','"//224'"
      << dec << (UInt_t)TYPE << "','"
      << dec << (UInt_t)OBT << "','"
      << dec << (UInt_t)TSYNC << "');";
  conn->Query(oss.str().c_str());
  if ( IsDebug() ) printf(" Query the GL_TIMESYNC table to fill it:\n %s \n",oss.str().c_str()); 
  //
  toffset = (UInt_t)TSYNC - (UInt_t)(this->OBT(OBT)/1000) + t0;
  //
  delete result;
  return(signal);
}

/**
 * Insert all the new rows into GL_ROOT. 
 * The raw file indicates in the parameters should be already been stored in the database.
 */
Int_t PamelaDBOperations::insertPamelaRootFile(){
  stringstream oss;
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  UInt_t idtimesync = 0;
  //
  oss.str("");
  oss << " SELECT COUNT(GL_ROOT.ID_RAW),GL_RAW.ID,GL_ROOT.ID FROM GL_RAW "
      << " LEFT JOIN GL_ROOT "
      << " ON GL_RAW.ID = GL_ROOT.ID_RAW "
      << " WHERE GL_RAW.PATH = '" << this->GetRawPath().Data() << "' AND "
      << " GL_RAW.NAME = '" << this->GetRawFile().Data() << "' GROUP BY GL_RAW.ID ";
  result = conn->Query(oss.str().c_str());
  //
  if ( !result ) throw -12;
  //
  row = result->Next();
  //
  if ( !row ) throw -10;
  if ( row != NULL && (UInt_t)atoll(row->GetField(0))>0 ){
    idroot = (UInt_t)atoll(row->GetField(2));
    return(1);
  };
  //
  // determine which timesync has to be used
  //
  oss.str("");
  oss << "SELECT GL_TIMESYNC.ID FROM GL_TIMESYNC LEFT JOIN GL_RAW ON GL_RAW.ID = GL_TIMESYNC.ID_RAW ORDER BY GL_TIMESYNC.ID DESC LIMIT 1;";
  result = conn->Query(oss.str().c_str());
  //
  if ( !result ) throw -3;
  //
  row = result->Next();
  //
  if ( !row ) throw -3;
  idtimesync = (UInt_t)atoll(row->GetField(0));
  //
  oss.str("");
  oss << "INSERT INTO GL_ROOT (ID_RAW, ID_TIMESYNC,PATH, NAME) VALUES ('"
      << this->GetID_RAW() << "', '" << idtimesync << "', '" << this->GetRootPath().Data() << "', '" << this->GetRootFile().Data() << "')";
  //
  if (conn->Query(oss.str().c_str()) == 0) throw -4;
  //
  delete result;
  //
  oss.str("");
  oss << "SELECT ID FROM GL_ROOT WHERE ID_RAW=" << this->GetID_RAW() << ";";
  //
  result = conn->Query(oss.str().c_str());
  if ( !result ) throw -12;
  row = result->Next();
  this->SetID_ROOT((UInt_t)atoll(row->GetField(0)));
  //
  delete result;
  //
  return(0);
}

/**
 * Assign the BOOT_NUMBER to the raw file.
 */
Int_t PamelaDBOperations::assignBOOT_NUMBER(){
  stringstream oss;
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  oss.str("");
  oss << "SELECT ID, BOOT_NUMBER FROM GL_RAW WHERE "
      << " PATH = '" << this->GetRawPath().Data() << "' AND "
      << " NAME = '" << this->GetRawFile().Data() << "' ";
  result = conn->Query(oss.str().c_str());
  //
  if ( !result ) throw -4;;
  row = result->Next();
  if ( !row ) return(16);
  if ( row->GetField(1) ){
    this->SetBOOTnumber((UInt_t)atoll(row->GetField(1)));
    return(1);
  };
  if ( !row->GetField(0) ) throw -26;
  //
  UInt_t idRaw = (UInt_t)atoll(row->GetField(0));
  //
  //
  //
  TTree *trDumpEv = 0;
  trDumpEv = (TTree*)file->Get("VarDump");
  if ( !trDumpEv || trDumpEv->IsZombie() ) throw -20;
  //
  VarDumpEvent  *vde = 0;
  VarDumpRecord *vdr = 0;
  //
  trDumpEv->SetBranchAddress("VarDump", &vde);
  if ( trDumpEv->GetEntries() > 0 ){
    Bool_t found = false;
    for ( Int_t i = 0; i < trDumpEv->GetEntries(); i++){
      trDumpEv->GetEntry(i);
      vde->Records->GetEntries();
      if ( vde->Records->GetEntries()>0 ){
        found = true;
        goto fill;
      };
    };
  fill:
    if ( found ){
      //
      vdr = (VarDumpRecord*)vde->Records->At(6);
      //
      this->SetBOOTnumber((Int_t)vdr->VAR_VALUE);
      //
    } else {
      if ( !this->GetBOOTnumber() ) return(4);
    };
  } else {
    if ( !this->GetBOOTnumber() ) return(2);
  };
  //
  oss.str("");
  oss << " UPDATE GL_RAW "
      << " SET GL_RAW.BOOT_NUMBER = '" << dec << this->GetBOOTnumber() << "'"
      << " WHERE GL_RAW.ID = '" << idRaw << "'";
  conn->Query(oss.str().c_str());       
  //
  delete result;
  return(0);
};

/**
 * Scan runtrailer packet, fill the GL_RUN table and
 * check for missing and truncated runs
 */
Int_t PamelaDBOperations::insertPamelaRUN(){
  Int_t signal = 0;
  //
  stringstream oss;
  oss.str("");
  //
  signal = this->SetUpperLimits();
  //
  // loop on runheader and runtrailer events
  //
  TTree *rh=(TTree*)file->Get("RunHeader");
  if ( !rh || rh->IsZombie() ) throw -17;
  TTree *rt=(TTree*)file->Get("RunTrailer");
  if ( !rt || rt->IsZombie() ) throw -18;
  //
  PacketType *pctp=0;
  EventCounter *cod=0;
  //
  rh->SetBranchAddress("RunHeader", &runh);
  rh->SetBranchAddress("Header", &ehh);
  //
  rt->SetBranchAddress("RunTrailer", &runt);
  rt->SetBranchAddress("Header", &eht);
  //
  UInt_t obtt = 0;
  UInt_t obth = 0;
  UInt_t pktt = 0;
  UInt_t pkth = 0;
  Int_t pth = -1;
  Int_t ptht = -1;
  Int_t evbeft = 0;
  Int_t evbefh = 0;
  //
  // no runtrailers in the file!
  //
  if ( !rtev ){
    if ( !upperentry ){
      if ( IsDebug() ) printf(" No physics events nor runs in the file \n");
      throw -8;
    } else {
      this->HandleRunFragments(true,true,0,upperentry); 
    };
  } else {
    //
    for (Int_t ptt=0; ptt<rtev; ptt++){
      //
      rt->GetEntry(ptt);
      pht = eht->GetPscuHeader();
      pktt = pht->GetCounter();
      obtt = pht->GetOrbitalTime();
      //
      cod = eht->GetCounter();
      ptht = cod->Get(pctp->RunHeader) - 1;
      evbeft = cod->Get(pctp->Physics);
      //
      if ( !ptt &&  !(ptht+1) ){
        //
        if ( IsDebug() ) printf(" Piece of run at the beginning of the file %i %i %i \n",ptht,pth,ptt); 
        //
        this->HandleRunFragments(true,false,0,(evbeft-1)); 
        //
        //
      } else if ( pth == ptht ){
        //
        if ( IsDebug() ) printf(" Missing header %i %i %i\n",ptht,pth,ptt); 
        //
        if ( (ptt-1) < 0 ) throw -15; // should never arrive here!
        rt->GetEntry(ptt-1);
        cod = eht->GetCounter();
        evbefh = cod->Get(pctp->Physics);
        rt->GetEntry(ptt);
        pht = eht->GetPscuHeader();
        //
        if ( IsDebug() ) printf(" Try to find the beginning of a run which has only the runtrailer   %i %i %i \n",ptht,pth,ptt); 
        if ( IsDebug() ) printf(" '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''  %i %i %i \n",pkth,obth,obtt);
        //
        this->HandleMissingHoT(true,false,evbefh,evbeft-1); 
        //
      } else {
        //
        if ( IsDebug() ) printf(" Could be a good run, we have a runheader followed by a runtrailer %i %i %i\n",ptht,pth,ptt); 
        // 
        rh->GetEntry(ptht);
        phh = ehh->GetPscuHeader();
        pkth = phh->GetCounter();
        obth = phh->GetOrbitalTime();
        cod = ehh->GetCounter();
        evbefh = cod->Get(pctp->Physics);
        if ( IsDebug() ) printf(" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' %i %i %i \n",pkth,obth,obtt);
        //
        // handle this run
        //
        this->HandleRun();
        //
        //
        //
        if ( PKT(pkth)>PKT(pktfirst) && OBT(obth)>OBT(obtfirst) && !ptt ){
          //
          if ( IsDebug() ) printf(" Piece of run at the beginning of the file WITH NO RUNTRAILER \n"); 
          //
          this->HandleRunFragments(true,true,0,(evbefh-1)); 
          //
        };
        //
        //
        if ( (ptht - pth) > 1 ){
          //
          if ( IsDebug() ) printf(" Missing runtrailers! \n"); 
          if ( IsDebug() ) printf(" Attention there is a jump in the runheader counter %i %i %i \n",ptht,pth,ptt); 
          // is not the consecutive header
          while ( pth != ptht ){          
            //
            // treat the header(s) in the middle and then go to the next header, repeat until you reach the correct header.
            //
            pth++;
            //
            rh->GetEntry(pth+1);
            phh = ehh->GetPscuHeader();
            pktt = phh->GetCounter();
            obtt = phh->GetOrbitalTime();
            cod = ehh->GetCounter();
            evbeft = cod->Get(pctp->Physics);
            rh->GetEntry(pth);
            phh = ehh->GetPscuHeader();
            cod = ehh->GetCounter();
            pkth = phh->GetCounter();
            obth = phh->GetOrbitalTime();
            evbefh = cod->Get(pctp->Physics);
            //
            if ( IsDebug() ) printf(" Try to find the end of a run which has only the runheader   %i %i %i \n",ptht,pth,ptt); 
            if ( IsDebug() ) printf(" ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' %i %i %i \n",pkth,obth,obtt);
            //
            this->HandleMissingHoT(false,true,evbefh,evbeft-1); 
            //
          };
          //
        } else if ( !(ptht - pth) ){
          //
          if ( IsDebug() ) printf(" Missing runheader! \n"); 
          if ( IsDebug() ) printf(" Attention! the runheader counter did not changed %i %i %i \n",ptht,pth,ptt); 
          if ( IsDebug() ) printf(" The run should have already been handled by HandleRun() \n"); 
          //
        } else {
          //
          // go on with next header
          //
          pth = ptht;
        };
        //           
      };
      //
      if ( ptt+1 == rtev){  
        ptht++;
        if ( ptht < rhev ){
          rh->GetEntry(ptht);
          phh = ehh->GetPscuHeader();
          pkth = phh->GetCounter();
          obth = phh->GetOrbitalTime();
          cod = ehh->GetCounter();
          evbefh = cod->Get(pctp->Physics);
          if ( IsDebug() ) printf(" Piece of run at the end of file %i %i %i \n",pkth,obth,obtt);
          if ( IsDebug() ) printf(" ''''''''''''''''''''''''''''''' %i %i %i \n",ptht,pth,ptt); 
          if ( IsDebug() ) printf(" ''''''''''''''''''''''''''''''' %i \n",rhev); 
          //
          this->HandleRunFragments(false,true,evbefh,upperentry); 
        } else {
          //
          // check if we have a fragment with no header
          //
          if ( (UInt_t)evbeft < upperentry-1 ){
            if ( IsDebug() ) printf(" Piece of run at the end of the file with NO RUNHEADER!\n");
            //
            if ( (ptt-1) < 0 ) throw -15; // should never arrive here!
            rt->GetEntry(ptt-1);
            cod = eht->GetCounter();
            evbefh = cod->Get(pctp->Physics);
            rt->GetEntry(ptt);
            pht = eht->GetPscuHeader();   
            this->HandleRunFragments(true,true,evbefh,upperentry);
          };
        };
      };
      //
    };
  };
  // 
  return(signal);
};

/**
 * 
 *  Check if the run has already been inserted
 * 
 */
Bool_t PamelaDBOperations::IsRunAlreadyInserted(){
  //
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  //
  stringstream oss;
  oss.str("");
  //
  // the where clause is of the type: boot_number = _our_boot && (
  //                                  ( runhead_time >= (_our_runhead_time-10) &&  runtrail_time <= (_our_runtrail_time+10) &&
  //                                  ( runhead_obt >= _our_runheadobt || runhead_pkt >= _our_runheadpkt ) && 
  //                                  ( runtrail_obt >= _our_runtrailobt || runtrail_pkt >= _our_runtrailpkt ) ) 
  //                                  || 
  //                                  ( runhead_time <= _our_runhead_time &&  runtrail_time >= _our_runtrail_time) && 
  //                                  ( runhead_obt <= _our_runheadobt || runhead_pkt <= _our_runheadpkt ) && 
  //                                  ( runtrail_obt <= _our_runtrailobt || runtrail_pkt <= _our_runtrailpkt ) ) 
  //                                  )
  //
  oss << " SELECT ID,NEVENTS,TRK_CALIB_USED,PKT_COUNTER FROM GL_RUN WHERE "
      << " BOOT_NUMBER=" << this->GetBOOTnumber() << " AND ("
      << " (RUNHEADER_TIME>=" << (UInt_t)(glrun->GetRUNHEADER_TIME()-10) << " AND "
      << " RUNTRAILER_TIME<=" << (UInt_t)(glrun->GetRUNTRAILER_TIME()+10) << " AND ("
      << " RUNHEADER_OBT>=" << glrun->GetRUNHEADER_OBT() << " OR "
      << " RUNHEADER_PKT>=" << glrun->GetRUNHEADER_PKT() << ") AND ("
      << " RUNTRAILER_OBT<=" << glrun->GetRUNTRAILER_OBT() << " OR "
      << " RUNTRAILER_PKT<=" << glrun->GetRUNTRAILER_PKT() << ") ) OR "
      << " (RUNHEADER_TIME<=" << (UInt_t)glrun->GetRUNHEADER_TIME() << " AND "
      << " RUNTRAILER_TIME>=" << (UInt_t)glrun->GetRUNTRAILER_TIME() <<" AND ("
      << " RUNHEADER_OBT<=" << glrun->GetRUNHEADER_OBT() << " OR "
      << " RUNHEADER_PKT<=" << glrun->GetRUNHEADER_PKT() << ") AND ("
      << " RUNTRAILER_OBT>=" << glrun->GetRUNTRAILER_OBT() << " OR "
      << " RUNTRAILER_PKT>=" << glrun->GetRUNTRAILER_PKT() << ") ));";
  //
  if ( IsDebug() ) printf(" check if run has been inserted: query is \n %s \n",oss.str().c_str());
  result = conn->Query(oss.str().c_str());
  //
  if ( !result ) throw -4;
  //
  row = result->Next();
  //
  if ( !row ){
    if ( IsDebug() ) printf(" The run is new \n");
    if ( IsDebug() ) printf(" -> fill the DB \n");
    return(false); // the file has not been inserted in the DB, go on.
  };
  //
  Bool_t signal = true;
  //
  while ( row != NULL ){    
    if ( IsDebug() ) printf(" A run exists with runheader and runtrailer time and packets compatible with this one \n");
    //
    // the run has already been inserted 
    //
    //    return(true);  //<<<<<<<<<<<<<<<<<<<<<<<< patch follows, uncomment here
    //
    // PATCH!
    // we keep the processing run if (in order of growing importance) 1) we have the runtrailer while the old run doesn't have it 2) we have the runheader 
    // while the old run doesn't have it 3) we have more events than the old run 
    //
    if ( glrun->GetNEVENTS() > (UInt_t)atoll(row->GetField(1)) ){
      //
      if ( IsDebug() ) printf(" The new run has more events than the old one \n");
      glrun->DeleteRun(conn,(UInt_t)atoll(row->GetField(0)),"GL_RUN");
//       oss.str("");
//       oss << "DELETE FROM GL_RUN WHERE ID=" << row->GetField(0) <<";";
//       if ( IsDebug() ) printf(" delete the run entry: query is \n %s \n",oss.str().c_str());      
//       conn->Query(oss.str().c_str());
      if ( signal ) signal = false;
      goto gonext;      
      //
    } else if ( glrun->GetNEVENTS() < (UInt_t)atoll(row->GetField(1)) ){
      if ( IsDebug() ) printf(" The new run has less events than the old one \n");
      if ( IsDebug() ) printf(" The run is already inserted \n"); 
      goto gonext;
    };
    //
    if ( glrun->GetTRK_CALIB() && !(UInt_t)atoll(row->GetField(2)) ){
      //
      if ( IsDebug() ) printf(" The new run has the same number of events and the runheader the old one miss the runheader \n");
      //
      glrun->DeleteRun(conn,(UInt_t)atoll(row->GetField(0)),"GL_RUN");
//       oss.str("");
//       oss << "DELETE FROM GL_RUN WHERE ID=" << row->GetField(0) <<";";
//       if ( IsDebug() ) printf(" delete the run entry: query is \n %s \n",oss.str().c_str());      
//       conn->Query(oss.str().c_str());
      //
      if ( signal ) signal = false;
      goto gonext;
    } else if ( !glrun->GetTRK_CALIB() && (UInt_t)atoll(row->GetField(2)) ){
      if ( IsDebug() ) printf(" The new run has the same number of events but miss the runheader the old has the runheader \n");
      if ( IsDebug() ) printf(" The run is already inserted \n");
      goto gonext;
    };
    //
    if ( glrun->GetPKT_COUNTER() && !(UInt_t)atoll(row->GetField(3)) ){  
      //
      if ( IsDebug() ) printf(" The new run has the same number of events, the runheader and the runtrailer the old one miss the runtrailer \n");
      //
      glrun->DeleteRun(conn,(UInt_t)atoll(row->GetField(0)),"GL_RUN");
//       oss.str("");
//       oss << "DELETE FROM GL_RUN WHERE ID=" << row->GetField(0) <<";";
//       if ( IsDebug() ) printf(" delete the run entry: query is \n %s \n",oss.str().c_str());      
//       conn->Query(oss.str().c_str());
      if ( signal ) signal = false;
      //
    };
    //
  gonext:
    // END PATCH!
    //
    row = result->Next();
    //
  };
  //
  delete result;
  //
  if ( signal && IsDebug() ) printf(" The run has already been inserted \n");    
  return(signal);
};

/**
 * Handle runs which seems to be good ones.
 **/
void PamelaDBOperations::HandleRun(){  
  ULong64_t chkpkt = 0;  
  ULong64_t pktt = (ULong64_t)PKT(pht->GetCounter());
  ULong64_t pkth = (ULong64_t)PKT(phh->GetCounter());
  //
  chkpkt = pkth + (ULong64_t)runt->PKT_COUNTER + 1ULL + 1ULL;   
  //
  if ( labs(chkpkt-pktt)<2 ){ 
    //
    if ( IsDebug() ) printf(" check %llu pktt %llu \n",chkpkt,pktt); 
    //
    // it must be a good run, fill the db
    //
    this->FillClass();
    //
    if ( !IsRunAlreadyInserted() ){
      glrun->SetID(this->AssignRunID());
      glrun->SetID_RUN_FRAG(0);
      glrun->Fill_GL_RUN(conn);
    };
  } else {
    // 
    if ( IsDebug() ) printf(" oh no! the distance between runheader and runtrailer seems wrong: check %llu pktt %llu \n",chkpkt,pktt); 
    if ( IsDebug() ) printf(" try to recover run(s) without runheader and runtrailer between runheader and runtrailer\n"); 
    //
    this->HandleSuspiciousRun();
    //
  };
  //
  //
  return;
};


/**
 * Handle run fragments at the beginning or at the end of the file
 **/
void PamelaDBOperations::HandleRunFragments(Bool_t mishead, Bool_t mistrail, UInt_t firstev, UInt_t lastev){  
  //
  UInt_t rhfirstev = firstev;
  UInt_t rtlastev = lastev;
  Bool_t found = false;
  //
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  //
  stringstream oss;
  oss.str("");
  //
  // is the piece of run good (no other packets inside)?
  //
  if ( !this->IsRunConsistent(mishead,mistrail,firstev,lastev)){
    // 
    // if not, handle other pieces and continue with the first one
    //
    if ( IsDebug() ) printf("The run is not consistent, it contains non-physics packets! The run has been handled \n");
    //
  };
  //
  // we have now the good first piece of a run, fill the glrun object
  //
  if ( rhfirstev != firstev && !mishead ) mishead = true;
  if ( rtlastev != lastev && !mistrail ) mistrail = true;
  //
  this->FillClass(mishead,mistrail,firstev,lastev);
  //
  if ( IsDebug() ) printf("The run is good, is it the other piece in the GL_RUN_FRAGMENTS table?\n");
  //
  // can we find the other piece of the run in the GL_RUN_FRAGMENTS table?
  //    
  if ( mishead && rhfirstev == firstev ) { // look for runheader (only when at the beginning of the file, if at the end and the runh is 
                                           // missing it no way we can found a piece in the frag table
    //
    oss.str("");
    oss << " SELECT ID,TRK_CALIB_USED,RUNTRAILER_TIME,RUNTRAILER_OBT,RUNHEADER_PKT,RUNTRAILER_PKT FROM GL_RUN_FRAGMENTS WHERE "
        << " BOOT_NUMBER=" << this->GetBOOTnumber() << " AND "
        << " RUNHEADER_TIME <= " << (UInt_t)glrun->GetRUNHEADER_TIME() 
        << " ORDER BY RUNHEADER_TIME DESC LIMIT 1;"; // DESC NOT ASC!!
    //
    if ( IsDebug() ) printf(" look for runheader in the fragments table: query is \n %s \n",oss.str().c_str());
    result = conn->Query(oss.str().c_str());
    //
    if ( !result ) throw -4;
    //
    row = result->Next();
    //
    if ( !row ){
      if ( IsDebug() ) printf(" the corresponding piece has NOT been found \n");
      found = false;
    } else {
      //
      found = false; // default value
      //
      if ( IsDebug() ) printf(" Found a possible candidate, checking if it is the good one... \n");
      //
      // if we have both runheader and runtrailer we can check with pkt_counter:
      //
      if ( !mistrail && (UInt_t)atoll(row->GetField(1)) != 0 ){
        ULong64_t chkpkt = 0;  
        ULong64_t pktt = (ULong64_t)PKT(glrun->GetRUNTRAILER_PKT());
        ULong64_t pkth = (ULong64_t)PKT((UInt_t)atoll(row->GetField(4)));
        //
        chkpkt = pkth + (ULong64_t)glrun->GetPKT_COUNTER() + 1ULL + 1ULL;   
        //
        if ( labs(chkpkt-pktt)<2 ){ 
          //
          if ( IsDebug() ) printf(" FOUND!!! check %llu pktt %llu \n",chkpkt,pktt); 
          //
          found = true;
          //
        } else {
          //
          if ( IsDebug() ) printf(" The check with pkt counter failed: check %llu pktt %llu \n",chkpkt,pktt);       
          //
          found = false;
          //
        };
      };
      if ( !found ){
        //
        // if we arrive here we were not able to decide if the two pieces matches using only the pkt counter information, we must check times and obts
        //
        ULong64_t chkpkt1 = 0;
        ULong64_t orunh1 = (ULong64_t)PKT(glrun->GetRUNHEADER_PKT());
        ULong64_t dbrunt1 = (ULong64_t)PKT((UInt_t)atoll(row->GetField(5)));
        chkpkt1 = labs(orunh1-dbrunt1);
        //
        ULong64_t chkpkt2 = 0;
        ULong64_t orunh2 = (ULong64_t)OBT(glrun->GetRUNHEADER_OBT());
        ULong64_t dbrunt2 = (ULong64_t)OBT((UInt_t)atoll(row->GetField(3)));
        chkpkt2 = labs(orunh2-dbrunt2);
        //
        ULong64_t chkpkt3 = 0;
        ULong64_t orunh3 = (ULong64_t)(glrun->GetRUNHEADER_TIME());
        ULong64_t dbrunt3 = (ULong64_t)((UInt_t)atoll(row->GetField(2)));
        chkpkt3 = labs(orunh3-dbrunt3);
        //
        if ( (chkpkt1 < 200 || chkpkt2 < 20000) && chkpkt3 < 20 ){
          //    if ( chkpkt1 < 100 && chkpkt2 < 30000 && chkpkt3 < 30 ){
          //
          if ( IsDebug() ) printf(" FOUND!!! check1 %llu<200 cechk2 %llu<20000 check3 %llu<20 \n",chkpkt1,chkpkt2,chkpkt3); 
          //
          found = true;
          //
        } else {
          //
          if ( IsDebug() ) printf(" Check failed: check1 %llu<200? cechk2 %llu<20000? check3 %llu<20? \n",chkpkt1,chkpkt2,chkpkt3); 
          //
          found = false;
          //
        };
      };
    };
    //
    if ( found ){
      // 
      // we have found the missing piece, glue the two together, merge the informations, fill the gl_run table (check first runs do not exists), delete entry in frag table
      // 
      if ( IsDebug() ) printf(" now you can handle the piece of the run \n "); 
      //
      GL_RUN *glrun1 = new GL_RUN();
      //
      UInt_t idfrag = (UInt_t)atoll(row->GetField(0));
      //
      oss.str("");
      oss << " ID="<<row->GetField(0)<<";";
      //
      glrun1->Query_GL_RUN_FRAGMENTS(oss.str().c_str(),conn); // here we have runheader infos
      //
      // merge infos
      //
      UInt_t apkt = PKT(glrun1->GetRUNTRAILER_PKT());
      ULong64_t aobt = OBT(glrun1->GetRUNTRAILER_OBT());
      UInt_t bpkt = PKT(glrun->GetRUNHEADER_PKT());
      ULong64_t bobt = OBT(glrun->GetRUNHEADER_OBT());
      if ( IsDebug() ) printf(" Check overlapping events: %i %i %llu %llu firstev is %i\n",apkt,bpkt,aobt,bobt,firstev);
      TTree *T= 0;
      T = (TTree*)file->Get("Physics");
      if ( !T || T->IsZombie() ) throw -16;
      EventHeader *eh = 0;
      PscuHeader *ph = 0;
      T->SetBranchAddress("Header", &eh);
      while ( apkt > bpkt && aobt > bobt && firstev < lastev ){
        T->GetEntry(firstev);
        ph = eh->GetPscuHeader();
        bpkt = PKT(ph->GetCounter());
        bobt = OBT(ph->GetOrbitalTime());       
        firstev++;
      };
      if ( IsDebug() ) printf(" Check overlapping events done: %i %i %llu %llu firstev is %i\n",apkt,bpkt,aobt,bobt,firstev);
      //
      glrun1->SetPKT_COUNTER(glrun->GetPKT_COUNTER());
      glrun1->SetPKT_READY_COUNTER(glrun->GetPKT_READY_COUNTER());
      glrun1->SetRUNTRAILER_TIME(glrun->GetRUNTRAILER_TIME());
      glrun1->SetRUNTRAILER_OBT(glrun->GetRUNTRAILER_OBT());
      glrun1->SetRUNTRAILER_PKT(glrun->GetRUNTRAILER_PKT());
      //
      glrun->SetEV_FROM(firstev);
      glrun->SetNEVENTS(lastev-firstev+1);
      glrun->SetRUNHEADER_TIME(glrun1->GetRUNHEADER_TIME());
      glrun->SetRUNHEADER_OBT(glrun1->GetRUNHEADER_OBT());
      glrun->SetRUNHEADER_PKT(glrun1->GetRUNHEADER_PKT());
      glrun->SetCOMPILATIONTIMESTAMP(glrun1->GetCOMPILATIONTIMESTAMP());
      glrun->SetFAV_WRK_SCHEDULE(glrun1->GetFAV_WRK_SCHEDULE());
      glrun->SetEFF_WRK_SCHEDULE(glrun1->GetEFF_WRK_SCHEDULE());
      glrun->SetPRH_VAR_TRG_MODE_A(glrun1->GetPRH_VAR_TRG_MODE_A());
      glrun->SetPRH_VAR_TRG_MODE_B(glrun1->GetPRH_VAR_TRG_MODE_B());
      glrun->SetACQ_BUILD_INFO(glrun1->GetACQ_BUILD_INFO());
      glrun->SetACQ_VAR_INFO(glrun1->GetACQ_VAR_INFO());
      glrun->SetRM_ACQ_AFTER_CALIB(glrun1->GetRM_ACQ_AFTER_CALIB());  
      glrun->SetRM_ACQ_SETTING_MODE(glrun1->GetRM_ACQ_SETTING_MODE());  
      glrun->SetTRK_CALIB_USED(glrun1->GetTRK_CALIB_USED());  
      glrun->SetCAL_DSP_MASK(glrun1->GetCAL_DSP_MASK());  
      glrun->SetLAST_TIMESYNC(glrun1->GetLAST_TIMESYNC());  
      glrun->SetOBT_TIMESYNC(glrun1->GetOBT_TIMESYNC());  
      //
      if ( !IsRunAlreadyInserted() ){
        //
        glrun->SetID(this->AssignRunID());
        glrun->SetID_RUN_FRAG(glrun1->GetID());
        glrun->Fill_GL_RUN(conn);
        //
        // get id number
        //
//      oss.str("");
//      oss << " SELECT ID FROM GL_RUN WHERE BOOT_NUMBER="<<this->GetBOOTnumber()<<" AND "
//          << " RUNHEADER_OBT="<<glrun->GetRUNHEADER_OBT()<<" AND "
//          << " RUNTRAILER_OBT="<<glrun->GetRUNTRAILER_OBT()<<";";
//      //
//      if ( IsDebug() ) printf(" search for idrun0 , query is : \n%s\n",oss.str().c_str());
//      //
//      result =  conn->Query(oss.str().c_str());
//      if ( !result ) throw -4;
//      row = result->Next();
//      //
//      UInt_t idrun0 = 0;
//      if ( !row ){
//        throw -10;
//      } else {
//        idrun0 = (UInt_t)atoll(row->GetField(0));
//      };
        //
        //      glrun1->SetID_RUN_FRAG(idrun0);
        glrun1->SetID_RUN_FRAG(glrun->GetID());
        glrun1->Fill_GL_RUN(conn);
        //
//      oss.str("");
//      oss << " SELECT ID FROM GL_RUN WHERE ID_RUN_FRAG="<<idrun0<<" ;";
//      //
//      if ( IsDebug() ) printf(" search for idrun1 , query is : \n%s\n",oss.str().c_str());
//      //
//      result =  conn->Query(oss.str().c_str());
//      if ( !result ) throw -4;
//      row = result->Next();
//      //
//      UInt_t idrun1 = 0;
//      if ( !row ){
//        throw -10;
//      } else {
//        idrun1 = (UInt_t)atoll(row->GetField(0));
//      };
//      //
//      oss.str("");
//      oss << " UPDATE GL_RUN SET ID_RUN_FRAG="<<idrun1<<" WHERE ID="<<idrun0<<" ;";
//      //
//      result =  conn->Query(oss.str().c_str());
//      if ( !result ) throw -4;
        //
      };
      //
      delete glrun1;
      //
      // delete old entry in fragment table
      //
      glrun->DeleteRun(conn,idfrag,"GL_RUN_FRAGMENTS");
//       oss.str("");
//       //
//       oss << " DELETE FROM GL_RUN_FRAGMENTS WHERE ID = " << idfrag << ";";
//       //
//       if ( IsDebug() ) printf(" Delete from frag table the old run :\n query is \n %s \n",oss.str().c_str());
//       result =  conn->Query(oss.str().c_str());
//       if ( !result ) throw -4;
      //
      return;
      //
    };
    //
  };
  //
  if ( mistrail && rtlastev == lastev ) { // look for runtrailer (only when at the end of the file, if at the beginning and the runh is 
                                           // missing it no way we can found a piece in the frag table
    //
    oss.str("");
    oss << " SELECT ID,PKT_COUNTER,RUNHEADER_TIME,RUNHEADER_OBT,RUNTRAILER_PKT,RUNHEADER_PKT FROM GL_RUN_FRAGMENTS WHERE "
        << " BOOT_NUMBER=" << this->GetBOOTnumber() << " AND "
        << " RUNTRAILER_TIME >= " << (UInt_t)glrun->GetRUNTRAILER_TIME() 
        << " ORDER BY RUNTRAILER_TIME ASC LIMIT 1;";
    //
    if ( IsDebug() ) printf(" look for runtrailer in the fragments table: query is \n %s \n",oss.str().c_str());
    result = conn->Query(oss.str().c_str());
    //
    if ( !result ) throw -4;
    //
    row = result->Next();
    //
    if ( !row ){
      if ( IsDebug() ) printf(" the corresponding piece has NOT been found \n");
      found = false;
    } else {
      //
      found = false; // default value
      //
      if ( IsDebug() ) printf(" Found a possible candidate, checking if it is the good one... \n");
      //
      // if we have both runheader and runtrailer we can check with pkt_counter:
      //
      if ( !mishead && (UInt_t)atoll(row->GetField(1)) != 0 ){
        ULong64_t chkpkt = 0;  
        ULong64_t pktt = (ULong64_t)PKT((UInt_t)atoll(row->GetField(4)));
        ULong64_t pkth = (ULong64_t)PKT(glrun->GetRUNHEADER_PKT());
        //
        chkpkt = pkth + (ULong64_t)((UInt_t)atoll(row->GetField(1))) + 1ULL + 1ULL;   
        //
        if ( labs(chkpkt-pktt)<2 ){ 
          //
          if ( IsDebug() ) printf(" FOUND!!! check %llu pktt %llu \n",chkpkt,pktt); 
          //
          found = true;
          //
        } else {
          //
          if ( IsDebug() ) printf(" The check with pkt counter failed: check %llu pktt %llu \n",chkpkt,pktt);       
          //
          found = false;
          //
        };
      };
      if ( !found ){
        //
        // if we arrive here we were not able to decide if the two pieces matches using only the pkt counter information, we must check times and obts
        //
        ULong64_t chkpkt1 = 0;
        ULong64_t orunh1 = (ULong64_t)PKT(glrun->GetRUNTRAILER_PKT());
        ULong64_t dbrunt1 = (ULong64_t)PKT((UInt_t)atoll(row->GetField(5)));
        chkpkt1 = labs(orunh1-dbrunt1);
        //
        ULong64_t chkpkt2 = 0;
        ULong64_t orunh2 = (ULong64_t)OBT(glrun->GetRUNTRAILER_OBT());
        ULong64_t dbrunt2 = (ULong64_t)OBT((UInt_t)atoll(row->GetField(3)));
        chkpkt2 = labs(orunh2-dbrunt2);
        //
        ULong64_t chkpkt3 = 0;
        ULong64_t orunh3 = (ULong64_t)(glrun->GetRUNTRAILER_TIME());
        ULong64_t dbrunt3 = (ULong64_t)((UInt_t)atoll(row->GetField(2)));
        chkpkt3 = labs(orunh3-dbrunt3);
        //
        if ( (chkpkt1 < 200 || chkpkt2 < 20000) && chkpkt3 < 20 ){
          //
          if ( IsDebug() ) printf(" FOUND!!! check1 %llu<200 cechk2 %llu<20000 check3 %llu<20 \n",chkpkt1,chkpkt2,chkpkt3); 
          //
          found = true;
          //
        } else {
          //
          if ( IsDebug() ) printf(" Check failed: check1 %llu<200? cechk2 %llu<20000? check3 %llu<20? \n",chkpkt1,chkpkt2,chkpkt3); 
          //
          found = false;
          //
        };
      };
    };
    //
    if ( found ){
      // 
      // we have found the missing piece, glue the two together, merge the informations, fill the gl_run table (check first runs do not exists), delete entry in frag table
      // 
      if ( IsDebug() ) printf(" now you can handle the piece of the run \n "); 
      //
      GL_RUN *glrun1 = new GL_RUN();
      //
      UInt_t idfrag = (UInt_t)atoll(row->GetField(0));
      //
      oss.str("");
      oss << " ID="<<row->GetField(0)<<";";
      //
      glrun1->Query_GL_RUN_FRAGMENTS(oss.str().c_str(),conn); // here we have runtrailer infos
      //
      // merge infos
      //
      UInt_t apkt = PKT(glrun->GetRUNTRAILER_PKT());
      ULong64_t aobt = OBT(glrun->GetRUNTRAILER_OBT());
      UInt_t bpkt = PKT(glrun1->GetRUNHEADER_PKT());
      ULong64_t bobt = OBT(glrun1->GetRUNHEADER_OBT());
      if ( IsDebug() ) printf(" Check overlapping events: %i %i %llu %llu lastev is %i\n",apkt,bpkt,aobt,bobt,lastev);
      TTree *T= 0;
      T = (TTree*)file->Get("Physics");
      if ( !T || T->IsZombie() ) throw -16;
      EventHeader *eh = 0;
      PscuHeader *ph = 0;
      T->SetBranchAddress("Header", &eh);
      while ( apkt > bpkt && aobt > bobt && lastev > 0 ){
        T->GetEntry(lastev);
        ph = eh->GetPscuHeader();
        apkt = PKT(ph->GetCounter());
        aobt = OBT(ph->GetOrbitalTime());       
        lastev--;
      };
      if ( IsDebug() ) printf(" Check overlapping events done: %i %i %llu %llu lastev is %i\n",apkt,bpkt,aobt,bobt,lastev);
      //
      glrun->SetEV_TO(lastev);
      glrun->SetNEVENTS(lastev-firstev+1);
      glrun->SetPKT_COUNTER(glrun1->GetPKT_COUNTER());
      glrun->SetPKT_READY_COUNTER(glrun1->GetPKT_READY_COUNTER());
      glrun->SetRUNTRAILER_TIME(glrun1->GetRUNTRAILER_TIME());
      glrun->SetRUNTRAILER_OBT(glrun1->GetRUNTRAILER_OBT());
      glrun->SetRUNTRAILER_PKT(glrun1->GetRUNTRAILER_PKT());
      //
      glrun1->SetRUNHEADER_TIME(glrun->GetRUNHEADER_TIME());
      glrun1->SetRUNHEADER_OBT(glrun->GetRUNHEADER_OBT());
      glrun1->SetRUNHEADER_PKT(glrun->GetRUNHEADER_PKT());
      glrun1->SetCOMPILATIONTIMESTAMP(glrun->GetCOMPILATIONTIMESTAMP());
      glrun1->SetFAV_WRK_SCHEDULE(glrun->GetFAV_WRK_SCHEDULE());
      glrun1->SetEFF_WRK_SCHEDULE(glrun->GetEFF_WRK_SCHEDULE());
      glrun1->SetPRH_VAR_TRG_MODE_A(glrun->GetPRH_VAR_TRG_MODE_A());
      glrun1->SetPRH_VAR_TRG_MODE_B(glrun->GetPRH_VAR_TRG_MODE_B());
      glrun1->SetACQ_BUILD_INFO(glrun->GetACQ_BUILD_INFO());
      glrun1->SetACQ_VAR_INFO(glrun->GetACQ_VAR_INFO());
      glrun1->SetRM_ACQ_AFTER_CALIB(glrun->GetRM_ACQ_AFTER_CALIB());  
      glrun1->SetRM_ACQ_SETTING_MODE(glrun->GetRM_ACQ_SETTING_MODE());  
      glrun1->SetTRK_CALIB_USED(glrun->GetTRK_CALIB_USED());  
      glrun1->SetCAL_DSP_MASK(glrun->GetCAL_DSP_MASK());  
      glrun1->SetLAST_TIMESYNC(glrun->GetLAST_TIMESYNC());  
      glrun1->SetOBT_TIMESYNC(glrun->GetOBT_TIMESYNC());  
      //
      if ( !IsRunAlreadyInserted() ){
        //
        glrun->SetID(this->AssignRunID());
        //
        glrun->SetID_RUN_FRAG(glrun1->GetID());
        glrun->Fill_GL_RUN(conn);
        //
        // get id number
        //
//      oss.str("");
//      oss << " SELECT ID FROM GL_RUN WHERE BOOT_NUMBER="<<this->GetBOOTnumber()<<" AND "
//          << " RUNHEADER_OBT="<<glrun->GetRUNHEADER_OBT()<<" AND "
//          << " RUNTRAILER_OBT="<<glrun->GetRUNTRAILER_OBT()<<";";
//      //
//      if ( IsDebug() ) printf(" search for idrun0 , query is : \n%s\n",oss.str().c_str());
//      //
//      result =  conn->Query(oss.str().c_str());
//      if ( !result ) throw -4;
//      row = result->Next();
//      //
//      UInt_t idrun0 = 0;
//      if ( !row ){
//        throw -10;
//      } else {
//        idrun0 = (UInt_t)atoll(row->GetField(0));
//      };
//      //
//      glrun1->SetID_RUN_FRAG(idrun0);
//
        glrun1->SetID_RUN_FRAG(glrun->GetID());
        glrun1->Fill_GL_RUN(conn);
        //
//      oss.str("");
//      oss << " SELECT ID FROM GL_RUN WHERE ID_RUN_FRAG="<<idrun0<<" ;";
//      //
//      if ( IsDebug() ) printf(" search for idrun1 , query is : \n%s\n",oss.str().c_str());
//      //
//      result =  conn->Query(oss.str().c_str());
//      if ( !result ) throw -4;
//      row = result->Next();
//      //
//      UInt_t idrun1 = 0;
//      if ( !row ){
//        throw -10;
//      } else {
//        idrun1 = (UInt_t)atoll(row->GetField(0));
//      };
//      //
//      oss.str("");
//      oss << " UPDATE GL_RUN SET ID_RUN_FRAG="<<idrun1<<" WHERE ID="<<idrun0<<" ;";
//      //
//      result =  conn->Query(oss.str().c_str());
//      if ( !result ) throw -4;
        //
      };
      //
      delete glrun1;
      //
      // delete old entry in fragment table
      //
      glrun->DeleteRun(conn,idfrag,"GL_RUN_FRAGMENTS");
//       oss.str("");
//       //
//       oss << " DELETE FROM GL_RUN_FRAGMENTS WHERE ID = " << idfrag << ";";
//       //
//       if ( IsDebug() ) printf(" Delete from frag table the old run :\n query is \n %s \n",oss.str().c_str());
//       result =  conn->Query(oss.str().c_str());
//       if ( !result ) throw -4;
      //
      //
      return;
      //
    };
    //
  };
  //
  if ( !found ){      
    //
    if ( IsDebug() ) printf(" not found, check if we have already processed the file \n "); 
    //
    // not found, has this run already inserted in the GL_RUN or in the GL_RUN_FRAGMENTS table?
    //
    oss.str("");
    oss << " SELECT ID FROM GL_RUN WHERE "
        << " BOOT_NUMBER=" << this->GetBOOTnumber() << " AND ("
        << " (RUNHEADER_TIME>=" << (UInt_t)(glrun->GetRUNHEADER_TIME()-10) << " AND "
        << " RUNTRAILER_TIME<=" << (UInt_t)(glrun->GetRUNTRAILER_TIME()+10) << " AND ("
        << " RUNHEADER_OBT>=" << glrun->GetRUNHEADER_OBT() << " OR "
        << " RUNHEADER_PKT>=" << glrun->GetRUNHEADER_PKT() << ") AND ("
        << " RUNTRAILER_OBT<=" << glrun->GetRUNTRAILER_OBT() << " OR "
        << " RUNTRAILER_PKT<=" << glrun->GetRUNTRAILER_PKT() << ") ) OR "
        << " (RUNHEADER_TIME<=" << (UInt_t)glrun->GetRUNHEADER_TIME() << " AND "
        << " RUNTRAILER_TIME>=" << (UInt_t)glrun->GetRUNTRAILER_TIME() <<" AND ("
        << " RUNHEADER_OBT<=" << glrun->GetRUNHEADER_OBT() << " OR "
        << " RUNHEADER_PKT<=" << glrun->GetRUNHEADER_PKT() << ") AND ("
        << " RUNTRAILER_OBT>=" << glrun->GetRUNTRAILER_OBT() << " OR "
        << " RUNTRAILER_PKT>=" << glrun->GetRUNTRAILER_PKT() << ") ));";
    //
    if ( IsDebug() ) printf(" check if run has been inserted: query is \n %s \n",oss.str().c_str());
    result = conn->Query(oss.str().c_str());
    //
    if ( !result ) throw -4;
    //
    row = result->Next();
    //
    if ( !row ){
      //
      oss.str("");
      oss << " SELECT ID FROM GL_RUN_FRAGMENTS WHERE "
          << " BOOT_NUMBER=" << this->GetBOOTnumber() << " AND ("
          << " (RUNHEADER_TIME>=" << (UInt_t)(glrun->GetRUNHEADER_TIME()-10) << " AND "
          << " RUNTRAILER_TIME<=" << (UInt_t)(glrun->GetRUNTRAILER_TIME()+10) << " AND ("
          << " RUNHEADER_OBT>=" << glrun->GetRUNHEADER_OBT() << " OR "
          << " RUNHEADER_PKT>=" << glrun->GetRUNHEADER_PKT() << ") AND ("
          << " RUNTRAILER_OBT<=" << glrun->GetRUNTRAILER_OBT() << " OR "
          << " RUNTRAILER_PKT<=" << glrun->GetRUNTRAILER_PKT() << ") ) OR "
          << " (RUNHEADER_TIME<=" << (UInt_t)glrun->GetRUNHEADER_TIME() << " AND "
          << " RUNTRAILER_TIME>=" << (UInt_t)glrun->GetRUNTRAILER_TIME() <<" AND ("
          << " RUNHEADER_OBT<=" << glrun->GetRUNHEADER_OBT() << " OR "
          << " RUNHEADER_PKT<=" << glrun->GetRUNHEADER_PKT() << ") AND ("
          << " RUNTRAILER_OBT>=" << glrun->GetRUNTRAILER_OBT() << " OR "
          << " RUNTRAILER_PKT>=" << glrun->GetRUNTRAILER_PKT() << ") ));";
      //
      if ( IsDebug() ) printf(" check if run has been inserted: query is \n %s \n",oss.str().c_str());
      result = conn->Query(oss.str().c_str());
      //
      if ( !result ) throw -4;
      //
      row = result->Next();
      //
      if ( !row ){
        //
        // no, insert this run in the GL_RUN_FRAGMENTS table (check if exist before!)
        //
        if ( IsDebug() ) printf(" The run is new \n");
        if ( IsDebug() ) printf(" -> fill the GL_RUNFRAGMENTS table \n");
        //
        glrun->SetID(this->AssignRunID());
        glrun->SetID_RUN_FRAG(0);
        glrun->Fill_GL_RUN_FRAGMENTS(conn);
        //
      } else {
        if ( IsDebug() ) printf(" The run is already present in the fragment table \n");
      };
    } else {
      if ( IsDebug() ) printf(" The run is already present in the GL_RUN table \n");
    };
  };
  //
  return;
};


/**
 * Handle run without header or trailer
 **/
void PamelaDBOperations::HandleMissingHoT(Bool_t mishead, Bool_t mistrail, UInt_t firstev, UInt_t lastev){  
  //
  //
  // is the piece of run good (no other packets inside)?
  //
  if ( !this->IsRunConsistent(mishead,mistrail,firstev,lastev)){
    // 
    // if not, handle other pieces and continue with the first one
    //
    if ( IsDebug() ) printf("The run is not consistent, it contains non-physics packets! The run has been handled \n");
    //
  } else {
    //
    this->FillClass(mishead,mistrail,firstev,lastev);
    //
    if ( !IsRunAlreadyInserted() ){
      glrun->SetID(this->AssignRunID());
      glrun->SetID_RUN_FRAG(0);
      glrun->Fill_GL_RUN(conn);    
    };
    //
  };
  //
  return;
};

/**
 * 
 * check if we have non-physics packets inside the run
 * 
 */
Bool_t PamelaDBOperations::IsRunConsistent(Bool_t mishead, Bool_t mistrail, UInt_t &firstev, UInt_t &lastev){
  //  
  EventCounter *code=0;
  //
  UInt_t nevent = 0;
  UInt_t checkfirst = 0;
  UInt_t checklast = 0;
  UInt_t firstentry = 0;
  UInt_t lastentry = 0;
  UInt_t firstTime = 0;
  UInt_t lastTime = 0;
  UInt_t firstPkt = 0;
  UInt_t lastPkt = 0;
  UInt_t firstObt = 0;
  UInt_t lastObt = 0;
  //
  pcksList packetsNames;
  pcksList::iterator Iter;
  getPacketsNames(packetsNames);
  //
  TTree *T= 0;
  T =(TTree*)file->Get("Physics");
  if ( !T || T->IsZombie() ) throw -16;
  EventHeader *eh = 0;
  PscuHeader *ph = 0;
  T->SetBranchAddress("Header", &eh);
  nevent = T->GetEntries();
  //
  //
  if ( firstev == lastev+1 ) { // no events inside the run!
    if ( IsDebug() ) printf(" Checking but no events in the run! \n");
    // return true is correct
    return(true);
    //
  } else {
    //
    T->GetEntry(firstev);
    code = eh->GetCounter();
    checkfirst = 0;
    for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
       if ( strcmp(*Iter,"Physics") ) checkfirst += code->Get(GetPacketType(*Iter));
     };
    if ( IsDebug() ) printf(" Check first is %i firstev is %i\n",checkfirst,firstev);
    //
    T->GetEntry(lastev);
    code = eh->GetCounter();
    checklast = 0;
    for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
       if ( strcmp(*Iter,"Physics") ) checklast += code->Get(GetPacketType(*Iter));
     };
    if ( IsDebug() ) printf(" Check last is %i lastev is %i\n",checklast,lastev);
    //
    if ( checkfirst == checklast ){ 
      //
      if ( IsDebug() ) printf(" No packets but physics inside the run, I will consider it as good\n");
      //
      return(true);
      //
    } else {
      //
      if ( IsDebug() ) printf(" There are no-phyics packets inside the run!\n");
      //
      // HERE WE MUST HANDLE THAT RUNS AND GO BACK
      //
      if ( IsDebug() ) printf(" Never seen this case, try to handle it anyway, it was throw -95\n");
      //
      Bool_t emptyruns = false;
      UInt_t check = 0;
      UInt_t lastevtemp = lastev;
      UInt_t firstevno = firstev;
      //
      for (UInt_t i=firstev; i<=lastev; i++){
        //
        T->GetEntry(i);
        code = eh->GetCounter();
        //
        check = 0;
        //
        for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
          if ( strcmp(*Iter,"Physics") ) check += code->Get(GetPacketType(*Iter));
        };
        //
        if ( checkfirst < check || i == lastev ){
          //
          firstentry = firstevno;
          //
          if ( checkfirst < check ){
            lastentry = i-1;
          } else {
            lastentry = i;
          };
          //
          if ( IsDebug() ) printf(" Run between %i and %i entries\n",firstentry,lastentry);
          //
          glrun->SetEV_FROM(firstentry);
          glrun->SetEV_TO(lastentry);
          if ( lastentry == (firstentry-1) ){ // no physics packets inside physics run with no runheader no runtrailer
            if ( IsDebug() ) printf(" no physics packets inside physics run with no runheader no runtrailer\n");
            lastentry--;
          };
          glrun->SetNEVENTS(lastentry-firstentry+1);
          //
          glrun->Set_GL_RUNH0();
          glrun->Set_GL_RUNT0();
          //
          glrun->SetLAST_TIMESYNC(0);
          glrun->SetOBT_TIMESYNC(0);
          //
          T->GetEntry(firstentry);
          ph = eh->GetPscuHeader();             
          firstObt = ph->GetOrbitalTime();
          firstTime = this->GetAbsTime(firstObt);
          firstPkt = ph->GetCounter();           
          //
          T->GetEntry(lastentry);
          ph = eh->GetPscuHeader();     
          lastObt = ph->GetOrbitalTime();
          lastTime = this->GetAbsTime(lastObt);
          lastPkt = ph->GetCounter();            
          //
          glrun->SetRUNHEADER_PKT(firstPkt);
          glrun->SetRUNTRAILER_PKT(lastPkt);
          //
          glrun->SetRUNHEADER_OBT(firstObt);
          glrun->SetRUNTRAILER_OBT(lastObt);
          //
          if ( firstev == firstentry && !emptyruns && !mishead ){
            glrun->Set_GL_RUNH(runh,phh);
            firstTime = this->GetAbsTime(phh->GetOrbitalTime());
            if ( IsDebug() ) printf(" We have the runheader \n");
          };
          if ( lastev == i && !mistrail ){
            glrun->Set_GL_RUNT(runt,pht);
            lastTime = this->GetAbsTime(pht->GetOrbitalTime());
            if ( IsDebug() ) printf(" We have the runtrailer \n");
          };
          //
          if ( lastentry == (firstentry-2) ){ // no events in the run
            emptyruns = true;
            if ( IsDebug() ) printf(" No events in the run \n");
            lastTime = firstTime;
            if ( (UInt_t)firstTime == this->GetAbsTime(phh->GetOrbitalTime()) ){
              lastObt = glrun->RUNHEADER_OBT;
              lastPkt = glrun->RUNHEADER_PKT;
            } else {
              lastObt = firstObt;
              lastPkt = firstPkt;
            };
            glrun->SetRUNTRAILER_PKT(lastPkt);
            glrun->SetRUNTRAILER_OBT(lastObt);
            lastentry++;
          };
          //      
          this->SetCommonGLRUN(firstTime,lastTime);
          //
          if ( !IsRunAlreadyInserted() ){
            glrun->SetID(this->AssignRunID());
            glrun->SetID_RUN_FRAG(0);
            glrun->Fill_GL_RUN(conn);      
          };
          //
          firstevno = lastentry + 1;
          //
          checkfirst = check;
          //
        };
        //
        if ( check == checklast && i != lastev ){
          lastevtemp = i - 1;
          i = lastev - 1; 
        };
        //
      };
      //
      lastev = lastevtemp;
      //
      return(false);
      //
    };
  };
  //
  return(false); // should never arrive here
};

/**
 * 
 * we end up here when we have a runheader and a runtrailer but they seems not belonging to the same run since the number of events does not coincide with the
 * number of event written in the runtrailer. We try to split into different runs scanning the physics events from the runheader to the runtrailer and
 * looking for non-physics packets inside.
 * 
 */
void PamelaDBOperations::HandleSuspiciousRun(){
  //  
  PacketType *pctp=0;
  EventCounter *codt=0;
  EventCounter *codh=0;
  EventCounter *code=0;
  UInt_t firstev = 0;
  UInt_t lastev = 0;
  UInt_t nevent = 0;
  UInt_t checkfirst = 0;
  UInt_t checklast = 0;
  UInt_t firstentry = 0;
  UInt_t lastentry = 0;
  UInt_t firstTime = 0;
  UInt_t lastTime = 0;
  UInt_t firstPkt = 0;
  UInt_t lastPkt = 0;
  UInt_t firstObt = 0;
  UInt_t lastObt = 0;
  //
  pcksList packetsNames;
  pcksList::iterator Iter;
  getPacketsNames(packetsNames);
  //
  TTree *rh=0;
  rh = (TTree*)file->Get("RunHeader");
  if ( !rh || rh->IsZombie() ) throw -17;
  TTree *T=0;
  T =(TTree*)file->Get("Physics");
  if ( !T || T->IsZombie() ) throw -16;
  EventHeader *eh = 0;
  PscuHeader *ph = 0;
  T->SetBranchAddress("Header", &eh);
  nevent = T->GetEntries();
  //
  codt = eht->GetCounter();
  codh = ehh->GetCounter();
  firstev = codh->Get(pctp->Physics);
  lastev = codt->Get(pctp->Physics)-1;
  //
  if ( firstev == lastev+1 ) { // no events inside the run!
    if ( IsDebug() ) printf(" Checking but no events in the run! \n");
    //
    this->FillClass();
    if ( !IsRunAlreadyInserted() ){
      glrun->SetID(this->AssignRunID());
      glrun->SetID_RUN_FRAG(0);
      glrun->Fill_GL_RUN(conn);    
    };
    //
  } else {
    //
    UInt_t nrunh  = 0;
    UInt_t nrunh1 = 0;
    T->GetEntry(firstev);
    code = eh->GetCounter();
    checkfirst = 0;
    for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
       if ( strcmp(*Iter,"Physics") ) checkfirst += code->Get(GetPacketType(*Iter));
       if ( !strcmp(*Iter,"RunHeader") ) nrunh1++;
     };
    if ( IsDebug() ) printf(" Check first is %i \n",checkfirst);
    //
    T->GetEntry(lastev);
    code = eh->GetCounter();
    checklast = 0;
    for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
       if ( strcmp(*Iter,"Physics") ) checklast += code->Get(GetPacketType(*Iter));
     };
    if ( IsDebug() ) printf(" Check last is %i \n",checklast);
    //
    if ( checkfirst == checklast ){ 
      //
      if ( IsDebug() ) printf(" No packets but physics inside the run, I will consider it as good\n");
      //
      this->FillClass();
      if ( !IsRunAlreadyInserted() ){
        glrun->SetID(this->AssignRunID());
        glrun->SetID_RUN_FRAG(0);
        glrun->Fill_GL_RUN(conn);         
      };
      //
    } else {
      //
      if ( IsDebug() ) printf(" There are no-physics packets inside the run, try to separate runs \n");
      //
      Bool_t emptyruns = false;
      UInt_t check = 0;
      UInt_t firstevno = firstev;
      //
      for (UInt_t i=firstev; i<=lastev; i++){
        //
        T->GetEntry(i);
        code = eh->GetCounter();
        //
        check = 0;
        //
        for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
          if ( strcmp(*Iter,"Physics") ) check += code->Get(GetPacketType(*Iter));
          if ( !strcmp(*Iter,"RunHeader") ) nrunh++;
        };
        //
        if ( checkfirst < check || i == lastev ){
          //
          firstentry = firstevno;
          //
          if ( checkfirst < check ){
            lastentry = i-1;
          } else {
            lastentry = i;
          };
          //
          if ( IsDebug() ) printf(" Run between %i and %i entries\n",firstentry,lastentry);
          //
          glrun->SetEV_FROM(firstentry);
          glrun->SetEV_TO(lastentry);
          if ( lastentry == (firstentry-1) ){ // no physics packets inside physics run with no runheader no runtrailer
            if ( IsDebug() ) printf(" no physics packets inside physics run with no runheader no runtrailer\n");
            lastentry--;
          };
          glrun->SetNEVENTS(lastentry-firstentry+1);
          //
          glrun->Set_GL_RUNH0();
          glrun->Set_GL_RUNT0();
          //
          glrun->SetLAST_TIMESYNC(0);
          glrun->SetOBT_TIMESYNC(0);
          //
          T->GetEntry(firstentry);
          ph = eh->GetPscuHeader();             
          firstObt = ph->GetOrbitalTime();
          firstTime = this->GetAbsTime(firstObt);
          firstPkt = ph->GetCounter();           
          //
          T->GetEntry(lastentry);
          ph = eh->GetPscuHeader();     
          lastObt = ph->GetOrbitalTime();
          lastTime = this->GetAbsTime(lastObt);
          lastPkt = ph->GetCounter();            
          //
          glrun->SetRUNHEADER_PKT(firstPkt);
          glrun->SetRUNTRAILER_PKT(lastPkt);
          //
          glrun->SetRUNHEADER_OBT(firstObt);
          glrun->SetRUNTRAILER_OBT(lastObt);
          //
          if ( (firstev == firstentry && !emptyruns) || nrunh == (nrunh1 + 1) ){
            rh->GetEntry(nrunh1-1);
            phh = ehh->GetPscuHeader();
            nrunh1++;
            glrun->Set_GL_RUNH(runh,phh);
            firstTime = this->GetAbsTime(phh->GetOrbitalTime());
            if ( IsDebug() ) printf(" We have the runheader \n");
          };
          if ( lastev == i && checkfirst == check ){
            glrun->Set_GL_RUNT(runt,pht);
            lastTime = this->GetAbsTime(pht->GetOrbitalTime());
            if ( IsDebug() ) printf(" We have the runtrailer \n");
          };
          //
          if ( lastentry == (firstentry-2) ){ // no events in the run
            emptyruns = true;
            if ( IsDebug() ) printf(" No events in the run \n");
            lastTime = firstTime;
            if ( (UInt_t)firstTime == this->GetAbsTime(phh->GetOrbitalTime()) ){
              lastObt = glrun->RUNHEADER_OBT;
              lastPkt = glrun->RUNHEADER_PKT;
            } else {
              lastObt = firstObt;
              lastPkt = firstPkt;
            };
            glrun->SetRUNTRAILER_PKT(lastPkt);
            glrun->SetRUNTRAILER_OBT(lastObt);
            lastentry++;
          };
          //      
          this->SetCommonGLRUN(firstTime,lastTime);
          //
          if ( !IsRunAlreadyInserted() ){
            glrun->SetID(this->AssignRunID());
            glrun->SetID_RUN_FRAG(0);
            glrun->Fill_GL_RUN(conn);      
          };
          //
          if ( i == lastev && checkfirst < check ){ // if the last event gives a wrong check...
            //
            firstentry = i;
            //
            lastentry = i;
            //
            if ( IsDebug() ) printf(" Run between %i and %i entries\n",firstentry,lastentry);
            //
            glrun->SetEV_FROM(firstentry);
            glrun->SetEV_TO(lastentry);
            glrun->SetNEVENTS(lastentry-firstentry+1);
            //
            glrun->Set_GL_RUNH0();
            //
            glrun->SetLAST_TIMESYNC(0);
            glrun->SetOBT_TIMESYNC(0);
            //
            T->GetEntry(firstentry);
            ph = eh->GetPscuHeader();           
            firstObt = ph->GetOrbitalTime();
            firstTime = this->GetAbsTime(firstObt);
            firstPkt = ph->GetCounter();                 
            //
            glrun->SetRUNHEADER_PKT(firstPkt);
            //
            glrun->SetRUNHEADER_OBT(firstObt);
            //
            glrun->Set_GL_RUNT(runt,pht);
            lastTime = this->GetAbsTime(pht->GetOrbitalTime());
            if ( IsDebug() ) printf(" We have the runtrailer \n");
            //    
            this->SetCommonGLRUN(firstTime,lastTime);
            //
            if ( !IsRunAlreadyInserted() ){
              glrun->SetID(this->AssignRunID());
              glrun->SetID_RUN_FRAG(0);
              glrun->Fill_GL_RUN(conn);      
            };
          };
          //
          firstevno = lastentry + 1;
          //
          checkfirst = check;
          //
        };
        //
        if ( check == checklast && i != lastev ) i = lastev - 1; // >>>>>>>>>>>>>>>>>>>>>>
        //
      };
    };
  };
  //
  return;
};


/**
 * Scan calorimeter calibrations packets, fill the GL_CALO_CALIB table
 */
Int_t PamelaDBOperations::insertCALO_CALIB(){
  //
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  //
  stringstream oss;
  oss.str("");
  //
  CalibCalPedEvent *calibCalPed = 0;
  TTree *tr = 0;
  EventHeader *eh = 0;
  PscuHeader *ph = 0;
  //
  UInt_t nevents = 0;
  UInt_t fromtime = 0;
  UInt_t totime = 0;
  UInt_t obt = 0;
  UInt_t pkt = 0;
  //
  tr = (TTree*)file->Get("CalibCalPed");
  if ( !tr || tr->IsZombie() ) throw -21;
  //
  tr->SetBranchAddress("CalibCalPed", &calibCalPed);
  tr->SetBranchAddress("Header", &eh);
  nevents = tr->GetEntries();
  //
  if ( !nevents ) return(1);
  //
  for (UInt_t i=0; i < nevents; i++){
    tr->GetEntry(i);
    for (UInt_t section = 0; section < 4; section++){
      //
      if ( calibCalPed->cstwerr[section] ){
        valid = 1;
        if ( calibCalPed->cperror[section] ) valid = 0;
        ph = eh->GetPscuHeader();
        obt = ph->GetOrbitalTime();  
        pkt = ph->GetCounter();  
        fromtime = this->GetAbsTime(ph->GetOrbitalTime());  
        if ( this->PKT(pkt) >= this->PKT(pktfirst) && this->OBT(obt) >= this->OBT(obtfirst) ){
          //
          if ( IsDebug() ) printf(" Calo calibration for section %i at time %i obt %i pkt %i \n",section,fromtime,obt,pkt);
          //
          // check if the calibration has already been inserted
          //
          oss.str("");
          oss << " SELECT ID FROM GL_CALO_CALIB WHERE "
              << " SECTION = "<< section << " AND "
              << " BOOT_NUMBER = "<< this->GetBOOTnumber() << " AND "
              << " OBT = "<< obt << " AND "
              << " PKT = "<< pkt << ";";
          //
          if ( IsDebug() ) printf(" Check if the calo calibration has already been inserted: query is \n %s \n",oss.str().c_str());
          result = conn->Query(oss.str().c_str());
          //
          if ( !result ) throw -4;
          //
          row = result->Next();
          //
          if ( row ){
            //
            if ( IsDebug() ) printf(" Calo calibration already inserted in the DB\n");
            //
          } else {
            //
            // we have to insert a new calibration, check where to place it
            //
            oss.str("");
            oss << " SELECT ID,TO_TIME FROM GL_CALO_CALIB WHERE "
                << " SECTION = "<< section << " AND "
                << " FROM_TIME < "<< fromtime << " AND "
                << " TO_TIME > "<< fromtime << ";";
            //
            if ( IsDebug() ) printf(" Check where to place the  calo calibration: query is \n %s \n",oss.str().c_str());
            result = conn->Query(oss.str().c_str());
            //
            if ( !result ) throw -4;
            //
            row = result->Next();
            //
            if ( !row ){
              //
              // no calibrations in the db contain our calibration
              //
              if ( IsDebug() ) printf(" Calibration with fromtime lower than others to be inserted in the DB for section %i \n",section);
              if ( fromtime < 1150863400 ){
                if ( IsDebug() ) printf(" First PAMELA flight calibration at time %i \n",fromtime);
                fromtime = 0;// the first flight calibration was taken at about  1156429100 s, this line allow to analyze first runs in raw mode
              };
              //
              oss.str("");
              oss << " SELECT FROM_TIME FROM GL_CALO_CALIB WHERE "
                  << " SECTION = "<< section << " AND "
                  << " FROM_TIME > "<< fromtime << " ORDER BY FROM_TIME ASC LIMIT 1;";
              //
              if ( IsDebug() ) printf(" Check the upper limit for calibration: query is \n %s \n",oss.str().c_str());
              result = conn->Query(oss.str().c_str());
              //
              if ( !result ) throw -4;
              //
              row = result->Next();
              if ( !row ){
                totime = numeric_limits<UInt_t>::max();
              } else {
                totime = (UInt_t)atoll(row->GetField(0));
              };
              //
            } else {
              //
              // determine upper and lower limits and make space for the new calibration
              //
              totime = (UInt_t)atoll(row->GetField(1));
              //
              oss.str("");
              oss << " UPDATE GL_CALO_CALIB SET "   
                  << " TO_TIME = "<< fromtime << " WHERE "  // NOTICE: to_time is equal to from_time of the calibration before, so the interval is: [from_time,to_time[
                  << " ID = "<< row->GetField(0) << ";";
              //
              if ( IsDebug() ) printf(" Make space for the new calibration: query is \n %s \n",oss.str().c_str());
              result = conn->Query(oss.str().c_str());
              //
              if ( !result ) throw -4;
              //
            };
            //
            oss.str("");
            oss << " INSERT INTO GL_CALO_CALIB (ID,ID_ROOT_L0,EV_ROOT,FROM_TIME,TO_TIME,SECTION,OBT,PKT,BOOT_NUMBER,VALIDATION) "
                << " VALUES (NULL,' "
                << idroot << "','"
                << i << "','"
                << fromtime << "','"
                << totime << "','"
                << section << "','"
                << obt << "','"
                << pkt << "','"
                << this->GetBOOTnumber() << "','"
                << valid << "');";
            //
            if ( IsDebug() ) printf(" Insert the new calibration: query is \n %s \n",oss.str().c_str());
            //
            result = conn->Query(oss.str().c_str());
            //
            if ( !result ) throw -4;
            //
          };
          //
        } else {
          //
          if ( IsDebug() ) printf(" Repetead calo calibration for section %i at time %i obt %i pkt %i \n",section,fromtime,obt,pkt);
          //
        };      
        //      
      };
    };
  };
  //
  return(0);
};
 
/**
 * Fill the GL_TRK_CALIB table
 */
void PamelaDBOperations::HandleTRK_CALIB(Bool_t pk1, Bool_t pk2){
  //
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  //
  stringstream oss;
  oss.str("");
  //
  UInt_t totime = 0;
  //
  if ( !pk1 && !pk2 ){
    if ( IsDebug() ) printf(" Cannot handle trk calibration with both packet missing!\n");
  };
  //
  // check if the calibration has already been inserted
  //
  oss.str("");
  oss << " SELECT ID FROM GL_TRK_CALIB WHERE "
      << " BOOT_NUMBER = "<< this->GetBOOTnumber(); // 
  oss << " AND ( ( ";   
  if ( pk1 ){
    oss << " OBT1 = "<< obt1 << " AND "
        << " PKT1 = "<< pkt1
        << " ) OR ( ";
  } else {
    oss << " PKT1 = "<< pkt2-1
        << " ) OR ( ";   
  };      
  if ( pk2 ){
    oss << " OBT2 = "<< obt2 << " AND "
        << " PKT2 = "<< pkt2;
  } else {
    oss << " PKT2 = "<< pkt1+1;
  };      
  oss << " ) );";   
  //
  if ( IsDebug() ) printf(" Check if the trk calibration has already been inserted: query is \n %s \n",oss.str().c_str());
  result = conn->Query(oss.str().c_str());
  //
  if ( !result ) throw -4;
  //
  row = result->Next();
  //
  if ( row ){
    //
    if ( IsDebug() ) printf(" Trk calibration already inserted in the DB\n");
    //
  } else {
    //
    // we have to insert a new calibration, check where to place it
    //
    oss.str("");
    oss << " SELECT ID,TO_TIME FROM GL_TRK_CALIB WHERE "
        << " FROM_TIME < "<< fromtime << " AND "
        << " TO_TIME > "<< fromtime << ";";
    //
    if ( IsDebug() ) printf(" Check where to place the trk calibration: query is \n %s \n",oss.str().c_str());
    result = conn->Query(oss.str().c_str());
    //
    if ( !result ) throw -4;
    //
    row = result->Next();
    //
    if ( !row ){
      //
      // no calibrations in the db contain our calibration
      //
      if ( IsDebug() ) printf(" Calibration with fromtime lower than others to be inserted in the DB\n");
      if ( fromtime < 1150863400 ) fromtime = 0; // the first flight calibration was taken at about 1150863300 s, this line allows to analyze first runs in raw mode
      //
      oss.str("");
      oss << " SELECT FROM_TIME FROM GL_TRK_CALIB WHERE "
          << " FROM_TIME > "<< fromtime << " ORDER BY FROM_TIME ASC LIMIT 1;";
      //
      if ( IsDebug() ) printf(" Check the upper limit for calibration: query is \n %s \n",oss.str().c_str());
      result = conn->Query(oss.str().c_str());
      //
      if ( !result ) throw -4;
      //
      row = result->Next();
      if ( !row ){
        totime = numeric_limits<UInt_t>::max();
      } else {
        totime = (UInt_t)atoll(row->GetField(0));
      };
      //
    } else {
      //
      // determine upper and lower limits and make space for the new calibration
      //
      totime = (UInt_t)atoll(row->GetField(1));
      //
      oss.str("");
      oss << " UPDATE GL_TRK_CALIB SET "   
          << " TO_TIME = "<< fromtime << " WHERE "  // NOTICE: to_time is equal to from_time of the calibration before, so the interval is: [from_time,to_time[
          << " ID = "<< row->GetField(0) << ";";
      //
      if ( IsDebug() ) printf(" Make space for the new trk calibration: query is \n %s \n",oss.str().c_str());
      result = conn->Query(oss.str().c_str());
      //
      if ( !result ) throw -4;
      //
    };
    //
    oss.str("");
    oss << " INSERT INTO GL_TRK_CALIB (ID,ID_ROOT_L0,EV_ROOT_CALIBTRK1,EV_ROOT_CALIBTRK2,FROM_TIME,TO_TIME,OBT1,PKT1,OBT2,PKT2,BOOT_NUMBER,VALIDATION) "
        << " VALUES (NULL,' "
        << idroot << "',";
    //
    if ( !pk1 ){
      oss << "NULL,";
    } else {
      oss << "'"
          << t1 << "',";
    };
    //
    if ( !pk2 ){
      oss << "NULL,'";
    } else {
      oss << "'"
          << t2 << "','";
    };
    //
    oss << fromtime << "','"
        << totime << "','"
        << obt1 << "','"
        << pkt1 << "','"
        << obt2 << "','"
        << pkt2 << "','"
        << this->GetBOOTnumber() << "','"
        << valid << "');";
    //
    if ( IsDebug() ) printf(" Insert the new trk calibration: query is \n %s \n",oss.str().c_str());
    //
    result = conn->Query(oss.str().c_str());
    //
    if ( !result ) throw -4;
    //
  };
  //
};

/**
 * Scan tracker calibrations packets, fill the GL_TRK_CALIB table
 */
Int_t PamelaDBOperations::insertTRK_CALIB(){
  //
  CalibTrk1Event *caltrk1 = 0;
  CalibTrk2Event *caltrk2 = 0;
  TTree *tr1 = 0;
  TTree *tr2 = 0;
  EventHeader *eh1 = 0;
  PscuHeader *ph1 = 0;
  EventHeader *eh2 = 0;
  PscuHeader *ph2 = 0;
  //
  PacketType *pctp=0;
  EventCounter *codt2=0;
  //
  Int_t nevents1 = 0;
  Int_t nevents2 = 0;
  //
  fromtime = 0;
  //
  obt1 = 0;
  pkt1 = 0;
  obt2 = 0;
  pkt2 = 0;
  //
  tr1 = (TTree*)file->Get("CalibTrk1");
  if ( !tr1 || tr1->IsZombie() ) throw -22;
  tr2 = (TTree*)file->Get("CalibTrk2");
  if ( !tr2 || tr2->IsZombie() ) throw -23;
  //
  tr1->SetBranchAddress("CalibTrk1", &caltrk1);
  tr1->SetBranchAddress("Header", &eh1);
  nevents1 = tr1->GetEntries();
  tr2->SetBranchAddress("CalibTrk2", &caltrk2);
  tr2->SetBranchAddress("Header", &eh2);
  nevents2 = tr2->GetEntries();
  //
  if ( !nevents1 && !nevents2 ) return(1);
  //
  t2 = -1;
  Int_t pret2 = 0;
  Int_t t2t1cal = 0;
  //
  for (t1=0; t1 < nevents1; t1++){
    //
    pret2 = t2;
    tr1->GetEntry(t1);
    //
    ph1 = eh1->GetPscuHeader();
    obt1 = ph1->GetOrbitalTime();  
    pkt1 = ph1->GetCounter();  
    fromtime = this->GetAbsTime(ph1->GetOrbitalTime());  
    //
    valid = 1;
    //
    if ( caltrk1->unpackError != 0 && caltrk1->good0 == 0 ) valid = 0;// CONDITIONS ON THE GOODNESS OF THE CALIBRATION PKT1
    //
    //
    if ( this->PKT(pkt1) >= this->PKT(pktfirst) && this->OBT(obt1) >= this->OBT(obtfirst) ){
      //
      if ( IsDebug() ) printf(" Trk calibration1 at time %i obt %i pkt %i \n",fromtime,obt1,pkt1);
      //
      // Do we have the second calibration packet?
      //
      while ( t2t1cal < t1+1 ){ // get the calibration packet2 that follows the packet1
        //
        t2++;
        //
        if ( t2 < nevents2 ){
          tr2->GetEntry(t2);
          codt2 = eh2->GetCounter();
          t2t1cal = codt2->Get(pctp->CalibTrk1);
          //
          ph2 = eh2->GetPscuHeader();
          obt2 = ph2->GetOrbitalTime();  
          pkt2 = ph2->GetCounter();  
          //
          if ( caltrk2->unpackError != 0 || caltrk2->good0 == 0 ) valid = 0; // CONDITIONS ON THE GOODNESS OF THE CALIBRATION PKT2
          //      
        } else {
          //
          // running out of vector without finding the corresponding calibration, sig
          //
          pret2 = t2;
          obt2 = 0;
          pkt2 = pkt1+2;
          t2t1cal = t1+1;
        };
        if ( this->PKT(pkt2) < this->PKT(pktfirst) && this->OBT(obt2) < this->OBT(obtfirst) ){
          //
          // running out of vector without finding the corresponding calibration, sig
          //
          pret2 = t2;
          obt2 = 0;
          pkt2 = pkt1+2;
          t2t1cal = t1+1;
        };
        //
      };
      //
      if ( IsDebug() ) printf(" Found trk calibration2 at obt %i pkt %i t2 is %i \n",obt2,pkt2,t2);
      //
      // The calibration is good
      //
      if ( this->PKT(pkt2) == this->PKT(pkt1)+1 ){
        //
        if ( IsDebug() ) printf(" The trk calibration2 at obt %i pkt %i t2 is %i is good \n",obt2,pkt2,t2);
        //
        // Handle good calib
        //
        this->HandleTRK_CALIB(true,true);
        //
        // Check for missing calibtrk1
        //
        if ( t2 != pret2+1 ){
          //
          if ( IsDebug() ) printf(" Missing the trk calibration1! Next one at obt %i pkt %i t2 is %i pret2 is %i \n",obt2,pkt2,t2,pret2);
          //
          while ( t2 > pret2+1 ){
            //
            // handle missing calib1
            //
            pret2++;
            //
            obt1 = 0;
            pkt1 = 0;
            //
            tr2->GetEntry(pret2);
            ph2 = eh2->GetPscuHeader();
            obt2 = ph2->GetOrbitalTime();  
            pkt2 = ph2->GetCounter();  
            //
            fromtime = this->GetAbsTime(ph2->GetOrbitalTime()); 
            //
            valid = 0;
            this->HandleTRK_CALIB(false,true);
            //
          };
          //
        };
        //
      } else if ( this->PKT(pkt2) > this->PKT(pkt1)+1 ){
        //
        // Check for missing calibtrk2
        //
        if ( IsDebug() ) printf(" Missing the trk calibration2! Next one at obt %i pkt %i t2 is %i\n",obt2,pkt2,t2);
        t2 = pret2;
        //
        // handle missing calib2
        //
        obt2 = 0;
        pkt2 = 0;
        valid = 0;
        this->HandleTRK_CALIB(true,false);       
        //
      };
      //
    } else {
      //
      if ( IsDebug() ) printf(" Repetead trk calibration1 at time %i obt %i pkt %i \n",fromtime,obt1,pkt1);
      //
    };
    //    
  };
  //
  // we have one more calib pkt2 !
  //
  t2++;
  while ( t2 < nevents2 ){
    //
    // handle missing calib1
    //
    obt1 = 0;
    pkt1 = 0;
    //
    tr2->GetEntry(t2);
    ph2 = eh2->GetPscuHeader();
    obt2 = ph2->GetOrbitalTime();  
    pkt2 = ph2->GetCounter();  
    //
    fromtime = this->GetAbsTime(ph2->GetOrbitalTime()); 
    valid = 0;
    if ( this->PKT(pkt2) > this->PKT(pktfirst) || this->OBT(obt2) > this->OBT(obtfirst) ){
      //
      if ( IsDebug() ) printf(" Missing the trk calibration1! Next one at obt %i pkt %i t2 is %i\n",obt2,pkt2,t2);
      //
      this->HandleTRK_CALIB(false,true);
      //
    };
    //
    t2++;
    //
  };
  //
  return(0);
};


/**
 * Scan S4 calibrations packets, fill the GL_S4_CALIB table
 */
Int_t PamelaDBOperations::insertS4_CALIB(){
  //
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  //
  stringstream oss;
  oss.str("");
  //
  TTree *tr = 0;
  EventHeader *eh = 0;
  PscuHeader *ph = 0;
  //
  UInt_t nevents = 0;
  UInt_t fromtime = 0;
  UInt_t totime = 0;
  UInt_t obt = 0;
  UInt_t pkt = 0;
  //
  tr = (TTree*)file->Get("CalibS4");
  if ( !tr || tr->IsZombie() ) throw -24;
  //
  tr->SetBranchAddress("Header", &eh);
  //
  nevents = tr->GetEntries();
  //
  if ( !nevents ) return(1);
  //
  for (UInt_t i = 0; i < nevents; i++){
    //
    tr->GetEntry(i);
    //
    ph = eh->GetPscuHeader();
    obt = ph->GetOrbitalTime();  
    pkt = ph->GetCounter();  
    fromtime = this->GetAbsTime(ph->GetOrbitalTime());  
    if ( this->PKT(pkt) >= this->PKT(pktfirst) && this->OBT(obt) >= this->OBT(obtfirst) ){
      //
      if ( IsDebug() ) printf(" S4 calibration at time %i obt %i pkt %i \n",fromtime,obt,pkt);
      //
      // check if the calibration has already been inserted
      //
      oss.str("");
      oss << " SELECT ID FROM GL_S4_CALIB WHERE "
          << " BOOT_NUMBER = "<< this->GetBOOTnumber() << " AND "
          << " OBT = "<< obt << " AND "
          << " PKT = "<< pkt << ";";
      //
      if ( IsDebug() ) printf(" Check if the S4 calibration has already been inserted: query is \n %s \n",oss.str().c_str());
      result = conn->Query(oss.str().c_str());
      //
      if ( !result ) throw -4;
      //
      row = result->Next();
      //
      if ( row ){
        //
        if ( IsDebug() ) printf(" S4 calibration already inserted in the DB\n");
        //
      } else {
        //
        // we have to insert a new calibration, check where to place it
        //
        oss.str("");
        oss << " SELECT ID,TO_TIME FROM GL_S4_CALIB WHERE "
            << " FROM_TIME < "<< fromtime << " AND "
            << " TO_TIME > "<< fromtime << ";";
        //
        if ( IsDebug() ) printf(" Check where to place the S4 calibration: query is \n %s \n",oss.str().c_str());
        result = conn->Query(oss.str().c_str());
        //
        if ( !result ) throw -4;
        //
        row = result->Next();
        //
        if ( !row ){
          //
          // no calibrations in the db contain our calibration
          //
          if ( IsDebug() ) printf(" Calibration with fromtime lower than others to be inserted in the DB \n");
          if ( fromtime < 1150863400 ){
            if ( IsDebug() ) printf(" First PAMELA flight calibration at time %i \n",fromtime);
            fromtime = 0;// the first flight calibration was taken at about  1156429100 s, this line allow to analyze first runs in raw mode
          };
          //
          oss.str("");
          oss << " SELECT FROM_TIME FROM GL_S4_CALIB WHERE "
              << " FROM_TIME > "<< fromtime << " ORDER BY FROM_TIME ASC LIMIT 1;";
          //
          if ( IsDebug() ) printf(" Check the upper limit for calibration: query is \n %s \n",oss.str().c_str());
          result = conn->Query(oss.str().c_str());
          //
          if ( !result ) throw -4;
          //
          row = result->Next();
          if ( !row ){
            totime = numeric_limits<UInt_t>::max();
          } else {
            totime = (UInt_t)atoll(row->GetField(0));
          };
          //
        } else {
          //
          // determine upper and lower limits and make space for the new calibration
          //
          totime = (UInt_t)atoll(row->GetField(1));
          //
          oss.str("");
          oss << " UPDATE GL_S4_CALIB SET "   
              << " TO_TIME = "<< fromtime << " WHERE "  // NOTICE: to_time is equal to from_time of the calibration before, so the interval is: [from_time,to_time[
              << " ID = "<< row->GetField(0) << ";";
          //
          if ( IsDebug() ) printf(" Make space for the new calibration: query is \n %s \n",oss.str().c_str());
          result = conn->Query(oss.str().c_str());
          //
          if ( !result ) throw -4;
          //
        };
        //
        oss.str("");
        oss << " INSERT INTO GL_S4_CALIB (ID,ID_ROOT_L0,EV_ROOT,FROM_TIME,TO_TIME,OBT,PKT,BOOT_NUMBER) "
            << " VALUES (NULL,' "
            << idroot << "','"
            << i << "','"
            << fromtime << "','"
            << totime << "','"
            << obt << "','"
            << pkt << "','"
            << this->GetBOOTnumber() << "');";
        //
        if ( IsDebug() ) printf(" Insert the new calibration: query is \n %s \n",oss.str().c_str());
        //
        result = conn->Query(oss.str().c_str());
        //
        if ( !result ) throw -4;
        //
      };
      //
    } else {
      //
      if ( IsDebug() ) printf(" Repetead S4 calibration at time %i obt %i pkt %i \n",fromtime,obt,pkt);
      //
    };      
    //      
  };
  //
  return(0);
};
 
/**
 * Scan the fragment table and move old fragments to the GL_RUN table
 */
Int_t PamelaDBOperations::CleanGL_RUN_FRAGMENTS(){
  //
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  TSQLResult *result2 = 0;
  TSQLRow    *row2   = 0;
  //
  UInt_t moved = 0;
//  UInt_t timelim = 0;
//  TDatime *time = new TDatime();
  //
  stringstream oss;
  oss.str("");
  //
  //
  // check if there are entries older than "olderthan" seconds from now
  //
  oss.str("");
  oss << " SELECT ID FROM GL_RUN_FRAGMENTS WHERE"
      << " INSERT_TIME <= '" << clean_time->AsSQLString() << "';";
  //
  if ( IsDebug() ) printf(" Select from GL_RUN_FRAGMENTS runs older than %s : query is \n %s \n",clean_time->AsSQLString(),oss.str().c_str());
  result = conn->Query(oss.str().c_str());
  //
  if ( result ){
    //
    row = result->Next();
    //
    while ( row ){
      //
      oss.str("");
      oss << " ID= "<< row->GetField(0);
      //
      glrun->Query_GL_RUN_FRAGMENTS(oss.str().c_str(),conn);   
      //
      oss.str("");
      oss << " SELECT ID,NEVENTS,TRK_CALIB_USED,PKT_COUNTER FROM GL_RUN WHERE "
          << " BOOT_NUMBER=" << glrun->GetBOOT_NUMBER() << " AND ("
          << " (RUNHEADER_TIME>=" << (UInt_t)(glrun->GetRUNHEADER_TIME()-10) << " AND "
          << " RUNTRAILER_TIME<=" << (UInt_t)(glrun->GetRUNTRAILER_TIME()+10) << " AND ("
          << " RUNHEADER_OBT>=" << glrun->GetRUNHEADER_OBT() << " OR "
          << " RUNHEADER_PKT>=" << glrun->GetRUNHEADER_PKT() << ") AND ("
          << " RUNTRAILER_OBT<=" << glrun->GetRUNTRAILER_OBT() << " OR "
          << " RUNTRAILER_PKT<=" << glrun->GetRUNTRAILER_PKT() << ") ) OR "
          << " (RUNHEADER_TIME<=" << (UInt_t)glrun->GetRUNHEADER_TIME() << " AND "
          << " RUNTRAILER_TIME>=" << (UInt_t)glrun->GetRUNTRAILER_TIME() <<" AND ("
          << " RUNHEADER_OBT<=" << glrun->GetRUNHEADER_OBT() << " OR "
          << " RUNHEADER_PKT<=" << glrun->GetRUNHEADER_PKT() << ") AND ("
          << " RUNTRAILER_OBT>=" << glrun->GetRUNTRAILER_OBT() << " OR "
          << " RUNTRAILER_PKT>=" << glrun->GetRUNTRAILER_PKT() << ") ));";
      //
      if ( IsDebug() ) printf(" check if run has been inserted: query is \n %s \n",oss.str().c_str());
      result2 = conn->Query(oss.str().c_str());
      //
      if ( !result2 ) throw -4;
      //
      row2 = result2->Next();
      //
      if ( !row2 ){
        //
        if ( IsDebug() ) printf(" The run is new \n");
        if ( IsDebug() ) printf(" -> fill the DB \n");      
        //
        //      glrun->SetID(this->AssignRunID()); we use the old run number!
        glrun->SetID_RUN_FRAG(glrun->GetID());
        glrun->Fill_GL_RUN(conn);   
        //
//      oss.str("");
//      oss << " SELECT ID FROM GL_RUN WHERE "
//          << " BOOT_NUMBER=" << glrun->GetBOOT_NUMBER() << " AND "
//          << " RUNHEADER_PKT=" << (UInt_t)glrun->GetRUNHEADER_PKT() << " AND "
//          << " RUNTRAILER_PKT=" << (UInt_t)glrun->GetRUNTRAILER_PKT() << " AND "
//          << " RUNHEADER_OBT=" << (UInt_t)glrun->GetRUNHEADER_OBT() << " AND "
//          << " RUNTRAILER_OBT=" << (UInt_t)glrun->GetRUNTRAILER_OBT() << "; ";
//      //
//      if ( IsDebug() ) printf(" Look for the ID of the inserted run: query is \n %s \n",oss.str().c_str());
//      result2 = conn->Query(oss.str().c_str());
//      //
//      if ( !result2 ) throw -4;
//      //
//      row2 = result2->Next();
//      //
//      if ( !row2 ) throw -25;
//      //
//      oss.str("");
//      oss << " UPDATE GL_RUN SET ID_RUN_FRAG = " << row2->GetField(0) << " WHERE ID = " << row2->GetField(0);
//      if ( IsDebug() ) printf(" Update the ID_RUN_FRAG of the inserted run: query is \n %s \n",oss.str().c_str());
//      result2 = conn->Query(oss.str().c_str());
//      //
//      if ( !result2 ) throw -4;
        //
        moved++;
        //
      } else {
        if ( IsDebug() ) printf(" The already exist in the GL_RUN table! \n");
      };
      if ( IsDebug() ) printf(" Delete run %s from the GL_RUN_FRAGMENTS table \n",row->GetField(0));      
      //
      //
      glrun->DeleteRun(conn,(UInt_t)atoll(row->GetField(0)),"GL_RUN_FRAGMENTS");
//      oss.str("");
//       oss << " DELETE from GL_RUN_FRAGMENTS where ID = " << row->GetField(0);
//       if ( IsDebug() ) printf(" Clean the GL_RUN_FRAGMENTS table: query is \n %s \n",oss.str().c_str());
//       result2 = conn->Query(oss.str().c_str());
//      //
//      if ( !result2 ) throw -4;
//      //
      row = result->Next();
    };
  };
  if ( IsDebug() ) printf(" Moved %u runs\n",moved);
  return(0);
};

/**
 * Check if runs are good, i.e. if the tracker calibration is correctly associated..
 */
Int_t PamelaDBOperations::ValidateRuns(){
  //
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  //
  stringstream oss;
  oss.str("");
  //
  // =======================================================
  // validate runs by checking missing calibrations 
  // =======================================================
  UInt_t t_stop  = 0;
  UInt_t t_start = 0;
  // --------------------------------------------------------------
  // 1) get the OBT of the last run inserted after clean-time limit
  // --------------------------------------------------------------
  oss.str("");
  oss << " SELECT * FROM GL_RUN  WHERE INSERT_TIME <= '" << clean_time->AsSQLString() 
          << "' ORDER BY RUNHEADER_TIME DESC LIMIT 1;";
  if ( IsDebug() ) printf(" Get start validation-time: query is \n %s \n",oss.str().c_str());
  result = conn->Query(oss.str().c_str());
  if ( !result ) throw -4;
  if ( !result->GetRowCount() ) {
          printf(" No runs to validate \n");
          return(1);
  }else{
        row = result->Next();
        t_start = (UInt_t)atoll(row->GetField(4));
  };  
  // --------------------------------------------------------------
  // 2) get the OBT of the last validated run
  // --------------------------------------------------------------
  oss.str("");
  oss << " SELECT * FROM GL_RUN  WHERE VALIDATION=1 AND RUNHEADER_TIME<="<< t_start
      <<" ORDER BY RUNHEADER_TIME DESC LIMIT 1;";
  if ( IsDebug() ) printf(" Get stop validation-time: query is \n %s \n",oss.str().c_str());
  result = conn->Query(oss.str().c_str());
  if ( !result ) throw -4;
  if ( result->GetRowCount() ){
          row = result->Next();
          t_stop = (UInt_t)atoll(row->GetField(4));
  };
  if ( IsDebug() ) printf("Validation interval: from time %i - to time %i \n\n",t_stop,t_start);
  // --------------------------------------------------------------
  // now retrieves runs to be validated 
  // --------------------------------------------------------------
  oss.str("");
  oss << " SELECT * FROM GL_RUN  WHERE  RUNHEADER_TIME <=" << t_start;
  oss << " AND RUNHEADER_TIME >="<< t_stop; 
  oss << " ORDER BY RUNHEADER_TIME DESC;";
//  if ( IsDebug() )
  if ( IsDebug() )printf(" Check runs for validation: query is \n %s \n",oss.str().c_str());
  result = conn->Query(oss.str().c_str());
  if ( !result ) throw -4;
  if ( !result->GetRowCount() ) printf(" No runs to validate \n");
//  printf("------------------------------------------------------------------------------- \n");
  
  Int_t nrow = 0;
  GL_RUN* this_run = new GL_RUN();
  GL_RUN* next_run = new GL_RUN();
  Int_t   nseq_max = 1000;
//  UInt_t* sequence = new UInt_t[100];
  vector<UInt_t> sequence(nseq_max);
  Int_t   nseq = 0;
  Bool_t CHECK = false;
  Bool_t this_ONLINE = false;
  Bool_t next_ONLINE = false;
  UInt_t t1=0,t2=0;
  // ---------------------------------------------------------------------------------
  // - loop over runs, back in time, 
  // - select sequences of runs close in time (less than 60 s apart), 
  //   which could be preceeded by a calibration
  // - check if there might be a missing calibration 
  // ---------------------------------------------------------------------------------
  while(1){
          
          row = result->Next();
          if( row == NULL ) break;
          
          //------------
          //get run info
          //------------
          this_run->Set_GL_RUN(row);
                  
          Bool_t this_BAD = false;
          if(this_run->GetTRK_CALIB_USED() == 1 || this_run->GetTRK_CALIB_USED() == 2) this_ONLINE = true;
          else if (this_run->GetTRK_CALIB_USED() == 104)                          this_ONLINE = false;
          else{
//                printf("Missing or corrupted header!! \n");
                  this_ONLINE = false;
                  this_BAD = true;
          };

          //-----------------------------------
          //compare with previous(next in time)
          //-----------------------------------
          CHECK = false;
          UInt_t interval=0;
          
          if( nrow != 0){
        
                  
                  t1 = this_run->GetRUNTRAILER_TIME();
                  t2 = next_run->GetRUNHEADER_TIME();
                  interval = (t2-t1);
                  
                  if(this_ONLINE && next_ONLINE){                               // this: ON-LINE + next: ON-LINE
                          
                          if( this_run->ID == next_run->ID_RUN_FRAG ) interval = 0;     //=> run fragments
                          
                          if( interval >= 60 )CHECK = true;                     //more than 60 s => there might be a calibration
                          
                          if( !CHECK && this_run->VALIDATION ){
                                  for (Int_t irun = 0; irun < nseq; irun++)assignVALIDATION(sequence[irun],true);
                                  nseq=0;
                          }
                  
                  }else if( !this_ONLINE && next_ONLINE) {              // this: DEFAULT + next:ON-LINE
                          
                          CHECK = true;

                  }else if( !next_ONLINE ){                                             // this:ANY + next:DEFAULT 
                          
                          assignVALIDATION(next_run->ID,true);
                          nseq=0;
                  }
          }

          //----------------------------
          //check run sequence for calib
          //----------------------------
          if( CHECK ){
                  // check if calibration exists
                  if ( IsDebug() )printf("DT %i ===> CHECK Missing calibration\n",interval); 
                  Bool_t MISSING = MissingTRK_CALIB(t1,t2);
                  for (Int_t irun = 0; irun < nseq; irun++)assignVALIDATION(sequence[irun],!MISSING);
                  nseq=0;
          };
          //--------------
          //store run info
          //--------------
          *next_run   = *this_run;
          next_ONLINE = this_ONLINE;
          if( !this_BAD ){ 
                  if(nseq < nseq_max){
                          sequence[nseq] = this_run->ID; 
                          nseq++;
                  }else printf("ValidateRuns ***WARNING*** : run sequence exceed assumed size (%i) \n",nseq_max);
          };
          
          if ( IsDebug() ) printf("%i Run %i \n",nrow,this_run->ID);
          nrow++;
          
  };
  delete this_run;
  delete next_run;
  //
  return(0);
};
/**
 * Check if there might be a missing tracker calibration in a given time interval
 * @param t1 From absolute time
 * @param t2 To absolute time
 * @return true if there might be a missing calibration
 */
Bool_t PamelaDBOperations::MissingTRK_CALIB(UInt_t t1,UInt_t t2){
        
        GL_TRK_CALIB* trkcalib = new GL_TRK_CALIB();
        
        // get the closest VALIDATED calibration before the run start (t2)
        if ( trkcalib->Query_GL_TRK_CALIB(t2, conn) )return(true);      //>>> missing
        
        if ( trkcalib->TO_TIME  < t2 ) return(true);                                    //>>> missing
        
        //==============================================================
        // Check is done first on the basis of time between calibration,
        // which should be equal to the time between ascending-nodes.
        //==============================================================
        if ( t2 - trkcalib->FROM_TIME > 5700) {
                if ( IsDebug() )printf("Long time between calib and run start %i :-( ==> there might be a missing calib \n",t2 - trkcalib->FROM_TIME);
        //==============================================================
        // there might be a missing calibration, due to:
        // - MM full
        // - corrupted packets
        // - loss of data
        // There is an exception in case a download was done during ascending node
        //==============================================================
                Bool_t DOWNLOAD = false;
                // check if the calib was skipped becouse of download .... DA FARE!! 
                if(DOWNLOAD)return(false);
                
                return(true);                                   //>>> missing
                
        };
        
        //==============================================================
        // If the last calibration is close to the run less than this time, 
        // it is enough to say that there are no missing calibrations
        //==============================================================
        // the long time interval bewteen runs might be due to download
        if ( IsDebug() )printf("Short time between calib and run start %i :-) ==> OK! \n",t2 - trkcalib->FROM_TIME);
        return(false);
        
};
/**
 * Assign VALIDATION value to a GL_RUN entry
 * @param idrun Run ID
 * @param validation true/false
 */
Int_t PamelaDBOperations::assignVALIDATION(UInt_t idrun, Bool_t validation){
        TSQLResult *result = 0;
        stringstream oss;
        oss.str("");
        oss << " UPDATE GL_RUN SET VALIDATION="<< (UInt_t)validation <<" WHERE ID= " << idrun << ";";
        //
//      if ( IsDebug() ) 
//      printf(" Set VALIDATION = %i for run %i \n",validation,idrun);
        if ( IsDebug() )printf(" Query: %s \n",oss.str().c_str());
        result = conn->Query(oss.str().c_str());
        if ( !result ) throw -4;
        return(0);
}


// Insert TLEs from file tlefilename in the table GL_TLE in the db
// opened by conn, sorting them by date from older to newer, if each
// TLE has not been alread inserted.
Int_t PamelaDBOperations::populateTLE()//(TSQLServer *conn, char *tleFile)
{
  fstream tlefile(tlefilename, ios::in);

  if ( !tlefile ) throw -7;

  vector<cTle*> ctles;
  vector<cTle*>::iterator iter;
  int present = 0;

  // Get three lines from tlefile, create a cTle object and put it
  // into ctles
  while(1) {
    cTle *tlef;
    string str1, str2, str3;

    getline(tlefile, str1);
    if(tlefile.eof()) break; 

    getline(tlefile, str2);
    if(tlefile.eof()) break;

    getline(tlefile, str3);
    if(tlefile.eof()) break;

    // We now have three good lines for a cTle.
    tlef = new cTle(str1, str2, str3);
    ctles.push_back(tlef);
  }

  tlefile.close();

  // Sort by date
  sort(ctles.begin(), ctles.end(), compTLE);

  // Now we insert each TLE into the db
  for(iter = ctles.begin(); iter != ctles.end(); iter++) {
    cTle *tle = *iter;

    // Do nothing if it's already present in the db.  Just increase
    // the counter present.
    if (! isTlePresent(tle))
      {
        int status = insertTle(tle);

        // Insert query failed.  Return 1.
        if(status == EXIT_FAILURE) {
          
          if( IsDebug() ) {
            cerr << "Error: inserting TLE:" << endl
                 << tle->getName() << endl
                 << tle->getLine1() << endl
                 << tle->getLine2() << endl;
          }

          throw -4;
          return 1;
        }

      }
    else
      present++;

  }

  int inserted = ctles.size() - present;  // Number of inserted TLE.
  if ( IsDebug() )
    cout << "\nProcessed TLEs ranging from " << getTleDatetime(ctles[0]) << " to " << getTleDatetime(ctles[ctles.size()-1]) << "." << endl
         << inserted << " newly inserted TLEs out of " << ctles.size() << " processed." << endl;

  ctles.clear();


  // Return 2 if no new TLE has been inserted.  0 otherwise.
  if(! inserted ) return 2;
  return 0;
}


// Insert tle in the table GL_TLE using the connection conn.
int PamelaDBOperations::insertTle(cTle *tle)
{
  stringstream oss;
  TSQLResult *result = 0;

  oss.str("");
  oss << " INSERT INTO GL_TLE (TLE1, TLE2, TLE3, FROM_TIME)"
      << " VALUES ( '" 
      << tle->getName() << "', '" 
      << tle->getLine1() << "', '"
      << tle->getLine2() << "', '"
      << getTleDatetime(tle) << "')";

  //  cout << oss.str().c_str() << endl;
  result = conn->Query(oss.str().c_str());
  if (result == NULL)
    return EXIT_FAILURE;

  return EXIT_SUCCESS;
}


// Return whether tle is already in the db connected by conn.
bool PamelaDBOperations::isTlePresent(cTle *tle)
{
  stringstream oss;
  TSQLResult *result = 0;

  oss.str("");
  oss << "SELECT * FROM GL_TLE WHERE FROM_TIME = '"
      << getTleDatetime(tle) << "'";

  result = conn->Query(oss.str().c_str());
  if (result == NULL) throw -4;

  if (result->GetRowCount())
    return true;
  else 
    return false;
}


// Return whether the first TLE is dated early than the second
bool compTLE (cTle *tle1, cTle *tle2)
{
  return getTleJulian(tle1) < getTleJulian(tle2);
}


// Return the date of the tle using the format (year-2000)*1e3 +
// julian day.  e.g. 6365 is the 31th Dec 2006.
// It does *not* return a cJulian date.
float getTleJulian(cTle *tle) {
  return tle->getField(cTle::FLD_EPOCHYEAR)*1e3 + tle->getField(cTle::FLD_EPOCHDAY);
}


// Return a string like YYYY-MM-DD hh:mm:ss, usable for mysql datetime
// format.
string getTleDatetime(cTle *tle)
{
  int year, mon, day, hh, mm, ss;
  double dom; // day of month (is double!)
  stringstream date; // date in datetime format

  // create a cJulian from the date in tle
  cJulian jdate = cJulian( 2000 + (int) tle->getField(cTle::FLD_EPOCHYEAR), tle->getField(cTle::FLD_EPOCHDAY)); 

  // get year, month, day of month
  jdate.getComponent(&year, &mon, &dom);

  // build a datetime YYYY-MM-DD hh:mm:ss
  date.str("");
  day = (int) floor(dom);
  hh = (int) floor( (dom - day) * 24);
  mm = (int) floor( ((dom - day) * 24 - hh) * 60);
  ss = (int) floor( ((((dom - day) * 24 - hh) * 60 - mm) * 60));
  //  ms = (int) floor( (((((dom - day) * 24 - hh) * 60 - mm) * 60) - ss) * 1000);

  date << year << "-" << mon << "-" << day << " " << hh << ":" << mm << ":" << ss;

  return date.str();
}