chamilo / chamilo-lms

/* For licensing terms, see /license.txt */

use Chamilo\CoreBundle\Entity\Course;

use Chamilo\CoreBundle\Entity\Session as SessionEntity;

use Chamilo\CourseBundle\Entity\CLpCategory;

use Chamilo\UserBundle\Entity\User;

use ChamiloSession as Session;

use CpChart\Cache as pCache;

use CpChart\Data as pData;

use CpChart\Image as pImage;

use ExtraField as ExtraFieldModel;

/**

 *  Class Tracking.

 *

 *  @author  Julio Montoya <[email protected]>

 */

class Tracking

{

    /**

     * Get group reporting.

     *

     * @param int    $course_id

     * @param int    $sessionId

     * @param int    $group_id

     * @param string $type

     * @param int    $start

     * @param int    $limit

     * @param int    $sidx

     * @param string $sord

     * @param array  $where_condition

     *

     * @return array|null

     */

    public static function get_group_reporting(

        $course_id,

        $sessionId = 0,

        $group_id = 0,

        $type = 'all',

        $start = 0,

        $limit = 1000,

        $sidx = 1,

        $sord = 'desc',

        $where_condition = []

    ) {

        $course_id = (int) $course_id;

        $sessionId = (int) $sessionId;

        if (empty($course_id)) {

            return null;

        }

        $courseInfo = api_get_course_info_by_id($course_id);

        if ('count' == $type) {

            return GroupManager::get_group_list(null, $courseInfo, null, $sessionId, true);

        }

        $groupList = GroupManager::get_group_list(null, $courseInfo, null, $sessionId);

        $parsedResult = [];

        if (!empty($groupList)) {

            foreach ($groupList as $group) {

                $users = GroupManager::get_users($group['id'], true, null, null, false, $courseInfo['real_id']);

                $time = 0;

                $avg_student_score = 0;

                $avg_student_progress = 0;

                $work = 0;

                $messages = 0;

                foreach ($users as $user_data) {

                    $time += self::get_time_spent_on_the_course(

                        $user_data['user_id'],

                        $courseInfo['real_id'],

                        $sessionId

                    );

                    $average = self::get_avg_student_score(

                        $user_data['user_id'],

                        $courseInfo['code'],

                        [],

                        $sessionId

                    );

                    if (is_numeric($average)) {

                        $avg_student_score += $average;

                    }

                    $avg_student_progress += self::get_avg_student_progress(

                        $user_data['user_id'],

                        $courseInfo['code'],

                        [],

                        $sessionId

                    );

                    $work += self::count_student_assignments(

                        $user_data['user_id'],

                        $courseInfo['code'],

                        $sessionId

                    );

                    $messages += self::count_student_messages(

                        $user_data['user_id'],

                        $courseInfo['code'],

                        $sessionId

                    );

                }

                $countUsers = count($users);

                $averageProgress = empty($countUsers) ? 0 : round($avg_student_progress / $countUsers, 2);

                $averageScore = empty($countUsers) ? 0 : round($avg_student_score / $countUsers, 2);

                $groupItem = [

                    'id' => $group['id'],

                    'name' => $group['name'],

                    'time' => api_time_to_hms($time),

                    'progress' => $averageProgress,

                    'score' => $averageScore,

                    'works' => $work,

                    'messages' => $messages,

                ];

                $parsedResult[] = $groupItem;

            }

        }

        return $parsedResult;

    }

    /**

     * Get the lp quizzes result as content for pdf export.

     *

     * @param $userId

     * @param $sessionId

     *

     * @return string

     */

    public static function getLpQuizContentToPdf(

        $userId,

        $sessionId

    ) {

        $tblLp = Database::get_course_table(TABLE_LP_MAIN);

        $tblLpItem = Database::get_course_table(TABLE_LP_ITEM);

        $tblLpItemView = Database::get_course_table(TABLE_LP_ITEM_VIEW);

        $tblLpView = Database::get_course_table(TABLE_LP_VIEW);

        $tblUser = Database::get_main_table(TABLE_MAIN_USER);

        $tblCourse = Database::get_main_table(TABLE_MAIN_COURSE);

        $courses = Tracking::get_courses_list_from_session($sessionId);

        // It creates lp quiz table html

        $lpQuizTable = '';

        if (!empty($courses)) {

            $lpQuizTable .= '<table class="data_table">';

            $lpQuizTable .= '<tbody>';

            $lpTitle = '';

            foreach ($courses as $course) {

                $courseId = $course['c_id'];

                $sql = 'SELECT DISTINCT c_lp_item.title AS quiz_name,

				c_lp.name AS lp_name,

				CASE WHEN c_lp_item_view.max_score>0 THEN CONCAT(ROUND((SUM(c_lp_item_view.score)/c_lp_item_view.max_score)*100),\'%\') ELSE 0 END AS score,

				SUM(c_lp_view.view_count) AS attempts

				FROM '.$tblLpView.' AS c_lp_view

				JOIN '.$tblUser.' AS user ON (user.user_id = c_lp_view.user_id)

				JOIN '.$tblCourse.' AS course ON (course.id = c_lp_view.c_id)

				JOIN '.$tblLp.' AS c_lp ON (c_lp.id = c_lp_view.lp_id AND c_lp.c_id = c_lp_view.c_id)

				JOIN '.$tblLpItem.' AS c_lp_item ON (c_lp_item.c_id = c_lp_view.c_id AND c_lp_item.lp_id = c_lp_view.lp_id)

				LEFT JOIN '.$tblLpItemView.' AS c_lp_item_view ON (c_lp_item_view.lp_item_id = c_lp_item.id AND c_lp_item_view.lp_view_id = c_lp_view.id AND c_lp_item_view.c_id = c_lp_view.c_id)

				WHERE

				    user.id = '.$userId.' AND

				    course.id = '.$courseId.' AND

				    c_lp_item.item_type = \'quiz\' AND

				    c_lp_view.session_id = '.$sessionId.'

				GROUP BY quiz_name, lp_name

				ORDER BY c_lp_item.display_order';

                $result = Database::query($sql);

                while ($row = Database::fetch_array($result)) {

                    if ($lpTitle != $row['lp_name']) {

                        $lpTitle = $row['lp_name'];

                        $lpQuizTable .= '<tr>

                            <th>'.stripslashes($lpTitle).'</th>

                            <th>'.get_lang('Score').'</th>

                            <th>'.get_lang('Attempts').'</th>

                        </tr>';

                    }

                    $lpQuizTable .= '<tr>

                        <td>'.$row['quiz_name'].'</td>

                        <td>'.$row['score'].'</td>

                        <td>'.$row['attempts'].'</td>

                    </tr>';

                }

            }

            $lpQuizTable .= '</tbody></table>';

        }

        return $lpQuizTable;

    }

    /**

     * Get the tables html as contents to export lp certificate pdf .

     *

     * @return array

     */

    public static function getLpCertificateTablesToPdf(

        $userId,

        $sessionId

    ) {

        $tblTrackCourseAccess = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);

        $tblLp = Database::get_course_table(TABLE_LP_MAIN);

        $tblLpItem = Database::get_course_table(TABLE_LP_ITEM);

        $tblLpItemView = Database::get_course_table(TABLE_LP_ITEM_VIEW);

        $tblLpView = Database::get_course_table(TABLE_LP_VIEW);

        $tblUser = Database::get_main_table(TABLE_MAIN_USER);

        $tblCourse = Database::get_main_table(TABLE_MAIN_COURSE);

        $courses = Tracking::get_courses_list_from_session($sessionId);

        // It creates the progress table html

        $progressTable = '';

        if (!empty($courses)) {

            $timeSpent = 0;

            $numberVisits = 0;

            $progress = 0;

            foreach ($courses as $course) {

                $courseId = $course['c_id'];

                $timeSpent += Tracking::get_time_spent_on_the_course($userId, $courseId, $sessionId);

                $sql = 'SELECT DISTINCT count(course_access_id) as count

                    FROM '.$tblTrackCourseAccess.'

                    WHERE

                        user_id = '.$userId.' AND

                        c_id = '.$courseId.' AND

                        session_id = '.$sessionId.'

                    ORDER BY login_course_date ASC';

                $result = Database::query($sql);

                $row = Database::fetch_array($result);

                $numberVisits += $row['count'];

                $progress += Tracking::get_avg_student_progress($userId, $course['code'], [], $sessionId);

            }

            $average = round($progress / count($courses), 1);

            $average = empty($average) ? '0%' : $average.'%';

            $first = Tracking::get_first_connection_date($userId);

            $last = Tracking::get_last_connection_date($userId);

            $table = new HTML_Table(['class' => 'data_table']);

            $column = 0;

            $row = 0;

            $headers = [

                get_lang('TimeSpent'),

                get_lang('NumberOfVisits'),

                get_lang('GlobalProgress'),

                get_lang('FirstLogin'),

                get_lang('LastConnexionDate'),

            ];

            foreach ($headers as $header) {

                $table->setHeaderContents($row, $column, $header);

                $column++;

            }

            $table->setCellContents(1, 0, api_time_to_hms($timeSpent));

            $table->setCellContents(1, 1, $numberVisits);

            $table->setCellContents(1, 2, $average);

            $table->setCellContents(1, 3, $first);

            $table->setCellContents(1, 4, $last);

            $progressTable = $table->toHtml();

        }

        // It creates the course table html

        $courseTable = '';

        if (!empty($courses)) {

            $totalCourseTime = 0;

            $totalAttendance = [0, 0];

            $totalScore = 0;

            $totalProgress = 0;

            $gradeBookTotal = [0, 0];

            $totalEvaluations = '0/0 (0%)';

            $totalCourses = count($courses);

            $scoreDisplay = ScoreDisplay::instance();

            $courseTable .= '<table class="data_table">';

            $courseTable .= '<thead>';

            $courseTable .= '<tr>

                    <th>'.get_lang('ToolLearnpath').'</th>

                    <th>'.get_lang('ConnectionTime').'</th>

					<th>'.get_lang('NumberOfVisits').'</th>

                    <th>'.get_lang('Progress').'</th>

                    <th>'.get_lang('FirstLogin').'</th>

                    <th>'.get_lang('LastConnexion').'</th>

                </tr>';

            $courseTable .= '</thead>';

            $courseTable .= '<tbody>';

            foreach ($courses as $course) {

                $courseId = $course['c_id'];

                $courseInfoItem = api_get_course_info_by_id($courseId);

                $courseId = $courseInfoItem['real_id'];

                $courseCodeItem = $courseInfoItem['code'];

                $isSubscribed = CourseManager::is_user_subscribed_in_course(

                    $userId,

                    $courseCodeItem,

                    true,

                    $sessionId

                );

                if ($isSubscribed) {

                    $timeInSeconds = Tracking::get_time_spent_on_the_course(

                        $userId,

                        $courseId,

                        $sessionId

                    );

                    $totalCourseTime += $timeInSeconds;

                    $time_spent_on_course = api_time_to_hms($timeInSeconds);

                    $progress = Tracking::get_avg_student_progress(

                        $userId,

                        $courseCodeItem,

                        [],

                        $sessionId

                    );

                    $totalProgress += $progress;

                    $bestScore = Tracking::get_avg_student_score(

                        $userId,

                        $courseCodeItem,

                        [],

                        $sessionId,

                        false,

                        false,

                        true

                    );

                    if (is_numeric($bestScore)) {

                        $totalScore += $bestScore;

                    }

                    if ($progress > 0) {

                        $progress = empty($progress) ? '0%' : $progress.'%';

                        $score = empty($bestScore) ? '0%' : $bestScore.'%';

                        $sql = 'SELECT

                                    DISTINCT count(course_access_id) as count,

   							        date_format(min(login_course_date),\'%d/%m/%Y\') as first,

							        date_format(max(logout_course_date),\'%d/%m/%Y\') as last

                                FROM

                                    '.$tblTrackCourseAccess.'

                                WHERE

                                    user_id = '.$userId.' AND

                                    c_id = '.$courseId.' AND

                                    session_id = '.$sessionId;

                        $result = Database::query($sql);

                        $row = Database::fetch_array($result);

                        $numberVisitsByCourse = $row['count'];

                        $firstByCourse = $row['first'];

                        $lastByCourse = $row['last'];

                        $courseTable .= '<tr>

                        <td ><a href="'.$courseInfoItem['course_public_url'].'?id_session='.$sessionId.'">'.$courseInfoItem['title'].'</a></td>

                        <td >'.$time_spent_on_course.'</td>

						<td >'.$numberVisitsByCourse.'</td>

                        <td >'.$progress.'</td>

                        <td >'.$firstByCourse.'</td>

                        <td >'.$lastByCourse.'</td>';

                        $courseTable .= '</tr>';

                    }

                }

            }

            $totalAttendanceFormatted = $scoreDisplay->display_score($totalAttendance);

            $totalScoreFormatted = $scoreDisplay->display_score([$totalScore / $totalCourses, 100], SCORE_AVERAGE);

            $totalProgressFormatted = $scoreDisplay->display_score(

                [$totalProgress / $totalCourses, 100],

                SCORE_AVERAGE

            );

            $totalEvaluations = $scoreDisplay->display_score($gradeBookTotal);

            $totalTimeFormatted = api_time_to_hms($totalCourseTime);

            $courseTable .= '<tr>

                <th>'.get_lang('Total').'</th>

                <th>'.$totalTimeFormatted.'</th>

				<th>'.$numberVisits.'</th>

                <th>'.$totalProgressFormatted.'</th>

                <th>'.$first.'</th>

                <th>'.$last.'</th>

            </tr>';

            $courseTable .= '</tbody></table>';

        }

        // It creates the lps table html

        $lpTable = '';

        if (!empty($courses)) {

            $lpTable = '<table class="data_table">';

            $lpTable .= '<tbody>';

            $lpTitle = '';

            foreach ($courses as $course) {

                $courseId = $course['c_id'];

                $sql = 'SELECT DISTINCT c_lp.name AS lp_name,

				c_lp_item.title AS chapter,

				SEC_TO_TIME(c_lp_item_view.total_time) AS temps,

				c_lp_item_view.status AS etat,

				SUM(c_lp_view.view_count) AS tentatives,

				c_lp_item_2.title AS chapter2,

				SEC_TO_TIME(c_lp_item_view_2.total_time) AS temps2,

				c_lp_item_view_2.status AS etat2

				FROM '.$tblLpView.' AS c_lp_view

				JOIN '.$tblUser.' AS user ON user.user_id = c_lp_view.user_id

				JOIN '.$tblCourse.' AS course ON course.id = c_lp_view.c_id

				JOIN '.$tblLp.' AS c_lp ON (c_lp.id = c_lp_view.lp_id AND c_lp.c_id = c_lp_view.c_id)

				JOIN '.$tblLpItem.' AS c_lp_item ON (c_lp_item.c_id = c_lp_view.c_id AND c_lp_item.lp_id = c_lp_view.lp_id)

				LEFT JOIN '.$tblLpItemView.' AS c_lp_item_view ON (c_lp_item_view.lp_item_id = c_lp_item.id AND c_lp_item_view.lp_view_id = c_lp_view.id AND c_lp_item_view.c_id = c_lp_view.c_id)

                LEFT JOIN '.$tblLpItem.' AS c_lp_item_2 ON (c_lp_item_2.parent_item_id > 0 AND c_lp_item_2.parent_item_id = c_lp_item.id)

				LEFT JOIN '.$tblLpItemView.' AS c_lp_item_view_2 ON (c_lp_item_view_2.lp_item_id = c_lp_item_2.id AND c_lp_item_view_2.lp_view_id = c_lp_view.id AND c_lp_item_view_2.c_id = c_lp_view.c_id)

				WHERE

				    user.id = '.$userId.' AND

				    course.id = '.$courseId.' AND

                    c_lp_view.session_id = '.$sessionId.' AND

				    c_lp_item.parent_item_id = 0

				GROUP BY

				    lp_name, chapter, chapter2

				ORDER BY

				    lp_name, c_lp_item.display_order';

                $result = Database::query($sql);

                $chapterTitle = false;

                while ($row = Database::fetch_array($result)) {

                    if ($lpTitle != $row['lp_name']) {

                        $lpTitle = $row['lp_name'];

                        $lpTable .= '<tr>

                        <th>'.stripslashes($lpTitle).'</th>

                        <th>'.get_lang('Duration').'</th>

                        <th>'.get_lang('Progress').'</th>

                    </tr>';

                    }

                    if ('' == $row['chapter2']) {

                        $progression = $row['etat'];

                        if (('completed' === $progression) || ('passed' === $progression)) {

                            $progression = get_lang('Done');

                        } else {

                            $progression = get_lang('Incomplete');

                        }

                        $lpTable .= '<tr>

                        <td>'.$row['chapter'].'</td>

                        <td>'.$row['temps'].'</td>

                        <td>'.$progression.'</td>

                    </tr>';

                        $chapterTitle = false;

                    } else {

                        if (false == $chapterTitle) {

                            $lpTable .= '<tr>

                            <td>'.$row['chapter'].'</td>

                            <td></td>

                            <td></td>

                        </tr>';

                            $chapterTitle = true;

                        }

                        $progression = $row['etat2'];

                        if ('completed' == $progression) {

                            $progression = get_lang('Done');

                        } else {

                            $progression = get_lang('Incomplete');

                        }

                        $lpTable .= '<tr>

                        <td>&nbsp;-&nbsp;'.$row['chapter2'].'</td>

                        <td>'.$row['temps2'].'</td>

                        <td>'.$progression.'</td>

                    </tr>';

                    }

                }

            }

            $lpTable .= '</tbody></table>';

        }

        return ['progress_table' => $progressTable, 'course_table' => $courseTable, 'lp_table' => $lpTable];

    }

    /**

     * It gets table html of Lp stats used to export in pdf.

     *

     * @return string

     */

    public static function getLpStatsContentToPdf(

        $userId,

        $courseInfo,

        $sessionId,

        $lpId,

        $lpName

    ) {

        $hideTime = api_get_configuration_value('hide_lp_time');

        $lpId = (int) $lpId;

        $userId = (int) $userId;

        $sessionId = (int) $sessionId;

        $courseId = $courseInfo['real_id'];

        $isAllowedToEdit = api_is_allowed_to_edit(null, true);

        $sessionCondition = api_get_session_condition($sessionId);

        $counter = 0;

        $totalTime = 0;

        $h = get_lang('h');

        $resultDisabledExtAll = true;

        $timeHeader = '<th>'.get_lang('ScormTime').'</th>';

        if ($hideTime) {

            $timeHeader = '';

        }

        $output = '<h2 class="clearfix text-center">'.$lpName.'</h2>';

        $output .= '<table class="table table-hover table-striped data_table">

            <thead>

                <tr>

                <th>'.get_lang('ScormLessonTitle').'</th>

                <th>'.get_lang('ScormStatus').'</th>

                <th>'.get_lang('ScormScore').'</th>

                '.$timeHeader.'

                </tr>

            </thead>

            <tbody>

        ';

        $tblLpItem = Database::get_course_table(TABLE_LP_ITEM);

        $tblLpItemView = Database::get_course_table(TABLE_LP_ITEM_VIEW);

        $tblLpView = Database::get_course_table(TABLE_LP_VIEW);

        $tblQuizQuestions = Database::get_course_table(TABLE_QUIZ_QUESTION);

        $tblQuiz = Database::get_course_table(TABLE_QUIZ_TEST);

        $tblStatsExercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);

        $tblStatsAttempts = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);

        // it gets the max view

        $sql = "SELECT max(view_count) FROM $tblLpView WHERE c_id = $courseId AND lp_id = $lpId AND user_id = $userId $sessionCondition";

        $res = Database::query($sql);

        $view = 0;

        $viewCondition = "";

        if (Database::num_rows($res) > 0) {

            $view = Database::result($res, 0, 0);

            $viewCondition = " AND v.view_count = ".(int) $view;

        }

        $chapterTypes = learnpath::getChapterTypes();

        $minimumAvailable = self::minimumTimeAvailable($sessionId, $courseId);

        $list = learnpath::get_flat_ordered_items_list($lpId, 0, $courseId);

        if (is_array($list) && count($list) > 0) {

            foreach ($list as $myItemId) {

                $sql = "SELECT

                    iv.status as mystatus,

                    v.view_count as mycount,

                    iv.score as myscore,

                    iv.total_time as mytime,

                    i.iid as myid,

                    i.lp_id as mylpid,

                    iv.lp_view_id as mylpviewid,

                    i.title as mytitle,

                    i.max_score as mymaxscore,

                    iv.max_score as myviewmaxscore,

                    i.item_type as item_type,

                    iv.view_count as iv_view_count,

                    iv.id as iv_id,

                    path

                FROM $tblLpItem as i

                INNER JOIN $tblLpItemView as iv

                ON (i.iid = iv.lp_item_id AND i.c_id = iv.c_id)

                INNER JOIN $tblLpView as v

                ON (iv.lp_view_id = v.id AND v.c_id = iv.c_id)

                WHERE

                    v.c_id = $courseId AND

                    i.iid = $myItemId AND

                    i.lp_id = $lpId  AND

                    v.user_id = $userId AND

                    v.session_id = $sessionId

                    $viewCondition

                ORDER BY iv.view_count";

                $result = Database::query($sql);

                $num = Database::num_rows($result);

                $timeForTotal = 0;

                if ($num > 0) {

                    // Not extended.

                    $row = Database::fetch_array($result, 'ASSOC');

                    $myId = $row['myid'];

                    $myLpId = $row['mylpid'];

                    $myLpViewId = $row['mylpviewid'];

                    $lpItemPath = (int) $row['path'];

                    $resultDisabledExtAll = false;

                    if ($row['item_type'] === 'quiz') {

                        // Check results_disabled in quiz table.

                        $sql = "SELECT results_disabled

                                FROM $tblQuiz

                                WHERE iid = $lpItemPath";

                        $resResultDisabled = Database::query($sql);

                        $rowResultDisabled = Database::fetch_row($resResultDisabled);

                        if (Database::num_rows($resResultDisabled) > 0 && 1 === (int) $rowResultDisabled[0]) {

                            $resultDisabledExtAll = true;

                        }

                    }

                    // Check if there are interactions below

                    $extendThisAttempt = 0;

                    $interNum = learnpath::get_interactions_count_from_db($row['iv_id'], $courseId);

                    $objecNum = learnpath::get_objectives_count_from_db($row['iv_id'], $courseId);

                    if ($interNum > 0 || $objecNum > 0) {

                        $extendThisAttempt = 1;

                    }

                    $lesson_status = $row['mystatus'];

                    $score = $row['myscore'];

                    $subtotalTime = $row['mytime'];

                    while ($tmp_row = Database::fetch_array($result)) {

                        $subtotalTime += $tmp_row['mytime'];

                    }

                    $title = $row['mytitle'];

                    // Selecting the exe_id from stats attempts tables in order to look the max score value.

                    $sql = 'SELECT * FROM '.$tblStatsExercises.'

                            WHERE

                                exe_exo_id="'.$row['path'].'" AND

                                exe_user_id="'.$userId.'" AND

                                orig_lp_id = "'.$lpId.'" AND

                                orig_lp_item_id = "'.$row['myid'].'" AND

                                c_id = '.$courseId.' AND

                                status <> "incomplete" AND

                                session_id = '.$sessionId.'

                             ORDER BY exe_date DESC

                             LIMIT 1';

                    $resultLastAttempt = Database::query($sql);

                    $num = Database::num_rows($resultLastAttempt);

                    $idLastAttempt = null;

                    if ($num > 0) {

                        while ($rowLA = Database::fetch_array($resultLastAttempt)) {

                            $idLastAttempt = $rowLA['exe_id'];

                        }

                    }

                    switch ($row['item_type']) {

                        case 'sco':

                            if (!empty($row['myviewmaxscore']) && $row['myviewmaxscore'] > 0) {

                                $maxscore = $row['myviewmaxscore'];

                            } elseif ($row['myviewmaxscore'] === '') {

                                $maxscore = 0;

                            } else {

                                $maxscore = $row['mymaxscore'];

                            }

                            break;

                        case 'quiz':

                            // Get score and total time from last attempt of an exercise en lp.

                            $sql = "SELECT iid, score

                                    FROM $tblLpItemView

                                    WHERE

                                        c_id = $courseId AND

                                        lp_item_id = '".(int) $myId."' AND

                                        lp_view_id = '".(int) $myLpViewId."'

                                    ORDER BY view_count DESC

                                    LIMIT 1";

                            $resScore = Database::query($sql);

                            $rowScore = Database::fetch_array($resScore);

                            $sql = "SELECT SUM(total_time) as total_time

                                    FROM $tblLpItemView

                                    WHERE

                                        c_id = $courseId AND

                                        lp_item_id = '".(int) $myId."' AND

                                        lp_view_id = '".(int) $myLpViewId."'";

                            $resTime = Database::query($sql);

                            $rowTime = Database::fetch_array($resTime);

                            $score = 0;

                            $subtotalTime = 0;

                            if (Database::num_rows($resScore) > 0 && Database::num_rows($resTime) > 0) {

                                $score = (float) $rowScore['score'];

                                $subtotalTime = (int) $rowTime['total_time'];

                            }

                            // Selecting the max score from an attempt.

                            $sql = "SELECT SUM(t.ponderation) as maxscore

                                    FROM (

                                        SELECT DISTINCT

                                            question_id, marks, ponderation

                                        FROM $tblStatsAttempts as at

                                        INNER JOIN $tblQuizQuestions as q

                                        ON q.iid = at.question_id

                                        WHERE exe_id ='$idLastAttempt'

                                    ) as t";

                            $result = Database::query($sql);

                            $rowMaxScore = Database::fetch_array($result);

                            $maxscore = $rowMaxScore['maxscore'];

                            // Get duration time from track_e_exercises.exe_duration instead of lp_view_item.total_time

                            $sql = 'SELECT SUM(exe_duration) exe_duration

                                    FROM '.$tblStatsExercises.'

                                    WHERE

                                        exe_exo_id="'.$row['path'].'" AND

                                        exe_user_id="'.$userId.'" AND

                                        orig_lp_id = "'.$lpId.'" AND

                                        orig_lp_item_id = "'.$row['myid'].'" AND

                                        c_id = '.$courseId.' AND

                                        status <> "incomplete" AND

                                        session_id = '.$sessionId.'

                                     ORDER BY exe_date DESC ';

                            $sumScoreResult = Database::query($sql);

                            $durationRow = Database::fetch_array($sumScoreResult, 'ASSOC');

                            if (!empty($durationRow['exe_duration'])) {

                                $exeDuration = $durationRow['exe_duration'];

                                if ($exeDuration != $subtotalTime && !empty($rowScore['iid']) && !empty($exeDuration)) {

                                    $subtotalTime = $exeDuration;

                                    // Update c_lp_item_view.total_time

                                    $sqlUpdate = "UPDATE $tblLpItemView SET total_time = '$exeDuration' WHERE iid = ".$rowScore['iid'];

                                    Database::query($sqlUpdate);

                                }

                            }

                            break;

                        default:

                            $maxscore = $row['mymaxscore'];

                            break;

                    }

                    $timeForTotal = $subtotalTime;

                    $time = learnpathItem::getScormTimeFromParameter('js', $subtotalTime);

                    if (empty($title)) {

                        $title = learnpath::rl_get_resource_name(

                            $courseInfo['code'],

                            $lpId,

                            $row['myid']

                        );

                    }

                    if (in_array($row['item_type'], $chapterTypes)) {

                        $title = Security::remove_XSS($title);

                        $output .= '<tr>

                                <td colspan="4"><h4>'.$title.'</h4></td>

                        </tr>';

                    } else {

                        if ('quiz' === $row['item_type']) {

                            $sql = 'SELECT * FROM '.$tblStatsExercises.'

                                     WHERE

                                        exe_exo_id="'.$row['path'].'" AND

                                        exe_user_id="'.$userId.'" AND

                                        orig_lp_id = "'.$lpId.'" AND

                                        orig_lp_item_id = "'.$row['myid'].'" AND

                                        c_id = '.$courseId.' AND

                                        status <> "incomplete" AND

                                        session_id = '.$sessionId.'

                                     ORDER BY exe_date DESC ';

                            $resultLastAttempt = Database::query($sql);

                            $num = Database::num_rows($resultLastAttempt);

                        }

                        $title = Security::remove_XSS($title);

                        if ($lpId == $myLpId && false) {

                            $output .= '<tr>

                                    <td>'.$title.'</td>

                                    <td>&nbsp;</td>

                                    <td>&nbsp;</td>

                                    <td>&nbsp;</td>

                            </tr>';

                        } else {

                            $output .= "<tr>";

                            $scoreItem = null;

                            if ($row['item_type'] === 'quiz') {

                                $scoreItem .= ExerciseLib::show_score($score, $maxscore, false);

                            } else {

                                $scoreItem .= $score == 0 ? '/' : ($maxscore == 0 ? $score : $score.'/'.$maxscore);

                            }

                            $timeRow = '<td style="width:20%;text-align:center;">'.$time.'</td>';

                            if ($hideTime) {

                                $timeRow = '';

                            }

                            $output .= '

                                <td style="width:35%;font-weight:bold;">'.$title.'</td>

                                <td style="width:25%;text-align:center;"><div class="btn btn-primary boldTitle">'.learnpathitem::humanize_status($lesson_status, false).'</div></td>

                                <td style="width:20%;text-align:center;">'.$scoreItem.'</td>

                                '.$timeRow.'

                             ';

                            $output .= '</tr>';

                        }

                    }

                    $counter++;

                    if ($extendThisAttempt) {

                        $list1 = learnpath::get_iv_interactions_array($row['iv_id'], $courseId);

                        foreach ($list1 as $id => $interaction) {

                            $timeRow = '<td>'.$interaction['time'].'</td>';

                            if ($hideTime) {

                                $timeRow = '';

                            }

                            $output .= '<tr>

                                    <td>'.$interaction['order_id'].'</td>

                                    <td>'.$interaction['id'].'</td>

                                    <td>'.$interaction['type'].'</td>

                                    <td>'.urldecode($interaction['student_response']).'</td>

                                    <td>'.$interaction['result'].'</td>

                                    <td>'.$interaction['latency'].'</td>

                                    '.$timeRow.'

                               </tr>';

                            $counter++;

                        }

                        $list2 = learnpath::get_iv_objectives_array($row['iv_id'], $courseId);

                        foreach ($list2 as $id => $interaction) {

                            $output .= '<tr>

                                    <td>'.$interaction['order_id'].'</td>

                                    <td>'.$interaction['objective_id'].'</td>

                                    <td>'.$interaction['status'].'</td>

                                    <td>'.$interaction['score_raw'].'</td>

                                    <td>'.$interaction['score_max'].'</td>

                                    <td>'.$interaction['score_min'].'</td>

                               </tr>';

                            $counter++;

                        }

                    }

                    // Attempts listing by exercise.

                    if ($lpId == $myLpId) {

                        // Get attempts of a exercise.

                        if (!empty($lpId) && 'quiz' === $row['item_type']) {

                            $sql = "SELECT path FROM $tblLpItem

                                    WHERE

                                        c_id = $courseId AND

                                        lp_id = '$lpId'";

                            $resPath = Database::query($sql);

                            if (Database::num_rows($resPath) > 0) {

                                while ($rowPath = Database::fetch_array($resPath)) {

                                    $sql = 'SELECT * FROM '.$tblStatsExercises.'

                                            WHERE

                                                exe_exo_id="'.(int) $rowPath['path'].'" AND

                                                status <> "incomplete" AND

                                                exe_user_id="'.$userId.'" AND

                                                orig_lp_id = "'.$lpId.'" AND

                                                orig_lp_item_id = "'.$myItemId.'" AND

                                                c_id = '.$courseId.'  AND

                                                session_id = '.$sessionId.'

                                            ORDER BY exe_date';

                                    $resAttempts = Database::query($sql);

                                    if (Database::num_rows($resAttempts) > 0) {

                                        $n = 1;

                                        while ($rowAttempts = Database::fetch_array($resAttempts)) {

                                            $myScore = $rowAttempts['exe_result'];

                                            $myMaxscore = $rowAttempts['exe_weighting'];

                                            $mktimeStartDate = api_strtotime($rowAttempts['start_date'], 'UTC');

                                            $mktimeExeDate = api_strtotime($rowAttempts['exe_date'], 'UTC');

                                            $timeAttempt = ' - ';

                                            if ($mktimeStartDate && $mktimeExeDate) {

                                                $timeAttempt = api_format_time($rowAttempts['exe_duration'], 'js');

                                            }

                                            // Show only float when need it

                                            if ($myScore == 0) {

                                                $viewScore = ExerciseLib::show_score(

                                                    0,

                                                    $myMaxscore,

                                                    false

                                                );

                                            } else {

                                                if ($myMaxscore == 0) {

                                                    $viewScore = $myScore;

                                                } else {

                                                    $viewScore = ExerciseLib::show_score(

                                                        $myScore,

                                                        $myMaxscore,

                                                        false

                                                    );

                                                }

                                            }

                                            $myLessonStatus = $rowAttempts['status'];

                                            if ($myLessonStatus == '') {

                                                $myLessonStatus = learnpathitem::humanize_status('completed', false);

                                            } elseif ($myLessonStatus == 'incomplete') {

                                                $myLessonStatus = learnpathitem::humanize_status('incomplete', false);

                                            }

                                            $timeRow = '<td style="text-align:center;">'.$timeAttempt.'</td>';

                                            if ($hideTime) {

                                                $timeRow = '';

                                            }

                                            $output .= '<tr>

                                            <td>&mdash; <em>'.get_lang('Attempt').' '.$n.'</em></td>

                                            <td style="text-align:center;"><div class="btn btn-primary boldTitle">'.$myLessonStatus.'</div></td>

                                            <td style="text-align:center;">'.$viewScore.'</td>

                                            '.$timeRow;

                                            $output .= '</tr>';

                                            // It displays categories questions by attempts

                                            $categoriesQuestions = TestCategory::getQuestionsByCat($rowAttempts['exe_exo_id'], [], [], false, $courseId);

                                            $categories = TestCategory::getListOfCategoriesIDForTest($rowAttempts['exe_exo_id'], $courseId);

                                            $objExercise = new Exercise($courseId);

                                            $objExercise->read($rowAttempts['exe_exo_id']);

                                            if (!empty($categoriesQuestions)) {

                                                foreach ($categoriesQuestions as $catId => $questions) {

                                                    $catScore = 0;

                                                    $catWeight = 0;

                                                    if (!empty($questions)) {

                                                        foreach ($questions as $questionId) {

                                                            $questionResult = $objExercise->manage_answer(

                                                                $rowAttempts['exe_id'],

                                                                $questionId,

                                                                '',

                                                                'exercise_show',

                                                                [],

                                                                false,

                                                                true,

                                                                false,

                                                                $objExercise->selectPropagateNeg()

                                                            );

                                                            $catScore += $questionResult['score'];

                                                            $catWeight += $questionResult['weight'];

                                                        }

                                                    }

                                                    $timeCatRow = '';

                                                    if (!$hideTime) {

                                                        $timeCatRow = '<td></td>';

                                                    }

                                                    $catTitle = $categories[$catId]['title'];

                                                    $catScoreDisplay = ($catWeight > 0) ? round(($catScore * 100) / $catWeight).'% ('.$catScore.'/'.$catWeight.')' : '';

                                                    $output .= "<tr>";

                                                    $output .= '

                                                        <td style="width:35%">&mdash;&mdash; '.$catTitle.'</td>

                                                        <td style="width:25%;"></td>

                                                        <td style="width:20%;text-align:center;">'.$catScoreDisplay.'</td>

                                                        '.$timeCatRow.'

                                                     ';

                                                    $output .= '</tr>';

                                                }

                                            }

                                            $n++;

                                        }

                                    }

                                }

                            }

                        }

                    }

                }

                $totalTime += $timeForTotal;

            }

        }

        $totalScore = self::get_avg_student_score(

            $userId,

            $courseInfo['code'],

            [$lpId],

            $sessionId,

            false,

            false

        );

        $totalTime = learnpathItem::getScormTimeFromParameter('js', $totalTime);

        $totalTime = str_replace('NaN', '00'.$h.'00\'00"', $totalTime);

        if (!$isAllowedToEdit && $resultDisabledExtAll) {

            $finalScore = Display::return_icon('invisible.png', get_lang('ResultsHiddenByExerciseSetting'));

        } else {

            if (is_numeric($totalScore)) {

                $finalScore = $totalScore.'%';

            } else {

                $finalScore = $totalScore;

            }

        }

        $progress = learnpath::getProgress($lpId, $userId, $courseId, $sessionId);

        $timeTotal = '<th style="text-align:center;">'.$totalTime.'</th>';

        if ($hideTime) {

            $timeTotal = '';

        }

        $output .= '<tr>

                <th><i>'.get_lang('AccomplishedStepsTotal').'</i></th>

                <th style="text-align:center;">'.$progress.'%</th>

                <th style="text-align:center;">'.$finalScore.'</th>

                '.$timeTotal.'

           </tr></tbody></table>';

        return $output;

    }

    /**

     * @param int    $user_id

     * @param array  $courseInfo

     * @param int    $session_id

     * @param string $origin

     * @param bool   $export_csv

     * @param int    $lp_id

     * @param int    $lp_item_id

     * @param int    $extendId

     * @param int    $extendAttemptId

     * @param string $extendedAttempt

     * @param string $extendedAll

     * @param string $type            classic or simple

     * @param bool   $allowExtend     Optional. Allow or not extend te results

     *

     * @return string

     */

    public static function getLpStats(

        $user_id,

        $courseInfo,

        $session_id,

        $origin,

        $export_csv,

        $lp_id,

        $lp_item_id = null,

        $extendId = null,

        $extendAttemptId = null,

        $extendedAttempt = null,

        $extendedAll = null,

        $type = 'classic',

        $allowExtend = true

    ) {

        if (empty($courseInfo) || empty($lp_id)) {

            return '';

        }

        $hideTime = api_get_configuration_value('hide_lp_time');

        $lp_id = (int) $lp_id;

        $lp_item_id = (int) $lp_item_id;

        $user_id = (int) $user_id;

        $session_id = (int) $session_id;

        $origin = Security::remove_XSS($origin);

        $list = learnpath::get_flat_ordered_items_list($lp_id, 0, $courseInfo['real_id']);

        $is_allowed_to_edit = api_is_allowed_to_edit(null, true);

        $course_id = $courseInfo['real_id'];

        $courseCode = $courseInfo['code'];

        $session_condition = api_get_session_condition($session_id);

        // Extend all button

        $output = '';

        $extra = '<script>

        $(function() {

            $( "#dialog:ui-dialog" ).dialog( "destroy" );

            $( "#dialog-confirm" ).dialog({

                autoOpen: false,

                show: "blind",

                resizable: false,

                height:300,

                modal: true

            });

            $(".export").click(function() {

                var targetUrl = $(this).attr("href");

                $( "#dialog-confirm" ).dialog({

                    width:400,

                    height:300,

                    buttons: {

                        "'.addslashes(get_lang('Download')).'": function() {

                            var option = $("input[name=add_logo]:checked").val();

                            location.href = targetUrl+"&add_logo="+option;

                            $(this).dialog("close");

                        }

                    }

                });

                $("#dialog-confirm").dialog("open");

                return false;

            });

        });

        </script>';

        $extra .= '<div id="dialog-confirm" title="'.get_lang('ConfirmYourChoice').'">';

        $form = new FormValidator('report', 'post', null, null, ['class' => 'form-vertical']);

        $form->addCheckBox('add_logo', '', get_lang('AddRightLogo'), ['id' => 'export_format_csv_label']);

        $extra .= $form->returnForm();

        $extra .= '</div>';

        $output .= $extra;

        $url_suffix = '&lp_id='.$lp_id;

        if ('tracking' === $origin) {

            $url_suffix = '&session_id='.$session_id.'&course='.$courseCode.'&student_id='.$user_id.'&lp_id='.$lp_id.'&origin='.$origin;

        }

        $extend_all = 0;

        if (!empty($extendedAll)) {

            $extend_all_link = Display::url(

                Display::return_icon('view_less_stats.gif', get_lang('HideAllAttempts')),

                api_get_self().'?action=stats'.$url_suffix

            );

            $extend_all = 1;

        } else {

            $extend_all_link = Display::url(

                Display::return_icon('view_more_stats.gif', get_lang('ShowAllAttempts')),

                api_get_self().'?action=stats&extend_all=1'.$url_suffix

            );

        }

        if ($origin != 'tracking') {

            $output .= '<div class="section-status">';

            $output .= Display::page_header(get_lang('ScormMystatus'));

            $output .= '</div>';

        }

        $actionColumn = null;

        if ($type === 'classic') {

            $actionColumn = ' <th>'.get_lang('Actions').'</th>';

        }

        $timeHeader = '<th class="lp_time" colspan="2">'.get_lang('ScormTime').'</th>';

        if ($hideTime) {

            $timeHeader = '';

        }

        $output .= '<div class="table-responsive">';

        $output .= '<table id="lp_tracking" class="table tracking">

            <thead>

            <tr class="table-header">

                <th width="16">'.($allowExtend == true ? $extend_all_link : '&nbsp;').'</th>

                <th colspan="4">

                    '.get_lang('ScormLessonTitle').'

                </th>

                <th colspan="2">

                    '.get_lang('ScormStatus').'

                </th>

                <th colspan="2">

                    '.get_lang('ScormScore').'

                </th>

                '.$timeHeader.'

                '.$actionColumn.'

                </tr>

            </thead>

            <tbody>

        ';

        // Going through the items using the $items[] array instead of the database order ensures

        // we get them in the same order as in the imsmanifest file, which is rather random when using

        // the database table.

        $TBL_LP_ITEM = Database::get_course_table(TABLE_LP_ITEM);

        $TBL_LP_ITEM_VIEW = Database::get_course_table(TABLE_LP_ITEM_VIEW);

        $TBL_LP_VIEW = Database::get_course_table(TABLE_LP_VIEW);

        $tbl_quiz_questions = Database::get_course_table(TABLE_QUIZ_QUESTION);

        $TBL_QUIZ = Database::get_course_table(TABLE_QUIZ_TEST);

        $tbl_stats_exercices = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);

        $tbl_stats_attempts = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);

        $sql = "SELECT max(view_count)

                FROM $TBL_LP_VIEW

                WHERE

                    c_id = $course_id AND

                    lp_id = $lp_id AND

                    user_id = $user_id

                    $session_condition";

        $res = Database::query($sql);

        $view = 0;

        if (Database::num_rows($res) > 0) {

            $myrow = Database::fetch_array($res);

            $view = (int) $myrow[0];

        }