pl_contract_plan_qty.py 44 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296129712981299130013011302130313041305130613071308130913101311131213131314131513161317131813191320132113221323132413251326132713281329133013311332133313341335133613371338133913401341134213431344134513461347134813491350135113521353135413551356135713581359136013611362136313641365136613671368
  1. """合同产品在运输计划上的安排数量汇总。"""
  2. from __future__ import annotations
  3. from collections import defaultdict
  4. from typing import Any, DefaultDict, Dict, Iterable, Optional, Set, Tuple
  5. from sqlalchemy.orm import Session
  6. from app.models.pl_models import (
  7. PlPlan,
  8. PlPlanContract,
  9. PlPlanDelivery,
  10. PlPlanDeliveryProduct,
  11. PlPlanLoad,
  12. PlPlanLoadProduct,
  13. )
  14. from app.services.pl_plan_service import (
  15. PLAN_CONTRACT_SIDE_DEMANDER,
  16. PLAN_CONTRACT_SIDE_SUPPLIER,
  17. )
  18. from app.services.pl_plan_stop_service import (
  19. PLAN_CONTRACT_SIDE_DELIVERY,
  20. PLAN_CONTRACT_SIDE_LOAD,
  21. )
  22. from app.utils.crm_package_spec import decode_package_specs
  23. # 待安排 / 等通知 / 作废:不计入「已安排」(见 _bucket_for_plan)
  24. ProductPackageKey = Tuple[str, str]
  25. def normalize_package_spec(value: Any) -> str:
  26. if value is None:
  27. return ""
  28. return str(value).strip()
  29. def product_package_key(product_id: Any, package_spec: Any = None) -> Optional[ProductPackageKey]:
  30. pid = str(product_id or "").strip()
  31. if not pid:
  32. return None
  33. return (pid, normalize_package_spec(package_spec))
  34. def _to_float(value: Any) -> float:
  35. if value is None:
  36. return 0.0
  37. try:
  38. return float(value)
  39. except (TypeError, ValueError):
  40. return 0.0
  41. def contract_line_package_specs(row: Any) -> list[str]:
  42. """从合同产品行(ORM / dict / SimpleNamespace)解析包装列表。"""
  43. if isinstance(row, dict):
  44. raw_list = row.get("packageSpecs") or row.get("package_specs")
  45. if isinstance(raw_list, (list, tuple)) and raw_list:
  46. return decode_package_specs(raw_list)
  47. return decode_package_specs(row.get("packageSpec") or row.get("package_spec"))
  48. raw_list = getattr(row, "package_specs", None)
  49. if isinstance(raw_list, (list, tuple)) and raw_list:
  50. return decode_package_specs(raw_list)
  51. return decode_package_specs(getattr(row, "package_spec", None))
  52. def pool_keys_for_contract_line(row: Any) -> list[ProductPackageKey]:
  53. pid = ""
  54. if isinstance(row, dict):
  55. pid = str(row.get("productId") or row.get("product_id") or "").strip()
  56. else:
  57. pid = str(getattr(row, "product_id", None) or "").strip()
  58. if not pid:
  59. return []
  60. keys: list[ProductPackageKey] = []
  61. for spec in contract_line_package_specs(row):
  62. key = product_package_key(pid, spec)
  63. if key:
  64. keys.append(key)
  65. return keys
  66. def sum_qty_stats_for_keys(
  67. qty_map: Dict[ProductPackageKey, Dict[str, float]],
  68. keys: Iterable[ProductPackageKey],
  69. ) -> Dict[str, float]:
  70. archived = 0.0
  71. unarchived = 0.0
  72. for key in keys or []:
  73. stats = qty_map.get(key) or {}
  74. archived += _to_float(stats.get("archivedQty"))
  75. unarchived += _to_float(stats.get("arrangedUnarchivedQty"))
  76. archived = round(archived, 3)
  77. unarchived = round(unarchived, 3)
  78. return {
  79. "archivedQty": archived,
  80. "arrangedUnarchivedQty": unarchived,
  81. "arrangedTotalQty": round(archived + unarchived, 3),
  82. }
  83. def match_contract_product_for_key(
  84. products: Iterable[Any],
  85. key: Optional[ProductPackageKey],
  86. ) -> Optional[Any]:
  87. """按 productId + 包装∈行内集合 匹配合同产品行;同包装脏数据多行时 quantity 相加。"""
  88. if not key:
  89. return None
  90. pid, pkg = key[0], key[1]
  91. matched = []
  92. for p in products or []:
  93. row_pid = ""
  94. if isinstance(p, dict):
  95. row_pid = str(p.get("productId") or p.get("product_id") or "").strip()
  96. else:
  97. row_pid = str(getattr(p, "product_id", None) or "").strip()
  98. if row_pid != pid:
  99. continue
  100. specs = contract_line_package_specs(p)
  101. if pkg in specs:
  102. matched.append(p)
  103. if not matched:
  104. return None
  105. if len(matched) == 1:
  106. return matched[0]
  107. from types import SimpleNamespace
  108. total_qty = 0.0
  109. for p in matched:
  110. if isinstance(p, dict):
  111. total_qty += _to_float(p.get("quantity"))
  112. else:
  113. total_qty += _to_float(getattr(p, "quantity", None))
  114. base = matched[0]
  115. if isinstance(base, dict):
  116. return SimpleNamespace(
  117. id=base.get("id"),
  118. product_id=base.get("productId") or base.get("product_id"),
  119. package_spec=encode_specs_for_attr(contract_line_package_specs(base)),
  120. product_name=base.get("productName") or base.get("product_name"),
  121. model=base.get("model"),
  122. unit=base.get("unit"),
  123. quantity=total_qty,
  124. quantity_mode=base.get("quantityMode") or base.get("quantity_mode"),
  125. )
  126. return SimpleNamespace(
  127. id=getattr(base, "id", None),
  128. product_id=getattr(base, "product_id", None),
  129. package_spec=getattr(base, "package_spec", None),
  130. product_name=getattr(base, "product_name", None),
  131. model=getattr(base, "model", None),
  132. unit=getattr(base, "unit", None),
  133. quantity=total_qty,
  134. quantity_mode=getattr(base, "quantity_mode", None),
  135. )
  136. def encode_specs_for_attr(specs: list[str]) -> str:
  137. from app.utils.crm_package_spec import encode_package_specs
  138. return encode_package_specs(specs)
  139. def _bucket_for_plan(*, archive_status: Optional[str], follow_status: Optional[str], transport_status: Optional[str]) -> Optional[str]:
  140. """返回 'archived' | 'arranged_unarchived' | None(不计入)。"""
  141. follow = (follow_status or "").strip()
  142. if follow == "cancelled" or follow in ("wait_notice", "pending_arrange"):
  143. return None
  144. archive = (archive_status or "").strip() or "unarchived"
  145. if archive == "archived":
  146. return "archived"
  147. # 已安排未归档(含 archive draft 暂存)
  148. transport = (transport_status or "").strip()
  149. if transport == "arranged" or follow in ("arranged", "completed"):
  150. return "arranged_unarchived"
  151. return None
  152. def merge_product_qty_maps(
  153. *,
  154. archived: Dict[ProductPackageKey, float],
  155. arranged_unarchived: Dict[ProductPackageKey, float],
  156. ) -> Dict[ProductPackageKey, Dict[str, float]]:
  157. """(product_id, package_spec) -> {archivedQty, arrangedUnarchivedQty, arrangedTotalQty}"""
  158. keys = set(archived) | set(arranged_unarchived)
  159. out: Dict[ProductPackageKey, Dict[str, float]] = {}
  160. for key in keys:
  161. a = round(float(archived.get(key, 0.0)), 3)
  162. u = round(float(arranged_unarchived.get(key, 0.0)), 3)
  163. out[key] = {
  164. "archivedQty": a,
  165. "arrangedUnarchivedQty": u,
  166. "arrangedTotalQty": round(a + u, 3),
  167. }
  168. return out
  169. # plan:计划数量;archive_actual:装货 inbound / 送货 outbound,null 回退 plan_qty
  170. QTY_MODE_PLAN = "plan"
  171. QTY_MODE_ARCHIVE_ACTUAL = "archive_actual"
  172. def _resolve_stop_product_qty(
  173. row: Any,
  174. *,
  175. stop_kind: str,
  176. qty_mode: str,
  177. ) -> float:
  178. """按模式解析段产品计入量。"""
  179. if qty_mode == QTY_MODE_ARCHIVE_ACTUAL:
  180. attr = "inbound_qty" if stop_kind == "load" else "outbound_qty"
  181. raw = getattr(row, attr, None)
  182. if raw is not None:
  183. return _to_float(raw)
  184. return _to_float(getattr(row, "plan_qty", None))
  185. return _to_float(getattr(row, "plan_qty", None))
  186. def accumulate_stop_products(
  187. target: DefaultDict[ProductPackageKey, float],
  188. products: Iterable[Any],
  189. *,
  190. stop_kind: str = "load",
  191. qty_mode: str = QTY_MODE_PLAN,
  192. ) -> None:
  193. for row in products or []:
  194. key = product_package_key(
  195. getattr(row, "product_id", None),
  196. getattr(row, "package_spec", None),
  197. )
  198. if not key:
  199. continue
  200. qty = _resolve_stop_product_qty(row, stop_kind=stop_kind, qty_mode=qty_mode)
  201. if qty:
  202. target[key] += qty
  203. def resolve_linked_plan_scopes(
  204. link_rows: Iterable[PlPlanContract],
  205. legacy_plan_ids: Iterable[str],
  206. ) -> Dict[str, Dict[str, Any]]:
  207. """
  208. plan_id -> {
  209. load_ids: set[str] | None, # None = 该侧全部装货段
  210. delivery_ids: set[str] | None,
  211. use_all_stops: bool, # 遗留主表合同:优先送货段,否则装货段,各产品只计一次
  212. }
  213. """
  214. scopes: Dict[str, Dict[str, Any]] = {}
  215. def _ensure(plan_id: str) -> Dict[str, Any]:
  216. if plan_id not in scopes:
  217. scopes[plan_id] = {
  218. "load_ids": set(),
  219. "delivery_ids": set(),
  220. "use_all_stops": False,
  221. "has_explicit": False,
  222. }
  223. return scopes[plan_id]
  224. for row in link_rows or []:
  225. plan_id = str(getattr(row, "plan_id", None) or "").strip()
  226. if not plan_id:
  227. continue
  228. scope = _ensure(plan_id)
  229. side = (getattr(row, "side", None) or "").strip()
  230. stop_id = str(getattr(row, "stop_id", None) or "").strip()
  231. scope["has_explicit"] = True
  232. if side in (PLAN_CONTRACT_SIDE_LOAD, PLAN_CONTRACT_SIDE_SUPPLIER):
  233. if stop_id:
  234. scope["load_ids"].add(stop_id)
  235. else:
  236. scope["load_ids"] = None # type: ignore[assignment]
  237. elif side in (PLAN_CONTRACT_SIDE_DELIVERY, PLAN_CONTRACT_SIDE_DEMANDER):
  238. if stop_id:
  239. scope["delivery_ids"].add(stop_id)
  240. else:
  241. scope["delivery_ids"] = None # type: ignore[assignment]
  242. elif stop_id:
  243. # 未知 side 但有 stop:两边都尝试匹配时由调用方按 id 过滤
  244. scope["load_ids"].add(stop_id)
  245. scope["delivery_ids"].add(stop_id)
  246. for plan_id in legacy_plan_ids or []:
  247. pid = str(plan_id or "").strip()
  248. if not pid:
  249. continue
  250. scope = _ensure(pid)
  251. if not scope["has_explicit"]:
  252. scope["use_all_stops"] = True
  253. return scopes
  254. def sum_qtys_for_scope(
  255. *,
  256. load_products_by_load: Dict[str, list],
  257. delivery_products_by_delivery: Dict[str, list],
  258. all_load_ids: Set[str],
  259. all_delivery_ids: Set[str],
  260. scope: Dict[str, Any],
  261. qty_mode: str = QTY_MODE_PLAN,
  262. ) -> Dict[ProductPackageKey, float]:
  263. acc: DefaultDict[ProductPackageKey, float] = defaultdict(float)
  264. if scope.get("use_all_stops"):
  265. # 遗留:优先送货段数量;无送货则用装货,避免装+送双计
  266. if all_delivery_ids:
  267. for did in all_delivery_ids:
  268. accumulate_stop_products(
  269. acc,
  270. delivery_products_by_delivery.get(did, []),
  271. stop_kind="delivery",
  272. qty_mode=qty_mode,
  273. )
  274. else:
  275. for lid in all_load_ids:
  276. accumulate_stop_products(
  277. acc,
  278. load_products_by_load.get(lid, []),
  279. stop_kind="load",
  280. qty_mode=qty_mode,
  281. )
  282. return dict(acc)
  283. load_ids = scope.get("load_ids")
  284. delivery_ids = scope.get("delivery_ids")
  285. if load_ids is None:
  286. for lid in all_load_ids:
  287. accumulate_stop_products(
  288. acc,
  289. load_products_by_load.get(lid, []),
  290. stop_kind="load",
  291. qty_mode=qty_mode,
  292. )
  293. elif load_ids:
  294. for lid in load_ids:
  295. accumulate_stop_products(
  296. acc,
  297. load_products_by_load.get(lid, []),
  298. stop_kind="load",
  299. qty_mode=qty_mode,
  300. )
  301. if delivery_ids is None:
  302. for did in all_delivery_ids:
  303. accumulate_stop_products(
  304. acc,
  305. delivery_products_by_delivery.get(did, []),
  306. stop_kind="delivery",
  307. qty_mode=qty_mode,
  308. )
  309. elif delivery_ids:
  310. for did in delivery_ids:
  311. accumulate_stop_products(
  312. acc,
  313. delivery_products_by_delivery.get(did, []),
  314. stop_kind="delivery",
  315. qty_mode=qty_mode,
  316. )
  317. return dict(acc)
  318. def aggregate_contract_product_plan_qtys(
  319. db: Session,
  320. *,
  321. tenant_no: str,
  322. contract_id: str,
  323. ) -> Dict[ProductPackageKey, Dict[str, float]]:
  324. """返回 (productId, packageSpec) -> 三数量字段。"""
  325. cid = (contract_id or "").strip()
  326. if not cid:
  327. return {}
  328. link_rows = (
  329. db.query(PlPlanContract)
  330. .filter(
  331. PlPlanContract.tenant_no == tenant_no,
  332. PlPlanContract.del_flag == 0,
  333. PlPlanContract.contract_id == cid,
  334. )
  335. .all()
  336. )
  337. link_plan_ids = {str(r.plan_id) for r in link_rows if r.plan_id}
  338. legacy_plans = (
  339. db.query(PlPlan)
  340. .filter(
  341. PlPlan.tenant_no == tenant_no,
  342. PlPlan.del_flag == 0,
  343. PlPlan.contract_id == cid,
  344. )
  345. .all()
  346. )
  347. legacy_only_ids = {p.id for p in legacy_plans if p.id not in link_plan_ids}
  348. scopes = resolve_linked_plan_scopes(link_rows, legacy_only_ids)
  349. plan_ids = list(scopes.keys())
  350. if not plan_ids:
  351. return {}
  352. plans = (
  353. db.query(PlPlan)
  354. .filter(
  355. PlPlan.tenant_no == tenant_no,
  356. PlPlan.del_flag == 0,
  357. PlPlan.id.in_(plan_ids),
  358. PlPlan.follow_status != "cancelled",
  359. )
  360. .all()
  361. )
  362. plan_by_id = {p.id: p for p in plans}
  363. loads = (
  364. db.query(PlPlanLoad)
  365. .filter(
  366. PlPlanLoad.tenant_no == tenant_no,
  367. PlPlanLoad.del_flag == 0,
  368. PlPlanLoad.plan_id.in_(plan_ids),
  369. )
  370. .all()
  371. )
  372. deliveries = (
  373. db.query(PlPlanDelivery)
  374. .filter(
  375. PlPlanDelivery.tenant_no == tenant_no,
  376. PlPlanDelivery.del_flag == 0,
  377. PlPlanDelivery.plan_id.in_(plan_ids),
  378. )
  379. .all()
  380. )
  381. load_ids_by_plan: DefaultDict[str, Set[str]] = defaultdict(set)
  382. delivery_ids_by_plan: DefaultDict[str, Set[str]] = defaultdict(set)
  383. for row in loads:
  384. load_ids_by_plan[row.plan_id].add(row.id)
  385. for row in deliveries:
  386. delivery_ids_by_plan[row.plan_id].add(row.id)
  387. all_load_ids = {row.id for row in loads}
  388. all_delivery_ids = {row.id for row in deliveries}
  389. load_products_by_load: DefaultDict[str, list] = defaultdict(list)
  390. if all_load_ids:
  391. for prod in (
  392. db.query(PlPlanLoadProduct)
  393. .filter(
  394. PlPlanLoadProduct.tenant_no == tenant_no,
  395. PlPlanLoadProduct.del_flag == 0,
  396. PlPlanLoadProduct.load_id.in_(all_load_ids),
  397. )
  398. .all()
  399. ):
  400. load_products_by_load[prod.load_id].append(prod)
  401. delivery_products_by_delivery: DefaultDict[str, list] = defaultdict(list)
  402. if all_delivery_ids:
  403. for prod in (
  404. db.query(PlPlanDeliveryProduct)
  405. .filter(
  406. PlPlanDeliveryProduct.tenant_no == tenant_no,
  407. PlPlanDeliveryProduct.del_flag == 0,
  408. PlPlanDeliveryProduct.delivery_id.in_(all_delivery_ids),
  409. )
  410. .all()
  411. ):
  412. delivery_products_by_delivery[prod.delivery_id].append(prod)
  413. archived: DefaultDict[ProductPackageKey, float] = defaultdict(float)
  414. arranged_unarchived: DefaultDict[ProductPackageKey, float] = defaultdict(float)
  415. for plan_id, scope in scopes.items():
  416. plan = plan_by_id.get(plan_id)
  417. if not plan:
  418. continue
  419. bucket = _bucket_for_plan(
  420. archive_status=plan.archive_status,
  421. follow_status=plan.follow_status,
  422. transport_status=plan.transport_status,
  423. )
  424. if not bucket:
  425. continue
  426. qty_mode = (
  427. QTY_MODE_ARCHIVE_ACTUAL if bucket == "archived" else QTY_MODE_PLAN
  428. )
  429. qtys = sum_qtys_for_scope(
  430. load_products_by_load=load_products_by_load,
  431. delivery_products_by_delivery=delivery_products_by_delivery,
  432. all_load_ids=load_ids_by_plan.get(plan_id, set()),
  433. all_delivery_ids=delivery_ids_by_plan.get(plan_id, set()),
  434. scope=scope,
  435. qty_mode=qty_mode,
  436. )
  437. target = archived if bucket == "archived" else arranged_unarchived
  438. for pid, qty in qtys.items():
  439. target[pid] += qty
  440. return merge_product_qty_maps(archived=dict(archived), arranged_unarchived=dict(arranged_unarchived))
  441. def attach_plan_qty_fields_to_products(
  442. products: list,
  443. qty_by_product_id: Dict[ProductPackageKey, Dict[str, float]],
  444. ) -> None:
  445. zeros = {
  446. "archivedQty": 0.0,
  447. "arrangedUnarchivedQty": 0.0,
  448. "arrangedTotalQty": 0.0,
  449. }
  450. for item in products or []:
  451. if not isinstance(item, dict):
  452. continue
  453. specs = contract_line_package_specs(item)
  454. item["packageSpecs"] = specs
  455. if specs:
  456. item["packageSpec"] = specs[0]
  457. elif not item.get("packageSpec"):
  458. item["packageSpec"] = ""
  459. keys = pool_keys_for_contract_line(item)
  460. breakdown: list[Dict[str, Any]] = []
  461. for key in keys:
  462. stats = (qty_by_product_id.get(key) if key else None) or zeros
  463. breakdown.append(
  464. {
  465. "packageSpec": key[1] if key else "",
  466. "archivedQty": stats["archivedQty"],
  467. "arrangedUnarchivedQty": stats["arrangedUnarchivedQty"],
  468. "arrangedTotalQty": stats["arrangedTotalQty"],
  469. }
  470. )
  471. line_stats = sum_qty_stats_for_keys(qty_by_product_id, keys) if keys else dict(zeros)
  472. item["archivedQty"] = line_stats["archivedQty"]
  473. item["arrangedUnarchivedQty"] = line_stats["arrangedUnarchivedQty"]
  474. item["arrangedTotalQty"] = line_stats["arrangedTotalQty"]
  475. item["packageQtyBreakdown"] = breakdown
  476. line_qty = _to_float(item.get("quantity"))
  477. if line_qty > 0:
  478. item["remainingQty"] = round(line_qty - line_stats["arrangedTotalQty"], 3)
  479. else:
  480. item["remainingQty"] = None
  481. def plan_ids_linked_to_contract(
  482. db: Session,
  483. *,
  484. tenant_no: str,
  485. contract_id: str,
  486. ) -> Set[str]:
  487. cid = (contract_id or "").strip()
  488. if not cid:
  489. return set()
  490. ids: Set[str] = set()
  491. for (plan_id,) in (
  492. db.query(PlPlanContract.plan_id)
  493. .filter(
  494. PlPlanContract.tenant_no == tenant_no,
  495. PlPlanContract.del_flag == 0,
  496. PlPlanContract.contract_id == cid,
  497. )
  498. .distinct()
  499. .all()
  500. ):
  501. if plan_id:
  502. ids.add(str(plan_id))
  503. for (plan_id,) in (
  504. db.query(PlPlan.id)
  505. .filter(
  506. PlPlan.tenant_no == tenant_no,
  507. PlPlan.del_flag == 0,
  508. PlPlan.contract_id == cid,
  509. )
  510. .all()
  511. ):
  512. if plan_id:
  513. ids.add(str(plan_id))
  514. return ids
  515. def contract_ids_linked_to_plan(
  516. db: Session,
  517. *,
  518. tenant_no: str,
  519. plan: PlPlan,
  520. ) -> Set[str]:
  521. ids: Set[str] = set()
  522. if plan.contract_id:
  523. ids.add(str(plan.contract_id).strip())
  524. for (cid,) in (
  525. db.query(PlPlanContract.contract_id)
  526. .filter(
  527. PlPlanContract.tenant_no == tenant_no,
  528. PlPlanContract.del_flag == 0,
  529. PlPlanContract.plan_id == plan.id,
  530. )
  531. .distinct()
  532. .all()
  533. ):
  534. if cid:
  535. ids.add(str(cid).strip())
  536. return {x for x in ids if x}
  537. def auto_start_execute_approved_contracts(
  538. db: Session,
  539. *,
  540. tenant_no: str,
  541. plan: PlPlan,
  542. user_id: Optional[str] = None,
  543. ) -> list[str]:
  544. """将关联且状态为 approved 的合同置为 executing(不 commit)。返回已推进的合同 id。"""
  545. from datetime import datetime
  546. from app.models.crm_models import CrmContract
  547. started: list[str] = []
  548. for cid in contract_ids_linked_to_plan(db, tenant_no=tenant_no, plan=plan):
  549. row = (
  550. db.query(CrmContract)
  551. .filter(
  552. CrmContract.id == cid,
  553. CrmContract.tenant_no == tenant_no,
  554. CrmContract.del_flag == 0,
  555. )
  556. .first()
  557. )
  558. if not row or row.status != "approved":
  559. continue
  560. row.status = "executing"
  561. row.update_by = user_id
  562. row.update_time = datetime.now()
  563. started.append(cid)
  564. return started
  565. # =============================================================================
  566. # 安排运输:合同数量硬拦 / 成功后提醒
  567. # =============================================================================
  568. FRAMEWORK_CONTRACT_TYPES = frozenset({"framework", "framework_sale", "framework_purchase"})
  569. def effective_arranged_total(
  570. *,
  571. arranged_total: float,
  572. old_plan_qty: float,
  573. new_plan_qty: float,
  574. ) -> float:
  575. """已安排合计 − 本计划旧量 + 本计划新量。"""
  576. return round(
  577. _to_float(arranged_total) - _to_float(old_plan_qty) + _to_float(new_plan_qty),
  578. 3,
  579. )
  580. def contract_product_has_fixed_qty(
  581. *,
  582. contract_type: Optional[str],
  583. quantity: Any,
  584. quantity_mode: Optional[str],
  585. ) -> bool:
  586. """合同产品行是否按固定数量做超量硬拦。"""
  587. ctype = (contract_type or "").strip()
  588. mode = (quantity_mode or "").strip() or "fixed"
  589. if ctype in FRAMEWORK_CONTRACT_TYPES and mode == "customer_demand":
  590. return False
  591. qty = _to_float(quantity)
  592. return qty > 0
  593. def evaluate_product_qty_for_arrange(
  594. *,
  595. has_fixed_qty: bool,
  596. contract_qty: Optional[float],
  597. arranged_total: float,
  598. old_plan_qty: float,
  599. new_plan_qty: float,
  600. ) -> Optional[Dict[str, Any]]:
  601. """
  602. 返回:
  603. - None: 跳过(本次无量)
  604. - kind=violation | remaining | framework_cumulative
  605. """
  606. new_qty = _to_float(new_plan_qty)
  607. if new_qty <= 0:
  608. return None
  609. old_qty = _to_float(old_plan_qty)
  610. arranged = _to_float(arranged_total)
  611. effective = effective_arranged_total(
  612. arranged_total=arranged,
  613. old_plan_qty=old_qty,
  614. new_plan_qty=new_qty,
  615. )
  616. if not has_fixed_qty:
  617. return {
  618. "kind": "framework_cumulative",
  619. "arrangedTotalQty": effective,
  620. "thisPlanQty": round(new_qty, 3),
  621. "effectiveTotal": effective,
  622. }
  623. cq = _to_float(contract_qty)
  624. overflow = round(effective - cq, 3)
  625. base = {
  626. "contractQty": round(cq, 3),
  627. "arrangedTotalQty": effective,
  628. "thisPlanQty": round(new_qty, 3),
  629. "oldPlanQty": round(old_qty, 3),
  630. "newPlanQty": round(new_qty, 3),
  631. "effectiveTotal": effective,
  632. }
  633. if overflow > 0:
  634. return {**base, "kind": "violation", "overflowQty": overflow}
  635. return {
  636. **base,
  637. "kind": "remaining",
  638. "remainingQty": round(cq - effective, 3),
  639. }
  640. CONTRACT_QTY_VIOLATION_CODE = "CONTRACT_QTY_OVERFLOW"
  641. def _stop_kind_from_scope(scope: Dict[str, Any]) -> str:
  642. load_ids = scope.get("load_ids")
  643. delivery_ids = scope.get("delivery_ids")
  644. has_load = load_ids is None or bool(load_ids)
  645. has_delivery = delivery_ids is None or bool(delivery_ids)
  646. if scope.get("use_all_stops"):
  647. return "delivery"
  648. if has_load and not has_delivery:
  649. return "load"
  650. if has_delivery and not has_load:
  651. return "delivery"
  652. if has_load:
  653. return "load"
  654. return "delivery"
  655. def _plan_scope_for_contract(
  656. *,
  657. plan_id: str,
  658. contract_id: str,
  659. link_rows: list[PlPlanContract],
  660. legacy_contract_id: Optional[str],
  661. ) -> Dict[str, Any]:
  662. cid = (contract_id or "").strip()
  663. links = [r for r in link_rows if str(r.contract_id or "").strip() == cid]
  664. legacy_ids: list[str] = []
  665. if not links and legacy_contract_id and str(legacy_contract_id).strip() == cid:
  666. legacy_ids = [plan_id]
  667. scopes = resolve_linked_plan_scopes(links, legacy_ids)
  668. return scopes.get(plan_id) or {
  669. "load_ids": set(),
  670. "delivery_ids": set(),
  671. "use_all_stops": False,
  672. "has_explicit": False,
  673. }
  674. def _load_plan_stop_product_maps(
  675. db: Session,
  676. *,
  677. tenant_no: str,
  678. plan_id: str,
  679. ) -> tuple[Set[str], Set[str], Dict[str, list], Dict[str, list]]:
  680. loads = (
  681. db.query(PlPlanLoad)
  682. .filter(
  683. PlPlanLoad.tenant_no == tenant_no,
  684. PlPlanLoad.del_flag == 0,
  685. PlPlanLoad.plan_id == plan_id,
  686. )
  687. .all()
  688. )
  689. deliveries = (
  690. db.query(PlPlanDelivery)
  691. .filter(
  692. PlPlanDelivery.tenant_no == tenant_no,
  693. PlPlanDelivery.del_flag == 0,
  694. PlPlanDelivery.plan_id == plan_id,
  695. )
  696. .all()
  697. )
  698. load_ids = {row.id for row in loads}
  699. delivery_ids = {row.id for row in deliveries}
  700. load_products_by_load: DefaultDict[str, list] = defaultdict(list)
  701. if load_ids:
  702. for prod in (
  703. db.query(PlPlanLoadProduct)
  704. .filter(
  705. PlPlanLoadProduct.tenant_no == tenant_no,
  706. PlPlanLoadProduct.del_flag == 0,
  707. PlPlanLoadProduct.load_id.in_(load_ids),
  708. )
  709. .all()
  710. ):
  711. load_products_by_load[prod.load_id].append(prod)
  712. delivery_products_by_delivery: DefaultDict[str, list] = defaultdict(list)
  713. if delivery_ids:
  714. for prod in (
  715. db.query(PlPlanDeliveryProduct)
  716. .filter(
  717. PlPlanDeliveryProduct.tenant_no == tenant_no,
  718. PlPlanDeliveryProduct.del_flag == 0,
  719. PlPlanDeliveryProduct.delivery_id.in_(delivery_ids),
  720. )
  721. .all()
  722. ):
  723. delivery_products_by_delivery[prod.delivery_id].append(prod)
  724. return load_ids, delivery_ids, dict(load_products_by_load), dict(delivery_products_by_delivery)
  725. def collect_plan_contract_qty_checks(
  726. db: Session,
  727. *,
  728. tenant_no: str,
  729. plan_id: str,
  730. ) -> tuple[list[Dict[str, Any]], list[Dict[str, Any]]]:
  731. """
  732. 返回 (violations, reminders)。
  733. 在写运输前调用;reminders 可缓存到成功响应,勿在状态变更后再算。
  734. """
  735. from app.models.crm_models import CrmContract, CrmContractProduct
  736. plan = (
  737. db.query(PlPlan)
  738. .filter(
  739. PlPlan.id == plan_id,
  740. PlPlan.tenant_no == tenant_no,
  741. PlPlan.del_flag == 0,
  742. )
  743. .first()
  744. )
  745. if not plan:
  746. return [], []
  747. link_rows = (
  748. db.query(PlPlanContract)
  749. .filter(
  750. PlPlanContract.tenant_no == tenant_no,
  751. PlPlanContract.del_flag == 0,
  752. PlPlanContract.plan_id == plan_id,
  753. )
  754. .all()
  755. )
  756. contract_ids: Set[str] = set()
  757. for row in link_rows:
  758. cid = str(row.contract_id or "").strip()
  759. if cid:
  760. contract_ids.add(cid)
  761. legacy_cid = str(plan.contract_id or "").strip()
  762. if legacy_cid:
  763. contract_ids.add(legacy_cid)
  764. if not contract_ids:
  765. return [], []
  766. load_ids, delivery_ids, load_products_by_load, delivery_products_by_delivery = (
  767. _load_plan_stop_product_maps(db, tenant_no=tenant_no, plan_id=plan_id)
  768. )
  769. in_bucket = (
  770. _bucket_for_plan(
  771. archive_status=plan.archive_status,
  772. follow_status=plan.follow_status,
  773. transport_status=plan.transport_status,
  774. )
  775. is not None
  776. )
  777. violations: list[Dict[str, Any]] = []
  778. reminders: list[Dict[str, Any]] = []
  779. for cid in sorted(contract_ids):
  780. contract = (
  781. db.query(CrmContract)
  782. .filter(
  783. CrmContract.id == cid,
  784. CrmContract.tenant_no == tenant_no,
  785. CrmContract.del_flag == 0,
  786. )
  787. .first()
  788. )
  789. if not contract:
  790. continue
  791. scope = _plan_scope_for_contract(
  792. plan_id=plan_id,
  793. contract_id=cid,
  794. link_rows=link_rows,
  795. legacy_contract_id=legacy_cid,
  796. )
  797. plan_qtys = sum_qtys_for_scope(
  798. load_products_by_load=load_products_by_load,
  799. delivery_products_by_delivery=delivery_products_by_delivery,
  800. all_load_ids=load_ids,
  801. all_delivery_ids=delivery_ids,
  802. scope=scope,
  803. )
  804. if not plan_qtys:
  805. continue
  806. qty_map = aggregate_contract_product_plan_qtys(
  807. db, tenant_no=tenant_no, contract_id=cid
  808. )
  809. products = (
  810. db.query(CrmContractProduct)
  811. .filter(
  812. CrmContractProduct.contract_id == cid,
  813. CrmContractProduct.tenant_no == tenant_no,
  814. CrmContractProduct.del_flag == 0,
  815. )
  816. .all()
  817. )
  818. stop_kind = _stop_kind_from_scope(scope)
  819. contract_type = (contract.contract_type or "").strip()
  820. contract_no = contract.contract_no or ""
  821. customer_name = (contract.customer_name or "").strip()
  822. line_groups: Dict[str, Dict[str, Any]] = {}
  823. for key, new_qty_raw in plan_qtys.items():
  824. new_qty = _to_float(new_qty_raw)
  825. if new_qty <= 0:
  826. continue
  827. if not isinstance(key, tuple) or len(key) != 2:
  828. continue
  829. pid, pkg = key[0], key[1]
  830. cp = match_contract_product_for_key(products, key)
  831. # 计划产品不在合同明细:不校验数量(归属另有规则)
  832. if not cp:
  833. continue
  834. line_id = str(getattr(cp, "id", None) or "").strip()
  835. if not line_id:
  836. specs = contract_line_package_specs(cp)
  837. line_id = f"{pid}\0{'|'.join(specs)}"
  838. group = line_groups.get(line_id)
  839. if not group:
  840. group = {
  841. "cp": cp,
  842. "pid": pid,
  843. "pool_keys": pool_keys_for_contract_line(cp),
  844. "this_plan_qty": 0.0,
  845. "packages": [],
  846. }
  847. line_groups[line_id] = group
  848. group["this_plan_qty"] += new_qty
  849. if pkg:
  850. group["packages"].append(pkg)
  851. for group in line_groups.values():
  852. cp = group["cp"]
  853. pid = group["pid"]
  854. new_qty = group["this_plan_qty"]
  855. pkg_label = "、".join(dict.fromkeys(group["packages"])) or None
  856. pool_stats = sum_qty_stats_for_keys(qty_map, group["pool_keys"])
  857. arranged_total = pool_stats["arrangedTotalQty"]
  858. if in_bucket:
  859. old_qty = new_qty
  860. # arranged 已含当前库内量;effective = arranged - old + new = arranged
  861. else:
  862. old_qty = 0.0
  863. has_fixed = contract_product_has_fixed_qty(
  864. contract_type=contract_type,
  865. quantity=cp.quantity,
  866. quantity_mode=cp.quantity_mode,
  867. )
  868. evaluated = evaluate_product_qty_for_arrange(
  869. has_fixed_qty=has_fixed,
  870. contract_qty=_to_float(cp.quantity) if has_fixed else None,
  871. arranged_total=arranged_total,
  872. old_plan_qty=old_qty,
  873. new_plan_qty=new_qty,
  874. )
  875. if not evaluated:
  876. continue
  877. item = {
  878. "contractId": cid,
  879. "contractNo": contract_no,
  880. "contractType": contract_type,
  881. "customerName": customer_name,
  882. "stopKind": stop_kind,
  883. "productId": pid,
  884. "packageSpec": pkg_label,
  885. "packageSpecs": contract_line_package_specs(cp),
  886. "productName": cp.product_name or "",
  887. "model": (cp.model or "").strip() or None,
  888. "unit": (cp.unit or "").strip() or None,
  889. "thisPlanQty": evaluated.get("thisPlanQty", round(new_qty, 3)),
  890. "arrangedTotalQty": evaluated["arrangedTotalQty"],
  891. }
  892. kind = evaluated["kind"]
  893. if kind == "violation":
  894. violations.append(
  895. {
  896. **item,
  897. "contractQty": evaluated["contractQty"],
  898. "oldPlanQty": evaluated["oldPlanQty"],
  899. "newPlanQty": evaluated["newPlanQty"],
  900. "effectiveTotal": evaluated["effectiveTotal"],
  901. "overflowQty": evaluated["overflowQty"],
  902. # 与提醒表同列:剩余可为负,表示超量
  903. "remainingQty": round(
  904. evaluated["contractQty"] - evaluated["effectiveTotal"], 3
  905. ),
  906. }
  907. )
  908. elif kind == "remaining":
  909. reminders.append(
  910. {
  911. **item,
  912. "mode": "remaining",
  913. "contractQty": evaluated["contractQty"],
  914. "remainingQty": evaluated["remainingQty"],
  915. }
  916. )
  917. elif kind == "framework_cumulative":
  918. reminders.append(
  919. {
  920. **item,
  921. "mode": "framework_cumulative",
  922. }
  923. )
  924. return violations, reminders
  925. def assert_contract_qty_for_arrange(
  926. db: Session,
  927. *,
  928. tenant_no: str,
  929. plan_id: str,
  930. ) -> list[Dict[str, Any]]:
  931. """超限则 422;通过则返回 reminders(供成功响应复用)。"""
  932. from fastapi import HTTPException
  933. violations, reminders = collect_plan_contract_qty_checks(
  934. db, tenant_no=tenant_no, plan_id=plan_id
  935. )
  936. if violations:
  937. raise HTTPException(
  938. status_code=422,
  939. detail={
  940. "code": CONTRACT_QTY_VIOLATION_CODE,
  941. "message": "合同产品安排数量超过合同数量",
  942. "items": violations,
  943. },
  944. )
  945. return reminders
  946. def build_contract_qty_reminder_payload(
  947. reminders: list[Dict[str, Any]],
  948. ) -> Optional[Dict[str, Any]]:
  949. if not reminders:
  950. return None
  951. return {"items": reminders}
  952. def validate_stop_products_package_against_contract(
  953. *,
  954. contract_products: Iterable[Any],
  955. stop_products: Iterable[Any],
  956. ) -> tuple[bool, Optional[str]]:
  957. """
  958. 绑合同时:计划段产品包装必填,且必须属于本合同该产品的包装集合。
  959. 返回 (ok, error_message)。
  960. """
  961. allowed_by_pid: DefaultDict[str, Set[str]] = defaultdict(set)
  962. contract_pids: Set[str] = set()
  963. for row in contract_products or []:
  964. pid = ""
  965. if isinstance(row, dict):
  966. pid = str(row.get("productId") or row.get("product_id") or "").strip()
  967. else:
  968. pid = str(getattr(row, "product_id", None) or "").strip()
  969. if not pid:
  970. continue
  971. contract_pids.add(pid)
  972. for pkg in contract_line_package_specs(row):
  973. if pkg:
  974. allowed_by_pid[pid].add(pkg)
  975. for row in stop_products or []:
  976. pid = str(getattr(row, "product_id", None) or "").strip()
  977. if not pid or pid not in contract_pids:
  978. continue
  979. allowed = allowed_by_pid.get(pid) or set()
  980. if not allowed:
  981. continue
  982. pkg = normalize_package_spec(getattr(row, "package_spec", None))
  983. name = (
  984. str(getattr(row, "product_name", None) or "").strip()
  985. or pid
  986. )
  987. if not pkg:
  988. return False, f"请选择包装规格:{name}"
  989. if pkg not in allowed:
  990. return False, f"包装规格不在合同明细中:{name}({pkg})"
  991. return True, None
  992. def get_contract_product_arrange_qty_detail(
  993. db: Session,
  994. *,
  995. tenant_no: str,
  996. contract_id: str,
  997. product_id: str,
  998. package_spec: Optional[str] = None,
  999. exclude_plan_id: Optional[str] = None,
  1000. ) -> Dict[str, Any]:
  1001. """
  1002. 表单「可安排数量」与问号弹窗数据。
  1003. arrangedQty / availableQty 均不含 exclude_plan_id(若该计划已在已安排桶中)。
  1004. """
  1005. from app.models.crm_models import CrmContract, CrmContractProduct
  1006. cid = (contract_id or "").strip()
  1007. pid = (product_id or "").strip()
  1008. pkg = normalize_package_spec(package_spec)
  1009. key = product_package_key(pid, pkg)
  1010. exclude_id = (exclude_plan_id or "").strip() or None
  1011. empty = {
  1012. "contractId": cid,
  1013. "contractNo": None,
  1014. "customerName": None,
  1015. "productId": pid,
  1016. "packageSpec": pkg or None,
  1017. "packageSpecs": [],
  1018. "packageBreakdown": [],
  1019. "productName": None,
  1020. "hasFixedQty": False,
  1021. "contractQty": None,
  1022. "arrangedQty": 0.0,
  1023. "availableQty": None,
  1024. "plans": [],
  1025. }
  1026. if not cid or not pid or not key:
  1027. return empty
  1028. contract = (
  1029. db.query(CrmContract)
  1030. .filter(
  1031. CrmContract.id == cid,
  1032. CrmContract.tenant_no == tenant_no,
  1033. CrmContract.del_flag == 0,
  1034. )
  1035. .first()
  1036. )
  1037. if not contract:
  1038. return empty
  1039. candidates = (
  1040. db.query(CrmContractProduct)
  1041. .filter(
  1042. CrmContractProduct.contract_id == cid,
  1043. CrmContractProduct.product_id == pid,
  1044. CrmContractProduct.tenant_no == tenant_no,
  1045. CrmContractProduct.del_flag == 0,
  1046. )
  1047. .all()
  1048. )
  1049. cp = match_contract_product_for_key(candidates, key)
  1050. contract_type = (contract.contract_type or "").strip()
  1051. has_fixed = False
  1052. contract_qty: Optional[float] = None
  1053. package_specs: list[str] = []
  1054. pool_keys: list[ProductPackageKey] = [key] if key else []
  1055. if cp:
  1056. package_specs = contract_line_package_specs(cp)
  1057. pool_keys = pool_keys_for_contract_line(cp) or pool_keys
  1058. has_fixed = contract_product_has_fixed_qty(
  1059. contract_type=contract_type,
  1060. quantity=cp.quantity,
  1061. quantity_mode=cp.quantity_mode,
  1062. )
  1063. if has_fixed:
  1064. contract_qty = round(_to_float(cp.quantity), 3)
  1065. qty_map = aggregate_contract_product_plan_qtys(
  1066. db, tenant_no=tenant_no, contract_id=cid
  1067. )
  1068. arranged_including = sum_qty_stats_for_keys(qty_map, pool_keys)["arrangedTotalQty"]
  1069. package_breakdown = []
  1070. for pool_key in pool_keys:
  1071. stats = qty_map.get(pool_key) or {
  1072. "archivedQty": 0.0,
  1073. "arrangedUnarchivedQty": 0.0,
  1074. "arrangedTotalQty": 0.0,
  1075. }
  1076. package_breakdown.append(
  1077. {
  1078. "packageSpec": pool_key[1],
  1079. "archivedQty": stats["archivedQty"],
  1080. "arrangedUnarchivedQty": stats["arrangedUnarchivedQty"],
  1081. "arrangedTotalQty": stats["arrangedTotalQty"],
  1082. }
  1083. )
  1084. link_rows = (
  1085. db.query(PlPlanContract)
  1086. .filter(
  1087. PlPlanContract.tenant_no == tenant_no,
  1088. PlPlanContract.del_flag == 0,
  1089. PlPlanContract.contract_id == cid,
  1090. )
  1091. .all()
  1092. )
  1093. link_plan_ids = {str(r.plan_id) for r in link_rows if r.plan_id}
  1094. legacy_plans = (
  1095. db.query(PlPlan)
  1096. .filter(
  1097. PlPlan.tenant_no == tenant_no,
  1098. PlPlan.del_flag == 0,
  1099. PlPlan.contract_id == cid,
  1100. )
  1101. .all()
  1102. )
  1103. legacy_only_ids = {p.id for p in legacy_plans if p.id not in link_plan_ids}
  1104. scopes = resolve_linked_plan_scopes(link_rows, legacy_only_ids)
  1105. plan_ids = list(scopes.keys())
  1106. exclude_qty = 0.0
  1107. plans_out: list[Dict[str, Any]] = []
  1108. if plan_ids:
  1109. plans = (
  1110. db.query(PlPlan)
  1111. .filter(
  1112. PlPlan.tenant_no == tenant_no,
  1113. PlPlan.del_flag == 0,
  1114. PlPlan.id.in_(plan_ids),
  1115. PlPlan.follow_status != "cancelled",
  1116. )
  1117. .all()
  1118. )
  1119. plan_by_id = {p.id: p for p in plans}
  1120. loads = (
  1121. db.query(PlPlanLoad)
  1122. .filter(
  1123. PlPlanLoad.tenant_no == tenant_no,
  1124. PlPlanLoad.del_flag == 0,
  1125. PlPlanLoad.plan_id.in_(plan_ids),
  1126. )
  1127. .all()
  1128. )
  1129. deliveries = (
  1130. db.query(PlPlanDelivery)
  1131. .filter(
  1132. PlPlanDelivery.tenant_no == tenant_no,
  1133. PlPlanDelivery.del_flag == 0,
  1134. PlPlanDelivery.plan_id.in_(plan_ids),
  1135. )
  1136. .all()
  1137. )
  1138. load_ids_by_plan: DefaultDict[str, Set[str]] = defaultdict(set)
  1139. delivery_ids_by_plan: DefaultDict[str, Set[str]] = defaultdict(set)
  1140. for row in loads:
  1141. load_ids_by_plan[row.plan_id].add(row.id)
  1142. for row in deliveries:
  1143. delivery_ids_by_plan[row.plan_id].add(row.id)
  1144. all_load_ids = {row.id for row in loads}
  1145. all_delivery_ids = {row.id for row in deliveries}
  1146. load_products_by_load: DefaultDict[str, list] = defaultdict(list)
  1147. if all_load_ids:
  1148. for prod in (
  1149. db.query(PlPlanLoadProduct)
  1150. .filter(
  1151. PlPlanLoadProduct.tenant_no == tenant_no,
  1152. PlPlanLoadProduct.del_flag == 0,
  1153. PlPlanLoadProduct.load_id.in_(all_load_ids),
  1154. )
  1155. .all()
  1156. ):
  1157. load_products_by_load[prod.load_id].append(prod)
  1158. delivery_products_by_delivery: DefaultDict[str, list] = defaultdict(list)
  1159. if all_delivery_ids:
  1160. for prod in (
  1161. db.query(PlPlanDeliveryProduct)
  1162. .filter(
  1163. PlPlanDeliveryProduct.tenant_no == tenant_no,
  1164. PlPlanDeliveryProduct.del_flag == 0,
  1165. PlPlanDeliveryProduct.delivery_id.in_(all_delivery_ids),
  1166. )
  1167. .all()
  1168. ):
  1169. delivery_products_by_delivery[prod.delivery_id].append(prod)
  1170. for plan_id, scope in scopes.items():
  1171. plan = plan_by_id.get(plan_id)
  1172. if not plan:
  1173. continue
  1174. bucket = _bucket_for_plan(
  1175. archive_status=plan.archive_status,
  1176. follow_status=plan.follow_status,
  1177. transport_status=plan.transport_status,
  1178. )
  1179. if not bucket:
  1180. continue
  1181. qty_mode = (
  1182. QTY_MODE_ARCHIVE_ACTUAL if bucket == "archived" else QTY_MODE_PLAN
  1183. )
  1184. qtys = sum_qtys_for_scope(
  1185. load_products_by_load=load_products_by_load,
  1186. delivery_products_by_delivery=delivery_products_by_delivery,
  1187. all_load_ids=load_ids_by_plan.get(plan_id, set()),
  1188. all_delivery_ids=delivery_ids_by_plan.get(plan_id, set()),
  1189. scope=scope,
  1190. qty_mode=qty_mode,
  1191. )
  1192. pq = 0.0
  1193. for pool_key in pool_keys:
  1194. pq += _to_float(qtys.get(pool_key))
  1195. if pq <= 0:
  1196. continue
  1197. if exclude_id and str(plan_id) == exclude_id:
  1198. exclude_qty = pq
  1199. continue
  1200. plan_date = getattr(plan, "plan_date", None)
  1201. plans_out.append(
  1202. {
  1203. "planId": plan_id,
  1204. "planNo": plan.plan_no or "",
  1205. "planDate": plan_date.isoformat() if plan_date else None,
  1206. "planQty": round(pq, 3),
  1207. "planType": getattr(plan, "plan_type", None),
  1208. "belongsCompany": getattr(plan, "belongs_company", None),
  1209. "supplierName": getattr(plan, "supplier_name", None),
  1210. "customerName": getattr(plan, "customer_name", None),
  1211. "licensePlate": getattr(plan, "license_plate", None),
  1212. "driverName": getattr(plan, "driver_name", None),
  1213. "followUserName": getattr(plan, "follow_user_name", None),
  1214. "followStatus": plan.follow_status,
  1215. "transportStatus": plan.transport_status,
  1216. "archiveStatus": plan.archive_status,
  1217. "createTime": plan.create_time.isoformat(sep=" ", timespec="seconds")
  1218. if getattr(plan, "create_time", None)
  1219. else None,
  1220. }
  1221. )
  1222. plans_out.sort(key=lambda x: (x.get("planDate") or "", x.get("planNo") or ""), reverse=True)
  1223. arranged_qty = round(max(0.0, arranged_including - exclude_qty), 3)
  1224. available_qty = None
  1225. if has_fixed and contract_qty is not None:
  1226. available_qty = round(contract_qty - arranged_qty, 3)
  1227. return {
  1228. "contractId": cid,
  1229. "contractNo": contract.contract_no or "",
  1230. "customerName": (contract.customer_name or "").strip() or None,
  1231. "productId": pid,
  1232. "packageSpec": pkg or None,
  1233. "packageSpecs": package_specs or ([pkg] if pkg else []),
  1234. "packageBreakdown": package_breakdown,
  1235. "productName": (cp.product_name if cp else None) or None,
  1236. "hasFixedQty": has_fixed,
  1237. "contractQty": contract_qty,
  1238. "arrangedQty": arranged_qty,
  1239. "availableQty": available_qty,
  1240. "plans": plans_out,
  1241. }