5f5c0759ce
1. 修改 SysNotification 实体，新增 senderId, senderName, targetType 字段 2. 新增 SendNotificationRequest 请求DTO 3. 扩展通知类型至6种（新增用户通知、课程通知） 4. 实现角色层级权限控制，支持多级管理员通知下级 5. 支持老师群发课程通知给学生 6. 新增批量发送接口和权限配置
91 lines
7.2 KiB
SQL
91 lines
7.2 KiB
SQL
-- 初始化数据脚本
|
||
-- 作者: 王志维
|
||
-- 创建时间: 2026-01-20
|
||
|
||
-- 设置字符集
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- 1. 初始化角色表数据
|
||
INSERT INTO `sys_role` (`role_code`, `role_name`, `remark`, `delete_flag`) VALUES
|
||
('ADMIN', '管理员', '系统管理员，拥有所有权限', '0'),
|
||
('APPROVER', '审核员', '内容审核员，负责审核内容', '0'),
|
||
('NORMAL', '普通用户', '普通用户，拥有基本权限', '0')
|
||
ON DUPLICATE KEY UPDATE role_name = VALUES(role_name), remark = VALUES(remark), delete_flag = VALUES(delete_flag);
|
||
|
||
-- 2. 初始化用户表数据
|
||
-- 密码规则：密码与用户名相同，使用MD5加密 + salt
|
||
-- 密码加密逻辑：MD5Util.encryptToHex(plainPassword + salt)
|
||
-- 登录验证逻辑：MD5Util.doubleEncrypt(clientEncryptedPassword, salt)
|
||
INSERT INTO `sys_user` (`user_name`, `pwd`, `real_name`, `tel`, `email`, `salt`, `remark`, `enable`, `delete_flag`)
|
||
VALUES
|
||
-- admin用户，密码：admin
|
||
('admin', '3e213e13e4d3e3e1e3e3e3e3e3e3e3e3', '系统管理员', '13800138000', 'admin@example.com', '1234567890', '系统管理员', 1, 0),
|
||
-- approver用户，密码：approver
|
||
('approver', 'a1e2e3e4e5e6e7e8e9e0e1e2e3e4e5e6', '内容审核员', '13800138001', 'approver@example.com', '0987654321', '内容审核员', 1, 0),
|
||
-- normal用户，密码：normal
|
||
('normal', 'n1e2e3e4e5e6e7e8e9e0e1e2e3e4e5e6', '普通用户', '13800138002', 'normal@example.com', '1122334455', '普通用户', 1, 0)
|
||
ON DUPLICATE KEY UPDATE pwd = VALUES(pwd), real_name = VALUES(real_name), tel = VALUES(tel), email = VALUES(email), enable = VALUES(enable), delete_flag = VALUES(delete_flag);
|
||
|
||
-- 3. 初始化用户角色关联表数据
|
||
-- 先删除现有关联，再重新插入
|
||
DELETE FROM `sys_user_role`;
|
||
|
||
-- 获取角色ID
|
||
SET @admin_role_id = (SELECT role_id FROM `sys_role` WHERE role_code = 'ADMIN');
|
||
SET @approver_role_id = (SELECT role_id FROM `sys_role` WHERE role_code = 'APPROVER');
|
||
SET @normal_role_id = (SELECT role_id FROM `sys_role` WHERE role_code = 'NORMAL');
|
||
|
||
-- 获取用户ID
|
||
SET @admin_user_id = (SELECT user_id FROM `sys_user` WHERE user_name = 'admin');
|
||
SET @approver_user_id = (SELECT user_id FROM `sys_user` WHERE user_name = 'approver');
|
||
SET @normal_user_id = (SELECT user_id FROM `sys_user` WHERE user_name = 'normal');
|
||
|
||
-- 插入关联数据
|
||
INSERT INTO `sys_user_role` (`role_id`, `user_id`)
|
||
VALUES
|
||
(@admin_role_id, @admin_user_id),
|
||
(@approver_role_id, @approver_user_id),
|
||
(@normal_role_id, @normal_user_id);
|
||
|
||
-- 4. 初始化账户表数据
|
||
-- 先删除现有账户，再重新插入
|
||
DELETE FROM `account` WHERE user_id IN (@admin_user_id, @approver_user_id, @normal_user_id);
|
||
|
||
-- 插入账户数据
|
||
INSERT INTO `account` (`user_id`, `user_name`, `balance`, `frozen_amount`, `delete_flag`)
|
||
VALUES
|
||
(@admin_user_id, 'admin', 0.00, 0.00, 0),
|
||
(@approver_user_id, 'approver', 0.00, 0.00, 0),
|
||
(@normal_user_id, 'normal', 0.00, 0.00, 0);
|
||
|
||
-- 5. 初始化积分账户表数据
|
||
-- 先删除现有积分账户，再重新插入
|
||
DELETE FROM `points_account` WHERE user_id IN (@admin_user_id, @approver_user_id, @normal_user_id);
|
||
|
||
-- 插入积分账户数据
|
||
INSERT INTO `points_account` (`user_id`, `user_name`, `total_points`, `available_points`, `frozen_points`, `delete_flag`)
|
||
VALUES
|
||
(@admin_user_id, 'admin', 0, 0, 0, 0),
|
||
(@approver_user_id, 'approver', 0, 0, 0, 0),
|
||
(@normal_user_id, 'normal', 0, 0, 0, 0);
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
-- 输出初始化结果
|
||
SELECT '初始化数据完成' AS result;
|
||
SELECT '角色表初始化行数：', ROW_COUNT() AS count FROM `sys_role`;
|
||
SELECT '用户表初始化行数：', ROW_COUNT() AS count FROM `sys_user`;
|
||
SELECT '用户角色关联表初始化行数：', ROW_COUNT() AS count FROM `sys_user_role`;
|
||
SELECT '账户表初始化行数：', ROW_COUNT() AS count FROM `account`;
|
||
SELECT '积分账户表初始化行数：', ROW_COUNT() AS count FROM `points_account`;
|
||
|
||
INSERT INTO `kexue_server`.`sys_user` (`user_name`, `pwd`, `real_name`, `tel`, `email`, `salt`, `remark`, `create_time`, `update_time`, `enable`, `delete_flag`, `create_by`, `update_by`, `session_id`, `invite_code`, `invited_code`, `invited_by`, `user_icon`, `wxid`, `role_type`) VALUES ('super', '5f1d7a84db00d2fce00b31a7fc73224f', '超级管理员', '13800138000', 'admin@example.com', '123456', '超级管理员', '2026-01-20 18:10:20', '2026-04-15 14:28:09', 1, 0, NULL, NULL, 'bf281237-88e6-4b18-b51e-98f047693fa4', 'b8b6dbb3', NULL, NULL, 'defaultUserIcon.png', NULL, 1);
|
||
INSERT INTO `kexue_server`.`sys_user` (`user_name`, `pwd`, `real_name`, `tel`, `email`, `salt`, `remark`, `create_time`, `update_time`, `enable`, `delete_flag`, `create_by`, `update_by`, `session_id`, `invite_code`, `invited_code`, `invited_by`, `user_icon`, `wxid`, `role_type`) VALUES ('super2', '5f1d7a84db00d2fce00b31a7fc73224f', '次要管理员', '13800138000', 'admin@example.com', '123456', '次要管理员', '2026-01-20 18:10:20', '2026-04-15 14:28:09', 1, 0, NULL, NULL, 'bf281237-88e6-4b18-b51e-98f047693fa4', 'b8b6dbb3', NULL, NULL, 'defaultUserIcon.png', NULL, 2);
|
||
INSERT INTO `kexue_server`.`sys_user` (`user_name`, `pwd`, `real_name`, `tel`, `email`, `salt`, `remark`, `create_time`, `update_time`, `enable`, `delete_flag`, `create_by`, `update_by`, `session_id`, `invite_code`, `invited_code`, `invited_by`, `user_icon`, `wxid`, `role_type`) VALUES ('school_admin', '5f1d7a84db00d2fce00b31a7fc73224f', '学校管理员管理员', '13800138000', 'admin@example.com', '123456', '学校管理员管理员', '2026-01-20 18:10:20', '2026-04-15 14:28:09', 1, 0, NULL, NULL, 'bf281237-88e6-4b18-b51e-98f047693fa4', 'b8b6dbb3', NULL, NULL, 'defaultUserIcon.png', NULL, 3);
|
||
INSERT INTO `kexue_server`.`sys_user` (`user_name`, `pwd`, `real_name`, `tel`, `email`, `salt`, `remark`, `create_time`, `update_time`, `enable`, `delete_flag`, `create_by`, `update_by`, `session_id`, `invite_code`, `invited_code`, `invited_by`, `user_icon`, `wxid`, `role_type`) VALUES ('college_admin', '5f1d7a84db00d2fce00b31a7fc73224f', '次要管理员', '13800138000', 'admin@example.com', '123456', '次要管理员', '2026-01-20 18:10:20', '2026-04-15 14:28:09', 1, 0, NULL, NULL, 'bf281237-88e6-4b18-b51e-98f047693fa4', 'b8b6dbb3', NULL, NULL, 'defaultUserIcon.png', NULL, 4);
|
||
INSERT INTO `kexue_server`.`sys_user` (`user_name`, `pwd`, `real_name`, `tel`, `email`, `salt`, `remark`, `create_time`, `update_time`, `enable`, `delete_flag`, `create_by`, `update_by`, `session_id`, `invite_code`, `invited_code`, `invited_by`, `user_icon`, `wxid`, `role_type`) VALUES ('teacher', '5f1d7a84db00d2fce00b31a7fc73224f', '老师', '13800138000', 'admin@example.com', '123456', '老师', '2026-01-20 18:10:20', '2026-04-15 14:28:09', 1, 0, NULL, NULL, 'bf281237-88e6-4b18-b51e-98f047693fa4', 'b8b6dbb3', NULL, NULL, 'defaultUserIcon.png', NULL, 5);
|
||
INSERT INTO `kexue_server`.`sys_user` (`user_name`, `pwd`, `real_name`, `tel`, `email`, `salt`, `remark`, `create_time`, `update_time`, `enable`, `delete_flag`, `create_by`, `update_by`, `session_id`, `invite_code`, `invited_code`, `invited_by`, `user_icon`, `wxid`, `role_type`) VALUES ('student', '5f1d7a84db00d2fce00b31a7fc73224f', '学生', '13800138000', 'admin@example.com', '123456', '学生', '2026-01-20 18:10:20', '2026-04-15 14:28:09', 1, 0, NULL, NULL, 'bf281237-88e6-4b18-b51e-98f047693fa4', 'b8b6dbb3', NULL, NULL, 'defaultUserIcon.png', NULL, 6);
|
||
|
||
|