def filteredSearchForHouses(conn, maxPrice, city, aid, rent): if aid: return execute( conn, f"SELECT h.name, h.price, h.address FROM House as h WHERE h.price <= {maxPrice} AND h.city= '{city}' AND h.aid= {aid} AND h.rent= {rent} AND h.status= 'active'" ) else: return execute( conn, f"SELECT h.name, h.price, h.address FROM House as h WHERE h.price <= {maxPrice} AND h.city= '{city}' AND h.rent= {rent} AND h.status= 'active'" )
def boats_sailed(conn): userInput = request.args.get('sailor-name') print(userInput) return execute( conn, "SELECT DISTINCT b.name FROM Boats AS b INNER JOIN Voyages AS v ON b.bid = v.bid INNER JOIN Sailors AS s ON s.sid = v.sid WHERE s.name = (?)", (userInput, ))
def add_boat(conn): userInputName = request.args.get('boat-name') userInputColor = request.args.get('boat-color') if userInputName == None or userInputColor == None: return return execute(conn, "INSERT INTO Boats (name, color) VALUES ((?), (?));", (userInputName, userInputColor))
def addBoat(conn): print("-----------------------------------") boat_name = request.args.get('boat-name') boat_color = request.args.get('boat-color') if boat_name == None or boat_color == None: print("herere") return return execute(conn, "INSERT INTO Boats(name,color) VALUES ((?), (?));", (boat_name, boat_color, ))
def add_nurse(conn): InputName = request.args.get('nurse-name') InputNumber = request.args.get('n-phone-no') if InputName == None or InputNumber == None: return return execute(conn, "INSERT INTO Nurse ( N_name, N_number) VALUES ((?), (?));", (InputName, InputNumber))
def Delete_appointments(conn): aid = request.args.get('aid') Doctor_name = request.args.get('doc-name') print( aid, " ", Doctor_name ) if aid == None or Doctor_name == None: return return execute(conn, "DELETE FROM Appointments WHERE aid = (?);"\ , ( aid,))
def Patients(conn): currentDoctor = str(login.user()) return execute( conn, "SELECT p.pid AS ID , p.P_name AS Name, p.DOB, p.Gender as Gender,\ p.P_number as Phone_Number, p.P_address AS Address, p.medical_history AS Medical_history\ FROM Patient AS p Join Appointments as a on p.pid = a.pid Join Doctor as d on a.did = d.did\ Where d.Doc_email = (?) ", (currentDoctor, ))
def add_a_boat(conn, name, color): if name == None or color == None or name == "" or color == "": raise Exception return execute(conn, "INSERT INTO Boats(name,color) VALUES (:name,:color) ", { 'name': name, 'color': color })
def insert_boat_in_DB(conn, boat_name, boat_color): if boat_name == None or boat_color == None or boat_name == "" or boat_color == "": raise Exception return execute( conn, "INSERT INTO Boats (name, color) VALUES (:boat_name, :boat_color);", { 'boat_name': boat_name, "boat_color": boat_color })
def add_sailor(conn): userInputName = request.args.get('sailor-name') userInputAge = request.args.get('sailor-age') userInputExp = request.args.get('sailor-experience') if userInputName == None or userInputAge == None or userInputExp == None: return return execute( conn, "INSERT INTO Sailors (name, age, experience) VALUES ((?), (?), (?));", (userInputName, userInputAge, userInputExp))
def add_voyage(conn): userInputSid = request.args.get('sailor-id') userInputBid = request.args.get('boat-id') userInputDate = request.args.get('date-of-voyage') if userInputSid == None or userInputBid == None or userInputDate == None: return return execute( conn, "INSERT INTO Voyages (sid, bid, date_of_voyage) VALUES ((?), (?), (?));", (userInputSid, userInputBid, userInputDate))
def addSailor(conn): name = request.args.get('sailor-name') age = request.args.get('sailor-age') experience = request.args.get('sailor-experience') if name == None or age == None or experience == None: return return execute( conn, "INSERT INTO Sailors(name, age, experience) VALUES ((?), (?), (?));", (name, age, experience))
def add_a_sailor(conn, name, age, experience): if name == None or age == None or experience == None or name == "": raise Exception return execute( conn, "INSERT INTO Sailors(name,age,experience) VALUES (:name,:age,:experience) ", { 'name': name, 'age': age, 'experience': experience })
def insert_sailors_in_DB(conn, sailors_name, sailors_age, sailors_experience): if sailors_name == None or sailors_age == None or sailors_experience == None or sailors_name == "" or sailors_age == "" or sailors_experience == "" or int( sailors_age) < 0 or int(sailors_experience) < 0: raise Exception return execute( conn, "INSERT INTO Sailors (name, age, experience) VALUES (:sailors_name, :sailors_age, :sailors_experience);", { 'sailors_name': sailors_name, "sailors_age": sailors_age, "sailors_experience": sailors_experience })
def insert_voyages_in_DB(conn, sid, bid, date): try: datetime.datetime.strptime(date, '%Y-%m-%d') except ValueError: raise Exception return execute( conn, "INSERT INTO Voyages (sid, bid, date_of_voyage) SELECT Sailors.sid, Boats.bid, :date FROM Sailors, Boats WHERE Sailors.sid=:sid AND Boats.bid=:bid;", { 'sid': sid, "bid": bid, "date": date })
def add_a_voyage(conn, sid, bid, date_of_voyage): try: datetime.datetime.strptime(date_of_voyage, '%Y-%m-%d') except ValueError: raise Exception return execute( conn, "INSERT INTO Voyages(sid, bid, date_of_voyage) VALUES (:sid,:bid,:date_of_voyage)", { 'sid': sid, 'bid': bid, 'date_of_voyage': date_of_voyage })
def Appointments(conn): currentDoctor = str(login.user()) return execute( conn, "SELECT a.aid as AID, d.Doc_name AS Doctor_Name, n.N_name as Nurse_Name,\ p.P_name as Patient_Name, a.Appointment_date AS Appointment_Date, a.Appointment_time AS Time,\ a.Appointment_type as Appointment_Type , a.room_number as Room_Number\ FROM Appointments as A \ Join Nurse as n ON a.nid = n.nid \ Join Doctor as d ON a.did = d.did \ Join Patient as p ON a.pid = p.pid \ Where d.Doc_email = (?) ", (currentDoctor, ))
def add_patient(conn): userInputName = request.args.get('patient-name') userInputDob = request.args.get('date-of-birth') userInputGender = request.args.get('patient-gender') userInputNumber = request.args.get('phone-no') userInputAddress = request.args.get('phone-address') userInputMedHistory = request.args.get('med-history') if userInputName == None or userInputDob == None or userInputGender == None or userInputNumber == None or userInputAddress == None or userInputMedHistory == None: return return execute( conn, "INSERT INTO Patient ( P_name, DOB, Gender, P_number, P_address, medical_history) VALUES ( (?), (?), (?), (?), (?), (?));", (userInputName, userInputDob, userInputGender, userInputNumber, userInputAddress, userInputMedHistory))
def add_appointment(conn): doctorID = request.args.get('doctor-id') nurseID = request.args.get('nurse-id') patientID = request.args.get('patient-id') appointmentDate = request.args.get('appointment-date') appointmentTime = request.args.get('appointment-time') appointmentType = request.args.get('appointment-type') roomNumber = request.args.get('room-number') if doctorID == None or nurseID == None or patientID == None or appointmentDate == None or\ appointmentTime == None or appointmentType == None or roomNumber == None: return return execute(conn, "INSERT INTO Appointments (did, nid, pid, Appointment_date, Appointment_time,\ Appointment_type, room_number) VALUES ( (?), (?), (?), (?), (?), (?), (?));",\ (doctorID,nurseID,patientID,appointmentDate,appointmentTime,appointmentType,roomNumber,))
def Edit_appointments(conn): aid = request.args.get('aid') Appointment_date = request.args.get('appointment-date') Appointment_time = request.args.get('appointment-time') room_number = request.args.get('room-number') print(Appointment_date, " ", Appointment_time, " ", room_number) if Appointment_date == None or Appointment_time == None or \ room_number == None or aid == None: return return execute( conn, "UPDATE Appointments\ SET Appointment_date = (?), \ Appointment_time = (?), room_number = (?) \ WHERE aid = (?);", (Appointment_date, Appointment_time, room_number, aid))
def displayItemInStock(conn): # TODO: Insert SQL to report/sum up Inventory sqlCommand = "SELECT * FROM items;" return execute(conn, sqlCommand)
def addToCart(conn, item_quantity): # TODO: Insert SQL to add to cart # item_id = request.args.get('data-itemid') sqlCommand = "SELECT * FROM boats;" return execute(conn, sqlCommand)
def addboatfunc(conn, b_name, b_color): return execute( conn, f"insert into Boats (name, color) values ('{b_name}', '{b_color}')" )
def voyages(conn): return execute(conn, "SELECT v.sid, v.bid, v.date_of_voyage FROM Voyages AS v")
def add_voyage(conn, sid, bid, voyage): return execute( conn, f"INSERT INTO Voyages (sid, bid, date_of_voyage) VALUES ('{sid}', '{bid}', '{voyage}')" )
def Patients(conn): return execute(conn, "SELECT d.Doc_email, d.Doc_password FROM Doctor AS d")
def addUser(conn, name, number, email): return execute( conn, f"INSERT INTO USER(name, phoneNumber, email) VALUES('{name}', {number}, '{email}')" )
def sales(conn): sqlCommand = "SELECT sum(ordertotal) as Total FROM orders;" return execute(conn, sqlCommand)
def displayItems(conn): sqlCommand = "select * from items;" return execute(conn, sqlCommand)
def updateHouse(conn, hid): return execute(conn, f"UPDATE HOUSE SET status='closed' WHERE hid = {hid}")