all-project/backend/sql/diagnose_quota_display_issue_final.sql

-- ===================================================================
-- 奖学金名额显示问题诊断脚本 (最终修正版)
-- 创建时间: 2025-01-27
-- 目的: 检查班级名额分配和奖学金类型的数据完整性
-- 表名修正: xg_class_msg, jcbt_scholarship_class_quota
-- ===================================================================

-- 1. 检查当前班主任和班级信息
SELECT
    '=== 1. 班主任和班级信息检查 ===' as info;

SELECT
    u.user_id,
    u.user_name,
    u.nick_name,
    c.id as class_id,
    c.class_name,
    c.grade,
    c.college_id,
    c.major_id
FROM sys_user u
LEFT JOIN xg_class_msg c ON u.user_id = c.class_teacher_id
WHERE u.user_type = '03'  -- 班主任
AND c.id IS NOT NULL
ORDER BY c.id;

-- 2. 检查奖学金批次信息
SELECT
    '=== 2. 奖学金批次信息检查 ===' as info;

SELECT
    id as batch_id,
    batch_name,
    status as batch_status,
    start_time,
    end_time,
    create_time
FROM jcbt_scholarship_batch
WHERE status = 'ACTIVE'  -- 启用状态
ORDER BY create_time DESC;

-- 3. 检查奖学金类型信息
SELECT
    '=== 3. 奖学金类型信息检查 ===' as info;

SELECT
    st.id as type_id,
    st.type_name,
    st.level_type,
    st.status as type_status,
    bt.batch_id,
    b.batch_name
FROM jcbt_scholarship_type st
LEFT JOIN jcbt_scholarship_batch_type bt ON st.id = bt.scholarship_type_id
LEFT JOIN jcbt_scholarship_batch b ON bt.batch_id = b.id
WHERE st.status = 1  -- 启用状态
ORDER BY st.level_type, st.type_name;

-- 4. 检查班级名额分配情况
SELECT
    '=== 4. 班级名额分配情况检查 ===' as info;

SELECT
    cq.id as quota_id,
    cq.batch_id,
    b.batch_name,
    cq.scholarship_type_id,
    st.type_name,
    cq.class_id,
    c.class_name,
    cq.total_quota,
    cq.used_quota,
    cq.available_quota,
    cq.allocation_method,
    cq.status,
    cq.create_time
FROM jcbt_scholarship_class_quota cq
LEFT JOIN jcbt_scholarship_batch b ON cq.batch_id = b.id
LEFT JOIN jcbt_scholarship_type st ON cq.scholarship_type_id = st.id
LEFT JOIN xg_class_msg c ON cq.class_id = c.id
ORDER BY cq.batch_id, cq.class_id, cq.scholarship_type_id;

-- 5. 检查已使用名额情况
SELECT
    '=== 5. 已使用名额情况检查 ===' as info;

SELECT
    sa.batch_id,
    b.batch_name,
    sa.scholarship_type_id,
    st.type_name,
    sa.applicant_class_id,
    c.class_name,
    COUNT(*) as actual_used_quota,
    GROUP_CONCAT(sa.application_status) as status_list
FROM jcbt_scholarship_application sa
LEFT JOIN jcbt_scholarship_batch b ON sa.batch_id = b.batch_id
LEFT JOIN jcbt_scholarship_type st ON sa.scholarship_type_id = st.type_id
LEFT JOIN xg_class_msg c ON sa.applicant_class_id = c.id
WHERE sa.application_status IN ('submitted', 'approved')  -- 有效申请
GROUP BY sa.batch_id, sa.scholarship_type_id, sa.applicant_class_id
ORDER BY sa.batch_id, sa.applicant_class_id, sa.scholarship_type_id;

-- 6. 检查名额使用情况汇总与一致性
SELECT
    '=== 6. 名额使用情况汇总与一致性检查 ===' as info;

SELECT
    cq.batch_id,
    b.batch_name,
    cq.class_id,
    c.class_name,
    cq.scholarship_type_id,
    st.type_name,
    cq.total_quota,
    cq.used_quota as recorded_used_quota,
    COALESCE(actual_used.used_count, 0) as actual_used_quota,
    cq.available_quota as recorded_available_quota,
    (cq.total_quota - COALESCE(actual_used.used_count, 0)) as calculated_available_quota,
    CASE
        WHEN cq.used_quota != COALESCE(actual_used.used_count, 0) THEN 'INCONSISTENT'
        WHEN cq.available_quota != (cq.total_quota - COALESCE(actual_used.used_count, 0)) THEN 'INCONSISTENT'
        ELSE 'CONSISTENT'
    END as consistency_status,
    CASE
        WHEN cq.total_quota = 0 THEN 'unlimited'
        WHEN (cq.total_quota - COALESCE(actual_used.used_count, 0)) > 0 THEN 'available'
        ELSE 'full'
    END as quota_status
FROM jcbt_scholarship_class_quota cq
LEFT JOIN jcbt_scholarship_batch b ON cq.batch_id = b.batch_id
LEFT JOIN jcbt_scholarship_type st ON cq.scholarship_type_id = st.type_id
LEFT JOIN xg_class_msg c ON cq.class_id = c.id
LEFT JOIN (
    SELECT
        batch_id,
        scholarship_type_id,
        applicant_class_id,
        COUNT(*) as used_count
    FROM jcbt_scholarship_application
    WHERE application_status IN ('submitted', 'approved')
    GROUP BY batch_id, scholarship_type_id, applicant_class_id
) actual_used ON cq.batch_id = actual_used.batch_id
    AND cq.scholarship_type_id = actual_used.scholarship_type_id
    AND cq.class_id = actual_used.applicant_class_id
ORDER BY cq.batch_id, cq.class_id, cq.scholarship_type_id;

-- 7. 检查特定班级的详细情况（以班级ID=1为例）
SELECT
    '=== 7. 特定班级详细情况检查 (class_id=1) ===' as info;

SELECT
    '班级基本信息:' as section,
    c.id as class_id,
    c.class_name,
    c.grade,
    c.class_teacher_id,
    u.user_name as class_teacher_name,
    u.nick_name as class_teacher_nick
FROM xg_class_msg c
LEFT JOIN sys_user u ON c.class_teacher_id = u.user_id
WHERE c.id = 1;

SELECT
    '该班级的名额分配:' as section,
    cq.scholarship_type_id,
    st.type_name,
    cq.total_quota,
    cq.used_quota,
    cq.available_quota,
    cq.allocation_method,
    cq.status
FROM jcbt_scholarship_class_quota cq
LEFT JOIN jcbt_scholarship_type st ON cq.scholarship_type_id = st.type_id
WHERE cq.class_id = 1
ORDER BY cq.scholarship_type_id;

-- 8. 检查数据完整性问题
SELECT
    '=== 8. 数据完整性问题检查 ===' as info;

-- 检查缺少名额分配的奖学金类型
SELECT
    '缺少名额分配的奖学金类型:' as issue_type,
    st.type_id,
    st.type_name,
    bt.batch_id,
    b.batch_name,
    COUNT(DISTINCT c.id) as total_classes_without_quota
FROM jcbt_scholarship_type st
INNER JOIN jcbt_scholarship_batch_type bt ON st.type_id = bt.type_id
INNER JOIN jcbt_scholarship_batch b ON bt.batch_id = b.batch_id
CROSS JOIN xg_class_msg c
LEFT JOIN jcbt_scholarship_class_quota cq ON st.type_id = cq.scholarship_type_id
    AND bt.batch_id = cq.batch_id
    AND c.id = cq.class_id
WHERE st.type_status = '1'
AND b.batch_status = '1'
AND c.class_status = '1'
AND cq.id IS NULL
GROUP BY st.type_id, st.type_name, bt.batch_id, b.batch_name;

-- 检查有名额分配但奖学金类型不存在的情况
SELECT
    '名额分配指向不存在的奖学金类型:' as issue_type,
    cq.id as quota_id,
    cq.scholarship_type_id,
    cq.batch_id,
    cq.class_id
FROM jcbt_scholarship_class_quota cq
LEFT JOIN jcbt_scholarship_type st ON cq.scholarship_type_id = st.type_id
WHERE st.type_id IS NULL;

-- 检查名额分配指向不存在的班级
SELECT
    '名额分配指向不存在的班级:' as issue_type,
    cq.id as quota_id,
    cq.class_id,
    cq.class_name as recorded_class_name
FROM jcbt_scholarship_class_quota cq
LEFT JOIN xg_class_msg c ON cq.class_id = c.id
WHERE c.id IS NULL;

-- 9. 模拟API查询结果
SELECT
    '=== 9. 模拟API查询结果 (batch_id=1, class_id=1) ===' as info;

SELECT
    st.type_id,
    st.type_name,
    st.type_level,
    st.type_description,
    CASE
        WHEN cq.total_quota IS NULL THEN 0
        ELSE cq.total_quota
    END as total_quota,
    CASE
        WHEN cq.used_quota IS NULL THEN 0
        ELSE cq.used_quota
    END as used_quota,
    CASE
        WHEN cq.available_quota IS NULL THEN 0
        WHEN cq.total_quota = 0 THEN 999999
        ELSE cq.available_quota
    END as available_quota,
    CASE
        WHEN cq.total_quota IS NULL THEN FALSE
        WHEN cq.total_quota = 0 THEN FALSE
        ELSE TRUE
    END as has_quota,
    CASE
        WHEN cq.total_quota IS NULL THEN 'no_allocation'
        WHEN cq.total_quota = 0 THEN 'unlimited'
        WHEN cq.available_quota > 0 THEN 'available'
        ELSE 'full'
    END as quota_status,
    cq.allocation_method,
    cq.status as allocation_status
FROM jcbt_scholarship_type st
INNER JOIN jcbt_scholarship_batch_type bt ON st.type_id = bt.type_id
LEFT JOIN jcbt_scholarship_class_quota cq ON st.type_id = cq.scholarship_type_id
    AND bt.batch_id = cq.batch_id
    AND cq.class_id = 1
WHERE bt.batch_id = 1
AND st.type_status = '1'
ORDER BY
    CASE quota_status
        WHEN 'available' THEN 1
        WHEN 'unlimited' THEN 2
        WHEN 'full' THEN 3
        WHEN 'no_allocation' THEN 4
    END,
    st.type_level,
    st.type_name;

-- 10. 额外检查：验证数据同步问题
SELECT
    '=== 10. 数据同步问题检查 ===' as info;

-- 检查used_quota和available_quota字段是否需要更新
SELECT
    'need_quota_update' as check_type,
    cq.id,
    cq.batch_id,
    cq.class_id,
    cq.scholarship_type_id,
    cq.total_quota,
    cq.used_quota as recorded_used,
    COALESCE(actual.actual_used, 0) as actual_used,
    cq.available_quota as recorded_available,
    (cq.total_quota - COALESCE(actual.actual_used, 0)) as calculated_available
FROM jcbt_scholarship_class_quota cq
LEFT JOIN (
    SELECT
        batch_id,
        scholarship_type_id,
        applicant_class_id,
        COUNT(*) as actual_used
    FROM jcbt_scholarship_application
    WHERE application_status IN ('submitted', 'approved')
    GROUP BY batch_id, scholarship_type_id, applicant_class_id
) actual ON cq.batch_id = actual.batch_id
    AND cq.scholarship_type_id = actual.scholarship_type_id
    AND cq.class_id = actual.applicant_class_id
WHERE cq.used_quota != COALESCE(actual.actual_used, 0)
   OR cq.available_quota != (cq.total_quota - COALESCE(actual.actual_used, 0));