def orderSetSearch(self, itemQuery, conn=None):
"""Look for clinical items based on specified query criteria"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
query = SQLQuery()
query.addSelect("ic.item_collection_id")
query.addSelect("ic.external_id")
query.addSelect("ic.name as collection_name")
query.addSelect("ic.section")
query.addSelect("ic.subgroup")
query.addSelect("ci.clinical_item_category_id")
query.addSelect("ci.clinical_item_id")
query.addSelect("ci.name")
query.addSelect("ci.description")
query.addFrom("item_collection as ic")
query.addFrom("item_collection_item as ici")
query.addFrom("clinical_item as ci")
query.addWhere("ic.item_collection_id = ici.item_collection_id")
query.addWhere("ici.clinical_item_id = ci.clinical_item_id")
query.addWhereNotEqual("ic.section", AD_HOC_SECTION)
if itemQuery.searchStr is not None:
searchWords = itemQuery.searchStr.split()
for searchWord in searchWords:
query.addWhereOp(
"ic.name", "~*",
"^%(searchWord)s|[^a-z]%(searchWord)s" %
{"searchWord": searchWord})
# Prefix search by regular expression
if itemQuery.analysisStatus is not None:
query.addWhereEqual("ci.analysis_status",
itemQuery.analysisStatus)
query.addOrderBy("lower(ic.name)")
query.addOrderBy("ic.external_id")
query.addOrderBy("lower(ic.section)")
query.addOrderBy("lower(ic.subgroup)")
query.addOrderBy("ci.clinical_item_id")
query.addOrderBy("ci.name")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
# Aggregate up into order sets
orderSetModel = None
for row in dataModels:
if orderSetModel is None or row[
"external_id"] != orderSetModel["external_id"]:
if orderSetModel is not None:
# Prior order set exists, yield/return it before preparing next one
yield orderSetModel
orderSetModel = \
{ "external_id": row["external_id"],
"name": row["collection_name"],
"itemList": list(),
}
orderSetModel["itemList"].append(row)
yield orderSetModel
# Yield the last processed model
finally:
if not extConn:
conn.close()
def signOrders(self,
userId,
patientId,
currentTime,
orderItemIds,
discontinuePatientOrderIds=None,
conn=None):
"""Commit new order item IDs for the given patient and starting now,
and discontinue (set end date) for any existing orders specified.
Record any patient state transitions the orders would trigger
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
# Denormalized recording of current patient state to facilitate easy retrieval linked to orders later
patientInfo = self.loadPatientInfo([patientId],
currentTime,
conn=conn)[0]
stateId = patientInfo["sim_state_id"]
postStateIdByItemId = patientInfo["postStateIdByItemId"]
orderItemIdSet = set(orderItemIds)
# Ensure unique and facilitate set operations
insertDict = {
"sim_user_id": userId,
"sim_patient_id": patientId,
"sim_state_id": stateId,
"relative_time_start": currentTime
}
for itemId in orderItemIdSet:
insertDict["clinical_item_id"] = itemId
DBUtil.insertRow("sim_patient_order", insertDict, conn=conn)
# See if any of these new orders triggered state transitions
triggerItemIds = postStateIdByItemId.viewkeys() & orderItemIdSet
while triggerItemIds: # Found a trigger item
triggerItemId = None
if len(
triggerItemIds
) > 1: # Found multiple. Weird. Arbitrarily act on the one that appeared first in the input list
for itemId in orderItemIds:
if itemId in triggerItemIds:
triggerItemId = itemId
break
else:
triggerItemId = triggerItemIds.pop()
postStateId = postStateIdByItemId[triggerItemId]
# Record the state transition
self.recordStateTransition(patientId,
stateId,
postStateId,
currentTime,
conn=conn)
# Reload patientInfo to reflect new patient state
patientInfo = self.loadPatientInfo([patientId],
currentTime,
conn=conn)[0]
stateId = patientInfo["sim_state_id"]
postStateIdByItemId = patientInfo["postStateIdByItemId"]
orderItemIdSet.discard(triggerItemId)
# Don't keep looking for this one, important to avoid infinite loop
triggerItemIds = postStateIdByItemId.viewkeys(
) & orderItemIdSet
if discontinuePatientOrderIds is not None:
updateDict = {
"relative_time_end": currentTime
}
for patientOrderId in discontinuePatientOrderIds:
DBUtil.updateRow("sim_patient_order",
updateDict,
patientOrderId,
conn=conn)
# If order is discontinued/cancelled at the same (or before) time of entry,
# take that as a signal to cleanup and delete the record altogether
# (effectively there was no time at which the order was ever allowed to exist)
deleteQuery = SQLQuery()
deleteQuery.delete = True
deleteQuery.addFrom("sim_patient_order")
deleteQuery.addWhereEqual("sim_patient_id", patientId)
deleteQuery.addWhere(
"relative_time_end <= relative_time_start")
DBUtil.execute(deleteQuery, conn=conn)
finally:
conn.commit()
if not extConn:
conn.close()
def loadResults(self, patientId, relativeTime, conn=None):
"""Load all results active by the given relativeTime.
Will look for sim_patient_state times and sim_patient_order for diagnostic orders,
to extrapolate all state-specific results for each order, or using default values
if no state specific ones available.
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
# First query for all expected result labels and states, without state-specific values as
# may want outer join behvaior against default state values
query = SQLQuery()
query.addSelect("distinct spo.sim_state_id")
query.addSelect("sr.sim_result_id")
query.addSelect("sr.name")
query.addSelect("sr.description")
query.addSelect("sr.priority")
query.addSelect("sr.group_string")
query.addSelect("spo.relative_time_start")
query.addSelect("sorm.turnaround_time")
query.addSelect(
"(spo.relative_time_start + sorm.turnaround_time) as result_relative_time"
)
query.addFrom("sim_patient_order as spo")
query.addFrom("sim_order_result_map as sorm")
query.addFrom("sim_result as sr")
query.addWhere("spo.clinical_item_id = sorm.clinical_item_id")
query.addWhere("sorm.sim_result_id = sr.sim_result_id")
query.addWhereEqual("spo.sim_patient_id", patientId)
# Only unlock results if appropiate prereq orders were placed in the past (and longer than the turnaround time)
query.addWhereOp("spo.relative_time_start + sorm.turnaround_time",
"<=", relativeTime)
# Also check that the triggering order was not cancelled before the completion of the turnaround time
query.addWhere(
"( spo.relative_time_end is null or spo.relative_time_start + sorm.turnaround_time <= spo.relative_time_end )"
)
query.addOrderBy("result_relative_time")
query.addOrderBy("sr.priority")
resultTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
resultModels = modelListFromTable(resultTable)
# Pass through results to get set of states to search for
stateIds = set([DEFAULT_STATE_ID])
# Include default state to fall back on
for resultModel in resultModels:
stateIds.add(resultModel["sim_state_id"])
# Second query for state-specific values
valueQuery = SQLQuery()
valueQuery.addSelect("ssr.sim_state_id")
valueQuery.addSelect("ssr.sim_result_id")
valueQuery.addSelect("ssr.num_value")
valueQuery.addSelect("ssr.num_value_noise")
valueQuery.addSelect("ssr.text_value")
valueQuery.addSelect("ssr.result_flag")
valueQuery.addSelect("ssr.clinical_item_id")
# Output clinical item if result flag means something
valueQuery.addFrom("sim_state_result as ssr")
valueQuery.addWhereIn("ssr.sim_state_id", stateIds)
valueTable = DBUtil.execute(valueQuery,
includeColumnNames=True,
conn=conn)
valueModels = modelListFromTable(valueTable)
# Store in-memory dictionary for rapid cross-referencing "join" to result table
valueModelByStateIdByResultId = dict()
for valueModel in valueModels:
resultId = valueModel["sim_result_id"]
stateId = valueModel["sim_state_id"]
if resultId not in valueModelByStateIdByResultId:
valueModelByStateIdByResultId[resultId] = dict()
valueModelByStateIdByResultId[resultId][stateId] = valueModel
# Now go back through original results and join up state-specific values, or use default values if needed
resultValueModels = list()
for resultModel in resultModels:
resultId = resultModel["sim_result_id"]
stateId = resultModel["sim_state_id"]
if resultId in valueModelByStateIdByResultId:
valueModelByStateId = valueModelByStateIdByResultId[
resultId]
if stateId in valueModelByStateId:
# Have a state-specific value, populate that
valueModel = valueModelByStateId[stateId]
resultModel.update(valueModel)
elif DEFAULT_STATE_ID in valueModelByStateId:
# No state-specific value, but have a default one to populate instead
valueModel = valueModelByStateId[DEFAULT_STATE_ID]
resultModel.update(valueModel)
resultValueModels.append(resultModel)
else:
# No result information available, even in default state. Skip these
#resultModel["num_value"] = None;
#resultModel["num_value_noise"] = None;
#resultModel["text_value"] = None;
#resultModel["result_flag"] = None;
#resultModel["clinical_item_id"] = None;
pass
return resultValueModels
finally:
if not extConn:
conn.close()
line = line.strip().split("\t")
treatment_aggregation_map[line[0]] = line[1]
print(treatment_aggregation_map)
# Create SCRIPT_FILE
SCRIPT_FILE = StringIO()
SCRIPT_FILE.write("psql stride jwang198")
# Find all clinical_item_ids associated with each patient_id
# SELECT * FROM clinical_item WHERE clinical_item_category_id = 161;
DATA_QUERY = SQLQuery()
DATA_QUERY.addSelect("clinical_item_id")
DATA_QUERY.addSelect("description")
DATA_QUERY.addSelect("clinical_item_category_id")
DATA_QUERY.addWhereEqual("clinical_item_category_id", "161")
DATA_QUERY.addFrom("clinical_item")
print(DATA_QUERY)
# Write out data to CSV
DBUtil.runDBScript(SCRIPT_FILE, False)
results = DBUtil.execute(DATA_QUERY)
output = open("/Users/jwang/Desktop/Results/treatment_teams.csv", "w")
output.write(
"clinical_item_id,description,clinical_item_category_id,treatment_team\n")
for line in results:
treatment_team = "Unclassified"
if line[1] in treatment_aggregation_map:
def copyPatientTemplate(self, patientData, templatePatientId, conn=None):
"""Create a new patient record based on the given template patient ID to copy from.
Will copy shallow attributes, overridden by any provided in the given patientData,
as well as any patient states, notes, or physician orders UP TO (and including)
relative time zero, but not subsequent states, notes, or physician orders
(the latter is expected to reflect real user interaction records).
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
templatePatientData = DBUtil.loadRecordModelById("sim_patient",
templatePatientId,
conn=conn)
del templatePatientData["sim_patient_id"]
# Remove prior ID to allow for new one
templatePatientData.update(patientData)
# Override with new content (if exists)
DBUtil.insertRow("sim_patient", templatePatientData, conn=conn)
# Create new patient record
patientId = DBUtil.execute(DBUtil.identityQuery("sim_patient"),
conn=conn)[0][0]
# Copy initial template patient states
query = SQLQuery()
query.addSelect("*")
# Copy all columns
query.addFrom("sim_patient_state as sps")
query.addWhereEqual("sps.sim_patient_id", templatePatientId)
query.addWhereOp("relative_time_start", "<=", 0)
query.addOrderBy("relative_time_start")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
nStates = len(dataModels)
for i, dataModel in enumerate(dataModels):
del dataModel["sim_patient_state_id"]
# Discard copied ID to allow new one
if i == nStates - 1:
del dataModel["relative_time_end"]
# Last state. Blank out end time to reflect open ended for simulation
dataModel["sim_patient_id"] = patientId
DBUtil.insertRow("sim_patient_state", dataModel, conn=conn)
# Copy initial template orders
query = SQLQuery()
query.addSelect("*")
query.addFrom("sim_patient_order as spo")
query.addWhereEqual("sim_patient_id", templatePatientId)
query.addWhereOp("relative_time_start", "<=", 0)
query.addOrderBy("relative_time_start")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
for dataModel in dataModels:
del dataModel["sim_patient_order_id"]
dataModel["sim_patient_id"] = patientId
DBUtil.insertRow("sim_patient_order", dataModel, conn=conn)
conn.commit()
# Transactional commit for multi-step process
return patientId
finally:
if not extConn:
conn.close()
def test_buildFeatureMatrix_multiFlowsheet(self):
"""
Test buildFeatureMatrix and addFlowsheet.
"""
# Verify FeatureMatrixFactory throws Error if patientEpisodeInput
# has not been set.
with self.assertRaises(ValueError):
self.factory.processPatientEpisodeInput()
# Initialize DB cursor.
cursor = self.connection.cursor()
# Build SQL query for list of patient episodes.
patientEpisodeQuery = SQLQuery()
patientEpisodeQuery.addSelect("CAST(pat_id AS bigint)")
patientEpisodeQuery.addSelect("sop.order_proc_id AS order_proc_id")
patientEpisodeQuery.addSelect("proc_code")
patientEpisodeQuery.addSelect("order_time")
patientEpisodeQuery.addSelect(
"COUNT(CASE result_in_range_yn WHEN 'Y' THEN 1 ELSE null END) AS normal_results"
)
patientEpisodeQuery.addFrom("stride_order_proc AS sop")
patientEpisodeQuery.addFrom("stride_order_results AS sor")
patientEpisodeQuery.addWhere("sop.order_proc_id = sor.order_proc_id")
patientEpisodeQuery.addWhereEqual("proc_code", "LABMETB")
patientEpisodeQuery.addGroupBy(
"pat_id, sop.order_proc_id, proc_code, order_time")
patientEpisodeQuery.addOrderBy(
"pat_id, sop.order_proc_id, proc_code, order_time")
cursor.execute(str(patientEpisodeQuery), patientEpisodeQuery.params)
# Set and process patientEpisodeInput.
self.factory.setPatientEpisodeInput(cursor, "pat_id", "order_time")
self.factory.processPatientEpisodeInput()
resultEpisodeIterator = self.factory.getPatientEpisodeIterator()
resultPatientEpisodes = list()
for episode in resultEpisodeIterator:
episode["pat_id"] = int(episode["pat_id"])
episode["order_time"] = DBUtil.parseDateValue(
episode["order_time"])
resultPatientEpisodes.append(episode)
# Verify results (note sort order).
expectedPatientEpisodes = FM_TEST_OUTPUT[
"test_processPatientEpisodeInput"]
self.assertEqualList(resultPatientEpisodes, expectedPatientEpisodes)
# Add flowsheet features.
flowsheetNames = ["Resp", "FiO2", "Glasgow Coma Scale Score"]
# Look for lab data 90 days before each episode, but never afterself.
preTimeDelta = datetime.timedelta(-90)
postTimeDelta = datetime.timedelta(0)
self.factory.addFlowsheetFeatures(flowsheetNames, preTimeDelta,
postTimeDelta)
self.factory.buildFeatureMatrix()
resultMatrix = self.factory.readFeatureMatrixFile()
# Verify results.
expectedMatrix = FM_TEST_OUTPUT[
"test_buildFeatureMatrix_multiFlowsheet"]["expectedMatrix"]
self.assertEqualTable(expectedMatrix, resultMatrix[2:], precision=5)
try:
os.remove(self.factory.getMatrixFileName())
except OSError:
pass
# clinical_item_category_id = 13 (Discharge)
# clinical_item_category_id = 23 (Admission)
SCRIPT_FILE = StringIO()
SCRIPT_FILE.write("psql stride jwang198")
DATA_QUERY = SQLQuery()
DATA_QUERY.addSelect("pat_anon_id")
DATA_QUERY.addSelect("shifted_transf_in_dt_tm")
DATA_QUERY.addSelect("department_in")
DATA_QUERY.addSelect("event_in")
DATA_QUERY.addSelect("shifted_transf_out_dt_tm")
DATA_QUERY.addSelect("event_out")
DATA_QUERY.addSelect("pat_enc_csn_anon_id")
DATA_QUERY.addWhereEqual("event_out = 'Discharge' OR event_in", 'Admission')
DATA_QUERY.addFrom("stride_adt")
print(DATA_QUERY)
# Write out data to CSV
DBUtil.runDBScript(SCRIPT_FILE, False)
results = DBUtil.execute(DATA_QUERY)
output = open("/Users/jwang/Desktop/Results/admission_discharge_flow.csv", "w")
output.write(
"patient_id,admission_time,department,admission,discharge_time,discharge,encounter_id\n"
)
for line in results:
output.write("{0},{1},{2},{3},{4},{5},{6}\n".format(
# H&P Note
SCRIPT_FILE = StringIO()
SCRIPT_FILE.write("psql stride jwang198")
DATA_QUERY = SQLQuery()
DATA_QUERY.addSelect("author_name")
DATA_QUERY.addSelect("pat_id")
DATA_QUERY.addSelect("note_date")
DATA_QUERY.addSelect("note_type")
DATA_QUERY.addSelect("provider_type")
DATA_QUERY.addSelect("specialty")
DATA_QUERY.addSelect("pat_enc_csn_id")
DATA_QUERY.addFrom("stride_note")
DATA_QUERY.addWhereEqual("note_type", "H&P")
DATA_QUERY.addOrderBy("author_name", dir="asc")
print(DATA_QUERY)
# Write out data to CSV
DBUtil.runDBScript(SCRIPT_FILE, False)
results = DBUtil.execute(DATA_QUERY)
output_hp = open("/Users/jwang/Desktop/Results/physician_patient_map_hp.csv",
"w")
output_hp.write(
"clinician_name,patient_id,note_date,note_type,provider_type,specialty,encounter_id\n"
)
for line in results:
if (line[4] == "Medical Student"
# Create SCRIPT_FILE
SCRIPT_FILE = StringIO()
SCRIPT_FILE.write("psql stride jwang198")
# Load in top patient count diagnoses (we will only include these as binary covariates)
# select *
# from clinical_item
# where clinical_item_category_id = 2
# order by patient_count desc
DATA_QUERY = SQLQuery()
DATA_QUERY.addSelect("clinical_item_id")
DATA_QUERY.addSelect("name")
DATA_QUERY.addSelect("description")
DATA_QUERY.addSelect("patient_count")
DATA_QUERY.addFrom("clinical_item")
DATA_QUERY.addWhereEqual("patient_count > 0 and clinical_item_category_id", 2)
top_admission_diagnoses_f = open(
"/Users/jwang/Desktop/Results/top_admission_diagnoses.csv", "w")
top_admission_diagnoses = {}
top_admission_diagnoses_string = []
DBUtil.runDBScript(SCRIPT_FILE, False)
results = DBUtil.execute(DATA_QUERY)
top_admission_diagnoses_f.write(
"clinical_item_id,name,description,patient_count\n")
for line in results:
top_admission_diagnoses_f.write("{0},{1},{2},{3}\n".format(
line[0], line[1], line[2], line[3]))
top_admission_diagnoses[line[0]] = line[2]
top_admission_diagnoses_string.append(str(line[0]))
top_admission_diagnoses_f.close()
DATA_QUERY = SQLQuery()
# From patient_item or clinical_item
DATA_QUERY.addSelect("patient_id")
DATA_QUERY.addSelect("clinical_item_category_id")
DATA_QUERY.addSelect('name')
DATA_QUERY.addSelect("description")
# Join
DATA_QUERY.addFrom("patient_item")
DATA_QUERY.addJoin(
"clinical_item",
"patient_item.clinical_item_id = clinical_item.clinical_item_id",
joinType="INNER")
DATA_QUERY.addWhereEqual("clinical_item_category_id = 161 AND description",
"Tt Med Univ (Primary)") # Everyone
#DATA_QUERY.addWhereEqual("clinical_item_category_id = 161 AND description", "Tt Pamf Med (Primary)") # Expert
DATA_QUERY.addOrderBy("patient_id", dir="ASC")
print(DATA_QUERY)
# Write out data to CSV
DBUtil.runDBScript(SCRIPT_FILE, False)
results = DBUtil.execute(DATA_QUERY)
unique_patient_ids = {}
# output = open("/Users/jwang/Desktop/expert.csv", "w")
# outlist = open("/Users/jwang/Desktop/expert_list.csv", "w")
output = open("/Users/jwang/Desktop/everyone.csv",