def recover_password(): email = request.form['email'] exists = sess.query(Users).filter_by(email=email).count() if exists == 0: return jsonify("Can't find that email address") elif exists > 1: return jsonify("Something terrible has happened") else: userid = sess.query(Users).filter_by(email=email).one().userid now = datetime.datetime.utcnow() m = hashlib.sha256() for i in [str(userid), str(now), email]: m.update(i.encode("utf-8")) token = m.hexdigest() newrequest = PasswordReset(userid=userid, token=token, date=now, used='f') sess.add(newrequest) sess.commit() resetlink = "https://cartoforum.com/resetpassword?token={}".format( token) msg = Message('Hello', sender='Cartoforum', recipients=[email]) msg.body = resetlink mail.send(msg) return render_template('index.html', status='resetlinksent')
def user_posts(): vtotal = voted = None userid = request.args.get('userid', 0, type=str) posts = [] for p, t, u in sess.query(Post, Thread, Users).filter_by(userid=userid).filter_by(groupid=session['groupid']).\ join(Thread).join(Users): qry = sess.query(sqlalchemy.sql.func.sum(Votes.vote)).filter_by(postid=p.postid) for res in qry.all(): vtotal = res for v in sess.query(Votes).filter_by(postid=p.postid).filter_by(userid=session['userid']): voted = v.vote posts.append([p.postid, p.userid, p.date, p.objectid, p.postcontent, t.nickname, u.username, vtotal[0] or 0, voted or 0]) return jsonify(posts=posts)
def search_posts(): userid = session['userid'] if 'userid' in session else 0 posts = [] qstr = request.args.get('q', 0, type=str) voted = vtotal = None for p, t, u in sess.query(Post, Thread, Users).order_by(Post.date).filter( Post.postcontent.like("%{}%".format(qstr))).join(Thread).filter_by(groupid=session['groupid']).join(Users): qry = sess.query(sqlalchemy.sql.func.sum(Votes.vote)).filter_by(postid=p.postid) for res in qry.all(): vtotal = res for v in sess.query(Votes).filter_by(postid=p.postid).filter_by(userid=userid): voted = v.vote posts.append([p.postid, p.userid, p.date, p.objectid, p.postcontent, t.nickname, u.username, vtotal[0], voted]) return jsonify(posts=posts)
def cast_vote(): post = request.args.get('post', 0, type=int) vote = request.args.get('vote', 0, type=int) v = sess.query(Votes).filter_by(userid=session['userid']).filter_by(postid=post) if v.count() > 0: v = sess.query(Votes).filter_by(userid=session['userid']).filter_by(postid=post).first() v.vote = vote else: v = Votes(postid=post, userid=session['userid'], vote=vote) sess.add(v) sess.commit() pid = sess.query(Votes).filter_by(userid=session['userid']).filter_by(postid=post).one().postid oid = sess.query(Post).filter_by(postid=pid).one().objectid score_ind = carto.update_object_stats(oid) return jsonify(score_ind)
def recent_posts(): userid = session['userid'] if 'userid' in session else 0 posts = [] for p, t, u in sess.query(Post, Thread, Users).order_by(Post.date.desc()).join(Thread).\ filter_by(groupid=session['groupid']).join(Users): voted = vtotal = None qry = sess.query(sqlalchemy.sql.func.sum(Votes.vote)).filter_by(postid=p.postid) for res in qry.all(): vtotal = res for v in sess.query(Votes).filter_by(postid=p.postid).filter_by(userid=userid): voted = v.vote posts.append([p.postid, p.userid, p.date, p.objectid, p.postcontent, t.nickname, u.username, vtotal[0] or 0, voted or 0]) return jsonify(posts=posts)
def index(): if not session.get('logged_in'): return render_template('index.html') else: username = sess.query(Users).filter_by( userid=session['userid']).one().username return render_template('groupselect.html', username=username)
def update_color_pref(): colormap = {'green': 0, 'red': 1, 'purple': 2, 'blue': 3, 'orange': 4} color = request.args.get('color', type=str) v = sess.query(Users).filter_by(userid=session['userid']).first() v.color = colormap[color] sess.commit() return jsonify('color pref updated')
def get_thread_posts(): userid = session['userid'] if 'userid' in session else 0 threads = [] threadid = request.args.get('threadid', 0, type=str) for t in sess.query(Thread).filter_by(threadid=threadid): threads.append({"threadid": t.threadid, "name": t.nickname, "retired": t.retired, "posts": []}) for p, th, u in sess.query(Post, Thread, Users).filter_by(threadid=threadid).join(Thread).join(Users): voted = vtotal = None qry = sess.query(sqlalchemy.sql.func.sum(Votes.vote)).filter_by(postid=p.postid) for res in qry.all(): vtotal = res for v in sess.query(Votes).filter_by(postid=p.postid).filter_by(userid=userid): voted = v.vote threads[len(threads)-1]["posts"].append([p.postid, p.userid, p.date, p.objectid, p.postcontent, t.nickname, u.username, vtotal[0] or 0, voted or 0]) return jsonify(threads=threads)
def check_username(): requestedname = request.args.get('name') taken = sess.query(Users).filter_by(username=requestedname).count() if taken > 0: return jsonify({requestedname: 'taken'}) else: return jsonify({requestedname: 'ok'})
def get_user_invites(): invreq = {'invites': [], 'requests': []} for gr, g, u in sess.query(GroupRequests, Group, Users).filter_by(invitee=session['userid']).\ filter_by(complete='f').join(Group).join(Users): invreq['requests'].append({ "requestid": gr.requestid, "requester": u.username, "group": g.groupname, "date": gr.dateissued }) cur.execute( "SELECT inviteme.requestid, users.username, groups.groupname, inviteme.date " "FROM inviteme INNER JOIN users ON users.userid = inviteme.userid " "JOIN groups ON groups.groupid = inviteme.groupid " "WHERE accepted is null AND groups.userid = '{}'".format( session['userid'])) response = cur.fetchall() for row in response: invreq['invites'].append({ "requestid": row[0], "requester": row[1], "group": [2], "date": row[3] }) pgconnect.commit() return jsonify(invites=invreq)
def get_alternate_username(username): while True: for i in range(1000000): alt_name = u"{}{}".format(username, i) userquery = sess.query(Users).filter_by(username='******'.format(alt_name)).count() if userquery == 0: return alt_name
def save_thread(): nick = request.args.get('nick', 0, type=str) name = request.args.get('name', 0, type=str) ug = sess.query(UsersGroups).filter_by(userid=session['userid']).filter_by(groupid=session['groupid']).one().userid if not ug: return jsonify("user not permitted to do this") t_exists = sess.query(Thread).filter_by(nickname=nick).filter_by(groupid=session['groupid']).count() if t_exists == 1: return jsonify("group already exists") try: insert_thread = Thread(nickname=nick, groupid=session['groupid']) sess.add(insert_thread) sess.commit() return jsonify("success") except: return jsonify("something went wrong")
def go_to_admin(): session['groupid'] = groupid = request.form['groupid'] uid = sess.query(Group).filter_by(groupid=groupid).filter_by( userid=session['userid']).count() if uid == 1: return render_template("admin.html", groupid=groupid) else: return index()
def delete_post(): postid = request.args.get('postid', 0, type=int) is_this_my_post = sess.query(Post).filter_by(userid=session['userid']).filter_by(postid=postid).one() if is_this_my_post.userid != session['userid']: return jsonify('request not allowed') objectid = is_this_my_post.objectid canIdelete = sess.query(Post).filter_by(responseto=postid).count() if canIdelete > 0: return jsonify('request not allowed') sess.query(Post).filter_by(userid=session['userid']).filter_by(postid=postid).delete() sess.commit() # check if you can delete the geometry usingobject = cur.execute("SELECT count(*) from posts where objectid = {}".format(objectid)) response = cur.fetchall() for row in response: if row[0] == 0: cur.execute("DELETE from mapobjects where objectid = {}".format(objectid)) pgconnect.commit() return jsonify('success')
def oauth_callback(): provider = 'google' oauth = OAuthSignIn.get_provider(provider) username, email = oauth.callback() if email is None: # I need a valid email address for my user identification flash('Authentication failed.') return redirect(url_for('index')) # Look if the user already exists nickname = username if username is None or username == "": nickname = email.split('@')[0] googleuser = sess.query(TwitterUsers).filter_by(username=nickname).filter_by(oauth_provider='google')\ .count() # log in oauth database if googleuser == 0: gu = TwitterUsers(oauth_provider='google', username=nickname, oauth_uid=nickname) sess.add(gu) sess.commit() else: gu = sess.query(TwitterUsers).filter_by(username=nickname).filter_by(oauth_provider='google').first() sess.commit() # log in users table userquery = sess.query(Users).filter_by(username='******'.format(nickname)).count() if userquery == 0: newuser = Users(username='******'.format(nickname), password='******') sess.add(newuser) sess.commit() tulogged = sess.query(Users).filter_by(username='******'.format(nickname)).one() session['userid'] = tulogged.userid session['logged_in'] = True # check if there's a session groupid, if so they probably came from a viewmap. # add them to the group and send them there if it's open if session['groupid']: opengroup = sess.query(Group).filter_by(groupid=session['groupid']).one().opengroup if opengroup: adduser_group = UsersGroups(userid=session['userid'], groupid=session['groupid']) sess.add(adduser_group) sess.commit return redirect(url_for('go_to_group')) return render_template('groupselect.html', username=nickname)
def do_login(): for u in sess.query(Users).filter_by(username=request.form['username']): m = hashlib.sha256() m.update(request.form['password'].encode("utf-8")) hashpwd = m.hexdigest() if u.password.strip() == hashpwd or u.password.strip( ) == hashpwd[0:59]: session['logged_in'] = True session['userid'] = u.userid else: return render_template('index.html', login='******') return app.index()
def get_replies(postid,clickedid,indent): indent += 10 vtotal = voted = None for p3, t3, u3 in sess.query(Post, Thread, Users).filter_by(responseto=postid).order_by(asc(Post.date))\ .join(Thread).join( Users): responseto = sess.query(Post).filter_by(responseto=p3.postid).count() deleteable = False qry = sess.query(sqlalchemy.sql.func.sum(Votes.vote)).filter_by(postid=p3.postid) for res in qry.all(): vtotal = res for v in sess.query(Votes).filter_by(postid=p3.postid).filter_by(userid=userid): voted = v.vote if p3.userid == userid and responseto == 0: deleteable = True if p3.postid: thread_data[i]['posts'].append( [p3.postid, p3.userid, p3.date, p3.objectid, p3.postcontent, t3.nickname, u3.username, vtotal[0], voted, p3.postid in clicked_post, deleteable, indent]) if responseto>0: get_replies(p3.postid, clickedid, indent)
def posts_by_extent(): posts = [] extent = request.args.get('ext', 0, type=str) extent = re.sub(' ', ',', extent) for geometrytype in ['POINT', 'LINE', 'POLYGON']: cur.execute("SELECT posts.postid, posts.userid, posts.date, posts.objectid, posts.postcontent, thread.nickname," "users.username FROM posts INNER JOIN thread on thread.threadid = posts.threadid INNER JOIN " "mapobjects on posts.objectid = mapobjects.objectid INNER JOIN users on users.userid = posts.userid" " WHERE posts.groupid = {} and ST_Within(mapobjects.geom,ST_MakeEnvelope({}, 3857)) " "AND ST_AsText(geom) like '{}%' Order by date DESC;". format(session['groupid'], extent, geometrytype)) response = cur.fetchall() for row in response: voted = vtotal = None qry = sess.query(sqlalchemy.sql.func.sum(Votes.vote)).filter_by(postid=row[0]) for res in qry.all(): vtotal = res for v in sess.query(Votes).filter_by(postid=row[0]).filter_by(userid=session['userid']): voted = v.vote posts.append([row[0], row[1], row[2], row[3], row[4], row[5], row[6], vtotal[0], voted]) return jsonify(posts=posts)
def delete_group(): cur.execute("Delete from mapobjects where groupid = {}".format( session['groupid'])) pgconnect.commit() sess.query(Post).filter_by(groupid=session['groupid']).delete() sess.query(Thread).filter_by(groupid=session['groupid']).delete() sess.query(Group).filter_by(groupid=session['groupid']).delete() username = sess.query(Users).filter_by( userid=session['userid']).one().username return render_template('groupselect.html', username=username)
def select_username_for_twitter(): alt_name = request.form['username'] userquery = sess.query(Users).filter_by( username='******'.format(alt_name)).count() if userquery == 0: # move on with their alt twitterid = sess.query(TwitterUsers).filter_by( username=session['twitter_user']).one().oauth_uid newuser = Users(username='******'.format(alt_name), password='******', twitterid=twitterid) sess.add(newuser) sess.commit() tulogged = sess.query(Users).filter_by( username='******'.format(alt_name)).one() session['userid'] = tulogged.userid session['logged_in'] = True return render_template('groupselect.html', username=alt_name) else: # offer them a different name alt_name = utils.get_alternate_username(session['twitter_user']) return render_template('select_username.html', alt_name=alt_name)
def save_post(): threadid = request.form['threadid'] replyID = request.form['replyID'] objid = request.form['objid'] if not objid: objid = 0 text = request.form['text'] ug = sess.query(UsersGroups).filter_by(userid=session['userid']).filter_by(groupid=session['groupid']).one().userid if not ug: return jsonify("user not permitted to do this") if replyID: thread_id = sess.query(Post).filter_by(postid=replyID).one().threadid insert_post = Post(userid=session['userid'], groupid=session['groupid'], date=datetime.datetime.utcnow(), responseto=replyID, objectid=objid, postcontent=text, threadid=thread_id) else: insert_post = Post(userid=session['userid'], groupid=session['groupid'], date=datetime.datetime.utcnow(), objectid=objid, postcontent=text, threadid=threadid) sess.add(insert_post) sess.commit() return jsonify("success")
def add_geojson(): json = urlparse.unquote(request.form['geojson']) group_admin = sess.query(Group).filter_by( groupid=session['groupid']).one().userid if session['userid'] != group_admin: return jsonify("not allowed") cur.execute( "INSERT INTO mapobjects (geom, groupid, userid, date) VALUES " "(ST_Transform(ST_GeomFromText('{}',4326),3857), {}, {}, '{}');". format(json, session['groupid'], session['userid'], datetime.datetime.utcnow())) pgconnect.commit() return jsonify('success')
def invite_user(): invitee = request.args.get('invitee', type=str) try: inviteeuserid = sess.query(Users).filter_by( username=invitee).one().userid except: return jsonify(response="user doesn't exist") inviteexists = sess.query(GroupRequests).filter_by(invitee=inviteeuserid).\ filter_by(groupid=session['groupid']).count() if inviteexists > 0: return jsonify(response='invite already exists') useringroup = sess.query(UsersGroups).filter_by( groupid=session['groupid']).filter_by(userid=inviteeuserid).count() if useringroup > 0: return jsonify(response='user already in group') newinvite = GroupRequests(requester=session['userid'], invitee=inviteeuserid, groupid=session['groupid'], dateissued=datetime.datetime.utcnow(), complete='f') sess.add(newinvite) sess.commit() return jsonify(response='invite sent')
def quit_group(): groupid = request.form['groupid'] uid = sess.query(UsersGroups).filter_by(groupid=groupid).filter_by( userid=session['userid']).count() if uid > 0: cur.execute( "DELETE FROM Votes where userid = {} and postid in (Select postid from posts where groupid = {})" .format(session['userid'], groupid)) sess.query(Post).filter_by(groupid=groupid).filter_by( userid=session['userid']).delete() cur.execute( "DELETE FROM mapobjects Where userid = {} and groupid = {}".format( session['userid'], groupid)) pgconnect.commit() sess.query(UsersGroups).filter_by(groupid=groupid).filter_by( userid=session['userid']).delete() sess.query(GroupRequests).filter_by(groupid=groupid).filter_by( invitee=session['userid']).delete() sess.query(InviteMe).filter_by(groupid=groupid).filter_by( userid=session['userid']).delete() sess.commit() username = sess.query(Users).filter_by( userid=session['userid']).one().username return render_template('groupselect.html', username=username)
def create_account(): username = request.form['username'] email = request.form['email'] if email in ['email address', '']: email = None password = request.form['password'] m = hashlib.sha256() m.update(password.encode("utf-8")) hashpass = m.hexdigest() emailexists = sess.query(Users).filter_by(email=email).count() if emailexists > 0 and email is not None: return render_template(email) else: newuser = Users(email=email, password=hashpass, username=username) sess.add(newuser) sess.commit() return render_template('index.html', account='created')
def get_user_groups(): groups = [] for g, u in sess.query( Group, UsersGroups).join(UsersGroups).filter_by(userid=session['userid']): if g.userid == session['userid']: groups.append({ "name": g.groupname, "groupid": g.groupid, "admin": "true" }) else: groups.append({ "name": g.groupname, "groupid": g.groupid, "admin": "false" }) return jsonify(groups=groups)
def update_basemap_pref(): basemap = request.args.get('basemap', 0, type=int) v = sess.query(Users).filter_by(userid=session['userid']).first() v.basemap = basemap sess.commit() return jsonify('basemap pref updated')
def group_select(): user = sess.query(Users).filter_by(userid=session['userid']).one() username = user.username return render_template('groupselect.html', username=username)
def reset_password(): token = request.args.get('token') userid = sess.query(PasswordReset).filter_by(token=token).filter_by( used='f').one().userid if userid > 0: return render_template('passwordreset.html', userid=userid)
def select_username(): username = request.form['username'] u = sess.query(Users).filter_by(userid=session['userid']).first() u.username = username sess.commit() return app.index()