1c93ea0a4b
- 审批：删 _flush_order 撞键内部重试（rollback 会回掉同事务业务行造成 静默丢数据，异常改由 create 外层重试整体重来/update 路径审计收敛）； act() 出参固化前置到 commit 之前（commit 后查询失败会把已通过的审批 记成失败审计）；驳回/撤回清空方案生效行（防被否决方案进 M3 快照）； keyword 改 contains(autoescape=True) 对齐全库转义口径；撤单抽 withdraw_pending_orders 供编辑清空方案时显式调用 - 业务：删业务遇在途审批单禁删 400、终态审批单随 _hard_delete 清理； 编辑清空方案同步撤回在途单；历史离职参与人编辑放行（冗余姓名固化 语义，仅新增参与人做在职校验） - 迁移：business_secondary_allocations 补 uk(business_id, personnel_id) （模型/迁移 py/导出 SQL/init-dev 四方同步，开发库往返重放验证） - 设置：upload_logo 改同步 def（阻塞操作不卡事件循环）+ file.size 先拦； company_name schema 剥空白防全空白入库；logo 旧文件清理移到 commit 成功后（防库指旧路径而文件已删断链）；uploadLogo 走 request() 解包 （原直发 service.post 不解包信封，logoUrl 会被置 undefined） - 备份：restore_drill --drop-exec 先清全部历史 drill 库再演练并即删本轮 （原语义永远删错对象，每轮净增一份整库拷贝） - 测试：conftest 清理补 system_settings 种子行与 uploads/logo 文件； 新增 5 例（驳回清方案/清空撤单/在途禁删终态随删/离职放行/BOOL 边界）； 全量回归 701 passed - 遗留 2 项记 AI_CONTEXT（方案版本承载、审批单快照，依赖 ④ 分摊语义） Co-Authored-By: Claude Code <noreply@anthropic.com>
82 lines
7.0 KiB
SQL
82 lines
7.0 KiB
SQL
-- Running upgrade 8efc189b131f -> 79bcc5aa98d4
|
||
|
||
CREATE TABLE system_settings (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
company_name VARCHAR(64) NOT NULL COMMENT '公司名称（登录页与导出页眉展示）',
|
||
logo_path VARCHAR(255) COMMENT 'logo 相对路径（文件存 uploads/logo/，null=未上传使用默认）',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_system_settings PRIMARY KEY (id)
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='系统设置表（公司信息，全系统单行）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE TABLE approval_orders (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
order_no VARCHAR(32) NOT NULL COMMENT '审批单号，自动生成 AP{YYYYMMDD}-{4位当日序号}',
|
||
order_type TINYINT NOT NULL COMMENT '审批对象类型：1二次分配方案 2月度结算（预留）',
|
||
ref_id BIGINT UNSIGNED NOT NULL COMMENT '引用对象ID（order_type=1 时为 businesses.id；固化无外键，删除保护由服务层承担）',
|
||
refer_no VARCHAR(32) NOT NULL COMMENT '引用对象单号（冗余固化，列表页免联查）',
|
||
status TINYINT NOT NULL COMMENT '审批状态：1待财务复核 2待领导审核 3已通过 4已驳回 5已撤回' DEFAULT '1',
|
||
submitter_id BIGINT UNSIGNED COMMENT '提交人ID（账号删除后置空）',
|
||
submitter_name VARCHAR(64) NOT NULL COMMENT '提交人用户名（冗余固化）',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_approval_orders PRIMARY KEY (id),
|
||
CONSTRAINT fk_approval_orders_submitter_id FOREIGN KEY(submitter_id) REFERENCES users (id) ON DELETE SET NULL,
|
||
CONSTRAINT uk_approval_orders_order_no UNIQUE (order_no)
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='审批单表（营销后勤提交→财务复核→领导审核；开关关闭时自动通过留痕）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_approval_orders_type_status ON approval_orders (order_type, status);
|
||
|
||
CREATE TABLE approval_steps (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
order_id BIGINT UNSIGNED NOT NULL COMMENT '审批单ID（approval_orders.id；单删记录随删）',
|
||
node TINYINT NOT NULL COMMENT '审批节点：0撤回留痕 1财务复核 2领导审核',
|
||
action TINYINT NOT NULL COMMENT '动作：1通过 2驳回 3自动通过 4撤回',
|
||
opinion VARCHAR(255) COMMENT '审批意见（驳回时填写）',
|
||
operator_id BIGINT UNSIGNED COMMENT '操作人ID（自动通过/系统动作置空）',
|
||
operator_name VARCHAR(64) NOT NULL COMMENT '操作人用户名（冗余固化；系统动作记「系统」）',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_approval_steps PRIMARY KEY (id),
|
||
CONSTRAINT fk_approval_steps_operator_id FOREIGN KEY(operator_id) REFERENCES users (id) ON DELETE SET NULL,
|
||
CONSTRAINT fk_approval_steps_order_id FOREIGN KEY(order_id) REFERENCES approval_orders (id) ON DELETE CASCADE
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='审批节点记录表（append-only；自动通过/撤回均有留痕）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_approval_steps_order_id ON approval_steps (order_id);
|
||
|
||
CREATE TABLE business_secondary_allocations (
|
||
id BIGINT UNSIGNED NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
||
business_id BIGINT UNSIGNED NOT NULL COMMENT '业务ID（businesses.id；业务删除随删）',
|
||
personnel_id BIGINT UNSIGNED NOT NULL COMMENT '参与业务员ID（personnel.id；被方案引用禁删）',
|
||
personnel_name VARCHAR(64) NOT NULL COMMENT '参与业务员姓名（冗余固化，离职后回显有名）',
|
||
ratio NUMERIC(5, 2) NOT NULL COMMENT '分摊比例（0-100 两位小数；不要求合计满额，余额归属待澄清#2）',
|
||
create_time DATETIME NOT NULL COMMENT '创建时间' DEFAULT CURRENT_TIMESTAMP,
|
||
update_time DATETIME NOT NULL COMMENT '更新时间' DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
CONSTRAINT pk_business_secondary_allocations PRIMARY KEY (id),
|
||
CONSTRAINT fk_business_secondary_allocations_business_id FOREIGN KEY(business_id) REFERENCES businesses (id) ON DELETE CASCADE,
|
||
CONSTRAINT fk_business_secondary_allocations_personnel_id FOREIGN KEY(personnel_id) REFERENCES personnel (id) ON DELETE RESTRICT,
|
||
CONSTRAINT uk_business_secondary_allocations_business_personnel UNIQUE (business_id, personnel_id)
|
||
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='业务二次分配方案明细表（合同建立时录入；比例不要求合计满额，余额归属待澄清）' COLLATE utf8mb4_general_ci;
|
||
|
||
CREATE INDEX idx_business_secondary_allocations_business_id ON business_secondary_allocations (business_id);
|
||
|
||
CREATE INDEX idx_business_secondary_allocations_personnel_id ON business_secondary_allocations (personnel_id);
|
||
|
||
INSERT INTO roles (role_code, role_name, description, sort_order) VALUES ('marketing_ops', '营销后勤', '提交与录入（审批链第一节点：月结/二次分配提交）', 3);
|
||
|
||
INSERT INTO roles (role_code, role_name, description, sort_order) VALUES ('finance', '财务', '复核（审批链第二节点）', 4);
|
||
|
||
INSERT INTO roles (role_code, role_name, description, sort_order) VALUES ('leader', '领导', '审核（审批链第三节点；全览只读）', 5);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order, status) VALUES (17, 'approval.secondary_allocation.enabled', '二次分配审批开关', '审批开关', 'BOOL', NULL, 'NONE', NULL, '0', NULL, NULL, '0=二次分配方案提交即生效（系统自动通过全节点留痕，留痕不断链）；1=提交后进入财务复核→领导审核审批流（2026-10-15 复核审批补充需求）', 17, 1);
|
||
|
||
INSERT INTO param_definitions (id, param_code, param_name, param_group, value_type, unit, dimension_type, options_text, default_value, value_min, value_max, description, sort_order, status) VALUES (18, 'approval.monthly_settlement.enabled', '月度结算审批开关', '审批开关', 'BOOL', NULL, 'NONE', NULL, '0', NULL, NULL, '预留参数：月度结算审批链开关（M3 ④ 月结子块接入时消费）；0=结算生成即生效，1=走财务复核→领导审核审批流', 18, 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (20, 17, '', '2026-01-01', '0', 1);
|
||
|
||
INSERT INTO param_values (id, param_id, dimension_value, effective_date, value_text, batch_id) VALUES (21, 18, '', '2026-01-01', '0', 1);
|
||
|
||
INSERT INTO system_settings (id, company_name, logo_path) VALUES (1, '衍水集团', NULL);
|
||
|
||
UPDATE alembic_version SET version_num='79bcc5aa98d4' WHERE alembic_version.version_num = '8efc189b131f';
|
||
|