112 lines
3.4 KiB
Python
112 lines
3.4 KiB
Python
from sqlalchemy import select, func
|
|
from sqlalchemy.ext.asyncio import AsyncSession
|
|
|
|
from app.models.user_oauth_account import UserOAuthAccount
|
|
from app.models.user_oauth import UserOAuth
|
|
|
|
|
|
async def get_oauth_account_list(
|
|
db: AsyncSession,
|
|
page: int = 1,
|
|
page_size: int = 20,
|
|
advertiser_id: str | None = None,
|
|
oauth_id: str | None = None,
|
|
advertiser_name: str | None = None,
|
|
user_id: str | None = None,
|
|
) -> dict:
|
|
# 构建连表查询
|
|
query = select(
|
|
UserOAuthAccount.id,
|
|
UserOAuthAccount.advertiser_id,
|
|
UserOAuthAccount.advertiser_name,
|
|
UserOAuthAccount.advertiser_role,
|
|
UserOAuthAccount.oauth_id,
|
|
UserOAuthAccount.created_at,
|
|
UserOAuth.account_id,
|
|
UserOAuth.account_name,
|
|
UserOAuth.account_role,
|
|
UserOAuth.account_username,
|
|
UserOAuth.account_userid,
|
|
UserOAuth.open_type,
|
|
).join(
|
|
UserOAuth,
|
|
UserOAuth.id == UserOAuthAccount.oauth_id
|
|
).where(
|
|
UserOAuth.deleted_at.is_(None),
|
|
UserOAuthAccount.deleted_at.is_(None),
|
|
UserOAuth.user_id == user_id, # 过滤当前登录用户
|
|
)
|
|
|
|
# 添加筛选条件
|
|
if advertiser_id:
|
|
query = query.where(UserOAuthAccount.advertiser_id == advertiser_id)
|
|
if oauth_id:
|
|
query = query.where(UserOAuthAccount.oauth_id == oauth_id)
|
|
if advertiser_name:
|
|
query = query.where(UserOAuthAccount.advertiser_name.like(f"%{advertiser_name}%"))
|
|
|
|
# 查询总数
|
|
count_query = select(func.count()).select_from(query.subquery())
|
|
total_result = await db.execute(count_query)
|
|
total = total_result.scalar() or 0
|
|
|
|
# 查询分页数据
|
|
offset = (page - 1) * page_size
|
|
query = query.offset(offset).limit(page_size).order_by(UserOAuthAccount.created_at.desc())
|
|
|
|
result = await db.execute(query)
|
|
accounts = result.all()
|
|
|
|
total_pages = (total + page_size - 1) // page_size if total > 0 else 0
|
|
# 转换为字典列表（或 Pydantic 实例列表）
|
|
data = [
|
|
{
|
|
"id": row.id,
|
|
"advertiser_id": row.advertiser_id,
|
|
"advertiser_name": row.advertiser_name,
|
|
"advertiser_role": row.advertiser_role,
|
|
"oauth_id": row.oauth_id,
|
|
"account_id": row.account_id,
|
|
"account_name": row.account_name,
|
|
"account_role": row.account_role,
|
|
"account_username": row.account_username,
|
|
"account_userid": row.account_userid,
|
|
"open_type": row.open_type,
|
|
"created_at": row.created_at,
|
|
}
|
|
for row in accounts
|
|
]
|
|
return {
|
|
"data": data,
|
|
"page": page,
|
|
"page_size": page_size,
|
|
"total": total,
|
|
"total_pages": total_pages,
|
|
}
|
|
|
|
|
|
async def delete_oauth_account(
|
|
db: AsyncSession,
|
|
account_id: str,
|
|
user_id: str,
|
|
) -> bool:
|
|
result = await db.execute(
|
|
select(UserOAuthAccount).join(
|
|
UserOAuth,
|
|
UserOAuth.id == UserOAuthAccount.oauth_id
|
|
).where(
|
|
UserOAuthAccount.id == account_id,
|
|
UserOAuthAccount.deleted_at.is_(None),
|
|
UserOAuth.user_id == user_id, # 过滤当前登录用户
|
|
)
|
|
)
|
|
account = result.scalar_one_or_none()
|
|
if not account:
|
|
raise ValueError("授权账户不存在")
|
|
|
|
# 软删除
|
|
account.deleted_at = func.now()
|
|
await db.commit()
|
|
|
|
return True
|