{
  "sourceSeriesId": [
      32101231
  ],
  "frequency": "mm",
  "dateOffset": -60,
  "value": "AVG(?)",
  "exportSqlProcedure": "equis.copy_logger_datum_to_result_temp_c"
}

IF OBJECT_ID(N'[equis].[copy_logger_datum_to_result_temp_c]') IS NOT NULL

DROP PROCEDURE [equis].[copy_logger_datum_to_result_temp_c];

GO

CREATE PROCEDURE [equis].[copy_logger_datum_to_result_temp_c]

(

 @deriver_logger_series_id BIGINT

,@deriver_start_date DATETIME2(0)

,@deriver_end_date DATETIME2(0)

,@deriver_update_yn CHAR

)

AS

BEGIN

EXEC equis.copy_logger_datum_to_result @deriver_logger_series_id, @deriver_start_date, @deriver_end_date, NULL, NULL, 'B', 'AIR', 'FIELD', 'N', 'NA', 'INITIAL', 'TEMP-C', 'TRG', 'DEG C';

END

IF OBJECT_ID(N'[equis].[copy_logger_datum_to_result]') IS NOT NULL

DROP PROCEDURE [equis].[copy_logger_datum_to_result];

GO

CREATE PROCEDURE [equis].[copy_logger_datum_to_result]

(

  @logger_series_id BIGINT -- dt_logger_datum, PK, FK (dt_logger_series), NOT NULL

,@start_date DATETIME2(0) -- dt_logger_datum.datum_utc_dt, PK, NOT NULL

,@end_date DATETIME2(0) -- dt_logger_datum.datum_utc_dt, PK, NOT NULL

,@facility_id BIGINT -- dt_sample/dt_test/dt_result, PK, FK (dt_facility), NOT NULL

,@sys_loc_code VARCHAR(20) -- dt_sample, FK (dt_location), NULL

,@sample_type_code VARCHAR(20) -- dt_sample, FK (rt_sample_type), NULL

,@matrix_code VARCHAR(10) -- dt_sample/dt_test.lab_matrix_code, FK (rt_matrix), NULL

,@analytic_method VARCHAR(20) -- dt_test, FK (rt_analytic_method), NOT NULL

,@fraction VARCHAR(10) -- dt_test, FK (rt_fraction), NULL

,@column_number VARCHAR(2) -- dt_test, NULL

,@test_type VARCHAR(10) -- dt_test, FK (rt_test_type), NULL

,@cas_rn VARCHAR(15) -- dt_result, PK, FK (rt_analyte), NOT NULL

,@result_type_code VARCHAR(10) -- dt_result, FK (rt_result_type), NOT NULL

,@result_unit VARCHAR(15) -- dt_result, FK (rt_unit.unit_code), NULL

)

AS

BEGIN

IF @facility_id IS NULL

BEGIN

  SELECT @facility_id = COALESCE(s.facility_id, l.facility_id)

  FROM dt_logger_series s

  INNER JOIN dt_logger l ON l.logger_id = s.logger_id

  WHERE s.logger_series_id = @logger_series_id;

END

IF @sys_loc_code IS NULL

BEGIN

  SELECT @sys_loc_code = COALESCE(s.sys_loc_code, l.sys_loc_code)

  FROM dt_logger_series s

  INNER JOIN dt_logger l ON l.logger_id = s.logger_id

  WHERE s.logger_series_id = @logger_series_id;

END

DECLARE @datum TABLE

(

    datum_utc_dt DATETIME2(0)

  ,datum_value REAL

  ,sys_sample_code VARCHAR(40)

  ,sample_id BIGINT

  ,test_id BIGINT

);

-- e.g., (datum_utc_dt, datum_value, sys_sample_code) = ('2001-02-03 04:05:06', 1.23, '123456,2001-02-03T04:05:06')

INSERT INTO @datum (datum_utc_dt, datum_value, sys_sample_code)

SELECT datum_utc_dt, datum_value, CONVERT(VARCHAR, logger_series_id) + ',' + CONVERT(VARCHAR, datum_utc_dt, 126)

FROM dt_logger_datum

WHERE logger_series_id = @logger_series_id AND datum_utc_dt BETWEEN @start_date AND @end_date;

-- dt_sample

UPDATE d

SET sample_id = s.sample_id

FROM @datum d

INNER JOIN dt_sample s ON s.sys_sample_code = d.sys_sample_code;

DECLARE @new_id_count BIGINT;

DECLARE @new_id_start BIGINT;

DECLARE @id BIGINT;

SELECT @new_id_count = COUNT(*) FROM @datum WHERE sample_id IS NULL;

IF @new_id_count > 0

BEGIN

  EXEC equis.get_ids @new_id_count, @new_id_start OUTPUT;

  SET @id = @new_id_start - 1;

  UPDATE @datum

  SET sample_id = @id, @id = @id + 1

  WHERE sample_id IS NULL;

  INSERT INTO dt_sample (facility_id, sample_id, sys_sample_code, sys_loc_code, sample_type_code, sample_date, matrix_code)

  SELECT @facility_id, d.sample_id, d.sys_sample_code, @sys_loc_code, @sample_type_code, d.datum_utc_dt, @matrix_code

  FROM @datum d

  WHERE d.sample_id BETWEEN @new_id_start AND @new_id_start + @new_id_count - 1;

END

-- dt_test

UPDATE d

SET test_id = t.test_id

FROM @datum d

INNER JOIN dt_test t ON t.sample_id = d.sample_id;

SELECT @new_id_count = COUNT(*) FROM @datum WHERE test_id IS NULL;

IF @new_id_count > 0

BEGIN

  EXEC equis.get_ids @new_id_count, @new_id_start OUTPUT;

  SET @id = @new_id_start - 1;

  UPDATE @datum

  SET test_id = @id, @id = @id + 1

  WHERE test_id IS NULL;

  INSERT INTO dt_test (facility_id, test_id, sample_id, analytic_method, analysis_date, fraction, column_number, test_type, lab_matrix_code)

  SELECT @facility_id, d.test_id, d.sample_id, @analytic_method, d.datum_utc_dt, @fraction, @column_number, @test_type, @matrix_code

  FROM @datum d

  WHERE d.test_id BETWEEN @new_id_start AND @new_id_start + @new_id_count - 1;

END

-- dt_result

MERGE INTO dt_result r

USING @datum d

ON r.facility_id = @facility_id AND r.test_id = d.test_id AND r.cas_rn = @cas_rn

WHEN MATCHED THEN

  UPDATE SET r.result_numeric = d.datum_value, r.result_type_code = @result_type_code, r.result_unit = @result_unit

WHEN NOT MATCHED THEN

  INSERT (facility_id, test_id, cas_rn, result_numeric, result_type_code, result_unit)

  VALUES (@facility_id, d.test_id, @cas_rn, d.datum_value, @result_type_code, @result_unit);

END