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