#!/bin/bash
T=/tmp/$$
trap 'rm -fr $T' 0 1 2 3 4 5 6 7 8 10 12 13 14 15
mkdir $T
name="$(basename $0)"
getopt -T
if [ $? -ne 4 ]; then
  echo "current version of getopt shell command required"
  echo "getopt shell command version is $(getopt --version)"
  exit 2
fi
eval set -- $(getopt -n "$name" -o ':c:s:e:j:n:u:a:q:x:l:hS' -- "$@")
error=0
help=0
while [ $# -gt 0 ]; do 
  case $1 in
  (-c)  cluster="$2"; needClusters=1; shift;;
  (-s)  start="$2"; needLogs=1; shift;;
  (-e)  end="$2"; needLogs=1; shift;;
  (-j)  job="$2"; shift;;
  (-n)  node="$2"; needNodes=1; shift;;
  (-u)  user="$2"; needUserDetails=1; shift;;
  (-a)  account="$2"; needAccountDetails=1; shift;;
  (-q)  queue="$2"; needQueueDetails=1; shift;;
  (-x)  exitcode="$2"; needExitCodeDetails=1; shift;;
  (-l)  logCodes="$2"; needLogs=1; shift;;
  (-h)  help=1;;
  (-S)  showSQL=1;;
  (--)  shift; break;;
  (-*)  error=1; echo "${name}: unrecognized option $1" 1>&2; shift;;
  (*)   break;;
  esac
  shift
done
for f; do
  error=1; echo "${name}: unrecognized option $f" 1>&2
done
if [ "$start" ]; then
  Start="$(getdate in mm slash dd slash ccyy thh colon tmm colon tss, $start)"
  if [ "$Start" == "error" ]; then
    error=1
    echo "start $start is invalid"
  else
    echo "Start: $Start"
  fi
fi
if [ "$end" ]; then
  End="$(getdate in mm slash dd slash ccyy thh colon tmm colon tss, $end)"
  if [ "$End" == "error" ]; then
    error=1
    echo "end $end is invalid"
  else
    echo "End:   $End"
  fi
fi
if [ "$help" != "0" -o "$error" != "0" ]; then
  echo "Usage: ${name} [-c cluster] [-s startTime] [-e endTime] [-j jobNumber]"
  echo "         [-n node] [-u user] [-a account] [-q queue] [-x exitCode ]"
  echo "         [-h] [-S]"
  echo ""
  echo "start and end times: whatever getdate accepts without error"
  echo "  at least the following are accepted: mm/dd/yyyy hh24:mi:ss,"
  echo "  yesterday [hh24:mi:ss] and [hh24:mi:ss]."
  echo ""
  echo "jobNumber: single job number"
  echo ""
  echo "node: single node name"
  echo ""
  echo "user: single user name"
  echo ""
  echo "account: single account name"
  echo ""
  echo "queue: single name name"
  echo ""
  echo "exitCode: decimal number or !0"
  echo ""
  echo "-h or an error produces this text"
  echo ""
  echo "-S: show the generated SQL"
  exit 0;
fi
if [ "$error" != "0" ]; then
  echo "execution terminated"
  exit 1
fi
if [ "$needNodes" -o "$needUserDetails" -o "$needAccountDetails" -o \
     "$needQueueDetails" -o "$needExitCodeDetails" ]; then
 needLogs=1
fi
connection="dbname=jml_prod user=jml_reader host=ds2 password=reader"
cat - >$T/findjob.sql <<EOF
select j.id
from jobs j
EOF
if [ "$needClusters" ]; then
cat - >>$T/findjob.sql <<EOF
join clusters c on (j.cluster_id = c.id and c.name = upper('$cluster'))
EOF
fi
if [ "$needLogs" ]; then
  list="'S','E'"
  if [ "$logCodes" ]; then
  list=""
  copy="$logCodes"
  while [ "$copy" ]; do
    if [ "$list" ]; then list="$list,"; fi
    list="${list}'$(expr "$copy" : '\(.\).*')'"
    copy="$(expr "$copy" : '.\(.*\)')"
  done
  fi
cat - >>$T/findjob.sql <<EOF
join job_logs l on (j.id = l.job_id)
join valid_job_states vs on (l.valid_job_state_id = vs.id
                         and vs.code in ($list))
EOF
fi
if [ "$needUserDetails" ]; then
cat - >>$T/findjob.sql <<EOF
join job_details du on (l.id = du.job_log_id)
EOF
fi
if [ "$needAccountDetails" ]; then
cat - >>$T/findjob.sql <<EOF
join job_details da on (l.id = da.job_log_id)
EOF
fi
if [ "$needQueueDetails" ]; then
cat - >>$T/findjob.sql <<EOF
join job_details dq on (l.id = dq.job_log_id)
EOF
fi
if [ "$needExitCodeDetails" ]; then
cat - >>$T/findjob.sql <<EOF
join job_details dx on (l.id = dx.job_log_id)
EOF
fi
if [ "$needNodes" ]; then
cat - >>$T/findjob.sql <<EOF
join job_nodes jn on (l.id = jn.job_log_id)
join nodes n on(jn.node_id = n.id)
EOF
fi
word="where"
if [ "$Start" -a "$End" ]; then
cat - >>$T/findjob.sql <<EOF
$word l.log_time
        between to_timestamp('$Start','MM/DD/YYYY HH24:MI:SS')
            and to_timestamp('$End','MM/DD/YYYY HH24:MI:SS')
EOF
word="  and"
elif [ "$Start" ]; then
cat - >>$T/findjob.sql <<EOF
$word l.log_time >= to_timestamp('$Start','MM/DD/YYYY HH24:MI:SS')
EOF
word="  and"
elif [ "$End" ]; then
cat - >>$T/findjob.sql <<EOF
$word l.log_time <= to_timestamp('$End','MM/DD/YYYY HH24:MI:SS')
EOF
word="  and"
fi
if [ "$job" ]; then
  element=$(expr "$job" : '[0-9][0-9]*\[\([0-9][0-9]*\)\]$')
  if [ "$element" ]; then
    job=$(expr "$job" : '\([0-9][0-9]*\)\[[0-9][0-9]*\]$')
  fi
  cat - >>$T/findjob.sql <<EOF
$word j.job_number = '$job'
EOF
  word="  and"
  if [ "$element" ]; then
    cat - >>$T/findjob.sql <<EOF
$word j.job_element = '$element'
EOF
  fi
fi
if [ "$node" ]; then
cat - >>$T/findjob.sql <<EOF
$word n.name = '$node'
EOF
word="  and"
fi
if [ "$user" ]; then
cat - >>$T/findjob.sql <<EOF
$word du.name = 'user'
  and du.value = '$user'
EOF
word="  and"
fi
if [ "$account" ]; then
cat - >>$T/findjob.sql <<EOF
$word da.name = 'account'
  and da.value = '$account'
EOF
word="  and"
fi
if [ "$queue" ]; then
cat - >>$T/findjob.sql <<EOF
$word dq.name = 'queue'
  and dq.value = '$queue'
EOF
word="  and"
fi
if [ "$exitcode" ]; then
  if [ "$exitcode" = "!0" ]; then
cat - >>$T/findjob.sql <<EOF
$word dx.name = 'Exit_status'
  and dx.value != '0'
EOF
  else
cat - >>$T/findjob.sql <<EOF
$word dx.name = 'Exit_status'
  and dx.value = '$exitcode'
EOF
  fi
word="  and"
fi
cat - >>$T/findjob.sql <<EOF
order by j.cluster_id, j.job_number, j.job_element
EOF
if [ "$showSQL" ]; then cat $T/findjob.sql; fi
if [ "$word" != "  and" ]; then
  echo "Dumping the entire database is a BAD idea!"
  exit 1
fi
psql "$connection" -f $T/findjob.sql -A -t -q > $T/report
cat $T/report | while read jobId junk; do
cat - >$T/findjob.sql <<EOF
select job_number, job_element, user_name, account, start_count, wall_seconds,
       last_start,last_end
from jobs j
where id = $jobId
EOF
if [ "$showSQL" ]; then cat $T/findjob.sql; fi
psql "$connection" -f $T/findjob.sql -A -t -q > $T/report2
cat - >$T/findlogs.sql <<EOF
select log_time,
       (select code from valid_job_states v where v.id = valid_job_state_id),
       d.value,
EOF
if [ "$Start" -a "$End" ]; then
cat - >>$T/findlogs.sql <<EOF
       l.log_time
         between to_timestamp('$Start','MM/DD/YYYY HH24:MI:SS')
             and to_timestamp('$End','MM/DD/YYYY HH24:MI:SS')
EOF
elif [ "$Start" ]; then
cat - >>$T/findlogs.sql <<EOF
       l.log_time >= to_timestamp('$Start','MM/DD/YYYY HH24:MI:SS')
EOF
elif [ "$End" ]; then
cat - >>$T/findlogs.sql <<EOF
       l.log_time <= to_timestamp('$End','MM/DD/YYYY HH24:MI:SS')
EOF
else
cat - >>$T/findlogs.sql <<EOF
       null
EOF
fi
cat - >>$T/findlogs.sql <<EOF
from job_logs l
left join job_details d on
  (    l.id = d.job_log_id
   and d.job_detail_name_id = (
         select id from job_detail_names where name = 'Exit_status'))
where job_id = $jobId
order by log_time, valid_job_state_id
EOF
if [ "$showSQL" ]; then cat $T/findlogs.sql; fi
psql "$connection" -f $T/findlogs.sql -A -t -q > $T/logs
gawk -F\| '{
jn=$1
if($2 != "") jn = jn "[" $2 "]"
printf "%-14s %20s\n","job number:", jn
printf "%-14s %20s\n","user:", $3
printf "%-14s %20s\n","account:", $4
printf "%-14s %20s\n","# of nodes:", $5
printf "%-14s %20s\n","wall seconds:", $6
printf "%-14s %20s\n","last start:", $7
printf "%-14s %20s\n","last end:", $8
}' $T/report2
cat $T/logs
echo ""
done