#!/usr/local/bin/perl
 BEGIN { $ENV{DBI_PUREPERL}=1 }
use DBI;

use CGI qw(:standard :html3);
use Time::localtime;
use Time::gmtime;
use Time::Local;
use Date::Manip qw(ParseDate);
use Mail::Mailer;
use Switch;
use Socket;
use Sys::Hostname;

my $host = hostname();

my $version = "3.4.2";
my $dateversion = "24 Feb 2010";
# Better Dew point plotting 
# Extension to known data points
# new Oracle server
# Extension to other systems
# added noplot option  20/10/07
# added nofile option  30/10/07 1.2.3
# modified nofile so that it only returns data 1.2.4
# modified query for speed 1.2.4
# search on card parameters by using channel 99 1.2.5
# included pixels on data point search 1.2.6
# allowed search on negative temperatures (or values) 1.2.6
# Easy search on aliases also for environmental 1.2.7
# little bug fix 1.2.7.1
# one more bug fix for SCT and ENV 1.2.7.2
# some bugs corrected,for nofile option 3 digits and milliseconds 1.2.7.3
# bug corrected like or = according to presence of a % 1.2.8
# Aliasrel introduced, different syntax: aliasrel min max element
#       syntax needs documentation !!!
# added new system: BCM number 45 in IDE 25 Aprile 08 version 1.3.1
# filter no apache for subscription (v. 1.3.1)
# added system 44 and 45
# added TRT ststem 3 to 6 (version 1.3.3)
# added TS/CV database (version 1.3.4 29 May 2008 SDA
# in case of "nofile" it sends only the original data 1.3.5
# kludge for milliseconds in TSCV query set to 002 version 1.3.6 6 June 2008
# corrected bug in TSCV query order by chrono 1.3.7, 7 June 2008
# added NMR system 42  1.3.8 11 June 2008
# added magnetic field ATLGCSIS 1.3.9 24 June 2008
# added maximum number of lines 1.4.0 8 July 2008
# modified system number for CIC 1.4.1 July 2008
# added new systems for pixels 1.4.2 4th August 2008 
# added query on comment_ 1.4.3 4th August 2008
# light-weight output version in case of no file for the browser: only one data point
# added step query 1.5.0 12 August 2008
# added new systems form central DCS 1.5.4  19 Aug 08 SDA
# added system 20 (SCS) to the SCT
# fixed a bug reported by Urban in nofile option when value table changes. v.1.5.6, 27 Aug 2008 SDA
# fixed bug reported by Kevin: pixel aliases dont contain PIX and were taken as SCT v1.5.7 28 Aug 2008 SDA
# added system 43 for TEH and deleted some data cleaning that is inappropriate v. 1.5.8  2 Sept 2008 SDA
# added system 43 for RADMON  v. 1.5.9  4 Sept 2008 SDA
# moved to off-line data base v. 2.0.0  19 Sept 2008
# added access to LUCID data points
# increased time limit to 61 days 2.0.1
# increased resolution to 5 digits only for NMR and magnet currents 2.0.2
# increased resolution fro RadMon 2.0.3 31 Oct 2008 SDA
# Added point_1 detection: connects to ATONR if it is run from point_1
# Added bitand search 2.1.0
# Added search within limits 2.1.0 SDA
# Fixed a couple of bugs, many more are resident bugs... 2.1.1 SDA
# got a faster response for SCT and IDE 2.1.2
# added all systems 3.0.0 
# solved ambiguity of sys_id table
# corrected tiny bug slowing down trt queries 3.0.1 SDA
# corrected bug that added channel to unknown datapoint 3.0.1
# corrected some bugs. No longer using sys_id in queries. Stop if no dp element found. Check on rel queries. 3.0.2 SDA
# got rid of some big chunk of code... 3.0.3 Sda 20 Jan 09
# added check on NaN and rounding to 8 decimals in Oracle. 6 Mar 09 SDA 3.0.4
#cleaned up most code, bug fix in step query 3.0.5 SDA 02 April 2009
# added ZDC IS and MUO to the schemas SDA 11 aug 09 3.1.0
# added possibility of searching for SCT trips in bitand search 3.2.0 SDA
# added LHC system 3.2.1 SD'A Nov 2009
# case of occupancy: increased resolution for viewer case 3.2.2 Nov 09
# include text variables not for plotting 3.4.2 Feb 2010

#parse the parameters

my $usage =   $ARGV[0];
my $crate =   $ARGV[1];
my $channel = $ARGV[2];
my $element = $ARGV[3];
my $stadate = $ARGV[4];
my $statime = $ARGV[5];
my $stodate = $ARGV[6];
my $stotime = $ARGV[7];

my $barrel  = $ARGV[1];#for trans stuff
my $row     = $ARGV[2];
my $module  = $ARGV[3];
my $arg4    = $ARGV[4];
my $arg5    = $ARGV[5];
my $arg6    = $ARGV[6];
my $arg7    = $ARGV[7];
my $arg8    = $ARGV[8];
my $arg9    = $ARGV[9];

my $maxlines = 120000;

# usage :
# dcsDBaccess.pl <query type> <crate> <channel> <element> <start date> <start time> <stop date> <stop time>

my $debug = 0;
   $batch = 1 if (rindex($arg9,"nofile")>=0);

#print "version $version $dateversion on $host \n" if ($debug > 0)  ;

&subscribe() if (rindex("nofile",$arg9) < 0);


if ($usage eq "--version" || $usage eq "-v"){
print "\n dcsDBaccess.pl  Version $version released on $dateversion running on $host\n";
die "\n\n";
}




my $instructions = "
Program for accessing the Atlas DCS data from the off-line Oracle archive.

Available options:
1) plot
2) aliasplot
3) descplot

4) step
5) aliastep
6) descstep

7) rel
8) aliasrel
9) descrel

10) relin
11) aliasrelin
12) descrelin

13) bitand
14) aliasbitand
15) descbitand

16) text
17) aliastext
18) desctext

Usage (1) for plotting DCS data and printing values and time stamps to ascii files
          set <query type> = 'plot' or 'aliasplot' or 'descplot'
Syntax:
  ./dcsDBaccess.pl <query type> <notused> <notused> <data_point_element> <start date> <start time> <stop date> <stop time>


Examples: 
          ./dcsDBaccess.pl plot   00 00    \%SCT\%Crate84.Channel00.LVch_Icc\% 11-06-2007 09:00 11-06-2007 14:00 datapoint gnuplot
          ./dcsDBaccess.pl aliasplot  00 00 SCT/PS/BARREL/Q1/BARREL5/B502_Loop093/CapilliaryA/Inlet/Pipe8/ModuleC4/LVchPINI 10-09-2008 00:00 10-09-2008 14:00 datapoint gnuplot

A gnuplot window will pop up on your screen if you use gnuplot option (faster) otherwise a root session will be started.
The results are stored in the ASCII file 'query_result.txt', for human reading
Additional ascii files are written, in the format <value> <time from start time>
one file per selected element (e.g. one for TM0 and one for TM1)
These files are called 'plot_db_X.dat' with X from 1 to n_elements.
The files 'second_plot_db_X.dat' are generated for plotting purposes only.


Usage (2) for 'relational queries', like: 'All elements within time interval with bias V larger than 10 Volts'
          set <query type> = 'rel' or 'aliasrel'
          This returns a list of data points which had at least one value outside the range [low;high]
Syntax:
 dcsDBaccess.pl <query type> <value_low> <value_high> <element> <start date> <start time> <stop date> <stop time>

Example: ./dcsDBaccess.pl rel 0.5 10  'SCT*LVch_Icc*' 11-06-2007 09:00 11-06-2007 14:00 datapoint

Example 2:
 'List all the humidity sensors with dew points outside the range -40C -5C'
Example: ./dcsDBaccess.pl aliasrel -40 -5  \%Dew\% 11-06-2007 09:00 11-06-2007 14:00


 The results are stored in the ASCII file query_result.txt for human reading\n\n";
if ($usage eq "--help" || $usage eq "-h"){
print "$instructions\n";
die "\n";
#add text
}



my $examples = "Correct usage:
Example 1:  ./dcsDBaccess.pl plot      84 00 LVch_Icc 11-06-2007 09:00 11-06-2007 14:00
Example 2:  ./dcsDBaccess.pl rel       -10 0  LVch_Icc 11-06-2007 09:00 11-06-2007 14:00
Example 3:  ./dcsDBaccess.pl aliasrel  -30 50 Dew 11-06-2007 09:00 11-06-2007 14:00
Example 4:  ./dcsDBaccess.pl plot       03 24 Tm0 13-05-2007 08:00 13-05-2007 09:00\n";

#add text
if ($usage ne "plot" && $usage ne "rel" && $usage ne "aliasrel" &&  $usage ne "tscvplot"
 && $usage ne "bitand" && $usage ne "aliasbitand" &&  $usage ne "descbitand"
 && $usage ne "alias" && $usage ne "aliasplot" && $usage ne "descplot" &&  $usage ne "descrel"  &&  $usage ne "descrelin"
 && $usage ne "step" && $usage ne "aliastep" && $usage ne "descstep" &&  $usage ne "relin" &&  $usage ne "aliasrelin"
 && $usage ne "text" &&  $usage ne "aliastext" &&  $usage ne "desctext"
){#add text
   print "The query type $usage is not available\n";
   print "\nUsage: dcsDBaccess.pl <query type> <value1> <value2> <search string> <start date> <start time> <stop date> <stop time>\n\n";   print "\n$instructions\n";

die "\n\n\n";
}


##############################################################


if ($usage eq "plot" || $usage eq "rel" ||  $usage eq "aliasrel" || $usage eq "aliasplot" || $usage eq "step"||
    $usage eq "descstep" || $usage eq "aliastep" ||  $usage eq "aliasrelin" ||  $usage eq "relin" ||
    $usage eq "descrel" || $usage eq "descrelin" || $usage eq "descplot" || $usage eq "bitand" || $usage eq "aliasbitand"){


if ($usage eq "plot" || $usage eq "aliasplot" ){
   $crate =~s/\D+//;
   $channel =~s/\D+//;
} # end of case plot or aliasplot

# checks on a valid string

#getting rid of consecutive wild cards.
$element =~ s/\%\%/\%/g;
$element =~ s/\%\%/\%/g;
$element =~ s/\%\%/\%/g;
$element =~ s/\%\%/\%/g;
my $len_ele= length $element;
if ($len_ele < 3  ) { print " $element is too short to describe a DPE.\n" if (!$batch); die "";}



 &timedate() ;#if (!$batch);

}

#==========================================================


# $DB = $arg[9] if ($arg8 eq "datapoint");
# $DB = "PIX" if ($arg9 eq "" || $arg9 eq "gnuplot" || $arg9 eq "noplot" || $arg9 eq "nofile");

 $equallike = "LIKE ";
 $searchstring = $element;

print "search $searchstring element $element \n"if ($debug > 0);
$equallike = "=" if ($arg8 eq "datapoint");

$equallike = "LIKE " if (rindex($searchstring,"%") >= 0);

print (" $searchstring Equallike $equallike ") if ($debug > 0);

$dd = length($searchstring) ;
print " Search string: $searchstring schema: $DB\n\n"  if ($debug > 0);
die  "$dd Search string too generic $searchstring, need to debug,
     \n please report this problem, bye now\n" if ($searchstring eq "%%" || $searchstring eq"%" || $searchstring eq "%%%" ||
(rindex($searchstring ,"%") >=0 && length($searchstring) < 5));


my $nrows = 0;
my @elementArry =();


my $resultfile = "query_result.txt";
my $plotfile = "plot_db_1.dat";
my $twoplotfile = "second_plot_db_1.dat";
my @filesToPlot = ( $plotfile );
open (RESULT,"> $resultfile") or die "could not open result file: $! "if ( rindex ($ARGV[9],"nofile")  < 0 ) ;
open (TOPLOT,"> $plotfile")  or die "could not open plot file: $!"if ( rindex ($ARGV[9],"nofile")  < 0 );
open (TWOPLOT,"> $twoplotfile")  or die "could not open twoplot file: $!"if ( rindex ($ARGV[9],"nofile")  < 0 );

  print " $resultfile $ARGV[9] $ARGV[8] \n" if ($debug > 0);

  print "\n going to connect $DB 1 ...\n" if ($debug > 0);


my $database = "dbi:Oracle:ATLAS_PVSSPROD";
#   $database = "dbi:Oracle:ATONR" if ($DB eq "TSCV" || rindex($host,"lxplus") < 0 );

  print "\n going to connect to $database $DB ...\n" if ($debug > 0);
#  $dbh = DBI->connect ('dbi:Oracle:ATONR',
#   $dbh = DBI->connect ('dbi:Oracle:ATLAS_PVSSPROD',
 $dbh = DBI->connect ($database,
            'ATLAS_PVSS_READER', 'PVSSRED4PRO' )
  or die "Connecting:", $DBI::errstr;

  print "connected to the database $database\n" if ($debug > 0);


#treat the various cases in an unified way

  switch ($usage) {
      case "aliasplot" {$searchwhat = "alias"; }
      case "descplot"  {$searchwhat = "comment_"; }
      case "plot"      {$searchwhat = "element_name"; }
      case "aliasrel"  {$searchwhat = "alias"; $orderby ="myalias";}
      case "aliasrelin"{$searchwhat = "alias"; $orderby ="myalias";}
      case "descrel"   {$searchwhat = "comment_"; $orderby ="mycomm";}
      case "rel"       { $searchwhat = "element_name"; $orderby = "elem_name";}
      case "relin"     { $searchwhat = "element_name"; $orderby = "elem_name";}
      case "bitand"    { $searchwhat = "element_name"; $orderby = "elem_name";}
      case "aliasbitand" { $searchwhat = "alias"; $orderby = "myalias";}
      case "step"      { $searchwhat = "element_name"; $orderby = "elem_name";}
      case "aliastep"  {$searchwhat = "alias"; $orderby ="myalias";}
      case "descstep"  {$searchwhat = "COMMENT_"; $orderby ="mycomm";}
      case "text"      { $searchwhat = "element_name"; $orderby = "elem_name";}
      case "aliastext"  {$searchwhat = "alias"; $orderby ="myalias";}
      case "desctext"  {$searchwhat = "COMMENT_"; $orderby ="mycomm";}
  }


# get the header identifier and sys_id 
   print "Getting_Alias_comment $searchwhat for $searchstring  \n"  if ($debug > 0);

   @resu = get_alias_comment ($searchwhat,$searchstring);
   
   #$dp_alias_desc = get_alias_comment ($searchwhat,$searchstring);
   $sys_id = pop(@resu);
    $dp_alias_desc = $resu[0].";".$resu[1].";".$resu[2].";";
    $elname =  $resu[0];
    print "Element Name $elname Resu @resu \n "if ($debug > 0);
    $DB = get_DB_from_element_name($elname);
#  } else {$dp_alias_desc = ""};
#}


print "sys ID $sys_id for  $dp_alias_desc $elname DataBase PVSS$DB  Sys Number $sys_nums \n"  if ($debug > 0);


my @tables = get_table_names ($stadate, $statime,$stodate, $stotime, $DB);


### above is unfinished.

print " Tables are @tables \n"  if ($debug > 0);




#======================= description ==========================

if ($usage eq "descplot"){
local $equin = "="; $equin = "IN" if (index($searchstring,"%")>=0);

$tblnbr=0;
$nrows = 0;
foreach $tbl (@tables) {
$tblnbr++;
     my $query_descplot ="
SELECT B.COMMENT_, ROUND(A.VALUE_NUMBER,8), TO_CHAR(A.TS, 'DD-MM-YYYY HH24:MI:SS:FF3')
FROM     ATLAS_PVSS".$DB.".".$tbl. " A,
   ATLAS_PVSS".$DB.".elements B
WHERE  A.ELEMENT_ID = B.ELEMENT_ID  AND ROWNUM <= ".$maxlines."
AND  A.ELEMENT_ID ".$equin."
   ( SELECT ELEMENT_ID
       FROM ATLAS_PVSS".$DB.".ELEMENTS
       WHERE comment_ ".$equallike." '".$searchstring."')
  AND TS BETWEEN TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI:SS' ) AND
                 TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI:SS' ) 
  AND A.VALUE_NUMBER <> 'NaN' AND A.VALUE_NUMBER <> BINARY_FLOAT_INFINITY 
  ORDER BY  B.COMMENT_, A.TS";

print "descplot \n $query_descplot \n\n"  if ($debug > 0)  ;
 die "$query_descplot \n Search string too generic %%, need to debug, \n
     \n please report this problem, bye now\n" if ($searchstring eq "%%");
  $sth = $dbh->prepare ($query_descplot)
  or die "Preparing:", $dbh::errstr;
  &fielder($datapoint);
   }

}


#========================= aliasplot ================================

if ($usage eq "aliasplot"){
local $equin = "="; $equin = "IN" if (index($searchstring,"%")>=0);
#special case for pixels
$equin = "IN" if (index($DB,"PIX")>=0);

$tblnbr=0;
   $nrows = 0;
foreach $tbl (@tables) {
$tblnbr++;
     my $query_aliasplot ="
SELECT B.ALIAS, ROUND(A.VALUE_NUMBER,8), TO_CHAR(A.TS, 'DD-MM-YYYY HH24:MI:SS:FF3')
FROM     ATLAS_PVSS".$DB.".".$tbl. " A,
   ATLAS_PVSS".$DB.".elements B
WHERE  A.ELEMENT_ID = B.ELEMENT_ID AND ROWNUM <= ".$maxlines."
AND  A.ELEMENT_ID ".$equin."
   ( SELECT ELEMENT_ID
       FROM ATLAS_PVSS".$DB.".ELEMENTS
       WHERE  alias ".$equallike." '".$searchstring."')
  AND TS BETWEEN TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI:SS' ) AND
                 TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI:SS' )
  AND A.VALUE_NUMBER <> 'NaN' AND A.VALUE_NUMBER <> BINARY_FLOAT_INFINITY 
  ORDER BY  B.ALIAS, A.TS";

print "aliasplot \n $query_aliasplot \n\n"  if ($debug > 0)  ;

die "$query_aliasplot \n Search string too generic %%, need to debug, \n
     \n please report this problem, bye now\n" if ($searchstring eq "%%");
  $sth = $dbh->prepare ($query_aliasplot)
  or die "Preparing:", $dbh::errstr;
  &fielder($datapoint);
   }
}

#======================== TSCV ===============================

if ($usage eq "plot" && $DB eq "TSCV"){

      print "usage is tscvplot $searchstring  \n"  if ($debug > 0)  ;
      $tblnbr=1;
      #$searchstring = $element if ($arg8 eq "datapoint");
my $TSCV_query = 
"select name,
       to_number(NVAL),
       to_char(to_date('01-jan-1970 00:00:00', 'dd-mon-yyyy hh24:mi:ss')
+ to_number(chrono) / 1000 / 60 / 60 / 24,'dd-mm-yyyy hh24:mi:ss') ts
from ATLAS_TDCV.TRD
where name ".$equallike." '".$searchstring."' and
      chrono BETWEEN (TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI') -
to_date('01-jan-1970 00:00:00', 'dd-mon-yyyy hh24:mi:ss')) * 1000 * 3600 * 24 AND
                    (TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI') -
to_date('01-jan-1970 00:00:00', 'dd-mon-yyyy hh24:mi:ss')) * 1000 * 3600 * 24 and
      nval is not null
order by name,chrono";
  print "TSCV \n $TSCV_query \n\n"  if ($debug > 0)  ;
  $sth = $dbh->prepare ($TSCV_query)
  or die "Preparing:", $dbh::errstr;
  &fielder();

}

#========================== plot ====================

if ($usage eq "plot" && $DB ne "TSCV"){



      print "usage is plot\n"  if ($debug > 0)  ;
      $searchstring = $element if ($arg8 eq "datapoint");
local $equin = "="; $equin = "IN" if (index($searchstring,"%")>=0);

$tblnbr = 0;
   $nrows = 0;
foreach $tbl (@tables) {
$tblnbr++;
$query_all ="
SELECT B.ELEMENT_NAME, ROUND(A.VALUE_NUMBER,8), TO_CHAR(A.TS, 'DD-MM-YYYY HH24:MI:SS:FF3')
FROM     ATLAS_PVSS".$DB.".".$tbl. " A,
   ATLAS_PVSS".$DB.".ELEMENTS B
WHERE A.ELEMENT_ID = B.ELEMENT_ID and  ROWNUM <= ".$maxlines."
AND  A.ELEMENT_ID ".$equin."
   ( SELECT ELEMENT_ID
       FROM ATLAS_PVSS".$DB.".ELEMENTS
       WHERE  ELEMENT_NAME ".$equallike." '".$searchstring."')
  AND TS BETWEEN TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI:SS' ) AND
                 TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI:SS' )
  AND A.VALUE_NUMBER <> 'NaN' AND A.VALUE_NUMBER <> BINARY_FLOAT_INFINITY 
  ORDER BY B.ELEMENT_NAME, A.TS";

print "Query All $query_all\n"   if ($debug > 0)  ;

  $sth = $dbh->prepare ($query_all)
  or die "Preparing:", $dbh::errstr;
  &fielder();
}
    }

#===================== "text" queries ==================================
if (index($usage,"text")>=0) {

$ARGV[9] = "noplot";
$tblnbr = 0;
   $nrows = 0;
foreach $tbl (@tables) {
$tblnbr++;
$equin = "="; $equin = "IN" if (index($searchstring,"%")>=0);
$query_text ="
SELECT B.".$searchwhat.", A.VALUE_STRING, TO_CHAR(A.TS, 'DD-MM-YYYY HH24:MI:SS:FF3')
FROM     ATLAS_PVSS".$DB.".".$tbl. " A,
   ATLAS_PVSS".$DB.".ELEMENTS B
WHERE A.ELEMENT_ID = B.ELEMENT_ID and  ROWNUM <= ".$maxlines."
AND  A.ELEMENT_ID ".$equin."
   ( SELECT ELEMENT_ID
       FROM ATLAS_PVSS".$DB.".ELEMENTS
       WHERE ".$searchwhat." ".$equallike." '".$searchstring."')
  AND TS BETWEEN TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI:SS' ) AND
                 TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI:SS' )
  ORDER BY B.ELEMENT_NAME, A.TS";
print "Query All $query_all\n"   if ($debug > 0)  ;

  $sth = $dbh->prepare ($query_text)
  or die "Preparing:", $dbh::errstr;
  &fielder();
}

}# end of text queries


#===================== "relational" queries ============================

if ($usage eq "rel" || $usage eq "aliasrel" || $usage eq "descrel" || 
    $usage eq "descrelin" || $usage eq "relin"  || $usage eq "aliasrelin"){

  switch ($usage) {
      case "aliasrel" {$searchwhat = "ALIAS"; $orderby ="myalias";}
      case "descrel"  {$searchwhat = "COMMENT_"; $orderby ="mycomm";}
      case "descrelin"  {$searchwhat = "COMMENT_"; $orderby ="mycomm";}
      case "aliasrelin" {$searchwhat = "ALIAS"; $orderby ="myalias";}
      else            { $searchwhat = "ELEMENT_NAME"; $orderby = "elem_name";}
  }

  switch ($usage){
   case "relin"       {$symbol1 = "< "; $symbol2 = "> "; $andor = " AND ";}
   case "descrelin"   {$symbol1 = "< "; $symbol2 = "> "; $andor = " AND ";}
   case "aliasrelin"  {$symbol1 = "< "; $symbol2 = "> "; $andor = " AND ";}
   else               {$symbol1 = "> "; $symbol2 = "< "; $andor = " OR ";}
  }

#  if ($sys_id) {$equalin = " = "; $sys_nums = $sys_id;}


#if ($usage eq "aliasrel") {$searchwhat = "ALIAS"; $orderby ="myalias";}
#else { $searchwhat = "ELEMENT_NAME"; $orderby = "elem_name";}

   $nrows = 0;
foreach $tbl (@tables) {
 $query_rel =
 "SELECT elem_name, myalias, mycomm, max_value, min_value  FROM
( SELECT B.ELEMENT_NAME elem_name, B.ALIAS myalias, B.COMMENT_ mycomm, max(TO_NUMBER(A.VALUE_NUMBER)) max_value, 
                                                                min(TO_NUMBER(A.VALUE_NUMBER)) min_value
FROM     ATLAS_PVSS".$DB.".".$tbl." A,
  ATLAS_PVSS".$DB.".ELEMENTS B
WHERE  A.ELEMENT_ID = B.ELEMENT_ID
AND  A.ELEMENT_ID IN
  ( SELECT ELEMENT_ID
      FROM ATLAS_PVSS".$DB.".ELEMENTS
      WHERE ".$searchwhat." LIKE '".$searchstring."\%' )
  AND TS BETWEEN TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI:SS' ) AND
                 TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI:SS' )
  AND A.VALUE_NUMBER <> 'NaN' AND A.VALUE_NUMBER <> BINARY_FLOAT_INFINITY 
GROUP BY  B.ALIAS,B.element_name,B.comment_ ORDER BY B.". $searchwhat ." ) sub_query
WHERE  sub_query.max_value ".$symbol1.$channel.$andor."  sub_query.min_value ".$symbol2.$crate." 
ORDER BY ".$orderby;

print "Query Rel \n $query_rel\n\n"  if ($debug > 0)  ;

$sth = $dbh->prepare ($query_rel)
  or die "Preparing:", $dbh::errstr;
&fielder();
} #end foreach
}

#======================= step query =====================================

if ($usage eq "step" || $usage eq "aliastep" || $usage eq "descstep"){
      switch ($usage) {
      case "aliastep"  {$searchwhat = "ALIAS"; $orderby ="el_al";}
      case "descstep"  {$searchwhat = "COMMENT_"; $orderby ="el_desc";}
      else             { $searchwhat = "ELEMENT_NAME"; $orderby = "el_name";}
  }
 
  my $minvalid = -280.;
foreach $tbl (@tables) {
  &steps($searchwhat,$equallike,$crate,$channel);
}

}

#  ====== end of step query ==============================================

# =========== Bit-AND query ==============================================
if ($usage eq "bitand" || $usage eq "aliasbitand" || $usage eq "descbitand"){
  switch ($usage) {
      case "aliasbitand"  {$searchwhat = "ALIAS"; $orderby ="el_al";}
      case "descbitand"   {$searchwhat = "COMMENT_"; $orderby ="el_desc";}
      else             { $searchwhat = "ELEMENT_NAME"; $orderby = "el_name";}
  }
foreach $tbl (@tables) {
  &bitandsearch($crate,$channel);
}
}

#===========end of bitand query ===========================================
$sth->finish();
$dbh->disconnect();
close (RESULT);
close (TOPLOT);





#Stop here if no result
die "\nThe DB query returned zero result\nHint: check the time window (SCT off) or the data point element you entered $element\n\n" 
if ($nrows <= 0 &&  rindex ($ARGV[9],"nofile")  < 0 );

print "The DB query returned zero result \n" if ($nrows <= 0  && $debug > 0)  ;

exit if ($nrows <= 0 );

#stop here if relational query
die "\nThe DB query returned $nrows DPE's\n" if ((rindex($usage,"rel") >=0 || rindex($usage,"step")>=0 ) 
&&  rindex ($ARGV[9],"nofile")  < 0 );


#else
#print  "\n\nNumber of rows retrieved: $nrows\nThe complete result is# in the file query_result.txt \n"  
#                     if ( rindex ($ARGV[9],"nofile")  < 0 );
print  "\n\nWARNING TRUNCATION the number of rows retrieved is the maximum allowed ($maxlines)\n\n" if ( $nrows >= $maxlines && rindex ($ARGV[9],"nofile")  < 0 );



###########################
#     PRINT ROOTFILE
###########################
#print "ANTALL RADER: ".$nrows."\n";

if ($nrows > 0){
   if ( rindex($ARGV[9],"gnuplot") > -1)    {&use_gnuplot(); exit;}
   if (rindex ($ARGV[9],"noplot") > -1 || rindex ($ARGV[9],"nofile") > -1) {print "\n"; exit;}


    print "\nYou are now in root.... exit with .q when finished
The standard plot will be automatically saved in gif format
It will be shown also after exiting root \n\n";
    &setupRoot();
    if (!-e "DCSgraph.C"){print "\n\n ERROR could not find file DCSgraph.C: \n\n"; die "by now";}
    
     my $plot = `root -l  .x DCSgraph.C`;
    #  my $plot = `root -l -b .x DCSgraph.C`;
    my $lastfile = `ls -tr *.gif | tail -n 1`;
    print "last file created was $lastfile\n";
    #$plot = `display $lastfile `;
    if (!defined($kidpid = fork())) {
    # fork returned undef, so failed
    die "Cannot fork: $!";
  } elsif ($kidpid == 0) {
    # fork returned 0, so this branch is child
    exec("display $lastfile &");
    print "Perl child process now exiting\n";
    exit(0);
    # if exec fails, fall through to the next statement
    die "can't exec display: $!";
  } else {
    # fork returned 0 nor undef
    # so this branch is parent
    # do nothing
    waitpid($kidpid, 0);
    print "Display launched, now parent process exiting\n\n";
    

    exit;
  }
}else{

    print "NO FILE TO PLOT \n";
}
##################
#   END ROOTFILE 
##################



# cleanup gnuplot file
#$xx = `/bin/rm $plotComFile`;

exit;

#================== use gnuplot ==================

sub use_gnuplot {

# now building the file to plot the data using gnuplot
print "Using gnuplot\n";

local $plotComFile = "plot_command.plt";
open (PLOTFILE,"> $plotComFile") or die "could not open $plotComFile $!\n";

local $dpnm = "";
if ($brm[0] ne "") {$dpnm = "Barrel".$brm[0]."_Row".$brm[1]."_Module".$brm[2];}




print PLOTFILE "#!/usr/local/bin/gnuplot -persist
set title '$dpnm From $stadate $statime to $lastdate $lasttime UTC'
set xlabel 'Time in UTC'
set xdata time
set timefmt '\%Y-\%m-%d-\%H-\%M-\%S'
set format x   '\%H:\%M:\%S'\n";

   local $continua = "plot";
   foreach  $onefile  (@filesToPlot) {
    #print "filesto plot @filesToPlot\n";
      $myelement = shift(@elementArry);
            print "Results for DPE $myelement \n";
      print PLOTFILE " $continua '$onefile' using 2:1 title '', 'second_$onefile' using 2:1 title '$myelement' with linespoints";
      $continua = ",\\\n";
  }
print PLOTFILE "\n";

close(PLOTFILE);

# display the result via gnuplot in batch mode
local $plot = `gnuplot -persist $plotComFile`;

}

#================= fielder ============================


sub fielder(){
local @dpnam = @_;
$sth -> execute or die "While Executing: ",$sth::errstr;

my $previousvalue;
my $previousdpe;
local @field=();
my @time_stamp;
my $numberdpe = 1;
#my @elementArry =();
print  "$dp_alias_desc\n" if ( (rindex ($usage,"plot") >=0 && 
                                rindex ($ARGV[9],"nofile") >= 0 && 
                                $tblnbr == 1 ) ||
			        $debug > 0 );
while (@field = $sth -> fetchrow_array) {
   $nrows = $nrows +1;
   $oneline = join("  ",@field);
   print "@field \n" if ($nrows < 10 && rindex ($ARGV[9],"nofile") < 0);

# in case of relational query but no web usage
   if ((rindex($usage,"rel") >=0 || rindex($usage,"step")>=0 || rindex($usage,"bit") >=0) && 
        rindex ($ARGV[9],"nofile") < 0) { print RESULT "@field \n";}

# in case of relational query or bitand query and web usage
   if  (($usage eq "rel" || $usage eq "relin" || $usage eq "aliasrel" ||  $usage eq "aliasrelin" ||
         $usage eq "descrelin"|| $usage eq "descrel" || rindex($usage,"bit") >=0 ||
         $usage eq "step" || $usage eq "aliastep" || $usage eq "descstep") && rindex ($ARGV[9],"nofile") >= 0) {
                 $oneline = join(";",@field); chomp $oneline; print "$oneline\n";
   }

#in case of text query
   if  (($usage eq "text" || $usage eq "aliastext" || $usage eq "desctext") && rindex ($ARGV[9],"nofile") < 0){
            $oneline = $field[2]." ".$field[3]."  ".$field[1]."\n" ; chomp $oneline; print RESULT "$field[0] UTC time\n\n" if ($nrows ==1);

   }

# in case of non relational query:
   if ($usage ne "rel" && $usage ne "aliasrel" && $usage ne "descrel" &&  $usage ne "descrelin" && $usage ne "step" && 
       $usage ne "aliastep" && $usage ne "descstep" && $usage ne "relin"&&  $usage ne "aliasrelin"  &&  rindex($usage,"bit") < 0) {

    if ($nrows == 1) {$DPelement = $field[0];$previousvalue = $field[1]; $previousdpe = $DPelement;
                      push(@elementArry,$DPelement); 
                      #print "DP element $DPelement\n"; 
                      @dummy = split(/\./,$DPelement);
                      pop(@dummy); 
                      $DPelement = join(".",@dummy);
                      $DPelement =~ s /\//./g; 
		      print TOPLOT "## $DPelement from $stadate $statime to $stodate $stotime\n" if ( rindex ($ARGV[9],"noplot")  < 0 );
    }



    @time_stamp = split (/\s+/, $field[2]);
    @date_array = split (/\-/,$time_stamp[0]);
    #@time_array = split(/:/,$field[3]);
    @time_array = split(/:/,$time_stamp[1]);
    $value = $field[1];
    $cur_dpe =  $field[0];
    $cur_day  =  $date_array[0];
    $cur_mon  =  $date_array[1];
    $cur_year =  $date_array[2];
    $cur_hour =  $time_array[0];
    $cur_min  =  $time_array[1];
    $cur_sec  =  $time_array[2];
    $cur_milli = $time_array[3] || "002";
    #print "time Array @time_array \n" if ($debug > 0);
    $seconds_cur  = timegm($cur_sec, $cur_min, $cur_hour, $cur_day, ($cur_mon-1), ($cur_year-1900))+
                   ($cur_milli/1000.);
    $seconds = $seconds_cur - $seconds_start;
    $previoustime = $seconds_cur - 1.;


    my @plottime = ($cur_year,$cur_mon,$cur_day,$cur_hour,$cur_min,$cur_sec);
    my $plotime = join("-",@plottime);
    # missing conversion of secondsprevious to time.
    $ttm = gmtime($previoustime);
    my @previous_t = (($ttm->year)+1900,  ($ttm->mon)+1,  $ttm->mday, $ttm->hour, $ttm->min, $ttm->sec  );
    my $plotprev_t = join("-",@previous_t);
    $previous_t[1] = sprintf("%02d",$previous_t[1]);
    $previous_t[2] = sprintf("%02d",$previous_t[2]);
    $previous_t[3] = sprintf("%02d",$previous_t[3]);
    $previous_t[4] = sprintf("%02d",$previous_t[4]);
    $previous_t[5] = sprintf("%02d",$previous_t[5]);
    $prevtime = $previous_t[2]."-".$previous_t[1]."-".$previous_t[0]." ".$previous_t[3].":".$previous_t[4].":".$previous_t[5].":".$cur_milli;
    #print "$previoustime  $plotprev_t\n" if  ($nrows < 10);
    if ($previousdpe ne $cur_dpe){
       print RESULT "\n" if ( rindex ($ARGV[9],"nofile")  < 0 );
       close(TWOPLOT);
       close(TOPLOT);
       $numberdpe = $numberdpe+1;
       $plotfile = "plot_db_".$numberdpe.".dat";
       $twoplotfile = "second_plot_db_".$numberdpe.".dat";
       open(TOPLOT,"> $plotfile") or die "could not open $plotfile $!\n" if ( rindex ($ARGV[9],"nofile")  < 0 );
       open(TWOPLOT,"> $twoplotfile")  or die "could not open $twoplotfile $!\n"if ( rindex ($ARGV[9],"nofile")  < 0 );
       $previousdpe = $cur_dpe;
       push (@elementArry, $cur_dpe);
       push (@filesToPlot, $plotfile);
    }

##
#  Case of no file, i.e. for the web browser
#  Limit the precision to 3 deecimal places, except for MAG and RAD
##
    if ( rindex ($ARGV[9],"nofile") > -1 ){
       #$millis = $cur_milli - 1;
#       $rounded = sprintf("%.3f", $previousvalue);
#       print "$cur_dpe $rounded $prevtime\n";
       $rounded = sprintf("%.3f", $value);
       $rounded = sprintf("%.6f", $value) if (rindex($cur_dpe,"MAG")>= 0 ||rindex($cur_dpe,".Current")>= 0 || 
                                              rindex($cur_dpe,"IDERAD")>= 0) ;
       $rounded = sprintf("%.8f", $value) if (rindex($cur_dpe,"Occupancy")>= 0 || rindex($cur_dpe,"SCTROS")>= 0);

       #print  "$dp_alias_desc\n" if ($nrows == 1);
       print "$rounded $cur_day-$cur_mon-$cur_year $cur_hour:$cur_min:$cur_sec:$cur_milli\n";
    }
# end of web browser case



    print RESULT "$oneline \n" if ( rindex ($ARGV[9],"nofile")  < 0 );
    print TWOPLOT "$previousvalue $plotprev_t\n" if ( rindex ($ARGV[9],"nofile")  < 0 );
    #print TWOPLOT "$value $seconds\n";
    print TWOPLOT "$value  $plotime\n" if ( rindex ($ARGV[9],"nofile")  < 0 );
#    print TOPLOT "$value $seconds\n";
    print TOPLOT "$value  $plotime\n" if ( rindex ($ARGV[9],"nofile")  < 0 );
    $previousvalue = $value;
  }
 }

my $lastdate = join(" ",@time_stamp);
#print RESULT "\n\nNumber of rows retrieved: $nrows\n for $numberdpe Data Point Elements\n" if ( rindex ($ARGV[9],"nofile")  < 0 );
return $nrows;

}


#=======================================================================

sub setupRoot {

$ENV{'ROOTSYS'} = "/afs/cern.ch/sw/lcg/external/root/5.16.00/slc4_ia32_gcc34/root";
$ENV{'PATH'}=$ENV{'PATH'}.":".$ENV{'ROOTSYS'}."/bin";
$ENV{'LD_LIBRARY_PATH'}= $ENV{'LD_LIBRARY_PATH'}.":".$ENV{'ROOTSYS'}."/lib";

return;
}

#=======================================================================


sub timedate(){

# checks that time and date entered are in a good format and make sense.

#$stadate=~s/\D+//;
$goodstadate = ParseDate($stadate);
 if (!$goodstadate) {$stadate = "";}
 while(!$stadate)
{print "enter start date (dd-mm-yyyy): ";
 $stadate= <STDIN>; chomp $stadate;
$goodstadate = ParseDate($stadate);
if (!$goodstadate) {print "Start date in bad format $stadate\n\n$examples\n"; $stadate= "";}
}

#$statime=~s/\D+//;
$goodstatime = ParseDate($statime);
 if (!$goodstatime) {$statime = "";}
 while(!$statime)
{print "enter start time (hh:mm): ";
 $statime= <STDIN>; chomp $statime;
$goodstatime = ParseDate($statime);
if (!$goodstatime) {print "Start time in bad format $statime\n\n$examples\n"; $statime= "";}
}
#print "\n".$goodstatime."\n";
#$stodate=~s/\D+//;
$goodstodate = ParseDate($stodate);
 if (!$goodstodate) {$stodate = "";}
 while(!$stodate)
{print "enter stop date (dd-mm-yyyy): ";
 $stodate= <STDIN>; chomp $stodate;
$goodstodate = ParseDate($stodate);
if (!$goodstodate) {print "Stop date in bad format $stodate\n\n$examples\n"; $stodate= "";}
}
#print "\n".$goodstodate."\n";
#$stotime=~s/\D+//;
$goodstotime = ParseDate($stotime);
 if (!$goodstotime) {$stotime = "";}
 while(!$stotime)
{print "enter stop time (hh:mm): ";
 $stotime= <STDIN>; chomp $stotime;
$goodstotime = ParseDate($stotime);
if (!$goodstotime) {print "Stop time in bad format $stotime\n\n$examples\n"; $stotime= "";}
}
#print "\n".$goodstotime."\n";
#today's date
my $now_day, $now_month, $now_year;
$tm = gmtime;
($now_hour, $now_min, $now_sec, $now_day, $now_month, $now_year) = 
($tm->hour, $tm->min, $tm->sec, $tm->mday, ($tm->mon)+1, ($tm->year)+1900 );
my $now_seconds =  timegm($now_sec, $now_min, $now_hour, $now_day, ($now_month-1), ($now_year-1900));

#printf ("\n dcsDBaccess.pl version $version\n Date is now %02d-%02d-%04d %02d:%02d:%02d UTC\n", $now_day,$now_month,$now_year, $now_hour,$now_min,$now_sec) if ( rindex ($ARGV[9],"nofile")  < 0 ) ;

$gooddate = ParseDate($stadate);
if (!$gooddate) {die "Start date in bad format $stadate\n\n$examples\n";}
$gooddate = ParseDate($stodate);
if (!$gooddate) {die "Stop date in bad format $stodate\n\n$examples\n";}
$gooddate = ParseDate($stotime);
if (!$gooddate) {die "Stop time in bad format $stotime\n\n$examples\n";}
$gooddate = ParseDate($statime);
if (!$gooddate) {die "Start time in bad format $statime\n\n$examples\n";}

@date_array = split (/\-/,$stadate);
@time_array = split(/:/,$statime);

print "Start $stadate ; @date_array ; @time_array\n" if ($debug> 0);

$sta_day = $date_array[0];
$sta_mon = $date_array[1];
$sta_year =$date_array[2];
$sta_hour = $time_array[0];
$sta_min  = $time_array[1];
$sta_sec =  $time_array[2];


@date_array = split (/\-/,$stodate);
@time_array = split(/:/,$stotime);
print "Stop $stodate @date_array ; @time_array\n" if ($debug> 0);

$sto_day = $date_array[0];
$sto_mon = $date_array[1];
$sto_year =$date_array[2];
$sto_hour = $time_array[0];
$sto_min  = $time_array[1];
$sto_sec =  $time_array[2];



$seconds_start = timegm($sta_sec, $sta_min, $sta_hour, $sta_day, ($sta_mon-1), ($sta_year-1900));
$seconds_stop  = timegm($sto_sec, $sto_min, $sto_hour, $sto_day, ($sto_mon-1), ($sto_year-1900));

if ($seconds_start >= $now_seconds) {die "Invalid Start date in the future: $stadate $statime $now_seconds\n" 
                                     if (rindex($ARGV[9],"nofile")  < 0 ); die "";}
if ($seconds_stop >= $now_seconds) {
$date_array[0]  = $now_day ;
$date_array[1]  = $now_month ;
$date_array[2]  = $now_year ;
$time_array[0]  = $now_hour ;
$time_array[1]  = $now_min ;
$time_array[2]  = $now_sec ;
#$stodate = join("-",@date_array);
#$stotime = join(':',@time_array);

$stodate = sprintf("%02d-%02d-%04d", $now_day, $now_month,$now_year);
$stotime = sprintf("%02d:%02d:%02d", $now_hour, $now_min, $now_sec);


printf ("Stop date you entered is in the future: replaced with present %02d-%02d-%04d %02d:%02d:%02d UTC\n", 
                     $now_day,$now_month,$now_year, $now_hour,$now_min,$now_sec) 
                     if ( rindex ($ARGV[9],"nofile")  < 0 ) ;

$seconds_stop = $now_seconds;
}

$time_difference = $seconds_stop - $seconds_start;

if ($time_difference >  5270400.) { #3456000.){# 864000. ){ #345600.) {   time limit 61 days
print "Too large time span:  $time_difference seconds
Max is 40 days. Try splitting in two queries"  if ( rindex ($ARGV[9],"nofile")  < 0 ) ;
die "Too much time Exiting from dcsDBaccess \n\n";
				}
if ($time_difference <10.) {die "Too small or negative time interval $time_difference seconds\n\n";}
$minn = sprintf("%10.3f",$time_difference / 60.);
$hours= sprintf("%8.5f", $time_difference / 3600.);
#print " The requested time span is $time_difference seconds or $minn minutes or $hours hours \n" if ( rindex ($ARGV[9],"nofile")  < 0 );

#print " This version can be used only for accessing the new Oracle schema from PVSS 3.6 SP-1 from June 11 2007\n\n";

return;
}

sub get_table_names {
#############################################################################################
#                                                                                           #
# Given a time interval, it returns the name of the Oracle table(s) containing the data     #
#                                                                                           #
# Saverio D'Auria - Sept 2007                                                               #
#############################################################################################

local @dates = @_;
local $stadat = $dates[0];
local $statim = $dates[1];
local $stodat = $dates[2];
local $stotim = $dates[3];
local $system = $dates[4];
local @result;             

if ($DB eq "TSCV") {print "returning TSCV" if ($debug > 0) ;return ""};

local $tablequery = "select 'EVENTHISTORY_'||lpad (archive#,8,'0') from
(
        select archive#, start_time, end_time
        from atlas_pvss".$system.".arc_archive A
        where A.group_name = 'EVENT' and
              (A.end_time > to_date('" . $stadat . " " . $statim . "','DD-MM-YYYY HH24:MI:SS') or A.end_time is null)
        intersect
        select archive#, start_time, end_time
        from atlas_pvss".$system.".arc_archive A
        where A.group_name = 'EVENT' and
              A.start_time < to_date('" . $stodat . " " . $stotim . "','DD-MM-YYYY HH24:MI:SS')
)"; 

  print "$tablequery\n\n" if ($debug > 0)  ;
# PREPARING
  $sth = $dbh->prepare ($tablequery)
  or die "Preparing:", $dbh::errstr;
# EXECUTING
  $sth -> execute or die "Executing: ",$sth::errstr;
# PARSING
  push(@resultArray,"$exdpe:\n");
  while (@field = $sth -> fetchrow_array) {
    push(@result,@field);
  }
 ###print @result; 
 print "  This is the pending table(s)  @result \n"if ($debug > 0)  ;
 return @result;


}

#============================ Get DB from Element Name =====================================

sub get_DB_from_element_name(){
#
# Author: Urban Bitenc, Freiburg University
# Date: 24.11.2008
# this function corresponds to the status of databases on 24.11.2008
#

local $mElement_name = $_[0];

local $mDBname = "";

local $mSubStr = substr($mElement_name, 3, 3);

# for the following 11 systems the name is easy:
if( $mSubStr eq "IDE" ||
    $mSubStr eq "TRT" ||
    $mSubStr eq "SCT" ||
    $mSubStr eq "PIX" ||
    $mSubStr eq "CSC" ||
    $mSubStr eq "LAR" ||
    $mSubStr eq "MDT" ||
    $mSubStr eq "RPC" ||
    $mSubStr eq "TDQ" ||
    $mSubStr eq "TGC" ||
    $mSubStr eq "ZDC" ||
    $mSubStr eq "TIL"   )                        {$mDBname = $mSubStr;}

elsif(substr($mElement_name, 0, 6)  eq "ATLCIC" ||
      substr($mElement_name, 0, 9)  eq "ATLGCSIS:" ||
      substr($mElement_name, 0, 9)  eq "ATLGCSLHC" ||
      substr($mElement_name, 0, 9)  eq "ATLGCSSYS")  {$mDBname = "DCS";}

elsif(substr($mElement_name, 0, 6) eq "ATLLCD")  {$mDBname = "LUC";}

elsif (substr($mElement_name, 0, 9) eq "ATLGCSIS1" || 
       substr($mElement_name, 0, 9) eq "ATLGCSIS2" ||
       substr($mElement_name, 0, 9) eq "ATLGCSIS3" ) {$mDBname = "IS";}

elsif (substr($mElement_name, 0, 10) eq "ATLGCSMUON") {$mDBname = "MUO";}

#elsif(substr($mElement_name, 0, 7) eq "PCSRSCT") {$mDBname = "SCT";}

## "System1" is ambigous: 61 dp in LAR and 4302 dp in DSS; get_DB_general is the only way to get the right system
elsif(substr($mElement_name, 0, 7) eq "System1") {$mDBname = "DSS";}
#{$mDBname = get_DB_general($mElement_name, "element_name");}

else {print "not recognized system for $mElement_name substr $mSubStr \n" if ($debug> 0); return "";}

print "$mDBname, $mElement_name (from get_DB_from_element_name)\n" if($debug > 0);

return $mDBname;
}



sub subscribe {
#This is to automatically subscribe
local $user=`whoami`;
return if (rindex($user,"apache") >=0);
if (!-e "textdump_sub.txt" && rindex ($user,"dauria") < 0) {
 `touch textdump_sub.txt`;

 $user_address = $user."\@cern.ch";
 $mailer = Mail::Mailer->new("sendmail");
 $mailer->open({To => "saverio.dauria\@cern.ch", From => $user_address, Subject => "Subscription to dcsDBaccess"})
 or die "can not open $!\n";
 print $mailer "First use of dcsDBaccess.pl $version $dateversion by $user";
 $mailer->close();
 print " Sent a subscription e-mail \n"  if ( rindex ($ARGV[9],"nofile")  < 0 ) ;
}

return;
}


sub get_alias_comment {
local $res;
local $sysid;
local @param = @_;
local $what = $param[0];

$like = $param[1];

if ($what ne "alias" && $what ne "element_name" && $what ne "comment_" ) {print "get alias comment Incorrect_usage_$what\n" if ($debug > 0); die;}

local $nn = 0;
print "now looking for element name alias comment_ for $what like $like\n" if  ($debug > 0);

if (rindex($like,"Evap.") >= 0) {print "TS/CV datapoint $like\n" if ($debug>0); 
                                 return $like.";;"; }

local $maxlin = 1;
local $query_wild =
"select distinct element_name, alias, comment_, sys_id
 from
  (select  element_name, alias, comment_, sys_id  from atlas_pvsssct.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvsside.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvsspix.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvsstrt.elements
    union all select element_name, alias, comment_, sys_id from atlas_pvssdcs.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvssluc.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvsstil.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvsstgc.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvsstdq.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvssmdt.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvssmuo.elements
    union all select element_name, alias, comment_, sys_id from atlas_pvssrpc.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvsslar.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvsscsc.elements 
    union all select element_name, alias, comment_, sys_id from atlas_pvssdss.elements
    union all select element_name, alias, comment_, sys_id from atlas_pvssis.elements
    union all select element_name, alias, comment_, sys_id from atlas_pvsszdc.elements
  ) el
where ( el.element_name not like 'PCSRSCTDC\%' and  el.element_name not like '\%UserName\%' 
and el.element_name not like '\%Example\%' and el.element_name not like '\%.' and el.".$what." like '".$like."'
and  ROWNUM <= ".$maxlin.")
order by ".$what;
# union all select * from atlas_pvssdcs.elements 
print "Query wild card:\n $query_wild \n\n\n" if ($debug > 0);


#   PREPARING
    $sth = $dbh->prepare ($query_wild)
    or die "Preparing:", $dbh::errstr;
#   EXECUTING
    $sth -> execute or die "Executing: ",$sth::errstr;
#   PARSING
    push(@resultArray,"\n$exdpe:\n");
    while (@field = $sth -> fetchrow_array) {
         $res = $field[0].";".$field[1].";".$field[2].";";
         $sysid = $field[3]; 
         $toprint = join(@field," -- ");
         print "Field fetched: @field :--- $toprint \r\n " if ($debug > 0);
         $nn++;
         @elalcosys = @field;
       }

if ($nn == 0) { print "\nNo $what like $like was found. Exiting\n" if (!$batch); &closenicely();}
return (@elalcosys);
}

sub closenicely{
  $sth->finish();
  $dbh->disconnect();
  exit;
}


sub steps {
local @param = @_;
local $what = $param[0];
local $like = $param[1];
local $stepsize = $param[2];
local $maxstepsize = $param[3] || 100 ;
local $invalid_min = $param[4] || -280;

print "Steps: $what $like ,$searchstring,$sys_nums, Step > $stepsize, $maxstepsize, $invalid_min \n" if ($debug > 0) ;

# global variables used here:
#$DB,$tbl,$searchstring,$sys_nums, $stadate, $statime $stodate, $stotime

local $query_steps =
"SELECT  unique el_name , el_al, el_desc  from
  (
    SELECT B.element_name el_name, B.alias el_al, B.comment_ el_desc, A.TS,
           to_number(A.VALUE_NUMBER) valu,
           to_number(LAG(A.VALUE_NUMBER,1,".$invalid_min.") 
             over (PARTITION BY B.ELEMENT_ID order by A.TS)) value_before
    FROM ATLAS_PVSS".$DB.".".$tbl. " A,
         (SELECT ELEMENT_ID, ELEMENT_NAME , ALIAS, COMMENT_ FROM ATLAS_PVSS".$DB.".ELEMENTS
WHERE ".$what." ".$like." '".$searchstring."') B
    where A.ELEMENT_ID = B.ELEMENT_ID and
   A.TS BETWEEN TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI:SS' ) AND
                TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI:SS' )
   AND A.VALUE_NUMBER <> 'NaN'AND  A.VALUE_NUMBER <> BINARY_FLOAT_INFINITY 
 )
 where abs(valu-value_before) > ".$stepsize." and value_before > ".$invalid_min." 
   and abs(valu-value_before) < ".$maxstepsize."
 order by el_name";

print "Query Steps: \n$query_steps\n"   if ($debug > 0)  ;

  $sth = $dbh->prepare ($query_steps)
  or die "Preparing:", $dbh::errstr;
  &fielder();


  return;
}

#  ================ bit and search ===============
#
#
sub bitandsearch {
local $word = $_[0];
local $resu = $_[1];
local $system = $DB;
local $sstring = $searchstring;
local $logop = " = ";

## standard case: if second parameter is negative search any bitand
## else check exact match
if ($resu < 0) {$resu = 0; $logop = " > ";} 

print "Bit AND search parameters DB $DB string $searchstring sys_ids $sys_nums table $tbl bitand $word \n" if ($debug > 0) ;
# global variables used here:
#$DB,$tbl,$searchstring,$sys_nums, $stadate, $statime $stodate, $stotime

local $BitAndQuery =
"SELECT  UNIQUE B.ELEMENT_NAME, B.ALIAS, B.COMMENT_ 
FROM     ATLAS_PVSS".$system.".".$tbl." A,
   ATLAS_PVSS".$system.".elements B
WHERE A.ELEMENT_ID = B.ELEMENT_ID
AND  A.ELEMENT_ID IN
   ( SELECT ELEMENT_ID
       FROM ATLAS_PVSS".$system.".ELEMENTS
       WHERE ".$searchwhat." LIKE '\%".$sstring."\%' AND ALIAS IS NOT NULL)
  AND TS BETWEEN TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI:SS')
             AND TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI:SS')
  AND (BITAND(TO_NUMBER(A.VALUE_NUMBER),".$word.")".$logop .$resu.")
  ORDER BY B.".$searchwhat;

print "Query BitAnd: \n$BitAndQuery\n"   if ($debug > 0)  ;

  $sth = $dbh->prepare ($BitAndQuery)
  or die "Preparing:", $dbh::errstr;
  &fielder();

}
#================================================================

#========================== trip search =========================
#
#
#================================================================

sub tripsearch {
# this is never used: Atlas SCT specific
# global variables used here:
#$DB,$tbl,$searchstring,$sys_nums, $stadate, $statime $stodate, $stotime

local $StateQuery =
"SELECT  B.ELEMENT_NAME, B.ALIAS, B.COMMENT_ , ROUND(A.VALUE_NUMBER), TO_CHAR(A.TS,'DD-MM-YYYY HH24:MI:SS:FF3')
FROM     ATLAS_PVSS".$system.".".$tbl." A,
   ATLAS_PVSSSCT.elements B
WHERE A.ELEMENT_ID = B.ELEMENT_ID
AND  A.ELEMENT_ID IN
   ( SELECT ELEMENT_ID
       FROM ATLAS_PVSS".$system.".ELEMENTS
       WHERE SYS_ID IN(".$sys_nums.") AND  ELEMENT_NAME LIKE '\%".$sstring."\%')
  AND TS BETWEEN TO_DATE('" . $stadate . " " . $statime . "','DD-MM-YYYY HH24:MI:SS')
             AND TO_DATE('" . $stodate . " " . $stotime . "','DD-MM-YYYY HH24:MI:SS')
  AND (BITAND(TO_NUMBER(A.VALUE_NUMBER),240)=160 OR BITAND(TO_NUMBER(A.VALUE_NUMBER),240)=176
       OR BITAND(TO_NUMBER(A.VALUE_NUMBER),15)=10 OR BITAND(TO_NUMBER(A.VALUE_NUMBER),15)=11)
  AND A.VALUE_NUMBER <> 'NaN' AND A.VALUE_NUMBER <> BINARY_FLOAT_INFINITY 
  ORDER BY B.ELEMENT_NAME, A.TS";

print "Query Trips: \n$query_steps\n"   if ($debug > 0)  ;

  $sth = $dbh->prepare ($StateQuery)
  or die "Preparing:", $dbh::errstr;
  &fielder();

}



#================= Decode Channel Status =============
#
#                   ATLAS SCT specific
#
#=====================================================
sub decode_State {

local @w = @_;
local $word = $w[0];
local $HVword = ($word & 240);  # take the upper 4 bits F0


local $LVword = $word & 15;  # take the lower 4 bits 0F
local $resultH = "";
local $resultL = "";
local $result = "";

printf("Decoding   %2x   %2x %2x \n", $word, $HVword, $LVword )  if ($debug > 0) ;

switch ($HVword) {
   case 0 { $resultH = "HV OFF";}
   case 16  { $resultH = "HV ON";}
   case 32  { $resultH = "HV Stand By";}
   case 48  { $resultH = "HV Manual";}
   case 64  { $resultH = "HV Mask OFF";}
   case 80  { $resultH = "HV Mask ON";}
   case 160 { $resultH = "HV Hardware Trip";}
   case 176 { $resultH = "HV Software Trip";}
   case 208 { $resultH = "HV Mismatch";}
   case 224 { $resultH = "HV UNKNOWN";}
 }

switch ($LVword){
   case 0 { $resultL = "LV OFF";}
   case 1 { $resultL = "LV ON";}
   case 2 { $resultL = "LV Stand By";}
   case 3 { $resultL = "LV Manual";}
   case 4 { $resultL = "LV Mask OFF";}
   case 5 { $resultL = "LV Mask ON";}
   case 6 { $resultL = "LV Hard Reset";}
   case 10 { $resultL = "LV Hardware Trip";}
   case 11 { $resultL = "LV Software Trip";}
   case 12 { $resultL = "LV Card latch";}
   case 13 { $resultL = "LV Mismatch";}
   case 14 { $resultL = "LV UNKNOWN";}
}

$result = $resultH." ".$resultL;
return $result;

}


