yodaUtility/YodaProfiler/PamelaDBOperations.cpp

#include <PamelaDBOperations.h>
#include <YFile.h>
#include <log4cxx/logger.h>
#include <iostream>
#include <string>
#include <fstream>
#include <list>
#include <errno.h>
#include <TSQLResult.h>
#include <TRFIOFile.h>
#include <TSQLRow.h>
#include <TTree.h>
#include <TGraph.h>
#include <TDatime.h>
#include <TF1.h>

#include <cOrbit.h>
#include <cTle.h>
#include <cEci.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>

extern "C" {
#include <dirent.h>
        //INITIZE(YEAR,DIMO)
        extern void initize_();
        //FELDCOF(YEAR,DIMO)
        extern void feldcof_(float*, float*);
        //FELDG(LATI,LONGI,HEIGHT,BNORTH,BEAST,BDOWN,BABS)
        extern void feldg_(float*, float*, float*, float*, float*, float*, float*);
        //SHELLG(LATI,LONGI,HEIGHT,DIMO,XL,ICODE,BAB1)
        extern void shellg_(float*, float*, float*, float*, float*, float*, float*);
        //FINDB0(STPS,BDEL,VALUE,BEQU,RR0)
        extern void findb0_(float*, float*, bool*, float*, float*);
}

using namespace log4cxx;
using namespace std;
using namespace pamela;
using namespace yngn::YException;
using namespace yngn::util;


//extern TArrayD S4_paramfit(CalibS4Event *S4CalibEvent);
/*
 * Received from Valeria Malvezzi 06/02/2006
 */
Double_t fitf(Double_t *x, Double_t *par){
  
  Double_t fitval =(par[0]*x[0])+par[1];
  return fitval;
}

/*
 * Received from Valeria Malvezzi 06/02/2006
 */
TArrayD S4_paramfit(pamela::CalibS4Event *S4CalibEvent){      
  
//----------- variable initialization -------------------------------------------------
  
  Double_t mip[3]={1, 30, 300};
  Double_t adc[3] = {0};
  TArrayD parametri(2);
 
//------------ Fit calibrations and find parameters to calibrate data ------------------
  pamela::S4::S4Event  *s4Record; 

  for (Int_t j = 0; j < 4; j++){
    for (Int_t i = 0; i < 128; i++){
      s4Record = (pamela::S4::S4Event*)S4CalibEvent->Records->At((j*128 + i));
      switch (j) {
      case 0 :{
        //calibS4_1->Fill(s4Record->S4_DATA);
        adc[0]=adc[0]+((s4Record->S4_DATA)-32);
        break;
      }
      case 1 :{
        //calibS4_2->Fill(s4Record->S4_DATA);
        adc[1]=adc[1]+((s4Record->S4_DATA)-32);
        break;
      }
      case 3 :{
        //calibS4_4->Fill(s4Record->S4_DATA);
        adc[2]=adc[2]+((s4Record->S4_DATA)-32);
        break;
      }
      }
    }
  }
  
  adc[0]=adc[0]/128;
  adc[1]=adc[1]/128;
  adc[2]=adc[2]/128;
  
  TGraph *fitpar = new TGraph (3, adc, mip);
  TF1 *func = new TF1("fitf", fitf, -0., 1000., 2); // definizione della funzione, 2 = num. parametri
  
  func->SetParameters(1,1);        //inizializzazione dei parametri a 1
  func->SetParNames("m","q");      //definisce il nome dei parametri
  fitpar->Fit(func,"r");          //fitta fitpar con la funzione func nel range definito nella funzione
    
  parametri[0] = func -> GetParameter(0); 
  parametri[1] = func -> GetParameter(1); 
 
  return parametri;
}


#define OK  1
#define NOK 0

static const char* RAW_FILE_NOT_FOUND = "Raw file not found in the database";

const char *PamelaDBOperations::TM_CALO_CALIB = "CREATE TEMPORARY TABLE `TM_CALO_CALIB` ( " 
                                    " `ID` bigint(64) unsigned NOT NULL auto_increment, "
                                    " `ID_REGISTRY` bigint(64) unsigned NOT NULL default '0', "
                                    " `REG_EVENT` bigint(64) unsigned NOT NULL default '0', "
                                    " `TIME` bigint(64) unsigned NOT NULL default '0', "
                                    " `CAL_SECTION` tinyint(3) unsigned NOT NULL default '0', "
                                    " `CPERROR` int(32) unsigned NOT NULL default '0', "
                                    " `CSTWERR` smallint(8) unsigned NOT NULL default '0', "
                                    " PRIMARY KEY  (`ID`) "
                                    " ) TYPE=MyISAM"; 

const char *PamelaDBOperations::TM_S4_CALIB = "CREATE TEMPORARY TABLE `TM_S4_CALIB` ( " 
                                    " `ID` bigint(64) unsigned NOT NULL auto_increment, "
                                    " `ID_REGISTRY` bigint(64) unsigned NOT NULL default '0', "
                                    " `REG_EVENT` bigint(64) unsigned NOT NULL default '0', "
                                    " `TIME` bigint(64) unsigned NOT NULL default '0', "
                                    " `PARAM_FIT0` float unsigned NOT NULL default '0', "
                                    " `PARAM_FIT1` float unsigned NOT NULL default '0', "
                                    " PRIMARY KEY  (`ID`) "
                                    " ) TYPE=MyISAM"; 
                                    
const char *PamelaDBOperations::TM_TRK_CALIB = "CREATE TEMPORARY TABLE `TM_TRK_CALIB` ( " 
                                    " `ID` bigint(64) unsigned NOT NULL auto_increment, "
                                    " `ID_REGISTRY` bigint(64) unsigned NOT NULL default '0', "
                                    " `REG_EVENT` bigint(64) unsigned NOT NULL default '0', "
                                    " `TIME` bigint(64) unsigned NOT NULL default '0', "
                                    " `TRK_PART` smallint(8) unsigned NOT NULL default '0', "
                                    " `PCK_COUNTER` bigint(64) unsigned NOT NULL default '0', "
                                    " PRIMARY KEY  (`ID`) "
                                    " ) TYPE=MyISAM"; 

const char *PamelaDBOperations::TM_REGISTRY = "CREATE TEMPORARY TABLE `TM_REGISTRY` ( " 
                                    " `ID` bigint(64) unsigned NOT NULL auto_increment, "
                                    " `REG_EVENT` bigint(64) unsigned NOT NULL default '0', "
                                    " `TIME` bigint(64) unsigned NOT NULL default '0', "
                                    " PRIMARY KEY  (`ID`) "
                                    " ) TYPE=MyISAM"; 

const char *PamelaDBOperations::TM_HEADER = "CREATE TEMPORARY TABLE `TM_HEADER` ( "
                                    " `ID` bigint(20) NOT NULL auto_increment, "
                                    " `ID_ROOT` bigint(20) unsigned NOT NULL default '0', "
                                    " `COUNTER` int(10) unsigned NOT NULL default '0', "
                                    " `OBT` int(10) unsigned NOT NULL default '0', "
                                    " `EVENT` int(10) unsigned NOT NULL default '0', "
                                    " `TIME` bigint(20) unsigned NOT NULL default '0', "
                                    " `TREE_NAME` varchar(50), "
                                    " PRIMARY KEY  (`ID`) "
                                    " ) TYPE=MyISAM ";

const char *PamelaDBOperations::TM_TIMESYNC = "CREATE TEMPORARY TABLE `TM_TIMESYNC` ("
                                    " `ID` bigint(20) unsigned NOT NULL auto_increment,"
                                    " `TS_OBT` bigint(20) unsigned NOT NULL default '0',"
                                    " `TS_TIME` bigint(20) unsigned NOT NULL default '0',"
                                    " `TYPE` smallint(5) unsigned NOT NULL default '0',"
                                    " `ID_HEADER` int(10) unsigned NOT NULL default '0',"
                                    " `ID_RAW` int(10) unsigned NOT NULL default '0',"
                                    " PRIMARY KEY  (`ID`)"
                                    " ) TYPE=MyISAM ";


static LoggerPtr logger = Logger::getLogger(_T("PamelaDBOperation"));

/**
* Insert a new row into GL_RAW table. Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn         An active connection to a suitable (for PAMELA) database.
* @param pathName     The path to the raw file.
* @param fileName     The name of the raw file.
* @param symbolicName An optional parameter.
*/
bool PamelaDBOperations::insertPamelaRawFile(TSQLServer *conn, const char *pathName, const char *fileName)
                                      throw (YSQLNotConnectedException, YSQLQueryException, YSQLDuplicateRowException){
    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    const char *id =  PamelaDBOperations::getID_RAW(conn, pathName, fileName);
    stringstream oss;
    TSQLResult *result = 0;

    if (id != NULL) throw YSQLDuplicateRowException("Existing RawFile");

    oss.str("");
    oss << "INSERT INTO GL_RAW (PATH, NAME) VALUES ('"
        << pathName << "', '" << fileName << "')";
    if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    return OK;
}


/**
* Insert a new row into GL_ROOT table. It's private beacuse does not perform cross check on idRaw.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param pathName   The path to the root file.
* @param fileName   The name of the root file.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::insertPamelaRootFiles(TSQLServer *conn, const char *pathName, const char *fileName, const char *idRaw)
                                      throw (YSQLNotConnectedException, YSQLQueryException){
    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream oss;

    oss.str("");
    oss << "INSERT INTO GL_ROOT (ID_RAW, PATH, NAME) VALUES ('"
        << idRaw << "', '" << pathName << "', '" << fileName << "')";
    //cout << oss.str().c_str() << "\n" ;
    if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    return OK;
}

/**
* Insert all the new rows into GL_ROOT. 
* The raw file indicates in the parameters should be already been stored in the database.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn           An active connection to a suitable (for PAMELA) database.
* @param pathName       The path to the root file.
* @param fileName       The name of the root file.
* @param rawPathName    The path to the raw file used to generate the root files.
* @param rawFileName    The name of the raw file used to generate the root files.
*/
int PamelaDBOperations::insertPamelaRootFiles(TSQLServer *conn, const char *pathName, const char *fileName, const char *rawPathName, const char *rawFileName)
                                      throw (YSQLNotConnectedException, YSQLQueryException){

  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  stringstream oss;
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;

  oss.str("");
  oss << " SELECT COUNT(GL_ROOT.ID_RAW), GL_RAW.ID FROM GL_RAW "
      << " LEFT JOIN GL_ROOT "
      << " ON GL_RAW.ID = GL_ROOT.ID_RAW "
      << " WHERE GL_RAW.PATH = '" << rawPathName << "' AND "
      << " GL_RAW.NAME = '" << rawFileName << "' GROUP BY GL_RAW.ID ";
  //cout << oss.str().c_str() << "\n";
  result = conn->Query(oss.str().c_str());
  row = result->Next();
  if ((row != NULL) && (atol(row->GetField(0)) > 0)) throw YSQLQueryException("ROOT file already inserted!");
   
  
  //if ((row != NULL) && (atol(row->GetField(0))) == 0){
    //cout << "pathName: " << pathName << " count: " << row->GetField(0) << " ID: " << row->GetField(1) << "\n";
    //conn->Query("BEGIN WORK");
    if (insertPamelaRootFiles(conn, pathName, fileName, row->GetField(1))) {
      //conn->Query("COMMIT");
    } else {
      //conn->Query("ROLLBACK");
    //}
  }
  return OK;
}


int PamelaDBOperations::insertPamelaTM_HEADER(TSQLServer *conn, const char *idRaw)
                        throw (YSQLNotConnectedException, YSQLQueryException){
    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    conn->Query(PamelaDBOperations::TM_HEADER);
    stringstream   oss;    
    TSQLResult  *result = 0;

    /*Took all the root file related to a specific raw file*/
    oss.str("");
    oss << "SELECT ID, PATH, NAME FROM GL_ROOT WHERE ID_RAW = '" << idRaw << "'";
    //cout << oss.str().c_str() << "\n";
    result = conn->Query(oss.str().c_str());
    if (result == 0) throw YSQLQueryException(oss.str().c_str());
    TSQLRow             *row     = 0;
    TFile               *file    = 0;
    TTree               *tr      = 0;
    EventHeader         *eh      = 0;
    PscuHeader          *ph      = 0;
    ULong64_t            nevents = 0;
    const char          *idRoot  = 0;
    row = result->Next();
    if (row == 0) return NOK;

    oss.str("");
    oss << row->GetField(1) << "/" << row->GetField(2);
    idRoot  = row->GetField(0);
    //cout << "Sto per aprire il file: " << oss.str().c_str() << "\n";
    file    = YFile::Open(oss.str().c_str());
    
    pcksList packetsNames;
    pcksList::iterator Iter;
    getPacketsNames(packetsNames);
    for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
        tr      = (TTree*)file->Get(*Iter);
        if (tr == NULL || tr->IsZombie()) {
                cout << "Missing Tree: " << *Iter << "\n";
                continue;
        }
        nevents = tr->GetEntries();
        tr->SetBranchAddress("Header", &eh);
        for (UInt_t i = 0; i < nevents; i++){
            tr->GetEntry(i);
            ph = eh->GetPscuHeader();
            oss.str("");
            oss << "INSERT INTO TM_HEADER (COUNTER, EVENT, ID_ROOT, OBT, TREE_NAME) VALUES ('"
                << ph->Counter     << "', '" << i << "', '" << idRoot << "', '" 
                << ph->OrbitalTime << "', '" << *Iter <<"')";
            //cout << oss.str().c_str() << "\n";
            if (conn->Query(oss.str().c_str()) == 0) {
                //delete result;
                throw YSQLQueryException(oss.str().c_str());
            }
        }
    }
    return OK;
}


/**
* Collect all the timesyncs data contained in the specified raw file and
* fill the GL_TIMESYNC table. 
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn           An active connection to a suitable (for PAMELA) database.
* @param rawPathName    The path to the raw file.
* @param rawFileName    The name of the raw file.
*/
int PamelaDBOperations::insertPamelaGL_TIMESYNC(TSQLServer *conn, const char *rawPathName, const char *rawFileName)
                      throw (YSQLNotConnectedException, YSQLQueryException){
  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  const char *id =  PamelaDBOperations::getID_RAW(conn, rawPathName, rawFileName);
  stringstream   oss;
  
  if (id == NULL)  throw YSQLQueryException(RAW_FILE_NOT_FOUND);
  /*
   * Verify that the TIMESYNC have been not already processed
   */
  oss.str("");
  oss << " SELECT COUNT(GL_TIMESYNC.ID) FROM GL_TIMESYNC "
      << " LEFT JOIN GL_RAW "
      << " ON GL_RAW.ID = GL_TIMESYNC.ID_RAW "
      << " WHERE GL_TIMESYNC.ID_RAW = " << id;
  TSQLResult *result = conn->Query(oss.str().c_str());
  if (result == NULL) throw YSQLQueryException(oss.str().c_str());
  TSQLRow *row = result->Next();
  if ((row != NULL) && (atol(row->GetField(0)) > 0)) throw YSQLQueryException("TIMESYNC already existing!");

  try {
   insertPamelaGL_TIMESYNC(conn, id);
  } catch (YSQLQueryException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
  } catch (YSQLNotConnectedException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
  } 
  return OK;
}

int PamelaDBOperations::insertPamelaGL_TIMESYNC(TSQLServer *conn, const char *idRaw) throw (YSQLNotConnectedException, YSQLQueryException){
    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    conn->Query(PamelaDBOperations::TM_TIMESYNC);
    stringstream   oss;
        
    TSQLResult  *result = 0;
    TSQLResult  *secResult = 0;

    oss.str("");
    oss << "SELECT ID, PATH, NAME FROM GL_ROOT WHERE ID_RAW = '" << idRaw << "'";
    result = conn->Query(oss.str().c_str());
    //cout << oss.str().c_str() << "\n";
    //if (result->GetRowCount() == 0) throw YSQLQueryException(oss.str().c_str());
    if (result->GetRowCount() == 0) throw YSQLQueryException(oss.str().c_str());
    TSQLRow         *row        = 0;
    TFile           *file       = 0;
    TTree           *tr         = 0;
    McmdEvent       *mcmdev     = 0;
    McmdRecord      *mcmdrc     = 0;
    TArrayC         *mcmddata   = 0;
    RunHeaderEvent  *runheader  = 0;
    RunTrailerEvent *runtrailer = 0;
    const char      *idRoot     = 0;
    const char      *idHeader   = 0;
    ULong64_t        nevents    = 0;
    ULong64_t        timesync   = 0;
    string           name;

    row = result->Next();
    while (!(row == 0)){
        oss.str("");
        oss << row->GetField(1) << "/" << row->GetField(2);
        //cout << oss.str().c_str() << "\n";
        name    = row->GetField(2);
        idRoot  = row->GetField(0);
        file    = YFile::Open(oss.str().c_str());

        /* 
         * process RunHeader
         */
        tr      = (TTree*)file->Get("RunHeader");
        nevents = tr->GetEntries();
        tr->SetBranchAddress("RunHeader", &runheader);
        for (UInt_t i = 0; i < nevents; i++){
        tr->GetEntry(i);
        idHeader = "000";
        oss.str("");
        oss << "INSERT INTO TM_TIMESYNC (ID_HEADER, TS_OBT, TS_TIME, TYPE, ID_RAW) VALUES ('"
                <<  idHeader << "', '" << dec << runheader->OBT_TIME_SYNC << "', '" 
                << dec << runheader->LAST_TIME_SYNC_INFO 
        << "', 0x20, '" << idRaw << "')";
        //cout << oss.str().c_str() << "\n";
        if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
        }
        
        /* 
         * process RunTrailer
         */
        tr      = (TTree*)file->Get("RunTrailer");
        nevents = tr->GetEntries();
        tr->SetBranchAddress("RunTrailer", &runtrailer);
        for (UInt_t i = 0; i < nevents; i++){
        tr->GetEntry(i);
        idHeader = "000";
        oss.str("");
        oss << "INSERT INTO TM_TIMESYNC (ID_HEADER, TS_OBT, TS_TIME, TYPE, ID_RAW) VALUES ('"
                << idHeader  << "', '" << dec << runtrailer->OBT_TYME_SYNC << "', '" 
                << dec << runtrailer->LAST_TYME_SYNC_INFO 
                << "', 0x21, '" << idRaw << "')";
        //cout << oss.str().c_str() << "\n";
        if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
        }

        /* 
         * process Mcmd
         */
        long int recEntries;
        tr = (TTree*)file->Get("Mcmd");
        tr->SetBranchAddress("Mcmd", &mcmdev);
        nevents = tr->GetEntries();
        for (UInt_t i = 0; i < nevents; i++){
                tr->GetEntry(i);
                recEntries = mcmdev->Records->GetEntries();
                for (int j = 0; j < recEntries; j++){
                        mcmdrc = (McmdRecord*)mcmdev->Records->At(j);
                        if (mcmdrc->ID1 == 0xE0){
                        mcmddata = mcmdrc->McmdData;
                        timesync = (((ULong64_t)mcmddata->At(0)<<24)&0xFF000000) + 
                                        (((ULong64_t)mcmddata->At(1)<<16)&0x00FF0000) + 
                                        (((ULong64_t)mcmddata->At(2)<<8)&0x0000FF00)  + 
                                        (((ULong64_t)mcmddata->At(3))&0x000000FF);
                        idHeader = "000";
                        oss.str("");
                        oss << "INSERT INTO TM_TIMESYNC (ID_HEADER, TS_OBT, TS_TIME, TYPE, ID_RAW) VALUES ('"
                                << idHeader  << "', '" << dec << mcmdrc->MCMD_RECORD_OBT << "', '" 
                                << dec << timesync << "', 0x55, '" << idRaw << "')";
                        //cout << oss.str().c_str() << "\n";
                        if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
                        }
                }
        }
        file->Close();
        row = result->Next();
    }
    
    oss.str("");
    oss << "select TS_OBT, TS_TIME "
        << " FROM TM_TIMESYNC "
        << " WHERE "
        << " ID_RAW = " << idRaw << " AND "
        << " ((TS_OBT <> 0) OR (TS_TIME <> 0)) AND "
        << " TYPE   = 0x55 ";
    //cout << oss.str().c_str() << "\n";
    secResult = conn->Query(oss.str().c_str());
    Long64_t sizeMcmdTS = secResult->GetRowCount();
    double *mcmdTS = new double[sizeMcmdTS];
    int index = 0;
    double tho = 1./1000.;
    row = secResult->Next();
    while (!(row == 0)) {
      if ((row->GetField(0) != NULL) && (row->GetField(1) != NULL)){
        mcmdTS[index++] = atof(row->GetField(1)) - atof(row->GetField(0))*tho;
      } 
      row = secResult->Next();
    }
    
    oss.str("");
    oss << "select TS_OBT, TS_TIME "
        << " FROM TM_TIMESYNC "
        << " WHERE "
        << " ID_RAW = " << idRaw << " AND "
        << " ((TS_OBT <> 0) OR (TS_TIME <> 0)) AND "
        << " TYPE = 0x20 ";
    //cout << oss.str().c_str() << "\n";
    secResult = conn->Query(oss.str().c_str());
    Long64_t sizeHead = secResult->GetRowCount();
    double *runHead = new double[sizeHead];
    index = 0;
    row = secResult->Next();
    while (!(row == 0)) {
      if ((row->GetField(1) != NULL) && (row->GetField(0) != NULL)){
        runHead[index++] = atof(row->GetField(1)) - atof(row->GetField(0));
      } 
      row = secResult->Next();
    }

    oss.str("");
    oss << "select TS_OBT, TS_TIME "
        << " FROM TM_TIMESYNC "
        << " WHERE "
        << " ID_RAW = " << idRaw << " AND "
        << " ((TS_OBT <> 0) OR (TS_TIME <> 0)) AND "
        << " TYPE = 0x21 ";
    //cout << oss.str().c_str() << "\n";
    secResult = conn->Query(oss.str().c_str());
    Long64_t sizeTrail = secResult->GetRowCount();
    double *runTrail = new double[sizeTrail];
    index = 0;
    row = secResult->Next();
    while (!(row == 0)) {
      if ((row->GetField(1) != NULL) && (row->GetField(0) != NULL)){
        runTrail[index++] = atof(row->GetField(1)) - atof(row->GetField(0));
      }
      row = secResult->Next();
    }

    oss.str("");
    oss << "INSERT INTO GL_TIMESYNC (DELTA_OBT_TMSYNC, RMS_DELTA, TYPE, ID_RAW) VALUES ('"
        << dec << TMath::Mean(sizeMcmdTS,mcmdTS)*1000. << "', '" << dec << TMath::RMS(sizeMcmdTS, mcmdTS)*1000. << "', " 
        << " 0x55, '" << idRaw << "')";
    //cout << oss.str().c_str() << "\n";
    conn->Query(oss.str().c_str());

    oss.str("");
    oss << "INSERT INTO GL_TIMESYNC (DELTA_OBT_TMSYNC, RMS_DELTA, TYPE, ID_RAW) VALUES ('"
        << dec << TMath::Mean(sizeHead,runHead)*1000. << "', '" << dec << TMath::RMS(sizeHead,runHead)*1000. << "', " 
        << " 0x20, '" << idRaw << "')";
    //cout << oss.str().c_str() << "\n";
    conn->Query(oss.str().c_str());

    oss.str("");
    oss << "INSERT INTO GL_TIMESYNC (DELTA_OBT_TMSYNC, RMS_DELTA, TYPE, ID_RAW) VALUES ('"
        << dec << TMath::Mean(sizeTrail,runTrail)*1000.  << "', '" << dec << TMath::RMS(sizeTrail,runTrail)*1000.  << "', " 
        << " 0x21, '" << idRaw << "')";
    //cout << oss.str().c_str() << "\n";
    conn->Query(oss.str().c_str());

    oss.str("");
    oss   << " DROP TABLE IF EXISTS TM_TIMESYNC ";
    // GOOD FOR MySQL ver > 3.2
    //oss   << " DROP TEMPORARY TABLE IF EXISTS TM_TIMESYNC ";
    conn->Query(oss.str().c_str());
    
    return OK;
}


/**
* Delete the duplicate row in TM_HEADER and TM_TIMESYNC
* generated by the specified raw file and fill the TM_TIMESYNC table. 
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::deletePamelaDuplicate(TSQLServer *conn, const char *idRaw)
                      throw (YSQLNotConnectedException, YSQLQueryException) {

    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream   oss;
        
    TSQLResult  *result = 0;
    TSQLRow     *row = 0;
    bool dup = true;
//---------------- Clean up the multiple rows in TM_HEADER ---------------------------------------
    /* 
     * Select the duplicate COUNTER-OBT pairs into a holding table
     */
    oss.str("");
    oss   << " CREATE TEMPORARY TABLE holdkey "
          << " SELECT ID, OBT, COUNTER, ID_ROOT, count(*) "
          << " FROM TM_HEADER "
          << " WHERE ID_ROOT = " << idRaw
          << " GROUP BY OBT, COUNTER"
          << " HAVING count(*) > 1";
    //cout << oss.str().c_str() << "\n";
    if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
       
    /*
     * Select the duplicate rows into a holding table, eliminating duplicates in the process
     */
    oss.str("");
    oss   << " CREATE TEMPORARY TABLE holddups "
          << " SELECT DISTINCT TM_HEADER.* "
          << " FROM TM_HEADER, holdkey "
          << " WHERE TM_HEADER.COUNTER = holdkey.COUNTER "
          << " AND TM_HEADER.OBT       = holdkey.OBT ";
    //cout << oss.str().c_str() << "\n";
    if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());

    /*
     * Verify that holddup contains only one row for each pair
     */
    oss.str("");
    oss   << " SELECT ID, COUNTER, ID_ROOT, count(*) "
          << " FROM holddups "
          << " GROUP BY COUNTER";
    //cout << oss.str().c_str() << "\n";
    result = conn->Query(oss.str().c_str());
    if (result == 0) throw YSQLQueryException(oss.str().c_str());

    dup = true;
    while (true) {
        row = result->Next();
        if (row == 0) break;
        if ((!atol(row->GetField(3)) == 0)){
            dup = false;
            break;
        } 
    }
    
    if (dup){
        /*
         * Delete the duplicate rows from TM_HEADER
         */
        oss.str("");
        oss   << " DELETE TM_HEADER "
              << " FROM TM_HEADER, holdkey "
              << " WHERE TM_HEADER.COUNTER = holdkey.COUNTER "
              << " AND TM_HEADER.ID_ROOT = holdkey.ID_ROOT ";
        //cout << oss.str().c_str() << "\n";
        //if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
        conn->Query(oss.str().c_str());

        /*
         * Put the unique rows back in TM_HEADER
         */
        oss.str("");
        oss   << " INSERT TM_HEADER SELECT * FROM holddups ";
        //cout << oss.str().c_str() << "\n";
        //if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
        conn->Query(oss.str().c_str());
    }

    /*
     * Delete the temporary Tables
     */
    oss.str("");
    oss   << " DROP TABLE IF EXISTS holdkey ";
    // GOOD FOR MySQL ver > 3.2
    //oss   << " DROP TEMPORARY TABLE IF EXISTS holdkey ";
    //cout << oss.str().c_str() << "\n";
    //if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    conn->Query(oss.str().c_str());
    oss.str("");
    oss   << " DROP TABLE IF EXISTS holddups ";
    // GOOD FOR MySQL ver > 3.2
    //oss   << " DROP TEMPORARY TABLE IF EXISTS holddups ";
    //cout << oss.str().c_str() << "\n";
    //if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    conn->Query(oss.str().c_str());
//---------------- Clean up the multiple rows in TM_HEADER   ---------------------------------------
    return OK;
}


/**
* Assign the collectied TIMESYNCs to the T_HEADERs row relative to the 
* same ID_ROOT unpacked data.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::assignPamelaTIMESYNC(TSQLServer *conn, const char *idRaw)
                      throw (YSQLNotConnectedException, YSQLQueryException) {

    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream   oss;
        
    TSQLResult  *result = NULL, *secResult = NULL;
    TSQLRow     *row    = NULL, *secRow    = NULL;
    ULong64_t    syncTIME = 0, absTime = 0;

    /* In any case this parameter can be changed to set the timesync scale*/
    unsigned long id_Raw = (unsigned long)atol(idRaw);
    bool          validTS = false;
    int           types[] = {0x55, 0x20, 0x21};
    Long64_t      boot_Num = -1;
    /*--------------------------------------------------*/

    oss.str("");
    oss << " SELECT COUNT(GL_TIMESYNC.DELTA_OBT_TMSYNC), GL_RAW.BOOT_NUMBER, SUM(GL_TIMESYNC.DELTA_OBT_TMSYNC) from GL_TIMESYNC, GL_RAW "
        << " WHERE GL_TIMESYNC.ID_RAW = GL_RAW.ID AND  GL_TIMESYNC.ID_RAW = " << dec << id_Raw 
        << " GROUP BY GL_TIMESYNC.ID_RAW";
      //        << " AND GL_RAW.BOOT_NUMBER IS NOT NULL GROUP BY GL_TIMESYNC.ID_RAW";
    //cout << oss.str().c_str() << "\n";
    result = conn->Query(oss.str().c_str());
    if (result == 0) throw YSQLQueryException("result: No Timesync!");
    row = result->Next();
    if (row == 0) throw YSQLQueryException("raw: No Timesync!");
    if (row->GetField(1) != 0) boot_Num = atol(row->GetField(1));
    //cout << "Questo è il boot number: " << dec << boot_Num;

    
    //Get the list of the id_raw belonging to the same BOOT_NUMBER. This have been done to foreseen 
    //situation where a file does not contain a valid timesync, BUT HOPEFULLY A VALID BOOT NUMBER!
    if ((atol(row->GetField(0)) > 0) && (atol(row->GetField(2)) == 0)){
      oss.str("");
      oss << " SELECT ID from GL_RAW "
          << " WHERE  GL_RAW.BOOT_NUMBER = " << dec << boot_Num ;
      //cout << oss.str().c_str() << "\n";
      secResult = conn->Query(oss.str().c_str());
    }
      
    if (atol(row->GetField(0)) > 0){
      //Looks for a valid TIMESYNC (before 0x55, then 0x20 and finally 0x21)
      while (true){ 
        for(int ty = 0; ty < 3; ty++){
          oss.str("");
          oss << " SELECT COUNT(GL_TIMESYNC.DELTA_OBT_TMSYNC) from GL_TIMESYNC "
              << " WHERE  GL_TIMESYNC.ID_RAW = '" << dec << id_Raw << "' AND GL_TIMESYNC.TYPE = " << dec << types[ty]
              << " AND GL_TIMESYNC.DELTA_OBT_TMSYNC <> 0";
          //cout << oss.str().c_str() << "\n";
          result = conn->Query(oss.str().c_str());
          row = result->Next();
          //Has found a good TIMESYNC?
          if ((row != NULL) && (atol(row->GetField(0)) > 0)){
            oss.str("");
            oss << " SELECT GL_TIMESYNC.DELTA_OBT_TMSYNC from GL_TIMESYNC "
                << " WHERE "
                << " GL_TIMESYNC.ID_RAW = " << dec << id_Raw << " AND "
                << " GL_TIMESYNC.TYPE = " << dec << types[ty];
            //cout << oss.str().c_str() << "\n";
            result      = conn->Query(oss.str().c_str());
            row = result->Next();
            if (row == 0) throw YSQLQueryException("No Timesync!");
            syncTIME    = (ULong64_t)atof(row->GetField(0));
            validTS = true;
            break;
          }
        }

        //Should exits from the loop?
        if (validTS) break;

        //Get another id_raw
        if (secResult != NULL) {
          secRow = secResult->Next();
          if (secRow == NULL) throw YSQLQueryException("No Timesync!");
          id_Raw = (ULong64_t)atof(secRow->GetField(0));
        }
      }
    }

    //Collect the TM_HEADER to assign them the correct TIMESYNC
    oss.str("");
    oss << " SELECT TM_HEADER.OBT, TM_HEADER.ID" 
        << " FROM GL_ROOT, TM_HEADER, GL_RAW "
        << " WHERE "
        << " GL_ROOT.ID_RAW =  " << idRaw << " AND "
        << " TM_HEADER.TIME = 0 AND "
        << " TM_HEADER.ID_ROOT =  GL_ROOT.ID ";
    //cout << oss.str().c_str() << "\n";
    result = conn->Query(oss.str().c_str());
    if (result == 0) throw YSQLQueryException(oss.str().c_str());
    
    //Calculate the absolute time for the GL_HEADER item
    while (true) {   
      row = result->Next();
      if (row == 0) break;
      absTime   =  (ULong64_t)atof(row->GetField(0)) + syncTIME;
      oss.str("");
      oss << " UPDATE TM_HEADER "
          << " SET TM_HEADER.TIME = '" << absTime << "'"
          << " WHERE TM_HEADER.ID = '" << row->GetField(1) << "'";
      //cout << oss.str().c_str() << "\n";
      if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    }
    return OK;
}


/**
* Create the Registry files connected to the processed unpaking.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param pathName       The path to the root file.
* @param fileName       The name of the root file.
* @param rawPathName    The path to the raw file used to generate the root files.
* @param rawFileName    The name of the raw file used to generate the root files.
*/
int PamelaDBOperations::createPamelaRegistry(TSQLServer *conn, const char *rawPathName, const char *rawFileName, bool overwrite = false)
                                      throw (YSQLNotConnectedException, YSQLQueryException, YNotExistingFileException, YExistingFileException){
  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  const char *id =  PamelaDBOperations::getID_RAW(conn, rawPathName, rawFileName);
  if (id == NULL)  throw YSQLQueryException(RAW_FILE_NOT_FOUND);
  
  try {
        createPamelaRegistry(conn, id, overwrite);
  } catch (YSQLQueryException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
  } catch (YSQLNotConnectedException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
  } catch (YNotExistingFileException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
  }
  
  return OK;
}

/**
* Create the Registry files connected to the processed unpaking.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::createPamelaRegistry(TSQLServer *conn, const char *idRaw, bool overwrite = false)
                      throw (YSQLNotConnectedException, YSQLQueryException, YNotExistingFileException, YExistingFileException) {
    if (conn == 0) throw YSQLNotConnectedException("Connection missing");

    stringstream   oss;
    TSQLResult      *result, *secResult;
    TSQLRow         *row,    *secRow;
    ULong64_t        offset_date;

    /*Took all the root file related to a specific raw file*/
    oss.str("");
    oss << "SELECT OFFSET_DATE FROM GL_RAW WHERE ID = '" << idRaw << "'";
    result = conn->Query(oss.str().c_str());
    row = result->Next();
    if (row != NULL && row != 0 && row->GetField(0) != NULL && row->GetField(0) != ""){
        offset_date = TDatime(row->GetField(0)).Convert();
    } else {
        return NOK;
    }

    ///TBD: Put the check "IF THE REGISTRY ALREDY EXISTS" before this try.
    try {
      if (insertPamelaTM_HEADER(conn, idRaw)){
        deletePamelaDuplicate(conn, idRaw);
        assignPamelaTIMESYNC(conn, idRaw);
      }
    }  catch (YSQLQueryException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    } catch (YSQLNotConnectedException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    }

    const char      *idRoot;
    string          registryName;
    RegistryEvent   *registry   = new RegistryEvent();
    TFile           *file       = 0;
    TTree           *tr         = 0;
    TBranch         *branch     = 0;
    cOrbit          *orbit      = 0;

    /*Took all the root file related to a specific raw file*/
    oss.str("");
    oss << "SELECT ID, PATH, NAME FROM GL_ROOT WHERE ID_RAW = '" << idRaw << "'";
    result = conn->Query(oss.str().c_str());
    row = result->Next();
    if (row == 0) return NOK;

    idRoot      = row->GetField(0);
    oss.str("");
    oss << row->GetField(1) << "/" << row->GetField(2);
    
    file    = YFile::Open(oss.str().c_str(), "UPDATE");
    pcksList packetsNames;
    pcksList::iterator Iter;
    getPacketsNames(packetsNames);
    
    for(Iter = packetsNames.begin(); Iter != packetsNames.end(); Iter++){
        //cout << "Tree Name: " << *Iter << "\n";
        tr      = (TTree*)file->Get(*Iter);
                if (tr == NULL || tr->IsZombie()) {
                        cout << "Missing Tree: " << *Iter << "\n";
                        continue;
                }
        //Branch Registry exist?
        if (!tr->GetBranchStatus("Registry")){
                branch  = tr->Branch("Registry", "pamela::RegistryEvent", &registry);
        } else {
                if (!overwrite) throw YExistingFileException("The Registry branches already exists: you have to force overwriting!");
                tr->SetBranchAddress("Registry", &registry);
                branch = tr->GetBranch("Registry");
                branch->Reset();
        }
        
        oss.str("");
        oss << "SELECT TLE1, TLE2, TLE3"
                << " FROM GL_TLE "
                << " WHERE FROM_TIME >= 0 ";
        //cout << oss.str().c_str() << "\n"; 
        result = conn->Query(oss.str().c_str());
        row = result->Next();
        if (!(row == NULL)){
                string tle1 = row->GetField(0);
                string tle2 = row->GetField(1);
                string tle3 = row->GetField(2);
                cTle tle(tle1, tle2, tle3);
                orbit = new cOrbit(tle);
        } else {
                cout << "TLE table empty." << "\n"; 
                return NOK;
        }
        
        
        /*Took all the TM_HEADER rows related to a specific root file and TREE_NAME*/
        oss.str("");
        oss << "SELECT EVENT, TIME FROM TM_HEADER WHERE ID_ROOT = '" << idRoot << "' " 
            << " AND TREE_NAME = '" << *Iter << "' ORDER BY EVENT ASC";
        secResult = conn->Query(oss.str().c_str());
        //cout << oss.str().c_str() << "\n";

        //Fill the 'Registry' TBranch
        secRow = secResult->Next();
        initize_();
        while (secRow != NULL){
                registry->event   = (unsigned int)atoi(secRow->GetField(0));
                registry->absTime = (ULong64_t)atof(secRow->GetField(1)) + (offset_date*1000);
                setGeo(orbit, registry);
                branch->Fill();
                secRow = secResult->Next();
                /*
                 cout << "FileName: " << registryName 
                   << "absTime: "  << dec << registry->absTime
                   << "event: "    << dec << registry->event << "\n";
                   */
        }
     }
     file->Write(0,TObject::kOverwrite);
     file->Close();
}
    
/**
* Assign the BOOT_NUMBER to the raw file.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn           An active connection to a suitable (for PAMELA) database.
* @param rawPathName    The path to the raw file used to generate the root files.
* @param rawFileName    The name of the raw file used to generate the root files.
*/
int PamelaDBOperations::assignBOOT_NUMBER(TSQLServer *conn, const char *rawPathName, const char *rawFileName)
                                      throw (YSQLNotConnectedException, YSQLQueryException){
  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  stringstream oss;
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  oss.str("");
  oss << "SELECT ID, BOOT_NUMBER FROM GL_RAW WHERE "
      << " PATH = '" << rawPathName << "' AND "
      << " NAME = '" << rawFileName << "' ";
  result = conn->Query(oss.str().c_str());
  //if (result == NULL) throw YSQLQueryException(oss.str().c_str());
  row = result->Next();
  if (row == 0) throw YSQLQueryException("The file is not in the database");
  if (row->GetField(1) != 0) throw YSQLQueryException("BOOT number already existing!");
  return assignBOOT_NUMBER(conn, row->GetField(0));

}


/**
* Update GL_RAW.BOOT_NUMBER columns.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
* 
*/
int PamelaDBOperations::assignBOOT_NUMBER(TSQLServer *conn, const char *idRaw) throw (YSQLNotConnectedException, YSQLQueryException) {

    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream     oss;
    TSQLResult      *result;
    TSQLRow         *row;
    
    TFile           *file       = 0;
    TTree           *tr         = 0;

    oss.str("");
    oss << "SELECT ID, PATH, NAME FROM GL_ROOT WHERE ID_RAW = '" << idRaw << "'";
    //cout << oss.str().c_str() << "\n";
    result = conn->Query(oss.str().c_str());
    if (result == 0) throw YSQLQueryException(oss.str().c_str());
    row = result->Next();
    oss.str("");
    oss << row->GetField(1) << "/" << row->GetField(2);
    //cout << oss.str().c_str() << "\n";
    TFile *varDumpFile  = YFile::Open(oss.str().c_str());

    TTree *trDumpEv = (TTree*)varDumpFile->Get("VarDump");
    if (trDumpEv == NULL) throw YSQLQueryException("Event file is empty");
    
    VarDumpEvent  *vde = 0;
    VarDumpRecord *vdr = 0;

    trDumpEv->SetBranchAddress("VarDump", &vde);
    if (trDumpEv->GetEntries() > 0){
      trDumpEv->GetEntry(0);
      vde->Records->GetEntries();
      if (vde->Records->GetEntries() > 0){
        vdr = (VarDumpRecord*)vde->Records->At(6);
        oss.str("");
        oss << " UPDATE GL_RAW "
            << " SET GL_RAW.BOOT_NUMBER = '" << dec << vdr->VAR_VALUE << "'"
            << " WHERE GL_RAW.ID = '" << idRaw << "'";
        conn->Query(oss.str().c_str()); 
        //cout << "Boot Number: "  << dec << vdr->VAR_ID       << "</VAR_ID> \n";
        //cout << "Boot Number: "  << dec << vdr->VAR_VALUE    << "</VAR_VALUE> \n";
      } else {
        throw YSQLQueryException("No VarDump!");
      }
    }
}


/**
* Insert RUN items into the GL_RUN table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param rawPathName    The path to the raw file used to generate the root files.
* @param rawFileName    The name of the raw file used to generate the root files.
*/
int PamelaDBOperations::insertPamelaRUN(TSQLServer *conn, const char *pathName, const char *fileName)
                                      throw (YSQLNotConnectedException, YSQLQueryException, YNotExistingFileException, YEmptyObjectException){

  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  const char *id =  PamelaDBOperations::getID_RAW(conn, pathName, fileName);
  if (id == NULL)  throw YSQLQueryException(RAW_FILE_NOT_FOUND);
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  /*
   * Verify that the RUN have been not already processed
   */
  stringstream   oss;
  oss.str("");
  oss << " SELECT COUNT(GL_RUN.ID) FROM GL_RUN "
      << " LEFT JOIN GL_ROOT "
      << " ON GL_ROOT.ID = GL_RUN.ID_REG_RUN "
      << " WHERE GL_ROOT.ID_RAW = " << id;
  result = conn->Query(oss.str().c_str());
  row = result->Next();
  if ((row != NULL) && (atol(row->GetField(0)) > 0)) throw YSQLQueryException("Runs already inserted in the database");
  /*
   * Finally extract the RUNs informations
   */
   try {
        insertPamelaRUN(conn, id);
   }  catch (YSQLQueryException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    } catch (YSQLNotConnectedException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    } catch (YNotExistingFileException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    } catch (YEmptyObjectException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    }
    return OK;
}

/**
* Insert RUN items into the GL_RUN table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
* 
* STILL TO BE DONE: When a new RUN is inserted it have to update the GL_CALIB before run
* any insertNEW_CALIBXXX in order to have always the run into one calibration!!!
*/
int PamelaDBOperations::insertPamelaRUN(TSQLServer *conn, const char *idRaw)
                      throw (YSQLNotConnectedException, YSQLQueryException, YNotExistingFileException, YEmptyObjectException) {

    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream   oss;
    TSQLResult      *result = 0;
    TSQLRow         *row = 0;
    const char      *idRoot = 0;
    RegistryEvent    *runHeaderRegistry  = new RegistryEvent();
    RegistryEvent    *runTrailerRegistry = new RegistryEvent();
    RegistryEvent    *calPedRegistry     = new RegistryEvent();
    RegistryEvent    *physRegistry       = new RegistryEvent();
    TFile            *rootFile = 0;
    TTree            *calTr,     *physTr,    *runHeaderEvTr  = 0,    *runTrailerEvTr = 0;
    TBranch          *calBr,     *physRegBr, *runHeaderRegBr = 0,    *runHeaderEvBr  = 0,     *runTrailerRegBr    = 0;
    RunHeaderEvent   *runheader  = 0;
    ULong64_t         nevents    = 0;

/*----------BEGIN - Create the temporary table to collect the PhysicsRegistry items-----------*/
    conn->Query(PamelaDBOperations::TM_REGISTRY);

    /*Took the root file related to the Physics*/
    oss << "SELECT ID, PATH, NAME FROM GL_ROOT WHERE ID_RAW = '" << idRaw << "'";
    result = conn->Query(oss.str().c_str());
    //cout << oss.str().c_str() << "\n";

    if (result == NULL) throw YSQLQueryException(oss.str().c_str());
    row      = result->Next();
    idRoot = row->GetField(0);

    oss.str("");
    oss << row->GetField(1) << "/" << row->GetField(2);
    //cout << oss.str().c_str() << "\n";
    rootFile    = YFile::Open(oss.str().c_str());
    if(rootFile->IsZombie()) throw YNotExistingFileException(oss.str().c_str());

    physTr      = (TTree*)rootFile->Get("Physics");
    if (!physTr->GetBranchStatus("Registry")) throw YEmptyObjectException("The 'Registry' TBranch not exist in the 'Physics' TTree");
    physTr->SetBranchAddress("Registry", &physRegistry);
    physRegBr = physTr->GetBranch("Registry");
    nevents = physRegBr->GetEntries();
    //cout << "nEvents: " << dec << nevents << "\n";
    
    /*INSERT the physics items into TM_REGISTRY*/
    for (UInt_t i = 0; i < nevents; i++){
        physRegBr->GetEntry(i);
        oss.str("");
        oss << " INSERT INTO TM_REGISTRY (REG_EVENT, TIME) "
            << " VALUES ( " 
            << dec  << physRegistry->event      << ", "
            << dec  << physRegistry->absTime    << ")";
        conn->Query(oss.str().c_str());
    }
        runHeaderEvTr   = (TTree*)rootFile->Get("RunHeader");
        runHeaderEvTr->SetBranchAddress("RunHeader", &runheader);
        runHeaderEvTr->SetBranchAddress("Registry", &runHeaderRegistry);
        runHeaderRegBr = runHeaderEvTr->GetBranch("Registry");
        
        runTrailerEvTr = (TTree*)rootFile->Get("RunTrailer");
        runTrailerEvTr->SetBranchAddress("Registry", &runTrailerRegistry);
        runTrailerRegBr = runTrailerEvTr->GetBranch("Registry");
/*----------END - Create the temporary table to collect the PhysicsRegistry items-----------*/

/*----------BEGIN - Fill the GL_RUN table collecting RunHeader and Physics items-----------*/

    nevents = runHeaderRegBr->GetEntries();
    //cout << "nEvents: " << dec << nevents << "\n";
    /*
     * NOTE - Below is aassumed as true that RUNHEADER events are synchronized
     *        with the RUNTRAILER data. This is true if data will not skip any 
     *        RunHeader or runTrailer.
     */
    for (UInt_t i = 0; i < nevents; i++){
        runTrailerRegBr->GetEntry(i);

        /*----- DO NOT INVERT!!-----*/
        runHeaderRegBr->GetEntry(i);
        runHeaderEvTr->GetEntry(runHeaderRegistry->event);
        /*----- DO NOT INVERT!!-----*/


        /*
         * This line is just for test. It should guarantee that the run is consistent.
         * The reason for this is that I have verified that on some MCMD TimeSyncTIME
         * the value seems stoned BUT the OBT still run. As consequence can happen to
         * have a runTrailer "older" then the relative runHeader which is obviously 
         * an error.....
         */
        if (runHeaderRegistry->absTime > runTrailerRegistry->absTime) continue;       
        /*---------------------------------------------------------------------------*/

        /* 
         * SELECT the interval of events belonging to this RUN
         */

        oss.str("");
        oss << " SELECT MIN(REG_EVENT), MAX(REG_EVENT) "
            << " FROM TM_REGISTRY "
            << " WHERE TIME BETWEEN " << runHeaderRegistry->absTime 
            << " AND " << runTrailerRegistry->absTime ;
        //cout << oss.str().c_str() << "\n";
        result = conn->Query(oss.str().c_str());
        if (result->GetRowCount() == 0) throw YSQLQueryException(oss.str().c_str());
        row         = result->Next();
        
        if ((row->GetField(0) == NULL) || (row->GetField(1) == NULL)) continue;
        /* 
         * INSERT the GL_RUN item 
         */

        oss.str("");
        oss << " INSERT INTO GL_RUN (ID_REG_RUN, RUNHEADER_TIME, EV_REG_RUNHEADER, "
            << " RUNTRAILER_TIME, EV_REG_RUNTRAILER, TRK_CALIB_USED, "
            << " EV_REG_PHYS_FROM, EV_REG_PHYS_TO, EFF_WRK_SCHEDULE, "
            << " PRH_VAR_TRG_MODE_A, PRH_VAR_TRG_MODE_B, ACQ_BUILD_INFO, ACQ_VAR_INFO) "
            << " VALUES ( " 
            << idRoot       << ", "
            << dec          << runHeaderRegistry->absTime << ", " 
            << dec          << i << ", "
            << dec          << runTrailerRegistry->absTime << ", " 
            << dec          << i << ", "
            << dec          << runheader->TRK_CALIB_USED << ", "
            << row->GetField(0)   << ", "
            << row->GetField(1)   << ", "
            << dec          << runheader->EFFECTIVE_WORKING_SCHEDULE << ", "
            << dec          << runheader->PRH_VAR_TRIGGER_MODE_A << ", "
            << dec          << runheader->PRH_VAR_TRIGGER_MODE_B << ", "
            << dec          << runheader->ACQ_BUILD_INFO << ", "
            << dec          << runheader->ACQ_VAR_INFO
            << ")";
        //cout << oss.str().c_str() << "\n";
        conn->Query(oss.str().c_str());

        /*
         * To be consistent the RUN have to be part of one calibration
         */
        for (int sec = 0; sec < 4 ; sec++){
          oss.str("");
          oss << "SELECT ID FROM GL_CALO_CALIB "
              << " WHERE FROM_TIME <= " <<  dec << runHeaderRegistry->absTime  << " AND "
              << " TO_TIME >=         " <<  dec << runTrailerRegistry->absTime << " AND "
              << " SECTION = "         << dec << sec;
          //cout << oss.str().c_str() << "\n";
          result = conn->Query(oss.str().c_str());
          
          //The RUN is included in some calibration?
          if (result->GetRowCount() == 0) {
            oss.str("");
            oss << "SELECT MAX(TO_TIME) AS MX_TM_CALIB, ID AS ID_CALIB "
                << " FROM  GL_CALO_CALIB " 
                << " WHERE TO_TIME <= " <<  dec << runHeaderRegistry->absTime << " AND "
                << " SECTION = "         << dec << sec
                << " GROUP BY ID ORDER BY MX_TM_CALIB DESC ";
            //cout << oss.str().c_str() << "\n";
            result = conn->Query(oss.str().c_str());
            //Exist a calibration with time less than the RUN?
            //if (result->GetRowCount() == 0) throw YSQLQueryException(oss.str().c_str());
            if (result->GetRowCount() == 0) continue;
            row = result->Next();
            oss.str("");
            oss << "UPDATE GL_CALO_CALIB "
                << " SET TO_TIME =  "   <<  dec << runTrailerRegistry->absTime
                << " WHERE ID = "       <<  row->GetField(1);
            //cout << oss.str().c_str() << "\n";
            result = conn->Query(oss.str().c_str());
        
          }
        }

        // GL_S4_CALIB
        oss.str("");
        oss << "SELECT ID FROM GL_S4_CALIB "
            << " WHERE FROM_TIME <= " <<  dec << runHeaderRegistry->absTime  << " AND "
            << " TO_TIME >=         " <<  dec << runTrailerRegistry->absTime;
        //cout << oss.str().c_str() << "\n";
        result = conn->Query(oss.str().c_str());
        row = result->Next();
        //The RUN is included in some calibration?
        if (row == NULL) {
                oss.str("");
                oss << "SELECT MAX(TO_TIME) AS MX_TM_CALIB, ID AS ID_CALIB "
                << " FROM  GL_S4_CALIB " 
                << " WHERE TO_TIME <= " <<  dec << runHeaderRegistry->absTime 
                << " GROUP BY ID ORDER BY MX_TM_CALIB DESC ";
                //cout << oss.str().c_str() << "\n";
                result = conn->Query(oss.str().c_str());
                //Exist a calibration with time less than the RUN?
                //if (result->GetRowCount() == 0) throw YSQLQueryException(oss.str().c_str());
                row = result->Next();
                if (row == NULL) continue;
                oss.str("");
                oss << "UPDATE GL_S4_CALIB "
                << " SET TO_TIME =  "   <<  dec << runTrailerRegistry->absTime
                << " WHERE ID = "       <<  row->GetField(1);
                //cout << oss.str().c_str() << "\n";
                result = conn->Query(oss.str().c_str());
        }

        
        // GL_TRK_CALIB
        
        oss.str("");
        oss << "SELECT ID FROM GL_TRK_CALIB "
        << " WHERE FROM_TIME <= " <<  dec << runHeaderRegistry->absTime  << " AND "
        << " TO_TIME >=         " <<  dec << runTrailerRegistry->absTime;
        //cout << oss.str().c_str() << "\n";
        result = conn->Query(oss.str().c_str());
        
        //The RUN is included in some calibration?
        if (result->GetRowCount() == 0) {
                oss.str("");
                oss << "SELECT MAX(TO_TIME) AS MX_TM_CALIB, ID AS ID_CALIB "
                << " FROM  GL_TRK_CALIB " 
                << " WHERE TO_TIME <= " <<  dec << runHeaderRegistry->absTime 
                << " GROUP BY ID ORDER BY MX_TM_CALIB DESC ";
                //cout << oss.str().c_str() << "\n";
                result = conn->Query(oss.str().c_str());
                //Exist a calibration with time less than the RUN?
                //if (result->GetRowCount() == 0) throw YSQLQueryException(oss.str().c_str());
                if (result->GetRowCount() == 0) continue;
                row = result->Next();
                oss.str("");
                oss << "UPDATE GL_TRK_CALIB "
                << " SET TO_TIME =  "   <<  dec << runTrailerRegistry->absTime
                << " WHERE ID = "       <<  row->GetField(1);
                //cout << oss.str().c_str() << "\n";
                result = conn->Query(oss.str().c_str());
        }
    }


    rootFile->Close();
    //delete result, row;
    oss.str("");
    oss   << " DROP TABLE IF EXISTS TM_REGISTRY ";
    // GOOD FOR MySQL ver > 3.2
    //oss   << " DROP TEMPORARY TABLE IF EXISTS TM_REGISTRY ";
    //if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    conn->Query(oss.str().c_str());
/*----------END - Fill the GL_RUN table collecting RunHeader and Physics items-----------*/
}

/**
* Populate the TM_CALIB_CALO_PED.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::populateTM_CALO_CALIB(TSQLServer *conn, const char *idRaw)
                      throw (YSQLNotConnectedException, YSQLQueryException, YEmptyObjectException) {
        if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream   oss;
        
    const char          *idRegistry;
    TSQLResult          *result;
    TSQLRow             *row            = 0;
    TFile               *file           = 0;
    TTree               *tr             = 0;
    TBranch             *calibCalPedEvBr = 0, *calibCalPedRegBr = 0;
    CalibCalPedEvent    *calibCalPed    = 0;
    RegistryEvent       *registry       = 0;
    Long64_t             nevents        = 0;

    /*Create the temporary table to collect the Calib_Cal_Ped items*/
    conn->Query(PamelaDBOperations::TM_CALO_CALIB);

    /*Get the Registry file relative to the CalibCalPed*/
    oss.str("");
    oss << "SELECT ID, PATH, NAME FROM GL_ROOT WHERE ID_RAW = '" << idRaw << "'";
    result = conn->Query(oss.str().c_str());
    //cout << oss.str().c_str() << "\n";
    
    if (result->GetRowCount() == 0) throw YSQLQueryException(oss.str().c_str());
    row     = result->Next();
    idRegistry = row->GetField(0);
    oss.str("");
    oss << row->GetField(1) << "/" << row->GetField(2);
    file    = YFile::Open(oss.str().c_str());
    if (file->IsZombie()) throw YNotExistingFileException("File not found");
    
    tr      = (TTree*)file->Get("CalibCalPed");
    if (tr->IsZombie()) throw YEmptyObjectException("CalibCalPed TTree not exist");
    
    if (!tr->GetBranchStatus("Registry")) throw YEmptyObjectException("Registry TBranch not existing for CalibCalPed TTree");
    calibCalPedRegBr = tr->GetBranch("Registry");
    tr->SetBranchAddress("Registry", &registry);

    if (!tr->GetBranchStatus("CalibCalPed")) throw YEmptyObjectException("CalibCalPed TBranch not existing for CalibCalPed TTree");
    calibCalPedEvBr = tr->GetBranch("CalibCalPed");
    tr->SetBranchAddress("CalibCalPed", &calibCalPed);

    nevents = calibCalPedRegBr->GetEntries();
    //cout << "nEvents: " << dec << nevents << "\n";
    for (Int_t i = 0; i < nevents; i++){
        /*----- DO NOT INVERT!!-----*/
        calibCalPedRegBr->GetEntry(i);
        calibCalPedEvBr->GetEntry(registry->event);
        //cout << "i: " << dec << i << "\n";
        //cout << "registry: " << dec << registry->event << "\n";
        /*----- DO NOT INVERT!!-----*/
        for (int j = 0; j < 4; j++){
            if (calibCalPed->cstwerr[j] == 0) continue;
            /* 
             * INSERT the CalibCalPed section into CL_CALIB_CAL_PED
             */
            oss.str("");
            oss << " INSERT INTO TM_CALO_CALIB (ID_REGISTRY, REG_EVENT, TIME, CAL_SECTION, CPERROR, CSTWERR) "
                << " VALUES ( " << idRegistry << ", "
                << dec << i << ", "
                << dec << registry->absTime << ", "
                << dec << j << ", "
                << dec << calibCalPed->cperror[j] << ", "
                << dec << calibCalPed->cstwerr[j] << ")" ;
            //cout << oss.str().c_str() << "\n";
            conn->Query(oss.str().c_str());
        }
    }
    file->Close();
    return OK;
}

/**
* Insert a new CALO items into the GL_CALO_XXX table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::insertNEW_CALO_CALIB(TSQLServer *conn, const char *rawPathName, const char *rawFileName)
                                      throw (YSQLNotConnectedException, YSQLQueryException){
  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  const char *id =  PamelaDBOperations::getID_RAW(conn, rawPathName, rawFileName);
  stringstream   oss;

  if (id == NULL)  throw YSQLQueryException(RAW_FILE_NOT_FOUND);

  /*
   * Verify that the CALO_CALIB have been not already processed
   */
  oss.str("");
  oss << " SELECT COUNT(GL_CALO_CALIB.ID) FROM GL_CALO_CALIB "
      << " LEFT JOIN GL_ROOT "
      << " ON GL_ROOT.ID = GL_CALO_CALIB.ID_REG_CALIBCALPED "
      << " WHERE GL_ROOT.ID_RAW = " << id;
  TSQLResult *result = conn->Query(oss.str().c_str());
  if (result == NULL) throw YSQLQueryException(oss.str().c_str());
  TSQLRow *row = result->Next();
  if ((row != NULL) && (atol(row->GetField(0)) > 0)) throw YSQLQueryException("CaloPed calibrations already inserted in the database");

   try {
        insertNEW_CALO_CALIB(conn, id);
   }   catch (YSQLQueryException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    } catch (YSQLNotConnectedException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    }
  return OK;
}

/**
* Insert a new CALO items into the GL_CALO_XXX table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::insertNEW_CALO_CALIB(TSQLServer *conn, const char *idRaw)
                                      throw (YSQLNotConnectedException, YSQLQueryException){
    
    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream     oss;
    TSQLResult      *result = 0;
    TSQLRow         *row = 0;
    
/*---- Create/Populate the TM_CALOCALIB table ----*/
    try {
      populateTM_CALO_CALIB(conn, idRaw);
    } catch (YEmptyObjectException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    }

/*---- BEGIN - Fill/update the GL_CALO_CALIB table looping on GL_RUN matching with TM_CALIB_CAL_PED ----*/
    oss.str("");
    oss << "SELECT TIME as TM_TIME, CAL_SECTION AS SECTION, REG_EVENT AS TM_EV, ID_REGISTRY AS TM_ID_REG, "
        << " ID AS TM_ID "
        << " FROM TM_CALO_CALIB "
        << " WHERE CPERROR = 0 "
        << " ORDER BY TIME ";

    //cout << oss.str().c_str() << "\n"; 
    result = conn->Query(oss.str().c_str());
    ULong64_t     time          = 0;
    int           section       = 0;
    unsigned long reg_ev        = 0;
    unsigned long id_reg        = 0;
    unsigned long tm_id         = 0;
    while (true){
        row       = result->Next();
        if (row == 0) break;
        time     = (ULong64_t)atof(row->GetField(0)) ;
        section  = (int)atoi(row->GetField(1)) ;
        reg_ev   = (unsigned long)atol(row->GetField(2)) ;
        id_reg   = (unsigned long)atol(row->GetField(3)) ;
        tm_id    = (unsigned long)atol(row->GetField(4)) ;
        try{
          insertNEW_CALO_CALIB(conn, time, section, reg_ev, id_reg, tm_id);
        } catch (YEmptyObjectException exc) {
          logger->error(exc.print());
        } catch (YProcessErrorException exc) {
          logger->error(exc.print());
        }
    }
    oss.str("");
    oss   << " DROP TABLE IF EXISTS TM_CALO_CALIB ";
    // GOOD FOR MySQL ver > 3.2
    //oss   << " DROP TEMPORARY TABLE IF EXISTS TM_CALO_CALIB ";
    //if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    conn->Query(oss.str().c_str());
/*---- END - Fill/update the GL_CALO_CALIB table looping on GL_RUN matching with TM_CALO_CALIB ----*/
}

/**
* Insert CALOCALIB items into the GL_CALO_CALIB table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param time       The time of the new calorimeter calibration.
* @param section    The section of this calibration.
* @param reg_ev     The registry entry number of the calibration.
* @param id_reg     The registry id number of the calibration.
* @param tm_id      The id of the TM_CALOCALIB.
*/
int PamelaDBOperations::insertNEW_CALO_CALIB(TSQLServer *conn, ULong64_t time, int section, unsigned long reg_ev, unsigned long id_reg, unsigned long tm_id)
                      throw (YSQLNotConnectedException, YSQLQueryException, YEmptyObjectException, YProcessErrorException) {
        
        if (conn == 0) throw YSQLNotConnectedException("Connection missing");
        if ((id_reg == 0) || (tm_id == 0)) throw YSQLNotConnectedException("Not valid IDs");

        TSQLResult      *result         = 0;
        TSQLRow         *row            = 0;
        ULong64_t        max_time       = 0;
        unsigned long    id_item        = 0;
        bool             processed      = false;
        stringstream   oss;


        /* Check if any GL_CALO_CALIB interval contain the new calibration for the specific section
           It is supposed that NO MORE than one interval could contain the new calibration; if this 
           happens check the assignement procedure.
        */
        oss.str("");
        oss << " SELECT ID, TO_TIME "
            << " FROM GL_CALO_CALIB "
            << " WHERE (   " << dec << time << " >= FROM_TIME AND "
            << dec << time << " <= TO_TIME ) AND "
            << " SECTION = " << dec << section;
        //cout << oss.str().c_str() << "\n";
        result = conn->Query(oss.str().c_str());
        if (result->GetRowCount() != 0) {
                /*
                  The new calibration time is contained in a previous CALO_CALIB item
                */
                row       = result->Next();
                
                if (time == (ULong64_t)atof(row->GetField(1))) return 0;
                /* 
                  Resize the existing CALO_CALIB item
                */
                oss.str("");
                oss << " UPDATE GL_CALO_CALIB "
                    << " SET GL_CALO_CALIB.TO_TIME = "  << dec << time 
                    << " WHERE GL_CALO_CALIB.ID = " << row->GetField(0);
                //cout << oss.str().c_str() << "\n";
                conn->Query(oss.str().c_str());

                //insert a new CALO_CALIB item
                oss.str("");
                oss << " INSERT INTO GL_CALO_CALIB (EV_REG_CALIBCALPED, ID_REG_CALIBCALPED, "
                    << " FROM_TIME, TO_TIME, SECTION) "
                    << " VALUES ( " 
                    << dec << reg_ev    << ", "
                    << dec << id_reg    << ", " 
                    << dec << time      << ", " 
                    << row->GetField(1) << ", " 
                    << dec << section   << ") ";
                //cout << oss.str().c_str() << "\n";            
                conn->Query(oss.str().c_str());
                processed = true;
        } else {
                if (processed) throw YSQLNotConnectedException("TM_CALIB processed twice");
                /* 
                   The calibration is NOT contained in any previous time interval
                */
                oss.str("");
                //THE FROM_TIME > time is not really needed.....
                oss << " SELECT ID, MAX(RUNTRAILER_TIME), RUNHEADER_TIME "
                << " FROM GL_RUN "
                << " WHERE (RUNHEADER_TIME > " << dec << time << ") "
                << " GROUP BY ID "
                << " ORDER BY RUNTRAILER_TIME DESC";
                //cout << oss.str().c_str() << "\n";
                result = conn->Query(oss.str().c_str());
                if (result->GetRowCount() != 0) {
                        row       = result->Next();
                        /* 
                          The new calibration time is LESS than any new RUNs 
                          (if the RUNs where old they should already belong to some calibration interval).

                          Note that this insert will span through all the time interval of the new RUNs,
                          so in the next loop new calibrations will not fall into this ELSE but in the previous IF!!!! This splution should guarantee that after this procedure every RUN should fall
                          into the calbration previous in time.
                        */
                        oss.str("");
                        oss << " INSERT INTO GL_CALO_CALIB (EV_REG_CALIBCALPED, ID_REG_CALIBCALPED, "
                            << " FROM_TIME, TO_TIME, SECTION) "
                            << " VALUES ( " 
                            << dec << reg_ev    << ", "
                            << dec << id_reg    << ", " 
                            << dec << time      << ", " 
                            << row->GetField(1) << ", " 
                            << dec << section   << ")";
                        //cout << oss.str().c_str() << "\n";
                        conn->Query(oss.str().c_str());
                        
                        /*
                          Delete the inserted TM_CALIBCALO
                        */
                        oss.str("");
                        oss << " DELETE FROM TM_CALO_CALIB "
                            << " WHERE ID = " << dec << tm_id;
                        //cout << oss.str().c_str() << "\n";
                        conn->Query(oss.str().c_str());
                        processed = true;
                } else {
                        /* 
                          The new calibration time is GREATER than the first one of the new RUNs 
                          (if the RUNs where old they should already belong to some calibration interval).

                          Note that this insert will span through all the time interval of the new RUNs,
                          so in the next loop new calibrations will not fall into this ELSE but in the previous IF!!!! This splution should guarantee that after this procedure every RUN should fall
                          into the calbration previous in time.
                        */

                        /*
                          Select the max time included in the calibration the already existing GL_CALO_CALIB
                        */
                        oss.str("");
                        oss << " SELECT ID AS ID_CALIB, MAX(TO_TIME) "
                        << " FROM GL_CALO_CALIB "
                        << " WHERE SECTION = " << dec << section
                        << " GROUP BY ID "
                        << "ORDER BY TO_TIME DESC ";
                        //cout << oss.str().c_str() << "\n";
                        result = conn->Query(oss.str().c_str());
                        if (result->GetRowCount() != 0) {
                                row             = result->Next();
                                id_item         = (unsigned long)atol(row->GetField(0)) ;
                                max_time        = (ULong64_t)atof(row->GetField(1)) ;
                                /* 
                                  Resize the existing CALO_CALIB item
                                */
                                oss.str("");
                                oss << " UPDATE GL_CALO_CALIB "
                                    << " SET GL_CALO_CALIB.TO_TIME = " << dec << max_time 
                                    << " WHERE GL_CALO_CALIB.ID = " << dec << id_item;
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());

                                oss.str("");
                                oss << " INSERT INTO GL_CALO_CALIB (EV_REG_CALIBCALPED, ID_REG_CALIBCALPED, "
                                << " FROM_TIME, TO_TIME, SECTION) "
                                << " VALUES ( " 
                                << dec << reg_ev        << ", "
                                << dec << id_reg        << ", " 
                                << dec << max_time      << ", " 
                                << dec << time          << ", " 
                                << dec << section       << ") ";
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());
                                
                                /*
                                  Delete the inserted TM_CALIBCALO
                                */
                                oss.str("");
                                oss << " DELETE FROM TM_CALO_CALIB "
                                << " WHERE ID = " << dec << tm_id;
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());
                        }
                        processed = true;
                }
        }
        if (!processed) throw YSQLNotConnectedException("TM_CALIB not processed");
}


/**
* Populate the TM_S4_CALIB.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/

int PamelaDBOperations::populateTM_S4_CALIB(TSQLServer *conn, const char *idRaw)
                      throw (YSQLNotConnectedException, YSQLQueryException, YEmptyObjectException) {
        if (conn == 0) throw YSQLNotConnectedException("Connection missing");
        stringstream   oss;
        
        const char          *idRegistry;
        TSQLResult          *result;
        TSQLRow             *row        = 0;
        TFile               *rootFile   = 0;
        TTree               *tr         = 0;
        TBranch             *calibS4RegBr = 0;
        TBranch             *calibS4EvBr  = 0;
        CalibS4Event        *calibS4    = new  CalibS4Event();
        RegistryEvent       *registry   = 0;
        Long64_t             nevents    = 0;
        double               param_fit  = 0;
        
        
        // Create the temporary table to collect the CalibS4 items
        conn->Query(PamelaDBOperations::TM_S4_CALIB);
        
        //Get the Registry file relative to the CalibS4
        oss.str("");
        oss << "SELECT ID, PATH, NAME FROM GL_ROOT WHERE ID_RAW = '" << idRaw << "'";
        result = conn->Query(oss.str().c_str());
        //cout << oss.str().c_str() << "\n";
        row     = result->Next();
        if (row == 0) throw YSQLQueryException(oss.str().c_str());
        
        idRegistry = row->GetField(0);
        oss.str("");
        oss << row->GetField(1) << "/" << row->GetField(2);
        rootFile    = YFile::Open(oss.str().c_str());
        
        
        if (rootFile->IsZombie()) throw YNotExistingFileException("File not found");
        tr      = (TTree*)rootFile->Get("CalibS4");
        if (tr->IsZombie()) throw YEmptyObjectException("Calibs4 TTree not exist");

        if (!tr->GetBranchStatus("Registry")) throw YEmptyObjectException("Registry TBranch not existing for CalibS4 TTree");
        calibS4RegBr = tr->GetBranch("Registry");
        tr->SetBranchAddress("Registry", &registry);

        calibS4EvBr = tr->GetBranch("CalibS4");
        tr->SetBranchAddress("CalibS4", &calibS4);

        nevents = calibS4RegBr->GetEntries();
        //cout << "nEvents in CalibS4Registry: " << dec << nevents << "\n";
        for (Int_t i = 0; i < nevents; i++){
                /*----- DO NOT INVERT!!-----*/
                calibS4RegBr->GetEntry(i);
                calibS4EvBr->GetEntry(registry->event);
                TArrayD params = S4_paramfit(calibS4);
                //cout << "Param0:" << dec << params.At(0);
                //cout << "Param1:" << dec << params.At(0);
                oss.str("");
                oss << " INSERT INTO TM_S4_CALIB (ID_REGISTRY, REG_EVENT, TIME, PARAM_FIT0, PARAM_FIT1) "
                        << " VALUES ( " << idRegistry << ", "
                        << dec << i << ", "
                        << dec << registry->absTime << ", "
                        << dec << params.At(0) << ", "
                        << dec << params.At(1) << ")" ;
                //cout << oss.str().c_str() << "\n";
                conn->Query(oss.str().c_str());
        }
        return OK;
}

/**
* Collect the CALIB_S4 from each section of each RUN.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param rawPathName    The path to the root file.
* @param rawFileName    The name of the root file.
*/
int PamelaDBOperations::insertNEW_S4_CALIB(TSQLServer *conn, const char *rawPathName, const char *rawFileName) 
  throw (YSQLNotConnectedException, YSQLQueryException){
  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  const char *id =  PamelaDBOperations::getID_RAW(conn, rawPathName, rawFileName);
  stringstream   oss;

  if (id == NULL)  throw YSQLQueryException(RAW_FILE_NOT_FOUND);

  /*
   * Verify that the S4_CALIB have been not already processed
   */
  oss.str("");
  oss << " SELECT COUNT(GL_S4_CALIB.ID) FROM GL_S4_CALIB "
      << " LEFT JOIN GL_ROOT "
      << " ON GL_ROOT.ID = GL_S4_CALIB.ID_REG_CALIBS4 "
      << " WHERE GL_ROOT.ID_RAW = " << id;
  TSQLResult *result = conn->Query(oss.str().c_str());
  if (result == NULL) throw YSQLQueryException(oss.str().c_str());
  TSQLRow *row = result->Next();
  if ((row != NULL) && (atol(row->GetField(0)) > 0)) throw YSQLQueryException("S4 calibrations already inserted in the database");

   try {
       return insertNEW_S4_CALIB(conn, id);
   }   catch (YSQLQueryException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    } catch (YSQLNotConnectedException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    } 
  return OK;
}

/**
* Insert a new S4 items into the GL_S4_CALIB table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::insertNEW_S4_CALIB(TSQLServer *conn, const char *idRaw)
                                      throw (YSQLNotConnectedException, YSQLQueryException){

    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream     oss;
    TSQLResult      *result = 0;
    TSQLRow         *row = 0;

/*---- Create/Populate the TM_S4CALIB table ----*/
        try {
                populateTM_S4_CALIB(conn, idRaw);
        } catch (YEmptyObjectException exc) {
                cout << "Eccezione! " << exc.print() << "\n";
                return NOK;
        }
    

/*---- BEGIN - Fill/update the GL_S4_CALIB table looping on GL_RUN matching with TM_CALIB_CAL_PED ----*/
    oss.str("");
    oss << "SELECT TIME, PARAM_FIT0, PARAM_FIT1, REG_EVENT, ID_REGISTRY, ID "
        << " FROM TM_S4_CALIB "
        << " ORDER BY TIME ";

    //cout << oss.str().c_str() << "\n"; 
    result = conn->Query(oss.str().c_str());
    ULong64_t     time          = 0;
    float         param_fit0    = 0;
    float         param_fit1    = 0;
    unsigned long reg_ev        = 0;
    unsigned long id_reg        = 0;
    unsigned long tm_id         = 0;
    while (true){
        row       = result->Next();
        if (row == 0) break;
        time       = (ULong64_t)atof(row->GetField(0)) ;
        param_fit0  = (float)atof(row->GetField(1)) ;
        param_fit1  = (float)atof(row->GetField(2)) ;
        reg_ev     = (unsigned long)atol(row->GetField(3)) ;
        id_reg     = (unsigned long)atol(row->GetField(4)) ;
        tm_id      = (unsigned long)atol(row->GetField(5)) ;
        try{
          insertNEW_S4_CALIB(conn, param_fit0, param_fit1, time, reg_ev, id_reg, tm_id);
        } catch (YEmptyObjectException exc) {
          logger->error(exc.print());
        } catch (YProcessErrorException exc) {
          logger->error(exc.print());
        }
    }
    oss.str("");
    oss   << " DROP TABLE IF EXISTS TM_S4_CALIB ";
    // GOOD FOR MySQL ver > 3.2
    //oss   << " DROP TEMPORARY TABLE IF EXISTS TM_S4_CALIB ";
    //if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    conn->Query(oss.str().c_str());
/*---- END - Fill/update the GL_CALO_CALIB table looping on GL_RUN matching with TM_CALIB_CAL_PED ----*/
}


/**
* Insert S4CALIB items into the GL_S4_CALIB table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param time       The time of the new S4 calibration.
* @param section    The section of this calibration.
* @param reg_ev     The registry entry number of the calibration.
* @param id_reg     The registry id number of the calibration.
* @param tm_id      The id of the TM_S4CALIB.
*/
int PamelaDBOperations::insertNEW_S4_CALIB(TSQLServer *conn, float paramfit0, float paramfit1, ULong64_t time, unsigned long reg_ev, unsigned long id_reg, unsigned long tm_id)
                      throw (YSQLNotConnectedException, YSQLQueryException, YEmptyObjectException, YProcessErrorException) {
        
        if (conn == 0) throw YSQLNotConnectedException("Connection missing");
        if ((id_reg == 0) || (tm_id == 0)) throw YSQLNotConnectedException("Not valid IDs");

        TSQLResult      *result         = 0;
        TSQLRow         *row            = 0;
        ULong64_t        max_time       = 0;
        unsigned long    id_item        = 0;
        bool             processed      = false;
        stringstream   oss;


        /* Check if any GL_S4_CALIB interval contain the new calibration for the specific section
           It is supposed that NO MORE than one interval could contain the new calibration; if this 
           happens check the assignement procedure.
        */
        oss.str("");
        oss << " SELECT ID, TO_TIME "
            << " FROM GL_S4_CALIB "
            << " WHERE (   " << dec << time << " >= FROM_TIME AND "
            << dec << time << " <= TO_TIME ) ";
        //cout << oss.str().c_str() << "\n";
        result = conn->Query(oss.str().c_str());

        row       = result->Next();
        if (row != 0) {
                if (time == (ULong64_t)atof(row->GetField(1))) return 0;
                /* 
                  Resize the existing S4_CALIB item
                */
                oss.str("");
                oss << " UPDATE GL_S4_CALIB "
                    << " SET GL_S4_CALIB.TO_TIME = "  << dec << time 
                    << " WHERE GL_S4_CALIB.ID = " << row->GetField(0);
                //cout << oss.str().c_str() << "\n";
                conn->Query(oss.str().c_str());

                //insert a new S4_CALIB item
                oss.str("");
                oss << " INSERT INTO GL_S4_CALIB (EV_REG_CALIBS4, ID_REG_CALIBS4, "
                    << " FROM_TIME, TO_TIME, PARAM_FIT0, PARAM_FIT1) "
                    << " VALUES ( " 
                    << dec << reg_ev      << ", "
                    << dec << id_reg      << ", " 
                    << dec << time        << ", " 
                    << row->GetField(1)   << ", " 
                    << dec << paramfit0   << ", " 
                    << dec << paramfit1   << ") ";
                //cout << oss.str().c_str() << "\n";            
                conn->Query(oss.str().c_str());
                processed = true;
        } else {
                if (processed) throw YSQLNotConnectedException("TM_CALIB processed twice");
                /* 
                   The calibration is NOT contained in any previous time interval
                */
                oss.str("");
                //THE FROM_TIME > time is not really needed.....
                oss << " SELECT ID, MAX(RUNTRAILER_TIME), RUNHEADER_TIME "
                << " FROM GL_RUN "
                << " WHERE (RUNHEADER_TIME > " << dec << time << ") "
                << " GROUP BY ID "
                << " ORDER BY RUNTRAILER_TIME DESC";
                //cout << oss.str().c_str() << "\n";
                result = conn->Query(oss.str().c_str());
                //if (result->GetRowCount() != 0) {
                row       = result->Next();
                if (row != NULL) {
                        /* 
                          The new calibration time is LESS than any new RUNs 
                          (if the RUNs where old they should already belong to some calibration interval).

                          Note that this insert will span through all the time interval of the new RUNs,
                          so in the next loop new calibrations will not fall into this ELSE but in the previous IF!!!! This splution should guarantee that after this procedure every RUN should fall
                          into the calbration previous in time.
                        */
                        oss.str("");
                        oss << " INSERT INTO GL_S4_CALIB (EV_REG_CALIBS4, ID_REG_CALIBS4, "
                            << " FROM_TIME, TO_TIME, PARAM_FIT0, PARAM_FIT1) "
                            << " VALUES ( " 
                            << dec << reg_ev      << ", "
                            << dec << id_reg      << ", " 
                            << dec << time        << ", " 
                            << row->GetField(1)   << ", " 
                            << dec << paramfit0   << ", " 
                            << dec << paramfit1   << ")";
                        //cout << oss.str().c_str() << "\n";
                        conn->Query(oss.str().c_str());
                        
                        /*
                          Delete the inserted TM_CALIBCALO
                        */
                        oss.str("");
                        oss << " DELETE FROM TM_S4_CALIB "
                            << " WHERE ID = " << dec << tm_id;
                        //cout << oss.str().c_str() << "\n";
                        conn->Query(oss.str().c_str());
                        processed = true;
                } else {
                        /* 
                          The new calibration time is GREATER than the first one of the new RUNs 
                          (if the RUNs where old they should already belong to some calibration interval).

                          Note that this insert will span through all the time interval of the new RUNs,
                          so in the next loop new calibrations will not fall into this ELSE but in the previous IF!!!! This splution should guarantee that after this procedure every RUN should fall
                          into the calbration previous in time.
                        */

                        /*
                          Select the max time included in the calibration the already existing GL_S4_CALIB
                        */
                        oss.str("");
                        oss << " SELECT ID AS ID_CALIB, MAX(TO_TIME) "
                            << " FROM GL_S4_CALIB "
                            << " GROUP BY ID "
                            << " ORDER BY TO_TIME DESC";
                        //cout << oss.str().c_str() << "\n";
                        result = conn->Query(oss.str().c_str());
                        //if (result->GetRowCount() != 0) {
                        if (row != NULL) {
                                row             = result->Next();
                                id_item         = (unsigned long)atol(row->GetField(0)) ;
                                max_time        = (ULong64_t)atof(row->GetField(1)) ;
                                /* 
                                  Resize the existing CALO_CALIB item
                                */
                                oss.str("");
                                oss << " UPDATE GL_S4_CALIB "
                                    << " SET GL_S4_CALIB.TO_TIME = " << dec << max_time 
                                    << " WHERE GL_S4_CALIB.ID = " << dec << id_item;
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());

                                oss.str("");
                                oss << " INSERT INTO GL_S4_CALIB (EV_REG_CALIBS4, ID_REG_CALIBS4, "
                                    << " FROM_TIME, TO_TIME, PARAM_FIT0, PARAM_FIT1) "
                                    << " VALUES ( " 
                                    << dec << reg_ev    << ", "
                                    << dec << id_reg    << ", " 
                                    << dec << max_time  << ", " 
                                    << dec << time      << ", " 
                                    << dec << paramfit0 << ", " 
                                    << dec << paramfit1 << ") ";
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());
                                
                                /*
                                  Delete the inserted TM_CALIBS4
                                */
                                oss.str("");
                                oss << " DELETE FROM TM_S4_CALIB "
                                    << " WHERE ID = " << dec << tm_id;
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());
                        }
                        processed = true;
                }
        }
        if (!processed) throw YSQLNotConnectedException("TM_CALIB not processed");
}


//******************************************************************************
/**
* Collect the CALIB_TRK from each section of each RUN.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param rawPathName    The path to the root file.
* @param rawFileName    The name of the root file.
*/
int PamelaDBOperations::insertNEW_TRK_CALIB(TSQLServer *conn, const char *rawPathName, const char *rawFileName) 
  throw (YSQLNotConnectedException, YSQLQueryException){
  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  const char *id =  PamelaDBOperations::getID_RAW(conn, rawPathName, rawFileName);
  stringstream   oss;

  if (id == NULL)  throw YSQLQueryException(RAW_FILE_NOT_FOUND);

  /*
   * Verify that the TRK_CALIB have been not already processed
   */
  oss.str("");
  oss << " SELECT COUNT(GL_TRK_CALIB.ID) FROM GL_TRK_CALIB "
      << " LEFT JOIN GL_ROOT "
      << " ON GL_ROOT.ID = GL_TRK_CALIB.ID_REG_CALIBTRK "
      << " WHERE GL_ROOT.ID_RAW = " << id;
  TSQLResult *result = conn->Query(oss.str().c_str());
  if (result == NULL) throw YSQLQueryException(oss.str().c_str());
  TSQLRow *row = result->Next();
  if ((row != NULL) && (atol(row->GetField(0)) > 0)) throw YSQLQueryException("TRKx calibrations already inserted in the database");

   try {
        insertNEW_TRK_CALIB(conn, id);
   }  catch (YSQLQueryException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    } catch (YSQLNotConnectedException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    }
  return OK;
}


/**
* Insert a new TRK items into the GL_TRK_CALIB table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::insertNEW_TRK_CALIB(TSQLServer *conn, const char *idRaw)
                                      throw (YSQLNotConnectedException, YSQLQueryException){

    if (conn == 0) throw YSQLNotConnectedException("Connection missing");
    stringstream     oss;
    TSQLResult      *res    = 0;
    TSQLResult      *res2   = 0;
    TSQLRow         *row    = 0;
    TSQLRow         *row2   = 0;

/*---- Create/Populate the TM_TRKCALIB table ----*/
    try {
        populateTM_TRK_CALIB(conn, idRaw);
    } catch (YEmptyObjectException exc) {
      cout << "Eccezione! " << exc.print() << "\n";
      return NOK;
    }

/*---- BEGIN - Fill/update the GL_TRK_CALIB table looping on GL_RUN matching with TM_CALIB_TRK ----*/
/* According to how the CPU execute the calibration sequence the CalibTrk1 and the CalibTrk2 are one 
 * after the other. Beacuse of this in the second query we search for the CalibTrk2 which has a 
 * (packet number + 1) 
*/
    ULong64_t     time          = 0;
    unsigned long reg_ev1       = 0;
    unsigned long reg_ev2       = 0;
    unsigned long id_reg        = 0;
    const char*   tm_id1        = 0;
    const char*   tm_id2        = 0;

    oss.str("");
    oss << "SELECT TIME, REG_EVENT, ID_REGISTRY, PCK_COUNTER, ID"
        << " FROM TM_TRK_CALIB WHERE TRK_PART = 1"
        << " ORDER BY TIME ";
        
    //cout << oss.str().c_str() << "\n"; 
    res = conn->Query(oss.str().c_str());
    row = res->Next();
    while (!(row == 0)){
        id_reg     = (unsigned long)atol(row->GetField(2)) ;
        reg_ev1    = (unsigned long)atol(row->GetField(1)) ;
        time       = (ULong64_t)atof(row->GetField(0)) ;
        tm_id1     = row->GetField(4);
        tm_id2     = row->GetField(1);
        oss.str("");
        oss << "SELECT REG_EVENT, ID"
                << " FROM TM_TRK_CALIB WHERE TRK_PART = 2 "
                << " AND ID_REGISTRY = " << row->GetField(2)
                << " AND PCK_COUNTER = " << dec << (atol(row->GetField(3)) + 1)
                << " ORDER BY TIME ";
        //cout << oss.str().c_str() << "\n"; 
        res2 = conn->Query(oss.str().c_str());
        row2 = res2->Next();
        if (!(row2 == 0)){
                reg_ev2    = (unsigned long)atol(row2->GetField(0)) ;
                try{
                        insertNEW_TRK_CALIB(conn, id_reg, reg_ev1, reg_ev2, time, tm_id1, tm_id2);
                } catch (YEmptyObjectException exc) {
                        logger->error(exc.print());
                } catch (YProcessErrorException exc) {
                        logger->error(exc.print());
                }
        } else {
                cout << "The corresponding CalibTrk2 have not been found!!" << "\n"; 
        }
        row       = res->Next();
    }

    oss.str("");
    oss   << " DROP TABLE IF EXISTS TM_TRK_CALIB ";
    // GOOD FOR MySQL ver > 3.2
    //oss   << " DROP TEMPORARY TABLE IF EXISTS TM_S4_CALIB ";
    //if (conn->Query(oss.str().c_str()) == 0) throw YSQLQueryException(oss.str().c_str());
    conn->Query(oss.str().c_str());
/*---- END - Fill/update the GL_CALO_CALIB table looping on GL_RUN matching with TM_CALIB_CAL_PED ----*/
}


/**
* Insert TRKCALIB items into the GL_TRK_CALIB table.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param id_reg     The registry id number of the calibration.
* @param reg_ev1    The registry entry number for the Trk1 calibration.
* @param reg_ev2    The registry entry number for the Trk2 calibration.
* @param time       The time of the calibration.
*/
int PamelaDBOperations::insertNEW_TRK_CALIB(TSQLServer *conn, unsigned long id_reg, unsigned long reg_ev1, unsigned long reg_ev2, ULong64_t time, const char* tm_id1, const char* tm_id2)
                      throw (YSQLNotConnectedException, YSQLQueryException, YEmptyObjectException, YProcessErrorException) {
        
        if (conn == 0) throw YSQLNotConnectedException("Connection missing");
        //if ((id_reg == 0) || (tm_id == 0)) throw YSQLNotConnectedException("Not valid IDs");

        TSQLResult      *result         = 0;
        TSQLRow         *row            = 0;
        ULong64_t        max_time       = 0;
        unsigned long    id_item        = 0;
        bool             processed      = false;
        stringstream   oss;


        /* Check if any GL_TRK_CALIB interval contain the new calibration for the specific section
           It is supposed that NO MORE than one interval could contain the new calibration; if this 
           happens check the assignement procedure.
        */
        oss.str("");
        oss << " SELECT ID, TO_TIME "
            << " FROM GL_TRK_CALIB "
            << " WHERE (   " << dec << time << " >= FROM_TIME AND "
            << dec << time << " <= TO_TIME )";
        //cout << oss.str().c_str() << "\n";
        result = conn->Query(oss.str().c_str());
        if (result->GetRowCount() != 0) {
                /*
                  The new calibration time is contained in a previous GL_TRK_CALIB item
                */
                row       = result->Next();
                
                if (time == (ULong64_t)atof(row->GetField(1))) return 0;
                /* 
                  Resize the existing TRK_CALIB item
                */
                oss.str("");
                oss << " UPDATE GL_TRK_CALIB "
                    << " SET GL_TRK_CALIB.TO_TIME = "  << dec << time 
                    << " WHERE GL_TRK_CALIB.ID = " << row->GetField(0);
                //cout << oss.str().c_str() << "\n";
                conn->Query(oss.str().c_str());

                //insert a new TRK_CALIB item
                oss.str("");
                oss << " INSERT INTO GL_TRK_CALIB (ID_REG_CALIBTRK, EV_REG_CALIBTRK1, EV_REG_CALIBTRK2, "
                    << " FROM_TIME, TO_TIME) "
                    << " VALUES ( " 
                    << dec << id_reg      << ", " 
                    << dec << reg_ev1     << ", "
                    << dec << reg_ev2     << ", "
                    << dec << time        << ", " 
                    << row->GetField(1)   << ") ";
                //cout << oss.str().c_str() << "\n";            
                conn->Query(oss.str().c_str());
                processed = true;
        } else {
                if (processed) throw YSQLNotConnectedException("TM_CALIB processed twice");
                /* 
                   The calibration is NOT contained in any previous time interval
                */
                oss.str("");
                //THE FROM_TIME > time is not really needed.....
                oss << " SELECT ID, MAX(RUNTRAILER_TIME), RUNHEADER_TIME "
                << " FROM GL_RUN "
                << " WHERE (RUNHEADER_TIME > " << dec << time << ") " 
                << " GROUP BY ID "
                << " ORDER BY RUNTRAILER_TIME DESC";
                //cout << oss.str().c_str() << "\n";
                result = conn->Query(oss.str().c_str());
                if (result->GetRowCount() != 0) {
                        row       = result->Next();
                        /* 
                          The new calibration time is LESS than any new RUNs 
                          (if the RUNs where old they should already belong to some calibration interval).

                          Note that this insert will span through all the time interval of the new RUNs,
                          so in the next loop new calibrations will not fall into this ELSE but in the previous IF!!!! This splution should guarantee that after this procedure every RUN should fall
                          into the calbration previous in time.
                        */
                        oss.str("");
                        oss << " INSERT INTO GL_TRK_CALIB (ID_REG_CALIBTRK, EV_REG_CALIBTRK1, EV_REG_CALIBTRK2,  "
                            << " FROM_TIME, TO_TIME) "
                            << " VALUES ( " 
                            << dec << id_reg      << ", " 
                            << dec << reg_ev1     << ", "
                            << dec << reg_ev2     << ", "
                            << dec << time        << ", " 
                            << row->GetField(1)   << ")";
                        //cout << oss.str().c_str() << "\n";
                        conn->Query(oss.str().c_str());
                        
                        /*
                          Delete the inserted TM_CALIBTRK
                        */
                        oss.str("");
                        oss << " DELETE FROM TM_TRK_CALIB "
                            << " WHERE ID = " << tm_id1
                            << " OR ID = " << tm_id2;
                        //cout << oss.str().c_str() << "\n";
                        conn->Query(oss.str().c_str());
                        processed = true;
                } else {
                        /* 
                          The new calibration time is GREATER than the first one of the new RUNs 
                          (if the RUNs where old they should already belong to some calibration interval).

                          Note that this insert will span through all the time interval of the new RUNs,
                          so in the next loop new calibrations will not fall into this ELSE but in the previous IF!!!! This splution should guarantee that after this procedure every RUN should fall
                          into the calbration previous in time.
                        */

                        /*
                          Select the max time included in the calibration the already existing GL_TRK_CALIB
                        */
                        oss.str("");
                        oss << " SELECT ID AS ID_CALIB, MAX(TO_TIME) "
                        << " FROM GL_TRK_CALIB "
                        << " GROUP BY ID "
                        << " ORDER BY TO_TIME DESC";
                        //cout << oss.str().c_str() << "\n";
                        result = conn->Query(oss.str().c_str());
                        if (result->GetRowCount() != 0) {
                                row             = result->Next();
                                id_item         = (unsigned long)atol(row->GetField(0)) ;
                                max_time        = (ULong64_t)atof(row->GetField(1)) ;
                                /* 
                                  Resize the existing TRK_CALIB item
                                */
                                oss.str("");
                                oss << " UPDATE GL_TRK_CALIB "
                                    << " SET GL_TRK_CALIB.TO_TIME = " << dec << max_time 
                                    << " WHERE GL_TRK_CALIB.ID = " << dec << id_item;
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());

                                oss.str("");
                                oss << " INSERT INTO GL_TRK_CALIB ( ID_REG_CALIBTRK, EV_REG_CALIBTRK1, EV_REG_CALIBTRK2,"
                                << " FROM_TIME, TO_TIME) "
                                << " VALUES ( " 
                                << dec << id_reg        << ", " 
                                << dec << reg_ev1       << ", "
                                << dec << reg_ev2       << ", "
                                << dec << max_time      << ", " 
                                << dec << time          << ") ";
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());
                                
                                /*
                                  Delete the inserted TM_CALIBTRK
                                */
                                oss.str("");
                                oss << " DELETE FROM TM_TRK_CALIB "
                                    << " WHERE ID = " << tm_id1
                                    << " OR ID = " << tm_id2;
                                //cout << oss.str().c_str() << "\n";
                                conn->Query(oss.str().c_str());
                        }
                        processed = true;
                }
        }
        if (!processed) throw YSQLNotConnectedException("TM_CALIB not processed");
}

/**
* Populate the TM_TRK_CALIB.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn       An active connection to a suitable (for PAMELA) database.
* @param idRaw      The ID code of the raw file according to the GL_RAW table.
*/
int PamelaDBOperations::populateTM_TRK_CALIB(TSQLServer *conn, const char *idRaw)
                      throw (YSQLNotConnectedException, YSQLQueryException, YEmptyObjectException) {
        if (conn == 0) throw YSQLNotConnectedException("Connection missing");
        
        //It is "a bit" redundant but for sake of clarity......
        stringstream   oss;
        const char          *idRegistry;
        TSQLResult          *result;
        TSQLRow             *row             = 0;
        TFile               *rootFile        = 0;
        TTree               *calibTrk1Tr     = 0;
        TTree               *calibTrk2Tr     = 0;
        TBranch             *calibTrk1EvBr   = 0;
        TBranch             *calibTrk1HdBr   = 0;
        TBranch             *calibTrk1RegBr  = 0;
        TBranch             *calibTrk2EvBr   = 0;
        TBranch             *calibTrk2HdBr   = 0;
        TBranch             *calibTrk2RegBr  = 0;
        
        EventHeader         *calibTrk1EH     = 0;
        PscuHeader          *calibTrk1PH     = 0;
        EventHeader         *calibTrk2EH     = 0;
        PscuHeader          *calibTrk2PH     = 0;
        
        CalibTrk1Event      *calibTrk1  = new  CalibTrk1Event();
        CalibTrk2Event      *calibTrk2  = new  CalibTrk2Event();
        RegistryEvent       *trk1Registry   = 0;
        RegistryEvent       *trk2Registry   = 0;
        Long64_t             nevents    = 0;
        
        /*Create the temporary table to collect the CalibTRK items*/
        conn->Query(PamelaDBOperations::TM_TRK_CALIB);
        
        /*Get the Registry file relative to the CalibTRK1*/
        oss.str("");
        oss << "SELECT ID, PATH, NAME FROM GL_ROOT WHERE ID_RAW = '" << idRaw << "'";
        result = conn->Query(oss.str().c_str());
        //cout << oss.str().c_str() << "\n";
        row     = result->Next();
        if (row == 0) throw YSQLQueryException(oss.str().c_str());
        
        idRegistry = row->GetField(0);
        oss.str("");
        oss << row->GetField(1) << "/"  << row->GetField(2);
        rootFile    = YFile::Open(oss.str().c_str());
        if (rootFile->IsZombie()) throw YNotExistingFileException("File not found");
        
        //Retrieve Registry and Events for CalibTrk1
        calibTrk1Tr      = (TTree*)rootFile->Get("CalibTrk1");
        if (calibTrk1Tr->IsZombie()) throw YEmptyObjectException("CalibTrk1 TTree not exist");
        if (!calibTrk1Tr->GetBranchStatus("Registry")) throw YEmptyObjectException("Registry TBranch not existing for CalibTrk1 TTree");
        calibTrk1RegBr = calibTrk1Tr->GetBranch("Registry");
        calibTrk1Tr->SetBranchAddress("Registry", &trk1Registry);
        
        calibTrk1EvBr = calibTrk1Tr->GetBranch("CalibTrk1");
        calibTrk1Tr->SetBranchAddress("CalibTrk1", &calibTrk1);
        
        calibTrk1HdBr = calibTrk1Tr->GetBranch("Header");
        calibTrk1Tr->SetBranchAddress("Header", &calibTrk1EH);

        //Populate TM_TRK_CALIB for CalibTrk2
        nevents = calibTrk1RegBr->GetEntries();
        //cout << "nEvents: " << dec << nevents << "\n";
        for (Int_t i = 0; i < nevents; i++){
                /*----- DO NOT INVERT!!-----*/
                calibTrk1RegBr->GetEntry(i);
                calibTrk1EvBr->GetEntry(trk1Registry->event);
                calibTrk1HdBr->GetEntry(trk1Registry->event);
                calibTrk1PH = calibTrk1EH->GetPscuHeader();
                oss.str("");
                oss << " INSERT INTO TM_TRK_CALIB (ID_REGISTRY, REG_EVENT, TIME, TRK_PART, PCK_COUNTER) "
                << " VALUES ( " 
                << idRegistry                   << ", "
                << dec << i                     << ", "
                << dec << trk1Registry->absTime << ", "
                << dec << 1                     << ", "
                << dec << calibTrk1PH->Counter  << ")" ;
                //cout << oss.str().c_str()     << "\n";
                conn->Query(oss.str().c_str());
        }


        //Retrieve Registry and Events for CalibTrk2
        calibTrk2Tr      = (TTree*)rootFile->Get("CalibTrk2");
        if (calibTrk2Tr->IsZombie()) throw YEmptyObjectException("CalibTrk2 TTree not exist");
        if (!calibTrk2Tr->GetBranchStatus("Registry")) throw YEmptyObjectException("Registry TBranch not existing for CalibTrk2 TTree");
        calibTrk2RegBr = calibTrk2Tr->GetBranch("Registry");
        calibTrk2Tr->SetBranchAddress("Registry", &trk2Registry);
        
        calibTrk2EvBr = calibTrk2Tr->GetBranch("CalibTrk2");
        calibTrk2Tr->SetBranchAddress("CalibTrk2", &calibTrk2);
        
        calibTrk2HdBr = calibTrk2Tr->GetBranch("Header");
        calibTrk2Tr->SetBranchAddress("Header", &calibTrk2EH);

        //Populate TM_TRK_CALIB for CalibTrk2
        nevents = calibTrk2RegBr->GetEntries();
        //cout << "nEvents: " << dec << nevents << "\n";
        for (Int_t i = 0; i < nevents; i++){
                /*----- DO NOT INVERT!!-----*/
                calibTrk2RegBr->GetEntry(i);
                calibTrk2EvBr->GetEntry(trk2Registry->event);
                calibTrk2HdBr->GetEntry(trk2Registry->event);
                calibTrk2PH = calibTrk2EH->GetPscuHeader();
                oss.str("");
                oss << " INSERT INTO TM_TRK_CALIB (ID_REGISTRY, REG_EVENT, TIME, TRK_PART, PCK_COUNTER) "
                << " VALUES ( " 
                << idRegistry                   << ", "
                << dec << i                     << ", "
                << dec << trk2Registry->absTime << ", "
                << dec << 2                     << ", "
                << dec << calibTrk2PH->Counter  << ")" ;
                //cout << oss.str().c_str()     << "\n";
                conn->Query(oss.str().c_str());
        }
        
        return OK;
}


/**
* Retrieve the ID_RAW, if exists.
* Throw exception if (conn == 0) or the SQL command went wrong.
* @param conn           An active connection to a suitable (for PAMELA) database.
* @param rawPathName    The path to the root file.
* @param rawFileName    The name of the root file.
*/
const char* PamelaDBOperations::getID_RAW(TSQLServer *conn, const char *rawPathName, const char *rawFileName) 
  throw (YSQLNotConnectedException, YSQLQueryException){

  if (conn == 0) throw YSQLNotConnectedException("Connection missing");
  stringstream oss;
  TSQLResult *result = 0;
  TSQLRow    *row    = 0;
  oss.str("");
  oss << "SELECT ID FROM GL_RAW WHERE "
      << " PATH = '" << rawPathName << "' AND "
      << " NAME = '" << rawFileName << "' ";
  result = conn->Query(oss.str().c_str());
  if (result == NULL) throw YSQLQueryException(oss.str().c_str());
  row = result->Next();
  if (row == NULL) return NULL;
  return row->GetField(0);
}

const char* PamelaDBOperations::setGeo(cOrbit *orbit, RegistryEvent* &reg) 
                      throw (YSQLNotConnectedException, YSQLQueryException){
                cEci         eci;
                cCoordGeo    coo;
                TDatime time = TDatime((reg->absTime/1000));
                orbit->getPosition( (time.GetYear() + time.GetMonth()/12.), &eci);
                coo = eci.toGeo();
                reg->m_Lat = rad2deg(coo.m_Lat);
                reg->m_Lon = rad2deg(coo.m_Lon);
                reg->m_Alt = coo.m_Alt;
                cout << "coo.m_Lat " << dec << coo.m_Lat << "\n";
                cout << "rad2deg(coo.m_Lat) " << dec << rad2deg(coo.m_Lat) << "\n";
                float dimo = 0.0;
                float year = time.GetYear();
                float bnorth, beast, bdown, babs;
                float xl, icode, bab1;
                float stps = 0.05;
                float bdel = 0.001;
                bool value = 0;
                year = 2004;
                float bequ, rr0;
                feldcof_(&year, &dimo);
                feldg_(&reg->m_Lat, &reg->m_Lon, &reg->m_Alt, &bnorth, &beast, &bdown, &babs);
                shellg_(&reg->m_Lat, &reg->m_Lon, &reg->m_Alt, &dimo, &xl, &icode, &bab1);
                findb0_(&stps, &bdel, &value, &bequ, &rr0);
                reg->magnGeo = YMagnGeo();
                reg->magnGeo.setBNorth(bnorth);
                reg->magnGeo.setBEast(beast);
                reg->magnGeo.setBDown(bdown);
                reg->magnGeo.setBAbs(babs);
                reg->magnGeo.setL(xl);
                reg->magnGeo.setBvsB0(babs/bequ);
}