-- 工艺路线工序产出：按产品归属（product_id 为空表示多产品通用）
|
CREATE TABLE IF NOT EXISTS `mes_pro_route_process_output` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`route_process_id` bigint NOT NULL COMMENT '工艺路线工序编号',
|
`route_id` bigint NOT NULL COMMENT '工艺路线编号',
|
`process_id` bigint NOT NULL COMMENT '工序编号',
|
`product_id` bigint DEFAULT NULL COMMENT '归属产品编号，为空表示多产品通用',
|
`item_id` bigint NOT NULL COMMENT '产出物料编号',
|
`sort` int DEFAULT NULL COMMENT '排序',
|
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`) USING BTREE,
|
KEY `idx_route_process_id` (`route_process_id`) USING BTREE,
|
KEY `idx_route_id` (`route_id`) USING BTREE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='MES 工艺路线工序产出归属';
|
|
-- 存量迁移：拆分 output_item_id。
|
-- 规则：产出物料是当前路线绑定的产品 → 归属到自身（产品专属）；否则 → 通用（NULL）。
|
INSERT INTO `mes_pro_route_process_output`
|
(`route_process_id`, `route_id`, `process_id`, `product_id`, `item_id`, `sort`)
|
WITH RECURSIVE seq AS (
|
SELECT 1 AS n
|
UNION ALL
|
SELECT n + 1 FROM seq WHERE n < 100
|
)
|
SELECT p.`id`,
|
p.`route_id`,
|
p.`process_id`,
|
rp.`item_id` AS product_id,
|
CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.`output_item_id`, ',', seq.n), ',', -1)) AS UNSIGNED) AS item_id,
|
seq.n
|
FROM `mes_pro_route_process` p
|
JOIN seq
|
ON seq.n <= 1 + LENGTH(p.`output_item_id`) - LENGTH(REPLACE(p.`output_item_id`, ',', ''))
|
LEFT JOIN `mes_pro_route_product` rp
|
ON rp.`route_id` = p.`route_id`
|
AND rp.`deleted` = 0
|
AND rp.`item_id` = CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.`output_item_id`, ',', seq.n), ',', -1)) AS UNSIGNED)
|
WHERE p.`deleted` = 0
|
AND p.`output_item_id` IS NOT NULL
|
AND p.`output_item_id` <> ''
|
AND TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.`output_item_id`, ',', seq.n), ',', -1)) <> ''
|
AND NOT EXISTS (
|
SELECT 1 FROM `mes_pro_route_process_output` o
|
WHERE o.`route_process_id` = p.`id`
|
AND o.`item_id` = CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.`output_item_id`, ',', seq.n), ',', -1)) AS UNSIGNED)
|
);
|
