/* Microsoft SQL Server - Scripting			*/
/* Server: CYCLONE					*/
/* Database: MBARI_Samples					*/
/* Creation Date 12/8/98 8:55:18 AM 			*/

/****** Object:  Stored Procedure dbo.migrate_import    Script Date: 12/8/98 8:55:19 AM ******/
/****** Object:  Stored Procedure dbo.migrate_import    Script Date: 10/14/98 1:16:59 PM ******/
CREATE PROCEDURE migrate_import
AS
/*---------------------------------------------*/
/* Declare Variables                           */
/*---------------------------------------------*/
DECLARE @fullCnt int,
        @errCnt int,
        @outpt varchar(225)
/*---------------------------------------------*/
/* turn off all the counts for general stuff   */
/*---------------------------------------------*/
SET NOCOUNT ON
/*---------------------------------------------*/
/* Clear out past errors                       */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ErrorMsg = NULL
/*---------------------------------------------*/
/* Validate FacilityName                       */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid FacilityName"
WHERE NOT EXISTS (SELECT * FROM Facility F
                  WHERE (F.FacilityName = MSSQL_IMPORT.FacilityName))
AND (MSSQL_IMPORT.FacilityName != Null)
/*---------------------------------------------*/
/* Validate MaterialName                       */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid MaterialName"
WHERE NOT EXISTS (SELECT * FROM Material M
                  WHERE (M.MaterialName = MSSQL_IMPORT.MaterialName))
AND (MSSQL_IMPORT.MaterialName != Null)
AND (MSSQL_IMPORT.MaterialName != "None")
AND (MSSQL_IMPORT.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Validate Equipment                          */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid Equipment"
WHERE NOT EXISTS (SELECT * FROM Equipment E
                  WHERE (E.Equipment = MSSQL_IMPORT.Equipment))
AND (MSSQL_IMPORT.Equipment != Null)
AND (MSSQL_IMPORT.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Validate PreservativeMethod                 */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid PreservativeMethod"
WHERE NOT EXISTS (SELECT * FROM PreservativeMethod PM
                  WHERE (PM.PreservativeMethod = MSSQL_IMPORT.PreservativeMethod))
AND (MSSQL_IMPORT.PreservativeMethod != Null)
AND (MSSQL_IMPORT.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Validate ContainerType                      */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid ContainerType"
WHERE NOT EXISTS (SELECT * FROM ContainerType CT
                  WHERE (CT.ContainerType = MSSQL_IMPORT.ContainerType))
AND (MSSQL_IMPORT.ContainerType != Null)
AND (MSSQL_IMPORT.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Validate SubSampleMethod                    */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid SubSampleMethod"
WHERE NOT EXISTS (SELECT * FROM SubSampleMethod SM
                  WHERE (SM.SubSampleMethod = MSSQL_IMPORT.SubSampleMethod))
AND (MSSQL_IMPORT.SubSampleMethod != Null)
AND (MSSQL_IMPORT.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Validate Citation                           */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid Citation"
WHERE NOT EXISTS (SELECT * FROM Citation C
                  WHERE (C.CitationID = MSSQL_IMPORT.CitationID))
AND (MSSQL_IMPORT.CitationID != Null)
AND (MSSQL_IMPORT.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Get the ChiefScientist ID From Person       */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ChiefScientistID = (SELECT MAX(PersonID) FROM Person P
                                            WHERE (P.LastName = MSSQL_IMPORT.ChiefScientist))
UPDATE MSSQL_IMPORT SET ChiefScientistID = (SELECT PersonID FROM Person P WHERE P.LastName = "unknown")
WHERE (MSSQL_IMPORT.ChiefScientist IS NULL)

UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid ChiefScientist"
WHERE (ChiefScientist != NULL)
  AND (ChiefScientistID = NULL)
  AND (ErrorMsg = NULL)
/*---------------------------------------------*/
/* Get the Collector ID From Person            */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET CollectorID = (SELECT MAX(PersonID) FROM Person P
                                       WHERE (P.LastName = MSSQL_IMPORT.Collector))
WHERE (MSSQL_IMPORT.ErrorMsg = NULL)
UPDATE MSSQL_IMPORT SET CollectorID = (SELECT PersonID FROM Person P 
                                       WHERE (P.LastName = "unknown"))
WHERE (MSSQL_IMPORT.Collector = NULL)
  AND (MSSQL_IMPORT.ErrorMsg = NULL)
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid Collector"
WHERE (Collector != NULL)
  AND (CollectorID = NULL)
  AND (ErrorMsg = NULL)
/*---------------------------------------------*/
/* Get the Contact ID From Person              */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET ContactID = (SELECT MAX(PersonID) FROM Person P
                                     WHERE (P.LastName = MSSQL_IMPORT.Contact))
WHERE (MSSQL_IMPORT.ErrorMsg = NULL)
UPDATE MSSQL_IMPORT SET ContactID = (SELECT PersonID FROM Person P WHERE P.LastName = "unknown")
WHERE (MSSQL_IMPORT.Contact = NULL)
  AND (MSSQL_IMPORT.ErrorMsg = NULL)
UPDATE MSSQL_IMPORT SET ErrorMsg = "Invalid Contact"
WHERE (Contact != NULL)
  AND (ContactID = NULL)
  AND (ErrorMsg = NULL)
/*---------------------------------------------*/
/* Create the CollectionGroup Records          */
/*---------------------------------------------*/
INSERT INTO CollectionGroup (StartDTG, ShipName, ROVName, ChiefScientistID)
	SELECT DISTINCT StartDTG, ShipName, ROVName, ChiefScientistID FROM MSSQL_IMPORT MI
	WHERE NOT EXISTS (SELECT CollectionGroupID FROM CollectionGroup CG
                        WHERE (CG.StartDTG = MI.StartDTG)
                          AND ((CG.ShipName = MI.ShipName) OR
                               ((CG.ShipName = NULL) AND 
                                (MI.ShipName = NULL))))
        AND (MI.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Put the CollectionGroupID in MSSQL_IMPORT   */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET CollectionGroupID = (SELECT DISTINCT CollectionGroupID FROM CollectionGroup CG
                                             WHERE (CG.StartDTG = MSSQL_IMPORT.StartDTG)
                                               AND ((CG.ShipName = MSSQL_IMPORT.ShipName) OR
                                                    ((CG.ShipName = NULL) AND 
                                                     (MSSQL_IMPORT.ShipName = NULL))))
WHERE (MSSQL_IMPORT.ErrorMsg = NULL)
UPDATE MSSQL_IMPORT SET ErrorMsg = "CollectionGroup Error"
WHERE (CollectionGroupID = NULL)
  AND (ErrorMsg = NULL)
/*---------------------------------------------*/
/* Create the CollectionEvent Records          */
/*---------------------------------------------*/
INSERT INTO CollectionEvent (CollectionRefName,CollectionEventDTG, TapeTimeCode, Latitude, Longitude,
	Depth, DiveName, WaypointName, CollectionVIMSConcept, Equipment, Comment, CollectionGroupID)
   SELECT DISTINCT CollectionRefName, CollectionEventDTG, MAX(TapeTimeCode), MAX(Latitude), MAX(Longitude),
	MAX(Depth), MAX(DiveName), MAX(WaypointName), MAX(CollectionVIMSConcept), MAX(Equipment), MAX(Comment), MAX(CollectionGroupID)
   FROM MSSQL_IMPORT MI
   WHERE NOT EXISTS (SELECT CollectionEventID FROM CollectionEvent CE
                     WHERE (CE.CollectionEventDTG = MI.CollectionEventDTG)
                       AND (CE.CollectionRefName = MI.CollectionRefName))
     AND (MI.ErrorMsg = NULL)
  GROUP BY CollectionRefName, CollectionEventDTG
/*---------------------------------------------*/
/* Put the CollectionEventID in MSSQL_IMPORT   */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET CollectionEventID = (SELECT MAX(CollectionEventID) FROM CollectionEvent CE
                                             WHERE (CE.CollectionEventDTG = MSSQL_IMPORT.CollectionEventDTG)
                                               AND (CE.CollectionRefName = MSSQL_IMPORT.CollectionRefName))
WHERE (MSSQL_IMPORT.ErrorMsg = NULL)
UPDATE MSSQL_IMPORT SET ErrorMsg = "CollectionEvent Error"
WHERE (CollectionEventID = NULL)
  AND (ErrorMsg = NULL)
/*---------------------------------------------*/
/* Create the Sample Records                   */
/*---------------------------------------------*/
INSERT INTO Sample (CollectionEventID, SampleRefName, DescriptionComment, CollectorID, ContactID, LotCount, 
	Sorted, PreservativeMethod, SubSampleMethod, AnalysisComment, SampleExists, ContainerType, Owner,
	ConditionComment, NeedsLabel, NeedsNewContainer, NeedsPreservative)
   SELECT CollectionEventID, SampleRefName, DescriptionComment, CollectorID, ContactID, LotCount, 
	Sorted, PreservativeMethod, SubSampleMethod, AnalysisComment, SampleExists, ContainerType, Owner,
	ConditionComment, NeedsLabel, NeedsNewContainer, NeedsPreservative
   FROM MSSQL_IMPORT MI
   WHERE NOT EXISTS (SELECT SampleID FROM Sample S
                     WHERE (S.SampleRefName = MI.SampleRefName))
     AND (MI.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Put the SampleID in MSSQL_IMPORT            */
/*---------------------------------------------*/
UPDATE MSSQL_IMPORT SET SampleID = (SELECT SampleID FROM Sample S
                                    WHERE (S.SampleRefName = MSSQL_IMPORT.SampleRefName))
WHERE (MSSQL_IMPORT.ErrorMsg = NULL)
UPDATE MSSQL_IMPORT SET ErrorMsg = "Sample Field Error"
WHERE (SampleID = NULL)
  AND (ErrorMsg = NULL)
/*---------------------------------------------------*/
/* Put the ParentSampleID in MSSQL_IMPORT            */
/*---------------------------------------------------*/
UPDATE MSSQL_IMPORT SET ParentSampleID = (SELECT SampleID FROM Sample S
                                    WHERE (S.SampleRefName = MSSQL_IMPORT.ParentRefName))
WHERE (MSSQL_IMPORT.ErrorMsg = NULL)
UPDATE MSSQL_IMPORT SET ErrorMsg = "ParentRefName Error"
WHERE (ParentSampleID = NULL)
  AND (ParentRefName <> NULL)
  AND (ErrorMsg = NULL)
/*------------------------------------------*/
/* Update the Sample ParentID               */
/*------------------------------------------*/
UPDATE SAMPLE SET ParentSampleID = (SELECT ParentSampleID FROM MSSQL_IMPORT
                                    WHERE (Sample.SampleID = MSSQL_IMPORT.SampleID)
                                      AND (MSSQL_IMPORT.ParentSampleID <> NULL)
                                      AND (MSSQL_IMPORT.ErrorMsg = NULL))
/*---------------------------------------------*/
/* Create the SeedVIMSConcepts Records         */
/*---------------------------------------------*/
INSERT INTO SeedVIMSConcepts (SampleID, SeedConcept)
   SELECT DISTINCT SampleID, SeedConcept1 
   FROM MSSQL_IMPORT MI
   WHERE NOT EXISTS (SELECT * FROM SeedVIMSConcepts SVC
                      WHERE (SVC.SampleID = MI.SampleID)
                        AND (SVC.SeedConcept = MI.SeedConcept1))
     AND (MI.SeedConcept1 != NULL)
     AND (MI.ErrorMsg = NULL)
INSERT INTO SeedVIMSConcepts (SampleID, SeedConcept)
   SELECT DISTINCT SampleID, SeedConcept2 
   FROM MSSQL_IMPORT MI
   WHERE NOT EXISTS (SELECT * FROM SeedVIMSConcepts SVC
                      WHERE (SVC.SampleID = MI.SampleID)
                        AND (SVC.SeedConcept = MI.SeedConcept2))
     AND (MI.SeedConcept2 != NULL)
     AND (MI.ErrorMsg = NULL)
INSERT INTO SeedVIMSConcepts (SampleID, SeedConcept)
   SELECT DISTINCT SampleID, SeedConcept3 
   FROM MSSQL_IMPORT MI
   WHERE NOT EXISTS (SELECT * FROM SeedVIMSConcepts SVC
                      WHERE (SVC.SampleID = MI.SampleID)
                        AND (SVC.SeedConcept = MI.SeedConcept3))
     AND (MI.SeedConcept3 != NULL)
     AND (MI.ErrorMsg = NULL)
INSERT INTO SeedVIMSConcepts (SampleID, SeedConcept)
   SELECT DISTINCT SampleID, SeedConcept4 
   FROM MSSQL_IMPORT MI

   WHERE NOT EXISTS (SELECT * FROM SeedVIMSConcepts SVC
                      WHERE (SVC.SampleID = MI.SampleID)
                        AND (SVC.SeedConcept = MI.SeedConcept4))
     AND (MI.SeedConcept4 != NULL)
     AND (MI.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Create the Storage Location Records         */
/*---------------------------------------------*/
INSERT INTO StorageLocation (SampleID, FacilityName, SpecificLocationDescription)
   SELECT SampleID, FacilityName, SpecificLocationDescription
   FROM MSSQL_IMPORT MI
   WHERE (MI.FacilityName != NULL)
     AND (MI.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Create the HazMat Records                   */
/*---------------------------------------------*/
INSERT INTO HazMat (SampleID, MaterialName, Concentration, Quantity, Units)
   SELECT SampleID, MaterialName, Concentration, Quantity, Units
   FROM MSSQL_IMPORT MI
   WHERE (MI.MaterialName != NULL)
     AND (MI.MaterialName != "None")
     AND (MI.ErrorMsg = NULL)
/*---------------------------------------------*/
/* Print out the success/fail counts           */
/*---------------------------------------------*/
SELECT @fullCnt = (SELECT COUNT(*) FROM MSSQL_IMPORT
   WHERE (ErrorMsg = NULL))
SELECT @errCnt = (SELECT COUNT(*) FROM MSSQL_IMPORT
   WHERE (ErrorMsg != NULL))
SELECT @outpt =  convert (char (4), @fullCnt) + " Rows Entered Successfully."
PRINT @outpt
SELECT @outpt =  convert (char (4), @errCnt) + " Rows Failed."
PRINT @outpt
/*----------------------------------------------*/
/* Remove the records which loaded successfully */
/*----------------------------------------------*/
DELETE FROM MSSQL_IMPORT WHERE (ErrorMsg = NULL)
/*---------------------------------------------*/
/* Print out errors                            */
/*---------------------------------------------*/
If (@errCnt > 0)
   SELECT SampleRefName, ErrorMsg FROM MSSQL_IMPORT
SET NOCOUNT OFF
GO