zkxw/docs/sql/migrations/2026-09-21-oa-instance-view-grant.sql

-- 评论 @ 用户：查看授权（不写入抄送任务）
ALTER TABLE `oa_instance_comment`
  ADD COLUMN `mention_user_ids_json` text COLLATE utf8mb4_unicode_ci NULL COMMENT '评论@用户ID列表 JSON' AFTER `revoked_time`;

CREATE TABLE IF NOT EXISTS `oa_instance_view_grant` (
  `id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID',
  `instance_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '实例ID',
  `user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '被授权查看人',
  `source` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'comment_mention' COMMENT 'comment_mention',
  `comment_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '来源评论ID，撤回评论不删除授权',
  `granted_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '授权人',
  `tenant_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` smallint(6) NOT NULL DEFAULT '0' COMMENT '删除标记',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_oa_view_grant_user` (`tenant_no`,`instance_id`,`user_id`),
  KEY `idx_oa_view_grant_user_time` (`tenant_no`,`user_id`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='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('oa-mention-', s.`tenant_no`, '-', s.`id`)),
  '提到我的',
  s.`parent_id`,
  s.`menu_type`,
  '/pages/oa/mentions/index',
  s.`component`,
  'oa:mention',
  s.`is_frame`,
  s.`visible`,
  s.`status`,
  s.`tenant_no`,
  IFNULL(s.`order_num`, 0) + 1,
  '评论@后可查看的审批单',
  s.`create_by`,
  s.`create_dept_id`,
  NOW(),
  s.`update_by`,
  NOW(),
  0
FROM `sys_menu` s
WHERE s.`del_flag` = 0
  AND s.`menu_name` = '抄送我的'
  AND s.`path` LIKE '%oa/cc%'
  AND NOT EXISTS (
    SELECT 1 FROM `sys_menu` x
    WHERE x.`del_flag` = 0
      AND x.`tenant_no` = s.`tenant_no`
      AND x.`path` LIKE '%oa/mentions%'
  );

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('oa-mention-role-', rm.`role_id`, '-', m.`id`)),
  rm.`role_id`,
  m.`id`,
  m.`tenant_no`,
  NULL,
  NULL,
  rm.`create_by`,
  IFNULL(rm.`create_dept_id`, ''),
  NOW(),
  rm.`update_by`,
  NOW(),
  0
FROM `sys_menu` m
JOIN `sys_menu` cc
  ON cc.`del_flag` = 0
 AND cc.`tenant_no` = m.`tenant_no`
 AND cc.`parent_id` = m.`parent_id`
 AND cc.`menu_name` = '抄送我的'
 AND cc.`path` LIKE '%oa/cc%'
JOIN `sys_role_menu` rm
  ON rm.`del_flag` = 0
 AND rm.`menu_id` = cc.`id`
WHERE m.`del_flag` = 0
  AND m.`path` LIKE '%oa/mentions%'
  AND NOT EXISTS (
    SELECT 1 FROM `sys_role_menu` x
    WHERE x.`del_flag` = 0
      AND x.`role_id` = rm.`role_id`
      AND x.`menu_id` = m.`id`
  );