SELECT r2.MBARI_ID, d.id, r2.FullSerial, r2.Model, d.mfgModel, r2.Manufacturer, d.mfgName  
FROM Device as d, 
(
SELECT * FROM
(
SELECT     *
FROM         instru
WHERE     not exists (select null
FROM Device
WHERE Device.id = instru.[SSDS ID] )
) r1
WHERE EXISTS (select null
FROM Device
WHERE Device.mfgSerialNumber = r1.FullSerial) 
) r2
WHERE d.mfgSerialNumber = FullSerial
ORDER BY r2.MBARI_ID

SELECT r2.MBARI_ID, d.id, r2.Serial, r2.Model, d.mfgModel, r2.Manufacturer, d.mfgName  
FROM Device as d, 
(
SELECT * FROM
(
SELECT     *
FROM         instru
WHERE     not exists (select null
FROM Device
WHERE Device.id = instru.[SSDS ID] )
) r1
WHERE EXISTS (select null
FROM Device
WHERE Device.mfgSerialNumber = r1.Serial) 
) r2
WHERE d.mfgSerialNumber = r2.Serial
ORDER BY r2.MBARI_ID

UPDATE Instru SET [SSDS ID] = 1359
WHERE MBARI_ID = 10

UPDATE Instru SET [SSDS ID] = 1567
WHERE MBARI_ID = 23

UPDATE Instru SET [SSDS ID] = 1568
WHERE MBARI_ID = 50

UPDATE Instru SET [SSDS ID] = 1222
WHERE MBARI_ID = 129

UPDATE Instru SET [SSDS ID] = 1273
WHERE MBARI_ID = 162

UPDATE Instru SET [SSDS ID] = 1337
WHERE MBARI_ID = 195

UPDATE Instru SET [SSDS ID] = 1279
WHERE MBARI_ID = 201

UPDATE Instru SET [SSDS ID] = 1356
WHERE MBARI_ID = 204

UPDATE Instru SET [SSDS ID] = 1470
WHERE MBARI_ID = 227

UPDATE Instru SET [SSDS ID] = 1562
WHERE MBARI_ID = 288

UPDATE Instru SET [SSDS ID] = 1382
WHERE MBARI_ID = 51

UPDATE Instru SET [SSDS ID] = 1406
WHERE MBARI_ID = 137

UPDATE Instru SET [SSDS ID] = 1267
WHERE MBARI_ID = 157

UPDATE Instru SET [SSDS ID] = 1456
WHERE MBARI_ID = 175

UPDATE Instru SET [SSDS ID] = 1457
WHERE MBARI_ID = 176

UPDATE Instru SET [SSDS ID] = 1314
WHERE MBARI_ID = 190

UPDATE Instru SET [SSDS ID] = 1459
WHERE MBARI_ID = 256

UPDATE instru 
SET Technician = 'coenen'
WHERE Technician = 'pc'

CREATE TABLE Manufacturers
 (id int IDENTITY PRIMARY KEY,
  label varchar (100) NOT NULL,
  fullName varchar(100) NOT NULL, 
  oldNames varchar(250) NULL,
  comments varchar(250) NULL)
GO

INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Aanderaa Data Instruments','Aanderaa Data Instruments','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Axys Technologies','Axys Technologies Inc.','','Axys Environmental Systems')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Biospherical Instruments','Biospherical Instruments Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Bluefin Robotics','Bluefin Robotics Corporation','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Brook Ocean Technology','Brook Ocean Technology Limited','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Crossbow Technology','Crossbow Technology Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Garmin','Garmin','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('HOBI Labs','Hydro-Optics, Biology, & Instrumentation Laboratories','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Magellan Navigation','Magellan Navigation, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('MBARI','Monterey Bay Aquarium Research Institute','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('McLane Research Laboratories','McLane Research Laboratories, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('NOBSKA','NOBSKA','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('ORBCOMM','ORBCOMM, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Paroscientific','Paroscientific, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('QUAKE Global','QUAKE Global, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Satlantic','Satlantic Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Sea-Bird Electronics','Sea-Bird Electronics, Inc.','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('SeaTech','SeaTech, Inc.','','no longer exists')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Simrad','Simrad, Inc','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Star Engineering for WHOI-Asimet','Star Engineering','',
                                                                         'Star Engineering is only authorized seller of ASIMETs')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('tbd','to be determined value for manufacturer','','if totally unknown use unknown')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Teledyne Benthos','Teledyne Benthos','Benthos','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Teledyne RD Instruments','Teledyne RD Instruments','RD Instruments',''	)
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('testing','test entry','','use for test entries')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('UC Santa Barbara','University of California, Santa Barbara','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('unknown','unknown manufacturer','','use for permanently unknown values, else use tbd')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('WET Labs','WET Labs','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('WHOI','Woods Hole Oceanographic Institution','','')
INSERT INTO Manufacturers (label, fullName, oldNames, comments) values ('Xantrex Technology','Xantrex Technology Inc.','','')

/** Copy all Instru devices without SSDS IDs **/
INSERT INTO [Northwind].[graybeal].[Device]
([version], [uuid], [name], [description], [mfgName], [mfgModel], [mfgSerialNumber], 
[infoUrlList], [PersonID_FK], [DeviceTypeID_FK], [Serial], 
[Calibration_organization], [Features], [Pressure_sensor], [Depth_Rating], 
[Firmware_EPROM], [Memory], [Receive_frequency], [Transmit_frequency], 
[Enable_code], [Release_code], [Tilt_option], [Purchased_for], 
[Owner], [Custodian], [Date_new], [PO], [Transaction_col], 
[Permanent_comment], [document_dir], [osg_view], [MBARI_ID])

  /** Notes for following:
  Set Version, UUID to 0
  Set name to null and description to Mfg + Model
  * Leaving DeviceType to fix up later
  * mfgSerialNumber gets Serial; overwrite with FullSerial (if exists) later
  Transaction_col is set to null (always null anyway)
  * Leaving MBARI_ID unchanged for now; later set it to id
  **/
  SELECT 
  0,'0',null,i.Manufacturer + ' ' + i.Model , i.Manufacturer, i.Model, i.Serial, 
  CAST (i.[manufacture web page]AS varchar(2048)), 126, 0, i.Serial,
  i.[Calibration organization], i.Features, i.[Pressure sensor], i.[Depth Rating],
  i.[Firmware/EPROM], i.Memory, i.[Receive frequency], i.[Transmit frequency],
  i.[Enable code], i.[Release code], i.[Tilt option], i.[Purchased for],
  i.Owner, i.Custodian, i.[Date new], i.PO, null,
  i.[Permanent comment], i.[document dir], 1, i.MBARI_ID
  
  FROM Instru as i
  WHERE i.[SSDS ID] is null OR i.[SSDS ID] = 0

/** Copy SSDS ID for new devices back into the SSDS ID column of Instru. **/
UPDATE    i
  SET              [SSDS ID] = d.id
  FROM Device d, instru i
  WHERE      d.MBARI_ID = i.MBARI_ID AND i.[SSDS ID] is null

/** Update all Instru devices with SSDS IDs **/
UPDATE    d
SET      
/* Don't overwrite the model in Device if instru model is null */       
d.mfgModel = 
  case when i.Model is not null then i.Model else d.mfgModel end,
d.mfgSerialNumber = i.Serial,
d.infoUrlList = CAST (i.[manufacture web page]AS varchar(2048)),
d.PersonID_FK = 126,
d.Serial = i.Serial,
d.Calibration_organization = i.[Calibration organization],
d.Features = i.Features,
d.Pressure_sensor = i.[Pressure sensor],
d.Depth_Rating = i.[Depth Rating],
d.Firmware_EPROM = i.[Firmware/EPROM],
d.Memory = i.Memory,
d.Receive_frequency = i.[Receive frequency],
d.Transmit_frequency = i.[Transmit frequency],
d.Enable_code = i.[Enable code],
d.Release_code = i.[Release code],
d.Tilt_option = i.[Tilt option],
d.Purchased_for = i.[Purchased for],
d.Owner = i.Owner,
d.Custodian = i.Custodian,
d.Date_new = i.[Date new],
d.PO = i.PO,
d.Transaction_col = null,
d.Permanent_comment = i.[Permanent comment],
d.document_dir = i.[document dir],
d.osg_view = 1,
d.MBARI_ID = i.MBARI_ID
FROM Device d, instru i
WHERE      d.MBARI_ID = i.MBARI_ID

/* Update mfgSerialNmber column with any FullSerial value */
UPDATE    d
SET              mfgSerialNumber = i.[FullSerial]
FROM Device d, instru i
WHERE      d.[MBARI_ID] = i.[MBARI_ID] 
AND NOT (i.FullSerial is null OR i.FullSerial = '')

/** Make Interim table **/
DROP TABLE TempDevFld
CREATE TABLE TempDevFld
 (id int PRIMARY KEY,
  tempMfg varchar (255) NULL,
  tempType varchar (255) NULL
 )
GO
/* Populate with all the device IDs */
INSERT INTO TempDevFld
  (id)
SELECT d.id 
FROM Device as d

/** Update manufacturer names to ones in Manufacturers table **/
/* Grab names from Instru that match exactly */
UPDATE    t
SET       t.tempMfg = i.Manufacturer
FROM      TempDevFld t, instru i
WHERE      t.id = i.[SSDS ID] and i.Manufacturer in (SELECT label from Manufacturers) AND t.tempMfg is null
/* Grab names from Device that match exactly */
UPDATE    t
SET       t.tempMfg = d.mfgName
FROM      TempDevFld t, Device d
WHERE      t.id = d.id and d.MfgName in (SELECT label from Manufacturers) AND t.tempMfg is null

/* Find names that are like the correct names */
/* First from instru */
UPDATE    t 
SET       t.tempMfg = r1.label 
FROM TempDevFld t, 
(     
  SELECT t.id as tid, t.tempMfg, i.[SSDS ID] as iid, i.Manufacturer, m.label
    FROM graybeal.TempDevFld t JOIN graybeal.instru i
      ON (t.id = i.[SSDS ID]), graybeal.Manufacturers m
  WHERE t.tempMfg is null AND
     ((substring(i.Manufacturer,1,3) = substring(m.label,1,3) 
            AND i.Manufacturer <> m.label 
            AND i.Manufacturer <> 'SeaTech' AND m.label <> 'SeaTech') OR
         CHARINDEX(i.Manufacturer,m.label) > 1)
) r1
WHERE t.id = r1.tid AND t.tempMfg is null

/* Then from Device */
UPDATE    t 
SET       t.tempMfg = r1.label 
FROM TempDevFld t, 
(     
  SELECT t.id as tid, t.tempMfg, d.id as did, d.mfgName, m.label
    FROM graybeal.TempDevFld t JOIN graybeal.Device d
      ON (t.id = d.id), graybeal.Manufacturers m
  WHERE t.tempMfg is null AND
     ((substring(d.mfgName,1,3) = substring(m.label,1,3) AND (d.mfgName <> m.label) 
            AND d.mfgName <> 'SeaTech' AND m.label <> 'SeaTech') OR
         CHARINDEX(d.mfgName,m.label) > 1)
) r1
WHERE t.id = r1.tid AND t.tempMfg is null



/* Do manual corrections */
/** Manual MfgName **/

UPDATE Device
SET mfgName = 'Aanderaa Instruments'
WHERE mfgName = 'Anderraa Instruments'

UPDATE Device
SET mfgName = 
'Magellan Navigation'
WHERE mfgName LIKE
'Ashtech%'

UPDATE Device
SET mfgName = 
'Star Engineering for WHOI-Asimet'
WHERE mfgName LIKE
'Asimet%'

UPDATE Device
SET mfgName = 
'AXYS Technologies'
WHERE mfgName LIKE
'Triaxys%'

UPDATE Device
SET mfgName = 
'UC Santa Barbara'
WHERE mfgName =
'UCSB'

UPDATE Device 
SET mfgName =
'testing'
WHERE mfgName LIKE
'Fraben%'

/** Get relevant ID and manufacturer information from 3 tables **/
SELECT t.tempMfg, d.mfgName, i.Manufacturer, t.id, d.id, i.[SSDS ID], i.MBARI_ID
FROM 
  (
  TempDevFld t 
  FULL OUTER JOIN Device d 
    ON t.id = d.id 
  )
  FULL OUTER JOIN instru i
    ON i.[SSDS ID] = t.id
ORDER BY tempMfg, mfgName

/* Update mfgModel to reflect manual corrections */
UPDATE Device
SET mfgModel = 
'GPC16-HVS'
WHERE id = 1313

UPDATE Device
SET mfgModel = 
'ECO FLNTUSB'
WHERE id = 1395

UPDATE Device
SET mfgModel = 
null
WHERE mfgModel =
'(null)'

UPDATE Device
SET mfgModel = 
replace (mfgModel, '-', ' ')
WHERE mfgModel LIKE 'ECO-FLNT%'

UPDATE Device
SET mfgModel = 
'ECO Triplet'
WHERE mfgModel =
'triplet'

UPDATE Device
SET mfgModel = 
'MMCv4'
WHERE mfgModel LIKE
'MMC v%'

UPDATE Device
SET mfgModel = 
'Medusa Rev 2'
WHERE mfgModel LIKE
'Medusa R%'


UPDATE Device
SET mfgModel = 
'Medusa Rev 2'
WHERE mfgModel LIKE
'Medusa v%'

UPDATE Device
SET mfgModel = 
'OASIS Buoy'
WHERE mfgModel LIKE
'Fiberglass tor%'

UPDATE Device
SET mfgModel = 
'E-meter'
WHERE mfgModel =
'Emeter'

/* Insert new devices */
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Backscatterometer/Fluorometer', 'backscatterometer that additionally provides fluorescence data')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Battery', 'device that stores energy and makes it available in an electrical form; not mediated by electronics (see Power Supply-Battery)')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Communication/GPS', 'transmits signals from one place to another, and contains GPS locator')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Echo Sounder-Singlebeam', 'acoustic system which transmits short acoustic pulses from single transmitter' +
            ' into the water column, and then detects echoes from impedance discontinuities (typically fish or plankton)')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Echo Sounder-Multibeam', 'acoustic system which transmits short acoustic pulses from multiple transmitters' +
            ' into the water column, and then detects echoes from impedance discontinuities (typically from the benthic layer)')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Inductive Modem Cable Coupler', 'connector for indcutive modems (ICC)')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Power Supply-Electric', 'power supply (delivers power to other equipment) that does not store energy')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Pump', 'mechanical device used to move liquids or gases')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Release', 'mechanism to disconnect two components, typically allowing a buoyant component in water'+
                   ' to detach from an anchor and float to the surface')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Sonar-Sidescan Swath Bathymetry', 'fixed-mounted acoustic bathymetric sounding systems that sweep a wide path; also known as SSBS')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Sonar-Scanning', 'acoustic system that can move (examples are Articulating, Profiling, and Pencil Beam)')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Sonar-Subbottom', 'acoustic system designed to penetrate layers beneath the benthic surface (e.g., CHIRP)')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Transmissometer', 'device to measure the optical transparency of water (also turbidity sensor)')
INSERT INTO graybeal.DeviceType(version ,Name, Description)
VALUES(0, 'Transponder', 'electrical device designed to receive a specific signal and automatically transmit a specific reply')

UPDATE DeviceType
SET description='Deprecated, please choose another type'
WHERE description is null

/* Update the nephelometer spelling in DeviceType table */
update DeviceType
set name='Fluorometer/Nephelometer', description='fluorometer configured to also do nephelometry (turbidity sensing)'
where id=158

/* Update Device Type column */
/* Grab names from Device that exist (but do it through view) */
UPDATE    t
SET       t.tempType = dv.devType
FROM      TempDevFld t, devTypeView dv
WHERE      t.id = dv.id AND dv.devType is not null AND t.tempType is null
/* Grab names from Device that match exactly */

/* Change instru entries to be 'right' */
UPDATE i
SET i.Type = 'Controller-Mooring Node'
FROM Instru i
WHERE i.Type = 'Controller' AND (i.Model LIKE 'OASIS%' OR i.Model = 'MMC')

UPDATE i
SET i.Type = 'backscatterometer'
FROM instru i
WHERE Type = 'backscatter' AND (Model ='HS2' OR Model='VSFS')

UPDATE i
SET i.Type = 'scatterometer'
FROM instru i
WHERE Type = 'backscatter' AND Model ='ECO BBSB'

/* Find matching instru names */
/* Grab names from Instru that match exactly */
UPDATE    t
SET       t.tempType = i.Type
FROM      TempDevFld t, instru i
WHERE      t.id = i.[SSDS ID]
          AND i.Type in (SELECT devType from devTypeView) 
          AND t.tempType is null

UPDATE    t
SET       t.tempType = 'Radiometer-Multispectral'
FROM      TempDevFld t
WHERE      t.id = 1381
UPDATE    t
SET       t.tempType = 'Inductive Model-Surface'
FROM      TempDevFld t
WHERE      t.id = 1319
UPDATE    t
SET       t.tempType = 'Controller-CTD'
FROM      TempDevFld t
WHERE      t.id = 1224
UPDATE    t
SET       t.tempType = 'Communication/GPS'
FROM      TempDevFld t
WHERE      t.id = 1323
UPDATE    t
SET       t.tempType = 'Current Sensors-ADCP'
FROM      TempDevFld t
WHERE     t.tempType = 'ADCP'

UPDATE t
set t.tempType = 'Fluorometer/Nephelometer'
FROM TempDevFld t
WHERE t.tempType = 'fluor/turbidity'
UPDATE t
set t.tempType = 'Backscatterometer/Fluorometer'
FROM TempDevFld t
WHERE t.tempType = 'backscatter/fluorometer'
UPDATE t
set t.tempType = 'Metereology Package'
FROM TempDevFld t
WHERE t.tempType = 'temperature/humidity'
UPDATE t
set t.tempType = 'Nitrogen Sensor/ISUS'
FROM TempDevFld t
WHERE t.tempType = 'Nitrate'
UPDATE t
set t.tempType = 'Nitrogen Sensor (this is Satlantic unit)'
FROM TempDevFld t
WHERE t.tempType = 'Nitrate analyzer'
UPDATE t
set t.tempType = 'Oxygen Sensor'
FROM TempDevFld t
WHERE t.tempType = 'Oxygen optode'
UPDATE t
set t.tempType = 'Toroid'
FROM TempDevFld t
WHERE t.tempType = 'Platform'
UPDATE t
set t.tempType = 'Power Supply-Electric'
FROM TempDevFld t
WHERE t.tempType = 'power source'
UPDATE t
set t.tempType = 'CO2 Monitor'
FROM TempDevFld t
WHERE t.tempType = 'pCO2'
UPDATE t
set t.tempType = 'Transponder'
FROM TempDevFld t
WHERE t.tempType = 'transducer'
UPDATE t
set t.tempType = 'Echo Sounder-Singlebeam'
FROM TempDevFld t
WHERE t.tempType = 'sounder'
UPDATE t
set t.tempType = 'Shutter-Antifouling'
FROM TempDevFld t
WHERE t.tempType = 'shutter'
UPDATE t
set t.tempType = 'Inductive Modem Cable Coupler'
FROM TempDevFld t
WHERE t.tempType = 'ICC'
UPDATE t
set t.tempType = 'Pump'
FROM TempDevFld t
WHERE t.tempType = 'pump'
UPDATE t
set t.tempType = 'Release'
FROM TempDevFld t
WHERE t.tempType = 'release'
set t.tempType = 'Backscatterometer'
FROM TempDevFld t
WHERE t.tempType = 'backscatter'
set t.tempType = 'Battery'
FROM TempDevFld t
WHERE t.tempType = 'battery'
set t.tempType = 'Transmissometer'
FROM TempDevFld t
WHERE t.tempType = 'transmissometer'

UPDATE dv
SET dv.devType = t.tempType
FROM devTypeView dv, TempDevFld t
WHERE dv.id = t.id