380 lines
15 KiB
Python
380 lines
15 KiB
Python
from typing import Optional, List, Tuple
|
|
from datetime import datetime
|
|
|
|
from sqlalchemy import select, func
|
|
from sqlalchemy.ext.asyncio import AsyncSession
|
|
|
|
from app.models.user import User
|
|
from app.models.user_oauth import UserOAuth
|
|
from app.models.user_oauth_account import UserOAuthAccount
|
|
from app.models.resources_material import ResourcesMaterial
|
|
from app.models.pre_test_template import PreTestTemplate
|
|
from app.models.upload_task import UploadTask
|
|
from app.utils.datetime_util import BEIJING_TZ, datetime_to_db_tz_str, db_tz_str_to_datetime, parse_date_range
|
|
|
|
|
|
async def get_oauth_list(
|
|
id: Optional[str] = None,
|
|
db: AsyncSession = None,
|
|
phone: Optional[str] = None,
|
|
account_id: Optional[str] = None,
|
|
account_userid: Optional[str] = None,
|
|
created_at: Optional[datetime] = None,
|
|
appid: Optional[str] = None,
|
|
open_type: Optional[int] = None,
|
|
page: int = 1,
|
|
page_size: int = 10,
|
|
) -> Tuple[List[UserOAuth], int]:
|
|
offset = (page - 1) * page_size
|
|
|
|
query = select(UserOAuth).where(UserOAuth.deleted_at.is_(None))
|
|
if id:
|
|
query = query.where(UserOAuth.id == id)
|
|
if phone:
|
|
query = query.join(User, User.id == UserOAuth.user_id).where(User.phone == phone)
|
|
if account_id:
|
|
query = query.where(UserOAuth.account_id == account_id)
|
|
if account_userid:
|
|
query = query.where(UserOAuth.account_userid == account_userid)
|
|
# 【改动3】适配入参 created_at=["2025-01-01","2026-01-01"] 字符串日期场景
|
|
if created_at and len(created_at) == 2:
|
|
try:
|
|
start_dt, end_dt = parse_date_range(created_at, is_timezone=True)
|
|
# 直接用datetime范围匹配，不走func.date，保留时分秒精度，命中索引
|
|
query = query.where(
|
|
UserOAuth.created_at >= start_dt,
|
|
UserOAuth.created_at <= end_dt
|
|
)
|
|
except ValueError:
|
|
# 捕获日期格式错误，非法时间直接不附加该查询条件
|
|
pass
|
|
if appid:
|
|
query = query.where(UserOAuth.appid == appid)
|
|
if open_type:
|
|
query = query.where(UserOAuth.open_type == open_type)
|
|
|
|
count_query = select(func.count(UserOAuth.id)).where(UserOAuth.deleted_at.is_(None))
|
|
|
|
if phone:
|
|
count_query = count_query.join(User, User.id == UserOAuth.user_id).where(User.phone == phone)
|
|
if account_id:
|
|
count_query = count_query.where(UserOAuth.account_id == account_id)
|
|
if account_userid:
|
|
count_query = count_query.where(UserOAuth.account_userid == account_userid)
|
|
if created_at:
|
|
try:
|
|
start_dt, end_dt = parse_date_range(created_at, is_timezone=True)
|
|
# 直接用datetime范围匹配，不走func.date，保留时分秒精度，命中索引
|
|
count_query = count_query.where(
|
|
UserOAuth.created_at >= start_dt,
|
|
UserOAuth.created_at <= end_dt
|
|
)
|
|
except ValueError:
|
|
# 捕获日期格式错误，非法时间直接不附加该查询条件
|
|
pass
|
|
if appid:
|
|
count_query = count_query.where(UserOAuth.appid == appid)
|
|
if open_type:
|
|
count_query = count_query.where(UserOAuth.open_type == open_type)
|
|
|
|
total_result = await db.execute(count_query)
|
|
total = total_result.scalar_one()
|
|
|
|
query = query.order_by(UserOAuth.id.desc()).offset(offset).limit(page_size)
|
|
result = await db.execute(query)
|
|
list = [];
|
|
for oauth in result.scalars().all():
|
|
user_query = select(User).where(User.id == oauth.user_id)
|
|
user_result = await db.execute(user_query)
|
|
user = user_result.scalar_one()
|
|
if user:
|
|
phone = user.phone
|
|
else:
|
|
phone = None
|
|
item = {
|
|
"id": oauth.id,
|
|
"account_id": oauth.account_id,
|
|
"account_name": oauth.account_name,
|
|
"account_role": oauth.account_role,
|
|
"account_username": oauth.account_username,
|
|
"account_userid": oauth.account_userid,
|
|
"user_id": oauth.user_id,
|
|
"user_phone": phone,
|
|
"open_type": oauth.open_type,
|
|
"port_type": oauth.port_type,
|
|
"appid": oauth.appid,
|
|
"access_token": oauth.access_token,
|
|
"refresh_token": oauth.refresh_token,
|
|
"access_token_expired": oauth.access_token_expired,
|
|
"refresh_token_expired": oauth.refresh_token_expired,
|
|
"created_at": oauth.created_at,
|
|
"updated_at": oauth.updated_at,
|
|
}
|
|
list.append(item)
|
|
return list, total
|
|
|
|
|
|
async def get_material_list(
|
|
db: AsyncSession,
|
|
phone: Optional[str] = None,
|
|
id: Optional[str] = None,
|
|
resource_type: Optional[str] = None,
|
|
advertiser_id: Optional[str] = None,
|
|
material_id: Optional[str] = None,
|
|
upload_id: Optional[str] = None,
|
|
created_at: Optional[datetime] = None,
|
|
status: Optional[str] = None,
|
|
page: int = 1,
|
|
page_size: int = 20,
|
|
) -> Tuple[List[ResourcesMaterial], int]:
|
|
offset = (page - 1) * page_size
|
|
|
|
query = select(ResourcesMaterial).where(ResourcesMaterial.deleted_at.is_(None))
|
|
|
|
if phone:
|
|
query = query.join(UserOAuth, UserOAuth.id == ResourcesMaterial.oauth_id).join(User, User.id == UserOAuth.user_id).where(User.phone == phone)
|
|
if id:
|
|
query = query.where(ResourcesMaterial.id == id)
|
|
if resource_type:
|
|
query = query.where(ResourcesMaterial.resource_type == resource_type)
|
|
if advertiser_id:
|
|
query = query.where(ResourcesMaterial.advertiser_id == advertiser_id)
|
|
if material_id:
|
|
query = query.where(ResourcesMaterial.material_id == material_id)
|
|
if upload_id:
|
|
query = query.where(ResourcesMaterial.upload_id == upload_id)
|
|
if created_at:
|
|
start_dt, end_dt = parse_date_range(created_at, is_timezone=True)
|
|
# 直接用datetime范围匹配，不走func.date，保留时分秒精度，命中索引
|
|
query = query.where(
|
|
ResourcesMaterial.created_at >= start_dt,
|
|
ResourcesMaterial.created_at <= end_dt
|
|
)
|
|
if status:
|
|
query = query.where(ResourcesMaterial.status == status)
|
|
|
|
count_query = select(func.count(ResourcesMaterial.id)).where(ResourcesMaterial.deleted_at.is_(None))
|
|
|
|
if phone:
|
|
count_query = count_query.join(UserOAuth, UserOAuth.id == ResourcesMaterial.oauth_id).join(User, User.id == UserOAuth.user_id).where(User.phone == phone)
|
|
if id:
|
|
count_query = count_query.where(ResourcesMaterial.id == id)
|
|
if resource_type:
|
|
count_query = count_query.where(ResourcesMaterial.resource_type == resource_type)
|
|
if advertiser_id:
|
|
count_query = count_query.where(ResourcesMaterial.advertiser_id == advertiser_id)
|
|
if material_id:
|
|
count_query = count_query.where(ResourcesMaterial.material_id == material_id)
|
|
if upload_id:
|
|
count_query = count_query.where(ResourcesMaterial.upload_id == upload_id)
|
|
if created_at:
|
|
start_dt, end_dt = parse_date_range(created_at, is_timezone=True)
|
|
# 直接用datetime范围匹配，不走func.date，保留时分秒精度，命中索引
|
|
count_query = count_query.where(
|
|
ResourcesMaterial.created_at >= start_dt,
|
|
ResourcesMaterial.created_at <= end_dt
|
|
)
|
|
if status:
|
|
count_query = count_query.where(ResourcesMaterial.status == status)
|
|
|
|
total_result = await db.execute(count_query)
|
|
total = total_result.scalar_one()
|
|
|
|
query = query.order_by(ResourcesMaterial.id.desc()).offset(offset).limit(page_size)
|
|
result = await db.execute(query)
|
|
list = []
|
|
for item in result.scalars().all():
|
|
user_query = select(User).where(User.id == item.user_id)
|
|
user_result = await db.execute(user_query)
|
|
user = user_result.scalar_one()
|
|
if user:
|
|
phone = user.phone
|
|
else:
|
|
phone = None
|
|
item = {
|
|
"id": item.id,
|
|
"oauth_id": item.oauth_id,
|
|
"user_phone": phone,
|
|
"advertiser_id": item.advertiser_id,
|
|
"target_table": item.target_table,
|
|
"target_id": item.target_id,
|
|
"material_id": item.material_id,
|
|
"upload_id": item.upload_id,
|
|
"resource_type": item.resource_type,
|
|
"user_id": item.user_id,
|
|
"created_at": item.created_at,
|
|
"updated_at": item.updated_at,
|
|
"task_id": item.task_id,
|
|
"note": item.note,
|
|
"status": item.status,
|
|
"pre_result": item.pre_result,
|
|
"pre_test_template_id": item.pre_test_template_id,
|
|
}
|
|
list.append(item)
|
|
|
|
return list, total
|
|
|
|
|
|
async def get_pre_test_template_list(
|
|
db: AsyncSession,
|
|
phone: Optional[str] = None,
|
|
id: Optional[str] = None,
|
|
created_at: Optional[datetime] = None,
|
|
page: int = 1,
|
|
page_size: int = 10,
|
|
) -> Tuple[List[PreTestTemplate], int]:
|
|
offset = (page - 1) * page_size
|
|
|
|
query = select(PreTestTemplate).where(PreTestTemplate.deleted_at.is_(None))
|
|
|
|
if phone:
|
|
query = query.join(User, User.id == PreTestTemplate.user_id).where(User.phone == phone)
|
|
if id:
|
|
query = query.where(PreTestTemplate.id == id)
|
|
if created_at:
|
|
start_dt, end_dt = parse_date_range(created_at, is_timezone=True)
|
|
# 直接用datetime范围匹配，不走func.date，保留时分秒精度，命中索引
|
|
query = query.where(
|
|
PreTestTemplate.created_at >= start_dt,
|
|
PreTestTemplate.created_at <= end_dt
|
|
)
|
|
|
|
count_query = select(func.count(PreTestTemplate.id)).where(PreTestTemplate.deleted_at.is_(None))
|
|
|
|
if phone:
|
|
count_query = count_query.join(User, User.id == PreTestTemplate.user_id).where(User.phone == phone)
|
|
if id:
|
|
count_query = count_query.where(PreTestTemplate.id == id)
|
|
if created_at:
|
|
# 直接用datetime范围匹配，不走func.date，保留时分秒精度，命中索引
|
|
start_dt, end_dt = parse_date_range(created_at, is_timezone=True)
|
|
count_query = count_query.where(
|
|
PreTestTemplate.created_at >= start_dt,
|
|
PreTestTemplate.created_at <= end_dt
|
|
)
|
|
|
|
total_result = await db.execute(count_query)
|
|
total = total_result.scalar_one()
|
|
|
|
query = query.order_by(PreTestTemplate.id.desc()).offset(offset).limit(page_size)
|
|
result = await db.execute(query)
|
|
list = []
|
|
for item in result.scalars().all():
|
|
user_query = select(User).where(User.id == item.user_id)
|
|
user_result = await db.execute(user_query)
|
|
user = user_result.scalar_one()
|
|
if user:
|
|
phone = user.phone
|
|
else:
|
|
phone = None
|
|
item = {
|
|
"id": item.id,
|
|
"name": item.name,
|
|
"user_phone": phone,
|
|
"user_id": item.user_id,
|
|
"platform": item.platform,
|
|
"external_action": item.external_action,
|
|
"cpa_bid": item.cpa_bid,
|
|
"audience_gender": item.audience_gender,
|
|
"audience_age": item.audience_age,
|
|
"audience_region": item.audience_region,
|
|
"audience_network": item.audience_network,
|
|
"cus_name": item.cus_name,
|
|
"pricing_type": item.pricing_type,
|
|
"cost_cap": item.cost_cap,
|
|
"target_cost": item.target_cost,
|
|
"nobid": item.nobid,
|
|
"cpc_bid": item.cpc_bid,
|
|
"budget": item.budget,
|
|
"is_default": item.is_default,
|
|
"created_at": item.created_at,
|
|
"updated_at": item.updated_at,
|
|
"note": item.note,
|
|
}
|
|
list.append(item)
|
|
return list, total
|
|
|
|
|
|
async def get_upload_task_list(
|
|
db: AsyncSession,
|
|
phone: Optional[str] = None,
|
|
id: Optional[str] = None,
|
|
created_at: Optional[List[str]] = None,
|
|
advertiser_id: Optional[str] = None,
|
|
status: Optional[int] = None,
|
|
resource_id: Optional[str] = None,
|
|
page: int = 1,
|
|
page_size: int = 10,
|
|
) -> Tuple[List[UploadTask], int]:
|
|
offset = (page - 1) * page_size
|
|
|
|
query = select(UploadTask).where(UploadTask.deleted_at.is_(None))
|
|
|
|
if phone:
|
|
query = query.join(User, User.id == UploadTask.user_id).where(User.phone == phone)
|
|
if id:
|
|
query = query.where(UploadTask.id == id)
|
|
if created_at and len(created_at) >= 2:
|
|
# 直接用datetime范围匹配，不走func.date，保留时分秒精度，命中索引
|
|
start_dt, end_dt = parse_date_range(created_at, is_timezone=True)
|
|
query = query.where(
|
|
UploadTask.created_at >= start_dt,
|
|
UploadTask.created_at <= end_dt
|
|
)
|
|
if advertiser_id:
|
|
query = query.where(UploadTask.advertiser_id == advertiser_id)
|
|
if status:
|
|
query = query.where(UploadTask.status == status)
|
|
if resource_id:
|
|
query = query.where(UploadTask.resource_id == resource_id)
|
|
|
|
count_query = select(func.count(UploadTask.id)).where(UploadTask.deleted_at.is_(None))
|
|
|
|
if phone:
|
|
count_query = count_query.join(User, User.id == UploadTask.user_id).where(User.phone == phone)
|
|
if id:
|
|
count_query = count_query.where(UploadTask.id == id)
|
|
if created_at and len(created_at) >= 2:
|
|
# 直接用datetime范围匹配，不走func.date，保留时分秒精度，命中索引
|
|
start_dt, end_dt = parse_date_range(created_at, is_timezone=True)
|
|
count_query = count_query.where(
|
|
UploadTask.created_at >= start_dt,
|
|
UploadTask.created_at <= end_dt
|
|
)
|
|
if advertiser_id:
|
|
count_query = count_query.where(UploadTask.advertiser_id == advertiser_id)
|
|
if status:
|
|
count_query = count_query.where(UploadTask.status == status)
|
|
if resource_id:
|
|
count_query = count_query.where(UploadTask.resource_id == resource_id)
|
|
|
|
total_result = await db.execute(count_query)
|
|
total = total_result.scalar_one()
|
|
|
|
query = query.order_by(UploadTask.id.desc()).offset(offset).limit(page_size)
|
|
result = await db.execute(query)
|
|
list = []
|
|
for item in result.scalars().all():
|
|
user_query = select(User).where(User.id == item.user_id)
|
|
user_result = await db.execute(user_query)
|
|
user = user_result.scalar_one()
|
|
if user:
|
|
phone = user.phone
|
|
else:
|
|
phone = None
|
|
item = {
|
|
"id": item.id,
|
|
"user_phone": phone,
|
|
"user_id": item.user_id,
|
|
"advertiser_id": item.advertiser_id,
|
|
"resource_id": item.resource_id,
|
|
"status": item.status,
|
|
"note": item.note,
|
|
"oauth_id": item.oauth_id,
|
|
"created_at": item.created_at,
|
|
"updated_at": item.updated_at,
|
|
"other_info": item.other_info,
|
|
}
|
|
list.append(item)
|
|
return list, total
|