 --2.  Before running this code, need to make sure that the SampleMedium terms
 --    used in the ODM 1.1.1 Variables table match valid ODM2 MediumCV terms.
 -------------------------------------------------------------------------------
-SELECT DISTINCT dv.ValueID, smp.SampleID, 
-	smp.SampleID + @MaxSamplingFeatureID AS SamplingFeatureID, 
-	smp.SampleID + @MaxActionID AS CollectionActionID, 
-	(smp.SampleID + @MaxSampleID + @MaxActionID) AS AnalysisActionID, 
-	@MaxResultID + dv.ValueID AS ResultID, smp.LabMethodID, 
-	'Specimen' AS SamplingFeatureTypeCV, NULL AS SamplingFeatureGeoTypeCV, 
-	NULL AS SpatialReferenceID, NULL AS FeatureGeometry, 
-	'Specimen loaded from an ODM 1.1.1 database.' AS SamplingFeatureDescription, 
-	smp.SampleType AS SpecimenTypeCV, smp.LabSampleCode AS SamplingFeatureCode, 
-	vr.SampleMedium AS SpecimenMediumCV, 1 AS IsFieldSpecimen, st.SiteID
-INTO #TempSpecimenInfo
-FROM LittleBearRiverODM.dbo.Samples smp, LittleBearRiverODM.dbo.DataValues dv, 
-	LittleBearRiverODM.dbo.Variables vr, LittleBearRiverODM.dbo.Sites st
-WHERE smp.SampleID = dv.SampleID AND dv.VariableID = vr.VariableID 
-	AND dv.SiteID = st.SiteID;
-
--------------------------------------------------------------------------------
---Insert the records for the Specimens in the ODM2.SamplingFeature table 
--------------------------------------------------------------------------------
-SET IDENTITY_INSERT ODM2.ODM2.SamplingFeatures ON;
-INSERT INTO ODM2.ODM2.SamplingFeatures (SamplingFeatureID, SamplingFeatureTypeCV, 
-	SamplingFeatureCode, SamplingFeatureName, SamplingFeatureDescription, 
-	SamplingFeatureGeoTypeCV, FeatureGeometry, Elevation_m, ElevationDatumCV)
-SELECT SamplingFeatureID, SamplingFeatureTypeCV, SamplingFeatureCode, 
-	NULL AS SamplingFeatureName, SamplingFeatureDescription, 
-	SamplingFeatureGeoTypeCV, NULL AS FeatureGeometry, NULL AS Elevation_m, 
-	NULL AS ElevationDatumCV
-FROM #TempSpecimenInfo 
-ORDER BY SamplingFeatureID;
-SET IDENTITY_INSERT ODM2.ODM2.SamplingFeatures OFF;
-
--------------------------------------------------------------------------------
---Insert records into the ODM2.Specimens table for the samples
--------------------------------------------------------------------------------
 INSERT INTO ODM2.ODM2.Specimens (SamplingFeatureID, SpecimenTypeCV, 
 	SpecimenMediumCV, IsFieldSpecimen)
-SELECT SamplingFeatureID, SpecimenTypeCV, SpecimenMediumCV, IsFieldSpecimen
-FROM #TempSpecimenInfo
-ORDER BY SamplingFeatureID;
+SELECT DISTINCT sp.SamplingFeatureID, smp.SampleType AS SpecimenTypeCV, 
+	vr.SampleMedium AS SpecimenMediumCV, 1 AS IsFieldSpecimen
+FROM #tempInsertedSpecimens sp, LittleBearRiverODM.dbo.Samples smp,
+	LittleBearRiverODM.dbo.DataValues dv, LittleBearRiverODM.dbo.Variables vr
+WHERE smp.SampleID = dv.SampleID AND dv.VariableID = vr.VariableID
+	AND sp.SamplingFeatureCode = smp.LabSampleCode;
 
 -------------------------------------------------------------------------------
 --Populate the ODM2.RelatedFeatures table to associate the Specimen 
 --SamplingFeatures with the Site at which they were collected
+--NOTES:
+--1.  The following code works because I used the SiteIDs from the ODM 1.1.1
+--    Sites table as the SamplingFeatureIDs for those Sites in ODM2.
 -------------------------------------------------------------------------------
 INSERT INTO ODM2.ODM2.RelatedFeatures (SamplingFeatureID, RelationshipTypeCV, 
 	RelatedFeatureID, SpatialOffsetID)
-SELECT SamplingFeatureID, 'wasCollectedAt' AS RelationshipTypeCV, 
-	SiteID AS RelatedFeatureID, NULL AS SpatialOffsetID
-FROM #TempSpecimenInfo 
-ORDER BY SamplingFeatureID, RelatedFeatureID;
-
--------------------------------------------------------------------------------
---Add "Sample collection" Actions to the ODM2.Actions table for each specimen
---NOTES:
---1.  Use the "Method" associated with the sample result in ODM 1.1.1
---2.  This works for LBR data because of the way I have specified the Method 
---    for sample-based data values as a sample collection method, but may not
---    work for all ODM 1.1 databases because others may not have done this.
--------------------------------------------------------------------------------
-SET IDENTITY_INSERT ODM2.ODM2.Actions ON;
-INSERT INTO ODM2.ODM2.Actions (ActionID, ActionTypeCV, MethodID, BeginDateTime,
-	BeginDateTimeUTCOffset, EndDateTime, EndDateTimeUTCOffset,
-	ActionDescription, ActionFileLink)
-SELECT DISTINCT temp.CollectionActionID AS ActionID, 
-	'Sample collection' AS ActionTypeCV, dv.MethodID, 
-	dv.LocalDateTime AS BeginDateTime, 
-	dv.UTCOffset AS BeginDateTimeUTCOffset, dv.LocalDateTime AS EndDateTime, 
-	dv.UTCOffset AS EndDateTimeUTCOffset, 
-	'Sample collection' AS ActionDescription, NULL AS ActionFileLink
-FROM LittleBearRiverODM.dbo.DataValues dv, #TempSpecimenInfo temp 
-WHERE dv.SampleID = temp.SampleID AND dv.SampleID IS NOT NULL;
-SET IDENTITY_INSERT ODM2.ODM2.Actions OFF;
-
--------------------------------------------------------------------------------
---Populate the ODM2.ActionBy table for the "Sample collection" actions
---NOTES:
---1.  This uses the contact person from the Sources table in ODM 1.1 as the 
---    "Sample collector"
--------------------------------------------------------------------------------
-INSERT INTO ODM2.ODM2.ActionBy (ActionID, AffiliationID, IsActionLead, 
-	RoleDescription)
-SELECT temp.CollectionActionID AS ActionID, aff.AffiliationID, 1 AS IsActionLead, 
-	'Sample collector' AS RoleDescription
-FROM #TempSpecimenInfo temp, ODM2.ODM2.Affiliations aff, 
-	LittleBearRiverODM.dbo.DataValues dv  
-WHERE temp.SampleID = dv.SampleID AND dv.SourceID = aff.OrganizationID
-ORDER BY ActionID, AffiliationID;
-
--------------------------------------------------------------------------------
---Populate the ODM2.FeatureActions table for the "Sample collection" actions
--------------------------------------------------------------------------------
-INSERT INTO ODM2.ODM2.FeatureActions (SamplingFeatureID, ActionID)
-SELECT SamplingFeatureID, CollectionActionID AS ActionID
-FROM #TempSpecimenInfo;
-
--------------------------------------------------------------------------------
---Add "Sample analysis" Actions to the ODM2.Actions table for the samples
+SELECT DISTINCT sp.SamplingFeatureID, 'Was collected at' AS RelationshipTypeCV, 
+	s.SiteID AS RelatedFeatureID, NULL AS SpatialOffsetID
+FROM #tempInsertedSpecimens sp, LittleBearRiverODM.dbo.Samples smp, 
+	LittleBearRiverODM.dbo.DataValues dv, LittleBearRiverODM.dbo.Sites s
+WHERE sp.SamplingFeatureCode = smp.LabSampleCode AND smp.SampleID = dv.SampleID
+	AND dv.SiteID = s.SiteID
+ORDER BY SamplingFeatureID, RelatedFeatureID;	
+
+-------------------------------------------------------------------------------
+--Add "Specimen analysis" Actions to the ODM2.Actions table for the samples in
+--the ODM 1.1.1 database.
 --NOTE:  
---1.  Use the "LabMethod" associated with the sample result in ODM 1.1
--------------------------------------------------------------------------------
+--1.  Only adds Actions for Samples that actually appear in the ODM 1.1.1
+--    DataValues table
+--2.  Uses the "LabMethod" associated with the Sample in the ODM 1.1.1 
+--    database.
+--3.  Assumes that each DataValue in the ODM 1.1.1 database that has a SampleID
+--    associated with it is the result of a separate analysis on that sample.
+-------------------------------------------------------------------------------
+--Create a temporary table with the information I need to populate the ODM2
+--Actions, ActionBy, and FeatureActions tables
+DECLARE @MaxActionID AS int;
+SELECT @MaxActionID = MAX(ActionID) FROM ODM2.ODM2.Actions;
+DECLARE @MaxResultID AS int;
+SELECT @MaxResultID = MAX(ResultID) FROM ODM2.ODM2.Results;
+SELECT DISTINCT dv.ValueID, dv.ValueID + @MaxResultID AS ResultID,
+	tempsp.SamplingFeatureID, dv.SampleID, 
+	@MaxActionID + ROW_NUMBER() OVER (ORDER BY SamplingFeatureID) AS ActionID, 
+	'Specimen analysis' AS ActionTypeCV, met.MethodID, 
+	dv.LocalDateTime AS BeginDateTime, dv.UTCOffset AS BeginDateTimeUTCOffset, 
+	dv.LocalDateTime AS EndDateTime, dv.UTCOffset AS EndDateTimeUTCOffset, 
+	'Specimen laboratory analysis.' AS ActionDescription, NULL AS ActionFileLink
+INTO #tempActionInfo
+FROM LittleBearRiverODM.dbo.DataValues dv, ODM2.ODM2.Methods met,
+	LittleBearRiverODM.dbo.LabMethods lmet, #tempInsertedSpecimens tempsp, 
+	LittleBearRiverODM.dbo.Samples smp
+WHERE tempsp.SamplingFeatureCode = smp.LabSampleCode 
+	AND smp.SampleID = dv.SampleID
+	AND smp.LabMethodID = lmet.LabMethodID 
+	AND lmet.LabMethodName = met.MethodName
+ORDER BY SamplingFeatureID ASC;
+
+--Now insert the "Specimen analysis" Actions into the ODM2.Actions table
 SET IDENTITY_INSERT ODM2.ODM2.Actions ON;
 INSERT INTO ODM2.ODM2.Actions (ActionID, ActionTypeCV, MethodID, BeginDateTime,
 	BeginDateTimeUTCOffset, EndDateTime, EndDateTimeUTCOffset, 
 	ActionDescription, ActionFileLink)
-SELECT DISTINCT temp.AnalysisActionID AS ActionID, 
-	'Sample analysis' AS ActionTypeCV, met.MethodID, 
-	dv.LocalDateTime AS BeginDateTime, dv.UTCOffset AS BeginDateTimeUTCOffset, 
-	dv.LocalDateTime AS EndDateTime, dv.UTCOffset AS EndDateTimeUTCOffset, 
-	'Sample laboratory analysis' AS ActionDescription, NULL AS ActionFileLink
-FROM LittleBearRiverODM.dbo.DataValues dv, 
-	LittleBearRiverODM.dbo.LabMethods lmet, ODM2.ODM2.Methods met, 
-	#TempSpecimenInfo temp
-WHERE temp.SampleID = dv.SampleID AND temp.LabMethodID = lmet.LabMethodID 
-	AND lmet.LabMethodName = met.MethodName;
+SELECT ActionID, ActionTypeCV, MethodID, BeginDateTime, BeginDateTimeUTCOffset, 
+	EndDateTime, EndDateTimeUTCOffset, 
+	'Specimen laboratory analysis.' AS ActionDescription, NULL AS ActionFileLink
+FROM #tempActionInfo 
 SET IDENTITY_INSERT ODM2.ODM2.Actions OFF;
 
 -------------------------------------------------------------------------------
---Populate the ODM2.ActionBy table for the "Sample analysis" actions
+--Populate the ODM2.ActionBy table for the "Specimen analysis" actions
 --NOTES:
 --1.  This uses the contact person from the Sources table in ODM 1.1 as the 
---    "Sample collector"
+--    "Laboratory analyst"
 -------------------------------------------------------------------------------
 INSERT INTO ODM2.ODM2.ActionBy (ActionID, AffiliationID, IsActionLead,
 	RoleDescription)
-SELECT temp.AnalysisActionID AS ActionID, aff.AffiliationID, 1 AS IsActionLead, 
-	'Analyst' AS RoleDescription
-FROM #TempSpecimenInfo temp, ODM2.ODM2.Affiliations aff, 
+SELECT DISTINCT tempai.ActionID, aff.AffiliationID, 1 AS IsActionLead, 
+	'Laboratory analyst' AS RoleDescription
+FROM #tempActionInfo tempai, ODM2.ODM2.Affiliations aff, 
 	LittleBearRiverODM.dbo.DataValues dv  
-WHERE temp.SampleID = dv.SampleID AND dv.SourceID = aff.OrganizationID
+WHERE tempai.SampleID = dv.SampleID AND dv.SourceID = aff.OrganizationID
 ORDER BY ActionID, AffiliationID;
 
 -------------------------------------------------------------------------------
---Populate the ODM2.FeatureActions table for the "Sample analysis" actions
--------------------------------------------------------------------------------
-DECLARE @InsertedFeatureActions TABLE(
-	FeatureActionID int,
-	SamplingFeatureID int,
-	ActionID int);	
+--Populate the ODM2.FeatureActions table for the "Specimen analysis" actions
+-------------------------------------------------------------------------------
+CREATE TABLE #tempInsertedFeatureActions
+(
+	FeatureActionID INT,
+	SamplingFeatureID INT,
+	ActionID INT
+);	
 INSERT INTO ODM2.ODM2.FeatureActions (SamplingFeatureID, ActionID)
 OUTPUT INSERTED.FeatureActionID, INSERTED.SamplingFeatureID, 
-	INSERTED.ActionID INTO @InsertedFeatureActions
-SELECT SamplingFeatureID, AnalysisActionID AS ActionID
-FROM #TempSpecimenInfo;
+	INSERTED.ActionID 
+INTO #tempInsertedFeatureActions
+SELECT SamplingFeatureID, ActionID
+FROM #tempActionInfo;
 
 -------------------------------------------------------------------------------
 --Add records to the ODM2.Results table for measurements resulting from 
---Specimens  
+--laboratory analysis of Specimens  
 --NOTES:
 --1.  The ResultType will be "Measurement" and there will be at least one 
 --    Result for every sample

FROM LittleBearRiverODM.dbo.LabMethods;

--Create a temporary table that will make this easier
SELECT DISTINCT lm.LabMethodID + @MaxMethodID + 1 AS MethodID, 
	'Specimen analysis' AS MethodTypeCV, 
	lm.LabMethodID + @MaxMethodID + 1 AS MethodCode, 
	lm.LabMethodName AS MethodName, lm.LabMethodDescription AS MethodDescription,
	lm.LabMethodLink AS MethodLink, org.OrganizationID		
INTO #TempLabMethodInfo
FROM LittleBearRiverODM.dbo.LabMethods lm, ODM2.ODM2.Organizations org
WHERE lm.LabName = org.OrganizationName AND 
	org.OrganizationID > @MaxOrganizationID;
--Add the LabMethods from ODM 1.1.1 to the ODM2 Methods table
SET IDENTITY_INSERT ODM2.ODM2.Methods ON; 
INSERT INTO ODM2.ODM2.Methods (MethodID, MethodTypeCV, MethodCode, MethodName,
	MethodDescription, MethodLink, OrganizationID)
SELECT MethodID, MethodTypeCV, MethodCode, MethodName, MethodDescription, 
	MethodLink, OrganizationID  
FROM #TempLabMethodInfo 
ORDER BY MethodID;
SET IDENTITY_INSERT ODM2.ODM2.Methods OFF;
--Drop the temp table
DROP TABLE #TempLabMethodInfo;

--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--Populate the ODM2.Actions, ODM2.Results, and ODM2.ResultValues tables for 
--sensor-based time series data
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


--    DataValues table.
--3.  Set Status = "Complete" for now 
-------------------------------------------------------------------------------
SET IDENTITY_INSERT ODM2.ODM2.Results ON;
INSERT INTO ODM2.ODM2.Results (ResultID, FeatureActionID, ResultTypeCV, 
	VariableID, UnitsID, TaxonomicClassifierID, ProcessingLevelID, ResultDateTime,
	ResultDateTimeUTCOffset, ValidDateTime, ValidDateTimeUTCOffset, StatusCV, 
	SampledMediumCV, ValueCount)
SELECT tempai.ResultID, tempfas.FeatureActionID, 'Measurement' AS ResultTypeCV, 
	dv.VariableID, vbl.VariableUnitsID AS UnitsID, NULL AS TaxonomicClassifierID,
	dv.QualityControlLevelID AS ProcessingLevelID, 
	dv.LocalDateTime AS ResultDateTime, dv.UTCOffset AS ResultDateTimeUTCOffset,
	NULL AS ValidDateTime, NULL AS ValidDateTimeUTCOffset, 'Complete' AS StatusCV,
	vbl.SampleMedium AS SampledMediumCV, 1 AS ValueCount
FROM LittleBearRiverODM.dbo.DataValues dv, LittleBearRiverODM.dbo.Variables vbl, 
	#tempActionInfo AS tempai, #tempInsertedFeatureActions AS tempfas
WHERE dv.ValueID = tempai.ValueID AND dv.VariableID = vbl.VariableID 
	AND tempai.SamplingFeatureID = tempfas.SamplingFeatureID 
	AND tempai.ActionID = tempfas.ActionID
ORDER BY ResultID ASC;
SET IDENTITY_INSERT ODM2.ODM2.Results OFF;

-------------------------------------------------------------------------------
--Populate the ODM2.ODM2.MeasurementResults table for specimen-based data
--NOTES:  
--1.  Results are created only for DataValues in the ODM 1.1.1 database if they 
--    have an associated SampleID
--2.  This assumes that values in the ODM database only have Z offsets, which
--    could be wrong for many data values. May need to be adjusted after the
--    script is run to correct the coordinates for any offsets.
--3.  I am preserving ValueIDs from the ODM 1.1 database so I can go back later
--    and add annotations for Qualifiers
-------------------------------------------------------------------------------
