-- =====================================================================
|
-- 种子质检「袋码 / 托盘码 / 批次码」三种扫码类型 —— 数据库升级总脚本
|
--
|
-- 用途：把「种子质检三类型 + 生产线扫码入库三类型」所需的库结构一次性对齐。
|
-- 全部 DDL 带存在性判断，可对同一环境重复执行，已存在的列/索引/表/
|
-- 菜单/仓库数据会跳过，不再报 1060 Duplicate column name。
|
--
|
-- 用法：必须先选择数据库（use <库名>）再整份执行：
|
-- mysql -u<user> -p <库名> < mes_seed_quality_multitype_upgrade.sql
|
-- 或在客户端里先 use <库名>。
|
-- 若未选库，第 0 步会明确报出，且所有 DDL 一律跳过（不会误改结构）。
|
--
|
-- 前置表（缺失说明环境过旧，需先跑对应基础脚本）：
|
-- mes_pro_bag_code / mes_pro_pallet / mes_pro_batch / mes_pro_scan_event
|
-- mes_wm_warehouse / mes_wm_warehouse_location / mes_wm_warehouse_area
|
-- mes_qc_seed_quality / system_menu
|
-- 其中 mes_pro_scan_event 由 sql/mes_scan_event_inbound.sql 创建
|
--
|
-- 本脚本不含任何 DELETE，也不做数据搬迁，可安全在生产库执行。
|
-- 提醒：mes_wm_qualified_warehouse.sql 里的「虚拟仓库合格库存搬迁」是破坏性
|
-- 数据迁移（先合并再删源记录），不在本脚本内，需要时单独评估后执行。
|
-- 本脚本不使用多租户，无 tenant_id 字段。
|
-- =====================================================================
|
|
-- ---------------------------------------------------------------------
|
-- 0. 目标库确认（未选库时所有 DDL 会被跳过，只报提示）
|
-- ---------------------------------------------------------------------
|
SET @db = DATABASE();
|
SELECT IF(@db IS NULL OR @db = '',
|
'未选择数据库：请先 use <库名> 再执行本脚本，本次未做任何结构变更',
|
CONCAT('目标库：', @db)) AS db_check;
|
|
-- ---------------------------------------------------------------------
|
-- 1. 每袋质检状态：质检按袋粒度流转、销售出库校验的依据
|
-- ---------------------------------------------------------------------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'qc_status');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_bag_code` ADD COLUMN `qc_status` TINYINT NOT NULL DEFAULT 10 COMMENT ''质检状态（10未质检 20质检合格 30质检不合格）'' AFTER `status`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 存量回填：按已有种子质检记录同步袋码质检状态（幂等，仅改不一致的行）
|
UPDATE `mes_pro_bag_code` b
|
JOIN `mes_qc_seed_quality` q ON b.code = q.code AND b.deleted = 0
|
SET b.qc_status = IF(q.result = 1, 20, 30), b.update_time = NOW()
|
WHERE b.qc_status <> IF(q.result = 1, 20, 30);
|
|
-- ---------------------------------------------------------------------
|
-- 2. 单袋入库标记：resolve 的 inbound 判断、submit-group 的
|
-- 「有效袋码必须全部入库」校验都依赖该列
|
-- ---------------------------------------------------------------------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'inbound_event_id');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_bag_code` ADD COLUMN `inbound_event_id` BIGINT NULL COMMENT ''入库事件ID''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------------------------------
|
-- 3. 三个内部业务码：托盘码 / 批次码识别时与追溯码锁定同一对象，防止重复质检
|
-- ---------------------------------------------------------------------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'business_code');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_bag_code` ADD COLUMN `business_code` VARCHAR(16) DEFAULT NULL COMMENT ''内部出入库业务码（16位数字）'' AFTER `code`',
|
'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_pro_pallet' AND column_name = 'business_code');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_pallet` ADD COLUMN `business_code` VARCHAR(16) DEFAULT NULL COMMENT ''内部出入库业务码（16位数字）'' AFTER `code`',
|
'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_pro_batch' AND column_name = 'business_code');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_batch` ADD COLUMN `business_code` VARCHAR(16) DEFAULT NULL COMMENT ''内部出入库业务码（16位数字）'' AFTER `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_pro_bag_code' AND index_name = 'uk_business_code');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_bag_code` ADD UNIQUE KEY `uk_business_code` (`business_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_pro_pallet' AND index_name = 'uk_business_code');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_pallet` ADD UNIQUE KEY `uk_business_code` (`business_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_pro_batch' AND index_name = 'uk_business_code');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_batch` ADD UNIQUE KEY `uk_business_code` (`business_code`)', '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_pro_scan_event' AND column_name = 'scan_type');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_scan_event` ADD COLUMN `scan_type` VARCHAR(16) NULL COMMENT ''扫码类型（BAG袋码/PALLET托盘码/BATCH批次码）'' AFTER `bag_code`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 设备编码改为非必填（前端已去掉该录入项，历史列保持兼容）。
|
-- MODIFY 本身可重复执行；表不存在时会报错，属预期（说明未跑基础脚本）。
|
ALTER TABLE `mes_pro_scan_event`
|
MODIFY COLUMN `device_code` VARCHAR(64) NULL DEFAULT NULL COMMENT '设备编码（不再必填）';
|
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'inbound_event_id');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_pallet` ADD COLUMN `inbound_event_id` BIGINT NULL COMMENT ''最近入库扫码事件编号'' AFTER `bag_count`',
|
'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_pro_pallet' AND column_name = 'inbound_time');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_pro_pallet` ADD COLUMN `inbound_time` DATETIME NULL COMMENT ''最近入库时间'' AFTER `inbound_event_id`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 历史扫码事件按袋码处理
|
UPDATE `mes_pro_scan_event` SET `scan_type` = 'BAG' WHERE `scan_type` IS NULL;
|
|
-- ---------------------------------------------------------------------
|
-- 5. 仓库类型 + 合格库数据
|
-- 质检页「入库合格库」下拉只列 type=20 且未冻结的仓库，缺了无法提交质检
|
-- ---------------------------------------------------------------------
|
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_wm_warehouse' AND column_name = 'type');
|
SET @sql = IF(@db IS NOT NULL AND @exists = 0,
|
'ALTER TABLE `mes_wm_warehouse` ADD COLUMN `type` TINYINT NOT NULL DEFAULT 10 COMMENT ''仓库类型：10暂存库，20合格库'' AFTER `frozen`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 现有仓库归类：虚拟线边仓库=暂存库；产品库/物料库=合格库
|
UPDATE `mes_wm_warehouse` SET `type` = 10 WHERE `code` = 'WIP_VIRTUAL_WAREHOUSE' AND `deleted` = 0;
|
UPDATE `mes_wm_warehouse` SET `type` = 20 WHERE `code` IN ('1212', 'WHS000001') AND `deleted` = 0;
|
|
-- 新建「成品合格库」+ 库区 + 库位（幂等）
|
INSERT INTO `mes_wm_warehouse` (`code`, `name`, `frozen`, `type`, `remark`)
|
SELECT 'FG_QUALIFIED_WH', '成品合格库', 0, 20, '质检合格直接入库的目标仓库（type=20 合格库）'
|
WHERE NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse` WHERE `code` = 'FG_QUALIFIED_WH' AND `deleted` = 0);
|
SET @wh_id = (SELECT `id` FROM `mes_wm_warehouse` WHERE `code` = 'FG_QUALIFIED_WH' AND `deleted` = 0 LIMIT 1);
|
|
INSERT INTO `mes_wm_warehouse_location` (`code`, `name`, `warehouse_id`, `frozen`, `remark`)
|
SELECT 'FG_QUALIFIED_WH_LOC', '成品合格库区', @wh_id, 0, '质检合格入库默认库区'
|
WHERE NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse_location` WHERE `code` = 'FG_QUALIFIED_WH_LOC' AND `deleted` = 0);
|
SET @loc_id = (SELECT `id` FROM `mes_wm_warehouse_location` WHERE `code` = 'FG_QUALIFIED_WH_LOC' AND `deleted` = 0 LIMIT 1);
|
|
INSERT INTO `mes_wm_warehouse_area` (`code`, `name`, `location_id`, `frozen`, `remark`)
|
SELECT 'FG_QUALIFIED_WH_AREA', '成品合格库位', @loc_id, 0, '质检合格入库默认库位'
|
WHERE NOT EXISTS (SELECT 1 FROM `mes_wm_warehouse_area` WHERE `code` = 'FG_QUALIFIED_WH_AREA' AND `deleted` = 0);
|
|
-- ---------------------------------------------------------------------
|
-- 6. 按袋库存明细表：质检合格 / 不合格按袋扣暂存、入合格库
|
-- ---------------------------------------------------------------------
|
CREATE TABLE IF NOT EXISTS `mes_wm_material_stock_bag` (
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`material_stock_id` BIGINT NOT NULL COMMENT '库存汇总记录ID',
|
`bag_code_id` BIGINT NOT NULL COMMENT '生产袋码ID',
|
`bag_code` VARCHAR(64) NOT NULL COMMENT '袋码',
|
`item_id` BIGINT NOT NULL COMMENT '物料ID',
|
`batch_id` BIGINT NULL COMMENT '仓库批次ID',
|
`batch_code` VARCHAR(64) NULL COMMENT '批次号',
|
`vendor_id` BIGINT NULL COMMENT '供应商ID',
|
`warehouse_id` BIGINT NOT NULL COMMENT '仓库ID',
|
`location_id` BIGINT NULL COMMENT '库区ID',
|
`area_id` BIGINT NULL COMMENT '库位ID',
|
`stock_state` TINYINT NOT NULL COMMENT '库存状态：10暂存，20合格，30已出库，40报废',
|
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '明细状态：1在库，2已出库，3已报废',
|
`quantity` DECIMAL(18, 6) NOT NULL DEFAULT 1 COMMENT '袋码库存数量',
|
`frozen` BIT NOT NULL DEFAULT 0 COMMENT '是否冻结',
|
`last_transaction_id` BIGINT NULL COMMENT '最近库存事务ID',
|
`inbound_time` DATETIME NULL COMMENT '入库时间',
|
`outbound_time` DATETIME NULL COMMENT '出库时间',
|
`creator` VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` BIT NOT NULL DEFAULT 0 COMMENT '是否删除',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_bag_code` (`bag_code`),
|
KEY `idx_material_stock` (`material_stock_id`),
|
KEY `idx_batch_state` (`batch_id`, `stock_state`, `status`),
|
KEY `idx_location_state` (`warehouse_id`, `location_id`, `area_id`, `stock_state`, `status`),
|
KEY `idx_bag_code_id` (`bag_code_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MES库存袋码明细';
|
|
-- ---------------------------------------------------------------------
|
-- 7. 质检记录独立菜单（挂在「质量管理」5500 下）
|
-- ---------------------------------------------------------------------
|
INSERT INTO `system_menu`
|
(`id`, `name`, `parent_id`, `type`, `sort`, `status`, `path`, `icon`, `component`, `component_name`, `permission`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
|
SELECT 1065224, '质检记录', 5500, 2, 95, 0, 'seed-quality-record', 'ep:list', 'mes/qc/seedQuality/record', 'MesQcSeedQualityRecord', '', b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 1065224 AND `deleted` = b'0');
|
|
-- ---------------------------------------------------------------------
|
-- 8. 执行后自检：每项 present=1 为就绪；最后一项应 >= 1
|
-- 若全部为 0，多半是第 0 步报的「未选择数据库」
|
-- ---------------------------------------------------------------------
|
SELECT 'mes_pro_bag_code.qc_status' AS item, COUNT(*) AS present FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'qc_status'
|
UNION ALL SELECT 'mes_pro_bag_code.inbound_event_id', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'inbound_event_id'
|
UNION ALL SELECT 'mes_pro_bag_code.business_code', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_bag_code' AND column_name = 'business_code'
|
UNION ALL SELECT 'mes_pro_pallet.business_code', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'business_code'
|
UNION ALL SELECT 'mes_pro_batch.business_code', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_batch' AND column_name = 'business_code'
|
UNION ALL SELECT 'mes_pro_scan_event.scan_type', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_scan_event' AND column_name = 'scan_type'
|
UNION ALL SELECT 'mes_pro_pallet.inbound_event_id', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'inbound_event_id'
|
UNION ALL SELECT 'mes_pro_pallet.inbound_time', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_pro_pallet' AND column_name = 'inbound_time'
|
UNION ALL SELECT 'mes_wm_warehouse.type', COUNT(*) FROM information_schema.columns
|
WHERE table_schema = @db AND table_name = 'mes_wm_warehouse' AND column_name = 'type'
|
UNION ALL SELECT 'TABLE mes_wm_material_stock_bag', COUNT(*) FROM information_schema.tables
|
WHERE table_schema = @db AND table_name = 'mes_wm_material_stock_bag'
|
UNION ALL SELECT 'MENU 质检记录 1065224', COUNT(*) FROM `system_menu` WHERE `id` = 1065224 AND `deleted` = b'0'
|
UNION ALL SELECT 'DATA type=20 未冻结仓库', COUNT(*) FROM `mes_wm_warehouse` WHERE `type` = 20 AND `frozen` = 0 AND `deleted` = 0;
|
