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

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

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

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

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

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

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

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

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

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

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



SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


--created by Markus Döring
--8.03.2005
--updated by Markus Döring
--24.05.2006
CREATE   FUNCTION [dbo].[f_species2000_getStatus] (@RIdentifier AS INT)
RETURNS NVARCHAR(50) AS
BEGIN
	DECLARE @Species2000Status  AS NVarchar(50)
	DECLARE @StatusID	AS INT
	DECLARE @Published	AS BIT
	DECLARE @Doubtful	AS char
	DECLARE @Rank	AS INT
	DECLARE @PTRefID	AS INT
		SELECT
			@PTRefID  = PTrefFk,
			@StatusID = StatusFk,
			@Published=PublishFlag,
			@Doubtful = DoubtfulFlag,
			@Rank     = RankFk
		FROM PTaxon join Name on PTNameFk=NameId
		WHERE RIdentifier=@RIdentifier

	-- calculate species2000 status
	IF (@Doubtful = 'i') SET @Species2000Status='inactive'
	ELSE IF (@Doubtful = 'd') SET @Species2000Status='provisional' 
	ELSE BEGIN
		IF @StatusID = 1 BEGIN
			IF (@Published=0) SET @Species2000Status='misapplied' 
			ELSE SET @Species2000Status='accepted' 
		END ELSE If @StatusID = 2 BEGIN
			SET @Species2000Status='synonym' 
		END ELSE If @StatusID = 3 BEGIN 
			-- partial synonym
			SET @Species2000Status='ambiguous' 
		END ELSE If @StatusID = 4 BEGIN 
			-- pro parte synonym
			SET @Species2000Status='synonym' 
		END
	END

	RETURN @Species2000Status
END












GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO





--created by Markus Döring
--8.03.2005
CREATE  FUNCTION [dbo].[f_species2000_getAuthorString] (@NameID AS INT)
RETURNS NVARCHAR(1000) AS
BEGIN
	DECLARE @AuthorString  AS NVarchar(1000)

	/* TAKEN FROM B-MODEL NAME TRIGGER: */
	/* Declarations */
	DECLARE @AuthorTeam NVARCHAR(80)
	DECLARE @ExAuthorTeam NVARCHAR(80)
	DECLARE @BasAuthorTeam NVARCHAR(80)
	DECLARE @ExBasAuthorTeam NVARCHAR(80)
	DECLARE @BasAuthorStr NVARCHAR(160)
	DECLARE @NormalAuthorStr NVARCHAR(160)

	/* retrieve authorteam data */
SELECT 
	@AuthorTeam=at1.AuthorTeamCache, 
	@ExAuthorTeam=at2.AuthorTeamCache, 
	@BasAuthorTeam=at3.AuthorTeamCache, 
	@ExBasAuthorTeam=at4.AuthorTeamCache
FROM Name
	left join AuthorTeam at1 on at1.AuthorTeamId = AuthorTeamFk
	left join AuthorTeam at2 on at2.AuthorTeamId = ExAuthorTeamFk
	left join AuthorTeam at3 on at3.AuthorTeamId = BasAuthorTeamFk
	left join AuthorTeam at4 on at4.AuthorTeamId = ExBasAuthorTeamFk
WHERE NameId = @NameId

	/* The Author section */
	/* create substring for authors */
	IF (@ExBasAuthorTeam IS NOT NULL)BEGIN
		SET @BasAuthorStr = dbo.f_Core_StrSpaceAdd(@ExBasAuthorTeam + ' ex', @BasAuthorTeam)
	END ELSE BEGIN
		SET @BasAuthorStr = @BasAuthorTeam
	END
	IF (@ExAuthorTeam IS NOT NULL)BEGIN
		SET @NormalAuthorStr  = dbo.f_Core_StrSpaceAdd(@ExAuthorTeam + ' ex', @AuthorTeam)
	END ELSE BEGIN
		SET @NormalAuthorStr = @AuthorTeam
	END
	SET @AuthorString = dbo.f_Core_StrSpaceAdd('(' + @BasAuthorStr + ')', @NormalAuthorStr  )
	/* End of the Author section */

	RETURN @AuthorString
END






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO





/*	Author: M. Döring  */
/*	Created:  3/2005		*/
/*	Updated: 5/2006	*/
/* Returns the family name string of a given potential taxon.
If the passed taxonID is above family level, return NULL.
*/

CREATE      FUNCTION [dbo].[f_species2000_getFamily] (@TaxonID AS INT)
RETURNS VARCHAR(500) AS
BEGIN
	DECLARE @Family VARCHAR(500)
	DECLARE @TempID INT
	DECLARE @TempNameFk INT
	DECLARE @TempReferenceFk INT

	-- get initial accepted taxon data.
	select @TempNameFk=PTNameFk, @TempReferenceFk=PTRefFk  from dbo.SPICE_PublishedTaxa_VIEW WHERE TaxonID = @TaxonID
	-- traverse taxonomic hierarchy
	WHILE EXISTS (
		SELECT * FROM RelPTaxon join Name on PTNameFk2 = NameId
		WHERE (PTNameFk1=@TempNameFk) AND (PTRefFk1=@TempReferenceFk) 
		AND (RankFk >= 20) AND (RelQualifierFk in (1,-99) )
	) BEGIN 
		SELECT @TempNameFk=PTNameFk2, @TempReferenceFk=PTRefFk2 FROM RelPTaxon WHERE (PTNameFk1=@TempNameFk) AND (PTRefFk1=@TempReferenceFk) AND (RelQualifierFk in (1,-99))
	END
	SET @Family=NULL
	SELECT @Family=NameCache FROM PTaxon join name on nameid=ptnamefk WHERE (PTNameFk=@TempNameFk) AND (PTRefFk=@TempReferenceFk) AND (RankFk=20)

	RETURN @Family
END








GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


-------------------------------------
--created by M.Döring 19.07.2005
---------------------------------------
CREATE      FUNCTION dbo.f_species2000_getNomRefTitle (@RefId AS INT) RETURNS NVARCHAR(2000)
AS
BEGIN
DECLARE @NomRefcache nvarchar(2000)
DECLARE @RefCategoryFk INT
DECLARE @InRefFk INT
DECLARE @NomTitleAbbrev nvarchar ( 500)
DECLARE @NomAuthorTeamFk INT
DECLARE @Edition nvarchar ( 50 )
DECLARE @Volume nvarchar ( 20 )
DECLARE @Series nvarchar ( 20 )
DECLARE @RefYear nvarchar ( 40 )
DECLARE @Authorteam nvarchar(255)

DECLARE @prefixEdition varchar(10)
DECLARE @prefixSeries varchar(10)
DECLARE @prefixVolume varchar(10)
DECLARE @blank CHAR(1)
DECLARE @comma CHAR(1)
DECLARE @dot CHAR(1)
DECLARE @prefixReference varchar(10)
DECLARE @prefixReferenceJounal varchar(10)
	SET @prefixEdition = 'ed.'
	SET @prefixSeries = 'ser.'
	SET @prefixVolume = 'vol.'
	SET @blank = ' '
	SET @comma =','
	SET @dot = '.'
	SET @prefixReference = 'in'
	SET @prefixReferenceJounal = 'in'


 SELECT @RefCategoryFk=RefCategoryFk, -- Get the Reference category
		@InRefFk=InRefFk,
		@NomTitleAbbrev=dbo.f_core_replNullTrim(NomTitleAbbrev) ,
		@NomAuthorTeamFk=NomAuthorTeamFk,
		@Edition=	dbo.f_core_replNullTrim(Edition),
		@Volume=	dbo.f_core_replNullTrim(Volume),
		@Series=	dbo.f_core_replNullTrim(Series),
		@RefYear=	dbo.f_core_replNullTrim(RefYear)
 FROM   Reference
 WHERE  RefId =@RefId
	
-- ==============================================================================
-- Concatenate the NomRefCache
-- ==============================================================================

			If @RefCategoryFk in (3, 10) BEGIN -- book or unresolved
			SET @NomRefCache = dbo.f_core_cumulateBookNomRefCache('', @NomTitleAbbrev, @Edition, @Series, @Volume, '', @prefixEdition, @prefixSeries, @prefixVolume, @blank, @comma, @dot)
			END ELSE If @RefCategoryFk = 9 BEGIN -- journal
			SET @NomRefCache = @NomTitleAbbrev
			END ELSE If @RefCategoryFk = 1 BEGIN --article in periodical
			SET @NomTitleAbbrev = dbo.f_core_getNomRefCacheStringForCategory1(@InRefFk)
			SET @NomRefCache = dbo.f_core_cumulateJournalNomRefCache('', @NomTitleAbbrev, @Series, @Volume, '', @prefixSeries, @prefixReferenceJounal, @blank, @comma, @dot)
			END ELSE If @RefCategoryFk = 2 BEGIN -- part of title
			SET @NomRefCache = @prefixReference + @blank + dbo.f_core_getNomRefCacheStringForCategory2(@InRefFk)
			END  ELSE If @RefCategoryFk IS NULL OR @RefCategoryFk BETWEEN 4 AND  8 BEGIN --no nomenclatur citation
			SET @NomRefCache = NULL
                		END
-- ==============================================================================
-- Return the NomRefCache of this record
-- ==============================================================================
Return @NomRefCache
-- =============================================================================
END

















GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE    VIEW dbo.SPICE_Name_VIEW
AS
SELECT     
dbo.Name.NameId, dbo.Name.NameCache, dbo.f_species2000_getAuthorString(dbo.Name.NameId) AS FullAuthorCache, 
'FullSpeciesAuthorCache' = CASE WHEN rankfk > 60 THEN NULL ELSE dbo.f_species2000_getAuthorString(dbo.Name.NameId) END, 
dbo.Name.SupragenericName, dbo.Name.Genus, dbo.Name.SpeciesEpi, dbo.Name.InfraSpeciesEpi, 
dbo.Name.RankFk, dbo.Rank.Rank, dbo.Rank.RankAbbrev, 
dbo.Name.NomRefFk, dbo.Name.NomRefDetailFk

FROM         dbo.Name INNER JOIN dbo.Rank ON dbo.Name.RankFk = dbo.Rank.RankId




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




create   VIEW dbo.SPICE_PublishedTaxa_VIEW
AS

SELECT     RIdentifier AS TaxonId, PTNameFk, PTRefFk, StatusFk, 1 AS RelStatus, PublishFlag, RIdentifier AS AccTaxonFk
FROM         dbo.PTaxon
WHERE     (PublishFlag = 1) AND statusfk = 1
UNION
SELECT     PTaxon_Syn.RIdentifier, PTaxon_Syn.PTNameFk, PTaxon_Syn.PTRefFk, PTaxon_Syn.StatusFk, dbo.RelPTaxon.RelQualifierFk, 
                      PTaxon_Syn.PublishFlag, PTaxon.RIdentifier
FROM         dbo.PTaxon INNER JOIN
                      dbo.RelPTaxon ON dbo.PTaxon.PTNameFk = dbo.RelPTaxon.PTNameFk2 AND dbo.PTaxon.PTRefFk = dbo.RelPTaxon.PTRefFk2 INNER JOIN
                      dbo.PTaxon PTaxon_Syn ON dbo.RelPTaxon.PTNameFk1 = PTaxon_Syn.PTNameFk AND dbo.RelPTaxon.PTRefFk1 = PTaxon_Syn.PTRefFk
WHERE     (dbo.PTaxon.PublishFlag = 1) AND (PTaxon.statusfk = 1) AND (dbo.RelPTaxon.RelQualifierFk > 1) AND (dbo.RelPTaxon.RelQualifierFk < 10)




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE    VIEW dbo.SPICE_Reference_VIEW
AS
SELECT     dbo.Reference.RefId, dbo.Reference.RefCache, dbo.f_species2000_getNomRefTitle(RefId) 
                      AS Title, 'Author' = CASE WHEN dbo.Reference.RefAuthorString IS NULL 
                      THEN dbo.AuthorTeam.AuthorTeamCache ELSE dbo.Reference.RefAuthorString END, dbo.Reference.RefYear
FROM         dbo.Reference LEFT JOIN
                      
                      dbo.AuthorTeam ON dbo.Reference.NomAuthorTeamFk = dbo.AuthorTeam.AuthorTeamId









GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO







CREATE    VIEW dbo.SPICE_TaxonDetailed_VIEW
AS

SELECT     PT.*, dbo.f_species2000_getStatus(PT.TaxonId) AS Status, 
	N.*, dbo.f_species2000_getFamily(PT.TaxonId) AS Family
FROM  dbo.SPICE_PublishedTaxa_VIEW AS PT 
join dbo.SPICE_Name_VIEW as N on N.Nameid=PT.PTNameFk




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






CREATE    VIEW dbo.SPICE_TaxonID2HigherTaxonID_VIEW
AS
SELECT     PT.RIdentifier as TaxonID, PTaxon_Higher.RIdentifier AS HigherTaxonID, dbo.Name.RankFk
FROM         dbo.PTaxon as PT 
	INNER JOIN
             dbo.RelPTaxon 
		ON PT.PTNameFk = dbo.RelPTaxon.PTNameFk1 
		AND PT.PTRefFk = dbo.RelPTaxon.PTRefFk1 
	INNER JOIN
             dbo.PTaxon PTaxon_Higher 
		ON dbo.RelPTaxon.PTNameFk2 = PTaxon_Higher.PTNameFk 
		AND dbo.RelPTaxon.PTRefFk2 = PTaxon_Higher.PTRefFk 
	INNER JOIN
             dbo.Name ON PT.PTNameFk = dbo.Name.NameId
WHERE     (dbo.RelPTaxon.RelQualifierFk = 1) 	






GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO








CREATE         PROCEDURE [dbo].[s_spice_exportViews] AS

print 'Updating table SPICE_PublishedTaxa'
--delete from dbo.SPICE_PublishedTaxa
insert into dbo.SPICE_PublishedTaxa select * from dbo.SPICE_PublishedTaxa_VIEW

print 'Updating table SPICE_Name'
--delete from dbo.SPICE_Name
insert into dbo.SPICE_Name select * from dbo.SPICE_Name_VIEW

print 'Updating table SPICE_TaxonID2HigherTaxonID'
--delete from dbo.SPICE_TaxonID2HigherTaxonID
insert into dbo.SPICE_TaxonID2HigherTaxonID select * from dbo.SPICE_TaxonID2HigherTaxonID_VIEW

print 'Updating table SPICE_AdditionalTaxonInfo'
--delete from dbo.SPICE_AdditionalTaxonInfo
insert into dbo.SPICE_AdditionalTaxonInfo select * from dbo.SPICE_AdditionalTaxonInfo_VIEW

print 'Updating table SPICE_Reference'
--delete from dbo.SPICE_Reference
insert into dbo.SPICE_Reference select * from dbo.SPICE_Reference_VIEW





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




--created by Markus Döring
--8.03.2005
CREATE  FUNCTION [dbo].[f_species2000_getOccurence] (@OccurId AS INT)
RETURNS NVARCHAR(100) AS
BEGIN
	DECLARE @Occurence  AS NVarchar(100)
	DECLARE @Doubtful	AS bit
	DECLARE @Extinct	AS bit
		SELECT
			@Doubtful  = doubtful,
			@Extinct = extinct
		FROM PTaxonGeoTDWG
		WHERE id=@OccurId

	-- calculate result

	IF @Doubtful = 1 BEGIN
		SET @Occurence='doubtful'
		IF @Extinct = 1 BEGIN
			SET @Occurence='doubtful, extinct'
		END
	END ELSE IF @Extinct = 1 SET @Occurence='extinct'

	RETURN @Occurence
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO