def query_group(session_token, skip, limit, params):
"""
查询有权限对组， TODO 关联查询
:param params:
:param limit:
:param skip:
:param session_token:
:return:
"""
user_id = util.review_auth_token(APP_SECRET, session_token)
user = User.query.filter_by(id=user_id).first()
if user is None:
return jsonify({"result": {"error_code": 1, "msg": 'miss userk'}}), 200
query = db.session.query(
Group, User).filter(GroupUser.userId == user_id).filter(
GroupUser.groupId ==
Group.id).filter(Group.belongUserId == User.id).filter(
GroupUser.isDisable == 0).filter(
GroupUser.isAudit == 1).limit(limit).offset(skip).all()
results = []
for group, user in query:
results.append({
"objectId": group.id,
"name": group.name,
"avatar": group.avatar,
"desc": group.desc,
"belongUserId": group.belongUserId,
"belongUserName": user.lastName + user.firstName,
"isPublic": group.isPublic,
"createdAt": util.get_iso8601_from_dt(group.createdAt),
"updatedAt": util.get_iso8601_from_dt(group.updatedAt),
})
return jsonify({"results": results})
def query_goods(session_token, skip, limit, params):
"""
查询有权限对组， TODO 关联查询
:param params:
:param limit:
:param skip:
:param session_token:
:return:
"""
user_id = util.review_auth_token(APP_SECRET, session_token)
user = User.query.filter_by(id=user_id).first()
if user is None:
return jsonify({"result": {"error_code": 1, "msg": 'miss user'}}), 200
query = db.session.query(Goods, Space, User, Position).filter(Goods.spaceId == Space.id) \
.filter(Goods.belongUserId == User.id) \
.filter(Goods.positionId == Position.id)
if params is None:
params = {}
if "belongGroupId" in params:
query = query.filter(Goods.belongGroupId == params["belongGroupId"])
if "spaceId" in params:
query = query.filter(Goods.spaceId == params["spaceId"])
if "positionId" in params:
query = query.filter(Goods.positionId == params["positionId"])
if "type" in params:
query = query.filter(Goods.type == params["type"])
if "objectId" in params:
query = query.filter(Goods.id == params["objectId"])
if "isDisable" in params:
query = query.filter(Goods.isDisable == params["isDisable"])
else:
query = query.filter(Goods.isDisable == 0)
# 私密性
query = query.filter(
or_(Goods.isPublic == 1,
and_(Goods.isPublic == 0, Goods.belongUserId == user_id)))
query = query.order_by(desc(Goods.id)).limit(limit).offset(skip).all()
results = []
for data, space, user, position in query:
results.append({
"objectId": data.id,
"name": data.name,
"avatar": data.avatar,
"coordinate": data.coordinate,
"belongUserId": data.belongUserId,
"belongUserName": user.lastName + user.firstName,
"belongGroupId": data.belongGroupId,
"spaceId": data.spaceId,
"spaceName": space.name,
"positionId": data.positionId,
"positionName": position.name,
"type": data.type,
"isPublic": data.isPublic,
"note": get_latest_note_in_goods(data.id),
"marksNum": get_marks_num_in_goods(data.id),
"newsNum": get_news_num_in_goods(data.id),
"createdAt": util.get_iso8601_from_dt(data.createdAt),
"updatedAt": util.get_iso8601_from_dt(data.updatedAt),
})
return jsonify({"results": results})
def query_position(session_token, skip, limit, params):
"""
查询有权限对组，
:param params:
:param limit:
:param skip:
:param session_token:
:return:
"""
user_id = util.review_auth_token(APP_SECRET, session_token)
user = User.query.filter_by(id=user_id).first()
if user is None:
return jsonify({"result": {"error_code": 1, "msg": 'miss user'}}), 200
query = db.session.query(Position, User, Space) \
.filter(Position.spaceId == Space.id).filter(Position.belongUserId == User.id)
if params is None:
params = {}
if "belongGroupId" in params:
query = query.filter(Position.belongGroupId == params["belongGroupId"])
if "spaceId" in params:
query = query.filter(Position.spaceId == params["spaceId"])
if "objectId" in params:
query = query.filter(Position.id == params["objectId"])
# 私密性
query = query.filter(
or_(Position.isPublic == 1,
and_(Position.isPublic == 0, Position.belongUserId == user_id)))
query = query.filter(
Position.isDisable == 0).limit(limit).offset(skip).all()
results = []
for data, user, space in query:
results.append({
"objectId": data.id,
"name": data.name,
"avatar": data.avatar,
"coordinate": data.coordinate,
"belongUserId": data.belongUserId,
"belongGroupId": data.belongGroupId,
"spaceId": data.spaceId,
"belongUserName": user.lastName,
"spaceName": space.name,
"goodsNum": get_goods_num_in_position(data.spaceId),
"membersNum": get_members_num_in_position(data.spaceId),
"isPublic": data.isPublic,
"createdAt": util.get_iso8601_from_dt(data.createdAt),
"updatedAt": util.get_iso8601_from_dt(data.updatedAt),
})
return jsonify({"results": results})
def pack_users_me(user):
"""
把user打包成返回数据
:param user:
:return:
"""
oid = str(user.id)
user_dict = dict(user.__dict__)
user_dict['objectId'] = oid
if "password" in user_dict:
del user_dict['password']
del user_dict["_sa_instance_state"]
del user_dict["checkCode"]
user_dict['updatedAt'] = util.get_iso8601_from_dt(user_dict['updatedAt'])
user_dict['createdAt'] = util.get_iso8601_from_dt(user_dict['createdAt'])
print(user_dict)
return jsonify(user_dict), 200
def query_marks(session_token, skip, limit, params):
"""
查询有权限对组， TODO 关联查询
:param params:
:param limit:
:param skip:
:param session_token:
:return:
"""
user_id = util.review_auth_token(APP_SECRET, session_token)
user = User.query.filter_by(id=user_id).first()
if user is None:
return jsonify({"result": {"error_code": 1, "msg": 'miss user'}}), 200
query = db.session.query(Marks, User).filter(
Marks.belongUserId == User.id).filter(Marks.isDisable == 0)
if params is None:
params = {}
if "belongGroupId" in params:
query = query.filter(Marks.belongGroupId == params["belongGroupId"])
if "spaceId" in params:
query = query.filter(Marks.spaceId == params["spaceId"])
if "positionId" in params:
query = query.filter(Marks.positionId == params["positionId"])
if "goodsId" in params:
query = query.filter(Marks.goodsId == params["goodsId"])
if "objectId" in params:
query = query.filter(Marks.id == params["objectId"])
query = query.order_by(desc(Marks.id)).limit(limit).offset(skip).all()
results = []
for data, user in query:
results.append({
"objectId": data.id,
"belongUserId": data.belongUserId,
"belongUserName": user.lastName + user.firstName,
"avatar": user.avatar,
"belongGroupId": data.belongGroupId,
"spaceId": data.spaceId,
"positionId": data.positionId,
"goodsId": data.goodsId,
"isPublic": data.isPublic,
"createdAt": util.get_iso8601_from_dt(data.createdAt),
"updatedAt": util.get_iso8601_from_dt(data.updatedAt),
})
return jsonify({"results": results})
def query_group_user(session_token, skip, limit, params):
"""
查询分组用户
:param session_token:
:param skip:
:param limit:
:param params:
:return:
"""
user_id = util.review_auth_token(APP_SECRET, session_token)
user = User.query.filter_by(id=user_id).first()
if user is None:
return jsonify({"result": {"error_code": 1, "msg": 'miss userk'}}), 200
query = db.session.query(
User, GroupUser).filter(GroupUser.groupId == params["groupId"]).filter(
GroupUser.userId == User.id).filter(GroupUser.isDisable == 0)
if "isAudit" in params:
query = query.filter(GroupUser.isAudit == params["isAudit"])
query = query.limit(limit).offset(skip).all()
results = []
for user, group_user in query:
results.append({
"objectId":
group_user.id,
"name":
user.lastName + user.firstName,
"avatar":
user.avatar,
"userId":
group_user.userId,
"groupId":
group_user.groupId,
"isAudit":
group_user.isAudit,
"createdAt":
util.get_iso8601_from_dt(group_user.createdAt),
"updatedAt":
util.get_iso8601_from_dt(group_user.updatedAt),
})
return jsonify({"results": results})
def fix_search(session_token, keyword, belong_group_id):
"""
混合查询，
:param belong_group_id:
:param keyword:
:param session_token:
:return:
"""
user_id = util.review_auth_token(APP_SECRET, session_token)
user = User.query.filter_by(id=user_id).first()
if user is None:
return jsonify({"result": {"error_code": 1, "msg": 'miss user'}}), 200
# 查询空间数据
query = db.session.query(Space, User).filter(Space.belongUserId == User.id).filter(
Space.belongGroupId == user.defaultGroupId) \
.filter(Space.isDisable == 0)
query = query.filter(Space.belongGroupId == belong_group_id).filter(
Space.name.like(f"%{keyword}%"))
# 私密性
query = query.filter(
or_(Space.isPublic == 1,
and_(Space.isPublic == 0, Space.belongUserId == user_id)))
query = query.all()
space_results = []
for data, user in query:
space_results.append({
"objectId":
data.id,
"name":
data.name,
"avatar":
data.avatar,
"belongUserId":
data.belongUserId,
"belongUserName":
user.lastName,
"belongGroupId":
data.belongGroupId,
"positionNum":
get_position_num_in_space(data.id),
"goodsNum":
get_goods_num_in_space(data.id),
"membersNum":
get_members_num_in_space(data.id),
"isPublic":
data.isPublic,
"createdAt":
util.get_iso8601_from_dt(data.createdAt),
"updatedAt":
util.get_iso8601_from_dt(data.updatedAt),
})
# 查询位置数据
query = db.session.query(Position, User, Space) \
.filter(Position.spaceId == Space.id).filter(Position.belongUserId == User.id)
query = query.filter(Space.belongGroupId == belong_group_id).filter(
Position.name.like(f"%{keyword}%"))
# 私密性
query = query.filter(
or_(Position.isPublic == 1,
and_(Position.isPublic == 0, Position.belongUserId == user_id)))
query = query.filter(Position.isDisable == 0).all()
position_results = []
for data, user, space in query:
position_results.append({
"objectId":
data.id,
"name":
data.name,
"avatar":
data.avatar,
"coordinate":
data.coordinate,
"belongUserId":
data.belongUserId,
"belongGroupId":
data.belongGroupId,
"spaceId":
data.spaceId,
"belongUserName":
user.lastName,
"spaceName":
space.name,
"goodsNum":
get_goods_num_in_position(data.spaceId),
"membersNum":
get_members_num_in_position(data.spaceId),
"isPublic":
data.isPublic,
"createdAt":
util.get_iso8601_from_dt(data.createdAt),
"updatedAt":
util.get_iso8601_from_dt(data.updatedAt),
})
query = db.session.query(Goods, Space, User, Position).filter(Goods.spaceId == Space.id) \
.filter(Goods.belongUserId == User.id) \
.filter(Goods.positionId == Position.id)
query = query.filter(Space.belongGroupId == belong_group_id).filter(
Goods.name.like(f"%{keyword}%"))
query = query.filter(Goods.isDisable == 0)
# 私密性
query = query.filter(
or_(Goods.isPublic == 1,
and_(Goods.isPublic == 0, Goods.belongUserId == user_id)))
query = query.order_by(desc(Goods.id)).all()
goods_results = []
for data, space, user, position in query:
goods_results.append({
"objectId":
data.id,
"name":
data.name,
"avatar":
data.avatar,
"coordinate":
data.coordinate,
"belongUserId":
data.belongUserId,
"belongUserName":
user.lastName + user.firstName,
"belongGroupId":
data.belongGroupId,
"spaceId":
data.spaceId,
"spaceName":
space.name,
"positionId":
data.positionId,
"positionName":
position.name,
"type":
data.type,
"isPublic":
data.isPublic,
"note":
get_latest_note_in_goods(data.id),
"marksNum":
get_marks_num_in_goods(data.id),
"newsNum":
get_news_num_in_goods(data.id),
"createdAt":
util.get_iso8601_from_dt(data.createdAt),
"updatedAt":
util.get_iso8601_from_dt(data.updatedAt),
})
return jsonify({
"result": {
"data": {
"space_results": space_results,
"position_results": position_results,
"goods_results": goods_results
},
"error_code": 0,
"msg": "查询成功"
}
})
def query_news(session_token, skip, limit, params):
"""
查询有权限对组， belongGroupId, spaceId, positionId, goodsId,
:param params:
:param limit:
:param skip:
:param session_token:
:return:
"""
user_id = util.review_auth_token(APP_SECRET, session_token)
user = User.query.filter_by(id=user_id).first()
if user is None:
return jsonify({"result": {"error_code": 1, "msg": 'miss user'}}), 200
# sub_qry = db.session.query(func.count(Reads.id).label("read_flag")).filter(
# Reads.newsId == News.id).filter(Reads.belongUserId == user_id).correlate(News).as_scalar()
query = db.session.query(News, User, Space, Position).filter(News.isDisable == 0) \
.filter(News.belongUserId == User.id) \
.filter(News.spaceId == Space.id) \
.filter(News.positionId == Position.id) \
.filter(News.goodsId == Goods.id) \
.filter(Goods.isDisable == 0) \
.filter(not_(exists().where(and_(News.id == Reads.newsId, Reads.belongUserId == user_id))))
if params is None:
params = {}
if "belongGroupId" in params:
query = query.filter(News.belongGroupId == params["belongGroupId"])
if "spaceId" in params:
query = query.filter(News.spaceId == params["spaceId"])
if "positionId" in params:
query = query.filter(News.positionId == params["positionId"])
if "goodsId" in params:
query = query.filter(News.goodsId == params["goodsId"])
if "type" in params:
query = query.filter(News.type == params["type"])
if "objectId" in params:
query = query.filter(News.id == params["objectId"])
query = query.order_by(desc(News.id)).limit(limit).offset(skip).all()
results = []
for data, user, space, position in query:
if data.type == 4:
content = "# 共{}个物品 #".format(
get_goods_num_in_position(data.positionId))
else:
content = data.content
results.append({
"objectId": data.id,
"title": data.title,
"content": content,
"type": data.type,
"avatar": data.avatar,
"belongUserId": data.belongGroupId,
"belongUserName": user.lastName + user.firstName,
"belongGroupId": data.belongGroupId,
"spaceId": data.spaceId,
"spaceName": space.name,
"positionId": data.positionId,
"positionName": position.name,
"goodsId": data.goodsId,
"isPublic": data.isPublic,
"createdAt": util.get_iso8601_from_dt(data.createdAt),
"updatedAt": util.get_iso8601_from_dt(data.updatedAt),
})
return jsonify({"results": results})