Refused Observations SQL

class nh_eobs_mental_health.sql.refused_observations.RefusedObservationsSQL(pool, cr)

ews_activities_skeleton = "\n SELECT ews.id AS ews_id,\n activity.id AS id,\n activity.data_ref,\n activity.data_model,\n ews.partial_reason,\n activity.spell_activity_id,\n activity.creator_id,\n activity.state,\n activity.summary,\n activity.date_terminated,\n activity.sequence\n FROM nh_activity as activity\n INNER JOIN nh_clinical_patient_observation_ews as ews\n ON split_part(activity.data_ref, ',', 2)::int = ews.id\n WHERE activity.data_model = 'nh.clinical.patient.observation.ews'\n "

get_collect_activities_sql(activity_ids_sql)

get_collect_patients_sql(spell_ids)

get_ews_activities()

get_refused_chain_count_sql()

get_refused_ews_activities()

get_refused_last_ews()

get_refused_review_chain_sql()

get_refused_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()

refused_chain_count_skeleton = "\n SELECT (\n with reasons (reason) as (\n select unnest(partial_tree)\n )\n select count(*)\n from reasons\n where reason = 'refused'\n ) as count,\n first_activity_id as activity_id,\n spell_activity_id,\n first_activity_date_terminated\n FROM (\n SELECT *,\n row_number() over(\n partition by last_activity_id\n ORDER BY\n last_activity_id DESC,\n first_activity_id ASC\n ) AS last_activity_rank,\n row_number() over(\n partition by first_activity_id\n ORDER BY\n last_activity_id DESC,\n first_activity_id ASC\n ) AS first_activity_rank\n FROM refused_ews_activities\n WHERE NOT array_to_string(partial_tree, ',', 'null') LIKE '%null%'\n ) AS refused_ews\n WHERE last_activity_rank = 1\n AND first_activity_rank = 1\n "

refused_ews_skeleton = "\n WITH RECURSIVE refused_ews_tree AS (\n --Select the fields we want to use from the original table\n SELECT id,\n ews_id,\n creator_id,\n data_model,\n data_ref,\n spell_activity_id,\n state,\n ARRAY[id] as activity_ids,\n partial_reason,\n ARRAY[partial_reason] as partial_tree,\n id as first_activity_id,\n id as last_activity_id,\n true as refused,\n sequence,\n date_terminated as first_activity_date_terminated,\n date_terminated as last_activity_date_terminated\n FROM ews_activities\n --Make sure we only get EWS\n WHERE partial_reason = 'refused'\n AND state = 'completed'\n --Join the two tables\n UNION ALL\n --Select the same table but join it with parent row (via creator_id)\n SELECT child_act.id,\n child_act.ews_id,\n child_act.creator_id,\n child_act.data_model,\n child_act.data_ref,\n child_act.spell_activity_id,\n child_act.state,\n array_append(act.activity_ids, child_act.id) AS activity_ids,\n child_act.partial_reason,\n array_append(act.partial_tree, child_act.partial_reason)\n AS partial_tree,\n activity_ids[1] AS first_activity_id,\n child_act.id AS last_activity_id,\n NOT array_to_string(\n array_cat(\n partial_tree,\n ARRAY[child_act.partial_reason]\n ), ', ') <> array_to_string(\n array_cat(\n partial_tree,\n ARRAY[child_act.partial_reason]\n ), ', ', '(null)')\n AS refused,\n act.sequence,\n act.first_activity_date_terminated\n as first_activity_date_terminated,\n child_act.date_terminated as last_activity_date_terminated\n FROM ews_activities as child_act\n INNER JOIN refused_ews_tree as act\n ON (child_act.creator_id = act.id)\n WHERE child_act.state = 'completed'\n )\n\n SELECT *,\n row_number() over(\n partition by spell_activity_id\n ORDER BY spell_activity_id ASC,\n first_activity_id DESC,\n last_activity_id DESC\n ) AS rank\n FROM refused_ews_tree\n "

refused_last_ews_skeleton = "\n SELECT refused.id,\n refused.ews_id,\n refused.refused,\n acts.spell_id,\n acts.spell_activity_id,\n acts.date_terminated\n from refused_ews_activities AS refused\n RIGHT OUTER JOIN wb_activity_ranked AS acts\n ON acts.id = refused.id AND refused.rank = 1\n WHERE acts.rank = 1\n AND acts.state = 'completed'\n AND acts.data_model = 'nh.clinical.patient.observation.ews'\n "

refused_review_chain_skeleton = "\n SELECT rchain.count,\n rchain.spell_activity_id,\n review_activity.state as review_state,\n review_activity.date_terminated as review_date_terminated,\n review_activity.terminate_uid as review_terminate_uid,\n freq_activity.state as freq_state,\n freq_activity.date_terminated as freq_date_terminated,\n freq_activity.terminate_uid as freq_terminate_uid,\n rchain.first_activity_date_terminated\n as first_refusal_date_terminated\n FROM refused_chain_count AS rchain\n LEFT JOIN nh_activity AS review_activity\n ON review_activity.creator_id = rchain.activity_id\n AND review_activity.data_model = 'nh.clinical.notification.clinical_review'\n LEFT JOIN nh_activity AS freq_activity\n ON freq_activity.creator_id = review_activity.id\n AND freq_activity.data_model =\n 'nh.clinical.notification.clinical_review_frequency'\n "