def albums_edit_route():
titles = []
if 'username' in session:
if check_session() == False:
options = {"edit": False, "login": True}
return render_template("user.html",
options=options,
prev_url='/j89pws9vn291/pa4/albums/edit')
else:
username = session['username']
cur = mysql.connection.cursor()
try:
cur.execute(
"SELECT albumid, title, access FROM Album WHERE username=%s",
(username, ))
titles = cur.fetchall()
except:
print "Get title query is wrong!"
finally:
cur.close()
options = {"edit": True, "sensitive": True}
session['lastactivity'] = datetime.now()
return render_template("albums.html",
options=options,
titles=titles,
username=username)
else:
return abort(404)
def album_route():
album_id = request.args.get('id', None)
if album_id is not None:
picids = []
album_owner = []
cur = mysql.connection.cursor()
cur.execute(
"SELECT title, access, username FROM Album WHERE albumid=%s",
(album_id, ))
album_info = cur.fetchone()
if 'username' in session and check_session():
username = session['username']
if album_info[1] == 'private':
rows_count = cur.execute(
"SELECT username FROM AlbumAccess WHERE albumid=%s AND username=%s",
(album_id, username))
if rows_count > 0 or username == album_info[2]:
options = {
"edit": False,
"private": True,
"sensitive": True
}
else:
options = {"edit": False, "login": True, "refill": True}
print "access denied!"
return render_template("user.html", options=options)
else:
options = {"edit": False, "private": False, "sensitive": True}
else:
if album_info[1] == 'private':
options = {"edit": False, "login": True, "refill": True}
print "access denied!"
return render_template("user.html",
options=options,
prev_url='/j89pws9vn291/pa4/album?id=' +
album_id)
else:
options = {"edit": False, "private": False, "sensitive": False}
cur.execute(
"SELECT Photo.picid, Photo.url, Photo.date, Contain.caption FROM Photo,Contain WHERE Photo.picid=Contain.picid and albumid=%s ORDER BY Contain.sequencenum",
(album_id, ))
picids = cur.fetchall()
cur.close()
session['lastactivity'] = datetime.now()
return render_template("album.html",
options=options,
picids=picids,
albumid=album_id,
album_info=album_info)
else:
return abort(404)
def main_route():
if 'username' in session and check_session() is True:
user = session['username']
options = {"logged_in": True}
session['lastactivity'] = datetime.now()
titles = []
cur = mysql.connection.cursor()
try:
cur.execute(
"SELECT albumid, title, access FROM Album WHERE access='public' OR username=%s OR albumid IN (SELECT albumid FROM AlbumAccess WHERE username=%s)",
(user, user))
titles = cur.fetchall()
except:
print "Get info of all public albums query is wrong!"
finally:
cur.close()
return render_template("index.html",
options=options,
user=user,
titles=titles)
else:
options = {"logged_in": False}
return render_template("index.html", options=options)
def user_route():
if request.method == 'POST':
user_name = request.form.get('user_name', None)
first_name = request.form.get('first_name', None)
last_name = request.form.get('last_name', None)
email = request.form.get('email', None)
password = request.form.get('password', None)
confirmPassword = request.form.get('confirmPassword', None)
if password == confirmPassword:
if check_username(user_name) is False:
cur = mysql.connection.cursor()
try:
cur.execute(
"INSERT INTO User VALUES (%s, %s, %s, %s, %s)",
(user_name, first_name, last_name, password, email))
mysql.connection.commit()
except:
print "Insert new user query is wrong!"
finally:
cur.close()
send_email(email, first_name, user_name)
options = {"edit": False, "login": True, "refill": False}
else:
options = {"edit": False, "login": False, "refill": True}
else:
options = {"edit": False, "login": False, "refill": True}
return render_template("user.html", options=options)
else:
if 'username' in session and check_session() is True:
session['lastactivity'] = datetime.now()
return redirect('/j89pws9vn291/pa4/user/edit')
else:
options = {"edit": False, "login": False, "refill": False}
return render_template("user.html", options=options)
def pic_route():
pic_id = None
username = None
prev_id = []
next_id = []
id = None
pic_id = request.args.get('id', None)
if pic_id is not None:
next_url = None
options = {"prev": True, "next": True}
sensitive = False
cur = mysql.connection.cursor()
try:
cur.execute("SELECT url FROM Photo WHERE picid=%s", (pic_id, ))
pic_url = cur.fetchone()
cur.execute(
"SELECT sequencenum, albumid, caption FROM Contain WHERE picid=%s",
(pic_id, ))
seq_tuple = cur.fetchone()
cur.execute("SELECT username, access FROM Album WHERE albumid=%s",
(seq_tuple[1], ))
album_info = cur.fetchone()
if 'username' in session and check_session():
username = session['username']
if album_info[1] == 'private':
rows_count = cur.execute(
"SELECT username FROM AlbumAccess WHERE albumid=%s AND username=%s",
(seq_tuple[1], username))
if username != album_info[0] and rows_count == 0:
options = {
"edit": False,
"login": True,
"refill": True
}
print "Access denied!"
return render_template("user.html", options=options)
sensitive = True
else:
if album_info[1] == 'private':
cur.close()
options = {"edit": False, "login": True, "refill": True}
print "Access denied!"
return render_template(
"user.html",
options=options,
prev_url='/j89pws9vn291/pa4/pic?id=' + pic_id)
id = seq_tuple[1]
cur.execute(
"SELECT MAX(sequencenum) FROM Contain WHERE albumid=%s",
(seq_tuple[1], ))
max_seq = cur.fetchone()
seq_num = int(seq_tuple[0])
prev_seq = seq_num - 1
next_seq = seq_num + 1
if seq_num == 1:
prev_seq = None
if seq_num >= max_seq[0]:
next_seq = None
if prev_seq == None and next_seq == None:
# print "pass"
options = {"prev": False, "next": False}
elif prev_seq is None:
cur.execute(
"SELECT picid FROM Contain WHERE albumid=%s and sequencenum=%s",
(seq_tuple[1], next_seq))
next_id = cur.fetchone()
options = {"prev": False, "next": True}
elif next_seq == None:
cur.execute(
"SELECT picid FROM Contain WHERE albumid=%s and sequencenum=%s",
(seq_tuple[1], prev_seq))
prev_id = cur.fetchone()
options = {"prev": True, "next": False}
else:
cur.execute(
"SELECT picid FROM Contain WHERE albumid=%s and sequencenum=%s",
(seq_tuple[1], next_seq))
next_id = cur.fetchone()
cur.execute(
"SELECT picid FROM Contain WHERE albumid=%s and sequencenum=%s",
(seq_tuple[1], prev_seq))
prev_id = cur.fetchone()
except:
print "Some query inside is wrong!"
finally:
cur.close()
result = get_pic_favorite_info(pic_id)
latest_favorites = result[0]
num_favorites = result[1]
session['lastactivity'] = datetime.now()
return render_template("pic.html",
pic_url=pic_url,
prev_id=prev_id,
next_id=next_id,
options=options,
id=id,
user_name=username,
caption=seq_tuple[2],
pic_id=pic_id,
latest_favorites=latest_favorites,
num_favorites=num_favorites,
sensitive=sensitive)
else:
return abort(404)
def user_edit_route():
if 'username' in session:
if check_session() is False:
options = {"edit": False, "login": True, "refill": True}
return render_template("user.html",
options=options,
prev_url='/j89pws9vn291/pa4/user/edit')
else:
username = session['username']
session['lastactivity'] = datetime.now()
if request.method == 'POST':
first_name = request.form.get('first_name', None)
last_name = request.form.get('last_name', None)
email = request.form.get('email', None)
password = request.form.get('password', None)
confirmPassword = request.form.get('confirmPassword', None)
if password == confirmPassword:
cur = mysql.connection.cursor()
try:
cur.execute(
"UPDATE User SET firstname=%s, lastname=%s, password=%s, email=%s WHERE username=%s",
(first_name, last_name, password, email, username))
mysql.connection.commit()
except:
print "Update existing user query is wrong!"
finally:
cur.close()
else:
cur = mysql.connection.cursor()
try:
cur.execute(
"SELECT firstname, lastname, email FROM User WHERE username=%s",
(username, ))
name_and_email = cur.fetchone()
options = {
"edit": True,
"login": False,
"refill": False
}
return render_template("user.html",
options=options,
name_and_email=name_and_email)
except:
print "Insert new user query is wrong!"
finally:
cur.close()
else:
cur = mysql.connection.cursor()
try:
cur.execute(
"SELECT firstname, lastname, email FROM User WHERE username=%s",
(username, ))
name_and_email = cur.fetchone()
options = {"edit": True, "login": False, "refill": False}
return render_template("user.html",
options=options,
name_and_email=name_and_email)
except:
print "Insert new user is wrong!"
finally:
cur.close()
options = {"edit": False, "login": True, "refill": False}
return render_template("user.html", options=options)
else:
options = {"edit": False, "login": True, "refill": False}
return render_template("user.html",
options=options,
prev_url='/j89pws9vn291/pa4/user/edit')
def albums_edit_route_post():
if 'username' in session and check_session():
op = request.form.get('op', None)
if op is not None:
titles = []
if op == "delete":
albumid = request.form.get('albumid')
cur = mysql.connection.cursor()
try:
#delete the pics related to this album
cur.execute("SELECT picid FROM Contain WHERE albumid=%s",
(albumid, ))
picid_need_remove = cur.fetchall()
for picid_remove in picid_need_remove:
cur.execute("SELECT url FROM Photo WHERE picid=%s",
(picid_remove[0], ))
url_remove = cur.fetchone()
os.remove(UPLOAD_FOLDER + url_remove[0])
cur.execute("SELECT username FROM Album WHERE albumid=%s",
(albumid, ))
username = cur.fetchone()
cur.execute("DELETE FROM Album WHERE albumid=%s",
(albumid, ))
mysql.connection.commit()
cur.execute(
"SELECT albumid, title, access FROM Album WHERE username=%s",
(username[0], ))
titles = cur.fetchall()
except:
print "Some query inside is wrong!"
finally:
cur.close()
if op == "add":
username = request.form.get('username')
title = request.form.get('title')
access = request.form.get('access')
cur = mysql.connection.cursor()
try:
cur.execute(
"INSERT INTO Album (title, created, lastupdated, username, access) VALUES (%s, NOW(), NOW(), %s, %s)",
(title, username, access))
mysql.connection.commit()
cur.execute(
"SELECT albumid, title, access FROM Album WHERE username=%s",
(username, ))
titles = cur.fetchall()
except:
print "Insert new album record is wrong! \n Get info of the album query is wrong!"
finally:
cur.close()
options = {"edit": True, "sensitive": True}
session['lastactivity'] = datetime.now()
return render_template("albums.html",
options=options,
username=username,
titles=titles)
else:
return abort(404)
else:
options = {"edit": False, "login": True}
return render_template("user.html",
options=options,
prev_url='/j89pws9vn291/pa4/albums/edit')
def album_edit_route_post():
op = request.form.get('op', None)
if op is not None:
albumid = request.form.get('albumid')
picids = []
users_with_access = []
cur = mysql.connection.cursor()
try:
cur.execute(
"SELECT title, access, username FROM Album WHERE albumid=%s",
(albumid, ))
album_info = cur.fetchone()
except:
print "Get info of an album query is wrong!"
finally:
cur.close()
if op == "add":
_file = request.files['filepath']
if _file and allowed_file(_file.filename):
filename = _file.filename.rsplit('.', 1)[0]
format = _file.filename.rsplit('.', 1)[1]
# new_filename = _file.filename.rsplit('.', 1)[0]
# filename.update(new_filename.encode('utf-8'))
picid = filename
url = '/pictures/' + picid + '.' + format
_file.save(UPLOAD_FOLDER + url)
cur = mysql.connection.cursor()
try:
cur.execute(
"SELECT sequencenum FROM Contain WHERE albumid=%s",
(albumid, ))
test_seq = cur.fetchall()
if len(test_seq) == 0:
new_seq = 1
else:
cur.execute(
"SELECT MAX(sequencenum) FROM Contain WHERE albumid=%s",
(albumid, ))
maxsequence = cur.fetchone()
new_seq = maxsequence[0] + 1
cur.execute(
"INSERT INTO Contain (albumid, picid, sequencenum) VALUES (%s, %s, %s)",
(albumid, picid, new_seq))
cur.execute(
"INSERT INTO Photo (picid, url, format, date) VALUES (%s, %s, %s, NOW())",
(picid, url, format))
mysql.connection.commit()
except:
print "Some query inside is wrong!"
finally:
cur.close()
if op == "delete":
picid = request.form.get('picid')
url = ""
cur = mysql.connection.cursor()
try:
cur.execute("SELECT url FROM Photo WHERE picid=%s", (picid, ))
url = cur.fetchone()
#update seqnum
cur.execute(
"SELECT sequencenum FROM Contain WHERE albumid=%s AND picid=%s",
(albumid, picid))
del_seq = cur.fetchone()
cur.execute(
"SELECT picid,sequencenum FROM Contain WHERE sequencenum>%s",
(del_seq[0], ))
pic_need_to_decre = cur.fetchall()
if len(pic_need_to_decre) == 0:
print "this is the last pic"
else:
for pic_decre in pic_need_to_decre:
cur.execute(
"UPDATE Contain SET sequencenum=%s WHERE picid=%s AND albumid=%s",
(pic_decre[1] - 1, pic_decre[0], albumid))
mysql.connection.commit()
#update seqnum
cur.execute(
"DELETE FROM Contain WHERE albumid=%s AND picid=%s",
(albumid, picid))
mysql.connection.commit()
except:
print "Update sequence num query is wrong!"
finally:
cur.close()
os.remove(UPLOAD_FOLDER + url[0])
if op == "title_edit":
new_title = request.form.get('new_title')
cur = mysql.connection.cursor()
try:
cur.execute("UPDATE Album SET title=%s WHERE albumid=%s",
(new_title, albumid))
mysql.connection.commit()
except:
print "Update album title query is wrong!"
finally:
cur.close()
if op == "access_edit":
cur = mysql.connection.cursor()
try:
if album_info[1] == 'public':
cur.execute(
"UPDATE Album SET access='private' WHERE albumid=%s",
(albumid, ))
else:
cur.execute(
"UPDATE Album SET access='public' WHERE albumid=%s",
(albumid, ))
cur.execute("DELETE FROM AlbumAccess WHERE albumid=%s",
(albumid, ))
mysql.connection.commit()
except:
print "Update access of an album query is wrong!"
finally:
cur.close()
if op == "add_new_user_with_access":
new_user_with_access = request.form.get('new_user_with_access')
cur = mysql.connection.cursor()
try:
rows_count = cur.execute(
"SELECT username FROM User WHERE username=%s",
(new_user_with_access, ))
if rows_count > 0:
cur.execute(
"INSERT INTO AlbumAccess(albumid, username) VALUES (%s, %s)",
(albumid, new_user_with_access))
mysql.connection.commit()
except:
print "Add new access of a user query is wrong!"
finally:
cur.close()
if op == "revoke":
revoked_user = request.form.get('username')
cur = mysql.connection.cursor()
try:
cur.execute(
"DELETE FROM AlbumAccess WHERE albumid=%s AND username=%s",
(albumid, revoked_user))
mysql.connection.commit()
except:
print "Delete access of a user query is wrong!"
finally:
cur.close()
cur = mysql.connection.cursor()
cur.execute(
"SELECT title, access, username FROM Album WHERE Album.albumid=%s",
(albumid, ))
album_info = cur.fetchone()
cur.execute(
"SELECT Photo.picid, Photo.url, Photo.date, Contain.caption FROM Photo, Contain WHERE Photo.picid=Contain.picid and albumid=%s ORDER BY Contain.sequencenum",
(albumid, ))
picids = cur.fetchall()
cur.execute("UPDATE Album SET lastupdated=NOW() WHERE albumid=%s",
(albumid, ))
mysql.connection.commit()
if 'username' in session and check_session():
username = session['username']
if username == album_info[2]:
if album_info[1] == "private":
cur.execute(
"SELECT username FROM AlbumAccess WHERE albumid=%s",
(albumid, ))
users_with_access = cur.fetchall()
options = {
"edit": True,
"private": True,
"sensitive": True
}
elif album_info[1] == "public":
options = {
"edit": True,
"private": False,
"sensitive": True
}
else:
options = {"edit": False, "login": True, "refill": True}
print "Access denied!"
return render_template("user.html", options=options)
else:
options = {"edit": False, "login": True, "refill": True}
print "Access denied!"
return render_template("user.html",
options=options,
prev_url='/j89pws9vn291/pa4/album?id=' +
albumid)
cur.close()
session['lastactivity'] = datetime.now()
return render_template("album.html",
options=options,
albumid=albumid,
picids=picids,
album_info=album_info,
users_with_access=users_with_access)
else:
return abort(404)
def album_edit_route():
album_edit_id = request.args.get('id', None)
if album_edit_id is not None:
picids = []
users_with_access = []
cur = mysql.connection.cursor()
try:
cur.execute(
"SELECT title, access, username FROM Album WHERE albumid=%s",
(album_edit_id, ))
album_info = cur.fetchone()
if 'username' in session and check_session():
username = session['username']
if username == album_info[2]:
if album_info[1] == "private":
options = {
"edit": True,
"private": True,
"sensitive": True
}
elif album_info[1] == "public":
options = {
"edit": True,
"private": False,
"sensitive": True
}
else:
options = {"edit": False, "login": True, "refill": True}
print "Access denied!"
return render_template(
"user.html",
options=options,
prev_url='/j89pws9vn291/pa4/album/edit?id=' +
album_edit_id)
else:
options = {"edit": False, "login": True, "refill": True}
print "Access denied!"
return render_template(
"user.html",
options=options,
prev_url='/j89pws9vn291/pa4/album/edit?id=' +
album_edit_id)
except:
print "Get info of an album query is wrong!"
finally:
cur.close()
cur = mysql.connection.cursor()
try:
cur.execute("SELECT username FROM AlbumAccess WHERE albumid=%s",
(album_edit_id, ))
users_with_access = cur.fetchall()
cur.execute(
"SELECT Photo.picid, Photo.url, Photo.date, Contain.caption FROM Photo, Contain WHERE Photo.picid=Contain.picid AND albumid=%s ORDER BY Contain.sequencenum",
(album_edit_id, ))
picids = cur.fetchall()
except:
print "Get info of a user access query or get info of a photo query is wrong!"
finally:
cur.close()
return render_template("album.html",
options=options,
picids=picids,
albumid=album_edit_id,
album_info=album_info,
users_with_access=users_with_access)
else:
return abort(404)