def delete_user():
user_id = flask.request.form['userid']
db.execute('DELETE FROM users WHERE user_id = %s', (user_id))
flask.flash('User deleted', 'message')
return flask.redirect(flask.url_for('admin'))
def add_item(self, video_id):
self._create_order()
db.execute(
"INSERT INTO orders (order_id, video_id) VALUES (%s, %s)",
(self.order_id, video_id)
)
def follow_user():
userid = flask.request.form['userid']
db.execute('INSERT INTO follow (user_id_to, user_id_from) VALUES (%s, %s)', (userid, current_user.get_id()))
flask.flash('You are now following ' + userid, 'message')
return flask.redirect(flask.url_for('settings'))
def stop_follow_user():
userid = flask.request.form['userid']
db.execute('DELETE FROM follow WHERE user_id_to = %s AND user_id_from = %s', (userid, current_user.get_id()))
flask.flash('You are no longer following ' + userid, 'message')
return flask.redirect(flask.url_for('settings'))
def settings():
# change preferred genres
# update balance
# get permissions from db
db.execute(
'SELECT fave_perm, rank_perm, watched_perm, balance FROM users WHERE user_id = %s',
(current_user.get_id()))
results = db.fetchall()[0]
perms = {}
perms['fave'] = results[0]
perms['rank'] = results[1]
perms['watch'] = results[2]
balance = results[3]
# get preferred genres from db
db.execute(
'SELECT genre_name FROM prefers, genre WHERE prefers.user_id = %s AND prefers.genre_id = genre.genre_id',
(current_user.get_id()))
results = db.fetchall()
genres = [genre[0] for genre in results]
genre_string = ', '.join(genres)
return flask.render_template('settings.html',
perms=perms,
genres=genre_string,
balance=balance)
def increase_balance():
balance_inc = flask.request.form['balance']
db.execute('UPDATE users SET balance = balance + %s WHERE user_id = %s', (balance_inc, current_user.get_id()))
flask.flash('Balance updated', 'message')
return flask.redirect(flask.url_for('settings'))
def get_items(self):
self._create_order()
db.execute("SELECT video_id FROM orders WHERE order_id=%s",
(self.order_id, ))
return [i[0] for i in db.fetchall()]
def change_watch_perm():
new_perm_text = flask.request.form['favorites']
new_perm = { 'private' : 0, 'friends' : 1, 'public' : 2 }[new_perm_text]
db.execute('UPDATE users SET watched_perm = %s WHERE user_id = %s', (new_perm, current_user.get_id()))
flask.flash('Permission changed', 'message')
return flask.redirect(flask.url_for('settings'))
def follow_user():
userid = flask.request.form['userid']
db.execute('INSERT INTO follow (user_id_to, user_id_from) VALUES (%s, %s)',
(userid, current_user.get_id()))
flask.flash('You are now following ' + userid, 'message')
return flask.redirect(flask.url_for('settings'))
def increase_balance():
balance_inc = flask.request.form['balance']
db.execute('UPDATE users SET balance = balance + %s WHERE user_id = %s',
(balance_inc, current_user.get_id()))
flask.flash('Balance updated', 'message')
return flask.redirect(flask.url_for('settings'))
def fetch(user_id):
db.execute("SELECT * FROM users WHERE user_id=%s", (user_id, ))
raw_results = db.fetchone()
if raw_results is None:
return None
else:
return User(*raw_results)
def fetch(user_id):
db.execute("SELECT * FROM users WHERE user_id=%s", (user_id, ))
raw_results = db.fetchone()
if raw_results is None:
return None
else:
return User(*raw_results)
def _create_order(self):
if self.order_id is not None:
return
app.logger.info("Creating new order with user_id: %s", self.user_id)
db.execute(
"INSERT INTO ordersmeta (user_id,completed) VALUES (%s, NULL)",
(self.user_id, ))
def get_items(self):
self._create_order()
db.execute(
"SELECT video_id FROM orders WHERE order_id=%s",
(self.order_id, )
)
return [i[0] for i in db.fetchall()]
def stop_follow_user():
userid = flask.request.form['userid']
db.execute(
'DELETE FROM follow WHERE user_id_to = %s AND user_id_from = %s',
(userid, current_user.get_id()))
flask.flash('You are no longer following ' + userid, 'message')
return flask.redirect(flask.url_for('settings'))
def change_watch_perm():
new_perm_text = flask.request.form['favorites']
new_perm = {'private': 0, 'friends': 1, 'public': 2}[new_perm_text]
db.execute('UPDATE users SET watched_perm = %s WHERE user_id = %s',
(new_perm, current_user.get_id()))
flask.flash('Permission changed', 'message')
return flask.redirect(flask.url_for('settings'))
def _create_order(self):
if self.order_id is not None:
return
app.logger.info("Creating new order with user_id: %s", self.user_id)
db.execute(
"INSERT INTO ordersmeta (user_id,completed) VALUES (%s, NULL)",
(self.user_id, )
)
def new_series():
if flask.request.form['series_title'] == '':
flask.flash('Missing required field: title', 'error')
return flask.redirect(flask.url_for('admin'))
title = flask.request.form['series_title']
db.execute('INSERT INTO series (title) VALUES (%s)', (title))
flask.flash('New series added', 'message')
return flask.redirect(flask.url_for('admin'))
def __init__(self, user_id):
db.execute(
"SELECT (order_id) FROM ordersmeta WHERE completed IS NULL AND user_id=%s",
(user_id, ))
result = db.fetchone()
self.user_id = user_id
if result is None:
self.order_id = None
else:
self.order_id = result[0]
def __init__(self, user_id):
db.execute(
"SELECT (order_id) FROM ordersmeta WHERE completed IS NULL AND user_id=%s",
(user_id, )
)
result = db.fetchone()
self.user_id = user_id
if result is None:
self.order_id = None
else:
self.order_id = result[0]
def delete_movie(movie_id):
db.execute('DELETE FROM video WHERE video_id = %s', (movie_id))
db.execute('DELETE FROM categorize WHERE video_id = %s', (movie_id))
db.execute('DELETE FROM comment WHERE video_id = %s', (movie_id))
db.execute('DELETE FROM likes WHERE video_id = %s', (movie_id))
flask.flash('Video deleted', 'message')
return flask.redirect(flask.url_for('admin'))
def change_genres():
liked_genres = [g.strip() for g in flask.request.form['genres'].split(',')]
# generate list of genre names and ids
genre_ids = {}
if len(liked_genres) > 0:
db.execute('SELECT * FROM genre')
genres = db.fetchall()
for genre in genres:
genre_ids[genre[1].lower()] = genre[0]
for lg in liked_genres:
if lg not in genre_ids:
continue
db.execute('INSERT INTO prefers VALUES (%s, %s)', (current_user.get_id(), genre_ids[lg]))
flask.flash('Preferred genres changed', 'message')
return flask.redirect(flask.url_for('settings'))
def change_genres():
liked_genres = [g.strip() for g in flask.request.form['genres'].split(',')]
# generate list of genre names and ids
genre_ids = {}
if len(liked_genres) > 0:
db.execute('SELECT * FROM genre')
genres = db.fetchall()
for genre in genres:
genre_ids[genre[1].lower()] = genre[0]
for lg in liked_genres:
if lg not in genre_ids:
continue
db.execute('INSERT INTO prefers VALUES (%s, %s)',
(current_user.get_id(), genre_ids[lg]))
flask.flash('Preferred genres changed', 'message')
return flask.redirect(flask.url_for('settings'))
def settings():
# change preferred genres
# update balance
# get permissions from db
db.execute('SELECT fave_perm, rank_perm, watched_perm, balance FROM users WHERE user_id = %s', (current_user.get_id()))
results = db.fetchall()[0]
perms = {}
perms['fave'] = results[0]
perms['rank'] = results[1]
perms['watch'] = results[2]
balance = results[3]
# get preferred genres from db
db.execute('SELECT genre_name FROM prefers, genre WHERE prefers.user_id = %s AND prefers.genre_id = genre.genre_id', (current_user.get_id()))
results = db.fetchall()
genres = [genre[0] for genre in results]
genre_string = ', '.join(genres)
return flask.render_template('settings.html', perms = perms, genres = genre_string, balance = balance)
def wall():
events = []
# get all watched events from followers where allowable
db.execute('SELECT watched.user_id, video.title, watched.timestamp, video.video_id FROM watched, video, users WHERE watched.video_id = video.video_id AND watched.user_id IN (SELECT follow.user_id_to FROM follow WHERE follow.user_id_from = %s) AND users.user_id = watched.user_id AND users.watched_perm >= 1', (current_user.get_id()))
results = db.fetchall()
watched = []
for result in results:
d = {}
d['user'] = result[0]
d['title'] = result[1]
d['time'] = result[2]
d['verb'] = 'watched'
d['suffix'] = ''
d['id'] = result[3]
watched.append(d)
events.append(d)
# get all liked events from followers where allowable
db.execute('SELECT likes.user_id, video.title, likes.timestamp, video.video_id FROM likes, video, users WHERE likes.video_id = video.video_id AND likes.user_id IN (SELECT follow.user_id_to FROM follow WHERE follow.user_id_from = %s) AND users.user_id = likes.user_id AND users.fave_perm >= 1', (current_user.get_id()))
results = db.fetchall()
likes = []
for result in results:
d = {}
d['user'] = result[0]
d['title'] = result[1]
d['time'] = result[2]
d['verb'] = 'liked'
d['suffix'] = ''
d['id'] = result[3]
likes.append(d)
events.append(d)
# get all the reated events from followers where allowable
db.execute('SELECT rate.user_id, video.title, rate.rate_time, rate.rating, video.video_id FROM rate, video, users WHERE rate.video_id = video.video_id AND rate.user_id IN (SELECT follow.user_id_to FROM follow WHERE follow.user_id_from = %s) AND users.user_id = rate.user_id AND users.rank_perm >= 1', (current_user.get_id()))
results = db.fetchall()
rates = []
for result in results:
d = {}
d['user'] = result[0]
d['title'] = result[1]
d['time'] = result[2]
d['verb'] = 'rated'
d['suffix'] = 'with ' + str(result[3]) + ' stars'
d['id'] = result[4]
rates.append(d)
events.append(d)
# sort events by time
events = sorted(events, key = lambda k: k['time'], reverse = True)
return flask.render_template('wall.html', events = events)
def register():
user_fields = ['First name*', 'Middle name', 'Last name*', 'UID*', 'Password*', 'Email*', 'Street 1', 'Street 2', 'State', 'Country', 'Zip code', 'Genres']
if flask.request.method == 'GET':
user_fields = ['First name*', 'Middle name', 'Last name*', 'UID*', 'Password*', 'Email*', 'Street 1', 'Street 2', 'State', 'Country', 'Zip code', 'Genres']
return flask.render_template("register.html", user_fields = user_fields)
else:
# verify required fields
required_field_indices = [0, 2, 3, 4, 5]
for rfi in required_field_indices:
if flask.request.form[user_fields[rfi].lower()].strip() == '':
flask.flash('Missing required field: ' + user_fields[rfi][0:-1], 'error')
return flask.render_template("register.html", user_fields = user_fields)
# register the new user
try:
db.execute('INSERT INTO users VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', \
(flask.request.form[user_fields[3].lower()], \
flask.request.form[user_fields[4].lower()], \
flask.request.form[user_fields[0].lower()], \
flask.request.form[user_fields[1].lower()], \
flask.request.form[user_fields[2].lower()], \
flask.request.form[user_fields[5].lower()], \
flask.request.form[user_fields[6].lower()], \
flask.request.form[user_fields[7].lower()], \
flask.request.form[user_fields[8].lower()], \
flask.request.form[user_fields[9].lower()], \
flask.request.form[user_fields[10].lower()], \
'0', '0', '0', '0'))
except Exception as e:
print str(e)
flask.flash('UID already in use')
return flask.render_template("register.html", user_fields = user_fields)
# register new users likes
liked_genres = [g.strip() for g in flask.request.form[user_fields[11].lower()].split(',')]
# generate list of genre names and ids
genre_ids = {}
if len(liked_genres) > 0:
db.execute('SELECT * FROM genre')
genres = db.fetchall()
for genre in genres:
genre_ids[genre[1].lower()] = genre[0]
for lg in liked_genres:
if lg not in genre_ids:
continue
db.execute('INSERT INTO prefers VALUES (%s, %s)', (flask.request.form[user_fields[3].lower()], genre_ids[lg]))
return flask.redirect(flask.url_for('login'))
def cart():
model = ShoppingCart(current_user.get_id())
videos = model.get_items()
if videos:
db.execute("SELECT title,dvd_price FROM video WHERE video_id IN (%s)" %
(",".join(str(i) for i in videos), ))
videos_info = db.fetchall()
else:
videos_info = []
items = []
for video_id, video_info in zip(videos, videos_info):
items.append({
"video_id": video_id,
"title": video_info[0],
"dvd_price": video_info[1]
})
app.logger.debug("Query returned\n%s", repr(items))
if flask.request.method == "POST":
action = flask.request.form.get("action")
app.logger.info("Cart action %s triggered.", action)
if action == "checkout":
db.execute("SELECT balance FROM users WHERE user_id=%s",
(current_user.get_id(), ))
balance = int(db.fetchone()[0])
price_sum = sum(i["dvd_price"] for i in items)
if price_sum > balance:
flask.flash("You don't have enough money in your account.",
category="error")
else:
db.execute(
'UPDATE users SET balance = balance - %s WHERE user_id = %s',
(price_sum, current_user.get_id()))
model.complete_order()
flask.flash("Your order has been placed!", category="message")
return flask.redirect(flask.url_for("cart"))
elif action == "clear":
model.clear_cart()
flask.flash("Cart has been cleared.", category="message")
return flask.redirect(flask.url_for("cart"))
return flask.render_template("cart.html", items=items)
def cart():
model = ShoppingCart(current_user.get_id())
videos = model.get_items()
if videos:
db.execute(
"SELECT title,dvd_price FROM video WHERE video_id IN (%s)" %
(",".join(str(i) for i in videos), )
)
videos_info = db.fetchall()
else:
videos_info = []
items = []
for video_id, video_info in zip(videos, videos_info):
items.append({
"video_id": video_id,
"title": video_info[0],
"dvd_price": video_info[1]
})
app.logger.debug("Query returned\n%s", repr(items))
if flask.request.method == "POST":
action = flask.request.form.get("action")
app.logger.info("Cart action %s triggered.", action)
if action == "checkout":
db.execute("SELECT balance FROM users WHERE user_id=%s", (current_user.get_id(), ))
balance = int(db.fetchone()[0])
price_sum = sum(i["dvd_price"] for i in items)
if price_sum > balance:
flask.flash("You don't have enough money in your account.", category = "error")
else:
db.execute('UPDATE users SET balance = balance - %s WHERE user_id = %s', (price_sum, current_user.get_id()))
model.complete_order()
flask.flash("Your order has been placed!", category = "message")
return flask.redirect(flask.url_for("cart"))
elif action == "clear":
model.clear_cart()
flask.flash("Cart has been cleared.", category = "message")
return flask.redirect(flask.url_for("cart"))
return flask.render_template("cart.html", items = items)
def admin():
# check for super user rights
user_id = current_user.get_id()
db.execute('SELECT COUNT(*) FROM super_user WHERE super_user_id = %s',
(user_id))
if db.fetchall()[0][0] == 0:
flask.flash(
'You do not have the privileges necessary to view this page',
'error')
return flask.render_template('admin.html', error=True)
# get a list of movies
db.execute('SELECT video_id, title FROM video')
videos = db.fetchall()
# get a list of comments
db.execute(
'SELECT comment.comment_id, comment.content, comment.comment_time, users.first_name, users.last_name, video.title FROM comment, users, video WHERE users.user_id = comment.user_id AND video.video_id = comment.video_id'
)
results = db.fetchall()
comments = []
for result in results:
d = {}
d['id'] = result[0]
d['content'] = result[1]
d['time'] = result[2]
d['name'] = result[3] + ' ' + result[4]
d['title'] = result[5]
comments.append(d)
print comments
return flask.render_template('admin.html',
error=False,
videos=videos,
comments=comments)
def add_item(self, video_id):
self._create_order()
db.execute("INSERT INTO orders (order_id, video_id) VALUES (%s, %s)",
(self.order_id, video_id))
def clear_cart(self):
db.execute("DELETE FROM orders WHERE order_id=%s", (self.order_id, ))
def movie_info(movie_id):
if flask.request.method == 'POST' and current_user:
if flask.request.form['hiddinput'] == 'online':
# get current balance
db.execute('SELECT balance FROM users WHERE user_id = %s', (current_user.get_id()))
balance = db.fetchall()[0][0]
# get online price
db.execute('SELECT online_price FROM video WHERE video_id = %s', (movie_id))
price = db.fetchall()[0][0]
if balance < price:
flask.flash('You do not have enough money to watch this movie online', 'error')
return flask.redirect(flask.url_for('movie_info', movie_id = movie_id))
db.execute('INSERT INTO watched (user_id, video_id) VALUES (%s, %s)', (current_user.get_id(), movie_id))
db.execute('UPDATE users, video SET users.balance = (users.balance - video.online_price) WHERE users.user_id = %s AND video.video_id = %s', (current_user.get_id(), movie_id))
elif flask.request.form['hiddinput'] == 'dvd':
cart = ShoppingCart(current_user.get_id())
try:
cart.add_item(movie_id)
except:
flask.flash("Movie already in cart.", category = "error")
elif flask.request.form['hiddinput'] == 'fave_up':
db.execute('INSERT INTO likes (user_id, video_id) VALUES (%s, %s)', (current_user.get_id(), movie_id))
elif flask.request.form['hiddinput'] == 'fave_down':
db.execute('DELETE FROM likes WHERE user_id = %s AND video_id = %s', (current_user.get_id(), movie_id))
elif flask.request.form['hiddinput'] == 'comment':
db.execute('INSERT INTO comment (user_id, video_id, content) VALUES (%s, %s, %s)', (current_user.get_id(), movie_id, flask.request.form['hidden_comment']))
else:
# user entered new rating
new_rating = flask.request.form['hiddinput']
if new_rating != 'None':
db.execute('INSERT INTO rate (user_id, video_id, rating) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE rating = %s', (current_user.get_id(), movie_id, new_rating, new_rating))
# get relevant info from db
db.execute('SELECT * FROM video WHERE video_id=%s', (movie_id))
results = db.fetchall()
# make sure movie exists
if len(results) != 1:
flask.flash('Movie does not exist', category = 'error')
return flask.redirect(flask.url_for('browse'))
video = results[0]
info = {}
info['title'] = video[1]
info['year'] = video[2]
info['online_price'] = video[3]
info['dvd_price'] = video[4]
info['votes'] = video[5]
info['rating'] = video[6]
# get genre information from database
db.execute('SELECT genre_name FROM genre')
genres = [genre[0] for genre in db.fetchall()]
db.execute('SELECT genre_id FROM categorize WHERE video_id = %s', (movie_id))
genre_string = ''
for genre in db.fetchall():
genre_string += genres[genre[0] - 1] + ', '
info['genre'] = genre_string[0:-2]
# get watch count from db
db.execute('SELECT COUNT(*) FROM watched WHERE video_id = %s', (movie_id))
info['watch_count'] = db.fetchall()[0][0]
# get stars from db
db.execute('SELECT first_name, last_name FROM star')
stars = [star[0] + ' ' + star[1] for star in db.fetchall()]
db.execute('SELECT star_id FROM played WHERE video_id = %s', (movie_id))
video_stars = []
for star in db.fetchall():
video_stars.append(stars[star[0] - 1])
info['stars'] = video_stars
# get writers from db
db.execute('SELECT first_name, last_name FROM author')
authors = [author[0] + ' ' + author[1] for author in db.fetchall()]
db.execute('SELECT author_id FROM written WHERE video_id = %s', (movie_id))
author_string = ''
for author in db.fetchall():
author_string += authors[author[0] - 1] + ', '
info['authors'] = author_string[0:-2]
# check if user likes this video already
db.execute('SELECT COUNT(*) FROM likes WHERE user_id = %s AND video_id = %s', (current_user.get_id(), movie_id))
info['likes'] = db.fetchall()[0][0] > 0
# get rating information from db
db.execute('SELECT AVG(rate.rating) FROM rate, users WHERE rate.video_id = %s AND rate.user_id = users.user_id AND users.rank_perm = 2', (movie_id))
info['overall_rating'] = db.fetchall()[0][0]
db.execute('SELECT AVG(rate.rating) FROM rate, follow, users WHERE follow.user_id_from = %s AND rate.user_id = follow.user_id_to AND rate.video_id = %s AND rate.user_id = users.user_id AND users.rank_perm >= 1', (current_user.get_id(), movie_id))
info['follower_rating'] = db.fetchall()[0][0]
db.execute('SELECT rating FROM rate WHERE user_id = %s AND video_id = %s', (current_user.get_id(), movie_id))
results = db.fetchall()
user_rating = results[0][0] if len(results) > 0 else None
if user_rating:
info['user_rating'] = int(user_rating)
else:
info['user_rating'] = 0
# check for TV series info
if video[7]:
info['episode'] = video[7]
db.execute('''SELECT season.season_number, series.title
FROM season, series
WHERE season.season_id = %s AND series.series_id = season.series_id''', (video[8]))
results = db.fetchall()[0]
info['season'] = results[0]
info['series'] = results[1]
# get comments from db
db.execute('SELECT users.first_name, users.last_name, comment.comment_time, comment.content FROM comment, users WHERE video_id = %s AND comment.user_id = users.user_id', (movie_id))
results = db.fetchall()
comments = []
for comment in results:
comments.append({ 'name' : comment[0] + ' ' + comment[1],
'time' : comment[2],
'content' : comment[3] })
return flask.render_template('movie_info.html', movie_id = movie_id, info = info, comments = comments)
def browse():
# get list of all videos
db.execute('SELECT video_id, title FROM video')
videos = db.fetchall()
return flask.render_template('browse.html', videos=videos)
def movie_info(movie_id):
if flask.request.method == 'POST' and current_user:
if flask.request.form['hiddinput'] == 'online':
# get current balance
db.execute('SELECT balance FROM users WHERE user_id = %s',
(current_user.get_id()))
balance = db.fetchall()[0][0]
# get online price
db.execute('SELECT online_price FROM video WHERE video_id = %s',
(movie_id))
price = db.fetchall()[0][0]
if balance < price:
flask.flash(
'You do not have enough money to watch this movie online',
'error')
return flask.redirect(
flask.url_for('movie_info', movie_id=movie_id))
db.execute(
'INSERT INTO watched (user_id, video_id) VALUES (%s, %s)',
(current_user.get_id(), movie_id))
db.execute(
'UPDATE users, video SET users.balance = (users.balance - video.online_price) WHERE users.user_id = %s AND video.video_id = %s',
(current_user.get_id(), movie_id))
elif flask.request.form['hiddinput'] == 'dvd':
cart = ShoppingCart(current_user.get_id())
try:
cart.add_item(movie_id)
except:
flask.flash("Movie already in cart.", category="error")
elif flask.request.form['hiddinput'] == 'fave_up':
db.execute('INSERT INTO likes (user_id, video_id) VALUES (%s, %s)',
(current_user.get_id(), movie_id))
elif flask.request.form['hiddinput'] == 'fave_down':
db.execute(
'DELETE FROM likes WHERE user_id = %s AND video_id = %s',
(current_user.get_id(), movie_id))
elif flask.request.form['hiddinput'] == 'comment':
db.execute(
'INSERT INTO comment (user_id, video_id, content) VALUES (%s, %s, %s)',
(current_user.get_id(), movie_id,
flask.request.form['hidden_comment']))
else:
# user entered new rating
new_rating = flask.request.form['hiddinput']
if new_rating != 'None':
db.execute(
'INSERT INTO rate (user_id, video_id, rating) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE rating = %s',
(current_user.get_id(), movie_id, new_rating, new_rating))
# get relevant info from db
db.execute('SELECT * FROM video WHERE video_id=%s', (movie_id))
results = db.fetchall()
# make sure movie exists
if len(results) != 1:
flask.flash('Movie does not exist', category='error')
return flask.redirect(flask.url_for('browse'))
video = results[0]
info = {}
info['title'] = video[1]
info['year'] = video[2]
info['online_price'] = video[3]
info['dvd_price'] = video[4]
info['votes'] = video[5]
info['rating'] = video[6]
# get genre information from database
db.execute('SELECT genre_name FROM genre')
genres = [genre[0] for genre in db.fetchall()]
db.execute('SELECT genre_id FROM categorize WHERE video_id = %s',
(movie_id))
genre_string = ''
for genre in db.fetchall():
genre_string += genres[genre[0] - 1] + ', '
info['genre'] = genre_string[0:-2]
# get watch count from db
db.execute('SELECT COUNT(*) FROM watched WHERE video_id = %s', (movie_id))
info['watch_count'] = db.fetchall()[0][0]
# get stars from db
db.execute('SELECT first_name, last_name FROM star')
stars = [star[0] + ' ' + star[1] for star in db.fetchall()]
db.execute('SELECT star_id FROM played WHERE video_id = %s', (movie_id))
video_stars = []
for star in db.fetchall():
video_stars.append(stars[star[0] - 1])
info['stars'] = video_stars
# get writers from db
db.execute('SELECT first_name, last_name FROM author')
authors = [author[0] + ' ' + author[1] for author in db.fetchall()]
db.execute('SELECT author_id FROM written WHERE video_id = %s', (movie_id))
author_string = ''
for author in db.fetchall():
author_string += authors[author[0] - 1] + ', '
info['authors'] = author_string[0:-2]
# check if user likes this video already
db.execute(
'SELECT COUNT(*) FROM likes WHERE user_id = %s AND video_id = %s',
(current_user.get_id(), movie_id))
info['likes'] = db.fetchall()[0][0] > 0
# get rating information from db
db.execute(
'SELECT AVG(rate.rating) FROM rate, users WHERE rate.video_id = %s AND rate.user_id = users.user_id AND users.rank_perm = 2',
(movie_id))
info['overall_rating'] = db.fetchall()[0][0]
db.execute(
'SELECT AVG(rate.rating) FROM rate, follow, users WHERE follow.user_id_from = %s AND rate.user_id = follow.user_id_to AND rate.video_id = %s AND rate.user_id = users.user_id AND users.rank_perm >= 1',
(current_user.get_id(), movie_id))
info['follower_rating'] = db.fetchall()[0][0]
db.execute('SELECT rating FROM rate WHERE user_id = %s AND video_id = %s',
(current_user.get_id(), movie_id))
results = db.fetchall()
user_rating = results[0][0] if len(results) > 0 else None
if user_rating:
info['user_rating'] = int(user_rating)
else:
info['user_rating'] = 0
# check for TV series info
if video[7]:
info['episode'] = video[7]
db.execute(
'''SELECT season.season_number, series.title
FROM season, series
WHERE season.season_id = %s AND series.series_id = season.series_id''',
(video[8]))
results = db.fetchall()[0]
info['season'] = results[0]
info['series'] = results[1]
# get comments from db
db.execute(
'SELECT users.first_name, users.last_name, comment.comment_time, comment.content FROM comment, users WHERE video_id = %s AND comment.user_id = users.user_id',
(movie_id))
results = db.fetchall()
comments = []
for comment in results:
comments.append({
'name': comment[0] + ' ' + comment[1],
'time': comment[2],
'content': comment[3]
})
return flask.render_template('movie_info.html',
movie_id=movie_id,
info=info,
comments=comments)
def complete_order(self):
db.execute(
"UPDATE ordersmeta SET completed=NOW() WHERE order_id=%s",
(self.order_id, )
)
def clear_cart(self):
db.execute(
"DELETE FROM orders WHERE order_id=%s", (self.order_id, )
)
def new_movie():
# check for required fields
required_fields = [
'title', 'year', 'online_price', 'dvd_price', 'genre', 'starring',
'writers'
]
for rf in required_fields:
if flask.request.form[rf] == '':
flask.flash('Missing required field: ' + rf, 'error')
return flask.redirect(flask.url_for('admin'))
# parse genres
genres = [
genre.strip() for genre in flask.request.form['genre'].split(',')
]
# get genre ids
db.execute('SELECT genre_name FROM genre')
temp = db.fetchall()
genre_ids = [genre_id[0].lower() for genre_id in temp]
# parse stars
stars = [
star.strip() for star in flask.request.form['starring'].split(',')
]
# get star ids
db.execute('SELECT first_name, last_name FROM star')
temp = db.fetchall()
star_ids = [star_id[0] + ' ' + star_id[1] for star_id in temp]
# parse writers
writers = [
writer.strip() for writer in flask.request.form['writers'].split(',')
]
# get writer ids
db.execute('SELECT first_name, last_name FROM author')
temp = db.fetchall()
writer_ids = [writer_id[0] + ' ' + writer_id[1] for writer_id in temp]
# check if the movie is a tv show
if flask.request.form['episode'] == '' or flask.request.form[
'series'] == '' or flask.request.form['season'] == '':
# add new movie to video table
db.execute(
'INSERT INTO video (title, year, online_price, dvd_price) VALUES (%s, %s, %s, %s)',
(flask.request.form['title'], flask.request.form['year'],
flask.request.form['online_price'],
flask.request.form['dvd_price']))
# get latest video id
db.execute('SELECT MAX(video_id) FROM video')
video_id = db.fetchall()[0][0]
# add each genre to new movie
for genre in genres:
try:
genre_id = genre_ids.index(genre) + 1
db.execute(
'INSERT INTO categorize (video_id, genre_id) VALUES (%s, %s)',
(video_id, genre_id))
except:
pass
# add each star to new movie
for star in stars:
try:
star_id = star_ids.index(star) + 1
db.execute(
'INSERT INTO played (video_id, star_id) VALUES (%s, %s)',
(video_id, star_id))
except:
pass
# add each star to new movie
for writer in writers:
try:
writer_id = writer_ids.index(writer) + 1
db.execute(
'INSERT INTO written (video_id, author_id) VALUES (%s, %s)',
(video_id, writer_id))
except:
pass
flask.flash('New movie added', 'message')
return flask.redirect(flask.url_for('admin'))
else:
episode = flask.request.form['episode']
series = flask.request.form['series']
season = flask.request.form['season']
# verify that series exists and grab the series id
db.execute('SELECT series_id FROM series WHERE title=%s', (series))
results = db.fetchall()
if len(results) == 0:
flask.flash('Series does not exist: ' + series, 'error')
return flask.redirect(flask.url_for('admin'))
series_id = results[0][0]
# add season to db
db.execute(
'INSERT INTO season (series_id, season_number) VALUES (%s, %s)',
(series_id, season))
# extract season id
db.execute(
'SELECT season_id FROM season WHERE series_id = %s AND season_number = %s',
(series_id, season))
season_id = db.fetchall()[0][0]
# add new movie to video table
db.execute(
'INSERT INTO video (title, year, online_price, dvd_price, episode, season_id) VALUES (%s, %s, %s, %s, %s, %s)',
(flask.request.form['title'], flask.request.form['year'],
flask.request.form['online_price'],
flask.request.form['dvd_price'], episode, season_id))
# get latest video id
db.execute('SELECT MAX(video_id) FROM video')
video_id = db.fetchall()[0][0]
# add each genre to new movie
for genre in genres:
try:
genre_id = genre_ids.index(genre) + 1
db.execute(
'INSERT INTO categorize (video_id, genre_id) VALUES (%s, %s)',
(video_id, genre_id))
except:
pass
flask.flash('New show added', 'message')
return flask.redirect(flask.url_for('admin'))
return flask.redirect(flask.url_for('admin'))
def register():
user_fields = [
'First name*', 'Middle name', 'Last name*', 'UID*', 'Password*',
'Email*', 'Street 1', 'Street 2', 'State', 'Country', 'Zip code',
'Genres'
]
if flask.request.method == 'GET':
user_fields = [
'First name*', 'Middle name', 'Last name*', 'UID*', 'Password*',
'Email*', 'Street 1', 'Street 2', 'State', 'Country', 'Zip code',
'Genres'
]
return flask.render_template("register.html", user_fields=user_fields)
else:
# verify required fields
required_field_indices = [0, 2, 3, 4, 5]
for rfi in required_field_indices:
if flask.request.form[user_fields[rfi].lower()].strip() == '':
flask.flash(
'Missing required field: ' + user_fields[rfi][0:-1],
'error')
return flask.render_template("register.html",
user_fields=user_fields)
# register the new user
try:
db.execute('INSERT INTO users VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', \
(flask.request.form[user_fields[3].lower()], \
flask.request.form[user_fields[4].lower()], \
flask.request.form[user_fields[0].lower()], \
flask.request.form[user_fields[1].lower()], \
flask.request.form[user_fields[2].lower()], \
flask.request.form[user_fields[5].lower()], \
flask.request.form[user_fields[6].lower()], \
flask.request.form[user_fields[7].lower()], \
flask.request.form[user_fields[8].lower()], \
flask.request.form[user_fields[9].lower()], \
flask.request.form[user_fields[10].lower()], \
'0', '0', '0', '0'))
except Exception as e:
print str(e)
flask.flash('UID already in use')
return flask.render_template("register.html",
user_fields=user_fields)
# register new users likes
liked_genres = [
g.strip()
for g in flask.request.form[user_fields[11].lower()].split(',')
]
# generate list of genre names and ids
genre_ids = {}
if len(liked_genres) > 0:
db.execute('SELECT * FROM genre')
genres = db.fetchall()
for genre in genres:
genre_ids[genre[1].lower()] = genre[0]
for lg in liked_genres:
if lg not in genre_ids:
continue
db.execute(
'INSERT INTO prefers VALUES (%s, %s)',
(flask.request.form[user_fields[3].lower()], genre_ids[lg]))
return flask.redirect(flask.url_for('login'))
def delete_comment(comment_id):
db.execute('DELETE FROM comment WHERE comment_id = %s', (comment_id))
flask.flash('Comment deleted', 'message')
return flask.redirect(flask.url_for('admin'))
def complete_order(self):
db.execute("UPDATE ordersmeta SET completed=NOW() WHERE order_id=%s",
(self.order_id, ))
def browse():
# get list of all videos
db.execute('SELECT video_id, title FROM video')
videos = db.fetchall()
return flask.render_template('browse.html', videos = videos)
def wall():
events = []
# get all watched events from followers where allowable
db.execute(
'SELECT watched.user_id, video.title, watched.timestamp, video.video_id FROM watched, video, users WHERE watched.video_id = video.video_id AND watched.user_id IN (SELECT follow.user_id_to FROM follow WHERE follow.user_id_from = %s) AND users.user_id = watched.user_id AND users.watched_perm >= 1',
(current_user.get_id()))
results = db.fetchall()
watched = []
for result in results:
d = {}
d['user'] = result[0]
d['title'] = result[1]
d['time'] = result[2]
d['verb'] = 'watched'
d['suffix'] = ''
d['id'] = result[3]
watched.append(d)
events.append(d)
# get all liked events from followers where allowable
db.execute(
'SELECT likes.user_id, video.title, likes.timestamp, video.video_id FROM likes, video, users WHERE likes.video_id = video.video_id AND likes.user_id IN (SELECT follow.user_id_to FROM follow WHERE follow.user_id_from = %s) AND users.user_id = likes.user_id AND users.fave_perm >= 1',
(current_user.get_id()))
results = db.fetchall()
likes = []
for result in results:
d = {}
d['user'] = result[0]
d['title'] = result[1]
d['time'] = result[2]
d['verb'] = 'liked'
d['suffix'] = ''
d['id'] = result[3]
likes.append(d)
events.append(d)
# get all the reated events from followers where allowable
db.execute(
'SELECT rate.user_id, video.title, rate.rate_time, rate.rating, video.video_id FROM rate, video, users WHERE rate.video_id = video.video_id AND rate.user_id IN (SELECT follow.user_id_to FROM follow WHERE follow.user_id_from = %s) AND users.user_id = rate.user_id AND users.rank_perm >= 1',
(current_user.get_id()))
results = db.fetchall()
rates = []
for result in results:
d = {}
d['user'] = result[0]
d['title'] = result[1]
d['time'] = result[2]
d['verb'] = 'rated'
d['suffix'] = 'with ' + str(result[3]) + ' stars'
d['id'] = result[4]
rates.append(d)
events.append(d)
# sort events by time
events = sorted(events, key=lambda k: k['time'], reverse=True)
return flask.render_template('wall.html', events=events)