--use DiversityCollection_Test
--use DiversityCollection_CONC

IF (SELECT DBO.VERSION()) <> '02.05.04'
BEGIN
RAISERROR ('WRONG VERION. Script is scheduled as update for version 02.05.04', 18, 1) 
END
GO


--#####################################################################################################################

GRANT SELECT ON [dbo].[Collection] TO [DiversityCollectionManager]
GO

GRANT UPDATE ON [dbo].[Collection] TO [DiversityCollectionManager]
GO

--#####################################################################################################################
-- EventSeriesTopID -------------------------------------------------------------------------------------
--#####################################################################################################################

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventSeriesTopID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
ALTER FUNCTION [dbo].[EventSeriesTopID] (@SeriesID int)
RETURNS int
/*
Returns the top ID within the hierarchy for a given ID from the table CollectionEventSeries.
MW 03.01.2006
*/
AS
BEGIN
declare @TopID int
declare @i int

set @TopID = (select SeriesParentID from CollectionEventSeries where SeriesID = @SeriesID) 

set @i = (select count(*) from CollectionEventSeries where SeriesID = @SeriesID)

if (@TopID is null )
	set @TopID =  @SeriesID
else	
	begin
	while (@i > 0)
		begin
		set @SeriesID = (select SeriesParentID from CollectionEventSeries where SeriesID = @SeriesID and not SeriesParentID is null) 
		set @i = (select count(*) from CollectionEventSeries 
		where SeriesID = @SeriesID 
		and not SeriesParentID is null
		and SeriesParentID <> @SeriesID)
		end
	set @TopID = @SeriesID
	end
return @TopID
END
' 
END

GO

--#####################################################################################################################
-- EventSeriesChildNodes -------------------------------------------------------------------------------------
--#####################################################################################################################

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventSeriesChildNodes]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
ALTER FUNCTION [dbo].[EventSeriesChildNodes] (@ID int)  
RETURNS @ItemList TABLE (SeriesID int primary key,
   SeriesParentID int NULL,
   DateCache datetime NULL,
   SeriesCode nvarchar (50)  NULL ,
   Description nvarchar (500)  NULL ,
   Notes nvarchar (500) )
/*
Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item.
MW 09.06.2009
*/
AS
BEGIN
   declare @ParentID int
   DECLARE @TempItem TABLE (SeriesID int primary key,
   SeriesParentID int NULL,
   DateCache datetime NULL,
   SeriesCode nvarchar (50)  NULL ,
   Description nvarchar (500)  NULL ,
   Notes nvarchar (500) )

-- insert the first childs into the table
 INSERT @TempItem (SeriesID, SeriesParentID, DateCache, SeriesCode, Description,  Notes) 
	SELECT SeriesID, SeriesParentID, DateCache, SeriesCode, Description,  Notes
	FROM CollectionEventSeries WHERE SeriesParentID = @ID 

-- for each child get the childs
   DECLARE HierarchyCursor  CURSOR for
   select SeriesID from @TempItem
   open HierarchyCursor
   FETCH next from HierarchyCursor into @ParentID
   WHILE @@FETCH_STATUS = 0
   AND @ParentID not in (select SeriesID from @TempItem)
   AND @ParentID not in (select SeriesParentID from @TempItem)
   BEGIN
		insert into @TempItem select SeriesID, SeriesParentID, DateCache, SeriesCode, Description,  Notes 
		from dbo.EventSeriesChildNodes (@ParentID) where SeriesID not in (select SeriesID from @TempItem)
   		FETCH NEXT FROM HierarchyCursor into @ParentID
   END
   CLOSE HierarchyCursor
   DEALLOCATE HierarchyCursor
 INSERT @ItemList (SeriesID, SeriesParentID, DateCache, SeriesCode, Description,  Notes) 
   SELECT distinct SeriesID, SeriesParentID, DateCache, SeriesCode, Description,  Notes
   FROM @TempItem ORDER BY DateCache
   RETURN
END
' 
END

GO


--#####################################################################################################################
-- EventSeriesHierarchy -------------------------------------------------------------------------------------
--#####################################################################################################################

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventSeriesHierarchy]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
ALTER FUNCTION [dbo].[EventSeriesHierarchy] (@SeriesID int)  
RETURNS @EventSeriesList TABLE (SeriesID int primary key,
   SeriesParentID int NULL,
   DateCache datetime NULL,
   SeriesCode nvarchar(50) NULL,
   Description nvarchar(500) NULL,
   Notes nvarchar(500) NULL)

/*
Returns a table that lists all the Series related to the given Series.
MW 02.01.2006
*/
AS
BEGIN

-- getting the TopID
declare @TopID int
declare @i int
set @TopID = (select dbo.EventSeriesTopID(@SeriesID) )

-- get the ID s of the child nodes
DECLARE @TempItem TABLE (SeriesID int primary key,
   SeriesParentID int NULL,
   DateCache datetime NULL,
   SeriesCode nvarchar(50) NULL,
   Description nvarchar(500) NULL,
   Notes nvarchar(500) NULL)

	INSERT @TempItem (SeriesID, SeriesParentID, DateCache, SeriesCode, Description, Notes) 
	SELECT * FROM dbo.EventSeriesChildNodes (@TopID)

-- copy the root node in the result list
   INSERT @EventSeriesList
   SELECT DISTINCT SeriesID, SeriesParentID, DateCache, SeriesCode, Description, Notes
   FROM CollectionEventSeries
   WHERE CollectionEventSeries.SeriesID = @TopID
   AND SeriesID NOT IN (SELECT SeriesID FROM @EventSeriesList)

   -- copy the child nodes into the result list
   INSERT @EventSeriesList
   SELECT DISTINCT SeriesID, SeriesParentID, DateCache, SeriesCode, Description, Notes
   FROM CollectionEventSeries
   WHERE CollectionEventSeries.SeriesID in (select SeriesID from @TempItem)
   AND SeriesID NOT IN (SELECT SeriesID FROM @EventSeriesList)
   ORDER BY DateCache

   RETURN
END' 
END

GO


--#####################################################################################################################
-- EventSeriesSuperiorList -------------------------------------------------------------------------------------
--#####################################################################################################################

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventSeriesSuperiorList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
ALTER FUNCTION [dbo].[EventSeriesSuperiorList] (@SeriesID int)  
RETURNS @EventSeriesList TABLE (SeriesID int primary key,
   SeriesParentID int NULL,
   DateCache datetime NULL,
   SeriesCode nvarchar(50) NULL,
   Description nvarchar(500) NULL,
   Notes nvarchar(500) NULL)

/*
Returns a table that lists all the Series above the given Series.
MW 02.01.2006
*/
AS
BEGIN

	while (not @SeriesID is null)
		begin
		INSERT @EventSeriesList
		SELECT DISTINCT SeriesID, SeriesParentID, DateCache, SeriesCode, Description, Notes
		FROM CollectionEventSeries
		WHERE CollectionEventSeries.SeriesID = @SeriesID
		AND CollectionEventSeries.SeriesID NOT IN (SELECT SeriesID FROM @EventSeriesList)
		set @SeriesID = (select SeriesParentID from CollectionEventSeries where SeriesID = @SeriesID)
		IF @SeriesID = (select SeriesParentID from CollectionEventSeries where SeriesID = @SeriesID)
			begin 
			set @SeriesID = null
			end 
		end
   RETURN
END
' 
END

GO


--#####################################################################################################################


declare @i int
set @i = (select count(*) from LocalisationSystem where LocalisationSystemID = 14)
if (@i = 0)
begin
INSERT INTO LocalisationSystem
           (LocalisationSystemID
           ,LocalisationSystemName
           ,DefaultMeasurementUnit
           ,ParsingMethodName
           ,DisplayText
           ,DisplayEnable
           ,DisplayOrder
           ,[Description]
           ,DisplayTextLocation1
           ,DescriptionLocation1
           ,DisplayTextLocation2
           ,DescriptionLocation2
           )
     VALUES
           (14
           ,'Depth'
           ,'m'
           ,'Height'
           ,'Depth'
           ,1
           ,201
           ,'Depth in meters below the surface of e.g. a water body at which the collection was made'
           ,'Depth from'
           ,'All material collected was at least this deep'
           ,'to'
           ,'All material collected was at most this deep'
           )
END
GO



declare @i int
set @i = (select count(*) from LocalisationSystem where LocalisationSystemID = 15)
if (@i = 0)
begin
INSERT INTO LocalisationSystem
           (LocalisationSystemID
           ,LocalisationSystemName
           ,DefaultMeasurementUnit
           ,ParsingMethodName
           ,DisplayText
           ,DisplayEnable
           ,DisplayOrder
           ,[Description]
           ,DisplayTextLocation1
           ,DescriptionLocation1
           ,DisplayTextLocation2
           ,DescriptionLocation2
           )
     VALUES
           (15
           ,'Height'
           ,'m'
           ,'Height'
           ,'Height'
           ,1
           ,202
           ,'Height resp. distance from groundlevel when e.g. located in a tree'
           ,'Height from'
           ,'All material collected was at least at this hight'
           ,'to'
           ,'All material collected was at most at this hight'
           )
END
GO


--#####################################################################################################################
/*
update [LocalisationSystem]
set [Description] = 'Messtischblatt (MTB) / TK25 (used in A, CH, D)'
where [LocalisationSystemID] = 3

update [LocalisationSystem]
set [Description] = 'Altitude above seelevel (mNN)'
where [LocalisationSystemID] = 4

update [LocalisationSystem]
set [Description] = 'Coordinates of the World Geodetic System 1984'
where [LocalisationSystemID] = 8

update [LocalisationSystem]
set [Description] = 'Exposition of e.g. a hillside in relation to the sun'
where [LocalisationSystemID] = 10

update [LocalisationSystem]
set [Description] = 'Slope or steepness of the surface of e.g. a hillside'
where [LocalisationSystemID] = 11

update [LocalisationSystem]
set [Description] = 'Geographic coordinates based on the Potsdam-Datum using the  Bessel-Ellipsoid'
where [LocalisationSystemID] = 12
*/


--#####################################################################################################################

/*

-- script for creation of the commands for adding a row GUID to the tables

select 'ALTER TABLE [dbo].[' + T.TABLE_NAME + '] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());'
from INFORMATION_SCHEMA.TABLES T
where T.TABLE_TYPE = 'BASE TABLE'
and T.TABLE_NAME not like 'xx%'
and T.TABLE_NAME not like '%_log'
and T.TABLE_NAME not like '%_log_%'
and T.TABLE_NAME not like 'Application%'
and T.TABLE_NAME not like '%Proxy'
and T.TABLE_NAME not like 'Workbench%'
and T.TABLE_NAME not like 'dtproperties%'
and T.TABLE_NAME not like 'sysdiagrams%'
order by T.TABLE_NAME

*/
/*
ALTER TABLE [dbo].[Analysis] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[AnalysisTaxonomicGroup] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollCircumstances_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollDateCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[Collection] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionAgent] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionEvent] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionEventImage] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionEventLocalisation] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionEventProperty] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionEventSeries] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionEventSeriesImage] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionExternalDatasource] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionManager] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionProject] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionRequester] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionSpecimen] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionSpecimenImage] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionSpecimenPart] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionSpecimenProcessing] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionSpecimenRelation] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollectionSpecimenTransaction] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollEventDateCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollEventImageType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollEventSeriesImageType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollExchangeType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollIdentificationCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollIdentificationDateCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollIdentificationQualifier_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollLabelTranscriptionState_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollLabelType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollMaterialCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollSpecimenImageType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollSpecimenRelationType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollTaxonomicGroup_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollTransactionType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollTypeStatus_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[CollUnitRelationType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[ExternalRequestCredentials ] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[Identification] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[IdentificationUnit] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[IdentificationUnitAnalysis] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[IdentificationUnitInPart] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[LanguageCode_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[LocalisationSystem] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[MeasurementUnit_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[Processing] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[ProcessingMaterialCategory] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[ProjectProxy] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[ProjectUser] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[Property] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[Transaction] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[TransactionDocument] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
ALTER TABLE [dbo].[UserProxy] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid());
*/

--#####################################################################################################################


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

GO

