public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "cronid,timenumber,timetype,timeinterval,last_update,jobdata,date_added,status"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by cronid desc'; $result = array(); $command = "select {$selectFields} from cronjobs {$whereQuery} {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $query = Database::query($queryCMD); $inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes'; if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { if (isset($row['jobdata'])) { $row['jobdata'] = String::jsonToArray($row['jobdata']); } $row['date_addedFormat'] = Render::dateFormat($row['date_added']); $result[] = $row; } } else { return false; } // print_r($result);die(); return $result; }
/** * @package chamilo.survey * @author Arnaud Ligot <*****@*****.**> * @version $Id: $ * * A small peace of code to enable user to access images included into survey * which are accessible by non authenticated users. This file is included * by document/download.php */ function check_download_survey($course, $invitation, $doc_url) { require_once 'survey.lib.php'; // Getting all the course information $_course = CourseManager::get_course_information($course); $course_id = $_course['real_id']; // Database table definitions $table_survey = Database::get_course_table(TABLE_SURVEY); $table_survey_question = Database::get_course_table(TABLE_SURVEY_QUESTION); $table_survey_question_option = Database::get_course_table(TABLE_SURVEY_QUESTION_OPTION); $table_survey_invitation = Database::get_course_table(TABLE_SURVEY_INVITATION); // Now we check if the invitationcode is valid $sql = "SELECT * FROM {$table_survey_invitation}\n\t WHERE\n\t c_id = {$course_id} AND\n\t invitation_code = '" . Database::escape_string($invitation) . "'"; $result = Database::query($sql); if (Database::num_rows($result) < 1) { Display::display_error_message(get_lang('WrongInvitationCode'), false); Display::display_footer(); exit; } $survey_invitation = Database::fetch_assoc($result); // Now we check if the user already filled the survey if ($survey_invitation['answered'] == 1) { Display::display_error_message(get_lang('YouAlreadyFilledThisSurvey'), false); Display::display_footer(); exit; } // Very basic security check: check if a text field from a survey/answer/option contains the name of the document requested // Fetch survey ID // If this is the case there will be a language choice $sql = "SELECT * FROM {$table_survey}\n\t WHERE\n\t c_id = {$course_id} AND\n\t code='" . Database::escape_string($survey_invitation['survey_code']) . "'"; $result = Database::query($sql); if (Database::num_rows($result) > 1) { if ($_POST['language']) { $survey_invitation['survey_id'] = $_POST['language']; } else { echo '<form id="language" name="language" method="POST" action="' . api_get_self() . '?course=' . $_GET['course'] . '&invitationcode=' . $_GET['invitationcode'] . '">'; echo ' <select name="language">'; while ($row = Database::fetch_assoc($result)) { echo '<option value="' . $row['survey_id'] . '">' . $row['lang'] . '</option>'; } echo '</select>'; echo ' <input type="submit" name="Submit" value="' . get_lang('Ok') . '" />'; echo '</form>'; display::display_footer(); exit; } } else { $row = Database::fetch_assoc($result); $survey_invitation['survey_id'] = $row['survey_id']; } $sql = "SELECT count(*)\n\t FROM {$table_survey}\n\t WHERE\n\t c_id = {$course_id} AND\n\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n title LIKE '%{$doc_url}%'\n or subtitle LIKE '%{$doc_url}%'\n or intro LIKE '%{$doc_url}%'\n or surveythanks LIKE '%{$doc_url}%'\n )\n\t\t UNION\n\t\t SELECT count(*)\n\t\t FROM {$table_survey_question}\n\t\t WHERE\n\t\t c_id = {$course_id} AND\n\t\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n survey_question LIKE '%{$doc_url}%'\n or survey_question_comment LIKE '%{$doc_url}%'\n )\n\t\t UNION\n\t\t SELECT count(*)\n\t\t FROM {$table_survey_question_option}\n\t\t WHERE\n\t\t c_id = {$course_id} AND\n\t\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n option_text LIKE '%{$doc_url}%'\n )"; $result = Database::query($sql); if (Database::num_rows($result) == 0) { Display::display_error_message(get_lang('WrongInvitationCode'), false); Display::display_footer(); exit; } return $_course; }
public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "id,parentid,date_added,title,url,status,sort_order"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by id desc'; $result = array(); $command = "select {$selectFields} from " . Database::getPrefix() . "links {$whereQuery}"; $command .= " {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes'; $cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1; $md5Query = md5($queryCMD); if ($cache == 'yes') { // Load dbcache $loadCache = Cache::loadKey('dbcache/system/link/' . $md5Query, $cacheTime); if ($loadCache != false) { $loadCache = unserialize($loadCache); return $loadCache; } // end load } $query = Database::query($queryCMD); if (isset(Database::$error[5])) { return false; } $inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes'; if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { if (isset($row['title'])) { $row['title'] = String::decode($row['title']); } if (isset($row['date_added'])) { $row['date_addedFormat'] = Render::dateFormat($row['date_added']); } if (isset($row['url']) && !preg_match('/^http/i', $row['url'])) { if (preg_match('/^\\/(.*?)$/i', $row['url'], $matches)) { $tmp = $matches[1]; $row['urlFormat'] = System::getUrl() . $tmp; } } $result[] = $row; } } else { return false; } // Save dbcache Cache::saveKey('dbcache/system/link/' . $md5Query, serialize($result)); // end save return $result; }
public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "userid,groupid,username,firstname,lastname,image,email,password,userdata,ip,verify_code,parentid,date_added,forgot_code,forgot_date"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by date_added desc'; $result = array(); $prefix = ''; $prefixall = Database::isPrefixAll(); if ($prefixall != false || $prefixall == 'no') { $prefix = Database::getPrefix(); } $command = "select {$selectFields} from " . $prefix . "users {$whereQuery}"; $command .= " {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes'; $cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1; $md5Query = md5($queryCMD); if ($cache == 'yes') { // Load dbcache $loadCache = Cache::loadKey('dbcache/system/user/' . $md5Query, $cacheTime); if ($loadCache != false) { $loadCache = unserialize($loadCache); return $loadCache; } // end load } // echo $queryCMD;die(); $query = Database::query($queryCMD); if (isset(Database::$error[5])) { return false; } if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { if (isset($row['date_added'])) { $row['date_addedFormat'] = Render::dateFormat($row['date_added']); } if (isset($row['image'])) { $row['imageFormat'] = self::getAvatar($row['image']); } $result[] = $row; } } else { return false; } // Save dbcache Cache::saveKey('dbcache/system/user/' . $md5Query, serialize($result)); // end save return $result; }
public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "groupid,group_title,groupdata"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by groupid desc'; $result = array(); $prefix = ''; $prefixall = Database::isPrefixAll(); if ($prefixall != false || $prefixall == 'no') { $prefix = Database::getPrefix(); } $command = "select {$selectFields} from " . $prefix . "usergroups {$whereQuery}"; $command .= " {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes'; $cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : 15; $md5Query = md5($queryCMD); if ($cache == 'yes') { // Load dbcache $loadCache = Cache::loadKey('dbcache/system/usergroup/' . $md5Query, $cacheTime); if ($loadCache != false) { $loadCache = unserialize($loadCache); return $loadCache; } // end load } $query = Database::query($queryCMD); if (isset(Database::$error[5])) { return false; } $inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes'; if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { if (isset($row['date_added'])) { $row['date_addedFormat'] = Render::dateFormat($row['date_added']); } if (isset($row['groupdata'])) { $row['groupdata'] = self::arrayToLine($row['groupdata']); } $result[] = $row; } } else { return false; } // Save dbcache Cache::saveKey('dbcache/system/usergroup/' . $md5Query, serialize($result)); // end save return $result; }
public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "requestid,userid,total_request,date_added,status,comments"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by requestid desc'; $result = array(); $command = "select {$selectFields} from " . Database::getPrefix() . "request_payments {$whereQuery}"; $command .= " {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes'; $cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : 1; if ($cache == 'yes') { // Load dbcache $loadCache = DBCache::get($queryCMD, $cacheTime); if ($loadCache != false) { $loadCache = unserialize($loadCache); return $loadCache; } // end load } $query = Database::query($queryCMD); if (isset(Database::$error[5])) { return false; } $inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes'; if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { if (isset($row['comments'])) { $row['comments'] = String::decode($row['comments']); } if (isset($row['date_added'])) { $row['date_addedFormat'] = Render::dateFormat($row['date_added']); } if ($inputData['isHook'] == 'yes') { if (isset($row['comments'])) { $row['comments'] = Shortcode::load($row['comments']); } } $result[] = $row; } } else { return false; } // Save dbcache DBCache::make(md5($queryCMD), $result); // end save return $result; }
public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "userid,company,firstname,lastname,address_1,address_2,city,state,postcode,country,phone,fax"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by userid desc'; $result = array(); $command = "select {$selectFields} from " . Database::getPrefix() . "address {$whereQuery}"; $command .= " {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes'; $cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1; $md5Query = md5($queryCMD); if ($cache == 'yes') { // Load dbcache $loadCache = Cache::loadKey('dbcache/system/address/' . $md5Query, $cacheTime); if ($loadCache != false) { $loadCache = unserialize($loadCache); return $loadCache; } // end load } $query = Database::query($queryCMD); if (isset(Database::$error[5])) { return false; } $inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes'; if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { if (isset($row['title'])) { $row['title'] = String::decode($row['title']); } if (isset($row['friendly_url'])) { $row['url'] = self::url($row); } if (isset($row['date_added'])) { $row['date_addedFormat'] = Render::dateFormat($row['date_added']); } $result[] = $row; } } else { return false; } // Save dbcache Cache::saveKey('dbcache/system/address/' . $md5Query, serialize($result)); // end save return $result; }
/** * Gets user info from external source * @param string login * @param string password * @return user array with at least the following fields: * firstname * lastname * status * email * login * password * or false if no data * */ function external_get_user_info($login, $password) { //Those are the mandatory fields for user creation. //See external_add_user function for all the fields you can have. $table = USERINFO_TABLE; $sql = "SELECT * from {$table} where username='******'"; $result = Database::query($sql); if (Database::num_rows($result) == 0) { //false password return false; } $user_info = Database::fetch_assoc($result); // User status $admin = false; switch ($user_info['status']) { case 'admin': $status = COURSEMANAGER; $admin = true; break; case 'teacher': $status = COURSEMANAGER; break; case 'user': $status = STUDENT; break; default: $status = STUDENT; } // Language switch ($user_info['language']) { case 'FR': $language = 'french'; break; case 'EN': $language = 'english'; break; default: $language = 'english'; break; } //Can Send Message ? $can_send_message = $user_info['can_send_message'] == 1 ? 'yes' : 'no'; $u = array('firstname' => $user_info['firstname'], 'lastname' => $user_info['lastname'], 'status' => $status, 'admin' => $admin, 'email' => $user_info['email'], 'username' => $user_info['username'], 'language' => $language, 'password' => DEFAULT_PASSWORD, 'courses' => $user_info['courses'], 'profile_link' => $user_info['profile_link'], 'worldwide_bu' => $user_info['worlwide_bu'], 'manager' => $user_info['manager'], 'extra' => array('position_title' => $user_info['position_title'], 'country' => $user_info['country'], 'job_family' => $user_info['job_family'], 'country_bu' => $user_info['country_bu'], 'worldwide_bu' => $user_info['worldwide_bu'], 'profile_link' => $user_info['profile_link'], 'can_send_message' => $can_send_message, 'update_type' => 'external_logininfo')); return $u; //Please return false if user does not exist //return false; }
public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "postid,catid"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by date_added desc'; $result = array(); $command = "select {$selectFields} from " . Database::getPrefix() . "post_categories {$whereQuery}"; $command .= " {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes'; $cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1; $md5Query = md5($queryCMD); if ($cache == 'yes') { // Load dbcache $loadCache = Cache::loadKey('dbcache/system/postcategory/' . $md5Query, $cacheTime); if ($loadCache != false) { $loadCache = unserialize($loadCache); return $loadCache; } // end load } $query = Database::query($queryCMD); if (isset(Database::$error[5])) { return false; } $inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes'; if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { $result[] = $row; } } else { return false; } // Save dbcache Cache::saveKey('dbcache/system/postcategory/' . $md5Query, serialize($result)); // end save return $result; }
public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "metaid,foldername,func,zonename,layoutname,layoutposition,content,status,type"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by metaid desc'; $result = array(); $command = "select {$selectFields} from plugins_meta {$whereQuery}"; $command .= " {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes'; $cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : 15; $md5Query = md5($queryCMD); if ($cache == 'yes') { // Load dbcache $loadCache = Cache::loadKey('dbcache/system/pluginmeta/' . $md5Query, $cacheTime); if ($loadCache != false) { $loadCache = unserialize($loadCache); return $loadCache; } // end load } $query = Database::query($queryCMD); if (isset(Database::$error[5])) { return false; } if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { $result[] = $row; } } else { return false; } // Save dbcache Cache::saveKey('dbcache/system/pluginmeta/' . $md5Query, serialize($result)); // end save return $result; }
/** * Get recent posts * * Returns an array of all recent posts, given a certain posts amount. * * @param int $amount Number of posts to get (defaults to 10). * @param int $offset How many posts to skip. * @return array Array of {@link Post} instances of all recent posts. */ function getRecentPosts($amount = '%', $offset = 0) { // default amount from setting if ($amount == '%') { $amount = $this->getSetting('recent_count'); } // db query $sql = 'SELECT `postid`, `time`, `content`, `comments_count` ' . 'FROM `+posts` ' . 'WHERE 1 ' . 'ORDER BY `postid` DESC ' . 'LIMIT ' . ($offset + 0) . ',' . ($amount + 0); $q = $this->db->query($sql) or $this->displayError('site->getRecentPosts', $this->db->error()); $recent = array(); while ($postdata = $this->db->fetch_assoc($q)) { $post = new Post($this); $post->fetch_from_array($postdata); $recent[] = $post; } // while return $recent; }
function storage_get_all_users() { $sql = "select user_id, username, firstname, lastname\n\t\tfrom " . Database::get_main_table(TABLE_MAIN_USER) . "\n\t\torder by user_id asc"; $res = Database::query($sql); $results = array(); while ($row = Database::fetch_assoc($res)) { $results[] = $row; } return json_encode($results); }
$all_visible_files_path[] = $all_visible_files['path']; $files[$all_visible_files['path']] = $all_visible_files; } // 2nd: Get all folders that are invisible in the given path $sql = "SELECT path, session_id, docs.id, props.to_group_id, docs.c_id\n FROM {$doc_table} AS docs INNER JOIN {$prop_table} AS props\n ON\n docs.id = props.ref AND\n docs.c_id = props.c_id\n WHERE\n docs.c_id = {$courseId} AND\n props.tool = '" . TOOL_DOCUMENT . "' AND\n docs.path LIKE '" . $querypath . "/%' AND\n props.visibility <> '1' AND\n (props.session_id IN ('0', '{$sessionId}') OR props.session_id IS NULL) AND\n docs.filetype = 'folder'"; $query2 = Database::query($sql); // If we get invisible folders, we have to filter out these results from all visible files we found if (Database::num_rows($query2) > 0) { $files = array(); // Add item to an array while ($invisible_folders = Database::fetch_assoc($query2)) { //3rd: Get all files that are in the found invisible folder (these are "invisible" too) $sql = "SELECT path, docs.id, props.to_group_id, docs.c_id\n FROM {$doc_table} AS docs\n INNER JOIN {$prop_table} AS props\n ON\n docs.id = props.ref AND\n docs.c_id = props.c_id\n WHERE\n docs.c_id = {$courseId} AND\n props.tool ='" . TOOL_DOCUMENT . "' AND\n docs.path LIKE '" . $invisible_folders['path'] . "/%' AND\n docs.filetype = 'file' AND\n (props.session_id IN ('0', '{$sessionId}') OR props.session_id IS NULL) AND\n props.visibility ='1'"; $query3 = Database::query($sql); // Add tem to an array while ($files_in_invisible_folder = Database::fetch_assoc($query3)) { $files_in_invisible_folder_path[] = $files_in_invisible_folder['path']; $files[$files_in_invisible_folder['path']] = $files_in_invisible_folder; } } // Compare the array with visible files and the array with files in invisible folders // and keep the difference (= all visible files that are not in an invisible folder) $files_for_zipfile = diff((array) $all_visible_files_path, (array) $files_in_invisible_folder_path); } else { // No invisible folders found, so all visible files can be added to the zipfile $files_for_zipfile = $all_visible_files_path; } Session::write('doc_files_to_download', $files); // Add all files in our final array to the zipfile for ($i = 0; $i < count($files_for_zipfile); $i++) { $zip->add($sysCoursePath . $courseInfo['path'] . '/document' . $files_for_zipfile[$i], PCLZIP_OPT_REMOVE_PATH, $sysCoursePath . $courseInfo['path'] . '/document' . $remove_dir, PCLZIP_CB_PRE_ADD, 'fixDocumentNameCallback');
/** * Get the subgroups ID from a group. * The default $levels value is 10 considering it as a extensive level of depth * @param int $groupId The parent group ID * @param int $levels The depth levels * @return array The list of ID */ public static function getGroupsByDepthLevel($groupId, $levels = 10) { $groups = array(); $groupId = intval($groupId); $groupTable = Database::get_main_table(TABLE_USERGROUP); $groupRelGroupTable = Database::get_main_table(TABLE_USERGROUP_REL_USERGROUP); $select = "SELECT "; $from = "FROM {$groupTable} g1 "; for ($i = 1; $i <= $levels; $i++) { $tableIndexNumber = $i; $tableIndexJoinNumber = $i - 1; $select .= "g{$i}.id as id_{$i} "; $select .= $i != $levels ? ", " : null; if ($i == 1) { $from .= "INNER JOIN {$groupRelGroupTable} gg0 ON g1.id = gg0.subgroup_id and gg0.group_id = {$groupId} "; } else { $from .= "LEFT JOIN {$groupRelGroupTable} gg{$tableIndexJoinNumber} "; $from .= " ON g{$tableIndexJoinNumber}.id = gg{$tableIndexJoinNumber}.group_id "; $from .= "LEFT JOIN {$groupTable} g{$tableIndexNumber} "; $from .= " ON gg{$tableIndexJoinNumber}.subgroup_id = g{$tableIndexNumber}.id "; } } $result = Database::query("{$select} {$from}"); while ($item = Database::fetch_assoc($result)) { foreach ($item as $groupId) { if (!empty($groupId)) { $groups[] = $groupId; } } } return array_map('intval', $groups); }
public static function get($inputData = array()) { $limitQuery = ""; $limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0; $limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0; $limitPage = (int) $limitPage > 0 ? $limitPage : 0; $limitPosition = $limitPage * (int) $limitShow; $limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}"; $limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery; $field = "commentid,postid,type,fullname,email,parentid,date_added,status,content"; $selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field; $whereQuery = isset($inputData['where']) ? $inputData['where'] : ''; $orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by commentid desc'; $result = array(); $command = "select {$selectFields} from " . Database::getPrefix() . "comments {$whereQuery}"; $command .= " {$orderBy}"; $queryCMD = isset($inputData['query']) ? $inputData['query'] : $command; $queryCMD .= $limitQuery; $cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes'; $cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1; $md5Query = md5($queryCMD); if ($cache == 'yes') { // Load dbcache $loadCache = Cache::loadKey('dbcache/system/comment/' . $md5Query, $cacheTime); if ($loadCache != false) { $loadCache = unserialize($loadCache); return $loadCache; } // end load } $query = Database::query($queryCMD); if (isset(Database::$error[5])) { return false; } $inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes'; if ((int) $query->num_rows > 0) { while ($row = Database::fetch_assoc($query)) { if (isset($row['fullname'])) { $row['fullname'] = String::decode($row['fullname']); } if (isset($row['content'])) { $row['content'] = String::decode($row['content']); } if (isset($row['date_added'])) { $row['date_addedFormat'] = Render::dateFormat($row['date_added']); } if ($inputData['isHook'] == 'yes') { if (isset($row['content'])) { $row['content'] = Shortcode::toHTML($row['content']); } } $result[] = $row; } } else { return false; } // Save dbcache Cache::saveKey('dbcache/system/comment/' . $md5Query, serialize($result)); // end save return $result; }
/** * Displays a list with posts a user can select to execute his task. * * @param Integer $blog_id * @param unknown_type $task_id */ public static function display_select_task_post($blog_id, $task_id) { // Init $tbl_blogs_tasks = Database::get_course_table(TABLE_BLOGS_TASKS); $tbl_blogs_posts = Database::get_course_table(TABLE_BLOGS_POSTS); $tbl_users = Database::get_main_table(TABLE_MAIN_USER); $course_id = api_get_course_int_id(); $sql = "SELECT title, description FROM {$tbl_blogs_tasks}\n\t\t\t\tWHERE task_id = '" . (int) $task_id . "'\n\t\t\t\tAND c_id = {$course_id}"; $result = Database::query($sql); $row = Database::fetch_assoc($result); // Get posts and authors $sql = "SELECT post.*, user.lastname, user.firstname, user.username\n\t\t\t\tFROM {$tbl_blogs_posts} post\n\t\t\t\tINNER JOIN {$tbl_users} user ON post.author_id = user.user_id\n\t\t\t\tWHERE post.blog_id = '" . (int) $blog_id . "' AND post.c_id = {$course_id}\n\t\t\t\tORDER BY post_id DESC\n\t\t\t\tLIMIT 0, 100"; $result = Database::query($sql); // Display echo '<span class="blogpost_title">' . get_lang('SelectTaskArticle') . ' "' . stripslashes($row['title']) . '"</span>'; echo '<span style="font-style: italic;"">' . stripslashes($row['description']) . '</span><br><br>'; if (Database::num_rows($result) > 0) { while ($blog_post = Database::fetch_array($result)) { $username = api_htmlentities(sprintf(get_lang('LoginX'), $blog_post['username']), ENT_QUOTES); echo '<a href="blog.php?action=execute_task&blog_id=' . $blog_id . '&task_id=' . $task_id . '&post_id=' . $blog_post['post_id'] . '#add_comment">' . stripslashes($blog_post['title']) . '</a>, ' . get_lang('WrittenBy') . ' ' . stripslashes(Display::tag('span', api_get_person_name($blog_post['firstname'], $blog_post['lastname']), array('title' => $username))) . '<br />'; } } else { echo get_lang('NoArticles'); } }
} if (!empty($data)) { $data[] = array('id' => 'T', 'text' => 'TODOS'); echo json_encode($data); } else { echo json_encode(array(array('id' => 'T', 'text' => 'TODOS'))); } } break; case 'search_survey_by_course': if (api_is_platform_admin()) { $survey = Database::get_course_table(TABLE_SURVEY); $sql = "SELECT survey_id as id, title, anonymous\n FROM {$survey}\n WHERE\n c_id = %d AND\n session_id = %d AND\n title LIKE '%s'"; $sql_query = sprintf($sql, intval($_GET['course_id']), intval($_GET['session_id']), '%' . Database::escape_string($_GET['q']) . '%'); $result = Database::query($sql_query); while ($survey = Database::fetch_assoc($result)) { $survey['title'] .= $survey['anonymous'] == 1 ? ' (' . get_lang('Anonymous') . ')' : ''; $data[] = array('id' => $survey['id'], 'text' => strip_tags(html_entity_decode($survey['title']))); } if (!empty($data)) { echo json_encode($data); } else { echo json_encode(array()); } } break; case 'display_sessions_courses': $sessionId = intval($_GET['session']); $userTable = Database::get_main_table(TABLE_MAIN_USER); $coursesData = SessionManager::get_course_list_by_session_id($sessionId); $courses = array();
/** * Displays the menu for manipulating a step * @return string html */ public function display_manipulate($item_id, $item_type = TOOL_DOCUMENT) { $course_id = api_get_course_int_id(); $course_code = api_get_course_id(); $_course = api_get_course_info(); $return = '<div class="actions">'; switch ($item_type) { case 'dokeos_chapter': case 'chapter': // Commented the message cause should not show it. //$lang = get_lang('TitleManipulateChapter'); break; case 'dokeos_module': case 'module': // Commented the message cause should not show it. //$lang = get_lang('TitleManipulateModule'); break; case TOOL_DOCUMENT: // Commented the message cause should not show it. //$lang = get_lang('TitleManipulateDocument'); break; case TOOL_LINK: case 'link': // Commented the message cause should not show it. //$lang = get_lang('TitleManipulateLink'); break; case TOOL_QUIZ: // Commented the message cause should not show it. //$lang = get_lang('TitleManipulateQuiz'); break; case TOOL_STUDENTPUBLICATION: // Commented the message cause should not show it. //$lang = get_lang('TitleManipulateStudentPublication'); break; } $tbl_lp_item = Database::get_course_table(TABLE_LP_ITEM); $item_id = intval($item_id); $sql = "SELECT * FROM " . $tbl_lp_item . " as lp WHERE lp.c_id = " . $course_id . " AND lp.id = " . $item_id; $result = Database::query($sql); $row = Database::fetch_assoc($result); $webCodepath = api_get_path(WEB_CODE_PATH); $audio_player = null; // We display an audio player if needed. if (!empty($row['audio'])) { $audio_player .= '<div class="lp_mediaplayer" id="container"> <a href="http://www.macromedia.com/go/getflashplayer">Get the Flash Player</a> to see this player.</div>'; $audio_player .= '<script type="text/javascript" src="' . $webCodepath . 'inc/lib/mediaplayer/swfobject.js"></script>'; $audio_player .= '<script> var s1 = new SWFObject("' . $webCodepath . 'inc/lib/mediaplayer/player.swf","ply","250","20","9","#FFFFFF"); s1.addParam("allowscriptaccess","always"); s1.addParam("flashvars","file=' . api_get_path(WEB_PUBLIC_PATH) . 'courses/' . $_course['path'] . '/document/audio/' . $row['audio'] . '&autostart=true"); s1.write("container"); </script>'; } $url = api_get_self() . '?cidReq=' . Security::remove_XSS($_GET['cidReq']) . '&view=build&id=' . $item_id . '&lp_id=' . $this->lp_id; $return .= Display::url(Display::return_icon('edit.png', get_lang('Edit'), array(), ICON_SIZE_SMALL), $url . '&action=edit_item&path_item=' . $row['path']); $return .= Display::url(Display::return_icon('move.png', get_lang('Move'), array(), ICON_SIZE_SMALL), $url . '&action=move_item'); // Commented for now as prerequisites cannot be added to chapters. if ($item_type != 'dokeos_chapter' && $item_type != 'chapter') { $return .= Display::url(Display::return_icon('accept.png', get_lang('LearnpathPrerequisites'), array(), ICON_SIZE_SMALL), $url . '&action=edit_item_prereq'); } $return .= Display::url(Display::return_icon('delete.png', get_lang('Delete'), array(), ICON_SIZE_SMALL), $url . '&action=delete_item'); if ($item_type == TOOL_HOTPOTATOES) { $document_data = DocumentManager::get_document_data_by_id($row['path'], $course_code); $return .= get_lang('File') . ': ' . $document_data['absolute_path_from_document']; } if ($item_type == TOOL_DOCUMENT) { $document_data = DocumentManager::get_document_data_by_id($row['path'], $course_code); $return .= get_lang('File') . ': ' . $document_data['absolute_path_from_document']; } $return .= '</div>'; if (!empty($audio_player)) { $return .= '<br />' . $audio_player; } return $return; }
/** * Get the data of the last received messages for a user * @param int $userId The user id * @param int $lastId The id of the last received message * @return int The count of new messages */ public static function getMessagesFromLastReceivedMessage($userId, $lastId = 0) { $userId = intval($userId); $lastId = intval($lastId); if (empty($userId)) { return 0; } $messagesTable = Database::get_main_table(TABLE_MESSAGE); $userTable = Database::get_main_table(TABLE_MAIN_USER); $messages = array(); $sql = "SELECT m.*, u.user_id, u.lastname, u.firstname\n FROM {$messagesTable} as m\n INNER JOIN {$userTable} as u\n ON m.user_sender_id = u.user_id\n WHERE\n m.user_receiver_id = {$userId} AND\n m.msg_status = " . MESSAGE_STATUS_UNREAD . "\n AND m.id > {$lastId}\n ORDER BY m.send_date DESC"; $result = Database::query($sql); if ($result !== false) { while ($row = Database::fetch_assoc($result)) { $messages[] = $row; } } return $messages; }
function fullexportspecial() { global $tbl_session, $tbl_session_course, $export; $FileZip = create_zip(); $to_group_id = 0; $code_course = ''; $list_course = array(); $zip_folder = new PclZip($FileZip['TEMP_FILE_ZIP']); $list_course = CourseManager::get_course_list(); $tbl_document = Database::get_course_table(TABLE_DOCUMENT); $tbl_property = Database::get_course_table(TABLE_ITEM_PROPERTY); $groupCondition = " props.to_group_id = {$to_group_id}"; if (empty($to_group_id)) { $groupCondition = " (props.to_group_id = 0 OR props.to_group_id IS NULL)"; } if (count($list_course) > 0) { foreach ($list_course as $_course) { if ($FileZip['PATH'] == '/') { $querypath = ''; // to prevent ...path LIKE '//%'... in query } else { $querypath = $FileZip['PATH']; } $course_id = $_course['real_id']; //Add tem to the zip file course $sql = "SELECT path FROM {$tbl_document} AS docs, {$tbl_property} AS props\n WHERE props.tool='" . TOOL_DOCUMENT . "'\n AND docs.id=props.ref\n AND docs.path LIKE '" . $querypath . "/%'\n AND docs.filetype='file'\n AND (docs.session_id = '0' OR docs.session_id IS NULL)\n AND props.visibility<>'2'\n AND {$groupCondition}\n AND docs.c_id = {$course_id}\n AND props.c_id = {$course_id}"; $query = Database::query($sql); while ($rows_course_file = Database::fetch_assoc($query)) { $rows_course_file['path']; $zip_folder->add($FileZip['PATH_COURSE'] . $_course['directory'] . "/document" . $rows_course_file['path'], PCLZIP_OPT_ADD_PATH, $_course['directory'], PCLZIP_OPT_REMOVE_PATH, $FileZip['PATH_COURSE'] . $_course['directory'] . "/document" . $FileZip['PATH_REMOVE']); } //Add tem to the zip file session course $code_course = $_course['code']; $sql_session = "SELECT s.id, name, c_id\n FROM {$tbl_session_course} sc\n INNER JOIN {$tbl_session} s\n ON sc.session_id = s.id\n WHERE c_id = '{$course_id}' "; $query_session = Database::query($sql_session); while ($rows_session = Database::fetch_assoc($query_session)) { $session_id = $rows_session['id']; $sql_session_doc = "SELECT path FROM {$tbl_document} AS docs, {$tbl_property} AS props\n WHERE props.tool='" . TOOL_DOCUMENT . "'\n AND docs.id=props.ref\n AND docs.path LIKE '" . $querypath . "/%'\n AND docs.filetype='file'\n AND docs.session_id = '{$session_id}'\n AND props.visibility<>'2'\n AND {$groupCondition}\n AND docs.c_id = {$course_id}\n AND props.c_id = {$course_id} "; $query_session_doc = Database::query($sql_session_doc); while ($rows_course_session_file = Database::fetch_assoc($query_session_doc)) { $zip_folder->add($FileZip['PATH_COURSE'] . $_course['directory'] . '/document' . $rows_course_session_file['path'], PCLZIP_OPT_ADD_PATH, $_course['directory'] . "/" . $rows_session['name'], PCLZIP_OPT_REMOVE_PATH, $FileZip['PATH_COURSE'] . $_course['directory'] . '/document' . $FileZip['PATH_REMOVE']); } } } $name = rename_zip($FileZip); if ($name === false) { $export = false; return false; } else { $export = true; return $name; } } else { Display::display_error_message(get_lang('ErrorMsgSpecialExport')); //main API $export = false; return false; } }
/** * Gives a list of [session_id-course_code] => [status] for the current user. * @param integer $user_id * @return array list of statuses (session_id-course_code => status) */ public static function get_personal_session_course_list($user_id) { // Database Table Definitions $tbl_course = Database::get_main_table(TABLE_MAIN_COURSE); $tbl_user = Database::get_main_table(TABLE_MAIN_USER); $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION); $tbl_session_user = Database::get_main_table(TABLE_MAIN_SESSION_USER); $tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER); $tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); if ($user_id != strval(intval($user_id))) { return array(); } // We filter the courses from the URL $join_access_url = $where_access_url = ''; if (api_get_multiple_access_url()) { $access_url_id = api_get_current_access_url_id(); if ($access_url_id != -1) { $tbl_url_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $join_access_url = "LEFT JOIN {$tbl_url_course} url_rel_course ON url_rel_course.c_id = course.id"; $where_access_url = " AND access_url_id = {$access_url_id} "; } } // Courses in which we subscribed out of any session $tbl_user_course_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY); $sql = "SELECT\n course.code,\n course_rel_user.status course_rel_status,\n course_rel_user.sort sort,\n course_rel_user.user_course_cat user_course_cat\n FROM " . $tbl_course_user . " course_rel_user\n LEFT JOIN " . $tbl_course . " course\n ON course.id = course_rel_user.c_id\n LEFT JOIN " . $tbl_user_course_category . " user_course_category\n ON course_rel_user.user_course_cat = user_course_category.id\n {$join_access_url}\n WHERE\n course_rel_user.user_id = '" . $user_id . "' AND\n course_rel_user.relation_type <> " . COURSE_RELATION_TYPE_RRHH . "\n {$where_access_url}\n ORDER BY user_course_category.sort, course_rel_user.sort, course.title ASC"; $course_list_sql_result = Database::query($sql); $personal_course_list = array(); if (Database::num_rows($course_list_sql_result) > 0) { while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) { $course_info = api_get_course_info($result_row['code']); $result_row['course_info'] = $course_info; $personal_course_list[] = $result_row; } } $coachCourseConditions = null; // Getting sessions that are related to a coach in the session_rel_course_rel_user table if (api_is_allowed_to_create_course()) { $sessionListFromCourseCoach = array(); $sql = " SELECT DISTINCT session_id\n FROM {$tbl_session_course_user}\n WHERE user_id = {$user_id} AND status = 2 "; $result = Database::query($sql); if (Database::num_rows($result)) { $result = Database::store_result($result); foreach ($result as $session) { $sessionListFromCourseCoach[] = $session['session_id']; } } if (!empty($sessionListFromCourseCoach)) { $condition = implode("','", $sessionListFromCourseCoach); $coachCourseConditions = " OR ( s.id IN ('{$condition}'))"; } } // Get the list of sessions where the user is subscribed // This is divided into two different queries $sessions = array(); $sql = "SELECT DISTINCT s.id, name, access_start_date, access_end_date\n FROM {$tbl_session_user}, {$tbl_session} s\n WHERE (\n session_id = s.id AND\n user_id = {$user_id} AND\n relation_type <> " . SESSION_RELATION_TYPE_RRHH . "\n )\n {$coachCourseConditions}\n ORDER BY access_start_date, access_end_date, name"; $result = Database::query($sql); if (Database::num_rows($result) > 0) { while ($row = Database::fetch_assoc($result)) { $sessions[$row['id']] = $row; } } $sql = "SELECT DISTINCT\n id, name, access_start_date, access_end_date\n FROM {$tbl_session} s\n WHERE (\n id_coach = {$user_id}\n )\n {$coachCourseConditions}\n ORDER BY access_start_date, access_end_date, name"; $result = Database::query($sql); if (Database::num_rows($result) > 0) { while ($row = Database::fetch_assoc($result)) { if (empty($sessions[$row['id']])) { $sessions[$row['id']] = $row; } } } if (api_is_allowed_to_create_course()) { foreach ($sessions as $enreg) { $session_id = $enreg['id']; $session_visibility = api_get_session_visibility($session_id); if ($session_visibility == SESSION_INVISIBLE) { continue; } // This query is horribly slow when more than a few thousand // users and just a few sessions to which they are subscribed $id_session = $enreg['id']; $personal_course_list_sql = "SELECT DISTINCT\n course.code code,\n course.title i,\n " . (api_is_western_name_order() ? "CONCAT(user.firstname,' ',user.lastname)" : "CONCAT(user.lastname,' ',user.firstname)") . " t,\n email, course.course_language l,\n 1 sort,\n category_code user_course_cat,\n access_start_date,\n access_end_date,\n session.id as session_id,\n session.name as session_name\n FROM {$tbl_session_course_user} as session_course_user\n INNER JOIN {$tbl_course} AS course\n ON course.id = session_course_user.c_id\n INNER JOIN {$tbl_session} as session\n ON session.id = session_course_user.session_id\n LEFT JOIN {$tbl_user} as user\n ON user.id = session_course_user.user_id OR session.id_coach = user.id\n WHERE\n session_course_user.session_id = {$id_session} AND (\n (session_course_user.user_id = {$user_id} AND session_course_user.status = 2)\n OR session.id_coach = {$user_id}\n )\n ORDER BY i"; $course_list_sql_result = Database::query($personal_course_list_sql); while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) { $result_row['course_info'] = api_get_course_info($result_row['code']); $key = $result_row['session_id'] . ' - ' . $result_row['code']; $personal_course_list[$key] = $result_row; } } } foreach ($sessions as $enreg) { $session_id = $enreg['id']; $session_visibility = api_get_session_visibility($session_id); if ($session_visibility == SESSION_INVISIBLE) { continue; } /* This query is very similar to the above query, but it will check the session_rel_course_user table if there are courses registered to our user or not */ $personal_course_list_sql = "SELECT DISTINCT\n course.code code,\n course.title i, CONCAT(user.lastname,' ',user.firstname) t,\n email,\n course.course_language l,\n 1 sort,\n category_code user_course_cat,\n access_start_date,\n access_end_date,\n session.id as session_id,\n session.name as session_name,\n IF((session_course_user.user_id = 3 AND session_course_user.status=2),'2', '5')\n FROM {$tbl_session_course_user} as session_course_user\n INNER JOIN {$tbl_course} AS course\n ON course.id = session_course_user.c_id AND session_course_user.session_id = {$session_id}\n INNER JOIN {$tbl_session} as session ON session_course_user.session_id = session.id\n LEFT JOIN {$tbl_user} as user ON user.id = session_course_user.user_id\n WHERE session_course_user.user_id = {$user_id}\n ORDER BY i"; $course_list_sql_result = Database::query($personal_course_list_sql); while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) { $result_row['course_info'] = api_get_course_info($result_row['code']); $key = $result_row['session_id'] . ' - ' . $result_row['code']; if (!isset($personal_course_list[$key])) { $personal_course_list[$key] = $result_row; } } } return $personal_course_list; }
/** * Get list of courses based on users of a group for a group admin * @param int $userId The user id * @return array */ public static function getCoursesFollowedByGroupAdmin($userId) { $coursesList = []; $courseTable = Database::get_main_table(TABLE_MAIN_COURSE); $courseUserTable = Database::get_main_table(TABLE_MAIN_COURSE_USER); $userGroup = new UserGroup(); $userIdList = $userGroup->getGroupUsersByUser($userId); if (empty($userIdList)) { return []; } $sql = "SELECT DISTINCT(c.id), c.title\n FROM {$courseTable} c\n INNER JOIN {$courseUserTable} cru ON c.id = cru.c_id\n WHERE (\n cru.user_id IN (" . implode(', ', $userIdList) . ")\n AND cru.relation_type = 0\n )"; if (api_is_multiple_url_enabled()) { $courseAccessUrlTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE); $accessUrlId = api_get_current_access_url_id(); if ($accessUrlId != -1) { $sql = "SELECT DISTINCT(c.id), c.title\n FROM {$courseTable} c\n INNER JOIN {$courseUserTable} cru ON c.id = cru.c_id\n INNER JOIN {$courseAccessUrlTable} crau ON c.id = crau.c_id\n WHERE crau.access_url_id = {$accessUrlId}\n AND (\n cru.id_user IN (" . implode(', ', $userIdList) . ") AND\n cru.relation_type = 0\n )"; } } $result = Database::query($sql); while ($row = Database::fetch_assoc($result)) { $coursesList[] = $row; } return $coursesList; }
/** * Get the progress of a exercise * @param int $sessionId The session ID (session.id) * @param int $courseId The course ID (course.id) * @param int $exerciseId The quiz ID (c_quiz.id) * @param int $answer The answer status (0 = incorrect, 1 = correct, 2 = both) * @param array $options An array of options you can pass to the query (limit, where and order) * @return array An array with the data of exercise(s) progress */ public static function get_exercise_progress($sessionId = 0, $courseId = 0, $exerciseId = 0, $date_from = null, $date_to = null, $options = array()) { $sessionId = intval($sessionId); $courseId = intval($courseId); $exerciseId = intval($exerciseId); $date_from = Database::escape_string($date_from); $date_to = Database::escape_string($date_to); /* * This method gets the data by blocks, as previous attempts at one single * query made it take ages. The logic of query division is described below */ // Get tables names $tuser = Database::get_main_table(TABLE_MAIN_USER); $tquiz = Database::get_course_table(TABLE_QUIZ_TEST); $tquiz_answer = Database::get_course_table(TABLE_QUIZ_ANSWER); $tquiz_question = Database::get_course_table(TABLE_QUIZ_QUESTION); $tquiz_rel_question = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION); $ttrack_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES); $ttrack_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT); $sessions = array(); $courses = array(); // if session ID is defined but course ID is empty, get all the courses // from that session if (!empty($sessionId) && empty($courseId)) { // $courses is an array of course int id as index and course details hash as value $courses = SessionManager::get_course_list_by_session_id($sessionId); $sessions[$sessionId] = api_get_session_info($sessionId); } elseif (empty($sessionId) && !empty($courseId)) { // if, to the contrary, course is defined but not sessions, get the sessions that include this course // $sessions is an array like: [0] => ('id' => 3, 'name' => 'Session 35'), [1] => () etc; $course = api_get_course_info_by_id($courseId); $sessionsTemp = SessionManager::get_session_by_course($courseId); $courses[$courseId] = $course; foreach ($sessionsTemp as $sessionItem) { $sessions[$sessionItem['id']] = $sessionItem; } } elseif (!empty($courseId) && !empty($sessionId)) { //none is empty $course = api_get_course_info_by_id($courseId); $courses[$courseId] = array($course['code']); $courses[$courseId]['code'] = $course['code']; $sessions[$sessionId] = api_get_session_info($sessionId); } else { //both are empty, not enough data, return an empty array return array(); } // Now we have two arrays of courses and sessions with enough data to proceed // If no course could be found, we shouldn't return anything. // Sessions can be empty (then we only return the pure-course-context results) if (count($courses) < 1) { return array(); } $data = array(); // The following loop is less expensive than what it seems: // - if a course was defined, then we only loop through sessions // - if a session was defined, then we only loop through courses // - if a session and a course were defined, then we only loop once foreach ($courses as $courseIdx => $courseData) { $where = ''; $whereParams = array(); $whereSessionParams = ''; if (count($sessions > 0)) { foreach ($sessions as $sessionIdx => $sessionData) { if (!empty($sessionIdx)) { $whereSessionParams .= $sessionIdx . ','; } } $whereSessionParams = substr($whereSessionParams, 0, -1); } if (!empty($exerciseId)) { $exerciseId = intval($exerciseId); $where .= ' AND q.id = %d '; $whereParams[] = $exerciseId; } /* * This feature has been disabled for now, to avoid having to * join two very large tables //2 = show all questions (wrong and correct answered) if ($answer != 2) { $answer = intval($answer); //$where .= ' AND qa.correct = %d'; //$whereParams[] = $answer; } */ $limit = ''; if (!empty($options['limit'])) { $limit = " LIMIT " . $options['limit']; } if (!empty($options['where'])) { $where .= ' AND ' . Database::escape_string($options['where']); } $order = ''; if (!empty($options['order'])) { $order = " ORDER BY " . $options['order']; } if (!empty($date_to) && !empty($date_from)) { $where .= sprintf(" AND (te.start_date BETWEEN '%s 00:00:00' AND '%s 23:59:59')", $date_from, $date_to); } $sql = "SELECT\n te.session_id,\n ta.id as attempt_id,\n te.exe_user_id as user_id,\n te.exe_id as exercise_attempt_id,\n ta.question_id,\n ta.answer as answer_id,\n ta.tms as time,\n te.exe_exo_id as quiz_id,\n CONCAT ('c', q.c_id, '_e', q.id) as exercise_id,\n q.title as quiz_title,\n qq.description as description\n FROM {$ttrack_exercises} te\n INNER JOIN {$ttrack_attempt} ta ON ta.exe_id = te.exe_id\n INNER JOIN {$tquiz} q ON q.id = te.exe_exo_id\n INNER JOIN {$tquiz_rel_question} rq ON rq.exercice_id = q.id AND rq.c_id = q.c_id\n INNER JOIN {$tquiz_question} qq\n ON\n qq.id = rq.question_id AND\n qq.c_id = rq.c_id AND\n qq.position = rq.question_order AND\n ta.question_id = rq.question_id\n WHERE\n te.c_id = {$courseIdx} " . (empty($whereSessionParams) ? '' : "AND te.session_id IN ({$whereSessionParams})") . "\n AND q.c_id = {$courseIdx}\n {$where} {$order} {$limit}"; $sql_query = vsprintf($sql, $whereParams); // Now browse through the results and get the data $rs = Database::query($sql_query); $userIds = array(); $questionIds = array(); $answerIds = array(); while ($row = Database::fetch_array($rs)) { //only show if exercise is visible if (api_get_item_visibility($courseData, 'quiz', $row['exercise_id'])) { $userIds[$row['user_id']] = $row['user_id']; $questionIds[$row['question_id']] = $row['question_id']; $answerIds[$row['question_id']][$row['answer_id']] = $row['answer_id']; $row['session'] = $sessions[$row['session_id']]; $data[] = $row; } } // Now fill questions data. Query all questions and answers for this test to avoid $sqlQuestions = "SELECT tq.c_id, tq.id as question_id, tq.question, tqa.id_auto,\n tqa.answer, tqa.correct, tq.position, tqa.id_auto as answer_id\n FROM {$tquiz_question} tq, {$tquiz_answer} tqa\n WHERE\n tqa.question_id = tq.id AND\n tqa.c_id = tq.c_id AND\n tq.c_id = {$courseIdx} AND\n tq.id IN (" . implode(',', $questionIds) . ")"; $resQuestions = Database::query($sqlQuestions); $answer = array(); $question = array(); while ($rowQuestion = Database::fetch_assoc($resQuestions)) { $questionId = $rowQuestion['question_id']; $answerId = $rowQuestion['answer_id']; $answer[$questionId][$answerId] = array('position' => $rowQuestion['position'], 'question' => $rowQuestion['question'], 'answer' => $rowQuestion['answer'], 'correct' => $rowQuestion['correct']); $question[$questionId]['question'] = $rowQuestion['question']; } // Now fill users data $sqlUsers = "SELECT user_id, username, lastname, firstname\n FROM {$tuser}\n WHERE user_id IN (" . implode(',', $userIds) . ")"; $resUsers = Database::query($sqlUsers); while ($rowUser = Database::fetch_assoc($resUsers)) { $users[$rowUser['user_id']] = $rowUser; } foreach ($data as $id => $row) { $rowQuestId = $row['question_id']; $rowAnsId = $row['answer_id']; $data[$id]['session'] = $sessions[$row['session_id']]['name']; $data[$id]['firstname'] = $users[$row['user_id']]['firstname']; $data[$id]['lastname'] = $users[$row['user_id']]['lastname']; $data[$id]['username'] = $users[$row['user_id']]['username']; $data[$id]['answer'] = $answer[$rowQuestId][$rowAnsId]['answer']; $data[$id]['correct'] = $answer[$rowQuestId][$rowAnsId]['correct'] == 0 ? get_lang('No') : get_lang('Yes'); $data[$id]['question'] = $question[$rowQuestId]['question']; $data[$id]['question_id'] = $rowQuestId; $data[$id]['description'] = $row['description']; } /* The minimum expected array structure at the end is: attempt_id, session name, exercise_id, quiz_title, username, lastname, firstname, time, question_id, question, answer, */ } return $data; }
$userManager = UserManager::getManager(); $repository = UserManager::getRepository(); /** * E-mails list loop */ foreach ($list as $mail) { $mail = trim($mail); $sql = "SELECT user_id, official_code, firstname, lastname, email, username, language\n FROM {$users} WHERE email = '{$mail}'\n"; $res = Database::query($sql); if ($res === false) { echo 'Error in database with email ' . $mail . "\n"; } if (Database::num_rows($res) == 0) { echo '[Error] Email not found in database: ' . $row['email'] . "\n"; } else { $row = Database::fetch_assoc($res); $pass = api_substr($row['username'], 0, 4) . rand(0, 9) . rand(0, 9); if ($user) { /** @var User $user */ $user = $repository->find($row['user_id']); $user->setPlainPassword($pass); $userManager->updateUser($user, true); } else { echo "[Error] Error updating password. Skipping {$mail}\n"; continue; } $user = array('FirstName' => $row['firstname'], 'LastName' => $row['lastname'], 'UserName' => $row['username'], 'Password' => $pass, 'Email' => $mail); $l = api_get_interface_language(); if (!empty($row['language'])) { $l = $row['language']; }
/** * Gets a list of pending orders * @result array List of orders * @todo Enable pagination */ function pendingList($bcCodetext) { $tableBuyTemporal = $bcCodetext === 'THIS_IS_A_SESSION' ? Database::get_main_table(TABLE_BUY_SESSION_TEMPORARY) : Database::get_main_table(TABLE_BUY_COURSE_TEMPORAL); $sql = "SELECT * FROM {$tableBuyTemporal};"; $res = Database::query($sql); $aux = array(); while ($row = Database::fetch_assoc($res)) { $aux[] = $row; } return $aux; }
public function build_session_course() { $tbl_session = Database::get_main_table(TABLE_MAIN_SESSION); $tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE); $list_course = CourseManager::get_course_list(); $list = array(); foreach ($list_course as $_course) { $this->course = new Course(); $this->course->code = $_course['code']; $this->course->type = 'partial'; $this->course->path = api_get_path(SYS_COURSE_PATH) . $_course['directory'] . '/'; $this->course->backup_path = api_get_path(SYS_COURSE_PATH) . $_course['directory']; $this->course->encoding = api_get_system_encoding(); //current platform encoding $code_course = $_course['code']; $courseId = $_course['real_id']; $sql_session = "SELECT s.id, name, c_id\n FROM {$tbl_session_course} sc\n INNER JOIN {$tbl_session} s\n ON sc.session_id = s.id\n WHERE sc.c_id = '{$courseId}' "; $query_session = Database::query($sql_session); while ($rows_session = Database::fetch_assoc($query_session)) { $session = new CourseSession($rows_session['id'], $rows_session['name']); $this->course->add_resource($session); } $list[] = $this->course; } return $list; }
/** * Init */ $rights_full = array("article_add", "article_delete", "article_edit", "article_rate", "article_comments_add", "article_comments_delete", "article_comments_rate", "task_management", "member_management", "role_management"); $rights_limited = array("Add", "Edit", "Delete"); $rights_blog = array("article_add", "article_delete", "article_edit", "article_rate", "article_comments_add", "article_comments_delete", "article_comments_rate", "task_management", "member_management", "role_management"); $course_tool_table = Database::get_course_table(TABLE_TOOL_LIST); // Get all user $blog_users = Blog::get_blog_users($_GET['blog_id']); $course_id = api_get_course_int_id(); // Remove the blog creater because he has all the rights automatically // and we want to keep it that way. $tbl_course_rel_user = $table = Database::get_main_table(TABLE_MAIN_COURSE_USER); $sql = "SELECT user_id FROM {$tbl_course_rel_user} WHERE status = '1' AND course_code = '" . $_SESSION['_cid'] . "'"; $result = Database::query($sql); while ($user = Database::fetch_assoc($result)) { unset($blog_users[$user['user_id']]); } //$user_id=$userIdViewed; if ($mainUserInfo['status'] == 1) { $course_admin = 1; } include_once 'permissions_functions.inc.php'; // ACTIONS if (isset($_GET['do'])) { if (isset($_GET['permission']) and isset($_GET['tool']) and ($_GET['do'] == 'grant' or $_GET['do'] == 'revoke')) { $result_message = store_one_permission('user', $_GET['do'], $_GET['user_id'], $_GET['tool'], $_GET['permission']); } if (isset($_GET['role']) and ($_GET['do'] == 'grant' or $_GET['do'] == 'revoke')) { $result_message = assign_role('user', $_GET['do'], $user_id, $_GET['role'], $_GET['scope']); }
} $tableRows[] = $tableRow; } $tplPath = 'skill/student_report.html.twig'; } else { if ($isStudentBoss) { $selectedStudent = isset($_REQUEST['student']) ? intval($_REQUEST['student']) : 0; $tableRows = array(); $followedStudents = UserManager::getUsersFollowedByStudentBoss($userId); foreach ($followedStudents as &$student) { $student['completeName'] = api_get_person_name($student['firstname'], $student['lastname']); } if ($selectedStudent > 0) { $sql = "SELECT s.name, sru.acquired_skill_at, c.title, c.directory\n FROM {$skillTable} s\n INNER JOIN {$skillRelUserTable} sru\n ON s.id = sru.skill_id\n INNER JOIN {$courseTable} c\n ON sru.course_id = c.id\n WHERE sru.user_id = {$selectedStudent}\n "; $result = Database::query($sql); while ($resultData = Database::fetch_assoc($result)) { $tableRow = array('completeName' => $followedStudents[$selectedStudent]['completeName'], 'skillName' => $resultData['name'], 'achievedAt' => api_format_date($resultData['acquired_skill_at'], DATE_FORMAT_NUMBER), 'courseImage' => Display::return_icon('course.png', null, null, ICON_SIZE_MEDIUM, null, true), 'courseName' => $resultData['title']); $imageSysPath = sprintf("%s%s/course-pic.png", api_get_path(SYS_COURSE_PATH), $resultData['directory']); if (file_exists($imageSysPath)) { $thumbSysPath = sprintf("%s%s/course-pic32.png", api_get_path(SYS_COURSE_PATH), $resultData['directory']); $thumbWebPath = sprintf("%s%s/course-pic32.png", api_get_path(WEB_COURSE_PATH), $resultData['directory']); if (!file_exists($thumbSysPath)) { $courseImageThumb = new Image($imageSysPath); $courseImageThumb->resize(32); $courseImageThumb->send_image($thumbSysPath); } $tableRow['courseImage'] = $thumbWebPath; } $tableRows[] = $tableRow; } }
$courseInfo = api_get_course_info(); allowOnlySubscribedUser(api_get_user_id(), $work_id, $courseInfo['real_id']); $userCondition = null; // All users if ($courseInfo['show_score'] == 0) { // Do another filter } else { // Only teachers $userCondition = " AND props.insert_user_id = " . api_get_user_id(); } //for other users, we need to create a zipfile with only visible files and folders $sql = "SELECT DISTINCT url, title, description, insert_user_id, insert_date, contains_file\n FROM {$tbl_student_publication} AS work INNER JOIN {$prop_table} AS props\n ON (props.c_id = {$course_id} AND\n work.c_id = {$course_id} AND\n work.id = props.ref)\n WHERE\n props.tool='work' AND\n work.accepted = 1 AND\n work.active = 1 AND\n work.parent_id = {$work_id} AND\n work.filetype = 'file' AND\n props.visibility = '1' AND\n work.post_group_id = {$groupId}\n {$userCondition}\n "; } $query = Database::query($sql); //add tem to the zip file while ($not_deleted_file = Database::fetch_assoc($query)) { $user_info = api_get_user_info($not_deleted_file['insert_user_id']); $insert_date = api_get_local_time($not_deleted_file['insert_date']); $insert_date = str_replace(array(':', '-', ' '), '_', $insert_date); $filename = $insert_date . '_' . $user_info['username'] . '_' . basename($not_deleted_file['title']); if (file_exists($sys_course_path . $_course['path'] . '/' . $not_deleted_file['url']) && !empty($not_deleted_file['url'])) { $files[basename($not_deleted_file['url'])] = $filename; $zip_folder->add($sys_course_path . $_course['path'] . '/' . $not_deleted_file['url'], PCLZIP_OPT_REMOVE_PATH, $sys_course_path . $_course['path'] . '/work', PCLZIP_CB_PRE_ADD, 'my_pre_add_callback'); } //Convert texts in html files if ($not_deleted_file['contains_file'] == 0) { $filename = trim($filename) . ".html"; $work_temp = api_get_path(SYS_ARCHIVE_PATH) . api_get_unique_id() . '_' . $filename; file_put_contents($work_temp, $not_deleted_file['description']); $files[basename($work_temp)] = $filename; $zip_folder->add($work_temp, PCLZIP_OPT_REMOVE_PATH, api_get_path(SYS_ARCHIVE_PATH), PCLZIP_CB_PRE_ADD, 'my_pre_add_callback');
/** * Returns true if user exists in the platform when asking the password * * @param string $username (email or username) * @return array|boolean */ public static function get_user_accounts_by_username($username) { if (strpos($username, '@')) { $username = api_strtolower($username); $email = true; } else { $username = api_strtolower($username); $email = false; } if ($email) { $condition = "LOWER(email) = '" . Database::escape_string($username) . "' "; } else { $condition = "LOWER(username) = '" . Database::escape_string($username) . "'"; } $tbl_user = Database::get_main_table(TABLE_MAIN_USER); $query = "SELECT user_id AS uid, lastname AS lastName, firstname AS firstName, username AS loginName, password, email,\n status AS status, official_code, phone, picture_uri, creator_id\n\t\t\t\t FROM {$tbl_user}\n\t\t\t\t WHERE ( {$condition} AND active = 1) "; $result = Database::query($query); $num_rows = Database::num_rows($result); if ($result && $num_rows > 0) { return Database::fetch_assoc($result); } return false; }