settlement_service.py 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488
  1. # app/services/settlement_service.py
  2. from datetime import datetime
  3. from typing import List, Dict, Any, Optional
  4. from sqlalchemy.ext.asyncio import AsyncSession
  5. from sqlalchemy import select, update, and_, or_, func
  6. from app.models.order import VasOrder
  7. from app.models.product import VasProduct
  8. from app.models.plugin import Plugin
  9. from app.models.user import VasUser
  10. from app.models.cost import VasCostRecord
  11. from app.models.fault import VasFaultCompensation
  12. from app.models.settlement import VasSettlementPeriod, VasSettlementDetail
  13. from app.schemas.settlement import (
  14. SettlementPreviewRequest,
  15. SettlementConfirmRequest,
  16. SettlementPreviewResponse,
  17. SettlementPayoutItem,
  18. SettlementOrderDetailOut,
  19. )
  20. from app.models.payment import VasPayment
  21. from app.services.payment_service import PaymentService, convert_currency
  22. from app.services.cost_service import CostService
  23. from app.core.biz_exception import BizLogicError, NotFoundError
  24. from app.utils.pagination import paginate
  25. class SettlementService:
  26. @staticmethod
  27. async def _query_eligible_orders(
  28. db: AsyncSession,
  29. start_date: datetime,
  30. end_date: datetime,
  31. enforce_risk_cleared: bool = False
  32. ) -> List[VasOrder]:
  33. """
  34. 获取账期内履约完成且具备结算资格的订单
  35. """
  36. # 使用 completed_at 作为履约完成时间,若为NULL则使用 updated_at
  37. stmt = select(VasOrder).where(
  38. VasOrder.status == 'completed',
  39. VasOrder.settlement_status != 'settled',
  40. func.coalesce(VasOrder.completed_at, VasOrder.updated_at) >= start_date,
  41. func.coalesce(VasOrder.completed_at, VasOrder.updated_at) <= end_date,
  42. )
  43. if enforce_risk_cleared:
  44. now = datetime.utcnow()
  45. stmt = stmt.where(
  46. (VasOrder.risk_cleared_at == None) | (VasOrder.risk_cleared_at <= now)
  47. )
  48. stmt = stmt.order_by(VasOrder.created_at.asc())
  49. return (await db.execute(stmt)).scalars().all()
  50. @staticmethod
  51. async def preview_settlement(
  52. db: AsyncSession,
  53. req: SettlementPreviewRequest
  54. ) -> SettlementPreviewResponse:
  55. """
  56. 试算账期分润(不修改数据库,仅预览计算结果)
  57. """
  58. orders = await SettlementService._query_eligible_orders(db, req.start_date, req.end_date)
  59. costs_summary = await CostService.get_costs_summary_by_date(db, req.start_date, req.end_date)
  60. # 加载订单成功支付记录,按实际流水币种统计(是欧元就是欧元,是rmb就是rmb,不强制折算汇率)
  61. order_ids = [o.id for o in orders]
  62. payments_by_order: Dict[str, List[VasPayment]] = {}
  63. if order_ids:
  64. p_stmt = select(VasPayment).where(
  65. VasPayment.order_id.in_(order_ids),
  66. VasPayment.status.in_(["succeeded", "paid"]),
  67. )
  68. p_res = await db.execute(p_stmt)
  69. for p in p_res.scalars().all():
  70. payments_by_order.setdefault(p.order_id, []).append(p)
  71. def resolve_order_payment(o: VasOrder) -> tuple[int, str, int, int]:
  72. """
  73. 按实际支付流水统计订单实收与币种
  74. 核心规则:显式扣除通道结算手续费(fee_amount),确保分润基数仅为纯服务净额,避免平台倒贴网关手续费
  75. 返回: (net_amt, currency, gross_amt, fee_amt)
  76. """
  77. pays = payments_by_order.get(o.id, [])
  78. if pays:
  79. pay_cur = pays[0].currency or o.base_currency or "CNY"
  80. total_gross = sum(p.amount for p in pays if p.currency == pay_cur)
  81. if total_gross == 0:
  82. total_gross = pays[0].amount
  83. total_fee = sum((p.fee_amount or 0) for p in pays if p.currency == pay_cur)
  84. net_amt = max(0, total_gross - total_fee)
  85. return net_amt, pay_cur, total_gross, total_fee
  86. else:
  87. return o.final_amount, (o.base_currency or "CNY"), o.final_amount, 0
  88. revenue_by_currency: Dict[str, int] = {}
  89. fee_by_currency: Dict[str, int] = {}
  90. gross_by_currency: Dict[str, int] = {}
  91. for o in orders:
  92. net_amt, cur, gross_amt, fee_amt = resolve_order_payment(o)
  93. revenue_by_currency[cur] = revenue_by_currency.get(cur, 0) + net_amt
  94. fee_by_currency[cur] = fee_by_currency.get(cur, 0) + fee_amt
  95. gross_by_currency[cur] = gross_by_currency.get(cur, 0) + gross_amt
  96. total_orders = len(orders)
  97. total_cost = sum(costs_summary.values())
  98. cny_orders_count = sum(1 for o in orders if resolve_order_payment(o)[1] == "CNY")
  99. avg_cost_cny = (total_cost // cny_orders_count) if cny_orders_count > 0 else 0
  100. total_revenue_cny = revenue_by_currency.get("CNY", 0)
  101. net_profit_cny = max(0, total_revenue_cny - total_cost)
  102. profit_by_currency: Dict[str, int] = {}
  103. for cur, amt in revenue_by_currency.items():
  104. if cur == "CNY":
  105. profit_by_currency[cur] = net_profit_cny
  106. else:
  107. profit_by_currency[cur] = amt
  108. # 加载所有系统用户,用于真实自然人账号对账与显示
  109. u_stmt = select(VasUser)
  110. all_users = (await db.execute(u_stmt)).scalars().all()
  111. users_map: Dict[str, VasUser] = {u.id: u for u in all_users}
  112. partner_a = (req.partner_a_id or "").strip()
  113. partner_b = (req.partner_b_id or "").strip()
  114. if not partner_a:
  115. raise BizLogicError("Please provide system user ID for Partner A (partner_a_id). The settlement system does not use default presets.")
  116. if not partner_b:
  117. raise BizLogicError("Please provide system user ID for Partner B (partner_b_id). The settlement system does not use default presets.")
  118. if partner_a not in users_map:
  119. raise BizLogicError(f"Specified Partner A user ID '{partner_a}' does not exist in the system users table.")
  120. if partner_b not in users_map:
  121. raise BizLogicError(f"Specified Partner B user ID '{partner_b}' does not exist in the system users table.")
  122. if partner_a == partner_b:
  123. raise BizLogicError("Partner A and Partner B cannot be the same user.")
  124. # 预加载当前账期涉及的所有商品与插件,技术分成按订单绑定的商品及处理插件开发管理者自动归属
  125. product_ids = {o.product_id for o in orders if o.product_id}
  126. products_map: Dict[int, VasProduct] = {}
  127. if product_ids:
  128. p_stmt = select(VasProduct).where(VasProduct.id.in_(product_ids))
  129. p_res = await db.execute(p_stmt)
  130. for prod in p_res.scalars().all():
  131. products_map[prod.id] = prod
  132. # 加载所有可用插件库
  133. pl_stmt = select(Plugin)
  134. pl_res = await db.execute(pl_stmt)
  135. all_plugins = pl_res.scalars().all()
  136. plugins_by_id: Dict[int, Plugin] = {pl.id: pl for pl in all_plugins}
  137. plugins_by_name: Dict[str, Plugin] = {pl.name.lower(): pl for pl in all_plugins}
  138. def resolve_order_developer(o: VasOrder) -> str:
  139. # 1. 订单本身若已记录明确的 developer_id,且属于系统真实用户
  140. if o.developer_id and o.developer_id.strip():
  141. dev_candidate = o.developer_id.strip()
  142. if dev_candidate in users_map:
  143. return dev_candidate
  144. # 尝试匹配昵称
  145. for u in all_users:
  146. if u.nickname and u.nickname.strip().lower() == dev_candidate.lower():
  147. return u.id
  148. # 2. 从订单绑定的商品中解析
  149. if not o.product_id or o.product_id not in products_map:
  150. raise BizLogicError(
  151. f"Order #{o.id} is not associated with a valid product (product_id={o.product_id}). Technical share cannot be calculated without product association."
  152. )
  153. prod = products_map[o.product_id]
  154. # 2.1 若商品表显式指定了技术开发者ID且为真实用户
  155. if getattr(prod, 'developer_id', None) and prod.developer_id.strip():
  156. dev_candidate = prod.developer_id.strip()
  157. if dev_candidate in users_map:
  158. return dev_candidate
  159. for u in all_users:
  160. if u.nickname and u.nickname.strip().lower() == dev_candidate.lower():
  161. return u.id
  162. # 2.2 严格检查商品是否显式绑定了处理插件(不进行任何模糊推导,未绑定直接报错)
  163. pl = None
  164. if getattr(prod, 'plugin_id', None) and prod.plugin_id in plugins_by_id:
  165. pl = plugins_by_id[prod.plugin_id]
  166. elif getattr(prod, 'plugin_name', None) and prod.plugin_name.strip():
  167. pl = plugins_by_name.get(prod.plugin_name.strip().lower())
  168. if not pl:
  169. raise BizLogicError(
  170. 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."
  171. )
  172. # 2.3 从绑定的插件中提取 developer_id,且必须为系统真实用户
  173. if not getattr(pl, 'developer_id', None) or not pl.developer_id.strip():
  174. raise BizLogicError(
  175. 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."
  176. )
  177. dev_candidate = pl.developer_id.strip()
  178. if dev_candidate in users_map:
  179. return dev_candidate
  180. for u in all_users:
  181. if u.nickname and u.nickname.strip().lower() == dev_candidate.lower():
  182. return u.id
  183. raise BizLogicError(
  184. f"Developer ID '{dev_candidate}' configured for plugin '{pl.name}' on product '{prod.title}' does not exist in system users table."
  185. )
  186. # 临时聚合各人按币种的毛利润
  187. user_gross_map: Dict[str, Dict[str, Dict[str, int]]] = {
  188. partner_a: {},
  189. partner_b: {},
  190. }
  191. order_details: List[SettlementOrderDetailOut] = []
  192. for o in orders:
  193. order_amt, order_cur, gross_amt, fee_amt = resolve_order_payment(o)
  194. order_cost = avg_cost_cny if order_cur == "CNY" else 0
  195. order_profit = max(0, order_amt - order_cost)
  196. dev_id = resolve_order_developer(o)
  197. dev_user = users_map.get(dev_id)
  198. dev_name = dev_user.nickname if (dev_user and dev_user.nickname) else dev_id
  199. h_user = users_map.get(o.handler_id) if o.handler_id else None
  200. h_name = h_user.nickname if (h_user and h_user.nickname) else o.handler_id
  201. if order_profit <= 0:
  202. # 亏损或保本单
  203. order_details.append(SettlementOrderDetailOut(
  204. order_id=o.id,
  205. channel_type=o.channel_type or "direct",
  206. agency_id=o.agency_id,
  207. handler_id=o.handler_id,
  208. handler_name=h_name,
  209. developer_id=dev_id,
  210. developer_name=dev_name,
  211. order_amount=order_amt,
  212. currency=order_cur,
  213. gross_amount=gross_amt,
  214. fee_amount=fee_amt,
  215. original_amount=o.final_amount,
  216. original_currency=o.base_currency or order_cur,
  217. avg_cost=order_cost,
  218. net_profit=order_profit,
  219. tech_fee=0,
  220. sales_fee=0,
  221. pool_fee=0,
  222. partner_a_amount=0,
  223. partner_b_amount=0,
  224. ))
  225. continue
  226. tech_fee = int(order_profit * 0.20)
  227. sales_fee_total = int(order_profit * 0.40)
  228. pool_fee = order_profit - tech_fee - sales_fee_total
  229. part_a = pool_fee // 2
  230. part_b = pool_fee // 2
  231. if dev_id not in user_gross_map:
  232. user_gross_map[dev_id] = {}
  233. user_gross_map[dev_id].setdefault(order_cur, {"pool": 0, "sales": 0, "tech": 0})
  234. user_gross_map[dev_id][order_cur]["tech"] += tech_fee
  235. # 销售提成归属逻辑
  236. if o.handler_id:
  237. # 社交自收直客单 / 中介单:归该对接人个人
  238. h_id = o.handler_id
  239. if h_id not in user_gross_map:
  240. user_gross_map[h_id] = {}
  241. user_gross_map[h_id].setdefault(order_cur, {"pool": 0, "sales": 0, "tech": 0})
  242. user_gross_map[h_id][order_cur]["sales"] += sales_fee_total
  243. displayed_sales_fee = sales_fee_total
  244. else:
  245. # 官网公海单:如果没有设定对接人,就是甲方乙方平分40%销售提成
  246. part_a += sales_fee_total // 2
  247. part_b += sales_fee_total // 2
  248. displayed_sales_fee = 0 # 对接人提成为0,提成已直接平分计入甲方与乙方
  249. user_gross_map[partner_a].setdefault(order_cur, {"pool": 0, "sales": 0, "tech": 0})
  250. user_gross_map[partner_a][order_cur]["pool"] += part_a
  251. user_gross_map[partner_b].setdefault(order_cur, {"pool": 0, "sales": 0, "tech": 0})
  252. user_gross_map[partner_b][order_cur]["pool"] += part_b
  253. order_details.append(SettlementOrderDetailOut(
  254. order_id=o.id,
  255. channel_type=o.channel_type or "direct",
  256. agency_id=o.agency_id,
  257. handler_id=o.handler_id,
  258. handler_name=h_name,
  259. developer_id=dev_id,
  260. developer_name=dev_name,
  261. order_amount=order_amt,
  262. currency=order_cur,
  263. gross_amount=gross_amt,
  264. fee_amount=fee_amt,
  265. original_amount=o.final_amount,
  266. original_currency=o.base_currency or order_cur,
  267. avg_cost=order_cost,
  268. net_profit=order_profit,
  269. tech_fee=tech_fee,
  270. sales_fee=displayed_sales_fee,
  271. pool_fee=pool_fee,
  272. partner_a_amount=part_a,
  273. partner_b_amount=part_b,
  274. ))
  275. # 查询未核销的个人过错罚单,并从提成中扣除(按币种生成对账条目)
  276. payout_summary: List[SettlementPayoutItem] = []
  277. user_fault_penalty: Dict[str, int] = {}
  278. for uid in user_gross_map.keys():
  279. fault_stmt = select(func.coalesce(func.sum(VasFaultCompensation.penalty_amount), 0)).where(
  280. VasFaultCompensation.liable_user_id == uid,
  281. VasFaultCompensation.deducted_status == 0
  282. )
  283. user_fault_penalty[uid] = int((await db.execute(fault_stmt)).scalar() or 0)
  284. for uid, currencies_map in user_gross_map.items():
  285. user_obj = users_map.get(uid)
  286. user_display_name = user_obj.nickname if (user_obj and user_obj.nickname) else uid
  287. for cur, breakdown in currencies_map.items():
  288. total_user_gross = breakdown["pool"] + breakdown["sales"] + breakdown["tech"]
  289. if total_user_gross == 0:
  290. continue
  291. fault_penalty = 0
  292. if cur == "CNY":
  293. fault_penalty = user_fault_penalty.get(uid, 0)
  294. sym = "¥" if cur == "CNY" else "€" if cur == "EUR" else "$" if cur == "USD" else cur
  295. roles = []
  296. if breakdown["pool"] > 0:
  297. roles.append(f"合伙分红:{sym}{breakdown['pool']/100:.2f}")
  298. if breakdown["sales"] > 0:
  299. roles.append(f"销售提成:{sym}{breakdown['sales']/100:.2f}")
  300. if breakdown["tech"] > 0:
  301. roles.append(f"技术版权:{sym}{breakdown['tech']/100:.2f}")
  302. net_payout = max(0, total_user_gross - fault_penalty)
  303. payout_summary.append(SettlementPayoutItem(
  304. user_id=uid,
  305. user_name=user_display_name,
  306. role_description=f"[{cur}] " + " | ".join(roles),
  307. gross_amount=total_user_gross,
  308. fault_penalty=fault_penalty,
  309. net_payout=net_payout,
  310. currency=cur,
  311. ))
  312. return SettlementPreviewResponse(
  313. period_range=f"{req.start_date.strftime('%Y-%m-%d')} ~ {req.end_date.strftime('%Y-%m-%d')}",
  314. total_orders=total_orders,
  315. total_revenue=total_revenue_cny,
  316. total_cost=total_cost,
  317. avg_cost=avg_cost_cny,
  318. net_profit=net_profit_cny,
  319. revenue_by_currency=revenue_by_currency,
  320. profit_by_currency=profit_by_currency,
  321. payout_summary=payout_summary,
  322. cost_breakdown=costs_summary,
  323. details=order_details,
  324. )
  325. @staticmethod
  326. async def confirm_settlement(
  327. db: AsyncSession,
  328. req: SettlementConfirmRequest,
  329. confirmed_by: str
  330. ) -> VasSettlementPeriod:
  331. """
  332. 正式确认并冻结账期分润(写入快照,锁定订单状态)
  333. """
  334. # 1. 检查账期ID是否已存在
  335. stmt = select(VasSettlementPeriod).where(VasSettlementPeriod.id == req.period_id)
  336. if (await db.execute(stmt)).scalar_one_or_none():
  337. raise BizLogicError(f"Settlement period ID '{req.period_id}' already exists")
  338. # 2. 试算结果
  339. preview = await SettlementService.preview_settlement(
  340. db,
  341. SettlementPreviewRequest(
  342. start_date=req.start_date,
  343. end_date=req.end_date,
  344. partner_a_id=req.partner_a_id,
  345. partner_b_id=req.partner_b_id,
  346. default_developer_id=req.default_developer_id,
  347. )
  348. )
  349. now = datetime.utcnow()
  350. # 3. 创建账期总表
  351. period = VasSettlementPeriod(
  352. id=req.period_id,
  353. name=req.period_name,
  354. start_date=req.start_date,
  355. end_date=req.end_date,
  356. total_orders=preview.total_orders,
  357. total_revenue=preview.total_revenue,
  358. total_cost=preview.total_cost,
  359. avg_cost=preview.avg_cost,
  360. net_profit=preview.net_profit,
  361. status='confirmed',
  362. summary_data={
  363. "payout_summary": [p.dict() for p in preview.payout_summary],
  364. "cost_breakdown": preview.cost_breakdown,
  365. },
  366. confirmed_by=confirmed_by,
  367. confirmed_at=now,
  368. notes=req.notes,
  369. )
  370. db.add(period)
  371. # 4. 插入每单分润快照,并将对应订单状态置为 settled
  372. for d in preview.details:
  373. detail = VasSettlementDetail(
  374. period_id=req.period_id,
  375. order_id=d.order_id,
  376. channel_type=d.channel_type,
  377. agency_id=d.agency_id,
  378. handler_id=d.handler_id,
  379. developer_id=d.developer_id,
  380. order_amount=d.order_amount,
  381. avg_cost=d.avg_cost,
  382. net_profit=d.net_profit,
  383. tech_fee=d.tech_fee,
  384. sales_fee=d.sales_fee,
  385. pool_fee=d.pool_fee,
  386. partner_a_amount=d.partner_a_amount,
  387. partner_b_amount=d.partner_b_amount,
  388. snapshot=d.dict(),
  389. )
  390. db.add(detail)
  391. # 更新订单的结算归属
  392. await db.execute(
  393. update(VasOrder)
  394. .where(VasOrder.id == d.order_id)
  395. .values(
  396. settlement_status='settled',
  397. settlement_period_id=req.period_id
  398. )
  399. )
  400. # 5. 绑定当期耗材成本的 period_id
  401. await db.execute(
  402. update(VasCostRecord)
  403. .where(
  404. VasCostRecord.paid_at >= req.start_date,
  405. VasCostRecord.paid_at <= req.end_date,
  406. VasCostRecord.period_id == None
  407. )
  408. .values(period_id=req.period_id)
  409. )
  410. # 6. 将已参与结算扣除的过错记录置为已扣除
  411. await db.execute(
  412. update(VasFaultCompensation)
  413. .where(VasFaultCompensation.deducted_status == 0)
  414. .values(
  415. deducted_status=1,
  416. period_id=req.period_id
  417. )
  418. )
  419. await db.commit()
  420. await db.refresh(period)
  421. return period
  422. @staticmethod
  423. async def get_period(db: AsyncSession, period_id: str) -> Optional[VasSettlementPeriod]:
  424. stmt = select(VasSettlementPeriod).where(VasSettlementPeriod.id == period_id)
  425. return (await db.execute(stmt)).scalar_one_or_none()
  426. @staticmethod
  427. async def list_periods(db: AsyncSession, page: int = 0, size: int = 20):
  428. stmt = select(VasSettlementPeriod).order_by(VasSettlementPeriod.created_at.desc())
  429. return await paginate(db, stmt, page, size)
  430. @staticmethod
  431. async def list_period_details(db: AsyncSession, period_id: str, page: int = 0, size: int = 50):
  432. stmt = select(VasSettlementDetail).where(VasSettlementDetail.period_id == period_id)
  433. stmt = stmt.order_by(VasSettlementDetail.created_at.asc())
  434. return await paginate(db, stmt, page, size)