def create_group():
groupname = request.args.get('groupname')
bounds = request.args.get('bounds')
bounds_arr = request.args.get('bounds').split(" ")
opengroup = 'false'
if request.args.get('opengroup') == 'on':
opengroup = 'true'
cur.execute(
"SELECT count(*) from groups where groupname = '{}' and userid = {} and bounds = '{}'"
.format(groupname, session['userid'], bounds))
response = cur.fetchall()
for row in response:
if row[0] > 0:
return jsonify("group already exists")
cur.execute(
"INSERT INTO groups (geom, groupname, userid, bounds,opengroup) "
"VALUES (ST_Centroid(ST_GeomFromText('MULTIPOINT ({} {},{} {})')), '{}', {}, '{}', {})"
.format(bounds_arr[0], bounds_arr[1], bounds_arr[2], bounds_arr[3],
groupname, session['userid'], bounds, opengroup))
cur.execute(
"SELECT groupid FROM groups WHERE groupname = '{}'".format(groupname))
response = cur.fetchall()
for row in response:
groupid = row[0]
cur.execute("INSERT INTO usersgroups VALUES ({},{})".format(
session['userid'], groupid))
pgconnect.commit()
return jsonify(groupid=groupid)
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_policy_csv(regionid, policystatusid, branchid='ignore'):
if branchid == 'ignore':
cur.execute("select healthtime, machineclass, machinefunction, count(ipaddr) from region_info_{0} "
"where policystatus={1} group by healthtime,machineclass,machinefunction order by healthtime asc".format(regionid,policystatusid))
else:
cur.execute("select healthtime, machineclass, machinefunction, count(ipaddr) from region_info_{0} "
"where policystatus={1} and facility='{2}' group by healthtime,machineclass,machinefunction order by healthtime asc".format(regionid,policystatusid, branchid))
rows = cur.fetchall()
output = cStringIO.StringIO()
writer = csv.writer(output, delimiter=',')
writer.writerow(("healthtime","machineclass","machinefunction","ipaddrcount"))
for row in rows:
writer.writerow(row)
return output.getvalue()
def manage_request():
requestid = request.form['requestid']
action = request.form['submit']
cur.execute(
"SELECT groupid,invitee FROM grouprequests WHERE requestid = {};".
format(requestid))
response = cur.fetchall()
for row in response:
if action == 'accept':
# make sure it doesn't add twice
cur.execute("INSERT INTO usersgroups VALUES ({},{})".format(
row[1], row[0]))
cur.execute(
"UPDATE grouprequests set complete = 't' WHERE requestid = {}".
format(requestid))
pgconnect.commit()
return render_template('groupselect.html')
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 get_policy_csv(regionid, policystatusid, branchid='ignore'):
if branchid == 'ignore':
cur.execute(
"select healthtime, machineclass, machinefunction, count(ipaddr) from region_info_{0} "
"where policystatus={1} group by healthtime,machineclass,machinefunction order by healthtime asc"
.format(regionid, policystatusid))
else:
cur.execute(
"select healthtime, machineclass, machinefunction, count(ipaddr) from region_info_{0} "
"where policystatus={1} and facility='{2}' group by healthtime,machineclass,machinefunction order by healthtime asc"
.format(regionid, policystatusid, branchid))
rows = cur.fetchall()
output = cStringIO.StringIO()
writer = csv.writer(output, delimiter=',')
writer.writerow(
("healthtime", "machineclass", "machinefunction", "ipaddrcount"))
for row in rows:
writer.writerow(row)
return output.getvalue()
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 oauth_authorized(resp):
next_url = request.args.get('next') or url_for('index')
if resp is None:
flash(u'You denied the request to sign in.')
return redirect(next_url)
session['twitter_token'] = (resp['oauth_token'],
resp['oauth_token_secret'])
username = resp['screen_name']
session['twitter_user'] = username
if session.has_key('groupid'):
open = sess.query(Group).filter_by(groupid=session['groupid'])
# check if twitter user has already logged in to cartoforum
twitteruser = sess.query(TwitterUsers).filter_by(username=username).count()
flash('You were signed in as %s' % username)
if twitteruser == 0:
tu = TwitterUsers(oauth_provider='twitter',
username=username,
oauth_uid=resp['user_id'],
oauth_token=resp['oauth_token'],
oauth_secret=resp['oauth_token_secret'])
sess.add(tu)
sess.commit()
else:
tu = sess.query(TwitterUsers).filter_by(username=username).first()
tu.oauth_token = resp['oauth_token']
tu.oauth_secret = resp['oauth_token_secret']
sess.commit()
# check if the twitter users screen name has already been taken.
userquery = sess.query(Users).filter_by(
username='******'.format(username)).count()
if userquery == 0:
# move on with their twitter screen name
newuser = Users(username='******'.format(username),
password='******',
twitterid=resp['user_id'])
sess.add(newuser)
sess.commit()
else:
# this username exists, is the twitterid different from what we have logged?
twitterid = sess.query(TwitterUsers).filter_by(
username='******'.format(username)).count()
if twitterid == 0:
# offer them a different name
alt_name = utils.get_alternate_username(session['twitter_user'])
if not session['groupid']:
return render_template('select_username.html',
alt_name=alt_name)
else:
username = sess.query(Users).filter_by(
twitterid=resp['user_id']).one().username
tulogged = sess.query(Users).filter_by(
username='******'.format(username)).one()
session['userid'] = tulogged.userid
session['logged_in'] = True
if session.has_key('groupid'):
cur.execute("INSERT INTO usersgroups VALUES ({},{})".format(
session['userid'], session['groupid']))
cur.execute(
"SELECT groupname,bounds from groups where groupid = {}".format(
session['groupid']))
pgconnect.commit()
response = cur.fetchall()
for row in response:
groupname = row[0]
bounds = row[1]
# Check for group membership, return group name and bounds and username
user = sess.query(Users).filter_by(userid=session['userid']).one()
username = user.username
basemap = user.basemap
color = user.color
# TODO: check that user is a member of group
return render_template('map.html',
groupid=session['groupid'],
userid=session['userid'],
username=username,
basemap=basemap,
color=color,
groupname=groupname,
bounds=bounds)
else:
return render_template('groupselect.html', username=username)