/**
* Returns users whose last login is prior from $ceiling
*
* @param int|string $ceiling last login date
* @param bool $active_only if true returns only active users. Otherwise returns all users.
* @return ResultSet
*/
static function list_zombies($ceiling, $active_only = true)
{
$ceiling = is_numeric($ceiling) ? (int) $ceiling : strtotime($ceiling);
$ceiling = date('Y-m-d H:i:s', $ceiling);
$user_table = Database::get_main_table(TABLE_MAIN_USER);
$login_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$sql = 'SELECT
user.user_id,
user.firstname,
user.lastname,
user.username,
user.auth_source,
user.email,
user.status,
user.registration_date,
user.active,
access.login_date';
global $_configuration;
if ($_configuration['multiple_access_urls']) {
$access_url_rel_user_table = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$current_url_id = api_get_current_access_url_id();
$sql .= " FROM $user_table as user, $login_table as access, $access_url_rel_user_table as url
WHERE
access.login_date = (SELECT MAX(a.login_date)
FROM $login_table as a
WHERE a.login_user_id = user.user_id
) AND
access.login_date <= '$ceiling' AND
user.user_id = access.login_user_id AND
url.user_id = user.user_id AND url.access_url_id=$current_url_id";
} else {
$sql .= " FROM $user_table as user, $login_table as access
WHERE
access.login_date = (SELECT MAX(a.login_date)
FROM $login_table as a
WHERE a.login_user_id = user.user_id
) AND
access.login_date <= '$ceiling' AND
user.user_id = access.login_user_id";
}
if($active_only)
{
$sql .= ' AND user.active = 1';
}
return ResultSet::create($sql);
}
function get_number_students_finish_exercise($exercise_id, $course_code, $session_id)
{
$track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
$exercise_id = intval($exercise_id);
$course_code = Database::escape_string($course_code);
$session_id = intval($session_id);
$sql = "SELECT DISTINCT exe_user_id\n \t\tFROM {$track_exercises} e INNER JOIN {$track_attempt} a ON (a.exe_id = e.exe_id)\n \t\tWHERE \texe_exo_id \t\t= {$exercise_id} AND\n \t\t\t\tcourse_code \t= '{$course_code}' AND\n \t\t\t\te.session_id \t= {$session_id} AND\n \t\t\t\tstatus = ''";
$result = Database::query($sql);
$return = 0;
if ($result) {
$return = Database::num_rows($result);
}
return $return;
}
$courseCode = $_GET['coursecode'];
$questionId = $_GET['questionId'];
$coordinates = $_GET['coord'];
$objExcercise = $_SESSION['objExercise'];
$exerciseId = $objExcercise->selectId();
// Save clicking order
$answerOrderId = count($_SESSION['exerciseResult'][$questionId]['ids']) + 1;
if ($_GET['answerId'] == "0") {
$hit = 0;
$answerId = NULL;
} else {
$hit = 1;
$answerId = api_substr($_GET['answerId'], 22, 2);
// Save into session
$_SESSION['exerciseResult'][$questionId][$answerId] = $hit;
}
//round-up the coordinates
$coords = explode('/', $coordinates);
$coordinates = '';
foreach ($coords as $coord) {
list($x, $y) = explode(';', $coord);
$coordinates .= round($x) . ';' . round($y) . '/';
}
$coordinates = substr($coordinates, 0, -1);
$TBL_TRACK_E_HOTSPOT = Database::get_statistic_table(STATISTIC_TRACK_E_HOTSPOTS);
// Save into db
$sql = "INSERT INTO {$TBL_TRACK_E_HOTSPOT} (user_id , course_id , quiz_id , question_id , answer_id , correct , coordinate ) VALUES (\n\t\t\t" . intval($_user['user_id']) . ",\n\t\t\t'" . Database::escape_string($courseCode) . "',\n\t\t\t" . intval($exerciseId) . ",\n\t\t\t" . intval($questionId) . ",\n\t\t\t" . intval($answerId) . ",\n\t\t\t" . intval($hit) . "',\n\t\t\t'" . Database::escape_string($coordinates) . "')";
$result = Database::query($sql);
// Save insert id into session if users changes answer.
$insert_id = Database::insert_id();
$_SESSION['exerciseResult'][$questionId]['ids'][$answerOrderId] = $insert_id;
require_once '../newscorm/learnpathItem.class.php';
require_once '../newscorm/scorm.class.php';
require_once '../newscorm/scormItem.class.php';
require_once '../newscorm/aicc.class.php';
require_once '../newscorm/aiccItem.class.php';
}
require_once '../inc/global.inc.php';
$courseInfo = api_get_course_info();
$_user = api_get_user_info();
$this_section = SECTION_COURSES;
require_once api_get_path(LIBRARY_PATH) . 'fileManage.lib.php';
$documentPath = api_get_path(SYS_COURSE_PATH) . $courseInfo['path'] . "/document";
$test = $_REQUEST['test'];
$full_file_path = $documentPath . $test;
my_delete($full_file_path . $_user['user_id'] . ".t.html");
$TABLETRACK_HOTPOTATOES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
$TABLE_LP_ITEM_VIEW = Database::get_course_table(TABLE_LP_ITEM_VIEW);
$score = $_REQUEST['score'];
$origin = $_REQUEST['origin'];
$learnpath_item_id = intval($_REQUEST['learnpath_item_id']);
$lpViewId = isset($_REQUEST['lp_view_id']) ? intval($_REQUEST['lp_view_id']) : null;
$course_id = $courseInfo['real_id'];
$_cid = api_get_course_id();
$jscript2run = '';
/**
* Save the score for a HP quiz. Can be used by the learnpath tool as well
* for HotPotatoes quizzes. When coming from the learning path, we
* use the session variables telling us which item of the learning path has to
* be updated (score-wise)
* @param string File is the exercise name (the file name for a HP)
* @param integer Score to save inside the tracking tables (HP and learnpath)
/**
* 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
*/
public function getExercisesReporting($document_path, $hotpotato_name)
{
$return = array();
$TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
$TBL_TRACK_HOTPOTATOES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
$cid = api_get_course_id();
$course_id = api_get_course_int_id();
//$user_id = intval($user_id);
$user_id = null;
$session_id_and = ' AND te.session_id = ' . api_get_session_id() . ' ';
$hotpotato_name = Database::escape_string($hotpotato_name);
if (!empty($exercise_id)) {
$session_id_and .= " AND exe_exo_id = {$exercise_id} ";
}
if (empty($user_id)) {
$sql = "SELECT firstname as userpart1, lastname as userpart2 ,\n email,\n tth.exe_name,\n tth.exe_result,\n tth.exe_weighting,\n tth.exe_date\n FROM {$TBL_TRACK_HOTPOTATOES} tth, {$TBL_USER} tu\n WHERE tu.user_id=tth.exe_user_id AND\n tth.exe_cours_id = '" . Database::escape_string($cid) . "' AND\n tth.exe_name = '{$hotpotato_name}'\n ORDER BY tth.exe_cours_id ASC, tth.exe_date ASC";
} else {
$user_id_and = ' AND te.exe_user_id = ' . api_get_user_id() . ' ';
// get only this user's results
$sql = "SELECT '', exe_name, exe_result , exe_weighting, exe_date\n FROM {$TBL_TRACK_HOTPOTATOES}\n WHERE\n exe_user_id = '" . $user_id . "' AND\n exe_cours_id = '" . Database::escape_string($cid) . "' AND\n tth.exe_name = '{$hotpotato_name}'\n ORDER BY exe_cours_id ASC, exe_date ASC";
}
$results = array();
$resx = Database::query($sql);
while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
$results[] = $rowx;
}
$hpresults = array();
$resx = Database::query($sql);
while ($rowx = Database::fetch_array($resx, 'ASSOC')) {
$hpresults[] = $rowx;
}
/*if ($filter) {
switch ($filter) {
case 1 :
$filter_by_not_revised = true;
break;
case 2 :
$filter_by_revised = true;
break;
default :
null;
}
}*/
// Print the Result of Hotpotatoes Tests
if (is_array($hpresults)) {
for ($i = 0; $i < sizeof($hpresults); $i++) {
$return[$i] = array();
$title = GetQuizName($hpresults[$i]['exe_name'], $document_path);
if ($title == '') {
$title = basename($hpresults[$i]['exe_name']);
}
if (empty($user_id)) {
$return[$i]['email'] = $hpresults[$i]['email'];
$return[$i]['first_name'] = $hpresults[$i]['userpart1'];
$return[$i]['last_name'] = $hpresults[$i]['userpart2'];
}
$return[$i]['title'] = $title;
$return[$i]['exe_date'] = $hpresults[$i]['exe_date'];
$return[$i]['result'] = $hpresults[$i]['exe_result'];
$return[$i]['max'] = $hpresults[$i]['exe_weighting'];
}
}
$this->results = $return;
return true;
}
/**
*
* @global bool $is_platformAdmin
* @global bool $is_allowedCreateCourse
* @global object $_user
* @global int $_cid
* @global array $_course
* @global type $_real_cid
* @global type $_courseUser
* @global type $is_courseAdmin
* @global type $is_courseTutor
* @global type $is_courseCoach
* @global type $is_courseMember
* @global type $is_sessionAdmin
* @global type $is_allowed_in_course
*
* @param type $course_id
* @param type $reset
*/
static function init_course($course_id, $reset)
{
global $_configuration;
global $is_platformAdmin;
global $is_allowedCreateCourse;
global $_user;
global $_cid;
global $_course;
global $_real_cid;
global $_courseUser;
global $is_courseAdmin;
//course teacher
global $is_courseTutor;
//course teacher - some rights
global $is_courseCoach;
//course coach
global $is_courseMember;
//course student
global $is_sessionAdmin;
global $is_allowed_in_course;
if ($reset) {
// Course session data refresh requested or empty data
if ($course_id) {
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$course_cat_table = Database::get_main_table(TABLE_MAIN_CATEGORY);
$sql = "SELECT course.*, course_category.code faCode, course_category.name faName\n FROM {$course_table}\n LEFT JOIN {$course_cat_table}\n ON course.category_code = course_category.code\n WHERE course.code = '{$course_id}'";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
$course_data = Database::fetch_array($result);
//@TODO real_cid should be cid, for working with numeric course id
$_real_cid = $course_data['id'];
$_cid = $course_data['code'];
$_course = array();
$_course['real_id'] = $course_data['id'];
$_course['id'] = $course_data['code'];
//auto-assigned integer
$_course['code'] = $course_data['code'];
$_course['name'] = $course_data['title'];
$_course['title'] = $course_data['title'];
$_course['official_code'] = $course_data['visual_code'];
// use in echo
$_course['sysCode'] = $course_data['code'];
// use as key in db
$_course['path'] = $course_data['directory'];
// use as key in path
$_course['dbName'] = $course_data['db_name'];
// use as key in db list
$_course['db_name'] = $course_data['db_name'];
// not needed in Chamilo 1.9
$_course['dbNameGlu'] = $_configuration['table_prefix'] . $course_data['db_name'] . $_configuration['db_glue'];
// use in all queries //not needed in Chamilo 1.9
$_course['titular'] = $course_data['tutor_name'];
// this should be deprecated and use the table course_rel_user
$_course['language'] = $course_data['course_language'];
$_course['extLink']['url'] = $course_data['department_url'];
$_course['extLink']['name'] = $course_data['department_name'];
$_course['categoryCode'] = $course_data['faCode'];
$_course['categoryName'] = $course_data['faName'];
$_course['visibility'] = $course_data['visibility'];
$_course['subscribe_allowed'] = $course_data['subscribe'];
$_course['unsubscribe'] = $course_data['unsubscribe'];
$_course['activate_legal'] = $course_data['activate_legal'];
$_course['show_score'] = $course_data['show_score'];
//used in the work tool
Session::write('_cid', $_cid);
Session::write('_course', $_course);
//@TODO real_cid should be cid, for working with numeric course id
Session::write('_real_cid', $_real_cid);
// if a session id has been given in url, we store the session
// Database Table Definitions
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
if (!empty($_GET['id_session'])) {
$_SESSION['id_session'] = intval($_GET['id_session']);
$sql = 'SELECT name FROM ' . $tbl_session . ' WHERE id="' . intval($_SESSION['id_session']) . '"';
$rs = Database::query($sql);
list($_SESSION['session_name']) = Database::fetch_array($rs);
} else {
Session::erase('session_name');
Session::erase('id_session');
}
if (!isset($_SESSION['login_as'])) {
//Course login
if (isset($_user['user_id'])) {
event_course_login($_course['sysCode'], $_user['user_id'], api_get_session_id());
}
}
} else {
//exit("WARNING UNDEFINED CID !! ");
header('location:' . api_get_path(WEB_PATH));
}
} else {
Session::erase('_cid');
Session::erase('_real_cid');
Session::erase('_course');
if (!empty($_SESSION)) {
foreach ($_SESSION as $key => $session_item) {
if (strpos($key, 'lp_autolunch_') === false) {
continue;
} else {
if (isset($_SESSION[$key])) {
Session::erase($key);
}
}
}
}
//Deleting session info
if (api_get_session_id()) {
Session::erase('id_session');
Session::erase('session_name');
}
}
} else {
// Continue with the previous values
if (empty($_SESSION['_course']) or empty($_SESSION['_cid'])) {
//no previous values...
$_cid = -1;
//set default values that will be caracteristic of being unset
$_course = -1;
} else {
$_cid = $_SESSION['_cid'];
$_course = $_SESSION['_course'];
// these lines are usefull for tracking. Indeed we can have lost the id_session and not the cid.
// Moreover, if we want to track a course with another session it can be usefull
if (!empty($_GET['id_session'])) {
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$sql = 'SELECT name FROM ' . $tbl_session . ' WHERE id="' . intval($_SESSION['id_session']) . '"';
$rs = Database::query($sql);
list($_SESSION['session_name']) = Database::fetch_array($rs);
$_SESSION['id_session'] = intval($_GET['id_session']);
}
if (!isset($_SESSION['login_as'])) {
$save_course_access = true;
//The value $_dont_save_user_course_access should be added before the call of global.inc.php see the main/inc/chat.ajax.php file
//Disables the updates in the TRACK_E_COURSE_ACCESS table
if (isset($_dont_save_user_course_access) && $_dont_save_user_course_access == true) {
$save_course_access = false;
}
if ($save_course_access) {
$course_tracking_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
/*
* When $_configuration['session_lifetime'] is too big 100 hours (in order to let users take exercises with no problems)
* the function Tracking::get_time_spent_on_the_course() returns big values (200h) due the condition:
* login_course_date > now() - INTERVAL $session_lifetime SECOND
*
*/
/*
if (isset($_configuration['session_lifetime'])) {
$session_lifetime = $_configuration['session_lifetime'];
} else {
$session_lifetime = 3600; // 1 hour
} */
$session_lifetime = 3600;
// 1 hour
$course_code = $_course['sysCode'];
$time = api_get_utc_datetime();
if (isset($_user['user_id']) && !empty($_user['user_id'])) {
//We select the last record for the current course in the course tracking table
//But only if the login date is < than now + max_life_time
$sql = "SELECT course_access_id FROM {$course_tracking_table}\n WHERE user_id = " . intval($_user['user_id']) . " AND\n course_code = '{$course_code}' AND\n session_id = " . api_get_session_id() . " AND\n login_course_date > now() - INTERVAL {$session_lifetime} SECOND\n ORDER BY login_course_date DESC LIMIT 0,1";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
$i_course_access_id = Database::result($result, 0, 0);
//We update the course tracking table
$sql = "UPDATE {$course_tracking_table} SET logout_course_date = '{$time}', counter = counter+1\n WHERE course_access_id = " . intval($i_course_access_id) . " AND session_id = " . api_get_session_id();
//error_log($sql);
Database::query($sql);
} else {
$sql = "INSERT INTO {$course_tracking_table} (course_code, user_id, login_course_date, logout_course_date, counter, session_id)" . "VALUES('" . $course_code . "', '" . $_user['user_id'] . "', '{$time}', '{$time}', '1','" . api_get_session_id() . "')";
//error_log($sql);
Database::query($sql);
}
}
}
}
}
}
/* COURSE / USER REL. INIT */
$session_id = api_get_session_id();
$user_id = isset($_user['user_id']) ? $_user['user_id'] : null;
//Course permissions
$is_courseAdmin = false;
//course teacher
$is_courseTutor = false;
//course teacher - some rights
$is_courseMember = false;
//course student
//Course - User permissions
$is_sessionAdmin = false;
if ($reset) {
if (isset($user_id) && $user_id && isset($_cid) && $_cid) {
//Check if user is subscribed in a course
$course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$sql = "SELECT * FROM {$course_user_table}\n WHERE user_id = '" . $user_id . "' AND relation_type <> " . COURSE_RELATION_TYPE_RRHH . "\n AND course_code = '{$course_id}'";
$result = Database::query($sql);
$cuData = null;
if (Database::num_rows($result) > 0) {
// this user have a recorded state for this course
$cuData = Database::fetch_array($result, 'ASSOC');
$is_courseAdmin = (bool) ($cuData['status'] == 1);
$is_courseTutor = (bool) ($cuData['tutor_id'] == 1);
$is_courseMember = true;
//Checking if the user filled the course legal agreement
if ($_course['activate_legal'] == 1 && !api_is_platform_admin()) {
$user_is_subscribed = CourseManager::is_user_accepted_legal($user_id, $_course['id'], $session_id);
if (!$user_is_subscribed) {
$url = api_get_path(WEB_CODE_PATH) . 'course_info/legal.php?course_code=' . $_course['code'] . '&session_id=' . $session_id;
header('Location: ' . $url);
exit;
}
}
$_courseUser['role'] = $cuData['role'];
Session::write('_courseUser', $_courseUser);
}
//We are in a session course? Check session permissions
if (!empty($session_id)) {
//I'm not the teacher of the course
if ($is_courseAdmin == false) {
// this user has no status related to this course
// The user is subscribed in a session? The user is a Session coach a Session admin ?
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
//Session coach, session admin, course coach admin
$sql = "SELECT session.id_coach, session_admin_id, session_rcru.id_user\n \t\tFROM {$tbl_session} session, {$tbl_session_course_user} session_rcru\n\t\t\t\t\t WHERE session_rcru.id_session = session.id AND\n\t\t\t\t\t session_rcru.course_code = '{$_cid}' AND\n\t\t\t\t\t session_rcru.id_user = '******' AND\n session_rcru.id_session = {$session_id} AND\n\t\t\t\t\t session_rcru.status = 2";
$result = Database::query($sql);
$row = Database::store_result($result);
//I'm a session admin?
if (isset($row) && isset($row[0]) && $row[0]['session_admin_id'] == $user_id) {
$_courseUser['role'] = 'Professor';
$is_courseMember = false;
$is_courseTutor = false;
$is_courseAdmin = false;
$is_courseCoach = false;
$is_sessionAdmin = true;
} else {
//Im a coach or a student?
$sql = "SELECT id_user, status FROM " . $tbl_session_course_user . "\n WHERE course_code = '{$_cid}' AND\n id_user = '******' AND\n id_session = '" . $session_id . "'\n LIMIT 1";
$result = Database::query($sql);
if (Database::num_rows($result)) {
$row = Database::fetch_array($result, 'ASSOC');
$session_course_status = $row['status'];
switch ($session_course_status) {
case '2':
// coach - teacher
$_courseUser['role'] = 'Professor';
$is_courseMember = true;
$is_courseTutor = true;
$is_courseCoach = true;
$is_sessionAdmin = false;
if (api_get_setting('extend_rights_for_coach') == 'true') {
$is_courseAdmin = true;
} else {
$is_courseAdmin = false;
}
Session::write('_courseUser', $_courseUser);
break;
case '0':
//student
$is_courseMember = true;
$is_courseTutor = false;
$is_courseAdmin = false;
$is_sessionAdmin = false;
Session::write('_courseUser', $_courseUser);
break;
default:
//unregister user
$is_courseMember = false;
$is_courseTutor = false;
$is_courseAdmin = false;
$is_sessionAdmin = false;
Session::erase('_courseUser');
break;
}
} else {
//unregister user
$is_courseMember = false;
$is_courseTutor = false;
$is_courseAdmin = false;
$is_sessionAdmin = false;
Session::erase('_courseUser');
}
}
}
//If I'm the admin platform i'm a teacher of the course
if ($is_platformAdmin) {
$is_courseAdmin = true;
}
}
} else {
// keys missing => not anymore in the course - user relation
// course
$is_courseMember = false;
$is_courseAdmin = false;
$is_courseTutor = false;
$is_courseCoach = false;
$is_sessionAdmin = false;
Session::erase('_courseUser');
}
//Checking the course access
$is_allowed_in_course = false;
if (isset($_course)) {
switch ($_course['visibility']) {
case COURSE_VISIBILITY_OPEN_WORLD:
//3
$is_allowed_in_course = true;
break;
case COURSE_VISIBILITY_OPEN_PLATFORM:
//2
if (isset($user_id) && !api_is_anonymous($user_id)) {
$is_allowed_in_course = true;
}
break;
case COURSE_VISIBILITY_REGISTERED:
//1
if ($is_platformAdmin || $is_courseMember) {
$is_allowed_in_course = true;
}
break;
case COURSE_VISIBILITY_CLOSED:
//0
if ($is_platformAdmin || $is_courseAdmin) {
$is_allowed_in_course = true;
}
break;
case COURSE_VISIBILITY_HIDDEN:
//4
if ($is_platformAdmin) {
$is_allowed_in_course = true;
}
break;
}
}
// check the session visibility
if ($is_allowed_in_course == true) {
//if I'm in a session
if ($session_id != 0) {
if (!$is_platformAdmin) {
// admin and session coach are *not* affected to the invisible session mode
// the coach is not affected because he can log in some days after the end date of a session
$session_visibility = api_get_session_visibility($session_id);
switch ($session_visibility) {
case SESSION_INVISIBLE:
$is_allowed_in_course = false;
break;
}
//checking date
}
}
}
// save the states
Session::write('is_courseAdmin', $is_courseAdmin);
Session::write('is_courseMember', $is_courseMember);
Session::write('is_courseTutor', $is_courseTutor);
Session::write('is_courseCoach', $is_courseCoach);
Session::write('is_allowed_in_course', $is_allowed_in_course);
Session::write('is_sessionAdmin', $is_sessionAdmin);
} else {
// continue with the previous values
if (isset($_SESSION['_courseUser'])) {
$_courseUser = $_SESSION['_courseUser'];
}
$is_courseAdmin = $_SESSION['is_courseAdmin'];
$is_courseTutor = $_SESSION['is_courseTutor'];
$is_courseCoach = $_SESSION['is_courseCoach'];
$is_courseMember = $_SESSION['is_courseMember'];
$is_allowed_in_course = $_SESSION['is_allowed_in_course'];
}
}
/**
* Writes the current data to the database
* @return boolean Query result
*/
function write_to_db()
{
if ($this->debug > 0) {
error_log('New LP - In learnpathItem::write_to_db()', 0);
}
$mode = $this->get_lesson_mode();
$credit = $this->get_credit();
$my_verified_status = $this->get_status(false);
$item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
$sql_verified = 'SELECT status FROM ' . $item_view_table . ' WHERE lp_item_id="' . $this->db_id . '" AND lp_view_id="' . $this->view_id . '" AND view_count="' . $this->attempt_id . '" ;';
$rs_verified = Database::query($sql_verified, __FILE__, __LINE__);
$row_verified = Database::fetch_array($rs_verified);
$my_case_completed = array('completed', 'passed', 'browsed', 'failed');
//added by isaac flores
if (in_array($sql_verified['status'], $my_case_completed)) {
$save = false;
} else {
$save = true;
}
if ($save === false && $this->type == 'sco' || $this->type == 'sco' && ($credit == 'no-credit' or $mode == 'review' or $mode == 'browse')) {
//this info shouldn't be saved as the credit or lesson mode info prevent it
if ($this->debug > 1) {
error_log('New LP - In learnpathItem::write_to_db() - credit(' . $credit . ') or lesson_mode(' . $mode . ') prevent recording!', 0);
}
} else {
//check the row exists
$inserted = false;
// this a special case for multiple attempts and Dokeos exercises
if ($this->type == 'quiz' && $this->get_prevent_reinit() == 0 && $this->get_status() == 'completed') {
// we force the item to be restarted
$this->restart();
$sql = "INSERT INTO {$item_view_table} " . "(total_time, " . "start_time, " . "score, " . "status, " . "max_score, " . "lp_item_id, " . "lp_view_id, " . "view_count, " . "suspend_data, " . "lesson_location)" . "VALUES" . "(" . $this->get_total_time() . "," . "" . $this->current_start_time . "," . "" . $this->get_score() . "," . "'" . $this->get_status(false) . "'," . "'" . $this->get_max() . "'," . "" . $this->db_id . "," . "" . $this->view_id . "," . "" . $this->get_attempt_id() . "," . "'" . Database::escape_string($this->current_data) . "'," . "'" . $this->lesson_location . "')";
if ($this->debug > 2) {
error_log('New LP - In learnpathItem::write_to_db() - Inserting into item_view forced: ' . $sql, 0);
}
$res = Database::query($sql, __FILE__, __LINE__);
$this->db_item_view_id = Database::insert_id();
$inserted = true;
}
$item_view_table = Database::get_course_table(TABLE_LP_ITEM_VIEW);
$check = "SELECT * FROM {$item_view_table} " . "WHERE lp_item_id = " . $this->db_id . " " . "AND lp_view_id = " . $this->view_id;
if ($this->debug > 2) {
error_log('New LP - In learnpathItem::write_to_db() - Querying item_view: ' . $check, 0);
}
$check_res = Database::query($check);
//depending on what we want (really), we'll update or insert a new row
//now save into DB
$res = 0;
if ($inserted == false && Database::num_rows($check_res) < 1) {
/*$my_status = '';
if ($this->type!=TOOL_QUIZ) {
$my_status = $this->get_status(false);
}*/
$sql = "INSERT INTO {$item_view_table} " . "(total_time, " . "start_time, " . "score, " . "status, " . "max_score, " . "lp_item_id, " . "lp_view_id, " . "view_count, " . "suspend_data, " . "lesson_location)" . "VALUES" . "(" . $this->get_total_time() . "," . "" . $this->current_start_time . "," . "" . $this->get_score() . "," . "'" . $this->get_status(false) . "'," . "'" . $this->get_max() . "'," . "" . $this->db_id . "," . "" . $this->view_id . "," . "" . $this->get_attempt_id() . "," . "'" . Database::escape_string($this->current_data) . "'," . "'" . $this->lesson_location . "')";
if ($this->debug > 2) {
error_log('New LP - In learnpathItem::write_to_db() - Inserting into item_view: ' . $sql, 0);
}
$res = Database::query($sql, __FILE__, __LINE__);
$this->db_item_view_id = Database::insert_id();
} else {
$sql = '';
if ($this->type == 'hotpotatoes') {
//make an exception for HotPotatoes, don't update the score
//because it has been saved outside of this tool
$sql = "UPDATE {$item_view_table} " . "SET total_time = " . $this->get_total_time() . ", " . " start_time = " . $this->get_current_start_time() . ", " . " score = " . $this->get_score() . ", " . " status = '" . $this->get_status(false) . "'," . " max_score = '" . $this->get_max() . "'," . " suspend_data = '" . Database::escape_string($this->current_data) . "'," . " lesson_location = '" . $this->lesson_location . "' " . "WHERE lp_item_id = " . $this->db_id . " " . "AND lp_view_id = " . $this->view_id;
} else {
//for all other content types...
if ($this->type == 'quiz') {
$my_status = ' ';
$total_time = ' ';
if (!empty($_REQUEST['exeId'])) {
$TBL_TRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$safe_exe_id = Database::escape_string($_REQUEST['exeId']);
$sql = 'SELECT start_date,exe_date FROM ' . $TBL_TRACK_EXERCICES . ' WHERE exe_id = ' . (int) $safe_exe_id;
$res = Database::query($sql, __FILE__, __LINE__);
$row_dates = Database::fetch_array($res);
$time_start_date = convert_mysql_date($row_dates['start_date']);
$time_exe_date = convert_mysql_date($row_dates['exe_date']);
$mytime = (int) $time_exe_date - (int) $time_start_date;
$total_time = " total_time = " . $mytime . ", ";
}
} else {
$my_type_lp = learnpath::get_type_static($this->lp_id);
// this is a array containing values finished
$case_completed = array('completed', 'passed', 'browsed');
//is not multiple attempts
if ($this->get_prevent_reinit() == 1) {
// process of status verified into data base
$sql_verified = 'SELECT status FROM ' . $item_view_table . ' WHERE lp_item_id="' . $this->db_id . '" AND lp_view_id="' . $this->view_id . '" AND view_count="' . $this->attempt_id . '" ;';
$rs_verified = Database::query($sql_verified, __FILE__, __LINE__);
$row_verified = Database::fetch_array($rs_verified);
//get type lp: 1=lp dokeos and 2=scorm
// if not is completed or passed or browsed and learning path is scorm
if (!in_array($this->get_status(false), $case_completed) && $my_type_lp == 2) {
//&& $this->type!='dir'
$total_time = " total_time = total_time +" . $this->get_total_time() . ", ";
$my_status = " status = '" . $this->get_status(false) . "' ,";
} else {
//verified into data base
if (!in_array($row_verified['status'], $case_completed) && $my_type_lp == 2) {
//&& $this->type!='dir'
$total_time = " total_time = total_time +" . $this->get_total_time() . ", ";
$my_status = " status = '" . $this->get_status(false) . "' ,";
} elseif (in_array($row_verified['status'], $case_completed) && $my_type_lp == 2 && $this->type != 'sco') {
//&& $this->type!='dir'
$total_time = " total_time = total_time +" . $this->get_total_time() . ", ";
$my_status = " status = '" . $this->get_status(false) . "' ,";
} else {
//&& !in_array($row_verified['status'],$case_completed)
//is lp dokeos or aicc
if ($my_type_lp == 1 && $this->type != 'chapter') {
$total_time = " total_time = total_time + " . $this->get_total_time() . ", ";
$my_status = " status = '" . $this->get_status(false) . "' ,";
} else {
if ($my_type_lp == 3) {
$total_time = " total_time = total_time +" . $this->get_total_time() . ", ";
$my_status = " status = '" . $this->get_status(false) . "' ,";
}
}
}
}
} else {
// is multiple attempts
if (in_array($this->get_status(false), $case_completed) && $my_type_lp == 2) {
//reset zero new attempt ?
$my_status = " status = '" . $this->get_status(false) . "' ,";
} elseif (!in_array($this->get_status(false), $case_completed) && $my_type_lp == 2) {
$total_time = " total_time = " . $this->get_total_time() . ", ";
$my_status = " status = '" . $this->get_status(false) . "' ,";
} else {
//is dokeos LP
$total_time = " total_time = total_time +" . $this->get_total_time() . ", ";
$my_status = " status = '" . $this->get_status(false) . "' ,";
}
//code added by isaac flores
//this code line fix the problem of wrong status
if ($my_type_lp == 2) {
//verify current status in multiples attempts
$sql_status = 'SELECT status FROM ' . $item_view_table . ' WHERE lp_item_id="' . $this->db_id . '" AND lp_view_id="' . $this->view_id . '" AND view_count="' . $this->attempt_id . '" ';
$rs_status = Database::query($sql_status, __FILE__, __LINE__);
$current_status = Database::result($rs_status, 0, 'status');
if (in_array($current_status, $case_completed)) {
$my_status = '';
$total_time = '';
} else {
$total_time = " total_time = total_time +" . $this->get_total_time() . ", ";
}
}
}
/*if ($my_type_lp==1 && !in_array($row_verified['status'],$case_completed)) {
$total_time =" total_time = total_time + ".$this->get_total_time().", ";
}*/
}
// if $this->force_scorm_time is set to true, then we need to add the session time to the initial time
if ($this->force_scorm_time) {
$total_time = ' total_time = ' . ($this->time_before_session + $this->get_total_time()) . ' , ';
$this->force_scorm_time = false;
}
$my_view_count = 'view_count=' . ($this->get_attempt_id() + 1) . ',';
$sql = "UPDATE {$item_view_table} " . "SET " . $total_time . " start_time = " . $this->get_current_start_time() . ", " . " score = " . $this->get_score() . ", " . $my_status . $my_view_count . " max_score = '" . $this->get_max() . "'," . " suspend_data = '" . Database::escape_string($this->current_data) . "'," . " lesson_location = '" . $this->lesson_location . "' " . "WHERE lp_item_id = " . $this->db_id;
//." " .
//"AND lp_view_id = ".$this->view_id." "
$this->current_start_time = time();
}
if ($this->debug > 2) {
error_log('New LP - In learnpathItem::write_to_db() - Updating item_view: ' . $sql, 0);
}
$res = Database::query($sql, __FILE__, __LINE__);
}
//if(!$res)
//{
// $this->error = 'Could not update item_view table...'.mysql_error();
//}
if (is_array($this->interactions) && count($this->interactions) > 0) {
//save interactions
$tbl = Database::get_course_table(TABLE_LP_ITEM_VIEW);
$sql = "SELECT id FROM {$tbl} " . "WHERE lp_item_id = " . $this->db_id . " " . "AND lp_view_id = " . $this->view_id . " " . "AND view_count = " . $this->attempt_id;
$res = Database::query($sql, __FILE__, __LINE__);
if (Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
$lp_iv_id = $row[0];
if ($this->debug > 2) {
error_log('New LP - In learnpathItem::write_to_db() - Got item_view_id ' . $lp_iv_id . ', now checking interactions ', 0);
}
foreach ($this->interactions as $index => $interaction) {
$correct_resp = '';
if (is_array($interaction[4]) && !empty($interaction[4][0])) {
foreach ($interaction[4] as $resp) {
$correct_resp .= $resp . ',';
}
$correct_resp = substr($correct_resp, 0, strlen($correct_resp) - 1);
}
$iva_table = Database::get_course_table(TABLE_LP_IV_INTERACTION);
$iva_sql = "SELECT id FROM {$iva_table} " . "WHERE lp_iv_id = {$lp_iv_id} " . "AND (order_id = {$index} " . "OR interaction_id = '" . Database::escape_string($interaction[0]) . "')";
$iva_res = Database::query($iva_sql, __FILE__, __LINE__);
//id(0), type(1), time(2), weighting(3),correct_responses(4),student_response(5),result(6),latency(7)
if (Database::num_rows($iva_res) > 0) {
//update (or don't)
$iva_row = Database::fetch_array($iva_res);
$iva_id = $iva_row[0];
$ivau_sql = "UPDATE {$iva_table} " . "SET interaction_id = '" . Database::escape_string($interaction[0]) . "'," . "interaction_type = '" . Database::escape_string($interaction[1]) . "'," . "weighting = '" . Database::escape_string($interaction[3]) . "'," . "completion_time = '" . Database::escape_string($interaction[2]) . "'," . "correct_responses = '" . Database::escape_string($correct_resp) . "'," . "student_response = '" . Database::escape_string($interaction[5]) . "'," . "result = '" . Database::escape_string($interaction[6]) . "'," . "latency = '" . Database::escape_string($interaction[7]) . "'" . "WHERE id = {$iva_id}";
$ivau_res = Database::query($ivau_sql, __FILE__, __LINE__);
} else {
//insert new one
$ivai_sql = "INSERT INTO {$iva_table} " . "(order_id, lp_iv_id, interaction_id, interaction_type, " . "weighting, completion_time, correct_responses, " . "student_response, result, latency)" . "VALUES" . "(" . $index . "," . $lp_iv_id . ",'" . Database::escape_string($interaction[0]) . "','" . Database::escape_string($interaction[1]) . "'," . "'" . Database::escape_string($interaction[3]) . "','" . Database::escape_string($interaction[2]) . "','" . Database::escape_string($correct_resp) . "'," . "'" . Database::escape_string($interaction[5]) . "','" . Database::escape_string($interaction[6]) . "','" . Database::escape_string($interaction[7]) . "'" . ")";
$ivai_res = Database::query($ivai_sql, __FILE__, __LINE__);
}
}
}
}
}
if ($this->debug > 2) {
error_log('New LP - End of learnpathItem::write_to_db()', 0);
}
return true;
}
/**
* Displays the documents downloaded for a specific user in a specific course.
* @param string kind of view inside tracking info
* @param int User id
* @param string Course code
* @param int Session id (optional, default = 0)
* @return void
*/
public function display_document_tracking_info($view, $user_id, $course_id, $session_id = 0)
{
// protect data
$user_id = intval($user_id);
$course_id = Database::escape_string($course_id);
$session_id = intval($session_id);
$downloads_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
if (substr($view,4,1) == '1') {
$new_view = substr_replace($view,'0',4,1);
$title[1]= get_lang('DocumentsDetails');
$sql = "SELECT down_doc_path
FROM $downloads_table
WHERE down_cours_id = '$course_id'
AND down_user_id = '$user_id'
AND down_session_id = '$session_id'
GROUP BY down_doc_path";
$results = getManyResults1Col($sql);
$title_line = get_lang('DocumentsTitleDocumentColumn')."\n";
$line = null;
if (is_array($results)) {
for ($j = 0 ; $j < count($results) ; $j++) {
$line .= $results[$j]."\n";
}
} else {
$line = get_lang('NoResult');
}
} else {
$new_view = substr_replace($view,'1',4,1);
}
return array($title_line, $line);
}
if ($result) {
echo Display::display_confirmation_message(get_lang('MessageHasBeenSent'));
} else {
echo Display::display_error_message(get_lang('ErrorSendingMessage'));
}
break;
case 'send_invitation':
$subject = isset($_REQUEST['subject']) ? $_REQUEST['subject'] : null;
SocialManager::send_invitation_friend_user($_REQUEST['user_id'], $subject, $_REQUEST['content']);
break;
case 'find_users':
if (api_is_anonymous()) {
echo '';
break;
}
$track_online_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ONLINE);
$tbl_my_user = Database::get_main_table(TABLE_MAIN_USER);
$tbl_my_user_friend = Database::get_main_table(TABLE_MAIN_USER_REL_USER);
$tbl_user = Database::get_main_table(TABLE_MAIN_USER);
$tbl_access_url_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$search = Database::escape_string($_REQUEST['tag']);
$access_url_id = api_get_multiple_access_url() == 'true' ? api_get_current_access_url_id() : 1;
$user_id = api_get_user_id();
$is_western_name_order = api_is_western_name_order();
$likeCondition = " AND (firstname LIKE '%{$search}%' OR lastname LIKE '%{$search}%' OR email LIKE '%{$search}%') ";
if (api_get_setting('allow_social_tool') == 'true' && api_get_setting('allow_message_tool') == 'true') {
// All users
if (api_get_setting('allow_send_message_to_all_platform_users') == 'true' || api_is_platform_admin()) {
if ($access_url_id != 0) {
$sql = "SELECT DISTINCT u.user_id as id, u.firstname, u.lastname, u.email\n FROM {$tbl_user} u LEFT JOIN {$tbl_access_url_rel_user} r ON u.user_id = r.user_id\n WHERE\n u.status <> 6 AND\n u.user_id <> {$user_id} AND\n r.access_url_id = {$access_url_id}\n {$likeCondition} ";
} else {
/**
* Print the number of users that didn't login for a certain period of time
*/
static function print_users_not_logged_in_stats()
{
$total_logins = array();
$table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$current_url_id = api_get_current_access_url_id();
$total = self::count_users();
if (api_is_multiple_url_enabled()) {
$table_url = ", {$access_url_rel_user_table}";
$where_url = " AND login_user_id=user_id AND access_url_id='" . $current_url_id . "'";
} else {
$table_url = '';
$where_url = '';
}
$sql[get_lang('Thisday')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= NOW() {$where_url}";
$sql[get_lang('Last7days')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= NOW() {$where_url}";
$sql[get_lang('Last31days')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= NOW() {$where_url}";
$sql[sprintf(get_lang('LastXMonths'), 6)] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 6 MONTH) >= NOW() {$where_url}";
$sql[get_lang('NeverConnected')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} WHERE 1=1 {$where_url}";
foreach ($sql as $index => $query) {
$res = Database::query($query);
$obj = Database::fetch_object($res);
$r = $total - $obj->number;
$total_logins[$index] = $r < 0 ? 0 : $r;
}
Statistics::print_stats(get_lang('StatsUsersDidNotLoginInLastPeriods'), $total_logins, false);
}
/**
* Deletes an attempt from TABLE_STATISTIC_TRACK_E_HOTPOTATOES
* @param int $id
*/
function deleteAttempt($id)
{
$table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
$id = intval($id);
$sql = "DELETE FROM {$table} WHERE id = {$id}";
Database::query($sql);
}
// So, we have to reassign this variable again in order to keep its value right.
$charset = $charset_initial_value;
// The global variable $text_dir has been defined in the language file trad4all.inc.php.
// For determing text direction correspondent to the current language we use now information from the internationalization library.
$text_dir = api_get_text_direction();
// ===== "who is logged in?" module section =====
// check and modify the date of user in the track.e.online table
if (!($x = strpos($_SERVER['PHP_SELF'], 'whoisonline.php'))) {
preventMultipleLogin($_user["user_id"]);
LoginCheck(isset($_user['user_id']) ? $_user['user_id'] : '');
}
// ===== end "who is logged in?" module section =====
//Update of the logout_date field in the table track_e_login (needed for the calculation of the total connection time)
if (!isset($_SESSION['login_as']) && isset($_user)) {
// if $_SESSION['login_as'] is set, then the user is an admin logged as the user
$tbl_track_login = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$sql_last_connection = "SELECT login_id, login_date FROM {$tbl_track_login} WHERE login_user_id='" . $_user["user_id"] . "' ORDER BY login_date DESC LIMIT 0,1";
$q_last_connection = Database::query($sql_last_connection);
if (Database::num_rows($q_last_connection) > 0) {
$i_id_last_connection = Database::result($q_last_connection, 0, 'login_id');
// is the latest logout_date still relevant?
$sql_logout_date = "SELECT logout_date FROM {$tbl_track_login} WHERE login_id={$i_id_last_connection}";
$q_logout_date = Database::query($sql_logout_date);
$res_logout_date = convert_sql_date(Database::result($q_logout_date, 0, 'logout_date'));
if ($res_logout_date < time() - $_configuration['session_lifetime']) {
// it isn't, we should create a fresh entry
event_login();
// now that it's created, we can get its ID and carry on
$q_last_connection = Database::query($sql_last_connection);
$i_id_last_connection = Database::result($q_last_connection, 0, 'login_id');
}
/**
* TODO: Not used, to b deleted?
* Enter description here...
* @param int $user_id
* @param string $course_code
* @param date $year
* @param date $month
* @param date $day
* @return unknown
*/
static function get_connections_to_course_by_time($user_id, $course_code, $year = '', $month = '', $day = '') {
// Database table definitions
$tbl_track_course = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
$sql = 'SELECT login_course_date, logout_course_date FROM ' . $tbl_track_course . '
WHERE user_id = ' . intval($user_id) . '
AND course_code="' . Database::escape_string($course_code) . '"
ORDER BY login_course_date DESC';
$rs = Database::query($sql);
$connections = array();
while ($row = Database::fetch_array($rs)) {
$login_date = $row['login_course_date'];
$logout_date = $row['logout_course_date'];
$timestamp_login_date = strtotime($login_date);
$timestamp_logout_date = strtotime($logout_date);
$connections[] = array('login' => $timestamp_login_date, 'logout' => $timestamp_logout_date);
}
return $connections;
}
/**
* User logs in for the first time to a course
* @param string $course_code
* @param int $user_id
* @param int $session_id
*/
function event_course_login($course_code, $user_id, $session_id)
{
$course_tracking_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
//@todo use api_get_utc_datetime
$time = api_get_utc_datetime();
$course_code = Database::escape_string($course_code);
$user_id = intval($user_id);
$session_id = intval($session_id);
$session_lifetime = 3600;
//We select the last record for the current course in the course tracking table
$sql = "SELECT course_access_id\n FROM {$course_tracking_table}\n WHERE\n user_id = {$user_id} AND\n course_code = '{$course_code}' AND\n session_id = {$session_id} AND\n login_course_date > '{$time}' - INTERVAL {$session_lifetime} SECOND\n ORDER BY login_course_date DESC LIMIT 0,1";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
$i_course_access_id = Database::result($result, 0, 0);
//We update the course tracking table
$sql = "UPDATE {$course_tracking_table} SET logout_course_date = '{$time}', counter = counter+1\n WHERE course_access_id = " . intval($i_course_access_id) . " AND session_id = " . $session_id;
Database::query($sql);
} else {
$sql = "INSERT INTO {$course_tracking_table} (course_code, user_id, login_course_date, logout_course_date, counter, session_id)" . "VALUES('" . $course_code . "', '" . $user_id . "', '{$time}', '{$time}', '1','" . $session_id . "')";
Database::query($sql);
}
// Course catalog stats modifications see #4191
CourseManager::update_course_ranking(null, null, null, null, true, false);
}
/**
*
* @param int $courseId
* @param int $sessionId
* @param array $quizId
* @return array exercises
*/
public function getExerciseAndResult($courseId, $sessionId, $quizId = array())
{
if (empty($quizId)) {
return array();
}
$sessionId = intval($sessionId);
$ids = is_array($quizId) ? $quizId : array($quizId);
$ids = array_map('intval', $ids);
$ids = implode(',', $ids);
$track_exercises = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
if ($sessionId != 0) {
$sql = "SELECT * FROM {$track_exercises} te " . "INNER JOIN c_quiz cq ON cq.id = te.exe_exo_id " . "INNER JOIN course c ON te.exe_cours_id = c.code AND c.id = cq.c_id " . "WHERE " . "c.id = %s AND " . "te.session_id = %s AND " . "cq.id IN (%s) " . "ORDER BY cq.id ";
$sql = sprintf($sql, $courseId, $sessionId, $ids);
} else {
$sql = "SELECT * FROM {$track_exercises} te " . "INNER JOIN c_quiz cq ON cq.id = te.exe_exo_id " . "INNER JOIN course c ON te.exe_cours_id = c.code AND c.id = cq.c_id " . "WHERE " . "c.id = %s AND " . "cq.id IN (%s) " . "ORDER BY cq.id ";
$sql = sprintf($sql, $courseId, $ids);
}
$result = Database::query($sql);
$rows = array();
while ($row = Database::fetch_array($result, 'ASSOC')) {
$rows[] = $row;
}
return $rows;
}
$res = api_sql_query($sql, __FILE__, __LINE__);
$row = Database::fetch_array($res);
$lp_charset = $row['default_encoding'];
}
$htmlHeadXtra[] = "<style type='text/css'>\n/*<![CDATA[*/\n.secLine {background-color : #E6E6E6;}\n.content {padding-left : 15px;padding-right : 15px; }\n.specialLink{color : #0000FF;}\n/*]]>*/\n</style>\n<style media='print' type='text/css'>\n\n</style>";
/*
-----------------------------------------------------------
Constants and variables
-----------------------------------------------------------
*/
// regroup table names for maintenance purpose
$TABLETRACK_ACCESS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
$TABLETRACK_LINKS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LINKS);
$TABLETRACK_DOWNLOADS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
$TABLETRACK_ACCESS_2 = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$TABLETRACK_EXERCISES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$TABLECOURSE = Database::get_main_table(TABLE_MAIN_COURSE);
$TABLECOURSE_LINKS = Database::get_course_table(TABLE_LINK);
$table_user = Database::get_main_table(TABLE_MAIN_USER);
$TABLEQUIZ = Database::get_course_table(TABLE_QUIZ_TEST);
//$table_scormdata = Database::get_scorm_table(TABLE_SCORM_SCO_DATA);
//$table_scormmain = Database::get_scorm_table(TABLE_SCORM_MAIN);
//$tbl_learnpath_main = Database::get_course_table(TABLE_LEARNPATH_MAIN);
//$tbl_learnpath_item = Database::get_course_table(TABLE_LEARNPATH_ITEM);
//$tbl_learnpath_chapter = Database::get_course_table(TABLE_LEARNPATH_CHAPTER);
$tbl_learnpath_main = Database::get_course_table(TABLE_LP_MAIN);
$tbl_learnpath_item = Database::get_course_table(TABLE_LP_ITEM);
$tbl_learnpath_view = Database::get_course_table(TABLE_LP_VIEW);
$tbl_learnpath_item_view = Database::get_course_table(TABLE_LP_ITEM_VIEW);
if (isset($_GET['origin']) && $_GET['origin'] == 'resume_session') {
/**
* Calculate the total user time in the platform
* @param int $userId The user id
* @param string $from Optional. From date
* @param string $until Optional. Until date
* @return string The time (hh:mm:ss)
*/
public static function getTotalUserTimeInPlatform($userId, $from = '', $until = '')
{
$userId = intval($userId);
$trackLoginTable = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$whereConditions = array(
'login_user_id = ? ' => $userId
);
if (!empty($from) && !empty($until)) {
$whereConditions["AND (login_date >= '?' "] = $from;
$whereConditions["AND logout_date <= DATE_ADD('?', INTERVAL 1 DAY)) "] = $until;
}
$trackResult = Database::select(
'SEC_TO_TIME(SUM(UNIX_TIMESTAMP(logout_date) - UNIX_TIMESTAMP(login_date))) as total_time',
$trackLoginTable,
array(
'where' => $whereConditions
), 'first'
);
if ($trackResult != false) {
return $trackResult['total_time'] ? $trackResult['total_time'] : '00:00:00';
}
return '00:00:00';
}
}
// oar
if ($hotspot['hotspot_type'] == 'oar') {
$output .= "&hotspot_" . $hotspot['id'] . "_type=delineation";
}
$output .= "&hotspot_" . $hotspot['id'] . "_coord=" . $hotspot['hotspot_coordinates'] . "";
$i++;
}
// Generate empty (the maximum number of points is 12 - it is said so in the user interface)
$i++;
for ($i; $i <= 12; $i++) {
$output .= "&hotspot_" . $i . "=false";
}
// Get clicks
if (isset($_SESSION['exerciseResultCoordinates']) && $from_db == 0) {
foreach ($_SESSION['exerciseResultCoordinates'][$questionId] as $coordinate) {
$output2 .= $coordinate . "|";
}
} else {
// get it from db
$tbl_track_e_hotspot = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_HOTSPOT);
$sql = "SELECT hotspot_coordinate\n FROM {$tbl_track_e_hotspot}\n WHERE hotspot_question_id = {$questionId} AND \n hotspot_course_code = '{$course_code}' AND \n hotspot_exe_id = {$exe_id} \n ORDER by hotspot_id";
$rs = @Database::query($sql);
// don't output error because we are in Flash execution.
while ($row = Database::fetch_array($rs)) {
$output2 .= $row['hotspot_coordinate'] . "|";
}
}
$output .= "&p_hotspot_answers=" . api_substr($output2, 0, -1) . "&done=done";
$explode = explode('&', $output);
echo $output;
/**
* return total score for test exe_id for all question in the category $in_cat_id for user
* If no question for this category, return ""
*/
public static function getCatScoreForExeidForUserid($in_cat_id, $in_exe_id, $in_user_id)
{
$tbl_track_attempt = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
$tbl_question_rel_category = Database::get_course_table(TABLE_QUIZ_QUESTION_REL_CATEGORY);
$in_cat_id = intval($in_cat_id);
$in_exe_id = intval($in_exe_id);
$in_user_id = intval($in_user_id);
$query = "SELECT DISTINCT\n\t\t marks, exe_id, user_id, ta.question_id, category_id\n FROM {$tbl_track_attempt} ta , {$tbl_question_rel_category} qrc\n WHERE\n ta.question_id=qrc.question_id AND\n qrc.category_id={$in_cat_id} AND\n exe_id={$in_exe_id} AND user_id={$in_user_id}";
$res = Database::query($query);
$totalcatscore = "";
while ($data = Database::fetch_array($res)) {
$totalcatscore += $data['marks'];
}
return $totalcatscore;
}
@author Andre Boivin base code
@author Julio Montoya fixing lot of little details
@todo this script is not ready for a production use that's why I'm commenting the function delete_inactive_student
* @package chamilo.admin
* script pour effacer les user inactif depuis x temps
*/
// name of the language file that needs to be included
$language_file = array('registration', 'admin');
$cidReset = true;
require_once '../inc/global.inc.php';
require_once api_get_path(LIBRARY_PATH) . 'sortable_table.class.php';
require_once api_get_path(LIBRARY_PATH) . 'formvalidator/FormValidator.class.php';
require_once api_get_path(LIBRARY_PATH) . 'security.lib.php';
require_once api_get_path(LIBRARY_PATH) . 'usermanager.lib.php';
$tbl_stats_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$this_section = SECTION_PLATFORM_ADMIN;
api_protect_admin_script(true);
/**
* Make sure this function is protected because it does NOT check password!
*
*/
/** INIT SECTION
*/
Display::display_header($tool_name, "");
//On sélectionne les user élèves
$sql = "SELECT user_id FROM " . $table_user . " user WHERE user.status= '5' ORDER by lastname ";
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$user_id = $row['user_id'];
// pour chaque élève, on trouve la dernière connexion
/**
* 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 .= ' ';
} else {
$actions .= "<a href='exercise_show.php?" . api_get_cidreq() . "&action=qualify&id={$id}'>" . Display::return_icon('quiz.gif', get_lang('Qualify'));
$actions .= ' ';
}
$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 . ' ';
}
} 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;
}
/**
* @author Sebastien Piraux <*****@*****.**>
* @author Julio Montoya
* @desc Record information for upload event
* @param int $docId
* @param int $userId
* @param string $courseCode
* @param int $sessionId
* @return int
*/
function event_upload($docId, $userId, $courseCode, $sessionId)
{
$table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_UPLOADS);
$reallyNow = api_get_utc_datetime();
$userId = intval($userId);
$docId = intval($docId);
$sessionId = intval($sessionId);
$courseCode = Database::escape_string($courseCode);
$sql = "INSERT INTO ".$table."
( upload_user_id,
upload_cours_id,
upload_work_id,
upload_date,
upload_session_id
)
VALUES (
".$userId.",
'".$courseCode."',
'".$docId."',
'".$reallyNow."',
'".$sessionId."'
)";
Database::query($sql);
return 1;
}
require_once '../inc/global.inc.php';
require_once 'exercise.lib.php';
require_once api_get_path(LIBRARY_PATH) . 'mail.lib.inc.php';
if (empty($origin)) {
$origin = isset($_REQUEST['origin']) ? $_REQUEST['origin'] : null;
}
if ($origin == 'learnpath') {
api_protect_course_script(false, false, true);
} else {
api_protect_course_script(true, false, true);
}
// Database table definitions
$TBL_EXERCICE_QUESTION = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION);
$TBL_QUESTIONS = Database::get_course_table(TABLE_QUIZ_QUESTION);
$TBL_TRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$TBL_TRACK_ATTEMPT = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
// General parameters passed via POST/GET
if ($debug) {
error_log('Entered exercise_result.php: ' . print_r($_POST, 1));
}
if (empty($formSent)) {
$formSent = $_REQUEST['formSent'];
}
if (empty($exerciseResult)) {
$exerciseResult = $_SESSION['exerciseResult'];
}
if (empty($questionId)) {
$questionId = $_REQUEST['questionId'];
}
if (empty($choice)) {
$choice = $_REQUEST['choice'];
api_not_allowed();
}
$obj_question = Question::read($my_qst);
if (isset($_SESSION['gradebook'])) {
$gradebook = $_SESSION['gradebook'];
}
if (!empty($gradebook) && $gradebook == 'view') {
$interbreadcrumb[] = array('url' => '../gradebook/' . $_SESSION['gradebook_dest'], 'name' => get_lang('ToolGradebook'));
}
$nameTools = get_lang('Exercice');
$interbreadcrumb[] = array("url" => "exercice.php", "name" => get_lang('Exercices'));
$my_msg = 'No change.';
if ($action == 'mark') {
if (!empty($_POST['score']) and $_POST['score'] < $obj_question->selectWeighting() and $_POST['score'] >= 0) {
//mark the user mark into the database using something similar to the following function:
$exercise_table = Database::get_statistic_table('track_e_exercices');
#global $origin, $tbl_learnpath_user, $learnpath_id, $learnpath_item_id;
$sql = "SELECT * FROM {$exercise_table}\n\t\t\t WHERE exe_user_id = " . intval($my_usr) . " AND exe_cours_id = '" . Database::escape_string($my_cid) . "' AND exe_exo_id = " . intval($my_exe) . "\n\t\t\t ORDER BY exe_date DESC";
#echo $sql;
$res = Database::query($sql);
if (Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
//@todo Check that just summing past score and the new free answer mark doesn't come up
// with a score higher than the possible score for that exercise
$my_score = $row['exe_result'] + $_POST['score'];
$sql = "UPDATE {$exercise_table} SET exe_result = '{$my_score}'\n\t\t\t\t WHERE exe_id = '" . $row['exe_id'] . "'";
#echo $sql;
$res = Database::query($sql);
$my_msg = get_lang('MarkIsUpdated');
} else {
$my_score = $_POST['score'];
require_once api_get_path(LIBRARY_PATH) . 'document.lib.php';
/* Constants and variables */
$is_allowedToEdit = api_is_allowed_to_edit(null, true);
$is_tutor = api_is_allowed_to_edit(true);
if (!$is_allowedToEdit) {
header('Location: /main/exercice/exercice.php?cidReq=' . Security::remove_XSS($_GET['cidReq']));
exit;
}
$interbreadcrumb[] = array('url' => 'exercise_report.php', 'name' => get_lang('Exercices'));
$interbreadcrumb[] = array('url' => 'exercise_report.php' . '?filter=2', 'name' => get_lang('StudentScore'));
$interbreadcrumb[] = array('url' => 'exercise_history.php' . '?exe_id=' . intval($_GET['exe_id']), 'name' => get_lang('Details'));
$TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
$TBL_EXERCICES = Database::get_course_table(TABLE_QUIZ_TEST);
$TBL_EXERCICES_QUESTION = Database::get_course_table(TABLE_QUIZ_QUESTION);
$TBL_TRACK_EXERCICES = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
$TBL_TRACK_ATTEMPT_RECORDING = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ATTEMPT_RECORDING);
Display::display_header($nameTools, get_lang('Exercise'));
if (isset($_GET['message'])) {
if (in_array($_GET['message'], array('ExerciseEdited'))) {
$my_message_history = Security::remove_XSS($_GET['message']);
Display::display_confirmation_message(get_lang($my_message_history));
}
}
echo '<div class="actions">';
echo '<a href="exercise_report.php?' . api_get_cidreq() . '&filter=2">' . Display::return_icon('back.png', get_lang('BackToResultList'), '', ICON_SIZE_MEDIUM) . '</a>';
echo '</div>';
?>
<table class="data_table">
<tr class="row_odd">
<th><?php
/**
* Make sure this function is protected because it does NOT check password!
*
* This function defines globals.
* @param int User ID
* @return bool False on failure, redirection on success
* @author Evie Embrechts
* @author Yannick Warnier <*****@*****.**>
*/
function login_user($user_id)
{
$user_id = intval($user_id);
$user_info = api_get_user_info($user_id);
// Check if the user is allowed to 'login_as'
$can_login_as = api_can_login_as($user_id);
if (!$can_login_as) {
return false;
}
//Load $_user to be sure we clean it before logging in
global $uidReset, $loginFailed, $_user;
$main_user_table = Database::get_main_table(TABLE_MAIN_USER);
$main_admin_table = Database::get_main_table(TABLE_MAIN_ADMIN);
$track_e_login_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LOGIN);
unset($_user['user_id']);
// uid not in session ? prevent any hacking
$firstname = $user_info['firstname'];
$lastname = $user_info['lastname'];
$user_id = $user_info['user_id'];
//$message = "Attempting to login as ".api_get_person_name($firstname, $lastname)." (id ".$user_id.")";
if (api_is_western_name_order()) {
$message = sprintf(get_lang('AttemptingToLoginAs'), $firstname, $lastname, $user_id);
} else {
$message = sprintf(get_lang('AttemptingToLoginAs'), $lastname, $firstname, $user_id);
}
$loginFailed = false;
$uidReset = false;
if ($user_id) {
// a uid is given (log in succeeded)
$sql_query = "SELECT user.*, a.user_id is_admin,\n\t\t\tUNIX_TIMESTAMP(login.login_date) login_date\n\t\t\tFROM {$main_user_table}\n\t\t\tLEFT JOIN {$main_admin_table} a\n\t\t\tON user.user_id = a.user_id\n\t\t\tLEFT JOIN {$track_e_login_table} login\n\t\t\tON user.user_id = login.login_user_id\n\t\t\tWHERE user.user_id = '" . $user_id . "'\n\t\t\tORDER BY login.login_date DESC LIMIT 1";
$sql_result = Database::query($sql_query);
if (Database::num_rows($sql_result) > 0) {
// Extracting the user data
$user_data = Database::fetch_array($sql_result);
//Delog the current user
LoginDelete($_SESSION["_user"]["user_id"]);
// Cleaning session variables
unset($_SESSION['_user']);
unset($_SESSION['is_platformAdmin']);
unset($_SESSION['is_allowedCreateCourse']);
unset($_SESSION['_uid']);
$_user['firstName'] = $user_data['firstname'];
$_user['lastName'] = $user_data['lastname'];
$_user['mail'] = $user_data['email'];
$_user['lastLogin'] = $user_data['login_date'];
$_user['official_code'] = $user_data['official_code'];
$_user['picture_uri'] = $user_data['picture_uri'];
$_user['user_id'] = $user_data['user_id'];
$_user['status'] = $user_data['status'];
$is_platformAdmin = (bool) (!is_null($user_data['is_admin']));
$is_allowedCreateCourse = (bool) ($user_data['status'] == 1);
// Filling session variables with new data
$_SESSION['_uid'] = $user_id;
$_SESSION['_user'] = $_user;
$_SESSION['is_platformAdmin'] = $is_platformAdmin;
$_SESSION['is_allowedCreateCourse'] = $is_allowedCreateCourse;
$_SESSION['login_as'] = true;
// will be useful later to know if the user is actually an admin or not (example reporting)s
$target_url = api_get_path(WEB_PATH) . "user_portal.php";
$message .= '<br />' . sprintf(get_lang('LoginSuccessfulGoToX'), '<a href="' . $target_url . '">' . $target_url . '</a>');
Display::display_header(get_lang('UserList'));
Display::display_normal_message($message, false);
Display::display_footer();
exit;
} else {
exit("<br />WARNING UNDEFINED UID !! ");
}
}
}
/*<![CDATA[*/
.secLine {background-color : #E6E6E6;}
.content {padding-left : 15px;padding-right : 15px; }
.specialLink{color : #0000FF;}
/*]]>*/
</style>
<style media='print' type='text/css'>
/*<![CDATA[*/
td {border-bottom: thin dashed gray;}
/*]]>*/
</style>";
$TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$TABLECOURSE_GROUPSUSER = Database::get_course_table(TABLE_GROUP_USER);
$TABLEUSER = Database::get_main_table(TABLE_MAIN_USER);
$TABLETRACK_ACCESS = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
Display::display_header($nameTools, "Tracking");
include(api_get_path(LIBRARY_PATH) . "statsUtils.lib.inc.php");
// the variables for the days and the months
// Defining the shorts for the days
$DaysShort = api_get_week_days_short();
// Defining the days of the week to allow translation of the days
$DaysLong = api_get_week_days_long();
// Defining the months of the year to allow translation of the months
$MonthsLong = api_get_months_long();
// Defining the months of the year to allow translation of the months
$MonthsShort = api_get_months_short();
$is_allowedToTrack = $is_groupTutor; // allowed to track only user of one group
$is_allowedToTrackEverybodyInCourse = $is_courseAdmin; // allowed to track all student in course
/**
* Returns the "what's new" icon notifications
*
* The general logic of this function is to track the last time the user
* entered the course and compare to what has changed inside this course
* since then, based on the item_property table inside this course. Note that,
* if the user never entered the course before, he will not see notification
* icons. This function takes session ID into account (if any) and only shows
* the corresponding notifications.
* @param array Course information array, containing at least elements 'db' and 'k'
* @return string The HTML link to be shown next to the course
*/
public static function show_notification($course_info)
{
$t_track_e_access = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
$course_tool_table = Database::get_course_table(TABLE_TOOL_LIST);
$tool_edit_table = Database::get_course_table(TABLE_ITEM_PROPERTY);
$course_code = Database::escape_string($course_info['code']);
$user_id = api_get_user_id();
$course_id = $course_info['real_id'];
$course_info['id_session'] = intval($course_info['id_session']);
// Get the user's last access dates to all tools of this course
$sql = "SELECT *\n FROM {$t_track_e_access} USE INDEX (access_cours_code, access_user_id)\n WHERE\n access_cours_code = '" . $course_code . "' AND\n access_user_id = '{$user_id}' AND\n access_session_id ='" . $course_info['id_session'] . "'";
$resLastTrackInCourse = Database::query($sql);
$oldestTrackDate = $oldestTrackDateOrig = '3000-01-01 00:00:00';
while ($lastTrackInCourse = Database::fetch_array($resLastTrackInCourse)) {
$lastTrackInCourseDate[$lastTrackInCourse['access_tool']] = $lastTrackInCourse['access_date'];
if ($oldestTrackDate > $lastTrackInCourse['access_date']) {
$oldestTrackDate = $lastTrackInCourse['access_date'];
}
}
if ($oldestTrackDate == $oldestTrackDateOrig) {
//if there was no connexion to the course ever, then take the
// course creation date as a reference
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$sql = "SELECT course.creation_date " . "FROM {$course_table} course " . "WHERE course.code = '" . $course_code . "'";
$res = Database::query($sql);
if ($res && Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
}
$oldestTrackDate = $row['creation_date'];
}
// Get the last edits of all tools of this course.
$sql = "SELECT\n tet.*,\n tet.lastedit_date last_date,\n tet.tool tool,\n tet.ref ref,\n tet.lastedit_type type,\n tet.to_group_id group_id,\n ctt.image image,\n ctt.link link\n FROM {$tool_edit_table} tet, {$course_tool_table} ctt\n WHERE\n tet.c_id = {$course_id} AND\n ctt.c_id = {$course_id} AND\n tet.lastedit_date > '{$oldestTrackDate}' " . " AND (ctt.name = tet.tool OR (ctt.name = 'student_publication' AND tet.tool = 'work')) " . " AND ctt.visibility = '1' " . " AND tet.lastedit_user_id != {$user_id} AND tet.id_session = '" . $course_info['id_session'] . "'\n ORDER BY tet.lastedit_date";
$res = Database::query($sql);
// Get the group_id's with user membership.
$group_ids = GroupManager::get_group_ids($course_info['real_id'], $user_id);
$group_ids[] = 0;
//add group 'everyone'
$notifications = array();
// Filter all last edits of all tools of the course
while ($res && ($item_property = Database::fetch_array($res))) {
// First thing to check is if the user never entered the tool
// or if his last visit was earlier than the last modification.
if ((!isset($lastTrackInCourseDate[$item_property['tool']]) || $lastTrackInCourseDate[$item_property['tool']] < $item_property['lastedit_date']) && (in_array($item_property['to_group_id'], $group_ids) && ($item_property['tool'] != TOOL_DROPBOX && $item_property['tool'] != TOOL_NOTEBOOK && $item_property['tool'] != TOOL_CHAT)) && ($item_property['visibility'] == '1' || $course_info['status'] == '1' && $item_property['visibility'] == '0' || !isset($item_property['visibility']))) {
if ($course_info['real_id'] == 1) {
// var_dump($item_property);
}
// Also drop announcements and events that are not for the user or his group.
if (($item_property['tool'] == TOOL_ANNOUNCEMENT || $item_property['tool'] == TOOL_CALENDAR_EVENT) && ($item_property['to_user_id'] != $user_id && (!isset($item_property['to_group_id']) || !in_array($item_property['to_group_id'], $group_ids)))) {
continue;
}
// If it's a survey, make sure the user's invited. Otherwise drop it.
if ($item_property['tool'] == TOOL_SURVEY) {
$survey_info = survey_manager::get_survey($item_property['ref'], 0, $course_code);
if (!empty($survey_info)) {
$invited_users = SurveyUtil::get_invited_users($survey_info['code'], $course_code);
if (!in_array($user_id, $invited_users['course_users'])) {
continue;
}
}
}
// If it's a learning path, ensure it is currently visible to the user
if ($item_property['tool'] == TOOL_LEARNPATH) {
require_once api_get_path(SYS_CODE_PATH) . 'newscorm/learnpath.class.php';
if (!learnpath::is_lp_visible_for_student($item_property['ref'], $user_id, $course_code)) {
continue;
}
}
if ($item_property['tool'] == 'work' && $item_property['type'] == 'DirectoryCreated') {
$item_property['lastedit_type'] = 'WorkAdded';
}
$notifications[$item_property['tool']] = $item_property;
}
}
// Show all tool icons where there is something new.
$retvalue = ' ';
while (list($key, $notification) = each($notifications)) {
$lastDate = date('d/m/Y H:i', convert_sql_date($notification['lastedit_date']));
$type = $notification['lastedit_type'];
if (empty($course_info['id_session'])) {
$my_course['id_session'] = 0;
} else {
$my_course['id_session'] = $course_info['id_session'];
}
$label = get_lang('TitleNotification') . ": " . get_lang($type) . " ({$lastDate})";
$retvalue .= '<a href="' . api_get_path(WEB_CODE_PATH) . $notification['link'] . '?cidReq=' . $course_code . '&ref=' . $notification['ref'] . '&gidReq=' . $notification['to_group_id'] . '&id_session=' . $my_course['id_session'] . '">' . Display::return_icon($notification['image'], $label) . '</a> ';
}
return $retvalue;
}
FROM $tbl_session as session
WHERE session.id_coach = ".$_user['user_id']."
ORDER BY date_start, date_end, name");
while ($session = Database:: fetch_array($result)) {
$session_is_coach[$session['id']] = $session;
}
$students_online = array();
foreach ($session_is_coach as $session) {
$sql = "SELECT DISTINCT last_access.access_user_id,
last_access.access_date,
last_access.access_cours_code,
last_access.access_session_id,
".(api_is_western_name_order() ? "CONCAT(user.firstname,' ',user.lastname)" : "CONCAT(user.lastname,' ',user.firstname)")." as name,
user.email
FROM ".Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_LASTACCESS)." AS last_access
INNER JOIN ".Database::get_main_table(TABLE_MAIN_USER)." AS user
ON user.user_id = last_access.access_user_id
WHERE access_session_id='".$session['id']."'
AND NOW()-access_date<1000 GROUP BY access_user_id";
$result = Database::query($sql);
while($user_list = Database::fetch_array($result)) {
$students_online[$user_list['access_user_id']] = $user_list;
}
}
if (count($students_online) > 0) {
foreach ($students_online as $student_online) {
echo "<tr>
function processStudentList($filter_score, $global, $exercise, $courseInfo, $sessionId, $newSessionList)
{
$exerciseStatsTable = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES);
if (empty($sessionId)) {
$students = CourseManager::get_student_list_from_course_code(
$courseInfo['code'],
false
);
} else {
$students = CourseManager::get_student_list_from_course_code(
$courseInfo['code'],
true,
$sessionId
);
}
$html = null;
$totalStudents = count($students);
if (!$global) {
$html .= "<tr>";
}
if (!$global) {
$html .= '<td rowspan="'.$totalStudents.'">';
} else {
$html .= '<td>';
}
$html .= $exercise['title'];
if ($global && !empty($sessionId)) {
$sessionName = isset($newSessionList[$sessionId]) ? $newSessionList[$sessionId] : null;
$html .= Display::return_icon('star.png', get_lang('Session')).' ('.$sessionName.')';
}
$html .= '</td>';
$globalRow = array(
$courseInfo['title'],
$exercise['title']
);
$total_with_parameter_score = 0;
$taken = 0;
$export_array_global = array();
$studentResult = array();
foreach ($students as $student) {
$studentId = isset($student['user_id']) ? $student['user_id'] : $student['id_user'];
$sql = "SELECT COUNT(ex.exe_id) as count
FROM $exerciseStatsTable AS ex
WHERE
ex.exe_cours_id = '".$courseInfo['code']."' AND
ex.exe_exo_id = ".$exercise['id']." AND
exe_user_id='".$studentId."' AND
session_id = $sessionId
";
$result = Database::query($sql);
$attempts = Database::fetch_array($result);
$sql = "SELECT exe_id, exe_result, exe_weighting
FROM $exerciseStatsTable
WHERE
exe_user_id = ".$studentId." AND
exe_cours_id = '".$courseInfo['code']."' AND
exe_exo_id = ".$exercise['id']." AND
session_id = $sessionId
ORDER BY exe_result DESC
LIMIT 1";
$result = Database::query($sql);
$score = 0;
$weighting = 0;
while ($scoreInfo = Database::fetch_array($result)) {
$score = $score + $scoreInfo['exe_result'];
$weighting = $weighting + $scoreInfo['exe_weighting'];
}
$percentageScore = 0;
if ($weighting != 0) {
$percentageScore = round(($score*100)/$weighting);
}
if ($attempts['count'] > 0 ) {
$taken++;
}
if ($percentageScore >= $filter_score) {
$total_with_parameter_score++;
}
$tempArray = array();
if (!$global) {
$userInfo = api_get_user_info($studentId);
// User
$userRow = '<td>';
$userRow .= $userInfo['complete_name'];
$userRow .= '</td>';
// Best result
if (!empty($attempts['count'])) {
$userRow .= '<td>';
$userRow .= $percentageScore;
$tempArray[] = $percentageScore;
$userRow .= '</td>';
if ($percentageScore >= $filter_score) {
$userRow .= '<td style="background-color:#DFFFA8">';
$userRow .= get_lang('PassExam').'</td>';
$tempArray[] = get_lang('PassExam');
} else {
$userRow .= '<td style="background-color:#FC9A9E" >';
$userRow .= get_lang('ExamFail').'</td>';
$tempArray[] = get_lang('ExamFail');
}
$userRow .= '<td>';
$userRow .= $attempts['count'];
$tempArray[] = $attempts['count'];
$userRow .= '</td>';
} else {
$score = '-';
$userRow .= '<td>';
$userRow .= '-';
$tempArray[] = '-';
$userRow .= '</td>';
$userRow .= '<td style="background-color:#FCE89A">';
$userRow .= get_lang('NoAttempt');
$tempArray[] = get_lang('NoAttempt');
$userRow .= '</td>';
$userRow .= '<td>';
$userRow .= 0;
$tempArray[] = 0;
$userRow .= '</td>';
}
$userRow .= '</tr>';
$studentResult[$studentId] = array(
'html' => $userRow,
'score' => $score,
'array' => $tempArray,
'user' => $userInfo['complete_name']
);
}
}
$row_not_global['exercise'] = $exercise['title'];
if (!$global) {
if (!empty($studentResult)) {
$studentResultEmpty = $studentResultContent = array();
foreach ($studentResult as $row) {
if ($row['score'] == '-') {
$studentResultEmpty[] = $row;
} else {
$studentResultContent[] = $row;
}
}
// Sort only users with content
usort($studentResultContent, 'sort_user');
$studentResult = array_merge($studentResultContent, $studentResultEmpty);
foreach ($studentResult as $row) {
$html .= $row['html'];
$row_not_global['results'][] = $row['array'];
$row_not_global['users'][] = $row['user'];
}
$export_array[] = $row_not_global;
}
}
if ($global) {
// Exam taken
$html .= '<td>';
$html .= $taken;
$globalRow[]= $taken;
$html .= '</td>';
// Exam NOT taken
$html .= '<td>';
$html .= $not_taken = $totalStudents - $taken;
$globalRow[]= $not_taken;
$html .= '</td>';
// Exam pass
if (!empty($total_with_parameter_score)) {
$html .= '<td style="background-color:#DFFFA8" >';
} else {
$html .= '<td style="background-color:#FCE89A" >';
}
$html .= $total_with_parameter_score;
$globalRow[]= $total_with_parameter_score;
$html .= '</td>';
// Exam fail
$html .= '<td>';
$html .= $fail = $taken - $total_with_parameter_score;
$globalRow[]= $fail;
$html .= '</td>';
$html .= '<td>';
$html .= $totalStudents;
$globalRow[]= $totalStudents;
$html .= '</td>';
$html .= '</tr>';
$export_array_global[] = $globalRow;
}
return array(
'html' => $html,
'export_array_global' => $export_array_global,
'total_students' => $totalStudents
);
}