insert into sequpdate_state(updateid,uid) values('new view v_export_serie_csv',82);

-- public.v_export_serie_csv source

CREATE OR REPLACE VIEW public.v_export_serie_csv
AS SELECT to_char(study.date, 'DD/MM/YYYY'::text) AS studydate_fmt,
    study.date AS studydate,
    study.description_auto AS studydescription,
    study.mstud_id_auto,
    serie.mserd_id_auto,
    serie.description_auto AS seriedescription,
    serie.acquisition_type,
    patient.name AS patientname,
    f_get_age_classification(EXTRACT(year FROM age(study.date, patient.birthdate::timestamp without time zone))::integer) AS classification,
    to_char(patient.birthdate::timestamp with time zone, 'DD/MM/YYYY'::text) AS birthdate,
    EXTRACT(year FROM age(study.date, patient.birthdate::timestamp without time zone)) AS age,
    replace(f_get_imc(study.pat_size, study.pat_weight), '.'::text, ','::text) AS imc,
    replace(NULLIF(study.pat_size, '0'::double precision)::text, '.'::text, ','::text) AS height,
    replace(study.pat_weight::text, '.'::text, ','::text) AS weight,
    v_modality_config.name AS modality,
    v_modality_config.mod_id,
    study.performingphysician AS radiologist,
    study.operator AS technologist,
    replace(COALESCE(round((serie.val ->> '79'::text)::numeric, 2)::text, round(((serie.val ->> '30'::text)::numeric) * ((serie.val ->> '101'::text)::numeric) / (1000::numeric * COALESCE(NULLIF((serie.val ->> '94'::text)::numeric, 0::numeric), 1::numeric)), 2)::text, ''::text), '.'::text, ','::text) AS mas,
    replace(round(NULLIF(serie.val ->> '23'::text, ''::text)::numeric, 2)::text, '.'::text, ','::text) AS kv,
    replace(round(serie.ctdivol_auto::numeric, 2)::text, '.'::text, ','::text) AS ctdivol,
    replace(round(((serie.val ->> '28'::text)::numeric) / 10::numeric, 2)::text, '.'::text, ','::text) AS spirale_length,
    replace(round(serie.dlp_auto::numeric, 2)::text, '.'::text, ','::text) AS pdl,
    replace(round(serie.dap_auto::numeric, 2)::text, '.'::text, ','::text) AS pds,
    replace(round(serie.dgm_auto::numeric, 2)::text, '.'::text, ','::text) AS dgm,
    rsd.description AS original_study_description,
    rsd2.description AS original_serie_description,
    serie.protocolname AS protocol_name,
    ''::text AS ccam,
    patient.sex,
    patient.id AS patient_id,
    study.accessionnumber AS "Accession Number",
    replace(round((serie.val ->> '73'::text)::numeric, 2)::text, '.'::text, ','::text) AS compression_force,
    replace(round((serie.val ->> '46'::text)::numeric, 2)::text, '.'::text, ','::text) AS breast_thickness,
    replace(COALESCE(serie.val ->> '45'::text, "left"(serie.val ->> '44'::text, 1) || (serie.val ->> '75acr'::text)), '.'::text, ','::text) AS incidence,
    (COALESCE(serie.val ->> '74'::text, ''::text) || '/'::text) || COALESCE(serie.val ->> '72'::text, ''::text) AS anode_filter,
    serie.val ->> '100'::text AS "Image comments",
    ''::text AS "Micro dose",
    replace(round((serie.val ->> '32'::text)::numeric, 2)::text, '.'::text, ','::text) AS exposure_time,
    replace(round((serie.val ->> '30'::text)::numeric, 2)::text, '.'::text, ','::text) AS ma,
    replace(round((serie.val ->> '94'::text)::numeric, 2)::text, '.'::text, ','::text) AS pitch,
    replace(serie.val ->> '65'::text, '.'::text, ','::text) AS acquisition_plane,
    ''::text AS opening,
    replace(round((serie.val ->> '66'::text)::numeric, 2)::text, '.'::text, ','::text) AS air_kerma,
    study.comments_on_radiation_dose AS study_comment,
    ''::text AS "Commentaire 2",
    ''::text AS "Commentaire 3",
    replace(round((serie.val ->> '68'::text)::numeric, 2)::text, '.'::text, ','::text) AS "Primary Angle(°)",
    replace(round((serie.val ->> '69'::text)::numeric, 2)::text, '.'::text, ','::text) AS "Primary Angle End(°)",
    replace(round((serie.val ->> '90'::text)::numeric, 2)::text, '.'::text, ','::text) AS "Secondary Angle(°)",
    replace(round((serie.val ->> '92'::text)::numeric, 2)::text, '.'::text, ','::text) AS "Secondary Angle End(°)",
    replace(round((serie.val ->> '63'::text)::numeric, 2)::text, '.'::text, ','::text) AS "Distance foyer detecteur(mm)",
    replace(round((serie.val ->> '112'::text)::numeric, 2)::text, '.'::text, ','::text) AS "Longueur de champs(mm)",
    replace(round((serie.val ->> '114'::text)::numeric, 2)::text, '.'::text, ','::text) AS "Largeur de champs(mm)",
    replace(round((serie.val ->> '116'::text)::numeric, 2)::text, '.'::text, ','::text) AS "Aire de champs(dm²)",
    replace(round(COALESCE(
        CASE
            WHEN NULLIF((serie.val ->> '124'::text)::numeric, 0::numeric) IS NOT NULL THEN ((serie.val ->> '125'::text)::numeric) / ((serie.val ->> '124'::text)::numeric)
            ELSE NULL::numeric
        END, ((serie.val ->> '127'::text)::numeric) / 1000::numeric), 2)::text, '.'::text, ','::text) AS scopie_duration,
    replace(round((serie.val ->> '133'::text)::numeric, 2)::text, '.'::text, ','::text) AS max_filtration_thickness,
    replace(round((serie.val ->> '76'::text)::numeric, 2)::text, '.'::text, ','::text) AS dose_rp,
    NULL::text AS nrd
   FROM study,
    patient,
    v_modality_config,
    raw_study_description rsd,
    serie,
    raw_serie_description rsd2,
    raw_serie_description_link rsdl2
  WHERE rsd.rstud_id = study.rstud_id AND rsdl2.selected = true AND rsd2.rserd_id = rsdl2.rserd_id AND rsdl2.ser_id = serie.ser_id AND study.stu_id = serie.stu_id AND patient.pat_id = study.pat_id AND study.pro_id = v_modality_config.pro_id;

