settlement_service.py 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366
  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.cost import VasCostRecord
  10. from app.models.fault import VasFaultCompensation
  11. from app.models.settlement import VasSettlementPeriod, VasSettlementDetail
  12. from app.schemas.settlement import (
  13. SettlementPreviewRequest,
  14. SettlementConfirmRequest,
  15. SettlementPreviewResponse,
  16. SettlementPayoutItem,
  17. SettlementOrderDetailOut,
  18. )
  19. from app.services.cost_service import CostService
  20. from app.core.biz_exception import BizLogicError, NotFoundError
  21. from app.utils.pagination import paginate
  22. class SettlementService:
  23. @staticmethod
  24. async def _query_eligible_orders(
  25. db: AsyncSession,
  26. start_date: datetime,
  27. end_date: datetime,
  28. enforce_risk_cleared: bool = False
  29. ) -> List[VasOrder]:
  30. """
  31. 获取账期内履约完成且具备结算资格的订单
  32. """
  33. # 使用 completed_at 作为履约完成时间,若为NULL则使用 updated_at
  34. stmt = select(VasOrder).where(
  35. VasOrder.status == 'completed',
  36. VasOrder.settlement_status != 'settled',
  37. func.coalesce(VasOrder.completed_at, VasOrder.updated_at) >= start_date,
  38. func.coalesce(VasOrder.completed_at, VasOrder.updated_at) <= end_date,
  39. )
  40. if enforce_risk_cleared:
  41. now = datetime.utcnow()
  42. stmt = stmt.where(
  43. (VasOrder.risk_cleared_at == None) | (VasOrder.risk_cleared_at <= now)
  44. )
  45. stmt = stmt.order_by(VasOrder.created_at.asc())
  46. return (await db.execute(stmt)).scalars().all()
  47. @staticmethod
  48. async def preview_settlement(
  49. db: AsyncSession,
  50. req: SettlementPreviewRequest
  51. ) -> SettlementPreviewResponse:
  52. """
  53. 试算账期分润(不修改数据库,仅预览计算结果)
  54. """
  55. orders = await SettlementService._query_eligible_orders(db, req.start_date, req.end_date)
  56. costs_summary = await CostService.get_costs_summary_by_date(db, req.start_date, req.end_date)
  57. total_revenue = sum(o.final_amount for o in orders)
  58. total_cost = sum(costs_summary.values())
  59. total_orders = len(orders)
  60. avg_cost = (total_cost // total_orders) if total_orders > 0 else 0
  61. net_profit = total_revenue - total_cost
  62. partner_a = req.partner_a_id or "partner_a"
  63. partner_b = req.partner_b_id or "partner_b"
  64. # 预加载当前账期涉及的所有商品与插件,技术分成按订单绑定的商品及处理插件开发管理者自动归属
  65. product_ids = {o.product_id for o in orders if o.product_id}
  66. products_map: Dict[int, VasProduct] = {}
  67. if product_ids:
  68. p_stmt = select(VasProduct).where(VasProduct.id.in_(product_ids))
  69. p_res = await db.execute(p_stmt)
  70. for prod in p_res.scalars().all():
  71. products_map[prod.id] = prod
  72. # 收集商品关联的处理插件
  73. plugin_ids = {prod.plugin_id for prod in products_map.values() if getattr(prod, 'plugin_id', None)}
  74. plugin_names = set()
  75. for prod in products_map.values():
  76. if getattr(prod, 'plugin_name', None) and prod.plugin_name.strip():
  77. plugin_names.add(prod.plugin_name.strip())
  78. if getattr(prod, 'provider', None) and prod.provider.strip():
  79. plugin_names.add(prod.provider.strip())
  80. plugins_map: Dict[str, Plugin] = {}
  81. if plugin_ids or plugin_names:
  82. conditions = []
  83. if plugin_ids:
  84. conditions.append(Plugin.id.in_(plugin_ids))
  85. if plugin_names:
  86. conditions.append(Plugin.name.in_(plugin_names))
  87. pl_stmt = select(Plugin).where(or_(*conditions))
  88. pl_res = await db.execute(pl_stmt)
  89. for pl in pl_res.scalars().all():
  90. plugins_map[str(pl.id)] = pl
  91. plugins_map[pl.name.lower()] = pl
  92. def resolve_order_developer(o: VasOrder) -> str:
  93. # 1. 订单本身若已记录明确的 developer_id
  94. if o.developer_id and o.developer_id.strip():
  95. return o.developer_id.strip()
  96. # 2. 从订单绑定的商品推断
  97. prod = products_map.get(o.product_id)
  98. if prod:
  99. # 2.1 商品直接指定了底层开发者
  100. if getattr(prod, 'developer_id', None) and prod.developer_id.strip():
  101. return prod.developer_id.strip()
  102. # 2.2 商品绑定了处理插件 -> 由插件开发管理者 (Plugin.developer_id) 获得技术分成
  103. pl = None
  104. if getattr(prod, 'plugin_id', None):
  105. pl = plugins_map.get(str(prod.plugin_id))
  106. if not pl and getattr(prod, 'plugin_name', None):
  107. pl = plugins_map.get(prod.plugin_name.strip().lower())
  108. if not pl and getattr(prod, 'provider', None):
  109. pl = plugins_map.get(prod.provider.strip().lower())
  110. if pl and getattr(pl, 'developer_id', None) and pl.developer_id.strip():
  111. return pl.developer_id.strip()
  112. if pl:
  113. return f"plugin_{pl.name}_dev"
  114. if getattr(prod, 'provider', None):
  115. return f"{prod.provider.lower()}_dev"
  116. # 3. 兜底
  117. return req.default_developer_id or "unassigned_tech"
  118. # 临时聚合各人的毛利润
  119. user_gross_map: Dict[str, Dict[str, int]] = {
  120. partner_a: {"pool": 0, "sales": 0, "tech": 0},
  121. partner_b: {"pool": 0, "sales": 0, "tech": 0},
  122. }
  123. order_details: List[SettlementOrderDetailOut] = []
  124. for o in orders:
  125. order_profit = o.final_amount - avg_cost
  126. dev_id = resolve_order_developer(o)
  127. if order_profit <= 0:
  128. # 亏损或保本单
  129. order_details.append(SettlementOrderDetailOut(
  130. order_id=o.id,
  131. channel_type=o.channel_type or "direct",
  132. agency_id=o.agency_id,
  133. handler_id=o.handler_id,
  134. developer_id=dev_id,
  135. order_amount=o.final_amount,
  136. avg_cost=avg_cost,
  137. net_profit=order_profit,
  138. tech_fee=0,
  139. sales_fee=0,
  140. pool_fee=0,
  141. partner_a_amount=0,
  142. partner_b_amount=0,
  143. ))
  144. continue
  145. tech_fee = int(order_profit * 0.20)
  146. sales_fee = int(order_profit * 0.40)
  147. pool_fee = order_profit - tech_fee - sales_fee
  148. part_a = pool_fee // 2
  149. part_b = pool_fee // 2
  150. if dev_id not in user_gross_map:
  151. user_gross_map[dev_id] = {"pool": 0, "sales": 0, "tech": 0}
  152. user_gross_map[dev_id]["tech"] += tech_fee
  153. # 销售提成归属逻辑
  154. if o.handler_id:
  155. # 社交自收直客单 / 中介单:归该对接人个人
  156. h_id = o.handler_id
  157. if h_id not in user_gross_map:
  158. user_gross_map[h_id] = {"pool": 0, "sales": 0, "tech": 0}
  159. user_gross_map[h_id]["sales"] += sales_fee
  160. else:
  161. # 官网公海单:甲乙平分 40% 销售提成
  162. part_a += sales_fee // 2
  163. part_b += sales_fee // 2
  164. user_gross_map[partner_a]["pool"] += part_a
  165. user_gross_map[partner_b]["pool"] += part_b
  166. order_details.append(SettlementOrderDetailOut(
  167. order_id=o.id,
  168. channel_type=o.channel_type or "direct",
  169. agency_id=o.agency_id,
  170. handler_id=o.handler_id,
  171. developer_id=dev_id,
  172. order_amount=o.final_amount,
  173. avg_cost=avg_cost,
  174. net_profit=order_profit,
  175. tech_fee=tech_fee,
  176. sales_fee=sales_fee,
  177. pool_fee=pool_fee,
  178. partner_a_amount=part_a,
  179. partner_b_amount=part_b,
  180. ))
  181. # 查询未核销的个人过错罚单,并从提成中扣除
  182. payout_summary: List[SettlementPayoutItem] = []
  183. for uid, breakdown in user_gross_map.items():
  184. total_user_gross = breakdown["pool"] + breakdown["sales"] + breakdown["tech"]
  185. if total_user_gross == 0:
  186. continue
  187. # 查询该用户待扣罚单
  188. fault_stmt = select(func.coalesce(func.sum(VasFaultCompensation.penalty_amount), 0)).where(
  189. VasFaultCompensation.liable_user_id == uid,
  190. VasFaultCompensation.deducted_status == 0
  191. )
  192. fault_penalty = int((await db.execute(fault_stmt)).scalar() or 0)
  193. roles = []
  194. if breakdown["pool"] > 0:
  195. roles.append(f"合伙分红:{breakdown['pool']/100:.2f}元")
  196. if breakdown["sales"] > 0:
  197. roles.append(f"销售提成:{breakdown['sales']/100:.2f}元")
  198. if breakdown["tech"] > 0:
  199. roles.append(f"技术版权:{breakdown['tech']/100:.2f}元")
  200. net_payout = max(0, total_user_gross - fault_penalty)
  201. payout_summary.append(SettlementPayoutItem(
  202. user_id=uid,
  203. role_description=" | ".join(roles),
  204. gross_amount=total_user_gross,
  205. fault_penalty=fault_penalty,
  206. net_payout=net_payout,
  207. ))
  208. return SettlementPreviewResponse(
  209. period_range=f"{req.start_date.strftime('%Y-%m-%d')} ~ {req.end_date.strftime('%Y-%m-%d')}",
  210. total_orders=total_orders,
  211. total_revenue=total_revenue,
  212. total_cost=total_cost,
  213. avg_cost=avg_cost,
  214. net_profit=net_profit,
  215. payout_summary=payout_summary,
  216. cost_breakdown=costs_summary,
  217. details=order_details,
  218. )
  219. @staticmethod
  220. async def confirm_settlement(
  221. db: AsyncSession,
  222. req: SettlementConfirmRequest,
  223. confirmed_by: str
  224. ) -> VasSettlementPeriod:
  225. """
  226. 正式确认并冻结账期分润(写入快照,锁定订单状态)
  227. """
  228. # 1. 检查账期ID是否已存在
  229. stmt = select(VasSettlementPeriod).where(VasSettlementPeriod.id == req.period_id)
  230. if (await db.execute(stmt)).scalar_one_or_none():
  231. raise BizLogicError(f"账期ID '{req.period_id}' 已存在,不可重复生成")
  232. # 2. 试算结果
  233. preview = await SettlementService.preview_settlement(
  234. db,
  235. SettlementPreviewRequest(
  236. start_date=req.start_date,
  237. end_date=req.end_date,
  238. partner_a_id=req.partner_a_id,
  239. partner_b_id=req.partner_b_id,
  240. default_developer_id=req.default_developer_id,
  241. )
  242. )
  243. now = datetime.utcnow()
  244. # 3. 创建账期总表
  245. period = VasSettlementPeriod(
  246. id=req.period_id,
  247. name=req.period_name,
  248. start_date=req.start_date,
  249. end_date=req.end_date,
  250. total_orders=preview.total_orders,
  251. total_revenue=preview.total_revenue,
  252. total_cost=preview.total_cost,
  253. avg_cost=preview.avg_cost,
  254. net_profit=preview.net_profit,
  255. status='confirmed',
  256. summary_data={
  257. "payout_summary": [p.dict() for p in preview.payout_summary],
  258. "cost_breakdown": preview.cost_breakdown,
  259. },
  260. confirmed_by=confirmed_by,
  261. confirmed_at=now,
  262. notes=req.notes,
  263. )
  264. db.add(period)
  265. # 4. 插入每单分润快照,并将对应订单状态置为 settled
  266. for d in preview.details:
  267. detail = VasSettlementDetail(
  268. period_id=req.period_id,
  269. order_id=d.order_id,
  270. channel_type=d.channel_type,
  271. agency_id=d.agency_id,
  272. handler_id=d.handler_id,
  273. developer_id=d.developer_id,
  274. order_amount=d.order_amount,
  275. avg_cost=d.avg_cost,
  276. net_profit=d.net_profit,
  277. tech_fee=d.tech_fee,
  278. sales_fee=d.sales_fee,
  279. pool_fee=d.pool_fee,
  280. partner_a_amount=d.partner_a_amount,
  281. partner_b_amount=d.partner_b_amount,
  282. snapshot=d.dict(),
  283. )
  284. db.add(detail)
  285. # 更新订单的结算归属
  286. await db.execute(
  287. update(VasOrder)
  288. .where(VasOrder.id == d.order_id)
  289. .values(
  290. settlement_status='settled',
  291. settlement_period_id=req.period_id
  292. )
  293. )
  294. # 5. 绑定当期耗材成本的 period_id
  295. await db.execute(
  296. update(VasCostRecord)
  297. .where(
  298. VasCostRecord.paid_at >= req.start_date,
  299. VasCostRecord.paid_at <= req.end_date,
  300. VasCostRecord.period_id == None
  301. )
  302. .values(period_id=req.period_id)
  303. )
  304. # 6. 将已参与结算扣除的过错记录置为已扣除
  305. await db.execute(
  306. update(VasFaultCompensation)
  307. .where(VasFaultCompensation.deducted_status == 0)
  308. .values(
  309. deducted_status=1,
  310. period_id=req.period_id
  311. )
  312. )
  313. await db.commit()
  314. await db.refresh(period)
  315. return period
  316. @staticmethod
  317. async def get_period(db: AsyncSession, period_id: str) -> Optional[VasSettlementPeriod]:
  318. stmt = select(VasSettlementPeriod).where(VasSettlementPeriod.id == period_id)
  319. return (await db.execute(stmt)).scalar_one_or_none()
  320. @staticmethod
  321. async def list_periods(db: AsyncSession, page: int = 0, size: int = 20):
  322. stmt = select(VasSettlementPeriod).order_by(VasSettlementPeriod.created_at.desc())
  323. return await paginate(db, stmt, page, size)
  324. @staticmethod
  325. async def list_period_details(db: AsyncSession, period_id: str, page: int = 0, size: int = 50):
  326. stmt = select(VasSettlementDetail).where(VasSettlementDetail.period_id == period_id)
  327. stmt = stmt.order_by(VasSettlementDetail.created_at.asc())
  328. return await paginate(db, stmt, page, size)