# 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.models.payment import VasPayment from app.services.payment_service import PaymentService, convert_currency 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) # 加载订单成功支付记录,按实际流水币种统计(是欧元就是欧元,是rmb就是rmb,不强制折算汇率) order_ids = [o.id for o in orders] payments_by_order: Dict[str, List[VasPayment]] = {} if order_ids: p_stmt = select(VasPayment).where( VasPayment.order_id.in_(order_ids), VasPayment.status.in_(["succeeded", "paid"]), ) p_res = await db.execute(p_stmt) for p in p_res.scalars().all(): payments_by_order.setdefault(p.order_id, []).append(p) def resolve_order_payment(o: VasOrder) -> tuple[int, str]: """ 按实际支付流水统计订单实收与币种 返回: (amount, currency) """ pays = payments_by_order.get(o.id, []) if pays: pay_cur = pays[0].currency or o.base_currency or "CNY" total_amt = sum(p.amount for p in pays if p.currency == pay_cur) if total_amt == 0: total_amt = pays[0].amount return total_amt, pay_cur else: return o.final_amount, (o.base_currency or "CNY") revenue_by_currency: Dict[str, int] = {} for o in orders: amt, cur = resolve_order_payment(o) revenue_by_currency[cur] = revenue_by_currency.get(cur, 0) + amt total_orders = len(orders) total_cost = sum(costs_summary.values()) cny_orders_count = sum(1 for o in orders if resolve_order_payment(o)[1] == "CNY") avg_cost_cny = (total_cost // cny_orders_count) if cny_orders_count > 0 else 0 total_revenue_cny = revenue_by_currency.get("CNY", 0) net_profit_cny = max(0, total_revenue_cny - total_cost) profit_by_currency: Dict[str, int] = {} for cur, amt in revenue_by_currency.items(): if cur == "CNY": profit_by_currency[cur] = net_profit_cny else: profit_by_currency[cur] = amt # 加载所有系统用户,用于真实自然人账号对账与显示 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, Dict[str, int]]] = { partner_a: {}, partner_b: {}, } order_details: List[SettlementOrderDetailOut] = [] for o in orders: order_amt, order_cur = resolve_order_payment(o) order_cost = avg_cost_cny if order_cur == "CNY" else 0 order_profit = max(0, order_amt - order_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=order_amt, currency=order_cur, original_amount=o.final_amount, original_currency=o.base_currency or order_cur, avg_cost=order_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_total = int(order_profit * 0.40) pool_fee = order_profit - tech_fee - sales_fee_total part_a = pool_fee // 2 part_b = pool_fee // 2 if dev_id not in user_gross_map: user_gross_map[dev_id] = {} user_gross_map[dev_id].setdefault(order_cur, {"pool": 0, "sales": 0, "tech": 0}) user_gross_map[dev_id][order_cur]["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] = {} user_gross_map[h_id].setdefault(order_cur, {"pool": 0, "sales": 0, "tech": 0}) user_gross_map[h_id][order_cur]["sales"] += sales_fee_total displayed_sales_fee = sales_fee_total else: # 官网公海单:如果没有设定对接人,就是甲方乙方平分40%销售提成 part_a += sales_fee_total // 2 part_b += sales_fee_total // 2 displayed_sales_fee = 0 # 对接人提成为0,提成已直接平分计入甲方与乙方 user_gross_map[partner_a].setdefault(order_cur, {"pool": 0, "sales": 0, "tech": 0}) user_gross_map[partner_a][order_cur]["pool"] += part_a user_gross_map[partner_b].setdefault(order_cur, {"pool": 0, "sales": 0, "tech": 0}) user_gross_map[partner_b][order_cur]["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=order_amt, currency=order_cur, original_amount=o.final_amount, original_currency=o.base_currency or order_cur, avg_cost=order_cost, net_profit=order_profit, tech_fee=tech_fee, sales_fee=displayed_sales_fee, pool_fee=pool_fee, partner_a_amount=part_a, partner_b_amount=part_b, )) # 查询未核销的个人过错罚单,并从提成中扣除(按币种生成对账条目) payout_summary: List[SettlementPayoutItem] = [] user_fault_penalty: Dict[str, int] = {} for uid in user_gross_map.keys(): fault_stmt = select(func.coalesce(func.sum(VasFaultCompensation.penalty_amount), 0)).where( VasFaultCompensation.liable_user_id == uid, VasFaultCompensation.deducted_status == 0 ) user_fault_penalty[uid] = int((await db.execute(fault_stmt)).scalar() or 0) for uid, currencies_map in user_gross_map.items(): user_obj = users_map.get(uid) user_display_name = user_obj.nickname if (user_obj and user_obj.nickname) else uid for cur, breakdown in currencies_map.items(): total_user_gross = breakdown["pool"] + breakdown["sales"] + breakdown["tech"] if total_user_gross == 0: continue fault_penalty = 0 if cur == "CNY": fault_penalty = user_fault_penalty.get(uid, 0) sym = "¥" if cur == "CNY" else "€" if cur == "EUR" else "$" if cur == "USD" else cur roles = [] if breakdown["pool"] > 0: roles.append(f"合伙分红:{sym}{breakdown['pool']/100:.2f}") if breakdown["sales"] > 0: roles.append(f"销售提成:{sym}{breakdown['sales']/100:.2f}") if breakdown["tech"] > 0: roles.append(f"技术版权:{sym}{breakdown['tech']/100:.2f}") net_payout = max(0, total_user_gross - fault_penalty) payout_summary.append(SettlementPayoutItem( user_id=uid, user_name=user_display_name, role_description=f"[{cur}] " + " | ".join(roles), gross_amount=total_user_gross, fault_penalty=fault_penalty, net_payout=net_payout, currency=cur, )) 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_cny, total_cost=total_cost, avg_cost=avg_cost_cny, net_profit=net_profit_cny, revenue_by_currency=revenue_by_currency, profit_by_currency=profit_by_currency, 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)