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}
