-- =============================================
|
-- MES 托盘码自动生成（按有效袋码累计阈值）迁移脚本
|
-- 1. mes_pro_batch 增加包装袋型 bag_type（小袋 10 / 大袋 20）
|
-- 2. mes_pro_pallet 增加 auto_generated 自动生成标识
|
-- 3. 增加包装袋型字典
|
-- MySQL 8.4 适用，不包含 tenant_id
|
-- =============================================
|
|
SET NAMES utf8mb4;
|
|
-- ----------------------------
|
-- 1. 生产批次增加包装袋型
|
-- ----------------------------
|
SET @batch_bag_type_exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_batch' AND column_name = 'bag_type');
|
SET @batch_bag_type_sql = IF(@batch_bag_type_exists = 0,
|
'ALTER TABLE mes_pro_batch ADD COLUMN bag_type TINYINT DEFAULT NULL COMMENT ''包装袋型（字典 mes_pro_bag_type：10 小袋 20 大袋），用于自动归集托盘'' AFTER item_id',
|
'SELECT 1');
|
PREPARE batch_bag_type_stmt FROM @batch_bag_type_sql;
|
EXECUTE batch_bag_type_stmt;
|
DEALLOCATE PREPARE batch_bag_type_stmt;
|
|
-- ----------------------------
|
-- 2. 托盘码表增加自动生成标识
|
-- ----------------------------
|
SET @pallet_auto_generated_exists = (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_pallet' AND column_name = 'auto_generated');
|
SET @pallet_auto_generated_sql = IF(@pallet_auto_generated_exists = 0,
|
'ALTER TABLE mes_pro_pallet ADD COLUMN auto_generated BIT(1) NOT NULL DEFAULT b''0'' COMMENT ''是否自动生成（按有效袋码累计阈值生成，容量固定为阈值）'' AFTER bag_count',
|
'SELECT 1');
|
PREPARE pallet_auto_generated_stmt FROM @pallet_auto_generated_sql;
|
EXECUTE pallet_auto_generated_stmt;
|
DEALLOCATE PREPARE pallet_auto_generated_stmt;
|
|
-- ----------------------------
|
-- 3. 包装袋型字典
|
-- ----------------------------
|
INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
|
SELECT 'MES 包装袋型', 'mes_pro_bag_type', 0, '托盘自动归集阈值判定', '1', NOW(), '1', NOW(), b'0' FROM DUAL
|
WHERE NOT EXISTS (SELECT 1 FROM (SELECT id FROM system_dict_type WHERE `type` = 'mes_pro_bag_type' AND deleted = 0) t);
|
|
INSERT INTO `system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
|
(1065364, 1, '小袋', '10', 'mes_pro_bag_type', 0, 'primary', '', '1', NOW(), '1', NOW(), b'0'),
|
(1065365, 2, '大袋', '20', 'mes_pro_bag_type', 0, 'warning', '', '1', NOW(), '1', NOW(), b'0');
|
