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