__author__ = 'Sulantha'
import csv
from SQLDB.DBClient import DBClient
DBClient = DBClient()
CSFFile = 'CSVFiles/CSV_SUBJECT_CSF_DATA.csv'
with open(CSFFile, 'rU') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
next(csv_reader)
for line in csv_reader:
sql = "INSERT OR REPLACE INTO CSV_SUBJECT_CSF_DATA VALUES (NULL, '{0}', '{1}', '{2}', '{3}', '{4}')".format(line[0], line[1], line[2], line[3], line[4])
DBClient.executeNoResult(sql)
__author__ = 'Sulantha'
from SQLDB.DBClient import DBClient
import csv
newPETCSV = 'CSVFiles/CSV_RESULT_MCI_CONV_DATA.csv'
DBC = DBClient()
getAllMCIAtanyPointRIDs = "SELECT DISTINCT RID FROM CSV_SUBJECT_DX_DATA WHERE DX_Current_Code_TNL = 'MCI'"
allRIDs = DBC.executeAllRes(getAllMCIAtanyPointRIDs)
finalList = [['RID', 'DX_BL', 'REC_ID_1', 'REC_ID_2', 'VISCODE_1', 'VISCODE_2', 'EXAM_DATE_1', 'EXAM_DATE_2', 'DX_1', 'DX_2', 'CONV']]
for rid in allRIDs:
allSubjectINFOSQL = "SELECT * FROM CSV_SUBJECT_DX_DATA WHERE RID = '{0}' ORDER BY VISCODE ASC".format(rid[0])
allSubjectINFO = DBC.executeAllRes(allSubjectINFOSQL)
subRecs = []
for subRec in allSubjectINFO:
subRecs.append(subRec)
if len(subRecs) == 1:
print("Only one rec found. Skipping. - {0}".format(subRecs[0]))
for rec in subRecs:
vis = 0 if rec[2] == 'bl' else int(rec[2][1:])
vis24_str = 'm{0}'.format(vis+24)
visit24_rec = None
for newRec in subRecs:
if newRec[2] == vis24_str:
visit24_rec = newRec
break
if visit24_rec:
if rec[21] == 'MCI':
__author__ = 'Sulantha'
from SQLDB.DBClient import DBClient
DBClient = DBClient()
getAllRecsSQL = "SELECT RID FROM CSV_SUBJECT_DX_DATA"
dxCodeDict = {"NL":1, "Dementia":3,"MCI":2}
dxSTRDict = {1:'CN', 3:'AD', 2:'MCI'}
allRes = DBClient.executeAllRes(getAllRecsSQL)
for rec in allRes:
subRID = rec[0]
allRecsForSub = "SELECT * FROM CSV_SUBJECT_DX_DATA WHERE RID = '{0}' ORDER BY VISCODE ASC".format(subRID)
subResults = DBClient.executeAllRes(allRecsForSub)
subDXList = []
for subRec in subResults:
dx = subRec[19]
if dx == '':
subDXList.append(0)
continue
if 'to' in dx:
dx = dx.split('to')[1].strip()
dxCode = dxCodeDict[dx]
subDXList.append(dxCode)
allRecsForSub = "SELECT * FROM CSV_SUBJECT_DX_DATA WHERE RID = '{0}' ORDER BY VISCODE ASC".format(subRID)
subResults = DBClient.executeAllRes(allRecsForSub)
idx = 0
import datetime
from SQLDB.DBClient import DBClient
dbc = DBClient()
getRescSQL = "SELECT * FROM CSV_RESULT_MCI_CONV_DATA"
allResults = dbc.executeAllRes(getRescSQL)
for rec in allResults:
rec_id = rec[0]
rid = rec[1]
d1 = datetime.datetime.strptime(rec[7], '%Y-%m-%d')
d2 = datetime.datetime.strptime(rec[8], '%Y-%m-%d')
PETRecsSQL = "SELECT * FROM CSV_SUBJECT_PET_DATA WHERE RID = '{0}' AND SEQ_CODE = '{1}'".format(rid, 'TNL_PP')
petData = dbc.executeAllRes(PETRecsSQL)
allPETRECs = []
for petRec in petData:
allPETRECs.append([i for i in petRec])
d1_match_list = []
d2_match_list = []
for petLine in allPETRECs:
da = datetime.datetime.strptime(petLine[4], '%Y-%m-%d')
if abs((da-d1).days) < 60:
d1_match_list.append('{0}_{1}'.format(petLine[6], petLine[7]))
if abs((da-d2).days) < 60:
d2_match_list.append('{0}_{1}'.format(petLine[6], petLine[7]))
sql = "INSERT INTO MCI_CONV_PET_MATCH VALUES (NULL, '{0}', '{1}', '{2}', '{3}', '{4}')".format(rid, rec[7], rec[8], ','.join(d1_match_list), ','.join(d2_match_list))
dbc.executeNoResult(sql)
