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