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

IF (SELECT DBO.VERSION()) <> '02.05.05'
BEGIN
RAISERROR ('WRONG VERION. Script is scheduled as update for version 02.05.05', 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


--#####################################################################################################################
-- missing entries in LocalisationSystem -------------------------------------------------------------------------------------
--#####################################################################################################################


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
GO

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

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

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

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

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




--#####################################################################################################################
-- Entity and related objects -------------------------------------------------------------------------------------
--#####################################################################################################################


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EntityLanguageCode_Enum]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EntityLanguageCode_Enum](
	[Code] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](500) NULL,
	[DisplayText] [nvarchar](50) NULL,
	[DisplayOrder] [smallint] NULL,
	[DisplayEnable] [bit] NULL,
	[ParentCode] [nvarchar](50) NULL,
	[InternalNotes] [nvarchar](500) NULL,
	[RowGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 CONSTRAINT [PK_EntityLanguageCode_Enum] PRIMARY KEY CLUSTERED 
(
	[Code] 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
/****** Object:  Default [DF__EntityLan__RowGU__365DB336]    Script Date: 08/19/2009 15:27:46 ******/
IF Not EXISTS (SELECT * FROM sys.default_constraints WHERE object_id = OBJECT_ID(N'[dbo].[DF__EntityLan__RowGU__365DB336]') AND parent_object_id = OBJECT_ID(N'[dbo].[EntityLanguageCode_Enum]'))
Begin
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF__EntityLan__RowGU__365DB336]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[EntityLanguageCode_Enum] ADD  DEFAULT (newsequentialid()) FOR [RowGUID]
END


End
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the DiversityWorkbench may depend upon it.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityLanguageCode_Enum', @level2type=N'COLUMN',@level2name=N'Code'
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'EntityLanguageCode_Enum', @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'EntityLanguageCode_Enum', @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'EntityLanguageCode_Enum', @level2type=N'COLUMN',@level2name=N'DisplayOrder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Enumerated objects can be hidden from the user interface if this attribute is set to false (= unchecked check box)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityLanguageCode_Enum', @level2type=N'COLUMN',@level2name=N'DisplayEnable'
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'EntityLanguageCode_Enum', @level2type=N'COLUMN',@level2name=N'InternalNotes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The code of the superior entry, if a hierarchy within the entries is necessary' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityLanguageCode_Enum', @level2type=N'COLUMN',@level2name=N'ParentCode'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'EntityLanguageCode_Enum', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The codes for the languages used for the entities' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityLanguageCode_Enum'
GO

if (select COUNT(*) from EntityLanguageCode_Enum E where E.Code = 'de-DE') < 1 begin
INSERT [dbo].[EntityLanguageCode_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [ParentCode], [InternalNotes]) 
VALUES (N'de-DE', NULL, N'Deutsch', NULL, 1, NULL, NULL)
end
GO

if (select COUNT(*) from EntityLanguageCode_Enum E where E.Code = 'en-US') < 1 begin
INSERT [dbo].[EntityLanguageCode_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [ParentCode], [InternalNotes]) 
VALUES (N'en-US', NULL, N'Englisch', NULL, 1, NULL, NULL)
end
GO


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


if (select COUNT(*) from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'EntityContext_Enum') < 1 begin

CREATE TABLE [dbo].[EntityContext_Enum](
	[Code] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](500) NULL,
	[DisplayText] [nvarchar](50) NULL,
	[DisplayOrder] [smallint] NULL,
	[DisplayEnable] [bit] NULL,
	[InternalNotes] [nvarchar](500) NULL,
	[ParentCode] [nvarchar](50) NULL,
 CONSTRAINT [PK_EntityContext_Enum] PRIMARY KEY CLUSTERED 
(
	[Code] 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'A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the DiversityWorkbench may depend upon it.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityContext_Enum', @level2type=N'COLUMN',@level2name=N'Code'
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'EntityContext_Enum', @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'EntityContext_Enum', @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'EntityContext_Enum', @level2type=N'COLUMN',@level2name=N'DisplayOrder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Enumerated objects can be hidden from the user interface if this attribute is set to false (= unchecked check box)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityContext_Enum', @level2type=N'COLUMN',@level2name=N'DisplayEnable'
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'EntityContext_Enum', @level2type=N'COLUMN',@level2name=N'InternalNotes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The code of the superior entry, if a hierarchy within the entries is necessary' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityContext_Enum', @level2type=N'COLUMN',@level2name=N'ParentCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The context in which the application is used, e.g. Collection-administration, field-mapping' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityContext_Enum'
GO

if (select COUNT(*) from EntityContext_Enum E where E.Code = 'CollectionManagement') < 1 begin
INSERT [dbo].[EntityContext_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [InternalNotes], [ParentCode]) VALUES (N'CollectionManagement', N'Management of scientifc collections like herbaria', N'Collection management', NULL, 1, NULL, N'General')
end
GO

if (select COUNT(*) from EntityContext_Enum E where E.Code = 'CollectionManagement.Mobile') < 1 begin
INSERT [dbo].[EntityContext_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [InternalNotes], [ParentCode]) VALUES (N'CollectionManagement.Mobile', N'Management of scientifc collections using a mobile device', N'Collection management - mobile', NULL, 1, NULL, N'CollectionManagement')
end
GO

if (select COUNT(*) from EntityContext_Enum E where E.Code = 'General') < 1 begin
INSERT [dbo].[EntityContext_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [InternalNotes], [ParentCode]) VALUES (N'General', N'General context', N'General', NULL, 1, NULL, NULL)
end
GO

if (select COUNT(*) from EntityContext_Enum E where E.Code = 'Observation') < 1 begin
INSERT [dbo].[EntityContext_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [InternalNotes], [ParentCode]) VALUES (N'Observation', N'Recoding observations', N'Observation', NULL, 1, NULL, N'General')
end
GO

if (select COUNT(*) from EntityContext_Enum E where E.Code = 'Observation.Mobile') < 1 begin
INSERT [dbo].[EntityContext_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [InternalNotes], [ParentCode]) VALUES (N'Observation.Mobile', N'Recoding observations using a mobile device', N'Observation - mobile', NULL, 1, NULL, N'Observation')
end
GO

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


/****** Object:  Table [dbo].[Entity]    Script Date: 07/30/2009 12:23:47 ******/
if (select COUNT(*) from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'Entity') < 1 begin
CREATE TABLE [dbo].[Entity](
	[Entity] [varchar](500) NOT NULL,
	[DisplayGroup] [varchar](500) NULL,
	[Notes] [nvarchar](max) NULL,
	[Obsolete] [bit] NULL,
	[LogCreatedWhen] [datetime] NULL,
	[LogCreatedBy] [nvarchar](50) NULL,
	[LogUpdatedWhen] [datetime] NULL,
	[LogUpdatedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED 
(
	[Entity] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
end

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the entity, e.g. Table.Column.Content within the database or a unique string for e.g. a message within the DiversityWorkbench e.g. "DiversityWorkbench.Message.Connection.NoAccess", PK' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity', @level2type=N'COLUMN',@level2name=N'Entity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If DiversityWorkbench entities should be displayed in a group, the name of the group' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity', @level2type=N'COLUMN',@level2name=N'DisplayGroup'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Notes about the entity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity', @level2type=N'COLUMN',@level2name=N'Notes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'True if an entity is obsolete. Obsolete entities may be kept to ensure compatibility with older modules' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity', @level2type=N'COLUMN',@level2name=N'Obsolete'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The time when this dataset was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity', @level2type=N'COLUMN',@level2name=N'LogCreatedWhen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who created this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity', @level2type=N'COLUMN',@level2name=N'LogCreatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The last time when this dataset was updated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity', @level2type=N'COLUMN',@level2name=N'LogUpdatedWhen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who was the last to update this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity', @level2type=N'COLUMN',@level2name=N'LogUpdatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The entities in an application e.g. the tables and columns in a database' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Entity'
GO

/****** Object:  ForeignKey [FK_Entity_Entity]    Script Date: 09/11/2009 10:17:22 ******/
ALTER TABLE [dbo].[Entity]  WITH CHECK ADD  CONSTRAINT [FK_Entity_Entity] FOREIGN KEY([DisplayGroup])
REFERENCES [dbo].[Entity] ([Entity])
GO
ALTER TABLE [dbo].[Entity] CHECK CONSTRAINT [FK_Entity_Entity]
GO

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

/****** Object:  Table [dbo].[EntityUsage_Enum]    Script Date: 07/30/2009 12:23:47 ******/
if (select COUNT(*) from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'EntityUsage_Enum') < 1 begin
CREATE TABLE [dbo].[EntityUsage_Enum](
	[Code] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](500) NULL,
	[DisplayText] [nvarchar](50) NULL,
	[DisplayOrder] [smallint] NULL,
	[DisplayEnable] [bit] NULL,
	[InternalNotes] [nvarchar](500) NULL,
	[ParentCode] [nvarchar](50) NULL,
 CONSTRAINT [PK_EntityUsage_Enum] PRIMARY KEY CLUSTERED 
(
	[Code] 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'A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the DiversityWorkbench may depend upon it.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage_Enum', @level2type=N'COLUMN',@level2name=N'Code'
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'EntityUsage_Enum', @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'EntityUsage_Enum', @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'EntityUsage_Enum', @level2type=N'COLUMN',@level2name=N'DisplayOrder'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Enumerated objects can be hidden from the user interface if this attribute is set to false (= unchecked check box)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage_Enum', @level2type=N'COLUMN',@level2name=N'DisplayEnable'
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'EntityUsage_Enum', @level2type=N'COLUMN',@level2name=N'InternalNotes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The code of the superior entry, if a hierarchy within the entries is necessary' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage_Enum', @level2type=N'COLUMN',@level2name=N'ParentCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The usage of an entity, e.g. hidden, readonly' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage_Enum'
GO

if (select COUNT(*) from EntityUsage_Enum E where E.Code = 'inapplicable') < 1 begin
INSERT [dbo].[EntityUsage_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [InternalNotes], [ParentCode]) 
VALUES (N'inapplicable', N'This entity is not applicable in the given context', N'inapplicable', 1, 1, NULL, NULL)
end
GO

if (select COUNT(*) from EntityUsage_Enum E where E.Code = 'read only') < 1 begin
INSERT [dbo].[EntityUsage_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [InternalNotes], [ParentCode]) 
VALUES (N'read only', N'The access to this entity is restricted to read only', N'read only', 2, 1, NULL, NULL)
end
GO

if (select COUNT(*) from EntityUsage_Enum E where E.Code = 'hidden') < 1 begin
INSERT [dbo].[EntityUsage_Enum] ([Code], [Description], [DisplayText], [DisplayOrder], [DisplayEnable], [InternalNotes], [ParentCode]) 
VALUES (N'hidden', N'The entity is hidden from a user interface', N'hidden', 3, 1, NULL, NULL)
end
GO

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

/****** Object:  Table [dbo].[EntityUsage]    Script Date: 07/30/2009 12:23:47 ******/
if (select COUNT(*) from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'EntityUsage') < 1 begin
CREATE TABLE [dbo].[EntityUsage](
	[Entity] [varchar](500) NOT NULL,
	[EntityContext] [nvarchar](50) NOT NULL,
	[EntityUsage] [nvarchar](50) NULL,
	[PresetValue] [nvarchar](500) NULL,
	[Notes] [nvarchar](max) NULL,
	[LogCreatedWhen] [datetime] NULL,
	[LogCreatedBy] [nvarchar](50) NULL,
	[LogUpdatedWhen] [datetime] NULL,
	[LogUpdatedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_EntityUsage] PRIMARY KEY CLUSTERED 
(
	[Entity] ASC,
	[EntityContext] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
end
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the entity. Foreign key, relates to table Entity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'Entity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The context for the representation, e.g. "Exchange with ABCD", "collection management" or "observation" as defined in table EntityContext_Enum' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'EntityContext'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'How the entity should be used within a certain context, e.g. "hidden" as defined in table EntityUsage_Enum' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'EntityUsage'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If a value is preset the value resp. SQL statement for the value, e.g. ''determination'' for identifications when using a mobile device during an expedition' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'PresetValue'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Notes about the usage of the entity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'Notes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The time when this dataset was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'LogCreatedWhen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who created this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'LogCreatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The last time when this dataset was updated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'LogUpdatedWhen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who was the last to update this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage', @level2type=N'COLUMN',@level2name=N'LogUpdatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The usage of an entity in a certain context, e.g. hidden, readonly' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityUsage'
GO

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


/****** Object:  Table [dbo].[EntityRepresentation]    Script Date: 07/30/2009 12:23:47 ******/
if (select COUNT(*) from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'EntityRepresentation') < 1 begin
CREATE TABLE [dbo].[EntityRepresentation](
	[Entity] [varchar](500) NOT NULL,
	[LanguageCode] [nvarchar](50) NOT NULL,
	[EntityContext] [nvarchar](50) NOT NULL,
	[DisplayText] [nvarchar](50) NULL,
	[Abbreviation] [nvarchar](20) NULL,
	[Description] [nvarchar](max) NULL,
	[Notes] [nvarchar](max) NULL,
	[LogCreatedWhen] [datetime] NULL,
	[LogCreatedBy] [nvarchar](50) NULL,
	[LogUpdatedWhen] [datetime] NULL,
	[LogUpdatedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_EntityRepresentation] PRIMARY KEY CLUSTERED 
(
	[Entity] ASC,
	[LanguageCode] ASC,
	[EntityContext] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
end
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the entity. Foreign key, relates to table Entity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'Entity'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ISO 639: 2-letter codes for the language of the content' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'LanguageCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The context for the representation, e.g. "Exchange with ABCD", "collection management" or "observation" as defined in table EntityContext_Enum' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'EntityContext'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The text for the entity as shown e.g. in  a user interface' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'DisplayText'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The abbreviation for the entity as shown e.g. in  a user interface' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'Abbreviation'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The description of the entity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'Description'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Notes about the representation of the entity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'Notes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The time when this dataset was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'LogCreatedWhen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who created this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'LogCreatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The last time when this dataset was updated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'LogUpdatedWhen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who was the last to update this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation', @level2type=N'COLUMN',@level2name=N'LogUpdatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The description of the entity in a certain context in different languages' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'EntityRepresentation'
GO



/****** Object:  UserDefinedFunction [dbo].[EntityInformation]    Script Date: 07/30/2009 12:23:48 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EntityInformation]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'


CREATE FUNCTION [dbo].[EntityInformation] (@Entity [varchar] (500), @Language nvarchar(50), @Context nvarchar(50))  
RETURNS @EntityList TABLE ([Entity] [varchar] (500) Primary key ,
	[DisplayGroup] [nvarchar](50) NULL,
	[DisplayText] [nvarchar](50) NULL,
	[Abbreviation] [nvarchar](20) NULL,
	[Description] [nvarchar](max) NULL,
	[EntityUsage] [nvarchar](50) NULL,
	[PresetValue] [nvarchar](500) NULL,
	[DoesExist] [bit] NULL,
	[DisplayTextOK] [bit] NULL,
	[AbbreviationOK] [bit] NULL,
	[DescriptionOK] [bit] NULL)
/*
Returns the information to an entity.
MW 15.07.2009
Test:
select * from [dbo].[EntityInformation] (''CollCircumstances_Enum.Code.bred'', ''de-DE'', ''General'')
select * from [dbo].[EntityInformation] (''CollLabelType_Enum'', ''de-DE'', ''Observation.mobile'')
select * from [dbo].[EntityInformation] (''CollectionSpecimen'', ''de-DE'', ''Observation'')
select * from [dbo].[EntityInformation] (''CollectionSpecimen'', ''de-DE'', '''')
select * from [dbo].[EntityInformation] (''CollectionSpecimen'', ''en-US'', ''Observation'')
select * from [dbo].[EntityInformation] (''CollectionSpecimen'', ''en-US'', '''')
select * from [dbo].[EntityInformation] (''CollectionSpecimen'', '''', '''')
select * from [dbo].[EntityInformation] (''EntityUsage.PresetValue'', '''', '''')
select * from [dbo].[EntityInformation] (''Identification.CollectionSpecimenID'', ''en-US'', ''Observation'')
select * from [dbo].[EntityInformation] (''Identification.CollectionSpecimenID'', ''en-US'', ''General'')
select * from [dbo].[EntityInformation] (''CollectionSpecimen.CollectionSpecimenID'', ''en-US'', ''General'')
select * from [dbo].[EntityInformation] (''Identification.IdentificationDate'', ''de-DE'', ''Observation'')
select * from [dbo].[EntityInformation] (''IdentificationUnit.TaxonomicGroup'', ''de-DE'', ''Observation.Mobile'')
select * from [dbo].[EntityInformation] (''IdentificationUnit.TaxonomicGroup'', ''en-US'', ''Observation.Mobile'')
select * from [dbo].[EntityInformation] (''IdentificationUnit.TaxonomicGroup'', ''en-US'', ''Observation.Mobile'')
select * from [dbo].[EntityInformation] (''Test'', ''en-US'', ''Observation.Mobile'')
select * from [dbo].[EntityInformation] (''CollMaterialCategory_Enum.Code.drawing or photograph'', ''de-DE'', ''General'')
*/

AS
BEGIN

if @Context = '''' begin set @Context = ''General'' end

-- fill the list with the basic data from the table Entity

insert @EntityList (Entity, [DisplayGroup]
	, [DoesExist], [DisplayTextOK], [AbbreviationOK], [DescriptionOK]) 
SELECT TOP 1 [Entity]
      ,[DisplayGroup]
      ,1, 0, 0, 0
  FROM [Entity]
WHERE Entity = @Entity


-- if nothing is found, fill in the values according to the parameters of the function

if (select count(*) from @EntityList) = 0
begin
	insert @EntityList (Entity, DisplayText, Abbreviation, DoesExist, [DisplayTextOK], [AbbreviationOK], [DescriptionOK]) 
	Values (@Entity, 
	case when @Entity like ''%.%'' then rtrim(substring(@Entity, charindex(''.'', @Entity)+1, 50)) else @Entity end, 
	substring(case when @Entity like ''%.%'' then rtrim(substring(@Entity, charindex(''.'', @Entity)+1, 50)) else @Entity end, 1, 20)
	, 0, 0, 0, 0)

	declare @Table nvarchar(50)
	declare @Column nvarchar(50)
	if (@Entity not like ''%.%'')
	begin
	set @Table = @Entity
	update E set E.[Description] = (SELECT max(CONVERT(nvarchar(MAX), [value])) 
	FROM ::fn_listextendedproperty(NULL, ''user'', ''dbo'', ''table'', @Table, default, NULL) WHERE name =  ''MS_Description'')
	, E.[DescriptionOK] = 1
	from @EntityList E 
	end
	
	if (@Entity like ''%.%'' and @Entity not like ''%.%.%'')
	begin
	set @Table = (select rtrim(substring(@Entity, 1, charindex(''.'', @Entity)-1)))
	set @Column = (select rtrim(substring(@Entity, charindex(''.'', @Entity)+1, 50)))
	update E set E.[Description] = (SELECT max(CONVERT(nvarchar(MAX), [value])) 
	FROM ::fn_listextendedproperty(NULL, ''user'', ''dbo'', ''table'', @Table, ''column'', @Column) WHERE name =  ''MS_Description'')
	, E.[DescriptionOK] = 1
	from @EntityList E 
	end
	
	if (select count(*) from @EntityList where len([Description]) > 0) = 0
	begin
	update E set E.[Description] = case when @Entity like ''%.%'' then rtrim(substring(@Entity, charindex(''.'', @Entity)+1, 50)) 
	+ case when @Entity like ''%.%'' then '' - '' + rtrim(substring(@Entity, 1, charindex(''.'', @Entity)-1)) else '''' end
	else @Entity end
	from @EntityList E 
	end
	
	if (@Entity like ''%_Enum.Code.%'')
	begin
	declare @EnumTable nvarchar(50)
	set @EnumTable = (select substring(@Entity, 1, charindex(''.'', @Entity) - 1))
	declare @Value nvarchar(50)
	set @Value = (select rtrim(substring(@Entity, charindex(''.'', @Entity) + 6, 255)))
	update E set E.[Description] = @Value, E.DisplayText = substring(@Value, 1, 50), E.Abbreviation = substring(@Value, 1, 20)
	from @EntityList E 
	end
	return
end


-- set the details for usage and representation of the entry

declare @ParentContext nvarchar(50)
declare @CurrentContext nvarchar(50)


-- set the Usage if there is one

update E set E.[EntityUsage] = U.EntityUsage, E.PresetValue = U.PresetValue
from dbo.EntityUsage U, @EntityList E
where U.Entity = @Entity
and U.EntityContext = @Context


-- search for usage information in parent datasets

set @CurrentContext = @Context
set @ParentContext = @Context
while not @ParentContext is null
begin
	update E set 
	E.EntityUsage = case when E.EntityUsage is null then U.EntityUsage else E.EntityUsage end,
	E.PresetValue = case when E.PresetValue is null then U.PresetValue else E.PresetValue end
	from dbo.EntityUsage U, @EntityList E
	where U.Entity = @Entity
	and U.EntityContext = @ParentContext
	
	set @CurrentContext = @ParentContext
	set @ParentContext = (select ParentCode from dbo.EntityContext_Enum where Code = @CurrentContext)
	
	-- avoid loops on itself
	if (@ParentContext = @CurrentContext) begin set @ParentContext = null end
end


-- set the representation values

update E set E.[DisplayText] = R.DisplayText, 
E.Abbreviation = R.Abbreviation, 
E.[Description] = R.[Description],
E.[DisplayTextOK] = case when R.DisplayText is null or R.DisplayText = '''' then 0 else 1 end, 
E.[AbbreviationOK] = case when R.Abbreviation is null or R.Abbreviation = '''' then 0 else 1 end, 
E.[DescriptionOK] = case when R.[Description] is null or R.[Description] = '''' then 0 else 1 end
from dbo.EntityRepresentation R, @EntityList E
where R.Entity = @Entity
and R.EntityContext = @Context
and R.LanguageCode = @Language


-- search for representation values in parent datasets in the same language if nothing is found

set @ParentContext = (select ParentCode from dbo.EntityContext_Enum where Code = @Context)
while not @ParentContext is null
begin
	update E set 
	E.[DisplayText] = case when E.DisplayText is null then R.DisplayText else E.DisplayText end, 
	E.Abbreviation = case when E.Abbreviation is null then R.Abbreviation else E.Abbreviation end, 
	E.[Description] = case when E.[Description] is null then R.[Description] else E.[Description] end,
	E.[DisplayTextOK] = case when E.[DisplayTextOK] = 0 and R.[DisplayText] <> '''' then 1 else E.[DisplayTextOK] end, 
	E.[AbbreviationOK] = case when E.[AbbreviationOK] = 0 and R.[Abbreviation] <> '''' then 1 else E.[AbbreviationOK] end, 
	E.[DescriptionOK] = case when E.[DescriptionOK] = 0 and R.[Description] <> '''' then 1 else E.[DescriptionOK] end
	from dbo.EntityRepresentation R, @EntityList E
	where R.Entity = @Entity
	and R.EntityContext = @ParentContext
	and R.LanguageCode = @Language
	set @CurrentContext = @ParentContext
	set @ParentContext = (select ParentCode from dbo.EntityContext_Enum where Code = @CurrentContext)
	
	-- avoid loops on itself
	if (@ParentContext = @CurrentContext) begin set @ParentContext = null end
end

--if (select count(*) from @EntityList where len([Description]) > 0) > 0 return


-- search for representation values in parent datasets in the default language if nothing is found

set @ParentContext = @Context
while not @ParentContext is null
begin
	update E set 
	E.[DisplayText] = case when E.DisplayText is null then R.DisplayText else E.DisplayText end, 
	E.Abbreviation = case when E.Abbreviation is null then R.Abbreviation else E.Abbreviation end, 
	E.[Description] = case when E.[Description] is null then R.[Description] else E.[Description] end,
	E.[DisplayTextOK] = case when E.[DisplayTextOK] = 0 and R.[DisplayText] <> '''' then 1 else E.[DisplayTextOK] end, 
	E.[AbbreviationOK] = case when E.[AbbreviationOK] = 0 and R.[Abbreviation] <> '''' then 1 else E.[AbbreviationOK] end, 
	E.[DescriptionOK] = case when E.[DescriptionOK] = 0 and R.[Description] <> '''' then 1 else E.[DescriptionOK] end
	from dbo.EntityRepresentation R, @EntityList E
	where R.Entity = @Entity
	and R.EntityContext = @ParentContext
	and R.LanguageCode = ''en-US''
	
	set @CurrentContext = @ParentContext
	set @ParentContext = (select ParentCode from dbo.EntityContext_Enum where Code = @CurrentContext)
	
	-- avoid loops on itself
	if (@ParentContext = @CurrentContext) begin set @ParentContext = null end
end

update E set E.[DisplayText] = substring(case when @Entity like ''%.%'' then rtrim(substring(@Entity, charindex(''.'', @Entity)+1, 50)) else @Entity end, 1, 20)
from @EntityList E 
where E.DisplayText is null

update E set E.Abbreviation = substring(case when @Entity like ''%.%'' then rtrim(substring(@Entity, charindex(''.'', @Entity)+1, 50)) else @Entity end, 1, 20)
from @EntityList E 
where E.Abbreviation is null


   RETURN
END

' 
END
GO


/****** Object:  ForeignKey [FK_EntityRepresentation_Entity]    Script Date: 08/20/2009 10:48:32 ******/
ALTER TABLE [dbo].[EntityRepresentation]  WITH CHECK ADD  CONSTRAINT [FK_EntityRepresentation_Entity] FOREIGN KEY([Entity])
REFERENCES [dbo].[Entity] ([Entity])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[EntityRepresentation] CHECK CONSTRAINT [FK_EntityRepresentation_Entity]
GO
/****** Object:  ForeignKey [FK_EntityRepresentation_EntityContext_Enum]    Script Date: 08/20/2009 10:48:32 ******/
ALTER TABLE [dbo].[EntityRepresentation]  WITH CHECK ADD  CONSTRAINT [FK_EntityRepresentation_EntityContext_Enum] FOREIGN KEY([EntityContext])
REFERENCES [dbo].[EntityContext_Enum] ([Code])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[EntityRepresentation] CHECK CONSTRAINT [FK_EntityRepresentation_EntityContext_Enum]
GO
/****** Object:  ForeignKey [FK_EntityRepresentation_EntityLanguageCode_Enum]    Script Date: 08/20/2009 10:48:32 ******/
ALTER TABLE [dbo].[EntityRepresentation]  WITH CHECK ADD  CONSTRAINT [FK_EntityRepresentation_EntityLanguageCode_Enum] FOREIGN KEY([LanguageCode])
REFERENCES [dbo].[EntityLanguageCode_Enum] ([Code])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[EntityRepresentation] CHECK CONSTRAINT [FK_EntityRepresentation_EntityLanguageCode_Enum]
GO
/****** Object:  ForeignKey [FK_EntityUsage_Entity]    Script Date: 08/20/2009 10:48:32 ******/
ALTER TABLE [dbo].[EntityUsage]  WITH CHECK ADD  CONSTRAINT [FK_EntityUsage_Entity] FOREIGN KEY([Entity])
REFERENCES [dbo].[Entity] ([Entity])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[EntityUsage] CHECK CONSTRAINT [FK_EntityUsage_Entity]
GO
/****** Object:  ForeignKey [FK_EntityUsage_EntityContext_Enum]    Script Date: 08/20/2009 10:48:32 ******/
ALTER TABLE [dbo].[EntityUsage]  WITH CHECK ADD  CONSTRAINT [FK_EntityUsage_EntityContext_Enum] FOREIGN KEY([EntityContext])
REFERENCES [dbo].[EntityContext_Enum] ([Code])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[EntityUsage] CHECK CONSTRAINT [FK_EntityUsage_EntityContext_Enum]
GO
/****** Object:  ForeignKey [FK_EntityUsage_EntityUsage_Enum]    Script Date: 08/20/2009 10:48:32 ******/
ALTER TABLE [dbo].[EntityUsage]  WITH CHECK ADD  CONSTRAINT [FK_EntityUsage_EntityUsage_Enum] FOREIGN KEY([EntityUsage])
REFERENCES [dbo].[EntityUsage_Enum] ([Code])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[EntityUsage] CHECK CONSTRAINT [FK_EntityUsage_EntityUsage_Enum]
GO


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

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

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

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

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


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

GRANT SELECT ON [dbo].[EntityInformation] TO [DiversityCollectionUser] AS [dbo]
GO



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

/****** Object:  Trigger [trgUpdCollectionSpecimen]    Script Date: 07/31/2009 10:36:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
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, 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.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

--#####################################################################################################################
--##### First Lines - integration of Analysis ############################################################################################
--#####################################################################################################################

IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesIdentificationUnitAnalysis]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[FirstLinesIdentificationUnitAnalysis]
AS
SELECT     A.CollectionSpecimenID, A.IdentificationUnitID, A.AnalysisID, A.AnalysisNumber, A.AnalysisResult, A.ExternalAnalysisURI, A.ResponsibleName, 
                      A.ResponsibleAgentURI, A.AnalysisDate, A.SpecimenPartID, A.Notes, A.LogCreatedWhen, A.LogCreatedBy, A.LogUpdatedWhen, A.LogUpdatedBy, 
                      dbo.Analysis.DisplayText
FROM         dbo.IdentificationUnitAnalysis AS A INNER JOIN
                      dbo.FirstLinesIdentificationUnit ON A.CollectionSpecimenID = dbo.FirstLinesIdentificationUnit.CollectionSpecimenID AND 
                      A.IdentificationUnitID = dbo.FirstLinesIdentificationUnit.IdentificationUnitID INNER JOIN
                      dbo.Analysis ON A.AnalysisID = dbo.Analysis.AnalysisID
WHERE     EXISTS
                          (SELECT     CollectionSpecimenID
                            FROM          dbo.IdentificationUnitAnalysis AS I
                            GROUP BY CollectionSpecimenID, IdentificationUnitID
                            HAVING      (CollectionSpecimenID = A.CollectionSpecimenID) AND (IdentificationUnitID = A.IdentificationUnitID) AND (MIN(AnalysisID) = A.AnalysisID) 
                                                   AND (MIN(AnalysisNumber) = A.AnalysisNumber))
'
GO



/*#####################################################################################################################*/


ALTER VIEW [dbo].[FirstLinesCollectionSpecimen]
AS
SELECT     dbo.CollectionSpecimen.CollectionSpecimenID, dbo.CollectionSpecimen.AccessionNumber AS Accession_number, 
                      dbo.CollectionSpecimen.DataWithholdingReason AS Data_withholding_reason, 
                      dbo.CollectionEvent.DataWithholdingReason AS Data_withholding_reason_for_collection_event, 
                      dbo.FirstLinesCollectionAgent.DataWithholdingReason AS Data_withholding_reason_for_collector, 
                      dbo.CollectionEvent.CollectorsEventNumber AS Collectors_event_number, dbo.CollectionEvent.CollectionDay AS Collection_day, 
                      dbo.CollectionEvent.CollectionMonth AS Collection_month, dbo.CollectionEvent.CollectionYear AS Collection_year, 
                      dbo.CollectionEvent.CollectionDateSupplement AS Collection_date_supplement, dbo.CollectionEvent.CollectionTime AS Collection_time, 
                      dbo.CollectionEvent.CollectionTimeSpan AS Collection_time_span, dbo.CollectionEvent.CountryCache AS Country, 
                      dbo.CollectionEvent.LocalityDescription AS Locality_description, dbo.CollectionEvent.HabitatDescription AS Habitat_description, 
                      dbo.CollectionEvent.CollectingMethod AS Collecting_method, dbo.CollectionEvent.Notes AS Collection_event_notes, 
                      dbo.FirstLinesNamedArea.Location1 AS Named_area, dbo.FirstLinesNamedArea.Location2 AS NamedAreaLocation2, NULL 
                      AS Remove_link_to_gazetteer, dbo.FirstLinesNamedArea.DistanceToLocation AS Distance_to_location, 
                      dbo.FirstLinesNamedArea.DirectionToLocation AS Direction_to_location, dbo.FirstLinesCoordinatesWGS84.Location1 AS Longitude, 
                      dbo.FirstLinesCoordinatesWGS84.Location2 AS Latitude, dbo.FirstLinesCoordinatesWGS84.LocationAccuracy AS Coordinates_accuracy, NULL 
                      AS Link_to_GoogleMaps, dbo.FirstLinesAltitude.Location1 AS Altitude_from, dbo.FirstLinesAltitude.Location2 AS Altitude_to, 
                      dbo.FirstLinesAltitude.LocationAccuracy AS Altitude_accuracy, dbo.FirstLinesMTB.Location1 AS MTB, dbo.FirstLinesMTB.Location2 AS Quadrant, 
                      dbo.FirstLinesMTB.LocationNotes AS Notes_for_MTB, dbo.FirstLinesSamplingPlot.Location1 AS Sampling_plot, 
                      dbo.FirstLinesSamplingPlot.Location2 AS Link_to_SamplingPlots, NULL AS Remove_link_to_SamplingPlots, 
                      dbo.FirstLinesSamplingPlot.LocationAccuracy AS Accuracy_of_sampling_plot, 
                      dbo.FirstLinesSamplingPlot.AverageLatitudeCache AS Latitude_of_sampling_plot, 
                      dbo.FirstLinesSamplingPlot.AverageLongitudeCache AS Longitude_of_sampling_plot, 
                      dbo.FirstLinesGeographicRegion.DisplayText AS Geographic_region, dbo.FirstLinesLithostratigraphy.DisplayText AS Lithostratigraphy, 
                      dbo.FirstLinesChronostratigraphy.DisplayText AS Chronostratigraphy, dbo.FirstLinesCollectionAgent.CollectorsName AS Collectors_name, 
                      dbo.FirstLinesCollectionAgent.CollectorsAgentURI AS Link_to_DiversityAgents, NULL AS Remove_link_for_collector, 
                      dbo.FirstLinesCollectionAgent.CollectorsNumber AS Collectors_number, dbo.FirstLinesCollectionAgent.Notes AS Notes_about_collector, 
                      dbo.CollectionSpecimen.AccessionDay AS Accession_day, dbo.CollectionSpecimen.AccessionMonth AS Accession_month, 
                      dbo.CollectionSpecimen.AccessionYear AS Accession_year, dbo.CollectionSpecimen.AccessionDateSupplement AS Accession_date_supplement, 
                      dbo.CollectionSpecimen.DepositorsName AS Depositors_name, 
                      dbo.CollectionSpecimen.DepositorsAgentURI AS Depositors_link_to_DiversityAgents, NULL AS Remove_link_for_Depositor, 
                      dbo.CollectionSpecimen.DepositorsAccessionNumber AS Depositors_accession_number, 
                      dbo.CollectionSpecimen.ExsiccataAbbreviation AS Exsiccata_abbreviation, 
                      dbo.CollectionSpecimen.ExsiccataURI AS Link_to_DiversityExsiccatae, NULL AS Remove_link_to_exsiccatae, 
                      dbo.FirstLinesIdentificationUnit.ExsiccataNumber AS Exsiccata_number, dbo.CollectionSpecimen.OriginalNotes AS Original_notes, 
                      dbo.CollectionSpecimen.AdditionalNotes AS Additional_notes, dbo.CollectionSpecimen.InternalNotes AS Internal_notes, 
                      dbo.CollectionSpecimen.LabelTitle AS Label_title, dbo.CollectionSpecimen.LabelType AS Label_type, 
                      dbo.CollectionSpecimen.LabelTranscriptionState AS Label_transcription_state, 
                      dbo.CollectionSpecimen.LabelTranscriptionNotes AS Label_transcription_notes, dbo.CollectionSpecimen.Problems, 
                      dbo.FirstLinesIdentificationUnit.TaxonomicGroup AS Taxonomic_group, dbo.FirstLinesIdentificationUnit.RelationType AS Relation_type, 
                      dbo.FirstLinesIdentificationUnit.ColonisedSubstratePart AS Colonised_substrate_part, dbo.FirstLinesIdentificationUnit.LifeStage AS Life_stage, 
                      dbo.FirstLinesIdentificationUnit.Gender, dbo.FirstLinesIdentificationUnit.NumberOfUnits AS Number_of_units, 
                      dbo.FirstLinesIdentificationUnit.Circumstances, dbo.FirstLinesIdentificationUnit.OrderCache AS Order_of_taxon, 
                      dbo.FirstLinesIdentificationUnit.FamilyCache AS Family_of_taxon, dbo.FirstLinesIdentificationUnit.UnitIdentifier AS Identifier_of_organism, 
                      dbo.FirstLinesIdentificationUnit.UnitDescription AS Description_of_organism, dbo.FirstLinesIdentificationUnit.OnlyObserved AS Only_observed, 
                      dbo.FirstLinesIdentificationUnit.Notes AS Notes_for_organism, dbo.FirstLinesIdentification.TaxonomicName AS Taxonomic_name, 
                      dbo.FirstLinesIdentification.NameURI AS Link_to_DiversityTaxonNames, NULL AS Remove_link_for_identification, 
                      dbo.FirstLinesIdentification.VernacularTerm AS Vernacular_term, dbo.FirstLinesIdentification.IdentificationDay AS Identification_day, 
                      dbo.FirstLinesIdentification.IdentificationMonth AS Identification_month, dbo.FirstLinesIdentification.IdentificationYear AS Identification_year, 
                      dbo.FirstLinesIdentification.IdentificationCategory AS Identification_category, 
                      dbo.FirstLinesIdentification.IdentificationQualifier AS Identification_qualifier, dbo.FirstLinesIdentification.TypeStatus AS Type_status, 
                      dbo.FirstLinesIdentification.TypeNotes AS Type_notes, dbo.FirstLinesIdentification.Notes AS Notes_for_identification, 
                      dbo.FirstLinesIdentification.ReferenceTitle AS Reference_title, dbo.FirstLinesIdentification.ReferenceURI AS Link_to_DiversityReferences, NULL 
                      AS Remove_link_for_reference, dbo.FirstLinesIdentification.ResponsibleName AS Responsible, 
                      dbo.FirstLinesIdentification.ResponsibleAgentURI AS Link_to_DiversityAgents_for_responsible, NULL AS Remove_link_for_determiner, 
                      dbo.FirstLinesIdentificationUnitAnalysis.DisplayText AS Analysis, 
                      dbo.FirstLinesIdentificationUnitAnalysis.AnalysisID, 
                      dbo.FirstLinesIdentificationUnitAnalysis.AnalysisNumber AS Analysis_number, 
                      dbo.FirstLinesIdentificationUnitAnalysis.AnalysisResult AS Analysis_result, 
                      dbo.FirstLinesSecondUnit.TaxonomicGroup AS Taxonomic_group_of_second_organism, 
                      dbo.FirstLinesSecondUnitIdentification.TaxonomicName AS Taxonomic_name_of_second_organism, 
                      dbo.FirstLinesSecondUnitIdentification.NameURI AS Link_to_DiversityTaxonNames_of_second_organism, NULL 
                      AS Remove_link_for_second_organism, dbo.FirstLinesSecondUnitIdentification.VernacularTerm AS Vernacular_term_of_second_organism, 
                      dbo.FirstLinesCollectionSpecimenPart.CollectionID AS Collection, dbo.FirstLinesCollectionSpecimenPart.MaterialCategory AS Material_category, 
                      dbo.FirstLinesCollectionSpecimenPart.StorageLocation AS Storage_location, dbo.FirstLinesCollectionSpecimenPart.Stock, 
                      dbo.FirstLinesCollectionSpecimenPart.PreparationMethod AS Preparation_method, 
                      dbo.FirstLinesCollectionSpecimenPart.PreparationDate AS Preparation_date, dbo.FirstLinesCollectionSpecimenPart.Notes AS Notes_for_part, 
                      dbo.FirstLinesCollectionSpecimenTransaction._TransactionID, dbo.FirstLinesCollectionSpecimenTransaction._Transaction, 
                      dbo.FirstLinesCollectionSpecimenOnLoan._TransactionID AS On_loan, dbo.CollectionEvent.CollectionEventID AS _CollectionEventID, 
                      dbo.FirstLinesIdentificationUnit.IdentificationUnitID AS _IdentificationUnitID, 
                      dbo.FirstLinesIdentification.IdentificationSequence AS _IdentificationSequence, dbo.FirstLinesSecondUnit.IdentificationUnitID AS _SecondUnitID, 
                      dbo.FirstLinesSecondUnitIdentification.IdentificationSequence AS _SecondSequence, 
                      dbo.FirstLinesCollectionSpecimenPart.SpecimenPartID AS _SpecimenPartID, 
                      dbo.FirstLinesCoordinatesWGS84.AverageLatitudeCache AS _CoordinatesAverageLatitudeCache, 
                      dbo.FirstLinesCoordinatesWGS84.AverageLongitudeCache AS _CoordinatesAverageLongitudeCache, 
                      dbo.FirstLinesCoordinatesWGS84.LocationNotes AS _CoordinatesLocationNotes, 
                      dbo.FirstLinesGeographicRegion.PropertyURI AS _GeographicRegionPropertyURI, 
                      dbo.FirstLinesLithostratigraphy.PropertyURI AS _LithostratigraphyPropertyURI, 
                      dbo.FirstLinesChronostratigraphy.PropertyURI AS _ChronostratigraphyPropertyURI, 
                      dbo.FirstLinesNamedArea.AverageLatitudeCache AS _NamedAverageLatitudeCache, 
                      dbo.FirstLinesNamedArea.AverageLongitudeCache AS _NamedAverageLongitudeCache, 
                      dbo.FirstLinesLithostratigraphy.PropertyHierarchyCache AS _LithostratigraphyPropertyHierarchyCache, 
                      dbo.FirstLinesChronostratigraphy.PropertyHierarchyCache AS _ChronostratigraphyPropertyHierarchyCache, 
                      dbo.FirstLinesSecondUnit.FamilyCache AS _SecondUnitFamilyCache, dbo.FirstLinesSecondUnit.OrderCache AS _SecondUnitOrderCache, 
                      dbo.FirstLinesAltitude.AverageAltitudeCache AS _AverageAltitudeCache
FROM         dbo.FirstLinesCollectionAgent RIGHT OUTER JOIN
                      dbo.FirstLinesIdentification RIGHT OUTER JOIN
                      dbo.FirstLinesIdentificationUnit LEFT OUTER JOIN
                      dbo.FirstLinesIdentificationUnitAnalysis ON 
                      dbo.FirstLinesIdentificationUnit.CollectionSpecimenID = dbo.FirstLinesIdentificationUnitAnalysis.CollectionSpecimenID AND 
                      dbo.FirstLinesIdentificationUnit.IdentificationUnitID = dbo.FirstLinesIdentificationUnitAnalysis.IdentificationUnitID ON 
                      dbo.FirstLinesIdentification.CollectionSpecimenID = dbo.FirstLinesIdentificationUnit.CollectionSpecimenID AND 
                      dbo.FirstLinesIdentification.IdentificationUnitID = dbo.FirstLinesIdentificationUnit.IdentificationUnitID RIGHT OUTER JOIN
                      dbo.FirstLinesCollectionSpecimenTransaction RIGHT OUTER JOIN
                      dbo.FirstLinesCollectionSpecimenOnLoan RIGHT OUTER JOIN
                      dbo.CollectionSpecimenID_UserAvailable INNER JOIN
                      dbo.CollectionSpecimen ON dbo.CollectionSpecimenID_UserAvailable.CollectionSpecimenID = dbo.CollectionSpecimen.CollectionSpecimenID ON 
                      dbo.FirstLinesCollectionSpecimenOnLoan.CollectionSpecimenID = dbo.CollectionSpecimen.CollectionSpecimenID ON 
                      dbo.FirstLinesCollectionSpecimenTransaction.CollectionSpecimenID = dbo.CollectionSpecimen.CollectionSpecimenID ON 
                      dbo.FirstLinesIdentificationUnit.CollectionSpecimenID = dbo.CollectionSpecimen.CollectionSpecimenID LEFT OUTER JOIN
                      dbo.FirstLinesSecondUnit LEFT OUTER JOIN
                      dbo.FirstLinesSecondUnitIdentification ON 
                      dbo.FirstLinesSecondUnit.CollectionSpecimenID = dbo.FirstLinesSecondUnitIdentification.CollectionSpecimenID AND 
                      dbo.FirstLinesSecondUnit.IdentificationUnitID = dbo.FirstLinesSecondUnitIdentification.IdentificationUnitID ON 
                      dbo.CollectionSpecimen.CollectionSpecimenID = dbo.FirstLinesSecondUnit.CollectionSpecimenID ON 
                      dbo.FirstLinesCollectionAgent.CollectionSpecimenID = dbo.CollectionSpecimen.CollectionSpecimenID LEFT OUTER JOIN
                      dbo.FirstLinesNamedArea RIGHT OUTER JOIN
                      dbo.FirstLinesGeographicRegion RIGHT OUTER JOIN
                      dbo.FirstLinesLithostratigraphy RIGHT OUTER JOIN
                      dbo.FirstLinesSamplingPlot RIGHT OUTER JOIN
                      dbo.CollectionEvent ON dbo.FirstLinesSamplingPlot.CollectionEventID = dbo.CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesChronostratigraphy ON dbo.CollectionEvent.CollectionEventID = dbo.FirstLinesChronostratigraphy.CollectionEventID ON 
                      dbo.FirstLinesLithostratigraphy.CollectionEventID = dbo.CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesMTB ON dbo.CollectionEvent.CollectionEventID = dbo.FirstLinesMTB.CollectionEventID ON 
                      dbo.FirstLinesGeographicRegion.CollectionEventID = dbo.CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesAltitude ON dbo.CollectionEvent.CollectionEventID = dbo.FirstLinesAltitude.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesCoordinatesWGS84 ON dbo.CollectionEvent.CollectionEventID = dbo.FirstLinesCoordinatesWGS84.CollectionEventID ON 
                      dbo.FirstLinesNamedArea.CollectionEventID = dbo.CollectionEvent.CollectionEventID ON 
                      dbo.CollectionSpecimen.CollectionEventID = dbo.CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesCollectionSpecimenPart ON dbo.CollectionSpecimen.CollectionSpecimenID = dbo.FirstLinesCollectionSpecimenPart.CollectionSpecimenID

GO

--#####################################################################################################################
--######  TaxonomicGroups  ##########################################################################################
--#####################################################################################################################

if (SELECT COUNT(*) FROM [dbo].[CollTaxonomicGroup_Enum] WHERE Code = 'cnidaria') = 0
begin
INSERT INTO [dbo].[CollTaxonomicGroup_Enum]
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable])
     VALUES
           ('evertebrate'
           ,'evertebrate'
           ,'evertebrate'
           ,2010
           ,1)
end
GO


if (SELECT COUNT(*) FROM [dbo].[CollTaxonomicGroup_Enum] WHERE Code = 'cnidaria') = 0
begin
INSERT INTO [dbo].[CollTaxonomicGroup_Enum]
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable])
     VALUES
           ('cnidaria'
           ,'cnidaria'
           ,'cnidaria'
           ,2010
           ,1)
end
GO



UPDATE    CollTaxonomicGroup_Enum
SET              ParentCode = 'evertebrate'
WHERE     (Code = 'echinoderm')
GO

UPDATE    CollTaxonomicGroup_Enum
SET              ParentCode = 'evertebrate'
WHERE     (Code = 'arthropode')
GO

UPDATE    CollTaxonomicGroup_Enum
SET              ParentCode = 'arthropode'
WHERE     (Code = 'insect')
GO

UPDATE    CollTaxonomicGroup_Enum
SET              ParentCode = 'evertebrate'
WHERE     (Code = 'mollusc')
GO

UPDATE    CollTaxonomicGroup_Enum
SET              ParentCode = 'vertebrate'
WHERE     (Code = 'bird')
GO

UPDATE    CollTaxonomicGroup_Enum
SET              ParentCode = 'vertebrate'
WHERE     (Code = 'fish')
GO

UPDATE    CollTaxonomicGroup_Enum
SET              ParentCode = 'vertebrate'
WHERE     (Code = 'mammal')
GO

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

if (SELECT COUNT(*) FROM [dbo].[CollMaterialCategory_Enum] WHERE Code = 'dried specimen') = 0
begin
INSERT INTO [dbo].[CollMaterialCategory_Enum]
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable]
           ,[ParentCode])
     VALUES
           ('dried specimen'
           ,'dried specimen'
           ,'dried specimen'
           ,25
           ,1
           ,'preserved specimen')
end
GO

UPDATE [dbo].[CollMaterialCategory_Enum]
   SET [ParentCode] = 'dried specimen'
 WHERE [Code] = 'herbarium sheets'


GO




--#####################################################################################################################
--#####  Project dependent analysis  ###########################################################################################
--#####################################################################################################################

GRANT INSERT ON [dbo].[AnalysisTaxonomicGroup] TO [DiversityCollectionEditor] AS [dbo]
GO


if (select COUNT(*) from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'ProjectAnalysis') < 1 begin

CREATE TABLE [dbo].[ProjectAnalysis](
	[AnalysisID] [int] NOT NULL,
	[ProjectID] [int] NOT NULL,
	[LogCreatedWhen] [datetime] NULL,
	[LogCreatedBy] [nvarchar](50) NULL,
	[LogUpdatedWhen] [datetime] NULL,
	[LogUpdatedBy] [nvarchar](50) NULL,
	[RowGUID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_ProjectAnalysis] PRIMARY KEY CLUSTERED 
(
	[AnalysisID] ASC,
	[ProjectID] 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'ProjectAnalysis', @level2type=N'COLUMN',@level2name=N'AnalysisID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectAnalysis', @level2type=N'COLUMN',@level2name=N'ProjectID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The time when this dataset was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectAnalysis', @level2type=N'COLUMN',@level2name=N'LogCreatedWhen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who created this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectAnalysis', @level2type=N'COLUMN',@level2name=N'LogCreatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The last time when this dataset was updated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectAnalysis', @level2type=N'COLUMN',@level2name=N'LogUpdatedWhen'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who was the last to update this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectAnalysis', @level2type=N'COLUMN',@level2name=N'LogUpdatedBy'
GO
/****** Object:  Default [DF_ProjectAnalysis_LogCreatedWhen]    Script Date: 08/07/2009 16:20:03 ******/
ALTER TABLE [dbo].[ProjectAnalysis] ADD  CONSTRAINT [DF_ProjectAnalysis_LogCreatedWhen]  DEFAULT (getdate()) FOR [LogCreatedWhen]
GO
/****** Object:  Default [DF_ProjectAnalysis_LogCreatedBy]    Script Date: 08/07/2009 16:20:03 ******/
ALTER TABLE [dbo].[ProjectAnalysis] ADD  CONSTRAINT [DF_ProjectAnalysis_LogCreatedBy]  DEFAULT (user_name()) FOR [LogCreatedBy]
GO
/****** Object:  Default [DF_ProjectAnalysis_LogUpdatedWhen]    Script Date: 08/07/2009 16:20:03 ******/
ALTER TABLE [dbo].[ProjectAnalysis] ADD  CONSTRAINT [DF_ProjectAnalysis_LogUpdatedWhen]  DEFAULT (getdate()) FOR [LogUpdatedWhen]
GO
/****** Object:  Default [DF_ProjectAnalysis_LogUpdatedBy]    Script Date: 08/07/2009 16:20:03 ******/
ALTER TABLE [dbo].[ProjectAnalysis] ADD  CONSTRAINT [DF_ProjectAnalysis_LogUpdatedBy]  DEFAULT (user_name()) FOR [LogUpdatedBy]
GO
/****** Object:  Default [DF_ProjectAnalysis_RowGUID]    Script Date: 08/07/2009 16:20:03 ******/
ALTER TABLE [dbo].[ProjectAnalysis] ADD  CONSTRAINT [DF_ProjectAnalysis_RowGUID]  DEFAULT (newsequentialid()) FOR [RowGUID]
GO
/****** Object:  ForeignKey [FK_ProjectAnalysis_Analysis]    Script Date: 08/07/2009 16:20:03 ******/
ALTER TABLE [dbo].[ProjectAnalysis]  WITH CHECK ADD  CONSTRAINT [FK_ProjectAnalysis_Analysis] FOREIGN KEY([AnalysisID])
REFERENCES [dbo].[Analysis] ([AnalysisID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ProjectAnalysis] CHECK CONSTRAINT [FK_ProjectAnalysis_Analysis]
GO
/****** Object:  ForeignKey [FK_ProjectAnalysis_ProjectProxy]    Script Date: 08/07/2009 16:20:03 ******/
ALTER TABLE [dbo].[ProjectAnalysis]  WITH CHECK ADD  CONSTRAINT [FK_ProjectAnalysis_ProjectProxy] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[ProjectProxy] ([ProjectID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ProjectAnalysis] CHECK CONSTRAINT [FK_ProjectAnalysis_ProjectProxy]
GO
GRANT DELETE ON [dbo].[ProjectAnalysis] TO [DiversityCollectionAdministrator] AS [dbo]
GO
GRANT ALTER ON [dbo].[ProjectAnalysis] TO [DiversityCollectionEditor] AS [dbo]
GO
GRANT INSERT ON [dbo].[ProjectAnalysis] TO [DiversityCollectionEditor] AS [dbo]
GO
GRANT SELECT ON [dbo].[ProjectAnalysis] TO [DiversityCollectionUser] AS [dbo]
GO



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


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AnalysisProjectList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[AnalysisProjectList] (@ProjectID int)  
RETURNS @AnalysisList TABLE ([AnalysisID] [int] Primary key ,
	[AnalysisParentID] [int] NULL ,
	[DisplayText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
	[MeasurementUnit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Notes] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
	[AnalysisURI] [varchar] (255) COLLATE Latin1_General_CI_AS NULL)

/*
Returns a table that lists all the analysis items related to the given project.
MW 08.08.2009
TEST:
Select * from AnalysisProjectList(3)

*/
AS
BEGIN

INSERT INTO @AnalysisList
           ([AnalysisID]
           ,[AnalysisParentID]
           ,[DisplayText]
           ,[Description]
           ,[MeasurementUnit]
           ,[Notes]
           ,[AnalysisURI])
SELECT Analysis.AnalysisID, Analysis.AnalysisParentID, Analysis.DisplayText, Analysis.Description, Analysis.MeasurementUnit, Analysis.Notes, 
Analysis.AnalysisURI
FROM  ProjectAnalysis INNER JOIN
Analysis ON ProjectAnalysis.AnalysisID = Analysis.AnalysisID
WHERE ProjectAnalysis.ProjectID = @ProjectID

DECLARE @TempItem TABLE (AnalysisID int primary key)
INSERT INTO @TempItem ([AnalysisID])
SELECT Analysis.AnalysisID
FROM  ProjectAnalysis INNER JOIN
Analysis ON ProjectAnalysis.AnalysisID = Analysis.AnalysisID
WHERE ProjectAnalysis.ProjectID = @ProjectID

declare @ParentID int

DECLARE HierarchyCursor  CURSOR for
	select AnalysisID from @TempItem
	open HierarchyCursor
	FETCH next from HierarchyCursor into @ParentID
	WHILE @@FETCH_STATUS = 0
	BEGIN
	insert into @AnalysisList select AnalysisID , AnalysisParentID, DisplayText , Description , MeasurementUnit, Notes , AnalysisURI
	from dbo.AnalysisChildNodes (@ParentID) where AnalysisID not in (select AnalysisID from @AnalysisList)
	FETCH NEXT FROM HierarchyCursor into @ParentID
	END
CLOSE HierarchyCursor
DEALLOCATE HierarchyCursor

   RETURN
END

' 
END
GO

GRANT SELECT ON [dbo].[AnalysisProjectList] TO [DiversityCollectionUser] AS [dbo]
GO






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



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AnalysisList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'

CREATE FUNCTION [dbo].[AnalysisList] (@ProjectID int, @TaxonomicGroup nvarchar(50))  
RETURNS @AnalysisList TABLE ([AnalysisID] [int] Primary key ,
	[AnalysisParentID] [int] NULL ,
	[DisplayText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
	[MeasurementUnit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Notes] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
	[AnalysisURI] [varchar] (255) COLLATE Latin1_General_CI_AS NULL,
	[DisplayTextHierarchy] nvarchar (255))

/*
Returns a table that lists all the analysis items related to the given project.
MW 08.08.2009
TEST:
Select * from AnalysisList(3, ''fungus'')

*/
AS
BEGIN

INSERT INTO @AnalysisList
           ([AnalysisID]
           ,[AnalysisParentID]
           ,[DisplayText]
           ,[Description]
           ,[MeasurementUnit]
           ,[Notes]
           ,[AnalysisURI]
           ,[DisplayTextHierarchy])
SELECT A.AnalysisID, A.AnalysisParentID, A.DisplayText, A.Description, A.MeasurementUnit, A.Notes, A.AnalysisURI, 
	CASE WHEN Appp.DisplayText IS NULL 
	THEN '''' ELSE Appp.DisplayText + '' - '' END + CASE WHEN App.DisplayText IS NULL 
	THEN '''' ELSE App.DisplayText + '' - '' END + CASE WHEN Ap.DisplayText IS NULL 
	THEN '''' ELSE Ap.DisplayText + '' - '' END + A.DisplayText 
	+ CASE WHEN A.MeasurementUnit IS NULL THEN '''' ELSE '' ['' + A.MeasurementUnit + '']'' END 
	AS DisplayTextHierarchy
FROM Analysis AS Appp RIGHT OUTER JOIN Analysis AS App ON Appp.AnalysisID = App.AnalysisParentID RIGHT OUTER JOIN
	Analysis AS Ap ON App.AnalysisID = Ap.AnalysisParentID RIGHT OUTER JOIN
	Analysis AS A INNER JOIN
	AnalysisTaxonomicGroup AS T ON A.AnalysisID = T.AnalysisID INNER JOIN
	ProjectAnalysis AS P ON A.AnalysisID = P.AnalysisID ON Ap.AnalysisID = A.AnalysisParentID
WHERE (T.TaxonomicGroup = @TaxonomicGroup) 
AND (P.ProjectID = @ProjectID)

DECLARE @TempItem TABLE (AnalysisID int primary key)
INSERT INTO @TempItem ([AnalysisID])
SELECT AnalysisID
FROM  @AnalysisList

declare @ParentID int

DECLARE HierarchyCursor  CURSOR for
	select AnalysisID from @TempItem
	open HierarchyCursor
	FETCH next from HierarchyCursor into @ParentID
	WHILE @@FETCH_STATUS = 0
	BEGIN
	insert into @AnalysisList 
	(AnalysisID , AnalysisParentID, DisplayText , Description , MeasurementUnit, Notes , AnalysisURI, DisplayTextHierarchy)
	select A.AnalysisID , A.AnalysisParentID, A.DisplayText , A.Description , A.MeasurementUnit, A.Notes , A.AnalysisURI,
	CASE WHEN Appp.DisplayText IS NULL 
	THEN '''' ELSE Appp.DisplayText + '' - '' END + CASE WHEN App.DisplayText IS NULL 
	THEN '''' ELSE App.DisplayText + '' - '' END + CASE WHEN Ap.DisplayText IS NULL 
	THEN '''' ELSE Ap.DisplayText + '' - '' END + A.DisplayText 
	+ CASE WHEN A.MeasurementUnit IS NULL THEN '''' ELSE '' ['' + A.MeasurementUnit + '']'' END 
	AS DisplayTextHierarchy
	FROM Analysis AS Appp RIGHT OUTER JOIN Analysis AS App ON Appp.AnalysisID = App.AnalysisParentID RIGHT OUTER JOIN
	Analysis AS Ap ON App.AnalysisID = Ap.AnalysisParentID RIGHT OUTER JOIN
	dbo.AnalysisChildNodes (@ParentID) AS A  ON Ap.AnalysisID = A.AnalysisParentID where A.AnalysisID not in (select AnalysisID from @AnalysisList)
	FETCH NEXT FROM HierarchyCursor into @ParentID
	END
CLOSE HierarchyCursor
DEALLOCATE HierarchyCursor

   RETURN
END


' 
END
GO

GRANT SELECT ON [dbo].[AnalysisList] TO [DiversityCollectionUser] AS [dbo]
GO



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




IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AnalysisListForUnit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'


CREATE FUNCTION [dbo].[AnalysisListForUnit] (@IdentificationUnitID int)  
RETURNS @AnalysisList TABLE ([AnalysisID] [int] Primary key ,
	[AnalysisParentID] [int] NULL ,
	[DisplayText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
	[MeasurementUnit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Notes] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
	[AnalysisURI] [varchar] (255) COLLATE Latin1_General_CI_AS NULL,
	[DisplayTextHierarchy] nvarchar (255))

/*
Returns a table that lists all the analysis items related to the given unit.
the list depends upon the analysis types available for a taxonomic group
and the projects available for an analysis
MW 08.08.2009
TEST:
Select * from AnalysisListForUnit(224404)

*/
AS
BEGIN

DECLARE @AnalysisID int

-- PROJECTS

-- GET THE ANALYSIS ACCORDING TO PROJECTS
DECLARE @AnalysisProjectCursor TABLE (AnalysisID int primary key)

INSERT INTO @AnalysisProjectCursor (AnalysisID)
SELECT DISTINCT A.AnalysisID
FROM  CollectionProject AS P INNER JOIN
ProjectAnalysis AS A ON P.ProjectID = A.ProjectID INNER JOIN
IdentificationUnit AS U ON P.CollectionSpecimenID = U.CollectionSpecimenID
WHERE (U.IdentificationUnitID = @IdentificationUnitID)



-- GET THE ANALYSIS CHILDS ACCORDING TO PROJECTS
DECLARE @AnalysisProject TABLE (AnalysisID int primary key)

INSERT INTO @AnalysisProject (AnalysisID)
SELECT DISTINCT A.AnalysisID
FROM  CollectionProject AS P INNER JOIN
ProjectAnalysis AS A ON P.ProjectID = A.ProjectID INNER JOIN
IdentificationUnit AS U ON P.CollectionSpecimenID = U.CollectionSpecimenID
WHERE (U.IdentificationUnitID = @IdentificationUnitID)

DECLARE AnalysisProjectCursor  CURSOR FOR
	SELECT AnalysisID FROM @AnalysisProjectCursor
	OPEN AnalysisProjectCursor
	FETCH next from AnalysisProjectCursor into @AnalysisID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @AnalysisProject (AnalysisID)
		SELECT DISTINCT AnalysisID
		FROM AnalysisChildNodes(@AnalysisID) C
		WHERE C.AnalysisID NOT IN (SELECT AnalysisID FROM @AnalysisProject)
		FETCH NEXT FROM AnalysisProjectCursor INTO @AnalysisID
	END
CLOSE AnalysisProjectCursor
DEALLOCATE AnalysisProjectCursor




-- TAXA

-- GET THE ANALYSIS ACCORDING TO TAXA
DECLARE @AnalysisTaxaCursor TABLE (AnalysisID int primary key)

INSERT INTO @AnalysisTaxaCursor (AnalysisID)
SELECT DISTINCT  A.AnalysisID
FROM IdentificationUnit AS U INNER JOIN
AnalysisTaxonomicGroup AS T ON U.TaxonomicGroup = T.TaxonomicGroup INNER JOIN
Analysis AS A ON T.AnalysisID = A.AnalysisID
WHERE (U.IdentificationUnitID = @IdentificationUnitID)



-- GET THE ANALYSIS CHILDS ACCORDING TO TAXA
DECLARE @AnalysisTaxa TABLE (AnalysisID int primary key)

INSERT INTO @AnalysisTaxa (AnalysisID)
SELECT DISTINCT  A.AnalysisID
FROM IdentificationUnit AS U INNER JOIN
AnalysisTaxonomicGroup AS T ON U.TaxonomicGroup = T.TaxonomicGroup INNER JOIN
Analysis AS A ON T.AnalysisID = A.AnalysisID
WHERE (U.IdentificationUnitID = @IdentificationUnitID)

DECLARE AnalysisTaxaCursor  CURSOR FOR
	SELECT AnalysisID FROM @AnalysisTaxaCursor
	OPEN AnalysisTaxaCursor
	FETCH next from AnalysisTaxaCursor into @AnalysisID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @AnalysisTaxa (AnalysisID)
		SELECT DISTINCT AnalysisID
		FROM AnalysisChildNodes(@AnalysisID) C
		WHERE C.AnalysisID NOT IN (SELECT AnalysisID FROM @AnalysisTaxa)
		FETCH NEXT FROM AnalysisTaxaCursor INTO @AnalysisID
	END
CLOSE AnalysisTaxaCursor
DEALLOCATE AnalysisTaxaCursor

INSERT INTO @AnalysisList
           ([AnalysisID]
           ,[AnalysisParentID]
           ,[DisplayText]
           ,[Description]
           ,[MeasurementUnit]
           ,[Notes]
           ,[AnalysisURI]
           ,[DisplayTextHierarchy])
SELECT A.AnalysisID, A.AnalysisParentID, A.DisplayText, A.Description, A.MeasurementUnit, A.Notes, A.AnalysisURI, 
	CASE WHEN Ap3.DisplayText IS NULL 
	THEN '''' ELSE Ap3.DisplayText + '' - '' END + CASE WHEN Ap2.DisplayText IS NULL 
	THEN '''' ELSE Ap2.DisplayText + '' - '' END + CASE WHEN Ap1.DisplayText IS NULL 
	THEN '''' ELSE Ap1.DisplayText + '' - '' END + A.DisplayText 
	+ CASE WHEN A.MeasurementUnit IS NULL THEN '''' ELSE '' ['' + A.MeasurementUnit + '']'' END 
	AS DisplayTextHierarchy
FROM Analysis AS Ap3 RIGHT OUTER JOIN Analysis AS Ap2 ON Ap3.AnalysisID = Ap2.AnalysisParentID RIGHT OUTER JOIN
	Analysis AS Ap1 ON Ap2.AnalysisID = Ap1.AnalysisParentID RIGHT OUTER JOIN
	Analysis AS A  ON Ap1.AnalysisID = A.AnalysisParentID
	INNER JOIN @AnalysisTaxa AS T ON A.AnalysisID = T.AnalysisID 
	INNER JOIN @AnalysisProject AS P ON A.AnalysisID = P.AnalysisID

   RETURN
END



' 
END
GO

GRANT SELECT ON [dbo].[AnalysisListForUnit] TO [DiversityCollectionUser] AS [dbo]
GO


--#####################################################################################################################
--######  RowGUID for DivMobi  ################################################################################################
--#####################################################################################################################



/*

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

select distinct 'if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = ' + CHAR(39) + T.TABLE_NAME + CHAR(39) + ' and C.COLUMN_NAME = ' + CHAR(39) + 'RowGUID' + CHAR(39) + ') < 1 begin ALTER TABLE [dbo].[' + T.TABLE_NAME + '] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;'
from INFORMATION_SCHEMA.COLUMNS T, INFORMATION_SCHEMA.TABLES TT 
where TT.TABLE_TYPE = 'BASE TABLE'
and TT.TABLE_NAME = T.TABLE_NAME
and TT.TABLE_CATALOG = T.TABLE_CATALOG
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 'Workbench%'
and T.TABLE_NAME not like 'dtproperties%'
and T.TABLE_NAME not like 'sysdiagrams%'
--and not exists (select * from INFORMATION_SCHEMA.COLUMNS C where C.COLUMN_NAME = 'RowGUID' and C.TABLE_NAME = T.TABLE_NAME)

*/


if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Analysis' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[Analysis] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()); end 
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'AnalysisTaxonomicGroup' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[AnalysisTaxonomicGroup] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()); end 
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollCircumstances_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollCircumstances_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()); end 
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollDateCategory_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollDateCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()); end 
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Collection' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[Collection] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()); end 
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionAgent' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionAgent] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionEvent' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionEvent] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionEventImage' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionEventImage] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionEventLocalisation' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionEventLocalisation] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionEventProperty' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionEventProperty] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionEventSeries' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionEventSeries] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionEventSeriesImage' and C.COLUMN_NAME = 'RowGUID') < 1 
begin ALTER TABLE [dbo].[CollectionEventSeriesImage] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionExternalDatasource' and C.COLUMN_NAME = 'RowGUID') < 1 
begin ALTER TABLE [dbo].[CollectionExternalDatasource] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionManager' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionManager] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionProject' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionProject] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionRequester' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionRequester] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionSpecimen' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionSpecimen] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionSpecimenImage' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionSpecimenImage] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionSpecimenPart' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionSpecimenPart] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionSpecimenProcessing' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionSpecimenProcessing] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionSpecimenRelation' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionSpecimenRelation] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollectionSpecimenTransaction' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollectionSpecimenTransaction] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollEventDateCategory_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollEventDateCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollEventImageType_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollEventImageType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollEventSeriesImageType_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollEventSeriesImageType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollExchangeType_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollExchangeType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollIdentificationCategory_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollIdentificationCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollIdentificationDateCategory_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollIdentificationDateCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollIdentificationQualifier_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollIdentificationQualifier_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollLabelTranscriptionState_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollLabelTranscriptionState_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollLabelType_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollLabelType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollMaterialCategory_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollMaterialCategory_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollSpecimenImageType_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollSpecimenImageType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollSpecimenRelationType_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollSpecimenRelationType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollTaxonomicGroup_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollTaxonomicGroup_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollTransactionType_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollTransactionType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollTypeStatus_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollTypeStatus_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'CollUnitRelationType_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[CollUnitRelationType_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Entity' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[Entity] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'EntityContext_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[EntityContext_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'EntityLanguageCode_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[EntityLanguageCode_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'EntityRepresentation' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[EntityRepresentation] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'EntityUsage' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[EntityUsage] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'EntityUsage_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[EntityUsage_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'ExternalRequestCredentials ' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[ExternalRequestCredentials ] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Identification' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[Identification] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'IdentificationUnit' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[IdentificationUnit] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'IdentificationUnitAnalysis' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[IdentificationUnitAnalysis] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'IdentificationUnitInPart' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[IdentificationUnitInPart] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'LanguageCode_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[LanguageCode_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'LocalisationSystem' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[LocalisationSystem] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'MeasurementUnit_Enum' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[MeasurementUnit_Enum] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Processing' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[Processing] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'ProcessingMaterialCategory' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[ProcessingMaterialCategory] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'ProjectProxy' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[ProjectProxy] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'ProjectUser' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[ProjectUser] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Property' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[Property] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'Transaction' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[Transaction] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'TransactionDocument' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[TransactionDocument] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO
if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'UserProxy' and C.COLUMN_NAME = 'RowGUID') < 1 begin 
ALTER TABLE [dbo].[UserProxy] ADD RowGUID [uniqueidentifier] ROWGUIDCOL  NOT NULL  DEFAULT (newsequentialid()) end;
GO


--#####################################################################################################################
--####   Processing - Project dependent ###########################################################################################
--#####################################################################################################################


CREATE TABLE [dbo].[ProjectProcessing](
	[ProcessingID] [int] NOT NULL,
	[ProjectID] [int] NOT NULL,
	[LogCreatedWhen] [datetime] NULL,
	[LogCreatedBy] [nvarchar](50) NULL,
	[LogUpdatedWhen] [datetime] NULL,
	[LogUpdatedBy] [nvarchar](50) NULL,
	[RowGUID] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_ProjectProcessing] PRIMARY KEY CLUSTERED 
(
	[ProcessingID] ASC,
	[ProjectID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

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

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID of the project to which the specimen belongs (Projects are defined in DiversityProjects)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectProcessing', @level2type=N'COLUMN',@level2name=N'ProjectID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The time when this dataset was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectProcessing', @level2type=N'COLUMN',@level2name=N'LogCreatedWhen'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who created this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectProcessing', @level2type=N'COLUMN',@level2name=N'LogCreatedBy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The last time when this dataset was updated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectProcessing', @level2type=N'COLUMN',@level2name=N'LogUpdatedWhen'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Who was the last to update this dataset' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectProcessing', @level2type=N'COLUMN',@level2name=N'LogUpdatedBy'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The types of processing available within a project' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProjectProcessing'
GO

ALTER TABLE [dbo].[ProjectProcessing]  WITH CHECK ADD  CONSTRAINT [FK_ProjectProcessing_Processing] FOREIGN KEY([ProcessingID])
REFERENCES [dbo].[Processing] ([ProcessingID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ProjectProcessing] CHECK CONSTRAINT [FK_ProjectProcessing_Processing]
GO

ALTER TABLE [dbo].[ProjectProcessing]  WITH CHECK ADD  CONSTRAINT [FK_ProjectProcessing_ProjectProxy] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[ProjectProxy] ([ProjectID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ProjectProcessing] CHECK CONSTRAINT [FK_ProjectProcessing_ProjectProxy]
GO

ALTER TABLE [dbo].[ProjectProcessing] ADD  CONSTRAINT [DF_ProjectProcessing_LogCreatedWhen]  DEFAULT (getdate()) FOR [LogCreatedWhen]
GO

ALTER TABLE [dbo].[ProjectProcessing] ADD  CONSTRAINT [DF_ProjectProcessing_LogCreatedBy]  DEFAULT (user_name()) FOR [LogCreatedBy]
GO

ALTER TABLE [dbo].[ProjectProcessing] ADD  CONSTRAINT [DF_ProjectProcessing_LogUpdatedWhen]  DEFAULT (getdate()) FOR [LogUpdatedWhen]
GO

ALTER TABLE [dbo].[ProjectProcessing] ADD  CONSTRAINT [DF_ProjectProcessing_LogUpdatedBy]  DEFAULT (user_name()) FOR [LogUpdatedBy]
GO

ALTER TABLE [dbo].[ProjectProcessing] ADD  CONSTRAINT [DF_ProjectProcessing_RowGUID]  DEFAULT (newsequentialid()) FOR [RowGUID]
GO


GRANT DELETE ON [dbo].[ProjectProcessing] TO [DiversityCollectionAdministrator] AS [dbo]
GO
GRANT ALTER ON [dbo].[ProjectProcessing] TO [DiversityCollectionEditor] AS [dbo]
GO
GRANT INSERT ON [dbo].[ProjectProcessing] TO [DiversityCollectionEditor] AS [dbo]
GO
GRANT SELECT ON [dbo].[ProjectProcessing] TO [DiversityCollectionUser] AS [dbo]
GO



/****** Object:  UserDefinedFunction [dbo].[ProcessingListForUnit]    Script Date: 08/20/2009 13:52:05 ******/


CREATE FUNCTION [dbo].[ProcessingListForPart] (@CollectionSpecimenID int, @SpecimenPartID int)  
RETURNS @ProcessingList TABLE ([ProcessingID] [int]  Primary key,
	[ProcessingParentID] [int] NULL,
	[DisplayText] [nvarchar](50) NULL,
	[Description] [nvarchar](max) NULL,
	[Notes] [nvarchar](max) NULL,
	[ProcessingURI] [varchar](255) NULL,
	[DisplayTextHierarchy] nvarchar (255))

/*
Returns a table that lists all the processing items related to the given part.
the list depends upon the processing types available for a material category
and the projects available for a processing
MW 08.08.2009
TEST:
SELECT * FROM dbo.ProcessingListForPart(177930,  NULL)
SELECT * FROM dbo.ProcessingListForPart(177930, 153619)

*/
AS
BEGIN

DECLARE @ProcessingID int

-- PROJECTS

-- GET THE Processing ACCORDING TO PROJECTS
DECLARE @ProcessingProjectCursor TABLE (ProcessingID int primary key)

INSERT INTO @ProcessingProjectCursor (ProcessingID)
SELECT DISTINCT A.ProcessingID
FROM  CollectionProject AS P INNER JOIN
ProjectProcessing AS A ON P.ProjectID = A.ProjectID INNER JOIN
CollectionSpecimen AS U ON P.CollectionSpecimenID = U.CollectionSpecimenID
--WHERE (U.SpecimenPartID = @SpecimenPartID)



-- GET THE Processing CHILDS ACCORDING TO PROJECTS
DECLARE @ProcessingProject TABLE (ProcessingID int primary key)

INSERT INTO @ProcessingProject (ProcessingID)
SELECT DISTINCT A.ProcessingID
FROM  CollectionProject AS P INNER JOIN
ProjectProcessing AS A ON P.ProjectID = A.ProjectID INNER JOIN
CollectionSpecimenPart AS U ON P.CollectionSpecimenID = U.CollectionSpecimenID

DECLARE ProcessingProjectCursor  CURSOR FOR
	SELECT ProcessingID FROM @ProcessingProjectCursor
	OPEN ProcessingProjectCursor
	FETCH next from ProcessingProjectCursor into @ProcessingID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @ProcessingProject (ProcessingID)
		SELECT DISTINCT ProcessingID
		FROM ProcessingChildNodes(@ProcessingID) C
		WHERE C.ProcessingID NOT IN (SELECT ProcessingID FROM @ProcessingProject)
		FETCH NEXT FROM ProcessingProjectCursor INTO @ProcessingID
	END
CLOSE ProcessingProjectCursor
DEALLOCATE ProcessingProjectCursor




-- Material

-- GET THE PROCESSING ACCORDING TO MATERIAL
DECLARE @ProcessingMaterialCursor TABLE (ProcessingID int primary key)

IF NOT @SpecimenPartID IS NULL
BEGIN
INSERT INTO @ProcessingMaterialCursor (ProcessingID)
SELECT DISTINCT  A.ProcessingID
FROM CollectionSpecimenPart AS U INNER JOIN
ProcessingMaterialCategory AS T ON U.MaterialCategory = T.MaterialCategory INNER JOIN
Processing AS A ON T.ProcessingID = A.ProcessingID
WHERE (U.SpecimenPartID = @SpecimenPartID)
AND (U.CollectionSpecimenID = @CollectionSpecimenID)
END

IF @SpecimenPartID IS NULL
BEGIN
INSERT INTO @ProcessingMaterialCursor (ProcessingID)
SELECT DISTINCT  A.ProcessingID
FROM CollectionSpecimenPart AS U INNER JOIN
ProcessingMaterialCategory AS T ON U.MaterialCategory = T.MaterialCategory INNER JOIN
Processing AS A ON T.ProcessingID = A.ProcessingID
WHERE (U.CollectionSpecimenID = @CollectionSpecimenID)
END


-- GET THE Processing CHILDS ACCORDING TO Material
DECLARE @ProcessingMaterial TABLE (ProcessingID int primary key)

IF NOT @SpecimenPartID IS NULL
BEGIN
INSERT INTO @ProcessingMaterial (ProcessingID)
SELECT DISTINCT  A.ProcessingID
FROM CollectionSpecimenPart AS U INNER JOIN
ProcessingMaterialCategory AS T ON U.MaterialCategory = T.MaterialCategory INNER JOIN
Processing AS A ON T.ProcessingID = A.ProcessingID
WHERE (U.SpecimenPartID = @SpecimenPartID)
AND (U.CollectionSpecimenID = @CollectionSpecimenID)
END

IF @SpecimenPartID IS NULL
BEGIN
INSERT INTO @ProcessingMaterial (ProcessingID)
SELECT DISTINCT  A.ProcessingID
FROM CollectionSpecimenPart AS U INNER JOIN
ProcessingMaterialCategory AS T ON U.MaterialCategory = T.MaterialCategory INNER JOIN
Processing AS A ON T.ProcessingID = A.ProcessingID
WHERE (U.CollectionSpecimenID = @CollectionSpecimenID)
END

DECLARE ProcessingMaterialCursor  CURSOR FOR
	SELECT ProcessingID FROM @ProcessingMaterialCursor
	OPEN ProcessingMaterialCursor
	FETCH next from ProcessingMaterialCursor into @ProcessingID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @ProcessingMaterial (ProcessingID)
		SELECT DISTINCT ProcessingID
		FROM ProcessingChildNodes(@ProcessingID) C
		WHERE C.ProcessingID NOT IN (SELECT ProcessingID FROM @ProcessingMaterial)
		FETCH NEXT FROM ProcessingMaterialCursor INTO @ProcessingID
	END
CLOSE ProcessingMaterialCursor
DEALLOCATE ProcessingMaterialCursor

INSERT INTO @ProcessingList
           ([ProcessingID]
           ,[ProcessingParentID]
           ,[DisplayText]
           ,[Description]
           ,[Notes]
           ,[ProcessingURI]
           ,[DisplayTextHierarchy])
SELECT A.ProcessingID, A.ProcessingParentID, A.DisplayText, A.Description, A.Notes, A.ProcessingURI, 
	CASE WHEN Ap3.DisplayText IS NULL 
	THEN '' ELSE Ap3.DisplayText + ' - ' END + CASE WHEN Ap2.DisplayText IS NULL 
	THEN '' ELSE Ap2.DisplayText + ' - ' END + CASE WHEN Ap1.DisplayText IS NULL 
	THEN '' ELSE Ap1.DisplayText + ' - ' END + A.DisplayText 
	AS DisplayTextHierarchy
FROM Processing AS Ap3 RIGHT OUTER JOIN Processing AS Ap2 ON Ap3.ProcessingID = Ap2.ProcessingParentID RIGHT OUTER JOIN
	Processing AS Ap1 ON Ap2.ProcessingID = Ap1.ProcessingParentID RIGHT OUTER JOIN
	Processing AS A  ON Ap1.ProcessingID = A.ProcessingParentID
	INNER JOIN @ProcessingMaterial AS T ON A.ProcessingID = T.ProcessingID 
	INNER JOIN @ProcessingProject AS P ON A.ProcessingID = P.ProcessingID

   RETURN
END

GO


GRANT SELECT ON [dbo].[ProcessingListForPart] TO [DiversityCollectionUser] AS [dbo]
GO

--#####################################################################################################################
--######   Description in Property etc  #######################################################################################
--#####################################################################################################################

/*
SELECT 'UPDATE Property SET Description = ' + char(39) + [Description] + CHAR(39) + ' where PropertyID = ' + cast(PropertyID as varchar)
  FROM [Property]
where Description <> ''
*/

UPDATE Property SET Description = 'European Nature Information System, http://eunis.eea.eu.int/index.jsp' where PropertyID = 1
GO
UPDATE Property SET Description = 'Import aus Tabelle Naturraum der Datenbank Mykologie aus Görlitz' where PropertyID = 10
GO
UPDATE Property SET Description = 'Chronostratigraphie nach Angaben der Bayerischen Staatssammlung für Paläontologie' where PropertyID = 20
GO
UPDATE Property SET Description = 'Lithostratigraphie nach Angaben der Bayerischen Staatssammlung für Paläontologie' where PropertyID = 30
GO

/*
SELECT 'UPDATE CollTaxonomicGroup_Enum SET Description = ' + char(39) + [Description] + CHAR(39) + ' where Code = ' + CHAR(39) + [Code] + CHAR(39) 
  FROM [DiversityCollection_Test].[dbo].[CollTaxonomicGroup_Enum] T
  where T.Code <> T.Description
*/

UPDATE CollTaxonomicGroup_Enum SET Description = 'Arthropoda: insects, spiders, crabs etc.' where Code = 'arthropode'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'bacteria/prokaryotic' where Code = 'bacterium'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Marchantiomorpha (liverworts), Anthocerotophyta (hornworts) and Bryophyta (mosses)' where Code = 'bryophyte'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Cnidaria: sea anemones, corals, jellyfish, sea pens, hydra' where Code = 'cnidaria'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Echinodermata: sea urchins, starfish, and their allies' where Code = 'echinoderm'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Animalia (= Metazoa) excl. Chordata' where Code = 'evertebrate'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Eumycota: mushrooms, sac fungi, yeast, molds, rusts, smuts, etc.' where Code = 'fungus'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Hexapoda: insects, springtails, diplurans, and proturans' where Code = 'insect'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Lichenized Eumycota' where Code = 'lichen'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Mollusca: Snails, clams, mussels, squids, octopi, chitons, and tusk shells' where Code = 'mollusc'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Myxomycota' where Code = 'myxomycete'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'other/anorganic' where Code = 'other'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Lycophytes, ferns and seedplants' where Code = 'plant'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'unknown group' where Code = 'unknown'
GO
UPDATE CollTaxonomicGroup_Enum SET Description = 'Chordata' where Code = 'vertebrate'
GO



/*
SELECT 'UPDATE CollMaterialCategory_Enum SET Description = ' + char(39) + [Description] + CHAR(39) + ' where Code = ' + CHAR(39) + [Code] + CHAR(39) 
  FROM CollMaterialCategory_Enum E
  where E.Code <> E.Description
*/

UPDATE CollMaterialCategory_Enum SET Description = 'bones or skeleton from vertebrates' where Code = 'bones'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'cones of e.g. Gymnospermae, not stored together with the herbarium sheet' where Code = 'cones'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'living cultures of organisms' where Code = 'cultures'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'original line or color drawing' where Code = 'drawing'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'capsules or sheets as stored in a botanical collection' where Code = 'herbarium sheets'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'icones, images etc. stored in a botanical collection' where Code = 'icones'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'medium like sound recordings, videos, images and the like' where Code = 'medium'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'glass plate with specimen for microscopic study' where Code = 'micr. slide'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'pelt, skin or fur of an animal' where Code = 'pelt'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'photographic print (color or black/white)' where Code = 'photogr. print'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'image in the form of a photographic slide for projection' where Code = 'photogr. slide'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'table with specimen for Scanning electron microscopy' where Code = 'SEM table'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'shell e.g. of a snail' where Code = 'shell'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'skull of a vertrebrate' where Code = 'skull'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'sound recoding' where Code = 'sound'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'specimen stored in a collection' where Code = 'specimen'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'specimen preparation for Transmission electron microscopy' where Code = 'TEM specimen'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'tooth of an animal' where Code = 'tooth'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'trace e.g. footprints of an animal' where Code = 'trace'
GO
UPDATE CollMaterialCategory_Enum SET Description = 'vial, flask or simlar container' where Code = 'vial'
GO

--#####################################################################################################################
--######   AgentURI in UserProxy   #######################################################################################
--#####################################################################################################################


if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = 'UserProxy' and C.COLUMN_NAME = 'AgentURI') < 1 begin 
ALTER TABLE [dbo].[UserProxy] ADD AgentURI [varchar](255) NULL end;
GO





CREATE FUNCTION [dbo].[CurrentUser] ()  
RETURNS @User TABLE ([LoginName] [nvarchar] (50) Primary key ,
	[CombinedNameCache] [nvarchar](255) NULL,
	[UserURI] [varchar](255) NULL,
	[AgentURI] [varchar](255) NULL,
	[UserName] [nvarchar](255) NULL)
/*
Returns the information for the current user.
MW 15.07.2009
Test:
select UserName from [dbo].[CurrentUser] ()
select AgentURI from [dbo].[CurrentUser] ()

*/

AS
BEGIN

-- fill the list with the basic data from the table UserProxy
INSERT INTO @User
	([LoginName]
	,[CombinedNameCache]
	,[UserName]
	,[UserURI]
	,[AgentURI])
SELECT TOP 1 [LoginName]
	,case when [CombinedNameCache] is null then [LoginName] else [CombinedNameCache] end
	,case when [CombinedNameCache] is null then [LoginName] else 
	case when [CombinedNameCache] like '%(%' then 
	RTRIM(substring([CombinedNameCache], 1, charindex('(', [CombinedNameCache]) - 1)) 
	else [CombinedNameCache] end end
	,[UserURI]
	,[AgentURI]
  FROM [UserProxy]
WHERE LoginName = User_Name()


   RETURN
END

GO

GRANT SELECT ON [dbo].[CurrentUser] TO [DiversityCollectionUser] AS [dbo]
GO


--#####################################################################################################################
--######  RowGUID for log tables  ################################################################################################
--#####################################################################################################################



/*

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

select distinct 'if (select count(*) from INFORMATION_SCHEMA.COLUMNS C where C.TABLE_NAME = ' + CHAR(39) + T.TABLE_NAME + CHAR(39) + ' and C.COLUMN_NAME = ' + CHAR(39) + 'RowGUID' + CHAR(39) + ') < 1 begin ALTER TABLE [dbo].[' + T.TABLE_NAME + '] ADD RowGUID [uniqueidentifier] ROWGUIDCOL NULL end;GO'
from INFORMATION_SCHEMA.COLUMNS T, INFORMATION_SCHEMA.TABLES TT 
where TT.TABLE_TYPE = 'BASE TABLE'
and TT.TABLE_NAME = T.TABLE_NAME
and TT.TABLE_CATALOG = T.TABLE_CATALOG
and T.TABLE_NAME not like 'xx%'
and T.TABLE_NAME like '%_log'
and T.TABLE_NAME not like '%_log_%'
and T.TABLE_NAME not like 'Application%'
and T.TABLE_NAME not like 'Workbench%'
and T.TABLE_NAME not like 'dtproperties%'
and T.TABLE_NAME not like 'sysdiagrams%'

*/


--#####################################################################################################################
--######  Grant for [CollEventSeriesImageType_Enum]  ################################################################################################
--#####################################################################################################################

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


GRANT SELECT ON [dbo].[CollEventSeriesImageType_Enum] TO [DiversityCollectionUser] AS [dbo]
GO


--#####################################################################################################################
--######  OnlyHierarchy for Analysis and Processing  ################################################################################################
--#####################################################################################################################


ALTER TABLE [dbo].[Analysis] ADD OnlyHierarchy [bit] NULL;
GO
ALTER TABLE [dbo].[Analysis] ADD  CONSTRAINT [DF_Analysis_OnlyHierarchy]  DEFAULT ((0)) FOR [OnlyHierarchy]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If the entry is only used for the hierarchical arrangement of the entries' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Analysis', @level2type=N'COLUMN',@level2name=N'OnlyHierarchy'
GO



ALTER TABLE [dbo].[Processing] ADD OnlyHierarchy [bit] NULL;
GO
ALTER TABLE [dbo].[Processing] ADD  CONSTRAINT [DF_Processing_OnlyHierarchy]  DEFAULT ((0)) FOR [OnlyHierarchy]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If the entry is only used for the hierarchical arrangement of the entries' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Processing', @level2type=N'COLUMN',@level2name=N'OnlyHierarchy'
GO


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


ALTER FUNCTION [dbo].[AnalysisChildNodes] (@ID int)  
RETURNS @ItemList TABLE (AnalysisID int primary key,
	AnalysisParentID int NULL ,
	DisplayText nvarchar (50)   NULL ,
	Description nvarchar  (500)   NULL ,
	MeasurementUnit nvarchar (50)   NULL ,
	Notes nvarchar  (1000)   NULL ,
	AnalysisURI 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.
MW02.01.2006

SELECT * from [dbo].[AnalysisChildNodes] (81)
*/
AS
BEGIN
   declare @ParentID int
   RETURN
END
GO


ALTER FUNCTION [dbo].[AnalysisChildNodes] (@ID int)  
RETURNS @ItemList TABLE (AnalysisID int primary key,
	AnalysisParentID int NULL ,
	DisplayText nvarchar (50)   NULL ,
	Description nvarchar  (500)   NULL ,
	MeasurementUnit nvarchar (50)   NULL ,
	Notes nvarchar  (1000)   NULL ,
	AnalysisURI 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.
MW02.01.2006
*/
AS
BEGIN
   declare @ParentID int
   DECLARE @TempItem TABLE (AnalysisID int primary key,
	AnalysisParentID int NULL ,
	DisplayText nvarchar (50)   NULL ,
	Description nvarchar  (500)   NULL ,
	MeasurementUnit nvarchar (50)   NULL ,
	Notes nvarchar  (1000)   NULL ,
	AnalysisURI varchar  (255)   NULL,
	OnlyHierarchy [bit] NULL )

 INSERT @TempItem (AnalysisID , AnalysisParentID, DisplayText , Description , MeasurementUnit, Notes , AnalysisURI, OnlyHierarchy) 
	SELECT AnalysisID , AnalysisParentID, DisplayText , Description , MeasurementUnit, Notes , AnalysisURI, OnlyHierarchy
	FROM Analysis WHERE AnalysisParentID = @ID 

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



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

ALTER FUNCTION [dbo].[AnalysisHierarchy] (@AnalysisID int)  
RETURNS @AnalysisList TABLE ([AnalysisID] [int] Primary key ,
	[AnalysisParentID] [int] NULL ,
	[DisplayText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
	[MeasurementUnit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Notes] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
	[AnalysisURI] [varchar] (255) COLLATE Latin1_General_CI_AS NULL,
	[OnlyHierarchy] [bit] NULL)  


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

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

set @TopID = (select AnalysisParentID from Analysis where AnalysisID = @AnalysisID) 

set @i = (select count(*) from Analysis where AnalysisID = @AnalysisID)

if (@TopID is null )
	set @TopID =  @AnalysisID
else	
	begin
	while (@i > 0)
		begin
		set @AnalysisID = (select AnalysisParentID from Analysis where AnalysisID = @AnalysisID and not AnalysisParentID is null) 
		set @i = (select count(*) from Analysis where AnalysisID = @AnalysisID and not AnalysisParentID is null)
		end
	set @TopID = @AnalysisID
	end

-- copy the root node in the result list
   INSERT @AnalysisList
   SELECT DISTINCT AnalysisID, AnalysisParentID, DisplayText, Description, MeasurementUnit, Notes, AnalysisURI, OnlyHierarchy
   FROM Analysis
   WHERE Analysis.AnalysisID = @TopID

-- copy the child nodes into the result list
   INSERT @AnalysisList
   SELECT * FROM dbo.AnalysisChildNodes (@TopID)
   
   RETURN
END
GO


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


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

ALTER FUNCTION [dbo].[AnalysisProjectList] (@ProjectID int)   
RETURNS @AnalysisList TABLE ([AnalysisID] [int] Primary key , 	
[AnalysisParentID] [int] NULL , 	
[DisplayText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL , 	
[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL , 	
[MeasurementUnit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL , 	
[Notes] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL , 	
[AnalysisURI] [varchar] (255) COLLATE Latin1_General_CI_AS NULL,
[OnlyHierarchy] [bit] NULL)  
/* 
Returns a table that lists all the analysis items related to the given project. 
MW 08.08.2009 
TEST: 
Select * from AnalysisProjectList(3)  
Select * from AnalysisProjectList(703)  
*/ 
AS BEGIN  INSERT INTO @AnalysisList            
([AnalysisID]            
,[AnalysisParentID]            
,[DisplayText]            
,[Description]            
,[MeasurementUnit]            
,[Notes]            
,[AnalysisURI]
,[OnlyHierarchy]) 
SELECT Analysis.AnalysisID, Analysis.AnalysisParentID, Analysis.DisplayText, Analysis.Description, 
Analysis.MeasurementUnit, Analysis.Notes,  Analysis.AnalysisURI, Analysis.OnlyHierarchy
FROM  ProjectAnalysis 
INNER JOIN Analysis ON ProjectAnalysis.AnalysisID = Analysis.AnalysisID 
WHERE ProjectAnalysis.ProjectID = @ProjectID  

DECLARE @TempItem TABLE (AnalysisID int primary key) 

INSERT INTO @TempItem ([AnalysisID]) 
SELECT Analysis.AnalysisID 
FROM  ProjectAnalysis 
INNER JOIN Analysis ON ProjectAnalysis.AnalysisID = Analysis.AnalysisID 
WHERE ProjectAnalysis.ProjectID = @ProjectID  
declare @ParentID int  
DECLARE HierarchyCursor  CURSOR for 	select AnalysisID from @TempItem 	
open HierarchyCursor 	
FETCH next from HierarchyCursor into @ParentID 	
WHILE @@FETCH_STATUS = 0 	
BEGIN 	
insert into @AnalysisList select AnalysisID , AnalysisParentID, DisplayText , Description , MeasurementUnit, 
Notes , AnalysisURI, OnlyHierarchy 	
from dbo.AnalysisChildNodes (@ParentID) 
where AnalysisID not in (select AnalysisID from @AnalysisList) 	
FETCH NEXT FROM HierarchyCursor into @ParentID 	END 
CLOSE HierarchyCursor 
DEALLOCATE HierarchyCursor  
--DELETE FROM  @AnalysisList WHERE OnlyHierarchy = 1  
RETURN 
END  
GO


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


ALTER FUNCTION [dbo].[AnalysisList] 
(@ProjectID int, @TaxonomicGroup nvarchar(50))   
RETURNS @AnalysisList TABLE ([AnalysisID] [int] Primary key , 	
[AnalysisParentID] [int] NULL , 	
[DisplayText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL , 	
[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL , 	
[MeasurementUnit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL , 	
[Notes] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL , 	
[AnalysisURI] [varchar] (255) COLLATE Latin1_General_CI_AS NULL, 	
[DisplayTextHierarchy] nvarchar (255),
[OnlyHierarchy] [bit] NULL)    
/* 
Returns a table that lists all the analysis items related to the given project. 
MW 08.08.2009 
TEST: Select * from AnalysisList(3, 'fungus')  
*/ 
AS 
BEGIN  
INSERT INTO @AnalysisList            
([AnalysisID]            
,[AnalysisParentID]            
,[DisplayText]            
,[Description]            
,[MeasurementUnit]            
,[Notes]            
,[AnalysisURI]            
,[DisplayTextHierarchy]
,[OnlyHierarchy]) 
SELECT A.AnalysisID, A.AnalysisParentID, A.DisplayText, A.Description, A.MeasurementUnit, 
A.Notes, A.AnalysisURI,  	
CASE WHEN Appp.DisplayText IS NULL  	
THEN '' ELSE Appp.DisplayText + ' - ' END 
+ CASE WHEN App.DisplayText IS NULL  	
THEN '' ELSE App.DisplayText + ' - ' END 
+ CASE WHEN Ap.DisplayText IS NULL  	
THEN '' ELSE Ap.DisplayText + ' - ' END + A.DisplayText  	
+ CASE WHEN A.MeasurementUnit IS NULL THEN '' ELSE ' [' + A.MeasurementUnit + ']' END  	
AS DisplayTextHierarchy, A.OnlyHierarchy
FROM Analysis AS Appp 
RIGHT OUTER JOIN Analysis AS App ON Appp.AnalysisID = App.AnalysisParentID 
RIGHT OUTER JOIN 	Analysis AS Ap ON App.AnalysisID = Ap.AnalysisParentID 
RIGHT OUTER JOIN 	Analysis AS A 
INNER JOIN 	AnalysisTaxonomicGroup AS T ON A.AnalysisID = T.AnalysisID 
INNER JOIN 	ProjectAnalysis AS P ON A.AnalysisID = P.AnalysisID ON Ap.AnalysisID = A.AnalysisParentID 
WHERE (T.TaxonomicGroup = @TaxonomicGroup)  AND (P.ProjectID = @ProjectID)  
DECLARE @TempItem TABLE (AnalysisID int primary key) 
INSERT INTO @TempItem ([AnalysisID]) 
SELECT AnalysisID FROM  @AnalysisList  
declare @ParentID int  
DECLARE HierarchyCursor  CURSOR for 	select AnalysisID from @TempItem 	
open HierarchyCursor 	
FETCH next from HierarchyCursor into @ParentID 	
WHILE @@FETCH_STATUS = 0 	
BEGIN 	
insert into @AnalysisList  	(AnalysisID , AnalysisParentID, DisplayText , Description , 
MeasurementUnit, Notes , AnalysisURI, DisplayTextHierarchy, OnlyHierarchy) 	
select A.AnalysisID , A.AnalysisParentID, A.DisplayText , A.Description , 
A.MeasurementUnit, A.Notes , A.AnalysisURI, 	
CASE WHEN Appp.DisplayText IS NULL  	
THEN '' ELSE Appp.DisplayText + ' - ' END + CASE WHEN App.DisplayText IS NULL  	
THEN '' ELSE App.DisplayText + ' - ' END + CASE WHEN Ap.DisplayText IS NULL  	
THEN '' ELSE Ap.DisplayText + ' - ' END + A.DisplayText  	
+ CASE WHEN A.MeasurementUnit IS NULL THEN '' ELSE ' [' + A.MeasurementUnit + ']' END  	
AS DisplayTextHierarchy, A.OnlyHierarchy 	
FROM Analysis AS Appp 
RIGHT OUTER JOIN Analysis AS App ON Appp.AnalysisID = App.AnalysisParentID 
RIGHT OUTER JOIN 	Analysis AS Ap ON App.AnalysisID = Ap.AnalysisParentID 
RIGHT OUTER JOIN 	dbo.AnalysisChildNodes (@ParentID) AS A  ON Ap.AnalysisID = A.AnalysisParentID 
where A.AnalysisID not in (select AnalysisID from @AnalysisList) 	
FETCH NEXT FROM HierarchyCursor into @ParentID 	
END 
CLOSE HierarchyCursor 
DEALLOCATE HierarchyCursor     

RETURN 
END   
GO


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

ALTER FUNCTION [dbo].[AnalysisListForUnit] (@IdentificationUnitID int)   
RETURNS @AnalysisList TABLE ([AnalysisID] [int] Primary key , 	
	[AnalysisParentID] [int] NULL , 	
	[DisplayText] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL , 	
	[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL , 	
	[MeasurementUnit] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL , 	
	[Notes] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL , 	
	[AnalysisURI] [varchar] (255) COLLATE Latin1_General_CI_AS NULL, 	
	[DisplayTextHierarchy] nvarchar (255),  
	[OnlyHierarchy] [bit] NULL)    
/* 
Returns a table that lists all the analysis items related to the given unit. 
The list depends upon the analysis types available for a taxonomic group and the projects available for an analysis 
MW 08.08.2009 
TEST: Select * from AnalysisListForUnit(224404)  
*/ 
AS 
BEGIN  
DECLARE @AnalysisID int   

DECLARE @AnalysisProjectCursor TABLE (AnalysisID int primary key)  
INSERT INTO @AnalysisProjectCursor (AnalysisID) 
SELECT DISTINCT A.AnalysisID FROM  CollectionProject AS P 
	INNER JOIN ProjectAnalysis AS A ON P.ProjectID = A.ProjectID 
	INNER JOIN IdentificationUnit AS U ON P.CollectionSpecimenID = U.CollectionSpecimenID 
	WHERE (U.IdentificationUnitID = @IdentificationUnitID)    

DECLARE @AnalysisProject TABLE (AnalysisID int primary key)  
INSERT INTO @AnalysisProject (AnalysisID) 
SELECT DISTINCT A.AnalysisID 
	FROM  CollectionProject AS P 
	INNER JOIN ProjectAnalysis AS A ON P.ProjectID = A.ProjectID 
	INNER JOIN IdentificationUnit AS U ON P.CollectionSpecimenID = U.CollectionSpecimenID 
	WHERE (U.IdentificationUnitID = @IdentificationUnitID)  

DECLARE AnalysisProjectCursor CURSOR FOR SELECT AnalysisID FROM @AnalysisProjectCursor 	

OPEN AnalysisProjectCursor 	
FETCH next from AnalysisProjectCursor into @AnalysisID 	
WHILE @@FETCH_STATUS = 0 	
BEGIN 		
	INSERT INTO @AnalysisProject (AnalysisID) 		
		SELECT DISTINCT AnalysisID 		
		FROM AnalysisChildNodes(@AnalysisID) C 		
	WHERE C.AnalysisID NOT IN (SELECT AnalysisID FROM @AnalysisProject)
	FETCH NEXT FROM AnalysisProjectCursor INTO @AnalysisID
END
CLOSE AnalysisProjectCursor 
DEALLOCATE AnalysisProjectCursor    
  
DECLARE @AnalysisTaxaCursor TABLE (AnalysisID int primary key)  
INSERT INTO @AnalysisTaxaCursor (AnalysisID) 
SELECT DISTINCT  A.AnalysisID 
	FROM IdentificationUnit AS U 
	INNER JOIN AnalysisTaxonomicGroup AS T ON U.TaxonomicGroup = T.TaxonomicGroup 
	INNER JOIN Analysis AS A ON T.AnalysisID = A.AnalysisID 
	WHERE (U.IdentificationUnitID = @IdentificationUnitID)  
  
DECLARE @AnalysisTaxa TABLE (AnalysisID int primary key)  
INSERT INTO @AnalysisTaxa (AnalysisID) 
SELECT DISTINCT  A.AnalysisID FROM IdentificationUnit AS U 
	INNER JOIN AnalysisTaxonomicGroup AS T ON U.TaxonomicGroup = T.TaxonomicGroup 
	INNER JOIN Analysis AS A ON T.AnalysisID = A.AnalysisID 
	WHERE (U.IdentificationUnitID = @IdentificationUnitID)  

DECLARE AnalysisTaxaCursor CURSOR FOR SELECT AnalysisID FROM @AnalysisTaxaCursor 	
OPEN AnalysisTaxaCursor 	
FETCH next from AnalysisTaxaCursor into @AnalysisID 	
WHILE @@FETCH_STATUS = 0 	
BEGIN 		
	INSERT INTO @AnalysisTaxa (AnalysisID) 		
	SELECT DISTINCT AnalysisID 		
		FROM AnalysisChildNodes(@AnalysisID) C 		
		WHERE C.AnalysisID NOT IN (SELECT AnalysisID FROM @AnalysisTaxa) 		
	FETCH NEXT FROM AnalysisTaxaCursor INTO @AnalysisID 	
END 
CLOSE AnalysisTaxaCursor 
DEALLOCATE AnalysisTaxaCursor  

INSERT INTO @AnalysisList            
	([AnalysisID]            
	,[AnalysisParentID]            
	,[DisplayText]            
	,[Description]            
	,[MeasurementUnit]            
	,[Notes]            
	,[AnalysisURI]            
	,[DisplayTextHierarchy]
	, OnlyHierarchy) 
SELECT A.AnalysisID, 
	A.AnalysisParentID, 
	A.DisplayText, 
	A.Description, 
	A.MeasurementUnit, 
	A.Notes, 
	A.AnalysisURI,  	
	CASE WHEN Ap3.DisplayText IS NULL THEN '' ELSE Ap3.DisplayText + ' - ' END 
	+ CASE WHEN Ap2.DisplayText IS NULL THEN '' ELSE Ap2.DisplayText + ' - ' END 
	+ CASE WHEN Ap1.DisplayText IS NULL THEN '' ELSE Ap1.DisplayText + ' - ' END 
	+ A.DisplayText  	
	+ CASE WHEN A.MeasurementUnit IS NULL THEN '' ELSE ' [' + A.MeasurementUnit + ']' END  	
	AS DisplayTextHierarchy, A.OnlyHierarchy
	FROM Analysis AS Ap3 RIGHT OUTER JOIN Analysis AS Ap2 ON Ap3.AnalysisID = Ap2.AnalysisParentID 
	RIGHT OUTER JOIN 	Analysis AS Ap1 ON Ap2.AnalysisID = Ap1.AnalysisParentID 
	RIGHT OUTER JOIN 	Analysis AS A  ON Ap1.AnalysisID = A.AnalysisParentID 	
	INNER JOIN @AnalysisTaxa AS T ON A.AnalysisID = T.AnalysisID  	
	INNER JOIN @AnalysisProject AS P ON A.AnalysisID = P.AnalysisID   
	  
DELETE FROM  @AnalysisList WHERE OnlyHierarchy = 1  
RETURN 
END    
GO


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


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

GO

