% sqlquery is a function that queries the sql command (1st argument) in a given table (2nd argument)



%%%%%%%%%%%% 1- get the list of ROV dives 
v_all=sqlquery('SELECT DISTINCT rov, dive, day_night, year FROM transect_data','midwater'); 
% FILTER: I only keep ROV dives were v_all.day_night is 'D' and v_all.year >=1997.

% Then for each ROVdive (from v_all.rov and v_all.dive), for instance here Ventana 1232:


%%%%%%%%%%%% 2- get the start & end times from the midwater table
v=sqlquery('SELECT id, depth_m, start_datetime_gmt, end_datetime_gmt, volume_m3, day_night FROM transect_data WHERE dive = 1232 AND rov = ''Ventana'' ORDER BY depth_m','midwater');
% FILTER: add the filter on day_night again because there is one "dive" (Ventana3750) that actually contains one night and one day dive
% FILTER: on depths (v.depth_m): only keep standard depths (50-100-200-300-400-500-600-700-800-900-1000)
% FILTER: on the number of depth sampled (v.depth_m) - if less than 3, I don't take the dive into account


%%%%%%%%%%%% 3- query VARS for each depth based on start & end dates
% (example based on Ventana1232 at 200m: start & end dates come from the previous query)
output=sqlquery('SELECT ConceptName,DEPTH,RecordedDate,ObservationID_FK,LinkName,LinkValue,VideoArchiveName,DiveNumber FROM Annotations WHERE (RovName = ''Ventana'') AND (((RecordedDate >= ''1997-04-08 20:43:25'') AND (RecordedDate <= ''1997-04-08 20:53:25'')))','VARS');

% Note that if only looking at a few species, it is faster to only query those. For instance
'SELECT ConceptName,DEPTH,RecordedDate,ObservationID_FK,AssociationID_FK,LinkName,LinkValue,VideoArchiveName,DiveNumber FROM Annotations WHERE (RovName = ''Ventana'') AND (ConceptName = ''Euphausia'' OR ConceptName = ''Bathochordaeus'') AND (((RecordedDate >= ''1997-04-08 20:43:25'') AND (RecordedDate <= ''1997-04-08 20:53:25'')))'
% Also note that while I describe things here for one depth, in reality I query all depths at once because it is slightly faster.

% FILTER: on depths (output.DEPTH) - they should be less than 40m away from the theoretical depth we're looking at (200m here). In reality those "bad depths" likely correspond to bad time stamps (hence should be removed from the transect).


%%%%%%%%%%%% 4- clean the results by removing duplicates
% find annotations tagged with identity-reference (for which output.LinkName is 'identity-reference')
% if there are some, for each corresponding entry:
		% contruct an unique ID based on ConceptName, VideoArchiveName and LinkValue (using java.lang.String, Brian's trick)
		% for each unique ID, find the corresponding ObservationsID_FK and replace them by a unique number (one of the ObservationsID_FK)
		% then clean up duplicate ObservationsID_FK (those duplicates were either created from the process on unique ID described above, or already existed in VARS)
% (I can give the matlab code if needed)


%%%%%%%%%%%% 5- use information on population quantity from the Association table
% create a variable output.counts that is the same length as output.ConceptName and populated by 1 (default behavior: one annotation = one animal).
% find annotations tagged with 'population-quantity' (for which output.LinkName is 'population-quantity').
% for these, replace the 1 in output.counts by the value given by output.LinkValue. Note that 999 just means "too numerous to count" but for now I'm keeping it at 999.
% Note that I check on those in the previous step to make sure I am not removing duplicates that contain a population-quantity tag, but I haven't found any case where this would happen.


%%%%%%%%%%%% 6- sum the counts
% for each ConceptName, sum all the values in output.counts (I do that using matlab's function histcn, and actually sum on both depth & ConceptName since I query on all depths at once).
% get the volume from the query at step 2 (v.volume_m3)
% you can get the transect duration from start & end times at step 2
% I also put all values with no counts at 0, then put all ROVdive/depth combinations at NaN when they don't exist in the midwater table (e.g. dives where only certain depths were sampled) to differenciate between "absent" and "not sampled".


%%%%%%%%%%%% 7- postprocessing
% Various, let me know if you need details. 
% The main one is to sum counts for given "highlevels" - for instance my "Aegina" represents annotations of Aegina and all its descendants (Aegina sp. 1, Aegina rosea, etc). To get the descendants I query the VARSknowledge table using programs from Brian that I modified a bit, let me know if you need these.
% Then mathematical processing that I am still working on (e.g. regrid monthly, integrate over the vertical, treat missing data, log transform, generate anomalies, etc).