protected function isDocumentDownloadableInCourse($requestedUrl)
{
if (claro_is_in_a_group()) {
$groupContext = true;
$courseContext = false;
$is_allowedToEdit = claro_is_group_member() || claro_is_group_tutor() || claro_is_course_manager();
} else {
$groupContext = false;
$courseContext = true;
$is_allowedToEdit = claro_is_course_manager();
}
if ($courseContext) {
$courseTblList = claro_sql_get_course_tbl();
$tbl_document = $courseTblList['document'];
if (strtoupper(substr(PHP_OS, 0, 3)) == "WIN") {
$modifier = '';
} else {
$modifier = 'BINARY ';
}
$sql = "SELECT visibility\n FROM `{$tbl_document}`\n WHERE {$modifier} path = '" . claro_sql_escape($requestedUrl) . "'";
$docVisibilityStatus = claro_sql_query_get_single_value($sql);
if (!is_null($docVisibilityStatus) && $docVisibilityStatus == 'i' && !$is_allowedToEdit) {
return false;
} else {
return true;
}
} else {
// ????
}
}
function extractResponseFromTracking($attemptDetailsId)
{
$tbl_cdb_names = get_module_course_tbl(array('qwz_tracking_answers'), claro_get_current_course_id());
$tbl_qwz_tracking_answers = $tbl_cdb_names['qwz_tracking_answers'];
// get the answers the user has gaven for this question
$sql = "SELECT `answer`\n FROM `" . $tbl_qwz_tracking_answers . "`\n WHERE `details_id` = " . (int) $attemptDetailsId . " ORDER BY `id` ASC ";
$this->response = claro_sql_query_get_single_value($sql);
return true;
}
/**
* Return the title of an item in a course
*
* @param $courseId identifies a course in database
* @param $itemId integer who identifies the exercice
* @return string The title of the item
*/
function _getTitle($courseId, $itemId)
{
$tbl_cdb_names = get_module_course_tbl(array('lp_learnPath'), $courseId);
$tblItem = $tbl_cdb_names['lp_learnPath'];
$sql = 'SELECT `name`
FROM `' . $tblItem . '`
WHERE `learnPath_id`=' . (int) $itemId;
$title = claro_sql_query_get_single_value($sql);
return $title;
}
/**
* return the number of exercises using question $quId
*
* @param $quId requested question id
* @return number of exercises using question $quId
* @author Sebastien Piraux <*****@*****.**>
*/
function count_exercise_using_question($quId)
{
$tbl_cdb_names = get_module_course_tbl(array('qwz_rel_exercise_question'), claro_get_current_course_id());
$tbl_quiz_rel_exercise_question = $tbl_cdb_names['qwz_rel_exercise_question'];
$sql = "SELECT COUNT(`exerciseId`)\n FROM `" . $tbl_quiz_rel_exercise_question . "`\n WHERE `questionId` = '" . (int) $quId . "'";
$exerciseCount = claro_sql_query_get_single_value($sql);
if (!$exerciseCount) {
return 0;
} else {
return $exerciseCount;
}
}
/**
* CLAROLINE
*
* @version $Revision: 13511 $
* @copyright (c) 2001-2011, Universite catholique de Louvain (UCL)
* @license http://www.gnu.org/copyleft/gpl.html (GPL) GENERAL PUBLIC LICENSE
* @author Piraux S�bastien <*****@*****.**>
* @author Lederer Guillaume <*****@*****.**>
* @package CLLNP
* @since 1.8
*/
function lp_display_document($TABLEASSET)
{
// document browser vars
// Update infos about asset
$sql = "SELECT `path`\n FROM `" . $TABLEASSET . "`\n WHERE `module_id` = " . (int) $_SESSION['module_id'];
$assetPath = claro_sql_query_get_single_value($sql);
$courseDir = claro_get_course_path() . '/document';
$baseWorkDir = get_path('coursesRepositorySys') . $courseDir;
$file = $baseWorkDir . $assetPath;
$fileSize = format_file_size(filesize($file));
$fileDate = format_date(filectime($file));
//####################################################################################\\
//######################## DISPLAY DETAILS ABOUT THE DOCUMENT ########################\\
//####################################################################################\\
$out = '';
$out .= "\n\n" . '<hr noshade="noshade" size="1" />' . "\n\n" . '<h4>' . get_lang('Document in module') . '</h4>' . "\n\n" . '<table class="claroTable" width="100%" border="0" cellspacing="2">' . "\n" . '<thead>' . "\n" . '<tr class="headerX">' . "\n" . '<th>' . get_lang('Filename') . '</th>' . "\n" . '<th>' . get_lang('Size') . '</th>' . "\n" . '<th>' . get_lang('Date') . '</th>' . "\n" . '</tr>' . "\n" . '</thead>' . "\n" . '<tbody>' . "\n" . '<tr align="center">' . "\n" . '<td align="left">' . basename($file) . '</td>' . "\n" . '<td>' . $fileSize . '</td>' . "\n" . '<td>' . $fileDate . '</td>' . "\n" . '</tr>' . "\n" . '</tbody>' . "\n" . '</table>' . "\n";
return $out;
}
$is_blocked = false;
$atleastOne = false;
$moduleNb = 0;
// look for maxDeep
$maxDeep = 1;
// used to compute colspan of <td> cells
for ($i = 0; $i < sizeof($flatElementList); $i++) {
if ($flatElementList[$i]['children'] > $maxDeep) {
$maxDeep = $flatElementList[$i]['children'];
}
}
$moduleNameLength = 25;
// size of 'name' to display in the list, the string will be partially displayed if it is more than $moduleNameLength letters long
// get the name of the learning path
$sql = "SELECT `name`\n FROM `" . $TABLELEARNPATH . "`\n WHERE `learnPath_id` = '" . (int) $_SESSION['path_id'] . "'";
$lpName = claro_sql_query_get_single_value($sql);
$out .= '<p><b>' . wordwrap($lpName, $moduleNameLength, ' ', 1) . '</b></p>' . "\n" . '<p>' . "\n" . '<small>' . get_lang('View') . ' : ' . '<a href="' . claro_htmlspecialchars(Url::Contextualize('viewer.php?frames=0')) . '" target="_top">' . get_lang('Fullscreen') . '</a>' . ' | ' . '<a href="' . claro_htmlspecialchars(Url::Contextualize('viewer.php?frames=1')) . '" target="_top">' . get_lang('In frames') . '</a>' . '</small>' . "\n" . '</p>' . "\n\n" . '<table width="100%">' . "\n\n";
$previous = "";
// temp id of previous module, used as a buffer in foreach
$previousModule = "";
// module id that will be used in the previous link
$nextModule = "";
// module id that will be used in the next link
foreach ($flatElementList as $module) {
if ($module['contentType'] == CTEXERCISE_) {
$moduleImg = get_icon_url('quiz', 'CLQWZ');
} else {
$moduleImg = get_icon_url(choose_image(basename($module['path'])));
}
$contentType_alt = selectAlt($module['contentType']);
if ($module['scoreMax'] > 0 && $module['raw'] > 0) {
/**
*
* @copyright (c) 2001-2011, Universite catholique de Louvain (UCL)
* @param int $userId
* @param int $topicId
* @return bool
*/
function is_topic_notification_requested($topicId, $userId)
{
$tbl_cdb_names = claro_sql_get_course_tbl();
$tbl_user_notify = $tbl_cdb_names['bb_rel_topic_userstonotify'];
$sql = "SELECT COUNT(*) notification_qty\n FROM `" . $tbl_user_notify . "`\n WHERE `user_id` = " . (int) $userId . "\n AND `topic_id` = " . (int) $topicId;
if (claro_sql_query_get_single_value($sql) > 0) {
return true;
} else {
return false;
}
}
}
$userList = $myPager->get_result_list();
if (is_array($userList)) {
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_course = $tbl_mdb_names['course'];
$tbl_category = $tbl_mdb_names['category'];
$tbl_rel_course_user = $tbl_mdb_names['rel_course_user'];
$tbl_rel_course_category = $tbl_mdb_names['rel_course_category'];
foreach ($userList as $userKey => $user) {
// Count number of courses
$sql = "SELECT count(DISTINCT code_cours) AS qty_course\n FROM `" . $tbl_rel_course_user . "`\n WHERE user_id = '" . (int) $user['user_id'] . "'\n GROUP BY user_id";
$userList[$userKey]['qty_course'] = (int) claro_sql_query_get_single_value($sql);
$userList[$userKey]['qty_class'] = count(get_class_list_for_user_id($user['user_id']));
// Count number of categories
$sql = "SELECT COUNT(ca.id) FROM `{$tbl_category}` AS ca\n \n LEFT JOIN `{$tbl_rel_course_category}` AS rcc\n ON ca.id = rcc.categoryId\n \n LEFT JOIN `{$tbl_course}` AS co\n ON rcc.courseId = co.cours_id\n \n LEFT JOIN `{$tbl_rel_course_user}` AS rcu\n ON rcu.code_cours = co.code\n \n WHERE rcc.rootCourse = 1\n AND rcu.user_id = " . (int) $user['user_id'] . "\n \n GROUP BY ca.id";
$userList[$userKey]['qty_category'] = (int) claro_sql_query_get_single_value($sql);
}
}
$userGrid = array();
if (is_array($userList)) {
foreach ($userList as $userKey => $user) {
$userGrid[$userKey]['user_id'] = $user['user_id'];
$userGrid[$userKey]['name'] = $user['name'];
$userGrid[$userKey]['firstname'] = $user['firstname'];
$userEmailLabel = null;
if (!empty($_SESSION['admin_user_search'])) {
$bold_search = str_replace('*', '.*', $_SESSION['admin_user_search']);
$userGrid[$userKey]['name'] = preg_replace('/(' . $bold_search . ')/i', '<b>\\1</b>', $user['name']);
$userGrid[$userKey]['firstname'] = preg_replace('/(' . $bold_search . ')/i', '<b>\\1</b>', $user['firstname']);
$userEmailLabel = preg_replace('/(' . $bold_search . ')/i', '<b>\\1</b>', $user['email']);
}
protected function renderContent()
{
$html = '';
$html .= '<ul>' . "\n";
//-- Total access
$sql = "SELECT count(*)\n FROM `" . $this->tbl_course_tracking_event . "`\n WHERE `type` = 'course_access'";
$count = claro_sql_query_get_single_value($sql);
$html .= '<li>' . get_lang('Total') . ' : ' . $count . '</li>' . "\n";
// last 31 days
$sql = "SELECT count(*)\n FROM `" . $this->tbl_course_tracking_event . "`\n WHERE `type` = 'course_access'\n AND `date` > DATE_ADD(CURDATE(), INTERVAL -31 DAY)";
$count = claro_sql_query_get_single_value($sql);
if ($count) {
$html .= '<li><a href="#" class="showDetailsLast31Days" id="last31DaysDetails">' . get_lang('Last 31 days') . ' : ' . $count . '</a></li>' . "\n";
$html .= '<div id="trackDetailsLast31Days" class="hidden">' . "\n" . '<blockquote>';
//-- students connected last week
$sql = "SELECT U.`user_id`, U.`nom` AS `lastname`, U.`prenom` AS `firstname`, MAX(CTE.`date`) AS `last_access_date`\n\t FROM `" . $this->tbl_user . "` AS U, `" . $this->tbl_rel_course_user . "` AS CU\n\t LEFT JOIN `" . $this->tbl_course_tracking_event . "` AS `CTE`\n\t ON `CTE`.`user_id` = CU.`user_id`\n\t WHERE U.`user_id` = CU.`user_id`\n\t AND CU.`code_cours` = '" . claro_sql_escape(claro_get_current_course_id()) . "'\n\t GROUP BY U.`user_id`\n\t HAVING `last_access_date` > ( NOW() - INTERVAL 31 DAY )\n\t ORDER BY `lastname`, `firstname`";
$html .= get_lang('Students connected since last month:');
$results = claro_sql_query_fetch_all($sql);
$html .= '<ul>' . "\n";
foreach ($results as $result) {
$html .= '<li>' . '<a href="../user/userInfo.php?uInfo=' . $result['user_id'] . '">' . $result['firstname'] . ' ' . $result['lastname'] . '</a> ';
if (is_null($result['last_access_date'])) {
$html .= '( <b>' . get_lang('Never connected') . '</b> )';
} else {
$html .= '( ' . get_lang('Last access') . ' : ' . $result['last_access_date'] . ' )';
}
$html .= '</li>' . "\n";
}
$html .= '</ul>' . "\n" . '</blockquote>' . "\n" . '</div>';
} else {
$html .= '<li>' . get_lang('Last 31 days') . ' : ' . $count . '</li>' . "\n";
}
// last week
$sql = "SELECT count(*)\n FROM `" . $this->tbl_course_tracking_event . "`\n WHERE `type` = 'course_access'\n AND `date` > ( NOW() - INTERVAL 8 DAY )";
$count = claro_sql_query_get_single_value($sql);
$html .= '<li><a href="#" class="showDetailsLastWeek" id="lastWeekDetails">' . get_lang('Last week') . ' : ' . $count . '</a></li>' . "\n";
$html .= '<div id="trackDetailsLastWeek" class="hidden">' . "\n" . '<blockquote>';
//-- students connected last week
$sql = "SELECT U.`user_id`, U.`nom` AS `lastname`, U.`prenom` AS `firstname`, MAX(CTE.`date`) AS `last_access_date`\n FROM `" . $this->tbl_user . "` AS U, `" . $this->tbl_rel_course_user . "` AS CU\n LEFT JOIN `" . $this->tbl_course_tracking_event . "` AS `CTE`\n ON `CTE`.`user_id` = CU.`user_id`\n WHERE U.`user_id` = CU.`user_id`\n AND CU.`code_cours` = '" . claro_sql_escape(claro_get_current_course_id()) . "'\n GROUP BY U.`user_id`\n HAVING `last_access_date` > ( NOW() - INTERVAL 8 DAY )\n ORDER BY `lastname`, `firstname`";
$html .= get_lang('Students connected since last week:');
$results = claro_sql_query_fetch_all($sql);
if (!empty($results) && is_array($results)) {
$html .= '<ul>' . "\n";
foreach ($results as $result) {
$html .= '<li>' . '<a href="../user/userInfo.php?uInfo=' . $result['user_id'] . '">' . $result['firstname'] . ' ' . $result['lastname'] . '</a> ';
if (is_null($result['last_access_date'])) {
$html .= '( <b>' . get_lang('Never connected') . '</b> )';
} else {
$html .= '( ' . get_lang('Last access') . ' : ' . $result['last_access_date'] . ' )';
}
$html .= '</li>' . "\n";
}
$html .= '</ul>' . "\n";
} else {
$html .= ' <small>' . get_lang('No result') . '</small><br />' . "\n";
}
$html .= '</blockquote>' . "\n" . '</div>';
// today
$sql = "SELECT count(*)\n FROM `" . $this->tbl_course_tracking_event . "`\n WHERE `type` = 'course_access'\n AND `date` > CURDATE()";
$count = claro_sql_query_get_single_value($sql);
$html .= '<li><a href="#" class="showDetailsToday" id="todayDetails">' . get_lang('Today') . ' : ' . $count . '</a></li>' . "\n";
//-- students connected today
$html .= '<div id="trackDetailsToday" class="hidden">' . "\n" . '<blockquote>';
$sql = "SELECT U.`user_id`, U.`nom` AS `lastname`, U.`prenom` AS `firstname`, MAX(CTE.`date`) AS `last_access_date`\n FROM `" . $this->tbl_user . "` AS U, `" . $this->tbl_rel_course_user . "` AS CU\n LEFT JOIN `" . $this->tbl_course_tracking_event . "` AS `CTE`\n ON `CTE`.`user_id` = CU.`user_id`\n WHERE U.`user_id` = CU.`user_id`\n AND CU.`code_cours` = '" . claro_sql_escape(claro_get_current_course_id()) . "'\n GROUP BY U.`user_id`\n HAVING `last_access_date` > CURDATE()\n ORDER BY `lastname`, `firstname`";
$html .= get_lang('Students connected today:');
$results = claro_sql_query_fetch_all($sql);
if (!empty($results) && is_array($results)) {
$html .= '<ul>' . "\n";
foreach ($results as $result) {
$html .= '<li>' . '<a href="../user/userInfo.php?uInfo=' . $result['user_id'] . '">' . $result['firstname'] . ' ' . $result['lastname'] . '</a> ';
if (is_null($result['last_access_date'])) {
$html .= '( <b>' . get_lang('Never connected') . '</b> )';
} else {
$html .= '( ' . get_lang('Last access') . ' : ' . $result['last_access_date'] . ' )';
}
$html .= '</li>' . "\n";
}
$html .= '</ul>' . "\n";
} else {
$html .= ' <small>' . get_lang('No result') . '</small><br />' . "\n";
}
$html .= '</blockquote>' . "\n" . '</div>';
//-- students not connected for more than 1/2 month
$sql = "SELECT U.`user_id`, U.`nom` AS `lastname`, U.`prenom` AS `firstname`, MAX(CTE.`date`) AS `last_access_date`\n FROM `" . $this->tbl_user . "` AS U, `" . $this->tbl_rel_course_user . "` AS CU\n LEFT JOIN `" . $this->tbl_course_tracking_event . "` AS `CTE`\n ON `CTE`.`user_id` = CU.`user_id`\n WHERE U.`user_id` = CU.`user_id`\n AND CU.`code_cours` = '" . claro_sql_escape(claro_get_current_course_id()) . "'\n GROUP BY U.`user_id`\n HAVING `last_access_date` IS NULL\n OR `last_access_date` < ( NOW() - INTERVAL 15 DAY )\n ORDER BY `lastname`, `firstname`";
$results = claro_sql_query_fetch_all($sql);
if (!empty($results) && is_array($results)) {
$html .= '<li><a href="#" id="noTrack" class="showNoTrackDetails">' . get_lang('Not recently connected students :');
$html .= sizeof($results) . '</a>';
$html .= '<div id="noTrackDetails" class="hidden">' . "\n" . '<blockquote>';
$html .= '<ul>' . "\n";
foreach ($results as $result) {
$html .= '<li>' . '<a href="../user/userInfo.php?uInfo=' . $result['user_id'] . '">' . $result['firstname'] . ' ' . $result['lastname'] . '</a> ';
if (is_null($result['last_access_date'])) {
$html .= '( <b>' . get_lang('Never connected') . '</b> )';
} else {
$html .= '( ' . get_lang('Last access') . ' : ' . $result['last_access_date'] . ' )';
}
$html .= '</li>' . "\n";
}
$html .= '</ul>' . "\n" . '</blockquote>';
} else {
$html .= '<li>' . get_lang('Not recently connected students :');
$html .= ' <small>' . get_lang('No result') . '</small><br />' . "\n";
}
$html .= '</li>' . "\n";
$html .= '<li><a href="course_access_details.php">' . get_lang('Traffic Details') . '</a></li>' . '</ul>' . "\n";
return $html;
}
$sql = "SELECT `access`, count( * ) AS `nbr`\n FROM `" . $tbl_course . "`\n WHERE `access` IS NOT NULL\n GROUP BY `access`";
$content .= buildTab2Col($sql, get_lang('Number of courses by access'));
//-- number of courses by registration
$sql = "SELECT `registration`, count( * ) AS `nbr`\n FROM `" . $tbl_course . "`\n WHERE `registration` IS NOT NULL\n GROUP BY `registration`";
$content .= buildTab2Col($sql, get_lang('Number of courses by enrollment'));
//-- number of courses by visibility
$sql = "SELECT `visibility`, count( * ) AS `nbr`\n FROM `" . $tbl_course . "`\n WHERE `visibility` IS NOT NULL\n GROUP BY `visibility`";
$content .= buildTab2Col($sql, get_lang('Number of courses by visibility'));
$footer = '';
$html .= renderStatBlock($header, $content, $footer);
//-- USERS
$header = get_lang('Users');
$content = '';
//-- total number of users
$sql = "SELECT count(*)\n FROM `" . $tbl_user . "`";
$count = claro_sql_query_get_single_value($sql);
$content .= ' ' . get_lang('Number of users') . ' : ' . $count . '<br />' . "\n";
//-- number of users by course
$sql = "SELECT CONCAT(C.`administrativeNumber`, ' - ', C.`intitule`) , count( CU.user_id ) as `nb`\n FROM `" . $tbl_course . "` C, `" . $tbl_rel_course_user . "` CU\n WHERE CU.`code_cours` = C.`code`\n AND `code` IS NOT NULL\n GROUP BY C.`code`\n ORDER BY nb DESC";
$content .= buildTab2Col($sql, get_lang('Number of users by course'));
//-- number of users by status
$sql = "SELECT `isCourseCreator`, count( `user_id` ) AS `nbr`\n FROM `" . $tbl_user . "`\n WHERE `isCourseCreator` IS NOT NULL\n GROUP BY `isCourseCreator`";
$content .= buildTab2Col($sql, get_lang('Number of users by status'));
$footer = '';
$html .= renderStatBlock($header, $content, $footer);
/*
* Access to tools
* // due to the moving of access tables in course DB this part of the code exec (nbCourser+1) queries
* // this can create heavy overload on servers ... should be reconsidered
*
*/
} elseif (isset($_REQUEST['editMainUserInfo'])) {
$userIdViewed = (int) $_REQUEST['editMainUserInfo'];
$displayMode = "viewMainInfoEdit";
} elseif ($cmd == 'exUpdateCourseUserProperties') {
$userIdViewed = $_REQUEST['submitMainUserInfo'];
// Set variable for course manager or student status
if (!empty($_REQUEST['profileId']) && $userIdViewed != claro_get_current_user_id()) {
$userProperties['profileId'] = $_REQUEST['profileId'];
}
// Set variable for tutor setting
if (isset($_REQUEST['isTutor'])) {
// check first the user isn't registered to a group yet
$tbl_crs_names = claro_sql_get_course_tbl();
$tbl_group_rel_team_user = $tbl_crs_names['group_rel_team_user'];
$sql = "SELECT COUNT(user)\n FROM `" . $tbl_group_rel_team_user . "`\n WHERE user = "******"viewContentList";
}
}
/**
* Display the tree of classes
*
* @author Guillaume Lederer
* @param list of all the classes informations of the platform
* @param list of the classes that must be visible
* @return
*
* @see
*
*/
function display_tree_class_in_user($class_list, $course_code, $parent_class = null, $deep = 0)
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_course = $tbl_mdb_names['course'];
$html_form = '';
// Get the course id with cours code
$sql = "SELECT `C`.`cours_id`\n FROM `" . $tbl_course . "` as C\n WHERE `code` = '" . $course_code . "'";
claro_sql_query_get_single_value($sql);
foreach ($class_list as $cur_class) {
if ($parent_class == $cur_class['class_parent_id']) {
// Set space characters to add in name display
$blankspace = ' ';
for ($i = 0; $i < $deep; $i++) {
$blankspace .= ' ';
}
// See if current class to display has children
$has_children = FALSE;
foreach ($class_list as $search_parent) {
if ($cur_class['id'] == $search_parent['class_parent_id']) {
$has_children = TRUE;
break;
}
}
// Set link to open or close current class
if ($has_children) {
if (isset($_SESSION['class_add_visible_class'][$cur_class['id']]) && $_SESSION['class_add_visible_class'][$cur_class['id']] == "open") {
$open_close_link = '<a href="' . $_SERVER['PHP_SELF'] . '?cmd=exClose&class_id=' . $cur_class['id'] . '">' . "\n" . '<img src="' . get_icon_url('collapse') . '" alt="" />' . "\n" . '</a>' . "\n";
} else {
$open_close_link = '<a href="' . $_SERVER['PHP_SELF'] . '?cmd=exOpen&class_id=' . $cur_class['id'] . '">' . "\n" . '<img src="' . get_icon_url('expand') . '" alt="" />' . "\n" . '</a>' . "\n";
}
} else {
$open_close_link = ' ° ';
}
// Display current class
$qty_user = get_class_user_number($cur_class['id']);
// Need some optimisation here ...
$html_form .= '<tr>' . "\n" . '<td>' . "\n" . $blankspace . $open_close_link . " " . $cur_class['name'] . '</td>' . "\n" . '<td align="center">' . "\n" . $qty_user . ' ' . get_lang('UsersMin') . '</td>' . "\n" . '<td align="center">' . "\n";
if (empty($cur_class['course_id'])) {
$html_form .= '<a href="' . $_SERVER['PHP_SELF'] . '?cmd=exEnrol&class_id=' . $cur_class['id'] . '"' . ' onclick="return confirmation_enrol(\'' . clean_str_for_javascript($cur_class['name']) . '\');">' . '<img src="' . get_icon_url('enroll') . '" alt="' . get_lang('Enrol to course') . '" />' . "\n" . '</a>' . "\n";
} else {
$html_form .= '<a href="' . $_SERVER['PHP_SELF'] . '?cmd=exUnenrol&class_id=' . $cur_class['id'] . '"' . ' onclick="return confirmation_unenrol(\'' . clean_str_for_javascript($cur_class['name']) . '\');">' . '<img src="' . get_icon_url('unenroll') . '" alt="' . get_lang('Unenrol from course') . '" />' . "\n" . '</a>' . "\n";
}
$html_form .= '</td>' . "\n" . '</tr>' . "\n";
// RECURSIVE CALL TO DISPLAY CHILDREN
if (isset($_SESSION['class_add_visible_class'][$cur_class['id']]) && $_SESSION['class_add_visible_class'][$cur_class['id']] == 'open') {
$html_form .= display_tree_class_in_user($class_list, $course_code, $cur_class['id'], $deep + 1);
}
}
}
return $html_form;
}
// determine the default order of this Learning path
$sql = "SELECT MAX(`rank`)\n FROM `" . $TABLELEARNPATHMODULE . "`";
$orderMax = claro_sql_query_get_single_value($sql);
$order = $orderMax + 1;
// finally : insert in learning path
$sql = "INSERT INTO `" . $TABLELEARNPATHMODULE . "`\n (`learnPath_id`, `module_id`, `specificComment`, `rank`, `lock`)\n VALUES ('" . (int) $_SESSION['path_id'] . "', '" . (int) $moduleId . "','" . claro_sql_escape(get_block('blockDefaultModuleAddedComment')) . "', " . $order . ",'OPEN')";
claro_sql_query($sql);
$msgList['info'][] = get_lang("%moduleName has been added as module", array('%moduleName' => $exercise['title'])) . '<br />' . "\n";
} else {
// check if this is this LP that used this exercise as a module
$sql = "SELECT COUNT(*)\n FROM `" . $TABLELEARNPATHMODULE . "` AS LPM,\n `" . $TABLEMODULE . "` AS M,\n `" . $TABLEASSET . "` AS A\n WHERE M.`module_id` = LPM.`module_id`\n AND M.`startAsset_id` = A.`asset_id`\n AND A.`path` = " . (int) $exercise['id'] . "\n AND LPM.`learnPath_id` = " . (int) $_SESSION['path_id'];
$num = claro_sql_query_get_single_value($sql);
if ($num == 0) {
// determine the default order of this Learning path
$sql = "SELECT MAX(`rank`)\n FROM `" . $TABLELEARNPATHMODULE . "`";
$orderMax = claro_sql_query_get_single_value($sql);
$order = $orderMax + 1;
// finally : insert in learning path
$sql = "INSERT INTO `" . $TABLELEARNPATHMODULE . "`\n (`learnPath_id`, `module_id`, `specificComment`, `rank`, `lock`)\n VALUES (" . (int) $_SESSION['path_id'] . ", " . (int) $existingModule['module_id'] . ",'" . claro_sql_escape(get_block('blockDefaultModuleAddedComment')) . "', " . $order . ", 'OPEN')";
$query = claro_sql_query($sql);
$msgList['info'][] = get_lang("%moduleName has been added as module", array('%moduleName' => $exercise['title'])) . '<br />' . "\n";
} else {
$msgList['info'][] = get_lang("%moduleName is already used as a module in this learning path", array('%moduleName' => $exercise['title'])) . '<br />' . "\n";
}
}
}
}
//end while
//STEP ONE : display form to add an exercise
$out .= claro_html_msg_list($msgList);
$out .= display_my_exercises($dialogBox);
/**
* delete a list of user id from a list of courses
* @param array $userIdList list of user id to remove
* @param array|string $courseCodeList array of course codes or one single course code
* @param bool $force forece the deletion
* @param bool $delTrackData delete tracking data
* @param bool $unregister_by_class unregister by class
* @return int number of deleted users
*/
function user_remove_userlist_from_course($userIdList, $courseCodeList = array(), $force = false, $delTrackData = false, $unregister_by_class = false)
{
$tbl = claro_sql_get_main_tbl();
$userDeletedCount = 0;
if (!is_array($courseCodeList)) {
$courseCodeList = array($courseCodeList);
}
foreach ($userIdList as $userIdToProcess) {
$userId = is_numeric($userIdToProcess) ? $userIdToProcess : $userIdToProcess['user_id'];
if (!$force && $userId == $GLOBALS['_uid']) {
// PREVIOUSLY CHECK THE USER IS NOT COURSE ADMIN OF THESE COURSES
$sql = "SELECT COUNT(user_id)\n FROM `" . $tbl['rel_course_user'] . "`\n WHERE user_id = " . (int) $userId . "\n AND isCourseManager = 1\n AND code_cours IN ('" . implode("', '", array_map('claro_sql_escape', $courseCodeList)) . "') ";
if (claro_sql_query_get_single_value($sql) > 0) {
Claroline::getInstance()->log('DELETE_USER_FAILED', array('USER' => $userId, 'failure' => 'course_manager_cannot_unsubscribe_himself'));
continue;
}
}
$sql = "SELECT code_cours , count_user_enrol, count_class_enrol\n FROM `" . $tbl['rel_course_user'] . "`\n WHERE `code_cours` IN ('" . implode("', '", array_map('claro_sql_escape', $courseCodeList)) . "')\n AND `user_id` = " . $userId;
$userEnrolCourseList = claro_sql_query_fetch_all($sql);
foreach ($userEnrolCourseList as $thisUserEnrolCourse) {
$thisCourseCode = $thisUserEnrolCourse['code_cours'];
$count_user_enrol = $thisUserEnrolCourse['count_user_enrol'];
$count_class_enrol = $thisUserEnrolCourse['count_class_enrol'];
if ($count_user_enrol + $count_class_enrol <= 1) {
// remove user from course
if (user_remove_from_group($userId, $thisCourseCode) == false) {
return false;
}
$dbNameGlued = claro_get_course_db_name_glued($thisCourseCode);
$tbl_cdb_names = claro_sql_get_course_tbl($dbNameGlued);
$tbl_bb_notify = $tbl_cdb_names['bb_rel_topic_userstonotify'];
$tbl_group_team = $tbl_cdb_names['group_team'];
$tbl_userinfo_content = $tbl_cdb_names['userinfo_content'];
$sqlList = array();
$toolCLFRM = get_module_data('CLFRM');
if (is_tool_activated_in_course($toolCLFRM['id'], $thisUserEnrolCourse['code_cours'])) {
$sqlList = array("DELETE FROM `" . $tbl_bb_notify . "` WHERE user_id = " . (int) $userId);
}
array_push($sqlList, "DELETE FROM `" . $tbl_userinfo_content . "` WHERE user_id = " . (int) $userId, "UPDATE `" . $tbl_group_team . "` SET `tutor` = NULL WHERE `tutor`='" . (int) $userId . "'");
foreach ($sqlList as $thisSql) {
if (claro_sql_query($thisSql) == false) {
continue;
}
}
if ($delTrackData) {
if (user_delete_course_tracking_data($userId, $thisCourseCode) == false) {
continue;
}
}
$sql = "DELETE FROM `" . $tbl['rel_course_user'] . "`\n WHERE user_id = " . (int) $userId . "\n AND code_cours = '" . claro_sql_escape($thisCourseCode) . "'";
if (claro_sql_query($sql) == false) {
continue;
}
} else {
// decrement the count of registration by the user or class
if (!$unregister_by_class) {
$count_user_enrol--;
} else {
$count_class_enrol--;
}
// update enrol count in table rel_course_user
$sql = "UPDATE `" . $tbl['rel_course_user'] . "`\n SET `count_user_enrol` = '" . $count_user_enrol . "',\n `count_class_enrol` = '" . $count_class_enrol . "'\n WHERE `user_id` = " . (int) $userId . "\n AND `code_cours` = '" . claro_sql_escape($thisCourseCode) . "'";
if (!claro_sql_query($sql)) {
continue;
}
}
}
$userDeletedCount++;
}
return $userDeletedCount;
}
$_SESSION['exeStartTime'] = $now;
}
$currentTime = 0;
} else {
$currentTime = $now - $_SESSION['exeStartTime'];
}
if ($startExercise) {
$exeStartTime = $_SESSION['exeStartTime'];
} else {
$exeStartTime = 0;
}
//-- exercise properties
if (claro_is_user_authenticated()) {
// count number of attempts of the user
$sql = "SELECT count(`result`) AS `tryQty`\n FROM `" . $tbl_qwz_tracking . "`\n WHERE `user_id` = '" . (int) claro_get_current_user_id() . "'\n AND `exo_id` = " . (int) $exId . "\n GROUP BY `user_id`";
$userAttemptCount = claro_sql_query_get_single_value($sql);
if ($userAttemptCount) {
$userAttemptCount++;
} else {
$userAttemptCount = 1;
}
// first try
} else {
$userAttemptCount = 1;
}
$exerciseIsAvailable = true;
if (!$is_allowedToEdit) {
// do the checks only if user has no edit right
// check if exercise can be displayed
if ($exercise->getStartDate() > $now || !is_null($exercise->getEndDate()) && $exercise->getEndDate() < $now) {
// not yet available, no more available
/**
* get minimum position already used in the course_tool of the def_rank value
* @return : the minimum value
*/
function get_course_tool_min_rank()
{
$tbl = claro_sql_get_main_tbl();
$sql = "SELECT MIN(def_rank) as minrank FROM `" . $tbl['tool'] . "`";
return claro_sql_query_get_single_value($sql);
}
/**
* @param int $user_id
* @return string
*/
public function getLastActionBeforeLoginDate($user_id)
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_rel_course_user = $tbl_mdb_names['rel_course_user'];
$_user = claro_get_current_user_data();
//if we already knwo in session what is the last action date, just retrieve it from the session
if (isset($_SESSION['last_action'])) {
return $_SESSION['last_action'];
}
//otherwise we must find it in the access statistics information of the database
$last_login_date = date("Y-m-d H:i:s", $_user['lastLogin']);
// 1 - retriev course list in which the user is subscribed
$sql = "SELECT `code_cours`\n FROM `" . $tbl_rel_course_user . "` AS CU\n WHERE CU.`user_id`='" . (int) $user_id . "'";
$courses = claro_sql_query_fetch_all($sql);
// 2 - retrieve each max(date) of acces for each date
$last_action_date = "0000-00-00 00:00:00";
//set default last action date
//look for last action date in every tracking table of courses where the user is registered
foreach ($courses as $course) {
$tbl_c_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course['code_cours']));
$tbl_course_tracking_event = $tbl_c_names['tracking_event'];
$sqlMaxDate = "SELECT MAX(`date`) AS MAXDATE\n FROM `" . $tbl_course_tracking_event . "` AS STAT,\n `" . $tbl_rel_course_user . "` AS CU\n WHERE `type` = 'course_access'\n AND STAT.`user_id` = " . (int) $user_id . "\n AND STAT.`user_id` = CU.`user_id`\n AND CU.`user_id` = " . (int) $user_id;
$maxDate = claro_sql_query_get_single_value($sqlMaxDate);
if ($maxDate && strtotime($maxDate) > strtotime($last_action_date)) {
$last_action_date = $maxDate;
}
}
// return (and save in session) the latest action :
// last login or latest action in the tracking tables
if (strtotime($last_action_date) > strtotime($last_login_date)) {
$_SESSION['last_action'] = $last_action_date;
return $last_action_date;
} else {
$_SESSION['last_action'] = $last_login_date;
return $last_login_date;
}
}
$cmd = isset($_REQUEST['cmd']) ? $_REQUEST['cmd'] : '';
switch ($cmd) {
// REQUEST EDIT
case "rqEdit":
if (isset($_SESSION['path_id'])) {
$dialogBox->form("\n\n" . '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">' . "\n" . '<fieldset>' . "\n" . claro_form_relay_context() . '<h4>' . get_lang('Edit this learning path') . '</h4>' . "\n" . '<dl>' . "\n" . '<dt><label for="newName">' . get_lang('Title') . '</label></dt>' . "\n" . '<dd>' . "\n" . '<input type="text" name="newName" id="newName" size="50" maxlength="255" value="' . claro_htmlspecialchars(claro_utf8_decode($LPDetails['name'], get_conf('charset'))) . '" />' . "\n" . '</dd>' . "\n" . '<dt><label for="newComment">' . get_lang('Comment') . '</label></dt>' . "\n" . '<dd>' . "\n" . claro_html_textarea_editor('newComment', $LPDetails['comment'], 15, 55) . '</dd>' . "\n" . '</dl>' . "\n" . '</fieldset>' . "\n" . '<input type="hidden" name="cmd" value="exEdit" />' . "\n" . '<input type="submit" value="' . get_lang('Ok') . '" /> ' . "\n" . claro_html_button(Url::Contextualize($_SERVER['PHP_SELF']), get_lang('Cancel')) . '</form>' . "\n");
} else {
$dialogBox->error(get_lang('Wrong operation'));
}
break;
// EXECUTE EDIT
// EXECUTE EDIT
case "exEdit":
// Name must be unique
$sql = "SELECT COUNT(`name`)\n FROM `" . $TABLELEARNPATH . "`\n WHERE `name` = '" . claro_sql_escape($_POST['newName']) . "'\n AND !(`learnPath_id` = " . (int) $_SESSION['path_id'] . ")";
$num = claro_sql_query_get_single_value($sql);
if ($num == 0) {
$sql = "UPDATE `" . $TABLELEARNPATH . "`\n SET `name` = '" . claro_sql_escape($_POST['newName']) . "',\n `comment` = '" . claro_sql_escape($_POST['newComment']) . "'\n WHERE `learnPath_id` = " . (int) $_SESSION['path_id'];
if (claro_sql_query($sql)) {
$dialogBox->success(get_lang('Learning path updated'));
}
} else {
$dialogBox->error(get_lang('Error : Name already exists in the learning path or in the module pool') . '<br />');
}
break;
// MODULE DELETE
// MODULE DELETE
case "delModule":
//--- BUILD ARBORESCENCE OF MODULES IN LEARNING PATH
$sql = "SELECT M.*, LPM.*\n FROM `" . $TABLEMODULE . "` AS M, `" . $TABLELEARNPATHMODULE . "` AS LPM\n WHERE M.`module_id` = LPM.`module_id`\n AND LPM.`learnPath_id` = " . (int) $_SESSION['path_id'] . "\n ORDER BY LPM.`rank` ASC";
$result = claro_sql_query($sql);
private function movePortlet($label, $direction)
{
switch ($direction) {
case self::UP:
//1-find value of current module rank in the dock
$sql = "SELECT `rank`\n FROM `" . $this->tblDesktopPortlet . "`\n WHERE `label`='" . claro_sql_escape($label) . "'";
$result = claro_sql_query_get_single_value($sql);
//2-move down above module
$sql = "UPDATE `" . $this->tblDesktopPortlet . "`\n SET `rank` = `rank`+1\n WHERE `label` != '" . claro_sql_escape($label) . "'\n AND `rank` = " . (int) $result['rank'] . " -1 ";
claro_sql_query($sql);
//3-move up current module
$sql = "UPDATE `" . $this->tblDesktopPortlet . "`\n SET `rank` = `rank`-1\n WHERE `label` = '" . claro_sql_escape($label) . "'\n AND `rank` > 1";
claro_sql_query($sql);
break;
case self::DOWN:
//1-find value of current module rank in the dock
$sql = "SELECT `rank`\n FROM `" . $this->tblDesktopPortlet . "`\n WHERE `label`='" . claro_sql_escape($label) . "'";
$result = claro_sql_query_get_single_value($sql);
//this second query is to avoid a page refreshment wrong update
$sqlmax = "SELECT MAX(`rank`) AS `max_rank`\n FROM `" . $this->tblDesktopPortlet . "`";
$resultmax = claro_sql_query_get_single_value($sqlmax);
if ($resultmax['max_rank'] == $result['rank']) {
break;
}
//2-move up above module
$sql = "UPDATE `" . $this->tblDesktopPortlet . "`\n SET `rank` = `rank` - 1\n WHERE `label` != '" . claro_sql_escape($label) . "'\n AND `rank` = " . (int) $result['rank'] . " + 1\n AND `rank` > 1";
claro_sql_query($sql);
//3-move down current module
$sql = "UPDATE `" . $this->tblDesktopPortlet . "`\n SET `rank` = `rank` + 1\n WHERE `label`='" . claro_sql_escape($label) . "'";
claro_sql_query($sql);
break;
}
}
private function getUserTotalForumTopics()
{
$sql = "SELECT count(`topic_title`)\n FROM `" . $this->tbl_bb_topics . "`\n WHERE `topic_poster` = '" . (int) $this->userId . "'";
$value = claro_sql_query_get_single_value($sql);
if (is_numeric($value)) {
return $value;
} else {
return 0;
}
}
/**
* Add a new tool in tool_list table of a course
*
* @param string claro_label
* @param string access level to tools if null get the default value from main table
* @param string course db name glued
*
* @return boolean
*/
function add_tool_in_course_tool_list($claro_label, $access = null, $courseDbNameGlu = null)
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_cdb_names = claro_sql_get_course_tbl($courseDbNameGlu);
$tbl_course_tool = $tbl_mdb_names['tool'];
$tbl_tool_list = $tbl_cdb_names['tool'];
// get rank of tool in course table
$sql = "SELECT MAX(`rank`) as `max_rank`\n FROM `" . $tbl_tool_list . "`";
$rank = claro_sql_query_get_single_value($sql);
$rank++;
// get id of tool on the platform and default access
$sql = "SELECT `id`, `def_access`\n FROM `" . $tbl_course_tool . "`\n WHERE `claro_label` = '" . claro_sql_escape($claro_label) . "'";
$result = upgrade_sql_query($sql);
if (mysql_num_rows($result)) {
$row = mysql_fetch_array($result);
// if $access emtpy get default access
if (empty($access)) {
$access = $row['access'];
}
// add tool in course_tool table
$sql = "INSERT INTO `" . $tbl_tool_list . "`\n (`tool_id`,`rank`,`access`)\n VALUES\n ('" . $row['id'] . "','" . $rank . "','" . $access . "')";
$result = upgrade_sql_query($sql);
return mysql_insert_id();
} else {
return FALSE;
}
}
if (!empty($classList)) {
$groupBy = empty($_GET['groupBy']) ? '' : $_GET['groupBy'];
$groupByCmdList = array();
$groupByCmdList['class'] = array('name' => get_lang('Display grouped by class'), 'url' => $_SERVER['PHP_SELF'] . '?path_id=' . (int) $path_id . '&groupBy=class');
if (!empty($groupBy)) {
unset($groupByCmdList[$groupBy]);
$groupByCmdList[] = array('name' => get_lang('Display ungrouped'), 'url' => $_SERVER['PHP_SELF'] . '?path_id=' . (int) $path_id);
}
$cmdList = array_values($groupByCmdList);
} else {
$cmdList = array();
$groupBy = '';
}
// get infos about the learningPath
$sql = "SELECT `name`\n FROM `" . $TABLELEARNPATH . "`\n WHERE `learnPath_id` = " . (int) $path_id;
$learnPathName = claro_sql_query_get_single_value($sql);
if ($learnPathName) {
// display title
$titleTab['mainTitle'] = $nameTools;
$titleTab['subTitle'] = claro_htmlspecialchars($learnPathName);
$out .= claro_html_tool_title($titleTab, null, $cmdList);
// display a list of user and their respective progress
$sql = "SELECT U.`nom`, U.`prenom`, U.`user_id`\n FROM `" . $TABLEUSER . "` AS U,\n `" . $TABLECOURSUSER . "` AS CU\n WHERE U.`user_id`= CU.`user_id`\n AND CU.`code_cours` = '" . claro_sql_escape(claro_get_current_course_id()) . "'";
$usersList = claro_sql_query_fetch_all($sql);
switch ($groupBy) {
case 'class':
$out .= getLearnPathDetailByClass($path_id, $usersList);
break;
default:
$out .= getLearnPathDetailTable($path_id, $usersList);
break;
/**
* Return if course status is enable
*/
function claro_is_course_enable()
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_course = $tbl_mdb_names['course'];
$courseId = claro_get_current_course_id();
$curdate = claro_mktime();
if (claro_is_course_manager()) {
$sql = " SELECT c.`code`\n FROM `" . $tbl_course . "` c\n WHERE (c.`status` != 'trash')\n AND c.`code` = '" . $courseId . "';";
} else {
$sql = " SELECT c.`code`\n FROM `" . $tbl_course . "` c\n WHERE (c.`status` = 'enable'\n OR (c.`status` = 'date'\n AND (UNIX_TIMESTAMP(`creationDate`) <= '" . $curdate . "'\n OR `creationDate` IS NULL OR UNIX_TIMESTAMP(`creationDate`) = 0\n )\n AND ('" . $curdate . "' <= UNIX_TIMESTAMP(`expirationDate`)\n OR `expirationDate` IS NULL\n )\n )\n )\n AND c.`code` = '" . $courseId . "';";
}
$result = claro_sql_query_get_single_value($sql);
if (isset($result) or claro_is_platform_admin()) {
$return = true;
} else {
$return = false;
}
return $return;
}
/**
* Check if action already exists
*/
public function exists()
{
$sql = " SELECT count(*)\n FROM `" . $this->tbl['action'] . "`\n WHERE name ='" . claro_sql_escape($this->name) . "' AND\n type ='" . claro_sql_escape($this->type) . "' AND\n tool_id = " . (int) $this->toolId;
if (claro_sql_query_get_single_value($sql) == 0) {
return false;
} else {
return true;
}
}
/**
* Upgrade assignment tool to 1.6
*/
function assignment_upgrade_to_16($course_code)
{
global $currentCourseVersion, $currentcoursePathSys, $currentCourseCreationDate;
global $_uid;
$versionRequiredToProceed = '/^1.5/';
$tool = 'CLWRK';
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_rel_course_user = $tbl_mdb_names['rel_course_user'];
$tbl_course_tool = $tbl_mdb_names['tool'];
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
/**
* STEP 1 Create new work table
*/
$sql_step1[] = "CREATE TABLE IF NOT EXISTS `" . $currentCourseDbNameGlu . "wrk_assignment` (\n `id` int(11) NOT NULL auto_increment,\n `title` varchar(200) NOT NULL default '',\n `description` text NOT NULL,\n `visibility` enum('VISIBLE','INVISIBLE') NOT NULL default 'VISIBLE',\n `def_submission_visibility` enum('VISIBLE','INVISIBLE') NOT NULL default 'VISIBLE',\n `assignment_type` enum('INDIVIDUAL','GROUP') NOT NULL default 'INDIVIDUAL',\n `authorized_content` enum('TEXT','FILE','TEXTFILE') NOT NULL default 'FILE',\n `allow_late_upload` enum('YES','NO') NOT NULL default 'YES',\n `start_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `end_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `prefill_text` text NOT NULL,\n `prefill_doc_path` varchar(200) NOT NULL default '',\n `prefill_submit` enum('ENDDATE','AFTERPOST') NOT NULL default 'ENDDATE',\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM";
/*
$sql_step1[] = "UPDATE `".$currentCourseDbNameGlu."wrk_assignment`
SET
`end_date` = '".date('Y-m-d H:i:00', mktime( date('H'),date('i'),0,date('m'), date('d'), date('Y')+1 ) )."'
WHERE `end_date` = '0000-00-00 00:00:00'
";
*/
$sql_step1[] = "CREATE TABLE IF NOT EXISTS `" . $currentCourseDbNameGlu . "wrk_submission` (\n `id` int(11) NOT NULL auto_increment,\n `assignment_id` int(11) default NULL,\n `parent_id` int(11) default NULL,\n `user_id` int(11) default NULL ,\n `group_id` int(11) default NULL,\n `title` varchar(200) NOT NULL default '',\n `visibility` enum('VISIBLE','INVISIBLE') default 'VISIBLE',\n `creation_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `last_edit_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `authors` varchar(200) NOT NULL default '',\n `submitted_text` text NOT NULL,\n `submitted_doc_path` varchar(200) NOT NULL default '',\n `private_feedback` text,\n `original_id` int(11) default NULL,\n `score` smallint(3) default NULL,\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM";
if (!upgrade_apply_sql($sql_step1)) {
return $step;
}
$step = set_upgrade_status($tool, 2, $course_code);
case 2:
/**
* STEP 2 : Create a new assignment
*/
// get work intro in tool
$sql_work_intro = "SELECT ti.texte_intro\n FROM `" . $currentCourseDbNameGlu . "tool_list` tl,\n `" . $currentCourseDbNameGlu . "tool_intro` ti,\n `" . $tbl_course_tool . "` ct\n WHERE ti.id = tl.id\n AND tl.tool_id = ct.id\n AND ct.claro_label = 'CLWRK___'";
$work_intro = claro_sql_query_get_single_value($sql_work_intro);
if ($work_intro === FALSE) {
$work_intro = '';
}
$sql_step2[] = "INSERT INTO `" . $currentCourseDbNameGlu . "wrk_assignment`\n SET `id` = 1,\n `title` = 'Assignments',\n `description`= '" . mysql_real_escape_string($work_intro) . "',\n `visibility` = 'VISIBLE',\n `def_submission_visibility` = 'VISIBLE',\n `assignment_type` = 'INDIVIDUAL',\n `authorized_content` = 'FILE',\n `allow_late_upload` = 'NO',\n `start_date` = '" . $currentCourseCreationDate . "',\n `end_date` = DATE_ADD(NOW(),INTERVAL 1 YEAR),\n `prefill_text` = '',\n `prefill_doc_path` = '',\n `prefill_submit` = 'ENDDATE' ";
if (!upgrade_apply_sql($sql_step2)) {
return $step;
}
$step = set_upgrade_status($tool, 3, $course_code);
case 3:
/**
* STEP 3 : Add old works as submissions of new assignment
*/
// get course manager of the course
$sql_get_id_of_one_teacher = "SELECT `user_id` `uid` " . " FROM `" . $tbl_rel_course_user . "` " . " WHERE `code_cours` = '" . $course_code . "' LIMIT 1";
$teacher = claro_sql_query_fetch_all($sql_get_id_of_one_teacher);
$teacher_uid = $teacher[0]['uid'];
// if no course manager, you are enrolled in as
if (!is_numeric($teacher_uid)) {
$teacher_uid = $_uid;
$sql_set_teacher = "INSERT INTO `" . $tbl_rel_course_user . "`\n SET `user_id` = '" . $teacher_uid . "'\n , `code_cours` = '" . $course_code . "'\n , `role` = 'Course missing manager';";
if (!claro_sql_query($sql_set_teacher)) {
return $step;
}
log_message('Warning : Course ' . $course_code . ' has no teacher, you are enrolled in as course manager.');
}
// add old work in submission of course manager
$sql_step3[] = "INSERT IGNORE INTO `" . $currentCourseDbNameGlu . "wrk_submission`\n (assignment_id,user_id,title,visibility,authors,submitted_text,submitted_doc_path)\n SELECT 1, '" . $teacher_uid . "', titre, IF(accepted,'VISIBLE','INVISIBLE'), auteurs, description, url\n FROM `" . $currentCourseDbNameGlu . "assignment_doc`";
if (!upgrade_apply_sql($sql_step3)) {
return $step;
}
$step = set_upgrade_status($tool, 4, $course_code);
case 4:
/**
* STEP 4 : Update document path of submissions
*/
$sql_step4[] = "UPDATE `" . $currentCourseDbNameGlu . "wrk_submission`\n SET submitted_doc_path = REPLACE (`submitted_doc_path` ,'work/','')";
if (!upgrade_apply_sql($sql_step4)) {
return $step;
}
$step = set_upgrade_status($tool, 5, $course_code);
case 5:
/**
* STEP 5 : Create new folder to store assig_1 and move old old work documents in it
*/
// create new folder
$work_dirname = $currentcoursePathSys . 'work/';
$assignment_dirname = $work_dirname . 'assig_1/';
if (!is_dir($assignment_dirname)) {
if (!@mkdir($assignment_dirname, CLARO_FILE_PERMISSIONS)) {
log_message('Error: Cannot create ' . $assignment_dirname);
return $step;
}
}
// move assignment from work to work/assig_1
if (is_dir($work_dirname)) {
if ($handle = opendir($work_dirname)) {
while (FALSE !== ($file = readdir($handle))) {
if (is_dir($work_dirname . $file)) {
continue;
}
if (@rename($work_dirname . $file, $assignment_dirname . $file) === FALSE) {
log_message('Error: Cannot rename ' . $work_dirname . $file . ' to ' . $assignment_dirname . $file);
return $step;
}
}
closedir($handle);
}
}
$step = set_upgrade_status($tool, 6, $course_code);
case 6:
/**
* STEP 6 Drop deprecated assignment_doc
*/
// $sql_step6[] = "DROP TABLE IF EXISTS `".$currentCourseDbNameGlu."assignment_doc`";
// if ( ! upgrade_apply_sql($sql_step6) ) return $step;
$step = set_upgrade_status($tool, 0, $course_code);
default:
return $step;
}
// end switch
}
return false;
}
/**
* save assignment to DB
*
* @author Sebastien Piraux <*****@*****.**>
* @return mixed false or id of the record
*/
public function save()
{
// TODO method to validate data
if ($this->id == -1) {
// insert
$sql = "INSERT INTO `" . $this->tblAssignment . "`\n SET `title` = '" . claro_sql_escape($this->title) . "',\n `description` = '" . claro_sql_escape($this->description) . "',\n `visibility` = '" . claro_sql_escape($this->visibility) . "',\n `def_submission_visibility` = '" . claro_sql_escape($this->defaultSubmissionVisibility) . "',\n `assignment_type` = '" . claro_sql_escape($this->assignmentType) . "',\n `authorized_content` = '" . claro_sql_escape($this->submissionType) . "',\n `allow_late_upload` = '" . claro_sql_escape($this->allowLateUpload) . "',\n `start_date` = FROM_UNIXTIME('" . claro_sql_escape($this->startDate) . "'),\n `end_date` = FROM_UNIXTIME('" . claro_sql_escape($this->endDate) . "'),\n `prefill_text` = '" . claro_sql_escape($this->autoFeedbackText) . "',\n `prefill_doc_path` = '" . claro_sql_escape($this->autoFeedbackFilename) . "',\n `prefill_submit` = '" . claro_sql_escape($this->autoFeedbackSubmitMethod) . "'";
// execute the creation query and get id of inserted assignment
$insertedId = claro_sql_query_insert_id($sql);
if ($insertedId) {
$this->id = (int) $insertedId;
$this->buildDirPaths();
// create the assignment directory if query was successfull and dir not already exists
if (!is_dir($this->assigDirSys)) {
claro_mkdir($this->assigDirSys, CLARO_FILE_PERMISSIONS, true);
}
return $this->id;
} else {
return false;
}
} else {
if ($this->applyVisibilityChangeToOldSubmissions) {
// get current assignment defaultSubmissionVisibility
$sqlGetOldData = "SELECT `def_submission_visibility`\n FROM `" . $this->tblAssignment . "`\n WHERE `id` = '" . $this->id . "'";
$prevDefaultSubmissionVisibility = claro_sql_query_get_single_value($sqlGetOldData);
// change visibility of all works only if defaultSubmissionVisibility has changed
if ($this->_forceVisibilityChange || $this->defaultSubmissionVisibility != $prevDefaultSubmissionVisibility) {
$this->updateAllSubmissionsVisibility($this->defaultSubmissionVisibility, true);
}
}
// update, main query
$sql = "UPDATE `" . $this->tblAssignment . "`\n SET `title` = '" . claro_sql_escape($this->title) . "',\n `description` = '" . claro_sql_escape($this->description) . "',\n `visibility` = '" . claro_sql_escape($this->visibility) . "',\n `def_submission_visibility` = '" . claro_sql_escape($this->defaultSubmissionVisibility) . "',\n `assignment_type` = '" . claro_sql_escape($this->assignmentType) . "',\n `authorized_content` = '" . claro_sql_escape($this->submissionType) . "',\n `allow_late_upload` = '" . claro_sql_escape($this->allowLateUpload) . "',\n `start_date` = FROM_UNIXTIME('" . claro_sql_escape($this->startDate) . "'),\n `end_date` = FROM_UNIXTIME('" . claro_sql_escape($this->endDate) . "'),\n `prefill_text` = '" . claro_sql_escape($this->autoFeedbackText) . "',\n `prefill_doc_path` = '" . claro_sql_escape($this->autoFeedbackFilename) . "',\n `prefill_submit` = '" . claro_sql_escape($this->autoFeedbackSubmitMethod) . "'\n WHERE `id` = '" . $this->id . "'";
// execute and return main query
if (claro_sql_query($sql)) {
return $this->id;
} else {
return false;
}
}
}
/**
* Add a new announcement in the given or current course.
*
* @param string $title title of the new item
* @param string $content content of the new item
* @param date $visibleFrom
* @param date $visibleUntil
* @param bool visibility
* @param date $time publication date of the item def:now
* @param string $course_id sysCode of the course (leaveblank for current course)
* @return id of the new item
* @since 1.7
* @todo convert to param date timestamp
*/
function announcement_add_item($title = '', $content = '', $visible_from = null, $visible_until = null, $visibility = null, $time = null, $course_id = null)
{
$tbl = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
$sqlTime = is_null($time) ? $sqlTime = "NOW()" : "FROM_UNIXTIME('" . (int) $time . "')";
// Determine the position of the new announcement
$sql = "SELECT (MAX(ordre) + 1) AS nextRank\n FROM `" . $tbl['announcement'] . "`";
$nextRank = claro_sql_query_get_single_value($sql);
$visibility = $visibility == 1 ? "SHOW" : "HIDE";
$visible_from = !is_null($visible_from) ? "'" . claro_sql_escape($visible_from) . "'" : "NULL";
$visible_until = !is_null($visible_until) ? "'" . claro_sql_escape($visible_until) . "'" : "NULL";
// Insert announcement
$sql = "INSERT INTO `" . $tbl['announcement'] . "`\n SET title = '" . claro_sql_escape(trim($title)) . "',\n contenu = '" . claro_sql_escape(trim($content)) . "',\n temps = " . $sqlTime . ",\n visibleFrom = " . $visible_from . ",\n visibleUntil = " . $visible_until . ",\n ordre = '" . (int) $nextRank . "',\n visibility = '" . $visibility . "'";
return claro_sql_query_insert_id($sql);
}
/**
* Count groups where a user is ennrolled in a given course
* @param integer $user_id
* @param integer (optional) course_id
* @return integer Count of groups where a given user is ennrolled in a given (o current) course
* @author Christophe Gesche <*****@*****.**>
*
*/
function group_count_group_of_a_user($user_id, $course_id = null)
{
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
$sql = "SELECT COUNT(`team`)\n FROM `" . $tbl_cdb_names['group_rel_team_user'] . "`\n WHERE user = " . (int) $user_id;
return claro_sql_query_get_single_value($sql);
}
/**
* Trig the execution of the SQL queries
*
* @access private
*/
function _execute_pager_queries()
{
$preparedQuery = $this->_get_prepared_query($this->sql, $this->offset, $this->step, $this->sortKeyList);
$this->resultList = claro_sql_query_fetch_all($preparedQuery);
// The query below has to be executed immediateley after the previous one.
// Otherwise other potential queries could impair the reliability
// of mySQL FOUND_ROWS() function.
$this->totalItemCount = claro_sql_query_get_single_value('SELECT FOUND_ROWS()');
}
/**
*
* @param $node
* @return
* @author Christophe Gesché <*****@*****.**>
*
*/
function get_node_children_count_course($node)
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_course = $tbl_mdb_names['course'];
$sql = "SELECT COUNT( `courses`.`cours_id` ) `nbCourse`\n FROM `" . $tbl_course . "` `courses`\n WHERE `courses`.`faculte` = '" . claro_sql_escape($node) . "'";
return claro_sql_query_get_single_value($sql);
}
