antissoft / lvqianmeiye_ERP

-- =============================================================================
-- 员工到院转化：App 到院登记 + 管理后台到院转化统计
-- 说明：
--   1. App 菜单挂在 app首页 > 业务操作，沿用 App 菜单的前导斜杠 URL 习惯。
--   2. 后台菜单挂在 报表中心 > 客户统计。
--   3. App 菜单授权继承同时拥有 app首页、业务操作的角色；Web 菜单授权继承客户统计角色。
--   4. 新增角色后，系统不会自动继承本脚本产生的授权，须在授予父菜单时同步重跑本脚本。
--   5. 菜单、按钮、角色授权均按业务键判断，脚本可重复执行。
-- =============================================================================

SET @AppModuleId = 'member-arrival-app-lq';
SET @WebModuleId = 'member-arrival-web-lq';
SET @AdminRoleId = '94e3a9bb0fce4547886972998fddba1c';

SELECT F_Id INTO @AppParentId
FROM BASE_MODULE
WHERE F_FullName = '业务操作'
  AND F_Category = 'App'
  AND F_DeleteMark IS NULL
  AND F_ParentId IN (
    SELECT F_Id
    FROM BASE_MODULE
    WHERE F_FullName = 'app首页'
      AND F_Category = 'App'
      AND F_DeleteMark IS NULL
  )
LIMIT 1;

SELECT F_Id INTO @WebParentId
FROM BASE_MODULE
WHERE F_FullName = '客户统计'
  AND F_Category = 'Web'
  AND F_DeleteMark IS NULL
  AND F_ParentId IN (
    SELECT F_Id
    FROM BASE_MODULE
    WHERE F_FullName = '报表中心'
      AND F_Category = 'Web'
      AND F_DeleteMark IS NULL
  )
LIMIT 1;

-- App：到院登记
INSERT INTO BASE_MODULE (
  F_Id, F_ParentId, F_Type, F_FullName, F_EnCode, F_UrlAddress, F_Icon,
  F_SortCode, F_EnabledMark, F_Category, F_DeleteMark, F_LinkTarget,
  F_PropertyJson, F_IsButtonAuthorize, F_IsColumnAuthorize, F_IsDataAuthorize,
  F_IsFormAuthorize, F_CreatorTime, F_CreatorUserId
)
SELECT
  @AppModuleId, @AppParentId, 2, '到院登记', 'memberArrivalApp',
  '/pagesA/member-arrival/member-arrival', 'icon-ym icon-ym-generator-steps',
  7, 1, 'App', NULL, '_self',
  '{"moduleId":"","iconBackgroundColor":"","isTree":0}',
  0, 0, 0, 0, NOW(), 'admin'
FROM (SELECT 1) AS seed
WHERE @AppParentId IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_MODULE
    WHERE F_EnCode = 'memberArrivalApp'
      AND F_Category = 'App'
      AND F_DeleteMark IS NULL
  );

SELECT F_Id INTO @ActualAppModuleId
FROM BASE_MODULE
WHERE F_EnCode = 'memberArrivalApp'
  AND F_Category = 'App'
  AND F_DeleteMark IS NULL
LIMIT 1;

UPDATE BASE_MODULE
SET F_ParentId = @AppParentId,
    F_Type = 2,
    F_FullName = '到院登记',
    F_UrlAddress = '/pagesA/member-arrival/member-arrival',
    F_Icon = 'icon-ym icon-ym-generator-steps',
    F_SortCode = 7,
    F_EnabledMark = 1,
    F_LastModifyTime = NOW(),
    F_LastModifyUserId = 'admin'
WHERE F_Id = @ActualAppModuleId
  AND @AppParentId IS NOT NULL;

-- Web：到院转化统计
INSERT INTO BASE_MODULE (
  F_Id, F_ParentId, F_Type, F_FullName, F_EnCode, F_UrlAddress, F_Icon,
  F_SortCode, F_EnabledMark, F_Category, F_DeleteMark, F_LinkTarget,
  F_PropertyJson, F_IsButtonAuthorize, F_IsColumnAuthorize, F_IsDataAuthorize,
  F_IsFormAuthorize, F_CreatorTime, F_CreatorUserId
)
SELECT
  @WebModuleId, @WebParentId, 2, '到院转化统计', 'memberArrival',
  'permission/member-arrival', 'icon-ym icon-ym-generator-steps',
  0, 1, 'Web', NULL, '_self',
  '{"moduleId":"","iconBackgroundColor":"","isTree":0}',
  1, 0, 1, 0, NOW(), 'admin'
FROM (SELECT 1) AS seed
WHERE @WebParentId IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_MODULE
    WHERE F_EnCode = 'memberArrival'
      AND F_Category = 'Web'
      AND F_DeleteMark IS NULL
  );

SELECT F_Id INTO @ActualWebModuleId
FROM BASE_MODULE
WHERE F_EnCode = 'memberArrival'
  AND F_Category = 'Web'
  AND F_DeleteMark IS NULL
LIMIT 1;

UPDATE BASE_MODULE
SET F_ParentId = @WebParentId,
    F_Type = 2,
    F_FullName = '到院转化统计',
    F_UrlAddress = 'permission/member-arrival',
    F_Icon = 'icon-ym icon-ym-generator-steps',
    F_SortCode = 0,
    F_EnabledMark = 1,
    F_IsButtonAuthorize = 1,
    F_IsDataAuthorize = 1,
    F_LastModifyTime = NOW(),
    F_LastModifyUserId = 'admin'
WHERE F_Id = @ActualWebModuleId
  AND @WebParentId IS NOT NULL;

-- 后台按钮权限。仅用于后续 v-hasPermi 精细控制；页面未使用权限指令时不影响现有可用性。
INSERT INTO BASE_MODULEBUTTON (
  F_Id, F_ParentId, F_FullName, F_EnCode, F_Icon, F_UrlAddress,
  F_PropertyJson, F_Description, F_SortCode, F_EnabledMark,
  F_CreatorTime, F_CreatorUserId, F_ModuleId
)
SELECT button_id, '-1', button_name, button_code, button_icon, button_url,
       NULL, button_desc, sort_code, 1, NOW(), 'admin', @ActualWebModuleId
FROM (
  SELECT 'member-arrival-btn-view-lq' AS button_id, '查看' AS button_name,
         'btn_view' AS button_code, 'icon-ym icon-ym-btn-preview' AS button_icon,
         NULL AS button_url, '查看到院记录及统计' AS button_desc, 1 AS sort_code
  UNION ALL
  SELECT 'member-arrival-btn-add-lq', '补录', 'btn_add',
         'icon-ym icon-ym-btn-add', NULL, '补录到院登记', 2
  UNION ALL
  SELECT 'member-arrival-btn-void-lq', '作废', 'btn_cancel',
         'icon-ym icon-ym-btn-delete', NULL, '作废到院登记', 3
  UNION ALL
  SELECT 'member-arrival-btn-export-lq', '导出', 'btn_export',
         'icon-ym icon-ym-btn-download', NULL, '导出到院记录', 4
) buttons
WHERE @ActualWebModuleId IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_MODULEBUTTON b
    WHERE b.F_ModuleId = @ActualWebModuleId
      AND b.F_EnCode = buttons.button_code
      AND b.F_DeleteMark IS NULL
  );

-- App 菜单：仅继承同时拥有 app首页和业务操作的有效角色。
-- 不给完全没有 App 父级权限的角色越权新增 App 入口。
INSERT INTO BASE_AUTHORIZE (
  F_Id, F_ItemType, F_ItemId, F_ObjectType, F_ObjectId,
  F_SortCode, F_CreatorTime, F_CreatorUserId
)
SELECT LEFT(SHA2(CONCAT('member-arrival:app-module:', parent_auth.F_ObjectId), 256), 50),
       'module', @ActualAppModuleId, 'Role', parent_auth.F_ObjectId, 1, NOW(), 'admin'
FROM BASE_AUTHORIZE parent_auth
JOIN BASE_ROLE role_info
  ON role_info.F_Id = parent_auth.F_ObjectId
 AND role_info.F_DeleteMark IS NULL
 AND role_info.F_EnabledMark = 1
WHERE @ActualAppModuleId IS NOT NULL
  AND parent_auth.F_ItemType = 'module'
  AND parent_auth.F_ObjectType = 'Role'
  AND parent_auth.F_ItemId = @AppParentId
  AND EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE root_auth
    JOIN BASE_MODULE app_root
      ON app_root.F_Id = root_auth.F_ItemId
     AND app_root.F_FullName = 'app首页'
     AND app_root.F_Category = 'App'
     AND app_root.F_DeleteMark IS NULL
    WHERE root_auth.F_ItemType = 'module'
      AND root_auth.F_ObjectType = 'Role'
      AND root_auth.F_ObjectId = parent_auth.F_ObjectId
  )
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE a
    WHERE a.F_ItemType = 'module'
      AND a.F_ItemId = @ActualAppModuleId
      AND a.F_ObjectType = 'Role'
      AND a.F_ObjectId = parent_auth.F_ObjectId
  );

-- Web 菜单：继承客户统计父菜单的全部有效角色。
INSERT INTO BASE_AUTHORIZE (
  F_Id, F_ItemType, F_ItemId, F_ObjectType, F_ObjectId,
  F_SortCode, F_CreatorTime, F_CreatorUserId
)
SELECT LEFT(SHA2(CONCAT('member-arrival:web-module:', parent_auth.F_ObjectId), 256), 50),
       'module', @ActualWebModuleId, 'Role', parent_auth.F_ObjectId, 1, NOW(), 'admin'
FROM BASE_AUTHORIZE parent_auth
JOIN BASE_ROLE role_info
  ON role_info.F_Id = parent_auth.F_ObjectId
 AND role_info.F_DeleteMark IS NULL
 AND role_info.F_EnabledMark = 1
WHERE @ActualWebModuleId IS NOT NULL
  AND parent_auth.F_ItemType = 'module'
  AND parent_auth.F_ObjectType = 'Role'
  AND parent_auth.F_ItemId = @WebParentId
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE a
    WHERE a.F_ItemType = 'module'
      AND a.F_ItemId = @ActualWebModuleId
      AND a.F_ObjectType = 'Role'
      AND a.F_ObjectId = parent_auth.F_ObjectId
  );

-- 查看：所有拥有到院转化统计页面的角色均可查看。
INSERT INTO BASE_AUTHORIZE (
  F_Id, F_ItemType, F_ItemId, F_ObjectType, F_ObjectId,
  F_SortCode, F_CreatorTime, F_CreatorUserId
)
SELECT LEFT(SHA2(CONCAT('member-arrival:view:', page_auth.F_ObjectId), 256), 50),
       'button', b.F_Id, 'Role', page_auth.F_ObjectId, b.F_SortCode, NOW(), 'admin'
FROM BASE_MODULEBUTTON b
JOIN BASE_AUTHORIZE page_auth
  ON page_auth.F_ItemType = 'module'
 AND page_auth.F_ItemId = @ActualWebModuleId
 AND page_auth.F_ObjectType = 'Role'
WHERE b.F_ModuleId = @ActualWebModuleId
  AND b.F_DeleteMark IS NULL
  AND b.F_EnCode = 'btn_view'
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE a
    WHERE a.F_ItemType = 'button'
      AND a.F_ItemId = b.F_Id
      AND a.F_ObjectType = 'Role'
      AND a.F_ObjectId = page_auth.F_ObjectId
  );

-- 补录：仅继承客户资料管理“新增”权限的页面角色，避免给纯统计只读角色。
INSERT INTO BASE_AUTHORIZE (
  F_Id, F_ItemType, F_ItemId, F_ObjectType, F_ObjectId,
  F_SortCode, F_CreatorTime, F_CreatorUserId
)
SELECT LEFT(SHA2(CONCAT('member-arrival:add:', page_auth.F_ObjectId), 256), 50),
       'button', target_button.F_Id, 'Role', page_auth.F_ObjectId,
       target_button.F_SortCode, NOW(), 'admin'
FROM BASE_AUTHORIZE page_auth
JOIN BASE_MODULEBUTTON target_button
  ON target_button.F_ModuleId = @ActualWebModuleId
 AND target_button.F_EnCode = 'btn_add'
 AND target_button.F_DeleteMark IS NULL
WHERE page_auth.F_ItemType = 'module'
  AND page_auth.F_ItemId = @ActualWebModuleId
  AND page_auth.F_ObjectType = 'Role'
  AND EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE source_auth
    JOIN BASE_MODULEBUTTON source_button
      ON source_button.F_Id = source_auth.F_ItemId
     AND source_button.F_EnCode = 'btn_add'
     AND source_button.F_DeleteMark IS NULL
    JOIN BASE_MODULE source_module
      ON source_module.F_Id = source_button.F_ModuleId
     AND source_module.F_FullName = '客户资料管理'
     AND source_module.F_DeleteMark IS NULL
    WHERE source_auth.F_ItemType = 'button'
      AND source_auth.F_ObjectType = 'Role'
      AND source_auth.F_ObjectId = page_auth.F_ObjectId
  )
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE a
    WHERE a.F_ItemType = 'button'
      AND a.F_ItemId = target_button.F_Id
      AND a.F_ObjectType = 'Role'
      AND a.F_ObjectId = page_auth.F_ObjectId
  );

-- 导出：仅继承系统中已有导出/下载权限的页面角色（当前数据为管理员、财务部）。
INSERT INTO BASE_AUTHORIZE (
  F_Id, F_ItemType, F_ItemId, F_ObjectType, F_ObjectId,
  F_SortCode, F_CreatorTime, F_CreatorUserId
)
SELECT LEFT(SHA2(CONCAT('member-arrival:export:', page_auth.F_ObjectId), 256), 50),
       'button', target_button.F_Id, 'Role', page_auth.F_ObjectId,
       target_button.F_SortCode, NOW(), 'admin'
FROM BASE_AUTHORIZE page_auth
JOIN BASE_MODULEBUTTON target_button
  ON target_button.F_ModuleId = @ActualWebModuleId
 AND target_button.F_EnCode = 'btn_export'
 AND target_button.F_DeleteMark IS NULL
WHERE page_auth.F_ItemType = 'module'
  AND page_auth.F_ItemId = @ActualWebModuleId
  AND page_auth.F_ObjectType = 'Role'
  AND EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE source_auth
    JOIN BASE_MODULEBUTTON source_button
      ON source_button.F_Id = source_auth.F_ItemId
     AND source_button.F_DeleteMark IS NULL
     AND source_button.F_ModuleId <> @ActualWebModuleId
     AND (source_button.F_FullName = '导出' OR source_button.F_EnCode = 'btn_download')
    WHERE source_auth.F_ItemType = 'button'
      AND source_auth.F_ObjectType = 'Role'
      AND source_auth.F_ObjectId = page_auth.F_ObjectId
  )
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE a
    WHERE a.F_ItemType = 'button'
      AND a.F_ItemId = target_button.F_Id
      AND a.F_ObjectType = 'Role'
      AND a.F_ObjectId = page_auth.F_ObjectId
  );

-- 作废：仅继承已有其他模块作废权限；若无来源权限则仅管理员兜底。
INSERT INTO BASE_AUTHORIZE (
  F_Id, F_ItemType, F_ItemId, F_ObjectType, F_ObjectId,
  F_SortCode, F_CreatorTime, F_CreatorUserId
)
SELECT LEFT(SHA2(CONCAT('member-arrival:cancel:', page_auth.F_ObjectId), 256), 50),
       'button', target_button.F_Id, 'Role', page_auth.F_ObjectId,
       target_button.F_SortCode, NOW(), 'admin'
FROM BASE_AUTHORIZE page_auth
JOIN BASE_MODULEBUTTON target_button
  ON target_button.F_ModuleId = @ActualWebModuleId
 AND target_button.F_EnCode = 'btn_cancel'
 AND target_button.F_DeleteMark IS NULL
WHERE page_auth.F_ItemType = 'module'
  AND page_auth.F_ItemId = @ActualWebModuleId
  AND page_auth.F_ObjectType = 'Role'
  AND EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE source_auth
    JOIN BASE_MODULEBUTTON source_button
      ON source_button.F_Id = source_auth.F_ItemId
     AND source_button.F_DeleteMark IS NULL
     AND source_button.F_ModuleId <> @ActualWebModuleId
     AND (source_button.F_FullName = '作废' OR source_button.F_EnCode = 'btn_cancel')
    WHERE source_auth.F_ItemType = 'button'
      AND source_auth.F_ObjectType = 'Role'
      AND source_auth.F_ObjectId = page_auth.F_ObjectId
  )
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE a
    WHERE a.F_ItemType = 'button'
      AND a.F_ItemId = target_button.F_Id
      AND a.F_ObjectType = 'Role'
      AND a.F_ObjectId = page_auth.F_ObjectId
  );

-- 管理员兜底：两个菜单和四个按钮必须完整授权。
INSERT INTO BASE_AUTHORIZE (
  F_Id, F_ItemType, F_ItemId, F_ObjectType, F_ObjectId,
  F_SortCode, F_CreatorTime, F_CreatorUserId
)
SELECT LEFT(SHA2(CONCAT('member-arrival:admin-module:', module_id), 256), 50),
       'module', module_id, 'Role', @AdminRoleId, sort_code, NOW(), 'admin'
FROM (
  SELECT @ActualAppModuleId AS module_id, 1 AS sort_code
  UNION ALL
  SELECT @ActualWebModuleId, 2
) modules
WHERE module_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE a
    WHERE a.F_ItemType = 'module'
      AND a.F_ItemId = modules.module_id
      AND a.F_ObjectType = 'Role'
      AND a.F_ObjectId = @AdminRoleId
  );

INSERT INTO BASE_AUTHORIZE (
  F_Id, F_ItemType, F_ItemId, F_ObjectType, F_ObjectId,
  F_SortCode, F_CreatorTime, F_CreatorUserId
)
SELECT LEFT(SHA2(CONCAT('member-arrival:admin-button:', b.F_Id), 256), 50),
       'button', b.F_Id, 'Role', @AdminRoleId, b.F_SortCode, NOW(), 'admin'
FROM BASE_MODULEBUTTON b
WHERE b.F_ModuleId = @ActualWebModuleId
  AND b.F_DeleteMark IS NULL
  AND b.F_EnCode IN ('btn_view', 'btn_add', 'btn_cancel', 'btn_export')
  AND NOT EXISTS (
    SELECT 1
    FROM BASE_AUTHORIZE a
    WHERE a.F_ItemType = 'button'
      AND a.F_ItemId = b.F_Id
      AND a.F_ObjectType = 'Role'
      AND a.F_ObjectId = @AdminRoleId
  );