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