shanxia2.0/docs/sql/migrations/2026-08-09-system-menu-groups.sql

-- 系统管理：新增二级目录「系统信息」「临时存放」，并迁移原子菜单 parent_id
-- 幂等可重复执行；与 web-admin/src/router/modules/sqxp.ts 对齐
-- 目录 id：SYSM...010 / SYSM...011

-- 1) 插入二级目录
INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'SYSM000000000000000000000000000010', '系统信息', 'SYSM000000000000000000000000000001',
  'M', 'info', NULL, NULL, 1, '1', '1', 'shanxia2.0', 1, '系统管理-系统信息分组',
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_menu` WHERE `id` = 'SYSM000000000000000000000000000010' AND `del_flag` = 0
);

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  'SYSM000000000000000000000000000011', '临时存放', 'SYSM000000000000000000000000000001',
  'M', 'temp', NULL, NULL, 1, '1', '1', 'shanxia2.0', 2, '系统管理-临时存放分组',
  'system', '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_menu` WHERE `id` = 'SYSM000000000000000000000000000011' AND `del_flag` = 0
);

-- 2) 「系统信息」下菜单
UPDATE `sys_menu`
SET `parent_id` = 'SYSM000000000000000000000000000010',
    `update_time` = NOW()
WHERE `del_flag` = 0
  AND `id` IN (
    '101',            -- 租户管理
    '102',            -- 字典管理
    '106',            -- 菜单管理
    '103',            -- 组织管理
    '105',            -- 角色管理
    '104',            -- 用户管理
    '108',            -- 职位管理
    'SYS_OPERLOG'     -- 操作日志
  );

-- 3) 「临时存放」下菜单（含隐藏附属页）
UPDATE `sys_menu`
SET `parent_id` = 'SYSM000000000000000000000000000011',
    `update_time` = NOW()
WHERE `del_flag` = 0
  AND `id` IN (
    'SYS_APPROVER_SETTING',                 -- 报价、合同审批人设置
    'CRM3000000000000000000000000040',      -- 主体危化品能力
    'CRM1000000000000000000000000010',      -- 线索管理
    'CRM1000000000000000000000000011',      -- 线索详情
    'CRM1000000000000000000000000012',      -- 线索统计（若存在）
    'CRM2000000000000000000000000010',      -- 商机管理
    'CRM2000000000000000000000000011',      -- 商机详情
    'CRM2000000000000000000000000012',      -- 商机看板
    'CRM2000000000000000000000000013',      -- 销售漏斗
    'CRM2000000000000000000000000014',      -- 商机统计
    'CRM1000000000000000000000000003',      -- 公海池
    'CRM1000000000000000000000000004'       -- 公海池规则
  );

-- 排序：系统信息内
UPDATE `sys_menu` SET `order_num` = 1, `update_time` = NOW() WHERE `id` = '101' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 2, `update_time` = NOW() WHERE `id` = '102' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 3, `update_time` = NOW() WHERE `id` = '106' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 4, `update_time` = NOW() WHERE `id` = '103' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 5, `update_time` = NOW() WHERE `id` = '105' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 6, `update_time` = NOW() WHERE `id` = '104' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 7, `update_time` = NOW() WHERE `id` = '108' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 8, `update_time` = NOW() WHERE `id` = 'SYS_OPERLOG' AND `del_flag` = 0;

-- 排序：临时存放内（可见菜单）
UPDATE `sys_menu` SET `order_num` = 1, `update_time` = NOW() WHERE `id` = 'SYS_APPROVER_SETTING' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 2, `update_time` = NOW() WHERE `id` = 'CRM3000000000000000000000000040' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 3, `update_time` = NOW() WHERE `id` = 'CRM1000000000000000000000000010' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 4, `update_time` = NOW() WHERE `id` = 'CRM2000000000000000000000000010' AND `del_flag` = 0;
UPDATE `sys_menu` SET `order_num` = 5, `update_time` = NOW() WHERE `id` = 'CRM1000000000000000000000000003' AND `del_flag` = 0;

-- 4) 角色菜单：曾拥有子菜单的角色，补挂二级目录（避免孤儿叶子）
INSERT INTO `sys_role_menu` (
  `id`, `role_id`, `menu_id`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  CONCAT('RM_SYS_INFO_', UPPER(MD5(CONCAT(rm.role_id, ':SYSM000000000000000000000000000010')))),
  rm.role_id,
  'SYSM000000000000000000000000000010',
  COALESCE(NULLIF(rm.tenant_no, ''), 'shanxia2.0'),
  0,
  'auto: system info group',
  'system',
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM `sys_role_menu` rm
WHERE rm.del_flag = 0
  AND rm.menu_id IN (
    '101', '102', '106', '103', '105', '104', '108', 'SYS_OPERLOG'
  )
  AND NOT EXISTS (
    SELECT 1
    FROM `sys_role_menu` x
    WHERE x.role_id = rm.role_id
      AND x.menu_id = 'SYSM000000000000000000000000000010'
      AND x.del_flag = 0
  )
GROUP BY rm.role_id, rm.tenant_no;

INSERT INTO `sys_role_menu` (
  `id`, `role_id`, `menu_id`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  CONCAT('RM_SYS_TEMP_', UPPER(MD5(CONCAT(rm.role_id, ':SYSM000000000000000000000000000011')))),
  rm.role_id,
  'SYSM000000000000000000000000000011',
  COALESCE(NULLIF(rm.tenant_no, ''), 'shanxia2.0'),
  0,
  'auto: system temp group',
  'system',
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM `sys_role_menu` rm
WHERE rm.del_flag = 0
  AND rm.menu_id IN (
    'SYS_APPROVER_SETTING',
    'CRM3000000000000000000000000040',
    'CRM1000000000000000000000000010',
    'CRM1000000000000000000000000011',
    'CRM1000000000000000000000000012',
    'CRM2000000000000000000000000010',
    'CRM2000000000000000000000000011',
    'CRM2000000000000000000000000012',
    'CRM2000000000000000000000000013',
    'CRM2000000000000000000000000014',
    'CRM1000000000000000000000000003',
    'CRM1000000000000000000000000004'
  )
  AND NOT EXISTS (
    SELECT 1
    FROM `sys_role_menu` x
    WHERE x.role_id = rm.role_id
      AND x.menu_id = 'SYSM000000000000000000000000000011'
      AND x.del_flag = 0
  )
GROUP BY rm.role_id, rm.tenant_no;