async def launch_history_detail(request, **kwargs):
user_id = kwargs['user']['user_id']
task_id = request.raw_args.get('task_id')
logger.debug(
f'get launch history detail, user_id:{user_id}, task_id:{task_id}')
async with db.conn.acquire() as con:
group_task_stmt = await con.prepare(
'''select group_ids, content, to_char(send_date, 'YYYY-MM-DD HH24:MI:SS') as send_date from "group_task" where id = $1'''
)
group_task_res = await group_task_stmt.fetchrow(task_id)
group_ids = ujson.loads(group_task_res.get('group_ids'))
content = ujson.loads(group_task_res.get('content'))
for task_item in content:
if task_item.get('type') == 4:
file_path = task_item.get('filePath')
task_item.update({'filePath': file_path})
send_date = group_task_res.get('send_date')
get_group_stmt = await con.prepare(
'select name from "group" where id = any($1)')
group_names = records_to_value_list(
await get_group_stmt.fetch(group_ids), 'name')
resp_dict = {
'groups': group_names,
'materials': content,
'send_date': send_date
}
return response_json(resp_dict)
async def two_star_extra_limit_groups(limit_size):
"""获取要额外注销的二星群列表"""
async with db.conn.acquire() as con:
groups_stmt = await con.prepare(
'''select id from "group" where status <> 3 and quality_level = 2 order by create_date asc limit $1'''
)
return records_to_value_list(await groups_stmt.fetch(limit_size),
'id')
async def launch_switch_off_groups():
"""投放开关关闭的一星群"""
async with db.conn.acquire() as con:
launch_switch_off_stmt = await con.prepare(
'''select id from "group" where status <> 3 and quality_level = 1 and launch_switch = 0 order by create_date asc'''
)
return records_to_value_list(await launch_switch_off_stmt.fetch(),
'id')
async def one_star_mem_count_limit_groups():
"""群人数小于一定人数"""
async with db.conn.acquire() as con:
mem_count_limit_groups_stmt = await con.prepare(
'''select id from "group" where status <> 3 and mem_count > 0 and mem_count <= $1 and (quality_level is null or quality_level = 1) order by create_date asc'''
)
return records_to_value_list(
await mem_count_limit_groups_stmt.fetch(
settings['ONE_STAR_MEM_COUNT_LIMIT']), 'id')
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 illegal_groups():
"""违法群"""
async with db.conn.acquire() as con:
illegal_groups_stmt = await con.prepare(
'''select id::varchar from "group" where status <> 3 and name similar to $1'''
)
return records_to_value_list(
await
illegal_groups_stmt.fetch(settings['ILLEGAL_GROUP_NAME']),
'id')
async def sync_robot_info():
logger.info(f'sync robot info start!')
async with db.conn.acquire() as con:
get_robot_stmt = await con.prepare(
'''select code from "robot" where status <> 3''')
robots = await get_robot_stmt.fetch()
robot_codes = records_to_value_list(robots, 'code')
logger.info(f'sync robot count:{len(robot_codes)}')
loop = asyncio.get_event_loop()
asyncio.run_coroutine_threadsafe(sync_robot(robot_codes), loop)
logger.info(f'sync robot end!')
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 no_click_groups():
"""无点击群"""
redis_key = NO_CLICK_GROUPS_REDIS_KEY.format(today=today())
group_codes_value = await redis.conn.get(redis_key)
if group_codes_value:
group_codes = ujson.loads(group_codes_value)
async with db.conn.acquire() as con:
get_group_stmt = await con.prepare(
'''select id from "group" where status <> 3 and quality_level = 1 and code = any($1) order by create_date asc'''
)
return records_to_value_list(
await get_group_stmt.fetch(group_codes), 'id')
else:
return []
