357 lines
11 KiB
Python
357 lines
11 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""
|
||
申请服务 —— 处理用户申请的创建、查询、审核。
|
||
|
||
职责：
|
||
- create_application()：创建申请 + site_code 映射查找
|
||
- approve_application()：批准 + 创建绑定/角色
|
||
- reject_application()：拒绝 + 记录原因
|
||
- get_user_applications()：查询用户申请列表
|
||
|
||
所有数据库操作使用 psycopg2 原生 SQL，不引入 ORM。
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
import logging
|
||
|
||
from fastapi import HTTPException, status
|
||
|
||
from app.database import get_connection
|
||
|
||
logger = logging.getLogger(__name__)
|
||
|
||
|
||
async def create_application(
|
||
user_id: int,
|
||
site_code: str,
|
||
applied_role_text: str,
|
||
phone: str,
|
||
employee_number: str | None = None,
|
||
nickname: str | None = None,
|
||
) -> dict:
|
||
"""
|
||
创建用户申请。
|
||
|
||
1. 检查是否有 pending 申请（有则 409）
|
||
2. 查找 site_code → site_id 映射
|
||
3. 插入 user_applications 记录
|
||
4. 更新 users.nickname（如提供）
|
||
|
||
返回:
|
||
申请记录 dict，包含 id / site_code / applied_role_text / status /
|
||
review_note / created_at / reviewed_at
|
||
"""
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
# 1. 检查重复 pending 申请
|
||
cur.execute(
|
||
"""
|
||
SELECT id FROM auth.user_applications
|
||
WHERE user_id = %s AND status = 'pending'
|
||
LIMIT 1
|
||
""",
|
||
(user_id,),
|
||
)
|
||
if cur.fetchone() is not None:
|
||
raise HTTPException(
|
||
status_code=status.HTTP_409_CONFLICT,
|
||
detail="已有待审核的申请，请等待审核完成",
|
||
)
|
||
|
||
# 2. 查找 site_code → site_id 映射
|
||
site_id = None
|
||
cur.execute(
|
||
"SELECT site_id FROM auth.site_code_mapping WHERE site_code = %s",
|
||
(site_code,),
|
||
)
|
||
mapping_row = cur.fetchone()
|
||
if mapping_row is not None:
|
||
site_id = mapping_row[0]
|
||
|
||
# 3. 创建申请记录
|
||
cur.execute(
|
||
"""
|
||
INSERT INTO auth.user_applications
|
||
(user_id, site_code, site_id, applied_role_text,
|
||
phone, employee_number, status)
|
||
VALUES (%s, %s, %s, %s, %s, %s, 'pending')
|
||
RETURNING id, site_code, applied_role_text, status,
|
||
review_note, created_at::text, reviewed_at::text
|
||
""",
|
||
(
|
||
user_id,
|
||
site_code,
|
||
site_id,
|
||
applied_role_text,
|
||
phone,
|
||
employee_number,
|
||
),
|
||
)
|
||
row = cur.fetchone()
|
||
|
||
# 4. 更新 nickname（如提供）
|
||
if nickname:
|
||
cur.execute(
|
||
"UPDATE auth.users SET nickname = %s, updated_at = NOW() WHERE id = %s",
|
||
(nickname, user_id),
|
||
)
|
||
|
||
# 5. 更新用户状态为 pending（new → pending）
|
||
cur.execute(
|
||
"""
|
||
UPDATE auth.users SET status = 'pending', updated_at = NOW()
|
||
WHERE id = %s AND status IN ('new', 'rejected')
|
||
""",
|
||
(user_id,),
|
||
)
|
||
|
||
conn.commit()
|
||
finally:
|
||
conn.close()
|
||
|
||
return {
|
||
"id": row[0],
|
||
"site_code": row[1],
|
||
"applied_role_text": row[2],
|
||
"status": row[3],
|
||
"review_note": row[4],
|
||
"created_at": row[5],
|
||
"reviewed_at": row[6],
|
||
}
|
||
|
||
|
||
|
||
async def approve_application(
|
||
application_id: int,
|
||
reviewer_id: int,
|
||
role_id: int,
|
||
binding: dict | None = None,
|
||
review_note: str | None = None,
|
||
) -> dict:
|
||
"""
|
||
批准申请。
|
||
|
||
1. 查询申请记录（不存在则 404）
|
||
2. 检查申请状态为 pending（否则 409）
|
||
3. 更新 user_applications.status = 'approved'
|
||
4. 创建 user_site_roles 记录
|
||
5. 创建 user_assistant_binding 记录（如有 binding）
|
||
6. 更新 users.status = 'approved'
|
||
7. 记录 reviewer_id 和 reviewed_at
|
||
|
||
参数:
|
||
application_id: 申请 ID
|
||
reviewer_id: 审核人 user_id
|
||
role_id: 分配的角色 ID
|
||
binding: 绑定信息，格式 {"assistant_id": ..., "staff_id": ..., "binding_type": ...}
|
||
review_note: 审核备注（可选）
|
||
|
||
返回:
|
||
更新后的申请记录 dict
|
||
"""
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
# 1. 查询申请记录
|
||
cur.execute(
|
||
"""
|
||
SELECT id, user_id, site_id, status
|
||
FROM auth.user_applications
|
||
WHERE id = %s
|
||
""",
|
||
(application_id,),
|
||
)
|
||
app_row = cur.fetchone()
|
||
if app_row is None:
|
||
raise HTTPException(
|
||
status_code=status.HTTP_404_NOT_FOUND,
|
||
detail="申请不存在",
|
||
)
|
||
|
||
_, app_user_id, app_site_id, app_status = app_row
|
||
|
||
# 2. 检查状态为 pending
|
||
if app_status != "pending":
|
||
raise HTTPException(
|
||
status_code=status.HTTP_409_CONFLICT,
|
||
detail=f"申请当前状态为 {app_status}，无法审核",
|
||
)
|
||
|
||
# 3. 更新申请状态为 approved
|
||
cur.execute(
|
||
"""
|
||
UPDATE auth.user_applications
|
||
SET status = 'approved',
|
||
reviewer_id = %s,
|
||
review_note = %s,
|
||
reviewed_at = NOW()
|
||
WHERE id = %s
|
||
RETURNING id, site_code, applied_role_text, status,
|
||
review_note, created_at::text, reviewed_at::text
|
||
""",
|
||
(reviewer_id, review_note, application_id),
|
||
)
|
||
updated_row = cur.fetchone()
|
||
|
||
# 4. 创建 user_site_roles 记录（如果有 site_id）
|
||
if app_site_id is not None:
|
||
cur.execute(
|
||
"""
|
||
INSERT INTO auth.user_site_roles (user_id, site_id, role_id)
|
||
VALUES (%s, %s, %s)
|
||
ON CONFLICT (user_id, site_id, role_id) DO NOTHING
|
||
""",
|
||
(app_user_id, app_site_id, role_id),
|
||
)
|
||
|
||
# 5. 创建 user_assistant_binding 记录（如有 binding 且有 site_id）
|
||
if binding and app_site_id is not None:
|
||
cur.execute(
|
||
"""
|
||
INSERT INTO auth.user_assistant_binding
|
||
(user_id, site_id, assistant_id, staff_id, binding_type)
|
||
VALUES (%s, %s, %s, %s, %s)
|
||
""",
|
||
(
|
||
app_user_id,
|
||
app_site_id,
|
||
binding.get("assistant_id"),
|
||
binding.get("staff_id"),
|
||
binding.get("binding_type", "assistant"),
|
||
),
|
||
)
|
||
|
||
# 6. 更新用户状态为 approved
|
||
cur.execute(
|
||
"""
|
||
UPDATE auth.users
|
||
SET status = 'approved', updated_at = NOW()
|
||
WHERE id = %s
|
||
""",
|
||
(app_user_id,),
|
||
)
|
||
|
||
conn.commit()
|
||
finally:
|
||
conn.close()
|
||
|
||
return {
|
||
"id": updated_row[0],
|
||
"site_code": updated_row[1],
|
||
"applied_role_text": updated_row[2],
|
||
"status": updated_row[3],
|
||
"review_note": updated_row[4],
|
||
"created_at": updated_row[5],
|
||
"reviewed_at": updated_row[6],
|
||
}
|
||
|
||
|
||
async def reject_application(
|
||
application_id: int,
|
||
reviewer_id: int,
|
||
review_note: str,
|
||
) -> dict:
|
||
"""
|
||
拒绝申请。
|
||
|
||
1. 查询申请记录（不存在则 404）
|
||
2. 检查申请状态为 pending（否则 409）
|
||
3. 更新 user_applications.status = 'rejected'
|
||
4. 记录 reviewer_id、review_note、reviewed_at
|
||
|
||
返回:
|
||
更新后的申请记录 dict
|
||
"""
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
# 1. 查询申请记录
|
||
cur.execute(
|
||
"SELECT id, status FROM auth.user_applications WHERE id = %s",
|
||
(application_id,),
|
||
)
|
||
app_row = cur.fetchone()
|
||
if app_row is None:
|
||
raise HTTPException(
|
||
status_code=status.HTTP_404_NOT_FOUND,
|
||
detail="申请不存在",
|
||
)
|
||
|
||
# 2. 检查状态为 pending
|
||
if app_row[1] != "pending":
|
||
raise HTTPException(
|
||
status_code=status.HTTP_409_CONFLICT,
|
||
detail=f"申请当前状态为 {app_row[1]}，无法审核",
|
||
)
|
||
|
||
# 3. 更新申请状态为 rejected
|
||
cur.execute(
|
||
"""
|
||
UPDATE auth.user_applications
|
||
SET status = 'rejected',
|
||
reviewer_id = %s,
|
||
review_note = %s,
|
||
reviewed_at = NOW()
|
||
WHERE id = %s
|
||
RETURNING id, site_code, applied_role_text, status,
|
||
review_note, created_at::text, reviewed_at::text
|
||
""",
|
||
(reviewer_id, review_note, application_id),
|
||
)
|
||
updated_row = cur.fetchone()
|
||
conn.commit()
|
||
finally:
|
||
conn.close()
|
||
|
||
return {
|
||
"id": updated_row[0],
|
||
"site_code": updated_row[1],
|
||
"applied_role_text": updated_row[2],
|
||
"status": updated_row[3],
|
||
"review_note": updated_row[4],
|
||
"created_at": updated_row[5],
|
||
"reviewed_at": updated_row[6],
|
||
}
|
||
|
||
|
||
async def get_user_applications(user_id: int) -> list[dict]:
|
||
"""
|
||
查询用户的所有申请记录。
|
||
|
||
按创建时间倒序排列。
|
||
|
||
返回:
|
||
申请记录 dict 列表
|
||
"""
|
||
conn = get_connection()
|
||
try:
|
||
with conn.cursor() as cur:
|
||
cur.execute(
|
||
"""
|
||
SELECT id, site_code, applied_role_text, status,
|
||
review_note, created_at::text, reviewed_at::text
|
||
FROM auth.user_applications
|
||
WHERE user_id = %s
|
||
ORDER BY created_at DESC
|
||
""",
|
||
(user_id,),
|
||
)
|
||
rows = cur.fetchall()
|
||
finally:
|
||
conn.close()
|
||
|
||
return [
|
||
{
|
||
"id": r[0],
|
||
"site_code": r[1],
|
||
"applied_role_text": r[2],
|
||
"status": r[3],
|
||
"review_note": r[4],
|
||
"created_at": r[5],
|
||
"reviewed_at": r[6],
|
||
}
|
||
for r in rows
|
||
]
|