111 lines
6.2 KiB
SQL
111 lines
6.2 KiB
SQL
-- ================================================================
|
||
-- 第一部分：取消软删 — 单体设备相关产品分类 + 单体设备记录
|
||
-- （若未执行过误删可跳过本段，重复执行亦安全）
|
||
-- ================================================================
|
||
|
||
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
-- 1) 恢复「单体设备 / 核心单体设备」根分类
|
||
UPDATE f_product_category
|
||
SET del_flag = 0
|
||
WHERE del_flag = 1
|
||
AND name_zh IN ('单体设备', '核心单体设备');
|
||
|
||
-- 2) 恢复挂在上述根下的子分类（名称关联，不要求 module_type 已正确）
|
||
UPDATE f_product_category child
|
||
INNER JOIN f_product_category parent ON parent.product_category_id = child.parent_id
|
||
SET child.del_flag = 0
|
||
WHERE child.del_flag = 1
|
||
AND parent.name_zh IN ('单体设备', '核心单体设备');
|
||
|
||
-- 3) 恢复历史上常用的单体子目录 ID 段（migration 51–62、图册 200–205）
|
||
UPDATE f_product_category
|
||
SET del_flag = 0
|
||
WHERE del_flag = 1
|
||
AND (
|
||
product_category_id BETWEEN 51 AND 62
|
||
OR product_category_id BETWEEN 200 AND 205
|
||
);
|
||
|
||
-- 4) 恢复单体设备根上 module_type = equipment 但被软删的行（若有）
|
||
UPDATE f_product_category
|
||
SET del_flag = 0
|
||
WHERE del_flag = 1
|
||
AND module_type = 'equipment'
|
||
AND (parent_id IS NULL OR parent_id = 0);
|
||
|
||
-- 5) 恢复 f_single_equipment：分类属 equipment，或挂在已知单体分类 ID 上
|
||
UPDATE f_single_equipment se
|
||
LEFT JOIN f_product_category pc ON pc.product_category_id = se.category_id AND pc.del_flag = 0
|
||
SET se.del_flag = 0
|
||
WHERE se.del_flag = 1
|
||
AND (
|
||
pc.module_type = 'equipment'
|
||
OR se.category_id BETWEEN 51 AND 62
|
||
OR se.category_id BETWEEN 200 AND 205
|
||
OR se.category_id = 5
|
||
);
|
||
|
||
-- ================================================================
|
||
-- 第二部分：图册六大子类 + 图册设备（仅追加，判重插入）
|
||
-- ================================================================
|
||
|
||
SELECT COALESCE(
|
||
(SELECT product_category_id FROM f_product_category
|
||
WHERE module_type = 'equipment' AND (parent_id IS NULL OR parent_id = 0)
|
||
ORDER BY del_flag ASC, sort_order, product_category_id LIMIT 1),
|
||
(SELECT product_category_id FROM f_product_category
|
||
WHERE name_zh IN ('单体设备', '核心单体设备')
|
||
ORDER BY del_flag ASC, product_category_id LIMIT 1)
|
||
) INTO @equip_root;
|
||
|
||
UPDATE f_product_category
|
||
SET module_type = 'equipment'
|
||
WHERE product_category_id = @equip_root
|
||
AND (@equip_root IS NOT NULL)
|
||
AND (module_type IS NULL OR module_type = '' OR module_type <> 'equipment');
|
||
|
||
-- 仅插入/更新六个图册子类，不碰其它子目录
|
||
INSERT INTO f_product_category (product_category_id, name_zh, name_en, module_type, parent_id, sort_order, is_published) VALUES
|
||
(200, '卷取设备', 'Reeling & Coiling Equipment', 'equipment', @equip_root, 200, 1),
|
||
(201, '炉类设备', 'Furnace Equipment', 'equipment', @equip_root, 201, 1),
|
||
(202, '涂布与焊接设备', 'Coating & Welding Equipment', 'equipment', @equip_root, 202, 1),
|
||
(203, '破鳞设备', 'Scale Breaking Equipment', 'equipment', @equip_root, 203, 1),
|
||
(204, '拉矫矫直设备', 'Leveling & Straightening Equipment', 'equipment', @equip_root, 204, 1),
|
||
(205, '剪切修边设备', 'Shearing & Trimming Equipment', 'equipment', @equip_root, 205, 1)
|
||
ON DUPLICATE KEY UPDATE
|
||
name_zh = VALUES(name_zh),
|
||
name_en = VALUES(name_en),
|
||
module_type = 'equipment',
|
||
parent_id = VALUES(parent_id),
|
||
sort_order = VALUES(sort_order),
|
||
del_flag = 0,
|
||
is_published = 1;
|
||
|
||
-- 图册 15 条：按「分类 + 中文名」判重，已存在则跳过
|
||
INSERT INTO f_single_equipment (category_id, name_zh, name_en, technical_highlights_zh, technical_highlights_en, sort_order, is_published)
|
||
SELECT s.category_id, s.name_zh, s.name_en, s.tzh, s.ten, s.so, s.pub
|
||
FROM (
|
||
SELECT 200 AS category_id, '开收卷机（机型一）' AS name_zh, 'Payoff / Tension Reel (Type A)' AS name_en, '板材开卷、张力卷取与收卷' AS tzh, 'Strip payoff, tension reel and recoiling' AS ten, 200 AS so, 1 AS pub UNION ALL
|
||
SELECT 200, '开收卷机（机型二）', 'Payoff / Tension Reel (Type B)', '重载卷取与张力控制', 'Heavy-duty recoiling with tension control', 210, 1 UNION ALL
|
||
SELECT 201, '立式炉', 'Vertical Furnace', '立式布置热处理炉', 'Vertically arranged heat-treatment furnace', 220, 1 UNION ALL
|
||
SELECT 201, '卧式炉', 'Horizontal Furnace', '卧式连续热处理炉', 'Horizontal continuous heat-treatment furnace', 230, 1 UNION ALL
|
||
SELECT 202, '焊机', 'Welder', '带钢闪光焊、搭接焊等', 'Flash welding, lap welding for strip joining', 240, 1 UNION ALL
|
||
SELECT 202, '立式涂机', 'Vertical Coating Machine', '立式辊涂、涂层施加', 'Vertical roll coater / film application', 250, 1 UNION ALL
|
||
SELECT 202, '卧式涂机', 'Horizontal Coating Machine', '水平涂覆与烘干前道工序', 'Horizontal coating application', 260, 1 UNION ALL
|
||
SELECT 203, '破鳞机（机型一）', 'Scale Breaker (Type I)', '热轧氧化铁皮破碎与疏松', 'Breaking and loosening hot-rolled scale', 270, 1 UNION ALL
|
||
SELECT 203, '破鳞机（机型二）', 'Scale Breaker (Type II)', '另一型式破鳞与延伸', 'Alternative scale breaking configuration', 280, 1 UNION ALL
|
||
SELECT 204, '拉矫机', 'Tension Leveler', '带钢拉伸弯曲矫直', 'Tension leveling / stretch leveling', 290, 1 UNION ALL
|
||
SELECT 204, '两弯两矫拉弯矫直机', 'Two-bend Two-straight Stretch-bend Leveler', '多辊拉弯矫直改善板形', 'Multi-roll stretch-bend leveling for strip shape', 300, 1 UNION ALL
|
||
SELECT 205, '圆盘剪', 'Side Trimmer', '两侧碎边裁剪宽度', 'Side trimming for strip width', 310, 1 UNION ALL
|
||
SELECT 205, '碎边剪', 'Edge Chop Shear', '碎边切断与收集', 'Chopping and handling of edge trim', 320, 1 UNION ALL
|
||
SELECT 205, '圆盘剪（双刀头）', 'Side Trimmer (Twin Knife Head)', '双刀头提高修边效率', 'Twin knife heads for trimming efficiency', 330, 1 UNION ALL
|
||
SELECT 205, '双刀头碎边圆盘剪', 'Twin-head Edge-chop Disc Shear', '碎边与圆盘剪组合结构', 'Combined edge chop and disc shear', 340, 1
|
||
) AS s
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM f_single_equipment se
|
||
WHERE se.del_flag = 0 AND se.category_id = s.category_id AND se.name_zh = s.name_zh
|
||
);
|
||
|
||
ALTER TABLE f_product_category AUTO_INCREMENT = 210;
|