mysql_get_sinfo

 Returns subject info for given subject id.
 
   sinfo = mysql_get_sinfo(subid,conn_id);

 Returns information about the subject from the database and puts it into a
 structure

 REQUIRES
   subid - a subject ID for which to retrieve subject table info
   params
       .conn_id - active MySQL connection ID to use.
       .enc_key - output of mysql_login, to decrypt subject table data
 
 RETURNS
   sinfo
       .datenum -- date of birth
       .gender
       .exp_ids -- experiments the subject has participated in
       .exp_names
       .sess_ids -- session IDs

0001 function sinfo = mysql_get_sinfo(subid,params)
0002 
0003 % Returns subject info for given subject id.
0004 %
0005 %   sinfo = mysql_get_sinfo(subid,conn_id);
0006 %
0007 % Returns information about the subject from the database and puts it into a
0008 % structure
0009 %
0010 % REQUIRES
0011 %   subid - a subject ID for which to retrieve subject table info
0012 %   params
0013 %       .conn_id - active MySQL connection ID to use.
0014 %       .enc_key - output of mysql_login, to decrypt subject table data
0015 %
0016 % RETURNS
0017 %   sinfo
0018 %       .datenum -- date of birth
0019 %       .gender
0020 %       .exp_ids -- experiments the subject has participated in
0021 %       .exp_names
0022 %       .sess_ids -- session IDs
0023 
0024 % 03/05/06 Petr Janata - determines participation dates for the subject
0025 % 04/15/06 PJ - optimized search for subject ID in response tables
0026 % 06/21/06 PJ - searches session table instead of response tables.
0027 % 10/05/09 Stefan Tomic - reads in subject name and DOB as encrypted data
0028 % 10/30/09 PJ - minor fix to convert date string to datenum, following
0029 %               subject table encryption modification
0030 % 06/15/10 PJ - mysql_make_conn
0031 % 02/17/11 FB - adapted for new mysql authentication scheme, using
0032 % mysql_login before this function to get login information for the
0033 % particular user that will be running this function. This will
0034 % 03/29/11 FB - if DOB is encrypted, datenum(sinfo.datenum) will fail. This
0035 % error is now being caught, so that mysql_get_sinfo can continue
0036 % 10/18/12 PJ - ignores experiments with no response table
0037 
0038 sinfo = [];
0039 
0040 % Do some parameter checking
0041 msg = nargchk(1,2,nargin);
0042 if ~isempty(msg)
0043   disp(msg)
0044   return
0045 end
0046 
0047 % Check for valid connection to database
0048 if isempty(params) || ~isfield(params,'conn_id') || ...
0049         isempty(params.conn_id) || mysql(params.conn_id,'status')
0050   error('%s: Do not have a valid connection ID', mfilename);
0051 end
0052 
0053 % Initialize the output structure
0054 sinfo = ...
0055     struct('datenum',[],'gender','','name_last','','name_first','','exp_ids',[],'sess_ids',[],'exp_date',[],'exp_names',{''});
0056 
0057 if isfield(params,'enc_key') && ~isempty(params.enc_key)
0058   mysql_str = sprintf(['select date_entered, aes_decrypt(`dob`,''%s''),'...
0059       ' gender, aes_decrypt(`name_last`,''%s''), '...
0060       'aes_decrypt(`name_first`,''%s'')  from subject '...
0061       'where subject_id="%s";'],...
0062       params.enc_key,params.enc_key,params.enc_key,subid);
0063 else
0064   mysql_str = sprintf(['select date_entered, dob, gender, name_last, '...
0065       'name_first from subject where subject_id="%s";'],subid);
0066 end
0067 [date_entered...
0068     sinfo.datenum, sinfo.gender, sinfo.name_last, sinfo.name_first] = ...
0069     mysql(params.conn_id, mysql_str);
0070 
0071 % Convert date string to datenum
0072 try sinfo.datenum = datenum(sinfo.datenum);
0073 catch sinfo.datenum = 'encrypted'; end
0074 
0075 %
0076 % Determine which experiments this subject has been in.  Do this on the basis
0077 % of the session table. Prior to 11/21/05, the subject ID was not being written
0078 % into the session table, so for subjects who entered the database, we have to
0079 % do things the slow way just to be safe.
0080 %
0081 
0082 crit_datenum = 732637; % datenum('Nov-21-2005');
0083 
0084 if date_entered >= crit_datenum
0085   % Get a list of the experiment and session IDs as well as dates
0086   mysql_str = sprintf('SELECT experiment_id, session_id, date_time  FROM session WHERE subject_id="%s";', subid);
0087   [sinfo.exp_ids, sinfo.sess_ids, sinfo.exp_date] = mysql(params.conn_id, mysql_str);
0088   
0089   % Get the experiment names
0090   expids = sprintf('%d,', sinfo.exp_ids(:));
0091   expids(end) = [];
0092   mysql_str = sprintf(['SELECT experiment_title, experiment_id FROM experiment WHERE' ...
0093     ' experiment_id IN (%s);'], expids);
0094   [sinfo.exp_names, sinfo.exp_name_ids] = mysql(params.conn_id, mysql_str);
0095   
0096 else
0097 
0098   % First, get the list of response tables
0099   mysql_str = 'SELECT experiment_id, experiment_title, response_table FROM experiment;';
0100   [expids, exptitles, resp_tbls] = mysql(params.conn_id, mysql_str);
0101 
0102   % Now, loop through the tables, checking whether this particular subject has
0103   % entries in this table
0104   ntbl = length(resp_tbls);
0105   tbl_mask = zeros(1,ntbl);
0106   for itbl = 1:ntbl
0107         if isempty(resp_tbls{itbl})
0108             continue
0109         end
0110     mysql_str = sprintf('SELECT subject_id FROM %s WHERE subject_id="%s";', resp_tbls{itbl}, subid);
0111     sublist = mysql(params.conn_id, mysql_str);
0112     if ~isempty(sublist)
0113       tbl_mask(itbl) = 1;
0114     end
0115   end % for itbl = 1:ntbl
0116 
0117   exp_idxs = find(tbl_mask);
0118   sinfo.exp_ids = expids(exp_idxs);
0119   sinfo.exp_names = exptitles(exp_idxs);
0120 
0121   % Get participation dates
0122   for itbl = 1:length(exp_idxs)
0123     mysql_str = sprintf('SELECT date_time, session_id FROM %s where subject_id="%s"', resp_tbls{exp_idxs(itbl)}, subid);
0124     [dateinfo, session_ids] = mysql(params.conn_id, mysql_str);
0125     [sessids,sess_offsets] = unique(session_ids);
0126     for isess = 1:length(sessids)
0127       sinfo.exp_date{itbl}(isess) = dateinfo(sess_offsets(isess));
0128     end
0129   end % for itbl = 1:length(exp_idxs
0130   
0131 end % if date_entered >= crit_datenum