in clns-eTarget_ingest/targetdata.py [0:0]
def updateDatabaseBloodReport(self, patientDetails, filename, updateSelection, prev_gene_panel_id):
# Check if patient exists
checkSQL = "SELECT * FROM dbo.PERSON WHERE target_id='"+patientDetails['patientID']+"'"
cursor = self.conn.cursor()
cursor.execute(checkSQL)
foundPerson = 'No'
personID = 0
for row in cursor:
foundPerson = 'Yes'
personID = row[0]
self.log.logMessage('Is patient '+patientDetails['patientID']+' in the database? '+foundPerson)
if foundPerson == 'Yes':
findGeneConceptID = "SELECT data_source_concept_id from CONCEPT_DATA_SOURCES WHERE panel_name='"+patientDetails['ngsSampleType']+"'"
cursor = self.conn.cursor()
cursor.execute(findGeneConceptID)
row=cursor.fetchone()
geneConceptID=0
if row is not None:
geneConceptID=row[0]
else:
insertGeneConcept = "INSERT INTO CONCEPT_DATA_SOURCES (panel_name) VALUES('"+patientDetails['ngsSampleType']+"')"
cursor = self.conn.cursor()
cursor.execute(insertGeneConcept)
geneConceptID=cursor.lastrowid
# Get the lastest blood specimen to attach the report to
#specimenSQL = "SELECT TOP 1 * FROM SPECIMEN WHERE person_id = "+str(personID)+" AND specimen_concept_id=1 ORDER BY specimen_date DESC"
specimenSQL = "SELECT * FROM SPECIMEN WHERE person_id = "+str(personID)+" AND specimen_concept_id=1 AND baseline_number = "+str(patientDetails['baseline'])+" ORDER BY specimen_date DESC"
cursor = self.conn.cursor()
cursor.execute(specimenSQL)
row = cursor.fetchone()
#print('row count for '+patientDetails['patientID']+' is: '+str(row.rowcount))
# Check a result has been returned (if not there is no blood sample in the database for this person)
if row is not None:
specimenID = 0
specimenID = row[0]
self.log.logMessage('Adding report for person ID '+str(personID)+' ('+patientDetails['patientID']+') against specimen_id '+str(specimenID))
# Update the existing patient data from the file data
cfdnaColourKey = patientDetails['colourGreenCFDNA']+';'+patientDetails['colourYellowCFDNA']+';'+patientDetails['colourRedCFDNA']
averageColourKey = patientDetails['colourGreenReadDepth']+';'+patientDetails['colourYellowReadDepth']+';'+patientDetails['colourRedReadDepth']
# Record whether analysis failed
analysisFailed = 'false'
if patientDetails['geneData'][1][0] == 'Failed':
# Analysis failed
analysisFailed = 'true'
# Record if there were no mutations found
noMutations = 'false'
if patientDetails['geneData'][1][0] == 'None':
# No mutations found
noMutations = 'true'
# Update MEASUREMENT_GENE_PANEL table
update1Cursor = self.conn.cursor()
update1SQL = "INSERT INTO MEASUREMENT_GENE_PANEL (specimen_id, data_source_concept_id, average_read_depth, bioinformatics_pipeline, ngs_library_cfdna_input, ngs_run, level_of_detection, cfdna_input_colour_key, average_read_depth_colour_key, analysis_failed, no_mutations_found, ngs_comment, exploratory_comment, run_number, baseline_number) VALUES('"+str(specimenID)+"', '"+str(geneConceptID)+"', '"+patientDetails['averageReadDepth']+"', '"+patientDetails['pipelineVersion']+"', '"+patientDetails['ngsLibraryCFDNAInput']+"', '"+patientDetails['ngsRun']+"', '"+patientDetails['detectionLevel']+"', '"+cfdnaColourKey+"', '"+averageColourKey+"', '"+analysisFailed+"', '"+noMutations+"', '"+patientDetails['ngsComment']+"', '"+patientDetails['exploratoryComment']+"', '"+patientDetails['runNumber']+"', "+str(patientDetails['baseline'])+")"
update1SQLID = 0
try:
update1Cursor.execute(update1SQL)
#self.log.logMessage('Updated MEASUREMENT_GENE_PANEL with '+str(update1Cursor.rowcount)+' row(s)')
update1SQLID = update1Cursor.lastrowid
self.log.logMessage('Created MEASUREMENT_GENE_PANEL record ID: '+str(update1SQLID))
except Exception as e:
self.log.logMessage('Database MEASUREMENT_GENE_PANEL update failed, exiting...')
self.log.logMessage('Error message: ')
self.log.logMessage(str(e))
# Update MEASUREMENT_GENE_VARIANT table
if patientDetails['geneData'][1][0] != 'Failed' and patientDetails['geneData'][1][0] != 'None':
for i in patientDetails['geneData']:
if "sep-" in patientDetails['geneData'][i][0].lower():
patientDetails['geneData'][i][0]=patientDetails['geneData'][i][0].lower().replace('sep-0','SEPT');
patientDetails['geneData'][i][0]=patientDetails['geneData'][i][0].replace('sep-','SEPT');
# Mutations found, add the gene data for the patient
selectGeneCursor = self.conn.cursor()
selectGeneCursor.execute("SELECT * FROM CONCEPT_GENE WHERE gene_name = '"+patientDetails['geneData'][i][0]+"'")
gene_concept_id = ''
# If the gene exists in the table, get the ID, else add it to the table and use the ID
selectGeneCursorRowcount = 0
gene_concept_id = 0
for row in selectGeneCursor:
selectGeneCursorRowcount = selectGeneCursorRowcount+1
gene_concept_id = row[0]
if selectGeneCursorRowcount == 0:
selectGeneCursor = self.conn.cursor()
selectGeneCursor.execute("INSERT INTO CONCEPT_GENE (gene_name) VALUES('"+patientDetails['geneData'][i][0]+"')")
gene_concept_id = selectGeneCursor.lastrowid
update2Cursor = self.conn.cursor()
update2SQL = "INSERT INTO MEASUREMENT_GENE_VARIANT (measurement_gene_panel_id, gene_concept_id, cosmic_url, variant_allele_frequency, read_depth, germline_frequency, functional_effect, cdna_change, amino_acid_change, chromosome, high_confidence, ngs_gene_present, is_specific_mutation_in_panel, position) VALUES("+str(update1SQLID)+", '"+str(gene_concept_id)+"', '"+patientDetails['geneData'][i][11].replace("'",'"')+"', '"+patientDetails['geneData'][i][4]+"', '"+patientDetails['geneData'][i][5]+"', '"+patientDetails['geneData'][i][6]+"', '"+patientDetails['geneData'][i][7]+"', '"+patientDetails['geneData'][i][2]+"', '"+patientDetails['geneData'][i][3]+"', '"+patientDetails['geneData'][i][1]+"', '"+patientDetails['geneData'][i][8]+"', '"+patientDetails['geneData'][i][9]+"', '"+patientDetails['geneData'][i][10]+"', '"+patientDetails['geneData'][i][12]+"')"
#print(patientDetails['geneData'][i][12])
try:
update2Cursor.execute(update2SQL)
#self.log.logMessage('Updated MEASUREMENT_GENE_VARIANT with '+str(update2Cursor.rowcount)+' row(s)')
measurement_gene_variant_id = update2Cursor.lastrowid
self.log.logMessage('Created MEASUREMENT_GENE_VARIANT record ID: '+str(measurement_gene_variant_id))
except Exception as e:
self.log.logMessage('Database MEASUREMENT_GENE_VARIANT update failed, exiting...')
self.log.logMessage('Error message: ')
self.log.logMessage(str(e))
# new version of old blood report keep selections
if updateSelection == True:
findPreviouseSelectionQuery="select person_id from SELECTED_GENE_VARIANT FULL OUTER JOIN MEASUREMENT_GENE_VARIANT on MEASUREMENT_GENE_VARIANT.measurement_gene_variant_id=SELECTED_GENE_VARIANT.measurement_gene_variant_id FULL OUTER JOIN MEASUREMENT_GENE_PANEL on MEASUREMENT_GENE_PANEL.measurement_gene_panel_id=MEASUREMENT_GENE_VARIANT.measurement_gene_panel_id where person_id="+str(personID)+" and type='CTDNA' and baseline_number="+str(patientDetails['baseline'])+" and run_number="+str(patientDetails['runNumber'])+" and MEASUREMENT_GENE_PANEL.measurement_gene_panel_id=" +str(prev_gene_panel_id)+ " and position='"+patientDetails['geneData'][i][12] +"';"
findPreviousSelectionCursor = self.conn.cursor()
findPreviousSelectionCursor.execute(findPreviouseSelectionQuery)
row=findPreviousSelectionCursor.fetchone()
findPreviousSelectionCursor.close()
if row is not None:
#was previously selected
insertSelectionQuery="insert into SELECTED_GENE_VARIANT (person_id, measurement_gene_variant_id, type) values("+str(personID)+","+str(measurement_gene_variant_id)+",'CTDNA');"
insertSelectionCursor = self.conn.cursor()
insertSelectionCursor.execute(insertSelectionQuery)
insertSelectionCursor.close()
# Insert report data
reportIssued = self.formatDate(patientDetails['reportIssued'])
update3Cursor = self.conn.cursor()
update3SQL = "INSERT INTO REPORT (date_issued, measurement_gene_panel_id) VALUES('"+reportIssued+"','"+str(update1SQLID)+"')"
try:
update3Cursor.execute(update3SQL)
#self.log.logMessage('Updated REPORT with '+str(update3Cursor.rowcount)+' row(s)')
update3SQLID = update3Cursor.lastrowid
self.log.logMessage('Created REPORT record ID: '+str(update3SQLID))
except Exception as e:
self.log.logMessage('Database REPORT update failed.')
self.log.logMessage('Error message: ')
self.log.logMessage(str(e))
# Delete the file once processed
if update1Cursor.rowcount > 0 and update3Cursor.rowcount > 0:
# All should be OK, we can commit changes.
self.conn.commit()
# Both updates were successful...
# Delete the file
self.log.logMessage('Deleting '+filename)
try:
#os.remove(self.filepath+filename)
self.file_service.delete_file(self.data, None, filename)
self.log.systemStatusUpdate(filename, 'CEP', self.timestamp(), 'Success')
self.conn.commit()
except:
self.log.logMessage('There was a problem deleting '+filename)
else:
self.log.logMessage('Error: The cfDNA blood report did not update properly in the database for '+patientDetails['patientID'])
self.log.systemStatusUpdate(filename, 'CEP', self.timestamp(), 'Error: Data import failed')
self.conn.rollback()
else:
self.log.logMessage(' ######## ERROR: There is no blood sample for patient '+patientDetails['patientID']+' ########')
self.log.logMessage(' ######## Skipping import for this patient '+patientDetails['patientID']+' ########')
self.log.systemStatusUpdate(filename, 'CEP', self.timestamp(), 'Error: No specimen data')
self.conn.rollback()
else:
self.log.logMessage(" ######## ERROR: Patient "+patientDetails['patientID']+" doesn't exist. ########")
self.log.systemStatusUpdate(filename, 'CEP', self.timestamp(), 'Error: Patient is not present in the database')
self.conn.rollback()