hzdyjs/退学、休学、复学、毕业/07-快速执行脚本.md

07-快速执行脚本

📋 概述

本分文档提供快速执行的简化SQL脚本，适用于紧急处理或批量操作场景。这些脚本去除了复杂的事务处理和日志记录，专注于核心数据更新操作。

⚡ 快速执行脚本

休学处理快速脚本

-- 休学处理：禁用账号，暂停组织关系
SET @student_id = 123456789;
SET @student_no = '20240001';
SET @operator_id = 1;

-- 备份原始数据
INSERT INTO xg_student_msg_his SELECT * FROM xg_student_msg WHERE id = @student_id;
INSERT INTO sys_user_his SELECT * FROM sys_user WHERE user_id = @student_id;
INSERT INTO xg_class_pos_msg_his SELECT * FROM xg_class_pos_msg WHERE student_id = @student_id;
INSERT INTO stgl_member_his SELECT * FROM stgl_member WHERE user_id = @student_id;

-- 更新核心状态
UPDATE xg_student_msg SET student_status = '2', update_by = @operator_id, update_time = NOW() WHERE id = @student_id;
UPDATE sys_user SET status = '1' WHERE user_id = @student_id AND account_type = '2';

-- 处理组织关系
UPDATE xg_class_pos_msg SET is_current = 0, cadre_status = '6', dismissal_date = CURDATE(), dismissal_reason = '学生休学' WHERE student_id = @student_id AND is_current = 1;
UPDATE club_member SET member_status = '4' WHERE user_id = @student_id AND member_status IN ('0', '1', '2');
UPDATE stgl_member SET status = 4 WHERE user_id = @student_id AND status IN (0, 1, 2);

退学处理快速脚本

-- 退学处理：禁用账号，终止组织关系和住宿
SET @student_id = 123456789;
SET @student_no = '20240001';
SET @operator_id = 1;

-- 备份原始数据
INSERT INTO xg_student_msg_his SELECT * FROM xg_student_msg WHERE id = @student_id;
INSERT INTO sys_user_his SELECT * FROM sys_user WHERE user_id = @student_id;
INSERT INTO xg_class_pos_msg_his SELECT * FROM xg_class_pos_msg WHERE student_id = @student_id;
INSERT INTO stgl_member_his SELECT * FROM stgl_member WHERE user_id = @student_id;
INSERT INTO xg_dorm_bed_student_his SELECT * FROM xg_dorm_bed_student WHERE student_id = @student_id;

-- 更新核心状态
UPDATE xg_student_msg SET student_status = '4', update_by = @operator_id, update_time = NOW() WHERE id = @student_id;
UPDATE sys_user SET status = '1' WHERE user_id = @student_id AND account_type = '2';

-- 处理组织关系
UPDATE xg_class_pos_msg SET is_current = 0, cadre_status = '4', dismissal_date = CURDATE(), dismissal_reason = '学生退学' WHERE student_id = @student_id AND is_current = 1;
UPDATE club_member SET member_status = '4' WHERE user_id = @student_id AND member_status IN ('0', '1', '2');
UPDATE stgl_member SET status = 4 WHERE user_id = @student_id AND status IN (0, 1, 2);

-- 处理住宿关系
UPDATE xg_student_msg SET dormitory_building = NULL, dormitory_room = NULL, ext_field1 = NULL, update_time = NOW(), update_by = @operator_id WHERE id = @student_id;
UPDATE xg_dorm_bed_student SET status = '1', checkout_time = NOW() WHERE student_id = @student_id AND status = '0';
UPDATE xg_dorm_bed SET bed_status = '0' WHERE id IN (SELECT bed_id FROM xg_dorm_bed_student WHERE student_id = @student_id AND status = '1');

毕业处理快速脚本

-- 毕业处理：禁用账号，终止组织关系和住宿
SET @student_id = 123456789;
SET @student_no = '20240001';
SET @operator_id = 1;

-- 备份原始数据
INSERT INTO xg_student_msg_his SELECT * FROM xg_student_msg WHERE id = @student_id;
INSERT INTO sys_user_his SELECT * FROM sys_user WHERE user_id = @student_id;
INSERT INTO xg_class_pos_msg_his SELECT * FROM xg_class_pos_msg WHERE student_id = @student_id;
INSERT INTO stgl_member_his SELECT * FROM stgl_member WHERE user_id = @student_id;
INSERT INTO xg_dorm_bed_student_his SELECT * FROM xg_dorm_bed_student WHERE student_id = @student_id;

-- 更新核心状态
UPDATE xg_student_msg SET student_status = '5', update_by = @operator_id, update_time = NOW() WHERE id = @student_id;
UPDATE sys_user SET status = '1' WHERE user_id = @student_id AND account_type = '2';

-- 处理组织关系
UPDATE xg_class_pos_msg SET is_current = 0, cadre_status = '4', dismissal_date = CURDATE(), dismissal_reason = '学生毕业' WHERE student_id = @student_id AND is_current = 1;
UPDATE club_member SET member_status = '4' WHERE user_id = @student_id AND member_status IN ('0', '1', '2');
UPDATE stgl_member SET status = 4 WHERE user_id = @student_id AND status IN (0, 1, 2);

-- 处理住宿关系
UPDATE xg_student_msg SET dormitory_building = NULL, dormitory_room = NULL, ext_field1 = NULL, update_time = NOW(), update_by = @operator_id WHERE id = @student_id;
UPDATE xg_dorm_bed_student SET status = '1', checkout_time = NOW() WHERE student_id = @student_id AND status = '0';
UPDATE xg_dorm_bed SET bed_status = '0' WHERE id IN (SELECT bed_id FROM xg_dorm_bed_student WHERE student_id = @student_id AND status = '1');

复学处理快速脚本

-- 复学处理：启用账号，恢复学籍状态
SET @student_id = 123456789;
SET @student_no = '20240001';
SET @operator_id = 1;

-- 备份原始数据
INSERT INTO xg_student_msg_his SELECT * FROM xg_student_msg WHERE id = @student_id;
INSERT INTO sys_user_his SELECT * FROM sys_user WHERE user_id = @student_id;

-- 更新核心状态
UPDATE xg_student_msg SET student_status = '3', update_by = @operator_id, update_time = NOW() WHERE id = @student_id;
UPDATE sys_user SET status = '0' WHERE user_id = @student_id AND account_type = '2';

🔧 批量处理脚本

批量休学处理

-- 批量休学处理脚本
-- 使用前请将学生信息填入临时表 student_leave_batch

DROP TEMPORARY TABLE IF EXISTS student_leave_batch;
CREATE TEMPORARY TABLE student_leave_batch (
    student_id BIGINT PRIMARY KEY,
    student_no VARCHAR(32),
    leave_date DATE DEFAULT CURDATE()
);

-- 插入待处理的學生信息
-- INSERT INTO student_leave_batch (student_id, student_no) VALUES (123, '20240001'), (456, '20240002');

-- 批量备份原始数据
INSERT INTO xg_student_msg_his SELECT * FROM xg_student_msg WHERE id IN (SELECT student_id FROM student_leave_batch);
INSERT INTO sys_user_his SELECT * FROM sys_user WHERE user_id IN (SELECT student_id FROM student_leave_batch);
INSERT INTO xg_class_pos_msg_his SELECT * FROM xg_class_pos_msg WHERE student_id IN (SELECT student_id FROM student_leave_batch);
INSERT INTO stgl_member_his SELECT * FROM stgl_member WHERE user_id IN (SELECT student_id FROM student_leave_batch);

-- 批量更新学生状态
UPDATE xg_student_msg
SET student_status = '2',
    update_by = 1,
    update_time = NOW()
WHERE id IN (SELECT student_id FROM student_leave_batch)
  AND deleted = 0;

-- 批量禁用用户账号
UPDATE sys_user
SET status = '1',
    update_by = 1,
    update_time = NOW()
WHERE user_id IN (SELECT student_id FROM student_leave_batch)
  AND account_type = '2'
  AND del_flag = '0';

-- 批量处理班级干部
UPDATE xg_class_pos_msg
SET is_current = 0,
    cadre_status = '6',
    dismissal_date = CURDATE(),
    dismissal_reason = '学生休学',
    update_by = 1,
    update_time = NOW()
WHERE student_id IN (SELECT student_id FROM student_leave_batch)
  AND is_current = 1
  AND deleted = 0;

-- 批量处理社团成员
UPDATE club_member
SET member_status = '4',
    update_by = 1,
    update_time = NOW()
WHERE user_id IN (SELECT student_id FROM student_leave_batch)
  AND member_status IN ('0', '1', '2')
  AND deleted = 0;

-- 批量处理志愿者成员
UPDATE stgl_member
SET status = 4,
    update_by = 1,
    update_time = NOW()
WHERE user_id IN (SELECT student_id FROM student_leave_batch)
  AND status IN (0, 1, 2)
  AND deleted = 0;

-- 批量处理奖学金申请
UPDATE jcbt_scholarship_application
SET current_status = 'WITHDRAWN',
    update_by = 1,
    update_time = NOW()
WHERE student_id IN (SELECT student_id FROM student_leave_batch)
  AND current_status IN ('PENDING', 'APPROVED')
  AND deleted = 0;

-- 显示处理结果
SELECT '休学处理完成' as result,
       COUNT(*) as processed_count
FROM student_leave_batch;

⚠️ 使用注意事项

快速脚本特点

• 简化处理: 去除了复杂的事务管理和详细日志记录
• 快速执行: 适用于紧急情况或已知数据环境
• 手动确认: 执行前请仔细检查参数设置
• 备份要求: 重要操作前必须备份数据

参数设置要求

-- 必须设置的参数
SET @student_id = 123456789;    -- 学生ID
SET @student_no = '20240001';   -- 学生学号
SET @operator_id = 1;          -- 操作员ID

-- 可选参数
SET @change_date = '2025-12-24';  -- 变更日期，默认为当前日期

执行顺序建议

1. 备份数据: 执行任何修改前备份相关表
2. 设置参数: 确认所有参数正确设置
3. 逐步执行: 可以分批次执行，便于问题排查
4. 验证结果: 执行后检查各表更新结果
5. 记录日志: 手动记录操作日志

错误处理

• 如果脚本执行失败，回滚事务
• 检查约束冲突和外键关系
• 确认用户权限和数据权限
• 查看数据库错误日志

---

📅 更新记录

• 2025-12-XX: 分文档创建，包含快速执行脚本
• 后续更新: 根据实际业务需求持续完善

---

本分文档提供快速执行的简化脚本，适用于紧急处理场景，使用时请谨慎并做好数据备份。