# Tài liệu 6 Câu lệnh SQL cho getReportData (Dùng bảng LCMS)

Tài liệu này cung cấp phiên bản SQL được chuyển đổi sang hệ thống bảng `lcms_` cho hàm `getReportData`. Các câu lệnh này sử dụng CTE (Common Table Expressions) để tối ưu hiệu suất.

---

## TRƯỜNG HỢP 1: LỌC THEO HỌC SINH (FILTER BY STUDENT)

### 1. Câu lệnh Thống kê Hoàn thành (Completion Statistics)
```sql
WITH FilteredAssignments AS (
    SELECT 
        ua.usrasi_student_id AS student_id,
        ua.usrasi_course_id AS course_id,
        ua.usrasi_section_id AS section_id,
        c.cou_section_type AS section_type,
        c.cou_id AS section_pk_id,
        CASE WHEN MIN(ua.usrasi_completion_state) = 1 THEN 1 ELSE 0 END AS is_completed
    FROM lcms_user_assignments ua
    JOIN lcms_courses c ON ua.usrasi_section_id = c.cou_id AND c.cou_type = 'section'
    WHERE c.cou_section_type IN (?) -- $sectionFilter
      AND ua.usrasi_student_id IN (?) -- $studentFilter
      AND ua.usrasi_course_id IN (?) -- $courseFilter
      AND ua.usrasi_completion_time BETWEEN ? AND ? -- $dateFilter
      AND c.cou_visible = 1
    GROUP BY ua.usrasi_student_id, ua.usrasi_course_id, ua.usrasi_section_id, c.cou_section_type, c.cou_id
)
SELECT 
    student_id,
    course_id,
    section_type,
    COUNT(section_id) AS total_sections,
    SUM(is_completed) AS completed_sections,
    ROUND((SUM(is_completed) / NULLIF(COUNT(section_id), 0)) * 100, 2) AS completion_ratio
FROM FilteredAssignments
GROUP BY student_id, course_id, section_type
```

### 2. Câu lệnh Thống kê Điểm số (Score Statistics)
```sql
WITH FilteredAssignments AS (
    SELECT 
        ua.usrasi_student_id AS student_id,
        ua.usrasi_course_id AS course_id,
        ua.usrasi_section_id AS section_id,
        c.cou_id AS section_pk_id,
        c.cou_section_type AS section_type,
        CASE WHEN MIN(ua.usrasi_completion_state) = 1 THEN 1 ELSE 0 END AS is_section_completed
    FROM lcms_user_assignments ua
    JOIN lcms_courses c ON ua.usrasi_section_id = c.cou_id AND c.cou_type = 'section'
    WHERE c.cou_section_type IN (?)
      AND ua.usrasi_student_id IN (?)
      AND ua.usrasi_course_id IN (?)
      AND c.cou_visible = 1
    GROUP BY ua.usrasi_student_id, ua.usrasi_course_id, ua.usrasi_section_id, c.cou_id, c.cou_section_type
),
QuizCountPerSection AS (
    SELECT cou_parent_id AS section_pk_id, COUNT(*) AS total_quizzes
    FROM lcms_courses 
    WHERE cou_type = 'quiz' AND cou_visible = 1
    GROUP BY cou_parent_id
),
MaxScores AS (
    SELECT 
        fa.student_id,
        c.cou_id AS quiz_id,
        c.cou_parent_id AS section_pk_id,
        MAX(GREATEST(
            CASE WHEN ss.stusco_overall_score IS NULL OR ss.stusco_overall_score = '' OR ss.stusco_overall_score = '-1' THEN -1 ELSE CAST(ss.stusco_overall_score AS DECIMAL(10,2)) END,
            COALESCE(CAST(uqg.usrqgr_score AS DECIMAL(10,2)), -1)
        )) AS highest_score
    FROM FilteredAssignments fa
    JOIN lcms_courses c ON c.cou_parent_id = fa.section_pk_id AND c.cou_type = 'quiz'
    LEFT JOIN lcms_student_scores ss ON ss.stusco_student_id = fa.student_id AND ss.stusco_course_id = c.cou_id
    LEFT JOIN lcms_user_quiz_grades uqg ON uqg.usrqgr_user_id = fa.student_id AND uqg.usrqgr_quiz_id = c.cou_id
    WHERE c.cou_visible = 1
    GROUP BY fa.student_id, c.cou_id, c.cou_parent_id
    HAVING highest_score >= 0
),
SectionGrades AS (
    SELECT 
        ms.student_id,
        ms.section_pk_id,
        AVG(ms.highest_score) AS avg_score
    FROM MaxScores ms
    JOIN QuizCountPerSection qc ON ms.section_pk_id = qc.section_pk_id
    GROUP BY ms.student_id, ms.section_pk_id, qc.total_quizzes
    HAVING COUNT(ms.quiz_id) >= qc.total_quizzes
)
SELECT 
    fa.student_id,
    fa.course_id,
    fa.section_type,
    COUNT(fa.section_id) AS total_sections,
    SUM(fa.is_section_completed) AS completed_sections,
    ROUND(AVG(sg.avg_score), 2) AS avg_homework_score,
    ROUND((SUM(fa.is_section_completed) / NULLIF(COUNT(fa.section_id), 0)) * 100, 2) AS completion_ratio
FROM FilteredAssignments fa
LEFT JOIN SectionGrades sg ON fa.student_id = sg.student_id AND fa.section_pk_id = sg.section_pk_id
GROUP BY fa.student_id, fa.course_id, fa.section_type
```

---

## TRƯỜNG HỢP 2: LỌC THEO 1 KHÓA HỌC (FILTER BY 1 COURSE)

### 3. Câu lệnh Thống kê Hoàn thành (Tất cả học viên)
```sql
WITH FilteredAssignments AS (
    SELECT 
        ua.usrasi_course_id AS course_id,
        ua.usrasi_student_id AS student_id,
        ua.usrasi_section_id AS section_id,
        c.cou_section_type AS section_type,
        c.cou_id AS section_pk_id,
        CASE WHEN MIN(ua.usrasi_completion_state) = 1 THEN 1 ELSE 0 END AS is_completed
    FROM lcms_user_assignments ua
    JOIN lcms_courses c ON ua.usrasi_section_id = c.cou_id AND c.cou_type = 'section'
    WHERE c.cou_section_type IN (?)
      AND ua.usrasi_course_id = ?
      AND ua.usrasi_completion_time BETWEEN ? AND ?
      AND c.cou_visible = 1
    GROUP BY ua.usrasi_course_id, ua.usrasi_student_id, ua.usrasi_section_id, c.cou_section_type, c.cou_id
)
SELECT 
    course_id,
    section_type,
    COUNT(section_id) AS total_sections_all_students,
    SUM(is_completed) AS completed_sections_all_students,
    ROUND((SUM(is_completed) / NULLIF(COUNT(section_id), 0)) * 100, 2) AS overall_completion_ratio
FROM FilteredAssignments
GROUP BY course_id, section_type
```

### 4. Câu lệnh Thống kê Điểm số (Tất cả học viên)
```sql
WITH FilteredAssignments AS (
    SELECT 
        ua.usrasi_student_id AS student_id,
        ua.usrasi_course_id AS course_id,
        ua.usrasi_section_id AS section_id,
        c.cou_id AS section_pk_id,
        c.cou_section_type AS section_type,
        CASE WHEN MIN(ua.usrasi_completion_state) = 1 THEN 1 ELSE 0 END AS is_section_completed
    FROM lcms_user_assignments ua
    JOIN lcms_courses c ON ua.usrasi_section_id = c.cou_id AND c.cou_type = 'section'
    WHERE c.cou_section_type IN (?)
      AND ua.usrasi_course_id = ?
      AND c.cou_visible = 1
    GROUP BY ua.usrasi_student_id, ua.usrasi_course_id, ua.usrasi_section_id, c.cou_id, c.cou_section_type
),
-- [Các CTE QuizCountPerSection, MaxScores, SectionGrades tương tự ở trên]
SELECT 
    fa.course_id,
    fa.section_type,
    COUNT(fa.section_id) AS total_sections_assigned,
    SUM(fa.is_section_completed) AS total_completed_sections,
    ROUND(AVG(sg.avg_score), 2) AS avg_homework_score,
    ROUND((SUM(fa.is_section_completed) / NULLIF(COUNT(fa.section_id), 0)) * 100, 2) AS completion_ratio
FROM FilteredAssignments fa
LEFT JOIN SectionGrades sg ON fa.student_id = sg.student_id AND fa.section_pk_id = sg.section_pk_id
GROUP BY fa.course_id, fa.section_type
```

---

## TRƯỜNG HỢP 3: LỌC THEO NHIỀU KHÓA HỌC (MULTIPLE COURSES)

### 5. Câu lệnh Thống kê Hoàn thành (Gộp nhiều khóa học)
```sql
WITH FilteredAssignments AS (
    SELECT 
        ua.usrasi_student_id,
        ua.usrasi_course_id AS course_id,
        ua.usrasi_section_id AS section_id,
        c.cou_section_type AS section_type,
        c.cou_id AS section_pk_id,
        CASE WHEN MIN(ua.usrasi_completion_state) = 1 THEN 1 ELSE 0 END AS is_completed
    FROM lcms_user_assignments ua
    JOIN lcms_courses c ON ua.usrasi_section_id = c.cou_id AND c.cou_type = 'section'
    WHERE c.cou_section_type IN (?)
      AND ua.usrasi_course_id IN (?)
      AND c.cou_visible = 1
    GROUP BY ua.usrasi_student_id, ua.usrasi_course_id, ua.usrasi_section_id, c.cou_section_type, c.cou_id
)
SELECT 
    GROUP_CONCAT(DISTINCT course_id) AS list_course_ids,
    section_type,
    COUNT(section_id) AS grand_total_sections,
    SUM(is_completed) AS grand_completed_sections,
    ROUND((SUM(is_completed) / NULLIF(COUNT(section_id), 0)) * 100, 2) AS grand_completion_ratio
FROM FilteredAssignments
GROUP BY section_type
```

### 6. Câu lệnh Thống kê Điểm số (Gộp nhiều khóa học)
```sql
WITH FilteredAssignments AS (
    SELECT 
        ua.usrasi_student_id AS student_id,
        ua.usrasi_course_id AS course_id,
        ua.usrasi_section_id AS section_id,
        c.cou_id AS section_pk_id,
        c.cou_section_type AS section_type,
        CASE WHEN MIN(ua.usrasi_completion_state) = 1 THEN 1 ELSE 0 END AS is_section_completed
    FROM lcms_user_assignments ua
    JOIN lcms_courses c ON ua.usrasi_section_id = c.cou_id AND c.cou_type = 'section'
    WHERE c.cou_section_type IN (?)
      AND ua.usrasi_course_id IN (?)
      AND c.cou_visible = 1
    GROUP BY ua.usrasi_student_id, ua.usrasi_course_id, ua.usrasi_section_id, c.cou_id, c.cou_section_type
),
-- [Các CTE QuizCountPerSection, MaxScores, SectionGrades tương tự ở trên]
SELECT 
    GROUP_CONCAT(DISTINCT fa.course_id) AS merged_course_ids,
    fa.section_type,
    COUNT(fa.section_id) AS grand_total_sections,
    SUM(fa.is_section_completed) AS grand_completed_sections,
    ROUND(AVG(sg.avg_score), 2) AS grand_avg_homework_score,
    ROUND((SUM(fa.is_section_completed) / NULLIF(COUNT(fa.section_id), 0)) * 100, 2) AS grand_completion_ratio
FROM FilteredAssignments fa
LEFT JOIN SectionGrades sg ON fa.student_id = sg.student_id AND fa.section_pk_id = sg.section_pk_id
GROUP BY fa.section_type
```