예제 #1
0
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'))
예제 #2
0
    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)
        )
예제 #3
0
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'))
예제 #4
0
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'))
예제 #5
0
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)
예제 #6
0
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'))
예제 #7
0
    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()]
예제 #8
0
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'))
예제 #9
0
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'))
예제 #10
0
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'))
예제 #11
0
    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)
예제 #12
0
    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)
예제 #13
0
    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, ))
예제 #14
0
    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()]
예제 #15
0
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'))
예제 #16
0
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'))
예제 #17
0
    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, )
        )
예제 #18
0
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'))
예제 #19
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]
예제 #20
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]
예제 #21
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'))
예제 #22
0
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'))
예제 #23
0
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'))
예제 #24
0
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)
예제 #25
0
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)
예제 #26
0
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'))
예제 #27
0
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)
예제 #28
0
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)
예제 #29
0
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)
예제 #30
0
    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))
예제 #31
0
 def clear_cart(self):
     db.execute("DELETE FROM orders WHERE order_id=%s", (self.order_id, ))
예제 #32
0
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)
예제 #33
0
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)
예제 #34
0
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)
예제 #35
0
 def complete_order(self):
     db.execute(
         "UPDATE ordersmeta SET completed=NOW() WHERE order_id=%s",
         (self.order_id, )
     )
예제 #36
0
 def clear_cart(self):
     db.execute(
         "DELETE FROM orders WHERE order_id=%s", (self.order_id, )
     )
예제 #37
0
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'))
예제 #38
0
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'))
예제 #39
0
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'))
예제 #40
0
 def complete_order(self):
     db.execute("UPDATE ordersmeta SET completed=NOW() WHERE order_id=%s",
                (self.order_id, ))
예제 #41
0
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)
예제 #42
0
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)