def query_by_project_v2(cls, pid):
user_list = [
item.user_id
for item in UserBindProject.query.filter_by(project_id=pid).all()
]
user_info_row = User.query.add_columns(
User.id.label('userid'), User.name.label('username'),
User.nickname.label('nickname'), User.email.label('email'),
User.telephone.label('telephone'), User.picture.label('picture'),
User.weight.label('userweight')).filter(
User.id.in_(user_list)).all()
keys = ['userid', 'nickname', 'picture', 'username', 'userweight']
user_info_list = row2list(user_info_row, keys)
role_row = Role.query.filter(Role.status != Role.DISABLE).all()
role_keys = ['id', 'name', 'comment']
role_list = row2list(role_row, role_keys)
role_dict = {}
for role in role_list:
role_dict[role['id']] = role
for user in user_info_list:
user_role_info = []
role_list = [
item.role_id for item in UserBindRole.query.filter_by(
user_id=user['userid'], project_id=pid).all()
]
for role_id in role_list:
if role_id:
if role_id in role_dict.keys() and role_dict.get(role_id):
role_info = role_dict.get(role_id)
role_info['project_id'] = pid
user_role_info.append(role_info)
user['role'] = user_role_info
return user_info_list
def get_all_message_by_user_id(user_id, page_size, page_index):
total = _get_message_query_by_user_id(user_id).count()
messages = _get_message_query_by_user_id(user_id).limit(
int(page_size)).offset((int(page_index) - 1) * int(page_size)).all()
keys = ['id', 'content', 'status', 'create_time']
data = row2list(messages, keys)
return 0, data, total
def get_guest_info():
guest_query = Guest.query.add_columns(Guest.ip, Guest.platform,
Guest.browser, Guest.string,
Guest.version, Guest.count).all()
data = row2list(guest_query)
total = len(data)
return 0, data, total
def get_all_unread_message_by_user_id(user_id, page_size, page_index):
total = _get_message_query_by_user_id(user_id).filter(
Message.status == Message.UNREAD).count()
messages = _get_message_query_by_user_id(user_id).filter(
Message.status == Message.UNREAD).limit(int(page_size)).offset(
(int(page_index) - 1) * int(page_size)).all()
data = row2list(messages)
return 0, data, total
def get_statistics_route_db():
query = RouteStatistics.query.add_columns(
RouteStatistics.route,
RouteStatistics.service,
RouteStatistics.method,
RouteStatistics.count,
).all()
data = row2list(query)
return 0, data
def get_5_message_by_user_id(user_id):
"""
获取一个用户10最近10信息，不论已读或者未读
:param user_id: 用户
:return: ['id', 'content', 'status', 'create_time']
"""
# ret_user = user_trpc.requests('get', '/test')
# if not ret_user:
# raise Exception("Error:cls.user_trpc.requests('get', '/allflow')")
messages = _get_message_query_by_user_id(user_id).filter(
Message.status == Message.UNREAD).limit(5).all()
data = row2list(messages)
if len(data) < 5:
rows = 5 - len(data)
rows_messages = _get_message_query_by_user_id(user_id).filter(
Message.status != Message.UNREAD).limit(rows).all()
rows_data = row2list(rows_messages)
if rows_data:
data.extend(rows_data)
total = len(data)
return 0, data, total
def get_guest_info(page_size, page_index):
guest_query = Guest.query.add_columns(
Guest.ip,
Guest.platform,
Guest.browser,
Guest.string,
Guest.version,
Guest.count
)
total = guest_query.count()
guest_info = guest_query.order_by(
desc(Guest.id)).limit(int(page_size)).offset(int(page_index - 1) * int(page_size)).all()
data = row2list(guest_info)
return 0, data, total
def get_all_message_by_user_id(user_id, page_size, page_index):
total = _get_message_query_by_user_id(user_id).count()
messages = _get_message_query_by_user_id(user_id).limit(
int(page_size)).offset((int(page_index) - 1) * int(page_size)).all()
data = row2list(messages)
return 0, data, total