///////////////////////////////////////////////////////////////////////////////
// assumes libStntuple_oracle.so is loaded
///////////////////////////////////////////////////////////////////////////////
TGraph* gr;

int get_jobset(int RunNumber = 195409, const char* Pass = "17") {

  TString query;

  TString pass_name, retired;

  int  process_run;
  int  jobset, rc;

  TSQLResult* res;

  query  = "select  cps.passname, cps.retired, cus.process_run , cus.jobset ";
  query += "from used_sets cus, passes cps, passcalibs cpsc ";
  query += Form("where cus.process_run=%i and cus.process_name=cps.process_name ",RunNumber);
  query += "and cus.process_run >= cpsc.lorun ";
  query += "and cus.process_run <= cpsc.hirun ";
  query += "and cus.proc_calib_version=cpsc.proc_calib_version ";
  query += Form("and cpsc.pass_index=cps.pass_index and cps.passname='%s' and decode(cps.retired,NULL,'N','Y') != 'Y'",Pass) ;

  cdfofprd.Query(query.Data(),res);

  TOracleRow* row = (TOracleRow*) res->Next();

  if (row == 0) {
    rc = -1;
  }
  else {
    pass_name   = TCdfOracle::Strip(row->GetFieldData( 0));
    retired     = TCdfOracle::Strip(row->GetFieldData( 1));
    process_run = *((int*)          row->GetFieldData( 2));
    jobset      = *((int*)          row->GetFieldData( 3));

    rc = jobset;
  }


  delete res;

  return rc;
}


//-----------------------------------------------------------------------------
int get_calib_run(int RunNumber, const char* TableName, const char* Pass) {

  TString     query;
  TSQLResult* res;

  TString     system, calib_table;

  int jobset, calib_run, rc;

  jobset = get_jobset(RunNumber,Pass);

  if (jobset < 0) return -1;

  query  = "select ctp.system, crl.calib_run, crl.calib_table ";
  query += "from calibrunlists    crl, calib_properties ctp, set_run_maps csrm ";
  query += Form("where crl.cid=csrm.cid and csrm.jobset=%i ",jobset);
  query += Form("and crl.calib_table=ctp.calib_table and crl.calib_table='%s'",TableName);

  cdfofprd.Query(query.Data(),res);

  TOracleRow* row = (TOracleRow*) res->Next();

  if (row == 0) {
    rc = -1;
  }
  else {
    system      = TCdfOracle::Strip(row->GetFieldData( 0));
    calib_run   = *((int*)          row->GetFieldData( 1));
    system      = TCdfOracle::Strip(row->GetFieldData( 2));

    rc = calib_run;
  }


  delete res;
  return rc;

}

//-----------------------------------------------------------------------------
int get_n_tables(int RunNumber, const char* Pass="17", int debug=0) {

  TString     query;
  TSQLResult* res;

  TString     system, calib_table;

  int jobset, calib_run, n_tables, rc;

  jobset = get_jobset(RunNumber,Pass);

  if (jobset < 0) return -1;

  query  = "select ctp.system, crl.calib_run, crl.calib_table ";
  query += "from calibrunlists crl, calib_properties ctp, set_run_maps csrm ";
  query += Form("where crl.cid=csrm.cid and csrm.jobset=%i ",jobset);
  query += "and crl.calib_table=ctp.calib_table order by ctp.system";

  cdfofprd.Query(query.Data(),res);

  n_tables = 0;

  while ((row = (TOracleRow*) res->Next())) {
    if (row == 0) {
      rc = -1;
      printf (" rc = -1\n");
    }
    else {
      n_tables++;
      if (debug) {
	calib_table     = TCdfOracle::Strip(row->GetFieldData( 2));
	printf ("%s\n",calib_table.Data());
      }
	
    }
  }

  delete res;
  return n_tables;

}

struct CalibTableData_t {	 
  TString  name;
  int      cid;
  int      calib_run;
};



//-----------------------------------------------------------------------------
int get_table(const char* Name, int RunNumber, const char* Pass="17", 
	      CalibTableData_t* Table, int debug=0) {

  TString     query;
  TSQLResult* res;

  TString     system, calib_table;

  int jobset, calib_run, n_tables, rc(0);

  jobset = get_jobset(RunNumber,Pass);

  if (jobset < 0) return -1;

  query  = "select crl.cid, crl.calib_run, crl.calib_table ";
  query += "from calibrunlists crl, calib_properties ctp, set_run_maps csrm ";
  query += Form("where crl.cid=csrm.cid and csrm.jobset=%i ",jobset);
  query += Form("and crl.calib_table=ctp.calib_table and crl.calib_table=\'%s\'",Name);

  if (debug) { 
    printf("%s\n",query.Data());
  }

  cdfofprd.Query(query.Data(),res);

  while ((row = (TOracleRow*) res->Next())) {
    if (row == 0) {
      rc = -1;
      printf (" rc = -1\n");
      break;
    }
    else {
      Table->cid         = *((int*  ) row->GetFieldData(0));
      Table->calib_run   = *((int*  ) row->GetFieldData(1));
      Table->name        = TCdfOracle::Strip(row->GetFieldData(2));
    }
  }

  delete res;

  return 0;
}


//-----------------------------------------------------------------------------
int print_table(const char* Name, int MinRun, int MaxRun, const char* Pass="17") {

  TString        query;
  TSQLResult*    res;
  CalibTableData_t  table;


  int debug(0);

  TString     system, calib_table;

  int jobset, calib_run, n_tables, rn, rc;

  TObjArray* list_of_runs  = new TObjArray(10000);
  TStnRunSummary*   rs;

  int flag = cdfofprd.ForceDisconnect();
  cdfofprd.SetForceDisconnect(0);
  cdfofprd.GetListOfGoodRuns  (list_of_runs,MinRun,MaxRun);

  int nruns = list_of_runs->GetEntriesFast();

  float* run = new float[nruns];
  float* cid = new float[nruns];

  int npt = 0;

  for (int i=0; i<nruns; i++) {
    rs = (TStnRunSummary*) list_of_runs->At(i);
    if (rs) rn = rs->RunNumber();

    get_table(Name,rn,Pass,&table);

    printf("%6i  %i  %i  %s\n",rn,table.cid,table.calib_run,table.name.Data());

    run[npt] = rn;
    cid[npt] = table.cid;
    npt++;
  }

  gr = new TGraph(npt,run,cid);

  char  title[200];
  printf("%s : CID vs run number (%i-%i)",Name,MinRun,MaxRun);

  TCanvas* c = new_slide("a","a",1,1,600,400);
  TPad* p1 = (TPad*) c->GetPrimitive("p1");

  p1->cd(1);

  gr->SetTitle(title);
  gr->Draw("PAL");

  cdfofprd.SetForceDisconnect(flag);

  return 0;
}


//-----------------------------------------------------------------------------
float glumi;
TStnRunSummary*   grs;


struct BeamlineData_t {	 
  int    cid;
  int    channelid;
  double x; 
  double y; 
  int    flag0;
  int    flag1;
  int    rc;
};


//_____________________________________________________________________________
int get_beamline(const char*     Detector, 
		 int             RunNumber, 
		 const char*     Pass="17", 
		 BeamlineData_t* Data, 
		 int             debug=0) {

  // detector = "svx" or "cot"

  TString     query;
  TSQLResult* res;
  TStnRunSummary rs;

  TString     cmd, system, calib_table;
  TObjArray   data(100);
  TObjString *ostr;

  int         jobset, cid, channelid, calib_run, n_tables, rc(0), flag0, flag1;
  float       beamx, beamy;

  char*       line;
  char        buf[1000];

  jobset = get_jobset(RunNumber,Pass);

  cdfofprd.GetRunSummary(RunNumber,&rs);

  //  if (debug) printf(" run, jobset = %i %i\n",RunNumber, jobset);

  if (jobset < 0) return -1;

  cmd = Form(". cdfopr/scripts/db_queries; get_beamline %s  %i %s",Detector,RunNumber,Pass);
  
  FILE* pipe = gSystem->OpenPipe(cmd.Data(),"r");

  while (fgets(buf,10000,pipe)) { data.Add(new TObjString(buf)); }
  gSystem->ClosePipe(pipe);

  TIter itt(&data);
  while (ostr = (TObjString*) itt.Next()) {
    line = (char*) ostr->String().Data();
    //    printf("%s\n",line);
    sscanf(line,"%i %i %lf %lf %i %i", 
	   &Data->cid,&Data->channelid,&Data->x,&Data->y,&Data->flag0,&Data->flag1);
    
    Data->rc           = Data->flag0;

    if ((Data->flag0 < 1.e6) || (debug != 0)) {
      printf("%3s %7i %10i %10.3f %2i %2i %2i %7i %7i %10.5f %10.5f 0x%08x 0x%08x %20s\n",
	     Detector, RunNumber, jobset,
	     rs.LumiTape(), 
	     rs.SvxStatusBit(), rs.IslStatusBit(), rs.CotStatusBit(),
	     Data->cid, Data->channelid, Data->x, Data->y, Data->flag0, Data->flag1, 
	     rs.TriggerTableName().Data());
    }
  }

  return Data->rc;

}
//_____________________________________________________________________________
int get_whaler(int RunNumber, const char* Table, const char* Pass, 
	       int* cid, int* geomid, float* response, float* sigma,
	       int debug=0) {
  // Table: WHALINERESPONSE or WHAOFFLER
  // call: 
  // int   cid     [10000], geomid[10000];
  // float response[10000], sigma [10000];

  // get_whaler(168889,"WHAOFFLER","17",cid,geomid,response,sigma,1)

  TString     query;
  TSQLResult* res;
  TStnRunSummary rs;

  TString     system, calib_table;

  int         jobset, calib_run, nchannels(0), rc(0);

  jobset = get_jobset(RunNumber,Pass);

  cdfofprd.GetRunSummary(RunNumber,&rs);

  if (debug) printf(" run, jobset = %i %i\n",RunNumber, jobset);

  if (jobset < 0) return -1;

  query  = "select wha.cid, wha.geomid, wha.response, wha.deltaresp ";
  query += Form("from %s wha, set_run_maps csrm ",Table);
  query += Form("where wha.cid=csrm.cid  and  csrm.jobset=%i",jobset);

  cdfofprd.Query(query.Data(),res);

  while ((row = (TOracleRow*) res->Next())) {
    cid      [nchannels]  = *((int*  ) row->GetFieldData(0));
    geomid   [nchannels]  = *((int*  ) row->GetFieldData(1));
    response [nchannels]  = *((float*) row->GetFieldData(2));
    sigma    [nchannels]  = *((float*) row->GetFieldData(3));
    
    if (debug != 0) {
      printf ("run, cid, geomid, response, sigma = %7i %7i %7i %10.5f %10.5f\n",
	      RunNumber, cid[nchannels],geomid[nchannels],response[nchannels],
	      sigma[nchannels]);
    }
    nchannels++;
  }

  printf("nchannels = %5i\n", nchannels);

  delete res;
  return rc;

}

//-----------------------------------------------------------------------------
// check SVX alignment, tables are:
//
// SIALIGNBARREL
// SIALIGNFRAME
// SIALIGNLADDER
// SIALIGNWAFER
// SIALIGNWARP
//-----------------------------------------------------------------------------
int plot_svx_alignment_run(const char* Table, 
			  int         MinRun, 
			  int         MaxRun, 
			  const char* Pass) 
{
  int rn, calib_run;

  TObjArray* list_of_runs         = new TObjArray(10000);
  TStnRunSummary*   rs;

  int flag = cdfofprd.ForceDisconnect();
  cdfofprd.SetForceDisconnect(0);
  cdfofprd.GetListOfGoodRuns  (list_of_runs,MinRun,MaxRun);

  int nruns = list_of_runs->GetEntriesFast();

  float* x = new float[nruns];
  float* y = new float[nruns];

  for (int i=0; i<nruns; i++) {
    rs = (TStnRunSummary*) list_of_runs->At(i);
    if (rs) {
      rn = rs->RunNumber();

      calib_run = get_calib_run(rn,Table,Pass);
      if (calib_run >=0) printf("run, calib_run = %7i %7i\n",rn,calib_run);
      x[i] = rn;
      y[i] = calib_run;
    }
    else {
      printf("i = %i\n",i);
    }
  }
  printf("Total number of runs = %7i\n",nruns);

  gr = new TGraph(nruns,x,y);
  gr->SetMarkerStyle(20);
  gr->SetMarkerSize(1);
  gr->GetXaxis()->SetLabelSize(0.02);

  gr->Draw("AP");

}


//_____________________________________________________________________________
TGraph* g_n_tables;

int plot_n_tables(int MinRun, int MaxRun, const char* Pass="17") {

  int rn, n_tables;

  TObjArray* list_of_runs         = new TObjArray(10000);
  TStnRunSummary*   rs;

  int flag = cdfofprd.ForceDisconnect();
  cdfofprd.SetForceDisconnect(0);
  cdfofprd.GetListOfGoodRuns  (list_of_runs,MinRun,MaxRun);

  int nruns = list_of_runs->GetEntriesFast();

  float* x = new float[nruns];
  float* y = new float[nruns];

  for (int i=0; i<nruns; i++) {
    rs = (TStnRunSummary*) list_of_runs->At(i);
    if (rs) {
      rn = rs->RunNumber();

      n_tables = get_n_tables(rn,Pass);

      if (n_tables >=0) printf("run, n_tables = %7i %7i\n",rn,n_tables);

      x[i] = rn;
      y[i] = n_tables;
    }
    else {
      printf("i = %i\n",i);
    }
  }

  g_n_tables = new TGraph(nruns,x,y);
  g_n_tables->SetMarkerStyle(20);
  g_n_tables->SetMarkerSize(1);
  g_n_tables->GetXaxis()->SetLabelSize(0.02);

  g_n_tables->Draw("AP");

}


int    npt     = 0;  
float* run     = 0;

float* cot_rc  = 0;
float* cot_x   = 0;
float* cot_y   = 0;
float* cot_cid = 0;

float* svx_rc  = 0;
float* svx_x   = 0;
float* svx_y   = 0;
float* svx_cid = 0;

float* dx      = 0;
float* dy      = 0;

TGraph* g_beamline_fit;

//_____________________________________________________________________________
int fill_beamlines(int MinRun, int MaxRun, const char* Pass="17",int Debug=0) {
  int rn, cot_qual, svx_qual;

  BeamlineData_t   cot_beam;
  BeamlineData_t   svx_beam;

  TObjArray* list_of_runs         = new TObjArray(10000);

  int flag = cdfofprd.ForceDisconnect();
  cdfofprd.SetForceDisconnect(0);
  cdfofprd.GetListOfGoodRuns  (list_of_runs,MinRun,MaxRun);

  int nruns = list_of_runs->GetEntriesFast();

  if (run) {
    delete [] run;

    delete [] cot_rc;
    delete [] cot_x;
    delete [] cot_y;
    delete [] cot_cid;

    delete [] svx_rc;
    delete [] svx_x;
    delete [] svx_y;
    delete [] svx_cid;

    delete [] dx;
    delete [] dy;
  }

  // these are globals...

  run     = new float[nruns];

  cot_rc  = new float[nruns];
  cot_x   = new float[nruns];
  cot_y   = new float[nruns];
  cot_cid = new float[nruns];

  svx_rc  = new float[nruns];
  svx_x   = new float[nruns];
  svx_y   = new float[nruns];
  svx_cid = new float[nruns];

  dx      = new float[nruns];
  dy      = new float[nruns];

  npt=0;
  for (int i=0; i<nruns; i++) {
    grs = (TStnRunSummary*) list_of_runs->At(i);
    if (grs && 
	(grs->TriggerTableName().Index("PHYSICS") >= 0) && 
	(grs->TriggerTableName().Index("TEST")    <  0)    ) {

      rn = grs->RunNumber();
      glumi = grs->LumiTape();

      cot_qual = get_beamline("cot",grs->RunNumber(),Pass,&cot_beam,1);
      svx_qual = get_beamline("svx",grs->RunNumber(),Pass,&svx_beam,1);

      if (Debug) {
	printf("run, cot_qual, svx_qual = %7i %7i %7i\n",rn,cot_qual,svx_qual);
      }

      run    [npt] = rn;

      cot_rc [npt] = cot_beam.rc;
      cot_x  [npt] = cot_beam.x;
      cot_y  [npt] = cot_beam.y;
      cot_cid[npt] = cot_beam.cid;

      svx_rc [npt] = svx_beam.rc;
      svx_x  [npt] = svx_beam.x;
      svx_y  [npt] = svx_beam.y;
      svx_cid[npt] = svx_beam.cid;

      dx     [npt] = cot_beam.x-svx_beam.x;
      dy     [npt] = cot_beam.y-svx_beam.y;

      npt++;
    }
    else {
      if (grs == 0) printf("i = %i\n",i);
    }
  }

  return 0;
}

//_____________________________________________________________________________
int plot_beamlines(const char* Plot) {

  // detector = "svx" or "cot"
  // 'Plot' = "cot_fit_code", "svx_fit_code", "cot_x","svx_x","cot_y","svx_y","dx","dy"

  if      (strcmp(Plot,"cot_fit_code") == 0) g_beamline_fit = new TGraph(npt,run,cot_rc);
  else if (strcmp(Plot,"svx_fit_code") == 0) g_beamline_fit = new TGraph(npt,run,svx_rc);

  else if (strcmp(Plot,"cot_x")        == 0) g_beamline_fit = new TGraph(npt,run,cot_x );
  else if (strcmp(Plot,"cot_y")        == 0) g_beamline_fit = new TGraph(npt,run,cot_y );
  else if (strcmp(Plot,"cot_cid")      == 0) g_beamline_fit = new TGraph(npt,run,cot_cid);

  else if (strcmp(Plot,"svx_x")        == 0) g_beamline_fit = new TGraph(npt,run,svx_x );
  else if (strcmp(Plot,"svx_y")        == 0) g_beamline_fit = new TGraph(npt,run,svx_y );
  else if (strcmp(Plot,"svx_cid")      == 0) g_beamline_fit = new TGraph(npt,run,svx_cid);

  else if (strcmp(Plot,"dx")           == 0) g_beamline_fit = new TGraph(npt,run,dx    );
  else if (strcmp(Plot,"dy")           == 0) g_beamline_fit = new TGraph(npt,run,dy    );

  g_beamline_fit->SetTitle(Plot);

  g_beamline_fit->SetMarkerStyle(20);
  g_beamline_fit->SetMarkerSize(1);
  g_beamline_fit->GetXaxis()->SetLabelSize(0.02);

  g_beamline_fit->Draw("AP");

  cdfofprd.SetForceDisconnect(flag);
  cdfofprd.Disconnect();

}


//------------------------------------------------------------------------------
int plot_init_lumi(int MinRun, int MaxRun) {
  // detector = "svx" or "cot"

  TGraph* g_init_lumi;

  int    rn, qual;
  float  lumi;
  int    debug(0);

  TObjArray* list_of_runs         = new TObjArray(10000);

  int flag = cdfofprd.ForceDisconnect();
  cdfofprd.SetForceDisconnect(0);
  cdfofprd.GetListOfGoodRuns  (list_of_runs,MinRun,MaxRun);

  int nruns = list_of_runs->GetEntriesFast();

  float* x = new float[nruns];
  float* y = new float[nruns];

  int npt=0;
  for (int i=0; i<nruns; i++) {
    grs = (TStnRunSummary*) list_of_runs->At(i);
    if (grs && 
	(grs->TriggerTableName().Index("PHYSICS") >= 0) && 
	(grs->TriggerTableName().Index("TEST")    <  0)    ) {

      rn   = grs->RunNumber();
      lumi = grs->B0InitLumi();

      x[npt] = rn;
      y[npt] = lumi;
      npt++;
    }
    else {
      if (grs == 0) printf("i = %i\n",i);
    }
  }

  g_init_lumi = new TGraph(npt,x,y);
  g_init_lumi->SetTitle("Initial Luminosity");
  g_init_lumi->SetMarkerStyle(20);
  g_init_lumi->SetMarkerSize(1);
  g_init_lumi->GetXaxis()->SetLabelSize(0.02);

  g_init_lumi->Draw("AP");

  cdfofprd.SetForceDisconnect(flag);
  cdfofprd.Disconnect();

  return 0;
}