shanxia/bx/bx-backend/app/services/reimburse.py

from datetime import datetime

from sqlalchemy import and_, func, or_

from app.utils.date_range import current_month_bounds, parse_create_time_day_bounds
from sqlalchemy.orm import Session

from app.models.reimburse import ReimburseApply, ReimburseApproval, ReimburseOffset
from app.models.workflow import WorkflowDef, WorkflowNode
from app.schemas.reimburse import ReimburseApplyOut, ReimburseDetailOut
from app.services.attachment import AttachmentService
from app.services.base import CrudService
from app.utils.codegen import generate_apply_code, generate_title

# 我的报销统计「已通过」：含待财务处理的 approved，以及已发放/冲抵后的终态（与列表筛选项「审批通过」语义区分）
_REIMBURSE_MY_STATS_PASSED_STATUSES = ("approved", "processed", "paid", "offset")


class ReimburseService:

    @staticmethod
    def get_by_id(db: Session, id: int) -> ReimburseApply | None:
        return CrudService.get_by_id(ReimburseApply, db, id)

    @staticmethod
    def get_paged(db: Session, page: int, page_size: int, filters: list | None = None):
        return CrudService.get_paged(ReimburseApply, db, page, page_size, filters)

    @staticmethod
    def create(db: Session, obj_in: dict) -> ReimburseApply:
        return CrudService.create(ReimburseApply, db, obj_in)

    @staticmethod
    def update(db: Session, id: int, obj_in: dict) -> ReimburseApply | None:
        return CrudService.update(ReimburseApply, db, id, obj_in)

    @staticmethod
    def soft_delete(db: Session, id: int) -> bool:
        return CrudService.soft_delete(ReimburseApply, db, id)

    @staticmethod
    def pending_approval(
        db: Session,
        user,
        page: int,
        page_size: int,
        keyword: str = "",
        applicant_id: int | None = None,
        apply_date: str | None = None,
    ):
        """查询待当前用户审批的报销申请（current_node 对应的 WorkflowNode.approver_id == user.id）"""
        subquery = (
            db.query(ReimburseApply.id)
            .join(WorkflowNode, and_(
                WorkflowNode.wf_id == ReimburseApply.wf_id,
                WorkflowNode.node_name == ReimburseApply.current_node,
            ))
            .filter(
                ReimburseApply.del_flag == 0,
                ReimburseApply.status == "pending",
                WorkflowNode.approver_id == user.id,
            )
        )
        if keyword:
            subquery = subquery.filter(
                ReimburseApply.apply_code.contains(keyword) | ReimburseApply.applicant_name.contains(keyword)
            )
        if applicant_id is not None:
            subquery = subquery.filter(ReimburseApply.applicant_id == applicant_id)
        bounds = parse_create_time_day_bounds(apply_date)
        if bounds:
            d0, d1 = bounds
            subquery = subquery.filter(ReimburseApply.create_time >= d0, ReimburseApply.create_time < d1)
        subquery = subquery.subquery()
        total = db.query(func.count()).select_from(subquery).scalar()
        rows = (
            db.query(ReimburseApply)
            .join(subquery, ReimburseApply.id == subquery.c.id)
            .order_by(ReimburseApply.id.desc())
            .offset((page - 1) * page_size)
            .limit(page_size)
            .all()
        )
        return rows, total or 0

    @staticmethod
    def approved_by_me(
        db: Session,
        user,
        page: int,
        page_size: int,
        keyword: str = "",
        applicant_id: int | None = None,
        apply_date: str | None = None,
    ):
        """查询当前用户已审批过的报销申请"""
        subquery = (
            db.query(ReimburseApproval.apply_id)
            .filter(ReimburseApproval.approver_id == user.id)
            .distinct()
            .subquery()
        )
        query = (
            db.query(ReimburseApply)
            .join(subquery, ReimburseApply.id == subquery.c.apply_id)
            .filter(ReimburseApply.del_flag == 0)
        )
        if keyword:
            query = query.filter(
                ReimburseApply.apply_code.contains(keyword) | ReimburseApply.applicant_name.contains(keyword)
            )
        if applicant_id is not None:
            query = query.filter(ReimburseApply.applicant_id == applicant_id)
        bounds = parse_create_time_day_bounds(apply_date)
        if bounds:
            d0, d1 = bounds
            query = query.filter(ReimburseApply.create_time >= d0, ReimburseApply.create_time < d1)
        total = query.count()
        rows = (
            query.order_by(ReimburseApply.id.desc())
            .offset((page - 1) * page_size)
            .limit(page_size)
            .all()
        )
        return rows, total

    @staticmethod
    def submit(db: Session, apply_id: int, user) -> ReimburseApply | None:
        apply_obj = (
            db.query(ReimburseApply)
            .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
            .first()
        )
        if not apply_obj:
            return None
        apply_obj.status = "pending"
        if not apply_obj.apply_code:
            apply_obj.apply_code = generate_apply_code(db, "BX")
        apply_obj.update_by = str(user.id)
        wf = (
            db.query(WorkflowDef)
            .filter(WorkflowDef.id == apply_obj.wf_id, WorkflowDef.del_flag == 0)
            .first()
        )
        if wf:
            nodes = (
                db.query(WorkflowNode)
                .filter(WorkflowNode.wf_id == wf.id)
                .order_by(WorkflowNode.node_order)
                .all()
            )
            if nodes:
                apply_obj.current_node = nodes[0].node_name
        db.flush()
        return apply_obj

    @staticmethod
    def approve(db: Session, apply_id: int, approver, opinion: str) -> ReimburseApply | None:
        apply_obj = (
            db.query(ReimburseApply)
            .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
            .first()
        )
        if not apply_obj:
            return None
        wf = (
            db.query(WorkflowDef)
            .filter(WorkflowDef.id == apply_obj.wf_id, WorkflowDef.del_flag == 0)
            .first()
        )
        if not wf:
            return None
        nodes = (
            db.query(WorkflowNode)
            .filter(WorkflowNode.wf_id == wf.id)
            .order_by(WorkflowNode.node_order)
            .all()
        )
        current_idx = None
        for i, node in enumerate(nodes):
            if node.node_name == apply_obj.current_node:
                current_idx = i
                break
        if current_idx is None:
            return None
        current_node = nodes[current_idx]
        if current_node.approver_id != approver.id:
            return None
        approval = ReimburseApproval(
            apply_id=apply_id,
            approver_id=approver.id,
            approver_name=approver.username,
            approval_opinion=opinion,
            node_name=current_node.node_name,
            node_order=current_node.node_order,
            approval_status="approved",
            approval_time=datetime.now(),
        )
        db.add(approval)
        if current_idx == len(nodes) - 1:
            apply_obj.status = "approved"
            apply_obj.current_node = None
        else:
            apply_obj.current_node = nodes[current_idx + 1].node_name
        apply_obj.update_by = str(approver.id)
        db.flush()
        return apply_obj

    @staticmethod
    def reject(db: Session, apply_id: int, approver, opinion: str) -> ReimburseApply | None:
        apply_obj = (
            db.query(ReimburseApply)
            .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
            .first()
        )
        if not apply_obj:
            return None
        wf = (
            db.query(WorkflowDef)
            .filter(WorkflowDef.id == apply_obj.wf_id, WorkflowDef.del_flag == 0)
            .first()
        )
        if not wf:
            return None
        nodes = (
            db.query(WorkflowNode)
            .filter(WorkflowNode.wf_id == wf.id)
            .order_by(WorkflowNode.node_order)
            .all()
        )
        current_node = None
        for node in nodes:
            if node.node_name == apply_obj.current_node:
                current_node = node
                break
        if current_node is None:
            return None
        if current_node.approver_id != approver.id:
            return None
        approval = ReimburseApproval(
            apply_id=apply_id,
            approver_id=approver.id,
            approver_name=approver.username,
            approval_opinion=opinion,
            node_name=current_node.node_name,
            node_order=current_node.node_order,
            approval_status="rejected",
            approval_time=datetime.now(),
        )
        db.add(approval)
        apply_obj.status = "rejected"
        apply_obj.current_node = None
        apply_obj.update_by = str(approver.id)
        db.flush()
        return apply_obj

    @staticmethod
    def pay(
        db: Session,
        apply_id: int,
        user,
        pay_remark: str = "",
        actual_amount: float | None = None,
    ) -> ReimburseApply | None:
        apply_obj = (
            db.query(ReimburseApply)
            .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
            .first()
        )
        if not apply_obj:
            return None
        apply_obj.status = "processed"
        apply_obj.update_by = str(user.id)
        apply_obj.pay_status = "paid"
        apply_obj.pay_time = datetime.now()
        apply_obj.pay_user_id = user.id
        apply_obj.pay_user_name = user.real_name or user.username
        if actual_amount is not None:
            apply_obj.actual_amount = actual_amount
        db.flush()
        return apply_obj

    @staticmethod
    def offset(
        db: Session, apply_id: int, advance_id: int, amount: float, user, remark: str = ""
    ) -> ReimburseApply | None:
        apply_obj = (
            db.query(ReimburseApply)
            .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
            .first()
        )
        if not apply_obj:
            return None
        apply_obj.status = "offset"
        apply_obj.offset_advance_id = advance_id
        apply_obj.offset_amount = amount
        apply_obj.update_by = str(user.id)

        offset_record = ReimburseOffset(
            reimburse_id=apply_id,
            advance_id=advance_id,
            offset_amount=amount,
            remark=remark or None,
            create_by=(user.real_name or user.username),
        )
        db.add(offset_record)
        db.flush()
        return apply_obj

    @staticmethod
    def batch_offset(
        db: Session, apply_id: int, items: list[dict], pay_amount: float, user, remark: str = ""
    ) -> ReimburseApply | None:
        apply_obj = (
            db.query(ReimburseApply)
            .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
            .first()
        )
        if not apply_obj:
            return None

        has_offsets = bool(items)
        total_offset = 0.0
        first_advance_id = None

        for item in items:
            advance_id = item["advance_id"]
            offset_amount = float(item["offset_amount"])
            if first_advance_id is None:
                first_advance_id = advance_id
            total_offset += offset_amount
            offset_record = ReimburseOffset(
                reimburse_id=apply_id,
                advance_id=advance_id,
                offset_amount=offset_amount,
                remark=remark or None,
                create_by=(user.real_name or user.username),
            )
            db.add(offset_record)

        if first_advance_id is not None:
            apply_obj.offset_advance_id = first_advance_id
            apply_obj.offset_amount = total_offset

        if pay_amount > 0:
            apply_obj.pay_status = "paid"
            apply_obj.pay_time = datetime.now()
            apply_obj.pay_user_id = user.id
            apply_obj.pay_user_name = user.real_name or user.username
            apply_obj.actual_amount = pay_amount
            apply_obj.status = "processed"
        elif has_offsets:
            apply_obj.actual_amount = 0
            apply_obj.status = "processed"

        apply_obj.update_by = str(user.id)
        db.flush()
        return apply_obj

    @staticmethod
    def get_approvals(db: Session, apply_id: int) -> list[dict]:
        apply_obj = (
            db.query(ReimburseApply)
            .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
            .first()
        )
        if not apply_obj or not apply_obj.wf_id:
            return []

        wf = (
            db.query(WorkflowDef)
            .filter(WorkflowDef.id == apply_obj.wf_id, WorkflowDef.del_flag == 0)
            .first()
        )
        if not wf:
            return []

        nodes = (
            db.query(WorkflowNode)
            .filter(WorkflowNode.wf_id == wf.id)
            .order_by(WorkflowNode.node_order)
            .all()
        )
        if not nodes:
            return []

        approvals = (
            db.query(ReimburseApproval)
            .filter(ReimburseApproval.apply_id == apply_id)
            .all()
        )
        approval_map = {a.node_order: a for a in approvals}

        result = []
        for node in nodes:
            approval = approval_map.get(node.node_order)
            if approval:
                result.append({
                    "id": approval.id,
                    "reimburse_id": apply_id,
                    "node_name": approval.node_name,
                    "node_order": approval.node_order,
                    "approval_status": approval.approval_status,
                    "approval_opinion": approval.approval_opinion,
                    "approver_name": approval.approver_name,
                    "approval_time": approval.approval_time,
                })
            else:
                result.append({
                    "id": 0,
                    "reimburse_id": apply_id,
                    "node_name": node.node_name,
                    "node_order": node.node_order,
                    "approval_status": "pending",
                    "approval_opinion": None,
                    "approver_name": node.approver_name or "",
                    "approval_time": None,
                })
        return result

    @staticmethod
    def create_with_details(db: Session, obj_in: dict, details: list[dict], user) -> ReimburseApply:
        from app.models.reimburse import ReimburseDetail

        total_amount = sum(d.get("amount", 0) for d in details)
        apply_date = obj_in.pop("apply_date", None)
        title = obj_in.pop("title", None) or generate_title(
            db, user.real_name or user.username, apply_date
        )
        first_detail = details[0] if details else {}

        apply_data = {
            **obj_in,
            "title": title,
            "amount": total_amount,
            "expense_id": first_detail.get("expense_id", 0),
            "expense_name": first_detail.get("expense_name", ""),
            "applicant_id": user.id,
            "applicant_name": user.real_name or user.username,
        }

        apply_obj = ReimburseApply(**apply_data)
        db.add(apply_obj)
        db.flush()

        for d in details:
            detail = ReimburseDetail(
                reimburse_id=apply_obj.id,
                expense_id=d["expense_id"],
                expense_name=d["expense_name"],
                amount=d["amount"],
                remark=d.get("remark"),
                create_by=str(user.id),
            )
            db.add(detail)

        db.flush()
        return apply_obj

    @staticmethod
    def update_with_details(db: Session, apply_id: int, obj_in: dict, details: list[dict] | None, user) -> ReimburseApply | None:
        from app.models.reimburse import ReimburseDetail

        apply_obj = (
            db.query(ReimburseApply)
            .filter(ReimburseApply.id == apply_id, ReimburseApply.del_flag == 0)
            .first()
        )
        if not apply_obj:
            return None

        for k, v in obj_in.items():
            setattr(apply_obj, k, v)

        if details is not None:
            db.query(ReimburseDetail).filter(
                ReimburseDetail.reimburse_id == apply_id
            ).update({"del_flag": 1})

            total_amount = 0
            first_expense_name = ""
            first_expense_id = 0
            for i, d in enumerate(details):
                detail = ReimburseDetail(
                    reimburse_id=apply_id,
                    expense_id=d["expense_id"],
                    expense_name=d["expense_name"],
                    amount=d["amount"],
                    remark=d.get("remark"),
                    create_by=str(user.id),
                )
                db.add(detail)
                total_amount += d["amount"]
                if i == 0:
                    first_expense_name = d["expense_name"]
                    first_expense_id = d["expense_id"]

            apply_obj.amount = total_amount
            apply_obj.expense_name = first_expense_name
            apply_obj.expense_id = first_expense_id

        apply_obj.update_by = str(user.id)
        db.flush()
        return apply_obj

    @staticmethod
    def get_details(db: Session, reimburse_id: int) -> list:
        from app.models.reimburse import ReimburseDetail

        return db.query(ReimburseDetail).filter(
            ReimburseDetail.reimburse_id == reimburse_id,
            ReimburseDetail.del_flag == 0
        ).order_by(ReimburseDetail.id).all()

    @staticmethod
    def out_rows_with_attachment_counts(db: Session, rows: list[ReimburseApply]) -> list[ReimburseApplyOut]:
        """列表/详情输出：从 bx_attachment 聚合 invoice_count / attach_count（库内冗余字段可能未维护）。"""
        if not rows:
            return []
        ids = [r.id for r in rows]
        counts = AttachmentService.count_invoice_attach_by_biz_ids(db, "reimburse_apply", ids)
        out_list: list[ReimburseApplyOut] = []
        for r in rows:
            o = ReimburseApplyOut.model_validate(r)
            inv, att = counts.get(r.id, (0, 0))
            out_list.append(o.model_copy(update={"invoice_count": inv, "attach_count": att}))
        return out_list

    @staticmethod
    def out_one_with_attachment_counts(db: Session, row: ReimburseApply) -> ReimburseApplyOut:
        return ReimburseService.out_rows_with_attachment_counts(db, [row])[0]

    @staticmethod
    def build_apply_out_with_details(db: Session, reimburse: ReimburseApply) -> ReimburseApplyOut:
        out = ReimburseService.out_one_with_attachment_counts(db, reimburse)
        detail_rows = ReimburseService.get_details(db, reimburse.id)
        out.details = [ReimburseDetailOut.model_validate(d) for d in detail_rows]
        return out

    @staticmethod
    def global_status_counts(db: Session) -> dict[str, int]:
        """全库报销单按 status 计数（del_flag=0），用于管理端汇总卡片。"""
        rows = (
            db.query(ReimburseApply.status, func.count())
            .filter(ReimburseApply.del_flag == 0)
            .group_by(ReimburseApply.status)
            .all()
        )
        return {str(s or ""): int(c) for s, c in rows}

    @staticmethod
    def my_apply_stats(db: Session, user_id: int) -> dict:
        """我的报销：全量聚合（与列表分页无关）；本月按 create_time 自然月。

        approved_count / approved_amount：审批流程已通过的单据（含待支付 approved 与已处理 processed/paid/offset），
        与前端「已通过申请」「已审批金额」一致；不等同于 status 仅为 approved 的待支付队列。
        """
        base = and_(ReimburseApply.del_flag == 0, ReimburseApply.applicant_id == user_id)
        total_count = db.query(func.count()).select_from(ReimburseApply).filter(base).scalar() or 0
        pending = db.query(func.count()).select_from(ReimburseApply).filter(base, ReimburseApply.status == "pending").scalar() or 0
        approved_count = (
            db.query(func.count())
            .select_from(ReimburseApply)
            .filter(base, ReimburseApply.status.in_(_REIMBURSE_MY_STATS_PASSED_STATUSES))
            .scalar()
            or 0
        )
        rejected_count = (
            db.query(func.count()).select_from(ReimburseApply).filter(base, ReimburseApply.status == "rejected").scalar() or 0
        )
        approved_amount = (
            db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
            .filter(base, ReimburseApply.status.in_(_REIMBURSE_MY_STATS_PASSED_STATUSES))
            .scalar()
            or 0
        )
        m0, m1 = current_month_bounds()
        month_apply_amount = (
            db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
            .filter(base, ReimburseApply.create_time >= m0, ReimburseApply.create_time < m1)
            .scalar()
            or 0
        )
        month_apply_count = (
            db.query(func.count())
            .select_from(ReimburseApply)
            .filter(base, ReimburseApply.create_time >= m0, ReimburseApply.create_time < m1)
            .scalar()
            or 0
        )
        return {
            "total_count": int(total_count),
            "pending_count": int(pending),
            "approved_count": int(approved_count),
            "rejected_count": int(rejected_count),
            "approved_amount": float(approved_amount),
            "month_apply_amount": float(month_apply_amount),
            "month_apply_count": int(month_apply_count),
        }

    @staticmethod
    def pending_approval_stats(db: Session, user) -> dict:
        """待我审批的报销：总条数 + 其中申请提交于本月的金额合计（create_time）。"""
        subquery = (
            db.query(ReimburseApply.id)
            .join(
                WorkflowNode,
                and_(
                    WorkflowNode.wf_id == ReimburseApply.wf_id,
                    WorkflowNode.node_name == ReimburseApply.current_node,
                ),
            )
            .filter(
                ReimburseApply.del_flag == 0,
                ReimburseApply.status == "pending",
                WorkflowNode.approver_id == user.id,
            )
        ).subquery()
        total = db.query(func.count()).select_from(subquery).scalar() or 0
        m0, m1 = current_month_bounds()
        month_sum = (
            db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
            .join(subquery, ReimburseApply.id == subquery.c.id)
            .filter(ReimburseApply.create_time >= m0, ReimburseApply.create_time < m1)
            .scalar()
            or 0
        )
        return {"pending_total": int(total), "month_pending_amount": float(month_sum)}

    @staticmethod
    def finance_payment_stats(db: Session) -> dict:
        """财务支付看板：待处理=approved；已打款=有 pay_time；冲抵=offset 或 无 pay_time 的 processed 且有冲抵额；本月金额=打款按 pay_time、冲抵按 update_time。"""
        base = ReimburseApply.del_flag == 0
        pending = (
            db.query(func.count()).select_from(ReimburseApply).filter(base, ReimburseApply.status == "approved").scalar() or 0
        )
        bank_paid = and_(ReimburseApply.pay_time.isnot(None))
        paid = (
            db.query(func.count()).select_from(ReimburseApply).filter(base, bank_paid).scalar() or 0
        )
        offset_settled = or_(
            ReimburseApply.status == "offset",
            and_(
                ReimburseApply.status == "processed",
                ReimburseApply.pay_time.is_(None),
                func.coalesce(ReimburseApply.offset_amount, 0) > 0,
            ),
        )
        offset = db.query(func.count()).select_from(ReimburseApply).filter(base, offset_settled).scalar() or 0
        m0, m1 = current_month_bounds()
        month_paid = (
            db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
            .filter(
                base,
                bank_paid,
                ReimburseApply.pay_time >= m0,
                ReimburseApply.pay_time < m1,
            )
            .scalar()
            or 0
        )
        month_offset = (
            db.query(func.coalesce(func.sum(ReimburseApply.amount), 0))
            .filter(base, offset_settled, ReimburseApply.update_time >= m0, ReimburseApply.update_time < m1)
            .scalar()
            or 0
        )
        month_amount = float(month_paid) + float(month_offset)
        return {
            "pending_count": int(pending),
            "paid_count": int(paid),
            "offset_count": int(offset),
            "month_processed_amount": month_amount,
        }