reimburse.py 27 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728
  1. from datetime import datetime
  2. from sqlalchemy import and_, exists, func, or_, select
  3. from app.utils.date_range import build_create_time_filters, current_month_bounds, parse_create_time_day_bounds
  4. from sqlalchemy.orm import Session
  5. from app.models.reimburse import ReimburseApply, ReimburseApproval, ReimburseOffset
  6. from app.models.workflow import WorkflowDef, WorkflowNode
  7. from app.schemas.reimburse import ReimburseApplyOut, ReimburseDetailOut
  8. from app.services.attachment import AttachmentService
  9. from app.services.base import CrudService
  10. from app.utils.codegen import generate_apply_code, generate_title
  11. # 我的报销统计「已通过」:含待财务处理的 approved,以及已发放/冲抵后的终态(与列表筛选项「审批通过」语义区分)
  12. _REIMBURSE_MY_STATS_PASSED_STATUSES = ("approved", "processed", "paid", "offset")
  13. class ReimburseService:
  14. @staticmethod
  15. def get_by_id(db: Session, id: int) -> ReimburseApply | None:
  16. return CrudService.get_by_id(ReimburseApply, db, id)
  17. @staticmethod
  18. def get_paged(db: Session, page: int, page_size: int, filters: list | None = None):
  19. return CrudService.get_paged(ReimburseApply, db, page, page_size, filters)
  20. @staticmethod
  21. def build_expense_name_filter(expense_name: str | None):
  22. """按费用项目名称模糊匹配:主表首条明细名 + 任意明细行均参与检索。"""
  23. from app.models.reimburse import ReimburseDetail
  24. trimmed = (expense_name or "").strip()
  25. if not trimmed:
  26. return None
  27. detail_match = exists(
  28. select(1).where(
  29. ReimburseDetail.reimburse_id == ReimburseApply.id,
  30. ReimburseDetail.del_flag == 0,
  31. ReimburseDetail.expense_name.contains(trimmed),
  32. )
  33. )
  34. return or_(ReimburseApply.expense_name.contains(trimmed), detail_match)
  35. @staticmethod
  36. def create(db: Session, obj_in: dict) -> ReimburseApply:
  37. return CrudService.create(ReimburseApply, db, obj_in)
  38. @staticmethod
  39. def update(db: Session, id: int, obj_in: dict) -> ReimburseApply | None:
  40. return CrudService.update(ReimburseApply, db, id, obj_in)
  41. @staticmethod
  42. def soft_delete(db: Session, id: int) -> bool:
  43. return CrudService.soft_delete(ReimburseApply, db, id)
  44. @staticmethod
  45. def pending_approval(
  46. db: Session,
  47. user,
  48. page: int,
  49. page_size: int,
  50. keyword: str = "",
  51. expense_name: str | None = None,
  52. applicant_id: int | None = None,
  53. apply_date: str | None = None,
  54. apply_date_start: str | None = None,
  55. apply_date_end: str | None = None,
  56. ):
  57. """查询待当前用户审批的报销申请(current_node 对应的 WorkflowNode.approver_id == user.id)"""
  58. subquery = (
  59. db.query(ReimburseApply.id)
  60. .join(WorkflowNode, and_(
  61. WorkflowNode.wf_id == ReimburseApply.wf_id,
  62. WorkflowNode.node_name == ReimburseApply.current_node,
  63. ))
  64. .filter(
  65. ReimburseApply.del_flag == 0,
  66. ReimburseApply.status == "pending",
  67. WorkflowNode.approver_id == user.id,
  68. )
  69. )
  70. if keyword:
  71. subquery = subquery.filter(
  72. ReimburseApply.apply_code.contains(keyword) | ReimburseApply.applicant_name.contains(keyword)
  73. )
  74. expense_filter = ReimburseService.build_expense_name_filter(expense_name)
  75. if expense_filter is not None:
  76. subquery = subquery.filter(expense_filter)
  77. if applicant_id is not None:
  78. subquery = subquery.filter(ReimburseApply.applicant_id == applicant_id)
  79. for cond in build_create_time_filters(
  80. ReimburseApply.create_time, apply_date, apply_date_start, apply_date_end
  81. ):
  82. subquery = subquery.filter(cond)
  83. subquery = subquery.subquery()
  84. total = db.query(func.count()).select_from(subquery).scalar()
  85. rows = (
  86. db.query(ReimburseApply)
  87. .join(subquery, ReimburseApply.id == subquery.c.id)
  88. .order_by(ReimburseApply.id.desc())
  89. .offset((page - 1) * page_size)
  90. .limit(page_size)
  91. .all()
  92. )
  93. return rows, total or 0
  94. @staticmethod
  95. def approved_by_me(
  96. db: Session,
  97. user,
  98. page: int,
  99. page_size: int,
  100. keyword: str = "",
  101. expense_name: str | None = None,
  102. applicant_id: int | None = None,
  103. apply_date: str | None = None,
  104. apply_date_start: str | None = None,
  105. apply_date_end: str | None = None,
  106. ):
  107. """查询当前用户已审批过的报销申请"""
  108. subquery = (
  109. db.query(ReimburseApproval.apply_id)
  110. .filter(ReimburseApproval.approver_id == user.id)
  111. .distinct()
  112. .subquery()
  113. )
  114. query = (
  115. db.query(ReimburseApply)
  116. .join(subquery, ReimburseApply.id == subquery.c.apply_id)
  117. .filter(ReimburseApply.del_flag == 0)
  118. )
  119. if keyword:
  120. query = query.filter(
  121. ReimburseApply.apply_code.contains(keyword) | ReimburseApply.applicant_name.contains(keyword)
  122. )
  123. expense_filter = ReimburseService.build_expense_name_filter(expense_name)
  124. if expense_filter is not None:
  125. query = query.filter(expense_filter)
  126. if applicant_id is not None:
  127. query = query.filter(ReimburseApply.applicant_id == applicant_id)
  128. for cond in build_create_time_filters(
  129. ReimburseApply.create_time, apply_date, apply_date_start, apply_date_end
  130. ):
  131. query = query.filter(cond)
  132. total = query.count()
  133. rows = (
  134. query.order_by(ReimburseApply.id.desc())
  135. .offset((page - 1) * page_size)
  136. .limit(page_size)
  137. .all()
  138. )
  139. return rows, total
  140. @staticmethod
  141. def submit(db: Session, apply_id: int, user) -> ReimburseApply | None:
  142. apply_obj = (
  143. db.query(ReimburseApply)
  144. .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
  145. .first()
  146. )
  147. if not apply_obj:
  148. return None
  149. apply_obj.status = "pending"
  150. if not apply_obj.apply_code:
  151. apply_obj.apply_code = generate_apply_code(db, "BX")
  152. apply_obj.update_by = str(user.id)
  153. wf = (
  154. db.query(WorkflowDef)
  155. .filter(WorkflowDef.id == apply_obj.wf_id, WorkflowDef.del_flag == 0)
  156. .first()
  157. )
  158. if wf:
  159. nodes = (
  160. db.query(WorkflowNode)
  161. .filter(WorkflowNode.wf_id == wf.id)
  162. .order_by(WorkflowNode.node_order)
  163. .all()
  164. )
  165. if nodes:
  166. apply_obj.current_node = nodes[0].node_name
  167. db.flush()
  168. return apply_obj
  169. @staticmethod
  170. def approve(db: Session, apply_id: int, approver, opinion: str) -> ReimburseApply | None:
  171. apply_obj = (
  172. db.query(ReimburseApply)
  173. .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
  174. .first()
  175. )
  176. if not apply_obj:
  177. return None
  178. wf = (
  179. db.query(WorkflowDef)
  180. .filter(WorkflowDef.id == apply_obj.wf_id, WorkflowDef.del_flag == 0)
  181. .first()
  182. )
  183. if not wf:
  184. return None
  185. nodes = (
  186. db.query(WorkflowNode)
  187. .filter(WorkflowNode.wf_id == wf.id)
  188. .order_by(WorkflowNode.node_order)
  189. .all()
  190. )
  191. current_idx = None
  192. for i, node in enumerate(nodes):
  193. if node.node_name == apply_obj.current_node:
  194. current_idx = i
  195. break
  196. if current_idx is None:
  197. return None
  198. current_node = nodes[current_idx]
  199. if current_node.approver_id != approver.id:
  200. return None
  201. approval = ReimburseApproval(
  202. apply_id=apply_id,
  203. approver_id=approver.id,
  204. approver_name=approver.username,
  205. approval_opinion=opinion,
  206. node_name=current_node.node_name,
  207. node_order=current_node.node_order,
  208. approval_status="approved",
  209. approval_time=datetime.now(),
  210. )
  211. db.add(approval)
  212. if current_idx == len(nodes) - 1:
  213. apply_obj.status = "approved"
  214. apply_obj.current_node = None
  215. else:
  216. apply_obj.current_node = nodes[current_idx + 1].node_name
  217. apply_obj.update_by = str(approver.id)
  218. db.flush()
  219. return apply_obj
  220. @staticmethod
  221. def reject(db: Session, apply_id: int, approver, opinion: str) -> ReimburseApply | None:
  222. apply_obj = (
  223. db.query(ReimburseApply)
  224. .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
  225. .first()
  226. )
  227. if not apply_obj:
  228. return None
  229. wf = (
  230. db.query(WorkflowDef)
  231. .filter(WorkflowDef.id == apply_obj.wf_id, WorkflowDef.del_flag == 0)
  232. .first()
  233. )
  234. if not wf:
  235. return None
  236. nodes = (
  237. db.query(WorkflowNode)
  238. .filter(WorkflowNode.wf_id == wf.id)
  239. .order_by(WorkflowNode.node_order)
  240. .all()
  241. )
  242. current_node = None
  243. for node in nodes:
  244. if node.node_name == apply_obj.current_node:
  245. current_node = node
  246. break
  247. if current_node is None:
  248. return None
  249. if current_node.approver_id != approver.id:
  250. return None
  251. approval = ReimburseApproval(
  252. apply_id=apply_id,
  253. approver_id=approver.id,
  254. approver_name=approver.username,
  255. approval_opinion=opinion,
  256. node_name=current_node.node_name,
  257. node_order=current_node.node_order,
  258. approval_status="rejected",
  259. approval_time=datetime.now(),
  260. )
  261. db.add(approval)
  262. apply_obj.status = "rejected"
  263. apply_obj.current_node = None
  264. apply_obj.update_by = str(approver.id)
  265. db.flush()
  266. return apply_obj
  267. @staticmethod
  268. def pay(
  269. db: Session,
  270. apply_id: int,
  271. user,
  272. pay_remark: str = "",
  273. actual_amount: float | None = None,
  274. ) -> ReimburseApply | None:
  275. apply_obj = (
  276. db.query(ReimburseApply)
  277. .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
  278. .first()
  279. )
  280. if not apply_obj:
  281. return None
  282. apply_obj.status = "processed"
  283. apply_obj.update_by = str(user.id)
  284. apply_obj.pay_status = "paid"
  285. apply_obj.pay_time = datetime.now()
  286. apply_obj.pay_user_id = user.id
  287. apply_obj.pay_user_name = user.real_name or user.username
  288. if actual_amount is not None:
  289. apply_obj.actual_amount = actual_amount
  290. db.flush()
  291. return apply_obj
  292. @staticmethod
  293. def offset(
  294. db: Session, apply_id: int, advance_id: int, amount: float, user, remark: str = ""
  295. ) -> ReimburseApply | None:
  296. apply_obj = (
  297. db.query(ReimburseApply)
  298. .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
  299. .first()
  300. )
  301. if not apply_obj:
  302. return None
  303. apply_obj.status = "offset"
  304. apply_obj.offset_advance_id = advance_id
  305. apply_obj.offset_amount = amount
  306. apply_obj.update_by = str(user.id)
  307. offset_record = ReimburseOffset(
  308. reimburse_id=apply_id,
  309. advance_id=advance_id,
  310. offset_amount=amount,
  311. remark=remark or None,
  312. create_by=(user.real_name or user.username),
  313. )
  314. db.add(offset_record)
  315. db.flush()
  316. return apply_obj
  317. @staticmethod
  318. def batch_offset(
  319. db: Session, apply_id: int, items: list[dict], pay_amount: float, user, remark: str = ""
  320. ) -> ReimburseApply | None:
  321. apply_obj = (
  322. db.query(ReimburseApply)
  323. .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
  324. .first()
  325. )
  326. if not apply_obj:
  327. return None
  328. has_offsets = bool(items)
  329. total_offset = 0.0
  330. first_advance_id = None
  331. for item in items:
  332. advance_id = item["advance_id"]
  333. offset_amount = float(item["offset_amount"])
  334. if first_advance_id is None:
  335. first_advance_id = advance_id
  336. total_offset += offset_amount
  337. offset_record = ReimburseOffset(
  338. reimburse_id=apply_id,
  339. advance_id=advance_id,
  340. offset_amount=offset_amount,
  341. remark=remark or None,
  342. create_by=(user.real_name or user.username),
  343. )
  344. db.add(offset_record)
  345. if first_advance_id is not None:
  346. apply_obj.offset_advance_id = first_advance_id
  347. apply_obj.offset_amount = total_offset
  348. if pay_amount > 0:
  349. apply_obj.pay_status = "paid"
  350. apply_obj.pay_time = datetime.now()
  351. apply_obj.pay_user_id = user.id
  352. apply_obj.pay_user_name = user.real_name or user.username
  353. apply_obj.actual_amount = pay_amount
  354. apply_obj.status = "processed"
  355. elif has_offsets:
  356. apply_obj.actual_amount = 0
  357. apply_obj.status = "processed"
  358. apply_obj.update_by = str(user.id)
  359. db.flush()
  360. return apply_obj
  361. @staticmethod
  362. def get_approvals(db: Session, apply_id: int) -> list[dict]:
  363. apply_obj = (
  364. db.query(ReimburseApply)
  365. .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
  366. .first()
  367. )
  368. if not apply_obj or not apply_obj.wf_id:
  369. return []
  370. wf = (
  371. db.query(WorkflowDef)
  372. .filter(WorkflowDef.id == apply_obj.wf_id, WorkflowDef.del_flag == 0)
  373. .first()
  374. )
  375. if not wf:
  376. return []
  377. nodes = (
  378. db.query(WorkflowNode)
  379. .filter(WorkflowNode.wf_id == wf.id)
  380. .order_by(WorkflowNode.node_order)
  381. .all()
  382. )
  383. if not nodes:
  384. return []
  385. approvals = (
  386. db.query(ReimburseApproval)
  387. .filter(ReimburseApproval.apply_id == apply_id)
  388. .all()
  389. )
  390. approval_map = {a.node_order: a for a in approvals}
  391. result = []
  392. for node in nodes:
  393. approval = approval_map.get(node.node_order)
  394. if approval:
  395. result.append({
  396. "id": approval.id,
  397. "reimburse_id": apply_id,
  398. "node_name": approval.node_name,
  399. "node_order": approval.node_order,
  400. "approval_status": approval.approval_status,
  401. "approval_opinion": approval.approval_opinion,
  402. "approver_name": approval.approver_name,
  403. "approval_time": approval.approval_time,
  404. })
  405. else:
  406. result.append({
  407. "id": 0,
  408. "reimburse_id": apply_id,
  409. "node_name": node.node_name,
  410. "node_order": node.node_order,
  411. "approval_status": "pending",
  412. "approval_opinion": None,
  413. "approver_name": node.approver_name or "",
  414. "approval_time": None,
  415. })
  416. return result
  417. @staticmethod
  418. def create_with_details(db: Session, obj_in: dict, details: list[dict], user) -> ReimburseApply:
  419. from app.models.reimburse import ReimburseDetail
  420. total_amount = sum(d.get("amount", 0) for d in details)
  421. apply_date = obj_in.pop("apply_date", None)
  422. title = obj_in.pop("title", None) or generate_title(
  423. db, user.real_name or user.username, apply_date
  424. )
  425. first_detail = details[0] if details else {}
  426. apply_data = {
  427. **obj_in,
  428. "title": title,
  429. "amount": total_amount,
  430. "expense_id": first_detail.get("expense_id", 0),
  431. "expense_name": first_detail.get("expense_name", ""),
  432. "applicant_id": user.id,
  433. "applicant_name": user.real_name or user.username,
  434. }
  435. apply_obj = ReimburseApply(**apply_data)
  436. db.add(apply_obj)
  437. db.flush()
  438. for d in details:
  439. detail = ReimburseDetail(
  440. reimburse_id=apply_obj.id,
  441. expense_id=d["expense_id"],
  442. expense_name=d["expense_name"],
  443. amount=d["amount"],
  444. remark=d.get("remark"),
  445. create_by=str(user.id),
  446. )
  447. db.add(detail)
  448. db.flush()
  449. return apply_obj
  450. @staticmethod
  451. def update_with_details(db: Session, apply_id: int, obj_in: dict, details: list[dict] | None, user) -> ReimburseApply | None:
  452. from app.models.reimburse import ReimburseDetail
  453. apply_obj = (
  454. db.query(ReimburseApply)
  455. .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
  456. .first()
  457. )
  458. if not apply_obj:
  459. return None
  460. for k, v in obj_in.items():
  461. setattr(apply_obj, k, v)
  462. if details is not None:
  463. db.query(ReimburseDetail).filter(
  464. ReimburseDetail.reimburse_id == apply_id
  465. ).update({"del_flag": 1})
  466. total_amount = 0
  467. first_expense_name = ""
  468. first_expense_id = 0
  469. for i, d in enumerate(details):
  470. detail = ReimburseDetail(
  471. reimburse_id=apply_id,
  472. expense_id=d["expense_id"],
  473. expense_name=d["expense_name"],
  474. amount=d["amount"],
  475. remark=d.get("remark"),
  476. create_by=str(user.id),
  477. )
  478. db.add(detail)
  479. total_amount += d["amount"]
  480. if i == 0:
  481. first_expense_name = d["expense_name"]
  482. first_expense_id = d["expense_id"]
  483. apply_obj.amount = total_amount
  484. apply_obj.expense_name = first_expense_name
  485. apply_obj.expense_id = first_expense_id
  486. apply_obj.update_by = str(user.id)
  487. db.flush()
  488. return apply_obj
  489. @staticmethod
  490. def get_details(db: Session, reimburse_id: int) -> list:
  491. from app.models.reimburse import ReimburseDetail
  492. return db.query(ReimburseDetail).filter(
  493. ReimburseDetail.reimburse_id == reimburse_id,
  494. ReimburseDetail.del_flag == 0
  495. ).order_by(ReimburseDetail.id).all()
  496. @staticmethod
  497. def get_details_by_reimburse_ids(db: Session, reimburse_ids: list[int]) -> dict[int, list]:
  498. from app.models.reimburse import ReimburseDetail
  499. if not reimburse_ids:
  500. return {}
  501. rows = (
  502. db.query(ReimburseDetail)
  503. .filter(
  504. ReimburseDetail.reimburse_id.in_(reimburse_ids),
  505. ReimburseDetail.del_flag == 0,
  506. )
  507. .order_by(ReimburseDetail.reimburse_id, ReimburseDetail.id)
  508. .all()
  509. )
  510. grouped: dict[int, list] = {}
  511. for row in rows:
  512. grouped.setdefault(row.reimburse_id, []).append(row)
  513. return grouped
  514. @staticmethod
  515. def out_rows_with_attachment_counts(db: Session, rows: list[ReimburseApply]) -> list[ReimburseApplyOut]:
  516. """列表/详情输出:从 bx_attachment 聚合 invoice_count / attach_count(库内冗余字段可能未维护)。"""
  517. if not rows:
  518. return []
  519. ids = [r.id for r in rows]
  520. counts = AttachmentService.count_invoice_attach_by_biz_ids(db, "reimburse_apply", ids)
  521. details_map = ReimburseService.get_details_by_reimburse_ids(db, ids)
  522. out_list: list[ReimburseApplyOut] = []
  523. for r in rows:
  524. o = ReimburseApplyOut.model_validate(r)
  525. inv, att = counts.get(r.id, (0, 0))
  526. detail_rows = details_map.get(r.id, [])
  527. details_out = [ReimburseDetailOut.model_validate(d) for d in detail_rows] or None
  528. out_list.append(
  529. o.model_copy(update={"invoice_count": inv, "attach_count": att, "details": details_out})
  530. )
  531. return out_list
  532. @staticmethod
  533. def out_one_with_attachment_counts(db: Session, row: ReimburseApply) -> ReimburseApplyOut:
  534. return ReimburseService.out_rows_with_attachment_counts(db, [row])[0]
  535. @staticmethod
  536. def build_apply_out_with_details(db: Session, reimburse: ReimburseApply) -> ReimburseApplyOut:
  537. out = ReimburseService.out_one_with_attachment_counts(db, reimburse)
  538. detail_rows = ReimburseService.get_details(db, reimburse.id)
  539. out.details = [ReimburseDetailOut.model_validate(d) for d in detail_rows]
  540. return out
  541. @staticmethod
  542. def global_status_counts(db: Session) -> dict[str, int]:
  543. """全库报销单按 status 计数(del_flag=0),用于管理端汇总卡片。"""
  544. rows = (
  545. db.query(ReimburseApply.status, func.count())
  546. .filter(ReimburseApply.del_flag == 0)
  547. .group_by(ReimburseApply.status)
  548. .all()
  549. )
  550. return {str(s or ""): int(c) for s, c in rows}
  551. @staticmethod
  552. def my_apply_stats(db: Session, user_id: int) -> dict:
  553. """我的报销:全量聚合(与列表分页无关);本月按 create_time 自然月。
  554. approved_count / approved_amount:审批流程已通过的单据(含待支付 approved 与已处理 processed/paid/offset),
  555. 与前端「已通过申请」「已审批金额」一致;不等同于 status 仅为 approved 的待支付队列。
  556. """
  557. base = and_(ReimburseApply.del_flag == 0, ReimburseApply.applicant_id == user_id)
  558. total_count = db.query(func.count()).select_from(ReimburseApply).filter(base).scalar() or 0
  559. pending = db.query(func.count()).select_from(ReimburseApply).filter(base, ReimburseApply.status == "pending").scalar() or 0
  560. approved_count = (
  561. db.query(func.count())
  562. .select_from(ReimburseApply)
  563. .filter(base, ReimburseApply.status.in_(_REIMBURSE_MY_STATS_PASSED_STATUSES))
  564. .scalar()
  565. or 0
  566. )
  567. rejected_count = (
  568. db.query(func.count()).select_from(ReimburseApply).filter(base, ReimburseApply.status == "rejected").scalar() or 0
  569. )
  570. approved_amount = (
  571. db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
  572. .filter(base, ReimburseApply.status.in_(_REIMBURSE_MY_STATS_PASSED_STATUSES))
  573. .scalar()
  574. or 0
  575. )
  576. m0, m1 = current_month_bounds()
  577. month_apply_amount = (
  578. db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
  579. .filter(base, ReimburseApply.create_time >= m0, ReimburseApply.create_time < m1)
  580. .scalar()
  581. or 0
  582. )
  583. month_apply_count = (
  584. db.query(func.count())
  585. .select_from(ReimburseApply)
  586. .filter(base, ReimburseApply.create_time >= m0, ReimburseApply.create_time < m1)
  587. .scalar()
  588. or 0
  589. )
  590. return {
  591. "total_count": int(total_count),
  592. "pending_count": int(pending),
  593. "approved_count": int(approved_count),
  594. "rejected_count": int(rejected_count),
  595. "approved_amount": float(approved_amount),
  596. "month_apply_amount": float(month_apply_amount),
  597. "month_apply_count": int(month_apply_count),
  598. }
  599. @staticmethod
  600. def pending_approval_stats(db: Session, user) -> dict:
  601. """待我审批的报销:总条数 + 其中申请提交于本月的金额合计(create_time)。"""
  602. subquery = (
  603. db.query(ReimburseApply.id)
  604. .join(
  605. WorkflowNode,
  606. and_(
  607. WorkflowNode.wf_id == ReimburseApply.wf_id,
  608. WorkflowNode.node_name == ReimburseApply.current_node,
  609. ),
  610. )
  611. .filter(
  612. ReimburseApply.del_flag == 0,
  613. ReimburseApply.status == "pending",
  614. WorkflowNode.approver_id == user.id,
  615. )
  616. ).subquery()
  617. total = db.query(func.count()).select_from(subquery).scalar() or 0
  618. m0, m1 = current_month_bounds()
  619. month_sum = (
  620. db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
  621. .join(subquery, ReimburseApply.id == subquery.c.id)
  622. .filter(ReimburseApply.create_time >= m0, ReimburseApply.create_time < m1)
  623. .scalar()
  624. or 0
  625. )
  626. return {"pending_total": int(total), "month_pending_amount": float(month_sum)}
  627. @staticmethod
  628. def finance_payment_stats(db: Session) -> dict:
  629. """财务支付看板:待处理=approved;已打款=有 pay_time;冲抵=offset 或 无 pay_time 的 processed 且有冲抵额;本月金额=打款按 pay_time、冲抵按 update_time。"""
  630. base = ReimburseApply.del_flag == 0
  631. pending = (
  632. db.query(func.count()).select_from(ReimburseApply).filter(base, ReimburseApply.status == "approved").scalar() or 0
  633. )
  634. bank_paid = and_(ReimburseApply.pay_time.isnot(None))
  635. paid = (
  636. db.query(func.count()).select_from(ReimburseApply).filter(base, bank_paid).scalar() or 0
  637. )
  638. offset_settled = or_(
  639. ReimburseApply.status == "offset",
  640. and_(
  641. ReimburseApply.status == "processed",
  642. ReimburseApply.pay_time.is_(None),
  643. func.coalesce(ReimburseApply.offset_amount, 0) > 0,
  644. ),
  645. )
  646. offset = db.query(func.count()).select_from(ReimburseApply).filter(base, offset_settled).scalar() or 0
  647. m0, m1 = current_month_bounds()
  648. month_paid = (
  649. db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
  650. .filter(
  651. base,
  652. bank_paid,
  653. ReimburseApply.pay_time >= m0,
  654. ReimburseApply.pay_time < m1,
  655. )
  656. .scalar()
  657. or 0
  658. )
  659. month_offset = (
  660. db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
  661. .filter(base, offset_settled, ReimburseApply.update_time >= m0, ReimburseApply.update_time < m1)
  662. .scalar()
  663. or 0
  664. )
  665. month_amount = float(month_paid) + float(month_offset)
  666. return {
  667. "pending_count": int(pending),
  668. "paid_count": int(paid),
  669. "offset_count": int(offset),
  670. "month_processed_amount": month_amount,
  671. }