#!/usr/local/bin/perl

use DBI;
use DBD::Pg;

if(!defined($ARGV[0]) || !defined($ARGV[1])){
  print "Usage: ./extract_data database ChamberType [ChamberNumber]\n";
  print "Example 1: './extract_data gateDB L1C1' extracts test results of all L1C1 ROCs and inserts them into gateDB\n";
  print "Example 2: './extract_data play L1C1 13' extracts test results of L1C1 ROC #13 and inserts them into playDB\n";
  exit;
}

# ALL GLOBALS HERE:
$dbname = $ARGV[0];
$roc_type = $ARGV[1];
$roc_num = -1;
if(defined($ARGV[2])) {$roc_num=int($ARGV[2]);}
$testdir = "/alice1/trd/TestROC/".$roc_type."/";
$plotdir = "/alice1/www/html/trd-db-files/".$roc_type."/";
$logfile = ">>extract_data.log";
open(LOGFILE,$logfile) or die "ERROR: Cannot open $logfile!\n";

# database connection:
$dbh = DBI->connect("dbi:Pg:dbname=$dbname","alice");
if(!dbh){print LOGFILE "ERROR: Cannot connect to database $dbname!\n"; exit;}
#$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options",
#                    "$username","$password",{AutoCommit=>0,RaiseError=>1,PrintError=>0});

if($roc_num>=0) {
  extract_roc($roc_type,$roc_num);
} else {
  for($i=0; $i<100; $i++) {
    extract_roc($roc_type,$i);
  }
}

#########################################################
# main subroutine - calls other subroutines to do the job
#########################################################
sub extract_roc {
  my($type,$num) = @_;
  my $numplots;

  $idx = $num;
  if($num<10) {$idx = "00".$num;}
  elsif($num>9&&$num<100) {$idx = "0".$num;}
  my $datadir = $testdir.$idx;
  my @allfiles = `ls $datadir/*.txt`;
  my $numfiles = scalar(@allfiles);
  if($numfiles<1) {print LOGFILE "\n ROC $type-$idx has empty data directory. Skipping\n"; return;}

  print LOGFILE "\n*** Starting ROC $type-$idx processing...\n";
  
  check_roc($type,$num);

  my $filebase = $datadir."/".$type."#".$idx."g_";
  if(check_table($type,$num,"leak")){
    print LOGFILE "ROC $type-$idx leak data already inserted. Skipping\n";
  } else {
    $numplots = check_plots($idx,"leak");
    if($numplots<2) {print LOGFILE "WARNING: ROC $type-$idx has only $numplots leak plots\n";}
    process_leak_data($filebase,$type,$num);
  }
  if(check_table($type,$num,"cond")){
    print LOGFILE "ROC $type-$idx cond data already inserted. Skipping\n";
  } else {
    $numplots = check_plots($idx,"cond");
    if($numplots<2) {print LOGFILE "WARNING: ROC $type-$idx has only $numplots cond plots\n";}
    process_cond_data($filebase,$type,$num);
  }
  if(check_table($type,$num,"guni")){
    print LOGFILE "ROC $type-$idx guni data already inserted. Skipping\n";
  } else {
    $numplots = check_plots($idx,"guni");
    if($numplots<2) {print LOGFILE "WARNING: ROC $type-$idx has only $numplots guni plots\n";}
    process_guni_data($filebase,$type,$num);
  }
  if(check_table($type,$num,"gabs")){
    print LOGFILE "ROC $type-$idx gabs data already inserted. Skipping\n";
  } else {
    $numplots = check_plots($idx,"gabs");
    if($numplots<2) {print LOGFILE "WARNING: ROC $type-$idx has only $numplots gabs plots\n";}
    process_gabs_data($filebase,$type,$num);
  }
  if(check_table($type,$num,"spec")){
    print LOGFILE "ROC $type-$idx spec data already inserted. Skipping\n";
  } else {
    $numplots = check_plots($idx,"spec");
    if($numplots<1) {print LOGFILE "WARNING: ROC $type-$idx has no spec plots\n";}
    process_spec_data($filebase,$type,$num);
  }
  if(check_table($type,$num,"stab")){
    print LOGFILE "ROC $type-$idx stab data already inserted. Skipping\n";
  } else {
    $numplots = check_plots($idx,"stab");
    if($numplots<7) {print LOGFILE "WARNING: ROC $type-$idx has only $numplots stab plots\n";}
    process_stab_data($filebase,$type,$num);
  }
}

####################################################################################
# checks if this roc exists in the roc table; if not, add it with 'unknown' location
####################################################################################
sub check_roc {
  my($type,$num) = @_;
  my $SQL1 = "SELECT roc_type, roc_serial FROM roc WHERE roc_type=? AND roc_serial=?";
  my $sth1 = $dbh->prepare($SQL1);
  $sth1->execute($type,$num);
  my @row = $sth1->fetchrow_array;
  if(scalar(@row)<1){
    print LOGFILE "ROC $type-$num not found in roc table! Adding it...\n";
    my $SQL2 = "INSERT INTO roc (roc_type,roc_serial,loc_id,person) VALUES (?,?,?,?)";
    my $sth2 = $dbh->prepare($SQL2);
    $sth2->execute($type,$num,8,"PC"); # 'unknown' location has code 8
    if($sth2->rows<1) {print LOGFILE "ERROR: chamber addition to roc table failed! Exiting.\n"; exit;}
  }
}

##########################################################
# checks if this roc already exists in the test head table
##########################################################
sub check_table {
  my($type,$num,$table) = @_;
  my $SQL = "SELECT roc_type, roc_serial FROM roc_test_head_".$table." WHERE roc_type=? AND roc_serial=?";
  my $sth = $dbh->prepare($SQL);
  $sth->execute($type,$num);
  my @row = $sth->fetchrow_array;
  if(scalar(@row)>0) {return 1;}
  return 0;
}

########################################################
# check the plot files; return the number of files found
########################################################
sub check_plots {
  my($num,$name) = @_;
  my $inplot = $plotdir.$num."-".$name."*.gif";
  my @allfiles = `ls $inplot`;
  my $nfiles = scalar(@allfiles);

  return $nfiles;
}

##################################################################################
# extracts data from leak rate test files (par and data) and insert it into gateDB
##################################################################################
sub process_leak_data {
  my($filebase,$roc_type,$roc_serial) = @_;
  my $SQL; my $sth;

  #################################################
  # read in and insert the leak test parameter file
  #################################################
  $SQL = "INSERT INTO roc_test_head_leak (roc_type,roc_serial) VALUES (?,?)";
  $sth = $dbh->prepare($SQL); $sth->execute($roc_type,$roc_serial);
  $where = "WHERE roc_type='".$roc_type."' AND roc_serial='".$roc_serial."'";
  if($sth->rows<1) {print LOGFILE "ERROR: leak test parameters insertion from $parfile failed! Returning.\n"; return;}  
  my $parfile = $filebase."leak_par.txt";
  if(!(-e $parfile)) {print LOGFILE "WARNING: file $parfile does not exist!\n";}
  else {
    open(PARFILE,$parfile) or die "ERROR: Cannot open $parfile!\n";
    while(<PARFILE>){
      if($_=~/^operator.*:(.*)$/) {$p=$1; insert_par("operator",\$p,"leak",$where);}
      elsif($_=~/^creator\slab.*:(.*)$/) {$p=$1; insert_par("institute",\$p,"leak",$where);}
      elsif($_=~/^gas\smixture\s?:(.*)$/) {$p=$1; insert_par("gas_mixture",\$p,"leak",$where);}
      elsif($_=~/^flow\srate.*:(.*)$/) {$p=$1; insert_par("flow_rate",\$p,"leak",$where);}
      elsif($_=~/^time\sat\sstart\s?:(.*)$/) {$p=$1; insert_par("start_time",\$p,"leak",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_oxy",\$p,"leak",$where);}
      elsif($_=~/^temperature\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_temp",\$p,"leak",$where);}
      elsif($_=~/^pressure\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_pres",\$p,"leak",$where);}
      elsif($_=~/^high\sgas\sflow.*:(.*)$/) {$p=$1; insert_par("high_gas",\$p,"leak",$where);}
      elsif($_=~/^leakrate\shigh\sflow.*:(.*)$/) {$p=$1; insert_par("high_leak",\$p,"leak",$where);}
      elsif($_=~/^low\sgas\sflow.*:(.*)$/) {$p=$1; insert_par("low_gas",\$p,"leak",$where);}
      elsif($_=~/^leakrate\slow\sflow.*:(.*)$/) {$p=$1; insert_par("low_leak",\$p,"leak",$where);}
      elsif($_=~/^time\sat\send\s?:(.*)$/) {$p=$1; insert_par("end_time",\$p,"leak",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\send.*:(.*)$/) {$p=$1; insert_par("end_oxy",\$p,"leak",$where);}
      elsif($_=~/^temperature\sat\send.*:(.*)$/) {$p=$1; insert_par("end_temp",\$p,"leak",$where);}
      elsif($_=~/^pressure\sat\send.*:(.*)$/) {$p=$1; insert_par("end_pres",\$p,"leak",$where);}
      elsif($_=~/^comment.*:(.*)$/) {$p=$1; insert_par("comment",\$p,"leak",$where);}
      else {print LOGFILE "Unidentified leak parameter in:$_";}
    }
    close(PARFILE); undef($SQL); undef($sth);
  }

  ############################################
  # read in and insert the leak test data file
  ############################################
  my $datafile = $filebase."leak_data.txt";
  if(!(-e $datafile)) {print LOGFILE "WARNING: file $datafile does not exist!\n";}
  else {
    open(DATAFILE,$datafile) or die "ERROR: Cannot open $datafile!\n";
    my $counter = 0; 
    $SQL = "INSERT INTO roc_test_data_leak (roc_type,roc_serial,relative_time,oxygen_conc) VALUES (?,?,?,?)";
    $sth = $dbh->prepare($SQL);
    while(<DATAFILE>){
      $counter++;
      $_=~/^(.{1,9})\s(.{1,9})$/;
      $rel_time = $1; $oxy_conc = $2;
      trim(\$rel_time); trim(\$oxy_conc);
      $sth->execute($roc_type,$roc_serial,$rel_time,$oxy_conc);
      if($sth->rows<1) {print LOGFILE "ERROR: leak test data insertion from $datafile failed at line $counter!\n"; return;}
    }
    close(DATAFILE); undef($SQL); undef($sth);
  }
}

#####################################################################################
# extracts data from conditioning test files (par and data) and insert it into gateDB
#####################################################################################
sub process_cond_data {
  my($filebase,$roc_type,$roc_serial) = @_;
  my $SQL; my $sth;
  
  #########################################################
  # read in and insert the conditioning test parameter file
  #########################################################
  $SQL = "INSERT INTO roc_test_head_cond (roc_type,roc_serial) VALUES (?,?)";
  $sth = $dbh->prepare($SQL); $sth->execute($roc_type,$roc_serial);
  $where = "WHERE roc_type='".$roc_type."' AND roc_serial='".$roc_serial."'";
  if($sth->rows<1) {print LOGFILE "ERROR: cond test parameters insertion from $parfile failed! Returning.\n"; return;}  
  my $parfile = $filebase."cond_par.txt";
  if(!(-e $parfile)) {print LOGFILE "WARNING: file $parfile does not exist!\n";}
  else {
    open(PARFILE,$parfile) or die "ERROR: Cannot open $parfile!\n";
    while(<PARFILE>){
      if($_=~/^operator.*:(.*)$/) {$p=$1; insert_par("operator",\$p,"cond",$where);}
      elsif($_=~/^creator\slab.*:(.*)$/) {$p=$1; insert_par("institute",\$p,"cond",$where);}
      elsif($_=~/^gas\smixture\s?:(.*)$/) {$p=$1; insert_par("gas_mixture",\$p,"cond",$where);}
      elsif($_=~/^flow\srate.*:(.*)$/) {$p=$1; insert_par("flow_rate",\$p,"cond",$where);}
      elsif($_=~/^time\sat\sstart\s?:(.*)$/) {$p=$1; insert_par("start_time",\$p,"cond",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_oxy",\$p,"cond",$where);}
      elsif($_=~/^temperature\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_temp",\$p,"cond",$where);}
      elsif($_=~/^pressure\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_pres",\$p,"cond",$where);}
      elsif($_=~/^drift\svoltage.*:(.*)$/) {$p=$1; insert_par("drift_volt",\$p,"cond",$where);}
      elsif($_=~/^low\sthreshold\scurrent.*:(.*)$/) {$p=$1; insert_par("low_curr",\$p,"cond",$where);}
      elsif($_=~/^high\sthreshold\scurrent.*:(.*)$/) {$p=$1; insert_par("high_curr",\$p,"cond",$where);}
      elsif($_=~/^trip\scurrent.*:(.*)$/) {$p=$1; insert_par("trip_curr",\$p,"cond",$where);}
      elsif($_=~/^number\sof\strips.*:(.*)$/) {$p=$1; insert_par("trip_num",\$p,"cond",$where);}
      elsif($_=~/^time\sat\send\s?:(.*)$/) {$p=$1; insert_par("end_time",\$p,"cond",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\send.*:(.*)$/) {$p=$1; insert_par("end_oxy",\$p,"cond",$where);}
      elsif($_=~/^temperature\sat\send.*:(.*)$/) {$p=$1; insert_par("end_temp",\$p,"cond",$where);}
      elsif($_=~/^pressure\sat\send.*:(.*)$/) {$p=$1; insert_par("end_pres",\$p,"cond",$where);}
      elsif($_=~/^comment.*:(.*)$/) {$p=$1; insert_par("comment",\$p,"cond",$where);}
      else {print LOGFILE "Unidentified cond parameter in:$_";}
    }
    close(PARFILE); undef($SQL); undef($sth);
  }

  ####################################################
  # read in and insert the conditioning test data file
  ####################################################
  my $datafile = $filebase."cond_data.txt";
  if(!(-e $datafile)) {print LOGFILE "WARNING: file $datafile does not exist!\n";}
  else {
    open(DATAFILE,$datafile) or die "ERROR: Cannot open $datafile!\n";
    my $counter = 0;
    $SQL = "INSERT INTO roc_test_data_cond ";
    $SQL .= "(roc_type,roc_serial,relative_time,anode_voltage,anode_dark_current) VALUES (?,?,?,?,?)";
    $sth = $dbh->prepare($SQL);
    while(<DATAFILE>){
      $counter++;
      $_=~/^(.{1,9})\s(.{1,9})\s(.{1,9})$/;
      $rel_time = $1; $anode_volt = $2; $anode_curr = $3;
      trim(\$rel_time); trim(\$anode_volt); trim(\$anode_curr);
      $sth->execute($roc_type,$roc_serial,$rel_time,$anode_volt,$anode_curr);
      if($sth->rows<1) {print LOGFILE "ERROR: conditioning data insertion from $datafile failed at line $counter!\n"; return;}
    }
    close(DATAFILE); undef($SQL); undef($sth);
  }
}

########################################################################################
# extracts data from gain uniformity test files (par and data) and insert it into gateDB
########################################################################################
sub process_guni_data {
  my($filebase,$roc_type,$roc_serial) = @_;
  my $SQL; my $sth; my $counter;
  
  #########################################################
  # read in and insert the conditioning test parameter file
  #########################################################
  $SQL = "INSERT INTO roc_test_head_guni (roc_type,roc_serial) VALUES (?,?)";
  $sth = $dbh->prepare($SQL); $sth->execute($roc_type,$roc_serial);
  $where = "WHERE roc_type='".$roc_type."' AND roc_serial='".$roc_serial."'";
  if($sth->rows<1) {print LOGFILE "ERROR: guni test parameters insertion from $parfile failed! Returning.\n"; return;}  
  my $parfile = $filebase."guni_par.txt";
  if(!(-e $parfile)) {print LOGFILE "WARNING: file $parfile does not exist!\n";}
  else {
    open(PARFILE,$parfile) or die "ERROR: Cannot open $parfile!\n";
    while(<PARFILE>){
      if($_=~/^operator.*:(.*)$/) {$p=$1; insert_par("operator",\$p,"guni",$where);}
      elsif($_=~/^creator\slab.*:(.*)$/) {$p=$1; insert_par("institute",\$p,"guni",$where);}
      elsif($_=~/^gas\smixture\s?:(.*)$/) {$p=$1; insert_par("gas_mixture",\$p,"guni",$where);}
      elsif($_=~/^flow\srate.*:(.*)$/) {$p=$1; insert_par("flow_rate",\$p,"guni",$where);}
      elsif($_=~/^time\sat\sstart\s?:(.*)$/){$p=$1; insert_par("start_time",\$p,"guni",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_oxy",\$p,"guni",$where);}
      elsif($_=~/^temperature\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_temp",\$p,"guni",$where);}
      elsif($_=~/^pressure\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_pres",\$p,"guni",$where);}
      elsif($_=~/^anode\svoltage.*:(.*)$/) {$p=$1; insert_par("anode_volt",\$p,"guni",$where);}
      elsif($_=~/^drift\svoltage.*:(.*)$/) {$p=$1; insert_par("drift_volt",\$p,"guni",$where);}
      elsif($_=~/^dark\scurrent\sstart.*:(.*)$/) {$p=$1; insert_par("start_curr",\$p,"guni",$where);}
      elsif($_=~/^dark\scurrent\send.*:(.*)$/) {$p=$1; insert_par("end_curr",\$p,"guni",$where);}
      elsif($_=~/^source.*:(.*)$/) {$p=$1; insert_par("source",\$p,"guni",$where);}
      elsif($_=~/^phi\soffset\s1d.*:(.*)$/) {$p=$1; insert_par("phi_offset",\$p,"guni",$where);}
      elsif($_=~/^number\spoints\sz-dir.*:(.*)$/) {$p=$1; insert_par("z_num_pts",\$p,"guni",$where);}
      elsif($_=~/^number\spoints\sphi-dir.*:(.*)$/) {$p=$1; insert_par("phi_num_pts",\$p,"guni",$where);}
      elsif($_=~/^mean\scurrent\s2d.*:(.*)$/) {$p=$1; insert_par("mean_curr",\$p,"guni",$where);}
      elsif($_=~/^stand\sdeviation\scurrent\s2d.*:(.*)$/) {$p=$1; insert_par("sigma_curr",\$p,"guni",$where);}
      elsif($_=~/^max\sdeviation\scurrent\s2d.*:(.*)$/) {$p=$1; insert_par("max_dev_curr",\$p,"guni",$where);}
      elsif($_=~/^time\sat\send\s?:(.*)$/) {$p=$1; insert_par("end_time",\$p,"guni",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\send.*:(.*)$/) {$p=$1; insert_par("end_oxy",\$p,"guni",$where);}
      elsif($_=~/^temperature\sat\send.*:(.*)$/) {$p=$1; insert_par("end_temp",\$p,"guni",$where);}
      elsif($_=~/^pressure\sat\send.*:(.*)$/) {$p=$1; insert_par("end_pres",\$p,"guni",$where);}
      elsif($_=~/^comment.*:(.*)$/) {$p=$1; insert_par("comment",\$p,"guni",$where);}
      else {print LOGFILE "Unidentified guni parameter in:$_";}
    }
    close(PARFILE); undef($SQL); undef($sth);
  }

  #######################################################
  # read in and insert the gain uniformity test data file
  #######################################################
  my $datafile = $filebase."guni_1d.txt";
  if(!(-e $datafile)) {print LOGFILE "WARNING: file $datafile does not exist!\n";}
  else {
    open(DATAFILE,$datafile) or die "ERROR: Cannot open $datafile!\n";
    $counter = 0;
    $SQL = "INSERT INTO roc_test_data_guni_1d ";
    $SQL .= "(roc_type,roc_serial,z_pos,anode_current) VALUES (?,?,?,?)";
    $sth = $dbh->prepare($SQL);
    while(<DATAFILE>){
      $counter++;
      $_=~/^(.{1,9})\s(.{1,9})\s(.{1,9})$/;
      $z_pos = $1; $anode_curr = $3;
      trim(\$z_pos); trim(\$anode_curr);
      $sth->execute($roc_type,$roc_serial,$z_pos,$anode_curr);
      if($sth->rows<1) {print LOGFILE "ERROR: gain unif. 1d data insertion from $datafile failed at line $counter!\n"; return;}
    }
    close(DATAFILE); undef($SQL); undef($sth);
  }
  $datafile = $filebase."guni_2d.txt";
  if(!(-e $datafile)) {print LOGFILE "WARNING: file $datafile does not exist!\n";}
  else {
    open(DATAFILE,$datafile) or die "ERROR: Cannot open $datafile!\n";
    $counter = 0;
    $SQL = "INSERT INTO roc_test_data_guni_2d ";
    $SQL .= "(roc_type,roc_serial,z_pos,phi_pos,anode_current) VALUES (?,?,?,?,?)";
    $sth = $dbh->prepare($SQL);
    while(<DATAFILE>){
      $counter++;
      $_=~/^(.{1,9})\s(.{1,9})\s(.{1,9})$/;
      $z_pos = $1; $phi_pos = $2; $anode_curr = $3;
      trim(\$z_pos); trim(\$phi_pos); trim(\$anode_curr);
      $sth->execute($roc_type,$roc_serial,$z_pos,$phi_pos,$anode_curr);
      if($sth->rows<1) {print LOGFILE "ERROR: gain unif. 2d data insertion from $datafile failed at line $counter!\n"; return;}
    }
    close(DATAFILE); undef($SQL); undef($sth);
  }
}

######################################################################################
# extracts data from absolute gabs test files (par and data) and insert it into gateDB
######################################################################################
sub process_gabs_data {
  my($filebase,$roc_type,$roc_serial) = @_;
  my $SQL; my $sth;
  
  ##########################################################
  # read in and insert the absolute gain test parameter file
  ##########################################################
  $SQL = "INSERT INTO roc_test_head_gabs (roc_type,roc_serial) VALUES (?,?)";
  $sth = $dbh->prepare($SQL); $sth->execute($roc_type,$roc_serial);
  $where = "WHERE roc_type='".$roc_type."' AND roc_serial='".$roc_serial."'";
  if($sth->rows<1) {print LOGFILE "ERROR: gabs test parameters insertion from $parfile failed! Returning.\n"; return;}  
  my $parfile = $filebase."gabs_par.txt";
  if(!(-e $parfile)) {print LOGFILE "WARNING: file $parfile does not exist!\n";}
  else {
    open(PARFILE,$parfile) or die "ERROR: Cannot open $parfile!\n";
    while(<PARFILE>){
      if($_=~/^operator.*:(.*)$/) {$p=$1; insert_par("operator",\$p,"gabs",$where);}
      elsif($_=~/^creator\slab.*:(.*)$/) {$p=$1; insert_par("institute",\$p,"gabs",$where);}
      elsif($_=~/^gas\smixture\s?:(.*)$/) {$p=$1; insert_par("gas_mixture",\$p,"gabs",$where);}
      elsif($_=~/^flow\srate.*:(.*)$/) {$p=$1; insert_par("flow_rate",\$p,"gabs",$where);}
      elsif($_=~/^time\sat\sstart\s?:(.*)$/) {$p=$1; insert_par("start_time",\$p,"gabs",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_oxy",\$p,"gabs",$where);}
      elsif($_=~/^temperature\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_temp",\$p,"gabs",$where);}
      elsif($_=~/^pressure\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_pres",\$p,"gabs",$where);}
      elsif($_=~/^drift\svoltage.*:(.*)$/) {$p=$1; insert_par("drift_volt",\$p,"gabs",$where);}
      elsif($_=~/^source.*:(.*)$/) {$p=$1; insert_par("source",\$p,"gabs",$where);}
      elsif($_=~/^count\srate\ssource.*:(.*)$/) {$p=$1; insert_par("count_rate",\$p,"gabs",$where);}
      elsif($_=~/^time\sat\send\s?:(.*)$/) {$p=$1; insert_par("end_time",\$p,"gabs",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\send.*:(.*)$/) {$p=$1; insert_par("end_oxy",\$p,"gabs",$where);}
      elsif($_=~/^temperature\sat\send.*:(.*)$/) {$p=$1; insert_par("end_temp",\$p,"gabs",$where);}
      elsif($_=~/^pressure\sat\send.*:(.*)$/) {$p=$1; insert_par("end_pres",\$p,"gabs",$where);}
      elsif($_=~/^comment.*:(.*)$/) {$p=$1; insert_par("comment",\$p,"gabs",$where);}
      else {print LOGFILE "Unidentified gabs parameter in:$_";}
    }
    close(PARFILE); undef($SQL); undef($sth);
  }

  #####################################################
  # read in and insert the absolute gain test data file
  #####################################################
  my $datafile = $filebase."gabs_data.txt";
  if(!(-e $datafile)) {print LOGFILE "WARNING: file $datafile does not exist!\n";}
  else {
    open(DATAFILE,$datafile) or die "ERROR: Cannot open $datafile!\n";
    my $counter = 0;
    $SQL = "INSERT INTO roc_test_data_gabs ";
    $SQL .= "(roc_type,roc_serial,anode_voltage,anode_current,gas_gain) VALUES (?,?,?,?,?)";
    $sth = $dbh->prepare($SQL);
    while(<DATAFILE>){
      $counter++;
      $_=~/^(.{1,9})\s(.{1,9})\s(.{1,9})$/;
      $anode_volt = $1; $anode_curr = $2; $gas_gain = $3;
      trim(\$anode_volt); trim(\$anode_curr); trim(\$gas_gain);
      $sth->execute($roc_type,$roc_serial,$anode_volt,$anode_curr,$gas_gain);
      if($sth->rows<1) {print LOGFILE "ERROR: absolute gain data insertion from $datafile failed at line $counter!\n"; return;}
    }
    close(DATAFILE); undef($SQL); undef($sth);
  }
}

#################################################################################
# extracts data from spectral test files (par and data) and insert it into gateDB
#################################################################################
sub process_spec_data {
  my($filebase,$roc_type,$roc_serial) = @_;
  my $SQL; my $sth;
  
  #########################################################
  # read in and insert the spectral test parameter file
  #########################################################
  $SQL = "INSERT INTO roc_test_head_spec (roc_type,roc_serial) VALUES (?,?)";
  $sth = $dbh->prepare($SQL); $sth->execute($roc_type,$roc_serial);
  $where = "WHERE roc_type='".$roc_type."' AND roc_serial='".$roc_serial."'";
  if($sth->rows<1) {print LOGFILE "ERROR: spec test parameters insertion from $parfile failed! Returning.\n"; return;}  
  my $parfile = $filebase."spec_par.txt";
  if(!(-e $parfile)) {print LOGFILE "WARNING: file $parfile does not exist!\n";}
  else {
    open(PARFILE,$parfile) or die "ERROR: Cannot open $parfile!\n";
    while(<PARFILE>){
      if($_=~/^operator.*:(.*)$/) {$p=$1; insert_par("operator",\$p,"spec",$where);}
      elsif($_=~/^creator\slab.*:(.*)$/) {$p=$1; insert_par("institute",\$p,"spec",$where);}
      elsif($_=~/^gas\smixture\s?:(.*)$/) {$p=$1; insert_par("gas_mixture",\$p,"spec",$where);}
      elsif($_=~/^flow\srate.*:(.*)$/) {$p=$1; insert_par("flow_rate",\$p,"spec",$where);}
      elsif($_=~/^time\sat\sstart\s?:(.*)$/) {$p=$1; insert_par("start_time",\$p,"spec",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_oxy",\$p,"spec",$where);}
      elsif($_=~/^temperature\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_temp",\$p,"spec",$where);}
      elsif($_=~/^pressure\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_pres",\$p,"spec",$where);}
      elsif($_=~/^anode\svoltage.*:(.*)$/) {$p=$1; insert_par("anode_volt",\$p,"spec",$where);}
      elsif($_=~/^drift\svoltage.*:(.*)$/) {$p=$1; insert_par("drift_volt",\$p,"spec",$where);}
      elsif($_=~/^peak\sposition.*:(.*)$/) {$p=$1; insert_par("peak_pos",\$p,"spec",$where);}
      elsif($_=~/^stand\sdeviation.*:(.*)$/) {$p=$1; insert_par("sigma",\$p,"spec",$where);}
      elsif($_=~/^time\sat\send\s?:(.*)$/) {$p=$1; insert_par("end_time",\$p,"spec",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\send.*:(.*)$/) {$p=$1; insert_par("end_oxy",\$p,"spec",$where);}
      elsif($_=~/^temperature\sat\send.*:(.*)$/) {$p=$1; insert_par("end_temp",\$p,"spec",$where);}
      elsif($_=~/^pressure\sat\send.*:(.*)$/) {$p=$1; insert_par("end_pres",\$p,"spec",$where);}
      elsif($_=~/^comment.*:(.*)$/) {$p=$1; insert_par("comment",\$p,"spec",$where);}
      else {print LOGFILE "Unidentified spec parameter in:$_";}
    }
    close(PARFILE); undef($SQL); undef($sth);
  }

  ####################################################
  # read in and insert the spectral test data file
  ####################################################
  my $datafile = $filebase."spec_data.txt";
  if(!(-e $datafile)) {print LOGFILE "WARNING: file $datafile does not exist!\n";}
  else {
    open(DATAFILE,$datafile) or die "ERROR: Cannot open $datafile!\n";
    my $counter = 0;
    $SQL = "INSERT INTO roc_test_data_spec ";
    $SQL .= "(roc_type,roc_serial,channel,entries) VALUES (?,?,?,?)";
    $sth = $dbh->prepare($SQL);
    while(<DATAFILE>){
      $counter++;
      $_=~/^(.{1,9})\s(.{1,9})$/;
      $channel = $1; $entries = $2;
      trim(\$channel); trim(\$entries);
      $sth->execute($roc_type,$roc_serial,int($channel),int($entries));
      if($sth->rows<1) {print LOGFILE "ERROR: spectral data insertion from $datafile failed at line $counter!\n"; return;}
    }
    close(DATAFILE); undef($SQL); undef($sth);
  }
}

##################################################################################
# extracts data from stability test files (par and data) and insert it into gateDB
##################################################################################
sub process_stab_data {
  my($filebase,$roc_type,$roc_serial) = @_;
  my $SQL; my $sth;
  
  #########################################################
  # read in and insert the stability test parameter file
  #########################################################
  $SQL = "INSERT INTO roc_test_head_stab (roc_type,roc_serial) VALUES (?,?)";
  $sth = $dbh->prepare($SQL); $sth->execute($roc_type,$roc_serial);
  $where = "WHERE roc_type='".$roc_type."' AND roc_serial='".$roc_serial."'";
  if($sth->rows<1) {print LOGFILE "ERROR: stab test parameters insertion from $parfile failed! Returning.\n"; return;}  
  my $parfile = $filebase."stab_par.txt";
  if(!(-e $parfile)) {print LOGFILE "WARNING: file $parfile does not exist!\n";}
  else {
    open(PARFILE,$parfile) or die "ERROR: Cannot open $parfile!\n";
    while(<PARFILE>){
      if($_=~/^operator.*:(.*)$/) {$p=$1; insert_par("operator",\$p,"stab",$where);}
      elsif($_=~/^creator\slab.*:(.*)$/) {$p=$1; insert_par("institute",\$p,"stab",$where);}
      elsif($_=~/^gas\smixture\s?:(.*)$/) {$p=$1; insert_par("gas_mixture",\$p,"stab",$where);}
      elsif($_=~/^flow\srate.*:(.*)$/) {$p=$1; insert_par("flow_rate",\$p,"stab",$where);}
      elsif($_=~/^time\sat\sstart\s?:(.*)$/) {$p=$1; insert_par("start_time",\$p,"stab",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_oxy",\$p,"stab",$where);}
      elsif($_=~/^temperature\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_temp",\$p,"stab",$where);}
      elsif($_=~/^pressure\sat\sstart.*:(.*)$/) {$p=$1; insert_par("start_pres",\$p,"stab",$where);}
      elsif($_=~/^anode\svoltage.*:(.*)$/) {$p=$1; insert_par("anode_volt",\$p,"stab",$where);}
      elsif($_=~/^drift\svoltage.*:(.*)$/) {$p=$1; insert_par("drift_volt",\$p,"stab",$where);}
      elsif($_=~/^time\sat\send\s?:(.*)$/) {$p=$1; insert_par("end_time",\$p,"stab",$where);}
      elsif($_=~/^oxygen\sconcentration\sat\send.*:(.*)$/) {$p=$1; insert_par("end_oxy",\$p,"stab",$where);}
      elsif($_=~/^temperature\sat\send.*:(.*)$/) {$p=$1; insert_par("end_temp",\$p,"stab",$where);}
      elsif($_=~/^pressure\sat\send.*:(.*)$/) {$p=$1; insert_par("end_pres",\$p,"stab",$where);}
      elsif($_=~/^comment.*:(.*)$/) {$p=$1; insert_par("comment",\$p,"stab",$where);}
      else {print LOGFILE "Unidentified spec parameter in:$_";}
    }
    close(PARFILE); undef($SQL); undef($sth);
  }

  ####################################################
  # read in and insert the stability test data file
  ####################################################
  my $datafile = $filebase."stab_data.txt";
  if(!(-e $datafile)) {print LOGFILE "WARNING: file $datafile does not exist!\n";}
  else {
    open(DATAFILE,$datafile) or die "ERROR: Cannot open $datafile!\n";
    my $counter = 0;
    $SQL = "INSERT INTO roc_test_data_stab ";
    $SQL .= "(roc_type,roc_serial,relative_time,peak_pos,sigma,chi2,anode_current,oxygen_conc,";
    $SQL .= "pressure,temperature) VALUES (?,?,?,?,?,?,?,?,?,?)";
    $sth = $dbh->prepare($SQL);
    while(<DATAFILE>){
      $counter++;
      ($time,$peak,$sig,$chi2,$curr,$oxy,$pres,$temp) = $_=~/(\d{1,9}\.\d{1,9})/g;
      trim(\$time);trim(\$peak);trim(\$sig);trim(\$chi2);trim(\$curr);trim(\$oxy);trim(\$pres);trim(\$temp);
      $sth->execute($roc_type,$roc_serial,$time,$peak,$sig,$chi2,$curr,$oxy,$pres,$temp);
      if($sth->rows<1) {print LOGFILE "ERROR: stability data insertion from $datafile failed at line $counter!\n"; return;}
    }
    close(DATAFILE); undef($SQL); undef($sth);
  }
}

sub insert_par {
  my($parname,$parval,$table,$where) = @_;
  ${$parval} =~ s/^\s+//; ${$parval} =~ s/\s+$//; # clean from whitespaces (need a reference)
  my $SQL = "UPDATE roc_test_head_".$table." SET ".$parname."='".${$parval}."' ".$where;
  if(${$parval} eq '-') {return;}
  my $sth = $dbh->prepare($SQL); if(${$parval}) {$sth->execute();}
}

sub trim {
  my $str = shift;
  ${$str} =~ s/^\s+//;
  ${$str} =~ s/\s+$//;
}

close(LOGFILE) or die "ERROR: Cannot close $logfile!\n";