def submit_assignment(class_name, assignment_name, content):
db = auth.get_db()
res = db.execute("""
SELECT * FROM user_submissions
WHERE class_name = ? AND assignment_name = ? AND username = ?;
""", (class_name, assignment_name, g.user)).fetchall()
if len(res) == 0:
sql = """
INSERT INTO user_submissions
(submission, username, class_name, assignment_name, discription)
VALUES (?, ?, ?, ?, ?);
"""
else:
sql = """
UPDATE user_submissions SET submission = ?
WHERE username = ? AND class_name = ? AND assignment_name = ? AND discription = ?;
"""
#print(sql)
db.execute(sql, (
content,
g.user,
class_name,
assignment_name,
f"{class_name}/{assignment_name}"
))
db.commit()
def internal_verify(submission_id):
if request.remote_addr != '127.0.0.1':
return 'error', 404
db = auth.get_db()
content = db.execute(
"""
SELECT submission FROM user_submissions
WHERE id = ?;
""", (submission_id)
).fetchone()[0]
return render('verify.html', content=content)
def blackout(itemname):
form = blackoutForm()
if request.method == 'GET':
return render_template('blackout.html', item=itemname, form=form)
db = get_db()
cur = db.cursor()
cur.execute(
'INSERT INTO ITEM_BLACKOUT (Title, Owner_email, Start_date, End_date) VALUES (?,?,?,?)',
(itemname, g.user['Email'], form.start.data, form.end.data))
db.commit()
return redirect(url_for('ownerItems'))
def get_assignments(username):
db = auth.get_db()
raw = db.execute(
'SELECT class_name, assignment_name, submission FROM user_submissions WHERE username = ?',
(username,)
).fetchall()
data = {}
for class_name, assignment_name, submission in raw:
if class_name not in data:
data[class_name] = {}
data[class_name][assignment_name] = submission
return data
def profile():
db = get_db()
cur = db.cursor()
# get the number of items rented out, the number of items borrowed, the number of items posted, and the user's interests
owner_rentals = cur.execute(
'SELECT COUNT (*) FROM RENTAL WHERE Owner_email=?',
(g.user['Email'], ))
num_owner_rentals = cur.fetchone()[0]
renter_rentals = cur.execute(
'SELECT COUNT (*) FROM RENTAL WHERE Renter_email=?',
(g.user['Email'], ))
num_renter_rentals = cur.fetchone()[0]
all_items = cur.execute('SELECT COUNT (*) FROM ITEM WHERE Owner_email=?',
(g.user['Email'], ))
num_items = cur.fetchone()[0]
categories = cur.execute(
'SELECT Name FROM CATEGORY EXCEPT SELECT Category_name FROM INTERESTED_IN WHERE User_email=? ORDER BY Category_name ASC',
(g.user['Email'], )).fetchall()
interests = cur.execute('SELECT * FROM INTERESTED_IN WHERE User_email=?',
(g.user['Email'], )).fetchall()
all_interests = ""
# pass interests to profile.html if not empty
if request.method == 'GET':
if interests:
for i in interests:
all_interests += i[
1] + ", " # stores only the names of the user's interests
return render_template('profile.html',
o_rentals=num_owner_rentals,
r_rentals=num_renter_rentals,
items=num_items,
interests=all_interests[:-2],
categories=categories)
else:
return render_template('profile.html',
o_rentals=num_owner_rentals,
items=num_items,
r_rentals=num_renter_rentals,
itmes=num_items,
categories=categories)
elif request.method == 'POST': # if user pressed add interest button, inserts a new row into INTERESTED_IN
cur.execute(
'INSERT INTO INTERESTED_IN (User_email, Category_name) VALUES (?,?)',
(
g.user['Email'],
request.form['interest'],
))
db.commit()
cur.close()
return redirect(url_for('profile'))
def view_all():
# Load all items from DB, then pass to browse.html file to display
#use the global variable filter_item
form = FilterForm()
# connect to db
db = get_db()
cur = db.cursor()
#multiple if statements for different cases of filtration. ex. 2 categories together, only filter by category etc.
#depending on what case of filtration is needed, we will execute a different query on the database.
if request.method == 'POST' and form.validate_on_submit():
if (form.category.data != 'none' and form.city.data != 'none'
and form.maxPrice.data != 'none'):
cur.execute(
"SELECT * FROM ITEM, USER WHERE USER.Email = ITEM.Owner_email AND Category_name=? AND Daily_rate<=? AND USER.City =?",
(form.category.data, form.maxPrice.data, form.city.data))
elif (form.category.data != 'none' and form.city.data != 'none'):
cur.execute(
"SELECT * FROM ITEM, USER WHERE Category_name=? AND USER.Email = ITEM.Owner_email AND USER.City =?",
(form.category.data, form.city.data))
elif (form.category.data != 'none' and form.maxPrice.data != 'none'):
cur.execute(
"SELECT * FROM ITEM WHERE Category_name=? AND Daily_rate<=?",
(form.category.data, form.maxPrice.data))
elif (form.maxPrice.data != 'none' and form.city.data != 'none'):
cur.execute(
"SELECT * FROM ITEM, USER WHERE Daily_rate<=? AND USER.Email = ITEM.Owner_email AND USER.City =?",
(form.maxPrice.data, form.city.data))
elif (form.category.data != 'none'):
cur.execute("SELECT * FROM ITEM WHERE Category_name=?",
(form.category.data, ))
elif (form.city.data != 'none'):
cur.execute(
"SELECT * FROM ITEM, USER WHERE USER.Email = ITEM.Owner_email AND USER.City =?",
(form.city.data, ))
elif (form.maxPrice.data != 'none'):
cur.execute("SELECT * FROM ITEM WHERE Daily_rate<=?",
(form.maxPrice.data, ))
else:
cur.execute("SELECT * FROM ITEM")
else:
cur.execute("SELECT * FROM ITEM")
data = cur.fetchall() #an array of all items fetched from DB
return render_template('browse.html', data=data,
form=form) #show the data in the html
def verify_assignment(class_name, assignment_name):
try:
db = auth.get_db()
assignment_id = int(db.execute(
"""
SELECT id FROM user_submissions
WHERE username = ? AND class_name = ? AND assignment_name = ?;
""", (g.user, class_name, assignment_name)
).fetchone()[0])
run_chrome(f'/internal_verify/{assignment_id}')
flash(('success','sucessfully verified'))
except:
flash(('error', 'error verifying'))
return redirect(f'/') #view/{class_name}/{assignment_name}')
def report(ownerEmail):
form = ReportForm()
if request.method == 'GET':
return render_template('report.html', form=form)
elif request.method == 'POST':
description = form.description.data
date = form.dateOfOffense.data
todaysDate = datetime.date(datetime.now())
db = get_db()
cur = db.cursor()
cur.execute(
"INSERT INTO REPORT (User_email, Reported_user_email, Admin_ID, Offense_description, Date_of_offense, Date_of_report) VALUES (?,?,?,?,?,?)",
(g.user['Email'], ownerEmail, None, description, date, todaysDate))
db.commit()
return redirect(url_for('renterTransactions'))
def search():
data=[]
if request.method == 'POST':
search_text = request.form.get('search_text')
sql = """
SELECT class_name, assignment_name, submission FROM user_submissions
WHERE username = '******' AND (assignment_name LIKE '%s' OR class_name LIKE '%s');
""" % (g.user, search_text, search_text)
db = auth.get_db()
data = db.execute(sql).fetchall()
db.commit()
print(data)
return render(
'search.html',
data=data
)
def postItem():
form = PostItemForm()
db = get_db()
cur = db.cursor()
if request.method == 'POST':
print(form.daily_rate.data)
if form.validate_on_submit():
title = form.title.data
category = form.category.data
if ('<' in category):
category = category.split(' < ')[1]
description = form.description.data
daily_rate = form.daily_rate.data
print(daily_rate)
print(type(daily_rate))
try:
item = cur.execute('INSERT INTO ITEM VALUES (?, ?, ?, ?, ?)', (
title,
category,
g.user['Email'],
description,
daily_rate,
))
db.commit()
return redirect(url_for('ownerItems'))
except sqlite3.IntegrityError:
flash('Item title already in use!', 'warning')
# Get all categories
categories = cur.execute('SELECT * FROM CATEGORY').fetchall()
# Convert categories with parents to a string of format "Parent < Name"
ctgr = []
for c in categories:
if c[1] == None:
ctgr.append(c[0])
else:
val = c[1] + ' < ' + c[0]
ctgr.append(val)
# sort alphabetically
ctgr.sort()
# set form's Select Field choices
form.category.choices = [c for c in ctgr] # category
form.category.data = ctgr[0]
return render_template('postItem.html', form=form)
def class_page_ex(class_name, extension=None, assignment_name=None):
# print(extension, assignment_name)
if class_name not in classes:
flash(('error', f'not valid request {request.url}'))
return redirect('/')
if assignment_name is not None:
return render(
'class.html',
class_name=class_name,
extension=extension,
assignment_name=assignment_name,
)
elif extension is not None:
if extension == 'submissions':
db = auth.get_db()
all_assignments = get_assignments(g.user)
if class_name not in all_assignments:
all_assignments[class_name] = {}
content = all_assignments[class_name]
for key in content:
if len(content[key]) >= 10:
content[key] = content[key][:10] + '...'
return render(
'class.html',
class_name=class_name,
extension=extension,
content=content
)
elif extension == 'assignments':
return render(
'class.html',
class_name=class_name,
extension=extension,
content=class_assignments[class_name]
)
else: # overview
return render(
'class.html',
class_name=class_name,
content=class_overview[class_name]
)
def editItem():
form = EditItemForm()
db = get_db()
cur = db.cursor()
itemName = request.args.get('item')
item = cur.execute('SELECT * FROM ITEM WHERE Title=? AND Owner_email=?', (
itemName,
g.user['Email'],
)).fetchone() # gets the item that the user desires to edit
categories = cur.execute(
'SELECT Name FROM CATEGORY').fetchall() # gets all the categories
if form.validate_on_submit(): # update item using entered information
if form.category.data is None: # form.category.data will be none if user doesn't select a new category
cat = item[1]
else:
cat = form.category.data
cur.execute(
'UPDATE ITEM SET Title=?, Category_name=?, Description=?, Daily_rate=? WHERE Title=?',
(
form.title.data,
cat,
form.description.data,
form.daily_rate.data,
itemName,
))
db.commit()
cur.close()
return redirect(url_for('ownerItems'))
elif request.method == 'GET': #populates the form with the selected item's information
form.title.data = item[0]
form.category.choices = [(g[0]) for g in categories]
form.category.data = item[1]
form.description.data = item[3]
form.daily_rate.data = item[4]
return render_template('editItem.html', form=form)
def editProfile():
form = UserInfoForm()
db = get_db()
cur = db.cursor()
user = cur.execute(
'SELECT * FROM USER WHERE Email=?', (g.user['Email'], )).fetchone(
) #gets the current logged in user to pass to editProfile.html
if form.validate_on_submit(
): #update g.user table with entered information
cur.execute(
'UPDATE USER SET Email=?, First_name=?, Last_name=?, Dob=?, Street_address =?, City=?, Province=?, Postal_code=? WHERE Email=?',
(
form.email.data,
form.fname.data,
form.lname.data,
form.dob.data,
form.street.data,
form.city.data,
form.province.data,
form.postalCode.data,
g.user['Email'],
))
db.commit()
cur.close()
return redirect(url_for('profile'))
elif request.method == 'GET': #populates the form with the current_user's information
form.email.data = user[0]
form.fname.data = user[2]
form.lname.data = user[3]
form.dob.data = datetime.strptime(user[4], '%Y-%m-%d')
form.street.data = user[5]
form.city.data = user[6]
form.province.data = user[7]
form.postalCode.data = user[8]
return render_template('editProfile.html', form=form, user=user)
def rent_item(title):
form = RentalRequestForm()
if request.method == 'POST':
# if GET, return a html form for user to enter their transaction + rental information
# if POST, create a transaction entry in DB using user information -> then redirect back to home page
if form.validate_on_submit():
start = form.startDate.data
duration = form.duration.data
pickup = form.pickup.data
dropoff = form.dropoff.data
#connect to the database so we can add a new entry
db = get_db()
cur = db.cursor()
#get the relevant information about this item
item = cur.execute("SELECT * FROM ITEM WHERE Title=?",
(title, )).fetchone()
#title is probably not returning anything!
#return an error message if nobody is logged in at the moment
if (g.user is None):
flash(
'Please login or register for an account if you would like to rent this item',
'success')
return render_template(
'rentItem.html', title=title, form=form
) #render the home page again or a confirmation page
cur.execute(
"INSERT INTO RENTAL (Renter_email, Owner_email, Item_title, Start_date, Duration, Pick_up_time, Drop_off_time, Type) VALUES (?,?,?,?,?,?,?,?)",
(g.user['Email'], item[2], item[0], start, duration, pickup,
dropoff, "pending"))
db.commit()
flash('The rental request has been submitted successfully.',
'success')
return redirect(url_for('home'))
return render_template(
'rentItem.html', title=title,
form=form) #render the home page again or a confirmation page
def ownerItems():
db = get_db()
cur = db.cursor()
blackout_dict = {} #empty dictionary
all_items = cur.execute(
'SELECT * FROM ITEM WHERE Owner_email=?',
(g.user['Email'],
)).fetchall() #gets all items that the current user owns
db.commit()
for item in all_items:
blackout_dict[item[0]] = "None"
blackouts = cur.execute(
'SELECT * FROM ITEM_BLACKOUT WHERE Owner_email=?',
(g.user['Email'],
)).fetchall() #gets all blackouts that the current user has set
for b in blackouts:
for i in all_items:
if i[0] == b[0]:
blackout_dict[i[0]] = b[2] + " to " + b[3]
if request.method == 'GET':
return render_template('items.html',
items=all_items,
blackouts=blackout_dict)
elif request.method == 'POST':
thetype = request.args.get(
't'
) # type determines if delete or add blackout button was pressed
if thetype == '1' and request.form['deleteBtn'] is not None:
cur.execute('DELETE FROM ITEM WHERE Title=? AND Owner_email=?',
(request.form['deleteBtn'], g.user['Email']))
db.commit()
cur.close()
return redirect(url_for('ownerItems'))
def renterTransactions():
db = get_db()
cur = db.cursor()
# gets the pending, booked, and complete rentals where the current user is the renter
pending = cur.execute(
'SELECT * FROM RENTAL WHERE Renter_email=? AND Type=?', (
g.user['Email'],
'pending',
)).fetchall() # not approved by owner
booked = cur.execute(
'SELECT * FROM RENTAL WHERE Renter_email=? AND Type=?', (
g.user['Email'],
'booked',
)).fetchall() # ongoing rental
days_remaining = determineDaysRemaining(
booked
) # determine the number of days remaining for each booked rental
complete = cur.execute(
'SELECT * FROM RENTAL WHERE Renter_email=? AND Type=?', (
g.user['Email'],
'complete',
)
).fetchall(
) # completed rental (item returned and owner has marked it as complete)
# passes only the non null rentals to 'renterTransactions.html', along with the days remaining
if request.method == 'GET':
if pending and booked and complete:
return render_template('renterTransactions.html',
pending=pending,
booked=booked,
days_remaining=days_remaining,
complete=complete,
zip=zip)
elif pending and booked:
return render_template('renterTransactions.html',
pending=pending,
booked=booked,
days_remaining=days_remaining,
zip=zip)
elif pending and complete:
return render_template('renterTransactions.html',
pending=pending,
complete=complete)
elif booked and complete:
return render_template('renterTransactions.html',
booked=booked,
days_remaining=days_remaining,
complete=complete,
zip=zip)
elif pending:
return render_template('renterTransactions.html', pending=pending)
elif booked:
return render_template('renterTransactions.html',
booked=booked,
days_remaining=days_remaining,
zip=zip)
elif complete:
return render_template('renterTransactions.html',
complete=complete)
else:
return render_template('renterTransactions.html')
elif request.method == 'POST': # updates either the Rating or Review attribute for a completed RENTAL
rate = request.args.get(
'rate'
) # used to determine if the rating or review button was pressed2
itemid = request.args.get('itemid')
if complete and rate == '1' and request.form['ratingBtn'] is not None:
cur.execute(
'UPDATE RENTAL SET Rating=? WHERE tID=?',
(int(request.form['rating']), request.args.get('itemid')))
elif complete and rate == '0' and request.form['reviewBtn'] is not None:
cur.execute('UPDATE RENTAL SET Review=? WHERE tID=?',
(request.form['review'], request.args.get('itemid')))
db.commit()
cur.close()
return redirect(url_for('renterTransactions'))
def ownerTransactions():
db = get_db()
cur = db.cursor()
# gets the pending, booked, and complete rentals where the current user is the owner
pending = cur.execute(
'SELECT * FROM RENTAL WHERE Owner_email=? AND Type=?', (
g.user['Email'],
'pending',
)).fetchall() # need to approve
booked = cur.execute('SELECT * FROM RENTAL WHERE Owner_email=? AND Type=?',
(
g.user['Email'],
'booked',
)).fetchall() # active rental
days_remaining = determineDaysRemaining(
booked
) # determine the number of days remaining for each booked rental
complete = cur.execute(
'SELECT * FROM RENTAL WHERE Owner_email=? AND Type=?', (
g.user['Email'],
'complete',
)).fetchall() # item returned
# passes only the non null rentals to 'ownderTransactions.html', along with the days remaining
if request.method == 'GET':
if pending and booked and complete:
return render_template('ownerTransactions.html',
pending=pending,
booked=booked,
days_remaining=days_remaining,
complete=complete,
zip=zip)
elif pending and booked:
return render_template('ownerTransactions.html',
pending=pending,
booked=booked,
days_remaining=days_remaining,
zip=zip)
elif pending and complete:
return render_template('ownerTransactions.html',
pending=pending,
complete=complete)
elif booked and complete:
return render_template('ownerTransactions.html',
booked=booked,
complete=complete,
days_remaining=days_remaining,
zip=zip)
elif pending:
return render_template('ownerTransactions.html', pending=pending)
elif booked:
return render_template('ownerTransactions.html',
booked=booked,
days_remaining=days_remaining,
zip=zip)
elif complete:
return render_template('ownerTransactions.html', complete=complete)
else:
return render_template('ownerTransactions.html')
elif request.method == 'POST':
# updates a pending rental to booked, or a booked rental to complete
type = request.args.get('t')
if pending and type == '1' and request.form['approveBtn'] is not None:
cur.execute('UPDATE RENTAL SET Type=? WHERE tID=?',
('booked', request.form['approveBtn']))
elif booked and type == '0' and request.form['completeBtn'] is not None:
cur.execute('UPDATE RENTAL SET Type=? WHERE tID=?',
('complete', request.form['completeBtn']))
db.commit()
cur.close()
return redirect(url_for('ownerTransactions'))
