#!/bin/sh
exec perl -Sx $0 "$@"

#!perl
#    Time-stamp: <2002-09-20 21:28:52 takeuchi>
    use strict;
# runlist.pl
#
# This script accesses to ORACLE DB and fetch various information
# of specified runs.
# Usage:
#   runlist.pl from to
#
my @items=qw(
	     run date atime etime l1a l2a l3a tevstore
	     datatype intlivelum trigger usesrc runtype
	     lumbegin lumend
	     );
my ($from, $to, $sqlscript, @elements, %info);

#
#  treat Arguments
#
if ( scalar(@ARGV) == 1 ) {
    ( $from, $to )=@ARGV[0,0];
} elsif ( scalar(@ARGV) == 2 ) {
    ( $from, $to )=@ARGV[0,1];
} else {
    print <<EOF;
usage: runlist.pl from to
EOF
    exit;
}

# define temporary file for SQL script
$sqlscript="/tmp/sqlrun.$ENV{USER}.$$.sql";
# generate SQL script
open( fh, ">$sqlscript" );
print fh <<EOT;
SET NEWPAGE 0;
SET SPACE 0;
SET LINESIZE 4096;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;

SELECT
   RC.RUNNUMBER RUN, ',',
   TO_CHAR(RC.CREATETIME,'YYYY.MM.DD') DAY, ',',
   TO_CHAR(RC.ACTIVATETIME,'HH24:MI:SS') ACTIVE, ',',
   TO_CHAR(RC.TERMINATETIME,'HH24:MI:SS') END, ',',
   RC.L1ACCEPTS L1A, ',',
   RC.L2ACCEPTS L2A, ',',
   RC.CSL_NEVENTSOUTPUT L3A, ',',
   RC.TEVSTORE TEVSTORE, ',',
   DT.DESCRIPTION, ',',
   TO_CHAR(RC.B0INTEGRATEDLIVELUMI_TERMINATE-
	    RC.B0INTEGRATEDLIVELUMI_ACTIVATE,'9990.0000'), ',',
   RS.PHYSICSTABLENAME, ',',
   RS.USESRC, ',',
   RT.DESCRIPTION, ',',
   TO_CHAR(RC.B0LUMINOSITY_ACTIVATE,'999,990.000') LUMI_BEGIN, ',',
   TO_CHAR(RC.B0LUMINOSITY_TERMINATE,'999,990.000') LUMI_END
   FROM
     RUNCONFIGURATIONS RC,
     RUNSETS RS,
     DATATYPES DT,
     RUNTYPES RT
   WHERE
     RC.RUNNUMBER>=$from AND RC.RUNNUMBER<=$to AND
     RS.NAME=RC.RS_NAME AND
     RS.DATATYPE=DT.DATATYPE AND
     RS.RUNTYPE=RT.RUNTYPE
   ORDER BY RUNNUMBER;
quit;
EOT

close(fh);

# open pipe to result of SQL script using sqlplus
# $BFDIST/oracle_tnsnames/tnsnames.ora
open( fh, "sqlplus -s cdf_reader/reader\@cdfofprd \@$sqlscript|" );

while (<fh>) {
    # strip white spaces off line
    chomp; s/^\s*//; s/\s*$//;
    @elements=split(/\s*,\s*/, $_);
    # stock information into hash %info
    foreach (@items) { $info{$_}=shift @elements; }

    # now you can use %info
    # user treatment
    user_sub(\%info);
}

close(fh);

# delete temorary file
unlink $sqlscript;

#
# subroutine
#

#
# user defined subroutine
# here, what you want to do should be written.
# you can access data by $info->{item_name}
sub user_sub {
    my $info=shift;

    if ($info->{runtype}=~/^Physics/) {
	printf( "%6s %10s %8s %8s ",
		$info->{run}, $info->{date}, $info->{atime},
		$info->{etime} );
	( $info->{intlivelum} eq '' ) ? print '     null' :
	    printf("%9.4f", $info->{intlivelum});
	( $info->{lumbegin} eq '' ) ? print '     null' :
	    printf("%9.4f", $info->{lumbegin});
	print "\n";
    }
}
# EOF
