-- ===================================================================== -- 种子质检「加工部 / 销售部」双线检验 + BPM 审批 + 经销商退货复检 -- -- 变更内容: -- 1. 一条袋码的初检同时生成加工部、销售部两条质检记录(qc_line) -- 2. 两条线各自走部门领导审批,互不影响 -- 3. 两条线都进入复检审核后,系统自动发起公司质检审批 -- 4. 经销商退货复检复用同一张表(biz_type=2),单独走销售部线 -- -- 本脚本全部 DDL 带存在性判断,可对同一环境重复执行。 -- 不使用多租户,无 tenant_id 字段。 -- 执行前请先 use <目标库>。 -- ===================================================================== SET @db = DATABASE(); -- --------------------------------------------------------------------- -- 1. 质检线:1 加工部 2 销售部 0 未区分(历史数据) -- --------------------------------------------------------------------- SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'qc_line'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD COLUMN `qc_line` TINYINT NOT NULL DEFAULT 0 COMMENT ''质检线:1加工部 2销售部 0未区分(历史数据)'' AFTER `code`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- --------------------------------------------------------------------- -- 2. 业务类型:1 生产入库质检 2 经销商退货质检 -- --------------------------------------------------------------------- SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'biz_type'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD COLUMN `biz_type` TINYINT NOT NULL DEFAULT 1 COMMENT ''业务类型:1生产入库质检 2经销商退货质检'' AFTER `qc_line`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- --------------------------------------------------------------------- -- 3. BPM 流程实例与时间节点 -- --------------------------------------------------------------------- SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'process_instance_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD COLUMN `process_instance_id` VARCHAR(64) DEFAULT NULL COMMENT ''本线部门审批流程实例编号'' AFTER `workflow_status`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'company_process_instance_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD COLUMN `company_process_instance_id` VARCHAR(64) DEFAULT NULL COMMENT ''公司质检审批流程实例编号'' AFTER `process_instance_id`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'line_finished_at'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD COLUMN `line_finished_at` DATETIME DEFAULT NULL COMMENT ''本线部门审批完成时间'' AFTER `process_approved_at`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'company_submitted_at'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD COLUMN `company_submitted_at` DATETIME DEFAULT NULL COMMENT ''公司质检审批发起时间'' AFTER `line_finished_at`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- --------------------------------------------------------------------- -- 4. 经销商退货关联 -- --------------------------------------------------------------------- SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'return_sales_id'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD COLUMN `return_sales_id` BIGINT DEFAULT NULL COMMENT ''经销商退货单编号'' AFTER `qualified_warehouse_id`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'return_sales_code'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD COLUMN `return_sales_code` VARCHAR(64) DEFAULT NULL COMMENT ''经销商退货单号'' AFTER `return_sales_id`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- --------------------------------------------------------------------- -- 5. 唯一键由 (code) 调整为 (code, qc_line),同袋码允许两条质检线记录 -- 先删除旧唯一键,再创建新唯一键,均带存在性判断 -- --------------------------------------------------------------------- SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND index_name = 'uk_code'); SET @sql = IF(@exists > 0, 'ALTER TABLE `mes_qc_seed_quality` DROP INDEX `uk_code`', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND index_name = 'uk_code_line'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD UNIQUE KEY `uk_code_line` (`code`, `qc_line`)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- --------------------------------------------------------------------- -- 6. 辅助索引:流程实例回查、业务类型筛选 -- --------------------------------------------------------------------- SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND index_name = 'idx_process_instance'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD INDEX `idx_process_instance` (`process_instance_id`)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND index_name = 'idx_company_process_instance'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD INDEX `idx_company_process_instance` (`company_process_instance_id`)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND index_name = 'idx_biz_type'); SET @sql = IF(@exists = 0, 'ALTER TABLE `mes_qc_seed_quality` ADD INDEX `idx_biz_type` (`biz_type`, `workflow_status`)', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 历史记录统一归为生产入库质检,质检线保持 0(未区分),避免重复审批 UPDATE `mes_qc_seed_quality` SET `biz_type` = 1 WHERE `biz_type` IS NULL OR `biz_type` = 0; -- --------------------------------------------------------------------- -- 7. 执行后自检:present=1 表示字段/索引已就绪 -- --------------------------------------------------------------------- SELECT 'mes_qc_seed_quality.qc_line' AS item, COUNT(*) AS present FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'qc_line' UNION ALL SELECT 'mes_qc_seed_quality.biz_type', COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'biz_type' UNION ALL SELECT 'mes_qc_seed_quality.process_instance_id', COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'process_instance_id' UNION ALL SELECT 'mes_qc_seed_quality.company_process_instance_id', COUNT(*) FROM information_schema.columns WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND column_name = 'company_process_instance_id' UNION ALL SELECT 'INDEX uk_code_line', COUNT(*) FROM information_schema.statistics WHERE table_schema = @db AND table_name = 'mes_qc_seed_quality' AND index_name = 'uk_code_line';