zkxw/docs/sql/migrations/2026-10-10-market-mobile-menus.sql

-- =============================================================================
-- 小程序市场现场菜单：市场专员 / 地区经理两套目录与十二个业务叶子
--
-- 说明：
--   1. 面向每个租户已有的「移动端菜单」根生成菜单，不写死租户编号。
--   2. 目录仅用于角色授权分组，path 为空；首页只展示叶子。
--   3. 不在此处写入 sys_role_menu；由管理员在 Web 角色管理中手工勾选。
--   4. MySQL 5.7 可执行；所有 INSERT 均以 NOT EXISTS 保证幂等。
-- =============================================================================

-- 市场专员目录
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-market-directory|', root.tenant_no, '|', root.id, '|M||市场专员'
  )),
  '市场专员',
  root.id,
  'M',
  '',
  NULL,
  NULL,
  1,
  '1',
  '1',
  root.tenant_no,
  10,
  '小程序市场现场：市场专员菜单目录',
  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 = '市场专员'
      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),
  component = VALUES(component),
  perms = VALUES(perms),
  is_frame = VALUES(is_frame),
  visible = VALUES(visible),
  status = VALUES(status),
  order_num = VALUES(order_num),
  remark = VALUES(remark),
  update_time = VALUES(update_time),
  del_flag = VALUES(del_flag);

-- 地区经理目录
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-market-directory|', root.tenant_no, '|', root.id, '|M||地区经理'
  )),
  '地区经理',
  root.id,
  'M',
  '',
  NULL,
  NULL,
  1,
  '1',
  '1',
  root.tenant_no,
  20,
  '小程序市场现场：地区经理菜单目录',
  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 = '地区经理'
      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),
  component = VALUES(component),
  perms = VALUES(perms),
  is_frame = VALUES(is_frame),
  visible = VALUES(visible),
  status = VALUES(status),
  order_num = VALUES(order_num),
  remark = VALUES(remark),
  update_time = VALUES(update_time),
  del_flag = VALUES(del_flag);

-- 十二个业务叶子：每个角色目录各六个，均进入小程序受控 web-view。
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-market-leaf|', root.tenant_no, '|', root.id, '|', directory.id,
    '|', entry.path, '|', entry.perms
  )),
  entry.menu_name,
  directory.id,
  'C',
  entry.path,
  NULL,
  entry.perms,
  1,
  '1',
  '1',
  root.tenant_no,
  entry.order_num,
  CONCAT('小程序市场现场：', entry.role_name, ' / ', entry.menu_name),
  NULL,
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM sys_menu root
INNER JOIN (
  SELECT 'specialist' AS role_code, '市场专员' AS role_name,
         'presign' AS module_code, '预签约订单' AS menu_name,
         '/pages/market/webview/index?entry=specialist-presign' AS path,
         'mini:market:specialist:presign' AS perms, 10 AS order_num
  UNION ALL
  SELECT 'specialist', '市场专员', 'protocol', '协议单',
         '/pages/market/webview/index?entry=specialist-protocol',
         'mini:market:specialist:protocol', 20
  UNION ALL
  SELECT 'specialist', '市场专员', 'payment-chase', '催缴信息',
         '/pages/market/webview/index?entry=specialist-payment-chase',
         'mini:market:specialist:payment-chase', 30
  UNION ALL
  SELECT 'specialist', '市场专员', 'payment', '缴费信息',
         '/pages/market/webview/index?entry=specialist-payment',
         'mini:market:specialist:payment', 40
  UNION ALL
  SELECT 'specialist', '市场专员', 'sample', '样本信息',
         '/pages/market/webview/index?entry=specialist-sample',
         'mini:market:specialist:sample', 50
  UNION ALL
  SELECT 'specialist', '市场专员', 'freight', '运费信息',
         '/pages/market/webview/index?entry=specialist-freight',
         'mini:market:specialist:freight', 60
  UNION ALL
  SELECT 'manager', '地区经理', 'presign', '预签约订单',
         '/pages/market/webview/index?entry=manager-presign',
         'mini:market:manager:presign', 10
  UNION ALL
  SELECT 'manager', '地区经理', 'protocol', '协议单',
         '/pages/market/webview/index?entry=manager-protocol',
         'mini:market:manager:protocol', 20
  UNION ALL
  SELECT 'manager', '地区经理', 'payment-chase', '催缴信息',
         '/pages/market/webview/index?entry=manager-payment-chase',
         'mini:market:manager:payment-chase', 30
  UNION ALL
  SELECT 'manager', '地区经理', 'payment', '缴费信息',
         '/pages/market/webview/index?entry=manager-payment',
         'mini:market:manager:payment', 40
  UNION ALL
  SELECT 'manager', '地区经理', 'sample', '样本信息',
         '/pages/market/webview/index?entry=manager-sample',
         'mini:market:manager:sample', 50
  UNION ALL
  SELECT 'manager', '地区经理', 'freight', '运费信息',
         '/pages/market/webview/index?entry=manager-freight',
         'mini:market:manager:freight', 60
) entry
  ON 1 = 1
INNER JOIN sys_menu directory
  ON directory.tenant_no = root.tenant_no
 AND directory.parent_id = root.id
 AND directory.menu_name = entry.role_name
 AND directory.menu_type = 'M'
 AND directory.path = ''
 AND directory.del_flag = 0
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 = directory.id
      AND existing.menu_name = entry.menu_name
      AND existing.perms = entry.perms
      AND existing.path = entry.path
      AND existing.menu_type = 'C'
      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),
  component = VALUES(component),
  perms = VALUES(perms),
  is_frame = VALUES(is_frame),
  visible = VALUES(visible),
  status = VALUES(status),
  order_num = VALUES(order_num),
  remark = VALUES(remark),
  update_time = VALUES(update_time),
  del_flag = VALUES(del_flag);