def getConv():
convid = request.args.get('id', None)
mess = request.args.get('mess', None)
db = get_db()
if mess is not None:
send(convid, mess)
return redirect(url_for('pm.getConv', id=convid))
conversation = db.execute(
'SELECT id, userid, body, created FROM messages WHERE convid = ?',
(convid, )).fetchall()
members = db.execute('SELECT user1, user2 FROM conv WHERE id = ?',
(convid, )).fetchone()
memnames = []
name = db.execute('SELECT fname, lname FROM user WHERE id = ?',
(members[0], )).fetchone()
memnames.append(name[0] + ' ' + name[1])
name = db.execute('SELECT fname, lname FROM user WHERE id = ?',
(members[1], )).fetchone()
memnames.append(name[0] + ' ' + name[1])
return render_template('pm/message.html',
conv=conversation,
members=members,
membername=memnames,
active='messages',
convid=convid)
def getConvs():
db = get_db()
all_relevant_ids = db.execute(
'SELECT id, user1, user2 FROM conv WHERE user1 = ? or user2 = ? ORDER BY id',
(g.user['id'], g.user['id'])).fetchall()
convid = []
uidlist = []
for row in all_relevant_ids:
convid.append(row[0])
if row[1] is not g.user['id']:
uidlist.append(row[1])
else:
uidlist.append(row[2])
namelist = []
for uid in uidlist:
namelist.append(
db.execute('SELECT fname, lname FROM user'
' WHERE id = ?', (uid, )).fetchone())
return render_template('pm/messages.html',
convid=convid,
namelist=namelist,
active='messages')
def create():
"""Create a new post for the current user."""
if request.method == 'POST':
title = request.form['title']
group = request.form['groups']
body = request.form['body']
error = None
if not title:
error = 'Title is required.'
if error is not None:
flash(error)
else:
db = get_db()
gid = db.execute('SELECT id FROM groups WHERE gname = ?',
(group, )).fetchone()
db.execute(
'INSERT INTO post (title, body, author_id, gid)'
' VALUES (?, ?, ?, ?)', (title, body, g.user['id'], gid[0]))
db.commit()
pid = db.execute(
'SELECT id FROM post ORDER BY id DESC LIMIT 1').fetchone()
sub_user(g.user['id'], pid[0])
new_notif(title, g.user['id'], pid[0], gid[0], 'post')
return redirect(url_for('blog.index'))
glist = (request.args.get('g', None), )
if glist[0] == None:
glist = get_subscribed_groups(format(int(g.user['id']), '05'))
return render_template('blog/create.html', glist=glist, active='feed')
def change_pass():
error = None
if request.method == 'POST':
old = request.form['old']
new = request.form['new']
new2 = request.form['new2']
if not check_password_hash(g.user['password'], old):
error = "Incorrect Password."
elif new != new2:
error = "Passwords do not match."
elif not (any(x.isupper() for x in new) and any(x.islower()
for x in new)
and any(x.isdigit() for x in new) and len(new) >= 7):
error = "Password does not meet specified criteria."
if error == None:
flash("Password Successfully Updated.")
db = get_db()
db.execute('UPDATE user SET (password) = ? WHERE id = ?',
(generate_password_hash(new), g.user['id']))
db.commit()
return redirect(url_for('profile.profile', id=g.user['id']))
flash(error)
return render_template('profile/pass.html')
def login():
"""Log in a registered user by adding the user id to the session."""
username = request.args.get('username', "")
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
user = db.execute('SELECT * FROM user WHERE username = ?',
(username, )).fetchone()
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect password.'
if error is None:
# store the user id in a new session and return to the index
session.clear()
session['user_id'] = format(int(user['id']), '05')
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html',
active="login",
username=username)
def like():
db = get_db()
redir = request.args['r']
lid = request.args['id']
gid = request.args['gid']
gname = request.args.get('gname', None)
pid = request.args.get('pid', None)
ltype = request.args['type']
if ltype == "post":
db.execute('INSERT INTO likes (userid, pid) VALUES (?,?)',
(g.user['id'], lid))
author = db.execute('SELECT author_id FROM post WHERE id = ?',
(lid, )).fetchone()[0]
if author is not g.user['id']:
new_notif(None, g.user['id'], lid, gid, "like")
elif ltype == "comment":
db.execute('INSERT INTO likes (userid, cid) VALUES (?,?)',
(g.user['id'], lid))
db.commit()
if redir == '1':
return redirect(url_for('blog.index'))
elif redir == '2':
return redirect(url_for('groups.load_post', p=pid, gname=gname))
elif redir == '3':
return redirect(url_for('groups.load_group', gname=gname))
def send(convid, body):
db = get_db()
db.execute('INSERT INTO messages (userid, convid, body) VALUES (?,?,?)',
(g.user['id'], convid, body))
db.execute('UPDATE conv SET edited = CURRENT_TIMESTAMP WHERE id = ?',
(convid, ))
db.commit()
def create_group(gname):
admin = request.args.get('a', "")
db = get_db()
if db.execute('SELECT id FROM groups WHERE gname = ?',
(gname, )).fetchone() is None:
db.execute('INSERT INTO groups (gname, administrators) VALUES (?, ?)',
(gname, admin))
db.commit()
def delAll():
mark_all_read()
db = get_db()
notifs = db.execute('SELECT id FROM notifications WHERE userid = ?',
(g.user['id'], )).fetchall()
for row in notifs:
delete(row[0])
return redirect(url_for('notif.index'))
def delNotif():
db = get_db()
nid = request.args.get('id')
notif = db.execute('SELECT read FROM notifications WHERE id = ?',
(nid, )).fetchone()[0]
if notif == 0:
read(nid)
delete(nid)
return redirect(url_for('notif.index'))
def mark_all_read():
db = get_db()
notifs = db.execute('SELECT read, id FROM notifications WHERE userid = ?',
(g.user['id'], )).fetchall()
for row in notifs:
if row[0] == 0:
read(row[1])
return redirect(url_for('notif.index'))
def mark_unread():
nid = request.args.get('id', None)
db = get_db()
db.execute('UPDATE user SET notifs = (?) WHERE id = ?',
(g.user['notifs'] + 1, g.user['id']))
db.execute('UPDATE notifications SET read = (?) WHERE id = ?', (0, nid))
db.commit()
return redirect(url_for('notif.index'))
def get_subscribed_group_ids(uid):
group_list = []
db = get_db()
garray = db.execute(
'SELECT * FROM groups WHERE members IS NOT NULL ORDER BY gname'
).fetchall()
for row in garray:
if uid in row[2]:
group_list.append(row[0])
return group_list
def movingRead():
nid = request.args['n']
pid = request.args['p']
gname = request.args['gname']
db = get_db()
if db.execute('SELECT read FROM notifications WHERE id = ?',
(nid, )).fetchone()[0] == 0:
read(nid)
return redirect(url_for('groups.load_post', p=pid, gname=gname))
def read(nid):
db = get_db()
notifs = db.execute('SELECT notifs FROM user WHERE id = ?',
(g.user['id'], )).fetchone()[0]
db.execute('UPDATE user SET notifs = (?) WHERE id = ?',
(notifs - 1, g.user['id']))
db.execute('UPDATE notifications SET read = (?) WHERE id = ?', (1, nid))
db.commit()
def get_likes(pid, cid, t):
db = get_db()
idlikes = []
if t == "post":
likes_list = db.execute('SELECT userid FROM likes WHERE pid = ?',
(pid, )).fetchall()
for row in likes_list:
idlikes.append(row[0])
return idlikes
def create():
if request.method == 'POST':
name = request.form['name'].title()
univ = request.form['colleges']
gname = univ + '-' + name
db = get_db()
error = None
if not name:
error = "Group name is required."
elif not univ:
error = 'School name is required.'
elif db.execute('SELECT id FROM colleges WHERE cname = ?',
(univ, )).fetchone() is None:
error = 'School name \'{0}\' is invalid. Select a school from the drop-down list.'.format(
univ)
elif db.execute('SELECT id FROM groups WHERE gname = ?',
(gname, )).fetchone() is not None:
error = 'Group {0} is already registered.'.format(gname)
if error is None:
uid = session.get('user_id')
create_group(gname)
subscribe_user(gname, uid)
db.commit()
return redirect(url_for('groups.load_group', gname=gname))
college_list = []
db = get_db()
clist = db.execute('SELECT cname FROM colleges').fetchall()
for row in clist:
college_list.append(row[0])
return render_template('groups/create.html',
college_list=college_list,
active="groups")
def get_subscribed_users(pid):
member_list = []
id_list = []
members = []
db = get_db()
marray = db.execute('SELECT subbed_users FROM post WHERE id = ?',
(pid, )).fetchone()[0]
id_list = []
if marray is not None:
members = marray.split(',')
for user_id in members:
id_list.append(user_id)
return id_list
def index():
"""Show all the posts, most recent first."""
userid = session.get('user_id')
db = get_db()
notifs = db.execute(
'SELECT read, pid, gid, created, body, id FROM notifications'
' WHERE userid = ?'
' ORDER BY created DESC', (userid, )).fetchall()
glist = []
for row in notifs:
glist.append(get_groupname(row[2]))
return render_template('notifications/notifications.html',
notifications=notifs,
glist=glist,
active='notif')
def unsubscribe_user(gname, uid):
found = 0
members = []
newmem = []
db = get_db()
mem = db.execute('SELECT members FROM groups WHERE gname = ?',
(gname, )).fetchone()
if mem[0] is not None:
members = mem[0].split(',')
for user_id in members:
if uid != user_id:
newmem.append(user_id)
newstring = ','.join(newmem)
db.execute('UPDATE groups SET members = (?) WHERE gname = ?',
(newstring, gname))
db.commit()
def load_logged_in_user():
"""If a user id is stored in the session, load the user object from
the database into ``g.user``."""
user_id = session.get('user_id')
g.memberlist = []
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM user WHERE id = ?',
(user_id, )).fetchone()
g.grouplist = get_subscribed_groups(format(int(user_id), '05'))
try:
g.memberlist = get_subscribed_members(g.grouplist[0])
except:
print()
def deleteAccount():
db = get_db()
delAll()
glist = get_subscribed_groups(g.user['id'])
for group in glist:
unsubscribe_user(group, g.user['id'])
postids = db.execute('SELECT id FROM post WHERE author_id = ?',
(g.user['id'], )).fetchall()
for row in postids:
db.execute('DELETE FROM notifications WHERE pid = ?', (row[0], ))
db.execute('DELETE FROM post WHERE id = ?', (row[0], ))
db.execute('DELETE FROM user WHERE id = ?', (g.user['id'], ))
db.commit()
session.clear()
return redirect(url_for('auth.login'))
def get_subscribed_members(gname):
member_list = []
id_list = []
members = []
db = get_db()
marray = db.execute('SELECT members FROM groups WHERE gname = ?',
(gname, )).fetchone()
if marray[0] is not None:
members = marray[0].split(',')
for user_id in members:
member = db.execute('SELECT fname, lname FROM user WHERE id = ?',
(user_id, )).fetchone()
if member is not None:
name = member[0] + ' ' + member[1]
member_list.append(name)
id_list.append(user_id)
return member_list, id_list
def subscribe_user(gname, uid):
found = 0
members = []
db = get_db()
mem = db.execute('SELECT members FROM groups WHERE gname = ?',
(gname, )).fetchone()
if mem[0] is not None:
members = mem[0].split(',')
for user_id in members:
if uid == user_id:
found = 1
break
if not (found):
members.append(str(uid))
newstring = ','.join(members)
db.execute('UPDATE groups SET members = (?) WHERE gname = ?',
(newstring, gname))
db.commit()
def sub_user(uid, pid):
db = get_db()
found = 0
members = []
ulist = db.execute('SELECT subbed_users FROM post WHERE id = ?',
(pid, )).fetchone()[0]
if ulist is not None:
members = ulist.split(',')
for user_id in members:
if int(uid) == int(user_id):
found = 1
break
if not (found):
members.append(str(uid))
newstring = ','.join(members)
db.execute('UPDATE post SET subbed_users = (?) WHERE id = ?',
(newstring, pid))
db.commit()
def get_admins(gname):
usern_a = []
ids_a = []
db = get_db()
aarray = db.execute('SELECT administrators FROM groups WHERE gname = ?',
(gname, )).fetchone()
if aarray is not None:
try:
admins = aarray[0].split(',')
except:
admins = []
for user_id in admins:
admin = db.execute('SELECT username FROM user WHERE id = ?',
(user_id, )).fetchone()
if admin is not None:
usern_a.append(admin)
ids_a.append(int(user_id))
return ids_a, usern_a
def update(id):
"""Update a post if the current user is the author."""
post = get_post(id)
if request.method == 'POST':
title = request.form['title']
body = request.form['body']
error = None
if not title:
error = 'Title is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute('UPDATE post SET title = ?, body = ? WHERE id = ?',
(title, body, id))
db.commit()
return redirect(url_for('blog.index'))
return render_template('blog/update.html', post=post, active='feed')
def delete(id):
"""Delete a post.
Ensures that the post exists and that the logged in user is the
author of the post.
"""
get_post(id)
db = get_db()
ulist = db.execute('SELECT userid FROM notifications WHERE pid = ?',
(id, )).fetchall()
for row in ulist:
count = db.execute('SELECT notifs FROM user WHERE id = ?',
(row[0], )).fetchone()[0]
db.execute('UPDATE user SET notifs = ? WHERE id = ?',
(count - 1, row[0]))
db.execute('DELETE FROM notifications WHERE pid = ?', (id, ))
db.execute('DELETE FROM post WHERE id = ?', (id, ))
db.commit()
return redirect(url_for('blog.index'))
def unlike():
db = get_db()
redir = request.args['r']
gname = request.args.get('gname', None)
pid = request.args.get('pid', None)
pixels = request.args.get('pixels', 0)
lid = request.args['id']
ltype = request.args['type']
if ltype == "post":
db.execute('DELETE FROM likes WHERE (userid, pid) = (?, ?)',
(g.user['id'], lid))
elif ltype == "comment":
db.execute('DELETE FROM likes WHERE (userid, cid) = (?, ?)',
(g.user['id'], lid))
db.commit()
if redir == '1':
return redirect(url_for('blog.index', pixels=pixels))
elif redir == '2':
return redirect(url_for('groups.load_post', p=pid, gname=gname))
elif redir == '3':
return redirect(url_for('groups.load_group', gname=gname))
def remove_admin(gname):
db = get_db()
admins = []
aarray = db.execute('SELECT administrators FROM groups WHERE gname = ?',
(gname, )).fetchone()[0]
if aarray is not None:
try:
admins = aarray.split(',')
except:
admins = []
if str(g.user['id']) in admins:
admins.remove(str(g.user['id']))
admins = ','.join(admins)
db.execute('UPDATE groups SET administrators = ? WHERE gname = ?',
(admins, gname))
db.commit()
return redirect(url_for('groups.load_group', gname=gname))
