DECLARE @remap_code VARCHAR(20) = 'airmonitors';

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) = 'qualifier';

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

-- https://api.airmonitors.net/3.5/GET/[AccountId]/[LicenceKey]/flags

-- Qualifier (case insensitive), Abbreviation (4 character max)

INSERT

 @qualifiers (internal_value, external_value)

VALUES

  ('Below Level of Detection', 'BLD')

,('Greater Than Upper Limit', 'GTUL')

,('Invalid', 'INV')

,('Less Than Lower Limit', 'LTLL')

,('No Data', 'ND')

,('Optimising', 'OPT')

,('Rebasing', 'REB')

,('Stabilizing', 'STB')

,('Valid', NULL);

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 internal_value;

SET @external_field = 'unit';

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

-- https://api.airmonitors.net/3.5/GET/[AccountId]/[LicenceKey]/units

-- Unit (case insensitive), Abbreviation (15 character max)

INSERT

 @units (internal_value, external_value)

VALUES

  ('ATM', 'atm')

,('Becquerel', 'Bq')

,('Becquerel Per Meter Cubed', 'Bq/m3')

,('CC Per Minute', 'cc/min')

,('Cell Pressure', 'torr')

,('Cell Temperature', 'K')

,('Celsius', 'C')

,('Centigrade', 'C')

,('Counts per minute', 'cpm')

,('Cubic Meters', 'm3')

,('Cubic Meters Per Hour', 'm3/h')

,('Decibels', 'dB')

,('Degrees', 'Deg')

,('Derived Air Concentration Per Hour', 'DAC-h')

,('Digit', NULL)

,('Dose Area Product per Hour', 'mGy/h')

,('Fahrenheit', 'F')

,('Feet', 'ft')

,('GKG', NULL)

,('grammes per m3', 'g/m3')

,('HCMHR', NULL)

,('Hectopascals', 'hPa')

,('Hertz', 'Hz')

,('Inch', 'In')

,('inch m3', 'In/m3')

,('Kelvin', 'K')

,('KG/M3', 'kg/m3')

,('Kilometres Per Second', 'kps')

,('Kilopascals', 'kpa')

,('KiloWattHour', 'kWHr')

,('KJKG', NULL)

,('Knots', 'kn')

,('Litres Per Minute', 'l/min')

,('LM2', NULL)

,('LM2H', NULL)

,('Meters Per Second', 'm/s')

,('Metres', 'm')

,('Micro Seconds', 'uSecs')

,('Micrograms', 'ug')

,('Micrograms per cm2', 'mg/cm2')

,('Micrograms Per Cubic Meter', 'ug/m3')

,('Miles Per Hour', 'mph')

,('Milimeters', 'mm')

,('Milli Amps', 'mA')

,('Milligrams Per Cubic Meter', 'mg/m3')

,('Millileters Per Minute', 'ml/min')

,('Millimetres Of Mercury', 'mmHg')

,('Millimetres Per Hour', 'mm/h')

,('Millisievert per Hour', 'mSv/h')

,('Millivolts', 'mV')

,('Minutes', 'mins')

,('MM/DAY', 'mm/day')

,('Nano Amps', 'nA')

,('nanogramme per meter cubed', 'ng/m3')

,('None', NULL)

,('Number', NULL)

,('Particles per cm3', 'P/cm3')

,('Parts Per Billion', 'ppb')

,('Parts Per Million', 'ppm')

,('Per Day', '/day')

,('Per Hour', '/h')

,('Percent', '%')

,('Percent Of Time', '%time')

,('Pressure (bar)', 'bar')

,('Pressure (mbar)', 'mbar')

,('Pressure (psi)', 'psi')

,('Quantity', 'Qty')

,('Revolutions Per Minute', 'RPM')

,('Seconds', 's')

,('Torr', 'torr')

,('UM2CM3', 'um2/cm3')

,('Volts', 'V')

,('WM2', 'W/m2');

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 internal_value;