chewbacca/PamOffLineSW/PacketUser.cpp

//============================================================================
// $Id: PacketUser.cpp,v 1.16 2010/02/02 15:26:09 mocchiut Exp $ 
// Description : 
//============================================================================
#include "PacketUser.h"
#include <sys/time.h>
#include <iostream>

namespace PamOffLineSW {
extern LogUtil* mainLogUtil;
extern short compression;
extern char *outDir;
extern TSQLServer *sqlServer;
extern bool multiFile;
extern char* fni;
extern char * nome_output;
extern bool is_new_route;
extern unsigned int download;

extern unsigned int mmm_number;
extern unsigned int orbit_number;
extern unsigned long int time_Offset;
extern bool tryMerge;
extern bool candelete; // EM
//marco_new_01
extern bool single_connection;

//don't perform check about continuity: it means don't use DB.
extern bool do_cont_check;

//marco_new_31:
extern char* db_user;
extern char* db_pwd;
extern char* pelosconnection;

//per tenere conto reset obt
extern unsigned long int max_pkt_obt;

#define TAGVALUELEN 4
extern char tag_value[TAGVALUELEN];

PacketUser PacketUser::instance;

int PacketUser::numDiscontinity = 1;
int PacketUser::numPKT = 0;
int PacketUser::numPKTSaved = 0;

PacketUser& PacketUser::getInstance() {
  return instance;
}

PacketUser::PacketUser() {
  gROOT->SetBatch(kTRUE);
  numDiscontinity = 1;
  numPKTSaved = 0;
  numPKT = 0;
  pkt_number_init = 0;
  obt_init = 0;
  pkt_number_last = 0;
  obt_last = 0;
  obt_time_sync = 0;
  last_time_sync_info = 0;

  //(tassa)
  obt_time_sync_prevvalue = 0;
  last_time_sync_info_prevvalue = 0;
  time_is_estimated = false;

  //EM
  recoverlimit = 400;
  idtorecover = new TArrayI(100);
  arsize = 0;

  real_time_init = 0;
  real_time_last = 0;
  bad_pkt = 0;
  bad_pkt_EventReader = 0;
  bad_pkt_CalibReader = 0;
  reader = NULL;
  pRun = NULL;
  reader = new pamela::techmodel::EventReader();
  Table_ROOT_Good = "ROOT_TABLE";
  Table_ROOT_Bad = "ROOT_TABLE_BAD";
  Table_GL_RESURS_OFFSET = "GL_RESURS_OFFSET";
  Table_ROOT_Merging = "ROOT_TABLE_MERGING";
  //    good_pkt_Calib=0;
  my_id = 0;
  boot_number = 0;
  //(tassa)
  boot_number_prevvalue = 0;

  //(tassa)
  //        id_to_recover[1000]; // Emiliano: che si vuole fare con questa riga? cosi` non fa nulla...
  id_to_recover_index = 0;
}

PacketUser::~PacketUser() {
  if (reader) {
    delete reader;
    reader = NULL;
  }
  numDiscontinity = 1;
  numPKTSaved = 0;
  numPKT = 0;
  pkt_number_init = 0;
  obt_init = 0;
  pkt_number_last = 0;
  obt_last = 0;
  obt_time_sync = 0;
  last_time_sync_info = 0;
  obt_time_sync_prevvalue = 0;
  last_time_sync_info_prevvalue = 0;
  time_is_estimated = false;

  real_time_init = 0;
  real_time_last = 0;
  bad_pkt = 0;
  bad_pkt_EventReader = 0;
  bad_pkt_CalibReader = 0;
  //    good_pkt_Calib=0;
  my_id = 0;
  boot_number = 0;
  boot_number_prevvalue = 0;
}

//Put the packet in a root file. Create a new ROOT file for each group of packets
void PacketUser::usePKT(char*& headerPkt, char*& pamPkt, long int length, bool isCons, bool isPKTGood,
    const PacketType* type, unsigned long int counter, unsigned long int obt) {
  //If the packet type was not recognised before
  if (!type) {
    return;
  }

  //in order to start ...
  if (numPKT == 0) {
    if (single_connection) {
      OpenDBConnection(NULL);
    }

    setInit(counter, obt);
    StartGroup();
  }

  //here we know if the incoming packet can be considered consecutive to the previuos   one
  if ((!isCons) && (numPKT != 0)) {
    setReal_Time();
    //closing the group in file rootfilename
    FinishGroup(rootfilename);

    //se gia' e' cambiato download non cambio numdisco
    if (!is_new_route) {
      numDiscontinity++;
    }

    //the current pkt will be placed in a new group: rootfilename
    setInit(counter, obt);
    StartGroup();
  }

  //add current packet to the group and calculate bad_pkt_EventReader and bad_pkt_CalibReader
  int ret = reader->PKT_RunEvent(headerPkt, pamPkt, length, type);
  /*
   ret
   0    packet good
   1    exception but used    //never happen
   2    CRC exception but used
   3    CALIBRATION PACKET with error but used
   -1    CRC exception  packet DISCARDED
   -2    FATAL exception packet DISCARDED //never happen
   -3    No way to read events of this type. packet DISCARDED( es OLD CalibCal)
   ret = 10 if the packet is good but comes from a cadre with VRL problems
   */

  //here we also know if the packet comes from one or more corrupted cadres
  if ((!isPKTGood) && (!ret)) {
    ret = 10;
  }
  switch (ret) {
  case 0: {
    numPKTSaved++;
    break;
  }
  case 1: {
    bad_pkt_EventReader++;
    numPKTSaved++;
    break;
  }
  case 2: {
    bad_pkt_EventReader++;
    numPKTSaved++;
    break;
  }
  case 3: {
    bad_pkt_CalibReader++;
    numPKTSaved++;
    break;
  }
  case 10: {
    bad_pkt++;
    numPKTSaved++;
    break;
  }
  default: {
    stringstream oss;
    oss.str() = "";
    oss << "Packet DISCARDED, reason = " << ret;
    string msg = oss.str();
    mainLogUtil->logAll(msg);
    break;
  }
  }

  /*
   if ((ret==0)&&((type==PacketType::CalibTrk1)||(type==PacketType::CalibTrk2)|| (type==PacketType::CalibCalPed)))
   {
   good_pkt_Calib++;//maybe I will remove this in future
   }
   */

  if (ret >= 0)// only the packet not discarded of course
    setLast(counter, obt);

  //TODO: decide what kind of packet I can use here
  //    if((ret==0)||(ret==10))//I want to use only good packet
  if (ret >= 0)//I want to use only accepted packet
  {
    setTimeSync(pamPkt, length, type);//here I retrieve also the boot_number if packet type is vardump
  }

  //just to be sure ...
  if (pamPkt) {
    delete[] pamPkt;
    pamPkt = NULL;
  }
  if (headerPkt) {
    delete[] headerPkt;
    headerPkt = NULL;
  }

  numPKT++;
}

//set the value of the counter and obt of the first packet of the group 
void PacketUser::setInit(unsigned long int counter, unsigned long int obt) {
  sprintf(nnnn_mmm_ppp, "%05d_%03d_%03d", orbit_number, mmm_number, download);
  sprintf(rootfilename, "%s_%s_%d", nome_output, nnnn_mmm_ppp, numDiscontinity);
  pkt_number_init = counter;
  obt_init = obt;
  pkt_number_last = 0;
  obt_last = 0;
  real_time_init = 0;
  real_time_last = 0;
  bad_pkt = 0;
  numPKTSaved = 0;
  bad_pkt_EventReader = 0;
  bad_pkt_CalibReader = 0;
  //good_pkt_Calib=0;

  //marco_NB:
  //ogni volta che trovo una discontinuita` resetto come se mi trovassi in un nuovo download oppure uso i vecchi valori se esistono?

  if ( obt_time_sync ) obt_time_sync_prevvalue = obt_time_sync; // save the last NOT NULL obt_time_sync! EM
  if ( last_time_sync_info ) last_time_sync_info_prevvalue = last_time_sync_info; // save the last NOT NULL last_time_sync
  time_is_estimated = false;
  obt_time_sync = 0;
  last_time_sync_info = 0;
  boot_number_prevvalue = boot_number;
  boot_number = 0;

  //    NB: se ho discontinuita in genere non so a che download appartiene potrebbe proprio essere sbagliato tenere i vecchi valori

}

//starts a new root file
void PacketUser::StartGroup() {
  pRun = new PamelaRun(rootfilename, gSystem->ExpandPathName(outDir), multiFile, compression); // EMI
  strcat(rootfilename, ".root");
  reader->Init(pRun);
  stringstream oss;
  oss.str() = "";
  oss << "######################### Start a new group in file: " << rootfilename << " ############################";
  string msg = oss.str();
  mainLogUtil->logInfo(msg);
}

//set the value of the counter and obt of the last packet of the group 
void PacketUser::setLast(unsigned long int counter, unsigned long int obt) {
  pkt_number_last = counter;
  obt_last = obt;
}

//retrieve obt_time_sync and last_time_sync_info from packet if type has special values and BOOT_NUMBER
void PacketUser::setTimeSync(char* packet, long int pktLength, const PacketType* type) {
  //do nothing
  if (!do_cont_check) {
    if (is_new_route)
      is_new_route = false;
    return;
  }

  //TODO: usare anche MCMD ... NOTA: questo da sempre errore ret != 0
  //In RunHeader e RunTrailer sono in secondi(?), controllare se aggiungo altri tipi di paccheti se invece sono in secondi
  // devo ricalcolare tutto non per ogni gruppetto (ROOT file) ma solo se cambia download
  //When I found a new download I need to reset all values
  if (is_new_route) {
    //reset
    is_new_route = false;
    //(tassa) ci ho ripensato se stiamo in un secondo download non prendiamo i bootnumber e timesync del precedente!
    obt_time_sync_prevvalue = 0;//obt_time_sync;
    last_time_sync_info_prevvalue = 0;//last_time_sync_info;

    obt_time_sync = 0;
    last_time_sync_info = 0;
    boot_number_prevvalue = 0;//boot_number;
    boot_number = 0;
    time_is_estimated = false;
    if (time_Offset)
      timeOffset = time_Offset;
    else
      timeOffset = retrieveTimeOffset(Table_GL_RESURS_OFFSET);
  }

  //retrieve boot number if possible
  setBootNumber(packet, pktLength, type);

  //Retrieve if possible obt_time_sync and last_time_sync_info
  int offset = 0;
  if (type == PacketType::RunHeader) {
    offset = 0;
  }
  else if (type == PacketType::RunTrailer) {
    offset = 3;
  }
  else if (type == PacketType::Mcmd) {
    //          mainLogUtil->logAll("TODO: Packet Mcmd now is not used to retrieve Absolute time. This may cause problems somewhere.");
    return;
  }
  else {
    return;
  }

  //I don't need to retrieve them again if they are good
  //  if ((obt_time_sync) || (last_time_sync_info))
  //    return;
  // no, do it, since there is a (small indeed) time drift due to different clock of PAMELA CPU and RESURS CPU...

  obt_time_sync = (((UINT32) packet[5 + offset] << 24) & 0xFF000000) + (((UINT32) packet[6 + offset] << 16)
      & 0x00FF0000) + (((UINT32) packet[7 + offset] << 8) & 0x0000FF00) + (((UINT32) packet[8 + offset]) & 0x000000FF);
  last_time_sync_info = (((UINT32) packet[9 + offset] << 24) & 0xFF000000) + (((UINT32) packet[10 + offset] << 16)
      & 0x00FF0000) + (((UINT32) packet[11 + offset] << 8) & 0x0000FF00)
      + (((UINT32) packet[12 + offset]) & 0x000000FF);

  stringstream oss;
  oss.str() = "";
  oss << "In download: " << download << " timeOffset: " << timeOffset << " obt_time_sync: " << obt_time_sync
      << " last_time_sync_info: " << last_time_sync_info << " using Packet Type: " << type->GetName();
  //    oss<<"In download: "<<download<<" timeOffset: "<<timeOffset<<" obt_time_sync: "<<obt_time_sync<<" last_time_sync_info: "<<last_time_sync_info<<" using Packet Type: "<<type->GetName().c_str();
  string msg = oss.str();
  mainLogUtil->logAll(msg);
}

//Boot Number
void PacketUser::setBootNumber(char* packet, long int pktLength, const PacketType* type) {
  //se gia calcolato esco: lo calcolo una volta per download e per continuita'
  if (boot_number)
    return;

  if (type == PacketType::VarDump) {
    int b_offset = 34;//4+5*6
    boot_number = (((UINT32) packet[1 + b_offset] << 24) & 0xFF000000) + (((UINT32) packet[2 + b_offset] << 16)
        & 0x00FF0000) + (((UINT32) packet[3 + b_offset] << 8) & 0x0000FF00) + (((UINT32) packet[4 + b_offset])
        & 0x000000FF);

    /*
     long int  dataLength = pktLength - 2; //the block of data
     int b_offset = 4;
     while (b_offset < dataLength){
     boot_number  = (((UINT32)packet[1+b_offset]<<24)&0xFF000000) + (((UINT32)packet[2+b_offset]<<16)&0x00FF0000) +  (((UINT32)packet[3+b_offset]<<8)&0x0000FF00) + (((UINT32)packet[4+b_offset])&0x000000FF);
     b_offset = b_offset + 5;
     }
     */
    stringstream oss;
    oss.str() = "";
    //          oss<<"In download: "<<download<<" boot_number: "<<boot_number<<" using Packet Type: "<<type->GetName().c_str();
    oss << "In download: " << download << " boot_number: " << boot_number << " using Packet Type: " << type->GetName();
    string msg = oss.str();
    mainLogUtil->logAll(msg);
    return;
  }
  else {
    return;
  }

}
//set the real time of the first packet and the last packet oif a group of packet if possible
void PacketUser::setReal_Time() {

  if (!obt_time_sync && !last_time_sync_info && numPKTSaved > recoverlimit) {
    time_is_estimated = true;
    obt_time_sync = obt_time_sync_prevvalue;
    last_time_sync_info = last_time_sync_info_prevvalue;
  }
  if (obt_time_sync || last_time_sync_info) {
    real_time_init = (obt_init / 1000 - obt_time_sync) + last_time_sync_info;
    //se obt si e' resettato
    if (obt_last < obt_init) {
      real_time_last = (max_pkt_obt / 1000 + obt_last / 1000 - obt_time_sync) + last_time_sync_info;
      stringstream oss;
      oss.str() = "";
      oss << "obt_last(" << obt_last << ") < obt_init(" << obt_init
          << "). It is due to Pamela Reset. Adding max_pkt_obt(" << max_pkt_obt
          << ") to obt_last in order to compute real_time_last";
      string msg = oss.str();
      mainLogUtil->logWarning(msg);
    }
    else
      real_time_last = (obt_last / 1000 - obt_time_sync) + last_time_sync_info;
    real_time_init += timeOffset;
    real_time_last += timeOffset;
  }
}

void PacketUser::setReal_TimeRECOVERY(
unsigned long int & _real_time_init,
unsigned long int & _real_time_last,
unsigned long int _obt_init,
unsigned long int _obt_last
) {
  
  if (obt_time_sync || last_time_sync_info) {
    _real_time_init = (_obt_init / 1000 - obt_time_sync) + last_time_sync_info;
    //se obt si e' resettato
    if (_obt_last < _obt_init) {
      _real_time_last = (max_pkt_obt / 1000 + _obt_last / 1000 - obt_time_sync) + last_time_sync_info;
      stringstream oss;
      oss.str() = "";
      oss << "obt_last(" << obt_last << ") < obt_init(" << obt_init
          << "). It is due to Pamela Reset. Adding max_pkt_obt(" << max_pkt_obt
          << ") to obt_last in order to compute real_time_last";
      string msg = oss.str();
      mainLogUtil->logWarning(msg);
    }
    else
      _real_time_last = (_obt_last / 1000 - obt_time_sync) + last_time_sync_info;
    _real_time_init += timeOffset;
    _real_time_last += timeOffset;
  }
}


//timeOffset
//retrieve from table Table_GL_RESURS_OFFSET timeOffset  
unsigned long int PacketUser::retrieveTimeOffset(const char * table) {
  if (!do_cont_check)
    return 0;

  //if(!table){table=Table_GL_RESURS_OFFSET}

  if (!single_connection) {
    OpenDBConnection(NULL);// no, locak tutto Nota qui ho deciso di lockare solo questa tabella e non tutte
  }

  UInt_t t0 = 0;//toffset
  stringstream oss;
  oss.str("");
  oss
      << "SELECT YEAR(OFFSET_DATE),MONTH(OFFSET_DATE),DAY(OFFSET_DATE),HOUR(OFFSET_DATE),MINUTE(OFFSET_DATE),SECOND(OFFSET_DATE),ID FROM "
      << table << " WHERE SPECIAL_FILE='" << nnnn_mmm_ppp << "';";

  string msg = oss.str();
  mainLogUtil->logAll(msg);

  TSQLResult* res = NULL;
  TSQLRow* row = NULL;
  res = sqlServer->Query(oss.str().c_str());
  if (!res) {
    mainLogUtil->logError("DBError retrieveTimeOffset 1");
    return 0;
  }

  row = res->Next();
  if (!row) {
    if (res) {
      delete res;
      res = NULL;
    }

    oss.str("");
    oss
        << "SELECT YEAR(OFFSET_DATE),MONTH(OFFSET_DATE),DAY(OFFSET_DATE),HOUR(OFFSET_DATE),MINUTE(OFFSET_DATE),SECOND(OFFSET_DATE),ID FROM "
        << table << " WHERE FROM_ORBIT < " << orbit_number << " AND SPECIAL_FILE='' order by FROM_ORBIT desc limit 1;";

    string msg2 = oss.str();
    mainLogUtil->logAll(msg2);

    res = sqlServer->Query(oss.str().c_str());
    if (!res) {
      mainLogUtil->logError("DBError retrieveTimeOffset 2");
      return 0;
    }
    row = res->Next();
    if (!row) {
      mainLogUtil->logError("DBError retrieveTimeOffset 3");
      return 0;
    }

  }

  TTimeStamp tu = TTimeStamp((UInt_t) atoi(row->GetField(0)), (UInt_t) atoi(row->GetField(1)), (UInt_t) atoi(
      row->GetField(2)), (UInt_t) atoi(row->GetField(3)), (UInt_t) atoi(row->GetField(4)), (UInt_t) atoi(row->GetField(
      5)), 0, true, 0);
  t0 = (UInt_t) tu.GetSec();

  if (res) {
    delete res;
    res = NULL;
  }
  if (row) {
    delete row;
    row = NULL;
  }

  if (!single_connection) {
    CloseDBConnection();
  }
  return t0;
}

//this is public and can be called from the main at the end of the game
void PacketUser::FinishLastGroup() {
  mainLogUtil->logAll("######################### Closing the last group ############################");
  setReal_Time();
  FinishGroup(rootfilename);
  mainLogUtil->logAll("######################### Closed the last group ############################");
  //
  if (single_connection) {
    CloseDBConnection();
  }
}

//Finish the old root file
void PacketUser::FinishGroup(char * filename) {
  if (pRun) {
    if ( !( !real_time_init && !real_time_last && numPKTSaved <= recoverlimit ) || !candelete  ) pRun->WriteFiles();
    delete pRun;
    pRun = NULL;
    stringstream close;
    close.str() = "";
    string msg;

    close << "TAG XX ######################### Close group in file: " << rootfilename << " ############################";
    msg = close.str();
    mainLogUtil->logInfo(msg);

    // se non faccio proprio controlli su continuita' neanche salvo su DB
    if (!do_cont_check)
      return;

    stringstream oss;
    oss.str() = "";

    //TODO: check when I don't want to log in DB informations
    // se non ho salvato nessun pacchetto
    if (numPKTSaved == 0) {
      stringstream err;
      err.str() = "";
      err << "NO PACKET SAVED in file: " << rootfilename << " The group is empty: you can remove this file.";
      msg = err.str();
      mainLogUtil->logError(msg);
      //      return;
    }

    //se non ho salvato nessun pacchetto buono
    if ((numPKTSaved - bad_pkt_EventReader - bad_pkt_CalibReader - bad_pkt) == 0) {
      stringstream err;
      err.str() = "";
      err << "NO GOOD PACKET SAVED in file: " << rootfilename << " The group is BAD: you may want to remove this file.";
      msg = err.str();
      mainLogUtil->logInfo(msg);
      //                        mainLogUtil->logError(msg);
      //                        return; //TODO: decidi se nn vuoi affatto usarlo o addirittura salvarlo tra i BAD???
    }

    if (!single_connection) {
      OpenDBConnection(NULL);//Nota qui ho deciso di lockare tutte le tabelle
    }


    //don't save in Table_ROOT_Good and don't search for ROOT files in the same temporal range
    if ((!real_time_init) && (!real_time_last)) {
      //saved in another table for future study
      if (saveROOT_DB(
          Table_ROOT_Bad,
          outDir,// here outDir is good, no expand EMI
          filename, pkt_number_init, pkt_number_last, obt_init, obt_last, obt_time_sync, last_time_sync_info,
          real_time_init, real_time_last, boot_number, timeOffset, bad_pkt, bad_pkt_EventReader, bad_pkt_CalibReader,
          numPKTSaved, fni, time_is_estimated) == true) {
        oss.str() = "";
        oss << "Saved information regarding file: " << filename << " in table " << Table_ROOT_Bad;
        msg = oss.str();
        mainLogUtil->logInfo(msg);
      }
      else {
        oss.str() = "";
        oss << "Problem storing information in DB regarding file: " << filename;
        msg = oss.str();
        mainLogUtil->logError(msg);
      }


      if ( numPKTSaved > recoverlimit){ // EM
        //
        if ( obt_time_sync && last_time_sync_info ){
          stringstream err;
          err.str() = "";
          err << "(EM): -- RECOVERING WHEN NUMPACKET > "<< recoverlimit <<"!! -- file=<" << filename << ">. We have a previous time sync, immediate recovery!\n";
          msg = err.str();
          mainLogUtil->logInfo(msg);
          //
          setReal_TimeRECOVERY(
                               real_time_init,
                               real_time_last,
                               obt_init,
                               obt_last
                               );
          //
        } else {
          stringstream err;
          err.str() = "";
          err << "TAG(tassa): -- RECOVERING WHEN NUMPACKET > "<< recoverlimit <<"!! -- file=<" << filename << ">. Saving in a queue to try alfer to recover it!\n";
          msg = err.str();
          mainLogUtil->logInfo(msg);
          
          //      TString qu = Form("select ID_N from ROOT_TABLE_BAD order by INSERT_TIME desc limit 1;"); //  <=========================
          TString qu = Form("select ID_N from ROOT_TABLE_BAD where FILE_NAME='%s' and PKT_NUMBER_INIT=%i and INSERTED_BY='%s';",filename,pkt_number_init,tag_value); //  <=========================
          TSQLResult *result = sqlServer->Query(qu.Data());
          TSQLRow    *row = result->Next();
          Int_t eid = -1;
          if ( row ) eid = (Int_t)atoi(row->GetField(0));
          idtorecover->AddAt(eid,arsize);
          arsize++;
          if ( arsize > 100 ) idtorecover->Set(arsize); 
          //    dbinfo *app = new dbinfo( outDir,
          //                              filename, pkt_number_init, pkt_number_last, obt_init, obt_last, boot_number, timeOffset, bad_pkt, bad_pkt_EventReader, bad_pkt_CalibReader,
          //                              numPKTSaved, fni, time_is_estimated);
          //    m_dbinfos.push_back(app);
        }
        
        CleanDisk();
        //      if (!single_connection) {
        //        CloseDBConnection();
        //      }
        //      return; // commented here...

      };
    }

    if ( real_time_init || real_time_last) {
      //saves info in DB in table Table_ROOT
      if (boot_number == 0)
        boot_number = boot_number_prevvalue;
      
      if (saveROOT_DB(
                      Table_ROOT_Good,
                      outDir, // here outDir is good, no expand EMI
                      filename, pkt_number_init, pkt_number_last, obt_init, obt_last, obt_time_sync, last_time_sync_info,
                      real_time_init, real_time_last, boot_number, timeOffset, bad_pkt, bad_pkt_EventReader, bad_pkt_CalibReader,
                      numPKTSaved, fni, time_is_estimated) == true) {
        oss.str() = "";
        oss << "Saved information regarding file: " << filename << " in table " << Table_ROOT_Good << " id= " << my_id;
        msg = oss.str();
        mainLogUtil->logInfo(msg);
        
        if (tryMerge) {
          merge_ROOTfiles();
        }
      } else {
      oss.str() = "";
      oss << "Problem storing information in DB regarding file: " << filename;
      msg = oss.str();
      mainLogUtil->logError(msg);
      }
    };

    // if dbinfo is not empty than
    if ( arsize > 0 ){

      if ( !obt_time_sync || !last_time_sync_info ){
        TString *nomino=new TString(rootfilename);
        nomino->Remove(15);
        TString qu = Form("select OBT_TIME_SYNC,LAST_TIME_SYNC_INFO from ROOT_TABLE where FILE_NAME like '%s_%%' and (abs(PKT_NUMBER_INIT-%lu)<50000 or abs(PKT_OBT_INIT-%lu)<3000000) limit 1;",nomino->Data(),pkt_number_init,obt_init);
        //
        oss.str() = "";
        oss << "(EM) Trying to find obt_time_sync and last_time_sync_info from DB, query is: " << qu.Data();
        msg = oss.str();
        mainLogUtil->logInfo(msg);
        //
        TSQLResult *result = sqlServer->Query(qu.Data());
        TSQLRow    *row = result->Next();
        if ( row && result->GetRowCount() == 1 ){
          obt_time_sync = (unsigned long int)atoll(row->GetField(0));
          last_time_sync_info = (unsigned long int)atoll(row->GetField(1));
          oss.str() = "";
          oss << "(EM) found obt_time_sync and last_time_sync_info from DB! obt_time_sync =  " <<obt_time_sync <<" last_time_sync_info = " << last_time_sync_info;
          msg = oss.str();
          mainLogUtil->logInfo(msg);
        };      
        delete nomino;
      };      

      if ( obt_time_sync && last_time_sync_info ){
        //    if(!m_dbinfos.empty()){
        //       while(!m_dbinfos.empty()){ 
        //      dbinfo *app = m_dbinfos.back();
        //      m_dbinfos.pop_back();
        Int_t ez=0;
        while ( ez < arsize ){
          Int_t eid = idtorecover->At(ez);
          if ( eid > -1 ){
          
            TString qu = Form("select FOLDER_NAME,FILE_NAME,PKT_NUMBER_INIT,PKT_NUMBER_FINAL,PKT_OBT_INIT,PKT_OBT_FINAL,BOOT_NUMBER,TIME_OFFSET,BAD_PKT,BAD_PKT_READ,BAD_PKT_CALREAD,NUM_PKT_SAVED,INPUT_NAME,TIME_IS_ESTIMATED from ROOT_TABLE_BAD where ID_N=%i;",eid);
            TSQLResult *result = sqlServer->Query(qu.Data());
            TSQLRow    *row = result->Next();
                  
            TString efolder_name=""; 
            TString efile_name="";
            unsigned long int epkt_number_in=0ULL; 
            unsigned long int epkt_number_fin=0ULL; 
            unsigned long int eobt_in=0ULL;
            unsigned long int eobt_fin=0ULL; 
            unsigned long int emboot_num=0ULL; 
            unsigned long int etime_offset=0ULL; 
            int ebad_pkt=0;
            int ebad_pkt_read=0; 
            int ebad_pkt_CalRead=0; 
            int enum_PKT_Saved=0; 
            TString enome_input=""; 
            bool e_time_is_estimated=false;

            if ( row ){
              efolder_name =                      (TString)row->GetField(0);
              efile_name =                        (TString)row->GetField(1);
              epkt_number_in =    (unsigned long int)atoll(row->GetField(2));
              epkt_number_fin =   (unsigned long int)atoll(row->GetField(3));
              eobt_in =           (unsigned long int)atoll(row->GetField(4));
              eobt_fin =          (unsigned long int)atoll(row->GetField(5));
              emboot_num =        (unsigned long int)atoll(row->GetField(6));
              etime_offset =      (unsigned long int)atoll(row->GetField(7));
              ebad_pkt =                         (int)atoi(row->GetField(8));
              ebad_pkt_read =                    (int)atoi(row->GetField(9));
              ebad_pkt_CalRead =                (int)atoi(row->GetField(10));
              enum_PKT_Saved =                  (int)atoi(row->GetField(11));
              enome_input =                      (TString)row->GetField(12);
              e_time_is_estimated =            (bool)atoi(row->GetField(13));
            };
            delete result;

            setReal_TimeRECOVERY(
                                 real_time_init,
                                 real_time_last,
                                 eobt_in,
                                 eobt_fin
                                 );
            //
          
            if (saveROOT_DB(
                            Table_ROOT_Good,
                            efolder_name.Data(),
                            efile_name.Data(), 
                            epkt_number_in, 
                            epkt_number_fin, eobt_in, eobt_fin, obt_time_sync, last_time_sync_info,
                            real_time_init, real_time_last, emboot_num, etime_offset, ebad_pkt, ebad_pkt_read, ebad_pkt_CalRead,
                            enum_PKT_Saved, enome_input.Data(), e_time_is_estimated) == true) {
              oss.str() = "";
              oss << "\nTAG(tassa): RECOVERED!!!! Saved information regarding file: " << efile_name << " in table " << Table_ROOT_Good << " id= " << my_id;
              msg = oss.str();
              mainLogUtil->logInfo(msg);
              /*merging we use global variable. we haveto set with current value*/
            
              strcpy(outDir,efolder_name.Data() );
              //            outDir=efolder_name.Data();
              strcpy(filename,efile_name.Data() );
              //            filename=efile_name.Data();
              pkt_number_init=epkt_number_in;
              pkt_number_last=epkt_number_fin;
              obt_init=eobt_in;
              obt_last=eobt_fin;
              boot_number=emboot_num;
              timeOffset=etime_offset;
              bad_pkt=ebad_pkt;
              bad_pkt_EventReader=ebad_pkt_read;
              bad_pkt_CalibReader=ebad_pkt_CalRead;
              numPKTSaved=enum_PKT_Saved;
              strcpy(fni,enome_input.Data() );
              //            fni=enome_input.Data();
              time_is_estimated=e_time_is_estimated;
            
              if (tryMerge) {
                merge_ROOTfiles();
              }
            
              TString qu2 = Form("update ROOT_TABLE_BAD set ON_DISK='MVD' where ID_N=%i;",eid);
              sqlServer->Query(qu2.Data());

            
            }
            else {
              oss.str() = "";
              oss << "Problem storing information in DB regarding file: " << filename;
              msg = oss.str();
              mainLogUtil->logError(msg);
            }
          
          
          };
          ez++;
        }
        //
        idtorecover->Reset();
        delete idtorecover;
        idtorecover = new TArrayI(100);
        arsize =  0;
        //      delete app;
      };
    };
    
    CleanDisk();
    if (!single_connection) {
      CloseDBConnection();
    }

  }//pRun
}
  
  
  void PacketUser::CleanDisk(){
    if ( !candelete ) return;
    //
    // delete small files present in root_table_bad from disk:
    //
    TString qu = Form("select FOLDER_NAME,FILE_NAME from ROOT_TABLE_BAD where NUM_PKT_SAVED<=%i and INSERTED_BY='%s' and ON_DISK='YES';",recoverlimit,tag_value);
    TSQLResult *result = sqlServer->Query(qu.Data());
    TSQLRow    *row = result->Next();
    while ( row ){
      TString tbdel = (TString)gSystem->ExpandPathName(row->GetField(0))+"/"+ (TString)row->GetField(1);
      stringstream err;
      string msg;
      err.str() = "";      
      err << " Deleting file " << tbdel.Data() << " from disk! ";
      msg = err.str();
      mainLogUtil->logInfo(msg);
      gSystem->Unlink(tbdel.Data());
      //      printf("ciccio %s  \n",tbdel.Data());
      TString q2u = Form("UPDATE ROOT_TABLE_BAD set ON_DISK='NO' where FOLDER_NAME='%s' and FILE_NAME='%s' and NUM_PKT_SAVED<=%i and INSERTED_BY='%s';",row->GetField(0),row->GetField(1),recoverlimit,tag_value);
      //      printf("pluto %s \n",q2u.Data());
      sqlServer->Query(q2u.Data());
      //      printf("paperoga \n");
      row=result->Next();
      //
    };
    
  }

//save in Table_ROOT_Good or in Table_ROOT_Bad
bool PacketUser::saveROOT_DB(const char* table_name, const char* folder_name, const char* file_name,
    unsigned long int pkt_number_in, unsigned long int pkt_number_fin, unsigned long int obt_in,
    unsigned long int obt_fin, unsigned long int oT_sync, unsigned long int lT_sync_info, unsigned long int mtime_init,
    unsigned long int mtime_last, unsigned long int mboot_num, unsigned long int time_offset, int bad_pkt,
    int bad_pkt_read, int bad_pkt_CalRead, int num_PKT_Saved, const char* nome_input, bool _time_is_estimated) {
  //TODO: forse far ritornare ID della cosa appena inserita se e' andatato tutto ok invece che true false?
  stringstream oss;
  oss.str("");
  oss << "INSERT INTO " << table_name
      << " (ID_N, FOLDER_NAME, FILE_NAME, PKT_NUMBER_INIT, PKT_NUMBER_FINAL, PKT_OBT_INIT, PKT_OBT_FINAL, OBT_TIME_SYNC, LAST_TIME_SYNC_INFO, REAL_TIME_INIT, REAL_TIME_LAST, BOOT_NUMBER, TIME_OFFSET,BAD_PKT,BAD_PKT_READ,BAD_PKT_CALREAD,NUM_PKT_SAVED,INPUT_NAME,INSERT_TIME,TIME_IS_ESTIMATED,INSERTED_BY)"
      << " VALUES ('" << 0 << "','" << folder_name << "','" << file_name << "','" << pkt_number_in << "','"
      << pkt_number_fin << "','" << obt_in << "','" << obt_fin << "','" << oT_sync << "','" << lT_sync_info << "','"
      << mtime_init << "','" << mtime_last << "','" << mboot_num << "','" << time_offset << "','" << bad_pkt << "','"
      << bad_pkt_read << "','" << bad_pkt_CalRead << "','" << num_PKT_Saved << "','" << nome_input << "'," << "NULL,"
      << _time_is_estimated << ",'" << tag_value << "'" << ");";
  string msg = oss.str();
  mainLogUtil->logAll(msg);

  stringstream oss1;
  oss1.str() = "";
  string msg1;
  string query;

  TSQLResult* res = NULL;

  query = oss.str();
  msg1 = "SaveROOT_DB query: ";
  msg1 += query;
  mainLogUtil->logInfo(msg1);

  res = sqlServer->Query(query.c_str());
  if (!res) {
    oss1 << "DBError UNABLE to: " << oss.str().c_str() << endl;
    msg1 = oss1.str();
    mainLogUtil->logError(msg1);
    return false;
  }


  if(!strcmp(table_name,Table_ROOT_Good)) {
    //the ID of the current ROOT file in table Table_ROOT_Good
    my_id = select_maxIDN_DB(table_name);
    if (!boot_number) {
      if (id_to_recover_index < 1000)
        id_to_recover[id_to_recover_index++] = my_id;
    }
    else if (boot_number && id_to_recover_index && !is_new_route) {
      recover_boot_number();
    }
  }
  //    cout<<"DBG: my_id = "<<my_id<<endl;
  if (res) {
    delete res;
    res = NULL;
  }
  return true;
}

void PacketUser::recover_boot_number() {
  string msg1;
  stringstream oss1;
  stringstream oss;
  oss.str("");
  string query = "";
  TSQLResult* res = NULL;
  if (!boot_number || !id_to_recover_index)
    return;

  for (int i = 0; i < id_to_recover_index; i++) {
    oss.str("");
    oss << "UPDATE " << Table_ROOT_Good << " SET BOOT_NUMBER=" << boot_number << " WHERE ID_N=" << id_to_recover[i]
        << ";";
    query = oss.str();
    res = sqlServer->Query(query.c_str());
    if (!res) {
      oss1 << "DBError UNABLE to: " << query.c_str() << endl;
      msg1 = oss1.str();
      mainLogUtil->logError(msg1);
      return;
    }
  }

  for (int i = 0; i < id_to_recover_index; i++) {
    oss.str("");
    oss << "UPDATE " << Table_ROOT_Merging << " SET BOOT_NUMBER=" << boot_number << " WHERE ROOT_ID_N="
        << id_to_recover[i] << ";";
    query = oss.str();
    res = sqlServer->Query(query.c_str());
    if (!res) {
      oss1 << "DBError UNABLE to: " << query.c_str() << endl;
      msg1 = oss1.str();
      mainLogUtil->logError(msg1);
      return;
    }
  }

  id_to_recover_index = 0;
}

/**********************************************************************************************/
/*###########################################################################################################*/
/**********************************         MERGING          *************************************************/
/*###########################################################################################################*/
//      Merge ROT files: find other ROOT files in the same temporal ranges
//    it finds ROOT files that covers particular temporal range of interest and save this info in DB
/**********************************************************************************************/
//try to merge current ROOT file with files in DB
bool PacketUser::merge_ROOTfiles() {
  stringstream log;
  log.str("");
  string slog;

  mainLogUtil->logInfo("Trying merging ROOT files");
  double percentage = (double) (bad_pkt + bad_pkt_EventReader + bad_pkt_CalibReader) / numPKTSaved;

  TSQLResult* res = NULL;
  TSQLRow* row = NULL;
  //numero di ROOT files trovati sul DB che hanno relaz di tipo dato
  unsigned int num_rows = 0;

  //Record su DB:
  unsigned int idN = 0;
  unsigned int root_id_db = 0;
  unsigned long int pkt_num_in_db = 0;
  unsigned long int pkt_num_fin_db = 0;
  unsigned long int pkt_obt_in_db = 0;
  unsigned long int pkt_obt_fin_db = 0;
  unsigned long int time_in_db = 0;
  unsigned long int time_fin_db = 0;
  unsigned int boot_num_db = 0;
  double perc = 0;

  //marco_new: non considero mai caso di continuita'(mio finale=DB iniz o viceversa), non mi interessa
  //marco_new: after e before contengono anche smaller ma con un bordo comune
  for (int relaz = AFTER; relaz <= BIGGER; relaz++) {
    //marco_new: esci se sono arrivato a zero
    if (real_time_last == real_time_init) {
      log.str("");
      log << "Current File Lenght=0; real_time_last=real_time_init= " << real_time_last;
      slog = log.str();
      mainLogUtil->logAll(slog);
      mainLogUtil->logInfo("Finish merging ROOT files");
      if (res) {
        delete res;
        res = NULL;
      }
      if (row) {
        delete row;
        row = NULL;
      }
      return true;
    }

    num_rows = 0;
    res = Select_merging(Table_ROOT_Merging, real_time_init, real_time_last, (type_Rel_ROOT) relaz);

    if (!res) {
      mainLogUtil->logError("DB_ERROR: Unable to find ROOT files to be merged RES=null");
      return false;
    }

    num_rows = res->GetRowCount();
    log.str("");
    log << "Found " << num_rows << " ROOT file in DB with relation of kind = " << relaz;
    slog = log.str();
    mainLogUtil->logAll(slog);

    if (num_rows > 0) {
      for (unsigned int i = 0; i < num_rows; i++) {
        row = res->Next();
        if (!row) {
          mainLogUtil->logError("DB_ERROR: Unable to find ROOT files to be merged. ROW=null");
          if (res) {
            delete res;
            res = NULL;
          }
          return false;
        }

        //Leggi Record dal DB:
        idN = atoll(row->GetField(0));
        root_id_db = atoll(row->GetField(1));
        pkt_num_in_db = atoll(row->GetField(2));
        pkt_num_fin_db = atoll(row->GetField(3));
        pkt_obt_in_db = atoll(row->GetField(4));
        pkt_obt_fin_db = atoll(row->GetField(5));
        time_in_db = atoll(row->GetField(6));
        time_fin_db = atoll(row->GetField(7));
        boot_num_db = atoll(row->GetField(8));
        perc = atof(row->GetField(9));

        log.str("");
        log << "*** ROOT file found in DB has idN= " << idN << "  root_id_db= " << root_id_db;
        log << " pkt_num_in_db= " << pkt_num_in_db << "  pkt_num_fin_db= " << pkt_num_fin_db << "  pkt_obt_in_db= "
            << pkt_obt_in_db << "  pkt_obt_fin_db= " << pkt_obt_fin_db;
        log << "  time_in_db= " << time_in_db << "  time_fin_db= " << time_fin_db << " boot_num_db= " << boot_num_db
            << "  perc= " << perc << "  ***";
        log << "\n*** Current ROOT file has pkt_number_init= " << pkt_number_init << "  obt_init= " << obt_init
            << "  real_time_init= " << real_time_init;
        log << " pkt_number_last= " << pkt_number_last << "  obt_last= " << obt_last << "  real_time_last= "
            << real_time_last << " ***";
        slog = log.str();
        mainLogUtil->logAll(slog);
        if (perc > percentage) {
          mainLogUtil->logInfo("Current ROOT file is better than the one in DB");
          //modifica DB
          updateMergeROOT_DB(Table_ROOT_Merging, root_id_db, pkt_num_in_db, pkt_num_fin_db, pkt_obt_in_db,
              pkt_obt_fin_db, time_in_db, time_fin_db, boot_num_db, perc, idN, (type_Rel_ROOT) relaz);
        }
        else {
          mainLogUtil->logInfo("Found ROOT file in DB is better than current ROOT file");
          if (relaz == AFTER) {
            mainLogUtil->logInfo("Relaz:AFTER = Changing Init values of the Current ROOT file");
            pkt_number_init = (pkt_num_fin_db + 1);
            obt_init = pkt_obt_fin_db;
            real_time_init = time_fin_db;
            log.str("");
            log << "\n*** Current ROOT file becomes: pkt_number_init= " << pkt_number_init << "  obt_init= "
                << obt_init << "  real_time_init= " << real_time_init;
            log << " pkt_number_last= " << pkt_number_last << "  obt_last= " << obt_last << "  real_time_last= "
                << real_time_last << " ***";
            slog = log.str();
            mainLogUtil->logAll(slog);
          }
          else if (relaz == BEFORE) {
            mainLogUtil->logInfo("Relaz:BEFORE = Changing Last values of the Current ROOT file");
            pkt_number_last = (pkt_num_in_db - 1);
            obt_last = pkt_obt_in_db;
            real_time_last = time_in_db;

            log.str("");
            log << "\n*** Current ROOT file becomes: pkt_number_init= " << pkt_number_init << "  obt_init= "
                << obt_init << "  real_time_init= " << real_time_init;
            log << " pkt_number_last= " << pkt_number_last << "  obt_last= " << obt_last << "  real_time_last= "
                << real_time_last << " ***";
            slog = log.str();
            mainLogUtil->logAll(slog);
          }
          else if (relaz == SMALLER) {
            //non devo proprio salvarlo ed esco ...
            mainLogUtil->logInfo("Relaz:SMALLER = Nothing to save");
            mainLogUtil->logInfo("\nFinish merging ROOT files");
            if (res) {
              delete res;
              res = NULL;
            }
            if (row) {
              delete row;
              row = NULL;
            }
            return true;
          }
          else if (relaz == BIGGER)//spezzettamento
          {
            mainLogUtil->logInfo("Relaz:BIGGER = (1st part) Saving the first part of the Current ROOT file");
            saveMergeROOT_DB(Table_ROOT_Merging, my_id, pkt_number_init, (pkt_num_in_db - 1), obt_init, pkt_obt_in_db,
                real_time_init, time_in_db, boot_number, percentage);

            mainLogUtil->logInfo("Relaz:BIGGER = (2nd part)Changing Init values of the Current ROOT file");
            pkt_number_init = (pkt_num_fin_db + 1);
            obt_init = pkt_obt_fin_db;
            real_time_init = time_fin_db;

            log.str("");
            log << "\n*** Current ROOT file becomes: pkt_number_init= " << pkt_number_init << "  obt_init= "
                << obt_init << "  real_time_init= " << real_time_init;
            log << " pkt_number_last= " << pkt_number_last << "  obt_last= " << obt_last << "  real_time_last= "
                << real_time_last << " ***";
            slog = log.str();
            mainLogUtil->logAll(slog);
          }//if relaz
        }//if perc
      }//for num_rows
    }//if num_rows

    if (res) {
      delete res;
      res = NULL;
    }
    if (row) {
      delete row;
      row = NULL;
    }

  }//for relaz

  mainLogUtil->logInfo("Saving Current ROOT file");
  saveMergeROOT_DB(Table_ROOT_Merging, my_id, pkt_number_init, pkt_number_last, obt_init, obt_last, real_time_init,
      real_time_last, boot_number, percentage);

  mainLogUtil->logInfo("Finish merging ROOT files");
  return true;

}

//unsigned int root_id,  percentage non le devo modificare
bool PacketUser::updateMergeROOT_DB(const char* table_name, unsigned int root_id, unsigned long int pkt_number_in,
    unsigned long int pkt_number_fin, unsigned long int obt_in, unsigned long int obt_fin,
    unsigned long int mtime_init, unsigned long int mtime_last, unsigned long int mboot_num, double bad_perc,
    unsigned int ID_record, type_Rel_ROOT type_rel) {

  stringstream oss;
  oss.str("");

  if (type_rel == AFTER) {
    mainLogUtil->logInfo("Relaz:AFTER = Updating Last values of the DB ROOT file");
    //marco_new:tolti tutti gli apici che qui non devono essere
    
    int good = 1;
    if ( pkt_number_in >= (pkt_number_init - 1) && abs((int)(pkt_number_in-(pkt_number_init-1)))<10 ) good = 0;
    
    oss << "UPDATE " << table_name << " SET PKT_NUMBER_FINAL =" << (pkt_number_init - 1) << ", PKT_OBT_FINAL ="
        << obt_init << ", REAL_TIME_LAST =" << real_time_init << ", GOOD="<< good << " WHERE ID_N =" << ID_record << ";";

  }
  else if (type_rel == BEFORE) {
    mainLogUtil->logInfo("Relaz:BEFORE = Updating Init values of the DB ROOT file");

    int good = 1;
    if ( (pkt_number_last+1) >= pkt_number_fin && abs((int)((pkt_number_last+1)-pkt_number_fin))<10 ) good = 0;
    
    oss << "UPDATE " << table_name << " SET PKT_NUMBER_INIT =" << (pkt_number_last + 1) << ", PKT_OBT_INIT ="
        << obt_last << ", REAL_TIME_INIT =" << real_time_last << ", GOOD="<< good << " WHERE ID_N =" << ID_record << ";";
  }
  else if (type_rel == SMALLER) {
    //spezzettamentento
    mainLogUtil->logInfo("Relaz:SMALLER = (1st part) Updating Last values of the DB ROOT file");

    int good = 1;
    if ( pkt_number_in >= (pkt_number_init - 1) && abs((int)(pkt_number_in-(pkt_number_init-1)))<10 ) good = 0;

    oss << "UPDATE " << table_name << " SET PKT_NUMBER_FINAL=" << (pkt_number_init - 1) << ", PKT_OBT_FINAL ="
        << obt_init << ", REAL_TIME_LAST =" << real_time_init << ", GOOD="<< good << " WHERE ID_N =" << ID_record << ";";

    mainLogUtil->logInfo("Relaz:SMALLER = (2nd part) Saving the second part of the DB ROOT file");

    saveMergeROOT_DB(table_name, root_id, (pkt_number_last + 1), pkt_number_fin, obt_last, obt_fin, real_time_last,
        mtime_last, mboot_num, bad_perc);
  }
  else if (type_rel == BIGGER) {
    //marco_new: ok anche se coincidenti etc
    //    oss << "DELETE FROM "<< table_name <<" WHERE ID_N ="<< ID_record<<";"; // EMILIANO
    oss << "UPDATE " << table_name << " SET GOOD=0 WHERE ID_N =" << ID_record << ";"; // EMILIANO DO NOT DELETE FROM ROOT_TABLE_MERGING JUST SET GOOD FLAG TO ZERO
    mainLogUtil->logInfo("Record deleted from merging table");
  }
  else
    return false;

  string msg = oss.str();
  mainLogUtil->logInfo(msg);

  stringstream oss1;
  oss1.str() = "";
  string msg1;

  TSQLResult* res = NULL;
  res = sqlServer->Query(oss.str().c_str());
  if (!res) {
    oss1 << "DBError UNABLE to: " << oss.str().c_str() << endl;
    msg1 = oss1.str();
    mainLogUtil->logError(msg1);
    return false;
  }
  if (res) {
    delete res;
    res = NULL;
  }
  return true;
}

//cerca eventuali ROOT files in DB che possono venire mergiati
//marco_new bordi: messo in after a before casi in cui smaller con un bordo in comune
//marco_new bordi: messo in bigger casi con bordi in comune; tra questi anche caso particolare di coincidente
//marco_new bordi: smaller resta solo caso in cui DB e' piu largo sia a dx che a sx
TSQLResult* PacketUser::Select_merging(const char* table_name, unsigned long int mtime_init, unsigned long int mtime_last,
    type_Rel_ROOT type_rel) {
  TSQLResult* res = NULL;
  stringstream query;
  query.str("");

  if (type_rel == AFTER)
    query << "select * from " << table_name << " where GOOD=1 and REAL_TIME_LAST > " << mtime_init
        << " and REAL_TIME_LAST <= " << mtime_last << " and REAL_TIME_INIT < " << mtime_init; // EMI
  else if (type_rel == BEFORE)
    query << "select * from " << table_name << " where GOOD=1 and REAL_TIME_INIT >= " << mtime_init
        << " and REAL_TIME_INIT < " << mtime_last << " and REAL_TIME_LAST > " << mtime_last; // EMI
  else if (type_rel == SMALLER)
    query << "select * from " << table_name << " where GOOD=1 and REAL_TIME_INIT < " << mtime_init
        << " and REAL_TIME_LAST > " << mtime_last; // EMI
  else if (type_rel == BIGGER)
    query << "select * from " << table_name << " where GOOD=1 and REAL_TIME_INIT >= " << mtime_init
        << " and REAL_TIME_LAST <= " << mtime_last; // EMI
  else
    return 0;

  query << " order by REAL_TIME_INIT ASC;";//marco_new: inutile
  //se volessi mettere un filtro sulla qualita'
  //    query<<" and NUM_PKT_SAVED - BAD_PKT_CALREAD - BAD_PKT_READ - BAD_PKT >= 2" ;
  string msg = query.str();
  mainLogUtil->logAll(msg);
  res = sqlServer->Query(query.str().c_str());
  stringstream oss1;
  oss1.str() = "";
  string msg1;

  if (!res) {
    oss1 << "DBError UNABLE to: " << query.str().c_str() << endl;
    msg1 = oss1.str();
    mainLogUtil->logError(msg1);
  }

  return res;
}

bool PacketUser::saveMergeROOT_DB(const char* table_name, unsigned int root_id, unsigned long int pkt_number_in,
    unsigned long int pkt_number_fin, unsigned long int obt_in, unsigned long int obt_fin,
    unsigned long int mtime_init, unsigned long int mtime_last, unsigned long int mboot_num, double percentage) {

  int good = 1;
  if ( pkt_number_in >= pkt_number_fin && abs((int)(pkt_number_fin-pkt_number_in))<10 ) good = 0;

  stringstream oss;
  oss.str("");
  oss << "INSERT INTO " << table_name
      << " (ID_N, ROOT_ID_N, PKT_NUMBER_INIT, PKT_NUMBER_FINAL, PKT_OBT_INIT, PKT_OBT_FINAL, REAL_TIME_INIT, REAL_TIME_LAST, BOOT_NUMBER, BAD_PKT_PERCENTAGE, INSERT_TIME, INSERTED_BY,GOOD)"
      << " VALUES ('" << 0 << "','" << root_id << "','" << pkt_number_in << "','" << pkt_number_fin << "','" << obt_in
      << "','" << obt_fin << "','" << mtime_init << "','" << mtime_last << "','" << mboot_num << "','" << percentage
      << "'," << "NULL" << ",'" << tag_value << "','" << good <<"');";

  string msg = oss.str();
  mainLogUtil->logAll(msg);

  //marco_new: controlla
  //marco_new: esci se sono arrivato a zero
  //    if(real_time_last==real_time_init){
  if (mtime_init == mtime_last) {
    mainLogUtil->logAll("Nothing to be saved");

    //          if(res){delete res; res = NULL;}
    //          if(row){delete row; row = NULL;}
    return true;
  }

  stringstream oss1;
  oss1.str() = "";
  string msg1;

  TSQLResult* res = NULL;
  res = sqlServer->Query(oss.str().c_str());
  if (!res) {
    oss1 << "DBError UNABLE to: " << oss.str().c_str() << endl;
    msg1 = oss1.str();
    mainLogUtil->logError(msg1);
    return false;
  }
  if (res) {
    delete res;
    res = NULL;
  }
  return true;
}

//Utility: return max(IDN) 
unsigned int PacketUser::select_maxIDN_DB(const char* table_name) {

  if (!strcmp(table_name, Table_ROOT_Bad)) {
    return 0;
  }
  unsigned int idN = 0;
  TSQLResult* res = NULL;
  stringstream oss;
  oss.str("");
  oss << "select max(ID_N) from " << table_name;
  //    string msg = oss.str();
  //    mainLogUtil->logAll(msg);
  res = sqlServer->Query(oss.str().c_str());

  stringstream oss1;
  oss1.str() = "";
  string msg1;

  if (!res) {
    oss1 << "DBError UNABLE to: " << oss.str().c_str() << endl;
    msg1 = oss1.str();
    mainLogUtil->logError(msg1);
    return 0;
  }

  TSQLRow* row = NULL;
  row = res->Next();
  if (!row) {
    oss1 << "DBError UNABLE to: " << oss.str().c_str() << endl;
    msg1 = oss1.str();
    mainLogUtil->logError(msg1);
    return 0;
  }

  idN = atoi(row->GetField(0));
  if (row) {
    delete row;
    row = NULL;
  }
  if (res) {
    delete res;
    res = NULL;
  }

  oss << ". idN = " << idN;
  string msg = oss.str();
  mainLogUtil->logAll(msg);

  return idN;
}

/**
 * Lock par=table, if par=null lock all tables
 */
int PacketUser::LockTables(const char* tableTobeLocked) {
  //se non ho il DB  :)
  if (!do_cont_check)
    return 999;

  TSQLResult* res = NULL;
  stringstream oss;
  oss.str("");
  //  if (!tableTobeLocked)
  oss << "lock table " << Table_ROOT_Good << " write, " << Table_ROOT_Bad << " write, " << Table_GL_RESURS_OFFSET
      << " write, " << Table_ROOT_Merging << " write;";
    //  else
    //    oss << "lock table " << tableTobeLocked << " write; ";

  string msg = oss.str();
  mainLogUtil->logAll(msg);

  res = sqlServer->Query(oss.str().c_str());

  stringstream oss1;
  oss1.str() = "";
  string msg1;

  if (!res) {
    oss1 << "DBError UNABLE to: " << oss.str().c_str() << endl;
    msg1 = oss1.str();
    mainLogUtil->logError(msg1);
    return 1;
  }

  if (res) {
    delete res;
    res = NULL;
  }
  mainLogUtil->logAll("TABLES LOCKED");
  return 0;
}

/**
 * UNLock tables
 */
int PacketUser::UnLockTables() {
  //se non ho il DB  :)
  if (!do_cont_check)
    return 999;

  TSQLResult* res = NULL;
  stringstream oss;
  oss.str("");
  oss << "unlock tables;";

  string msg = oss.str();
  mainLogUtil->logAll(msg);

  res = sqlServer->Query(oss.str().c_str());

  stringstream oss1;
  oss1.str() = "";
  string msg1;

  if (!res) {
    oss1 << "DBError UNABLE to: " << oss.str().c_str() << endl;
    msg1 = oss1.str();
    mainLogUtil->logError(msg1);
    return 1;
  }

  if (res) {
    delete res;
    res = NULL;
  }
  mainLogUtil->logAll("TABLES UNLOCKED");

  return 0;
}

/*********************************************************/
//marco_new_31
void PacketUser::OpenDBConnection(const char* tableTobeLocked) {
  if (do_cont_check) {
    //controllo che non sia gia' aperta
    if (!sqlServer) {
      sqlServer = TSQLServer::Connect(pelosconnection, db_user, db_pwd);
      if ((!sqlServer) || (!(sqlServer->IsConnected()))) {
        cout << "Can not connect with MYSQL sever" << endl;
      }
      mainLogUtil->logAll("Connected to DB");
      stringstream myquery;
      myquery.str("");
      myquery << "SET time_zone='+0:00'";
      sqlServer->Query(myquery.str().c_str());
      //blocca le tabelle
      LockTables(tableTobeLocked);
    }
  }

}

//marco_new_31
void PacketUser::CloseDBConnection() {
  if (do_cont_check) {
    //controllo che ce ne sia una aperta
    if (sqlServer && sqlServer->IsConnected()) {
      //marco_new: sblocca tutte le tabelle
      UnLockTables();
      sqlServer->Close();
      delete sqlServer;
      sqlServer = NULL;
      mainLogUtil->logAll("Closed Connection to DB");
    }
  }
}

/***********************************************************************************************************************/
/*#################################################### HIC SUNT LEONES ################################################*/
/***********************************************************************************************************************/
//UNUSED: dbg functions...

//Return the system time in ms
unsigned long long PacketUser::Record_Time() {
  timeval tv;
  gettimeofday(&tv, NULL);
  unsigned long long timems = (unsigned long long) tv.tv_sec * (unsigned long long) 1000 + tv.tv_usec / 1000;
  return timems;
}

//dbg_functions:

//UNUSED. save a packet in a file
void PacketUser::savePKT_file(char* headerPkt, char* pamPkt, long int length, bool append, char* nomefile) {
  char fno[80] = "";
  ofstream fout;
  sprintf(fno, "%s/pkt%d_of_%s.pkt", gSystem->ExpandPathName(outDir), numPKTSaved, nomefile); // EMI

  if (append == true)
    fout.open(fno, ios::binary | ios::app);
  else
    fout.open(fno, ios::binary);

  if (!fout) {
    cout << "can not open output file " << fno << endl;
    return;
  }
  fout.write(headerPkt, LENGTH_HEADER_PKT);
  fout.write(pamPkt, length);
  fout.close();
}

//UNUSED
void PacketUser::saveALL_PKT(char* headerPkt, char* pamPkt, long int length, bool append) {
  char fno[80] = "";
  ofstream fout;
  if (append == true) {
    sprintf(fno, "%s/packets.pkt", gSystem->ExpandPathName(outDir)); // EMI
    fout.open(fno, ios::binary | ios::app);
  }
  else {
    sprintf(fno, "%s/packet%d.pkt", gSystem->ExpandPathName(outDir), numPKT); // EMI
    fout.open(fno, ios::binary);
  }

  if (!fout) {
    cout << "can not open output file " << fno << endl;
    return;
  }
  fout.write(headerPkt, LENGTH_HEADER_PKT);
  fout.write(pamPkt, length);
  fout.close();
}

}