Пример #1
0
/**
 * Gets the exam'data results
 * @todo this function should be moved in a library  + no global calls
 * @param int $from
 * @param int $number_of_items
 * @param int $column
 * @param string $direction
 * @param int $exercise_id
 * @param null $extra_where_conditions
 * @param bool $get_count
 * @return array
 */
function get_exam_results_data($from, $number_of_items, $column, $direction, $exercise_id, $extra_where_conditions = null, $get_count = false)
{
    //@todo replace all this globals
    global $documentPath, $filter;
    $course_id = api_get_course_int_id();
    $course_code = api_get_course_id();
    $sessionId = api_get_session_id();
    $is_allowedToEdit = api_is_allowed_to_edit(null, true) || api_is_allowed_to_edit(true) || api_is_drh();
    $TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
    $TBL_EXERCICES = Database::get_course_table(TABLE_QUIZ_TEST);
    $TBL_GROUP_REL_USER = Database::get_course_table(TABLE_GROUP_USER);
    $TBL_GROUP = Database::get_course_table(TABLE_GROUP);
    $TBL_TRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
    $TBL_TRACK_HOTPOTATOES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
    $TBL_TRACK_ATTEMPT_RECORDING = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
    $session_id_and = ' AND te.session_id = ' . $sessionId . ' ';
    $exercise_id = intval($exercise_id);
    $exercise_where = '';
    if (!empty($exercise_id)) {
        $exercise_where .= ' AND te.exe_exo_id = ' . $exercise_id . '  ';
    }
    $hotpotatoe_where = '';
    if (!empty($_GET['path'])) {
        $hotpotatoe_path = Database::escape_string($_GET['path']);
        $hotpotatoe_where .= ' AND exe_name = "' . $hotpotatoe_path . '"  ';
    }
    // sql for chamilo-type tests for teacher / tutor view
    $sql_inner_join_tbl_track_exercices = " (\n        SELECT DISTINCT ttte.*, if(tr.exe_id,1, 0) as revised\n        FROM {$TBL_TRACK_EXERCICES} ttte LEFT JOIN {$TBL_TRACK_ATTEMPT_RECORDING} tr\n        ON (ttte.exe_id = tr.exe_id)\n        WHERE\n            exe_cours_id = '{$course_code}' AND\n            exe_exo_id = {$exercise_id} AND\n            ttte.session_id = " . $sessionId . "\n    )";
    if ($is_allowedToEdit) {
        //Teacher view
        if (isset($_GET['gradebook']) && $_GET['gradebook'] == 'view') {
            //$exercise_where_query = ' te.exe_exo_id = ce.id AND ';
        }
        $sqlFromOption = "";
        $sqlWhereOption = "";
        // for hpsql
        //@todo fix to work with COURSE_RELATION_TYPE_RRHH in both queries
        //Hack in order to filter groups
        $sql_inner_join_tbl_user = '';
        if (strpos($extra_where_conditions, 'group_id')) {
            $sql_inner_join_tbl_user = "******" . $course_id . ")\n                INNER JOIN {$TBL_GROUP} g ON (gru.group_id = g.id AND g.c_id=" . $course_id . ")\n            )";
        }
        if (strpos($extra_where_conditions, 'group_all')) {
            $extra_where_conditions = str_replace("AND (  group_id = 'group_all'  )", '', $extra_where_conditions);
            $extra_where_conditions = str_replace("AND group_id = 'group_all'", '', $extra_where_conditions);
            $extra_where_conditions = str_replace("group_id = 'group_all' AND", '', $extra_where_conditions);
            $sql_inner_join_tbl_user = "******";
            $sql_inner_join_tbl_user = null;
        }
        if (strpos($extra_where_conditions, 'group_none')) {
            $extra_where_conditions = str_replace("AND (  group_id = 'group_none'  )", "AND (  group_id is null  )", $extra_where_conditions);
            $extra_where_conditions = str_replace("AND group_id = 'group_none'", "AND (  group_id is null  )", $extra_where_conditions);
            $sql_inner_join_tbl_user = "******" . $course_id . " )\n                LEFT OUTER JOIN {$TBL_GROUP} g ON (gru.group_id = g.id AND g.c_id = " . $course_id . ")\n            )";
        }
        // All
        $is_empty_sql_inner_join_tbl_user = false;
        if (empty($sql_inner_join_tbl_user)) {
            $is_empty_sql_inner_join_tbl_user = true;
            $sql_inner_join_tbl_user = "******";
        }
        $sqlFromOption = " , {$TBL_GROUP_REL_USER} AS gru ";
        $sqlWhereOption = "  AND gru.c_id = " . api_get_course_int_id() . " AND gru.user_id = user.user_id ";
        $first_and_last_name = api_is_western_name_order() ? "firstname, lastname" : "lastname, firstname";
        if ($get_count) {
            $sql_select = "SELECT count(te.exe_id) ";
        } else {
            $sql_select = "SELECT DISTINCT\n                    user_id,\n                    {$first_and_last_name},\n                    official_code,\n                    ce.title,\n                    username,\n                    te.exe_result,\n                    te.exe_weighting,\n                    te.exe_date,\n                    te.exe_id,\n                    email as exemail,\n                    te.start_date,\n                    steps_counter,\n                    exe_user_id,\n                    te.exe_duration,\n                    propagate_neg,\n                    revised,\n                    group_name,\n                    group_id,\n                    orig_lp_id";
        }
        $sql = " {$sql_select}\n                FROM {$TBL_EXERCICES} AS ce\n                INNER JOIN {$sql_inner_join_tbl_track_exercices} AS te ON (te.exe_exo_id = ce.id)\n                INNER JOIN {$sql_inner_join_tbl_user}  AS user ON (user.user_id = exe_user_id)\n                WHERE\n                    te.status != 'incomplete' AND\n                    te.exe_cours_id='" . api_get_course_id() . "' {$session_id_and} AND\n                    ce.active <>-1 AND ce.c_id=" . api_get_course_int_id() . "\n                    {$exercise_where}\n                    {$extra_where_conditions}\n                ";
        // sql for hotpotatoes tests for teacher / tutor view
        if ($get_count) {
            $hpsql_select = "SELECT count(username)";
        } else {
            $hpsql_select = "SELECT\n                    {$first_and_last_name} ,\n                    username,\n                    official_code,\n                    tth.exe_name,\n                    tth.exe_result ,\n                    tth.exe_weighting,\n                    tth.exe_date";
        }
        $hpsql = " {$hpsql_select}\n                FROM\n                    {$TBL_TRACK_HOTPOTATOES} tth,\n                    {$TBL_USER} user\n                    {$sqlFromOption}\n                WHERE\n                    user.user_id=tth.exe_user_id\n                    AND tth.exe_cours_id = '" . api_get_course_id() . "'\n                    {$hotpotatoe_where}\n                    {$sqlWhereOption}\n                ORDER BY\n                    tth.exe_cours_id ASC,\n                    tth.exe_date DESC";
    }
    if ($get_count) {
        $resx = Database::query($sql);
        $rowx = Database::fetch_row($resx, 'ASSOC');
        return $rowx[0];
    }
    $teacher_list = CourseManager::get_teacher_list_from_course_code(api_get_course_id());
    $teacher_id_list = array();
    foreach ($teacher_list as $teacher) {
        $teacher_id_list[] = $teacher['user_id'];
    }
    $list_info = array();
    // Simple exercises
    if (empty($hotpotatoe_where)) {
        $column = !empty($column) ? Database::escape_string($column) : null;
        $from = intval($from);
        $number_of_items = intval($number_of_items);
        if (!empty($column)) {
            $sql .= " ORDER BY {$column} {$direction} ";
        }
        $sql .= " LIMIT {$from}, {$number_of_items}";
        $results = array();
        $resx = Database::query($sql);
        while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
            $results[] = $rowx;
        }
        $group_list = GroupManager::get_group_list();
        $clean_group_list = array();
        if (!empty($group_list)) {
            foreach ($group_list as $group) {
                $clean_group_list[$group['id']] = $group['name'];
            }
        }
        $lp_list_obj = new learnpathList(api_get_user_id());
        $lp_list = $lp_list_obj->get_flat_list();
        if (is_array($results)) {
            $users_array_id = array();
            $from_gradebook = false;
            if (isset($_GET['gradebook']) && $_GET['gradebook'] == 'view') {
                $from_gradebook = true;
            }
            $sizeof = count($results);
            $user_list_id = array();
            $locked = api_resource_is_locked_by_gradebook($exercise_id, LINK_EXERCISE);
            // Looping results
            for ($i = 0; $i < $sizeof; $i++) {
                $revised = $results[$i]['revised'];
                if ($from_gradebook && $is_allowedToEdit) {
                    if (in_array($results[$i]['username'] . $results[$i]['firstname'] . $results[$i]['lastname'], $users_array_id)) {
                        continue;
                    }
                    $users_array_id[] = $results[$i]['username'] . $results[$i]['firstname'] . $results[$i]['lastname'];
                }
                $lp_obj = isset($results[$i]['orig_lp_id']) && isset($lp_list[$results[$i]['orig_lp_id']]) ? $lp_list[$results[$i]['orig_lp_id']] : null;
                $lp_name = null;
                if ($lp_obj) {
                    $url = api_get_path(WEB_CODE_PATH) . 'newscorm/lp_controller.php?' . api_get_cidreq() . '&action=view&lp_id=' . $results[$i]['orig_lp_id'];
                    $lp_name = Display::url($lp_obj['lp_name'], $url, array('target' => '_blank'));
                }
                //Add all groups by user
                $group_name_list = null;
                if ($is_empty_sql_inner_join_tbl_user) {
                    $group_list = GroupManager::get_group_ids(api_get_course_int_id(), $results[$i]['user_id']);
                    foreach ($group_list as $id) {
                        $group_name_list .= $clean_group_list[$id] . '<br/>';
                    }
                    $results[$i]['group_name'] = $group_name_list;
                }
                $results[$i]['exe_duration'] = !empty($results[$i]['exe_duration']) ? round($results[$i]['exe_duration'] / 60) : 0;
                $user_list_id[] = $results[$i]['exe_user_id'];
                $id = $results[$i]['exe_id'];
                $dt = api_convert_and_format_date($results[$i]['exe_weighting']);
                // we filter the results if we have the permission to
                if (isset($results[$i]['results_disabled'])) {
                    $result_disabled = intval($results[$i]['results_disabled']);
                } else {
                    $result_disabled = 0;
                }
                if ($result_disabled == 0) {
                    $my_res = $results[$i]['exe_result'];
                    $my_total = $results[$i]['exe_weighting'];
                    $results[$i]['start_date'] = api_get_local_time($results[$i]['start_date']);
                    $results[$i]['exe_date'] = api_get_local_time($results[$i]['exe_date']);
                    if (!$results[$i]['propagate_neg'] && $my_res < 0) {
                        $my_res = 0;
                    }
                    $score = show_score($my_res, $my_total);
                    $actions = '';
                    if ($is_allowedToEdit) {
                        if (isset($teacher_id_list)) {
                            if (in_array($results[$i]['exe_user_id'], $teacher_id_list)) {
                                $actions .= Display::return_icon('teachers.gif', get_lang('Teacher'));
                            }
                        }
                        if ($revised) {
                            $actions .= "<a href='exercise_show.php?" . api_get_cidreq() . "&action=edit&id={$id}'>" . Display::return_icon('edit.png', get_lang('Edit'), array(), ICON_SIZE_SMALL);
                            $actions .= '&nbsp;';
                        } else {
                            $actions .= "<a href='exercise_show.php?" . api_get_cidreq() . "&action=qualify&id={$id}'>" . Display::return_icon('quiz.gif', get_lang('Qualify'));
                            $actions .= '&nbsp;';
                        }
                        $actions .= "</a>";
                        if ($filter == 2) {
                            $actions .= ' <a href="exercise_history.php?' . api_get_cidreq() . '&exe_id=' . $id . '">' . Display::return_icon('history.gif', get_lang('ViewHistoryChange')) . '</a>';
                        }
                        // Admin can always delete the attempt
                        if ($locked == false || api_is_platform_admin()) {
                            $ip = TrackingUserLog::get_ip_from_user_event($results[$i]['exe_user_id'], date('Y-m-d h:i:s'), false);
                            $actions .= '<a href="http://www.whatsmyip.org/ip-geo-location/?ip=' . $ip . '" target="_blank"><img src="' . api_get_path(WEB_CODE_PATH) . 'img/icons/22/info.png" title="' . $ip . '" /></a>';
                            $delete_link = '<a href="exercise_report.php?' . api_get_cidreq() . '&filter_by_user='******'filter_by_user']) . '&filter=' . $filter . '&exerciseId=' . $exercise_id . '&delete=delete&did=' . $id . '"
                                onclick="javascript:if(!confirm(\'' . sprintf(get_lang('DeleteAttempt'), $results[$i]['username'], $dt) . '\')) return false;">' . Display::return_icon('delete.png', get_lang('Delete')) . '</a>';
                            $delete_link = utf8_encode($delete_link);
                            if (api_is_drh() && !api_is_platform_admin()) {
                                $delete_link = null;
                            }
                            $actions .= $delete_link . '&nbsp;';
                        }
                    } else {
                        $attempt_url = api_get_path(WEB_CODE_PATH) . 'exercice/result.php?' . api_get_cidreq() . '&id=' . $results[$i]['exe_id'] . '&id_session=' . $sessionId . '&height=500&width=750';
                        $attempt_link = Display::url(get_lang('Show'), $attempt_url, array('class' => 'ajax btn'));
                        $actions .= $attempt_link;
                    }
                    if ($revised) {
                        $revised = Display::label(get_lang('Validated'), 'success');
                    } else {
                        $revised = Display::label(get_lang('NotValidated'), 'info');
                    }
                    if ($is_allowedToEdit) {
                        $results[$i]['status'] = $revised;
                        $results[$i]['score'] = $score;
                        $results[$i]['lp'] = $lp_name;
                        $results[$i]['actions'] = $actions;
                        $list_info[] = $results[$i];
                    } else {
                        $results[$i]['status'] = $revised;
                        $results[$i]['score'] = $score;
                        $results[$i]['actions'] = $actions;
                        $list_info[] = $results[$i];
                    }
                }
            }
        }
    } else {
        $hpresults = getManyResultsXCol($hpsql, 6);
        // Print HotPotatoes test results.
        if (is_array($hpresults)) {
            for ($i = 0; $i < sizeof($hpresults); $i++) {
                $hp_title = GetQuizName($hpresults[$i][3], $documentPath);
                if ($hp_title == '') {
                    $hp_title = basename($hpresults[$i][3]);
                }
                $hp_date = api_get_local_time($hpresults[$i][6], null, date_default_timezone_get());
                $hp_result = round($hpresults[$i][4] / ($hpresults[$i][5] != 0 ? $hpresults[$i][5] : 1) * 100, 2) . '% (' . $hpresults[$i][4] . ' / ' . $hpresults[$i][5] . ')';
                if ($is_allowedToEdit) {
                    $list_info[] = array($hpresults[$i][0], $hpresults[$i][1], $hpresults[$i][2], '', $hp_title, '-', $hp_date, $hp_result, '-');
                } else {
                    $list_info[] = array($hp_title, '-', $hp_date, $hp_result, '-');
                }
            }
        }
    }
    return $list_info;
}
$form = new FormValidator('search_simple','POST','','',null,false);
$form->addElement('select', 'session_id', get_lang('Sessions'), $my_session_list, array('id'=>'session_id', 'onchange'=>'load_courses();'));
$form->addElement('select', 'course_code',get_lang('Courses'), $course_select_list);
$form->addElement('style_submit_button','submit_form', get_lang('Filter'));

if (!empty($_REQUEST['course_code']))	$course_code = $_REQUEST['course_code']; else $course_code = '';
if (empty($course_code)) {
	$course_code = 0;
}

$form->setDefaults(array('course_code'=>(string)$course_code));
$course_info = api_get_course_info($course_code);
//var_dump($session_id);
if (!empty($course_info)) {
	$list = new learnpathList('', $course_code);
	$lp_list = $list->get_flat_list();
	$_course = $course_info;
	$main_question_list = array();
	foreach ($lp_list as $lp_id =>$lp) {
		$exercise_list = get_all_exercises_from_lp($lp_id, $course_info['real_id']);
		foreach ($exercise_list as $exercise) {
			$my_exercise = new Exercise();
			$my_exercise->read($exercise['path']);
			$question_list = $my_exercise->selectQuestionList();

			$exercise_stats = get_all_exercise_event_from_lp(
				$exercise['path'],
				$course_info['id'],
				$session_id
			);