all-project/backend/sql/scholarship_test_data.sql

-- ====================================================================
-- 奖学金管理模块 - 测试数据脚本
-- 创建时间: 2025-01-27
-- 说明: 包含奖学金类型、班级、申请记录的测试数据
-- ====================================================================

USE hzdyjs_xg;

-- =====================================================
-- 1. 清理现有测试数据
-- =====================================================
DELETE FROM jcbt_scholarship_application WHERE student_name LIKE '测试学生%';
DELETE FROM jcbt_scholarship_type WHERE type_name LIKE '测试%';
DELETE FROM xg_class_msg WHERE class_name LIKE '测试%';

-- =====================================================
-- 2. 插入奖学金类型测试数据
-- =====================================================
INSERT INTO jcbt_scholarship_type (
    id, type_code, type_name, type_category, level_type, award_amount,
    description, application_guide, application_conditions, evaluation_criteria,
    compatibility_type, sort_order, status, is_system, tenant_id, deleted,
    create_by, create_time, update_by, update_time
) VALUES 
(
    1863768901234567001, 
    'ACADEMIC_EXCELLENCE', 
    '优秀学生奖学金', 
    'ACADEMIC', 
    'FIRST_CLASS', 
    3000.00,
    '奖励品学兼优的学生',
    '申请时需要提交成绩单和获奖证书',
    '学期平均分85分以上，无挂科记录',
    '按成绩排名评定',
    'COMPATIBLE',
    1, 1, 0, '000000', 0,
    1, NOW(), 1, NOW()
),
(
    1863768901234567002, 
    'POVERTY_ASSISTANCE', 
    '贫困助学金', 
    'ASSISTANCE', 
    'GENERAL', 
    2000.00,
    '资助家庭经济困难学生',
    '申请时需要提交家庭经济证明',
    '家庭经济困难，成绩及格以上',
    '按经济困难程度评定',
    'COMPATIBLE',
    2, 1, 0, '000000', 0,
    1, NOW(), 1, NOW()
),
(
    1863768901234567003, 
    'INNOVATION_PRIZE', 
    '创新创业奖学金', 
    'INNOVATION', 
    'SPECIAL', 
    5000.00,
    '奖励在创新创业方面表现突出的学生',
    '申请时需要提交创新项目材料',
    '有创新成果或创业经历',
    '按创新项目质量评定',
    'EXCLUSIVE',
    3, 1, 0, '000000', 0,
    1, NOW(), 1, NOW()
);

-- =====================================================
-- 3. 插入班级测试数据
-- =====================================================
INSERT INTO xg_class_msg (
    id, college_id, college_name, major_id, major_name, 
    class_code, class_name, class_type, grade, enrollment_year, graduation_year,
    class_teacher_id, class_teacher_name, class_status, 
    planned_capacity, current_student_count, tenant_id, deleted,
    create_by, create_time, update_by, update_time
) VALUES 
(
    1863768901234568001,
    100, '计算机学院', 101, '软件工程',
    'CS2021001', '测试班级2021级软件工程1班', '1', '2021', 2021, 2025,
    1001, '张老师', '1',
    30, 28, '000000', 0,
    1, NOW(), 1, NOW()
),
(
    1863768901234568002,
    100, '计算机学院', 101, '软件工程',
    'CS2021002', '测试班级2021级软件工程2班', '1', '2021', 2021, 2025,
    1002, '李老师', '1',
    30, 26, '000000', 0,
    1, NOW(), 1, NOW()
),
(
    1863768901234568003,
    200, '机械学院', 201, '机械设计制造及其自动化',
    'ME2021001', '测试班级2021级机械1班', '1', '2021', 2021, 2025,
    2001, '王老师', '1',
    25, 24, '000000', 0,
    1, NOW(), 1, NOW()
);

-- =====================================================
-- 4. 插入申请记录测试数据
-- =====================================================
INSERT INTO jcbt_scholarship_application (
    id, application_no, batch_id, scholarship_type_id,
    student_no, student_name, student_id, dept_id, major_id, class_id,
    application_type, current_status, apply_reason, self_evaluation,
    calculated_score, final_score, ranking_in_dept, ranking_in_major,
    submit_time, tenant_id, deleted,
    create_by, create_time, update_by, update_time
) VALUES 
(
    1863768901234569001,
    'APPLY20250127001',
    1863768901234560001, -- 假设批次ID
    1863768901234567001, -- 优秀学生奖学金
    '2021001001', '测试学生张三', 1863768901234570001,
    100, 101, 1863768901234568001, -- 计算机学院，软件工程，测试班级1
    'STUDENT', 'SUBMITTED', '学习成绩优异，积极参与社会实践',
    '我在本学期各科成绩均在90分以上，积极参与班级和学院活动，希望能够获得奖学金鼓励。',
    92.5, 93.0, 2, 1,
    NOW(), '000000', 0,
    1, NOW(), 1, NOW()
),
(
    1863768901234569002,
    'APPLY20250127002',
    1863768901234560001, -- 假设批次ID
    1863768901234567002, -- 贫困助学金
    '2021001002', '测试学生李四', 1863768901234570002,
    100, 101, 1863768901234568001, -- 计算机学院，软件工程，测试班级1
    'STUDENT', 'SUBMITTED', '家庭经济困难，需要资助',
    '家庭经济困难，父母务农收入微薄，希望通过助学金减轻经济负担，专心学习。',
    78.5, 80.0, 15, 8,
    NOW(), '000000', 0,
    1, NOW(), 1, NOW()
),
(
    1863768901234569003,
    'APPLY20250127003',
    1863768901234560001, -- 假设批次ID
    1863768901234567003, -- 创新创业奖学金
    '2021002001', '测试学生王五', 1863768901234570003,
    100, 101, 1863768901234568002, -- 计算机学院，软件工程，测试班级2
    'RECOMMEND', 'REVIEWING', '在创新创业方面表现突出',
    '参与开发了校园APP项目，获得大学生创新创业大赛二等奖，具有很强的创新能力。',
    88.0, 89.5, 5, 3,
    NOW(), '000000', 0,
    1, NOW(), 1, NOW()
),
(
    1863768901234569004,
    'APPLY20250127004',
    1863768901234560001, -- 假设批次ID
    1863768901234567001, -- 优秀学生奖学金
    '2021003001', '测试学生赵六', 1863768901234570004,
    200, 201, 1863768901234568003, -- 机械学院，机械设计，测试班级3
    'STUDENT', 'APPROVED', '学习成绩优秀，综合素质高',
    '各科成绩均名列前茅，担任班长职务，热心为同学服务，积极参与各项活动。',
    95.0, 95.5, 1, 1,
    DATE_SUB(NOW(), INTERVAL 2 DAY), '000000', 0,
    1, DATE_SUB(NOW(), INTERVAL 2 DAY), 1, NOW()
),
(
    1863768901234569005,
    'APPLY20250127005',
    1863768901234560001, -- 假设批次ID
    1863768901234567002, -- 贫困助学金
    '2021003002', '测试学生孙七', 1863768901234570005,
    200, 201, 1863768901234568003, -- 机械学院，机械设计，测试班级3
    'STUDENT', 'REJECTED', '经济困难但成绩有待提高',
    '虽然家庭经济困难，但我会努力学习，提高成绩，争取下次申请成功。',
    65.0, 68.0, 20, 12,
    DATE_SUB(NOW(), INTERVAL 3 DAY), '000000', 0,
    1, DATE_SUB(NOW(), INTERVAL 3 DAY), 1, NOW()
);

-- =====================================================
-- 5. 插入奖学金批次测试数据（如果需要）
-- =====================================================
INSERT IGNORE INTO jcbt_scholarship_batch (
    id, batch_name, batch_code, academic_year, semester,
    start_date, end_date, status, description,
    tenant_id, deleted, create_by, create_time, update_by, update_time
) VALUES (
    1863768901234560001,
    '2024-2025学年第一学期奖学金申请',
    'BATCH202401',
    '2024-2025', '1',
    '2024-09-01', '2024-09-30', 'ACTIVE', '2024-2025学年第一学期奖学金申请批次',
    '000000', 0, 1, NOW(), 1, NOW()
);

-- =====================================================
-- 6. 验证数据插入
-- =====================================================
SELECT '=== 奖学金类型数据 ===' AS info;
SELECT id, type_code, type_name, award_amount FROM jcbt_scholarship_type WHERE type_name LIKE '测试%' OR type_name LIKE '%奖学金' OR type_name LIKE '%助学金' LIMIT 5;

SELECT '=== 班级数据 ===' AS info;
SELECT id, class_code, class_name, college_name, major_name FROM xg_class_msg WHERE class_name LIKE '测试%' LIMIT 5;

SELECT '=== 申请记录数据 ===' AS info;
SELECT id, application_no, student_name, current_status FROM jcbt_scholarship_application WHERE student_name LIKE '测试学生%' LIMIT 5;

SELECT '=== 关联查询测试 ===' AS info;
SELECT 
    a.application_no,
    a.student_name,
    st.type_name as scholarship_type_name,
    c.class_name,
    a.current_status
FROM jcbt_scholarship_application a
LEFT JOIN jcbt_scholarship_type st ON a.scholarship_type_id = st.id
LEFT JOIN xg_class_msg c ON a.class_id = c.id
WHERE a.student_name LIKE '测试学生%'
LIMIT 10;

COMMIT;