async def user_owner_group_info(user_id, page_size, current_page):
"""查询用户群主群信息"""
async with db.conn.acquire() as con:
if page_size == -1:
st = await con.prepare('''
select "group".id::varchar , "group".code, "group".name, launch_switch,mem_count, welcome_msg,
to_char("group".create_date, 'YYYY-MM-DD HH24:MI:SS') as create_date from "group" left join "user" on
"group".user_id = "user".id where user_id = $1 and "group".owner_user_code = "user".code
and "group".status !=3 order by create_date desc''')
groups = records_to_list(await st.fetch(user_id))
else:
st = await con.prepare('''
select "group".id::varchar , "group".code, "group".name,mem_count, welcome_msg, launch_switch,
to_char("group".create_date, 'YYYY-MM-DD HH24:MI:SS') as create_date from "group" left join "user" on
"group".user_id = "user".id where user_id = $1 and owner_user_code = "user".code and "group".status !=3
order by create_date desc limit $2 offset $3''')
groups = records_to_list(await st.fetch(
user_id, int(page_size),
int(current_page) * int(page_size)))
select_count = await con.prepare(
'''select count(1) from "group" left join "user" on "group".user_id="user".id
where user_id = $1 and owner_user_code ="user".code and "group".status !=3'''
)
total_records = await select_count.fetchval(user_id)
page_info = get_page_info(page_size, current_page, total_records)
return groups, page_info
async def get_group_list(request, user, **kwargs):
"""获取用户的群列表"""
user_id = user.get('user_id')
status = request.raw_args.get('status')
current_page = int(request.raw_args.get('current_page', 0))
page_size = int(request.raw_args.get('page_size', 20))
user_groups = []
total_records = 0
async with db.conn.acquire() as con:
if int(status) == group_status['normal']:
select_stmt = await con.prepare('''
select "group".code, "group".launch_switch, "group".settle_count, "group".quality_level, "group".old_group,
robot_group_map.nickname,"group".name , "robot".name as robot_name from "group" left join robot_group_map on
"group".id = "robot_group_map".group_id left join robot on robot.id = "robot_group_map".robot_id where
"group".user_id = $1 and "group".status<>3 and "robot_group_map".status <>3 and "robot".status<>3 and
(quality_level is not null or old_group = 1) order by "group".create_date desc limit $2 offset $3'''
)
user_groups = records_to_list(await select_stmt.fetch(
user_id, int(page_size),
int(current_page) * int(page_size)))
select_count = await con.prepare(
'''select count(1) from "group" left join robot_group_map on
"group".id = robot_group_map.group_id left join robot on robot.id = robot_group_map.robot_id
where "group".user_id = $1 and "group".status<>3 and "robot_group_map".status <>3 and "robot".status<>3
and (quality_level is not null or old_group = 1)''')
total_records = await select_count.fetchval(user_id)
elif int(status) == group_status['error']:
select_stmt = await con.prepare('''
select "group".code , "group".name, to_char("group".create_date, 'YYYY-MM-DD') as create_date from "group" where "group".user_id = $1 and "group".status != 3 and old_group != 1
and quality_level is null order by "group".create_date desc limit $2 offset $3'''
)
user_groups = records_to_list(await select_stmt.fetch(
user_id, int(page_size),
int(current_page) * int(page_size)))
select_count = await con.prepare('''select count(1) from "group"
where user_id = $1 and status != 3 and quality_level is null and old_group != 1'''
)
total_records = await select_count.fetchval(user_id)
page_info = get_page_info(page_size, current_page, total_records)
if len(user_groups) > 0 and int(status) == group_status['normal']:
for item in user_groups:
if not item['old_group']:
if item['settle_count'] >= 30 and item['launch_switch'] == 0:
income_status = '未收益'
else:
income_status = '收益中'
item['income_status'] = income_status
item['group_income'] = group_income[item['quality_level']]
item['text_income'] = click_income
item['robot_name'] = item['nickname'] if item[
'nickname'] else item['robot_name']
item.pop('nickname')
return response_json(user_groups, page_info=page_info)
async def withdraw_list_admin(request, *args, **kwargs):
"""提现列表"""
current_page = int(request.raw_args.get('current_page', 0))
page_size = int(request.raw_args.get('page_size', 20))
params = request.json
sql, sql_count = await withdraw_sql(params)
sql = sql + ''' limit ''' + str(page_size) + ''' offset ''' + str(
current_page * page_size)
async with db.conn.acquire() as con:
select_result = records_to_list(await con.fetch(sql))
select_count = await con.fetchval(sql_count)
page_info = get_page_info(page_size, current_page, select_count)
return response_json(select_result, page_info=page_info)
async def launch_history(request, **kwargs):
user_id = kwargs['user']['user_id']
current_page = int(request.raw_args.get('current_page'))
page_size = int(request.raw_args.get('page_size'))
logger.debug(f'launch history req, user_id:{user_id}')
async with db.conn.acquire() as con:
user_task_stmt = await con.prepare(
'''select id as task_id, to_char(send_date, 'YYYY-MM-DD HH24:MI:SS') as send_date, jsonb_array_length(group_ids)::varchar as group_count, status as send_flag from "group_task"
where user_id = $1 and type = 2 and status =1 and create_date >= (current_date - 2)
order by create_date desc limit $2 offset $3''')
user_tasks = await user_task_stmt.fetch(user_id, page_size,
current_page * page_size)
task_list = records_to_list(user_tasks)
history_count_stmt = await con.prepare(
'''select count(1) from "group_task" where user_id = $1 and type = 2 and status = 1 and create_date >= (current_date - 2)'''
)
total_records = await history_count_stmt.fetchval(user_id)
page_info = get_page_info(page_size, current_page, total_records)
return response_json(task_list, page_info=page_info)
async def problem_feedback(request, *args, **kwargs):
current_page = int(request.raw_args.get('current_page', 0))
page_size = int(request.raw_args.get('page_size', 20))
params = request.json
sql, sql_count = await problem_sql(params)
sql = sql + ''' limit ''' + str(page_size) + ''' offset ''' + str(
current_page * page_size)
async with db.conn.acquire() as con:
select_result = records_to_list(await con.fetch(sql))
select_count = await con.fetchval(sql_count)
for result in select_result:
if result['code'] is not None:
result['robot_info'] = result['name'] + '(' + result[
'wechat_no'] + ')'
page_info = get_page_info(page_size, current_page, select_count)
return response_json(select_result, page_info=page_info)
async def get_alt_msg(request, *args, **kwargs):
"""获取@用户机器人信息"""
user_id = kwargs['user']['user_id']
current_page = int(request.raw_args.get('current_page', 0))
page_size = int(request.raw_args.get('page_size', 20))
async with db.conn.acquire() as con:
select_altmsg = await con.prepare(
'''select "alt_msg".msg_id as msg_id, "group".name as group_name, "alt_msg".msg_content as msg_content,
"group".code as group_code, "alt_msg".status as status from "alt_msg" left join "group" on
"group".id="alt_msg".group_id where "group".status !=3 and "alt_msg".user_id = $1 and
"alt_msg".create_date >=(current_date - interval '3' day) order by "alt_msg".create_date desc
limit $2 offset $3''')
msg_infos = records_to_list(await select_altmsg.fetch(
user_id, page_size, current_page * page_size))
select_count = await con.prepare(
'''select count(1) from "alt_msg" left join "group" on "group".id="alt_msg".group_id
where "group".status !=3 and "alt_msg".user_id = $1 and "alt_msg".create_date >=(current_date - interval '3' day)'''
)
total_records = await select_count.fetchval(user_id)
page_info = get_page_info(page_size, current_page, total_records)
return response_json(msg_infos, page_info=page_info)
async def user_apprentice(user_id, current_page, page_size, leave):
"""获取用户徒弟徒孙信息"""
if user_id is None:
return None
async with db.conn.acquire() as con:
share_records = []
count = 0
if leave == APPRENTICE:
select_stmt = await con.prepare(
'''select "sharing_record".user_id::varchar , to_char("sharing_record".create_date, 'YYYY-MM-DD HH24:MI:SS')
as create_date, "user".nickname, "user".need_remind from "sharing_record" left join "user" on
"user".id = "sharing_record".user_id where "sharing_record".status != 3 and
"sharing_record".sharing_user_id = $1 order by "sharing_record".create_date desc limit $2 offset $3'''
)
share_records = records_to_list(await select_stmt.fetch(
user_id, page_size, current_page * page_size))
select_count = await con.prepare(
'''select count(1) from "sharing_record"
where status != 3 and sharing_user_id = $1''')
count = await select_count.fetchval(user_id)
elif leave == GRANDSON:
select_stmt = await con.prepare(
'''with t as (select user_id from "sharing_record" where status != 3 and sharing_user_id = $1 )
select "sharing_record".user_id::varchar , to_char("sharing_record".create_date, 'YYYY-MM-DD HH24:MI:SS')
as create_date, "user".nickname, "user".need_remind from t,"sharing_record" left join "user" on "user".id = "sharing_record".user_id
where "sharing_record".status != 3 and "sharing_record".sharing_user_id = t.user_id
order by "sharing_record".create_date desc limit $2 offset $3'''
)
share_records = records_to_list(await select_stmt.fetch(
user_id, page_size, current_page * page_size))
select_count = await con.prepare(
'''select count(1) from "sharing_record"
where status != 3 and sharing_user_id in (select user_id from "sharing_record" where status != 3 and
sharing_user_id = $1)''')
count = await select_count.fetchval(user_id)
result = {
'infos': await disciple_result_params(share_records),
'page_info': get_page_info(page_size, current_page, count)
}
return result
async def mass_message_task_view(request, user):
'''群发消息页面'''
async def mass_message_data(direction, current_page, page_size):
async with db.conn.acquire() as con:
if direction == 'up':
st = await con.prepare(
'''select to_char(send_date, 'YYYY-MM-DD HH24:MI:SS') as send_date,
jsonb_array_length(group_ids)::int as group_counts, status, id
from "group_task" where user_id=$1 and status <> $2 and type=1
and send_date >= timestamp 'today' and send_date <= (current_date + interval '3' day)
order by send_date desc limit $3 offset $4''')
st_count = await con.prepare(
'''select count(1) from "group_task" where user_id=$1 and status <> $2 and type=1
and send_date >= timestamp 'today' and send_date < (current_date + interval '3' day)'''
)
else:
st = await con.prepare(
'''select to_char(send_date, 'YYYY-MM-DD HH24:MI:SS') as send_date,
jsonb_array_length(group_ids)::int as group_counts, status, id
from "group_task" where user_id=$1 and status <> $2 and type=1
and send_date < timestamp 'today' and send_date >= (current_date - interval '3' day)
order by send_date desc limit $3 offset $4''')
st_count = await con.prepare(
'''select count(1) from "group_task" where user_id=$1 and status <> $2 and type=1
and send_date < timestamp 'today' and send_date >= (current_date - interval '3' day)'''
)
count = await st_count.fetchval(user['user_id'], FAIL_DELETE)
datas = await st.fetch(user['user_id'], FAIL_DELETE, page_size,
current_page * page_size)
return records_to_list(datas), count
current_page = int(request.raw_args.get('current_page', 0))
page_size = int(request.raw_args.get('page_size', 20))
direction = request.raw_args.get('direction', 'up')
datas, count = await mass_message_data(direction, current_page, page_size)
page_info = get_page_info(page_size, current_page, count)
return response_json(datas, page_info=page_info)
async def users_info(request):
'''
返回给BI的数据
{
"user_id": string, // 用户id
"maps": {
"father_id": string, // 师傅
"grand_father_id": string // 师爷
},
"groups": ["", ""] // 群id
}
}
'''
async def get_user_relationships(user_id):
async with db.conn.acquire() as con:
st = await con.prepare('''with recursive parent as (
select user_id, sharing_user_id, 0 as depth from "sharing_record" where user_id=$1
union
select psr.user_id, psr.sharing_user_id, depth - 1
from "sharing_record" psr join parent p on p.sharing_user_id=psr.user_id where p.depth > -1)
select "user".code as code, parent.depth from parent join "user" on parent.sharing_user_id="user".id;
''')
users = await st.fetch(user_id)
results = {'grand_father_id': None}
for user in users:
if user['depth'] == 0:
results.update({'father_id': user['code']})
elif user['depth'] == -1:
results.update({'grand_father_id': user['code']})
if len(results) == 1 and not results.get('grand_father_id'):
results = None
return results
async def get_user_ids(datetime, user_ids):
async with db.conn.acquire() as con:
st = await con.prepare(
'''select "user".id as user_id, "user".code as user_code, "group".code as group_code from "user"
join "group" on "user".id="group".user_id
where "user".status<>3 and "group".status <>3 and "user".create_date <= to_timestamp($1, 'YYYY-MM-DD HH24:MI:SS')
and "group".create_date <= to_timestamp($2, 'YYYY-MM-DD HH24:MI:SS') and "user".id=any($3) order by "user".id'''
)
users = await st.fetch(datetime, datetime, user_ids)
return records_to_list(users), records_to_value_list(
users, 'user_code')
async def get_user_counts(datetime):
async with db.conn.acquire() as con:
st = await con.prepare('''select "user".id from "user"
join "group" on "user".id="group".user_id where "user".status<>3
and "group".status <>3 and "user".create_date <= to_timestamp($1, 'YYYY-MM-DD HH24:MI:SS')
and "group".create_date <= to_timestamp($2, 'YYYY-MM-DD HH24:MI:SS') group by "user".id'''
)
total_users = await st.fetch(datetime, datetime)
return records_to_value_list(total_users, 'id'), len(total_users)
async def async_groupby(users, sort_key):
return groupby(users, key=itemgetter(sort_key))
async def pre_next_page_count(current_page, page_size):
return current_page * page_size, (current_page + 1) * page_size
logger.debug(f'receiver bi data: {request.json}')
datetime = request.json.get('datetime')
current_page = int(request.raw_args.get('current_page'))
page_size = int(request.raw_args.get('page_size'))
timestamp = request.json.get('timestamp')
sign = request.json.get('sign')
# 数据校验
if not all([timestamp, sign]):
return response_json(None, code=PARAMS_ERROR_CODE)
secret_md5 = md5()
secret_md5.update('#'.join([timestamp, MD5_SECRET_KEY]).encode('utf-8'))
if secret_md5.hexdigest() != sign:
return response_json(None, code=PARAMS_ERROR_CODE)
data = []
static_users, user_counts = await get_user_counts(datetime)
pre_count, next_count = await pre_next_page_count(current_page, page_size)
page_user_ids = static_users[pre_count:next_count]
users, user_codes = await get_user_ids(datetime, page_user_ids)
cache_maps = {}
if user_codes:
maps = await redis.conn.hmget(COMPLETE_USER_REDIS_KEY, user_codes)
for key, value in zip(user_codes, maps):
cache_maps[key] = value
for user_code, items in await async_groupby(users, 'user_code'):
user_maps = cache_maps.get(user_code)
copy_items = list(items)
user_id = copy_items[0]['user_id']
groups = []
for item in copy_items:
groups.append(item['group_code'])
if not user_maps:
user_maps = await get_user_relationships(user_id=user_id)
data.append({
'user_id': user_code,
'groups': groups,
'maps': user_maps
})
page_info = get_page_info(page_size, current_page, user_counts)
return response_json(data, page_info=page_info)