;; MTA_DB_GET  BDS June 2001
;;  extracts mta average tables for specified msids using DataSeeker
;;  returns an idl structure of arrays 
;;  returns -1 on error
;;  also saves a (horribly named) fits file of retrieved data
;;
;;  Calling sequence
;;    result = mta_db_get(ter_cols='', $
;;                       [ prim='', sec='', time='', outfile='', opt='' ] )
;;     where - ter_cols is a string containing comma delimited column
;;                        names (eg. '_1dahacu_avg') or full 
;;                        DataSeeker style specs
;;                        (eg. 'mtaacis..aciseleca_avg._1dahacu_avg')
;;           - prim is an optional comma delimited list of DataSeeker 
;;                        primary criteria, value pairs
;;                        (eg. prim='obsid=9999, grating=HETG')
;;                        valid criteria are: obsid, rafrom, rato,
;;                             decfrom, decto, rollfrom, rollto,
;;                             si, grating
;;           - sec is an optional comma delimited list of DataSeeker 
;;                        secondary (configuration) criteria, value pairs
;;                        (eg. sec='_ctufmtsl=FMT2')
;;           - time is an optional time filter (secs since 01/01/98)
;;                        tstartss-tstopss
;;           - outfile is an optional filename for the fits output.
;;                      if not specified, program will select a default name.
;;           - opt is an optional string of additional DataSeeker options
;;                      to append to command line. Use with caution.
;;
;;   coming soon:
;;     expanded capabilty to pass primary and secondary 
;;            criteria to DataSeeker - done
;;     lookup table for db and table names, so columns can
;;            be specified with msid only - done
;;
;;  Limitations:
;;     Requires environment variables:
;;       ASCDS_MTA_UNAME - set to db user
;;       ASCDS_MTA_PWD   - set to db user's password
;;
;;  TEST VERSION - needs some hacks to work see README
;;
;;  Examples:
;;     old test = mta_db_get(time='95000000-95050000', /
;;                       ter_cols='mtaacis..aciseleca_avg._1dahacu_avg')
;;     test = mta_db_get(time='95000000-95050000', /
;;                       ter_cols='_1dahacu_avg')
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

FUNCTION ID, msid, type
  ; return db..table.column string given column
  if (type eq 1) then lookup = 'avgdb.rdb'
  if (type eq 2) then lookup = 'configdb.rdb'
  get_lun, lunit
  ; figure num lines input
  xnum = strarr(1)
  spawn, 'wc -l '+lookup, xnum
  xxnum = fltarr(2)
  xxnum = strsplit(xnum(0),' ', /extract)
  numobs = long(xxnum(0))
  
  openr, lunit, lookup
  
  array = strarr(numobs)
  readf, lunit, array
  
  free_lun, lunit
  
  i = 1
  line = strsplit(array(i), '	', /extract)
  while (line(2) ne msid and i le numobs-2) do begin
    line = strsplit(array(i), '	', /extract)
    i = i + 1
  endwhile
  
  if (line(2) ne msid) then begin
    print, msid, ' not found
    return, -1
  endif else begin
    return, strcompress(line(0)+'..'+line(1)+'.'+line(2), /remove_all)
  endelse
end

FUNCTION PRIM_VALID, str
  ; check for primary criteria validity
  v_opts = ['obsid', 'rafrom', 'rato', 'decfrom', 'decto', $
            'rollfrom', 'rollto', 'si', 'grating']
  tmp = strsplit(strcompress(str, /remove_all), '=', /extract)
  i = 0
  for i = 0, n_elements(v_opts) - 1 do begin
    if (strlowcase(tmp(0)) eq v_opts(i)) then return, 1
  endfor
  ; valid option not found
  print, "Valid primary criteria are: ", v_opts
  return, -1
end

FUNCTION DATE_VALID, str
  ; be sure date string is valid for DataSeeker
  ;  checks format but not sanity
  ;  if invalid, return NULL
  ;  if valid as is, return str
  ;  if invalid but fixable, return fixed string
  bad = "NULL"
  fix = 0
  inv = 0
  tmp = strsplit(strcompress(str), " ", /extract)
  if (n_elements(tmp lt 2)) then tmp = [tmp, " "]
  big = strsplit(tmp(0), "/", /extract)
  if (fix(big(2)) ge 50 and fix(big(2)) le 99) then begin
    fix = fix + 1
    big(2) = strtrim(1900+fix(big(2)),2)
  endif
  if (fix(big(2)) ge 0 and fix(big(2)) le 49) then begin
    fix = fix + 1
    big(2) = strtrim(2000+fix(big(2)),2)
  endif
  if (fix(big(1)) gt 12) then begin
    if (fix(big(0)) le 12) then begin
      fix = fix + 1
      mtmp = big(1)
      big(1) = big(0) 
      big(0) = mtmp
    endif else inv = inv + 1
  endif
  if (inv ge 1) then return, bad
  if (fix ge 1) then return, strcompress(big(0)+"/"+big(1)+"/"+big(2), /remove_all)+" "+tmp(1)
  return, str
end

;;;;;;;;;;;;;;;;;;; main ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
FUNCTION MTA_DB_GET, TIME=time, TER_COLS=ter_cols, PRIM=prim, SEC=sec, $
                     OUTFILE=outfile, OPT=opt

dspath = './dataseeker.pl'
;dspath = './dataseeker.brad.pl'
; old dspath = './dataseeker.old.pl'

if (keyword_set(outfile)) then begin
  ; delete old outfile
  ;  or else old values will be silently returned on dataseeker failure
  junk = findfile(outfile, count=outfilecnt)
  if (outfilecnt gt 0) then begin
    if (keyword_set(opt)) then begin
      if (strpos(opt, "-b") gt -1) then begin
        print, outfile+" exists and noclobber is set"
        return, -1
      endif
    endif
    spawn, '/usr/bin/rm '+outfile
  endif
endif else begin
  tmpname = strtrim(systime(1),2)
  outfile = 'ds'+tmpname+'.fits
endelse

user = getenv("ASCDS_MTA_UNAME")
if (user eq "") then user = getenv("USER")
if (user eq "") then begin
  print, "Environment variable ASCDS_MTA_UNAME not set."
  return, -1
endif
pwd = getenv("ASCDS_MTA_PWD")
if (pwd eq "") then begin
  print, "Environment variable ASCDS_MTA_PWD not set."
  return, -1
endif

; begin building command line
;  we'll add on as we go, depending of keywords set
;old command = dspath+' -o '+outfile+' -f ds.cfg -n -u '+user+' -w '+pwd+' -s "'
;command = dspath+' -o '+outfile+' -n -u '+user+' -w '+pwd+' -s "'
; for now must specify some extra stuff
;command = dspath+' -o '+outfile+' -n -u '+user+' -w '+pwd+' -p ./pcadfilter.out -c ./ccdmfilter.out -r avgdb.rdb -d sqlocc -s "'
; this will work if ciao environment is set up correctly
command = dspath+' -o '+outfile+' -n -u '+user+' -w '+pwd+' -s "'

; set time range
if (keyword_set(time)) then begin
  times = strarr(2)
  times = strsplit(time, "-", /extract)
  if (strpos(times[0], "/") ge 0) then begin
    time = date_valid(times[0])
    if (time ne "NULL") then begin
      command = command+'tstartdate='+time+' '
    endif else begin
      print, " "
      print, "Sorry, your date spec "+times[0]+" is invalid"
      print, " Try again or use seconds since Jan. 1, 1998 instead"
      return, -1
    endelse
  endif else begin
    command = command+'tstartss='+times[0]+' '
  endelse
  if (strpos(times[1], "/") ge 0) then begin
    time = date_valid(times[1])
    if (time ne "NULL") then begin
      command = command+'tstopdate='+time+' '
    endif else begin
      print, " "
      print, "Sorry, your date spec "+times[1]+" is invalid"
      print, " Try again or use seconds since Jan. 1, 1998 instead"
      return, -1
    endelse
  endif else begin
    command = command+'tstopss='+times[1]+' '
  endelse
endif else begin
  print, " "
  print, "WARNING! You did not specific a time range.  I'll try to get the whole database."
  print, " Panic with ctrl-C to abort."
endelse
 
; set primary criteria
;  options are obsid, rafrom, rato, decfrom, decto, rollfrom, rollto,
;              si, grating
if (keyword_set(prim)) then begin
  parse = strsplit(prim, ',', /extract)
  for i = 0, n_elements(parse)-1 do begin
    if (prim_valid(parse(i)) eq -1) then begin
      print, parse(i), " is invalid primary criteria. Aborting."
      return, -1
    endif else begin
      command = command+' prim-'+strcompress(parse(i), /remove_all)
    endelse
  endfor
endif

; set secondary criteria
if (keyword_set(sec)) then begin
  parse = strsplit(sec, ',', /extract)
  for i = 0, n_elements(parse)-1 do begin
    if (strpos(parse(i), ".") ge 0) then mnemonic = parse(i) $
    else begin
      val = strsplit(parse(i), "=", /extract)
      mnemonic = id(strtrim(val(0),2), 2)
    endelse
    if (mnemonic eq "-1") then begin
      print, "Mnemonic error.  Aborting"
      return, -1
    endif else begin
      command = command+' sec-'+mnemonic+'='+val(1)
    endelse
  endfor
  command=command+' '
endif

; set tertiary columns
if (keyword_set(ter_cols)) then begin
  parse = strsplit(ter_cols, ',', /extract)
  if (strpos(parse(0), ".") ge 0) then mnemonic = parse(0) $
  else mnemonic = id(parse(0), 1)
  if (mnemonic eq "-1") then begin
    print, "Mnemonic error.  Aborting."
    return, -1
  endif else begin
    command = command+' ter-columns='+mnemonic
  endelse
  for i = 1, n_elements(parse)-1 do begin
    if (strpos(parse(i), ".") ge 0) then mnemonic = parse(i) $
    else mnemonic = id(parse(i), 1)
      if (mnemonic eq "-1") then begin
        print, "Mnemonic error.  Aborting"
        return, -1
      endif else begin
        command = command+','+mnemonic
      endelse
  endfor
  command=command+' '
endif else begin
  print, " "
  print, "You asked for no data - that's what I'm giving you."
  ;print, "  Specify columns to return with keyword TER_COLS in DataSeeker fashion"
  ;print, "  like this: ter_cols=dbname..table.column"
  ;print, "             ter_cols=mtaacis..aciseleca_avg,mtapcad..pcadftsgrad_avg._ohrthr27_avg"
  print, "  Specify columns to return with keyword TER_COLS"
  print, "  like this: ter_cols='column_mnemonic'"
  print, "             ter_cols='_1dahacu_avg,_ohrthr27_avg'"
  return, -1
endelse
  
; close the quotes, here we go
command = command+'"'

if (keyword_set(opt)) then command = command+" "+opt

command = strcompress(command)

;not needed now, maybe later -->get_lun, cunit
;not needed now, maybe later -->openw, cunit, tmpcfg
;not needed now, maybe later -->printf, cunit, "tstartss="+times[0]
;not needed now, maybe later -->printf, cunit, "tstopss="+times[1]
;not needed now, maybe later -->free_lun, cunit
; oldcommand = './dataseeker.pl -o '+outfile+$
; old        ' -s"tstartss='+times[0]+' tstopss='+times[1]+ $
; old        ' ter-columns='+ter_cols+ $
; old        '" -fds.cfg -u '+user+ $
; old        ' -w '+pwd+' -n'

; this just takes pwd out of command for debugging at STDOUT
break = strpos(command, "-w") + 2
pwdbreak = strpos(command, pwd)
comm_len = strlen(command)
pass_len = strlen(pwd)
command2 = strmid(command, 0, break)+" ***"+strmid(command, pwdbreak+pass_len, comm_len)
print, "command used > "+command2 ; debug

time0=systime(1)
spawn, "setenv PATH '"+getenv('PATH')+"' ; "+command
e_time=systime(1)-time0

; read into struct, print some info and return struct
junk = findfile(outfile, count=outfilecnt)
if (outfilecnt eq 0) then begin
  print, 'No data was retrieved in '+string(e_time)+' seconds'
  return, -1
endif else begin
  get_lun, tunit
  openr, tunit, outfile
  status = fstat(tunit)
  free_lun, tunit
  if (status.size le 0) then begin
    print, 'No data was retrieved in '+string(e_time)+' seconds'
    return, -1
  endif else begin
    intab = mrdfits(outfile, 1)
    print, ' '
    print, 'Columns:'
    print, tag_names(intab)
    print, ' retrieved in '+string(e_time)+' seconds'
    print, ' '
    return, intab
  endelse
endelse

end