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

% TREE_VARS : renvoie les descendants ou ascendants d'un concept (incluant le concept en question si le flag wconcept est mis)
%
%  [liste_concepts,liste_ranks] = tree_VARS(conceptname,direction,varargin)
%
% direction 'ascendants' ou 'descendants' (défaut ascendants)
% ou 'up', 'down'
% ou 'rank'
% 
% 'wconcept'
% 'firstlevel'		uniquement les parents (up) / child (down) directs
%
% Monique, octobre 2014, MBARI (fonctions de Brian S.)
% octobre 2014 : modifié pour renvoyer le rang en plus des conceptnames
% décembre 2014 : ajouté les flags wconcept, firstlevel et rank


[~,flag]=read_varargin(varargin,{},{'up','down','ascendants','descendants','wconcept','firstlevel','rank'});
if nargin<2 || flag.ascendants || flag.up, direction='up'; 
elseif flag.descendants || flag.down, direction='down'; 
elseif flag.rank, direction='rank';
else error('??? tree_VARS doit être ascendants, descendants, up ou down')
end

sql=['WITH org_name AS '... ' ... 
    '( ' ... 
    '    SELECT DISTINCT ' ... 
    '        ISNULL(parent.RankLevel, '''') + ISNULL(parent.RankName, '''') as parent_rank, ' ... 
    '        parent.id AS parent_id, ' ... 
    '        parentname.ConceptName as parent_name, ' ... 
    '        ISNULL(child.RankLevel, '''') + ISNULL(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 {'up','rank'}, sql=[sql,... 
   '        child_name = ''',conceptname,''' '];
	case 'down', sql=[sql,... 
   '        parent_name = ''',conceptname,''' '];
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 {'up','rank'}, sql=[sql,... 
    '            org_name AS C ON C.child_id = p.parent_id '];
	case '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'); 
for varname={'parent_name','parent_rank','child_name','child_rank'}, varname=varname{:}; a.(varname)=cellfun(@strtrim, a.(varname),'UniformOutput',false); end

switch direction

	case 'rank'
		iname=find(eqStrtot(a.child_name,conceptname),1); liste_concepts=a.child_rank(iname); liste_ranks='';

	case 'up', 		% besoin de mettre dans l'ordre (on dirait)
		if flag.firstlevel, iok=eqStrtot(a.child_name,conceptname); for varname=reshape_ligne(fieldnames(a)), varname=varname{:}; a.(varname)=a.(varname)(iok); end, end
		
		nb_up=length(a.parent_name); 
		if nb_up<=1, liste_concepts=a.parent_name; liste_ranks=a.parent_rank; 
			if flag.wconcept, iname=find(eqStrtot(a.child_name,conceptname),1); 
				liste_concepts=[liste_concepts;a.child_name(iname)]; liste_ranks=[liste_ranks;a.child_rank(iname)]; end, 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
		if flag.wconcept, iname=find(eqStrtot(a.child_name,conceptname),1); liste_concepts=[liste_concepts;a.child_name(iname)]; liste_ranks=[liste_ranks;a.child_rank(iname)]; end

	case 'down', 
		if flag.firstlevel, iok=eqStrtot(a.parent_name,conceptname); for varname=reshape_ligne(fieldnames(a)), varname=varname{:}; a.(varname)=a.(varname)(iok); end, end
		liste_concepts=a.child_name; liste_ranks=a.child_rank;
		if flag.wconcept, 
			if isempty(a.parent_name), liste_concepts={conceptname}; liste_ranks=tree_VARS(conceptname,'rank'); 
			else iname=find(eqStrtot(a.parent_name,conceptname),1); liste_concepts=[a.parent_name(iname);liste_concepts]; liste_ranks=[a.parent_rank(iname);liste_ranks]; end
		end

end
liste_concepts=strtrim(liste_concepts);
liste_ranks=strtrim(liste_ranks);


return
