def createPatient(self, patientData, initialStateId, conn=None):
"""Create a new patient record given the data dictionary.
Create a respective initial patient state record as well.
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
DBUtil.insertRow("sim_patient", patientData, conn=conn)
patientId = DBUtil.execute(DBUtil.identityQuery("sim_patient"),
conn=conn)[0][0]
patientStateData = {
"sim_patient_id": patientId,
"sim_state_id": initialStateId,
"relative_time_start": 0
}
DBUtil.insertRow("sim_patient_state", patientStateData, conn=conn)
conn.commit()
# Transactional commit for two step process
return patientId
finally:
if not extConn:
conn.close()
def createUser(self, userData, conn=None):
"""Create a new user record given the data dictionary
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
DBUtil.insertRow("sim_user", userData, conn=conn)
conn.commit()
finally:
if not extConn:
conn.close()
def prepareItemAssociations(self, itemIdPairs, linkedItemIdsByBaseId,
conn):
"""Make sure all pair-wise item association records are ready / initialized
so that subsequent queries don't have to pause to check for their existence.
Should help greatly to reduce number of queries and execution time.
"""
clinicalItemIdSet = set()
#Do the below to convert the list of strings into a list of pairs, which is needed for the rest of this function
for index, pair in enumerate(itemIdPairs):
itemIdPairs[index] = eval(pair)
for (itemId1, itemId2) in itemIdPairs:
clinicalItemIdSet.add(itemId1)
clinicalItemIdSet.add(itemId2)
nItems = len(clinicalItemIdSet)
# Now go through all needed item pairs and create default records as needed
log.debug("Ensure %d baseline records ready" % (nItems * nItems))
for itemId1 in clinicalItemIdSet:
# Query to see which ones already exist in the database
# Do this for each source clinical item instead of all combinations to avoid excessive in memory tracking
query = SQLQuery()
query.addSelect("clinical_item_id")
query.addSelect("subsequent_item_id")
query.addFrom("clinical_item_association")
query.addWhereEqual("clinical_item_id", itemId1)
query.addWhereIn("subsequent_item_id", clinicalItemIdSet)
associationTable = DBUtil.execute(query, conn=conn)
# Keep track in memory temporarily for rapid lookup
existingItemIdPairs = set()
for row in associationTable:
existingItemIdPairs.add(tuple(row))
for itemId2 in clinicalItemIdSet:
itemIdPair = (itemId1, itemId2)
if itemIdPair not in existingItemIdPairs and self.acceptableClinicalItemIdPair(
itemId1, itemId2, linkedItemIdsByBaseId):
defaultAssociation = RowItemModel(
itemIdPair, ("clinical_item_id", "subsequent_item_id"))
try: # Optimistic insert of a new item pair, should be safe since just checked above, but parallel processes may collide
DBUtil.insertRow("clinical_item_association",
defaultAssociation,
conn=conn)
except conn.IntegrityError, err:
log.warning(err)
pass
def main(argv):
conversionProcessor = STRIDEOrderResultsConversion();
conn = DBUtil.connection();
try:
# Pull out list of result names to look for that are not already in the calculated
nameTable = DBUtil.execute("select name from sim_result except select base_name from order_result_stat", conn=conn);
prog = ProgressDots(big=1,small=1,total=len(nameTable));
for row in nameTable:
baseName = row[0];
print("Calculating Stats for %s" % baseName, file=sys.stderr);
statModel = conversionProcessor.calculateResultStats( baseName, conn=conn );
DBUtil.insertRow("order_result_stat", statModel, conn=conn );
prog.update();
prog.printStatus();
conn.commit();
finally:
conn.close();
def recordStateTransition(self,
patientId,
preStateId,
postStateId,
currentTime,
conn=None):
"""Record a patient state transition
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
# Ending of pre-state
updateQuery = \
"""update sim_patient_state
set relative_time_end = %(p)s
where sim_patient_id = %(p)s
and sim_state_id = %(p)s
and relative_time_start <= %(p)s
and relative_time_end is null
""" % {"p": DBUtil.SQL_PLACEHOLDER}
updateParams = (currentTime, patientId, preStateId, currentTime)
DBUtil.execute(updateQuery, updateParams, conn=conn)
# Beginning of post-state
insertDict = {
"sim_patient_id": patientId,
"sim_state_id": postStateId,
"relative_time_start": currentTime
}
DBUtil.insertRow("sim_patient_state", insertDict, conn=conn)
finally:
conn.commit()
if not extConn:
conn.close()
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 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)
finally:
conn.commit()
if not extConn:
conn.close()
def populateResultFlag(self, resultModel, conn=None):
"""If order result row model has no pre-specified result_flag, then assign one
based on distribution of known results for this item type, or just a default "Result" flag.
"""
if resultModel[
"result_flag"] is not None: # Only proceed if flag is currently null / blank
return
elif resultModel[
"result_in_range_yn"] is not None: # Alternative specification of normal / abnormal
if resultModel["result_in_range_yn"] == "Y":
resultModel["result_flag"] = FLAG_IN_RANGE
else: #resultModel["result_in_range_yn"] == "N":
resultModel["result_flag"] = FLAG_ABNORMAL
return
elif resultModel["ord_num_value"] is None or resultModel[
"ord_num_value"] == SENTINEL_RESULT_VALUE:
# No specific result flag or (numerical) value provided. Just record that some result was generated at all
resultModel["result_flag"] = FLAG_RESULT
return
elif resultModel['base_name'] is None:
# With 2014-2017 data, there are fields with a null base_name.
# We can't build summary stats around this case, so just return
# FLAG_RESULT.
resultModel['result_flag'] = FLAG_RESULT
return
#else: # General case, no immediately available result flags
extConn = conn is not None
if not extConn:
conn = self.connFactory.connection()
if self.resultStatsByBaseName is None:
# Ensure result stats cache is preloaded
dataTable = DBUtil.execute("select * from order_result_stat",
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
self.resultStatsByBaseName = modelDictFromList(
dataModels, "base_name")
if resultModel["base_name"] not in self.resultStatsByBaseName:
# Result stats not already in cache. Query from DB and store in cache for future use.
statModel = self.calculateResultStats(resultModel["base_name"],
conn=conn)
# Store results back in cache to facilitate future lookups
self.resultStatsByBaseName[resultModel["base_name"]] = statModel
DBUtil.insertRow("order_result_stat", statModel, conn=conn)
statModel = self.resultStatsByBaseName[resultModel["base_name"]]
if statModel["max_result_flag"] is not None or statModel[
"max_result_in_range"] is not None:
# Alternative result flagging methods exist. We should just use those and treat this as a normal "in range" result
resultModel["result_flag"] = FLAG_IN_RANGE
else:
# No values in the entire database for this item have a result flag. Let's see if we can estimate ranges based on numerical values
if statModel[
"value_count"] > 0: # Found some values to calculate summary stats
try:
mean = statModel["value_sum"] / statModel["value_count"]
# Std Dev = sqrt( E[x^2] - E[x]^2 )
var = (statModel["value_sum_squares"] /
statModel["value_count"]) - (mean * mean)
stdev = math.sqrt(var)
zScore = 0
if stdev > 0:
zScore = (resultModel["ord_num_value"] - mean) / stdev
if zScore < -Z_SCORE_LIMIT:
resultModel["result_flag"] = FLAG_LOW
elif zScore > Z_SCORE_LIMIT:
resultModel["result_flag"] = FLAG_HIGH
else: # |zScore| < Z_SCORE_LIMIT
resultModel["result_flag"] = FLAG_IN_RANGE
except ValueError as exc:
# Math error, probably stdev = 0 or variance < 0, just treat as an unspecified result
resultModel["result_flag"] = FLAG_RESULT
else: # No value distribution, just record as a non-specific result
resultModel["result_flag"] = FLAG_RESULT
if not extConn:
conn.close()
