| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410 |
- # app/services/settlement_service.py
- from datetime import datetime
- from typing import List, Dict, Any, Optional
- from sqlalchemy.ext.asyncio import AsyncSession
- from sqlalchemy import select, update, and_, or_, func
- from app.models.order import VasOrder
- from app.models.product import VasProduct
- from app.models.plugin import Plugin
- from app.models.user import VasUser
- from app.models.cost import VasCostRecord
- from app.models.fault import VasFaultCompensation
- from app.models.settlement import VasSettlementPeriod, VasSettlementDetail
- from app.schemas.settlement import (
- SettlementPreviewRequest,
- SettlementConfirmRequest,
- SettlementPreviewResponse,
- SettlementPayoutItem,
- SettlementOrderDetailOut,
- )
- from app.services.cost_service import CostService
- from app.core.biz_exception import BizLogicError, NotFoundError
- from app.utils.pagination import paginate
- class SettlementService:
- @staticmethod
- async def _query_eligible_orders(
- db: AsyncSession,
- start_date: datetime,
- end_date: datetime,
- enforce_risk_cleared: bool = False
- ) -> List[VasOrder]:
- """
- 获取账期内履约完成且具备结算资格的订单
- """
- # 使用 completed_at 作为履约完成时间,若为NULL则使用 updated_at
- stmt = select(VasOrder).where(
- VasOrder.status == 'completed',
- VasOrder.settlement_status != 'settled',
- func.coalesce(VasOrder.completed_at, VasOrder.updated_at) >= start_date,
- func.coalesce(VasOrder.completed_at, VasOrder.updated_at) <= end_date,
- )
- if enforce_risk_cleared:
- now = datetime.utcnow()
- stmt = stmt.where(
- (VasOrder.risk_cleared_at == None) | (VasOrder.risk_cleared_at <= now)
- )
- stmt = stmt.order_by(VasOrder.created_at.asc())
- return (await db.execute(stmt)).scalars().all()
- @staticmethod
- async def preview_settlement(
- db: AsyncSession,
- req: SettlementPreviewRequest
- ) -> SettlementPreviewResponse:
- """
- 试算账期分润(不修改数据库,仅预览计算结果)
- """
- orders = await SettlementService._query_eligible_orders(db, req.start_date, req.end_date)
- costs_summary = await CostService.get_costs_summary_by_date(db, req.start_date, req.end_date)
-
- total_revenue = sum(o.final_amount for o in orders)
- total_cost = sum(costs_summary.values())
- total_orders = len(orders)
- avg_cost = (total_cost // total_orders) if total_orders > 0 else 0
- net_profit = total_revenue - total_cost
- # 加载所有系统用户,用于真实自然人账号对账与显示
- u_stmt = select(VasUser)
- all_users = (await db.execute(u_stmt)).scalars().all()
- users_map: Dict[str, VasUser] = {u.id: u for u in all_users}
- partner_a = (req.partner_a_id or "").strip()
- partner_b = (req.partner_b_id or "").strip()
- if not partner_a:
- raise BizLogicError("Please provide system user ID for Partner A (partner_a_id). The settlement system does not use default presets.")
- if not partner_b:
- raise BizLogicError("Please provide system user ID for Partner B (partner_b_id). The settlement system does not use default presets.")
- if partner_a not in users_map:
- raise BizLogicError(f"Specified Partner A user ID '{partner_a}' does not exist in the system users table.")
- if partner_b not in users_map:
- raise BizLogicError(f"Specified Partner B user ID '{partner_b}' does not exist in the system users table.")
- if partner_a == partner_b:
- raise BizLogicError("Partner A and Partner B cannot be the same user.")
- # 预加载当前账期涉及的所有商品与插件,技术分成按订单绑定的商品及处理插件开发管理者自动归属
- product_ids = {o.product_id for o in orders if o.product_id}
- products_map: Dict[int, VasProduct] = {}
- if product_ids:
- p_stmt = select(VasProduct).where(VasProduct.id.in_(product_ids))
- p_res = await db.execute(p_stmt)
- for prod in p_res.scalars().all():
- products_map[prod.id] = prod
- # 加载所有可用插件库
- pl_stmt = select(Plugin)
- pl_res = await db.execute(pl_stmt)
- all_plugins = pl_res.scalars().all()
- plugins_by_id: Dict[int, Plugin] = {pl.id: pl for pl in all_plugins}
- plugins_by_name: Dict[str, Plugin] = {pl.name.lower(): pl for pl in all_plugins}
- def resolve_order_developer(o: VasOrder) -> str:
- # 1. 订单本身若已记录明确的 developer_id,且属于系统真实用户
- if o.developer_id and o.developer_id.strip():
- dev_candidate = o.developer_id.strip()
- if dev_candidate in users_map:
- return dev_candidate
- # 尝试匹配昵称
- for u in all_users:
- if u.nickname and u.nickname.strip().lower() == dev_candidate.lower():
- return u.id
- # 2. 从订单绑定的商品中解析
- if not o.product_id or o.product_id not in products_map:
- raise BizLogicError(
- f"Order #{o.id} is not associated with a valid product (product_id={o.product_id}). Technical share cannot be calculated without product association."
- )
- prod = products_map[o.product_id]
- # 2.1 若商品表显式指定了技术开发者ID且为真实用户
- if getattr(prod, 'developer_id', None) and prod.developer_id.strip():
- dev_candidate = prod.developer_id.strip()
- if dev_candidate in users_map:
- return dev_candidate
- for u in all_users:
- if u.nickname and u.nickname.strip().lower() == dev_candidate.lower():
- return u.id
- # 2.2 严格检查商品是否显式绑定了处理插件(不进行任何模糊推导,未绑定直接报错)
- pl = None
- if getattr(prod, 'plugin_id', None) and prod.plugin_id in plugins_by_id:
- pl = plugins_by_id[prod.plugin_id]
- elif getattr(prod, 'plugin_name', None) and prod.plugin_name.strip():
- pl = plugins_by_name.get(prod.plugin_name.strip().lower())
- if not pl:
- raise BizLogicError(
- f"Product '{prod.title}' (ID: {prod.id}) for order #{o.id} is not bound to an execution plugin. Please bind a plugin in Product Management before settling."
- )
- # 2.3 从绑定的插件中提取 developer_id,且必须为系统真实用户
- if not getattr(pl, 'developer_id', None) or not pl.developer_id.strip():
- raise BizLogicError(
- f"Plugin '{pl.name} ({pl.display_name})' bound to product '{prod.title}' has no developer_id configured. Please configure a developer in Plugin Management before settling."
- )
- dev_candidate = pl.developer_id.strip()
- if dev_candidate in users_map:
- return dev_candidate
- for u in all_users:
- if u.nickname and u.nickname.strip().lower() == dev_candidate.lower():
- return u.id
- raise BizLogicError(
- f"Developer ID '{dev_candidate}' configured for plugin '{pl.name}' on product '{prod.title}' does not exist in system users table."
- )
- # 临时聚合各人的毛利润
- user_gross_map: Dict[str, Dict[str, int]] = {
- partner_a: {"pool": 0, "sales": 0, "tech": 0},
- partner_b: {"pool": 0, "sales": 0, "tech": 0},
- }
- order_details: List[SettlementOrderDetailOut] = []
- for o in orders:
- order_profit = o.final_amount - avg_cost
- dev_id = resolve_order_developer(o)
- dev_user = users_map.get(dev_id)
- dev_name = dev_user.nickname if (dev_user and dev_user.nickname) else dev_id
- h_user = users_map.get(o.handler_id) if o.handler_id else None
- h_name = h_user.nickname if (h_user and h_user.nickname) else o.handler_id
- if order_profit <= 0:
- # 亏损或保本单
- order_details.append(SettlementOrderDetailOut(
- order_id=o.id,
- channel_type=o.channel_type or "direct",
- agency_id=o.agency_id,
- handler_id=o.handler_id,
- handler_name=h_name,
- developer_id=dev_id,
- developer_name=dev_name,
- order_amount=o.final_amount,
- avg_cost=avg_cost,
- net_profit=order_profit,
- tech_fee=0,
- sales_fee=0,
- pool_fee=0,
- partner_a_amount=0,
- partner_b_amount=0,
- ))
- continue
- tech_fee = int(order_profit * 0.20)
- sales_fee = int(order_profit * 0.40)
- pool_fee = order_profit - tech_fee - sales_fee
- part_a = pool_fee // 2
- part_b = pool_fee // 2
- if dev_id not in user_gross_map:
- user_gross_map[dev_id] = {"pool": 0, "sales": 0, "tech": 0}
- user_gross_map[dev_id]["tech"] += tech_fee
- # 销售提成归属逻辑
- if o.handler_id:
- # 社交自收直客单 / 中介单:归该对接人个人
- h_id = o.handler_id
- if h_id not in user_gross_map:
- user_gross_map[h_id] = {"pool": 0, "sales": 0, "tech": 0}
- user_gross_map[h_id]["sales"] += sales_fee
- else:
- # 官网公海单:甲乙平分 40% 销售提成
- part_a += sales_fee // 2
- part_b += sales_fee // 2
- user_gross_map[partner_a]["pool"] += part_a
- user_gross_map[partner_b]["pool"] += part_b
- order_details.append(SettlementOrderDetailOut(
- order_id=o.id,
- channel_type=o.channel_type or "direct",
- agency_id=o.agency_id,
- handler_id=o.handler_id,
- handler_name=h_name,
- developer_id=dev_id,
- developer_name=dev_name,
- order_amount=o.final_amount,
- avg_cost=avg_cost,
- net_profit=order_profit,
- tech_fee=tech_fee,
- sales_fee=sales_fee,
- pool_fee=pool_fee,
- partner_a_amount=part_a,
- partner_b_amount=part_b,
- ))
- # 查询未核销的个人过错罚单,并从提成中扣除
- payout_summary: List[SettlementPayoutItem] = []
- for uid, breakdown in user_gross_map.items():
- total_user_gross = breakdown["pool"] + breakdown["sales"] + breakdown["tech"]
- if total_user_gross == 0:
- continue
- # 查询该用户待扣罚单
- fault_stmt = select(func.coalesce(func.sum(VasFaultCompensation.penalty_amount), 0)).where(
- VasFaultCompensation.liable_user_id == uid,
- VasFaultCompensation.deducted_status == 0
- )
- fault_penalty = int((await db.execute(fault_stmt)).scalar() or 0)
-
- roles = []
- if breakdown["pool"] > 0:
- roles.append(f"合伙分红:{breakdown['pool']/100:.2f}元")
- if breakdown["sales"] > 0:
- roles.append(f"销售提成:{breakdown['sales']/100:.2f}元")
- if breakdown["tech"] > 0:
- roles.append(f"技术版权:{breakdown['tech']/100:.2f}元")
- user_obj = users_map.get(uid)
- user_display_name = user_obj.nickname if (user_obj and user_obj.nickname) else uid
- net_payout = max(0, total_user_gross - fault_penalty)
- payout_summary.append(SettlementPayoutItem(
- user_id=uid,
- user_name=user_display_name,
- role_description=" | ".join(roles),
- gross_amount=total_user_gross,
- fault_penalty=fault_penalty,
- net_payout=net_payout,
- ))
- return SettlementPreviewResponse(
- period_range=f"{req.start_date.strftime('%Y-%m-%d')} ~ {req.end_date.strftime('%Y-%m-%d')}",
- total_orders=total_orders,
- total_revenue=total_revenue,
- total_cost=total_cost,
- avg_cost=avg_cost,
- net_profit=net_profit,
- payout_summary=payout_summary,
- cost_breakdown=costs_summary,
- details=order_details,
- )
- @staticmethod
- async def confirm_settlement(
- db: AsyncSession,
- req: SettlementConfirmRequest,
- confirmed_by: str
- ) -> VasSettlementPeriod:
- """
- 正式确认并冻结账期分润(写入快照,锁定订单状态)
- """
- # 1. 检查账期ID是否已存在
- stmt = select(VasSettlementPeriod).where(VasSettlementPeriod.id == req.period_id)
- if (await db.execute(stmt)).scalar_one_or_none():
- raise BizLogicError(f"Settlement period ID '{req.period_id}' already exists")
- # 2. 试算结果
- preview = await SettlementService.preview_settlement(
- db,
- SettlementPreviewRequest(
- start_date=req.start_date,
- end_date=req.end_date,
- partner_a_id=req.partner_a_id,
- partner_b_id=req.partner_b_id,
- default_developer_id=req.default_developer_id,
- )
- )
- now = datetime.utcnow()
- # 3. 创建账期总表
- period = VasSettlementPeriod(
- id=req.period_id,
- name=req.period_name,
- start_date=req.start_date,
- end_date=req.end_date,
- total_orders=preview.total_orders,
- total_revenue=preview.total_revenue,
- total_cost=preview.total_cost,
- avg_cost=preview.avg_cost,
- net_profit=preview.net_profit,
- status='confirmed',
- summary_data={
- "payout_summary": [p.dict() for p in preview.payout_summary],
- "cost_breakdown": preview.cost_breakdown,
- },
- confirmed_by=confirmed_by,
- confirmed_at=now,
- notes=req.notes,
- )
- db.add(period)
- # 4. 插入每单分润快照,并将对应订单状态置为 settled
- for d in preview.details:
- detail = VasSettlementDetail(
- period_id=req.period_id,
- order_id=d.order_id,
- channel_type=d.channel_type,
- agency_id=d.agency_id,
- handler_id=d.handler_id,
- developer_id=d.developer_id,
- order_amount=d.order_amount,
- avg_cost=d.avg_cost,
- net_profit=d.net_profit,
- tech_fee=d.tech_fee,
- sales_fee=d.sales_fee,
- pool_fee=d.pool_fee,
- partner_a_amount=d.partner_a_amount,
- partner_b_amount=d.partner_b_amount,
- snapshot=d.dict(),
- )
- db.add(detail)
- # 更新订单的结算归属
- await db.execute(
- update(VasOrder)
- .where(VasOrder.id == d.order_id)
- .values(
- settlement_status='settled',
- settlement_period_id=req.period_id
- )
- )
- # 5. 绑定当期耗材成本的 period_id
- await db.execute(
- update(VasCostRecord)
- .where(
- VasCostRecord.paid_at >= req.start_date,
- VasCostRecord.paid_at <= req.end_date,
- VasCostRecord.period_id == None
- )
- .values(period_id=req.period_id)
- )
- # 6. 将已参与结算扣除的过错记录置为已扣除
- await db.execute(
- update(VasFaultCompensation)
- .where(VasFaultCompensation.deducted_status == 0)
- .values(
- deducted_status=1,
- period_id=req.period_id
- )
- )
- await db.commit()
- await db.refresh(period)
- return period
- @staticmethod
- async def get_period(db: AsyncSession, period_id: str) -> Optional[VasSettlementPeriod]:
- stmt = select(VasSettlementPeriod).where(VasSettlementPeriod.id == period_id)
- return (await db.execute(stmt)).scalar_one_or_none()
- @staticmethod
- async def list_periods(db: AsyncSession, page: int = 0, size: int = 20):
- stmt = select(VasSettlementPeriod).order_by(VasSettlementPeriod.created_at.desc())
- return await paginate(db, stmt, page, size)
- @staticmethod
- async def list_period_details(db: AsyncSession, period_id: str, page: int = 0, size: int = 50):
- stmt = select(VasSettlementDetail).where(VasSettlementDetail.period_id == period_id)
- stmt = stmt.order_by(VasSettlementDetail.created_at.asc())
- return await paginate(db, stmt, page, size)
|