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 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 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 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 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 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 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 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 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 patientLabResponse(patientID): query = "SELECT * FROM LabResponse WHERE labRequestID IN \ (SELECT labRequestDocumentID FROM ELabRequestDocument WHERE patientID='{0}')".format(patientID) 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] = [str(result[0]),str(result[1]),str(result[2]),str(result[3]),str(result[4])] return labReqRes
def getDetailsByName(inpText,resType): query = "SELECT {0}Name,{0}ID FROM {1}Details WHERE {0}Name LIKE '{2}%'".format(resType.lower(),resType,inpText) conn = mysql.connect() cursor =mysql.get_db().cursor() queryResults = cursor.execute(query) data = cursor.fetchall() cursor.close() conn.close() res = {} if(queryResults!=0): for i,result in enumerate(data): res[i] = [result[0],result[1]] print("res:",res) return res else: return {"data":None}
def patientLabVisitReminderUpdate(patientID): query = "SELECT labID,labRequestDocumentID,reminderDate,reminderTime \ FROM LabVisitReminder \ WHERE patientID='{0}'".format(patientID) conn = mysql.connect() cursor =mysql.get_db().cursor() queryResults = cursor.execute(query) data = cursor.fetchall() cursor.close() conn.close() labVisitRes = {} for i,result in enumerate(data): labVisitRes[i] = [str(result[0]),str(result[1]),str(result[2]),str(result[3])] return labVisitRes
def patientLabResponse(patientID): query = "SELECT doctorID,ePrescriptionID,testType,description FROM ELabRequestDocument WHERE patientID='{0}' AND labRequestDocumentID IN (\ SELECT labRequestDocumentID FROM LabResponse\ )".format(patientID) 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
def getNumberOfRequests(labid): # query="select DATE(dateTimeStamp) , COUNT(DATE(dateTimeStamp)) from LabRequest \ # group by DATE(dateTimeStamp) order by DATE(dateTimeStamp) ;" query="select DATE(dateTimeStamp) , COUNT(DATE(dateTimeStamp)) from LabRequest\ where labID='"+labid+"' group by DATE(dateTimeStamp) \ order by DATE(dateTimeStamp) ;" conn = mysql.connect() cursor =mysql.get_db().cursor() cursor.execute(query) res=cursor.fetchall() cursor.close() conn.close() format = "%Y-%m-%d" data=[] if(res): for tuple in res: data.append([str(tuple[0]),tuple[1]]) return data
def patientFetchPrescriptions(patientID): query = "SELECT * FROM MedicineDetails WHERE ePrescriptionID IN (\ SELECT ePrescriptionID FROM EPrescription 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])] cursor.close() conn.close() return res
def patientMedReminderUpdate(patientID): res = {"TakeMedicine": None, "OrderMedicine": None} # Take Medicine updates: query = "SELECT symptoms,medicineSuggestion,timeToTake,startDate,endDate \ FROM MedicineDetails \ WHERE ePrescriptionID IN (SELECT ePrescriptionID FROM MedicineReminder WHERE patientID='{0}')".format( patientID) conn = mysql.connect() cursor = mysql.get_db().cursor() queryResults = cursor.execute(query) data = cursor.fetchall() takeMedicineRes = {} for i, result in enumerate(data): takeMedicineRes[i] = [ str(result[0]), str(result[1]), str(result[2]), str(result[3]), str(result[4]) ] res["TakeMedicine"] = takeMedicineRes # Order Medicine updates: query = "SELECT ePrescriptionID,reminderDate,reminderTime FROM MedicineReminder WHERE patientID='{0}'".format( patientID) queryResults = cursor.execute(query) data = cursor.fetchall() cursor.close() conn.close() orderMedicineRes = {} for i, result in enumerate(data): orderMedicineRes[i] = [str(result[0]), str(result[1]), str(result[2])] res["OrderMedicine"] = orderMedicineRes return res
def getAvailableTimeSlots(doctorID,inpDate): query = "SELECT pickATime FROM DoctorAppointments WHERE doctorID='{0}' AND dateStamp='{1}'".format(\ doctorID, inpDate ) conn = mysql.connect() cursor =mysql.get_db().cursor() queryResults = cursor.execute(query) data = cursor.fetchall() res = {} # print("------------------data------------------\n",data) for i,result in enumerate(data): # print(i,result) res[i] = str(result[0]) # str to convert datetime.timedelta to a time representation cursor.close() conn.close() return res
def loginCheck(email,password,acctType): query = "SELECT password FROM {0}Login WHERE email='{1}'".format(acctType,email) conn = mysql.connect() cursor =mysql.get_db().cursor() cursor.execute(query) # conn.commit() data = cursor.fetchall() # mysql.get_db().commit() # data = cursor.fetchall() cursor.close() conn.close() print("loginCheck data:",data) try: if(password!=data[0][0]): return False else: return True except Exception as e: return False
def loginCheck(email,password,acctType): query = "SELECT password FROM {0}Login WHERE email='{1}'".format(acctType,email) conn = mysql.connect() cursor =mysql.get_db().cursor() cursor.execute(query) # conn.commit() data = cursor.fetchall() # mysql.get_db().commit() # data = cursor.fetchall() cursor.close() conn.close() print("loginCheck data:",data) try: if(password!=data[0][0]): return False # Invalid password entered by user, considering the # email exists in the DB for that acctType. else: return True except Exception as e: return False # If there's no such email in DB for that acctType.
def getNumberOfRequests(email): query = "SELECT DATE(mr.pickupTime) , COUNT(DATE(mr.pickupTime))\ FROM MedicineRequest mr, PharmacyDetails pd \ WHERE isPending=1 AND pd.pharmacyID=mr.pharmacyID AND pd.email='{0}'\ GROUP BY DATE(pickupTime) ORDER BY DATE(pickupTime)".format(email) # query="select DATE(dateTimeStamp) , COUNT(DATE(dateTimeStamp)) from LabRequest\ # where isPending=1 and labID='"+labid+"' group by DATE(dateTimeStamp) order by DATE(dateTimeStamp) ;" conn = mysql.connect() cursor = mysql.get_db().cursor() cursor.execute(query) res = cursor.fetchall() print("YO-----------", res) cursor.close() conn.close() format = "%Y-%m-%d" data = [] if (res): data = [] for tuple in res: data.append([str(tuple[0]), tuple[1]]) #print(data) return data
def getDetailsByID(ID, acctType): query = "SELECT * FROM {0}Details WHERE {0}ID='{1}'".format(acctType, ID) conn = mysql.connect() cursor = mysql.get_db().cursor() queryResults = cursor.execute(query) data = cursor.fetchall() cursor.close() conn.close() res = {} if (acctType == "Doctor"): 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]), str(result[6]), str(result[7]) ] else: for i, result in enumerate(data): res[i] = [ str(result[0]), str(result[1]), str(result[2]), str(result[3]), str(result[4]) ] return res
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 insertNewUser(inpDict, acctType): if (acctType == "Patient"): insertDetailQuery = "INSERT INTO PatientDetails VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')".format(\ inpDict["patientID"], inpDict["name"], inpDict["email"], inpDict["dob"], inpDict["address"], inpDict["sex"], inpDict["phoneNO"] ) elif (acctType == "Doctor"): insertDetailQuery = "INSERT INTO DoctorDetails VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')".format(\ inpDict["doctorID"], inpDict["doctorName"], inpDict["email"], inpDict["dob"], inpDict["address"], inpDict["sex"], inpDict["phoneNO"], inpDict["designation"] ) elif (acctType == "Lab"): insertDetailQuery = "INSERT INTO LabDetails VALUES ('{0}','{1}','{2}','{3}','{4}')".format(\ inpDict["labID"], inpDict["labName"], inpDict["address"], inpDict["email"], inpDict["phoneNO"] ) elif (acctType == "Pharmacy"): insertDetailQuery = "INSERT INTO PharmacyDetails VALUES ('{0}','{1}','{2}','{3}','{4}')".format(\ inpDict["pharmacyID"], inpDict["pharmacyName"], inpDict["address"], inpDict["email"], inpDict["phoneNO"] ) else: return ("Error") insertLoginDetailQuery = "INSERT INTO {0}Login VALUES('{1}','{2}')".format(\ acctType, inpDict["email"], inpDict["password"] ) print(insertDetailQuery) print(insertLoginDetailQuery) conn = mysql.connect() cursor = mysql.get_db().cursor() insertDetailRes = cursor.execute(insertDetailQuery) insertLoginDetailRes = cursor.execute(insertLoginDetailQuery) mysql.get_db().commit() cursor.close() conn.close() if (insertDetailRes == 1 and insertLoginDetailRes == 1): return True else: return False
def patientMedicineRequest(ID,payload,method): if(method=="GET"): queryGetAllPrescriptions = "SELECT * FROM MedicineRequest WHERE patientID={0} AND isPending=1".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): 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}