// File: hrichparoraio.pc
//
// AUTHOR : Ilse Koenig
// Created : 02/05/2000 by I. Koenig
//
// Last update by Ilse Koenig: 16/01/2002
// Last update by Thomas Eberl: 02/10/28 16:10:26
// Last update by Tassilo Christ: 26/03/2002
//_HADES_CLASS_DESCRIPTION 
//////////////////////////////////////////////////////////////////////////////
// HRichParOraIo
//
// Interface class to database Oracle for input/output of parameters needed
// by the Rich
// (uses the Oracle C/C++ precompiler)
//
//////////////////////////////////////////////////////////////////////////////
using namespace std;
#include "hrichparoraio.h"
#include "hades.h"
#include "hruntimedb.h"
#include "hrun.h"
#include "hspectrometer.h"
#include "hdetector.h"
#include "hparset.h"
#include "hparcond.h"
#include "hparamlist.h"
#include "hrichdetector.h"
#include "hrichthresholdpar.h"
#include "hrichcalpar.h"
#include "hrichcalparcell.h"
#include "hrichgeometrypar.h"
#include "hrichmappingpar.h"
#include "hrichframe.h"
#include "hrichwire.h"
#include "hrichpad.h"
#include <iostream>
#include <iomanip>
#include <unistd.h>

#define SQLCA_STORAGE_CLASS extern
#define ORACA_STORAGE_CLASS extern

// Oracle communication area
#include <oraca.h>
// SQL Communications Area
/* Include the SQL Communications Area, a structure through
 * which ORACLE makes runtime status information such as error
 * codes, warning flags, and diagnostic text available to the
 * program.
 */
#include <sqlca.h>

ClassImp(HRichParOraIo)

#define RICH_MAXSEC 6
#define RICH_MAXPADS_READOUT 4800
#define RICH_MAXPADS_DIGI 4894
#define RICH_MAXPADS_DESIGN 4850
#define RICH_MAXFRAME_CNRS 7
#define RICH_MAXPAD_DIGI_CNRS 4
#define RICH_MAXWIRES 182
#define RICH_MAXCHANNELS 17464
#define RICH_PADMATRIX 9696
#define RICH_UNCONNCH 88

HRichParOraIo::HRichParOraIo(HOraConn* pC) : HDetParOraIo(pC) {
  // constructor
  // sets the name of the I/O class "HRichParIo"
  // gets the pointer to the connection class
  fName="HRichParIo";
  initModules=0;
  richIds=0;
}
//************************************************************************

HRichParOraIo::~HRichParOraIo() {
  // destructor
  if (initModules) delete initModules;
  if (richIds) delete richIds;
}
//************************************************************************


Bool_t HRichParOraIo::init(HParSet* pPar,Int_t* set) {
  // calls special read-function for each parameter container

  const Text_t* name=pPar->GetName();
  if (strncmp(name,"RichAnalysisParameters",strlen("RichAnalysisParameters"))==0)
     return readCond((HParCond*)pPar,set);
  if (strncmp(name,"RichDigitisationParameters",strlen("RichDigitisationParameters"))==0)
     return readCond((HParCond*)pPar,set);
  if (strncmp(name,"RichCorrelatorParameters",strlen("RichCorrelatorParameters"))==0)
     return readCond((HParCond*)pPar,set);

  if (startIo()==-1) return kFALSE; // no modules found
    if (strcmp(name,"RichThresholdPar")==0) return read(((HRichThresholdPar*)pPar),set);	
    if (strcmp(name,"RichCalPar")==0) return read(((HRichCalPar*)pPar),set);	
    if (strcmp(name,"RichGeometryParameters")==0) return read(((HRichGeometryPar*)pPar),set);
    if (strcmp(name,"RichMappingParameters")==0) return read(((HRichMappingPar*)pPar),set);

  cout<<"initialization of "<<name<<" not possible from Oracle!"<<endl;
  return kFALSE;
}
//************************************************************************

Int_t HRichParOraIo::write(HParSet* pPar) {
  // calls the appropriate write function for the container
  const Text_t* name=pPar->GetName();
  if (strncmp(name,"RichAnalysisParameters",strlen("RichAnalysisParameters"))==0)
     return writeCond((HParCond*)pPar);
  if (strncmp(name,"RichDigitisationParameters",strlen("RichDigitisationParameters"))==0)
     return writeCond((HParCond*)pPar);
  if (strncmp(name,"RichCorrelatorParameters",strlen("RichCorrelatorParameters"))==0)
     return writeCond((HParCond*)pPar);
  if (startIo()==-1) return kFALSE; // no modules found
  if (!strcmp(name,"RichThresholdPar"))
     return writePar((HRichThresholdPar*)pPar); 
  if (!strcmp(name,"RichCalPar"))
     return writePar((HRichCalPar*)pPar);
  if (!strcmp(name,"RichGeometryParameters"))
     return writePar((HRichGeometryPar*)pPar);
  if (!strcmp(name,"RichMappingParameters"))
     return writePar((HRichMappingPar*)pPar);
  return 0;
}
//************************************************************************


Int_t HRichParOraIo::startIo(void) {
  // Gets the run start of the first run and reads the detector setup from Oracle
  if (richIds==0) {
    richIds=new TArrayI(RICH_MAXSEC);
    for(Int_t i=0;i<RICH_MAXSEC;i++) richIds->AddAt(0,i);
    initModules=new TArrayI(RICH_MAXSEC);
  }
  if (getRunStart()==-1) return -1;
  return readIds();

}
//************************************************************************

Int_t HRichParOraIo::readIds() {
  // reads the id numbers of all modules defined for actual run
  // and stores them in array richIds
  // returns the number of modules found

  EXEC SQL BEGIN DECLARE SECTION;
    int sec[RICH_MAXSEC];
  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR DO showSqlError("readIds()");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;

  EXEC SQL SELECT sector INTO :sec FROM rich_ana.setup_at_run;
  Int_t nMods=sqlca.sqlerrd[2]; // number of rows returned by select 
  HRichDetector* det=(HRichDetector*)(gHades->getSetup()->getDetector("Rich"));
  for(Int_t i=0;i<nMods;i++) {
    if (det->getModule((sec[i]-1),0)) 
	richIds->AddAt(sec[i],(sec[i]-1));
    //only for modules defined in setup, modules (sectors) are added from the setup
    //valid for the time defined by the requested run
  }
  return nMods;
}
//************************************************************************

void HRichParOraIo::printIds() {
  // prints the id numbers of all RICH sectors
  if (richIds) {
    cout<<"RICH sectors:";
    for(Int_t i=0;i<RICH_MAXSEC;i++) cout<<" "<<richIds->At(i);
    cout<<'\n';
  }
}
//************************************************************************

void HRichParOraIo::printInfo(const Text_t* msg) {
  // prints the modules initialized from Oracle
  // will later go to the log file
  Bool_t first=kTRUE;
  for(Int_t i=0;i<RICH_MAXSEC;i++) {
    if (initModules->At(i)) {
      if (first) {
        cout<<msg;
        first=kFALSE;
      }
      cout<<(initModules->At(i)-1)<<" ";
    }
  }
  cout<<'\n';
}
//************************************************************************


Int_t HRichParOraIo::getCalParVersion(HParSet* pPar,Int_t* set,Int_t* versions) {
  // reads the latest version number for the calibration parameters
  // valid for the current event file

  HParOraSet* oraSet=getOraSet(pPar);
  Int_t runStart=getRunStart(pPar);
  if (oraSet->contextId==-1 || runStart==-1) return -1;
  Int_t contVers=pPar->getInputVersion(inputNumber);
  if (contVers!=-1 && runStart>=oraSet->versDate[0] && runStart<=oraSet->versDate[1]) {
    return contVers;
  }
  oraSet->clearVersDate();
	
  EXEC SQL BEGIN DECLARE SECTION;
   int context; 
   struct {
     int sec[RICH_MAXSEC];
     int vers[RICH_MAXSEC];
     double since[RICH_MAXSEC];
     double until[RICH_MAXSEC];
   } calvers;	
  EXEC SQL END DECLARE SECTION;

  context=oraSet->contextId;
  Int_t nSec=0;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("getCalParVersion(HParOraSet*,Int_t, Int_t)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL SELECT sector, version, hdate.to_ansitime(valid_since),
                                   hdate.to_ansitime(valid_until)
           INTO :calvers
           FROM rich_ana.rich_cal_par_vers_at_date
           WHERE context_id = :context;

  nSec=sqlca.sqlerrd[2];
  for(Int_t i=0;i<nSec;i++) { // loop over found modules
    Int_t s=calvers.sec[i]-1;
    cout << "Retrieved calibration parameter version number: "<< calvers.vers[i] << endl;
    if (richIds->At(s)>0) {
      versions[s]=calvers.vers[i];
      if (calvers.since[i]>oraSet->versDate[0]) {
        oraSet->versDate[0]=calvers.since[i];
      }
      if (calvers.until[i]<oraSet->versDate[1]) {
        oraSet->versDate[1]=calvers.until[i];
      }	
    }
  }
  if (oraSet->versDate[0]>=0) return getActRunId();
  return -1;
}


Bool_t HRichParOraIo::read(HRichThresholdPar* pPar, Int_t* set) {
  // reads the threshold parameters
  HParOraSet* oraSet=getOraSet(pPar);
  Int_t runStart=getRunStart(pPar);
  Int_t contVers=pPar->getInputVersion(inputNumber);
  if (contVers!=-1 && runStart>=oraSet->versDate[0] && runStart<=oraSet->versDate[1]) {
    return kTRUE; //no action necessary
  }
  oraSet->clearVersDate();
  pPar->clear();
  EXEC SQL BEGIN DECLARE SECTION;
    struct {
      int    sec[RICH_MAXSEC];
      double first[RICH_MAXSEC];
      double last[RICH_MAXSEC];
      int    p_calpar_id[RICH_MAXSEC];
      float  p_offset[RICH_MAXSEC];
      float  p_multiply_mean[RICH_MAXSEC];
      float  p_multiply_sigma[RICH_MAXSEC];
    } thresholds;
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichThresholdPar)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL SELECT sector_id, hdate.to_ansitime(used_first),
                  hdate.to_ansitime(used_last),
                  cal_par_vers_id, shift, multiply_offset, multiply_sigma
           INTO :thresholds
           FROM rich_ana.rich_threshold_par_at_run;
  initModules->Reset();
  for(Int_t i=0;i<sqlca.sqlerrd[2];i++) { // loop over found sectors
    Int_t sec=thresholds.sec[i]-1;
    if (set[sec]&&richIds->At(sec)>0) {
      if (thresholds.first[i]>oraSet->versDate[0]) {
        oraSet->versDate[0]=thresholds.first[i];
      }
      if (thresholds.last[i]<oraSet->versDate[1]) {
        oraSet->versDate[1]=thresholds.last[i];
      }	
      pPar->setCalParVers(sec,thresholds.p_calpar_id[i]);
      pPar->setShift(sec,thresholds.p_offset[i]);
      pPar->setMultiply_offset(sec,thresholds.p_multiply_mean[i]);	
      pPar->setMultiply_sigma(sec,thresholds.p_multiply_sigma[i]);
      initModules->AddAt(sec+1,sec);
    }
  }
  Bool_t allFound=kTRUE;
  for(Int_t i=0;i<RICH_MAXSEC;i++) {
    if (set[i]==1 && initModules->At(i)==0) {
      allFound=kFALSE;
    }
  }
  if (allFound) {
    pPar->setInputVersion(getActRunId(),inputNumber);
    pPar->setChanged();
    printInfo("RichThresholdPar: sector(s) initialized from Oracle: ");
  } else {
    pPar->setInputVersion(-1,inputNumber);
  }
  return allFound;
}


Bool_t HRichParOraIo::read(HRichCalPar* pPar, Int_t* set) {
  Int_t version=getPredefVersion(pPar);//find out if a user-defined version is set	
  Int_t contVers=pPar->getInputVersion(inputNumber);//cntr version that is set now
  Int_t versions[RICH_MAXSEC];

  if (version==-1) {
    version=getCalParVersion(pPar,set,versions);
  }
  //No predefined version is set, get the version which is supposed to be loaded 
  //for the current run and context.

  if (version==-1) { 
    pPar->setInputVersion(-1,inputNumber);
    return kFALSE;
    //something went wrong, there was no version validated for our current run!
  }
  if (contVers==version) {
    //Here we are if and only if the previously loaded version of the thresholds and the
    //version to be loaded for the current run are identical! It will save us a lot of time
    //if we skip loading!
    return kTRUE; //no action necessary
  }

  //Here we are if either no version was loaded before or if the version required for the current
  //run differs from the version loaded previously.

  pPar->setInputVersion(version,inputNumber);
  pPar->clear();

  EXEC SQL BEGIN DECLARE SECTION;
    int sec;
    int c_vers;
    struct {
      int p_col[RICH_MAXPADS_READOUT];
      int p_row[RICH_MAXPADS_READOUT];
      float s[RICH_MAXPADS_READOUT];//slope
      float o[RICH_MAXPADS_READOUT];//offset
      float si[RICH_MAXPADS_READOUT];//sigma
    } cal;
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichCalPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL DECLARE cal_cur CURSOR FOR
    SELECT pad_col, pad_row, slope, offset, sigma
      FROM rich_ana.rich_cal_par_all
      WHERE sector = :sec
        AND vers =:c_vers;
  Bool_t allFound=kTRUE;
  initModules->Reset();
  HLocation loc;
  loc.setNIndex(3);
  HRichCalParCell* pCell=0;

  for(Int_t i=0;i<RICH_MAXSEC;i++) {
    //  cout<<"looping over sec: "<<i<<endl;
    if (set[i]) {
      loc[0] = i;
      sec=richIds->At(i);
      //c_vers=calparVersions->At((sec-1));
      c_vers=versions[i];
      if (sec>0 && c_vers>0) {
        //  cout<<"opening cursor for index: "<<i<<" sec: "<<sec<<" cal_par_vers: "<<c_vers<<endl;
        EXEC SQL OPEN cal_cur;
        EXEC SQL FETCH cal_cur INTO :cal;
        Int_t nPads=sqlca.sqlerrd[2];
	// cout<<"number of found rows : "<<nPads<<endl;
        for(Int_t k=0;k<nPads;k++) {
          loc[1] = cal.p_row[k];   // y
          loc[2] = cal.p_col[k];   // x
	  //cout<<"y: "<<loc[1]<<"  x: "<<loc[2]<<" slope: "<<cal.s[k]<<" offset: "<<cal.o[k]<<" sigma: "<<cal.si[k]<<endl;
	//loc.Dump();
          pCell = (HRichCalParCell*)pPar->getSlot(loc);
		//cout<<pCell<<endl;
          if (pCell) {
            //pCell = new(pCell) HRichCalParCell;
            pCell->setParams(cal.s[k],cal.o[k],cal.si[k]);
          } else {
            Error("read(HRichCalPar*,Int_t*)",
                  "slot not found:  %i %i %i",loc[0],loc[1],loc[2]);
            EXEC SQL CLOSE cal_cur;
            return kFALSE;
          }
        }
        if (nPads>0) {
          set[i]=0;
          initModules->AddAt(i+1,i);
        } else allFound=kFALSE;
      } else allFound=kFALSE;
    }
  }
  EXEC SQL CLOSE cal_cur;
  pPar->setChanged();
  printInfo("RichCalPar: sector(s) initialized from Oracle: ");
  //pPar->PrintCalPar();//check for negative offsets
  return allFound;
}
//************************************************************************


Bool_t HRichParOraIo::read(HRichGeometryPar* pPar, Int_t* set) {
  // reads the geometry parameters and fill the RichGeometryPar container

  // ----- retrieve nr of frame corners --------
  // ** the retrieved value is used for consistency checking only
  EXEC SQL BEGIN DECLARE SECTION;
    int fr_cnrs_max;
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL SELECT max(frame_corner_id)
           INTO   :fr_cnrs_max
           FROM   rich_ana.frame_corner
           WHERE  frame_id=1;
  if(fr_cnrs_max != RICH_MAXFRAME_CNRS) {
    cout<<"ERROR: max nr of frame corners inconsistent !"<<endl;
    return kFALSE;
  }
// -------------------------------------------


// -- retrieve frame corner values ----------------------
  EXEC SQL BEGIN DECLARE SECTION;
    float fr_x[RICH_MAXFRAME_CNRS], fr_y[RICH_MAXFRAME_CNRS];
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found1;
  EXEC SQL DECLARE frame_cur CURSOR FOR
           SELECT x,y
           FROM   rich_ana.frame_corner
           WHERE  frame_id=1;
  EXEC SQL OPEN frame_cur;
  EXEC SQL FETCH frame_cur INTO :fr_x,:fr_y;
not_found1:
  EXEC SQL CLOSE frame_cur;

// ------ retrieve max nr of wires -----------
// ** the retrieved value is used for consistency checking only
  EXEC SQL BEGIN DECLARE SECTION;
        int wire_id_max;
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL SELECT max(wire_id)
           INTO   :wire_id_max
           FROM   rich_ana.wire
           WHERE  sector_id=1; // equal nr of wires for all sectors
  if(wire_id_max != RICH_MAXWIRES) {
    cout<<"ERROR: max nr of wires per sector inconsistent !"<<endl;
    return kFALSE;
  }
// -------------------------------------------

// ---- retrieve wire coordinates --------------
  EXEC SQL BEGIN DECLARE SECTION;
    float wires[RICH_MAXWIRES];
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found2;
  EXEC SQL DECLARE wire_cur CURSOR FOR
           SELECT dist_sym
           FROM   rich_ana.wire
           WHERE  sector_id=1; // wire pos identical for all sectors
  EXEC SQL OPEN wire_cur;
  EXEC SQL FETCH wire_cur INTO :wires;

not_found2:
  EXEC SQL CLOSE wire_cur;
// --------------------------------------------

// - retrieve distance of wires from padplane -
  EXEC SQL BEGIN DECLARE SECTION;
        float wire_dist;
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL SELECT DISTINCT dist_plane
           INTO :wire_dist
           FROM   rich_ana.wire;
  Int_t nr_ret_rows = sqlca.sqlerrd[2];
  if (nr_ret_rows != 1) {
    cout<<"ERROR: Not all wires have the same distance from the padplane !"<<endl;
    return kFALSE;
  }
// -------------------------------------------

// ---------- retrieve sector center ---------
  EXEC SQL BEGIN DECLARE SECTION;
    float cntr_x[RICH_MAXSEC];
    float cntr_y[RICH_MAXSEC];
    float cntr_z[RICH_MAXSEC];
    int center_id[RICH_MAXSEC];
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found3;
  EXEC SQL DECLARE cntr_cur CURSOR FOR
           SELECT center_id,x,y,z
           FROM rich_ana.sector_center;
  EXEC SQL OPEN cntr_cur;
  EXEC SQL FETCH cntr_cur INTO :center_id,:cntr_x,:cntr_y,:cntr_z;
not_found3:
  EXEC SQL CLOSE cntr_cur;
// -------------------------------------------

// -- retrieve rotation angles and sector shift rel to target of sectors ----
  EXEC SQL BEGIN DECLARE SECTION;
    int rot_angle[RICH_MAXSEC];
    float sector_shift[RICH_MAXSEC];
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found4;
  EXEC SQL DECLARE rot_angle_cur CURSOR FOR
           SELECT rot_angle,sec_shift
           FROM rich_ana.sector_pos;
  EXEC SQL OPEN rot_angle_cur;
  EXEC SQL FETCH rot_angle_cur INTO :rot_angle,:sector_shift;
not_found4:
  EXEC SQL CLOSE rot_angle_cur;
// -------------------------------------------

// -- retrieve rotation matrices -------------
  EXEC SQL BEGIN DECLARE SECTION;
    float mat_col[3*3*RICH_MAXSEC];
    float mat_row[3*3*RICH_MAXSEC];
    float mat_value[3*3*RICH_MAXSEC];
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found5;
  EXEC SQL DECLARE rotmat_cur CURSOR FOR
           SELECT mat_col,mat_row,value
           FROM rich_ana.sec_rotmat;
  EXEC SQL OPEN rotmat_cur;
  EXEC SQL FETCH rotmat_cur INTO :mat_col,:mat_row,:mat_value;
not_found5:
  EXEC SQL CLOSE rotmat_cur;
// -------------------------------------------

//============================================
//---------------- PAD INI -------------------
//============================================


// -- retrieve max nr of digi pads -----------
// ** for consistency checking only
  EXEC SQL BEGIN DECLARE SECTION;
        int digi_pads_max;
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL SELECT count( distinct pad_pos_id)
           INTO   :digi_pads_max
           FROM   rich_ana.digi_pad_corner;
  if(digi_pads_max != RICH_MAXPADS_DIGI) {
    cout<<"ERROR: max nr of pads for digitization per sector inconsistent !"<<endl;
    return kFALSE;
  }
// -------------------------------------------

// -- retrieve max nr of digi pad corners ----
// ** for consistency checking only
  EXEC SQL BEGIN DECLARE SECTION;
    int digi_cnrs_max;
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL SELECT count(pad_pos_id)
           INTO   :digi_cnrs_max
           FROM   rich_ana.digi_pad_corner;
  if(digi_pads_max != RICH_MAXPADS_DIGI) {
    cout<<"ERROR: max nr of pads for digitization per sector inconsistent !"<<endl;
    return kFALSE;
  }
// -------------------------------------------

// ----- retrieve digi pad corners -----------
  EXEC SQL BEGIN DECLARE SECTION;
    float cornerx[RICH_MAXPAD_DIGI_CNRS*RICH_MAXPADS_DIGI];
    float cornery[RICH_MAXPAD_DIGI_CNRS*RICH_MAXPADS_DIGI];
    int pad_pos_id[RICH_MAXPAD_DIGI_CNRS*RICH_MAXPADS_DIGI];
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found8;
  EXEC SQL DECLARE pad_cur CURSOR FOR
           SELECT pad_pos_id,x,y
           FROM   rich_ana.digi_pad_corner
           ORDER BY pad_pos_id,corner_num ASC;
  EXEC SQL OPEN pad_cur;
  EXEC SQL FETCH pad_cur INTO :pad_pos_id,:cornerx,:cornery;
not_found8:
  EXEC SQL CLOSE pad_cur;
// -------------------------------------------

// ------ retrieve digi pad angles -----------
// set the version of theta/phi to be used:
// currently: angle_par_vers_id =1 means Toni with M-C-simulation
//                      (4670 per sector, many readout pads are missing !)
//            angle_par_vers_id =2 means Soenke num calc for pads used in digitization
//                      (4894 per sector)
//            angle_par_vers_id =3 means Soenke num calc for pads of the orig pad design
//                      (4850 per sector)
  Int_t av = 2; // FIXME: should be chosen by user,
                // later accord to time stamp or sim run
  EXEC SQL BEGIN DECLARE SECTION;
    int ang_pos_id[RICH_MAXSEC*RICH_MAXPADS_DIGI];
    int ang_sector_id[RICH_MAXSEC*RICH_MAXPADS_DIGI];
    float ang_theta[RICH_MAXSEC*RICH_MAXPADS_DIGI];
    float ang_phi[RICH_MAXSEC*RICH_MAXPADS_DIGI];
    int cav = av;
  EXEC SQL END   DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichGeometryPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found9;
  EXEC SQL DECLARE angle_cur CURSOR FOR
           SELECT pad_pos_id, sector_id, theta, phi
           FROM rich_ana.pad a, rich_ana.angle_par b
           WHERE b.angle_par_vers_id=:cav
           AND a.pad_id = b.pad_id
           ORDER BY pad_pos_id ASC, sector_id ASC;
  EXEC SQL OPEN angle_cur;
  EXEC SQL FETCH angle_cur INTO :ang_pos_id,:ang_sector_id,:ang_theta,:ang_phi;
not_found9:
  EXEC SQL CLOSE angle_cur;
// -------------------------------------------

  HRichDetector *pRichDet = (HRichDetector*)gHades->getSetup()->getDetector("Rich");
  for (Int_t i = 0; i < 6; i++) {
    pPar->setSectorActive(i, pRichDet->getModule(i,0));
    if (pPar->getSectorActive(i) > 0) pPar->incrementSectorNr();
  }
// the number of cols and rows to be used may only be retrieved from
// HRichDetector, never set them yourself ! --> Pedestal !!!
  if (pPar->getSectorsNr() > 0) {
        pPar->setColumns(pRichDet->getColumns());
        pPar->setRows(pRichDet->getRows());
  }

// sector parameters
  for (Int_t i = 0; i < 6; i++) {
   if (pPar->getSectorActive(i) > 0) {
//     pPar->fSectors[i].setSectorId(i);
    switch (i) {
     case 0: pPar->setSectorPhi(i, 90.);
             break;
     case 1: pPar->setSectorPhi(i, 150.);
             break;
     case 2: pPar->setSectorPhi(i, 210.);
             break;
     case 3: pPar->setSectorPhi(i, 270.);
             break;
     case 4: pPar->setSectorPhi(i, 330.);
             break;
     case 5: pPar->setSectorPhi(i, 30.);
             break;
      }
    }
  }

// initialize frame corners

  Int_t fArea0, fArea1, nrcor1, nrcor2;
  Float_t a, b, xcor1, xcor2, ycor1, ycor2;

// corner nr is incremented in addCorner() 
  HRichFrameCorner *pFrameCorner;
  for (Int_t i = 0; i < RICH_MAXFRAME_CNRS; i++) {
    pFrameCorner = new HRichFrameCorner;
    pFrameCorner->setX(fr_x[i]);
    pFrameCorner->setY(fr_y[i]);
    pFrameCorner->setCornerNr(i);
    pPar -> getFramePar()->addCorner(pFrameCorner);
  }

// calculation of flag area
  for (Int_t i = 0; i < pPar -> getFramePar()->getCornerNr(); i++) {
    pPar -> getFramePar()->getCorner(i)->getXY(&xcor1, &ycor1);
    nrcor1 = pPar -> getFramePar()->getCorner(i)->getCornerNr();
    if (i+1 < pPar -> getFramePar()->getCornerNr()) {
      pPar -> getFramePar()->getCorner(i+1)->getXY(&xcor2, &ycor2);
      nrcor2 = pPar -> getFramePar()->getCorner(i+1)->getCornerNr();
    } else {
      pPar -> getFramePar()->getCorner(0)->getXY(&xcor2, &ycor2);
      nrcor2 = pPar -> getFramePar()->getCorner(0)->getCornerNr();
    }

    fArea0 = fArea1 = 0;
    if (xcor1 == xcor2) {
      for (Int_t j = 0; j < pPar -> getFramePar()->getCornerNr(); j++) {
        if (pPar -> getFramePar()->getCorner(j)->getCornerNr() != nrcor1 &&
            pPar -> getFramePar()->getCorner(j)->getCornerNr() != nrcor2) {
          if (pPar -> getFramePar()->getCorner(j)->getX() > xcor1) fArea1++;
          if (pPar -> getFramePar()->getCorner(j)->getX() < xcor1) fArea0++;
        }
      }
      if (fArea1+2 == pPar -> getFramePar()->getCornerNr()) pPar -> getFramePar()->getCorner(i)->setFlagArea(1);
      else if (fArea0+2 == pPar -> getFramePar()->getCornerNr()) pPar -> getFramePar()->getCorner(i)->setFlagArea(0);
      else {
        Error("HRichFrame::initAscii",
              "Inconsistency in frame corners coordinates.");
        throw (Bool_t) kFALSE;
      }
    } else if (ycor1 == ycor2) {
      for (Int_t j = 0; j < pPar -> getFramePar()->getCornerNr(); j++) {
        if (pPar -> getFramePar()->getCorner(j)->getCornerNr() != nrcor1 &&
            pPar -> getFramePar()->getCorner(j)->getCornerNr() != nrcor2) {
          if (pPar -> getFramePar()->getCorner(j)->getY() > ycor1) fArea1++;
          if (pPar -> getFramePar()->getCorner(j)->getY() < ycor1) fArea0++;
        }
      }
      if (fArea1+2 == pPar -> getFramePar()->getCornerNr()) pPar -> getFramePar()->getCorner(i)->setFlagArea(1);
      else if (fArea0+2 == pPar -> getFramePar()->getCornerNr()) pPar -> getFramePar()->getCorner(i)->setFlagArea(0);
      else {
        Error("HRichFrame::initAscii",
              "Inconsistency in frame corners coordinates.");
        throw (Bool_t) kFALSE;
      }
    } else {
      a = (ycor2 - ycor1)/(xcor2 - xcor1);
      b = (xcor2*ycor1 - xcor1*ycor2)/(xcor2 - xcor1);
      for (Int_t j = 0; j < pPar -> getFramePar()->getCornerNr(); j++) {
        if (pPar -> getFramePar()->getCorner(j)->getCornerNr() != nrcor1 &&
            pPar -> getFramePar()->getCorner(j)->getCornerNr() != nrcor2) {
          if (pPar -> getFramePar()->getCorner(j)->getY() > a * pPar -> getFramePar()->getCorner(j)->getX() + b) fArea1++;
          if (pPar -> getFramePar()->getCorner(j)->getY() < a * pPar -> getFramePar()->getCorner(j)->getX() + b) fArea0++;
        }
      }
      if (fArea1+2 == pPar -> getFramePar()->getCornerNr()) pPar -> getFramePar()->getCorner(i)->setFlagArea(1);
      else if (fArea0+2 == pPar -> getFramePar()->getCornerNr()) pPar -> getFramePar()->getCorner(i)->setFlagArea(0);
      else {
        Error("HRichFrame::initAscii",
              "Inconsistency in frame corners coordinates.");
        throw (Bool_t) kFALSE;
      }
    }
  } //   end of loop over all corners

// initialize wire coords

  HRichWire *pWire;
  pPar->getWiresPar()->setWiresNr(RICH_MAXWIRES);
  Float_t distwire = ( wires[RICH_MAXWIRES-1]/10. - wires[RICH_MAXWIRES-2]/10. ) / 2. ;
  pPar->getWiresPar()->setDistWire(distwire);
  for (Int_t i = 0; i < pPar->getWiresPar()->getWiresNr(); i++) {
    pWire = new HRichWire;
    pWire->setNrWire(i);
    pWire->setXWire(wires[i]/10.);
    pPar->getWiresPar()->addWire(pWire);
  }
  pPar->setDistanceWiresPads(wire_dist/10.);
  pPar->setSectorShift(sector_shift[0]);// all sectors have a uniform shift

// ------ ini pad structure --------------

// create pad table (TClonesArray)
// set dimensions
// dimensions taken from HRichDetector
  if (pPar->getPadsPar()->createPads(pPar->getColumns(),pPar->getRows()) == 0) {
    cout<<"ERROR: zero Pads created in initialization"<<endl;
    return kFALSE;
  }

  // this is the dummy initialisation of all pads structure
  // pPar->fColumns*pPar->fRows ==> 96*96
  for (Int_t i = 0; i < pPar->getPadsPar()->getPadsNr(); i++) {
    HRichPad *pad = new HRichPad;
    pad->setPadNr(i);
    pad->CalcNrtoXY(pPar->getPadsPar()->getPadsX());
    pad->setPadActive(0);
    pad->setPadFlag(0);
    pad->setAmplitFraction(0.);
    for (Int_t j=0; j<RICH_MAXSEC; j++) pad->setPhi(j,0.0);
    pad->setTheta(0.0);
    pPar->getPadsPar()->setPad(pad, i);
    delete pad;
  }

  Int_t c_index,a_index,cpos_tmp,apos_tmp,xpad,ypad,cnr_cntr,sec_cntr;

  for (Int_t i=0;i< RICH_MAXPADS_DIGI;i++){
    cnr_cntr = 0;
    sec_cntr = 0;
    c_index = i*RICH_MAXPAD_DIGI_CNRS;
    a_index = i*RICH_MAXSEC;
    cpos_tmp = pad_pos_id[c_index];
    apos_tmp = ang_pos_id[a_index];
    if (cpos_tmp != apos_tmp){
      cout<<"ERROR HRichParOraIO: operating on different pads !"<<endl;
      return kFALSE;
    }
    xpad = cpos_tmp/100;
    ypad = cpos_tmp%100;
    HRichPad *pad = pPar->getPadsPar()->getPad(xpad, ypad);
    pad->setPadActive(1);
    pPar->getPadsPar()->incActivePadsNr();
//    pad->setLinkedPadsNr(0);
    // FIXME: linked pads are currently not cared for.
    // The reason is that they are not used in digitization and
    // there are dummy values in the ASCII file
    // --------------------------------------------------------

    // ---- pad corners ----

    for(Int_t j=c_index;j<c_index+RICH_MAXPAD_DIGI_CNRS;j++) {
      HRichPadCorner *padcorner = new HRichPadCorner;
      //cout<<cornerx[j]<<"  "<<cornery[j]<<endl;
      padcorner->setX(cornerx[j]/10.);
      padcorner->setY(cornery[j]/10.);
      padcorner->setCornerNr(cnr_cntr++);
      pad->addCorner(padcorner);
    }
    pad->setTheta(ang_theta[a_index]); // pad theta angle

    // ---- pad phi angles - recalculated and checked later on ---

    for(Int_t k=a_index;k<a_index+RICH_MAXSEC;k++){
      if (pPar->getSectorActive(sec_cntr) > 0) {
        pad->setPhi(sec_cntr,ang_phi[k]);
      }
      sec_cntr++;
    }
  }

// finalization of pad variables, checks and calculations 
  pPar->getPadsPar()->initParameters();
// here the azimuthal angle (phi) for the pads is calculated from the 
// pad coordinates in mm on the padplane. The phis are stored in ORACLE
// but not in the ASCII file version of the parameters.
// The consistency between ORACLE data and the calculated angles is
// checked and the calculated values are used in the analysis. 
  pPar->initParameters();
// --------------- end of pad ini ----------------------

  pPar->setChanged();
  // no version management foreseen
  pPar->setInputVersion(1,inputNumber);
  pPar->setStatic();

// -----------------------------------------------------

  cout<<"RichGeometryPar initialized from Oracle"<<'\n';

  return kTRUE;
}
//************************************************************************


Bool_t HRichParOraIo::read(HRichMappingPar* pPar, Int_t* set) {
    // reads the electronic mapping parameters and fill the RichMappingPar container

  EXEC SQL BEGIN DECLARE SECTION;
    struct {
      int rc[RICH_MAXPADS_READOUT];
      int port[RICH_MAXPADS_READOUT];
      int mod[RICH_MAXPADS_READOUT];
      int ch[RICH_MAXPADS_READOUT];
      int x[RICH_MAXPADS_READOUT];
      int y[RICH_MAXPADS_READOUT];
    } mapping;
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichMappingPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found20;
  EXEC SQL DECLARE map_cur CURSOR FOR
    SELECT DISTINCT rc,port,module,channel,x,y
      FROM rich_ana.upi_at_date
       ORDER BY rc,port,module,channel;

  EXEC SQL OPEN map_cur;
  EXEC SQL FETCH map_cur INTO :mapping;
not_found20:
  EXEC SQL CLOSE map_cur;
  Int_t nPads=sqlca.sqlerrd[2];
  pPar->fPadsNr = nPads;
  EXEC SQL BEGIN DECLARE SECTION;
    int max_rc;
    int max_port;
    int max_mod;
    int max_ch;
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichMappingPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  EXEC SQL SELECT max(rc) INTO :max_rc FROM rich_ana.upi_at_date;
  EXEC SQL SELECT max(port) INTO :max_port FROM rich_ana.upi_at_date;
  EXEC SQL SELECT max(module) INTO :max_mod FROM rich_ana.upi_at_date;
  EXEC SQL SELECT max(channel) INTO :max_ch FROM rich_ana.upi_at_date;
	
  HRichDetector *pRichDet = 
	(HRichDetector*)gHades->getSetup()->getDetector("Rich");
  if (!pRichDet) return kFALSE;
 
  pPar->fRows = pRichDet->getRows();
  pPar->fCols = pRichDet->getColumns();
  pPar->fUnconnCh = RICH_UNCONNCH;
  pPar->fRCs = max_rc+1;
  pPar->fPorts = max_port+1;
  pPar->fModules = max_mod+1;
  pPar->fChannels = max_ch+1;
  cout<<"Nr of pads returned :"<<nPads<<endl;
  pPar->uiplog  = new TVector(RICH_MAXCHANNELS);
  pPar->padsxy  = new TVector(RICH_MAXCHANNELS);
  pPar->padsx   = new TVector(RICH_MAXCHANNELS);
  pPar->padsy   = new TVector(RICH_MAXCHANNELS);
  pPar->uncuip  = new TVector(RICH_MAXCHANNELS);

  for (Int_t i=0;i<RICH_MAXCHANNELS;i++) {
    (*(pPar->uiplog))(i) = 0;
    (*(pPar->padsxy))(i) = 0;
    (*(pPar->padsx))(i)  = 0;
    (*(pPar->padsy))(i)  = 0;
    (*(pPar->uncuip))(i) = 0;
  }
  pPar->xyuip    = new TVector(RICH_PADMATRIX);
  pPar->xyuiplog = new TVector(RICH_PADMATRIX);
  for (Int_t i=0;i<RICH_PADMATRIX;i++) {
    (*(pPar->xyuip))(i)    = 0;
    (*(pPar->xyuiplog))(i) = 0;
  }
  Int_t fAddress1,fAddress2;
  for (Int_t k=0;k<nPads;k++){
    fAddress1 = mapping.rc[k]*10000+mapping.port[k]*1000+
                mapping.mod[k]*100+mapping.ch[k];
    fAddress2 = mapping.x[k]*100 + mapping.y[k];
    (*(pPar->uiplog))(fAddress1) = 1;
    (*(pPar->padsxy))(fAddress1) = fAddress2;
    (*(pPar->padsx))(fAddress1) = mapping.x[k];
    (*(pPar->padsy))(fAddress1) = mapping.y[k];
    (*(pPar->xyuip))(fAddress2) = fAddress1;
    (*(pPar->xyuiplog))(fAddress2) = 1;
  }

  EXEC SQL BEGIN DECLARE SECTION;
    struct {
      int rc[RICH_UNCONNCH];
      int port[RICH_UNCONNCH];
      int mod[RICH_UNCONNCH];
      int ch[RICH_UNCONNCH];
    } unconn_map;
  EXEC SQL END DECLARE SECTION;
  EXEC SQL WHENEVER SQLERROR DO showSqlError("read(HRichMappingPar*,Int_t*)");
  EXEC SQL WHENEVER NOT FOUND GOTO not_found21;
  EXEC SQL DECLARE unconn_cur CURSOR FOR
    SELECT DISTINCT rc,port,module,channel
      FROM rich_ana.unconn_pads_at_date
      ORDER BY rc,port,module,channel;
  EXEC SQL OPEN unconn_cur;
  EXEC SQL FETCH unconn_cur INTO :unconn_map;
not_found21:
  EXEC SQL CLOSE unconn_cur;

  nPads=sqlca.sqlerrd[2];
  if (nPads != RICH_UNCONNCH) Error("read(HRichMappingPar*,Int_t*)",
                              "nr of readout channels and unconnected channels is inconsistent");

  for (Int_t k=0;k<nPads;k++){
    fAddress1 = unconn_map.rc[k]*10000+unconn_map.port[k]*1000+
                 unconn_map.mod[k]*100+unconn_map.ch[k];
    (*(pPar->uncuip))(fAddress1) = 1;
  }

  pPar->setChanged();
  // no version management foreseen
  pPar->setInputVersion(1,inputNumber);
  pPar->setStatic();
// -----------------------------------------------------
  cout<<"RichMappingPar initialized from Oracle"<<'\n';

  return kTRUE;
}
//************************************************************************


Int_t HRichParOraIo::writePar(HRichGeometryPar* pPar) {
  cout<<"sorry, this is a dummy write function for the Geometry container"<<endl;
  return 0;
}
//************************************************************************


Int_t HRichParOraIo::writePar(HRichThresholdPar* pPar) {
  cout<<"sorry, this is a dummy write function for the Threshold container"<<endl;
  cout<<"It is definitely NOT foreseen that threshold parameters are written from" << endl;	
  cout<<"within the analysis software!"<<endl;
  return 0;
}

Int_t HRichParOraIo::writePar(HRichMappingPar* pPar) {
  cout<<"sorry, this is a dummy write function for the Mapping container"<<endl;
  cout<<"It is not foreseen to write the Mapping container to Oracle."<<endl;
  return 0;
}
//************************************************************************

Int_t HRichParOraIo::writePar(HRichCalPar* pPar) {
  // creates a new version and writes the calibration parameters to Oracle
  // returns the version number or -1 if an error occurs

  cout<<"****************************************************************\n";
  Int_t version=createCalparVers(pPar);
  if (version==-1) return -1;
  EXEC SQL BEGIN DECLARE SECTION;
    int sec[RICH_MAXPADS_READOUT];
    int x[RICH_MAXPADS_READOUT];
    int y[RICH_MAXPADS_READOUT];
    int vers[RICH_MAXPADS_READOUT];
    float slo[RICH_MAXPADS_READOUT];
    float ofs[RICH_MAXPADS_READOUT];
    float sig[RICH_MAXPADS_READOUT];
    int rows_to_insert;

  EXEC SQL END DECLARE SECTION;

  HLocation loc;
  loc.set(3,0,0,0);
  HRichCalParCell* pCell=0;
  Float_t cs=0, co=0, csi=0;
  Int_t nsec=0, nrow=0, ncol=0;
  for(Int_t s=0;s<pPar->getNSectors();s++) {
    Int_t nCell=-1;
    if (richIds->At(s)>0) {
      loc[0] = s;
      for(Int_t r=0;r<pPar->getNRows();r++) {
        loc[1]=r;    // y
        for(Int_t c=0;c<pPar->getNColumns();c++) {
          loc[2]=c;  // x
          pCell = (HRichCalParCell*)pPar->getObject(loc);
          if (pCell) {
	    cs=pCell->getSlope();
            co=pCell->getOffset();
            csi=pCell->getSigma();
       	    if (csi != 0.) { // only non-default values
			     // The no-default-values condition was set
			     // different here and in the ascii file io
			     // object. Changed consistently by Tassilo 
			     // April 2002
              nCell++;
              if (nCell==RICH_MAXPADS_READOUT) {
                Error("writePar(HRichCalPar*)","\n Number of Pads > 4800\n");
                rollback();
                return -1;
              }
              sec[nCell]=s+1;
              x[nCell]=c;
              y[nCell]=r;
              vers[nCell]=version;
              slo[nCell]=cs;
              ofs[nCell]=co;
              sig[nCell]=csi;
            }
          } else { cout<<nrow<<" "<<ncol<<" "<<nsec<<" "<<r<<" "<<c<<" "<<s<<endl; }
        }
      }
    }
    if (nCell==-1) continue;
    rows_to_insert=nCell+1;
    // FIX IT !! CHECK FOR THE RETURN VALUE of the cal_par_query
    // function and exclude inserts for return value -1

    EXEC SQL WHENEVER SQLERROR GOTO not_found;
    EXEC SQL WHENEVER NOT FOUND GOTO not_found;
    EXEC SQL FOR :rows_to_insert
        INSERT INTO rich_ana.rich_cal_par_data
            (pad_id, vers_id, slope, offset, sigma)
            VALUES (rich_ana.rich_cal_par_query.get_pad_id(:sec,:x,:y),
                    :vers, :slo, :ofs, :sig);

    cout<<"sector_id: "<<(s+1)<<"    "<<rows_to_insert<<" rows inserted\n";
  }
  cout<<"******    Calibration parameters written to Oracle\n";
  cout<<"****************************************************************\n";

  commit();
  pPar->setChanged(kFALSE);
  return version;
not_found:
  showSqlError("writePar(HRichCalPar*)");
  rollback();
  pPar->setChanged(kFALSE);
  return -1;
}
//************************************************************************

Int_t HRichParOraIo::createCalparVers(HRichCalPar* pPar) {
  // creates a new version for the calibration parameters
  // return the new version
  cout<<"---------------  "<<pPar->GetName()<<"   ---------------\n";
  if (strlen(pPar->getAuthor())==0) {
    Error("createCalparVers(...)",
          "author of calibration parameters not defined");
    return -1;
  }
  if (strlen(pPar->getDescription())==0) {
    Error("createCalparVers(...)",
          "descriction of calibration parameters not defined");
    return -1;
  }
  EXEC SQL BEGIN DECLARE SECTION;
    char* creator;
    char* descript;    
    int run;
    int context;
    int vers=-1;
  EXEC SQL END DECLARE SECTION;
  context = getContextId(pPar->IsA()->GetName(),pPar->getParamContext());
  if (context==-1) return -1;
  run=getActRunId();
  creator=(Char_t*)pPar->getAuthor();
  descript=(Char_t*)pPar->getDescription();
  EXEC SQL WHENEVER SQLERROR GOTO not_found;
  EXEC SQL WHENEVER NOT FOUND GOTO not_found;
  EXEC SQL EXECUTE
    BEGIN
      SELECT rich_ana.rich_cal_par_query.next_version INTO :vers FROM DUAL;
      INSERT INTO rich_ana.rich_cal_par_vers 
		(vers_id, orig_context_id, run_id, author, description)
         VALUES (:vers, :context, :run, :creator, :descript);
    END;
  END-EXEC;
  cout<<"******    Version cal_par_vers_id "<<vers<<" created\n";
  return vers;
not_found:
  showSqlError("createCalparVers()");
  return vers;
}
//************************************************************************