def session_valid(self,sid):
cursor = sql.getCursor()
cursor.execute("select user_id, created from session where session_id = %s and datediff(now(),created)<5 ",(sid))
rows = cursor.fetchone()
if rows != None:
return True
return False
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 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 get_userid_from_face(self, face):
cursor = sql.getCursor()
cursor.execute("select user_id from facebook_user where facebook_id = %s", (face))
row = cursor.fetchone()
if row:
return row[0]
else:
return None
def test():
v='sh600216'
# print str(count)+'/'+str(total),'start',v
# try:
df=oneStock(v,starttime=datetime(2016,1,1),endtime=datetime(2016,8,30))
[conn,cur]=getCursor()
pdsql.to_sql(df, v, conn, flavor='mysql')
setPrimaryKey(v)
return
def test():
v = 'sh600216'
# print str(count)+'/'+str(total),'start',v
# try:
df = oneStock(v,
starttime=datetime(2016, 1, 1),
endtime=datetime(2016, 8, 30))
[conn, cur] = getCursor()
pdsql.to_sql(df, v, conn, flavor='mysql')
setPrimaryKey(v)
return
def check_login(email, password):
cursor = sql.getCursor()
cursor.execute("select user_id,password from email_user where email=%s", (email))
row = cursor.fetchone()
##log.debug("Row:")
##log.debug(row)
hashpw = row[1]
##log.debug("Hashpw: " + hashpw)
h = bcrypt.hashpw(password,hashpw)
##log.debug("h: " + h)
if row[0] and h == hashpw:
return row[0]
return None
def test(now):
import datetime
from sql import getCursor, insertStock
from stock import oneStock, getStockList
import pandas.io.sql as pdsql
before = now - datetime.timedelta(700)
v = 'sh600216'
df = oneStock(v, starttime=before, endtime=time)
df = df.iloc[[-1]]
[conn, cur] = getCursor()
pdsql.to_sql(df, v, conn, if_exists='append', index_label='date')
return
def fbUserId(request):
import face
from MySQLSessionStore import MySQLSessionStore
session_store = MySQLSessionStore()
cursor = sql.getCursor()
token = request.args.get('token')
fid = face.valid_face(token)
if fid == None:
return Response('[{"user_id":-200}]', mimetype='text/plain')
user_id = session_store.get_userid_from_face(fid)
if not user_id:
response = Response('[{"user_id": -100, "study_id": null, "firstname": "null", "surname": "null", "latlon": "null", "institution_name": "null", "campus_name": "null", "department_name": "null", "name_of_study": "null", "starting_year": null, "car": null, "gcm_id":null}]',mimetype='text/plain')
return response
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, gcm_id 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 facebook_user WHERE facebook_id=%s)",(fid))
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['institution_name'] = str(row[5])
c['campus_name'] = str(row[6])
c['department_name'] = str(row[7])
c['name_of_study'] = str(row[8])
c['starting_year'] = row[9]
c['car'] = row[10]
c['gcm_id'] = row[11]
rowarray.append(c)
j = json.dumps(rowarray, ensure_ascii=False)
response = Response(j, mimetype='text/plain')
sid = request.cookies.get('hccook')
if not (sid and session_store.session_valid(sid)):
import datetime
request.session = session_store.session_new("",user_id)
response.set_cookie('hccook', value=request.session.sid, max_age=3600*24*4, expires=datetime.datetime.utcnow() + datetime.timedelta(days=4))
return response
def test(now):
import datetime
from sql import getCursor,insertStock
from stock import oneStock,getStockList
import pandas.io.sql as pdsql
before=now-datetime.timedelta(700)
v='sh600216'
df=oneStock(v,starttime=before,endtime=time)
df=df.iloc[[-1]]
[conn,cur]=getCursor()
pdsql.to_sql(df, v, conn, if_exists='append',index_label='date')
return
def department(request):
cursor = sql.getCursor()
rowarray = []
cursor.execute("select * from department")
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['department_id'] = row[0]
c['institution_id'] = row[1]
c['department_name'] = row[2]
rowarray.append(c)
if rowarray:
return Response(json.dumps(rowarray, ensure_ascii=False), mimetype='text/plain')
return Response("{}",mimetype='text/plain')
def institution(request):
cursor = sql.getCursor()
rowarray = []
cursor.execute("select * from institution")
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c["institution_id"] = row[0]
c["institution_name"] = row[1]
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 init():
list=getStockList()
total=len(list)
count=1
for v in list:
print str(count)+'/'+str(total),'start',v
# try:
df=oneStock(v,starttime=datetime(2016,1,1),endtime=datetime(2016,8,30))
[conn,cur]=getCursor()
pdsql.to_sql(df, v, conn, flavor='mysql')
# setPrimaryKey(v)
break
# except :
# count+=1
# print 'error'
# continue
print str(count)+'/'+str(total),'end',v
count+=1
return
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 init():
list = getStockList()
total = len(list)
count = 1
for v in list:
print str(count) + '/' + str(total), 'start', v
# try:
df = oneStock(v,
starttime=datetime(2016, 1, 1),
endtime=datetime(2016, 8, 30))
[conn, cur] = getCursor()
pdsql.to_sql(df, v, conn, flavor='mysql')
# setPrimaryKey(v)
break
# except :
# count+=1
# print 'error'
# continue
print str(count) + '/' + str(total), 'end', v
count += 1
return
def getAllStudies(request):
#if not validate_login.is_logged_in(request):
# return validate_login.failed_login()
cursor = sql.getCursor()
rowarray = []
cursor.execute("SELECT * FROM study")
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['study_id'] = row[0]
c['department_id'] = row[1]
c['campus_id'] = row[2]
c['name_of_study'] = row[3]
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 study(request):
#if not validate_login.is_logged_in(request):
# return validate_login.failed_login()
cursor = sql.getCursor()
rowarray = []
cursor.execute("SELECT study_id, institution_name, campus_name, department_name, name_of_study FROM study 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")
rows = cursor.fetchall()
for row in rows:
c = collections.OrderedDict()
c['study_id'] = row[0]
c['institution_name'] = row[1]
c['campus_name'] = row[2]
c['department_name'] = row[3]
c['name_of_study'] = row[4]
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 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')
def get_gcm_id(user_id):
cursor = sql.getCursor()
cursor.execute("select gcm_id from user where user_id = %s",(user_id))
def get_userid(self, sid):
cursor = sql.getCursor()
cursor.execute("select user_id, created from session where session_id = %s and datediff(now(),created)<5 ",(sid))
return cursor.fetchone()[0]
def email_exists(email):
cursor = sql.getCursor()
cursor.execute("select COUNT(1) from email_user where email=%s", (email))
if cursor.fetchone()[0]:
return True
return False
