
PRINT 'EXECUTE 014'
GO

/* create MCLDistComplete 

Add a new column to store information about endemism in conjunction with MedCheckList.
Populate the column afterwards. Relies on function dbo.f_mclBook_isEndemic() which has to
be present in database. 

Author:	Niels Hoffmann
Date:	06/2007

*/

-- create the field in table emArea
IF EXISTS
   (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'emOccurrence'
            AND COLUMN_NAME = 'MCLDistComplete'
            )

  )
   ALTER TABLE [dbo].[emOccurrence]
      DROP COLUMN [MCLDistComplete]

GO

ALTER TABLE dbo.emOccurrence 
	ADD MCLDistComplete varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO

-- create function 

/*

Calculates if a taxon is endemic for medChecklist Area.

Author:	Niels Hoffmann
Date:	06/2007

*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_mclBook_isEndemic]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_mclBook_isEndemic]
GO


CREATE FUNCTION dbo.f_mclBook_isEndemic (@nameId INT, @refId INT) RETURNS varchar AS
BEGIN
	
	DECLARE @isEndemic AS bit
	SET @isEndemic = 0

	-- we start by assuming that a taxon that is endemic for MCL-area must at least be endemic for EM-area
	IF (SELECT COUNT(*) FROM dbo.emOccurrence WHERE (SummaryStatus = 999) AND(PTNameFk = @nameId)AND(PTRefFk = @refId)) > 0
	BEGIN
		-- all of this taxons entries in emOccurrence have to be native and explicitly in emArea
		/*
		Choose all entries with SummaryStatus 350 (native) and an areaFk that is not in MclArea.
		If the count of that query is bigger than 0, it is not endemic for MclArea, otherwise it is. */
		IF 	(SELECT COUNT(*) 
			 FROM dbo.emOccurrence 
			 WHERE 	(SummaryStatus = 350) AND
				(AreaFk NOT IN(SELECT AreaId FROM dbo.emArea WHERE MCLCode <> '')) AND
				(PTRefFk = @refId) AND
				(PTNameFk = @nameId)
			)>0
		BEGIN			
			SET @isEndemic = 0
		END
		ELSE
			SET @isEndemic = 1
	END
	
	RETURN @isEndemic
END

GO




-- populate only the rows that have the WordlDistComplete information.
UPDATE dbo.emOccurrence set MCLDistComplete = dbo.f_mclBook_isEndemic(PTNameFk, PTRefFk) WHERE SummaryStatus = 999
GO


