settlement_service.py 20 KB

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