-- ============================================================
|
-- CRM 开票导入 菜单与按钮权限
|
--
|
-- 背景：开票管理新增 Excel 批量导入能力
|
-- （后端接口 GET /crm/invoice/get-import-template 与 POST /crm/invoice/import），
|
-- 需要在开票列表页暴露「导入」按钮权限，否则前端按钮会被 @PreAuthorize 拦截。
|
--
|
-- 新增按钮权限：crm:invoice:import（开票列表「导入」按钮）
|
--
|
-- ID 自适应：本脚本不写死菜单 ID
|
-- ① 菜单 ID 由 system_menu 自增分配（不指定 id 列）；
|
-- ② 父菜单按权限码反查开票管理菜单（crm:invoice:export -> 其 parent_id），
|
-- 查不到时依次回退 crm:invoice:query、名称「开票管理」的 type=2 菜单；
|
-- ③ 角色授权跟随开票管理「导出」按钮已有的角色，不写死 role_id。
|
--
|
-- 幂等：按权限码先删后插，可重复执行。
|
--
|
-- 执行方式（PowerShell，避免中文乱码）：
|
-- mysql -uroot -p1234 --default-character-set=utf8mb4 ruoyi-vue-pro < docs/sql/crm_invoice_import_menu.sql
|
-- ============================================================
|
|
SET NAMES utf8mb4;
|
|
-- ------------------------------------------------------------
|
-- 0. 幂等清理
|
-- ------------------------------------------------------------
|
DELETE rm FROM `system_role_menu` rm
|
JOIN `system_menu` m ON rm.`menu_id` = m.`id`
|
WHERE m.`permission` = 'crm:invoice:import';
|
|
DELETE FROM `system_menu` WHERE `permission` = 'crm:invoice:import';
|
|
-- ------------------------------------------------------------
|
-- 1. 父菜单：开票管理菜单（type=2）
|
-- ------------------------------------------------------------
|
SET @invoice_menu_id = COALESCE(
|
(SELECT `parent_id` FROM `system_menu` WHERE `permission` = 'crm:invoice:export' AND `deleted` = b'0' LIMIT 1),
|
(SELECT `parent_id` FROM `system_menu` WHERE `permission` = 'crm:invoice:query' AND `deleted` = b'0' LIMIT 1),
|
(SELECT `id` FROM `system_menu` WHERE `name` = '开票管理' AND `type` = 2 AND `deleted` = b'0' LIMIT 1));
|
|
-- ------------------------------------------------------------
|
-- 2. 新增按钮（菜单 ID 自增）
|
-- ------------------------------------------------------------
|
INSERT INTO `system_menu` (`name`, `permission`, `type`, `sort`, `parent_id`, `path`, `icon`, `component`, `component_name`, `status`, `visible`, `keep_alive`, `always_show`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
|
SELECT '开票导入', 'crm:invoice:import', 3, 5, @invoice_menu_id, '', '', '', '', 0, b'1', b'1', b'1', '1', NOW(), '1', NOW(), b'0'
|
WHERE @invoice_menu_id IS NOT NULL;
|
SET @menu_invoice_import = IF(ROW_COUNT() > 0, LAST_INSERT_ID(), NULL);
|
|
-- ------------------------------------------------------------
|
-- 3. 授权：跟随开票管理「导出」按钮已有的角色（兜底「查询」按钮）
|
-- ------------------------------------------------------------
|
INSERT INTO `system_role_menu` (`role_id`, `menu_id`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
|
SELECT DISTINCT rm.`role_id`, @menu_invoice_import, '1', NOW(), '1', NOW(), b'0'
|
FROM `system_role_menu` rm
|
WHERE rm.`menu_id` = COALESCE(
|
(SELECT `id` FROM `system_menu` WHERE `permission` = 'crm:invoice:export' AND `deleted` = b'0' LIMIT 1),
|
(SELECT `id` FROM `system_menu` WHERE `permission` = 'crm:invoice:query' AND `deleted` = b'0' LIMIT 1))
|
AND rm.`deleted` = b'0'
|
AND @menu_invoice_import IS NOT NULL;
|
|
-- ------------------------------------------------------------
|
-- 4. 校验：菜单应返回 1 行；授权行数与「开票管理导出」按钮一致
|
-- ------------------------------------------------------------
|
SELECT '菜单 crm:invoice:import ' AS item, COUNT(*) AS cnt
|
FROM `system_menu` WHERE `permission` = 'crm:invoice:import' AND `deleted` = b'0'
|
UNION ALL SELECT '授权 / 开票导出授权 ', CONCAT(
|
(SELECT COUNT(*) FROM `system_role_menu` rm JOIN `system_menu` m ON rm.`menu_id` = m.`id`
|
WHERE m.`permission` = 'crm:invoice:import' AND rm.`deleted` = b'0'), ' / ',
|
(SELECT COUNT(*) FROM `system_role_menu` rm JOIN `system_menu` m ON rm.`menu_id` = m.`id`
|
WHERE m.`permission` = 'crm:invoice:export' AND rm.`deleted` = b'0'));
|
