SQL Overrides

class sql_statements.NHEobsSQL(pool, cr)

WORKLOAD_BUCKET_EXTRACT = '^(\\d+)[-|+]{1}(\\d+)?\\sminutes\\s([late|remain]+)$'

collect_activities_skeleton = "\n select distinct activity.id,\n activity.summary,\n patient.id as patient_id,\n ews1.clinical_risk,\n case\n when activity.date_scheduled is not null then\n activity.date_scheduled::text\n when activity.create_date is not null then\n activity.create_date::text\n else ''\n end as deadline,\n case\n when activity.date_scheduled is not null then\n case when greatest(\n now() at time zone 'UTC', activity.date_scheduled) !=\n activity.date_scheduled\n then 'overdue: ' else '' end ||\n case when extract(days from (greatest(\n now() at time zone 'UTC', activity.date_scheduled) -\n least(now() at time zone 'UTC',\n activity.date_scheduled))) > 0\n then extract(days from (greatest(\n now() at time zone 'UTC', activity.date_scheduled) -\n least(now() at time zone 'UTC', activity.date_scheduled)\n )) || ' day(s) '\n else '' end ||\n to_char(justify_hours(greatest(now() at time zone 'UTC',\n activity.date_scheduled) - least(now() at time zone 'UTC',\n activity.date_scheduled)), 'HH24:MI') || ' hours'\n when activity.create_date is not null then\n case when greatest(now() at time zone 'UTC',\n activity.create_date) != activity.create_date\n then 'overdue: ' else '' end ||\n case when extract(days from (greatest(now() at time zone\n 'UTC', activity.create_date) - least(now() at time zone\n 'UTC', activity.create_date))) > 0\n then extract(days from (greatest(now() at time zone 'UTC',\n activity.create_date) - least(now() at time zone 'UTC',\n activity.create_date))) || ' day(s) '\n else '' end ||\n to_char(justify_hours(greatest(now() at time zone 'UTC',\n activity.create_date) - least(now() at time zone 'UTC',\n activity.create_date)), 'HH24:MI') || ' hours'\n else to_char((interval '0s'), 'HH24:MI') || ' hours'\n end as deadline_time,\n coalesce(patient.family_name, '') || ', ' ||\n coalesce(patient.given_name, '') || ' ' ||\n coalesce(patient.middle_names,'') as full_name,\n location.name as location,\n location_parent.name as parent_location,\n case\n when ews1.score is not null then ews1.score::text\n else ''\n end as ews_score,\n case\n when ews1.id is not null and ews2.id is not null and\n (ews1.score - ews2.score) = 0 then 'same'\n when ews1.id is not null and ews2.id is not null and\n (ews1.score - ews2.score) > 0 then 'up'\n when ews1.id is not null and ews2.id is not null and\n (ews1.score - ews2.score) < 0 then 'down'\n when ews1.id is null and ews2.id is null then 'none'\n when ews1.id is not null and ews2.id is null then 'first'\n when ews1.id is null and ews2.id is not null then 'no latest'\n end as ews_trend,\n case\n when position('notification' in activity.data_model)::bool\n then true\n else false\n end as notification\n from nh_activity activity\n inner join nh_activity spell_activity\n on spell_activity.id = activity.parent_id\n inner join nh_clinical_patient patient\n on patient.id = activity.patient_id\n inner join nh_clinical_location location\n on location.id = spell_activity.location_id\n inner join nh_clinical_location location_parent\n on location_parent.id = location.parent_id\n left join ews1 on ews1.spell_activity_id = spell_activity.id\n left join ews2 on ews2.spell_activity_id = spell_activity.id\n where activity.id in ({activity_ids})\n and spell_activity.state = 'started'\n order by deadline asc, activity.id desc\n "

collect_patients_skeleton = "\n select distinct activity.id,\n patient.id,\n patient.dob,\n patient.gender,\n patient.sex,\n patient.other_identifier,\n case char_length(patient.patient_identifier) = 10\n when true then substring(patient.patient_identifier from 1\n for 3) || ' ' || substring(patient.patient_identifier from 4\n for 3) || ' ' || substring(patient.patient_identifier from 7\n for 4)\n else patient.patient_identifier\n end as patient_identifier,\n coalesce(patient.family_name, '') || ', ' ||\n coalesce(patient.given_name, '') || ' ' ||\n coalesce(patient.middle_names,'') as full_name,\n case\n when ews0.date_scheduled is not null then\n case when greatest(now() at time zone 'UTC',\n ews0.date_scheduled) != ews0.date_scheduled\n then 'overdue: ' else '' end ||\n case when extract(days from (greatest(now() at time zone\n 'UTC', ews0.date_scheduled) - least(now() at time zone\n 'UTC', ews0.date_scheduled))) > 0\n then extract(days from (greatest(now() at time zone 'UTC',\n ews0.date_scheduled) - least(now() at time zone 'UTC',\n ews0.date_scheduled))) || ' day(s) '\n else '' end ||\n to_char(justify_hours(greatest(now() at time zone 'UTC',\n ews0.date_scheduled) - least(now() at time zone 'UTC',\n ews0.date_scheduled)), 'HH24:MI') || ' hours'\n else to_char((interval '0s'), 'HH24:MI') || ' hours'\n end as next_ews_time,\n location.name as location,\n location_parent.name as parent_location,\n case\n when ews1.score is not null then ews1.score::text\n else ''\n end as ews_score,\n ews1.clinical_risk,\n case\n when ews1.id is not null and ews2.id is not null and\n (ews1.score - ews2.score) = 0 then 'same'\n when ews1.id is not null and ews2.id is not null and\n (ews1.score - ews2.score) > 0 then 'up'\n when ews1.id is not null and ews2.id is not null and\n (ews1.score - ews2.score) < 0 then 'down'\n when ews1.id is null and ews2.id is null then 'none'\n when ews1.id is not null and ews2.id is null then 'first'\n when ews1.id is null and ews2.id is not null then 'no latest'\n end as ews_trend,\n case\n when ews0.frequency is not null then ews0.frequency\n else 0\n end as frequency\n from nh_activity activity\n inner join nh_clinical_patient patient\n on patient.id = activity.patient_id\n inner join nh_clinical_location location\n on location.id = activity.location_id\n inner join nh_clinical_location location_parent\n on location_parent.id = location.parent_id\n left join ews1 on ews1.spell_activity_id = activity.id\n left join ews2 on ews2.spell_activity_id = activity.id\n left join ews0 on ews0.spell_activity_id = activity.id\n where activity.state = 'started' and activity.data_model =\n 'nh.clinical.spell' and activity.id in ({spell_ids})\n order by location\n "

discharge_transfer_skeleton = "\n SELECT\n activity.spell_id AS spell_id,\n array_agg(DISTINCT users.id) AS user_ids,\n array_agg(DISTINCT users2.id) AS ward_user_ids\n FROM wb_spell_ranked activity\n INNER JOIN wb_{table}_ranked {table}\n ON {table}.parent_id = activity.id\n AND {table}.rank = 1 AND {table}.state = 'completed'\n INNER JOIN nh_clinical_patient_{table} {table}_data\n ON {table}_data.id = {table}.data_id\n INNER JOIN nh_clinical_location location\n ON location.id = {table}_data.{location_row}\n INNER JOIN ward_locations wl ON wl.id = location.id\n LEFT JOIN user_location_rel ulrel ON ulrel.location_id = location.id\n LEFT JOIN res_users users ON users.id = ulrel.user_id\n LEFT JOIN user_location_rel ulrel2 ON ulrel2.location_id = wl.ward_id\n LEFT JOIN res_users users2 ON users2.id = ulrel2.user_id\n WHERE now() AT TIME ZONE 'UTC' - {table}.date_terminated <\n INTERVAL '{time}'\n AND activity.rank = 1 AND activity.state = '{state}'\n GROUP BY activity.spell_id"

generate_workload_cases(workload)

get_collect_activities_sql(activity_ids_sql)

get_collect_patients_sql(spell_ids)

get_discharge_transfer_sql(table, location_row, interval, state)

get_last_discharge_users(interval)

get_last_transfer_users(interval)

get_wardboard(interval)

get_wb_transfer_ranked_sql()

get_workload(settings)

wardboard_skeleton = "\n SELECT DISTINCT\n spell.id AS id,\n spell.patient_id AS patient_id,\n spell_activity.id AS spell_activity_id,\n spell_activity.date_started AS spell_date_started,\n spell_activity.date_terminated AS spell_date_terminated,\n spell.pos_id,\n spell.code AS spell_code,\n spell_activity.state AS spell_state,\n CASE\n WHEN extract(days FROM justify_hours(now() AT TIME ZONE 'UTC'\n - spell_activity.date_started)) > 0 THEN extract(days FROM\n justify_hours(now() AT TIME ZONE 'UTC' -\n spell_activity.date_started)) || ' day(s) '\n ELSE ''\n END || to_char(justify_hours(now() AT TIME ZONE 'UTC' -\n spell_activity.date_started), 'HH24:MI') time_since_admission,\n spell.move_date,\n patient.family_name,\n patient.given_name,\n patient.middle_names,\n CASE\n WHEN patient.given_name IS NULL THEN ''\n ELSE upper(substring(patient.given_name FROM 1 FOR 1))\n END AS initial,\n coalesce(patient.family_name, '') || ', ' ||\n coalesce(patient.given_name, '') || ' ' ||\n coalesce(patient.middle_names,'') AS full_name,\n location.name AS location,\n location.id AS location_id,\n wlocation.ward_id AS ward_id,\n patient.sex,\n patient.dob,\n patient.other_identifier AS hospital_number,\n CASE char_length(patient.patient_identifier) = 10\n WHEN TRUE THEN substring(patient.patient_identifier FROM 1 FOR 3)\n || ' ' || substring(patient.patient_identifier FROM 4 FOR 3) ||\n ' ' || substring(patient.patient_identifier FROM 7 FOR 4)\n ELSE patient.patient_identifier\n END AS nhs_number,\n extract(year FROM age(now(), patient.dob)) AS age,\n ews0.next_diff_polarity ||\n CASE\n WHEN ews0.date_scheduled IS NOT NULL THEN\n CASE WHEN extract(days FROM (greatest(now() AT TIME ZONE 'UTC',\n ews0.date_scheduled) - least(now() AT TIME ZONE 'UTC',\n ews0.date_scheduled))) > 0\n THEN extract(days FROM (greatest(now() AT TIME ZONE 'UTC',\n ews0.date_scheduled) - least(now() AT TIME ZONE 'UTC',\n ews0.date_scheduled))) || ' day(s) '\n ELSE '' END ||\n to_char(justify_hours(greatest(now() AT TIME ZONE 'UTC',\n ews0.date_scheduled) - least(now() AT TIME ZONE 'UTC',\n ews0.date_scheduled)), 'HH24:MI') || ' hours'\n ELSE to_char((INTERVAL '0s'), 'HH24:MI')\n END AS next_diff,\n CASE ews0.frequency < 60\n WHEN true THEN ews0.frequency || ' min(s)'\n ELSE ews0.frequency/60 || ' hour(s) ' || ews0.frequency -\n ews0.frequency/60*60 || ' min(s)'\n END AS frequency,\n ews0.date_scheduled,\n CASE WHEN ews1.id IS NULL THEN 'none' ELSE ews1.score::text END\n AS ews_score_string,\n ews1.score AS ews_score,\n CASE\n WHEN ews1.id IS NOT NULL AND ews2.id IS NOT NULL\n AND (ews1.score - ews2.score) = 0 THEN 'same'\n WHEN ews1.id IS NOT NULL AND ews2.id IS NOT NULL\n AND (ews1.score - ews2.score) > 0 THEN 'up'\n WHEN ews1.id IS NOT NULL AND ews2.id IS NOT NULL\n AND (ews1.score - ews2.score) < 0 THEN 'down'\n WHEN ews1.id IS NULL AND ews2.id IS NULL THEN 'none'\n WHEN ews1.id IS NOT NULL AND ews2.id IS NULL THEN 'first'\n WHEN ews1.id IS NULL AND ews2.id IS NOT NULL THEN 'no latest'\n END AS ews_trend_string,\n CASE WHEN ews1.id IS NULL THEN 'NoScore' ELSE ews1.clinical_risk END\n AS clinical_risk,\n ews1.score - ews2.score AS ews_trend,\n param.height,\n param.o2target_level_id AS o2target,\n CASE WHEN param.mrsa THEN 'yes' ELSE 'no' END AS mrsa,\n CASE WHEN param.diabetes THEN 'yes' ELSE 'no' END AS diabetes,\n CASE WHEN pbp.status THEN 'yes' ELSE 'no' END AS pbp_monitoring,\n CASE WHEN param.status THEN 'yes' ELSE 'no' END AS palliative_care,\n CASE\n WHEN param.post_surgery AND param.post_surgery_date > now() -\n INTERVAL '4h' THEN 'yes'\n ELSE 'no'\n END AS post_surgery,\n CASE\n WHEN param.critical_care AND param.critical_care_date > now() -\n INTERVAL '24h' THEN 'yes'\n ELSE 'no'\n END AS critical_care,\n param.uotarget_vol,\n param.uotarget_unit,\n consulting_doctors.names AS consultant_names,\n CASE\n WHEN spell_activity.date_terminated > now() - INTERVAL '{time}'\n AND spell_activity.state = 'completed' THEN true\n ELSE false\n END AS recently_discharged\n\n FROM nh_clinical_spell spell\n INNER JOIN nh_activity spell_activity\n ON spell_activity.id = spell.activity_id\n INNER JOIN nh_clinical_patient patient ON spell.patient_id = patient.id\n LEFT JOIN nh_clinical_location location ON location.id = spell.location_id\n LEFT JOIN ews1 ON spell.id = ews1.spell_id\n LEFT JOIN ews2 ON spell.id = ews2.spell_id\n LEFT JOIN ews0 ON spell.id = ews0.spell_id\n LEFT JOIN ward_locations wlocation ON wlocation.id = location.id\n LEFT JOIN consulting_doctors ON consulting_doctors.spell_id = spell.id\n LEFT JOIN pbp pbp ON pbp.spell_id = spell.id\n LEFT JOIN param ON param.spell_id = spell.id\n ORDER BY location.name"

wb_transfer_ranked_skeleton = "\n select *\n from (\n select\n spell.id as spell_id,\n activity.*,\n split_part(activity.data_ref, ',', 2)::int as data_id,\n rank() over (partition by spell.id, activity.data_model,\n activity.state order by activity.sequence desc)\n from nh_clinical_spell spell\n inner join nh_activity activity\n on activity.spell_activity_id = spell.activity_id\n and activity.data_model = 'nh.clinical.patient.transfer') sub_query\n where rank = 1\n "

workload_skeleton = "\n WITH activity AS (\n SELECT\n activity.id AS id,\n spell.id AS activity_id,\n extract (EPOCH FROM (coalesce(activity.date_scheduled,\n activity.date_deadline) - now() AT TIME ZONE 'UTC'))::INT/60\n AS proximity,\n activity.summary AS summary,\n activity.state AS state,\n activity.user_id AS user_id,\n coalesce(activity.date_scheduled,\n activity.date_deadline) AS date_scheduled,\n activity.data_model AS data_model,\n patient.other_identifier AS patient_other_id,\n patient.patient_identifier AS nhs_number,\n patient.family_name AS family_name,\n CASE\n WHEN patient.given_name IS NULL THEN ''\n ELSE upper(substring(patient.given_name FROM 1 FOR 1))\n END AS initial,\n ward.id AS ward_id\n FROM nh_activity activity\n INNER JOIN nh_clinical_patient patient\n ON activity.patient_id = patient.id\n INNER JOIN nh_clinical_location bed\n ON activity.location_id = bed.id\n INNER JOIN nh_clinical_location ward\n ON bed.parent_id = ward.id\n INNER JOIN nh_activity spell\n ON spell.data_model = 'nh.clinical.spell'\n AND spell.patient_id = activity.patient_id\n WHERE activity.state != 'completed'\n AND activity.state != 'cancelled'\n AND spell.state = 'started')\n SELECT\n id,\n activity_id,\n {proximity_interval},\n summary,\n state,\n user_id,\n date_scheduled,\n data_model,\n patient_other_id,\n nhs_number,\n ward_id,\n family_name,\n initial\n FROM activity"