JY1.0/sql/二级组件迁移顺序与缺项修正_测试库_20260806.sql

/* 修改人:Ld 修改时间:2026-08-06 16:15:15; 补回建议清单缺项并重排角色2新一级菜单下二级组件顺序 */
SET XACT_ABORT ON;
BEGIN TRAN;

IF NOT EXISTS
(
    SELECT 1
    FROM dbo.基础表_角色模块信息
    WHERE id = 135
      AND pid = 163
      AND 角色编号 = 2
)
BEGIN
    INSERT INTO dbo.基础表_角色模块信息
    (
        id,
        pid,
        模块顺序,
        角色编号,
        是否启用,
        角色模块顺序
    )
    VALUES
    (
        135,
        163,
        135,
        2,
        1,
        7
    );
END
ELSE
BEGIN
    UPDATE dbo.基础表_角色模块信息
    SET 是否启用 = 1,
        角色模块顺序 = 7
    WHERE id = 135
      AND pid = 163
      AND 角色编号 = 2;
END;

IF NOT EXISTS
(
    SELECT 1
    FROM dbo.基础表_角色模块信息
    WHERE id = 45
      AND pid = 164
      AND 角色编号 = 2
)
BEGIN
    INSERT INTO dbo.基础表_角色模块信息
    (
        id,
        pid,
        模块顺序,
        角色编号,
        是否启用,
        角色模块顺序
    )
    VALUES
    (
        45,
        164,
        45,
        2,
        1,
        24
    );
END
ELSE
BEGIN
    UPDATE dbo.基础表_角色模块信息
    SET 是否启用 = 1,
        角色模块顺序 = 24
    WHERE id = 45
      AND pid = 164
      AND 角色编号 = 2;
END;

DECLARE @OrderMap TABLE
(
    id INT PRIMARY KEY,
    pid INT NOT NULL,
    顺序 INT NOT NULL
);

INSERT INTO @OrderMap (id, pid, 顺序)
VALUES
(132, 159, 1),
(67, 160, 1),
(11, 160, 2),
(12, 160, 3),
(13, 160, 4),
(14, 160, 5),
(147, 160, 6),
(68, 160, 7),
(17, 160, 8),
(16, 160, 9),
(18, 160, 10),
(81, 160, 11),
(19, 160, 12),
(15, 160, 13),
(99, 160, 14),
(86, 160, 15),
(87, 160, 16),
(108, 160, 17),
(114, 160, 18),
(120, 160, 19),
(82, 160, 20),
(129, 160, 21),
(155, 160, 22),
(156, 160, 23),
(35, 160, 24),
(36, 160, 25),
(37, 160, 26),
(41, 160, 27),
(124, 160, 28),
(157, 160, 29),
(88, 160, 30),
(94, 161, 1),
(78, 161, 2),
(28, 161, 3),
(98, 161, 4),
(80, 161, 5),
(29, 161, 6),
(79, 161, 7),
(30, 161, 8),
(100, 161, 9),
(84, 161, 10),
(95, 161, 11),
(153, 161, 12),
(154, 161, 13),
(149, 161, 14),
(25, 162, 1),
(69, 162, 2),
(24, 162, 3),
(102, 162, 4),
(21, 162, 5),
(22, 162, 6),
(23, 162, 7),
(117, 162, 8),
(134, 162, 9),
(150, 162, 10),
(125, 162, 11),
(126, 162, 12),
(133, 162, 13),
(104, 163, 1),
(110, 163, 2),
(103, 163, 3),
(121, 163, 4),
(128, 163, 5),
(131, 163, 6),
(135, 163, 7),
(43, 164, 1),
(112, 164, 2),
(44, 164, 3),
(46, 164, 4),
(47, 164, 5),
(48, 164, 6),
(49, 164, 7),
(75, 164, 8),
(51, 164, 9),
(76, 164, 10),
(77, 164, 11),
(50, 164, 12),
(92, 164, 13),
(93, 164, 14),
(96, 164, 15),
(118, 164, 16),
(122, 164, 17),
(123, 164, 18),
(130, 164, 19),
(138, 164, 20),
(143, 164, 21),
(151, 164, 22),
(158, 164, 23),
(45, 164, 24);

UPDATE r
SET r.pid = o.pid,
    r.模块顺序 = r.id,
    r.是否启用 = 1,
    r.角色模块顺序 = o.顺序
FROM dbo.基础表_角色模块信息 r
INNER JOIN @OrderMap o ON o.id = r.id
WHERE r.角色编号 = 2
  AND r.pid IN (159, 160, 161, 162, 163, 164);

SELECT p.title AS 一级菜单, COUNT(*) AS 二级数量
FROM dbo.基础表_角色模块信息 r
INNER JOIN dbo.基础表_模块信息 p ON p.id = r.pid
WHERE r.角色编号 = 2
  AND ISNULL(r.是否启用, 1) = 1
  AND r.pid IN (159, 160, 161, 162, 163, 164)
GROUP BY p.title, r.pid
ORDER BY r.pid;

COMMIT TRAN;