/* {"app": "dbt", "dbt_version": "1.0.0", "profile_name": "normalize", "target_name": "prod", "node_id": "model.airbyte_utils.genasish"} */ create index if not exists "6c42e7518c446df34576b0cdf867465e" on "genasis5".lims."genasish" using btree (_airbyte_emitted_at);

[ Date: 2025-11-28 00:06:12 - Database: genasis5 - User: genadmin_user - Remote: 10.16.50.5 - Application: dbt ]

/* {"app": "dbt", "dbt_version": "1.0.0", "profile_name": "normalize", "target_name": "prod", "node_id": "model.airbyte_utils.genasish"} */ create index if not exists "6c42e7518c446df34576b0cdf867465e" on "genasis5".lims."genasish" using btree (_airbyte_emitted_at);

[ Date: 2025-11-28 00:06:12 - Database: genasis5 - User: genadmin_user - Remote: 10.16.50.5 - Application: dbt ]

CREATE_REPLICATION_SLOT "pg_403623_sync_232682_7503782490379358197" LOGICAL pgoutput (SNAPSHOT 'use');

[ Date: 2025-11-28 09:32:33 - Database: data_collector - User: ismael - Remote: 10.16.50.12 - Application: pg_403623_sync_232682_7503782490379358197 ]

COPY core.value (id, sample_id, analysis_method_id, prepare_method_id, transformation_method_id, import_batch_id, value_xml, unit_id, original_unit_id, value_origin_type_id, recalc_id, parameter_id, acl_group_id, acl_owner_id, pghist_user_id, is_archived, updated_at, value, loq, lod) TO stdout;

[ Date: 2025-11-28 02:45:40 - Database: genasis4 - User: postgres - Remote: 127.0.0.1 - Application: pg_dump ]

COPY core.value (id, sample_id, analysis_method_id, prepare_method_id, transformation_method_id, import_batch_id, value_xml, unit_id, original_unit_id, value_origin_type_id, recalc_id, parameter_id, acl_group_id, acl_owner_id, pghist_user_id, is_archived, updated_at, value, loq, lod) TO stdout;

[ Date: 2025-11-28 01:01:43 - Database: genasis4 - User: postgres - Remote: 127.0.0.1 - Application: pg_dump ]

COPY core.value (id, sample_id, analysis_method_id, prepare_method_id, transformation_method_id, import_batch_id, unit_id, original_unit_id, value_origin_type_id, parameter_id, updated_at, value, loq, lod, created_at, created_by, updated_by, recalc_type) TO stdout;

[ Date: 2025-11-28 02:45:52 - Database: genasis5 - User: postgres - Remote: 127.0.0.1 - Application: pg_dump ]

COPY core.value (id, sample_id, analysis_method_id, prepare_method_id, transformation_method_id, import_batch_id, unit_id, original_unit_id, value_origin_type_id, parameter_id, updated_at, value, loq, lod, created_at, created_by, updated_by, recalc_type) TO stdout;

[ Date: 2025-11-28 01:01:55 - Database: genasis5 - User: postgres - Remote: 127.0.0.1 - Application: pg_dump ]

COPY air_sensor.data (id, document_context_id, date, inquire_household_id, temp, rh, co2, pm25, pm10, pm1, tvocs, ach, inquire_sensor_id) TO stdout;

[ Date: 2025-11-28 01:01:18 - Database: data_extractor - User: postgres - Remote: 127.0.0.1 - Application: pg_dump ]

COPY air_sensor.data (id, document_context_id, date, inquire_household_id, temp, rh, co2, pm25, pm10, pm1, tvocs, ach, inquire_sensor_id) TO stdout;

[ Date: 2025-11-28 02:45:15 - Database: data_extractor - User: postgres - Remote: 127.0.0.1 - Application: pg_dump ]

/* {"app": "dbt", "dbt_version": "1.0.0", "profile_name": "normalize", "target_name": "prod", "node_id": "model.airbyte_utils.genasish"} */ create table "genasis5".lims."genasish__dbt_tmp" as ( with __dbt__cte__genasish_ab1 as ( -- SQL model to parse JSON blob stored in a single column and extract into separated field columns as described by the JSON Schema -- depends_on: "genasis5".lims._airbyte_raw_genasish select jsonb_extract_path_text(_airbyte_data, 'STANOVIL' ) as stanovil, jsonb_extract_path_text(_airbyte_data, 'VZOREKID' ) as vzorekid, jsonb_extract_path_text(_airbyte_data, 'PARAMETRID' ) as parametrid, jsonb_extract_path_text(_airbyte_data, 'ANALYSISMETHODID' ) as analysismethodid, jsonb_extract_path_text(_airbyte_data, 'NEJISTOTA' ) as nejistota, jsonb_extract_path_text(_airbyte_data, 'DATUMSTAN' ) as datumstan, jsonb_extract_path_text(_airbyte_data, 'HODNOTA' ) as hodnota, jsonb_extract_path_text(_airbyte_data, 'PRIZNAK' ) as priznak, jsonb_extract_path_text(_airbyte_data, 'JEDNOTKA' ) as jednotka, jsonb_extract_path_text(_airbyte_data, 'LOQ' ) as loq, _airbyte_ab_id, _airbyte_emitted_at, now() as _airbyte_normalized_at from "genasis5".lims._airbyte_raw_genasish as table_alias -- genasish where 1 = 1), __dbt__cte__genasish_ab2 as ( -- SQL model to cast each column to its adequate SQL type converted from the JSON schema type -- depends_on: __dbt__cte__genasish_ab1 select cast(stanovil as text ) as stanovil, cast(vzorekid as float ) as vzorekid, cast(parametrid as text ) as parametrid, cast(analysismethodid as text ) as analysismethodid, cast(nejistota as text ) as nejistota, cast(datumstan as text ) as datumstan, cast(hodnota as float ) as hodnota, cast(priznak as text ) as priznak, cast(jednotka as text ) as jednotka, cast(loq as float ) as loq, _airbyte_ab_id, _airbyte_emitted_at, now() as _airbyte_normalized_at from __dbt__cte__genasish_ab1 -- genasish where 1 = 1), __dbt__cte__genasish_ab3 as ( -- SQL model to build a hash column based on the values of this record -- depends_on: __dbt__cte__genasish_ab2 select md5(cast(coalesce(cast(stanovil as text), '') || '-' || coalesce(cast(vzorekid as text), '') || '-' || coalesce(cast(parametrid as text), '') || '-' || coalesce(cast(analysismethodid as text), '') || '-' || coalesce(cast(nejistota as text), '') || '-' || coalesce(cast(datumstan as text), '') || '-' || coalesce(cast(hodnota as text), '') || '-' || coalesce(cast(priznak as text), '') || '-' || coalesce(cast(jednotka as text), '') || '-' || coalesce(cast(loq as text), '') as text) ) as _airbyte_genasish_hashid, tmp.* from __dbt__cte__genasish_ab2 tmp -- genasish where 1 = 1) -- Final base SQL model -- depends_on: __dbt__cte__genasish_ab3 select stanovil, vzorekid, parametrid, analysismethodid, nejistota, datumstan, hodnota, priznak, jednotka, loq, _airbyte_ab_id, _airbyte_emitted_at, now() as _airbyte_normalized_at, _airbyte_genasish_hashid from __dbt__cte__genasish_ab3 -- genasish from "genasis5".lims._airbyte_raw_genasish where 1 = 1 );

[ Date: 2025-11-28 00:06:11 - Database: genasis5 - User: genadmin_user - Remote: 10.16.50.5 - Application: dbt ]

COPY core.import_buffer (id, order_no, "row", pghist_user_id, is_archived, valid_result_type_id, resource_id) TO stdout;

[ Date: 2025-11-28 02:45:28 - Database: genasis4 - User: postgres - Remote: 127.0.0.1 - Application: pg_dump ]

COPY core.import_buffer (id, order_no, "row", pghist_user_id, is_archived, valid_result_type_id, resource_id) TO stdout;

[ Date: 2025-11-28 01:01:31 - Database: genasis4 - User: postgres - Remote: 127.0.0.1 - Application: pg_dump ]

SELECT "id", "sample_id", "analysis_method_id", "prepare_method_id", "transformation_method_id", "import_batch_id", "unit_id", "original_unit_id", "value_origin_type_id", "parameter_id", "updated_at", "value", "loq", "lod", "created_at", "created_by", "updated_by", "recalc_type" FROM "core"."value";

[ Date: 2025-11-28 22:15:40 - Database: genasis5 - User: genadmin_user - Remote: 10.16.50.5 - Application: PostgreSQL JDBC Driver - Bind query: yes ]

WITH sample_temperature as ( SELECT sa.id, sa.sampling_start_date::date, sa.sampling_end_date::date, si.temp_avg_01, si.temp_avg_02, si.temp_avg_03, si.temp_avg_04, si.temp_avg_05, si.temp_avg_06, si.temp_avg_07, si.temp_avg_08, si.temp_avg_09, si.temp_avg_10, si.temp_avg_11, si.temp_avg_12 FROM core.sample sa LEFT JOIN core.import_batch_sample ibs ON (sa.id = ibs.sample_id) LEFT JOIN core.site si ON (si.id = sa.site_id) LEFT JOIN core.material ma ON (ma.id = sa.material_id) WHERE ibs.import_batch_id = '1575' AND sa.id IN ( SELECT sample_id FROM core.value WHERE transformation_method_id = 10 /*trans.method none*/) AND ma.code = 'OUT*AT*PAS**' ), sampling_days as ( SELECT sample_temperature.*, generate_series(sampling_start_date, sampling_end_date, '1 day'::interval)::date as day FROM sample_temperature ), sampling_daily_temperatur as ( SELECT id, day, date_part('month', day) as month, case when date_part('month', day) = 1 then temp_avg_01 when date_part('month', day) = 2 then temp_avg_02 when date_part('month', day) = 3 then temp_avg_03 when date_part('month', day) = 4 then temp_avg_04 when date_part('month', day) = 5 then temp_avg_05 when date_part('month', day) = 6 then temp_avg_06 when date_part('month', day) = 7 then temp_avg_07 when date_part('month', day) = 8 then temp_avg_08 when date_part('month', day) = 9 then temp_avg_09 when date_part('month', day) = 10 then temp_avg_10 when date_part('month', day) = 11 then temp_avg_11 when date_part('month', day) = 12 then temp_avg_12 end as temp_avg FROM sampling_days ), sample_weighted_avarages as ( SELECT id, avg(temp_avg) FROM sampling_daily_temperatur GROUP BY id ), parameter_oldest_values_prepared as ( SELECT p.id, coalesce(parameter_history.code, p.code) as code FROM core.parameter p LEFT JOIN ( SELECT id, code FROM ( SELECT foreign_id as id, value_old as code, rank() OVER (PARTITION BY foreign_id ORDER BY id ASC) value_change_order FROM core.codelist_value_history cvh_pa WHERE cvh_pa.table_name = 'core.parameter') WHERE value_change_order = 1) parameter_history ON (p.id = parameter_history.id) ), value_with_multiplication_coefficient_prepared as ( SELECT v.id AS value_id, sa.id AS sample_id, public.f_genpastoact (1, sa.sampling_start_date::date, sa.sampling_end_date::date, povp.code, swa.avg) as coefficient FROM core.value v LEFT JOIN sample_weighted_avarages swa ON v.sample_id = swa.id LEFT JOIN core.sample sa ON v.sample_id = sa.id LEFT JOIN parameter_oldest_values_prepared povp ON v.parameter_id = povp.id WHERE v.import_batch_id = '1575' AND v.recalc_type IS NULL AND swa.avg IS NOT NULL ), value_with_multiplication_coefficient as ( SELECT value_id, sample_id, coefficient FROM value_with_multiplication_coefficient_prepared WHERE coefficient >= 0) INSERT INTO core.value (sample_id, import_batch_id, analysis_method_id, transformation_method_id, original_unit_id, unit_id, parameter_id, value_origin_type_id, prepare_method_id, recalc_type, created_by, updated_by, updated_at, created_at, value, loq, lod) SELECT vmc.sample_id, '1575' as import_batch_id, v.analysis_method_id, 14 as transformation_method_id, null as original_unit_id, ( SELECT id FROM core.unit WHERE code = left (u.code, strpos(u.code, '_') - 1) || '_M-3') as unit_id, v.parameter_id, 2 as value_origin_type_id, v.prepare_method_id, 'PASSIVE_ACTIVE' as recalc_type, current_setting('session.id_user')::int as created_by, current_setting('session.id_user')::int as updated_by, now() as updated_at, now() as created_at, significant_digits (v.value * coefficient, 3) as value, significant_digits (v.loq * coefficient, 3) as loq, v.lod FROM core.value v JOIN value_with_multiplication_coefficient vmc ON (v.id = vmc.value_id) JOIN core.unit u ON (v.unit_id = u.id);