def send(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
try:
db = sql.getdb()
cursor = db.cursor()
user_id_receiver = request.form.get('user_id_receiver')
message = request.form.get('message')
##log.debug(request.form)
#cursor.execute("insert into message(user_id_sender, user_id_receiver, message, sent) values(" + user_id_sender + "," + user_id_receiver + "," + "\"" + message + "\"" + ", current_timestamp)" )
cursor.execute("insert into message(user_id_sender, user_id_receiver, message, sent) values(%s,%s,%s,current_timestamp)",(request.user_id, user_id_receiver, message))
db.commit()
#cursor.execute("select gcm_id from user where user_id = %s", (user_id_receiver))
cursor.execute("select firstname, surname from user where user_id = %s", (request.user_id))
row = cursor.fetchone()
firstname = row[0]
surname = row[1]
cursor.execute("select gcm_id from user where user_id = %s", (user_id_receiver))
row = cursor.fetchone()
receiver = row[0]
db.close()
send_gcm(firstname, surname, receiver, message, request.user_id)
return Response(message, mimetype='text/plain', status=200)
except Exception as e:
##log.exception("Send exception: ")
return Response('{test:test}',status=400)
def conversation(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
cursor = sql.getCursor()
user_id_receiver = request.args.get('user_id_receiver')
rowarrayc = []
cursor.execute("select * from message where user_id_sender IN(%s, %s) and user_id_receiver IN(%s, %s) order by sent ASC", (request.user_id, user_id_receiver, request.user_id, user_id_receiver))
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['user_id_sender'] = row[0]
c['user_id_receiver'] = row[1]
c['message'] = row[2]
c['sent'] = str(row[3])
c['read'] = str(row[4])
rowarrayc.append(c)
if rowarrayc:
j = json.dumps(rowarrayc, ensure_ascii=False)
return Response(j, mimetype='text/plain')
return Response('{test:"test"}', mimetype='text/plain')
def allusrs(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
cursor = sql.getCursor()
rowarray = []
cursor.execute("SELECT user.user_id, user.study_id, firstname, surname, AsText(latlon), car, starting_year, institution_name, campus_name, department_name, name_of_study, facebook_id FROM user INNER JOIN study ON user.study_id = study.study_id INNER JOIN department ON study.department_id = department.department_id INNER JOIN campus ON study.campus_id = campus.campus_id INNER JOIN institution ON department.institution_id = institution.institution_id LEFT JOIN facebook_user ON user.user_id = facebook_user.user_id")
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['user_id'] = row[0]
c['study_id'] = row[1]
c['firstname'] = row[2]
c['surname'] = row[3]
c['latlon'] = row[4]
c['car'] = row[5]
c['starting_year'] = row[6]
c['institution_name'] = row[7]
c['campus_name'] = row[8]
c['department_name'] = row[9]
c['name_of_study'] = row[10]
c['facebook_id'] = row[11]
rowarray.append(c)
if rowarray:
j = json.dumps(rowarray, ensure_ascii=False)
return Response(j, mimetype='text/plain')
def deleteuser(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
print "Deleting user"
uid = request.args.get('uid')
print uid
print request.user_id
if uid and int(uid) == request.user_id:
print "Starting delete"
db = sql.getdb()
try:
cursor = db.cursor()
cursor.execute("delete from message where user_id_sender = %s or user_id_receiver = %s", (request.user_id, request.user_id))
cursor.execute("delete from facebook_user where user_id = %s", (request.user_id))
cursor.execute("delete from email_user where user_id = %s", (request.user_id))
cursor.execute("delete from user where user_id = %s", (request.user_id))
cursor.execute("delete from session where user_id = %s", (request.user_id))
db.commit()
except:
print "Delete failed"
db.rollback()
def emailUser(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
cursor = sql.getCursor()
email = request.args.get('email')
rowarray = []
#cursor.execute("SELECT user_id, user.study_id, firstname, surname, AsText(latlon) as latlon, institution.institution_name, campus.campus_name, department.department_name, name_of_study, starting_year, car FROM user INNER JOIN study ON user.study_id = study.study_id INNER JOIN campus ON study.campus_id = campus.campus_id ""INNER JOIN department ON study.department_id = department.department_id INNER JOIN institution ON department.institution_id = institution.institution_id WHERE user_id = (SELECT user_id FROM email_user WHERE email=%s)",(email))
cursor.execute("SELECT user_id, user.study_id, firstname, surname, AsText(latlon) as latlon, institution.institution_name, campus.campus_name, department.department_name, name_of_study, starting_year, car, gcm_id, gcm_version FROM user INNER JOIN study ON user.study_id = study.study_id INNER JOIN campus ON study.campus_id = campus.campus_id ""INNER JOIN department ON study.department_id = department.department_id INNER JOIN institution ON department.institution_id = institution.institution_id WHERE user_id = (SELECT user_id FROM session WHERE user_id=%s LIMIT 1)",(request.user_id))
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['user_id'] = row[0]
c['study_id'] = row[1]
c['firstname'] = row[2]
c['surname'] = row[3]
c['latlon'] = row[4]
c['institution_name'] = row[5]
c['campus_name'] = row[6]
c['department_name'] = row[7]
c['name_of_study'] = row[8]
c['starting_year'] = row[9]
c['car'] = row[10]
c['gcm_id'] = row[11]
c['gcm_version'] = row[12]
rowarray.append(c)
if rowarray:
j = json.dumps(rowarray, ensure_ascii=False)
return Response(j, mimetype='text/plain')
return Response('{test:"test"}', mimetype='text/plain')
def read(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
db = sql.getdb()
cursor = db.cursor()
user_id_sender = request.form.get('user_id_sender')
#cursor.execute("update message set `read`=current_timestamp where user_id_sender=" + user_id_sender + " and user_id_receiver=" + user_id_receiver + " and `read` is NULL")
cursor.execute("update message set `read`=current_timestamp where user_id_sender=%s and user_id_receiver=%s and `read` is NULL", (user_id_sender, request.user_id))
cursor.close()
db.commit()
db.close()
return Response('{test:"test"}')
def reggcm(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
gcmId = request.form.get('gcmId')
gcmVersion = request.form.get('gcmVersion')
if not (gcmId and gcmVersion):
return
db = sql.getdb()
cursor = db.cursor()
cursor.execute("update user set gcm_id = %s, gcm_version = %s where user_id = %s", (gcmId,gcmVersion, request.user_id))
db.commit()
def newMessages(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
rowarrayi = []
#cursor.execute("select * from message where user_id_receiver = " + user_id_receiver + " AND `read` is NULL;")
cursor.execute("select * from message where user_id_receiver = %s AND `read` is NULL;", (request.user_id))
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['user_id_sender'] = row[0]
c['user_id_receiver'] = row[1]
c['message'] = row[2]
c['sent'] = str(row[3])
rowarrayi.append(c)
if rowarrayi:
j= json.dumps(rowarrayi, ensure_ascii=False)
return Response(j, mimetype='text/plain')
return Response('{test:"test"}', mimetype='text/plain')
def inbox(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
cursor = sql.getCursor()
rowarrayi = []
#cursor.execute("SELECT * FROM message inner join (select * from message WHERE `user_id_receiver` = %s order by sent desc) a on (message.user_id_sender=a.user_id_sender) group by message.user_id_sender order by a.sent desc", (request.user_id))
cursor.execute("SELECT m.user_id_sender, m.message, m.sent FROM (select user_id_sender, max(sent) msent from message WHERE `user_id_receiver` = %s group by user_id_sender) a inner join message m on (m.user_id_sender=a.user_id_sender and m.sent = a.msent) order by m.sent desc", (request.user_id))
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['user_id_sender'] = row[0]
c['message'] = row[1]
c['sent'] = str(row[2])
rowarrayi.append(c)
if rowarrayi:
j = json.dumps(rowarrayi, ensure_ascii=False)
return Response(j, mimetype='text/plain')
return Response('{test:"test"}', mimetype='text/plain')
def updateAddress(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
##log.debug(request.form)
lat = request.form.get('lat')
lon = request.form.get('lon')
if lat and lon:
try:
db = sql.getdb()
cursor = db.cursor()
cursor.execute("UPDATE user set latlon = POINT(%s,%s) where user_id = %s", (lat,lon,request.user_id))
db.commit()
except Exception as e:
##log.exception("Exception:")
db.rollback()
return Response('{test:test}',status=400)
return Response('{test:test}',status=200)
return Response('{test:test}',status=400)
def usr(request):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
cursor = sql.getCursor()
rowarray = []
cursor.execute("SELECT user_id, study_id, firstname, surname, AsText(latlon), car, starting_year FROM user")
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['user_id'] = row[0]
c['study_id'] = row[1]
c['firstname'] = str(row[2])
c['surname'] = str(row[3])
c['latlon'] = str(row[4])
c['car'] = row[5]
c['starting_year'] = row[6]
rowarray.append(c)
if rowarray:
j = json.dumps(rowarray, ensure_ascii=False)
return Response(j,mimetype='text/plain')
return Response('{test:"test"}')
def usr(request, **values):
if not validate_login.is_logged_in(request):
return validate_login.failed_login()
cursor = sql.getCursor()
fname = request.args.get('fname')
lat = request.args.get('lat')
lon = request.args.get('lon')
rowarray = []
cursor.execute("SELECT user_id FROM user where firstname=%s and latlon=point(%s,%s)",(fname,lat,lon))
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['user_id'] = row[0]
rowarray.append(c)
if rowarray:
j = json.dumps(rowarray, ensure_ascii=False)
return Response(j, mimetype='text/plain')
return Response('{test:"test"}', mimetype='text/plain')
