# 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("请提供合伙人甲方的系统用户ID (partner_a_id),结算系统不使用任何预设值!") if not partner_b: raise BizLogicError("请提供合伙人乙方的系统用户ID (partner_b_id),结算系统不使用任何预设值!") if partner_a not in users_map: raise BizLogicError(f"指定的合伙人甲方用户ID【{partner_a}】在系统用户表中不存在!") if partner_b not in users_map: raise BizLogicError(f"指定的合伙人乙方用户ID【{partner_b}】在系统用户表中不存在!") if partner_a == partner_b: raise BizLogicError("合伙人甲方与乙方不能为同一用户!") # 预加载当前账期涉及的所有商品与插件,技术分成按订单绑定的商品及处理插件开发管理者自动归属 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"订单 #{o.id} 未关联有效商品 (product_id={o.product_id}),无法核算技术分成,请先完善订单商品关联!" ) 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"订单 #{o.id} 所属商品【{prod.title}】(商品ID: {prod.id}) 尚未绑定履约处理插件,无法计算技术分成!请先在「商品管理」编辑弹窗中绑定对应插件后再进行结算。" ) # 2.3 从绑定的插件中提取 developer_id,且必须为系统真实用户 if not getattr(pl, 'developer_id', None) or not pl.developer_id.strip(): raise BizLogicError( f"商品【{prod.title}】绑定的插件【{pl.name} ({pl.display_name})】尚未配置技术开发管理者 (developer_id),无法计算技术分成!请先在「插件管理」中完善开发者配置。" ) 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"商品【{prod.title}】绑定的插件【{pl.name}】所配置的开发者ID【{dev_candidate}】在系统用户表中不存在,无法结算技术分成!" ) # 临时聚合各人的毛利润 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)