all-project/backend/sql/diagnose_quota_display_issue.sql

-- ===================================================================
-- 奖学金名额显示问题诊断脚本
-- 创建时间: 2025-01-27
-- 目的: 检查班级名额分配和奖学金类型的数据完整性
-- ===================================================================

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

SELECT
    u.user_id,
    u.user_name,
    u.nick_name,
    c.class_id,
    c.class_name,
    c.grade_level,
    c.dept_id,
    d.dept_name
FROM sys_user u
LEFT JOIN jcbt_class c ON u.user_id = c.head_teacher_id
LEFT JOIN sys_dept d ON c.dept_id = d.dept_id
WHERE u.user_type = '03'  -- 班主任
AND c.class_id IS NOT NULL
ORDER BY c.class_id;

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

SELECT
    batch_id,
    batch_name,
    batch_status,
    start_date,
    end_date,
    create_time
FROM jcbt_scholarship_batch
WHERE batch_status = '1'  -- 启用状态
ORDER BY create_time DESC;

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

SELECT
    st.type_id,
    st.type_name,
    st.type_level,
    st.type_status,
    bt.batch_id,
    b.batch_name
FROM jcbt_scholarship_type st
LEFT JOIN jcbt_batch_type bt ON st.type_id = bt.type_id
LEFT JOIN jcbt_scholarship_batch b ON bt.batch_id = b.batch_id
WHERE st.type_status = '1'  -- 启用状态
ORDER BY st.type_level, st.type_name;

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

SELECT
    qa.allocation_id,
    qa.batch_id,
    b.batch_name,
    qa.type_id,
    st.type_name,
    qa.class_id,
    c.class_name,
    qa.allocated_quota,
    qa.allocation_basis,
    qa.create_time
FROM jcbt_quota_allocation qa
LEFT JOIN jcbt_scholarship_batch b ON qa.batch_id = b.batch_id
LEFT JOIN jcbt_scholarship_type st ON qa.type_id = st.type_id
LEFT JOIN jcbt_class c ON qa.class_id = c.class_id
ORDER BY qa.batch_id, qa.class_id, qa.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 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 jcbt_class c ON sa.applicant_class_id = c.class_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
    qa.batch_id,
    b.batch_name,
    qa.class_id,
    c.class_name,
    qa.type_id,
    st.type_name,
    qa.allocated_quota,
    COALESCE(used.used_count, 0) as used_quota,
    (qa.allocated_quota - COALESCE(used.used_count, 0)) as available_quota,
    CASE
        WHEN qa.allocated_quota = 0 THEN 'unlimited'
        WHEN (qa.allocated_quota - COALESCE(used.used_count, 0)) > 0 THEN 'available'
        ELSE 'full'
    END as quota_status
FROM jcbt_quota_allocation qa
LEFT JOIN jcbt_scholarship_batch b ON qa.batch_id = b.batch_id
LEFT JOIN jcbt_scholarship_type st ON qa.type_id = st.type_id
LEFT JOIN jcbt_class c ON qa.class_id = c.class_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
) used ON qa.batch_id = used.batch_id
    AND qa.type_id = used.scholarship_type_id
    AND qa.class_id = used.applicant_class_id
ORDER BY qa.batch_id, qa.class_id, qa.type_id;

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

SELECT
    '班级基本信息:' as section,
    c.class_id,
    c.class_name,
    c.grade_level,
    u.user_name as head_teacher_name,
    u.nick_name as head_teacher_nick
FROM jcbt_class c
LEFT JOIN sys_user u ON c.head_teacher_id = u.user_id
WHERE c.class_id = 1;

SELECT
    '该班级的名额分配:' as section,
    qa.type_id,
    st.type_name,
    qa.allocated_quota,
    qa.allocation_basis
FROM jcbt_quota_allocation qa
LEFT JOIN jcbt_scholarship_type st ON qa.type_id = st.type_id
WHERE qa.class_id = 1
ORDER BY qa.type_id;

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

-- 检查缺少名额分配的奖学金类型
SELECT
    '缺少名额分配的奖学金类型:' as issue_type,
    st.type_id,
    st.type_name,
    bt.batch_id,
    b.batch_name
FROM jcbt_scholarship_type st
INNER JOIN jcbt_batch_type bt ON st.type_id = bt.type_id
INNER JOIN jcbt_scholarship_batch b ON bt.batch_id = b.batch_id
LEFT JOIN jcbt_quota_allocation qa ON st.type_id = qa.type_id AND bt.batch_id = qa.batch_id
WHERE st.type_status = '1'
AND b.batch_status = '1'
AND qa.allocation_id IS NULL;

-- 检查有名额分配但奖学金类型不存在的情况
SELECT
    '名额分配指向不存在的奖学金类型:' as issue_type,
    qa.allocation_id,
    qa.type_id,
    qa.batch_id,
    qa.class_id
FROM jcbt_quota_allocation qa
LEFT JOIN jcbt_scholarship_type st ON qa.type_id = st.type_id
WHERE st.type_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 qa.allocated_quota IS NULL THEN 0
        ELSE qa.allocated_quota
    END as total_quota,
    COALESCE(used.used_count, 0) as used_quota,
    CASE
        WHEN qa.allocated_quota IS NULL THEN 0
        WHEN qa.allocated_quota = 0 THEN 999999
        ELSE (qa.allocated_quota - COALESCE(used.used_count, 0))
    END as available_quota,
    CASE
        WHEN qa.allocated_quota IS NULL THEN FALSE
        WHEN qa.allocated_quota = 0 THEN FALSE
        ELSE TRUE
    END as has_quota,
    CASE
        WHEN qa.allocated_quota IS NULL THEN 'unlimited'
        WHEN qa.allocated_quota = 0 THEN 'unlimited'
        WHEN (qa.allocated_quota - COALESCE(used.used_count, 0)) > 0 THEN 'available'
        ELSE 'full'
    END as quota_status
FROM jcbt_scholarship_type st
INNER JOIN jcbt_batch_type bt ON st.type_id = bt.type_id
LEFT JOIN jcbt_quota_allocation qa ON st.type_id = qa.type_id
    AND bt.batch_id = qa.batch_id
    AND qa.class_id = 1
LEFT JOIN (
    SELECT
        scholarship_type_id,
        applicant_class_id,
        COUNT(*) as used_count
    FROM jcbt_scholarship_application
    WHERE batch_id = 1
    AND applicant_class_id = 1
    AND application_status IN ('submitted', 'approved')
    GROUP BY scholarship_type_id, applicant_class_id
) used ON st.type_id = used.scholarship_type_id
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
    END,
    st.type_level,
    st.type_name;