
PRINT 'EXECUTE 029'

-- UPDATE all inlcuded taxa with the GenusSubdivisionEpi of the given taxon
if exists (select * from dbo.sysobjects where id = object_id(N'p_mclBook_writeGenusSubdivisionToIncludedTaxa') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_mclBook_writeGenusSubdivisionToIncludedTaxa]
GO


CREATE PROCEDURE dbo.p_mclBook_writeGenusSubdivisionToIncludedTaxa
    @nameId int,
    @refId int
AS

   UPDATE dbo.Name
       SET GenusSubdivisionEpi = (SELECT GenusSubdivisionEpi FROM dbo.Name WHERE NameId = @nameId)
   WHERE NameId IN (
           SELECT 
               PTNameFk1 
           FROM 
               dbo.RelPTaxon
           WHERE PTNameFk2 = @nameId AND PTRefFk2 = @refId AND RelQualifierFk = 1
   )
  
    
GO

-- find all sections (rankId 45) and call p_mclBook_writeGenusSubdivisionToIncludedTaxa on them
DECLARE @startRank int, @rank int

SET @startRank = 45

DECLARE rankCursor SCROLL CURSOR FOR
	SELECT 
		RankId 
	FROM 
		Rank 
	WHERE 
		RankId >= @startRank 
	ORDER BY 
		RankId ASC


OPEN rankCursor
FETCH FIRST FROM
	rankCursor
INTO
	@rank
	
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @rank
	
	DECLARE @nameFk int, @refFk int
	
	DECLARE innerCursor CURSOR FOR
		SELECT dbo.PTaxon.PTNameFk, dbo.PTaxon.PTRefFk
        FROM dbo.Name INNER JOIN
             dbo.PTaxon ON dbo.Name.NameId = dbo.PTaxon.PTNameFk
        WHERE  dbo.PTaxon.PTRefFk IN (7000000, 7700000) 
				AND dbo.Name.RankFk = 45
					AND (NOT (dbo.Name.GenusSubdivisionEpi IS NULL))
	
	OPEN innerCursor
	FETCH NEXT FROM innerCursor INTO @nameFk, @refFk
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
	    EXEC p_mclBook_writeGenusSubdivisionToIncludedTaxa @nameFk, @refFk
	    FETCH NEXT FROM innerCursor INTO @nameFk, @refFk
	END
	
	CLOSE innerCursor
    DEALLOCATE innerCursor
	
	FETCH NEXT FROM
		rankCursor
	INTO
		@rank
END

CLOSE rankCursor
DEALLOCATE rankCursor

    


