
PRINT 'EXECUTE 023'
GO

-- creating the view, that holds information on nomenclatural references of invalid designations

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_mclBook_invalidDesignationReferences]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_mclBook_invalidDesignationReferences]
GO

CREATE VIEW dbo.v_mclBook_invalidDesignationReferences
AS
SELECT     dbo.PTaxon.PTNameFk AS NameId, dbo.PTaxon.PTRefFk AS RefId, dbo.NomStatusRel.Notes, dbo.NomStatusRel.NomStatusRefFk, 
                      dbo.NomStatus.NomStatus
FROM         dbo.NomStatusRel INNER JOIN
                      dbo.Name ON dbo.NomStatusRel.NameFk = dbo.Name.NameId INNER JOIN
                      dbo.PTaxon ON dbo.Name.NameId = dbo.PTaxon.PTNameFk INNER JOIN
                      dbo.NomStatus ON dbo.NomStatusRel.NomStatusFk = dbo.NomStatus.NomStatusId
WHERE     (dbo.PTaxon.PTRefFk IN (7000000, 7700000)) AND (dbo.NomStatusRel.NomStatusRefFk IN (7000000, 7700000)) AND 
                      (dbo.NomStatus.NomStatus LIKE N'nom. inval.')
GO

-- pity is, that the references are IdInSource numbers that come as a space separated list
-- therefore we have to parse this list and return the correct IdInMCL

-- first of all we might need a function, that returns an IdInMCL for a given IdInSource-String

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

CREATE FUNCTION dbo.f_mclBook_getIdInMCL ( @IdInSource VARCHAR(100)) 
RETURNS varchar(10) 
AS
BEGIN
    DECLARE @refId as int
    DECLARE @IdInMCL as varchar(10)

    SET @refId = dbo.f_mclBook_idInSourceToReferenceId(@IdInSource)
    
    SET @IdInMCL = (SELECT IdInMCL FROM Reference WHERE RefId = @refId)
        
    RETURN @IdInMCL
END
GO





/*

create a field in name table for storing additional source information on invalid
designations. This is a special case for MCL. The information is not present in the database 
and will be entered there only for the purpose of generating MCL

*/

IF EXISTS
   (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'Name'
            AND COLUMN_NAME = 'OccRefString'
            )

  )
   ALTER TABLE [dbo].[Name]
      DROP COLUMN [OccRefString]

GO

ALTER TABLE dbo.Name 
	ADD OccRefString nvarchar(256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
GO

UPDATE Name SET OccRefString = '{Cass. in Cuvier, Dict. Sci. Nat. 18: 58. 1821}' WHERE NameId = 7000237;
UPDATE Name SET OccRefString = '{Soó in Acta Bot. Acad. Sci. Hung. 23: 386. 1978}' WHERE NameId = 7001709;
UPDATE Name SET OccRefString = '{H. Scholz in Florist. Rundbr. 27: 9. 1993}' WHERE NameId = 7000287;

GO

/* 
Get all IdInMCL references for a given invalid designation

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

CREATE PROCEDURE dbo.p_mclBook_getReferenceForInvalidDesignation
	@NameId int
AS
BEGIN
	DECLARE @referenceString varchar(100)
	SET @referenceString = (SELECT NOTES FROM dbo.v_mclBook_invalidDesignationReferences WHERE NameId = @NameId)
    SELECT 
        Items,
        dbo.f_mclBook_idInSourceToReferenceId(Items) AS RefId,
        (SELECT IsMCLRelevant FROM Reference WHERE RefId = dbo.f_mclBook_idInSourceToReferenceId(Items)) AS IsMCLRelevant,
        dbo.f_mclBook_getIdInMCL( Items ) AS IdInMCL 
    FROM 
        dbo.f_core_split(@referenceString, ' ')
END
GO

