function getscctableinfo, scid, date_obs, tbid, RESET=reset, USE_LOADTIME=use_loadtime
;+
; $Id: getscctableinfo.pro,v 1.26 2021/11/23 21:24:37 secchia Exp $
;
; Project   : STEREO SECCHI
;                   
; Name      : getscctableinfo
;               
; Purpose   : get current table info for given table id
;               
; Explanation: 
;               
; Use       : IDL> 
;    
; Inputs    : 
;               
; Outputs   : String containing path/filename+revision (if different than build revision)
;
; Keywords  : 
;
;   /RESET  	Do a new database query and set loadtime
;   /USE_LOADTIME   Use loadtime for query, not date_obs (unless /RESET)
;
; Calls from LASCO : getenv_slash.pro
;
; Common    : SCC_REDUCE
;               
; Restrictions: environment defined by sourcing sccimg[ab].env
;               
; Side effects: 
;               
; Category    : Pipeline (see http://durance.oamp.fr/lasco/fichiers/soft_categories.html)
;               
; Prev. Hist. : None.
;
; Written     : Nathan Rich, NRL/I2, Sep 2004
;               
; $Log: getscctableinfo.pro,v $
; Revision 1.26  2021/11/23 21:24:37  secchia
; nr - more robust to failed sqdb queries
;
; Revision 1.25  2019/02/25 20:19:32  secchia
; changed weekly/monthly mpeg movies
;
; Revision 1.24  2016/06/13 19:54:56  mcnutt
; corrected event search date and set all end dates to 2020
;
; Revision 1.23  2016/06/10 14:28:03  secchia
; set search end date to 2019
;
; Revision 1.22  2011/01/12 15:55:34  nathan
; fix thresh tbid
;
; Revision 1.21  2011/01/04 19:50:16  mcnutt
; add more info to event occured message
;
; Revision 1.20  2010/06/28 19:50:10  nathan
; update for 5.15.00; add lighttb
;
; Revision 1.19  2010/02/01 21:08:31  nathan
; Account for table changes from /SMRELOADTASK; added wavelet and thresh tables,
; which still need to have unprotek updates added to that section of this program.
;
; Revision 1.18  2009/09/01 19:25:44  nathan
; update for 5.14.00
;
; Revision 1.17  2009/05/08 20:46:45  nathan
; add 5.09.02 and fix 5.08,5.09,5.09.01
;
; Revision 1.16  2008/10/09 17:37:55  nathan
; do not use loadtime if mask table (tbid=6)
;
; Revision 1.15  2008/10/07 16:54:50  nathan
; implement method to key off of imgctr to determine when schedule tables were read
;
; Revision 1.14  2008/10/07 14:45:59  nathan
; added 5.09 info; added /DAILY_SCHEDULE
;
; Revision 1.13  2008/04/11 22:26:33  secchib
; nr - update for bld 5.08.00
;
; Revision 1.12  2008/03/31 16:36:51  nathan
; update for FSW 5.07.01 which happened back on Jan. 2, 2008
;
; Revision 1.11  2007/12/03 18:40:20  nathan
; updated for BLD5_07
;
; Revision 1.10  2007/11/27 21:44:09  nathan
; use default values if sqdb returns no data
;
; Revision 1.9  2007/10/05 18:43:42  secchia
; nr - update for bld 5.05
;
; Revision 1.8  2007/08/28 20:43:48  nathan
; add bld 5.01+ cases
;
; Revision 1.7  2007/01/31 20:58:39  nathan
; explain output
;
; Revision 1.6  2006/12/21 18:40:25  nathan
; add estimated date/time for B software patch
;
; Revision 1.5  2006/12/21 03:06:00  nathan
; add BLD5_00 case
;
; Revision 1.4  2006/12/21 02:13:08  nathan
; fix hkevents start date
;
; Revision 1.3  2006/12/16 00:32:34  nathan
; progress...
;
; Revision 1.2  2006/12/14 15:45:51  nathan
; not ready yet
;
; Revision 1.1  2006/12/08 22:53:02  nathan
; for getting the current info for given table id
;
;-            

; FSW tables are defined in $SSW_SECCHI/idl/database/fswtableids.txt

; HKEVENTSSYS=110 means reboot = /SMTBLINIT
; HKEVENTCODE=110+ strpos(s.hkeventstr,'is')>0 = /SMTBLINIT for IS tables
; HKEVENTCODE=110+ strpos(s.hkeventstr,'ip')>0 = /SMTBLINIT for IP tables
; HKEVENTSSYS=99  means uploaded file (sourcefileid is defined)
; HKEVENTDAT1=255 ""

common sccevent, uploadevts, scevts, tableids, loadtime, reloadip, reloadis

; For now at least, just save two event structures, and parse per tableid for each table 
; and each image.

ab=strlowcase(scid)
utc_obs=anytim2utc(date_obs)

;; Need to do this for each new day?
dbok=1
IF keyword_set(RESET) OR datatype(uploadevts) EQ 'UND' or datatype(scevts) eq 'UND' THEN BEGIN
    uploadevts=gethkevents(ab,'2006/10/26','2030/10/26',ssys=99)
    wait,3
    tblloads =gethkevents(ab,'2006/10/26','2030/10/26',ssys=10)	; from /SMTBLLOAD
    wait,3
    reboots=gethkevents(ab,'2006/10/26','2030/10/26',ssys=110)	; from reboot
    wait,3
    reloads=gethkevents(ab,'2006/10/26','2030/10/26',code=110)	; from /SMRELOADTASK
help,tblloads,reboots,reloads
IF datatype(uploadevts) NE 'STC' THEN dbok=0
IF datatype(tblloads) NE 'STC' THEN dbok=0
IF datatype(reboots) NE 'STC' THEN dbok=0
IF datatype(reloads) NE 'STC' THEN dbok=0
    IF (dbok) THEN BEGIN
	    wip=where(strpos(reloads.hkeventstr,'ip') GT 0,nwip)
	    wis=where(strpos(reloads.hkeventstr,'is') GT 0,nwis)
	    reloadip=reloads[wip]
	    reloadis=reloads[wis]
	    help,reloads,reloadip,reloadis
	    wait,2
	    scevts=[tblloads,reboots]
	    srt=sort(scevts.unix_time)
	    scevts=scevts[srt]
	    
	    
	    loadtime=utc_obs
    ENDIF
ENDIF
IF (dbok) THEN BEGIN
    ;;
    ; find last time before current time this table was loaded and get name of table onboard
    subs1=where(scevts.HKEVENTDAT1 EQ tbid and scevts.HKEVENTCODE EQ 420)
    thistbl0=scevts[subs1]
    IF tbid EQ 7 or tbid EQ 8 THEN BEGIN
    ; special case for wavelet and threshold, which don't get evt messages at reboot(!?)
    	subs2=where(scevts.hkeventdat1 EQ 6 and scevts.hkeventssys EQ 110)
	thistbl0=[thistbl0,scevts[subs2]]
    ENDIF
    ; Include reloadtask events
    thistbl=thistbl0
    IF tbid LE 8 and tbid GE 6  THEN thistbl=[thistbl0,reloadip]
    IF tbid EQ 36 or tbid EQ 38 THEN thistbl=[thistbl0,reloadis]
    srt2=sort(thistbl.unix_time)
    thistbl=thistbl[srt2] 
    IF keyword_set(USE_LOADTIME) and tbid NE 6 THEN imgunixtime=utc2unixtai(loadtime) ELSE $
    	imgunixtime=utc2unixtai(utc_obs)
    ;stop
    last=find_closest(imgunixtime, thistbl.unix_time,/LESS)
    ;       LESS:   Returns the closest subscript for arr that is less than or
    ;               equal to num Otherwise the subscript of the point closest
    ;               to num is returned.  Notice that the value of arr might be
    ;               greater than num.
    fileused=thistbl[last].HKEVENTSTR
    ; IF filesed = is.gz or ip.gz, then change fileused to last reboot value for thistbl
    IF thistbl[last[0]].hkeventcode EQ 110 THEN BEGIN
    	    message,'Last load was SMRELOADTASK',/info
	    wrb=where(thistbl0.hkeventssys EQ 110)
	    thistbl2=thistbl0[wrb]
	    last2=find_closest(imgunixtime, thistbl2.unix_time,/LESS)
    	    fileused=thistbl2[last2].HKEVENTSTR
    ENDIF

ENDIF ELSE BEGIN
    message,'Database query failed; using default values for Table info.',/info
    fileused='undefined'
ENDELSE

IF datatype(tableids) EQ 'UND' THEN BEGIN
	tableids=strarr(47,1)
	; see $SSW_SECCHI/idl/database/fswtableids.txt
	; subscript of first dimension is tableid
	; subscript 0 of 2nd dimension is root of default filename
	tableids[ 6,0]='occult'
	tableids[ 7,0]='wavelet.img'
	tableids[ 8,0]='thresh'
	tableids[25,0]='wavetb1'
	tableids[26,0]='rotbtb1'
	tableids[27,0]='wavetb1'
	tableids[28,0]='rotbtb1'
	tableids[29,0]='wavetb1'
	tableids[30,0]='rotbtb1'
	tableids[31,0]='wavetb1'
	tableids[32,0]='rotbtb1'
	tableids[33,0]='wavetb1'
	tableids[34,0]='rotbtb1'
	tableids[35,0]='setuptb'
	tableids[36,0]='expostb'
	tableids[37,0]='lighttb'
	tableids[38,0]='imagetbl.img'
ENDIF

rotbids=[26,28,30,32,34]
w=where(tbid EQ rotbids,isrotb)
wvtbids=[25,27,29,31,33]
w=where(tbid EQ wvtbids,iswvtb)

; identify source of this onboard table -- either uploaded or init

IF datatype(uploadevts) NE 'STC' or $
; if database not available, assume onboard table
   strmid(fileused,0,4) EQ 'mode' or $
   strmid(fileused,0,8) EQ 'unprotek' THEN BEGIN
; last table load was from unprotek

	; get ground pipeline filename of table file
	IF tbid EQ 38 or tbid EQ 7 THEN tblfname=tableids[tbid,0] ELSE tblfname=tableids[tbid,0]+ab+'.img'

	IF (ab EQ 'a' and date_obs LT '2006-12-18T18:15:00') OR $
	   (ab EQ 'b' and date_obs LT '2006-12-26T18:15:00')                                       THEN BEGIN
	; BLD5_00 case
		tbldir=concat_dir(getenv('TABLES'),'BLD5_00')
		tblfile=concat_dir(tbldir,tblfname)
        	; get revision number
		;spawn,"grep -h '$Id' "+tblfile, fileidline, /sh
		;parts=strsplit(fileidline,' ,',/extract)  
		
		; start BLD5_00 special cases
		IF tbid EQ 38 and ab EQ 'a' and date_obs GT '2006-11-18T18:15:00' THEN rev='1.71' ELSE $
		IF tbid EQ 38 and ab EQ 'b' and date_obs GT '2006-11-18T21:36:00' THEN rev='1.71' ELSE $
		;IF tbid EQ  6 and ab EQ 'a'                                       THEN rev='1.16' ELSE $
		;IF tbid EQ  6 and ab EQ 'b'                                       THEN rev='1.14' ELSE $
		rev=''
		
		fieldvalue="ops/tables/BLD5_00/"+tblfname+rev
		; ok, I decided that because revision is captured in the directory,
		; there is no need to include rev in fieldvalue unless it differs from
		; the tagged revision.

	ENDIF ELSE BEGIN
	; BLD5_01 case 
	
		;tbldir=concat_dir(getenv('TABLES'),'BLD501')
		;tblfile=concat_dir(tbldir,tblfname)
		; get revision number
		;spawn,"grep -h '$Id' "+tblfile, fileidline, /sh
		;parts=strsplit(fileidline,' ,',/extract)  
	
		rev=''
		
		fieldvalue="ops/tables/BLD501/"+tblfname+rev 
	
	; ++++ BLD5_01+ cases
	
	    	tbldir='ops/tables/default'
	
	; 5.01.05 A, 2007-02-22, rotbtb1a.img,v 1.7
		IF (isrotb)   and ab EQ 'a' and date_obs GT '2007-02-22T12:00:00' THEN fieldvalue=tbldir+'/rotbtb1a.img1.7'
	; 5.01.05 B, 2007-02-28, rotbtb1b.img,v 1.7
		IF (isrotb)   and ab EQ 'b' and date_obs GT '2007-02-28T12:00:00' THEN fieldvalue=tbldir+'/rotbtb1b.img1.7'
	; 5.01.07 A, 2007-04-27: imagetbl.img,v 1.97  expostba.img,v 1.27
		IF tbid EQ 38 and ab EQ 'a' and date_obs GT '2007-04-27T12:00:00' THEN fieldvalue=tbldir+'/imagetbl.img1.97'
	; 5.01.07 B, 2007-04-26: imagetbl.img,v 1.97  expostbb.img,v 1.30
		IF tbid EQ 38 and ab EQ 'b' and date_obs GT '2007-04-26T12:00:00' THEN fieldvalue=tbldir+'/imagetbl.img1.97'
	; 5.03.00 A, 2007-05-30: expostba.img,v 1.28
		IF tbid EQ 36 and ab EQ 'a' and date_obs GT '2007-05-30T12:00:00' THEN fieldvalue=tbldir+'/expostba.img1.28'
	; 5.03.00 B, 2007-05-30: expostbb.img,v 1.32
		IF tbid EQ 36 and ab EQ 'b' and date_obs GT '2007-05-30T12:00:00' THEN fieldvalue=tbldir+'/expostbb.img1.32'
	; 5.03.01 A, 2007-07-26: expostba.img,v 1.30  imagetbl.img,v 1.99  expqueta.img,v 1.2
		IF tbid EQ 36 and ab EQ 'a' and date_obs GT '2007-07-26T12:00:00' THEN BEGIN
		    fieldvalue=tbldir+'/expostba.img1.30'
		    IF strpos(fileused,'expque') GT 0 THEN fieldvalue='ops/tables/current/expqueta.img1.2
		ENDIF
		IF tbid EQ 38 and ab EQ 'a' and date_obs GT '2007-07-26T12:00:00' THEN fieldvalue=tbldir+'/imagetbl.img1.99'
		
	; 5.03.01 B, 2007-07-24: expostbb.img,v 1.34  imagetbl.img,v 1.99  expquetb.img,v 1.2
		IF tbid EQ 36 and ab EQ 'b' and date_obs GT '2007-07-24T12:00:00' THEN BEGIN
		    fieldvalue=tbldir+'/expostbb.img1.32'
		    IF strpos(fileused,'expque') GT 0 THEN fieldvalue='ops/tables/current/expquetb.img1.2
		ENDIF
		IF tbid EQ 38 and ab EQ 'b' and date_obs GT '2007-07-24T12:00:00' THEN fieldvalue=tbldir+'/imagetbl.img1.99'
	; 5.05.00 A, 2007-10-05: imagetbl.img,v 1.103, wavelet.img,v 1.20    
		IF tbid EQ 38 and ab EQ 'a' and date_obs GT '2007-10-05T17:36:00' THEN fieldvalue=tbldir+'/imagetbl.img1.103'
	; 5.05.00 B, 2007-10-04: imagetbl.img,v 1.103, wavelet.img,v 1.20    
		IF tbid EQ 38 and ab EQ 'b' and date_obs GT '2007-10-04T18:30:00' THEN fieldvalue=tbldir+'/imagetbl.img1.103'
	
	; 5.07.00 A, 2007-11-30: occulta.img,v 1.19    
		IF tbid EQ 6 and ab EQ 'a' and date_obs GT '2007-11-30T14:00:00' THEN fieldvalue=tbldir+'/occulta.img1.19'
	; 5.07.00 B, 2007-11-30: occultb.img,v 1.17   
		IF tbid EQ 6 and ab EQ 'b' and date_obs GT '2007-11-30T14:55:00' THEN fieldvalue=tbldir+'/occultb.img1.17'
    	
	; 5.07.01 A. 2008-01-02: imagetbl.img,v 1.119
	; 5.07.01 B. 2008-01-02: imagetbl.img,v 1.119, expquetb.img,v 1.4
		IF tbid EQ 38 and date_obs GT '2008-01-03' THEN fieldvalue=tbldir+'/imagetbl.img1.119'
		IF tbid EQ 36 and ab EQ 'b' and date_obs GT '2008-01-03' THEN $
		     IF strpos(fileused,'expque') GT 0 THEN fieldvalue='ops/tables/current/expquetb.img1.2
    	; 5.08.00 A, 2008-04-11
		datemod='2008-04-11T20:05:00'
		IF tbid EQ 6  and ab EQ 'a' and date_obs GT datemod THEN fieldvalue=tbldir+'/occulta.img1.20'
	    	IF tbid EQ 38 and ab EQ 'a' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.121'
    	; 5.08.00 B, 2008-04-11
		datemod='2008-04-11T14:19:00'
		IF tbid EQ 6  and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/occultb.img1.18'
	    	IF tbid EQ 38 and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.121'
		IF tbid EQ 36 and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/expostbb.img1.40'
    	; 5.09.00 A&B
		IF tbid EQ 36 and ab EQ 'b' and date_obs GT '2008-09-16' THEN $
		     IF strpos(fileused,'expque') GT 0      	    THEN fieldvalue='ops/tables/current/expquetb.img1.5'
		IF tbid EQ 36 and ab EQ 'a' and date_obs GT '2008-09-11' THEN $
		     IF strpos(fileused,'expque') GT 0      	    THEN fieldvalue='ops/tables/current/expqueta.img1.3'
    	; 5.09.01 A
		datemod='2008-12-22T11:08:00'
		IF tbid EQ 6  and ab EQ 'a' and date_obs GT datemod THEN fieldvalue=tbldir+'/occulta.img1.26'
	    	IF tbid EQ 38 and ab EQ 'a' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.126'
    	; 5.09.01 B
		datemod='2008-12-19T01:30:00'
	    	IF tbid EQ 38 and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.126'
		IF tbid EQ 6  and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/occultb.img1.23'
    	; 5.09.02 B
		datemod='2009-05-08T03:00:00'
		IF tbid EQ 6  and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/occultb.img1.24'
	    	IF tbid EQ 38 and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.133'
    	; 5.14.00 A
		datemod='2009-09-01T14:00:00'
		IF tbid EQ 6  and ab EQ 'a' and date_obs GT datemod THEN fieldvalue=tbldir+'/occulta.img1.29'
	    	IF tbid EQ 38 and ab EQ 'a' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.139'
    	; 5.14.00 B
		datemod='2009-09-01T04:00:00'
		IF tbid EQ 6  and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/occultb.img1.26'
	    	IF tbid EQ 38 and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.139'
    	; 5.15.00 A
		datemod='2010-02-04T00:30:00'
		IF tbid EQ 8  and ab EQ 'a' and date_obs GT datemod THEN fieldvalue=tbldir+'/thresha.img1.36'
	    	IF tbid EQ 38 and ab EQ 'a' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.146'
    	; 5.15.00 B
		datemod='2010-02-03T19:01:00'
		IF tbid EQ 8  and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/threshb.img1.35'
	    	IF tbid EQ 38 and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/imagetbl.img1.146'
	    	IF tbid EQ 37 and ab EQ 'b' and date_obs GT datemod THEN fieldvalue=tbldir+'/lighttbb.cpp1.12'
	ENDELSE
	
ENDIF ELSE BEGIN
; table file was uploaded	
	tbldir=concat_dir(getenv('TABLES'),'current')
	subs2=where(uploadevts.HKEVENTSTR EQ fileused)
if subs2(0) gt -1 then begin
	thisfile=uploadevts[subs2]
	last2=find_closest(thistbl[last].unix_time,thisfile.unix_time,/LESS)
	fieldvalue=thisfile[last2].sourcefileid
endif else if fileused eq 'temp/image135.igz' then fieldvalue='source/is/src/imagetbl.img1.135'
        ; strip off path
	;break_file,thisfile[last2].sourcefileid,dlg,dir,fn,ext
	;fieldvalue=fn+ext
ENDELSE
if datatype(fieldvalue) eq 'UND' and fileused eq 'temp/imageecl.igz' then fieldvalue='source/is/src/imagetbl.img1.151'
if datatype(fieldvalue) eq 'UND' and fileused eq 'temp/exposoff.igz' then fieldvalue='ops/tables/current/exposoff.img1.1'
; If strmid(HKEVENTSTR,0,4) EQ 'mode' then what source file was used? --> need table with 
; time, mode/tables/filename and/or table ID, name+version number of sourcefile
; each time a file is copied to unprotek/tables OR software version is updated onboard

return,fieldvalue
print,subs2
end