-- 客户近3年财务报表 + 客户档案按钮权限 -- 执行库:product-inventory-management-xmgl CREATE TABLE IF NOT EXISTS `customer_financial_report` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键', `customer_id` bigint NOT NULL COMMENT '客户id', `report_year` int NOT NULL COMMENT '报表年度', `report_name` varchar(255) DEFAULT NULL COMMENT '报表名称', `report_files` text COMMENT '报表附件(JSON数组)', `upload_user_id` bigint DEFAULT NULL COMMENT '上传人id', `upload_user_name` varchar(100) DEFAULT NULL COMMENT '上传人', `tenant_id` bigint DEFAULT NULL COMMENT '租户id', `dept_id` bigint DEFAULT NULL COMMENT '部门id', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_customer_year` (`customer_id`, `report_year`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户近3年财务报表'; -- 客户档案菜单挂上查询权限 UPDATE `sys_menu` SET `perms` = 'basic:customer:list' WHERE `menu_id` = 2001; -- 按钮权限(F 类型) INSERT IGNORE INTO `sys_menu` (`menu_id`,`menu_name`,`parent_id`,`order_num`,`path`,`component`,`query`,`route_name`,`is_frame`,`is_cache`,`menu_type`,`visible`,`status`,`perms`,`icon`,`create_by`,`create_time`,`remark`) VALUES (2362,'客户新增',2001,2,'',NULL,'','',1,0,'F','0','0','basic:customer:add','#','admin',NOW(),'客户档案按钮权限'), (2363,'客户修改',2001,3,'',NULL,'','',1,0,'F','0','0','basic:customer:edit','#','admin',NOW(),'客户档案按钮权限'), (2364,'客户删除',2001,4,'',NULL,'','',1,0,'F','0','0','basic:customer:remove','#','admin',NOW(),'客户档案按钮权限'), (2365,'客户导入',2001,5,'',NULL,'','',1,0,'F','0','0','basic:customer:import','#','admin',NOW(),'客户档案按钮权限'), (2366,'客户导出',2001,6,'',NULL,'','',1,0,'F','0','0','basic:customer:export','#','admin',NOW(),'客户档案按钮权限'), (2367,'客户详情',2001,7,'',NULL,'','',1,0,'F','0','0','basic:customer:query','#','admin',NOW(),'客户档案按钮权限'), (2368,'近3年财务报表上传',2001,8,'',NULL,'','',1,0,'F','0','0','basic:customer:report','#','admin',NOW(),'客户档案按钮权限'); -- 已有客户档案菜单的角色自动获得按钮权限 INSERT IGNORE INTO `sys_role_menu` (`role_id`,`menu_id`) SELECT rm.role_id, m.menu_id FROM `sys_role_menu` rm JOIN `sys_menu` m ON m.menu_id BETWEEN 2362 AND 2368 WHERE rm.menu_id = 2001; -- 客户门户:我的申报 -> 财务报表上传(仅客户角色可见) UPDATE `sys_menu` SET `menu_name` = '财务报表上传', `path` = 'myReport', `route_name` = 'MyReport', `component` = 'customerPortal/myReport/index', `perms` = 'customer:report:mine', `order_num` = 2 WHERE `menu_id` = 2358; -- 已绑定的客户账号补授“客户”角色(role_key=customer) INSERT IGNORE INTO `sys_user_role` (`user_id`,`role_id`) SELECT DISTINCT cu.user_id, r.role_id FROM `customer_user` cu JOIN `sys_role` r ON r.role_key = 'customer' AND r.del_flag = '0';