function buildRequestModules()
{
global $TABLELEARNPATHMODULE;
global $TABLEMODULE;
global $TABLEASSET;
$firstSql = "SELECT LPM.`module_id`\n FROM `" . $TABLELEARNPATHMODULE . "` AS LPM\n WHERE LPM.`learnPath_id` = " . (int) $_SESSION['path_id'];
$firstResult = claro_sql_query($firstSql);
// 2) We build the request to get the modules we need
$sql = "SELECT M.*, A.`path`\n FROM `" . $TABLEMODULE . "` AS M\n LEFT JOIN `" . $TABLEASSET . "` AS A ON M.`startAsset_id` = A.`asset_id`\n WHERE M.`contentType` != \"SCORM\"\n AND M.`contentType` != \"LABEL\"";
while ($list = mysql_fetch_array($firstResult)) {
$sql .= " AND M.`module_id` != " . (int) $list['module_id'];
}
//$sql .= " AND M.`contentType` != \"".CTSCORM_."\"";
/** To find which module must displayed we can also proceed with only one query.
* But this implies to use some features of MySQL not available in the version 3.23, so we use
* two differents queries to get the right list.
* Here is how to proceed with only one
$query = "SELECT *
FROM `".$TABLEMODULE."` AS M
WHERE NOT EXISTS(SELECT * FROM `".$TABLELEARNPATHMODULE."` AS TLPM
WHERE TLPM.`module_id` = M.`module_id`)";
*/
return $sql;
}
/**
* CLAROLINE
*
* @version $Revision: 14314 $
* @copyright (c) 2001-2011, Universite catholique de Louvain (UCL)
* @license http://www.gnu.org/copyleft/gpl.html (GPL) GENERAL PUBLIC LICENSE
* @author Piraux Sebastien <*****@*****.**>
* @author Lederer Guillaume <*****@*****.**>
* @package CLLNP
* @since 1.8
*/
function lp_display_scorm($TABLELEARNPATHMODULE)
{
$out = '';
// change raw if value is a number between 0 and 100
if (isset($_POST['newRaw']) && is_num($_POST['newRaw']) && $_POST['newRaw'] <= 100 && $_POST['newRaw'] >= 0) {
$sql = "UPDATE `" . $TABLELEARNPATHMODULE . "`\n SET `raw_to_pass` = " . (int) $_POST['newRaw'] . "\n WHERE `module_id` = " . (int) $_SESSION['module_id'] . "\n AND `learnPath_id` = " . (int) $_SESSION['path_id'];
claro_sql_query($sql);
$dialogBoxContent = get_lang('Minimum raw to pass has been changed');
}
$out .= '<hr noshade="noshade" size="1" />';
//####################################################################################\\
//############################### DIALOG BOX SECTION #################################\\
//####################################################################################\\
if (!empty($dialogBoxContent)) {
$dialogBox = new DialogBox();
$dialogBox->success($dialogBoxContent);
$out .= $dialogBox->render();
}
// form to change raw needed to pass the exercise
$sql = "SELECT `lock`, `raw_to_pass`\n FROM `" . $TABLELEARNPATHMODULE . "` AS LPM\n WHERE LPM.`module_id` = " . (int) $_SESSION['module_id'] . "\n AND LPM.`learnPath_id` = " . (int) $_SESSION['path_id'];
$learningPath_module = claro_sql_query_fetch_all($sql);
if (isset($learningPath_module[0]['lock']) && $learningPath_module[0]['lock'] == 'CLOSE' && isset($learningPath_module[0]['raw_to_pass'])) {
$out .= "\n\n" . '<form method="post" action="' . $_SERVER['PHP_SELF'] . '">' . "\n" . '<label for="newRaw">' . get_lang('Change minimum raw mark to pass this module (percentage) : ') . '</label>' . "\n" . '<input type="text" value="' . claro_htmlspecialchars($learningPath_module[0]['raw_to_pass']) . '" name="newRaw" id="newRaw" size="3" maxlength="3" /> % ' . "\n" . '<input type="submit" value="' . get_lang('Ok') . '" />' . "\n" . '</form>' . "\n\n";
}
return $out;
}
/**
* Add a message to the log. The message will be associated with the current
* course_code, user_id, tool_id, date and IP address of the client
* @param string $type
* @param string $data
* @return boolean
*/
public function log($type, $data)
{
$cid = claro_get_current_course_id();
$tid = claro_get_current_tool_id();
$uid = claro_get_current_user_id();
$date = claro_date("Y-m-d H:i:s");
$ip = !empty($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : null;
$data = serialize($data);
$sql = "INSERT INTO `" . $this->tbl_log . "`\n SET `course_code` = " . (is_null($cid) ? "NULL" : "'" . claro_sql_escape($cid) . "'") . ",\n `tool_id` = " . (is_null($tid) ? "NULL" : "'" . claro_sql_escape($tid) . "'") . ",\n `user_id` = " . (is_null($uid) ? "NULL" : "'" . claro_sql_escape($uid) . "'") . ",\n `ip` = " . (is_null($ip) ? "NULL" : "'" . claro_sql_escape($ip) . "'") . ",\n `date` = '" . $date . "',\n `type` = '" . claro_sql_escape($type) . "',\n `data` = '" . claro_sql_escape($data) . "'";
return claro_sql_query($sql);
}
/**
* Delete action
*/
public function delete()
{
// Delete from rel_profile_action
$sql = "DELETE FROM `" . $this->tbl['rel_profile_action'] . "`\n WHERE action_id = " . (int) $this->id;
claro_sql_query($sql);
// Delete from action
$sql = "DELETE FROM `" . $this->tbl['action'] . "`\n WHERE id = " . (int) $this->id;
claro_sql_query($sql);
$this->id = -1;
return true;
}
public function mergeCourseUsers($uidToRemove, $uidToKeep, $courseId)
{
$moduleCourseTbl = get_module_course_tbl(array('wrk_submission'), $courseId);
$sql = "UPDATE `{$moduleCourseTbl['wrk_submission']}`\n SET user_id = " . (int) $uidToKeep . "\n WHERE user_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot update wrk_submission from -{$uidToRemove} to +{$uidToKeep} in {$courseId}");
return false;
} else {
return true;
}
}
/**
* CLAROLINE mySQL query wrapper. It allows to send multiple query at once to SQl server in a single string
*
* @author Guillaume Lederer <*****@*****.**>,
* @param string $sqlQueries - the string containing sql queries to apply
* @param bool $breakOnFailure - stop query execution if one query failed (default true)
* @return true on success, false on failure
* @deprecated since Claroline 1.9, use Claroline::getDatabase() and new classes
* in database/extra.lib.php instead
*/
function claro_sql_multi_query($sqlQueries, $breakOnFailure = true)
{
$queryArray = PMA_splitSqlFile($sqlQueries);
foreach ($queryArray as $theQuery) {
if (!$theQuery['empty']) {
if (true === $breakOnFailure && false === claro_sql_query($theQuery['query'])) {
return false;
}
}
}
return true;
}
public function deleteForUser($userId, $date = null)
{
if (!is_null($date) && !empty($date)) {
$dateCondition = " AND `T`.`date` < FROM_UNIXTIME('" . (int) $date . "')";
}
$sql = "DELETE\n FROM `" . $this->tbl_course_tracking_event . "`\n WHERE `type` = 'download'\n AND `user_id` = " . (int) $userId . $dateCondition;
if (claro_sql_query($sql)) {
return true;
} else {
return false;
}
}
public function mergeCourseUsers($uidToRemove, $uidToKeep, $courseId)
{
$tblList[] = 'lp_module';
$tblList[] = 'lp_learnPath';
$tblList[] = 'lp_rel_learnPath_module';
$tblList[] = 'lp_asset';
$tblList[] = 'lp_user_module_progress';
$moduleCourseTbl = get_module_course_tbl($tblList, $courseId);
// Update lp_user_module_progress
$sql = "UPDATE `{$moduleCourseTbl['lp_user_module_progress']}`\n SET user_id = " . (int) $uidToKeep . "\n WHERE user_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot update lp_user_module_progress from -{$uidToRemove} to +{$uidToKeep} in {$courseId}");
return false;
}
return true;
}
public static function setForUser($_uid)
{
$tbl = claro_sql_get_main_tbl();
$ssoCookieExpireTime = time() + get_conf('ssoCookiePeriodValidity', 3600);
$ssoCookieValue = md5(time() . rand(100, 1000000));
$sql = "UPDATE `{$tbl['sso']}`\n" . "SET cookie = '" . $ssoCookieValue . "',\n" . "rec_time = NOW()\n" . "WHERE user_id = " . (int) $_uid;
$affectedRowCount = claro_sql_query_affected_rows($sql);
if ($affectedRowCount < 1) {
$sql = "INSERT INTO `{$tbl['sso']}`\n" . "SET cookie = '" . $ssoCookieValue . "',\n" . "rec_time = NOW(),\n" . "user_id = " . (int) $_uid;
claro_sql_query($sql);
}
return setcookie(get_conf('ssoCookieName', 'clarolineSsoCookie'), $ssoCookieValue, $ssoCookieExpireTime, get_conf('ssoCookiePath', '/'), get_conf('ssoCookieDomain', 'sso.claroline.net'));
// Note. $ssoCookieName, $ssoCookieValussoCookieExpireTime,
// $soCookiePath and $ssoCookieDomain are coming from
// claroline/inc/conf/auth.conf.php
}
public function mergeCourseUsers($uidToRemove, $uidToKeep, $courseId)
{
$moduleCourseTbl = get_module_course_tbl(array('wiki_pages', 'wiki_pages_content'), $courseId);
// Update wiki_pages
$sql = "UPDATE `{$moduleCourseTbl['wiki_pages']}`\n SET owner_id = " . (int) $uidToKeep . "\n WHERE owner_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot update wiki_pages from -{$uidToRemove} to +{$uidToKeep} in {$courseId}");
return false;
}
// Update wiki_pages_content
$sql = "UPDATE `{$moduleCourseTbl['wiki_pages_content']}`\n SET editor_id = " . (int) $uidToKeep . "\n WHERE editor_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot update wiki_pages_content from -{$uidToRemove} to +{$uidToKeep} in {$courseId}");
return false;
}
return true;
}
/**
* Check if a username is already used
*
* @author Daniel Streiff <*****@*****.**>
* @param string $username
* @return boolean
*/
function shibbolethUsernameExists($username)
{
global $tbl_user;
global $shibbolethAuthSource;
// unique for all authSources
$sql = 'SELECT count(*)
FROM `' . $tbl_user . '`
WHERE
username = "******"';
$result = claro_sql_query($sql);
$row = mysql_fetch_array($result);
if ($row[0] == 0) {
return false;
} else {
return true;
}
}
/**
* CLAROLINE
*
* @version $Revision: 14314 $
* @copyright (c) 2001-2011, Universite catholique de Louvain (UCL)
* @license http://www.gnu.org/copyleft/gpl.html (GPL) GENERAL PUBLIC LICENSE
* @author Piraux Sebastien <*****@*****.**>
* @author Lederer Guillaume <*****@*****.**>
* @package CLLNP
* @since 1.8
*/
function lp_display_exercise($cmd, $TABLELEARNPATHMODULE, $TABLEMODULE, $TABLEASSET, $tbl_quiz_exercise)
{
$out = '';
if (isset($cmd) && ($cmd = "raw")) {
// change raw if value is a number between 0 and 100
if (isset($_POST['newRaw']) && is_num($_POST['newRaw']) && $_POST['newRaw'] <= 100 && $_POST['newRaw'] >= 0) {
$sql = "UPDATE `" . $TABLELEARNPATHMODULE . "`\n SET `raw_to_pass` = " . (int) $_POST['newRaw'] . "\n WHERE `module_id` = " . (int) $_SESSION['module_id'] . "\n AND `learnPath_id` = " . (int) $_SESSION['path_id'];
claro_sql_query($sql);
$dialogBoxContent = get_lang('Minimum raw to pass has been changed');
}
}
$out .= '<hr noshade="noshade" size="1" />';
//####################################################################################\\
//############################### DIALOG BOX SECTION #################################\\
//####################################################################################\\
if (!empty($dialogBoxContent)) {
$dialogBox = new DialogBox();
$dialogBox->success($dialogBoxContent);
$out .= $dialogBox->render();
}
// form to change raw needed to pass the exercise
$sql = "SELECT `lock`, `raw_to_pass`\n FROM `" . $TABLELEARNPATHMODULE . "` AS LPM\n WHERE LPM.`module_id` = " . (int) $_SESSION['module_id'] . "\n AND LPM.`learnPath_id` = " . (int) $_SESSION['path_id'];
$learningPath_module = claro_sql_query_get_single_row($sql);
// if this module blocks the user if he doesn't complete
if (isset($learningPath_module['lock']) && $learningPath_module['lock'] == 'CLOSE' && isset($learningPath_module['raw_to_pass'])) {
$out .= '<form method="post" action="' . $_SERVER['PHP_SELF'] . '">' . "\n" . claro_form_relay_context() . '<label for="newRaw">' . get_lang('Change minimum raw mark to pass this module (percentage) :') . ' </label>' . "\n" . '<input type="text" value="' . claro_htmlspecialchars($learningPath_module['raw_to_pass']) . '" name="newRaw" id="newRaw" size="3" maxlength="3" /> % ' . "\n" . '<input type="hidden" name="cmd" value="raw" />' . "\n" . '<input type="submit" value="' . get_lang('Ok') . '" />' . "\n" . '</form>' . "\n\n";
}
// display current exercise info and change comment link
$sql = "SELECT `E`.`id` AS `exerciseId`, `M`.`name`\n FROM `" . $TABLEMODULE . "` AS `M`,\n `" . $TABLEASSET . "` AS `A`,\n `" . $tbl_quiz_exercise . "` AS `E`\n WHERE `A`.`module_id` = M.`module_id`\n AND `M`.`module_id` = " . (int) $_SESSION['module_id'] . "\n AND `E`.`id` = `A`.`path`";
$module = claro_sql_query_get_single_row($sql);
if ($module) {
$out .= "\n\n" . '<h4>' . get_lang('Exercise in module') . ' :</h4>' . "\n" . '<p>' . "\n" . claro_htmlspecialchars($module['name']) . '<a href="../exercise/admin/edit_exercise.php?exId=' . $module['exerciseId'] . '">' . '<img src="' . get_icon_url('edit') . '" alt="' . get_lang('Modify') . '" />' . '</a>' . "\n" . '</p>' . "\n";
}
// else sql error, do nothing except in debug mode, where claro_sql_query_fetch_all will show the error
return $out;
}
public function deleteForUser($userId, $date = null)
{
if (!is_null($date) && !empty($date)) {
$dateCondition = " AND `date` < FROM_UNIXTIME('" . (int) $date . "') ";
}
// get data to delete from exercise tracking table
$sql = "SELECT `id`\n FROM `" . $this->tbl_qwz_tracking . "`\n WHERE `user_id` = " . (int) $userId . $dateCondition;
$exeList = claro_sql_query_fetch_all_cols($sql);
if (is_array($exeList['id']) && !empty($exeList['id'])) {
// delete
$sql = "DELETE FROM `" . $this->tbl_qwz_tracking . "`\n WHERE `user_id` = " . (int) $userId . $dateCondition;
claro_sql_query($sql);
// get data to delete
$sql = "SELECT `id` FROM `" . $this->tbl_qwz_tracking_questions . "` WHERE `exercise_track_id` IN ('" . implode("', '", $exeList['id']) . "')";
$detailList = claro_sql_query_fetch_all_cols($sql);
if (is_array($detailList['id']) && !empty($detailList['id'])) {
$sql = "DELETE FROM `" . $this->tbl_qwz_tracking_questions . "` WHERE `exercise_track_id` IN ('" . implode("', '", $exeList['id']) . "')";
claro_sql_query($sql);
$sql = "DELETE FROM `" . $this->tbl_qwz_tracking_answers . "` WHERE details_id IN ('" . implode("', '", $detailList['id']) . "')";
claro_sql_query($sql);
}
}
return true;
}
} else {
$dialogBox->form(get_lang('Confirm your subscription as tutor of the group "<b>%group_name</b>"', array('%group_name' => claro_get_current_group_data('name'))) . "\n" . '<form action="' . claro_htmlspecialchars($_SERVER['PHP_SELF']) . '" method="post">' . "\n" . claro_form_relay_context() . '<input type="hidden" name="tutorRegistration" value="1" />' . "\n" . '<input type="hidden" name="doReg" value="1" />' . "\n" . '<br />' . "\n" . '<input type="submit" value="' . get_lang("Ok") . '" />' . "\n" . claro_html_button(claro_htmlspecialchars(Url::Contextualize($_SERVER['PHP_SELF'])), get_lang("Cancel")) . "\n" . '</form>' . "\n");
}
}
}
if (isset($_REQUEST['tutorRegDone'])) {
$dialogBox->success(get_lang("You are now the tutor of this group."));
}
if (isset($_REQUEST['tutorUnregistration'])) {
//RECHECK if subscribe is aivailable
if ($isTutorUnregAllowed) {
if (isset($_REQUEST['doUnreg'])) {
//RECHECK if subscribe is aivailable
if ($isTutorUnregAllowed) {
$sql = "UPDATE `" . $tbl_group_team . "`\n SET \n `tutor` = NULL\n WHERE\n `id` = " . (int) claro_get_current_group_id();
if (claro_sql_query($sql)) {
// REFRESH THE SCRIPT TO COMPUTE NEW PERMISSIONS ON THE BASSIS OF THIS CHANGE
claro_redirect(dirname($_SERVER['PHP_SELF']) . '/group.php?gidReset=1&tutorUnregDone=1');
exit;
}
}
} else {
$dialogBox->form(get_lang('Confirm your unsubscription as tutor from the group "<b>%group_name</b>"', array('%group_name' => claro_get_current_group_data('name'))) . "\n" . '<form action="' . claro_htmlspecialchars($_SERVER['PHP_SELF']) . '" method="post">' . "\n" . claro_form_relay_context() . '<input type="hidden" name="tutorUnregistration" value="1" />' . "\n" . '<input type="hidden" name="doUnreg" value="1" />' . "\n" . '<br />' . "\n" . '<input type="submit" value="' . get_lang("Ok") . '" />' . "\n" . claro_html_button(claro_htmlspecialchars(Url::Contextualize($_SERVER['PHP_SELF'])), get_lang("Cancel")) . "\n" . '</form>' . "\n");
}
}
}
/********************************
* GROUP INFORMATIONS RETRIVIAL
********************************/
/*----------------------------------------------------------------------------
GET GROUP MEMBER LIST
// Shibboleth attributes available, check if there is already an account with this uniqueId
require '../../../inc/claro_init_global.inc.php';
// Library
require_once 'shibboleth.lib.php';
// uncomment to fake attributes
// setShibbolethAttributes();
// the unique id has to contain something
if (isset($_SERVER[$shibbolethUniqueIdAttr])) {
if (!$_SERVER[$shibbolethUniqueIdAttr] == '') {
if (isset($_uid)) {
// check if the uniqueId is already used
$sql = 'SELECT user_id
FROM `' . $tbl_user . '`
WHERE
`' . $shibbolethUidTbl . '` = "' . $_SERVER[$shibbolethUniqueIdAttr] . '"';
$result = claro_sql_query($sql);
if (mysql_num_rows($result) > 0) {
// uniqueId already in use
claro_die("<center>WARNING ! UNABLE TO CHANGE AUTHSOURCE. YOU ALREADY HAVE A USERACCOUNT.</center>");
} else {
// change user's authSource
$sqlPrepareList = array();
$sqlPrepareList[] = 'nom = "' . addslashes(utf8_decode($_SERVER[$shibbolethData['nom']])) . '"';
$sqlPrepareList[] = 'prenom = "' . addslashes(utf8_decode($_SERVER[$shibbolethData['prenom']])) . '"';
// Use first email only
$shibbolethEmail = explode($shibbolethEmailSep, $_SERVER[$shibbolethData['email']]);
if ($shibbolethEmail[0] == '') {
$shibbolethEmail[0] = $shibbolethDefaultEmail;
}
$sqlPrepareList[] = 'email = "' . addslashes($shibbolethEmail[0]) . '"';
$sqlPrepareList[] = 'authSource = "' . $shibbolethAuthSource . '"';
/**
* Displaces an entry (up or down).
*
* @param integer $entryId an valid id of announcement.
* @param string $cmd 'UP' or 'DOWN'
* @return true;
*
* @author Christophe Gesche <*****@*****.**>
*/
function move_entry($item_id, $cmd, $course_id = null)
{
$tbl = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
if ($cmd == 'DOWN') {
$thisAnnouncementId = $item_id;
$sortDirection = 'DESC';
} elseif ($cmd == 'UP') {
$thisAnnouncementId = $item_id;
$sortDirection = 'ASC';
} else {
return false;
}
if ($sortDirection) {
$sql = "SELECT id,\n ordre AS rank\n FROM `" . $tbl['announcement'] . "`\n ORDER BY `ordre` " . $sortDirection;
$result = claro_sql_query($sql);
$thisAnnouncementRankFound = false;
$thisAnnouncementRank = '';
while (list($announcementId, $announcementRank) = mysql_fetch_row($result)) {
// STEP 2 : FOUND THE NEXT ANNOUNCEMENT ID AND ORDER.
// COMMIT ORDER SWAP ON THE DB
if ($thisAnnouncementRankFound == true) {
$nextAnnouncementId = $announcementId;
$nextAnnouncementRank = $announcementRank;
$sql = "UPDATE `" . $tbl['announcement'] . "`\n SET ordre = '" . (int) $nextAnnouncementRank . "'\n WHERE id = '" . (int) $thisAnnouncementId . "'";
claro_sql_query($sql);
$sql = "UPDATE `" . $tbl['announcement'] . "`\n SET ordre = '" . $thisAnnouncementRank . "'\n WHERE id = '" . $nextAnnouncementId . "'";
claro_sql_query($sql);
return true;
}
// STEP 1 : FIND THE ORDER OF THE ANNOUNCEMENT
if ($announcementId == $thisAnnouncementId) {
$thisAnnouncementRank = $announcementRank;
$thisAnnouncementRankFound = true;
}
}
if (!$thisAnnouncementRankFound) {
return false;
}
}
}
/**
* delete link between a course and its associated categories.
*
* @author Antonin Bourguignon <*****@*****.**>
* @param int id of course
* @param array collection of categories
* (leave it empty to unlink ALL categories)
* @return boolean success
* @since 1.10
*/
function unlink_course_categories($courseId, $categories = array())
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_rel_course_category = $tbl_mdb_names['rel_course_category'];
// Include specified categories identifiers in the request
$categoriesIdsRestriction = "";
if (!empty($categories)) {
$categoriesIds = '';
for ($i = 0; $i < count($categories); $i++) {
$categoriesIds .= $categories[$i]->id;
// More elements to come ? Add a comma
if ($i < count($categories) - 1) {
$categoriesIds .= ", ";
}
}
$categoriesIdsRestriction = " AND categoryId IN (" . $categoriesIds . ")";
}
$sql = "DELETE FROM `" . $tbl_rel_course_category . "`\n WHERE courseId=" . (int) $courseId . $categoriesIdsRestriction;
return claro_sql_query($sql);
}
/**
* 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;
}
function check_if_db_exist($db_name, $db = null)
{
// I HATE THIS SOLUTION .
// It's would be better to have a SHOW DATABASE case insensitive
// IF SHOW DATABASE IS NOT AIVAILABLE, sql failed an function return false.
if (PHP_OS != 'WIN32' && PHP_OS != 'WINNT') {
$sql = "SHOW DATABASES LIKE '" . $db_name . "'";
} else {
$sql = "SHOW DATABASES LIKE '" . strtolower($db_name) . "'";
}
if ($db) {
$res = claro_sql_query($sql, $db);
} else {
$res = claro_sql_query($sql);
}
if (mysql_errno() == 0) {
$foundDbName = mysql_fetch_array($res, MYSQL_NUM);
} else {
$foundDbName = false;
}
return $foundDbName;
}
if (count($userList) > 0) {
$allowedAuthSources = AuthDriverManager::getDriversAllowingLostPassword();
foreach ($userList as $user) {
if (isset($allowedAuthSources[$user['authSource']])) {
$passwordFound = true;
if (get_conf('userPasswordCrypted', false)) {
/*
* If password are crypted, we can not send them as such.
* We have to generate new ones.
*/
$user['password'] = generate_passwd();
// UPDATE THE DB WITH THE NEW GENERATED PASSWORD
$sql = 'UPDATE `' . $tbl_user . '`
SET `password` = "' . claro_sql_escape(md5($user['password'])) . '"
WHERE `user_id` = "' . $user['uid'] . '"';
if (claro_sql_query($sql) === false) {
trigger_error('<p align="center">' . get_lang('Wrong operation') . '</p>', E_USER_ERROR);
}
}
// Build user account list for email
$userAccountList[] = array('firstname' => $user['firstName'], 'lastname' => $user['lastName'], 'username' => $user['loginName'], 'password' => $user['password']);
} else {
$extAuthPasswordCount++;
}
}
if ($passwordFound) {
/*
* Prepare the email message wich has to be send to the user
*/
// mail subject
$emailSubject = get_lang('Login request') . ' ' . get_conf('siteName');
require_once $includePath . '/lib/module/manage.lib.php';
// Define display
DEFINE('DISPLAY_WELCOME_PANEL', 1);
DEFINE('DISPLAY_RESULT_PANEL', 2);
/*=====================================================================
Main Section
=====================================================================*/
/**
* Create Upgrade Status table
*/
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_upgrade_status = $tbl_mdb_names['upgrade_status'];
$sql = "CREATE TABLE IF NOT EXISTS `" . $tbl_upgrade_status . "` (\n`id` INT NOT NULL auto_increment ,\n`cid` VARCHAR( 40 ) NOT NULL ,\n`claro_label` VARCHAR( 8 ) ,\n`status` TINYINT NOT NULL ,\nPRIMARY KEY ( `id` )\n)";
claro_sql_query($sql);
$sql = "ALTER IGNORE TABLE `" . $tbl_upgrade_status . "` CHANGE `claro_label` `claro_label` VARCHAR(50) ";
claro_sql_query($sql);
/**
* Initialise variables
*/
if (isset($_REQUEST['verbose'])) {
$verbose = true;
}
if (isset($_REQUEST['cmd'])) {
$cmd = $_REQUEST['cmd'];
} else {
$cmd = false;
}
$display = DISPLAY_WELCOME_PANEL;
/**
* Define display
*/
/**
* return subClass of a given class
*
* @param unknown_type $class_id
* @return unknown
*
* @since 1.8.0
*/
function getSubClasses($class_id)
{
$tbl = claro_sql_get_main_tbl();
$sub_classes_list = array();
$sql = "SELECT `id`\n FROM `" . $tbl['class'] . "`\n WHERE `class_parent_id`=" . (int) $class_id;
$query_result = claro_sql_query($sql);
while ($this_sub_class = mysql_fetch_array($query_result)) {
// add this subclass id to array
$sub_classes_list[] = $this_sub_class['id'];
// add children of this subclass id to array
$this_sub_classes_list = getSubClasses($this_sub_class['id']);
$sub_classes_list = array_merge($this_sub_classes_list, $sub_classes_list);
}
return $sub_classes_list;
}
/**
* clean the content of a bloc for information category
*
* @param integer $def_id
* @param integer $user_id
* @return boolean true if succeed, else bolean false
*/
function claro_user_info_cleanout_cat_content($user_id, $def_id, $course_id = NULL)
{
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
$tbl_userinfo_content = $tbl_cdb_names['userinfo_content'];
if (0 == (int) $user_id || 0 == (int) $def_id) {
return false;
}
$sql = "DELETE FROM `" . $tbl_userinfo_content . "`\n WHERE user_id = " . (int) $user_id . "\n AND def_id = " . (int) $def_id;
claro_sql_query($sql);
return true;
}
/**
* add a user in the table of recipient
*
* @param int $messageId message id
* @param int $userId user id (recipient id)
*/
protected function addRecipient($messageId, $userId)
{
$tableName = get_module_main_tbl(array('im_recipient'));
$sql = "INSERT INTO `" . $tableName['im_recipient'] . "` " . "(message_id, user_id, sent_to) \n" . "VALUES (" . (int) $messageId . ", " . (int) $userId . ", 'toAll')\n";
claro_sql_query($sql);
}
function get_db_size($tdb)
{
$db = mysql_connect(get_conf('dbHost'), get_conf('dbLogin'), get_conf('dbPass')) or die("Error connecting to MySQL Server!\n");
mysql_select_db($tdb, $db);
$sql_result = "SHOW TABLE STATUS FROM " . $tdb;
$result = claro_sql_query($sql_result);
mysql_close($db);
if ($result) {
$size = 0;
while ($data = mysql_fetch_array($result)) {
$size = $size + $data['Data_length'] + $data['Index_length'];
}
return $size;
} else {
return false;
}
}
function fill_course_properties($courseDbName)
{
$currentCourseDbNameGlu = get_conf('courseTablePrefix') . $courseDbName . get_conf('dbGlu');
$sql = "INSERT " . "INTO `{$currentCourseDbNameGlu}course_properties`(`name`, `value`, `category`)\n" . "VALUES\n" . "('self_registration' , '1', 'GROUP'),\n" . "('self_unregistration' , '0', 'GROUP'),\n" . "('nbGroupPerUser' , '1', 'GROUP'),\n" . "('private' , '1', 'GROUP')";
$groupToolList = get_group_tool_label_list();
foreach ($groupToolList as $thisGroupTool) {
$sql .= ",\n(" . "'" . claro_sql_escape($thisGroupTool['label']) . "', '1', 'GROUP'" . ")";
}
return claro_sql_query($sql);
}
}
} else {
$_gid = null;
$_group = null;
}
} else {
$_gid = !empty($_SESSION['_gid']) ? $_SESSION['_gid'] : null;
$_group = !empty($_SESSION['_group']) ? $_SESSION['_group'] : null;
}
/*---------------------------------------------------------------------------
Group / User relation initialisation
---------------------------------------------------------------------------*/
if ($uidReset || $cidReset || $gidReset) {
if ($_uid && $_cid && $_gid) {
$sql = "SELECT status,\n role\n FROM `" . $_course['dbNameGlu'] . "group_rel_team_user`\n WHERE `user` = '" . (int) $_uid . "'\n AND `team` = '" . (int) $gidReq . "'";
$result = claro_sql_query($sql) or die('WARNING !! Load user course_group status (DB QUERY) FAILED ! ' . __LINE__);
if (mysql_num_rows($result) > 0) {
$gpuData = mysql_fetch_array($result);
$_groupUser['status'] = $gpuData['status'];
$_groupUser['role'] = $gpuData['role'];
$is_groupMember = true;
} else {
$is_groupMember = false;
$_groupUser = null;
}
$is_groupTutor = $_group['tutorId'] == $_uid;
} else {
$is_groupMember = false;
$is_groupTutor = false;
$_groupUser = null;
}
/**
* delete answers from db
*
* @author Sebastien Piraux <*****@*****.**>
* @return boolean result of operation
*/
public function delete()
{
if ($this->id != -1) {
// delete question from all exercises
$sql = "DELETE FROM `" . $this->tblAnswer . "`\n WHERE `id` = " . (int) $this->id;
if (!claro_sql_query($sql)) {
return false;
}
$this->id = -1;
}
return true;
}
/**
* Clean status of a tool
*
* @param string course_code
*
* @return integer status value
*
* @since 1.7
*/
function clean_upgrade_status($course_code = null)
{
// get table name
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_upgrade_status = $tbl_mdb_names['upgrade_status'];
// course_code empty
if (is_null($course_code)) {
$course_code = '';
}
// delete all status for this course
$sql = " DELETE FROM `" . $tbl_upgrade_status . "`\n WHERE cid = '" . $course_code . "' ";
return claro_sql_query($sql);
}
/**
* insert or update course data
*
* @return boolean success
*/
public function save()
{
if (empty($this->courseId)) {
// Insert
$keys = define_course_keys($this->officialCode, '', get_conf('dbNamePrefix'));
$courseSysCode = trim($keys['currentCourseId']);
$courseDbName = trim($keys['currentCourseDbName']);
$courseDirectory = trim($keys['currentCourseRepository']);
if (empty($courseSysCode) || empty($courseDbName) || empty($courseDirectory)) {
throw new Exception("Error missing data for course {$this->officialCode}");
}
if (!$this->useExpirationDate) {
$this->expirationDate = 'NULL';
}
// Session courses are created without categories links:
// so we duplicate the source course's categories links
/*if ( !is_null($this->sourceCourseId) && !empty($this->sourceCourseId) )
{
$sourceCourse = new claroCourse();
$sourceCourse->load(claroCourse::getCodeFromId($this->sourceCourseId));
$this->categories = $sourceCourse->categories;
}*/
if (!is_null($this->sourceCourseId) && !empty($this->sourceCourseId)) {
$sourceCourse = new claroCourse();
$sourceCourse->load(claroCourse::getCodeFromId($this->sourceCourseId));
if ($sourceCourse->sourceCourseId) {
throw new Exception('You cannot create a course session from another course session');
}
}
if (prepare_course_repository($courseDirectory, $courseSysCode) && register_course($courseSysCode, $this->officialCode, $this->sourceCourseId, $courseDirectory, $courseDbName, $this->titular, $this->email, $this->categories, $this->title, $this->language, $GLOBALS['_uid'], $this->access, $this->registration, $this->registrationKey, $this->visibility, $this->departmentName, $this->extLinkUrl, $this->publicationDate, $this->expirationDate, $this->status, $this->userLimit) && install_course_database($courseDbName) && install_course_tools($courseDbName, $this->language, $courseDirectory)) {
$courseObj = new Claro_Course($courseSysCode);
$courseObj->load();
$courseRegistration = new Claro_CourseUserRegistration(AuthProfileManager::getUserAuthProfile($GLOBALS['_uid']), $courseObj, null, null);
$courseRegistration->ignoreRegistrationKeyCheck();
$courseRegistration->ignoreCategoryRegistrationCheck();
$courseRegistration->setCourseAdmin();
$courseRegistration->setCourseTutor();
$courseRegistration->forceSuperUser();
if ($courseRegistration->addUser()) {
// Set course id
$this->courseId = $courseSysCode;
// Notify event manager
$args['courseSysCode'] = $courseSysCode;
$args['courseDbName'] = $courseDbName;
$args['courseDirectory'] = $courseDirectory;
$args['courseCategory'] = $this->categories;
$GLOBALS['eventNotifier']->notifyEvent("course_created", $args);
return true;
} else {
$this->backlog->failure($courseRegistration->getErrorMessage());
return false;
}
} else {
$lastFailure = claro_failure::get_last_failure();
$this->backlog->failure('Error : ' . $lastFailure);
return false;
}
} else {
// Update
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_course = $tbl_mdb_names['course'];
$tbl_cdb_names = claro_sql_get_course_tbl();
$tbl_course_properties = $tbl_cdb_names['course_properties'];
if (!$this->useExpirationDate) {
$this->expirationDate = null;
}
$sqlExpirationDate = is_null($this->expirationDate) ? 'NULL' : 'FROM_UNIXTIME(' . claro_sql_escape($this->expirationDate) . ')';
$sqlCreationDate = is_null($this->publicationDate) ? 'NULL' : 'FROM_UNIXTIME(' . claro_sql_escape($this->publicationDate) . ')';
$sql = "UPDATE `" . $tbl_course . "`\n SET `intitule` = '" . claro_sql_escape($this->title) . "',\n `titulaires` = '" . claro_sql_escape($this->titular) . "',\n `administrativeNumber` = '" . claro_sql_escape($this->officialCode) . "',\n `language` = '" . claro_sql_escape($this->language) . "',\n `extLinkName` = '" . claro_sql_escape($this->departmentName) . "',\n `extLinkUrl` = '" . claro_sql_escape($this->extLinkUrl) . "',\n `email` = '" . claro_sql_escape($this->email) . "',\n `visibility` = '" . ($this->visibility ? 'visible' : 'invisible') . "',\n `access` = '" . claro_sql_escape($this->access) . "',\n `registration` = '" . claro_sql_escape($this->registration) . "',\n `registrationKey` = '" . claro_sql_escape($this->registrationKey) . "',\n `lastEdit` = NOW(),\n `creationDate` = " . $sqlCreationDate . ",\n `expirationDate` = " . $sqlExpirationDate . ",\n `status` = '" . claro_sql_escape($this->status) . "',\n `userLimit` = '" . (int) $this->userLimit . "'\n WHERE code='" . claro_sql_escape($this->courseId) . "'";
// Handle categories
// 1/ Remove all links in database
$this->unlinkCategories();
// 2/ Link new categories selection
$this->linkCategories($this->categories);
// If it's a source course, do the same for all its session courses
if ($this->isSourceCourse) {
$sql2 = "SELECT cours_id FROM `" . $tbl_course . "`\n WHERE sourceCourseId = " . $this->id;
$sessionCourses = claro_sql_query_fetch_all_rows($sql2);
foreach ($sessionCourses as $sessionCourse) {
unlink_course_categories($sessionCourse['cours_id']);
link_course_categories($sessionCourse['cours_id'], $this->categories);
}
}
return claro_sql_query($sql);
}
}
