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 { // ???? } }
/** * create the message in the message table and return the identification of this * * @return int message identification */ private final function addMessage($messageToSend) { //create an array of the name of the table needed $tableName = get_module_main_tbl(array('im_message')); $subject = claro_sql_escape($messageToSend->getSubject()); $message = claro_sql_escape($messageToSend->getMessage()); if (is_null($messageToSend->getSender())) { $sender = claro_get_current_user_id(); } else { $sender = (int) $messageToSend->getSender(); } if (!is_null($messageToSend->getCourseCode())) { $course = "'" . claro_sql_escape($messageToSend->getCourseCode()) . "'"; } else { $course = "NULL"; } if (!is_null($messageToSend->getGroupId())) { $group = (int) $messageToSend->getGroupId(); } else { $group = "NULL"; } if (!is_null($messageToSend->getToolsLabel())) { $tools = "'" . claro_sql_escape($messageToSend->getToolsLabel()) . "'"; } else { $tools = "NULL"; } // add the message in the table of messages and retrieves the ID $addInternalMessageSQL = "INSERT INTO `" . $tableName['im_message'] . "` \n" . "(sender, subject, message, send_time, course, `group` , tools) \n" . "VALUES ({$sender},'" . $subject . "','" . $message . "', '\n" . date("Y-m-d H:i:s", claro_time()) . "'," . $course . "," . $group . "," . $tools . ")\n"; // try to read the last ID inserted if the request pass if (claro_sql_query($addInternalMessageSQL)) { return claro_sql_insert_id(); } else { throw new Exception(claro_sql_errno() . ":" . claro_sql_error()); } }
/** * @see SelectorStrategy */ public function getStrategy() { $condition = $this->strategy; foreach ($this->valueList as $key => $value) { $condition = str_replace('%' . $key . '%', claro_sql_escape($value), $condition); } return " WHERE " . $condition . "\n"; }
/** * 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); }
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; } }
/** * return the conditions * * @return string conditions */ public function getStrategy() { $condition = ""; $this->search = trim($this->search); if ($this->search != "" && $this->search != "*") { $wordList = array(); if ($this->searchStrategy == parent::SEARCH_STRATEGY_EXPRESSION) { $wordList = array($this->search); } elseif ($this->searchStrategy == parent::SEARCH_STRATEGY_WORD) { $wordList = preg_split('/\\s+/', $this->search); } $searchCondition = ""; foreach ($wordList as $key => $word) { if ($searchCondition != "") { $searchCondition .= " OR "; } $searchCondition .= str_replace('%search%', claro_sql_escape($word), self::SEARCH_SELECT) . "\n"; } $condition .= " AND (" . $searchCondition . ")"; } return $condition; }
/** * Load tool action list value of a profile */ public function loadToolActionList() { // load all action for this profile type $sql = " SELECT A.id, A.name, A.tool_id, CT.claro_label\n FROM `" . $this->tbl['action'] . "` `A`,\n `" . $this->tbl['course_tool'] . "` `CT`\n WHERE type = '" . claro_sql_escape($this->profile->type) . "'\n AND A.tool_id = CT.id\n ORDER BY CT.def_rank"; $actionResult = claro_sql_query_fetch_all($sql); // initialise all tool action foreach ($actionResult as $action) { $toolId = $action['tool_id']; $actionName = $action['name']; $this->toolActionList[$toolId][$actionName] = false; } // load value of action $sql = " SELECT PA.action_id, PA.value, A.tool_id, A.name\n FROM `" . $this->tbl['rel_profile_action'] . "` `PA`,\n `" . $this->tbl['action'] . "` `A`\n WHERE PA.profile_id = " . $this->profile->id . "\n AND PA.action_id = A.id\n AND PA.courseId = ''"; $action_list = claro_sql_query_fetch_all($sql); // load all actions value for the profile foreach ($action_list as $this_action) { $actionName = $this_action['name']; $actionValue = (bool) $this_action['value']; $toolId = $this_action['tool_id']; if (isset($this->toolActionList[$toolId][$actionName])) { $this->toolActionList[$toolId][$actionName] = $actionValue; } } }
/** * 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; }
/** * save object in db * * @author Sebastien Piraux <*****@*****.**> * @return boolean result of operation */ public function save() { $sqlGradeList = implode(',', $this->gradeList); $sqlWrongAnswerList = implode(',', $this->wrongAnswerList); if ($this->id == -1) { // insert $sql = "INSERT INTO `" . $this->tblAnswer . "`\n SET `questionId` = " . (int) $this->questionId . ",\n `answer` = '" . claro_sql_escape($this->answerText) . "',\n `gradeList` = '" . claro_sql_escape($sqlGradeList) . "',\n `wrongAnswerList` = '" . claro_sql_escape($sqlWrongAnswerList) . "',\n `type` = " . (int) $this->type; // execute the creation query and get id of inserted assignment $insertedId = claro_sql_query_insert_id($sql); if ($insertedId) { $this->id = (int) $insertedId; return $this->id; } else { return false; } } else { // update $sql = "UPDATE `" . $this->tblAnswer . "`\n SET `answer` = '" . claro_sql_escape($this->answerText) . "',\n `gradeList` = '" . claro_sql_escape($sqlGradeList) . "',\n `wrongAnswerList` = '" . claro_sql_escape($sqlWrongAnswerList) . "',\n `type` = " . (int) $this->type . "\n WHERE `id` = " . (int) $this->id; // execute and return main query if (claro_sql_query($sql)) { return $this->id; } else { return false; } } }
switch (claro_failure::get_last_failure()) { case 'BASE_DIR_DONT_EXIST': pushClaroMessage($searchBasePath . ' : call to an unexisting directory in groups'); break; default: pushClaroMessage('Search failed'); break; } // TODO claro_search_file would return an empty array when failed $filePathList = array(); } for ($i = 0; $i < count($filePathList); $i++) { $filePathList[$i] = str_replace($baseWorkDir, '', $filePathList[$i]); } if ($cmd == 'exSearch' && $courseContext) { $sql = "SELECT path FROM `" . $dbTable . "`\n WHERE comment LIKE '%" . claro_sql_escape($searchPatternSql) . "%'"; $dbSearchResult = claro_sql_query_fetch_all_cols($sql); if (!$is_allowedToEdit) { for ($i = 0; $i < count($searchExcludeList); $i++) { for ($j = 0; $j < count($dbSearchResult['path']); $j++) { if (preg_match('|^' . $searchExcludeList[$i] . '|', $dbSearchResult['path'][$j])) { unset($dbSearchResult['path'][$j]); } } } } $filePathList = array_unique(array_merge($filePathList, $dbSearchResult['path'])); } $fileList = array(); if (count($filePathList) > 0) { /*--------------------------------------------------------------------------
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; } }
claro_die('ERROR : COURSE NOT FOUND!!!'); } //---------------------------------- // Build query and find info in db //---------------------------------- $sql = "\nSELECT\n U.nom, U.prenom, U.`user_id` AS ID,\n CU.*,\n CU.`user_id` AS Register\nFROM `" . $tbl_user . "` AS U"; $toAdd = "\nLEFT JOIN `" . $tbl_course_user . "` AS CU\n ON CU.`user_id`=U.`user_id`\n AND CU.`code_cours` = '" . claro_sql_escape($cidToEdit) . "'\n "; $sql .= $toAdd; //deal with LETTER classification call if (isset($_GET['letter'])) { $toAdd = "\n AND U.`nom` LIKE '" . claro_sql_escape($_GET['letter']) . "%' "; $sql .= $toAdd; } //deal with KEY WORDS classification call if (isset($_REQUEST['search']) && $_REQUEST['search'] != '') { $toAdd = " WHERE (U.`nom` LIKE '" . claro_sql_escape($_REQUEST['search']) . "%'\n OR U.`username` LIKE '" . claro_sql_escape($_REQUEST['search']) . "%'\n OR U.`prenom` LIKE '" . claro_sql_escape($_REQUEST['search']) . "%') "; $sql .= $toAdd; } // deal with REORDER //first see is direction must be changed if (isset($_REQUEST['chdir']) && $_REQUEST['chdir'] == 'yes') { if ($_SESSION['admin_register_dir'] == 'ASC') { $_SESSION['admin_register_dir'] = 'DESC'; } else { $_SESSION['admin_register_dir'] = 'ASC'; } } if (isset($_SESSION['admin_register_order_crit'])) { if ($_SESSION['admin_register_order_crit'] == 'user_id') { $toAdd = " ORDER BY `U`.`user_id` " . $_SESSION['admin_register_dir']; } else {
/** * Move tracking data from old tables to new ones. * * @return upgrade status */ function upgrade_main_database_tracking_data_to_19() { $tbl_mdb_names = claro_sql_get_main_tbl(); $tool = 'MAIN_TRACKING_DATA_19'; switch ($step = get_upgrade_status($tool)) { case 1: // drop id to be able to recreate it with correct autoincrement values at last step $sql = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` DROP `id`"; if (upgrade_sql_query($sql)) { $step = set_upgrade_status($tool, $step + 1); } else { return $step; } unset($sql); case 2: // get total number of rows in track_e_login $sql = "SELECT COUNT(*)\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`"; $tableRows = (int) claro_sql_query_fetch_single_value($sql); $recoveredOffset = UpgradeTrackingOffset::retrieve(); // get a subgroup of 250 rows and insert group by group in tracking_event table for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) { // we have to store offset to start again from it if something failed UpgradeTrackingOffset::store($offset); $query = "SELECT `login_id`, `login_user_id`, `login_date`, `login_ip`\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`\n ORDER BY `login_date`, `login_id`\n LIMIT " . $offset . ", 250"; // then copy these 250 rows to tracking_event $eventList = claro_sql_query_fetch_all_rows($query); // build query to insert all 250 rows $sql = "INSERT INTO `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "tracking_event`\n ( `user_id`, `date`, `type`, `data` )\n VALUES\n "; //inject former data into new table structure foreach ($eventList as $event) { $sql .= "(" . (int) $event['login_user_id'] . ",'" . claro_sql_escape($event['login_date']) . "','user_login','" . claro_sql_escape(serialize(array('ip' => $event['login_ip']))) . "'),\n"; } unset($eventList); if (upgrade_sql_query(rtrim($sql, ",\n"))) { unset($sql); //continue; } else { return $step; } } UpgradeTrackingOffset::reset(); $step = set_upgrade_status($tool, $step + 1); case 3: // get total number of rows in track_e_login $sql = "SELECT COUNT(*)\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`"; $tableRows = (int) claro_sql_query_fetch_single_value($sql); $recoveredOffset = UpgradeTrackingOffset::retrieve(); // get a subgroup of 250 rows and insert group by group in tracking_event table for ($offset = $recoveredOffset; $offset < $tableRows; $offset += 250) { // we have to store offset to start again from it if something failed UpgradeTrackingOffset::store($offset); $query = "SELECT `open_id`, `open_date`\n FROM `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`\n ORDER BY `open_date`, `open_id`\n LIMIT " . $offset . ", 250"; // then copy these 250 rows to tracking_event $eventList = claro_sql_query_fetch_all_rows($query); // build query to insert all 250 rows $sql = "INSERT INTO `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "tracking_event`\n ( `user_id`, `date`, `type`, `data` )\n VALUES\n "; //inject former data into new table structure foreach ($eventList as $event) { $sql .= "(NULL,'" . claro_sql_escape($event['open_date']) . "','platform_access',''),\n"; } unset($eventList); if (upgrade_sql_query(rtrim($sql, ",\n"))) { unset($sql); //continue; } else { return $step; } } UpgradeTrackingOffset::reset(); $step = set_upgrade_status($tool, $step + 1); case 4: // order table using dates then recreate primary key with correct autoincrement value $sqlForUpdate[] = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` ORDER BY `date`"; $sqlForUpdate[] = "ALTER TABLE `" . $tbl_mdb_names['tracking_event'] . "` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST"; if (upgrade_apply_sql($sqlForUpdate)) { $step = set_upgrade_status($tool, $step + 1); } else { return $step; } case 5: //drop deprecated tracking tables $sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_open`"; $sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf('statsDbName') . '`.`' . get_conf('statsTblPrefix') . "track_e_login`"; // we should probably keep this table as it may be usefull for history purpose. By the way it is not used in // any tracking interface. //$sqlForUpdate[] = "DROP TABLE IF EXISTS `" . get_conf( 'mainTblPrefix' ) . "track_e_default`"; if (upgrade_apply_sql($sqlForUpdate)) { $step = set_upgrade_status($tool, $step + 1); } else { return $step; } unset($sqlForUpdate); default: $step = set_upgrade_status($tool, 0); return $step; } return false; }
/** * Save the file currentVersion.inc.php * * @param string course code * @param string claroline version * @param string database version * * @since 1.7 */ function save_course_current_version($course_code, $fileVersion) { $tbl_mdb_names = claro_sql_get_main_tbl(); // query to update version of course $sql = " UPDATE `" . $tbl_mdb_names['course'] . "`\n SET versionClaro = '" . claro_sql_escape($fileVersion) . "'\n WHERE code = '" . $course_code . "'"; return claro_sql_query($sql); }
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); }
$sql = "SELECT `user_id` `uid` ,\n `nom` `lastName` ,\n `prenom` `firstName` ,\n `username` `loginName` ,\n `password` ,\n `email` ,\n `authSource` ,\n `creatorId`\n FROM `" . $tbl_user . "`\n WHERE LOWER(email) = '" . claro_sql_escape($emailTo) . "'"; $userList = claro_sql_query_fetch_all($sql); 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 */
/** * 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); } }
/** * Prepares the sql request to select courses in database. * * @return string $sql */ function prepare_get_filtred_course_list() { $tbl_mdb_names = claro_sql_get_main_tbl(); $sqlFilter = array(); // Prepare filter deal with KEY WORDS classification call if (isset($_SESSION['admin_course_search'])) { $sqlFilter[] = "( co.`intitule` LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_course_search'])) . "%'" . "\n" . " OR co.`administrativeNumber` LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_course_search'])) . "%'" . "\n" . ")"; } // Deal with ADVANCED SEARCH parmaters call if (isset($_SESSION['admin_course_intitule']) && !empty($_SESSION['admin_course_intitule'])) { $sqlFilter[] = "(co.`intitule` LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_course_intitule'])) . "%')"; } if (isset($_SESSION['admin_course_code']) && !empty($_SESSION['admin_course_code'])) { $sqlFilter[] = "(co.`administrativeNumber` LIKE '%" . claro_sql_escape(pr_star_replace($_SESSION['admin_course_code'])) . "%')"; } if (isset($_SESSION['admin_course_language'])) { $sqlFilter[] = "(co.`language` = '" . claro_sql_escape($_SESSION['admin_course_language']) . "')"; } if (isset($_SESSION['admin_course_visibility'])) { if ($_SESSION['admin_course_visibility'] == 'invisible') { $sqlFilter[] = "co.`visibility`='INVISIBLE'"; } elseif ($_SESSION['admin_course_visibility'] == 'visible') { $sqlFilter[] = "co.`visibility`='VISIBLE'"; } } if (isset($_SESSION['admin_course_access'])) { if ($_SESSION['admin_course_access'] == 'public') { $sqlFilter[] = "co.`access`='public'"; } elseif ($_SESSION['admin_course_access'] == 'private') { $sqlFilter[] = "co.`access`='private'"; } elseif ($_SESSION['admin_course_access'] == 'platform') { $sqlFilter[] = "co.`access`='platform'"; } } if (isset($_SESSION['admin_course_subscription'])) { if ($_SESSION['admin_course_subscription'] == 'allowed') { $sqlFilter[] = "co.`registration`='OPEN'"; } elseif ($_SESSION['admin_course_subscription'] == 'denied') { $sqlFilter[] = "co.`registration`='CLOSE'"; } elseif ($_SESSION['admin_course_subscription'] == 'key') { $sqlFilter[] = "co.`registration`='OPEN' AND CHAR_LENGTH(co.`registrationKey`) != 0"; } } // Create the WHERE clauses $sqlFilter = sizeof($sqlFilter) ? "WHERE " . implode(" AND ", $sqlFilter) : ""; // Build the complete SQL request $sql = "SELECT co.`cours_id` AS `id`, " . "\n" . "co.`administrativeNumber` AS `officialCode`, " . "\n" . "co.`intitule` AS `intitule`, " . "\n" . "co.`code` AS `sysCode`, " . "\n" . "co.`sourceCourseId` AS `sourceCourseId`, " . "\n" . "co.`isSourceCourse` AS `isSourceCourse`, " . "\n" . "co.`visibility` AS `visibility`, " . "\n" . "co.`access` AS `access`, " . "\n" . "co.`registration` AS `registration`, " . "\n" . "co.`registrationKey` AS `registrationKey`, " . "\n" . "co.`directory` AS `repository`, " . "\n" . "co.`status` AS `status` " . "\n" . "FROM `" . $tbl_mdb_names['course'] . "` AS co " . "\n" . $sqlFilter; return $sql; }
} // set credit if needed if ($lesson_status_value == "COMPLETED" || $lesson_status_value == "PASSED") { if (strtoupper($_POST['credit']) == "CREDIT") { $credit_value = "CREDIT"; } } if (isScormTime($_POST['session_time'])) { $total_time_value = addScormTime($_POST['total_time'], $_POST['session_time']); } else { $total_time_value = $_POST['total_time']; } $sql = "UPDATE `" . $TABLEUSERMODULEPROGRESS . "` \n SET \n `lesson_location` = '" . claro_sql_escape($_POST['lesson_location']) . "',\n `lesson_status` = '" . claro_sql_escape($lesson_status_value) . "',\n `entry` = '" . claro_sql_escape($entry_value) . "',\n `raw` = '" . (int) $_POST['raw'] . "',\n `scoreMin` = '" . (int) $_POST['scoreMin'] . "',\n `scoreMax` = '" . (int) $_POST['scoreMax'] . "',\n `total_time` = '" . claro_sql_escape($total_time_value) . "',\n `session_time` = '" . claro_sql_escape($_POST['session_time']) . "',\n `suspend_data` = '" . claro_sql_escape($_POST['suspend_data']) . "',\n `credit` = '" . claro_sql_escape($credit_value) . "'\n WHERE `user_module_progress_id` = " . (int) $_POST['ump_id']; claro_sql_query($sql); // Generate an event to notify that the module tracking has been updated $learnPathEventArgs = array('userId' => (int) claro_get_current_user_id(), 'courseCode' => claro_get_current_course_id(), 'scoreRaw' => (int) $_POST['raw'], 'scoreMin' => (int) $_POST['scoreMin'], 'scoreMax' => (int) $_POST['scoreMax'], 'sessionTime' => claro_sql_escape($_POST['session_time']), 'userModuleProgressId' => (int) $_POST['ump_id'], 'type' => "update", 'status' => claro_sql_escape($lesson_status_value)); $learnPathEvent = new Event('lp_user_module_progress_modified', $learnPathEventArgs); EventManager::notify($learnPathEvent); } // display the form to accept new commit and // refresh TOC frame, has to be done here to show recorded progression as soon as it is recorded ?> <!-- API form --> <html> <head> <title>update progression</title> <?php if ($_POST['ump_id']) { ?> <script type="text/javascript">
/** * to know if user is registered to a course or not * * @copyright (c) 2001-2011, Universite catholique de Louvain (UCL) * @param int id of user in DB * @param int id of course in DB * @return boolean true if user is enrolled false otherwise */ function is_registered_to($user_id, $course_id) { $tbl_mdb_names = claro_sql_get_main_tbl(); $tbl_rel_course_user = $tbl_mdb_names['rel_course_user']; $sql = "SELECT count(*) `user_reg`\n FROM `" . $tbl_rel_course_user . "`\n WHERE `code_cours` = '" . claro_sql_escape($course_id) . "' AND `user_id` = '" . (int) $user_id . "'"; $res = claro_sql_query_fetch_all($sql); return (bool) ($res[0]['user_reg'] > 0); }
/** * Update an 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 $visible_from * @param date $visible_until * @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 handler of query * @since 1.7 * @todo convert to param date timestamp */ function announcement_update_item($announcement_id, $title = null, $content = null, $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)); $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"; $sqlSet = array(); if (!is_null($title)) { $sqlSet[] = " title = '" . claro_sql_escape(trim($title)) . "' "; } if (!is_null($content)) { $sqlSet[] = " contenu = '" . claro_sql_escape(trim($content)) . "' "; } if (!is_null($content)) { $sqlSet[] = " visibleFrom = " . $visible_from . " "; } if (!is_null($content)) { $sqlSet[] = " visibleUntil = " . $visible_until . " "; } if (!is_null($visibility)) { $sqlSet[] = " visibility = '" . $visibility . "' "; } if (!is_null($time)) { $sqlSet[] = " temps = from_unixtime('" . (int) $time . "') "; } if (count($sqlSet) > 0) { $sql = "UPDATE `" . $tbl['announcement'] . "`\n SET " . implode(', ', $sqlSet) . "\n WHERE id='" . (int) $announcement_id . "'"; return claro_sql_query($sql); } else { return null; } }
} } } /*--------------------------------------------------------------------------- Course / tool relation initialisation ---------------------------------------------------------------------------*/ // if the requested tool is different from the current tool in session // (special request can come from the tool id, or the tool label) if ($tidReq && $tidReq != $_SESSION['_tid'] || $tlabelReq && (!isset($_SESSION['_courseTool']['label']) || $tlabelReq != $_SESSION['_courseTool']['label'])) { $tidReset = true; } if ($tidReset || $cidReset) { if (($tidReq || $tlabelReq) && $_cid) { $tbl_mdb_names = claro_sql_get_main_tbl(); $tbl_tool = $tbl_mdb_names['tool']; $sql = " SELECT ctl.id AS id ,\n pct.id AS toolId ,\n pct.claro_label AS label ,\n ctl.script_name AS name ,\n ctl.visibility AS visibility ,\n pct.icon AS icon ,\n pct.access_manager AS access_manager,\n pct.script_url AS url\n\n FROM `" . $_course['dbNameGlu'] . "tool_list` ctl,\n `" . $tbl_tool . "` pct\n\n WHERE `ctl`.`tool_id` = `pct`.`id`\n AND (`ctl`.`id` = '" . (int) $tidReq . "'\n OR (" . (int) is_null($tidReq) . " AND pct.claro_label = '" . claro_sql_escape($tlabelReq) . "')\n )"; // Note : 'ctl' stands for 'course tool list' and 'pct' for 'platform course tool' $_courseTool = claro_sql_query_get_single_row($sql); if (is_array($_courseTool)) { $_tid = $_courseTool['id']; $_mainToolId = $_courseTool['toolId']; } else { $activatedModules = get_module_label_list(true); if (!in_array($tlabelReq, $activatedModules)) { exit('WARNING !! Undefined Tlabel or Tid: your script declare ' . 'be a tool wich is not registred at line ' . __LINE__ . '. ' . 'Please contact your platform administrator.'); } else { $_tid = null; $_mainToolId = null; $_courseTool = null; } }
if (!claro_is_in_a_course() || !claro_is_course_allowed()) { claro_disp_auth_form(true); } if (isset($_REQUEST['searchUser'])) { $sqlClauseString = ' p.poster_id = ' . (int) $_REQUEST['searchUser']; } elseif (isset($_REQUEST['searchPattern'])) { $searchPatternString = trim($_REQUEST['searchPattern']); if ($searchPatternString != '') { $searchPatternList = explode(' ', $searchPatternString); $sqlClauseList = ''; foreach ($searchPatternList as $thisSearchPattern) { $thisSearchPattern = str_replace('_', '\\_', $thisSearchPattern); $thisSearchPattern = str_replace('%', '\\%', $thisSearchPattern); $thisSearchPattern = str_replace('?', '_', $thisSearchPattern); $thisSearchPattern = str_replace('*', '%', $thisSearchPattern); $sqlClauseList[] = " pt.post_text LIKE '%" . claro_sql_escape($thisSearchPattern) . "%'\n OR p.nom LIKE '%" . claro_sql_escape($thisSearchPattern) . "%'\n OR p.prenom LIKE '%" . claro_sql_escape($thisSearchPattern) . "%'\n OR t.topic_title LIKE '%" . claro_sql_escape($thisSearchPattern) . "%'"; } $sqlClauseString = implode("\n OR \n", $sqlClauseList); } else { $sqlClauseString = null; } } else { $sqlClauseString = null; } if ($sqlClauseString) { $tbl_cdb_names = claro_sql_get_course_tbl(); $tbl_posts_text = $tbl_cdb_names['bb_posts_text']; $tbl_posts = $tbl_cdb_names['bb_posts']; $tbl_topics = $tbl_cdb_names['bb_topics']; $tbl_forums = $tbl_cdb_names['bb_forums']; $sql = "SELECT pt.post_id,\n pt.post_text,\n p.nom AS lastname,\n p.prenom AS firstname,\n p.`poster_id`,\n p.post_time,\n t.topic_id,\n t.topic_title,\n f.forum_id,\n f.forum_name,\n f.group_id\n FROM `" . $tbl_posts_text . "` AS pt,\n `" . $tbl_posts . "` AS p,\n `" . $tbl_topics . "` AS t,\n `" . $tbl_forums . "` AS f\n WHERE ( " . $sqlClauseString . ")\n AND pt.post_id = p.post_id\n AND p.topic_id = t.topic_id\n AND t.forum_id = f.forum_id\n ORDER BY p.post_time DESC, t.topic_id";
/** * Record result of user when an exercice was done * @param exerciseTrackId id in qwz_tracking table * @param questionId id of the question * @param values array with user answers * @param questionResult result of this question * * @author Sebastien Piraux <*****@*****.**> */ function track_exercise_details($exerciseTrackId, $questionId, $values, $questionResult) { // get table names $tblList = get_module_course_tbl(array('qwz_tracking_questions', 'qwz_tracking_answers'), claro_get_current_course_id()); $tbl_qwz_tracking_questions = $tblList['qwz_tracking_questions']; $tbl_qwz_tracking_answers = $tblList['qwz_tracking_answers']; // add the answer tracking informations $sql = "INSERT INTO `" . $tbl_qwz_tracking_questions . "`\n SET `exercise_track_id` = " . (int) $exerciseTrackId . ",\n `question_id` = '" . (int) $questionId . "',\n `result` = '" . (double) $questionResult . "'"; $details_id = claro_sql_query_insert_id($sql); // check if previous query succeed to add answers if ($details_id && is_array($values)) { // add, if needed, the different answers of the user // one line by answer // each entry of $values should be correctly formatted depending on the question type foreach ($values as $answer) { $sql = "INSERT INTO `" . $tbl_qwz_tracking_answers . "`\n SET `details_id` = " . (int) $details_id . ",\n `answer` = '" . claro_sql_escape($answer) . "'"; claro_sql_query($sql); } } return 1; }
/** * Fetch data and privileges of the given user in the given course * * U don't have enough of this function * use claro_get_course_user_data($cid,$uid,$ignoreCache=false) * or claro_get_course_user_privilege($cid,$uid,$ignoreCache=false) * * @param string $cid course id * @param integer $uid user id * @param bool $ignoreCache true to for read in database instead of cache * @return array(data( array('role')), 'privilege'(array('_profileId','is_courseMember','is_courseTutor','is_courseAdmin'))) * @see claro_get_course_user_data($cid,$uid,$ignoreCache=false) * @see claro_get_course_user_privilege($cid,$uid,$ignoreCache=false) * @author Christophe Gesche <*****@*****.**> */ function claro_get_course_user_properties($cid, $uid, $ignoreCache = false) { $admin = claro_is_platform_admin(); $tbl_mdb_names = claro_sql_get_main_tbl(); $tbl_rel_course_user = $tbl_mdb_names['rel_course_user']; static $course_user_cache = null; static $course_user_data = null; static $course_user_privilege = array(); if ($course_user_cache != array('uid' => $uid, 'cid' => $cid) || $ignoreCache) { $sql = "SELECT profile_id AS profileId,\n isCourseManager,\n isPending,\n tutor,\n role\n FROM `" . $tbl_rel_course_user . "` `cours_user`\n WHERE `user_id` = '" . (int) $uid . "'\n AND `code_cours` = '" . claro_sql_escape($cid) . "'"; $cuData = claro_sql_query_get_single_row($sql); if (!empty($cuData)) { $course_user_data['role'] = $cuData['role']; // not used $course_user_privilege['_profileId'] = $cuData['profileId']; $course_user_privilege['is_coursePending'] = (bool) $cuData['isPending']; $course_user_privilege['is_courseMember'] = (bool) ($cuData['isPending'] == 0); $course_user_privilege['is_courseTutor'] = (bool) ($cuData['tutor'] == 1); $course_user_privilege['is_courseAdmin'] = (bool) ($cuData['isCourseManager'] == 1); } else { $course_user_privilege['_profileId'] = claro_get_profile_id('guest'); $course_user_privilege['is_coursePending'] = false; $course_user_privilege['is_courseMember'] = false; $course_user_privilege['is_courseAdmin'] = false; $course_user_privilege['is_courseTutor'] = false; $course_user_data = null; // not used } $course_user_privilege['is_courseAdmin'] = (bool) ($course_user_privilege['is_courseAdmin'] || claro_is_platform_admin()); $course_user_cache = array('uid' => $uid, 'cid' => $cid); } return array('data' => $course_user_data, 'privilege' => $course_user_privilege); }
if (get_conf('useSendfile', true) && ($mimeType != 'text/html' || $extension == 'url') || $wasFolder) { if (claro_send_file($pathInfo) !== false) { $claroline->notifier->event('download', array('data' => array('url' => $document_url))); if ($wasFolder) { unlink($pathInfo); } if (!$canRetry) { $sql = 'DELETE FROM `' . $tableName . '` WHERE token = \'' . claro_sql_escape($token) . '\''; Claroline::getDatabase()->exec($sql); } } else { header('HTTP/1.1 404 Not Found'); claro_die(get_lang('File download failed : %failureMSg%', array('%failureMsg%' => claro_failure::get_last_failure()))); } } else { $sql = 'DELETE FROM `' . $tableName . '` WHERE token = \'' . claro_sql_escape($token) . '\''; Claroline::getDatabase()->exec($sql); // redirect to document claro_redirect($document_url); } } else { header('HTTP/1.1 404 Not Found'); } //Clean left zip here $sql = 'SELECT * FROM `' . $tableName . '` WHERE ADDTIME(`requestTime`,\'0 0:0:30\') < NOW() AND NOT `wasFolder` = \'0\''; $result = Claroline::getDatabase()->query($sql); while (($row = $result->fetch()) !== false) { if (is_file($row['requestedPath'])) { unlink($row['requestedPath']); } }
$feedbackConditionList[] = "`fb`.`original_id` = " . (int) claro_get_current_user_id(); } } $submissionFilterSql = implode(' OR ', $submissionConditionList); if (!empty($submissionFilterSql)) { $submissionFilterSql = ' AND (' . $submissionFilterSql . ') '; } $feedbackFilterSql = implode(' OR ', $feedbackConditionList); if (!empty($feedbackFilterSql)) { $feedbackFilterSql = ' AND (' . $feedbackFilterSql . ')'; } if ($assignment->getAssignmentType() == 'INDIVIDUAL') { if (!$is_allowedToEditAll) { $showOnlyVisibleCondition = " HAVING `submissionCount` > 0"; } $sql = "SELECT `u`.`user_id` AS `authId`,\n CONCAT(`u`.`nom`, ' ', `u`.`prenom`) AS `name`,\n `s`.`title`,\n COUNT(DISTINCT(`s`.`id`)) AS `submissionCount`,\n COUNT(DISTINCT(`fb`.`id`)) AS `feedbackCount`,\n MAX(`fb`.`score`) AS `maxScore`,\n MAX(`s`.`last_edit_date`) AS `last_edit_date`\n\n #GET USER LIST\n FROM `" . $tbl_user . "` AS `u`\n\n #ONLY FROM COURSE\n INNER JOIN `" . $tbl_rel_course_user . "` AS `cu`\n ON `u`.`user_id` = `cu`.`user_id`\n AND `cu`.`code_cours` = '" . claro_sql_escape(claro_get_current_course_id()) . "'\n\n # SEARCH ON SUBMISSIONS\n LEFT JOIN `" . $tbl_wrk_submission . "` AS `s`\n ON ( `s`.`assignment_id` = " . (int) $req['assignmentId'] . " OR `s`.`assignment_id` IS NULL)\n AND `s`.`user_id` = `u`.`user_id`\n AND `s`.`original_id` IS NULL\n " . $submissionFilterSql . "\n\n # SEARCH ON FEEDBACKS\n LEFT JOIN `" . $tbl_wrk_submission . "` as `fb`\n ON `fb`.`parent_id` = `s`.`id`\n " . $feedbackFilterSql . "\n\n GROUP BY `u`.`user_id`,\n `s`.`original_id`\n " . $showOnlyVisibleCondition; if (isset($_GET['sort']) && isset($_GET['dir'])) { $sortKeyList[$_GET['sort']] = $_GET['dir']; } elseif (isset($_GET['sort']) && isset($_GET['dir'])) { $sortKeyList[$_GET['sort']] = SORT_ASC; } if (!isset($sortKeyList['submissionCount'])) { $sortKeyList['submissionCount'] = SORT_DESC; } $sortKeyList['s.last_edit_date'] = SORT_DESC; $sortKeyList['fb.last_edit_date'] = SORT_DESC; $sortKeyList['cu.isCourseManager'] = SORT_ASC; $sortKeyList['cu.tutor'] = SORT_DESC; $sortKeyList['u.nom'] = SORT_ASC; $sortKeyList['u.prenom'] = SORT_ASC; // get last submission titles
/** * edit a bloc for information category * * @param integer $def_id, * @param integer $user_id, * @param string $user_ip, DEFAULT $REMOTE_ADDR * @param string $content ; if empty call delete the bloc * @return boolean true if succeed, else bolean false */ function claro_user_info_edit_cat_content($def_id, $user_id, $content = "", $user_ip = "", $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 (empty($user_ip)) { global $REMOTE_ADDR; $user_ip = $REMOTE_ADDR; } if (0 == (int) $user_id || 0 == (int) $def_id) { return claro_failure::set_failure('id_nul'); } $content = trim($content); if ('' == trim($content)) { return claro_user_info_cleanout_cat_content($user_id, $def_id); } $sql = "UPDATE `" . $tbl_userinfo_content . "` SET\n `content` = '" . claro_sql_escape($content) . "',\n `ed_ip` = '" . $user_ip . "',\n `ed_date` = now()\n WHERE def_id = " . (int) $def_id . "\n AND user_id = " . (int) $user_id; claro_sql_query($sql); return true; }
/** * Rewrite the SQL query to allowing paging. It adds LIMIT parameter to the * end of the query end SQL_CALC_FOUND_ROWS between the SELECT statement * and the column list * * @access private * @param string $sql current SQL query * @param int $offset requested offset * @param int $step current step paging * @return string the rewrote query */ function _get_prepared_query($sql, $offset, $step, $sortKeyList) { if (count($sortKeyList) > 0) { $orderByList = array(); foreach ($sortKeyList as $thisSortKey => $thisSortDirection) { if ($thisSortDirection == SORT_DESC) { $direction = 'DESC'; } elseif ($thisSortDirection == SORT_ASC) { $direction = 'ASC'; } else { $direction = ''; } $orderByList[] = claro_sql_escape($thisSortKey) . ' ' . $direction; } $sql .= "\n\t" . 'ORDER BY ' . implode(', ', $orderByList); } if ($step > 0) { // Include SQL_CALC_FOUND_ROWS inside the query // This mySQL clause permit to know how many rows the statement // would have returned with no LIMIT clause, without running the // statement again. To retrieve this rows count, one invokes // FOUND_ROWS() afterward (see get_total_result_count method). $sql = substr_replace($sql, 'SELECT SQL_CALC_FOUND_ROWS ', 0, strlen('SELECT ')) . "\n\t" . ' LIMIT ' . $offset . ', ' . $step; } return $sql; }
/** * This function empties all classes, * remove link between classes and users * delete related users from related courses */ function empty_all_class() { $tbl = claro_sql_get_main_tbl(); $sql = "\n SELECT id FROM `" . $tbl['class'] . "`"; $searchResultList = claro_sql_query_fetch_all($sql); foreach ($searchResultList as $thisClass) { $classId = $thisClass['id']; // find all the students enrolled in that class $sql2 = "\n SELECT user_id from `" . $tbl['rel_class_user'] . "`\n WHERE class_id = '" . claro_sql_escape($classId) . "'"; $thisClassUser = claro_sql_query_fetch_all($sql2); // Find all the courses to whom the class is enrolled $sql2 = "\n SELECT courseId\n FROM `" . $tbl['rel_course_class'] . "`\n WHERE classId = '" . claro_sql_escape($classId) . "'"; $searchResultList2 = claro_sql_query_fetch_all($sql2); foreach ($searchResultList2 as $thisCourse) { $courseCode = $thisCourse['courseId']; foreach ($thisClassUser as $thisUser) { $user_id = $thisUser['user_id']; if (!user_remove_from_course($user_id, $courseCode, false, false, $classId)) { return claro_failure::set_failure('PROBLEM_WITH_COURSE_UNSUSCRIBTION ' . $user_id . ' ' . $courseCode); //TODO : ameliorer la detection d'erreur } } } class_remove_all_users($classId); } return true; }