78 lines
3.7 KiB
Python
78 lines
3.7 KiB
Python
"""客户评级模型（需求 2.2.5）。
|
||
|
||
评级从「纯计算器不落库」升级为**每客户一行落库**（2026-10-19 确认）：
|
||
客户管理页要罗列评级列并按其排序，评分必须可回溯。设计要点：
|
||
|
||
- ``customer_id`` 唯一键 + RESTRICT 外键：一客户一份当前评级（快照语义，
|
||
非流水），删除客户由服务层先删评级行再删主行（RESTRICT 兜底并发窗口）
|
||
- ``grade_name`` 为**快照固化、无外键**：等级映射表仍允许自由增删清空
|
||
（rating_service 的既有前提），删等级不能伤及已评级客户的历史结论 ——
|
||
与归属留痕「ID/人名固化、不回溯」同一论证口径；等级未命中时为 NULL
|
||
- ``scorer_id`` 外键 SET NULL + ``scorer_name`` 冗余固化：账号删除后
|
||
记录仍可读（同 ownership_change_logs 的操作人口径）
|
||
- 分值列 DECIMAL(5,1)：UI 整数 0-20，DECIMAL 留十进制余地；总分由
|
||
**服务端重算**，不采信前端回传
|
||
"""
|
||
|
||
from decimal import Decimal
|
||
|
||
from sqlalchemy import ForeignKey, Numeric, String, UniqueConstraint
|
||
from sqlalchemy.orm import Mapped, mapped_column
|
||
|
||
from app.models.base import BIGINT_UNSIGNED, Base, TimestampMixin, mysql_table_args
|
||
|
||
|
||
class CustomerRating(Base, TimestampMixin):
|
||
"""客户评级表：每客户一行（uk_customer_ratings_customer_id）。
|
||
|
||
五项各 0-20 分、总分 = 五项之和（0-100），判定规则同等级映射表
|
||
（按 sort_order 升序首个 min_score ≤ 总分，见 rating_service.resolve_grade）。
|
||
"""
|
||
|
||
__tablename__ = "customer_ratings"
|
||
__table_args__ = mysql_table_args(
|
||
"客户评级表（每客户一行；等级名为快照固化无外键，删等级不伤已评级记录）",
|
||
UniqueConstraint("customer_id", name="uk_customer_ratings_customer_id"),
|
||
)
|
||
|
||
id: Mapped[int] = mapped_column(
|
||
BIGINT_UNSIGNED, primary_key=True, autoincrement=True, comment="主键ID"
|
||
)
|
||
customer_id: Mapped[int] = mapped_column(
|
||
BIGINT_UNSIGNED,
|
||
ForeignKey("customers.id", ondelete="RESTRICT"),
|
||
nullable=False,
|
||
comment="客户ID（唯一：一客户一份当前评级）",
|
||
)
|
||
score_ship_amount: Mapped[Decimal] = mapped_column(
|
||
Numeric(5, 1), nullable=False, server_default="0", comment="发货额评分（0-20）"
|
||
)
|
||
score_receipt_rate: Mapped[Decimal] = mapped_column(
|
||
Numeric(5, 1), nullable=False, server_default="0", comment="回款率评分（0-20）"
|
||
)
|
||
score_profit_rate: Mapped[Decimal] = mapped_column(
|
||
Numeric(5, 1), nullable=False, server_default="0", comment="毛利率评分（0-20）"
|
||
)
|
||
score_contract_cooperation: Mapped[Decimal] = mapped_column(
|
||
Numeric(5, 1), nullable=False, server_default="0", comment="合同履约配合能力评分（0-20）"
|
||
)
|
||
score_site_construction: Mapped[Decimal] = mapped_column(
|
||
Numeric(5, 1), nullable=False, server_default="0", comment="现场规范施工能力评分（0-20）"
|
||
)
|
||
total_score: Mapped[Decimal] = mapped_column(
|
||
Numeric(5, 1), nullable=False, comment="总分（服务端重算，五项之和 0-100）"
|
||
)
|
||
grade_name: Mapped[str | None] = mapped_column(
|
||
String(32),
|
||
comment="评级等级（保存时按等级映射快照固化，无外键；未命中/等级表空为 NULL）",
|
||
)
|
||
remark: Mapped[str | None] = mapped_column(String(255), comment="备注")
|
||
scorer_id: Mapped[int | None] = mapped_column(
|
||
BIGINT_UNSIGNED,
|
||
ForeignKey("users.id", ondelete="SET NULL"),
|
||
comment="评级操作人ID（账号删除后置空）",
|
||
)
|
||
scorer_name: Mapped[str] = mapped_column(
|
||
String(64), nullable=False, comment="评级操作人用户名（冗余固化）"
|
||
)
|