DECLARE @remap_code VARCHAR(20) = 'In-Situ HydroVu';

IF NOT EXISTS(SELECT * FROM rt_remap WHERE remap_code = @remap_code)

BEGIN

INSERT rt_remap (remap_code, status_flag)

VALUES (@remap_code, 'A');

END

DELETE FROM rt_remap_detail WHERE remap_code = @remap_code;

DECLARE @external_field VARCHAR(256) = 'parameter';

DECLARE @qualifiers TABLE (internal_value VARCHAR(128), external_value VARCHAR(256));

-- https://www.hydrovu.com/public-api/docs/index.html

-- Authorize > Friendly Name Lookup > Try it out > Execute

-- Parameter (case insensitive), Friendly Name (256 character max)

INSERT

 @qualifiers (internal_value, external_value)

VALUES

  ('1', 'Temperature')

,('2', 'Pressure')

,('3', 'Depth')

,('4', 'Level: Depth to Water')

,('5', 'Level: Elevation')

,('9', 'Actual Conductivity')

,('10', 'Specific Conductivity')

,('11', 'Resistivity')

,('12', 'Salinity')

,('13', 'Total Dissolved Solids')

,('14', 'Density')

,('16', 'Baro')

,('17', 'pH')

,('18', 'pH MV')

,('19', 'ORP')

,('20', 'DO')

,('21', '% Saturation O2')

,('22', 'NO3-')

,('23', 'NH4+')

,('24', 'Cl-')

,('25', 'Turbidity')

,('26', 'Battery Voltage')

,('28', 'Flow Rate')

,('29', 'Total Flow (volume)')

,('30', 'Partial Pressure O2')

,('31', 'Total Suspended Solids')

,('32', 'External Voltage')

,('33', 'Battery Level')

,('34', 'RWT Concentration')

,('35', 'RWT Fluorescence')

,('36', 'Cl- mV')

,('37', 'NO3- as N')

,('38', 'NO3- mV')

,('39', 'NH4+ as N')

,('40', 'NH4+ mV')

,('41', 'NH3 as N')

,('42', 'Sigma NH3')

,('49', 'Velocity')

,('50', 'Chl-a Concentration')

,('51', 'Chl-a Fluorescence')

,('54', 'BGA-PC Concentration')

,('55', 'BGA-PC Fluorescence')

,('58', 'BGA-PE Concentration')

,('59', 'BGA-PE Fluorescence')

,('flowVelocity', 'Flow Velocity')

,('density', 'Density')

,('concentration', 'Concentration');

INSERT INTO rt_remap_detail (external_field, external_value, internal_value, status_flag, remap_code)

SELECT @external_field, external_value, internal_value, 'A', @remap_code

FROM @qualifiers;

SELECT * FROM rt_remap_detail WHERE remap_code = @remap_code AND external_field = @external_field ORDER BY ISNUMERIC(internal_value) DESC, TRY_CAST(internal_value AS BIGINT), internal_value;

SET @external_field = 'unit';

DECLARE @units TABLE (internal_value VARCHAR(128), external_value VARCHAR(15));

-- Unit (case insensitive), Friendly Name (15 character max)

INSERT

 @units (internal_value, external_value)

VALUES

  ('1', 'C')

,('2', 'F')

,('17', 'psi')

,('19', 'kPa')

,('20', 'bar')

,('21', 'mbar')

,('22', 'mm Hg')

,('23', 'in Hg')

,('24', 'cm H2O')

,('26', 'torr')

,('27', 'atm')

,('33', 'mm')

,('34', 'cm')

,('35', 'm')

,('37', 'in')

,('38', 'ft')

,('65', 'uS/cm')

,('66', 'mS/cm')

,('81', 'ohm-cm')

,('97', 'psu')

,('113', 'ppm')

,('114', 'ppt')

,('117', 'mg/L')

,('118', 'ug/L')

,('120', 'g/L')

,('121', 'ppb')

,('129', 'g/cm3')

,('145', 'pH')

,('162', 'mV')

,('163', 'V')

,('177', '% sat')

,('193', 'FNU')

,('194', 'NTU')

,('209', 'ft3/s')

,('210', 'ft3/min')

,('211', 'ft3/hr')

,('212', 'ft3/day')

,('213', 'gal/s')

,('214', 'gal/min')

,('215', 'gal/hr')

,('216', 'Mgal/day')

,('217', 'm3/s')

,('218', 'm3/min')

,('219', 'm3/hr')

,('220', 'm3/day')

,('221', 'L/s')

,('222', 'ac-ft/d')

,('223', 'mL/min')

,('225', 'ft3')

,('226', 'gal')

,('227', 'Mgal')

,('228', 'm3')

,('230', 'ac-ft')

,('241', '%')

,('257', 'RFU')

,('274', 'mL/hr')

,('275', 'L/min')

,('276', 'L/hr')

,('337', 'ft/s')

,('338', 'm/s')

,('megaliters', 'ML')

,('yard', 'yd')

,('acreInchPerMinute', 'ac-in/min')

,('g/mL', 'g/mL')

,('acreFeetPerSecond', 'ac-ft/sec')

,('kilolitersPerMinute', 'kL/min')

,('milliter', 'mL')

,('litersPerDay', 'L/d')

,('megalitersPerDay', 'ML/d')

,('millilitersPerSecond', 'mL/s')

,('acreInchPerDay', 'ac-in/d')

,('megalitersPerMinute', 'ML/min')

,('acreInchPerHour', 'ac-in/hr')

,('liter', 'L')

,('acreFeetPerMinute', 'ac-ft/min')

,('kilolitersPerSecond', 'kL/sec')

,('acreInch', 'ac-in')

,('kilolitersPerDay', 'kL/d')

,('megalitersPerSecond', 'ML/sec')

,('megalitersPerHour', 'ML/hr')

,('kiloliters', 'kL')

,('kilolitersPerHour', 'kL/hr')

,('millilitersPerDay', 'mL/day')

,('acreInchPerSecond', 'ac-in/sec')

,('acreFeetPerHour', 'ac-ft/hr')

,('inH2o', 'in H2O');

INSERT INTO rt_remap_detail (external_field, external_value, internal_value, status_flag, remap_code)

SELECT @external_field, external_value, internal_value, 'A', @remap_code

FROM @units;

SELECT * FROM rt_remap_detail WHERE remap_code = @remap_code AND external_field = @external_field ORDER BY ISNUMERIC(internal_value) DESC, TRY_CAST(internal_value AS BIGINT), internal_value;