zkxw/docs/sql/migrations/2026-11-11-storage-cert-qc-field-acl.sql

-- Issue #93：储存证质检信息字段级 ACL（质检/生产）。MySQL 5.7，可重复执行。
-- 菜单按钮「储存证质检字段权限配置」控制弹窗 footer 的「质检权限」「生产权限」入口；
-- 本脚本会将该按钮授权给当前租户下全部有效角色。

CREATE TABLE IF NOT EXISTS biz_storage_cert_qc_acl (
  id VARCHAR(64) NOT NULL,
  scope VARCHAR(32) NOT NULL COMMENT 'qc|production',
  field_keys TEXT NOT NULL COMMENT '可编辑字段 key JSON 数组',
  configured_flag CHAR(1) NOT NULL DEFAULT '0' COMMENT '是否已正式配置 0否1是',
  tenant_no VARCHAR(64) NOT NULL,
  order_num INT NULL,
  remark VARCHAR(512) NULL,
  create_by VARCHAR(64) NULL,
  create_dept_id VARCHAR(64) NOT NULL DEFAULT '',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_by VARCHAR(64) NULL,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  del_flag SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_tenant_scope (tenant_no, scope, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS biz_storage_cert_qc_acl_user (
  id VARCHAR(64) NOT NULL,
  acl_id VARCHAR(64) NOT NULL,
  user_id VARCHAR(64) NOT NULL,
  tenant_no VARCHAR(64) NOT NULL,
  order_num INT NULL,
  remark VARCHAR(512) NULL,
  create_by VARCHAR(64) NULL,
  create_dept_id VARCHAR(64) NOT NULL DEFAULT '',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_by VARCHAR(64) NULL,
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  del_flag SMALLINT NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY ix_tenant_acl (tenant_no, acl_id, del_flag),
  KEY ix_tenant_user (tenant_no, user_id, del_flag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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
    INSERT(INSERT(MD5(CONCAT('storage-cert-qc-acl:', parent.tenant_no, ':', parent.id)),
                  13, 1, '3'), 17, 1, '8'),
    '储存证质检字段权限配置', parent.id, 'F', '', NULL,
    'cell-storage:sample-info:storage-cert-qc-acl', 1, '1', '1', parent.tenant_no, 95,
    '控制储存证质检弹窗中质检/生产字段与人员 ACL 配置入口',
    'migration', '', NOW(), 'migration', NOW(), 0
FROM sys_menu AS parent
WHERE parent.del_flag = 0 AND parent.status = '1'
  AND parent.perms = 'cell-storage:sample-info:list'
  AND NOT EXISTS (
      SELECT 1 FROM sys_menu AS existing
      WHERE existing.parent_id = parent.id
        AND existing.tenant_no = parent.tenant_no
        AND existing.perms = 'cell-storage:sample-info:storage-cert-qc-acl'
        AND existing.del_flag = 0
  );

-- 给所有有效角色授予「质检权限 / 生产权限」配置按钮（同一菜单码）
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
    INSERT(INSERT(MD5(CONCAT('storage-cert-qc-acl-role:', r.tenant_no, ':', r.id, ':', m.id)),
                  13, 1, '3'), 17, 1, '8'),
    r.id,
    m.id,
    r.tenant_no,
    NULL,
    'migration: grant storage-cert-qc-acl to all roles',
    'migration',
    '',
    NOW(),
    'migration',
    NOW(),
    0
FROM sys_role AS r
JOIN sys_menu AS m
  ON m.tenant_no = r.tenant_no
 AND m.perms = 'cell-storage:sample-info:storage-cert-qc-acl'
 AND m.del_flag = 0
 AND m.status = '1'
WHERE r.del_flag = 0
  AND NOT EXISTS (
      SELECT 1 FROM sys_role_menu AS existing
      WHERE existing.role_id = r.id
        AND existing.menu_id = m.id
        AND existing.tenant_no = r.tenant_no
        AND existing.del_flag = 0
  );

SELECT tenant_no, parent_id, id, menu_name, perms, status
FROM sys_menu
WHERE perms = 'cell-storage:sample-info:storage-cert-qc-acl' AND del_flag = 0;

SELECT r.tenant_no, r.role_name, m.perms, rm.id AS role_menu_id
FROM sys_role_menu AS rm
JOIN sys_role AS r ON r.id = rm.role_id AND r.tenant_no = rm.tenant_no AND r.del_flag = 0
JOIN sys_menu AS m ON m.id = rm.menu_id AND m.tenant_no = rm.tenant_no AND m.del_flag = 0
WHERE m.perms = 'cell-storage:sample-info:storage-cert-qc-acl'
  AND rm.del_flag = 0
ORDER BY r.tenant_no, r.role_name;