-- 一袋一码：破损袋码替换迁移
|
-- 说明：旧袋码保留并标记已作废，新袋码与旧袋码通过替换记录关联。
|
|
SET @cancel_time_exists = (
|
SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_bag_code' AND column_name = 'cancel_time'
|
);
|
SET @sql = IF(@cancel_time_exists = 0,
|
'ALTER TABLE mes_pro_bag_code ADD COLUMN cancel_time DATETIME DEFAULT NULL COMMENT ''作废时间'' AFTER print_time',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @cancel_reason_exists = (
|
SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_bag_code' AND column_name = 'cancel_reason'
|
);
|
SET @sql = IF(@cancel_reason_exists = 0,
|
'ALTER TABLE mes_pro_bag_code ADD COLUMN cancel_reason VARCHAR(255) DEFAULT NULL COMMENT ''作废原因'' AFTER cancel_time',
|
'SELECT 1');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
CREATE TABLE IF NOT EXISTS `mes_pro_bag_code_replace` (
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`replace_uuid` VARCHAR(32) NOT NULL COMMENT '替换组 UUID',
|
`batch_id` BIGINT NOT NULL COMMENT '生产批次编号',
|
`batch_code` VARCHAR(64) DEFAULT NULL COMMENT '生产批次号',
|
`old_bag_code_id` BIGINT NOT NULL COMMENT '旧袋码编号',
|
`old_code` VARCHAR(64) NOT NULL COMMENT '旧袋码',
|
`old_bag_no` INT NOT NULL COMMENT '旧袋序号',
|
`old_status` TINYINT NOT NULL COMMENT '旧袋码作废前状态',
|
`old_pallet_id` BIGINT DEFAULT NULL COMMENT '旧袋码原托盘编号',
|
`old_pallet_code` VARCHAR(64) DEFAULT NULL COMMENT '旧袋码原托盘码',
|
`new_bag_code_id` BIGINT NOT NULL COMMENT '新袋码编号',
|
`new_code` VARCHAR(64) NOT NULL COMMENT '新袋码',
|
`new_bag_no` INT NOT NULL COMMENT '新袋序号',
|
`reason` VARCHAR(255) NOT NULL COMMENT '替换原因',
|
`replace_time` DATETIME NOT 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(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uk_old_bag_code_id` (`old_bag_code_id`),
|
UNIQUE KEY `uk_new_bag_code_id` (`new_bag_code_id`),
|
KEY `idx_replace_uuid` (`replace_uuid`),
|
KEY `idx_batch_id` (`batch_id`),
|
KEY `idx_old_code` (`old_code`),
|
KEY `idx_new_code` (`new_code`)
|
) ENGINE = InnoDB COMMENT = 'MES 袋码替换记录';
|
|
INSERT INTO `system_menu` (`id`, `name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
|
SELECT 602629, '袋码破损替换', 'mes:pro-bag-code:replace', 3, 6, 602618, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
|
FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM `system_menu` WHERE `id` = 602629 OR (`permission` = 'mes:pro-bag-code:replace' AND `deleted` = b'0'));
|
