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 []