ALL VIEWS

-- SQL disease_progresses

-- mv_encounter_disease_progresses
SELECT receptions.id,
       receptions.patient_id,
       receptions.onset_date,
       CASE
           WHEN (EXISTS (SELECT 1
                         FROM ehr_reasons
                                  JOIN classification_icpc2_items ON ehr_reasons.encounter_id = receptions.id AND
                                                                     ehr_reasons.icpc2_reason_id =
                                                                     classification_icpc2_items.id AND
                                                                     classification_icpc2_items.code::text =
                                                                     'A98'::text)) AND (EXISTS (SELECT 1
                                                                                                FROM ehr_observations
                                                                                                         JOIN classification_fhir_observation_codes
                                                                                                              ON classification_fhir_observation_codes.id =
                                                                                                                 ehr_observations.codeable_id AND
                                                                                                                 (classification_fhir_observation_codes.code::text = ANY
                                                                                                                  (ARRAY ['39156-5'::character varying::text, '14743-9'::character varying::text, '8462-4'::character varying::text, '8480-6'::character varying::text]))))
               THEN true
           ELSE false
           END AS prof_check
FROM receptions
         JOIN user_patients ON user_patients.id = receptions.patient_id
         JOIN personalities
              ON personalities.id = user_patients.personality_id AND personalities.type::text = 'Patient::Person'::text
         JOIN declarations ON declarations.personality_id = personalities.id AND
                              declarations.declaration_status_id = ((SELECT declaration_statuses.id
                                                                     FROM declaration_statuses
                                                                     WHERE declaration_statuses.code::text = 'ACTIVE'::text))



-- mv_connected_assocs_disease_progresses
SELECT combined_data.id,
       combined_data.patient_id,
       combined_data.service_code,
       combined_data.performed_date,
       combined_data.assoc_type
FROM (SELECT ehr_direction_service_requests.id,
             ehr_direction_service_requests.patient_id,
             ehr_ehealth_services.code                          AS service_code,
             ehr_direction_service_requests.ehealth_inserted_at AS performed_date,
             0                                                  AS assoc_type
      FROM ehr_direction_service_requests
               JOIN ehr_ehealth_services
                    ON ehr_ehealth_services.id = ehr_direction_service_requests.service_requestable_id AND
                       ehr_direction_service_requests.service_requestable_type::text = 'Ehr::Ehealth::Service'::text AND
                       (ehr_direction_service_requests.status::text = ANY
                        (ARRAY ['active'::character varying::text, 'completed'::character varying::text]))
      UNION
      SELECT ehr_diagnostic_reports.id,
             ehr_diagnostic_reports.patient_id,
             ehr_ehealth_services.code     AS service_code,
             ehr_diagnostic_reports.issued AS perfomed_date,
             1                             AS assoc_type
      FROM ehr_diagnostic_reports
               JOIN ehr_ehealth_services ON ehr_diagnostic_reports.service_id = ehr_ehealth_services.id AND
                                            ehr_diagnostic_reports.ehealth_status = 1
      UNION
      SELECT ehr_observations.id,
             ehr_observations.patient_id,
             NULL::character varying AS service_code,
             ehr_observations.issued AS performed_date,
             2                       AS assoc_type
      FROM ehr_observations
               JOIN classification_fhir_observation_codes
                    ON classification_fhir_observation_codes.id = ehr_observations.codeable_id AND
                       classification_fhir_observation_codes.code::text = '14743-9'::text) combined_data
         JOIN user_patients ON user_patients.id = combined_data.patient_id
         JOIN personalities
              ON personalities.id = user_patients.personality_id AND personalities.type::text = 'Patient::Person'::text
         JOIN declarations ON declarations.personality_id = personalities.id AND
                              declarations.declaration_status_id = ((SELECT declaration_statuses.id
                                                                     FROM declaration_statuses
                                                                     WHERE declaration_statuses.code::text = 'ACTIVE'::text))


-- SQL chronic diseases
-- mv_stat_conditions

SELECT ehr_conditions.encounter_id,
       ehr_conditions.patient_id,
       ehr_conditions.employee_id,
       classification_icpc2_items.code,
       classification_icpc2_items.title_translations -> 'uk'::text AS label,
       receptions.asserted_date
FROM ehr_conditions
         JOIN receptions ON receptions.id = ehr_conditions.encounter_id AND receptions.ehealth_status = 1
         JOIN classification_icpc2_items
              ON classification_icpc2_items.type::text = 'Classification::Icpc2::Condition'::text AND
                 classification_icpc2_items.id = ehr_conditions.icpc2_code_id
WHERE ehr_conditions.employee_id IS NOT NULL
  AND ((classification_icpc2_items.code::text = ANY
        (ARRAY ['T90'::character varying, 'K85'::character varying, 'K86'::character varying, 'K87'::character varying, 'R96'::character varying]::text[])) OR
       classification_icpc2_items.code::text ~~ 'P%'::text)

-- mv_stat_imt_diseases
        SELECT DISTINCT ON (ehr_observations.id, ehr_observations.patient_id, classification_fhir_observation_codes.code)
          'ІМТ' as label,
          ehr_observations.encounter_id,
          ehr_observations.employee_id,
          ehr_observations.patient_id,
          classification_fhir_observation_codes.code,
          receptions.asserted_date,
          CASE WHEN classification_fhir_observation_codes.code = '39156-5' THEN
            NULLIF(REGEXP_REPLACE(ehr_observations.surveillance_value ->>'value','[^0-9.]+','','g')::text, '')::float
          ELSE
            COALESCE(NULLIF(REGEXP_REPLACE(ehr_observations.surveillance_value ->>'value','[^0-9.]+','','g')::text, '')::float, 1::float) / POWER((COALESCE(NULLIF(REGEXP_REPLACE(imt_table.observation_value, '[^0-9.]+', '', 'g')::text,'')::numeric, 1::numeric))::float * 0.01::float, 2)
          END AS imt_index
        FROM "ehr_observations"
          INNER JOIN classification_fhir_observation_codes ON classification_fhir_observation_codes."id" = ehr_observations."codeable_id" AND classification_fhir_observation_codes.code IN ('39156-5', '29463-7')
          INNER JOIN "classification_fhir_items" ON "classification_fhir_items"."id" = "classification_fhir_observation_codes"."quantity_unit_id" AND "classification_fhir_items"."type" IN ('Classification::Fhir::ValueSet::UcumUnit')
          INNER JOIN receptions ON receptions.id = ehr_observations.encounter_id AND receptions.ehealth_status = 1
          LEFT OUTER JOIN (SELECT ehr_observations.id,
                            ehr_observations.patient_id,
                            classification_fhir_observation_codes.title_translations -> 'uk' as observation_title,
                            ehr_observations.surveillance_value ->> 'value'                  AS "observation_value",
                            classification_fhir_items.code                                   as unit_code
                          FROM ehr_observations
                            INNER JOIN classification_fhir_observation_codes ON classification_fhir_observation_codes."id" = ehr_observations."codeable_id" AND classification_fhir_observation_codes.code = '8302-2'
                            INNER JOIN "classification_fhir_items" ON "classification_fhir_items"."id" = "classification_fhir_observation_codes"."quantity_unit_id" AND "classification_fhir_items"."type" IN ('Classification::Fhir::ValueSet::UcumUnit') ORDER BY ehr_observations.id DESC) AS imt_table
                          ON imt_table.patient_id = ehr_observations.patient_id AND classification_fhir_observation_codes.code = '29463-7'
          WHERE ehr_observations.id IS NOT NULL AND ehr_observations.employee_id IS NOT NULL AND nullif(trim(ehr_observations.surveillance_value ->> 'value'), '') IS NOT NULL
          AND (classification_fhir_observation_codes.code = '39156-5' AND (COALESCE(NULLIF(REGEXP_REPLACE(ehr_observations.surveillance_value ->>'value', '[^0-9.]+', '', 'g')::text, '')::NUMERIC, 1::NUMERIC)::float > 25::float) OR (classification_fhir_observation_codes.code IN ('29463-7') AND imt_table.patient_id IS NOT NULL AND COALESCE(NULLIF(REGEXP_REPLACE(imt_table.observation_value, '[^0-9.]+', '', 'g')::text, '')::NUMERIC, 0::NUMERIC) > 0::NUMERIC AND COALESCE(NULLIF(REGEXP_REPLACE(ehr_observations.surveillance_value ->> 'value', '[^0-9.]+', '', 'g')::text, '')::float, 1::float) / POWER((COALESCE(NULLIF(REGEXP_REPLACE(imt_table.observation_value, '[^0-9.]+', '', 'g')::text, '')::numeric, 1::numeric))::float * 0.01::float, 2) > 25::float))



-- mv_stat_receptions_conditions
SELECT receptions.id,
       receptions.legal_entity_id,
       receptions.patient_id,
       medication_prescriptions.ehealth_status AS medication_prescriptions_ehealth_status,
       medication_prescriptions.program_id     AS prescription_program_id,
       classification_icpc2_items.id           AS icpc2_code_id
FROM receptions
         JOIN ehr_conditions ON ehr_conditions.encounter_id = receptions.id
         LEFT JOIN medication_prescriptions ON medication_prescriptions.encounter_id = receptions.id AND
                                               (medication_prescriptions.ehealth_status = ANY (ARRAY [0, 1]))
         JOIN classification_icpc2_items ON classification_icpc2_items.id = ehr_conditions.icpc2_code_id AND
                                            classification_icpc2_items.type::text =
                                            'Classification::Icpc2::Condition'::text AND
                                            classification_icpc2_items.code::text >= 'K29'::text AND
                                            classification_icpc2_items.code::text <= 'K99'::text
WHERE receptions.ehealth_status = 1

-- mv_stat_declarations
SELECT DISTINCT ON (declarations.id) declarations.id,
                                     declarations.employee_id,
                                     declarations.personality_id,
                                     declarations.division_id,
                                     declarations.start_date,
                                     declarations.end_date,
                                     "Parties"."LegalEntityId" AS legal_entity_id,
                                     user_patients.id          AS patient_id,
                                     personalities.birth_date,
                                     declaration_statuses.code AS declaration_status_code,
                                     receptions.asserted_date
FROM declarations
         JOIN dbo."Employees" ON "Employees"."Id" = declarations.employee_id
         JOIN declaration_statuses ON declaration_statuses.id = declarations.declaration_status_id
         JOIN dbo."Parties" ON "Parties"."Id" = "Employees"."PartyId"
         JOIN personalities
              ON personalities.id = declarations.personality_id AND personalities.type::text = 'Patient::Person'::text
         JOIN user_patients ON user_patients.personality_id = personalities.id
         LEFT JOIN receptions ON receptions.patient_id = user_patients.id AND receptions.ehealth_status = 1
WHERE declaration_statuses.code::text = ANY
      (ARRAY ['ACTIVE'::character varying::text, 'COMPLETED'::character varying::text])
ORDER BY declarations.id, receptions.asserted_date DESC


-- mv_stat_patient_personalities
SELECT user_patients.id,
       personalities.id                                                                                   AS personalities_id,
       declarations.id                                                                                    AS declaration_id,
       declarations.employee_id,
       personalities.gender,
       user_patients.is_smoking,
       personalities.birth_date,
       EXTRACT(year FROM age(personalities.birth_date::timestamp with time zone))                         AS age,
       concat_ws(' '::text, personalities.last_name,
                 personalities.first_name)                                                                AS original_name,
       concat_ws(' '::text, personalities.last_name, personalities.first_name, personalities.second_name) AS full_name
FROM user_patients
         JOIN personalities ON user_patients.personality_id = personalities.id
         JOIN declarations ON declarations.personality_id = personalities.id
         JOIN declaration_statuses ON declaration_statuses.id = declarations.declaration_status_id
WHERE declaration_statuses.code::text = 'ACTIVE'::text
  AND personalities.type::text = 'Patient::Person'::text


-- TOTALS --

-- mv_stat_legal_entity_total_completed_service_requests
SELECT ehr_direction_service_requests.legal_entity_id,
       count(ehr_direction_service_requests.id) AS counter
FROM ehr_direction_service_requests
WHERE ehr_direction_service_requests.status::text = 'completed'::text
  AND ehr_direction_service_requests.category_code::text = 'counselling'::text
GROUP BY ehr_direction_service_requests.legal_entity_id


-- mv_stat_legal_entity_total_reception_conditions
SELECT receptions.legal_entity_id,
       count(DISTINCT receptions.patient_id) AS counter
FROM receptions
         JOIN ehr_conditions ON ehr_conditions.encounter_id = receptions.id
         JOIN classification_icpc2_items ON classification_icpc2_items.id = ehr_conditions.icpc2_code_id AND
                                            classification_icpc2_items.type::text =
                                            'Classification::Icpc2::Condition'::text AND
                                            classification_icpc2_items.code::text >= 'K29'::text AND
                                            classification_icpc2_items.code::text <= 'K99'::text
WHERE receptions.ehealth_status = 1
GROUP BY receptions.legal_entity_id

-- mv_stat_legal_entity_total_receptions
SELECT receptions.legal_entity_id,
       count(receptions.id) AS counter
FROM receptions
WHERE receptions.ehealth_status = 1
GROUP BY receptions.legal_entity_id

-- mv_stat_legal_entity_total_service_requests
SELECT ehr_direction_service_requests.legal_entity_id,
       count(ehr_direction_service_requests.id) AS counter
FROM ehr_direction_service_requests
WHERE (ehr_direction_service_requests.status::text = ANY
       (ARRAY ['completed'::character varying::text, 'active'::character varying::text]))
  AND ehr_direction_service_requests.category_code::text = 'counselling'::text
GROUP BY ehr_direction_service_requests.legal_entity_id