SQL disease_progresses/patients

-- http://localhost:4000/api/v1/disease_progresses/patients
WITH "limited_assocs" AS (SELECT
    service_code,
    patient_id,
    assoc_type
FROM
    mv_connected_assocs_disease_progresses
WHERE performed_date BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999'), "limited_encounters" AS (SELECT
    patient_id,
    onset_date,
    prof_check
FROM mv_encounter_disease_progresses
WHERE onset_date BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') SELECT DISTINCT mv_stat_patient_personalities.id,
mv_stat_patient_personalities.full_name,
mv_stat_patient_personalities.employee_id,
mv_stat_patient_personalities.birth_date,
DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) as age,
"mv_stat_employees_parties"."DivisionId" as division_id,
  CASE
    WHEN (DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 40 AND mv_stat_patient_personalities.gender = 0 OR
          DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 50 AND mv_stat_patient_personalities.gender = 1)
    THEN CASE
           WHEN EXISTS (
                  SELECT 1 FROM limited_assocs WHERE
                    limited_assocs.patient_id = mv_stat_patient_personalities.id AND
                    limited_assocs.service_code in ('T34006','T34011','T34013','T34024','T340001','T34004') AND
                    limited_assocs.assoc_type in (0,1)
                )
                  THEN 2
                ELSE 1
    END ELSE 0
  END hypertension,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 45
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('T34005','T34023','T34025','T34038') AND
            limited_assocs.assoc_type in (0,1)
        ) OR EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.assoc_type in (2)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END diabetes,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 40 AND mv_stat_patient_personalities.gender = 0
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('U67002','Y34011','Y34003') AND
            limited_assocs.assoc_type in (0)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END prostate_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 50 AND 69 AND mv_stat_patient_personalities.gender = 1
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('TX41973','59300-00','55070-00','55076-00','Х41941') AND
            limited_assocs.assoc_type in (0)
      )
          THEN 2
        ELSE 1
    END ELSE 0
  END breast_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 50 AND 75 AND mv_stat_patient_personalities.gender = 0
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('D36003','D67006','32084-00','32090-00','32084-02','32090-02') AND
            limited_assocs.assoc_type in (0)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END colorectal_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 14
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('B33006','B33012') AND
            limited_assocs.assoc_type in (0,1)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END hiv,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 40 and 64)) as from_40_to_64_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    limited_encounters.prof_check = true AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 40 and 64)) as from_40_to_64_prof_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 65)) as upto_65_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    limited_encounters.prof_check = true AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 65)) AS upto_65_prof_visits FROM "mv_stat_patient_personalities" INNER JOIN "mv_stat_declarations" ON "mv_stat_declarations"."patient_id" = "mv_stat_patient_personalities"."id" AND ("mv_stat_declarations"."declaration_status_code" = 'ACTIVE') INNER JOIN "mv_stat_employees_parties" ON "mv_stat_employees_parties"."Id" = "mv_stat_patient_personalities"."employee_id" LEFT OUTER JOIN limited_assocs ON limited_assocs.patient_id = mv_stat_patient_personalities.id WHERE "mv_stat_patient_personalities"."id" IS NOT NULL AND ("mv_stat_employees_parties"."LegalEntityId" = 3228)
  ↳ app/services/disease_progresses/indicators_by_patient.rb:46:in `map'
  Views::MvStatEmployeesParty Load (113.5ms)  SELECT "mv_stat_employees_parties".* FROM "mv_stat_employees_parties" WHERE "mv_stat_employees_parties"."Id" = $1 LIMIT $2  [["Id", 180499], ["LIMIT", 1]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:46:in `map'
  CACHE Views::MvStatEmployeesParty Load (0.1ms)  SELECT "mv_stat_employees_parties".* FROM "mv_stat_employees_parties" WHERE "mv_stat_employees_parties"."Id" = $1 LIMIT $2  [["Id", 180499], ["LIMIT", 1]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:46:in `map'
  Views::MvStatEmployeesParty Load (21.3ms)  SELECT "mv_stat_employees_parties".* FROM "mv_stat_employees_parties" WHERE "mv_stat_employees_parties"."Id" = $1 LIMIT $2  [["Id", 181286], ["LIMIT", 1]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:46:in `map'
  CACHE Views::MvStatEmployeesParty Load (0.1ms)  SELECT "mv_stat_employees_parties".* FROM "mv_stat_employees_parties" WHERE "mv_stat_employees_parties"."Id" = $1 LIMIT $2  [["Id", 181286], ["LIMIT", 1]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:46:in `map'
  CACHE Views::MvStatEmployeesParty Load (0.1ms)  SELECT "mv_stat_employees_parties".* FROM "mv_stat_employees_parties" WHERE "mv_stat_employees_parties"."Id" = $1 LIMIT $2  [["Id", 181286], ["LIMIT", 1]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:46:in `map'
  Views::MvEncounterDiseaseProgress Load (23.9ms)  SELECT "mv_encounter_disease_progresses".* FROM "mv_encounter_disease_progresses" WHERE "mv_encounter_disease_progresses"."patient_id" = $1 AND "mv_encounter_disease_progresses"."onset_date" BETWEEN $2 AND $3  [["patient_id", 11395822], ["onset_date", "2024-01-01 00:00:00"], ["onset_date", "2024-02-02 23:59:59.999999"]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:143:in `build_diseases_percentage'
  Views::MvEncounterDiseaseProgress Load (29.0ms)  SELECT "mv_encounter_disease_progresses".* FROM "mv_encounter_disease_progresses" WHERE "mv_encounter_disease_progresses"."patient_id" = $1 AND "mv_encounter_disease_progresses"."onset_date" BETWEEN $2 AND $3  [["patient_id", 11395824], ["onset_date", "2024-01-01 00:00:00"], ["onset_date", "2024-02-02 23:59:59.999999"]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:143:in `build_diseases_percentage'
  Views::MvConnectedAssocsDiseaseProgress Load (24.7ms)  SELECT "mv_connected_assocs_disease_progresses".* FROM "mv_connected_assocs_disease_progresses" WHERE "mv_connected_assocs_disease_progresses"."patient_id" IN ($1, $2, $3, $4, $5) AND "mv_connected_assocs_disease_progresses"."performed_date" BETWEEN $6 AND $7  [["patient_id", 11395822], ["patient_id", 11395824], ["patient_id", 11464974], ["patient_id", 11464976], ["patient_id", 11464994], ["performed_date", "2024-01-01 00:00:00"], ["performed_date", "2024-02-02 23:59:59.999999"]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:68:in `filter'
  Views::MvEncounterDiseaseProgress Load (20.7ms)  SELECT "mv_encounter_disease_progresses".* FROM "mv_encounter_disease_progresses" WHERE "mv_encounter_disease_progresses"."patient_id" = $1 AND "mv_encounter_disease_progresses"."onset_date" BETWEEN $2 AND $3  [["patient_id", 11464974], ["onset_date", "2024-01-01 00:00:00"], ["onset_date", "2024-02-02 23:59:59.999999"]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:143:in `build_diseases_percentage'
  Views::MvEncounterDiseaseProgress Load (21.0ms)  SELECT "mv_encounter_disease_progresses".* FROM "mv_encounter_disease_progresses" WHERE "mv_encounter_disease_progresses"."patient_id" = $1 AND "mv_encounter_disease_progresses"."onset_date" BETWEEN $2 AND $3  [["patient_id", 11464976], ["onset_date", "2024-01-01 00:00:00"], ["onset_date", "2024-02-02 23:59:59.999999"]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:143:in `build_diseases_percentage'
  Views::MvEncounterDiseaseProgress Load (20.2ms)  SELECT "mv_encounter_disease_progresses".* FROM "mv_encounter_disease_progresses" WHERE "mv_encounter_disease_progresses"."patient_id" = $1 AND "mv_encounter_disease_progresses"."onset_date" BETWEEN $2 AND $3  [["patient_id", 11464994], ["onset_date", "2024-01-01 00:00:00"], ["onset_date", "2024-02-02 23:59:59.999999"]]
  ↳ app/services/disease_progresses/indicators_by_patient.rb:143:in `build_diseases_percentage'
Completed 200 OK in 588ms (Views: 0.9ms | ActiveRecord: 426.2ms | Allocations: 21048)


Started GET "/api/v1/disease_progresses/patients" for ::1 at 2024-02-02 11:00:16 +0200
Processing by Api::V1::DiseaseProgresses::PatientsController#index as HTML
  MainApplication::Dbo::LegalEntity Load (21.0ms)  SELECT "dbo"."LegalEntities".* FROM "dbo"."LegalEntities" WHERE "dbo"."LegalEntities"."Id" = $1 LIMIT $2  [["Id", 3228], ["LIMIT", 1]]
  ↳ app/controllers/api/v1/disease_progresses/patients_controller.rb:28:in `legal_entity'
  Views::MvStatPatientPersonality Load (31.5ms)  WITH "limited_assocs" AS (SELECT
    service_code,
    patient_id,
    assoc_type
FROM
    mv_connected_assocs_disease_progresses
WHERE performed_date BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999'), "limited_encounters" AS (SELECT
    patient_id,
    onset_date,
    prof_check
FROM mv_encounter_disease_progresses
WHERE onset_date BETWEEN '2024-01-01 00:00:00' AND '2024-02-02 23:59:59.999999') SELECT DISTINCT mv_stat_patient_personalities.id,
mv_stat_patient_personalities.full_name,
mv_stat_patient_personalities.employee_id,
mv_stat_patient_personalities.birth_date,
DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) as age,
"mv_stat_employees_parties"."DivisionId" as division_id,
  CASE
    WHEN (DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 40 AND mv_stat_patient_personalities.gender = 0 OR
          DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 50 AND mv_stat_patient_personalities.gender = 1)
    THEN CASE
           WHEN EXISTS (
                  SELECT 1 FROM limited_assocs WHERE
                    limited_assocs.patient_id = mv_stat_patient_personalities.id AND
                    limited_assocs.service_code in ('T34006','T34011','T34013','T34024','T340001','T34004') AND
                    limited_assocs.assoc_type in (0,1)
                )
                  THEN 2
                ELSE 1
    END ELSE 0
  END hypertension,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 45
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('T34005','T34023','T34025','T34038') AND
            limited_assocs.assoc_type in (0,1)
        ) OR EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.assoc_type in (2)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END diabetes,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 40 AND mv_stat_patient_personalities.gender = 0
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('U67002','Y34011','Y34003') AND
            limited_assocs.assoc_type in (0)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END prostate_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 50 AND 69 AND mv_stat_patient_personalities.gender = 1
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('TX41973','59300-00','55070-00','55076-00','Х41941') AND
            limited_assocs.assoc_type in (0)
      )
          THEN 2
        ELSE 1
    END ELSE 0
  END breast_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 50 AND 75 AND mv_stat_patient_personalities.gender = 0
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('D36003','D67006','32084-00','32090-00','32084-02','32090-02') AND
            limited_assocs.assoc_type in (0)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END colorectal_cancer,
  CASE
    WHEN DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 14
      THEN CASE
        WHEN EXISTS (
          SELECT 1 FROM limited_assocs WHERE
            limited_assocs.patient_id = mv_stat_patient_personalities.id AND
            limited_assocs.service_code in ('B33006','B33012') AND
            limited_assocs.assoc_type in (0,1)
      )
        THEN 2
      ELSE 1
    END ELSE 0
  END hiv,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 40 and 64)) as from_40_to_64_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    limited_encounters.prof_check = true AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) BETWEEN 40 and 64)) as from_40_to_64_prof_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 65)) as upto_65_visits,
  (EXISTS (SELECT 1 FROM limited_encounters WHERE
    limited_assocs.patient_id = limited_encounters.patient_id AND
    limited_encounters.prof_check = true AND
    DATE_PART('YEAR', AGE('2024-02-02 23:59:59.999999', mv_stat_patient_personalities.birth_date)) >= 65)) AS upto_65_prof_visits FROM "mv_stat_patient_personalities" INNER JOIN "mv_stat_declarations" ON "mv_stat_declarations"."patient_id" = "mv_stat_patient_personalities"."id" AND ("mv_stat_declarations"."declaration_status_code" = 'ACTIVE') INNER JOIN "mv_stat_employees_parties" ON "mv_stat_employees_parties"."Id" = "mv_stat_patient_personalities"."employee_id" LEFT OUTER JOIN limited_assocs ON limited_assocs.patient_id = mv_stat_patient_personalities.id WHERE "mv_stat_patient_personalities"."id" IS NOT NULL AND ("mv_stat_employees_parties"."LegalEntityId" = 3228)


SELECT "mv_encounter_disease_progresses".* FROM "mv_encounter_disease_progresses" WHERE "mv_encounter_disease_progresses"."patient_id" = $1 AND "mv_encounter_disease_progresses"."onset_date" BETWEEN $2 AND $3  [["patient_id", 11395822], ["onset_date", "2024-01-01 00:00:00"], ["onset_date", "2024-02-02 23:59:59.999999"]]

SELECT "mv_connected_assocs_disease_progresses".* FROM "mv_connected_assocs_disease_progresses" WHERE "mv_connected_assocs_disease_progresses"."patient_id" IN ($1, $2, $3, $4, $5) AND "mv_connected_assocs_disease_progresses"."performed_date" BETWEEN $6 AND $7  [["patient_id", 11395822], ["patient_id", 11395824], ["patient_id", 11464974], ["patient_id", 11464976], ["patient_id", 11464994], ["performed_date", "2024-01-01 00:00:00"], ["performed_date", "2024-02-02 23:59:59.999999"]]

SELECT "mv_encounter_disease_progresses".* FROM "mv_encounter_disease_progresses" WHERE "mv_encounter_disease_progresses"."patient_id" = $1 AND "mv_encounter_disease_progresses"."onset_date" BETWEEN $2 AND $3  [["patient_id", 11464974], ["onset_date", "2024-01-01 00:00:00"], ["onset_date", "2024-02-02 23:59:59.999999"]]