def putLabReponse(labRequestID,resultLink, description):
format = "%Y-%m-%d"
now = datetime.datetime.utcnow().strftime(format)
responseTime =now
query1 = "INSERT INTO LabResponse (labRequestID, description , dateTimeStamp, resultLink) \
VALUES ('{0}','{1}','{2}','{3}')".format(labRequestID,description,responseTime,resultLink)
#print(query1)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
res1=cursor.execute(query1)
mysql.get_db().commit()
cursor.close()
conn.close()
conn = mysql.connect()
cursor =mysql.get_db().cursor()
query2 = "UPDATE LabRequest SET isPending=0 WHERE labRequestDocumentID="+labRequestID+";"
res2=cursor.execute(query2)
mysql.get_db().commit()
cursor.close()
conn.close()
if ((res1) and(res2)):
print("Successful entry")
return True
def patientMedicineRequest(ID,payload,method):
if(method=="GET"):
queryGetAllPrescriptions = "SELECT * \
FROM MedicineRequest \
WHERE patientID={0} \
AND isPending=1".format(ID);
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryGetAllPrescriptionsRes = cursor.execute(queryGetAllPrescriptions)
data1 = cursor.fetchall()
cursor.close()
conn.close()
res = {}
for i,result in enumerate(data1):
res[i] = [str(result[0]),str(result[1]),str(result[2]),str(result[3]),str(result[4])]
# print("---------------data1---------------------\n",data1)
# print("---------------data2---------------------\n",data2)
# for i,result in enumerate(data1):
return res
elif(method=="POST"):
# get date from form
# return json of available times.
query = "INSERT INTO MedicineRequest \
VALUES ('{0}','{1}','{2}','{3}','{4}')".format(\
payload["ePrescriptionID"],
ID,
payload["pharmacyID"],
payload["pickupTime"],
1
)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
mysql.get_db().commit()
cursor.close()
conn.close()
if queryResults==1:
return {"Success":True}
else:
return {"Failed":True}
def patientMedicineRequest(ID, payload, method):
if (method == "GET"):
queryGetAllPrescriptions = "SELECT * FROM MedicineRequest WHERE patientID={0}".format(
ID)
# queryPrescriptionDetails = "SELECT MedicineDetails.symptoms,MedicineDetails.medicineSuggestion,MedicineDetails.timeToTake,MedicineDetails.startDate,MedicineDetails.endDate\
# FROM MedicineRequest,MedicineDetails \
# WHERE MedicineDetails.ePrescriptionID <=> MedicineRequest.ePrescriptionID \
# AND MedicineRequest.patientID<=>'{0}'".format(ID);
queryPrescriptionDetails = "SELECT * FROM MedicineDetails \
WHERE ePrescriptionID IN (SELECT ePrescriptionID FROM MedicineRequest WHERE \
patientID = {0} \
)".format(ID)
conn = mysql.connect()
cursor = mysql.get_db().cursor()
queryGetAllPrescriptionsRes = cursor.execute(queryGetAllPrescriptions)
data1 = cursor.fetchall()
queryPrescriptionDetailsRes = cursor.execute(queryPrescriptionDetails)
data2 = cursor.fetchall()
cursor.close()
conn.close()
res = {}
# for i,result in enumerate(data1):
return res
elif (method == "POST"):
# get date from form
# return json of available times.
query = "INSERT INTO MedicineRequest VALUES ('{0}','{1}','{2}','{3}')".format(\
payload["labDocID"],
labID,
payload["apptDate"],
1
)
conn = mysql.connect()
cursor = mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
mysql.get_db().commit()
cursor.close()
conn.close()
if queryResults == 1:
return {"Success": True}
else:
return {"Failed": True}
def patientDoctorAppointment(patientID,payload,method):
if(method=="GET"):
query = "SELECT doctorID,dateStamp,pickATime FROM DoctorAppointments WHERE patientID='{0}' AND addedToDoctorCalendar=0".format(\
patientID
)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
cursor.close()
conn.close()
doctorApptRes = {}
for i,result in enumerate(data):
doctorApptRes[i] = [str(result[0]),str(result[1]),str(result[2])]
return doctorApptRes
elif(method=="POST"):
# get date from form
# Check all times already input in for that date
# return json of available times.
# query = "SELECT"
query = "INSERT INTO DoctorAppointments VALUES ('{0}','{1}','{2}','{3}','{4}')".format(\
patientID,
payload["doctorID"],
payload["apptDate"],
payload["apptTime"],
1
)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
mysql.get_db().commit()
cursor.close()
conn.close()
if queryResults==1:
return {"Success":True}
else:
return {"Failed":True}
def patientLabRequest(ID,payload,method): #ID is labID if POST, patientID if GET
if(method=="GET"):
query = "SELECT doctorID,ePrescriptionID,testType,description \
FROM ELabRequestDocument \
WHERE patientID='{0}' AND labRequestDocumentID IN (\
SELECT labRequestDocumentID FROM LabRequest WHERE isPending=1\
)".format(ID)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
cursor.close()
conn.close()
labReqRes = {}
for i,result in enumerate(data):
labReqRes[i] = [result[0],result[1],result[2],result[3]]
return labReqRes
elif(method=="POST"):
# get date from form
# return json of available times.
query = "INSERT INTO LabRequest \
VALUES ('{0}','{1}','{2}','{3}')".format(\
payload["labRequestDocumentID"],
payload["labID"],
payload["apptDate"],
1
)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
mysql.get_db().commit()
cursor.close()
conn.close()
if queryResults==1:
return {"Success":True}
else:
return {"Failed":True}
def firstAppointmentUpdate(email):
conn = mysql.connect()
print(mysql)
#conn = mysql.connection
query = "SELECT patientID, dateStamp, pickATime from DoctorAppointments where addedToDoctorCalendar=1 and doctorID in (SELECT doctorID from DoctorDetails where email='" + email + "') for update"
cursor = conn.cursor()
cursor.execute(query)
data = cursor.fetchall()
# print("data is ",data)
if (data != ''):
somedict = {
"patientID": [x[0] for x in data],
"start_datetime": [
datetime.datetime.combine(x[1], (datetime.datetime.min +
x[2]).time()) for x in data
],
"end_datetime": [
datetime.datetime.combine(
x[1], (datetime.datetime.min + x[2] +
datetime.timedelta(minutes=30)).time())
for x in data
]
}
print(somedict)
return json.dumps(somedict, default=myconverter)
return {}
def putLabReponse(labRequestID,resultLink, description):
#responseTime = datetime.datetime.strptime(str(datetime.datetime.now()), "%Y-%m-%d %H:%M:%S")
format = "%Y-%m-%d"
now = datetime.datetime.utcnow().strftime(format)
responseTime =now
print(responseTime)
print(labRequestID)
print(resultLink)
#query = "INSERT INTO LabResponse ('labRequestID','resultLink', 'description','dateTimeStamp') VALUES ('"+ labRequestID+"','"+(resultLink)+"','"+description+"','"+ responseTime+"';"
#query = "INSERT INTO LabResponse ('labRequestID', 'description','dateTimeStamp') VALUES (%s,%s,%s)"
#query = "INSERT INTO Files Values ('"+resultLink+"');"
query = "INSERT INTO LabResponse (labRequestID, description , dateTimeStamp, resultLink) VALUES ('{0}','{1}','{2}','{3}')".format(labRequestID,description,responseTime,resultLink)
print("----------------------query:---------------\n",query)
# res=cursor.execute("INSERT INTO LabResponse ('labRequestID', 'description') VALUES (%s,%s)",(labRequestID,description))
conn = mysql.connect()
cursor =mysql.get_db().cursor()
res=cursor.execute(query)
conn.commit()
query = "UPDATE LabRequest SET isPending=0 WHERE labRequestDocumentID="+labRequestID+";"
res2=cursor.execute(query)
conn.commit()
cursor.close()
conn.close()
#res=1
if ((res1) and(res2)):
print("Successful entry")
return True
def graph(email):
query = " select m.MedicineSuggestion,COUNT(m.MedicineSuggestion) from MedicineDetails m, MedicineRequest mr, EPrescription e, PharmacyDetails pd, PatientDetails p where pd.email='" + email + "' and mr.ePrescriptionID=e.ePrescriptionID and e.ePrescriptionID=m.ePrescriptionID and mr.patientID=p.patientID and pd.pharmacyID=mr.pharmacyID GROUP BY m.MedicineSuggestion ORDER BY COUNT(m.MedicineSuggestion) DESC LIMIT 4;"
print(query)
conn = mysql.connect()
cursor = mysql.get_db().cursor()
cursor.execute(query)
res = cursor.fetchall()
#print("Hiiii",(jsonify(data)))
print(res)
#print("==\n",res2)
data1 = []
sum = 0
med = dict()
if (res):
for tuple in res:
items = tuple[0].split(', ')
for item in items:
if item in med.keys():
med[item] += tuple[1]
else:
med.update({item: tuple[1]})
print(med)
medarray = []
for k, v in med.items():
medarray.append([k, v])
print(k, v)
print(medarray)
# data1.append([tuple[0],tuple[1]])
#sum+=tuple[1]
#data1.append(["Other",res1[0][0]-sum])
#print(data1)
return (medarray)
def checkForAppointments(email):
# conn = mysql.connect()
# print(mysql)
# #conn = mysql.connection
# query= "SELECT patientID, dateTimeStamp from doctorAppointments where doctorID='"+'12'+"'"
# cursor =conn.cursor()
# cursor.execute(query)
# data = cursor.fetchall()
# somedict = {"patientID" : [x[0] for x in data],
# "dateTimeStamp" : [x[1] for x in data]}
# print("somedict is ",somedict)
# print(type(somedict))
# somedict1 = json.dumps(somedict,default=myconverter)
# print("somedict1 is ",somedict1)
# return somedict1
#try:
conn = mysql.connect()
conn.autocommit = False
print(mysql)
#conn = mysql.connection
query = "SELECT patientID, dateStamp, pickATime from DoctorAppointments where addedToDoctorCalendar=0 and doctorID in (SELECT doctorID from DoctorDetails where email='" + email + "') for update"
cursor = conn.cursor()
cursor.execute(query)
data = cursor.fetchall()
# print("data is ",data)
if (data != ''):
somedict = {
"patientID": [x[0] for x in data],
"start_datetime": [
datetime.datetime.combine(x[1], (datetime.datetime.min +
x[2]).time()) for x in data
],
"end_datetime": [
datetime.datetime.combine(
x[1], (datetime.datetime.min + x[2] +
datetime.timedelta(minutes=30)).time())
for x in data
]
}
# print("somedict is ",somedict)
# print(type(somedict))
somedict1 = json.dumps(somedict, default=myconverter)
# print("somedict1 is ",somedict1)
if len(somedict) > 0: # ensure that the dictionary is not empty
# print("\nInside if top\n")
query1 = "UPDATE DoctorAppointments SET addedToDoctorCalendar=1 where addedToDoctorCalendar=0 and doctorID in (SELECT doctorID from DoctorDetails where email='" + email + "')"
cursor1 = conn.cursor()
cursor1.execute(query1)
# print("\nInside if\n")
conn.commit()
# print("Hello Hii I am inside try block\n")
return somedict1
else:
somedict1 = dict()
return somedict1
def getpharmacyPres(email, patientID):
query = "SELECT p.slNo, p.medicineSuggestion, p.remarks from EPrescription p where p.patientID='" + patientID + "';"
print(query)
conn = mysql.connect()
cursor = mysql.get_db().cursor()
res = cursor.execute(query)
data = cursor.fetchall()
#print("Hi in sql ###############",data)
cursor.close()
conn.close()
return (data)
def getEprescritionDetails(email, patientID):
query = "SELECT p.patientID, p.ePrescriptionID, p.doctorID from EPrescription p WHERE p.patientID='" + patientID + "'; "
print(query)
conn = mysql.connect()
cursor = mysql.get_db().cursor()
res = cursor.execute(query)
data = cursor.fetchall()
#print("Hi in sql ###############",data)
cursor.close()
conn.close()
return (data)
def getpharmacytitle(email):
query="SELECT p.pharmacyName, p.address, p.phoneNO from PharmacyDetails p, PharmacyLogin WHERE PharmacyLogin.email='"+email+"';"
print(query)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
res = cursor.execute(query)
data = cursor.fetchall()
#print("Hi in sql ###############",data)
cursor.close()
conn.close()
return (data)
def getUsernameByEmail(email,acctType):
query = "SELECT "+ acctType.lower() + "Name from "+ acctType +"Details where email='"+email+"'"
print(query)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
res = cursor.execute(query)
data = cursor.fetchall()
cursor.close()
conn.close()
return data[0][0]
def prescriptionResponseUpdate(payload,pharmacyID):
query = "INSERT INTO MedicineResponse(ePrescriptionID, patientID, remarks) VALUES('{0}','{1}','{2}')".format(payload["prescriptionID"],payload["patientID"],payload["response"])
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
mysql.get_db().commit()
cursor.close()
conn.close()
if queryResults==0:
return {"Failed":True}
query = "UPDATE MedicineRequest SET isPending=0 WHERE ePrescriptionID='{0}' AND patientID='{1}'".format(payload["prescriptionID"],payload["patientID"])
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
mysql.get_db().commit()
cursor.close()
conn.close()
def getTop4Request(labid):
# query1= "SELECT testType, COUNT(testType) FROM ELabRequestDocument GROUP BY testType \
# ORDER BY COUNT(testType) DESC LIMIT 4;"
query1= "SELECT rd.testType, COUNT(rd.testType) FROM ELabRequestDocument rd, LabRequest lr \
WHERE rd.labRequestDocumentID= lr.labRequestDocumentID and \
lr.labID= '"+labid+"' GROUP BY rd.testType \
ORDER BY COUNT(rd.testType) DESC LIMIT 4;"
conn = mysql.connect()
cursor =mysql.get_db().cursor()
cursor.execute(query1)
res1=cursor.fetchall()
cursor.close()
conn.close()
# query2= "SELECT COUNT(*) FROM ELabRequestDocument;"
query2= "SELECT COUNT(*) FROM ELabRequestDocument rd , LabRequest lr \
WHERE rd.labRequestDocumentID= lr.labRequestDocumentID and \
lr.labID= '"+labid+"';"
conn = mysql.connect()
cursor =mysql.get_db().cursor()
cursor.execute(query2)
res2=cursor.fetchall()
cursor.close()
conn.close()
print(res1)
print("==\n",res2)
data=[]
sum=0;
if (res1) and (res2):
for tuple in res1:
data.append([str(tuple[0]),tuple[1]])
sum+=tuple[1]
data.append(["Other",res2[0][0]-sum])
print(data)
return (data);
def isExistingUser(ID,acctType):
query = "SELECT * FROM {0}Details WHERE {1}ID={2}".format(acctType,acctType.lower(),ID)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
res = cursor.execute(query)
cursor.close()
conn.close()
if(res==0):
return False
else:
return True
def prescriptionRequest(pharmacyId):
query = "SELECT mr.ePrescriptionID, mr.patientID, md.medicineSuggestion FROM MedicineRequest mr LEFT JOIN MedicineDetails md ON mr.ePrescriptionID = md.ePrescriptionID WHERE mr.ispending=1 and mr.pharmacyID='{0}'".format(pharmacyId)
conn = mysql.connect()
cursor = mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
res = {}
for i,result in enumerate(data):
if(str(result[0])+" "+str(result[1]) not in res):
res[str(result[0])+" "+str(result[1])] = [str(result[2])]
else:
res[str(result[0])+" "+str(result[1])].append(str(result[2]))
return res
def getLabRequestDetails(email, reqid):
query="SELECT a.patientID, a.doctorID , a.labRequestDocumentID, a.testType, a.description FROM ELabRequestDocument a, LabRequest lr WHERE a.labRequestDocumentID='"+reqid+"' and a.labRequestDocumentID= lr.labRequestDocumentID ;"
conn = mysql.connect()
cursor =mysql.get_db().cursor()
cursor.execute(query)
res=cursor.fetchall()
cursor.close()
conn.close()
print(res)
return (res)
def getLabResponses(email):
query="SELECT a.patientID, a.doctorID , a.labRequestDocumentID FROM ELabRequestDocument a ,LabRequest lr, LabLogin lo, LabDetails ld where lo.email= '"+email+"' and ld.email = lo.email and ld.labid= lr.labid and lr.labRequestDocumentID=a.labRequestDocumentID and lr.isPending=0;"
conn = mysql.connect()
cursor =mysql.get_db().cursor()
cursor.execute(query)
res=cursor.fetchall()
cursor.close()
conn.close()
print(res)
return (res)
def getpharmacytitle(email):
query = "SELECT pharmacyName,address,phoneNO \
FROM PharmacyDetails \
WHERE email in (SELECT email FROM PharmacyLogin WHERE email='{0}')".format(
email)
print(query)
conn = mysql.connect()
cursor = mysql.get_db().cursor()
res = cursor.execute(query)
data = cursor.fetchall()
#print("Hi in sql ###############",data)
cursor.close()
conn.close()
return (data)
def getLabPrescriptionDetails(reqid):
query="SELECT md.ePrescriptionID, md.symptoms, md.medicineSuggestion, md.timeToTake, md.startDate, md.endDate from MedicineDetails md, ELabRequestDocument elrd where elrd.labRequestDocumentID = "+reqid+ " and elrd. ePrescriptionID = md.ePrescriptionID;"
conn = mysql.connect()
cursor =mysql.get_db().cursor()
cursor.execute(query)
res=cursor.fetchall()
cursor.close()
conn.close()
print(res)
return res
def patientMedicineResponse(ID):
query = "SELECT * FROM MedicineResponse WHERE patientID='{0}'".format(ID)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
res = cursor.execute(query)
data = cursor.fetchall()
cursor.close()
# conn.close()
res = {}
for i,result in enumerate(data):
res[i] = [str(result[0]),str(result[1]),str(result[2]),str(result[3])];
return res
def getLabReportFilename(reqid):
query= "SELECT resultLink FROM LabResponse WHERE labRequestID = "+reqid+";"
conn = mysql.connect()
cursor =mysql.get_db().cursor()
cursor.execute(query)
res=cursor.fetchall()
cursor.close()
conn.close()
print(res[0][0])
return res[0][0]
def getLabId(email) :
query = "SELECT labID FROM LabDetails WHERE email ='"+email+"';"
conn = mysql.connect()
cursor =mysql.get_db().cursor()
cursor.execute(query)
res = cursor.fetchall()
cursor.close()
conn.close()
print(res)
return (res)
def getUsernameByEmail(email,acctType):
# query = "SELECT "+ acctType.lower() + "Name from "+ acctType +"Details where email='"+email+"'"
query = "SELECT {0}Name \
FROM {1}Details \
WHERE email='{2}'".format(acctType.lower(),acctType,email)
print(query)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
res = cursor.execute(query)
data = cursor.fetchall()
cursor.close()
conn.close()
return data[0][0]
def getELabRequestDocumentByID(ID):
query = "SELECT * FROM ELabRequestDocument \
WHERE labRequestDocumentID='{0}'".format(ID)
print("---------query-----------",query)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
res = {}
for i,result in enumerate(data):
res[i] = [str(result[0]),str(result[1]),str(result[2]),str(result[3]),str(result[4])]
print("---------res-----------",res)
return res
def getMedicineDetailsByEPrescriptionID(ID):
query = "SELECT symptoms,medicineSuggestion\
FROM MedicineDetails \
WHERE ePrescriptionID='{0}'".format(ID)
print("---------query-----------",query)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
res = {}
for i,result in enumerate(data):
res[i] = [str(result[0]),str(result[1])]
print("---------res-----------",res)
return res
def patientDocVisitReminderUpdate(patientID):
query = "SELECT doctorID,reminderDate,reminderTime FROM DoctorVisitReminder WHERE patientID='{0}'".format(patientID)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
cursor.close()
conn.close()
docVisitRes = {}
for i,result in enumerate(data):
docVisitRes[i] = [str(result[0]),str(result[1]),str(result[2])]
return docVisitRes
def patientFetchLabDocs(patientID):
query = "SELECT * FROM ELabRequestDocument WHERE patientID='{0}'".format(patientID)
conn = mysql.connect()
cursor =mysql.get_db().cursor()
queryResults = cursor.execute(query)
data = cursor.fetchall()
res = {}
for i,result in enumerate(data):
res[i] = [str(result[0]),str(result[1]),str(result[2]),str(result[3]),str(result[4]),str(result[5])]
cursor.close()
conn.close()
return res
def searchPatientHistory(patientID):
conn = mysql.connect()
conn.autocommit = False
cursor = conn.cursor()
global_dict = dict(dict())
query = "SELECT ePrescriptionID, doctorID from EPrescription where patientID='" + patientID + "'"
cursor.execute(query)
data = cursor.fetchall()
if (data != ''):
somedict = {
"ePrescriptionID": [x[0] for x in data],
"doctorID": [x[1] for x in data]
}
print("somedict of search is ", somedict)
for i in range(len(somedict["ePrescriptionID"])):
json_data = {}
json_data["ePrescriptionID"] = somedict["ePrescriptionID"][i]
query1 = "SELECT symptoms, medicineSuggestion from MedicineDetails where ePrescriptionID='" + str(
somedict["ePrescriptionID"][i]) + "'"
cursor.execute(query1)
data = cursor.fetchall()
json_data["symptoms"] = []
json_data["medicineSuggestion"] = []
for j in range(len(data)):
json_data["symptoms"].append(data[j][0])
json_data["medicineSuggestion"].append(data[j][1])
query2 = "SELECT testType, description FROM ELabRequestDocument where ePrescriptionID='" + str(
somedict["ePrescriptionID"][i]) + "'"
cursor.execute(query2)
data2 = cursor.fetchall()
json_data["testType"] = []
json_data["description"] = []
for j in range(len(data2)):
json_data["testType"].append(data2[j][0])
json_data["description"].append(data2[j][1])
print(json_data)
print(type(json_data))
global_dict[i] = json_data
# print(data2)
print("global_dict is ", global_dict)
return global_dict
