/**
* Return the list of assigment of the current course
*
* @param string coursecode or null (to take default)
*
* @return array of array(id,title,description,def_submission_visibility,visibility,assignment_type,start_date_unix,end_date_unix)
*/
function assignmentList($courseCode = null)
{
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($courseCode));
$tbl_wrk_assignment = $tbl_cdb_names['wrk_assignment'];
$sql = "SELECT `id`,\n `title`,\n `description`,\n `def_submission_visibility`,\n `visibility`,\n `assignment_type`,\n unix_timestamp(`start_date`)\n AS `start_date_unix`,\n unix_timestamp(`end_date`)\n AS `end_date_unix`\n FROM `" . $tbl_wrk_assignment . "`";
return claro_sql_query_fetch_all_rows($sql);
}
public function __construct($courseId, $userId)
{
$this->courseId = $courseId;
$this->userId = (int) $userId;
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($this->courseId));
$this->tbl_course_tracking_event = $tbl_cdb_names['tracking_event'];
}
public function __construct($courseCode)
{
$this->courseCode = $courseCode;
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($this->courseCode));
$tblToolIntro = $tbl_cdb_names['tool_intro'];
$sql = "SELECT id, tool_id, title, display_date,\n content, rank, visibility\n FROM `{$tblToolIntro}`\n ORDER BY rank ASC";
$this->toolIntroductions = Claroline::getDatabase()->query($sql);
}
public function __construct($courseId, $userId)
{
$this->courseId = $courseId;
$this->userId = (int) $userId;
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($this->courseId));
$this->tbl_wrk_assignment = $tbl_cdb_names['wrk_assignment'];
$this->tbl_wrk_submission = $tbl_cdb_names['wrk_submission'];
$this->tbl_group_team = $tbl_cdb_names['group_team'];
}
/**
* constructor
*
* @param $course_id
*/
public function __construct($course_id = null)
{
$this->id = (int) -1;
$this->category = -1;
$this->title = '';
$this->content = '';
$this->lastEditDate = time();
$this->visibility = 'VISIBLE';
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
$this->tblCourseDescription = $tbl_cdb_names['course_description'];
}
function buildRecords($exportUserInfo = true)
{
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_user = $tbl_mdb_names['user'];
$tbl_rel_course_user = $tbl_mdb_names['rel_course_user'];
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($this->course_id));
$tbl_team = $tbl_cdb_names['group_team'];
$tbl_rel_team_user = $tbl_cdb_names['group_rel_team_user'];
$username = claro_is_platform_admin() && get_conf('export_sensitive_data_for_admin', false) || get_conf('export_user_username', false) ? "`U`.`username` AS `username`," : "";
if (claro_is_platform_admin() && get_conf('export_sensitive_data_for_admin', false) || get_conf('export_user_password', false)) {
if (claro_is_platform_admin() && get_conf('export_sensitive_data_for_admin', false) || get_conf('export_user_password_encrypted', true)) {
$password = "******";
} else {
$password = "******";
}
} else {
$password = '';
}
// get user list
$sql = "SELECT `U`.`user_id` AS `userId`,\n `U`.`nom` AS `lastname`,\n `U`.`prenom` AS `firstname`,\n {$username}\n {$password}\n `U`.`email` AS `email`,\n `U`.`officialCode` AS `officialCode`,\n GROUP_CONCAT(`G`.`id`) AS `groupId`,\n GROUP_CONCAT(`G`.`name`) AS `groupName`\n FROM\n (\n `" . $tbl_user . "` AS `U`,\n `" . $tbl_rel_course_user . "` AS `CU`\n )\n LEFT JOIN `" . $tbl_rel_team_user . "` AS `GU`\n ON `U`.`user_id` = `GU`.`user`\n LEFT JOIN `" . $tbl_team . "` AS `G`\n ON `GU`.`team` = `G`.`id`\n WHERE `U`.`user_id` = `CU`.`user_id`\n AND `CU`.`code_cours`= '" . claro_sql_escape($this->course_id) . "'\n GROUP BY U.`user_id`\n ORDER BY U.`user_id`";
$userList = claro_sql_query_fetch_all($sql);
// build recordlist with good values for answers
if (is_array($userList) && !empty($userList)) {
// add titles at row 0, for that get the keys of the first row of array
$this->recordList[0] = array_keys($userList[0]);
$i = 1;
$userIdList = array();
foreach ($userList as $user) {
$userIdList[$user['userId']] = $i;
if (!(claro_is_platform_admin() && get_conf('export_sensitive_data_for_admin', false) || get_conf('export_user_id', false))) {
$user['userId'] = $i;
}
// $this->recordList is defined in parent class csv
$this->recordList[$i] = $user;
$i++;
}
if ($exportUserInfo) {
$userInfoList = new UserInfoList($this->course_id);
$userInfoLabelList = $userInfoList->getUserInfoLabels();
foreach ($userInfoLabelList as $catId => $catTitle) {
$this->recordList[0][] = $catTitle;
$userCatInfo = $userInfoList->getUserInfo($catId);
foreach ($userCatInfo as $userCatInfo) {
$this->recordList[$userIdList[$userCatInfo['userId']]][] = $userCatInfo['content'];
}
}
}
}
if (is_array($this->recordList) && !empty($this->recordList)) {
return true;
} else {
return false;
}
}
/**
* Constructor
*
* @param integer $id
* @param string $courseCode
*/
public function __construct($id = null, $courseCode = null, $toolId = null, $title = '', $content = '', $rank = null, $displayDate = null, $visibility = 'SHOW')
{
$this->id = $id;
$this->couseCode = $courseCode;
$this->toolId = $toolId;
$this->title = $title;
$this->content = $content;
$this->rank = $rank;
$this->displayDate = $displayDate;
$this->visibility = $visibility;
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($this->couseCode));
$this->tblToolIntro = $tbl_cdb_names['tool_intro'];
}
/**
* get all the items
*
* @param $courseId string glued dbName of the course to affect default: current course
*
* @return array of arrays with data of the item
*
* @author Christophe Gesché <*****@*****.**>
*
*/
function course_description_get_item_list($courseId = null)
{
$tbl = claro_sql_get_course_tbl(claro_get_course_db_name_glued($courseId));
$tblCourseDescription = $tbl['course_description'];
if (get_conf('cldsc_use_new_ordering_of_labels')) {
// sort first the principal categories
$sql = "SELECT `cd`.`id`,\n\t `cd`.`category`,\n\t `cd`.`title`,\n\t `cd`.`content`,\n\t UNIX_TIMESTAMP(cd.`lastEditDate`)\n\t AS `unix_lastEditDate`,\n\t `cd`.`visibility`\n\t FROM `" . $tblCourseDescription . "` AS `cd`\n\t WHERE `cd`.`category` != '-1'\n\t ORDER BY `cd`.`category` ASC";
// and then the "other" category ... by title
$sql2 = "SELECT cd.`id`,\n\t cd.`category`,\n\t cd.`title`,\n\t cd.`content`,\n\t UNIX_TIMESTAMP(`cd`.`lastEditDate`)\n\t AS `unix_lastEditDate`,\n\t `cd`.`visibility`\n\t FROM `" . $tblCourseDescription . "` AS `cd`\n\t WHERE `cd`.`category` = '-1'\n\t ORDER BY `cd`.`title` ASC";
return array_merge(claro_sql_query_fetch_all($sql), claro_sql_query_fetch_all($sql2));
} else {
$sql = "SELECT `cd`.`id`,\n\t `cd`.`category`,\n\t `cd`.`title`,\n\t `cd`.`content`,\n\t UNIX_TIMESTAMP(cd.`lastEditDate`)\n\t AS `unix_lastEditDate`,\n\t `cd`.`visibility`\n\t FROM `" . $tblCourseDescription . "` AS `cd`\n\t ORDER BY `cd`.`category` ASC";
return claro_sql_query_fetch_all($sql);
}
}
/**
* Return list of groupe subscribed by a given user in a given/current course
*
* @param integer $user_id
* @param course_syscode $course
*
*/
function get_user_group_list($userId, $course = null)
{
if (!is_null($course)) {
$course = claro_get_current_course_id();
}
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course));
$tbl_group_team = $tbl_cdb_names['group_team'];
$tbl_group_rel_team_user = $tbl_cdb_names['group_rel_team_user'];
$mainTableName = get_module_main_tbl(array('user', 'rel_course_user'));
$userGroupList = array();
$sql = "SELECT `tu`.`team` as `id` , `t`.`name`\n FROM `" . $tbl_group_rel_team_user . "` as `tu`\n INNER JOIN `" . $tbl_group_team . "` as `t`\n ON `tu`.`team` = `t`.`id`\n INNER JOIN `" . $mainTableName['rel_course_user'] . "` AS `cu`\n ON `cu`.user_id = `tu`.`user`\n WHERE `tu`.`user` = " . (int) $userId;
$groupList = claro_sql_query_fetch_all($sql);
if (is_array($groupList)) {
foreach ($groupList as $group) {
$userGroupList[$group['id']] = $group;
}
}
return $userGroupList;
}
/**
* Get tool visibility for course when not in course context
* @param int $toolId
* @param string $courseCode
* @return bool
* @since Claroline 1.11.7
*/
function is_tool_visible_for_portlet($toolId, $courseCode)
{
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($courseCode));
return (bool) Claroline::getDatabase()->query("SELECT `visibility`\n FROM `" . $tbl_cdb_names['tool'] . "`\n WHERE `tool_id` = " . Claroline::getDatabase()->quote($toolId))->fetch(Mysql_ResultSet::FETCH_VALUE);
}
/**
* get user parameter on the base of a cookie value
*
* @author Hugues Peeters <*****@*****.**>
* @param string $auth
* @param string $cookie
* @param string $cid
* @param int $gid
* @return array user parameters if it suceeds
* boolean false otherwise
*/
function get_user_info_from_cookie($auth, $cookie, $cid, $gid)
{
if (!is_allowed_to_receive_user_info($auth)) {
return null;
}
$res = array('userLastname' => null, 'userFirstname' => null, 'userLoginName' => null, 'userEmail' => null, 'userOfficialCode' => null, 'ssoCookieName' => null, 'ssoCookieValue' => null, 'ssoCookieExpireTime' => null, 'ssoCookieDomain' => null, 'ssoCookiePath' => null, 'courseTitle' => null, 'courseTitular' => null, 'courseCode' => null, 'courseDbName' => null, 'courseRegistrationAllowed' => null, 'courseVisibility' => null, 'courseAccess' => null, 'is_courseMember' => null, 'is_courseTutor' => null, 'is_courseAdmin' => null, 'is_courseAllowed' => null, 'groupName' => null, 'groupDescription' => null, 'groupPrivate' => null, 'is_groupMember' => null, 'is_groupTutor' => null, 'is_groupAllowed' => null);
$ssoCookieName = get_conf('ssoCookieName');
$ssoCookieDomain = get_conf('ssoCookieDomain');
$ssoCookiePath = get_conf('ssoCookiePath');
$ssoCookieExpireTime = time() + get_conf('ssoCookiePeriodValidity', 3600);
$mainTblList = claro_sql_get_main_tbl();
$tbl_user = $mainTblList['user'];
$tbl_sso = $mainTblList['sso'];
$sql = "SELECT user.nom lastname,\n user.prenom firstname,\n user.username loginName,\n user.email email,\n user.officialCode officialCode,\n user.user_id userId\n\n FROM `" . $tbl_sso . "` AS sso,\n `" . $tbl_user . "` AS user\n WHERE cookie = '" . $cookie . "'\n AND user.user_id = sso.user_id";
$userResult = claro_sql_query_fetch_all($sql);
if (count($userResult) > 0) {
$user = $userResult[0];
$uid = $user['userId'];
$res['userLastname'] = $user['lastname'];
$res['userFirstname'] = $user['firstname'];
$res['userLoginName'] = $user['loginName'];
$res['userEmail'] = $user['email'];
$res['userOfficialCode'] = $user['officialCode'];
$newSsoCookieValue = generate_cookie();
record_sso_cookie($uid, $newSsoCookieValue);
$res['ssoCookieName'] = $ssoCookieName;
$res['ssoCookieValue'] = $newSsoCookieValue;
$res['ssoCookieExpireTime'] = $ssoCookieExpireTime;
$res['ssoCookieDomain'] = $ssoCookieDomain;
$res['ssoCookiePath'] = $ssoCookiePath;
} else {
return null;
}
if ($uid && $cid) {
$tbl_course = $mainTblList['course'];
// for claroline 1.6
// $tbl_course = $mainTblList['cours' ]; // for claroline 1.5
$tbl_rel_course_user = $mainTblList['rel_course_user'];
$sql = "SELECT `c`.`intitule` AS title,\n `c`.`administrativeNumber` AS officialCode,\n `c`.`titulaires` AS titular,\n `c`.`dbName` AS dbName,\n `c`.`visibility` AS visibility,\n `c`.`access` AS access,\n `c`.`registration` AS registration,\n `cu`.`isCourseManager` AS isCourseManager,\n `cu`.`role` AS userRole,\n `cu`.`tutor` AS tutor\n FROM `" . $tbl_course . "` AS c\n LEFT JOIN `" . $tbl_rel_course_user . "` AS cu\n ON `c`.`code` = `cu`.`code_cours`\n AND `cu`.`user_id` = " . (int) $uid . "\n WHERE `c`.`code` = '" . $cid . "'";
$courseResult = claro_sql_query_fetch_all($sql);
if (count($courseResult > 0)) {
$course = $courseResult[0];
$res['courseTitle'] = $course['title'];
$res['courseTitular'] = $course['titular'];
$res['courseCode'] = $course['officialCode'];
$res['courseDbName'] = $course['dbName'];
$res['courseRegistrationAllowed'] = (bool) ($course['registration'] == 'OPEN');
$res['courseVisibility'] = (bool) ($course['visibility'] == 'VISIBLE');
$res['courseAccess'] = (bool) ($course['access'] == 'PUBLIC');
$res['is_courseMember'] = (bool) (!is_null($course['userStatus']));
$res['is_courseTutor'] = (bool) ($course['tutor'] == 1);
$res['is_courseAdmin'] = (bool) ($course['isCourseManager'] == 1);
$res['is_courseAllowed'] = (bool) ($course['visibility'] || $course['is_courseMember']);
}
}
if ($uid && $cid && $gid) {
$courseTblList = claro_sql_get_course_tbl(claro_get_course_db_name_glued($cid));
$tbl_group_team = $courseTblList['group_team'];
$tbl_group_property = $courseTblList['group_property'];
$tbl_group_rel_team_user = $courseTblList['group_rel_team_user'];
$sql = "SELECT g.`name`,\n g.`description`,\n g.`tutor` tutorId,\n gp.`private`,\n gp.`self_registration`,\n gtu.`user`,\n gtu.`team`,\n gtu.`status`,\n gtu.`role`\n FROM `" . $tbl_group_team . "` AS g,\n `" . $tbl_group_property . "` AS gp,\n `" . $tbl_group_rel_team_user . "` AS gtu\n WHERE gtu.`user` = '" . $uid . "'\n AND gtu.`team` = '" . $gid . "'\n AND gtu.`team` = g.`id`";
$groupResult = claro_sql_query_fetch_all($sql);
if (count($groupResult) > 0) {
$group = $groupResult[0];
$res['groupName'] = $group['name'];
$res['groupDescription'] = $group['description'];
$res['groupPrivate'] = (bool) ($group['private'] == 1);
$res['is_groupMember'] = (bool) ($group['user'] == $uid);
$res['is_groupTutor'] = (bool) ($group['tutorId'] == $uid);
$res['is_groupAllowed'] = (bool) ($group['is_groupMember'] || $group['is_groupTutor'] || !$group['private']);
}
}
return $res;
}
function announcement_get_item($announcement_id, $course_id = NULL)
{
$tbl = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
$sql = "SELECT id,\n\t\t\t\t\t\t\t\t\t title,\n\t\t\t\t\t contenu AS content,\n\t\t\t\t\t\t temps AS `time`,\n\t\t\t\t\t\t\t\t\t visibility,\n\t\t\t\t\t ordre AS rank\n\t\t\t\tFROM `" . $tbl['announcement'] . "`\n\t\t\t\tWHERE id=" . (int) $announcement_id;
$announcement = claro_sql_query_get_single_row($sql);
if ($announcement) {
return $announcement;
} else {
return claro_failure::set_failure('ANNOUNCEMENT_UNKNOW');
}
}
/**
* Get the list of invisible documents of the current course
*
* @param $baseWorkDir path document
* @param $cidReq course identifier
* @return list of invisible document
*/
function getInvisibleDocumentList($baseWorkDir, $cidReq = null)
{
$documentList = array();
if (is_null($cidReq)) {
$cid = claro_get_current_course_id();
} else {
$cid = $cidReq;
}
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($cid));
$tbl_document = $tbl_cdb_names['document'];
$sql = "SELECT path\n FROM `" . $tbl_document . "`\n WHERE visibility = 'i'";
$documentList = claro_sql_query_fetch_all_cols($sql);
$documentList = $documentList['path'];
for ($i = 0; $i < count($documentList); $i++) {
$documentList[$i] = '/' . ltrim($baseWorkDir . $documentList[$i], '/');
}
return $documentList;
}
/**
* Upgrade tracking tool to 1.8
*/
function tracking_upgrade_to_18($course_code)
{
$versionRequiredToProceed = '/^1.7/';
$tool = 'CLSTATS';
global $currentCourseVersion;
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
$sql = "UPDATE `" . $currentCourseDbNameGlu . "track_e_access`\n SET access_tlabel = TRIM(TRAILING '_' FROM access_tlabel)";
if (upgrade_sql_query($sql)) {
$step = set_upgrade_status($tool, 2, $course_code);
} else {
return $step;
}
case 2:
$sql = "ALTER IGNORE TABLE `" . $currentCourseDbNameGlu . "track_e_exercices`\n CHANGE `exe_exo_id` `exe_exo_id` int(11)";
if (upgrade_sql_query($sql)) {
$step = set_upgrade_status($tool, 0, $course_code);
} else {
return $step;
}
default:
return $step;
}
}
return false;
}
protected function mergeCourseUsers($uidToRemove, $uidToKeep, $courseId)
{
$courseTbl = claro_sql_get_course_tbl(claro_get_course_db_name_glued($courseId));
// Get groups for the user to remove
$sql = "SELECT team\n FROM `{$courseTbl['group_rel_team_user']}`\n WHERE user= "******"\n SELECT user, team, role, status\n FROM `{$courseTbl['group_rel_team_user']}`\n WHERE user = "******"\n AND team = " . (int) $thisTeam);
$toRemove_team_entry = claro_sql_query_fetch_single_row("\n SELECT user, team, role, status\n FROM `{$courseTbl['group_rel_team_user']}`\n WHERE user = "******"\n AND team = " . (int) $thisTeam);
if ($toKeep_team_entry) {
$status = $toKeep_team_entry['status'] > $toRemove_team_entry['status'] ? null : $toRemove_team_entry['status'];
$role = empty($toKeep_team_entry['role']) ? $toRemove_team_entry['role'] : null;
if (!is_null($role) || !is_null($status)) {
if (!claro_sql_query("UPDATE `{$courseTbl['group_rel_team_user']}`\n SET role = '" . $role . "',\n status = " . $status . "\n WHERE user = "******"\n AND team = " . (int) $thisTeam)) {
Console::error("Cannot update user group status for +{$uidToKeep} in group_rel_team_user in {$courseId}:{$thisTeam}");
$this->hasError = true;
}
}
if (!claro_sql_query("DELETE FROM `{$courseTbl['group_rel_team_user']}`\n WHERE user = "******"\n AND team = " . (int) $thisTeam)) {
Console::error("Cannot delete user -{$uidToRemove} in group_rel_team_user in {$courseId}:{$thisTeam}");
$this->hasError = true;
}
} else {
if (!claro_sql_query("UPDATE `{$courseTbl['group_rel_team_user']}`\n SET user = "******"\n WHERE user = "******"\n AND team = " . (int) $thisTeam)) {
Console::error("Cannot replace -{$uidToRemove} with +{$uidToKeep} in group_rel_team_user {$courseId}:{$thisTeam}");
$this->hasError = true;
}
}
}
// Update tracking
$sql = "UPDATE `{$courseTbl['tracking_event']}`\n SET user_id = " . (int) $uidToKeep . "\n WHERE user_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot replace -{$uidToRemove} with +{$uidToKeep} in tracking_event in course {$courseId}");
$this->hasError = true;
}
$qwz_tbl_names = get_module_course_tbl(array('qwz_tracking'), $courseId);
$sql = "UPDATE `{$qwz_tbl_names['qwz_tracking']}`\n SET user_id = " . (int) $uidToKeep . "\n WHERE user_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot replace -{$uidToRemove} with +{$uidToKeep} in qwz_tracking in {$courseId}");
$this->hasError = true;
}
// Update user info in course
$sql = "DELETE FROM `{$courseTbl['userinfo_content']}`\n WHERE user_id = " . (int) $uidToRemove;
if (!claro_sql_query($sql)) {
Console::error("Cannot remove user info for user -{$uidToRemove} in {$courseId}");
$this->hasError = true;
}
}
/**
* Upgrade introduction text table to 1.7
* @param $course_code string
* @return boolean whether tru if succeed
*/
function introtext_upgrade_to_17($course_code)
{
global $currentCourseVersion;
$versionRequiredToProceed = '/^1.6/';
$tool = 'CLINTRO';
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
// IN 1.7 intro table has new column to prepare multi-intro orderable
// Job for this upgrade
// STEP 1 BAcKUP OLD TABLE Before creat the new
// STEP 2 Create The new table
// STEP 3 FILL The new table with value from the old
// STEP 4 Delete backuped table
////////////////////////////////////
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
// groups of forums
case 1:
// STEP 1 BAcKUP OLD TABLE Before creat the new
$sql_step1[] = "RENAME TABLE `" . $currentCourseDbNameGlu . "tool_intro` TO `" . $currentCourseDbNameGlu . "tool_intro_prev17`";
if (!upgrade_apply_sql($sql_step1)) {
return $step;
}
$step = set_upgrade_status($tool, 2, $course_code);
case 2:
// STEP 2 Create The new table
$sql_step2[] = "CREATE TABLE `" . $currentCourseDbNameGlu . "tool_intro` (\n `id` int(11) NOT NULL auto_increment,\n `tool_id` int(11) NOT NULL default '0',\n `title` varchar(255) default NULL,\n `display_date` datetime default NULL,\n `content` text,\n `rank` int(11) default '1',\n `visibility` enum('SHOW','HIDE') NOT NULL default 'SHOW',\n PRIMARY KEY (`id`) ) ";
if (!upgrade_apply_sql($sql_step2)) {
return $step;
}
$step = set_upgrade_status($tool, 3, $course_code);
case 3:
// STEP 3 FILL The new table with value from the old
$sql = " SELECT `id`, `texte_intro`\n FROM `" . $currentCourseDbNameGlu . "tool_intro_prev17` ";
$result = upgrade_sql_query($sql);
if (!$result) {
return $step;
}
while ($row = mysql_fetch_array($result)) {
$sql = "INSERT INTO `" . $currentCourseDbNameGlu . "tool_intro`\n (`tool_id`,`content`)\n VALUES\n ('" . $row['id'] . "','" . claro_sql_escape($row['texte_intro']) . "')";
if (!upgrade_sql_query($sql)) {
return $step;
}
}
$step = set_upgrade_status($tool, 4, $course_code);
case 4:
// STEP 4 Delete OLD
$sql_step4[] = "DROP TABLE IF EXISTS `" . $currentCourseDbNameGlu . "tool_intro_prev17`";
if (!upgrade_apply_sql($sql_step4)) {
return $step;
}
$step = set_upgrade_status($tool, 0, $course_code);
default:
return $step;
}
}
return false;
}
/**
* delete a course of the plateform
*
* TODO detect failure with claro_failure
*
* @param string $cid
*
* @return boolean TRUE if suceed
* boolean FALSE otherwise.
*/
function delete_course($code, $sourceCourseId)
{
global $eventNotifier;
// Declare needed tables
$tbl_mdb_names = claro_sql_get_main_tbl();
$tbl_course = $tbl_mdb_names['course'];
$tbl_rel_course_user = $tbl_mdb_names['rel_course_user'];
$tbl_course_class = $tbl_mdb_names['rel_course_class'];
$tbl_rel_course_category = $tbl_mdb_names['rel_course_category'];
$tbl_rel_course_portlet = $tbl_mdb_names['rel_course_portlet'];
$this_course = claro_get_course_data($code);
if (!$this_course) {
// This is bad !
throw new Exception("Course not found");
}
$currentCourseId = trim($this_course['sysCode']);
if (empty($currentCourseId)) {
// This is bad !
throw new Exception("Missing course id");
}
// Delete user registrations into this course
$sql = 'DELETE FROM `' . $tbl_rel_course_user . '`
WHERE code_cours="' . $currentCourseId . '"';
claro_sql_query($sql);
// Remove any recording in rel_cours_class
$sql = "DELETE FROM `" . $tbl_course_class . "`\n WHERE courseId ='" . claro_sql_escape($currentCourseId) . "'";
claro_sql_query($sql);
// Remove links between this course and categories
$sql = "DELETE FROM `" . $tbl_rel_course_category . "`\n WHERE courseId ='" . $this_course['id'] . "'";
claro_sql_query($sql);
// Remove links between this course and portlets
$sql = "DELETE FROM `" . $tbl_rel_course_portlet . "`\n WHERE courseId ='" . $this_course['id'] . "'";
claro_sql_query($sql);
// Delete the course inside the platform course registery
$sql = 'DELETE FROM `' . $tbl_course . '`
WHERE code= "' . claro_sql_escape($currentCourseId) . '"';
claro_sql_query($sql);
// Is it a session course ?
if (!is_null($sourceCourseId)) {
// Does the source course still have session courses ?
$sql = "SELECT COUNT(cours_id) AS nbSessionCourses\n FROM `" . $tbl_course . "`\n WHERE sourceCourseId = " . (int) $sourceCourseId;
$result = claro_sql_query_get_single_row($sql);
if ($result['nbSessionCourses'] == 0) {
$sql = "UPDATE `" . $tbl_course . "`\n SET isSourceCourse = 0\n WHERE cours_id = " . (int) $sourceCourseId;
claro_sql_query($sql);
}
}
// Delete course right
RightCourseProfileToolRight::resetAllRightProfile($currentCourseId);
// Delete course module tables
// FIXME handle errors
list($success, $log) = delete_all_modules_from_course($currentCourseId);
// Notify the course deletion event
$args['cid'] = $this_course['sysCode'];
$args['tid'] = null;
$args['rid'] = null;
$args['gid'] = null;
$args['uid'] = $GLOBALS['_uid'];
$eventNotifier->notifyEvent("course_deleted", $args);
if ($currentCourseId == $code) {
$currentCourseDbName = trim($this_course['dbName']);
$currentCourseDbNameGlu = trim($this_course['dbNameGlu']);
$currentCoursePath = trim($this_course['path']);
if (empty($currentCourseDbName)) {
// This is bad !
throw new Exception("Missing db name");
}
if (empty($currentCourseDbNameGlu)) {
// This is bad !
throw new Exception("Missing db name glu");
}
if (get_conf('singleDbEnabled')) {
// SEARCH ALL TABLES RELATED TO THE CURRENT COURSE
claro_sql_query("use " . get_conf('mainDbName'));
$tbl_to_delete = claro_sql_get_course_tbl(claro_get_course_db_name_glued($currentCourseId));
foreach ($tbl_to_delete as $tbl_name) {
$sql = 'DROP TABLE IF EXISTS `' . $tbl_name . '`';
claro_sql_query($sql);
}
// Underscores must be replaced because they are used as wildcards in LIKE sql statement
$cleanCourseDbNameGlu = str_replace("_", "\\_", $currentCourseDbNameGlu);
$sql = 'SHOW TABLES LIKE "' . $cleanCourseDbNameGlu . '%"';
$result = claro_sql_query($sql);
// DELETE ALL TABLES OF THE CURRENT COURSE
$tblSurvivor = array();
while (false !== ($courseTable = mysql_fetch_array($result, MYSQL_NUM))) {
$tblSurvivor[] = $courseTable[0];
//$tblSurvivor[$courseTable]='not deleted';
}
if (sizeof($tblSurvivor) > 0) {
Claroline::getInstance()->log('DELETE_COURSE', array_merge(array('DELETED_COURSE_CODE' => $code, 'UNDELETED_TABLE_COUNTER' => sizeof($tblSurvivor)), $tblSurvivor));
}
} else {
$sql = "DROP DATABASE `" . $currentCourseDbName . "`";
claro_sql_query($sql);
}
// MOVE THE COURSE DIRECTORY INTO THE COURSE GARBAGE COLLECTOR
if (!empty($currentCoursePath)) {
if (file_exists(get_conf('coursesRepositorySys') . $currentCoursePath . '/')) {
claro_mkdir(get_conf('garbageRepositorySys'), CLARO_FILE_PERMISSIONS, true);
rename(get_conf('coursesRepositorySys') . $currentCoursePath . '/', get_conf('garbageRepositorySys', 'garbage') . '/' . $currentCoursePath . '_' . date('YmdHis'));
} else {
Console::warning("DELETE_COURSE : Course directory not found {$currentCoursePath} for course {$currentCourseId}");
}
Claroline::log('COURSE_DELETED', array('courseCode' => $currentCourseId, 'courseDbName' => $currentCourseDbName, 'courseDbNameGlu' => $currentCourseDbNameGlu, 'coursePath' => $currentCoursePath));
return true;
} else {
Console::error("DELETE_COURSE : Try to delete a course repository with no folder name {$currentCourseId} !");
return true;
}
} else {
return false;
}
}
claro_die(get_lang('Tracking has been disabled by system administrator.'));
}
if (!claro_is_in_a_course() || !claro_is_course_allowed()) {
claro_disp_auth_form();
}
if (!claro_is_course_manager()) {
claro_die(get_lang('Not allowed'));
}
/*
* Libraries
*/
require_once get_path('incRepositorySys') . '/lib/statsUtils.lib.inc.php';
/*
* DB tables definition
*/
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued(claro_get_current_course_id()));
$tbl_course_tracking_event = $tbl_cdb_names['tracking_event'];
/*
* Input
*/
if (!empty($_REQUEST['displayType']) && in_array($_REQUEST['displayType'], array('month', 'day', 'hour'))) {
$displayType = $_REQUEST['displayType'];
} else {
$displayType = '';
}
if (!empty($_REQUEST['period']) && in_array($_REQUEST['period'], array('year', 'month', 'day'))) {
$period = $_REQUEST['period'];
} else {
$period = 'day';
}
if (!empty($_REQUEST['reqdate'])) {
/**
* constructor
*
* @author Sebastien Piraux <*****@*****.**>
*/
public function __construct($course_id = null)
{
$this->id = (int) -1;
$this->title = '';
$this->description = '';
$this->visibility = 'VISIBLE';
$this->defaultSubmissionVisibility = 'VISIBLE';
$this->assignmentType = 'INDIVIDUAL';
$this->submissionType = 'FILE';
$this->allowLateUpload = 'YES';
$this->startDate = time();
// now as unix timestamp
$days = (int) get_conf('clwrk_endDateDelay', 365);
$this->endDate = strtotime("+{$days} days");
$this->autoFeedbackText = '';
$this->autoFeedbackFilename = '';
$this->autoFeedbackSubmitMethod = 'ENDDATE';
$this->submissionList = array();
$this->assigDirSys = '';
$this->assigDirWeb = '';
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
$this->tblAssignment = $tbl_cdb_names['wrk_assignment'];
$this->tblSubmission = $tbl_cdb_names['wrk_submission'];
$this->applyVisibilityChangeToOldSubmissions = !get_conf('confval_def_sub_vis_change_only_new');
$this->_forceVisibilityChange = false;
}
/**
* remove tracking user data from a course
*
* @author Hugues Peeters <*****@*****.**>
*
* @param int $userId user ID from the course_user table
* @param string $courseCode course code from the cours table
*
* @return boolean TRUE if removing suceed
* boolean FALSE otherwise.
*/
function user_delete_course_tracking_data($userId, $courseId)
{
$dbNameGlued = claro_get_course_db_name_glued($courseId);
$tbl_cdb_names = claro_sql_get_course_tbl($dbNameGlued);
$tbl_tracking_event = $tbl_cdb_names['tracking_event'];
$sql = "DELETE FROM `" . $tbl_tracking_event . "` WHERE user_id = " . (int) $userId;
return claro_sql_query($sql);
}
/**
* Is the module registered in the given course ?
* @param int $toolId main tool id
* @param string $courseId course code
* @return boolean
*/
function is_module_registered_in_course($toolId, $courseId)
{
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($courseId));
$tblCourseToolList = $tbl_cdb_names['tool'];
$sql = "SELECT COUNT(*) FROM `{$tblCourseToolList}`\n" . "WHERE tool_id = " . (int) $toolId;
$res = claro_sql_query_fetch_single_value($sql);
return $res;
}
/**
* @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;
}
}
/**
* Get list of all user properties for this course
*
* @param string $course_id coude code of course
* @return array containing a list of arrays.
* And each of these arrays contains
* 'catId', 'title', 'comment', and 'nbline',
*
*/
function claro_user_info_claro_user_info_get_cat_def_list($course_id = NULL)
{
$tbl = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
$sql = "SELECT id catId, " . "\n" . " title," . "\n" . " comment ," . "\n" . " nbline" . "\n" . "FROM `" . $tbl['userinfo_def'] . "`" . "\n" . "ORDER BY rank";
$cat_def_list = claro_sql_query_fetch_all_rows($sql);
if (!empty($cat_def_list)) {
return $cat_def_list;
} else {
return false;
}
}
function init_qwz_questions_categories()
{
$currentCourseDbNameGlu = claro_get_course_db_name_glued(claro_get_current_course_id());
$sql = "CREATE TABLE IF NOT EXISTS `" . $currentCourseDbNameGlu . "qwz_questions_categories` (\n `id` int(11) NOT NULL auto_increment,\n `title` varchar(50) NOT NULL,\n `description` TEXT,\n PRIMARY KEY (`id`)\n ) ENGINE=MyISAM COMMENT='Record the categories of questions';";
claro_sql_query($sql);
}
/**
* Upgrade tracking tool to 1.6
* STEP 1 BAcKUP OLD TABLE Before creat the new
* STEP 2 Create The new table
* STEP 3 Update tracking Table
*/
function tracking_upgrade_to_16($course_code)
{
global $currentCourseVersion;
$versionRequiredToProceed = '/^1.5/';
$tool = 'CLTRK';
$currentCourseDbNameGlu = claro_get_course_db_name_glued($course_code);
if (preg_match($versionRequiredToProceed, $currentCourseVersion)) {
switch ($step = get_upgrade_status($tool, $course_code)) {
case 1:
// STEP 1 BAcKUP OLD TABLE Before creat the new
$sql_step1[] = "RENAME TABLE `" . $currentCourseDbNameGlu . "track_e_access` TO `" . $currentCourseDbNameGlu . "track_e_access_15`";
if (!upgrade_apply_sql($sql_step1)) {
return $step;
}
$step = set_upgrade_status($tool, 2, $course_code);
case 2:
// STEP 2 Create The new table
$sql_step2[] = "CREATE TABLE IF NOT EXISTS `" . $currentCourseDbNameGlu . "track_e_access` (\n `access_id` int(11) NOT NULL auto_increment,\n `access_user_id` int(11) default NULL ,\n `access_date` datetime NOT NULL default '0000-00-00 00:00:00',\n `access_tid` int(10) default NULL,\n `access_tlabel` varchar(8) default NULL,\n PRIMARY KEY (`access_id`)\n ) ENGINE=MyISAM COMMENT='Record informations about access to course or tools'";
if (!upgrade_apply_sql($sql_step2)) {
return $step;
}
$step = set_upgrade_status($tool, 3, $course_code);
case 3:
// STEP 3 Update tracking Table
$sql_step3[] = "ALTER IGNORE TABLE `" . $currentCourseDbNameGlu . "track_e_exercices` " . "ADD `exe_time` mediumint(8) NOT NULL default '0'";
$sql_step3[] = "ALTER IGNORE TABLE `" . $currentCourseDbNameGlu . "track_e_exercices` " . "CHANGE `exe_result` `exe_result` float NOT NULL default '0'";
$sql_step3[] = "ALTER IGNORE TABLE `" . $currentCourseDbNameGlu . "track_e_exercices` " . "CHANGE `exe_weighting` `exe_weighting` float NOT NULL default '0'";
if (!upgrade_apply_sql($sql_step3)) {
return $step;
}
$step = set_upgrade_status($tool, 0, $course_code);
default:
return $step;
}
}
return false;
}
/**
* Create a new forum (set of threads)
*
* @param string $forum_name
* @param string $forum_desc
* @param boolean $forum_post_allowed
* @param integer $cat_id
* @param integer $group_id default null(current)
* @param string $course_id default null(current)
*
* @return integer id of new forum;
*
*/
function create_forum($forum_name, $forum_desc, $forum_post_allowed, $cat_id, $anonymity_type = 'forbidden', $group_id = null, $course_id = NULL)
{
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
$tbl_forum_forums = $tbl_cdb_names['bb_forums'];
// find order in the category we have to give to the newly created forum
$sql = "SELECT MAX(`forum_order`)\n FROM `" . $tbl_forum_forums . "`\n WHERE cat_id = " . (int) $cat_id;
$result = claro_sql_query($sql);
list($orderMax) = mysql_fetch_row($result);
$order = $orderMax + 1;
//temporary fix for 1.9 releases : avoids change in database definition (using unused 'forum_type' field)
//TODO : use a specific enum field (field name: anonymity) in bb_forum table
switch ($anonymity_type) {
case 'forbidden':
$forum_type = 0;
break;
case 'allowed':
$forum_type = 1;
break;
case 'default':
$forum_type = 2;
break;
default:
$forum_type = 0;
break;
}
// add new forum in DB
$sql = "INSERT INTO `" . $tbl_forum_forums . "`\n SET forum_name = '" . claro_sql_escape($forum_name) . "',\n group_id = " . (is_null($group_id) ? "NULL" : (int) $group_id) . ",\n forum_desc = '" . claro_sql_escape($forum_desc) . "',\n forum_access = " . ($forum_post_allowed ? 2 : 0) . ",\n forum_moderator = 1,\n cat_id = " . (int) $cat_id . ",\n forum_type = " . (int) $forum_type . ",\n forum_order = " . (int) $order;
return claro_sql_query_insert_id($sql);
}
public function __construct($courseId, $userId)
{
$this->courseId = $courseId;
$this->userId = (int) $userId;
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($this->courseId));
$this->tbl_bb_topics = $tbl_cdb_names['bb_topics'];
$this->tbl_bb_posts = $tbl_cdb_names['bb_posts'];
}
$reqdate = time();
}
/*
* Permissions
*/
$userData = user_get_properties($userId);
if (is_null($userId) || empty($userData)) {
claro_die(get_lang('User not found'));
}
if (!claro_is_platform_admin() && !claro_is_course_manager() && $userId != claro_get_current_user_id()) {
claro_die(get_lang('Not allowed'));
}
/*
* Prepare output
*/
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($courseId));
$tbl_course_tracking_event = $tbl_cdb_names['tracking_event'];
// FIXME use userCard
if ($period == 'week') {
$sqlAccessDates = "SELECT `date`\n FROM `" . $tbl_course_tracking_event . "`\n WHERE `user_id` = " . (int) $userId . "\n AND `type` = 'course_access'\n AND WEEK(`date`) = WEEK( FROM_UNIXTIME('" . $reqdate . "') )\n AND YEAR(`date`) = YEAR( FROM_UNIXTIME(" . $reqdate . ") )\n ORDER BY `date` ASC ";
// used in links to move from one week to another
$previousReqDate = $reqdate - 7 * 86400;
// 86400=24*60*60
$nextReqDate = $reqdate + 7 * 86400;
// prepare displayed date
$weekStartDate = $reqdate - 86400 * date("w", $reqdate);
$weekEndDate = $reqdate + 86400 * (6 - date("w", $reqdate));
$displayedDate = get_lang('From') . ' ' . claro_html_localised_date(get_locale('dateFormatLong'), $weekStartDate) . " " . get_lang('to') . ' ' . claro_html_localised_date(get_locale('dateFormatLong'), $weekEndDate);
} else {
$sqlAccessDates = "SELECT `date`\n FROM `" . $tbl_course_tracking_event . "`\n WHERE `user_id` = " . (int) $userId . "\n AND `type` = 'course_access'\n AND MONTH(`date`) = MONTH( FROM_UNIXTIME('" . $reqdate . "') )\n AND YEAR(`date`) = YEAR( FROM_UNIXTIME(" . $reqdate . ") )\n ORDER BY `date` ASC ";
// used in links to move from one month to another
function clann_get_max_and_min_rank($course_id = null)
{
$course_id = is_null($course_id) ? claro_get_current_course_id() : $course_id;
$tbl = claro_sql_get_course_tbl(claro_get_course_db_name_glued($course_id));
return Claroline::getDatabase()->query("\n SELECT \n MAX(ordre) AS maxRank,\n MIN(ordre) AS minRank\n FROM `" . $tbl['announcement'] . "`")->fetch();
}
/**
* Is tool visible
*
* @param integer $tid tool identifier
* @param string courseId
* @todo to move in a lib
*
* @return boolean 'true' if it's visible
*/
function claro_is_tool_visible($tid, $courseId)
{
global $_mainToolId;
static $toolVisibilityCache = false;
// load tool id
if (is_null($tid)) {
if (!empty($_mainToolId)) {
$tid = $_mainToolId;
} else {
return false;
}
}
// load course id
if (is_null($courseId)) {
if (claro_is_in_a_course()) {
$courseId = claro_get_current_course_id();
} else {
return false;
}
}
if (!$toolVisibilityCache) {
$toolVisibilityCache = array();
$tbl_cdb_names = claro_sql_get_course_tbl(claro_get_course_db_name_glued($courseId));
$sql = " SELECT tool_id, visibility\n FROM `" . $tbl_cdb_names['tool'] . "`";
$tool_visibilityTmp = claro_sql_query_fetch_all_rows($sql);
foreach ($tool_visibilityTmp as $tool) {
$toolVisibilityCache[$tool['tool_id']] = $tool['visibility'];
}
}
return (bool) $toolVisibilityCache[$tid];
}
