def careUser(arg, userInfo):
to_user = arg['userId']
from_user = userInfo['id']
fans = session.query(t_fans).filter(t_fans.from_user == from_user,
t_fans.to_user == to_user).all()
if len(fans) > 0:
session.delete(fans[0])
session.commit()
# session.close()
return returnFormat('', '已取消关注')
id = str(uuid.uuid1())
row = t_fans(id=id, from_user=from_user, to_user=to_user)
db.insert(row)
createTime = pendulum.now('UTC')
msg = {
'sendId': from_user,
'receiveId': to_user,
'time': createTime.float_timestamp * 1000,
'type': 4,
'data': {
'sendName': userInfo['name'],
'sendAvatar': userInfo['avatarUrl'],
'content': '',
}
}
socket1.sendMsg(msg)
addMsg(msg)
return returnFormat('', '关注成功')
def userAuth(arg, userInfo):
gameId = int(arg['gameId'])
userId = arg['userId']
authList = session.query(t_game, t_auth, t_level, t_user).outerjoin(
t_auth, t_game.id == t_auth.gameId).join(
t_user, t_user.id == t_auth.userId).join(
t_level,
and_(t_level.gameId == t_auth.gameId,
t_level.id == t_auth.levelId)).filter(
t_auth.userId == userId, t_auth.gameId == gameId)
print(authList)
authList = authList.all()
arry = []
for game, auth, level, user in authList:
item = {
'userId': auth.userId,
'id': auth.id,
'status': auth.status,
'voidSrc': auth.voidSrc,
'voidTime': auth.voidTime,
'gameImg': auth.gameImg,
'gameName': game.name,
'sex': auth.sex,
'levelId': auth.levelId,
'levelName': level.levelName,
'levelImg': level.levelImg,
'detail': auth.detail,
}
arry.append(item)
if len(authList) <= 0:
return returnFormat('', '用户未认证该游戏')
else:
return returnFormat(arry[0])
def login(arg):
openId = arg['openId']
# session_key = arg['session_key']
# sql = 'select * from user where openId="%s"' % (openId)
results = session.query(t_user).filter(t_user.openId == openId).all()
obj = {}
# results = runSql(sql)
# print(len(results))
if len(results) == 0:
results = False
else:
results = results[0]
if not results:
# session.close()
return returnFormat('', '登录openId未注册', '901')
else:
userId = results.id
token = results.token
# print('揭秘', token)
# print('揭秘', openId)
print(userId, '查询token')
# valid = validToken(token)
if not token:
valid = False
else:
tokenValid = aes.decrypt(token)
# print('登录揭秘', token)
valid = validToken(tokenValid)
if valid == False:
token = generate_token(userId)
session.query(t_user).filter(t_user.id == userId).update(
{'token': token})
try:
# 提交即保存到数据库:
session.commit()
# 关闭session:
# session.close()
except:
session.rollback()
return returnFormat('202', '数据库操作失败')
obj['userId'] = userId
obj['name'] = results.name
obj['avatarUrl'] = results.avatarUrl
obj['openId'] = results.openId
obj['token'] = token
# session.close()
return returnFormat(obj)
def getChatUnreadMsg ():
# print('获取用户的未读消息')
# print(userInfo)
userId = userInfo['id']
results = session.query(t_message).filter(t_message.receiveId==userId, t_message.msgType==1, t_message.status==0).all()
for item in results:
item.timeNum = arrow.get(item.time).timestamp * 1000
print(item.status, '结果集')
# sql = 'select * from message where receiveId="%s" and sendId="%s" and status=0' % (userId, sendId)
# results = runSql(sql)
# arry = []
# print(results)
# arry.append(item)
dic = {
'id': fields.String,
'sendId': fields.String,
'receiveId': fields.String,
'type': fields.String(attribute='msgType'),
'time': fields.Integer(attribute='timeNum'),
'data': fields.String(attribute='msg')
}
results = marshal(results, dic)
print('用户消息', results)
# session.close()
return returnFormat(results, '拉取用户未读消息不修改状态')
def addMsg (arg):
msgId = str(uuid.uuid1())
sendId = arg['sendId']
receiveId = arg['receiveId']
timeStr = arg['time']
startTime = int(timeStr)
startTime = datetime.datetime.fromtimestamp(startTime/1000)
msgType = int(arg['type'])
status = 0
content = arg.get('content', '')
row = session.query(t_user).filter(t_user.id==sendId).first()
data = arg['data']
msgStr = json.dumps(data, ensure_ascii=False)
db.select(row)
# print(arg)
# print('时间')
# userInfo = getUserByToken(token)
# if userInfo == '1' or userInfo == '2':
# return returnFormat('', 'token无效', '701')
# else:
# sql = 'insert into message (id, sendId, receiveId, msg, time, type, status) values ("%s", "%s", "%s", "%s", "%d", "%d", "%d")' % (msgId, sendId, receiveId, msg, timeStr, msgType, status)
row = t_message(id=msgId, sendId=sendId, receiveId=receiveId, msg=msgStr, time=startTime, msgType=msgType, status=0)
session.add(row)
session.commit()
# session.close()
return returnFormat('', '添加成功')
def userAuthGame(arg, userInfo):
userId = arg['userId']
authList = session.query(t_auth, t_game, t_level, t_user)
authList = authList.outerjoin(t_game, t_game.id == t_auth.gameId)
authList = authList.join(t_user, t_user.id == t_auth.userId)
authList = authList.join(
t_level,
and_(t_level.gameId == t_auth.gameId, t_level.id == t_auth.levelId))
authList = authList.filter(t_auth.userId == userId, t_auth.status == 1)
print(authList)
authList = authList.all()
arry = []
for auth, game, level, user in authList:
item = {
'userId': auth.userId,
'id': auth.id,
'status': auth.status,
'voidSrc': auth.voidSrc,
'voidTime': auth.voidTime,
'gameImg': auth.gameImg,
'gameId': auth.gameId,
'gameName': game.name,
'gameLogo': game.logo,
'sex': auth.sex,
'levelId': auth.levelId,
'levelName': level.levelName,
'levelImg': level.levelImg,
'detail': auth.detail,
}
arry.append(item)
db.select(authList)
return returnFormat(arry)
def addActivity(arg, userInfo):
# startTime = int(arg['startTime'])
startTime = int(arg['startTime'])
desc = arg['desc']
seat = int(arg['seat'])
# city = arg['city']
cover = arg['cover']
gameId = arg['gameId']
title = arg['title']
activityId = str(uuid.uuid1())
# createTime = int(round(time.time() * 1000))
createTime = pendulum.now('UTC')
if startTime == '':
startTime = 0
else:
startTime = int(startTime)
startTime = datetime.datetime.fromtimestamp(startTime / 1000)
startTime = pendulum.instance(startTime)
userId = userInfo['id']
results = t_activity(id=activityId,
gameId=gameId,
cover=cover,
detail=desc,
title=title,
startTime=startTime,
createTime=createTime,
seat=seat,
userId=userId,
vacancy=seat)
session.add(results)
session.commit()
# session.close()
# sql = 'insert into activity (id, userId, createTime, startTime, t_desc, t_limit, t_left, cover, gameId) values ("%s", "%s", "%d", "%d", "%s", "%d", "%d", "%s", "%s")' % (activityId, userId, createTime, startTime, desc, limit, 0, cover, gameId)
# results = runSql(sql)
return returnFormat(activityId, '发布成功')
def getReply(arg, userInfo):
commentId = arg['commentId']
reply = session.query(t_reply).filter(
t_reply.parentId == commentId).order_by(t_reply.time.asc()).all()
replydic = {
'activityId': fields.String,
'id': fields.String,
'content': fields.String,
'userId': fields.String,
'userAvatarUrl': fields.String,
'userName': fields.String,
'parentId': fields.String,
'toId': fields.String,
'toAvatarUrl': fields.String,
'toName': fields.String,
'time': fields.Integer,
'replyCmtId': fields.String
}
arry = []
for item in reply:
time = pendulum.instance(item.time).float_timestamp * 1000
item.time = time
arry.append(item)
results = marshal(arry, replydic)
db.select(reply)
return returnFormat(results)
def getSysMsg (arg, userInfo):
# print('获取用户的未读消息')
# print(userInfo)
unReadNum = 0
userId = userInfo['id']
results = session.query(t_message, t_user.avatarUrl).join(t_user, t_message.sendId == t_user.id).filter(t_message.receiveId==userId, t_message.msgType==2).all()
for item, avatarUrl in results:
item.sendAvatarUrl = avatarUrl
item.timeNum = arrow.get(item.time).timestamp * 1000
if item.status == 0:
unReadNum += 1
print(item.status, '结果集')
# sql = 'select * from message where receiveId="%s" and sendId="%s" and status=0' % (userId, sendId)
# results = runSql(sql)
# arry = []
# print(results)
# arry.append(item)
dic = {
'id': fields.String,
'sendAvatarUrl': fields.String,
'sendId': fields.String,
'receiveId': fields.String,
'type': fields.String(attribute='msgType'),
'time': fields.Integer(attribute='timeNum'),
'data': fields.String(attribute='msg')
}
results = marshal(results, dic)
print('用户消息', results)
# session.close()
return returnFormat({'list': results, 'unReadNum': unReadNum}, '拉取系统未读消息')
def getApplyMsg (arg, userInfo):
unReadNum = 0
userId = userInfo['id']
results = session.query(t_message).filter(t_message.receiveId==userId, t_message.msgType==3).all()
for item in results:
item.timeNum = arrow.get(item.time).timestamp * 1000
if item.status == 0:
unReadNum += 1
print(item.status, '结果集')
# sql = 'select * from message where receiveId="%s" and sendId="%s" and status=0' % (userId, sendId)
# results = runSql(sql)
# arry = []
# print(results)
# arry.append(item)
dic = {
'id': fields.String,
'sendId': fields.String,
'receiveId': fields.String,
'type': fields.String(attribute='msgType'),
'time': fields.Integer(attribute='timeNum'),
'data': fields.Nested(attribute='msg')
}
results = marshal(results, dic)
print('用户消息', results)
# session.close()
readChatMsg({
'receiveId': userId,
'sendId': '001'
})
return returnFormat({'list': results, 'unReadNum': unReadNum}, '拉取上车未读消息')
def getWords(arg, userInfo):
gameId = arg['gameId']
userId = arg['userId']
row = session.query(t_words,
t_user).join(t_user,
t_user.id == t_words.from_user).filter(
t_words.gameId == gameId,
t_words.to_user == userId).all()
arry = []
for words, user in row:
item = {
'id': words.id,
'userId': words.from_user,
'to_user': words.to_user,
'activityId': words.activityId,
'content': words.content,
'gameId': words.gameId,
'userName': user.name,
'avatarUrl': user.avatarUrl,
'time':
pendulum.instance(words.time).float_timestamp * 1000, # 发帖时间
}
arry.append(item)
db.select(row)
return returnFormat(arry)
def joinActivity(arg, userInfo):
ids = str(uuid.uuid1())
activityId = arg['id']
detail = arg['detail']
userId = userInfo['id']
status = 3
createTime = pendulum.now('UTC')
results = session.query(t_passenger).filter(
t_passenger.userId == userId,
t_passenger.activityId == activityId).all()
# session.close()
with session.no_autoflush:
activity = session.query(t_activity, t_user.name,
t_user.avatarUrl).join(
t_user,
t_user.id == t_activity.userId).filter(
t_activity.id == activityId).one()
# session.close()
if len(results) > 0:
return returnFormat('', '已申请过', '901')
row = t_passenger(id=ids,
activityId=activityId,
detail=detail,
createTime=createTime,
userId=userId,
status=status)
session.add(row)
session.commit()
# session.close()
arg = {
'sendId': userId,
'receiveId': activity.Activity.userId,
'time': createTime.format('x'),
'type': 3,
'data': {
'sendName': activity.name,
'sendAvatar': activity.avatarUrl,
'content': detail,
'activityTitle': activity.Activity.title,
'activityId': activityId
}
}
socket1.sendMsg(arg)
addMsg(arg)
return returnFormat('', '参加成功')
def readChatMsg (arg, userInfo):
receiveId = arg['receiveId']
sendId = arg['sendId']
msgType = int(arg['type'])
id = arg.get('id', False)
lenNum = 0
if msgType==1:
results = session.query(t_message).filter(t_message.receiveId==receiveId, t_message.msgType==msgType, t_message.sendId==sendId, t_message.status==0)
lenNum = len(results.all())
results.update({t_message.status: 1})
if msgType == 2 or msgType == 3 or msgType == 4 or msgType == 5:
if not id:
return returnFormat('', '参数错误', '901')
results = session.query(t_message).filter(t_message.id == id,t_message.status==0).update({t_message.status: 1})
session.commit()
# session.close()
print('更新成功', lenNum, '####' ,msgType)
return returnFormat(lenNum, '')
def readMsgById(arg, userInfo):
id = arg['id']
lenNum = 0
row = session.query(t_message).filter(t_message.id == id, t_message.status==0)
lenNum = len(row.all())
row.update({t_message.status: 1})
session.commit()
# session.close()
return returnFormat(lenNum, '')
def updateUser(arg, userInfo):
arg.pop('clientType')
arg.pop('deviceId')
person = session.query(t_user).filter(
t_user.id == userInfo['id']).update(arg)
session.commit()
# session.close()
return returnFormat('')
def getUserById(arg, userInfo):
print('用户信心token', userInfo)
userId = arg['userId']
myUserId = userInfo['id']
isfans = False
# sql2 = 'select * from user where id="%s"' % (userId)
results = session.query(t_user).filter(t_user.id == userId).all()
fans = session.query(t_fans).filter(t_fans.from_user == myUserId,
t_fans.to_user == userId).all()
fansNum = session.query(t_fans).filter(t_fans.to_user == userId).all()
fansNum = len(fansNum)
if len(fans) > 0:
isfans = True
if len(results) == 0:
results = False
else:
arry = []
dic = {
'id': fields.String,
'name': fields.String,
'openId': fields.String,
'token': fields.String,
'phone': fields.String,
'city': fields.String,
'auth': fields.String,
'avatarUrl': fields.String,
'age': fields.String,
'motto': fields.String,
'isFans': fields.Boolean,
'fansNum': fields.Integer
}
for item in results:
print('用户信息', item.id)
item.isFans = isfans
item.fansNum = fansNum
arry.append(item)
results = marshal(arry, dic)
results = results[0]
print('用户信息', results)
# session.close()
if not results:
return returnFormat('', '用户不存在', '901')
return returnFormat(results)
def recallJoin(arg, userInfo):
passengerId = arg['id']
activityId = arg['activityId']
row = session.query(t_passenger).filter(
t_passenger.id == passengerId,
t_passenger.activityId == activityId).first()
session.delete(row)
session.commit()
# session.close()
return returnFormat('', '撤回成功')
def editStatus(arg, userInfo):
userId = arg['userId']
activityId = arg['activityId']
status = int(arg['status'])
actionUserid = userInfo['id']
activity = session.query(t_activity).filter(
t_activity.id == activityId).one()
# session.close()
if activity.userId != actionUserid:
return returnFormat('', '没有权限', '901')
row = session.query(t_passenger).filter(
t_passenger.userId == userId,
t_passenger.activityId == activityId).update({'status': status})
session.commit()
# session.close()
session.query(t_activity).filter(t_activity.id == activityId).update(
{'vacancy': activity.seat - 1})
session.commit()
# session.close()
return returnFormat('', '操作成功')
def auth(arg, userInfo):
gameId = int(arg['gameId'])
voidSrc = arg['voidSrc']
voidTime = arg['voidTime']
gameImg = arg['gameImg']
detail = arg['desc']
levelId = int(arg['levelId'])
sex = int(arg['sex'])
authId = str(uuid.uuid1())
print('用户信息', userInfo)
userId = userInfo['id']
createTime = pendulum.now('UTC')
authRow = session.query(t_auth).filter(t_auth.userId == userId,
t_auth.gameId == gameId).first()
print('信息')
print(authRow)
if authRow != None:
if authRow.status != 2:
session.delete(authRow)
session.commit()
# session.close()
else:
return returnFormat('', '提交失败，认证信息正在审核中', '901')
row = t_auth(id=authId,
gameId=gameId,
voidSrc=voidSrc,
detail=detail,
gameImg=gameImg,
sex=sex,
status=2,
levelId=levelId,
userId=userId,
createTime=createTime,
voidTime=voidTime)
session.add(row)
session.commit()
# session.close()
return returnFormat('', '提交成功')
def getCareList(arg, userInfo):
userId = arg['userId']
fansNum = session.query(t_fans, t_user).join(
t_user,
t_user.id == t_fans.to_user).filter(t_fans.from_user == userId).all()
arry = []
for fans, user in fansNum:
item = {
'userId': user.id,
'id': fans.id,
'avatarUrl': user.avatarUrl,
'name': user.name,
'motto': user.motto
}
arry.append(item)
# session.close()
return returnFormat(arry)
def activityDetail(arg, userInfo):
activityId = arg['id']
results = results = session.query(
t_activity, t_user.name, t_user.age, t_user.avatarUrl, t_game.name,
t_game.logo, t_auth.sex, t_auth.levelId, t_auth.status,
t_level.levelImg, t_user.id).outerjoin(
t_auth,
and_(t_auth.userId == t_activity.userId,
t_auth.gameId == t_activity.gameId)).join(
t_user, t_activity.userId == t_user.id).join(
t_game, t_activity.gameId == t_game.id).outerjoin(
t_level, t_level.id == t_auth.levelId).filter(
t_activity.id == activityId)
# results = session.query(t_activity, t_user.name, t_game.name, t_game.logo).join(t_user, t_activity.userId == t_user.id).join(t_game, t_activity.gameId==t_game.id).all()
arry = []
for item, userName, userAge, avatarUrl, gameName, gameLogo, authSex, levelId, authStatus, levelImg, userId in results:
row = {
"id": item.id,
"detail": item.detail,
"gameId": item.gameId,
"title": item.title,
"userName": userName,
"age": userAge,
"gameName": gameName,
"gameLogo": gameLogo,
"sex": authSex,
"levelId": levelId,
"auth": authStatus,
"avatarUrl": avatarUrl,
"levelImg": levelImg,
"userId": item.userId,
"cover": item.cover,
"vacancy": item.vacancy,
"seat": item.seat,
# item.startDate = pendulum.instance(item.startTime).float_timestamp
"startTime":
pendulum.instance(item.startTime).float_timestamp * 1000,
"createTime":
pendulum.instance(item.createTime).float_timestamp * 1000
}
arry.append(row)
return returnFormat(arry[0])
def commentUser(arg, userInfo):
activityId = arg['id']
content = arg['content']
fromUser = userInfo['id']
row = session.query(t_activity).filter(t_activity.id == activityId).first()
toUser = row.userId
gameId = row.gameId
ids = str(uuid.uuid1())
createTime = pendulum.now('UTC')
addRow = t_words(id=ids,
from_user=fromUser,
to_user=toUser,
content=content,
activityId=activityId,
gameId=gameId,
time=createTime)
db.insert(addRow)
return returnFormat('', '评论成功')
def getGameList(arg):
time1 = pendulum.now('UTC').float_timestamp * 1000
rows = session.query(t_game).all()
rowsDic = {
'id': fields.Integer(attribute='id'),
'logo': fields.String(attribute='logo'),
'name': fields.String(attribute='name'),
'views': fields.String(attribute='views')
}
time2 = pendulum.now('UTC').float_timestamp * 1000
t = time2 - time1
t = str(t)
print('getGameList查询耗时：', t)
rows = marshal(rows, rowsDic)
# session.close()
return returnFormat(rows)
def getGameInfo(arg, userInfo):
gameId = arg['id']
rows = session.query(t_game).filter(t_game.id == gameId).all()
levelRows = session.query(t_level).filter(t_level.gameId == gameId).all()
rowsDic = {
'gameId': fields.Integer(attribute='id'),
'gameLogo': fields.String(attribute='logo'),
'gameName': fields.String(attribute='name'),
'gameViews': fields.String(attribute='views')
}
levelRowsDic = {
'id': fields.String(attribute='id'),
'levelName': fields.String,
'levelImg': fields.String
}
rows = marshal(rows, rowsDic)[0]
levelRows = marshal(levelRows, levelRowsDic)
rows['levels'] = levelRows
return returnFormat(rows)
def getActivityUsers(arg, userInfo):
activityId = arg['id']
activity = session.query(t_activity).filter(
t_activity.id == activityId).first()
results = session.query(
t_passenger, t_passenger.detail, t_passenger.status,
t_passenger.createTime, t_user.id, t_user.name, t_user.avatarUrl,
t_auth.sex, t_user.age,
t_auth.status).join(t_user, t_passenger.userId == t_user.id).outerjoin(
t_auth,
and_(t_passenger.userId == t_auth.userId,
t_auth.gameId == activity.gameId)).filter(
t_passenger.activityId == activityId).all()
arry = []
for item, detail, status, createTime, userId, userName, userAvatarUrl, userSex, userAge, authstatus in results:
item.userName = userName
item.age = userAge if authstatus == 1 else ''
item.avatarUrl = userAvatarUrl
item.sex = userSex if authstatus == 1 else ''
item.userId = userId
item.detail = detail
item.status = status
item.createTime = arrow.get(item.createTime).timestamp * 1000
arry.append(item)
dic = {
'detail': fields.String, # 描述
'userName': fields.String, # 用户名
'userId': fields.String, # 用户id
'avatarUrl': fields.String, # 活动封面
'createTime': fields.Integer, # 发帖时间
'sex': fields.String, # 性别
'age': fields.String, # 段位id
'age': fields.String, # 年龄
'status': fields.String # 请求状态
}
# session.close()
results = marshal(arry, dic)
# db.select(results)
return returnFormat(results)
def register(arg):
print(arg)
# regType = arg['regType']
regAccount = arg['openId']
avatarUrl = arg['avatarUrl']
name = arg['name']
userId = str(uuid.uuid1())
token = generate_token(userId)
# sql = 'insert into user (name, openId, token, id) values ("%s", "%s", "%s", "%s")' % (name, regAccount, token, userId)
results = t_user(name=name,
openId=regAccount,
avatarUrl=avatarUrl,
token=token,
id=userId)
# 添加到session:
session.add(results)
# 提交即保存到数据库:
session.commit()
# 关闭session:
# session.close()
return returnFormat({'token': token, 'userId': userId}, '注册成功')
def action (request, sqlFn, isValidAuth=1):
# print('签名', request.META.get("HTTP_ACCESS_TOKEN"))
# print('签名2', request.META.get("HTTP_CLIENT"))
# print(request.META.get("HTTP_ACCESS_TOKEN"))
time1 = pendulum.now('UTC').float_timestamp * 1000
if request.method == 'GET' :
params = request.GET.dict()
else:
params = json.loads(request.body)
# print('签名2', params)
if isValidAuth == 1:
auth = request.META.get("HTTP_ACCESS_TOKEN")
sqlReuslt = getUserByToken(auth, params['openId'])
if sqlReuslt == '1' or sqlReuslt == '2':
sqlReuslt = returnFormat('', 'token无效', '701')
else:
# print(sqlReuslt['id'], '用户id')
# try:
sqlReuslt = sqlFn(params, sqlReuslt)
# except Exception as e:
# print('mysql异常', e)
# session.rollback()
else:
sqlReuslt = sqlFn(params)
time2 = pendulum.now('UTC').float_timestamp * 1000
t = time2 - time1
t = str(t)
print(time1)
print(time2)
print('接口耗时:', t)
# print('***********返回数据start***************')
# print(json.dumps(sqlReuslt, ensure_ascii=False))
# print('***********返回数据end***************')
return HttpResponse(json.dumps(sqlReuslt, ensure_ascii=False), content_type='application/json; charset=utf-8')
def gameStatus(arg, userInfo):
userId = userInfo['id']
ret = session.query(t_game, t_auth.status).outerjoin(
t_auth, and_(t_auth.gameId == t_game.id, t_auth.userId == userId))
print(ret, '我们')
dic = {
'gameId': fields.String,
'logo': fields.String,
'gameName': fields.String,
'status': fields.Integer
}
arry = []
for item, status in ret:
s = 0 if not status else status
print(s, '状态')
row = {
'gameId': item.id,
'logo': item.logo,
'gameName': item.name,
'status': s
}
arry.append(row)
results = marshal(arry, dic)
return returnFormat(results)
def activityList(arg, userInfo):
page = int(arg['page']) - 1
pageSize = int(arg['pageSize'])
userId = arg.get('userId', False)
gameId = arg.get('gameId', False)
filterUserId = userInfo['id']
# if userId:
# filterUserId = userId
time1 = pendulum.now('UTC').float_timestamp * 1000
results = session.query(
t_activity, t_user.name, t_user.age, t_user.avatarUrl, t_game.name,
t_game.logo, t_auth.sex, t_auth.levelId, t_auth.status,
t_level.levelImg, t_user.id, t_passenger.id).outerjoin(
t_auth,
and_(t_auth.userId == t_activity.userId,
t_auth.gameId == t_activity.gameId)).join(
t_user, t_activity.userId == t_user.id).outerjoin(
t_passenger,
and_(t_passenger.activityId == t_activity.id,
t_passenger.userId == filterUserId)).join(
t_game,
t_activity.gameId == t_game.id).outerjoin(
t_level,
t_level.id == t_auth.levelId).order_by(
t_activity.createTime.desc())
if userId:
results = results.filter(t_activity.userId == userId)
if gameId:
results = results.filter(t_activity.gameId == gameId)
print(results)
num = results.count()
results = results.limit(pageSize).offset(page * pageSize)
# results = session.query(t_activity, t_user.name, t_game.name, t_game.logo).join(t_user, t_activity.userId == t_user.id).join(t_game, t_activity.gameId==t_game.id).all()
time2 = pendulum.now('UTC').float_timestamp * 1000
t = time2 - time1
t = str(t)
print('sql查询耗时:', t)
arry = []
for item, userName, userAge, avatarUrl, gameName, gameLogo, authSex, levelId, authStatus, levelImg, userId, pid in results:
if pid == None:
passenger = 2
else:
passenger = 1
print('活动时间;', item.startTime)
print('活动时间;', item.createTime)
print('活动id;', item.id)
act = {
'id': item.id, # 活动ID
'userId': userId, # 用户
'detail': item.detail, # 描述
'title': item.title, # 标题
'gameId': item.gameId, # 游戏ID
'gameName': gameName, # 游戏名字
'gameLogo': gameLogo, # 游戏图标
'userName': userName, # 用户名
'avatarUrl': avatarUrl, # 用户名 'userId': fields.String, # 用户id
'cover': item.cover, # 活动封面
'desc': item.detail, # 描述
'isPassenger': passenger, # 1已申请，2未申请
'startTime':
pendulum.instance(item.startTime).float_timestamp * 1000, # 发车时间
# 'startDate': fields.Integer(attribute='startTime'), # 发车时间
'createTime':
pendulum.instance(item.createTime).float_timestamp * 1000, # 发帖时间
# 'createTime': 1576771200000.0, # 发帖时间
'vacancy': item.vacancy, # 空位
'seat': item.seat, # 座位总数
'sex': authSex, # 性别
'levelId': levelId, # 段位id
'auth': authStatus, # 认证状态
'age': userAge, # 年龄
'levelImg': levelImg # 段位logo
}
arry.append(act)
time3 = pendulum.now('UTC').float_timestamp * 1000
t1 = time3 - time2
t1 = str(t1)
# session.close()
print('遍历耗时:', t1)
return returnFormat(arry, total=num)
def getMyJoin(arg, userInfo):
page = int(arg['page']) - 1
pageSize = int(arg['pageSize'])
userId = userInfo['id']
results = session.query(t_passenger, t_activity, t_auth, t_level, t_game,
t_user, t_words)
results = results.join(t_activity, t_activity.id == t_passenger.activityId)
results = results.outerjoin(
t_auth,
and_(t_auth.userId == t_passenger.userId,
t_auth.gameId == t_activity.gameId))
results = results.join(t_user, t_passenger.userId == t_user.id)
results = results.join(t_game, t_activity.gameId == t_game.id)
results = results.outerjoin(t_level, t_level.id == t_auth.levelId)
results = results.outerjoin(t_words, t_words.activityId == t_activity.id)
results = results.order_by(t_passenger.createTime.asc())
results = results.filter(t_passenger.userId == userId)
results.limit(pageSize).offset(page * pageSize)
arry = []
for item, activity, auth, level, game, user, words in results:
startTime = pendulum.instance(
activity.startTime).float_timestamp * 1000
nowTime = pendulum.now('UTC').float_timestamp * 1000
if words == None:
comment = False
else:
comment = True
if nowTime > startTime:
if item.status == 2:
status = 4 # 已完成
elif item.status == 3:
status = 5 # 已过期
else:
status = item.status
else:
status = item.status
ret = {
'comment': comment,
'passengerId': item.id, # 乘客编号
'id': activity.id, # 活动ID
'userId': userId, # 用户
'detail': activity.detail, # 描述
'status': status, # 上车状态
'gameId': activity.gameId, # 游戏ID
'gameName': game.name, # 游戏名字
'gameLogo': game.logo, # 游戏图标
'userName': user.name, # 用户名
'avatarUrl':
user.avatarUrl, # 用户名 'userId': fields.String, # 用户id
'cover': activity.cover, # 活动封面
'desc': item.detail, # 乘客留言
'startTime':
pendulum.instance(activity.startTime).float_timestamp *
1000, # 发车时间
# 'startDate': fields.Integer(attribute='startTime'), # 发车时间
'createTime':
pendulum.instance(activity.createTime).float_timestamp *
1000, # 发帖时间
'vacancy': activity.vacancy, # 空位
'seat': activity.seat, # 座位总数
'sex': auth.sex if auth != None else '', # 性别
'levelId': auth.levelId if auth != None else '', # 段位id
'auth': auth.status if auth != None else '', # 认证状态
'age': user.age, # 年龄
'levelImg': level.levelImg if level != None else '' # 段位logo
}
arry.append(ret)
# session.close()
return returnFormat(arry)
