build_current_courses_rows()

Complexity

Conditions	40
Paths	> 20000

Size

Total Lines	317
Code Lines	177

Duplication

Lines	0
Ratio	0 %

Importance

Changes

0

<?php
function build_current_courses_rows(int $userId, int $sessionId = 0): array
{
    $rows = [];
    $rowIndex = 0;

    $myCourses = CourseManager::get_course_list_of_user_as_course_admin($userId);

    if (empty($myCourses)) {
        return $rows;
    }

    foreach ($myCourses as $course) {
        $courseId = (int) ($course['id'] ?? 0);

        if (0 === $courseId) {
            continue;
        }

        $courseInfo = api_get_course_info_by_id($courseId);
        if (empty($courseInfo)) {
            // Safety check: if course info is not found, skip.
            continue;
        }

        $courseCode = $courseInfo['code'] ?? $course['code'];

        // Only show open courses.
        if (0 === (int) $courseInfo['visibility']) {
            continue;
        }

        // ------------------------------------------------------------
        // 1) Teachers list (for display).
        // ------------------------------------------------------------
        $teachers = CourseManager::get_teacher_list_from_course_code($courseCode);
        $teacherNames = [];

        if (!empty($teachers)) {
            foreach ($teachers as $teacher) {
                $teacherNames[] = $teacher['firstname'].' '.$teacher['lastname'];
            }
        }

        // ------------------------------------------------------------
        // 2) Students list (only "real" students, status = STUDENT).
        // ------------------------------------------------------------
        $tmpStudents = CourseManager::get_student_list_from_course_code($courseCode, false);
        $students = [];

        foreach ($tmpStudents as $student) {
            $userInfo = api_get_user_info($student['user_id']);
            if (STUDENT !== (int) $userInfo['status']) {
                continue;
            }

            $students[] = (int) $student['user_id'];
        }

        $studentCount = count($students);
        $studentsIdList = $studentCount > 0 ? implode(',', $students) : '';

        // ------------------------------------------------------------
        // 3) Course tables and main tables.
        // ------------------------------------------------------------
        $tLp = Database::get_course_table(TABLE_LP_MAIN);                // c_lp
        $tLpItem = Database::get_course_table(TABLE_LP_ITEM);            // c_lp_item
        $tNews = Database::get_course_table(TABLE_ANNOUNCEMENT);         // c_announcement
        $tLpView = Database::get_course_table(TABLE_LP_VIEW);            // c_lp_view
        $tLpItemView = Database::get_course_table(TABLE_LP_ITEM_VIEW);   // c_lp_item_view
        $tResourceLink = 'resource_link'; // Main table name for resource links.

        // ------------------------------------------------------------
        // 4) Get LP list for this course (base course, no session).
        //    FIRST TRY: through resource_link (original behaviour).
        // ------------------------------------------------------------
        $sqlLp = "SELECT lp.iid, lp.title
                  FROM $tLp lp
                  INNER JOIN $tResourceLink li
                      ON lp.resource_node_id = li.id
                  WHERE li.c_id = $courseId
                    AND li.session_id = $sessionId";

        $resLp = Database::query($sqlLp);

        if (0 === Database::num_rows($resLp)) {
            // SECOND TRY (fallback): LPs that have tracking entries in c_lp_view.
            $sqlLpFallback = "SELECT DISTINCT lp.iid, lp.title
                              FROM $tLp lp
                              INNER JOIN $tLpView v
                                  ON v.lp_id = lp.iid
                              WHERE v.c_id = $courseId
                                AND (v.session_id = $sessionId OR v.session_id IS NULL)";
            $resLp = Database::query($sqlLpFallback);
        }

        if (0 === Database::num_rows($resLp)) {
            // Course without learning paths: still add one row so the course
            // is visible in the report (all stats = 0).
            $rows[$rowIndex] = [
                'lp' => get_lang('No learning paths'),
                'teachers' => !empty($teacherNames) ? implode(', ', $teacherNames) : '',
                'course_name' => $course['title'],
                'count_students' => $studentCount,
                'count_students_accessing' => 0,
                'count_students_accessing_percentage' => 0,
                // Legacy naming: "_at_50" actually stores the count of 100% completed.
                'count_students_complete_all_activities_at_50' => 0,
                // Legacy: this stores the percentage of 100% completed.
                'count_students_complete_all_activities' => 0,
                'average_percentage_activities_completed_per_student' => 0,
                'total_time_spent' => 0,
                'average_time_spent_per_student' => 0,
                'learnpath_docs' => 0,
                'learnpath_exercises' => 0,
                'learnpath_links' => 0,
                'learnpath_forums' => 0,
                'learnpath_assignments' => 0,
                'total_announcements' => 0,
            ];
            $rowIndex++;

            continue;
        }

        // Store LPs in a PHP array to be able to iterate twice.
        $lpRows = [];
        $lpIds = [];

        while ($lpRow = Database::fetch_array($resLp)) {
            $lpId = (int) $lpRow['iid'];
            $lpRows[] = $lpRow;
            $lpIds[] = $lpId;
        }

        if (empty($lpRows)) {
            continue;
        }

        // ------------------------------------------------------------
        // 5) Pre-compute stats per (lp, user) using c_lp_view + c_lp_item_view.
        //    This avoids looping over each student in PHP.
        // ------------------------------------------------------------
        $lpStats = []; // [lp_id => ['count_accessing', 'count_100', 'sum_progress', 'sum_time']]

        if ($studentCount > 0 && !empty($lpIds) && '' !== $studentsIdList) {
            $lpIdList = implode(',', $lpIds);

            // This query returns ONE row per (lp_id, user_id) with:
            // - progress (0..100)
            // - total_time (sum of all item views in that LP for that user)
            $sqlStats = "
                SELECT
                    v.lp_id,
                    v.user_id,
                    COALESCE(MAX(v.progress), 0) AS progress,
                    COALESCE(SUM(iv.total_time), 0) AS total_time
                FROM $tLpView v
                LEFT JOIN $tLpItemView iv
                    ON iv.lp_view_id = v.iid
                WHERE v.c_id = $courseId
                  AND (v.session_id = $sessionId OR v.session_id IS NULL)
                  AND v.lp_id IN ($lpIdList)
                  AND v.user_id IN ($studentsIdList)
                GROUP BY v.lp_id, v.user_id
            ";

            $resStats = Database::query($sqlStats);

            while ($row = Database::fetch_array($resStats)) {
                $lpId = (int) $row['lp_id'];
                $progress = (int) $row['progress'];
                $totalTime = (int) $row['total_time'];

                if (!isset($lpStats[$lpId])) {
                    $lpStats[$lpId] = [
                        'count_accessing' => 0,
                        'count_100' => 0,
                        'sum_progress' => 0,
                        'sum_time' => 0,
                    ];
                }

                // Student is considered "accessing" if there is progress or time.
                if ($progress > 0 || $totalTime > 0) {
                    $lpStats[$lpId]['count_accessing']++;
                }

                // Student completed 100% of the LP.
                if (100 === $progress) {
                    $lpStats[$lpId]['count_100']++;
                }

                $lpStats[$lpId]['sum_progress'] += $progress;
                $lpStats[$lpId]['sum_time'] += $totalTime;
            }
        }

        // ------------------------------------------------------------
        // 6) Build one row per LP with all stats.
        // ------------------------------------------------------------
        foreach ($lpRows as $lpRow) {
            $lpId = (int) $lpRow['iid'];
            $lpTitle = $lpRow['title'];

            // Base row with default values.
            $rows[$rowIndex] = [
                'lp' => '<a href="'.
                    api_get_path(WEB_CODE_PATH).'lp/lp_controller.php?cidReq='.
                    $courseCode.'&amp;action=view&amp;lp_id='.$lpId.
                    '" target="_blank">'.
                    Security::remove_XSS($lpTitle).
                    '</a>',
                'teachers' => !empty($teacherNames) ? implode(', ', $teacherNames) : '',
                'course_name' => $course['title'],
                'count_students' => $studentCount,
                'count_students_accessing' => 0,
                'count_students_accessing_percentage' => 0,
                // Legacy naming kept for compatibility.
                'count_students_complete_all_activities_at_50' => 0,
                'count_students_complete_all_activities' => 0,
                'average_percentage_activities_completed_per_student' => 0,
                'total_time_spent' => 0,
                'average_time_spent_per_student' => 0,
                'learnpath_docs' => 0,
                'learnpath_exercises' => 0,
                'learnpath_links' => 0,
                'learnpath_forums' => 0,
                'learnpath_assignments' => 0,
                'total_announcements' => 0,
            ];

            // 6.a) Apply pre-computed stats for this LP (if any).
            if ($studentCount > 0 && isset($lpStats[$lpId])) {
                $stats = $lpStats[$lpId];

                $countAccessing = (int) $stats['count_accessing'];
                $countComplete100 = (int) $stats['count_100'];
                $sumProgress = (int) $stats['sum_progress'];
                $sumTime = (int) $stats['sum_time'];

                $rows[$rowIndex]['count_students_accessing'] = $countAccessing;

                if ($studentCount > 0) {
                    $rows[$rowIndex]['count_students_accessing_percentage'] =
                        $countAccessing > 0
                            ? round($countAccessing / $studentCount * 100, 0)
                            : 0;

                    // Keep legacy semantics:
                    // - "_at_50" column stores the absolute number of students at 100%.
                    // - "count_students_complete_all_activities" stores the percentage.
                    $rows[$rowIndex]['count_students_complete_all_activities_at_50'] =
                        $countComplete100;

                    $rows[$rowIndex]['count_students_complete_all_activities'] =
                        $countComplete100 > 0
                            ? round($countComplete100 / $studentCount * 100, 0)
                            : 0;

                    // Average progress across all enrolled students (0..100).
                    $rows[$rowIndex]['average_percentage_activities_completed_per_student'] =
                        $sumProgress > 0
                            ? round($sumProgress / $studentCount, 2)
                            : 0;
                }

                if ($sumTime > 0) {
                    $rows[$rowIndex]['total_time_spent'] = api_time_to_hms($sumTime);
                    $rows[$rowIndex]['average_time_spent_per_student'] =
                        api_time_to_hms($sumTime / max($studentCount, 1));
                }
            }

            // 6.b) Count LP items (documents, quizzes, links, forums, assignments).
            $sqlLpItems = "SELECT lpi.item_type
                           FROM $tLpItem lpi
                           WHERE lpi.lp_id = $lpId
                           ORDER BY lpi.item_type";
            $resLpItems = Database::query($sqlLpItems);

            while ($rowItem = Database::fetch_array($resLpItems)) {
                switch ($rowItem['item_type']) {
                    case 'document':
                        $rows[$rowIndex]['learnpath_docs']++;
                        break;
                    case 'quiz':
                        $rows[$rowIndex]['learnpath_exercises']++;
                        break;
                    case 'link':
                        $rows[$rowIndex]['learnpath_links']++;
                        break;
                    case 'forum':
                    case 'thread':
                        $rows[$rowIndex]['learnpath_forums']++;
                        break;
                    case 'student_publication':
                        $rows[$rowIndex]['learnpath_assignments']++;
                        break;
                }
            }

            // 6.c) Announcements count (optional – commented out for perf).
            /*
            $sqlNews = "SELECT COUNT(n.iid) AS total
                        FROM $tNews n
                        WHERE n.c_id = $courseId";
            $resNews = Database::query($sqlNews);
            if ($rowNews = Database::fetch_array($resNews)) {
                $rows[$rowIndex]['total_announcements'] = (int) $rowNews['total'];
            }
            */

            $rowIndex++;
        }
    }

    return $rows;
}