
PRINT 'EXECUTE 018'

/*

Returns true if given summaryStatus marks a questionable or false entry. 

author: Niels Hoffmann
create: 08 / 2007

*/
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'f_mclBook_occurrenceIsQuestionableOrFalse')
	DROP FUNCTION dbo.f_mclBook_occurrenceIsQuestionableOrFalse
GO

CREATE FUNCTION dbo.f_mclBook_occurrenceIsQuestionableOrFalse (@summaryStatus int) 
RETURNS int
AS
BEGIN
	DECLARE @result AS BIT
	SET @result = 0

	IF(@summaryStatus  = 110 OR
           @summaryStatus  = 210 OR
           @summaryStatus  = 310 OR
           @summaryStatus  = 220 OR
           @summaryStatus  = 320)
		SET @result = 1		

	RETURN @result
END 
GO

/*

Returns the IdInSource number if a preferredSource exists for this occuurenceId, 
returns NULL otherwise

author: Niels Hoffmann
create: 08 / 2007

*/
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'f_mclBook_occurrenceGetPreferredSource')
	DROP FUNCTION dbo.f_mclBook_occurrenceGetPreferredSource
GO

CREATE FUNCTION dbo.f_mclBook_occurrenceGetPreferredSource (@occurenceId int) 
RETURNS INT
AS
BEGIN
	DECLARE @result AS int
	SET @result = NULL

	IF(EXISTS (SELECT 
	               PreferredReferenceFlag 
	           FROM 
	               dbo.emOccurrenceSource 
	           WHERE 
	               occurrenceFk = @occurenceId  
	           GROUP BY 
	               PreferredReferenceFlag 
	               HAVING(NOT (dbo.emOccurrenceSource.PreferredReferenceFlag IS NULL))
	           ))
		SET @result = (SELECT 
		                   TOP 1 dbo.emOccurrenceSource.SourceNumber 
		               FROM 
		                   dbo.emOccurrenceSource 
		               WHERE 
		                   occurrenceSourceId = @occurenceId
		               )
	RETURN @result
END 
GO

/*

Translates IdInSource entries into referenceId's

author: Niels Hoffmann
create: 08 / 2007

*/

IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'f_mclBook_idInSourceToReferenceId')
	DROP FUNCTION dbo.f_mclBook_idInSourceToReferenceId
GO

CREATE FUNCTION dbo.f_mclBook_idInSourceToReferenceId (@idInSource varchar(5))
RETURNS int
AS 
BEGIN
	DECLARE @refId as int
	SET @refId = (SELECT RefFk FROM mclBook_RefSourceNumberRel WHERE SourceNumber = @idInSource)
	RETURN @refId
END
GO

/*

Tests if a given source is a mcl base flora

author: Niels Hoffmann
create: 08 / 2007

*/
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'f_mclBook_isBaseFlora')
	DROP FUNCTION dbo.f_mclBook_isBaseFlora
GO

CREATE FUNCTION dbo.f_mclBook_isBaseFlora (@source varchar(5))
RETURNS BIT
AS 
BEGIN
	DECLARE @result AS BIT
	SET @result = 0
	IF EXISTS (SELECT * 
			FROM Reference 
			WHERE RefId = dbo.f_mclBook_idInSourceToReferenceId(@source) AND (NOT(IdInMcl IS NULL)) AND (NOT(IsMCLBaseFlora IS NULL)) )
		SET @result = 1
	RETURN @result
END
GO


/*

Tests if a given source is a base flora for a certain area

author: Niels Hoffmann
create: 08 / 2007

*/
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'f_mclBook_isBaseFloraForArea')
	DROP FUNCTION dbo.f_mclBook_isBaseFloraForArea
GO

CREATE FUNCTION dbo.f_mclBook_isBaseFloraForArea (@source varchar(5), @areaId int)
RETURNS BIT
AS 
BEGIN
	DECLARE @result AS BIT
	SET @result = 0
	
	IF EXISTS (	SELECT 
				* 
			FROM 
				mclBook_RefAreaRel 
			WHERE 	
				RefFk = dbo.f_mclBook_idInSourceToReferenceId(@source) AND 
				AreaFk = @areaId 
		  )
		SET @result = 1
	RETURN @result
END
GO

/*

Compares two reference id's and returns the youngest of the two by comparing the 
RefYear field

author: Niels Hoffmann
create: 08 / 2007

*/
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'f_mclBook_getYoungestReference')
	DROP FUNCTION dbo.f_mclBook_getYoungestReference

GO

CREATE FUNCTION dbo.f_mclBook_getYoungestReference(@newReferenceId int, @oldReferenceId int)
RETURNS INT
AS 
BEGIN
	DECLARE @resultingReferenceId int
	SET @resultingReferenceId = 0

	
	--SET @resultingReferenceId = @oldReferenceId

	IF (@oldReferenceId = 0)
		SET @resultingReferenceId = @newReferenceId

	ELSE
	BEGIN
		DECLARE @newRefYear nvarchar (40)
		DECLARE @oldRefYear nvarchar (40)

		SET @newRefYear = (SELECT RefYear FROM Reference WHERE RefId = @newReferenceId)
		SET @oldRefYear = (SELECT RefYear FROM Reference WHERE RefId = @oldReferenceId)
	
		IF (@newRefYear > @oldRefYear)
			SET @resultingReferenceId = @newReferenceId
		ELSE
			SET @resultingReferenceId = @oldReferenceId
	END
	RETURN @resultingReferenceId
END
GO

/* Create a field in emOccurrence to store the preferred reference*/

IF EXISTS
   (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'emOccurrence'
            AND COLUMN_NAME = 'MCLPreferredRefFk'
            )

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

GO

ALTER TABLE [dbo].[emOccurrence]
      ADD [MCLPreferredRefFk] [int] NULL 

GO


-- ########################################################################## --
-- ################## calculatePreferredOccurrenceSourceById ################ --
-- ########################################################################## --

/** Calculate the preferred occurrence source for a given occurrence id
* 
* @author Niels Hoffmann
* @create 08 / 2007
*/
IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'p_mclBook_calculatePreferredOccurrenceSourceById')
	DROP PROCEDURE dbo.p_mclBook_calculatePreferredOccurrenceSourceById
GO

CREATE PROCEDURE dbo.p_mclBook_calculatePreferredOccurrenceSourceById 
	@OccurrenceId int,
	@AreaFk int = 0,
	@SummaryStatus int = 0
AS
BEGIN
	/* these fields we get from database
	
	but not at the moment
	these seem empty
	
	 */
	DECLARE 
	    @OccurrenceFk int, 
		@SourceNumber varchar(5), 
		@PreferredReferenceFlag BIT

	
	DECLARE emOccurrenceSourceCursor SCROLL CURSOR FOR
		SELECT 	
			OccurrenceFk, 
			SourceNumber, 
			PreferredReferenceFlag
		FROM 
			emOccurrenceSource 
		WHERE 
			OccurrenceFk = @OccurrenceId
	
	/* declare temporary variables to store temporary calculations in */
	DECLARE 
		@youngestBaseFloraRefId int, 
		@youngestBaseFloraForAreaRefId int,
		/* the RefId that is actually beeing processed */
		@actRefId int,
		/* count iterations */
		@loopCount int


	/* start iterating */
	OPEN 	emOccurrenceSourceCursor
	
	/* do the first iteration */
	FETCH FIRST FROM 
		emOccurrenceSourceCursor
	INTO 	
		@OccurrenceFk, 
		@SourceNumber, 
		@PreferredReferenceFlag


	SET @youngestBaseFloraRefId = 0 
	SET @youngestBaseFloraForAreaRefId = 0
	SET @loopCount = 0


	/* start looping */
	WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT 'loop run ' + cast(@loopCount AS varchar(20))
			SET @loopCount = @loopCount + 1
	    
			/* if there is a source that was chosen by hand it takes precedence 
			over calculated sources */
			IF (@PreferredReferenceFlag = 1)
				BEGIN
					PRINT 'entry has a PreferredReferenceFlag: ' + CAST (@SourceNumber AS varchar (20))
    	    
					UPDATE 
    						emOccurrence 
    					SET 
    						MCLPreferredRefFk = dbo.f_mclBook_idInSourceToReferenceId(@SourceNumber) 
    					WHERE 
    						OccurrenceId = @OccurrenceFk
            				-- Break out of the loop
    					BREAK;
    				END
	
	
			/* define reference id of the actual source */
			SET @actRefId = dbo.f_mclBook_idInSourceToReferenceId(@SourceNumber)
			PRINT 'actRefId: ' + cast(@actRefId AS varchar(20))
		

			IF(dbo.f_mclBook_isBaseFloraForArea(@SourceNumber, @AreaFk) = 1)
				/* if the actual sourceNumber is a base flora for the area
				we need to remember it and compare it to possible other basefloras that 
				are younger */
				SET @youngestBaseFloraForAreaRefId = dbo.f_mclBook_getYoungestReference(@actRefId, @youngestBaseFloraForAreaRefId)
			
			
			/* in case we don't find a base flora for the given area in sources
			we might find another base flora, that states this record */
			ELSE IF(dbo.f_mclBook_isBaseFlora(@SourceNumber) = 1)
				/* we need to remember the base flora here and compare it to possible other basefloras that 
				are younger */
				SET @youngestBaseFloraRefId = dbo.f_mclBook_getYoungestReference(@actRefId, @youngestBaseFloraRefId)
			
	
			FETCH NEXT FROM 
				emOccurrenceSourceCursor
			INTO 	
				@OccurrenceFk, 
				@SourceNumber, 
				@PreferredReferenceFlag
		END /* end of loop */
		
	IF(@loopCount = 0)
		BEGIN
			PRINT 'no sources found. setting MCLPreferredRefFk to -99 for further examination '
			UPDATE emOccurrence SET MCLPreferredRefFk = -99 WHERE OccurrenceId = @OccurrenceFk
		END
	ELSE
		BEGIN

			PRINT 'loop ended after runs: ' + cast(@loopCount AS varchar(20))
			
			DECLARE @fOrQ BIT
			SET @fOrQ = dbo.f_mclBook_occurrenceIsQuestionableOrFalse(@SummaryStatus)
		    /*
		    3) Bei Statusangaben "Q" oder "F" muss die Basisflora in jedem Fall mit ausgegeben werden.
		    */
			IF( @fOrQ = 1) 
				BEGIN
					IF(@youngestBaseFloraForAreaRefId != 0)
		        			BEGIN
							PRINT 'entry is Q or F and backed up by base flora for this area: ' + CAST (@youngestBaseFloraForAreaRefId AS varchar (20))
							UPDATE emOccurrence SET MCLPreferredRefFk = @youngestBaseFloraForAreaRefId WHERE OccurrenceId = @OccurrenceFk
						END
					ELSE IF(@youngestBaseFloraRefId != 0)
						BEGIN
		            				PRINT 'entry is Q or F and backed up by base flora: ' + CAST (@youngestBaseFloraRefId AS varchar (20))
				    			UPDATE emOccurrence SET MCLPreferredRefFk = @youngestBaseFloraRefId WHERE OccurrenceId = @OccurrenceFk
						END
					ELSE
						BEGIN
							PRINT 'entry is Q or F and has additional flora: ' + CAST (@actRefId AS varchar (20))
				    			UPDATE emOccurrence SET MCLPreferredRefFk = @actRefId WHERE OccurrenceId = @OccurrenceFk
						END
				END
			ELSE IF (@fOrQ = 0 AND @youngestBaseFloraForAreaRefId != 0)
				BEGIN
					PRINT 'is not Q or F. But has base flora for area: ' + CAST (@youngestBaseFloraForAreaRefId AS varchar (20))
					UPDATE emOccurrence SET MCLPreferredRefFk = NULL WHERE OccurrenceId = @OccurrenceFk
				END
			ELSE IF(@SummaryStatus = 350 AND @youngestBaseFloraForAreaRefId != 0)
				BEGIN
		    			PRINT 'no update. SummaryStatus is 350 and entry has base flora for area.'
				END
			/* 
			2) Dagegen muss die Nummer einer Basisflora ausgegeben werden, wenn sich die Angabe auf ein Areal bezieht, 
			das nicht dieser Standardflora zugeordnet ist.
			*/
			ELSE IF(@youngestBaseFloraForAreaRefId = 0 AND @youngestBaseFloraRefId != 0)
				BEGIN
					PRINT 'entry has a base flora entry that is not for this area: ' + CAST (@youngestBaseFloraRefId AS varchar (20))
					UPDATE emOccurrence SET MCLPreferredRefFk = @youngestBaseFloraRefId WHERE OccurrenceId = @OccurrenceFk
				END
			/*
			alles andere wird keine Basisflora sein und sollte deswegen mindestens eine PreferredReferenceFlag gesetzt haben
			*/
			ELSE IF(@youngestBaseFloraForAreaRefId = 0 AND @youngestBaseFloraRefId = 0)
				BEGIN
			    		IF (@loopCount = 1)
			    			BEGIN 
				 			PRINT 'no base floras found. Assuming additional flora: ' + CAST (@actRefId AS varchar (20))
				    			UPDATE emOccurrence SET MCLPreferredRefFk = @actRefId WHERE OccurrenceId = @OccurrenceFk
						END
				END
			ELSE
				BEGIN
					PRINT 'weird. set MCLPreferredRefFk to -1 for further examination'
					UPDATE emOccurrence SET MCLPreferredRefFk = -1 WHERE OccurrenceId = @OccurrenceFk
				END
			PRINT '========================='	
		
			/* debug 
			INSERT INTO 
			    dbo.mcl_debugOccRef 
				    (OccurrenceId, 
				     YoungestBaseFloraForAreaRefId, 
				     YoungestBaseFloraRefId, 
				     SummaryStatus, 
				     QuestionableOrFalse, 
				     AreaFk, 
				     Created_When)
			VALUES
				(@OccurrenceId, 
				 @youngestBaseFloraForAreaRefId, 
				 @youngestBaseFloraRefId, 
		    	 	 @SummaryStatus,
				 @fOrQ, 
				 @AreaFk, 
				 GETDATE())
			*/
		END
	CLOSE emOccurrenceSourceCursor
	DEALLOCATE emOccurrenceSourceCursor
END
GO

/* 
debug table

 */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mcl_debugOccRef]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mcl_debugOccRef]
GO

CREATE TABLE [dbo].[mcl_debugOccRef] (
	[OccurrenceId] [int] NULL ,
	[YoungestBaseFloraForAreaRefId] [int] NULL ,
	[YoungestBaseFloraRefId] [int] NULL ,
	[SummaryStatus] [int] NULL ,
	[QuestionableOrFalse] [tinyint] NULL ,
	[AreaFk] [int] NULL ,
	[Created_When] [datetime] NULL 
) ON [PRIMARY]
GO



/*

perform preferred occurrence source calculation on all MCL entries in emOccurrence

*/
DECLARE @OccurrenceId int, @AreaFk int, @SummaryStatus int

DECLARE emOccurrenceCursor SCROLL CURSOR FOR
	SELECT     
	    dbo.emOccurrence.OccurrenceId, 
	    dbo.emOccurrence.AreaFk, 
	    dbo.emOccurrence.SummaryStatus
    FROM         
        dbo.emOccurrence INNER JOIN
        dbo.emArea ON dbo.emOccurrence.AreaFk = dbo.emArea.AreaId
    WHERE
        (NOT (dbo.emArea.MCLCode IS NULL))
        AND 
        dbo.emOccurrence.SummaryStatus IN (110, 210, 310, 220, 320, 250, 260, 270, 280, 330, 340, 350)
        AND
        dbo.emOccurrence.PTRefFk IN (7000000,7700000)
        

OPEN emOccurrenceCursor

FETCH FIRST FROM emOccurrenceCursor
INTO @OccurrenceId, @AreaFk, @SummaryStatus 

WHILE @@FETCH_STATUS = 0
BEGIN
	
	-- perform calculations for every entry in emOccurrence
	EXEC dbo.p_mclBook_calculatePreferredOccurrenceSourceById @OccurrenceId, @AreaFk, @SummaryStatus

	FETCH NEXT FROM emOccurrenceCursor
	INTO @OccurrenceId, @AreaFk, @SummaryStatus 
END

CLOSE emOccurrenceCursor
DEALLOCATE emOccurrenceCursor
GO




