Example #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;
}
     $sql = "SELECT access_tool, COUNT(DISTINCT access_user_id),count( access_tool )\n                FROM {$TABLETRACK_ACCESS}\n                WHERE access_tool IS NOT NULL AND c_id = '{$course_id}'\n                GROUP BY access_tool";
     $results = getManyResults3Col($sql);
     if (is_array($results)) {
         for ($j = 0; $j < count($results); $j++) {
             $line .= $results[$j][0] . "/" . get_lang($results[$j][0]) . ";" . $results[$j][1] . ";" . $results[$j][2] . "\n";
         }
     } else {
         $line = get_lang('NoResult') . "\n";
     }
 }
 /* 	Links */
 $tempView = $view;
 if ($view[3] == '1') {
     $tempView[3] = '0';
     $sql = "SELECT cl.title, cl.url,count(DISTINCT sl.links_user_id), count(cl.title)\n                    FROM {$TABLETRACK_LINKS} AS sl, {$TABLECOURSE_LINKS} AS cl\n                    WHERE\n                    \tcl.c_id = {$course_id} AND\n                    \tsl.links_link_id = cl.id AND\n                    \tsl.c_id = '{$course_id}'\n                    GROUP BY cl.title, cl.url";
     $results = getManyResultsXCol($sql, 4);
     $title[1] = $nameTools;
     $line = '';
     $title_line = get_lang('LinksTitleLinkColumn') . ";" . get_lang('LinksTitleUsersColumn') . ";" . get_lang('LinksTitleCountColumn') . "\n";
     if (is_array($results)) {
         for ($j = 0; $j < count($results); $j++) {
             $line .= $results[$j][1] . "'>" . $results[$j][0] . ";" . $results[$j][2] . ";" . $results[$j][3] . "\n";
         }
     } else {
         $line = get_lang('NoResult') . "\n";
     }
 }
 /* 	Documents */
 $tempView = $view;
 if ($view[4] == '1') {
     $tempView[4] = '0';
Example #3
0
    /**
     * Displays the student publications for a specific user in a specific course.
     * @todo remove globals
     */
    public function display_student_publications_tracking_info($view, $user_id, $course_id)
    {
    	global $TABLETRACK_UPLOADS, $TABLECOURSE_WORK, $dateTimeFormatLong, $_course;
    	if (substr($view,2,1) == '1') {
    		$new_view = substr_replace($view,'0',2,1);
    		$sql = "SELECT u.upload_date, w.title, w.author, w.url
                    FROM $TABLETRACK_UPLOADS u , $TABLECOURSE_WORK w
                    WHERE u.upload_work_id = w.id
                        AND u.upload_user_id = '$user_id'
                        AND u.upload_cours_id = '$course_id'
                    ORDER BY u.upload_date DESC";
    		$results = getManyResultsXCol($sql,4);

    		$title[1]=get_lang('WorksDetails');
    		$line='';
    		$title_line=get_lang('WorkTitle').";".get_lang('WorkAuthors').";".get_lang('Date')."\n";

    		if (is_array($results)) {
    			for($j = 0 ; $j < count($results) ; $j++) {
    				$pathToFile = api_get_path(WEB_COURSE_PATH).$_course['path']."/".$results[$j][3];
    				$beautifulDate = api_convert_and_format_date($results[$j][0], null, date_default_timezone_get());
    				$line .= $results[$j][1].";".$results[$j][2].";".$beautifulDate."\n";
    			}

    		} else {
    			$line= get_lang('NoResult');
    		}
    	} else {
    		$new_view = substr_replace($view,'1',2,1);
    	}
    	return array($title_line, $line);
    }
 /**
  * Gets the results of all students (or just one student if access is limited)
  * @param	string		The document path (for HotPotatoes retrieval)
  * @param	integer		User ID. Optional. If no user ID is provided, we take all the results. Defauts to null
  */
 function _getGradeBookReporting($document_path, $user_id = null)
 {
     $return = array();
     $TBL_EXERCISES = Database::get_course_table(TABLE_QUIZ_TEST);
     $TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
     $TBL_TRACK_EXERCISES = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
     $TBL_TRACK_HOTPOTATOES = Database::get_main_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
     $cid = api_get_course_id();
     $course_id = api_get_course_int_id();
     if (empty($user_id)) {
         //get all results (ourself and the others) as an admin should see them
         //AND exe_user_id <> $_user['user_id']  clause has been removed
         $sql = "SELECT " . (api_is_western_name_order() ? "CONCAT(firstname,' ',lastname)" : "CONCAT(lastname,' ',firstname)") . ", ce.title, te.exe_result ,\n\t\t\t\t\t\tte.exe_weighting, te.exe_date,te.exe_id, user.email, user.user_id\n\t\t\t\t  FROM {$TBL_EXERCISES} ce , {$TBL_TRACK_EXERCISES} te, {$TBL_USER} user\n\t\t\t\t  WHERE ce.c_id = {$course_id} AND\n\t\t\t\t  \t\tte.exe_exo_id = ce.id AND\n\t\t\t\t  \t\tuser_id=te.exe_user_id AND te.c_id = '{$course_id}'\n\t\t\t\t  ORDER BY te.c_id ASC, ce.title ASC, te.exe_date ASC";
         $hpsql = "SELECT " . (api_is_western_name_order() ? "CONCAT(tu.firstname,' ',tu.lastname)" : "CONCAT(tu.lastname,' ',tu.firstname)") . ", tth.exe_name,\n\t\t\t\t\t\ttth.exe_result , tth.exe_weighting, tth.exe_date, tu.email, tu.user_id\n\t\t\t\t\tFROM {$TBL_TRACK_HOTPOTATOES} tth, {$TBL_USER} tu\n\t\t\t\t\tWHERE  tu.user_id=tth.exe_user_id AND tth.c_id = '" . $course_id . "'\n\t\t\t\t\tORDER BY tth.c_id ASC, tth.exe_date ASC";
     } else {
         // get only this user's results
         $sql = "SELECT '',ce.title, te.exe_result , te.exe_weighting, te.exe_date,te.exe_id\n\t\t\t\t\t\tFROM {$TBL_EXERCISES} ce , {$TBL_TRACK_EXERCISES} te\n\t\t\t\t  \t\tWHERE \tce.c_id \t\t= {$course_id} AND\n\t\t\t\t  \t\t\t\tte.exe_exo_id \t= ce.id AND\n\t\t\t\t  \t\t\t\tte.exe_user_id \t= '" . $user_id . "' AND\n\t\t\t\t  \t\t\t\tte.c_id = '{$course_id}'\n\t\t\t\t  \t\tORDER BY te.c_id ASC, ce.title ASC, te.exe_date ASC";
         $hpsql = "SELECT '',exe_name, exe_result , exe_weighting, exe_date\n\t\t\t\t\tFROM {$TBL_TRACK_HOTPOTATOES}\n\t\t\t\t\tWHERE exe_user_id = '" . $user_id . "' AND c_id = '" . $course_id . "'\n\t\t\t\t\tORDER BY c_id ASC, exe_date ASC";
     }
     $results = getManyResultsXCol($sql, 8);
     $hpresults = getManyResultsXCol($hpsql, 7);
     $NoTestRes = 0;
     $NoHPTestRes = 0;
     $j = 0;
     //Print the results of tests
     if (is_array($results)) {
         for ($i = 0; $i < sizeof($results); $i++) {
             $return[$i] = array();
             $id = $results[$i][5];
             $mailid = $results[$i][6];
             $user = $results[$i][0];
             $test = $results[$i][1];
             $res = $results[$i][2];
             if (empty($user_id)) {
                 $user = $results[$i][0];
                 $return[$i]['user'] = $user;
                 $return[$i]['user_id'] = $results[$i][7];
             }
             $return[$i]['title'] = $test;
             $return[$i]['time'] = api_convert_and_format_date($results[$i][4], null, date_default_timezone_get());
             $return[$i]['result'] = $res;
             $return[$i]['max'] = $results[$i][3];
             $j = $i;
         }
     }
     $j++;
     // Print the Result of Hotpotatoes Tests
     if (is_array($hpresults)) {
         for ($i = 0; $i < sizeof($hpresults); $i++) {
             $return[$j + $i] = array();
             $title = GetQuizName($hpresults[$i][1], $document_path);
             if ($title == '') {
                 $title = basename($hpresults[$i][1]);
             }
             if (empty($user_id)) {
                 $return[$j + $i]['user'] = $hpresults[$i][0];
                 $return[$j + $i]['user_id'] = $results[$i][6];
             }
             $return[$j + $i]['title'] = $title;
             $return[$j + $i]['time'] = api_convert_and_format_date($hpresults[$i][4], null, date_default_timezone_get());
             $return[$j + $i]['result'] = $hpresults[$i][2];
             $return[$j + $i]['max'] = $hpresults[$i][3];
         }
     }
     $this->results = $return;
     return true;
 }