| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488 |
- # 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, int, int]:
- """
- 按实际支付流水统计订单实收与币种
- 核心规则:显式扣除通道结算手续费(fee_amount),确保分润基数仅为纯服务净额,避免平台倒贴网关手续费
- 返回: (net_amt, currency, gross_amt, fee_amt)
- """
- pays = payments_by_order.get(o.id, [])
- if pays:
- pay_cur = pays[0].currency or o.base_currency or "CNY"
- total_gross = sum(p.amount for p in pays if p.currency == pay_cur)
- if total_gross == 0:
- total_gross = pays[0].amount
- total_fee = sum((p.fee_amount or 0) for p in pays if p.currency == pay_cur)
- net_amt = max(0, total_gross - total_fee)
- return net_amt, pay_cur, total_gross, total_fee
- else:
- return o.final_amount, (o.base_currency or "CNY"), o.final_amount, 0
- revenue_by_currency: Dict[str, int] = {}
- fee_by_currency: Dict[str, int] = {}
- gross_by_currency: Dict[str, int] = {}
- for o in orders:
- net_amt, cur, gross_amt, fee_amt = resolve_order_payment(o)
- revenue_by_currency[cur] = revenue_by_currency.get(cur, 0) + net_amt
- fee_by_currency[cur] = fee_by_currency.get(cur, 0) + fee_amt
- gross_by_currency[cur] = gross_by_currency.get(cur, 0) + gross_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, gross_amt, fee_amt = 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,
- gross_amount=gross_amt,
- fee_amount=fee_amt,
- 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,
- gross_amount=gross_amt,
- fee_amount=fee_amt,
- 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)
|