#!/usr/bin/env bash  


#------------------------------------------------------------------------------
connect () {
  
  query=$1
#  echo "db_queries:$LINENO" : query=$query
  sqlplus cdf_reader/reader@cdfofprd @$query > aaa.$$

  cat aaa.$$ | grep -vi oracle | \
        grep -v JServer | grep -v SQL | grep -v "Connected to" | \
        grep -v 'With the Partitioning' | \
        awk '{if ($0 != "") print $0}'

  if [ $? == 0 ] ; then rm aaa.$$
  else                   echo $LINENO ERROR: rc=$rc ; 
  fi
}

#------------------------------------------------------------------------------
describe_table() {
  table=$1;

query=$$.query
cat << EOF > $query
set heading off;
set feedback off;
set pagesize 0;
set verify off;
describe $table;
quit;
EOF

connect $query ; rc=$?
if [ $rc == 0 ] ; then rm $query
else                   echo $LINENO ERROR rc=$rc
fi

}

#------------------------------------------------------------------------------
print_jobset_id() {

  process_name=$1
     pass_name=$2
    run_number=$3

query=$$.query
cat << EOF > $query
set heading off;
set feedback off;
set pagesize 0;
set verify off;
select unique us.jobset from used_sets us, passes ps, passcalibs pc where 
       us.process_name='$process_name' and 
       us.process_name=ps.process_name and 
       ps.passname='$pass_name' and 
       '.'||ps.retired = '.' and 
       ps.pass_index=pc.pass_index and 
       us.proc_calib_version=pc.proc_calib_version and 
       us.process_run=$run_number;
quit;
EOF
# cat $query
connect $query ; rc=$?
if [ $rc == 0 ] ; then rm $query
else                   echo $LINENO ERROR rc=$rc
fi

}


#------------------------------------------------------------------------------
get_jobset () {

run_number=$1
pass=$2

query=$$.query
cat << EOF > $query
set heading off;
set feedback off;
set pagesize 0;
set verify off;
select  cus.jobset 
from    used_sets cus, passes cps, passcalibs cpsc  
where   cus.process_run=$run_number and cus.process_name=cps.process_name 
and     cus.process_run >= cpsc.lorun 
and     cus.process_run <= cpsc.hirun 
and     cus.proc_calib_version=cpsc.proc_calib_version 
and     cpsc.pass_index=cps.pass_index and cps.passname='$pass' 
and     decode(cps.retired,NULL,'N','Y') != 'Y';
quit
EOF
### select  cps.passname, cus.process_run , cus.jobset 

connect $query ; rc=$?
if [ $rc == 0 ] ; then rm $query
else                   echo $LINENO ERROR rc=$rc
fi

}




#-----------------------------------------------------------------------
print_calib_table() {

    table=$1
    process_name=$2
    pass_name=$3
    run_number=$4

jobset=`print_jobset_id $process_name $pass_name $run_number` ; rc=$?

if [ $rc == 0 ] ; then
  query=$$.query
  cat << EOF > $query
set heading off;
set feedback off;
set pagesize 0;
set verify off;
select tbl.* from $table tbl where tbl.cid in 
  (select crl.cid from calibrunlists crl where crl.cid in 
     (select srm.cid from set_run_maps srm where srm.jobset in $jobset)
  ) order by tbl.geomid ;
quit;
EOF

connect $query ; rc=$?
if [ $rc == 0 ] ; then rm $query
else                   echo $LINENO ERROR rc=$rc
fi

fi
}


#------------------------------------------------------------------------------
# get_beamline run_number pass 
#------------------------------------------------------------------------------
get_beamline() {

detector=$1
run_number=$2
pass=$3

jobset=`get_jobset $run_number $pass`
rc=$?;

if [ $rc == 0 ] ; then
  query=$$.query
  cat << EOF > $query
set heading off;
set feedback off;
set pagesize 0;
set verify off;
select beam.cid, beam.channelid, beam.beamx, beam.beamy, beam.flag0, beam.flag1 
from ${detector}beamposition  beam, set_run_maps csrm 
where beam.cid=csrm.cid  and  csrm.jobset=$jobset and beam.channelid=1 ;
quit;
EOF

connect $query ; rc=$?
if [ $rc == 0 ] ; then rm $query
else                   echo $LINENO ERROR rc=$rc
fi

fi
}


#------------------------------------------------------------------------------
get_list_of_rs_ranges() {

  export dataset=$1
  export min_run=-1       ; if [ .$2 != '.' ] ; then export min_run=$2 ; fi
  export max_run=10000000 ; if [ .$3 != '.' ] ; then export max_run=$3 ; fi

  query=$$.get_list_of_files.query

cat << EOF > $query
set pagesize  0 
set heading   off
column lum_min format 9999999999990
column lum_max format 9999999999990
column file_name format a20
select lum_min, lum_max, file_name , event_count
from data_files 
join data_files_lumblocks using(file_id)       
join data_files_param_values using(file_id)
where param_value_id in (              
  select param_value_id             
  from param_values                 
  where param_value = '$dataset' 
    and param_type_id in (       
     select param_type_id    
     from param_types        
     where param_type = 'dataset'
       and param_category_id in (
             select param_category_id
             from param_categories   
             where param_category = 'cdf')))
order by lum_min, lum_max, file_name ;
quit;
EOF

# cat $query
connect $query | awk -v LowRun=$min_run -v HighRun=$max_run '{ 
  run=$1/65536;
  if (run >= LowRun) {
    if (run < HighRun+1) print $0
  }
}'

rm $query
}


#    and rc.b0integratedlumi >= 10.0
#    and rs.usefred = 1
#    and rst.shiftcrew_status = 1
#    and rst.runcontrol_status = 1
#    and lower(rs.physicstablename) like '%physic%'
#    and lower(rs.physicstablename) not like '%test%'
#    and rc.csl_neventsoutput >= 1000

#------------------------------------------------------------------------------
get_list_of_files() {

  export dataset=$1
  export min_run=-1       ; if [ .$2 != '.' ] ; then export min_run=$2 ; fi
  export max_run=10000000 ; if [ .$3 != '.' ] ; then export max_run=$3 ; fi

#   echo 'dataset min_run, max_run= '  $dataset $min_run $max_run

  query=$$.get_list_of_files.query

cat <<EOF > $query
set linesize 1000;
set heading off;
column FILE_NAME format A20
column FILE_SIZE_IN_BYTES format 9999.999999EEEE
select * from (
SELECT /*+ RULE; */ SMDF.FILE_NAME, 
	            SMDF.FILE_SIZE_IN_BYTES,
		    SMDF.EVENT_COUNT,
/* low run  */
                    ( SELECT MIN(SMR.RUN_NUMBER) FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) FIRSTRUN, 
                    SMDF.FIRST_EVENT_NUMBER, 
/* high run */
                   ( SELECT MAX(SMR.RUN_NUMBER) FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) LASTRUN,
/* high event */
                    SMDF.LAST_EVENT_NUMBER
/* */
                     FROM  DATA_FILES  SMDF
                     WHERE SMDF.FILE_ID IN ( SELECT FILE_ID FROM DATA_FILES DF 
                                                            JOIN DATA_FILES_PARAM_VALUES DFPV USING(FILE_ID) 
                                                            JOIN PARAM_VALUES PV              USING(PARAM_VALUE_ID) 
                                                            JOIN PARAM_TYPES PT               USING(PARAM_TYPE_ID) 
                                                            JOIN PARAM_CATEGORIES PC          USING(PARAM_CATEGORY_ID)
                                             WHERE PC.PARAM_CATEGORY = 'cdf' AND 
                                                   PT.PARAM_TYPE = 'dataset' AND 
                                                   PV.PARAM_VALUE = '$dataset')
/* */
                     ORDER BY SMDF.FILE_NAME )
      where (firstrun >= $min_run and firstrun <= $max_run) or (lastrun >= $min_run and lastrun <= $max_run) ;
exit;
EOF
# cat $query
connect $query  | grep ${dataset:1:4}
rc=$?

if [ $rc == 0 ] ; then rm $query
else                   echo $LINENO ERROR rc=$rc
fi

}


#------------------------------------------------------------------------------
# get number of events in the files of a given $dataset which (files) fall 
# into run range $min_run , $max_run
#-------------------------------
get_nevents_in_files() {
  export dataset=$1
  export min_run=-1       ; if [ .$2 != '.' ] ; then export min_run=$2 ; fi
  export max_run=10000000 ; if [ .$3 != '.' ] ; then export max_run=$3 ; fi

  get_list_of_files $dataset $min_run $max_run | \
  awk 'BEGIN{n=0} { n = n+$3} END{print n}'
}

#------------------------------------------------------------------------------
# call:    stntuple_query dsid fileset
# example: stntuple_query edil0h 000001
# not sure what happens when fileset name is not defined...
#------------------------------------------------------------------------------
stntuple_query() {
  query=$$.stntuple_query
  dataset=$1
  snapshot=$2;
  if [ .$snapshot != "." ] ; then project_def=$dataset.$snapshot 
  else                            project_def=$dataset
  fi

  cat <<EOF > $query
set linesize 500;
set heading off;
column location_id        format a40;
column file_name          format a20;
column proj_def_name      format a20;
column full_path          format a60;
column first_event_number format 999999999 ;
column last_event_number  format 999999999 ;
column proj_snap_version  format 99999 ;
select smpjd.proj_def_name, smdf.file_name, smdf.file_size_in_bytes fsize, 
 1 /* smdf.create_date */, smdf.event_count, 
(select min(smr.run_number) from data_files_runs smdfr, runs smr  
 where smdf.file_id = smdfr.file_id and smdfr.run_id = smr.run_id) low_run, 
smdf.first_event_number loevt, 
(select max(smr.run_number) from data_files_runs smdfr, runs smr  
 where smdf.file_id = smdfr.file_id and smdfr.run_id = smr.run_id) high_run, 
smdf.last_event_number hievt, sl.full_path 
from  project_definitions smpjd, project_snapshots smpjsnp, project_files smpjf, 
      data_files smdf, data_storage_locations sl, data_file_locations dfl 
where smpjd.proj_def_id = smpjsnp.proj_def_id and smpjsnp.proj_snap_id = smpjf.proj_snap_id and
      smpjf.file_id = smdf.file_id and smpjd.proj_def_name = '$project_def' and
      smdf.file_id = dfl.file_id and dfl.location_id = sl.location_id ;
exit;
EOF
# cat $query
connect $query | grep $project_def
rc=$?

if [ $rc == 0 ] ; then rm $query
else                   echo $LINENO ERROR rc=$rc
fi

  export run_number=$1
  query=$$.get_l3_tag.query
  cat << END > $query 
set linesize 1000;
set heading off;
select RC.RUNNUMBER, L3.TCL_TAG 
from  RUNCONFIGURATIONS RC, RUNSETS RS, VALID_TAG_SETS VTS, L3_TCLS L3 
where RC.RS_NAME = RS.NAME and RS.L3TAGSET = VTS.L3TAGSET and VTS.TCL_TAG = L3.TCL_TAG 
and RC.RUNNUMBER=$run_number ;
exit;
END

connect $query | awk -v run=$run_number '{if ($1 == run) print $2}'
rc=$?

if [ $rc == 0 ] ; then rm   $query
else                   echo $LINENO ERROR rc=$rc
fi
}

#------------------------------------------------------------------------------
get_list_of_files_pattern() {
  pattern=$1
  $connect << END | awk '{ if ($0 != "") print $0}'
set linesize 1000;
set heading off;
column FILE_NAME format A20
column FILE_SIZE_IN_BYTES format 9999.999999EEEE
SELECT /*+ RULE; */ SMDF.FILE_NAME, 
	            SMDF.FILE_SIZE_IN_BYTES,
		    SMDF.EVENT_COUNT,
                    SMDF.FIRST_EVENT_NUMBER, 
/* low run  */
                   ( SELECT MIN(SMR.RUN_NUMBER) FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) FIRSTRUN, 
/* high event */
                    SMDF.LAST_EVENT_NUMBER,
/* high run */
                   ( SELECT MAX(SMR.RUN_NUMBER) FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) LASTRUN
/* */
                     FROM  DATA_FILES  SMDF
                     WHERE SMDF.file_name like '$pattern'
/* */
                     ORDER BY SMDF.FILE_NAME ;
exit;
END
}

#------------------------------------------------------------------------------
# call: get_l3_tag  run number
#------------------------------------------------------------------------------
get_l3_tag() {

  export run_number=$1
  query=$$.get_l3_tag.query
  cat << END > $query 
set linesize 1000;
set heading off;
select RC.RUNNUMBER, L3.TCL_TAG 
from  RUNCONFIGURATIONS RC, RUNSETS RS, VALID_TAG_SETS VTS, L3_TCLS L3 
where RC.RS_NAME = RS.NAME and RS.L3TAGSET = VTS.L3TAGSET and VTS.TCL_TAG = L3.TCL_TAG 
and RC.RUNNUMBER=$run_number ;
exit;
END

connect $query | awk -v run=$run_number '{if ($1 == run) print $2}'
rc=$?

if [ $rc == 0 ] ; then rm   $query
else                   echo $LINENO ERROR rc=$rc
fi
}

#-----------------------------------------------------------------------
#        sam create dataset definition \
#        --defname="high_pt_muons_1" \
#        --group=test \
#        --defdesc="High pt muons before the August 2004 shutdown" \
#        --dim="CDF.DATASET bhmu0d and RUN_NUMBER <= 186598"
#
#        sam take snapshot --defname="high_pt_muons_1" --group=test
# sewk8d.def.0001
#-----------------------------------------------------------------------
select_snapshot() {
  snapshot=$1

  query=$$.select_snapshot.query
  cat << END > $query
set linesize 1000;
set heading off;
column PROJ_DEF_NAME format A30
SELECT SMPJD.PROJ_DEF_NAME, SMPJSNP.PROJ_SNAP_VERSION, SMPJSNP.PROJ_SNAP_ID
FROM PROJECT_DEFINITIONS SMPJD, PROJECT_SNAPSHOTS SMPJSNP 
WHERE SMPJD.PROJ_DEF_NAME = '$snapshot' and SMPJD.PROJ_DEF_ID=SMPJSNP.PROJ_DEF_ID;
exit;
END

connect $query ; rc=$?
if [ $rc == 0 ] ; then rm $query
else                   echo $LINENO ERROR rc=$rc
fi

# /* */
# /*    , (SELECT COUNT(*) FROM PROJECT_FILES SMPJF WHERE SMPJSNP.PROJ_SNAP_ID = SMPJF.PROJ_SNAP_ID) FILECOUNT, 
#        (SELECT SUM(SMDF.FILE_SIZE_IN_BYTES) FROM DATA_FILES SMDF 
#                JOIN PROJECT_FILES SMPJF USING(FILE_ID) 
#                WHERE SMPJSNP.PROJ_SNAP_ID = SMPJF.PROJ_SNAP_ID) BYTECOUNT, 
#        (SELECT SUM(SMDF.EVENT_COUNT) FROM DATA_FILES SMDF JOIN PROJECT_FILES SMPJF USING(FILE_ID) 
#                WHERE SMPJSNP.PROJ_SNAP_ID = SMPJF.PROJ_SNAP_ID) EVENTCOUNT, 
#        SMWG.WORK_GRP_NAME, SMP.LAST_NAME, SMP.FIRST_NAME, SMP.USERNAME, 
#        SMWG2.WORK_GRP_NAME WORK_GRP_NAME2, SMP2.LAST_NAME LAST_NAME2, SMP2.FIRST_NAME FIRST_NAME2, 
#        SMP2.USERNAME USERNAME2 
# */
# /* 
#           , PERSONS SMP, WORKING_GROUPS SMWG, PERSONS_WORKING_GROUPS SMPWG, PERSONS SMP2, 
#             WORKING_GROUPS SMWG2, PERSONS_WORKING_GROUPS SMPWG2  
# */
}

#-----------------------------------------------------------------------
# get files for a given shapshot
#-----------------------------------------------------------------------
get_snapshot_files() {
  snapshot=$1
  $connect << END | grep $snapshot
set linesize 1000;
set heading off;
column PROJ_DEF_NAME format A30;
column FILE_NAME format A20;
SELECT /*+ RULE; */ SMPJD.PROJ_DEF_NAME, SMPJSNP.PROJ_SNAP_VERSION, 
                    SMPJF.FILE_NUMBER, SMDF.FILE_ID,
                    SMDF.FILE_NAME, SMDF.FILE_SIZE_IN_BYTES, SMDF.EVENT_COUNT 
FROM PROJECT_DEFINITIONS SMPJD, PROJECT_SNAPSHOTS SMPJSNP, PROJECT_FILES SMPJF, DATA_FILES SMDF  
WHERE SMPJD.PROJ_DEF_ID = SMPJSNP.PROJ_DEF_ID AND SMPJSNP.PROJ_SNAP_ID = SMPJF.PROJ_SNAP_ID AND 
      SMPJF.FILE_ID = SMDF.FILE_ID AND SMPJD.PROJ_DEF_NAME = '$snapshot'
                                                                            /* */
ORDER BY SMPJD.PROJ_DEF_NAME, SMPJSNP.PROJ_SNAP_VERSION, SMDF.FILE_NAME;
exit;
END
}

#-----------------------------------------------------------------------
original_query() {
  dataset=$1
  $connect << END
SELECT /*+ RULE; */ SMDF.FILE_NAME, 
                    /* SMDT.DATA_TIER, SMAF.FAMILY, SMAF.APPL_NAME, SMAF.VERSION, */
                    /* SMPROC.PROCESS_TYPE, SMPDS.PHYSICAL_DATASTREAM_NAME, */
                    /* SMLDS.LOGICAL_DATASTREAM_NAME, SMDF.FILE_ID, */
/*                   ( SELECT COUNT(SMR.RUN_NUMBER) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) RUNCOUNT, */
	            SMDF.FILE_SIZE_IN_BYTES,
		    SMDF.EVENT_COUNT,
                    SMDF.FIRST_EVENT_NUMBER, 
                   ( SELECT MIN(SMR.RUN_NUMBER) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) FIRSTRUN, 
                    SMDF.LAST_EVENT_NUMBER,
                   ( SELECT MAX(SMR.RUN_NUMBER) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID) LASTRUN
                /*, SMDF.FILE_PARTITION, */
/*
                   ( SELECT MIN(SMRT.RUN_TYPE) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR, RUN_TYPES SMRT 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID AND 
                     SMR.RUN_TYPE_ID = SMRT.RUN_TYPE_ID) MINRUNTYPE, 
                   ( SELECT COUNT(DISTINCT SMRT.RUN_TYPE) 
                     FROM DATA_FILES_RUNS SMDFR, RUNS SMR, RUN_TYPES SMRT 
                     WHERE SMDF.FILE_ID = SMDFR.FILE_ID AND SMDFR.RUN_ID = SMR.RUN_ID AND SMR.RUN_TYPE_ID = SMRT.RUN_TYPE_ID) RUNTYPECOUNT, 
                     SMFCST.FILE_CONTENT_STATUS, 
                     DECODE( (SELECT 1 FROM DUAL 
                              WHERE ( SMDF.FILE_CONTENT_STATUS_ID IS NULL OR 
                                      SMDF.FILE_CONTENT_STATUS_ID = (SELECT FILE_CONTENT_STATUS_ID FROM FILE_CONTENT_STATUSES 
                                                                     WHERE FILE_CONTENT_STATUS = 'good')) AND 
                              EXISTS ( SELECT 1 FROM DATA_FILE_LOCATIONS SMDFL, DATA_STORAGE_LOCATIONS SMDSL 
                                       WHERE SMDFL.FILE_ID = SMDF.FILE_ID AND SMDFL.LOCATION_ID = SMDSL.LOCATION_ID AND 
                                       ( SMDSL.LOCATION_TYPE != 'tape' OR 
                                         EXISTS (SELECT 1 FROM VOLUMES SMV 
                                                 WHERE SMV.VOLUME_ID = SMDFL.VOLUME_ID AND SMV.VOLUME_STATUS = 'online')))),1,'Yes','No') DELIVERABLE,
	            SMDF.START_TIME, SMDF.END_TIME,
*/
                     FROM DATA_FILES            SMDF, 
                          APPLICATION_FAMILIES  SMAF, 
                          PHYSICAL_DATASTREAMS  SMPDS,
                          LOGICAL_DATASTREAMS   SMLDS, 
                          PROCESSES             SMPROC, 
                          PROCESS_TYPES         SMPT, 
                          DATA_TIERS            SMDT, 
                          FILE_CONTENT_STATUSES SMFCST  
/* */
                     WHERE SMDF.APPL_FAMILY_ID         = SMAF.APPL_FAMILY_ID(+) AND 
                           SMDF.STREAM_ID              = SMPDS.STREAM_ID(+) AND 
                           SMDF.DATA_TIER_ID           = SMDT.DATA_TIER_ID(+) AND 
                           SMPDS.LOGICAL_DATASTREAM_ID = SMLDS.LOGICAL_DATASTREAM_ID(+) AND 
                           SMDF.PROCESS_ID             = SMPROC.PROCESS_ID(+) AND 
                           SMPROC.PROCESS_TYPE         = SMPT.PROCESS_TYPE(+)AND 
                           SMDF.FILE_CONTENT_STATUS_ID = SMFCST.FILE_CONTENT_STATUS_ID AND
/* */
                           SMDF.FILE_ID IN ( SELECT FILE_ID FROM DATA_FILES DF 
                                                            JOIN DATA_FILES_PARAM_VALUES DFPV USING(FILE_ID) 
                                                            JOIN PARAM_VALUES PV              USING(PARAM_VALUE_ID) 
                                                            JOIN PARAM_TYPES PT               USING(PARAM_TYPE_ID) 
                                                            JOIN PARAM_CATEGORIES PC          USING(PARAM_CATEGORY_ID) 
                                             WHERE PC.PARAM_CATEGORY = 'cdf' AND 
                                                   PT.PARAM_TYPE = 'dataset' AND 
                                                   PV.PARAM_VALUE = '$dataset')
/* */
                     ORDER BY SMDF.FILE_NAME ;
END
}