zkxw/docs/sql/migrations/2026-10-11-mobile-menu-market-oa-dirs.sql

-- =============================================================================
-- 移动端菜单重组：市场部 / OA工作流两大目录
--
-- 目标结构：
--   移动端菜单
--   ├─ 市场部
--   │  ├─ 预签约订单（专员）…运费信息（专员）
--   │  └─ 预签约订单（经理）…运费信息（经理）
--   └─ OA工作流
--      └─ 发起审批…开启微信通知
--
-- 说明：
--   1. 复用原「市场专员」目录 id，改名为「市场部」；原「地区经理」目录软删。
--   2. 叶子 id / path / perms 不变，仅调整 parent_id、menu_name、order_num。
--   3. 已授权 OA 叶子的角色自动补授「OA工作流」目录（幂等）。
--   4. MySQL 5.7 可执行。
-- =============================================================================

-- 1) 「市场专员」→「市场部」
UPDATE sys_menu market_dir
INNER JOIN sys_menu root
  ON root.id = market_dir.parent_id
 AND root.parent_id = '0'
 AND root.menu_name = '移动端菜单'
 AND root.del_flag = 0
SET
  market_dir.menu_name = '市场部',
  market_dir.order_num = 10,
  market_dir.remark = '小程序：市场部菜单目录',
  market_dir.update_time = NOW()
WHERE market_dir.menu_type = 'M'
  AND market_dir.path = ''
  AND market_dir.del_flag = 0
  AND market_dir.menu_name IN ('市场专员', '市场部');

-- 2) 新建「OA工作流」目录（每个租户移动端根下一条）
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
  MD5(CONCAT(
    'mobile-oa-directory|', root.tenant_no, '|', root.id, '|M||OA工作流'
  )),
  'OA工作流',
  root.id,
  'M',
  '',
  NULL,
  NULL,
  1,
  '1',
  '1',
  root.tenant_no,
  20,
  '小程序：OA工作流菜单目录',
  NULL,
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM sys_menu root
WHERE root.parent_id = '0'
  AND root.menu_name = '移动端菜单'
  AND root.del_flag = 0
  AND NOT EXISTS (
    SELECT 1
    FROM sys_menu existing
    WHERE existing.tenant_no = root.tenant_no
      AND existing.parent_id = root.id
      AND existing.menu_name = 'OA工作流'
      AND existing.menu_type = 'M'
      AND existing.path = ''
      AND existing.del_flag = 0
  )
ON DUPLICATE KEY UPDATE
  tenant_no = VALUES(tenant_no),
  parent_id = VALUES(parent_id),
  menu_name = VALUES(menu_name),
  menu_type = VALUES(menu_type),
  path = VALUES(path),
  order_num = VALUES(order_num),
  remark = VALUES(remark),
  update_time = VALUES(update_time),
  del_flag = VALUES(del_flag);

-- 3) 市场叶子：挂到「市场部」，名称加（专员）/（经理），调整排序
UPDATE sys_menu leaf
INNER JOIN sys_menu root
  ON root.parent_id = '0'
 AND root.menu_name = '移动端菜单'
 AND root.del_flag = 0
 AND root.tenant_no = leaf.tenant_no
INNER JOIN sys_menu market_dir
  ON market_dir.tenant_no = root.tenant_no
 AND market_dir.parent_id = root.id
 AND market_dir.menu_name = '市场部'
 AND market_dir.menu_type = 'M'
 AND market_dir.path = ''
 AND market_dir.del_flag = 0
INNER JOIN (
  SELECT 'mini:market:specialist:presign' AS perms, '预签约订单（专员）' AS menu_name, 10 AS order_num
  UNION ALL SELECT 'mini:market:specialist:protocol', '协议单（专员）', 20
  UNION ALL SELECT 'mini:market:specialist:payment-chase', '催缴信息（专员）', 30
  UNION ALL SELECT 'mini:market:specialist:payment', '缴费信息（专员）', 40
  UNION ALL SELECT 'mini:market:specialist:freight', '运费信息（专员）', 50
  UNION ALL SELECT 'mini:market:manager:presign', '预签约订单（经理）', 60
  UNION ALL SELECT 'mini:market:manager:protocol', '协议单（经理）', 70
  UNION ALL SELECT 'mini:market:manager:payment-chase', '催缴信息（经理）', 80
  UNION ALL SELECT 'mini:market:manager:payment', '缴费信息（经理）', 90
  UNION ALL SELECT 'mini:market:manager:freight', '运费信息（经理）', 100
) map ON map.perms = leaf.perms
SET
  leaf.parent_id = market_dir.id,
  leaf.menu_name = map.menu_name,
  leaf.order_num = map.order_num,
  leaf.remark = CONCAT('小程序市场现场：', map.menu_name),
  leaf.update_time = NOW()
WHERE leaf.menu_type = 'C'
  AND leaf.del_flag = 0
  AND leaf.perms LIKE 'mini:market:%';

-- 4) OA 叶子：挂到「OA工作流」
UPDATE sys_menu leaf
INNER JOIN sys_menu root
  ON root.parent_id = '0'
 AND root.menu_name = '移动端菜单'
 AND root.del_flag = 0
 AND root.tenant_no = leaf.tenant_no
INNER JOIN sys_menu oa_dir
  ON oa_dir.tenant_no = root.tenant_no
 AND oa_dir.parent_id = root.id
 AND oa_dir.menu_name = 'OA工作流'
 AND oa_dir.menu_type = 'M'
 AND oa_dir.path = ''
 AND oa_dir.del_flag = 0
SET
  leaf.parent_id = oa_dir.id,
  leaf.update_time = NOW()
WHERE leaf.del_flag = 0
  AND leaf.menu_type = 'C'
  AND (
    leaf.perms IN (
      'mini:oa:start',
      'mini:oa:drafts',
      'mini:oa:pending',
      'mini:oa:done',
      'mini:oa:mine',
      'mini:oa:cc',
      'oa:mention',
      'mini:oa:wechat-notify'
    )
    OR leaf.path IN (
      '/pages/oa/start/index',
      '/pages/oa/drafts/index',
      '/pages/oa/pending/index',
      '/pages/oa/done/index',
      '/pages/oa/mine/index',
      '/pages/oa/cc/index',
      '/pages/oa/mentions/index',
      '/pages/oa/wechat-notify/index'
    )
  );

-- 5) 软删「地区经理」目录（步骤 3 已把子节点迁到市场部；避免同表子查询触发 1093）
UPDATE sys_menu manager_dir
INNER JOIN sys_menu root
  ON root.id = manager_dir.parent_id
 AND root.parent_id = '0'
 AND root.menu_name = '移动端菜单'
 AND root.del_flag = 0
SET
  manager_dir.del_flag = 1,
  manager_dir.update_time = NOW(),
  manager_dir.remark = '已重组至市场部，软删'
WHERE manager_dir.menu_type = 'M'
  AND manager_dir.path = ''
  AND manager_dir.menu_name = '地区经理'
  AND manager_dir.del_flag = 0;

-- 6) 已有 OA 叶子授权的角色补授「OA工作流」目录
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
  MD5(CONCAT(r.id, '|', oa_dir.id)),
  r.id,
  oa_dir.id,
  IFNULL(NULLIF(r.tenant_no, ''), oa_dir.tenant_no),
  0,
  'grant OA工作流 directory to roles with OA leaf menus',
  NULL,
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM sys_role r
INNER JOIN sys_role_menu rm
  ON rm.role_id = r.id
 AND rm.del_flag = 0
INNER JOIN sys_menu leaf
  ON leaf.id = rm.menu_id
 AND leaf.del_flag = 0
 AND leaf.menu_type = 'C'
 AND (
   leaf.perms IN (
     'mini:oa:start',
     'mini:oa:drafts',
     'mini:oa:pending',
     'mini:oa:done',
     'mini:oa:mine',
     'mini:oa:cc',
     'oa:mention',
     'mini:oa:wechat-notify'
   )
   OR leaf.path LIKE '/pages/oa/%'
 )
INNER JOIN sys_menu oa_dir
  ON oa_dir.id = leaf.parent_id
 AND oa_dir.menu_name = 'OA工作流'
 AND oa_dir.menu_type = 'M'
 AND oa_dir.del_flag = 0
WHERE r.del_flag = 0
  AND NOT EXISTS (
    SELECT 1
    FROM sys_role_menu x
    WHERE x.del_flag = 0
      AND x.role_id = r.id
      AND x.menu_id = oa_dir.id
  )
GROUP BY r.id, oa_dir.id, r.tenant_no, oa_dir.tenant_no;