def updateMedicationPricing():
tmp = sp.call('clear', shell=True)
mname = input("Enter Medicine Name you would like to update: ")
query = "SELECT Price FROM Med_Details WHERE Med_Name = '%s'" % (mname)
if qexec(query):
return -1
ids = cur.fetchall()
if ids == ():
print("No such medication found")
return -1
for row in ids:
print("The current price for this medicine is: {}".format(
row["Price"]))
mprice = int(input("Enter the new price: "))
query = "UPDATE Med_Details SET Price = '%d' WHERE Med_Name = '%s'" % (
mprice, mname)
if qexec(query):
return -1
print("The medication price was updated successfully!")
return 0
def updatePaymentStatus():
tmp = sp.call('clear', shell=True)
billnum = int(input("Enter Bill Number: "))
query = "SELECT Payment_Status FROM Bill WHERE Bill_No = '%d'" % (billnum)
if qexec(query):
return -1
ids = cur.fetchall()
if ids == ():
print("No such Bill Number found")
return -1
for row in ids:
print("The current status of this bill is: {}".format(
row["Payment_Status"]))
status = "K"
while status != "Y" and status != "N":
status = input("Enter the new payment status for this bill (Y/N): ")
query = "UPDATE Bill SET Payment_Status = '%s' WHERE Bill_No = '%d'" % (
status, billnum)
if qexec(query):
return -1
print("The payment status was updated successfully!")
return 0
def updateTestPricing():
tmp = sp.call('clear', shell=True)
ttype = input("Enter test type you would like to update: ")
query = "SELECT Cost FROM Test_Pricing WHERE Type = '%s'" % (ttype)
if qexec(query):
return -1
ids = cur.fetchall()
if ids == ():
print("No such test type found")
return -1
for row in ids:
print("The current cost for this test type is: {}".format(row["Cost"]))
tcost = int(input("Enter the new cost: "))
query = "UPDATE Test_Pricing SET Cost = '%d' WHERE Type = '%s'" % (tcost,
ttype)
if qexec(query):
return -1
print("The test price was updated successfully!")
return 0
def modtest():
try:
td = (input("Enter Test Date (YYYY-MM-DD): "))
tt = (input("Enter Test Time (HH:MM:SS): "))
query = """SELECT * FROM Test_or_Surgery WHERE Date = "%s" and Time = "%s" """ % (td, tt)
if(qexec(query)):
return -1;
res = cur.fetchall()
if(res == ()):
print("Invalid entry. No such test or surgery.")
return -1
print("Current Test/Surgery Result = ", res[0]["Result"])
tres = (input("Enter New Test/Surgery Result: "))
query = """UPDATE Test_or_Surgery set Result = "%s" WHERE Date = "%s" and Time = "%s" """ % (tres, td, tt)
if(qexec(query)):
return -1;
print("Success!")
return 0
except Exception as e:
print("Error: ", e)
return -1
def addAppointment():
tmp = sp.call('clear', shell=True)
row = {}
print("Enter new appointment details: ")
row["Date"] = input("Date (YYYY-MM-DD): ")
row["Time"] = input("Time (HH:MM:SS): ")
query = "INSERT INTO Appointment(Time, Date) VALUES ('%s', '%s')" % (
row["Time"], row["Date"])
if qexec(query):
return -1
query = "SELECT LAST_INSERT_ID()"
if qexec(query):
return -1
aid = cur.fetchone()
pno = schedules(row["Date"], row["Time"], aid["LAST_INSERT_ID()"])
print("The appointment was created successfully!")
print("The prescription number (Pno) is: {}".format(pno))
return 0
def schedules(appdate, apptime, aid):
row = {}
flag = 1
query = "SELECT Pno FROM Schedules"
if (qexec(query)):
return -1
ids = cur.fetchall()
while (flag):
temp = random.randint(0, inf)
row["Pno"] = temp
flag = 0
for oid in ids:
if (oid == temp):
flag = 1
break
row["Staff_Id"] = int(input("Staff Id: "))
row["Patient_Id"] = int(input("Patient Id: "))
row["Time"] = apptime
row["Date"] = appdate
row["Id"] = aid
row["Duration"] = input("Duration (in minutes): ")
query = "INSERT INTO Schedules VALUES ('%d', '%s', '%s', %d, '%d', '%d', '%s')" % (
row["Staff_Id"], row["Time"], row["Date"], row["Id"], row["Pno"],
row["Patient_Id"], row["Duration"])
if qexec(query):
return -1
return row["Pno"]
def modroom():
try:
rtype = (input("Enter room type whose tariff is to be edited: "))
query = """SELECT * FROM Room_Pricing WHERE Room_Type = "%s" """ % (
rtype)
if (qexec(query)):
return -1
res = cur.fetchall()
if (res == ()):
print("Invalid type. No such room.")
return -1
print("Current room tariff = ", res[0]["Hourly_Tariff"])
tar = float(input("Enter new tariff: "))
query = """UPDATE Room_Pricing set Hourly_Tariff = %f WHERE Room_Type = "%s" """ % (
tar, rtype)
if (qexec(query)):
return -1
print("Success!")
return 0
except Exception as e:
print("Error: ", e)
return -1
def adddoc(staff_id):
tmp = sp.call('clear', shell=True)
doctor = {}
special = {}
works = {}
heads = {}
doctor["Staff_Id"] = staff_id
doctor["Consultation_Fee"] = int(input("Consultation Fee: "))
query = "insert into Doctor values(%d, %d)" % (doctor["Staff_Id"],
doctor["Consultation_Fee"])
if (qexec(query)):
return -1
more = "Y"
while (more == "Y"):
special["Staff_Id"] = staff_id
special["Expertise_Area"] = input("Expertise Area: ")
query = """insert into Specialisation values(%d, "%s")""" % (
special["Staff_Id"], special["Expertise_Area"])
if (qexec(query)):
continue
more = input("Do you wish to enter more expertise areas (Y/N): ")
works["Staff_Id"] = staff_id
row = ()
dnew = "N"
while (row == () and dnew != "Y"):
works["Dno"] = int(input("Department Number: "))
query = "SELECT Dno FROM Works_In WHERE Dno = %d" % (works["Dno"])
if (qexec(query)):
return -1
row = cur.fetchall()
if (row == ()):
dnew = input(
"Department does not exist. Would you like to create a new department (Y/N): "
)
if (dnew == "Y"):
if (adddept(works["Dno"], staff_id)):
return -1
query = "insert into Works_In values(%d, %d)" % (works["Staff_Id"],
works["Dno"])
if (qexec(query)):
return -1
if (row == ()):
heads["Staff_Id"] = staff_id
heads["Dno"] = works["Dno"]
query = "insert into Heads values(%d, %d)" % (heads["Dno"],
heads["Staff_Id"])
if (qexec(query)):
return -1
return 0
def recommends(pno):
row = {}
row["Pno"] = pno
medornot = "K"
while medornot != "Y" and medornot != "N":
medornot = input("Has any medication been prescribed? (Y/N): ")
if medornot == "Y":
row["Med_Name"] = input("Medicine Name: ")
row["Batch_No"] = int(input("Batch No: "))
row["Dosage"] = int(input("Dosage: "))
query = "SELECT Price FROM Med_Details WHERE Med_Name = '%s'" % (
row["Med_Name"])
if qexec(query):
return -1
ids = cur.fetchall()
global medprice
for med in ids:
medprice = med["Price"]
global dosage
dosage = row["Dosage"]
query = """UPDATE Batch_Details SET Qty = Qty - %d WHERE Batch_No = %d""" % (
dosage, row["Batch_No"])
if (qexec(query)):
return -1
else:
row["Med_Name"] = "NULL"
row["Batch_No"] = "NULL"
row["Dosage"] = "NULL"
global billno
billno = createBill(pno)
row["Bill_No"] = billno
if medornot == "Y":
query = "INSERT INTO Recommends VALUES ('%d', '%s', '%d', '%d', '%d')" % (
row["Pno"], row["Med_Name"], row["Batch_No"], row["Bill_No"],
row["Dosage"])
else:
query = "INSERT INTO Recommends VALUES ('%d', %s, %s, '%d', %s)" % (
row["Pno"], row["Med_Name"], row["Batch_No"], row["Bill_No"],
row["Dosage"])
if qexec(query):
return -1
return 0
def updateAppointment():
tmp = sp.call('clear', shell=True)
adate = input("Enter date of the appointment: ")
atime = input("Enter time of the appointment: ")
apid = int(input("Enter patient id: "))
query = "SELECT * FROM Schedules WHERE Date = '%s' AND Time = '%s' AND Patient_Id = %d" % (
adate, atime, apid)
if qexec(query):
return -1
ids = cur.fetchall()
if ids == ():
print("No appointment found on this date, time and patient id")
return -1
for row in ids:
print(
"The patient has an appointment scheduled on {} at time {}".format(
row["Date"], row["Time"]))
newdate = input("Enter the new date of the appointment: ")
newtime = input("Enter the new time of the appointment: ")
if (newdate == adate and newtime == atime):
print("No change in appointment as same details entered.")
return -1
query = """INSERT INTO Appointment(Time, Date) VALUES ("%s", "%s")""" % (
newtime, newdate)
if qexec(query):
return -1
query = "SELECT LAST_INSERT_ID()"
if qexec(query):
return -1
nid = cur.fetchone()
newid = nid["LAST_INSERT_ID()"]
if (updateSchedules(adate, atime, apid, newdate, newtime, newid)):
return -1
print("The appointment was updated successfully!")
return 0
def updateSchedules(adate, atime, apid, newdate, newtime, newid):
query = "SELECT Pno FROM Schedules WHERE Date = '%s' AND Time = '%s' AND Patient_Id = %d" % (
adate, atime, apid)
if qexec(query):
return -1
pno = cur.fetchall()
query = "UPDATE Schedules SET Date = '%s', Time = '%s', Id = %d WHERE Pno = %d" % (
newdate, newtime, newid, pno[0]["Pno"])
if qexec(query):
return -1
def addPrescription():
tmp = sp.call('clear', shell=True)
row = {}
ids = {}
pno = 0
while (ids == {}):
pno = int(input("Pno: "))
query = "SELECT Pno FROM Schedules WHERE Pno = %d" % (pno)
if qexec(query):
return -1
ids = cur.fetchall()
row["Pno"] = pno
# print("Enter Prescription details: ")
row["Complaint"] = input("Complaint: ")
row["Diagnosis"] = input("Diagnosis: ")
query = "INSERT INTO Prescription VALUES ('%d', '%s', '%s')" % (
row["Pno"], row["Complaint"], row["Diagnosis"])
if qexec(query):
return -1
if recommends(pno):
return -1
if entails(pno):
return -1
row["Payment_Status"] = "K"
while row["Payment_Status"] != "Y" and row["Payment_Status"] != "N":
row["Payment_Status"] = input("Payment Status (Y/N): ")
query = "UPDATE Bill SET Payment_Status = '%s' WHERE Bill_No = %d" % (
row["Payment_Status"], billno)
if qexec(query):
return -1
print(
"The prescription was created successfully! Associated Bill Number = ",
billno)
return 0
def createPays(pno, bno):
query = "SELECT * FROM Schedules WHERE Pno = %d" % (pno)
if (qexec(query)):
return -1
dets = cur.fetchall()
query = "INSERT INTO Pays VALUES (%d, %d, %d)" % (
bno, dets[0]["Patient_Id"], dets[0]["Staff_Id"])
if (qexec(query)):
return -1
return 0
def addshift(staff_id):
try:
tmp = sp.call('clear', shell=True)
more = "Y"
shift = {}
while (more == "Y"):
shift["Staff_Id"] = staff_id
shift["Shift_Day"] = "Holiday"
while shift["Shift_Day"] not in shifts:
shift["Shift_Day"] = (input("Shift Day: "))
shift["Shift_Start_Time"] = (
input("Shift Start Time (HH:MM:SS): "))
shift["Shift_End_Time"] = (input("Shift End Time (HH:MM:SS): "))
query = """insert into Shift values(%d, "%s", "%s", "%s")""" % (
shift["Staff_Id"], shift["Shift_Start_Time"],
shift["Shift_End_Time"], shift["Shift_Day"])
if (qexec(query)):
continue
more = input("Do you wish to enter more shifts (Y/N): ")
return 0
except Exception as e:
print("Error: ", e)
return -1
def addroom():
try:
tmp = sp.call('clear', shell=True)
room = {}
roompr = {}
print("Enter new room's details: ")
room["Room_No"] = int(input("Room Number: "))
room["Location_Floor"] = int(input("Floor Number: "))
room["Location_Block"] = (input("Block: "))
room["Room_Type"] = (input("Room Type: "))
room["Available"] = 1
roompr["Room_Type"] = room["Room_Type"]
query = """SELECT * from Room_Pricing WHERE Room_Type = '%s'""" % (
room["Room_Type"])
if (qexec(query)):
return -1
rpri = cur.fetchall()
if (rpri == ()):
tariff = (input("Hourly Tariff: "))
if (tariff != ""):
roompr["Hourly_Tariff"] = int(tariff)
else:
roompr["Hourly_Tariff"] = "NULL"
query = """insert into Room_Pricing values("%s", %s)""" % (
roompr["Room_Type"], roompr["Hourly_Tariff"])
if (qexec(query)):
return -1
query = """insert into Room values(%d, %d, "%s", "%s", %d)""" % (
room["Room_No"], room["Location_Floor"], room["Location_Block"],
room["Room_Type"], room["Available"])
if (qexec(query)):
return -1
print("Success!")
return 0
except Exception as e:
print("Error: ", e)
return -1
def entails(pno):
row = {}
row["Pno"] = pno
row["Bill_No"] = billno
testornot = "K"
while testornot != "Y" and testornot != "N":
testornot = input("Has any test/surgery been prescribed? (Y/N): ")
if testornot == "Y":
row["Date"] = input("Test Date (YYYY-MM-DD): ")
row["Time"] = input("Test Time (HH:MM:SS): ")
else:
row["Date"] = "NULL"
row["Time"] = "NULL"
if testornot == "Y":
if addTestorSurgery(row["Date"], row["Time"]):
return -1
query = "INSERT INTO Entails VALUES ('%d', '%s', '%s', '%d')" % (
row["Pno"], row["Date"], row["Time"], row["Bill_No"])
else:
query = "INSERT INTO Entails VALUES ('%d', %s, %s, '%d')" % (
row["Pno"], row["Date"], row["Time"], row["Bill_No"])
if qexec(query):
return -1
query = "SELECT * FROM Schedules WHERE Pno = %d" % (pno)
if qexec(query):
return -1
x = cur.fetchall()
query = "SELECT * FROM Doctor WHERE Staff_Id = %d" % (x[0]["Staff_Id"])
if qexec(query):
return -1
y = cur.fetchall()
query = "UPDATE Bill SET Amount = Amount + %d WHERE Bill_No = %d" % (
y[0]["Consultation_Fee"], billno)
if qexec(query):
return -1
return 0
def fee(res):
query = "SELECT * FROM Doctor WHERE Staff_Id = %d" % (res["Staff_Id"])
if (qexec(query)):
return -1
doc = cur.fetchall()
print("Current consultation fee = ", doc[0]["Consultation_Fee"])
num = int(input("Enter new fee: "))
query = "UPDATE Doctor set Consultation_Fee = %d WHERE Staff_Id = %d" % (
num, res["Staff_Id"])
if (qexec(query)):
return -1
print("Success!")
return 0
def modpat():
try:
pid = int(input("Enter patient id whose details are to be edited: "))
query = """SELECT * FROM Patient WHERE Patient_Id = %d""" % (pid)
if (qexec(query)):
return -1
res = cur.fetchall()
if (res == ()):
print("Invalid id. No such patient.")
return -1
print("Choose 1 field to edit: ")
print("1. Contact")
print("2. Address")
query = """SELECT * FROM Insured_Patients WHERE Patient_Id = %d""" % (
pid)
if (qexec(query)):
return -1
inspat = cur.fetchall()
if (inspat != ()):
print("3. Insurance latest renewal date")
choice = int(input("Enter choice: "))
tmp = sp.call('clear', shell=True)
if (choice == 1):
if (contact(res[0])):
return -1
elif (choice == 2):
if (addr(res[0])):
return -1
elif (inspat != () and choice == 3):
if (ins(inspat[0])):
return -1
else:
print("Error: Invalid option.")
return 0
except Exception as e:
print("Error: ", e)
return -1
def ins(inspat):
query = "SELECT * FROM Insured_Details WHERE Insurance_Id = %d" % (
inspat["Insurance_Id"])
if (qexec(query)):
return -1
insdet = cur.fetchall()
print("Current latest renewal date = ", insdet[0]["Latest_Renewal_Date"])
num = (input("Enter new date (YYYY-MM-DD): "))
query = """UPDATE Insured_Details set Latest_Renewal_Date = "%s" WHERE Insurance_Id = %d""" % (
num, inspat["Insurance_Id"])
if (qexec(query)):
return -1
print("Success!")
return 0
def addMedication():
tmp = sp.call('clear', shell=True)
row = {}
print("Enter Medication details: ")
row["Med_Name"] = input("Medicine name: ")
row["Batch_No"] = int(input("Batch No: "))
row["Expiry_Date"] = input("Expiry Date (YYYY-MM-DD): ")
snew = "N"
ret = ()
while (ret == () and snew != "Y"):
row["Supplier_Id"] = int(input("Supplier Id: "))
query = "SELECT Supplier_Id FROM Supplier_Details WHERE Supplier_Id = %d" % (
row["Supplier_Id"])
if (qexec(query)):
return -1
ret = cur.fetchall()
if (ret == ()):
snew = input(
"Supplier does not exist. Would you like to create a new supplier (Y/N): "
)
if (snew == "Y"):
if addSupplierDetails(row["Supplier_Id"]):
return -1
query = "INSERT INTO Medication VALUES ('%s', '%d', '%s', '%d')" % (
row["Med_Name"], row["Batch_No"], row["Expiry_Date"],
row["Supplier_Id"])
if qexec(query):
return -1
if addMedDetails(row["Med_Name"]):
return -1
if addBatchDetails(row["Batch_No"]):
return -1
print("The medication was added successfully!")
return 0
def freeroom():
try:
rno = int(input("Enter room number: "))
query = "UPDATE Room set Available = 1 WHERE Room_No = %d" % (rno)
if (qexec(query)):
return -1
query = "DELETE FROM Stays_In WHERE Room_No = %d" % (rno)
if (qexec(query)):
return -1
print("Success!")
return 0
except Exception as e:
print("Error: ", e)
return -1
def bookroom():
try:
rno = int(input("Enter room number: "))
query = """SELECT * FROM Room WHERE Room_No = %d""" % (rno)
if (qexec(query)):
return -1
res = cur.fetchall()
if (res == ()):
print("Invalid room number. No such room.")
return -1
elif (res[0]["Available"] == 0):
print("Room unavailable.")
return -1
bno = int(
input("Enter bill number to which this room will be charged: "))
query = """SELECT * FROM Entails WHERE Bill_No = %d""" % (bno)
if (qexec(query)):
return -1
ent = cur.fetchall()
if (ent == ()):
print("Invalid bill number. No such bill.")
return -1
query = """SELECT * FROM Schedules WHERE Pno = %d""" % (ent[0]["Pno"])
if (qexec(query)):
return -1
sched = cur.fetchall()
dur = int(input("Enter stay duration (number of hours rounded up): "))
query = """SELECT * FROM Room_Pricing WHERE Room_Type = "%s" """ % (
res[0]["Room_Type"])
if (qexec(query)):
return -1
pri = cur.fetchall()
query = """UPDATE Bill set Amount = Amount + %d, Payment_Status = "N" WHERE Bill_No = %d""" % (
pri[0]["Hourly_Tariff"] * dur, bno)
if (qexec(query)):
return -1
query = """insert into Stays_In values(%d, %d, %d, "%s")""" % (
bno, sched[0]["Patient_Id"], rno, dur)
if (qexec(query)):
return -1
query = "UPDATE Room set Available = 0 WHERE Room_No = %d" % (rno)
if (qexec(query)):
return -1
print("Success!")
return 0
except Exception as e:
print("Error: ", e)
return -1
def delappt():
try:
tmp = sp.call('clear', shell=True)
pid = int(
input("Enter Patient Id whose appointment is to be removed: "))
ad = (input("Enter Appointment Date (YYYY-MM-DD): "))
at = (input("Enter Appointment Time (HH:MM:SS): "))
query = """SELECT * FROM Schedules WHERE Patient_Id = %d and Time = "%s" and Date = "%s" """ % (
pid, at, ad)
if (qexec(query)):
return -1
res = cur.fetchall()
if (res == ()):
print("Invalid entry. No such appointment.")
return -1
query = """SELECT * FROM Prescription WHERE Pno = %d""" % (
res[0]["Pno"])
if (qexec(query)):
return -1
pre = cur.fetchall()
if (pre != ()):
print("Error. Cannot cancel a completed appointment.")
return -1
query = "DELETE FROM Schedules WHERE Pno = %d" % (res[0]["Pno"])
if (qexec(query)):
return -1
query = """DELETE FROM Appointment WHERE Time = "%s" and Date = "%s" """ % (
res[0]["Time"], res[0]["Date"])
if (qexec(query)):
return -1
print("Success!")
return 0
except Exception as e:
print("Error: ", e)
return -1
def contact(res):
print("Current contact number = ", res["Contact_No"])
num = int(input("Enter new number: "))
query = "UPDATE Staff set Contact_No = %d WHERE Staff_Id = %d" % (
num, res["Staff_Id"])
if (qexec(query)):
return -1
print("Success!")
return 0
def sal(res):
print("Current salary = ", res["Salary"])
num = int(input("Enter new salary: "))
query = "UPDATE Staff set Salary = %d WHERE Staff_Id = %d" % (
num, res["Staff_Id"])
if (qexec(query)):
return -1
print("Success!")
return 0
def addBatchDetails(batch_no):
row = {}
row["Batch_No"] = batch_no
row["Qty"] = int(input("Quantity: "))
query = "INSERT INTO Batch_Details VALUES ('%d', '%d')" % (row["Batch_No"],
row["Qty"])
if qexec(query):
return -1
return 0
def addSupplierDetails(supplier_id):
row = {}
row["Supplier_Id"] = supplier_id
row["Supplier_Name"] = input("Supplier_Name: ")
query = "INSERT INTO Supplier_Details VALUES ('%s', '%s')" % (
row["Supplier_Id"], row["Supplier_Name"])
if qexec(query):
return -1
return 0
def addMedDetails(med_name):
row = {}
row["Med_Name"] = med_name
row["Manufacturer"] = input("Manufacturer: ")
row["Price"] = int(input("Price: "))
query = "SELECT * FROM Med_Details WHERE Med_Name = '%s' AND Manufacturer = '%s' AND Price = %d" % (
row["Med_Name"], row["Manufacturer"], row["Price"])
if (qexec(query)):
return -1
ret = cur.fetchall()
if (ret != ()):
return 0
query = "INSERT INTO Med_Details VALUES ('%s', '%s', '%d')" % (
row["Med_Name"], row["Manufacturer"], row["Price"])
if qexec(query):
return -1
return 0
def addins(patid):
tmp = sp.call('clear', shell=True)
inspat = {}
insdet = {}
inspat["Patient_Id"] = patid
inspat["Insurance_Id"] = int(input("Insurance Id: "))
insdet["Insurance_Id"] = inspat["Insurance_Id"]
insdet["Company"] = (input("Company: "))
insdet["Latest_Renewal_Date"] = input("Latest Renewal Date (YYYY-MM-DD): ")
query = """insert into Insured_Details values(%d, "%s", "%s")""" % (
insdet["Insurance_Id"], insdet["Company"],
insdet["Latest_Renewal_Date"])
if (qexec(query)):
return -1
query = """insert into Insured_Patients values(%d, %d)""" % (
inspat["Patient_Id"], inspat["Insurance_Id"])
if (qexec(query)):
return -1
return 0
def addedu(staff_id):
tmp = sp.call('clear', shell=True)
more = "Y"
education = {}
while (more == "Y"):
education["Staff_Id"] = staff_id
education["Degree"] = input("Degree: ")
query = """insert into Education values(%d, "%s")""" % (
education["Staff_Id"], education["Degree"])
if (qexec(query)):
continue
more = input("Do you wish to enter more degrees (Y/N): ")
return 0
