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'))