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