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}")
