-- MES 质检升级：化工检验报告能力（指标分组树 + 公式文本 + 平行测量 + 报告表头字段）
|
-- 适用：MySQL 8.x；不含 tenant_id；重复执行会报"字段已存在"，需先核对 information_schema
|
|
-- 1. 指标字典：层级分组 + 条目类型 + 公式/单位文本 + 排序
|
ALTER TABLE mes_qc_indicator
|
ADD COLUMN parent_id BIGINT NOT NULL DEFAULT 0 COMMENT '父指标编号（0=顶级）' AFTER name,
|
ADD COLUMN item_type TINYINT NOT NULL DEFAULT 1 COMMENT '条目类型：1=录入项，2=分组项' AFTER parent_id,
|
ADD COLUMN formula_text VARCHAR(500) NULL COMMENT '公式展示文本（打印报告用，不参与计算）' AFTER item_type,
|
ADD COLUMN unit_text VARCHAR(50) NULL COMMENT '单位文本（如 kg/m³、/%，打印展示用）' AFTER formula_text,
|
ADD COLUMN sort_order INT NOT NULL DEFAULT 0 COMMENT '同级排序号' AFTER unit_text;
|
CREATE INDEX idx_qc_indicator_parent_id ON mes_qc_indicator (parent_id);
|
|
-- 2. 方案指标：排序 + 是否参与判定（分组/中间参数行=0）
|
ALTER TABLE mes_qc_template_indicator
|
ADD COLUMN sort_order INT NOT NULL DEFAULT 0 COMMENT '展示排序号' AFTER indicator_id,
|
ADD COLUMN judge_flag TINYINT NOT NULL DEFAULT 1 COMMENT '是否参与合格判定：1=是，0=否' AFTER sort_order;
|
|
-- 3. 检验结果明细：平行测量批次号
|
ALTER TABLE mes_qc_indicator_result_detail
|
ADD COLUMN measure_no INT NOT NULL DEFAULT 1 COMMENT '测量批次号（从 1 开始，默认 1）' AFTER indicator_id;
|
CREATE INDEX idx_qc_result_detail_measure ON mes_qc_indicator_result_detail (result_id, indicator_id, measure_no);
|
|
-- 4. 检验方案：默认执行标准（建单时带入主表）
|
ALTER TABLE mes_qc_template
|
ADD COLUMN default_execute_standard VARCHAR(50) NULL COMMENT '默认执行标准（如 GB/T6027）' AFTER types;
|
|
-- 5. 四类检验单主表：报告表头 + 复核/审核签字字段
|
ALTER TABLE mes_qc_iqc
|
ADD COLUMN sampling_location VARCHAR(100) NULL COMMENT '采样地点' AFTER status,
|
ADD COLUMN certificate_no VARCHAR(50) NULL COMMENT '证书编号/合格证号' AFTER sampling_location,
|
ADD COLUMN execute_standard VARCHAR(50) NULL COMMENT '执行标准' AFTER certificate_no,
|
ADD COLUMN audit_user_id BIGINT NULL COMMENT '复核人用户编号' AFTER execute_standard,
|
ADD COLUMN approve_user_id BIGINT NULL COMMENT '审核人用户编号' AFTER audit_user_id;
|
|
ALTER TABLE mes_qc_ipqc
|
ADD COLUMN sampling_location VARCHAR(100) NULL COMMENT '采样地点' AFTER status,
|
ADD COLUMN certificate_no VARCHAR(50) NULL COMMENT '证书编号/合格证号' AFTER sampling_location,
|
ADD COLUMN execute_standard VARCHAR(50) NULL COMMENT '执行标准' AFTER certificate_no,
|
ADD COLUMN audit_user_id BIGINT NULL COMMENT '复核人用户编号' AFTER execute_standard,
|
ADD COLUMN approve_user_id BIGINT NULL COMMENT '审核人用户编号' AFTER audit_user_id;
|
|
ALTER TABLE mes_qc_oqc
|
ADD COLUMN sampling_location VARCHAR(100) NULL COMMENT '采样地点' AFTER status,
|
ADD COLUMN certificate_no VARCHAR(50) NULL COMMENT '证书编号/合格证号' AFTER sampling_location,
|
ADD COLUMN execute_standard VARCHAR(50) NULL COMMENT '执行标准' AFTER certificate_no,
|
ADD COLUMN audit_user_id BIGINT NULL COMMENT '复核人用户编号' AFTER execute_standard,
|
ADD COLUMN approve_user_id BIGINT NULL COMMENT '审核人用户编号' AFTER audit_user_id;
|
|
ALTER TABLE mes_qc_rqc
|
ADD COLUMN sampling_location VARCHAR(100) NULL COMMENT '采样地点' AFTER status,
|
ADD COLUMN certificate_no VARCHAR(50) NULL COMMENT '证书编号/合格证号' AFTER sampling_location,
|
ADD COLUMN execute_standard VARCHAR(50) NULL COMMENT '执行标准' AFTER certificate_no,
|
ADD COLUMN audit_user_id BIGINT NULL COMMENT '复核人用户编号' AFTER execute_standard,
|
ADD COLUMN approve_user_id BIGINT NULL COMMENT '审核人用户编号' AFTER audit_user_id;
|
