{\rtf1\ansi\ansicpg1252\deff0\deflang1036{\fonttbl{\f0\fswiss\fcharset0 Arial;}}
{\*\generator Msftedit 5.41.15.1515;}\viewkind4\uc1\pard\f0\fs20 CREATE   FUNCTION dbo.f_toto_extra (@NameId INT, @RefId INT)\par
RETURNS @Results TABLE (NameFk1 INT ,RefFk1 INT , RelQualifierFk INT,Sens VARCHAR(15),NameFk2 INT,RefFk2 INT, Cle VARCHAR(400), Rang1 INT, Rang2 INT, Done INT) \par
AS\par
\tab    BEGIN\par
    --DECLARE  @Results TABLE (NameFk1 INT ,RefFk1 INT , RelQualifierFk INT,Sens VARCHAR(15),NameFk2 INT,RefFk2 INT, Cle VARCHAR(400), Rang1 INT, Rang2 INT, Done INT) \par
    DECLARE @CurrentTaxo INT\par
    DECLARE @HigherTaxo INT\par
    DECLARE @HigherId INT\par
    DECLARE @CurrentStatus INT\par
    DECLARE @AcceptedName INT\par
    DECLARE @Done INT\par
    \par
    SELECT @CurrentTaxo = RankFK FROM Name WHERE NameId = @NameId;\par
    SELECT @CurrentStatus=StatusFK FROM PTaxon WHERE PTNameFK = @NameId and PTRefFK = @RefID;\par
\par
   --Status 1 = accepted\par
\par
   IF @CurrentStatus !=1\par
   --getAcceptedName First\par
\tab    BEGIN\par
\tab       SELECT @AcceptedName = PTNameFK2 FROM RelPTaxon WHERE PTNameFK1=@NameId AND PTRefFK1=@RefId AND RelQualifierFK IN (2,3,4,5,6,7) \par
\tab    END\par
\par
   IF @CurrentStatus =1\par
\tab    BEGIN --use the normal name\par
\tab      SELECT @HigherTaxo=PTNameFK2,@HigherId=RankFK FROM RelPTaxon, Name WHERE PTNameFK1 = @NameId AND RelQualifierFK = 1 AND PTRefFK1=@RefId AND NameId = PTNameFK2;\par
\tab    END\par
   ELSE --use accepted name @AcceptedName\par
      SELECT @HigherTaxo=PTNameFK2,@HigherId=RankFK FROM RelPTaxon, Name WHERE PTNameFK1 = @AcceptedName AND RelQualifierFK = 1 AND PTRefFK1=@RefId AND NameId = PTNameFK2;\par
  \par
\par
/*a recupere le higher ID  */\par
\par
  IF @HigherId >40\par
\tab  BEGIN\par
\par
\tab IF @CurrentStatus =1\par
\tab\tab   BEGIN\par
\tab\tab   INSERT INTO @Results \par
\tab\tab         SELECT  PTNameFk1, PTRefFK1 , RelQualifierFk  , '',  PTNameFk2, PTRefFk2 , CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk,0\par
\tab\tab         FROM RelPTaxon,Name N1, Name N2\par
\tab\tab         WHERE (PTNameFk1=@NameId AND PTRefFk1 = @RefId AND PTNameFk2 = N2.NameId AND PTNameFk1=N1.NameId)\par
\tab\tab         AND RelQualifierFK != -99 AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\par
\tab\tab   INSERT INTO @Results\par
\tab\tab         SELECT PTNameFk2, PTRefFK2, RelQualifierFk, '_inverse',  PTNameFK1, PTRefFk1, CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N2.RankFk, N1.RankFk,0\par
\tab\tab         FROM RelPTaxon,Name N1,Name N2\par
\tab\tab         WHERE (PTNameFk2 = @NameId AND PTRefFk2 = @RefId AND PTNameFk2=N1.NameId AND PTNameFk2=N2.NameId)\par
\tab\tab         AND RelQualifierFk != '-99' AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\tab\tab   END\par
\tab  IF @CurrentStatus !=1\par
\tab\tab BEGIN\par
\tab\tab\par
\tab\tab INSERT INTO @Results \par
\tab\tab\tab SELECT  PTNameFk1, PTRefFK1 , RelQualifierFk  , '',  PTNameFk2, PTRefFk2 , CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk,0\par
\tab\tab\tab FROM RelPTaxon,Name N1, Name N2\par
\tab\tab\tab WHERE (PTNameFk1=@AcceptedName AND PTRefFk1 = @RefId AND PTNameFk2 = N2.NameId AND PTNameFk1=N1.NameId)\par
\tab\tab\tab AND RelQualifierFK != -99 AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\tab\tab INSERT INTO @Results\par
\tab\tab\tab    SELECT PTNameFk2, PTRefFK2, RelQualifierFk, '_inverse',  PTNameFK1, PTRefFk1, CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N2.RankFk, N1.RankFk,0\par
\tab\tab\tab    FROM RelPTaxon,Name N1,Name N2\par
\tab\tab\tab    WHERE (PTNameFk2 = @AcceptedName AND PTRefFk2 = @RefId AND PTNameFk2=N1.NameId AND PTNameFk2=N2.NameId)\par
                                           AND RelQualifierFk != '-99' AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\tab\tab END\par
\par
\tab   --higher taxa is smaller than Genus : add all elements that are taxonomically included in that higher taxa\par
\tab         INSERT INTO @Results \par
\tab         SELECT  PTNameFk2, PTRefFK2 , RelQualifierFk  , '_inverse',  PTNameFk1, PTRefFk1 , CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N2.RankFk, N1.RankFk,0\par
\tab         FROM RelPTaxon,Name N1, Name N2\par
\tab          WHERE (PTNameFk2 = @HigherTaxo AND PTRefFk2 = @RefId AND PTNameFk2 = N2.NameId AND PTNameFk1=N1.NameId)\par
\tab          AND RelQualifierFk =1 AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\par
\tab  \par
\tab   END\par
  IF @HigherId <= 40 \par
\tab   -- higher taxa is genus or greater add all elements linked to the current object\par
\tab   IF @CurrentStatus =1\par
\tab\tab   BEGIN\par
\tab\tab   INSERT INTO @Results \par
\tab\tab         SELECT  PTNameFk1, PTRefFK1 , RelQualifierFk  , '',  PTNameFk2, PTRefFk2 , CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk,1 \tab\tab         FROM RelPTaxon,Name N1, Name N2\par
\tab\tab         WHERE (PTNameFk1=@NameId AND PTRefFk1 = @RefId AND PTNameFk2 = N2.NameId AND PTNameFk1=N1.NameId)\par
\tab\tab         AND RelQualifierFK != -99 AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\par
\tab\tab   INSERT INTO @Results\par
\tab\tab         SELECT PTNameFk2, PTRefFK2, RelQualifierFk, '_inverse',  PTNameFK1, PTRefFk1, CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N2.RankFk, N2.RankFk,1\par
\tab\tab         FROM RelPTaxon,Name N1,Name N2\par
\tab\tab         WHERE (PTNameFk2 = @NameId AND PTRefFk2 = @RefId AND PTNameFk2=N1.NameId AND PTNameFk2=N2.NameId)\par
\tab\tab         AND RelQualifierFk != '-99' AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\tab\tab   END\par
\tab  IF @CurrentStatus !=1\par
\tab\tab BEGIN\par
\tab\tab\par
\tab\tab INSERT INTO @Results \par
\tab\tab\tab SELECT  PTNameFk1, PTRefFK1 , RelQualifierFk  , '',  PTNameFk2, PTRefFk2 , CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk,1\par
\tab\tab\tab FROM RelPTaxon,Name N1, Name N2\par
\tab\tab\tab WHERE (PTNameFk1=@AcceptedName AND PTRefFk1 = @RefId AND PTNameFk2 = N2.NameId AND PTNameFk1=N1.NameId)\par
\tab\tab\tab AND RelQualifierFK != -99 AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\tab\tab INSERT INTO @Results\par
\tab\tab\tab    SELECT PTNameFk2, PTRefFK2, RelQualifierFk, '_inverse',  PTNameFK1, PTRefFk1, CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR), N2.RankFk, N1.RankFk,1\par
\tab\tab\tab    FROM RelPTaxon,Name N1,Name N2\par
\tab\tab\tab    WHERE (PTNameFk2 = @AcceptedName AND PTRefFk2 = @RefId AND PTNameFk2=N1.NameId AND PTNameFk2=N2.NameId)\par
                                           AND RelQualifierFk != '-99' AND CAST(PTNameFk1 as VARCHAR)+'_'+CAST(PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\tab\tab END\par
\tab\tab\par
    DECLARE @min INT\par
    DECLARE @max INT\par
    SELECT @max=MAX(DISTINCT done) from @Results\par
    SELECT @min=MIN(DISTINCT done) from @Results \par
\par
DECLARE @nom1 INT\par
DECLARE @ref1 INT\par
DECLARE @nom2 INT\par
DECLARE @ref2 INT\par
DECLARE @rang2 INT\par
\par
   \par
  IF @min != @max\par
  BEGIN  SET @Done = 0  --0 peut aller plus loin: 1 s'arrete\par
  END\par
  ELSE SET @Done = 1\par
  \par
  WHILE @Done = 0\par
  BEGIN\par
           DECLARE monCurseur CURSOR FOR \par
           SELECT R.NameFK1,R.RefFK1,R.NameFK2,R.RefFK2,R.Rang2\par
           FROM @Results R\par
           WHERE R.done = 0\par
          OPEN monCurseur\par
          FETCH NEXT FROM monCurseur INTO @nom1,@ref1,@nom2, @ref2,@rang2\par
           WHILE @@FETCH_STATUS = 0 \par
              BEGIN\par
\tab\tab UPDATE @Results SET done=1 WHERE NameFK1=@nom1 AND NameFK2=@nom2 AND RefFK1=@ref1 AND RefFK2=@ref2\par
\tab\tab  INSERT INTO @Results \par
\tab\tab\tab SELECT  DISTINCT RPT.PTNameFk2,RPT.PTRefFK2, RPT.RelQualifierFk, '_inverse', RPT.PTNameFk1, RPT.PTRefFk1,CAST(RPT.PTNameFk1 as VARCHAR)+'_'+CAST(RPT.PTNameFk2 as VARCHAR), N2.RankFk, N1.RankFk,\par
                                      (CASE WHEN @rang2 >40 THEN 0 ELSE 1 END)\par
\tab\tab             FROM RelPTaxon RPT, @Results R, Name N1, Name N2            \par
\tab\tab             WHERE \par
\tab\tab                 RPT.PTRefFK2=@ref2 AND  RPT.PTNameFK2=@nom2  AND RPT.PTNameFk2 = N2.NameId  AND RPT.PTNameFk1=N1.NameId\par
\tab\tab                AND RPT.RelQualifierFk  != '-99' \par
\tab\tab                AND CAST(RPT.PTNameFk1 as VARCHAR)+'_'+CAST(RPT.PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results)\par
\par
\tab\tab INSERT INTO @Results \par
\tab\tab\tab  SELECT  DISTINCT RPT.PTNameFk1,RPT.PTRefFK1, RPT.RelQualifierFk, '',  RPT.PTNameFk2, RPT.PTRefFk2,CAST(RPT.PTNameFk1 as VARCHAR)+'_'+CAST(RPT.PTNameFk2 as VARCHAR), N1.RankFk, N2.RankFk,\par
\tab\tab\tab   (CASE WHEN @rang2 >40 THEN 0 ELSE 1 END)\tab\par
\tab\tab             FROM RelPTaxon RPT, @Results R, Name N1, Name N2            \par
\tab\tab             WHERE \par
\tab\tab                 RPT.PTRefFK1=@ref2 AND RPT.PTNameFK1=@nom2  AND RPT.PTNameFk1 = N1.NameId  AND RPT.PTNameFk2 = N2.NameId\par
\tab\tab                 AND RPT.RelQualifierFk  != '-99' \par
\tab\tab                 AND CAST(RPT.PTNameFk1 as VARCHAR)+'_'+CAST(RPT.PTNameFk2 as VARCHAR) NOT IN (SELECT Cle FROM @Results) \par
\par
\tab\tab FETCH NEXT FROM monCurseur INTO @nom2, @ref2,@rang2\par
\tab END\par
\par
\tab CLOSE monCurseur\par
\tab DEALLOCATE monCurseur\par
      \par
\tab SELECT @max=MAX(DISTINCT done) from @Results\par
\tab SELECT @min=MIN(DISTINCT done) from @Results \par
\tab    \par
\tab  IF @min != @max\par
\tab   BEGIN  SET @Done = 0  --0 peut aller plus loin: 1 s'arrete\par
\tab   END\par
\tab   ELSE SET @Done = 1\par
  \par
END\par
 \par
   RETURN\par
END\par
\par
\par
\par
\par
\par
\par
}