reimburse.py 51 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370
  1. from __future__ import annotations
  2. from datetime import datetime
  3. from decimal import Decimal
  4. from typing import Any, Dict, List, Optional, Sequence, Tuple
  5. from fastapi import HTTPException, status
  6. from sqlalchemy import and_, func, or_
  7. from sqlalchemy.exc import IntegrityError
  8. from sqlalchemy.orm import Session
  9. from app.core.bx_auth import BxUserContext
  10. from app.core.menu_guard import SUPER_ADMIN_ROLE
  11. from app.core.tenant_context import get_tenant_no
  12. from app.core.utils import generate_uuid
  13. from app.models.bx_models import (
  14. BxAttachment,
  15. BxInvoice,
  16. BxInvoiceLedger,
  17. BxReimburseApply,
  18. BxReimburseApproval,
  19. BxReimburseDetail,
  20. BxReimburseInvoice,
  21. BxReimburseOffset,
  22. BxWorkflowDef,
  23. BxWorkflowNode,
  24. )
  25. from app.models.models import SysUser
  26. from app.schemas.bx.reimburse import (
  27. ReimburseApplyOut,
  28. ReimburseDetailOut,
  29. ReimburseInvoiceBriefOut,
  30. ReimburseInvoiceLinkIn,
  31. )
  32. from app.services.bx.approval_cycle import filter_current_cycle_approvals
  33. from app.services.bx.attachment import AttachmentService
  34. from app.services.bx.base import BxCrudService
  35. from app.services.bx.invoice_ledger import ENTRY_DEBIT_REIMBURSE, InvoiceLedgerService
  36. from app.services.bx_data_scope import apply_bx_scope
  37. from app.services.web_auth_roles import iter_user_menu_perm_codes
  38. from app.utils.bx_codegen import (
  39. allocate_reimburse_title_seq,
  40. generate_apply_code,
  41. format_reimburse_title,
  42. is_auto_reimburse_title,
  43. parse_reimburse_date,
  44. resolve_reimburse_title,
  45. )
  46. from app.utils.bx_date_range import current_month_bounds, resolve_create_time_bounds
  47. REIMBURSE_CHANGE_APPLICANT_PERM = "bx:reimburseApply:changeApplicant"
  48. _REIMBURSE_MY_STATS_PASSED_STATUSES = ("approved", "processed", "paid", "offset")
  49. class ReimburseService:
  50. @staticmethod
  51. def _tenant_no(db: Session, user: BxUserContext) -> str:
  52. return get_tenant_no(db, user.to_tenant_user_dict())
  53. @staticmethod
  54. def can_change_applicant(db: Session, user: BxUserContext) -> bool:
  55. """是否可选择他人作为报销申请人(超管或具备按钮权限)。"""
  56. if user.role == SUPER_ADMIN_ROLE:
  57. return True
  58. return REIMBURSE_CHANGE_APPLICANT_PERM in set(
  59. iter_user_menu_perm_codes(db, user.id, user.tenant_no)
  60. )
  61. @staticmethod
  62. def resolve_applicant(
  63. db: Session,
  64. user: BxUserContext,
  65. applicant_id: Optional[str],
  66. *,
  67. tenant_no: str,
  68. ) -> tuple[str, str]:
  69. """
  70. 解析报销申请人。
  71. 无 changeApplicant 权限时忽略客户端指定,强制本人;
  72. 有权限时可指定同租户有效用户。
  73. 返回 (applicant_id, applicant_name)。
  74. """
  75. requested = (applicant_id or "").strip() or None
  76. if not requested or requested == user.id:
  77. return user.id, (user.name or user.id)
  78. if not ReimburseService.can_change_applicant(db, user):
  79. return user.id, (user.name or user.id)
  80. target = (
  81. db.query(SysUser)
  82. .filter(
  83. SysUser.id == requested,
  84. SysUser.tenant_no == tenant_no,
  85. SysUser.del_flag == 0,
  86. SysUser.status == "1",
  87. )
  88. .first()
  89. )
  90. if not target:
  91. raise HTTPException(
  92. status_code=status.HTTP_400_BAD_REQUEST,
  93. detail="申请人不存在或已停用",
  94. )
  95. return target.id, (target.nick_name or target.user_no or target.id)
  96. @staticmethod
  97. def get_by_id(
  98. db: Session,
  99. reimburse_id: str,
  100. tenant_no: str,
  101. *,
  102. user: Optional[BxUserContext] = None,
  103. skip_scope: bool = False,
  104. ) -> Optional[BxReimburseApply]:
  105. return BxCrudService.get_by_id(
  106. BxReimburseApply, db, reimburse_id, tenant_no, user=user, skip_scope=skip_scope
  107. )
  108. @staticmethod
  109. def get_paged(
  110. db: Session,
  111. tenant_no: str,
  112. page: int,
  113. page_size: int,
  114. filters: Optional[List[Any]] = None,
  115. *,
  116. user: Optional[BxUserContext] = None,
  117. order_by: Optional[Any] = None,
  118. ) -> Tuple[Sequence[BxReimburseApply], int]:
  119. return BxCrudService.get_paged(
  120. BxReimburseApply,
  121. db,
  122. tenant_no,
  123. page,
  124. page_size,
  125. filters,
  126. user=user,
  127. order_by=order_by,
  128. )
  129. @staticmethod
  130. def soft_delete(db: Session, reimburse_id: str, *, user: BxUserContext) -> bool:
  131. tenant_no = ReimburseService._tenant_no(db, user)
  132. return BxCrudService.soft_delete(
  133. BxReimburseApply, db, reimburse_id, tenant_no, user_id=user.id, user=user
  134. )
  135. @staticmethod
  136. def _workflow_nodes(db: Session, wf_id: str, tenant_no: str) -> List[BxWorkflowNode]:
  137. return (
  138. db.query(BxWorkflowNode)
  139. .filter(
  140. BxWorkflowNode.wf_id == wf_id,
  141. BxWorkflowNode.tenant_no == tenant_no,
  142. BxWorkflowNode.del_flag == 0,
  143. )
  144. .order_by(BxWorkflowNode.node_order)
  145. .all()
  146. )
  147. @staticmethod
  148. def pending_approval(
  149. db: Session,
  150. user: BxUserContext,
  151. tenant_no: str,
  152. page: int,
  153. page_size: int,
  154. keyword: str = "",
  155. applicant_id: Optional[str] = None,
  156. expense_name: Optional[str] = None,
  157. apply_date: Optional[str] = None,
  158. apply_date_from: Optional[str] = None,
  159. apply_date_to: Optional[str] = None,
  160. extra_filters: Optional[list] = None,
  161. order_by: Optional[Any] = None,
  162. ):
  163. subquery = (
  164. db.query(BxReimburseApply.id)
  165. .join(
  166. BxWorkflowNode,
  167. and_(
  168. BxWorkflowNode.wf_id == BxReimburseApply.wf_id,
  169. BxWorkflowNode.node_name == BxReimburseApply.current_node,
  170. BxWorkflowNode.tenant_no == tenant_no,
  171. BxWorkflowNode.del_flag == 0,
  172. ),
  173. )
  174. .filter(
  175. BxReimburseApply.del_flag == 0,
  176. BxReimburseApply.tenant_no == tenant_no,
  177. BxReimburseApply.status == "pending",
  178. BxWorkflowNode.approver_id == user.id,
  179. )
  180. )
  181. if extra_filters:
  182. # Column filters from builder already include keyword / dates / etc.
  183. subquery = subquery.filter(*extra_filters)
  184. else:
  185. if keyword:
  186. kw_conds = [
  187. BxReimburseApply.apply_code.contains(keyword),
  188. BxReimburseApply.applicant_name.contains(keyword),
  189. BxReimburseApply.title.contains(keyword),
  190. ]
  191. expense_kw = ReimburseService.build_expense_name_condition(
  192. db, tenant_no, keyword
  193. )
  194. if expense_kw is not None:
  195. kw_conds.append(expense_kw)
  196. subquery = subquery.filter(or_(*kw_conds))
  197. if applicant_id:
  198. subquery = subquery.filter(BxReimburseApply.applicant_id == applicant_id)
  199. expense_filter = ReimburseService.build_expense_name_condition(
  200. db, tenant_no, expense_name
  201. )
  202. if expense_filter is not None:
  203. subquery = subquery.filter(expense_filter)
  204. start, end = resolve_create_time_bounds(
  205. apply_date=apply_date,
  206. apply_date_from=apply_date_from,
  207. apply_date_to=apply_date_to,
  208. )
  209. if start is not None:
  210. subquery = subquery.filter(BxReimburseApply.create_time >= start)
  211. if end is not None:
  212. subquery = subquery.filter(BxReimburseApply.create_time < end)
  213. subquery = subquery.subquery()
  214. total = db.query(func.count()).select_from(subquery).scalar() or 0
  215. order_expr = order_by if order_by is not None else BxReimburseApply.create_time.desc()
  216. rows = (
  217. db.query(BxReimburseApply)
  218. .join(subquery, BxReimburseApply.id == subquery.c.id)
  219. .order_by(order_expr)
  220. .offset((page - 1) * page_size)
  221. .limit(page_size)
  222. .all()
  223. )
  224. return rows, total
  225. @staticmethod
  226. def approved_by_me(
  227. db: Session,
  228. user: BxUserContext,
  229. tenant_no: str,
  230. page: int,
  231. page_size: int,
  232. keyword: str = "",
  233. applicant_id: Optional[str] = None,
  234. expense_name: Optional[str] = None,
  235. apply_date: Optional[str] = None,
  236. apply_date_from: Optional[str] = None,
  237. apply_date_to: Optional[str] = None,
  238. extra_filters: Optional[list] = None,
  239. order_by: Optional[Any] = None,
  240. ):
  241. subquery = (
  242. db.query(BxReimburseApproval.apply_id)
  243. .filter(
  244. BxReimburseApproval.approver_id == user.id,
  245. BxReimburseApproval.tenant_no == tenant_no,
  246. BxReimburseApproval.del_flag == 0,
  247. )
  248. .distinct()
  249. .subquery()
  250. )
  251. query = (
  252. db.query(BxReimburseApply)
  253. .join(subquery, BxReimburseApply.id == subquery.c.apply_id)
  254. .filter(BxReimburseApply.del_flag == 0, BxReimburseApply.tenant_no == tenant_no)
  255. )
  256. if extra_filters:
  257. query = query.filter(*extra_filters)
  258. else:
  259. if keyword:
  260. kw_conds = [
  261. BxReimburseApply.apply_code.contains(keyword),
  262. BxReimburseApply.applicant_name.contains(keyword),
  263. BxReimburseApply.title.contains(keyword),
  264. ]
  265. expense_kw = ReimburseService.build_expense_name_condition(
  266. db, tenant_no, keyword
  267. )
  268. if expense_kw is not None:
  269. kw_conds.append(expense_kw)
  270. query = query.filter(or_(*kw_conds))
  271. if applicant_id:
  272. query = query.filter(BxReimburseApply.applicant_id == applicant_id)
  273. expense_filter = ReimburseService.build_expense_name_condition(
  274. db, tenant_no, expense_name
  275. )
  276. if expense_filter is not None:
  277. query = query.filter(expense_filter)
  278. start, end = resolve_create_time_bounds(
  279. apply_date=apply_date,
  280. apply_date_from=apply_date_from,
  281. apply_date_to=apply_date_to,
  282. )
  283. if start is not None:
  284. query = query.filter(BxReimburseApply.create_time >= start)
  285. if end is not None:
  286. query = query.filter(BxReimburseApply.create_time < end)
  287. query = apply_bx_scope(query, BxReimburseApply, user, db, tenant_no)
  288. total = query.count()
  289. order_expr = order_by if order_by is not None else BxReimburseApply.create_time.desc()
  290. rows = (
  291. query.order_by(order_expr)
  292. .offset((page - 1) * page_size)
  293. .limit(page_size)
  294. .all()
  295. )
  296. return rows, total
  297. @staticmethod
  298. def _has_debit_reimburse(db: Session, tenant_no: str, reimburse_id: str) -> bool:
  299. return (
  300. db.query(BxInvoiceLedger.id)
  301. .filter(
  302. BxInvoiceLedger.tenant_no == tenant_no,
  303. BxInvoiceLedger.reimburse_id == reimburse_id,
  304. BxInvoiceLedger.entry_type == ENTRY_DEBIT_REIMBURSE,
  305. BxInvoiceLedger.del_flag == 0,
  306. )
  307. .first()
  308. is not None
  309. )
  310. @staticmethod
  311. def normalize_invoice_links(
  312. invoice_links: Optional[Sequence[ReimburseInvoiceLinkIn]] = None,
  313. invoice_ids: Optional[Sequence[str]] = None,
  314. ) -> Optional[List[Tuple[str, Optional[str]]]]:
  315. """归一化勾选链接;返回 None 表示未传(不改关联),[] 表示清空。"""
  316. if invoice_links is not None:
  317. normalized: List[Tuple[str, Optional[str]]] = []
  318. seen: set[Tuple[str, str]] = set()
  319. for link in invoice_links:
  320. invoice_id = str(link.invoice_id or "").strip()
  321. if not invoice_id:
  322. continue
  323. attachment_id = str(link.attachment_id or "").strip() or None
  324. key = (invoice_id, attachment_id or "")
  325. if key in seen:
  326. continue
  327. seen.add(key)
  328. normalized.append((invoice_id, attachment_id))
  329. return normalized
  330. if invoice_ids is not None:
  331. normalized_ids: List[Tuple[str, Optional[str]]] = []
  332. seen_ids: set[str] = set()
  333. for raw_id in invoice_ids:
  334. invoice_id = str(raw_id or "").strip()
  335. if not invoice_id or invoice_id in seen_ids:
  336. continue
  337. seen_ids.add(invoice_id)
  338. normalized_ids.append((invoice_id, None))
  339. return normalized_ids
  340. return None
  341. @staticmethod
  342. def sync_reimburse_invoices(
  343. db: Session,
  344. reimburse_id: str,
  345. invoice_ids: Optional[List[str]],
  346. tenant_no: str,
  347. user: BxUserContext,
  348. *,
  349. applicant_id: str,
  350. invoice_links: Optional[Sequence[ReimburseInvoiceLinkIn]] = None,
  351. ) -> None:
  352. links = ReimburseService.normalize_invoice_links(invoice_links, invoice_ids)
  353. if links is None:
  354. return
  355. invoice_id_list = list({invoice_id for invoice_id, _ in links})
  356. if invoice_id_list:
  357. invoices = (
  358. db.query(BxInvoice)
  359. .filter(
  360. BxInvoice.tenant_no == tenant_no,
  361. BxInvoice.id.in_(invoice_id_list),
  362. BxInvoice.del_flag == 0,
  363. )
  364. .all()
  365. )
  366. invoice_map = {inv.id: inv for inv in invoices}
  367. for invoice_id in invoice_id_list:
  368. invoice = invoice_map.get(invoice_id)
  369. if not invoice:
  370. raise HTTPException(
  371. status_code=status.HTTP_400_BAD_REQUEST,
  372. detail=f"发票 {invoice_id} 不存在",
  373. )
  374. if invoice.owner_user_id != applicant_id:
  375. raise HTTPException(
  376. status_code=status.HTTP_400_BAD_REQUEST,
  377. detail=f"发票 {invoice_id} 不属于申请人",
  378. )
  379. attachment_ids = [aid for _, aid in links if aid]
  380. attachment_map: Dict[str, BxAttachment] = {}
  381. if attachment_ids:
  382. attachments = (
  383. db.query(BxAttachment)
  384. .filter(
  385. BxAttachment.id.in_(attachment_ids),
  386. BxAttachment.biz_type == "invoice",
  387. BxAttachment.tenant_no == tenant_no,
  388. BxAttachment.del_flag == 0,
  389. )
  390. .all()
  391. )
  392. attachment_map = {att.id: att for att in attachments}
  393. for invoice_id, attachment_id in links:
  394. if not attachment_id:
  395. continue
  396. att = attachment_map.get(attachment_id)
  397. if not att:
  398. raise HTTPException(
  399. status_code=status.HTTP_400_BAD_REQUEST,
  400. detail=f"发票附件 {attachment_id} 不存在",
  401. )
  402. if att.biz_id != invoice_id:
  403. raise HTTPException(
  404. status_code=status.HTTP_400_BAD_REQUEST,
  405. detail=f"附件 {attachment_id} 不属于发票 {invoice_id}",
  406. )
  407. db.query(BxReimburseInvoice).filter(
  408. BxReimburseInvoice.reimburse_id == reimburse_id,
  409. BxReimburseInvoice.tenant_no == tenant_no,
  410. BxReimburseInvoice.del_flag == 0,
  411. ).update({"del_flag": 1, "update_by": user.id}, synchronize_session=False)
  412. for invoice_id, attachment_id in links:
  413. db.add(
  414. BxReimburseInvoice(
  415. id=generate_uuid(),
  416. reimburse_id=reimburse_id,
  417. invoice_id=invoice_id,
  418. attachment_id=attachment_id,
  419. tenant_no=tenant_no,
  420. create_by=user.id,
  421. create_dept_id=user.dept_id or "",
  422. )
  423. )
  424. db.flush()
  425. @staticmethod
  426. def get_linked_invoice_ids(db: Session, reimburse_id: str, tenant_no: str) -> List[str]:
  427. rows = (
  428. db.query(BxReimburseInvoice.invoice_id)
  429. .filter(
  430. BxReimburseInvoice.reimburse_id == reimburse_id,
  431. BxReimburseInvoice.tenant_no == tenant_no,
  432. BxReimburseInvoice.del_flag == 0,
  433. )
  434. .order_by(BxReimburseInvoice.create_time.asc())
  435. .all()
  436. )
  437. # 同一发票多附件时去重,保持首次出现顺序
  438. seen: set[str] = set()
  439. ordered: List[str] = []
  440. for (invoice_id,) in rows:
  441. if invoice_id in seen:
  442. continue
  443. seen.add(invoice_id)
  444. ordered.append(invoice_id)
  445. return ordered
  446. @staticmethod
  447. def get_linked_invoices_brief(
  448. db: Session, reimburse_id: str, tenant_no: str
  449. ) -> List[ReimburseInvoiceBriefOut]:
  450. rows = (
  451. db.query(BxReimburseInvoice, BxInvoice)
  452. .join(
  453. BxInvoice,
  454. and_(
  455. BxInvoice.id == BxReimburseInvoice.invoice_id,
  456. BxInvoice.tenant_no == tenant_no,
  457. BxInvoice.del_flag == 0,
  458. ),
  459. )
  460. .filter(
  461. BxReimburseInvoice.reimburse_id == reimburse_id,
  462. BxReimburseInvoice.tenant_no == tenant_no,
  463. BxReimburseInvoice.del_flag == 0,
  464. )
  465. .order_by(BxReimburseInvoice.create_time.asc())
  466. .all()
  467. )
  468. if not rows:
  469. return []
  470. explicit_att_ids = [
  471. link.attachment_id for link, _ in rows if (link.attachment_id or "").strip()
  472. ]
  473. invoice_ids_need_fallback = [
  474. invoice.id
  475. for link, invoice in rows
  476. if not (link.attachment_id or "").strip()
  477. ]
  478. att_by_id: Dict[str, BxAttachment] = {}
  479. if explicit_att_ids:
  480. for att in (
  481. db.query(BxAttachment)
  482. .filter(
  483. BxAttachment.id.in_(explicit_att_ids),
  484. BxAttachment.tenant_no == tenant_no,
  485. BxAttachment.del_flag == 0,
  486. )
  487. .all()
  488. ):
  489. att_by_id[att.id] = att
  490. first_att_by_invoice: Dict[str, BxAttachment] = {}
  491. if invoice_ids_need_fallback:
  492. fallback_atts = (
  493. db.query(BxAttachment)
  494. .filter(
  495. BxAttachment.biz_type == "invoice",
  496. BxAttachment.biz_id.in_(invoice_ids_need_fallback),
  497. BxAttachment.tenant_no == tenant_no,
  498. BxAttachment.del_flag == 0,
  499. )
  500. .order_by(BxAttachment.sort_order.asc(), BxAttachment.create_time.asc())
  501. .all()
  502. )
  503. for att in fallback_atts:
  504. first_att_by_invoice.setdefault(att.biz_id, att)
  505. result: List[ReimburseInvoiceBriefOut] = []
  506. sort_keys: List[Tuple[datetime, str]] = []
  507. for link, invoice in rows:
  508. att: Optional[BxAttachment] = None
  509. aid = (link.attachment_id or "").strip()
  510. if aid:
  511. att = att_by_id.get(aid)
  512. if att is None:
  513. att = first_att_by_invoice.get(invoice.id)
  514. # 固定顺序:附件上传时间优先,否则发票创建时间;同时间按附件/发票 ID
  515. ts = att.create_time if att and att.create_time else invoice.create_time
  516. sort_keys.append((ts or datetime.min, str(att.id if att else invoice.id)))
  517. result.append(
  518. ReimburseInvoiceBriefOut(
  519. id=invoice.id,
  520. amount=float(invoice.amount),
  521. owner_user_name=invoice.owner_user_name,
  522. remark=invoice.remark,
  523. attachment_id=att.id if att else (aid or None),
  524. file_name=(att.file_name if att else None),
  525. file_type=(att.file_type if att else None),
  526. create_time=ts,
  527. )
  528. )
  529. # 与发票夹选择弹窗一致:按时间从新到旧
  530. ordered = sorted(
  531. range(len(result)),
  532. key=lambda i: (sort_keys[i][0], sort_keys[i][1]),
  533. reverse=True,
  534. )
  535. return [result[i] for i in ordered]
  536. @staticmethod
  537. def get_linked_invoice_ids_map(
  538. db: Session, reimburse_ids: List[str], tenant_no: str
  539. ) -> Dict[str, List[str]]:
  540. if not reimburse_ids:
  541. return {}
  542. rows = (
  543. db.query(BxReimburseInvoice.reimburse_id, BxReimburseInvoice.invoice_id)
  544. .filter(
  545. BxReimburseInvoice.reimburse_id.in_(reimburse_ids),
  546. BxReimburseInvoice.tenant_no == tenant_no,
  547. BxReimburseInvoice.del_flag == 0,
  548. )
  549. .order_by(BxReimburseInvoice.create_time.asc())
  550. .all()
  551. )
  552. grouped: Dict[str, List[str]] = {}
  553. for reimburse_id, invoice_id in rows:
  554. bucket = grouped.setdefault(reimburse_id, [])
  555. if invoice_id not in bucket:
  556. bucket.append(invoice_id)
  557. return grouped
  558. @staticmethod
  559. def submit(
  560. db: Session,
  561. apply_id: str,
  562. user: BxUserContext,
  563. *,
  564. invoice_ids: Optional[List[str]] = None,
  565. invoice_links: Optional[Sequence[ReimburseInvoiceLinkIn]] = None,
  566. ) -> Optional[BxReimburseApply]:
  567. tenant_no = ReimburseService._tenant_no(db, user)
  568. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, user=user)
  569. if not apply_obj:
  570. return None
  571. if invoice_links is not None or invoice_ids is not None:
  572. ReimburseService.sync_reimburse_invoices(
  573. db,
  574. apply_id,
  575. invoice_ids,
  576. tenant_no,
  577. user,
  578. applicant_id=apply_obj.applicant_id,
  579. invoice_links=invoice_links,
  580. )
  581. if not ReimburseService._has_debit_reimburse(db, tenant_no, apply_id):
  582. amount = Decimal(str(apply_obj.amount or 0))
  583. InvoiceLedgerService.debit_reimburse(
  584. db,
  585. tenant_no=tenant_no,
  586. owner_user_id=apply_obj.applicant_id,
  587. amount=amount,
  588. reimburse_id=apply_id,
  589. user=user,
  590. )
  591. apply_obj.status = "pending"
  592. if not apply_obj.apply_code:
  593. apply_obj.apply_code = generate_apply_code(db, "BX", tenant_no)
  594. apply_obj.update_by = user.id
  595. if apply_obj.wf_id:
  596. nodes = ReimburseService._workflow_nodes(db, apply_obj.wf_id, tenant_no)
  597. if nodes:
  598. apply_obj.current_node = nodes[0].node_name
  599. db.flush()
  600. return apply_obj
  601. @staticmethod
  602. def approve(
  603. db: Session, apply_id: str, approver: BxUserContext, opinion: str
  604. ) -> Optional[BxReimburseApply]:
  605. tenant_no = ReimburseService._tenant_no(db, approver)
  606. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, skip_scope=True)
  607. if not apply_obj or not apply_obj.wf_id:
  608. return None
  609. nodes = ReimburseService._workflow_nodes(db, apply_obj.wf_id, tenant_no)
  610. if not nodes:
  611. return None
  612. current_idx = None
  613. for i, node in enumerate(nodes):
  614. if node.node_name == apply_obj.current_node:
  615. current_idx = i
  616. break
  617. if current_idx is None:
  618. return None
  619. current_node = nodes[current_idx]
  620. if current_node.approver_id != approver.id:
  621. return None
  622. approval = BxReimburseApproval(
  623. id=generate_uuid(),
  624. apply_id=apply_id,
  625. approver_id=approver.id,
  626. approver_name=approver.name,
  627. approval_opinion=opinion,
  628. node_name=current_node.node_name,
  629. node_order=current_node.node_order,
  630. approval_status="approved",
  631. approval_time=datetime.now(),
  632. tenant_no=tenant_no,
  633. create_by=approver.id,
  634. create_dept_id=approver.dept_id or "",
  635. )
  636. db.add(approval)
  637. if current_idx == len(nodes) - 1:
  638. apply_obj.status = "approved"
  639. apply_obj.current_node = None
  640. else:
  641. apply_obj.current_node = nodes[current_idx + 1].node_name
  642. apply_obj.update_by = approver.id
  643. db.flush()
  644. return apply_obj
  645. @staticmethod
  646. def reject(
  647. db: Session, apply_id: str, approver: BxUserContext, opinion: str
  648. ) -> Optional[BxReimburseApply]:
  649. tenant_no = ReimburseService._tenant_no(db, approver)
  650. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, skip_scope=True)
  651. if not apply_obj or not apply_obj.wf_id:
  652. return None
  653. nodes = ReimburseService._workflow_nodes(db, apply_obj.wf_id, tenant_no)
  654. current_node = None
  655. for node in nodes:
  656. if node.node_name == apply_obj.current_node:
  657. current_node = node
  658. break
  659. if current_node is None or current_node.approver_id != approver.id:
  660. return None
  661. approval = BxReimburseApproval(
  662. id=generate_uuid(),
  663. apply_id=apply_id,
  664. approver_id=approver.id,
  665. approver_name=approver.name,
  666. approval_opinion=opinion,
  667. node_name=current_node.node_name,
  668. node_order=current_node.node_order,
  669. approval_status="rejected",
  670. approval_time=datetime.now(),
  671. tenant_no=tenant_no,
  672. create_by=approver.id,
  673. create_dept_id=approver.dept_id or "",
  674. )
  675. db.add(approval)
  676. apply_obj.status = "rejected"
  677. apply_obj.current_node = None
  678. apply_obj.update_by = approver.id
  679. InvoiceLedgerService.release_reimburse(
  680. db, tenant_no=tenant_no, reimburse_id=apply_id, user=approver
  681. )
  682. db.flush()
  683. return apply_obj
  684. @staticmethod
  685. def withdraw(db: Session, apply_id: str, user: BxUserContext) -> Optional[BxReimburseApply]:
  686. tenant_no = ReimburseService._tenant_no(db, user)
  687. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, user=user)
  688. if not apply_obj:
  689. return None
  690. if apply_obj.applicant_id != user.id:
  691. return None
  692. if apply_obj.status != "pending":
  693. return None
  694. apply_obj.status = "draft"
  695. apply_obj.current_node = None
  696. apply_obj.update_by = user.id
  697. InvoiceLedgerService.release_reimburse(
  698. db, tenant_no=tenant_no, reimburse_id=apply_id, user=user
  699. )
  700. db.flush()
  701. return apply_obj
  702. @staticmethod
  703. def _approval_record_dict(approval: BxReimburseApproval, apply_id: str) -> Dict[str, Any]:
  704. return {
  705. "id": approval.id,
  706. "reimburse_id": apply_id,
  707. "node_name": approval.node_name,
  708. "node_order": approval.node_order,
  709. "approval_status": approval.approval_status,
  710. "approval_opinion": approval.approval_opinion,
  711. "approver_name": approval.approver_name,
  712. "approval_time": approval.approval_time,
  713. }
  714. @staticmethod
  715. def _latest_approval_per_node(
  716. approvals: Sequence[BxReimburseApproval],
  717. ) -> Dict[int, BxReimburseApproval]:
  718. """同一节点多次审批时,取 approval_time 最新的一条作为当前节点状态。"""
  719. approval_map: Dict[int, BxReimburseApproval] = {}
  720. for approval in approvals:
  721. existing = approval_map.get(approval.node_order)
  722. if existing is None:
  723. approval_map[approval.node_order] = approval
  724. continue
  725. approval_time = approval.approval_time or datetime.min
  726. existing_time = existing.approval_time or datetime.min
  727. if approval_time >= existing_time:
  728. approval_map[approval.node_order] = approval
  729. return approval_map
  730. @staticmethod
  731. def _query_approvals(db: Session, apply_id: str, tenant_no: str) -> List[BxReimburseApproval]:
  732. return (
  733. db.query(BxReimburseApproval)
  734. .filter(
  735. BxReimburseApproval.apply_id == apply_id,
  736. BxReimburseApproval.tenant_no == tenant_no,
  737. BxReimburseApproval.del_flag == 0,
  738. )
  739. .all()
  740. )
  741. @staticmethod
  742. def get_approvals(
  743. db: Session, apply_id: str, tenant_no: str, *, user: Optional[BxUserContext] = None
  744. ) -> List[Dict[str, Any]]:
  745. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, user=user)
  746. if not apply_obj or not apply_obj.wf_id:
  747. return []
  748. wf = BxCrudService.get_by_id(BxWorkflowDef, db, apply_obj.wf_id, tenant_no)
  749. if not wf:
  750. return []
  751. nodes = ReimburseService._workflow_nodes(db, wf.id, tenant_no)
  752. if not nodes:
  753. return []
  754. approvals = ReimburseService._query_approvals(db, apply_id, tenant_no)
  755. cycle_approvals = filter_current_cycle_approvals(approvals, apply_obj.status)
  756. approval_map = ReimburseService._latest_approval_per_node(cycle_approvals)
  757. result: List[Dict[str, Any]] = []
  758. for node in nodes:
  759. approval = approval_map.get(node.node_order)
  760. if approval:
  761. result.append(ReimburseService._approval_record_dict(approval, apply_id))
  762. else:
  763. result.append(
  764. {
  765. "id": "0",
  766. "reimburse_id": apply_id,
  767. "node_name": node.node_name,
  768. "node_order": node.node_order,
  769. "approval_status": "pending",
  770. "approval_opinion": None,
  771. "approver_name": node.approver_name or "",
  772. "approval_time": None,
  773. }
  774. )
  775. return result
  776. @staticmethod
  777. def get_approval_history(
  778. db: Session, apply_id: str, tenant_no: str, *, user: Optional[BxUserContext] = None
  779. ) -> List[Dict[str, Any]]:
  780. """返回全部审批动作,按时间正序排列,用于展示完整审批历史。"""
  781. if not ReimburseService.get_by_id(db, apply_id, tenant_no, user=user):
  782. return []
  783. approvals = (
  784. db.query(BxReimburseApproval)
  785. .filter(
  786. BxReimburseApproval.apply_id == apply_id,
  787. BxReimburseApproval.tenant_no == tenant_no,
  788. BxReimburseApproval.del_flag == 0,
  789. )
  790. .order_by(BxReimburseApproval.approval_time.asc(), BxReimburseApproval.create_time.asc())
  791. .all()
  792. )
  793. return [ReimburseService._approval_record_dict(a, apply_id) for a in approvals]
  794. @staticmethod
  795. def create_with_details(
  796. db: Session,
  797. obj_in: dict,
  798. details: List[dict],
  799. *,
  800. user: BxUserContext,
  801. applicant_id: Optional[str] = None,
  802. applicant_name: Optional[str] = None,
  803. ) -> BxReimburseApply:
  804. tenant_no = ReimburseService._tenant_no(db, user)
  805. data = dict(obj_in)
  806. total_amount = sum(float(d.get("amount", 0) or 0) for d in details)
  807. reimburse_date = parse_reimburse_date(data.pop("reimburse_date", None))
  808. requested_title = data.pop("title", None)
  809. first_detail = details[0] if details else {}
  810. resolved_id = applicant_id or user.id
  811. resolved_name = applicant_name or user.name
  812. apply_obj: BxReimburseApply | None = None
  813. # 去掉调用方预生成的单号,在重试循环内重新取号(避免撞 uk_apply_code 后仍用旧号)
  814. data.pop("apply_code", None)
  815. for attempt in range(5):
  816. title, title_seq = resolve_reimburse_title(
  817. db, resolved_name, tenant_no, reimburse_date, requested_title
  818. )
  819. apply_obj = BxReimburseApply(
  820. id=generate_uuid(),
  821. apply_code=generate_apply_code(db, "BX", tenant_no),
  822. title=title,
  823. reimburse_date=reimburse_date,
  824. title_seq=title_seq,
  825. amount=total_amount,
  826. expense_id=str(first_detail.get("expense_id") or ""),
  827. expense_name=first_detail.get("expense_name") or "",
  828. applicant_id=resolved_id,
  829. applicant_name=resolved_name,
  830. tenant_no=tenant_no,
  831. create_by=user.id,
  832. create_dept_id=user.dept_id or "",
  833. **data,
  834. )
  835. db.add(apply_obj)
  836. try:
  837. db.flush()
  838. break
  839. except IntegrityError:
  840. db.rollback()
  841. if attempt >= 4:
  842. raise
  843. continue
  844. if apply_obj is None:
  845. raise RuntimeError("报销申请创建失败")
  846. for d in details:
  847. detail = BxReimburseDetail(
  848. id=generate_uuid(),
  849. reimburse_id=apply_obj.id,
  850. expense_id=str(d["expense_id"]),
  851. expense_name=d["expense_name"],
  852. amount=d["amount"],
  853. remark=d.get("remark"),
  854. occur_date=d.get("occur_date"),
  855. tenant_no=tenant_no,
  856. create_by=user.id,
  857. create_dept_id=user.dept_id or "",
  858. )
  859. db.add(detail)
  860. db.flush()
  861. return apply_obj
  862. @staticmethod
  863. def update_with_details(
  864. db: Session,
  865. apply_id: str,
  866. obj_in: dict,
  867. details: Optional[List[dict]],
  868. *,
  869. user: BxUserContext,
  870. ) -> Optional[BxReimburseApply]:
  871. tenant_no = ReimburseService._tenant_no(db, user)
  872. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, user=user)
  873. if not apply_obj:
  874. return None
  875. reimburse_date = None
  876. date_changed = False
  877. if "reimburse_date" in obj_in:
  878. reimburse_date = parse_reimburse_date(obj_in.pop("reimburse_date"))
  879. date_changed = reimburse_date != apply_obj.reimburse_date
  880. requested_title = obj_in.pop("title", None)
  881. prev_applicant_id = apply_obj.applicant_id
  882. for key, value in obj_in.items():
  883. setattr(apply_obj, key, value)
  884. if reimburse_date is not None:
  885. apply_obj.reimburse_date = reimburse_date
  886. if requested_title is not None:
  887. apply_obj.title = str(requested_title).strip()
  888. applicant_changed = apply_obj.applicant_id != prev_applicant_id
  889. should_regen_title = (date_changed or applicant_changed) and is_auto_reimburse_title(
  890. apply_obj.title
  891. )
  892. if should_regen_title:
  893. seq = allocate_reimburse_title_seq(
  894. db, tenant_no, apply_obj.reimburse_date, exclude_apply_id=apply_id
  895. )
  896. apply_obj.title_seq = seq
  897. apply_obj.title = format_reimburse_title(
  898. apply_obj.applicant_name, apply_obj.reimburse_date, seq
  899. )
  900. elif requested_title is not None and not is_auto_reimburse_title(apply_obj.title):
  901. apply_obj.title_seq = None
  902. if details is not None:
  903. db.query(BxReimburseDetail).filter(
  904. BxReimburseDetail.reimburse_id == apply_id,
  905. BxReimburseDetail.tenant_no == tenant_no,
  906. BxReimburseDetail.del_flag == 0,
  907. ).update({"del_flag": 1}, synchronize_session=False)
  908. total_amount = Decimal("0")
  909. first_expense_name = ""
  910. first_expense_id = ""
  911. for i, d in enumerate(details):
  912. detail = BxReimburseDetail(
  913. id=generate_uuid(),
  914. reimburse_id=apply_id,
  915. expense_id=str(d["expense_id"]),
  916. expense_name=d["expense_name"],
  917. amount=d["amount"],
  918. remark=d.get("remark"),
  919. occur_date=d.get("occur_date"),
  920. tenant_no=tenant_no,
  921. create_by=user.id,
  922. create_dept_id=user.dept_id or "",
  923. )
  924. db.add(detail)
  925. total_amount += Decimal(str(d["amount"]))
  926. if i == 0:
  927. first_expense_name = d["expense_name"]
  928. first_expense_id = str(d["expense_id"])
  929. apply_obj.amount = total_amount
  930. apply_obj.expense_name = first_expense_name
  931. apply_obj.expense_id = first_expense_id
  932. apply_obj.update_by = user.id
  933. db.flush()
  934. return apply_obj
  935. @staticmethod
  936. def get_details(db: Session, reimburse_id: str, tenant_no: str) -> List[BxReimburseDetail]:
  937. return (
  938. db.query(BxReimburseDetail)
  939. .filter(
  940. BxReimburseDetail.reimburse_id == reimburse_id,
  941. BxReimburseDetail.tenant_no == tenant_no,
  942. BxReimburseDetail.del_flag == 0,
  943. )
  944. .order_by(BxReimburseDetail.create_time)
  945. .all()
  946. )
  947. @staticmethod
  948. def detail_expense_keyword_match(name_kw: str):
  949. """明细行:费用项目名称或发生日期(YYYY-MM-DD)模糊包含。"""
  950. return or_(
  951. BxReimburseDetail.expense_name.contains(name_kw),
  952. and_(
  953. BxReimburseDetail.occur_date.isnot(None),
  954. func.date_format(BxReimburseDetail.occur_date, "%Y-%m-%d").contains(name_kw),
  955. ),
  956. )
  957. @staticmethod
  958. def build_expense_name_condition(
  959. db: Session, tenant_no: str, expense_name: Optional[str]
  960. ):
  961. name_kw = str(expense_name or "").strip()
  962. if not name_kw:
  963. return None
  964. detail_reimburse_ids = (
  965. db.query(BxReimburseDetail.reimburse_id)
  966. .filter(
  967. BxReimburseDetail.tenant_no == tenant_no,
  968. BxReimburseDetail.del_flag == 0,
  969. ReimburseService.detail_expense_keyword_match(name_kw),
  970. )
  971. )
  972. return or_(
  973. BxReimburseApply.expense_name.contains(name_kw),
  974. BxReimburseApply.id.in_(detail_reimburse_ids),
  975. )
  976. @staticmethod
  977. def get_details_map_by_reimburse_ids(
  978. db: Session, reimburse_ids: List[str], tenant_no: str
  979. ) -> Dict[str, List[BxReimburseDetail]]:
  980. if not reimburse_ids:
  981. return {}
  982. detail_rows = (
  983. db.query(BxReimburseDetail)
  984. .filter(
  985. BxReimburseDetail.reimburse_id.in_(reimburse_ids),
  986. BxReimburseDetail.tenant_no == tenant_no,
  987. BxReimburseDetail.del_flag == 0,
  988. )
  989. .order_by(BxReimburseDetail.create_time)
  990. .all()
  991. )
  992. grouped: Dict[str, List[BxReimburseDetail]] = {}
  993. for detail in detail_rows:
  994. grouped.setdefault(detail.reimburse_id, []).append(detail)
  995. return grouped
  996. @staticmethod
  997. def out_rows_with_attachment_counts(
  998. db: Session, tenant_no: str, rows: List[BxReimburseApply]
  999. ) -> List[ReimburseApplyOut]:
  1000. if not rows:
  1001. return []
  1002. ids = [r.id for r in rows]
  1003. counts = AttachmentService.count_invoice_attach_by_biz_ids(
  1004. db, "reimburse_apply", ids, tenant_no
  1005. )
  1006. details_map = ReimburseService.get_details_map_by_reimburse_ids(db, ids, tenant_no)
  1007. invoice_ids_map = ReimburseService.get_linked_invoice_ids_map(db, ids, tenant_no)
  1008. out_list: List[ReimburseApplyOut] = []
  1009. for row in rows:
  1010. out = ReimburseApplyOut.model_validate(row)
  1011. inv, att = counts.get(row.id, (0, 0))
  1012. detail_rows = details_map.get(row.id, [])
  1013. linked_ids = invoice_ids_map.get(row.id, [])
  1014. out_list.append(
  1015. out.model_copy(
  1016. update={
  1017. "invoice_count": inv,
  1018. "attach_count": att,
  1019. "details": [ReimburseDetailOut.model_validate(d) for d in detail_rows],
  1020. "invoice_ids": linked_ids,
  1021. }
  1022. )
  1023. )
  1024. return out_list
  1025. @staticmethod
  1026. def out_one_with_attachment_counts(
  1027. db: Session, tenant_no: str, row: BxReimburseApply
  1028. ) -> ReimburseApplyOut:
  1029. return ReimburseService.out_rows_with_attachment_counts(db, tenant_no, [row])[0]
  1030. @staticmethod
  1031. def build_apply_out_with_details(
  1032. db: Session, tenant_no: str, reimburse: BxReimburseApply
  1033. ) -> ReimburseApplyOut:
  1034. out = ReimburseService.out_one_with_attachment_counts(db, tenant_no, reimburse)
  1035. detail_rows = ReimburseService.get_details(db, reimburse.id, tenant_no)
  1036. out.details = [ReimburseDetailOut.model_validate(d) for d in detail_rows]
  1037. out.invoice_ids = ReimburseService.get_linked_invoice_ids(db, reimburse.id, tenant_no)
  1038. out.invoices = ReimburseService.get_linked_invoices_brief(db, reimburse.id, tenant_no)
  1039. return out
  1040. @staticmethod
  1041. def global_status_counts(db: Session, tenant_no: str, *, user: BxUserContext) -> Dict[str, int]:
  1042. query = db.query(BxReimburseApply.status, func.count()).filter(
  1043. BxReimburseApply.del_flag == 0, BxReimburseApply.tenant_no == tenant_no
  1044. )
  1045. query = apply_bx_scope(query, BxReimburseApply, user, db, tenant_no)
  1046. rows = query.group_by(BxReimburseApply.status).all()
  1047. return {str(s or ""): int(c) for s, c in rows}
  1048. @staticmethod
  1049. def _my_apply_base(db: Session, user: BxUserContext, tenant_no: str):
  1050. query = db.query(BxReimburseApply).filter(
  1051. BxReimburseApply.del_flag == 0,
  1052. BxReimburseApply.tenant_no == tenant_no,
  1053. or_(
  1054. BxReimburseApply.applicant_id == user.id,
  1055. BxReimburseApply.create_by == user.id,
  1056. ),
  1057. )
  1058. return apply_bx_scope(query, BxReimburseApply, user, db, tenant_no)
  1059. @staticmethod
  1060. def my_apply_stats(db: Session, user: BxUserContext, tenant_no: str) -> dict:
  1061. base_q = ReimburseService._my_apply_base(db, user, tenant_no).subquery()
  1062. base = BxReimburseApply.id.in_(db.query(base_q.c.id))
  1063. total_count = (
  1064. db.query(func.count()).select_from(BxReimburseApply).filter(base).scalar() or 0
  1065. )
  1066. pending = (
  1067. db.query(func.count())
  1068. .select_from(BxReimburseApply)
  1069. .filter(base, BxReimburseApply.status == "pending")
  1070. .scalar()
  1071. or 0
  1072. )
  1073. approved_count = (
  1074. db.query(func.count())
  1075. .select_from(BxReimburseApply)
  1076. .filter(base, BxReimburseApply.status.in_(_REIMBURSE_MY_STATS_PASSED_STATUSES))
  1077. .scalar()
  1078. or 0
  1079. )
  1080. rejected_count = (
  1081. db.query(func.count())
  1082. .select_from(BxReimburseApply)
  1083. .filter(base, BxReimburseApply.status == "rejected")
  1084. .scalar()
  1085. or 0
  1086. )
  1087. approved_amount = (
  1088. db.query(func.coalesce(func.sum(BxReimburseApply.amount), 0))
  1089. .filter(base, BxReimburseApply.status.in_(_REIMBURSE_MY_STATS_PASSED_STATUSES))
  1090. .scalar()
  1091. or 0
  1092. )
  1093. m0, m1 = current_month_bounds()
  1094. month_apply_amount = (
  1095. db.query(func.coalesce(func.sum(BxReimburseApply.amount), 0))
  1096. .filter(base, BxReimburseApply.create_time >= m0, BxReimburseApply.create_time < m1)
  1097. .scalar()
  1098. or 0
  1099. )
  1100. month_apply_count = (
  1101. db.query(func.count())
  1102. .select_from(BxReimburseApply)
  1103. .filter(base, BxReimburseApply.create_time >= m0, BxReimburseApply.create_time < m1)
  1104. .scalar()
  1105. or 0
  1106. )
  1107. return {
  1108. "total_count": int(total_count),
  1109. "pending_count": int(pending),
  1110. "approved_count": int(approved_count),
  1111. "rejected_count": int(rejected_count),
  1112. "approved_amount": float(approved_amount),
  1113. "month_apply_amount": float(month_apply_amount),
  1114. "month_apply_count": int(month_apply_count),
  1115. }
  1116. @staticmethod
  1117. def pending_approval_stats(db: Session, user: BxUserContext, tenant_no: str) -> dict:
  1118. subquery = (
  1119. db.query(BxReimburseApply.id)
  1120. .join(
  1121. BxWorkflowNode,
  1122. and_(
  1123. BxWorkflowNode.wf_id == BxReimburseApply.wf_id,
  1124. BxWorkflowNode.node_name == BxReimburseApply.current_node,
  1125. BxWorkflowNode.tenant_no == tenant_no,
  1126. BxWorkflowNode.del_flag == 0,
  1127. ),
  1128. )
  1129. .filter(
  1130. BxReimburseApply.del_flag == 0,
  1131. BxReimburseApply.tenant_no == tenant_no,
  1132. BxReimburseApply.status == "pending",
  1133. BxWorkflowNode.approver_id == user.id,
  1134. )
  1135. ).subquery()
  1136. total = db.query(func.count()).select_from(subquery).scalar() or 0
  1137. m0, m1 = current_month_bounds()
  1138. month_sum = (
  1139. db.query(func.coalesce(func.sum(BxReimburseApply.amount), 0))
  1140. .join(subquery, BxReimburseApply.id == subquery.c.id)
  1141. .filter(BxReimburseApply.create_time >= m0, BxReimburseApply.create_time < m1)
  1142. .scalar()
  1143. or 0
  1144. )
  1145. return {"pending_total": int(total), "month_pending_amount": float(month_sum)}
  1146. @staticmethod
  1147. def pay(
  1148. db: Session,
  1149. apply_id: str,
  1150. user: BxUserContext,
  1151. pay_remark: str = "",
  1152. actual_amount: Optional[float] = None,
  1153. ) -> Optional[BxReimburseApply]:
  1154. tenant_no = ReimburseService._tenant_no(db, user)
  1155. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, user=user)
  1156. if not apply_obj:
  1157. return None
  1158. apply_obj.status = "processed"
  1159. apply_obj.update_by = user.id
  1160. apply_obj.pay_status = "paid"
  1161. apply_obj.pay_time = datetime.now()
  1162. apply_obj.pay_user_id = user.id
  1163. apply_obj.pay_user_name = user.name
  1164. if actual_amount is not None:
  1165. apply_obj.actual_amount = actual_amount
  1166. db.flush()
  1167. return apply_obj
  1168. @staticmethod
  1169. def offset(
  1170. db: Session,
  1171. apply_id: str,
  1172. advance_id: str,
  1173. amount: float,
  1174. user: BxUserContext,
  1175. remark: str = "",
  1176. ) -> Optional[BxReimburseApply]:
  1177. tenant_no = ReimburseService._tenant_no(db, user)
  1178. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, user=user)
  1179. if not apply_obj:
  1180. return None
  1181. apply_obj.status = "offset"
  1182. apply_obj.offset_advance_id = advance_id
  1183. apply_obj.offset_amount = amount
  1184. apply_obj.update_by = user.id
  1185. offset_record = BxReimburseOffset(
  1186. id=generate_uuid(),
  1187. reimburse_id=apply_id,
  1188. advance_id=advance_id,
  1189. offset_amount=amount,
  1190. remark=remark or None,
  1191. tenant_no=tenant_no,
  1192. create_by=user.name,
  1193. create_dept_id=user.dept_id or "",
  1194. )
  1195. db.add(offset_record)
  1196. db.flush()
  1197. return apply_obj
  1198. @staticmethod
  1199. def batch_offset(
  1200. db: Session,
  1201. apply_id: str,
  1202. items: List[dict],
  1203. pay_amount: float,
  1204. user: BxUserContext,
  1205. remark: str = "",
  1206. ) -> Optional[BxReimburseApply]:
  1207. tenant_no = ReimburseService._tenant_no(db, user)
  1208. apply_obj = ReimburseService.get_by_id(db, apply_id, tenant_no, user=user)
  1209. if not apply_obj:
  1210. return None
  1211. has_offsets = bool(items)
  1212. total_offset = 0.0
  1213. first_advance_id = None
  1214. for item in items:
  1215. advance_id = str(item["advance_id"])
  1216. offset_amount = float(item["offset_amount"])
  1217. if first_advance_id is None:
  1218. first_advance_id = advance_id
  1219. total_offset += offset_amount
  1220. offset_record = BxReimburseOffset(
  1221. id=generate_uuid(),
  1222. reimburse_id=apply_id,
  1223. advance_id=advance_id,
  1224. offset_amount=offset_amount,
  1225. remark=remark or None,
  1226. tenant_no=tenant_no,
  1227. create_by=user.name,
  1228. create_dept_id=user.dept_id or "",
  1229. )
  1230. db.add(offset_record)
  1231. if first_advance_id is not None:
  1232. apply_obj.offset_advance_id = first_advance_id
  1233. apply_obj.offset_amount = total_offset
  1234. if pay_amount > 0:
  1235. apply_obj.pay_status = "paid"
  1236. apply_obj.pay_time = datetime.now()
  1237. apply_obj.pay_user_id = user.id
  1238. apply_obj.pay_user_name = user.name
  1239. apply_obj.actual_amount = pay_amount
  1240. apply_obj.status = "processed"
  1241. elif has_offsets:
  1242. apply_obj.actual_amount = 0
  1243. apply_obj.status = "processed"
  1244. apply_obj.update_by = user.id
  1245. db.flush()
  1246. return apply_obj
  1247. @staticmethod
  1248. def finance_payment_stats(db: Session, tenant_no: str, *, user: BxUserContext) -> dict:
  1249. base_q = (
  1250. db.query(BxReimburseApply)
  1251. .filter(BxReimburseApply.del_flag == 0, BxReimburseApply.tenant_no == tenant_no)
  1252. )
  1253. base_q = apply_bx_scope(base_q, BxReimburseApply, user, db, tenant_no).subquery()
  1254. base = BxReimburseApply.id.in_(db.query(base_q.c.id))
  1255. pending = (
  1256. db.query(func.count())
  1257. .select_from(BxReimburseApply)
  1258. .filter(base, BxReimburseApply.status == "approved")
  1259. .scalar()
  1260. or 0
  1261. )
  1262. bank_paid = BxReimburseApply.pay_time.isnot(None)
  1263. paid = (
  1264. db.query(func.count()).select_from(BxReimburseApply).filter(base, bank_paid).scalar() or 0
  1265. )
  1266. offset_settled = or_(
  1267. BxReimburseApply.status == "offset",
  1268. and_(
  1269. BxReimburseApply.status == "processed",
  1270. BxReimburseApply.pay_time.is_(None),
  1271. func.coalesce(BxReimburseApply.offset_amount, 0) > 0,
  1272. ),
  1273. )
  1274. offset = (
  1275. db.query(func.count()).select_from(BxReimburseApply).filter(base, offset_settled).scalar() or 0
  1276. )
  1277. m0, m1 = current_month_bounds()
  1278. month_paid = (
  1279. db.query(func.coalesce(func.sum(BxReimburseApply.amount), 0))
  1280. .filter(base, bank_paid, BxReimburseApply.pay_time >= m0, BxReimburseApply.pay_time < m1)
  1281. .scalar()
  1282. or 0
  1283. )
  1284. month_offset = (
  1285. db.query(func.coalesce(func.sum(BxReimburseApply.amount), 0))
  1286. .filter(base, offset_settled, BxReimburseApply.update_time >= m0, BxReimburseApply.update_time < m1)
  1287. .scalar()
  1288. or 0
  1289. )
  1290. return {
  1291. "pending_count": int(pending),
  1292. "paid_count": int(paid),
  1293. "offset_count": int(offset),
  1294. "month_processed_amount": float(month_paid) + float(month_offset),
  1295. }