% Extract_BOG_data.m

clear
close all

% this is tuned to extract data from BOG for glider validation

% decide which cruise you want to extract
% cruiseid = '12219'; % May 2, 2019
% cruiseid = '19919'; % July 18, 2019
% cruiseid = '21819'; % August 6, 2019
% cruiseid = 'CN19F'; % Fall CANON 2019
cruiseid = '35019'; % Dec 16, 2019
cruiseid = '23420'; % August 2020
% variables to extract. output will be in this order; useful when making
% table
% selectvars = {'cruise, ', 'date_time, ', 'seq, ', 'ctrb_id, ', 'depth, ', 'dec_lat, ', 'dec_long, '};
% paramvars = {'pressure, ', 'tmp, ', 'sal, ', 'oxy_ml, ', 'alk, ', 'pH, ', 'tco2, ', 'no3, ', 'no2, ', 'sio4, ', 'po4 '};

selectvars = {'CRUISE', 'DATE_TIME', 'SEQ', 'ctrb_id', 'depth', 'DEC_LAT', 'DEC_LONG'};
paramvars = {'PRESSURE', 'TMP', 'SAL', 'OXY_ML', 'O2', 'alk', 'pH', 'tco2', 'no3', 'no2', 'sio4', 'po4'};

% link to key BOG fields:
% https://www3.mbari.org/bog/documents/BOG_CTD_Key_Fields.htm

% input parameters for BOG query
selectvars_ = cellfun(@(x) strcat(x,', '), selectvars,'UniformOutput', false); 
select_str = ['SELECT ',horzcat(selectvars_{:})];
paramvars_ = cellfun(@(x) strcat(x,', '), paramvars(1:end-1),'UniformOutput', false); 
parameter_str = horzcat(paramvars_{:},paramvars{end},' '); % need a space at the end. 
from_str='FROM BCTD ';
% criteria_str='WHERE ctrb_id in (''c1'',''mooring1'',''mooring2'')  and year_date>2019000 ';
criteria_str = ['WHERE cruise in (''',cruiseid,''')'];
order_str='ORDER by date_time, seq, depth; ';
%     concatenate strings
strsql=[select_str,parameter_str,from_str,criteria_str,order_str];
disp(['SQL string: ',strsql])

db = database('BOG', 'everyone', 'guest');
c = exec(db, strsql);
c = fetch(c);

% get the data
trex = c.Data;

close(c); 

% create table, extracting variables from the querey string. The last
% variable doesn't have ',' in it, so need to treat it differently.
% bog = array2table(zeros(0,18), 'VariableNames', horzcat(cellfun(@(x) x(1:strfind(x,',')-1), selectvars, 'UniformOutput', false), ...
%     cellfun(@(x) x(1:strfind(x,',')-1), paramvars(1:end-1), 'UniformOutput', false),paramvars(end)));
bog = array2table(zeros(0,19), 'VariableNames', horzcat(selectvars, paramvars));
% declare variables that are not floats
bog.CRUISE = cell(0,1);
bog.DATE_TIME = datetime(zeros(0,6));
bog.ctrb_id = cell(0,1);

bog.CRUISE(1:size(trex,1)) = trex(:,1);
bog.DATE_TIME = datetime(trex(:,2), 'InputFormat', 'yyyy-MM-dd HH:mm:ss.s');
for i = 3:length(selectvars)
    bog.(selectvars{i}) = trex(:,i);
end
for i = 1:length(paramvars)
    bog.(paramvars{i}) = trex(:,i+length(selectvars));
end

writetable(bog, sprintf('BOG_%s.csv',cruiseid));









