#!/usr/local/bin/perl

use DBI;
use DBD::Pg;

@types = ("L0C0","L0C1","L1C0","L1C1","L2C0","L2C1","L3C0","L3C1","L4C0","L4C1","L5C0","L5C1");
$dbh = DBI->connect("dbi:Pg:dbname=gateDB","alice"); # database connection
if(!dbh){print "ERROR: Cannot connect to database $dbname!\n"; exit;}

foreach $roctype (@types){
  $outfile = ">>DATA/".$roctype.".out";
  open(OUTFILE,$outfile) or die "ERROR: Cannot open $outfile!\n";
  for($rocnum=0; $rocnum<100; $rocnum++){
    print OUTFILE "$roctype $rocnum ";
    print OUTFILE gasleak($roctype,$rocnum);
    print OUTFILE condition($roctype,$rocnum);
    print OUTFILE gainunif($roctype,$rocnum);
    print OUTFILE absgain($roctype,$rocnum);
    print OUTFILE spectral($roctype,$rocnum);
    print OUTFILE "\n";
  } # rocnum loop
  close(OUTFILE); undef($outfile);
} # roctype loop

# gas leak summary parameters:
sub gasleak {
  my($type,$num) = @_;
  my $SQL = "SELECT high_leak,conductance FROM roc_test_head_leak WHERE roc_type=? AND roc_serial=?";
  my $sth = $dbh->prepare($SQL); $sth->execute($type,$num);
  my @row = $sth->fetchrow_array;
  my $gasleak=-1; if(defined($row[0])) {$gasleak=$row[0];}
  my $conduct=-1; if(defined($row[1])) {$conduct=$row[1];}
  my $result = $gasleak." ".$conduct." ";
  return $result;
}

# conditioning summary parameters:
sub condition {
  my($type,$num) = @_;
  my $SQL = "SELECT max(relative_time) FROM roc_test_data_cond WHERE roc_type=? AND roc_serial=?";
  my $sth = $dbh->prepare($SQL); $sth->execute($type,$num);
  my @row = $sth->fetchrow_array;
  my $maxtime=-1; my $lastcurr=-1;
  if(defined($row[0])){
    $maxtime=$row[0];
    $SQL = "SELECT anode_dark_current FROM roc_test_data_cond WHERE ";
    $SQL .= "roc_type=? AND roc_serial=? AND relative_time=?";
    my $sth2 = $dbh->prepare($SQL); $sth2->execute($type,$num,$maxtime);
    @row = $sth2->fetchrow_array;
    if(defined($row[0])){$lastcurr=$row[0];}
  }
  my $result = $maxtime." ".$lastcurr." ";
  return $result;
}

# gain uniformity summary parameters:
sub gainunif {
  my($type,$num) = @_;
  my $SQL = "SELECT sigma_curr,max_dev_curr,mean_curr FROM roc_test_head_guni WHERE roc_type=? AND roc_serial=?";
  my $sth = $dbh->prepare($SQL); $sth->execute($type,$num);
  my @row = $sth->fetchrow_array;
  my $sigma=-1;  if(defined($row[0])) {$sigma=$row[0];}
  my $maxdev=-1; if(defined($row[1])) {$maxdev=$row[1];}
  my $mean=-1;   if(defined($row[2])) {$mean=$row[2];}
  my $result = $sigma." ".$maxdev." ".$mean." ";
  return $result; 
}

# absolute gain summary parameters:
sub absgain {
  my($type,$num) = @_;
  my $SQL = "SELECT gas_gain FROM roc_test_data_gabs WHERE roc_type=? AND roc_serial=?";
  my $sth = $dbh->prepare($SQL); $sth->execute($type,$num);
  my @row; my @gain = (-1,-1,-1,-1); my $idx=0;
  while(@row = $sth->fetchrow_array){
    if($idx>3) {next;}
    if(defined($row[0])) {$gain[$idx]=$row[0];}
    $idx++;
  }
  my $result = $gain[0]." ".$gain[1]." ".$gain[2]." ".$gain[3]." ";
  return $result; 
}

# spectral summary parameters:
sub spectral {
  my($type,$num) = @_;
  my $SQL = "SELECT sigma FROM roc_test_head_spec WHERE roc_type=? AND roc_serial=?";
  my $sth = $dbh->prepare($SQL); $sth->execute($type,$num);
  my @row = $sth->fetchrow_array;
  my $sigma=-1;  if(defined($row[0])) {$sigma=$row[0];}
  my $result = $sigma;
  return $result; 
}