262 lines
11 KiB
SQL
262 lines
11 KiB
SQL
-- ============================================================
|
||
-- BOM单多级优化 - 数据库变更脚本
|
||
-- 版本: v2.0.016
|
||
-- 作者: 周启威
|
||
-- 日期: 2026-03-27
|
||
-- 说明: 在 md_new_bom_item 新增子BOM引用字段，支持多级BOM结构
|
||
-- ============================================================
|
||
|
||
-- --------------------------------------------------
|
||
-- 1. md_new_bom_item 表新增字段
|
||
-- - sub_bom_id : 引用的子BOM主键ID（当 is_sub_bom=1 时有效）
|
||
-- - is_sub_bom : 是否子BOM项 0-否 1-是
|
||
-- - level : BOM明细层级深度（0=顶层物料，1=子BOM展开第一层，以此类推）
|
||
-- - is_key_material: 是否关键物料（从 is_key_component 独立出来更通用）
|
||
-- --------------------------------------------------
|
||
|
||
ALTER TABLE `md_new_bom_item`
|
||
ADD COLUMN `sub_bom_id` bigint(20) DEFAULT NULL COMMENT '引用的子BOM主键ID（多级BOM用）' AFTER `process_route`,
|
||
ADD COLUMN `is_sub_bom` tinyint(1) DEFAULT '0' COMMENT '是否子BOM项 0-否 1-是' AFTER `sub_bom_id`,
|
||
ADD COLUMN `level` int(11) DEFAULT '0' COMMENT 'BOM明细层级深度（0=顶层）' AFTER `is_sub_bom`,
|
||
ADD KEY `idx_sub_bom_id` (`sub_bom_id`);
|
||
|
||
-- --------------------------------------------------
|
||
-- 2. md_new_bom 表新增字段（辅助多级展示）
|
||
-- - max_level : BOM最大层级深度（由系统计算写入，供前端展示参考）
|
||
-- - is_multilevel : 是否多级BOM 0-否 1-是
|
||
-- - component_count : 直接子件数量（含子BOM项）
|
||
-- --------------------------------------------------
|
||
|
||
ALTER TABLE `md_new_bom`
|
||
ADD COLUMN `max_level` int(11) DEFAULT '0' COMMENT 'BOM最大层级深度' AFTER `sort_order`,
|
||
ADD COLUMN `is_multilevel` tinyint(1) DEFAULT '0' COMMENT '是否多级BOM 0-否 1-是' AFTER `max_level`,
|
||
ADD COLUMN `component_count` int(11) DEFAULT '0' COMMENT '直接子件数量（含子BOM项）' AFTER `is_multilevel`;
|
||
|
||
-- --------------------------------------------------
|
||
-- 3. 新增 BOM 层级关系表 md_new_bom_level_relation
|
||
-- 用于记录任意BOM到其直接父BOM的引用关系（支持快速查询"哪些BOM用到了我"）
|
||
-- 子BOM被引用时自动写入，废弃/删除时清理
|
||
-- --------------------------------------------------
|
||
|
||
CREATE TABLE IF NOT EXISTS `md_new_bom_level_relation` (
|
||
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`parent_bom_id` bigint(20) NOT NULL COMMENT '父级BOM主键ID',
|
||
`parent_material_id` bigint(20) NOT NULL COMMENT '父级产品物料ID',
|
||
`child_bom_id` bigint(20) NOT NULL COMMENT '子级BOM主键ID',
|
||
`child_material_id` bigint(20) NOT NULL COMMENT '子级产品物料ID',
|
||
`level` int(11) NOT NULL DEFAULT '1' COMMENT '层级（1=直接子级，2=孙子级，以此类推）',
|
||
`bom_item_id` bigint(20) DEFAULT NULL COMMENT '对应明细行ID（来源 md_new_bom_item.id）',
|
||
`create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
|
||
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
|
||
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_parent_bom_id` (`parent_bom_id`),
|
||
KEY `idx_child_bom_id` (`child_bom_id`),
|
||
KEY `idx_parent_material_id` (`parent_material_id`),
|
||
KEY `idx_child_material_id` (`child_material_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='BOM多级层级关系表';
|
||
|
||
-- --------------------------------------------------
|
||
-- 4. 新增 BOM 多级展开视图 v_bom_multilevel_expand
|
||
-- 将多级BOM按层级展开为扁平视图，方便前端树形展示和报表
|
||
-- 注意：MySQL 8.0+ 递归CTE实现
|
||
-- --------------------------------------------------
|
||
|
||
DROP VIEW IF EXISTS `v_bom_multilevel_expand`;
|
||
|
||
CREATE ALGORITHM=MERGE SQL SECURITY INVOKER VIEW `v_bom_multilevel_expand` AS
|
||
SELECT
|
||
-- 层级标识（用于前端树形缩进）
|
||
CAST(REPEAT(' ', `r`.`level` - 1) AS CHAR(100)) AS `indent_space`,
|
||
`r`.`level` AS `bom_level`,
|
||
`r`.`parent_bom_id` AS `parent_bom_id`,
|
||
`r`.`child_bom_id` AS `bom_id`,
|
||
`b`.`bom_number` AS `bom_number`,
|
||
`b`.`product_code` AS `product_code`,
|
||
`b`.`product_name` AS `product_name`,
|
||
`b`.`specification` AS `specification`,
|
||
`b`.`version` AS `bom_version`,
|
||
`b`.`bom_type` AS `bom_type`,
|
||
`b`.`status` AS `bom_status`,
|
||
`b`.`base_quantity` AS `base_quantity`,
|
||
`b`.`unit_name` AS `unit_name`,
|
||
`b`.`max_level` AS `max_level`,
|
||
`b`.`is_multilevel` AS `is_multilevel`,
|
||
`r`.`bom_item_id` AS `bom_item_id`,
|
||
`r`.`parent_material_id` AS `parent_material_id`,
|
||
`r`.`child_material_id` AS `child_material_id`,
|
||
`b`.`material_cost` AS `direct_material_cost`,
|
||
`b`.`labor_cost` AS `direct_labor_cost`,
|
||
`b`.`manufacturing_cost` AS `direct_manufacturing_cost`,
|
||
`b`.`total_cost` AS `direct_total_cost`,
|
||
`b`.`create_time` AS `create_time`,
|
||
`b`.`update_time` AS `update_time`
|
||
FROM `md_new_bom_level_relation` `r`
|
||
INNER JOIN `md_new_bom` `b` ON `r`.`child_bom_id` = `b`.`id`
|
||
ORDER BY `r`.`parent_bom_id`, `r`.`level`, `b`.`product_name`;
|
||
|
||
-- --------------------------------------------------
|
||
-- 5. 初始化存储过程 sp_refresh_bom_level_relation
|
||
-- 在BOM发布时调用，更新 md_new_bom_level_relation 表
|
||
-- 逻辑：扫描指定BOM的所有明细行，按 is_sub_bom=1 的 sub_bom_id 递归收集子BOM
|
||
-- --------------------------------------------------
|
||
|
||
DROP PROCEDURE IF EXISTS `sp_refresh_bom_level_relation`;
|
||
|
||
DELIMITER $$
|
||
|
||
CREATE PROCEDURE `sp_refresh_bom_level_relation`(
|
||
IN p_bom_id BIGINT -- 起始BOM ID（通常为发布的BOM）
|
||
)
|
||
BEGIN
|
||
DECLARE v_done INT DEFAULT 0;
|
||
DECLARE v_max_level INT DEFAULT 10; -- 最大递归层级保护
|
||
|
||
-- 临时表：递归收集 BOM->子BOM 关系
|
||
DROP TEMPORARY TABLE IF EXISTS tmp_bom_relation;
|
||
CREATE TEMPORARY TABLE tmp_bom_relation (
|
||
parent_bom_id BIGINT,
|
||
parent_material_id BIGINT,
|
||
child_bom_id BIGINT,
|
||
child_material_id BIGINT,
|
||
level INT,
|
||
bom_item_id BIGINT
|
||
);
|
||
|
||
-- 递归游标：从指定BOM出发，收集所有 is_sub_bom=1 的明细行对应的子BOM
|
||
INSERT INTO tmp_bom_relation
|
||
SELECT DISTINCT
|
||
p_bom_id AS parent_bom_id,
|
||
b.material_id AS parent_material_id,
|
||
item.sub_bom_id AS child_bom_id,
|
||
item.material_id AS child_material_id,
|
||
1 AS level,
|
||
item.id AS bom_item_id
|
||
FROM md_new_bom b
|
||
INNER JOIN md_new_bom_item item ON item.bom_id = b.id AND item.is_sub_bom = 1 AND item.sub_bom_id IS NOT NULL
|
||
WHERE b.id = p_bom_id;
|
||
|
||
-- 逐层展开（最多10层，防止死循环）
|
||
level_loop: WHILE v_done = 0 DO
|
||
INSERT IGNORE INTO tmp_bom_relation
|
||
SELECT DISTINCT
|
||
rel.child_bom_id AS parent_bom_id,
|
||
sub.material_id AS parent_material_id,
|
||
item.sub_bom_id AS child_bom_id,
|
||
item.material_id AS child_material_id,
|
||
rel.level + 1 AS level,
|
||
item.id AS bom_item_id
|
||
FROM tmp_bom_relation rel
|
||
INNER JOIN md_new_bom sub ON sub.id = rel.child_bom_id
|
||
INNER JOIN md_new_bom_item item ON item.bom_id = sub.id AND item.is_sub_bom = 1 AND item.sub_bom_id IS NOT NULL
|
||
WHERE rel.level < v_max_level;
|
||
IF ROW_COUNT() = 0 THEN
|
||
SET v_done = 1;
|
||
END IF;
|
||
END WHILE level_loop;
|
||
|
||
-- 删除旧关系（以该BOM为父级的所有记录）
|
||
DELETE FROM md_new_bom_level_relation WHERE parent_bom_id = p_bom_id;
|
||
|
||
-- 写入新关系
|
||
INSERT INTO md_new_bom_level_relation
|
||
(parent_bom_id, parent_material_id, child_bom_id, child_material_id, level, bom_item_id)
|
||
SELECT parent_bom_id, parent_material_id, child_bom_id, child_material_id, level, bom_item_id
|
||
FROM tmp_bom_relation;
|
||
|
||
-- 清理临时表
|
||
DROP TEMPORARY TABLE tmp_bom_relation;
|
||
END$$
|
||
|
||
DELIMITER ;
|
||
|
||
-- --------------------------------------------------
|
||
-- 6. 触发器：md_new_bom_item 增删改后自动刷新层级关系
|
||
-- --------------------------------------------------
|
||
|
||
DROP TRIGGER IF EXISTS `trg_md_new_bom_item_after_insert`;
|
||
|
||
DELIMITER $$
|
||
|
||
CREATE TRIGGER `trg_md_new_bom_item_after_insert`
|
||
AFTER INSERT ON `md_new_bom_item`
|
||
FOR EACH ROW
|
||
BEGIN
|
||
-- 如果插入的是子BOM行，且该BOM已有父BOM引用关系，则需要刷新关系表
|
||
-- 此处仅记录，待 BOM 发布时统一刷新 sp_refresh_bom_level_relation
|
||
-- 实际刷新由业务层在 publishBomVersion 时调用存储过程
|
||
-- 此触发器仅用于：删除 bom_item 时连带清理关系记录
|
||
END$$
|
||
|
||
DELIMITER ;
|
||
|
||
DROP TRIGGER IF EXISTS `trg_md_new_bom_item_after_delete`;
|
||
|
||
DELIMITER $$
|
||
|
||
CREATE TRIGGER `trg_md_new_bom_item_after_delete`
|
||
AFTER DELETE ON `md_new_bom_item`
|
||
FOR EACH ROW
|
||
BEGIN
|
||
-- 删除时清理孤立的关系记录
|
||
DELETE FROM md_new_bom_level_relation WHERE bom_item_id = OLD.id;
|
||
END$$
|
||
|
||
DELIMITER ;
|
||
|
||
-- --------------------------------------------------
|
||
-- 7. 批量初始化脚本（对现有已发布的多级BOM执行刷新）
|
||
-- 示例：对 id = 1 的 BOM 执行一次层级关系构建
|
||
-- 正式上线时，取消下面注释并填写实际 BOM ID 列表
|
||
-- --------------------------------------------------
|
||
|
||
-- CALL sp_refresh_bom_level_relation(1);
|
||
-- CALL sp_refresh_bom_level_relation(2);
|
||
-- CALL sp_refresh_bom_level_relation(3);
|
||
|
||
-- --------------------------------------------------
|
||
-- 8. 更新 BOM 主表的 max_level / is_multilevel 辅助字段
|
||
-- 在 sp_refresh_bom_level_relation 执行后调用
|
||
-- --------------------------------------------------
|
||
|
||
DROP PROCEDURE IF EXISTS `sp_update_bom_multilevel_flags`;
|
||
|
||
DELIMITER $$
|
||
|
||
CREATE PROCEDURE `sp_update_bom_multilevel_flags`(
|
||
IN p_bom_id BIGINT
|
||
)
|
||
BEGIN
|
||
DECLARE v_max_level INT;
|
||
DECLARE v_is_multilevel INT;
|
||
DECLARE v_component_count INT;
|
||
|
||
-- 计算最大层级
|
||
SELECT COALESCE(MAX(level), 0) INTO v_max_level
|
||
FROM md_new_bom_level_relation
|
||
WHERE parent_bom_id = p_bom_id;
|
||
|
||
-- 判断是否多级（直接子级中存在子BOM项即为多级）
|
||
SELECT COUNT(*) INTO v_is_multilevel
|
||
FROM md_new_bom_item
|
||
WHERE bom_id = p_bom_id AND is_sub_bom = 1 AND sub_bom_id IS NOT NULL;
|
||
|
||
-- 统计直接子件数量
|
||
SELECT COUNT(*) INTO v_component_count
|
||
FROM md_new_bom_item
|
||
WHERE bom_id = p_bom_id;
|
||
|
||
UPDATE md_new_bom
|
||
SET max_level = v_max_level,
|
||
is_multilevel = IF(v_max_level > 0 OR v_is_multilevel > 0, 1, 0),
|
||
component_count = v_component_count,
|
||
update_time = NOW()
|
||
WHERE id = p_bom_id;
|
||
END$$
|
||
|
||
DELIMITER ;
|
||
|
||
-- 示例调用：
|
||
-- CALL sp_update_bom_multilevel_flags(1);
|