dule.bahmnicore-omod.1.2.0.source-code.BahmniBMPatientListInWard.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of bahmnicore-omod Show documentation
The newest version!
DELETE FROM global_property where property = 'bedManagement.sqlGet.patientListForAdmissionLocation';
INSERT INTO global_property (`property`, `property_value`, `description`, `uuid`)
VALUES ('bedManagement.sqlGet.patientListForAdmissionLocation',
'SELECT
  b.bed_number AS "Bed",
  concat(pn.given_name," ", ifnull(pn.family_name,"")) AS "Name",
  pi.identifier AS "Id",
  pv.gender AS "Gender",
  TIMESTAMPDIFF(YEAR, pv.birthdate, CURDATE()) AS "Age",
  admission_provider_name.given_name AS "Admission By",
  cast(DATE_FORMAT(latestAdmissionEncounter.max_encounter_datetime, "%d %b %y %h:%i %p") AS CHAR) AS "Admission Time",
  adtNotes.value_text AS "ADT Notes",
  dispositionInfo.providerName AS "Disposition By",
  cast(DATE_FORMAT(dispositionInfo.providerDate, "%d %b %y %h:%i %p") AS CHAR) AS "Disposition Time",
  diagnosis.diagnosisConcept AS "Diagnosis",
  diagnosis.certainty AS "Diagnosis Certainty",
  diagnosis.diagnosisOrder AS "Diagnosis Order",
  diagnosis.status AS "Diagnosis Status",
  diagnosis.diagnosis_provider AS "Diagnosis Provider",
  cast(DATE_FORMAT(diagnosis.diagnosis_datetime, "%d %b %y %h:%i %p") AS CHAR) AS "Diagnosis Datetime"
FROM bed_location_map blm
  INNER JOIN bed b
    ON blm.bed_id = b.bed_id AND b.status = "OCCUPIED"
  INNER JOIN location l on blm.location_id = l.location_id and l.name = ${location_name}
  INNER JOIN bed_patient_assignment_map bpam ON b.bed_id = bpam.bed_id AND date_stopped IS NULL
  INNER JOIN person pv ON pv.person_id = bpam.patient_id
  INNER JOIN person_name pn ON pn.person_id = pv.person_id
  INNER JOIN patient_identifier pi ON pv.person_id = pi.patient_id
  INNER JOIN patient_identifier_type pit on pi.identifier_type = pit.patient_identifier_type_id
  INNER JOIN global_property gp on gp.property="bahmni.primaryIdentifierType" and gp.property_value=pit.uuid
  INNER JOIN (
        SELECT patient_id, max(encounter_datetime) AS max_encounter_datetime,
          max(visit_id) as visit_id, max(encounter_id) AS encounter_id
        FROM encounter INNER JOIN encounter_type ON encounter_type.encounter_type_id = encounter.encounter_type
        WHERE encounter_type.name = "ADMISSION"
        GROUP BY patient_id) latestAdmissionEncounter
      ON pv.person_id = latestAdmissionEncounter.patient_id
  LEFT OUTER JOIN obs adtNotes
    ON adtNotes.encounter_id = latestAdmissionEncounter.encounter_id
    AND adtNotes.voided = 0
    AND adtNotes.concept_id = (SELECT concept_id
                              FROM concept_name
                              WHERE name = "Adt Notes" AND concept_name_type = "FULLY_SPECIFIED" AND locale = "en")
  LEFT OUTER JOIN encounter_provider ep ON ep.encounter_id = latestAdmissionEncounter.encounter_id
  LEFT OUTER JOIN provider admission_provider ON admission_provider.provider_id = ep.provider_id
  LEFT OUTER JOIN person_name admission_provider_name
    ON admission_provider_name.person_id = admission_provider.person_id
  LEFT OUTER JOIN (
        SELECT
          bpam.patient_id AS person_id,
          concept_name.name AS disposition,
          latestDisposition.obs_datetime AS providerDate,
          person_name.given_name AS providerName
        FROM bed_patient_assignment_map bpam
          INNER JOIN (SELECT person_id, max(obs_id) obs_id
                      FROM obs WHERE concept_id = (SELECT concept_id
                          FROM concept_name
                          WHERE name = "Disposition" AND concept_name_type = "FULLY_SPECIFIED" AND locale = "en")
                      GROUP BY person_id)
                maxObsId ON maxObsId.person_id = bpam.patient_id
          INNER JOIN obs latestDisposition
            ON maxObsId.obs_id = latestDisposition.obs_id AND latestDisposition.voided = 0
          INNER JOIN concept_name ON latestDisposition.value_coded = concept_name.concept_id AND
                                     concept_name_type = "FULLY_SPECIFIED"
          LEFT OUTER JOIN encounter_provider ep ON latestDisposition.encounter_id = ep.encounter_id
          LEFT OUTER JOIN provider disp_provider ON disp_provider.provider_id = ep.provider_id
          LEFT OUTER JOIN person_name ON person_name.person_id = disp_provider.person_id
        WHERE bpam.date_stopped IS NULL
    ) dispositionInfo ON pv.person_id = dispositionInfo.person_id
  LEFT OUTER JOIN (
    SELECT
      diagnosis.person_id AS person_id,
      diagnosis.obs_id AS obs_id,
      diagnosis.obs_datetime AS diagnosis_datetime,
      if(diagnosisConceptName.name IS NOT NULL, diagnosisConceptName.name, diagnosis.value_text) AS diagnosisConcept,
      certaintyConceptName.name AS certainty,
      diagnosisOrderConceptName.name AS diagnosisOrder,
      diagnosisStatusConceptName.name AS status,
      person_name.given_name AS diagnosis_provider
    FROM bed_patient_assignment_map bpam
      INNER JOIN visit latestVisit
        ON latestVisit.patient_id = bpam.patient_id AND latestVisit.date_stopped IS NULL
        AND bpam.date_stopped IS NULL
      INNER JOIN encounter ON encounter.visit_id = latestVisit.visit_id
      INNER JOIN obs diagnosis ON bpam.patient_id = diagnosis.person_id AND diagnosis.voided = 0 AND
          diagnosis.encounter_id = encounter.encounter_id AND
          diagnosis.concept_id IN (
            SELECT concept_id FROM concept_name
            WHERE name IN ("Coded Diagnosis", "Non-Coded Diagnosis")
              AND concept_name_type = "FULLY_SPECIFIED" )
      LEFT OUTER JOIN concept_name diagnosisConceptName
        ON diagnosis.value_coded IS NOT NULL
        AND diagnosis.value_coded = diagnosisConceptName.concept_id
        AND diagnosisConceptName.concept_name_type = "FULLY_SPECIFIED"
        AND diagnosisConceptName.locale ="en"
      LEFT OUTER JOIN encounter_provider ep ON diagnosis.encounter_id = ep.encounter_id
      LEFT OUTER JOIN provider diagnosis_provider ON diagnosis_provider.provider_id = ep.provider_id
      LEFT OUTER JOIN person_name ON person_name.person_id = diagnosis_provider.person_id
      INNER JOIN obs certainty
        ON diagnosis.obs_group_id = certainty.obs_group_id AND certainty.voided = 0
        AND certainty.concept_id = (
          SELECT concept_id FROM concept_name
          WHERE name = "Diagnosis Certainty"
          AND concept_name_type = "FULLY_SPECIFIED"
          AND locale = "en")
      LEFT OUTER JOIN concept_name certaintyConceptName
        ON certainty.value_coded IS NOT NULL
        AND certainty.value_coded = certaintyConceptName.concept_id
        AND certaintyConceptName.concept_name_type = "FULLY_SPECIFIED"
        AND certaintyConceptName.locale = "en"
      INNER JOIN obs diagnosisOrder
        ON diagnosis.obs_group_id = diagnosisOrder.obs_group_id AND diagnosisOrder.voided = 0
        AND diagnosisOrder.concept_id = (
          SELECT concept_id FROM concept_name
          WHERE name = "Diagnosis order" AND concept_name_type = "FULLY_SPECIFIED" and locale ="en")
      LEFT OUTER JOIN concept_name diagnosisOrderConceptName
        ON diagnosisOrder.value_coded IS NOT NULL
        AND diagnosisOrder.value_coded = diagnosisOrderConceptName.concept_id
        AND diagnosisOrderConceptName.concept_name_type = "FULLY_SPECIFIED"
        AND diagnosisOrderConceptName.locale = "en"
      LEFT JOIN obs diagnosisStatus
        ON diagnosis.obs_group_id = diagnosisStatus.obs_group_id AND diagnosisStatus.voided = 0
        AND diagnosisStatus.concept_id = (
          SELECT concept_id FROM concept_name
          WHERE name = "Bahmni Diagnosis Status"
          AND concept_name_type = "FULLY_SPECIFIED"
          AND locale = "en")
      LEFT OUTER JOIN concept_name diagnosisStatusConceptName
        ON diagnosisStatus.value_coded IS NOT NULL
        AND diagnosisStatus.value_coded = diagnosisStatusConceptName.concept_id
        AND diagnosisStatusConceptName.concept_name_type = "FULLY_SPECIFIED"
        AND diagnosisStatusConceptName.locale = "en"
) diagnosis ON diagnosis.person_id = pv.person_id',
'Sql query to get admitted patients details in an Admission Location',
uuid());