
PRINT 'EXECUTE 020'

/* Create InMclArea */

-- create field InMCLArea
IF EXISTS
   (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PTaxon'
            AND COLUMN_NAME = 'InMCLArea'
            )

  )
   ALTER TABLE [dbo].[PTaxon]
      DROP COLUMN [InMCLArea]

GO

ALTER TABLE dbo.PTaxon 
	ADD [InMCLArea] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO


-- update every PTaxon that has an occurrence record in mclArea

UPDATE 
	dbo.PTaxon 
SET 
	InMCLArea = 1  
FROM
        dbo.PTaxon INNER JOIN
	dbo.emOccurrence ON dbo.PTaxon.PTNameFk = dbo.emOccurrence.PTNameFk AND dbo.PTaxon.PTRefFk = dbo.emOccurrence.PTRefFk INNER JOIN
	dbo.emArea ON dbo.emOccurrence.AreaFk = dbo.emArea.AreaId
WHERE     
	(NOT (dbo.emArea.MCLCode IS NULL))




-- calculate anything higher than species

DECLARE @startRank int, @rank int

SET @startRank = 60

DECLARE rankCursor SCROLL CURSOR FOR
	SELECT 
		RankId 
	FROM 
		Rank 
	WHERE 
		RankId <= @startRank 
	ORDER BY 
		RankId DESC


OPEN rankCursor
FETCH FIRST FROM
	rankCursor
INTO
	@rank

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @rank
	UPDATE 
		dbo.PTaxon
	SET 
		InMclArea = 1
	FROM    
		dbo.Name INNER JOIN
		dbo.PTaxon ON dbo.Name.NameId = dbo.PTaxon.PTNameFk INNER JOIN
		dbo.PTaxon PTaxon_1 INNER JOIN
		dbo.RelPTaxon ON PTaxon_1.PTNameFk = dbo.RelPTaxon.PTNameFk1 AND PTaxon_1.PTRefFk = dbo.RelPTaxon.PTRefFk1 INNER JOIN
		dbo.Name Name_1 ON PTaxon_1.PTNameFk = Name_1.NameId ON dbo.PTaxon.PTNameFk = dbo.RelPTaxon.PTNameFk2 AND 
		dbo.PTaxon.PTRefFk = dbo.RelPTaxon.PTRefFk2
	WHERE
		(dbo.RelPTaxon.RelQualifierFk = 1) AND (Name_1.RankFk = @rank) AND (PTaxon_1.InMCLArea = '1')


	FETCH NEXT FROM
		rankCursor
	INTO
		@rank
END

CLOSE rankCursor
DEALLOCATE rankCursor