# 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.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 partner_a = req.partner_a_id or "partner_a" partner_b = req.partner_b_id or "partner_b" # 预加载当前账期涉及的所有商品与插件,技术分成按订单绑定的商品及处理插件开发管理者自动归属 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 # 收集商品关联的处理插件 plugin_ids = {prod.plugin_id for prod in products_map.values() if getattr(prod, 'plugin_id', None)} plugin_names = set() for prod in products_map.values(): if getattr(prod, 'plugin_name', None) and prod.plugin_name.strip(): plugin_names.add(prod.plugin_name.strip()) if getattr(prod, 'provider', None) and prod.provider.strip(): plugin_names.add(prod.provider.strip()) plugins_map: Dict[str, Plugin] = {} if plugin_ids or plugin_names: conditions = [] if plugin_ids: conditions.append(Plugin.id.in_(plugin_ids)) if plugin_names: conditions.append(Plugin.name.in_(plugin_names)) pl_stmt = select(Plugin).where(or_(*conditions)) pl_res = await db.execute(pl_stmt) for pl in pl_res.scalars().all(): plugins_map[str(pl.id)] = pl plugins_map[pl.name.lower()] = pl def resolve_order_developer(o: VasOrder) -> str: # 1. 订单本身若已记录明确的 developer_id if o.developer_id and o.developer_id.strip(): return o.developer_id.strip() # 2. 从订单绑定的商品推断 prod = products_map.get(o.product_id) if prod: # 2.1 商品直接指定了底层开发者 if getattr(prod, 'developer_id', None) and prod.developer_id.strip(): return prod.developer_id.strip() # 2.2 商品绑定了处理插件 -> 由插件开发管理者 (Plugin.developer_id) 获得技术分成 pl = None if getattr(prod, 'plugin_id', None): pl = plugins_map.get(str(prod.plugin_id)) if not pl and getattr(prod, 'plugin_name', None): pl = plugins_map.get(prod.plugin_name.strip().lower()) if not pl and getattr(prod, 'provider', None): pl = plugins_map.get(prod.provider.strip().lower()) if pl and getattr(pl, 'developer_id', None) and pl.developer_id.strip(): return pl.developer_id.strip() if pl: return f"plugin_{pl.name}_dev" if getattr(prod, 'provider', None): return f"{prod.provider.lower()}_dev" # 3. 兜底 return req.default_developer_id or "unassigned_tech" # 临时聚合各人的毛利润 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) 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, developer_id=dev_id, 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, developer_id=dev_id, 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}元") net_payout = max(0, total_user_gross - fault_penalty) payout_summary.append(SettlementPayoutItem( user_id=uid, 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"账期ID '{req.period_id}' 已存在,不可重复生成") # 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)