function  [liste_concepts,liste_ranks] = tree_VARS(conceptname,direction)

% TREE_VARS : renvoie les descendants ou ascendants d'un concept
%
%  liste_concepts = tree_VARS(conceptname,direction)
%
% direction 'ascendants' ou 'descendants' (défaut ascendants)
% ou 'up', 'down'
%
% Monique, octobre 2014, MBARI (fonctions de Brian S.)
% octobre 2014 : modifié pour renvoyer le rang en plus des conceptnames


if nargin<2, direction='ascendants'; end

sql=['WITH org_name AS '... ' ... 
    '( ' ... 
    '    SELECT DISTINCT ' ... 
    '        parent.RankName AS parent_rank, ' ... 
    '        parent.id AS parent_id, ' ... 
    '        parentname.ConceptName as parent_name, ' ... 
    '        child.RankName AS child_rank, ' ... 
    '        child.id AS child_id, ' ... 
    '        childname.ConceptName as child_name ' ... 
    '    FROM ' ... 
    '        Concept parent RIGHT OUTER JOIN  ' ... 
    '        Concept child ON child.ParentConceptID_FK = parent.id LEFT OUTER JOIN ' ... 
    '        ConceptName childname ON childname.ConceptID_FK = child.id LEFT OUTER JOIN ' ... 
    '        ConceptName parentname ON parentname.ConceptID_FK = parent.id ' ... 
    '    WHERE ' ... 
    '        childname.NameType = ''Primary'' AND ' ... 
    '        parentname.NameType = ''Primary'' ' ... 
    '),  ' ... 
    'jn AS  ' ... 
    '(    ' ... 
    '    SELECT ' ... 
    '        parent_rank, ' ... 
    '        parent_id, ' ... 
    '        parent_name, ' ... 
	 '			 child_rank, ' ...
    '        child_id, ' ... 
    '        child_name ' ... 
    '    FROM ' ... 
    '        org_name  ' ... 
    '    WHERE '];

switch direction
	case {'ascendants','up'}, sql=[sql,... 
   '        child_name = ''',conceptname,''' '];
	case {'descendants','down'}, sql=[sql,... 
   '        parent_name = ''',conceptname,''' '];
	otherwise error('??? tree_VARS doit être ascendants, descendants, up ou down')
end

	sql=[sql,...
    '    UNION ALL  ' ... 
    '        SELECT ' ... 
    '            C.parent_rank, ' ... 
    '            C.parent_id, ' ... 
    '            C.parent_name, ' ... 
    '            C.child_rank, ' ... 
    '            C.child_id, ' ... 
    '            C.child_name  ' ... 
    '        FROM ' ... 
    '            jn AS p JOIN  '];

switch direction
	case {'ascendants','up'}, sql=[sql,... 
    '            org_name AS C ON C.child_id = p.parent_id '];
	case {'descendants','down'},sql=[sql,... 
    '            org_name AS C ON C.parent_id = p.child_id '];
end

	sql=[sql,...
    ')  ' ... 
'SELECT DISTINCT ' ... 
    'jn.parent_id, ' ... 
    'jn.parent_name, ' ... 
    'jn.parent_rank, ' ... 
    'jn.child_id, ' ... 
    'jn.child_name, ' ... 
    'jn.child_rank ' ... 
'FROM ' ... 
    'jn ' ... 
'ORDER BY ' ... 
    '1'];

a=sqlquery(sql,'VARSknowledge');

switch direction
	case {'ascendants','up'}, 	% besoin de mettre dans l'ordre
		nb_up=length(a.parent_name); if nb_up<=1, liste_concepts=a.parent_name; liste_ranks=a.parent_rank; return, end
		ii=1; iup=1; liste_concepts=a.parent_name; liste_ranks=a.parent_rank; liste_concepts{ii}=a.parent_name{iup}; liste_ranks{ii}=a.parent_rank{iup}; ii=ii+1;
		while ii<=nb_up, liste_concepts{ii}=a.child_name{iup}; liste_ranks{ii}=a.child_rank{iup}; iup=eqStrtot(a.parent_name,liste_concepts{ii}); ii=ii+1; end
	case {'descendants','down'}, liste_concepts=a.child_name; liste_ranks=a.child_rank;
end

return
