--use DiversityCollection_Test
--use DiversityCollection_CONC

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


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

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesSamplingPlot]'))
DROP VIEW [dbo].[FirstLinesSamplingPlot]
GO

CREATE VIEW [dbo].[FirstLinesSamplingPlot]
AS
SELECT     CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
                      DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache
FROM         dbo.CollectionEventLocalisation
WHERE     (LocalisationSystemID = 13)
GO

GRANT SELECT ON [dbo].[FirstLinesSamplingPlot] TO [DiversityCollectionUser]
GO

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



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


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



IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesIdenitificationUnitDisplayOrder1]'))
DROP VIEW [dbo].[FirstLinesIdenitificationUnitDisplayOrder1]
GO

CREATE VIEW [dbo].[FirstLinesIdenitificationUnitDisplayOrder1]
AS
SELECT     CollectionSpecimenID, IdentificationUnitID, MIN(DisplayOrder) AS DisplayOrder
FROM         dbo.IdentificationUnit
WHERE     (DisplayOrder > 0) AND EXISTS
                          (SELECT     CollectionSpecimenID
                            FROM          dbo.IdentificationUnit AS U
                            WHERE      (DisplayOrder > 0)
                            GROUP BY CollectionSpecimenID
                            HAVING      (CollectionSpecimenID = dbo.IdentificationUnit.CollectionSpecimenID) AND (MIN(DisplayOrder) = dbo.IdentificationUnit.DisplayOrder))
GROUP BY CollectionSpecimenID, IdentificationUnitID
GO

GRANT SELECT ON [dbo].[FirstLinesIdenitificationUnitDisplayOrder1] TO [DiversityCollectionUser]
GO

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


ALTER VIEW [dbo].[FirstLinesIdentificationUnit]
AS
SELECT     dbo.IdentificationUnit.CollectionSpecimenID, dbo.IdentificationUnit.IdentificationUnitID, dbo.IdentificationUnit.LastIdentificationCache, 
                      dbo.IdentificationUnit.FamilyCache, dbo.IdentificationUnit.OrderCache, dbo.IdentificationUnit.TaxonomicGroup, dbo.IdentificationUnit.OnlyObserved, 
                      dbo.IdentificationUnit.RelatedUnitID, dbo.IdentificationUnit.RelationType, dbo.IdentificationUnit.ColonisedSubstratePart, 
                      dbo.IdentificationUnit.LifeStage, dbo.IdentificationUnit.Gender, dbo.IdentificationUnit.NumberOfUnits, dbo.IdentificationUnit.ExsiccataNumber, 
                      dbo.IdentificationUnit.ExsiccataIdentification, dbo.IdentificationUnit.UnitIdentifier, dbo.IdentificationUnit.UnitDescription, 
                      dbo.IdentificationUnit.Circumstances, dbo.IdentificationUnit.DisplayOrder, dbo.IdentificationUnit.Notes
FROM         dbo.IdentificationUnit INNER JOIN
                      dbo.FirstLinesIdenitificationUnitDisplayOrder1 ON 
                      dbo.IdentificationUnit.CollectionSpecimenID = dbo.FirstLinesIdenitificationUnitDisplayOrder1.CollectionSpecimenID
GROUP BY dbo.FirstLinesIdenitificationUnitDisplayOrder1.CollectionSpecimenID, dbo.IdentificationUnit.CollectionSpecimenID, 
                      dbo.IdentificationUnit.IdentificationUnitID, dbo.IdentificationUnit.LastIdentificationCache, dbo.IdentificationUnit.FamilyCache, 
                      dbo.IdentificationUnit.OrderCache, dbo.IdentificationUnit.TaxonomicGroup, dbo.IdentificationUnit.OnlyObserved, dbo.IdentificationUnit.RelatedUnitID, 
                      dbo.IdentificationUnit.RelationType, dbo.IdentificationUnit.ColonisedSubstratePart, dbo.IdentificationUnit.LifeStage, dbo.IdentificationUnit.Gender, 
                      dbo.IdentificationUnit.NumberOfUnits, dbo.IdentificationUnit.ExsiccataNumber, dbo.IdentificationUnit.ExsiccataIdentification, 
                      dbo.IdentificationUnit.UnitIdentifier, dbo.IdentificationUnit.UnitDescription, dbo.IdentificationUnit.Circumstances, dbo.IdentificationUnit.DisplayOrder, 
                      dbo.IdentificationUnit.Notes
HAVING      (dbo.IdentificationUnit.DisplayOrder = MIN(dbo.FirstLinesIdenitificationUnitDisplayOrder1.DisplayOrder)) AND 
                      (dbo.IdentificationUnit.IdentificationUnitID = MIN(dbo.FirstLinesIdenitificationUnitDisplayOrder1.IdentificationUnitID))

GO

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

declare @i int
set @i = (select count(*) from ProjectProxy where ProjectID = 0)
if (@i = 0)
begin
INSERT INTO ProjectProxy
           (ProjectID
           ,Project
           )
     VALUES
           (0
           ,'Diversity Workbench'
           )
END
GO

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

declare @i int
set @i = (select count(*) from UserProxy where LoginName = 'dbo')
if (@i = 0)
begin
INSERT INTO UserProxy
           (LoginName
           ,CombinedNameCache
           )
     VALUES
           ('dbo'
           ,'dbo'
           )
END
GO

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

declare @i int
set @i = (select count(*) from ProjectUser where LoginName = 'dbo' and ProjectID = 0)
if (@i = 0)
begin
INSERT INTO ProjectUser
           (LoginName
           ,ProjectID
           )
     VALUES
           ('dbo'
           ,0
           )
END
GO

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

GRANT DELETE ON [dbo].[CollectionRequester] TO [DiversityCollectionManager]
GO

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

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[procCopyCollectionSpecimen]'))
DROP PROCEDURE [dbo].[procCopyCollectionSpecimen]
GO


CREATE  PROCEDURE [dbo].[procCopyCollectionSpecimen] 
	(@CollectionSpecimenID int output ,
	@OriginalCollectionSpecimenID int ,
	@AccessionNumber  nvarchar(50),
	@EventCopyMode int,
	@CopyUnits int)
AS
declare @count int
declare @EventID int

/*
Copy a collection specimen
@EventCopyMode
-1: dont copy the event, leave the entry in table CollectionSpecimen empty
0:  take same event as original specimen
1:  create new event with the same data as the old specimen
*/

if (@EventCopyMode = 0) set @EventID = (SELECT CollectionEventID FROM CollectionSpecimen WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID)
if (@EventCopyMode < 0) set @EventID = null

if (@EventCopyMode > 0) 
begin
	DECLARE @RC int
	DECLARE @CollectionEventID int
	DECLARE @OriginalCollectionEventID int
	set @OriginalCollectionEventID = (SELECT CollectionEventID FROM CollectionSpecimen WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID)

	-- CollectionEvent
	INSERT INTO CollectionEvent
		  (SeriesID, CollectorsEventNumber, CollectionDate, CollectionDay, CollectionMonth, CollectionYear, CollectionDateSupplement, 
		  CollectionDateCategory, CollectionTime, CollectionTimeSpan, LocalityDescription, HabitatDescription, ReferenceTitle, ReferenceURI, CollectingMethod, 
		  Notes, CountryCache, DataWithholdingReason)
	SELECT SeriesID, CollectorsEventNumber, CollectionDate, CollectionDay, CollectionMonth, CollectionYear, CollectionDateSupplement, 
		  CollectionDateCategory, CollectionTime, CollectionTimeSpan, LocalityDescription, HabitatDescription, ReferenceTitle, ReferenceURI, CollectingMethod, 
		  Notes, CountryCache, DataWithholdingReason
	FROM  CollectionEvent
	WHERE CollectionEventID = @OriginalCollectionEventID

	SET @CollectionEventID = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])

	-- CollectionEventImage
	INSERT INTO CollectionEventImage
		(CollectionEventID, URI, ResourceURI, ImageType, Notes)
	SELECT @CollectionEventID, URI, ResourceURI, ImageType, Notes
	FROM CollectionEventImage
	WHERE (CollectionEventID = @OriginalCollectionEventID)

	-- CollectionEventLocalisation
	INSERT INTO CollectionEventLocalisation
		(CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
		DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache)
	SELECT @CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
		DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache
	FROM CollectionEventLocalisation
	WHERE (CollectionEventID = @OriginalCollectionEventID)

	-- CollectionEventProperty
	INSERT INTO CollectionEventProperty
		(CollectionEventID, PropertyID, DisplayText, PropertyURI, PropertyHierarchyCache, PropertyValue,  
		ResponsibleName, ResponsibleAgentURI, Notes,AverageValueCache)
	SELECT CollectionEventID, PropertyID, DisplayText, PropertyURI, PropertyHierarchyCache, PropertyValue,  
		ResponsibleName, ResponsibleAgentURI, Notes,AverageValueCache
	FROM CollectionEventProperty
	WHERE (CollectionEventID = @OriginalCollectionEventID)

	set @EventID = @CollectionEventID
end

-- CollectionSpecimen
INSERT INTO CollectionSpecimen (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)
SELECT @EventID, CollectionID, @AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, 
	AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, 
	LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, 
	Problems, DataWithholdingReason
FROM CollectionSpecimen
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

SET @CollectionSpecimenID = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])

-- CollectionProject
INSERT INTO CollectionProject (CollectionSpecimenID, ProjectID)
SELECT @CollectionSpecimenID, ProjectID
FROM CollectionProject
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionAgent
INSERT INTO CollectionAgent ( CollectionSpecimenID, CollectorsName, CollectorsAgentURI, CollectorsSequence, CollectorsNumber, Notes, DataWithholdingReason)
SELECT   @CollectionSpecimenID, CollectorsName, CollectorsAgentURI, CollectorsSequence, CollectorsNumber, Notes, DataWithholdingReason
FROM CollectionAgent
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionSpecimenPart
INSERT INTO CollectionSpecimenPart ( CollectionSpecimenID, SpecimenPartID, DerivedFromSpecimenPartID, PreparationMethod, PreparationDate, 
	AccessionNumber, PartSublabel, CollectionID, MaterialCategory, StorageLocation, Stock, Notes)
SELECT @CollectionSpecimenID, SpecimenPartID, DerivedFromSpecimenPartID, PreparationMethod, PreparationDate, 
	AccessionNumber, PartSublabel, CollectionID, MaterialCategory, CASE WHEN @CopyUnits > 0 THEN StorageLocation ELSE '' END, Stock, Notes
FROM CollectionSpecimenPart
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionRelation
INSERT INTO CollectionSpecimenRelation ( CollectionSpecimenID, RelatedSpecimenURI, RelatedSpecimenDisplayText, RelationType, 
	RelatedSpecimenCollectionID, RelatedSpecimenDescription, Notes, IsInternalRelationCache)
SELECT @CollectionSpecimenID, RelatedSpecimenURI, RelatedSpecimenDisplayText, RelationType, 
	RelatedSpecimenCollectionID, RelatedSpecimenDescription, Notes, IsInternalRelationCache
FROM CollectionSpecimenRelation
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionSpecimenProcessing
INSERT INTO CollectionSpecimenProcessing
	(CollectionSpecimenID, ProcessingDate, ProcessingID, Protocoll, SpecimenPartID, ProcessingDuration,  
	ResponsibleName, ResponsibleAgentURI,Notes)
SELECT @CollectionSpecimenID, ProcessingDate, ProcessingID, Protocoll, SpecimenPartID, ProcessingDuration,  
	ResponsibleName, ResponsibleAgentURI,Notes
FROM CollectionSpecimenProcessing
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID


if @CopyUnits > 0
begin
	-- IdentificationUnit
	DECLARE @TempUnitTable TABLE (IdentificationUnitID int primary key,
	   OriginalIdentificationUnitID int NULL)
	DECLARE @IdentificationUnitID int
	DECLARE @OriginalIdentificationUnitID int

	DECLARE UnitCursor CURSOR FOR
	SELECT IdentificationUnitID FROM IdentificationUnit WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
	OPEN UnitCursor
	FETCH NEXT FROM UnitCursor INTO @OriginalIdentificationUnitID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- IdentificationUnit
		INSERT INTO IdentificationUnit (CollectionSpecimenID, LastIdentificationCache, FamilyCache, OrderCache, TaxonomicGroup, 
			OnlyObserved, RelationType, ColonisedSubstratePart, LifeStage, Gender, NumberOfUnits, ExsiccataNumber, ExsiccataIdentification, 
			Circumstances, DisplayOrder, Notes)
		SELECT @CollectionSpecimenID, LastIdentificationCache, FamilyCache, OrderCache, TaxonomicGroup, 
			OnlyObserved, RelationType, ColonisedSubstratePart, LifeStage, Gender, NumberOfUnits, ExsiccataNumber, ExsiccataIdentification, 
			Circumstances, DisplayOrder, Notes
		FROM IdentificationUnit
		WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
		AND IdentificationUnitID = @OriginalIdentificationUnitID

		SET @IdentificationUnitID = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])
		INSERT INTO @TempUnitTable (IdentificationUnitID, OriginalIdentificationUnitID) VALUES (@IdentificationUnitID, @OriginalIdentificationUnitID)

		-- Identification
		INSERT INTO Identification (CollectionSpecimenID, IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, 
			  IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, 
			  TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, Notes, ResponsibleName, ResponsibleAgentURI)
		SELECT @CollectionSpecimenID, @IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, 
			  IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, 
			  TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, Notes, ResponsibleName, ResponsibleAgentURI
		FROM Identification
		WHERE Identification.CollectionSpecimenID = @OriginalCollectionSpecimenID
		and Identification.IdentificationUnitID = @OriginalIdentificationUnitID

		-- IdentificationUnitInPart
		INSERT INTO IdentificationUnitInPart (CollectionSpecimenID, IdentificationUnitID, SpecimenPartID, DisplayOrder)
		SELECT @CollectionSpecimenID, @IdentificationUnitID, SpecimenPartID, DisplayOrder
		FROM IdentificationUnitInPart
		WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
		AND IdentificationUnitInPart.IdentificationUnitID = @OriginalIdentificationUnitID

   		FETCH NEXT FROM UnitCursor INTO @OriginalIdentificationUnitID
	END
	CLOSE UnitCursor
	DEALLOCATE UnitCursor

	DECLARE @I INT
	SET @I = (SELECT COUNT(*) FROM @TempUnitTable)
	IF @I > 0
	BEGIN
		UPDATE N SET N.RelatedUnitID = NH.IdentificationUnitID
		FROM IdentificationUnit N, IdentificationUnit NH, @TempUnitTable T, @TempUnitTable TH, IdentificationUnit O--, IdentificationUnit OH
		WHERE N.IdentificationUnitID = T.IdentificationUnitID
		AND O.IdentificationUnitID = T.OriginalIdentificationUnitID
		AND O.RelatedUnitID = TH.OriginalIdentificationUnitID
		AND TH.IdentificationUnitID = NH.IdentificationUnitID
	END
end

SELECT @CollectionSpecimenID

GO

GRANT EXECUTE ON [dbo].[procCopyCollectionSpecimen] TO [DiversityCollectionEditor]
GO

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



EXEC dbo.sp_executesql @statement = N'ALTER TRIGGER [dbo].[trgInsIdentification] ON [dbo].[Identification] 
FOR INSERT AS

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

/* setting the version in the main table */ 
declare @i int 
set @i = (select count(*) from inserted) 
if @i = 1 
begin 

/* setting the date fields */ 
UPDATE Identification
SET IdentificationDay = DAY(inserted.IdentificationDate), 
IdentificationMonth = MONTH(inserted.IdentificationDate), 
IdentificationYear = YEAR(inserted.IdentificationDate)
FROM inserted, Identification
WHERE inserted.IdentificationUnitID = Identification.IdentificationUnitID
AND inserted.CollectionSpecimenID = Identification.CollectionSpecimenID
AND inserted.IdentificationSequence = Identification.IdentificationSequence
AND NOT inserted.IdentificationDate IS NULL
AND inserted.IdentificationDay IS NULL 
AND inserted.IdentificationMonth IS NULL 
AND inserted.IdentificationYear IS NULL

DECLARE @ID int
SET  @ID = (SELECT CollectionSpecimenID FROM inserted)
EXECUTE procSetVersionCollectionSpecimen @ID
end
'
GO


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


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

