--use DiversityCollection_Test
--use DiversityCollection_CONC
--use DiversityCollection_BASETest

IF (SELECT [dbo].[Version]()) <> '02.05.06'
BEGIN
RAISERROR ('WRONG VERION. Script is scheduled as update for version 02.05.06', 18, 1) 
END
GO


--#####################################################################################################################
--######  Adding ReferenceDetails to the tables CollectionEvent, -Specimen and Identification  ########################
--#####################################################################################################################

if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionEvent' and C.COLUMN_NAME = 'ReferenceDetails') < 1 begin 
ALTER TABLE [dbo].[CollectionEvent] ADD ReferenceDetails nvarchar(50) NULL; end 
GO

if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionSpecimen' and C.COLUMN_NAME = 'ReferenceDetails') < 1 begin 
ALTER TABLE [dbo].[CollectionSpecimen] ADD ReferenceDetails nvarchar(50) NULL; end 
GO

if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Identification' and C.COLUMN_NAME = 'ReferenceDetails') < 1 begin 
ALTER TABLE [dbo].[Identification] ADD ReferenceDetails nvarchar(50) NULL; end 
GO

--###### log tables   ######################################################################################

if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionEvent_log' and C.COLUMN_NAME = 'ReferenceDetails') < 1 begin 
ALTER TABLE [dbo].[CollectionEvent_log] ADD ReferenceDetails nvarchar(50) NULL; end 
GO

if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionSpecimen_log' and C.COLUMN_NAME = 'ReferenceDetails') < 1 begin 
ALTER TABLE [dbo].[CollectionSpecimen_log] ADD ReferenceDetails nvarchar(50) NULL; end 
GO

if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Identification_log' and C.COLUMN_NAME = 'ReferenceDetails') < 1 begin 
ALTER TABLE [dbo].[Identification_log] ADD ReferenceDetails nvarchar(50) NULL; end 
GO

--###### trigger CollectionEvent  ##################################################################################

ALTER TRIGGER [dbo].[trgDelCollectionEvent] ON [dbo].[CollectionEvent] 
FOR DELETE AS 
/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 
/* saving the original dataset in the logging table */ 
INSERT INTO CollectionEvent_Log (CollectionEventID, Version, SeriesID, CollectorsEventNumber, CollectionDate, CollectionDay, CollectionMonth, CollectionYear, CollectionDateSupplement, CollectionDateCategory, CollectionTime, CollectionTimeSpan, LocalityDescription, HabitatDescription, ReferenceTitle, ReferenceURI, ReferenceDetails, CollectingMethod, Notes, CountryCache, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState) 
SELECT deleted.CollectionEventID, deleted.Version, deleted.SeriesID, deleted.CollectorsEventNumber, deleted.CollectionDate, deleted.CollectionDay, deleted.CollectionMonth, deleted.CollectionYear, deleted.CollectionDateSupplement, deleted.CollectionDateCategory, deleted.CollectionTime, deleted.CollectionTimeSpan, deleted.LocalityDescription, deleted.HabitatDescription, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.ReferenceDetails, deleted.CollectingMethod, deleted.Notes, deleted.CountryCache, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  'D'
FROM DELETED
GO

ALTER TRIGGER [dbo].[trgUpdCollectionEvent] ON [dbo].[CollectionEvent] 
FOR UPDATE AS
/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 30.08.2007  */ 
if not update(Version) 
BEGIN

/* setting the version in the main table */ 
DECLARE @i int 
DECLARE @ID int
DECLARE @Version int

set @i = (select count(*) from deleted) 

if @i = 1 
BEGIN 
   SET  @ID = (SELECT CollectionEventID FROM deleted)
   EXECUTE procSetVersionCollectionEvent @ID
END 

/* updating the CollectionDate  */
Update CollectionEvent
set CollectionDate = case when isdate
(cast (	cast( CollectionEvent.CollectionDay as varchar ) + '.' + 
	cast( CollectionEvent.CollectionMonth as varchar )  + '.' + 
	cast( CollectionEvent.CollectionYear as varchar )
as varchar)
) = 1
then cast (
	cast( CollectionEvent.CollectionDay as varchar ) + '.' + 
	cast( CollectionEvent.CollectionMonth as varchar )  + '.' + 
	cast( CollectionEvent.CollectionYear as varchar )
as datetime) 
else null
end
FROM CollectionEvent, deleted 
where 1 = 1 
AND CollectionEvent.CollectionEventID = deleted.CollectionEventID


/* setting the date in table CollectionEventSeries */
update X
set X.DateCache = E.CollectionDate from CollectionEventSeries X, CollectionEvent E, deleted i 
where not E.CollectionDate is null and not E.SeriesID is null and E.SeriesID = i.SeriesID and E.SeriesID = X.SeriesID 
and E.CollectionDate = (select min(CollectionDate) from CollectionEvent where SeriesID = i.SeriesID and not CollectionDate is null 
and CollectionEventID <> i.CollectionEventID) and  X.DateCache <> E.CollectionDate


/* saving the original dataset in the logging table */ 
INSERT INTO CollectionEvent_Log (CollectionEventID, Version, SeriesID, CollectorsEventNumber, CollectionDate, CollectionDay, CollectionMonth, CollectionYear, CollectionDateSupplement, CollectionDateCategory, CollectionTime, CollectionTimeSpan, LocalityDescription, HabitatDescription, ReferenceTitle, ReferenceURI, ReferenceDetails, CollectingMethod, Notes, CountryCache, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy, LogState) 
SELECT deleted.CollectionEventID, deleted.Version, deleted.SeriesID, deleted.CollectorsEventNumber, deleted.CollectionDate, deleted.CollectionDay, deleted.CollectionMonth, deleted.CollectionYear, deleted.CollectionDateSupplement, deleted.CollectionDateCategory, deleted.CollectionTime, deleted.CollectionTimeSpan, deleted.LocalityDescription, deleted.HabitatDescription, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.ReferenceDetails, deleted.CollectingMethod, deleted.Notes, deleted.CountryCache, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy, 'U'
FROM DELETED

END

/* updating the logging columns */
Update CollectionEvent
set LogUpdatedWhen = getdate(), LogUpdatedBy = current_user
FROM CollectionEvent, deleted 
where 1 = 1 
AND CollectionEvent.CollectionEventID = deleted.CollectionEventID

GO

--###### trigger CollectionSpecimen  ##################################################################################

ALTER TRIGGER [dbo].[trgDelCollectionSpecimen] ON [dbo].[CollectionSpecimen] 
FOR DELETE AS 

/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 


/* saving the original dataset in the logging table */ 
INSERT INTO CollectionSpecimen_Log (CollectionSpecimenID, Version, CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, ReferenceDetails, Problems, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState) 
SELECT deleted.CollectionSpecimenID, deleted.Version, deleted.CollectionEventID, deleted.CollectionID, deleted.AccessionNumber, deleted.AccessionDate, deleted.AccessionDay, deleted.AccessionMonth, deleted.AccessionYear, deleted.AccessionDateSupplement, deleted.AccessionDateCategory, deleted.DepositorsName, deleted.DepositorsAgentURI, deleted.DepositorsAccessionNumber, deleted.LabelTitle, deleted.LabelType, deleted.LabelTranscriptionState, deleted.LabelTranscriptionNotes, deleted.ExsiccataURI, deleted.ExsiccataAbbreviation, deleted.OriginalNotes, deleted.AdditionalNotes, deleted.InternalNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.ReferenceDetails, deleted.Problems, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  'D'
FROM DELETED

GO


ALTER TRIGGER [dbo].[trgUpdCollectionSpecimen] ON [dbo].[CollectionSpecimen] 
FOR UPDATE AS
/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 
if not update(Version) 
BEGIN

/* setting the version in the main table */ 
DECLARE @i int 
DECLARE @ID int
DECLARE @Version int

set @i = (select count(*) from deleted) 

if @i = 1 
BEGIN 
   SET  @ID = (SELECT CollectionSpecimenID FROM deleted)
   EXECUTE procSetVersionCollectionSpecimen @ID
END 

/* saving the original dataset in the logging table */ 
INSERT INTO CollectionSpecimen_Log (CollectionSpecimenID, Version, CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, ReferenceDetails, Problems, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState) 
SELECT deleted.CollectionSpecimenID, deleted.Version, deleted.CollectionEventID, deleted.CollectionID, deleted.AccessionNumber, deleted.AccessionDate, deleted.AccessionDay, deleted.AccessionMonth, deleted.AccessionYear, deleted.AccessionDateSupplement, deleted.AccessionDateCategory, deleted.DepositorsName, deleted.DepositorsAgentURI, deleted.DepositorsAccessionNumber, deleted.LabelTitle, deleted.LabelType, deleted.LabelTranscriptionState, deleted.LabelTranscriptionNotes, deleted.ExsiccataURI, deleted.ExsiccataAbbreviation, deleted.OriginalNotes, deleted.AdditionalNotes, deleted.InternalNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.ReferenceDetails, deleted.Problems, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  'U'
FROM DELETED

END

/* updating the logging columns */
Update CollectionSpecimen
set LogUpdatedWhen = getdate(), LogUpdatedBy = current_user, LabelTranscriptionState = CASE WHEN CollectionSpecimen.LabelTranscriptionState = 'not started' THEN 'incomplete' ELSE CollectionSpecimen.LabelTranscriptionState END
FROM CollectionSpecimen, deleted 
where 1 = 1 
AND CollectionSpecimen.CollectionSpecimenID = deleted.CollectionSpecimenID

GO


--###### trigger Identification  ##################################################################################

ALTER TRIGGER [dbo].[trgDelIdentification] ON [dbo].[Identification] 
FOR DELETE AS 

/*  Created by DiversityWorkbench Administration.  */ 
/*  Administration  1.0.0.0 */ 
/*  Date: 01.09.2006  */ 


/* setting the version in the main table */ 
declare @i int 
set @i = (select count(*) from deleted) 
if @i = 1 
begin 
DECLARE @ID int
SET  @ID = (SELECT CollectionSpecimenID FROM deleted)
EXECUTE procSetVersionCollectionSpecimen @ID
end 

DECLARE @Version int
SET @Version = (SELECT Version FROM CollectionSpecimen WHERE CollectionSpecimenID = @ID)

/* saving the original dataset in the logging table */ 
if (not @Version is null) 
begin
INSERT INTO Identification_Log (CollectionSpecimenID, IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, ReferenceDetails, Notes, ResponsibleName, ResponsibleAgentURI, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogVersion,  LogState) 
SELECT deleted.CollectionSpecimenID, deleted.IdentificationUnitID, deleted.IdentificationSequence, deleted.IdentificationDate, deleted.IdentificationDay, deleted.IdentificationMonth, deleted.IdentificationYear, deleted.IdentificationDateSupplement, deleted.IdentificationDateCategory, deleted.VernacularTerm, deleted.TaxonomicName, deleted.NameURI, deleted.IdentificationCategory, deleted.IdentificationQualifier, deleted.TypeStatus, deleted.TypeNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.ReferenceDetails, deleted.Notes, deleted.ResponsibleName, deleted.ResponsibleAgentURI, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  @Version,  'D'
FROM DELETED
end
else
begin
INSERT INTO Identification_Log (CollectionSpecimenID, IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, ReferenceDetails, Notes, ResponsibleName, ResponsibleAgentURI, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogVersion, LogState) 
SELECT deleted.CollectionSpecimenID, deleted.IdentificationUnitID, deleted.IdentificationSequence, deleted.IdentificationDate, deleted.IdentificationDay, deleted.IdentificationMonth, deleted.IdentificationYear, deleted.IdentificationDateSupplement, deleted.IdentificationDateCategory, deleted.VernacularTerm, deleted.TaxonomicName, deleted.NameURI, deleted.IdentificationCategory, deleted.IdentificationQualifier, deleted.TypeStatus, deleted.TypeNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.ReferenceDetails, deleted.Notes, deleted.ResponsibleName, deleted.ResponsibleAgentURI, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy, CollectionSpecimen.Version, 'D' 
FROM DELETED, CollectionSpecimen
WHERE deleted.CollectionSpecimenID = CollectionSpecimen.CollectionSpecimenID
end

/* setting the LastIdentificationCache if the deleted dataset was the last identification*/
update IdentificationUnit
set LastIdentificationCache = 
case 
when a.TaxonomicName is null or  a.TaxonomicName = '' 
then 
	case 
	when a.VernacularTerm is null or  a.VernacularTerm = '' 
	then IdentificationUnit.TaxonomicGroup + ' [ ' + cast(a.IdentificationUnitID as nvarchar) + ' ]'
	else a.VernacularTerm
	end
else a.TaxonomicName
end 
from IdentificationUnit, Identification a, deleted
where IdentificationUnit.CollectionSpecimenID = deleted.CollectionSpecimenID
and IdentificationUnit.IdentificationUnitID = deleted.IdentificationUnitID
and a.CollectionSpecimenID = deleted.CollectionSpecimenID
and a.IdentificationUnitID = deleted.IdentificationUnitID
and a.IdentificationSequence = 
(select max(b.IdentificationSequence) 
from Identification b
where b.CollectionSpecimenID = a.CollectionSpecimenID
and b.IdentificationUnitID = a.IdentificationUnitID
group by b.IdentificationUnitID, b.CollectionSpecimenID)
and LastIdentificationCache <> case 
when a.TaxonomicName is null or  a.TaxonomicName = '' 
then 
	case 
	when a.VernacularTerm is null or  a.VernacularTerm = '' 
	then IdentificationUnit.TaxonomicGroup + ' [ ' + cast(a.IdentificationUnitID as nvarchar) + ' ]'
	else a.VernacularTerm
	end
else a.TaxonomicName
end

GO



ALTER TRIGGER [dbo].[trgUpdIdentification] ON [dbo].[Identification] 
FOR UPDATE AS

/*  Created by DiversityWorkbench Administration.  */ 
/*  Administration  1.0.0.0 */ 
/*  Date: 01.09.2006  */ 

/* setting the version in the main table */ 
declare @i int 
set @i = (select count(*) from deleted) 
if @i = 1 
begin 
DECLARE @ID int
SET  @ID = (SELECT CollectionSpecimenID FROM deleted)
EXECUTE procSetVersionCollectionSpecimen @ID
end 

DECLARE @Version int
SET @Version = (SELECT Version FROM CollectionSpecimen WHERE CollectionSpecimenID = @ID)

/* updating the LastIdentificationCache in IdentificationUnit */
update IdentificationUnit
set LastIdentificationCache = 
case 
when a.TaxonomicName is null or  a.TaxonomicName = '' 
then 
	case 
	when a.VernacularTerm is null or  a.VernacularTerm = '' 
	then IdentificationUnit.TaxonomicGroup + ' [ ' + cast(a.IdentificationUnitID as nvarchar) + ' ]'
	else a.VernacularTerm
	end
else a.TaxonomicName
end 
from IdentificationUnit, Identification a, inserted
where IdentificationUnit.CollectionSpecimenID = inserted.CollectionSpecimenID
and IdentificationUnit.IdentificationUnitID = inserted.IdentificationUnitID
and a.CollectionSpecimenID = inserted.CollectionSpecimenID
and a.IdentificationUnitID = inserted.IdentificationUnitID
and a.IdentificationSequence = 
(select max(b.IdentificationSequence) 
from Identification b
where b.CollectionSpecimenID = a.CollectionSpecimenID
and b.IdentificationUnitID = a.IdentificationUnitID
group by b.IdentificationUnitID, b.CollectionSpecimenID)
and (LastIdentificationCache is null or LastIdentificationCache <> case 
when a.TaxonomicName is null or  a.TaxonomicName = '' 
then 
	case 
	when a.VernacularTerm is null or  a.VernacularTerm = '' 
	then IdentificationUnit.TaxonomicGroup + ' [ ' + cast(a.IdentificationUnitID as nvarchar) + ' ]'
	else a.VernacularTerm
	end
else a.TaxonomicName
end )

/* updating the logging columns */
Update Identification
set LogUpdatedWhen = getdate(), LogUpdatedBy = current_user
FROM Identification, deleted 
where 1 = 1 
AND Identification.CollectionSpecimenID = deleted.CollectionSpecimenID
AND Identification.IdentificationSequence = deleted.IdentificationSequence
AND Identification.IdentificationUnitID = deleted.IdentificationUnitID

/* saving the original dataset in the logging table */ 
if (not @Version is null) 
begin
INSERT INTO Identification_Log (CollectionSpecimenID, IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, ReferenceDetails, Notes, ResponsibleName, ResponsibleAgentURI, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogVersion,  LogState) 
SELECT deleted.CollectionSpecimenID, deleted.IdentificationUnitID, deleted.IdentificationSequence, deleted.IdentificationDate, deleted.IdentificationDay, deleted.IdentificationMonth, deleted.IdentificationYear, deleted.IdentificationDateSupplement, deleted.IdentificationDateCategory, deleted.VernacularTerm, deleted.TaxonomicName, deleted.NameURI, deleted.IdentificationCategory, deleted.IdentificationQualifier, deleted.TypeStatus, deleted.TypeNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.ReferenceDetails, deleted.Notes, deleted.ResponsibleName, deleted.ResponsibleAgentURI, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  @Version,  'U'
FROM DELETED
end
else
begin
INSERT INTO Identification_Log (CollectionSpecimenID, IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, ReferenceDetails, Notes, ResponsibleName, ResponsibleAgentURI, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogVersion, LogState) 
SELECT deleted.CollectionSpecimenID, deleted.IdentificationUnitID, deleted.IdentificationSequence, deleted.IdentificationDate, deleted.IdentificationDay, deleted.IdentificationMonth, deleted.IdentificationYear, deleted.IdentificationDateSupplement, deleted.IdentificationDateCategory, deleted.VernacularTerm, deleted.TaxonomicName, deleted.NameURI, deleted.IdentificationCategory, deleted.IdentificationQualifier, deleted.TypeStatus, deleted.TypeNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.ReferenceDetails, deleted.Notes, deleted.ResponsibleName, deleted.ResponsibleAgentURI, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy, CollectionSpecimen.Version, 'U' 
FROM DELETED, CollectionSpecimen
WHERE deleted.CollectionSpecimenID = CollectionSpecimen.CollectionSpecimenID
end

GO

--#####################################################################################################################
--######   Lebensraumtypen   ######################################################################################
--#####################################################################################################################


if (select COUNT(*) from Property where PropertyID = 40) = 0
begin
INSERT INTO [dbo].[Property]
           ([PropertyID]
           ,[PropertyName]
           ,[ParsingMethodName]
           ,[DisplayText]
           ,[DisplayEnabled]
           ,[Description])
     VALUES
           (40
           ,'Lebensraumtyp (LfU)'
           ,'Vegetation'
           ,'Lebensraumtyp (LfU)'
           ,1
           ,'Lebensraumtypen (Bayerisches Landesamt für Umwelt)')
end


--#####################################################################################################################
--######   audio / video   ######################################################################################
--#####################################################################################################################

if (select COUNT(*) from [dbo].[CollSpecimenImageType_Enum] where [Code] = 'audio') = 0
begin
INSERT INTO [dbo].[CollSpecimenImageType_Enum]
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable])
     VALUES
           ('audio'
           ,'audio record of e.g. the song of an observed bird'
           ,'audio'
           ,10
           ,1)
end


if (select COUNT(*) from [dbo].[CollSpecimenImageType_Enum] where [Code] = 'audio (for transcription)') = 0
begin
INSERT INTO [dbo].[CollSpecimenImageType_Enum]
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable])
     VALUES
           ('audio (for transcription)'
           ,'audio record of spoken comments that can be deleted after transcription into the database'
           ,'audio (for transcription)'
           ,11
           ,1)
end


if (select COUNT(*) from [dbo].[CollSpecimenImageType_Enum] where [Code] = 'video') = 0
begin
INSERT INTO [dbo].[CollSpecimenImageType_Enum]
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable])
     VALUES
           ('video'
           ,'video record of e.g. an observed organism'
           ,'video'
           ,12
           ,1)
end


if (select COUNT(*) from [dbo].CollEventImageType_Enum where [Code] = 'audio (for transcription)') = 0
begin
INSERT INTO [dbo].CollEventImageType_Enum
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable])
     VALUES
           ('audio (for transcription)'
           ,'audio record of spoken comments that can be deleted after transcription into the database'
           ,'audio (for transcription)'
           ,11
           ,1)
end


if (select COUNT(*) from [dbo].CollEventSeriesImageType_Enum where [Code] = 'audio (for transcription)') = 0
begin
INSERT INTO [dbo].CollEventSeriesImageType_Enum
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable])
     VALUES
           ('audio (for transcription)'
           ,'audio record of spoken comments that can be deleted after transcription into the database'
           ,'audio (for transcription)'
           ,11
           ,1)
end

GO

--#####################################################################################################################
--######   Processing Hierarchy  ######################################################################################
--#####################################################################################################################


ALTER FUNCTION [dbo].[ProcessingChildNodes] (@ID int)  
RETURNS @ItemList TABLE (ProcessingID int primary key,
	ProcessingParentID int NULL ,
	DisplayText nvarchar (50)   NULL ,
	Description nvarchar  (500)   NULL ,
	Notes nvarchar  (1000)   NULL ,
	ProcessingURI varchar  (255)   NULL ,
	OnlyHierarchy bit NULL)  
/*
Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item.
MW 21.10.2009
*/
AS
BEGIN
   RETURN
END

GO


ALTER FUNCTION [dbo].[ProcessingChildNodes] (@ID int)  
RETURNS @ItemList TABLE (ProcessingID int primary key,
	ProcessingParentID int NULL ,
	DisplayText nvarchar (50)   NULL ,
	Description nvarchar  (500)   NULL ,
	Notes nvarchar  (1000)   NULL ,
	ProcessingURI varchar  (255)   NULL ,
	OnlyHierarchy bit NULL)  
/*
Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item.
MW 21.10.2009
*/
AS
BEGIN
   declare @ParentID int
   DECLARE @TempItem TABLE (ProcessingID int primary key,
	ProcessingParentID int NULL ,
	DisplayText nvarchar (50)   NULL ,
	Description nvarchar  (500)   NULL ,
	Notes nvarchar  (1000)   NULL ,
	ProcessingURI varchar  (255)   NULL ,
	OnlyHierarchy bit NULL)  

 INSERT @TempItem (ProcessingID , ProcessingParentID, DisplayText , Description , Notes , ProcessingURI, OnlyHierarchy) 
	SELECT ProcessingID , ProcessingParentID, DisplayText , Description , Notes , ProcessingURI, OnlyHierarchy
	FROM Processing WHERE ProcessingParentID = @ID 

   DECLARE HierarchyCursor  CURSOR for
   select ProcessingID from @TempItem
   open HierarchyCursor
   FETCH next from HierarchyCursor into @ParentID
   WHILE @@FETCH_STATUS = 0
   BEGIN
	insert into @TempItem select ProcessingID , ProcessingParentID, DisplayText , Description , Notes , ProcessingURI, OnlyHierarchy
	from dbo.ProcessingChildNodes (@ParentID) where ProcessingID not in (select ProcessingID from @TempItem)
   	FETCH NEXT FROM HierarchyCursor into @ParentID
   END
   CLOSE HierarchyCursor
   DEALLOCATE HierarchyCursor
 INSERT @ItemList (ProcessingID , ProcessingParentID, DisplayText , Description , Notes , ProcessingURI, OnlyHierarchy) 
   SELECT distinct ProcessingID , ProcessingParentID, DisplayText , Description , Notes , ProcessingURI, OnlyHierarchy
   FROM @TempItem ORDER BY DisplayText
   RETURN
END

GO


/****** Object:  UserDefinedFunction [dbo].[ProcessingHierarchy]    Script Date: 10/21/2009 15:13:52 ******/
ALTER FUNCTION [dbo].[ProcessingHierarchy] (@ID int)  
RETURNS @List TABLE ([ProcessingID] [int] Primary key ,
	[ProcessingParentID] [int] NULL ,
	[DisplayText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
	[Notes] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
	[ProcessingURI] [varchar] (255) COLLATE Latin1_General_CI_AS NULL,
	[OnlyHierarchy] [bit] NULL)  

/*
Returns a table that lists all the analysis items related to the given processing.
MW 21.10.2009
*/
AS
BEGIN

-- getting the TopID
declare @TopID int
declare @i int

set @TopID = (select ProcessingParentID from Processing where ProcessingID = @ID) 

set @i = (select count(*) from Processing where ProcessingID = @ID)

if (@TopID is null )
	set @TopID =  @ID
else	
	begin
	while (@i > 0)
		begin
		set @ID = (select ProcessingParentID from Processing where ProcessingID = @ID and not ProcessingParentID is null) 
		set @i = (select count(*) from Processing where ProcessingID = @ID and not ProcessingParentID is null)
		end
	set @TopID = @ID
	end


-- get the ID's of the child nodes
-- copy the root node in the result list
   INSERT @List
   SELECT DISTINCT ProcessingID, ProcessingParentID, DisplayText, Description, Notes, ProcessingURI, OnlyHierarchy
   FROM Processing
   WHERE Processing.ProcessingID = @TopID

-- copy the child nodes into the result list
   INSERT @List
   SELECT * FROM dbo.ProcessingChildNodes (@TopID)

   RETURN
END



GO



--#####################################################################################################################
--######   Result list for Analysis   ######################################################################################
--#####################################################################################################################

if (select count(*) from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'AnalysisResult') = 0 begin 

CREATE TABLE [dbo].[AnalysisResult](
	[AnalysisID] [int] NOT NULL,
	[AnalysisResult] [nvarchar](255) NOT NULL,
	[Description] [nvarchar](500) NULL,
	[DisplayText] [nvarchar](50) NULL,
	[DisplayOrder] [smallint] NULL,
	[Notes] [nvarchar](500) NULL,
	[LogInsertedBy] [nvarchar](50) NULL,
	[LogInsertedWhen] [smalldatetime] NULL,
	[LogUpdatedBy] [nvarchar](50) NULL,
	[LogUpdatedWhen] [smalldatetime] NULL,
 CONSTRAINT [PK_AnalysisResult] PRIMARY KEY CLUSTERED 
(
	[AnalysisID] ASC,
	[AnalysisResult] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

end

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID of the analysis (Primary key)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'AnalysisID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The categorized value of the analysis' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'AnalysisResult'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Description of enumerated object, displayed in the user interface' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'Description'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Short abbreviated description of the object, displayed in the user interface' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'DisplayText'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The order in which the entries are displayed. The order may be changed at any time, but all values must be unique.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'DisplayOrder'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Internal development notes about usage, definition, etc. of an enumerated object' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'Notes'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of user who first entered (typed or imported) the data.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'LogInsertedBy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time when the data were first entered (typed or imported) into this database.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'LogInsertedWhen'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of user who last updated the data.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'LogUpdatedBy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date and time when the data were last updated.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult', @level2type=N'COLUMN',@level2name=N'LogUpdatedWhen'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Value lists for analysis types with predefined values, e.g. "0, 1, 2, 3, ..." for Red list category. Includes description etc. for the values in the list.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AnalysisResult'
GO

ALTER TABLE [dbo].[AnalysisResult]  WITH CHECK ADD  CONSTRAINT [FK_AnalysisResult_Analysis] FOREIGN KEY([AnalysisID])
REFERENCES [dbo].[Analysis] ([AnalysisID])
GO

ALTER TABLE [dbo].[AnalysisResult] CHECK CONSTRAINT [FK_AnalysisResult_Analysis]
GO

ALTER TABLE [dbo].[AnalysisResult] ADD  CONSTRAINT [DF_AnalysisResult_LogInsertedBy]  DEFAULT (user_name()) FOR [LogInsertedBy]
GO

ALTER TABLE [dbo].[AnalysisResult] ADD  CONSTRAINT [DF_AnalysisResult_LogInsertedWhen]  DEFAULT (getdate()) FOR [LogInsertedWhen]
GO

ALTER TABLE [dbo].[AnalysisResult] ADD  CONSTRAINT [DF_AnalysisResult_LogUpdatedBy]  DEFAULT (user_name()) FOR [LogUpdatedBy]
GO

ALTER TABLE [dbo].[AnalysisResult] ADD  CONSTRAINT [DF_AnalysisResult_LogUpdatedWhen]  DEFAULT (getdate()) FOR [LogUpdatedWhen]
GO


GRANT DELETE ON [dbo].[AnalysisResult] TO [DiversityCollectionAdministrator] AS [dbo]
GO
GRANT INSERT ON [dbo].[AnalysisResult] TO [DiversityCollectionAdministrator] AS [dbo]
GO
GRANT UPDATE ON [dbo].[AnalysisResult] TO [DiversityCollectionAdministrator] AS [dbo]
GO
GRANT SELECT ON [dbo].[AnalysisResult] TO [DiversityCollectionUser] AS [dbo]
GO



--#####################################################################################################################
--######   setting the Version   ######################################################################################
--#####################################################################################################################


ALTER FUNCTION [dbo].[Version] ()  
RETURNS nvarchar(8)
AS
BEGIN
RETURN '02.05.07'
END

GO

