Ward Dashboard SQL Override

class nh_eobs_mental_health.sql.ward_dashboard.WardDashboardSQL(pool, cr)

get_last_finished_obs_stop()

get_ward_dashboard()

get_ward_dashboard_bed_count()

get_ward_dashboard_capacity_count()

get_ward_dashboard_obs_stop_count()

get_ward_dashboard_on_ward_count()

get_ward_dashboard_reason_count(*args, **kwargs)

get_ward_dashboard_reason_view()

get_ward_dashboard_refused_obs_count()

get_ward_dashboard_workload()

get_wardboard(interval)

Override wardboard SQL view to include acuity index :param interval: Time interval used for recently transferred / discharged :return: SQL statement used in nh_eobs.init()

last_finished_obs_stop_for_spell_skeleton = "\n SELECT\n activity.id AS id,\n spell.activity_id AS spell_activity_id,\n spell.id AS spell_id,\n activity.date_terminated AS activity_date_terminated,\n activity.state as activity_state\n FROM nh_clinical_spell AS spell\n JOIN (\n SELECT DISTINCT ON (spell_activity_id) * FROM nh_activity\n WHERE data_model = 'nh.clinical.pme.obs_stop'\n AND state = ANY('{cancelled, completed}')\n ORDER BY spell_activity_id, date_terminated DESC\n ) AS activity\n ON activity.spell_activity_id = spell.activity_id\n "

ward_dashboard_bed_count_skeleton = "\n SELECT\n ward_locations.ward_id AS location_id,\n count(loc.id)\n FROM nh_clinical_location AS loc\n INNER JOIN wdb_ward_locations AS ward_locations\n ON ward_locations.id = loc.id\n WHERE loc.usage = 'bed'\n GROUP BY ward_locations.ward_id\n "

ward_dashboard_capacity_skeleton = '\n SELECT\n bed_count.location_id,\n (bed_count.count - workload.count) AS count\n FROM wdb_bed_count AS bed_count\n INNER JOIN wdb_workload_count AS workload\n ON bed_count.location_id = workload.location_id\n '

ward_dashboard_obs_stop_skeleton = "\n SELECT\n ward_locations.ward_id AS location_id,\n SUM(CASE WHEN spell.obs_stop = 't' THEN 1 ELSE 0 END) AS count\n FROM nh_clinical_spell AS spell\n INNER JOIN nh_clinical_location AS location\n ON location.id = spell.location_id AND location.usage = 'bed'\n INNER JOIN wdb_ward_locations AS ward_locations\n ON ward_locations.id = location.id\n GROUP BY ward_locations.ward_id"

ward_dashboard_on_ward_skeleton = '\n SELECT\n avail.location_id,\n (avail.patients_in_bed - obs_stop.count) AS count\n FROM loc_availability AS avail\n INNER JOIN wdb_obs_stop_count AS obs_stop\n ON avail.location_id = obs_stop.location_id\n '

ward_dashboard_reason_count_skeleton = '\n select * from wdb_reasons where reason = {reason}'

ward_dashboard_reason_view_skeleton = "\n SELECT\n ward_locations.ward_id AS location_id,\n count(spell.patient_id),\n obs_stop.reason\n FROM nh_clinical_spell AS spell\n LEFT JOIN nh_clinical_location AS location\n ON spell.location_id = location.id\n AND location.usage = 'bed'\n LEFT JOIN wdb_ward_locations AS ward_locations\n ON location.id = ward_locations.id\n LEFT JOIN nh_clinical_pme_obs_stop AS obs_stop\n ON spell.id = obs_stop.spell\n LEFT JOIN nh_activity AS activity\n ON obs_stop.activity_id = activity.id\n AND activity.state = 'started'\n WHERE obs_stop.reason IS NOT NULL\n AND activity.state IS NOT NULL\n GROUP BY ward_locations.ward_id, obs_stop.reason"

ward_dashboard_refused_obs_count_skeleton = '\n SELECT ward_beds.location_id,\n coalesce(\n sum(\n CASE WHEN spell.refusing_obs = TRUE THEN 1 ELSE 0 END),0)\n AS count\n FROM nh_clinical_spell AS spell\n LEFT JOIN ward_beds\n ON spell.location_id = ANY(bed_ids)\n GROUP BY ward_beds.location_id\n '

ward_dashboard_skeleton = "\n -- Create Ward Dashboard\n SELECT\n location.id AS id,\n location.id AS location_id,\n lwp.waiting_patients,\n avail.patients_in_bed,\n avail.free_beds,\n clu1.related_users AS related_hcas,\n clu2.related_users AS related_nurses,\n clu3.related_users AS related_doctors,\n rpc.high_risk_patients,\n rpc.med_risk_patients,\n rpc.low_risk_patients,\n rpc.no_risk_patients,\n rpc.noscore_patients,\n CASE\n WHEN rpc.high_risk_patients > 0 THEN 2\n WHEN rpc.med_risk_patients > 0 THEN 3\n WHEN rpc.low_risk_patients > 0 THEN 4\n WHEN rpc.no_risk_patients > 0 THEN 0\n WHEN rpc.noscore_patients > 0 THEN 7\n ELSE 7\n END AS kanban_color,\n awol.count AS awol_count,\n acute_ed.count AS acute_hospital_ed_count,\n extended_leave.count AS extended_leave_count,\n capacity.count AS capacity_count,\n workload.count AS workload_count,\n on_ward.count AS on_ward_count,\n refused_obs.count AS refused_obs_count\n FROM nh_clinical_location AS location\n LEFT JOIN loc_waiting_patients AS lwp\n ON lwp.location_id = location.id\n LEFT JOIN wdb_awol_count AS awol ON awol.location_id = location.id\n LEFT JOIN wdb_acute_hospital_ed_count AS acute_ed\n ON acute_ed.location_id = location.id\n LEFT JOIN wdb_extended_leave_count AS extended_leave\n ON extended_leave.location_id = location.id\n LEFT JOIN wdb_capacity_count AS capacity\n ON capacity.location_id = location.id\n LEFT JOIN wdb_workload_count AS workload\n ON workload.location_id = location.id\n LEFT JOIN wdb_on_ward_count AS on_ward\n ON on_ward.location_id = location.id\n LEFT JOIN wdb_refused_obs_count AS refused_obs\n ON refused_obs.location_id = location.id\n LEFT JOIN loc_availability AS avail\n ON avail.location_id = location.id\n LEFT JOIN child_loc_users AS clu1\n ON clu1.location_id = location.id\n AND clu1.group_name = 'NH Clinical HCA Group'\n LEFT JOIN child_loc_users AS clu2\n ON clu2.location_id = location.id\n AND clu2.group_name = 'NH Clinical Nurse Group'\n LEFT JOIN child_loc_users AS clu3\n ON clu3.location_id = location.id\n AND clu3.group_name = 'NH Clinical Doctor Group'\n LEFT JOIN loc_risk_patients_count AS rpc\n ON rpc.location_id = location.id\n WHERE location.usage = 'ward'\n "

ward_dashboard_workload_skeleton = '\n SELECT\n ward_locations.ward_id AS location_id,\n count(spell.patient_id)\n FROM nh_clinical_spell AS spell\n INNER JOIN nh_clinical_location AS location\n ON location.id = spell.location_id\n INNER JOIN wdb_ward_locations AS ward_locations\n ON ward_locations.id = location.id\n GROUP BY ward_locations.ward_id'