function reports_template_exercicesMultiCourses_getSQL() { // foreach quiz $result = array(); $columns = Database::query('select r.id as kid, c.title as course, ' . 'r.child_name as test from ' . Database::get_main_table(TABLE_MAIN_REPORTS_KEYS) . ' r, ' . Database::get_main_table(TABLE_MAIN_COURSE) . ' c ' . 'where r.course_id=c.id and r.tool_id=' . reports_getToolId(TOOL_QUIZ) . ' order by r.course_id, r.child_name'); if (Database::num_rows($columns) == 0) { die('<b>' . get_lang('no data found') . '</b>'); } $query = 'select u.lastname Name, u.firstname Firstname'; $columns = Database::store_result($columns); if ($_REQUEST['tattempt'] == 'min' || $_REQUEST['tattempt'] == 'max') { $function = $_REQUEST['tattempt']; } else { $function = 'avg'; } foreach ($columns as $key => $column) { $query .= ', ' . $function . '(k' . $key . '.score) as `' . $column['course'] . ' - ' . $column['test'] . '` '; } $query .= ' from ' . Database::get_main_table(TABLE_MAIN_USER) . ' u '; foreach ($columns as $key => $column) { // fixme sessions $query .= 'left outer join ' . Database::get_main_table(TABLE_MAIN_REPORTS_VALUES) . ' k' . $key . ' on k' . $key . '.key_id = ' . $column['kid'] . ' and k' . $key . '.user_id = u.user_id '; } $query .= ' group by '; foreach ($columns as $key => $column) { // grouping attempt $query .= 'k' . $key . '.attempt, '; } $query = substr($query, 0, -2); // removing last ', '; return $query; }
/** * * @param type $course_code * @param type $session_id * @return type * @assert ('') === false */ public static function get_surveys($course_code, $session_id = 0) { $table_survey = Database::get_course_table(TABLE_SURVEY); if (empty($course_code)) { return false; } $course_info = api_get_course_info($course_code); $session_condition = api_get_session_condition($session_id, true, true); $sql = "SELECT * FROM {$table_survey} WHERE c_id = {$course_info['real_id']} {$session_condition} "; $result = Database::query($sql); $result = Database::store_result($result, 'ASSOC'); return $result; }
function reports_template_courseTime_getSQL() { // fetch columns $result = array(); $query = 'select r.id as kid, c.title as course ' . 'from ' . Database::get_main_table(TABLE_MAIN_REPORTS_KEYS) . ' r, ' . Database::get_main_table(TABLE_MAIN_COURSE) . ' c ' . 'where r.course_id=c.id and r.tool_id is null and r.child_id is null' . ' order by c.title'; $columns = Database::query($query); if (Database::num_rows($columns) == 0) { die('<b>' . get_lang('no data found: ' . $query) . '</b>'); } $columns = Database::store_result($columns); // fetch data $query = 'select u.lastname Name, u.firstname Firstname'; foreach ($columns as $key => $column) { $query .= ', sec_to_time(k' . $key . '.report_time) as `' . $column['course'] . '` '; } $query .= ' from ' . Database::get_main_table(TABLE_MAIN_USER) . ' u '; foreach ($columns as $key => $column) { // fixme sessions $query .= 'left outer join ' . Database::get_main_table(TABLE_MAIN_REPORTS_VALUES) . ' k' . $key . ' on k' . $key . '.key_id = ' . $column['kid'] . ' and k' . $key . '.user_id = u.user_id '; } return $query; }
/** * Returns users whose last login is prior from $ceiling * * @param int|string $ceiling last login date * @param bool $active_only if true returns only active users. Otherwise returns all users. * @return ResultSet */ static function listZombies($ceiling, $active_only = true, $count = 0, $from = 10, $column = 'user.firstname', $direction = 'desc') { if (empty($column)) { $column = 'user.firstname'; } $ceiling = is_numeric($ceiling) ? (int) $ceiling : strtotime($ceiling); $ceiling = date('Y-m-d H:i:s', $ceiling); $user_table = Database::get_main_table(TABLE_MAIN_USER); $login_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); $sql = 'SELECT user.user_id, user.firstname, user.lastname, user.username, user.auth_source, user.email, user.status, user.registration_date, user.active, access.login_date'; if (api_is_multiple_url_enabled()) { $access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $current_url_id = api_get_current_access_url_id(); $sql .= " FROM {$user_table} as user, {$login_table} as access, {$access_url_rel_user_table} as url\n WHERE\n access.login_date = (SELECT MAX(a.login_date)\n FROM {$login_table} as a\n WHERE a.login_user_id = user.user_id\n ) AND\n access.login_date <= '{$ceiling}' AND\n user.user_id = access.login_user_id AND\n url.user_id = user.user_id AND url.access_url_id={$current_url_id}"; } else { $sql .= " FROM {$user_table} as user, {$login_table} as access\n WHERE\n access.login_date = (SELECT MAX(a.login_date)\n FROM {$login_table} as a\n WHERE a.login_user_id = user.user_id\n ) AND\n access.login_date <= '{$ceiling}' AND\n user.user_id = access.login_user_id"; } if ($active_only) { $sql .= ' AND user.active = 1'; } $count = intval($count); $from = intval($from); $sql .= " ORDER BY {$column} {$direction}"; $sql .= " LIMIT {$count}, {$from} "; $result = Database::query($sql); return Database::store_result($result, 'ASSOC'); }
$sql = "SELECT u.user_id,lastname,firstname,username, official_code\n FROM " . $tbl_user . " u\n INNER JOIN {$tbl_user_rel_access_url} user_rel_url\n ON (user_rel_url.user_id = u.user_id)\n WHERE\n u.user_id<>2 AND\n access_url_id = {$access_url_id} AND\n (" . $target_name . " LIKE '" . $first_letter_user . "%' )\n {$where_filter}\n ORDER BY " . (count($users) > 0 ? "(u.user_id IN(" . implode(',', $users) . ")) DESC," : "") . " " . $orderBy; } } $result = Database::query($sql); $db_users = Database::store_result($result); unset($result); $sql = "SELECT code,visual_code,title\n FROM {$tbl_course}\n WHERE visual_code LIKE '" . $first_letter_course . "%'\n ORDER BY " . (count($courses) > 0 ? "(code IN('" . implode("','", $courses) . "')) DESC," : "") . " visual_code"; if (api_is_multiple_url_enabled()) { $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $sql = "SELECT code, visual_code, title\n FROM {$tbl_course} as course\n INNER JOIN {$tbl_course_rel_access_url} course_rel_url\n ON (course_rel_url.course_code= course.code)\n WHERE\n access_url_id = {$access_url_id} AND\n (visual_code LIKE '" . $first_letter_course . "%' )\n ORDER BY " . (count($courses) > 0 ? "(code IN('" . implode("','", $courses) . "')) DESC," : "") . " visual_code"; } } $result = Database::query($sql); $db_courses = Database::store_result($result); unset($result); if (api_is_multiple_url_enabled()) { $tbl_course_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $sqlNbCours = "\tSELECT course_rel_user.course_code, course.title\n FROM {$tbl_course_user} as course_rel_user\n INNER JOIN {$tbl_course} as course\n ON course.code = course_rel_user.course_code\n INNER JOIN {$tbl_course_rel_access_url} course_rel_url\n ON (course_rel_url.course_code= course.code)\n WHERE\n access_url_id = {$access_url_id} AND\n course_rel_user.user_id='" . $_user['user_id'] . "' AND\n course_rel_user.status='1'\n ORDER BY course.title"; } } ?> <form name="formulaire" method="post" action="<?php echo api_get_self(); ?> " style="margin:0px;"> <?php
/** * * Get categories by title for json calls * @param string $tag * @return array * @assert() === false */ public function get_categories_by_keyword($tag) { if (empty($tag)) { return false; } $table = Database::get_course_table(TABLE_QUIZ_CATEGORY); $sql = "SELECT iid, title, c_id FROM {$table} WHERE 1=1 "; $tag = Database::escape_string($tag); $where_condition = array(); if (!empty($tag)) { $condition = ' LIKE "%' . $tag . '%"'; $where_condition = array("title {$condition}"); $where_condition = ' AND (' . implode(' OR ', $where_condition) . ') '; } switch ($this->type) { case 'simple': $course_condition = " AND c_id = '" . api_get_course_int_id() . "' "; break; case 'global': $course_condition = " AND c_id = '0' "; break; case 'all': $course_condition = " AND c_id IN ('0', '" . api_get_course_int_id() . "')"; break; } $where_condition .= $course_condition; $order_clause = " ORDER BY title"; $sql .= $where_condition . $order_clause; $result = Database::query($sql); if (Database::num_rows($result)) { return Database::store_result($result, 'ASSOC'); } return false; }
function WSSuscribeCoursesToSession($params) { global $debug; if (!WSHelperVerifyKey($params)) { return return_error(WS_ERROR_SECRET_KEY); } if ($debug) { error_log('WSSuscribeCoursesToSession: ' . print_r($params, 1)); } // initialisation $tbl_session_rel_course_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION); $tbl_session_rel_user = Database::get_main_table(TABLE_MAIN_SESSION_USER); $tbl_session_rel_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE); $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE); $coursessessions_params = $params['coursessessions']; $results = array(); $orig_course_id_value = array(); $orig_session_id_value = array(); foreach ($coursessessions_params as $coursesession_param) { $original_session_id_value = $coursesession_param['original_session_id_value']; $original_session_id_name = $coursesession_param['original_session_id_name']; $original_course_id_name = $coursesession_param['original_course_id_name']; $original_course_id_values = $coursesession_param['original_course_id_values']; $orig_session_id_value[] = $original_session_id_value; $id_session = SessionManager::getSessionIdFromOriginalId($original_session_id_value, $original_session_id_name); if (empty($id_session)) { $results[] = 0; continue; } // Get course list from row_original_course_id_values $course_list = []; $courseCodeList = []; foreach ($original_course_id_values as $row_original_course_list) { $course_code = Database::escape_string($row_original_course_list['course_code']); $courseInfo = api_get_course_info($course_code); $courseId = $courseInfo['real_id']; $courseInfo = CourseManager::getCourseInfoFromOriginalId($course_code, $original_course_id_name); if (empty($courseInfo) || isset($courseInfo) && $courseInfo['visibility'] == 0) { $results[] = 0; continue; // Original_course_id_value doesn't exist. } $courseCodeList[] = $courseInfo['code']; $course_list[] = $courseInfo['real_id']; } if (empty($course_list)) { $results[] = 0; continue; } $orig_course_id_value[] = implode(',', $courseCodeList); // Get general coach ID $sql = "SELECT id_coach FROM {$tbl_session} WHERE id='{$id_session}'"; $id_coach = Database::query($sql); $id_coach = Database::fetch_array($id_coach); $id_coach = $id_coach[0]; // get list of courses subscribed to this session $sql = "SELECT c_id FROM {$tbl_session_rel_course} WHERE session_id ='{$id_session}'"; $rs = Database::query($sql); $existingCourses = Database::store_result($rs); $nbr_courses = count($existingCourses); // get list of users subscribed to this session $sql = "SELECT user_id FROM {$tbl_session_rel_user}\n WHERE session_id = '{$id_session}' AND relation_type<>" . SESSION_RELATION_TYPE_RRHH . ""; $result = Database::query($sql); $user_list = Database::store_result($result); $course_directory = array(); // Pass through the courses list we want to add to the session. foreach ($course_list as $enreg_course) { $enreg_course = Database::escape_string($enreg_course); $exists = false; // Check if the course we want to add is already subscribed. foreach ($existingCourses as $existingCourse) { if ($enreg_course == $existingCourse['c_id']) { $exists = true; } } if (!$exists) { // if the course isn't subscribed yet $sql = "INSERT INTO {$tbl_session_rel_course} (session_id, c_id)\n VALUES ('{$id_session}','{$enreg_course}')"; Database::query($sql); Event::addEvent(LOG_SESSION_ADD_COURSE, LOG_COURSE_ID, $enreg_course, api_get_utc_datetime(), api_get_user_id(), $enreg_course, $id_session); // We add the current course in the existing courses array, // to avoid adding another time the current course $existingCourses[] = array('c_id' => $enreg_course); $nbr_courses++; // subscribe all the users from the session to this course inside the session $nbr_users = 0; foreach ($user_list as $enreg_user) { $enreg_user_id = Database::escape_string($enreg_user['user_id']); $sql = "INSERT IGNORE INTO {$tbl_session_rel_course_rel_user} (session_id, c_id, user_id)\n VALUES ('{$id_session}','{$enreg_course}','{$enreg_user_id}')"; Event::addEvent(LOG_SESSION_ADD_USER_COURSE, LOG_USER_ID, $enreg_user_id, api_get_utc_datetime(), api_get_user_id(), $enreg_course, $id_session); $result = Database::query($sql); if (Database::affected_rows($result)) { $nbr_users++; } } $sql = "UPDATE {$tbl_session_rel_course} SET\n nbr_users = {$nbr_users}\n WHERE session_id='{$id_session}' AND c_id='{$enreg_course}'"; Database::query($sql); $sql_directory = "SELECT directory FROM {$tbl_course} WHERE id = '{$enreg_course}'"; $res_directory = Database::query($sql_directory); $row_directory = Database::fetch_row($res_directory); $course_directory[] = $row_directory[0]; } } Database::query("UPDATE {$tbl_session} SET nbr_courses={$nbr_courses} WHERE id='{$id_session}'"); $course_directory[] = $id_session; $cad_course_directory = implode(',', $course_directory); $results[] = $cad_course_directory; continue; } $count_results = count($results); $output = array(); for ($i = 0; $i < $count_results; $i++) { $output[] = array('original_course_id_values' => $orig_course_id_value[$i], 'original_session_id_value' => $orig_session_id_value[$i], 'result' => $results[$i]); } return $output; }
/** * @param int $itemId * @param int $fieldId * @param string $fieldValue * @return array|bool */ public function getAllValuesByItemAndFieldAndValue($itemId, $fieldId, $fieldValue) { $fieldId = intval($fieldId); $itemId = intval($itemId); $extraFieldType = $this->getExtraField()->getExtraFieldType(); $fieldValue = Database::escape_string($fieldValue); $sql = "SELECT s.* FROM {$this->table} s\n INNER JOIN {$this->table_handler_field} sf\n ON (s.field_id = sf.id)\n WHERE\n field_id = '{$fieldId}' AND\n item_id = '{$itemId}' AND\n value = '{$fieldValue}' AND\n sf.extra_field_type = {$extraFieldType}\n ORDER BY value"; $result = Database::query($sql); if (Database::num_rows($result)) { return Database::store_result($result, 'ASSOC'); } return false; }
/** * @param int $courseId * @param int $categoryId * @param string $name * @return array */ public static function getGroupListFilterByName($name, $categoryId, $courseId) { $name = trim($name); if (empty($name)) { return array(); } $name = Database::escape_string($name); $courseId = intval($courseId); $table_group = Database::get_course_table(TABLE_GROUP); $sql = "SELECT * FROM {$table_group}\n WHERE c_id = {$courseId} AND name LIKE '%{$name}%'"; if (!empty($categoryId)) { $categoryId = intval($categoryId); $sql .= " AND category_id = {$categoryId}"; } $sql .= " ORDER BY name"; $result = Database::query($sql); return Database::store_result($result, 'ASSOC'); }
/** * @param array $list * @return array */ function searchCategoryById($list) { if (empty($list)) { return array(); } else { $list = array_map('intval', $list); $list = implode("','", $list); } $tableCategory = Database::get_main_table(TABLE_MAIN_CATEGORY); $conditions = null; $whereCondition = null; if (self::isMultipleUrlSupport()) { $table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE_CATEGORY); $conditions = " INNER JOIN {$table} a ON (c.id = a.course_category_id)"; $whereCondition = " AND a.access_url_id = " . api_get_current_access_url_id(); } $sql = "SELECT c.*, c.name as text FROM {$tableCategory} c {$conditions}\n WHERE c.id IN {$list} {$whereCondition}"; $result = Database::query($sql); return Database::store_result($result, 'ASSOC'); }
/** * Cleans the student's results only for the Exercise tool (Not from the LP) * The LP results are NOT deleted by default, otherwise put $cleanLpTests = true * Works with exercises in sessions * @param bool $cleanLpTests * @param string $cleanResultBeforeDate * * @return int quantity of user's exercises deleted */ public function clean_results($cleanLpTests = false, $cleanResultBeforeDate = null) { $table_track_e_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES); $table_track_e_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT); $sql_where = ' AND orig_lp_id = 0 AND orig_lp_item_id = 0'; // if we want to delete results from LP too if ($cleanLpTests) { $sql_where = ""; } // if we want to delete attempts before date $cleanResultBeforeDate // $cleanResultBeforeDate must be a valid UTC-0 date yyyy-mm-dd if (!empty($cleanResultBeforeDate)) { $cleanResultBeforeDate = Database::escape_string($cleanResultBeforeDate); if (api_is_valid_date($cleanResultBeforeDate)) { $sql_where .= " AND exe_date <= '{$cleanResultBeforeDate}' "; } else { return 0; } } $sql = "SELECT exe_id\n FROM {$table_track_e_exercises}\n WHERE\n c_id = " . api_get_course_int_id() . " AND\n exe_exo_id = " . $this->id . " AND\n session_id = " . api_get_session_id() . " " . $sql_where; $result = Database::query($sql); $exe_list = Database::store_result($result); // deleting TRACK_E_ATTEMPT table // check if exe in learning path or not $i = 0; if (is_array($exe_list) && count($exe_list) > 0) { foreach ($exe_list as $item) { $sql = "DELETE FROM {$table_track_e_attempt}\n WHERE exe_id = '" . $item['exe_id'] . "'"; Database::query($sql); $i++; } } $session_id = api_get_session_id(); // delete TRACK_E_EXERCISES table $sql = "DELETE FROM {$table_track_e_exercises}\n WHERE c_id = " . api_get_course_int_id() . "\n AND exe_exo_id = " . $this->id . "\n {$sql_where}\n AND session_id = " . $session_id . ""; Database::query($sql); Event::addEvent(LOG_EXERCISE_RESULT_DELETE, LOG_EXERCISE_ID, $this->id, null, null, api_get_course_int_id(), $session_id); return $i; }
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION); $tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE); $tbl_user = Database::get_main_table(TABLE_MAIN_USER); $tbl_chat_connected = Database::get_course_table(TABLE_CHAT_CONNECTED); $query = "SELECT username FROM {$tbl_user} WHERE user_id='" . $user_id . "'"; $result = Database::query($query); list($pseudo_user) = Database::fetch_array($result); $isAllowed = !(empty($pseudo_user) || !$_cid); $isMaster = api_is_course_admin(); $date_inter = api_get_utc_datetime(time() - 120); $users = array(); $course_id = api_get_course_int_id(); if (empty($session_id)) { $sql = "SELECT DISTINCT\n\t\t t1.user_id,\n\t\t username,\n\t\t firstname,\n\t\t lastname,\n\t\t picture_uri,\n\t\t email,\n\t\t t3.status\n FROM {$tbl_user} t1, {$tbl_chat_connected} t2, {$tbl_course_user} t3\n WHERE\n t2.c_id = {$course_id} AND\n t1.user_id=t2.user_id AND\n t3.user_id=t2.user_id AND\n t3.relation_type<>" . COURSE_RELATION_TYPE_RRHH . " AND\n t3.c_id = '" . $courseInfo['real_id'] . "' AND\n t2.last_connection>'" . $date_inter . "' {$extra_condition}\n ORDER BY username"; $result = Database::query($sql); $users = Database::store_result($result); } else { $session_condition = api_get_session_condition($session_id, true, false, 't3.session_id'); // select learners $query = "SELECT DISTINCT t1.user_id,username,firstname,lastname,picture_uri,email\n FROM {$tbl_user} t1, {$tbl_chat_connected} t2, {$tbl_session_course_user} t3\n\t\t WHERE\n\t\t t2.c_id = {$course_id} AND\n\t\t t1.user_id=t2.user_id AND t3.user_id=t2.user_id AND\n\t\t t3.session_id = '" . $session_id . "' AND\n\t\t t3.c_id = '" . $courseInfo['real_id'] . "' AND\n\t\t t2.last_connection>'" . $date_inter . "' {$session_condition}\n\t\t ORDER BY username"; $result = Database::query($query); while ($learner = Database::fetch_array($result)) { $users[$learner['user_id']] = $learner; } // select session coach $query = "SELECT DISTINCT t1.user_id,username,firstname,lastname,picture_uri,email\n\t\t FROM {$tbl_user} t1,{$tbl_chat_connected} t2,{$tbl_session} t3\n\t\t WHERE\n\t\t\t\t\tt2.c_id = {$course_id} AND\n\t\t\t\t\tt1.user_id=t2.user_id AND\n\t\t\t\t\tt3.id_coach=t2.user_id AND\n\t\t\t\t\tt3.id = '" . $session_id . "' AND\n\t\t\t\t\tt2.last_connection > '" . $date_inter . "'\n\t\t\t\t\t{$extra_condition}\n\t\t\t\t ORDER BY username"; $result = Database::query($query); if ($coach = Database::fetch_array($result)) { $users[$coach['user_id']] = $coach; } // select session course coach
if (isset($_POST['link'][$row['id']])) { //$new_weight = trim($_POST['link'][$row['id']]*$original_total/$masked_total); $new_weight = trim($_POST['link'][$row['id']]); GradebookUtils::updateLinkWeight($row['id'], $resource_name, $new_weight); $item_weight = $new_weight; } $output .= '<tr><td>' . GradebookUtils::build_type_icon_tag($row['type']) . '</td> <td> ' . $resource_name . ' ' . Display::label($table_evaluated[$row['type']][3], 'info') . ' </td>'; $output .= '<td> <input type="hidden" name="link_' . $row['id'] . '" value="' . $resource_name . '" /> <input size="10" type="text" name="link[' . $row['id'] . ']" value="' . $item_weight . '"/> </td></tr>'; } $sql = 'SELECT * FROM ' . $table_evaluation . ' WHERE category_id = ' . $my_selectcat; $result = Database::query($sql); $evaluations = Database::store_result($result); foreach ($evaluations as $evaluationRow) { $item_weight = $evaluationRow['weight']; //$item_weight = $masked_total*$item_weight/$original_total; //update only if value changed if (isset($_POST['evaluation'][$evaluationRow['id']])) { //$new_weight = trim($_POST['evaluation'][$evaluationRow['id']]*$original_total/$masked_total); $new_weight = trim($_POST['evaluation'][$evaluationRow['id']]); GradebookUtils::updateEvaluationWeight($evaluationRow['id'], $new_weight); $item_weight = $new_weight; } $output .= '<tr> <td>' . GradebookUtils::build_type_icon_tag('evalnotempty') . '</td> <td>' . $evaluationRow['name'] . ' ' . Display::label(get_lang('Evaluation')) . '</td>'; $output .= '<td> <input type="hidden" name="eval_' . $evaluationRow['id'] . '" value="' . $evaluationRow['name'] . '" />
/** * * @global bool $is_platformAdmin * @global bool $is_allowedCreateCourse * @global object $_user * @global int $_cid * @global array $_course * @global int $_real_cid * @global type $_courseUser * @global type $is_courseAdmin * @global type $is_courseTutor * @global type $is_courseCoach * @global type $is_courseMember * @global type $is_sessionAdmin * @global type $is_allowed_in_course * * @param type $course_id * @param type $reset */ static function init_course($course_id, $reset) { global $_configuration; global $is_platformAdmin; global $is_allowedCreateCourse; global $_user; global $_cid; global $_course; global $_real_cid; global $is_courseAdmin; //course teacher global $is_courseTutor; //course teacher - some rights global $is_courseCoach; //course coach global $is_courseMember; //course student global $is_sessionAdmin; global $is_allowed_in_course; if ($reset) { // Course session data refresh requested or empty data if ($course_id) { $course_table = Database::get_main_table(TABLE_MAIN_COURSE); $course_cat_table = Database::get_main_table(TABLE_MAIN_CATEGORY); $sql = "SELECT course.*, course_category.code faCode, course_category.name faName\n FROM {$course_table}\n LEFT JOIN {$course_cat_table}\n ON course.category_code = course_category.code\n WHERE course.code = '{$course_id}'"; $result = Database::query($sql); if (Database::num_rows($result) > 0) { $course_data = Database::fetch_array($result); //@TODO real_cid should be cid, for working with numeric course id $_real_cid = $course_data['id']; $_cid = $course_data['code']; $_course = array(); $_course['real_id'] = $course_data['id']; $_course['id'] = $course_data['code']; //auto-assigned integer $_course['code'] = $course_data['code']; $_course['name'] = $course_data['title']; $_course['title'] = $course_data['title']; $_course['official_code'] = $course_data['visual_code']; // use in echo $_course['sysCode'] = $course_data['code']; // use as key in db $_course['path'] = $course_data['directory']; // use as key in path $_course['titular'] = $course_data['tutor_name']; // this should be deprecated and use the table course_rel_user $_course['language'] = $course_data['course_language']; $_course['extLink']['url'] = $course_data['department_url']; $_course['extLink']['name'] = $course_data['department_name']; $_course['categoryCode'] = $course_data['faCode']; $_course['categoryName'] = $course_data['faName']; $_course['visibility'] = $course_data['visibility']; $_course['subscribe_allowed'] = $course_data['subscribe']; $_course['unsubscribe'] = $course_data['unsubscribe']; $_course['activate_legal'] = $course_data['activate_legal']; $_course['show_score'] = $course_data['show_score']; //used in the work tool Session::write('_cid', $_cid); Session::write('_course', $_course); //@TODO real_cid should be cid, for working with numeric course id Session::write('_real_cid', $_real_cid); // if a session id has been given in url, we store the session // Database Table Definitions $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION); if (!empty($_GET['id_session'])) { $_SESSION['id_session'] = intval($_GET['id_session']); $sql = 'SELECT name FROM ' . $tbl_session . ' WHERE id="' . intval($_SESSION['id_session']) . '"'; $rs = Database::query($sql); list($_SESSION['session_name']) = Database::fetch_array($rs); } else { Session::erase('session_name'); Session::erase('id_session'); } if (!isset($_SESSION['login_as'])) { //Course login if (isset($_user['user_id'])) { Event::event_course_login(api_get_course_int_id(), $_user['user_id'], api_get_session_id()); } } } else { //exit("WARNING UNDEFINED CID !! "); header('location:' . api_get_path(WEB_PATH)); } } else { Session::erase('_cid'); Session::erase('_real_cid'); Session::erase('_course'); if (!empty($_SESSION)) { foreach ($_SESSION as $key => $session_item) { if (strpos($key, 'lp_autolaunch_') === false) { continue; } else { if (isset($_SESSION[$key])) { Session::erase($key); } } } } //Deleting session info if (api_get_session_id()) { Session::erase('id_session'); Session::erase('session_name'); } } } else { // Continue with the previous values if (empty($_SESSION['_course']) or empty($_SESSION['_cid'])) { //no previous values... $_cid = -1; //set default values that will be caracteristic of being unset $_course = -1; } else { $_cid = $_SESSION['_cid']; $_course = $_SESSION['_course']; // these lines are usefull for tracking. Indeed we can have lost the id_session and not the cid. // Moreover, if we want to track a course with another session it can be usefull if (!empty($_GET['id_session'])) { $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION); $sql = 'SELECT name FROM ' . $tbl_session . ' WHERE id="' . intval($_SESSION['id_session']) . '"'; $rs = Database::query($sql); list($_SESSION['session_name']) = Database::fetch_array($rs); $_SESSION['id_session'] = intval($_GET['id_session']); } if (!isset($_SESSION['login_as'])) { $save_course_access = true; //The value $_dont_save_user_course_access should be added before the call of global.inc.php see the main/inc/chat.ajax.php file //Disables the updates in the TRACK_E_COURSE_ACCESS table if (isset($_dont_save_user_course_access) && $_dont_save_user_course_access == true) { $save_course_access = false; } if ($save_course_access) { $course_tracking_table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS); /* * When $_configuration['session_lifetime'] is too big 100 hours (in order to let users take exercises with no problems) * the function Tracking::get_time_spent_on_the_course() returns big values (200h) due the condition: * login_course_date > now() - INTERVAL $session_lifetime SECOND * */ /* if (isset($_configuration['session_lifetime'])) { $session_lifetime = $_configuration['session_lifetime']; } else { $session_lifetime = 3600; // 1 hour } */ $session_lifetime = 3600; // 1 hour $time = api_get_utc_datetime(); if (isset($_user['user_id']) && !empty($_user['user_id'])) { //We select the last record for the current course in the course tracking table //But only if the login date is < than now + max_life_time $sql = "SELECT course_access_id FROM {$course_tracking_table}\n WHERE\n user_id = " . intval($_user['user_id']) . " AND\n c_id = '" . api_get_course_int_id() . "' AND\n session_id = " . api_get_session_id() . " AND\n login_course_date > now() - INTERVAL {$session_lifetime} SECOND\n ORDER BY login_course_date DESC LIMIT 0,1"; $result = Database::query($sql); if (Database::num_rows($result) > 0) { $i_course_access_id = Database::result($result, 0, 0); //We update the course tracking table $sql = "UPDATE {$course_tracking_table}\n SET logout_course_date = '{$time}', counter = counter+1\n WHERE course_access_id = " . intval($i_course_access_id) . " AND session_id = " . api_get_session_id(); Database::query($sql); } else { $sql = "INSERT INTO {$course_tracking_table} (c_id, user_id, login_course_date, logout_course_date, counter, session_id)" . "VALUES('" . api_get_course_int_id() . "', '" . $_user['user_id'] . "', '{$time}', '{$time}', '1','" . api_get_session_id() . "')"; Database::query($sql); } } } } } } /* COURSE / USER REL. INIT */ $session_id = api_get_session_id(); $user_id = isset($_user['user_id']) ? $_user['user_id'] : null; //Course permissions $is_courseAdmin = false; //course teacher $is_courseTutor = false; //course teacher - some rights $is_courseMember = false; //course student //Course - User permissions $is_sessionAdmin = false; if ($reset) { if (isset($user_id) && $user_id && isset($_cid) && $_cid) { //Check if user is subscribed in a course $course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER); $sql = "SELECT * FROM {$course_user_table}\n WHERE\n user_id = '" . $user_id . "' AND\n relation_type <> " . COURSE_RELATION_TYPE_RRHH . " AND\n course_code = '{$course_id}'"; $result = Database::query($sql); $cuData = null; if (Database::num_rows($result) > 0) { // this user have a recorded state for this course $cuData = Database::fetch_array($result, 'ASSOC'); $is_courseAdmin = (bool) $cuData['status'] == 1; $is_courseTutor = (bool) $cuData['is_tutor'] == 1; $is_courseMember = true; // Checking if the user filled the course legal agreement if ($_course['activate_legal'] == 1 && !api_is_platform_admin()) { $user_is_subscribed = CourseManager::is_user_accepted_legal($user_id, $_course['id'], $session_id); if (!$user_is_subscribed) { $url = api_get_path(WEB_CODE_PATH) . 'course_info/legal.php?course_code=' . $_course['code'] . '&session_id=' . $session_id; header('Location: ' . $url); exit; } } } //We are in a session course? Check session permissions if (!empty($session_id)) { //I'm not the teacher of the course if ($is_courseAdmin == false) { // this user has no status related to this course // The user is subscribed in a session? The user is a Session coach a Session admin ? $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION); $tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE); $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); //Session coach, session admin, course coach admin $sql = "SELECT session.id_coach, session_admin_id, session_rcru.user_id\n FROM {$tbl_session} session, {$tbl_session_course_user} session_rcru\n WHERE\n session_rcru.session_id = session.id AND\n session_rcru.c_id = '{$_real_cid}' AND\n session_rcru.user_id = '{$user_id}' AND\n session_rcru.session_id = {$session_id} AND\n session_rcru.status = 2"; $result = Database::query($sql); $row = Database::store_result($result); //I'm a session admin? if (isset($row) && isset($row[0]) && $row[0]['session_admin_id'] == $user_id) { $is_courseMember = false; $is_courseTutor = false; $is_courseAdmin = false; $is_courseCoach = false; $is_sessionAdmin = true; } else { //Im a coach or a student? $sql = "SELECT user_id, status\n FROM " . $tbl_session_course_user . "\n WHERE\n c_id = '{$_cid}' AND\n user_id = '" . $user_id . "' AND\n session_id = '" . $session_id . "'\n LIMIT 1"; $result = Database::query($sql); if (Database::num_rows($result)) { $row = Database::fetch_array($result, 'ASSOC'); $session_course_status = $row['status']; switch ($session_course_status) { case '2': // coach - teacher $is_courseMember = true; $is_courseTutor = true; $is_courseCoach = true; $is_sessionAdmin = false; if (api_get_setting('extend_rights_for_coach') == 'true') { $is_courseAdmin = true; } else { $is_courseAdmin = false; } break; case '0': //student $is_courseMember = true; $is_courseTutor = false; $is_courseAdmin = false; $is_sessionAdmin = false; break; default: //unregister user $is_courseMember = false; $is_courseTutor = false; $is_courseAdmin = false; $is_sessionAdmin = false; break; } } else { //unregister user $is_courseMember = false; $is_courseTutor = false; $is_courseAdmin = false; $is_sessionAdmin = false; } } } //If I'm the admin platform i'm a teacher of the course if ($is_platformAdmin) { $is_courseAdmin = true; } } } else { // keys missing => not anymore in the course - user relation // course $is_courseMember = false; $is_courseAdmin = false; $is_courseTutor = false; $is_courseCoach = false; $is_sessionAdmin = false; } //Checking the course access $is_allowed_in_course = false; if (isset($_course)) { switch ($_course['visibility']) { case COURSE_VISIBILITY_OPEN_WORLD: //3 $is_allowed_in_course = true; break; case COURSE_VISIBILITY_OPEN_PLATFORM: //2 if (isset($user_id) && !api_is_anonymous($user_id)) { $is_allowed_in_course = true; } break; case COURSE_VISIBILITY_REGISTERED: //1 if ($is_platformAdmin || $is_courseMember) { $is_allowed_in_course = true; } break; case COURSE_VISIBILITY_CLOSED: //0 if ($is_platformAdmin || $is_courseAdmin) { $is_allowed_in_course = true; } break; case COURSE_VISIBILITY_HIDDEN: //4 if ($is_platformAdmin) { $is_allowed_in_course = true; } break; } } // check the session visibility if ($is_allowed_in_course == true) { //if I'm in a session if ($session_id != 0) { if (!$is_platformAdmin) { // admin and session coach are *not* affected to the invisible session mode // the coach is not affected because he can log in some days after the end date of a session $session_visibility = api_get_session_visibility($session_id); switch ($session_visibility) { case SESSION_INVISIBLE: $is_allowed_in_course = false; break; } //checking date } } } // save the states Session::write('is_courseAdmin', $is_courseAdmin); Session::write('is_courseMember', $is_courseMember); Session::write('is_courseTutor', $is_courseTutor); Session::write('is_courseCoach', $is_courseCoach); Session::write('is_allowed_in_course', $is_allowed_in_course); Session::write('is_sessionAdmin', $is_sessionAdmin); } else { // continue with the previous values $is_courseAdmin = $_SESSION['is_courseAdmin']; $is_courseTutor = $_SESSION['is_courseTutor']; $is_courseCoach = $_SESSION['is_courseCoach']; $is_courseMember = $_SESSION['is_courseMember']; $is_allowed_in_course = $_SESSION['is_allowed_in_course']; } }
if (!empty($_GET['msg'])) { Display::display_normal_message(urldecode($_GET['msg'])); } // the form header $session_info = SessionManager::fetch($id_session); $user_info = api_get_user_info($id_user); echo '<legend>' . $tool_name . ': ' . $session_info['name'] . ' - ' . $user_info['complete_name'] . '</legend>'; $nosessionCourses = $sessionCourses = array(); // actual user $sql = "SELECT course.id, course.code, title, visual_code, srcru.id_session\n FROM {$tbl_course} course INNER JOIN {$tbl_session_rel_course_rel_user} as srcru\n ON course.id = srcru.c_id\n WHERE srcru.id_user = {$id_user} AND id_session = {$id_session}"; //all $sql_all = "SELECT course.id, code, title, visual_code, src.id_session " . "FROM {$tbl_course} course INNER JOIN {$tbl_session_rel_course} as src " . "ON course.id = src.c_id AND id_session = {$id_session}"; $result = Database::query($sql); $Courses = Database::store_result($result); $result = Database::query($sql_all); $CoursesAll = Database::store_result($result); $course_temp = array(); foreach ($Courses as $course) { $course_temp[] = $course['id']; } foreach ($CoursesAll as $course) { if (in_array($course['id'], $course_temp)) { $nosessionCourses[$course['id']] = $course; } else { $sessionCourses[$course['id']] = $course; } } unset($Courses); ?> <form name="formulaire" method="post" action="<?php
/** * Generate SQL to select all the links categories in the current course and * session * @param int $courseId * @param int $sessionId * @return array */ public static function getLinkCategories($courseId, $sessionId) { $tblLinkCategory = Database::get_course_table(TABLE_LINK_CATEGORY); $tblItemProperty = Database::get_course_table(TABLE_ITEM_PROPERTY); $courseId = intval($courseId); // Condition for the session. $sessionCondition = api_get_session_condition($sessionId, true, true, 'linkcat.session_id'); // Getting links $sql = "SELECT *, linkcat.id\n FROM {$tblLinkCategory} linkcat\n WHERE\n linkcat.c_id = " . $courseId . "\n {$sessionCondition}\n ORDER BY linkcat.display_order DESC"; $result = Database::query($sql); $categories = Database::store_result($result); $sql = "SELECT *, linkcat.id\n FROM {$tblLinkCategory} linkcat\n INNER JOIN {$tblItemProperty} itemproperties\n ON (linkcat.id = itemproperties.ref AND linkcat.c_id = itemproperties.c_id)\n WHERE\n itemproperties.tool = '" . TOOL_LINK_CATEGORY . "' AND\n (itemproperties.visibility = '0' OR itemproperties.visibility = '1')\n {$sessionCondition} AND\n linkcat.c_id = " . $courseId . "\n ORDER BY linkcat.display_order DESC"; $result = Database::query($sql); $categoryInItemProperty = array(); if (Database::num_rows($result)) { while ($row = Database::fetch_array($result, 'ASSOC')) { $categoryInItemProperty[$row['id']] = $row; } } foreach ($categories as &$category) { if (!isset($categoryInItemProperty[$category['id']])) { api_set_default_visibility($category['id'], TOOL_LINK_CATEGORY); } } $sql = "SELECT DISTINCT linkcat.*, visibility\n FROM {$tblLinkCategory} linkcat\n INNER JOIN {$tblItemProperty} itemproperties\n ON (linkcat.id = itemproperties.ref AND linkcat.c_id = itemproperties.c_id)\n WHERE\n itemproperties.tool = '" . TOOL_LINK_CATEGORY . "' AND\n (itemproperties.visibility = '0' OR itemproperties.visibility = '1')\n {$sessionCondition} AND\n linkcat.c_id = " . $courseId . "\n GROUP BY c_id, id\n ORDER BY linkcat.display_order DESC\n "; $result = Database::query($sql); return Database::store_result($result, 'ASSOC'); }
/** * @param int $courseId * @param int $sessionId * * @return array */ public function getCategories($courseId, $sessionId = 0) { $table = Database::get_course_table(TABLE_QUIZ_QUESTION_CATEGORY); $itemProperty = Database::get_course_table(TABLE_ITEM_PROPERTY); $sessionId = intval($sessionId); $courseId = intval($courseId); if (empty($sessionId)) { $sessionCondition = api_get_session_condition($sessionId, true, false, 'i.session_id'); } else { $sessionCondition = api_get_session_condition($sessionId, true, true, 'i.session_id'); } if (empty($courseId)) { return array(); } $sql = "SELECT c.* FROM {$table} c\n INNER JOIN {$itemProperty} i\n ON c.c_id = i.c_id AND i.ref = c.id\n WHERE\n c.c_id = {$courseId} AND\n i.tool = '" . TOOL_TEST_CATEGORY . "'\n {$sessionCondition}\n ORDER BY title"; $result = Database::query($sql); return Database::store_result($result, 'ASSOC'); }
/** * * Delete documents from a session in a course. * @param array $courseInfo * @param int $sessionId * * @return bool */ public function deleteDocumentsFromSession($courseInfo, $sessionId) { if (empty($courseInfo)) { return false; } if (empty($sessionId)) { return false; } $itemPropertyTable = Database::get_course_table(TABLE_ITEM_PROPERTY); $documentTable = Database::get_course_table(TABLE_DOCUMENT); $conditionSession = api_get_session_condition($sessionId, true, false, 'd.session_id'); //get invisible folders $sql = "SELECT DISTINCT d.id, path\n FROM {$itemPropertyTable} i\n INNER JOIN {$documentTable} d\n ON (i.c_id = d.c_id)\n WHERE\n d.id = i.ref AND\n i.tool = '" . TOOL_DOCUMENT . "'\n {$conditionSession} AND\n i.c_id = {$courseInfo['real_id']} AND\n d.c_id = {$courseInfo['real_id']} "; $result = Database::query($sql); $documents = Database::store_result($result, 'ASSOC'); if ($documents) { $course_dir = $courseInfo['directory'] . '/document'; $sys_course_path = api_get_path(SYS_COURSE_PATH); $base_work_dir = $sys_course_path . $course_dir; foreach ($documents as $document) { $documentId = $document['id']; DocumentManager::delete_document($courseInfo, null, $base_work_dir, $sessionId, $documentId); } } }
} } } // display the header Display::display_header($tool_name); //select of sessions $sql = "SELECT id, name FROM {$tbl_session} ORDER BY name"; if (api_is_multiple_url_enabled()) { $tbl_session_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_SESSION); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $sql = "SELECT s.id, name FROM {$tbl_session} s\n\t\t\tINNER JOIN {$tbl_session_rel_access_url} as session_rel_url\n\t\t\tON (s.id = session_rel_url.session_id)\n\t\t\tWHERE access_url_id = {$access_url_id}\n\t\t\tORDER BY name"; } } $result = Database::query($sql); $Sessions = Database::store_result($result); echo '<div class="actions">'; echo '<a href="../session/session_list.php">' . Display::return_icon('back.png', get_lang('BackTo') . ' ' . get_lang('SessionList'), '', ICON_SIZE_MEDIUM) . '</a>'; echo '</div>'; if (!empty($errorMsg)) { Display::display_normal_message($errorMsg, false); //main API } $form = new FormValidator('session_export', 'post', api_get_self()); $form->addElement('hidden', 'formSent', 1); $form->addElement('radio', 'file_type', get_lang('OutputFileType'), 'CSV', 'csv', null); $form->addElement('radio', 'file_type', '', 'XLS', 'xls', null); $form->addElement('radio', 'file_type', null, 'XML', 'xml', null, array('id' => 'file_type_xml')); $options = array(); $options['0'] = get_lang('AllSessions'); foreach ($Sessions as $enreg) {
/** * @return array */ public static function getOfficialCodeGrouped() { $user = Database::get_main_table(TABLE_MAIN_USER); $sql = "SELECT DISTINCT official_code\n FROM {$user}\n GROUP BY official_code"; $result = Database::query($sql); $values = Database::store_result($result, 'ASSOC'); $result = array(); foreach ($values as $value) { $result[$value['official_code']] = $value['official_code']; } return $result; }
unset($users[$uid]); } } unset($users); //clean to free memory //filling the correct users in list $sql = "SELECT user_id, lastname, firstname, username, id_session, official_code\n FROM {$tbl_user} u\n LEFT JOIN {$tbl_session_rel_user}\n ON {$tbl_session_rel_user}.id_user = u.user_id AND\n {$tbl_session_rel_user}.id_session = '{$id_session}' AND\n {$tbl_session_rel_user}.relation_type<>" . SESSION_RELATION_TYPE_RRHH . "\n WHERE u.status<>" . DRH . " AND u.status<>6 {$order_clause}"; if (api_is_multiple_url_enabled()) { $tbl_user_rel_access_url = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $sql = "SELECT u.user_id, lastname, firstname, username, id_session, official_code\n FROM {$tbl_user} u\n LEFT JOIN {$tbl_session_rel_user}\n ON {$tbl_session_rel_user}.id_user = u.user_id AND\n {$tbl_session_rel_user}.id_session = '{$id_session}' AND\n {$tbl_session_rel_user}.relation_type<>" . SESSION_RELATION_TYPE_RRHH . "\n INNER JOIN {$tbl_user_rel_access_url} url_user ON (url_user.user_id=u.user_id)\n WHERE access_url_id = {$access_url_id} AND u.status<>" . DRH . " AND u.status<>6\n {$order_clause}"; } } $result = Database::query($sql); $users = Database::store_result($result, 'ASSOC'); foreach ($users as $uid => $user) { if ($user['id_session'] == $id_session) { $sessionUsersList[$user['user_id']] = $user; if (array_key_exists($user['user_id'], $nosessionUsersList)) { unset($nosessionUsersList[$user['user_id']]); } } unset($users[$uid]); } unset($users); //clean to free memory } if ($add_type == 'multiple') { $link_add_type_unique = '<a href="' . api_get_self() . '?id_session=' . $id_session . '&add=' . $addProcess . '&add_type=unique">' . Display::return_icon('single.gif') . get_lang('SessionAddTypeUnique') . '</a>'; $link_add_type_multiple = Display::url(Display::return_icon('multiple.gif') . get_lang('SessionAddTypeMultiple'), '');
$html_result .= '<th>' . get_lang('Status') . '</th>'; $html_result .= '<th>' . get_lang('Attempts') . '</th>'; $html_result .= '</tr>'; } $table = Database::get_course_table(TABLE_TOOL_LIST); $t_quiz = Database::get_course_table(TABLE_QUIZ_TEST); $export_array_global = $export_array = array(); if (!empty($course_list) && is_array($course_list)) { foreach ($course_list as $current_course) { $global_row = $row_not_global = array(); $course_id = $current_course['real_id']; $a_students = CourseManager::get_student_list_from_course_code($course_id, false); $total_students = count($a_students); $sqlExercices = "SELECT count(id) as count FROM " . $t_quiz . " AS quiz WHERE active='1' AND c_id = {$course_id} "; $resultExercices = Database::query($sqlExercices); $data_exercises = Database::store_result($resultExercices); $exercise_count = $data_exercises[0]['count']; if ($global) { if ($exercise_count == 0) { $exercise_count = 2; } $html_result .= "<tr class='{$s_css_class}'>\n <td rowspan={$exercise_count}>"; $html_result .= $current_course['title']; $html_result .= "</td>"; } $sql = "SELECT visibility FROM {$table} WHERE c_id = {$course_id} AND name='quiz'"; $resultVisibilityQuizz = Database::query($sql); if (Database::result($resultVisibilityQuizz, 0, 'visibility') == 1) { $sqlExercices = " SELECT quiz.title,id FROM " . $t_quiz . " AS quiz WHERE c_id = {$course_id} AND active='1' ORDER BY quiz.title ASC"; //Getting the exam list if (!$global) {
/** * Get user list by usergroup * @param $id * @return array */ public function getUserListByUserGroup($id) { $id = intval($id); $sql = "SELECT u.* FROM " . $this->table_user . " u\n INNER JOIN " . $this->usergroup_rel_user_table . " c\n ON c.user_id = u.id\n WHERE c.usergroup_id = {$id}"; $result = Database::query($sql); return Database::store_result($result); }
/** * @param int $categoryId * @return array */ static function getSessionsByCategory($categoryId) { $categoryId = intval($categoryId); $tableSession = Database::get_main_table(TABLE_MAIN_SESSION); $sql = "select * FROM {$tableSession} WHERE session_category_id = {$categoryId}"; $result = Database::query($sql); return Database::store_result($result); }
if (Database::num_rows($rs) > 0) { while ($infos = Database::fetch_array($rs)) { $arr_infos[] = $infos['user_id']; } } } $order_clause = api_sort_by_first_name() ? ' ORDER BY firstname, lastname, username' : ' ORDER BY lastname, firstname, username'; if (api_is_multiple_url_enabled()) { $tbl_access_rel_user = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER); $access_url_id = api_get_current_access_url_id(); $sql = "SELECT u.user_id,lastname,firstname,username\n FROM {$tbl_user} u\n LEFT JOIN {$tbl_access_rel_user} a\n ON(u.user_id= a.user_id)\n WHERE\n status='1' AND\n active = 1 AND\n access_url_id = {$access_url_id} " . $order_clause; } else { $sql = "SELECT user_id,lastname,firstname,username\n FROM {$tbl_user}\n WHERE\n status = '1' AND\n active = 1 " . $order_clause; } $result = Database::query($sql); $coaches = Database::store_result($result); if (!api_is_platform_admin() && api_is_teacher()) { $userInfo = api_get_user_info(); $coaches = [$userInfo]; } Display::display_header($tool_name); $tool_name = get_lang('ModifySessionCourse'); api_display_tool_title($tool_name); ?> <div class="session-course-edit"> <form method="post" action="<?php echo api_get_self(); ?> ?id_session=<?php echo $id_session;
/** * Get las connection date for a student * @param array $studentList Student id array * @param int $days * @param bool $getCount * @return int */ public static function getInactiveUsers($studentList, $days, $getCount = true) { if (empty($studentList)) { return 0; } $days = intval($days); $date = api_get_utc_datetime(strtotime($days . ' days ago')); $studentList = array_map('intval', $studentList); $tbl_track_login = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN); $select = " SELECT login_user_id "; if ($getCount) { $select = " SELECT count(DISTINCT login_user_id) as count"; } $sql = "{$select}\n FROM {$tbl_track_login}\n WHERE\n login_user_id IN (' " . implode("','", $studentList) . "' ) AND\n login_date < '{$date}'\n "; $rs = Database::query($sql); if (Database::num_rows($rs) > 0) { if ($getCount) { $count = Database::fetch_array($rs); return $count['count']; } return Database::store_result($rs, 'ASSOC'); } return false; }
/** * * @param $exerciseId * @param $mediaId * @return array|bool */ public function getQuestionsPerMediaWithCategories($exerciseId, $mediaId) { $exerciseId = intval($exerciseId); $mediaId = intval($mediaId); $questionTable = Database::get_course_table(TABLE_QUIZ_QUESTION); $questionRelExerciseTable = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION); $sql = "SELECT q.* FROM {$questionTable} q INNER JOIN {$questionRelExerciseTable} r ON (q.iid = r.question_id)\n WHERE (r.exercice_id = {$exerciseId} AND q.parent_id = {$mediaId}) "; $result = Database::query($sql); if (Database::num_rows($result)) { return Database::store_result($result, 'ASSOC'); } return false; }
/** * restore works * @todo fix this crappy function */ function restore_student_publication() { $work_assignment_table = Database::get_course_table(TABLE_STUDENT_PUBLICATION_ASSIGNMENT); $work_table = Database::get_course_table(TABLE_STUDENT_PUBLICATION); $item_property_table = Database::get_course_table(TABLE_ITEM_PROPERTY); //query in student publication $sql = 'SELECT c_id, id, url, title,description,author,active,accepted,post_group_id, sent_date,filetype,has_properties,view_properties,qualification,date_of_qualification,qualificator_id,session_id FROM ' . $work_table . ' WHERE c_id = ' . $this->course_origin_id . ' AND filetype="folder" AND active IN (0, 1) '; $result = Database::query($sql); $folders = Database::store_result($result, 'ASSOC'); foreach ($folders as $folder) { $old_id = $folder['id']; unset($folder['id']); $folder['c_id'] = $this->destination_course_id; $folder['parent_id'] = 0; $new_id = Database::insert($work_table, $folder); if ($new_id) { //query in item property $sql = 'SELECT tool,insert_user_id,insert_date,lastedit_date,ref,lastedit_type, lastedit_user_id,to_group_id,to_user_id,visibility,start_visible, end_visible FROM ' . $item_property_table . ' ip INNER JOIN ' . $work_table . ' sp ON ip.ref=sp.id WHERE sp.c_id = ' . $this->course_origin_id . ' AND ip.c_id = ' . $this->course_origin_id . ' AND tool="work" AND sp.id = ' . $old_id . ''; $result = Database::query($sql); $sub_folders = Database::store_result($result, 'ASSOC'); foreach ($sub_folders as $sub_folder) { $sub_folder['c_id'] = $this->destination_course_id; $sub_folder['ref'] = $new_id; $new_item_id = Database::insert($item_property_table, $sub_folder); } $sql = 'SELECT sa.id, sa.expires_on,sa.ends_on,sa.add_to_calendar, sa.enable_qualification, sa.publication_id FROM ' . $work_assignment_table . ' sa INNER JOIN ' . $work_table . ' sp ON sa.publication_id=sp.id WHERE sp.c_id = ' . $this->course_origin_id . ' AND sa.c_id = ' . $this->course_origin_id . ' AND filetype="folder" AND sp.id = ' . $old_id . ''; $result = Database::query($sql); $assing_list = Database::store_result($result, 'ASSOC'); foreach ($assing_list as $assign) { $assign['c_id'] = $this->destination_course_id; $assign['id'] = $new_id; Database::insert($work_assignment_table, $assign); } } } $destination = '../../courses/' . $this->course->destination_path . '/work/'; $origin = '../../courses/' . $this->course->info['path'] . '/work/'; self::allow_create_all_directory($origin, $destination, false); }
/** * @param int $courseId * @param int $sessionId * @param string $order * @return array */ public function getUserAgreementList($courseId, $sessionId, $order = null) { $courseId = intval($courseId); $sessionId = intval($sessionId); $table = Database::get_main_table('session_rel_course_rel_user_legal'); $userTable = Database::get_main_table(TABLE_MAIN_USER); $sql = "SELECT *\n FROM {$table} s INNER JOIN {$userTable} u\n ON u.user_id = s.user_id\n WHERE c_id = {$courseId} AND session_id = {$sessionId} "; if (!empty($order)) { $sql .= $order; } $result = Database::query($sql); $data = array(); if (Database::num_rows($result) > 0) { $data = Database::store_result($result, 'ASSOC'); } return $data; }
/** * Get a list of students that do not have a result record for this evaluation */ public function get_not_subscribed_students($first_letter_user = '') { $tbl_user = Database::get_main_table(TABLE_MAIN_USER); $tbl_grade_results = Database::get_main_table(TABLE_MAIN_GRADEBOOK_RESULT); $sql = 'SELECT user_id,lastname,firstname,username FROM ' . $tbl_user . " WHERE lastname LIKE '" . Database::escape_string($first_letter_user) . "%'" . ' AND status = ' . STUDENT . ' AND user_id NOT IN' . ' (SELECT user_id FROM ' . $tbl_grade_results . ' WHERE evaluation_id = ' . intval($this->id) . ' )' . ' ORDER BY lastname'; $result = Database::query($sql); $users = Database::store_result($result); return $users; }