def master_query(self):
"""
Prompts a general query to the user and attempts to execute it.
"""
while True:
menu_string = "\n######################################################\n"
menu_string += " Custom Query \n"
menu_string += "######################################################\n"
menu_string += "\nPlease select eh type of custom query you intend to input:\n"
menu_string += "\t 1. SELECTION query\n"
menu_string += "\t 2. INSERT/UPDATE/DELETE query\n"
menu_string += "\t 3. Exit"
print(menu_string)
mgr_input = input()
if re.match(r'^1.*', str(mgr_input)):
stmt = input("Please type your query:")
query_executer(stmt)
elif re.match(r'^2.*', str(mgr_input)):
stmt = input("Please type your query")
query_executer(stmt)
elif re.match(r'^3.*', str(mgr_input)):
print("Exiting...")
break
else:
print("Invalid Input")
return
def see_orders(self):
"""
Function for menu option 2
"""
username = self.login_resp['username']
get_order_sql = "SELECT * FROM orderTable WHERE rid IN "
get_order_sql += "(SELECT rid FROM request WHERE custname = '{}');".format(username)
while True:
query_executer(get_order_sql)
# response_top = result.head()
# print(response_top)
# size = result.shape[0]
# for i in range(size):
# print("{}\t{}\n".format(i,result[i]))
print("To continue:\n")
print("1. Make change to an order\n")
print("2. Go back\n")
user_input = input()
if re.match(r'^1', str(user_input)):
'''to edit order'''
self.edit_order()
elif re.match(r'^2', str(user_input)):
'''goes back to previous level'''
break
def edit_order(self):
"""
cancel/rate an order
"""
print("Please enter the order number\n")
oid= input()
get_order_sql = "SELECT * FROM orderTable WHERE oid = {};".format(oid)
order_response = query_executer(get_order_sql)
if(order_response.shape[0] == 0):
print("Order not found\n")
else:
get_request_sql = "(SELECT * FROM request WHERE rid = {});".format(order_response['rid'][0])
request_response = query_executer(get_request_sql)
if(request_response['custname'][0] == self.login_resp['username']):
while True:
# print(request)
print("Would you like to\n")
print("1. Cancel this order\n")
print("2. Rate this order\n")
print("3. Go back\n")
user_input = input()
if re.match(r'^1', str(user_input)):
'''to edit order'''
self.cancel_order(order_response['oid'][0])
print('Order cancelled')
elif re.match(r'^2', str(user_input)):
'''Rate this order'''
get_order_sql = "SELECT * FROM orderTable WHERE oid = {};".format(oid)
order_response = query_executer(get_order_sql)
if order_response['ordstatus'][0] != 'complete':
print("Cannot rate an unfinished order\n")
continue
print("From 1-5, how would you like to rate this order?\n")
rate = int(input())
if rate <=0 or rate >5:
print("Invalid rating\n")
continue
self.rate_order(order_response['oid'][0], rate)
print("Please leave your comment.\n")
comment = str(input())
self.comment_order(order_response['oid'][0],comment)
date_str = self.get_current_date()
self.edit_rate_date(order_response['oid'][0],date_str)
break
else:
break
else:
print("Order not found\n")
def edit_preference(self):
username = self.login_resp['username']
get_customer_sql = "SELECT * FROM customer WHERE username = '******'".format(username)
while True:
customer_response = query_executer(get_customer_sql)
print('Your current preference is \n \"{}\"'.format(customer_response['preferences'][0]))
print('please enter your new preference')
user_input = input()
edit_preference_sql = "UPDATE customer SET preferences = '{}' WHERE username = '******';".format(user_input, username)
query_executer(edit_preference_sql,insert = True)
print("Preference editted successfully")
break
def new_user(self):
"""
Creates and inserts a new user in the database
"""
operating = True
try:
while operating:
### 1.Prompt values
## 1. Prompt new username and email
print("Register: ")
new_user = input("Please input username: "******"Please enter your email: ")
stmt = "SELECT * FROM usertable WHERE username='******' or email='{}';".format(
new_user, new_email)
output = query_executer(
stmt, verbose=False
) # there shouldn't be any users in the output
if len(output) > 0:
print(
"Error: A user with the same username or email already exists."
)
continue
## 2. verify input email is valid
if not re.match(EMAIL_REGEX, new_email):
print("Make sure you input a valid email!")
continue # go back to main menu
## 3. protmpt and verify password
new_pass = getpass(
"Please input password with 1) 1 Uppercase 2) 1 lowercase 3) at least 8 characters:\n"
)
if not re.match(STRONG_EMAIL, new_pass):
print("Error: Password muss contain: ")
print(" 1 Uppercase, 1 lowercase, at least 8 characters")
continue
## 4. prompt user first and last name
firstname = input("First name: ")
lastname = input("Lasname: ")
## 5. Sex
print("Sex:\n1. Male\n2. Female")
user_input = input()
if re.match(r'^1.*', str(user_input)):
sex = "Male"
elif re.match(r'^2.*', str(user_input)):
sex = "Female"
else:
print("Invalid input")
continue
## 6. city
city = input("City: ")
## 7. Phonenumber
phonenum = input(
"Please input your phone number with no spaces or special characters: "
)
if not (phonenum.isdigit() and len(phonenum) > 6
and len(phonenum) < 15):
print("Invalid input")
## 8. dateofbirth
print("Date of birth: ")
year = input("year (YYYY): ")
month = input("month (MM): ")
day = input("day (DD): ")
if not (
year.isnumeric() and month.isnumeric()
and day.isnumeric() and
(int(str(datetime.date.today().year)) - int(year)) > 18):
print(
"Invalid input or age: you must be at least 18 years old to use the service."
)
date = str(
datetime.date(year=int(year),
month=int(month),
day=int(day)))
### 2. Create statement
stmt = "INSERT INTO usertable (username, password, email , firstname, lastname, sex, city , phoneNum, dateOfBirth) "
stmt += "VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}', {}, '{}') ;".format(
new_user, new_pass, new_email, firstname, lastname, sex,
city, phonenum, date)
# Execute insertion
result = query_executer(stmt, insert=True)
# Verify insertion worked
stmt = "SELECT * FROM usertable "
stmt += "WHERE username='******' ;\n".format(new_user)
result = query_executer(stmt)
while True:
if len(result) >= 1:
print(
"--INFO-- : User succesfully created! You can now log-in."
)
print(result)
sub_menu = "############################################################### \n"
sub_menu += " New User Registration \n"
sub_menu += "###############################################################\n"
sub_menu += "I want to register as a ...\n"
sub_menu += "1. Customer\n"
sub_menu += "2. Mate\n"
print(sub_menu)
user_input = input()
if re.match(r'^1.*', str(user_input)):
self.usertype = "Customer"
preferences = input(
"Please write your preferences: (max 1000 characters)\n"
)
stmt = "INSERT INTO customer (username, preferences) VALUES ('{}','{}')\n;".format(
new_user, preferences)
query_executer(stmt,
insert=True) # execute insertion.
print(
"Thank you! You can now log-in in the main menu"
)
operating = False # finish
break
elif re.match(r'^2.*', str(user_input)):
self.usertype = "Mate"
#TODO: Implement filling infomration for a new mate
print(
"--INFO--: Information complete. You may now log-in an apply to be a mate\\"
)
print(
"\t\t You cannot work as a mate until a manager reviews and accepts your application."
)
raise NotImplementedError(
"Functionality not available yet.")
else:
print("Invalid input")
continue
else:
raise ReferenceError(
"The record you tried to create raised an error.")
except Exception as e:
print("I/O error occurred\n")
print("ARGS:{}\n".format(e.args))
print("Error: ", e)
print(e.__traceback__)
print("Context: ", e.__context__)
print("Cause: ", e.__cause__)
def review_application(self):
"""
Allows the manager to see some application from a customer to a mate
"""
try:
while True:
menu_string = "\n######################################################\n"
menu_string += " Look for an Application \n"
menu_string += "######################################################\n"
menu_string += "1. See all applications\n"
menu_string += "2. Custom search\n"
menu_string += "3. Exit\n"
print(menu_string)
sub_input1 = input()
if re.match(r'^1', str(sub_input1)):
## Manager only allow to see the order in his charge
print("**You username**")
mngname = str(input())
stmt = "SELECT *"
stmt += "FROM application\n"
stmt += "WHERE mngname= " + "\'" + mngname + "\'" + ";"
query_executer(
stmt) # execute and display query and result
elif re.match(r'^2', str(sub_input1)):
## 1. Custom search according to different attributes
print("**You username**")
mngname = input()
print(
"Custom search: Choose custom preferences for your application :) "
)
### 1.1 status:Pending, Approved, Rejected
print("Status:\n 1) Pending 2)Approved 3)Rejected")
if re.match(r'^1', str(input())):
status = "Pending"
elif re.match(r'^2', str(sub_input1)):
status = "Approved"
elif re.match(r'^3', str(sub_input1)):
status = "Rejected"
else:
print("Invalid input")
continue
stmt = "SELECT *\n"
stmt += "FROM application "
stmt += "WHERE mngname=" + "\'" + mngname + "\'" + "\n"
stmt += "AND appStatus=" + "\'" + status + "\'" + ";"
query_executer(stmt)
elif re.match(r'^3', str(sub_input1)):
print("Exit")
break
else:
print("Invalid Input")
except Exception as e:
print("I/O error occurred\n")
print("ARGS:{}\n".format(e.args))
print("Error: ", e)
print(e.__traceback__)
print("Context: ", e.__context__)
def overview_activity(self):
"""
Allows the manager to see and modify some activity (insert, delete, udpate)
"""
try:
while True:
menu_string = "\n######################################################\n"
menu_string += " Overview for an Activity \n"
menu_string += "######################################################\n"
menu_string += "1. Update\n"
menu_string += "2. Delete\n"
menu_string += "3. Insert\n"
menu_string += "4. Exit\n"
print(menu_string)
sub_input1 = input()
if re.match(r'^1', str(sub_input1)):
# Check the value that manager want to update
# Insert the new record in to the modification table
print("Enter the id of activity you want to modified")
aid = str(input())
#print("You enter:"+aid);
print("Enter the description you want to update")
description = str(input()).strip()
#print("You enter:"+description)
stmt = "UPDATE activity\n"
stmt += "SET description=" + "\'" + description + "\'" + "\n"
stmt += "WHERE aid=" + aid + ";"
print(stmt)
query_executer(
stmt,
insert=True) # execute and display query and result
elif re.match(r'^2', str(sub_input1)):
print("Enter the id of activity you want to delete")
aid = str(input())
stmt = "DELETE FROM activity WHERE aid=" + aid + ";"
query_executer(stmt, insert=True)
elif re.match(r'^3', str(sub_input1)):
print("Enter the id of activity you want to insert")
aid = str(input())
print("Enter the description")
description = str(input())
print("Enter the mngName")
mngname = str(input())
stmt = "INSERT INTO activity " \
"VALUES("+aid+"," +"\'"+description+"\'"+","+"\'"+mngname+"\'"+");"
query_executer(stmt, insert=True)
elif re.match(r'^4', str(sub_input1)):
print("Exit")
break
else:
print("Invalid Input")
except Exception as e:
print("I/O error occurred\n")
print("ARGS:{}\n".format(e.args))
print("Error: ", e)
print(e.__traceback__)
print("Context: ", e.__context__)
def modify_order(self):
"""
Allows the manager to modify an existing order
"""
try:
while True:
menu_string = "\n######################################################\n"
menu_string += " Modify the Order \n"
menu_string += "######################################################\n"
menu_string += "1. Update\n"
menu_string += "2. Delete\n"
menu_string += "3. Insert\n"
menu_string += "4. Exit\n"
print(menu_string)
sub_input1 = input()
if re.match(r'^1', str(sub_input1)):
# Check the value that manager want to update
# Insert the new record in to the modification table
print("Enter the id of order you want to modified")
oid = str(input())
print("Enter the your user name")
mgnName = str(input())
print(
"Enter the status of order you want to change 1)active 2)pending 3) complete"
)
choice = str(input())
if re.match(r'^1', choice):
status = "active"
elif re.match(r'^2', choice):
status = "pending"
elif re.match(r'^3', choice):
status = 'complete'
else:
print("Invalid input")
continue
stmt = "UPDATE ordertable\n"
stmt += "SET ordstatus=" + "\'" + status + "\'" + "\n"
stmt += "WHERE oid=" + oid + ";"
today = date.today()
d1 = today.strftime("%Y-%m-%d")
query_executer(
stmt,
insert=True) # execute and display query and result
stmt1 = "INSERT INTO modification VALUES(" + "\'" + mgnName + "\'" + "," + oid + "," + "\'" + d1 + "\'" + ");"
query_executer(stmt1, insert=True)
elif re.match(r'^2', str(sub_input1)):
print("Enter the id of order you want to modified")
oid = str(input())
stmt = "DELETE FROM modification WHERE oid=" + oid + ";"
query_executer(stmt, insert=True)
stmt1 = "DELETE FROM ordertable WHERE oid=" + oid + ";"
query_executer(stmt1, insert=True)
elif re.match(r'^3', str(sub_input1)):
print("Enter the id of order you want to insert")
oid = str(input())
print("Enter the start day in from of YY-MM-DD")
startDate = str(input())
print(
"Enter the status of the order1)active 2) pending 3) complete"
)
choice = str(input())
if re.match(r'^1', choice):
status = "active"
elif re.match(r'^2', choice):
status = "pending"
elif re.match(r'^3', choice):
status = 'complete'
else:
print("Invalid input")
continue
print("Enter the id of request you want to insert")
rid = str(input())
# Those attribute should be insert by costumer
# print("Enter the rating day in from of YY-MM-DD")
# ratingDate = str(input())
# print("Enter the comment")
# comment = str(input())
# print("Enter the rating")
# rating = str(input())
stmt = "INSERT INTO orderTable (oid, startDate, ordStatus,rid) " \
"VALUES("+oid+"," +"\'"+startDate+"\'"+","+"\'"+status+"\'"+","+rid+");"
query_executer(stmt, insert=True)
elif re.match(r'^4', str(sub_input1)):
print("Exit")
break
else:
print("Invalid Input")
except Exception as e:
print("I/O error occurred\n")
print("ARGS:{}\n".format(e.args))
print("Error: ", e)
print(e.__traceback__)
print("Context: ", e.__context__)
def see_mates(self):
"""
Function for menu option 1.
"""
try:
while True:
menu_string = "\n######################################################\n"
menu_string += " Look for a Mate \n"
menu_string += "######################################################\n"
menu_string += "1. See all mates\n"
menu_string += "2. Custom search\n"
menu_string += "3. Exit\n"
print(menu_string)
sub_input1 = input()
if re.match(r'^1', str(sub_input1)):
## 1.7 Order by
print("**Order by**")
print("1.Hourly Rate \n2.Age \n3.Nickname \n4.None")
if re.match(r'^1', str(input())):
order = "hourlyrate"
elif re.match(r'^2', str(input())):
order = "age"
elif re.match(r'^3', str(input())):
order = "nickname"
elif re.match(r'^3', str(input())):
order = None
else:
print("Invalid input")
continue
stmt = "SELECT nickname,\n\t description\n\t, sex,\n\t language,\n\t height,\n\t weight,\n\t hourlyrate \n"
stmt += "FROM mate m \n"
stmt += "JOIN usertable u \n"
stmt += "\t ON m.username = u.username \n"
if order is not None:
stmt += "ORDER BY {}\n".format(order)
stmt += ";\n"
query_executer(stmt) # execute and display query and result
elif re.match(r'^2', str(sub_input1)):
## 1. Custom search according to different attributes
print("Custom search: Choose custom preferences for your mate :) ")
### 1.1 Sex
print("Sex:\n 1) Male 2)Female")
if re.match(r'^1', str(input())):
sex = "Male"
elif re.match(r'^2', str(sub_input1)):
sex = "Female"
else:
print("Invalid input")
continue
### 1.2. Age
print("**Age**")
lower_age = input("Min: ")
upper_age = input("Max: ")
if lower_age < 18:
print("I'm calling the police...")
raise ValueError("WARNING: Pedophile spotted")
### 1.3 Languages
print("**Languages spoken:**")
print("1) English \n2) French\n3) Both")
if re.match(r'^1', str(input())):
lang = "English"
elif re.match(r'^2', str(input())):
lang = "French"
elif re.match(r'^3', str(input())):
lang = "Eng & French"
else:
print("Invalid input")
continue
## 1.4 Height
print("**Height (cm)**")
lower_height = float(input("Min: "))/100
upper_height = float(input("Max: "))/100
if lower_height > upper_height:
print("Invalid input")
continue
## 1.5 Weight
print("**Weight (kg)**")
lower_weight = int(input("Min: "))
upper_weight = int(input("Max: "))
if lower_weight > upper_weight:
print("Invalid input")
continue
## 1.6 Hourly Rate
print("**Hourly rate (CAD$)**")
lower_rate = int(input("Min: "))
upper_rate = int(input("Max: "))
if lower_rate > upper_rate:
print("Invalid input")
continue
## 1.7 Order by
print("**Order by**")
print("1.Hourly Rate \n2.Age \n3.Nickname")
if re.match(r'^1', str(input())):
order = "hourlyrate"
elif re.match(r'^2', str(input())):
order = "age"
elif re.match(r'^3', str(input())):
order = "nickname"
else:
print("Invalid input")
continue
stmt = "SELECT * FROM "
stmt += "\t ("
stmt = "\t SELECT \n"
stmt += "\t\t m.nickname, \n"
stmt += "\t\t u.sex,\n"
stmt += "\t\t DATE_PART('year', CURRENT_DATE) - DATE_PART('year', u.dateofbirth) AS age,\n"
stmt += "\t\t m.language,\n"
stmt += "\t\t m.height,\n"
stmt += "\t\t m.weight,\n"
stmt += "\t\t m.hourlyrate,\n"
stmt += "\t\t m.description\n"
stmt += "\t FROM mate m \n"
stmt += "\t JOIN usertable u \n"
stmt += "\t\t ON m.username = u.username"
stmt += "\t WHERE sex='{}' AND language='{}' \n".format(sex,lang)
stmt += "\t\t AND (height BETWEEN {} AND {}) \n".format(lower_height, upper_height)
stmt += "\t\t AND (weight BETWEEN {} AND {}) \n".format(lower_weight, upper_weight)
stmt += "\t\t AND (hourlyrate BETWEEN {} AND {}) \n".format(lower_rate, upper_rate)
stmt += "\t ORDER BY '{}' \n".format(order)
stmt += "\t ) T"
stmt += "WHERE T.age BETWEEN {} AND {}".format(lower_age, upper_age)
stmt += ";\n"
elif re.match(r'^3', str(sub_input1)):
print("Exit")
break
else:
print("Invalid Input")
except Exception as e:
print("I/O error occurred\n")
print("ARGS:{}\n".format(e.args))
print("Error: ", e)
print(e.__traceback__)
print("Context: ", e.__context__)
def edit_rate_date(self, oid, date):
edit_sql = "UPDATE orderTable SET ratingDate = '{}' WHERE oid = {};".format(date, oid)
query_executer(edit_sql,insert = True)
def cancel_order(self, oid):
cancel_order_sql = "UPDATE orderTable SET ordStatus = 'complete' WHERE oid = {};".format(oid)
query_executer(cancel_order_sql,insert = True)
def comment_order(self, oid,comment):
comment_order_sql = "UPDATE orderTable SET comment = '{}' WHERE oid = {};".format(comment,oid)
query_executer(comment_order_sql,insert = True)
def rate_order(self, oid,rate):
rate_order_sql = "UPDATE orderTable SET rating = {} WHERE oid = {};".format(rate,oid)
query_executer(rate_order_sql,insert = True)