def queryItems(self, options, outputFile):
"""Query for all clinical item records that fulfill the options criteria
and then send the results as tab-delimited output to the outputFile.
"""
pauseSeconds = float(options.pauseSeconds)
query = SQLQuery()
query.addSelect(
"cic.description, ci.clinical_item_id, ci.name, ci.description")
query.addFrom("clinical_item_category as cic")
query.addFrom("clinical_item as ci")
query.addWhere(
"cic.clinical_item_category_id = ci.clinical_item_category_id")
if options.itemPrefix:
query.addWhereOp("ci.description", "like",
options.itemPrefix + "%%")
# Add wildcard to enabe prefix search
if options.categoryNames:
query.addWhereIn("cic.description",
options.categoryNames.split(","))
query.addOrderBy(
"cic.description, ci.name, ci.description, ci.clinical_item_id")
formatter = TextResultsFormatter(outputFile)
prog = ProgressDots()
for row in DBUtil.execute(query,
includeColumnNames=True,
connFactory=self.connFactory):
formatter.formatTuple(row)
time.sleep(pauseSeconds)
prog.update()
prog.printStatus()
def queryPatients(period, locations, rxCount):
log.info(
"Select patients fitting criteria in designated time period: (%s,%s)" %
period)
query = SQLQuery()
query.addSelect("med.pat_id")
query.addSelect("count(order_med_id)")
query.addFrom("stride_mapped_meds as map")
query.addFrom("stride_order_med as med")
query.addFrom("stride_patient as pat")
query.addWhere("analysis_status = 1")
query.addWhere("map.medication_id = med.medication_id")
query.addWhere("med.pat_id = pat.pat_id")
query.addWhere("possible_oncology = 0")
query.addWhereIn("patient_location", locations)
query.addWhereOp("ordering_datetime", ">", period[0])
query.addWhereOp("ordering_datetime", "<", period[-1])
query.addGroupBy("med.pat_id")
query.addHaving("count(order_med_id) >2")
results = DBUtil.execute(query)
cols = ["patientId", "nOpioidRx"]
patientDF = pd.DataFrame(results, columns=cols)
#patientDF.set_index("patientId",drop=False,inplace=True);
patientDF["periodStart"] = period[0]
# Identify this group of patient records
return patientDF
def queryItems(self, options, outputFile):
"""Query for all clinical item records that fulfill the options criteria
and then send the results as tab-delimited output to the outputFile.
"""
pauseSeconds = float(options.pauseSeconds)
query = SQLQuery()
query.addSelect("count(order_med_id_coded) as nOrders")
query.addSelect("om.med_route, om.medication_id, om.med_description")
query.addFrom("starr_datalake2018.order_med as om")
if options.descriptionPrefix:
query.addWhereOp("om.med_description", "like",
options.descriptionPrefix + "%%")
# Add wildcard to enabe prefix search
if options.medRoutes:
query.addWhereIn("om.med_route", options.medRoutes.split(","))
query.addGroupBy("om.medication_id, om.med_description, om.med_route")
query.addOrderBy("nOrders desc, om.med_description")
formatter = TextResultsFormatter(outputFile)
prog = ProgressDots()
for row in DBUtil.execute(query,
includeColumnNames=True,
connFactory=self.connFactory):
formatter.formatTuple(row)
time.sleep(pauseSeconds)
prog.update()
prog.printStatus()
def loadPatientOrders(self,
patientId,
currentTime,
loadActive=True,
conn=None):
"""Load orders for the given patient that exist by the specified current time point.
loadActive - Specify whether to load active vs. inactive/completed orders. Set to None to load both
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
query = SQLQuery()
query.addSelect("po.sim_patient_order_id")
query.addSelect("po.sim_user_id")
query.addSelect("po.sim_patient_id")
query.addSelect("po.sim_state_id")
query.addSelect("po.clinical_item_id")
query.addSelect("po.relative_time_start")
query.addSelect("po.relative_time_end")
query.addSelect("ci.name")
query.addSelect("ci.description")
query.addSelect("cic.source_table")
query.addSelect("cic.description as category_description")
query.addFrom("sim_patient_order as po")
query.addFrom("clinical_item as ci")
query.addFrom("clinical_item_category as cic")
query.addWhere("po.clinical_item_id = ci.clinical_item_id")
query.addWhere(
"ci.clinical_item_category_id = cic.clinical_item_category_id")
query.addWhereEqual("sim_patient_id", patientId)
query.addWhereOp("relative_time_start", "<=", currentTime)
if loadActive: # Filter out inactive orders here.
query.openWhereOrClause()
query.addWhere("relative_time_end is null")
query.addWhereOp("relative_time_end", ">", currentTime)
query.closeWhereOrClause()
#elif loadActive is not None: # Filter out active orders here.
# query.addWhereOp("relative_time_end","<=", currentTime)
if loadActive: # Organize currently active orders by category
query.addOrderBy("cic.description")
query.addOrderBy("ci.description")
query.addOrderBy("relative_time_start")
else: # Otherwise chronologic order
query.addOrderBy("relative_time_start")
query.addOrderBy("cic.description")
query.addOrderBy("ci.description")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
return dataModels
finally:
if not extConn:
conn.close()
def querySourceItems(self, startDate=None, endDate=None, progress=None, conn=None):
"""Query the database for list of all source clinical items (lab results in this case)
and yield the results one at a time. If startDate provided, only return items
whose result_time is on or after that date.
Only include results records where the result_flag is set to an informative value,
to focus only on abnormal lab results (including would be a ton more relatively uninformative
data that would greatly expend data space and subsequent computation time)
"""
extConn = conn is not None;
if not extConn:
conn = self.connFactory.connection();
# Column headers to query for that map to respective fields in analysis table
headers = ["sor.order_proc_id", "pat_id", "pat_enc_csn_id", "order_type", "proc_id", "proc_code", "base_name", "component_name", "common_name", "ord_num_value", "result_flag", "result_in_range_yn", "sor.result_time"];
query = SQLQuery();
for header in headers:
query.addSelect( header );
query.addFrom("stride_order_proc as sop");
query.addFrom("%s as sor" % SOURCE_TABLE);
query.addWhere("sop.order_proc_id = sor.order_proc_id");
#query.addWhere("result_flag <> '*'"); # Will exclude nulls and the uninformative '*' values for text-based microbiology results
if startDate is not None:
query.addWhereOp("sor.result_time",">=", startDate);
if endDate is not None:
query.addWhereOp("sor.result_time","<", endDate);
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(), conn=conn)[0][0];
cursor = conn.cursor();
# Do one massive query, but yield data for one item at a time.
cursor.execute( str(query), tuple(query.params) );
row = cursor.fetchone();
while row is not None:
rowModel = RowItemModel( row, headers );
# Normalize qualified labels
rowModel["order_proc_id"] = rowModel["sor.order_proc_id"];
rowModel["result_time"] = rowModel["sor.result_time"];
if rowModel['base_name'] is None:
row = cursor.fetchone()
continue
self.populateResultFlag(rowModel,conn=conn);
yield rowModel; # Yield one row worth of data at a time to avoid having to keep the whole result set in memory
row = cursor.fetchone();
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close();
if not extConn:
conn.close();
def updateClinicalItemCounts(self, acceptCache=False, conn=None):
"""Update the summary item_counts for clinical_items based
on clinical_item_association summary counts.
If acceptCache is True, then will first check for existence of an entry "clinicalItemCountsUpdated"
in the data_cache table. If it exists, assume we have done this update already, and no need to force the calculations again
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
if acceptCache:
isCacheUpdated = (self.getCacheData(
"clinicalItemCountsUpdated", conn=conn) is not None)
if isCacheUpdated:
# Item count caches already updated, no need to recalculate them
return
# First reset all counts to zero
query = "update clinical_item set item_count = 0, patient_count = 0, encounter_count = 0 "
params = []
if self.maxClinicalItemId is not None: # Restrict to (test) data
query += "where clinical_item_id < %s" % DBUtil.SQL_PLACEHOLDER
params.append(self.maxClinicalItemId)
DBUtil.execute(query, params, conn=conn)
sqlQuery = SQLQuery()
sqlQuery.addSelect("clinical_item_id")
sqlQuery.addSelect("count_0 as item_count")
sqlQuery.addSelect("patient_count_0 as patient_count")
sqlQuery.addSelect("encounter_count_0 as encounter_count")
sqlQuery.addFrom("clinical_item_association as ci")
sqlQuery.addWhere("clinical_item_id = subsequent_item_id")
# Look along "diagonal" of matrix for primary summary stats
if self.maxClinicalItemId is not None: # Restrict to (test) data
sqlQuery.addWhereOp("clinical_item_id", "<",
self.maxClinicalItemId)
resultTable = DBUtil.execute(sqlQuery,
includeColumnNames=True,
conn=conn)
resultModels = modelListFromTable(resultTable)
for result in resultModels:
DBUtil.updateRow("clinical_item",
result,
result["clinical_item_id"],
conn=conn)
# Make a note that this cache data has been updated
self.setCacheData("clinicalItemCountsUpdated", "True", conn=conn)
finally:
if not extConn:
conn.close()
def clinicalItemSearch(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("ci.clinical_item_id")
query.addSelect("ci.name")
query.addSelect("ci.description")
query.addSelect("cic.source_table")
query.addSelect("cic.description as category_description")
query.addFrom("clinical_item as ci")
query.addFrom("clinical_item_category as cic")
query.addWhere(
"ci.clinical_item_category_id = cic.clinical_item_category_id")
if itemQuery.searchStr is not None:
searchWords = itemQuery.searchStr.split()
#query.openWhereOrClause()
for searchField in ("ci.description", ):
for searchWord in searchWords:
query.addWhereOp(
searchField, "~*",
"^%(searchWord)s|[^a-z]%(searchWord)s" %
{"searchWord": searchWord
}) # Prefix search by regular expression
#query.closeWhereOrClause()
if itemQuery.sourceTables:
query.addWhereIn("cic.source_table", itemQuery.sourceTables)
if itemQuery.analysisStatus is not None:
query.addWhereEqual("ci.analysis_status",
itemQuery.analysisStatus)
query.addWhere(
"ci.item_count <> 0"
) # Also ignore items with no occurence in the analyzed data (occurs if item was accepted for analysis from multi-year dataset, but never used in a sub-time frame's analysis)
if itemQuery.sortField:
query.addOrderBy(itemQuery.sortField)
query.addOrderBy("cic.description")
query.addOrderBy("ci.name")
query.addOrderBy("ci.description")
if itemQuery.resultCount is not None:
query.limit = itemQuery.resultCount
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
return dataModels
finally:
if not extConn:
conn.close()
def deactivateAnalysisByCount(self,
thresholdInstanceCount,
categoryIds=None,
conn=None):
"""Find clinical items to deactivate, based on their instance (patient_item) counts
being too low to be interesting. Can restrict to applying to only items under certain categories.
Use data/analysis/queryItemCounts.py to help guide selections with queries like:
select count(clinical_item_id), sum(item_count)
from clinical_item
where item_count > %s
and clinical_item_category_id in (%s)
(and analysis_status = 1)? Seems like good filter, but the process itself will change this count
Direct search option as below, but that's usually for pre-processing before activations even start.
Former meant to count records that have already gone through analysis.
select clinical_item_id, count(distinct patient_id), count(distinct encounter_id), count(patient_item_id)
from patient_item
group by clinical_item_id
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
# Make sure clinical item instance (patient item) counts are up to date
self.updateClinicalItemCounts(conn=conn)
query = SQLQuery()
query.addSelect("clinical_item_id")
query.addFrom("clinical_item")
if self.maxClinicalItemId is not None: # Restrict to limited / test data
query.addWhereOp("clinical_item_id", "<",
self.maxClinicalItemId)
if categoryIds is not None:
query.addWhereIn("clinical_item_category_id", categoryIds)
query.addWhereOp("item_count", "<=", thresholdInstanceCount)
results = DBUtil.execute(query, conn=conn)
clinicalItemIds = set()
for row in results:
clinicalItemIds.add(row[0])
self.deactivateAnalysis(clinicalItemIds, conn=conn)
finally:
if not extConn:
conn.close()
def querySourceItems(self, startDate=None, endDate=None, progress=None, conn=None):
"""Query the database for list of all source clinical items (orders, etc.)
and yield the results one at a time. If startDate provided, only return items whose order_time is on or after that date.
Ignore entries with instantiated_time not null, as those represent child orders spawned from an original order,
whereas we are more interested in the decision making to enter the original order.
"""
extConn = conn is not None;
if not extConn:
conn = self.connFactory.connection();
# Column headers to query for that map to respective fields in analysis table
queryHeaders = ["op.order_proc_id", "pat_id", "pat_enc_csn_id", "op.order_type", "op.proc_id", "op.proc_code", "description", "order_time","protocol_id","protocol_name","section_name","smart_group"];
headers = ["order_proc_id", "pat_id", "pat_enc_csn_id", "order_type", "proc_id", "proc_code", "description", "order_time","protocol_id","protocol_name","section_name","smart_group"];
query = SQLQuery();
for header in queryHeaders:
query.addSelect( header );
query.addFrom("stride_order_proc as op left outer join stride_orderset_order_proc as os on op.order_proc_id = os.order_proc_id");
query.addWhere("order_time is not null"); # Rare cases of "comment" orders with no date/time associated
query.addWhere("instantiated_time is null");
query.addWhere("(stand_interval is null or stand_interval not like '%%PRN')"); # Ignore PRN orders to simplify somewhat
if startDate is not None:
query.addWhereOp("order_time",">=", startDate);
if endDate is not None:
query.addWhereOp("order_time","<", endDate);
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(), conn=conn)[0][0];
cursor = conn.cursor();
# Do one massive query, but yield data for one item at a time.
cursor.execute( str(query), tuple(query.params) );
row = cursor.fetchone();
while row is not None:
rowModel = RowItemModel( row, headers );
yield rowModel; # Yield one row worth of data at a time to avoid having to keep the whole result set in memory
row = cursor.fetchone();
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close();
if not extConn:
conn.close();
def querySourceItems(self, convOptions, progress=None, conn=None):
"""Query the database for list of all source clinical items (medications, etc.)
and yield the results one at a time. If startDate provided, only return items whose
occurence date is on or after that date.
"""
extConn = conn is not None;
if not extConn:
conn = self.connFactory.connection();
# Column headers to query for that map to respective fields in analysis table
headers = ["stride_treatment_team_id","pat_id","pat_enc_csn_id","trtmnt_tm_begin_date","trtmnt_tm_end_date","treatment_team","prov_name"];
query = SQLQuery();
for header in headers:
query.addSelect( header );
query.addFrom("stride_treatment_team");
if convOptions.startDate is not None:
query.addWhereOp("trtmnt_tm_begin_date",">=", convOptions.startDate);
if convOptions.endDate is not None:
query.addWhereOp("trtmnt_tm_begin_date","<", convOptions.endDate); # Still use begin date as common filter value
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(), conn=conn)[0][0];
cursor = conn.cursor();
# Do one massive query, but yield data for one item at a time.
cursor.execute( str(query), tuple(query.params) );
row = cursor.fetchone();
while row is not None:
rowModel = RowItemModel( row, headers );
for normalizedModel in self.normalizeRowModel(rowModel, convOptions, conn=conn):
yield normalizedModel; # Yield one row worth of data at a time to avoid having to keep the whole result set in memory
row = cursor.fetchone();
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close();
if not extConn:
conn.close();
def loadNotes(self, patientId, currentTime, conn=None):
"""Load notes committed up to the given simulation time.
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
query = SQLQuery()
query.addSelect("sn.sim_note_id")
query.addSelect("sps.sim_patient_id")
# Link
query.addSelect("sn.sim_state_id")
query.addSelect("sn.note_type_id")
query.addSelect("sn.author_type_id")
query.addSelect("sn.service_type_id")
query.addSelect(
"(sps.relative_time_start + sn.relative_state_time) as relative_time"
)
query.addSelect("sn.content")
query.addFrom("sim_note as sn")
query.addFrom("sim_patient_state as sps")
query.addWhere("sn.sim_state_id = sps.sim_state_id")
query.addWhereEqual("sps.sim_patient_id", patientId)
# Only unlock notes once traverse expected time
query.addWhereOp(
"(sps.relative_time_start + sn.relative_state_time)", "<=",
currentTime)
query.addOrderBy(
"(sps.relative_time_start + sn.relative_state_time)")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
return dataModels
finally:
if not extConn:
conn.close()
def calculateResultStats(self, baseName, conn):
"""Query the database for lab results by the given baseName
to calculate several summary statistics.
"""
query = SQLQuery();
query.addSelect("count(ord_num_value) as value_count");
query.addSelect("sum(ord_num_value) as value_sum");
query.addSelect("sum(ord_num_value*ord_num_value) as value_sum_squares");
query.addSelect("max(result_flag) as max_result_flag");
query.addSelect("max(result_in_range_yn) as max_result_in_range");
query.addFrom("stride_order_results");
query.addWhereOp("ord_num_value","<>", SENTINEL_RESULT_VALUE );
query.addWhereEqual("base_name", baseName )
dataTable = DBUtil.execute(query, includeColumnNames=True, conn=conn)
dataModels = modelListFromTable(dataTable);
statModel = dataModels[0]; # Assume that exactly 1 row item will exist
statModel["base_name"] = baseName;
return statModel;
def queryDrugScreens( patientDF, period, locations ):
log.info("Populate drug screens by primary locations");
query = SQLQuery();
query.addSelect("pat_id");
query.addSelect("count(distinct order_proc_id)");
query.addFrom("stride_order_proc_drug_screen");
query.addWhere("ordering_mode = 'Outpatient'");
query.addWhereIn("patient_location", locations );
query.addWhereOp("ordering_date",">", period[0]);
query.addWhereOp("ordering_date","<", period[-1]);
query.addWhereIn("pat_id", patientDF["patientId"] );
query.addGroupBy("pat_id");
results = DBUtil.execute(query);
cols = ["patientId","nDrugScreens"];
newDF = pd.DataFrame(results,columns=cols);
patientDF = patientDF.merge(newDF, how="left");
patientDF["nDrugScreens"][np.isnan(patientDF["nDrugScreens"])] = 0; # Populate default values if no data
patientDF["nDrugScreens"] = patientDF["nDrugScreens"].astype("int"); # Beware of float conversion somewhere
return patientDF;
def loadPendingResultOrders(self, patientId, relativeTime, conn=None):
"""Load all patient orders at the given relativeTime that
are due to yield results, but have not yet.
Include an estimate of time until results available.
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
query = SQLQuery()
query.addSelect(
"distinct po.clinical_item_id"
) # Distinct so don't report multiple times for panel orders
query.addSelect("po.relative_time_start")
query.addSelect("po.relative_time_end")
query.addSelect("ci.name")
query.addSelect("ci.description")
query.addSelect(
"sorm.turnaround_time"
) # Could have different turnaround times for single order if different sub results. Just report each.
query.addSelect(
"sorm.turnaround_time - (%d - po.relative_time_start) as time_until_result"
% relativeTime) # Calculate time until expect result
query.addFrom("sim_patient_order as po")
query.addFrom("clinical_item as ci")
query.addFrom("sim_order_result_map as sorm")
query.addWhere("po.clinical_item_id = ci.clinical_item_id")
query.addWhere("po.clinical_item_id = sorm.clinical_item_id")
query.addWhereEqual("sim_patient_id", patientId)
# Only catch orders up to the given relativeTime and not cancelled
query.addWhereOp("relative_time_start", "<=", relativeTime)
query.openWhereOrClause()
query.addWhere("relative_time_end is null")
query.addWhereOp("relative_time_end", ">", relativeTime)
query.closeWhereOrClause()
# Only PENDING orders, so don't report orders who results should already be available
query.addWhereOp("sorm.turnaround_time + po.relative_time_start",
">", relativeTime)
query.addOrderBy("time_until_result")
query.addOrderBy("relative_time_start")
query.addOrderBy("ci.name")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
return dataModels
finally:
if not extConn:
conn.close()
def queryPatientItemsPerPatient(self, analysisOptions, progress=None, conn=None):
"""Query the database for an ordered list of patient clinical items,
in the order in which they occurred.
This could be a large amount of data, so option to provide
list of specific patientIds or date ranges to query for. In either case,
results will be returned as an iterator over individual lists
for each patient. Lists will contain RowItemModels, each with data:
* patient_id
* encounter_id
* clinical_item_id
* item_date
* analyze_date
"""
extConn = conn is not None;
if not extConn:
conn = self.connFactory.connection();
# Reset for actual data selects
query= SQLQuery();
query.addSelect("pi.patient_item_id");
query.addSelect("pi.patient_id");
query.addSelect("pi.encounter_id");
query.addSelect("pi.clinical_item_id");
query.addSelect("pi.item_date");
query.addSelect("pi.analyze_date");
query.addFrom("patient_item as pi");
query.addFrom("clinical_item as ci");
query.addWhere("pi.clinical_item_id = ci.clinical_item_id");
query.addWhere("ci.analysis_status <> 0"); # Skip steps designated to be ignored
if analysisOptions.patientIds is not None:
query.addWhereIn("patient_id", analysisOptions.patientIds );
if analysisOptions.startDate is not None:
query.addWhereOp("pi.item_date",">=", analysisOptions.startDate);
if analysisOptions.endDate is not None:
query.addWhereOp("pi.item_date","<", analysisOptions.endDate);
query.addOrderBy("pi.patient_id");
query.addOrderBy("pi.item_date");
query.addOrderBy("pi.clinical_item_id");
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(), conn=conn)[0][0];
cursor = conn.cursor();
# Do one massive query, but yield data for one patient at a time.
# This should minimize the number of DB queries and the amount of
# data that must be kept in memory at any one time.
cursor.execute( str(query), tuple(query.params) );
currentPatientId = None;
currentPatientData = list();
headers = ["patient_item_id","patient_id","encounter_id","clinical_item_id","item_date","analyze_date"];
row = cursor.fetchone();
while row is not None:
(patient_item_id, patientId, encounter_id, clinicalItemId, itemDate, analyzeDate) = row;
if currentPatientId is None:
currentPatientId = patientId;
if patientId != currentPatientId:
# Changed user, yield the existing data for the previous user
yield currentPatientData;
# Update our data tracking for the current user
currentPatientId = patientId;
currentPatientData = list();
rowModel = RowItemModel( row, headers );
currentPatientData.append( rowModel );
row = cursor.fetchone();
# Yield the final user's data
yield currentPatientData;
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close();
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)
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()
def loadPatientInfo(self, patientIds=None, relativeTime=None, conn=None):
"""Load basic information about the specified patients.
Report patient state at given time, or default to time zero
"""
if relativeTime is None:
relativeTime = 0
# Just look for time zero default then
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
query = SQLQuery()
query.addSelect("sp.sim_patient_id")
query.addSelect("sp.name")
query.addSelect("sp.age_years")
query.addSelect("sp.gender")
query.addSelect("s.sim_state_id")
query.addSelect("s.name as state_name")
query.addSelect("s.description as state_description")
query.addSelect("sps.relative_time_start")
query.addSelect("sps.relative_time_end")
query.addFrom("sim_patient as sp")
query.addFrom("sim_patient_state as sps")
query.addFrom("sim_state as s")
query.addWhere("sp.sim_patient_id = sps.sim_patient_id")
query.addWhere("sps.sim_state_id = s.sim_state_id")
if patientIds is not None:
query.addWhereIn("sp.sim_patient_id", patientIds)
# Look for the state that matches the given relative time offset
query.addWhereOp("sps.relative_time_start", "<=", relativeTime)
query.openWhereOrClause()
query.addWhere("sps.relative_time_end is null")
query.addWhereOp("sps.relative_time_end", ">", relativeTime)
query.closeWhereOrClause()
query.addOrderBy("sp.name")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
if len(dataModels) > 0:
# Secondary query to build lookup table of possible state transition options from patient current states
subQuery = SQLQuery()
subQuery.addSelect("pre_state_id")
subQuery.addSelect("post_state_id")
subQuery.addSelect("clinical_item_id")
subQuery.addSelect("time_trigger")
subQuery.addFrom("sim_state_transition as sst")
subQuery.addWhereIn(
"pre_state_id",
columnFromModelList(dataModels, "sim_state_id"))
subResults = DBUtil.execute(subQuery, conn=conn)
# For each pre-state, track which clinical items or times trigger which post-states
postStateIdByItemIdByPreStateId = dict()
postStateIdTimeTriggerByPreStateId = dict()
for preStateId, postStateId, itemId, timeTrigger in subResults:
if preStateId not in postStateIdByItemIdByPreStateId:
postStateIdByItemIdByPreStateId[preStateId] = dict()
postStateIdByItemIdByPreStateId[preStateId][
itemId] = postStateId
if timeTrigger is not None:
postStateIdTimeTriggerByPreStateId[preStateId] = (
postStateId, timeTrigger)
# Record in patient result models for retrieval
for i, dataModel in enumerate(dataModels):
patientId = dataModel["sim_patient_id"]
stateId = dataModel["sim_state_id"]
dataModel["postStateIdByItemId"] = dict()
if stateId in postStateIdByItemIdByPreStateId:
dataModel[
"postStateIdByItemId"] = postStateIdByItemIdByPreStateId[
stateId]
dataModel["postStateIdTimeTriggerByPreStateId"] = dict()
if stateId in postStateIdTimeTriggerByPreStateId:
dataModel[
"postStateIdTimeTrigger"] = postStateIdTimeTriggerByPreStateId[
stateId]
if dataModel[
"relative_time_end"] is None and "postStateIdTimeTrigger" in dataModel:
# Check that we haven't passed (and should thus trigger) a time-based state transition
(postStateId,
timeTrigger) = dataModel["postStateIdTimeTrigger"]
preStateTime = dataModel["relative_time_start"]
postStateTriggerTime = (preStateTime + timeTrigger)
if postStateTriggerTime <= relativeTime: # Trigger state transition just by time elapsed
#print >> sys.stderr, relativeTime, preStateTime, stateId, postStateTriggerTime, postStateId
self.recordStateTransition(patientId,
stateId,
postStateId,
postStateTriggerTime,
conn=conn)
# State change which can yield new triggers, so recursively reload.
# Small risk of infinite recusion if timeTriggers are zero. Otherwise, should converge as each recursion will update the preState relativeTimeStart
dataModels[i] = self.loadPatientInfo([patientId],
relativeTime,
conn=conn)[0]
return dataModels
finally:
if not extConn:
conn.close()
def queryMixSourceItems(self,
rxcuiDataByMedId,
convOptions,
progress=None,
conn=None):
"""Query the database for list of source clinical items (medications from mixes, etc.)
and yield the results one at a time.
"""
extConn = conn is not None
if not extConn:
conn = self.connFactory.connection()
# Column headers to query for that map to respective fields in analysis table
queryHeaders = [
"med.order_med_id", "med.pat_id", "med.pat_enc_csn_id",
"mix.medication_id", "mix.medication_name", "mix.ingredient_type",
"med.ordering_date", "med.med_route", "med.number_of_doses",
"protocol_id", "protocol_name", "section_name", "smart_group"
]
headers = [
"order_med_id", "pat_id", "pat_enc_csn_id", "medication_id",
"description", "ingredient_type", "ordering_date", "med_route",
"number_of_doses", "protocol_id", "protocol_name", "section_name",
"smart_group"
]
query = SQLQuery()
for header in queryHeaders:
query.addSelect(header)
query.addFrom(
"stride_order_med as med left outer join stride_orderset_order_med as os on med.order_med_id = os.order_med_id"
)
# Grab order set links if they exist
query.addFrom("stride_order_medmixinfo as mix")
query.addWhere("med.order_med_id = mix.order_med_id")
#query.addWhereEqual("med.medication_id", TEMPLATE_MEDICATION_ID );
#query.addWhere("mix.line = 1"); # Just take the first item from a mix
query.addWhere("freq_name not like '%%PRN'")
# Ignore PRN orders
if convOptions.startDate is not None:
query.addWhereOp("ordering_date", ">=", convOptions.startDate)
if convOptions.endDate is not None:
query.addWhereOp("ordering_date", "<", convOptions.endDate)
query.addOrderBy("med.ordering_date, med.order_med_id, mix.line")
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(),
conn=conn)[0][0]
cursor = conn.cursor()
# Do one massive query, but yield data for one item at a time.
cursor.execute(str(query), tuple(query.params))
# Accumulate mixture components one item at a time
mixByOrderMedId = dict()
row = cursor.fetchone()
while row is not None:
rowModel = RowItemModel(row, headers)
orderMedId = rowModel["order_med_id"]
if orderMedId not in mixByOrderMedId: # New medication mix encountered. Process any prior ones before moving on
for normalizedModel in self.normalizeMixData(
rxcuiDataByMedId, mixByOrderMedId, convOptions):
yield normalizedModel
# Yield one row worth of data at a time to avoid having to keep the whole result set in memory
mixByOrderMedId.clear()
# Discard previously processed mixes so don't have a ton left in memory
mixByOrderMedId[orderMedId] = list()
# Prep for next mix
mixByOrderMedId[orderMedId].append(rowModel)
row = cursor.fetchone()
# One more pass for remaining items
for normalizedModel in self.normalizeMixData(rxcuiDataByMedId,
mixByOrderMedId,
convOptions):
yield normalizedModel
# Yield one row worth of data at a time to avoid having to keep the whole result set in memory
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close()
if not extConn:
conn.close()
def querySourceItems(self,
rxcuiDataByMedId,
convOptions,
progress=None,
conn=None):
"""Query the database for list of all source clinical items (medications, etc.)
and yield the results one at a time. If startDate provided, only return items whose
ordering_date is on or after that date.
"""
extConn = conn is not None
if not extConn:
conn = self.connFactory.connection()
# Column headers to query for that map to respective fields in analysis table
queryHeaders = [
"med.order_med_id", "pat_id", "pat_enc_csn_id",
"med.medication_id", "med.description", "ordering_date",
"med_route", "number_of_doses", "protocol_id", "protocol_name",
"section_name", "smart_group"
]
headers = [
"order_med_id", "pat_id", "pat_enc_csn_id", "medication_id",
"description", "ordering_date", "med_route", "number_of_doses",
"protocol_id", "protocol_name", "section_name", "smart_group"
]
query = SQLQuery()
for header in queryHeaders:
query.addSelect(header)
query.addFrom(
"stride_order_med as med left outer join stride_orderset_order_med as os on med.order_med_id = os.order_med_id"
)
# Grab order set links if they exist
query.addWhere("med.medication_id <> %s" % TEMPLATE_MEDICATION_ID)
query.addWhere("freq_name not like '%%PRN'")
# Ignore PRN orders
if convOptions.startDate is not None:
query.addWhereOp("ordering_date", ">=", convOptions.startDate)
if convOptions.endDate is not None:
query.addWhereOp("ordering_date", "<", convOptions.endDate)
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(),
conn=conn)[0][0]
cursor = conn.cursor()
# Do one massive query, but yield data for one item at a time.
cursor.execute(str(query), tuple(query.params))
row = cursor.fetchone()
while row is not None:
rowModel = RowItemModel(row, headers)
for normalizedModel in self.normalizeMedData(
rxcuiDataByMedId, rowModel, convOptions):
yield normalizedModel
# Yield one row worth of data at a time to avoid having to keep the whole result set in memory
row = cursor.fetchone()
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close()
if not extConn:
conn.close()
def querySourceItems(self, startDate=None, endDate=None, progress=None, conn=None):
"""Query the database for list of all source clinical items (diagnosed probelms in this case)
and yield the results one at a time. If startDate provided, only return items
whose noted_date is on or after that date.
"""
extConn = conn is not None;
if not extConn:
conn = self.connFactory.connection();
# Facilitate rapid lookup of ICD9/ICD10 codes
if self.icd9_str_by_code is None:
self.prepare_icd9_lookup(conn=conn)
if self.icd10_str_by_code is None:
self.prepare_icd10_lookup(conn=conn)
# Column headers to query for that map to respective fields in analysis table
headers = ["pat_id","pat_enc_csn_id","noted_date","resolved_date","dx_icd9_code","dx_icd9_code_list","dx_icd10_code_list","data_source"];
query = SQLQuery();
for header in headers:
query.addSelect( header );
query.addFrom("stride_dx_list as dx");
query.addWhere("noted_date is not null"); # Only work with elements that have dates assigned for now
if startDate is not None:
query.addWhereOp("noted_date",">=", startDate);
if endDate is not None:
query.addWhereOp("noted_date","<", endDate);
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(), conn=conn)[0][0];
cursor = conn.cursor();
# Do one massive query, but yield data for one item at a time.
cursor.execute( str(query), tuple(query.params) );
row = cursor.fetchone();
while row is not None:
row_model = RowItemModel( row, headers );
# 2014-2017 data does not have dx_icd9_code. Instead, has
# both dx_icd9_code_list and dx_icd10_code_list. For these items,
# there is a one:many mapping of source item to converted item.
# Collect icd10 codes.
icd10_codes = set()
if row_model['dx_icd10_code_list'] not in ['', None]:
codes = row_model['dx_icd10_code_list'].split(',')
for code in codes:
icd10_codes.add(code)
# Collect icd9 codes.
icd9_codes = set()
if row_model['dx_icd9_code'] not in ['', None]:
icd9_codes.add(row_model['dx_icd9_code'])
else:
if row_model['dx_icd9_code_list'] not in ['', None]:
codes = row_model['dx_icd9_code_list'].split(',')
for code in codes:
icd9_codes.add(code)
# If there are no ICD codes, skip to next row.
if len(icd9_codes) == 0 and len(icd10_codes) == 0:
row = cursor.fetchone()
continue
# Process ICD codes.
# Build a temporary dictionary so that a single loop can take care
# of both ICD9 and ICD10 without mixing the data.
icd_versions = {
'ICD9': {
'codes': icd9_codes,
'lookup': self.icd9_str_by_code
},
'ICD10': {
'codes': icd10_codes,
'lookup': self.icd10_str_by_code
}
}
for version, info in icd_versions.iteritems():
icd_codes = info['codes']
icd_lookup = info['lookup']
for icd_code in icd_codes:
# Look up string. Otherwise default to ICD code.
row_model['icd_str'] = icd_code
if icd_code in icd_lookup:
row_model['icd_str'] = icd_lookup[icd_code]
row_model['dx_icd_code'] = version + '.' + icd_code
# Yield one row worth of data at a time to avoid having to keep
# the whole result set in memory.
yield row_model
orig_code = icd_code
if SUBCODE_DELIM in orig_code:
# Insert copies of item for parent node codes to aggregate
# component diagnoses into general categories.
while icd_code[-1] != SUBCODE_DELIM:
icd_code = icd_code[:-1] # Truncate trailing digit
if icd_code in icd_lookup:
# Found a a matching parent code, so yield this
# version.
row_model['icd_str'] = icd_lookup[icd_code]
row_model['dx_icd_code'] = version + '.' + icd_code
yield row_model
# One more cycle to get parent node with no subcode
# delimiter at all.
icd_code = icd_code[:-1] # Truncate off SUBCODE_DELIM
if icd_code in icd_lookup:
row_model['icd_str'] = icd_lookup[icd_code]
row_model['dx_icd_code'] = version + '.' + icd_code
yield row_model
row = cursor.fetchone();
progress.Update();
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close();
if not extConn:
conn.close();
def main(argv):
medIdsByActiveRx = dict()
medIdsByActiveRx['Buprenorphine'] = ('125498', '114474', '212560',
'114475', '114467', '114468')
medIdsByActiveRx['Fentanyl Patch'] = ('2680', '27908', '125379', '27905',
'27906', '540107', '540638',
'540101', '27907')
medIdsByActiveRx['Methadone'] = ('540483', '4953', '4951', '10546',
'214468', '15996', '41938', '4954',
'4952')
medIdsByActiveRx['Hydrocodone'] = ('3724', '4579', '8576', '8577', '8951',
'10204', '12543', '13040', '14963',
'14965', '14966', '17061', '17927',
'19895', '20031', '28384', '29486',
'29487', '34505', '34544', '35613',
'117862', '204249', '206739')
medIdsByActiveRx['Hydromorphone'] = ('2458', '2459', '2464', '2465',
'3757', '3758', '3759', '3760',
'3761', '10224', '10225', '10226',
'10227', '200439', '201094', '201096',
'201098', '540125', '540179',
'540666')
medIdsByActiveRx['Morphine'] = (
'5167', '5168', '5172', '5173', '5176', '5177', '5178', '5179', '5180',
'5183', '6977', '10655', '15852', '20908', '20909', '20910', '20914',
'20915', '20919', '20920', '20921', '20922', '29464', '30138', '31413',
'36140', '36141', '79691', '87820', '89282', '91497', '95244', '96810',
'112562', '112564', '115335', '115336', '126132', '198543', '198544',
'198623', '201842', '201848', '205011', '206731', '207949', '208896',
'540182', '540300')
medIdsByActiveRx['Oxycodone'] = ('5940', '5941', '6122', '6981', '10812',
'10813', '10814', '14919', '16121',
'16123', '16129', '16130', '19187',
'26637', '26638', '27920', '27921',
'27922', '27923', '28897', '28899',
'28900', '31851', '31852', '31863',
'31864', '92248', '126939', '200451',
'203690', '203691', '203692', '203705',
'203706', '203707', '204020', '204021')
query = baseQuery()
totalPatients = float(DBUtil.execute(query)[0][0])
# print"Total Patients\t%s" % totalPatients
# print"======= Dx Groups ===========";
# print"Dx Group\tPt Count\tDx Rate";
patientsPerDxGroup = dict()
query = SQLQuery()
query.addSelect("count(distinct prob.pat_id) as ptCount")
query.addSelect("prob.%s" % DX_COL)
query.addFrom("stride_problem_list as prob")
query.addWhereOp("prob.noted_date", "<", LIMIT_DATE)
query.addGroupBy("prob.%s" % DX_COL)
if DOUBLE_DX:
query.addSelect("prob2.%s" % DX_COL)
query.addFrom("stride_problem_list as prob2")
query.addWhere("prob.pat_id = prob2.pat_id")
query.addWhereOp("prob2.noted_date", "<", LIMIT_DATE)
query.addGroupBy("prob2.%s" % DX_COL)
results = DBUtil.execute(query)
for row in results:
patientCount = row[0]
dxGroup = row[1]
if DOUBLE_DX:
dxGroup = (dxGroup, row[2])
# Composite tuple including second diagnosis
patientsPerDxGroup[dxGroup] = patientCount
progress = ProgressDots()
for activeRx, medIds in medIdsByActiveRx.iteritems():
query = baseQuery()
query.addWhereIn("medication_id", medIds)
# Baseline prescription rates
rxPtCount = DBUtil.execute(query)[0][0]
# print"====== Rx Counts ======";
# print"Rx\tPt Count\tRx Rate";
# print"%s\t%s\t%s" % (activeRx, rxPtCount, (rxPtCount/totalPatients));
# print"======== Rx-Dx Association ========";
statIds = (
"P-Fisher",
"P-YatesChi2",
"oddsRatio",
"relativeRisk",
"interest",
"LR+",
"LR-",
"sensitivity",
"specificity",
"PPV",
"NPV",
)
if progress.getCounts() == 0:
headerCols = [
"Rx", "Dx", "RxDxCount", "RxCount", "DxCount", "Total"
]
if DOUBLE_DX:
headerCols.insert(2, "Dx2")
headerCols.extend(statIds)
headerStr = str.join("\t", headerCols)
print headerStr
# Query out per diagnosis group, but do as aggregate grouped query
query.addSelect("prob.%s" % DX_COL)
query.addFrom("stride_problem_list as prob")
query.addWhere("med.pat_id = prob.pat_id")
query.addWhereOp("prob.noted_date", "<", LIMIT_DATE)
#query.addWhereIn("prob.%s" % DX_COL, dxKeys );
query.addGroupBy("prob.%s" % DX_COL)
if DOUBLE_DX:
query.addSelect("prob2.%s" % DX_COL)
query.addFrom("stride_problem_list as prob2")
query.addWhere("prob.pat_id = prob2.pat_id")
query.addWhereOp("prob2.noted_date", "<", LIMIT_DATE)
query.addGroupBy("prob2.%s" % DX_COL)
results = DBUtil.execute(query)
for row in results:
rxDxPtCount = row[0]
dxGroup = row[1]
if DOUBLE_DX:
dxGroup = (dxGroup, row[2])
# Composite tuple including second diagnosis
dxPtCount = patientsPerDxGroup[dxGroup]
conStats = ContingencyStats(rxDxPtCount, rxPtCount, dxPtCount,
totalPatients)
dataCells = [
activeRx, dxGroup, rxDxPtCount, rxPtCount, dxPtCount,
totalPatients
]
if DOUBLE_DX:
dataCells[1] = dxGroup[0]
dataCells.insert(2, dxGroup[1])
for statId in statIds:
try:
dataCells.append(conStats[statId])
except ZeroDivisionError:
dataCells.append(None)
for i, value in enumerate(dataCells):
dataCells[i] = str(value)
# String conversion to allow for concatenation below
dataStr = str.join("\t", dataCells)
print dataStr
progress.update()
progress.printStatus()
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 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 baseQuery():
query = SQLQuery()
query.addSelect("count(distinct med.pat_id) as ptCount")
query.addFrom("stride_order_med as med")
query.addWhereOp("ordering_datetime", "<", LIMIT_DATE)
return query
