
PRINT 'EXECUTE 016'

/* Create IdInMCL */


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

  )
   ALTER TABLE [dbo].[Reference]
      DROP COLUMN [IdInMCL]

GO

ALTER TABLE dbo.Reference 
	ADD [IdInMCL] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO


-- populate with data 

-- stand 28.2.08

Update dbo.Reference set IdInMCL = 	1	WHERE IdInSource LIKE '69934' 
Update dbo.Reference set IdInMCL = 	2	WHERE IdInSource LIKE '32318' 
Update dbo.Reference set IdInMCL = 	3	WHERE IdInSource LIKE '23829' 
Update dbo.Reference set IdInMCL = 	4	WHERE IdInSource LIKE '69483' 
Update dbo.Reference set IdInMCL = 	5	WHERE IdInSource LIKE '69066' 
Update dbo.Reference set IdInMCL = 	6	WHERE IdInSource LIKE '69080' 
Update dbo.Reference set IdInMCL = 	7	WHERE IdInSource LIKE '69254' 
Update dbo.Reference set IdInMCL = 	8	WHERE IdInSource LIKE '24249' 
Update dbo.Reference set IdInMCL = 	9	WHERE IdInSource LIKE '69076' 
Update dbo.Reference set IdInMCL = 	10	WHERE IdInSource LIKE '69202' 
Update dbo.Reference set IdInMCL = 	11	WHERE IdInSource LIKE '69874' 
Update dbo.Reference set IdInMCL = 	12	WHERE IdInSource LIKE '69060' 
Update dbo.Reference set IdInMCL = 	13	WHERE IdInSource LIKE '69256' 
Update dbo.Reference set IdInMCL = 	14	WHERE IdInSource LIKE '69081' 
Update dbo.Reference set IdInMCL = 	15	WHERE IdInSource LIKE '69843' 
Update dbo.Reference set IdInMCL = 	16	WHERE IdInSource LIKE '69090' 
Update dbo.Reference set IdInMCL = 	17	WHERE IdInSource LIKE '69473' 
Update dbo.Reference set IdInMCL = 	18	WHERE IdInSource LIKE '69296' 
Update dbo.Reference set IdInMCL = 	19	WHERE IdInSource LIKE '69085' 
Update dbo.Reference set IdInMCL = 	20	WHERE IdInSource LIKE '69137' 
Update dbo.Reference set IdInMCL = 	21	WHERE IdInSource LIKE '31599' 
Update dbo.Reference set IdInMCL = 	22	WHERE IdInSource LIKE '31627' 
Update dbo.Reference set IdInMCL = 	23	WHERE IdInSource LIKE '69877' 
Update dbo.Reference set IdInMCL = 	24	WHERE IdInSource LIKE '32160 %' 
Update dbo.Reference set IdInMCL = 	25	WHERE IdInSource LIKE '24049' 
Update dbo.Reference set IdInMCL = 	26	WHERE IdInSource LIKE '69077' 
Update dbo.Reference set IdInMCL = 	27	WHERE IdInSource LIKE '24209' 
Update dbo.Reference set IdInMCL = 	28	WHERE IdInSource LIKE '24197' 
Update dbo.Reference set IdInMCL = 	29	WHERE IdInSource LIKE '69088' 
Update dbo.Reference set IdInMCL = 	30	WHERE IdInSource LIKE '69070' 
Update dbo.Reference set IdInMCL = 	31	WHERE IdInSource LIKE '69072' 
Update dbo.Reference set IdInMCL = 	32	WHERE IdInSource LIKE '69891' 
Update dbo.Reference set IdInMCL = 	33	WHERE IdInSource LIKE '35010' 
Update dbo.Reference set IdInMCL = 	34	WHERE IdInSource LIKE '% 25399 %' 
Update dbo.Reference set IdInMCL = 	35	WHERE IdInSource LIKE '70220' 
Update dbo.Reference set IdInMCL = 	36	WHERE IdInSource LIKE '34777' 
Update dbo.Reference set IdInMCL = 	37	WHERE IdInSource LIKE '69259' 
Update dbo.Reference set IdInMCL = 	38	WHERE IdInSource LIKE '69260' 
Update dbo.Reference set IdInMCL = 	39	WHERE IdInSource LIKE '69261' 
Update dbo.Reference set IdInMCL = 	40	WHERE IdInSource LIKE '28142' 
Update dbo.Reference set IdInMCL = 	41	WHERE IdInSource LIKE '69069' 
Update dbo.Reference set IdInMCL = 	42	WHERE IdInSource LIKE '24266' 
Update dbo.Reference set IdInMCL = 	43	WHERE IdInSource LIKE '69083' 
Update dbo.Reference set IdInMCL = 	44	WHERE IdInSource LIKE '24167' 
Update dbo.Reference set IdInMCL = 	45	WHERE IdInSource LIKE '69092' 
Update dbo.Reference set IdInMCL = 	46	WHERE IdInSource LIKE '69026' 
Update dbo.Reference set IdInMCL = 	47	WHERE IdInSource LIKE '71042' 
Update dbo.Reference set IdInMCL = 	48	WHERE IdInSource LIKE '26129' 
Update dbo.Reference set IdInMCL = 	49	WHERE IdInSource LIKE '29083' 
Update dbo.Reference set IdInMCL = 	50	WHERE IdInSource LIKE '23879' 
Update dbo.Reference set IdInMCL = 	51	WHERE IdInSource LIKE '69201' 
Update dbo.Reference set IdInMCL = 	52	WHERE IdInSource LIKE '69068' 
Update dbo.Reference set IdInMCL = 	53	WHERE IdInSource LIKE '69067' 
Update dbo.Reference set IdInMCL = 	54	WHERE IdInSource LIKE '69243' 
Update dbo.Reference set IdInMCL = 	55	WHERE IdInSource LIKE '69244' 
Update dbo.Reference set IdInMCL = 	56	WHERE IdInSource LIKE '69245' 
Update dbo.Reference set IdInMCL = 	57	WHERE IdInSource LIKE '69234' 
Update dbo.Reference set IdInMCL = 	58	WHERE IdInSource LIKE '69836' 
Update dbo.Reference set IdInMCL = 	59	WHERE IdInSource LIKE '69039' 
Update dbo.Reference set IdInMCL = 	60	WHERE IdInSource LIKE '24530' 
Update dbo.Reference set IdInMCL = 	61	WHERE IdInSource LIKE '24094' 
Update dbo.Reference set IdInMCL = 	62	WHERE IdInSource LIKE '69078' 
Update dbo.Reference set IdInMCL = 	63	WHERE IdInSource LIKE '28716' 
Update dbo.Reference set IdInMCL = 	64	WHERE IdInSource LIKE '31626' 
Update dbo.Reference set IdInMCL = 	65	WHERE IdInSource LIKE '69200' 
Update dbo.Reference set IdInMCL = 	66	WHERE IdInSource LIKE '24194' 
Update dbo.Reference set IdInMCL = 	67	WHERE IdInSource LIKE '69093' 
Update dbo.Reference set IdInMCL = 	68	WHERE IdInSource LIKE '23985 %' 
Update dbo.Reference set IdInMCL = 	69	WHERE IdInSource LIKE '24103' 
Update dbo.Reference set IdInMCL = 	70	WHERE IdInSource LIKE '26281' 
Update dbo.Reference set IdInMCL = 	71	WHERE IdInSource LIKE '23865' 
Update dbo.Reference set IdInMCL = 	72	WHERE IdInSource LIKE '69758' 
Update dbo.Reference set IdInMCL = 	73	WHERE IdInSource LIKE '28743' 
Update dbo.Reference set IdInMCL = 	74	WHERE IdInSource LIKE '69082' 
Update dbo.Reference set IdInMCL = 	75	WHERE IdInSource LIKE '69291' 
Update dbo.Reference set IdInMCL = 	76	WHERE IdInSource LIKE '69500' 
Update dbo.Reference set IdInMCL = 	77	WHERE IdInSource LIKE '69952' 
Update dbo.Reference set IdInMCL = 	78	WHERE IdInSource LIKE '69062' 
Update dbo.Reference set IdInMCL = 	79	WHERE IdInSource LIKE '69038' 
Update dbo.Reference set IdInMCL = 	80	WHERE IdInSource LIKE '69079' 
Update dbo.Reference set IdInMCL = 	81	WHERE IdInSource LIKE '69474' 
Update dbo.Reference set IdInMCL = 	82	WHERE IdInSource LIKE '69084' 
Update dbo.Reference set IdInMCL = 	83	WHERE IdInSource LIKE '23988' 
Update dbo.Reference set IdInMCL = 	84	WHERE IdInSource LIKE '28906' 
Update dbo.Reference set IdInMCL = 	85	WHERE IdInSource LIKE '70224' 
Update dbo.Reference set IdInMCL = 	86	WHERE IdInSource LIKE '69121' 
Update dbo.Reference set IdInMCL = 	87	WHERE IdInSource LIKE '27133' 
Update dbo.Reference set IdInMCL = 	88	WHERE IdInSource LIKE '24258' 
Update dbo.Reference set IdInMCL = 	89	WHERE IdInSource LIKE '69087' 
Update dbo.Reference set IdInMCL = 	90	WHERE IdInSource LIKE '69995' 
Update dbo.Reference set IdInMCL = 	91	WHERE IdInSource LIKE '69475' 
Update dbo.Reference set IdInMCL = 	92	WHERE IdInSource LIKE '69872' 
Update dbo.Reference set IdInMCL = 	93	WHERE IdInSource LIKE '69262' 
Update dbo.Reference set IdInMCL = 	94	WHERE IdInSource LIKE '69253' 
Update dbo.Reference set IdInMCL = 	95	WHERE IdInSource LIKE '70087' 
Update dbo.Reference set IdInMCL = 	96	WHERE IdInSource LIKE '69195' 
Update dbo.Reference set IdInMCL = 	97	WHERE IdInSource LIKE '69089' 
Update dbo.Reference set IdInMCL = 	98	WHERE IdInSource LIKE '69073' 
Update dbo.Reference set IdInMCL = 	99	WHERE IdInSource LIKE '30072 %' 






GO


/**
*    This function counts how many times a reference was used in MCL occurrences 
*
*    @return     the count or zero if not referenced 
*
*    @deprecated this function is not used anymore. We check if a flora is used
*    by checking its usage in MCLPreferredRefFk.
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_mclBook_countReferenceUsage]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_mclBook_countReferenceUsage]
GO

CREATE FUNCTION dbo.f_mclBook_countReferenceUsage (@idInSource VARCHAR(4000)) RETURNS int
AS
BEGIN
	DECLARE @returnValue AS int
	SET  @returnValue = 0
	
	DECLARE @source varchar(10)

	DECLARE sourceCursor CURSOR FOR
		SELECT * FROM dbo.f_core_split(@IdInSource, ' | ')

	OPEN sourceCursor
	FETCH NEXT FROM sourceCursor INTO @source
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @returnValue = @returnValue + 
			(	
			SELECT     
		        	COUNT(*)
		        FROM    
		        	dbo.emOccurrenceSource INNER JOIN
		        	dbo.emOccurrence ON emOccurrenceSource.OccurrenceFk = dbo.emOccurrence.OccurrenceId INNER JOIN
		        	dbo.emArea ON dbo.emOccurrence.AreaFk = dbo.emArea.AreaId
		        WHERE   
		        	(NOT (dbo.emArea.MCLCode IS NULL))
		        		AND 
		        	(dbo.emOccurrence.PTRefFk IN (7000000, 7700000))
		        		AND
		        	(dbo.emOccurrenceSource.SourceNumber = @source)
		      )
			
		FETCH NEXT FROM sourceCursor INTO @source
	END
	CLOSE sourceCursor
	DEALLOCATE sourceCursor 
			
	RETURN @returnValue
END
GO



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

  )
   ALTER TABLE [dbo].[Reference]
      DROP COLUMN [IsMCLFlora]

GO


ALTER TABLE dbo.Reference ADD IsMCLFlora bit NULL
GO

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

  )
   ALTER TABLE [dbo].[Reference]
      DROP COLUMN [IsMCLRelevant]

GO


ALTER TABLE dbo.Reference ADD IsMCLRelevant bit NULL
GO



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

  )
   ALTER TABLE [dbo].[Reference]
      DROP COLUMN [IsMCLBaseFlora]

GO


ALTER TABLE dbo.Reference ADD IsMCLBaseFlora bit NULL
GO


-- populate fields with information on baseFloras

UPDATE dbo.Reference SET IsMCLFlora = 1, IsMCLBaseFlora = 1, IsMCLRelevant = 1 WHERE IdInMCL <= 99
GO




